Update Openquery (linkedserver, 'select...) Set Field = #
Sep 23, 2004
After running the following OpenQuery...
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...
All- Please advise how to configure a gridview so that when introducing non-editable fields to the select statement, the edit function doesn't crash with a "Procedure or function (UpdateCommand) has too many arguments specified." The scenario: Update works find when I have a gridview based on the following SPs:
SELECT: 1 ALTER PROCEDURE dbo.H2SelectCommand 2 AS 3 SET NOCOUNT ON; 4 SELECT headcount_id, person_id, act_session_id, no_answer 5 FROM headcount 6
INSERT:
1 ALTER PROCEDURE dbo.H2UpdateCommand 2 ( 3 @person_id smallint, 4 @act_session_id smallint, 5 @no_answer bit, 6 @Original_headcount_id smallint, 7 @headcount_id smallint 8 ) 9 AS 10 SET NOCOUNT OFF; 11 UPDATE [headcount] SET [person_id] = @person_id, [act_session_id] = @act_session_id, [no_answer] = @no_answer WHERE (([headcount_id] = @headcount_id)); 12
HOWEVER, when I alter the select command so that it includes a column (person.person_name) from a parent directory like this:
1 ALTER PROCEDURE dbo.H2SelectCommand 2 AS 3 SET NOCOUNT ON; 4 SELECT headcount_id, person.person_name, headcount.person_id, act_session_id, no_answer 5 FROM headcount 6 7 INNER JOIN person 8 ON headcount.person_id = person.person_id
...and then proceed to do an Edit/Update operation, I get the pesky "Procedure or function H2UpdateCommand has too many arguments specified" message. The only obvious thing I could think to try was to configure the new column in the gridview to be Read Only, but this didn't help. Do I have to alter the Update SP to somehow account for the new field, even though the warning already says that there's too many arguments in it? Any ideas on how to fix this error would be appreciated! -Kurt
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 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 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);
I have the following SQL. I need the results to be in the @NSTATUS variable. How do I do this?
DECLAREÂ Â Â @HISTORY_IDÂ Â Â Â Â Â INT, Â Â Â Â Â Â @NSTATUSÂ Â Â VARCHAR(20), Â Â Â Â Â Â @IMPORT_DATEÂ Â Â DATETIME, Â Â Â Â Â Â @TSQLÂ Â Â Â Â Â Â Â Â VARCHAR(8000); SETÂ Â Â Â Â Â @HISTORY_ID = 350721; Â Â SET @TSQL = 'SELECT DM_IMPORT_STATUS FROM OPENQUERY(NGDEV2_LINK2, ''SELECT DM_IMPORT_STATUS from NEXTGEN.PARTY_HISTORY WHERE PARTY_HISTORY_ID = ''''' + CAST(@HISTORY_ID as nvarchar(30)) + ''''''')' Â Â SELECT @TSQL, @HISTORY_ID; Â Â EXEC (@TSQL) ;
If I have a table with 1 or more Nullable fields and I want to make sure that when an INSERT or UPDATE occurs and one or more of these fields are left to NULL either explicitly or implicitly is there I can set these to non-null values without interfering with the INSERT or UPDATE in as far as the other fields in the table?
EXAMPLE:
CREATE TABLE dbo.MYTABLE( ID NUMERIC(18,0) IDENTITY(1,1) NOT NULL, FirstName VARCHAR(50) NULL, LastName VARCHAR(50) NULL,
[Code] ....
If an INSERT looks like any of the following what can I do to change the NULL being assigned to DateAdded to a real date, preferable the value of GetDate() at the time of the insert? I've heard of INSTEAD of Triggers but I'm not trying tto over rise the entire INSERT or update just the on (maybe 2) fields that are being left as null or explicitly set to null. The same would apply for any UPDATE where DateModified is not specified or explicitly set to NULL. I would want to change it so that DateModified is not null on any UPDATE.
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded) VALUES('John','Smith',NULL)
INSERT INTO dbo.MYTABLE( FirstName, LastName) VALUES('John','Smith')
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded) SELECT FirstName, LastName, NULL FROM MYOTHERTABLE
I have 2 servers one has SQL 7.0 running on NT4.0 and another has SQL 2000 running on W2K Server. I have established a linked server for SQL 2000 from SQL 7.0.
I don't have any problem of creating linked servers for sql 2000 from sql 7.0. I am also able to create linked server logins to access the sql2000 tables. Even I am able to see the tables on the Databases.
Im sorry if im in the wrong section. My problem is a very common one and it has been hard for me to find the fix.
Ive gone thru Component Services, Firewall settings, Registry and Microsoft Sites, but to avail.
"The 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 the specified transaction coordinator. ] OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a]."
My Begin Tran doesnt start at all.
begin tran use smartpos go
set identity_insert smartpos.dbo.smp_product on
insert into smartpos.dbo.smp_product (prodId ,[Description] ,barcode ,BuyPriceExclVat ,BuyPriceVatTYpeId ,point ,SupplierID ,Ref ,QtyType ,QtyFactor ,MinStock ,MaxStock ,Active ,ItemType ,ClientLastUpdate ) select prodId ,[Description] ,barcode ,BuyPriceExclVat ,BuyPriceVatTYpeId ,point ,SupplierID ,Ref ,QtyType ,QtyFactor ,MinStock ,MaxStock ,Active ,ItemType ,LastUpdate from server.smartpos.dbo.smp_product svr where not exists (select prodid from smartpos.dbo.smp_product where prodid=svr.prodid)
SELECT * FROM ServidorPrincipal.BDPrincipal.dbo.tblCADENA;
Because it sends this message:
Server: Msg 7356, Level 16, State 1, Line 1 OLE DB provider 'SQLNCLI' supplied inconsistent metadata for a column. Metadata information was changed at execution time.
OLE DB error trace [Non-interface error: Column 'ID_CADENA' (compile-time ordinal 1) of object '"BDPrincipal"."dbo"."tblCADENA"' was reported to have changed. The exact nature of the change is unknown].
Hi all, I have a problem about a query to update a table
UPDATE Email SET EmailDT='31 Mar 2004' WHERE Idx={BDF51DBD-9E4F-4990-A751-5B25D071E288}
where Idx field is a uniqueidentifier type and EmailDT is datetime type. I found that when this query calling by a VB app. then it have error "[Microsoft][ODBC SQL Server Driver]Syntax error or access violation" and i have tried again in Query Analyzer, same error also occur, the MS SQL server is version 7. Please help. thanks.
Not a SQL guy but can do enough to be dangerous :)Trying to update a record. We have records that have a field with datasurrounded by some comment text such as *** Previous Public Solution*** Start and *** Previous Public Solution *** End . What I am tryingto do is write a SQL statement that will:Check that field C100 = TICKET0001 (to test with one record beforerunning on whole db)Check that field C101 is = ClosedCheck that field C102 is nullCopy field C103 data to field C102 and strip out any words such as ***Previous Public Solution *** Start and *** Previous Public Solution*** endThanks for any help!Kevin
I'm trying to change the datasource of a SQL Server LinkedServer using SMO
I've got the relevant linkedserver as an object, changed the DataSource property, and selected it again to confirm the change. However, the change is lost as soon as the object is destroyed. Looking at BoL I think I need to be using alter() method of the linkedserver class but I just get an error Alter failed for LinkedServer '<servername>'.
any ideas how I should be using this class to do what I want?
I can post my script (PowerShell) if it would help.
I'm copying almost all contents of one table from one server/db to another. In relation to speed, what is the difference of using SSIS vs a linked server and sp? I'm going to do the benchmarks, but I'm curious about behind the scenes kinda stuff, theoretically which one if any should be faster and why?
Hello All, I created two database instances in the same MS SQL SERVER 2005 named hafeez and local. I created a linked server from hafeez database to local named HTOL
I created a dummy table in local database named samplocal.
Now i am able to list the records using the linked server, for this i used the following query.
SELECT * FROM OPENQUERY(HTOL,'SELECT nameandval FROM SAMPLOCAL');
Now the problem is, i am not able to insert the rows into the samplocal table using linkedserver. I am using the following query to insert the rows and getting the following errro.
INSERT INTO OPENQUERY(HTOL,'SELECT nameandval FROM SAMPLOCAL') VALUES('tertertetttwertw');
Msg 7356, Level 16, State 1, Line 1 The OLE DB provider "SQLNCLI" for linked server "HTOL" supplied inconsistent metadata for a column. The column "nameandval" (compile-time ordinal 1) of object "SELECT nameandval FROM SAMPLOCAL" was reported to have a "Incomplete schema-error logic." of 0 at compile time and 0 at run time.
Is the above query correct? or shall i miss anything?
I have a question when I work on LinkedserverThe Linkedserver name is [Hp-server],the Datebase name isNewexec,the Table name is Customers_CoypTestThe SQLScript is below:Update [Hp-server].Newexec.dbo.Customers_CoypTestset Unitname=b.Unitnamefrom [Hp-server].Newexec.dbo.Customers_CoypTest a joinNewexec.dbo.Customers_CoypTest bon a.Cid=b.Cid and b.Cid='Tony'The server returns ERROR like this:a) can not open this table '"Newexec"."dbo"."Customers_CoypTest"'(come from OLE DB provide server 'SQLOLEDB'). provide server do notsupport index scan on the data source.b) [OLE/DB provider returned message:Error occured whenmulti-operate.If possible, please check each OLE DB status value.Nowork had been completed.]c) OLE DB Error trace[OLE/DB Provider 'SQLOLEDB'IOpenRowset::OpenRowset returned 0x80040e21:[PROPID=DBPROP_COMMANDTIMEOUT VALUE=600 STATUS=DBPROPSTATUS_OK],[PROPID=Unknown PropertyID VALUE=True STATUS=DBPROPSTATUS_OK],[PROPID=DBPROP_IRowsetIndex VALUE=TrueSTATUS=DBPROPSTATUS_NOTSUPPORTED]].
I am not expert on SQL server, i need to be able to access different server from withing sproc,
1. Is Linked Server is the best aproach.
2. If yes then how i set it up that it take all authenticated users of this server to remote, i tried with impersonate but it gives error that NT authority dont hae access etc etc (when i try run a sql that accesses remote server)
3. The reason i need to access remote server is ... one of the that table in my db (logTable) is getting to big it is 10 times the size of rest of the db altogether so i though i move it out not just from same db actully put it on a seprate server. so that all the tasks about main db would become easy etc.
We currently have an application that primarily sits on SQL server and needs to access some legacy data in a DB2 database. The solution we are using is to use a linked server between the two servers. The problem with this is that complex queries occasionally cause the driver to fetch a complete table and filter that table locally. I wondered whether service broker (which I have never used) could allow me to access the DB2 database using MQSeries and thus speed up the application as our ODBC calls have low priority on our mainframe.
Thanks and sorry if this sounds a stupid use of service broker.
When running a query with a table from another SQL7 Server that is linked, if only 1 table from each server is specified, the query runs fast and returns only the data rows needed from the linked server. When another table is added on to query (on the server running the query) the query processor tries to run a remote query and return over 400,000 rows over the network. Any suggestions?
I need some help.I am trying to write a query which does the followingSELECT * from table1 where field1=(SELECT distinct field1 FROM table1WHERE field2='2005' or field2='2010')I need all the values from table1 which match any value from field 1from the subquery.Any help is appreciated.thanks
Hi. I'm not such an expert in sql and I wanted to update a table's fields only if the value that im going to update it with is not null. How do I do that?
Hello, I have a field in a table that I have to update to the number of the week. Information i use is from another field, which is text in the format like :
01022005 02142005 07082005 11222005 ... 12022005
mmddyyyy.
How to write an update to update empty field with a number of the week based on the value from another field.
I would like to query a table for a max value of one field for a distinct combination of two other fields. Let's call these fields RowID, ObjectID, and ObjectType. RowID is an auto-increment field, so for each distinct combination of ObjectID and ObjectType, there will be many values of RowID. To visualize an example:
Of these rows, I would only want 4, 5, and 6 (max values for distinct combination of ObjectID and ObjectType).
I hope I explained this clearly. I would imagine I'd need to use some form of nested query, but nothing I have tried so far has worked. I am using SQL 2005.
if a db field "mynum" of type int contains for example the number 543.Is there a way I can update this field without first selecting the value?so for example set the the current value to +1
in sql server I want to update the "UpdatedDate" field automatically when I change ANY other field in that specific record.it has to be set to the current server time.
I am trying to update a field in a temptable with the count of items in another table. To illustrate, CustomerID=23 and I want the number of occurences in the temp table. Here's the code which DOESN'T work:
INSERT INTO TempTable ( CustomerID, FirstName, LastName, DateAdded, AlbumPicture, LayoutCount ) SELECT Albums.CustomerID, Customers.FirstName, Customers.LastName, DateAdded, AlbumPicture, COUNT(*) FROM Layouts WHERE Layout.customerID = Albums.CustomerID FROM Albums JOIN Customers on (Albums.CustomerID=Customers.CustomerID)
Please take a look at the COUNT line. Here I want to count the occurences of a specific customerid in another table and put in into th LayoutCount field.
SQL server reports "Incorrect syntax near the keyword 'FROM'". Any ideas how to achieve this?
