Disk or network error

Hi

I have written an application in VB through which I can convert DBASE
IV database to MS SQL Server database. Also, the application provides
database manipulation features to the user like addition, deletion etc
of records.
I am using OPENROWSET for conversion purposes. Also the server is MS
SQL Server 2000.
Now this application workks fine when I have installed the instance of
MS SQL Server on my local machine. It converts the database and stores
it on the server perfectly and there are no isues.
However, when I try to use the application to connect with MS SQL
Server on another machine, there are a few problems. I connect to the
other MS SQL Server on other machine through named pipes. My
application successfully accesses the database and allows user to
modify it according to his needs.
For this I created the user account of that user on the machine.
But when I try to convert the database, I get the following error
message:

[Microsoft ODBC Dbase driver]Disk or Network Error

Here is the line in VB code which is causing all the trouble:

con.Execute "select * into " & UCase(X) & " from
OPENROWSET('MSDASQL','Driver={Microsoft dBASE Driver
(*.dbf)};DEFAULTDIR=" & BaseData & ";SourceType=DBF','select * from " &
UCase(X) & "')"

Here, X is the dbf file name, BaseData is the path name of file.
I gave the user full rights but i am still getting this error message.
Now, what am I doing wrong here?

Regards,
ShwetabhHi

You don't say of you can open the DBASE file from DBASE on the remote
machine? Are you using a mapped drive or UNC name? Have you checked that the
permissions are correct for the SQL Server service account?

John
"Shwetabh" <shwetabhgoel@.gmail.com> wrote in message
news:1144529567.672831.5960@.v46g2000cwv.googlegrou ps.com...
> Hi
> I have written an application in VB through which I can convert DBASE
> IV database to MS SQL Server database. Also, the application provides
> database manipulation features to the user like addition, deletion etc
> of records.
> I am using OPENROWSET for conversion purposes. Also the server is MS
> SQL Server 2000.
> Now this application workks fine when I have installed the instance of
> MS SQL Server on my local machine. It converts the database and stores
> it on the server perfectly and there are no isues.
> However, when I try to use the application to connect with MS SQL
> Server on another machine, there are a few problems. I connect to the
> other MS SQL Server on other machine through named pipes. My
> application successfully accesses the database and allows user to
> modify it according to his needs.
> For this I created the user account of that user on the machine.
> But when I try to convert the database, I get the following error
> message:
> [Microsoft ODBC Dbase driver]Disk or Network Error
> Here is the line in VB code which is causing all the trouble:
> con.Execute "select * into " & UCase(X) & " from
> OPENROWSET('MSDASQL','Driver={Microsoft dBASE Driver
> (*.dbf)};DEFAULTDIR=" & BaseData & ";SourceType=DBF','select * from " &
> UCase(X) & "')"
> Here, X is the dbf file name, BaseData is the path name of file.
> I gave the user full rights but i am still getting this error message.
> Now, what am I doing wrong here?
> Regards,
> Shwetabh|||Hi,

Actually, the DBASE files are stored in a seperate folder on the remote
machine (remote in the sense that though it is in the intranet, SQL
server 2000 isnt installed on it). I have made the drive shared. Also,
in my effort to get it to work, I gave the user all rights on the SQL
server. But it still doesnt work.
And yes, I am using UNC name.

Regards,
Shwetabh|||Hi Shwetabh

If you service account is a domain account then log onto the SQL Server
machine as this account and see if you can access the DBASE files.

John

Shwetabh wrote:
> Hi,
> Actually, the DBASE files are stored in a seperate folder on the remote
> machine (remote in the sense that though it is in the intranet, SQL
> server 2000 isnt installed on it). I have made the drive shared. Also,
> in my effort to get it to work, I gave the user all rights on the SQL
> server. But it still doesnt work.
> And yes, I am using UNC name.
> Regards,
> Shwetabh|||Hi,
Sorry for taking so long to reply.
Actually, I can access the database from my application.
But I am unable to import my DBASE database to sql server.
when I try to do so, using OPENROWSET I get the error message.
Also, since the end product will be used by many other people,
their individual accounts will be created, I dont think using domain
account will be a good idea.

Regards,
Shwetabh|||Hi

To access network resources you will need a domain account with the
appropriate privileges otherwise you will not be able to connect.

John
"Shwetabh" <shwetabhgoel@.gmail.com> wrote in message
news:1145602062.263512.306870@.e56g2000cwe.googlegr oups.com...
> Hi,
> Sorry for taking so long to reply.
> Actually, I can access the database from my application.
> But I am unable to import my DBASE database to sql server.
> when I try to do so, using OPENROWSET I get the error message.
> Also, since the end product will be used by many other people,
> their individual accounts will be created, I dont think using domain
> account will be a good idea.
> Regards,
> Shwetabh|||Hi,

Lets consider this,
I have the DBASE files on the local machine.
Now I want to convert them and store them in MS SQL
Server.
Now for doing this, I use OPENROWSET.
I can access the already converted database (which I converted
manually on the server for testing purpose). I can read, write, delete
that data. I get no errors when I do that.
Now when I try to convert database from DBF (stored on local disk)
to SQL using OPENROWSET, I get the error: Disk or Network Error.

How should I resolve this?

Shwetabh|||Shwetabh (shwetabhgoel@.gmail.com) writes:
> Sorry for taking so long to reply.
> Actually, I can access the database from my application.
> But I am unable to import my DBASE database to sql server.
> when I try to do so, using OPENROWSET I get the error message.
> Also, since the end product will be used by many other people,
> their individual accounts will be created, I dont think using domain
> account will be a good idea.

When John talked about domain account, he referred to the account
under which SQL Server itself is running. To access resources on
the network, the account should be a domain account. Running SQL
Server under a domain account is a very common thing to do, even
in multi-user enviroments.

To review the service accont for SQL Server, right-click My Computer
on the server, select Manage. Find Services, and in the list of
services, find MSSQL Server. Double-click, and go the log on tab.
Make sure that the user there has access to the remote drive
where the DBASE files are located.

Or move the DBASE files to a local disk on the server.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi,

I tried to access the database stored on a machine running Windows 2000
+ MS SQL Server 2000 and it worked fine.
Infact, it worked fine when used by any machine which would connect to
Windows 2000 server.
I was easily able to import the data on servers running Windows 2k.
But I am getting this error by connecting onto the servers running
Windows 2003.

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC dBase Driver] The
Microsoft Jet database engine could not find the object 'ACE'. Make
sure the object exists and that you spell its name and the path name
correctly.]

How can I resolve this problem?

Regards,
Shwetabh|||Hi,

I tried to access the database stored on a machine running Windows 2000
+ MS SQL Server 2000 and it worked fine.
Infact, it worked fine when used by any machine which would connect to
Windows 2000 server.
I was easily able to import the data on servers running Windows 2k.
But I am getting this error by connecting onto the servers running
Windows 2003.

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC dBase Driver] The
Microsoft Jet database engine could not find the object 'ACE'. Make
sure the object exists and that you spell its name and the path name
correctly.]

Windows 2003 server is also running MS SQL Server 2000. But I cant get
it to work.
How can I resolve this problem?

Regards,
Shwetabh|||Shwetabh (shwetabhgoel@.gmail.com) writes:
> I tried to access the database stored on a machine running Windows 2000
> + MS SQL Server 2000 and it worked fine.
> Infact, it worked fine when used by any machine which would connect to
> Windows 2000 server.
> I was easily able to import the data on servers running Windows 2k.
> But I am getting this error by connecting onto the servers running
> Windows 2003.
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDASQL' reported an error.
> [OLE/DB provider returned message: [Microsoft][ODBC dBase Driver] The
> Microsoft Jet database engine could not find the object 'ACE'. Make
> sure the object exists and that you spell its name and the path name
> correctly.]
> How can I resolve this problem?

Not that I am sure that I can help anyway, but it would definitely help if
you can give full details, both when it is working and when it is not.

1) Where is the dBase file located in relation to the SQL Server? Local
disk or an a network share?

2) Under what account is SQL Server running?

3) Do you get the error when you run under any account on SQL Server,
even as administrator?

In the end this may have more to do with Windows or Active Directory that
I know very little about.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Disk Fragmentation on W2K Server/SQL Server 2000

Hi, I'm hoping one of you SQL gurus out there will help me with a debate bet
ween myself, a simple Network Admin. and my colleague, an SQL DBA. The W2K/
SQL 2000 box is having performance problems so I was asked to check on it.
Now, in most environments I
have worked in, I took care of the physical server and OS and the DBA/Develo
per types did their own thing. In my current work environment, everyone has
been accustomed to taking care of their own boxes, the DBA included, so the
re are some issues with me
doing my job at all. Anyway, I did a performance analysis of the OS/Hardwar
e and configuration. I also researched the Best Practices for SQL 2000 Serve
r and after a week of collecting metrics, gave the information to the DBA.
There did not appear to be
any disk, memory, paging etc. problems but the one thing that really stood o
ut to me was that the disks were 49% fragmented and the files on those disks
93 and 99% fragmented. Primarily database and log files. Again, I just pr
ovided the info as I do no
t want to step on the DBAs toes. The DBA however, reacted defensively and s
aid that she has *never* defragged an SQL server and in searching through MS
DN, found *no* evidence that there is ever a need to defrag one. Further, t
hat if SQL server could now
find the files in their fragmented state, why was there a problem? If the d
rives were defragged, how would SQL server be able to find the files? She w
as serious. Now, I'm being frowned on as not knowing what I'm talking about.
I'm the newest member of
the team, but not a newbie in the field. I have worked with Microsoft produc
ts now for many years and I know that every Windows box PC or Server needs t
o be defragged and occasionally rebooted as a part of routine maintenace. T
here needs to be a mainten
ance windows for doing so. Having a 99.9% uptime record will not matter when
those drives grind to a halt. What I keep searching for is some reference
from Microsoft or other known expert or publication that will state this as
it pertains to SQL server.
Most of the SQL manuals leave out any OS administration information. Anyone
have any suggestions or comments? Microsoft, are you out there? Please He
lp! TIAmy two cents:
Databases if set up as auto grow and auto shrink, will definitely fragment
disk drives over time. Defragmentation the disks will help improve
performance.
Now, inside a database, there is a page split problem that causes
fragmentation too, It's the DBA's job to reindex tables on a regular basis
to fix index fragmentation. I think you should first get an idea of how the
DBA maintains the index defragmentation, then work on the physical disk
defragmentation. Also there are a lot of other factors affecting database
performance.
Richard
"cdub" <cdub@.newsguy.com> wrote in message
news:29C67933-B2CB-43F3-A498-E8EABD2DB936@.microsoft.com...
> Hi, I'm hoping one of you SQL gurus out there will help me with a debate
between myself, a simple Network Admin. and my colleague, an SQL DBA. The
W2K/SQL 2000 box is having performance problems so I was asked to check on
it. Now, in most environments I have worked in, I took care of the physical
server and OS and the DBA/Developer types did their own thing. In my
current work environment, everyone has been accustomed to taking care of
their own boxes, the DBA included, so there are some issues with me doing my
job at all. Anyway, I did a performance analysis of the OS/Hardware and
configuration. I also researched the Best Practices for SQL 2000 Server and
after a week of collecting metrics, gave the information to the DBA. There
did not appear to be any disk, memory, paging etc. problems but the one
thing that really stood out to me was that the disks were 49% fragmented and
the files on those disks 93 and 99% fragmented. Primarily database and log
files. Again, I just provided the info as I do not want to step on the DBAs
toes. The DBA however, reacted defensively and said that she has *never*
defragged an SQL server and in searching through MSDN, found *no* evidence
that there is ever a need to defrag one. Further, that if SQL server could
now find the files in their fragmented state, why was there a problem? If
the drives were defragged, how would SQL server be able to find the files?
She was serious. Now, I'm being frowned on as not knowing what I'm talking
about. I'm the newest member of the team, but not a newbie in the field. I
have worked with Microsoft products now for many years and I know that every
Windows box PC or Server needs to be defragged and occasionally rebooted as
a part of routine maintenace. There needs to be a maintenance windows for
doing so. Having a 99.9% uptime record will not matter when those drives
grind to a halt. What I keep searching for is some reference from Microsoft
or other known expert or publication that will state this as it pertains to
SQL server. Most of the SQL manuals leave out any OS administration
information. Anyone have any suggestions or comments? Microsoft, are you
out there? Please Help! TIA|||I agree with Richard. I've seen SQL performance affected by fragmentation
and auto-grow. The best way to handle it is usually to plan the size of the
database you need, defrag first, and then allocate the space that you need.
Christian Smith
"Richard Ding" <dingr@.cleanharbors.com> wrote in message
news:uEp2zCo8DHA.2656@.TK2MSFTNGP11.phx.gbl...
> my two cents:
> Databases if set up as auto grow and auto shrink, will definitely fragment
> disk drives over time. Defragmentation the disks will help improve
> performance.
> Now, inside a database, there is a page split problem that causes
> fragmentation too, It's the DBA's job to reindex tables on a regular basis
> to fix index fragmentation. I think you should first get an idea of how
the
> DBA maintains the index defragmentation, then work on the physical disk
> defragmentation. Also there are a lot of other factors affecting database
> performance.
>
> Richard
> "cdub" <cdub@.newsguy.com> wrote in message
> news:29C67933-B2CB-43F3-A498-E8EABD2DB936@.microsoft.com...
> between myself, a simple Network Admin. and my colleague, an SQL DBA. The
> W2K/SQL 2000 box is having performance problems so I was asked to check on
> it. Now, in most environments I have worked in, I took care of the
physical
> server and OS and the DBA/Developer types did their own thing. In my
> current work environment, everyone has been accustomed to taking care of
> their own boxes, the DBA included, so there are some issues with me doing
my
> job at all. Anyway, I did a performance analysis of the OS/Hardware and
> configuration. I also researched the Best Practices for SQL 2000 Server
and
> after a week of collecting metrics, gave the information to the DBA.
There
> did not appear to be any disk, memory, paging etc. problems but the one
> thing that really stood out to me was that the disks were 49% fragmented
and
> the files on those disks 93 and 99% fragmented. Primarily database and
log
> files. Again, I just provided the info as I do not want to step on the
DBAs
> toes. The DBA however, reacted defensively and said that she has *never*
> defragged an SQL server and in searching through MSDN, found *no* evidence
> that there is ever a need to defrag one. Further, that if SQL server
could
> now find the files in their fragmented state, why was there a problem? If
> the drives were defragged, how would SQL server be able to find the files?
> She was serious. Now, I'm being frowned on as not knowing what I'm talking
> about. I'm the newest member of the team, but not a newbie in the field.
I
> have worked with Microsoft products now for many years and I know that
every
> Windows box PC or Server needs to be defragged and occasionally rebooted
as
> a part of routine maintenace. There needs to be a maintenance windows for
> doing so. Having a 99.9% uptime record will not matter when those drives
> grind to a halt. What I keep searching for is some reference from
Microsoft
> or other known expert or publication that will state this as it pertains
to
> SQL server. Most of the SQL manuals leave out any OS administration
> information. Anyone have any suggestions or comments? Microsoft, are you
> out there? Please Help! TIA
>sql

Disconnected replication

This is the scenario.
We have one or more laptops that might never be connected to a network. We want to be able to save the data from the server to a USB drive, take the USB drive to a laptop and import the data. We also need to be able to send the data from the laptop to the server the same way (using USB drives) since the data on the laptop have been update/changed or deleted.

Is this even possible, and how do we do it using replication (if possible)?
If it si not possible, what other options do we have?

GeorgeUsually replication works between servers. In you case try to use DTS.|||Originally posted by snail
Usually replication works between servers. In you case try to use DTS.
Would DTS be able to Merge the data between the 2 servers? If so, how do I do it?|||Originally posted by gehrlekrona
Would DTS be able to Merge the data between the 2 servers? If so, how do I do it?

You cold import (export) data from USB drive and merge data by using special stored procedure - it depens on logic of moving your data.

Disaster/recovery techniques

I have recently installed SQL Server 2000 on a newly acquired Windows
2003 server. The network admins also got Brightstor Agent for SQL
Server to interface with Brightstor ARCserve Backup for Windows
running on our backup server. I think I have a pretty good handle on
how to setup the SQL Server backups using a combination of
full/differential/transaction log backups to handle any recovery
required for the SQL Server databases.
What are the alternatives for backing up the Server itself for use in
recovery? Two approaches come to mind.
The first is not worry about backing up the server, in which case for
a recovery, I would have to reinstall all the software and service
packs to rebuild the environment. This would certainly require that I
have fully documented what applications (Windows 2003 server, SQL
Server, Outlook, Brightstor Agent, etc) and the install options that
were chosen. The rebuild process for this could take some time, and
there may be some issues that I haven't considered.
The second is to fully backup the server including the registry. If
everything were installed to the "C:\" drive, I could maybe get by
with just backing up the "C:\" drive and the registry. I do have a
weekend window where I could down the server to perform this task on a
weekly basis. Then for recovery I could just install an operating
system, restore from the backup of the "C:\" drive and registry and
proceed to my SQL Server recovery. Are there any problems with this
approach?
Finally, are there other techniques that I should consider? Any
websites or newsgroup threads on this topic would be greatly
appreciated.
Thanks Much, Lane LesperanceI prefere the first approach. it's the databases you should back up, not the
server.
Part of a good recovery plan is to document everything, so you can put the
server back to its original configuration before applying your backup files.
you can use the 2nd one, but to me it will take longer, and you need to
really test it on a test box to make sure you can recover your data that
way.
Steve Lin
MCDBA
"Lane Lesperance" <llesperance@.cooperpower.com> wrote in message
news:66feb5c.0404051118.e0472bf@.posting.google.com...
> I have recently installed SQL Server 2000 on a newly acquired Windows
> 2003 server. The network admins also got Brightstor Agent for SQL
> Server to interface with Brightstor ARCserve Backup for Windows
> running on our backup server. I think I have a pretty good handle on
> how to setup the SQL Server backups using a combination of
> full/differential/transaction log backups to handle any recovery
> required for the SQL Server databases.
> What are the alternatives for backing up the Server itself for use in
> recovery? Two approaches come to mind.
> The first is not worry about backing up the server, in which case for
> a recovery, I would have to reinstall all the software and service
> packs to rebuild the environment. This would certainly require that I
> have fully documented what applications (Windows 2003 server, SQL
> Server, Outlook, Brightstor Agent, etc) and the install options that
> were chosen. The rebuild process for this could take some time, and
> there may be some issues that I haven't considered.
> The second is to fully backup the server including the registry. If
> everything were installed to the "C:\" drive, I could maybe get by
> with just backing up the "C:\" drive and the registry. I do have a
> weekend window where I could down the server to perform this task on a
> weekly basis. Then for recovery I could just install an operating
> system, restore from the backup of the "C:\" drive and registry and
> proceed to my SQL Server recovery. Are there any problems with this
> approach?
> Finally, are there other techniques that I should consider? Any
> websites or newsgroup threads on this topic would be greatly
> appreciated.
> Thanks Much, Lane Lesperance

Disaster Recovery plan for SQL server

Hi,
We plan to implement Disaster Recovery plan and system
for SQL 2000 server and databases. SQL server is
connected in headquarter network. Recovery SQL server
will stay on remote location (site) and it must have the
most recent copy of SQL production databases.
SQL server has 2 databases. Each database has about 2-3
GB of data. We can expect that database will grow
significantly in the future (10 Gb and more). Databases
accept about 2 MB of data per day. Connection between
headquarter and remote location will probably be on high-
speed WAN (10 Mb/s or more). At any disaster event, we
can loose data for about last 1 hour (maximum 1 hour, not
more anyway)
What is the best solution for implementing Disaster
Recovery?
Backup, restore with STANDBY server on remote location
(with frequently created transaction log backups)?
Or SQL database replication (transaction replication)?
Thanks in advance for help
MilanDefinitely Standby Server with Log Shipping.
Theres lots of info in SQL Books Online and it is offered as a feature with
SQL Enterprise Edition.
--
Regards,
Mandar Naik
This posting is provided AS IS with no warranties, and confers no rights.
"Milan Ojstersek" <milan.ojstersek@.hermes-plus.si> wrote in message
news:087a01c33f07$3e84d700$a301280a@.phx.gbl...
> Hi,
> We plan to implement Disaster Recovery plan and system
> for SQL 2000 server and databases. SQL server is
> connected in headquarter network. Recovery SQL server
> will stay on remote location (site) and it must have the
> most recent copy of SQL production databases.
> SQL server has 2 databases. Each database has about 2-3
> GB of data. We can expect that database will grow
> significantly in the future (10 Gb and more). Databases
> accept about 2 MB of data per day. Connection between
> headquarter and remote location will probably be on high-
> speed WAN (10 Mb/s or more). At any disaster event, we
> can loose data for about last 1 hour (maximum 1 hour, not
> more anyway)
> What is the best solution for implementing Disaster
> Recovery?
> Backup, restore with STANDBY server on remote location
> (with frequently created transaction log backups)?
> Or SQL database replication (transaction replication)?
> Thanks in advance for help
> Milan
>|||I prefer Log Shipping.
"Milan Ojstersek" <milan.ojstersek@.hermes-plus.si> wrote in message
news:087a01c33f07$3e84d700$a301280a@.phx.gbl...
> Hi,
> We plan to implement Disaster Recovery plan and system
> for SQL 2000 server and databases. SQL server is
> connected in headquarter network. Recovery SQL server
> will stay on remote location (site) and it must have the
> most recent copy of SQL production databases.
> SQL server has 2 databases. Each database has about 2-3
> GB of data. We can expect that database will grow
> significantly in the future (10 Gb and more). Databases
> accept about 2 MB of data per day. Connection between
> headquarter and remote location will probably be on high-
> speed WAN (10 Mb/s or more). At any disaster event, we
> can loose data for about last 1 hour (maximum 1 hour, not
> more anyway)
> What is the best solution for implementing Disaster
> Recovery?
> Backup, restore with STANDBY server on remote location
> (with frequently created transaction log backups)?
> Or SQL database replication (transaction replication)?
> Thanks in advance for help
> Milan
>

disagreement over PK "naming convention"

I'm working with a DBA that wants to have the PK for a personnel table be a
uer's network ID. I asked him what happens when the network ID changes, and
(even though there are tables that use it as a FK) he didn't seem to think
it was going to be a problem
Now the way I was raised as a DBA, you just didn't do this - PK's are
usually int identity, and never meaningful names... but what do I know?
Before I disagree more assertively with him, could there be a legitimate
reason, a different line of thought I've missed, regarding database design?
PaulPrimary key names (like table names) must be unique within the database.
Therefore, it makes sense to name the key something like PK_<table name>.
"PJ6" <nobody@.nowhere.net> wrote in message
news:%232WoT3jrFHA.3424@.TK2MSFTNGP14.phx.gbl...
> I'm working with a DBA that wants to have the PK for a personnel table be
> a uer's network ID. I asked him what happens when the network ID changes,
> and (even though there are tables that use it as a FK) he didn't seem to
> think it was going to be a problem
> Now the way I was raised as a DBA, you just didn't do this - PK's are
> usually int identity, and never meaningful names... but what do I know?
> Before I disagree more assertively with him, could there be a legitimate
> reason, a different line of thought I've missed, regarding database
> design?
> Paul
>|||PJ6 wrote:
> I'm working with a DBA that wants to have the PK for a personnel
> table be a uer's network ID. I asked him what happens when the
> network ID changes, and (even though there are tables that use it as
> a FK) he didn't seem to think it was going to be a problem
> Now the way I was raised as a DBA, you just didn't do this - PK's are
> usually int identity, and never meaningful names... but what do I
> know? Before I disagree more assertively with him, could there be a
> legitimate reason, a different line of thought I've missed, regarding
> database design?
>
Oh no! You don't know what you've started!
It's an endless debate without resolution:
http://groups.google.com/groups?q=n...:en&sa=N&tab=wg
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:eFucZ7jrFHA.3836@.TK2MSFTNGP12.phx.gbl...
> PJ6 wrote:
> Oh no! You don't know what you've started!
> It's an endless debate without resolution:
> http://groups.google.com/groups?q=n...:en&sa=N&tab=wg
>
But it's fair to say that there is not a consensus that would make your DBA
friend "wrong". I think that everyone would agree that primary keys should
be updated only rarely, and that doing so can be a hastle. However SQL
Server does support ON UPDATE CASCADE foreign key relationships for exactly
this scenario.
If you have a need to track users across changes in their NetworkID then you
have a valid _modeling_ argument to present against using NetworkID as the
PK. I would steer clear of the natural/surrogate key debate, however.
David|||You would expect each personnel's UserID to be unique at any given
moment in time, however, network IDs certainly do change on occasion,
especially if you have a merger between two previously unrelated
corporations or organizations. The keys used to enforce referential
integrity must be consistent and reliable across time, but human derived
coding schemes are frequently not.
I once worked as a contractor for an organization whose email and
network ids were based on first initial / last name. The company acquired a
new executive vice president who was much chagrined that an existing
employee, who had the same first initial/last name, was getting his email
sent from people who didn't bother to lookup the appropriate address. So, he
arm twisted the network admin into re-assinging email IDs to suit his own
personal need, and in the process they totally screwed Exchange server to
the point where the ID had to be deactivated alltogether.
That's the benefit of using a system generated identity value for the
primary key. Let the world plod along as it may, but at least your records
will always be intact.
"PJ6" <nobody@.nowhere.net> wrote in message
news:%232WoT3jrFHA.3424@.TK2MSFTNGP14.phx.gbl...
> I'm working with a DBA that wants to have the PK for a personnel table be
> a uer's network ID. I asked him what happens when the network ID changes,
> and (even though there are tables that use it as a FK) he didn't seem to
> think it was going to be a problem
> Now the way I was raised as a DBA, you just didn't do this - PK's are
> usually int identity, and never meaningful names... but what do I know?
> Before I disagree more assertively with him, could there be a legitimate
> reason, a different line of thought I've missed, regarding database
> design?
> Paul
>|||It′s a religious war :-D
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Bob Barrows [MVP]" wrote:

> PJ6 wrote:
> Oh no! You don't know what you've started!
> It's an endless debate without resolution:
> http://groups.google.com/groups?q=n...:en&sa=N&tab=wg
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>|||Without entering the natural/surrogate keys debate (personally, I stopped
using natural keys and double fields keys many years and since then, more
than half of my problems with databases have disappeared); the use of ON
UPDATE CASCADE foreign key relationships are often useless when your are
dealing with multiple databases on multiple servers or with printed reports.
When you see someone entering your office with a set of reports and bills 6
months old from a (big) client and say that it's now the time to correct
some kind of error(s) and give him his refund, you might be in a big trouble
if you have used natural keys that has changed since then, for exemple
because of a mislabelling or a key-punch error or whatever else.
The use of ON UPDATE CASCADE foreign key relationships is perfect in some
kinds of scenarios - for exemple for storing temporary identity values for
users on the road - but otherwise, it's a disaster waiting to happen.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:eh%23rRFkrFHA.2604@.TK2MSFTNGP14.phx.gbl...
> "Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
> news:eFucZ7jrFHA.3836@.TK2MSFTNGP12.phx.gbl...
> But it's fair to say that there is not a consensus that would make your
> DBA friend "wrong". I think that everyone would agree that primary keys
> should be updated only rarely, and that doing so can be a hastle. However
> SQL Server does support ON UPDATE CASCADE foreign key relationships for
> exactly this scenario.
> If you have a need to track users across changes in their NetworkID then
> you have a valid _modeling_ argument to present against using NetworkID as
> the PK. I would steer clear of the natural/surrogate key debate, however.
>
> David
>|||Ignore that. I initially though you were asking about how to name the
primary key.
"JT" <someone@.microsoft.com> wrote in message
news:uaBUH7jrFHA.1168@.TK2MSFTNGP11.phx.gbl...
> Primary key names (like table names) must be unique within the database.
> Therefore, it makes sense to name the key something like PK_<table name>.
> "PJ6" <nobody@.nowhere.net> wrote in message
> news:%232WoT3jrFHA.3424@.TK2MSFTNGP14.phx.gbl...
>|||The DBA you're working with is either (1) a neophyte, (2) a complete moron,
(3) short and doesn't care, (4) a plant from a competitor, (5) trying to
ensure his own job security by building a system that only he can maintain,
or (6) one of Joe Celko's students.
You have my sympathy!
"PJ6" <nobody@.nowhere.net> wrote in message
news:#2WoT3jrFHA.3424@.TK2MSFTNGP14.phx.gbl...
> I'm working with a DBA that wants to have the PK for a personnel table be
a
> uer's network ID. I asked him what happens when the network ID changes,
and
> (even though there are tables that use it as a FK) he didn't seem to think
> it was going to be a problem
> Now the way I was raised as a DBA, you just didn't do this - PK's are
> usually int identity, and never meaningful names... but what do I know?
> Before I disagree more assertively with him, could there be a legitimate
> reason, a different line of thought I've missed, regarding database
design?
> Paul
>|||>> Now the way I was raised as a DBA, you just didn't do this - PK's are usu
ally INTEGER identity, and never meaningful names... but what do I know? <<
No, IDENTITY cannot be a key by definition. A key is a subsert of
attributes whose valeus are unique within an entity. You really need
to read some of Dr. Codd's stuff.
If your company issues a network ID with employment to everyone, this
could work. Set up the proper DRI actions (ON UPDATE CASCADE) and the
SQL engine will handle changes.
Besides uniqueness, yoiu also want to have keys that can be verified
and validated. In this case, they are both easy to test -- just ping
the guy to see if the id matches to the person. This is why so many
e-commerce sites use your email as your log on.

DISABLENETWORKPROTOCOLS and named instance

Is it true that I need to enable network protocols to communicate with a
named instance of MSDE SP4 locally?
Installing a named instance with DISABLENETWORKPROTOCOLS=0 seems to prevent
osql connecting to it. I don't want remote access - only local access (so
it would be nice not to load the network protocols). Or have I missed
something?
hi Graham,
Graham Morris wrote:
> Is it true that I need to enable network protocols to communicate
> with a named instance of MSDE SP4 locally?
> Installing a named instance with DISABLENETWORKPROTOCOLS=0 seems to
> prevent osql connecting to it. I don't want remote access - only
> local access (so it would be nice not to load the network protocols).
> Or have I missed something?
you do not need to enable network protocols to connect to local named
instances as all local instances are available to reach via shared memory..
what kind of problem are you experiencing? what is the exception you are
reported with?
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||I start with a Windows XP SP2 system in a workgroup, with nothing installed
except Tweakui (I use VirtualPC to start with the same system each time).
I log in with local administrator privileges.
I make sure all the latest updates are installed from Windows Update.
I navigate to the MSDE SP4 directory and enter 'Setup INSTANCENAME=MYINST
BLANKSAPWD=1'
I reboot so that service MSSQL$MYINST autostarts.
On reboot the service manager shows an empty circle (no 'play' nor 'stop'
symbol). The dropdown does not show my server and instance.
I open a command prompt and enter "osql -S .\MYINST -E" and I get:
[DBNETLIB]SQL Server does not exist or access denied.
[DBNETLIB]ConnectionOpen (Connect()).
I now run C:\Program Files\Microsoft SQL Server\80\Tools\Binn\Svrnetcn.exe,
and add 'Named Pipes'.
The osql command now correctly displays the 1> prompt.
I restart the service manager and it know shows the state of the service.
I find that if I install a blank instance I don't need to and network
libraries. What could be happening?
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3ovjn4F7r0d5U1@.individual.net...
> hi Graham,
> Graham Morris wrote:
> you do not need to enable network protocols to connect to local named
> instances as all local instances are available to reach via shared
> memory..
> what kind of problem are you experiencing? what is the exception you are
> reported with?
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi,
Graham Morris wrote:
> I start with a Windows XP SP2 system in a workgroup, with nothing
> installed except Tweakui (I use VirtualPC to start with the same
> system each time).
> I log in with local administrator privileges.
> I make sure all the latest updates are installed from Windows Update.
> I navigate to the MSDE SP4 directory and enter 'Setup
> INSTANCENAME=MYINST BLANKSAPWD=1'
> I reboot so that service MSSQL$MYINST autostarts.
> On reboot the service manager shows an empty circle (no 'play' nor
> 'stop' symbol). The dropdown does not show my server and instance.
disabling network protocols can cause this as the SQL Server Service Manager
can fail to enlist instances of the like, as reported in
http://support.microsoft.com/default...b;EN-US;814132

> I open a command prompt and enter "osql -S .\MYINST -E" and I get:
> [DBNETLIB]SQL Server does not exist or access denied.
> [DBNETLIB]ConnectionOpen (Connect()).
> I now run C:\Program Files\Microsoft SQL
> Server\80\Tools\Binn\Svrnetcn.exe, and add 'Named Pipes'.
> The osql command now correctly displays the 1> prompt.
> I restart the service manager and it know shows the state of the
> service.
> I find that if I install a blank instance I don't need to and network
> libraries. What could be happening?
the reported exception, as indicated in
http://support.microsoft.com/default...6&Product=sql,
is a general MDAC related problem, probably caused by
http://support.microsoft.com/kb/328383/en-us
I'll try installing a virtual machine may self ...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||hi,
Andrea Montanari wrote:
> hi,
> Graham Morris wrote:
>

> I'll try installing a virtual machine may self ...
just tried... with the same scenario you reported... after installation +
reboot I logged in as local WinXP admin, the SQL Server Service Manager is
"empty", but I can log in via osql with no problem at all...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||I think I've solved this: without network protocols the osql -S parameter
doesn't like the syntax ".\MYINST". Put in the full host name (e. g.
MYHOST\MYINST) and it works!
While you're listening though: this is part of a larger problem: I can't
connect to MSDE SP4 on some domain machines (same problem with osql) until I
remove then replace the network libraries with svrnetcn.exe. This sounds as
though it might be a group policy problem, but I've no idea what. Ring any
bells?
Anyway thanks for the help on the first problem.
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3p0d08F7tp8aU1@.individual.net...
> hi,
> Andrea Montanari wrote:
>
> just tried... with the same scenario you reported... after installation +
> reboot I logged in as local WinXP admin, the SQL Server Service Manager is
> "empty", but I can log in via osql with no problem at all...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Graham
Graham Morris wrote:
> While you're listening though: this is part of a larger problem: I
> can't connect to MSDE SP4 on some domain machines (same problem with
> osql) until I remove then replace the network libraries with
> svrnetcn.exe. This sounds as though it might be a group policy
> problem, but I've no idea what. Ring any bells?
actually not... never heard of such a constraint.. you can have "problems"
connecting if you are not listed in the granted logins list, or if you have
network troubles, but as long as MDAC stack is quiet consistent on remote
clients I never heard of such a problem... I'll keep investigating
thought...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Disable named pipes on W2k/SQL2k cluster?

I have a cluster running on Windows 2000 with SQL 2000. In SQL Server Network Utility I can highlight named pipes but I cannot disable it. Does anyone know why and better yet should this not be done?You can no longer disable Named Pipes on SQL clusters as of SQL SP3. MS PSS has acknowledged this change although they have yet to document it anywhere.



The reasons given for this change:



1) If other protocols failed, they wanted at least one protocol to be available. For example if TCP/IP is enabled yet SQL Server fails to bind on 1433, Named Pipes should still be able to respond.

2) Prior to SQL SP3, if you disabled Named Pipes and re-enabled it you would an encounter an issue where the local machine name would be populated rather than the virtual server name. For example: \\.\pipe\$$\local machine name\sql\query instead of \\.\pipe\$$\virtual server name\sql\query

disable external connections to sql server 2000

Hi,
I am trying to disable external connections to my sql server installation:
removed (disabled) all the network protocols, restarted the server machine,
but I can still run EM and connect to that sql server. What am I missing to
disable the external connections?
Thank you,
VadimHi
Did you remove the client protocols instead of the server ones? What machine
are you connecting from?
John
"Vadim" wrote:

> Hi,
> I am trying to disable external connections to my sql server installation:
> removed (disabled) all the network protocols, restarted the server machine
,
> but I can still run EM and connect to that sql server. What am I missing t
o
> disable the external connections?
> Thank you,
> Vadim
>
>

disable external connections to sql server 2000

Hi,
I am trying to disable external connections to my sql server installation:
removed (disabled) all the network protocols, restarted the server machine,
but I can still run EM and connect to that sql server. What am I missing to
disable the external connections?
Thank you,
Vadim
Hi
Did you remove the client protocols instead of the server ones? What machine
are you connecting from?
John
"Vadim" wrote:

> Hi,
> I am trying to disable external connections to my sql server installation:
> removed (disabled) all the network protocols, restarted the server machine,
> but I can still run EM and connect to that sql server. What am I missing to
> disable the external connections?
> Thank you,
> Vadim
>
>