Quick T-SQL tip: Insert image into db using SQL statement

The goal of this quick post is to show how to insret image into database cell using sql statement. Here is the table that we insert the row into.

The cell is type of image, but the actual data stored inside of it is binary. Here is the insert statement that you can use to insert row into that table:

INSERT INTO MsMailTemplateImages(ContentID, Image, MsMailTemplateID) 
SELECT 'greentree' as ContentID, BulkColumn, 1 as MsMailTemplateID FROM 
OPENROWSET(BULK N'C:\Downloads\environmental.gif', SINGLE_BLOB) as DATA

Visual studio QuickReplace using Regular Exporession [Regex]

I don't have to persuade anybody that using regex saves a lot of time. Recently I was about to add 20 new parameters to the stored procedure updating record. The reason for that was the change in table definiton. I had to make following steps: 1) add new parameters to sp, 2) add this parameters to update statement 3) add this parameters to insert statement I could use copy and paste, because all three steps needed only some changes withing string with new columns. 1) The sample from input sql I had to write for sp Input params

@CategoryL3 nvarchar(50), @CategoryL4 nvarchar(50),

2) The sample from update statement

[CategoryL3]=@CategoryL3, [CategoryL4]=@CategoryL4,

3) The sample from insert statement

[CategoryL3], [CategoryL4] 

and then:

@CategoryL3, @CategoryL4 

Here is regex to find things from first sample:
[@]{.[^@ ]*} .[^@ ]*,
- in {} brackets we get the expression we want to extract from what has been found.
I'm looking for:
- @ at sing
- ".[^@ ]* "- all the signs (.*) until space (which is after star) that don't contain @ sign
- then there is space and anything thats between this space and comma, but again without @ sign (to avoid greedy results) .[^@ ]*


then in replace with text box I can put \1 which result with setting it to the values I enclosed with curly brackets.

Adding new parameter to WebReport (asp.net 2.0 sql server 2005)

The situation is as follows: we have existing WebReport which users stored procedure for getting data. We want to add new parameter. 1) Alter stored procedure to expect new paramter 2) In the layout tab in report designer go to menu Report => Report Parameters and add new parameter. Img. 1: CustomerIds - new parameter

Initially I thought it's enough, and you can imagine how supprised I was when trying to preview report I got an error: procedure expects parameter which was not supplied. I spend more than half an hour to figure out what caused my problem. There is third step you have to make when adding new param. 3) On dataset properties Parameter tab you have to enter the input parameter of the sp for 'name' and select report parameter under value. Img. 2: 3rd step.

That's it.

Restoring Database in SQL Server 2005

This is quick tip explaining how to restore database in SQL Server 2005. The new database had different schema than the one I wanted to restore data to.
This is the sql Generated by Sql management studio:

RESTORE DATABASE [DatabaseName] 
FROM  DISK = N'C:\temp\db\DBBackup.bak' 
WITH  FILE = 1,  NOUNLOAD,  STATS = 10
GO

Unfortunately it running it couses an error: Error 3154: The backup set holds a backup of a database other than the existing database.
All you need to do is to add parameter REPLACE. this is how new query looks like:

RESTORE DATABASE [DatabaseName] 
FROM  DISK = N'C:\temp\db\DBBackup.bak' 
WITH  REPLACE, FILE = 1,  NOUNLOAD,  STATS = 10
GO