Category Archives: SQL Toolbox

Database Size Calculations

This post falls firmly in the category of blocking and tackling, but is a question we all have to answer from time to time: “How big is this database?” Two methods: SELECT     DB_NAME(database_id) AS DatabaseName   , Name … Continue reading

Posted in SQL Server Development, SQL Toolbox | Leave a comment

XML Schema Creation in SQL Server 2005

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 … Continue reading

Posted in SQL Toolbox | Tagged , , | Leave a comment

Recursive Directory Scripting

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 … Continue reading

Posted in SQL Server Development, SQL Toolbox | Tagged , , | Leave a comment

Primary, Secondary, Tertiary, umm…?

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 … Continue reading

Posted in SQL Toolbox | Tagged , | Leave a comment

Moving Database Logins

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 … Continue reading

Posted in SQL Toolbox | Tagged , , , | Leave a comment

Enable CLR

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 … Continue reading

Posted in SQL Toolbox | Tagged , | Leave a comment

Reporting Services Scripting for fun and profit

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 … Continue reading

Posted in SQL Toolbox | Leave a comment

Windows Server 2008 & SQL Server 2005

It seems that Windows Server 2008 and/or SQL Server 2005 doesn’t automatically add a firewall exception to the OS for access to the database. As a result, an exception needs to be added, by following instructions found here.

Posted in SQL Toolbox | Tagged , | Leave a comment

First Day of Current Month

SELECT DATEADD( mm, DATEDIFF( mm, 0, GETDATE() ), 0 ) AS FirstOfMonth Since I always manage to get the syntax wrong from memory, above is a SELECT statement that will return the first day of the current month. Edit: As … Continue reading

Posted in SQL Toolbox | Tagged , , | 1 Comment

XML Nodes Query

A reminder of how the nodes query works inside Microsoft SQL Server. Continue reading

Posted in SQL Server Development, SQL Toolbox | Tagged , | Leave a comment