another question

Hi guys,
please create a table in tempdb running following
USE tempdb
CREATE TABLE delete_me (c1 int, c2 int )
INSERT delete_me (c1, c2)
SELECT 1, 1 UNION SELECT 2, 2 UNION SELECT 3, 3 UNION SELECT 4, 4
Then running the script below you can get (I do) the error message :
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'c3'.
But sometimes it works. The workaround seems to be to wrap UPDATE statement
up into EXEC, - it works always. Why is that?
BEGIN TRAN
ALTER TABLE delete_me
ADD c3 int
-- EXEC ('UPDATE delete_me SET c3 = 0')
UPDATE delete_me SET c3 = 0
ALTER TABLE delete_me
ALTER COLUMN c3 int NOT NULL
ROLLBACK TRAN
Thanks
AlexThats quite a normal behaviour. Object resolution takes place if the
object is already know so, this will fail due to the non existing
column. Look for
http://msdn.microsoft.com/library/d...>
_07_5wa6.asp
"Note Deferred Name Resolution can only be used when you reference
nonexistent table objects. All other objects must exist at the time the
stored procedure is created. For example, when you reference an
existing table in a stored procedure you cannot list nonexistent
columns for that table."
HTH, Jens Suessmeyer.|||AlexM
What is your SQL Server version?
It worked fine on my workstation (SS2000,SP3,Personal Edition)
"AlexM" <alex_remove_this_mak@.telus.net> wrote in message
news:CHYDf.157789$AP5.28253@.edtnps84...
> Hi guys,
> please create a table in tempdb running following
> USE tempdb
> CREATE TABLE delete_me (c1 int, c2 int )
> INSERT delete_me (c1, c2)
> SELECT 1, 1 UNION SELECT 2, 2 UNION SELECT 3, 3 UNION SELECT 4, 4
> Then running the script below you can get (I do) the error message :
> Server: Msg 207, Level 16, State 1, Line 1
> Invalid column name 'c3'.
> But sometimes it works. The workaround seems to be to wrap UPDATE
> statement up into EXEC, - it works always. Why is that?
>
> BEGIN TRAN
> ALTER TABLE delete_me
> ADD c3 int
> -- EXEC ('UPDATE delete_me SET c3 = 0')
> UPDATE delete_me SET c3 = 0
> ALTER TABLE delete_me
> ALTER COLUMN c3 int NOT NULL
>
> ROLLBACK TRAN
>
> Thanks
> Alex
>|||Thanks Jens, that note apparently slipped my mind ;-)
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1138778592.061349.312360@.o13g2000cwo.googlegroups.com...
> Thats quite a normal behaviour. Object resolution takes place if the
> object is already know so, this will fail due to the non existing
> column. Look for
> http://msdn.microsoft.com/library/d...
es_07_5wa6.asp
> "Note Deferred Name Resolution can only be used when you reference
> nonexistent table objects. All other objects must exist at the time the
> stored procedure is created. For example, when you reference an
> existing table in a stored procedure you cannot list nonexistent
> columns for that table."
>
> HTH, Jens Suessmeyer.
>|||ss2000, enterprise & developer, sp4
Jens pointed to the note which explains clearly why it happens. What it
worries me though that this behaviour is not consistent. Most of the tine it
acts according to BOL and that particular note, but sometimes the resolution
stage comes through with flying colors when referencing a missing column for
existing table. But this is a bit different story...
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eL4xwIwJGHA.3332@.TK2MSFTNGP11.phx.gbl...
> AlexM
> What is your SQL Server version?
> It worked fine on my workstation (SS2000,SP3,Personal Edition)
>
> "AlexM" <alex_remove_this_mak@.telus.net> wrote in message
> news:CHYDf.157789$AP5.28253@.edtnps84...
>

another Freetexttable query problem

Hi All: Last month, I was looking for way to query 3 FT tables
simultaneously.
I found that a UNION with 3 separate queries worked, but someone pointed out
that duplicates would occur (and they did).
It was suggested that I use a query like:
select <columns>
from table1
inner join freetexttable(table1,*, @.srchstring) ft1
on ft1.key = table1.<key column>
inner join table2
on table2.<common key> = table1.<common key>
inner join freetexttable(table2,*, @.srchstring) ft2
on ft2.key = table2.<key column>
I did get that to work, but the problem is the query won't return any
matches if the searchstring is not found in both FT tables.
Going back to my original goal, I have 3 tables:
products
manufacturers
skus
And these tables are all in the FT cat.
I want to be able to search for a term like "blue" or "5180-1" (a sku) and
return a match from any table in the FT cat.
Is there any way to do it with the nested join query above? Or is there
another way to do it?
Before I started using the freetexttable query, I was using some fugly code
like:
SELECT <columns> FROM products WHERE <columns> LIKE '%<searchstr>%'
<if no results then>
SELECT <columns> FROM manufacturers WHERE <columns> LIKE '%<searchstr>%'
...do I need to do something like that, but instead concatenate the results
from each query into a temp table?
Thanks for any advice!
Hello geek-y-guy,
If you use a left join your query should work
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> Hi All: Last month, I was looking for way to query 3 FT tables
> simultaneously.
> I found that a UNION with 3 separate queries worked, but someone
> pointed out that duplicates would occur (and they did).
> It was suggested that I use a query like:
> select <columns>
> from table1
> inner join freetexttable(table1,*, @.srchstring) ft1
> on ft1.key = table1.<key column>
> inner join table2
> on table2.<common key> = table1.<common key>
> inner join freetexttable(table2,*,
> @.srchstring) ft2
> on ft2.key = table2.<key
> column>
> I did get that to work, but the problem is the query won't return any
> matches if the searchstring is not found in both FT tables.
> Going back to my original goal, I have 3 tables:
> products
> manufacturers
> skus
> And these tables are all in the FT cat.
> I want to be able to search for a term like "blue" or "5180-1" (a sku)
> and return a match from any table in the FT cat.
> Is there any way to do it with the nested join query above? Or is
> there another way to do it?
> Before I started using the freetexttable query, I was using some fugly
> code like:
> SELECT <columns> FROM products WHERE <columns> LIKE
> '%<searchstr>%'
> <if no results then>
> SELECT <columns> FROM manufacturers WHERE <columns> LIKE
> '%<searchstr>%'
> ...do I need to do something like that, but instead concatenate the
> results from each query into a temp table?
> Thanks for any advice!
>
|||Thanks Simon, but are you saying a left join for every join in the query?
for the 3 tables there would be 6 joins in total.
"Simon Sabin" <SimonSabin@.noemail.noemail> wrote in message
news:62959f1a4eb038c9236201101b77@.msnews.microsoft .com...
> Hello geek-y-guy,
> If you use a left join your query should work
>
> Simon Sabin
> SQL Server MVP
> http://sqlblogcasts.com/blogs/simons
>
>