comparing columns from 2 servers

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

comparing columns from 2 servers

Author

Topic

shebert
Yak Posting Veteran

85 Posts

Posted - 2009-08-20 : 15:09:21

Hello
i can run this on one server serverA
SELECT
column_name=syscolumns.name
FROM sysobjects
JOIN syscolumns ON sysobjects.id = syscolumns.id
JOIN systypes ON syscolumns.xtype=systypes.xtype
WHERE sysobjects.xtype='U'
and sysobjects.name ='PhlatTrac_Order'

and this on a differnt server serverB

SELECT
Vcolumn_name=syscolumns.name
FROM sysobjects
JOIN syscolumns ON sysobjects.id = syscolumns.id
JOIN systypes ON syscolumns.xtype=systypes.xtype
WHERE sysobjects.xtype='V'
and sysobjects.name ='PhlatTrac_order'

can I run them both in one query analyser I want to compaire the columns to see if the view code can needs modification....

I have lot of views to compare
so i started an ssis package
I created 2 object variables Tablescolumn and ViewColumns
anyone know how I can compare these without temp tables?

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-08-20 : 15:40:07

You can setup a linked server on serverA to serverB.
Then do a SQL query, referencing serverB.databaseB.<schema>.<objectname>

If you don't want to setup a linked server (maybe it's just an adhoc query), look up OPENROWSET.

This will work without the use of temp tables.

shebert
Yak Posting Veteran

85 Posts

Posted - 2009-08-20 : 16:01:32

I was trying linked servers at first with no luck..... but now that i have a second look at it, I can get the data I need from the other server.
SELECT
Vcolumn_name=syscolumns.name
FROM [LD-ERP-SRV].iERP81_Luminus.dbo.sysobjects
JOIN [LD-ERP-SRV].iERP81_Luminus.dbo.syscolumns ON sysobjects.id = syscolumns.id
JOIN [LD-ERP-SRV].iERP81_Luminus.dbo.systypes ON syscolumns.xtype=systypes.xtype
WHERE sysobjects.xtype='V'
and sysobjects.name ='PhlatTrac_order'

Thanks for the Nudge in right direction

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources