SQL This!

Even tho this is a Sql Blog, the following actually is related... Many developers will often use Microsoft Access as a viable Front End to Sql Server instead of using VB or even VB dotNet. This is often a quick way to leverage rapid development. However if you've ever used Access you'll quickly find that it's not the friendly little IDE that it claims to be. When you attempt to leverage it's power as bound data application, you'll often find yourself wrestling within the clutches of it's Before Update, Current and After Update Events. I can say you haven't known how to really hate, until you've battled it out with these events. In a simple application it all works fine, in a typical Access MDB format, binding data to forms is common and relatively easy and painless. Try it with an ADP (Access Data Project). The Before Update event can easily be avoided and replaced with a database trigger. Trigger's to Sql Server are what Before Update

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 <= DateDiff(mm, @StartDate, @EndDate) BEGIN INSERT INTO @rptMonths(rptMonth) VALUES(DATEADD(mm, @iLoop, @StartDate)) SET @iLoop = @iLoop + 1 END

This has come up before in my line of work, to get a total workday count but with out the weekends. (DATEDIFF(dd, StartDate, EndDate) + 1) -(DATEDIFF(wk, StartDate, EndDate) * 2) -(CASE WHEN DATENAME(dw, StartDate) = 'Sunday' THEN 1 ELSE 0 END) -(CASE WHEN DATENAME(dw, EndDate) = 'Saturday' THEN 1 ELSE 0 END) AS TotalWorkDays

T his article was written back before I was looking into Sql Server 2005. The underlying idea is the same, in order to keep your database running healthy you will need to maintain and administer the underlying architecture. In this short but subtle refresh I've separated the 2000 concepts and implementations from those used today in Sql Server 2005. If you have comments or corrections do feel free to email me or leave a comment. SQL Server 2000 ===================================================== It is imperative you maintenance your Database. One way to check up on the indexes per table is to run the DBCC SHOWCONTIG command as DBCC SHOWCONTIG ('tbl_YourTableName') with fast,ALL_INDEXES You'll end up with a very similar display like the following... DBCC SHOWCONTIG scanning 'tbl_YourTableName' table... Table: 'tbl_YourTableName' (1113627606); index ID: 1, database ID: 8 TABLE level scan performed. - Pages Scanned................................: 1

The following script works well on Sql Server 2000 (I have not tested it w/ any other versions). The purpose is to gain as much information back on how your sql server is configured (general settings memory etc.) print 'Free Space' print '----------' exec master.dbo.xp_fixeddrives print 'Memory' print '------' exec master.dbo.xp_msver print 'Database Configuration' print '----------------------' exec master.dbo.sp_configure print'DB Size' print'-------' exec master.dbo.sp_databases print 'Linked Servers' print '--------------' exec master.dbo.sp_helplinkedsrvlogin print 'Windows info' print '------------' exec master.dbo.sp_server_info

Now how about you want to select against that Table you now get when you push a list of values through it? this Second part is very usefull tho you will have to edit the table name within when you change source tables... possibly a sproc???? Declare @CursorLess Table (PkID INT, ItemList INT) --Non Cursor solution DECLARE @NC Table (PkID INT, ItemList VARCHAR(2000)) --Cursor like holding bin DECLARE @PkID AS INT, @ListOfItems As VARCHAR(2000) -- IDs pointing back to original table INSERT INTO @NC (PkID, ListOfItems) --Change the below select to the actual PKID and List of Items, and Table Select PkID, ListOfItems From UserTable --Populate Holding bin ---------------------------------------------------- WHILE EXISTS(SELECT * FROM @NC) --Check that data exists BEGIN SELECT TOP 1 @PkID = PkID, @ListOfItems = ListOfItems FROM @NC --Set Variables INSERT INTO @Cursorless (PkID, ListOfItems) SELECT @PkID, Field1 FROM dbo.udfListToTable

E very now and again, a developer will be faced with the task of referincing a List of id's. It's typically irresistable to want to push all these IDs into a single field. If you're searching then you may end up considering a cursor... for that I decided to write the following function... it's like other Tsql functions that push a list to a table, but this is my take on it... I release the code here with out warranty. CREATE FUNCTION udfListToTable (@HList VarChar(2000), @Delimiter CHAR(1)) RETURNS @ListTable TABLE (Field1 VARCHAR(6)) AS BEGIN --By: Francisco Tapia --Date: 2/1/2005 --Purpose: To convert a Comma delimited text to a Temp Variable table To help avoid dynamic sql -- Instead you can join the temp table or use it in your where clause if a field is IN the subquery DECLARE @FieldText as VarChar(6) IF RIGHT(RTRIM(@HLIST),1) <>@Delimiter SET @HList = @HList + @Delimiter WHILE CHARINDEX(@Delimiter, @HList) > 0 BEGIN