Stored procedure;1

Stored procedure;1

Post by warwa » Wed, 03 Mar 2004 03:14:26



Can anyone tell me what ;1 means after a stored procedure name.  It is only
visible from some PCs in our network And they cannot run the stored
procedures

TIA
Warway

 
 
 

Stored procedure;1

Post by Arn0 » Wed, 03 Mar 2004 03:25:16



Quote:> Can anyone tell me what ;1 means after a stored procedure name.  It is
only
> visible from some PCs in our network And they cannot run the stored
> procedures

> TIA
> Warway

;number

Is an optional integer used to group procedures of the same name so they can
be dropped together with a single DROP PROCEDURE statement. For example, the
procedures used with an application called orders may be named orderproc;1,
orderproc;2, and so on. The statement DROP PROCEDURE orderproc drops the
entire group. If the name contains delimited identifiers, the number should
not be included as part of the identifier; use the appropriate delimiter
around procedure_name only.

 
 
 

Stored procedure;1

Post by warwa » Wed, 03 Mar 2004 03:32:55


I've no idea what you mean by this, sorry.

I have a stored procedure:
Alter PROCEDURE dbo.sp_QuotesActive4CQS

AS

SELECT * FROM "QuotesActive4CQSView"

ORDER BY "QuotesActive4CQSView".QuoteID DESC

and a View

SELECT dbo.tblQuote.QuoteID, dbo.tblCompany.Company,
    dbo.tblQuote.QuoteCreatedDate, dbo.tblPorts.Port,
    dbo.tblkpDespatchMethodType.DespatchMethodTypeID,
    dbo.tblkpService.ServiceDescription,
    dbo.tblEmployees.NameFirst + ' ' + dbo.tblEmployees.NameLast
     AS Name, dbo.tblOffice.Office, dbo.tblQuote.CreatedBy
FROM dbo.tblEmployees RIGHT OUTER JOIN
    dbo.tblkpDespatchMethodType RIGHT OUTER JOIN
    dbo.tblkpService RIGHT OUTER JOIN
    dbo.tblQuote INNER JOIN
    dbo.tblCompany ON
    dbo.tblQuote.CompanyID = dbo.tblCompany.CompanyID LEFT OUTER
     JOIN
    dbo.tblkpDespatchMethod ON
    dbo.tblQuote.DespatchMethodID = dbo.tblkpDespatchMethod.DespatchMethodID
     ON dbo.tblkpService.ServiceID = dbo.tblQuote.ServiceID ON
    dbo.tblkpDespatchMethodType.DespatchMethodTypeID =
dbo.tblQuote.DespatchMethodTypeID
     LEFT OUTER JOIN
    dbo.tblOffice ON
    dbo.tblQuote.OfficeID = dbo.tblOffice.OfficeID ON
    dbo.tblEmployees.username = dbo.tblQuote.CreatedBy LEFT OUTER
     JOIN
    dbo.tblPorts ON
    dbo.tblQuote.PortofDischarge = dbo.tblPorts.PortID
WHERE (dbo.tblQuote.ShipmentID = 0)

I don't beleive that I am doing what you say below.  My development version
and the Enterprise Manager view of the stored procedures don't have ;number

Only some PCs viewing the application, it is not user specific.

Do I need to DROP everything after it has run?

Warway




> > Can anyone tell me what ;1 means after a stored procedure name.  It is
> only
> > visible from some PCs in our network And they cannot run the stored
> > procedures

> > TIA
> > Warway

> ;number

> Is an optional integer used to group procedures of the same name so they
can
> be dropped together with a single DROP PROCEDURE statement. For example,
the
> procedures used with an application called orders may be named
orderproc;1,
> orderproc;2, and so on. The statement DROP PROCEDURE orderproc drops the
> entire group. If the name contains delimited identifiers, the number
should
> not be included as part of the identifier; use the appropriate delimiter
> around procedure_name only.

 
 
 

Stored procedure;1

Post by Arn0 » Wed, 03 Mar 2004 04:09:47



> I've no idea what you mean by this, sorry.

<snip>

> I don't beleive that I am doing what you say below.  My development
version
> and the Enterprise Manager view of the stored procedures don't have
;number

> Only some PCs viewing the application, it is not user specific.

> Do I need to DROP everything after it has run?

> Warway





> > > Can anyone tell me what ;1 means after a stored procedure name.  It is
> > only
> > > visible from some PCs in our network And they cannot run the stored
> > > procedures

> > > TIA
> > > Warway

> > ;number

> > Is an optional integer used to group procedures of the same name so they
> can
> > be dropped together with a single DROP PROCEDURE statement. For example,
> the
> > procedures used with an application called orders may be named
> orderproc;1,
> > orderproc;2, and so on. The statement DROP PROCEDURE orderproc drops the
> > entire group. If the name contains delimited identifiers, the number
> should
> > not be included as part of the identifier; use the appropriate delimiter
> > around procedure_name only.

Sorry - I have seen multiple stored procs with the same name like this
before, and just cut and pasted my answer from BOL (CREATE PROCEDURE).

Do these other PCs have Enterprise Manager? I am wondering if they are other
stored procedures with the same name, but perhaps owned by others which
could be why you can't see them. If you could generate the stored proc
script (for sp_QuotesActive4CQS; 1) on these other PCs you could compare the
code with your version.

If it is multiple procs with the same name, then DROP PROC
sp_QuotesActive4CQS would drop all of them - make sure you have the code to
recreate!

Hope that's clearer (and that I'm not barking up the wrong tree...)

 
 
 

Stored procedure;1

Post by warwa » Wed, 03 Mar 2004 04:16:54


Only the server has Enterprise Manager. I have checked the database and
there are only sp's owned by dbo. If I create sp with the ;1 then I won;'t
be able to access it from the PCs that alreaedy can.

Is it something to do with session?

Warway




> > I've no idea what you mean by this, sorry.

> <snip>

> > I don't beleive that I am doing what you say below.  My development
> version
> > and the Enterprise Manager view of the stored procedures don't have
> ;number

> > Only some PCs viewing the application, it is not user specific.

> > Do I need to DROP everything after it has run?

> > Warway





> > > > Can anyone tell me what ;1 means after a stored procedure name.  It
is
> > > only
> > > > visible from some PCs in our network And they cannot run the stored
> > > > procedures

> > > > TIA
> > > > Warway

> > > ;number

> > > Is an optional integer used to group procedures of the same name so
they
> > can
> > > be dropped together with a single DROP PROCEDURE statement. For
example,
> > the
> > > procedures used with an application called orders may be named
> > orderproc;1,
> > > orderproc;2, and so on. The statement DROP PROCEDURE orderproc drops
the
> > > entire group. If the name contains delimited identifiers, the number
> > should
> > > not be included as part of the identifier; use the appropriate
delimiter
> > > around procedure_name only.

> Sorry - I have seen multiple stored procs with the same name like this
> before, and just cut and pasted my answer from BOL (CREATE PROCEDURE).

> Do these other PCs have Enterprise Manager? I am wondering if they are
other
> stored procedures with the same name, but perhaps owned by others which
> could be why you can't see them. If you could generate the stored proc
> script (for sp_QuotesActive4CQS; 1) on these other PCs you could compare
the
> code with your version.

> If it is multiple procs with the same name, then DROP PROC
> sp_QuotesActive4CQS would drop all of them - make sure you have the code
to
> recreate!

> Hope that's clearer (and that I'm not barking up the wrong tree...)

 
 
 

Stored procedure;1

Post by Arn0 » Wed, 03 Mar 2004 04:48:18



> Only the server has Enterprise Manager. I have checked the database and
> there are only sp's owned by dbo. If I create sp with the ;1 then I won;'t
> be able to access it from the PCs that alreaedy can.

> Is it something to do with session?

> Warway





> > > I've no idea what you mean by this, sorry.

> > <snip>

> > > I don't beleive that I am doing what you say below.  My development
> > version
> > > and the Enterprise Manager view of the stored procedures don't have
> > ;number

> > > Only some PCs viewing the application, it is not user specific.

> > > Do I need to DROP everything after it has run?

> > > Warway





> > > > > Can anyone tell me what ;1 means after a stored procedure name.
It
> is
> > > > only
> > > > > visible from some PCs in our network And they cannot run the
stored
> > > > > procedures

> > > > > TIA
> > > > > Warway

> > > > ;number

> > > > Is an optional integer used to group procedures of the same name so
> they
> > > can
> > > > be dropped together with a single DROP PROCEDURE statement. For
> example,
> > > the
> > > > procedures used with an application called orders may be named
> > > orderproc;1,
> > > > orderproc;2, and so on. The statement DROP PROCEDURE orderproc drops
> the
> > > > entire group. If the name contains delimited identifiers, the number
> > > should
> > > > not be included as part of the identifier; use the appropriate
> delimiter
> > > > around procedure_name only.

> > Sorry - I have seen multiple stored procs with the same name like this
> > before, and just cut and pasted my answer from BOL (CREATE PROCEDURE).

> > Do these other PCs have Enterprise Manager? I am wondering if they are
> other
> > stored procedures with the same name, but perhaps owned by others which
> > could be why you can't see them. If you could generate the stored proc
> > script (for sp_QuotesActive4CQS; 1) on these other PCs you could compare
> the
> > code with your version.

> > If it is multiple procs with the same name, then DROP PROC
> > sp_QuotesActive4CQS would drop all of them - make sure you have the code
> to
> > recreate!

> > Hope that's clearer (and that I'm not barking up the wrong tree...)

You got me then - have you refreshed the sps in Enterprise Manager? How can
the sp's be seen on the PCs - i.e. if not Enterprise Manager then a custom
application? If so, then maybe the issue is there. If you can't see
sp_QuotesActive4CQS; 1 on the server then it's not there. (also check:

select * from sysobjects (nolock) where name like 'sp_QuotesActive%'

)

Also check/run above) in Master db as all procs beginning with sp_ can exist
in Master but can be run from any other db (providing an sp with the same
name does not exist in your db).

Good luck!

 
 
 

Stored procedure;1

Post by warwa » Wed, 03 Mar 2004 05:36:02


Checked the sysobjects

sp_QuoteFax 1147151132 P  1 0 1610612736 192 0 0 2004-02-20 15:12:25.610 0
192 0 P  0 4 0 2004-02-20 15:12:25.610 0 0 0 0 0 0 0
sp_QuoteChargesInCompare 1195151303 P  1 0 1610612736 32 0 0 2004-02-23
16:38:48.560 0 32 0 P  0 4 0 2004-02-23 16:38:48.560 0 0 0 0 0 0 0
sp_QuoteSelection 1291151645 P  1 0 1610612736 96 0 0 2004-02-24
15:17:18.607 0 96 0 P  0 4 0 2004-02-24 15:17:18.607 0 0 0 0 0 0 0
sp_Quote2Shipment 1339151816 P  1 0 1610612736 32 0 0 2004-02-24
16:27:10.200 0 32 0 P  0 4 0 2004-02-24 16:27:10.200 0 0 0 0 0 0 0
sp_QuotesActive 1730105204 P  1 0 1610612736 32 0 0 2004-02-16 16:55:49.343
0 32 0 P  0 4 0 2004-02-16 16:55:49.343 0 0 0 0 0 0 0
sp_QuotesActive4CQS 1762105318 P  1 0 1610612736 48 0 0 2004-02-16
16:55:49.797 0 48 0 P  0 4 0 2004-02-16 16:55:49.797 0 0 0 0 0 0 0
sp_QuotesAll 1890105774 P  1 0 1610612736 32 0 0 2004-02-16 16:55:51.250 0
32 0 P  0 4 0 2004-02-16 16:55:51.250 0 0 0 0 0 0 0

Nothing out the ordinary in my database and nothing at all sp_quote in
Master!

Warway




> > Only the server has Enterprise Manager. I have checked the database and
> > there are only sp's owned by dbo. If I create sp with the ;1 then I
won;'t
> > be able to access it from the PCs that alreaedy can.

> > Is it something to do with session?

> > Warway





> > > > I've no idea what you mean by this, sorry.

> > > <snip>

> > > > I don't beleive that I am doing what you say below.  My development
> > > version
> > > > and the Enterprise Manager view of the stored procedures don't have
> > > ;number

> > > > Only some PCs viewing the application, it is not user specific.

> > > > Do I need to DROP everything after it has run?

> > > > Warway





> > > > > > Can anyone tell me what ;1 means after a stored procedure name.
> It
> > is
> > > > > only
> > > > > > visible from some PCs in our network And they cannot run the
> stored
> > > > > > procedures

> > > > > > TIA
> > > > > > Warway

> > > > > ;number

> > > > > Is an optional integer used to group procedures of the same name
so
> > they
> > > > can
> > > > > be dropped together with a single DROP PROCEDURE statement. For
> > example,
> > > > the
> > > > > procedures used with an application called orders may be named
> > > > orderproc;1,
> > > > > orderproc;2, and so on. The statement DROP PROCEDURE orderproc
drops
> > the
> > > > > entire group. If the name contains delimited identifiers, the
number
> > > > should
> > > > > not be included as part of the identifier; use the appropriate
> > delimiter
> > > > > around procedure_name only.

> > > Sorry - I have seen multiple stored procs with the same name like this
> > > before, and just cut and pasted my answer from BOL (CREATE PROCEDURE).

> > > Do these other PCs have Enterprise Manager? I am wondering if they are
> > other
> > > stored procedures with the same name, but perhaps owned by others
which
> > > could be why you can't see them. If you could generate the stored proc
> > > script (for sp_QuotesActive4CQS; 1) on these other PCs you could
compare
> > the
> > > code with your version.

> > > If it is multiple procs with the same name, then DROP PROC
> > > sp_QuotesActive4CQS would drop all of them - make sure you have the
code
> > to
> > > recreate!

> > > Hope that's clearer (and that I'm not barking up the wrong tree...)

> You got me then - have you refreshed the sps in Enterprise Manager? How
can
> the sp's be seen on the PCs - i.e. if not Enterprise Manager then a custom
> application? If so, then maybe the issue is there. If you can't see
> sp_QuotesActive4CQS; 1 on the server then it's not there. (also check:

> select * from sysobjects (nolock) where name like 'sp_QuotesActive%'

> )

> Also check/run above) in Master db as all procs beginning with sp_ can
exist
> in Master but can be run from any other db (providing an sp with the same
> name does not exist in your db).

> Good luck!

 
 
 

Stored procedure;1

Post by Arn0 » Wed, 03 Mar 2004 05:38:16



Quote:> Checked the sysobjects

> sp_QuoteFax 1147151132 P  1 0 1610612736 192 0 0 2004-02-20 15:12:25.610 0
> 192 0 P  0 4 0 2004-02-20 15:12:25.610 0 0 0 0 0 0 0
> sp_QuoteChargesInCompare 1195151303 P  1 0 1610612736 32 0 0 2004-02-23
> 16:38:48.560 0 32 0 P  0 4 0 2004-02-23 16:38:48.560 0 0 0 0 0 0 0
> sp_QuoteSelection 1291151645 P  1 0 1610612736 96 0 0 2004-02-24
> 15:17:18.607 0 96 0 P  0 4 0 2004-02-24 15:17:18.607 0 0 0 0 0 0 0
> sp_Quote2Shipment 1339151816 P  1 0 1610612736 32 0 0 2004-02-24
> 16:27:10.200 0 32 0 P  0 4 0 2004-02-24 16:27:10.200 0 0 0 0 0 0 0
> sp_QuotesActive 1730105204 P  1 0 1610612736 32 0 0 2004-02-16
16:55:49.343
> 0 32 0 P  0 4 0 2004-02-16 16:55:49.343 0 0 0 0 0 0 0
> sp_QuotesActive4CQS 1762105318 P  1 0 1610612736 48 0 0 2004-02-16
> 16:55:49.797 0 48 0 P  0 4 0 2004-02-16 16:55:49.797 0 0 0 0 0 0 0
> sp_QuotesAll 1890105774 P  1 0 1610612736 32 0 0 2004-02-16 16:55:51.250 0
> 32 0 P  0 4 0 2004-02-16 16:55:51.250 0 0 0 0 0 0 0

> Nothing out the ordinary in my database and nothing at all sp_quote in
> Master!

> Warway

I tried :-)

Can only refer you to whatever is displaying the sp's on the other pc's.

Hope someone else can help!

 
 
 

Stored procedure;1

Post by Trey Walpol » Wed, 03 Mar 2004 05:46:20


My understanding is that the ";1" is supposed to be an internal versioning
code for the stored procedure, but I'm not sure that works correctly [or
perhaps isn't fully implemented] since I've never seen anything but ";1" (no
";2" ";3" etc.)
I've seen this used primarily in tools such as Crystal Reports, but also
when you right-click on the proc in QA and choose a Script as Execute
option.

AFAIK, it is unnecessary to use and is basically ignored.

What do you mean by "cannot run"? What errors are being returned? What
application is trying to run it with the ";1" [Crystal, etc.]?
I'd typically suspect a rights issue.


Quote:> Can anyone tell me what ;1 means after a stored procedure name.  It is
only
> visible from some PCs in our network And they cannot run the stored
> procedures

> TIA
> Warway

 
 
 

Stored procedure;1

Post by Arn0 » Wed, 03 Mar 2004 06:00:55


<snip>
Quote:

> You got me then - have you refreshed the sps in Enterprise Manager? How
can
> the sp's be seen on the PCs - i.e. if not Enterprise Manager then a custom
> application? If so, then maybe the issue is there. If you can't see
> sp_QuotesActive4CQS; 1 on the server then it's not there. (also check:

> select * from sysobjects (nolock) where name like 'sp_QuotesActive%'

> )

> Also check/run above) in Master db as all procs beginning with sp_ can
exist
> in Master but can be run from any other db (providing an sp with the same
> name does not exist in your db).

> Good luck!

Ah - I need to modify this as well (thanks to Trey post). In fact you will
only see sp_QuotesActive4CQS in EM and sysobjects, BUT if you right click
and script Create you could get

create proc QuotesActive4CQS ;1 as select * from master..sysservers
GO

create proc QuotesActive4CQS ;2 as select * from sysobjects
GO

as an example. But you could still

EXEC QuotesActive4CQS; 1
EXEC QuotesActive4CQS; 2

Apologies for that - Crystal Reports could well be a suspect...

 
 
 

Stored procedure;1

Post by warwa » Wed, 03 Mar 2004 17:52:40


Thanks for all your help.

I'm using SQL2000 SP3 on SBS2003 server and Windows2000 workstations with MS
Access 2000 project file, I've tried both adp and ade with the same results.

The ones that don't work are looking for the sp with the ;1 suffix and when
the same project it is run from another PC it runs fine and knows nothing
about the ;1

I'm not using Crystal Reports anywhere, I've searched every database on the
server for sp_A% and found nothing that is anything like the sp with the
problem.

I spoke to MS support last night adn have to ring them again in a moment.
They want to charge 185 + vat for the incident when I'm trying to find out
something that isn't documented.

Warway


Quote:> <snip>

> > You got me then - have you refreshed the sps in Enterprise Manager? How
> can
> > the sp's be seen on the PCs - i.e. if not Enterprise Manager then a
custom
> > application? If so, then maybe the issue is there. If you can't see
> > sp_QuotesActive4CQS; 1 on the server then it's not there. (also check:

> > select * from sysobjects (nolock) where name like 'sp_QuotesActive%'

> > )

> > Also check/run above) in Master db as all procs beginning with sp_ can
> exist
> > in Master but can be run from any other db (providing an sp with the
same
> > name does not exist in your db).

> > Good luck!

> Ah - I need to modify this as well (thanks to Trey post). In fact you will
> only see sp_QuotesActive4CQS in EM and sysobjects, BUT if you right click
> and script Create you could get

> create proc QuotesActive4CQS ;1 as select * from master..sysservers
> GO

> create proc QuotesActive4CQS ;2 as select * from sysobjects
> GO

> as an example. But you could still

> EXEC QuotesActive4CQS; 1
> EXEC QuotesActive4CQS; 2

> Apologies for that - Crystal Reports could well be a suspect...

 
 
 

Stored procedure;1

Post by Arn0 » Wed, 03 Mar 2004 20:30:03


I did also find this newsgroup thread:

http://tinyurl.com/28cnl

where someone else had the same issue (3 years ago!)

No resolution of the problem though - but maybe some pointers?

 
 
 

1. Calling a Java Stored Procedure from another Java Stored Stored Procedure

Hi,
I'm using the stored procedure builder of DB2 UDB v6.1 on NT to create a
Java Stored Procedure to call another Java Stored Procedure. Both of
them belong to the same project in stored procedure builder.
The sp that calls another sp has the code as follows:
// Calling another java sp -- ErrorHandler
ErrorHandler err = new ErrorHandler();
err.execute("Test #1", 100, "Testing #1");
When I want to build the sp which calls another sp from within, it gave
me an error as follows:
C:\IBMVJava\ide\tools\com-ibm-db2-tools-dev-spb-ivj\spb\bld953747549650\
com\intertrac\datamart\sp\SelectBillShipAddr.java:18: Class
com.intertrac.datamart.sp.ErrorHandler not found in type declaration.
ErrorHandler err = new ErrorHandler();
Does anyone have any clue of how this can be properly done ? Please let
me know.
Thanks,
ra

Sent via Deja.com http://www.deja.com/
Before you buy.

2. Strange SQL Profiler data

3. Stored Procedure calling Stored Procedure

4. Database 'xxxx', OID yyyyy, has disappeared from pg_database

5. Executing a Stored Procedure in a Stored Procedure and selecting on the result

6. Sort Text field by Numeric Sequence

7. Call a stored procedure from another stored procedure

8. Filemaker and Windows2000

9. Calling a stored procedure with parameters from another stored procedure

10. using SQL stored procedure results in stored procedure

11. Using Resultset in Stored Procedure in another stored procedure

12. Stored Procedure using another Stored Procedure

13. Calling a Stored Procedure from a Stored Procedure