May 2005 Blog Posts
Since, I'm tired of retyping this...If you want to get an answer fast about a database question (doesn't matter the platform) and it's sql related....Do this
Please state your problem in the context of a business requirement. Please do not force a narrowly focused technical solution, which may or may not be of any value. It may also be a distraction to what the actual solution would be. To aid in the solution please do the following if possible1. State the question"How do I find the earliest row entered"
2. Please post the DDL of your tables (Including Indexes, and constraints)
No Kidding...I should have guessed that Bill was one. There's a boat load of MVPs. Let's see who we know. OK so 85 ain't a boat load.
I know or have spoken with
Mr. Bill, Frank Kalis, Adam Machanic (I believe this is our Adam), Brad McGehee, Nigel Rivett, Jasper Smith, Rob Volk (And why isn't his bio there?)
Don't know, but I'd like to.
Kalen Delaney (Still have to get her internals book), Brian Knight (I love Brians Books), Valeria Rodriguez Z. (Tell me again why all dba's don't look like this?)
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.
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.
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...
As of Oracle 8i (which I believe is no longer supported) there was no utility to unload data. I guess Oracle felt that once the got the data, there was no reason to relinquesh it. In any case, for myself, and my rusty Oracle, here a sample of code on how to code in PLSQL as well as how to perform an extract. I know it'll be hard from a sql server perspective, but this actually flies.
-- EXEC Ben_Extract_Package.Ex_ENR_PARTIC_sp;
-- EXEC Ben_Extract_Package.Ex_ENR_PARTIC_PLAN_sp;
-- EXEC Ben_Extract_Package.Ex_ENR_PARTIC_DPND_sp;
-- EXEC Ben_Extract_Package.Ex_ENR_DPND_BENEF_sp;
-- * Prudential Benefits Annual Enrollment System *
-- * *
-- * Description: Extract Feed...