Results 1 to 2 of 2

Thread: Need advice

  1. #1
    Join Date
    Sep 2002
    Posts
    218

    Need advice

    -- Downloaded off the net ---

    l'm trying to build a stored procedure that exports or imports tables from a database in bulk.l'm trying to
    build the cursor in so that l can loop through the database chosen and export the tables.l'm getting this
    error:-

    Server: Msg 156, Level 15, State 1, Procedure Sp_Export_Or_import_Table_New, Line 22
    Incorrect syntax near the keyword 'BEGIN'.
    Server: Msg 170, Level 15, State 1, Procedure Sp_Export_Or_import_Table_New, Line 43
    Line 43: Incorrect syntax near 'd'.

    How do l rectify this? Am l in doing the right thing? Please help? How do l add the export part?



    Alter Proc Sp_Export_Or_import_Table_New
    @dbName varchar(30)
    ,@tbName varchar(255)
    ,@next_tbl varchar(255)
    ,@filePath varchar(80)
    ,@cmode varchar(6)
    ,@sep char(1)
    ,@usr varchar(30)
    ,@pwd varchar(30)
    As
    Declare @cmd varchar(1000)
    Begin
    IF @cmode = 'EXPORT'
    Begin
    Exec ('Declare d Cursor For Select Name From ' + @dbname + '..sysobjects
    Where type = ' + '''u''')
    Open d
    Fetch Next From d Into @tbname
    SET NOCOUNT ON

    While ((@@FETCH_STATUS <> 1)
    BEGIN
    Set @cmd = 'bcp.exe ' +
    @dbName + '..' + @tbName + ' out ' +
    @filePath + ' -c -q -C1252 -U ' + @usr +
    ' -P ' + @pwd + ' -t' + @sep
    Print @cmd + '...'
    Exec xp_cmdShell @cmd
    Print 'BCP the ' + @tbname + ' Table'
    Fetch Next From d Into @tbname
    End
    Close d
    deallocate d

    -- IF @cmode = 'IMPORT'
    -- Begin
    -- Set @cmd = 'bcp.exe ' +
    -- @dbName + '..' + @tbName + ' in ' +
    -- @filePath + ' -c -q -C1252 -U ' + @usr +
    -- ' -P ' + @pwd + ' -t' + @sep
    -- Print @cmd + '...'
    -- Exec xp_cmdShell @cmd
    -- End
    -- End
    Go

  2. #2
    Join Date
    Nov 2002
    Location
    UK
    Posts
    1
    you've commented out an END .. each BEGIN needs a matching END ..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •