Sql server agent job failing for linked server

Other SQL Server Topics
1

Hi - If anyone can help, i'd greatly appreciate it! (not familiar with jobs or sql server so pls excuse me)

I have a job setup that syncs info from one db to another db on a linked server.
The procedures seem to randomly fail and I keep getting errors. If i run them manually they will work sometimes (takes a few tries sometimes). I am trying to figure out whats going on and why this is happening...
Here is one message I am getting now but Im not seeing why it cant update the table. Is there any way to get more info on the error?
The OLE DB provider "MSDASQL" for linked server "mysever" could not update table "[myserver]...[mytable]"

2

Does the user have update permission granted? Is there more information in the logs? What SQL is the Job running? Post your code please.

3

no more information in the logs - showing that same error. When I try to run it manually it I get the same error.
The user has update granted - when I try to update just a one or two columns it works ok. When I start adding 3 or 4 columns..sometimes it works and sometimes it doesnt :frowning:
The SQL is an update - see below:

UPDATE mylinkedserver...MyTable
SET
[nComplexID] =s.[nComplexID]
,[bWebSite] =s.[bWebSite]
,[bSpecial] =s.[bSpecial]
,[strMRIBldgID] =s.[strMRIBldgID]
,[strAddress] =s.[strAddress]
,[strCity] =s.[strCity]
,[strCounty] =s.[strCounty]
,[strState] =s.[strState]
,[strArea] =s.[strArea]
,[strParentState] =s.[strParentState]
,[strZip] =s.[strZip]
,[strBuildingType] =s.[strBuildingType]
,[bHealthClub] =s.[bHealthClub]
,[bWasher] =s.[bWasher]
,[bDryer] =s.[bDryer]
,[bPets] =s.[bPets]
--,[fLongitude] =s.[fLongitude]
--,[fLatitude] =s.[fLatitude]
,[bActive] =s.[bActive]
,[strBalcony] =s.[strBalcony]
,[bBasketballCourt] =s.[bBasketballCourt]
,[strConciergeType] =s.[strConciergeType]
,[bGatedCommunity] =s.[bGatedCommunity]
,[bBusinessCenterFee] =s.[bBusinessCenterFee]
,[bHealthClubFee] =s.[bHealthClubFee]
,[bDoorman] =s.[bDoorman]
,[bPetsFee] =s.[bPetsFee]
,[bPoolFee] =s.[bPoolFee]
,[bSunDeck] =s.[bSunDeck]
,[bTennisCourt] =s.[bTennisCourt]
,[bPool] =s.[bPool]
,[bBusinessCenter] =s.[bBusinessCenter]
,[bGranite] =s.[bGranite]
,[bWalkInCloset] =s.[bWalkInCloset]
,[bDishwasher] =s.[bDishwasher]
,[nFloors] =s.[nFloors]
,[strPetType] =s.[strPetType]
,[nYearBuilt] =s.[nYearBuilt]
,[nOldPropID] =s.[nOldPropID]
,[txtDescription] =s.[txtDescription]
,[chWasher] =s.[chWasher]
,[dtCreated] =s.[dtCreated]
,[dtUpdated] =s.[dtUpdated]
,[strEntity] =s.[strEntity]
,[strBldgInfoLink] =s.[strBldgInfoLink]
,[strSubway] =s.[strSubway]
,[txtDescriptionDetails] =s.[txtDescriptionDetails]
,[strStatus] =s.[strStatus]
,[bCRated] =s.[bCRated]
,[strHopStopID] =s.[strHopStopID]
,[strGPSAddress] =s.[strGPSAddress]
,[bCS] =s.[bCS]
,[mStartingFrom] =s.[mStartingFrom]
,[bMeetingRoom] =s.[bMeetingRoom]
,[bStorageRoom] =s.[bStorageRoom]
,[bClubhouse] =s.[bClubhouse]
,[bActivityCenter] =s.[bActivityCenter]
,[bUnfurnishedRentals] =s.[bUnfurnishedRentals]
,[bWalkscore] =s.[bWalkscore]
,[bCats] =s.[bCats]
,[bDogs] =s.[bDogs]
,[bMicrowave] =s.[bMicrowave]
,[bGarage] =s.[bGarage]
,[bLuxury] =s.[bLuxury]
,[strCrossSt] =s.[strCrossSt]
,[strCrossAve] =s.[strCrossAve]
,[bDoorman24] =s.[bDoorman24]
,[bConcierge] =s.[bConcierge]
,[bDryCleaners] =s.[bDryCleaners]
,[bDining] =s.[bDining]
,[bPublicTrans] =s.[bPublicTrans]
-- ,bBookNow = s.bBookNow
--,nRating = s.nRating

FROM mylinkedserver...MyTable AS t 
	INNER JOIN  [dbo].mytable AS s WITH (NOLOCK)
ON s.[nBuildingID] = t.[nBuildingID]
WHERE
s.cSync = 'G'
4

Unless you're sure date will not be changing during your query don't use WITH (NOLOCK).
Create a table on the mylinkedserver server with the same structure as dbo.mytable, say mystaging.

INSERT INTO
   mylinkedserver...mystaging 
SELECT *
FROM dbo.mytable 
WHERE cSync = 'G';

Then do the update on the linked server.

5

Thanks for your help.
This is what I did as per your suggestions:
On the Linked Server (mySql)

  1. created the temp table "mystaging"
  2. created a procedure there to do the update.

On the sql server where the job is located:

  1. In the procedure where the step was failing, I replaced the update with the
    insert into the temp table "mystaging"
  2. call to the procedure on the linked server to do the update.

I think it is working now. There may be some other procedures in the scheduled job that may require the same fix but i'm going to enable it and see how it goes :slight_smile:

6

update: checked the history on the job and seems like it failed - the error I'm seeing now is "Cannot fetch a row from OLE DB provider "MSDASQL" for linked server MylinkedServer ....I'm guessing b/c I'm still selecting from the linked server see below..
I will update so its just getting the rows from dbo.buildings where csync=
'G' and leave the inner join on the update on the linked server..will see if that works..

this is the insert now:

insert into MylinkedServer...MyStaging
(nBuildingID,
nComplexID,
...)
select
s.nBuildingID,
s.nComplexID,
...
FROM MylinkedServer...FhBuildings AS t
INNER JOIN [dbo].Buildings AS s
ON s.[nBuildingID] = t.[nBuildingID]
WHERE
s.cSync = 'G'

I'll change it to:
insert into MylinkedServer...MyStaging
(nBuildingID,
nComplexID,
...)
select
s.nBuildingID,
s.nComplexID,
...
FROM [dbo].Buildings AS s
WHERE
s.cSync = 'G'

7

Why not create the an SSIS Project.

  1. Create a staging table
  2. Create a stored procedure that inserts from the staging table into your target table.
  3. Run an export in SSMS, in the data mapping dialog change it to delete rows in destination and save it to the file system.
  4. Create a New SSIS Project, delete the package it automatically creates, and add the package you saved in the previous step.
  5. Compile and deploy to SQL Server.
  6. Create a Job to run it.
8

What is SSIS? is it included in the MSSQL tool? I was given the task of figuring out why this scheduled job is failing and to fix it. This was created by someone on my team using the sql server agent.
Basically have 9 steps here and each one has a few procedures to run doing similar updates and deletes like the one above to sync specific tables on the mssql server from the sql server.
If you think SSIS is the way to go then maybe I need to start learning how to set up an SSIS project. pls advise.

9

Microsoft created SSIS (SQL Server Integration Services) to transfer data and files. It's the ETL tool that comes with SQL Server. I recommend using it for scheduled data transfers in a SQL Server environment. Download and install SQL Server Data Tools to get started. If you import or export data, what is created is an SSIS package. This can be Added to an SSIS Project and edited to tweak to your requirements. For example when you create an import that creates the destination table, you can open the package and change the create to a truncate if you want to so you can schedule the reload of a staging table.