Tag Archives: t-sql

Compare Data in Two SQLServer Tables without Any 3rd-Party Tool

For comparing data in tables you don’t need any 3rd party tool, SQL Server ships with the tablediff utility which can be used to compare the data in two tables. You can use it for troubleshooting replication. This utility can be used from the command prompt or in a batch file to perform the following tasks:

  • A row by row comparison between a source table in an instance of Microsoft SQL Server acting as a replication Publisher and the destination table at one or more instances of SQL Server acting as replication Subscribers.
  • Perform a fast comparison by only comparing row counts and schema.
  • Perform column-level comparisons.
  • Generate a Transact-SQL script to fix discrepancies at the destination server to bring the source and destination tables into convergence.
  • Log results to an output file or into a table in the destination database.

Lets see how to use it…

For testing i’ve created two tables Table1 and Table2 in DatabaseA and DatabaseB respectively on my local Instance, Then inserted few records in each table.

–On ServerA

create database DatabaseA

go

use DatabaseA

go

create table Table1(

tid int primary key,

tname varchar(20))

go

insert into Table1 values(1,’Sarab’)

insert into Table1 values(13,’Amit B’)

insert into Table1 values(18,’Amit K’)

insert into Table1 values(21,’Sachin’)

–ServerB(I’ve created this on my local instance however, You can create this on another server\Instance)

create database DatabaseB

go

use DatabaseB

go

create table Table2(

tid int primary key,

tname varchar(20))

go

insert into Table2 values(1,’Sarab’)

insert into Table2 values(13,’Amit B’)

insert into Table2 values(12,’Rishu’)

insert into Table2 values(18,’MV Priyank’)

The source table in the comparison must contain at least one primary key, identity, or ROWGUID column.

Now if you query both the tables you can see that I’ve purposely inserted 2 same records in both the tables (tid 1 & tid 13 marked Blue in the screenshot) and inserted same primary key with different values for tname (tid 18 marked Red in the screenshot) to show you how TableDiff Utility consider these scenarios.

1_SQL_Server_Compare_the_Data_in_Two_Tables_without_Any_3rd_Party_Tool

 

Location of the Utility File:
The TableDiff.exe Utility can be found at this location: C:\Program Files\Microsoft SQL Server\100\COM

Note – In case you are using SQL Server 2005 replace 100 with 90.

To start the action launch command prompt and locate the directory having TableDiff.exe utility, then execute the command below:

TableDiff.exe -SourceServer . -SourceDatabase DatabaseA -SourceTable Table1 -DestinationServer . -DestinationDatabase DatabaseB -DestinationTable Table2 -et difft1 -f d:\my_Diff_File.sql

2_SQL_Server_Compare_the_Data_in_Two_Tables_without_Any_3rd_Party_Tool

 

Parameters used:

Most of the parameters are self explanatory, the odd ones I am explaining below:

-et table_name
Specifies the name of the result table to create. If this table already exists, -DT must be used or the operation will fail.

-f [ file_name ]
Generates a Transact-SQL script to bring the table at the destination server into convergence with the table at the source server. You can optionally specify a name and path for the generated Transact-SQL script file. If file_name is not specified, the Transact-SQL script file is generated in the directory where the utility runs.
To get the complete list of all supported parameters visit: http://msdn.microsoft.com/en-us/library/ms162843.aspx

When -et parameter is used, the result table will be created in Destination Database. This table returns all rows where the data got mismatched based on primary (or Unique) Key including the script to resolve the conflict. It also returns all those rows which are only present at source or destination.
Here’s a screenshot of the output:

3_SQL_Server_Compare_the_Data_in_Two_Tables_without_Any_3rd_Party_Tool

 

And -f parameter will generate a file with T-SQL Script named my_Diff_File.sql which will have all the commands to resolve all the conflicts. The Output looks like this:

— Host: .

— Database: [databaseB]

— Table: [dbo].[table2]

DELETE FROM [dbo].[table2] WHERE [tid] = 12

UPDATE [dbo].[table2] SET [tname]=N’Amit K’ WHERE [tid] = 18

INSERT INTO [dbo].[table2] ([tid],[tname]) VALUES (21,N’Sachin’)

Points to consider:

  • The tablediff utility cannot be used with non-SQL Server servers.
  • Tables with sql_variant data type columns are not supported.

Permissions Required:

  • To compare tables, you need SELECT ALL permissions on the table objects being compared.
  • To use the -et option, you must be a member of the db_owner fixed database role, or at least have CREATE TABLE permission in the subscription database and ALTER permission on the destination owner schema at the destination server.
  • To use the -dt option, you must be a member of the db_owner fixed database role, or at least have ALTER permission on the destination owner schema at the destination server.
  • To use the -o or -f options, you must have write permissions to the specified file directory location.

Security Note: When possible, supply security credentials at runtime. If you must store credentials in a script file, you should secure the file to prevent unauthorized access.

 

Have Fun!

Regards

Sarabpreet Anand

Calculate Number of Leaves – excluding weekends

Hi Friends,

Sometime back I was answering questions on TechNet Forum when I saw an interesting and genuine problem.

The user wanted to calculate the number of leaves taken by an employee excluding weekends. Now calculating the number of leaves wasn’t a big deal all we need to do is just use datediff function but excluding the weekends was something which forced me to think harder.

Then I started digging into this and finally I was able to make logic.

Here is the script:  (Updated)

Direct Link: (Updated)

It works pretty well

For the sake of simplicity I’m creating the table with just three columns:

  1. leave_start
  2. leave_end
  3. leave_working_days

Here is the output:

1_SQL_Server_T-SQL_Calculate_Number_of_Leaves_excluding_weekends

Do let me know your way to tackle this problem – by way of leaving a comment.

Enjoy!

Regards

Sarabpreet Anand

Tech-Meet by Delhi User Group | 01-Nov-2014 | Gurgaon

Hi friends!

DElhi User Group1Delhi User Group presents Tech-Meet, your chance to learn Hot technologies from Industry experts.
The half-day free event for professionals in Delhi NCR includes a great line-up of industry-recognized speakers.

The event will start at sharp 9:30 AM and security takes time so please be on time to skip the line and grab the front seat. Winking smile 

I’ll be speaking on Backup and Restore improvements in SQL Server 2014.

REGISTER | Registration required | Limited seats only

Refer the Image below for the complete agenda:

agenda

location

See you at the event.

 

Thanks,
Sarabpreet Singh

Find Users with DBOwner Database role

Scenario: Auditors need a list of Users who have DBOwner Database role on any of the database which are hosted on Tier 1 Servers. Giving this information was Very easy for few servers where the DBCount was around 10 to 20, but we also have 8 SharePoint servers each hosting 200-350 DBs. Extracting this information on all these Servers was a pain, to ease things I created a script which can get all this information on a single execution.

This script can help you to extract the list of users from all databases who have DBOwner role. Once you execute the query you’ll get three column output for the query, three columns are: Database_Name, Role_Name, DBUser_Name. All the column names are self-explanatory.

You can download the script from this link: Find_DBOwners_from_All_Databases.sql

This is how the output will look like:

Find_DBOwner_all_Dbs

 

Note: Tested successfully on SQL Server 2012.

Enjoy!
Happy Learning 🙂

Thanks,
Sarabpreet Singh Anand
Subscribe now to get latest Tips\blog posts in your Inbox

SQL Server Day | Gurgaon | 8-Sept-2012 + Hiring Drive for Microsoft India

SQLServerGeeks.com cordially invites you to SQL Server Day, the quarterly dose of SQL Server knowledge & learning. And after multiple successful events in Bangalore, Kolkata, Mumbai & Delhi NCR, we are coming to Gurgaon, once again! This time SQL Server Day event has been customized for SQL Server Developers. We bring you 3 rock star speakers and 3 intriguing sessions related to SQL Server Development.

And Don’t forget to bring your CV and meet the Microsoft India Recruitment Team Smile

Details

Session & Speaker details

Venue & Contact details

Date & Time

Welcome Note (15 mins) by Amit Bansal(MVP-SQL Server)

Tips & Tricks of Transact SQL
by Sarabpreet Singh Anand, (Vice President, SQLServerGeeks.com)

Session by Microsoft IDC Recruitment Team

Optimizing Transactional and Procedural code by Amit Bansal (Director, Peopleware India, MVP-SQL Server)

SQL Server Myth-buster for Developers by Ahmad Osama

Closing Note (15 mins)

Microsoft Corporation (I) Pvt. Ltd.
DLF Phase 3, Cyber Greens,9th Floor, Tower A, DLF Cyber City, Sector 25A (Near Shankar Chowk)

Note: Refreshments will be served

 

September 8, 2012 (Saturday)
1.30 pm to 5.30 pm

Note: The event will start sharp at 2.00 pm. Please come by 1.30 pm for registration. (Due to security policies, registration takes time. Please come with a valid photo ID)


clip_image001[4]

You can register @ http://sqlserverdaygurgaon8sep2012.eventbrite.com/

8-sep-2012-t-sql-tips-sessions-gurgaon

See you at the event.