Comparing Tables between MS Access and SQL Server

Question

Comparing Tables between MS Access and SQL Server

Raghavender Chavva

SSCrazy Eights

Points: 8669
More actions
January 16, 2012 at 3:30 am

#249622

Hi All,
We have migrated Some tables from MS Access to SQL Server.
Now we need to compare those tables with the Access tables, how can we do that comparision?
Thank You.
Regards,
Raghavender Chavva

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

patrickmcginnis59 SSCertifiable Points: 6483 More actions · Answer 1

Raghavender (1/16/2012)
Hi All,
We have migrated Some tables from MS Access to SQL Server.
Now we need to compare those tables with the Access tables, how can we do that comparision?

How about exporting both sets of tables as delimitted text files, sort them, and do a text compare? Obviously you'll have to consider whether all columns will compare correctly, I could imagine some sources of differences like data time conversion to text, etc...

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 2

Another tool to keep in mind is the SQL Server Migration Assistant (SSMA) found here.

It may not show you the differences, but may help in your overall project/scope.

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Jared SSC Guru Points: 61793 More actions · Answer 3

I would create a linked server to Access: http://social.msdn.microsoft.com/Forums/en/sqlgetstarted/thread/589ca193-3541-4c5e-965c-4c515d6b476b

Then compare tables through SQL Server.

Jared
CE - Microsoft

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 4

Please let us know what you decided to do and if it helped.

My blog

Raghavender Chavva SSCrazy Eights Points: 8669 More actions · Answer 5

Sure, I think its better to compare using linked servers.

Thank You.

Regards,
Raghavender Chavva

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 6

Raghavender (1/19/2012)
Sure, I think its better to compare using linked servers.

Thank you for sharing!

My blog

Divine Flame SSCoach Points: 15943 More actions · Answer 7

carmellabanker (1/19/2012)
SQL Server Enterprise Manager allows for enterprise-wide configuration and management of SQL Server and SQL Server objects. SQL Server Enterprise Manager provides a powerful scheduling engine, administrative alert capabilities, and a built-in replication management interface. You can also use SQL Server Enterprise Manager to:
Manage logins and user permissions.
Create scripts.
Manage backup of SQL Server objects.
Back up databases and transaction logs.
Manage tables, views, stored procedures, triggers, indexes, rules, defaults, and user-defined data types.
Create full-text indexes, database diagrams, and database maintenance plans.
Import and export data
Transform data.
Perform various Web administration tasks.
By default, SQL Server Enterprise Manager is installed by SQL Server Setup as part of the server software on computers running the Microsoft Windows NT® operating system, and as part of the client software on computers running Windows NT and the Microsoft Windows 95® operating system. You will likely launch Data Transformation Services (DTS) from the SQL Server Enterprise Manager interface.
Search Engine Optimizing | Search Engine Marketing | Social Media Marketing

What's the point of saying all this here? I think OP has asked a totally different question :cool:.

Sujeet Singh

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 8

I agree with Divine. What was your point Carmella?

My blog

patrickmcginnis59 SSCertifiable Points: 6483 More actions · Answer 9

Divine Flame (1/19/2012)
carmellabanker (1/19/2012)
SQL Server Enterprise Manager allows for enterprise-wide configuration and management of SQL Server and SQL Server objects. SQL Server Enterprise Manager provides a powerful scheduling engine, administrative alert capabilities, and a built-in replication management interface. You can also use SQL Server Enterprise Manager to:
Manage logins and user permissions.
Create scripts.
Manage backup of SQL Server objects.
Back up databases and transaction logs.
Manage tables, views, stored procedures, triggers, indexes, rules, defaults, and user-defined data types.
Create full-text indexes, database diagrams, and database maintenance plans.
Import and export data
Transform data.
Perform various Web administration tasks.
By default, SQL Server Enterprise Manager is installed by SQL Server Setup as part of the server software on computers running the Microsoft Windows NT® operating system, and as part of the client software on computers running Windows NT and the Microsoft Windows 95® operating system. You will likely launch Data Transformation Services (DTS) from the SQL Server Enterprise Manager interface.
What's the point of saying all this here? I think OP has asked a totally different question :cool:.

Spamming, note the links. Click report button!