Solutions: 1) To remove the row count (“(139 rows affected)”) you should use SET NOCOUNT ON statement. See ref. 2) To remove column headers you should use -h parameter with value -1. See ref (section Formatting Options). Examples: C:\Users\sqlservr.exe>sqlcmd -S(local)\SQL2012 -d Test -E -h -1 -s, -W -Q “set nocount on; select * from dbo.Account” … Read more
You where right, Albert. I made some tests and found that it’s possible, indeed. The use is the same as in a SELECT statement. For example: UPDATE st SET some_row = A.another_row, some_row2 = A.another_row/2 FROM some_table st CROSS APPLY (SELECT TOP 1 another_row FROM another_table at WHERE at.shared_id=st.shared_id) AS A WHERE …
LOCK_ESCALATION = TABLE is the default behavior in SQL Server 2008 & is the ONLY behaviour in SQL Server 2005. You can safely drop the statement without any change in functionality.
ALTER TABLE YourTable ADD CONSTRAINT UQ_YourTable_ConstraintName UNIQUE(A, B)
Why doesn’t this work? I believe the default behaviour of SQL Server is to release shared locks as soon as they are no longer needed. Your sub-query will result in a short-lived shared (S) lock on the table, which will be released as soon as the sub-query completes. At this point there is nothing to … Read more
This gets all years from 2004 to the present, using a recursive CTE: with yearlist as ( select 2004 as year union all select yl.year + 1 as year from yearlist yl where yl.year + 1 <= YEAR(GetDate()) ) select year from yearlist order by year desc;
To the best of my knowledge, there is none. Writing one is pretty straightforward, though. The following gives you the constant alpha and slope beta for y = Alpha + Beta * x + epsilon: — test data (GroupIDs 1, 2 normal regressions, 3, 4 = no variance) WITH some_table(GroupID, x, y) AS ( SELECT … Read more
You can use sp_lock (and sp_lock2), but in SQL Server 2005 onwards this is being deprecated in favour of querying sys.dm_tran_locks: select object_name(p.object_id) as TableName, resource_type, resource_description from sys.dm_tran_locks l join sys.partitions p on l.resource_associated_entity_id = p.hobt_id
The supplied value must be a constant or a variable. You cannot specify a function name as a parameter value. (from MSDN Executing Stored Procedures). You need to do something like this: declare @EffectiveStartDateText varchar(30) set @EffectiveStartDateText = cast(@EffectiveStartDate as varchar) declare @EffectiveEndDateText varchar(30) set @EffectiveEndDateText = cast(@EffectiveEndDate as varchar) RAISERROR ( ‘SPName: InsertUpdateLiquidityDateRule: Start … Read more
This is what I’ve learned so far from my research. .NET sends in connection settings that are not the same as what you get when you log in to management studio. Here is what you see if you sniff the connection with Sql Profiler: — network protocol: TCP/IP set quoted_identifier off set arithabort off set … Read more