Msg 1038, Level 15, State 4, Procedure spCaseCrossCheck, Line 20 1

[1DMF]

What does that error mean?

An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.

It's higlighting the following line.

WHERE (((Replace(Business_Register.Postcode," ","")) Like @PCode + '%' AND ((Business_Register.DOB1) Like @DOB1 +  '%')) OR (((Replace(Business_Register.Postcode," ","")) Like @PCode + '%') AND ((Business_Register.DOB2) Like @DOB2 +  '%')))

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[1DMF]

OK, sorry , I'd copied from MS Access and forgot to change the double quotes to single.

However, I'm still getting an error I don't understand...

Msg 306, Level 16, State 2, Procedure spCaseCrossCheck, Line 18
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator

But the line it is moaning about is a SELECT, so what comparison is it talking about?

SELECT Business_Register.Sub_Date, Business_Register.Regulated, Business_Register.CNames, Business_Register.Address, Business_Register.Postcode, Business_Register.Client_Source, Business_Register.Introducer, Business_Register.Prod_Type, Business_Register.Property_Val, Business_Register.Amount, Business_Register.DOB1, Business_Register.DOB2, Business_Register.C1_Retire, Business_Register.C2_Retire, Business_Register.C1_Employment, Business_Register.C2_Employment, Business_Register.Ext_Retire, Business_Register.Repay_Method, Business_Register.Prod_Provider, Business_Register.Sub_Route, Business_Register.Rate_Type, Business_Register.Init_Rate, Business_Register.Term, Business_Register.Init_Period, Business_Register.LTV, Business_Register.Prod_Status, Business_Register.Client_Status, Business_Register.Offer_Date, Business_Register.Comp_Date, Business_Register.APR, Business_Register.Commission, [App_Fee]+[Comp_Fee] AS [Client fee], Business_Register.AR_Gross, Business_Register.NPW_Date, DateAdd(day,-90,[Init_Period]) AS [Review date], Business_Register.Diary_Date, Business_Register.Status, Business_Register.Documents, Business_Register.Check_Flag, Business_Register.Adv_MemNo

It 'parses' successfully , just won't execute?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[1DMF]

It was a duff, bogus message! Had nothing to do with the line it claimed was the error.

it was relating to the 'Group By' clause, i deleted it and it worked fine!

Why does MS T-SQL GUI report errors against line numbers that (a) don't exist as no line numbers are show and (b) aren't even the correct line relating to the error?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[gmmastros]

Why does MS T-SQL GUI report errors against line numbers that (a) don't exist as no line numbers are show and (b) aren't even the correct line relating to the error?

You're not the first person to have noticed this. The line number that it reports is the line number for the "batch" of TSQL commands. In the case of a stored procedure, it starts counting after the GO statement.

In SQL Server Management Studio, when you right click a stored procedure and click Modify, you get something like this:

USE [YourDatabaseName]
GO
/****** Object:  StoredProcedure [dbo].[ProcedureName]    Script Date: 01/20/2012 09:49:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER Procedure [dbo].[ProcedureName]  
	@ParameterName Integer  
As  
SET NOCOUNT ON  
-- More Stuff Here

Whenever I am working on a stored procedure and get a compile error, I usually delete the top part so that the first line in the window is ALTER Procedure. When you do this, the line number of the error message will line up with the actual line number.

Also... often times (but not always) you can double click on the part of the error message that includes the line number and the code window will jump to that part of the code.

Hope this helps.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

Thanks George,

I did do the ole click-a-rooney, but that's when it took me to the SELECT statement, yet I found the error was on the GROUP BY?

Hey ho, it's all working sweet now!


"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta