November 7, 2019 at 4:19 pm
Hi,
We have 2 dbs DB A and DB B on two servers AA and BB respectively linked to each other. Stored Procedure(stp) Stp_A in DB A calls stp_B in DB B fetches data from DB A and stores it in temperory table. we have used dynamic sql and used exec sp_executesql command. the sample code is such
Select @Sql = N' Insert into #TmpB
( column1, column2 )
Select column1, column2 from [' + @ServerName +'].['+@DBA +'].dbo.tbl_A
'
Exec sp_executesql @Sql
When our front end application on server AA executes the Stp_A in DB A which in turn call the stp_B in DB B, the above sp_executesql takes long time to execute but does not return any output and also the rest of the stp_B does not get executed. However on executing these stps from T-SQL it works fine.
What should be the case here. We have even set xact_abort on arith_abort on but still facing the issue.
Kindly help
Regards,
Saumik
November 7, 2019 at 8:32 pm
I would use Profiler to see what is going on. Run it on both machines, and make sure that you include the events Error:Exception and Error:UserMessage. Also include SP:StmtStarting and SP:StmtCompleted. You would need to figure out how to filter the traces, so that you get the correct level of information. Best if you can repro the problem in a test system, since running Profiler and capturing events on statement level can cause performance issues if you do it in production.
By the way, a better way to do the above is do
SELECT @sql = 'SELECT colunm1, column2 FROM dbo.tbl'
SELECT @sp_executesql = quotename(@server) + '.' + quotename(@DBA) + '.sys.sp_executesql'
INSERT #tmp(col1, col2)
EXEC @sp_executesql @sql
This will not resolve the issue, but it is a cleaner way to do it.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
November 8, 2019 at 7:20 am
Hi Erland,
We have used the profiler and included SP:StmtStarting and SP:StmtCompleted events but today will check with the error ones. However i am still looking out ways to debug the this issue.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply