April 3, 2006 at 5:04 pm
I am using Sql Server Express, and I can create the stored procedure and save them to a file, but my C# code can't seem to locate them. Are they not automatically "added" to the db when they are created? How is this done?
April 3, 2006 at 9:13 pm
The following answer assumes that this is a T-SQL stored procedure, not a CLR stored procedure:
The stored proc is saved in the database. So, you create a stored procedure by running code in the query window like this:
create proc usp_MyProc as
--some code that does something
return 0
The procedure is stored in a particular database. But the catch is that execute permission must be granted to the account that your C# program is using. Let's say that your account is "sam"
grant execute on usp_MyProc to sam
1) Make sure that the proc was actually created, not saved in a file.
2) Make sure that it was created in the correct database.
3) Make sure that the user has execute permission.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
April 4, 2006 at 6:36 am
Make sure you are referencing the owner of the stored procedure when you call it.
Ex.: SqlDataAdapter.SelectCommand.CommandText = "dbo.my_new_procedure"
SqlDataAdapter.SelectCommand.CommandText = "my_users_enterprise_manager_DB_Login_name.my_stored_procedure"
April 4, 2006 at 8:52 am
Good point.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
April 4, 2006 at 3:33 pm
This may also be a remedial point of view (if already known?).
I. Using ODBC connection (login, password, etc.) matching credentials.
II. Many methods exist for running stored procedures:
1. ICommand:Execute using DBPARAMBINDINFO structures. ICK!
2. ICommandWithParameters::SetParameterInfo. ICK!
3. Using Execute(), which is my personal favorite when receiving only a single item as returned value, such as true or false, or muliple row sets, and executing stored procedures returning a single item or multiple sets.
a. Using method Execute(CALL procedure_name(value1,value2,value3)), supply values when parameter names not known, provided order is correct pattern, with all parameters listed.
AND NOTE: Using (Execute Execute) Method
Set cnn
= Server.CreateObject("ADODB.Connection")
cnn.Open "credentials,etc.,"
Set rst = Server.CreateObject("ADODB.Recordset")
Set rst = cnn.Execute("EXECUTE Procedure_Name @param1='value1', @param2='value2', @param3='value3', @param4='value4';")
WHOA!
Coach James
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply