Truncating data on a remote Server

Question

Truncating data on a remote Server

imransi17

Mr or Mrs. 500

Points: 531
More actions
July 15, 2013 at 11:29 am

#280165

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 !

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

That's because xp_cmdshell expects an MS-DOS command and you're sending a T-SQL command.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

imransi17 Mr or Mrs. 500 Points: 531 More actions · Answer 2

so how would i truncate data in my scenario through T-SQL command? Please...

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Just as you posted, no need to call xp_cmdshell.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

imransi17 Mr or Mrs. 500 Points: 531 More actions · Answer 4

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

i tried double quotes and single quote and same error above.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

I just tried it and it worked fine.

I can't replicate your error either for a missing linked server or a misdirection for the database. Maybe someone else can help you.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 6

If I understand correctly what you want:

DECLARE @sp_executesql nvarchar(1000)

DECLARE @sql nvarchar(MAX)

SELECT @sp_executesql =

quotename(@server) + '.' + quotename(@db) + '.sys.sp_executesql'

SELECT @sql = 'TRUNCATE TABLE ' + quotename(@schema) + '.' + quotename(@tablename)

EXEC @sp_executesql @sql

That is, one variable to define sp_executesql and one for the command itself.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

imransi17 Mr or Mrs. 500 Points: 531 More actions · Answer 7

Thank you for the try but it gave me the following error

similar to the on earlier...

The name RemoteServerName.DatabaseName.DBO.sys.sp_executesql is not a valid identifier

Please advise.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

You might get some mileage out of this:

DECLARE @SQLTruncate VARCHAR(200)

SET @SQLTruncate = 'TRUNCATE TABLE ' + @schema + '.' + @tablename

EXECUTE(@SQLTruncate) AT LinkedServerName -- no quotes around linkedservername

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Erin Ramsay SSCrazy Eights Points: 9728 More actions · Answer 9

Hey Chris, does SQL Server consider that type of command a pass-through?

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 10

imransi17 (2013-07-16)
Thank you for the try but it gave me the following error
similar to the on earlier...
The name RemoteServerName.DatabaseName.DBO.sys.sp_executesql is not a valid identifier

To whom was that in reply to? And can you post the exact code you used?

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

@Erin, yes, and it's not limited to a single statement. One such pass-thru command in the system I'm currently working on contains several INSERT and UPDATE statements protected by a manual transaction.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

imransi17 Mr or Mrs. 500 Points: 531 More actions · Answer 12

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,

Truncating data on a remote Server

Cookies on SQLServerCentral