Stored proc for bcp out

Ok.. Seems like you started off with this procedure.
Fixed the syntax mistakes and revert out with results.. ( Can't test as I don't have access to server now)

CREATE PROCEDURE usp_BCP_out_AllTables
      @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 @ssql                   varchar(255)
DECLARE @tabcount            smallint
DECLARE @today            char(14)

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(255))
set @ssql = 'insert into #dumptables SELECT [name] from ' + @dbname + '.' + @schema + '.' + 'sysobjects where type = ''U'' and name <>  "dtproperties" '
exec (@ssql)

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 + '\' + @tablename + @today +'.dat -c  -t  -T -S' + @server
print @server
print @cmdline
      EXEC master..xp_cmdshell @cmdline--, NO_OUTPUT

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

DEALLOCATE cnames

/* Print usermessage */
SELECT CONVERT(varchar(10),@tabcount) + ' tables from database '+ @dbname + ' exported to ' + @path
GO

Select allOpen in new window

Hello,

Thanks the error message is returned :
Could not find server 'hacor' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

hacor is the db name

Thanks

Bibi

Can you post what exactly you have used now? I cannot find a mistake in the last post.

Hello,

I try to bcp out the data of tables for one schema :

CREATE procedure usp_BCP_out_AllTables
      @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 @ssql                   varchar(255)
DECLARE @tabcount            smallint
DECLARE @today            char(14)
 
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(255))
set @ssql = 'insert into #dumptables SELECT [name] from ' + @dbname + '.' + @schema + '.' + 'sys.objects o left join sys.schemas s
on s.schema_id = o.schema_id
where type = ''U'' and name <>  "dtproperties   and s.name= @Schema'

   
exec (@ssql)
 
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 + '\' + @tablename + @today +'.dat -c  -t  -T -S' + @server
print @server
print @cmdline
      EXEC master..xp_cmdshell @cmdline--, NO_OUTPUT
 
      SELECT @tabcount = @tabcount + 1
      FETCH NEXT FROM cnames INTO @tablename
END
 
DEALLOCATE cnames
 
/* Print usermessage */
SELECT CONVERT(varchar(10),@tabcount) + ' tables from database '+ @dbname + ' exported to ' + @path
GO

I have this error :

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'usp_BCP_out_AllTables'.
Msg 137, Level 15, State 2, Line 26
Must declare the scalar variable "@dbname".
Msg 137, Level 15, State 2, Line 29
Must declare the scalar variable "@dbname".
Msg 137, Level 15, State 2, Line 56
Must declare the scalar variable "@dbname".
Msg 137, Level 15, State 2, Line 57
Must declare the scalar variable "@server".
Msg 137, Level 15, State 2, Line 68
Must declare the scalar variable "@dbname".

Thanks

Regards

bibi

Sorry, but the procedure compiles flawlessly with my MSSQL 2005.

Yes, the procedure compiles flawlessly in 2005, but when I execute it
USE [master]
GO

DECLARE      @return_value int

EXEC      @return_value = [dbo].[usp_BCP_out_AllTables]
            @dbname = N'hcor',
            @schema = N'ABEL',
            @path = N'c:\temp'

                                           
SELECT      'Return Value' = @return_value

GO

Could not find server 'hcor' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

Maybe, I have forgotten something.

Thanks

bibi

The error is in this line:

set @ssql = 'insert into #dumptables SELECT [name] from ' + @dbname +  '.' + @schema + '.' + 'sys.objects o left join sys.schemas s
on  s.schema_id = o.schema_id
where type = ''U'' and name <>   "dtproperties   and s.name= @Schema'

I have not been able to see it before, but you use a "3 dot" specifier here:   @dbname.@schema.sys.objects. A name with three dots has the meaning of  @server.@dbname.@schema.@tablename. You need to omit the schema name, SYS is the schema here:

set @ssql = 'insert into #dumptables SELECT [name] from ' +  @dbname +  '.'  + 'sys.objects o left join sys.schemas s
on   s.schema_id = o.schema_id
where type = ''U'' and name <>    "dtproperties   and s.name= @Schema'
 

I have modified @ssql and the stored proc compiles but I have another error

set @ssql = 'insert into #dumptables SELECT [name] from ' +  @dbname +  '.'  + 'sys.objects o left join sys.schemas s
on   s.schema_id = o.schema_id
where type = ''U'' and name <>    "dtproperties"   and s.name= @schema'

Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@schema".

(1 row(s) affected)

Thanks a lot

bibi

Sorry, why can't I use my eyes? The var @schema needs to be outside of the quotes, of course ... (I have removed the double quotes around dtproperties, but they are not wrong, only unnecessary here):

set @ssql =
'insert into #dumptables SELECT [name] from ' +  @dbname +   '.'  + 'sys.objects o
left join sys.schemas s
on   s.schema_id =  o.schema_id
where type = ''U'' and name <>   dtproperties    and s.name= ''' + @schema + ''''

Hello,

Thanks I have modified because there is an error on dtproperties :

set @ssql =
'insert into #dumptables SELECT [name] from ' +  @dbname +   '.'  + 'sys.objects o
left join sys.schemas s
on   s.schema_id =  o.schema_id
where type = ''U'' and name <>   "dtproperties"    and s.name= ''' + @schema + ''''

This returns another error :
Msg 209, Level 16, State 1, Line 4
Ambiguous column name 'name'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'dtproperties'.
Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'name'.

Thanks for your help

Regards

bibi

I have modified by this :

'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 not like ''sys%''    and s.name= ''' + @schema + ''''

Regards

bibi

Ok, thanks a lot. I 'm testing this storec proc with big tables.

Thanks a lot