What is the difference between CALL and EXEC in T-SQL?

Consider: CREATE PROCEDURE LowerCityDiscounts @city VARCHAR(45), @decrease DECIMAL(10,2) AS BEGIN BEGIN TRANSACTION; UPDATE Customers SET discnt = discnt – @decrease WHERE Customers.city = @city; UPDATE Customers SET discnt = 0 WHERE Customers.city = @city AND discnt < 0 COMMIT; END; I tried to call this procedure with: CALL LowerCityDiscounts ‘Cleveland’, 5; but this only produces […]

How to fix “Only one expression can be specified in the select list when the subquery is not introduced with EXISTS” error?

I’m trying to run the following query on MS SQL 2012 Express: Select ( Select Id, Salt, Password, BannedEndDate from Users where username = ‘” + LoginModel.Username + “‘ ), ( Select Count(*) From LoginFails where username = ‘” + LoginModel.Username + “‘ And IP = ‘” + Request.ServerVariables[“REMOTE_ADDR”] + “‘)” ); But I get […]


I have found some SQL queries in an application I am examining like this: SELECT DISTINCT Company, Warehouse, Item, SUM(quantity) OVER (PARTITION BY Company, Warehouse, Item) AS stock I’m quite sure this gives the same result as: SELECT Company, Warehouse, Item, SUM(quantity) AS stock GROUP BY Company, Warehouse, Item Is there any benefit (performance, readability, […]

Cannot persist computed column – not deterministic

I have this function for a computed column : CREATE FUNCTION [dbo].[GetAllocatedStartTime](@Year INT, @Week INT) RETURNS DATETIME WITH schemabinding AS BEGIN RETURN dateadd(week,@Week-(1),dateadd(day,(-1),dateadd(week,datediff(week,(0),CONVERT([varchar](4),@Year,(0))+’-01-01′),(1)))) END GO I added the WITH schemabinding in the hope it would make it deterministic so I can persist it. It should be as the two inputs [Week] and [Year] will always […]

Linq-to-SQL: how many datacontexts?

I have a SQL Server 2008 database with > 300 tables. The application I have to design is an Windows Forms app, .NET 3.5, C#. Which is the best way to work with Linq-to-SQL ? I intend to make a datacontext for each business entity. Is there any problem ? I need to know if […]

Not able to connect any remote database server after installing Visual studio 2012

I have a machine which has VS2010 and sql server 2005, 2008 installed. I were able to connect every remote database server from my application prior to installing VS2012. After installing VS2012 I am getting “Attempted to read or write protected memory. This is often an indication that other memory is corrupt.” on code where […]

How to determine if anything changed in update trigger in t-sql

How can I determine if something has changed in UPDATE trigger? For example I have table named person with only one column NAME which contains value ‘Mike’. If I run UPDATE person SET NAME = ‘Mike’ how can I determine in the update trigger that nothing has changed? I know about UPDATE(col) statement, but I […]

C# data types to SQL Server data types

How can I “convert” C# datatype to SQL Server datatype (the SqlDbType is known) i.e: C# -> “String” SQL Server -> N’String’

Can I reset cursor's position to the beginning?

As in the topic. Can I simply reset cursor‘s position to the beginning in Transact-SQL, so it can run again over the table? I want to reset it in the following context: DECLARE @userID INT DECLARE user_cursor CURSOR FOR SELECT userID FROM users WHILE /* some condition */ BEGIN … FETCH NEXT FROM user_cursor INTO […]

How to Stop SSMS 2012 from scripting SPs using sp_executesql

I realise this is a very similar question to Stop SSMS from scripting SPs using sp_executesql? However, they seem to have changed the behaviour with SSMS 2012. If you have the ‘Check for existence’ option selected, as in: … it now generates an IF NOT EXISTS for the proc about to be created, as well […]

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