x002548's Blog

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

May 2005 Blog Posts

How to post a question for a database

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) Like CREATE...

posted @ Wednesday, May 25, 2005 4:37 PM | Feedback (5) | Filed Under [ Disconnected Ramblings ]

SQL Server MVP's

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?)  

posted @ Monday, May 23, 2005 4:31 PM | Feedback (15) | Filed Under [ Disconnected Ramblings ]

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 ]

bcp out for Oracle

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. -- @Q:\Packages\Ben_Extract_Package.pkg; -- 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...

posted @ Thursday, May 05, 2005 12:33 PM | Feedback (3) | Filed Under [ Oracle ]

Powered by:
Powered By Subtext Powered By ASP.NET