Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

monthly stored procedure to export table to text file Expand / Collapse
Author
Message
Posted Sunday, January 27, 2008 10:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 30, 2008 8:52 PM
Points: 6, 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'-
Post #448135
Posted Monday, January 28, 2008 12:59 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:00 PM
Points: 42,436, Visits: 35,490
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 2008, MVP
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

Post #448141
Posted Monday, January 28, 2008 1:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 30, 2008 8:52 PM
Points: 6, 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.

Post #448151
Posted Monday, January 28, 2008 1:57 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, October 14, 2009 1:56 AM
Points: 438, Visits: 754
Hi,

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

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

---
Post #448160
Posted Monday, January 28, 2008 2:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 30, 2008 8:52 PM
Points: 6, 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-
Post #448165
Posted Monday, January 28, 2008 5:36 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:00 PM
Points: 42,436, Visits: 35,490
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 2008, MVP
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

Post #448239
Posted Monday, January 28, 2008 8:12 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 30, 2008 8:52 PM
Points: 6, 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,
Post #448667
Posted Tuesday, January 29, 2008 12:40 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:00 PM
Points: 42,436, Visits: 35,490
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 2008, MVP
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

Post #448709
Posted Tuesday, January 29, 2008 12:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 30, 2008 8:52 PM
Points: 6, 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 :)
Post #448713
Posted Tuesday, January 29, 2008 8:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 30, 2008 8:52 PM
Points: 6, 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
Post #449162
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse