SQL truncate table on linked server


I have the following command

EXECUTE  XXXXXXX.master.dbo.sp_executesql N'TRUNCATE table TEST_RECOVERY.dbo.table1'

that works fine in QA but when added to a DTS package to clear down a table on a linked server I get the following:

Could not connect to server 'xxxxxx' because 'sa' is not defined as a remote login at the server.

The source and target sa pwords match up any idea's?? (Yup I know its bad practice to use sa but give me a break until I get it working 8))

LVL 5
NetstoreAsked:
Who is Participating?
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
EXEC sp_addlinkedsrvlogin 'LinkedServerName', 'false', 'sa', 'RemoteUserName', 'RemoteUserPassword'

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
::Could not connect to server 'xxxxxx' because 'sa' is not defined as a remote login at the server.

configure the linked server to make the local sa account to the remote sa account.
in that configuration, the passwords don't need to match any longer
0
 
NetstoreAuthor Commented:
angelII apologies for being an idiot could you give me instructions on how to do that please? Cheers
0
 
rw3adminConnect With a Mentor Commented:
Why are you using linked server inside a DTS package to begin with?, thats an added layer of complexity.

create a brand new database connection in DTS package for your linked server and then use that in your execute SQL task, you dont even have to put
"EXECUTE  XXXXXXX.master.dbo.sp_executesql N'TRUNCATE table TEST_RECOVERY.dbo.table1'"


you can then just do

TRUNCATE table TEST_RECOVERY.dbo.table1
0
 
NetstoreAuthor Commented:
rw3 thats bang on (What was I thinking...couldn't see the wood for the trees)

anne thanks also for the effor as your solution works as well
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.