January 11, 2011 at 5:42 am
i am using a CTE expression, as part of a script that copy data between diffrent db's.
in the middle of the script i have the following code :
;WITH
cteGetNewVSOldGroup (oldGroupId, newGroupId)
AS
(
select t1.oldGroupId,t2.id from #tblGroups t1 Inner join
[DB1].[dbo].Dyn_Group t2 on t1.name=t2.name
where t2.CompanyRef=@newCompanyRef and t2.UserRef=@newUserId
)
INSERT INTO [DB1].[dbo].[Dyn_Group2]
([GrandParent]
,[Parent]
,[Child])
select t2.newGroupId,t3.newGroupId,t4.newGroupId from #tblGroup2Group t1
inner join cteGetNewVSOldGroup as t2 on t1.GrandParent=t2.oldGroupId
inner join cteGetNewVSOldGroup as t3 on t1.Parent=t3.oldGroupId
inner join cteGetNewVSOldGroup as t4 on t1.Child=t4.oldGroupId
i get an error : Invalid object name 'cteGetNewVSOldGroup'.
any idea?
thanks
Peleg
January 11, 2011 at 7:39 am
It's because you're trying to run it across databases. The CTE created in the database you're in, not the database you're running against.
I'm unsure of how to resolve that. I'll have to do a bit of research.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 11, 2011 at 8:15 am
Are you referencing cteGetNewVSOldGroup later in your script?
January 11, 2011 at 8:23 am
Maxim Picard (1/11/2011)
Are you referencing cteGetNewVSOldGroup later in your script?
That may be an issue as well, but if you try to hop a database and reference a CTE, you'll get that exact error.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 11, 2011 at 8:30 am
Grant Fritchey (1/11/2011)
Maxim Picard (1/11/2011)
Are you referencing cteGetNewVSOldGroup later in your script?That may be an issue as well, but if you try to hop a database and reference a CTE, you'll get that exact error.
Hi Grant,
I don't understand what you mean. How can you change database context after creating the CTE? I am trying to replicate his script with test tables and not getting the error.
Maybe I need another cofee 🙂
CREATE DATABASE DB1
CREATE DATABASE DB2
GO
USE DB1
GO
CREATE TABLE dbo.TableInDB1(
col1 CHAR(1)
)
GO
INSERT dbo.TableInDB1 VALUES ('1')
GO
--==== Switch to another context
USE DB2
GO
;WITH cteTest (col1) AS
(
SELECT
col1
FROM
DB1.dbo.TableInDB1
)
SELECT
*
FROM
cteTest
January 11, 2011 at 8:51 am
Well now I can't recreate it. Nuts. I should have kept that code I had that generated it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 11, 2011 at 9:51 am
It looks like the CTE is being refernced in more than one query:
January 11, 2011 at 12:20 pm
"SSC Veteran" - what do you mean by that?
that you can't use the same cte more then once in one session?
January 11, 2011 at 12:26 pm
peleg (1/11/2011)
"SSC Veteran" - what do you mean by that?that you can't use the same cte more then once in one session?
Yep. A CTE can be referenced multiple times inside of the statement immediately following it, but it can't be referenced by multiple statements within the query.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 11, 2011 at 12:26 pm
The CTE "belongs" to the query, not the session.
IOW, if you want to persist it for the entire session you will not be able to use a CTE - you could use a table variable or temp table
jg
January 11, 2011 at 12:33 pm
Yep. A CTE can be referenced multiple times inside of the statement immediately following it, but it can't be referenced by multiple statements within the query.
you mean it can be used only once inside an inner join and that's all the problem?
January 11, 2011 at 12:38 pm
No. Not really. This is psuedo-code to describe the situation:
WITH MyCTE(...)
INSERT INTO MyTable
SELECT * FROM MyCTE AS x
JOIN MyCTE as y
ON x.ID = Y.ID
and x.Type=1
JOIN MyCTE AS z
ON x.ID = z.ID
AND z.Type=2
Something like this will work fine. But if I did this:
WITH MyCTE(...)
INSERT INTO MyTable
SELECT * FROM MyCTE as X
INSERT INTO MyOtherTable
SELECT * FROM MyCTE aS y
That will fail because the CTE only lives as long as the first statement following it's definition, regardless of how many times it's referenced within the statement.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply