Tuesday, 19 April 2011

TSQL : SQL Server Version detection

As part of rollout scripts I am automating the detection of of the SQL Server version.
The current use for this is to decide whether indexes should carry included columns (a 2005+ feature), but I can think of many more.
IF (CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), CHARINDEX('.', CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), 1) - 1) AS INTEGER)) >=9
BEGIN
PRINT 'SQL 2005 or greater detected'
END
Other properties such as Edition could be used to determine whether edition specific features e.g Page Compression, Resource Governor (SQL 2008 Enterprise)
SELECT
 CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), CHARINDEX('.', CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), 1) - 1) AS INTEGER) AS MajorVersion
,SERVERPROPERTY('ProductVersion') AS ProductVersion
,CASE LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), CHARINDEX('.', CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), 1) - 1)
 WHEN 8 THEN 'SQL 2000'
 WHEN 9 THEN 'SQL 2005'
 WHEN 10 THEN 'SQL 2008'
END AS ProductVersion
,SERVERPROPERTY('Edition') AS Edition
,SERVERPROPERTY('ProductLevel') AS ProductLevel

Monday, 18 April 2011

Add Administrator from the command line

(This could be fatal is xp_cmdshell has been left open...)

C:\Users\->net user admintest p4ssw0rd /add
The command completed successfully.

C:\Users\->net localgroup administrators admintest /add
The command completed successfully.

Thursday, 14 April 2011

Tsql : ROW_NUMBER, RANK, DENSE_RANK and PARTITION BY

ROW_NUMBER, RANK, DENSE_RANK and PARTITION BY

A script to demo all of the above, as a reminder of Windowing functions...
SELECT
  name
 ,type_desc
 ,COUNT(*) OVER(PARTITION BY NULL) AS CountAllRecords
 ,ROW_NUMBER() OVER (ORDER BY name) AS RowNumberByName
 ,RANK()  OVER (ORDER BY type_desc) AS RankbyType
 ,DENSE_RANK()  OVER (ORDER BY type_desc) AS DenseRankbyType
 ,RANK() OVER (ORDER BY LEFT(Name,1)) AS RankByFirstCharacterofName
 ,DENSE_RANK() OVER (ORDER BY LEFT(Name,1)) AS DenseRankByFirstCharacterofName
 ,ROW_NUMBER() OVER (PARTITION BY LEFT(Name,1) ORDER BY LEFT(Name,1)) AS RowNumberPartitionedbyLeft1
FROM sys.objects
ORDER BY name

Database Maintenance 2

Database Management

The items I listed back in the Database Maintenance Checklist are pretty standard. All necessary to keep a server healthy. How can we improve our efficiency and take the DBA role to the next level however?

Automate test restores

Restoring data can be scheduled by script and SQL Agent Jobs easily. Providing capacity is not an issue restores can be scheduled to a test environment out of core hours.

Configure Alerts

SQL can send emails on job failures and a wide range of configurable events warning of systems events. In addition to this I personally like to configure emails for Blocking Queries, Long Running queries and Server Restarts/Failovers.

Monitor Trends

Monitor the daily growth of backups, databases, data files or even tables. This can be done into a tools database and stored on server with a relatively small overhead.
Leaving this running will provide you with accurate figures for capacity planning.

Indexes 

Periodically review database indexes. Identifying and removing duplicated and unused indexes will save space and reduce the overhead of maintaining them. Similarly running scripts or the Database Tuning Advisor may help identify new indexes that are needed.

Index maintenance jobs may hide an inappropriate index e.g one that quickly fragments. Recording the index fragmentation level before running index maintenance can help identify this. Another use for my tools database, a collection of scripts I have on each machine.




Wednesday, 13 April 2011

osql & sqlcmd

Osql has long been deprecated, but it still exists on those pesky sql 2000 servers...


osql

sqlcmd

osql usage :

osql –e –q”SELECT column FROM table”

osql -Uusername -Ppassword -Sservername -iC:\script.sql -oc:\output.txt

sqlcmd usage :

sqlcmd –e –q”SELECT column FROM table”

sqlcmd -Uusername -Ppassword -Sservername -iC:\script.sql -oc:\output.txt

parameters

-e Use Trusted Connection (windows Authentication)
-u User name
-p Password
-s Server name
-i Script name
-q Query
-o Output file
-n remove numbering


parameters

as osql parameters , with new additions including -
-b batch abort
-f Codepage
-u Unicode output
-v Pass Variables



To Get values out from osql , use the EXIT command like this -
C:\>osql -e -n -q "EXIT (SELECT 47)"

47

Or in a batch file like this...

osql -e -n -q "EXIT (SELECT COUNT(*) FROM sysobjects)"
SET SqlObjectCount=%ErrorLevel%
ECHO %SqlObjectCount%

To return values out from sqlcmd , use the EXIT command like this -
C:\>sqlcmd -Usa -Ppassword -q"EXIT(SELECT 101)"

101

Or in a batch file like this...

sqlcmd -Usa -Ppassword -q "EXIT (SELECT COUNT(*) FROM sysobjects)"
SET SqlObjectCount=%ErrorLevel%
ECHO %SqlObjectCount%


To Pass parameters in to OSQL, build a sql script file on the fly, and load it ...

SET currentobject=table1
ECHO EXIT(SET NOCOUNT ON;SELECT COUNT(*) from sysobjects WHERE [name] = '%currentobject%') >%currentobject%_exists.sql
OSQL -Usa -P%password% -S %server% -d %db% -f 65001 -i %currentobject%_exists.sql


sqlcmd supports variables via the –v switch

pass.sql

SET NOCOUNT ON;
EXIT(SELECT 'filename passed is $(file)')

pass.bat

@echo off
for %%f in (*.bat) do (
sqlcmd -v file="%%f" -i "pass.sql"
)
)

Results -

B:\test2>pass


----------------------------
filename passed is check.bat


--------------------------------
filename passed is check_old.bat


---------------------------
filename passed is pass.bat


------------------------------
filename passed is rollout.bat



MSDN : osql
MSDN : sqlcmd
Using the SQLCMD command line utility for SQL SERVER
Mohamad Shehadeh's Blog : SQLCMD VS OSQL

Tuesday, 12 April 2011

(the deprecated) TEXT datatype

Argh! Have come up against the deprecated text datatype in a database i’m reporting on.
It’s a migrated SQL 2000 product in 8.0 Compatibility mode!

I cannot use equals (=) in a WHERE clause against the text column

So when I want to write
SELECT * FROM ProductUpdates WHERE Build = ‘9.71’
I get ...

Msg 402, Level 16, State 1, Line 1
The data types text and varchar are incompatible in the equal to operator.

I have to use PATINDEX to get around it, like this –
SELECT * FROM ProductUpdates  WHERE PATINDEX('9.71',Build) > 0
Or CAST the column like this
SELECT * FROM ProductUpdates  WHERE CAST(Build AS VARCHAR(MAX)) = ‘9.71’

Although this second approach would ruin the sargability of the query, i.e. the ability to use an index seek on the column.

Link : Using equal operator in transact-SQL for ntext datatype column

Monday, 11 April 2011

Codeplex : ExportSqlScript

I'm currently looking a replacing a product scripting system. It is command line driven (not a bad thing), but suffers from being overly complex. To apply changes, they need to be scripted to 3 places which is far from ideal. I need to create database build scripts that will support multiple versions, service packs and patches for a core product.

Redgate's SQL Compare is the obvious answer to see the initial state of play, but I cannot roll that out to the deployment team and tell them to get on with it. As much as I'd like to.

For this reason, some googling had led me to the perfect starting block, a codeplex project called 'ExportSqlScript'.

http://exportsqlscript.codeplex.com/

It is command line driven, and runs like this

ExportSQLScript.exe localhost adventureworks /ot:Tree /of:CreationOrder.txt /od:"b:\test2\ExportSqlScript" /ssq

This creates a folder for each object type (screenshot below) as well as CreationOrder.txt




CreationOrder.txt can be used to recreate the database as it takes into account object dependencies i.e. it creates views and constraints AFTER dependent tables and a functions / stored procedure after related tables / views. In theory it should match Jamie Thomon's Dependency Order script, that I always keep handy.

You can run scripts in order like this -
for /F "" %i in (CreationOrder.txt) do osql -Uuser -Ppassword -S localhost -d databasename -f 65001 -i %i

(Change %i for %%i inside a batch file)