I have an application that uses a MS SQL 2005 database. When data is changed in certain tables, that data needs to be pushed to a MySQL box. I've added the MySQL server as a linked server in SQL 2k5 and I can delete and insert data with no problem, however when I try to update I get the following error (query included):
Code Snippet
with RemoteTable(r_AccountID, r_Name)
as (select AccountID, Name from openquery(RACS_TEST, 'select AccountID, Name from accounts'))
update RemoteTable
set r_Name = ex_Name
from Export_RACS_Accounts join remotetable
on r_AccountID = ex_AccountID
OLE DB provider "MSDASQL" for linked server "RACS_TEST" returned message "Row cannot be located for updating. Some values may have been changed since it was last read.".
Msg 7343, Level 16, State 4, Line 33
The OLE DB provider "MSDASQL" for linked server "RACS_TEST" could not UPDATE table "[MSDASQL]". The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized.
I would like to backup a database on a remote SQL 2005 server using T-SQL. The local server I want to issue the command from is also a SQL 2005 Server.
Do I need to use the openquery function? I am doing this as a job step so I will be executing the query from a local server. I do not want to use a SSIS package.
Running above code would give me the following error:
OLE DB provider "SQLNCLI" for linked server "eurodata3" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Msg 7344, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "eurodata3" could not INSERT INTO table "[SQLNCLI]" because of column "xmlCol". The user did not have permission to write to the column.
Location: memilb.cpp:1493
Expression: (*ppilb)->m_cRef == 0
SPID: 59
Process ID: 1660
Don't be misled by permission statement. I could successfully run similar query as long as the column is not xml on remote server. I would appreciate some ideas to get around with it.
Greetings -I'm using an Access front end to a SQL Server (2000) databas*e. Viaseveral steps, I create a temp table and manipulate the data* in it.Iwant to update the backend with this new data but my UPDATE *queryfailsas my temp table is local and the SQL database doesn't know *about it.There are no linked tables in the FE database.I have the following (DAO):Set Db = CurrentDbSet Qdf = Db.CreateQueryDef(TMP_QUERY_NAME)Qdf.connect = ConnectString()sqlString = "UPDATE tblRemote " & _"SET " & _"tblRemote.Some_Foo = tblLocal.Foo, " & _"FROM tblRemote INNER JOIN tblLocal " & _"ON tblRemote.Some_ID = tblLocal.Some_ID;"Qdf.sql = sqlStringQdf.ReturnsRecords = FalseQdf.Execute dbFailOnErrorIs there any way of doing this without adding a linked table*?Thanks, chris
Two tables t1 and t2 have the same schema but exist on two different servers. Which is the better technique for updating t2 and why?
/****** Object: Table [dbo].[t1] Script Date: 9/6/2007 9:55:21 AM ******/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[t1] GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN CREATE TABLE [dbo].[t1] ( k [int] IDENTITY (1, 1) NOT NULL , a [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , b [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , c [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , x [int] NULL , y [int] NULL , amt [money] NULL ) ON [PRIMARY] END
GO
/****** Object: Table [dbo].[t2] Script Date: 9/6/2007 9:55:44 AM ******/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[t2] GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN CREATE TABLE [dbo].[t2] ( k [int] IDENTITY (1, 1) NOT NULL , a [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , b [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , c [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , x [int] NULL , y [int] NULL , amt [money] NULL ) ON [PRIMARY] END
GO
-- Technique 1:
set identity_insert t2 on
insert into t2 (k,a,b,c,x,y,amt) select k,a,b,c,x,y,amt from t1 where not exists (select k from t2 where t1.k = t2.k)
set identity_insert t2 off
update t2 set a = t1.a, b = t1.b, c = t1.c, x = t1.x, y = t1.y, amt = t1.amt from t1 where t1.k = t2.k
-- Technique 2: set identity_insert t2 on
declare t1_cur cursor for select k,a,b,c,x,y,amt from t1 for read only
open t1_cur
declare @k int declare @a char(10) declare @b char(10) declare @c char(10) declare @x int declare @y int declare @amt money
fetch next from t1_cur into @k,@a,@b,@c,@x,@y,@amt while(@@FETCH_STATUS = 0) begin if exists(select k from t2 where k = @k) begin update t2 set a = @a, b = @b, c = @c, x = @x, y = @y, amt = @amt where (k = @k) end else begin insert into t2 (k,a,b,c,x,y,amt) values(@k,@a,@b,@c,@x,@y,@amt) end
fetch next from t1_cur into @k,@a,@b,@c,@x,@y,@amt end
close t1_cur deallocate t1_cur
set identity_insert t2 off
Thanks,
Joel K Database Adminstration/Application Development
I have been looking for a solution for this for some time and have came up empty handed.
I have 2 servers development box and a live box. Time has passed and my live box has a lot of new data in the database and now I need to update the dev box so I can properly test with real data. Problem here is I want to keep the records that are in the dev box, update them if they exsist on the live box, because live server may or may not contain that record and isert all records that are not on the dev box database.
I hope I am making some sense here, I think I am just making it more difficult then it has to be.
Currently we are running SQL Server 7 with SP1 installed on an NT box. I need to update a field in a table in Oracle. I setup a linkedserver in SQL Server using the Microsoft OLE DB Provider for ODBC (MSDASQL) and didn't have any problems selecting data from the linked Oracle tables using OPENQUERY. For example: SELECT * FROM OPENQUERY(STATSDEV, "Select * from CR_EXPORT") This query works fine.
However, now I need to update a field in the CR_EXPORT table. So I have written the following query and I tried to run it:
UPDATE OPENQUERY(STATSDEV, "SELECT * FROM CR_EXPORT WHERE REQUEST_NUMBER = 1") SET STATUS = 2
I got the following error message:
Server: Msg 7352, Level 16, State 1, Line 1 OLE DB provider 'MSDASQL' supplied inconsistent metadata. The object '(user generated expression)' was missing expected column 'Bmk1000'.
The Oracle table has a unique index, so I think that the issue might be associated with the version of the OLE DB provider or the Service Pack, but I can not find any documentation to support my theory. Any help you could give would be VERY appreciated!
Hi AllI am updating a local table based on inner join between local tableand remote table.Update LocalTableSET Field1 = B.Field1FROM LinkedServer.dbname.dbo.RemoteTable BINNER JOIN LocalTable AON B.Field2 = A.Field2AND B.Field3 = A.Field3This query takes 18 minutes to run.I am hoping to speed up the process by writing in OPENQUERY syntax.ThanksRS
All,Can someone help me with the following SQL and help me write it in anOPENQUERY format. I am running the following code from a SQL Server 7box, trying to update a table in an Oracle Linked Server. The coderuns fine, except it takes almost an hour to complete. I know if I runvia OPENQUERY,I can get the same done in much less time.Some of the relevant information is as follows:ORACLE_HBCPRD04 is a linked Oracle Server.SITEADDRESS is a table in Oracle#SiteAddress_New is a table in SQL Server.UPDATE ORACLE_HBCPRD04...SITEADDRESSSETCUST_ADDR1 = CASE WHEN SiteAddress_New.CUST_ADDR1 = '' THEN NULLELSE SiteAddress_New.CUST_ADDR1 END,CUST_ADDR2 = CASE WHEN SiteAddress_New.CUST_ADDR2 = '' THEN NULLELSE SiteAddress_New.CUST_ADDR2 END ,CUST_ADDR3 = CASE WHEN SiteAddress_New.CUST_ADDR3 = '' THEN NULLELSE SiteAddress_New.CUST_ADDR3 END,CUST_ADDR4 = CASE WHEN SiteAddress_New.CUST_ADDR4 = '' THEN NULLELSE SiteAddress_New.CUST_ADDR4 END ,CTY_NM = CASE WHEN SiteAddress_New.CTY_NM = '' THEN NULL ELSESiteAddress_New.CTY_NM END,ST_ABBR = CASE WHEN SiteAddress_New.ST_ABBR = '' THEN NULL ELSESiteAddress_New.ST_ABBR END,POST_CD = CASE WHEN SiteAddress_New.POST_CD = '' THEN NULL ELSESiteAddress_New.POST_CD END,CNTY_NM = CASE WHEN SiteAddress_New.CNTY_NM = '' THEN NULL ELSESiteAddress_New.CNTY_NM END,CNTRY_NM = CASE WHEN SiteAddress_New.CNTRY_NM = '' THEN NULL ELSESiteAddress_New.CNTRY_NM END,ADDR_STAT = NULL ,LAST_UPDATE_DATE = SiteAddress_New.LAST_UPDATE_DATEFROMORACLE_HBCPRD04...SITEADDRESS SiteAddress INNER JOIN#SiteAddress_New SiteAddress_New ONSiteAddress.LEGACY_ADDR_ID = SiteAddress_New.LEGACY_ADDR_IDWHEREUPPER(SiteAddress_New.PROCESS_CODE) = 'U'Best Regards,addi
I need to update an Oracle table from SQL Server. I am trying to use Openquery Update statement. I need to pass a integer as a parameter. I will be updating a date field and a status field.
This is the gist of what I need to do in a stored procedure
DECLARE @ID1 INT, @SQL1 VARCHAR(8000), @STATUS VARCHAR(10), @DATE DATETIME; SET @ID1 = 350719; SET @STATUS = 'COMPLETED'; SET @DATE = GETDATE(); SELECT @ID1; SELECT @SQL1 = 'UPDATE OPENQUERY(NGDEV2_LINK2, ''select DM_IMPORT_STATUS, DM_IMPORT_DATE FROM NEXTGEN.PARTY_HISTORY WHERE PARTY_HISTORY_ID = ' + CAST(@ID1 as nvarchar(30)) + ''')' SET DM_IMPORT__STATUS = @STATUS, DM_IMPORT_DATE = @DATE; EXEC (@SQL1);
UPDATE OPENQUERY(LINKEDSERVER, 'SELECT START_ORDER_NO FROM "OECTLFIL" WHERE "FILE_KEY" = 1') SET START_ORDER_NO = 0
----The START_ORDER_NO field contains a 48
SET START_ORDER_NO = 1~9
----The START_ORDER_NO field contains 49~57 respectively.
SET START_ORDER_NO = 10 ---The START_ORDER_NO field contains 12337 SET START_ORDER_NO = 11 ---The START_ORDER_NO field contains 12593 SET START_ORDER_NO = 12 ---The START_ORDER_NO field contains 12849
incrementing by 256 as I increase the value passed...
ASCII 48-57 are the characters 0-9. The string '10' consists of the two bytes with the values 49 (0x31) and 48 (0x30). It is being viewed in reverse byte order as the value 0x3031 which equals 12337 (48*256 + 49).
The LinkedServer is Pervasive SQL 2000i using 'OLE DB Provider for ODBC'
The START_ORDER_NO field is a Numeric(8,0)
I'm thinking some kind of Unicode, or translation or code page issue, but I haven't had any luck yet.
I'm not sure how difficult this is, I don't think I'm a neophyte but I'm feeling like one...
We have a database on a 2005 box, which we need to keep in sync with one on a 2014 box (until we can turn off the one on 2005). The 2005 database is still being updated with changes that must be applied to the 2014 database, given the nature of the data (medical documents) we need to ensure updates are applied to the 2014 database in very near real time (these changes are - for example - statuses, not the documents themselves).
Cunning plan #1, ulgy - not at all a fan of triggers - but use an after update trigger to run a sp on the remote box via a linked server in this format, with a SQL Server login for the linked server with permissions to EXEC the remote proc.
CREATE TRIGGER [dbo].[SourceUpdate] ON [dbo].[SourceTable] AFTER UPDATE AS SET XACT_ABORT ON; SET NOCOUNT ON; IF UPDATE(ColumnName)
[Code] ....
However, while the sp can be run against the linked server as a standalone query OK, when running it in a trigger it's throwing
OLE DB provider "SQLNCLI" for linked server "WIBBLE" returned message "The transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Procedure TheAfterUpdateTrigger, Line 19
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "WIBBLE" was unable to begin a distributed transaction.
Whether it actually possible to call a proc on a remote box via a trigger and if so what additional hoops need to be jumped through (like I said, it'll run OK called via SSMS)?
We have a MS SQL database with an Oracle linked server 'ALTTEST' We can Select, Insert, Delete and Update tables on the Oracle Db using OpenQuery, but how do I apply a table lock with a transaction? I've tried applying the code below, but it doesn't work. Any help appreciated. BEGIN TRAN SELECT * from openquery(ALTTEST,'select LAST_PIN_NUMBER from sys_params') WITH (TABLOCKX) COMMIT
I created a stored procedure like this:CREATE PROCEDURE SPASBEGINCREATE TABLE #T( C INT )INSERT INTO #T(C) VALUES (1)SELECT * FROM #TENDWhen I call it this way: EXEC SP, it works ok.But when I do it like this:SELECT * FROM OPENQUERY( MYSERVER, 'EXEC SP')I receive an error: Invalid object name '#T'Why?...*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
QUERY 1: SELECT * FROM OPENQUERY(MYSERVER, 'SELECT * FROM SCOPE() WHERE FREETEXT(''Text to Search'')') AS Docs
QUERY 2: SELECT MediaID, LawID, LawDate, Agreement, Name, NameSearch, LawType, LawNo, RegID, IssueNo, Attachment From Dept_LegalLaw INNER JOIN Dept_LegalMinistries ON Dept_LegalLaw.RegID = Dept_LegalMinistries.RegID INNER JOIN Dept_LegalLawType ON Dept_LegalLaw.LawID = Dept_LegalLawType.LawID WHERE 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1
both queries are working fine separately and I can generate the desired results separately, but I cannot merge them to get one single result for example, after a FREETEXT search I want to get some values from my local table (Query2) to display as one result. Like in Query1 FREETEXT will search the web page based on the given text and in Query2 will select the remaining data from the local database (Title, ID, Name etc etc).
QUERY 1: SELECT * FROM OPENQUERY(MYSERVER, 'SELECT * FROM SCOPE() WHERE FREETEXT(''Text to Search'')') AS Docs
QUERY 2: SELECT MediaID, LawID, LawDate, Agreement, Name, NameSearch, LawType, LawNo, RegID, IssueNo, Attachment From Dept_LegalLaw INNER JOIN Dept_LegalMinistries ON Dept_LegalLaw.RegID = Dept_LegalMinistries.RegID INNER JOIN Dept_LegalLawType ON Dept_LegalLaw.LawID = Dept_LegalLawType.LawID WHERE 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1
both queries are working fine separately and I can generate the desired results separately, but I cannot merge them to get one single result for example, after a FREETEXT search I want to get some values from my local table (Query2) to display as one result. Like in Query1 FREETEXT will search the web page based on the given text and in Query2 will select the remaining data from the local database (Title, ID, Name etc etc).
Hi, I have two queries as under:QUERY 1:SELECT * FROM OPENQUERY(MYSERVER, 'SELECT * FROM SCOPE() WHERE FREETEXT(''Text to Search'')') AS DocsQUERY 2:SELECT MediaID, LawID, LawDate, Agreement, Name, NameSearch, LawType, LawNo, RegID, IssueNo, AttachmentFrom Dept_LegalLawINNER JOIN Dept_LegalMinistries ON Dept_LegalLaw.RegID = Dept_LegalMinistries.RegIDINNER JOIN Dept_LegalLawType ON Dept_LegalLaw.LawID = Dept_LegalLawType.LawIDWHERE 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1both queries are working fine separately and I can generate the desired results separately, but I cannot merge them to get one single result for example, after a FREETEXT search I want to get some values from my local table (Query2) to display as one result. Like in Query1 FREETEXT will search the web page based on the given text and in Query2 will select the remaining data from the local database (Title, ID, Name etc etc).Try many thing but no success yet.Need urgent help.Thanks
Hello Everybody / Anybody, Sorry but exiting problem!
The Problem: OPENQUERY throwing error [Error 7357]when the source SP uses temporary table. Description : Need to validate data against master list. My combo on UI has a source Stored Proc(contains a temp table in it). I'm importing data from Excel. Before import, I want to validate it against my master list values.
[say field Priority has master values "High, Medium,Low".] and in excel user has added 'ComplexHigh' under priority field] In this case, my import validator StoredProc should not accept value 'ComplexHigh' as it is not present in my Priority master list]
I'm preparing a temp table tabName containing o/p of SP, it works fine zakkas if my SP usp_SelectData does not contain temp table. I think you got what the situation is!! Woh!
Note : I have searched net for this and found nothing! So its challenge for all of us. TRY OUT!! ------------------------------------- The Code ----------------------------
create proc usp_SelectData as create table #xx (FixedCol int) insert into #xx select 1 union select 2 select * from #xx drop table #xx
create proc usp_SelectData2 as create table xx (FixedCol int) insert into xx select 1 union select 2 select * from xx drop table xx -- Please replace MyDB with your current Database SELECT * INTO tabName FROM OPENQUERY('EXEC MyDB.dbo.usp_SelectData')
-- Throws Error 7357 : [Could not process object 'EXEC MyDB.dbo.usp_SelectData'. The OLE DB provider 'SQLOLEDB' indicates that the object has no columns.] SELECT * INTO tabName FROM OPENQUERY('EXEC MyDB.dbo.usp_SelectData2') -- Works fine
One of our Oracle Tables changed and I am wondering if there's any way that I can query all of our Stored Procedures to try and find out if that Oracle Table Name is referenced in any of our SQL Server Stored Procedures OPENQUERY statements?
hi all, i have a local SQL server running with my Products table, now very soon we want to launch a website (asp.net 2.0 with sql 2k5), hosted on a shared environment, which synchronises with our local SQL. Replication is out of the question. i tested already with a webservice on our local server, but i can't find the right way how to periodically update our online Db.since our website has over 200 visits a day, i can't do a check everytime the code is launched.Our local server has a slow upload speed + is not 99.9% garantueed online... the dataset has a 1Mb file size. i do have a boolean which indicates if there are new products or not, in this way i don't have to download the full dataset on my local SQL.(i can provide a list with only the new products and a list of products which have to be deleted, but still, i'm looking for the best way WHEN to do the update) Forcing an update from my local server to the online server seems a problem since i have restricted rights on the online server.
Hi Everyone, I develop a asp.net 2.0 site with Sql2005 Database, now i want to publish it on a webhosting service. Once I copy the DB to app_data folder on webhosting, what i can do to update data from my local sql2000 server? I want update data daily, may I use SQL replication? How? I can access my sql2005 DB by FTP? Any ideas? Thanks a lot. Regards, Bordonhos
i have a local SQL Server Express 2005 on my local machine and i want my software to update from the SQL Expres to the SQL Server when the user click the save button to keep an image of the local database on the remote server without having to backup a file and restore it?
Hi, I'm using "Microsoft SQL Server Database Publishing Wizard" to import and create database on a remote server. Is there a convenient tool to update SQL database on a remote server to match with database that I have on my computer?
I have a database on my web hosting site. It is a sql server 2005 database that was created in sql server management studio express. I can access this database in code using a connection string and do things like show results on my page. However I now want to do things like delete certain records, or update stored procedure criteria, usually I would do this by running sql in management studio but as my database is now online with the hosting company how would I do this?
Hiya! I'm developing an app. using SQL Server 7 (as the back end) for a company supporting approx. 200 unrelated clients, all in different locations. There is no LAN or WAN connection between them, so we'd probably need to use TCP/IP. The problem is as follows: We have dynamic reports in our app. which are run based on data in two tables. Whenever we add a new report we'd like to send it to all of our clients i.e. update their tables to reflect the additional rows of new report info. How would this best be handled, by DTS or Replication or BCP? And how could it be done with either?
Hi,I have table with three columns as belowtable name:expNo(int) name(char) refno(int)I have data as belowNo name refno1 a2 b3 cI need to update the refno with no values I write a query as belowupdate exp set refno=(select no from exp)when i run the query i got error asSubquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used asan expression.I need to update one colum with other column value.What is the correct query for this ?Thanks,Mani
Requirements: We have Our Local DataBase(SQL Server) Guess eg. DBLocal We have a requirements to update this DBLocal Table Data with Other Database DBRemote(SQL Server) table data. This Task is Schedule in a Week. Means We needs to update this DBLocal Table Every Week from DBRemote Data.
Both Side we have only one table. Means Source is One Table and Destination is Table One.
Right Now I have a connection string for DBRemote(SQL Server) and SQL Statement for getting Data from DBRemote(SQL Server).
can any one tell me what I need to do for achive this requirement?
please provide me link also; from there i can get enough information for my requirements.
UPDATE CD SET col1=SR.col1,col2=SR.col2,col3=SR.col3,col4=SR.col4,col5=SR.col5,col6=SR.col6,col7=SR.col7,
col8=SR.col8,col9=SR.col9,col10=SR.col10
FROM LNKSQL1.db1.DBO.Table1 CD
join Table2 USRI on USRI.col00 = CD.col00
join table3 SR on USRI.col00 = SR.col00
Here, I'm trying to tun this from an instance and do a remote update. col00 is a primary key and there is a clustered index that exists on this column. When I run this query, it does a 'select * from tabl1' on the remote server and that table has about 60 million rows. I don't understand why it would do a select *... Also, we migrated to SQL 2005 a week or so back but before that everything was running smooth. I dont have the execution plan from before but this statement was fast. Right now, I can't run this statement at all. It takes about 37 secs to do one update. But if I did the update on a local server doing remote joins here, it would work fine. When I tried to show the execution plan, it took about 10 mins to show up an estimated plan and 99% of the time was spent on Remote scan. Please let me know what I can do to improve my situation. Thank you
In an ASP, I have a dynamically created SQL statement that amounts to "SELECT * FROM Server1.myDB.dbo.myTable WHERE Col1 = 1" (Col1 is the table's primary key). It returns the data immediately when executed.
However, when the same record is updated with "UPDATE Server1.myDB.dbo.myTable SET Comments = 'blah blah blah' WHERE Col1 = 1", the page times out before the query can complete.
I watched the program in Profiler, and I saw on the update that sp_cursorfetch was being executed as an RPC once per each row in the table. In a table of 78000 records, the timeout occurs well before the last record is fetched, and the update bombs.
I can run the same statements in Query Analyzer from a linked server and have the same results. The execution plan shows that a Remote Query is occurring on the select that returns 1 row, and a Remote Scan is taking place on the update scanning 78000 rows (I guess this is where all the sp_cursorfetch calls are happening...?).
How can I prevent the Remote Scan? How can I prevent the execution of the RPC sp_cursorfetch for each row in the remote table?
