September 9, 2008 at 12:28 pm
I need to link from one server to another server some tables and set up some jobs for them.How do I do that?
September 9, 2008 at 12:38 pm
The easiest way is usually to set up a linked server. There's a section in Books Online about how to do that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 10, 2008 at 10:17 am
How can I get data from linked server? I do this:SELECT *
FROM CSWReportDatabaseUPSMD.CSWReports.package_lists and it's not working.CSWReportDatabaseUPSMD=name of the linked server,name of database from the linked server,name of the table from linked server. Thank you
September 10, 2008 at 11:45 am
When you say "it doesn't work", do you mean you get an error message, or you get zero rows of data, or something else?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 10, 2008 at 12:02 pm
Can you show me how?Thank you
September 10, 2008 at 12:10 pm
Like
Select * from linkedservername.databasename.schema.table or view name
eg-- Select * from server1.master.dbo.sysdatabases
September 10, 2008 at 12:54 pm
Select * from CSWreportdatabase.CSWReportDatabase.package_lists. This what I did and still get an error:Msg 208, Level 16, State 1, Line 1
Invalid object name 'CSWreportdatabase.CSWReportDatabase.package_lists'.
CSWreportdatabase = linked server,CSWReportDatabase=database in linked server,package_lists=table in linked server. Please help me.Thank you
September 11, 2008 at 7:09 am
You are missing the "schema". Try using the four part naming convention specified above.
September 11, 2008 at 7:10 am
You're missing the schema between the database name and the table. It's probably dbo.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 11, 2008 at 7:29 am
Thank you, but still having problems. This what I wrote: Select * from CSWReportDatabase.CSWReportDatabase.dbo.package_lists
and this is what I get:Could not find server 'CSWReportDatabase' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
I don't understand, I linked server with the database, why I am getting this error?
September 11, 2008 at 8:09 am
try adding brackets around each item
[CSWReportDatabase].[CSWReportDatabase].[dbo].[package_lists]
-WM
September 11, 2008 at 8:16 am
williamminor (9/11/2008)
try adding brackets around each item[CSWReportDatabase].[CSWReportDatabase].[dbo].[package_lists]
-WM
That's only necessary if the names don't conform to the basic naming conventions. Won't solve the problem here.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 12, 2008 at 3:52 am
Hi this is how I do it. The below example is for linking to an ORACLE server
[font="Courier New"]USE master
GO
-- To use named parameters:
EXEC sp_addlinkedserver
@server = 'name of the linked server you want to see',
@srvproduct = 'Oracle',
@provider = 'MSDAORA',
@datasrc = 'name of datasource'
GO[/font]
Create the login
[font="Courier New"]exec sp_addlinkedsrvlogin 'name of linked server', false, null, 'user name', 'password'[/font]
Query:
[font="Courier New"]SELECT Count(*) FROM OPENQUERY('name of linked server', 'SELECT * FROM 'name of linked server'.'table or view name')[/font]
Also check this article on sqlservercentral:
http://www.sqlservercentral.com/articles/Distributed+Queries/anintroductiontolinkedservers/1366/%5B/url%5D
Cheers & have fun
😀
September 12, 2008 at 5:43 am
Thank you
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply