Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1267
  • Last Modified:

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))

0
Netstore
Asked:
Netstore
2 Solutions
 
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
EXEC sp_addlinkedsrvlogin 'LinkedServerName', 'false', 'sa', 'RemoteUserName', 'RemoteUserPassword'

0
 
rw3adminCommented:
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now