Vey basic SP Question (3rd Grade Level maybe)

[cyberbiker]

I think I am missing something very basic in my understanding of stored procedures.

So I need clarification on a (or some) basic points that I either cannot find in BOL or do not understand when I read it there.

The current problem (as one example) is that I want to have a stored procedure 'Sp_dosomething' in database "A"
This stored procedure needs to cause a DTS package to execute buy using an extended stored procedure in the "master" database ( sp_OAMethod)

I need to have my VB code call the stored procedure.

I am finding that I cannot seem to execute a stored procedure unless I am connected to the database containing
that stored procedure which appears logical.

But, at the same time, it also appears logical that all the stored procedures and extended stored procedures in 'master' should have a way of being accessed from elsewhere.

Anybody able to give me a 2 syllable, 3rd grade level primer about running stored procedures? Everything I find seems to assume I know this already.








Terry (cyberbiker)

 

[cyberbiker]

oops, SQl Server 7

Terry (cyberbiker)

 

[ecobb]

I've never tried this with Stored Procedures, but when doing a simple "select" statement between 2 databases, you will need to reference the database in the form of:

DatabaseName.Owner.TableName

For Example:

Select EmployeeID
From HRdatabase.dbo.Employees

You may want to try that concept with your Procedures.

Instead of:
Exec dbo.Sp_dosomething

Try:
Exec DatabaseName.dbo.Sp_dosomething

Like I said, I have no idea if this will work or not, but it's worth a try. Funny, though, I thought that whenever you executed a Procedure with the prefix "sp_", SQL Server was automatically supposed to look in the master database first.




Hope This Helps!

Ecobb

"My work is a game, a very serious game." - M.C. Escher

 

[cyberbiker]

Thank you very much.
I had thought the same about prefixing the stored procedure with sp but it does not seem to work that way. (Or I am missing something else)

However: I am trying to use a stored procedure (In database A) to call another stored procedure(in master). Is that a problem) I am testing using query analyzer



Terry (cyberbiker)

 

[cyberbiker]

Well

Finally figured out that stored procedures are case sensitive. Which should explain many of my problems.

However the stored procedure

sp_displayoaerrorinfo

(refered to in BOL) is not present in master which is why my current problem occurred.

I now have another problem which I am working on.

If I do not figure it out soon I will post that one in Data Transformation Forum.

Thanks again ECobb, your answer made me search BOL a different way which led me to the case sensitive problem

Terry (cyberbiker)

 

[fluteplr]

Stored procs are only case sensitive if you set up the server to be case sensitive. If you do that ALL objects will be case sensitive. Tables, fields etc etc.

 

[cyberbiker]

I am very sorry for wasting everybodys time.

I found my problem and why I was so confused. Poor eyesight(seriously)

After testing fluteplr 's response as well as ECobb I found the stored procedure sp_AOCreate (located in master) will in fact execute from another database on the server regardless of case.

Again I very seriously want to apologize for the wasted time you guys have spent











Terry (cyberbiker)