x002548's Blog

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

June 2005 Blog Posts

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 ]

Powered by:
Powered By Subtext Powered By ASP.NET