Obscure Error Message in Query Analyser
-
Hi People.
I hope you can help me with the following error message that I get when debugging a new SPROC in Query Analyser.
"[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification"
It tales two parameters, (one is a varchar(10)='', and the other is a datetime). When debugging, the SPROC quickly aborts and displays the above error message, and nothing else! No line numbers, nothing.
At first, I did have some PRINT comands, using CAST() function to convert a couple of ints to varchar, so I deleted them all.
I am still getting this error message, so I tried adding "WITH RECOMPILE" to the SPROC.
Still the same error message.
In Enterprise Manager, where I create the SPROC, when I click "Check Syntax", it returns "Syntax Check Successful".
Don't know what is wron, or where to go from there ...
Any Ideas please folks? Have come to a sudden stop.
Regards,
Lester Vincent
Sydney
-
can you post your CAST statement?
_____________
Code for TallyGenerator -
Just guess:
do you actually use something like PRINT ... + CAST(... as varchar(10)) ?
_____________
Code for TallyGenerator -
Hi Sergy. Yes, I did use this format at first.
Something like
PRINT 'Number of rows='+ CAST(@intRows as varchar(3))
(which I think is wrong, because it contains the "(3)" value and CAST doesn't require it)
But then I changed this to
PRINT 'Number of rows='+ CAST(@intRows as varchar)
The second format looked right, but the error message was the same.
But now, I have deleted all PRINT commands and am still getting the same error, even with "WITH RECOMPILE" added.
Any other ideas, Sergy?
Lester Vincent
Sydney
-
1st - you cannot use any operation within PRINT command.
Construction like
PRINT 'Number of rows='+ CAST(@intRows as varchar)
just not allowed.
declare @string
SELECT @string = 'Number of rows='+ CAST(@intRows as varchar(10) )
PRINT @string
2nd - you have deleted NOT ALL PRINT commands.
Or you have another use of CAST in another not allowed construction.
Post all your lines using CAST.
_____________
Code for TallyGenerator -
Sergiy,
I have double-checked and the SPROC contains no instances of PRINT or CAST.
The error message still occurs and the SPROC aborts on debugging in Query Analyser.
So I am still stuck with this issue. I am wondering if, because a couple of incorrectly formatted CAST statements originally existed, they are still in there somewhere. I thought if I were to add "WITH RECOMPILE" to my SPROC, that it might fix the problem, but even with that it still happens.
(BTW, the BOL entry on CAST statements shows the following
format
CAST ( expression AS data_type )
eg, It does not require "varchar(3)", simply "varchar".
It seems the CONVERT alternative does require the data length to be included.
Also, the "=" used in my PRINT example in prevous posting was inside single quotes, ie, not as an operator)
Thanks for taking the trouble, Sergiy. I am still living in hope on this one.
Lester Vincent
Sydney
-
Hi Lester,
While you are correct in saying that the size part of varchar is optional in teh Cast, you should really specify the size as it will default to 30 as has been discussed in previous posts, which can cause some confusion.
Cheers,
Rodney.
-
Lester,
despite you have "=" between quotes, you have "+" outside the quotes, and this is operator as well.
Than, there is no datatype varchar in SQL. There is varchar(n). You can check it with BOL.
And if you are receiving error mesage related to CAST operator then THERE IS CAST operator in you SP.
Are you trying to execute the same SP as you are modifying?
Do you qualify your SP name with "dbo." everywhere?
My suggestion.
Don't cut corners. Do every step properly. If you are not sure about anything open BOL.
It will save you a lot of time, much more than you are trying to save.
_____________
Code for TallyGenerator -
Thanks Rodney.
(What you have stated suggests there was nothing wrong with the way I had originally written the CAST statements.)
What I'd really like to discover is why the SPROC aborts and this crazy message keeps appearing when the SPROC contains neither PRINT, CAST, nor for that matter, CONVERT statements.
Any ideas folks, please.
Lester Vincent
Sydney
-
Hi Lester,
Yes I realise that it didn't solve your sproc problem but it's best practice, and when you start using cast with a varchar bigger than 30 characters it would cause a problem... And save you a posting in the future! Plus who knows what MS might do in the future with "defaults" like this? As Sergiy said varchar doesn't strictly speaking require a size... So it isn't a "default" in the true sense.
As for you problem with the Sproc without seeing the sproc I wouldn't know where to start!
If you could either post the sproc, or a reasonable test version that we can see and test, then someone on the fourm could post a solution, rathe rthan possible issues.
Hope this helps in some small way.
Rodney.
-
Thanks Rodney. I appreciate your input on this and take your point regarding data type declaration. (The optionality is something I picked up from a text on SQLS2K.
I think I will save the "visible" SPROC code to a Notepad file, delete the existing SPROC, and try re-creating it from the NotePad file.
Sergiy tells me a CAST statement is still in there somewhere, but there are none (visible, at least), and no PRINT statements either.
The SPROC is quite lengthy, and as part of this, I had to declare 51 local variables (all of which parse correctly). The UDF called by the SPROC is also quite lengthy, so I'd probably be kicked off the forum if I posted it all, and it would take more time than you would have available to examine it.
I do not generally qualify my SPROCs with "dbo." , but when listed in Enterprise Manager, they all have it.
Guess I'll have to keep on anyway.
I thought some other folk would have encountered this one somewhere previously.
Regards,
Lester Vincent
Sydney
-
Hi Lester,
You don't neccessarily have post the whole sproc just the important bits..
But having said that and re-read you previous comments. It does sound as though one the input variables is being passed a value which doesn't match the datatype of the input variable hence the error, has SQL does try and perform a implicit cast on some data types. i.e. char/ varchar to datetime format. if the char was something like '20060102' SQL would also see that as 02/01/2006 (sorry UK format).
So the error could be due to that.
Cheers,
Rodney.
-
Thanks Rodney. We're all friends here, as far as date format goes. I notice Sergiy is a kiwi, you're from the UK, and I'm in Aussieland.
As far as the date goes, I used GetDate() to see what format it produced today's date in, and it showed "Apr 05 2006", ie US format.
Since my last posting, I went into Query Analyser, got the same obscure message again when I right-mouse clicked the SPROC and selected "debug". I looked at the other options in the shortcut menu, selected "Open". It prompted for the parameter values, then I clicked the "Execute" button.
Interestingly, it ran as well as it could, (the mad error message didn't show), but it did tell me, so far
(a) that one of the table-names could not be found, PLUS a line Number!!!
(b) fixed that, and now it tells me it cannot find a column by its name, PLUS - a line number!!
Just what I needed! The table has old data and the column is a new one added since the data was created. I can fix that because I can SEE it!
I feel I am making some progress now, and want to thank both you and Sergiy for putting up with my ignorance.
It seems that "Debug" is not the only option to use here. A pity it has taken me so long to find out. I suppose now you will tell me I should have one that in the first place.
Regards,
Lester Vincent
Sydney
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply