SQL Azure CTP1 is out

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.

 

Sign up for your copy of CTP1 of SQL Azure here: https://connect.microsoft.com/Survey/NominationSurvey.aspx?SurveyID=5719&ProgramID=2089&SiteID=547

 

MSDN Documentationon SQL Azure: http://msdn.microsoft.com/en-us/library/ee336279.aspx

Zach Owens, who is a Microsoft Technical Evangelist has blogged about SQL Azure extensively. Check them out here: http://english.zachskylesowens.net/. He has also published a Windows Azure training kit with demo on SQL Azure. You can get that here: http://www.microsoft.com/downloads/details.aspx?FamilyID=413E88F8-5966-4A83-B309-53B7B77EDF78&displaylang=en

Feel free to post any questions you may have on SQL Azure and I will try to answer as best as I can.

 

Silicon Valley Code Camp - 10/27-28

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.
CodeCamp at FootHill College.  Click Here for Details and Registration

Setting up Transactional Replication from a back up copy

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

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..

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...

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

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

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.
«December»
SunMonTueWedThuFriSat
30123456
78910111213
14151617181920
21222324252627
28293031123
45678910