Having trouble connecting to MSDE

I have a very basic workgroup at home. I have three
computers, two of them on XP Pro (the ones I'm using MSDE
with). I was able to install MSDE and I see the icon on
my system tray. Now, I was reading how to upsize an
Access database from this page
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/dnmsde/html/msdeforvs.asp
I choose to create a new database after starting the
Upsize wizard. Then I choose the server, which shows up
in the drop down box. Then it asks me for a username and
password that has "CREATE DATABASE" privileges on the
server. So I put my administrator user name and password
in, then choose the name of my new MSDE database, but it
won't connect. I then tried using no username or
password and I get another error message. What's going
on?
This is a link to my errors.
http://www.neolinkcomputers.com/stuf...tion_errors.ht
m
Thanks
hi,
"Culbert" <anonymous@.discussions.microsoft.com> ha scritto nel messaggio
news:9fc901c47908$3cad5bf0$a501280a@.phx.gbl...
> I have a very basic workgroup at home. I have three
> computers, two of them on XP Pro (the ones I'm using MSDE
> with). I was able to install MSDE and I see the icon on
> my system tray. Now, I was reading how to upsize an
> Access database from this page
> http://msdn.microsoft.com/library/default.asp?
> url=/library/en-us/dnmsde/html/msdeforvs.asp
> I choose to create a new database after starting the
> Upsize wizard. Then I choose the server, which shows up
> in the drop down box. Then it asks me for a username and
> password that has "CREATE DATABASE" privileges on the
> server. So I put my administrator user name and password
> in, then choose the name of my new MSDE database, but it
> won't connect. I then tried using no username or
> password and I get another error message. What's going
> on?
> This is a link to my errors.
> http://www.neolinkcomputers.com/stuf...tion_errors.ht
> m
> Thanks
the reported errors indicate :
a) you instance is set to only allow Windows NT (trusted) connection, which
is the default when installing MSDE on Windows NT platform... in order to
allow SQL Server authenticated connection, please have a look at
http://support.microsoft.com/default...b;en-us;285097 for further
info in order to change this behaviour both at install time and later...
b)you windows account you are using to connect to that MSDE instance is not
granted login provileges...
you have to login with a sysadmin login, usually part of
builtin\administrators of SQL Server, and grant that windows account login
privileges using sp_grantlogin system stored procedure
(http://msdn.microsoft.com/library/de...-us/tsqlref/ts
_sp_ga-gz_8dri.asp) and then grant database access to that login for all
desired databases using sp_grantdbaccess system stored procedure
(http://msdn.microsoft.com/library/de...-us/tsqlref/ts
_sp_ga-gz_290z.asp)
for your convenience, you can have a look at a free prj of mine at the link
following my sign., which provides a user interface similar to Enterprise
Manager, which provides these functionnalities too in a viusal way..
further free and/or commercial tools for managing MSDE can be found at
http://www.microsoft.com/sql/msde/partners/default.asp and
http://www.aspfaq.com/show.asp?id=2442
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
|||
>--Original Message--
>hi,
>"Culbert" <anonymous@.discussions.microsoft.com> ha
scritto nel messaggio[vbcol=seagreen]
>news:9fc901c47908$3cad5bf0$a501280a@.phx.gbl...
MSDE[vbcol=seagreen]
http://www.neolinkcomputers.com/stuf...tion_errors.ht
>the reported errors indicate :
>a) you instance is set to only allow Windows NT (trusted)
connection, which
>is the default when installing MSDE on Windows NT
platform... in order to
>allow SQL Server authenticated connection, please have a
look at
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;285097 for further
>info in order to change this behaviour both at install
time and later...
>b)you windows account you are using to connect to that
MSDE instance is not
>granted login provileges...
>you have to login with a sysadmin login, usually part of
>builtin\administrators of SQL Server, and grant that
windows account login
>privileges using sp_grantlogin system stored procedure
>(http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/tsqlref/ts
>_sp_ga-gz_8dri.asp) and then grant database access to
that login for all
>desired databases using sp_grantdbaccess system stored
procedure
>(http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/tsqlref/ts
>_sp_ga-gz_290z.asp)
>for your convenience, you can have a look at a free prj
of mine at the link
>following my sign., which provides a user interface
similar to Enterprise
>Manager, which provides these functionnalities too in a
viusal way..
>further free and/or commercial tools for managing MSDE
can be found at
>http://www.microsoft.com/sql/msde/partners/default.asp and
>http://www.aspfaq.com/show.asp?id=2442
>--
>Andrea Montanari (Microsoft MVP - SQL Server)
>http://www.asql.biz/DbaMgr.shtm
http://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
>.
Incredible! I haven't tried this yet, but I'm confident
that this will work. Thank you for such a great response!!
>
|||Hi,
Maybe I should start a new thread, but my problem is the same, except that I know that the problem is my TCP/IP Access is disabled to MSDE.
I have no Enterprise Manager, any idea wich registry key I need to modify to enable TCP/IP Access?
(Similar to the SQL/Windows Authentification problem?)
Thanks!|||hi,
"uritsukidoji" <uritsukidoji.1aq8cb@.mail.mcse.ms> ha scritto nel messaggio
news:uritsukidoji.1aq8cb@.mail.mcse.ms...
> Hi,
> Maybe I should start a new thread, but my problem is the same, except
> that I know that the problem is my TCP/IP Access is disabled to MSDE.
> I have no Enterprise Manager, any idea wich registry key I need to
> modify to enable TCP/IP Access?
> (Similar to the SQL/Windows Authentification problem?)
> Thanks!
have a look at Server Network Utility (svrnetcn.exe) for server side
settings, and ClientNetwork Utility (cliconfg.exe) fro client side part
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

Hard Drive Space and Disk Caching

Dear friends
I have what may be a basic question to most folks here. Does SQL manage disk caching across partitions or hard drives for greatest efficiency
Basically I have a small C drive partition reserved for basic operating systems stuff. All program and data files, including SQL and databases are on the much larger D and E drives. Would there ever be an issue of the C drive not being large enough and causing SQL to slow down or not perform as well
Someone told me that all disk caching happens on the C drive, which doesn't make sense to me. It seems to me that SQL would be smart enough to use other physical drive resources as needed. They are encouraging us to use Partition Magic to expand the size of our C drive (which currently has about 1 Gig available, to be expanded to between 5 and 10 Gig)
Does this seem like good advice?I do not understand what they are saying - by definition caching is =holding data locally for fast access, SQL Server holds data in memory =having read it from disc. What drive letter is utterly irrelevant to it =(and from a data management perspective the whole concept of drive =letters is horrid!)
SQL Server caching is fine across any drive letter - it cares little =about such things. I have one system where C is getting really tight =(due to successive service packs and fixes) but all the SQL server =stuff, pagefile etc is elsewhere and we have noticed no performance =problems.
Mike John
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message =news:4376A82B-AC21-463C-BCDD-B124FE980C98@.microsoft.com...
> Dear friends,
> > I have what may be a basic question to most folks here. Does SQL =manage disk caching across partitions or hard drives for greatest =efficiency?
> > Basically I have a small C drive partition reserved for basic =operating systems stuff. All program and data files, including SQL and =databases are on the much larger D and E drives. Would there ever be an =issue of the C drive not being large enough and causing SQL to slow down =or not perform as well?
> > Someone told me that all disk caching happens on the C drive, which =doesn't make sense to me. It seems to me that SQL would be smart enough =to use other physical drive resources as needed. They are encouraging =us to use Partition Magic to expand the size of our C drive (which =currently has about 1 Gig available, to be expanded to between 5 and 10 =Gig).
> > Does this seem like good advice?|||Kevin,
First off sql servers cache is a memory based cache not a disk one. The
cache your thinking of is the OS cache or virtual memory. A properly tuned
Sql Server will not use this cache at all. And yes it is usually on the c:
drive. Second it sounds like your drives are all logical drives on the same
physical hard drive. If this is the case it doesn't matter since it is all
the same physical drive. Having multiple logical partitions is more of a
hindrance than a help in that case.
--
Andrew J. Kelly
SQL Server MVP
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:4376A82B-AC21-463C-BCDD-B124FE980C98@.microsoft.com...
> Dear friends,
> I have what may be a basic question to most folks here. Does SQL manage
disk caching across partitions or hard drives for greatest efficiency?
> Basically I have a small C drive partition reserved for basic operating
systems stuff. All program and data files, including SQL and databases are
on the much larger D and E drives. Would there ever be an issue of the C
drive not being large enough and causing SQL to slow down or not perform as
well?
> Someone told me that all disk caching happens on the C drive, which
doesn't make sense to me. It seems to me that SQL would be smart enough to
use other physical drive resources as needed. They are encouraging us to
use Partition Magic to expand the size of our C drive (which currently has
about 1 Gig available, to be expanded to between 5 and 10 Gig).
> Does this seem like good advice?|||Dear Andriew and Mike,
Thank you both for your responses. It seems to me, from them, that we do not have an immediate need to re-partition our hard drive.
I did wonder what you had in mind Andrew, though, when you said:
"Having multiple logical partitions is more of a hindrance than a help in that case."
Pardon my naivete. Thanks!
In Peace,
K.|||Two things that I see often. One is that by having several logical drives
it tends to give the impression there are multiple physical drives. For
someone not familiar with the hardware they may make assumptions that are
wrong based on this. The other is that by having multiple logical
partitions you effectively limit how much space you have in each. Where as
if it was all one logical partition you don't have to worry about running
out of room until you hit the disk limit, not the partition limit. Hope
that helps.
--
Andrew J. Kelly
SQL Server MVP
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:41D04118-B9C2-45BD-8D81-1179187976F6@.microsoft.com...
> Dear Andriew and Mike,
> Thank you both for your responses. It seems to me, from them, that we do
not have an immediate need to re-partition our hard drive.
> I did wonder what you had in mind Andrew, though, when you said:
> "Having multiple logical partitions is more of a hindrance than a help in
that case."
> Pardon my naivete. Thanks!
> In Peace,
> K.
>

Hard Drive Space and Disk Caching

Dear friends,
I have what may be a basic question to most folks here. Does SQL manage dis
k caching across partitions or hard drives for greatest efficiency?
Basically I have a small C drive partition reserved for basic operating syst
ems stuff. All program and data files, including SQL and databases are on t
he much larger D and E drives. Would there ever be an issue of the C drive
not being large enough and
causing SQL to slow down or not perform as well?
Someone told me that all disk caching happens on the C drive, which doesn't
make sense to me. It seems to me that SQL would be smart enough to use othe
r physical drive resources as needed. They are encouraging us to use Partit
ion Magic to expand the siz
e of our C drive (which currently has about 1 Gig available, to be expanded
to between 5 and 10 Gig).
Does this seem like good advice?I do not understand what they are saying - by definition caching is =
holding data locally for fast access, SQL Server holds data in memory =
having read it from disc. What drive letter is utterly irrelevant to it =
(and from a data management perspective the whole concept of drive =
letters is horrid!)=20
SQL Server caching is fine across any drive letter - it cares little =
about such things. I have one system where C is getting really tight =
(due to successive service packs and fixes) but all the SQL server =
stuff, pagefile etc is elsewhere and we have noticed no performance =
problems.
Mike John
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message =
news:4376A82B-AC21-463C-BCDD-B124FE980C98@.microsoft.com...
> Dear friends,
>=20
> I have what may be a basic question to most folks here. Does SQL =
manage disk caching across partitions or hard drives for greatest =
efficiency?
>=20
> Basically I have a small C drive partition reserved for basic =
operating systems stuff. All program and data files, including SQL and =
databases are on the much larger D and E drives. Would there ever be an =
issue of the C drive not being large enough and causing SQL to slow down =
or not perform as well?
>=20
> Someone told me that all disk caching happens on the C drive, which =
doesn't make sense to me. It seems to me that SQL would be smart enough =
to use other physical drive resources as needed. They are encouraging =
us to use Partition Magic to expand the size of our C drive (which =
currently has about 1 Gig available, to be expanded to between 5 and 10 =
Gig).
>=20
> Does this seem like good advice?|||Kevin,
First off sql servers cache is a memory based cache not a disk one. The
cache your thinking of is the OS cache or virtual memory. A properly tuned
Sql Server will not use this cache at all. And yes it is usually on the c:
drive. Second it sounds like your drives are all logical drives on the same
physical hard drive. If this is the case it doesn't matter since it is all
the same physical drive. Having multiple logical partitions is more of a
hindrance than a help in that case.
Andrew J. Kelly
SQL Server MVP
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:4376A82B-AC21-463C-BCDD-B124FE980C98@.microsoft.com...
> Dear friends,
> I have what may be a basic question to most folks here. Does SQL manage
disk caching across partitions or hard drives for greatest efficiency?
> Basically I have a small C drive partition reserved for basic operating
systems stuff. All program and data files, including SQL and databases are
on the much larger D and E drives. Would there ever be an issue of the C
drive not being large enough and causing SQL to slow down or not perform as
well?
> Someone told me that all disk caching happens on the C drive, which
doesn't make sense to me. It seems to me that SQL would be smart enough to
use other physical drive resources as needed. They are encouraging us to
use Partition Magic to expand the size of our C drive (which currently has
about 1 Gig available, to be expanded to between 5 and 10 Gig).
> Does this seem like good advice?|||Dear Andriew and Mike,
Thank you both for your responses. It seems to me, from them, that we do no
t have an immediate need to re-partition our hard drive.
I did wonder what you had in mind Andrew, though, when you said:
"Having multiple logical partitions is more of a hindrance than a help in th
at case."
Pardon my naivete. Thanks!
In Peace,
K.|||Two things that I see often. One is that by having several logical drives
it tends to give the impression there are multiple physical drives. For
someone not familiar with the hardware they may make assumptions that are
wrong based on this. The other is that by having multiple logical
partitions you effectively limit how much space you have in each. Where as
if it was all one logical partition you don't have to worry about running
out of room until you hit the disk limit, not the partition limit. Hope
that helps.
Andrew J. Kelly
SQL Server MVP
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:41D04118-B9C2-45BD-8D81-1179187976F6@.microsoft.com...
> Dear Andriew and Mike,
> Thank you both for your responses. It seems to me, from them, that we do
not have an immediate need to re-partition our hard drive.
> I did wonder what you had in mind Andrew, though, when you said:
> "Having multiple logical partitions is more of a hindrance than a help in
that case."
> Pardon my naivete. Thanks!
> In Peace,
> K.
>

Handling Null fields with three-tier architecture

I using Visual Web Designer Express (with Visual Basic), with a SQL Server 2000 database. I have a prototype application running satisfactorily using code that builds queries, but I'm now trying to rebuild the application "properly" using three-tier architecture. I have been following the principles of Scott Mitchell's tutorials. I have created an database .xsd with a table adaptor, and replaced the rather grotty query-building code in the business layer with better code referencing the table adaptor. Thus where the first version had code: -

Dim queryString As String = "SELECT * FROM NZGDB_User WHERE USRid = '" & Userid & "'"
Dim message As String = ""
Dim Found As Boolean = False
Try
Using connection As New SqlConnection(GDB_AppSettings.Connection)
Dim command As New SqlCommand(queryString, connection)
connection.Open()

Dim reader As SqlDataReader = command.ExecuteReader()

If reader.Read() Then
Found = True
_USRid = reader("USRid")
_USRPassword = reader("USRPassword")
_USREmail = reader("USREmail")
_USRTitle = reader("USRTitle")
_USRName = reader("USRName")
_USRRole = reader("USRRole")
If IsDBNull(reader("USRAgreedConditions")) = False Then
_USRAgreedConditions = reader("USRAgreedConditions")
End If
End If
reader.Close()
End Using
Catch ex As Exception
If Left(Err.Description, 68) = "An error has occurred while establishing a connection to the server." Then
Return "Cannot open database to logon"
Else
Return Err.Description
End If
End Try

the new version is much more elegant: -

Dim taUser As New NZGDBTableAdapters.NZGDB_UserTableAdapter()

Dim tbUser As NZGDB.NZGDB_UserDataTable = taUser.GetUserByUserid(userid)
If tbUser.Count <> 1 Then ' Anything other than 0 or 1 should be impossible
Return "User not found"
End If

Dim trUser As NZGDB.NZGDB_UserRow = tbUser(0)
_USRid = userid
_USRPassword = password
_USREmail = trUser.USREmail
_USRTitle = trUser.USRTitle
_USRName = trUser.USRName
_USRRole = trUser.USRRole
_USRAgreedConditions = trUser.USRAgreedConditions

However, there is a problem. The database field USRAgreedConditions is a Datetime field that can be null. The new version works perfectly when it is NOT null, but throws an exception: -

System.Data.StrongTypingException was unhandled by user code
Message="The value for column 'USRAgreedConditions' in table 'NZGDB_User' is DBNull."
Source="App_Code.wt2vzoc1"
...

There is no point in writing: -
If Not IsDBNull(trUser.USRAgreedConditions) Then
_USRAgreedConditions = trUser.USRAgreedConditions
End If
because the exception occurs within the automatically-created code in the data access layer. I tried changing the Nullvalue property of the field USRAgreedConditions in the table adaptor, but the only valid option is (Throw Exception) unless the field is a String. Of course USRAgreedConditions is a Datetime field, so I can't change the property.

It seems that my only options are: -
1. To stick with the old query-building code. But this doesn't give me the advantages of a proper 3-tier architecture
2. To change the generated code in wt2vzoc. This MUST be a bad idea - surely I should leave this code untouched. Besides, what if the table adaptor has to be regenerated when I change the table design?
3. Code a Try block within the business layer: -
Try
_USRAgreedConditions = trUser.USRAgreedConditions
Catch exAs Exception
_USRAgreedConditions =Nothing
EndTry

This seems to work OK, but seems less elegant than the original code in the old version: -
If IsDBNull(reader("USRAgreedConditions")) = False Then
_USRAgreedConditions = reader("USRAgreedConditions")
End If

Is there a better way?

Hi Robert my friend,

There is a better way. Change your query to list all of the fields within the table that you want and set the USRAgreedConditions field to a specific valid date if it is NULL via the ISNULL() function. The SQL below will return today's date in this field where it is NULL but you can change it to use any valid date value that you wish.

SELECT USRid, USRPassword, URSEmail, USRTitle, USRName, USRRole,

ISNULL(USRAgreedConditions, GetDate()) AS USRAgreedConditions

FROM NZGDB_User

For this reason, it is not good practice to rely solely on date fields for business logic. If this field being null indicates that the conditions have not been agreed yet, it would be better to have an additional BIT field that indicates Yes or No and then the USRAgreedConditions field would simply be a field containing the date information for those that are set to 1 (Yes).

Kind regards

Scotty

|||

Thanks Scotty, that's certainly a neater solution, and I'll progressively use it. Unfortunately my code is now riddled with Try/End try blocks to handle Null fields, so it will take some time to get rid of them all.

Your solution will certainly work in most, perhaps all, of my situations. However I wonder if there isn't a lurking problem. The point of NULL is to distinguish "No value" from "Default value". Sometimes you need to make the distinction in your code, and there is a risk that whatever value you choose for VALUE in
ISNULL(USRAgreedConditions, VALUE) AS USRAgreedConditions
will coincide with a real value. Getdate() won't do, and neither will 0 (which becomes 1/1/1900) as either could be a valid date in this application. In this case I can use Getdate()+20 and test for a future date (which is otherwise logically impossible), but I have to be careful to remember to check that my logic doesn't write these artificial values back to the database. I tried using
DIM USRAgreeConditions as NULLABLE (OF Datetime)
but found myself getting into a lot of trouble with errors popping up elsewhere.

It's a pity that the designers of the new NULLABLE feature didn't make the handling of all potentially-null fields consistent. Thus I find that there is little trouble with String fields (CHAR, VARCHAR, etc in the database), as I can simply set the column's Nullvalue property to Empty. It's just silly that I can't do this for GUID and Date (and any other type) fields. There are also some idiocies in the way that operator usage changes with NULLABLE fields. Enough raving!

Thanks for your help.

Regards, Robert

|||

Hi Robert,

You can also try this within the query. In addition to generating a valid date for your column, have another column that inidcates whether or not it was generated so that you can check this extra column in the code to determine how to process the date field: -

SELECT ISNULL(USRAgreedConditions, GetDate()) AS USRAgreedConditions,
CASE WHEN USRAgreedConditions IS NULL THEN 'Yes' ELSE '' END AS IsNullDate
FROM TableName

Kind regards

Scotty

|||

Thanks Scotty, yes that's exactly what I want. Something like this (returning the default value of Nothing and an indicator of whether it's a real null or not is what Microsoft should be doing automatically: in the meantime I guess this is the best we can do.

Thanks again, Robert