Problems in executing SQL or Stored Procs with ASP

Hi experts,

I m working with MS SQL Server 2000 with ASP for my application. There're some Stored Procedures created for the new functions but seems I can't run these new SPs with my ASP pages. When I load that ASP page, it shows error message that can't find my SP. I've execute the SP alone in SQL Enterprise Manager and it works.

When I work with the SPs, I can connect with the DB with Enterprise Manager only without administrator right. As my SPs I suppose they should work with ASP but I just worry would it be the problem with my right granted in SQL server?

There are some other SPs running fine created by dbo but for my SPs not by dbo. Would there be any differences? Does it mean my SPs need to be granted by dbo instead of my current role? I m sure if I use the same ASP page then running another existing SPs, it works really smooth.

I also tried to make a SQL statement in my ASP page (e.g. an insert statement) but it seems nothing can be inserted. I got really screwed up!!!

Thanks in advance!!
Manfred

Manfred:

First, how are your ASP pages connecting to your database? Do you know they login, etc.? How do you give this login/user permissions to execute your stored procedure? Also, you might have an "owner" problem with the stored procedure object. Try running this query and posting the results:

select type,
uid,
left ([name], 40) as [name]
from sysobjects
where type = 'P'
and name = 'yourProcName'

-- - Sample Query Results: --

-- type uid name
-- - -
-- P 1 myProcName

Dave

|||

Thanks a lot for your advice Dave!

I got a dbo login name/password from my colleague and I've created a new SP under dbo login. It works! I can execute the stuff I want with the SP.

Here 's the query result

SELECT type, uid, LEFT(name, 40) AS name
FROM dbo.sysobjects
WHERE (type = 'P') AND (name = 'PROC_TESTDEPT')

-- type uid name
-- - -
-- P 1 proc_TestDept