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
bibi92Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Can you post what exactly you have used now? I cannot find a mistake in the last post.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, 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
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
That won't ignore dtproperties. I don't know if you want/have to exclude it.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bibi92Author Commented:
Ok, thanks a lot. I 'm testing this storec proc with big tables.
0
bibi92Author Commented:
Thanks a lot
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.