Reading a text file using xp_cmdshell

At times you need to read the contents of a text file using SQL commands. You may want to read the results into a SQL result set or you may want to set the contents of the text file to a variable.
Xp_cmdshell is an extended stored procedure that enables SQL developers and SQL administrators to run dos commands on the underlying operating system.

Now let us assume that the name of the text file we need to read is Test.txt and it is located in the root folder of C drive. Open the command prompt by running the cmd command prompt on the RUN dialog box. On the command prompt, type the command shown in the below screen shot and hit Enter
'type c:\Test.txt'

 

The contents of the Test.txt text file will be displayed on the console. Now we will use t-sql code to run this "type @filename" dos command successfully on the Query Analyzer by the help of the extended stored procedure xp_cmdshell

EXEC MASTER.DBO.XP..CMDSHELL 'TYPE C: \TEST.TXT'

If you run the above command on your local instance the xp_cmdshell will be able to list the contents of the text file in the result pane.
We can use a variable to store the result and read the content in a result set. To do that, we need to create a temporary temple and use @file_content and @new_line.

SET NOCOUNT ON

CREATE TABLE #FILE_CONTENTS

             (

                          LINE_NUMBER   INT IDENTITY,

                          LINE_CONTENTS VARCHAR(MAX)

             )

DECLARE @FILE_CONTENTS VARCHAR(MAX)

DECLARE @NEW_LINE      CHAR(2)

SET @NEW_LINE =        CHAR(13) + CHAR(10)

INSERT #FILE_CONTENTS

EXEC MASTER.DBO.XP_CMDSHELL 'TYPE C:\TEST.TXT'

SELECT @FILE_CONTENTS = ISNULL(@FILE_CONTENTS, '')

+ @NEW_LINE + ISNULL(LINE_CONTENTS, '')

FROM   #FILE_CONTENTS

DROP TABLE #FILE_CONTENTS

SELECT @FILE_CONTENTS

SET NOCOUNT OFF

The Windows process generated by xp_cmdshell has the same security rights as the SQL Server service account. xp_cmdshell operates synchronously. Control is not returned to the caller until the command-shell command is completed. It can be enabled and disabled by using the Surface Area Configuration tool and by executing sp_configure.

Xp_cmdshell Proxy Account:

When it is called by a user who is not a member of the sysadmin fixed server role, xp_cmdshell connects to Windows by using the account name and password stored in the credential named xp_cmdshell_proxy_account. If this proxy credential does not exist, xp_cmdshell will fail.
The proxy account credential can be created by executing sp_xp_cmdshell_proxy_account. As arguments, this stored procedure takes a Windows user name and password. For example, the following command creates a proxy credential for Windows domain user 'Home\Subashini' and the Windows password 'sdfh%dkc93vcMt0'.

EXEC SP_XP_CMDSHELL_PROXY_ACCOUNT 'HOME\SUBASHINI',’DPUN%SU14BA93VCVTM0'

It requires CONTROL SERVER permission to execute xp_cmdshell.