WE are new to SQL Server 2005. My team memebers and I have the same login-in
as a group. One member developed some stored procedure code that I am trying
to review , but there are thse little lock symbols on the file icon. how can
I see the file?
Do you mean when you open the file in Management Studio, the tab at the top
has a lock icon? This means the file is read-only, possibly because it is
checked into source control. If this is not what you are talking about,
then you can explain (a) where you see the lock, and (b) what it means that
you can't "see" the file?
A
"Candyman" <Candyman@.discussions.microsoft.com> wrote in message
news:F8A96B55-7ED2-4913-94EF-FB215804AE17@.microsoft.com...
> WE are new to SQL Server 2005. My team memebers and I have the same
> login-in
> as a group. One member developed some stored procedure code that I am
> trying
> to review , but there are thse little lock symbols on the file icon. how
> can
> I see the file?
|||WE are in Management Studio. On the left hand side is the Obkect Explorer,
We have many listings under databases. The tree structure looks like
Databases\db_ReportSource\Programmability\Stored Procedures. . . Then there
is a file dbo.SQL_myquery listed and the adjacent icon looks like a light
blue piece of paper with a darker blue stripe at the top. Then there is a
little yelow lock on the right lower corner of the icon. My team mate can
right click on the file and see a Modify option to show me the code. That
Modify option is greyed out to me. ( and four other procs he created.) How do
I see the code? Is there another way to get to it?
"Aaron Bertrand [SQL Server MVP]" wrote:
> Do you mean when you open the file in Management Studio, the tab at the top
> has a lock icon? This means the file is read-only, possibly because it is
> checked into source control. If this is not what you are talking about,
> then you can explain (a) where you see the lock, and (b) what it means that
> you can't "see" the file?
> A
> "Candyman" <Candyman@.discussions.microsoft.com> wrote in message
> news:F8A96B55-7ED2-4913-94EF-FB215804AE17@.microsoft.com...
>
>
|||If you just want to see it and not modify it, then:
(1) Right-click the database name and select new query
(2) Make sure you are in Results to Text (Ctrl+T)
(3) Type:
EXEC sp_helptext 'SQL_myquery';
(4) Hit F5 (or the Execute button on the toolbar)
"Candyman" <Candyman@.discussions.microsoft.com> wrote in message
news:119BA461-C65C-4755-83CA-E7EF1C4CAE3B@.microsoft.com...[vbcol=seagreen]
> WE are in Management Studio. On the left hand side is the Obkect
> Explorer,
> We have many listings under databases. The tree structure looks like
> Databases\db_ReportSource\Programmability\Stored Procedures. . . Then
> there
> is a file dbo.SQL_myquery listed and the adjacent icon looks like a light
> blue piece of paper with a darker blue stripe at the top. Then there is a
> little yelow lock on the right lower corner of the icon. My team mate can
> right click on the file and see a Modify option to show me the code. That
> Modify option is greyed out to me. ( and four other procs he created.) How
> do
> I see the code? Is there another way to get to it?
> "Aaron Bertrand [SQL Server MVP]" wrote:
|||if I 'check (green check)' it, the programs returns: "query excecuted
successfully".
Then when I try to run as suggested the command errors out saying "There is
no text for object 'SQL_myquery'"
Could the file actuallly be residing on the other users machine and not in
SQL Server? or network?
"Aaron Bertrand [SQL Server MVP]" wrote:
> If you just want to see it and not modify it, then:
> (1) Right-click the database name and select new query
> (2) Make sure you are in Results to Text (Ctrl+T)
> (3) Type:
> EXEC sp_helptext 'SQL_myquery';
> (4) Hit F5 (or the Execute button on the toolbar)
>
>
> "Candyman" <Candyman@.discussions.microsoft.com> wrote in message
> news:119BA461-C65C-4755-83CA-E7EF1C4CAE3B@.microsoft.com...
>
>
|||> if I 'check (green check)' it, the programs returns: "query excecuted
> successfully".
Yes, if you hold your mouse over the green check you decided to click, you
will see that means "parse" (syntax check). The success message means that
the command didn't have any syntax errors.
> Then when I try to run as suggested the command errors out saying "There
> is
> no text for object 'SQL_myquery'"
Are you sure your query is running in the correct database? When you
right-clicked "the database" was it db_ReportSource, or some other database?
Are you sure you were in an Object Explorer context of the correct server?
What happens when you execute the following in a properly created New Query
window:
USE db_ReportSource;
GO
EXEC SQL_myquery;
GO
EXEC dbo.SQL_myquery;
GO
?
> Could the file actuallly be residing on the other users machine and not in
> SQL Server? or network?
No, from your earlier description, this is a stored procedure in a database.
There are many differences between a stored procedure in the database and a
file in the file system. They are certainly not the same, and a file
somewhere on your network is certainly not going to magically appear under
the stored procedures node in object explorer.
A
|||Candyman (Candyman@.discussions.microsoft.com) writes:
> if I 'check (green check)' it, the programs returns: "query excecuted
> successfully".
> Then when I try to run as suggested the command errors out saying "There
> is no text for object 'SQL_myquery'"
> Could the file actuallly be residing on the other users machine and not in
> SQL Server? or network?
First of all, it is not a file. It's an object in a database, and there
should indeed be a file for it in the file system, or even better in
the version-control system. But if you co-worker just created the procedure
in a query window, and then closed the window without saving it, there is
no file at all. Just an object in a database.
Normally, though, stored procedures can be disassembled from the database
so you can edit them, which is popular in teams that don't believe in
files or version-control systems.
But this small lock indicates that you can't. There are two possible reasons
for this:
o The procedure was created with the clause WITH ENCRYPTION.
o It is a stored procedure created in a CLR language such as VB .Net or C#.
If you right-click the procedure and selecr Properties, you might be
able to deduce which of the cases it is. If there things like "Property
AnsiNullsStatus is not available..." it is a CLR stored procedure.
In either cases, you will have to ask your co-worker where he has the
source code.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||The application asked me for some date parameters, which I enterd and
recieved results back so there is something there, but I still can't see the
original code.
Erland might have hit it on found the solution as the file properties do
seem to be encrypted. This is something we will have to discuss withthe
group. Thanks so much for your input and patience.
"Aaron Bertrand [SQL Server MVP]" wrote:
> Yes, if you hold your mouse over the green check you decided to click, you
> will see that means "parse" (syntax check). The success message means that
> the command didn't have any syntax errors.
>
> Are you sure your query is running in the correct database? When you
> right-clicked "the database" was it db_ReportSource, or some other database?
> Are you sure you were in an Object Explorer context of the correct server?
> What happens when you execute the following in a properly created New Query
> window:
> USE db_ReportSource;
> GO
> EXEC SQL_myquery;
> GO
> EXEC dbo.SQL_myquery;
> GO
> ?
>
> No, from your earlier description, this is a stored procedure in a database.
> There are many differences between a stored procedure in the database and a
> file in the file system. They are certainly not the same, and a file
> somewhere on your network is certainly not going to magically appear under
> the stored procedures node in object explorer.
> A
>
>
|||BINGO!
Under properties in the Options section the file has Encrypted set to TRUE
and Replication set to FALSE also ( I guess which means I could not copy the
file which I tried to do also.)
We are baby new to this and I have to bring this up to the author of the
code and to our new “Weekly user group” . Both of which are out today, but
thank you for the info! This is helpful.
I will also suggest we start using Source Safe for version control.
Thanks!
"Erland Sommarskog" wrote:
> Candyman (Candyman@.discussions.microsoft.com) writes:
> First of all, it is not a file. It's an object in a database, and there
> should indeed be a file for it in the file system, or even better in
> the version-control system. But if you co-worker just created the procedure
> in a query window, and then closed the window without saving it, there is
> no file at all. Just an object in a database.
> Normally, though, stored procedures can be disassembled from the database
> so you can edit them, which is popular in teams that don't believe in
> files or version-control systems.
> But this small lock indicates that you can't. There are two possible reasons
> for this:
> o The procedure was created with the clause WITH ENCRYPTION.
> o It is a stored procedure created in a CLR language such as VB .Net or C#.
> If you right-click the procedure and selecr Properties, you might be
> able to deduce which of the cases it is. If there things like "Property
> AnsiNullsStatus is not available..." it is a CLR stored procedure.
> In either cases, you will have to ask your co-worker where he has the
> source code.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
|||> Under properties in the Options section the file has Encrypted set to TRUE
> and Replication set to FALSE also ( I guess which means I could not copy
> the
> file which I tried to do also.)
No, replication and copying a file are not the same thing.
A
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment