Running a stored procedure from a batch file

Running a stored procedure from a batch file >

Example given is sending an email, put this all on one line....

osql -E -d databasename -S servername -Q " EXEC msdb.dbo.sp_send_dbmail @profile_name='Mail Profile', @recipients='name@domain.com', @body='Task Complete', @subject='Task Complete', @importance='High' "


could use the -o parameter to pipe output to a file if necessary, e.g. -o c:\output.txt

(off topic) Outlook 2007 : Location

Why is this dialog still needed in Outlook 2007?
Surely the area code was only useful when the pc is attached to a dial-up modem?

Enabling DAC via TSQL

sp_configure 'remote admin connections', 1
reconfigure with override

Not rocket science but only realised i needed when a box got maxxed out at 99% CPU and I could not get a connection!

(connect using 'admin:Servername' to a QUERY WINDOW in management studio)

DAC functionality is for emergency use and only works with a TSQL query window i.e for you to run sp_who, kill etc.

If you try and open an object explorer with the ADMIN: prefix, you get >



http://www.mydbalife.com/2009/06/dac-is-friend-that-you-need-to.html

Best Practice : Remove Auto Close from all databases!

Following Buck Woody's post that 'AutoClose Should be Off' , here is a script to ensure that is the case... 

SQL 2000 Version :

DECLARE @databasename varchar(100)
DECLARE @sqlAlterStatement varchar(500)

DECLARE NastyCursorThing CURSOR READ_ONLY FOR
SELECT Name FROM sysdatabases
WHERE DBID > 4 AND DATABASEPROPERTYEX(name, 'IsAutoClose') = 1

OPEN NastyCursorThing
FETCH NEXT FROM NastyCursorThing INTO @databasename
WHILE @@FETCH_STATUS = 0
 BEGIN
 SET @sqlAlterStatement ='ALTER DATABASE [' + @databasename + '] SET AUTO_CLOSE OFF WITH NO_WAIT' + CHAR(10)
 print @sqlAlterStatement
 EXEC(@sqlAlterStatement)
 FETCH NEXT FROM NastyCursorThing INTO @databasename
 END

CLOSE NastyCursorThing
DEALLOCATE NastyCursorThing

SQL 2005/2008 Version :

DECLARE @databasename varchar(100)
DECLARE @sqlAlterStatement varchar(500)

DECLARE NastyCursorThing CURSOR READ_ONLY FOR
SELECT name FROM sys.databases
WHERE database_id >  4 AND DATABASEPROPERTYEX(name, 'IsAutoClose') = 1

OPEN NastyCursorThing
FETCH NEXT FROM NastyCursorThing INTO @databasename
WHILE @@FETCH_STATUS = 0
 BEGIN
 SET @sqlAlterStatement ='ALTER DATABASE [' + @databasename + '] SET AUTO_CLOSE OFF WITH NO_WAIT' + CHAR(10)
 print @sqlAlterStatement
 EXEC(@sqlAlterStatement)
 FETCH NEXT FROM NastyCursorThing INTO @databasename
 END

CLOSE NastyCursorThing
DEALLOCATE NastyCursorThing

Table Compression : List compressed / uncompressed objects

Table compression - List compressed objects (tables & indexes)

SELECT
SCHEMA_NAME(sys.objects.schema_id) AS [SchemaName]
,OBJECT_NAME(sys.objects.object_id) AS [ObjectName]
,[rows]
,[data_compression_desc]
,[index_id]
FROM sys.partitions
INNER JOIN sys.objects
ON sys.partitions.object_id = sys.objects.object_id
WHERE data_compression > 0
AND SCHEMA_NAME(sys.objects.schema_id) <> 'SYS'
ORDER BY SchemaName, ObjectName

Table compression - List uncompressed objects (tables & indexes)
(Note the subtle difference in WHERE clause)

SELECT
SCHEMA_NAME(sys.objects.schema_id) AS [SchemaName]
,OBJECT_NAME(sys.objects.object_id) AS [ObjectName]
,[rows]
,[data_compression_desc]
,[index_id]
FROM sys.partitions
INNER JOIN sys.objects
ON sys.partitions.object_id = sys.objects.object_id
WHERE data_compression = 0
AND SCHEMA_NAME(sys.objects.schema_id) <> 'SYS'
ORDER BY SchemaName, ObjectName

Real World - Twitter DB Size

rather cool...

http://blogs.conchango.com/jamiethomson/archive/2009/06/09/calculating-the-size-of-twitter-s-status-table.aspx

Permissions ! doh!

This one escaped me.

Rather than using system views to generate permissions statements for all objects, you can use the grant statement WITHOUT the ON clause.

Therefore >
GRANT VIEW DEFINITION TO [user]

Rather than repeating this for each procedure >
GRANT VIEW DEFINITION ON sp_name TO [user]

Using it for the EXECUTE permission makes this piece of code redundant.