CODEWITHSUNDEEP
sqlsql-servercursor
Callum
Callum

Reputation: 3

Invalid object name 'DQM.dbo.ST_0043_BLANK_CONSENT_SAP_SIEBEL'

Hi all, Yes same person from an earlier question... I'm really bad when it comes to Cursors/SQL I have been using the below stored procedure/cursor that I have made.. It compiles properly, the problem comes when i try to execute it I get this error:

Msg 208, Level 16, State 1, Line 1 Invalid object name 'DQM.dbo.ST_0043_BLANK_CONSENT_SAP_SIEBEL'.

(0 row(s) affected) Msg 208, Level 16, State 1, Line 1 Invalid object name 'DQM.dbo.ST_0044_CONSENT_SIEBEL_SAP_DIFF'.

(0 row(s) affected) Msg 208, Level 16, State 1, Line 1 Invalid object name 'DQM.dbo.ST_0045_CONSENT_SIEBEL_BANNER_DIFF'.

Now I know for a fact all of these objects exist as when I do a select all, i get rows returned. Any help would be appreciated.

    USE [Adhoc_datafix]
GO
/****** Object:  StoredProcedure [dbo].[CostTest_02]    Script Date: 01/10/2013 15:43:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[CostTest_02]
AS
BEGIN

DECLARE @Issue_id int;
DECLARE @Master_id int;
DECLARE @Issue_table varchar(255);
DECLARE @price real;
DECLARE @rowcount bigint;
DECLARE @sql varchar(3000)


DECLARE cost_cursor cursor FOR
SELECT [Issue Id], [Master Issue Id], [Issue Table], [Price]
from Adhoc_datafix..[IssueTable]
FOR UPDATE OF [Issue Id];

OPEN cost_cursor;
FETCH NEXT FROM cost_cursor into @Issue_ID, @Master_id, @Issue_table, @Price

WHILE @@FETCH_STATUS = 0
BEGIN


SELECT @sql = 'Select count(*) from DQM.' + quotename(@Issue_table)
EXEC(@sql)
set @Rowcount = @@rowcount


UPDATE ADHOC_DATAFIX..[Issue Cost]
set [Issue Id] = @Issue_ID ,
    [Master Issue Id] = @Master_ID ,
    [Row Count] = @Rowcount,
    [Cost] = CAST(@Rowcount * @price as money)

--WHERE CURRENT OF cost_cursor;
FETCH NEXT FROM cost_cursor into @Issue_ID, @Master_id, @Issue_table, @Price
END

close cost_cursor;

DEALLOCATE cost_cursor;
END

Upvotes: 0

Views: 1130

Answers (1)

Pondlife
Pondlife

Reputation: 16240

The problem seems to be that you are using QUOTENAME() to quote the schema and table name together:

select quotename('dbo.ST_0045_CONSENT_SIEBEL_BANNER_DIFF')
-- returns [dbo.ST_0045_CONSENT_SIEBEL_BANNER_DIFF]

So SQL Server thinks you are trying to query a table called [dbo.ST_0045_CONSENT_SIEBEL_BANNER_DIFF] in the DQM schema:

select * from DQM.[dbo.ST_0045_CONSENT_SIEBEL_BANNER_DIFF]

But you really want to query a table called [ST_0045_CONSENT_SIEBEL_BANNER_DIFF] in the dbo schema:

select * from DQM.dbo.[ST_0045_CONSENT_SIEBEL_BANNER_DIFF]

To fix this, either remove the dbo. prefix from the start of the @Issue_Table string and hard-code it after DQM, or if you want to support different schemas use PARSENAME() to get the schema and table name separately:

SELECT @sql = 'Select count(*) from DQM.' + quotename(parsename(@Issue_table, 2)) + N'.' + quotename(parsename(@Issue_table, 1))

And as a general suggestion, any time you work with dynamic SQL, give yourself an easy way to PRINTor SELECT the final string so you can see if the query you constructed is really correct.

Upvotes: 1

Related Questions