linked server and query table - 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

SQL Server 2008 Forums

Other SQL Server 2008 Topics

linked server and query table

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2009-02-12 : 11:39:55

i have linked to another server (SERVER2) and it see it's tables and views.
how can from a StoredProcedure in on of my databases (Server1) query a table/view on Server2?

thanks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-12 : 12:23:52

Use Four-Part naming Like:

Select col.. from servername.Databasename.Schema.Tables or Views,

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2009-02-12 : 12:29:42

i tried but it dosent recofnize the SERVER2.
i saw an example [url]http://msdn.microsoft.com/en-us/library/ms178532.aspx[/url]
where you can use :
SELECT *
FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')

the problem is that when i try to give him an id of the server (where the OracleSvr example or the name of the server - it dosent except it)
any idea why?

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2009-02-12 : 12:35:01

i tried to use the option to create a script with a query and igot:


SELECT [PhoneNumber]
  FROM [Server2].[Pro].[dbo].[tbl1]

but i got an error :

quote:

OLE DB provider "SQLNCLI10" for linked server "server2" returned message "Unspecified error".
OLE DB provider "SQLNCLI10" for linked server "server2" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI10" for linked server "server2". The provider supports the interface, but returns a failure code when it is used.

(this is when i make al inked server between sql2008 and sql2000)
any idea?

thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-12 : 12:44:25

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/0782364d-dcb0-4c45-8343-5bfc4406ead4/

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2009-02-12 : 12:44:35

ok thw solution is here
[url]http://support.microsoft.com/kb/906954[/url]

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-12 : 12:45:51

quote:
Originally posted by pelegk2

ok thw solution is here
[url]http://support.microsoft.com/kb/906954[/url]

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Openquery should work for this.

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2009-02-12 : 13:11:04

the problem is when i try the Openquery i get an error that like i wrote above :

quote:

SELECT *
FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')

the problem is that when i try to give him the ID of the server (where the OracleSvr example or the name of the server - it dosent except it)
any idea why?

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2009-02-14 : 09:18:47

well i reopend the query window
and with OPENQUERY it work!
does any one know's if i can create a "linked server " with "berkly db"?

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-14 : 10:25:22

quote:
Originally posted by pelegk2

well i reopend the query window
and with OPENQUERY it work!
does any one know's if i can create a "linked server " with "berkly db"?

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Good . What is berkly db?

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2009-02-14 : 10:56:53

[url]http://www.oracle.com/technology/products/berkeley-db/index.html[/url]

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-14 : 11:24:50

quote:
Originally posted by pelegk2

[url]http://www.oracle.com/technology/products/berkeley-db/index.html[/url]

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

This forum is for SQL Server.

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2009-02-14 : 14:28:55

i know but i want to link SQL to berkeley

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-14 : 14:30:09

quote:
Originally posted by pelegk2

i know but i want to link SQL to berkeley

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Didn't it work with OPENQUERY?

Flapper_MK
Starting Member

2 Posts

Posted - 2009-10-06 : 10:26:01

run this on the master db of the sql 200 server

Flapper_MK
Starting Member

2 Posts

Posted - 2009-10-06 : 10:26:17

run this on the master db of the sql 200 server

create procedure sp_tables_info_rowset_64
@table_name sysname,
@table_schema sysname = null,
@table_type nvarchar(255) = null
as
declare @Result int set @Result = 0
exec @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type
go

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2009-10-06 : 15:46:10

Flapper_MK how can this procedure help me?
the goal here is to create a linked server with Berkly DB.
to another sql server i can create a linked server withought any problem

thanks
Peleg

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources