SQL This!

Setting up xp_cmdshell securely can be a challenge. Generally only sysadmins can execute the xp_cmdshell. It is actually a very huge security hole if you choose to program your website w/ the sa account as you lay your database in the path for SQL injections. This can be typical if the originating server or application is one that you are taking over. However, there will be instances where you will need to grant mere mortal users access to the xp_cmdshell, this can be done via the sql agent proxy, this gives you the ability to assing a generic nt user the ability to execute a limited range of file object manipulation. The following Microsoft article goes into the details for setting up your SQL Server agent proxy. How to configure a SQL Server Agent proxy account to enable non-system administrators to execute the xp_cmdshell extended stored procedure in SQL Server 2000

When you need to quickly find out diffrences of data/fields between two tables, there is nothing easier than this script: Table Compare

Here is an excellent collection of code snippets and useful libraries for just about any task you're likely to encounter. I've included the link below: My code library (SQL Server T-SQL code samples, snippets, examples, tips, tricks, stored procedures, user defined functions, VB programs): Narayana Vyas Kondreddi's home page

The following SELECT statement displays how long a sql server instance was up for; SQL Server refreshes the tempdb every time it restarts. To determine how long a SQL Server instance has been running, we use the created date of the tempdb. SELECT CAST(DATEDIFF(ss,crdate,GETDATE())/60/60/24 AS VARCHAR(4)) + 'd ' + CAST(DATEDIFF(ss,crdate,GETDATE())/60/60 % 24 AS VARCHAR(4)) + 'hr ' + CAST(DATEDIFF(ss,crdate,GETDATE())/60 % 60 AS VARCHAR(4)) + 'min ' + CAST(DATEDIFF(ss,crdate,GETDATE()) % 60 AS VARCHAR(4)) + 'sec' AS SQL_Server_HAS_BEEN_UP_FOR FROM MASTER.dbo.SYSDATABASES WHERE name = 'TempDB'; -By Billy Pang

I found this article while searching for some "text" in my own stored procedures, (the article link is available at the bottom.) --------------- October 6, 2000 Searching Stored Procedures Source Code By Alan Enderby Want to find that stored procedure that deletes those stock items. This simple (& crude) script will scan syscomments for a given string and display the stored procedure and the section of code. if exists (select * from sysobjects where id = object_id('dbo.sp_findsp') and sysstat & 0xf = 4) drop procedure dbo.sp_findsp GO create proc sp_findsp @s varchar(255) as DECLARE @msg varchar(255) ,@ul varchar(255) select @s='%' + @s + '%' select 'SP Name'=upper(o.name), Seq=colid ,'SP Line'=substring(text,patindex(@s,text)-5, 30) from syscomments c , sysobjects o where o.id=c.id and patindex(@s,text) > 0 order by name SELECT @msg='* Stored procedures containing string "' + @s + ...

Data scrubbing is a fact of database life. Whenever you import and normalize data you do run the risk of duplicate data imports as well. This can be especially true if your source database had few restrictions on preventing duplicate data entry. In one particular table I had many rows with duplicate data down to the entrytime field; everything was identical except for the PKID. I tried the typical Cursor script found in various websites, but that would Yield an estimate of 14hrs while the test server processed all 16k rows! I could not very well do that to the production server, on my quest I tried several while loops and eventually came up with this select statement which for my situation (2 duplicate rows for 1 real row of data) First thing I did was dump out all the duplicate rows PK's and FK's into a temp table called #Dup, I hit upon this select statment and I sucessfully narrowed down 16k rows down to 8k of unique data, and was able to delete the duplicate FK's b...

Every once in a while you need a quick Table w/ a range of dates, that's what this is for... Declare @iLoop AS INT SET @iLoop = 0 DECLARE @rptMonths TABLE(rptMonth DateTime) WHILE @iLoop BEGIN INSERT INTO @rptMonths(rptMonth) VALUES(DATEADD(mm, @iLoop, @StartDate)) SET @iLoop = @iLoop + 1 END