Where is the SQL Express install log?

The SQL Express installer (unlike MSDE) produces a log file by default. There is no need to specify extra parameters when running setup.exe.

The log gets written to:

%ProgramFiles%\Microsoft SQL Server\90\Setup Bootstrap\LOG\Summary.txt

For example, on my machine,

C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Summary.txt

Multiple other log files are written to the same folder, because the SQL Express install actually comprises multiple other installs.

Where is the MSDE install log?

Where you can find the MSDE install log depends on what options you chose when you ran your install.

By default, no log is produced when you run setup.exe to install MSDE. To get a log, you have to add the /L parameter.

To get the MSDE install to log:

1. In the MSDE installer folder, right-click Setup.exe and create a shortcut

2. View properties for the shortcut, and at the end of the Target string, add

/L*v C:\MSDE_setup.log

For example:

"C:\MSDE\setup.exe" /L*v C:\MSDE_setup.log

3. Install by running the shortcut.

Note that /L tells setup to produce a log. The *v parameter makes it a verbose log. And C:\MSDE_setup.log is the location of the log that will be produced.

You can use a different location and/or filename. Setup will overwrite the file if it already exists.

The procedure entry point ?SQLUIUpdateRegistryFromResource@@YAJHPAUHINSTANCE__@@IKPAPBG1@Z could not be located in the dynamic link library SQLRESLD.dll.

Had a customer the other day who was installing an app on Windows 2000 Professional. We don't see Windows 2000 much any more, even 2000 Server. Anyway, this particular machine had been around for years, and had a lot of old apps installed on it, including Microsoft SQL Server version 7.

Whenever the customer would launch our app, he'd get this error:

The procedure entry point ?SQLUIUpdateRegistryFromResource@@YAJHPAUHINSTANCE__@@IKPAPBG1@Z could not be located in the dynamic link library SQLRESLD.dll.

Then he'd click OK, and he'd get this error:

System.Runtime.InteropServices.COMException (0x8007007F): Retrieving the COM class factory for component with CLSID {10020200-E260-11CF-AE68-00AA004A34D5} FAILED DUE TO THE FOLLOWING ERROR: 8007007F.

This second error was recognizable - that's the class ID for SQL-DMO, which the app uses to communicate with SQL Server. We see that 0x8007007F error for that class ID all the time, on machines where SQL-DMO is not registered.

The usual solution is to go find SQLDMO.DLL in C:\Program Files\Microsoft SQL Server\80\Tools\Binn, and register it using regsvr32.exe. Or, sometimes we throw a copy of SQLDMO.DLL and SQLDMO.RLL in our own app folder, and register it there, just to get by.

(A quick note about registering DLLs: you can bring up a command window and navigate to the folder where the DLL resides, and then run regsvr32.exe sqldmo.dll, for example. But most of the time, I right-click on the DLL and choose Open With, and browse to regsvr32.exe in C:\Windows\System32, and make a permanent file type association. That way, from then on I can register any DLL just by double-clicking it. If I get an error that the DllRegisterServer entry point can't be found, then I know it didn't need registering in the first place.)

Back to the IKPAPBG1 issue. we tried to register SQL-DMO, and that failed. I don't have the error handy, but the gist of it was that some RLL couldn't be found at C:\Resources\1033. For this, we tried brute force, and copied

C:\Program Files\Microsoft SQL Server\MSSQL$INSTANCENAME\Binn\Resources\1033

to

C:\Resources\1033.

Not the "right" solution, but worth trying to see if it would get us up and running.

Tried to register SQLDMO.DLL again, and this time, the error thrown by regsvr32 was

C:\Program Files\Microsoft SQL Server\MSSQL$INSTANCENAME\Binn\sqlresld.dll
was loaded, but the DllUnregisterServer entry point was not found.

DllUnregisterserver may not be exported, or a corrupt version of C:\Program Files\Microsoft SQL Server\MSSQL$HDBID\Binn\sqlresld.dll may be in memory. Consider using PView to detect and remove it.

By this time, all the errors together were beginning point to a versioning problem. As if, for some reason, version 7 DLLs were being accessed instead of version 8 (2000). What would make that happen?

In Add/Remove programs, we could see that MSSQL 7 was still installed, although it wasn't running. We checked the Path environment variable, and noticed that C:\MSSQL7 was near the front of the list.

We considered removing it from the Path, since it wasn't in use, but decided instead to try renaming the C:\MSSQL7 folder so it wouldn't interfere. And that did the trick. We ran regsvr32.exe sqldmo.dll, and it worked, and we launched the app, and it worked too.

Solved. If this story helps you, post a comment and let me know!

Unable to open the physical file "C:\MyDatabase.mdf". Operating system error 5: "5(Access is denied.)"

One of the applications I test frequently detaches and re-attaches SQL Server databases. The app manages hundreds of databases all with identical schema, detachable for portability and ease of copying.

Originally the app only worked with SQL Server 2000 (or MSDE), but after SQL Server 2005 (SQL Express) had been available for a while, we decided to support it too. It turned out to be harder than we expected, because every time we detached a database, we were unable to attach it later.

The errors looked like this:

Unable to open the physical file "C:\MyDatabase.mdf". Operating system error 5: "5(Access is denied.)"

Turns out that SQL Server 2005, in order to better secure the data inside an MDF file, automatically resets file permissions whenever the MDF is detached. I got no problem with that -- it makes sense, when you think about how before, your data was always securable as long as you were accessing it through the SQL Server engine, but if you forgot to secure your MDFs and LDFs, anyone who got their hands on those could probably get whatever information they wanted out of them.

Just, for our app, it made life crazy. Permissions would get reset so that only the person who did the detach could re-attach. Or sometimes, a process running as a service under the Local System account would detach, and then nobody could manually re-attach.

Luckily, after a lot of research that took way too long, we discovered trace flag 1802. Trace flag 1802 turns off the permission-resetting behavior. This particular trace flag needs to be set in the startup properties for the instance, so that it is always in effect. If you set it dynamically using DBCC TRACEON, it won't stick through the next time you restart your instance.

To enable trace flag 1802:

1. Start > Programs > Microsoft SQL Server 2005 > Configuration Tools > SQL Server Configuration Manager

2. In the tree list on the left, click on SQL Server 2005 Services

3. On the right, right-click your SQL Server instance name, and choose Properties

4. In the Properties dialog, click on the Advanced tab

5. Find the property called Startup Parameters. There is a long string of parameters in this field. Click the dropdown arrow to view and edit the string.

6. At the end of the string, add ;-T1802

Cannot copy (filename): The filename, directory name, or volume label syntax is incorrect.

One of the products I test is available for download, in addition to being available on CD media. We use WinZip to create a single-file self-extracting downloadable version. Our customers are able to download a ProdName.exe file, and run it to extract the CD image to a local or network folder.

Sometimes, depending on where they extract it to, they get an error. The problem is that our product CD incorporates the SQL Express installer, which has a very deep folder structure of its own. The longest path in the product CD folder structure is 186 characters:

\Bin\3rdParty\SQLExpress\setup\program files\microsoft sql server\90\tools\binn \windows\winsxs\3kn09qps.lm8\x86_microsoft.vc80.mfcloc_1fc8b3b9a1e18e3b_ 8.0.50727.42_x-ww_3415f6d0.manifest

If someone attempts to extract the contents to a directory that has a long path already, they can get this error:

Cannot copy (filename): The filename, directory name, or volume label syntax is incorrect.

Or, if they're able to extract it successfully, sometimes they get this error when they try to copy the entire CD directory to a different location:

Can't create output file: (filename)

The errors occur because the maximum length of a Windows path is 260 characters.

The solution is to extract or copy the install to a folder with a shorter path name. In our case, because our sub-path takes up 186 characters, everything that comes before \Bin... must have a length not more than 74 characters.

How do you know what version of SQL Server you have?

To find out what version and service pack of SQL Server you are running, run this query:

SELECT @@VERSION

For example, I'm at home right now using an old laptop running Windows XP Home, and in the Services console I see only one SQL Server 2000 instance called MICROSOFTBCM.

I've never used BCM, but I know it stands for Business Contact Manager and it is part of Microsoft Office. Let's see what version of SQL Server it is, and what service pack is installed.

First, I open a command window and type:

c:> osql -S localhost\microsoftbcm -E

The response is:

[DBNETLIB]SQL Server does not exist or access denied.
[DBNETLIB]ConnectionOpen (Connect()).

I can see in the Services console that the MSSQL$MICROSOFTBCM service is Started, so I figure maybe TCP/IP connections are not enabled. In the command window, I type:

c:> svrnetcn

This brings up the SQL Server Network Utility. Sure enough, TCP/IP is in the list of Disabled Protocols. So I select it, click the Enable button, and click Apply. An alert tells me I need to restart the service in order for the change to take effect, so I switch over to the Services console and restart the service.

Now I try OSQL again, and this time I get connected. I type:

1> select @@version
2> go

The response is:

Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 2)

So now I can tell I'm running MSDE with SP2.

I think I'll disable TCP/IP again, since I don't really need it. I don't use this app. In fact, I'm going to disable the service so it won't start any more when I reboot.

Enterprise Manager snap-in failed

Once upon a time, there were days when I'd swear I installed & uninstalled MSDE fifty times. And sometimes I would discover, next time I tried to use SQL 2000 Enterprise Manager, that it wouldn't work any more. I'd get this error:

Enterprise Manager snap-in failed

When this happens, the quickest solution is to re-install Client Tools. If the SQL Server 2000 installer is handy, grab it and run a Client Tools Only install.

But often, that doesn't run smoothly. I get this error:

Setup has detected an existing client tools only installation. Please use the maintenance mode to add client components

To get past this, edit the registry.

1. Run regedit.

2. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\80\Tools

3. Change the value of DefClientOnlyInstalled to 0.

4. Re-install Client Tools. It should work, and it will probably set the value back to 1.

Now you can use Enterprise Manager again.

I have also read of another solution, that doesn't involve installing Client Tools again. This is useful if you don't have a SQL Server 2000 CD handy, or don't have access to an installer locally or on a network drive.

1. Run Regedit.

2. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\80\Tools\ClientSetup

3. On the right side, right-click and choose New-->String Value.

4. Name the new value SQLPath.

5. Double-click the SQLPath value to edit it. In the Value data field, enter:

C:\Program Files\Microsoft SQL Server\80\Tools

6. Click OK, then close Regedit.

7. At a command prompt, run:

regsvr32 "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\sqlmmc.dll"

I haven't had a chance to try this since I learned it, because it's been a while since I got a Snap-in failed to initialize error. If you try and it works for you, please let me know. Thanks.

How to run all SQL scripts in a folder

Suppose you have a set of SQL scripts for SQL Server that you want to run regularly. Place them all in a single folder, and then you can run a single command file to execute them all.

Here is an example:

for %z in (c:\Scripts\*.sql) do osql -S computername\instancename -U username -P password -d databasename -n -b -i %z

You can also do the same thing, except connect to the SQL Server using Windows Authentication instead of a SQL Server login using the -E parameter instead of -U and -P:

for %z in (c:\Scripts\*.sql) do osql -S computername\instancename -E -d databasename -n -b -i %z

Those dashed parameters (-S, -U, -P, -E, -d, -n, -b, -i) are for OSQL. Case matters. For example, -d is not the same as -D.

You can see all the possible OSQL parameters by running the command OSQL ?. For quick reference, here's a screenshot of what's returned when you run OSQL ?: