SQL 2K Stored Procedures in VB6

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL 2K Stored Procedures in VB6

Author

Topic

TestEngineer
Starting Member

29 Posts

Posted - 2005-11-02 : 13:55:36

I'm trying to get a feel for using stored procedures on SQL Server 2000 and accessing them through the ADODB reference in VB6. I created a Test table, "TestingTable1" that has "Test_ID" as a primary key (int, identity field) and SomeValue1 char(10).

I then right-clicked on "Stored Procedures" under the same database and selected "New Stored Procedure..." Below is what I entered

CREATE PROCEDURE spTesting
@val1 char(10)
AS
INSERT INTO TestingTable1 (SomeValue1)
VALUES (@val1)
GO

In my VB6 application, I replaced the SQL SELECT Statement passed to another table with "CALL spTesting (@val1='test')" and received an error message near @val1. I found that I needed to replace CALL with EXEC and then passed "EXEC spTesting (@val1='test')" and received the message: spTesting stored procedure could not be found." I checked the permissions and ensured the user account that the VB6 application is using to access had Exec privileges (it didn't) and check-marked it. It still returned the same error message.

If anyone could point me in a direction to get up to speed on basic stored procedures and the best way to access them through VB6 ADO, I'd appreciate it.

Thanks,
Tom

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2005-11-02 : 23:25:42

Please check in enterprise manager that who is the owner of the of object spTesting. Are you using the same user to access the databases?

The other reason could be there is something wrong in your code. Please also post the code you using to access the database and stored procedure.

Regards
Sachin

Don't sit back because of failure. It will come back to check if you still available. -- Binu

TestEngineer
Starting Member

29 Posts

Posted - 2005-11-07 : 12:42:13

Thanks for the suggestion SachinSamuel,

It was a matter of permissions. The database was set up to automatically use Windows authentication and I needed to login as EngineeringUser, as my software was doing. This gave me the access I need and I'm on my way to learning more about stored procedures.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources