Parameter Problem - driving me insane

Ok here we go,

MySQL 5
MyODBC 3.51
Crystal Reports.Net
and
ASP.Net using VB.Net

Set up the connection to the DB in Crystal using MyODBC. I used the Database expert to call my Stored Procedure which has one Parameter and the code is real simple going like this:

call SuccessLetter (AppNo)

From the wizard i can see and place all the fields from the SP onto the report as long as i hard code an application number into my parameter. But as soon as i change it to a variable (as above) i get the following error from Crystal: Unknown Fields AppNo in order clause MySQL ODBC 3.51 error 42S21

On the VB code side i used
Success.SetParameterValue("AppNo", GeneralFunctions.ApplicationNo)
to set up the parameter and this does get passed thru so the error is comming from the database expert - almost as if it dont recognise that my stored procedure has a parameter. Which it does and it does work if i use the SP outside of Crystal.

Any ideas - please help me before i go bald from tearing the hair out.

ThanksAnd another weird thing i;ve noticed is that Crystal puts the stored procedure in the list of tables when i use the database expert when i go to choose my tables

Parameter Available Values

If I have a paramter that has query based available values, how do I get it to use a connection string specified in another paramter to do the query?

The scenario is that I have one set of reports that pull from a number of databases that have identical schemas and each serve a different customer. i need to be able to somehow populate the valid values with the right data from the appropriate database at runtime.

Nevermind, I found it.

I just had to populate an array of ParameterValues with just the connection string and then do a call to GetReportParameters, that made sure that the other parameters had the data that they needed.

Parallel Install 2005 CTP & 2000 Leads to Connection Problems

Hey All,
More fun and games with SQL Server 2005.
Mike: thanks for your help before on the Beta. I was able to uninstall and
regain connectivity. I didn't have to wipe the box, but came close!!
Here is a new twist:
Previous situation:
- One data server
- 2 instances: SQL Server 2000 in a named instance; SQL Server 2005 Beta 2
in the default instance.
- Access to the legacy database (2000) is controlled through a trusted
connection, using an Active Directory Group (MSA Users). This group is
granted public role access with various pre-defined rights on the objects.
- Access to the beta is for a website we are developing, and is done
through a SQL Server connection. This connection is granted public role,
with limited access to objects--mostly stored procedure executions only.
- Everything running well.
Now:
- SQL Server Beta 2 expired.
- Uninstalled and cleaned SQL Server 2005 Beta 2.
- Installed SQL Server 2005 April CTP.
- Databases are running well, however:
- Trusted connections to the legacy database are all messed up: the Windows
group controlling access to the SQL 2000 database still exists, but members
of the group can no longer connect. Some weird behavior ensues:
* Some get an 18456. Messages include: "Cannot find the specified server"
* Some can connect via the IP, but not the DNS
DANGEROUS, but working (WHY?)--
* When I add the users to a completely unrelated AD group (with no
particular access to SQL Server, AND I make group associated with the trusted
connection members of the local Administrators group on the data box, THEN
they can connect. If I remove the user from the unrelated group OR I remove
the trusted group from the local Administrators group , they cannot connect.
Of course, this is less than desirable. What happened to the security?
HELP!!
Mike--I see you live in Zurich. I once lived in Yverdon.
Graeme Martin
I spoke with some folks at Microsoft who were very helpful. Just thought I
would share with you what we found--
1. The named instance (2000) wasn't showing up to users trying to connect:
* Check SQL Browser service is running.
* Check the following key in the registry:
HKLM/Software/Microsoft/Microsoft SQL Server/90/SQL Browser/SsrpListener
(REG_DWORD). This value needs to be (1).
2. The user was denied access from the trusted group. Run this SQL:
USE master
GO
SELECT name, denylogin, hasaccess FROM syslogins
-- denylogon should be 0; hasaccess should be 1 for your logins.
3. Use sp_grantlogin 'DOMAINNAME\username' to add logins. This SP will
ensure the proper fields are filled out.
4. Still can't figure out why the website won't show up, but I think it's an
unrelated issue.
Thanks all! Hope this helps you too!
Graeme Martin
"The Oracle" wrote:

> Hey All,
> More fun and games with SQL Server 2005.
> Mike: thanks for your help before on the Beta. I was able to uninstall and
> regain connectivity. I didn't have to wipe the box, but came close!!
> Here is a new twist:
> Previous situation:
> - One data server
> - 2 instances: SQL Server 2000 in a named instance; SQL Server 2005 Beta 2
> in the default instance.
> - Access to the legacy database (2000) is controlled through a trusted
> connection, using an Active Directory Group (MSA Users). This group is
> granted public role access with various pre-defined rights on the objects.
> - Access to the beta is for a website we are developing, and is done
> through a SQL Server connection. This connection is granted public role,
> with limited access to objects--mostly stored procedure executions only.
> - Everything running well.
> Now:
> - SQL Server Beta 2 expired.
> - Uninstalled and cleaned SQL Server 2005 Beta 2.
> - Installed SQL Server 2005 April CTP.
> - Databases are running well, however:
> - Trusted connections to the legacy database are all messed up: the Windows
> group controlling access to the SQL 2000 database still exists, but members
> of the group can no longer connect. Some weird behavior ensues:
> * Some get an 18456. Messages include: "Cannot find the specified server"
> * Some can connect via the IP, but not the DNS
> DANGEROUS, but working (WHY?)--
> * When I add the users to a completely unrelated AD group (with no
> particular access to SQL Server, AND I make group associated with the trusted
> connection members of the local Administrators group on the data box, THEN
> they can connect. If I remove the user from the unrelated group OR I remove
> the trusted group from the local Administrators group , they cannot connect.
> Of course, this is less than desirable. What happened to the security?
> HELP!!
> Mike--I see you live in Zurich. I once lived in Yverdon.
> --
> Graeme Martin
>

Parallel Install 2005 CTP & 2000 Leads to Connection Problems

Hey All,
More fun and games with SQL Server 2005.
Mike: thanks for your help before on the Beta. I was able to uninstall and
regain connectivity. I didn't have to wipe the box, but came close!!
Here is a new twist:
Previous situation:
- One data server
- 2 instances: SQL Server 2000 in a named instance; SQL Server 2005 Beta 2
in the default instance.
- Access to the legacy database (2000) is controlled through a trusted
connection, using an Active Directory Group (MSA Users). This group is
granted public role access with various pre-defined rights on the objects.
- Access to the beta is for a website we are developing, and is done
through a SQL Server connection. This connection is granted public role,
with limited access to objects--mostly stored procedure executions only.
- Everything running well.
Now:
- SQL Server Beta 2 expired.
- Uninstalled and cleaned SQL Server 2005 Beta 2.
- Installed SQL Server 2005 April CTP.
- Databases are running well, however:
- Trusted connections to the legacy database are all messed up: the Windows
group controlling access to the SQL 2000 database still exists, but members
of the group can no longer connect. Some weird behavior ensues:
* Some get an 18456. Messages include: "Cannot find the specified server"
* Some can connect via the IP, but not the DNS
DANGEROUS, but working (WHY?)--
* When I add the users to a completely unrelated AD group (with no
particular access to SQL Server, AND I make group associated with the truste
d
connection members of the local Administrators group on the data box, THEN
they can connect. If I remove the user from the unrelated group OR I remove
the trusted group from the local Administrators group , they cannot connect.
Of course, this is less than desirable. What happened to the security?
HELP!!
Mike--I see you live in Zurich. I once lived in Yverdon.
--
Graeme MartinI spoke with some folks at Microsoft who were very helpful. Just thought I
would share with you what we found--
1. The named instance (2000) wasn't showing up to users trying to connect:
* Check SQL Browser service is running.
* Check the following key in the registry:
HKLM/Software/Microsoft/Microsoft SQL Server/90/SQL Browser/SsrpListener
(REG_DWORD). This value needs to be (1).
2. The user was denied access from the trusted group. Run this SQL:
USE master
GO
SELECT name, denylogin, hasaccess FROM syslogins
-- denylogon should be 0; hasaccess should be 1 for your logins.
3. Use sp_grantlogin 'DOMAINNAME\username' to add logins. This SP will
ensure the proper fields are filled out.
4. Still can't figure out why the website won't show up, but I think it's an
unrelated issue.
Thanks all! Hope this helps you too!
Graeme Martin
"The Oracle" wrote:

> Hey All,
> More fun and games with SQL Server 2005.
> Mike: thanks for your help before on the Beta. I was able to uninstall an
d
> regain connectivity. I didn't have to wipe the box, but came close!!
> Here is a new twist:
> Previous situation:
> - One data server
> - 2 instances: SQL Server 2000 in a named instance; SQL Server 2005 Beta
2
> in the default instance.
> - Access to the legacy database (2000) is controlled through a trusted
> connection, using an Active Directory Group (MSA Users). This group is
> granted public role access with various pre-defined rights on the objects.
> - Access to the beta is for a website we are developing, and is done
> through a SQL Server connection. This connection is granted public role,
> with limited access to objects--mostly stored procedure executions only.
> - Everything running well.
> Now:
> - SQL Server Beta 2 expired.
> - Uninstalled and cleaned SQL Server 2005 Beta 2.
> - Installed SQL Server 2005 April CTP.
> - Databases are running well, however:
> - Trusted connections to the legacy database are all messed up: the Windo
ws
> group controlling access to the SQL 2000 database still exists, but member
s
> of the group can no longer connect. Some weird behavior ensues:
> * Some get an 18456. Messages include: "Cannot find the specified serve
r"
> * Some can connect via the IP, but not the DNS
> DANGEROUS, but working (WHY?)--
> * When I add the users to a completely unrelated AD group (with no
> particular access to SQL Server, AND I make group associated with the trus
ted
> connection members of the local Administrators group on the data box, THEN
> they can connect. If I remove the user from the unrelated group OR I remo
ve
> the trusted group from the local Administrators group , they cannot connec
t.
> Of course, this is less than desirable. What happened to the security?
> HELP!!
> Mike--I see you live in Zurich. I once lived in Yverdon.
> --
> Graeme Martin
>sql

Page Termination Crystal 8.5

I am using Crystal Reports 8.5 and connecting to a SQL Server 7.0 database through and OLE DB connection. I am running a stored procedure that returns close to 8 million records. This part works fine. My problem is during the process where the status bar states "Determining Page Count" the report will terminate prior to obtaining the end of the report. For example this morning the report terminated after 18+ hours on page 1755. This is a monthly report that is run and last month it had about 2000+ pages. The report has terminated anywhere from 1350 pages to 1760. There are no error messages. If you click on the button to go to the end of the report it will go to page 1. The page count remains Page 1 of 4,294,967,295 because it never completes pagination. Has anyone else experienced this problem?I found the solution to the problem. There was simply too many calculations going on for crystal to handle. The report was summing/grouping over 8 million records. I was able to move this to the stored procedure and eliminate the summing in crystal. The report runs much faster!!