jsaxon2
asked on
Execute SQL using ADO
I am accessing a SQL 6.5 database and looking for some tables to drop. I am unable to get the query to work. I get an error msg saying that says {"Invalid column name 'CNV_TPB105_CHARGE_DETAIL_ 3'." (1)}.
This is a simple SQL query. Please help. what do I need to change.
if exists (select * from sysobjects where id = object_id("CNV_TPB105_CHAR GE_DETAIL_ 3") and sysstat & 0xf = 3)
drop table CNV_TPB105_CHARGE_DETAIL_3
The code to execute it:
TESTHR(pConnection.CreateI nstance(__ uuidof(Con nection))) ;
hr = pConnection->Open(strSQLDB ,"","",NUL L);
pConnection->Execute(strEx ecString, NULL, NULL);
pConnection->Close();
This is a simple SQL query. Please help. what do I need to change.
if exists (select * from sysobjects where id = object_id("CNV_TPB105_CHAR
drop table CNV_TPB105_CHARGE_DETAIL_3
The code to execute it:
TESTHR(pConnection.CreateI
hr = pConnection->Open(strSQLDB
pConnection->Execute(strEx
pConnection->Close();
ASKER
I'm sorry, I copied the wrong SQL. It fails on the script below.
if exists (select * from sysobjects where id = object_id('dbo.CNV_TPB105_ CHARGE_DET AIL_3') and sysstat &
0xf = 3)
drop table CNV_TPB105_CHARGE_DETAIL_3
I've narrowed it down to the dbo. prefix that is causing the problem. Is there anyway around this so that I would not get the error. The script is exactly what SQL Generate Scripts function produces and I don't want to have to change all of my table drops.
if exists (select * from sysobjects where id = object_id('dbo.CNV_TPB105_
0xf = 3)
drop table CNV_TPB105_CHARGE_DETAIL_3
I've narrowed it down to the dbo. prefix that is causing the problem. Is there anyway around this so that I would not get the error. The script is exactly what SQL Generate Scripts function produces and I don't want to have to change all of my table drops.
Are you using single or double quotes? The first message implies that they are double, but they should be single, like in your second message. That's what the error implies as well.
how are you logging on the the database?
Are you loggin on as the dbo user, if not then you need to supply the owner prefix on tables you are accessing
Are you loggin on as the dbo user, if not then you need to supply the owner prefix on tables you are accessing
ASKER
The exact syntax of the SQL is in the second post. It is using single quotes.
I am logging in using sa.
Fails:
if exists (select * from sysobjects where id = object_id('dbo.CNV_TPB105_ CHARGE_DET AIL_3') and sysstat
&
0xf = 3)
drop table CNV_TPB105_CHARGE_DETAIL_3
Works:
if exists (select * from sysobjects where id = object_id('CNV_TPB105_CHAR GE_DETAIL_ 3') and sysstat & 0xf = 3)
drop table CNV_TPB105_CHARGE_DETAIL_3
I want to find out why it thinks I'm looking for a column when it is prefixed with the dbo. alias. Thanks for you time.
I am logging in using sa.
Fails:
if exists (select * from sysobjects where id = object_id('dbo.CNV_TPB105_
&
0xf = 3)
drop table CNV_TPB105_CHARGE_DETAIL_3
Works:
if exists (select * from sysobjects where id = object_id('CNV_TPB105_CHAR
drop table CNV_TPB105_CHARGE_DETAIL_3
I want to find out why it thinks I'm looking for a column when it is prefixed with the dbo. alias. Thanks for you time.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
and the tables
ASKER
The user 'sa' is the dbo. I have corrected the problem by using a different username and aliasing that user as 'dbo'. Also, I replaced the double quotes with the single quotes to help on some ALTER TABLE commands. I appreciate your time and knowledge.
Thank you,
John
P.S. If you could, please help explain why using the sa login does not work.
Thank you,
John
P.S. If you could, please help explain why using the sa login does not work.
object_id('CNV_TPB105_CHAR