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.
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?
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...
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
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
' 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
I have a Login that I setup in SQL Server 2005. The only database roles given to the login are db_datareader and public. On SQL 2000 these permissions are sufficient to allow me to setup a read only ODBC connection using this login and creating an import table in Access. When using the login in SQL 2005 and creating an import table, I am able to edit the underlying data, something I am not able to do with a SQL 2000 ODBC connection. Is there something in SQL 2005 that I am unaware of that would allow this login to modify (update) the table?
I am using SQL 2000 and would like to dynamically assign ODBC data source to transform data task. Do you have a stored procedure to perform read/write from/to ODBC data source? I would like to input data source and table name.
I'm new to my company, although not new to SQL 2005 and I found something interesting. I don't have an ERD yet, and so I was asking a co-worker what table some data was in, they told me a table that is NOT in SQL Server 2005's list of tables, views or synonyms.
I thought that was strange, and so I searched over and over again and still I couldn't find it. Then I did a select statement the table that Access thinks exists and SQL Server does not show and to my shock, the select statement pulled in data!
So how did this happen? How can I find the object in SSMS folder listing of tables/views or whatever and what am I overlooking?
Hi,I have two, similar SQL Server 7.0 databases (in the same SQL ServerGroup) that I use as the backend for two Access 2000 front endapplications - one is the live version, the other is the developmentversion.The live version works fine. But the other version is read-only, i.e.no changes can be made to the data in the tables, over the ODBCconnection.Both systems have similar ODBC connection properties, the permissionsettings on the SQL Server databases are the same - users, tableaccess, database access, etc. The connections use Windowsauthenication and the users are set up in SQL Server security loginswith public and db_owner selected.Has anyone seen or heard of this problem and does anyone have anyideas on how to fix it?I appreciate any comments or suggestions you may have.Thanks,Chris
I am trying to read an Informix 9.3 blob field into a TSQL (SQL Server Stored Procedure) and the data type conversion is not supported thru the MS ODBC Driver installed with Win2000/SQL2000. Does anyone know of another method to retrieve this data, or of a vendor that provides an ODBC driver that supports the Informix blob data type. Thanks!
I work on an MS Access project ( *.apd - file) that is linked with a SQL-express server 2005 (local installed). On this server are 2 databases x and y. I linked some tables of database x to y and here's the problem: the linked tables are read-only. My authentication for the server is Windows authentication and I'm administrator. I say this because I think the problem is a security problem.
when I try to change data in the linked table in Microsoft SQL Server Management Studio Express, I get now the follow error: --------------------------- No row was updated.
The data in row 12 was not committed Error source: .Net SqlClient Data provider. Error Message: Transaction context in use by another session.
Correct the errors an retry or press ESC to cancel the change(s). --------------------------------- All other programs are close, except MSE
thanks a lot!!!!!
Can somebody help me to make the linked tables are not read-only? What is the problem, what did I wrong?
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.
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.]
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.
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..?
I'd like to set up an ODBC DSN to a table in a linked SQL Server, viamy local SQL Server.I'm having a few problems:1. When I use Enterprise Manager to link the remote SQL Server, itdoesn't allow me to select the database in the remote server. It onlyshows one database.2. When I use the Data Sources program to set up the ODBC DSN, it onlyshows databases in the local SQL Server, not in the linked one. If Itry typing it in: [NAMEOFLINKEDSERVER].databasename - it tells me thatit is an invalid table.
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
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"
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.
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
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
I am using the following select statement to get the row count from SQL linked server table.
SELECT Count(*) FROM OPENQUERY (CMSPROD, 'Select * From MHDLIB.MHSERV0P')
MHDLIB is the library name in IBM DB2 database. The above query gives me only the row count of table MHSERV0P. However, I need to get the names, rowcounts, and sizes of all tables that exist in MHDLIB librray. Is it possible at all?
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?
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
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?
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!
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
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)
Hi , On my Desktop i registered Production Server in Enterprise Manager on that Server if i go to SecurityLinked Servers There is another Server is already mapped, when i am trying to see the Tables under that one of the Linked Server i am getting the Error message saying that "Error 17 SQL Server does not exist or access denied"
if i went to Production Server location and if i try to see the tables i am able to see properly, no problems why i am not able to see from my Desk top i am using the sa user while mapping the Production Server on my DESKTOP using (ENTERPRISE MANAGER)
And i check the Client Network Utility in the Alias using Named Pipe only, i changed to TCP/IP still same problem What might the Problem how can i see the Tables in Linked Server from my DESKTOP
Can the header record be read into one table, the detail into another and the trailer into a third within one data transformation or would this have to be done with an active script task.
I then have to take this data and write it back out to one file with a header and detail lines.
Would vb be better for that part or can it be done easily with active script using the file system object. I could write the one header record and append the detail lines after.
have created a Database Application in Java and display all the records in tabular format of one Table. This table have Millions of Rows, If I run Select * from Table, then my Machine not responding, so Now I wants to add paging of 1000 rows at one time.
Is there are any option/query to read block of rows at one time and then query again for next page ?
i.e In MYSQL have LIMIT clause with Select Statement
The problem I'm having now is that any users that are not in the sysadmin server role can't read any table. Say if I have 2 users:
Domain1User1 ( in db_datareader and db_datawriter Database Role, no sever role assigned) Domain1User2 ( in db_datareader and db_datawriter Database Role, sysadmin Server Role)
Here is what's expected to happen if everything goes well.
A list of customers ( first name, last name, age, etc) in a gridview should show up after login.
After both users logged in, Domain1User1 received an exception message of "Object reference not set to an instance of an object".
Domain1User2, however, was able to see the list.
I checked SQL Server Logs, and found 2 items: Login succeeded for user 'Domain1User1' ... Connection TRUSTED Login succeeded for user 'Domain1User2' ... Connection TRUSTED. Based on the log file, it appears that both users had good connections to the database. Then why is it that User1, which is not in sysadmin Server Role, was not allowed to make a query?
Now if I assign sysadmin Server Role to Domain1User1 as well, the User1 will be able to see the list without seeing the exception.
