problems with a script, could not find stored procedure

Hi all:
I am new to SQL and I am having a hard time trying to figure out the
following error:
Msg 2812, Level 16, State 62, Server PANCHO, Line 10
Could not find stored procedure 'deletej'.
My code is as follows:
*****file name: jpak.sq
use spjdatabase;
drop procedure insertj;
drop procedure deletej;
create procedure insertj @.jnum varchar(5), @.jname varchar(20), @.jcity
varchar(20), @.jInsert integer output as declare @.jInSupp integer;
select @.jInSupp = count (*) from J where J# = @.jnum;
if (@.jInSupp = 1)
begin
set @.jInsert = -1;
return 0;
end;
else return -1;
insert into J values(@.jnum, @.jname, @.jcity);
set @.jInsert = 0;
create procedure deletej @.jnum varchar(5), @.jDelete integer output as
declare @.jDelSupp integer;
select @.jDelSupp = count (*) from J where J# = @.jnum;
if (@.jDelSupp = 1)
begin
set @.jDelete = -1;
return 0;
end;
else return -1;
delete from J where J# = @.jnum;
set @.jDelete = 0;
go
*****file name: invokejpak.sql
use spjdatabase;
declare @.eInsert integer;
declare @.eDelete integer;
execute insertj 'J8', 'Wrench', 'Miami', @.eInsert output;
if @.eInsert = -1
print 'Insert Rejected' else
print 'Insert Accepted';
execute deletej 'J8', @.eDelete output;
if @.eDelete = -1
print 'Delete Rejected' else
print 'Delete Accepted';
go
*****file name: go.bat
osql -n -E -i invokejpak.sql
I run this by going to the command prompt and navigating to the
directory whwre I have these files, then just type go.exe. Any help will
be greatly appreciated.
FC
Hi
After a USE database you need to issue a GO statement.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"FC" wrote:

> Hi all:
> I am new to SQL and I am having a hard time trying to figure out the
> following error:
> Msg 2812, Level 16, State 62, Server PANCHO, Line 10
> Could not find stored procedure 'deletej'.
> My code is as follows:
> *****file name: jpak.sq
> use spjdatabase;
> drop procedure insertj;
> drop procedure deletej;
> create procedure insertj @.jnum varchar(5), @.jname varchar(20), @.jcity
> varchar(20), @.jInsert integer output as declare @.jInSupp integer;
> select @.jInSupp = count (*) from J where J# = @.jnum;
> if (@.jInSupp = 1)
> begin
> set @.jInsert = -1;
> return 0;
> end;
> else return -1;
> insert into J values(@.jnum, @.jname, @.jcity);
> set @.jInsert = 0;
> create procedure deletej @.jnum varchar(5), @.jDelete integer output as
> declare @.jDelSupp integer;
> select @.jDelSupp = count (*) from J where J# = @.jnum;
> if (@.jDelSupp = 1)
> begin
> set @.jDelete = -1;
> return 0;
> end;
> else return -1;
> delete from J where J# = @.jnum;
> set @.jDelete = 0;
> go
> *****file name: invokejpak.sql
> use spjdatabase;
> declare @.eInsert integer;
> declare @.eDelete integer;
> execute insertj 'J8', 'Wrench', 'Miami', @.eInsert output;
> if @.eInsert = -1
> print 'Insert Rejected' else
> print 'Insert Accepted';
> execute deletej 'J8', @.eDelete output;
> if @.eDelete = -1
> print 'Delete Rejected' else
> print 'Delete Accepted';
> go
> *****file name: go.bat
> osql -n -E -i invokejpak.sql
> I run this by going to the command prompt and navigating to the
> directory whwre I have these files, then just type go.exe. Any help will
> be greatly appreciated.
> FC
>
|||hi,
FC wrote:
> Hi all:
> I am new to SQL and I am having a hard time trying to figure out the
> following error:
> Msg 2812, Level 16, State 62, Server PANCHO, Line 10
> Could not find stored procedure 'deletej'.
> ....
> osql -n -E -i invokejpak.sql
> I run this by going to the command prompt and navigating to the
> directory whwre I have these files, then just type go.exe. Any help
> will be greatly appreciated.
>
it can even depends on unresolved object owner...
suppose you executed the "file name: jpak.sq", that creates the [insertj]
and [deletej] procedures with a ddl_admin member... as both procedures are
not qualified with a specific owner name they will be owned by the creator
user, say [L_ddl]... and their full qualified name actually is
[L_ddl].[insertj] and [L_ddl].[deletej]..
then you log in
> osql -n -E -i invokejpak.sql
using WinNT authentication as another user... say (for simplicity I used a
standard SQL Server login associated to) [L_user] user...
executing the script the way you are doing, it try to resolve [insertj] and
[deletej] objects as owned by [L_user] and, if no match is present (and of
course no one will) as owned by [dbo]... here again no match is found and
executing the script will result in something like
Server: Msg 2812, Level 16, State 62, Line 4
Could not find stored procedure 'insertj'.
Insert Accepted
Server: Msg 2812, Level 16, State 62, Line 9
Could not find stored procedure 'deletej'.
Delete Accepted
this way you should learn you always have to reference objects including the
corresponding owner and create them specifying it like
CREATE PROCEDURE dbo.procedure
.....
EXECUTE dbo.insertj 'J8', 'Wrench', 'Miami', @.eInsert output
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||I was having that issue also. Not sure if you figured it out. What solved it for me was changing the our connection
provider from Microsoft OLE DB Provider for ODBC Drivers to be Microsoft OLE DB Provider for SQL Server. Hope that helps.