MSDE and IIS - Web Hosting

In trying to get around the current Jet hang problem people are seeing under
Windows 2003 and the jet oledb provider, we have ended up having to convert
many existing MS Access web site projects to MSDE.
The clients don't have the budgets to license full sql server, and don't
need the full performance you see there versus the "limiters" put into MSDE,
so we have gone this route until the jet hang problem is resolved.
My question is: what are the memory and/or performance issues when MSDE is
installed on a web server providing db services to multiple sites? I mean,
is it just one instance of MSDE running and providing all db services, or is
it doing anything less efficient other than the limiters it has in place?
How many instances of sqlservr.exe processes should I see - just one right?
What mem usage should it be using? Is the usage based on the number of
databases and activity, or will it try to grab a certain percentage of all
memory available?
Any new info on the infamous jet oledb hang problem? I opened (and paid) a
ticket with MS and they sent something to test but it didn't work and I
haven't heard back since - that was months ago.
Thanks,
Kevin
hi Kevin,
"valkev" <nospam@.4me.com> ha scritto nel messaggio
news:0bCDc.9073$Ey3.2483@.newssvr24.news.prodigy.co m...
> ...
> My question is: what are the memory and/or performance issues when MSDE is
> installed on a web server providing db services to multiple sites? I
mean,
> is it just one instance of MSDE running and providing all db services, or
is
> it doing anything less efficient other than the limiters it has in place?
>
running a database server on the same machine of a web server always hits
some penalty...
the Workload Query Governor will hit when more then 8 concurrent batches are
run on the very same time, slowing down all active workloads, reverting to
normal behaviour when the concurrent batches gets again under 8 batches, but
this is not regarding memory usage...
SQL Server/MSDE is very hungry, regarding memory...
memory usage is database and application dependent, but usage and workloads
dependent too...
as you know, in few words, SQL Server organize it's memory allocation in two
distinct regions, the "buffer pool" (BPool) and the "memory to leave"
(MemToLeave) regions. (I'm excluding use of AWE in order to provide easy
understanding)
so... the BPool is the primary region SQL Server uses for it's internal
matter, while MemToLeave consists of the virtual memory space within the 1gb
user mode address space and the memory not used by the BPool.
when SQL Server starts, it begins calculating the upper limit the BPool can
reach... if no MaxMemory is set, this value will be set to the amount of the
physical memory or the size of the user mode address space (1gb) , minus the
size of the MemToLeave, whichever is less..
by default, MemToLeave is set to 384mb, 128mb of them are for worker thread
stacks and 256mb for allocation outside the BPool, such as memory for OLE-DB
providers, in process COM objects space and memory requirements and so on..
when MaxMemory value is explicitally set, this upper limit will only address
BPool region needs...
so only the address space of BPool pages is limited by this configuration
value, while SQL Server memory requirememts outside BPool allocation are not
limited this way...
but you are not limiting the resource, so SQL Server can reclaim that memory
(up to 1 gb, in our example) for it's uses, and it will perhaps release it
only under pressure by the OS claiming for additional memory, if it's the
case, else it will maintain that memory in order to cache pages and
execution plans...
under OS pressure, SQL Server will try having the Lazy Writer releases
resources, but you are not granted that this will occur, even if SQL Server
will always try to leave some memory for other OS needs, usually between 4
and 10mb of RAM... so, on SQL Server dedicated servers, you usually see
memory usage climbing to the top and stayng there for long time, becouse of
SQL Server tends to keep pages read from disk in memory to increase further
and successive access to those pages, as long as query plans and so on... on
a heavy loaded server, if you add web server duty, you will have resource
contentions for sure...

> How many instances of sqlservr.exe processes should I see - just one
right?
as many as you installed and started...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:2k8s2kF17hgbiU1@.uni-berlin.de...[vbcol=seagreen]
> hi Kevin,
> "valkev" <nospam@.4me.com> ha scritto nel messaggio
> news:0bCDc.9073$Ey3.2483@.newssvr24.news.prodigy.co m...
is[vbcol=seagreen]
> mean,
or[vbcol=seagreen]
> is
place?
> running a database server on the same machine of a web server always hits
> some penalty...
> the Workload Query Governor will hit when more then 8 concurrent batches
are
> run on the very same time, slowing down all active workloads, reverting to
> normal behaviour when the concurrent batches gets again under 8 batches,
but
> this is not regarding memory usage...
> SQL Server/MSDE is very hungry, regarding memory...
> memory usage is database and application dependent, but usage and
workloads
> dependent too...
> as you know, in few words, SQL Server organize it's memory allocation in
two
> distinct regions, the "buffer pool" (BPool) and the "memory to leave"
> (MemToLeave) regions. (I'm excluding use of AWE in order to provide easy
> understanding)
> so... the BPool is the primary region SQL Server uses for it's internal
> matter, while MemToLeave consists of the virtual memory space within the
1gb
> user mode address space and the memory not used by the BPool.
> when SQL Server starts, it begins calculating the upper limit the BPool
can
> reach... if no MaxMemory is set, this value will be set to the amount of
the
> physical memory or the size of the user mode address space (1gb) , minus
the
> size of the MemToLeave, whichever is less..
> by default, MemToLeave is set to 384mb, 128mb of them are for worker
thread
> stacks and 256mb for allocation outside the BPool, such as memory for
OLE-DB
> providers, in process COM objects space and memory requirements and so
on..
> when MaxMemory value is explicitally set, this upper limit will only
address
> BPool region needs...
> so only the address space of BPool pages is limited by this configuration
> value, while SQL Server memory requirememts outside BPool allocation are
not
> limited this way...
> but you are not limiting the resource, so SQL Server can reclaim that
memory
> (up to 1 gb, in our example) for it's uses, and it will perhaps release it
> only under pressure by the OS claiming for additional memory, if it's the
> case, else it will maintain that memory in order to cache pages and
> execution plans...
> under OS pressure, SQL Server will try having the Lazy Writer releases
> resources, but you are not granted that this will occur, even if SQL
Server
> will always try to leave some memory for other OS needs, usually between 4
> and 10mb of RAM... so, on SQL Server dedicated servers, you usually see
> memory usage climbing to the top and stayng there for long time, becouse
of
> SQL Server tends to keep pages read from disk in memory to increase
further
> and successive access to those pages, as long as query plans and so on...
on
> a heavy loaded server, if you add web server duty, you will have resource
> contentions for sure...
>
> right?
> as many as you installed and started...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
Andrea,
Thanks for the great info. Your explanation is consistent with what I'm
seeing.
So, the only real performance difference seen going from MSDE to SQL Server
is that the Workload Query Governor will not be kicking in and slowing
things down on active installations correct? Isn't this governor on the
order of milliseconds?
Thanks,
Kevin
|||Hi Kevin,
The Workload Governor on MSDE is somewhat more serious than a matter of
milliseconds. Basically it is supposed to give MSDE approximately the same
performance characteristics as Access, i.e. 20 concurrent users is about the
most you will get. Of course how this will work in your specific situation
you have to test yourself.
But as you are moving from Access to MSDE, MSDE should perform sufficiently
ok. If you already had performance problems on Access, MSDE probably won't
solve these though.
Jacco Schalkwijk
SQL Server MVP
"valkev" <nospam@.4me.com> wrote in message
news:euLDc.9211$%l7.3730@.newssvr24.news.prodigy.co m...[vbcol=seagreen]
> "Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
> news:2k8s2kF17hgbiU1@.uni-berlin.de...
MSDE[vbcol=seagreen]
> is
> or
> place?
hits[vbcol=seagreen]
> are
to[vbcol=seagreen]
> but
> workloads
> two
> 1gb
> can
> the
> the
> thread
> OLE-DB
> on..
> address
configuration[vbcol=seagreen]
> not
> memory
it[vbcol=seagreen]
the[vbcol=seagreen]
> Server
4[vbcol=seagreen]
> of
> further
on...[vbcol=seagreen]
> on
resource
> Andrea,
> Thanks for the great info. Your explanation is consistent with what I'm
> seeing.
> So, the only real performance difference seen going from MSDE to SQL
Server
> is that the Workload Query Governor will not be kicking in and slowing
> things down on active installations correct? Isn't this governor on the
> order of milliseconds?
> Thanks,
> Kevin
>
|||Thanks Jacco.
Yeah it's kind of funny. Under Access and IIS4 running on NT, we had some
sites getting over 3M hits per month and MOST of those hits were on
db-driven pages! The Access db itself might get as big as say 10MB in size.
And we had NO problems.
Then, when going to Windows 2003 Server and running very SMALL sites using
Access, we immediately ran into this Jet OLEDB hang problem and basically
nothing runs under Access on a new latest-greatest server. Submitte a
ticket, got a little response and a dll thrown at me which didn't help any,
and haven't heard since.
So, any db needs I have for sites on this particular server we're just
coding for MSDE instead of Access because it's really our only option.
I have PHP and MySQL installed on the server, and really want to jump into
mySQL to see how it compares to Access and MSDE and SQL Server but just
haven't had the time. I suspect it will perform somewhat like MSDE, faster
because of the governors, but not as well as SQL Server who knows. My
experience is Windows, SQL Server, SQL Enterprise, stored procedures, et al
so not sure how much I'll end up doing in mySQL UNLESS I foresee getting so
many new db-driven sites that we can't afford to move to SQL Server over
MSDE and so would need to go the mySQL route.
Decision decisions.
Thanks,
Kevin
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:uaN2pGQXEHA.1036@.TK2MSFTNGP10.phx.gbl...
> Hi Kevin,
> The Workload Governor on MSDE is somewhat more serious than a matter of
> milliseconds. Basically it is supposed to give MSDE approximately the same
> performance characteristics as Access, i.e. 20 concurrent users is about
the
> most you will get. Of course how this will work in your specific situation
> you have to test yourself.
> But as you are moving from Access to MSDE, MSDE should perform
sufficiently[vbcol=seagreen]
> ok. If you already had performance problems on Access, MSDE probably won't
> solve these though.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "valkev" <nospam@.4me.com> wrote in message
> news:euLDc.9211$%l7.3730@.newssvr24.news.prodigy.co m...
> MSDE
I[vbcol=seagreen]
services,[vbcol=seagreen]
> hits
batches[vbcol=seagreen]
reverting[vbcol=seagreen]
> to
batches,[vbcol=seagreen]
in[vbcol=seagreen]
easy[vbcol=seagreen]
internal[vbcol=seagreen]
the[vbcol=seagreen]
BPool[vbcol=seagreen]
of[vbcol=seagreen]
minus[vbcol=seagreen]
> configuration
are[vbcol=seagreen]
release[vbcol=seagreen]
> it
> the
between[vbcol=seagreen]
> 4
see[vbcol=seagreen]
becouse[vbcol=seagreen]
> on...
> resource
visual
> Server
>
|||Thanks Jacco.
Yeah it's kind of funny. Under Access and IIS4 running on NT, we had some
sites getting over 3M hits per month and MOST of those hits were on
db-driven pages! The Access db itself might get as big as say 10MB in size.
And we had NO problems.
Then, when going to Windows 2003 Server and running very SMALL sites using
Access, we immediately ran into this Jet OLEDB hang problem and basically
nothing runs under Access on a new latest-greatest server. Submitte a
ticket, got a little response and a dll thrown at me which didn't help any,
and haven't heard since.
So, any db needs I have for sites on this particular server we're just
coding for MSDE instead of Access because it's really our only option.
I have PHP and MySQL installed on the server, and really want to jump into
mySQL to see how it compares to Access and MSDE and SQL Server but just
haven't had the time. I suspect it will perform somewhat like MSDE, faster
because of the governors, but not as well as SQL Server who knows. My
experience is Windows, SQL Server, SQL Enterprise, stored procedures, et al
so not sure how much I'll end up doing in mySQL UNLESS I foresee getting so
many new db-driven sites that we can't afford to move to SQL Server over
MSDE and so would need to go the mySQL route.
Decision decisions.
Thanks,
Kevin
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:uaN2pGQXEHA.1036@.TK2MSFTNGP10.phx.gbl...
> Hi Kevin,
> The Workload Governor on MSDE is somewhat more serious than a matter of
> milliseconds. Basically it is supposed to give MSDE approximately the same
> performance characteristics as Access, i.e. 20 concurrent users is about
the
> most you will get. Of course how this will work in your specific situation
> you have to test yourself.
> But as you are moving from Access to MSDE, MSDE should perform
sufficiently[vbcol=seagreen]
> ok. If you already had performance problems on Access, MSDE probably won't
> solve these though.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "valkev" <nospam@.4me.com> wrote in message
> news:euLDc.9211$%l7.3730@.newssvr24.news.prodigy.co m...
> MSDE
I[vbcol=seagreen]
services,[vbcol=seagreen]
> hits
batches[vbcol=seagreen]
reverting[vbcol=seagreen]
> to
batches,[vbcol=seagreen]
in[vbcol=seagreen]
easy[vbcol=seagreen]
internal[vbcol=seagreen]
the[vbcol=seagreen]
BPool[vbcol=seagreen]
of[vbcol=seagreen]
minus[vbcol=seagreen]
> configuration
are[vbcol=seagreen]
release[vbcol=seagreen]
> it
> the
between[vbcol=seagreen]
> 4
see[vbcol=seagreen]
becouse[vbcol=seagreen]
> on...
> resource
visual
> Server
>
|||Don't assume you can't afford SQL Server over MySQL. MySQL is Open Source,
but it is not free, an entry level tech support contract is ?1500/year, the
most expensive support contract with MySQL is ?48,000 per year. Compare that
with SQL Server licenses.
Jacco Schalkwijk
SQL Server MVP
"valkev" <nospam@.4me.com> wrote in message
news:0V4Ec.9708$9w2.1928@.newssvr24.news.prodigy.co m...
> Thanks Jacco.
> Yeah it's kind of funny. Under Access and IIS4 running on NT, we had some
> sites getting over 3M hits per month and MOST of those hits were on
> db-driven pages! The Access db itself might get as big as say 10MB in
size.
> And we had NO problems.
> Then, when going to Windows 2003 Server and running very SMALL sites using
> Access, we immediately ran into this Jet OLEDB hang problem and basically
> nothing runs under Access on a new latest-greatest server. Submitte a
> ticket, got a little response and a dll thrown at me which didn't help
any,
> and haven't heard since.
> So, any db needs I have for sites on this particular server we're just
> coding for MSDE instead of Access because it's really our only option.
> I have PHP and MySQL installed on the server, and really want to jump into
> mySQL to see how it compares to Access and MSDE and SQL Server but just
> haven't had the time. I suspect it will perform somewhat like MSDE,
faster
> because of the governors, but not as well as SQL Server who knows. My
> experience is Windows, SQL Server, SQL Enterprise, stored procedures, et
al
> so not sure how much I'll end up doing in mySQL UNLESS I foresee getting
so
> many new db-driven sites that we can't afford to move to SQL Server over
> MSDE and so would need to go the mySQL route.
> Decision decisions.
> Thanks,
> Kevin
>
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid >
wrote[vbcol=seagreen]
> in message news:uaN2pGQXEHA.1036@.TK2MSFTNGP10.phx.gbl...
same[vbcol=seagreen]
> the
situation[vbcol=seagreen]
> sufficiently
won't[vbcol=seagreen]
> I
> services,
> batches
> reverting
> batches,
allocation[vbcol=seagreen]
> in
leave"[vbcol=seagreen]
> easy
> internal
> the
> BPool
amount[vbcol=seagreen]
> of
> minus
for[vbcol=seagreen]
so[vbcol=seagreen]
> are
that[vbcol=seagreen]
> release
it's[vbcol=seagreen]
releases[vbcol=seagreen]
> between
> see
> becouse
one[vbcol=seagreen]
> visual
I'm[vbcol=seagreen]
the
>
|||Don't assume you can't afford SQL Server over MySQL. MySQL is Open Source,
but it is not free, an entry level tech support contract is ?1500/year, the
most expensive support contract with MySQL is ?48,000 per year. Compare that
with SQL Server licenses.
Jacco Schalkwijk
SQL Server MVP
"valkev" <nospam@.4me.com> wrote in message
news:0V4Ec.9708$9w2.1928@.newssvr24.news.prodigy.co m...
> Thanks Jacco.
> Yeah it's kind of funny. Under Access and IIS4 running on NT, we had some
> sites getting over 3M hits per month and MOST of those hits were on
> db-driven pages! The Access db itself might get as big as say 10MB in
size.
> And we had NO problems.
> Then, when going to Windows 2003 Server and running very SMALL sites using
> Access, we immediately ran into this Jet OLEDB hang problem and basically
> nothing runs under Access on a new latest-greatest server. Submitte a
> ticket, got a little response and a dll thrown at me which didn't help
any,
> and haven't heard since.
> So, any db needs I have for sites on this particular server we're just
> coding for MSDE instead of Access because it's really our only option.
> I have PHP and MySQL installed on the server, and really want to jump into
> mySQL to see how it compares to Access and MSDE and SQL Server but just
> haven't had the time. I suspect it will perform somewhat like MSDE,
faster
> because of the governors, but not as well as SQL Server who knows. My
> experience is Windows, SQL Server, SQL Enterprise, stored procedures, et
al
> so not sure how much I'll end up doing in mySQL UNLESS I foresee getting
so
> many new db-driven sites that we can't afford to move to SQL Server over
> MSDE and so would need to go the mySQL route.
> Decision decisions.
> Thanks,
> Kevin
>
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid >
wrote[vbcol=seagreen]
> in message news:uaN2pGQXEHA.1036@.TK2MSFTNGP10.phx.gbl...
same[vbcol=seagreen]
> the
situation[vbcol=seagreen]
> sufficiently
won't[vbcol=seagreen]
> I
> services,
> batches
> reverting
> batches,
allocation[vbcol=seagreen]
> in
leave"[vbcol=seagreen]
> easy
> internal
> the
> BPool
amount[vbcol=seagreen]
> of
> minus
for[vbcol=seagreen]
so[vbcol=seagreen]
> are
that[vbcol=seagreen]
> release
it's[vbcol=seagreen]
releases[vbcol=seagreen]
> between
> see
> becouse
one[vbcol=seagreen]
> visual
I'm[vbcol=seagreen]
the
>
sql

MSDE and Client Tools from SQLServer Eval

Ok, a while back, probably a year or so I started moving to MSDE. At the time a read posts on the Web that said you could use the client tools from the SQL Server Eval to manage MSDE. Great! I thought.

But more recently I get the impression that using those tools in not allowed. Can someone give me a diffinative answer.

I really want to stick with MSDE and am having a hard time convincing the boss to shell out $9,000+ for SQL Server on my dual processor web server. But I need DTS, Job Scheduling, etc...

Now my boss has wind of MySQL and knows that it's CHEAP.

If I can legally use the client tools with MSDE than I'm fine, if not I dread going to MySQL.

Your Advice is appreciated.Naturally you should check that your use of MSDEis appropriate.

If so do a Google on "Manage MSDE" and you'll get lots of hits. One popular one isthis one.

I think you can use DTS with MSDE but I don't know about scheduling as that is run by the Sql Server Agent which is a separate service. If you have a machine with just MSDE on it (i.e., not Sql Server) then look in the services to see if Sql Server Agent is running. If it's not there you'll have to create your own schedulerwhich isn't that hard.|||How about getting the Developer Edition of SQLServer and using the Client Tools to manage MSDE on the live server.|||If you read theappropriate uses that McMurdoStation posted, you will see what Microsoft has to say about that:

Q. Can I use SQL Server tools and services in conjunction with MSDE?

A. You can only use SQL Server tools and services in conjunction with MSDE if you acquired MSDE through SQL Server 2000 (Developer Edition, Standard Edition, or Enterprise Edition), and if you are using MSDE in conjunction with a properly licensed copy of SQL Server 2000. Visit the How to Buy page for information on obtaining a valid SQL Server license.

The following utilities are installed by the MSDE setup application and are provided without restrictions for use with the copy of MSDE that is installed by your application: bcp.exe, cnfgsvr.exe, dcomscm.exe, osql.exe, sqlmaint.exe, sqlmangr.exe, scm.exe, sqladhlp.exe, and svrnetcn.exe. The dtsrun.exe utility is also provided, but can only be used to run existing Data Transformation Services (DTS) packages against the copy of MSDE installed by your application; it cannot be used to develop new DTS packages.

Terri|||Yes, but it is not clear to me if the Developer Edition (Tools) can be used to manage a production MSDE engine.|||First a disclaimer: I am neither a lawyer or a Microsoft employee. I am only offering my opinion on the interpretation of what often looks to me like gobbledy-gook.

If you purchase SQL Server Developer edition, this comes with MSDE and the client tools. It says that you can use the tools and services with MSDE if you 1) acquired MSDE through SQL Server 2000 Developer edition and 2) you are using MSDE in conjunction with a properly licensed copy of SQL Server 2000.

So, you'd definitely have #1 covered.

However, for #2, theSQL Server 2000 Licensing FAQ says this: The SQL Server Developer edition is licensed per developer and must be used for designing, developing, and testing purposes only.

Putting all of that together, it seems that using the client tools that came with the Developer edition to manage a production database would be against the licensing terms.

Terri|||Maybe I think this because I want to, but it seems that if I buy Developer edition:

1) acquired MSDE through SQL Server 2000 Developer edition

Yep I bought it.

2) using MSDE in conjunction with a properly licensed copy of SQL Server 2000

Yep. I'm using MSDE with a Developer edition on my Dev server. The Dev server is only for designing, developing and testing. That doesn't mean that the MSDE needs to be development only. Does it?

I know I am splitting hairs.

I just called Microsoft and they said it was ok. Great!

I'm soooo glad I don't have to use MySQL

MSDE and "SQL Web Data Administrator"

Hello all,

I am sorry for repeating the question in another section of the forum.

I am using "SQL Web Data Administrator" to manage the security and the roles issues in my database. I am using MSDE and i get to a point that i can not create grant statements in "SQL Web Data Administrator" manualy. I only was able to create some user accounts and giving them some roles automaticly"by clicking" without writtng grant statemnts. Clearly for me there was no place in the program to write grant statments. eg: in the graphical user interface of the "Sql Web Data Administrator" you can not restrict an access to some table in a database, but you can restrict access to a specific database for some users. Now, the question is : Is there any way to write grant permissions for some users using "Sql Web Data Administrator", or alternatevely, where can i wite any T-sql in my application and execute them.

Note:
I do not have Enterprise manager or sql analyzer.

please help.I don't know all of the ins and outs of WDA, but I know you can use the osql command line utility to execute T-SQL statements. SeeHow To Manage the SQL Server Desktop Engine (MSDE 2000) by Using the Osql Utility

Terri|||Dear Terri,

My main problem is not knowing how to write T-SQL statemnts in Osql or SWDA. the concern is how these T-sql statemnts can be used and saved. Yes, i can execute them in both programs,but that i think is not enough to save them to be used in the application. I tried to use grant statemnts in my application, but i do not know where to plug it in to be executed when the application is running.

Terri, can you tell me where can i write and save these T-Sql statments to be used and executed in my application(asp.net) especially the grant statments below:

Eg:

grant select on table1 to user1
grant select on table2 to user1
grant select on table3 to user1

Thanks a lot|||Sorry, I misunderstood your question. Typically stored procedures are created to execute code that will be reused. And you'd execute stored procedures such as these with the ExecuteNonQuery command since they are not returning a resultset.

Terri|||you are suggesting that the only way to run the sql grant statments is by putting them in a stored procedure. But what if i do not want to use stored procedure. Eg: restricting the access to some tables through the application itself.

thanks any way Terri|||Then just use command text. I'm not really sure what the issue is here (well other than the possible security issues if you're not careful)?

Dim myConnection As New SqlConnection( _
 "server=(local)\NetSDK;database=pubs;Integrated Security=SSPI")
Dim myCommand As New SqlCommand( _
 "GRANT SELECT ON authors TO Mary, John, Tom", myConnection)
myCommand.Connection.Open()
myCommand.ExecuteNonQuery()
myCommand.Connection.Close()

Terri|||Hello Terri again,

I am sorry for not clarifying what i want to achieve. What i want to get is to prevent certain users from aceesing certian tables(table1 , table2) and not table 3, 4...
I have used this code to do that. Unfortunatel, my code prevent access to the authorised tables too. What is the suitable query string that do this task in the below code?

 Sub Runquery_btn_Click(sender As Object, e As EventArgs)
 Dim denyquerystr as string
 Connectionstring = ctype(session("Connectionstr"), string)
 if Sqlinput_txt.text <> "" then
 try
 Sqlconnection = New Sqlconnection (connectionString)
 denyquerystr = "Deny SELECT ON table1 TO student"
 SqlCommand = new SqlCommand(denyquerystr, Sqlconnection)
 SqlConnection.Open()
 SqlCommand.ExecuteNonQuery()
 catch ex as Exception
 Alermessage (ex.Message)
 finally
 SqlConnection.Close()
 End try
End sub

Thanks again Terri|||I think the deny stataments has an effect on the whole application. Therefore i can not log in with the user student. Although, i did not use any deny stored procedure and deleted all the deny statemnts. Is there any way to get back to the normal situation. Means how it is possible to delete the effect of deny statments on the application

Pleas help.

MSDE 2000A EULA / SBS 2003 Web Edition

Hi. I'm a bit confused and wonder whether someone of you can give me some
more information on this.
The EULA for MSDE 2000A states that you cannot use MSDE for hosting
services. Does anyone know what this mean?
I'm running an ASP.Net application and would like to use MSDE as the backend
database. Does this fall into this category?
Or does it mean that if I'm an ISP, I'm not allowed to offer MSDE as a
feature on my hosting packages?
Also, it says that you're not allowed to install any applications on SBS
2003 Web Edition. Does MSDE fall into this category?
Thanks very much for your help!
Martin
Hi Martin,
If you plan on using Webserver I think you need the full version of SQL. As
for SBS webserver 2003 I think you mean Windows Webserver 2003, which is a
lower priced version of windows server with a limited feature set optimized
for webhosting and reducing the cost of server to make it more competitive
in that market place. If you are a programmer look into either the ISV
program or the Action pack program which will help you out with the software
required to setup the development environment.
Regards,
Jeff Loucks
Available Technology
Solutions For Professionals
www.availabletechnology.com
"Martin Feuersteiner" <theintrepidfox@.hotmail.com> wrote in message
news:u$$6SFOcEHA.212@.TK2MSFTNGP12.phx.gbl...
> Hi. I'm a bit confused and wonder whether someone of you can give me some
> more information on this.
> The EULA for MSDE 2000A states that you cannot use MSDE for hosting
> services. Does anyone know what this mean?
> I'm running an ASP.Net application and would like to use MSDE as the
backend
> database. Does this fall into this category?
> Or does it mean that if I'm an ISP, I'm not allowed to offer MSDE as a
> feature on my hosting packages?
> Also, it says that you're not allowed to install any applications on SBS
> 2003 Web Edition. Does MSDE fall into this category?
> Thanks very much for your help!
> Martin
>
>
>

MSDE 2000 Release A Supported systems

The addendum to the Readme file for MSDE 2000 Release A states "MSDE 2000 Release A is supported on Microsoft Windows Server 2003, Web Edition".
Does this mean that it executes only under Windows 2000 Server 2003 ?
I am running Windows 2000 Pro, it doesnt work. Does anyone run MSDE 2000 Rel A under other windows versions?
Mark Ferguson
hi Mark,
"Mark Ferguson" <MarkFerguson@.discussions.microsoft.com> ha scritto nel
messaggio news:F7D2BF11-BE8F-4AD7-B1AE-FAA334E68CE7@.microsoft.com...
> The addendum to the Readme file for MSDE 2000 Release A states "MSDE 2000
Release A is supported on Microsoft Windows Server 2003, Web Edition".
> Does this mean that it executes only under Windows 2000 Server 2003 ?
> I am running Windows 2000 Pro, it doesnt work. Does anyone run MSDE 2000
Rel
>A under other windows versions?
I think that extract shoul'd be intended as "Microsoft Windows Server 2003,
Web Edition can only run MSDE and not a full blown SQL Server edition"...
MSDE can be installed up to Win98 boxes, more system requirements at
http://www.microsoft.com/sql/msde/pr...fo/sysreqs.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

MSDE 2000 or SQL Server 2005 Express (Beta) for new project?

Hello guys! I have to develop a new web based project. It's a webportal for events. At the moment it's runnung on an Access database.The new version should be running on a SQL server - the desicion is:MSDE 2000 or SQL Server 2005 Express (Beta). What do you recommend? Thewebpages are developed under .NET 1.1.
SQL Server 2005 Express is not going to be as stable and is not aproven technology as is MSDE. But if your release date somewhatdistant (several months), you may want to use it (hopefully the RTMversion will be relased before you finish.) You'll still be anearly adopter, though, and may have to deal with bugs. In theend, it's a question of: What features do you need in Express that MSDEdoesn't provide? And do these features counter-balance the issuesof working with a beta?
|||Well ... it's a company philosophy to work always on the newsestplattform. If you are common with the MSDE and the SQL Express 2005edition you can surly say if there are some performance improvements.The project leaders think that the MSDE version is not so fast. Specialfeatures are not requested, only SQL querys and stored procedures (...and performance ). Is a change of database engine (from MSDEto SQL 2005) a problem in the future?
Thanks a lot!!!
|||

Here my first look at SQL Server 2005, an events web site needs Time Interval, it is built in SQL server 2005 and index Column include without Composite index. It also schreds and decomposes XML native. Hope this helps.

SQL SERVER 2005 Development

What is new for ASP.NET

The most important is CLR integration write your stored procedures in C#.NET and VB.NET.

INDEX COLUMN INCLUDE, NEW DRI(Declarative Referential Integrity) CASCADE DELETE SET NULL AND CASCADE UPDATE SET NULL, When used with Portal Templates allow the user to browse before login. This is almost like Denormalization with Composite index without the Primary Key restriction of NOT NULL. ANSI NULL enabling on index columns.

Varchar (max), Nvarchar(max) Varbinary(max) Variable length data that may exceed 8000 and 4000 with 2gig limit, in the past these are stored in Text and Image, SQL Server creates an

Arithmetic pointer to the data because it is not table row based.

DTS is now Integration Service with pipeline.

Service Broker can be used to Queue shipping data while SQL Server will process sales Transactions.

XML DATATYPE

XML native Datatypes including XML indexes and XML decomposition.

XML QUERY EXAMPLE using XML PATH

SELECT CustomerID as "@.CustomerID",

(SELECT OrderID as "@.OrderID"

FROM Orders

WHERE Orders.CustomerID = Customers.CustomerID

FOR XML PATH('Order'), TYPE),

(SELECT DISTINCT LastName as "@.LastName"

FROM Employees

JOIN Orders ON Orders.EmployeeID = Employees.EmployeeID

WHERE Customers.CustomerID = Orders.CustomerID

FOR XML PATH('Employee'), TYPE)

FROM Customers

FOR XML PATH('Customer')

COMMON TABLE EXPRESSIONS CTE VIRTIUAL VIEWS

VIEWS are Query Rewrites in ANSI SQL

ANSI SQL 99 with Recursive QUERY Implementation some restrictions.

Tables must be equal INNER JOIN

CONVERT clause can be used to meet the UNION operator requirement.

But multiple Recursive members can only be connected by UNION ALL not UNION because UNION removes duplicates just like SELECT DISTINCT .

Recursion Control

Default server wide - 100

Configurable maximum iterations

OPTION MAXRECURSION <value>

1-32767

Recursive Member Restrictions

No operations leading to DISTINCT

Only UNION ALL allowed between anchor and recursive

SELECT DISTINCT

GROUP BY

HAVING

Scalar Aggregation

TOP

Outer Joins

1 CTE reference per recursive member

The example below in the executing statement, the CTE is referenced twice; that is the reason for the OUTER JOIN, the OUTER JOIN is not allowed with Recursive CTE because in ANSI SQL OUTER JOIN has a default NULL condition and is limited to four iterations while a Recursive CTE can run to 100.

USE AdventureWorks;

GO

WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate)

AS

(

SELECT SalesPersonID, COUNT(*), MAX(OrderDate)

FROM Sales.SalesOrderHeader

GROUP BY SalesPersonID

)

SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,

E.ManagerID, OM.NumberOfOrders, OM.MaxDate

FROM HumanResources.Employee AS E

JOIN Sales_CTE AS OS

ON E.EmployeeID = OS.SalesPersonID

LEFT OUTER JOIN Sales_CTE AS OM

ON E.ManagerID = OM.SalesPersonID

ORDER BY E.EmployeeID;

GO

Recursive CTE using CONVERT clause to satisfy the UNION ALL operator requirement.

USE AdventureWorks ;

Go

WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort)

AS (SELECT CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName),

e.Title,

e.EmployeeID,

1,

CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName)

FROM HumanResources.Employee AS e

JOIN Person.Contact AS c ON e.ContactID = c.ContactID

WHERE e.ManagerID IS NULL

UNION ALL

SELECT CONVERT(Varchar(255), REPLICATE ('| ' , EmployeeLevel) +

c.FirstName + ' ' + c.LastName),

e.Title,

e.EmployeeID,

EmployeeLevel + 1,

CONVERT (Varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' +

LastName)

FROM HumanResources.Employee as e

JOIN Person.Contact AS c ON e.ContactID = c.ContactID

JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID

)

SELECT EmployeeID, Name, Title, EmployeeLevel

FROM DirectReports

ORDER BY Sort ;

GO

The following example is using MAXRECURSION to cancel a statement.

USE AdventureWorks ;

GO

WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS

(

SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel

FROM HumanResources.Employee

WHERE ManagerID IS NULL

UNION ALL

SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1

FROM HumanResources.Employee e

INNER JOIN DirectReports d

ON e.ManagerID = d.EmployeeID

)

SELECT *

FROM DirectReports

OPTION (MAXRECURSION 4) ;

GO

First look at SQL Server 2005 development. Comments appreciated.

MSDE + ASPX Web Provider

Can anyone tell me if there are Web Space Providers, who support MSDE and ASPX?
Thanks for Information!
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
Do you mean they provide you with a server?
If so why msde?
see
http://www.webhost4life.com/hosting.asp
"SqlJunkies User" wrote:

> Can anyone tell me if there are Web Space Providers, who support MSDE and ASPX?
> Thanks for Information!
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
>

MSDE & Web pages

Hello all,
I am trying to connect a web page to an Access database (using ADO), using
the MSDE engine as opposed to jet. What I have at the moment doesn't work,
could anyone help?
Thanks in advance,
Adonis.I'm confused by your posting. Are you using MSDE or Access -- two different
databases. And, what do you have as a connection string ?
Michael D.|||Sorry about that. Iam using MSDE. (I have upsized an Mdb file up to an
adp/mdf). The connection string is
strDBPath=server.MapPath("quaSAR.adp")
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=msdasql;Data Source=" &strDBPath & ";"
Any ideas?
Regards,
Adonis.
"Michael D" <anonymous@.discussions.microsoft.com> wrote in message
news:AC684267-206E-47B6-A607-9E9230771A91@.microsoft.com...
> I'm confused by your posting. Are you using MSDE or Access -- two
different databases. And, what do you have as a connection string ?
> Michael D.|||(1) What error message do you get when you are trying to connect ? Please cu
t an paste the entire message here.
(2) Can you connect to the MSDE instance via Query Analyser or an ODBC DSN c
onnection ?
Thanks,
Michael D.|||Have a look at this article...
http://support.microsoft.com/defaul...&product=adonet
Michael D.

MSDE & Web pages

Hello all,
I am trying to connect a web page to an Access database (using ADO), using
the MSDE engine as opposed to jet. What I have at the moment doesn't work,
could anyone help?
Thanks in advance,
Adonis.
I'm confused by your posting. Are you using MSDE or Access -- two different databases. And, what do you have as a connection string ?
Michael D.
|||Sorry about that. Iam using MSDE. (I have upsized an Mdb file up to an
adp/mdf). The connection string is
strDBPath=server.MapPath("quaSAR.adp")
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=msdasql;Data Source=" &strDBPath & ";"
Any ideas?
Regards,
Adonis.
"Michael D" <anonymous@.discussions.microsoft.com> wrote in message
news:AC684267-206E-47B6-A607-9E9230771A91@.microsoft.com...
> I'm confused by your posting. Are you using MSDE or Access -- two
different databases. And, what do you have as a connection string ?
> Michael D.
|||(1) What error message do you get when you are trying to connect ? Please cut an paste the entire message here.
(2) Can you connect to the MSDE instance via Query Analyser or an ODBC DSN connection ?
Thanks,
Michael D.
|||Have a look at this article...
http://support.microsoft.com/default...product=adonet
Michael D.

MSDE & Web pages

Hello all,
I am trying to connect a web page to an Access database (using ADO), using
the MSDE engine as opposed to jet. What I have at the moment doesn't work,
could anyone help?
Thanks in advance,
Adonis.Sorry about that. Iam using MSDE. (I have upsized an Mdb file up to an
adp/mdf). The connection string is
strDBPath=server.MapPath("quaSAR.adp")
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=msdasql;Data Source=" &strDBPath & ";"
Any ideas?
Regards,
Adonis.
"Michael D" <anonymous@.discussions.microsoft.com> wrote in message
news:AC684267-206E-47B6-A607-9E9230771A91@.microsoft.com...
> I'm confused by your posting. Are you using MSDE or Access -- two
different databases. And, what do you have as a connection string ?
> Michael D.|||(1) What error message do you get when you are trying to connect ? Please cut an paste the entire message here
(2) Can you connect to the MSDE instance via Query Analyser or an ODBC DSN connection
Thanks
Michael D.|||Have a look at this article..
http://support.microsoft.com/default.aspx?kbid=308656&product=adone
Michael D

MSDE

I'm trying to install MSDE so I can use MSQL with web matrix. On trying the install I'm getting a message about using SAPWD switch. I've got no idea what that's about, but obviously need to do something somewhere!

I'm running WINXP PRO

ThankxHi, it means that you need to set up a password for the sa account when installing MSDE. Read this:
http://msdn.microsoft.com/library/en-us/distsql/distsql_84xl.asp?frame=true|||how can you tell if you have msde installed or not?|||It will install itself in the c:\program files folder as Microsoft SQL Server|||Follow the instruction in the readme.ntml file. I believe it's section 3.8. Careful when typing the installation string it can be tricky. No spaces between the "="|||Thankx.

I entered the following and ran it. The installation started

C:\sql2ksp3\MSDE\setup SECURITYMODE=SQL SAPWD=my password

After the install ran I got a message saying that : "setup failed to configure the server. Reger to the server error and setup logs"

any clues?|||What do the error logs look like - can you try installing a named instance of msde - your command prompt should look as follows

setup SAPWD="AStrongSAPwd" SECURITYMODE=SQL INSTANCENAME=MyInstance

-Kashif|||I don't know where to access the error logs!

I tried the command that you suggested, I get the message: Please go to the Control Panel to install and configure system components

Thanks|||try this

setup SAPWD="AStrongSAPwd" SECURITYMODE=SQL /l* install.log

to get the logs.

-Kashif|||Kashif, thanks a lot, the following got it all going, my ASP.net apps are all working fine!

setup SAPWD= mypassword SECURITYMODE=SQL INSTANCENAME=MyInstance|||2003-09-02 16:41:40.29 server Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 1)

2003-09-02 16:41:40.29 server Copyright (C) 1988-2002 Microsoft Corporation.
2003-09-02 16:41:40.29 server All rights reserved.
2003-09-02 16:41:40.29 server Server Process ID is 164.
2003-09-02 16:41:40.29 server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL\LOG\ERRORLOG'.
2003-09-02 16:41:40.30 server SQL Server is starting at priority class 'normal'(1 CPU detected).
2003-09-02 16:41:40.40 server SQL Server configured for thread mode processing.
2003-09-02 16:41:40.40 server Using dynamic lock allocation. [500] Lock Blocks, [1000] Lock Owner Blocks.
2003-09-02 16:41:40.47 spid3 Warning ******************
2003-09-02 16:41:40.47 spid3 SQL Server started in single user mode. Updates allowed to system catalogs.
2003-09-02 16:41:40.48 spid3 Starting up database 'master'.
2003-09-02 16:41:40.67 server Using 'SSNETLIB.DLL' version '8.0.766'.
2003-09-02 16:41:40.67 spid5 Starting up database 'model'.
2003-09-02 16:41:40.72 spid3 Server name is 'IANO-SMXPDT'.
2003-09-02 16:41:40.72 spid3 Skipping startup of clean database id 5
2003-09-02 16:41:40.73 spid3 Skipping startup of clean database id 6
2003-09-02 16:41:40.73 spid3 Starting up database 'msdb'.
2003-09-02 16:41:40.76 server SQL server listening on Shared Memory.
2003-09-02 16:41:40.76 server SQL Server is ready for client connections
2003-09-02 16:41:40.83 spid5 Clearing tempdb database.
2003-09-02 16:41:41.20 spid5 Starting up database 'tempdb'.
2003-09-02 16:41:41.27 spid3 Recovery complete.
2003-09-02 16:41:41.27 spid3 SQL global counter collection task is created.
2003-09-02 16:41:41.29 spid3 Warning: override, autoexec procedures skipped.
2003-09-02 16:43:45.56 spid3 SQL Server is terminating due to 'stop' request from Service Control Manager.

What do I need to do to make this work?|||Are you having problems connecting to msde - what is the error that you see? The log file looks fine. Also can you check the version of ssnetlib.dll and post it here.

-Kashif|||Thanks for getting back to me.

Is this it ?
2000.80.766.0

I did a search and got SSnetlib.dll.CC1A8C58_27D1_4D38_BF11_C0A5CCB90616
It did not allow me to right click for properties so I copied it to a Temp directory
Where I go the above version.

Question:
At the end of the posted log file where it says
2003-09-03 09:54:23.12 spid5 Clearing tempdb database.
2003-09-03 09:54:23.51 spid5 Starting up database 'tempdb'.
2003-09-03 09:54:23.61 spid3 Recovery complete.
2003-09-03 09:54:23.61 spid3 SQL global counter collection task is created.
2003-09-03 09:54:23.64 spid3 Warning: override, autoexec procedures skipped.
2003-09-03 09:56:28.14 spid3 SQL Server is terminating due to 'stop' request from Service Control Manager.

Doesn't that 'stop' request mean that I have some service that needs starting or something?

- IanO -|||I forgot to post this. It may help you"
cnfgsvr.out

###############################################################################

Starting Service ...

SQL_Latin1_General_CP1_CI_AS

-m -Q -T4022 -T3659

An error occurred while attempting to start the service (1460)

SQL Server configuration failed.

###############################################################################|||Hasn't this gone on long enough?

How about disclosing the checks that the MSDE installer make and tell us what we have to do to our systems so it will pass the checks?

I and I suspect many others are beyond "just try this" suggestions.

I am appealing to the staff people who KNOW how the MSDE install script is written.
It is an obsurd waste of time and energy on this item and it delays getting into the purpose of this site - Allowing developers to get some experience building some .Net web applications.

So what say you?
Tell us how it works so we can move on?
Please include what service must be running etc.

IanOwm

MSDE

I would appreciate any help, I'm trying to connect a web front-end to an
Access 2000 database using MSDE, and I have it working using Jet but would
like to connect the database using MSDE.
I would appreciate any advice to where I need to edit the code, the code is
as follows: (which is setup for Jet at present)
<%@. Language=VBScript %>
<HTML>
<HEAD>
<TITLE>Add New Record</TITLE>
<%
strDBPath=server.MapPath("Employer.mdb")
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ";"
set RS = Server.CreateObject("ADODB.Recordset")
Conn.BeginTrans
RS.Open "Employer",Conn,3,3
RS.AddNew
RS("Employer ID") = Request("Employer ID")
RS("Employer Name") = Request("Employer Name")
RS("TitleID") = Request("TitleID")
RS("Forename") = Request("Forename")
RS("Surname") = Request("Surname")
RS("Web Address") = Request("Web Address")
RS("Email Address") = Request("Email Address")
RS("Date Recorded") = Request("Date Recorded")
RS("Employer ClassificationID") = Request("Employer ClassificationID")
RS.Update
Conn.CommitTrans
RS.Close
Conn.Close
%>
</HEAD>
<BODY>
Member Registered
</BODY>
</HTML>
As mentioned I would be very grateful for any advice.
Love,
Joanne
What you'll need to do is grant permissions for the login you are
using in MSDE to the database, and then create a connection string
that contains valid credentials. For connection string syntax
examples, see either one of these two sites:
http://able-consulting.com/ADO_Conn.htm
http://www.connectionstrings.com
--Mary
On Mon, 17 May 2004 21:40:09 +0000 (UTC), "Joanne"
<bill884@.hotmail.com> wrote:

>I would appreciate any help, I'm trying to connect a web front-end to an
>Access 2000 database using MSDE, and I have it working using Jet but would
>like to connect the database using MSDE.
>
>I would appreciate any advice to where I need to edit the code, the code is
>as follows: (which is setup for Jet at present)
>
><%@. Language=VBScript %>
><HTML>
><HEAD>
><TITLE>Add New Record</TITLE>
>
><%
>strDBPath=server.MapPath("Employer.mdb")
>Set Conn = Server.CreateObject("ADODB.Connection")
>Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ";"
>set RS = Server.CreateObject("ADODB.Recordset")
>
>Conn.BeginTrans
>RS.Open "Employer",Conn,3,3
>RS.AddNew
>RS("Employer ID") = Request("Employer ID")
>RS("Employer Name") = Request("Employer Name")
>RS("TitleID") = Request("TitleID")
>RS("Forename") = Request("Forename")
>RS("Surname") = Request("Surname")
>RS("Web Address") = Request("Web Address")
>RS("Email Address") = Request("Email Address")
>RS("Date Recorded") = Request("Date Recorded")
>RS("Employer ClassificationID") = Request("Employer ClassificationID")
>RS.Update
>Conn.CommitTrans
>
>RS.Close
>Conn.Close
>%>
>
></HEAD>
><BODY>
>Member Registered
></BODY>
></HTML>
>
>As mentioned I would be very grateful for any advice.
>
>Love,
>
>Joanne
>
|||th syntax change here
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & "uid=;"pwd=;
biswajit das
-- Mary Chipman wrote: --
What you'll need to do is grant permissions for the login you are
using in MSDE to the database, and then create a connection string
that contains valid credentials. For connection string syntax
examples, see either one of these two sites:
http://able-consulting.com/ADO_Conn.htm
http://www.connectionstrings.com
--Mary
On Mon, 17 May 2004 21:40:09 +0000 (UTC), "Joanne"
<bill884@.hotmail.com> wrote:

>I would appreciate any help, I'm trying to connect a web front-end to an
>Access 2000 database using MSDE, and I have it working using Jet but would
>like to connect the database using MSDE.
>as follows: (which is setup for Jet at present)
>

MSDE

I would appreciate any help, I'm trying to connect a web front-end to an
Access 2000 database using MSDE, and I have it working using Jet but would
like to connect the database using MSDE.

I would appreciate any advice to where I need to edit the code, the code is
as follows:

<%@. Language=VBScript %
<HTML
<HEAD
<TITLE>Add New Record</TITLE
<%

strDBPath=server.MapPath("Employer.mdb")

Set Conn = Server.CreateObject("ADODB.Connection")

Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ";"

set RS = Server.CreateObject("ADODB.Recordset")

Conn.BeginTrans

RS.Open "Employer",Conn,3,3

RS.AddNew

RS("Employer ID") = Request("Employer ID")

RS("Employer Name") = Request("Employer Name")

RS("TitleID") = Request("TitleID")

RS("Forename") = Request("Forename")

RS("Surname") = Request("Surname")

RS("Web Address") = Request("Web Address")

RS("Email Address") = Request("Email Address")

RS("Date Recorded") = Request("Date Recorded")

RS("Employer ClassificationID") = Request("Employer ClassificationID")

RS.Update

Conn.CommitTrans

RS.Close

Conn.Close

%
</HEAD
<BODY
Member Registered

</BODY
</HTML
As mentioned I would be very grateful for any advice.

Love,

JoanneConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath &
";"

see
http://www.able-consulting.com/MDAC...Providers.htm#O
LEDBProviderForSQLServer

The code would be better with just an insert statement.
Even better calling a stored procedure with parameters.

Nigel Rivett
www.nigelrivett.net

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Joanne (bill884@.hotmail.com) writes:
> I would appreciate any help, I'm trying to connect a web front-end to an
> Access 2000 database using MSDE, and I have it working using Jet but
> would like to connect the database using MSDE.

Now, this sounds a bit funny to me. I've heard of people access SQL Server
databases from Access, but you want to do it the other way round from
you ASP code? (I assume what you post is ASP, but I don't know ASP, so
I could be wrong.) That does not seem very meaningful to me.

However, assuming that you in fact have a copy of the database that
you have migrated to SQL Server, then you could do:

> strDBPath=server.MapPath("Employer.mdb")

Remove this:

> Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath &
> ";"

Replace with:

Provider=SQLOLEDB;Integrated Security=SSPI;Initial Catalog=Employer

This assumes that you have MSDE running on the local server. If you
have MSDE on another machine, you should add Server=SERVERNAME to
the connection string.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

MSDE

I would appreciate any help, I'm trying to connect a web front-end to an
Access 2000 database using MSDE, and I have it working using Jet but would
like to connect the database using MSDE.
I would appreciate any advice to where I need to edit the code, the code is
as follows: (which is setup for Jet at present)
<%@. Language=VBScript %>
<HTML>
<HEAD>
<TITLE>Add New Record</TITLE>
<%
strDBPath=server.MapPath("Employer.mdb")
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ";"
set RS = Server.CreateObject("ADODB.Recordset")
Conn.BeginTrans
RS.Open "Employer",Conn,3,3
RS.AddNew
RS("Employer ID") = Request("Employer ID")
RS("Employer Name") = Request("Employer Name")
RS("TitleID") = Request("TitleID")
RS("Forename") = Request("Forename")
RS("Surname") = Request("Surname")
RS("Web Address") = Request("Web Address")
RS("Email Address") = Request("Email Address")
RS("Date Recorded") = Request("Date Recorded")
RS("Employer ClassificationID") = Request("Employer ClassificationID")
RS.Update
Conn.CommitTrans
RS.Close
Conn.Close
%>
</HEAD>
<BODY>
Member Registered
</BODY>
</HTML>
As mentioned I would be very grateful for any advice.
Love,
JoanneWhat you'll need to do is grant permissions for the login you are
using in MSDE to the database, and then create a connection string
that contains valid credentials. For connection string syntax
examples, see either one of these two sites:
http://able-consulting.com/ADO_Conn.htm
http://www.connectionstrings.com
--Mary
On Mon, 17 May 2004 21:40:09 +0000 (UTC), "Joanne"
<bill884@.hotmail.com> wrote:

>I would appreciate any help, I'm trying to connect a web front-end to an
>Access 2000 database using MSDE, and I have it working using Jet but would
>like to connect the database using MSDE.
>
>I would appreciate any advice to where I need to edit the code, the code is
>as follows: (which is setup for Jet at present)
>
><%@. Language=VBScript %>
><HTML>
><HEAD>
><TITLE>Add New Record</TITLE>
>
><%
>strDBPath=server.MapPath("Employer.mdb")
>Set Conn = Server.CreateObject("ADODB.Connection")
>Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ";"
>set RS = Server.CreateObject("ADODB.Recordset")
>
>Conn.BeginTrans
>RS.Open "Employer",Conn,3,3
>RS.AddNew
>RS("Employer ID") = Request("Employer ID")
>RS("Employer Name") = Request("Employer Name")
>RS("TitleID") = Request("TitleID")
>RS("Forename") = Request("Forename")
>RS("Surname") = Request("Surname")
>RS("Web Address") = Request("Web Address")
>RS("Email Address") = Request("Email Address")
>RS("Date Recorded") = Request("Date Recorded")
>RS("Employer ClassificationID") = Request("Employer ClassificationID")
>RS.Update
>Conn.CommitTrans
>
>RS.Close
>Conn.Close
>%>
>
></HEAD>
><BODY>
>Member Registered
></BODY>
></HTML>
>
>As mentioned I would be very grateful for any advice.
>
>Love,
>
>Joanne
>|||th syntax change here
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & "uid
=;"pwd=;
biswajit das
-- Mary Chipman wrote: --
What you'll need to do is grant permissions for the login you are
using in MSDE to the database, and then create a connection string
that contains valid credentials. For connection string syntax
examples, see either one of these two sites:
http://able-consulting.com/ADO_Conn.htm
http://www.connectionstrings.com
--Mary
On Mon, 17 May 2004 21:40:09 +0000 (UTC), "Joanne"
<bill884@.hotmail.com> wrote:

>I would appreciate any help, I'm trying to connect a web front-end to an
>Access 2000 database using MSDE, and I have it working using Jet but would
>like to connect the database using MSDE.
>as follows: (which is setup for Jet at present)
>