.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

OPENQUERY Update statement errors

Posted By:      Posted Date: October 09, 2010    Points: 0   Category :Sql Server

Right now, I am struggling with OPENQuery on multiple fronts.  a) Not sure if the stored proc code below is correct b) Getting multiple syntax and other errors with the code and its a high time for me to resolve this on an urgent basis.


Here's what I get when I run the following Create Stroed Proc command:


Server: Msg 7321, Level 16, State 2, Procedure sp_GroundSure_UpdateSentOrFailedRequest_new, Line 25
An error occurred while preparing a query for execution against OLE DB provider 'MSDAORA'.
[OLE/DB provider returned message: ORA-00936: missing expression
OLE DB error trace [OLE/DB Provider 'MSDAORA' ICommandPrepare::Prepare returned 0x80040e14].


Any help will be helpful in this regard.


CREATE PROCEDURE sp_UpdateSentOrFailedRequest_new



View Complete Post

More Related Resource Links

Update statement not updating records and returns no errors

I am running an Update statement against an SQL database on server 2008. The update runs but does not update the record, but returns no errors. The database is part of a commercial help desk package called TrackIt 8.5 which was just a fresh install on a new web server and a new sql server. The update is running from a web form we use to automatically create active directory accounts. After the account is created the web form closes out the work order in the Trackit db. This was working fine when the system was running on Server 2003 and SQL server 2005. Running Trackit version 7.02. The login use to access the database has full permissions to read and write to the database, just as it did on the old server which never had an issue updating. Odd thing is, if I log onto the SQL server and make a change to any single field in that record. I can then run the web form and it updates the record normally. All other update statements that are running on other databases are running fine. Anyone have any suggestions on what to check that may be causing this issue?

update statement which i can use in execute sql task.

I am having a table in the following manner.I am having  yearmonth till 202012 and from 201005 to 202012 I am having activeflag,tablename1,tablename2,tablename3,tablename4 as zero. This table needs to be update before runing the package.Before I load data for tablename1 for 201005 I need to update that 201005 for tablename1 to 1 and activeflag to 1 and  I need to update the 201002 tablename1 to zero.and when I load data for 201006 I need to update first that active flag =1 for 201006 and tablename1 to 1 for 201006 and then Update 201003 tablename1 to zero.i.e in tablename1 at a given time i need to see only 3 (yearmonth)=1 and update the first non zero record to zero.similary i need to update other tables also.  yearmonth                      Activeflag           tablename 1       tablename2         tablename 3   tablename4 201001                             0                 &nb

Update Statement help from Multiple database

Hi I am having two separate datbase on same sever one datasbase name private from where i am executing my below query and other name is ODS Table name in Private=[A].[DimCapabilitySpecialty] Table name in ODS = [A].[CapabilitySpecialty]   both of them having column SpecialtyCd   i want to update the flag value of ODS table where there is particular specialitycd missing in source means table in private database   so for that i wrote below query   update A.DimCapabilitySpecialty set ActiveInd ='I' where SpecialtyCd !=[ODS].[A].[CapabilitySpecialty].[SpecialtyCd]   But it is giving me this error   Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "ODS.CapabilitySpecialty.SpecialtyCd" could not be bound.

Scope Statement Errors - Please Help ASAP!

Hello, I have  script that validates a criteria and then performs calculation based on the criteria. In general this is what is does, If account within the range of 100000 and 399999 then YTD AMT + Beg Balance else YTD AMT  Here is the script: CREATE MEMBER CURRENTCUBE.[Measures].[test]  AS NULL, VISIBLE = 1  ; Scope([Measures].[Actual Amount]); Scope (  EXCEPT  ( [COA AHC].[Account].MEMBERS, {[COA AHC].[Account].[100000]:[COA AHC].[Account].[399999]})) ;   This = Sum(YTD([Period].[YP - Hierarchy]), [Measures].[Actual Amount]); End Scope ; END SCOPE; Scope   ( {[COA AHC].[Account].[100000]:[COA AHC].[Account].[399999]},[Measures].[Actual Amount]) ;   This = Sum(YTD([Period].[YP - Hierarchy]), [Measures].[Actual Amount]) + [Measures].[Actual Beg Bal] ; End Scope ; It throws the following errors: 1) Error 1 MdxScript(FINANCE) (14, 1) An arbitrary shape of the sets is not allowed in the current context.   2) Error 2 MdxScript(FINANCE) (19, 1) The END SCOPE statement does not match the opening SCOPE statement.  3) Error 3 The END SCOPE statement does not match the opening SCOPE statement.  Can anyone please help me ASAP to resolve this? I am not sure what am doing wrong. Thank you all in advance. Thanks, KJ

Update query from select statement


Cana you I need to do an update from a select statement...i'm really close here but have not quite figured out the nuance...

the full select statement provides correct results and i've joined the outer table with the inside table....can you make recommendations?

BEGIN TRAN --rollback

UPDATE geprs_price..T_ITEM_PRC_CHG 


		FROM geprs_price..T_ITEM_PRC_CHG gep
		and gep.prc_eff_dt = dm.prc_eff_dt
		LEFT JOIN construct..T_AD_USERS con
		ON dm.CONFIRM_BY  = con.UID
		LEFT JOIN construct..T_AD_USERS crt
		ON dm.CREATE_BY  = crt.UID

		AND dm.PRC_TYP_ID in (39)



CLR Timeout when running an update statement


I have created several CLR'S for several webservices but i had no need to update my tables until now, most of my CLR'S return data to the cusstomer but now i have a CLR that receives information from a webservice and updates my data, When running a Select statement within the CLR everything is fine but when I send and update statement the CLR times out without good explanation. the code below is what Im using and that will reproduce the problem.

Partial Public Class StoredProcedures
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    <System.Security.Permissions.PermissionSet(System.Security.Permissions.SecurityAction.Assert, Unrestricted:=True)> _

Public Shared Sub sp_Request_TransNetwork()

        Using conn As New SqlConnection("context connection=true")

                       ssql = "select fields from my table" --this works perrfect!!!


Use XML DML with a select or update statement.

Given an SQL XML table column with contents like this:

  <taxcert>A18 3865 10</taxcert>
  <exempt_reason />

I'm trying to modify it with an SQL query like this:

        [XMLData].modify('insert <entityusecode_id />
              after (/fld)[1]

This SQL causes this error:

Msg 8137, Level 16, State 1, Line 39
Incorrect use of the XML
data type method 'modify'. A non-mutator method is expected in this context.

Deadlock: Store Procedure that only contains select statement owns an update key lock


I have a seemingly simple deadlock graph that contains 2 SP's. One SP is updating a table, while another SP (the victim) is selecting from it. The interesting thing about the graph is that the SP that contains the select (and only a select) is shown to own an UPDATE lock on the table that SP2 wants to update. How is this possible?



Random errors with the clr.dll 4.0.30319.1 after update to .NET Framework 4.0



After updating to .NET Framework 4.0 we have got some random crashes in our production environmnent.

We get a error in clr.dll but having problem to finding the root error. Do you have some tips and tricks?

The event logg looks like this:


Event Type: Error

Event Source: .NET Runtime 4.0 Error Reporting

Event Category: None

Event ID: 1000

Date: 10/8/2010

Time: 12:45:08 AM

User: N/A

Computer: [The Computer]


Faulting application [TheApplication.exe], version, stamp 4c934361, faulting module clr.dll, version 4.0.30319.1, stamp 4ba1d9ef, debug? 0, fault address 0x00003e54.


For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.


0000: 41 00 70 00 70 00 6c 00   A.p.p.l.

0008: 69 00 63 00 61 00 74 00   i.c.a.t.

0010: 69 00 6f 00 6e 00 20 00   i.o.n. .

0018: 46 00 61 00 69 00 6c 00   F.a.i.l.

0020: 75 00 72 00 65 00 20 00   u.r.e

what is wrong with the Following Update Statement


I have a Select that is defined like this



 select C.Number,A.GrpName from TBL_CNTC C
    ON A.CntcID = C.ID
    where A.GrpName <> C.Number


and it give me records that dont match in the the field "GrpName" and "Number"  and now i want to Update one table from the other like this



                UPDATE TBL_CNTC 
		SET Number = A.GrpName
		ON A.CntcID = C.ID
		where A.GrpName <> C.Number

Now as you can see above i  i want to Update the table TBL_CNTC from the table TBL_ACTV and it looks like like it updated data

(167 row(s) affected)


Update statement with CASE inconsistently updates VARCHAR column, stripping leading zeroes.


I have an update statement below that strips the leading zeroes off the front of AU. This value will have typically '0001234' or something like this. In my case statement, I have 2 scenarios: 1) when I use the commented out phrase (case when 1=1), the statement leaves AU alone as it should when the condition is met. 2) When I use the other statement, it STRIPS the leading zeroes off AU - I don't want it. I am just telling it to set it as is. Why is this doing this and what can I do to stop it. I was just attempting to set AU = AU in these conditions.

SELECT * from msdn.MSDN_Import1
UPDATE msdn.MSDN_Import1 SET MSDN.MSDN_Import1.AU =
--CASE WHEN 1=1                                                                       -- when I use this line, AU is untouched and stays AU = '0001234'
CASE WHEN NullIf(MSDN.MSDN_Import1.LOBCC,'') IS NULL            -- when I use this line, AU='0001234' becomes AU = '

Need help with an Update statement, trying to update a 'bit' type column.


I have a function that checks for a username and if it finds the username it will allow the user to update the user, there are 3 main columns: Name,Age,isFemale.  I have the enduser supply the information and then I try to update the record but I get a sql error for "not a valid" column.

console app:

 Console.WriteLine("Please enter your fullname");
                        string spName = Console.ReadLine();
                        Console.WriteLine("Please enter you age, only use digits");
                         int spAge = int.Parse(Console.ReadLine());
                        Console.WriteLine("Are you a female?, 1 for Yes - 0 for No");
                        int spIsFemale = int.Parse(Console.ReadLine());
                        Person sp = new Person();
                        sp.name = spName;
                        sp.age = spAge;
                        if (spIsFemale == 1)
                            sp.Gender = Gender.Female;
                            sp.Gender = Gender.Male;


I pass the spIsFemale by using an enum, here is the Person Class with function.

 public enum Gender { Female, Male };

class Pe

Trouble with an update statement


I'm 100% sure this is going to be something really simple but I'm using a dataset to update a table which works fine until I ask it to update using data from a textbox.

So this won't work:

Dim updateCMS As New AdminCMSTableAdapters.AT_CMSTableAdapter
updateCMS.Update("Home", "Home", txtHome.Text, 1)

But this will:

Dim updateCMS As New AdminCMSTableAdapters.AT_CMSTableAdapter
updateCMS.Update("Home", "Home", "Test", 1)

Any ideas because its driving me insane?!

Thanks in advance.

Primary key violation on update statement for sqlserver2005 in asp.net


Violation of PRIMARY KEY constraint 'PK_StudentInf'. Cannot insert duplicate key in object 'dbo.StudentInf'.
The statement has been terminated.

the above message is appearing when i am upadating the record.

I am doing project  using asp.net 3.5 c#.net and sqlserver 2005,

i am executing sp for update using sqlhelper block, when i am updating record the above error occuring rarely(not for all records), if i update one record if get an error above second time it is updating nicely,

i am not able to finding the problem when and where it is occuring.

one thing here i am upadating primary column also like hallticket NO.

is there any problem if update primary column .

please tell me the solution it's urgent, and also it is on LIVE.

please give me solution as early as possible.

Writing openquery with select statement for Informix DB which to set dirty read



I currently have an openquery statement that gets data from an informix database.  We are experiencing issues with not getting all of the data.  We would like to add a set isolation to dirty read statement.  Just can't seem to get the Informix statement to work with the T-SQL openquery statement:

Original statement:



loc, container, [order], po, parcelcarr,

Using EXEC/EXECUTE with an UPDATE T-SQL statement - not firing as expected


I'm working on EXECUTING a simple UPDATE statement for a configuration table.  The table contains one row of configuration data in multiple columns with different data types.  In order to simplify adding new columns to different configuration tables, I'm making a get and set procedure that determines where the setting is stored and sets it in the applicable table.  Determining the applicable table is easy, but setting the value is not working as expected.  The SQL that is generated below executes perfectly in SSMS, but when running the following SQL, EXEC does not execute the generated SQL as expected.

    SampleName VARCHAR(50),
    SampleNumber INT,
    SampleDate DATETIME


    @SQL VARCHAR(1000),
    @ConfigColumnName VARCHAR(50),
    @ConfigValue VARCHAR(7950),
    @ReturnValue INT

SET @ConfigColumnName = 'SampleName'
SET @ConfigValue = 'Test'

SET @ConfigValue = REPLACE(@ConfigValue, '''', '''''') --change single quotes to pairs of single quotes in value
SET @SQL = 'UPDATE #ConfigTest SET [' + @ConfigColumnName + '] = ''' +

Record Count on MERGE Statement on Insert,Update,Delete


HI All,

How can I get the numbers of records affected in the Merge statement, INSERT,UPDATE,DELETE  separately and store it in a variable so I can get it in the application side? 



khrizz tell
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend