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

 
Microsoft SQL Server

Avatar of undefined
Last Comment
bibi92

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
santoshmotwani

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
bibi92

thanks bibi
Your help has saved me hundreds of hours of internet surfing.
fblack61