Tag: sql-server

How to update every other row from select query?

I have a SQL query that pulls 160 records from a table in my database. SELECT TableID, Number, Active FROM Table WHERE Number IN (SELECT Number FROM Table GROUP BY Number, Active HAVING COUNT(*) > 1 AND COUNT(*) < 3 AND Active = 1) ORDER BY Number, TableID What I am trying to do is […]

Delete row from table that is NULL and has the same DATE

I have a SQL question, which I am not able to handle. I need to delete rows from a table, which are NULL in the date_to columns, but have a different value to the date_from column. The column I want to delete is the one with the number 3. It has the same value in […]

How to send an email to multiple recipients ?

I have a stored procedure which sent emails to few recipients. In this I want to send to two differet recipients using @copy_recipients. But I get a syntax error. How to make this work? stored procedure code EXEC msdb.dbo.sp_send_dbmail @profile_name = ‘mail’, @recipients = @Mail1, @copy_recipients = @Mail2;@Mail3, @body =@body , @subject =@subject

Remove Leading Zeros Except last Zero

Database – SQL Server 2012 I am currently using the following code: substring( SUBSTRING(col001, 59, 8), patindex( ‘%[^0]%’, SUBSTRING(col001, 59, 8) ), 10 ) as TOTAL_DETAIL_RECORD_COUNT A lot of substrings, I know but it is working for the most part. There is one catch though. Some of the column values are 000000000. When this is […]

SQL Server gives “duplicate key” error for a non-key property?

I have a DB table products with 5 columns. The Id is the primary key. In SQL Server Management Studio, you can clearly see this here: I have a product with Id = 69 and Name = “Lawn Darts” . I am trying to insert a new product with Id = 420 and Name = […]

Can't Compile using SqlDataConnection in F# on Mono in Linux

I am using mono on Linux, making an F# code to access an SQL Server Database. I am using SqlDataConnection. My code is the following: namespace AggregatorService open FSharp.Data.TypeProviders open System.Data open System.Data.Linq module DataBaseProvider = type databaseConn = SqlDataConnection<ConnectionString = “Data Source=10.0.40.11;Initial Catalog=master;Persist Security Info=True;User ID=sa;Password=xxxxx”> let getDataContext() = let dbConn = databaseConn.GetDataContext(); dbConn […]

Using Transaction in ADO.net SQL

I am new to ADO, so I want to ask if I did right using transaction. Here the code snippet string SQL1 = “INSERT INTO tbl_cust(cust_id,cust_name) values (‘000001′,’YoungMcD’) “; string SQL2 = “UPDATE tbl_cust SET custname=’OldMcDonald’ WHERE cust_id=’000001′”; string SQL3 = “SELECT * FROM tbl_supplier WHERE supplier_code =’000001′”; // write connstring string conn = System.Configuration.ConfigurationManager.ConnectionStrings[“connstr”].ConnectionString; […]

SQL: is the main- or the sub-query being executed first?

Assuming I have a table “MyTable” that contains 10’000 records; when I run the query below to obtain just 25 records: is it selecting all 10’000 records first before to return just 25 of them, or will the query be optimized automatically by the SQL parser? SELECT * FROM ( SELECT HPE.ID, HPE.EventTitle, HPE.EventDate, HPE.EventLocation, […]

How to kill SQL process without rolling back

I have a trigger that is suppose to send an email, recently learned it was a bad idea, and it locked a process on my database. When I killed the process it is now stuck rolling back. I understand that I can only fix this with a server restart. Now what I am trying to […]

LINQ query slow, creates Timeout; generated SQL is fine?

I have a fairly complex LINQ query that is often so slow, it creates a System.Data.SqlClient.SqlException: “The wait operation timed out”. However, when I log the generated SQL (by assigning a TextWriter to the DataContext‘s Log), and execute it directly on the SQL Server, it completes in about 4 seconds, which is fine. Where does […]

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