March 15, 2022 at 4:47 pm
Hi there
I am trying to reference a table in dynamic sql using a CTE
Now this code works outside of my main script
DROP TABLE IF EXISTS [dbo].[ChannelReading_940860_11_34216228-4C4A-4137-AEA5-EFA9D6A88364_MainLevelData]
GO
CREATE TABLE [dbo].[ChannelReading_940860_11_34216228-4C4A-4137-AEA5-EFA9D6A88364_MainLevelData](
[MinValue] [float] NULL,
[MaxValue] [float] NULL,
[AvgValue] [float] NULL,
[DateTime] [datetime2](7) NULL,
[Group] [bigint] NULL
) ON [PRIMARY]
GO
insert into [dbo].[ChannelReading_940860_11_34216228-4C4A-4137-AEA5-EFA9D6A88364_MainLevelData] (MinValue, MaxValue, AvgValue,[DateTime],[Group])
SELECT 58.9086074829102,59.1005477905273,59.0045776367188,'2022-04-25 03:54:16.0000000', 1
UNION ALL
SELECT 59.2725028991699,59.2828674316406,59.2776851654053,'2022-04-25 03:54:16.0000000', 1
declare @sql nvarchar(max)
declare @MainLevelDataTableName NVARCHAR(200) = '[ChannelReading_940860_11_34216228-4C4A-4137-AEA5-EFA9D6A88364_MainLevelData]'
set @sql = ' ;WITH MyCTE AS (' + char(10) + char(13) +
' SELECT TOP (1) * ' + char(10) + char(13) +
' from ' + @MainLevelDataTableName + char(10) + char(13) +
' WHERE [Group] = ' + CAST(1 AS nvarchar(4)) + char(10) + char(13) +
' order by [DateTime] desc' + char(10) + char(13) +
' ) ' + char(10) + char(13) +
' delete FROM MyCTE'
EXEC sp_executesql @sql
However when i run this in a script i get the following error (see attached screenshot)
Msg 208, Level 16, State 1, Line 21
Invalid object name 'ChannelReading_940860_11_34216228-4C4A-4137-AEA5-EFA9D6A88364_MainLevelData'.
What am I doing wrong here? I have placed the table name in square brackets but it cant reference this table?
March 15, 2022 at 5:25 pm
-- Note: the schema name is declared and used outside of dynamic SQL
CREATE TABLE [dbo].[ChannelReading_940860_11_34216228-4C4A-4137-AEA5-EFA9D6A88364_MainLevelData]
-- but the schema name is not declared or used in dynamic SQL
declare @MainLevelDataTableName NVARCHAR(200) = '[ChannelReading_940860_11_34216228-4C4A-4137-AEA5-EFA9D6A88364_MainLevelData]'
If your login's default schema is not dbo, then your dynamic SELECT statement runs as:
SELECT * FROM [<your_default_schema>].[ChannelReading_940860_11_34216228-4C4A-4137-AEA5-EFA9D6A88364_MainLevelData]
That table does not exist.
Try including the schema name with the table name in your query:
declare @MainLevelDataTableName NVARCHAR(200) = '[dbo].[ChannelReading_940860_11_34216228-4C4A-4137-AEA5-EFA9D6A88364_MainLevelData]';
Eddie Wuerch
MCM: SQL
March 15, 2022 at 6:12 pm
dbo always exists. The table "[dbo].[ChannelReading_940860_11_34216228-4C4A-4137-AEA5-EFA9D6A88364_MainLevelData]" does not exist in the database in which you are executing.
To prove it to yourself, just execute
To prove it to yourself, just execute SELECT TOP 1 * FROM [dbo].[ChannelReading_940860_11_34216228-4C4A-4137-AEA5-EFA9D6A88364_MainLevelData]
or
SELECT * FROM sys.objects WHERE name = 'ChannelReading_940860_11_34216228-4C4A-4137-AEA5-EFA9D6A88364_MainLevelData' AND OBJECT_SCHEMA_NAME(object_id) = 'dbo';
It may seem like a dumb question, but I see it frequently (and do it more than I'd like to admit) -- are you executing in the right database? If so, then the table name is incorrect/doesn't exist.
March 16, 2022 at 6:28 am
Hi
I dont know if it works... but only try, Set the databasename.dbo.<your tablename> in your expression.
Brgds frank
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply