Compare Databases Between 2 SQL Servers

May 17, 2006

Hello,
I wish to see if the tables from database A server A1 exist in database
A on server B1.
I setup a linked server from my first server (robertcamarda) to a
target (cognos-dev)
This works from robertcamarda:
select count(*) from [cognos-dev].ds_v6_source.dbo.stdmas

and this works:
select * from ds_v6_source.information_schema.tables order by
table_name

but this wont work:
select * from [cognos-dev].ds_v6_source.information_schema.tables order
by table_name

Error:
Msg 117, Level 15, State 1, Line 1
The object name 'cognos-dev.ds_v6_source.dbo.information_schema.tables'
contains more than the maximum number of prefixes. The maximum is 3.

I want to do something like:
select * from ds_v6_source.information_schema.tables
where table_name not in (select table_name from
[cognos-dev].ds_v6_source.information_schema.tables order by
table_name)

so I can see of the sql server (robertcamarda) has any missing tables
that exist on the server (cognos-dev)
TIA
Rob
SQL Server 2005 Enterprise

View 3 Replies


ADVERTISEMENT

Transact SQL :: How To Compare Columns On 2 Databases On 2 Different Servers

Aug 12, 2015

I need to compare columns in tables on 1 database on one server versus the same on a 2nd server.

I'm looking for added columns in dbase 1.

Is there a system T-SQL script that can be used for this?

View 4 Replies View Related

Compare Tables Of Different Servers

Oct 8, 2014

I have two databases under two different servers.

Server1:
DB1
Table1

Server2:
DB2
Table2

I need to compare Table 1 & 2 using SQL server and how can I achieve this?

View 2 Replies View Related

SQL 2012 :: Compare Database Across Servers

May 28, 2014

I want to compare the database structures(columns,datatypes..etc) across same databases located in different servers.

View 9 Replies View Related

SQL 2012 :: How To Compare Settings Of Two DB Servers

Sep 24, 2014

I am trying to reproduce a problem that is probably related to how a particular SQL server is configured. Is there a tool or best practice that is useful to compare all of the configuration settings between two SQL Servers (same version)?

View 2 Replies View Related

How To Compare Columns Of Two Tables In Two Different Servers

Mar 18, 2008



I have two table with the same name that reside in different databases. The two database have the same name, but reside in two linked servers
TABLE A: [ServerA].[ProdDB].dbo.[Orders]
TABLE B: [ServerB].[ProdDB].dbo.[Orders]


How do i find out if the two tables have the same number of columns or if the a column that exists on TableA does not exist on TableB.

Does any one have a script that could help me with this task. Thanks

I am using SQL Server 2005

View 8 Replies View Related

SQL Script To Compare Two Identical Tables On Two SQL Servers

May 23, 2007

Hi all,
I have two tables A and B on two both tables have similar architectures bout contain some deferent records.

I like to compare them and find and view the differences in records.


Thanks for any help.

Abrahim

View 3 Replies View Related

Compare Databases

Aug 23, 2005

Hi,I have a field TestDescription in tableA in DatabaseA. The data in this field is coming from three different fields in three different fields in DatabaseB.Is there a way to write a script to check if the length of this field TestDescription is greater than length of all those three fields in DatabaseB?Thanks

View 1 Replies View Related

Compare 2 Databases...

Sep 25, 2000

Is there a way by which I can compare 2 databases?...I mean get to know, the differences in the 2 databases, so I can update the other, accordingly....?
Any suggestions would be appreciated.
Thanks in advance:-)

View 4 Replies View Related

How Can I Compare Two Databases?

Sep 20, 2005

I have two databases - I will call them database A and database B.

I want to run some code on database A, and then check if database A became identical to database B.

How can I compare two databases? I want to compare them in terms of structure, not content.

Any suggestions? Thank you!

View 5 Replies View Related

How To Compare Across Two Databases

Dec 13, 2007

I have two databases - version 1 and version 2

I have the same table in both versions but the number of columns has changed from version 1 to version 2.

I can get the list of columns with:
select colid, name, col_text from _SCHEMA
where table_name like
'tableA' order by colid

How can I use something like this to compare tableA in dbo.1 to tableA in dbo.2 and just return the columns that exist in dbo.2 that aren't in dbo.1?

I thought something like this might do it, but I get major errors in execution:
use dbo.2 select colid, name, col_text from _SCHEMA
where table_name like
'tableA' and
name not in (use dbo.1 select name from _SCHEMA
where table_name like
'tableA')
order by colid

View 3 Replies View Related

Compare DataBases......

Jul 3, 2007

Hi



I want Tom Compare two databases to see if there are stroed procedure table that exist in one database

but not exist in the other.....



how do i do it?

Thanks

View 4 Replies View Related

Compare Databases

Dec 8, 2007


The following thread worked fine and i used this one for copying from one database to another database.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2494847&SiteID=1


Now one more question i would to confirm based on the above thread.

Suppose i have 2 records in database1.dbo.Department and 6 records in database2.dbo.Department.

I want to copy 2 records in database2.dbo.Department and 6 records in database1.dbo.Department.

It is just like comparing two databases.

Do i need to follow the same thread for copying databases which is above. I mean to say the same query.

How can i write script for comparing databases in terms of both schema and data.

Regards
Kashif Chotu



View 4 Replies View Related

How To Compare Objects In Two Databases In Sql 2k...

Jun 15, 2003

Hi,


I want to compare the production (say prod_db) objects against development database say dev_db.

In Oracle I would run a query using "minus" that is

select table_name from user_tables@prod_db
minus
select table_name from
user_tables@dev_db

what is the equivalent sql in t-sql(or are there any GUI tools to do this).I understand that from prod_db I need to create a link server to the dev_db to run such a query.

Thanks,
copernicus.

View 6 Replies View Related

Compare Two Databases Using Vbscript

Sep 9, 2004

How to compare db2 and sql7 database using VB script

View 2 Replies View Related

SQL Databases Compare Tool

Jul 23, 2005

Can someone recommend what SQL Database compare tool to useand why?Thank you

View 1 Replies View Related

How To Compare Two Databases Using Their DDLs?

Jul 20, 2005

Hello all!My question is: how to compare two database structures if I only havetheir DDL files? As the result I would expect an sql-script upgratingone database structure to another.The most 3rd-party tools I tested require connection to both databases.*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 1 Replies View Related

Compare Tables In Multiple Databases.

Apr 2, 2002

Hi,

I need to merge tables from several databases. Before the merge the table contents must be checked to rule out any duplicates.
Is there a most efficient way to do it? Thanks to you all.

View 2 Replies View Related

Compare Data Between 2 Sql Server Databases

Nov 20, 2000

How can I create a database link so that I can check data between 2 similar
SQL Server 7 databases ?

View 1 Replies View Related

SQL 2012 :: Compare And Join Databases

Feb 7, 2014

A customer has messed up while moving their databases. After working for a week they found that data is missing in the database.I have two backups, one from the old server and one from the new server today, they have been working in the new one for a week.

I need to compare these two databases and then update the new database with all data that is in the old one but not in the new database. Join the data in the two databases so to say. Both databases are from the same application so they use the same users, schema and so on.

View 9 Replies View Related

Compare Two Databases And Update Objects?

Jul 23, 2005

Is there a way to compare two databases one being an olddatabase and the second being a new database, I willupdate the old database's objects to match the new database'sobjects?Is there SQL code that could do this easily?How do you do this if you deal with the same scenario?Thank you

View 2 Replies View Related

SQL 2012 :: Compare Two Similar Databases (A And B) Stored Procedure?

Dec 3, 2014

Is there any way to compare two similar databases (A & B) stored procedure. I have to find stored procedure in second database B with respect to the difference.

View 7 Replies View Related

SQL Server 2008 :: Easy Way To Compare Contents Of Objects Between 2 Different Databases?

Jul 20, 2015

Is there an easy way to compare the contents of objects between 2 different databases? For example, say I had 2 databases, My_DB_1 and My_DB_2, each with a SEC_User table. Say I wanted to do an object-to-object comparison between databases to see if there were any differences. Here's some sample SQL:

use My_DB_1
select * from sys.dm_sql_referencing_entities('dbo.sec_user', 'object')

use My_DB_2
select * from sys.dm_sql_referencing_entities('dbo.sec_user', 'object')

Say that the result sets above both returned a SEC_GetUser sproc object ref. Is there a way to write SQL that will compare the SEC_GetUser sprocs (and other objects in the above rowsets) from both databases? For example, if My_DB_1.SEC_GetUser returns an extra column in the result set than My_DB_2.SEC_GetUser then I'd like my comparison SQL to return a single column "IsDifferent" with SEC_GetUser as a row....

View 1 Replies View Related

Connect 2 Databases On 2 Servers ???

Sep 4, 2007

 Hi, If you have  2 databases with the exact same table columns, and in the first database in a table column the indentidy seed starts at 1 and finishes at 32,000 can you attatch a second database so that the indentity starts at 32,001and carries on. What happens if you create a site and run out of disk space and need to attach another database which is located on another server??? I am really confused about this.Hope someone can give some links to some articles about this as I can't find any information about it.How can this be done? Thanks 

View 1 Replies View Related

Databases In Remote Servers

May 12, 2006

Hello,This is my first post, and i am looking for help about Databases in remote servers.This is my problem:1) I have a free account in "HostBasket", with 50 mb of space, and 10 mb of SQL Server... with ASP.NET 2 support --> http://www.hostbasket.com/dotnet2.shtml?LinkFrom=100602) They gave me this info for the database:Your SQL Server: sql15.hostbasket.comYour database name: myDatabaseNameYour login for the database: myLoginYour password for the database: myPasswordConnection string for ASP.NET (using ADO.NET with System.Data.SqlClient namespace):Persist Security Info=True;User ID=myLogin;Password=myPassword;Initial Catalog=MyDatabaseName;Data Source=sql15.hostbasket.com 3) Actually, i don't know what to do with that info... I mean, i have read so much info, and all people saying "their way" to do things... oh, I am so confused...4) I read in a website, this: "the database can be uploaded as a FILE saved in the App_Data folder, under the .mdf extension"I read that from : http://dotnetjunkies.com/QuickStartv20/aspnet/doc/data/vwd.aspx5) In the url given above, there is a "step by step" example... i follow it, exactly (i start a asp.net website, i create the database and the table, i show the data with the grids, etc )6) All is OK until here, i mean, when i run the website with VWD, it shows me the info OK.7) But... ¿How can i do this works on the Remote Server i described in 1) and 2) ?I know how to upload my website, in fact i uploaded a very basic one, and all was OK, but when i work with databases starts my problem...So, i am almost sure, that i have an error in the "Conecction String"... connectionString="Data Source=.SQLExpress;AttachDbFilename=|DataDirectory|MiBaseDeDatos.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" ¿How should it be, to work with my free remote server?or,if you have done the "easy way" of uploading the .MDF file in the server (but a server different of mine) please, tell me the "how to..." (and the server you used :p )--------------------Ok guys, i am a Chilean young (i dont speak english very well) and i tried to write my ideas in an easy way, so I REALLY HOPE you can help me.I am a New (very new) on this (vwd , .net , asp.net ) in fact, i am using this just 3 days ago...THANK YOU SO MUCH,Carlos,La Serena, Chile.

View 7 Replies View Related

Transferring Databases Between Servers

Feb 15, 2002

I need some advice on copying databases, stored procedures, views, logins ..etc from a SQL Serevr 7.0 server, to a new SQL Server 2000 server.
Is it better to backup the databases on one server and then restore them to the other. Or id it easier to use the Wizards to Import everything from new server, or to Export everything from the old server ?

Any advice would be appreciated. I need to ensure that I do not miss anything, in particular Stored Procedures in the old master database.

View 2 Replies View Related

Documenting SQL Servers/databases

Mar 27, 2001

I'm fairly new to a DBA role, I've been tasked with documenting all the SQL servers/databases. Is there anywhere on the Web where I could find a standard list of what needs to be documented. Any help/advice would be appreciated.

View 1 Replies View Related

Transferring Databases Between Servers

Jan 11, 2000

I tried using Import/Export to transfer a database from one SQL Server box to another using NT authentication.

The database appeared to transfer OK, however the process crashed with an error suggesting that a SQL Server login was not present.

How are the SQL Server logins transferred ?

Richard

View 2 Replies View Related

How To Connect Two Databases On Two Servers

Aug 23, 2013

We are wanting to know how to connect 2 databases residing on 2SQL servers. The workstation has access to both databases. Join on Cols 1 and 2.

Server1, Database1, Table1, Column1 (Char)
Server2, Database2, Table2, Column2 (VarChar)

View 2 Replies View Related

2 Databases On 2 Servers, 1 Query?

Jan 25, 2008

Hello,

We have adedicated MS-SQL Server for our CRM database and we have another MS-SQL database server for in house applications, written in c#.Net 2.0. To avoid duplicating information already in our CRM database, it would be nice if we could build a query that combined columns from both data sources.Example





Code Snippet

CRM database on Server1
Table Accounts


Columns
AccountID UniqueIdentifier Primary Key

Name NvarChar(50)



Application Database on Server2
Table Transaction


Columns

TransactionID UniqueIdentifier Primary Key

AccountID UniqueIdentifier
Amount Money




Would it be possible to create a T-SQL statement that will select




Code Snippet

Select
Server1.CRM.Accounts.Name,
Server2.Application.Transaction.TransactionID,
Server2.Application.Transaction.AccountID,
Server2.Application.Transaction.Amount



From Server1.CRM.Accounts INNER JOIN Server2.Application.Transaction




ON (Server1.CRM.Accounts.AccountID = Server2.CRM.Transaction.AccountID)


I appreciate there will need to be 2 connections and therefore some credential information would need to be passed too.


Is anything like this possible? I do not wish to copy the Name field from Server1.CRM.Accounts to Server2..Application.Transaction as this is duplication and any change to the value of Server1.CRM.Accounts.Name would not be reflected in the results.


Many thanks for any information provided.

View 3 Replies View Related

How To Run A JOIN Between Databases On Two SQL Servers

Feb 23, 2008





Code Snippet
SELECT ReciptItems.acc_TopicCode, ReciptItems.acc_DetailCode, ReciptItems.acc_CTopicCode,
SUM(ReciptItems.TotalInputPrice + ReciptItems.TotalOutputPrice), a.MoeenName_L1
FROM ReciptItems LEFT OUTER JOIN
Acc_mydbname.dbo.Categories AS a ON ReciptItems.acc_TopicCode = a.TopicCode
GROUP BY ReciptItems.acc_TopicCode, ReciptItems.acc_DetailCode, ReciptItems.acc_CTopicCode, a.MoeenName_L1





How Replace Acc_mydbname with (SELECT AccountDBName FROM Config)

(SELECT AccountDBName FROM Config) ='Acc_mydbname_2008.dbo.'

View 6 Replies View Related

Synchronize 2 Databases On Different Servers

Apr 10, 2006

Hi,

I was wondering if the following is possible: I have 2 SQL Express servers on different machines. One holds the production and one the development database. On a request basis I'd like mirror all the data of the production database to the development database. Right now I have to stop the sql engine, zip the mdf, send it over, attach it to database, etc. and that's quite cumbersome. So, is there an easier way to do this? If not with SQL Express, is it possible with the full version?

Thanks,

Tom

View 1 Replies View Related

Exchange Data Between Databases On Two Servers??

May 29, 2007

Dear all
 
I have tow server on the same intranet. One server has a sql server 2000 database and the other one has SQL server 2005 databse.
 The sql 2000 database has a table called employee. When ever a new employee is inserted in the database i would like the same values to be sent to the sql 2005 database. But this cant be done on the application level. It has to be done in the database. The application level can not be changed.
I was thinking a trigger but how to achieve the writing from one database to another. If they were on the same server then it would be easier but because they are on different servers i dont know how to do it.
 Has anyone had similar issue before?
Any help is apreciated.
 
Sincerely
Dan

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved