Tag Archives: XP_CMDSHELL

TableDiff utility to compare data between two databases

Introduction
As a database professional, we might come across in situation where we need to compare data row by row or column wise between two tables which either resides in same database or in same instance or may be in different instance in different server.
What do you in this situation?
1.) Do you write down script of your own?
2.) Do you use any third party software?
3.) Do you use “TableDiff” utility comes with SQL Server itself?
Third option, out of all of the above, seems good to me as we neither need to invent the zero again by writing down the script by our own nor we need to pay extra money to compare data.
“TableDiff” is one of the wonderful and oldest utility provided by Microsoft. It works fine with SQL Server 2000 to the latest SQL Server edition. However, I am providing you the script and example from my SQL Server 2008 instance.
Getting Ready

Before you move forward, you need to find out two tables whose data you wanted to compare. It might be in publisher/subscriber in replication, it might be in two different databases you are using for scale out or may be anywhere else.
If you don’t have this situation at the moment in your environment, don’t worry, I will be giving a script to raise the scenario to test “TableDiff” utility.


How to do it…

1.)    Open New Query window in you SQL Server
2.)    Create two different database by using following script:
USE master
GO
CREATE DATABASETableDiffDb1
GO
CREATE DATABASETableDiffDb2
GO
3.)    Create a sample table in “TableDiffDB1” database with following script
 USETableDiffDb1
GO
–if orders table is already there. you can delete it than create new one with name “Orders”
IF OBJECT_ID(‘orders’, ‘U’) IS NOT NULL BEGIN
      DROP TABLE orders
END
GO
–creating table
CREATE TABLEorders(OrderID INT IDENTITY, OrderDate DATETIME, Amount MONEY, Refno INT)
GO
–inserting 1000 sample rows into table
INSERT INTOorders(OrderDate, Amount, Refno)
SELECT TOP1000
      DATEADD(minute, ABS(a.object_id % 50000 ), CAST(‘2010-02-01’ AS DATETIME)),
      ABS(a.object_id % 10),
      CAST(ABS(a.object_id) AS VARCHAR)
FROM sys.all_objects a
CROSS JOINsys.all_objects b
GO
4.)    Creating “Orders” table in second database by copying 900 records (out of total 1000 records) from “Orders” table from “TableDiffDB1” database by using following script.
USE TableDiffDb2
GO
–if orders table is already there. you can delete it than create new one with name “Orders”
IF OBJECT_ID(‘orders’, ‘U’) IS NOT NULL BEGIN
      DROP TABLE orders
END
GO
–creating table
CREATE TABLEorders(OrderID INT IDENTITY, OrderDate DATETIME, Amount MONEY, Refno INT)
GO
–inserting 900 sample rows into table from TableDiffDb1 database’s Orders table
INSERT INTOorders(OrderDate, Amount, Refno)
SELECT TOP900 OrderDate,Amount,Refno FROM TableDiffDb1.dbo.orders
5.)    Now use following command to see the difference between two tables.
exec master..xp_cmdshell‘tablediff -sourceserver [RITESH-SHAH\MSSQL2008] -sourcedatabase TableDiffDb1 -sourcetable Orders -destinationserver [RITESH-SHAH\MSSQL2008] -destinationdatabase TableDiffDb2 -destinationtable Orders -et Difference -f D:\OrdersDifference.sql’

Replace your server instance name in “SourceServer” and “destinationServer” parameter in above given command and you will get one .SQL file in D drive. Running that SQL file will insert all missing records in “Orders” table of “TableDiffDb2” database as it shows you the list of all missing records there.

There’s more…

I would like to draw your attention to some of the facts which can help you if you don’t find “TableDiff” working in your environment.
Remember that “TableDiff.exe”  file resides in installation directory of SQL Server by default which is “C:\Program Files\Microsoft SQL Server\100\COM” in my case.  So, there is chance that “TableDiff” command is not accessible via DOS prompt, you have to set path for “TableDiff” in “ServerVariable”.
You can reach “ServerVariable” by “MY Computer Properties > Advanced System Settings > Advanced > Environment Variables > System Variables > PATH
If you find any path under “PATH” in “ServerVariable”, you can put “;” (semicolon) after that path and can add your path for “TableDiff”.
Generally people tend to use “TableDiff” from DOS prompt itself or via .bat (batch file) file but I have used “xp_cmdshell” extended stored procedure to show the use of command right from SQL Server but there may be a chance that “xp_cmdshellis disable in your environment. If your security constraint allows, you can enable “xp_cmdshell”. For more details about the steps, click here.
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Create comma separated file from SQL Server 2008 with BCP and XP_CMDSHELL

I was assigned a task few days back to generate comma separated file from SQL Server table, It is very easy to do so with BCP utility. Generally we used to use BCP from DOS prompt but herewith I am going to use BCP right from my SSMS with XP_CMDSHELL.

 

use Adventureworks

CREATE TABLE [dbo].[emps](

[Name] [varchar](50),

[Dept] [varchar](10),

[Company] [varchar](15)

) ON [PRIMARY]

GO

 

–insert records

INSERT INTO emps

SELECT ‘RITESH’,‘MIS’,‘ECHEM’ UNION ALL

SELECT ‘Rajan’,‘MIS’,‘mar’

 

 

declare @sql varchar(1000)

–creating emps.txt file in D drive from just created table above

select @sql = ‘bcp Adventureworks..emps out d:\emps.txt -c -t, -T -S ‘+ @@servername

–print @sql

–generally, you should run BCP from command prompt, if you wish to do it from

–SSMS, you have to use xp_cmdshell

exec master..xp_cmdshell @sql

 

 

If your xp_cmdshell is disable, there is one small article to enable it at:
http://www.sqlhub.com/2009/05/enable-xpcmdshell-in-sql-server-2008.html

I have written some more interesting article on BCP which you can find at
http://www.sqlhub.com/2009/03/bcp-or-bulk-copy-program-in-sql-server.html
http://www.sqlhub.com/2009/03/bcp-export-data-from-sql-server-to-flat.html
http://www.sqlhub.com/2009/03/bulk-insert-bulk-copy-or-bcp-difference.html

 

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Enable XP_cmdshell in SQL Server 2008

XP_CMDSHELL is very useful extended stored procedure in Microsoft SQL Server 2008 which will give you power to run command as an operating system power shell. By default it is disabled in any Microsoft SQL server version due to security reasons but you can enable it for your need. Herewith, I am giving you T-SQL commands to enable it.
–list all setting which you can configure with SP_Configure stored procedure
–you may not be able to see XP_CMDSHELL command in the list.
EXECUTE SP_CONFIGURE
–see full list of command by making show advanced options ON
EXECUTE SP_CONFIGURE ‘show advanced options’, 1
RECONFIGURE WITH OVERRIDE
GO
–enable xp_cmdshell command
EXECUTE SP_CONFIGURE ‘xp_cmdshell’, ‘1’
RECONFIGURE WITH OVERRIDE
GO

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah