sp_executesql not working

  • saum70

    SSC Eights!

    Points: 808

    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

     

  • Erland Sommarskog

    SSC-Insane

    Points: 23815

    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]

  • saum70

    SSC Eights!

    Points: 808

    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 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply