Link to home
Start Free TrialLog in
Avatar of bibi92
bibi92Flag for France

asked on

error in bcp statement

Hello

I try to build this sp :
create procedure [dbo].[bcp_out_articles]
      @dbname             varchar(30),
      @schema             varchar(30),
      @path               varchar(50) = 'C:\Temp',
      @server             varchar(50) = @@servername
AS
 
SET NOCOUNT ON
 
DECLARE @tablename             varchar(30)
DECLARE @cmdline            varchar(255)
DECLARE @sql              varchar(max)  
DECLARE @sql1             varchar(max)
DECLARE @tabcount            smallint
DECLARE @today            char(14)

exec master.dbo.sp_configure 'show advanced options', 1

Reconfigure with override

exec master.dbo.sp_configure 'xp_cmdshell', 1

Reconfigure with override

 
set @today =
 substring(convert(char(20),getdate(),20),1,4)+
       substring(convert(char(20),getdate(),20),6,2)+
       substring(convert(char(20),getdate(),20),9,2)+
       substring(convert(char(20),getdate(),20),12,2)+
       substring(convert(char(20),getdate(),20),15,2)+
       substring(convert(char(20),getdate(),20),18,2)  
 
SELECT @tabcount = 0  
 
EXEC ('USE ' + @dbname)
CREATE TABLE #dumptables (name varchar(max) )

exec('use ' + @dbname + ' insert into #dumptables select o.name from ' + @dbname + '.sys.objects o
left join sys.schemas s on s.schema_Id=o.schema_id where type=''U''
and o.name=''ACCES'' or o.name=''ACCES'' and s.name=''' + @schema + '''' )

 
DECLARE cnames       CURSOR FOR  
select [name] from #dumptables
 
OPEN cnames
 
FETCH NEXT FROM cnames INTO @tablename
WHILE (@@fetch_status <> -1)
BEGIN
      IF (@@fetch_status = -2)
      BEGIN
            FETCH NEXT FROM cnames INTO @tablename
            CONTINUE
      END
 
         
      PRINT 'Exporting table: ' + @tablename
 
      /* build commandline */
      -- Add "-S<servername>" for a remoteserver, terminator used = ~ (tilde), specify terminator after ''-t'', ''-T'' is used for trusted connection,  
      -- use -U<username> -P<password> for standard security
      SELECT @cmdline = 'bcp ' + @dbname + '.' + @schema + '.' + @tablename + ' out ' + @path + '\'+ ' TEST.dat +  -c  -t  -T -S' + @server
print @server
print @cmdline
      EXEC master..xp_cmdshell @cmdline--, NO_OUTPUT

This error message returns :
 Unknown argument 'TEST.dat' on command line.

How can I resolve this error?

Thanks

bibi
 
      SELECT @tabcount = @tabcount + 1
      FETCH NEXT FROM cnames INTO @tablename
END
 
DEALLOCATE cnames

exec master.dbo.sp_configure 'xp_cmdshell', 0

reconfigure with override

exec master.dbo.sp_configure 'show advanced options', 0

reconfigure with override

 
ASKER CERTIFIED SOLUTION
Avatar of santoshmotwani
santoshmotwani
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bibi92

ASKER

thanks bibi