SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Truncating data on a remote Server
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

imransi17
Starting Member

12 Posts

Posted - 07/15/2013 :  13:43:34  Show Profile  Reply with Quote
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 !

djj55
Constraint Violating Yak Guru

USA
308 Posts

Posted - 07/15/2013 :  14:26:57  Show Profile  Reply with Quote
I had to use DELETE as I could not get TRUNCATE to work.

djj
Go to Top of Page

imransi17
Starting Member

12 Posts

Posted - 07/15/2013 :  14:49:22  Show Profile  Reply with Quote
Delete will take forever for 10 Million rows not to mention Transaction log file...

please find a solution ;(
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4612 Posts

Posted - 07/15/2013 :  15:17:38  Show Profile  Reply with Quote
change
exec xp_cmdshell @cmd
to
exec @cmd


Although it looks like it works, double quotes (") are used for quoted-identifiers not for string delemiters. So you should change to sinble quoes (') and preface it with an N for unicode:
exec RemoteServerName.DatabaseName.DBO.sp_executesql N'truncate table DBO.tablename'
Go to Top of Page

imransi17
Starting Member

12 Posts

Posted - 07/15/2013 :  16:10:25  Show Profile  Reply with Quote
I ran exactly you mentioned and got this error

The RemoteServerName.DatabaseName.DBO.sp_executesql N'truncate table DBO.tablename' is not a valid identifier
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/16/2013 :  02:13:44  Show Profile  Reply with Quote
exec (@cmd)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

imransi17
Starting Member

12 Posts

Posted - 07/16/2013 :  11:18:53  Show Profile  Reply with Quote
Thanks visakh16... I got this error after i tried what you suggested.

Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/16/2013 :  13:40:10  Show Profile  Reply with Quote
do a
PRINT(@cmd)
and post the result

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

imransi17
Starting Member

12 Posts

Posted - 07/16/2013 :  14:57:43  Show Profile  Reply with Quote
This is the Print (@cmd) output
RemoteServerName.DatabaseName.DBO.sp_executesql 'truncate table DBO.tablename'
Go to Top of Page

mandm
Yak Posting Veteran

53 Posts

Posted - 07/16/2013 :  17:36:55  Show Profile  Reply with Quote
You just need to change the variable declaraton of @cmd to NVARCHAR.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/17/2013 :  01:45:47  Show Profile  Reply with Quote
quote:
Originally posted by imransi17

This is the Print (@cmd) output
RemoteServerName.DatabaseName.DBO.sp_executesql 'truncate table DBO.tablename'


where's the exec?
try like

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

imransi17
Starting Member

12 Posts

Posted - 07/17/2013 :  13:56:17  Show Profile  Reply with Quote
Visakh16: Your solution worked. Thank you so very much :)

THANK YOU !
Go to Top of Page

imransi17
Starting Member

12 Posts

Posted - 07/17/2013 :  13:57:09  Show Profile  Reply with Quote
Thank you all of you guys / gals as well to assist :)

Cheers,
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000