Results to txt file
-
Guys
I have below snippet of dynamic sql which I run to generate insert statements for bunch of lookup tables. How do I modify the dynamic sql to write the results insert statements to a txt file.
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
-
One way would be to send the results to yourself as text file. First create a stored procedure for your query. Then run
EXEC master.dbo.xp_sendmail
@recipients = 'youremailaddress'
, @subject = 'Whatever'
, @message = 'Whatever'
, @query = 'EXEC [name of your sproc]'
, @attach_results = 'true'
, @attachments = 'whatever.txt'
you could also create this statment into a sproc and then run it at will
-
I don't know what "SP_Generate_Inserts" does... but there's a couple of things I see wrong here...
First, it's a very bad practice to name your procs starting with SP_... those should be reserved for system stored procedures that live in Master or MSDB. Besides, there's no real need for any type of Hungarian notation in SQL save the possible exceptions of functions and views just to discriminate them from tables. Everything else is pretty obvious.
Second, I suspect the only reason why you're using a cursor is because "SP_Generate_Inserts" was written is such a fashion as to accept only 1 row at a time. Cursors are bad for performance and so are single row procs when you need to handle multiple rows.
My recommendation is to rewrite the whole thing so that it uses good solid setbased logic... then, creeating a text file from the result SET would be a piece of cake using BCP, OSQL, or any of the other wonderful set based tools available.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
David, Jeff, bledu
Thank you for your reply.
I have named as stored proc as SP_ since I created it as system stored procedure in master database. We need to use this stored proc quite often to generate insert scripts for tables in our database.
The reason for using dynamic sql is the same logic doesnt work from stored procedure. If I am able to create below stored proc it would be easy for me to run the same through bcp, osql.
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 DBO.SP_GENERATE_INSERTS @tab END'
exec sp_executesql @sql
set @fetchcount=@fetchcount+1
end
close c_inserttest
deallocate c_inserttest
end
When I execute the stored proc I get the following 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.'
Any suggestions/inputs would help.
Thanks
-
At a guess, I would say that as sp_generate_inserts is in the master database, it is looking in there (i.e. master) for a table.
You should qualify the table name with the database name: <database>.<owner>.<tablename>
J
-
I'm thinking you need to post the SP_Generate_Inserts proc... let us see what we can do to make it less RBAR dependent...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply