Truncating data on a remote Server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Truncating data on a remote Server

Author

Topic

imransi17
Starting Member

12 Posts

Posted - 2013-07-15 : 13:43:34

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

352 Posts

Posted - 2013-07-15 : 14:26:57

I had to use DELETE as I could not get TRUNCATE to work.

djj

imransi17
Starting Member

12 Posts

Posted - 2013-07-15 : 14:49:22

Delete will take forever for 10 Million rows not to mention Transaction log file...

please find a solution ;(

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-07-15 : 15:17:38

change

exec xp_cmdshell @cmd

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'

imransi17
Starting Member

12 Posts

Posted - 2013-07-15 : 16:10:25

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-16 : 02:13:44

exec (@cmd)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

imransi17
Starting Member

12 Posts

Posted - 2013-07-16 : 11:18:53

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'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-16 : 13:40:10

do a
PRINT(@cmd)
and post the result

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

imransi17
Starting Member

12 Posts

Posted - 2013-07-16 : 14:57:43

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

mandm
Posting Yak Master

120 Posts

Posted - 2013-07-16 : 17:36:55

You just need to change the variable declaraton of @cmd to NVARCHAR.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-17 : 01:45:47

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

imransi17
Starting Member

12 Posts

Posted - 2013-07-17 : 13:56:17

Visakh16: Your solution worked. Thank you so very much :)

THANK YOU !

imransi17
Starting Member

12 Posts

Posted - 2013-07-17 : 13:57:09

Thank you all of you guys / gals as well to assist :)

Cheers,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources