You can use a CTE: ;WITH RowNbrs AS ( SELECT ID , ROW_NUMBER() OVER (ORDER BY ID) AS RowNbr FROM MyTab WHERE a = b ) UPDATE t SET t.MyNo = 123 + r.RowNbr FROM MyTab t JOIN RowNbrs r ON t.ID = r.ID;
It looks like they removed it from that location. You can still do it by: Right clicking on the DB Click on tasks Click on generate scripts Go through the wizard and select your tables On the options page click the Advanced button Change the “Types of data to script” option, from the default “Schema … Read more
The best way is to simply reset the password by connecting with a domain/local admin (so you may need help from your system administrators), but this only works if SQL Server was set up to allow local admins (these are now left off the default admin group during setup). If you can’t use this or … Read more
This is well known and documented, see Rename a Computer that Hosts a Stand-Alone Instance of SQL Server: When you change the name of the computer that is running SQL Server, the new name is recognized during SQL Server startup. You do not have to run Setup again to reset the computer name. Instead, use … Read more
I tried using a CLR function and it was more than twice as fast as BCP. Here’s my code. Original Method: SET @bcpCommand = ‘bcp “SELECT blobcolumn FROM blobtable WHERE ID = ‘ + CAST(@FileID AS VARCHAR(20)) + ‘” queryout “‘ + @FileName + ‘” -T -c’ EXEC master..xp_cmdshell @bcpCommand CLR Method: declare @file varbinary(max) … Read more
Using ASCII(RIGHT(ProductAlternateKey, 1)) you can see that the right most character in row 2 is a Line Feed or Ascii Character 10. This can not be removed using the standard LTrim RTrim functions. You could however use (REPLACE(ProductAlternateKey, CHAR(10), ”) You may also want to account for carriage returns and tabs. These three (Line feeds, … Read more
Since SQL Server Version 2012 you can use: SELECT format(getdate(),’yyyyMMddHHmmssffff’)
I can think of two obvious ways but both of them involve processing the TVP again. The first is simply to change the DELETE condition WHEN NOT MATCHED BY SOURCE AND target.AccountId IN(SELECT AccountId FROM @Items) THEN DELETE; The second is to use a CTE to restrict the target WITH cte as ( SELECT ItemId, … Read more
The MOD keyword only exists in the DAX language (tabular dimensional queries), not TSQL Use % instead. Ref: Modulo
Try this one – SET NOCOUNT ON; IF OBJECT_ID (N’tempdb.dbo.#temp’) IS NOT NULL DROP TABLE #temp CREATE TABLE #temp ( [COUNT] INT , DB VARCHAR(50) ) DECLARE @TableName NVARCHAR(50) SELECT @TableName=”[dbo].[CUSTOMERS]” DECLARE @SQL NVARCHAR(MAX) SELECT @SQL = STUFF(( SELECT CHAR(13) + ‘SELECT ‘ + QUOTENAME(name, ””) + ‘, COUNT(1) FROM ‘ + QUOTENAME(name) + ‘.’ … Read more