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.
| Author |
Topic |
|
miranwar
Posting Yak Master
125 Posts |
 Posted - 2009-09-14 : 12:06:44
|
| trying to create a local linked server using OLEDB, this is to reference a database on the same server.Can some one assist me with the syntax. I have created a linked server using the following syntax:EXEC sp_addlinkedserver @server = 'SFDCSYNC', @srvproduct = '', @provider = 'MSDASQL', @datasrc = NULL, @location = NULL, @provstr = 'DRIVER={SQL Server};Server=(local); Initial Catalog=etlrefmap;uid=sa;pwd=Rgb#128152;'however I get errors when trying to select from a table from the LS: i.e select * from SFDCSync...mytable. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-09-14 : 13:15:28
|
| why do you need linked server if db is in same server? you can just query table by select * from dbname.dbo.mytable |
 |
|
|
miranwar
Posting Yak Master
125 Posts |
Posted - 2009-09-15 : 06:56:19
|
| Hi I don't want to hardcode the dbname in the SP. |
|
|
|
YellowBug
Aged Yak Warrior
616 Posts |
Posted - 2009-09-15 : 07:13:25
|
quote:
I don't want to hardcode the dbname in the SP.
Can you explain why you require this.Maybe you can use views instead. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-09-15 : 08:08:52
|
quote:
Originally posted by miranwar
Hi I don't want to hardcode the dbname in the SP.
Guess what? You'll have to do that anyway if you use a 4-part name. Not only that, you'll incur the overhead of a distributed transaction using a loopback linked server vs. a normal 3-part name reference. Not only could this affect performance, it very often causes a whole bunch of problems that prevent the code from executing (you've already found one).And can you please explain how hard-coding a linked server name is any different?BTW, the provider and connection string you're using are for ODBC, not OLEDB. |
|
|
|
|
|
|
How to create a Local Linked server using OLEDB