May 12, 2014 at 10:53 am
Should I not be able to see the stored procedure somewhere? I'm afraid to close the window that is is showing in. For example, I closed that window, and it asked me if I wanted to save it (similar to when I first tried making queries in SQL). I did save it. Now how do I find it again? I don't see it anywhere.
May 12, 2014 at 11:29 am
When you create a stored procedure with SSMS you:
1. Click on the "New Query" button (or select "New query with the current connection" in the File menu, or press CTRL+N).
2. Type in the code for the S.P. In the Query Editor window. It will begin with "CREATE PROCEDURE ProcedureName"
3. Check the syntax (click on the "Parse" button or select "Parse" in the Query menu, or press CTRL+F5)
4. When everything is OK (i.e. no error returned by the syntax checker) you execute the query (i.e. the CREATE PROCEDURE instruction will be executed and a new SP will be created in the active database). There is now a new S.P. named ProcedureName in the current database. You'll need to refresh the list of S.P. in the Object Explorer to be able to see it, though.
5. If you want to keep a record of the SQL expression of the S.P., you can save the text that's in the Query Editor window by clicking on the "Save" button, selecting one of the "Save" options in the File menu or press CTRL+S. This is not mandatory.
6. If you elected not to save the text of the S.P., when you close the Query Editor window, you are asked if you want to save the changes.
7. Whether you save the text of the S.P. or not, after step 4 the S.P. actually exists in the database.
8. From an external application, whatever the programming language or environment development, you can call the S.P. by using an object from a Data Access Library. This library can be DAO (and you use a Querydef object), it can be ADODB (and you can use a Command object), it can be ADODB.Net (for .Net languages), etc. In any case, the data access you use to call the S.P. must be provided with a connection string parameter which specifies the SQL Server name, the database name, etc. and another parameter that specifies the name of the stored procedure you want to call. Depending on the kind of data object in use, the parameters of the S.P. can be passed with the name of the stored procedure or can be loaded in the data object through its Parameters collection.
May 12, 2014 at 12:03 pm
I'm still missing something. What if I need to go back and edit the stored procedure? How do I get back to it?
I had hit hte execute button, and if I do it again, it says that the procedure already exists. I put in some test values, and want to create the full code... Why is this so confusing? How do I access/edit the current stored procedure(s) I create?
May 12, 2014 at 12:12 pm
jdasilva (5/12/2014)
I'm still missing something. What if I need to go back and edit the stored procedure? How do I get back to it?I had hit hte execute button, and if I do it again, it says that the procedure already exists. I put in some test values, and want to create the full code... Why is this so confusing? How do I access/edit the current stored procedure(s) I create?
If you want to edit a stored procedure you need to use ALTER PROCEDURE ... instead of CREATE PROCEDURE...
It is a bit confusing at first but once you work with them a few times it becomes straight forward. What you have to realize is that your code is creating the procedure instead of editing it directly. Think of like an assembly or an application. You use an IDE (SSMS in this case) to write the code and then compile it. This is essentially what you are doing in sql server. You write the procedure code and then compile it and deploy it to the server in one step. Does that help make it a little more clear?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 12, 2014 at 12:48 pm
For editing (changing) a stored procedure, you can:
1. Reload the file you saved when creating it (if you did so), then change the instruction "CREATE PROCEDURE" to "ALTER PROCEDURE".
2. You can also:
- Select the proper database in the Object explorer then
- Expand the "Programmability" tab then
- Expand the "Stored Procedures" tab then
- Select the S.P. in the list and right-click on its name then
- Select "Script Stored Procedure As..." in the contextual menu then
- Select "ALTER to" --> "New Query Editor window" or select "DROP and CREATE to" --> "New Query Editor window".
- If you select "ALTER to", the existing S.P. will be changed when you click the "Execute" button.
- If you select "DROP and CREATE to", the existing S.P. will be deleted and a new SP with the same name will be created when you click the "Execute" button.
May 12, 2014 at 3:16 pm
rf44 (5/12/2014)
For editing (changing) a stored procedure, you can:1. Reload the file you saved when creating it (if you did so), then change the instruction "CREATE PROCEDURE" to "ALTER PROCEDURE".
2. You can also:
- Select the proper database in the Object explorer then
- Expand the "Programmability" tab then
- Expand the "Stored Procedures" tab then
- Select the S.P. in the list and right-click on its name then
- Select "Script Stored Procedure As..." in the contextual menu then
- Select "ALTER to" --> "New Query Editor window" or select "DROP and CREATE to" --> "New Query Editor window".
- If you select "ALTER to", the existing S.P. will be changed when you click the "Execute" button.
- If you select "DROP and CREATE to", the existing S.P. will be deleted and a new SP with the same name will be created when you click the "Execute" button.
Step 2 does work, after a "refresh". I see it now. Thanks.
May 13, 2014 at 12:30 am
You're welcome!
May 14, 2014 at 1:58 pm
Sean Lange (5/6/2014)
jdasilva (5/6/2014)
How does SQL get the value of the variable from the form in access?In the example I am working on, I am polling the data for specific info for each person in an office. Currently, since the query is in the access file, I check he current form that calls up the query. In that form is a text field that holds the current office code. Let's say that it is in [Forms]![aform]![loc]. How do I code my SQL statement on the SQL server to see this value?
Or are you saying YES to my statement about making a table that holds the current office location code? If this is what you are suggesting, what happens when multiple offices try and access this query at the same time?
SQL Server cannot see your form. However, your form is where you will execute the stored procedure. From Access it would be something like this:
cmd.CommandText = "MyStoredProc"
dim parm as New ADODB.Parameter
set parm = cmd.CreateParameter("LocID", adInteger)
cmd.Parameters.Append parm
cmd.Parameters("LocID") = [Forms]![aform]![loc]
Set rs = cmd.Execute()
I would recommend NOT using some sort of table to hold the current location. That is not a good approach when you can just use parameters.
Sean,
As I was saying before, this is code is short of what I need. The connection to the SQL server is not defined anywhere, nor is the object cmd. I have tried putting this in the code for a button:
Private Sub Box42_Click()
Dim parm As New ADODB.Parameter
Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command
cmd.CommandText = "GetThisLocCode"
Set parm = cmd.CreateParameter("pLocCode", adInteger)
cmd.Parameters.Append parm
cmd.Parameters("pLocCode") = Me.ThisLocCode
Set rs = cmd.Execute()
End Sub
I did look around for similar code, and they defined the the vars like this. It does not seem to like the type Parameter or Command... Also, others set up a connection definition, which I assume opens the connection to talk with the SQL server.
May 14, 2014 at 2:21 pm
jdasilva (5/14/2014)
Sean Lange (5/6/2014)
jdasilva (5/6/2014)
How does SQL get the value of the variable from the form in access?In the example I am working on, I am polling the data for specific info for each person in an office. Currently, since the query is in the access file, I check he current form that calls up the query. In that form is a text field that holds the current office code. Let's say that it is in [Forms]![aform]![loc]. How do I code my SQL statement on the SQL server to see this value?
Or are you saying YES to my statement about making a table that holds the current office location code? If this is what you are suggesting, what happens when multiple offices try and access this query at the same time?
SQL Server cannot see your form. However, your form is where you will execute the stored procedure. From Access it would be something like this:
cmd.CommandText = "MyStoredProc"
dim parm as New ADODB.Parameter
set parm = cmd.CreateParameter("LocID", adInteger)
cmd.Parameters.Append parm
cmd.Parameters("LocID") = [Forms]![aform]![loc]
Set rs = cmd.Execute()
I would recommend NOT using some sort of table to hold the current location. That is not a good approach when you can just use parameters.
Sean,
As I was saying before, this is code is short of what I need. The connection to the SQL server is not defined anywhere, nor is the object cmd. I have tried putting this in the code for a button:
Private Sub Box42_Click()Dim parm As New ADODB.Parameter
Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command
cmd.CommandText = "GetThisLocCode"
Set parm = cmd.CreateParameter("pLocCode", adInteger)
cmd.Parameters.Append parm
cmd.Parameters("pLocCode") = Me.ThisLocCode
Set rs = cmd.Execute()
End Sub
I did look around for similar code, and they defined the the vars like this. It does not seem to like the type Parameter or Command... Also, others set up a connection definition, which I assume opens the connection to talk with the SQL server.
Yes you need a connection and you need to set the command connection to that connection.
dim conn = New ADODB.Connection
conn.ConnectionString = "PutYourConnectionStringHere"
con.Open
dim cmd as New ADODB.Command
set cmd.ActiveConnection = conn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "YourProcNameHere"
[Add your parameter lines here]
Does that help?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 15, 2014 at 8:34 am
I guess this now hits on an embarassing note... DAO vs. ADO... I beleive I am using DAO, though I am not 100% sure. Again, no background training in any of this...
coding VBA in Access 2013, how would I know what I am using? My problem is that when I try to use the examples you give, it doesn't seem to like the syntax. I've poked around and the closest I've gotten is this:
Dim db As Database
Dim rs As Recordset
Dim SQLstr As String
Dim apass As String
Set db = DBEngine.Workspaces(0).OpenDatabase _
("", False, False, "driver=SQL Server;server=Ontario;database=CNRFloorPlan;Trusted_Connection=Yes")
apass = Me.ThisLocCode.Value
Debug.Print apass
'Set rs = db.OpenRecordset("dbo.GetThisLocCode", dbOpenSnapshot, apass)
Set rs = db.OpenRecordset("dbo.SQLFloorPlanQuery", dbOpenSnapshot)
Debug.Print rs(0).Value
I commented out the call to the stored procedure, as it gives a Data type conversion error (3421). This code debugs out to show the first item in the SQL view called. There are 2 problems I am having. The error, and this only returns 1 record. (I debuged the record count as well, it came out to 1, but it should be over 100).
May 15, 2014 at 8:50 am
Try this link and see if it helps.
http://msdn.microsoft.com/en-us/library/office/aa831024%28v=office.10%29.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 15, 2014 at 10:52 am
In your sample, you're using DAO because Application.DBEngine.Workspaces(0).Databases(0) represents a DAO object. You can also use CurrentDb which is a shortcut to the same object.
A very good way to be sure of which kind of objects you use consists in declaring them explicitly:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim SQLstr As String
' etc...
May 15, 2014 at 2:34 pm
ok. well lets go back to the original stuff Sean posted. He used 2 delcarations that don't work (user-defined type not defined):
dim parm as ADODB.Parameter
dim cmd as ADOBD.Command
So here is my first issue. These 2 items seem to be key in the code, but if I can't declare them, I can go no further. I have tried with and without New in the declaration.
May 15, 2014 at 2:40 pm
jdasilva (5/15/2014)
ok. well lets go back to the original stuff Sean posted. He used 2 delcarations that don't work (user-defined type not defined):
dim parm as ADODB.Parameterdim cmd as ADOBD.Command
So here is my first issue. These 2 items seem to be key in the code, but if I can't declare them, I can go no further. I have tried with and without New in the declaration.
If you are doing this in Access you may have to add a reference in the project. It has been a LONG time since did that in Access. From your code window go to Tools -> References. Then iirc it is something like "Microsoft ActiveX DataObjects" or something like that.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 15, 2014 at 2:50 pm
Sean Lange (5/15/2014)
jdasilva (5/15/2014)
ok. well lets go back to the original stuff Sean posted. He used 2 delcarations that don't work (user-defined type not defined):
dim parm as ADODB.Parameterdim cmd as ADOBD.Command
So here is my first issue. These 2 items seem to be key in the code, but if I can't declare them, I can go no further. I have tried with and without New in the declaration.
If you are doing this in Access you may have to add a reference in the project. It has been a LONG time since did that in Access. From your code window go to Tools -> References. Then iirc it is something like "Microsoft ActiveX DataObjects" or something like that.
This is starting to get more complex... I am not the only one using the front end, which would mean I would have to turn this on for everyone at this time, and in the future that will be working with this... I am taking a step in the wrong direction now. I think I should go back then to the DAO method...
Viewing 15 posts - 31 through 45 (of 50 total)
You must be logged in to reply to this topic. Login to reply