I grok, therefore I am

DQS Services in Denali

Matt — Sun, 30 Oct 2011 18:56:09 +0000

Interesting article about the upcoming Data Quality Services feature in Denali. It’s worth mentioning, as it’s very similar to the Data Quality Control system that I developed (with a little inspiration from my friend) for our current data mart.

In short, what I do is run a series of data quality tests on the data every time it loads. These can then be flagged for follow-up by end-users, thus increasing the overall quality of the data in the data mart.

While the feature-set is not nearly as complete as the Microsoft version, it’s fun seeing something you worked on reflected in Sql Server.

XML Schema Creation in SQL Server 2005

Matt — Wed, 07 Sep 2011 14:18:19 +0000

Here’s an interesting approach for creating a quick and dirty schema to get you started with XML.

DECLARE @schema xml
SET @schema = (SELECT * FROM schema.TABLE FOR XML AUTO, ELEMENTS,
XMLSCHEMA('SchemaName'))
SELECT @schema

I don’t have the source, but I have where I found it.

T-SQL Alter Statement Quirk

Matt — Mon, 25 Apr 2011 21:12:40 +0000

To answer a recent question to the PSSUG user group e-mail, I put together a post instead of trying to answer via e-mail.

First, the question:

I have a staging table.

Stage_id identity
Stage_File_Name nvarchar(100)
AllData nvarchar(max)

I load a pipe delimited file into it. Placing the entire record into the nvarchar(max). This way I can load anything into it.

Instead of haveing a separate staging table for each differant file.

I am trying to Add the columns I need. Splitting out the file. Then moving it into the normal table for that file.

I have it working in a batch but cannot make it a stored procedure.

Alter table staging
Add colmumn A char(10),
B INT
C DATETIME
ETC…….

UPDATE staging
SET A = SS(ALLDATA,1,4)
B
C
ETC….

INSERT NORMAL TABLE (A,B,C,ETC…)
SELECT STAging

Alter table staging
drop column a,b,c,etc….

this works well until I try to make it so I can run it via stored procedure. Eventually I would like to create a job agent.

Any Ideas how I can make this work. Or am I going to have to write it to create a new staging table each time and drop it.

Here’s my (edited) answer, updated with working t-sql below.

…Assuming I understood your question correctly, I can give you a solution, but not an explanation. Perhaps someone else can chime in on the why.

If you wrap the alter statements (to add the columns) and update statements (on the new columns) in separate strings, and execute using the EXEC command, you can wrap it in a stored procedure. …

Here is the setup, with the script I whipped up based on the question.

CREATE DATABASE Testing_AlterStatementFun
GO

USE Testing_AlterStatementFun
GO

IF OBJECT_ID('Foo') IS NOT NULL
DROP TABLE Foo

CREATE TABLE Foo
(
PK INT IDENTITY(1,1)
, FK INT NOT NULL
, BigData VARCHAR(MAX) NOT NULL
)

INSERT INTO Foo ( FK, BigData ) VALUES ( 1, 'abcdefghijklmnopqrstuvwxyz' )

ALTER TABLE Foo
ADD First5 VARCHAR(5)
, Last5 VARCHAR(5)

UPDATE Foo
SET First5 = LEFT( BigData, 5 )

UPDATE Foo
SET Last5 = RIGHT( BigData, 5 )

The above code works as a script. Next, the part that doesn’t work. First, I recreate the table, since if you create Foo_Fail without doing that, it will see that table Foo has the referenced columns.

--Recreate and Load the table.
IF OBJECT_ID('Foo') IS NOT NULL
DROP TABLE Foo

CREATE TABLE Foo
(
PK INT IDENTITY(1,1)
, FK INT NOT NULL
, BigData VARCHAR(MAX) NOT NULL
)

INSERT INTO Foo ( FK, BigData ) VALUES ( 1, 'abcdefghijklmnopqrstuvwxyz' )

-- Can't create it, since First5 doesn't exist.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = object_id(N'[dbo].[Foo_Fail]') AND OBJECTPROPERTY(Object_id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[Foo_Fail]
GO

CREATE PROCEDURE [dbo].[Foo_Fail]
AS

ALTER TABLE Foo
ADD First5 VARCHAR(5)
, Last5 VARCHAR(5)

UPDATE Foo
SET First5 = LEFT( BigData, 5 )

UPDATE Foo
SET Last5 = RIGHT( BigData, 5 )

GO

As advertised, this fails.

However, this does function (it’s ugly, but it works).

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = object_id(N'[dbo].[Foo_Pass]') AND OBJECTPROPERTY(Object_id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[Foo_Pass]
GO

CREATE PROCEDURE [dbo].[Foo_Pass]
AS

EXEC( '
ALTER TABLE Foo
ADD First5 VARCHAR(5)
, Last5 VARCHAR(5)' )

EXEC( '
UPDATE Foo
SET First5 = LEFT( BigData, 5 )' )

EXEC( '
UPDATE Foo
SET Last5 = RIGHT( BigData, 5 )' )

GO

Finally, here’s the script working.

IF OBJECT_ID('Foo') IS NOT NULL
DROP TABLE Foo

CREATE TABLE Foo
(
PK INT IDENTITY(1,1)
, FK INT NOT NULL
, BigData VARCHAR(MAX) NOT NULL
)

INSERT INTO Foo ( FK, BigData ) VALUES ( 1, 'abcdefghijklmnopqrstuvwxyz' )

EXEC [dbo].[Foo_Pass]

And finally, cleanup your mess.

USE Master
GO
DROP DATABASE Testing_AlterStatementFun
GO

Recursive Directory Scripting

Matt — Mon, 28 Mar 2011 12:14:06 +0000

Inside source control, I have scripts arranged as follows:

Drive:/Base/Database/
Drive:/Base/Database/Functions
Drive:/Base/Database/Proc
Drive:/Base/Database/Tables
Etc..

Each script stored can be run multiple times with no ill effect. For example, a table generation script first checks to see that it exists before running a create.

I was troubleshooting an issue where it seemed the source and the database were out of sync, so I needed to push everything I had to a test database to continue my troubleshooting, and I didn’t want to run each script by hand.

I knew about sqlcmd, and wanted to quickly script out each file to run on the target database. I also needed a list of all the files in each of those layers of directories.

In the windows environment, it was surprisingly difficult to accomplish a recursive search for files to run, so I had to do some Google sleuthing to figure it out.

There were too many sources to link them all properly, but this was patched together from all over the web. If there’s a better method, I’d love to hear about it.

Here are the steps I followed:

- Open up a command prompt window
- Navigate to the root directory where my scripts are stored (in this example: Drive:/Base/Database/)
- run: for /r %d in (*.sql) do echo %d > sqlcmd_files.bat
(alternatively change > to >> to append to sqlcmd_files.bat instead of overwriting it)
- Edit sqlcmd_files.bat w/ your favorite text editor
- Find and Replace the Drive:\ with sqlcmd -Sserver_name -ddatabase_name -iDrive:\
(this should change your lines from Drive:/Base/Database/Proc/Filename.sql to sqlcmd -Sserver_name -ddatabase_name -iDrive:\Base/Database/Proc/Filename.sql )
- Save and execute.

Known limitations:
- Doesn’t take into account dependencies. If the tables happen to script out after a stored proc that uses that table sqlcmd will throw an error. In my case, I just ran the sqlcmd_files.bat twice. The first time laid down anything without dependencies, and the second time cleaned everything up.
- Specifically looks for files with the extension “.sql”. If you use something else, adjust accordingly.
- All of my paths have no spaces in them. If you do, consider wrapping in quotes around your paths.

Primary, Secondary, Tertiary, umm…?

Matt — Tue, 22 Mar 2011 22:03:36 +0000

I recently found myself asking the question posed in the title – what comes after primary, secondary, tertiary…?

I had a rough idea, based on my drum line days, and the various types of Tenor drums.

However, this article lays it out nice and neat.
Source

1st = primary, 2nd = secondary, 3rd = tertiary, 4th = quaternary, 5th = quinary, 6th = senary, 7th = septenary, 8th = octonary, 9th = nonary, 10th = denary, 12th = duodenary and 20th = vigenary.

Personally, anything after you hit the 5th level, you might consider another pattern, since you’ll lose your audience, unless they all happen to speak Latin.

Moving Database Logins

Matt — Mon, 21 Mar 2011 22:45:50 +0000

Recently we had a database issue, that necessitated us moving logins.

Here’s an article I used from Microsoft to help script the logins. The KB is specifically for 2005 -> 2008, but it will also get you login’s out of a stricken server.

Source

It worked really well, and I wanted to record it here for reference.

USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue VARCHAR (514) OUTPUT
AS
DECLARE @charvalue VARCHAR (514)
DECLARE @i INT
DECLARE @LENGTH INT
DECLARE @hexstring CHAR(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @LENGTH = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @LENGTH)
BEGIN
DECLARE @tempint INT
DECLARE @firstint INT
DECLARE @secondint INT
SELECT @tempint = CONVERT(INT, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END

SELECT @hexvalue = @charvalue
GO

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @TYPE VARCHAR (1)
DECLARE @hasaccess INT
DECLARE @denylogin INT
DECLARE @is_disabled INT
DECLARE @PWD_varbinary varbinary (256)
DECLARE @PWD_string VARCHAR (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string VARCHAR (514)
DECLARE @tmpstr VARCHAR (1024)
DECLARE @is_policy_checked VARCHAR (3)
DECLARE @is_expiration_checked VARCHAR (3)

DECLARE @defaultdb sysname

IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR

SELECT p.sid, p.name, p.TYPE, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.TYPE IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR

SELECT p.sid, p.name, p.TYPE, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.TYPE IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @TYPE, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (VARCHAR, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@TYPE IN ( 'G', 'U'))
BEGIN -- NT authenticated account/group

SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
END
ELSE BEGIN -- SQL Server authentication
-- obtain password and sid
SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

-- obtain password policy state
SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

IF ( @is_policy_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
END
IF ( @is_expiration_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
END
END
IF (@denylogin = 1)
BEGIN -- login is denied access
SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
END
ELSE IF (@hasaccess = 0)
BEGIN -- login exists but does not have access
SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
END
IF (@is_disabled = 1)
BEGIN -- login is disabled
SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
END
PRINT @tmpstr
END

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @TYPE, @is_disabled, @defaultdb, @hasaccess, @denylogin
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO

Then

EXEC sp_help_revlogin

RePost: Database development mistakes made by application developers

Matt — Mon, 13 Dec 2010 12:18:07 +0000

Link

Personally, I think this should also include database developers operating on too little sleep and/or too close of a deadline.

Enable CLR

Matt — Sun, 31 Oct 2010 01:21:22 +0000

This is just one of those things that I always forget how to do.

EXEC sp_CONFIGURE 'SHOW ADVANCED OPTIONS' , '1' -- Let me change it
GO
RECONFIGURE
GO
EXEC sp_CONFIGURE 'CLR ENABLED' , '1'
GO
RECONFIGURE
GO
EXEC sp_CONFIGURE 'SHOW ADVANCED OPTIONS' , '0' -- Return to status quo
GO
RECONFIGURE
GO

T-SQL Creativity

Matt — Fri, 17 Sep 2010 22:17:11 +0000

This article has little to do with useful t-sql applications – since to me, minesweeper is meant for a mouse. However, it really demonstrates a fun use of t-sql.
Minesweeper in T-SQL

Reporting Services Scripting for fun and profit

Matt — Wed, 11 Aug 2010 23:53:43 +0000

After spending too long on Google, and finally getting an appropriate answer from a colleague, I thought it wise to record this information in case someone finds themselves in a similar situation.

This stackoverflow sums up my issue nicely: “I need to create a repeatable process for deploying SQL Server Reporting Services reports. I am not in favor of using Visual Studio and or Business Development Studio to do this. The rs.exe method of scripting deployments also seems rather clunky. Does anyone have a very elegant way that they have been able to deploy reports. The key here is that I want the process to be completely automated.”

The current accepted answer is nice, but it goes a little too far for my taste. However, the real gem is in this link for Reporting Service Scripter. It’s a delightfully simple utility that does as advertised. I can then take the code it generates and check it into our source control system. Our deployment software just needed to be pointed at the Windows Command Script file generated, and we were off and running.

Hopefully this will help out anyone else in a similar situation.