Multiple DBs/Multi-join... IN CRYSTAL?

[MVisconte]

I have to compare Remedy data tables from a main site to four sub-sites. I can do this by using left and right outer joins (to similate full outer joins).

I came up w/ the stacked union queries to check
Master->Site1,
Master<-Site1
Master->Site2,
...

Gave that to our (new) DBA guy and he returned the MSDASQL statement form to be able to call the four remote DBs from our main MS SQL DB environment.

I'm trying to do it from an MS SQL query from w/in CR 9.0 -- as an &quot;Add Command&quot; query to pass to the SQL server.

I can't get the statement to work:

SELECT &quot;Main -> Site1&quot; as Site, a.Group_name as CheckFor
FROM OPENROWSET('MSDASQL', 'DRIVER={SQL SERVER};SERVER=SRVRNAME;UID=UserNm;PWD=SrvrPWD,ARSystem.dbo.Group_x) AS b
ON a.Group_name = b.Group_name
WHERE b.Group_name is null

I get &quot;Failed to open a rowset.
Details: 42000:[Miscrosoft][ODBC SQL Server Driver][SQL Server]Ad hoc access to OLE DB provider 'MSDASQL' has been denied. You must access this provider through a linked server.&quot;

OK, I'm &quot;duh'ed&quot; out.

I was hoping that this would let me hand-jam the various table names/fields to check as required and still get results out in Crystal.

Anybody have any experience w/ CR9 and the use of SQL statements from w/in?

Is there another / better way to do this? Ulp. I'll post this in SQL forums also for a related quex.

 

[synapsevampire]

Ask your DBA to make a Stored procedure out of it, that'll make things faster and simpler for you.

Pass this along to your DBA, you may need to apply a service pack (similar problem):

http://support.microsoft.com/default.aspx?scid=/servicedesks/bin/kbsearch.asp?Article=328569

-k

 

[MVisconte]

Thanks.

I wound up passing my poor, poor, pitiful code (compliments to Linda Rhondstat) on to the DBA and 'tween the two of us, we got it working. And fairly slickly, also. I can pass a table name and a field name, and get back a beaufitul (if slow) union query. Next problem:

I've hit the upper limit of the internal variable site. I essentially build the SQL on the fly from the parameters, but the limit on nvarchar is 4k, and at least ONE instance of tablename, fieldname pushes the whole thing past that. The DBA's recommendation was to try splitting the huge union query (sp) into two half-union sp's and call both from a master sp. Well, call me Ishmael, but I don't know how to union the two together. The simple:

exec usp_AllDat1 @param1 @param2 @param3
union
exec usp_AllDat2 @param1 @param2 @param3

bombs out on the union. The master works w/ just a single sp call and params, returning the two sets of unions (out of the four in the &quot;whole&quot; set).

Any thoughts, ideas, or aspirin?


We may have to resort to linked servers -- which should obviate the need for calling rowsets (I don't know that for a fact). I'd like to pursue the avenue we're on as far as possible rather than jump to a new track. Have I cross enough figures of speeach? Whew.