MSSQL Connect to a database from a TSQL script

Step 1:

Switch to SQLCMD mode from the query menu in Management Studio:

Step 2:

Add the connection info using:

:Connect server_name[\instance_name] [-l timeout] [-U user_name [-P password]]
Till Next Time

MSSQL Check if Function or Procedure has changed

I use this query to check if a procedure or function has changed in the past 5 days:
[code]

SELECT * from ods.information_schema.routines i where routine_type in ('PROCEDURE' ,'FUNCTION') AND LAST_ALTERED > getdate() - 5

[/code]

Till Next Time

MSSQL101 enable xp_cmdshell

EXEC master.dbo.sp_configure 'show advanced options', 1

RECONFIGURE

EXEC master.dbo.sp_configure 'xp_cmdshell', 1

RECONFIGURE

MSSQL select value into variable

Declare
@intTableCount int



set @intTableCount = (select COUNT(*) from INFORMATION_SCHEMA.TABLES
where TABLE_SCHEMA = 'dbo'
and TABLE_NAME = 'Table_Name')

select @intTableCount
 
It will trow an error if you omit the ( and ) .
 
Till Next Time

mssql READ UNCOMMITTED

SET TRANSACTION ISOLATION LEVEL


READ UNCOMMITTED

go
 
till next time

MSSQL SQL Server Agent appears with "Agent XPs disabled"

sp_configure 'show advanced options', 1;


GO

RECONFIGURE;

GO

sp_configure 'Agent XPs', 1;

GO

RECONFIGURE
 
Till Next Time

MSSQL startup norecovery

DBCC TRACEON (3607)
go

Till Next Time