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.

Bookmark : Schemabinding

If you are going to oncur the row by row processing overhead of a scalar user defined function (UDF) , you may as well investigate SCHEMABINDING...

MSDN : Improving query plans with the SCHEMABINDING option on T-SQL UDFs

Group Policy to Enable Instant File Initialization

If you run SQL Server using Network Service accounts, you'll need the following location in Group Policy to allow Instant File Initialization.

Grant 'Perform Volume Maintainence Tasks' to the SQL Server service account as shown below -








http://www.sqlskills.com/blogs/Kimberly/post/Instant-Initialization-What-Why-and-How.aspx

Returning OUTPUT values from stored procedures

Because i'm forever revisiting old code for the simplest of functions,
here is my memory jogger for retrieving OUTPUT values from stored procs!

Fig 1 : Really easy stored procedure looking at an INFORMATION SCHEMA view.
Specify the OUTPUT cluase in the parameter definition.

CREATE PROCEDURE dbo.RoutineCount (
    @RoutineType VARCHAR(20),
    @RoutineCount INT OUTPUT )
AS
BEGIN
SELECT @RoutineCount = COUNT(*) 
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_TYPE = @RoutineType
END
GO

Fig 2 : Returning the value from the procedure.
Declare a variable to pass it into and specify the OUTPUT clause.

DECLARE @CountOfRoutines INT

EXEC dbo.RoutineCount 
    @RoutineType = 'procedure',
    @RoutineCount = @CountOfRoutines OUTPUT

SELECT @CountOfRoutines AS RoutineCount

NB : You need to specify OUTPUT both in the sp definition and the sql that calls the proc.

Login Errors - State Codes

State  /   Error description
1     Account is locked out
2     User id is not valid
5     User id is not valid
6     Attempt to use a Windows login name with SQL Authentication
7     The login being used is disabled
8     Incorrect password (Password mismatch)
9     Invalid password
11-12     Login valid but server access failed
13     SQL Server service paused
16     Login valid, but not permissioned to use the target database
18     Password expired (Change your password!)
23     The server is in the process of shutting down. No new connections are allowed.
27     Initial database could not be found
38     Login valid but database unavailable (or login not permissioned)

Off-Topic : Page Speed

It has been a while since I did any real web development, i'm impressed with this Firebug Add-in!

http://code.google.com/speed/page-speed/

Table Compression Cheat

Creating a table with compression...

CREATE TABLE dbo.testtable (testcolumn1 char(1000), testcolumn2 int) WITH (DATA_COMPRESSION = PAGE);

Changing a table to take advantage of compression functionality...

ALTER TABLE dbo.testtable REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE); 

Want to implement table compression on all tables in a sql 2008 db?
Here's dynamic sql to build those commands...

page compression...

SELECT 'ALTER TABLE [' + SCHEMA_NAME(schema_id) + '].[' + NAME + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);' FROM sys.objects where TYPE = 'u'

row compression...

SELECT 'ALTER TABLE [' + SCHEMA_NAME(schema_id) + '].[' + NAME + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = ROW);' FROM sys.objects where TYPE = 'u'

and to remove it again...

SELECT 'ALTER TABLE [' + SCHEMA_NAME(schema_id) + '].[' + NAME + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);' FROM sys.objects where TYPE = 'u'

NLA Support in Windows XP

Could be useful.

How to get XP clients to support NLA (vista/2008/win7's network level authentication).

http://www.destinationip.com/index.php/site/xp_nla_support/

Clock Display in Windows 2008 RDP sessions

How to Force display of clock in Windows 2008 RDP sessions.

It took a long time to find this, so posting here (not taking credit for) >

Use Group Policy Preferences to push a registry key out to achieve this.

1. Start the Group Policy Management Console (GPMC) and go to User Configuration - Preferences - Windows Settings - Registry
2. right-click - New - Registry Wizard - select Local Computer - select the registry key
3. HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\StuckRects2
4. select the "Settings" value and enter the proper data, using the values below for the different properties of the taskbar
5. click "Finish"
6. select the "Settings" value - right-click - properties - Common tab
7. select the option "Run in logged-on user's security context (user policy option)

The "Settings" value, of this key will be a 40 pair hex value , e.g >

28 00 00 00 ff ff ff ff 02 00 00 00 03 00 00 00 6d 00 00 00 20 00 00 00 00 00 00 00 e0 03 00 00 00 05 00 00 00 04 00 00

The nineth pair of digits determines the Taskbar properties. Possible values are:

Always on top = 0x02
Auto hide = 0x01
Show small icons in Start menu = 0x04
Hide clock = 0x08

Combine the properties you want and set the byte. For example:

Always on top + Show small icons + Show clock = 06
Always on top + Show small icons + Hide clock = 0e

(I used 06 as the ninth hex pair)

Link : http://ts.veranoest.net/ts_faq_configuration.htm

Configure Internet Time

being in the UK, i'm chosing uk.pool.ntp.org to sync from.

here are the commands I ran on my domain controller...


w32tm /config /manualpeerlist:"uk.pool.ntp.org,0x8" /syncfromflags:MANUAL
net stop w32time
net start w32time
w32tm /config /update

and i put this in a batch file to run on new vms...

gpupdate /force
w32tm /config /syncfromflags:manual
w32tm /resync /rediscover
pause

Group Policy : NTP Settings (Time Server)

SQL 2008 : Table Compression

A quick demo of SQL 2008's table compression.
Table compression can be performed by compressing on a ROW or a PAGE basis.

Step 1 : Decide which method to use by estimating the savings compression will bring.
Their is a handy procedure to do this for you.

-- Estimate for row based compression 

EXEC sp_estimate_data_compression_savings 
       @schema_name =  'dbo'  
     , @object_name =  'DummyTestTable' 
     , @index_id =  null 
     , @partition_number =  null 
     , @data_compression =  'ROW'; 
go

-- Estimate for page based compression

EXEC sp_estimate_data_compression_savings 
       @schema_name =  'dbo'  
     , @object_name =  'DummyTestTable' 
     , @index_id =  null 
     , @partition_number =  null 
     , @data_compression =  'PAGE'; 
go

The results


So, the original table size is 66224960 KB

Size when compressed using ROW based compression 54011056 KB (size 82%, saving 18%)
Size when compressed using PAGE based compression 36455952 KB (size 55%, saving 45%)

Step 2 : Compress the table!

ALTER TABLE dbo.DummyTestTable REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = PAGE);

excellent table compression article :
http://www.devx.com/SQL_Server/Article/41171/1763

SQL 2005 / 2008 : Table Partitioning - Run through

Example of table partitioning in SQL Server (versions 2005 & 2008)

1) Create a database! >

USE [master]
GO

CREATE DATABASE [Data_Source] ON  PRIMARY 
( NAME = N'Data_Source', FILENAME = N'D:\Data\Data_Source.mdf' , SIZE = 204800KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB ), 

LOG ON 
( NAME = N'Data_Source_log', FILENAME = N'E:\logs\Data_Source_log.ldf' , SIZE = 1024000KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

2) Add Filegroups to the database >

USE [master]
GO
ALTER DATABASE [Data_Source] ADD FILEGROUP [2004]
ALTER DATABASE [Data_Source] ADD FILEGROUP [2005]
ALTER DATABASE [Data_Source] ADD FILEGROUP [2006]
ALTER DATABASE [Data_Source] ADD FILEGROUP [2007]
ALTER DATABASE [Data_Source] ADD FILEGROUP [2008]
ALTER DATABASE [Data_Source] ADD FILEGROUP [2009]
ALTER DATABASE [Data_Source] ADD FILEGROUP [2010]
ALTER DATABASE [Data_Source] ADD FILEGROUP [2011]
ALTER DATABASE [Data_Source] ADD FILEGROUP [2012]
ALTER DATABASE [Data_Source] ADD FILEGROUP [2013]
ALTER DATABASE [Data_Source] ADD FILEGROUP [2014]
ALTER DATABASE [Data_Source] ADD FILEGROUP [2015]

3) Add data files into the filegroups >

ALTER DATABASE [Data_Source] ADD FILE ( NAME = N'Data_2004Data', FILENAME = N'D:\Data\Data_Source_2004.mdf' , SIZE = 512KB , FILEGROWTH = 51200KB ) TO FILEGROUP [2004]
GO
ALTER DATABASE [Data_Source] ADD FILE ( NAME = N'Data_2005Data', FILENAME = N'D:\Data\Data_Source_2005.mdf' , SIZE = 512KB , FILEGROWTH = 51200KB ) TO FILEGROUP [2005]
GO
ALTER DATABASE [Data_Source] ADD FILE ( NAME = N'Data_2006Data', FILENAME = N'D:\Data\Data_Source_2006.mdf' , SIZE = 51200KB , FILEGROWTH = 51200KB ) TO FILEGROUP [2006]
GO
ALTER DATABASE [Data_Source] ADD FILE ( NAME = N'Data_2007Data', FILENAME = N'D:\Data\Data_Source_2007.mdf' , SIZE = 51200KB , FILEGROWTH = 51200KB ) TO FILEGROUP [2007]
GO
ALTER DATABASE [Data_Source] ADD FILE ( NAME = N'Data_2008Data', FILENAME = N'D:\Data\Data_Source_2008.mdf' , SIZE = 51200KB , FILEGROWTH = 51200KB ) TO FILEGROUP [2008]
GO
ALTER DATABASE [Data_Source] ADD FILE ( NAME = N'Data_2009Data', FILENAME = N'D:\Data\Data_Source_2009.mdf' , SIZE = 51200KB , FILEGROWTH = 51200KB ) TO FILEGROUP [2009]
GO
ALTER DATABASE [Data_Source] ADD FILE ( NAME = N'Data_2010Data', FILENAME = N'D:\Data\Data_Source_2010.mdf' , SIZE = 512KB , FILEGROWTH = 51200KB ) TO FILEGROUP [2010]
GO
ALTER DATABASE [Data_Source] ADD FILE ( NAME = N'Data_2011Data', FILENAME = N'D:\Data\Data_Source_2011.mdf' , SIZE = 512KB , FILEGROWTH = 51200KB ) TO FILEGROUP [2011]
GO
ALTER DATABASE [Data_Source] ADD FILE ( NAME = N'Data_2012Data', FILENAME = N'D:\Data\Data_Source_2012.mdf' , SIZE = 512KB , FILEGROWTH = 51200KB ) TO FILEGROUP [2012]
GO
ALTER DATABASE [Data_Source] ADD FILE ( NAME = N'Data_2013Data', FILENAME = N'D:\Data\Data_Source_2013.mdf' , SIZE = 512KB , FILEGROWTH = 51200KB ) TO FILEGROUP [2013]
GO
ALTER DATABASE [Data_Source] ADD FILE ( NAME = N'Data_2014Data', FILENAME = N'D:\Data\Data_Source_2014.mdf' , SIZE = 512KB , FILEGROWTH = 51200KB ) TO FILEGROUP [2014]
GO
ALTER DATABASE [Data_Source] ADD FILE ( NAME = N'Data_2015Data', FILENAME = N'D:\Data\Data_Source_2015.mdf' , SIZE = 512KB , FILEGROWTH = 51200KB ) TO FILEGROUP [2015]
GO

4) Create a partition function.
This is used to define data ranges that partitioning needs to take account of >

CREATE PARTITION FUNCTION [PFunction_YearDateRange](datetime) AS
RANGE LEFT FOR VALUES ( N'20041231 23:59:59.997'  --between January 1st and Dec 31st 2005
, N'20051231 23:59:59.997'  --before January 1st and Dec 31st 2006
, N'20061231 23:59:59.997'  --before January 1st and Dec 31st 2007
, N'20071231 23:59:59.997'  --before January 1st and Dec 31st 2008
, N'20081231 23:59:59.997'  --before January 1st and Dec 31st 2009
, N'20091231 23:59:59.997'  --before January 1st and Dec 31st 2010
, N'20101231 23:59:59.997'  --before January 1st and Dec 31st 2011
, N'20111231 23:59:59.997'  --before January 1st and Dec 31st 2012
, N'20121231 23:59:59.997'  --before January 1st and Dec 31st 2013
, N'20131231 23:59:59.997'  --before January 1st and Dec 31st 2014
, N'20141231 23:59:59.997'  --after January 1st 2015
)
GO

5) Create a partition scheme.
This is used to associate the ranges defined in the partition function with scheme names. It determines which filegroup incoming data is placed in.

CREATE PARTITION SCHEME PScheme_YearDateRange
AS
PARTITION PFunction_YearDateRange TO
([2004],[2005],[2006],[2007],[2008],[2009],[2010],[2011],[2012],[2013],[2014],[2015])
GO

6) Create our new table to take advantage of partitioning.
Note : The table is created on the function which determines the filegroup, rather than on a filegroup itself. >

CREATE TABLE [dbo].[PartitionTestTable] (
[UniqueId] [bigint] IDENTITY(1,1) NOT NULL,
[EventDateTime] [datetime] NOT NULL,
[ArbitraryCode1] [varchar](5) NULL,
[ArbitraryCode2] [varchar](5) NULL,
[ArbitraryCode3] [varchar](5) NULL,
[UnicodeText1] [nvarchar](255) NULL,
[UnicodeText2] [nvarchar](255) NULL,
[UnicodeText3] [nvarchar](255) NULL,
[Value] [money] NULL,
[Status] [bit] NOT NULL)
ON PScheme_YearDateRange(EventDateTime)
GO

7) Populate the table with varied data to fulfill the requirements of testing the partitioning. In this case, use data in different years.

You're done !

If you're interested, this system query will tell you the row count in each partition.

SELECT *
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='PartitionTestTable';

Similarly, when sufficient data is inserted, the size of the data files will differ in the operating system.

Partitioning gives you the option to place different filegroups in different physical locations e.g. faster disks for newer, more frequently accessed data.
It also makes archiving old data much easier.

links :
http://blog.sqlauthority.com/2008/01/25/sql-server-2005-database-table-partitioning-tutorial-how-to-horizontal-partition-database-table/
http://weblogs.sqlteam.com/dang/archive/2008/12/11/Partition-Details-and-Row-Counts.aspx

Windows 7 : UAC

Look how easy it is to control UAC in WIndows 7... :)

Oh, and Windows Paint which I used to take the screenshot has had a long overdue overhaul too...

Table Statistics query

When were my table statistics last updated?

SQL 2005+ Script

-- sql 2005+
SELECT DISTINCT
  OBJECT_NAME(object_id) AS TableName
, [name] AS IndexName
, STATS_DATE(object_id, index_id) AS TableStatsUpdated
FROM sys.indexes WITH (NOLOCK)
ORDER BY STATS_DATE(object_id, index_id) DESC;

SQL 2000 Script

-- sql 2000
SELECT DISTINCT
  OBJECT_NAME(id) AS TableName
, [name] AS IndexName
, STATS_DATE(id, indid) AS TableStatsUpdated
FROM sysindexes WITH (NOLOCK)
ORDER BY STATS_DATE(id, indid) DESC;