asterisk agony

[OhioSteve]

We maintain several applications with asp/html on the front end and SQL Server on the back end. Frequently our pages construct sql statements in asp and pass them to the db. Usually everything works well.

However, on rare occasions I add a field to a table. After that, I notice that sqls with the asterisk do NOT grab the new fields. Supposedly, the asterisk means "all fields".

This is driving me insane!! Why is this happening??

 

[donutman]

I doubt that it's a SQL Server problem. My guess is the coding of the app. Some controls retrieve the schema at design time so that may be an issue.
-Karl

 

[OhioSteve]

Do you mean something like global.asa? Is there something in there that fetches the schema periodically?

 

[SQLSister]

It is considered a poor practice to use Select *. You should never return more data than you actually need at the time or your application will be sluggish and slow. And never ever use * when doiing a select with one or more joins as then you are definietely returning at least one field twice.

 

[OhioSteve]

Okay, but why does it stop working when we add more fields?

 

[donutman]

Try it without the * and my guess is that it will continue to be a problem when you add a column. Another words if you retrieve your data via a SP, just adding the new column name may and probably will not update the app.
As far as your question about the global.asa, sorry I can't help. I do very little ASP programming, but in VB window apps it is usually necessary to recompile the app if the schema changes.
-Karl

 

[OhioSteve]

Compiling is not a concern here. We don't compile our pages.

If you name specific fields, it continues to retrieve those fields after the schema changes (assuming that those fields still exist, obviously). An asterisk is supposed to retrieve ALL fields. But it does not retrieve the new fields. So to get the new fields, you must shift from using the asterisk to specifically naming all of the fields. I have seen this happen several times...I am certain that it IS really happening.