{"id":6432,"date":"2022-02-24T06:38:00","date_gmt":"2022-02-24T05:38:00","guid":{"rendered":"https:\/\/expert-only.com\/?p=6432"},"modified":"2022-04-04T10:46:37","modified_gmt":"2022-04-04T08:46:37","slug":"invalid-object-name-sql-server-cte-error","status":"publish","type":"post","link":"https:\/\/expert-only.com\/en\/errors\/invalid-object-name-sql-server-cte-error\/","title":{"rendered":"Invalid object name SQL Server CTE Error"},"content":{"rendered":"\n

How to avoid the Invalid object name SQL Server CTE Error ? Or how to execute multiple SELECT queries on the same CTE ? As a reminder, a CTE is a temporary object that only exists between its creation and the first time it’s used. It means it’s deleted right after the first query ran against the CTE. <\/strong><\/p>\n\n\n\n

Indeed when querying the second time a CTE, for Common Table Expression, you face this error thrown by SQL Server. How to avoid the\u00a0Invalid object name SQL Server CTE Error ?<\/p>\n\n\n\n

This error is faced when a select query uses more than one time a CTE : Invalid object name<\/h2>\n\n\n\n

(1 row(s) affected)<\/strong>
Msg 208, Level 16, State 1, Line 9<\/strong>
Invalid object name ‘MyCTE’.<\/strong><\/p>\n\n\n\n

In fact, only the first SELECT query works not the second one or next ones.\u00a0Here’s a CTE<\/a> example with various SELECT queries executed against it.<\/p>\n\n\n\n

Only the first SELECT statement on the CTE works!<\/strong><\/p>\n\n\n\n

WITH MyCTE (Yesterday, Today, Tomorrow) AS\n(\nSELECT\n\ngetdate()-1 as Yesterday,\ngetdate() as Today,\ngetdate()+1 as Tomorrow\n)\n\nSELECT Yesterday FROM MyCTE\nSELECT Today FROM MyCTE\nSELECT Tomorrow FROM MyCTE\n<\/pre>\n\n\n\n
The solution<\/strong><\/span><\/h3>\n\n\n\n
It’s impossible to execute many times a SELECT statement against the same CTE, so use a temporary table instead of a CTE!<\/strong><\/h3>\n\n\n\n
To conclude about the workaround for this CTE error: temporary tables are  definitely the best way to keep results and query them many times. This query replacing the CTE with a temporary table works perfectly.<\/p>\n\n\n\n
IF OBJECT_ID('tempdb..#Temp_Table') IS NOT NULL\nDROP TABLE #Temp_Table\n\nSELECT getdate()-1 as Yesterday,\ngetdate() as Today,\ngetdate()+1 as Tomorrow\nINTO #Temp_Table\n\nSELECT Yesterday FROM #Temp_Table\nSELECT Today FROM #Temp_Table\nSELECT Tomorrow FROM #Temp_Table\n<\/pre>\n\n\n\n
Note: Check the existence of the table and if necessary drop it before to avoid errors.<\/p>\n\n\n\n
Another option is to create a real table and just reuse it, you can find a script here to avoid conversion error from xml to nvarchar<\/strong><\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"
How to avoid the Invalid object name SQL Server CTE Error ? Or how to execute multiple SELECT queries on the same CTE ? As a reminder, a CTE is a temporary object that only exists between its creation …<\/a><\/p>\n<\/div>","protected":false},"author":1,"featured_media":6048,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[486],"tags":[],"class_list":{"0":"post-6432","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-errors"},"_links":{"self":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts\/6432","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/comments?post=6432"}],"version-history":[{"count":0,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts\/6432\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/media\/6048"}],"wp:attachment":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/media?parent=6432"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/categories?post=6432"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/tags?post=6432"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}