"Select * from ..." doesn´t work, "select code, descr from..." does, why?

I run this que开发者_如何学运维ry on SQL Server and it doesn't work:

SELECT * FROM dbo.marcas

but if I put at least one field in the query, it works.

SELECT code FROM dbo.marcas

I know it must be simple, but I can't find an answer.

Thansk

Most likely, someone else is updating that same table, and thus places certain locks on the table.

When you do a SELECT * ... those locks will cause a conflict and your query won't execute, while a SELECT (list of columns)...... will work (since it's not affected by the locks)

I'm answering my own question because I have found the answer by myself.

Using EMS Sql Manager 2008 for SQL Server I executed select * from marcas and have no results, just errors. But If I recreated the table, voila, it just worked fine !!!

So the problem was the way I created the tables in the server. After a while, I realized the command that created the table in Foxpro using ODBC was:

oerr = sqlexec(oconn, "ALTER TABLE ["+xtabla+"] ADD ["+borrar.field_name+"] "+tipo_campo(borrar.field_type, borrar.field_len, borrar.field_dec),"")

so changed it to:

oerr = sqlexec(oconn, "ALTER TABLE ["+xtabla+"] ADD ["+alltrim(borrar.field_name)+"] "+tipo_campo(borrar.field_type, borrar.field_len, borrar.field_dec),"")

that is, I just deleted the extra spaces right after the table name.

Thats all, "codigo" is not equal to "codigo ".

Thanks to all of you who tried to help me.

I beleve

One possibility would be if you have a computed column in the table that's generating an error when SQL Server attempts to compute it. Sample code:

create function dbo.Crash ()
returns int
as
begin
    return 1/0
end
go
create table dbo.cctest (
    Col1 int not null,
    Col2 int not null,
    CrashCol as dbo.Crash()
)
go
insert into dbo.cctest (Col1,Col2)
select 1,2 union all
select 3,4
go
select Col1 from dbo.cctest
go
select * from dbo.cctest
go

results:

Col1
----
1
3

(2 row(s) affected)

Col1  Col2  CrashCol
--------------------

(2 row(s) affected)
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

So the first select worked since it didn't access the fault computed column

I recommend running the query in a sql client other than EMS, in the hope that you can get an informative error message.

"La operación en varios pasos generó errores. Compruebe los valores de estado." -> "The multi-step operation generated errors. Check the status values."