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 = […]
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 […]
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 { […]
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 […]
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; […]
— 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 […]
Thank you to this StackOverflow answer for showing how to retrieve PRINT result from SQL Server in C#: conn.InfoMessage += (sender, e) => { // e.Message contains the print output};
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
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 […]
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’)
60 Second Dev 