I'm not sure that you can use a server scheduled script to import your data from an external source--at least you can't import from an external file so doubt you can do it with ODBC.
The knowledge Base article on which I'm Basing this post: http://help.filemaker.com/app/answers/detail/a_id/7035/kw/server%20script%20import
Note the section titled: "FileMaker Server can only import from and Export to the following locations:"
Thanks for your response. Having read the document you referenced, I'm fairly curtain that a scheduled script can import ODBC data. In fact, the file you referenced states "In addition, importing from an ODBC data source is supported." My question is more about circumventing the need to manually input the Username and Password for the ODBC connection.
Any help would be appreciated.
Yes, but where is the ODBC data source located? If it's from a source external to your server...
The data source is located externally to the server. I am able to connect to the data source easily when I run the script myself within a client version of Filemaker running on the server, but have to manually enter in the username and password to access the odbc connection.
Are you saying that FIlemaker Server will not properly run this script on a schedule?
If so, how would you go about automating a process of importing data manually from the external SQL odbc source?
Please note that I haven't done much with ODBC. I've basically read a portion of a knowledge base article to you that I know about from my experiences trying to import and export records from FileMaker files.
When a script can only be run as a client, you can schedule the script in one of two ways:
- Use a robot file. This is a fileMaker file not hosted on the server with a small script set to run when the file is opened. This script then performs the script you want to schedule on the hosted database file. You usually have to define an account and password on your client to match an account on the hosted file and user file options so that this file opens itself with that password automatically. Then you use an OS based scheduler such as Windows Scheduled Tasks to open an run the robot file on the schedule you want.
- If you leave a client machine open 24/7, you can use Install OnTimer to set a script to run every 24 hours or other interval that you need.
Of course, this still leaves your original question unanswered, which is how to automate the process of issuing the needed password to open the ODBC source. And that's not something that I have an answer for.
Does anyone know how to automate the process of entering username/password for ODBC connections? Any help would be appreciate.
It depends on what data source your conneting to. If your connecting to mySQL i do know when you configure the DSN it will allow you to store the user name and password.
I set up the data source DSN as a system DSN and entered in the password/username into the system DSN setup but am still being asked for it each time I run the script.
anybody have any thoughts?
You can have your script save the odbc login info. It is an option in the script step that I have to always get people to use a generic login with limited permissions rather than their personal logins with god like permissions in the said system.
When we run the scripts on the client with no prompt and the passwords stored in filemaker in the script there are not issues. With SQL server and windows permissions just leave the login boxes blank and hit ok during scripts. To store passwords use sql user accounts stored in sql server and not a domain account.
A difference with linked tables is storing the login info in the ODBC 32 System DSN configuration.
Of course, my next foum search is why my script step imports aren't working even though they report success, so take this with a grain of salt.
forgot to address the prompt, you have to click the checkbox of perform without dialog.