Results 1 to 5 of 5
  1. #1
    Join Date
    May 2013
    Posts
    7

    Unanswered: Truncating data on a remote Server

    Hello,

    I am trying to truncate data from one server to the other (remote server)

    it works from query analyzer like this:

    exec RemoteServerName.DatabaseName.DBO.sp_executesql "truncate table DBO.tablename"

    it does NOT work from query analyzer like this:

    Declare @cmd varchar(2000)
    set @cmd = @servername + '.' + @databasename + '_Archive'+ '.' + @schema + '.' + 'sp_executesql' + ' ' + '"' +'truncate table' + ' ' + @schema + '.' + @tablename + '"'
    exec xp_cmdshell @cmd

    Produced this error:

    'RemoteServerName.DatabaseName.DBO.sp_executesql' is not recognized as an internal or external command,
    operable program or batch file.

    Please Help !

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Look up "BCP command".
    Last edited by Wim; 07-22-13 at 09:16.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is "air code", but I think you want:
    Code:
    Declare @cmd varchar(2000)
     set @cmd = @servername + '.' + @databasename + '_Archive'+ '.' + @schema + '.' + 'sp_executesql' + ' ' + '"' +'truncate table' + ' ' + @schema + '.' + @tablename + '"'
     execute (@cmd)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by imransi17 View Post
    Hello,

    I am trying to truncate data from one server to the other (remote server)

    it works from query analyzer like this:

    exec RemoteServerName.DatabaseName.DBO.sp_executesql "truncate table DBO.tablename"

    it does NOT work from query analyzer like this:

    Declare @cmd varchar(2000)
    set @cmd = @servername + '.' + @databasename + '_Archive'+ '.' + @schema + '.' + 'sp_executesql' + ' ' + '"' +'truncate table' + ' ' + @schema + '.' + @tablename + '"'
    exec xp_cmdshell @cmd

    Produced this error:

    'RemoteServerName.DatabaseName.DBO.sp_executesql' is not recognized as an internal or external command,
    operable program or batch file.

    Please Help !
    Try this code, it works for me:

    DECLARE @sqlcmd VARCHAR(200)
    DECLARE @servername VARCHAR(200)
    DECLARE @dbname VARCHAR(200)
    SET @servername = 'NAME OF SERVER'
    SET @dbname = 'database.dbo.table]'

    SET NOCOUNT ON
    SET @sqlcmd = 'SQLCMD -S'+@servername+' -Q "SET NOCOUNT ON; TRUNCATE TABLE ' + @dbname + '; SET NOCOUNT OFF; " -s"," -w 700'
    EXEC master..xp_cmdshell @sqlcmd

    PRINT @sqlcmd

  5. #5
    Join Date
    May 2013
    Posts
    7
    Thank you all of you guys and gals to assist. I finally got the solution to my above original issue.

    Solution:

    Declare @cmd varchar(2000)
    set @cmd = 'EXEC ' + @servername + '.' + @databasename + '_Archive'+ '.' + @schema + '.' + 'sp_executesql @statement= N''truncate table ' + @schema + '.' + @tablename + ''''
    exec (@cmd)


    Again Thank you all,

    Cheers,

Tags for this Thread

Posting Permissions

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