Error in Stored Proc - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Error in Stored Proc

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2007-07-13 : 13:26:38

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

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-13 : 14:16:01

(1) Not sure if you really need a CURSOR or you can do without.
(2) Why do you need dynamic sql here:


begin 
	Exec PROCEDURE DBO.SP_GENERATE_INSERTS @TAB
	set @fetchcount=@fetchcount+1
end

(3) Also, you need to be initializing the value of @Tab to '' inside the cursor loop, not outside. Otherwise there is a chance of a value from previous loop getting carried over.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2007-07-13 : 14:33:44

Thanks for your reply I still get the same error even after making the suggested changes

Any ideas, inputs

Thanks

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-13 : 14:43:58

Try this:



declare @tab nvarchar(80),
	@tab_prev nvarchar(80),
	@fetchcount int,
	@sql nvarchar(200)

Set @fetchcount = 0

DECLARE rs CURSOR 
LOCAL
FORWARD_ONLY
OPTIMISTIC
TYPE_WARNING
FOR SELECT tab FROM meta_info WHERE system_table = 1
OPEN rs
fetch next from rs into @Tab 
WHILE ( @@FETCH_STATUS = 0 )
	begin
		IF (@tab IS NOT NULL And DataLength(@tab) > 0 )
			EXEC PROCEDURE DBO.SP_GENERATE_INSERTS @TAB
			
		Set @tab = ''
		Set @fetchcount=@fetchcount+1
FETCH NEXT FROM rs INTO @tab 
END
		
CLOSE rs
DEALLOCATE rs

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2007-07-13 : 15:39:47

Thanks Dinaker this works. But the thing though is how can I write the generate scripts from the stored Proc - the result set to .txt or .sql file. Will dynamic sql allow bcp code.

Any suggestions and inputs would help

Thanks

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-13 : 17:01:11

what does your sp_Generate_Inserts do?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2007-07-13 : 17:10:00

SP_GENERATE_INSERTS procedure generates insert statements for all the rows in the table.

So when I execute exec SP_GENERATE_INSERTS employee, it gives me insert statements for all rows in the table employee.

Hence I need to store the results from the dynamice sql (which executes the stored proc for bunch of tables) into .txt and .sql which an app can
run.

Is there any to print the results into a file.

Thanks

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-13 : 17:12:35

If you need to generate scripts, fire up profiler, right click on DB in Enterprise Mgr, or Mgmt Studio, All Tasks-> Generate Scripts. See what SQL Server is doing and you can pretty much replicate the same.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources