Skip to main content
Solved

Errors when running FME Desktop workspace in scheduled task - MS SQL connection related

H

Hi,

I have created a FME Desktop workspace that imports data into the MS SQL database which runs successfully as a scheduled task if I am logged in the server. But it fails when I'm off the server and gives me this kind of error message. (I got a blank SQL provider error)

2020-05-07 22:00:21|  11.5|  0.0|INFORM|Microsoft SQL Server Non-Spatial Writer: Trying to connect using SQL Server 2012 Native Client...
2020-05-07 22:00:22|  12.3|  0.8|ERROR |Microsoft SQL Server Non-Spatial Writer: Failed to connect using SQL Server 2012 Native Client. Provider error ''. Connection string 'Provider=SQLNCLI11;Data Source=;Initial Catalog=cadasdb_equalizer;User ID=;Password=********'
2020-05-07 22:00:22|  12.3|  0.0|ERROR |Microsoft SQL Server Non-Spatial Writer: A suitable Microsoft SQL Server Native Client is not found. Install Microsoft SQL Server 2012 Native Client from http://www.microsoft.com/en-ca/download/details.aspx?id=29065 . Please expand 'Install Instructions' and download 'sqlncli.msi'
2020-05-07 22:00:22|  12.3|  0.0|ERROR |Failed to open writer
2020-05-07 22:00:22|  12.4|  0.0|ERROR |Destination Feature Type Routing Correlator (RoutingFactory): Failed to open writer

I use a FME Desktop version 2019.2.1 (32bit) and have the MS SQL Server Native client 2012 (Both 32 bit and 64 bit ) installed. 

I created a .bat file (LoadEqualizer2020.bat) to call the workspace :

C:\apps\FMEx86\fme.exe C:\FMEWorkspace\Wrokspace\load_equalizer_sql_all_Prod_test.fmw

It is set up in the scheduled task under my account as :

0684Q00000ArE5ZQAV.png

I also made the db connection public:

0684Q00000ArE7jQAF.png

0684Q00000ArE7oQAF.png

 

I've checked other posts but cannot find a solution. Any help is appreciated!

 

Jing

 

Best answer by hanj

I found this solution helps!

https://knowledge.safe.com/answers/97663/view.html

Instead of using a database connection, I used Embed Connection Parameters...

and the problem is solved!!

View original
Did this help you find an answer to your question?

9 replies

J
Forum|alt.badge.img+2

Are you using OS Authentication or a named user on the server?

H
  • hanj
  • Author
  • May 8, 2020
jlbaker2779 wrote:

Are you using OS Authentication or a named user on the server?

I use a domain user.

itay
Supporter
Forum|alt.badge.img+17
  • itaySupporter
  • Supporter
  • May 8, 2020

In these kind of deployment it is usually preferable to run the schdueled jobs under a service account.

You can edit the schduled jobs to run using the service account.

H
  • hanj
  • Author
  • May 11, 2020
itay wrote:

In these kind of deployment it is usually preferable to run the schdueled jobs under a service account.

You can edit the schduled jobs to run using the service account.

Would you explain a little more about it? I currently use a Domain admin account to run the task.

H
  • hanj
  • Author
  • May 11, 2020
itay wrote:

In these kind of deployment it is usually preferable to run the schdueled jobs under a service account.

You can edit the schduled jobs to run using the service account.

I've tried using domain admin account, local admin, Local Service, Network Service, System accounts, but still got the same error. I also have SQL Server Native Client 2012 32 bit and 64 bit installed. As a test, I am able to connect to the database with both clients through ODBC.

danielwortham
Contributor
Forum|alt.badge.img+1

In the scheduled task under "Security Options" make sure you have "Run whether user is logged in or not" and "Run with highest privileges" set.

J
Forum|alt.badge.img+2
hanj wrote:

I use a domain user.

I don't think it's a SQL server issue. If you look at your connection string you are missing a Data Source and User ID.

 

'Provider=SQLNCLI11;Data Source=;Initial Catalog=cadasdb_equalizer;User ID=;Password=********'

 

If you have access to the server, create a server user in SQL Server using SSMS or whatever management tool you use, grant permission for the user to the database, and reconfigure your FME connection using that account.

 

H
  • hanj
  • Author
  • May 11, 2020
danielwortham wrote:

In the scheduled task under "Security Options" make sure you have "Run whether user is logged in or not" and "Run with highest privileges" set.

Yes it is already configured with that two settings.

H
  • hanj
  • Author
  • Best Answer
  • May 11, 2020

I found this solution helps!

https://knowledge.safe.com/answers/97663/view.html

Instead of using a database connection, I used Embed Connection Parameters...

and the problem is solved!!

Reply


Helpful Members This Week

Community Stats

31,340
Posts
118,849
Replies
38,928
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.