Migrating ASP/MSSQL app from IIS 4 to IIS 6

We have moved an Internet and Intranet site over to our IIS 6 MS Small
Business Server 2003. The Internet site works fine, but was done by a company
recently. The Intranet site is older and we're having problems from the get
go. The first thing I've hit a problem with, I cannot seem to track down the
cause. I have the following code:

Set cnn=Server.CreateObject("ADODB.Connection")
cnn.Open "EXECBOB","SA",""
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = cnn
cmd.CommandType = 1
Set rst = Server.CreateObject("ADODB.Recordset")
cmd.CommandText = "SELECT * FROM Sendout_Records ORDER BY Arranged_Date DESC"
rst.Open cmd, , 3, 1
companyID=rst(6)
apptDate=rst(1)
latestJO="Congratulations to "&recruiterName(rst(11))&" for posting the
latest send out: "&PCase(rst(10))&" with "
rst.Close

That recordset is coming back empty, however, if I run the commandText in
the MSSQL ent mgr directlry, brings up many records. The 'EXECBOB' is the
ODBC DSN connection and I've tested to be sure it is working and pointing to
the proper new MSSQL 2000 server. So, I looked at the code for accessing data
in the Internet site and it is different:

Set ConnSQL = Server.CreateObject("ADODB.Connection")
ConnSQL.ConnectionTimeout = 30
ConnSQL.ConnectionString = "driver={SQL
Server};server=mrisbs2k3;database=execbob;Uid=sa;Pwd="
ConnSQL.Open
strSQL = "SELECT * FROM Sendout_Records ORDER BY Arranged_Date DESC"
Set rst = CreateObject("ADODB.Recordset")
Set rst = ConnSQL.Execute(strSQL)
companyID=rst(7)
apptDate=rst(1)
response.write rst(2) & "-" & rst(7)
latestJO="Congratulations to "&recruiterName(rst(11))&" for posting the
latest send out: "&PCase(rst(10))&" with "
rst.Close

This populates the the recordset fine, does this old code not work?

Thanks for any help....Robert

Re: Migrating ASP/MSSQL app from IIS 4 to IIS 6

WebTent wrote:
> We have moved an Internet and Intranet site over to our IIS 6 MS Small
> Business Server 2003. The Internet site works fine, but was done by a
> company recently. The Intranet site is older and we're having
> problems from the get go. The first thing I've hit a problem with, I
> cannot seem to track down the cause. I have the following code:
>
> Set cnn=Server.CreateObject("ADODB.Connection")
> cnn.Open "EXECBOB","SA",""

1. Don't use your sa account for your applications. Create a
limited-permission account to use instead. Using sa can lead to bad habits.
sa should only be used for server administration. Nothing else. The
potential for misuse in the wrong hands is huge.
2. A blank password for the sa account?!? Have you never heard of Code
Red??? Hopefully you've just censored the password so you could post your
connection's Open statement.
3. Avoid using ODBC: http://www.aspfaq.com/show.asp?id=2126

> Set cmd = Server.CreateObject("ADODB.Command")

It's not fatal, but I don't see why you are using an explicit Command
object.

> cmd.ActiveConnection = cnn
> cmd.CommandType = 1
> Set rst = Server.CreateObject("ADODB.Recordset")
> cmd.CommandText = "SELECT * FROM Sendout_Records ORDER BY

Don't use selstar: http://www.aspfaq.com/show.asp?id=2096

> Arranged_Date DESC"
> rst.Open cmd, , 3, 1

Again, nothing fatal, but I don't see why you are using anything but the
default forward-only cursor. This code could be simplified to:

sSQL="SELECT FROM Sendout_Records " & _
"ORDER BY Arranged_Date DESC"
set rst=cnn.execute(sSQL,,1)

> companyID=rst(6)
> apptDate=rst(1)
> latestJO="Congratulations to "&recruiterName(rst(11))&" for posting
> the latest send out: "&PCase(rst(10))&" with "
> rst.Close
>
> That recordset is coming back empty, however, if I run the
> commandText in the MSSQL ent mgr directlry, brings up many records.

Use SQL Profiler to verify that the commad text you expect to be executed is
actually what is being exxecuted.

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Migrating ASP/MSSQL app from IIS 4 to IIS 6

Thanks, I know, I know....I am a complete outsider on this project, I removed
the password for posting. I don't want to go into specifics, we are now
developing on a trusted network, I am just trying to match up with the old
code as much as possible to see if this all has to be re-written. So, I take
from your response, the old code should work (nothing fatal) although we need
to recommend this customer fix it? If the things you listed are the reasons
for the problems we are having, then a re-write is the only way, do you think
this code should work to get them running. Then perhaps we fix things?


"Bob Barrows [MVP]" wrote:

> WebTent wrote:
> > We have moved an Internet and Intranet site over to our IIS 6 MS Small
> > Business Server 2003. The Internet site works fine, but was done by a
> > company recently. The Intranet site is older and we're having
> > problems from the get go. The first thing I've hit a problem with, I
> > cannot seem to track down the cause. I have the following code:
> >
> > Set cnn=Server.CreateObject("ADODB.Connection")
> > cnn.Open "EXECBOB","SA",""
>
> 1. Don't use your sa account for your applications. Create a
> limited-permission account to use instead. Using sa can lead to bad habits.
> sa should only be used for server administration. Nothing else. The
> potential for misuse in the wrong hands is huge.
> 2. A blank password for the sa account?!? Have you never heard of Code
> Red??? Hopefully you've just censored the password so you could post your
> connection's Open statement.
> 3. Avoid using ODBC: http://www.aspfaq.com/show.asp?id=2126
>
> > Set cmd = Server.CreateObject("ADODB.Command")
>
> It's not fatal, but I don't see why you are using an explicit Command
> object.
>
> > cmd.ActiveConnection = cnn
> > cmd.CommandType = 1
> > Set rst = Server.CreateObject("ADODB.Recordset")
> > cmd.CommandText = "SELECT * FROM Sendout_Records ORDER BY
>
> Don't use selstar: http://www.aspfaq.com/show.asp?id=2096
>
> > Arranged_Date DESC"
> > rst.Open cmd, , 3, 1
>
> Again, nothing fatal, but I don't see why you are using anything but the
> default forward-only cursor. This code could be simplified to:
>
> sSQL="SELECT FROM Sendout_Records " & _
> "ORDER BY Arranged_Date DESC"
> set rst=cnn.execute(sSQL,,1)
>
> > companyID=rst(6)
> > apptDate=rst(1)
> > latestJO="Congratulations to "&recruiterName(rst(11))&" for posting
> > the latest send out: "&PCase(rst(10))&" with "
> > rst.Close
> >
> > That recordset is coming back empty, however, if I run the
> > commandText in the MSSQL ent mgr directlry, brings up many records.
>
> Use SQL Profiler to verify that the commad text you expect to be executed is
> actually what is being exxecuted.
>
> Bob Barrows
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>
>

Re: Migrating ASP/MSSQL app from IIS 4 to IIS 6

The profiler show the following, not what 'N' is before the sql string:

declare @P1 int
set @P1=180150001
declare @P2 int
set @P2=8
declare @P3 int
set @P3=1
declare @P4 int
set @P4=0
exec sp_cursoropen @P1 output, N'SELECT * FROM Sendout_Records', @P2 output,
@P3 output, @P4 output
select @P1, @P2, @P3, @P4

"Bob Barrows [MVP]" wrote:

> WebTent wrote:
> > We have moved an Internet and Intranet site over to our IIS 6 MS Small
> > Business Server 2003. The Internet site works fine, but was done by a
> > company recently. The Intranet site is older and we're having
> > problems from the get go. The first thing I've hit a problem with, I
> > cannot seem to track down the cause. I have the following code:
> >
> > Set cnn=Server.CreateObject("ADODB.Connection")
> > cnn.Open "EXECBOB","SA",""
>
> 1. Don't use your sa account for your applications. Create a
> limited-permission account to use instead. Using sa can lead to bad habits.
> sa should only be used for server administration. Nothing else. The
> potential for misuse in the wrong hands is huge.
> 2. A blank password for the sa account?!? Have you never heard of Code
> Red??? Hopefully you've just censored the password so you could post your
> connection's Open statement.
> 3. Avoid using ODBC: http://www.aspfaq.com/show.asp?id=2126
>
> > Set cmd = Server.CreateObject("ADODB.Command")
>
> It's not fatal, but I don't see why you are using an explicit Command
> object.
>
> > cmd.ActiveConnection = cnn
> > cmd.CommandType = 1
> > Set rst = Server.CreateObject("ADODB.Recordset")
> > cmd.CommandText = "SELECT * FROM Sendout_Records ORDER BY
>
> Don't use selstar: http://www.aspfaq.com/show.asp?id=2096
>
> > Arranged_Date DESC"
> > rst.Open cmd, , 3, 1
>
> Again, nothing fatal, but I don't see why you are using anything but the
> default forward-only cursor. This code could be simplified to:
>
> sSQL="SELECT FROM Sendout_Records " & _
> "ORDER BY Arranged_Date DESC"
> set rst=cnn.execute(sSQL,,1)
>
> > companyID=rst(6)
> > apptDate=rst(1)
> > latestJO="Congratulations to "&recruiterName(rst(11))&" for posting
> > the latest send out: "&PCase(rst(10))&" with "
> > rst.Close
> >
> > That recordset is coming back empty, however, if I run the
> > commandText in the MSSQL ent mgr directlry, brings up many records.
>
> Use SQL Profiler to verify that the commad text you expect to be executed is
> actually what is being exxecuted.
>
> Bob Barrows
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>
>

Re: Migrating ASP/MSSQL app from IIS 4 to IIS 6

The N just makes the string unicode. The sp_cursoropen procedure requires
nvarchar arguments.


WebTent wrote:
> The profiler show the following, not what 'N' is before the sql
> string:
>
> declare @P1 int
> set @P1=180150001
> declare @P2 int
> set @P2=8
> declare @P3 int
> set @P3=1
> declare @P4 int
> set @P4=0
> exec sp_cursoropen @P1 output, N'SELECT * FROM Sendout_Records', @P2
> output, @P3 output, @P4 output
> select @P1, @P2, @P3, @P4
>
> "Bob Barrows [MVP]" wrote:
>
>> WebTent wrote:
>>> We have moved an Internet and Intranet site over to our IIS 6 MS
>>> Small Business Server 2003. The Internet site works fine, but was
>>> done by a company recently. The Intranet site is older and we're
>>> having problems from the get go. The first thing I've hit a problem
>>> with, I cannot seem to track down the cause. I have the following
>>> code:
>>>
>>> Set cnn=Server.CreateObject("ADODB.Connection")
>>> cnn.Open "EXECBOB","SA",""
>>
>> 1. Don't use your sa account for your applications. Create a
>> limited-permission account to use instead. Using sa can lead to bad
>> habits. sa should only be used for server administration. Nothing
>> else. The potential for misuse in the wrong hands is huge.
>> 2. A blank password for the sa account?!? Have you never heard of
>> Code Red??? Hopefully you've just censored the password so you could
>> post your connection's Open statement.
>> 3. Avoid using ODBC: http://www.aspfaq.com/show.asp?id=2126
>>
>>> Set cmd = Server.CreateObject("ADODB.Command")
>>
>> It's not fatal, but I don't see why you are using an explicit Command
>> object.
>>
>>> cmd.ActiveConnection = cnn
>>> cmd.CommandType = 1
>>> Set rst = Server.CreateObject("ADODB.Recordset")
>>> cmd.CommandText = "SELECT * FROM Sendout_Records ORDER BY
>>
>> Don't use selstar: http://www.aspfaq.com/show.asp?id=2096
>>
>>> Arranged_Date DESC"
>>> rst.Open cmd, , 3, 1
>>
>> Again, nothing fatal, but I don't see why you are using anything but
>> the default forward-only cursor. This code could be simplified to:
>>
>> sSQL="SELECT FROM Sendout_Records " & _
>> "ORDER BY Arranged_Date DESC"
>> set rst=cnn.execute(sSQL,,1)
>>
>>> companyID=rst(6)
>>> apptDate=rst(1)
>>> latestJO="Congratulations to "&recruiterName(rst(11))&" for posting
>>> the latest send out: "&PCase(rst(10))&" with "
>>> rst.Close
>>>
>>> That recordset is coming back empty, however, if I run the
>>> commandText in the MSSQL ent mgr directlry, brings up many records.
>>
>> Use SQL Profiler to verify that the commad text you expect to be
>> executed is actually what is being exxecuted.
>>
>> Bob Barrows
>>
>> --
>> Microsoft MVP -- ASP/ASP.NET
>> Please reply to the newsgroup. The email account listed in my From
>> header is my spam trap, so I don't check it very often. You will get
>> a quicker response by posting to the newsgroup.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Migrating ASP/MSSQL app from IIS 4 to IIS 6

The only way to find out is to create a test page that uses my suggestions.

Bob Barrows

WebTent wrote:
> Thanks, I know, I know....I am a complete outsider on this project, I
> removed the password for posting. I don't want to go into specifics,
> we are now developing on a trusted network, I am just trying to match
> up with the old code as much as possible to see if this all has to be
> re-written. So, I take from your response, the old code should work
> (nothing fatal) although we need to recommend this customer fix it?
> If the things you listed are the reasons for the problems we are
> having, then a re-write is the only way, do you think this code
> should work to get them running. Then perhaps we fix things?
>
>
> "Bob Barrows [MVP]" wrote:
>
>> WebTent wrote:
>>> We have moved an Internet and Intranet site over to our IIS 6 MS
>>> Small Business Server 2003. The Internet site works fine, but was
>>> done by a company recently. The Intranet site is older and we're
>>> having problems from the get go. The first thing I've hit a problem
>>> with, I cannot seem to track down the cause. I have the following
>>> code:
>>>
>>> Set cnn=Server.CreateObject("ADODB.Connection")
>>> cnn.Open "EXECBOB","SA",""
>>
>> 1. Don't use your sa account for your applications. Create a
>> limited-permission account to use instead. Using sa can lead to bad
>> habits. sa should only be used for server administration. Nothing
>> else. The potential for misuse in the wrong hands is huge.
>> 2. A blank password for the sa account?!? Have you never heard of
>> Code Red??? Hopefully you've just censored the password so you could
>> post your connection's Open statement.
>> 3. Avoid using ODBC: http://www.aspfaq.com/show.asp?id=2126
>>
>>> Set cmd = Server.CreateObject("ADODB.Command")
>>
>> It's not fatal, but I don't see why you are using an explicit Command
>> object.
>>
>>> cmd.ActiveConnection = cnn
>>> cmd.CommandType = 1
>>> Set rst = Server.CreateObject("ADODB.Recordset")
>>> cmd.CommandText = "SELECT * FROM Sendout_Records ORDER BY
>>
>> Don't use selstar: http://www.aspfaq.com/show.asp?id=2096
>>
>>> Arranged_Date DESC"
>>> rst.Open cmd, , 3, 1
>>
>> Again, nothing fatal, but I don't see why you are using anything but
>> the default forward-only cursor. This code could be simplified to:
>>
>> sSQL="SELECT FROM Sendout_Records " & _
>> "ORDER BY Arranged_Date DESC"
>> set rst=cnn.execute(sSQL,,1)
>>
>>> companyID=rst(6)
>>> apptDate=rst(1)
>>> latestJO="Congratulations to "&recruiterName(rst(11))&" for posting
>>> the latest send out: "&PCase(rst(10))&" with "
>>> rst.Close
>>>
>>> That recordset is coming back empty, however, if I run the
>>> commandText in the MSSQL ent mgr directlry, brings up many records.
>>
>> Use SQL Profiler to verify that the commad text you expect to be
>> executed is actually what is being exxecuted.
>>
>> Bob Barrows
>>
>> --
>> Microsoft MVP -- ASP/ASP.NET
>> Please reply to the newsgroup. The email account listed in my From
>> header is my spam trap, so I don't check it very often. You will get
>> a quicker response by posting to the newsgroup.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Migrating ASP/MSSQL app from IIS 4 to IIS 6

By default, ADO use OLEDB, not ODBC; so writing something like:

cnn.Open "EXECBOB", "SA"

doesn't make sense for ADO as it takes "EXECBOB" as being the name of a
network machine, not a DSN.

Take a look at the following references for proper connection string for
ADO:

http://www.connectionstrings.com/

http://www.carlprothman.net/Default.aspx?tabid=81

http://msdn.microsoft.com/library/default.asp?url=/library/e n-us/ado270/htm/mdrefodbcprovspec.asp

Use the Microsoft OLE DB Provider for ODBC (second and third references) if
you want to use a DSN with ADO (however, this is an old and not recommended
configuration for today times).

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


"WebTent" wrote in message
news:7CD7DA53-6873-4D62-A12D-F839AAD4D255@microsoft.com...
> We have moved an Internet and Intranet site over to our IIS 6 MS Small
> Business Server 2003. The Internet site works fine, but was done by a
> company
> recently. The Intranet site is older and we're having problems from the
> get
> go. The first thing I've hit a problem with, I cannot seem to track down
> the
> cause. I have the following code:
>
> Set cnn=Server.CreateObject("ADODB.Connection")
> cnn.Open "EXECBOB","SA",""
> Set cmd = Server.CreateObject("ADODB.Command")
> cmd.ActiveConnection = cnn
> cmd.CommandType = 1
> Set rst = Server.CreateObject("ADODB.Recordset")
> cmd.CommandText = "SELECT * FROM Sendout_Records ORDER BY Arranged_Date
> DESC"
> rst.Open cmd, , 3, 1
> companyID=rst(6)
> apptDate=rst(1)
> latestJO="Congratulations to "&recruiterName(rst(11))&" for posting the
> latest send out: "&PCase(rst(10))&" with "
> rst.Close
>
> That recordset is coming back empty, however, if I run the commandText in
> the MSSQL ent mgr directlry, brings up many records. The 'EXECBOB' is the
> ODBC DSN connection and I've tested to be sure it is working and pointing
> to
> the proper new MSSQL 2000 server. So, I looked at the code for accessing
> data
> in the Internet site and it is different:
>
> Set ConnSQL = Server.CreateObject("ADODB.Connection")
> ConnSQL.ConnectionTimeout = 30
> ConnSQL.ConnectionString = "driver={SQL
> Server};server=mrisbs2k3;database=execbob;Uid=sa;Pwd="
> ConnSQL.Open
> strSQL = "SELECT * FROM Sendout_Records ORDER BY Arranged_Date DESC"
> Set rst = CreateObject("ADODB.Recordset")
> Set rst = ConnSQL.Execute(strSQL)
> companyID=rst(7)
> apptDate=rst(1)
> response.write rst(2) & "-" & rst(7)
> latestJO="Congratulations to "&recruiterName(rst(11))&" for posting the
> latest send out: "&PCase(rst(10))&" with "
> rst.Close
>
> This populates the the recordset fine, does this old code not work?
>
> Thanks for any help....Robert
>

Re: Migrating ASP/MSSQL app from IIS 4 to IIS 6

Sylvain Lafontaine wrote:
> By default, ADO use OLEDB, not ODBC; so writing something like:
>
> cnn.Open "EXECBOB", "SA"
>
> doesn't make sense for ADO as it takes "EXECBOB" as being the name of
> a network machine, not a DSN.

No, it defaults to the MSDASQL provider, which is the provider for odbc. So,
if "EXECBOB" is the name of a system DSN, this statement will work.
Of course, I'm not saying it's a good idea to use this technique: depending
on defaults is a good way to encounter hard-to-debug problems.


> http://msdn.microsoft.com/library/default.asp?url=/library/e n-us/ado270/htm/mdrefodbcprovspec.asp

Which says:
"This is the default provider for ADO"

> Use the Microsoft OLE DB Provider for ODBC (second and third
> references) if you want to use a DSN with ADO (however, this is an old and
> not
> recommended configuration for today times).
>

Agreed.

Bob Barrows
--
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"

Re: Migrating ASP/MSSQL app from IIS 4 to IIS 6

Thanks for the correction; it's has been so long now since the last time
that I've used an ODBC connection with ADO that I had completely forgot
about this default configuration.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


"Bob Barrows [MVP]" wrote in message
news:uPlVldr5FHA.1184@TK2MSFTNGP12.phx.gbl...
> Sylvain Lafontaine wrote:
>> By default, ADO use OLEDB, not ODBC; so writing something like:
>>
>> cnn.Open "EXECBOB", "SA"
>>
>> doesn't make sense for ADO as it takes "EXECBOB" as being the name of
>> a network machine, not a DSN.
>
> No, it defaults to the MSDASQL provider, which is the provider for odbc.
> So, if "EXECBOB" is the name of a system DSN, this statement will work.
> Of course, I'm not saying it's a good idea to use this technique:
> depending on defaults is a good way to encounter hard-to-debug problems.
>
>
>> http://msdn.microsoft.com/library/default.asp?url=/library/e n-us/ado270/htm/mdrefodbcprovspec.asp
>
> Which says:
> "This is the default provider for ADO"
>
>> Use the Microsoft OLE DB Provider for ODBC (second and third
>> references) if you want to use a DSN with ADO (however, this is an old
>> and not
>> recommended configuration for today times).
>>
>
> Agreed.
>
> Bob Barrows
> --
> 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"
>