[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 483
  • Last Modified:

Stored proc for bcp out

Hello,

I try to modify this stored proc with @schema

-- BCP out all user tables
-- Author       G. Hanson
-- Date Added   02/23/2005
-- Last Changed mm/dd/yyyy
--
-- Syntax      usp_BCP_out_AllTables 'database','path for datafiles','sql server name'
--
-- Notes      all data output files are tablenameyyyymmddhhss.dat
-- Change History

CREATE PROCEDURE usp_BCP_out_AllTables
      @dbname             varchar(30),@schema            varchar(30),
      @path                  varchar(50) = "C:\Temp",
             @server                  varchar(50) = "sql server name"
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 + ''.'' + @schemaname + ''.'' + @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 156, Level 15, State 1, Procedure usp_BCP_out_AllTables, Line 38
Incorrect syntax near the keyword 'where'.
Msg 102, Level 15, State 1, Procedure usp_BCP_out_AllTables, Line 61
Incorrect syntax near ''.

Maybe do you have a solutions?

Thanks

bibi
0
bibi92
Asked:
bibi92
  • 8
  • 5
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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

Open in new window

0
 
bibi92Author Commented:
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
0
 
QlemoDeveloperCommented:
Can you post what exactly you have used now? I cannot find a mistake in the last post.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
bibi92Author Commented:
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
0
 
QlemoDeveloperCommented:
Sorry, but the procedure compiles flawlessly with my MSSQL 2005.
0
 
bibi92Author Commented:
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


0
 
QlemoDeveloperCommented:
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'
 
0
 
bibi92Author Commented:
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
0
 
QlemoDeveloperCommented:
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 + ''''
0
 
bibi92Author Commented:
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
0
 
bibi92Author Commented:
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
0
 
QlemoDeveloperCommented:
That won't ignore dtproperties. I don't know if you want/have to exclude it.
0
 
bibi92Author Commented:
Ok, thanks a lot. I 'm testing this storec proc with big tables.
0
 
bibi92Author Commented:
Thanks a lot
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 8
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now