Hi All,

I have report that needs to run on different database servers and databases. The master file/table information is the same in all environments, just the data is different.

I wanted to know if a report can be build in such a way that we have the option to pick which connection and database at report runtime?

Other alternative is creating separate domains for each connection and copy the report to these domains. This works. But, not the best way to approach it.

Thanks.

How about using some Dialog Manager code to select the set of connection code strings to execute for that run ?

You would need some minimal user interface to provide a selection choice to pass to DM to selectively set your connection.

How can I achieve this? Could you please provide me with a sample/example?

OK, here goes, some very simple Dialog Manager code to choose between various connections

-IF &CHOICE EQ 'A' GOTO CONN1
-IF &CHOICE EQ 'B' GOTO CONN2
-IF &CHOICE EQ 'C' GOTO CONN3
-*
-CONN1
connection string for db 1
_GOTO REPRT_CODE
-*
-CONN2
connection string for db 2
-GOTO REPRT_CODE
-*
-CONN3
connection string for db 3
-GOTO REPRT_CODE
-*
-REPRT_CODE
-RUN

TABLE FILE CAR
...
END

-RUN

Since &CHOICE isn't set in this program it will be prompted for, unless you set up some interface to present a more understandable set of options to the user and pass the variable to your report program, and that may vary depending on your environment.

The rest is left up to you, Good Luck.

Thanks a lot Dave. I'll try that.

BIGuru,

I suppose you have in your edasprof.prf file a number of connections. For example with some SQL/SERVER databases:

  
ENGINE SQLMSS SET CONNECTION_ATTRIBUTES Srv1 webserver/user,pwd;database
ENGINE SQLMSS SET CONNECTION_ATTRIBUTES Srv2 webserver/user,pwd;database
ENGINE SQLMSS SET CONNECTION_ATTRIBUTES Srv3 webserver/user,pwd;database
ENGINE SQLMSS SET CONNECTION_ATTRIBUTES Srv4 webserver/user,pwd;database

Then, in your procedure, if you want to acces Srv3, you can code before any TABLE request:

SQL SQLMSS SET SERVER Srv3

Of course, this statement can be parameterized.
I hope this works for you.

I can see how the connect strings would work with SQL Passthru.

Correct me if i'm wrong but the CONNECTION setting within the ACX file is hard coded. Once it's accessed it'll use the connection entry for that ACX file. I don't see how the connect string would help under this scenario.

You are right IF the CONNECTION setting is coded in the ACX file. But it is not compulsory. Just delete it and WF will connect to the server defined by SET SERVER. This is very useful, for instance, when you have 2 databases, test and production for example or maybe 2 separate databses for different years and you want to report from both.

FC,

If your acx files are in differenct directories, you can use the variable to adjust the application path with an APP PREPENDPATH to find the acx files appropriate for the connection that you want.

However, if you are doing passthru there is no master and no acx file.

quote:

Originally posted by Danny-SRL:
BIGuru,

I suppose you have in your edasprof.prf file a number of connections. For example with some SQL/SERVER databases:

  
ENGINE SQLMSS SET CONNECTION_ATTRIBUTES Srv1 webserver/user,pwd;database
ENGINE SQLMSS SET CONNECTION_ATTRIBUTES Srv2 webserver/user,pwd;database
ENGINE SQLMSS SET CONNECTION_ATTRIBUTES Srv3 webserver/user,pwd;database
ENGINE SQLMSS SET CONNECTION_ATTRIBUTES Srv4 webserver/user,pwd;database

Then, in your procedure, if you want to acces Srv3, you can code before any TABLE request:

SQL SQLMSS SET SERVER Srv3

Of course, this statement can be parameterized.
I hope this works for you.

Danny,

I cannot use this solution as I have to hard code the connection information, say 'srv3', cos when the report moves to production this connection won't exist, there will only be one connection.

BIGuru,

So, just remove the SET SERVER statement, or use a parameter which would be blank in the production environment.
You can also use the PREPENDPATH statement but then you need to multiply all your ACX files which becomes quite cumbersome if you have many tables and if your test environment changes.

BIGuru,

Another idea would be to set the server name/IP Address in edasprof.prf, or, pass it from site.wfs, cgivars, etc.

Then, have a FOCEXEC which contains the environment variables, the server connection being one of them, and, -INCLUDE the fex at the top of each focexec:

 
-INCLUDE WF_ENV_001

Would have within it:

-SET &CONNECTION = IF &SERVER_NAME EQ 'IP address' OR 'Alias Name' THEN  'DEVELOPMENT NAME' ELSE 'PRODUCTION NAME';

Then the SQL code:

SQL SQLMSS SET SERVER &CONNECTION

Thought I would throw out one more idea...

Tom

Isn't this similar to a recent problem. You can do this one of several ways. You can use a 'Variable' as the high level identifier qualifier and then enter the value at run time. You can have different -Include .fex that have the correct statements and then either use the correct .fex or prompt for the .fex.