Locked Stored Procedures

Hi

I am reasonably new to this so bear with me.

I have a copy of a SQL Server 2000 Database, and would like to see what the stored procedures are doing (as I think it is wrong).

in Server Management Studio the stored procedures have a lock symbol on them and when I try and view them I get the message:

TITLE: Microsoft SQL Server Management Studio

Script failed for StoredProcedure 'dbo.sp_procedure'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Script+StoredProcedure&LinkId=20476

ADDITIONAL INFORMATION:

Property TextHeader is not available for StoredProcedure '[dbo].[sp_procedureprocedure]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. The text is encrypted. (Microsoft.SqlServer.Smo)

This was developed by someone else, but I need to see it.

My question is, is there any way i can see the text of this stored procedure?

Thanks

Will

The stored procedure code has been encrypted. Apparently, whoever wrote the code didn't want others to read the code.

I suggest that you contact the person ( or vendor ) that supplied the stored procedure code and request a copy of the code.

Local or global variable in an interaction session of the SQL analyzer

Hi,

I am new to SQL. Please bear with me and allow me to ask a dumb
question.

I am debugging a stored procedure (written in Trans-SQL), and I found
that the SQL analyzer that I use doesn't have a debugger. All I can
do it is execute a block of code and see what is going on in an
interaction seesion of the SQL analyzer. I would need to declare some
variable to hold values of the previous query in the interactive
session.

I understand that this can be easily done in a stored procedure via
the Declare command (e.g., Delcare @.order_no int). Is similar
functionality exists in an interaction session of the SQL analyzer?
If so, what is the command. Please advise.

Thank you very much for the help.

AlexAlex Cicco (lluum@.yahoo.com) writes:
> I am debugging a stored procedure (written in Trans-SQL), and I found
> that the SQL analyzer that I use doesn't have a debugger. All I can
> do it is execute a block of code and see what is going on in an
> interaction seesion of the SQL analyzer. I would need to declare some
> variable to hold values of the previous query in the interactive
> session.

I suppose that with SQL Analyzer you refer to Query Analyzer. If you are
using QA, you do indeed have an access to a debugger. Press F8 to get an
Object Browser Window, and then find the procedure. Right-click and at
the bottom of the context menu, voil!

> I understand that this can be easily done in a stored procedure via
> the Declare command (e.g., Delcare @.order_no int). Is similar
> functionality exists in an interaction session of the SQL analyzer?
> If so, what is the command. Please advise.

The same. For instance:

DECLARE @.x datetime
SET @.x = getdate()
SELECT @.x

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp