Error in Stored Procedure

Question

Error in Stored Procedure

am-244616

SSCarpal Tunnel

Points: 4936
More actions
July 13, 2007 at 11:29 am

#117709

Guys,
I have stored proc where I try to generate insert - dml statements for bunch of tables. When I execute it I always get thsi error
'Msg 50000, Level 16, State 1, Procedure sp_generate_inserts, Line 169
User table or view not found.
You may see this error, if you are not the owner of this table or view. In that case use @owner parameter to specify the owner name.
Make sure you have SELECT permission on that table or view.'
But when I run the procedure - sp_generate_inserts outside the stored proc it runs fine.
EXEC DBO.SP_GENERATE_INSERTS EMPLOYEE runs fine outside the stored proc.
ALTER PROCEDURE INSERTTEST
AS
BEGIN
declare c_inserttest cursor for
select tab from meta_info
where system_table = 1
declare
@tab nvarchar(80),
@tab_prev nvarchar(80),
@fetchcount int,
@sql nvarchar(200)
set@tab = ''
set@tab_prev = ''
set@fetchcount = 0
open c_inserttest
fetch next from c_inserttest into @tab
while @@fetch_status <> -1
begin
set @sql = N'BEGIN DECLARE @tab NVARCHAR(80)
EXEC PROCEDURE DBO.SP_GENERATE_INSERTS @tab END'
exec sp_executesql @sql
set @fetchcount=@fetchcount+1
end
close c_inserttest
deallocate c_inserttest
end
Any suggestions and inputs would help.
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SQLBill SSC Guru Points: 51440 More actions · Answer 1

How are you running the stored procedure? Are you running it via a job? If you are running it as a job, then it runs as the service login assigned to SQLServerAgent service. Then when you run just that specific part, you are running it with the login you are logged in as.

-SQLBill

am-244616 SSCarpal Tunnel Points: 4936 More actions · Answer 2

I am running the inner stored proc and outer stored proc with the same user - sa.

Is there anythng I am missing?

Thanks

Sergiy SSC Guru Points: 110208 More actions · Answer 3

You posted procedure INSERTTEST, but your error is in procedure sp_generate_inserts.

It's quite tricky to help you fixind something no one have seen.

_____________
Code for TallyGenerator

steve smith-401573 SSChampion Points: 10889 More actions · Answer 4

This may be a stupid question, but if you are using @tab to return values, why haven't you declared it as OUTPUT? Are you running this in 7 or 2000?

Jez-448386 SSCarpal Tunnel Points: 4594 More actions · Answer 5

I think that the problem is when you create the string for @sql. Put a print statement before the exec sp_executesql @sql line and see what the string is.

I think that the value of @sql should be :

set @sql = N'EXEC PROCEDURE DBO.SP_GENERATE_INSERTS ' + @TAB

J

PS. There might be a more efficient way of doing this if we knew what sp_generate_inserts does.