x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

SQL Server

Next Available Date

Saw a post the other day on SQLTeam.   The OP Asked: "If I supply a Date, How can I get the next available date, that is not a holiday, and is not on the weekend."   I came up with this hack...please let me know if anyone has come with a different version   Thanks   Brett   CREATE TABLE Holidays (Holiday_Dt datetime, HoldayName varchar(255)) GO INSERT INTO Holidays(Holiday_Dt, HoldayName) SELECT '1/1/2010', 'New Years Day' UNION ALL SELECT '1/18/2010', 'Martin Luther King' UNION ALL SELECT '2/15/2010', 'President''s Day' UNION ALL SELECT '5/31/2010', 'Memorial Day' UNION ALL SELECT '7/5/2010', 'July 4th Holiday' UNION ALL SELECT '9/6/2010', 'Labor Day' UNION ALL SELECT '11/25/2010', 'Thanksgiving' UNION ALL SELECT '11/26/2010', 'Black Friday' UNION...

posted @ Wednesday, June 30, 2010 2:43 PM | Feedback (3) | Filed Under [ SQL Server ]

Parsing String in SQL Server Hack

Alrighty Then How to Parse a string in SQl...not always easy...I imagine it really shouldn't have been difficult for M$ to make some T-SQL extensions, but they didn't (or at least up to 2k5...gotta look more into 2k8) Even in REXX we had some nice features...like MASK, WORD, WORDS, etc. So SQL Server Database devloper slaves are usually left with a myriad of slick hacks..REVERSE, CHARINDEX (to the nth degree), and ultimatley looping with logic in a User Defined Function (UDF) In any case, we had an OP in this thread, "Parse String, Extract Multiple Parts of a Sting" ask how to parse out...

posted @ Friday, November 20, 2009 12:45 PM | Feedback (2) | Filed Under [ SQL Server ]

Replace a Character Anywhere in a DB

Ran across this thread http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=135575 And figured I'd post this.  The code below will generate update statement for all tables that have a char data type.  It will then replace a quote (") with an empty sting Guess you could automate this, but this is simple enough.  Just cut and paste the results and execute that.  I guess you could go on and schedule it to run nightly to do a clean up. Hope you have a backup BEFORE you run the result set CREATE TABLE #myTemp99 (n int IDENTITY(1,1), TABLE_NAME varchar(256), COLUMN_NAME varchar(256)) GO INSERT INTO #myTemp99(TABLE_NAME, COLUMN_NAME)      SELECT TABLE_NAME, COLUMN_NAME         FROM INFORMATION_SCHEMA.Columns      ...

posted @ Monday, November 09, 2009 3:03 PM | Feedback (0) | Filed Under [ SQL Server ]

How can I update Multiple Tables at once

"CAN'T BE DONE" -- Crazy boy Well at first glance, that may very well be the answer, as in this thread: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=100207 Now, this might not be what the OP is looking for, but if you employ a partitioned view, then yes, it's doable cut and paste the sample code to see it in action USE Northwind GO CREATE TABLE myTable99 (Col1 int PRIMARY KEY CHECK (Col1 BETWEEN 1  AND 10), Col2 varchar(50)) CREATE TABLE myTable98 (Col1 int PRIMARY KEY CHECK (Col1 BETWEEN 11 AND 20), Col2 varchar(50)) GO INSERT INTO myTable99(Col1, Col2) SELECT 1, 'x' UNION ALL SELECT 2, 'y' UNION ALL SELECT 3, 'z' INSERT INTO myTable98(Col1, Col2) SELECT 11, 'x' UNION ALL SELECT...

posted @ Thursday, April 03, 2008 9:49 AM | Feedback (4) | Filed Under [ SQL Server ]

Because you're mine, I walk the line

....ummm, well, that "line" would be walking through a line in a family tree.  I saw a post the other the day that asked, if I know a relative somewhere in a families "lineage", how can I find the entire family tree from top to bottom. Well here's a hack to do that...don't ask for effecienciey.  This for SQL Server 2000.  I have to determine if CTE's in 2k5 can come up with a better solution, but here it is for now. CREATE TABLE Parent (    ID_PK int IDENTITY(1,1)  , [Name] varchar(20)  , PhoneNum varchar(20)  , Address varchar(30)) CREATE TABLE Child (    ID_PK int  , ParentID_FK int) GO INSERT...

posted @ Thursday, July 26, 2007 3:45 PM | Feedback (4) | Filed Under [ SQL Server ]

Alias to be or not to be

  EDIT:  This is bizzare..I ran multiple table joins, and reran it ovr and over, and the times are always different and one time one is faster than the othe and other times it's the other wway around. We were having a discussion over at SQLTeam on whether to use full table name aliases or short aliases to label columns.  It always seemed to be a mattter of preference and debate on how self docuenting the code.  For m I will always use short aliases and make sure I lable every column, even if it's unique, just so when I come back...

posted @ Tuesday, March 27, 2007 2:59 PM | Feedback (2) | Filed Under [ SQL Server ]

SET Versus SELECT (Or, Who Really Cares Anyway)

EDIT:  As Tara points out: Vyas did this test quite some time ago: http://vyaskn.tripod.com/differences_between_set_and_select.htm Either I never read it, or I forgot I read it.  Well hopefully I pulled some different points together differently here than Vyas did, and at the very least, I hope I made my feeling clear about a program that has to loop over 2 million times.  Thanks for the heads up Tara...                                      OK, this always comes up from time to time, and it always seem that people are both sides of the fence. "SET is faster because....", "No, SELECT is faster because".  Well there should be no debate...

posted @ Monday, February 12, 2007 2:36 PM | Feedback (3) | Filed Under [ SQL Server ]

Hierarchies with a twist...rocks, no salt

EDIT:  The script has been repaired and paired down. Basically this "solution" assigns a derived value to each entity...which I called codex for lack of better term.  Each Child inherits their Parents Codex node signature.  In the code below I show how to add a new position in the tree and how to move an entire branch...not sure what else you would want to do, but if you let me know, I'll take a crack at.  It also shows how to "mine" different element of meta data about the tree. This is a link that launched this discussion Ever wanted to do set...

posted @ Monday, November 13, 2006 4:36 PM | Feedback (7) | Filed Under [ SQL Server ]

How do I find all the tables referenced by Stored Procedures or Functions

Like this SELECT o.name, t.TABLE_NAME, c.text   FROM syscomments c   JOIN sysobjects o     ON c.id = o.id   JOIN INFORMATION_SCHEMA.Tables t     ON  c.text LIKE '%'+t.TABLE_NAME+'%'  

posted @ Friday, September 22, 2006 12:32 PM | Feedback (6) | Filed Under [ SQL Server ]

Stored Procedure Logging

Every so often, someone asks, "How do I know who executed a SQL Statement against my database". Well you can either have SQL Profiler running all the time (which can be very expensive), or you can use Lumingent's Log Explorer. I have taken a different tack lately. Any Access to a database I am supporting will be done ONLY Through stored procedures.  OK, that's not "lately", but the part I've added is that the developers MUST call the sproc below.  What this does is to log every stored procedure call.  I now have statistics as to what's being called when, and how long...

posted @ Thursday, September 21, 2006 9:38 AM | Feedback (4) | Filed Under [ SQL Server ]

Finding Trade and Receipt fallout

A director came to me asking if a set based approach could be used to find the fallout for trades and receipts.  Even though they handed the work off already.  The developer  used COBOL to compare the 2 file and did "spin-up" processing to match a trade to a receipt.  Only probalem is that is totally arbitrary since the "key" was basicall generic and did not exactly marry the receipt to the trade.  So their "fallout" was based on LILO.  They considered everything else as a match. What they really needed to do was identify target population that have "fallout" and...

posted @ Wednesday, September 20, 2006 1:31 PM | Feedback (4) | Filed Under [ SQL Server ]

How do I track data changes in a database

This question comes up quit often.  For example in this thread from dbForums SQL Server forum they are looking to do just that. The simplest answer is to create a history for every table, then create a trigger for each that will move the entire row of the before image information into history when a DELETE or UPDATE Modification occurs.  You could type all of this out manually if you'd like.  But I hate typing.  So the code below will generate all of the tables and the triggers for you.  Now I just have to write one of these things for DB2. USE...

posted @ Thursday, August 10, 2006 1:59 PM | Feedback (4) | Filed Under [ SQL Server Code Generataion ]

T-SQL HTML? Parser

EDIT:  Ok, so as is pointed out in the comments below SQL Server comes with a very powerful XML Parser already OPENXML and sp_xml_preparedocument.  I posted a code snipet in the comments I got from BOL so you can see how it works.  While my routine operaes in a different manner, it seems to have been an exercise in futility...but it was fun writing it...annd hey maybe it will come in hany.  Who knows. A poster over at SQLTeam asked a question on how to “read“ xml data from a string.  Now I had always toyed with the idea of parsing XML...

posted @ Thursday, July 27, 2006 2:29 PM | Feedback (6) | Filed Under [ SQL Server ]

How do I create a file with a header and trailer?

...was recently asked at SQLTeam http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=68461 This is what I would do USE NorthwindGO CREATE VIEW EXPORT_ORDERSASSELECT 1 AS ROW_ORDER,   'HEADER '  + CONVERT(char(25),GetDate()) AS Data_LineUNION ALLSELECT 2 AS ROW_ORDER,   COALESCE(CONVERT(char(15),OrderID),'') + COALESCE(CustomerID,'') + COALESCE(CONVERT(char(15),EmployeeID),'') + COALESCE(CONVERT(char(25),OrderDate),'') + COALESCE(CONVERT(char(25),RequiredDate),'') + COALESCE(CONVERT(char(25),ShippedDate),'') + COALESCE(CONVERT(char(15),ShipVia),'') + COALESCE(CONVERT(char(15),Freight),'') + COALESCE(CONVERT(char(80),ShipName),'') + COALESCE(CONVERT(char(120),ShipAddress),'') + COALESCE(CONVERT(char(30),ShipCity),'') + COALESCE(CONVERT(char(30),ShipRegion),'') + COALESCE(CONVERT(char(20),ShipPostalCode),'') + COALESCE(CONVERT(char(30),ShipCountry),'') AS Data_Line  FROM OrdersUNION ALLSELECT 3 AS ROW_ORDER,  'TRAILER '  + CONVERT(char(25),GetDate()) + CONVERT(char(15),COUNT(*)) AS Data_Line  FROM OrdersGO SELECT Data_Line FROM EXPORT_ORDERS ORDER BY ROW_ORDER EXEC master..xp_cmdshell 'bcp Northwind.dbo.EXPORT_ORDERS out C:\Orders.txt -S<servername> -c -T'GO DROP VIEW EXPORT_ORDERSGO

posted @ Thursday, June 29, 2006 1:03 PM | Feedback (2) | Filed Under [ SQL Server ]

OSQL Doesn't work like Query Analyzer ( Or Query Analyser for that matter...)

What?  Huh?  This can't be...can it?  Well this person seemed to think so: http://www.dbforums.com/showthread.php?t=1219537 I didn't think that this was possible...and, well, it isn't...so I am posting this for future reference....and to start blogging all things SQL again USE NorthwindGO CREATE TABLE myTable99(Col1 int)GO INSERT INTO myTable99(Col1)SELECT 1    UNION ALLSELECT 2    UNION ALLSELECT 3    UNION ALLSELECT null UNION ALLSELECT null UNION ALLSELECT nullGO CREATE PROC mySproc99 @Rows int OUTPUT AS SELECT @Rows = COUNT(*) FROM myTable99 WHERE Col1 IS NULLGO DECLARE @Rows intEXEC mySproc99 @Rows OUTPUTSELECT @Rows DECLARE @cmd varchar(8000), @sql varchar(8000)SELECT @sql = 'DECLARE @Rows int EXEC mySproc99 @Rows OUTPUT SELECT @Rows'SELECT @cmd = 'osql -U sa -P...

posted @ Monday, June 26, 2006 12:57 PM | Feedback (6) | Filed Under [ SQL Server ]

To the chagrin of Jeff or How to format data in T-SQL

EDIT:  Seems to be a lot of formatting question.  Thought I'd turn this into a collection of formatting tips.  I'll list an “Index“ here which will reference the code and “solutions“ 1. 10/10/2005 The thread that started this one,  Basically How to summarize data and prevent “duplicate“ entries from appearing in the list 2. 10/11/2005 The very next day we got a question on basically how to create mailing labels ______________________ 1. Jeff Smith rightly points out in this thread that the poster is actually asking presentation layer issues.  Now that may very well be true, but I've seen some developers take a very long time,...

posted @ Monday, October 10, 2005 5:05 PM | Feedback (2) | Filed Under [ SQL Server ]

How can I automate the copying of a table?

As much as I try to disuade people from building processes that might come back to haunt them later, this one always seems to come up.  So I gave in, I figure it might be useful from an admin perspective at some point, so here it is. Use it at you're own risk, and don't go swimming at least 1 hour after use. CREATE PROC usp_CopyTable   @TABLE_CATALOG  sysname , @TABLE_SCHEMA   sysname , @TABLE_NAME     sysname , @TMP_TABLE_NAME sysname OUTAS  SET NOCOUNT ON  BEGIN DECLARE @sql varchar(8000), @GetDate varchar(25) IF EXISTS (SELECT *        FROM INFORMATION_SCHEMA.Tables      WHERE TABLE_CATALOG = @TABLE_CATALOG        AND TABLE_SCHEMA  = @TABLE_SCHEMA        AND TABLE_NAME    = @TABLE_NAME)   BEGIN  SET...

posted @ Tuesday, August 16, 2005 1:40 PM | Feedback (3) | Filed Under [ SQL Server ]

Once I'm done processing files, How can I archive them?

The following stored procedure will take a supplied file path, create an archive folder within the supplied folder path, and move everything in to that folder to basically clear out the base folder.  The Archive folder gets tagged with the date and time of the operation.  The stored procedure uses code for the Dir procedure I wrote earlier.  In that link there is DDL for the table that is referenced in this sproc. Thanks for reading. CREATE PROC Archive(@path varchar(2000))ASSET NOCOUNT ON  BEGIN DECLARE @cmd varchar(4000), @today varchar(25), @archive varchar(2000)  EXEC Dir @path, 1  IF NOT EXISTS(SELECT * FROM Directory_Contents WHERE Dir = @path)  ...

posted @ Thursday, June 30, 2005 3:40 PM | Feedback (9) | Filed Under [ SQL Server ]

How do I use the DOS Command dir in T-SQL

Well, the short answer is you don't.  The modified answer is that you can use the code listed in this post. The longer answer is that you can use xp_cmdshell to do this (Which is what the stored procedure in this post does),  and must be either a member of the sysadmin account, or granted execution, say to a new role, and users can be placed in that role.  There is a caveat to that.  From Books Online: Execute permissions for xp_cmdshell default to members of the sysadmin fixed server role, but can be granted to other users. Important  If you...

posted @ Tuesday, June 28, 2005 10:33 AM | Feedback (13) | Filed Under [ SQL Server ]

Extract Just Numbers from a String

I guess you could modify this to Extract whatever you want.  Here's the thread with the original question. Now I'm not gonna say this is real effecient, but I thought it was a little clever.  Can anyone come up with a full blow set based solution?  I'm sure it'd be ok for an overnight batch process, or used in conjunction with bcp and QUERYOUT, then just bcp.  Now I didn't ask why anyone would need to do this.  I wonder if it was a homework question.  I didn't think to ask, I just got caught up with the challenge.  And...

posted @ Wednesday, June 22, 2005 5:05 PM | Feedback (3) | Filed Under [ SQL Server ]

How do I add many rows of Data using 1 Stored Procedure

Seems this question comes up quite a bit, as it did here.  And then since my boss also was asking me how this is done, I'd figure that I'd post a solution.  It involves forming a comma delimeted string and passing that in to the stored procedure.  Modifying a Bill Graziano User Defined Function, we simply pass in this comma delimeted string.  Now I solved the posters (mester) based on the original Requirements, but I do take exception to the table structures.  They indicate they have a Reseller table, and Company table, and a Junction Table.  While this may be...

posted @ Wednesday, June 22, 2005 1:28 PM | Feedback (0) | Filed Under [ SQL Server ]

Ghost records.....ooooooo....scary

OK, Gotta admit to being floored by this. First I would like to know how many people have heard of this. Second I would like to go on to describe what this is, how it happens, and how your Ghosts are exorcised.  I guess there truely is a Ghost in the Machine. When rows are marked as deleted after a DML operation and the transaction is committed, the rows become Ghost records.  Now in DB2, it doesn't care and if the page is mark deleted it will just reuse the page.  Not so in SQL Server.  I was amazed (and shocked) by the...

posted @ Tuesday, June 21, 2005 1:21 PM | Feedback (7) | Filed Under [ SQL Server ]

Database Design based on existing data

I really like to avoid doing database design based on existing data structures.  I really prefer to start with a functional flow of the business, target the data (be it existing electronic data, memo;s, post it's, whatever, data is data) for each function, organize all the data across functions into general entities, then take all of the data elemets or attributes and associate them with the higher level Entities.  From all of this, I then would form a logical data model, develop a data dictionary and present it to the the Clients for accuracy.  Then worry about the physical implementation. BUT! ...

posted @ Monday, June 13, 2005 10:38 AM | Feedback (0) | Filed Under [ SQL Server ]

SUBSTRING with Delimiters

I've been having to parse DB2 Load Cards and to extract a lot of data (Like starting postion, Length, Column name, ect) you need parse the data out of the card based on delimiters.  DB2 pretty much is consistent as to were the data resides in it's offsets to certain keywords.  Anyway instead of using strat and Length, I figured I could make a substring function that uses “delimiters” CREATE FUNCTION SUBSTRING_DEL( @Col varchar(8000), @s varchar(1000), @e varchar(1000))RETURNS varchar(8000)AS  BEGIN DECLARE @rs varchar(8000) SELECT @rs = SUBSTRING( @Col     , CHARINDEX(@s,@col)+LEN(@s)     , ((CHARINDEX(@e,@Col,CHARINDEX(@s,@Col)+LEN(@s))) - (CHARINDEX(@s,@Col)+LEN(@s)))) Return LTRIM(RTRIM(@rs))  ENDGO Here's a sample line as to what a line...

posted @ Wednesday, June 08, 2005 12:32 PM | Feedback (1) | Filed Under [ SQL Server ]

Fun with Dates (Date Conversion examples)

Update: 11/19/2009 No!  Not that kind of fun with your date.  It seems lately a lot of Date conversion questions have been popping up.  So I'd thought I'd list them all here with their solutions and the author that has supplied them.  I will be adding to this list, everytime we come across other date manipulation requests. 1. If I only have the week and Year how do I displayJAN, FEB, MAR, ect Asked by Chandra78. I suggested: DECLARE @week int, @year int SELECT @week = 23, @year = 2005 SELECT CONVERT(char(3)  ,DATEADD(ww,@week-1    ,CONVERT(datetime,'01/01/'+CONVERT(char(4),@Year)    )  ),100) 2. How Do I get the last day of the Month.  Was Asked by...

posted @ Thursday, June 02, 2005 12:07 PM | Feedback (23) | Filed Under [ SQL Server ]

Remove a User From All Databases on a server

I'm sure Nigel or Tara alread have blogged this, but it was asked for and I scratched one up.Anyone have any horror stories with something like this? EDIT: If you look in that thread, you'll see Pat Phelan's use of sp_MSForEachDb.  Very Clever. USE NorthwindGO CREATE PROC isp_dropuser_ALL @user sysnameASSET NOCOUNT ONDECLARE @MAX_name sysname, @name sysname, @sql nvarchar(4000), @check int SELECT @sql = 'SELECT @Check=1 FROM master..syslogins WHERE [name] = '''+@user+'''', @check = NULL EXECUTE sp_executesql @sql, N'@Check int OUT', @Check OUT IF @Check IS NOT NULL   BEGIN  SELECT @MAX_name = MAX([name]), @name = MIN([name]) FROM master..sysdatabases    WHILE @name <= @MAX_name    BEGIN   PRINT 'Interogatting Database ' + @name   SELECT @sql =...

posted @ Monday, May 23, 2005 2:57 PM | Feedback (1) | Filed Under [ SQL Server ]

Weekends, Holidays and other reasons to Party

OK, so I really never need a reason.  The question ofter arises. “How can I count the number of days between 2 dates, but exclude weekends and holidays” EDIT: In this thread, Dr. Cross Join(aka Jeff Smith) posts a method where you don't need to add weekends.  I gotta test it out yet, but I pretty sure Jeff's right. Here's How CREATE TABLE WeekEndsAndHolidays (DayOfWeekDate datetime, DayName char(3))GO SET NOCOUNT ONDECLARE @FirstSat datetime, @x intSELECT @FirstSat = '1/3/2004', @x = 1 --Add WeekEndsWHILE @x < 52BEGIN INSERT INTO WeekEndsAndHolidays(DayOfWeekDate, DayName) SELECT DATEADD(ww,@x,@FirstSat),   'SAT' UNION ALL SELECT DATEADD(ww,@x,@FirstSat+1), 'SUN' SELECT @x = @x + 1ENDSET NOCOUNT OFFGO SELECT * FROM WeekEndsAndHolidays...

posted @ Thursday, May 12, 2005 2:36 PM | Feedback (11) | Filed Under [ SQL Server ]

Find a word in a string

By doing this with an identity Column on the row, you have a pseudo array for sql server.  This user defined function will grab the nth occurance of a word in a string.  By marrying that with the IDENTITY, it can be assumed to be an array.  The next parts of these would be to be able to perform functions like DELETE and UPDATE of the word in that location (which may be more trouble than it's worth.)  Pluse in Ken Henderson's Book, “The Guru's Guide to Stored Procedures, XML, and HTML”, has an entire chapter dedicated to “creating” arrays...

posted @ Monday, May 09, 2005 3:51 PM | Feedback (4) | Filed Under [ SQL Server ]

Object Dependency Hierarchal Relationships

This always seems to pop as well.  To determine what objects are dependant on what you can use sp_depends.  Unfortunately that's for 1 level of relationships in either direction (to the parent or the child).  Also, and I don't know why they write them this way, but trying to automate some things using system stored procedures are a pain.  Especially when they return multiple result sets, or when the result have different number of columns.  At the bottom of this post is a rewrite of sp_depends that the script uses.  This script then builds a table of all the relationships...

posted @ Thursday, April 28, 2005 3:06 PM | Feedback (2) | Filed Under [ SQL Server ]

Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'DESC'.

bcp fails to import data near reserved word. [Doooh]Alrighty then.  Thanks Tara (again) for pointing out the obvious.  The resolution to this problem is the -q option.  As BOL states, it sets quoted identifiers on in the context of the bcp thread.  So that solves that problem, and I'm sure it's one that I won't forget.  It's curious why there isn't a problem with the bcp out though.[/Doooh] BOL -q Executes the SET QUOTED_IDENTIFIERS ON statement in the connection between the bcp utility and an instance of SQL Server. Use this option to specify a database, owner, table, or view name that contains a...

posted @ Monday, April 25, 2005 9:04 AM | Feedback (10) | Filed Under [ SQL Server ]


This pops up every so often, as it does Here.  The post title is the DB2 syntax for achieving a unique index that allows for nulls.  I always felt that since Nulls are not equal to anything, not even themselves, then how could you get a dup key violation.   To be fair, even in DB2 you can not have a primary key that allows more than 1 Null.  But you can create a unique index in lieu of the the PK that will allow nulls.  This allows the ability to build non-identifying relationships, something I believe is lacking in SQL Server. ...

posted @ Wednesday, April 20, 2005 1:23 PM | Feedback (3) | Filed Under [ SQL Server ]

DTS too hard to export data (How to export a table with a header using bcp)

I swear that's what was posted.  Mostly they want a header row in their data and they didn't want to use DTS... bcp out with column names post OK, here you go..probably need to deal with more datatype than I have accounted for... USE NorthwindGO SET NOCOUNT ON DECLARE @sql1 varchar(8000), @sql2 varchar(8000), @TABLE_NAME sysname, @TABLE_SCHEMA sysname DECLARE myCursor99 CURSOR   FOR  SELECT TABLE_SCHEMA, TABLE_NAME    FROM INFORMATION_SCHEMA.Tables   WHERE TABLE_TYPE = 'BASE TABLE'    AND TABLE_NAME LIKE 'O%' OPEN myCursor99FETCH NEXT FROM myCursor99 INTO @TABLE_SCHEMA, @TABLE_NAME WHILE @@FETCH_STATUS = 0  BEGIN  SELECT @sql2 = ' UNION ALL SELECT ', @sql1 = null  SELECT @sql1 = COALESCE(@sql1 + ', '+''''+'"'+''''+'+'+'''','') +...

posted @ Wednesday, April 13, 2005 3:07 PM | Feedback (3) | Filed Under [ SQL Server ]

New twist on Delimited string per ID using many columns...AND this time into a table

OK...this is out there..based on this previous thread that I got a LOT of help on, SQL Team was asked a Question from MikeB to basically take a columns “key“ and marry a set of columns and place them all on 1 row, as a result set, or in to a table. The correct answer is that this is a presentation issue, but I couldn't help myself.  Basically take this: fkItemID fkCostID Rate      Units      -------- -------- --------- ---------- 1        21       45.0000   DAY1        23       400.0000  LSUM2        22       225.0000  DAY And Produce this: fkItemID fkCostID1 Rate1    Units1 fkCostID2 Rate2     Units2     -------- --------- -------- ------ --------- --------- ------ 1        21        45.0000  DAY    23        400.0000  LSUM2        22        225.0000 DAY    NULL      NULL      NULL   Here's the code.  Just make sure to add the password for the bcp statements, or...

posted @ Wednesday, February 23, 2005 3:30 PM | Feedback (0) | Filed Under [ SQL Server ]

Show me The TOP n Number of things based on a key

OK, another one that seems to be often asked, and I'm sure there's a SQL Team article out there...I'm just lazy.  Anyway, this post got me interested in this again (and I always have trouble trying to remember the answer), so I decided to post some solutions, so I don't have to remember. With the Help of Pat Phelan, Rudy Limeback, and Mr. B. Lindman (aka the Blind Dude), we came up with the following. Notice the differences in the Plans.  While Pat's Subquery looks cleaner, Rudy's Join seems more effecient.  Any comments on the plans would be greatly appreciated. EDIT: Sunsande points...

posted @ Thursday, February 10, 2005 10:02 AM | Feedback (3) | Filed Under [ SQL Server ]

How do I search a database for the umpteenth time

OK, so I got tired of writing this...so I decided to build a bells and whistles full database search.  Now at first blush, the reaction that this is bad....and it is...but I've been handed so many poorly designed databases, I felt for this persons post.  So now I'm done...I don't have to write it again....I guess we could expanded it to server the entire server.... EDIT: Modified to Search Text and ntext as well.  Changes are highlighted in Red.  Anyone know why the Row counts are still being produced? USE NorthwindGO SET NOCOUNT OFFCREATE TABLE myTableSearch99(   SPID int , SearchDate datetime , TABLE_CATALOG sysname , TABLE_SCHEMA...

posted @ Tuesday, February 08, 2005 1:20 PM | Feedback (4) | Filed Under [ SQL Server ]

Mimic Oracles INSTR Function

Starting at a user defined location in a string to find the nth occurance of a target string's starting location EDIT: OK, I got Jay's set based method to work.  And I mistook Jay for Jay White (Page47) but I was wrong (but I should be given a pass, because it's just like what Mr. 47 would have written).  Now we have to ask, Jay who?  Not anymore, thanks Jay Anyway Thanks Jay.  Just as a note:  This requires a numbers table.  The concept of using this to eliminate recursion and cursors is sort of akin to turning a light on after stumbling...

posted @ Wednesday, February 02, 2005 1:03 PM | Feedback (11) | Filed Under [ SQL Server ]

But MOM! Do I have to use sp_executesql

OK, it seems often asked how to return values from dynamic sql.  My initial reastion was to do something like the following.  Which works, is flexible, is highly dynamic, and does take a bit of setup USE NorthwindGOSET NOCOUNT ONCREATE TABLE myCounts99(mySPID int, myCount int)GODECLARE @sql varchar(8000)SELECT @sql = 'DELETE FROM myCounts99 WHERE mySPID = ' + CONVERT(varchar(4),@@SPID) + CHAR(13)+ 'INSERT INTO myCounts99(mySPID, myCount) SELECT ' + CONVERT(varchar(4),@@SPID) + ', COUNT(*) FROM Orders'SELECT @sqlEXEC(@sql)DECLARE @x intSELECT @x = myCount FROM myCounts99 WHERE mySPID = @@SPIDSELECT @xGOSET NOCOUNT OFFDROP TABLE myCounts99GO But as Nigel http://www.sqlteam.com/forums/pop_profile.asp?mode=display&id=1578 points out in this thread http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=45193 it can be much...

posted @ Thursday, January 27, 2005 10:13 AM | Feedback (6) | Filed Under [ SQL Server ]

Search SQL Server for a table in any databse

I've seen recently a lot of people with quite a proliferation of databases on a single server.  I'm not sure why this is, but it may be a “personalized” database per client, so each client has their own replica of a database for an application.  If that's the case, then I would say those are bad designs.  If it's not, then I don't know.  It would just be a maint. nightmare.  In any event, someone finally asked: “Anyone might have an idea or know how to find a specific table from various databases? I have about 20 database” In the following Post: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=44682 EDIT:...

posted @ Thursday, January 13, 2005 12:03 PM | Feedback (5) | Filed Under [ SQL Server ]

Build a Comma Delemited String For All Rows

Seems to be a popular question people seem to look for.  I Still don't know why.  Kinda hard to perform set processing against data put in this form.  I forget the first day I saw this, or from whom, but I always reference this article on the great SQLTeam Site. http://www.sqlteam.com/item.asp?ItemID=2368 So, once they got the ability to do it for 1 ID, they'd like the Whole thing denormalized...so I'll post mine here so I don't have to retpe (lord knows I could use the practice though).  It's been done over and over, and I'm sure the original came from someone here...

posted @ Wednesday, January 05, 2005 2:22 PM | Feedback (4) | Filed Under [ SQL Server ]

Real World SQL Server Disaster Recovery

Just got an email from http://www.sql-server-performance.com/default.asp And they had a great link to an article from Brian Knight that discusses Disaster recovery and is “A Survival Toolkit for the DBA“.  It even comes with a bunch of scripts already written.  I always liked reading Brian's stuff, and he continues his engaging style in this document. AND, I did not know that Brian was a co-founder of http://www.sqlservercentral.com/ Here's the link to the Article. http://www.lumigent.com/go/sd19/  

posted @ Tuesday, December 21, 2004 12:36 PM | Feedback (3) | Filed Under [ SQL Server ]

Weekend and Holidaze

Wow...it's been 2 months... It seems there's a common question discussiong how to eliminate holidays (and weekends) from date ranges.  And since I'm sick of doing a search to find it, I'd thought I'd post it.  Also, people ask about daylight savings time.  This is the same type of thing, but since it's regional, there the added twist of having to know where you are.  Anyway, here's the code:   CREATE TABLE WeekEndsAndHolidays (DayOfWeekDate datetime, DayName char(3))GOSET NOCOUNT ONDECLARE @FirstSat datetime, @x intSELECT @FirstSat = '1/3/2004', @x = 1 --Add WeekEndsWHILE @x < 52BEGIN INSERT INTO WeekEndsAndHolidays(DayOfWeekDate, DayName) SELECT DATEADD(ww,@x,@FirstSat),   'SAT' UNION ALL SELECT DATEADD(ww,@x,@FirstSat+1), 'SUN' SELECT...

posted @ Monday, December 20, 2004 1:17 PM | Feedback (0) | Filed Under [ SQL Server ]

What Happened to my DATA?!?!!?!?!?

What did YOU do!  I lost All MY Work! Sound familiar?  I always love it when they blame the database.  User or Developer, it doesn't matter, you and the database will ALWAYS be the first one to be blamed.  Facts/smacts, they lost their data, and it's all your fault.   BUT! When you hit them square between the eyes that either the manager themself, or and underling destroyed a weeks worth of work....well there's just that great feeling when you see it in their face the time the realization hits home.....knida like when an 80 ton diesel engineer and a VW bug dance...talk about...

posted @ Wednesday, October 20, 2004 3:39 PM | Feedback (2) | Filed Under [ SQL Server ]

Bi-Directional Updates - No Thanks

So who really wins?   Well I guess the answer would be no one (or the last one, or the one who bribes the dba...)   In any case, it was an exercise I alway contemplated, and always thought wasn't really a good idea, but always seems to come up.  I guess I should start a list of PROS and CONS for this type of operation.  Anyway the code was fun, and I hope it helps.  Not rocket science, and I betcha there could be a bunch of problems.  I tried to take in to account if the tables got out of sync or...

posted @ Tuesday, September 21, 2004 1:20 PM | Feedback (3) | Filed Under [ SQL Server ]


2 things, 1st, it's not the margartias and 2nd, anything that reads SOX sucks...I gotta read...   Here's the post by Derrick Leggit (to quit?) http://www.sqlteam.com/forums/pop_profile.asp?mode=display&id=5635 And his article http://weblogs.sqlteam.com/derrickl/archive/2004/06/28/1682.aspx   OK:  and the code that always seems to be asked for.  And since rembereing things ain't a strong suite (would tyhat be clubs?)   An old trick in non identity RDBMS's (note: back then we didn't have the niceties of triggers to make sure the identity control table didn't get out of whack)   USE NorthwindGO CREATE TABLE myTable99(Col1 int PRIMARY KEY, Col2 char(1), Col3ect int)GO CREATE TABLE myTable99NextID(NextId int, InUseIND int)GO -- Set the seed INSERT INTO myTable99NextID(NextID, InUseIND) SELECT 0,0GO CREATE TRIGGER myTrigger99NextID...

posted @ Tuesday, June 29, 2004 1:55 PM | Feedback (0) | Filed Under [ SQL Server ]


The Top 10 questions asked (and answered) at SQL Team.  ALL helpf appreciated, and suggestions on the order of importance...   10.  Where the Hell is Rob 9. Where is Part 2 of More Trees and Hierarchy 8. Why is Graz in a Nun's outfit 7. They have DBAs in Australia? 6. Backup, what's a backup 5. Why is my Tansaction log 30 GB? 4. How do I put the results of dynamic SQL into a variable 3. How do I make An IN statement work with a comma delimeted string 2. How do I search an entire database for a string 1. Where can I find a margarita   OK, there are a...

posted @ Friday, June 18, 2004 11:30 AM | Feedback (2) | Filed Under [ SQL Server ]

Export out entire database

For some reason this seems to be the season for people needing to dump a database to text....   Well Here's one way...should rewrite it to use INFORMATION_SCHEMA, but it works well enough.. If exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[isp_bcp_out_database]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[isp_bcp_out_database]GO CREATE PROC isp_bcp_out_database   @dbName sysname , @fp varchar(255) , @User varchar(255) , @Pwd varchar(255)AS/*  EXEC isp_bcp_out_database    'Northwind'  , 'd:\Data\Northwind\'  , 'sa'  , '' */ SET NOCOUNT ON DECLARE bcpout CURSOR FOR SELECT  -- 'EXEC Master..xp_cmdshell ' +--    '"D:\MSSQL7\Binn\bcp.exe ' + db_Name() + '.[' + TABLE_SCHEMA + '].[' + TABLE_NAME+'] '    'bcp ' + db_Name() + '.[' + TABLE_SCHEMA + '].[' + TABLE_NAME+'] '  + 'out...

posted @ Thursday, June 17, 2004 2:16 PM | Feedback (8) | Filed Under [ SQL Server ]

Computed Columns? Worth their Salt?

Well maybe if it was for a margarita...but me,  I prefer Rocks no salt. But it would be an interesting poll to see how often they are used. Me, I've never used them...and in reading BOL, seems like more of a headache than anything else.  Why not just create a function? Anyone? Geez what a lame post....just curious though... FOR: 2 AGAINST: 2    

posted @ Wednesday, June 16, 2004 8:59 AM | Feedback (8) | Filed Under [ SQL Server ]

Surrogate Keys….The Devil’s spawn (OK Not really)

Always seems to spark a very heated debate…So I’d like to make a list of Pros and Cons that I’d like to keep Adding to. All comments appreciated. So I’ll start with the CON’s, mostly because I believe there are a few reasons for a surrogate key, but it is usually WAY overused and also gets dbas/developers in more trouble . EDIT:  Ok a couple of response so far.  I think I'd like to start a Poll (If that's possible).  I'll add another pro for the devolpers in item #4. EDIT: It's been suggested that surrogates are ANSI.  I thought they were...

posted @ Wednesday, June 09, 2004 11:02 AM | Feedback (26) | Filed Under [ SQL Server ]


Huh?  What is TQL?  A new query language...Tequila Query Language perhaps? Nope.  A SQL Server Template.  When I'm not stealing code, I'll use a template to get sprocs going.  For the most part, Error Handling in a sproc is the same (well for me) 99% of the time. Just cut and paste the code in to a *.tql file in the folder: C:\Program Files\Microsoft SQL Server\80\Tools\Templates\SQL Query Analyzer\Create Procedure Then in QA Do [CTRL]+[SHIFT]+{INSERT] to insert a new template.  To update the values in the template (for example the new name of your sproc, [CTRL]+[SHIFT]+M.  A dialog will appear and you can change the...

posted @ Tuesday, May 25, 2004 10:19 AM | Feedback (9) | Filed Under [ SQL Server ]

Dynamic Predicates?

Always seem to asked...bugged the hell out of me..The answer always seemd to be dynamic SQL since anything in the predicate to handle the absence of a criteria would cause a scan of the index...stage 2 predicate (non sargable) and all..   But what about this?  Just add as many Left Joins as you need.....if properly indexed, it all index seek on the main/large table that's being searched.   All Comments appreciated   EDIT:  After more research, I don't think ( I hate when that happens) That you can have a full AND Condition, but all we can hope for is a ranking of how many not...

posted @ Wednesday, May 05, 2004 3:27 PM | Feedback (6) | Filed Under [ SQL Server ]


It's all about the update... EDIT: 9/9/2004:  Well that wasn't much of an explanation.  What the hell are deltas.....is it a place in Mississippi where they have great music and food?  Well, yes....but deltas here are meant to describe a process, where by a database table has a set of data WITH a primary key, and a file is supplied daily with “updates“ (and I use that term loosely) that need to be applied to the table.  Both the file and the table do not have to have the same structure, number of columns, datatypes and/or sizes...but the MUST have a...

posted @ Friday, April 23, 2004 11:49 AM | Feedback (0) | Filed Under [ SQL Server ]

Reasons to not use SELECT * (and when to use it)

I'd like to expand this list with anyones comments.  Thanks!  And if anyone knows the M$ link that discusses how they optimized SELECT * for existance, I'd appreciate a linke.  Thamks again! Why you shouldn't 1. Effeciency: Only the data you need Reduces the amount of I/O that has to occur 2. Better use of indexes May use Index intersection or make use of covered indexes 3. Isolate code from Table object changesFor example, the following would through an error INSERT INTO myTable99(Col1, Col2, Col3,ect) SELECT * FROM myTable00 WHERE 4. I get a chuckle when Access developers say DELETE * FROM mytable99 'nough said 5. Increases network traffic, requires more...

posted @ Thursday, April 22, 2004 11:00 AM | Feedback (6) | Filed Under [ SQL Server ]

Sanitize input files

I had a devil of a time with the spawned thread blocking from xp_cmdshell and doing bcp's....   Sometime it would work, other times not... The sproc is treated as 1 logical unit of work...for example there is no problem in just rerunning it if it fails...   Even though I've got 4 transactions...   Any comment appreciated...   Oh, and btw, it takes a file (8k or less wide) loads it, and modifies what you want to cheange, archives the old file, and spits out a sanitized one... SET NOCOUNT ON if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[wrk_DataHold]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[wrk_DataHold]GO CREATE TABLE wrk_DataHold(Col1...

posted @ Tuesday, March 23, 2004 9:02 AM | Feedback (0) | Filed Under [ SQL Server ]

The Interview

I've decided to start a list of questions for canidates (or so I can remeber when I gotta go)...ones that I've run across and was confused by (yeah, yeah, there's not enough drive space available...so I'll keep it breif) 04/15/2004 Q:What do you do with an index intersection? A: Look both ways, before you cross join.   02/12/2004 1.  Why do the 2 statements below return different results? declare @l decimal(38,2) select @l = 24.35if @l - convert(int,@l) = 0    select floor (@l)  else     select @lselect case when @l - convert(int, @l) = 0 then floor (@l)                                                else @l       end    

posted @ Thursday, February 12, 2004 1:56 PM | Feedback (10) | Filed Under [ SQL Server ]

Interogate Sprocs

Seen a bunch of people interested in Searching through Sprocs lately.  Thought it would be handy (especially for some of these god awful legacy database we're so fortunate to inherit.   Just script the sprocs in to individual files and copy to the server (if they're not there already)   And execute the code below.   USE NorthwindGO CREATE TABLE Folder (dir_output varchar(8000))CREATE TABLE Folder_Parsed (Create_Time datetime, File_Size int, [File_Name] sysname)CREATE TABLE MyWork99  (myText99 varchar(7000))CREATE TABLE MySprocs99 (myText99 varchar(7000), SprocName sysname NULL, LineNum int IDENTITY(1,1))GO SET NOCOUNT ON INSERT INTO Folder EXEC master..xp_cmdshell 'Dir d:\Sprocs\*.*' INSERT INTO Folder_Parsed (Create_Time, File_Size, [File_Name] )SELECT   Convert(datetime,Substring(dir_output,1,8) + ' '  + (Substring(dir_output,11,5)  + Case When...

posted @ Thursday, February 05, 2004 2:12 PM | Feedback (13) | Filed Under [ SQL Server ]

Powered by:
Powered By Subtext Powered By ASP.NET