Execute a Server Routine to connect SQL Server Database

[SURA]

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

[SURA]

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