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.

Script to migrate Sp_Configure settings between SQL Servers

While setting up a new server, you will expect the server to have the same configuration settings similar to the old server.  In that situation you may use the following query to script out the configuration settings from the old server.

Run this on your old server

EXEC SP_CONFIGURE 'show advanced options' , 1;

GO

RECONFIGURE;

GO

DECLARE @spConfigVal TABLE

      ([name] VARCHAR(255)

      ,[minimum] INT

      ,[maximum] INT

      ,[config_value] INT

      ,[run_value] INT)

INSERT INTO @spConfigVal

EXEC SP_CONFIGURE

SELECT      'EXEC sp_configure ''' + name + ''', ' + CAST(config_value AS VARCHAR)

FROM  @spConfigVal

GO

EXEC SP_CONFIGURE 'show advanced options' , 0;

GO

RECONFIGURE;

GO

The output of the above script looks like this,

EXEC SP_CONFIGURE 'Ad Hoc Distributed Queries', 1

EXEC SP_CONFIGURE 'Agent XPs', 1

EXEC SP_CONFIGURE 'clr enabled', 1

EXEC SP_CONFIGURE 'cost threshold for parallelism', 5

EXEC SP_CONFIGURE 'cursor threshold', -1

EXEC SP_CONFIGURE 'Database Mail XPs', 1

EXEC SP_CONFIGURE 'default full-text language', 1033

EXEC SP_CONFIGURE 'default trace enabled', 1

EXEC SP_CONFIGURE 'fill factor (%)', 80

EXEC SP_CONFIGURE 'ft crawl bandwidth (max)', 100

EXEC SP_CONFIGURE 'ft notify bandwidth (max)', 100

EXEC SP_CONFIGURE 'max full-text crawl range', 4

EXEC SP_CONFIGURE 'max server memory (MB)', 10000

EXEC SP_CONFIGURE 'max text repl size (B)', 65536

EXEC SP_CONFIGURE 'min memory per query (KB)', 1024

EXEC SP_CONFIGURE 'min server memory (MB)', 10000

EXEC SP_CONFIGURE 'nested triggers', 1

EXEC SP_CONFIGURE 'network packet size (B)', 4096

EXEC SP_CONFIGURE 'PH timeout (s)', 60

EXEC SP_CONFIGURE 'query wait (s)', -1

EXEC SP_CONFIGURE 'remote access', 1

EXEC SP_CONFIGURE 'remote login timeout (s)', 20

EXEC SP_CONFIGURE 'remote query timeout (s)', 900

EXEC SP_CONFIGURE 'scan for startup procs', 1

EXEC SP_CONFIGURE 'server trigger recursion', 1

EXEC SP_CONFIGURE 'SMO and DMO XPs', 1

Copy the output and paste it in your new server SSMS window. Before executing this, change the show advanced options to 1 and execute the query. Finally change the advanced options back to 0 state. Run sp_configure to validate the settings between the servers

EXEC SP_CONFIGURE 'show advanced options' , 0;

RECONFIGURE;

GO;

How to recover SQL Server 2005 Database from SUSPECT Mode

A database may go to suspect mode and will not allow you to perform any operation unless and until you repair. The reasons for the database to be in suspect mode are:

Causes:

• A database can go in suspect mode for many reasons like improper shutdown of the database server, corruption of the database files etc. 
• If the DB recovery fails and one of the conditions for a delayed transaction does not qualify or the failure is not associated with a single database page (for example, damage to the transaction log is detected). In this situation, the engine changes the database state to SUSPECT
• To get the exact reason of a database going into suspect mode can be found using the following query,

DBCC CHECKDB ('suspect_db') WITH NO_INFOMSGS, ALL_ERRORMSGS

Output of the above query will give you an error messages.

Workaround:

You can find out the cause of the error by looking into the SQL server error log. If SQL Server has been restarted since the failed recovery, look at previous SQL Server error logs. If the recovery failed because of a persistent I/O error, a torn page, or other possible hardware problem, resolve the underlying hardware problem and restore the database by using a backup. If no backups are available, consider the repair options of DBCC CHECKDB.

• You can restore from a backup. Page-level restore is not an option here, because the problem is not specific to a database page.
• Use DBCC CHECKDB for emergency mode repair.
• You can change the database state to EMERGENCY and try to copy as much data as possible.

The final attempt to resolve the issue is, using ‘REPAIR_ALLOW_DATA_LOSS’.  First you can change the database status to the EMERGENCY mode. This could permit the system administrator read-only access to the database. Only members of the sysadmin fixed server role can set a database to the EMERGENCY state.

Then set the database to the single-user mode. Single user mode allows you to recover the damaged database.

Then run DBCC CheckDB command. This command checks the allocation, structural, logical integrity and errors of all the objects in the database. When you specify ‘REPAIR_ALLOW_DATA_LOSS’ as an argument of the DBCC CheckDB command, the procedure will check and repair the reported errors. But these repairs can cause some data to be lost.

EXEC sp_resetstatus 'suspect_db';

ALTER DATABASE 'suspect_db'

SET EMERGENCY

DBCC checkdb ('suspect_db')

ALTER DATABASE 'suspect_db'

SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB ('suspect_db', REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE 'suspect_db'

SET MULTI_USER

If the above script runs without any problem, you can bring your database back to the multi user mode.
Note: Using DATA LOSS repair options can lead to other problems. This is not a recommended way to recover the database. The database should be restored from a backup made earlier to the corruption, rather than repaired.