ADODB.Command error '800a0d5d'

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

Old Forums

CLOSED - General SQL Server

ADODB.Command error '800a0d5d'

Author

Topic

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-01-21 : 05:01:58

Hello,
i get this error from time to time on our site, what could be wrong

ADODB.Command error '800a0d5d' 

Application uses a value of the wrong type for the current operation. 

/welcome/welcome/messages/sendsms.asp, line 86

line 86 is the line where it calls the SP

set cmdSms = Server.CreateObject("ADODB.Command")

Kristen
Test

22859 Posts

Posted - 2006-01-21 : 06:57:51

You sure its that line? Maybe its the next line and the line counter is off by one.

I would expect the error to be caused by calling an Sproc with a parameter type that doesn't match the data variable in the calling language.

Kristen

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-01-21 : 11:58:23

Its all coming as a surprise to me, 'cos we have been using this script for a long while and the site is a heavily hit site, that sends bulk sms. I made a slight change to the code and since then, once every now and then we have that error.

Am looking now into error handling in VB script, which i think its not as efficient.

heres the full code, that calls the SP from the ASP page


	erorr line 86 >>set cmdSms = Server.CreateObject("ADODB.Command")
		cmdSms.ActiveConnection = db_connection
		cmdSms.CommandText = "dbo.S_sms_sendsms_Billing_web2"
cmdSms.Parameters.Append cmdSms.CreateParameter("@RETURN_VALUE", 3, 4)
cmdSms.Parameters.Append cmdSms.CreateParameter("@csv", 200, 1,8000, receipients)
cmdSms.Parameters.Append cmdSms.CreateParameter("@message", 200, 1,200, message)
cmdSms.Parameters.Append cmdSms.CreateParameter("@IP", 200, 1,25, IP)
cmdSms.Parameters.Append cmdSms.CreateParameter("@senderID", 200, 1,15, SenderID)
cmdSms.Parameters.Append cmdSms.CreateParameter("@user", 200, 1,15, session("Username"))
cmdSms.Parameters.Append cmdSms.CreateParameter("@results", 200, 1,15, "not sent")
		cmdSms.CommandType = 4
		cmdSms.CommandTimeout = 0
		cmdSms.Prepared = true
		
		cmdSms = CmdSms.Execute()

cmdSms_numRows = 0

Kristen
Test

22859 Posts

Posted - 2006-01-21 : 12:15:58

Hmmmm ...

Lets go with the Line Number first then.

I wonder if there is some foul-up with the "pooling" of connections. I've read that sometimes a connection can be reused without it having properly got cleared down - but its old news and I would have thought that that issue would have been fixed by now (if you have an up-to-date version of ADO)

If its only since a recent code change then obviously it makes sense to carefully double check that change.

Any chance that one of those Parameters.Append is getting a value which is "Null" or in some other way mal-formed?

When that happens to me I wrap each parameter in a function that sorts out Null, Empty, "Nothing" and all the other possible duff values that VBS can get stuffed with.


Function kStr(ByRef strData)
Dim intErrNo
Dim strErrMsg
on error resume next
if IsNull(strData) then
    kStr = ""
elseif IsEmpty(strData) then
    kStr = ""
else
    kStr = CStr(strData)
end if
if Err <> 0 Then
    intErrNo = Err.Number
    strErrMsg = Err.Description
End If
on error goto 0
if intErrNo <> 0 Then
    Response.Write("<br>kStr() Error = "& intErrNo & ", Description = "& strErrMsg & "<br>")
    Response.Write("<br>")
    Response.End
End if
End Function

Only other thought is whether the parameters are now longer than the permitted length?

Is @csv nvarchar and thus max 4000, rather than 8000?

Has the VBS variable "receipients" become longer than 8,000 characters?

Ditto for the other parameters.

But we're getting a long way away from line 86

Kristen

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-01-21 : 12:16:58

its kind of driving me crazy here, cos one minute its working and the next minute, it complains of line 86.

I have done a google search, but cant seem to get a definite answer. Except going on a wild goose chase

Most annoying thing, is that it just started giving errors when i made a few changes, yersterday.

>> Am doing a google search and looking at various results, right now

Assistance please ???

Kristen
Test

22859 Posts

Posted - 2006-01-21 : 13:27:50

afrika: Looks like you posted at the same time as me, and might not have seen my answer (and looks like you were replying to a post that is no longer here, i.e.:

">> Am doing a google search and looking at various results, right now"

so might have been deleted).

If you saw my post already and it was no help then ignore!

Kristen

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-01-21 : 19:52:12

Oh Hi Kristen,
AMAZING CODE, i must say

really nice error handling in vb script, i havent actually seen it in that way.

After my last post, i signed out immediately. I would incorporate the error handling function into my scripts and check to see the results.

By the way

1. I ran a select @@version, which was SP4

2. @csv is varchar(8000) * I dont use nvarchar

3. [CODE]on error goto 0[/CODE] This code, goto does not work with VB script ??? Am i wrong here

At present, The code seems to be working now without errors. however, i would add some error handling to trap any resultant errors and log them in an error log table

Once again, Thanks Kristen.

Am still working on it, would certianly give a feedback

Ehi

Kristen
Test

22859 Posts

Posted - 2006-01-22 : 03:27:39

"This code, goto does not work with VB script ??? Am i wrong here"

Well, yes and no!

This is a hang over from the crappy error handling in earlier versions of VB where you COULD "goto" a label when an error was trapped. That doesn't exist anymore, all we have is the even more crappy "on error resume next" and the way that you disable that is ... wait for it! ... "on error goto 0". Did I say it was crap already?!

We have a very simple house rule here.

Turn on "on error resume next"
Execute statement that needs error trapping
If error then save the Error Number & Description to local variables
Turn off error checking

Having any longer scope for the error trapping just leads to errors in the error handling logic not being found because they too get ignored by "on error resume next".

"i would add some error handling to trap any resultant errors and log them in an error log table"

Good plan. Crucial in fact!

We have a VB function called "Crash"

which logs the error and terminates the application. So we can do


IF MyYesNoAnswer = "Y" THEN
   ... do some YES stuff ...
ELSEIF MyYesNoAnswer = "N" THEN
   ... do some ON stuff ...
ELSE
   CRASH("Answer wasn't yes/no [" & MyYesNoAnswer & "]")
END IF

Kristen

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-01-22 : 08:45:10

Thanks a lot "SIR"

One crucial question though....
... why do you always call VB Code crappy ?

I recall a post over a year ago, where you said the same with regards .net and said you preferred VB? If i recall clearly...

...and which frontend code, may i ask is your favourite

We have implemented an error handling and trapping function, which logs the errors but not go life with it. Still pending.

Kristen
Test

22859 Posts

Posted - 2006-01-22 : 09:42:45

I started out [late '70s] with a variant of Basic. It was OK. In those days I disassembled the interpreter and added the functionality I needed to achieve what I wanted but to put it in perspective RAM came on 4K memory boards

... but that was then, and now is now!

VBScript is really crappy. No typed variables; the error trapping is lousy; the classes are "toy versions".

But at the beginning of 2000, when I transitioned to building Web applications, I wanted to be using ASP, so that-was-pretty-much-that.

Most of my life I've programmed with C & C++. I could build functions / libraries for stuff at a very low level - if I wanted to. So I could, for example, build a caching "container" that tightly fitted my needs; its a bit like re-inventing-the-wheel, but for something high-performance and highly-stressed its not a bad idea to be in total control of the tinkering. For caching in VBScript I have to use the Application Object, or possibly an instantiation of the Dictionary Object - with all sorts of inefficiency that that brings, plus issues with multi-processor threading and goodness knows what else.

VBScript just isn't low level enough to allow me to build something decent - the interpreted nature would be a killer.

dotNET is fine, but I hate the part where "It looks familiar to Windows Application VB programmers" - in particular the pretence that its message-passing just-like-windows. The page bloat to achieve this is horrific, the number of round trips sucks performance too; BUT it allows folk with good existing VB skills to build stuff easily; I don't have a problem with that, but for folk newly arriving I don't think that is a good metaphor to be using.

I also don't think that "bolt a lot of building blocks together" is a good approach either. It depends on the application of course - low volume or "thrown together" [e.g. "We're taking over this company, we need this application in a hurry to handle the shortfall of two company structures for 6 months"] is fine by me.

But I'm not in that market, we build applications for multiple-sales and high-efficiency usage. "We want to able to handle X,000 orders a day and give our clients the best possible performance" [which seems like a reasonably objective for a mid-large web application

] is not going to happen IMHO with a simple "bolt-together building blocks" approach; too much loss of control, abstraction at the detriment to performance, and bloat.

But that's just my opinion of course!

Right now I would like to be programming everything in C-Sharp, but the transition of our legacy stuff needs an "optimum moment", and a generous sprinkling of "Cash"

to make the switch. But it brings with it the opportunity to re-factor, or plain re-design, large areas of the system which would definitely be A Good Thing. Our current Engine is nearing 5 years old; we've torn-them-down-and-rebuilt-them about every 4-8 years (start after 4 years, "retire" the old application totally after 8 years), so its overdue.

We've actually only got about 400K of ASP files which drives all of our applications, but the code is kinda complicated!

There are a few 10K's of JavaScript and CSS, and of course there's a fit bit of SQL to go with the ASP (about 14MB

) and a further 10MB of "meta data" that drives the applications.

Kristen

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-01-22 : 19:07:18

>> I started out [late '70s]
RESPECT !!! I got in quite late

>>VBScript is really crappy. No typed variables; the error trapping is lousy; the classes are "toy versions".

Ok, one question, is there any advantage of typed variables ? And you can make it typed with "option explicit"

>>its a bit like re-inventing-the-wheel,
I really dont see anything wrong in reinventing the wheel sometimes to your taste, following conventional standards.

>>with all sorts of inefficiency that that brings

Another question, i would ask, is what do you think of MS SQL product in comparison to other leading products and IIS as a whole. Also if i may ask, what do you think of memory leaks and security loopholes in Windows and in comparion to others.

>>VBScript just isn't low level enough to allow me to build something decent

I guess nowadays nothing is as raw as before??? Even cars today are the same

>>just-like-windows

What do you think of windows ? and in comparison to linux ? (A repeat of the above question)

>>"We want to able to handle X,000 orders a day and give our clients the best possible performance

>>Our current Engine is nearing 5 years old
ASP Cant be that crappy then ? I see reliability and efficiency here

thanks for the info and your time

Ehi

Kristen
Test

22859 Posts

Posted - 2006-01-23 : 01:51:50

"is there any advantage of typed variables"

Well, if a variable is "typed" as a date and you pass it to SQL Server then the newbies wouldn't have the problem with "mm/dd/yy" and "dd/mm/yy" conversions etc.

Also saying "MyDateVariable = myBitVariable" instantly becomes a runtime error!

"And you can make it typed with "option explicit" "

Nope, that just requires you to pre-declare the variable (which IMHO is essential for anyone with even a slightly defensive-programming style of course ...)

"what do you think of MS SQL product in comparison to other leading products and IIS as a whole"

I think that MS SQL is top notch. Although I personally have become seriously worried over the performance hit that some people have had with SP4 - I mean, how can a Service Pack have a dire impact? It should only be fixing bugs, not dramatically changing the product or introducing new features. My expectation (with a Service pack) is that most people will just install it and NOT perform a full set of tests as they would with a new version - actually, most will probably not perform ANY tests!!. Some of the servers we look after have 30 databases on them, for different client applications, and there's no way we could get them to all implement a full set of tests just so we could Service Pack the server ... that dented my confidence a bit - I would have expected MS's QA process to have picked that up so that at the least it was documented.

IIS

We only use IIS to host our "application engine", so pretty much anything would do for us. The folk I'm connected with used IIS and had racks of Windows hosting servers etc., and I'm a "Microsoft man", so that's what we choose. However, the big hosting boys tell me they can put loads of shared sites on a Unix box, and far fewer on Windows/IIS before it starts getting unstable. That's probably an indication that IIS isn't ideal.

"memory leaks and security loopholes in Windows and in comparison to others"

I think that MS sell a lot of stuff. Therefore there are more disgruntled people; Windows poses a bigger target; etc. etc. so more hack attempts are made. The rest are living in Cloud Cuckoo Land that they are immune. And I can rest easier that MORE bugs are being found in MS stuff because of it, and they are getting fixed, and thus MS stuff is getting tighter than the rest. But ... the bolt-bits-together policy I mentioned earlier definitely should take some of the responsibility.

The bolt-together approach makes software that is 1) easy to put together and 2) huge! and beyond the wit-of-man to be sure it is bug-free, or even to know comprehensively how it works. This Windows stuff is just MASSIVE - maybe it has to be, but I would much prefer a policy of more effort to make smaller, leaner, applications because IME that extra effort roots out lots of the problems. It takes longer to market, and probably costs more short term, but long term I've found that route to offer a more stable product.

"What do you think of windows ? and in comparison to linux"

I don't know anything about Linux. But the message handling thing that Windows does (although that's really the GUI rather than the O/S) I found very complex when I first moved to it from DOS. And we made lots of mistakes because of it ... and there were always other messages that we COULD have handled but didn't ... etc. etc.. It amazes me how ANY company, other than MS, gets to the point where they have gathered enough knowledge and skill to write a World Class application.

"ASP Cant be that crappy then ?"

Hehehe ... True, but its taken us more effort than I would have liked, and I cannot do some of the [low level] things that I would like to do. But it did get us up&running quite quickly, and has allowed us an incremental development approach [which is of course frowned on, and even makes me shudder with hindsight!]

Kristen

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-01-23 : 04:15:32

Now, I now see what you mean.

>>Hehehe ... True, but its taken us more effort than I would have liked, and I cannot do some of the [low level] things that I would like to do

Anyway(s), Unix has come from a long line of tradition and since the GUI was a latter inclusion, guess thats more reason to be the most stable OS in the market today.

Also, based on understanding of what you said, I guess all scripting languages except for perl, C, C++ and the likes
1. would have a similar problems and
2. Guess scripts are not as precise them (High level programming languages) ?

its compiled vs interpreted, and i guess there is nothing much one can do, when a webserver is reading off your scripts, and the www was initally designed to be a one way, "html only" street. Every other thing we see today are just addons or extensions, including our crappy ASP

. Guess this is an advantage in .net

>>I think that MS SQL is top notch.
Thats comforting, coming from the guru

>>and far fewer on Windows/IIS before it starts getting unstable. That's probably an indication that IIS isn't ideal.

I heard this a couple of times. Usual story, IIS vs Apache. However, am subscribed to netcraft newletters, and follow the monthly reliable hosting companies regularly and other related issues. I think Windows is doing quite well. With its late entry into the market

I guess the summary of the whole thing is thus: "In man's bid to attain perfection and simplify his life and environment, He employs complexity to create simplicity, which in turn is giving him back complexity"

Someone once said, computing is like " Its a world of gurus trying to build a fool proof system and novices using this 'fool proof' system" and the novices are winning, Because they are far from being fool proof

Nice read

Thanks Kristen,

Kristen
Test

22859 Posts

Posted - 2006-01-23 : 11:56:25

"He employs complexity to create simplicity, which in turn is giving him back complexity"

Wasn't it Henry Ford who said something like "Simplify and add lightness" ?? I like that approach

Kristen

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-01-23 : 12:18:43

Really cant remember who said so.

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-01-24 : 13:23:53

Hi Kristen,
I finally found out the problem. Quite amussing i must say.

Our application is a web2phone sms script that connects directly to a gateway.

Both the senderID and the message to be sent must be converted to html encoding characters before they are sent. So in doing so, spaces and other characters e.g. the "&" sign must be converted to html encoded values before they are sent

Now our senderID and message columns where 15 and 200 respectively, owing to the fact that senderID characters must be a maximum of 11 and messages a maximum of 160. However, with the conversion, every converted character is giving a value e.g. spaces are %20 etc. So in these case(s) it would overflow, and hence give the error.

I have increased our data lengths to 30 for senderID and message to 300. So the SP can receive and process them correctly.

Now i think i deserve to buy myself a drink

Ehi

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-01-24 : 13:30:25

Oh thanks for the error handling script. Works like a charm, am incorporating it into every other page

or better yet. Option 2.

Dont pass the url encode characters to the SP, just keep them in the asp page. Both still work fine for us.

Kristen
Test

22859 Posts

Posted - 2006-01-24 : 13:35:38

"Now i think i deserve to buy myself a drink"

I've already got mine ... Cheers!

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-01-24 : 13:42:36

Cheers 2u2 !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources