Chronicals of… a developer in Johannesburg

I missed the post last for yesterday…

I wanted to say I am working on a simple registration website which will be live this weekend, Winhost is so great!

I am always on the lookout for some better hosting options. This one found me.

In my daily activities including being on the MSDN forums, this advert caught my eye. I hope it helps any of you other there, for $9 (R80) per month you can get 5 SQL 2008 databases!

http://www.winhost.com/windows-hosting-plans.aspx

Using the System.Web class you can encode url’s to a nice pretty version.

http://www.vcskicks.com/encode-url.php

Thanks to this site for helping me as I was trying to do some encodeing in a windows service, but I hadn’t manually loaded the reference. Therefore VS was not picking up the HttpUtility class.

A Hello World for Silverlight 2 and Deep Zoom.

http://www.hanselman.com/blog/TheWeeklySourceCode18DeepZoomSeadragonSilverlight2MultiScaleImageMouseWheelZoomingAndPanningEdition.aspx

This is for those who use string concatenation to join character variables and hard coded strings to form some sort of new output.

I found this when trying to throw and error and using the variable to describe the nature of the error. It will affect your PRINT, SELECT, and SET statements too.

EG.
DECLARE @Output varchar(100), @SomeVar varchar(10)
SET @SomeVar = NULL

SET @Output = ‘This will be ‘ + @SomeVar
RAISERROR (@Output, 12, -1) — or what ever you want here

The problem with the above is that trying to concatenate a null with a string will make the entire string null. So throw in an ISNULL and then we’re all set.

SET @Output = ‘This will be ‘ + ISNULL(@SomeVar,’NULL’)
RAISERROR (@Output, 12, -1) — or what ever you want here

With just over 4 weeks until my wedding, there is much to do. Blogging has been put at the back of that list.

I did figure out something interesting in SQL though. If you want to group a bunch of dates by day, but not by the second, but still want to return a datetime value just do this:

CONVERT(datetime,CONVERT(varchar, dateValue, 102))

Yesterday we had to migrate a couple of thousand rows from some excel documents (from different departments) into one table. All was running smoothly, but I found duplicates and part of the duplicate removal process was the following.

Select distinct variables into a #temp table.
Truncate the main table
Select into the main table from the #temp table and then all is well.

Query editor window 1: Created #temp table, selected distinct data into it, checked distinct data.

Query editor window 2: Began transaction, created #temp table, truncated table, inserted into new table <error occurred> tried to rollback.

End result. The table was truncated and I lost the data. Luckily I went back to the Query editor window 1 and found the temp table, only problem was – it didn’t have the rights to insert into the real table.

Now the point of the story – copied the #temp data into a ##temp data. Used that data from another query window to save the day.

A note to all of you, never truncate a table within a transaction and try to rollback. This is what we did….

Ever had an excel document with all the data that you want to import into a SQL database table? Bulk insert is your friend.

I know some of us (me included) are sometimes scared of handling external files in SQL. But this is really easy.

Best thing to do is get your excel document, export it as tab delimited, and import that into a table, temp table, or variable table within your sql query.

The main issue to note is that the path in the bulk insert is from the sql server’s “point of view”. So if you connect to a server through the network and need to access a file on you PC, then share it and grant the correct security permissions.

I felt the need to search for a text string in SQL 2005 within a stored procedure.
EASY! yes… but then you get the stored procedure name, and if you want … the text. The text isn’t very useful when it is one giant string now, is it?

So here is a little script I made. It will return the stored procedure name, the line of text where the string was found – and the line number. handy you say……?

DECLARE
@TextString varchar(100)

SET
@TextString = ” ——- YOUR STRING HERE

DECLARE
@ProcName varchar(100)
DECLARE @Results TABLE(ProcName varchar(100), ProcLineNo int, ProcLine varchar(1000))

DECLARE blah CURSOR FOR
SELECT DISTINCT
[Name]
FROM
SysObjects AS O (NOLOCK)
JOIN SysComments AS C (NOLOCK) ON O.Id = C.Id WHERE [Text] LIKE ‘%’+ @TextString +’%’
ORDER BY
[Name]

OPEN blah
FETCH NEXT FROM blah INTO @ProcName
WHILE @@FETCH_STATUS = 0
BEGIN

CREATE TABLE #ProcLines ([lineNo] int IDENTITY(1,1), line varchar(1000))

INSERT INTO
#ProcLines
EXEC sp_helptext @ProcName

INSERT INTO
@Results
SELECT
@ProcName,
[lineNo],
line
FROM
#ProcLines
WHERE
line LIKE ‘%’ + @TextString +’%’

DROP TABLE #ProcLines

FETCH NEXT FROM blah INTO @ProcName
END

CLOSE blah
DEALLOCATE blah

SELECT
*
FROM
@Results