I’m working on a consulting assignment using Crystal Reports 9.2x that uses two database connections. One connects to an Access 2000 database that uses one table of 31,000+ records. The other connects to a CacheInterSystems database via ODBC to a Linux box.
The Crystal Report uses eight tables from Cache and one from Access. Many of the tables in Cache are 300,000+ records and require a combination of inner and outer joins with an outer join to the Access table.
Several Accounting reports are created from these tables. These reports take over thirty minutes to well over an hour to run. So while waiting for these fiscal reports to run, I investigated an alternative.
I used the ODBC connections to create two linked servers via Enterprise Manager in SQL Server. Enterprise Manager requires ‘System’ data sources.
CONFIGURE LINKED SERVERS
- Open Enterprise Manager to the Server and open the node for Security.
- Right-click ‘Linked Servers’ and select ‘New Linked Server’.
- Enter a name for the linked server. This is arbitrary.
- Select ‘Other Data Source’ in ‘Server Type’ and select ‘Microsoft OLE DB Provider of ODBD Drivers’.
- Enter the name of the DSN connection in the ‘Data source’ box.
- Click OK.
- Repeat for the Access connection.
It would be nice if SQL Server had the capability that Access does to use the designer to build the query via a designer. It dosen’t and you have to create the T-SQL like a pass-through query. Also, linked servers are not defined with a particular database because they are linked to the server.
CREATE THE STORED PROCEDURE
Open the node for your database and drill-down to the node for stored procedures. Right-click and select ‘New Stored Procedure’.
OPENQUERY() is the function that distributes the T-SQL via the ODBC connection to the linked server and represents the return result set (derived table) that should be aliased. It takes two arguments. The first argument is the DSN name. The second is the linked-server-specific DML statement, written as a literal. I tried to create it dynamically and store the T-SQL in a variable and pass it as the second argument but no dice. Thus we need to do "SELECT fields FROM OPENQUERY(DSNName,’hard-coded sql statement’)."
Since I was using annual fiscal data, I did some research for as much data as possible required for the A/R reports and stored it in one table that was well indexed. I also used the OPENQUERY(dsn, ‘T-SQL’) as the alias to establish a join operation between the two linked servers. Incidently, it was also fussy with carriage returns, so paste the T-SQL in without them. The T-SQL was simplified for obvious reasons; it was significantly longer. The insert took well under thirty minutes to run. The subsequent reports took five or so minutes to run after being well indexed. I also added the code to index the table as well.
PASSING PARAMS TO OPENQUERY
Next, the issue of executing the open query statement with params.
Once you have built the full select statement, store the whole statement in a variable and do an EXEC.
CREATE DoDistribQuery(@StartDate dateTime,@EndDate datetime)
Set QUOTED_IDENTIFIER OFF
Declare @SQL varchar(5000)
IF EXISTS(SELECT *
WHERE name =’tbl_AR_Statistics_ Report’
AND type = 'U')
DROP TABLE tbl_AR_Statistics_Report
do code to add indexes
truncate table 'tablename' would probably be much faster
set @SQL ="select a.*, b.* Into tbl_AR_Statistics_ Report from OpenQuery(apmlive,'SELECT billing_tx_charge_detail.date_of_service, billing_tx_charge_detail.v_accounting_period FROM SYSTEM.billing_tx_history billing_tx_history Order By GUARANTOR_ID'
) as a Left Outer Join(
select iD,ServCode,Payment from OpenQuery(bhnacctrpt_access,'Select * From tblPaymentPerService')
On a.ID = b.ID
And a.SERVICECODE = b.SerCode
WHERE B.date_of_service Between " + Cast(@StartDate as VarChar)+ " AND " + Cast(@EndDate as VarChar)
I subsequently went on to create a job that was scheduled for evening updates.