Execute a Server Routine to connect SQL Server Database
Posted: Thu Mar 17, 2011 9:35 pm
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
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