SQL XML Nested Types

declare @people table (id int, name varchar(50));insert @people values (1, ‘John’);insert @people values (2, ‘Mary’);insert @people values (3, ‘Pat’);declare @cars table (id int, model varchar(50), person_id int);insert @cars values (1, ‘Corvette’, 1);insert @cars values (2, ‘Mustang’, 1);insert @cars values (3, ‘Viper’, 2);select p.id [@id] ,p.name [@name] ,( select model [@model] from @cars where person_id = […]

SQL Table Valued Parameters in .NET

private static SqlDataRecord CreatePersonRecord(int id, string name) { var metaData = new[] { new SqlMetaData(“Id”, SqlDbType.Int), new SqlMetaData(“Name”, SqlDbType.VarChar, 50) }; var record = new SqlDataRecord(metaData); record.SetInt32(0, id); record.SetString(1, name); return record;}private SqlDataRecord[] GetPersonRecords() { retun new[] { CreatePersonRecord(1, “John Doe”), CreatePersonRecord(2, “Jane Doe”) };}using (var conn = new SqlConnection(cs)) { conn.Open(); using (var comm […]

Demonstrating a SQL deadlock with .NET

  var conn1 = new SqlConnection(cs); conn1.Open(); var comm1 = conn1.CreateCommand(); comm1.CommandText = “Proc1;”; new Thread(() => { try { Console.WriteLine(“Executing comm1”); comm1.ExecuteNonQuery(); Console.WriteLine(“comm1 done”); } catch (Exception ex) { Console.WriteLine(“comm1: ” + ex); } }).Start(); var conn2 = new SqlConnection(cs); conn2.Open(); var comm2 = conn2.CreateCommand(); comm2.CommandText = “Proc2”; new Thread(() => { try { […]

SQL Server Identity Info

WITH cte AS ( SELECT s.name + ‘.’ + t.name AS TableName, c.name AS ColumnName FROM sys.tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id INNER JOIN sys.columns c ON c.object_id = t.object_id AND c.is_identity = 1 ) SELECT TableName, ColumnName, IDENT_SEED(TableName) AS Seed, IDENT_INCR(TableName) AS Increment, IDENT_CURRENT(TableName) AS LastIdentity FROM cte order by […]

SQL Server basic maintenance

Refresh Views: create procedure util.RefreshViewsasbegin declare @msg nvarchar(1000); set @msg = ‘————- Executing RefreshViews ————-‘; raiserror(@msg, 0, 1) with nowait; set nocount on; declare @viewNames table ( FullName nvarchar(261) — ‘[‘ + 128 + ‘].[‘ + 128 + ‘]’ ); insert @viewNames (FullName) select ‘[‘ + object_schema_name([object_id]) + ‘].[‘ + [name] + ‘]’ from sys.views; […]

Simple SQL Server backup/restore

— Back Up ———————————————declare @filename nvarchar(255) = ‘C:\backups\’ + format(sysutcdatetime(), ‘yyyyMMddHHmmss’) + ‘.bak’;backup database MyDB to disk = @filename WITH INIT;go– Restore ———————————————– Find the logical names – result will be the MDF and LDF– logical names:restore filelistonly from disk = ‘C:\backups\20201103130743.bak’;gorestore database DifferentDBfrom disk = ‘C:\backups\20201103130743.bak’ with replace,move ‘**MDF Logical Name**’ to ‘c:\data\DifferentDB.mdf’,move ‘**LDF […]

Make database readonly

Pretty straightforward to set a database to readonly mode. If the database is currently in use, it may get complicated, but as long as it’s currently unused, this should work: — Set to readonly:use master;goalter database mydatabase set read_only with no_wait;go– Re-enable writing:use master;goalter database mydatabase set read_write with no_wait;go

Executing dynamic SQL with sp_executesql

SQL Server lets you execute dynamic SQL with the EXEC command. However, if you’re accepting any user input as part of the query, you’ll be subject to SQL injection attacks. The system proc sp_executesql gives you the ability to build a parameterized statement dynamically, and execute it, passing in the parameter values. As long as […]

Check progress of SQL Server restore

From MSSQLTips: SELECT session_id as SPID ,command ,a.text AS Query ,start_time ,percent_complete ,dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_timeFROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) aWHERE r.command in (‘BACKUP DATABASE’,’RESTORE DATABASE’)