Hi All,

I am using SQL passthru in the following format and it works:

SQL SQLMSS SET DEFAULT_CONNECTION conn1
SQL SQLMSS PREPARE SQLOUT FOR
select * from db_name.dbo.table_name
;
END
TABLE FILE SQLOUT
PRINT *
END

But, when the report is ready to be run against a different connection and db, I have to rename all the connection names and db names.

Changing connection name - we can live with that.

But why do we need to prefix the db_name? If I dont prefix I get the following error -

(FOC1400) SQLCODE IS 208 (HEX: 000000D0) XOPEN: 42S02
: Microsoft OLE DB Provider for SQL Server: [42S02] Invalid object name 'table_name'. [42000] Statement(s) could not be prepared. [] Deferred pre
: pare could not be completed.
L (FOC1405) SQL PREPARE ERROR.
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: SQLOUT
BYPASSING TO END OF COMMAND

Any help on this issue is very much appreciated.

Thanks.

This message has been edited. Last edited by: BIGuru,

What type of data base? db_name is associated with table space in the db2 world, and for different environments it can be different.

quote:

db_name

Leah,
We are using SQL Server 2005.

Have you tried using variables for both the connection and db_name? I used it at one job, it was their standard. What you do is set up a
-include Names, which is a member in your procedure library, and depending on which db you are going against, you just -include the correct names.

I tried that and it works with the db_name prefix:
-INCLUDE app/abc.fex
SQL SQLMSS PREPARE SQLOUT FOR
select * from db_name.dbo.table_name
;
END
TABLE FILE SQLOUT
PRINT *
END


However it does not work without the db_name prefix. That’s the big problem! This table exists in all the environments, so that should not be the problem.

The same table-name could be used in multiple databases of connection conn1.

This is the normal behaviour of SQL Server: you have to specify database-name.table-name

So, anytime I move the report I need to manually change the connection name and the db name? There is no alternative?

Doesn't it make sense that you have to do that?

If the db name is the same on all connections then you wouldn't have to change the dbname.

You could do what RSquared suggested, create an include:

-SET &CONN_NAME = 'CONN1';

-SET &DB_NAME = DECODE &CONN_NAME ('CONN1' 'DB1', 'CONN2' 'DB2', 'CONN3' 'DBXX' ELSE 'DBQS');

Then in your fex:

-INCLUDE CONNNAMES

...

SELECT * FROM &DB_NAME...TABLE_NAME

...

(Note the three dots after the Dialogue Manager variable)

Thanks! Thats what I was trying and looks like it will work.

quote:

But why do we need to prefix the db_name?

Sequel Squirmer connections can include the db_name by putting ;dbname at the end

eg ENGINE SQLMSS SET CONNECTION BILLGATES GATES/ilove,melinda;WILLIAM

accesses database WILLIAM on server GATES.

John, good point. Then you probably don't need to prefix the table name with the db name.

That'll work! Thanks to everyone.