Category Archives: MS SQL Server

Microsoft SQL Server 2014: Change Default Data, Log, and Backup Directories via T-SQL or PowerShell

If you need to change the default data, log, and/or backup directories on a SQL 2014 MS SQL Server default instance after installation, you can use these three SQL commands: EXEC xp_instance_regwrite N’HKEY_LOCAL_MACHINE’ , N’Software\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQLServer’ , N’DefaultData’ , … Continue reading

Posted in MS SQL Server, PowerShell, T-SQL | Leave a comment

T-SQL: Delete Rows in a Batch Loop

Here’s a simple pattern to perform an action in a batch loop with T-SQL. DECLARE @RowCount INT = 1; — Initialize to a value >0 to begin the loop   SET ROWCOUNT 20000; — The number of rows to delete … Continue reading

Posted in MS SQL Server, T-SQL | Leave a comment

T-SQL: Describe a temp table with sp_help

If you are looking to verify the structure of a temp table you have created within a SQL query batch, you can use sp_help to describe the table. SELECT Top 1 * INTO #MyTempTable FROM dbo.SomeTable   EXEC tempdb.dbo.sp_help N’#MyTempTable’ … Continue reading

Posted in MS SQL Server, T-SQL | Leave a comment

T-SQL: Try/Catch Pattern

Here’s a simple try/catch pattern for a SQL Server query to help roll back a transaction when something goes awry: BEGIN Tran;   BEGIN Try — do stuff END Try BEGIN Catch IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; Throw END … Continue reading

Posted in MS SQL Server, T-SQL | Leave a comment

VSDBCMD: Create a .dbschema File from an Existing Database

If you have a Visual Studio Database project and want to reference an existing database, you can generate a .dbschema file from the command line. Here’s an example: [cc] vsdbcmd /a:Import /cs:”Server=(local);Database=MyDatabase;Trusted_Connection=True;” /dsp:SQL /ModelFile:MyDatabase.dbschema [/cc] Hope this helps!

Posted in MS SQL Server, Visual Studio | 1 Comment

HowTo: Execute a SQL Agent Job via a Stored Procedure

Here’s a way your application can kick off a long running SQL process that is wrapped up in a SQL Agent Job. Running a job via a stored procedure this way would allow your application not to block for the … Continue reading

Posted in MS SQL Server, T-SQL | Leave a comment