bibi92
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
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)
substring(convert(char(20)
substring(convert(char(20)
substring(convert(char(20)
substring(convert(char(20)
substring(convert(char(20)
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER