August 6, 2012 at 2:56 pm
Good Afternoon, I am relatively new to SQL Server 2008 and I am attempting to create a query. What I need is to access data from a SQL Server 2005 table and use it to update a table in my current db. Both SQL db's are located on a server called 'SQLSERVER'. The SQL 2005 instance is named 'ADVANCEPRO', the database is 'Advanced' and I wish to access a view called 'APImport'. My SQL 2008 instance is named 'ULTRASQL', the database is 'USATables' and I want to update fields in the 'dbo_base' table.
I have tried several things already, but being new I just don't think I have the language down enough to translate what MSDN wants to tell me. I have created a linked table by using the sp_addlinkedserver command but I cannot seem to access it from within my query. When I browse to the Server Objects\Linked Servers\APserver and look at the table, I cannot get it to show any fields or data.
What I am attempting to do is use live data from my 'ADVANCEPRO' table to periodically update any new entries into my 'dbo_base' table. This information will include "SerialNo', 'Model', 'StockNo', 'DateRaised' and 'APModel' fields. I would want to verify that the serial number is not already in the 'dbo_base' table.
There are more criteria that will need to be added to specify specifics, but I think I have a handle on that part. It is just accessing the data from a different SQL instance that seems to be throwing me off.
Any help would be greatly appreciated.
August 6, 2012 at 3:09 pm
john.bush (8/6/2012)
Good Afternoon, I am relatively new to SQL Server 2008 and I am attempting to create a query. What I need is to access data from a SQL Server 2005 table and use it to update a table in my current db. Both SQL db's are located on a server called 'SQLSERVER'. The SQL 2005 instance is named 'ADVANCEPRO', the database is 'Advanced' and I wish to access a view called 'APImport'. My SQL 2008 instance is named 'ULTRASQL', the database is 'USATables' and I want to update fields in the 'dbo_base' table.I have tried several things already, but being new I just don't think I have the language down enough to translate what MSDN wants to tell me. I have created a linked table by using the sp_addlinkedserver command but I cannot seem to access it from within my query. When I browse to the Server Objects\Linked Servers\APserver and look at the table, I cannot get it to show any fields or data.
What I am attempting to do is use live data from my 'ADVANCEPRO' table to periodically update any new entries into my 'dbo_base' table. This information will include "SerialNo', 'Model', 'StockNo', 'DateRaised' and 'APModel' fields. I would want to verify that the serial number is not already in the 'dbo_base' table.
There are more criteria that will need to be added to specify specifics, but I think I have a handle on that part. It is just accessing the data from a different SQL instance that seems to be throwing me off.
Any help would be greatly appreciated.
On ULTRASQL, browse the object nodes to Server Objects - Linked Servers. Make sure your linked server exists there. If it does, Rt-Click it and go to properties. Confirm that your Data Source reads as: SQLSERVER\ADVANCEPRO, and that the catalog reads as Advanced. In the security tab on the left, after clicking it, confirm your linked server is set to 'be made using the login's current security context'. This means that your login (when you're running the script) will be the one that's being used to connect through the link. Finally, in Server Options (again on the left) make sure RPC and RPC Out are set to True, as is Data Access.
Now that that's done, on the UltraSQL server, open a new query window and see if the following will run:
SELECT TOP 10 * FROM <linkedservername>.Advanced..APImport
Tell us what results from that. Also, the name of the linked server you've used wouldn't hurt, either, to make sure you don't need brackets [] wrapping it or something silly like that too.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 6, 2012 at 3:31 pm
Hi Craig,
I checked everything you mentioned and have got positive results. I seem to have left the RPC and RPC Out at defaults (False). What does RPC stand for? My linked server name is 'APserver'. After changing the setting I can now get info from the tables. Always seems to be the small things that trip you up.
Thanks,
John
August 6, 2012 at 3:35 pm
john.bush (8/6/2012)
Hi Craig,I checked everything you mentioned and have got positive results. I seem to have left the RPC and RPC Out at defaults (False). What does RPC stand for? My linked server name is 'APserver'. After changing the setting I can now get info from the tables. Always seems to be the small things that trip you up.
Thanks,
John
Remote Procedure Call. Basically it allows SQL to run through the link or to call a remote proc instead of having to drag the schema and everything under the sun to the other server to use any of it. You'll want that on for just about any linked server you build. Not entirely sure why it defaults to off, honestly.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply