January 27, 2008 at 10:59 pm
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'-
January 28, 2008 at 12:59 am
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
January 28, 2008 at 1:19 am
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.
January 28, 2008 at 1:57 am
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
---
January 28, 2008 at 2:39 am
ok i'll try that and get back to you, having problems with the server now,
should get it fixed in a bit,
-Thanks-
January 28, 2008 at 5:37 am
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
January 28, 2008 at 8:12 pm
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,
January 29, 2008 at 12:40 am
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
January 29, 2008 at 12:56 am
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 🙂
January 29, 2008 at 8:41 pm
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
January 29, 2008 at 11:15 pm
If you check the parameters for bcp in books online, you'll note that it takes a single table. If you want to export multiple tables, run bcp multiple times.
Books Online
bcp {[[database_name.][owner].]{table_name | view_name} | "query"}
{in | out | queryout | format} data_file
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
September 20, 2008 at 3:33 pm
Hey, how did you resolve the error message - Msg 170 , even I am facing the same prolem, Incorrect syntax near ' ' '.
And one more thing, so for this proc the way to execute it is :
Exec Procname 'DBName', 'Tablename','Direction','Filepath'
please guide me
Thanks
September 20, 2008 at 7:38 pm
Heh... Dude! I thought you said your buddy had it whipped.... 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply