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


Top 5 Contributors of the Month
Easy Web
Imran Ghani
Post New Web Links

CLR Timeout when running an update statement

Posted By:      Posted Date: October 01, 2010    Points: 0   Category :.NET Framework
 

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")

Try
                       ssql = "select fields from my table" --this works perrfect!!!
                        conn.Open()

                   &nbs


View Complete Post


More Related Resource Links

Running a single SQL update query on multiple foxpro databases

  
Hi, Env : SSIS 2008 My requirement is to run a update SQL statement on multiple foxpro databases. The fox database paths are available in a SQL server table.  I've created a for each loop control and assigned the database path to a variable named dbpath. Within the loop, I added an "execute sql task" control. Now what properties are to be set to get the requirement done. Appreciate any help. Regards. BH  Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful. BH

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.

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 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 

SET UPDT_OPR_ID = (select dm.CONFIRM_BY

		FROM geprs_price..T_ITEM_PRC_CHG gep
		JOIN SMS_DATAMART.dbo.T_PRC dm 
		ON dm.PRC_EFF_DT = gep.PRC_EFF_DT
		AND gep.SG_CNTRC_BID_PRC = dm.PRC
		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

		WHERE gep.sg_CNTRC_BID_PRC >0
		AND gep.UPDT_OPR_ID <>dm.CONFIRM_BY
		AND dm.PRC_TYP_ID in (39)
		AND con.DISPLAYNAME IS NOT NULL)

		



--COMMIT

Use XML DML with a select or update statement.

  
Given an SQL XML table column with contents like this:

<fld>
  <state>WA</state>
  <taxcert>A18 3865 10</taxcert>
  <effective>1/1/2010</effective>
  <certexp_yn>Y</certexp_yn>
  <expires>12/31/2010</expires>
  <singleuse_yn>N</singleuse_yn>
  <exempt_yn>N</exempt_yn>
  <exempt_reason />
</fld>

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

SELECT TOP 100
        [XMLData].modify('insert <entityusecode_id />
              after (/fld)[1]
             ')
FROM    [SCANREF] AS S

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?

TIA,

Kevin


OPENQUERY Update statement errors

  

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
(
    @STATUS_TO_SET     VARCHAR(50),
    @REFERENCE     VARCHAR(15),
    @PDF_FILE    IMAGE = NULL,
    @UPDATE_COUNT     INT OUTPUT
)
AS  

SET NOCOUNT ON


DECLARE @STATUS_TIMESTAMP DATETIME
SET @UPDATE_COUNT = 0

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
    INNER JOIN TBL_ACTV A
    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
		FROM TBL_CNTC C 
		INNER JOIN TBL_ACTV A 
		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)

but

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.

BEGIN TRANSACTION
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;
                        else
                            sp.Gender = Gender.Male;

                        Console.WriteLine(sp.savePerson());


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

 public enum Gender { Female, Male };

class Pe

Delete statement running slow

  

Dear Friends

I've got a delete statement which is taking 40 secs to run. There's only a row to delete but it's taking 40 secs . The table has got foreign keys which references other tables. I have checked indexes exist on these foreign key columns.

I have already updated statistics with fullscan on the this table.

DELETE FROM konten WITH(ROWLOCK) WHERE KONTONR = 123456

Any ideas

Many thanks.

 


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.



Error running update-spprofilephotostore

  

Hello,

I am getting the following error on every user's picture when i try to run the profile photo store update while migrating from 2007 to 2010 using the new farm and attach db method of upgrading. 

Any ideas of how to go about troubleshooting?  It appears that it is going back to our 2007 environment to grab the mysite photo and trying to move it across into the 2010 farm into the new my site host. Is there some sort of port or permission that needs to be granted between the two farms for this to work or other step im missing?

 

 

Update-SPProfilePhotoStore : Error processing the photo URL http://<mysiteurl>

/personal/jon%20j%20chard/Shared%20Pictures/Profile%20Pictures/DSC00063

.JPG for user <domain>\Jon Doe: System.Net.WebException: The underlying co

nnection was closed: An unexpected error occurred on a receive. ---> System.IO.

IOException: Unable to read data from the transport connection: An existing con

nection was forcibly closed by the remote host. ---> System.Net.Sockets.SocketE

xception: An existing connection was forcibly closed by the remote host

   at System.Net.Sockets.NetworkStrea

Error running update-spprofilephotostore

  

Hello,

I am getting the following error on every user's picture when i try to run the profile photo store update while migrating from 2007 to 2010 using the new farm and attach db method of upgrading. 

Any ideas of how to go about troubleshooting?  It appears that it is going back to our 2007 environment to grab the mysite photo and trying to move it across into the 2010 farm into the new my site host. Is there some sort of port or permission that needs to be granted between the two farms for this to work or other step im missing?

 

 

Update-SPProfilePhotoStore : Error processing the photo URL http://<mysiteurl>

/personal/jon%20j%20chard/Shared%20Pictures/Profile%20Pictures/DSC00063

.JPG for user <domain>\Jon Doe: System.Net.WebException: The underlying co

nnection was closed: An unexpected error occurred on a receive. ---> System.IO.

IOException: Unable to read data from the transport connection: An existing con

nection was forcibly closed by the remote host. ---> System.Net.Sockets.SocketE

xception: An existing connection was forcibly closed by the remote host

   at System.Net.Sockets.NetworkStrea

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.


CREATE TABLE #ConfigTest
(
    SampleName VARCHAR(50),
    SampleNumber INT,
    SampleDate DATETIME
)


DECLARE

    @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 + '] = ''' +

Categories: 
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