Showing posts with label anothersp. Show all posts
Showing posts with label anothersp. Show all posts

Sunday, February 19, 2012

Dynamic SQL stops responding with no error message

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
>