Access - SQL Server Linked Table : Insert Failed
Oct 12, 2000
Good afternoon one and all,
I have the folowing problem that I could use some help with :
I have an SQL server database acting as a back end to an access dbase. The SQL srv table contains over 32 million records and I am trying to use an append query (in access) to import a further 2 million records to the SQLSRV table. The append query fails with the message 'Insert on table bcdsales failed' followed by an ODBC timeout error message. I can append one record fine but a mass import fails.
Unfortunately i can't use SQL srv to do the import (internal policy says we must stick with access front end for now).
Any and all ideas welcomed.
TIA for your time and attention
Gurmi
View 2 Replies
ADVERTISEMENT
Jun 22, 2007
I have used an access linked table to import data from a sharePoint list hosted on SharePoint 2007 server.
I hope someone else has done this. (1)I Does anyone know of any other way to pull data from a SharePoint 2007 list.
(2)Has anyone pushed data to a SharePoint List from a SQL Server table?
I tried doing that via the linked table but the destination task of the data flow task does not allow me to push data into the table.
(3) Has anyone designed an SSIS package to write data to an AccESS table from a SQL Server table?
Any help would be appreciated
View 4 Replies
View Related
Mar 23, 2006
I get this error when trying to alter a stored procedure that has an insert statement referencing a new linked server I created:
INSERT INTO [servername].databasename.dbo.DirectReport
...
Msg 18456, Level 14, State 1, Procedure Get_Direct_Pay_Move_Data, Line 17
Login failed for user 'NT AUTHORITYANONYMOUS LOGON'.
I added Administrators and my logon to the permissions of my linked server but still get this error when it tries to save my stored proc...the one which has that insert.
View 4 Replies
View Related
Jun 18, 2004
Strange one here - I am posting this in both SQL Server and Access forums
Access is telling me it can't append any of the records due to a key violation.
The query:
INSERT INTO dbo_Colors ( NameColorID, Application, Red, Green, Blue )
SELECT Colors_Access.NameColorID, Colors_Access.Application, Colors_Access.Red, Colors_Access.Green, Colors_Access.Blue
FROM Colors_Access;
Colors_Access is linked from another MDB and dbo_Colors is linked from SQL Server 2000.
There are no indexes or foreign contraints on the SQL table. I have no relationships on the dbo_ table in my MDB. The query works if I append to another Access table. The datatypes all match between the two tables though the dbo_ tables has two additional fields not refrenced in the query.
I can manually append the records using cut and paste with no problems.
I have tried re-linking the tables.
Any ideas?
Thanks,
Brad
View 4 Replies
View Related
Oct 23, 2006
Insert statement to remote server is running very slowly. I have run Profiler and find there is a 'sp_cursor' call for each row. The source system is SQL2005 and destination is SQL2000(sp4). The linked server is using 'SQL server' type connection. Source query is against a single table with a where clause. source and destination table are identical with Primary keys. Purpose is just to move the rows. Connection is a slow network connection - should be ok. I have already overcome same problem for related update and delete queries by use of 'EXECUTE (query) AT LinkedServer' that works great - but insert can not take advantage of this...
INSERT [LinkedServSQL2000sp4].dbname.schema.tablename
({column list})
Select
{column list}
from tablename
WHERE col1 = '7/20/2006'
AND col2 in (2,5,7,12,32,54,45,33)
Any thoughts?
View 1 Replies
View Related
Oct 26, 2006
Hi,
I have an Access 2003 front end that contains a number of linked tables on SQL Server 2005 SE. I recreated the application on a second network for testing and was able to use the Linked Table Manager to refresh the database connections. The problem is when I try and add another linked table. I select Link Tables from the menu and then when I select 'ODBC Databases()' from the 'Files of Type' list box, the Link window closes right away.
Any suggestions?
View 5 Replies
View Related
Aug 13, 2012
I have a historical table on a dedicated SQL Server (let's call it the reporting db) that is populated every morning with production data that does not already exist. The data in the prod table is purged after 7 days and nothing is ever deleted from the historical table. I have set up the linked server between the two 2008 SQL Servers, but when I try to run this simple query from the reporting DB, it takes more than 5 minutes and still "executing". I eventually have to cancel it:
-- INSERT INTO Temp_Import_historical
SELECT TOP 1 *
FROM [192.168.1.100].ProdDB.dbo.Temp_Import_historical a
WHERE NOT EXISTS (select [Temp_Import_ID] from Temp_Import_historical where a.[Temp_Import_ID] = Temp_Import_historical.[Temp_Import_ID])
I have omitted the INSERT statement on purpose, since I can't even get to output 1 row. Why this is such a resource intensive query?
View 9 Replies
View Related
Jul 12, 2007
I have a 2000 machine which calls a stored procedure on another 2000 machine via a linked server. The results come back and insert into a temporary table.
When I use the same code executing the from the 2000 machine over to 2005 machine via a linked server I cannot insert into the table. But I am able to see the data if I remove the insert statement.
I have tried to place the data into a permanent table without success. I have also checked to be sure the linked server properties are the same.
Any help on this would be appreciated. Below is the code. It is very simple and returns only one value but the bigger procedure that is ran returns several records and mutliple columns. This seems to easy but doesn't work.
DECLARE @retval AS INT
DECLARE @value AS INT
SET @value = 4
CREATE TABLE #TempTable (Value DECIMAL(19, 10) NULL)
INSERT INTO #TempTable
EXEC @RetVal = Server.Database.dbo.testproc @value
SELECT * FROM #TempTable
DROP TABLE #TempTable
View 6 Replies
View Related
Oct 25, 2013
I linked an SQL server table into Access, but when I open the table in Access, no data shows. Do I need to set some permissions on the sql table?
View 1 Replies
View Related
Aug 28, 2014
I was trying to figure out what the OPENQUERY Syntax is to Insert into SQL Server Table from Oracle Linked Server.
View 7 Replies
View Related
Nov 19, 2013
I wonder if it possible to move data from tables on a linked server to a "normal database"?
Name linked server: Covas
Name table on linked server: tblCountries
Name field: cntCountryName
Name "normal" database: CovasCopy
Name "normal" table: Countries (or dbo.Countries)
Name "normal" field: Country
This is just a test setup. I figure that if I get this working the rest will be easier.
My current query:
select * from openquery(COVAS,'
INSERT INTO CovasCopy.dbo.Countries(Country)
SELECT cntCountryName FROM db_covas.tblCountries;')
View 8 Replies
View Related
Jan 12, 2000
Hi Everyone,
I have set up a link from ACCESS to a SQL 7.0 database using ODBC (File DSN saved on a shared DRIVE). The link works well only from the workstation where the link was created. But How can I create a link so a group of users can view the linked table in ACCESS without type a password? Any suggestion is appreciated.
Lunjun
View 2 Replies
View Related
Jan 11, 2008
hi, why this give me this error...
Msg 8152, Level 16, State 13, Line 1
String or binary data would be truncated.
The statement has been terminated.
INSERT INTO magpatoc.dbo.RSOTransfer(RSONO, Customer, ItemCode, ItemDescription, Source, MOQ, QuantityRequired, Remarks, ZeroStock, NewProduct, ProjectForecast, WithMotherPO, Other, RequestedBy, RequestedDatetime, NotedBy, RecievedBy, RecievedDatetime, PreparedBy, PreparedDatetime, ApprovedBy, ApprovedDate, ReservationNoDate, PurchaseOrderNo)
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:CopyOfRSODB.mdb';'admin';'', FinalCustItemRSO)
help please..
ed9teenMagnaza
View 1 Replies
View Related
Mar 25, 2002
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
Thanks
View 5 Replies
View Related
Jul 20, 2005
Anyone know if there is method that can insert all record from a tablein an MS Access 2000 database to a table in MS SQL Server 2000database by a SQL statement? (Therefore, I can execute the statementin my program)--Posted via http://dbforums.com
View 3 Replies
View Related
Sep 9, 2015
Many a times see the below error in SQL Error log.
Login failed for user 'NT AUTHORITYANONYMOUS LOGON'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: ]
Is this something to do here?
Note: If I run the below statement I know that the SQL Error log entry will go off, but wanted to know the real significance of this error?
CREATE LOGIN [NT AUTHORITYANONYMOUS LOGON] FROM WINDOWS
View 1 Replies
View Related
Nov 7, 2005
I've created a linked table to Access. I want to be able to delete records and modify existing records through this linked table. I used the account with sa privelleges yet i am unable to delete/update any records.
Does anyone know if there is any fix or am i doing anything wrong ?
View 3 Replies
View Related
Apr 30, 2004
I'm using ODBC to link a SQL table to access and using Access as the frontend, so to speak. Trying to write a query, but I get Timeout Error.
I need to resolve the timeout error, I've tried several things in the ODBC
configuration, but no solution yet.
thx
jm
View 7 Replies
View Related
May 26, 1999
I currently have a database that I can update in Microsoft Access by going into datasheet view of the table and just changing the value of a given field.
Here is the problem. I have made a copy of this database by backing it up and restoring to a new database. Now when I go into Access datasheet view to update, I get the following error: "ODBC--update on a linked table '<tablename>' failed". I have tried restoring to a database on the same server as well as others and have checked for differences set for each database as well as differneces in indexes. Any suggestions would be appreciated.
Thanks in advance
Keith
View 2 Replies
View Related
Mar 11, 2004
Can the DTS execute SQL task recognize a table in an Access database that is linked from another Access database?
I'm attempting to use a DTS Execute SQL task connected to Access1.mdb to make a new table in Access1.mdb that is using a Subquery from a linked table in Access2.mdb . (I know it sounds convoluded..) Its giving me an error in my FROM clause as if it can't recognize the linked table from Access2.mdb.
The reason why I'm doing this is that I'm not sure how to run an Access make table query from DTS that will overwrite the existing table in Access. I get an error that the table already exists.
Any Help?
View 1 Replies
View Related
Jan 15, 2008
I'm trying to set up a linked table from SQL Server 2005 to MS Access. The .mdb file resides on a network share and I'm unable to connect to it. If i copy the database to a folder on the server that SQL is installed, it works fine. Any help would be greatly appreciated. Below is the error message that I'm receiving.
TITLE: Microsoft SQL Server Management Studio
------------------------------
"The linked server has been created but failed a connection test. Do you want to keep the linked server?"
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "LINKSERVER".
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "LINKSERVER" returned message "The Microsoft Jet database engine cannot open the file '\serversharedatabase.mdb'. It is already opened exclusively by another user, or you need permission to view its data.". (Microsoft SQL Server, Error: 7303)
View 3 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
Nov 25, 2004
hi,
i have added a link server but it fails when i try to retrieve data.
SQL server in on the same machine
i have created the another SQL server instance
Main Server: SERVER1
Instance: SERVER1/NewServer
EXEC sp_addlinkedserver @server = SERVER1/NewServer, @srvproduct = 'SQL Server'
EXEC sp_addlinkedsrvlogin 'SERVER1/NewServer', false, NULL, 'sa', 'test'
select * from SERVER1/NewServer.db1.dbo.tbl_Prouduct
when i try to run the above query it gives me the error
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '/'.
anyone have suggestion??
Adi
View 2 Replies
View Related
Jun 29, 2006
I have an MS Access 2002 application that is distributed to a number of PCs around our office. The data for this application is stored on a central SQL Server that is linked in through ODBC.
This application has been in place for two years and working fine. We recently formatted and restored a PC, and now that particular PC has issues with the Access application.
Every time it tries to filter one of the linked SQL tables on a date field, Access goes unresponsive and GPFs out. If it's in a query that is behind a report, I get the old standard 'Catastrophic Failure'. If I open the table and right-click filter or run a query manually, Access GPFs.
I've tried recreating the ODBC, linking the tables through TCP/IP as well as Named Pipes. Nothing fixes it. All Windows and Office updates have been applied. This is not the first time we've reformatted a PC in the office, but we've never had this issue.
Has anyone run across this before?
Thanks!
-Ben
View 1 Replies
View Related
Jun 27, 2007
Hello,
Is there any body who create a linked object in sql server 2005 SP2 to Oracle9i sucessfully? I create a link using MS OLE DB provider for oracle, it is ok but I can select the table. It gave me invalid object name linkname.owner.tablename error. If I create a link using MS OLE DB provider for odbc, it failed saying system error.
"ODBC Driver Manager: Invalid connection string attribute. Specified driver could not be load due to system error 5(oracle in oraHome92)(SQL server, error 7303)"
I installed oracle odbc 9.2 on sql server 2005 server and tested it ok. Why link server object can't create?
any help, thanks!
View 1 Replies
View Related
Oct 19, 2006
OS: Windows XP (SP2)
App: MS Access 2003 (SP2)
DB: MS SQL 2000 (SP4, 2040)
User is using MS Access linked table to query database (using ODBC). User can open the query in MS Access. However when the user goes to export the data to MS Excel format, the user receives the error message:
"Unexpected Error from External Database Driver (22)."
A trace on the db, reveals that the user process is attempting to log in to the server as 'Admin'. However, when the query with the linked table is first opened, the user is prompted for username/password (non Windows authentication).
Any ideas? I googled and found some stuff, but nothing directly related and most of what I found was supposedly fixed with SP1.
Any help appreciated. I am not an Access guru.
Regards,
hmscott
View 1 Replies
View Related
Feb 15, 2008
We are using SQL Server 2005 (9.0.3054 on Windows 2003 RC2 SP2 with clustering two instances. We are running an instance on both boxes.
I have 2 linked servers to the same server on the first box (SQL1C) that use different SQL Logins to connect to the same box but to different databases. One uses the name of the server and one uses the name of the server with a 2 at the end of it. (servername and servername2). The second linked server was added recently and is used very frequently by the new application. The other thing is there is a server with the name servername2. They are SQL boxes but I am not sure what version or what they are running on. If it turns out to be important, I will ask.
Here's what is happening:
When we fail over to the second box, up until recently, we had no problems. All linked servers worked well. But since we have added this new linked server, when we fail over, the linked server no longer works and we get an error like from the Cold Fusion application:
[Macromedia][SQLServer JDBC Driver][SQLServer]TCP Provider: An existing connection was forcibly closed by the remote host.
I got a similar error in EMS, but did not copy it to save it. Since this is a production server, I can't really just try things.
We checked to make sure all IPs from box1 and box2 are allowed access to the server and they are not using IP blocking for inside the university domain.
There are no errors in the SQL Logs. I checked the application logs and found no errors for the linked server. The only error I could find and I didn't think it was related but I will include it was this:
Closed event notification conversation endpoint with handle '{4F54167A-F3D0-DC11-97FE-000E0CB2D7CA}', due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8470</Code><Description>Remote service has been dropped.</Description></Error>'.
Any ideas? One of our pplications goes down when the cluster fails over now. Help PLEASE!
Thanks,
Linda
View 7 Replies
View Related
Feb 1, 2008
FYI: I'm using SQL2005 on a windows 2003 server.
So, I've written an Instead of Trigger to update a foreign key field based on information in another field of the same record.
To add some error handling to the process I updated the Trigger to insert any records that don't have legitimate foreign keys into a second table.
This process works great when I test it by just adding a record using the table view in the SQL Management Studio or through a query run in the query browser.
However, when a record is added via an ODBC connection I get foreign key constraint errors and records are not added to the second table. If the foreign key is legit the record is added and the part of trigger that updates that keyed field executes just fine.
Is anyone aware of this issue? Is there a way around it?
I found the following MSKB article but I'm not sure if it applies to my situation:
http://support.microsoft.com/kb/304096
Here's my current code, if that track the problem in anyway:
Code:
ALTER TRIGGER UpdateTicketID
ON Email
Instead of INSERT
AS
IF ((Select charindex('{', [subject]) FROM Inserted) = 0)
BEGIN
INSERT INTO
BadEmail ([Subject], Sender, Body, EntryID, LastModificationTime, AttachmentLInk, SendTo, Cc, ContactID)
Select
[Subject], Sender, Body, EntryID, LastModificationTime, AttachmentLink, SendTo, Cc, ContactID
From
Inserted
END
ELSE IF ((Select substring([subject], charindex('{', [subject])+1, (charindex('}', [subject]) - charindex('{', [subject]))-1) From Inserted) NOT In (Select TicketID From Ticket))
BEGIN
INSERT INTO
BadEmail ([Subject], Sender, Body, EntryID, LastModificationTime, AttachmentLInk, SendTo, Cc, ContactID)
Select
[Subject], Sender, Body, EntryID, LastModificationTime, AttachmentLink, SendTo, Cc, ContactID
From
Inserted
END
ELSE
BEGIN
INSERT INTO
Email ([Subject], Sender, Body, ticketID, EntryID, LastModificationTime, AttachmentLink, SendTo, Cc, ContactID)
Select
[Subject]
, Sender
, Body
, substring([subject], charindex('{', [subject])+1, (charindex('}', [subject]) - charindex('{', [subject]))-1)
, EntryID
, LastModificationTime
, AttachmentLink
, SendTo
, Cc
, ContactID
From
Inserted
END
GO
Thank very much for any help.
-Will
View 1 Replies
View Related
Jun 27, 2001
I have SQL Server 7.0 running on a NT server. I need to get data from an access database on another NT server on the network. Can I have it mapped to a logical drive and create a linked server and get data to be used in my stored procs. Please help !!
Thanks
View 2 Replies
View Related
Nov 5, 2001
Hi,
I am running SQL Server 7.0 and am trying to set up a linked server to an access database so I can use a SQL7 stored procedure to update the access database.
I create the linked server as:
sp_addlinkedserver @server = N'LinkedServer',
@srvproduct = N' ',
@provider = N'Microsoft.Jet.OLEDB.4.0',
@datasrc = N'MyServerest2.mdb',
@catalog = N''
GO
I then try setting up the security in the security tabs to impersonate a user I have set up in the access database.
I also have NT security on the directory of the access database to limit the users who can access the directory.
My problem is that when I try to run the sql:
"select * from LinkedServer...IMS" (IMS is the tablename) I get the following error:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine cannot open the file 'ASPECT04accpactestest2.mdb'. It is already opened exclusively by another user, or you need permission to view its data.]
If I add "Everyone" tpo the security level on the NT directory it works fine, so I obviously have a security problem.
Can anyone tell me what user the sql is running under.
any ideas of the best way to set this up.
many thanks for the help
dave
View 1 Replies
View Related
Oct 5, 1999
I am new to SQL server. I have a pbm setting up a linked server to MS Access using ODBC. My MS Access database is in the Netware server. I have mapped the NT server to this Netware server using a binary account. Then I have setup the ODBC connection. I tested the ODBC its ok. But when I try to view to tables in the Linked server it gives me an error "Disk or Network Error". I am breaking my head for the past few days. If you have any solution. Pls reply. Thanks in advance.
View 2 Replies
View Related
Feb 9, 2007
After converting to SQL Server 2005 and VS2005 I am having a problem connecting to Linked Servers for Access databases. I have searched and read everything I can find, but the solution is still not evident. I have a lot of data integration and data conversion procedures to run and I am stuck at this point not understanding exactly what the problem is and how to resolve it. I believe it is somehow related to "double hop" and permissions with .NET. Many of the discussions are related to using .ASP but that is not involved in this situation.
The Linked Server is defined using the UNC path. The machines are not in a domain. They are in the same LAN segment. Connections will be made without using a security context is specified. A sample Linked Server definition:
EXEC master.dbo.sp_addlinkedserver @server = N'ACCESS EDUCATION', @srvproduct=N'OLE DB Provider for Jet', @provider=N'Microsoft.Jet.OLEDB.4.0', @datasrc=N'\Tacir2k3InfrastructureDatabasesJuly 2005 DatabasesEducationEduDb2005_TR_Db.mdb'
The error:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ACCESS EDUCATION" returned message "The Microsoft Jet database engine cannot open the file '\Tacir2k3InfrastructureDatabasesJuly 2005 DatabasesEducationEduDb2005_TR_Db.mdb'. It is already opened exclusively by another user, or you need permission to view its data.".
Machine 1 (Developer Workstation): XP SP2 with latest security patches; SS Management Studio, VS2005
Machine 2 (SQL Server server): Windows 2003 Server with SS2005. The Linked Servers are defined on an SS2005 instance on this machine.
Machine 3 (File server): Windows 2003 Server that contains the Access DB files
When I run SS Management Studio on Machine 2 (the database server), I have no problem accessing the Linked Server pointing to an Access DB file on Machine 3 and can compile or run SPs referencing the Linked Server.
When I run SS Management Studio on Machine 1 to execute an SP that references the Linked Servers, I get the above error.
When I use Machine 1 to compile an SP that references the Linked Server, I get the above error.
I tried granting Everyone Full Control to the folder that contains all the Access database subfolders but I get the same error when running from Machine 1.
View 6 Replies
View Related
Dec 8, 2006
Is there a way of INSERTing INTO an Access table using T-SQL? Here's an Access query that I need to do in SQL Server:
INSERT INTO tblCreditMemos
IN '\NW-ITD-FS2ilcas$AccessAutomated Budget ReportingABRBackEnd.mdb'
SELECT * FROM billc_cred03f_temp;
Any help appreciated.
View 5 Replies
View Related
