Website Copier

An excellent tool to download a website to you local drive.

Download HTTRack

SQL Records to CSV

I had a requirement to display the list of financial markets for an Advisor as a Comma Separated values in the ASP.net page. I wrote the COALESCE function in the SQL stored procedure to do this as following.

CREATE FUNCTION [DBO].[UFN_GETUSERMARKETSCSV](@PUSERID VARCHAR(50))
RETURNS VARCHAR(1000)
AS

BEGIN
DECLARE @TEMPMARKET TABLE (MARKET_ID INT, MARKET_NAME VARCHAR(255))
INSERT INTO @TEMPMARKET
SELECT m.Market_ID, m.Market_Name
FROM MMS_Market m
JOIN MMS_User_Market_Access uma
On.uma.Market_ID = m.Market_ID
Where uma.User_ID = @pUserID

DECLARE @CSV VARCHAR(1000)
SELECT @CSV = COALESCE(@CSV + ', ', '') + MARKET_NAME FROM @TEMPMARKET

RETURN @CSV
END

Batch Update in SQL Server

I recently faced an issue with batch updates in SQL server. There was a table in SQL server 2000 which had 1 billion records and I had to introduce a new column to that with a default value. Adding a default value along with the alter script failed with an exception as shown below.

Error: 9002, Severity: 17, State: 2
The log file for database '%.*ls' is full.

Further info: http://support.microsoft.com/kb/317375

Hence I used a script which updates the records in a batch as following

Set nocount on
DECLARE @batch_size int, @rowc int
SELECT @batch_size = 20000
SELECT @rowc = @batch_size
SET ROWCOUNT @batch_size
WHILE @rowc = @batch_size
BEGIN
Begin Tran
Update dbo.<> Set <> = <> where <> is null
SELECT @rowc = @@rowcount
Commit Tran
END
SET ROWCOUNT 0
Set nocount off


This had few drawbacks it was very slow (took 10 hours) to update ½ a billion records and caused an exception as following

[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData (CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken


I had to rewrite the script as following


SET NOCOUNT ON
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name LIKE '#tMonths%')
BEGIN
DROP TABLE #tMonths
END
SELECT [Month]
INTO #tMonths
FROM dbo. tblHoldings_Securities_Total
GROUP BY [Month]
DECLARE @dtMonth DATETIME
DECLARE @sMonth VARCHAR(30)
DECLARE @rc INT
DECLARE cur_months CURSOR FAST_FORWARD
FOR SELECT [Month] FROM #tMonths ORDER BY [Month]
OPEN cur_months
FETCH NEXT FROM cur_months INTO @dtMonth
WHILE @@fetch_status = 0
BEGIN
set @sMonth = cast(@dtMonth as varchar)

UPDATE dbo.tblHoldings_Securities_Total
SET Sec_identifier = 1
WHERE [Month] = @dtMonth
AND Sec_identifier IS NULL
SELECT @rc = @@ROWCOUNT
RAISERROR ('Month: %s; Updated count: %d',10,1, @sMonth, @rc) WITH NOWAIT
FETCH NEXT FROM cur_months INTO @dtMonth
END
CLOSE cur_months
DEALLOCATE cur_months


Advantages

1.The above method used grouping logic based on an Indexed column.
2.Completed in 4 hours.