SQL Server: How to convert image data type to plain text and back?

Following is a demonstration of one of the ways of using the SQL Convert function to convert image data type to plain text and back. FYI: Image data type is used to store binary data and can store variable-length binary data from 0 through 2^31-1 (2,147,483,647) bytes [1].

Microsoft advocates to avoid the further using of text, ntext and image data types since these data types will be removed from future versions of SQL Server. Further reading can be done about at Using Large-Value Data Types [2].

–The following example shows how plain text
–can be converted to image type data
SELECT CONVERT(binary(8), ‘Caveman’) AS ‘text to binary’

OUTPUT: text to binary
—————————–
0×436176656D616E00

–The following example shows how image type data
–can be converted to Text
SELECT CONVERT(char(8), 0×436176656D616E00) AS ‘binary to text’

OUTPUT: binary to text
—————————–
Caveman

Tip: CAST vs CONVERT function
Both these functions can be used for the explicit conversion of an expression of a datatype to another [1]. CONVERT function is more powerful of the two functions in give us more flexibility during conversion.

Using CAST:
CAST ( expression AS data_type )

Using CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Reference:
1. MSDN Online.
2. Using Large-Value Data Types

SSIS: Export data from Sql Server 2005 to Excel

Exporting data from a SQL Server 2005 to MS-Excel using SQL Server Integration Services (SSIS) is one of the simple ways of data transfer. This article demonstrates a step-by-step procedure of exporting data from Microsoft SQL Server to Microsoft Excel worksheets.

Update: 06/22/09 – You can also also check out my blog on Exporting data to Excel with out using SSIS now.

1. Open Visual Studio 2005 IDE and create a new Integration Services Project.

2. Add a Data Flow Task item to the Control Flow pane and double click on it.

3. Add a OLE DB Source item to the Data Flow pane and configure it to your Source Sql Server 2005 database.

4. Add an Excel Destination item to the Data Flow pane.

5. Create a connection between the two items by dragging the green arrow on the source item and dropping it on the destination item.

6. Configure the Excel Destination Item so that it points to the destination Excel file, then make sure that the mappings between the source table and the destination table are correct.

7. Compile and execute the package to export the data.

Note: This kind of exporting will work only when you have a pre-formated (with data columns) spread-sheet in the Excel file. Make sure that the Excel file in this context is always closed while exporting data.

Following are some other ways of exporting to an Excel file:

• Use Export wizard from Sql Server 2005.
• Use distributed queries [1]
• Write a C#/VB.Net program. [2]
• etc

References:
1. SQL SERVER – 2005 – Export Data From SQL Server 2005 to Microsoft Excel Datasheet. – Pinal Dave
2. Data Transfer from SQL Server to Excel – Levent Camlibel
3. MSDN Online

SQL Server: How to add an identity column to a table with data.

This article demonstrates a step-by step procedure about how an identity column an be added to a table that has data. There are two ways of adding an identity column to a table with existing data:

1. Create a new table with identity, copy data to this new table then drop the existing table followed by renaming the temp table.

2. Create a new column with identity & drop the existing column

With the second method of implementation the data of the intended identity column cannot be retained, hence we are only interested in the first method. Now lets take a look at how the script for Method 1 can be implemented.

Create TABLE DataTable(id int, name varchar(20) )
Insert into DataTable Values(4, 'test 1')
Insert into DataTable Values(5, 'test 2')
Insert into DataTable Values(6, 'test 3')

--Create a temp table with an identity column
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_DataTable
(
id int NOT NULL IDENTITY (1, 1),
name varchar(20) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_DataTable ON
GO
IF EXISTS (SELECT 1 FROM dbo.DataTable)
INSERT INTO dbo.Tmp_DataTable (id, name)
SELECT id, name FROM dbo.DataTable WITH (HOLDLOCK TABLOCKX)
GO
SET IDENTITY_INSERT dbo.Tmp_DataTable OFF
GO
DROP TABLE dbo.DataTable
GO
EXECUTE sp_rename N'dbo.Tmp_DataTable', N'DataTable', 'OBJECT'
GO
COMMIT

select * from DataTable

id name
——– ———-
4 test 1
5 test 2
6 test 3

Now you have a table with data that has an identity column. Caution must be exercised here to drop and recreate all table constraints and relations.

Tip 1: how to reseed an identity column of a table to a new value

–Lets assume the table needs to be reseeded to a value of 100
Declare @newValue int
set @newValue = 100

DBCC CHECKIDENT (‘MyDataTable’, reseed, @newValue – 1)

Tip 2: how to reset the identity column of a table.

Delete from MyDataTable
DBCC CHECKIDENT (‘MyDataTable’, reseed, 0)

Tip 3: how to find the last inserted identity value.

SQL Server provides are three methods of fetching the last insere.ted identity value and all three methods have different scope with in a database operational context. I think Scope_Identity is the most preferred and secure method of fetching the identity valu

1. select @@IDENTITY returns the last identity value that was inserted into a table by you are by any other entity in the database scope. If there is no identity insertion @@IDENTITY returns NULL.

2. select SCOPE_IDENTITY() returns the last identity value that was inserted into a table by you or your connection in the database scope.

3. select IDENT_CURRENT(‘DataTable’) returns the last identity value of a given table.

Reference:
1. MSDN Online