July 13, 2017 at 8:38 am
DECLARE @CountResults TABLE (CountReturned VARCHAR(1000))
declare @SqlStatement varchar(8000) ='SELECT table_name FROM [dbo].[DDS_Database_Dimension_Map] where DATABASE_ID ='''+@SourceDBName+'''' +'AND DIMENSION_TYPE = '''+@seriescode+''''
, @TABLENAME VARCHAR(100)
INSERT @CountResults
EXEC @SqlStatement
SET @TABLE_NAME = (SELECT *FROM @CountResults )
SELECT @TABLE_NAME
getting the following error :
Msg 203, Level 16, State 2, Line 38
The name 'SELECT table_name FROM [dbo].[DDS_Database_Dimension_Map] where DATABASE_ID ='WDI Final'AND DIMENSION_TYPE = 'series'' is not a valid identifier.
Please let me know how do i change the sql
July 13, 2017 at 9:02 am
You haven't posted all of your code, because you'd get an error that you haven't declared @SourceDBName with what you have posted. Once you've fixed that, put a PRINT @SqlStatement line in your code so that you can inspect the statement that is about to be executed. You should then be able to see what is wrong. If you can't, please post the statement here.
John
July 13, 2017 at 9:10 am
DECLARE
@SourceDBName varchar(100),
@code nvarchar(max),
@RunTimequery Nvarchar(max) ,
@TABLE_NAME varchar(100) ,
@Database_Name varchar(100) ,
@seriescode varchar(100),
@sqlCommand nvarchar(1000) ,
@sqlstatment nvarchar (max)
SET @SourceDBName = 'WDI Final'
SET @CODE = 'USA'
SET @TABLE_NAME = 'SDDS_SERIES_DIM'
set @seriescode = 'series'
DECLARE @CountResults TABLE (CountReturned VARCHAR(1000))
declare @SqlStatement varchar(8000) ='SELECT table_name FROM [dbo].[DDS_Database_Dimension_Map] where DATABASE_ID ='''+@SourceDBName+'''' +'AND DIMENSION_TYPE = '''+@seriescode+''''
, @TABLENAME VARCHAR(100)
INSERT @CountResults
EXEC @SqlStatement
print @sqlstatment
SET @TABLE_NAME = (SELECT *FROM @CountResults )
SELECT @TABLE_NAME
July 13, 2017 at 9:20 am
This line looks a bit odd to me too:SET @TABLE_NAME = (SELECT *FROM @CountResults )
SELECT @TABLE_NAMESELECT @TABLE_NAME
@TABLE_NAME is a varchar(100) and you are doing a SELECT * into it? I am expecting that to fail. But since it is a single column table, it might succeed but you will have no way to know what the result will be. Even repeated runs might end up with different ordering of @CountResults and thus you might get different values into @TABLE_NAME.
But the original problem is with your EXEC statement. It should be:EXEC (@SqlStatement)
you need brackets around it.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 13, 2017 at 9:33 am
bmg002 - Thursday, July 13, 2017 9:20 AMThis line looks a bit odd to me too:SET @TABLE_NAME = (SELECT *FROM @CountResults )
SELECT @TABLE_NAMESELECT @TABLE_NAME@TABLE_NAME is a varchar(100) and you are doing a SELECT * into it? I am expecting that to fail. But since it is a single column table, it might succeed but you will have no way to know what the result will be. Even repeated runs might end up with different ordering of @CountResults and thus you might get different values into @TABLE_NAME.
But the original problem is with your EXEC statement. It should be:
EXEC (@SqlStatement)you need brackets around it.
If it has one column and one row, then the SELECT * will work, but if more than one row or column then it will fail.
To the OP, you've also added your PRINT statement into your query, but not actually included the output of said print. Could you post that please?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply