SQL Jobs that invoke jobs in another server

Question

Post reply

SQL Jobs that invoke jobs in another server

kanthi.iyer

SSC Enthusiast

Points: 152
More actions
February 11, 2008 at 7:23 am

#74038

Hi
I have created a job in SQL 7.0 server, wherein the data is manipulated and it is to be exported to another server (SQL 2000). The problem is, the table to which this data needs to be exported, has to be truncated before the data export. In my job on the SQL 7.0 side, I call the SQL stsmt, TRUNCATE TABLE and I qualify the table name on server2, with [server name].[db name].dbo..
This job fails at this step.
Any suggestions?

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

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

toniupstny Hall of Fame Points: 3986 More actions · Answer 1

Have you checked the access /security authorizations on the other table/server?

Toni

kanthi.iyer SSC Enthusiast Points: 152 More actions · Answer 2

The servers are linked and I am logged in as sa.

The error message is that there are too many qualifiers to the table name and that only 2 are allowed.

I'd like to tag on another question.

I have 2 jobs in Server 1.

Job #2 is dependent on the success of Job #1.

In Job#2, I am truncating the tables in Server #2. Here is where I get the error.

I was thinking of a work around, where I can have Job #2 in Server #2. But how would I know the latest outcome of Job #1 in Server #1?

I appreciate your help.

SQLBill SSC Guru Points: 51440 More actions · Answer 3

The issue about too many qualifiers happens when you fully qualify the server you are on.

Example:

Linked servers (ServerA and ServerB)

DTS Package created on ServerA.

TRUNCATE TABLE ServerB.mydb.dbo.tableA

INSERT INTO ServerB.mydb.dbo.tableA

SELECT * FROM ServerA.mydb.dbo.tableA

-----------------------

The truncate will work without errors, but the insert will fail with the error you got since you fully qualify ServerA. Since the query runs from ServerA, don't include that server name.

INSERT INTO ServerB.mydb.dbo.tableA

SELECT * FROM mydb.dbo.tableA

-SQLBill

Erik Kutzler SSCarpal Tunnel Points: 4287 More actions · Answer 4

You can't execute a TRUNCATE TABLE command from a different server.

Here are 2 workarounds:

1) Add a SQL connection object to your DTS package that points to the server/database that you want to execute the TRUNCATE TABLE command on (if one doesn't already exist). Change the Existing Connection property of your Execute SQL task to this new connection.

2) Move job #2 to the other server. Add a step to job #1 that executes msdb.dbo.sp_start_job on the 2nd server.

EXEC Server2_Name.msdb.dbo.sp_start_job @job_name = 'Job2_Name'