SQL PassThru problem

[rewdee]

I'm trying to write an SQL Pass-thru query. I have set the connectionStr to:

ODBC;DSN=Camp Data;UID=Camp;PWD=****;DBQ=OSRSTST.WORLD;DBA=W;APA=T;PFC=1;TLO=0;

PWD is valid but not listed here

but I get an OBDC Fail messge with the table does not exist. What's driving me nuts is that I can link the table and run the same query on the linked table with this connect property in the MSysObjects table:

DSN=Camp Data;DBQ=OSRSTST.WORLD;DBA=W;APA=T;PFC=1;TLO=0;

Does anyone see what I'm doing wrong?

Thanks,
Rewdee

 

[VogonPoet]

Rewdee,

It sounds like it might be a problem with the SQL. What is the SQL you are passing through.

Michael

 

[rewdee]

Thanks Michael for the reply

I don't think it is the SQL because the SQL statement works on the linked table. Here is the SQL though:

INSERT INTO tblManCharges ( BusUnit, Dept, Company )
SELECT CAMP_MANUAL_CHARGES.BUSINESS_UNIT, CAMP_MANUAL_CHARGES.DEPARTMENT, CAMP_MANUAL_CHARGES.COMPANY
FROM CAMP_MANUAL_CHARGES
GROUP BY CAMP_MANUAL_CHARGES.BUSINESS_UNIT, CAMP_MANUAL_CHARGES.DEPARTMENT, CAMP_MANUAL_CHARGES.COMPANY;

Rewdee

 

[VogonPoet]

Rewdee,

I'm going to assume that tblManCharges is in the local database and not in SQL Server.

SQL is trying to update a table called tblManCharges in SQL. If you want to update the local table with a pass through query you'll need two queries. One to get the result set from SQL server and a second query that writes the results from the first into the local table. (I know that's kludgy).

I hope this is the answer.

Michael

 

[rewdee]

Thanks again Michael

You were right about the local table being tblManCharges ,however, I took out the INSERT statement and I am left with just a SELECT query but I still get the same ODBC error.

Keep trying. It was an idea I thought might work.

Thanks,
Rewdee

 

[VogonPoet]

Rewdee,

Do you have Query Analyzer on your desktop?

If so, try running the query from there. If it runs, then you know the problem is in the ODBC link. Of not, you know you have a problem with your SQL statement.

Michael