Fantastic undocumented sp...

exec sp_MSforeachdb 'select top 1 * from syscomments'

Checks each database for the query in the string..

The following is an interesting script to execute sql directly from a text file. The ususal suspects in this scenario usually involve osql or isql, but in this scenario, the user wanted to place a script in a network share, and run it on several databases on the same server. The major component of this script is the dos "type" command which works something like cat in unix, in that it redirects the contents of a text file to screen. The script that follows is a modified version of Chris Hearn's script that appears at www.sqlservercentral.com .

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

create procedure [dbo].[sp_cross_db_script]
@filepath varchar(256),
@filename varchar(1000),
@db_prefix varchar(1000),
@result_table varchar(1000)

as

If (Select object_id('tempdb.dbo.strfile')) > 0
Exec ('drop table tempdb.dbo.strfile')
Exec('Create table tempdb.dbo.strfile(id int identity(1,1) not null, script varchar(256) null)')

--check if result table exists if so delete
/**
The structure of the results table must be the same
structure that is output in script file
It's possible to create several script files as needed
see inserttag in script file and below for string replacement
**/
declare @rtbl varchar(1000)
if ((select count(*) from sysobjects where name = @result_table)!=0)
begin
set @rtbl = 'drop table ' + @result_table
exec(@rtbl)
end
set @rtbl = 'create table ' + @result_table + '('
set @rtbl = @rtbl + ' database_name varchar(1000),
accgrpid int,
accgrpname varchar(1000),
[Associated Standardized Name] varchar(1000),
[NetAccLimit] float)'
exec(@rtbl)

-- declare some variables (10-8k variables = 80,000 total caharaters)
declare @cnt int
declare @loop int
declare @str0 varchar(8000)
declare @str1 varchar(8000)
declare @str2 varchar(8000)
declare @str3 varchar(8000)
declare @str4 varchar(8000)
declare @str5 varchar(8000)
declare @str6 varchar(8000)
declare @str7 varchar(8000)
declare @str8 varchar(8000)
declare @str9 varchar(8000)

-- Initializes variables
set @loop = 1
set @str0 = ''
set @str1 = ''
set @str2 = ''
set @str3 = ''
set @str4 = ''
set @str5 = ''
set @str6 = ''
set @str7 = ''
set @str8 = ''
set @str9 = ''

-- Get the file
Set @filepath = 'type ' + @filepath + @filename
Insert into tempdb.dbo.strfile
Exec master.dbo.xp_cmdshell @filepath

--Clear null lines and embedded 'go's
Delete from tempdb.dbo.strfile where script is null or script = 'go'
update tempdb.dbo.strfile
set script = ' insert [data_quality].[dbo].' + @result_table
where script = '--'
-- Get the max id corresponding to the last executable line
Select @cnt = max(id) from tempdb.dbo.strfile

-- Build a multi-variable string cmd set (7744 is 8000-256)
While @loop <= @cnt Begin If datalength(@str8) >= 7744 and datalength(@str9) < str9 =" @str9" id =" @loop">= 7744 and datalength(@str8) < str8 =" @str8" id =" @loop">= 7744 and datalength(@str7) < str7 =" @str7" id =" @loop">= 7744 and datalength(@str6) < str6 =" @str6" id =" @loop">= 7744 and datalength(@str5) < str5 =" @str5" id =" @loop">= 7744 and datalength(@str4) < str3 =" @str3" id =" @loop">= 7744 and datalength(@str3) < str3 =" @str3" id =" @loop">= 7744 and datalength(@str2) < str2 =" @str2" id =" @loop">= 7744 and datalength(@str1) < str1 =" @str1" id =" @loop" str0 =" @str0" id =" @loop" loop =" min(id)"> @loop
End
--Print (@str0+@str1+@str2+@str3+@str4+@str5+@str6+@str7+@str8+@str9) -- for test
--Select * from tempdb.dbo.strfile -- for test

declare @status table
(
database_name varchar(100) primary key,
status varchar(100)
)
-- Exec the multi-variable string cmd
set @db_prefix = @db_prefix + '%'
declare curs_db cursor for
select distinct name from master..sysdatabases where name like @db_prefix
declare @db varchar(100)
open curs_db
fetch next from curs_db into @db
while (@@fetch_status<>-1)
begin
print 'Processing ' + @db
exec('use '+@db+';'+@str0+@str1+@str2+@str3+@str4+@str5+@str6+@str7+@str8+@str9+';use data_quality;')
fetch next from curs_db into @db
end
close curs_db
deallocate curs_db
Drop table tempdb.dbo.strfile




GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Can't execute SSIS packages from Visual Studio 2005

This one's easy, but I've been asked this enough to post a blog entry. You will not be able to execute an SSIS package from visual studio, unless you add that ssis package to a Solution. For example, it is common for users to use the wizard to create an ad hoc ssis package load data. Users will be able to execute the package by right clicking and executing it, but the execute button will be disabled if opened with Visual Studio.

Excel Solver "Internal or Memory Error"

This isn't a SQL Server or Database related post, but a thorny problem that I haven't seen a solution to nevertheless.

We had a problem where solver running in a macro was returning an Internal or Memory error, the first time the macro was run. When solver was run manually, it worked fine, and moreover the macro ran fine subsequently.

Solution: Add Solver.auto_open to code before Solver is used

An Excel VBA Macro Bug with Charting

We have some code that pulls data from ss2k and creates dynamic excel charts (about 30 or so), based on the pulled data. The developer chose to assign the XValues and Values features Seriescollection(i) to an Array. We were experiencing what seemed to be indeterminate behavior. The macro was blowing up with the error that XValues or Values could not be assigned. It turns out when assigning these properties to Arrays, the the size of individual elements in the Array is very important and MUST be less than 16 chars.

The other option is to dump the data to a hidden excel page, and use Range to assign values to Values and XValues.

The Classic Outer Join Mistake

Select a.col1, b.col1
from table1 a
left outer join table2 b
on a.some_value = b.some_value
where a.some_other_value = b.some_other_value
group by a.col1,b.col1

is not the same as ...

Select a.col1, b.col1
from table1 a
left outer join table2 b
on a.some_value = b.some_value
and a.some_other_value = b.some_other_value
group by a.col1,b.col1

By placing a clause in the where statement, it is no longer optional that the clause be met.

How do I reseed Identity in a Table

Its been asked often enough...that I'll make a note of how to do it here...

DBCC CHECKIDENT ('table_name', RESEED, new_reseed_value)

A regex to be used to validate User and Password fields

A common injection attack is created by the use of dynamic sql in validating user access. Below is a regex that can be used to limit user and password fields to letters,numbers, and underscore only..with length from 4 to 12.

[\d_a-zA-Z]{4,12}

Need more info on Injection attacks?

The above article gives the following pointers to avoid injection attacks:

• Do not use dynamic sql that takes values from user input(user stored procedures and parameters instead.)
• Validate user input
• Give the user the lowest access privileges required
• Store "secrets" securely(encrypt,or hash)
  

Tempdb filling up ?

It's most likely that you are running out of disk space or your growth size parameter is too small.

Here is a great artical on the issue.

You learn something every day...(Moving Schema)

ALTER SCHEMA XXX TRANSFER [DATABASE].[YYY] is pretty cool.

This little command moves table xxx in the current Database to [DATABASE] as table name YYY.

An interesting Query using Systables...

--##1 HAS SOME SUBSET OF THE ATTRIBUTES OF ##2
DECLARE @CHILD_FLAG INT
DECLARE @QUERY NVARCHAR(100)

SELECT * INTO ##2 FROM SYSOBJECTS
DECLARE @MASTER_ATTRIB_LIST TABLE
(
ATTRIB_NAME VARCHAR(1000) UNIQUE
)

DECLARE @CHILD_ATTRIB_LIST TABLE
(
ATTRIB_NAME VARCHAR(1000) UNIQUE
)

INSERT @MASTER_ATTRIB_LIST
SELECT C.NAME FROM
TEMPDB..SYSCOLUMNS C
JOIN TEMPDB..SYSOBJECTS O
ON O.ID = C.ID
AND O.NAME ='##2'

--MAKE ALL FIELDS IN ## NULLABLE
DECLARE @ATTRIB VARCHAR(100)
DECLARE CUR_ATTRIB CURSOR FOR
SELECT ATTRIB_NAME
FROM @MASTER_ATTRIB_LIST
OPEN CUR_ATTRIB
FETCH NEXT FROM CUR_ATTRIB INTO @ATTRIB
WHILE @@FETCH_STATUS = 0
BEGIN
SET @QUERY = 'ALTER TABLE ##2 ALTER COLUMN [' + @ATTRIB + '] VARCHAR(1000) NULL'
EXEC(@QUERY)
FETCH NEXT FROM CUR_ATTRIB INTO @ATTRIB
END
CLOSE CUR_ATTRIB
DEALLOCATE CUR_ATTRIB




SET @QUERY = N'SELECT CACHE,CATEGORY,CRDATE INTO ##1 FROM SYSOBJECTS'

EXECUTE MASTER..SP_EXECUTESQL @QUERY

INSERT @CHILD_ATTRIB_LIST
SELECT C.NAME FROM
TEMPDB..SYSCOLUMNS C
JOIN TEMPDB..SYSOBJECTS O
ON O.ID = C.ID
AND O.NAME ='##1'

SELECT @CHILD_FLAG=COUNT(*) FROM
@CHILD_ATTRIB_LIST WHERE ATTRIB_NAME NOT IN
(
SELECT ATTRIB_NAME FROM @MASTER_ATTRIB_LIST
)

IF @CHILD_FLAG = 0
BEGIN
TRUNCATE TABLE ##2
DECLARE @QUERY2 VARCHAR(8000)
DECLARE CUR_ATTRIB CURSOR FOR
SELECT ATTRIB_NAME
FROM @CHILD_ATTRIB_LIST
SET @QUERY = 'SELECT '
SET @QUERY2 = 'INSERT INTO ##2('
OPEN CUR_ATTRIB
FETCH NEXT FROM CUR_ATTRIB INTO @ATTRIB
WHILE @@FETCH_STATUS = 0
BEGIN
SET @QUERY2 = @QUERY2 + '['+ @ATTRIB + '],'
SET @QUERY = @QUERY + '['+ @ATTRIB + '],'
FETCH NEXT FROM CUR_ATTRIB INTO @ATTRIB
END
SET @QUERY = @QUERY + 'FROM ##1'
SET @QUERY2 = @QUERY2 + '*'
SET @QUERY = REPLACE(@QUERY2,',*',')') + REPLACE(@QUERY,',FROM',' FROM')
EXEC(@QUERY)
CLOSE CUR_ATTRIB
DEALLOCATE CUR_ATTRIB
END
ELSE
BEGIN

PRINT '##1 IS NOT A CHILD OF ##2'
END
SELECT * FROM ##2
DROP TABLE ##1
DROP TABLE ##2

Interesting but Useless Little Query

A student asked me earlier today to craft a query that would generate all binary numbers from 0 to 11111.


This is fairly easy to do using cross join...

DECLARE @BASE INT
SET @BASE = 5
DECLARE @BASE_CNTR INT
SET @BASE_CNTR = 0

DECLARE @QUERY VARCHAR(8000),
@QUERY2 VARCHAR(8000),
@QUERY3 VARCHAR(8000)

--EDIT BASE DIGITS HERE
SET @QUERY = 'DECLARE @DIGITS TABLE
(
REP VARCHAR(5) UNIQUE,
RANKING INT UNIQUE
)

INSERT @DIGITS
SELECT ''0'',0
UNION
SELECT ''1'',1'

SET @QUERY = @QUERY + '
SELECT '
SET @QUERY2 = ' FROM '
SET @QUERY3 = ' ORDER BY '
WHILE @BASE_CNTR < @BASE-1 BEGIN SET @QUERY = @QUERY + 'A'+CONVERT(VARCHAR(5),@BASE_CNTR)+'.REP,' SET @QUERY3 = @QUERY3 + 'A'+CONVERT(VARCHAR(5),@BASE_CNTR)+'.RANKING ASC,' IF @BASE_CNTR = 0 BEGIN SET @QUERY2 = @QUERY2 + ' @DIGITS A' + CONVERT(VARCHAR(5),@BASE_CNTR) + ' ' END ELSE BEGIN SET @QUERY2 = @QUERY2 + ' CROSS JOIN @DIGITS A'+ CONVERT(VARCHAR(5),@BASE_CNTR) + ' ' END SET @BASE_CNTR =@BASE_CNTR + 1 END SET @QUERY3 = @QUERY3 + '*' SET @QUERY = @QUERY + @QUERY2 SET @QUERY = REPLACE(@QUERY,', FROM', ' FROM')+REPLACE(@QUERY3,',*',' ') EXEC(@QUERY)

Let me know if anyone found this useful in anyway.

Simply query to return the nth item of a sorted Result Set

This is usually one of the most basic requests that a Database Developer will receive. For example, if a database keeps track of sales, the developer may be required to craft a query to return only the 10th sale of the day.

For example suppose, a process produces the following as a Result Set that represents the outcome of a horse race:
{Horse Name, Jockey,Company,Final Position}

And you are required to craft a query that returns the result for the Horse that came in 5th.

This could be done as follows:

Select Top 1 [Horse Name], [Jockey],[Company],[Final Position]
from
(Select Top 5 [Horse Name], [Jockey],[Company],[Final Position]
from [Result Set] r
order by [Final Position] asc
)q
order by [Final Position] desc

The Difference between Union and Union All


It' s amazing how many developers simply do not understand the difference between Union and Union All

Both are used to combine 2 result sets, but how they do it is different

• Union ALL Returns ALL data in both sets
• Union returns returns only distinct objects in the both sets

Suppose you have the following tables:


Select Attrib1,Attrib2,Attrib3
from A
Union
Select Attrib1,Attrib2,Attrib3
from B

returns only one record of {a,b,c}







Select Attrib1,Attrib2,Attrib3
from A
Union all
Select Attrib1,Attrib2,Attrib3
from B

returns 2 records of {a,b,c}

This ability creates some interesting results that are useful..
For example if you want to check whether structurally identical tables(A and B) have the same data:

the record count from

Select count(*) from A

and

select count(*)
from
(
Select * from A
union
Select * from B
)Q

must be the same

A Rant to those who LOVE Select into..

Please! For the love of GOD do not use Select x,y,z into in your stored procedures. Yes..I know your only grabbing 10 records..and the performance hit is negligible...but you are LOCKING up System Tables for the entirety of the transaction. While your at it please also DO NOT use Select * in your stored procedures. It's just bad form....its like setting Option Explict Off in VB Code. Table structures inevitably change...and probably far after you've moved onto another project.

Here's someting on the topic from folks far more eloquent than me.

A Short Compilation of T-SQL Datetime Styles:

If you've spent any time working with the datetime objects in T-SQL, you know how, getting the format you want can be difficult. Below I list some of the common Datetime styles that I have encountered.

Print Convert(varchar(50),getdate(),@style) will yield a datetime converted to a string based on the value of @style

Below are my compilation of commonly used @styles:

Checking whether a Dev Database and Production Database are in Sync

Problem: I have a Dev and a Production Environment that needs to be in sync..ie both data and structures need to match.

I know there are several 3rd party tools that do a good job of this, but the following simple script will achieve this quickly.

Place in Your Dev Database..Replace 'Your Production Database' with the name of your Production DB.

create PROCEDURE [DBO].P_COMPARE_DEV_PROD
AS
DECLARE @REPORT TABLE
(
MESSAGE VARCHAR(500)
)


CREATE TABLE #report
(
data varchar(100),
counts FLOAT
)

DECLARE @CNTABLE TABLE
(
C_NAME VARCHAR(100)
)
INSERT @CNTABLE
SELECT 'dtproperties'




DECLARE C CURSOR
FOR
select TABLE_NAME
from INFORMATION_SCHEMA.tables
where table_type = 'BASE TABLE'
AND TABLE_NAME NOT IN (SELECT C_NAME FROM @CNTABLE)
OPEN C
DECLARE @TBL_NAME VARCHAR(100)
Fetch NEXT FROM c INTO @TBL_NAME
While (@@FETCH_STATUS <> -1)
BEGIN
DECLARE @FL VARCHAR(5000)
SET @FL = 'SELECT ''' + @TBL_NAME + ''',COUNT(*) FROM [' + @TBL_NAME + '] '
DECLARE I CURSOR
FOR SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE NUMERIC_SCALE IS NOT NULL
AND COLUMN_NAME NOT LIKE '%ID'
AND TABLE_NAME = @TBL_NAME
DECLARE @COL_NM VARCHAR(100)
OPEN I
Fetch NEXT FROM I INTO @COL_NM
While (@@FETCH_STATUS <> -1)
BEGIN
SET @FL = @FL + ' UNION ALL '
SET @FL = @fl + 'SELECT ''' + @TBL_NAME + '.'+@COL_NM+''',SUM([' + @COL_NM + ']) FROM [' + @TBL_NAME + '] '
Fetch NEXT FROM I INTO @COL_NM
END
CLOSE I
DEALLOCATE I
--PRINT @FL
INSERT #REPORT
EXEC(@FL)
Fetch NEXT FROM c INTO @TBL_NAME
END

CLOSE C
DEALLOCATE C


CREATE TABLE #report2
(
data varchar(100),
counts FLOAT
)

DECLARE C CURSOR
FOR
select TABLE_NAME
from [Your Production Database].INFORMATION_SCHEMA.tables
where table_type = 'BASE TABLE'
AND TABLE_NAME NOT IN (SELECT C_NAME FROM @CNTABLE)
OPEN C
Fetch NEXT FROM c INTO @TBL_NAME
While (@@FETCH_STATUS <> -1)
BEGIN
SET @FL = 'SELECT ''' + @TBL_NAME + ''',COUNT(*) FROM [Your Production Database].[DBO].[' + @TBL_NAME + '] '
DECLARE I CURSOR
FOR SELECT COLUMN_NAME
FROM [Your Production Database].INFORMATION_SCHEMA.COLUMNS
WHERE NUMERIC_SCALE IS NOT NULL
AND COLUMN_NAME NOT LIKE '%ID'
AND TABLE_NAME = @TBL_NAME
OPEN I
Fetch NEXT FROM I INTO @COL_NM
While (@@FETCH_STATUS <> -1)
BEGIN
SET @FL = @FL + ' UNION ALL '
SET @FL = @fl + 'SELECT ''' + @TBL_NAME + '.'+@COL_NM+''',SUM([' + @COL_NM + ']) FROM [Your Production Database].[DBO].[' + @TBL_NAME + '] '
Fetch NEXT FROM I INTO @COL_NM
END
CLOSE I
DEALLOCATE I
--PRINT @FL
INSERT #REPORT2
EXEC(@FL)
Fetch NEXT FROM c INTO @TBL_NAME
END

CLOSE C
DEALLOCATE C


SELECT * INTO #MY_REPORT
FROM
(
SELECT P.DATA [TABLE_NAME], CONVERT(VARCHAR(50),ISNULL(P.COUNTS,0.00)) [PRODUCTION_CNT],
CASE WHEN CONVERT(VARCHAR(15),ISNULL(D.COUNTS,-1)) = '-1'
THEN 'MISSING'
ELSE CONVERT(VARCHAR(15),ISNULL(D.COUNTS,-1))
END AS [DEV_CNT]
FROM #REPORT2 P
LEFT OUTER JOIN #REPORT D
ON D.DATA = P.DATA
UNION ALL
SELECT D.DATA,'MISSING',CONVERT(VARCHAR(50),D.COUNTS)
FROM #REPORT2 D
WHERE D.DATA NOT IN
(
SELECT TABLE_NAME FROM
(
SELECT P.DATA [TABLE_NAME], P.COUNTS [PRODUCTION_CNT],ISNULL(D.COUNTS,-1) [DEV_CNT]
FROM #REPORT2 P
LEFT OUTER JOIN #REPORT D
ON D.DATA = P.DATA
)Q
)
) Q

ORDER BY 1 ASC

--INSERT DATA DESCREPANCIES
INSERT @REPORT
SELECT 'ROW COUNT DESREPANCIES :'
UNION ALL
SELECT '--------------------------------------'
UNION ALL
SELECT TABLE_NAME + ' IN PRODUCTION(NET): ' + PRODUCTION_CNT + ' IN DEV(NET): ' + DEV_CNT
FROM #MY_REPORT
WHERE TABLE_NAME NOT LIKE '%.%'
AND PRODUCTION_CNT != DEV_CNT
UNION ALL
SELECT 'DATA(NUMERIC ONLY) DISCREPANCIES'
UNION ALL
SELECT '--------------------------------------'
UNION ALL
SELECT TABLE_NAME + ' IN PRODUCTION(NET): ' + PRODUCTION_CNT + ' IN DEV(NET): ' + DEV_CNT
FROM #MY_REPORT
WHERE TABLE_NAME LIKE '%.%'
AND PRODUCTION_CNT != DEV_CNT
AND DEV_CNT != 'MISSING'
AND PRODUCTION_CNT != 'MISSING'
UNION ALL
SELECT 'SCHEMA DESCREPANCIES DISCREPANCIES'
UNION ALL
SELECT '--------------------------------------'
UNION ALL
SELECT 'COLUMN: TABLE_NAME '+ TABLE_NAME + ' IS MISSING FROM PRODUCTION, BUT EXISTS IN DEV'
FROM #MY_REPORT
WHERE TABLE_NAME LIKE '%.%'
AND PRODUCTION_CNT != DEV_CNT
AND PRODUCTION_CNT LIKE '%MISSING%'
UNION ALL
SELECT 'COLUMN: TABLE_NAME '+ TABLE_NAME + ' IS MISSING FROM DEV, BUT EXISTS IN PRODUCTION'
FROM #MY_REPORT
WHERE TABLE_NAME LIKE '%.%'
AND PRODUCTION_CNT != DEV_CNT
AND DEV_CNT LIKE '%MISSING%'

SELECT * FROM @REPORT

DROP TABLE #REPORT
DROP TABLE #REPORT2
drop table #MY_REPORT


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Directly accessing Excel Data Using OpenRowSet

The task I have today is as follows:

I need to access data that will be created in Excel by the user, and pull it into SQL Server for use in subsequent queries.

The 2 approaches that come immediately into mind are:

1. Using a DTS package to transfer the data
2. Using OpenRowSet and to access the data directly

My choice today was to use option 2.

Data referenced by openrowset can be accessed just like any table.

The syntax for using openrowset is as follows:

OPENROWSET('provider_name'
{
'datasource';'user_id';'password'
| 'provider_string'
},
{
[catalog.][schema.]object
| 'query'
})

For example, if you have a file located on the sql server named c:\test.xls, and you are selecting data from Sheet1.

select * from openrowset('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=YES;Database=c:\Test.xls', [Sheet1$])

The version of Excel used depends on what version of Excel Test.xls is.

Note: You will receive the following error if you do not have OLE DB Providers for Distributed Queries set up correctly.

Ad hoc access to OLE DB provider 'CustomOLEDBProvider' has been denied. You must access this provider through a linked server.
This link from Microsoft addresses this.

SQL Adventures

Welcome to my Blog about my experiences with SQL Server. My name is Mathew Kulangara, and I am a Database Architect here in Philadelphia, PA. This blog will be dedicated to solutions to problems I encounter in my daily endeavors.