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 |
|
Ann2
Starting Member
4 Posts |
 Posted - 2006-03-23 : 17:11:16
|
| Hi, I already spent days in browsing and whatever I found did not help. I tried to set linked server from SQL to db4 with Microsoft OLEDB provider for ODBC through enterprise manager and no matter what I specify for DataSource and Provider string I get errors like this: If I use system DSN (which exists and perfectly works with crystal reports) for datasource, it complains about driver and data source. If I use just a path to a network data location it says that data source name is too long. For provider I tried many values like path or DSN name and it does not work as well. With Microsoft Jet 4.0 OLEDB Provider it returns error 7399 Authentication failed. I did changed TEMP and TMP enviromental var pathes to c:/temp as it suggested in microsoft knowledge base, but than I could not stop and start sql server, so I even could not opened enterprise manager. Had to change the pathes back. So I only left the possibility to establish connection through ODBC and I have to do it in Enterprise Manager. Could you please explain each field from linked server properties for ODBC connection. Please help me! Thank you. |
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
|
|
Ann2
Starting Member
4 Posts |
Posted - 2006-03-24 : 13:18:46
|
| Dan,Thank you so much for you reply. I have been through this help as it was the first one to look in SQL Help. It looks like I do everything correct, but for some reason link does not recognize system dsn that I use to connect even though that dsn works perfect with crystal reports.Ann2 |
 |
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2006-03-24 : 13:24:33
|
quote:
Originally posted by Ann2
Dan,Thank you so much for you reply. I have been through this help as it was the first one to look in SQL Help. It looks like I do everything correct, but for some reason link does not recognize system dsn that I use to connect even though that dsn works perfect with crystal reports.Ann2
I'm guessing you're trying to link and access db... You didn't say so I am guessing.Use the syntax like this:EXEC sp_addlinkedserver @server = 'SEATTLEMktg', @provider = 'Microsoft.Jet.OLEDB.4.0', @srvproduct = 'OLE DB Provider for Jet', @datasrc = 'C:\MSOffice\Access\Samples\Northwind.mdb'Then to select from it you'd say: select * from SEATTLEMktg...ordersDaniel, MCP, A+SQL Server DBAwww.dallasteam.com |
|
|
|
Ann2
Starting Member
4 Posts |
Posted - 2006-03-24 : 14:02:01
|
| Dan,I did what you suggested.The datasourse I tried to put in is 'C:\Datafile'This is were actually all dbf files are located.I got error message when I tried to look in the tables in enterprise manager:Athentication failed.When did this: select * from DBF.astu5003.DBFwhere 'DBF' is how I named server it says:Invalid object name 'DBF.astu5003.DBF'.The Athentication failed problem may be solved as it says in microsoft knowledge bays by changing TEMP and TMP var's path to c:/temp in my computer properties. But than I have to stop and start sql server and I can not do that.Ann |
|
|
|
|
|
|
|
|
Linked servers