Showing posts with label msde. Show all posts
Showing posts with label msde. Show all posts

Friday, March 30, 2012

Problems installing SP4 on MSDE 2000 (SP3)

Hi there,

I have some trouble installing SP4 onto an existing MSDE 2000 (with SP3).
I run setup like described in the readme: "setup /upgradesp sqlrun" => Setup starts and I get: "Product already installed ... "
Im using the MSDE version of the SP4 setup, the MSSQL version Im running is 8.00.760
Ive also tried to use the UPGRADEUSER and UPGRADEPWD arguments, but with the same results.

Any ideas?

Thank you

I guess you attempted couple of times SP4 install.

You need to modify following registry key and rerun setup.

after running successfull you need to change it back to original values.

Registry key is:

Local Machine\software\Microsoft\MSSQLServer\MSSqlServer\CurrentVersion

change 8.00.194 to something else [8.00.888]

change 8.00.761 to something else [8.00.999]

Thanks!

Problems installing MSDE with the Server Service disabled

Hello ;
We are having problems installing our desktop Application at a
customer's site where the policy is to disable the Server Service.
Being a desktop configuration, our installation places MSDE on the
same PC as the rest of the application. Unfortunately the
installation will not succeed unless the Server Service is enabled.
Interestingly, we can disable the Server Service after installation
and the Application will still function normally. However, enabling
the Server Service --even just during installation-- would violate our
customer's network policy.
It is my understanding that Server Service is not required if your
references are confined to the local system. Therefore my question is
: Is there a way to install MSDE or Standard Edition on the local
system when the Server Service is disabled?
Thanks in advance for any feedback,
BobThe following article has a fix for the problem you are experiencing:
http://support.microsoft.com/?id=829386.
You will need to contact Microsft support to get the fix.
Rand
This posting is provided "as is" with no warranties and confers no rights.sql

Problems installing MSDE Desktop SP3a SQL server

Hi,
I'm trying to install MSDE Desktop SP3a SQL server on my Windows 2000 Professional Laptop.
Every time I try it nearly finishes the installation and then just rollsback.
Can anyone help? Is this a known issue?
Thanks in advance,
JockyHave you tried using the verbose logging switch when you install? You need to add something like this to the setup.exe you are running fromthe commandline:
/L*v c:\msde_install.log

|||The following article might help you setup MSDE from command line.
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q324998|||

That Microsoft article did the trick. Thanks y'all!

Jocky.

Problems Installing MSDE 2000

Hi,
I trying to install MSDE 2000 in my PC a lot of times, but unsucessfully;
show me a pop-up screen:Setup failed to configure the server. Refer to the
server error logs and setup error logs for more information.
My log file:
2005-05-07 10:01:25.98 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)
2005-05-07 10:01:25.98 server Copyright (C) 1988-2002 Microsoft
Corporation.
2005-05-07 10:01:25.98 server All rights reserved.
2005-05-07 10:01:25.98 server Server Process ID is 3264.
2005-05-07 10:01:25.98 server Logging SQL Server messages in file
'C:\Program Files\Microsoft SQL Server\MSSQL\Data\MSSQL\LOG\ERRORLOG'.
2005-05-07 10:01:26.00 server SQL Server is starting at priority class
'normal'(1 CPU detected).
2005-05-07 10:01:26.01 server SQL Server configured for thread mode
processing.
2005-05-07 10:01:26.01 server Using dynamic lock allocation. [500] Lock
Blocks, [1000] Lock Owner Blocks.
2005-05-07 10:01:26.03 spid3 Warning ******************
2005-05-07 10:01:26.03 spid3 SQL Server started in single user mode.
Updates allowed to system catalogs.
2005-05-07 10:01:26.03 spid3 Starting up database 'master'.
2005-05-07 10:01:26.57 server Using 'SSNETLIB.DLL' version '8.0.766'.
2005-05-07 10:01:26.57 spid5 Starting up database 'model'.
2005-05-07 10:01:26.64 spid3 Server name is 'KL55692'.
2005-05-07 10:01:26.64 spid3 Skipping startup of clean database id 5
2005-05-07 10:01:26.64 spid3 Skipping startup of clean database id 6
2005-05-07 10:01:26.64 spid3 Starting up database 'msdb'.
2005-05-07 10:01:26.79 server SQL server listening on Shared Memory.
2005-05-07 10:01:26.79 server SQL Server is ready for client connections
2005-05-07 10:01:27.40 spid5 Clearing tempdb database.
2005-05-07 10:01:28.00 spid5 Starting up database 'tempdb'.
2005-05-07 10:01:28.07 spid3 Recovery complete.
2005-05-07 10:01:28.07 spid3 SQL global counter collection task is
created.
2005-05-07 10:01:28.17 spid3 Warning: override, autoexec procedures
skipped.
2005-05-07 10:01:36.51 spid3 SQL Server is terminating due to 'stop'
request from Service Control Manager.
In my event viewer/ Application:
Type: Error Source: MsiInstaller Event: 1013
and my setup.ini file:
[Options]
DATADIR=C:\Program Files\Microsoft SQL Server\MSSQL\Data\
DISABLENETWORKPROTOCOLS=1
SAPWD=sa
SECURITYMODE=SQL
TARGETDIR=C:\Program Files\Microsoft SQL Server\MSSQL\Binn\
Do you have any idea how fix this?
hi Ivan,
Ivan Anzaldua wrote:
> Hi,
> I trying to install MSDE 2000 in my PC a lot of times, but
> unsucessfully; show me a pop-up screen:Setup failed to configure the
> server. Refer to the server error logs and setup error logs for more
> information.
this error usually shows up when a previously disinstalled MSDE instance has
not been properly cleaned up... please refer to
http://support.microsoft.com/default...99&Product=sql
for further info
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Problems installing MSDE

When a double click setup.exe, I get the following message:

"SQL Server 2000 Desktop Engine Setup

SQL Server 2000 Desktop Engine cannot be installed on thiscomputer. This product requires Microsoft Windows NT Version 4.0 Service Pack 5or higher. Please download the service pack fromwww.microsoft.com prior to installing."


My machine is running Windows 2000 5.00.2195 Service Pack 4.
Anyone knows how to solve this problem?

Part of the problems installing MSDE is trying to install the service pack before SQL Server, try the link below and download the eval edition good for 120 days and the developer edition is $30 or less on the web . Hope this helps.
http://www.microsoft.com/sql/evaluation/trial/default.mspx|||Thanks for your advice. I didn't write back earlier because I wanted to try our solution until I it was successful.
I tried what you suggested, but I was still getting the error message.I looked into the autorun.inf file. In it it had these lines
...
[servicepack]
NTVersion=4
...
I verified that I could run MSDE on my OS by reading the documentation.Then, I commented out the line, and the installation rancorrectly.

Problems installing MSDE

I was in the process of installing the MSDE program when I came upon an error message. Here are the steps I performed to install the MSDE on my computer.

1. Downloaded the program from the Microsoft website.
2. Extracted the files to the "C" directory.
3. Used command prompt to set password and security mode. Rebooted computer after process was completed.
4. To complete the installation, I opened up the setup.exe. However, I got the error message "The instance named specified is invalid" when I opened the program.

Any solutions? (and yes, I already have the Web Matrix and the .Net platorm and am running on Windows 2000 SP4 .)I just finished dealing with this problem myself.

This is what I did and it FINALLY WORKED.

To install the MSDE:

go to the DOS prompt and navigate to C:\sql2ksp3\MSDE (or wherever you unpacked to).

Type this: setup INSTANCENAME="anyname" SECURITYMODE=SQL SAPWD="somepwd"

If you are like me you were thinking "what is the instance name?!". Well in this setup command you are naming it whatever you want, same with the SAPWD. Previously mine was blank (I know this is bad bad bad).

Anyway, once I ran it this way it installed fine!

Good Luck|||Well this may sound really stupid, but I;m a real newbie...

I've read dozens of setup instruction posts..I've started over and done what was suggested in this post, and everything seems to have gone well. SO FAR

Where do I go from here? Do I have to activate the database somehow? Where is this database anyway, after the I run the command, what was installed and where?

The startkit 'congifuration' still fails everytime. Says I don't have either .NET, MSDE, or IIS. I KNOW I have all of this installed..the only weak link could be the MSDE, because I have no idea what is going on with it.

I've installed SQL Server2000 in the past and got to a point where it configured and coulnd fidn the database..

Anyway, are there STEP BY STEP instructions ANYWHERE on this crap. I'm getting sick of reading random posts, web documentation, program documentation, and trying to compile this in to a process to follow..|||As Cheyjey mentioned, use this parameters:
INSTANCENAME="anyname" SECURITYMODE=SQL SAPWD="somepwd"

But you said installed it once, i have to mention that one the major reasons for getting the error you said, is that you have already installed MSDE on your system...|||i mean MSDE SP3a|||And Dear Mickey,

it's not as hard as you say, MSDE is a kind of a server, you can use it either as a server and an interface between applications and SQL Server 2000|||Amins2s,

I've read many post regarding the installation of MSDE, and i'm having the same problems.

I'm new at this and have followed instructions, and am using MSDE for school project.

I have installed VS 2002 Enterprise Architect, then uninstalled it to upgrade to VS 2003
I download the MSDE SP3a service pack from MS, and try to install it.

I'm suppose to use SAPWD=itm and SECURITYMODE=SQL, but upon typing this, i get the speciefed instance is invalid.

How am i suppose to recitfy this. How do i know what the instance i have on it is?

Also you said that you might be getting this error because you have already installed it once before. But i don't remember installing it, since the only thing i have installed is the VS2002 and VS2003, plus the Framework.

If you've installed it once before, how can you correct it so that you can properly install it now?

Even when i go to the MS .net/Frameworksdk/samples/setup/ there is no msde file to execute, which i read from other post should be there.

Thank You.

Nick|||Hello dear Nick,

ok... give this solution a try: do not indicate the SECURITYMODE parameter. Just run it from the Start>Run and use the SAPWD=itm parameter only!
And from the point that my FrameworkSDK doesn't work (the CD is corrupted), unfotunately I can't answer the question.
Please try it and tell me if it worked or not.
If it didn't, then send a feedback (Reply) and this time with more details about your system info...

Problems installing MSDE

Hi

I've recently downloaded and installed MSDE. I used the instructions from the Microsoft website that makes it as an instance of VSDOTNET. When I rebooted after installing it, there was absolutely no service for the SQL Server Service Manager to do anything with - the icon was in my tray with a blank circle, not a green Play or a red Stop symbol. So I uninstalled it, with an aim to reinstalling it as I had done before - without specifying an instance name.

Only trouble is, I'm now trying to reinstall it using:

setup SAPWD=<pword> SecurityMode=SQL

and I'm getting an error message that says "Setup failed to configure the server. Refer to the serveer error logs and setup error logs for more information." when the progress bar is about 2-thirds of the way along. It does this when I try to install as an instance of VSDOTNET again. It just won't play, and I don't know how to get at the logs to see what's up.

I'm running Win2K SP3, with .NET 1.1. Anyone able to help me?

Thanks
JonCheck to see if you are installing SP3a. If so include the DISABLENETPROTOCOLS=0 in the setup command line. This will ensure that you have enabled the network protocols for a new install. You also don't have to create an instance at installation time.sql

Problems Installation MSDE 2000 - URGENT

I am having problems with the installation of MSDE in customers.
Here in the company I never had problems, they put in some customers, MSDE
begins the installation and and it cancels and it doesn't present any error
message.
That happened in nets that had the novell (besides Windows), in you conspire
new, with windows 2000 server sp4 and tb in the xp professional.
I have the same configurations here and I didn't have problems.
To using MSDE 2000 original (without service pack), but with the sp3 tb
he/she didn't give right.
Will it be that the net novel influences (don't I have novell here)?
I don't know what to do..Depending on what version of novell your client is running they may not be
running TCP/IP. If you can add TCP/IP that will probably fix the problem. If
not, review the BOL for IPX/SPX.
HTH,
Semper Fi,
Red
"Frank Dulk" <fdulk@.bol.com.br> wrote in message
news:eFkLGmOnDHA.2272@.tk2msftngp13.phx.gbl...
>
> I am having problems with the installation of MSDE in customers.
> Here in the company I never had problems, they put in some customers, MSDE
> begins the installation and and it cancels and it doesn't present any
error
> message.
> That happened in nets that had the novell (besides Windows), in you
conspire
> new, with windows 2000 server sp4 and tb in the xp professional.
> I have the same configurations here and I didn't have problems.
> To using MSDE 2000 original (without service pack), but with the sp3 tb
> he/she didn't give right.
> Will it be that the net novel influences (don't I have novell here)?
> I don't know what to do..
>

Wednesday, March 21, 2012

Problems connecting to MSDE through ADO.NET

Hi there,
i've installed MSDE and have successfully connected to it from a remote version of Enterprise Manager but when i try to connect using ADO.NET from a website using the same login details i get the following message:
"SQL Server does not exist or access denied"
I installed msde with DISABLENETWORKPROTOCOLS=0 and SECURITYMODE=SQL so everything should be setup properly.
My connection string looks like this:
server=ip_address\instance_name;uid=myusername;pwd=mypassword;database=mydatabase
This connection string works fine when testing locally (without ip address ie. localhost\instance_name)
Any ideas why this doesn't work??You need to check that if the user has accessto the database. For most hosting companies they provide ASPNET useraccount and also NETWORK users. Give permission to ASPNET user and seeif it works also try the same for NETWORK users.

|||Thanks for the reply, you've lost me a bit though. I've setup my own server using Apache and mod_aspdotnet and have successfully tested it remotely from asp.net pages connecting to a MySQL database also on my server. Now i wish to use MSDE and am getting this problem.
How do i setup an ASPNET user account and give it permission to use the database? And why do i need to?
cheers

Problems connecting to MSDE on Windows 2003

I have installed MSDE 2000 SP3a on Windows 2003 Server Web edition.
I am trying to connect to this DB using enterprise manager from another
machine (Windows 2000 Server where SQL Server is installed) with no luck. I
am getting: SQL Server does not exist or access is denied. ConnectionOpen
(Connect())".
I have tried connecting using both Windows Authentication mode and using the
sa account (which I am not sure I know the password for the MSDE).
Can anyone help ?
Thanks
ra294@.hotmail.comCheck whether the instance is listening on any network protocols using
Server Network utility and enable them appropriately.
Thanks,
Bala.
This posting is provided "AS IS" with no warranties, and confers no
rights.
"ra294" <ra294@.hotmail.com> wrote in message
news:%23qkr5RW%23DHA.3808@.TK2MSFTNGP09.phx.gbl...
> I have installed MSDE 2000 SP3a on Windows 2003 Server Web edition.
> I am trying to connect to this DB using enterprise manager from another
> machine (Windows 2000 Server where SQL Server is installed) with no luck.
I
> am getting: SQL Server does not exist or access is denied. ConnectionOpen
> (Connect())".
> I have tried connecting using both Windows Authentication mode and using
the
> sa account (which I am not sure I know the password for the MSDE).
> Can anyone help ?
> Thanks
> ra294@.hotmail.com
>

Problems connecting to MSDE from other PC

Hello,
We are a small software developement group. Recently we decided to try out
using MSDE, and one of our developers installed it on his PC. After some
struggling with logging on it was discovered that by default it uses Windows
authentication mode and reading KB articles we were able to change the auth
mode to mixed. Now he is able to connect to the local MSDE server using
login "sa" and the install-time defined password, using the freeware tool
"Toad for MSSQL server". It's interesting, that it connects successfully, if
in the "server" is given the computer name, but wouldn't connect if IP
address 127.0.0.1 or LAN address starting with 192.168.*.* is used.
However, there are problems in attempting to connect to this server from
other PCs on our LAN, that have SQL Enterprise managers installed. It just
reports that "Server does not exist or access denied". Does MSDE even
support remote connection with SQL EM? Or, should I somewhere enable it?
Perhaps I am missing something here. It would be great to be able to connect
to this MSDE from other PCs, using EM to compy data to and from databases.
Thanks,
Pavils
I discovered, that is was related to the disabled network drivers.
Reinstalling with the network support solved the problem.
Pavils
|||Pavils Jurjans wrote:
> *I discovered, that is was related to the disabled network drivers.
> Reinstalling with the network support solved the problem.
> Pavils *
I'm having similar problems. Are you saying it's a problem with the
machine's network drivers, not MSDE?
Thanks,
Russell
russmail
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message361867.html

Problems connecting to MSDE from other PC

Hello,
We are a small software developement group. Recently we decided to try out
using MSDE, and one of our developers installed it on his PC. After some
struggling with logging on it was discovered that by default it uses Windows
authentication mode and reading KB articles we were able to change the auth
mode to mixed. Now he is able to connect to the local MSDE server using
login "sa" and the install-time defined password, using the freeware tool
"Toad for MSSQL server". It's interesting, that it connects successfully, if
in the "server" is given the computer name, but wouldn't connect if IP
address 127.0.0.1 or LAN address starting with 192.168.*.* is used.
However, there are problems in attempting to connect to this server from
other PCs on our LAN, that have SQL Enterprise managers installed. It just
reports that "Server does not exist or access denied". Does MSDE even
support remote connection with SQL EM? Or, should I somewhere enable it?
Perhaps I am missing something here. It would be great to be able to connect
to this MSDE from other PCs, using EM to compy data to and from databases.
Thanks,
PavilsI discovered, that is was related to the disabled network drivers.
Reinstalling with the network support solved the problem.
Pavils

Problems connecting to MSDE from client.

Error message: SQL Server not found or access denied.
We have installed our SW on a network with 8 clients. I runs fine on all but
1 client. This client is running Win 2000 and has 3 network cards installed
to be shared by 3 networks.
We use SQL auth. I have run CLICONFG on the client and enabled Names pipes
and TCP as they are on the server with the same port nr.. Our SW loads
correctly from the server so there is connection. I have also pinged the
server.
Olav
hi Olav,
Olav wrote:
> Error message: SQL Server not found or access denied.
> We have installed our SW on a network with 8 clients. I runs fine on
> all but 1 client. This client is running Win 2000 and has 3 network
> cards installed to be shared by 3 networks.
> We use SQL auth. I have run CLICONFG on the client and enabled Names
> pipes and TCP as they are on the server with the same port nr.. Our
> SW loads correctly from the server so there is connection. I have
> also pinged the server.
>
please have a look at
http://support.microsoft.com/default...06&Product=sql
in the client-related causes..
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
sql

Problems configuring proxy account

Hi, at this moment I have a user that is member of sysadmin in a msde. I
need to remove this user from this group but the user use the
xp_cmdshell.
I configured the proxy account on this intance with a user that is
domain admin and also grant the exec permissions on the xp_cmdshell
extended store procedure, and when I execute a simple query:
exec master.dbo.xp_cmdshell 'dir c:'
returns this error message:
Msg 50001, Level 1, State 50001
xpsql.cpp: Error 1813 from CreateProcessAsUser on line 636
I already restart de sqlserveragent service, but it doesnt work.
Do someone know what could be the reason.
Thanks a lot for your help.
*** Sent via Developersdex http://www.codecomments.com ***
It seems like the service account for the SQL Server service lacks some of the windows Privileges
needed. Search for below in Books Online and you will find what those are:
"level token"
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Maria Isabel Guzman" <mariaisabelguzman@.icasa.com.gt> wrote in message
news:%237yaKpegHHA.4064@.TK2MSFTNGP02.phx.gbl...
> Hi, at this moment I have a user that is member of sysadmin in a msde. I
> need to remove this user from this group but the user use the
> xp_cmdshell.
> I configured the proxy account on this intance with a user that is
> domain admin and also grant the exec permissions on the xp_cmdshell
> extended store procedure, and when I execute a simple query:
> exec master.dbo.xp_cmdshell 'dir c:'
> returns this error message:
> Msg 50001, Level 1, State 50001
> xpsql.cpp: Error 1813 from CreateProcessAsUser on line 636
> I already restart de sqlserveragent service, but it doesnt work.
> Do someone know what could be the reason.
> Thanks a lot for your help.
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||Thanks a lot for your help. I already check and the service account i
use is a domainadmin and domainadmins are administrator of the server.
do you have any other clue?
*** Sent via Developersdex http://www.codecomments.com ***
|||Domain admin isn't enough. You need to make sure it has privileges like "Replace a Process Level
Token" and the other stuff mentioned in Books Online.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"MariaGuzman" <marisa@.devdex.com> wrote in message news:%236iAzLhgHHA.4284@.TK2MSFTNGP06.phx.gbl...
> Thanks a lot for your help. I already check and the service account i
> use is a domainadmin and domainadmins are administrator of the server.
> do you have any other clue?
>
> *** Sent via Developersdex http://www.codecomments.com ***

Monday, March 12, 2012

Problems (for 24 Hrs) installing MSDE 2000

Your help will be VERY VERY much welcome. I have been trying to solve my
installation problem for some 24 hours now. I have installed MSDE 2000 on
at least 10 other computers in the past few weeks.
On a new notebook Intel Pentium4 3 GHz, XP Pro, 1G RAM, 60G h/disk I have
not been able to install MSDE 2000 with the same error presenting all the
time:
I have switched off every conceivable service and device short of stopping
the OS but still without luck.
Very frustrated!
ZSL
======= EXTRACT from Event Logs (sorry, first event at bottom) ======
9/04/2004,9:06:26
PM,MsiInstaller,Information,None,11708,N/A,COMPUTER,Product: Microsoft SQL
Server Desktop Engine -- Installation operation failed.
9/04/2004,9:06:22 PM,MsiInstaller,Error,None,1013,N/A,COMPUTER,Product:
Microsoft SQL Server Desktop Engine -- Setup failed to configure the server.
Refer to the server error logs and setup error logs for more information.
9/04/2004,9:06:00 PM,MSSQLSERVER,Information,-2,17055,N/A,COMPUTER,"The
description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
found. The local computer may not have the necessary registry information or
message DLL files to display messages from a remote computer. You may be
able to use the /AUXSOURCE= flag to retrieve this description; see Help and
Support for details. The following information is part of the event: 17148,
SQL Server is terminating due to 'stop' request from Service Control
Manager.
.."
9/04/2004,9:05:50 PM,MSSQLSERVER,Information,-2,17055,N/A,COMPUTER,"The
description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
found. The local computer may not have the necessary registry information or
message DLL files to display messages from a remote computer. You may be
able to use the /AUXSOURCE= flag to retrieve this description; see Help and
Support for details. The following information is part of the event: 17126,
SQL Server is ready for client connections
.."
9/04/2004,9:05:50 PM,MSSQLSERVER,Information,-2,17055,N/A,COMPUTER,"The
description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
found. The local computer may not have the necessary registry information or
message DLL files to display messages from a remote computer. You may be
able to use the /AUXSOURCE= flag to retrieve this description; see Help and
Support for details. The following information is part of the event: 19013,
SQL server listening on Shared Memory.
.."
9/04/2004,9:05:50 PM,MSSQLServer,Warning,-8,19011,N/A,COMPUTER,The
description for Event ID ( 19011 ) in Source ( MSSQLServer ) cannot be
found. The local computer may not have the necessary registry information or
message DLL files to display messages from a remote computer. You may be
able to use the /AUXSOURCE= flag to retrieve this description; see Help and
Support for details. The following information is part of the event:
(SpnRegister) : Error 1355.
9/04/2004,9:05:44 PM,MSSQLSERVER,Information,-2,17055,N/A,COMPUTER,"The
description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
found. The local computer may not have the necessary registry information or
message DLL files to display messages from a remote computer. You may be
able to use the /AUXSOURCE= flag to retrieve this description; see Help and
Support for details. The following information is part of the event: 17052,
Recovery complete.
.."
9/04/2004,9:05:41 PM,MSSQLSERVER,Information,-2,17055,N/A,COMPUTER,"The
description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
found. The local computer may not have the necessary registry information or
message DLL files to display messages from a remote computer. You may be
able to use the /AUXSOURCE= flag to retrieve this description; see Help and
Support for details. The following information is part of the event: 17834,
Using 'SSNETLIB.DLL' version '8.0.766'.
.."
9/04/2004,9:05:40 PM,MSSQLSERVER,Information,-2,17055,N/A,COMPUTER,"The
description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
found. The local computer may not have the necessary registry information or
message DLL files to display messages from a remote computer. You may be
able to use the /AUXSOURCE= flag to retrieve this description; see Help and
Support for details. The following information is part of the event: 17658,
SQL Server started in single user mode. Updates allowed to system catalogs.
.."
9/04/2004,9:05:40 PM,MSSQLSERVER,Information,-2,17055,N/A,COMPUTER,"The
description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
found. The local computer may not have the necessary registry information or
message DLL files to display messages from a remote computer. You may be
able to use the /AUXSOURCE= flag to retrieve this description; see Help and
Support for details. The following information is part of the event: 17125,
Using dynamic lock allocation. [500] Lock Blocks, [1000] Lock Owner Blocks.
.."
9/04/2004,9:05:40 PM,MSSQLSERVER,Information,-2,17055,N/A,COMPUTER,"The
description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
found. The local computer may not have the necessary registry information or
message DLL files to display messages from a remote computer. You may be
able to use the /AUXSOURCE= flag to retrieve this description; see Help and
Support for details. The following information is part of the event: 17124,
SQL Server configured for thread mode processing.
.."
9/04/2004,9:05:40 PM,MSSQLSERVER,Information,-2,17055,N/A,COMPUTER,"The
description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
found. The local computer may not have the necessary registry information or
message DLL files to display messages from a remote computer. You may be
able to use the /AUXSOURCE= flag to retrieve this description; see Help and
Support for details. The following information is part of the event: 17162,
SQL Server is starting at priority class 'normal'(1 CPU detected).
.."
9/04/2004,9:05:40 PM,MSSQLSERVER,Information,-2,17176,N/A,COMPUTER,"The
description for Event ID ( 17176 ) in Source ( MSSQLSERVER ) cannot be
found. The local computer may not have the necessary registry information or
message DLL files to display messages from a remote computer. You may be
able to use the /AUXSOURCE= flag to retrieve this description; see Help and
Support for details. The following information is part of the event: 1436,
4/9/2004 7:54:39 PM, 4/9/2004 9:54:39 AM."
9/04/2004,9:05:40 PM,MSSQLSERVER,Information,-2,17055,N/A,COMPUTER,"The
description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
found. The local computer may not have the necessary registry information or
message DLL files to display messages from a remote computer. You may be
able to use the /AUXSOURCE= flag to retrieve this description; see Help and
Support for details. The following information is part of the event: 17104,
Server Process ID is 3140.
.."
9/04/2004,9:05:40 PM,MSSQLSERVER,Information,-2,17055,N/A,COMPUTER,"The
description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
found. The local computer may not have the necessary registry information or
message DLL files to display messages from a remote computer. You may be
able to use the /AUXSOURCE= flag to retrieve this description; see Help and
Support for details. The following information is part of the event: 17052,
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)
.."
9/04/2004,9:05:37 PM,LoadPerf,Information,None,1000,N/A,COMPUTER,Performance
counters for the MSSQLServer (MSSQLSERVER) service were loaded successfully.
The Record Data contains the new index values assigned to this service.
Have you got file sharing enabled on the system? You'll need it.
HTH,
Greg Low (MVP)
MSDE Manager SQL Tools
www.whitebearconsulting.com
"ZSL" <inzaneleo@.yahoo.com.au> wrote in message
news:%23ubXVHjHEHA.2928@.TK2MSFTNGP10.phx.gbl...
> Your help will be VERY VERY much welcome. I have been trying to solve my
> installation problem for some 24 hours now. I have installed MSDE 2000 on
> at least 10 other computers in the past few weeks.
> On a new notebook Intel Pentium4 3 GHz, XP Pro, 1G RAM, 60G h/disk I have
> not been able to install MSDE 2000 with the same error presenting all the
> time:
> I have switched off every conceivable service and device short of stopping
> the OS but still without luck.
> Very frustrated!
> ZSL
> ======= EXTRACT from Event Logs (sorry, first event at bottom) ======
> 9/04/2004,9:06:26
> PM,MsiInstaller,Information,None,11708,N/A,COMPUTER,Product: Microsoft SQL
> Server Desktop Engine -- Installation operation failed.
> 9/04/2004,9:06:22 PM,MsiInstaller,Error,None,1013,N/A,COMPUTER,Product:
> Microsoft SQL Server Desktop Engine -- Setup failed to configure the
server.
> Refer to the server error logs and setup error logs for more information.
> 9/04/2004,9:06:00 PM,MSSQLSERVER,Information,-2,17055,N/A,COMPUTER,"The
> description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
> found. The local computer may not have the necessary registry information
or
> message DLL files to display messages from a remote computer. You may be
> able to use the /AUXSOURCE= flag to retrieve this description; see Help
and
> Support for details. The following information is part of the event:
17148,
> SQL Server is terminating due to 'stop' request from Service Control
> Manager.
> ."
> 9/04/2004,9:05:50 PM,MSSQLSERVER,Information,-2,17055,N/A,COMPUTER,"The
> description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
> found. The local computer may not have the necessary registry information
or
> message DLL files to display messages from a remote computer. You may be
> able to use the /AUXSOURCE= flag to retrieve this description; see Help
and
> Support for details. The following information is part of the event:
17126,
> SQL Server is ready for client connections
> ."
> 9/04/2004,9:05:50 PM,MSSQLSERVER,Information,-2,17055,N/A,COMPUTER,"The
> description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
> found. The local computer may not have the necessary registry information
or
> message DLL files to display messages from a remote computer. You may be
> able to use the /AUXSOURCE= flag to retrieve this description; see Help
and
> Support for details. The following information is part of the event:
19013,
> SQL server listening on Shared Memory.
> ."
> 9/04/2004,9:05:50 PM,MSSQLServer,Warning,-8,19011,N/A,COMPUTER,The
> description for Event ID ( 19011 ) in Source ( MSSQLServer ) cannot be
> found. The local computer may not have the necessary registry information
or
> message DLL files to display messages from a remote computer. You may be
> able to use the /AUXSOURCE= flag to retrieve this description; see Help
and
> Support for details. The following information is part of the event:
> (SpnRegister) : Error 1355.
> 9/04/2004,9:05:44 PM,MSSQLSERVER,Information,-2,17055,N/A,COMPUTER,"The
> description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
> found. The local computer may not have the necessary registry information
or
> message DLL files to display messages from a remote computer. You may be
> able to use the /AUXSOURCE= flag to retrieve this description; see Help
and
> Support for details. The following information is part of the event:
17052,
> Recovery complete.
> ."
> 9/04/2004,9:05:41 PM,MSSQLSERVER,Information,-2,17055,N/A,COMPUTER,"The
> description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
> found. The local computer may not have the necessary registry information
or
> message DLL files to display messages from a remote computer. You may be
> able to use the /AUXSOURCE= flag to retrieve this description; see Help
and
> Support for details. The following information is part of the event:
17834,
> Using 'SSNETLIB.DLL' version '8.0.766'.
> ."
> 9/04/2004,9:05:40 PM,MSSQLSERVER,Information,-2,17055,N/A,COMPUTER,"The
> description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
> found. The local computer may not have the necessary registry information
or
> message DLL files to display messages from a remote computer. You may be
> able to use the /AUXSOURCE= flag to retrieve this description; see Help
and
> Support for details. The following information is part of the event:
17658,
> SQL Server started in single user mode. Updates allowed to system
catalogs.
> ."
> 9/04/2004,9:05:40 PM,MSSQLSERVER,Information,-2,17055,N/A,COMPUTER,"The
> description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
> found. The local computer may not have the necessary registry information
or
> message DLL files to display messages from a remote computer. You may be
> able to use the /AUXSOURCE= flag to retrieve this description; see Help
and
> Support for details. The following information is part of the event:
17125,
> Using dynamic lock allocation. [500] Lock Blocks, [1000] Lock Owner
Blocks.
> ."
> 9/04/2004,9:05:40 PM,MSSQLSERVER,Information,-2,17055,N/A,COMPUTER,"The
> description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
> found. The local computer may not have the necessary registry information
or
> message DLL files to display messages from a remote computer. You may be
> able to use the /AUXSOURCE= flag to retrieve this description; see Help
and
> Support for details. The following information is part of the event:
17124,
> SQL Server configured for thread mode processing.
> ."
> 9/04/2004,9:05:40 PM,MSSQLSERVER,Information,-2,17055,N/A,COMPUTER,"The
> description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
> found. The local computer may not have the necessary registry information
or
> message DLL files to display messages from a remote computer. You may be
> able to use the /AUXSOURCE= flag to retrieve this description; see Help
and
> Support for details. The following information is part of the event:
17162,
> SQL Server is starting at priority class 'normal'(1 CPU detected).
> ."
> 9/04/2004,9:05:40 PM,MSSQLSERVER,Information,-2,17176,N/A,COMPUTER,"The
> description for Event ID ( 17176 ) in Source ( MSSQLSERVER ) cannot be
> found. The local computer may not have the necessary registry information
or
> message DLL files to display messages from a remote computer. You may be
> able to use the /AUXSOURCE= flag to retrieve this description; see Help
and
> Support for details. The following information is part of the event: 1436,
> 4/9/2004 7:54:39 PM, 4/9/2004 9:54:39 AM."
> 9/04/2004,9:05:40 PM,MSSQLSERVER,Information,-2,17055,N/A,COMPUTER,"The
> description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
> found. The local computer may not have the necessary registry information
or
> message DLL files to display messages from a remote computer. You may be
> able to use the /AUXSOURCE= flag to retrieve this description; see Help
and
> Support for details. The following information is part of the event:
17104,
> Server Process ID is 3140.
> ."
> 9/04/2004,9:05:40 PM,MSSQLSERVER,Information,-2,17055,N/A,COMPUTER,"The
> description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
> found. The local computer may not have the necessary registry information
or
> message DLL files to display messages from a remote computer. You may be
> able to use the /AUXSOURCE= flag to retrieve this description; see Help
and
> Support for details. The following information is part of the event:
17052,
> 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)
> ."
> 9/04/2004,9:05:37
PM,LoadPerf,Information,None,1000,N/A,COMPUTER,Performance
> counters for the MSSQLServer (MSSQLSERVER) service were loaded
successfully.
> The Record Data contains the new index values assigned to this service.
>
>
|||Yep. File sharing is enabled.
ZSL
"Greg Low (MVP)" <greglow@.lowell.com.au> wrote in message
news:Oycj5ojHEHA.164@.TK2MSFTNGP10.phx.gbl...
> Have you got file sharing enabled on the system? You'll need it.
> HTH,
> --
> Greg Low (MVP)
> MSDE Manager SQL Tools
> www.whitebearconsulting.com
>
> "ZSL" <inzaneleo@.yahoo.com.au> wrote in message
> news:%23ubXVHjHEHA.2928@.TK2MSFTNGP10.phx.gbl...
my
on
have
the
stopping
SQL
> server.
information.
information
> or
> and
> 17148,
information
> or
> and
> 17126,
information
> or
> and
> 19013,
information
> or
> and
information
> or
> and
> 17052,
information
> or
> and
> 17834,
information
> or
> and
> 17658,
> catalogs.
information
> or
> and
> 17125,
> Blocks.
information
> or
> and
> 17124,
information
> or
> and
> 17162,
information
> or
> and
1436,
information
> or
> and
> 17104,
information
> or
> and
> 17052,
> PM,LoadPerf,Information,None,1000,N/A,COMPUTER,Performance
> successfully.
>
|||Problem solved by a repair-reinstall of XP Pro.
This has been a nightmare!
Surely the MSSQL/MSDE Developers and Support groups can solve these
problems. I spent 18 hours researching this problem on various internet
forums and I am astounded at the number of people have the same or simliar
problems...
Come on MS people! The last time I had problems like this with databases was
the late 80's when thrashing hard disks with heavy db activity caused
crashes.
There are other PC db's out there that do not have these problems (this is
not the place to name them). If it were only MSDE (ie not charge
distribution) then this might be forgiven but MSSQL it seems from reading
requests for HELP has similar issues!
Unhappy camper....
ZSL
"ZSL" <inzaneleo@.yahoo.com.au> wrote in message
news:%23ubXVHjHEHA.2928@.TK2MSFTNGP10.phx.gbl...
> Your help will be VERY VERY much welcome. I have been trying to solve my
> installation problem for some 24 hours now. I have installed MSDE 2000 on
> at least 10 other computers in the past few weeks.
> On a new notebook Intel Pentium4 3 GHz, XP Pro, 1G RAM, 60G h/disk I have
> not been able to install MSDE 2000 with the same error presenting all the
> time:
> I have switched off every conceivable service and device short of stopping
> the OS but still without luck.
> Very frustrated!
> ZSL
> ======= EXTRACT from Event Logs (sorry, first event at bottom) ======
> 9/04/2004,9:06:26
> PM,MsiInstaller,Information,None,11708,N/A,COMPUTER,Product: Microsoft SQL
> Server Desktop Engine -- Installation operation failed.
> 9/04/2004,9:06:22 PM,MsiInstaller,Error,None,1013,N/A,COMPUTER,Product:
> Microsoft SQL Server Desktop Engine -- Setup failed to configure the
server.
> Refer to the server error logs and setup error logs for more information.
> 9/04/2004,9:06:00 PM,MSSQLSERVER,Information,-2,17055,N/A,COMPUTER,"The
> description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
> found. The local computer may not have the necessary registry information
or
> message DLL files to display messages from a remote computer. You may be
> able to use the /AUXSOURCE= flag to retrieve this description; see Help
and
> Support for details. The following information is part of the event:
17148,
> SQL Server is terminating due to 'stop' request from Service Control
> Manager.
> ."
> 9/04/2004,9:05:50 PM,MSSQLSERVER,Information,-2,17055,N/A,COMPUTER,"The
> description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
> found. The local computer may not have the necessary registry information
or
> message DLL files to display messages from a remote computer. You may be
> able to use the /AUXSOURCE= flag to retrieve this description; see Help
and
> Support for details. The following information is part of the event:
17126,
> SQL Server is ready for client connections
> ."
> 9/04/2004,9:05:50 PM,MSSQLSERVER,Information,-2,17055,N/A,COMPUTER,"The
> description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
> found. The local computer may not have the necessary registry information
or
> message DLL files to display messages from a remote computer. You may be
> able to use the /AUXSOURCE= flag to retrieve this description; see Help
and
> Support for details. The following information is part of the event:
19013,
> SQL server listening on Shared Memory.
> ."
> 9/04/2004,9:05:50 PM,MSSQLServer,Warning,-8,19011,N/A,COMPUTER,The
> description for Event ID ( 19011 ) in Source ( MSSQLServer ) cannot be
> found. The local computer may not have the necessary registry information
or
> message DLL files to display messages from a remote computer. You may be
> able to use the /AUXSOURCE= flag to retrieve this description; see Help
and
> Support for details. The following information is part of the event:
> (SpnRegister) : Error 1355.
> 9/04/2004,9:05:44 PM,MSSQLSERVER,Information,-2,17055,N/A,COMPUTER,"The
> description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
> found. The local computer may not have the necessary registry information
or
> message DLL files to display messages from a remote computer. You may be
> able to use the /AUXSOURCE= flag to retrieve this description; see Help
and
> Support for details. The following information is part of the event:
17052,
> Recovery complete.
> ."
> 9/04/2004,9:05:41 PM,MSSQLSERVER,Information,-2,17055,N/A,COMPUTER,"The
> description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
> found. The local computer may not have the necessary registry information
or
> message DLL files to display messages from a remote computer. You may be
> able to use the /AUXSOURCE= flag to retrieve this description; see Help
and
> Support for details. The following information is part of the event:
17834,
> Using 'SSNETLIB.DLL' version '8.0.766'.
> ."
> 9/04/2004,9:05:40 PM,MSSQLSERVER,Information,-2,17055,N/A,COMPUTER,"The
> description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
> found. The local computer may not have the necessary registry information
or
> message DLL files to display messages from a remote computer. You may be
> able to use the /AUXSOURCE= flag to retrieve this description; see Help
and
> Support for details. The following information is part of the event:
17658,
> SQL Server started in single user mode. Updates allowed to system
catalogs.
> ."
> 9/04/2004,9:05:40 PM,MSSQLSERVER,Information,-2,17055,N/A,COMPUTER,"The
> description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
> found. The local computer may not have the necessary registry information
or
> message DLL files to display messages from a remote computer. You may be
> able to use the /AUXSOURCE= flag to retrieve this description; see Help
and
> Support for details. The following information is part of the event:
17125,
> Using dynamic lock allocation. [500] Lock Blocks, [1000] Lock Owner
Blocks.
> ."
> 9/04/2004,9:05:40 PM,MSSQLSERVER,Information,-2,17055,N/A,COMPUTER,"The
> description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
> found. The local computer may not have the necessary registry information
or
> message DLL files to display messages from a remote computer. You may be
> able to use the /AUXSOURCE= flag to retrieve this description; see Help
and
> Support for details. The following information is part of the event:
17124,
> SQL Server configured for thread mode processing.
> ."
> 9/04/2004,9:05:40 PM,MSSQLSERVER,Information,-2,17055,N/A,COMPUTER,"The
> description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
> found. The local computer may not have the necessary registry information
or
> message DLL files to display messages from a remote computer. You may be
> able to use the /AUXSOURCE= flag to retrieve this description; see Help
and
> Support for details. The following information is part of the event:
17162,
> SQL Server is starting at priority class 'normal'(1 CPU detected).
> ."
> 9/04/2004,9:05:40 PM,MSSQLSERVER,Information,-2,17176,N/A,COMPUTER,"The
> description for Event ID ( 17176 ) in Source ( MSSQLSERVER ) cannot be
> found. The local computer may not have the necessary registry information
or
> message DLL files to display messages from a remote computer. You may be
> able to use the /AUXSOURCE= flag to retrieve this description; see Help
and
> Support for details. The following information is part of the event: 1436,
> 4/9/2004 7:54:39 PM, 4/9/2004 9:54:39 AM."
> 9/04/2004,9:05:40 PM,MSSQLSERVER,Information,-2,17055,N/A,COMPUTER,"The
> description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
> found. The local computer may not have the necessary registry information
or
> message DLL files to display messages from a remote computer. You may be
> able to use the /AUXSOURCE= flag to retrieve this description; see Help
and
> Support for details. The following information is part of the event:
17104,
> Server Process ID is 3140.
> ."
> 9/04/2004,9:05:40 PM,MSSQLSERVER,Information,-2,17055,N/A,COMPUTER,"The
> description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
> found. The local computer may not have the necessary registry information
or
> message DLL files to display messages from a remote computer. You may be
> able to use the /AUXSOURCE= flag to retrieve this description; see Help
and
> Support for details. The following information is part of the event:
17052,
> 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)
> ."
> 9/04/2004,9:05:37
PM,LoadPerf,Information,None,1000,N/A,COMPUTER,Performance
> counters for the MSSQLServer (MSSQLSERVER) service were loaded
successfully.
> The Record Data contains the new index values assigned to this service.
>
>

Problem:MS-Access.adp with MSDE link to csv file

Hi. I'm coming from a background of developing mdbs and am trying to migrate
our databases to adps running atop MSDE 2000. However, I've encountered a
problem while trying to do analogous things to what I've done before with
mdbs...for example:

-Linking to a csv file on another machine: I am able to establish a link
uning the 'Link Table Wizard' that shows up as a new view. However, upon
openning the view I see only a single column (left-most).

What am I missing here?

JamesMaybe I can help, drop me a email direct, I do this sort of thing for a
living

Regards
Andrew

JimJimJimJim wrote:

> Hi. I'm coming from a background of developing mdbs and am trying to migrate
> our databases to adps running atop MSDE 2000. However, I've encountered a
> problem while trying to do analogous things to what I've done before with
> mdbs...for example:
> -Linking to a csv file on another machine: I am able to establish a link
> uning the 'Link Table Wizard' that shows up as a new view. However, upon
> openning the view I see only a single column (left-most).
> What am I missing here?
> James
>
>

Friday, March 9, 2012

Problem: Performance difference between MSDE and SQL Express 2005

Hello, all, I started out thinking my problems were elsewhere but as I
have worked through this I have isolated my problem, currently, as a
difference between MSDE and SQL Express 2005 (I'll just call it
Express for simplicity).

I have, to try to simplify things, put the exact same DB on two
systems, one running MSDE and one running Express. Both have 2 Ghz
processors (one Intel, one AMD), both have a decent amount of RAM
(Intel system has 1 GB, AMD system has 512 MB), and plenty of GB of
free disk space. MSDE is running on the Intel system, Express is
running on the AMD system. To keep things fair I use the exact same
DB's and query on both systems. The DB's were created on MSDE so I
sp_detach_db'd them from MSDE and then sp_attach_db'd them to Express
(this is how MS says to do a "side-by-side" upgrade, so it's
acceptable to do so). After fighting problems in performance
differences in different situations I have narrowed the problem down
to this:

Executing a simple select statement with join clause on the databases
yields a difference in execution time that is quite great. Using the
Express Management program I can run the query against either system
(MSDE or Express, the two systems are connected via crossover cable to
eliminate any network problems/issues). When running the query
against the MSDE system (which is over the network) I consistently get
<20 ms response times on the query. When running the query against
the Express installation (which is in shared memory) I consistently
get 700 ms or longer response times. Both times are for the Total
Execution Time.

The query is simply this: select db1.* from db1.owner.tablename as db1
inner join db2.owner.tablename as db2 on db1.pkey = db2.someid where
db1.criteria = 3

So, gimme all the columns from one table in one DB (local to the
installation), matching the records in another DB (also local to the
installation), where one field in the first db matches a field in the
second db and where, in the first db, one column value = 3.

The first table has a total record count of 630 records of which only
12 match the where clause. The second table has a total record count
of about 2,700 of which only 12 match up on the 12 out of 630.

Even though the data is the same and I've done the detach and attach,
and even done the sp_updatestats, the difference in execution time is
remarkable, in a bad way.

Checking the Execution Plan reveals that both queries have the same
steps, but, on the MSDE system the largest consumer in the process is
the Clustered Index Scan of the 630 record table (DB1 in my query
example), using 85%. The next big consumer is a Clustered Index Seek
against the other table (2,700 rows), using 15%.

The Execution Plan against the Express system reveals basically the
exact opposite: 27% going to the Clustered Index Scan of the 630
record DB1, and 72% going to the Clustered Index Seek of the 2,700
record DB2.

I'm sorry to be stupid but I have this information but I don't know
what to do with it. The best that I can tell from this is that this
is the source of my problems. My problems are that on my current
systems that my clients use the data is returned to them faster than
they can click the mouse and that the new system (that is, when they
chose (or are forced by attrition) to move to Vista and thus Express
2005) the screen pop is like 1.5 seconds. This creates poor user
experience. Worse, one process I allow the users to do goes from
taking 14-30 seconds to over 4 minutes (all on the same machine with
the same OS and version of my program, so it's not a machine or OS or
my app problem).

Anyway, I hope someone can shed some light on this now that I've pared
it down some.

Thanks in advance.

--HCHC (hboothe@.gte.net) writes:

Quote:

Originally Posted by

The query is simply this: select db1.* from db1.owner.tablename as db1
inner join db2.owner.tablename as db2 on db1.pkey = db2.someid where
db1.criteria = 3
>
So, gimme all the columns from one table in one DB (local to the
installation), matching the records in another DB (also local to the
installation), where one field in the first db matches a field in the
second db and where, in the first db, one column value = 3.
>
The first table has a total record count of 630 records of which only
12 match the where clause. The second table has a total record count
of about 2,700 of which only 12 match up on the 12 out of 630.
>
Even though the data is the same and I've done the detach and attach,
and even done the sp_updatestats, the difference in execution time is
remarkable, in a bad way.
>
Checking the Execution Plan reveals that both queries have the same
steps, but, on the MSDE system the largest consumer in the process is
the Clustered Index Scan of the 630 record table (DB1 in my query
example), using 85%. The next big consumer is a Clustered Index Seek
against the other table (2,700 rows), using 15%.
>
The Execution Plan against the Express system reveals basically the
exact opposite: 27% going to the Clustered Index Scan of the 630
record DB1, and 72% going to the Clustered Index Seek of the 2,700
record DB2.


Is there any index on the criteria column? It does not sound like
that, since you get a clustered scan on that table.

But before you apply any index, can you run the queries in both servers
preceded by

SET STATISTICS PROFILE ON

and then post the output? Since the output is very wide, it's not good
if you put directly into the article body, please put it in an attachment.
I see that you post from Google. I don't know if they permit attachments,
but if they don't, maybe you could put the output on a web site and just
post a URL?

Quote:

Originally Posted by

Worse, one process I allow the users to do goes from taking 14-30
seconds to over 4 minutes (all on the same machine with the same OS and
version of my program, so it's not a machine or OS or my app problem).


I suppose this process is against some completely different tables?
14 seconds on the table sizes you mentioned sounds absymal to me.

--
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|||On Feb 4, 6:12 am, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

HC (hboo...@.gte.net) writes:

Quote:

Originally Posted by

The query is simply this: select db1.* from db1.owner.tablename as db1
inner join db2.owner.tablename as db2 on db1.pkey = db2.someid where
db1.criteria = 3


>

Quote:

Originally Posted by

So, gimme all the columns from one table in one DB (local to the
installation), matching the records in another DB (also local to the
installation), where one field in the first db matches a field in the
second db and where, in the first db, one column value = 3.


>

Quote:

Originally Posted by

The first table has a total record count of 630 records of which only
12 match the where clause. The second table has a total record count
of about 2,700 of which only 12 match up on the 12 out of 630.


>

Quote:

Originally Posted by

Even though the data is the same and I've done the detach and attach,
and even done the sp_updatestats, the difference in execution time is
remarkable, in a bad way.


>

Quote:

Originally Posted by

Checking the Execution Plan reveals that both queries have the same
steps, but, on the MSDE system the largest consumer in the process is
the Clustered Index Scan of the 630 record table (DB1 in my query
example), using 85%. The next big consumer is a Clustered Index Seek
against the other table (2,700 rows), using 15%.


>

Quote:

Originally Posted by

The Execution Plan against the Express system reveals basically the
exact opposite: 27% going to the Clustered Index Scan of the 630
record DB1, and 72% going to the Clustered Index Seek of the 2,700
record DB2.


>
Is there any index on the criteria column? It does not sound like
that, since you get a clustered scan on that table.
>
But before you apply any index, can you run the queries in both servers
preceded by
>
SET STATISTICS PROFILE ON
>
and then post the output? Since the output is very wide, it's not good
if you put directly into the article body, please put it in an attachment.
I see that you post from Google. I don't know if they permit attachments,
but if they don't, maybe you could put the output on a web site and just
post a URL?
>

Quote:

Originally Posted by

Worse, one process I allow the users to do goes from taking 14-30
seconds to over 4 minutes (all on the same machine with the same OS and
version of my program, so it's not a machine or OS or my app problem).


>
I suppose this process is against some completely different tables?
14 seconds on the table sizes you mentioned sounds absymal to me.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text -
>
- Show quoted text -


Hmm, I thought I posted this...but it has not shown up in the last 30
minutes so I'm going to send it again, I'm sorry if it winds up
showing twice...

Hello, Erland. I have no user-defined indexes (indices?) in any of my
tables or databases. The only indexes that would exist in any of the
database tables would be those that are by default with primary key
constraints. The actual query I'm running is:

select tMO.* from caredata.dbo.tbl_medications as tM inner join
caredata_aa.dbo.tbl_medicationorders as tMO on tMO.medid = tM.pkey
where tMO.resid = 43

the join clause tMO.medid is matched against the primary key of the tM
table. There is no index for tMO.medid, nor is there one for
tMO.resid. Would it help to index them since the Clustered Index Seek
is being performed against the tbl_Medications table? In my program I
do a LEFT OUTER JOIN instead of an INNER JOIN, but the performance is
the same: slow. So, I just left it at an INNER JOIN to try to keep
things simple.

I ran the query as you asked, against both database engines and have
posted them online. I was having problems with my regular website so
I had to cobble one together for this purpose. It was done through a
web-interface so I just UUE encoded a ZIP file of the text file with
the results. Each db engine's results are in a different file. I
included the query (as seen above), and the statistics. You can find
them here: http://home.earthlink.net/~hboothe/ On that lame page
you'll see links on the left side for the pages dealing with each db
engine.

If you have any problems with the files lemme know and I'll see if I
can find another place to put them.

The 14 second process I refer to (that takes over 4 minutes on
Express) is using the same tables. What I am trying to do is to pare
this stuff down to the smallest piece that exhibits the unwanted
behavior so that the situation is not obfuscated unnecessarily. I
think that once the problem with the little query is fixed, the
problem with the big query will be fixed. The process that takes 14
seconds on these little tables in MSDE and over 4 minutes in Express
is nasty. I have multiple databases. One database is the parent of
the whole system, containing information that is relevant across each
of the other databases. You may notice in the query I've posted that
the tMO table is from one db (caredata_aa.dbo.tbl_MedicationOrders)
and tM is from another (caredata.dbo.tbl_Medications). My program
(system) can support many of the _aa databases (_aa, _ab, _ac, etc.).
The process that takes 14 seconds on MSDE is supposed to report to the
users those medications that are not in use by any medication order in
any of the databases (so, no match from tM.pkey to tMO.medid from any
tMO of which there may be as many as 4 (maximum number currently
used)). Each record must not match across each of multiple db's.

I'm clearly not a SQL Server expert, I'm just a VB programmer, but
since I have my own little business I have to do all the functions.
Since this worked on MSDE so well I thought I'd done a good job of
designing the queries and working with the DB. Obviously since it's
not working well on Express there is a good chance I've just done a
poor job of designing the DB or the queries. Maybe there's something
really simple I've overlooked. Now that I've narrowed the problem
down to one little query that does one simple join, I have eliminated
a lot of the initial potential problem points (VB6, ADO, connection
strings, recordset types, etc.). I'm going to go play with adding
indexes to the tables to see if that makes any difference.

Thank you for your help.

--HC|||Problem: Performance difference between MSDE and SQL Express 2005|||HC (hboothe@.gte.net) writes:

Quote:

Originally Posted by

select tMO.* from caredata.dbo.tbl_medications as tM inner join
caredata_aa.dbo.tbl_medicationorders as tMO on tMO.medid = tM.pkey
where tMO.resid = 43
>
the join clause tMO.medid is matched against the primary key of the tM
table. There is no index for tMO.medid, nor is there one for
tMO.resid. Would it help to index them since the Clustered Index Seek
is being performed against the tbl_Medications table?


There should be an index in resid. If the major part of the queries
against tbl_medicationorders are against resid, then it's probably a
good idea to cluster on this column. (You would then have to drop
the primary key, and the reapply it as nonclustered.)

Quote:

Originally Posted by

In my program I do a LEFT OUTER JOIN instead of an INNER JOIN, but the
performance is the same: slow. So, I just left it at an INNER JOIN to
try to keep things simple.


Well, since you only return data from tMO, this may be even better:

SELECT tMO.*
FROM caredata_aa.dbo.tbl_medicationorders tNO
WHERE tMO.resid = 43
AND EXISTS (SELECT *
FROM caredata.dbo.tbl_medications tM
WHERE tMO.medid = tM.pkey)

Not that it is likely to affect performance, but you would avoid
returning the same row twice from meditioncationorders.

Quote:

Originally Posted by

I ran the query as you asked, against both database engines and have
posted them online. I was having problems with my regular website so
I had to cobble one together for this purpose. It was done through a
web-interface so I just UUE encoded a ZIP file of the text file with
the results. Each db engine's results are in a different file. I
included the query (as seen above), and the statistics. You can find
them here: http://home.earthlink.net/~hboothe/ On that lame page
you'll see links on the left side for the pages dealing with each db
engine.


I'm afraid that they appear as somewhat cryptic to me. I guess that I
could copy them into a text editor and try to decode them, but I'm
lazy. Couldn't you just upload the output as text files there.

Quote:

Originally Posted by

The 14 second process I refer to (that takes over 4 minutes on
Express) is using the same tables. What I am trying to do is to pare
this stuff down to the smallest piece that exhibits the unwanted
behavior so that the situation is not obfuscated unnecessarily. I
think that once the problem with the little query is fixed, the
problem with the big query will be fixed. The process that takes 14
seconds on these little tables in MSDE and over 4 minutes in Express
is nasty. I have multiple databases. One database is the parent of
the whole system, containing information that is relevant across each
of the other databases. You may notice in the query I've posted that
the tMO table is from one db (caredata_aa.dbo.tbl_MedicationOrders)
and tM is from another (caredata.dbo.tbl_Medications). My program
(system) can support many of the _aa databases (_aa, _ab, _ac, etc.).
The process that takes 14 seconds on MSDE is supposed to report to the
users those medications that are not in use by any medication order in
any of the databases (so, no match from tM.pkey to tMO.medid from any
tMO of which there may be as many as 4 (maximum number currently
used)). Each record must not match across each of multiple db's.


Ehum, this design sounds dubious. I get the impression that if this was
all in the same database, and the same tables, this could be done in a
single query.

Or is there any particular reason you have multiple databases?

Anyway, it occurred to me, there is one thing to check for. Run this query:

select name from sys.databases where is_auto_close_on = 1

For all databses that appear do:

ALTER DATABASE db SET AUTO_CLOSE OFF

--
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|||On Feb 4, 5:38 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

HC (hboo...@.gte.net) writes:

Quote:

Originally Posted by

select tMO.* from caredata.dbo.tbl_medications as tM inner join
caredata_aa.dbo.tbl_medicationorders as tMO on tMO.medid = tM.pkey
where tMO.resid = 43


>

Quote:

Originally Posted by

the join clause tMO.medid is matched against the primary key of the tM
table. There is no index for tMO.medid, nor is there one for
tMO.resid. Would it help to index them since the Clustered Index Seek
is being performed against the tbl_Medications table?


>
There should be an index in resid. If the major part of the queries
against tbl_medicationorders are against resid, then it's probably a
good idea to cluster on this column. (You would then have to drop
the primary key, and the reapply it as nonclustered.)
>

Quote:

Originally Posted by

In my program I do a LEFT OUTER JOIN instead of an INNER JOIN, but the
performance is the same: slow. So, I just left it at an INNER JOIN to
try to keep things simple.


>
Well, since you only return data from tMO, this may be even better:
>
SELECT tMO.*
FROM caredata_aa.dbo.tbl_medicationorders tNO
WHERE tMO.resid = 43
AND EXISTS (SELECT *
FROM caredata.dbo.tbl_medications tM
WHERE tMO.medid = tM.pkey)
>
Not that it is likely to affect performance, but you would avoid
returning the same row twice from meditioncationorders.
>

Quote:

Originally Posted by

I ran the query as you asked, against both database engines and have
posted them online. I was having problems with my regular website so
I had to cobble one together for this purpose. It was done through a
web-interface so I just UUE encoded a ZIP file of the text file with
the results. Each db engine's results are in a different file. I
included the query (as seen above), and the statistics. You can find
them here:http://home.earthlink.net/~hboothe/ On that lame page
you'll see links on the left side for the pages dealing with each db
engine.


>
I'm afraid that they appear as somewhat cryptic to me. I guess that I
could copy them into a text editor and try to decode them, but I'm
lazy. Couldn't you just upload the output as text files there.
>
>
>
>
>

Quote:

Originally Posted by

The 14 second process I refer to (that takes over 4 minutes on
Express) is using the same tables. What I am trying to do is to pare
this stuff down to the smallest piece that exhibits the unwanted
behavior so that the situation is not obfuscated unnecessarily. I
think that once the problem with the little query is fixed, the
problem with the big query will be fixed. The process that takes 14
seconds on these little tables in MSDE and over 4 minutes in Express
is nasty. I have multiple databases. One database is the parent of
the whole system, containing information that is relevant across each
of the other databases. You may notice in the query I've posted that
the tMO table is from one db (caredata_aa.dbo.tbl_MedicationOrders)
and tM is from another (caredata.dbo.tbl_Medications). My program
(system) can support many of the _aa databases (_aa, _ab, _ac, etc.).
The process that takes 14 seconds on MSDE is supposed to report to the
users those medications that are not in use by any medication order in
any of the databases (so, no match from tM.pkey to tMO.medid from any
tMO of which there may be as many as 4 (maximum number currently
used)). Each record must not match across each of multiple db's.


>
Ehum, this design sounds dubious. I get the impression that if this was
all in the same database, and the same tables, this could be done in a
single query.
>
Or is there any particular reason you have multiple databases?
>
Anyway, it occurred to me, there is one thing to check for. Run this query:
>
select name from sys.databases where is_auto_close_on = 1
>
For all databses that appear do:
>
ALTER DATABASE db SET AUTO_CLOSE OFF
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text -
>
- Show quoted text -


Hello, Erland. First, I have put a variety of indexes into the
tbl_medicationorders (clustered and unclustered) with no real
difference in the Total Execution Time of the query. Second, I could
do the select as you suggest but I'm not getting any records from the
database other than the 12 I expect (for this particular select
statement, so I the modified statement would seem unnecessary. Third,
and this is the worst, I've found something that makes a difference in
the execution time of the queries every time.

Briefly, before I mention what has drastically shortened the execution
time of the query let me say that i thought it was flaky as hell so I,
thinking it might be a problem with my installation of SQL Express, re-
installed SQL Express from a fresh download from MS and reloaded the
DB's. I get the same flaky problems in query execution time.

Here's what I did after the reload, carefully mapped out just as I did
it. I rebooted after re-installing Express, just as a precaution. I
detached the databases from MSDE and attached them to Express
(different boxes, of course). I ran sp_updatestats on each DB. I ran
my query as I had previously posted with the inner join. I ran it
three times with the STATISTICS PROFILE ON and, in Management Studio I
had it show me the Execution Plan and the Client Statistics tabs. I
had noticed some really strange results, sometimes really fast,
sometimes really slow, even if I had not changed the database. I
found something that makes a difference in the query execution speed
but it's really bizarre. Here is what I got:

The average Total Execution Time for those three runs is 1,202.667
ms. Really slow and intolerable. Running this query from this box
against my MSDE system gives me responses in the 15-30 ms range.

I use the Object Explorer to locate a table in the caredata database.
I right-click on a table (one completely unrelated to the query) and
click Modify. I change NOTHING, just look at the window that shows me
the fields of the table. I click back on my query tab and, after
resetting client statistics, I run the query 3 times.

Now, the average Total Execution Time is down to 650.6667 ms. No
kidding. Same query, same data, same server, same table, EVERYTHING
is the same except now I have a tab open showing me the properties of
a table in the Caredata datbase.

Next, I open another unrelated table in the caredata_aa database (the
other DB involved in the query) in the same way (right-click, hit
Modify, change NOTHING). When I return to the tab with the query on
it and reset the client statistics and then run it three times I get
an average Total Execution Time of 62.000 ms.

What I'm seeing is that when I have an object (table) open from the
databases involved in the query (even if it's just a view of the
record layout in the Management Express console), the query runs
ridiculously faster, almost as fast as it runs against the MSDE
database.

I have reposted the data on that site for you. I didn't like how it
put line breaks in it which is why I UUE encoded it. You can take UUE
encoded stuff and just save it as a text file with a UUE extension and
programs like WinZip will read it. It's there now in plain text under
the SQL Express Results or here is a direct link: http://
home.earthlink.net/~hboothe/id2.html

I posted the results twice, once from when I had the other tables open
so the query was fast and once when the other tables were closed so
the query was long.

I don't understand why opening up the tables would make a difference.
If it was on a remote server that had to be sought out it might make
sense that the query would run faster if the server had already been
discovered but since it's running in shared memory that seems
unnecessary.

I ran the query you provided, as a nested select statement but the
response times were roughly the same as what I got with the join, on
average across 3 runs, 1,181.667ms Total Execution Time.

In all candor, the design may be dubious or outright stupid. The
design is based on a prior product which used separate sub-directories
to store the data for different clients. The idea here is that my
software product can keep information for one or more businesses.
That is, I may install my software at one company and they, as a
service, might host information for many other businesses on my
application. However, I may install the software at one company who
only hosts information for themselves. Each company that is hosted on
the system has it's data completely separate from the others. Any
information that would need to be shared and used across all the
different businesses on one installation go in the "parent" database
(which I call the repository) and any information specific to the
particular client goes in the "facility" database. I'm seeing some
ways I could have done it differently now, as a result of our
discussion, but when I started this project and product 2 years ago I
didn't consider doing it differently. Now the thing is installed
across several locations and I have another client about to go live
with a new system at the end of the month, presumably with Vista which
means no MSDE, so I'm in no position to try to re-do the DB structure/
layout/design.

Hmm, at the end you mention the auto close thingy. I just pulled the
db's with auto close and it's all the ones I attached. I ran the auto
close = burn and die (okay, I'm being goofy, I ran the alter database
db set auto_close off) and then ran my query and it was blazing fast.
That auto_close, from the name, would explain why having open
connections to the DB would fix the problem.

I'm going to reboot now (to make sure I've not done anything else that
might be affecting it) and then I'll check it and post back.

Thank you again for all your help and time.

--HC|||Problem: Performance difference between MSDE and SQL Express 2005|||HC (hboothe@.gte.net) writes:

Quote:

Originally Posted by

It turns out, as I'm sure you know, that AUTO_CLOSE defaults to on
ONLY for SQL Express (not for other versions of SQL 2005 (from my
reading of BOL)). Turning it off was a huge help to me and my
program.


As far as I know, AUTO_CLOSE is also on by default for MSDE. Probably
some time in the dim and distant path, you learnt about this, turned
it off, and by now you have forgotten all about it.

Happens to me too. Just watch this thread. It took me a couple of
posts to recognize the problem.

--
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|||On Feb 5, 4:20 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

HC (hboo...@.gte.net) writes:

Quote:

Originally Posted by

It turns out, as I'm sure you know, that AUTO_CLOSE defaults to on
ONLY for SQL Express (not for other versions of SQL 2005 (from my
reading of BOL)). Turning it off was a huge help to me and my
program.


>
As far as I know, AUTO_CLOSE is also on by default for MSDE. Probably
some time in the dim and distant path, you learnt about this, turned
it off, and by now you have forgotten all about it.
>
Happens to me too. Just watch this thread. It took me a couple of
posts to recognize the problem.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx


I know what you're saying but the auto_close is off. I checked my
databases and it's off and I'd remember this kind of hell. :) My
only offering is that I designed and built the databases using the SQL
Server Enterprise Manager that I have with SQL Server 2000 Enterprise
Edition so perhaps it is a function of whatever creates the database,
not in which data engine the database is created. At least with MSDE
2000.

I tried testing this by scripting an existing database and then using
OSQL to execute the script and make a new database on MSDE on my dev
machine (that I use all the time) but it still didn't mark the DB as
auto_close. But, you are right, MSDE is supposed to, by default, mark
the databases as auto_close on (I checked BOL). I'm not sure, but it
seems odd to me that the default behavior the BOL says I should see
isn't happening which makes me think I'm doing something wrong.

In any case, setting AUTO_CLOSE off on the DB's on Express (of which
all of mine were marked on) saved a ton of time on the queries. The
simple stuff (one join, one criterion) took 1,100 to 1,500 ms or so
repeatedly, now it takes less than 300 ms.

The problem I'm working on now is why my 2Ghz Centrino on a GB of RAM
running XP SP2 and MSDE 2000 on a 5,400 RPM disk kicks out my simple
join query, inside my program but with similar speed differences from
SQL Management Express, in about 3-5 hundreths of a second (0.03 -
0.05 seconds) but my Vista system running 2GB of RAM, Athlon 64 X2
dual core 3800+, on a 7,200 RPM disk and using SQL Express 2005 kicks
the same query, on the same data, with the same program, out in about
one tenth of a second (0.10). This isn't a huge problem in itself.
What makes this a problem are two things: 1) why any perf difference
to the negative on a faster machine with the new DB system (Express
vs. MSDE), and 2) the same tables used in that little query are used
in my big nasty query and the average time on my XP system with MSDE
is 17.433 seconds over 8 runs of the big query and on the Vista system
with Express the average time is 46.66 seconds.

Here's what I did, ran the same process on each of two machines, one
is the XP system I mention above, the other is the Vista system I
mention above. I ran the process 8 times on each system, each one
right after the other. I have incorporated a timer function in my
program that tracks how long it takes to run the process (grab the
data and pop it on screen). The time is capable of recording in
milliseconds. On the MSDE system (XP) I ran the process only 8 times
with the only index on either of the tables being the clustered
primary key index. On the Express system (Vista) I ran the process 24
times, 8 each of the following: no indexes other than the clustered
primary key index, 8 each of a clustered index on MedID (the primary
key from the table with 2,738 rows) and non-clustered indexes on
primary key and ResID, and 8 each of a clustered index on ResID, and
non-clustered on MedID and primary key. After each change of the
indexes I ran first the UPDATE STATISTICS tablewithchanges WITH
FULLSCAN and then executed SP_UPDATESTATS for that database. Each of
the three groups of 8 runs averaged about the same time, with the
lowest average of 45.7 and the highest of 46.8 seconds. Approximately
3 times longer than the time the XP and MSDE system took.

There are, perhaps, things I can do to make my process smaller or
faster, I suppose, but before I try changing what I'm doing I'd like
to understand what the difference is between MSDE and Express that is
causing the problem. My expectation from Express was that I would use
it with the databases I have currently and the same program and have
the same or better performance than what I had before. What I have is
a system that, while fast enough for the little query (one tenth of a
second is not bad) is unusable for my clients when that difference is
magnified to 45+ seconds for another. To summarize, I'm disappointed
that a process that was fast on prior version is slow on a newer
version and it makes me think, particularly in light of the info you
gave me on the AUTO_CLOSE, that there is yet something I don't know or
understand and that bugs me.

Anyway, I'm not sure there's a question in there, sorry.

Thank you, ad nauseum, for sticking with me and helping me with the
initial problem. That fix is huge to my ability to move to Vista and
Express.

--HC|||HC (hboothe@.gte.net) writes:

Quote:

Originally Posted by

I tried testing this by scripting an existing database and then using
OSQL to execute the script and make a new database on MSDE on my dev
machine (that I use all the time) but it still didn't mark the DB as
auto_close. But, you are right, MSDE is supposed to, by default, mark
the databases as auto_close on (I checked BOL). I'm not sure, but it
seems odd to me that the default behavior the BOL says I should see
isn't happening which makes me think I'm doing something wrong.


You might have turned off auto-close for the model database as well.
model is a template for all new databases.

As for the rest of your troubles, I'm afraid that I know too little about
your system to say much useful.

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

Problem writing data after upgrading

Hello,

I was using for years an application that had an MSDE database.

Recently. I've upgraded my computer to Windows Vista, so I had to upgrade MSDE to SQL Express.

I've managed to attach the old MSDE database of the application to SQL Express, and I am able to retrieve my data from within the application.

My problem is that I can not write or update data from within the application anymore. However, If I open a table in SQL Management Studio Express I can write/ update data with no problem.

The company that build the application I was using doesnt exist any more so I cant take any support from them.

The only thing I know for a fact is that the application is connecting to the database using the sa user.

Any ideas?

Thanks a lot in advance!!!

Tassos

hi,

that would need some deeper investigation. What are you experiencing during trying to update the database ? Do you get an error ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Just an error within the application saying "Update failed"|||That is a wrapped error of the application not a SQL Server error, you will either have to use the SQL Server profiler (which is NOT included in SQL Server Express) or change the application to expose the error messages which come directly from the server.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Problem writing data after upgrading

Hello,

I was using for years an application that had an MSDE database.

Recently. I've upgraded my computer to Windows Vista, so I had to upgrade MSDE to SQL Express.

I've managed to attach the old MSDE database of the application to SQL Express, and I am able to retrieve my data from within the application.

My problem is that I can not write or update data from within the application anymore. However, If I open a table in SQL Management Studio Express I can write/ update data with no problem.

The company that build the application I was using doesnt exist any more so I cant take any support from them.

The only thing I know for a fact is that the application is connecting to the database using the sa user.

Any ideas?

Thanks a lot in advance!!!

Tassos

hi,

that would need some deeper investigation. What are you experiencing during trying to update the database ? Do you get an error ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Just an error within the application saying "Update failed"|||That is a wrapped error of the application not a SQL Server error, you will either have to use the SQL Server profiler (which is NOT included in SQL Server Express) or change the application to expose the error messages which come directly from the server.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de