asp questions before i begin project

hi everyone. building a website for a client and have a few questions
related to best practices, what i should use etc...

the site will use a database to store info entered by users. each visit will
allow the user to input no more than 2 fields: email address, and a comment
field. the site has the potential to become pretty busy so my first question
is, what should i use on the backend? a dedicated SQL server isnt an option
so im looking at either an Access database, or the desktop edition of SQL
installed on the web server.

second question (and this might help decide which DB to use) is: what are
the proper ways to open the databse so that multiple writes can be done at
the same time in the case where more than 1 user are entering info at the
same time? im referring to LockType, CursorType etc... (im not entirely sure
of the workings of these things so if someone could give me a quick opinion,
and point me in the right direction i can take it from there)

thank you in advance

Mike
Thu Sep 07 11:37:49 CDT 2006


Jimmy wrote:
> hi everyone. building a website for a client and have a few questions
> related to best practices, what i should use etc...
>
> the site will use a database to store info entered by users. each visit will
> allow the user to input no more than 2 fields: email address, and a comment
> field. the site has the potential to become pretty busy so my first question
> is, what should i use on the backend? a dedicated SQL server isnt an option
> so im looking at either an Access database, or the desktop edition of SQL
> installed on the web server.
>
> second question (and this might help decide which DB to use) is: what are
> the proper ways to open the databse so that multiple writes can be done at
> the same time in the case where more than 1 user are entering info at the
> same time? im referring to LockType, CursorType etc... (im not entirely sure
> of the workings of these things so if someone could give me a quick opinion,
> and point me in the right direction i can take it from there)
>
> thank you in advance

SQL Server 2005 Express Edition running on the web server is preferable
to Access - especially if there are a lot of Write operations.

The best way to wirte to the database, whether Access or SQL Server is
to create a stored procedure (saved query in Access). Take the input
from the submitted form, validate it and then pass the values as
parameters to the proc.

Pseudo code:

==Stored Proc==

Create Procedure procInsertComments
@EmailAddress nvarchar(50),
@Comments ntext
AS
BEGIN
SET NOCOUNT ON
INSERT INTO table (EmailAddress,Comments) VALUES
@EmailAddress,
@Comments
GO

Mike
Thu Sep 07 11:42:59 CDT 2006


Mike Brind wrote:
> Jimmy wrote:
> > hi everyone. building a website for a client and have a few questions
> > related to best practices, what i should use etc...
> >
> > the site will use a database to store info entered by users. each visit will
> > allow the user to input no more than 2 fields: email address, and a comment
> > field. the site has the potential to become pretty busy so my first question
> > is, what should i use on the backend? a dedicated SQL server isnt an option
> > so im looking at either an Access database, or the desktop edition of SQL
> > installed on the web server.
> >
> > second question (and this might help decide which DB to use) is: what are
> > the proper ways to open the databse so that multiple writes can be done at
> > the same time in the case where more than 1 user are entering info at the
> > same time? im referring to LockType, CursorType etc... (im not entirely sure
> > of the workings of these things so if someone could give me a quick opinion,
> > and point me in the right direction i can take it from there)
> >
> > thank you in advance
>
> SQL Server 2005 Express Edition running on the web server is preferable
> to Access - especially if there are a lot of Write operations.
>
> The best way to wirte to the database, whether Access or SQL Server is
> to create a stored procedure (saved query in Access). Take the input
> from the submitted form, validate it and then pass the values as
> parameters to the proc.
>
> Pseudo code:
>
> ==Stored Proc==
>
> Create Procedure procInsertComments
> @EmailAddress nvarchar(50),
> @Comments ntext
> AS
> BEGIN
> SET NOCOUNT ON
> INSERT INTO table (EmailAddress,Comments) VALUES
> @EmailAddress,
> @Comments
> GO

Damn - clicked wrong button.... Disregard erroneous code above

Pseudo code:

==Stored Proc==
Create Procedure procInsertComments
@EmailAddress nvarchar(50),
@Comments ntext
AS
BEGIN
SET NOCOUNT ON
INSERT INTO table (EmailAddress,Comments) VALUES
@EmailAddress,
@Comments
END
GO

Then, when you have validated the form values, open the databse
connection, perform the insert and close immediately eg:

EmailAddress = validated form value
Comments = validated form value

strCon = connection string
con.open strCon
con.procInsertComments EmailAddress, Comments
con.Close : Set con = Nothing

--
Mike Brind

Bob
Thu Sep 07 12:06:46 CDT 2006

Mike Brind wrote:

> INSERT INTO table (EmailAddress,Comments) VALUES
> @EmailAddress,
> @Comments

I know this is air code, but the list of values should be parenthesized
INSERT INTO table (EmailAddress,Comments) VALUES (
@EmailAddress,
@Comments)

--
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.

Jimmy
Thu Sep 07 13:35:38 CDT 2006

perfect, thank you.

could you help me out with the best way to actually connect to the sql
database?
in other words, what would the code look like in the connection string?

would it have to be a DSN? or can SQL take dsn-less connections?
(ive heard dsn-less is actually better because the server doesnt have to dig
through the registry to resolve the dsn)




"Mike Brind" <paxtonend@hotmail.com> wrote in message
news:1157647378.941962.239630@m79g2000cwm.googlegroups.com...
>
> Mike Brind wrote:
>> Jimmy wrote:
>> > hi everyone. building a website for a client and have a few questions
>> > related to best practices, what i should use etc...
>> >
>> > the site will use a database to store info entered by users. each visit
>> > will
>> > allow the user to input no more than 2 fields: email address, and a
>> > comment
>> > field. the site has the potential to become pretty busy so my first
>> > question
>> > is, what should i use on the backend? a dedicated SQL server isnt an
>> > option
>> > so im looking at either an Access database, or the desktop edition of
>> > SQL
>> > installed on the web server.
>> >
>> > second question (and this might help decide which DB to use) is: what
>> > are
>> > the proper ways to open the databse so that multiple writes can be done
>> > at
>> > the same time in the case where more than 1 user are entering info at
>> > the
>> > same time? im referring to LockType, CursorType etc... (im not entirely
>> > sure
>> > of the workings of these things so if someone could give me a quick
>> > opinion,
>> > and point me in the right direction i can take it from there)
>> >
>> > thank you in advance
>>
>> SQL Server 2005 Express Edition running on the web server is preferable
>> to Access - especially if there are a lot of Write operations.
>>
>> The best way to wirte to the database, whether Access or SQL Server is
>> to create a stored procedure (saved query in Access). Take the input
>> from the submitted form, validate it and then pass the values as
>> parameters to the proc.
>>
>> Pseudo code:
>>
>> ==Stored Proc==
>>
>> Create Procedure procInsertComments
>> @EmailAddress nvarchar(50),
>> @Comments ntext
>> AS
>> BEGIN
>> SET NOCOUNT ON
>> INSERT INTO table (EmailAddress,Comments) VALUES
>> @EmailAddress,
>> @Comments
>> GO
>
> Damn - clicked wrong button.... Disregard erroneous code above
>
> Pseudo code:
>
> ==Stored Proc==
> Create Procedure procInsertComments
> @EmailAddress nvarchar(50),
> @Comments ntext
> AS
> BEGIN
> SET NOCOUNT ON
> INSERT INTO table (EmailAddress,Comments) VALUES
> @EmailAddress,
> @Comments
> END
> GO
>
> Then, when you have validated the form values, open the databse
> connection, perform the insert and close immediately eg:
>
> EmailAddress = validated form value
> Comments = validated form value
>
> strCon = connection string
> con.open strCon
> con.procInsertComments EmailAddress, Comments
> con.Close : Set con = Nothing
>
> --
> Mike Brind
>

Aaron
Thu Sep 07 13:42:17 CDT 2006

> in other words, what would the code look like in the connection string?
>
> would it have to be a DSN? or can SQL take dsn-less connections?

Yes, the latter is preferred.

http://databases.aspfaq.com/database/what-should-my-connection-string-look-like.html

Bob
Thu Sep 07 13:43:08 CDT 2006

Jimmy wrote:
> perfect, thank you.
>
> could you help me out with the best way to actually connect to the sql
> database?
> in other words, what would the code look like in the connection
> string?

http://www.aspfaq.com/show.asp?id=2126

--
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.

Mike
Thu Sep 07 13:59:02 CDT 2006


Bob Barrows [MVP] wrote:
> Mike Brind wrote:
>
> > INSERT INTO table (EmailAddress,Comments) VALUES
> > @EmailAddress,
> > @Comments
>
> I know this is air code, but the list of values should be parenthesized
> INSERT INTO table (EmailAddress,Comments) VALUES (
> @EmailAddress,
> @Comments)
>

Thanks Bob.

--
Mike Brind

Aaron
Thu Sep 07 15:08:56 CDT 2006

http://databases.aspfaq.com/database/using-stored-procedures.html




"Jimmy" <j@j.j> wrote in message
news:uoeIHQr0GHA.1588@TK2MSFTNGP02.phx.gbl...
> thank you both....
>
> ok, last one for now....
>
> ive never used stored procedures. does anyone have simple example or a
> good site?
>
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in
> message news:eSaL80q0GHA.1256@TK2MSFTNGP02.phx.gbl...
>>> in other words, what would the code look like in the connection string?
>>>
>>> would it have to be a DSN? or can SQL take dsn-less connections?
>>
>> Yes, the latter is preferred.
>>
>> http://databases.aspfaq.com/database/what-should-my-connection-string-look-like.html
>>
>
>

Jimmy
Thu Sep 07 14:30:12 CDT 2006

thank you both....

ok, last one for now....

ive never used stored procedures. does anyone have simple example or a good
site?


"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:eSaL80q0GHA.1256@TK2MSFTNGP02.phx.gbl...
>> in other words, what would the code look like in the connection string?
>>
>> would it have to be a DSN? or can SQL take dsn-less connections?
>
> Yes, the latter is preferred.
>
> http://databases.aspfaq.com/database/what-should-my-connection-string-look-like.html
>

Jimmy
Thu Sep 07 15:16:00 CDT 2006

thanks. not off to a good start just trying to get SQL up and running.

have it installed, created a single databse with a single table with a
single entry.
getting error:

"Microsoft OLE DB Provider for SQL Server (0x80004005)
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access
denied."

code is like this:

Dim strConn, strSQL, objCon, objRS

strConn = "Provider=SQLOLEDB;" & _
"Data Source=10.10.131.193;" & _
"Initial Catalog=testdb;" & _
"Network=DBMSSOCN;" & _
"User Id=sa;" & _
"Password=password"

Set objCon = CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")

objCon.Open strConn

strSQL = "SELECT * FROM TABLE1"
objRS.Open strSQL, objCon

Response.Write objRS("NAME")

objRS.Close
Set objRS = Nothing
objCon.Close
Set objCon = Nothing



any ideas?



"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:evILQlr0GHA.1588@TK2MSFTNGP02.phx.gbl...
> http://databases.aspfaq.com/database/using-stored-procedures.html
>
>
>
>
> "Jimmy" <j@j.j> wrote in message
> news:uoeIHQr0GHA.1588@TK2MSFTNGP02.phx.gbl...
>> thank you both....
>>
>> ok, last one for now....
>>
>> ive never used stored procedures. does anyone have simple example or a
>> good site?
>>
>>
>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in
>> message news:eSaL80q0GHA.1256@TK2MSFTNGP02.phx.gbl...
>>>> in other words, what would the code look like in the connection string?
>>>>
>>>> would it have to be a DSN? or can SQL take dsn-less connections?
>>>
>>> Yes, the latter is preferred.
>>>
>>> http://databases.aspfaq.com/database/what-should-my-connection-string-look-like.html
>>>
>>
>>
>
>

Bob
Thu Sep 07 15:23:11 CDT 2006

Jimmy wrote:
> thanks. not off to a good start just trying to get SQL up and running.
>
> have it installed, created a single databse with a single table with a
> single entry.
> getting error:
>
> "Microsoft OLE DB Provider for SQL Server (0x80004005)
> [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
> access denied."
>

Is it SQL 2005? Have you enabled TCP/IP? If you are using SQL 2005
Express, I'm not sure how to do it so you'll have to post to a SQL
Server group.
--
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.

Jimmy
Thu Sep 07 15:28:21 CDT 2006

ive enabled it everywhere i can see that it has to be enabled....

far as you can tell the code is ok though? this way i know where to focus



"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:Odnxxtr0GHA.1588@TK2MSFTNGP02.phx.gbl...
> Jimmy wrote:
>> thanks. not off to a good start just trying to get SQL up and running.
>>
>> have it installed, created a single databse with a single table with a
>> single entry.
>> getting error:
>>
>> "Microsoft OLE DB Provider for SQL Server (0x80004005)
>> [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
>> access denied."
>>
>
> Is it SQL 2005? Have you enabled TCP/IP? If you are using SQL 2005
> Express, I'm not sure how to do it so you'll have to post to a SQL
> Server group.
> --
> 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.
>
>

Mike
Thu Sep 07 15:48:26 CDT 2006

This is the connection string I use for SQL Server 2005 Express:

"Provider=SQLOLEDB;Data Source=.\SQLEXPRESS;Initial
Catalog=testdb;Integrated Security=SSPI"

Or, if you want to use the SQL Native Client (apparently offers better
performance):

"Provider=SQLNCLI;Server=.\SQLEXPRESS;Database=testdb;Trusted_Connection=yes;"

Create a user with INSERT, DELETE, UPDATE, READ and CONNECT permissions
on the db. Then run the script and see if it works.

--
Mike Brind


Jimmy wrote:
> thanks. not off to a good start just trying to get SQL up and running.
>
> have it installed, created a single databse with a single table with a
> single entry.
> getting error:
>
> "Microsoft OLE DB Provider for SQL Server (0x80004005)
> [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access
> denied."
>
> code is like this:
>
> Dim strConn, strSQL, objCon, objRS
>
> strConn = "Provider=SQLOLEDB;" & _
> "Data Source=10.10.131.193;" & _
> "Initial Catalog=testdb;" & _
> "Network=DBMSSOCN;" & _
> "User Id=sa;" & _
> "Password=password"
>
> Set objCon = CreateObject("ADODB.Connection")
> Set objRS = Server.CreateObject("ADODB.Recordset")
>
> objCon.Open strConn
>
> strSQL = "SELECT * FROM TABLE1"
> objRS.Open strSQL, objCon
>
> Response.Write objRS("NAME")
>
> objRS.Close
> Set objRS = Nothing
> objCon.Close
> Set objCon = Nothing
>
>
>
> any ideas?
>
>
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
> news:evILQlr0GHA.1588@TK2MSFTNGP02.phx.gbl...
> > http://databases.aspfaq.com/database/using-stored-procedures.html
> >
> >
> >
> >
> > "Jimmy" <j@j.j> wrote in message
> > news:uoeIHQr0GHA.1588@TK2MSFTNGP02.phx.gbl...
> >> thank you both....
> >>
> >> ok, last one for now....
> >>
> >> ive never used stored procedures. does anyone have simple example or a
> >> good site?
> >>
> >>
> >> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in
> >> message news:eSaL80q0GHA.1256@TK2MSFTNGP02.phx.gbl...
> >>>> in other words, what would the code look like in the connection string?
> >>>>
> >>>> would it have to be a DSN? or can SQL take dsn-less connections?
> >>>
> >>> Yes, the latter is preferred.
> >>>
> >>> http://databases.aspfaq.com/database/what-should-my-connection-string-look-like.html
> >>>
> >>
> >>
> >
> >

Bob
Thu Sep 07 15:47:16 CDT 2006

Jimmy wrote:
> ive enabled it everywhere i can see that it has to be enabled....
>

:-) That tells us nothing.

> far as you can tell the code is ok though?

Yes, that's why I didn't mention anything about the code* and focussed
on the database server configuration. In SQL 2005, the Network Libraries
(TCP/IP, named pipes) have to be explicitly enabled. Again, if you are
using Express, I don't know how to enable TCP/IP so you need to post to
a SQL Server group, unless someone who's used it jumps in here ...


* I should have mentioned this but it has nothing to do with your
problem: don't use the sa account in your applications. Create a SQL
Login with limited privileges and use that in your applications. If a
hacker gets in as sa, he can wreak havoc, not only to your database, but
to your machine and network if you have not locked it down.
--
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.

Bob
Thu Sep 07 16:03:52 CDT 2006

Mike Brind wrote:
> This is the connection string I use for SQL Server 2005 Express:

Have we seen confirmation that he is using Express?
>
> "Provider=SQLOLEDB;Data Source=.\SQLEXPRESS;Initial
> Catalog=testdb;Integrated Security=SSPI"
>
> Or, if you want to use the SQL Native Client (apparently offers better
> performance):

I haven't seen anything touting better performance. I've seen
recommendations against using it if not using SQL 2005 features such as
MARS.


--
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.

Mike
Thu Sep 07 16:32:50 CDT 2006


Bob Barrows [MVP] wrote:
> Mike Brind wrote:
> > This is the connection string I use for SQL Server 2005 Express:
>
> Have we seen confirmation that he is using Express?

No, but since in his OP he said that using the full version is not an
option, and then mentioned the "desktop" version, I think it's a fair
assumption that he is unlikely to have a full blown copy of SQL Server
2005 lying around to play with and has installed Express. He could, of
course, have downloaded the 180-day trial of the full version and be
using that, but why he would do so is beyond me. Nevertheless, you
have a point.

> >
> > "Provider=SQLOLEDB;Data Source=.\SQLEXPRESS;Initial
> > Catalog=testdb;Integrated Security=SSPI"
> >
> > Or, if you want to use the SQL Native Client (apparently offers better
> > performance):
>
> I haven't seen anything touting better performance. I've seen
> recommendations against using it if not using SQL 2005 features such as
> MARS.
>

I haven't seen these recommendations. Time for me to google, unless
you have any relevant bookmarks handy.... :-)

--
Mike Brind

Jimmy
Fri Sep 08 07:17:09 CDT 2006

thanks everyone. it is indeed 2005 express.
heres what i have learned... so by default sql is lietening on port 1433 i
believe?
FROM the server where sql express is running i can telnet to port 1433. but
i can NOT even telnet to this port from any other machine on the network,
including the webserver which is why my ASP page cant connect.
is there some setting somewhere in sql that prevents network connections by
default?

if no one knows ill go to a sql group i guess




"Mike Brind" <paxtonend@hotmail.com> wrote in message
news:1157664769.960312.48690@i42g2000cwa.googlegroups.com...
>
> Bob Barrows [MVP] wrote:
>> Mike Brind wrote:
>> > This is the connection string I use for SQL Server 2005 Express:
>>
>> Have we seen confirmation that he is using Express?
>
> No, but since in his OP he said that using the full version is not an
> option, and then mentioned the "desktop" version, I think it's a fair
> assumption that he is unlikely to have a full blown copy of SQL Server
> 2005 lying around to play with and has installed Express. He could, of
> course, have downloaded the 180-day trial of the full version and be
> using that, but why he would do so is beyond me. Nevertheless, you
> have a point.
>
>> >
>> > "Provider=SQLOLEDB;Data Source=.\SQLEXPRESS;Initial
>> > Catalog=testdb;Integrated Security=SSPI"
>> >
>> > Or, if you want to use the SQL Native Client (apparently offers better
>> > performance):
>>
>> I haven't seen anything touting better performance. I've seen
>> recommendations against using it if not using SQL 2005 features such as
>> MARS.
>>
>
> I haven't seen these recommendations. Time for me to google, unless
> you have any relevant bookmarks handy.... :-)
>
> --
> Mike Brind
>

Bob
Fri Sep 08 07:29:39 CDT 2006

Jimmy wrote:
> thanks everyone. it is indeed 2005 express.
> heres what i have learned... so by default sql is lietening on port
> 1433 i believe?

No. From what I've read, this is not turned on by default. Again, I
don't know how to turn it on in Express (I know how to turn it on using
SMSS in SQL 2005 Enterprise, but that's not relevant to Express).


--
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.

Bob
Fri Sep 08 08:33:45 CDT 2006

Does this help? it shows how to turn on TCP/IP:
http://www.aspfaq.com/sql2005/show.asp?id=3


Jimmy wrote:
> thanks everyone. it is indeed 2005 express.
> heres what i have learned... so by default sql is lietening on port
> 1433 i believe?
> FROM the server where sql express is running i can telnet to port
> 1433. but i can NOT even telnet to this port from any other machine
> on the network, including the webserver which is why my ASP page cant
> connect.
> is there some setting somewhere in sql that prevents network
> connections by default?
>
> if no one knows ill go to a sql group i guess
>
>
>
>
> "Mike Brind" <paxtonend@hotmail.com> wrote in message
> news:1157664769.960312.48690@i42g2000cwa.googlegroups.com...
>>
>> Bob Barrows [MVP] wrote:
>>> Mike Brind wrote:
>>>> This is the connection string I use for SQL Server 2005 Express:
>>>
>>> Have we seen confirmation that he is using Express?
>>
>> No, but since in his OP he said that using the full version is not an
>> option, and then mentioned the "desktop" version, I think it's a fair
>> assumption that he is unlikely to have a full blown copy of SQL
>> Server 2005 lying around to play with and has installed Express. He
>> could, of course, have downloaded the 180-day trial of the full
>> version and be using that, but why he would do so is beyond me.
>> Nevertheless, you have a point.
>>
>>>>
>>>> "Provider=SQLOLEDB;Data Source=.\SQLEXPRESS;Initial
>>>> Catalog=testdb;Integrated Security=SSPI"
>>>>
>>>> Or, if you want to use the SQL Native Client (apparently offers
>>>> better performance):
>>>
>>> I haven't seen anything touting better performance. I've seen
>>> recommendations against using it if not using SQL 2005 features
>>> such as MARS.
>>>
>>
>> I haven't seen these recommendations. Time for me to google, unless
>> you have any relevant bookmarks handy.... :-)
>>
>> --
>> Mike Brind

--
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.

Jimmy
Fri Sep 08 08:42:31 CDT 2006

i got it working guys, thank you.

the problem was a combination of the TCP/IP settings in SQL (dynamic ports
were a problem) and my connection string (i had to specify the instance
name!)



"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:exf1pt00GHA.4772@TK2MSFTNGP03.phx.gbl...
> Does this help? it shows how to turn on TCP/IP:
> http://www.aspfaq.com/sql2005/show.asp?id=3
>
>
> Jimmy wrote:
>> thanks everyone. it is indeed 2005 express.
>> heres what i have learned... so by default sql is lietening on port
>> 1433 i believe?
>> FROM the server where sql express is running i can telnet to port
>> 1433. but i can NOT even telnet to this port from any other machine
>> on the network, including the webserver which is why my ASP page cant
>> connect.
>> is there some setting somewhere in sql that prevents network
>> connections by default?
>>
>> if no one knows ill go to a sql group i guess
>>
>>
>>
>>
>> "Mike Brind" <paxtonend@hotmail.com> wrote in message
>> news:1157664769.960312.48690@i42g2000cwa.googlegroups.com...
>>>
>>> Bob Barrows [MVP] wrote:
>>>> Mike Brind wrote:
>>>>> This is the connection string I use for SQL Server 2005 Express:
>>>>
>>>> Have we seen confirmation that he is using Express?
>>>
>>> No, but since in his OP he said that using the full version is not an
>>> option, and then mentioned the "desktop" version, I think it's a fair
>>> assumption that he is unlikely to have a full blown copy of SQL
>>> Server 2005 lying around to play with and has installed Express. He
>>> could, of course, have downloaded the 180-day trial of the full
>>> version and be using that, but why he would do so is beyond me.
>>> Nevertheless, you have a point.
>>>
>>>>>
>>>>> "Provider=SQLOLEDB;Data Source=.\SQLEXPRESS;Initial
>>>>> Catalog=testdb;Integrated Security=SSPI"
>>>>>
>>>>> Or, if you want to use the SQL Native Client (apparently offers
>>>>> better performance):
>>>>
>>>> I haven't seen anything touting better performance. I've seen
>>>> recommendations against using it if not using SQL 2005 features
>>>> such as MARS.
>>>>
>>>
>>> I haven't seen these recommendations. Time for me to google, unless
>>> you have any relevant bookmarks handy.... :-)
>>>
>>> --
>>> Mike Brind
>
> --
> 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.
>
>

Bob
Fri Sep 08 08:47:33 CDT 2006

Jimmy wrote:
> i got it working guys, thank you.
>
> the problem was a combination of the TCP/IP settings in SQL (dynamic
> ports were a problem)

Could we trouble you for a few details for future reference?


--
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.

Dave
Fri Sep 08 08:54:59 CDT 2006

Jimmy wrote:
> "Microsoft OLE DB Provider for SQL Server (0x80004005)
> [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
> access denied."

DBNETLIB ? Isn't that named pipes? Add this parameter to your connection
string to specify TCP/IP:

Network Library=DBMSSOCN


I always use data link files to build my connection strings. It is a very
reliable way to build them:

Create an empty text file
Rename it with the .udl extension
Double-click to open as a data link
Click on the provider tab
Select desired provider and press [Next]
Configure and test your connection
Check "Allow saving password"

Once you have a working data link file, close it (save when prompted), and
open in a text editor. It will contain your connection string.



--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms.

Bob
Fri Sep 08 09:13:00 CDT 2006

Mike Brind wrote:
>>> Or, if you want to use the SQL Native Client (apparently offers
>>> better performance):
>>
>> I haven't seen anything touting better performance. I've seen
>> recommendations against using it if not using SQL 2005 features such
>> as MARS.
>>
>
> I haven't seen these recom