August 12, 2018 at 9:04 pm
Hello all,
Due to a client's request, I have built a rules engine that is dynamic, based on records in various tables. That being said -- I have a lot of dynamic SQL, stored procedures calling other stored procedures, etc.
A piece of my current process goes like this.
SELECT COUNT(*) AS BeforeCount FROM Database B -- Count is 0.
Call proc that does an insert from Database A to Database B, using dynamic sql, and sp_executesql. Note: my sp_executesql returns 0, so no error.
SELECT COUNT(*) AS AfterCount FROM Database B -- Count is 0.
What should happen, is that the insert should run, insert 28 records, and the count after should be 28. When I call this in a stored procedure, it doesn't work. When I debug it, running through step-by-step, it works. It's not a timing thing, and I removed all references to transactions. Does anyone have any ideas? Please let me know if you need more information.
Thank you!
August 12, 2018 at 9:38 pm
Also -- when I take the string of dynamic sql that is run by sp_executesql and run it manually in another query window, or even if I just call the proc (called spRunSQLLoad) itself, it works. I'm not sure if it's worth mentioning, but in the script that calls my inner proc, the first thing it does is execute an SSIS package from a catalog. This is bringing data in from an outside source, and spRunSQLLoad is supposed to be bringing it in from staging into a master table.
August 13, 2018 at 12:41 am
Can you post the actual code please?
😎
August 15, 2018 at 7:16 am
I'm sorry to waste your time on that question-- the dynamic SQL worked great. As I was cutting down the code so I could post in on a forum, I noticed that the error went away. Once I put in a wait after the SSIS package, everything ran as expected. Is there any way I can make sure this wait isn't an arbitrary amount of time, but I'm actually waiting for the SSIS package to be done running? A 5-second wait isn't long enough, but a 10-second wait is... this time. Who knows how long of a wait for next
Here is my code for executing the SSIS package:
DECLARE @execution_id BIGINT;
EXEC SSISDB.catalog.create_execution @package_name = @SSISPackageName
, @execution_id = @execution_id OUTPUT
, @folder_name = @SSISFolderName
, @project_name = @SSISProjectName
, @use32bitruntime = False
, @reference_id = NULL;
EXEC SSISDB.catalog.set_execution_parameter_value @execution_id
, @object_type = 50
, @parameter_name = N'LOGGING_LEVEL'
, @parameter_value = 1;
EXEC SSISDB.catalog.start_execution @execution_id;
August 15, 2018 at 9:01 am
This is calling an SSIS package. You'll need to check connection strings to make sure you are actually using the databases you think you are. Also, when you debug, are you doing it through SSIS or SSMS? Should debug in SSIS with same connections and stop just before it calls your dynamic proc to see what the variables are before you call the proc. You can also catch the execute statement to see what is being called and passed.
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 15, 2018 at 10:55 am
If you create a SQL Server Agent Job and make the first step executing the SSIS Package, and the execute sp the second step ths data will be loaded to Staging before your procedure executes and you wait will no be required.
August 15, 2018 at 11:46 am
I ended up answering my own question, but -- for the benefit of anyone who might run across this thread -- add this parameter before the execute call, and this takes care of your wait, making the process synchronous.
EXEC SSISDB.catalog.set_execution_parameter_value @execution_id
, @object_type = 50
, @parameter_name = N'SYNCHRONIZED'
, @parameter_value = 1;
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply