I will see what the profiler trace turns up. In the meantime, how do I
run the statement as command text? Do you have a simple example that you
can share?
With ADO, instead of :
command.CommandText = "EXEC dbo.sp_setapprole ?, ?"
try
command.CommandText = "EXEC dbo.sp_setapprole 'MyRole', 'MyRolePassword'"
Similarly, you ought to be able to use a stored procedure command type:
command.CommandType = adCmdStoredProcedure
command.CommandText = "dbo.sp_setapprole"
Set roleNameParameter = command.CreateParameter("@rolename")
roleNameParameter.Type = adVarWChar
roleNameParameter.Direction = adParamInput
roleNameParameter.Size = 8
roleNameParameter.Value = "RoleName"
command.Parameters.Append roleNameParameter
Set rolePasswordParameter = command.CreateParameter("@password")
rolePasswordParameter.Type = adVarWChar
rolePasswordParameter.Direction = adParamInput
rolePasswordParameter.Size = 12
rolePasswordParameter.Value = "RolePassword"
command.Parameters.Append rolePasswordParameter
Both of the above methods should avoid the ad-hoc parameterized statement.
Of course, you will need to adapt for the language and API you are using.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"nambia05" <na******@yahooo.comwrote in message
news:g2**********@aioe.org...
Dan Guzman wrote:
>>
My guess is the the sp_setapprole is being wrapped by the API (e.g.
sp_prepare, sp_executesql). You can run a Profiler trace to see what's
going on. Perhaps a solution is to execute the statement as command text
instead of a prepared statement.
I will see what the profiler trace turns up. In the meantime, how do I
run the statement as command text? Do you have a simple example that you
can share?
Thank you,
N.
