Brett Kaiser (x002548) Blog

Not Just a Number - Brett Kaiser

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. Read more →

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. Read more →

Replace a Character Anywhere in a DB

Ran across this thread 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. Read more →

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: 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 12, 'y' UNION ALL SELECT 13, 'z' GO CREATE VIEW myView99 AS SELECT Col1, Col2 FROM myTable99 UNION ALL SELECT Col1, Col2 FROM myTable98 GO SELECT * FROM myView99 UPDATE myView99 SET Col2 = 'x002548' WHERE Col2 = 'z' SELECT * FROM myView99 /* DROP VIEW myView99 DROP TABLE myTable99, myTable98 */ Read more →

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. Read more →

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. Read more →

SET Versus SELECT (Or, Who Really Cares Anyway)

EDIT: As Tara points out: Vyas did this test quite some time ago: 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. Read more →

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. Read more →

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

Like this SELECT, t.TABLE_NAME, c.text FROM syscomments c JOIN sysobjects o ON = JOIN INFORMATION_SCHEMA.Tables t ON c.text LIKE '%'+t.TABLE_NAME+'%'   Legacy Comments Peter DeBetta, Microsoft MVP - S 2006-09-22 re: How do I find all the tables referenced by Stored Procedures or Functions Actually, that isn't entirely reliable if the code of the procedure is longer than 8000 characters. Read more →

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. Read more →

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. Read more →

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. Read more →

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. Read more →

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

...was recently asked at SQLTeam 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 Read more →

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. Read more →

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. Read more →

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. Read more →