I am trying to use SQLCMD via command line to call 2 stored procedures which one is dependent on another. Both stored procedures have parameters. I can run 1 stored procedure with parameters, no problem.
It's trying to combine the 2 together because stored procedure User.pSessionCreate executes a unique SessionGuid ID needed to run the 2nd stored procedure Custom.pTotport_AssetClass
as one of the parameters. In addition, the data results is output to a .txt file. Thanks in advance for the help!
Here is an example of the SQLCMD I put together but I get the error "Cannot use the OUTPUT option when passing a constant to a stored procedure" in my output .txt file. You'll see below the @SessionGuid created from the 1st stored procedure is needed in the 2nd stored procedure parameter.
sqlcmd -S serverName" -d dbName -E -h -1 -Q "set nocount on;declare @SessionGuid nvarchar(48) exec User.pSessionCreate 'login', 'password', '@SessionGuid';exec Custom.pTotport_AssetClass '@SessionGuid', 'Group', '2015-3-31', 'PC', 'IN'" -o C:\temp\test.txt
Here is a actual syntax when running out of SSMS directly and works just fine. Just trying to accomplish this via SQLCMD for another process.
declare @SessionGuid nvarchar(48)
exec User.pSessionCreate @loginname = 'login', @password='password',@SessionGuid = @SessionGuid out
@SessionGuid = @SessionGuid,
@Portfolios = 'Group',
@ThruDate = '2015-3-31',
@ReportingCode = 'PC',
@FileType = 'AA'