I have two SQL statements in my code to pull data from diffe...

[Debra Waybright]

I have two SQL statements in my code to pull data from different tables. Can I join them Im getting an error that the access file record is absent for SQLOUT.

Here is basically what I am doing:

ENGINE SQLMSS SET DEFAULT_CONNECTION SQL_Reporting

SQL SQLMSS PREPARE PRO FOR

select key_field, some_other_field

from this_table

END

ENGINE SQLMSS SET DEFAULT_CONNECTION SQL_Reporting

SQL SQLMSS PREPARE SQLOUT FOR

select key_field, some_field

from that_table

END

JOIN FILE SQLOUT AT SQLOUT.key_field TO UNIQUE FILE PRO AT PRO.key_field TAG JS1

END

TABLE FILE SQLOUT

PRINT *

END

Thanks for any insight,

Deb

[David Beagan]

Usually it would be better to do the join in SQL Server.

I think the issue with your code is that the Reporting Server doesnt send the SQL to be run and create Hold files until there is a TABLE FILE on sqlout or pro. I tried it with a little bit different syntax, this code worked for me:

ENGINE SQLMSS SET DEFAULT_CONNECTION SQL_Reporting

 

SQL SQLMSS

SELECT name,create_date FROM sys.all_views ;

TABLE ON TABLE HOLD AS sqlout

END

 

SQL SQLMSS

SELECT name,modify_date FROM sys.all_views ;

TABLE ON TABLE HOLD AS pro

END

 

JOIN name IN sqlout TO UNIQUE

name IN pro AS JOIN1

END

 

TABLE FILE sqlout

PRINT name create_date modify_date

END

For larger files, consider creating an index on the file you are joining to.

[Debra Waybright]

I knew there had to be something I was missing! That worked.

Thank you!