On SQL Server 2000, I have a SP (sp_CloseBusinessAcount) that calls another
SP (sp_BuildPayments) which has among several lines of code one specific lin
e
that cancel the process with no error message. The line is a dynamic SQL tha
t
follows below:
execute ('update [PRM].[dbo].[' + @.TableName + '] ' +
' set ' + @.Account_Field_Name + ' = ' + @.Account_Value +
' from [PRM].[dbo].[' + @.TableName + ']' +
' where datepart(month, Event_Date) = ' + @.Event_Month +
' and datepart(year, Event_Date) = ' + @.Event_Year )
I've tried a lot of things with no sucess to find out the problem. Acctually
the code stop running in this line but @.@.error is 0. Does anyone know how to
solve this?
Thanks in advance.
Cheers.
JorgeJorge Luis Ribeiro wrote:
> On SQL Server 2000, I have a SP (sp_CloseBusinessAcount) that calls
> another SP (sp_BuildPayments) which has among several lines of code
> one specific line that cancel the process with no error message. The
> line is a dynamic SQL that follows below:
> execute ('update [PRM].[dbo].[' + @.TableName + '] ' +
> ' set ' + @.Account_Field_Name + ' = ' +
> @.Account_Value + ' from [PRM].[dbo].[' + @.TableName +
> ']' + ' where datepart(month, Event_Date) = ' +
> @.Event_Month + ' and datepart(year, Event_Date) = ' +
> @.Event_Year )
> I've tried a lot of things with no sucess to find out the problem.
> Acctually the code stop running in this line but @.@.error is 0. Does
> anyone know how to solve this?
> Thanks in advance.
> Cheers.
> Jorge
Do the users have UPDATE rights on the underlying table? The update is
going to require a table scan every time because you do not have and
SARGable clauses in the query. It's possible you are running into a
locking/blocking issue. You should be able to see the error value
outside the EXEC, unless there is a trigger on the table which is
resetting the value. Do you have a trigger on the table?
David Gugick
Imceda Software
www.imceda.com|||Hi
Print ('update [PRM].[dbo].[' + @.TableName + '] ' +
' set ' + @.Account_Field_Name + ' = ' + @.Account_Value +
' from [PRM].[dbo].[' + @.TableName + ']' +
' where datepart(month, Event_Date) = ' + @.Event_Month +
' and datepart(year, Event_Date) = ' + @.Event_Year )
Run the output in QA and see if it does the job.
"Jorge Luis Ribeiro" <Jorge Luis Ribeiro@.discussions.microsoft.com> wrote in
message news:B259EFD8-FECF-4652-844B-1AF42FE3F697@.microsoft.com...
> On SQL Server 2000, I have a SP (sp_CloseBusinessAcount) that calls
another
> SP (sp_BuildPayments) which has among several lines of code one specific
line
> that cancel the process with no error message. The line is a dynamic SQL
that
> follows below:
> execute ('update [PRM].[dbo].[' + @.TableName + '] ' +
> ' set ' + @.Account_Field_Name + ' = ' + @.Account_Value +
> ' from [PRM].[dbo].[' + @.TableName + ']' +
> ' where datepart(month, Event_Date) = ' + @.Event_Month +
> ' and datepart(year, Event_Date) = ' + @.Event_Year )
> I've tried a lot of things with no sucess to find out the problem.
Acctually
> the code stop running in this line but @.@.error is 0. Does anyone know how
to
> solve this?
> Thanks in advance.
> Cheers.
> Jorge|||Actually there is a trigger that calls the sp_CloseBusinessAccount that call
s
the other one.
I've tried to check the @.@.error in both procedures with no sucess.
As you mentioned the lock issue, this table @.TableName is a table that has
been created by the same procedure I intend to update.
"David Gugick" wrote:
> Jorge Luis Ribeiro wrote:
> Do the users have UPDATE rights on the underlying table? The update is
> going to require a table scan every time because you do not have and
> SARGable clauses in the query. It's possible you are running into a
> locking/blocking issue. You should be able to see the error value
> outside the EXEC, unless there is a trigger on the table which is
> resetting the value. Do you have a trigger on the table?
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment