Invokation of a stored procedure from an Integration Services package

Is it possible to execute a stored procedure from an Integration Services package? I see that its possible to enter sql commands that can be run but when a command to execute a stored procedure is entered the system cannot find the stored procedure (eventhough 'use mydbname' preceded it.

thx,

Marilyn

You certainly can execute a stored procedure, but we'll need some more information to help.

Are you using the Execute SQL task in SSIS? Is the database SQL Server? Does the account your are developing under have permissions to see the stored procedure? What error messages are you seeing?

Donald

Invalid Object Name?

Hi

I am developing a windows application that connects to a sql 2000 server. I have created a stored procedure and I am trying to execute the stored procedure in the query analyzer.

Here is the code for the stored procedure

CREATE PROCEDURE dbo.CountofComebacks
(
@.dlname as nvarchar(25),
@.CB as nvarchar(10)
)

AS

Select count([@.dlname].[Auditors working code])
from [@.dlname]
where [Auditors working code] = @.CB
GO

Here is the code that I am attempting to use in the query analyzer

Declare @.dlname as nvarchar(25)
set @.dlname = 'SWMC-OP-02-2005'
Declare @.CB as nvarchar(10)
set @.CB = 'CB'
Execute CountofComebacks @.dlname, @.CB

I get the following error in the query analyzer

Server: Msg 208, Level 16, State 1, Procedure CountofComebacks, Line 9
Invalid object name'@.dlname'.

Any assistance would be greatly appreciated.

thanks

CREATE PROCEDURE dbo.CountofComebacks
(
@.dlname as nvarchar(25),
@.CB as nvarchar(10)
)

AS

DECLARE @.sql varchar(1000)

SET @.sql = 'Select count([' + @.dlname + '].[Auditors working code])
from ' + @.dlname + 'where [Auditors working code] = ' + @.CB

EXEC(@.sql)
GO

If you want to know more about how this works google for Dynamic SQL. There are disadvantages in using this approach too. there are plenty of articles over the net that xplain about "Dynamic SQL".

|||

First let me say thank you for your response.

That being said I am still getting an error when attempting to execute the sp in the query analyzer.

Here is the code in the query analyzer

Declare @.dlname as nvarchar
Set @.dlname = 'SWMCOP022005'
Declare @.CB as nvarchar
Set @.CB = 'CB'
Declare @.NumberofCBs as integer
Execute CountofComebacks @.dlname, @.CB, @.NumberofCBs

Here is the sp

CREATE PROCEDURE dbo.CountofComebacks
(
@.dlname as nvarchar(25),
@.CB as nvarchar(10),
@.NumberofCBs as int
)

AS

DECLARE @.sql varchar(1000)

SET @.sql = 'Select count([' + @.dlname + '].[Auditors working code])
from ' + @.dlname + 'where [Auditors working code] = ' + @.CB

EXEC(@.sql)

return @.NumberofCBs
GO

and here is the error I receive in the query analyzer

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '='.
The 'CountofComebacks' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.

Is this because of the syntax error on line 2?

thanks

|||

throw in an ISNULL function.

SET @.sql = 'Select ISNULL(count([' + @.dlname + '].[Auditors working code]),0) from ' + @.dlname + 'where [Auditors working code] = ' + @.CB

sql

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]

Invalid object name 'sysmergepublications' Please Help

Hi,
I'm trying to use Alternate Sync Partner.
I have found this how-to http://support.microsoft.com/kb/321176
but when I execute procedure ( step 9, my names are different )
sp_addmergealternatepublisher @.publisher = 'PublisherA'
, @.publisher_db = 'TestA'
, @.publication = 'DemoPublication'
, @.alternate_publisher = 'PublisherB'
, @.alternate_publisher_db = 'TestB'
, @.alternate_publication = 'DemoPublication'
, @.alternate_distributor = 'PublisherB'
I get this error
"Server: Msg 208, Level 16, State 1, Procedure
sp_addmergealternatepublisher, Line 38
Invalid object name 'sysmergepublications'."
What can be wrong? It is running on Sql Server 2000 SP 3a , replication
between PublisherA and PublisherB is OK.
Best Regards
Wojciech Znaniecki
Uytkownik "Wojtek Z" <wojtas_z@.poczta.fm> napisa w wiadomoci
news:csoa5s$bqe$1@.nemesis.news.tpi.pl...
> "Server: Msg 208, Level 16, State 1, Procedure
> sp_addmergealternatepublisher, Line 38
> Invalid object name 'sysmergepublications'."
Sorry
My mistake,
Before sp_addmergealterpublisher i should have exec
use [db_name]
GO
Now it is ok.
Wojciech Znaniecki

Invalid object name 'Product'.

HI I'M NEWBIES in visual Basic with Sql Server
i try to make a database with stored procedure and whan i run the program the give an error

"Invalid object name 'Product'."

i dont know how to fix it here is my code
Dim sqlcon As New SqlClient.SqlConnection
sqlcon.ConnectionString = "Data Source=WISEMAN\SQLEXPRESS;Initial Catalog=Product;Integrated Security=True;Pooling=False;uid=uid;pwd=pwd "
Dim cmd As New SqlClient.SqlCommand
cmd.Connection = sqlcon
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "insertcustomer"
cmd.Parameters.AddWithValue("@.Productid", TextBox1.Text)
cmd.Parameters.AddWithValue("@.detail", TextBox2.Text)
sqlcon.Open()
cmd.ExecuteScalar()
cmd.ExecuteNonQuery()
sqlcon.Close()

the error at line is cmd.executescalar()
My database name is product
my name for my storedprocedure is insertcustomer
Code for insertcustomer
ALTER PROCEDURE dbo.InsertCustomer
(

@.ProductID int output,
@.detail varchar(50)
)

AS
SET NOCOUNT ON

INSERT INTO Productdetail
(detail)
VALUES
(@.detail);

IF @.@.ROWCOUNT>0 AND @.@.ERROR>0

SELECT @.Detail = detail

From Productdetail
Where (ProductID =SCOPE_IDENTITY())

if u have any idee please tell meHi shadwise,

Welcome to thescripts. I'm sure you will find a wealth of information ion the various forums here. I am moving this thread to the SQL server forum. You will still be able to access this particular thread through the introductions page, but future questions should be directed to the appropriate forum (which you will find by selecting "forums" on the blue bar near the top of your screen.

I hope the experts in the SQL server forum can help with your query!!|||

Quote:

Originally Posted by shadwise

...i dont know how to fix it here is my code...
if u have any idee please tell me

Does your server or database have case-sensitive collation? Please copy/paste complete error description.
I'm not sure about "Invalid object name 'Product'" problem, but there are several other problem worth mentioning:Wrap SqlConnection and SqlCommand scope with using (C# keyword, don't know correct VB syntax)

Invalid object name in stored procedure

-- In SQL Server 2000
--When run from Query Analyzer I correctly get identification of line
numbers having duplicate values of SKU_NameUsedBySCS:
-- LineNumber1 LineNumber2
-- 2 5
-- but when I try to create a stored procedure having this same code I get:
-- Invalid object name '#x'.
IF not EXISTS (SELECT name FROM sysobjects WHERE name = 'PermTable' )
begin
create table PermTable (scs_id int, sku_nameusedbyscs nvarchar(40))
insert into PermTable VALUES (11, 'name23')
insert into PermTable VALUES (11, 'name81')
insert into PermTable VALUES (11, 'name27')
insert into PermTable VALUES (11, 'name88')
insert into PermTable VALUES (11, 'name81')
end
declare @.SCS_ID int
set @.SCS_ID =11
set nocount on
select identity(int,1,1) as Sequence, scs_id,sku_nameusedbyscs into #x from
PermTable WHERE SCS_ID =@.SCS_ID
go
select top 40 min(Sequence) as LineNumber1, max(Sequence) as LineNumber2
from #x GROUP BY SKU_NameUsedBySCS having count(*) > 1
drop table #x
gohello steve, did you check previous error messages? is you database Case
Sensitive? If it is, then the select into statement must have the names of
your fields in lower case.
hope this helps.
"SteveInSC" wrote:

> -- In SQL Server 2000
> --When run from Query Analyzer I correctly get identification of line
> numbers having duplicate values of SKU_NameUsedBySCS:
> -- LineNumber1 LineNumber2
> -- 2 5
> -- but when I try to create a stored procedure having this same code I get
:
> -- Invalid object name '#x'.
>
> IF not EXISTS (SELECT name FROM sysobjects WHERE name = 'PermTable' )
> begin
> create table PermTable (scs_id int, sku_nameusedbyscs nvarchar(40))
> insert into PermTable VALUES (11, 'name23')
> insert into PermTable VALUES (11, 'name81')
> insert into PermTable VALUES (11, 'name27')
> insert into PermTable VALUES (11, 'name88')
> insert into PermTable VALUES (11, 'name81')
> end
> declare @.SCS_ID int
> set @.SCS_ID =11
> set nocount on
> select identity(int,1,1) as Sequence, scs_id,sku_nameusedbyscs into #x fro
m
> PermTable WHERE SCS_ID =@.SCS_ID
> go
> select top 40 min(Sequence) as LineNumber1, max(Sequence) as LineNumber2
> from #x GROUP BY SKU_NameUsedBySCS having count(*) > 1
> drop table #x
> go
>
>|||Hi
And where do you create the temporary table #x?
John
"SteveInSC" wrote:

> -- In SQL Server 2000
> --When run from Query Analyzer I correctly get identification of line
> numbers having duplicate values of SKU_NameUsedBySCS:
> -- LineNumber1 LineNumber2
> -- 2 5
> -- but when I try to create a stored procedure having this same code I get
:
> -- Invalid object name '#x'.
>
> IF not EXISTS (SELECT name FROM sysobjects WHERE name = 'PermTable' )
> begin
> create table PermTable (scs_id int, sku_nameusedbyscs nvarchar(40))
> insert into PermTable VALUES (11, 'name23')
> insert into PermTable VALUES (11, 'name81')
> insert into PermTable VALUES (11, 'name27')
> insert into PermTable VALUES (11, 'name88')
> insert into PermTable VALUES (11, 'name81')
> end
> declare @.SCS_ID int
> set @.SCS_ID =11
> set nocount on
> select identity(int,1,1) as Sequence, scs_id,sku_nameusedbyscs into #x fro
m
> PermTable WHERE SCS_ID =@.SCS_ID
> go
> select top 40 min(Sequence) as LineNumber1, max(Sequence) as LineNumber2
> from #x GROUP BY SKU_NameUsedBySCS having count(*) > 1
> drop table #x
> go
>
>|||At the time the proc is compiled the #x table does not exist as it's
created at runtime. So when you try to compile the code to get an
execution plan the query optimiser cannot create a plan involving #x
because it doesn't yet exist.
Try creating the temp table explicitly in the proc and then inserting
into it with a normal INSERT statement (rather than SELECT ... INTO).
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
SteveInSC wrote:

>-- In SQL Server 2000
>--When run from Query Analyzer I correctly get identification of line
>numbers having duplicate values of SKU_NameUsedBySCS:
>-- LineNumber1 LineNumber2
>-- 2 5
>-- but when I try to create a stored procedure having this same code I get:
>-- Invalid object name '#x'.
>
>IF not EXISTS (SELECT name FROM sysobjects WHERE name = 'PermTable' )
> begin
> create table PermTable (scs_id int, sku_nameusedbyscs nvarchar(40))
> insert into PermTable VALUES (11, 'name23')
> insert into PermTable VALUES (11, 'name81')
> insert into PermTable VALUES (11, 'name27')
> insert into PermTable VALUES (11, 'name88')
> insert into PermTable VALUES (11, 'name81')
> end
>declare @.SCS_ID int
>set @.SCS_ID =11
>set nocount on
>select identity(int,1,1) as Sequence, scs_id,sku_nameusedbyscs into #x from
>PermTable WHERE SCS_ID =@.SCS_ID
>go
>select top 40 min(Sequence) as LineNumber1, max(Sequence) as LineNumber2
>from #x GROUP BY SKU_NameUsedBySCS having count(*) > 1
>drop table #x
>go
>
>
>|||Hi Steve
If you have simply created the stored procedure by wrapping the SQL below in
a create procedure call then you have a GO in the middle of the declaration.
This will terminate the declaration.
Query Analyser will then try to run the later commands as immediate
commands. As the table is created by the SELECT ... INTO inside the
definition it will not find it for the later SELECT statement.
Try removing the GO statement in the middle of the declaration if there is
one.
As a separate point I would recommend using a Table variable (you know the
structure you want) as the scope is much better defined.
I hope this helps
Alasdair Russell
"SteveInSC" wrote:

> -- In SQL Server 2000
> --When run from Query Analyzer I correctly get identification of line
> numbers having duplicate values of SKU_NameUsedBySCS:
> -- LineNumber1 LineNumber2
> -- 2 5
> -- but when I try to create a stored procedure having this same code I get
:
> -- Invalid object name '#x'.
>
> IF not EXISTS (SELECT name FROM sysobjects WHERE name = 'PermTable' )
> begin
> create table PermTable (scs_id int, sku_nameusedbyscs nvarchar(40))
> insert into PermTable VALUES (11, 'name23')
> insert into PermTable VALUES (11, 'name81')
> insert into PermTable VALUES (11, 'name27')
> insert into PermTable VALUES (11, 'name88')
> insert into PermTable VALUES (11, 'name81')
> end
> declare @.SCS_ID int
> set @.SCS_ID =11
> set nocount on
> select identity(int,1,1) as Sequence, scs_id,sku_nameusedbyscs into #x fro
m
> PermTable WHERE SCS_ID =@.SCS_ID
> go
> select top 40 min(Sequence) as LineNumber1, max(Sequence) as LineNumber2
> from #x GROUP BY SKU_NameUsedBySCS having count(*) > 1
> drop table #x
> go
>
>|||Comment out the first "GO" and you will be all set.
Try this:
create procedure sp_abc as
IF not EXISTS (SELECT name FROM sysobjects WHERE name = 'PermTable' )
begin
create table PermTable (scs_id int, sku_nameusedbyscs nvarchar(40))
insert into PermTable VALUES (11, 'name23')
insert into PermTable VALUES (11, 'name81')
insert into PermTable VALUES (11, 'name27')
insert into PermTable VALUES (11, 'name88')
insert into PermTable VALUES (11, 'name81')
end
declare @.SCS_ID int
set @.SCS_ID =11
set nocount on
select identity(int,1,1) as Sequence, scs_id,sku_nameusedbyscs into #x
from
PermTable WHERE SCS_ID =@.SCS_ID
-- ############## MySQLServer ############ --go
select top 40 min(Sequence) as LineNumber1, max(Sequence) as
LineNumber2
from #x GROUP BY SKU_NameUsedBySCS having count(*) > 1
drop table #x
go
SteveInSC wrote:
> -- In SQL Server 2000
> --When run from Query Analyzer I correctly get identification of line
> numbers having duplicate values of SKU_NameUsedBySCS:
> -- LineNumber1 LineNumber2
> -- 2 5
> -- but when I try to create a stored procedure having this same code I get
:
> -- Invalid object name '#x'.
>
> IF not EXISTS (SELECT name FROM sysobjects WHERE name = 'PermTable' )
> begin
> create table PermTable (scs_id int, sku_nameusedbyscs nvarchar(40))
> insert into PermTable VALUES (11, 'name23')
> insert into PermTable VALUES (11, 'name81')
> insert into PermTable VALUES (11, 'name27')
> insert into PermTable VALUES (11, 'name88')
> insert into PermTable VALUES (11, 'name81')
> end
> declare @.SCS_ID int
> set @.SCS_ID =11
> set nocount on
> select identity(int,1,1) as Sequence, scs_id,sku_nameusedbyscs into #x fro
m
> PermTable WHERE SCS_ID =@.SCS_ID
> go
> select top 40 min(Sequence) as LineNumber1, max(Sequence) as LineNumber2
> from #x GROUP BY SKU_NameUsedBySCS having count(*) > 1
> drop table #x
> go|||You forgot to remove the "GO" before the "SELECT TOP 40".
Razvan

invalid object name (sql server

I am attempting to call a user defined function from a stored procedure in the same database, and I get the following error:

Invalid object name 'dbo.fn_NewSplit'.

I've tried calling it as dbo.fn_NewSplit, fn_NewSplit etc. I also setup another that had my DB login IE ihomesm_maindb.fn_NewSplit and get the same results.

Interestingly there is another previously built stored procedure calling the same function with apparantly no problems.

Also, I have tried to execute this stored procedure with both my .net application and within the query analyzer with the same error.

Can you post the function code as well as your TSQL calling the function?

|||

jmhooten:

I am attempting to call a user defined function from a stored procedure in the same database, and I get the following error:

Invalid object name 'dbo.fn_NewSplit'.

I've tried calling it as dbo.fn_NewSplit, fn_NewSplit etc. I also setup another that had my DB login IE ihomesm_maindb.fn_NewSplit and get the same results.

Interestingly there is another previously built stored procedure calling the same function with apparantly no problems.

Also, I have tried to execute this stored procedure with both my .net application and within the query analyzer with the same error.

Well, thank you Sql Server developers, my issue wasn't that the object name wasn't valid, it was that I was not calling it correctly (I should have called the function within a SELECT statement in my case) the last question lead me to the answer.

Invalid object name

Hi All
I am calling a function from a stored procedure (the function is created
before the stored procedure). Both the stored procedure and the function are
created successfully (no syntax errors in the enterprise manager).
When I try to execute the stored procedure I get the message "Invalid object
name 'twuser.Proc_SelectTwinEntries'".
I can't find anything wrong, can anyone help?
Thanks in advance
Elie Grouchko
CREATE FUNCTION twuser.Proc_SelectTwinEntries(
@.twc_comment_subject int
) RETURNS TABLE
AS
RETURN (
SELECT
[twc_comment_twin]
FROM [twt_comment]
WHERE ([twc_comment_subject] = @.twc_comment_subject) AND ([twc_comment_twin]
IS NOT NULL)
)
CREATE PROCEDURE twuser.Proc_SelectTopForumCommentsOrderByDate
@.twc_comment_subject int
AS
SELECT TOP 5 * FROM [twt_comment]
WHERE ((([twc_comment_subject] = @.twc_comment_subject) AND ([twc_comment_id]
!= @.twc_comment_subject)) OR
([twc_comment_firstparent] IN
(twuser. Proc_SelectTwinEntries(@.twc_comment_subj
ect))))
AND ([twc_comment_state] = 2) AND [twc_comment_twin] IS NULL
ORDER BY [twc_comment_date] DESC
GOYou can only reference a table-valued UDF in the FROM clause. You could
change your IN subquery:
SELECT [twc_comment_twin]
FROM twuser. Proc_SelectTwinEntries(@.twc_comment_subj
ect)
but I don't see much point here. Why not just combine the logic of the
two queries?
David Portas
SQL Server MVP
--|||> ([twc_comment_firstparent] IN
> (twuser. Proc_SelectTwinEntries(@.twc_comment_subj
ect))))
([twc_comment_firstparent] IN
(select [twc_comment_twin] from
twuser. Proc_SelectTwinEntries(@.twc_comment_subj
ect))...
"Elie Grouchko" wrote:

> Hi All
> I am calling a function from a stored procedure (the function is created
> before the stored procedure). Both the stored procedure and the function a
re
> created successfully (no syntax errors in the enterprise manager).
> When I try to execute the stored procedure I get the message "Invalid obje
ct
> name 'twuser.Proc_SelectTwinEntries'".
> I can't find anything wrong, can anyone help?
> Thanks in advance
> Elie Grouchko
> CREATE FUNCTION twuser.Proc_SelectTwinEntries(
> @.twc_comment_subject int
> ) RETURNS TABLE
> AS
> RETURN (
> SELECT
> [twc_comment_twin]
> FROM [twt_comment]
> WHERE ([twc_comment_subject] = @.twc_comment_subject) AND ([twc_comment_twi
n]
> IS NOT NULL)
> )
> CREATE PROCEDURE twuser.Proc_SelectTopForumCommentsOrderByDate
> @.twc_comment_subject int
> AS
> SELECT TOP 5 * FROM [twt_comment]
> WHERE ((([twc_comment_subject] = @.twc_comment_subject) AND ([twc_comment_i
d]
> != @.twc_comment_subject)) OR
> ([twc_comment_firstparent] IN
> (twuser. Proc_SelectTwinEntries(@.twc_comment_subj
ect))))
> AND ([twc_comment_state] = 2) AND [twc_comment_twin] IS NULL
> ORDER BY [twc_comment_date] DESC
> GO
>
>

Invalid object name

I have a stored procedure that creates several temporary tables. When I call
this procedure from the Query Analyzer, it works just fine.
When I call the stored procedure through the DTS or from a query from the
reporting services, I get the error: Invalid object name '#NSLP'
#NSLP is the first temporary table. Any suggestions will be highly
appreciated.
Code for the stored procedure follows
CREATE PROCEDURE [dbo].[procGetSponsorApprovals]
@.iMonth AS integer
AS
SET NOCOUNT ON
CREATE TABLE dbo.#NSLP ( Sponsor varchar(10),
EligBkfstSevere Integer,
EligBkfst Integer,
EligLunch Integer,
EligSnack Integer
)
INSERT INTO #NSLP
SELECT
B.Sponsor,
Sum(CASE WHEN B.EligBkfstSeverePct > 40 THEN 1 ELSE 0 END) AS
EligBkfstSevere,
Sum(CASE WHEN EligBkfst = 'Regular' or EligBkfst = 'Prov1' or
EligBkfst = 'Prov2' or EligBkfst = 'Prov3' THEN 1 ELSE 0 END) AS EligBkfst,
Sum(CASE WHEN EligLunch = 'Regular' or EligLunch = 'Prov1' or
EligLunch = 'Prov2' or EligLunch = 'Prov3' THEN 1 ELSE 0 END) AS EligLunch,
Sum(CASE WHEN EligSnack = 'Regular' or EligSnack = 'Prov1' or
EligSnack = 'Prov2' or EligSnack = 'Prov3' THEN 1 ELSE 0 END) AS EligSnack
FROM tblSLPAppCenter B,
(SELECT MAX(K.EnteredDate) AS EnteredDate ,
K.AgreementNo AS AgreementNo
FROM tblSLPAppCenter K
WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth >= @.iMonth) AND
(Status IN ('Approved','Suspended'))
GROUP BY K.AgreementNo) U
WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
GROUP BY Sponsor
ORDER BY sponsor
CREATE TABLE #DCCenter (Sponsor varchar(10), DCCenters integer)
INSERT INTO #DCCenter
SELECT
B.Sponsor,
Count(*) AS DCCenters
FROM tblDCAppCenter B,
(SELECT MAX(K.EnteredDate) AS EnteredDate ,
K.AgreementNo AS AgreementNo
FROM tblDCAppCenter K
WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth >= @.iMonth) AND
(Status IN ('Approved','Suspended'))
GROUP BY K.AgreementNo) U
WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
GROUP BY Sponsor
ORDER BY sponsor
CREATE TABLE #ACCenter (Sponsor varchar(10), ACCenters integer)
INSERT INTO #ACCenter
SELECT
B.Sponsor,
Count(*) AS ACCenters
FROM tblACAppCenter B,
(SELECT MAX(K.EnteredDate) AS EnteredDate ,
K.AgreementNo AS AgreementNo
FROM tblACAppCenter K
WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth >= @.iMonth) AND
(Status IN ('Approved','Suspended'))
GROUP BY K.AgreementNo) U
WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
GROUP BY Sponsor
ORDER BY sponsor
CREATE TABLE #SMCenter (Sponsor varchar(10), SMCenters integer)
INSERT INTO #SMCenter
SELECT
B.Sponsor,
Count(*) AS SMCenters
FROM tblSMAppCenter B,
(SELECT MAX(K.EnteredDate) AS EnteredDate ,
K.AgreementNo AS AgreementNo
FROM tblSMAppCenter K
WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth >= @.iMonth) AND
(Status IN ('Approved','Suspended'))
GROUP BY K.AgreementNo) U
WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
GROUP BY Sponsor
ORDER BY sponsor
CREATE TABLE #SFCenter (Sponsor varchar(10), SFCenters integer)
INSERT INTO #SFCenter
SELECT
B.Sponsor,
Count(*) AS SFCenters
FROM tblSFAppCenter B,
(SELECT MAX(K.EnteredDate) AS EnteredDate ,
K.AgreementNo AS AgreementNo
FROM tblSFAppCenter K
WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth >= @.iMonth) AND
(Status IN ('Approved','Suspended'))
GROUP BY K.AgreementNo) U
WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
GROUP BY Sponsor
ORDER BY sponsor
CREATE TABLE #SSFCenter (Sponsor varchar(10), SSFCenters integer)
INSERT INTO #SSFCenter
SELECT
B.Sponsor,
Count(*) AS SSFCenters
FROM tblSSFAppCenter B,
(SELECT MAX(K.EnteredDate) AS EnteredDate ,
K.AgreementNo AS AgreementNo
FROM tblSSFAppCenter K
WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth >= @.iMonth) AND
(Status IN ('Approved','Suspended'))
GROUP BY K.AgreementNo) U
WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
GROUP BY Sponsor
ORDER BY sponsor
SELECT
A.AgreementNo,
A.SponsorName,
CASE WHEN isnull(A.SLP,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever SLP],
CASE WHEN isnull(A.DC,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever DC],
CASE WHEN isnull(A.AC,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever AC],
CASE WHEN isnull(A.SM,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever SH],
CASE WHEN isnull(A.FH,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever FH],
CASE WHEN isnull(A.SF,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever SF],
isnull((SELECT 'Y'
FROM dbo.fnAdminSLP('All', @.iMonth)
WHERE AgreementNo = A.AgreementNo AND
(Status = 'Approved' OR Status = 'Suspended')),'_') AS [SLP Sponsor],
isnull((SELECT EligLunch
FROM #NSLP
WHERE Sponsor = A.AgreementNo AND
EligLunch > 0 ),0) AS NSLP,
isnull((SELECT EligBkfst
FROM #NSLP
WHERE Sponsor = A.AgreementNo AND
EligBkfst > 0 ),0) AS Brk,
isnull((SELECT EligBkfstSevere
FROM #NSLP
WHERE Sponsor = A.AgreementNo AND
EligBkfstSevere > 0 ),0) AS SevereBrk,
isnull((SELECT EligSnack
FROM #NSLP
WHERE Sponsor = A.AgreementNo AND
EligSnack > 0 ),0) AS ASSnk,
isnull((SELECT 'Y'
FROM dbo.fnAdminDC('All', @.iMonth)
WHERE AgreementNo = A.AgreementNo AND
(Status = 'Approved' OR Status = 'Suspended')),'_')
AS [DC Sponsor],
isnull((SELECT DCCenters
FROM #DCCenter
WHERE Sponsor = A.AgreementNo AND
DCCenters > 0 ),0) AS [DC Centers],
isnull((SELECT 'Y'
FROM dbo.fnAdminAC('All', @.iMonth)
WHERE AgreementNo = A.AgreementNo),'_') AS [AC Sponsor],
isnull((SELECT ACCenters
FROM #ACCenter
WHERE Sponsor = A.AgreementNo AND
ACCenters > 0 ),0) AS [AC Centers],
isnull((SELECT 'Y'
FROM dbo.fnAdminFH('All', @.iMonth)
WHERE AgreementNo = A.AgreementNo),'_') AS [FH Sponsor],
isnull((SELECT 'Y'
FROM dbo.fnAdminSM('All', @.iMonth)
WHERE AgreementNo = A.AgreementNo),'_') AS [SM Sponsor],
isnull((SELECT SMCenters
FROM #SMCenter
WHERE Sponsor = A.AgreementNo AND
SMCenters > 0 ),0) AS [SM Centers],
isnull((SELECT 'Y'
FROM dbo.fnAdminSF('All', @.iMonth)
WHERE AgreementNo = A.AgreementNo),'_') AS [SF Sponsor],
isnull((SELECT SFCenters
FROM #SFCenter
WHERE Sponsor = A.AgreementNo AND
SFCenters > 0 ),0) AS [SF Centers],
isnull((SELECT 'Y'
FROM dbo.fnAdminSSF('All', @.iMonth)
WHERE AgreementNo = A.AgreementNo),'_') AS [SSF Sponsor],
isnull((SELECT SSFCenters
FROM #SSFCenter
WHERE Sponsor = A.AgreementNo AND
SSFCenters > 0 ),0) AS [SSF Centers],
isnull(Type, '') as Type
FROM tblAgreeData A
WHERE Sponsor <> 0 and AgreementNo NOT like 'OO%'
ORDER BY AgreementNo
GOTry to press the refresh button next to the data source. This will populate
all the fields for you. There are also some other replies to this question
just search for Invalid object in the newsgroup and you should see other
people making suggestions to this. Hope this helps. Let me know if this is
what you are looking for or if you have a different question.
Brendon Schwartz
http://spaces.msn.com/members/brendon
"Ron Sellers" wrote:
> I have a stored procedure that creates several temporary tables. When I call
> this procedure from the Query Analyzer, it works just fine.
> When I call the stored procedure through the DTS or from a query from the
> reporting services, I get the error: Invalid object name '#NSLP'
> #NSLP is the first temporary table. Any suggestions will be highly
> appreciated.
>
> Code for the stored procedure follows
> CREATE PROCEDURE [dbo].[procGetSponsorApprovals]
> @.iMonth AS integer
> AS
> SET NOCOUNT ON
> CREATE TABLE dbo.#NSLP ( Sponsor varchar(10),
> EligBkfstSevere Integer,
> EligBkfst Integer,
> EligLunch Integer,
> EligSnack Integer
> )
> INSERT INTO #NSLP
> SELECT
> B.Sponsor,
> Sum(CASE WHEN B.EligBkfstSeverePct > 40 THEN 1 ELSE 0 END) AS
> EligBkfstSevere,
> Sum(CASE WHEN EligBkfst = 'Regular' or EligBkfst = 'Prov1' or
> EligBkfst = 'Prov2' or EligBkfst = 'Prov3' THEN 1 ELSE 0 END) AS EligBkfst,
> Sum(CASE WHEN EligLunch = 'Regular' or EligLunch = 'Prov1' or
> EligLunch = 'Prov2' or EligLunch = 'Prov3' THEN 1 ELSE 0 END) AS EligLunch,
> Sum(CASE WHEN EligSnack = 'Regular' or EligSnack = 'Prov1' or
> EligSnack = 'Prov2' or EligSnack = 'Prov3' THEN 1 ELSE 0 END) AS EligSnack
> FROM tblSLPAppCenter B,
> (SELECT MAX(K.EnteredDate) AS EnteredDate ,
> K.AgreementNo AS AgreementNo
> FROM tblSLPAppCenter K
> WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth >=> @.iMonth) AND
> (Status IN ('Approved','Suspended'))
> GROUP BY K.AgreementNo) U
> WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
> GROUP BY Sponsor
> ORDER BY sponsor
> CREATE TABLE #DCCenter (Sponsor varchar(10), DCCenters integer)
> INSERT INTO #DCCenter
> SELECT
> B.Sponsor,
> Count(*) AS DCCenters
> FROM tblDCAppCenter B,
> (SELECT MAX(K.EnteredDate) AS EnteredDate ,
> K.AgreementNo AS AgreementNo
> FROM tblDCAppCenter K
> WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth >=> @.iMonth) AND
> (Status IN ('Approved','Suspended'))
> GROUP BY K.AgreementNo) U
> WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
> GROUP BY Sponsor
> ORDER BY sponsor
>
> CREATE TABLE #ACCenter (Sponsor varchar(10), ACCenters integer)
> INSERT INTO #ACCenter
> SELECT
> B.Sponsor,
> Count(*) AS ACCenters
> FROM tblACAppCenter B,
> (SELECT MAX(K.EnteredDate) AS EnteredDate ,
> K.AgreementNo AS AgreementNo
> FROM tblACAppCenter K
> WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth >=> @.iMonth) AND
> (Status IN ('Approved','Suspended'))
> GROUP BY K.AgreementNo) U
> WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
> GROUP BY Sponsor
> ORDER BY sponsor
> CREATE TABLE #SMCenter (Sponsor varchar(10), SMCenters integer)
> INSERT INTO #SMCenter
> SELECT
> B.Sponsor,
> Count(*) AS SMCenters
> FROM tblSMAppCenter B,
> (SELECT MAX(K.EnteredDate) AS EnteredDate ,
> K.AgreementNo AS AgreementNo
> FROM tblSMAppCenter K
> WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth >=> @.iMonth) AND
> (Status IN ('Approved','Suspended'))
> GROUP BY K.AgreementNo) U
> WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
> GROUP BY Sponsor
> ORDER BY sponsor
> CREATE TABLE #SFCenter (Sponsor varchar(10), SFCenters integer)
> INSERT INTO #SFCenter
> SELECT
> B.Sponsor,
> Count(*) AS SFCenters
> FROM tblSFAppCenter B,
> (SELECT MAX(K.EnteredDate) AS EnteredDate ,
> K.AgreementNo AS AgreementNo
> FROM tblSFAppCenter K
> WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth >=> @.iMonth) AND
> (Status IN ('Approved','Suspended'))
> GROUP BY K.AgreementNo) U
> WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
> GROUP BY Sponsor
> ORDER BY sponsor
>
> CREATE TABLE #SSFCenter (Sponsor varchar(10), SSFCenters integer)
> INSERT INTO #SSFCenter
> SELECT
> B.Sponsor,
> Count(*) AS SSFCenters
> FROM tblSSFAppCenter B,
> (SELECT MAX(K.EnteredDate) AS EnteredDate ,
> K.AgreementNo AS AgreementNo
> FROM tblSSFAppCenter K
> WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth >=> @.iMonth) AND
> (Status IN ('Approved','Suspended'))
> GROUP BY K.AgreementNo) U
> WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
> GROUP BY Sponsor
> ORDER BY sponsor
> SELECT
> A.AgreementNo,
> A.SponsorName,
> CASE WHEN isnull(A.SLP,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever SLP],
> CASE WHEN isnull(A.DC,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever DC],
> CASE WHEN isnull(A.AC,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever AC],
> CASE WHEN isnull(A.SM,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever SH],
> CASE WHEN isnull(A.FH,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever FH],
> CASE WHEN isnull(A.SF,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever SF],
> isnull((SELECT 'Y'
> FROM dbo.fnAdminSLP('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo AND
> (Status = 'Approved' OR Status => 'Suspended')),'_') AS [SLP Sponsor],
> isnull((SELECT EligLunch
> FROM #NSLP
> WHERE Sponsor = A.AgreementNo AND
> EligLunch > 0 ),0) AS NSLP,
> isnull((SELECT EligBkfst
> FROM #NSLP
> WHERE Sponsor = A.AgreementNo AND
> EligBkfst > 0 ),0) AS Brk,
> isnull((SELECT EligBkfstSevere
> FROM #NSLP
> WHERE Sponsor = A.AgreementNo AND
> EligBkfstSevere > 0 ),0) AS SevereBrk,
> isnull((SELECT EligSnack
> FROM #NSLP
> WHERE Sponsor = A.AgreementNo AND
> EligSnack > 0 ),0) AS ASSnk,
> isnull((SELECT 'Y'
> FROM dbo.fnAdminDC('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo AND
> (Status = 'Approved' OR Status = 'Suspended')),'_')
> AS [DC Sponsor],
> isnull((SELECT DCCenters
> FROM #DCCenter
> WHERE Sponsor = A.AgreementNo AND
> DCCenters > 0 ),0) AS [DC Centers],
> isnull((SELECT 'Y'
> FROM dbo.fnAdminAC('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo),'_') AS [AC Sponsor],
> isnull((SELECT ACCenters
> FROM #ACCenter
> WHERE Sponsor = A.AgreementNo AND
> ACCenters > 0 ),0) AS [AC Centers],
> isnull((SELECT 'Y'
> FROM dbo.fnAdminFH('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo),'_') AS [FH Sponsor],
> isnull((SELECT 'Y'
> FROM dbo.fnAdminSM('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo),'_') AS [SM Sponsor],
> isnull((SELECT SMCenters
> FROM #SMCenter
> WHERE Sponsor = A.AgreementNo AND
> SMCenters > 0 ),0) AS [SM Centers],
> isnull((SELECT 'Y'
> FROM dbo.fnAdminSF('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo),'_') AS [SF Sponsor],
> isnull((SELECT SFCenters
> FROM #SFCenter
> WHERE Sponsor = A.AgreementNo AND
> SFCenters > 0 ),0) AS [SF Centers],
> isnull((SELECT 'Y'
> FROM dbo.fnAdminSSF('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo),'_') AS [SSF Sponsor],
> isnull((SELECT SSFCenters
> FROM #SSFCenter
> WHERE Sponsor = A.AgreementNo AND
> SSFCenters > 0 ),0) AS [SSF Centers],
> isnull(Type, '') as Type
> FROM tblAgreeData A
> WHERE Sponsor <> 0 and AgreementNo NOT like 'OO%'
> ORDER BY AgreementNo
> GO
>|||Have you tried using table variables instead of temp tables? On the surface,
they provide the same functionality, but may be treated differently by RS.
"Ron Sellers" <RonSellers@.discussions.microsoft.com> wrote in message
news:73374365-09D9-41B8-8B35-FD7E7B382264@.microsoft.com...
>I have a stored procedure that creates several temporary tables. When I
>call
> this procedure from the Query Analyzer, it works just fine.
> When I call the stored procedure through the DTS or from a query from the
> reporting services, I get the error: Invalid object name '#NSLP'
> #NSLP is the first temporary table. Any suggestions will be highly
> appreciated.
>
> Code for the stored procedure follows
> CREATE PROCEDURE [dbo].[procGetSponsorApprovals]
> @.iMonth AS integer
> AS
> SET NOCOUNT ON
> CREATE TABLE dbo.#NSLP ( Sponsor varchar(10),
> EligBkfstSevere Integer,
> EligBkfst Integer,
> EligLunch Integer,
> EligSnack Integer
> )
> INSERT INTO #NSLP
> SELECT
> B.Sponsor,
> Sum(CASE WHEN B.EligBkfstSeverePct > 40 THEN 1 ELSE 0 END) AS
> EligBkfstSevere,
> Sum(CASE WHEN EligBkfst = 'Regular' or EligBkfst = 'Prov1' or
> EligBkfst = 'Prov2' or EligBkfst = 'Prov3' THEN 1 ELSE 0 END) AS
> EligBkfst,
> Sum(CASE WHEN EligLunch = 'Regular' or EligLunch = 'Prov1' or
> EligLunch = 'Prov2' or EligLunch = 'Prov3' THEN 1 ELSE 0 END) AS
> EligLunch,
> Sum(CASE WHEN EligSnack = 'Regular' or EligSnack = 'Prov1' or
> EligSnack = 'Prov2' or EligSnack = 'Prov3' THEN 1 ELSE 0 END) AS EligSnack
> FROM tblSLPAppCenter B,
> (SELECT MAX(K.EnteredDate) AS EnteredDate ,
> K.AgreementNo AS AgreementNo
> FROM tblSLPAppCenter K
> WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth
> >=> @.iMonth) AND
> (Status IN
> ('Approved','Suspended'))
> GROUP BY K.AgreementNo) U
> WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
> GROUP BY Sponsor
> ORDER BY sponsor
> CREATE TABLE #DCCenter (Sponsor varchar(10), DCCenters integer)
> INSERT INTO #DCCenter
> SELECT
> B.Sponsor,
> Count(*) AS DCCenters
> FROM tblDCAppCenter B,
> (SELECT MAX(K.EnteredDate) AS EnteredDate ,
> K.AgreementNo AS AgreementNo
> FROM tblDCAppCenter K
> WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth
> >=> @.iMonth) AND
> (Status IN
> ('Approved','Suspended'))
> GROUP BY K.AgreementNo) U
> WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
> GROUP BY Sponsor
> ORDER BY sponsor
>
> CREATE TABLE #ACCenter (Sponsor varchar(10), ACCenters integer)
> INSERT INTO #ACCenter
> SELECT
> B.Sponsor,
> Count(*) AS ACCenters
> FROM tblACAppCenter B,
> (SELECT MAX(K.EnteredDate) AS EnteredDate ,
> K.AgreementNo AS AgreementNo
> FROM tblACAppCenter K
> WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth
> >=> @.iMonth) AND
> (Status IN
> ('Approved','Suspended'))
> GROUP BY K.AgreementNo) U
> WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
> GROUP BY Sponsor
> ORDER BY sponsor
> CREATE TABLE #SMCenter (Sponsor varchar(10), SMCenters integer)
> INSERT INTO #SMCenter
> SELECT
> B.Sponsor,
> Count(*) AS SMCenters
> FROM tblSMAppCenter B,
> (SELECT MAX(K.EnteredDate) AS EnteredDate ,
> K.AgreementNo AS AgreementNo
> FROM tblSMAppCenter K
> WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth
> >=> @.iMonth) AND
> (Status IN
> ('Approved','Suspended'))
> GROUP BY K.AgreementNo) U
> WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
> GROUP BY Sponsor
> ORDER BY sponsor
> CREATE TABLE #SFCenter (Sponsor varchar(10), SFCenters integer)
> INSERT INTO #SFCenter
> SELECT
> B.Sponsor,
> Count(*) AS SFCenters
> FROM tblSFAppCenter B,
> (SELECT MAX(K.EnteredDate) AS EnteredDate ,
> K.AgreementNo AS AgreementNo
> FROM tblSFAppCenter K
> WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth
> >=> @.iMonth) AND
> (Status IN
> ('Approved','Suspended'))
> GROUP BY K.AgreementNo) U
> WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
> GROUP BY Sponsor
> ORDER BY sponsor
>
> CREATE TABLE #SSFCenter (Sponsor varchar(10), SSFCenters integer)
> INSERT INTO #SSFCenter
> SELECT
> B.Sponsor,
> Count(*) AS SSFCenters
> FROM tblSSFAppCenter B,
> (SELECT MAX(K.EnteredDate) AS EnteredDate ,
> K.AgreementNo AS AgreementNo
> FROM tblSSFAppCenter K
> WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth
> >=> @.iMonth) AND
> (Status IN
> ('Approved','Suspended'))
> GROUP BY K.AgreementNo) U
> WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
> GROUP BY Sponsor
> ORDER BY sponsor
> SELECT
> A.AgreementNo,
> A.SponsorName,
> CASE WHEN isnull(A.SLP,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever SLP],
> CASE WHEN isnull(A.DC,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever DC],
> CASE WHEN isnull(A.AC,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever AC],
> CASE WHEN isnull(A.SM,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever SH],
> CASE WHEN isnull(A.FH,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever FH],
> CASE WHEN isnull(A.SF,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever SF],
> isnull((SELECT 'Y'
> FROM dbo.fnAdminSLP('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo AND
> (Status = 'Approved' OR Status => 'Suspended')),'_') AS [SLP Sponsor],
> isnull((SELECT EligLunch
> FROM #NSLP
> WHERE Sponsor = A.AgreementNo AND
> EligLunch > 0 ),0) AS NSLP,
> isnull((SELECT EligBkfst
> FROM #NSLP
> WHERE Sponsor = A.AgreementNo AND
> EligBkfst > 0 ),0) AS Brk,
> isnull((SELECT EligBkfstSevere
> FROM #NSLP
> WHERE Sponsor = A.AgreementNo AND
> EligBkfstSevere > 0 ),0) AS SevereBrk,
> isnull((SELECT EligSnack
> FROM #NSLP
> WHERE Sponsor = A.AgreementNo AND
> EligSnack > 0 ),0) AS ASSnk,
> isnull((SELECT 'Y'
> FROM dbo.fnAdminDC('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo AND
> (Status = 'Approved' OR Status = 'Suspended')),'_')
> AS [DC Sponsor],
> isnull((SELECT DCCenters
> FROM #DCCenter
> WHERE Sponsor = A.AgreementNo AND
> DCCenters > 0 ),0) AS [DC Centers],
> isnull((SELECT 'Y'
> FROM dbo.fnAdminAC('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo),'_') AS [AC Sponsor],
> isnull((SELECT ACCenters
> FROM #ACCenter
> WHERE Sponsor = A.AgreementNo AND
> ACCenters > 0 ),0) AS [AC Centers],
> isnull((SELECT 'Y'
> FROM dbo.fnAdminFH('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo),'_') AS [FH Sponsor],
> isnull((SELECT 'Y'
> FROM dbo.fnAdminSM('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo),'_') AS [SM Sponsor],
> isnull((SELECT SMCenters
> FROM #SMCenter
> WHERE Sponsor = A.AgreementNo AND
> SMCenters > 0 ),0) AS [SM Centers],
> isnull((SELECT 'Y'
> FROM dbo.fnAdminSF('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo),'_') AS [SF Sponsor],
> isnull((SELECT SFCenters
> FROM #SFCenter
> WHERE Sponsor = A.AgreementNo AND
> SFCenters > 0 ),0) AS [SF Centers],
> isnull((SELECT 'Y'
> FROM dbo.fnAdminSSF('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo),'_') AS [SSF Sponsor],
> isnull((SELECT SSFCenters
> FROM #SSFCenter
> WHERE Sponsor = A.AgreementNo AND
> SSFCenters > 0 ),0) AS [SSF Centers],
> isnull(Type, '') as Type
> FROM tblAgreeData A
> WHERE Sponsor <> 0 and AgreementNo NOT like 'OO%'
> ORDER BY AgreementNo
> GO
>sql

Invalid Object Name

Can anyone help me out with an error that I get when
attempting to create a dataset that uses a stored
procedure? I wrote a moderately complex sp that utilizes
temporary tables. In fact, the result set returned is
that of a select statement against a temp table. However,
when I try to define a new dataset for a report that I am
writing, I get the following error message:
Could not generate a list of fields for the query
Check the query syntax or click refresh fields on the
query toolbar
Invalid object name '#Tmp'
Of course #Tmp is the name of the temporary table that I
am selecting from to return the result set.
Any help would be appreciated. I am using a fresh install
of reporting services without any service packs applied.
Thanks!
TimTry declaring a table variable instead.
"Tim" wrote:
> Can anyone help me out with an error that I get when
> attempting to create a dataset that uses a stored
> procedure? I wrote a moderately complex sp that utilizes
> temporary tables. In fact, the result set returned is
> that of a select statement against a temp table. However,
> when I try to define a new dataset for a report that I am
> writing, I get the following error message:
> Could not generate a list of fields for the query
> Check the query syntax or click refresh fields on the
> query toolbar
> Invalid object name '#Tmp'
> Of course #Tmp is the name of the temporary table that I
> am selecting from to return the result set.
> Any help would be appreciated. I am using a fresh install
> of reporting services without any service packs applied.
> Thanks!
> Tim
>|||Tim:
Your solution to the temp table is better than mine and I would like to learn more about the local variable of type Table. Would you PLEASE share it with me.
I don't understand what do you mean by local variable of type Table. May I PLEASE have the URL to this local variable.
Thanks!
Augusta
"Tim" wrote:
> Can anyone help me out with an error that I get when
> attempting to create a dataset that uses a stored
> procedure? I wrote a moderately complex sp that utilizes
> temporary tables. In fact, the result set returned is
> that of a select statement against a temp table. However,
> when I try to define a new dataset for a report that I am
> writing, I get the following error message:
> Could not generate a list of fields for the query
> Check the query syntax or click refresh fields on the
> query toolbar
> Invalid object name '#Tmp'
> Of course #Tmp is the name of the temporary table that I
> am selecting from to return the result set.
> Any help would be appreciated. I am using a fresh install
> of reporting services without any service packs applied.
> Thanks!
> Tim
>

INVALID LENGTH PARAMETER PASSED....

I have the follwoing stored procedure:

ALTER procedure [dbo].[up_GetExecutionContext](
@.ExecutionGUID int = null
) as
begin
set nocount on

declare@.s varchar(500)
declare @.i int

set @.s = ''
select @.s = @.s + EventType + ','-- Dynamically build the list of
events
from(
select distinct top 100 percent [event] as EventType
from dbo.PackageStep
where (@.ExecutionGUID is null or PackageStep.packagerunid =
@.ExecutionGUID)
order by 1
) as x

set @.i = len(@.s)
select case @.i
when 500 then left(@.s, @.i - 3) + '...'-- If string is too long then
terminate with '...'
else left(@.s, @.i - 1) -- else just remove the final comma
end as 'Context'

set nocount off
end --procedure
GO

When I run this and pass in a value of NULL, things work fine. When I
pass in an actual value (i.e. 15198), I get the following message:

Invalid length parameter passed to the SUBSTRING function.

There is no SUBSTRING being used anywhere in the query and the
datatypes look okay to me.

Any suggestions would be greatly appreciated.

Thanks!!On Jun 4, 12:43 pm, ansonee <anso...@.yahoo.comwrote:

Quote:

Originally Posted by

I have the follwoing stored procedure:
>
ALTER procedure [dbo].[up_GetExecutionContext](
@.ExecutionGUID int = null
) as
begin
set nocount on
>
declare @.s varchar(500)
declare @.i int
>
set @.s = ''
select @.s = @.s + EventType + ',' -- Dynamically build the list of
events
from(
select distinct top 100 percent [event] as EventType
from dbo.PackageStep
where (@.ExecutionGUID is null or PackageStep.packagerunid =
@.ExecutionGUID)
order by 1
) as x
>
set @.i = len(@.s)
select case @.i
when 500 then left(@.s, @.i - 3) + '...' -- If string is too long then
terminate with '...'
else left(@.s, @.i - 1) -- else just remove the final comma
end as 'Context'
>
set nocount off
end --procedure
GO
>
When I run this and pass in a value of NULL, things work fine. When I
pass in an actual value (i.e. 15198), I get the following message:
>
Invalid length parameter passed to the SUBSTRING function.
>
There is no SUBSTRING being used anywhere in the query and the
datatypes look okay to me.
>
Any suggestions would be greatly appreciated.
>
Thanks!!

increase the value of @.s from 500 to 5000 maybe and test it ?|||ansonee (ansonee@.yahoo.com) writes:

Quote:

Originally Posted by

set @.s = ''
select @.s = @.s + EventType + ',' -- Dynamically build the list of
events
from(
select distinct top 100 percent [event] as EventType
from dbo.PackageStep
where (@.ExecutionGUID is null or PackageStep.packagerunid =
@.ExecutionGUID)
order by 1
) as x

I'm afraid that this relies on undefined behaviour. It may produce what
you want today. It might not tomorrow. If you are on SQL 2000, you
will need to run a cursor. On SQL 2005 there exists an option with
XML. See SQL Server MVP Antith Sen's article on
http://www.projectdmx.com/tsql/rowconcatenate.aspx for more information.

Quote:

Originally Posted by

set @.i = len(@.s)
select case @.i
when 500 then left(@.s, @.i - 3) + '...' -- If string is too
long then
terminate with '...'
else left(@.s, @.i - 1) -- else just remove the final comma
end as 'Context'
>
set nocount off
end --procedure
GO
>
When I run this and pass in a value of NULL, things work fine. When I
pass in an actual value (i.e. 15198), I get the following message:
>
Invalid length parameter passed to the SUBSTRING function.
>
There is no SUBSTRING being used anywhere in the query

No, but there is LEFT, which is just a shortcut for SUBSTRING.

More to the point, you have failed to handle the case that the query
does not find any events, and @.i is the empty string.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Invalid cursor state

Running a stored procedure returns
Invalid cursor state
Why does that occur ?Check if this KB article applies: http://support.microsoft.com/kb/831997
Linchi
"Hassan" wrote:

> Running a stored procedure returns
> Invalid cursor state
> Why does that occur ?
>
>|||Its SQL 2005 SP2 ;)
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:F1843F26-586A-4C5E-8AD9-42497EB698E3@.microsoft.com...[vbcol=seagreen]
> Check if this KB article applies: http://support.microsoft.com/kb/831997
> Linchi
> "Hassan" wrote:
>

Invalid cursor state

Running a stored procedure returns
Invalid cursor state
Why does that occur ?Check if this KB article applies: http://support.microsoft.com/kb/831997
Linchi
"Hassan" wrote:
> Running a stored procedure returns
> Invalid cursor state
> Why does that occur ?
>
>|||Its SQL 2005 SP2 ;)
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:F1843F26-586A-4C5E-8AD9-42497EB698E3@.microsoft.com...
> Check if this KB article applies: http://support.microsoft.com/kb/831997
> Linchi
> "Hassan" wrote:
>> Running a stored procedure returns
>> Invalid cursor state
>> Why does that occur ?
>>

Invalid Cursor State

Hi,

I have a stored procedure that calls 2 other stored procedures and combines the results into a temporary table. The results of the temporary table is then returned from the stored procedure.

When I execute the stored procedure in Query Analyzer, I get the exact data I want in the correct format - no errors.

When I execute that stored procedure in Omnivex SQL Link 3, I get an "Invalid Cursor State" error.

I did some digging on that error, and found that it could be related to print statements within the stored procedures. I removed all print statements from all 3 stored procedures and the error is still occuring.

Any suggestions?I suggest you post the code for the stored procedure and somebody will help you rewrite it without using a cursor. 99 times out of 100 they aren't necessary and only impede performance.|||Dude...I don't believe they are using a Cursor in the code...

What is Omnivex SQL Link 3 anyway?

EDIT: AHA

http://www.omnivex.com/index.asp

Is there code on the SQL Link side? Or does it act like ODBC...my Guess it's middleware and that's where you're problem resdies|||Good point.

I wonder if the stored proc might be returning spurious information before the final recordset. Maybe a simple SET NOCOUNT ON would solve the problem.|||That is brilliant! The SET NOCOUNT ON/OFF did the trick! Thank you so much!!!!|||"An expert is a person who has made all possible mistakes in a very narrow field." - Niels Bohr

Invalid cursor state

Running a stored procedure returns
Invalid cursor state
Why does that occur ?
Check if this KB article applies: http://support.microsoft.com/kb/831997
Linchi
"Hassan" wrote:

> Running a stored procedure returns
> Invalid cursor state
> Why does that occur ?
>
>
|||Its SQL 2005 SP2 ;)
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:F1843F26-586A-4C5E-8AD9-42497EB698E3@.microsoft.com...[vbcol=seagreen]
> Check if this KB article applies: http://support.microsoft.com/kb/831997
> Linchi
> "Hassan" wrote:

Invalid Cursor State

Hi all.
I have a procedure in my database whose code is more or less the
following:
CREATE PROCEDURE Fnt ( @.Name nvarchar(20) ) AS
declare @.number1 int;
declare @.number2 int;
CREATE TABLE #Table1
(
ValueRet int,
)
set @.number1=(select SUBSTRING( pol, 1 , 2 ) from table where (id IN
(select ref from table_set where num=@.Name)));
set @.number2=(select SUBSTRING( pol, 5 , 10 ) from table where (id IN
(select ref from table_set where num=@.Name)));
insert into #Table values (@.number1);
insert into #Table values (@.number2);
select * from #Table;
if @.@.Error <> 0
begin
return @.@.error
end
GO
The procedure runs ok within the database. I have a problem when quering
using ODBC. I run the SQLExecDirect and it returns SUCCESS. Then I run
the SQLBind instruction and it successes again. However when running the
SQLFecth I get an error Invalid Cursor State (24000), while it should
return the query values (there are values to be returned ;)) )
Does anybody knows why that happens?
Any help is really welcome.
TA.
CREATE PROCEDURE Fnt ( @.Name nvarchar(20) ) AS
SET NOCOUNT ON
declare @.number1 int;
declare @.number2 int;
"George" <george.news@.NOSPANgmx.net> wrote in message
news:Xns950AD84C3ED7newsgmxnet@.213.0.184.81...
> Hi all.
> I have a procedure in my database whose code is more or less the
> following:
> CREATE PROCEDURE Fnt ( @.Name nvarchar(20) ) AS
> declare @.number1 int;
> declare @.number2 int;
> CREATE TABLE #Table1
> (
> ValueRet int,
> )
> set @.number1=(select SUBSTRING( pol, 1 , 2 ) from table where (id IN
> (select ref from table_set where num=@.Name)));
> set @.number2=(select SUBSTRING( pol, 5 , 10 ) from table where (id IN
> (select ref from table_set where num=@.Name)));
> insert into #Table values (@.number1);
> insert into #Table values (@.number2);
> select * from #Table;
> if @.@.Error <> 0
> begin
> return @.@.error
> end
> GO
>
> The procedure runs ok within the database. I have a problem when quering
> using ODBC. I run the SQLExecDirect and it returns SUCCESS. Then I run
> the SQLBind instruction and it successes again. However when running the
> SQLFecth I get an error Invalid Cursor State (24000), while it should
> return the query values (there are values to be returned ;)) )
> Does anybody knows why that happens?
> Any help is really welcome.
>
> TA.

Invalid Cursor State

Hi all.
I have a procedure in my database whose code is more or less the
following:
CREATE PROCEDURE Fnt ( @.Name nvarchar(20) ) AS
declare @.number1 int;
declare @.number2 int;
CREATE TABLE #Table1
(
ValueRet int,
)
set @.number1=(select SUBSTRING( pol, 1 , 2 ) from table where (id IN
(select ref from table_set where num=@.Name)));
set @.number2=(select SUBSTRING( pol, 5 , 10 ) from table where (id IN
(select ref from table_set where num=@.Name)));
insert into #Table values (@.number1);
insert into #Table values (@.number2);
select * from #Table;
if @.@.Error <> 0
begin
return @.@.error
end
GO
The procedure runs ok within the database. I have a problem when quering
using ODBC. I run the SQLExecDirect and it returns SUCCESS. Then I run
the SQLBind instruction and it successes again. However when running the
SQLFecth I get an error Invalid Cursor State (24000), while it should
return the query values (there are values to be returned ;)) )
Does anybody knows why that happens?
Any help is really welcome.
TA.CREATE PROCEDURE Fnt ( @.Name nvarchar(20) ) AS
SET NOCOUNT ON
declare @.number1 int;
declare @.number2 int;
"George" <george.news@.NOSPANgmx.net> wrote in message
news:Xns950AD84C3ED7newsgmxnet@.213.0.184.81...
> Hi all.
> I have a procedure in my database whose code is more or less the
> following:
> CREATE PROCEDURE Fnt ( @.Name nvarchar(20) ) AS
> declare @.number1 int;
> declare @.number2 int;
> CREATE TABLE #Table1
> (
> ValueRet int,
> )
> set @.number1=(select SUBSTRING( pol, 1 , 2 ) from table where (id IN
> (select ref from table_set where num=@.Name)));
> set @.number2=(select SUBSTRING( pol, 5 , 10 ) from table where (id IN
> (select ref from table_set where num=@.Name)));
> insert into #Table values (@.number1);
> insert into #Table values (@.number2);
> select * from #Table;
> if @.@.Error <> 0
> begin
> return @.@.error
> end
> GO
>
> The procedure runs ok within the database. I have a problem when quering
> using ODBC. I run the SQLExecDirect and it returns SUCCESS. Then I run
> the SQLBind instruction and it successes again. However when running the
> SQLFecth I get an error Invalid Cursor State (24000), while it should
> return the query values (there are values to be returned ;)) )
> Does anybody knows why that happens?
> Any help is really welcome.
>
> TA.

Invalid column name Ploeg.

When i try to execute i receive following error:

Msg 207, Level 16, State 1, Procedure WedstrijdDeelnemersSelectAllMPNietGoedgekeurd, Line 85

Invalid column name 'Ploeg'.

I dont really see whats wrong with the select... it works fine in the 2 first parts of the querry

ALTER PROCEDURE [dbo].[WedstrijdDeelnemersSelectAllMPNietGoedgekeurd]-- Add the parameters for the stored procedure here@.WedstrijdIDintASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;...UNIONSELECTdbo.fncGetPersoonNaam(L.PersoonID,0)as Persoon,'Ploeg: ' + WDPI.Ploegas TypeInschrijving,WDPIL.LidClubID,WT.Omschrijvingas WedstrijdType,C.Omschrijvingas Categorie,WDC.WedstrijdDetailID,WDC.IDas WedstrijdDetailCategorieID,WDPI.IDas TypeInschrijvingIDFROMWedstrijd WINNERJOIN WedstrijdDetail WDON W.ID = WD.WedstrijdIDINNERJOIN WedstrijdType WTON WD.WedstrijdTypeID = WT.IDINNERJOIN WedstrijdDetailCategorie WDCON WD.ID = WDC.WedstrijdDetailIDINNERJOIN Categorie CON WDC.CategorieID = C.IDINNERJOIN WedstrijdDetailPloegInschrijving WDPION WDC.ID = WDPI.WedstrijdDetailCategorieIDINNERJOIN WedstrijdDetailPloegInschrijvingLid WDPILON WDPIL.WedstrijdDetailPloegInschrijvingID = WDPI.ID...

END

forget it, someone updated the database and removed the column...

Invalid column name in SP

I spend over 3 hrs to debug (The following store procedure, I always got
invalid column at The @.reportId) . I had used the same approach in other SP
.
and everything goes fine. BUT today, I don't know what's going on. Please
Help ~~~
EXEC sel_pl_acctjobperiod '200312','0001','HLSHK','DTS_ACCOUNT.DBO'
I got [invalid column name '0001',invalid column name '200312',invalid
column name 'HLSHK'.]
CREATE PROCEDURE dbo.sel_pl_acctjobperiod
@.jobperiod nvarchar(6),
@.reportid nvarchar(20),
@.BranchID nvarchar(10),
@.dbcname_dest varchar(20)
AS
SET NOCOUNT ON
DECLARE @.sql_insert varchar(4000)
DECLARE @.sql_sel_debit varchar(4000)
DECLARE @.sql_arinv_debit varchar(4000)
DECLARE @.sql_where varchar(4000)
DECLARE @.sql_arinv_debit_exec nvarchar(4000)
SELECT @.sql_insert = 'insert into tmp_pl_acctinfo
(deptid,branchid,reportid,smancode,jobno
,invno,iemtype,jobperiod ,
TtlIncome, TtlExpenses, TtlNetProfit,doctype) '
SELECT @.sql_sel_debit ='select Info.deptid,Info.branchid,"' + @.reportid + '"
as reportid ,'''' as
smancode,Info.JobNo,Info.invno,Info.iemtype,Info.jobperiod ,info.ttlbaseamt
as ttlincome, 0 as ttlexpenses, 0 as ttlnetprofit,'
SELECT @.sql_arinv_debit =' '''' as doctype from ' + @.dbcname_dest +
'.arinvinfo Info where Info.validsw = 1 and Info.accttype = "DEBIT" '
SELECT @.sql_where = ' and Info.jobperiod="' + @.jobperiod + '" and
Info.branchid="' + @.branchid + '"'
SELECT @.sql_arinv_debit_exec = @.sql_insert + @.sql_sel_debit +
@.sql_arinv_debit + @.sql_where
print @.sql_arinv_debit_exec
EXEC (@.sql_arinv_debit_exec)Try SET QUOTED_IDENTIFIER OFF then drop and re-create the proc.
Alternatively, change your double quotes (") into two single quotes
(''), otherwise they may be read as column delimiters.
The QUOTED_IDENTIFIER setting is saved with each proc so take notice of
that setting whenever you use CREATE PROC.
David Portas
SQL Server MVP
--|||Hi
You seem to have double quotes in your command string (@.sql_sel_debit),
therefore you may have QUOTED_IDENTIFIER ON. If you want to escape a single
quote use another single quote.
John
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:OuUg0lpQFHA.3144@.tk2msftngp13.phx.gbl...
>I spend over 3 hrs to debug (The following store procedure, I always got
>invalid column at The @.reportId) . I had used the same approach in other
>SP .
> and everything goes fine. BUT today, I don't know what's going on. Please
> Help ~~~
> EXEC sel_pl_acctjobperiod '200312','0001','HLSHK','DTS_ACCOUNT.DBO'
> I got [invalid column name '0001',invalid column name '200312',invalid
> column name 'HLSHK'.]
> CREATE PROCEDURE dbo.sel_pl_acctjobperiod
> @.jobperiod nvarchar(6),
> @.reportid nvarchar(20),
> @.BranchID nvarchar(10),
> @.dbcname_dest varchar(20)
>
> AS
> SET NOCOUNT ON
> DECLARE @.sql_insert varchar(4000)
> DECLARE @.sql_sel_debit varchar(4000)
> DECLARE @.sql_arinv_debit varchar(4000)
> DECLARE @.sql_where varchar(4000)
> DECLARE @.sql_arinv_debit_exec nvarchar(4000)
>
> SELECT @.sql_insert = 'insert into tmp_pl_acctinfo
> (deptid,branchid,reportid,smancode,jobno
,invno,iemtype,jobperiod ,
> TtlIncome, TtlExpenses, TtlNetProfit,doctype) '
> SELECT @.sql_sel_debit ='select Info.deptid,Info.branchid,"' + @.reportid +
> '" as reportid ,'''' as
> smancode,Info.JobNo,Info.invno,Info.iemtype,Info.jobperiod
> ,info.ttlbaseamt as ttlincome, 0 as ttlexpenses, 0 as ttlnetprofit,'
> SELECT @.sql_arinv_debit =' '''' as doctype from ' + @.dbcname_dest +
> '.arinvinfo Info where Info.validsw = 1 and Info.accttype = "DEBIT" '
> SELECT @.sql_where = ' and Info.jobperiod="' + @.jobperiod + '" and
> Info.branchid="' + @.branchid + '"'
> SELECT @.sql_arinv_debit_exec = @.sql_insert + @.sql_sel_debit +
> @.sql_arinv_debit + @.sql_where
>
> print @.sql_arinv_debit_exec
> EXEC (@.sql_arinv_debit_exec)
>|||Replace your double quotes with 2 single-quotes:
CREATE PROCEDURE dbo.sel_pl_acctjobperiod
@.jobperiod nvarchar(6),
@.reportid nvarchar(20),
@.BranchID nvarchar(10),
@.dbcname_dest varchar(20)
AS
SET NOCOUNT ON
DECLARE @.sql_insert varchar(4000)
DECLARE @.sql_sel_debit varchar(4000)
DECLARE @.sql_arinv_debit varchar(4000)
DECLARE @.sql_where varchar(4000)
DECLARE @.sql_arinv_debit_exec nvarchar(4000)
SELECT @.sql_insert = 'insert into tmp_pl_acctinfo
(deptid,branchid,reportid,smancode,jobno
,invno,iemtype,jobperiod ,
TtlIncome, TtlExpenses, TtlNetProfit,doctype) '
SELECT @.sql_sel_debit ='select Info.deptid,Info.branchid,''' + @.reportid +
'''
as reportid ,'''' as
smancode,Info.JobNo,Info.invno,Info.iemtype,Info.jobperiod ,info.ttlbaseamt
as ttlincome, 0 as ttlexpenses, 0 as ttlnetprofit,'
SELECT @.sql_arinv_debit =' '''' as doctype from ' + @.dbcname_dest +
'.arinvinfo Info where Info.validsw = 1 and Info.accttype = ''DEBIT'''
SELECT @.sql_where = ' and Info.jobperiod=''' + @.jobperiod + ''' and
Info.branchid=''' + @.branchid + ''''
SELECT @.sql_arinv_debit_exec = @.sql_insert + @.sql_sel_debit +
@.sql_arinv_debit + @.sql_where
print @.sql_arinv_debit_exec
go
That said, you should avoid using dynamic SQL. It's hard to debug, as you
can see.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:OuUg0lpQFHA.3144@.tk2msftngp13.phx.gbl...
I spend over 3 hrs to debug (The following store procedure, I always got
invalid column at The @.reportId) . I had used the same approach in other SP
.
and everything goes fine. BUT today, I don't know what's going on. Please
Help ~~~
EXEC sel_pl_acctjobperiod '200312','0001','HLSHK','DTS_ACCOUNT.DBO'
I got [invalid column name '0001',invalid column name '200312',invalid
column name 'HLSHK'.]
CREATE PROCEDURE dbo.sel_pl_acctjobperiod
@.jobperiod nvarchar(6),
@.reportid nvarchar(20),
@.BranchID nvarchar(10),
@.dbcname_dest varchar(20)
AS
SET NOCOUNT ON
DECLARE @.sql_insert varchar(4000)
DECLARE @.sql_sel_debit varchar(4000)
DECLARE @.sql_arinv_debit varchar(4000)
DECLARE @.sql_where varchar(4000)
DECLARE @.sql_arinv_debit_exec nvarchar(4000)
SELECT @.sql_insert = 'insert into tmp_pl_acctinfo
(deptid,branchid,reportid,smancode,jobno
,invno,iemtype,jobperiod ,
TtlIncome, TtlExpenses, TtlNetProfit,doctype) '
SELECT @.sql_sel_debit ='select Info.deptid,Info.branchid,"' + @.reportid + '"
as reportid ,'''' as
smancode,Info.JobNo,Info.invno,Info.iemtype,Info.jobperiod ,info.ttlbaseamt
as ttlincome, 0 as ttlexpenses, 0 as ttlnetprofit,'
SELECT @.sql_arinv_debit =' '''' as doctype from ' + @.dbcname_dest +
'.arinvinfo Info where Info.validsw = 1 and Info.accttype = "DEBIT" '
SELECT @.sql_where = ' and Info.jobperiod="' + @.jobperiod + '" and
Info.branchid="' + @.branchid + '"'
SELECT @.sql_arinv_debit_exec = @.sql_insert + @.sql_sel_debit +
@.sql_arinv_debit + @.sql_where
print @.sql_arinv_debit_exec
EXEC (@.sql_arinv_debit_exec)|||Is there some reason that you are using dynamic SQL and passing a table
name to a stored procedure? Is this an accounting system in which you
have no idea where the data is until run time? SQL injection and the
destruction of cohesion in the module would seem to tell us that this
is a bad programming style.
Are the job_period, report_id and branch_id really varying length
national characters? Most systems do not have encoding like that. Why
are you inviting bad data?
Next, the name of the table being loaded implies it is a temp table,
which would imply that you have a procedural design and not a
relational one. Are you actually building things step by step and
storing the intermediate results in working tables (aka "Cobol scratch
files in SQL disguise")?|||Thanks All.
I dont' know why my other SP didn't got such problem.
I never use SET QUOTED_IDENTIFIER OFF . before
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org>
'?:1113666233.068362.325440@.z14g2000cwz.googlegroups.com...
> Try SET QUOTED_IDENTIFIER OFF then drop and re-create the proc.
> Alternatively, change your double quotes (") into two single quotes
> (''), otherwise they may be read as column delimiters.
> The QUOTED_IDENTIFIER setting is saved with each proc so take notice of
> that setting whenever you use CREATE PROC.
> --
> David Portas
> SQL Server MVP
> --
>

Invalid Column Name

I get a Invalid Column Name ' '. with this procedure. Can anyone see what migh be wrong?

Thanks,

SELECT A.CompanyName,C.FirstName,C.LastName,C.Client_ID,
CASE WHEN A.[CompanyName] IS NULL OR A.[CompanyName] = '' THEN C.[FirstName] +" "+ C.[LastName] ELSE A.[CompanyName] END AS DRName, C.Client_ID
FROM tblClients C INNER JOIN tblClientAddresses A ON C.Client_ID = A.Client_ID
WHERE (C.Client_ID = 15057) AND (A.MailTo=1) AND Convert(varchar(5), GETDATE(), 10) BETWEEN Convert(varchar(5), A.Startdate, 10) AND Convert(varchar(5), A.Enddate, 10) OR (A.Startdate Is Null) AND (A.EndDate Is Null)
GONormally, unless you've fiddled with your settings, SQL won't like double quotes... + " " +|||How should this be done?

Thanks,|||single quotes... + ' ' +