Crytsal/SQL Stored Procedure Hell

[VBorDeliver]

Hi!

I have set up a report that I call from my VB app using the Crystal OCX control. I need to change the location of the SQL database on the fly, this I can do for reports that access the SQL tables directly by using the code:

CrystalReport.Connect = "dsq=" & DatabaseName

However I have set up a stored procedure for the report in question that works fine except for when I try and change the location of the database. It just won't do it!!!

Usually you have to remove the 'Database Name' part from the SQL query in Crystal, unfortunaly when using stored procedures you just cannot get rid of this part of the query, every time you remove it it just appears again!!!

Any ideas on how to solve this would be greatly appreciated, I don't really want to have a seperate report for each database.

 

[skuhlman]

I'm not sure if this will work for you. I did something like it (this is foxpro code) by cycling through the tables and doing a .SetLongOnInfo for each table/sp.

FOR l_nPointer = 1 TO g_oCRRep.Database.tables.count

g_oCRRep.Database.tables.item(l_nPointer).SetLogOnInfo(ALLTRIM(g_oClient.r_cServerName), ALLTRIM(g_oClient.r_cClientDBCName), g_oClient.r_cUserName, g_oClient.r_cUserPassword)

ENDFOR

 

[VBorDeliver]

Thanks, however this is alrady what I am doing with the VB command '.Connect' and it works fine for reports based on tables but for SP's it just seems to ignore the command!

Are you using the Crystal OCX control? If it works fine with Foxpro then it must be a VB/Crystal issue. I shall do some more investigating.

Thanks Again.

 

[chris777]

I am not a programmer, however, we are doing exactly what you are trying to do. I remove the databasename.dbo from the select location window and then setting the database location at runtime through the ASP application. However, we are using RAS and OLEDB.

Sorry I can't be more specific. I hope this can give you another direction to look at. Chris
DFW Crystal User Group

http://www.dfwcug.com