SQL: How to remove & update records in a table, sync'ing with a (Q)List data in order to decrease the burden on database/table?

I am using Qt and MS-Sql Server on Windows7 OS. What I have is an MS-SQL database that I use to store data/info coming from equipment that is mounted in some vehicles. There is a table in the database named TransactionFilesInfo – a table used to store information about transaction files from the equipment, when […]

Return after insert stored procedure, if success is return 1 else return 0

I have a stored procedure. After insert, it should return a value: if the insert was successful, then return 1, else return 0. For example: ALTER PROCEDURE [dbo].[sp_save_transaction] — Parameter @SourceCode nvarchar(255), @DepositorName nvarchar(255), @TransactionDate date AS SET XACT_ABORT ON –BEGIN TRAN DECLARE @PaymentDate date DECLARE @PaymentDepositorName nvarchar(255) DECLARE @PaymentNationId int SET @PaymentDate = @TransactionDate […]

Copy value from one column to another (same table) in SQL using a mapping table

I am trying to copy the value from one column to another within the same table, and struggling to work out the correct way to do this. Ideally I would do this in within the code rather than the SQL, however I am dealing with a legacy application and as such updating the code (whilst […]

How can I prevent SqlPackage.exe from dropping and re-creating constraints when deploying a dacpac?

I have a Visual Studio sql project with a table defined like the following: CREATE TABLE [dbo].[Hoerses] ( [HoersId] INT NOT NULL PRIMARY KEY, [DatePurchased] datetime NOT NULL CONSTRAINT [DF_Hoerses_DatePurchased] DEFAULT DATETIMEFROMPARTS(1985,01,01,0,0,0,0) ) When I target a preexisting SQL database with a “Script” command sqlpackage.exe /Action:Script /SourceFile:DatabaseProject1.dacpac /Profile:publish.xml /OutputPath:deployscript_test.sql /TargetPassword:redacted Then I get the following […]

Audit Microsoft SQL Server table with many columns

I have a problem with getting this audit to work on my table. I have a table with 197 columns, and i want to audit that. We have used this solution successfully before but the tables always had lesser columns. The solution i have implemented is from this url at the bottom. It just seems […]

SqlDependency not working

I’m using EF 6 with SQL Server 2012. I’m trying to use SqlDependency to refresh my cached data, in a class library (DLL). I have the following, based on whatever guides I found, but seems like it is not working, and I get no error. enabled broker service on the db created the broker queue, […]

Select query to reference tables based on table Id or Name

Consider I have a table which list all the tables in database along with the referenced tables. In my case, no foreign key reference are used in the tables. Tables references are maintained as below TableId ReferedInTableId 1 2 1 3 1 4 2 5 2 6 3 7 4 8 4 9 5 – […]

Customized processing to convert rows to Columns in SQL

I know that lots of questions have already been asked about SQL table formatting but I couldn’t find any solution to my current problem. My problem is, I have a table containing data about companies along with the employees linked to those companies. Company_ID Person_ID Name Departement_ID 1 1 aaa 1 1 2 bbb 1 […]

Odd Error When creating kind of a pivot table (sql)

I have Two tables: table 1: MSVTransaction table 2: [MonthlySalary] I would like to create a pivot table comparing the amount for employees between the two tables: select [MSV_EntitledIdNumber], [MS_Semel], sum(case when [MSV_Month] in (1) then [MSV_PaymentAmount] else 0 end) as JanMSV, sum(case when [MS_Month] in (1) then [MS_Amount] else 0 end) as JanSML, sum(case […]

Cross Server Select In SQLAlchemy

Is it possible to make SQLAlchemy do cross server joins? If I try to run something like engine = create_engine(‘mssql+pyodbc://SERVER/Database’) query = sql.text(‘SELECT TOP 10 * FROM [dbo].[Table]’) with engine.begin() as connection: data = connection.execute(query).fetchall() It works as I’d expect. If I change the query to select from [OtherServer].[OtherDatabase].[dbo].[Table] I get an error message “Login […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.