Hi All
I written a Server Routine to execute a SQL Stored procedure and it is working fine, but when i execute the same routine using Routine Activity, getting problem.
Herewith the Routine code:
SQLSTMT = 'sqlcmd -S' :SERVER:' -U ':UNAME:' -P ':PWD:' -d R_Ctrl' : ' -Q ':SQLFILE:' -h -1 -m 1 -W'
call DSExecute ("NT",SQLSTMT,out,SystemReturnCode)
Ans=Convert(@FM,"",out)
Return(Ans)
When i try to execute that routine using Routine Activity, I am getting problem.
DS Log output:
Test..JobControl (@GetBID): Routine DSU.BID did not finish OK, return code = 'Sqlcmd: 'NOCOUNT ON DECLARE @BID INT EXECUTE dbo.Start_ Test , @BID OUTPUT SELECT @BID': Unexpected argument. Enter '-?' for help.'
Parameter value i passed in the Routine Activity:
"DECLARE @BID INT EXECUTE dbo.Start_Test, @BID OUTPUT SELECT @BID"
Questions:
1. When i execute the routine using Routine Activity why "DECLARE" statement is missing (Refer DS Log output) ?
2. I noticed single quote is there in front of Sqlcmd. But when i run routine directly, it is not.
3. Is it the right way of executing the SQL Server Stored procedure Or Any other better way to do this?
Please have a look and let me know if i am doing any mistake in this.
It will be great if any one can help me in this.
Thanks in advance,
DS User
Execute a Server Routine to connect SQL Server Database
Moderators: chulett, rschirm, roy
Re: Execute a Server Routine to connect SQL Server Database
Hi All
Issue resolved. The way how i passed the value into the sqlcmd is the problem and found it.
The below code is working fine.
'sqlcmd -S ServerName -U UserName -P Password -d DBName -Q "SET NOCOUNT ON DECLARE @ID INT EXECUTE dbo.ProcedureName InputValue, @ID OUTPUT SELECT @ID"'
In the above sqlcmd ID is the return value.
Thanks to all for your efforts and time.
DS User
Issue resolved. The way how i passed the value into the sqlcmd is the problem and found it.
The below code is working fine.
'sqlcmd -S ServerName -U UserName -P Password -d DBName -Q "SET NOCOUNT ON DECLARE @ID INT EXECUTE dbo.ProcedureName InputValue, @ID OUTPUT SELECT @ID"'
In the above sqlcmd ID is the return value.
Thanks to all for your efforts and time.
DS User