ODBC Connection To A Linked Database
Jan 22, 2006
I'd like to set up an ODBC DSN to a table in a linked SQL Server, via
my local SQL Server.
I'm having a few problems:
1. When I use Enterprise Manager to link the remote SQL Server, it
doesn't allow me to select the database in the remote server. It only
shows one database.
2. When I use the Data Sources program to set up the ODBC DSN, it only
shows databases in the local SQL Server, not in the linked one. If I
try typing it in: [NAMEOFLINKEDSERVER].databasename - it tells me that
it is an invalid table.
View 5 Replies
ADVERTISEMENT
Feb 13, 2007
I apologize if this is not the correct forum for this posting. Looking at the descriptions, it appeared to be the best choice.
I am running Windows XP Pro SP2. I have installed the SQL Native Client for
XP. However, when I try to add a new data source through ODBC Connection
Manager, SQL Native Client is not listed as an option. I have followed this procedure on three other systems with no problems. What would be causing the
SQL Native Client to not show up in the list of available ODBC data sources?
View 4 Replies
View Related
Jan 17, 2008
Hello,
pls. let me know where I could post if this is the wrong place.
I have a Firebird 1.5 application. I created a linked server from my SQL
Server 2000 to the firebird database. In SQL Server Query Analyzer I get errors from various ODBC drivers with "normal" queryies like
SELECT LVNR FROM LINKEDSRV...LVVERW
Pls. note, this all works perfectly in MS Access databases with ODBC-Links to Firebird!
From a programmer of a commercial ODBC driver I heard that this problem may be caused internally by SQL Server, there may be no solution possible in the ODBC driver. One workaround would be to use the OPENQUERY-Syntax like
SELECT * FROM OPENQUERY(LINKEDSRV, 'select LVNR from LVVERW ')
Are there any other solutions? Are there any known issues with firebird odbc-drivers and sql server? Are
there any known good drivers for the use with sql-server? What is the purpose of OPENQUERY - workaround ODBC problems? Are there any settings in SQL Server 2000 (2005 Express) that could help? Are there any settings in ODBC DSN that would help?
regards
arno
PS: Here are my favorite error messages
Error -2147217900 [OLE/DB provider returned message: Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, char 89
"Col1014"] (Source: Microsoft OLE DB Provider for SQL Server) (SQL State:
01000) (NativeError: 7312)Error -2147217900 OLE DB-Fehlertrace [OLE/DB
Provider 'MSDASQL' ICommandPrepare:repare returned 0x80004005: ].
(Source: Microsoft OLE DB Provider for SQL Server) (SQL State: 01000)
(NativeError: 7300)Error -2147217900 Der OLE DB-Provider 'MSDASQL' meldete
einen Fehler. (Source: Microsoft OLE DB Provider for SQL Server) (SQL
State: 42000) (NativeError: 7399)
This "tricky" query does not work:
SELECT LVNR FROM LINKEDSRV...LVVERW;
Error -2147217900 OLE DB-Fehlertrace [Non-interface error: Column
'ERHALTENABSCHLAG' (compile-time ordinal 35) of object 'LVVERW' was reported
to have a DBTYPE of 5 at compile time and 131 at run time]. (Source:
Microsoft OLE DB Provider for SQL Server) (SQL State: 01000) (NativeError:
7300)Error -2147217900 Der OLE DB-Provider 'MSDASQL' hat inkonsistente
Metadaten für eine Spalte übergeben. Die Metadateninformationen wurden zur
Ausführungszeit geändert. (Source: Microsoft OLE DB Provider for SQL
Server) (SQL State: 42000) (NativeError: 7356)
View 2 Replies
View Related
Jun 5, 2007
Hai,
I am using ADOX to create linked tables in a jet database from an ODBC datasource.
The tables in the ODBC data source does not have a primary key.
so I am only able to create read only linked tables.But I want to update the records also.
I tried adding a primary key column to the linked table while creating the link.
but I am getting an error while adding the table to the catalog.
The error message is "Invalid Argument".
I use the following code for creating the linked table
Sub CreateLinkedTable(ByVal strTargetDB As String, ByVal strProviderString As String, ByVal strSourceTbl As String, ByVal strLinkTblName As String)
Dim catDB As ADOX.Catalog
Dim tblLink As ADOX._Table
Dim ADOConnection As New ADODB.Connection
ADOConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strTargetDB & ";User Id=admin;Password=;")
catDB = New ADOX.Catalog
catDB.ActiveConnection = ADOConnection
tblLink = New ADOX.Table
With tblLink
' Name the new Table and set its ParentCatalog property
' to the open Catalog to allow access to the Properties
' collection.
.Name = strLinkTblName
.ParentCatalog = catDB
' Set the properties to create the link.
Dim adoxPro As ADOX.Property
adoxPro = .Properties("Jet OLEDB:Create Link")
adoxPro.Value = True
adoxPro = .Properties("Jet OLEDB:Link Provider String")
adoxPro.Value = strProviderString
adoxPro = .Properties("Jet OLEDB:Remote Table Name")
adoxPro.Value = strSourceTbl
End With
'Adding primary key,
'***** the source column name is "Code" ******
tblLink.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "Code")
'Append the table to the Tables collection.
'******The exception occurs on the following line***********
catDB.Tables.Append(tblLink)
'Append the primary index to table.
catDB = Nothing
End Sub
If I avoid the line for adding the primary key,everything works fine,but the table ctreated is readonly.
Thanks in advance
Sudeep T S
View 4 Replies
View Related
Feb 29, 2008
I need detailed instructions on how to connect to a database from a Microsoft Access 2007 database to a Microsft Office Accounting 2007 database. The accounting database is an SQL 2005 datbase. It has an instance name of "MSSMLBIZ".
When I try I get an SQL error 53. Do not have permissions or database does not exist.
Thanks in advance for any help.,
View 1 Replies
View Related
Sep 27, 2006
Is there a way to choose which database you want to connect to in the ODBC connection?
And does this differ from one ODBC connection to another, say for instance Teradata and SQL Server?
I was looking up the syntax and it seems like you can say Database=LACPP and it will choose the database....
I have also seen username/password@LACPP.SQLSERVER
Are there any documents out there that talk about how to create different kinds of ODBC strings and if you can always limit which database you are looking at?
-Bob
View 1 Replies
View Related
Dec 6, 2007
Hi All, Please help me how to proceed from this situation, my requirement is to present (load) data in excel file and need to access (update/delete) it, the data which is in remote server; I want to connect database using odbc connection through ASP.NET. Please suggest me how to continue.
Thanks to all in Advance.
kolags@hotmail.com
View 1 Replies
View Related
May 3, 2007
My application works with the Connection pooling ODBC. Everything works well, when it data base this down the SQLConnect function finishes steeply my application.
View 3 Replies
View Related
Jun 1, 2015
I am using SSIS 2014 with the below .net framework version and installed in Windows server 2012 R2 . I have installed my client's odbc drivers (both 32 bit and 64 bit) in my production server and created ODBC system DSNs for 32 bit and 64 bit.
When i open SSIS 2014 and tried to create the odbc connection but i can able to see only the 32 bit system DSN connection ,i can't able to see my 64 bit odbc system dsn connection.
Microsoft Visual Studio 2012 Shell (Integrated)
Version 11.0.50727.1 RTMREL
Microsoft .NET Framework
Version 4.5.51650
SQL Server Integration Services  Â
Microsoft SQL Server Integration Services Designer
Version 12.0.1524.0
And i installed my client odbc drivers(32,64 bit) and created ODBC system DSNs in my local system and when i open ssis 2014 and i can able to see both the ODBC system DSNS(32,64) connections from SSIS ODBC connection.
I am using below version of .net framework in my local system which was installed in windows 7 and i have SSIS 2012 also installed in my system and i can able to see both ODBC connections using 2012 as well in my local system.
Microsoft Visual Studio 2012 Shell (Integrated)
Version 11.0.50727.1 RTMREL
Microsoft .NET Framework
Version 4.5.50938
SQL Server Integration Services  Â
Microsoft SQL Server Integration Services Designer
Version 12.0.1524.0
why i can not see the ODBC 64 bit system DSN connection from SSIS in my production server ?
View 9 Replies
View Related
Aug 30, 2006
When I create a new odbc connection to a SQL server 2005 Db I get a failure telling me dat de SQL server does not allow remote connections.
How can I allow the server to allow this.
Any help appreciated
regards
View 1 Replies
View Related
Mar 23, 2001
In order to use Access 97 as your frontend and SQL 7 as your backend on a
network (where the frontend is located on the same computer as the backend and
people just map to Access frontend) where security for the data is recognized
by your domain login account and what domain group you belong to..do you
have to have the same System DSN on everyone's computer for linking? I am
still having problems with people linking to the SQL server through the network and only my account is working correctly from my desktop. Everyone
else who tries to link gets an ODBC failure error message when trying to
open one of the linked tables. I have heard from one person that you must
have the same system DSN on every client's computer but to me that makes no
sense as I do not have the DSN listed on my individual computer...I am just
going through the network as myself..not as an admin..and getting directly
to the data. Everyone else can get to the shared folder on the server but
cannot get to the linked tables. Any ideas?
View 2 Replies
View Related
Jul 25, 2000
I have a linked server set up to a FoxPro database. I have used the 'Microsoft OLE DB Provider for ODBC' as the Provider name, the datasource name is 'TestFoxPro' and the Catalog is AssetTracker.
I have set it up and I am able to see the table objects in the Enterprise manager, but I am unable to select from the tables in the Query analyzer. I I have tried
select * from Test2.assettracker.dbo.assets
but i get the following error
Server: Msg 7312, Level 16, State 1, Line 1
Invalid use of schema and/or catalog for OLE DB provider 'MSDASQL'. A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog and/or schema.
So i have tried three part name, but then it does not find the object.
Any suggestions??
paull
View 1 Replies
View Related
Jul 14, 2002
Hi,,
I am trying to create linked server in sql2000 using odbc which point
to foxpro free files.
I tryed a lot but the following errro message is displayed:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Visual FoxPro Driver]File 'data1020.dbf' does not exist.]
thanks
View 1 Replies
View Related
Dec 11, 2006
In my ssis package,
I have a DSN connection like this: "DSN=myDSNname". Which decide from i have to pull the data.
By using OLE DB Source Editor, I want to assign that ODBC Connection to it.
By data source Reader i can achive this but where i have to pass the hard-code SQL Query that i don't want.
i'm using the variable for dynamic SQL command.
Thanks.
Manoj
View 5 Replies
View Related
Oct 27, 1999
I created a Linked ODBC Server named HP3000, I can view the tables in enterprise manager, but I cannot SELECT the server in my SQL statement.
Any pointers would be greatly appreciated.
View 1 Replies
View Related
Jun 14, 2007
Hi,
I have an Access database Front End which use SQL server as a Back End. The two are connected using ODBC. Occasionally, some of the linked tables in Access go read only. I can't add or edit records. The only way I know to get round the problem is to delete the link and re-create it. Refreshing the link does not work. Can anybody suggest why this would happen, and the best way to fix it when it does occur?
Edited 12:10 06/14/07
Some extra info. At the same time it goes read only, the link loses it's primary key.
Thanks
Colin
View 1 Replies
View Related
Mar 4, 2008
Gurus,
I cant find any odbc provider from my linked server.(SQL 2005)
I am using MDAC 2.8 SP1 ON WINDOWS XP SP2 on my machine.
What can i do to get odbc provider..?
Please help me to do this
Thanks
Krishna
View 2 Replies
View Related
Jul 20, 2005
I'm currently trying to access data from a Cache DB using MSSQL. I havelinked the Cache server through an ODBC connection. I can see in the LinkedServerexpansion all the tables in Cache for the File(?) I want to access.Here is my problem: Normally to access a linked sever I would do thefollowing:select *from ServerName.DatabaseName.dbo.TableNameI have triedselect *from ServerName.DSN.dbo.TableNameselect *from ServerName.DSN.SQLUser.TableName ("SQLUser" is the owner in Cache)None have worked (error no such object...)What is the syntax to select data on a linked sever via an ODBC connection?In the Linked server set up I have also check the RPC and RPC Out options...My ultimate goal is join tables in Cache and MSSQL into MSSQL.Any help would be greatly appreciated!Thanks,-p
View 2 Replies
View Related
Nov 5, 2007
I have SQL Server 2005
I have a linked server using an ODBC System DSN setup.
A DB with a view to access on of the table from the Acomba DB.
This view work just fine when I logged in using the Windows Authentification.
If I create a user (bla) with "sysadmin" as server roles
the user bla is the owner of the DB (he also created the DB)
He can't access that view. But can access any tables from the same DB
The error I keep having is the following :
Executed SQL statement : SELECT * FROM OPENQUERY(ACOMBABD, 'SELECT * FROM Customer') AS derivedtbl_1
Error Source: .NEt sqlClient DataProvider
Error Message: Cannot initialize the data srouce object of OLE DB prodiver "MSDASQL" for linked server "ACOMBA"
Major over and out
View 3 Replies
View Related
Feb 15, 2004
I have a SQL server that I am trying to link to a number of Oracle environments. After much tuning, we managed to achieve this although the four-part naming was not possible and we had to use Openquery and run pass throughs.
Nothing in our configuration has changed and SQL Server is no longer able connect to the linked databases. The Oracle client on the PC is fine and is able tnsping any of the remote databases. I am also able to create ODBC connections to the remote databases on the SQL box that are fine.
Using a datalink in DTS, I can connect to the remote databases. This suggests to me that there is something wrong within the actual database links. I have set them up using the working ODBC DSN's on the SQL box.
If I try and run a query against them in Query Analyser, I get the following error message :
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: ORA-12154: TNS:could not resolve service name
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005: ].
If I click on the tables icon in EM to view the remote catalogues I get the following error :
Error 7399: OLE DB provider 'MSDORA' reported an error.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005: ].
Any help that could be give on this would be greatly appreciated.
View 3 Replies
View Related
Mar 11, 2006
HiWe're trying to use call a stored procedure to update information in aremote Ingres database. We've linked the server, and can readinformation using SELECT * FROM OPENQUERY (..........), but we can'tfind a suitable syntax for executing a procedure.Using SELECT * FROM OPENQUERY and passing the EXEC statement in astring gives a message about not returning any columns - not surprisingas there aren't any, and trying to execute the procedure more directlyusing:-EXECUTE abrs..vipdba.ats_reader_pi0 ........Gives the errorCould not execute procedure 'ats_reader_pi0' on remote server 'abrs'.[OLE/DB provider returned message: Parameter type cannot be determinedfor at least one variant parameter.]Any bright ideas?Chloe
View 1 Replies
View Related
Aug 3, 2006
Hi,I have a prod database (main bread & bread DB) and have a small accessdatabase that sales team is using... I created a login inside SQL 2000and gave db_read permission and SELECT permissions... and public.Role of public is EXEC store procs and view some systables...I linked those tables that I gave permissions through ODBC link intoaccess db with the user that I created inside SQL as readonly user...but to my surprise when I ran a insert query from access on SQLtables... I was able to update and insert data... if my ODBC link isonly for readonly.. why was I able to unpdate & insert data into SQLtable?I don't want to give write/update/insert permissions for SQL tables tosales team... or anyone outside SQL 2000 server DB.Is there any easy way to create viewonly users inside SQL?I created users like this: security -logins -new login...select none serverrole.. db access (bread & butter DB)Thank you,hj******Pardon my English...
View 1 Replies
View Related
Sep 27, 2006
I have access97 front end , with linked tables to sql server 2005. when i change a table ID column (type int) to primary key so it is updatable, then try open the table i get odbc error with no msg, if i alter table again to remove primary key option table opens fine but is not updateable. any ideas would be a great help
View 5 Replies
View Related
Jan 6, 2004
Hello,
I have a SQL Server instance on my local computer and an Oracle
Database on a remote server. I want to run queries from tables
within both databases and am using linked servers to accomplish
this.
I configure my linked server in SQL Server using the Microsoft
OLE DB Provider for Oracle and can run queries using sql server
tables and oracle tables. However, even the simplest queries
take more than 10 minutes to run.
I have the Oracle 9 Client Installed and MDAC 2.7. I configured
my registry settings to match oracle 9's settings.
However nothing i do improves the performance of the queries
through the Microsoft OLEDB Provider for Oracle.
When I use MS Access, or use an ASP page with the following
string:
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "dsn=Oberon;uid=mfs;pwd=mfs;"
I implement the ODBC driver that I configured in my system DSN
and both run the same queries very fast. The data comes back
without a problem.
So i believe I have narrowed down my problem to the OLEDB
Provider. However, SQL Server does not give me a choice to use
the ORACLE native ODBC Provider.
So then I tried using Pass-Through Queries and this worked alot
faster in SQL Server...I am completely confused as to whats
going on.
Linked Server Query that takes over 10 minutes:
SELECT * FROM OBERON..LOGS.DATA_PHOTO ldp where Machine=301 AND C3='I051097';
Pass-Through Query that works faster:
select * from Openquery(OBERON, 'SELECT * FROM LOGS.DATA_PHOTO ldp where MACHINE=301 AND C3=''I051097''')
From researching pass through queries, my understanding is that
it actually uses ODBC to give the whole query to the remote
database where the query is then run and the results are passed
back as a table, thats why you say select * from (query)...however if my understanding is correct, then you cant combine tables in different databases very easily. And will it work with .asp and .aspx pages?
Anybody have any insight as to whats going on?
View 1 Replies
View Related
Dec 12, 2006
Hi.I have the problem that some records in a ms sqlserver table is unableto update from Access.I get the error message odbc-time out error in linked table......I tried to copy this table to another database, where none but me wasaktive.And then it worked quit ok when I try to save the record.I am thinking about if there is some trigger och restraint that Idon't know about, but I don't know how to se all of this in thedatabase.I have changed the odbc-timeout settings in Access but that doesn'tseed to make any differenceSo what's the problem?Does anyone have an idea?/regards BigOlle
View 1 Replies
View Related
Apr 22, 2006
I have an Oracle 9i server. To access the data in Oracle, I setup an ODBC connection to it and am able to return data from it using WinSQL (a general ODBC database client). The SQL statement is simply "SELECT * FROM COLOR" and all 133 records returned properly.
I need to copy the data from the Oracle server to SQL Express, therefore I set up linked server (by Microsoft ODBC provider) using the same ODBC connection as described above.
The problem is: only 32 records returned.
May I ask if there is any problem to this linked server setup?
Thanks.
View 4 Replies
View Related
Oct 9, 2007
What can be the reason(s) why I can't get data from a linked server using an ODBC datasource that works fast & fine from MS Access?
I have an ODBC connection (System DSN) configured for an Informix ODBC driver.
The Test button (belonging to this driver-setup) reports a successfull connection test.
Getting data from this database by linking tables in MS Access works fast and easy.
But I have tried for many days now to setup a linked server from SQL Server (2005)
Creation goes fine, but as soon as I issue a query, (e.g. 'select * from infrem723...remotetable' or using 'openquery')
I get the following error:
---
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "infrem723" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "infrem723".
---
Why is Access able to read what "MSDASQL" cannot?
I am desperate - can anybody help?Thanks a lot!
View 8 Replies
View Related
Jul 20, 2005
Hi,I used sp_addlinkedserver to link to a remote server through ODBC.When I execute select count(*) from LinkSrv.SI.DBO.SIHeader in SQL QueryAnalyzer. It returns 13705 records. But when I execute select * fromLinkSrv.SI.DBO.SIHeader. It only return 885 records. If I specify somecolumns, select ODCOMP, ODPONO, ODVDCD from LinkSrv.SI.DBO.SIHeader.It returns more records, 1213 records.I guess there is something limit the return storage, but I can notfind it.Any suggestion will be appreciated. Thank you
View 3 Replies
View Related
Jul 20, 2005
I changed from Access97 to AccessXP and I have immense performanceproblems.Details:- Access XP MDB with Jet 4.0 ( no ADP-Project )- Linked Tables to SQL-Server 2000 over ODBCI used the SQL Profile to watch the T-SQL-Command which Access ( whocreates the commands?) creates and noticed:1) some Jet-SQL commands with JOINS and Where-Statements aretranslated very well, using sp_prepexe and sp_execute, including thesimilar SQL-Statement as in JET.2) other Jet-SQL commands with JOINS and Where-Statements aretranslated very bad, because the Join wasn´t sent as a join, Accesscollects the data of the individual tables seperately.Access sends much to much data over the network, it is a disaster!3) in Access97 the same command was interpreted wellCould it be possible the Access uses a wrong protocol-stack, perhapsJet to OLEDB, OLEDB to ODBC, ODBC to SQL-Server orJet to ODBC, ODBC to OLEDB and OLEDB to SQL-Server instead ofJet to ODBC and ODBC direct to SQL-ServerDoes anyone knows anything about:- Command-Interpreter of JetODBC, Parameters, how to influence thecommand-interpreter- Protocol-Stack of a Jet4.0 / ODBC / SQL-Server applicationThanks , Andreas
View 6 Replies
View Related
Oct 17, 2000
Has anyone had an issue with the ODBC data source that when you set it up for SQL, it does not retain the username and password you entered and keeps defaulting back to the NT windows verification instead of using the SQL verification that you choose?
I add a datasource, select validation based off of SQL profile/password assigned, enter the user and password I assigned on SQL, go through the setup and test successfully. Then I try to connect using that datasource with my VB application and it says connection failure. When I go back to the datasource, it has changed back to NT verification and removes the user/password I entered for SQL verification. Maybe there is an service pack from MS on this?
any help is appreciated. At the very least, I can just hard code into my VB app, but that is my last resort. Thanks!
View 1 Replies
View Related
Jun 10, 1999
Hi everyone
I installed the MS SQL server 7.0 on a Windows NT 4.0 SP 4 server. The network runs with TCP/IP.
I want to connect the SQL server by a ODBC-connection. Only a connection on the server (local) was successful.
The odbcping-utility reports following:
COULD NOT CONNECT TO SQL SERVER
SQLState: 08001 Native Error: 6
Info. Message: [Microsoft][ODBC SQL Server Driver][DBNMPNTW]Selected SQL server not found.
SQLState: 01000 Native Error: 53
Info. Message: [Microsoft][ODBC SQL Server Driver][DBNMPNTW]ConnectionOpen (CreateFile()).
Do I forget to install a SQL server component? Or a network service?
Thank you for your help.
Greetings
Steven
View 3 Replies
View Related
Nov 8, 1999
I am currently running a application and a database server they are connected via ethernet. Only the application is connected to our network. Is there a way to connect MS Access to the database server without connecting the database server to the network? I've tried several different approaches and it still dosent work. Any help would be greatly appreciated.
Dale
View 1 Replies
View Related
Sep 15, 2004
Would someone provide a code snip example of a T-SQL script using an ODCB connection against a non-SQL2K database. I can hit the outside database using DTS (so I know it is possible) but I'm having trouble with the parameters using a script.
Thanks,
Fred.
View 2 Replies
View Related
