bug with TABLE variable??

Consider the following code but don't think about what it's supposed to do
as I've simplified it a lot.
The error I get is a syntax error when I try to save my proc so what this
proc is doing is not important.
DECLARE @.grpIds TABLE (grp_id int PRIMARY KEY)
INSERT @.grpIds SELECT grp_id FROM tab_grp
DELETE
@.grpIds
FROM
@.grpIds
INNER JOIN tab_rub_deny
ON tab_rub_deny.grp_id = @.grpIds.grp_id
Error:
@.grpIds must be declared
If I replace the table variable by a normal table, there's no error any
more.
It seems to be a bug.
Should I use a temporary table then?
Thanks
HenriYou have to alias the table variable in the from clause, then you can
reference it in other parts of your statement:
DELETE
g
FROM
@.grpIds AS g
INNER JOIN tab_rub_deny
ON tab_rub_deny.grp_id = g.grp_id
--
Jacco Schalkwijk
SQL Server MVP
"Henri" <hmfireball@.hotmail.com> wrote in message
news:u7f%23PkS4EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Consider the following code but don't think about what it's supposed to do
> as I've simplified it a lot.
> The error I get is a syntax error when I try to save my proc so what this
> proc is doing is not important.
> DECLARE @.grpIds TABLE (grp_id int PRIMARY KEY)
> INSERT @.grpIds SELECT grp_id FROM tab_grp
> DELETE
> @.grpIds
> FROM
> @.grpIds
> INNER JOIN tab_rub_deny
> ON tab_rub_deny.grp_id = @.grpIds.grp_id
> Error:
> @.grpIds must be declared
> If I replace the table variable by a normal table, there's no error any
> more.
> It seems to be a bug.
> Should I use a temporary table then?
> Thanks
> Henri
>
>|||> DELETE
> @.grpIds
> FROM
> @.grpIds
> INNER JOIN tab_rub_deny
> ON tab_rub_deny.grp_id = @.grpIds.grp_id
Please see http://www.aspfaq.com/2475
Of particular interest:
"Table variables must be referenced by an alias, except in the FROM clause.
Consider the following two scripts: " [...]
--
http://www.aspfaq.com/
(Reverse address to reply.)
> Error:
> @.grpIds must be declared
> If I replace the table variable by a normal table, there's no error any
> more.
> It seems to be a bug.
> Should I use a temporary table then?
> Thanks
> Henri
>
>|||It works now :-)
Thanks a lot for your help,
and thanks for the interesting resource link too :-)
Henri
"Henri" <hmfireball@.hotmail.com> a écrit dans le message de
news:u7f%23PkS4EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Consider the following code but don't think about what it's supposed to do
> as I've simplified it a lot.
> The error I get is a syntax error when I try to save my proc so what this
> proc is doing is not important.
> DECLARE @.grpIds TABLE (grp_id int PRIMARY KEY)
> INSERT @.grpIds SELECT grp_id FROM tab_grp
> DELETE
> @.grpIds
> FROM
> @.grpIds
> INNER JOIN tab_rub_deny
> ON tab_rub_deny.grp_id = @.grpIds.grp_id
> Error:
> @.grpIds must be declared
> If I replace the table variable by a normal table, there's no error any
> more.
> It seems to be a bug.
> Should I use a temporary table then?
> Thanks
> Henri
>
>

bug with TABLE variable??

Consider the following code but don't think about what it's supposed to do
as I've simplified it a lot.
The error I get is a syntax error when I try to save my proc so what this
proc is doing is not important.
DECLARE @.grpIds TABLE (grp_id int PRIMARY KEY)
INSERT @.grpIds SELECT grp_id FROM tab_grp
DELETE
@.grpIds
FROM
@.grpIds
INNER JOIN tab_rub_deny
ON tab_rub_deny.grp_id = @.grpIds.grp_id
Error:
@.grpIds must be declared
If I replace the table variable by a normal table, there's no error any
more.
It seems to be a bug.
Should I use a temporary table then?
Thanks
Henri
You have to alias the table variable in the from clause, then you can
reference it in other parts of your statement:
DELETE
g
FROM
@.grpIds AS g
INNER JOIN tab_rub_deny
ON tab_rub_deny.grp_id = g.grp_id
Jacco Schalkwijk
SQL Server MVP
"Henri" <hmfireball@.hotmail.com> wrote in message
news:u7f%23PkS4EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Consider the following code but don't think about what it's supposed to do
> as I've simplified it a lot.
> The error I get is a syntax error when I try to save my proc so what this
> proc is doing is not important.
> DECLARE @.grpIds TABLE (grp_id int PRIMARY KEY)
> INSERT @.grpIds SELECT grp_id FROM tab_grp
> DELETE
> @.grpIds
> FROM
> @.grpIds
> INNER JOIN tab_rub_deny
> ON tab_rub_deny.grp_id = @.grpIds.grp_id
> Error:
> @.grpIds must be declared
> If I replace the table variable by a normal table, there's no error any
> more.
> It seems to be a bug.
> Should I use a temporary table then?
> Thanks
> Henri
>
>
|||> DELETE
> @.grpIds
> FROM
> @.grpIds
> INNER JOIN tab_rub_deny
> ON tab_rub_deny.grp_id = @.grpIds.grp_id
Please see http://www.aspfaq.com/2475
Of particular interest:
"Table variables must be referenced by an alias, except in the FROM clause.
Consider the following two scripts: " [...]
http://www.aspfaq.com/
(Reverse address to reply.)

> Error:
> @.grpIds must be declared
> If I replace the table variable by a normal table, there's no error any
> more.
> It seems to be a bug.
> Should I use a temporary table then?
> Thanks
> Henri
>
>
|||It works now :-)
Thanks a lot for your help,
and thanks for the interesting resource link too :-)
Henri
"Henri" <hmfireball@.hotmail.com> a crit dans le message de
news:u7f%23PkS4EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Consider the following code but don't think about what it's supposed to do
> as I've simplified it a lot.
> The error I get is a syntax error when I try to save my proc so what this
> proc is doing is not important.
> DECLARE @.grpIds TABLE (grp_id int PRIMARY KEY)
> INSERT @.grpIds SELECT grp_id FROM tab_grp
> DELETE
> @.grpIds
> FROM
> @.grpIds
> INNER JOIN tab_rub_deny
> ON tab_rub_deny.grp_id = @.grpIds.grp_id
> Error:
> @.grpIds must be declared
> If I replace the table variable by a normal table, there's no error any
> more.
> It seems to be a bug.
> Should I use a temporary table then?
> Thanks
> Henri
>
>

BUG REPORT: ReadWriteVariables different between Script Task and Script Component

I have just spent two hours banging my head off the wall to figure this one out... wondering if I had a problem with variable scope or something...

I had a Script Task which was taking a few package level variables to use in the script.

So I put the list of variables in the ReadWriteVariables property.

Specified them like this:

"User::FileBeingProcessed, User::FileSource"

And all was well....

Then later on decided to use the same set of variables in a Script Component contained in a Data Flow.

And kept kept getting a "..variable not found.." error dialog when I clicked on Design Script.

Finally the solution came to me: Spaces in the list of variables !

In a script task you can have a space after a comma in the list... but in a script component you CAN'T !!

So what works in a Script Task is :

Task : "User::FileBeingProcessed, User::FileSource"

But that will not work in a Script Component...

See the difference?:

Task :"User::FileBeingProcessed, User::FileSource"
Component:"User::FileBeingProcessed,User::FileSource"

I know the BOL says to use a comma separated list.. but.. some consistency would be nice...

PJ

I agree..that's a bit...err...cra@.p!

I always thought both used the syntax without a space in there and hence that what I always use - and hence I never have a problem with it.

Bug it! http://lab.msdn.microsoft.com/productfeedback/default.aspx

-Jamie

|||

Oh yeah, if you worked without the spaces you'd never see the problem... its just if you're an awkward fecker like me... heh heh...

Very misleading tho.. I was thrashing around for ages worried about scope (since the Script component is only down inside a data flow) and then applying service packs.. etc...

Still.. yer eventually glad just to find out yer not going crazy...

heh,

PJ