Truncating table using linked server

• cutespn

  SSCommitted

  Points: 1669

  More actions

  August 24, 2004 at 7:54 am

  #162139

  Hi,

  I am trying to truncate table which is in another database, i m accessing it thru linked server.

  truncate table MyServer.MyStage.dbo.MyMemberStage

  after this i get an error

  The object name 'MyServer.MyStage.dbo.' contains more than the maximum number of prefixes. The maximum is 2.

   

  TIA

  Sudheer

   

   

  
  
  
  
  
  

  Regards,
  Sudheer 
  

  
  

  

• Razvan Socol

  SSCarpal Tunnel

  Points: 4811

  More actions

  August 24, 2004 at 9:13 am

  #520342

  Try the following (not tested):

  SELECT * FROM OPENQUERY (MyServer, 'TRUNCATE TABLE MyStage.dbo.MyMemberStage SELECT ''Done''')

  Razvan

• Chris Stamey

  SSCrazy

  Points: 2695

  More actions

  August 25, 2004 at 9:05 am

  #520543

  I wanted to do something similar to this myself. I could not get it to work and was finally told by someone from MS (at least they said they were), that Linked Servers do not support anything but Select, Update, Insert, and Delete. It does not support other TSQL commands.

  I do not know whether the solution from the previous poster will work for you, but I know it would not work for me.

  Chris

• joemai

  Hall of Fame

  Points: 3496

  More actions

  August 31, 2004 at 12:46 pm

  #521270

  I use this way:

  1. Link the server to your local machine:

           USE master

           GO

           EXEC sp_addlinkedserver

          'sqlserver', /*Name of the linked server*/

          N'SQL Server'

   

  2. run select to make sure your server is linked     

           Use master

           select * from sysservers

  3.Use sp_executesql

  EXECUTE  linkedserver.master.dbo.sp_executesql

            N'TRUNCATE table databasename..test'

   

  Hope this help.

  Minh

• Aaron Templeton

  SSCarpal Tunnel

  Points: 4655

  More actions

  August 31, 2004 at 3:17 pm

  #521278

  I can't help it.....

  That was pure genius. Very nice.

• rafaelbahialde

  Newbie

  Points: 7

  More actions

  July 10, 2019 at 8:55 pm

  #3661739

  Just in case any of you are wondering how to do this while connecting to Sybase ASE via Linked Server, here is how:

  SELECT * FROM OPENQUERY (<linkedserver>, 'select top 1 * from <database>.<schema>.<table> where 1=2; TRUNCATE TABLE <database>.<schema>.<table>' )

  This has been successfully tested against Sybase ASE 16 SP3 PL06 and SQL Server 2012. Should work for other RDBMS as well.