Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Execute SQL using ADO

Posted on 2001-06-19
8
Medium Priority
?
230 Views
Last Modified: 2010-04-02
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_CHARGE_DETAIL_3") and sysstat & 0xf = 3)
     drop table CNV_TPB105_CHARGE_DETAIL_3

The code to execute it:
          TESTHR(pConnection.CreateInstance(__uuidof(Connection)));
          hr = pConnection->Open(strSQLDB,"","",NULL);
          pConnection->Execute(strExecString, NULL, NULL);
          pConnection->Close();
0
Comment
Question by:jsaxon2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 2

Expert Comment

by:missionImpossible
ID: 6206342
maybe single quotes instead of "?
object_id('CNV_TPB105_CHARGE_DETAIL_3')
0
 
LVL 3

Author Comment

by:jsaxon2
ID: 6206600
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_DETAIL_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.
0
 
LVL 8

Expert Comment

by:mnashadka
ID: 6208517
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.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Expert Comment

by:MattC
ID: 6209722
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
0
 
LVL 3

Author Comment

by:jsaxon2
ID: 6210197
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_DETAIL_3') and sysstat

0xf = 3)
   drop table CNV_TPB105_CHARGE_DETAIL_3


Works:
if exists (select * from sysobjects where id = object_id('CNV_TPB105_CHARGE_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.
0
 
LVL 1

Accepted Solution

by:
MattC earned 400 total points
ID: 6210485
is 'sa' the database owner of that DB
0
 
LVL 1

Expert Comment

by:MattC
ID: 6210554
and the tables
0
 
LVL 3

Author Comment

by:jsaxon2
ID: 6211841
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.
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When writing generic code, using template meta-programming techniques, it is sometimes useful to know if a type is convertible to another type. A good example of when this might be is if you are writing diagnostic instrumentation for code to generat…
Introduction This article is a continuation of the C/C++ Visual Studio Express debugger series. Part 1 provided a quick start guide in using the debugger. Part 2 focused on additional topics in breakpoints. As your assignments become a little more …
The goal of the video will be to teach the user the difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…
The viewer will learn how to pass data into a function in C++. This is one step further in using functions. Instead of only printing text onto the console, the function will be able to perform calculations with argumentents given by the user.

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question