Friday, September 02, 2005

Access/SQL:How to satisfy the underlying record

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 events are to Access. If your changes can be made server side, it's often the best plan of attack. But what if in your trigger you update other tables? if you do you'll most certainly be faced with the dreaded "The data was added to the database but the data won't be displayed in the form because it doesn't satisfy the criteria in the underlying record source." followed by quickly seeing the data you just keyed into your Access Form disappear. Seems like it'd be easier to wrestle w/ the Before Update and Current Events. The practicallity of doing a trigger is that you don't need to make another round trip to the database. For Access the above error occurs because Access uses Sql's @@Identity to synchronize with the Front end. It's best outlined in KB article:KB275090.

My workaround? I created a temp table and filled it with the target table's PKID

CREATE TABLE #reIdentify (JUNKID int IDENTITY (1, 1) NOT NULL)
SET IDENTITY_INSERT #reIdentify ON
INSERT INTO #reIdentify(JunkID)
SELECT MyTablePKID FROM INSERTED
SET IDENTITY_INSERT #reIdentify OFF

Now I can have my Before_Update event occuring at the server level saving me client processing and battling yet another Access Bound Event.

Tuesday, August 02, 2005

How to setup xp_cmdshell

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

Friday, July 29, 2005

Table Compare

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

Friday, June 03, 2005

What's up doc?

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

Tuesday, May 31, 2005

Searching Stored Procedures Source Code

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 + '=' +
convert(varchar(8),@@rowcount) + ' *'
SELECT @ul=replicate('*',datalength(@msg))
Print ' '
PRINT @ul
PRINT @msg
Print @ul

GO
-----------
Searching Stored Procedures Source Code

Thursday, May 12, 2005

the Doppelganger

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 based on the PKid.


SELECT A.PKID
FROM #Dup AS A
INNER JOIN (SELECT FKID FROM #Dup GROUP BY FKID) B
ON A.FKID = B.FKID
AND A.PKID IN (SELECT TOP 1 PKID FROM #DUP C WHERE A.FKID = C.FKID)

The Select Statement finds one of the Duplicate tables, you can use this statement to delete only these rows leaving a unique record behind.

Monday, April 18, 2005

Month Variable Table

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

Wednesday, April 13, 2005

Get Workday count with out weekends

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

Thursday, April 07, 2005

Defrag Those Indexes - Maintenance

This 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................................: 1680
- Extent Switches..............................: 217
- Scan Density [Best Count:Actual Count].......: 96.33% [210:218]
- Logical Scan Fragmentation ..................: 0.18%
DBCC SHOWCONTIG scanning 'tbl_YourTableName' table...
Table: 'tbl_YourTableName' (1113627606); index ID: 2, database ID: 8
LEAF level scan performed.
- Pages Scanned................................: 480
- Extent Switches..............................: 64
- Scan Density [Best Count:Actual Count].......: 92.31% [60:65]
- Logical Scan Fragmentation ..................: 0.83%
DBCC SHOWCONTIG scanning 'tbl_YourTableName' table...
Table: 'tbl_YourTableName' (1113627606); index ID: 5, database ID: 8
LEAF level scan performed.
- Pages Scanned................................: 696
- Extent Switches..............................: 95
- Scan Density [Best Count:Actual Count].......: 90.63% [87:96]
- Logical Scan Fragmentation ..................: 0.72%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

What you're really looking for is the Scan fragmentation to be as low as possible. I've also read on other sites that you want the Scan Desity to be as close to each other as possible like for example 87:96 is fairly close and gives you a density over 90%.


From that you can easily run a defrag on each index as follows

DBCC INDEXDEFRAG (8, 1113627606, 5)
DBCC INDEXDEFRAG (8, 1113627606, 2)
DBCC INDEXDEFRAG (8, 1113627606, 1)

More Information can be obtained via: Sql Server Performance\dbcc commands
And sql-server-performance.com/rebuilding_indexes
and here, SQL Server Index Fragmentation and Its Resolution

If you just wish to defrangment the entire database this little script will put you on your way: (from the link above sql server performance site)
SET DatabaseName --Enter the name of the database you want to reindex

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor

------END SCRIPT
SQL Server 2005 ===================================================
Fastforward to today,
IndexDefrag still works in 2005, but if you read around the 'net you'll find many more references to choose to rebuild your indexes via the ALTER INDEX command, which is new for SQL Server 2005.

Before you go out and begin de-fragmenting tables like crazy, it's best to have a plan, finding out how much fragmentation is in your table in 2005 is easier too with the newer function that provides this information. below I've expanded on what you can find around the internet, the below script will allow you to identify rows over 100k and with more than 10% fragmentation, I also handled the commonly seen error for databases that are of the standard dictionary order (80), and the common "Error near '(' ".

/script/
SET NOCOUNT ON
DECLARE @db_id SMALLINT
SET @db_id = DB_ID()

SELECT
OBJECT_NAME(i.object_id) AS TableName,
i.name AS TableIndexName,
phystat.avg_fragmentation_in_percent, rows
FROM sys.dm_db_index_physical_stats(@db_id, NULL, NULL, NULL, 'DETAILED') phystat
inner JOIN sys.indexes i WITH(NOLOCK)
ON i.object_id = phystat.object_id
AND i.index_id = phystat.index_id
INNER JOIN sys.partitions p WITH(NOLOCK)
ON p.OBJECT_ID = i.object_id
WHERE phystat.avg_fragmentation_in_percent > 10 AND ROWS > 100000
/end script/

More information on this system function is availble here:
http://technet.microsoft.com/en-us/library/ms188917.aspx
This link will even give you a cool little script you can use to find out what tables in your database are fragmented over 40%, which is very useful if you are planning a weekend of maintenance.
http://www.sql-server-performance.com/articles/per/detect_fragmentation_sql2000_sql2005_p1.aspx


Now that you have the needed information on how fragmented the indexes are you can begin defragmenting using the Alter Index command

ALTER INDEX ALL ON TableName REBUILD

What is especially cool about 2005 is that you can choose to throttle the amount of CPUs you use for the defragging process, with the MAXDOP option note, with the following command you can restrict the number of processors down to 2 CPUs:

ALTER INDEX ALL ON TableName REBUILD WITH(MAXDOP=2)

You can also choose to rebuild your INDEX and continue to make it available for your users as it defrags, I suspect this has performance implications but none the less the command would be:

ALTER INDEX ALL ON TableName REBUILD WITH(MAXDOP=2, ONLINE=ON)

More information on the ALTER INDEX command is available on the MSDN here:
http://msdn2.microsoft.com/en-us/library/ms188388.aspx

Happy Defragging!

Wednesday, February 16, 2005

How is your Server Configured

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

Monday, February 07, 2005

Join against Lists in a Column

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(@ListOfItems, ';') -- Populate Temp Table
DELETE From @NC where PkID = @PkID --Clear Variables
END

SELECT * FROM @Cursorless -- Use the final Variable table to join approriately....

Sunday, February 06, 2005

List to Table

Every 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
IF CHARINDEX(@Delimiter, @HList) > 0
BEGIN
SELECT @FieldText =LEFT(@HList, CHARINDEX(@Delimiter, @HList)-1)
END
ELSE
BEGIN
SELECT @FieldText = RTRIM(LTRIM(@HList))
END
--Insert into Variable Table
INSERT INTO @ListTable(Field1)
SELECT RTRIM(LTRIM(@FieldText))
--Remove Item from list
SELECT @HList = RIGHT(RTRIM(@HList), LEN(RTRIM(@HList)) - CHARINDEX(@Delimiter, @HList))
END
RETURN
END