Chronicals of… a developer in Johannesburg

It was more than a month ago when I decided to post once a day for a month, and now it has almost been a month since my last post – the irony.

Quick news and possibly excuses:

I just moved house and have no internet at my new place – until today I haven’t at least.

Work has been madness.

We’re going to Thailand in 2 weeks, very excited.

I did some neat work with CTE in SQL – I will definitely post on it.

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

Sometimes we need to restore a database off someone else’s BAK file. If you dont do it often or have never done it, then here is how.

I like SQL Management Studio.

1. Create a new database with the name you want.
2. Right click that database and then go to Tasks -> Restore -> Database
3. In the new dialog, enter your database name, and select the backup file at the bottom.
   — Now here comes the trick —
4. Go to the options tab and select “Overwrite the Existing Database”, and select new paths to restore the files to (usually your default SQL folders).

The script to do this can be generated and here it is:

RESTORE DATABASE [q2g_dev]
FROM  DISK = N’D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\q2g_live MS SQL 2005.bak’
WITH  FILE = 1,
MOVE N’quip2go_Data’ TO N’D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\q2g_dev.mdf’,
MOVE N’quip2go_Log’ TO N’D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\q2g_dev_log.ldf’,
NOUNLOAD,
REPLACE,
STATS = 10
GO

SQL Database Restore Options

Enjoy. Use your database names instead of mine