Syscomments...

Silicon Valley Code Camp - 10/27-28

Dinakar Nethi — Tue, 09 Oct 2007 21:25:40 GMT

Peter Kellner is conducting a code camp in the bay area on Oct 27,28.

Check here for more info.

Speakers include Juval Lowy, Beth Massi, Deborah Kurata among many others.

Setting up Transactional Replication from a back up copy

Dinakar — Mon, 02 Apr 2007 18:51:29 GMT

Recently I was trying to set up Transactional Replication in SQL Server 2000 from a back-up copy. After a few hacks and work arounds and lot of trouble-shooting I was able to get it up and running. I documented the steps I followed. Check out the article here.

Getting Results of a Query into Excel without formatting issues

Dinakar — Mon, 02 Apr 2007 18:48:43 GMT

Recently I had to ship out chunks of data into an excel sheet for some queries. I wrote an article on it. Feel free to check it out here.

MVP Renewed for 2007..

Dinakar — Mon, 02 Apr 2007 18:25:58 GMT

I got an email this weekend saying my MVP award has been renewed for 2007..Thats second year in a row...am thrilled...not to mention the $150 . Last time I was at the summit I almost bought some stuff but I gave up frustrated with the line going a few hundred yards out of the building when it was freezing outside.. I will probably get the Laser KeyBoard/Mouse

New Yak around...

Blog Author — Thu, 29 Mar 2007 00:29:30 GMT

Allright..so after having stuggled with my blogs @Dotnetjunkies and sending numerous emails to the admin to fix the issues and not getting any response, I decided to pull the chord off from DNJ. I am setting up my shop here @SQLTeam and hopefully I will have good time here. The initial response from the forums at least, has been very good

Parse delimited string in a Stored procedure

Dinakar — Wed, 28 Mar 2007 22:36:34 GMT

Sometimes we need to pass an array to the Stored Procrdure and split the array inside the stored proc. For example, lets say there is a datagrid displaying sales orders, each sales order associated with an orderid (PK in the Sales table). If the user needs to delete a bunch of sales orders ( say 10-15 etc)..it would be easier to concatenate all the orderid's into one string like 10-24-23-34-56-57-....etc and pass it to the sql server stored proc and inside the stored proc, split the string into individual ids and delete each sales order.

There can be plenty of other situations where passing a delimited string to the stored proc is faster than making n number of trips to the server.

CREATE PROCEDURE ParseArray (@Array VARCHAR(1000),@separator CHAR(1))
AS 

BEGIN
SET NOCOUNT ON

-- @Array is the array we wish to parse
-- @Separator is the separator charactor such as a comma

        DECLARE @separator_position INT -- This is used to locate each separator character
        DECLARE @array_value VARCHAR(1000) -- this holds each array value as it is returned
-- For my loop to work I need an extra separator at the end. I always look to the
-- left of the separator character for each array value

        SET @array = @array + @separator

-- Loop through the string searching for separtor characters
        WHILE PATINDEX('%' + @separator + '%', @array) <> 0 
            BEGIN
                -- patindex matches the a pattern against a string
                SELECT  @separator_position = PATINDEX('%' + @separator + '%',@array)
                SELECT  @array_value = LEFT(@array, @separator_position - 1)
                -- This is where you process the values passed.

                -- Replace this select statement with your processing
                -- @array_value holds the value of this element of the array
                SELECT  Array_Value = @array_value
                -- This replaces what we just processed with and empty string
                SELECT  @array = STUFF(@array, 1, @separator_position, '')
            END
SET NOCOUNT OFF
END

GO

Exporting blank SRS report to excel does not render the headers

Blog Author — Wed, 28 Mar 2007 21:47:07 GMT

I worked on RS reports for a while and had been confonted with this issue. When the report does not receive any resultset from the datasource it shows up only the header columns in the report manager which is all ok. In our case this particular report was being exported to Excel and strangely the RS did not send anything except a huge textbox with nothing in it.

And our requirement was that the excel spreadsheet should have the header columns even if there was no data. Now I thought of concatenating all the column names and use the NoRows property but that will be exported as 1 column rather than individual columns.

So I found some work-around for that and wrote up an article under SQL Server 2000. Check it out here.