Invalid object name...

Hello all,
however, this is my first question to this news. I am working with RS SP1,
and have question. I have example procedure:
CREATE PROCEDURE GEGE_test_a
@.ord SQL_VARIANT AS
SET NOCOUNT ON
CREATE TABLE #table (ID SQL_VARIANT)
INSERT INTO #table(ID) VALUES (@.ord)
SELECT * FROM #table
DROP TABLE #table
GO
When I want add DataSet with this procedure (EXECUTE GEGE_test_a @.OrderID) I
get following error:
Could not generate a list of fields for the querry...
Invalid object name #table
Ofcourse, this procedure works good in Query Analyser. Anyone has idea, why
this is not working ?
--
Ing. Branislav GerzoI got it to work w/o a problem. However, I do see the same error if I enter
the (EXECUTE GEGE_test_a @.OrderID) statement in the dataset creation dialog
box while attempting to define the dataset it uses. Try actually executing
the procedure with a parameter or passing in a static value from the Generic
Query Designer data window once you've defined the dataset. If you use
static value like (EXECUTE GEGE_test_a '11'), simply change it after to use
your query parm.
--
-- "This posting is provided 'AS IS' with no warranties, and confers no
rights."
jhmiller@.online.microsoft.com
"Ing. Branislav Gerzo" <IngBranislavGerzo@.discussions.microsoft.com> wrote
in message news:0B87A6E5-C4C2-452E-A601-5B76C6DA3D75@.microsoft.com...
> Hello all,
> however, this is my first question to this news. I am working with RS SP1,
> and have question. I have example procedure:
> CREATE PROCEDURE GEGE_test_a
> @.ord SQL_VARIANT AS
> SET NOCOUNT ON
> CREATE TABLE #table (ID SQL_VARIANT)
> INSERT INTO #table(ID) VALUES (@.ord)
> SELECT * FROM #table
> DROP TABLE #table
> GO
> When I want add DataSet with this procedure (EXECUTE GEGE_test_a @.OrderID)
> I
> get following error:
> Could not generate a list of fields for the querry...
> Invalid object name #table
> Ofcourse, this procedure works good in Query Analyser. Anyone has idea,
> why
> this is not working ?
> --
> Ing. Branislav Gerzo|||Use a table ariable instead of the temp table:
ALTER PROCEDURE GEGE_test_a
@.ord SQL_VARIANT AS
SET NOCOUNT ON
DECLARE @.table TABLE(ID SQL_VARIANT)
INSERT INTO @.table(ID) VALUES (@.ord)
SELECT * FROM @.table
GO
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Ing. Branislav Gerzo" <IngBranislavGerzo@.discussions.microsoft.com> wrote
in message news:0B87A6E5-C4C2-452E-A601-5B76C6DA3D75@.microsoft.com...
> Hello all,
> however, this is my first question to this news. I am working with RS SP1,
> and have question. I have example procedure:
> CREATE PROCEDURE GEGE_test_a
> @.ord SQL_VARIANT AS
> SET NOCOUNT ON
> CREATE TABLE #table (ID SQL_VARIANT)
> INSERT INTO #table(ID) VALUES (@.ord)
> SELECT * FROM #table
> DROP TABLE #table
> GO
> When I want add DataSet with this procedure (EXECUTE GEGE_test_a @.OrderID)
I
> get following error:
> Could not generate a list of fields for the querry...
> Invalid object name #table
> Ofcourse, this procedure works good in Query Analyser. Anyone has idea,
why
> this is not working ?
> --
> Ing. Branislav Gerzo|||Dejan Sarka [DS], on Friday, October 29, 2004 at 17:17 (+0200)
contributed this to our collective wisdom:
DS> Use a table ariable instead of the temp table:
DS> ALTER PROCEDURE GEGE_test_a
DS> @.ord SQL_VARIANT AS
DS> SET NOCOUNT ON
DS> DECLARE @.table TABLE(ID SQL_VARIANT)
DS> INSERT INTO @.table(ID) VALUES (@.ord)
DS> SELECT * FROM @.table
DS> GO
thanks, I was afraid that someone will answer like this. Ofcourse,
this works, but my problem is, that in my situation I have to fill
@.table_var with result of another procedure. And I found this:
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q305977&
A3:
1. Tables variables cannot be used in a INSERT EXEC or SELECT INTO
statement.
2. You cannot use the EXEC statement or the sp_executesql stored
procedure to run a dynamic SQL Server query that refers a table
variable, if the table variable was created outside the EXEC statement
or the sp_executesql stored procedure. Because table variables can be
referenced in their local scope only, an EXEC statement and a
sp_executesql stored procedure would be outside the scope of the table
variable. However, you can create the table variable and perform all
processing inside the EXEC statement or the sp_executesql stored
procedure because then the table variables local scope is in the EXEC
statement or the sp_executesql stored procedure.
Ofcourse, i'd like to use table variables, they are fast, they are
cool. But, how to fill them with result of another procedure ?
I can't cheat them in any way, I have only one idea for that -
procedure which fill @.tabl_var using cursors. But I hope there is
better way do this.
Dejan, please help.
--
...m8s, cu l8r, Brano.
[Alright, who g r e a s e d the tagline?.]|||John H. Miller [JHM], on Friday, October 29, 2004 at 11:14 (-0400)
typed the following:
JHM> I got it to work w/o a problem. However, I do see the same error if I
enter
JHM> the (EXECUTE GEGE_test_a @.OrderID) statement in the dataset creation
dialog
JHM> box while attempting to define the dataset it uses.
anyone knows, why this error occurs ? I can't use temp tables in my
procedures ?
JHM> Try actually executing
JHM> the procedure with a parameter or passing in a static value from the
Generic
JHM> Query Designer data window once you've defined the dataset. If you use
JHM> static value like (EXECUTE GEGE_test_a '11'), simply change it after to
use
JHM> your query parm.
No, it also doesn't work, I get the same message back. (could not
generate a list...). I really don't know why, it is known bug, or
what?
Thanks a lot. My all work stops on this :(((
--
...m8s, cu l8r, Brano.
[Applaflammaphobia: A vacation fear that the house will bu]