is exec (exec @execstring) possible?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

SQL Server Development (2000)

is exec (exec @execstring) possible??

Author

Topic

gdeconto
Posting Yak Master

107 Posts

Posted - 2002-04-26 : 13:10:05

I am trying to get a list of columns in a remote table using server, database and table info passed into a stored procedure. I tried doing something like this but haven't been able to figure out a way to make it work. Any help appreciated:

CREATE PROCEDURE getremotetablecolumns @server varchar(255), @db varchar(255), @table varchar(255)
AS

DECLARE @execstring varchar(1024), @execstring2 varchar(1024)

create table #t1 (tablename varchar(255), ordinal_position int, data_type varchar(255), character_maximum_length int)

-- create string to get list of columns in the remote table
SET @execstring = 'exec ' + @server + '.' + @db + '.dbo.sp_executesql N''select column_name as name, ordinal_position, data_type, character_maximum_length from INFORMATION_SCHEMA.COLUMNS where table_name='''' + @table + '''''''

-- store list of columns in the remote table for later use
insert #t1 exec @execstring

-- ?? or do we have to execute the string returned by the exec??
-- set @execstring2 = exec @execstring
-- insert #t1 exec @execstring2

skond
Yak Posting Veteran

55 Posts

Posted - 2002-04-26 : 16:33:02

CREATE PROCEDURE getremotetablecolumns
(
@server varchar(255), @db varchar(255), @table varchar(255)
)
AS

DECLARE @execstring varchar(1024),
@execstring2 varchar(1024)

create table #t1 (tablename varchar(255), ordinal_position int, data_type varchar(255), character_maximum_length int)

-- create string to get list of columns in the remote table
SET @execstring = 'select column_name as name, ordinal_position, data_type, character_maximum_length from ' + @server + '.' + @db + '.INFORMATION_SCHEMA.COLUMNS where table_name='''' + @table + '''''''

-- store list of columns in the remote table for later use
insert #t1 exec (@execstring )

gdeconto
Posting Yak Master

107 Posts

Posted - 2002-04-26 : 16:44:46

Hi skond

[grin] aside from the obvious syntactic errors that I made when I was wearily entering this message in, the corrections you submitted will still not work.

the problem apparently has to do with the number of "." separators when accessing the remote server. for example, I get this error:

Server: Msg 117, Level 15, State 1, Line 1
The object name 'remoteserver.remotedb.dbo.INFORMATION_SCHEMA.' contains more than the maximum number of prefixes. The maximum is 3.

when I try to exec this command:

select column_name as name, ordinal_position, data_type, character_maximum_length from remoteserver.remotedb.dbo.INFORMATION_SCHEMA.COLUMNS where table_name='remotetable'

any other ideas?

skond
Yak Posting Veteran

55 Posts

Posted - 2002-04-26 : 16:47:51

I don't think you need .dbo there.

gdeconto
Posting Yak Master

107 Posts

Posted - 2002-04-26 : 17:19:33

that gets me this error:

Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'remoteserver' does not contain table '"remotedb"."INFORMATION_SCHEMA"."COLUMNS"'. The table either does not exist or the current user does not have permissions on that table.

which is similar to what I had when I first posted my message.

Have you been able to successfully get a list of columns in a remote table on a remote server using server name, db name and table names being passed in as parameters??

If so, was this done with a remote server running sql or other??

gdeconto
Posting Yak Master

107 Posts

Posted - 2002-04-26 : 18:26:54

the information_schema.columns view is located in the master db.

for some reason, it is not available when I connect to the linked_server; if I create a copy of that view in the target database, I can get the column info I need.

unfortunately, this kills the reason for using the information_schema.columns view in the first place; a documented and standardized way of accessing the column data.

sigh.....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources