Error on Update, but not when using cursor

I've implemented a UDF in SQL Server 2005 written in C#. The function with its assembly has been registered ok with SQL Server and works fine. It accepts three short strings (nvarchar of lengths 5, 35, and 35) and returns a SQL formatted string (SqlString).

When I run the function to test it it works just fine, and the same is true if I run the function inside a cursor to update a field in a table. But when I do a simple update it crashes. I've so far received two different errors: first one error saying a string could not be converted into an integer (but the error does not occur when I enter the same input values manually via a test Windows form, or through the new Query Analyzer as a single query - or using it inside a cursor). Then one error saying a string was too short (I couldn't use substring(X, Y) because the string, I was told, was too short - it wasn't).

The problem thus cannot be with the function since it works just fine if I do like this:

UPDATE myTable SET CodeField = dbo.fnMyFunction(Field1, Field2, Field3) WHERE PersonId = 10000001

And it works fine while doing the same thing inside a cursor (for instance working with the first 10, 100 or 1000 records).

But when I do this it crashes:

UPDATE myTable SET CodeField = dbo.fnMyFunction(Field1, Field2, Field3)

For your information the table has about 1.5M records (for testing, it contain more data when on the production server) and my aim is to update the CodeField column as quickly as possible. The CodeField is a 12-character string that is based on a rather complex algorithm including the Field1, Field2 and Field3 strings. I'm using C# because it manages strings much better than SQL Server - and it is so much easier coding this stuff.

Anyhow, I've had this kind of problem before with SQL Servers 2000 and 7 (maybe even 6.5) and it seems the problem occurs when I let SQL Server go about its business at its own pace. But when I do something to control that it really takes one record at a time (through using a cursor or executing the query with a WHERE clause like the one above) it works splendidly.

The problem here is that a cursor is way too slow, and there really shouldn't be a problem with a simple UPDATE command, should it? After all, everything works just fine except when I let SQL Server do what it does best (i.e. update the field at its own speed, whatever that is).

Any ideas? This is very frustrating since it is impossible to try and find the error - it isn't there when testing! And it is frustrating since I remember having had the same kind of problem (but every time with different errors arising) before without finding a solution (except for slowing everything down - not an option here).

Is there a certain tweak I can do to make things work out, or should I code things differently?

Thanks!

Hi Bylund

Your update statement will fail because it does not know which records you are trying to update.

Am I correct in thinking you want to update ALL records in the DB?

It seems to me, despite your function, you still need to provide some sort of where clause.

Let me know so I can help further.

Barry Andrew (Pace)sql

Error on Update, but not when using cursor

I've implemented a UDF in SQL Server 2005 written in C#. The function with its assembly has been registered ok with SQL Server and works fine. It accepts three short strings (nvarchar of lengths 5, 35, and 35) and returns a SQL formatted string (SqlString).

When I run the function to test it it works just fine, and the same is true if I run the function inside a cursor to update a field in a table. But when I do a simple update it crashes. I've so far received two different errors: first one error saying a string could not be converted into an integer (but the error does not occur when I enter the same input values manually via a test Windows form, or through the new Query Analyzer as a single query - or using it inside a cursor). Then one error saying a string was too short (I couldn't use substring(X, Y) because the string, I was told, was too short - it wasn't).

The problem thus cannot be with the function since it works just fine if I do like this:

UPDATE myTable SET CodeField = dbo.fnMyFunction(Field1, Field2, Field3) WHERE PersonId = 10000001

And it works fine while doing the same thing inside a cursor (for instance working with the first 10, 100 or 1000 records).

But when I do this it crashes:

UPDATE myTable SET CodeField = dbo.fnMyFunction(Field1, Field2, Field3)

For your information the table has about 1.5M records (for testing, it contain more data when on the production server) and my aim is to update the CodeField column as quickly as possible. The CodeField is a 12-character string that is based on a rather complex algorithm including the Field1, Field2 and Field3 strings. I'm using C# because it manages strings much better than SQL Server - and it is so much easier coding this stuff.

Anyhow, I've had this kind of problem before with SQL Servers 2000 and 7 (maybe even 6.5) and it seems the problem occurs when I let SQL Server go about its business at its own pace. But when I do something to control that it really takes one record at a time (through using a cursor or executing the query with a WHERE clause like the one above) it works splendidly.

The problem here is that a cursor is way too slow, and there really shouldn't be a problem with a simple UPDATE command, should it? After all, everything works just fine except when I let SQL Server do what it does best (i.e. update the field at its own speed, whatever that is).

Any ideas? This is very frustrating since it is impossible to try and find the error - it isn't there when testing! And it is frustrating since I remember having had the same kind of problem (but every time with different errors arising) before without finding a solution (except for slowing everything down - not an option here).

Is there a certain tweak I can do to make things work out, or should I code things differently?

Thanks!

Hi Bylund

Your update statement will fail because it does not know which records you are trying to update.

Am I correct in thinking you want to update ALL records in the DB?

It seems to me, despite your function, you still need to provide some sort of where clause.

Let me know so I can help further.

Barry Andrew (Pace)

Error on SQL .mdf update and edit - nText?

Hello anyone know how to fix this error?

The data types ntext and nvarchar are incompatible in the equal to operator.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: The data types ntext and nvarchar are incompatible in the equal to operator.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Thanks , John

An nText field can not be used in optimisitic updates. Your update statement that was generated looks something like:

UPDATE SomeTable
SET {Stuff}
WHERE nTextField=old_nTextField

And old_nTextField is defined as a nvarchar. You can't really define it as nText as nText is just a very long nvarchar field, and even if you COULD get it defined as a nText, it would still be invalid, because you can't use nText= anything, even another nText in a WHERE clause.

|||What should it be? I am now using a text , but it still does not work.|||

TEXT/NTEXT/IMAGE data in SQL Server are considered as BLOB (Binary Large Object) data and can not be used with "=" or in sorting. You may need "Text and Image Functions" to manipulate these kinds of data:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_0gs3.asp

Or you can convert the TEXT data (less than 8000 chars) into VARCHAR data to use it in comparation, for example:

select * from test_txt where convert(varchar(8000),t)='The index position of the value parameter if that string is found, or -1 if it is not. If value is Empty, the return value is 0.'

Or use LIKE operator:

select * from test_txt where t like 'The index position of the value parameter if that string is found, or -1 if it is not. If value is Empty, the return value is 0.'

Error on Server Management Report Samples

I came up with an error when I ran the dtsx pacakge which came up with
the Server Management Report Samples to update the RSExecutionLog
database.
I tried to create an SSIS package project and ran it from there. Its
giving me an error at "Update Parameters" Control Flow (on Derived
Column data flow). Errors are
"[Derived Column [979]] Error: The "component "Derived Column" (979)"
failed because truncation occurred, and the truncation row disposition
on "output column "ParametersStr" (999)" specifies failure on
truncation. A truncation error occurred on the specified object of the
specified component. "
"Task Update Parameters failed"
Does this error happens to you guys?
Is this because the RSExecutionLog table field is not lengthy enough to
hold the reportserver data?
Thanks,
Promod.I found the issue with SSIS package. The problem is with the lenght of
the field. It was an NText unicode data which had a length more than
65000. I re-written the Read Parameter query like
SELECT ExecutionLogID, Parameters
FROM ExecutionLogs WITH (NOLOCK)
WHERE Parameters IS NOT NULL AND
Datalength(Parameters) > 0
--added my me
AND Datalength(Parameters) < 4000
If there is a better solution let me know. I think this issue will
happen only when we have choose cube as the datasource.
Thanks,
Promod.

Error on Create Trigger

I have the following

CREATE TRIGGER dbo.tgrCacheCustomers
ON dbo.Customers
FOR INSERT, UPDATE, DELETE
AS
EXEC sp_makewebtask 'C:\DependencyFile.txt','SELECT top 1 CustomerId FROM customers'

and I get the following error that I dont understand:

Error 21037: [SQL-DMO] The name specified in the Text property's 'CREATE ...' statement must match the Name property, and must be followed by valid TSQL statements.

Any ideas someone?Before anyone can help, please kindly explain what you want to accomplish in:
EXEC sp_makewebtask 'C:\DependencyFile.txt','SELECT top 1 CustomerId FROM customers'

Thanks.|||

This error occurs sometimes when you renmae the trigger manually by using F2 Function key & rename. if the name doesent matches with the name specified in the trigger.

Regards
Salman Zafar

Error on Clustered Index and NText Field

Hi i have a problem whenever i try to update a table in which i have a clustered index and a ntext field i have this error through a ASP page

Microsoft OLE DB Provider for SQL Server (0x80040E14)
The query processor could not produce a query plan from the optimizer because a query cannot update a text, ntext, or image column and a clustering key at the same time.

ThanksMay be you are trying to update more then one recond in query.

BOL:

If an update query could alter more than one row while updating both the clustering key and one or more text, image, or Unicode columns, the update operation fails and SQL Server returns an error message.

Modifying a text, ntext, or image column with UPDATE initializes the column, assigns a valid text pointer to it, and allocates at least one data page unless updating the column with NULL.

Note The UPDATE statement is logged. If you are replacing or modifying large blocks of text, ntext, or image data, use the WRITETEXT or UPDATETEXT statement instead of the UPDATE statement. The WRITETEXT and UPDATETEXT statements (by default) are not logged.|||i only have this error when i try to update the ntext field and the data is > than 10 KB...
Any idea and how could i use the updatetext in a asp SCRIPT?
Thanks again

Error Message through access

HI I have access on the front end and SQL server as the engine, and I have a stored procdure in a macro called update. Now the problem with macro is when I execute it on to someone else computer I get an error message but when I execute it on my computer no error message and no macro Halt?? Anyone know what the deal is??I'd suggest that you check to see what the error message says. That could help a lot.

-PatP|||heres the message I am getting|||I'm 99% sure that the problem is object ownership. My guess would be that if you run:EXECUTE sp_help InsertTerms that the "owner" in the first line will not be dbo. To make it dbo so that the object can be seen by all users, you would use:EXECUTE sp_changeobjectowner 'InsertTerms', 'dbo'-PatP|||Open stored procedure...when did they add that?

And where do the result sets go?

What if a sproc returns more than 1.

I'll have to monkey around with this....|||All my problems today are solved I am so happy woohoooo...Pat and Bret I adore you...THANK YOU AGAIN

and more important I learned something, I do mean I learned something ahhh its all starting to come together like pieces to a puzzle

:):):):)

error message Only members of sysadmin role are allowed to update .....

Question to those who may have had this same error- it seems that I am not able to delete some of the reports that I have created. This just started happening recently and according to our system admin nothing has changed as far as permissions are concernced. We installed SP2 the other day and I was wondering if this could have anything to do with the error message below

by the way I am a member of the sysadmin group

thanks in advance

km

System.Web.Services.Protocols.SoapException: Server was unable to process request. > System.Data.SqlClient.SqlException: Only members of sysadmin role are allowed to update or delete jobs owned by a different login. Only members of sysadmin role are allowed to update or delete jobs owned by a different login. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.DBInterface.DeleteObject(String objectName) at Microsoft.ReportingServices.Library.RSService._DeleteItem(String item) at Microsoft.ReportingServices.Library.RSService.ExecuteBatch(Guid batchId) at Microsoft.ReportingServices.WebServer.ReportingService2005.ExecuteBatch() End of inner exception stack trace

It looks like you are getting this error because the report has a schedule associated with it and most likely you recently changed the way you connect to the Report Server database. To fix this you will need to set the owner for all of the Report Server SQL Agent jobs to be the user that RS is using to connect to it's database.