Calling SQL Server functions from Crystal

[newjim]

Can you call a SQL Server function from Crystal 8.5? I can't use stored procedures in my environment, so have created a SQL Server function that accepts a parameter and returns a single value. However, I can't see that function in the Database window of Crystal, so can't use it.

I've also tried using the function in an SQL expression in Crystal, like:

(select id from dbo.fx_Contact.ID('X4953A'))

This does work, but I have to hardcode the parameter. What I'd like to do is pass in the current id, like

(select id from dbo.fx_ContactDascoID(CONTACT."ID"))
but the expression won't accept variables.

Thanks.

 

[synapsevampire]

Very strange that you're allowed to use functions but not procs, I suggest that your dba go back to bartending school.

Unfortunately CR 8.5 does not allow for using functions directly as a data source, and you can't paste dynamic parameters into the sql in the 3 methods possible (ADO connection allows for pasting SQL, Database->Show SQL Query allows for editing part of the SQL, and a SQL Expression). Logically Crystal hadn't considered that soemone would think it wise to use functions as data sources, but not have Storeed Procedures available.

Perhaps you can create the function within Crystal or get additional rights to the database?

-k

 

[newjim]

The reason that we can't use stored procedures is that the Crystal report viewer runs inside of another program. This program wants to append the userid of the current user to the query that it fires to SQL Server, which does not work when the query is a stored procedure. It might do the same thing for a function call too.

Thanks for your answers. I'll have to try something else.

 

[synapsevampire]

Ahhh, I get it, a proprietary system with severe limitations. I am all too familiar with that scenario. perhaps you can set up alternative ways to provide business intelligence, such as Crystal Enterprise or 3rd party viewers.

-k

 

[chelseatech]

If it is some data conversion you are trying to do, one solution would be to convert that to a CR formula. You can do a lot in there.

If it is very complex, you could use VB and develop a User Function Library. They are quite easy to write.

Or can you do waht you want with a SQL expression in CR. The SQL expression might be able to call your server funtion.

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm