Dinakar Nethi

For those that have not heard about the "Azure" platform, Windows Azure is a cloud based operating system that offers software as services - .NET Services, Live Services and SQL Azure.

SQL Azure, formerly SDS - SQL Data Services, is a cloud based version of on-premise SQL Server, albeit with a few limitations. The benefits are enterprise level availability, scalability and built-in disaster recovery.

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.

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

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

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.

CREATEPROCEDURE ParseArray (@ArrayVARCHAR(1000),@separator CHAR(1))
ASBEGINSET NOCOUNT ON-- @Array is the array we wish to parse-- @Separator is the separator charactor such as a commaDECLARE @separator_position INT-- This is used to locate each separator characterDECLARE @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 valueSET @array = @array + @separator
-- Loop through the string searching for separtor charactersWHILE PATINDEX('%' + @separator + '%', @array) <> 0
BEGIN-- patindex matches the a pattern against a stringSELECT @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 arraySELECT Array_Value = @array_value
-- This replaces what we just processed with and empty stringSELECT @array = STUFF(@array, 1, @separator_position, '')
ENDSET NOCOUNT OFFEND
GO

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.