|
|
SQL Server
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...
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...
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
...
"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...
....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...
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...
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...
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...
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+'%'
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...
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...
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...
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...
...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
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...
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,...
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...
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) ...
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...
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...
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...
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...
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! ...
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...
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...
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 =...
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...
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...
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...
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...
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. ...
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 + ', '+''''+'"'+''''+'+'+'''','') +...
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...
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...
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...
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...
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...
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:...
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...
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/
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...
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...
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...
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...
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...
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...
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
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...
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...
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...
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...
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...
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...
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
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...
|