SQLAuthority News – Author Visit – Toronto, Canada – Insert Image in Database

I am traveling to Toronto from Microsoft MVP Sumeet. I will be very tired as I am continuously working very hard from last 27th Feb. I am still Jet Legged from my trip from India to USA and now I am again changing time zones by visiting Canada. I get all my energy from feeling that what I am doing is helping community and I am working hard to help people who are looking for help.

Interestingly Steven Biggins, a reader of this blog was with me in same flight to Canada. He recognized me and asked me following question. As I have never done this before I had to pull out my laptop in flight and start writing code. Following T-SQL script demonstrates the code which inserts the image in database. You will need small image on your hard drive to create this example. You can download Image and T-SQL Code.

USE [AdventureWorks]
GO
--Create Table
CREATE TABLE [dbo].[ImageTest](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Image] [varbinary](MAX) NULL
)
ON [PRIMARY]
GO
--Insert Image
INSERT INTO [dbo].[ImageTest]([Image])
SELECT * FROM
OPENROWSET
(BULK N'C:\ImageCode\sa.jpg', SINGLE_BLOB) AS Document
GO
--Clean Up DB
DROP TABLE [dbo].[ImageTest]
GO

Now my question to reader is how to retrieve the image and create JPG file again. I will post the solution soon but I rather prefer that the solution provided by my reader.

Following is my travel itinerary so far:

Feb 27, 2009 – Departing Ahmedabad to Mumbai
Feb 28, 2009 – Departing Mumbai to Seattle Sheraton Hotel
March 1, 2009 – Day 1 at MVP Summit
March 2, 2009 – Day 2 at MVP Summit
March 3, 2009 – Day 3 at MVP Summit
March 4, 2009 – Day 4 at MVP Summit
March 5, 2009 – Departing Seattle to Toronto
March 6, 2009 – Tech User Group Meeting, Markham, Canada
March 7, 2009 – Departing Toronto to Mumbai
March 8, 2009 – Missing Day due to Day Line Crossing
March 9, 2009 – Arriving to Ahmedabad

Reference : Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – Find Table Rowcount Without Using T-SQL and Without Opening Table

Recently I have been busy with interviewing many candidates for my organization. We are looking for some smart and experienced developers for some senior positions. I have wrote this previously SQL SERVER – Interesting Interview Questions.

I had asked following question to one of the candidate and he was not able to answer this question. I promised him that I will put answer to this question on blog.

The question was : How to find table rowcount without using T-SQL and without opening table?

Answer : Well it is quite simple by using SQL Server Management Studio.

Right click on table and click on Properties. Now on left nav of opened window click on storage and under general section there is display of Row Count.

If you have similar interesting questions, please share with me. I will post on blog.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – 2008 – Install SQL Server 2008 – How to Upgrade to SQL Server 2008 – Installation Tutorial

SQL SERVER 2008 RTM has been released for some time and I have got numerous request about how to install SQL Server 2008. I have created this step by step guide Installation Guide. Images are used to explain the process easier. I had previously wrote the same article earlier. It seemed necessary to re post it again as request of me posting Step by Step tutorial has increased for quite some time.

[Click On Images to See Larger Image]

[Click On Images to See Larger Image]

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Simple Example of Cursor

UPDATE: For working example using AdventureWorks visit : SQL SERVER – Simple Example of Cursor – Sample Cursor Part 2

This is the simplest example of the SQL Server Cursor. I have used this all the time for any use of Cursor in my T-SQL.
DECLARE @AccountID INT
DECLARE
@getAccountID CURSOR
SET
@getAccountID = CURSOR FOR
SELECT
Account_ID
FROM Accounts
OPEN @getAccountID
FETCH NEXT
FROM @getAccountID INTO @AccountID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT
@AccountID
FETCH NEXT
FROM @getAccountID INTO @AccountID
END
CLOSE
@getAccountID
DEALLOCATE @getAccountID

Reference: Pinal Dave (http://www.SQLAuthority.com), BOL