VB 6 can not see Stored Procedures 2

[Ralph8]

I created some Stored Procedures using Enterprise Manager for a VB 6.0/SQL Server(MSDE) project.

They look good but no matter how I try to "Execute" or "Call" them, I get a message that the compiler can not see the Sub or Function.

I have checked out the samples, etc. that MS provides in the VS Help files and the developer's web page, but I always get the same result.

Can some one point me in the right direction?


TIA

Ralph D. Meredith

http://www.merecosoftware.com

 

[johnwm]

I seem to recall that CajunCenturion did this one:
thread709-556591

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[MarkSweetland]

Also specify dbo as the owner when you create the stored procedure so it is visible to all users, not just the user that created it (SQL Server defaults to the user as the owner)

CREATE PROCEDURE [red]dbo[/red].myspname
AS
.
.
.


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[SonOfEmidec1100]

What do you mean by "compiler can not see the Sub or Function". Sort of seems to me that its not the sp but the subroutine/function you are calling. Maybe these are in a different module check they are declared as public not private. Or in a different form check declared as public and prefix the name with the form name when calling the function. (ie formname.subname)

Hope this helps, apologies if Im pointing you in the wrong direction.

 

[SemperFiDownUnda]

Show code please.

Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first

 

[Ralph8]

My current attempt appears to be the closest to working:

Option Explicit

' Services List by Teacher
Private Sub DataReport_Initialize()
.
.
rsSQLTempData.Open ("EXECUTE sp_Sort_Services_Tchr&quot

The above code gives a "RTE 91: Object variable or With block variable not set". I believe I have tried most/all the variations in the MS samples and in Thread 709-556591 and most of them make some reference to a Sub or Function not found.

Public rsSQLTempData As Recordset is in the Declarations of a MOD.

sp_Sort_Services_Tchr is a Stored Procedure that was created using the Enterprise Manager.



TIA

Ralph D. Meredith

http://www.merecosoftware.com

 

[rdroske]

Your problem is with rsSQLTempData, delclared but not created, add New to Dim or

Set rsSQLTempData = New Recordset

 

[Ralph8]

The "new" got me by the RTE 91. Now I am hanging with RTE's "3709: Operation is not allowed on an object referencing a closed or invalid connection" or "3001: Arguments of wrong type, out of range, or in conflict with each other", depending on what I try.

What I am trying to do here is extract some records from a temporary table that I have built in the DB into an independent record set (rsSQLTempData) using "ORDER BY" to Sort them into the needed order. Then I will clear the temporary table and dump rsSQLTempData back into it for further processing.

Said all that to say: A - The problem now seems to have something to do with a connection, and B - I am entering a state of confusion. I am going to take a break and come back and look again and see if my eyes see any thing differently.

Thanks guys.

TIA

Ralph D. Meredith

http://www.merecosoftware.com

 

[rdroske]

We haven't seen your code regarding the connection or how or if you set the active connection on your recordset, post more...

Also did you try your SP in Query Analyzer to verify that there are no errors in it?

 

[RiverGuy]

You're not using a connection. Try

rsSQLTempData.Open "EXECUTE sp_Sort_Services_Tchr", YourConnectionObjectOrString

 

[Joe46]

Have you tried running them with the execute method of the command object?

Joe

 

[rdroske]

Could be no connection, Iwas assuming recordset.ActiveConnection was set above the code shown.

Shouoldn't matter which way you chose to run it if it correct results should be the same.

 

[Ralph8]

This is in a Mod:

Option Explicit
.
Public rsSQLTempData As Recordset

This is my code:

' Services List by Teacher
Private Sub DataReport_Initialize()
.
Set rsSQLTempData = New Recordset
rsSQLTempData.Open "EXECUTE sp_Sort_Services_Tchr", pCon

pCon contains my connection string. That helped. Thanks RiverGuy.

This is my sp in the DB:

CREATE PROCEDURE sp_Sort_Services_Tchr AS
Select * into rsSQLTempData from tblTempData _
Order by Temp13, Temp2, Temp3, Temp4, Temp6, Temp11

I now get RTE '-2147217900(80040e14)': There is already an object named rsSQLTempData in the Data Base

There is no other code referring to rsSQLTempData prior to the above lines. The RTE occurs when executing the .Open "Execute line.

I now can't get around this and can't find a fix. I'm beginning to feel like a real dunce. I've been around for a while and have had decades of trouble shooting experience, but I am not doing well on this SQL.

Oh Well . . . Thanks for listening. any further ideas?


TIA

Ralph D. Meredith

http://www.merecosoftware.com

 

[RiverGuy]

Instead of SELECTING INTO TempData, SELECT INTO #TempData.

 

[rdroske]

It looks like what you are trying to do is get all of the records from the SQL table tblTempData into the recordset object in your VB program.

You can't reference that object from the sp but if that is what you want you don't even need the sp.

Just use

rsSQLTempData.Open "Select * from tblTempData _
Order by Temp13, Temp2, Temp3, Temp4, Temp6, Temp11", pCon

Post again if that is not what you are trying to do.

 

[stoolpigeon]

This is what SQL Server Books On Line for SQL Server 2000 says about Select INTO- it is trying to create a new table but you already have the table in existence. You want something like "INSERT into foo values (select * from foo2)"

I am also confused why you would want to worry about ordering the results when you are just pushing them into a table.

Finally- if you are not returning any results for use by your application, using a recordset to execute a stored procedure just creates overhead that you don't need. You can execute SQL Server stored procedures in VB as a method of a connection. Something like this:

dim cn as adodb.connection
set cn = new adodb.connection
cn.connectionstring="foofaafoo"
cn.open

cn.stored_procedure_name

There are lots of ways to do it- that's my favorite. Select Into documentation below. I'd really recommend you use the books online that should be with your Enterprise Manager. They offer lots of help for the database side of things.



INTO Clause
Creates a new table and inserts the resulting rows from the query into it.The user executing a SELECT statement with the INTO clause must have CREATE TABLE permission in the destination

database. SELECT...INTO cannot be used with the COMPUTE. For more information, see Transactions and Explicit Transactions.

You can use SELECT...INTO to create an identical table definition (different table name) with no data by having a FALSE condition in the WHERE clause.

Syntax
[ INTO new_table ]

Arguments
new_table

Specifies the name of a new table to be created, based on the columns in the select list and the rows chosen by the WHERE clause. The format of new_table is determined by evaluating the expressions in the select list. The columns in new_table are created in the order specified by the select list. Each column in new_table has the same name, data type, and value as the corresponding expression in the select list.

When a computed column is included in the select list, the corresponding column in the new table is not a computed column. The values in the new column are the values that were computed at the time SELECT...INTO was executed.

In this release of SQL Server, the select into/bulkcopy database option has no effect on whether you can create a permanent table with SELECT INTO. The amount of logging for certain bulk operations, including SELECT INTO, depends on the recovery model in effect for the database. For more information, see Using Recovery Models.

In previous releases, creating a permanent table with SELECT INTO was allowed only if select into/bulkcopy was set.

select into/bulkcopy is available for backward compatibility purposes, but may not be supported in future releases. Refer to the Recovery Models and Backward Compatibility and ALTER DATABASE topics for more information.

 

[RiverGuy]

If he is working with a regular user table, then two people cannot simultaneously use his application. By using a #Temporary table local to the connection, a stored procedure makes sense.

 

[SemperFiDownUnda]

If he's working with intergrated security he can have 2 people run this at the same time. But I'm not sure that is what he/she wants anyway. User tables are slower then temp tables.

If the table does not have to stick around then use a temp table. But you must make sure that you destroy it after you are done with it like a normal table if you plan to have this procedure run multiple times. I know temp tables will get dropped when connections are closed but it is a good habit to drop them yourself because when you get into COM and using connection pooling the connections don't actually close when your application closes but remain open waiting for another request for a ADODB.Connection with the same connection information.

When using either the ADODB.Command object or the ADODB.Connection object to execute a query that does not expect a result set back, as stoolpigeon is suggesting, make sure you use the adExecuteNoRecords option. Otherwise you suffer the same performance hits as using a recordset as both the command object's and connection object's .Execute method both, by default, set up a buffer for the recordset to be returned.

Use

SELECT ...
  INTO ...
  FROM ...

when you know the table does not exsists.
use

INSERT INTO ....
SELECT ....
  FROM ....

when the table already exsists.



Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first

 

[RiverGuy]

There could theoretically be two system administrators using it at the same time. They might be using the dbo schema. Therefore, I still think its a good idea to use a #.

 

[rdroske]

I still say all he is trying to do is select a table from an SQL database into a recordset in his VB program and was thinking he could (had to?) reference that recordset in the SQL sp.