Getting information information_schema on a linked

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)

Getting information information_schema on a linked

Author

Topic

the1gadget
Yak Posting Veteran

55 Posts

Posted - 2008-07-17 : 05:50:07

Hi All

I am trying to get the column information out of the information schema from a linked server (all M$ SQL). I need to put this information into a table.

e.g.

insert into MyCols
(Column_Name, Data_Type, Ordinal_Position)
select
Column_Name, Data_Type, Ordinal_Position
from [server].[database].information_schema.columns

If I do this query on it's own I get an error
e.g.

select Column_Name, Data_Type, Ordinal_Position from [server].[database].information_schema.columns

The error is

Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "server" does not contain the table ""database"."information_schema"."columns"". The table either does not exist or the current user does not have permissions on that table.

However I can do

exec [server].[database].dbo.sp_executesql N'select Column_Name, Data_Type, Ordinal_Position from information_schema.columns '

With no problem.

So I think that I just need to get the result into my table.

--
David

the1gadget
Yak Posting Veteran

55 Posts

Posted - 2008-07-17 : 06:09:28

I forgot to say that I have tried

create table ColsData(Column_Name nvarchar(128),Data_Type nvarchar(128),Ordinal_Position int)

insert into ColsData (Column_Name, Data_Type, Ordinal_Position)
exec [server].[database].dbo.sp_executesql N'select Column_Name, Data_Type, Ordinal_Position from information_schema.columns '

But I get the error

OLE DB provider "SQLNCLI" for linked server "server" returned message "The transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Line 1
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "server" was unable to begin a distributed transaction.

And The information is on a version of 2000 (MSDE I think) with the results wanted on a 2005 (developers edition).

--
David

the1gadget
Yak Posting Veteran

55 Posts

Posted - 2008-07-17 : 07:09:24

Hi All

This works but it is not the way I want to do things.

Create a view on the linked server looking at the information_schema.colunms.

Then I can do an insert from this.

--
David

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources