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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy