Failed to Open a Rowset

[Tenloe]

I am using vb 6,Crystal XI and SQLServer 2000. I have a report that when using the CRAXDDRT. The sql that it is producing in the .sqlquerystring is:

SELECT "PG_CERT"."CANCELDAYS, "PG_CERT"."INSURERE"
FROM "PG_CERT" "PG_CERT"
WHERE "PG_CERT"."CERT_ID"=3
ORDER BY "PG_CERT"."CERT_ID"

I am getting the following error:

Failed to open a rowset.
Details: ADO Error Code: 0x80040e37
Source: Microsoft OLE DB Provider for SQL Server
Description: Invalid object name 'PG_CERT'.
SQL State: 42S02

Anyone have a clue on how to fix this? Is it is because it is using an alias table name? If so does anyone know how to change that? The query when copied over to SQL Server works fine.

 

[Turkbear]

Hi,
It appears as though the alias is the same as the real name so it should not be a problem..

Can you run that query in Sql analyzer ( or whatever SqlServer's direct query tool is called)

Does the user account for your connection object have access rights to that table?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Tenloe]

yes, it runs great in sql server. I just went in in the VB and changed the query to not use pg_cert everywhere like this:

SELECT CANCELDAYS, INSURERE FROM STARS.PG_CERT PG_CERT WHERE (CERT_ID = 3) ORDER BY CERT_ID

and VB took it fine so that seems to be the problem, how would I turn this off in Crystal or VB? Thanks for the help.

 

[DoubleDyno]

You might try adding the database name to the FROM statement.

eg.
SELECT "PG_CERT"."CANCELDAYS, "PG_CERT"."INSURERE"
FROM "DB1"."PG_CERT" "PG_CERT"
WHERE "PG_CERT"."CERT_ID"=3
ORDER BY "PG_CERT"."CERT_ID"

When building some additional commands, I have had to do it this way.

 

[Tenloe]

How would I do that without doing string manipulation in VB?

 

[synapsevampire]

First, test it in Query Analyzer.

If it does not execute, or in either case, switch to ODBC, which tends to be very forgiving.

-k

 

[Tenloe]

Thanks, that worked adding the database name. Here is how I did it if anyone needs to do it.

sDatabaseName = objReport.Database.Tables(i).LogOnDatabaseName
sLogOnUserId = objReport.Database.Tables(i).LogOnUserID
sName = objReport.Database.Tables(i).Location
sLoc = sDatabaseName & "." & sLogOnUserId & "." & sName
DBTable.Location = sLoc