Troubleshooting an attempt to open connection

I need some help with troubleshooting.
When I execute my vbs script in a DOS environment it runs OK. But
when it is called by an asp calling client, it does not work and does
not indicate why.
I put some tracing statements to find out where it errored and it
echoes Point1 and Point2 but never reaches Point 2A. So I guess the
open connection must have failed.
1. What should I do to handle a connection error? On error resume
next seems strange...
2. Is my connection open statement incorrect? What should it be?
3. Again I'm confused when all players are vbs scripts, it runs OK.
When an asp client calls it, it simply does not work.

Thanks

Set objTHoldCon = CreateObject("ADODB.Connection")
Set objTHoldCom = CreateObject("ADODB.Command")

WScript.Echo "Point1"
'Opening the connection
objTHoldCon.Open "audit","sa","password"

WScript.Echo "Point2"

'assigning the command object parameters
Set objTHoldCom.ActiveConnection = objTHoldCon
objTHoldCom.CommandText = "usp_Thresholds_GetStatus"
objTHoldCom.CommandType = adCmdStoredProc

WScript.Echo "StatusHelper Point2a"

Tim
Fri Oct 19 23:01:08 PDT 2007

Perhaps the default ASP account (typically IUSR_[machinename]) doesn't have
access to the required info to open the connection.
Are you using a DSN: what database?

Why not just make the code part of the ASP page ? At least it would be
easier to debug.

Tim


<Hoa.Hoa.Nguyen@gmail.com> wrote in message
news:1192851667.501752.237320@z24g2000prh.googlegroups.com...
>I need some help with troubleshooting.
> When I execute my vbs script in a DOS environment it runs OK. But
> when it is called by an asp calling client, it does not work and does
> not indicate why.
> I put some tracing statements to find out where it errored and it
> echoes Point1 and Point2 but never reaches Point 2A. So I guess the
> open connection must have failed.
> 1. What should I do to handle a connection error? On error resume
> next seems strange...
> 2. Is my connection open statement incorrect? What should it be?
> 3. Again I'm confused when all players are vbs scripts, it runs OK.
> When an asp client calls it, it simply does not work.
>
> Thanks
>
> Set objTHoldCon = CreateObject("ADODB.Connection")
> Set objTHoldCom = CreateObject("ADODB.Command")
>
> WScript.Echo "Point1"
> 'Opening the connection
> objTHoldCon.Open "audit","sa","password"
>
> WScript.Echo "Point2"
>
> 'assigning the command object parameters
> Set objTHoldCom.ActiveConnection = objTHoldCon
> objTHoldCom.CommandText = "usp_Thresholds_GetStatus"
> objTHoldCom.CommandType = adCmdStoredProc
>
> WScript.Echo "StatusHelper Point2a"
>

Richard
Sat Oct 20 06:06:02 PDT 2007


<Hoa.Hoa.Nguyen@gmail.com> wrote in message
news:1192851667.501752.237320@z24g2000prh.googlegroups.com...
>I need some help with troubleshooting.
> When I execute my vbs script in a DOS environment it runs OK. But
> when it is called by an asp calling client, it does not work and does
> not indicate why.
> I put some tracing statements to find out where it errored and it
> echoes Point1 and Point2 but never reaches Point 2A. So I guess the
> open connection must have failed.
> 1. What should I do to handle a connection error? On error resume
> next seems strange...
> 2. Is my connection open statement incorrect? What should it be?
> 3. Again I'm confused when all players are vbs scripts, it runs OK.
> When an asp client calls it, it simply does not work.
>
> Thanks
>
> Set objTHoldCon = CreateObject("ADODB.Connection")
> Set objTHoldCom = CreateObject("ADODB.Command")
>
> WScript.Echo "Point1"
> 'Opening the connection
> objTHoldCon.Open "audit","sa","password"
>
> WScript.Echo "Point2"
>
> 'assigning the command object parameters
> Set objTHoldCom.ActiveConnection = objTHoldCon
> objTHoldCom.CommandText = "usp_Thresholds_GetStatus"
> objTHoldCom.CommandType = adCmdStoredProc
>
> WScript.Echo "StatusHelper Point2a"
>

The statement:

Set objTHoldCom.ActiveConnection = objTHoldCon

should not have the "Set". It should be:

objTHoldCom.ActiveConnection = objTHoldCon

--
Richard Mueller
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net
--

Bob
Sat Oct 20 08:06:23 PDT 2007

Richard Mueller [MVP] wrote:
> The statement:
>
> Set objTHoldCom.ActiveConnection = objTHoldCon
>
> should not have the "Set". It should be:
>
> objTHoldCom.ActiveConnection = objTHoldCon
>

No, that's wrong. The "Set" should be there to avoid ADO creating a new
implicit connection using objTHoldCon's connection string.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Bob
Sat Oct 20 08:14:19 PDT 2007

Hoa.Hoa.Nguyen@gmail.com wrote:
> I need some help with troubleshooting.
> When I execute my vbs script in a DOS environment it runs OK. But
> when it is called by an asp calling client, it does not work and does
> not indicate why.

Please post the asp errors to microsoft.public.inetserver.asp.db
Make sure you turn off your browser's "friendly errors" setting so you can
determine the actual error:
http://www.aspfaq.com/show.asp?id=2109

> I put some tracing statements to find out where it errored and it
> echoes Point1 and Point2 but never reaches Point 2A. So I guess the
> open connection must have failed.
> 1. What should I do to handle a connection error? On error resume
> next seems strange...

It may seem strange, but that's the only way to catch errors. I'm not sure
what seems strange to you about it.

on error resume next
statement that might raise error
if err <> 0 then
error occurred - handle it
end if

> 2. Is my connection open statement incorrect? What should it be?
> 3. Again I'm confused when all players are vbs scripts, it runs OK.
> When an asp client calls it, it simply does not work.
>
> Thanks
>
> Set objTHoldCon = CreateObject("ADODB.Connection")
> Set objTHoldCom = CreateObject("ADODB.Command")
>
> WScript.Echo "Point1"
> 'Opening the connection
> objTHoldCon.Open "audit","sa","password"

It is a serious mistake to use the sa account in your application code. The
sa account should be reserved for what it is designed for: system
administration. It is an even more serious error to put the sa password in
plain site in your application code. In the wrong hands, that sa account
could be used to wreak serious havoc, not only to your database, but also
the OS and, in some situations, the network.
Best practices include creating a limited permissions account with only the
permissions needed to perform the functions required by your application.

As for using an ODBC dsn in your connection, I do not recommend it:
http://www.aspfaq.com/show.asp?id=2126


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Richard
Sat Oct 20 09:25:12 PDT 2007


"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:utq40qyEIHA.5360@TK2MSFTNGP03.phx.gbl...
> Richard Mueller [MVP] wrote:
>> The statement:
>>
>> Set objTHoldCom.ActiveConnection = objTHoldCon
>>
>> should not have the "Set". It should be:
>>
>> objTHoldCom.ActiveConnection = objTHoldCon
>>
>
> No, that's wrong. The "Set" should be there to avoid ADO creating a new
> implicit connection using objTHoldCon's connection string.
>

I didn't know that. I tried a few programs using Set and it works. I assume
you are correct about creating unnecessary connections. I had gone by links
such as this:

http://www.microsoft.com/technet/scriptcenter/guide/sas_ads_emwf.mspx?mfr=true

and many others that do not use the set. However I found this link that
does:

http://msdn2.microsoft.com/en-us/library/Aa746471.aspx

I need to correct the information on my web site.

--
Richard Mueller
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net
--

Bob
Sat Oct 20 10:17:03 PDT 2007

Richard Mueller [MVP] wrote:
> "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
> news:utq40qyEIHA.5360@TK2MSFTNGP03.phx.gbl...
>> Richard Mueller [MVP] wrote:
>>> The statement:
>>>
>>> Set objTHoldCom.ActiveConnection = objTHoldCon
>>>
>>> should not have the "Set". It should be:
>>>
>>> objTHoldCom.ActiveConnection = objTHoldCon
>>>
>>
>> No, that's wrong. The "Set" should be there to avoid ADO creating a
>> new implicit connection using objTHoldCon's connection string.
>>
>
> I didn't know that. I tried a few programs using Set and it works. I
> assume you are correct about creating unnecessary connections.

The reasoning goes like this:
Objects are assigned to variables and properties using the Set keyword.
Without the Set keyword, the default property of the object is assigned
rather than the object itself. In this case, the ConnectString property of
the Connection is assigned. When you assign a string (assumed to contain a
valid connection string) to ActiveConnection, it causes an implicit
connection to be opened when the parent object is opened or executed as the
case may be.

> I had gone by links such as this:
>
> http://www.microsoft.com/technet/scriptcenter/guide/sas_ads_emwf.mspx?mfr=true
>
> and many others that do not use the set.

Yes - very lazy (I'm being charitable in using "lazy" rather than
"ignorant") of those doc writers ... this is not the only case where the
documentation does not show the best practice. For example, it should not
take long to find VB examples using "Dim cn as New ADODB.Connection" ...

> However I found this link
> that does:
>
> http://msdn2.microsoft.com/en-us/library/Aa746471.aspx
>
> I need to correct the information on my web site.
>

> Microsoft MVP Scripting and ADSI
> Hilltop Lab - http://www.rlmueller.net

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Hoa
Sat Oct 20 18:19:44 PDT 2007

On Oct 20, 10:14 am, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
wrote:
> Hoa.Hoa.Ngu...@gmail.com wrote:
> > I need some help with troubleshooting.
> > When I execute my vbs script in a DOS environment it runs OK. But
> > when it is called by an asp calling client, it does not work and does
> > not indicate why.
>
> Please post the asp errors to microsoft.public.inetserver.asp.db
> Make sure you turn off your browser's "friendly errors" setting so you can
> determine the actual error:http://www.aspfaq.com/show.asp?id=2109
>
> > I put some tracing statements to find out where it errored and it
> > echoes Point1 and Point2 but never reaches Point 2A. So I guess the
> > open connection must have failed.
> > 1. What should I do to handle a connection error? On error resume
> > next seems strange...
>
> It may seem strange, but that's the only way to catch errors. I'm not sure
> what seems strange to you about it.
>
> on error resume next
> statement that might raise error
> if err <> 0 then
> error occurred - handle it
> end if
>
> > 2. Is my connection open statement incorrect? What should it be?
> > 3. Again I'm confused when all players are vbs scripts, it runs OK.
> > When an asp client calls it, it simply does not work.
>
> > Thanks
>
> > Set objTHoldCon = CreateObject("ADODB.Connection")
> > Set objTHoldCom = CreateObject("ADODB.Command")
>
> > WScript.Echo "Point1"
> > 'Opening the connection
> > objTHoldCon.Open "audit","sa","password"
>
> It is a serious mistake to use the sa account in your application code. The
> sa account should be reserved for what it is designed for: system
> administration. It is an even more serious error to put the sa password in
> plain site in your application code. In the wrong hands, that sa account
> could be used to wreak serious havoc, not only to your database, but also
> the OS and, in some situations, the network.
> Best practices include creating a limited permissions account with only the
> permissions needed to perform the functions required by your application.
>
> As for using an ODBC dsn in your connection, I do not recommend it:http://www.aspfaq.com/show.asp?id=2126
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"

Hoa
Sat Oct 20 19:40:11 PDT 2007

Thanks, Bob. All points taken and agreed. I used the configuration
wizard and got past the connection error.
I also created a user/login instead of using the sa account.
Thanks for the help