Why?

[Lafor]

Can any 1 tell me please why this would not work in SQL Server????

declare @ls_SqlStr varchar(50)
set @ls_SqlStr = "ALTER TABLE emp_Final ADD Region_Name VARCHAR(50) NULL"
exec (@ls_SqlStr)

Invalid Column Name????

Thanks much....

[szlamany]

Yeah - your string is 54 characters long.

Increase the varchar(50) to varchar(100)

[techgnome]

Why are you doing it like that?

Wouldn't it be simpler to jsut run the ALTER Table?

But to answer your question: Because @ls_SqlStr was defined with a length of 50, which means that "ALTER TABLE emp_Final ADD Region_Name VARCHAR(50) NULL" Gets cut off: "ALTER TABLE emp_Final ADD Region_Name VARCHAR(50)" which becomes an incomplete definition.

TG

[Lafor]

U guys are just good....
Why was I doing it like that?

I was putting it as part of a stored procedure...
But come to think of it... I can't remember.. it's not necessary.. of course...
thanks a mil, guys...

[Lafora]

Hello Guys....

...The stored procedure failed with the Alter Table saying Invakid column name 'Region_Name'..

That's probably why.... I tried to incorporate it using exec(string)

I am not sure... why

but any help again is appreciated

[szlamany]

Go into QUERY ANALYZER and execute the statement right in a QUERY window.

If you get that error, then I would venture to guess that the column is already in the table.

[techgnome]

I shoulda seen it earlier.... you need to tell it WHAT you are adding:
ALTER TABLE emp_Final ADD COLLUMN Region_Name VARCHAR(50)

TG

[szlamany]

TG - boy, I hate syntax errors - missed that one myself altogether...

[Lafor]

I did not have to specify column at all and it seems to take it...
I just realized that if I run the stored procedure first time and the resulting table is created and making any change to the stored procedure and trying to compile it, the stored proc will not compile saying "invalid Column" ... It looks like the ALTER TABLE is really doing a live check on the table and noticing that the column already exists and spitting out this error... I say this because if I drop the table, then I can recompile the stored proc...

[Lafor]

The problem is that even running after the table is created the first time... generates the same result as well...

[vb_dba]

How are you creating this table?

[Lafor]

select * from some table into
where..... etc...

[vb_dba]

Ok, so when you run the stored procedure, you are creating the table. I'm guessing, later in the stored procedure, you try to query the table you created with the Select....Into statement. I'm also speculating that you have logic that either does/does not run the alter table statement.

If that's the case then, as you speculated earlier, if the table exists already (after you ran the SP the first time), when you try to recompile the sp, if the column doesn't exist, it throws the error. The reason however, is not because of your alter table statement, it's because you have a select statement, again I'm guessing, that queries the table and tries to retrieve the Region_Name column. Since the column doesn't already exist in the table, it throws the error. I really hope this makes sense.

Without looking at the entire procedure to see how it flows, that's my guess.

One way around this is to create the column when you create the table:

Select field1, field2, ..., SPACE(50)'Region_Name'
From ...
Into ...

[Lafor]

very good but does not work for me because the next part of the proc does this:

Update temp_Final
Set Region_Name = c.Region_Name
From Regions c
Where c.ZIP = temp_Final.vchZip


even with a case statement

set Region_Name = case when len(trim(c.Region_Name)) = 0 null
etc...

can't get the desired data...

[vb_dba]

If this is just a temporary table to be used by the Procedure and won't be used anywhere else, then why not declare it as a temporary table?

Select ...
Into #temp_Final
...

or

Create Table #temp_Final(...)

This would help resolve the compilation issue you are having since SQL Server would not see a physical table in sysobjects with the name temp_Final.

Would if possible to post the proc?

[Lafor]

That final table is needed every Monday morning... A job is scheduled to run this stored proc... So a temp table.... is not appropriate here

Also, just realized that if I schedule the SQL itself and every section separated with

go

then it does it

[szlamany]

GO is for query analyzer only - it means "build a packet" to send to the server.

That means you will be using QA, or OSQL/ISQL command line programs only for this...

[techgnome]

Originally posted by szlamany
GO is for query analyzer only - it means "build a packet" to send to the server.

That means you will be using QA, or OSQL/ISQL command line programs only for this...

OR DTS.... I use GO in my DTS SQL commands.....

TG

[szlamany]

My point being that it wasn't going to be a SPROC - like he talked about earlier

BTW - I get the feeling that this post hasn't been explained to us good enough anyway

[Lafor]

Szlamany is right... I did not give enough explanation and I am sorry about that... I had worked on this a few months ago and left it to tackle something else...

I'd like to thank all of you for your prompt response... It is a good thing to know of professionals like you out there who are first very good and most important of all are so willing to help in sharing their expertise. If there is anything that's left in my initial love for this profession... It is that.

Thanks a lot guys...