CODEWITHSUNDEEP
sql-server
devRena
devRena

Reputation: 327

SQL Server error: "The identifier that starts with .... is too long. Maximum length is 128"

I was using SQL Server 2014 earlier. When I am trying to execute the following query

EXECUTE msdb.dbo.sp_send_dbmail 
            @profile_name = "Mail" 
            ,@recipients = "[email protected]" 
            ,@from_address = "[email protected]"
            ,@execute_query_database = "EYE" 
            ,@query = "SELECT TOP (100) DateTime, VariableID, VariableName, 
            VariableValue_NumValue as VariableValue, VariableLabel as VariableLabel, SiteID, SiteName,
             DeviceName from dbo.v_AggregatedReport where ClientID = 1 and SiteID in 
             (select SiteID from dbo.v_AccessList where UserID = 16) and DateTime is not null and 
             VariableValue_NumValue is not null and DateTime
              between Web Aug 16 2017 00:00:00 GMT+0300 (GTB Daylight Time) and
              Sat Sep 16 2017 23:59:59 GMT+0300 (GTB Daylight Time) AND SiteID in (39) and 
              VariableName in (\'Total Active Energy\')"
            ,@subject = "Aggregated-Reports eye.v2" 
            ,@body= "Aggregated Reports about a month"
            ,@attach_query_result_as_file = 1 
            ,@query_attachment_filename = "Aggregated-Reports.csv" 
            ,@query_result_separator = "    "
            ,@query_result_no_padding= 1 
            ,@exclude_query_output =1
            ,@append_query_error = 0 
            ,@query_result_header =1;

but I get this error:

The identifier that starts with 'SELECT TOP (100) DateTime, VariableID, VariableName,VariableValue_NumValue as VariableValue, VariableLabel as VariableLab' is too long. Maximum length is 128

I searched for it a lot, but could not find the solution.

Upvotes: 1

Views: 8362

Answers (2)

Nicolaesse
Nicolaesse

Reputation: 2714

Try using the following query

SELECT TOP 100 
    A.[DateTime], A.VariableID, A.VariableName, A.VariableValue, 
    A.NumValue AS VariableValue, A.VariableLabel AS VariableLabel, 
    A.SiteID, A.SiteName,A. DeviceName 
FROM
    dbo.v_AggregatedReport A 
INNER JOIN 
    dbo.v_AccessList B ON A.SiteID = B.SiteID AND B.userID = 16
WHERE
    A.[DateTime] IS NOT NULL
    AND A.VariableValue_NumValue IS NOT NULL
    AND A.[DateTime] BETWEEN Web Aug 16 2017 00:00:00 GMT+0300 (GTB Daylight Time) and Sat Sep 16 2017 23:59:59 GMT+0300 (GTB Daylight Time) 
    AND A.SiteID = 39 
    AND A.VariableName = 'Total Active Energy'

Consider that Datetime is not a good idea for a column name;

Upvotes: 1

iamdave
iamdave

Reputation: 12243

As HoneyBadger said, you are using the double quotes where you should be using single quotes. You are also escaping the single quotes within your @query incorrectly, not qualifying your DateTime column name as it is a SQL keyword nor are you specifying your dates correctly.

I have not changed your overlapping criteria around the SiteID column however, as I do not know whether you need to have it match a result in the sub-query or simply be equal to 39?

It appears you have come from another development language and are trying to apply the same principles to your SQL code. I would recommend doing some research on SQL syntax and best practice:

EXECUTE msdb.dbo.sp_send_dbmail 
            @profile_name = 'Mail' 
            ,@recipients = '[email protected]' 
            ,@from_address = '[email protected]'
            ,@execute_query_database = 'EYE' 
            ,@query = 'select top (100) [DateTime]
                                        ,VariableID
                                        ,VariableName
                                        ,VariableValue_NumValue as VariableValue
                                        ,VariableLabel as VariableLabel
                                        ,SiteID
                                        ,SiteName
                                        ,DeviceName
                        from dbo.v_AggregatedReport
                        where ClientID = 1
                            and SiteID in (select SiteID
                                            from dbo.v_AccessList
                                            where UserID = 16
                                            )
                            and SiteID in (39)   -- This criteria clashes with the sub-select above.
                            and VariableValue_NumValue is not null
                            and [DateTime] >= ''20170816''
                            and [DateTime] < ''20170917''  -- Always look for less that the start of the next period.  Time is infinite yet current data types are not.
                            and VariableName in (''Total Active Energy'')
                        '
            ,@subject = 'Aggregated-Reports eye.v2' 
            ,@body= 'Aggregated Reports about a month'
            ,@attach_query_result_as_file = 1 
            ,@query_attachment_filename = 'Aggregated-Reports.csv' 
            ,@query_result_separator = '    '
            ,@query_result_no_padding= 1 
            ,@exclude_query_output =1
            ,@append_query_error = 0 
            ,@query_result_header =1;

Upvotes: 2

Related Questions