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...
WHERE col1 = '7/20/2006'
AND col2 in (2,5,7,12,32,54,45,33)
I am curious to see if anybody has figured out a way to insert a Memo field from Access 97 into a Text field in SQL Server 7. The problem is that it seems a view must be used because the only way to access a linked server is with the notation 'linked_server_name...table_name.field_name'. This notation is okay for select statments (and creating views), but it is not okay for READTEXT, or WRITETEXT. Does anybody have any ideas? Thanks in advance.
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).
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.
I am using SQL Server to connect to iSeries DB2 on AS/400 with Linked Server through ODBC DSN. I was able to run SELECT using four-part naming convention in SQL Query Analyzer. But INSERT is not working. Here's my query:-
INSERT INTO LINKEDSERVER1.CATALOG1.SCHEMA1.TABLE1 (COL1) VALUES ('VAL1')
It generates the following error.
OLE DB provider 'MSDASQL' reported an error. [OLE/DB provider returned message: [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL7008 - TABLE1 in SCHEMA1 not valid for operation.]
The file is not journaling. I have already set COMMIT to *NONE. So it should work in SQL Query Analyzer.
Needless to mention that StarSQL executes the query without errors.
Hi, I am trying to insert data from a SQL (7.0) table into an Access table but I get an error message saying the MS DTC is nor running. Apart from E.M, how can you check this. Using Query Analyzer I can select form tyhe destination table but not insert - perhaps I have missed something - acn you please help?
hi everybody.I have linked db2 server .Select from this server goes fine but when i do insert
insert into pricing..UCIT.BOOM(A,B) VALUES(3,5) OLE DB provider 'MSDASQL' reported an error. The provider did not give any information about the error
case B SELECT * from OPENQUERY(pricing, 'insert into UCIT.BOOM(A,B) VALUES(3,5)') Server: Msg 7357, Level 16, State 2, Line 1 Could not process object 'insert into UCIT.BOOM(A,B) VALUES(3,5)'. The OLE DB provider 'MSDASQL' indicates that the object has no columns.
how to do insert into linked db2 server? I am running SQL 2000 sp2 on WIN20000 and DB2 UDB 7.2 service pack 4
I have two sql servers, I have defined each one as a linked server tothe other. I can mostly access the servers from one another, but I getthe following error on a sql insert.Insert statement...INSERT INTO [U1STSV02].[Custom Log Shipping].dbo.ls_secondary_files(database_name, tl_file_name, tl_applied, lsplanid, lssecid,compression_type) VALUES ('javaweb', 'c:', 'N', 1, 1, 0)i get an error messageServer: Msg 913, Level 16, State 8, Line 1Could not find database ID 10. Database may not be activated yet or maybe in transition.I can query the table with select using the followingselect * from [u1stsv02].[custom log shipping].dbo.ls_secondary_filesand I can delete rows from the table usingdelete from [u1stsv02].[custom log shipping].dbo.ls_secondary_filesI have searched Microsoft's site and googled for a while and cannot seemto find a solution.Both servers are running SQL Server 2000 with service pack 4Thanks in advance for any replies.Steve KuekesPhysicians Pharmacy Alliancejust remove the "1", "2", "3" from my email to reach me.
hi,on localServer i execute this queryINSERT INTO table (A, B, C)SELECT A, B, C FROM LinkedServer.myDB.dbo.tableeverything is fine. But if i execute this oneINSERT INTO LinkedServer.myDB.dbo.table (A, B, C)SELECT A, B, C FROM tableit is very slow. Is there any solution to make it any faster?
Both servers running SQL 2000I have set up on our local SQL server (using Enterprise Manager) a linkedserver running on our ISP. Just did new linked server and added remotepassword and login.The following three queries work:insert into LinkedServer.dbname.dbo.Table2select *from LinkedServer.dbname.dbo.Table1select *into LocalTablefrom LinkedServer.dbname.dbo.Table1insert into LocalTableselect *from LinkedServer.dbname.dbo.Table1This query, which is what we really want to do, does not work:insert into LinkedServer.dbname.dbo.Table1select *from LocalTableand returns the error: 'The cursor does not include the table being modifiedor the table is not updatable through the cursor.'I am new to all this and would welcome some help.Adrian
The issue I have that I can't insert 22.17 (or other odd fractions) into an Oracle RDB database through a linked server using the RDB ODBC Driver.
insert into dev_shlagh_fin...simon values (22.16) GO insert into dev_shlagh_fin...simon values (22.17) GO insert into dev_shlagh_fin...simon values (22.18)
(1 row(s) affected) Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'MSDASQL' reported an error. [OLE/DB provider returned message: [Oracle][ODBC]Numeric value out of range.] OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::InsertRow returned 0x80004005: ]. (1 row(s) affected)
The problem seems related to that outlined in PRB: Numeric Value Out of Range Error with MS Oracle ODBC Driver Version 2.5 or Higherin - Q Article 199293. This is directly concerned with Oracle not RDB and for select statements but the issue is seems basically the same - OLD MDAC 2.6 is fine, anything later and you get the error. The problem is the workaround does not offer any help for inserts. Reading between the lines and converting syntax to RDB SQL you have this:-
insert into openquery(dev_fin,'select CAST(quantity AS AS NCHAR(8))) From simon') VALUES (22.17)
Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'MSDASQL' reported an error. [OLE/DB provider returned message: [Oracle][ODBC][Rdb]%SQL-F-SYNTAX_ERR, Syntax error] OLE DB error trace [OLE/DB Provider 'MSDASQL' ICommandPrepare:repare returned 0x80004005: ].
This appears to be a syntax error as it fails with all values but only when you try to insert, if you just select, i.e.
Select * from openquery(dev_fin,'select CAST(quantity AS AS NCHAR(8))) From simon')
his worke fine.
I have tried all I can think of...
HELP anyone please.. Its crucial that this works and rolling back to MDAC 2.6 does not seem a viable option!
Hei,We have 2 MS SQL SERVER 2000 installed on 2 different servers (2 separatedmachines).I am triing to connect them så that when one row is added to the table inthe database in main server - then the same row is added to the same tablein the second server database.I made the insert trigger on the table in the first server ( the secondserver is added as a linked server):-----------------------------------------------------------------------------------------create trigger ti_myTabe1 on myTable1 for insert asbegindeclare ........BEGINinsert into server2.myDatabase2.owner.myTable2(column1, column2, column3)SELECT column1, column2, column3FROM inserted insEND......end-----------------------------------------------------------------------------------------When I run the statement in "SQL Query Analyzer"on the first server:insert into Table1 values(va1,val2,val3)then error is coming:Server: Msg 7391, Level 16, State 1, Procedure ti_myTabe1 , Line 19The operation could not be performed because the OLE DB provider 'SQLOLEDB'was unable to begin a distributed transaction.[OLE/DB provider returned message: New transaction cannot enlist in thespecified transaction coordinator. ]OLE DB error trace [OLE/DB Provider 'SQLOLEDB'ITransactionJoin::JoinTransaction returned 0x8004d00a].The straing thing is: if I run the statement in "SQL Query Analyzer"on thefirst server:insert into server2.myDatabase2.owner.myTable2 values(va1,val2,val3)then it works!But not inside the trigger!!! - What I am doing wrong ?Any idea is greatly appeciated.
I'm writing an insert trigger in one SQL Server database that is supposed to insert another record into a linked SQL Server database. I have the linked server set up and have been using it for a few weeks in queries and stored procedure with no problem. Now that I'm trying to use it within a trigger and it just bombs.
I'm getting the following message in one of my logs and I don't know what it means... "Failed to obtain TransactionDispenserInterface: XACT_E_TMNOTAVAILABLE". I've googled around, but can't really find anything. Any help would be appreciated.
I have configured a non-SQL linked server (via an OLE DB provider) and I wish to insert data into it via Service Broker but I am getting the following error in the SQL Server log: The activated proc [dbo].[sp_ mytableServiceProgram] running on queue TestDB.dbo.mytableQueue output the following: 'Cannot promote the transaction to a distributed transaction because there is an active save point in this transaction.'
As you see below, my strored proc. is not issuing any 'save trans' statements, so why is it not allowing me to wrap my code in a transaction? How else can I use a transaction (in order to not lose anything from the queue) and yet still be able to insert to the linked server?
CREATE PROC sp_mytableServiceProgram AS SET NOCOUNT ON;
-- This procedure continues to process messages in the queue until the -- queue is empty.
WHILE (1 = 1) BEGIN BEGIN TRANSACTION; --BEGIN DISTRIBUTED TRANSACTION; --Tried this but didn't help.
-- Receive the next available message WAITFOR ( RECEIVE TOP(1) -- just handle one message at a time @MessageTypeName = message_type_name, @MessageBody = message_body, @Dialog = conversation_handle FROM mytableQueue ), TIMEOUT 2000 ;
-- If RECEIVE did not return a message, roll back the transaction -- and break out of the while loop, exiting the procedure. IF (@@ROWCOUNT = 0) BEGIN ROLLBACK TRANSACTION; BREAK; END ;
SET @XML = CAST(@MessageBody AS XML);
INSERT INTO LINKEDSERVER.dbname.user.mytable SELECT tbl.rows.value('@doc_no', 'INT') AS doc_no, tbl.rows.value('@queryid', 'NVARCHAR(50)') AS queryid, tbl.rows.value('@ar_num', 'NVARCHAR(50)') AS ar_num, tbl.rows.value('@status', 'NVARCHAR(20)') AS status, tbl.rows.value('@creationtime', 'DATETIME') AS creationtime, tbl.rows.value('@note', 'NVARCHAR(250)') AS note, tbl.rows.value('@posted', 'NCHAR(1)') AS posted, tbl.rows.value('@kms', 'INT') AS kms, tbl.rows.value('@schresid', 'NVARCHAR(50)') AS schresid, tbl.rows.value('@resolution_code', 'NCHAR(8)') AS resolution_code, tbl.rows.value('@page_count', 'INT') AS page_count, tbl.rows.value('@new_serial_number', 'NVARCHAR(20)') AS new_serial_number, tbl.rows.value('@taskresolution', 'NVARCHAR(250)') AS taskresolution FROM @XML.nodes('/inserted') tbl(rows);
-- If the INSERT did not insert any rows, rollback. IF @@ROWCOUNT = 0 BEGIN ROLLBACK TRANSACTION; BREAK; END COMMIT TRANSACTION; END GO
I am getting error when I try Inserting data in sybase 12.5 using linked server from SQL2K5
I am able to select
Following is the code i am using.error is same for both stmts insert into l_syb_ibt.ibtqa.dbo.rajtest (id)values (1) insert openquery(l_syb_ibt, 'select id from rajtest where 1=0') values (1000)
please help.thanks in advance
following is the error OLE DB provider "MSDASQL" for linked server "l_syb_ibt" returned message "Transaction cannot have multiple recordsets with this cursor type. Change the cursor type, commit the transaction, or close one of the recordsets.". Msg 7343, Level 16, State 2, Line 1 The OLE DB provider "MSDASQL" for linked server "l_syb_ibt" could not INSERT INTO table "[l_syb_ibt].[ibtqa].[dbo].[rajtest]".
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.
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.
QUESTION: How do I use ADOX (VB) to create a DSNless linked table to a SQL Server Table in MS Access?
CRITERIA: - Will need a code skeleton that satisfies all conditions above (Including the minimum table properties required). - SQL Server Name: MySQLServer - Database Name: MyTestDB - Table Name: MyTestTable
SQL Server 7.0 (SP1) Error: ------ OLE DB provider 'SQLOLEDB' supplied inconsistent metadata. An extra column was supplied during execution that was not found at compile time.
A column was deleted from the a table on the linked server and now this message appears when using the linked server definition to access the table. Deleting/Recreating the Linked Server has no effect. I found an earlier note on this...but it just kind of ended with no resolution. Anyone have any thoughts on this now.
Hello I have a problem with setting relations properly when inserting data using adonet. Already have searched for a solutions, still not finding a mistake... Here's the sql management studio diagram :
and that causes (at line 67):"The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Question_SurveyTemplate". The conflict occurred in database "ankietyzacja", table "dbo.SurveyTemplate", column 'id'. The statement has been terminated. at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo batchCommands, Int32 commandCount) at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo batchCommands, Int32 commandCount) at System.Data.Common.DbDataAdapter.Update(DataRow dataRows, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable) at AnkietyzacjaWebService.Service1.createSurveyTemplate(Object o) in J:\PL\PAI\AnkietyzacjaWebService\AnkietyzacjaWebServicece\Service1.asmx.cs:line 397"
Could You please tell me what am I missing here ? Thanks a lot.
Hello!Does anybody know how to get tables structure of linked server (DBF tablesvia ODBC connection). I know that table structure of "normal" (not linked)server can get from systables and syscolumns tables, but now I need astructure of linked server tables.Thanks!
Trying to do this all day and googling for answers but found none, hopesomeone can help. Thanks in advance.select * intoOPENROWSET('SQLOLEDB','SERVER';'uid';'pwd',##test) from LocalTableReason: I am joining local tables with linked server tables using theformat "LinkedServer.database.owner.object" to execute a query, ittakes forever to execute since the tables joined on the remote servershave more than 50Mil records. I read somewhere that sql server needs tocopy the tables locally to the temp db and does the join there, hence Iwas hoping to dump the data of the local database into a temp table onthe remote server and then do a join with OPENQUERY, which will executethe query on the linked server and return the results.
When creating a table I want to be able to specify not only the db to create it on but also which server to create it on. I have two servers that are linked together, I can view all data without issue.
Doing further research it looks like with the create table command you can tell it the new table name and the database but you can't tell it which server to use. Is there a way of doing this?
CREATE TABLE LAPTOP.database.dbo.tableName (a INT) gives the following error:
The object name 'LAPTOP.database.dbo.' contains more than the maximum number of prefixes. The maximum is 2.
I am new to linked servers so basically my question is, how do you point to a server within sql before I execute the create table command?
We're having a few linked servers in our company. some tables in one of the linked servers include columns (better saying, foreign keys) of tables in another server. I wonder if there's a way to create a foreign key referencing one column in another server. That is, suppose there's Column A in table A in Server A which references Column B in table B in server B, is there a way to create column A as foreign key referencing column B?
We're having a few linked servers in our company. some tables in one of the linked servers include columns (better saying, foreign keys) of tables in another server. I wonder if there's a way to create a foreign key referencing one columns in another server. That is, suppose there's Column A in table A in Server A which references Column B in table B in server B, is there a way to create column A as foreign key referencing column B?
Hi... I was hoping if someone could share me some thoughts with the issue that I am having at the moment.
Problem: When I run the package in my local machine and update local SS DB/table - new records writes OK in the table. BUT when I changed my destination meaning write record into another physical SS DB/table there is no INSERT data occurs. AND SO when I move/copy over that same package into another server (e.g. server that do not write record earlier) and run it locally IT WORKS fine too.
What I am trying to do is very simple - Add new records in a SS table using SSIS . I only care for new rows and not even changed rows. Here is my logic - 1. Create Ole DB source to RemoteSERVER - using SELECT stmt 2. I have LoopUp component that will look for NEW records - Directs all rows that don't find match and redirect rows (error output). 3. Since I don't care for any rows that is matched in my lookup - I do nothing or I trash the rows 4. I send the error rows (NEW rows) into OleDB destination
RESULTS when I run the package locally and destination table is also local - WORKS FINE; But when I run the package locally and destination table is in another Sserver (remote) - now rows is written.
The package is run thru BIDS manually so there is no sucurity restrictions attached to it.
I am not sure what I am missing. And I do not see error in my package either. It is not failing.