I recently had to load a lot of comma separated data into a file never knew how easy it was to load CSV data into a table. Here is the t-sql: BULK INSERT Address FROM 'c:\address.csv' WITH     (     FIELDTERMINATOR = ',',     ROWTERMINATOR = '\n',     FIRSTROW = 2,      ) So handy to have this feature. Simply just point the from...

 One common place where people replicate data is in situations where you need data at a specific point in time. A perfect example I saw today was this situation: You can see that the price column is duplicated in both the products and orderDetails table. The rationale for this is that so that when the price of the product changes that it does not affect historical orders. This is a fairly viable solution but has a few drawbacks. The main one is data duplication. Most of the time the data will be the same (unless product pricing...

Often for my deployments I need a script that recreates all stored procs and then grants the public role to have access to it. I posted a script that someone else had developed a long time ago but.... it sucked.... ya. so here is the simple new one:select    'grant execute on '+[name]+ ' to public'from    sysobjects where    xtype='P'or        xtype='FN'

One of the tools I find lacking in the industry is a good sql comparison utility. I have tried some free ones that really seem to not work very well at all. Commercially I have found that red-gate and adept's programs quite good but I am cheap. Has anyone found a good tool to compare to databases to each other that is free? Or would anyone be interested in starting an open source project to do this with me?

The other night at edmug Richard Campbell presented some good SQL Querying Tips & Techniques. A lot of them were about sql 2005 which was nice to see (espeically using Common Table Expressions which are quite handy in the case of recursion). The coolest thing I found was in regards to a cross tab query. The example used was to get sales amount by month for each employee. As you can see in the following example this is done with a lot of subqueries that builds a hideous execution plan.SELECT Salespeople.Salesperson, SUM(S1.Quantity*S1.Price) AS Total,   (SELECT SUM(S2.Quantity*S2.Price) FROM Sales AS...

    Today I was asked how to return an error code from a database along with other data. While I try to sheer away from error codes from the database and do my validation / logic in the business / data layers this still is necessary. Most people I have worked with have no idea that sql2000 (and 2005) support output parameters which can be quite usefull. CREATE PROCEDURE GetCustomers ( @ErrorCode int OUTPUT )ASSELECT * FROM customers--if there is an error set this to something elseset @ErrorCode = 0goIn code you would have something like this:cmd.parameters.add("@ErrorCode", SqlDbType.int)cmd.parameters("@ErrorCode").Direction = ParameterDirection.Outputreader =...

A while back I posted a script someone shared with me to generate grants on functions and stored procs to a user. I discovered it is pretty crappy and convoluted. I built this instead:select     'grant execute on ' + [name] + ' to public'   from     sysObjects where     (         xtype = 'P'     or     xtype='FN'     ) and    [name] not like 'dt_%'

A lot of people do not know is that SQL supports the GOTO command and labels just like good old basic. An example is worth a 3.3337 words:       BEGIN TRAN UpdateDetails                update     details         set        payer = @payer         where      payerId=@PayerId                IF @@error <> 0            GOTO   Errorhandler        update     payer        set        lastActivityDate=getdate()        where      payerId = @PayerId        IF @@error <> 0            GOTO Errorhandler    commit tran UpdateDetailsErrorHandler:        BEGIN    ROLLBACK TRAN UpdateDetails        END As you can see that after every statement we run we check the error status and if there is an error we go to ErrorHandler. Pretty easy (and ugly) Now you might wonder why I am checking the @@error after every statement and not just at...

I just built a quick little SQL reformater that makes your sql a bit more readable. It takes this:select pmtlndet.pmtlndet_amt, glcdty.glcdty_desc from pmtlndetjoin pmtln on pmtln.pmtln_ID = pmtLndet.pmtln_IDjoin pmt on pmt.pmt_id = pmtln.pmt_idjoin GlCdTy on pmtlndet.GlCdty_id = GlCdTy.GlCdty_idwhere pmt.hostappl_inv_num = @HostApplInvNum and pmtln.pmtStatSubTy_id=@RCRYty_IDand turns it into this: select pmtlndet.pmtlndet_amt, glcdty.glcdty_descfrom pmtlndetjoin pmtlnon pmtln.pmtln_ID=pmtLndet.pmtln_IDjoin pmton pmt.pmt_id=pmtln.pmt_idjoin GlCdTyon pmtlndet.GlCdty_id=GlCdTy.GlCdty_idwhere pmt.hostappl_inv_num=@HostApplInvNumand pmtln.pmtStatSubTy_id=@RCRYty_ID This tool was just a quick one off and does not reformat subqueries very well as well as function calls in the select statement (i.e. isnull(col, 'replacement')) but so far has saved me a ton of time. NOTE: When you click the parse button it automatically copies the reformated SQL to the clipboard.Download

For those of you who don't know what a GUID is it is a 128 bit value that is "Unique across space and time". A GUID looks like this: B2658C9D-A76G-4D72-B0E4-B732332408D6. There garanteed uniqueness has been used by the com+ system in windows for years. This is why two com components with the same name can be installed without conflicting (they each have a GUID that keeps them seperate).Developers seem to be pessimistic about GUID collissions in their systems. I just have to say that it will not happen. As I said com+ has been using this forever and I have never heard of a GUID collission ever....