Truncating table using linked server

  • cutespn

    SSCommitted

    Points: 1669

    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 

    My Blog

  • Razvan Socol

    SSCarpal Tunnel

    Points: 4738

    Try the following (not tested):

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

    Razvan

  • Chris Stamey

    SSCrazy

    Points: 2585

    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

    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: 4595

    I can't help it.....

    That was pure genius. Very nice.

  • rafaelbahialde

    Newbie

    Points: 7

    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.

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

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