open more than one table

this is what i have so far but i get a whole lot of errors

Use {Tools Database}
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetAppEle_P]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Test]
GO

Create Procedure Test
as
Set NoCount On
Select AER.AppID, AER.ROleID
FROM AppElemRole_T AER
WHERE AER.AppId < 10000 and AER.RoleId<1000
ORDER BY AER.AppId DESC
GO
GRANT EXEC ON Test to StufilesUser
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Three issues I see - and I've bolded each one

1) You missed one GETAPPELE_T.
2) The SELECT Statement itself should be the one you wish to execute in the SPROC
3) The GRANT EXEC line should be removed

Code:

---

Use {Tools Database}
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetAppEle_P]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Test]
GO

Create Procedure Test
as
Set NoCount On
Select AER.AppID, AER.ROleID
FROM AppElemRole_T AER
WHERE AER.AppId < 10000 and AER.RoleId<1000
ORDER BY AER.AppId DESC
GO
GRANT EXEC ON Test to StufilesUser
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

---

should i get rid of one of the GO statements? do they go before or after the statements?

GO is really a little command for QA to "package" up some query lines and send them to the server.

When GO is encountered - everything prior to it gets executed. Then processing continues to the next GO statement - for that next piece to be executed.

GO's follow stuff.

Getting rid of the double GO is a good thing - but probably not required anyway.

it says that i have a

Quote:

---

[Microsoft][ODBC SQL Server Driver]Syntax error or access violation

---

i'm not sure what that is but here is my code so far

Code:

---

Use {Tools Database}
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Test]')
        and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Test]
GO

Create Procedure Test
as
  Set NoCount On
  Select *
  FROM [Tool data]
  order by [ToolNumber] ASC
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

---

Double-click on the error message/line note and the cursor should jump to where the error is.

If that doesn't work then:

Put two - characters in front of everything - this is the comment character in T-SQL.

Start removing them from the top - to find what part isn't working.

So to start it will look like this. Execute that.

Then remove some more "--" until the NEXT GO STATEMENT - execute that - where does it work and when does it stop working?

Code:

---

Use {Tools Database}
SET QUOTED_IDENTIFIER ON
GO
--SET ANSI_NULLS ON
--GO

--if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Test]')
        and OBJECTPROPERTY(id, N'IsProcedure') = 1)
--drop procedure [dbo].[Test]
--GO

--Create Procedure Test
--as
--  Set NoCount On
--  Select *
--  FROM [Tool data]
--  order by [ToolNumber] ASC
--GO
--SET QUOTED_IDENTIFIER OFF
--GO
--SET ANSI_NULLS ON
--GO

---

i did that and i still get that error so i think that it may have some thing to do with the line
Use {Tools Database}
the word database is in blue so i think that maybe that has some thing to do with it.

Change it to:

Code:

---

Use [Tools Database]

---

Your database name should be in the square brackets.

ok i got it. was supposed to be
[tool database]
instead of
{tool database}
i executed it but it doesn't give any results

sorry didn't see your last post. so any way any suggestions on how to show the results?

Running the script that you've posted only creates the stored procedure. To get it's results, you need to run the SPROC.

In QA, type Use [Tool Database] and hit the F5 key (execute). Then type in the name of your SPROC (Test) and hit the F5 key again. You should see your results.

To answer your earlier question - this is how you would get the SPROC to execute from VB - using my GetAppEle_P example...

VB Code:

Dim objCmd As New ADODB.Command
Dim rsTemp As New ADODB.Recordset
 
    objCmd.CommandText = "GetAppEle_P"
    objCmd.CommandType = adCmdStoredProc
    
    objCmd.ActiveConnection = gCn
    Set rsTemp = objCmd.Execute

next lesson - SPROCS can have parameters

MSDN Stored Procedures

This link can provide you with more information on creating/using stored procedures.

i hit F5 in there but nothing happened. how would i type in [Tool Database] again? i looked for a place to type it but i can't find it.

In QA, click on File->New (or hit Ctrl+N). Type in Use [Tool Database] and hit the F5 key to execute the statement. Remove the Use [Tool Database] statement and type in Test and hit the F5 key again. Your records should appear in the Results pane.

Try:

Code:

---

USE [TOOLS DATABASE]
GO
EXEC TEST
GO

---

where would i put that?

Just click the NEW QUERY button - giving you a nice blank query window in QA and put that "Use...EXEC TEST" stuff.

ok do i have to save the sproce first? if so what folder? the same as the database?

Ok - you gotta keep in mind you have a couple of things going on here.

You have one query window where you are editing a SCRIPT for DROPPING and CREATING a procedure. Yes, you do need to SAVE this and what folder you put it in has nothing to do with SQL.

The .SQL file that gets saved is kind of like the .FRM/.BAS files in VB - it's source code. Create a folder called TOOLS DB SPROCS if you want and save it there.

We actually put our .SQL files in VISUAL SOURCE SAFE - so that we can keep track of changes to them.

When you EXECUTE QUERY (press F5) on the SPROC script in QA it's dropping and reloading the SPROC in the DATABASE - kind of like COMPILING and MAKING an EXECUTABLE in VB.

When you create another QUERY WINDOW and do the "USE...GO...EXEC TEST..." you are simply testing the SPROC by running it against the database.

ok i saved the sproc but it does not work even when i open a new file and do the USE..GO...EXEC TEST. it says that it can't find it.

In QA, press F8 - opens an object browser on the left hand side.

Make sure that you have clicked for a NEW and empty QUERY window.

Find your table in the object browser - click the plus sign to open the branch - click the plus sign in front of STORED PROCEDURES.

You will see your procedure there. Sometimes QA has odd REFRESH requirements - but if you just get into QA, from EM, you should see your SPROC in the list.

Right-click on your SPROC - drag it into the QUERY WINDOW and let go of the cursor - choose EXECUTE from the little menu.

If all that works, you should be able to press F5 and run your SPROC.

ok that worked
great
now can i use SPROCE's to make my program run faster?

Quote:

---

Originally posted by szlamany
To answer your earlier question - this is how you would get the SPROC to execute from VB - using my GetAppEle_P example...

VB Code:

Dim objCmd As New ADODB.Command
Dim rsTemp As New ADODB.Recordset
 
    objCmd.CommandText = "GetAppEle_P"
    objCmd.CommandType = adCmdStoredProc
    
    objCmd.ActiveConnection = gCn
    Set rsTemp = objCmd.Execute

next lesson - SPROCS can have parameters

---

I posted this earlier - it's the ADO way to execute a SPROC and return a recordset.

Good luck...

so how would i display the results in the textboxes?
rstemp!field=textbox.text?

Well, I would think it would be more like:

text1.text=rstemp!fieldname

It's just a regular recordset though - it doesn't really know that it came from a SPROC and not an in-line SQL statement.

How do you take data from ADO RS's now and put them into things??

i use a simple display function, but if it the same as a normal rs then i wouldn't have to change that part of the code. i have some things to finish up here and then maybe i'll work on a SPROC for my app. thanx for all the help.