SQL error on Stored Procedure crossing servers

[skuhlman]

I’m having problems using a data set from a stored procedure which crosses servers. Here’s the basic setup of my environment:

Server - MyProdServer1
Database – MyProd1ConfigDB
Database – MyProd1ClientADB
Stored Procedure - MyStoredProcedure
Database – MyProd1ClientBDB
Stored Procedure - MyStoredProcedure
Database – MyProd1ClientCDB
Stored Procedure - MyStoredProcedure
User – MyServerLink (has read access to MyProdServer2.MyProd2ConfigDB)

Server – MyProdServer2
Database – MyProd2ConfigDB
Database – MyProd2ClientZDB
Stored Procedure - MyStoredProcedure
Database – MyProd2ClientYDB
Stored Procedure - MyStoredProcedure
User – MyServerLink (has read access to MyProdServer1.MyProd1ConfigDB)

The database location for my report is one of the ‘MyStoredProcedure’s. The stored procedure access data from that client’s database as well as from one of the ‘MyProdXConfigDB’s. The server/database name of the config database is passed to the stored procedure as one of the parameters.

Within my Query Analyzer, execution of the stored procedure using either server name works correctly. The problem is that when I try to ‘verify database’ in Crystal Reports 8.5, the database is verified correctly when the server/database parameter is on the same server as the stored procedure, but does not work if it references the configuration database on the opposite server. The error message ‘General SQL Server error: Check messages from the SQL Server’ shows up in the Crystal Reports: Database Error window. When I look at the SQL log file it shows that the transaction is completing normally. That makes sense since it works in the query analyzer for both scenarios.

Why is Crystal complaining about a SQL error? Any ideas on how to make this work?

 

[synapsevampire]

What type of connectivity are you using?

I think that CR with SQL Server will allow for calling an SP within an SP (unlike with Oracle), but I'm not positive.

So it seems that you could execute an external SP and use the result set with the local result set to construct a single result set back to CR, if you're building more than one result set, that will cause problems.

It's unclear to me why CR would flip a bit because within the SP on a named server you execute another SP on another server.

Please share the outcome of your efforts.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[skuhlman]

If I'm understanding what you are suggesting, then I don't think that I can use that aproach for what I'm trying to accomplish. The data that's actually being pulled from the MyConfigXDBs is actually a field containing a SQL SELECT statement. That statement is then used further in the procedure to come up with the data set from the MyProdXClientXDBs. That result set is what is then shown on the final report.

 

[synapsevampire]

So you execute an external proc., which executes dynamic SQL.

Perhaps it's a matter of how you "used further in the procedure to come up with the data set from the MyProdXClientXDBs."?

I may give it a go this weekend when I get home just to see what CR does, hopefully someone else has done something similar and will resolve this.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[skuhlman]

AH, I figured out why it doesn't work...

To get my SPs which cross the server line to work through Query Analyzer the SPs had to be created like:

SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE SP_MYSTOREDPROCEDURE
(code)
GO

The ANSI settings execute and are stored in the SP environment, but don't get run when Crystal calls the SP. I had to additionally place the SET ANSI_... Commands in the body of the code section of the SP. Doing this then allowed the SP to run properly when being called by Crystal. Having it in the body of the code alone does not make it work through Query Analyzer. Lesson learned... Put the ANSI settings in both places if your SP is going to cross the server barrier.

 

[synapsevampire]

Thanks for posting the solution.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com