SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


monthly stored procedure to export table to text file


monthly stored procedure to export table to text file

Author
Message
markc-621514
markc-621514
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 Visits: 22
hi guys,

what i would like to do is create a stored procedure that extracts data from 2 tables "users" and "userpoints" -certain columns preferably- and export that to a text file on a monthly basis.

i've tried the following (without adding the monthly portion in yet):

Create procedure dbo.IMPEXPFROMDB
@filepath varchar(255), --filepath
@direction varchar(5) , --direction(In/Out)
@tablename varchar(255) --Valid tablename/viewname
as
begin
set nocount on
Declare @cmd varchar(1000)
Declare @dbname varchar(1000)
/* Validation for filepath */
If (@filepath is null)
begin
Raiserror('Please enter the FilePath ',16,1)
Return
end
/* Validation for Direction */
If (@direction is null) or (@direction Not In('In','Out'))
begin
Raiserror('Please enter the Direction(In/Out)',16,1)
Return
end
/* Validation for Table name */
If (@tablename is null)
begin
Raiserror('Please enter the Tablename or Viewname',16,1)
Return
end
else if (Object_id(@tablename)is null)
begin
Raiserror('Please enter a Validate Tablename or Viewname',16,1)
Return
end
--Get the database name.
set @dbname = db_name()
--bcp command.
set @cmd = 'bcp '+@dbname+'..'+@tablename+' '+@direction+' '+@filepath+' -c -S -U -P'
--To run the bcp using extended stored procedure.
exec master..xp_cmdshell @cmd
set nocount off
end

Exec IMPEXPFROMDB ‘databaseOneTest’, ‘UserReminder’, ‘OUT’, ‘C:\testOne\testzz.txt’ -c -Shomefix.techsailor.com -Uxxx -Pxxx

I received the following error:
Incorrect syntax near ' ‘ '

also, where should i add in the condition to automate this task on a monthly basis,

thanks alot'-
GilaMonster
GilaMonster
SSC Guru
SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)

Group: General Forum Members
Points: 231996 Visits: 46354
If you print the command that you're building up, what do you get?

As for monthly automation, create a SQL Agent job and set up a monthly schedule.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


markc-621514
markc-621514
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 Visits: 22
well nothing gets printed out, because of that error in syntax.
I shall add the sql agent job though. thanks.
Is there an easier way to extract columns form tables and dump them into a single text file, without using the command prompt- ?

when i do the SP below, i get more than 32 statements stating 13 rows affected followed by the error :- Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

CREATE Procedure BCP_Text_File
(
@table varchar(100),
@FileName varchar(100)
)
as
If exists(Select * from information_Schema.tables where table_name=@table)
Begin
Declare @str varchar(1000)
set @str='Exec Master..xp_Cmdshell ''bcp "Select * from '+db_name()+'..'+@table+'" queryout "'+@FileName+'" -c'''
Exec(@str)
end
else
Select 'The table '+@table+' does not exist in the database'

EXEC BCP_Text_File 'userreminder','C:\test.txt'

Your input would be greatly appreciated.
SqlUser-529296
SqlUser-529296
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1262 Visits: 754
Hi,

see the last 3 line. It will work. May be you have to delete one '.' Hehe

SET @cmd = 'bcp ' + @dbname + '.' + @tablename + ' ' + @direction + ' ' + @filepath + ' -T -c'
PRINT @cmd
--To run the bcp using extended stored procedure.
EXEC master..xp_cmdshell @cmd

---
markc-621514
markc-621514
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 Visits: 22
ok i'll try that and get back to you, having problems with the server now,
should get it fixed in a bit,

-Thanks-
GilaMonster
GilaMonster
SSC Guru
SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)

Group: General Forum Members
Points: 231996 Visits: 46354
mark.rozario (1/28/2008)
well nothing gets printed out, because of that error in syntax.


If you double click the error message (in management studio) it will take you to the line of the error.

What I was suggesting was adding print @cmd just before the exec master..xp_cmdshell so that you can see exactly what you're exec-ing. Often helps to debug.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


markc-621514
markc-621514
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 Visits: 22
Hey there, actually I am able to get the commands successfully executed not in the management studio, however, when i run the following command, I get the error saying I have passed in too many parameters:

Exec IMPEXPFROMDB 'databaseTest' 'tableNameOne', 'OUT', 'C:\testOne\testzz.txt'

I have tried removing one or the other but then prompts to enter data for that missing field will appear, i.e. "Please enter a tablename"- if i remove the tableNameOne from the list.

once again this is the parameter line required in my stored procedure:
set @cmd = 'bcp '+@dbname+'..'+@tablename+' '+@direction+' '+@filepath+' -c -T'

Am i missing out something simple?

Thanks alot,
GilaMonster
GilaMonster
SSC Guru
SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)

Group: General Forum Members
Points: 231996 Visits: 46354
Your stored proc is written to take 3 parameters and you're passing it 4.



Create procedure dbo.IMPEXPFROMDB
@filepath varchar(255), --filepath
@direction varchar(5) , --direction(In/Out)
@tablename varchar(255) --Valid tablename/viewname
as
...

Exec IMPEXPFROMDB 'databaseTest' 'tableNameOne', 'OUT', 'C:\testOne\testzz.txt'



The proc takes as a 1st parameter the file path and name, as a second parameter the direction and as a third parameter the table name.

In your call to it, you're passing first a db name (I assume), second the table name, third the direction and lastly the file name.

Based on the proc's declaration, the call to it should be
Exec IMPEXPFROMDB 'C:\testOne\testzz.txt', 'OUT', 'tableNameOne'

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


markc-621514
markc-621514
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 Visits: 22
yes it does work now however im having some remote desktop problems,
Will connect there and check if the file has been created, but the output does suggest it has been.

Thanks alot Smile
markc-621514
markc-621514
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 Visits: 22
hey yea it does work when i exec it, thanks,
im getting a bit more greedy now and would like to export more tables, with possible a break between data from each table.
excluding the break line of code, i've entered the following:

Exec IMPEXPFROMDB 'Voucher','Users','Userpoints','OUT','C:\testzz.txt'

I have changed my bcp command to:
set @cmd = 'bcp '+@dbname+'..'+@tablename1+' '+@dbname+'..'+@tablename2+' '+@dbname+'..'+@tablename3+' '+@direction+' '+@filepath+' -c -T'

i get the error: "Please enter the Direction(In/Out)"

i tried to place the out and file path after each table and duly change the bcp command but i get the error : "too many parameters entered"

any ideas, thanks
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search