Database Connector Error: ‘42S02:[Microsoft][ODBC SQL Server Driver][S

[Hviezdoslav]

Greetings,

I do not have access to the SQL Server.

I have a Crystal Report and in it is a sub report.

The main rpt has an Excel file (named Sheet1_ in Crystal Rpt even though on my desktop the name of the XLS file is different) and a SQL Server table linked (inner joined where two fields equal) in the Database Expert and this produces the proper results for this main report.

I want the sub rpt to use the same Excel file and the same SQL Server table. I am trying for the sub rpt to enter into Add Command the following to link the XLS file and the SQL Server table with the following code:

Select Sheet1_.Account_No From Sheet1_ X
Left Outer Join Pt_User_Defined_Fields P
On X.Account_No = P.Account
Where P.Account Is Null

When I click OK in the Add Command To Report for the sub report after entering the above query, I get the following error:

Database Connector Error: ‘42S02:[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name ‘Sheet1_’.[Database Vendor Code: 208]’

The real path on my PC and the real name of the Excel file is as follows:

C:\Users\michaelw\Desktop\My_CR_ShowPatientsNotInUserDefinedTbl\ Second_And_Last_Excel_File_For_Language.xls

The main report works I guess because I am linking (inner joining the XLS file and SQL Server table where each field equals) via the Database Expert.

When I try to use the Add Command for the sub report to query to show the accounts in the Excel file that do not have a row in the SQL Server table, I get the Database Connector Error: ‘42S02:[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name ‘Sheet1_’.[Database Vendor Code: 208]’.

Does anybody know by chance how I can resolve this? I would be very grateful.

 

[Hviezdoslav]

- - -
This is me again, the one who posted this thread to ask.

I tried the following in the Add Command under the path of the Excel file of the sub report:

SELECT `Sheet1_`.`Account_No`
FROM `Sheet1$` `Sheet1_`
LEFT OUTER JOIN Pt_User_Defined_Fields
ON `Sheet1_`.`Account_No`= Pt_User_Defined_Fields
WHERE Pt_User_Defined_Fields.Account Is Null

Now though I get the Database Connector Error: 'DAO Error Code: 0xce0 Source: DAO.Database Description: Join expression not supported.'

I tried the following in the Add Command under the SQL Server CRSS Database in the Database Expert of the sub report:

SELECT `Sheet1_`.`Account_No`
FROM `Sheet1$` `Sheet1_`
LEFT OUTER JOIN Pt_User_Defined_Fields
ON `Sheet1_`.`Account_No`= Pt_User_Defined_Fields
WHERE Pt_User_Defined_Fields.Account Is Null

I get the following error though: Failed to retrieve data from the database. Details: 42000:[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:Incorrect syntax near ''', [Database Vendor Code:170]

So I tried the following in the Add Command under the SQL Server CRSS Database in the Database Expert of the sub report:

Select Sheet1_.Account_No From Sheet1_ X
Left Outer Join Pt_User_Defined_Fields P
On X.Account_No = P.Account
Where P.Account Is Null

I got though the Database Connector Error: '42S02:[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'Sheet1_'.[Database Vendor Code:208]'

Please let me know if anybody has any idea on how I can do it.

Thanks very much.

Hviezdoslav
- - -

 

[Hviezdoslav]

This is me again. I am using Crystal Reports version XI. Hviezdoslav

 

[briangriffin]

I would wager that this report originally used a different Excel file as the data source. Then at some point the Set Datasource Location command was used to point to the current Excel file. Sometimes when you do this the name of the current location isn't updated, even though the report is using the correct data source. That makes it very difficult to know what's really going on.

Try saving the subreport independently, but if you're not familiar with how it's linked to the main report then make a note of that first. Then use the Database: Set Datasource Location to point the subreport to the correct Excel file. It would probably help to name the worksheet something meaningful, so you're not having to use Sheet1..

Once you have that working, reimport and relink it to the main report.

 

[Hviezdoslav]

Hello,

I have figured out how to do what I wanted.

In the main report, I have what I had in the first place. I connect the XLS file to the SQL Server table via the Database Expert and I linked the XLS file's Account field to the SQL Server table's Account field with Inner Join, Not Enforced, and = as the link type. This shows the account numbers in the XLS file that have rows in the SQL Server table and this shows the value in the Data field of that SQL Server table for each account number.

I created a sub report and put this sub rpt into the Crystal Report that has the main report. In the sub report, I used the Database Expert to link the Excel file's Account field to the SQL Server table's Account field using Left Outer Join, Not Enforced, and the = link type. In the sub report's Select Expert, I added the SQL Server table's Account field and then in the drop down I used Formula rather than Is Equal To or Is Any Value. In the textbox for the Formula for the SQL Server table's Account field, I typed the following:

IsNull({Pt_User_Defined_Fields.Account})

Now the sub report shows the account numbers in the XLS file that do NOT have any row in the SQL Server's Pt_User_Defined_Fields table.

So it seems that I had the right idea in the beginning when I was trying to use Add Commmand in the sub report to try in T-SQL something like the following but of course for the names of my XLS file and its field and for the names of my SQL Server table and its field:

Select c.CustomerId, O.OrderId

From Customers c

Left Outer Join Orders o

On c.CustomerId = o.CustomerId

Where rderId Is Null

I was on the right track but did not have success trying to code the T-SQl in the Add Command of the sub report within Crystal Reports. Were I not to have the need to use an Excel file and were I instead to be using two SQL Server tables with my same intention of showing in the sub report the accounts in the first SQL Server table that do not have any rows in the second SQL Server table, maybe my attempt to code the T-SQL in the Add Command of the sub report would have worked successfully.

In this case though, I got it to work in the sub report by linking the XLS file to the SQL Server table with Left Outer Join, Not Enforced, and the = link type; and then in the sub report's Select Expert adding the SQL Server table's Account field and using in the Formula drop down the code IsNull({Pt_User_Defined_Fields.Account}).

I thank you though for the response, Brian! I appreciate your attempt to help.

Hviezdoslav