SQL Executes in studio, not in app

So I have a SQL statement that runs fine in Managment Studio, but crashes in my app with "Invalid column name" for every column in the query. This is a programatically generated query.

I'm analyzing it in Management Studio 2005, but the database is on a SQL 2000 server.

Obviously I can't go into much detail due to NDAs and such, but here is an abbreviated version of the SQL query (Syntax intact, only modified field names and such):

select top 5
[field label] = [fld_lbl],
[field label2] = [fld_lbl2],
[field label3] = [fld_lbl3]
--table
from *TABLE*
--conditions
where convert(varchar,[fld_lbl]) Not LIKE '%90210%'
AND convert(varchar,[fld_lbl2]) = 0
--Sorts
order by convert(varchar,[fld_lbl]) asc,
convert(varchar,[fld_lbl3]) asc

...Help me! Thanks.

 

Missing 2 square brackets in where and order by around field names. Welcome to typing I will be your tutor for today

odujosh wrote:

Missing three square brackets in where and order by around field names.

My bad, I must have taken them out when I edited out the field names. They're there.

SlackmasterK wrote:



odujosh wrote: Missing three square brackets in where and order by around field names.

My bad, I must have taken them out when I edited out the field names. They're there.

Why are you doing conversions of the data at Retrieve time and not Set time?

And the column names don't seem very specific.

Beleive he is worried the names are super secret and would break his NDA (Non Disclousure Agreement)

And it AS if you want to alias a field in the Select clause.

W3bbo wrote:



SlackmasterK wrote:  odujosh wrote: Missing three square brackets in where and order by around field names. My bad, I must have taken them out when I edited out the field names. They're there.

Why are you doing conversions of the data at Retrieve time and not Set time?

And the column names don't seem very specific.

The guy who set up the back-end decided to make everything a varchar because "it's easier this way", so I have to work around it.

odujosh wrote:

And it AS if you want to alias a field in the Select clause.

Thanks, but that didn't fix it.

SlackmasterK wrote:

The guy who set up the back-end decided to make everything a varchar because "it's easier this way", so I have to work around it.

Yegods man. Have you spoken to the higher-uppers and asked for more time to fix this issue?

W3bbo wrote:

Yegods man. Have you spoken to the higher-uppers and asked for more time to fix this issue?

Do you think that might be my issue? Why then does it work in Management Studio?

No, I haven't spoken with the powers that be; the aforementioned person is still here and in a position of some influence

SlackmasterK wrote:


The guy who set up the back-end decided to make everything a varchar because "it's easier this way", so I have to work around it.

Death is too good for the man. Did he grow up with python or PHP?

Matthew van Eerde wrote:

Hmmm...

Try

select top 5
fld_lbl AS "Field Label",
fld_lbl2 AS "Field Label",
fld_lbl3 AS "Field Label"
--table
from *TABLE*
--conditions
where CAST(fld_lbl AS varchar(20)) Not LIKE '%90210%'
AND CAST(fld_lbl2 AS varchar(20)) = 0 -- huh?
--Sorts
order by CAST(fld_lbl AS varchar(20)) asc,
CAST(fld_lbl3 AS varchar(20)) asc

Tried that; it still says invalid column name for each field. A copy and paste still works in Management Studio.

For "huh?", a condition can be added by the user to specify whether something is a specific value... SQL doesn't seem to mind that part.  

evildictaitor wrote:

Death is too good for the man. Did he grow up with python or PHP?

W3bbo wrote:

Some other useless coffeehouse crap...

The database is created from a DB2 text dump (can't change that, not gonna try, like my pay rate, etc) and is changed frequently, without my consent or knowledge, absolutely nothing I can do about that. My requirement is to eliminate my position so I can go on doing other, better things with my time, since the department in question doesn't seem to give a crap about data integrity, or a notification period for downstream, mid-range groups.

So, I have to base the data off the text dump, since they don't document ANYTHING. As such, I had to use a versitile field, and leave the majority of the work arounds to the front-end...but with a name like Slackmaster, honestly, what kind of service should I have expected, and btw, wth is a Slackmaster anyway?

On to the first comment... Grew up on BASIC, moved to VB, then VB.NET, and don't ever, ever touch the languages endorsed by people who look like Grizzly Adams, don't bathe, and believe that Microsoft owns a small island in the pacific that is really a tactical launch bunker.

Back on track, do try to keep you're answers to the point, if you don't have a solution and want to make small talk, hit up the Coffeehouse. I can tell you this, the problem isn't with his SQL statement, I ran the same thing through a typical datagrid and it returned fine.

Matthew van Eerde wrote:

Hmmm...

What user does the app connect as, and what user does studio connect as?

EDIT: the reason I ask is because if you do

...
FROM
    Table
...

as two different users (sam and joe)...

then sam will pull from
    FROM sam.Table

and joe will pull from
    FROM joe.Table

One possible explanation of the error is that both tables exist; that sam.Table is the one you're looking for; and that joe.Table has different columns.

Just for fun, what does

SELECT * FROM Table

return, from the app perspective?  (I'm assuming you have sufficient control over the app to be able to execute such a statement.)

I'm logging on as the same custom account as the application is - It was given connection rights to avoid that specific issue. It has full rights to the database.

The table in question exists only once, and all tables are sharing the same schema as the SQL account in use.

ixdatul wrote:

[B]ut with a name like Slackmaster, honestly, what kind of service should I have expected, and btw, wth is a Slackmaster anyway?

I don't exactly go by that name at work. It helps me remind myself how far I've come.

As for the etymology of that idiom, when I worked at call center supporting Prodigy DSL (yechh), I was awarded the title by another employee called the "Call Time Nazí". He noticed that I did an exceptional job of barely meeting the metrics. They paid me $7/hr, and they got $7/hr worth.

Matthew van Eerde wrote:

How does the app connect?

As is the pattern with ADO.NET, I use a SqlAdapter. In this specific instance, I'm using a tSQL command as opposed to the de facto sprocs due to the difficulty in parameterizing the SQL Statement, which is actually stored elsewhere in SQL due to the nature of the application.

The SQLException is thrown immediately after calling SqlAdapter.Fill(DataTable).

[edit]: This seems a pertinent addition: When I alter my ConnectionStrings to connect to a server running identical databases on Sql Server 2005, it works fine. It only throws me an Exception when i connect to a Sql Server 2000.

"A Sql server 2000"?
"A Sql 2000 Server"?
"A 2000 Sql Server"?
"A Sql server 2000 server"?