Click here to Skip to main content
15,868,051 members
Articles / Database Development / SQL Server / SQL Server 2008
Tip/Trick

Read a file using transact-sql.

Rate me:
Please Sign up or sign in to vote.
5.00/5 (10 votes)
8 Jul 2010CPOL 32.7K   12   1
How easy to read a file without using of the seven extended stored procedures.

Introduction


To read the content of a file (for example read the text data from a *.txt, doc, rtf etc file) using transact-sql statement. We are familiar with the use of the seven extended stored procedures[^], especially when we want to read / write file and ole automation as well.

However; I would like to show you how easy to read a file without using of the seven extended stored procedures. We will use only the master.dbo.xp_cmdshell stored procedure for doing this.

For more information about master.dbo.xp_cmdshell stored procedure can be found at this link.[^]

A sample sql script is given below:


Script
SQL
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
--      Author:	Md. Marufuzzaman.
-- Create date: 
-- Description:	Get the content from files. 
-- =============================================
-- EXEC [dbo].[spGetText] @Path = 'C:\MyTextFile.txt'

ALTER PROCEDURE [dbo].[spGetText] 
(
	@Path	VARCHAR(500)
)
AS
BEGIN

DECLARE @Command VARCHAR(255)
SELECT @Path = ISNULL(@Path,'C:\Windows\System32\license.rtf') 
PRINT 'Path: ' + @Path

CREATE TABLE #Xml(dataRow VARCHAR(MAX))
	IF @Path IS NOT NULL AND LEN(@Path) > 10
		BEGIN
			SELECT @Command = 'type ' + @Path
			INSERT INTO [dbo].[#Xml] 
					EXEC master.dbo.xp_cmdshell @Command
		END
	IF @@ROWCOUNT <> 0
		BEGIN
			SELECT * FROM [dbo].[#Xml]
		END
			
DROP TABLE #Xml

END
GO

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Comments and Discussions

 
GeneralYou realize your @Command variable is too small for your 500... Pin
Andrew Rissing8-Jul-10 4:42
Andrew Rissing8-Jul-10 4:42 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.