Error inserting a SQL query string into a TEXT field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Error inserting a SQL query string into a TEXT field

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-02-28 : 07:43:19

Robert writes "I'm inserting a SQL query into a TEXT field and in Query Analyzer I'm getting an error that reads:

Server: Msg 103, Level 15, State 7, Line 2
The identifier that starts with 'Select SSN, FirstName, MiddleInitial, LastName, Grade, SchoolCode, (Select SiteName from tblSites where SiteCode = SchoolCode an' is too long. Maximum length is 128.

I'm using SQL 2000 w/ SP3 on a Windows 2000 Server.

The SQL query that I'm trying to store is:

Select SSN, FirstName, MiddleInitial, LastName, Grade, SchoolCode, (Select SiteName from tblSites where SiteCode = SchoolCode and SysCode = 'gv_SystemCode') AS SchoolName, BirthDate as Error from virtStudentAttendance Where SchoolCode <> '3999' and isDate(BirthDate) = 1 and DATEPART(month, BirthDate) < 12 and DATEDIFF(year, BirthDate, '12/01/'+cast(datepart(year,getdate()) as VARCHAR(4)))-1 not between 3 and 21 and isDate(DateEnrolledInSpecialEducation) = 1 and PrimaryExceptionality is not Null and PrimaryExceptionality <> '' and LeastRestrictiveEnvironment is Not Null and LeastRestrictiveEnvironment <> '' Union Select SSN, FirstName, MiddleInitial, LastName, Grade, SchoolCode, (Select SiteName from tblSites where SiteCode = SchoolCode and SysCode = 'gv_SystemCode') AS SchoolName, BirthDate as Error from virtStudentAttendance Where SchoolCode <> '3999' and isDate(BirthDate) = 1 and DATEPART(month, BirthDate) = 12 and DATEPART(Day, BirthDate) = 1 and DATEDIFF(year, BirthDate, '12/01/'+cast(datepart(year,getdate()) as VARCHAR(4)))-1 not between 3 and 21 and isDate(DateEnrolledInSpecialEducation) = 1 and PrimaryExceptionality is not Null and PrimaryExceptionality <> '' and LeastRestrictiveEnvironment is Not Null and LeastRestrictiveEnvironment <> '' Union Select SSN, FirstName, LastName, MiddleInitial, Grade, SchoolCode, (Select SiteName from tblSites where SiteCode = SchoolCode and SysCode = 'gv_SystemCode') AS SchoolName, BirthDate as Error from virtStudentAttendance Where SchoolCode <> '3999' and isDate(BirthDate) = 1 and DATEPART(month, BirthDate) = 12 and DATEPART(Day, BirthDate) > 1 and DATEDIFF(year, BirthDate, '12/01/'+cast(datepart(year,getdate()) as VARCHAR(4)))-2 not between 3 and 21 and isDate(DateEnrolledInSpecialEducation) = 1 and PrimaryExceptionality is not Null and PrimaryExceptionality <> '' and LeastRestrictiveEnvironment is Not Null and LeastRestrictiveEnvironment <> ''"

X002548
Not Just a Number

15586 Posts

Posted - 2003-02-28 : 10:23:52

I suggest you break it up to find out exactly where the problem is. Error messages do not alway pinpoint the problem, esp. when you get complicated.

Good Luck

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-02-28 : 10:48:45

Try indenting and formatting your SQL to make it more readable, and then go through it line by line.

Why all the UNIONs? What are you trying to get at with this query?

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-02-28 : 11:03:35

I've looked at your code .. each part of your UNION is exactly the same, from the same table, with most of the criteria exactly the same with 1 minor difference in each one.

You split up your recordset and put them back together as a UNION based on these categories:

Group1:
DATEPART(month, BirthDate) < 12

Group2:
DATEPART(month, BirthDate) = 12 and
DATEPART(Day, BirthDate) = 1

Group3:
DATEPART(month, BirthDate) = 12 and
DATEPART(Day, BirthDate) > 1

But when they are UNIONed together, you don't return this distinction anywhere in your query .... and the effect of the all the criteria together is that NO records are filtered at all because all records pass 1 of the 3 .... Why are these in separate UNIONs? For example, there is no field returned that says which of these 3 groups the record falls into, so why break them up?

There is no reason for any of the BirthDate criteria in your query.

Try:

SELECT

SSN, FirstName, MiddleInitial, LastName, Grade, SchoolCode, (Select SiteName from tblSites where SiteCode = SchoolCode and SysCode = 'gv_SystemCode') AS SchoolName, BirthDate as Error

FROM
virtStudentAttendance
WHERE
isDate(BirthDate) = 1 and
SchoolCode <> '3999' and
DATEDIFF(year, BirthDate, '12/01/'+cast(datepart(year,getdate()) as VARCHAR(4)))-1 not between 3 and 21 and
isDate(DateEnrolledInSpecialEducation) = 1 and
PrimaryExceptionality is not Null and
PrimaryExceptionality <> '' and
LeastRestrictiveEnvironment is Not Null and
LeastRestrictiveEnvironment <> ''

If you want to return an indicator of which of these 3 categories a row is in, look into using a CASE expression.

For example, add this expression as part of your SELECT list:

CASE
WHEN DATEPART(month, BirthDate) < 12
THEN 'Not Born in December'
WHEN DATEPART(month, BirthDate) = 12 and DATEPART(Day, BirthDate) = 1
THEN 'Born in 1st day Of December'
ELSE 'Born in December, not first day'
END As Category

your final query could then be:

SELECT

SSN, FirstName, MiddleInitial, LastName, Grade, SchoolCode, (Select SiteName from tblSites where SiteCode = SchoolCode and SysCode = 'gv_SystemCode') AS SchoolName, BirthDate as Error, CASE WHEN DATEPART(month, BirthDate) < 12 THEN 'Not Born in December' WHEN DATEPART(month, BirthDate) = 12 and DATEPART(Day, BirthDate) = 1 THEN 'Born in 1st day Of December' ELSE 'Born in December, not first day' END As Category

FROM
virtStudentAttendance
WHERE
isDate(BirthDate) = 1 and
SchoolCode <> '3999' and
DATEDIFF(year, BirthDate, '12/01/'+cast(datepart(year,getdate()) as VARCHAR(4)))-1 not between 3 and 21 and
isDate(DateEnrolledInSpecialEducation) = 1 and
PrimaryExceptionality is not Null and
PrimaryExceptionality <> '' and
LeastRestrictiveEnvironment is Not Null and
LeastRestrictiveEnvironment <> ''

I hope this helps a little...

- Jeff

Edited by - jsmith8858 on 02/28/2003 11:11:30

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources