sp_help and openquery - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

sp_help and openquery

Author

Topic

zwheeler
Starting Member

25 Posts

Posted - 2011-08-09 : 12:56:47

i have a linked server, i need to do a describe where i can get the field name and the fields data type:

a1_pipe_snapshot is the table name
I have tried the following and none of it works.
SELECT * FROM OPENQUERY (daps_linked,''SET FMTONLY OFF EXEC sp_help' + ' a1_pipe_snapshot'')

select * from openquery(daps_linked, 'show colums from a1_pipe_snapshot;')

SELECT * FROM openquery(daps_linked,'Exec sp_help a1_pipe_snapshot')

SELECT * FROM openquery(daps_linked,''Exec sp_help' + ' a1_pipe_snapshot'')

WHAT AM I DOING WRONG

this should be very easy

thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-09 : 13:23:48

might be this?
SELECT * FROM openquery(daps_linked,'Exec sp_help ''a1_pipe_snapshot''')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-08-09 : 13:24:18

Use INFORMATION_SCHEMA instead:

SELECT * FROM daps_linked.dbName.information_schema.columns WHERE table_name='a1_pipe_snapshot'

Change "dbName" to match the database the table is in.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources