sysindexes and syscolumns of remote server

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

Transact-SQL (2005)

sysindexes and syscolumns of remote server

Author

Topic

ramesh31
Starting Member

11 Posts

Posted - 2007-09-10 : 21:39:46

Hi,
I have to compare sysindexes & syscolumns tables of my local
server with another server .
I'm using Opendatasource to connect to the remote server,
but the number of records return by opendatasource is less and
when login to remote server and run the same query it gives me
different result.
I'm using admin user to login in opendatasource.

Did i miss anything.

Thanks in Advance

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-09-10 : 22:08:14

Did you specify db name in opendatasource?

ramesh31
Starting Member

11 Posts

Posted - 2007-09-11 : 00:33:03

yes I have given DB name in Opendatasource

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-11 : 03:48:19

post the query....

--------------------
keeping it simple...

ramesh31
Starting Member

11 Posts

Posted - 2007-09-11 : 11:48:37

select @sql='select A.id,A.name,''-'' as [user],''I'' as xtype from opendatasource(''SQLOLEDB'',''Data Source=' +@SERVERNAME1+ ';User ID=james; Password=pwderop'').' + @dbname1+
'.dbo.sysindexes A inner join '+@dbname1+ '.dbo.sysobjects B on A.id=B.id where b.xtype=(''U'')'
exec(@sql)

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-09-11 : 23:25:25

Did you get correct number of rows on remote server without join?

ramesh31
Starting Member

11 Posts

Posted - 2007-09-13 : 19:25:02

YES ,I got correct number of rows without join (query only the syscolumns)
but i have join sysobjects bcos i have to display column name and corresponding table name

nr
SQLTeam MVY

12543 Posts

Posted - 2007-09-13 : 19:44:32

The sysobjects is on your own server not the remote one.
Try using openrowset something like
select @sql='select * from openrowset from opendatasource(''SQLOLEDB'',
''' +@SERVERNAME1+ ''';''james'';''pwderop'',''select A.id,A.name,''''-'''' as [user],''''I'''' as xtype from ' + @dbname1+
'.dbo.sysindexes A inner join '+@dbname1+ '.dbo.sysobjects B on A.id=B.id where b.xtype=(''''U'''')'')'

otherwise something like
select @sql='select A.id,A.name,''-'' as [user],''I'' as xtype from opendatasource(''SQLOLEDB'',''Data Source=' +@SERVERNAME1+ ';User ID=james; Password=pwderop'').' + @dbname1+
'.dbo.sysindexes A inner join opendatasource(''SQLOLEDB'',''Data Source=' +@SERVERNAME1+ ';User ID=james; Password=pwderop'').'+@dbname1+ '.dbo.sysobjects B on A.id=B.id where b.xtype=(''U'')'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources