Report using ODBC Sql server 2005

[Genelia]

Hi to all,

Now i am working on generate report using odbc sql server 2005 database via import record and set query. sql server 2005 is on different system while creating ODBC dSN i connected it successfully.

On my system it is working fine.But now the query is:"is it possible fm file on fm server advanced,enable odbc on.... and generate this kind of report from other fm clients system without creating ODBC Sql server 2005 DSN?"

if it is possible then tell me the answer and if not then what is the meaning of this statement in filemaker : "by 1 odbc connection on fm server advanced ....other fm clients can access/do the work?"

I have very confusing query. tell me the answer or where i am wrong.

[bcooney]

Your setup isn't clear.

If you install a driver on the FMS and configure its connection to your Sql database, then using ESS, you'd add this External Data Source to your file, and your RG and for most intents and purposes, it'll appear as if it's a FM file. Each client, with this config, does not need the ODBC driver.

There's a very detailed ESS white paper on FMI. Start here: http://www.filemaker.com/support/technologies/sql.html

[Genelia]

Actually i dont need 2 or 5 or 10 tables from sql database for work.Thing that i have done is: I have very big query statement which is taking data from lots of tables of sql database. so when i put this query in import script step's query box.it gives data in import fields i just map it with fm created fm fields and in preview mode show the report result.

Now it is ok only when we have ODBC driver on system and created DSN for that.when this file opened by remote and goes through import script step by ODBC file then i shows "?" for file because on that system ON odbc/DSN is created.

so in simple terms "for every system who is accessing fm report file from remote need individual ODBC driver/DSN to do the import script step(ODBC file)?"

[bcooney]

Have you read any of the documentation that I linked to?

See p. 13 of the fm11_odbc_jdbc_guide_en.pdf

[Genelia]

Yes i read but that is talking about put tables from ODBC data source and access it from clients...yes it is clear to me.But i am not adding any such type of tables in my FIlemaker database... I am just using sql query which works through import script step :option ODBC data..map query result with Filemaker fields.

Because on host machine i have created DSN so it is working...but when i access that remote hosted file on other system as there is no DSN established on this system so my query not been to able to work on that condition because for execute it finds ODBC Data source and it is absent in that condition.

[bcooney]

Can you run the import in a FM Server script?

[Genelia]

Yes i also tried to schedule hat import script on fm host server because dSN is already there.But don't know why not get succeeded .Ok again let me try .have you any document which shows clear concept of schedule script . Might be i had not followed proper procedure.

[Genelia]

Yes Run from server side is working...but i want according to parameters its import the script step from OBDC.....is there any way for pass script parameter according to condition means 2,3,4,5 (This time i pass 2 manually so it is working for QUery no:2 only. i want whether it takes field value or global variable so that acc. to condition i put my values from client and according to that values it work) into schedule server script.

[bcooney]

In order to have the server "read" a field value, you'd need your server script to go to the proper context and "read" the value. Or, set the field value you need into a global in the Open Script (which runs each time a server script runs).

Why isn't ESS an option for you? It sounds as if you're not bringing data in overnight, but rather trying to allow live queries against the sql table. FM is not supposed to be used as a front-end to sql, and you should set your customers expectations. Unless you can create views in sql, your response will probably not be acceptable.