Hi there,
I've got something kind of weird going on. I have a stored procedure that
normally takes less than one second to execute, but it has started taking
over 30 seconds from time to time.
The SP is called by an ASP page on a web server, running on a separate
machine. The database server is running SQL Server 2000. The ASP page
makes an ODBC connection, using TCP/IP and creates & sends the stored
procedure call.
From time to time, something goes haywire, and the ASP page returns
[Microsoft][ODBC SQL Server Driver]Timeout expired
Then, the ASP page will not work no matter what we do, even if we leave it a
day or two. (No locks on the database, sometimes not even any connections!)
The only way to fix it is to run the stored procedure through Query
Analyzer. It will take a little over 30 seconds, but finish successfully.
Then, if I run the exact same SP again, it will complete in 0 seconds, and
the web page will work fine again.
I've seen this before, but I cannot for the life of me remember what we did
to fix it. We tried changing the ODBC connection to Named Pipes instead of
TCP/IP but that didn't help.
Any suggestions?
BeverleyI am having the same issue and the solution provided by SQL crafter in the S
ql Server Timeout expired post is not working. Could someone help us with t
his issue?
Thanks,
Rick|||I have been struggling with a similar problem for a couple of weeks and came
to this group in search of a solution. Perhaps a pooling of information
will help.
In my situation, I have a VB interactive front-end on multiple machines
accessing a SQL2K database through ODBC over TCP/IP. It has been in service
for over 2 years and the master table now has over 750K records in it.
Recently we started receiving occasional "Timeout Expired" errors in pretty
much the same manner as you describe -- a query that would normally take
1-2 seconds would suddenly take over 90 and crap out. I ran database
integrity checks, reconstructed the indexes, created additional indexes, ran
numerous original and reconfigured queries through the Index Analyzer. And
of course spent hours trying various searches in the MS KB. I have reached
the following conclusions:
The problem (in my case, at least) originates with the Query Optimizer. A
very specific query run through the Query Analyzer will *always* select an
appropriate index or mix of indexes. The *identical* query submitted
through ODBC, however, will sometimes select a completely inappropriate
index mix, or no index at all, wind up executing full-table scans or other
time-wasting substitutes, and time out. This effect, I suspect, is a
permanent feature of MSSQL, but does not manifest on smaller tables because
the malfunction does not cause a timeout on smaller tables, just an
inexcusable waste of time.
I proved this theory by adding index hints to my ODBC-originated queries
(which Books Online says should never be necessary), and observing the
performance stabilize. Problem is, I consider this a completely hokey
solution since if I ever decide to reconfigure my indexes, I will have
dozens of coordinating code changes to perform. Also, there are several
queries in which I cannot use index hints because the query requires
column-level 'OR'-ing with which hints are incompatible.
So I am still looking for a "proper" solution from one of you out there, or
at least an acknowledgment from MS that this is a behavioral issue with
SQL2K that needs to be addressed.
- Fred
"Beverley" <ali_webitems@.hotmail.com> wrote in message
news:OpxbH$K9DHA.712@.tk2msftngp13.phx.gbl...
> Hi there,
> I've got something kind of weird going on. I have a stored procedure that
> normally takes less than one second to execute, but it has started taking
> over 30 seconds from time to time.
> The SP is called by an ASP page on a web server, running on a separate
> machine. The database server is running SQL Server 2000. The ASP page
> makes an ODBC connection, using TCP/IP and creates & sends the stored
> procedure call.
> From time to time, something goes haywire, and the ASP page returns
> [Microsoft][ODBC SQL Server Driver]Timeout expired
> Then, the ASP page will not work no matter what we do, even if we leave it
a
> day or two. (No locks on the database, sometimes not even any
connections!)
> The only way to fix it is to run the stored procedure through Query
> Analyzer. It will take a little over 30 seconds, but finish successfully.
> Then, if I run the exact same SP again, it will complete in 0 seconds, and
> the web page will work fine again.
> I've seen this before, but I cannot for the life of me remember what we
did
> to fix it. We tried changing the ODBC connection to Named Pipes instead
of
> TCP/IP but that didn't help.
> Any suggestions?
> Beverley
>|||I have been struggling with a similar problem for a couple of weeks and
came
to this group in search of a solution. Perhaps a pooling of information
will help.
In my situation, I have a VB interactive front-end on multiple machines
accessing a SQL2K database through ODBC over TCP/IP. It has been in
service
for over 2 years and the master table now has over 750K records in it.
Recently we started receiving occasional "Timeout Expired" errors in pretty
much the same manner as you describe -- a query that would normally take
1-2 seconds would suddenly take over 90 and crap out. I ran database
integrity checks, reconstructed the indexes, created additional indexes,
ran
numerous original and reconfigured queries through the Index Analyzer. And
of course spent hours trying various searches in the MS KB. I have reached
the following conclusions:
The problem (in my case, at least) originates with the Query Optimizer. A
very specific query run through the Query Analyzer will *always* select an
appropriate index or mix of indexes. The *identical* query submitted
through ODBC, however, will sometimes select a completely inappropriate
index mix, or no index at all, wind up executing full-table scans or other
time-wasting substitutes, and time out. This effect, I suspect, is a
permanent feature of MSSQL, but does not manifest on smaller tables because
the malfunction does not cause a timeout on smaller tables, just an
inexcusable waste of time.
I proved this theory by adding index hints to my ODBC-originated queries
(which Books Online says should never be necessary), and observing the
performance stabilize. Problem is, I consider this a completely hokey
solution since if I ever decide to reconfigure my indexes, I will have
dozens of coordinating code changes to perform. Also, there are several
queries in which I cannot use index hints because the query requires
column-level 'OR'-ing with which hints are incompatible.
So I am still looking for a "proper" solution from one of you out there, or
at least an acknowledgment from MS that this is a behavioral issue with
SQL2K that needs to be addressed.
- ITFred|||ITFRED, have you found a solution to your problem at all'
I have a similar problem, I also get the *occasional timeout*.
Mine is an ASP web application connected to MSDE, and the application
gets a timeout error occasionally when the ASP code tries to update
many records in a table, but smaller tables work fine.
The error message is:
> Error Number -214xxxxxx, ODBC SQL Server Driver timeout expired.
In a testing environment on a dev server, the same ASP application
connects to a SQL2K database and it has NOT encountered any problems.
My conclusion is (thanks to all your posts I have read) that it is in
ODBC connections where the MSDE engine times out.
Can anyone please come up with a solution!'
Is using a different database an option, perhaps mysql or postgreSQL?
Thanks.
huge
ITFred wrote:
> *I have been struggling with a similar problem for a couple of weeks
> and
> came
> to this group in search of a solution. Perhaps a pooling of
> information
> will help.
> In my situation, I have a VB interactive front-end on multiple
> machines
> accessing a SQL2K database through ODBC over TCP/IP. It has been in
> service
> for over 2 years and the master table now has over 750K records in
> it.
> Recently we started receiving occasional "Timeout Expired" errors in
> pretty
> much the same manner as you describe -- a query that would normally
> take
> 1-2 seconds would suddenly take over 90 and crap out. I ran
> database
> integrity checks, reconstructed the indexes, created additional
> indexes,
> ran
> numerous original and reconfigured queries through the Index
> Analyzer. And
> of course spent hours trying various searches in the MS KB. I have
> reached
> the following conclusions:
> The problem (in my case, at least) originates with the Query
> Optimizer. A
> very specific query run through the Query Analyzer will *always*
> select an
> appropriate index or mix of indexes. The *identical* query
> submitted
> through ODBC, however, will sometimes select a completely
> inappropriate
> index mix, or no index at all, wind up executing full-table scans or
> other
> time-wasting substitutes, and time out. This effect, I suspect, is
> a
> permanent feature of MSSQL, but does not manifest on smaller tables
> because
> the malfunction does not cause a timeout on smaller tables, just an
> inexcusable waste of time.
> I proved this theory by adding index hints to my ODBC-originated
> queries
> (which Books Online says should never be necessary), and observing
> the
> performance stabilize. Problem is, I consider this a completely
> hokey
> solution since if I ever decide to reconfigure my indexes, I will
> have
> dozens of coordinating code changes to perform. Also, there are
> several
> queries in which I cannot use index hints because the query requires
> column-level 'OR'-ing with which hints are incompatible.
> So I am still looking for a "proper" solution from one of you out
> there, or
> at least an acknowledgment from MS that this is a behavioral issue
> with
> SQL2K that needs to be addressed.
> - ITFred *
huge
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message397556.html
Showing posts with label second. Show all posts
Showing posts with label second. Show all posts
Friday, March 9, 2012
Monday, February 20, 2012
Obscure error while attempting to install SQL Server 2005 SP1
I successfully installed SQL Server 2005 SP1 on one PC. When I attempt to install it on a second PC, however, I get the following obscure error:
Unexpected Error Occurred
The following unexpected error occurred:
That's right, there's nothing else listed, other an OK button. D'oh! When I press the OK button, the following error report dialog box appears:
Hotfix.exe
A recently applied update, NULL, failed to install.
There are no pending updates that I'm aware of, as I just booted the PC. I sent the error report.
I notice on this particular PC that the SP1 install EXE is expanding/writing to an external SCSI drive; would that make a difference? I don't see a way to specify a different (i.e., internal ATA drive).
Any other ideas?
You need to look in the setup log files to track down the problem. They're located here:
%\Program Files%\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files
There's also a summary.txt file up one directory and it may point you to the right log file. If you have trouble interpreting the results, please post the errors here and we'll see if we can help you out.
Paul
|||Thanks. Before I delve further into the log file, I noticed that Summary.txt indicates everything is fine, although I don't understand the references to "Uninstall":
Microsoft SQL Server 2005 9.00.1399.06
==============================
OS Version : Microsoft Windows XP Professional Service Pack 2 (Build 2600)
Time : Fri Apr 14 00:07:10 2006
Machine : PC001
Product : Microsoft SQL Server 2005 Express Edition
Product Version : 9.00.1399.06
Uninstall : Successful , Reboot required
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0003_PC001_SQL.log
--
Machine : PC001
Product : Microsoft SQL Server Setup Support Files (English)
Product Version : 9.00.1399.06
Uninstall : Successful
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0003_PC001_SQLSupport_1.log
--
Machine : PC001
Product : Microsoft SQL Server VSS Writer
Product Version : 9.00.1399.06
Uninstall : Successful
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0003_PC001_SqlWriter_1.log
--
Machine : PC001
Product : MSXML 6.0 Parser
Product Version : 6.00.3883.8
Uninstall : Successful
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0003_PC001_MSXML6_1.log
--
Setup succeeded with the installation, inspect the log file completely for status on all the components.Time : Fri Apr 14 01:30:27 2006
I'm not sure why the first section says "Microsoft SQL Server 2005 Express Edition". I don't have the Express Edition installed on this PC, although I installed and uninstalled it a few months ago. Indeed, the Express Edition doesn't appear within the "Add or Remove Programs" applet.
I welcome your suggestions as to how to proceed.
|||If it's any comfort, you are not the only one with this error.
Installed fine the first time on my development server where it is a clean install of Windows 2003 and SQL 2005. However on the pc’s I have attempted to install on, where there is Visual Studio and Office installed it refuses to install.
I have tried on 3 other PC's and they all suffer the same way.
|||
Problem solved.
I took the time over the weekend ( I need to get a life) to build a clean OS install and install SQL 2005 developer edition and then install the service pack to see when and where it broke.
The issue seems to be associated with the fact that the SQL 2005 developer edition I was trying to upgrade was from the ISO image downloaded from the MSDN website. I uninstalled the ISO image and installed SQL from the MSDN subscription CD's and the service pack installs fine.
Can someone at Microsoft please confirm this is an issue!
|||
Interesting observation, Des; thanks. My install came from the ISO image downloaded from the MSDN website, also.
How do you know that simply uninstalling and re-installing didn't resolve the problem?
|||I actually reinstalled from the ISO image first with the same result.
I then went hunting through boxes of MSDN disks till I found the real disks, and reinstalled from the MSDN media. Once installed from the MSDN media the service pack installed first go.
|||We have ugraded one server with SP1 for SQL 2005 without any issues,but when we performed the same task on other server with the same sp1 setup,we encountered the problem which you experienced "The following unexpected error occurred" Both the servers are installed from the same ISO image of MSDN, Herby I attach the log of the hotfix which I executed.
05/26/2006 10:24:48.546 ================================================================================
05/26/2006 10:24:48.546 Hotfix package launched
05/26/2006 10:24:48.546 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
05/26/2006 10:24:48.546 Successfully read registry key: CommonFilesDir, string value = C:\Program Files\Common Files
05/26/2006 10:24:48.546 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
05/26/2006 10:24:48.546 Successfully read registry key: ProgramFilesDir, string value = C:\Program Files
05/26/2006 10:24:48.609 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
05/26/2006 10:24:48.609 Successfully read registry key: CommonFilesDir, string value = C:\Program Files\Common Files
05/26/2006 10:24:48.609 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
05/26/2006 10:24:48.609 Successfully read registry key: ProgramFilesDir, string value = C:\Program Files
05/26/2006 10:24:48.609 Local Computer:
05/26/2006 10:24:48.609 Target Details: SRC-R-AS10
05/26/2006 10:24:48.609 commonfilesdir = C:\Program Files\Common Files
05/26/2006 10:24:48.609 lcidsupportdir = s:\0596decce11fd6b46f501bbd4cfb\1033
05/26/2006 10:24:48.609 programfilesdir = C:\Program Files
05/26/2006 10:24:48.609 supportdir = \\SRC-R-AS10\s$\0596decce11fd6b46f501bbd4cfb
05/26/2006 10:24:48.609 supportdirlocal = s:\0596decce11fd6b46f501bbd4cfb
05/26/2006 10:24:48.609 windir = C:\WINDOWS
05/26/2006 10:24:48.609 winsysdir = C:\WINDOWS\system32
05/26/2006 10:24:48.609
05/26/2006 10:24:48.656 Enumerating applicable products for this patch
05/26/2006 10:24:53.046 The patch installation could not proceed due to unexpected errors
05/26/2006 10:24:53.046
05/26/2006 10:24:53.046 Product Status Summary:
05/26/2006 10:24:53.171 Hotfix package closed
We experience the same error while installing the hotfix AS2005-KB914595-x86-ENU.exe (this hotfix was installed on other server which upgraded successfully.)
Reinstalling is not a feasible option for our environment.Kindly suggest.
~Vishal
|||I received the same "A recently applied update, NULL, failed to install." error when attempting to install SP1 on SQL Server 2005 Enterprise version. As previously noted, I also uninstalled the ISO version from MSDN and re-installed from the MSDN DVD.
Following the reinstall, I attempted to run the individual hotfixes, starting with sql2005-kb918222-x86-enu.exe. Once again, I received the error.
Finally, I attempted to run the fixed patch noted in KB914595 and received a slightly different message but with the same, unsatisfactory results. The message has now changed to "Uninstallation of the 'NULL' update failed". Note that the installer chose to place the support directory on a SAN connection (F:). Could this be a lead toward a solution?
Here's the log file from the %WINDIR%\Hotfix folder:
08/05/2006 16:22:34.875 ================================================================================
08/05/2006 16:22:34.875 Hotfix package launched
08/05/2006 16:22:34.890 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
08/05/2006 16:22:34.890 Successfully read registry key: CommonFilesDir, string value = C:\Program Files\Common Files
08/05/2006 16:22:34.890 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
08/05/2006 16:22:34.890 Successfully read registry key: ProgramFilesDir, string value = C:\Program Files
08/05/2006 16:22:35.031 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
08/05/2006 16:22:35.031 Successfully read registry key: CommonFilesDir, string value = C:\Program Files\Common Files
08/05/2006 16:22:35.031 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
08/05/2006 16:22:35.031 Successfully read registry key: ProgramFilesDir, string value = C:\Program Files
08/05/2006 16:22:35.031 Local Computer:
08/05/2006 16:22:35.031 Target Details: EMERALD
08/05/2006 16:22:35.031 commonfilesdir = C:\Program Files\Common Files
08/05/2006 16:22:35.031 lcidsupportdir = f:\074fcbd1c0e3fd87748b2a9b748b9208\1033
08/05/2006 16:22:35.031 programfilesdir = C:\Program Files
08/05/2006 16:22:35.031 supportdir = \\EMERALD\f$\074fcbd1c0e3fd87748b2a9b748b9208
08/05/2006 16:22:35.031 supportdirlocal = f:\074fcbd1c0e3fd87748b2a9b748b9208
08/05/2006 16:22:35.031 windir = C:\WINDOWS
08/05/2006 16:22:35.031 winsysdir = C:\WINDOWS\system32
08/05/2006 16:22:35.031
08/05/2006 16:22:35.093 Enumerating applicable products for this patch
08/05/2006 16:22:36.625 The patch installation could not proceed due to unexpected errors
08/05/2006 16:22:36.625
08/05/2006 16:22:36.625 Product Status Summary:
08/05/2006 16:22:36.843 Hotfix package closed
I think I have a solution to the problem 
After installing/uninstalling SQL Server several times and trying to install SP1, about a dozen or more times, I noticed that in the Hotfix log details (see my earlier posting) that the "lcidsupportdir", "supportdirlocal" and "supportdir" all referred to drive "F:". In the Hotfix log details posted by Vishal_LogicalCMG, these same values refer to drive "S:". On the system where SP-1 was failing, drive "F:" is connected to a SAN (I wonder if there's significance to the drive letter "S:" in Vishal's post?).
Since Hotfix.exe doesn't provide a parameter to specify these values, I guessed that Hotfix.exe simply looks for the drive with the most free space for its workspace. On my system, the SAN had a lot more space than the boot drive. I quickly whipped up a script that created a 1GB file and repeatedly made copies of it on drive F: until drive C: had more free space. (I know it's a hack, but I was desparate by now!)
You guessed it - Hotfix.exe now placed its workfiles on the root drive C: and SQL Server 2005 Enterprise version is running with SP-1 installed! It looks like Hotfix.exe doesn't like to work across a SAN.
Microsoft ... are you listening?
Dave
|||Thank you for help. I had the same problem with installing SP1 for SQL Server 2005. It was caused by running installation from USB harddrive. When I coppied SP1 installation to C: then SP1 and post-SP1 hotfixes installed successfully.|||Dave,
Thanks for your post. I had the same issue, but I was not using a SANS, i just had a second drive that had more free space than the C drive. When i filled up that drive the install worked just fine.
Gotta love the QA over at Microsoft sometimes...... DUH.
Thanks again.
|||An easy workaround is to set up a 1 kb Disk Quota on the Larger Drive, which will force the MSI installer to use the C: drive. :)|||Thanks a million Dave! Genious Stuff
I would have never thought of this solution...but finally I too managed to fix this weird issue with the help of your post.
Cheers!
|||The disk quote workaround did not work for me....
However, here is a slightly less kludgy solution is to go to the properties for each SAN drive (M:, N:, O:, etc) -> Security -> Add
your current username to the list, and Deny -> Full Control. Do this for
each SAN drive and the installer will be forced to use the C: drive. Yay
Caleb
Obscure error while attempting to install SQL Server 2005 SP1
I successfully installed SQL Server 2005 SP1 on one PC. When I attempt to install it on a second PC, however, I get the following obscure error:
Unexpected Error Occurred
The following unexpected error occurred:
That's right, there's nothing else listed, other an OK button. D'oh! When I press the OK button, the following error report dialog box appears:
Hotfix.exe
A recently applied update, NULL, failed to install.
There are no pending updates that I'm aware of, as I just booted the PC. I sent the error report.
I notice on this particular PC that the SP1 install EXE is expanding/writing to an external SCSI drive; would that make a difference? I don't see a way to specify a different (i.e., internal ATA drive).
Any other ideas?
You need to look in the setup log files to track down the problem. They're located here:
%\Program Files%\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files
There's also a summary.txt file up one directory and it may point you to the right log file. If you have trouble interpreting the results, please post the errors here and we'll see if we can help you out.
Paul
|||Thanks. Before I delve further into the log file, I noticed that Summary.txt indicates everything is fine, although I don't understand the references to "Uninstall":
Microsoft SQL Server 2005 9.00.1399.06
==============================
OS Version : Microsoft Windows XP Professional Service Pack 2 (Build 2600)
Time : Fri Apr 14 00:07:10 2006
Machine : PC001
Product : Microsoft SQL Server 2005 Express Edition
Product Version : 9.00.1399.06
Uninstall : Successful , Reboot required
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0003_PC001_SQL.log
--
Machine : PC001
Product : Microsoft SQL Server Setup Support Files (English)
Product Version : 9.00.1399.06
Uninstall : Successful
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0003_PC001_SQLSupport_1.log
--
Machine : PC001
Product : Microsoft SQL Server VSS Writer
Product Version : 9.00.1399.06
Uninstall : Successful
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0003_PC001_SqlWriter_1.log
--
Machine : PC001
Product : MSXML 6.0 Parser
Product Version : 6.00.3883.8
Uninstall : Successful
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0003_PC001_MSXML6_1.log
--
Setup succeeded with the installation, inspect the log file completely for status on all the components.Time : Fri Apr 14 01:30:27 2006
I'm not sure why the first section says "Microsoft SQL Server 2005 Express Edition". I don't have the Express Edition installed on this PC, although I installed and uninstalled it a few months ago. Indeed, the Express Edition doesn't appear within the "Add or Remove Programs" applet.
I welcome your suggestions as to how to proceed.
|||If it's any comfort, you are not the only one with this error.
Installed fine the first time on my development server where it is a clean install of Windows 2003 and SQL 2005. However on the pc’s I have attempted to install on, where there is Visual Studio and Office installed it refuses to install.
I have tried on 3 other PC's and they all suffer the same way.
|||
Problem solved.
I took the time over the weekend ( I need to get a life) to build a clean OS install and install SQL 2005 developer edition and then install the service pack to see when and where it broke.
The issue seems to be associated with the fact that the SQL 2005 developer edition I was trying to upgrade was from the ISO image downloaded from the MSDN website. I uninstalled the ISO image and installed SQL from the MSDN subscription CD's and the service pack installs fine.
Can someone at Microsoft please confirm this is an issue!
|||
Interesting observation, Des; thanks. My install came from the ISO image downloaded from the MSDN website, also.
How do you know that simply uninstalling and re-installing didn't resolve the problem?
|||I actually reinstalled from the ISO image first with the same result.
I then went hunting through boxes of MSDN disks till I found the real disks, and reinstalled from the MSDN media. Once installed from the MSDN media the service pack installed first go.
|||We have ugraded one server with SP1 for SQL 2005 without any issues,but when we performed the same task on other server with the same sp1 setup,we encountered the problem which you experienced "The following unexpected error occurred" Both the servers are installed from the same ISO image of MSDN, Herby I attach the log of the hotfix which I executed.
05/26/2006 10:24:48.546 ================================================================================
05/26/2006 10:24:48.546 Hotfix package launched
05/26/2006 10:24:48.546 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
05/26/2006 10:24:48.546 Successfully read registry key: CommonFilesDir, string value = C:\Program Files\Common Files
05/26/2006 10:24:48.546 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
05/26/2006 10:24:48.546 Successfully read registry key: ProgramFilesDir, string value = C:\Program Files
05/26/2006 10:24:48.609 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
05/26/2006 10:24:48.609 Successfully read registry key: CommonFilesDir, string value = C:\Program Files\Common Files
05/26/2006 10:24:48.609 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
05/26/2006 10:24:48.609 Successfully read registry key: ProgramFilesDir, string value = C:\Program Files
05/26/2006 10:24:48.609 Local Computer:
05/26/2006 10:24:48.609 Target Details: SRC-R-AS10
05/26/2006 10:24:48.609 commonfilesdir = C:\Program Files\Common Files
05/26/2006 10:24:48.609 lcidsupportdir = s:\0596decce11fd6b46f501bbd4cfb\1033
05/26/2006 10:24:48.609 programfilesdir = C:\Program Files
05/26/2006 10:24:48.609 supportdir = \\SRC-R-AS10\s$\0596decce11fd6b46f501bbd4cfb
05/26/2006 10:24:48.609 supportdirlocal = s:\0596decce11fd6b46f501bbd4cfb
05/26/2006 10:24:48.609 windir = C:\WINDOWS
05/26/2006 10:24:48.609 winsysdir = C:\WINDOWS\system32
05/26/2006 10:24:48.609
05/26/2006 10:24:48.656 Enumerating applicable products for this patch
05/26/2006 10:24:53.046 The patch installation could not proceed due to unexpected errors
05/26/2006 10:24:53.046
05/26/2006 10:24:53.046 Product Status Summary:
05/26/2006 10:24:53.171 Hotfix package closed
We experience the same error while installing the hotfix AS2005-KB914595-x86-ENU.exe (this hotfix was installed on other server which upgraded successfully.)
Reinstalling is not a feasible option for our environment.Kindly suggest.
~Vishal
|||I received the same "A recently applied update, NULL, failed to install." error when attempting to install SP1 on SQL Server 2005 Enterprise version. As previously noted, I also uninstalled the ISO version from MSDN and re-installed from the MSDN DVD.
Following the reinstall, I attempted to run the individual hotfixes, starting with sql2005-kb918222-x86-enu.exe. Once again, I received the error.
Finally, I attempted to run the fixed patch noted in KB914595 and received a slightly different message but with the same, unsatisfactory results. The message has now changed to "Uninstallation of the 'NULL' update failed". Note that the installer chose to place the support directory on a SAN connection (F:). Could this be a lead toward a solution?
Here's the log file from the %WINDIR%\Hotfix folder:
08/05/2006 16:22:34.875 ================================================================================
08/05/2006 16:22:34.875 Hotfix package launched
08/05/2006 16:22:34.890 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
08/05/2006 16:22:34.890 Successfully read registry key: CommonFilesDir, string value = C:\Program Files\Common Files
08/05/2006 16:22:34.890 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
08/05/2006 16:22:34.890 Successfully read registry key: ProgramFilesDir, string value = C:\Program Files
08/05/2006 16:22:35.031 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
08/05/2006 16:22:35.031 Successfully read registry key: CommonFilesDir, string value = C:\Program Files\Common Files
08/05/2006 16:22:35.031 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
08/05/2006 16:22:35.031 Successfully read registry key: ProgramFilesDir, string value = C:\Program Files
08/05/2006 16:22:35.031 Local Computer:
08/05/2006 16:22:35.031 Target Details: EMERALD
08/05/2006 16:22:35.031 commonfilesdir = C:\Program Files\Common Files
08/05/2006 16:22:35.031 lcidsupportdir = f:\074fcbd1c0e3fd87748b2a9b748b9208\1033
08/05/2006 16:22:35.031 programfilesdir = C:\Program Files
08/05/2006 16:22:35.031 supportdir = \\EMERALD\f$\074fcbd1c0e3fd87748b2a9b748b9208
08/05/2006 16:22:35.031 supportdirlocal = f:\074fcbd1c0e3fd87748b2a9b748b9208
08/05/2006 16:22:35.031 windir = C:\WINDOWS
08/05/2006 16:22:35.031 winsysdir = C:\WINDOWS\system32
08/05/2006 16:22:35.031
08/05/2006 16:22:35.093 Enumerating applicable products for this patch
08/05/2006 16:22:36.625 The patch installation could not proceed due to unexpected errors
08/05/2006 16:22:36.625
08/05/2006 16:22:36.625 Product Status Summary:
08/05/2006 16:22:36.843 Hotfix package closed
I think I have a solution to the problem 
After installing/uninstalling SQL Server several times and trying to install SP1, about a dozen or more times, I noticed that in the Hotfix log details (see my earlier posting) that the "lcidsupportdir", "supportdirlocal" and "supportdir" all referred to drive "F:". In the Hotfix log details posted by Vishal_LogicalCMG, these same values refer to drive "S:". On the system where SP-1 was failing, drive "F:" is connected to a SAN (I wonder if there's significance to the drive letter "S:" in Vishal's post?).
Since Hotfix.exe doesn't provide a parameter to specify these values, I guessed that Hotfix.exe simply looks for the drive with the most free space for its workspace. On my system, the SAN had a lot more space than the boot drive. I quickly whipped up a script that created a 1GB file and repeatedly made copies of it on drive F: until drive C: had more free space. (I know it's a hack, but I was desparate by now!)
You guessed it - Hotfix.exe now placed its workfiles on the root drive C: and SQL Server 2005 Enterprise version is running with SP-1 installed! It looks like Hotfix.exe doesn't like to work across a SAN.
Microsoft ... are you listening?
Dave
|||Thank you for help. I had the same problem with installing SP1 for SQL Server 2005. It was caused by running installation from USB harddrive. When I coppied SP1 installation to C: then SP1 and post-SP1 hotfixes installed successfully.|||Dave,
Thanks for your post. I had the same issue, but I was not using a SANS, i just had a second drive that had more free space than the C drive. When i filled up that drive the install worked just fine.
Gotta love the QA over at Microsoft sometimes...... DUH.
Thanks again.
|||An easy workaround is to set up a 1 kb Disk Quota on the Larger Drive, which will force the MSI installer to use the C: drive. :)|||Thanks a million Dave! Genious Stuff
I would have never thought of this solution...but finally I too managed to fix this weird issue with the help of your post.
Cheers!
However, here is a slightly less kludgy solution is to go to the properties for each SAN drive (M:, N:, O:, etc) -> Security -> Add your current username to the list, and Deny -> Full Control. Do this for each SAN drive and the installer will be forced to use the C: drive. Yay
Caleb
Obscure error while attempting to install SQL Server 2005 SP1
I successfully installed SQL Server 2005 SP1 on one PC. When I attempt to install it on a second PC, however, I get the following obscure error:
Unexpected Error Occurred
The following unexpected error occurred:
That's right, there's nothing else listed, other an OK button. D'oh! When I press the OK button, the following error report dialog box appears:
Hotfix.exe
A recently applied update, NULL, failed to install.
There are no pending updates that I'm aware of, as I just booted the PC. I sent the error report.
I notice on this particular PC that the SP1 install EXE is expanding/writing to an external SCSI drive; would that make a difference? I don't see a way to specify a different (i.e., internal ATA drive).
Any other ideas?
You need to look in the setup log files to track down the problem. They're located here:
%\Program Files%\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files
There's also a summary.txt file up one directory and it may point you to the right log file. If you have trouble interpreting the results, please post the errors here and we'll see if we can help you out.
Paul
|||Thanks. Before I delve further into the log file, I noticed that Summary.txt indicates everything is fine, although I don't understand the references to "Uninstall":
Microsoft SQL Server 2005 9.00.1399.06
==============================
OS Version : Microsoft Windows XP Professional Service Pack 2 (Build 2600)
Time : Fri Apr 14 00:07:10 2006
Machine : PC001
Product : Microsoft SQL Server 2005 Express Edition
Product Version : 9.00.1399.06
Uninstall : Successful , Reboot required
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0003_PC001_SQL.log
--
Machine : PC001
Product : Microsoft SQL Server Setup Support Files (English)
Product Version : 9.00.1399.06
Uninstall : Successful
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0003_PC001_SQLSupport_1.log
--
Machine : PC001
Product : Microsoft SQL Server VSS Writer
Product Version : 9.00.1399.06
Uninstall : Successful
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0003_PC001_SqlWriter_1.log
--
Machine : PC001
Product : MSXML 6.0 Parser
Product Version : 6.00.3883.8
Uninstall : Successful
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0003_PC001_MSXML6_1.log
--
Setup succeeded with the installation, inspect the log file completely for status on all the components.Time : Fri Apr 14 01:30:27 2006
I'm not sure why the first section says "Microsoft SQL Server 2005 Express Edition". I don't have the Express Edition installed on this PC, although I installed and uninstalled it a few months ago. Indeed, the Express Edition doesn't appear within the "Add or Remove Programs" applet.
I welcome your suggestions as to how to proceed.
|||If it's any comfort, you are not the only one with this error.
Installed fine the first time on my development server where it is a clean install of Windows 2003 and SQL 2005. However on the pc’s I have attempted to install on, where there is Visual Studio and Office installed it refuses to install.
I have tried on 3 other PC's and they all suffer the same way.
|||
Problem solved.
I took the time over the weekend ( I need to get a life) to build a clean OS install and install SQL 2005 developer edition and then install the service pack to see when and where it broke.
The issue seems to be associated with the fact that the SQL 2005 developer edition I was trying to upgrade was from the ISO image downloaded from the MSDN website. I uninstalled the ISO image and installed SQL from the MSDN subscription CD's and the service pack installs fine.
Can someone at Microsoft please confirm this is an issue!
|||
Interesting observation, Des; thanks. My install came from the ISO image downloaded from the MSDN website, also.
How do you know that simply uninstalling and re-installing didn't resolve the problem?
|||I actually reinstalled from the ISO image first with the same result.
I then went hunting through boxes of MSDN disks till I found the real disks, and reinstalled from the MSDN media. Once installed from the MSDN media the service pack installed first go.
|||We have ugraded one server with SP1 for SQL 2005 without any issues,but when we performed the same task on other server with the same sp1 setup,we encountered the problem which you experienced "The following unexpected error occurred" Both the servers are installed from the same ISO image of MSDN, Herby I attach the log of the hotfix which I executed.
05/26/2006 10:24:48.546 ================================================================================
05/26/2006 10:24:48.546 Hotfix package launched
05/26/2006 10:24:48.546 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
05/26/2006 10:24:48.546 Successfully read registry key: CommonFilesDir, string value = C:\Program Files\Common Files
05/26/2006 10:24:48.546 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
05/26/2006 10:24:48.546 Successfully read registry key: ProgramFilesDir, string value = C:\Program Files
05/26/2006 10:24:48.609 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
05/26/2006 10:24:48.609 Successfully read registry key: CommonFilesDir, string value = C:\Program Files\Common Files
05/26/2006 10:24:48.609 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
05/26/2006 10:24:48.609 Successfully read registry key: ProgramFilesDir, string value = C:\Program Files
05/26/2006 10:24:48.609 Local Computer:
05/26/2006 10:24:48.609 Target Details: SRC-R-AS10
05/26/2006 10:24:48.609 commonfilesdir = C:\Program Files\Common Files
05/26/2006 10:24:48.609 lcidsupportdir = s:\0596decce11fd6b46f501bbd4cfb\1033
05/26/2006 10:24:48.609 programfilesdir = C:\Program Files
05/26/2006 10:24:48.609 supportdir = \\SRC-R-AS10\s$\0596decce11fd6b46f501bbd4cfb
05/26/2006 10:24:48.609 supportdirlocal = s:\0596decce11fd6b46f501bbd4cfb
05/26/2006 10:24:48.609 windir = C:\WINDOWS
05/26/2006 10:24:48.609 winsysdir = C:\WINDOWS\system32
05/26/2006 10:24:48.609
05/26/2006 10:24:48.656 Enumerating applicable products for this patch
05/26/2006 10:24:53.046 The patch installation could not proceed due to unexpected errors
05/26/2006 10:24:53.046
05/26/2006 10:24:53.046 Product Status Summary:
05/26/2006 10:24:53.171 Hotfix package closed
We experience the same error while installing the hotfix AS2005-KB914595-x86-ENU.exe (this hotfix was installed on other server which upgraded successfully.)
Reinstalling is not a feasible option for our environment.Kindly suggest.
~Vishal
|||I received the same "A recently applied update, NULL, failed to install." error when attempting to install SP1 on SQL Server 2005 Enterprise version. As previously noted, I also uninstalled the ISO version from MSDN and re-installed from the MSDN DVD.
Following the reinstall, I attempted to run the individual hotfixes, starting with sql2005-kb918222-x86-enu.exe. Once again, I received the error.
Finally, I attempted to run the fixed patch noted in KB914595 and received a slightly different message but with the same, unsatisfactory results. The message has now changed to "Uninstallation of the 'NULL' update failed". Note that the installer chose to place the support directory on a SAN connection (F:). Could this be a lead toward a solution?
Here's the log file from the %WINDIR%\Hotfix folder:
08/05/2006 16:22:34.875 ================================================================================
08/05/2006 16:22:34.875 Hotfix package launched
08/05/2006 16:22:34.890 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
08/05/2006 16:22:34.890 Successfully read registry key: CommonFilesDir, string value = C:\Program Files\Common Files
08/05/2006 16:22:34.890 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
08/05/2006 16:22:34.890 Successfully read registry key: ProgramFilesDir, string value = C:\Program Files
08/05/2006 16:22:35.031 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
08/05/2006 16:22:35.031 Successfully read registry key: CommonFilesDir, string value = C:\Program Files\Common Files
08/05/2006 16:22:35.031 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
08/05/2006 16:22:35.031 Successfully read registry key: ProgramFilesDir, string value = C:\Program Files
08/05/2006 16:22:35.031 Local Computer:
08/05/2006 16:22:35.031 Target Details: EMERALD
08/05/2006 16:22:35.031 commonfilesdir = C:\Program Files\Common Files
08/05/2006 16:22:35.031 lcidsupportdir = f:\074fcbd1c0e3fd87748b2a9b748b9208\1033
08/05/2006 16:22:35.031 programfilesdir = C:\Program Files
08/05/2006 16:22:35.031 supportdir = \\EMERALD\f$\074fcbd1c0e3fd87748b2a9b748b9208
08/05/2006 16:22:35.031 supportdirlocal = f:\074fcbd1c0e3fd87748b2a9b748b9208
08/05/2006 16:22:35.031 windir = C:\WINDOWS
08/05/2006 16:22:35.031 winsysdir = C:\WINDOWS\system32
08/05/2006 16:22:35.031
08/05/2006 16:22:35.093 Enumerating applicable products for this patch
08/05/2006 16:22:36.625 The patch installation could not proceed due to unexpected errors
08/05/2006 16:22:36.625
08/05/2006 16:22:36.625 Product Status Summary:
08/05/2006 16:22:36.843 Hotfix package closed
I think I have a solution to the problem
After installing/uninstalling SQL Server several times and trying to install SP1, about a dozen or more times, I noticed that in the Hotfix log details (see my earlier posting) that the "lcidsupportdir", "supportdirlocal" and "supportdir" all referred to drive "F:". In the Hotfix log details posted by Vishal_LogicalCMG, these same values refer to drive "S:". On the system where SP-1 was failing, drive "F:" is connected to a SAN (I wonder if there's significance to the drive letter "S:" in Vishal's post?).
Since Hotfix.exe doesn't provide a parameter to specify these values, I guessed that Hotfix.exe simply looks for the drive with the most free space for its workspace. On my system, the SAN had a lot more space than the boot drive. I quickly whipped up a script that created a 1GB file and repeatedly made copies of it on drive F: until drive C: had more free space. (I know it's a hack, but I was desparate by now!)
You guessed it - Hotfix.exe now placed its workfiles on the root drive C: and SQL Server 2005 Enterprise version is running with SP-1 installed! It looks like Hotfix.exe doesn't like to work across a SAN.
Microsoft ... are you listening?
Dave
|||Thank you for help. I had the same problem with installing SP1 for SQL Server 2005. It was caused by running installation from USB harddrive. When I coppied SP1 installation to C: then SP1 and post-SP1 hotfixes installed successfully.|||Dave,
Thanks for your post. I had the same issue, but I was not using a SANS, i just had a second drive that had more free space than the C drive. When i filled up that drive the install worked just fine.
Gotta love the QA over at Microsoft sometimes...... DUH.
Thanks again.
|||An easy workaround is to set up a 1 kb Disk Quota on the Larger Drive, which will force the MSI installer to use the C: drive. :)|||Thanks a million Dave! Genious Stuff
I would have never thought of this solution...but finally I too managed to fix this weird issue with the help of your post.
Cheers!
|||The disk quote workaround did not work for me....
However, here is a slightly less kludgy solution is to go to the properties for each SAN drive (M:, N:, O:, etc) -> Security -> Add
your current username to the list, and Deny -> Full Control. Do this for
each SAN drive and the installer will be forced to use the C: drive. Yay
Caleb
Obscure error while attempting to install SQL Server 2005 SP1
I successfully installed SQL Server 2005 SP1 on one PC. When I attempt to install it on a second PC, however, I get the following obscure error:
Unexpected Error Occurred
The following unexpected error occurred:
That's right, there's nothing else listed, other an OK button. D'oh! When I press the OK button, the following error report dialog box appears:
Hotfix.exe
A recently applied update, NULL, failed to install.
There are no pending updates that I'm aware of, as I just booted the PC. I sent the error report.
I notice on this particular PC that the SP1 install EXE is expanding/writing to an external SCSI drive; would that make a difference? I don't see a way to specify a different (i.e., internal ATA drive).
Any other ideas?
You need to look in the setup log files to track down the problem. They're located here:
%\Program Files%\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files
There's also a summary.txt file up one directory and it may point you to the right log file. If you have trouble interpreting the results, please post the errors here and we'll see if we can help you out.
Paul
|||Thanks. Before I delve further into the log file, I noticed that Summary.txt indicates everything is fine, although I don't understand the references to "Uninstall":
Microsoft SQL Server 2005 9.00.1399.06
==============================
OS Version : Microsoft Windows XP Professional Service Pack 2 (Build 2600)
Time : Fri Apr 14 00:07:10 2006
Machine : PC001
Product : Microsoft SQL Server 2005 Express Edition
Product Version : 9.00.1399.06
Uninstall : Successful , Reboot required
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0003_PC001_SQL.log
--
Machine : PC001
Product : Microsoft SQL Server Setup Support Files (English)
Product Version : 9.00.1399.06
Uninstall : Successful
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0003_PC001_SQLSupport_1.log
--
Machine : PC001
Product : Microsoft SQL Server VSS Writer
Product Version : 9.00.1399.06
Uninstall : Successful
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0003_PC001_SqlWriter_1.log
--
Machine : PC001
Product : MSXML 6.0 Parser
Product Version : 6.00.3883.8
Uninstall : Successful
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0003_PC001_MSXML6_1.log
--
Setup succeeded with the installation, inspect the log file completely for status on all the components.Time : Fri Apr 14 01:30:27 2006
I'm not sure why the first section says "Microsoft SQL Server 2005 Express Edition". I don't have the Express Edition installed on this PC, although I installed and uninstalled it a few months ago. Indeed, the Express Edition doesn't appear within the "Add or Remove Programs" applet.
I welcome your suggestions as to how to proceed.
|||If it's any comfort, you are not the only one with this error.
Installed fine the first time on my development server where it is a clean install of Windows 2003 and SQL 2005. However on the pc’s I have attempted to install on, where there is Visual Studio and Office installed it refuses to install.
I have tried on 3 other PC's and they all suffer the same way.
|||
Problem solved.
I took the time over the weekend ( I need to get a life) to build a clean OS install and install SQL 2005 developer edition and then install the service pack to see when and where it broke.
The issue seems to be associated with the fact that the SQL 2005 developer edition I was trying to upgrade was from the ISO image downloaded from the MSDN website. I uninstalled the ISO image and installed SQL from the MSDN subscription CD's and the service pack installs fine.
Can someone at Microsoft please confirm this is an issue!
|||
Interesting observation, Des; thanks. My install came from the ISO image downloaded from the MSDN website, also.
How do you know that simply uninstalling and re-installing didn't resolve the problem?
|||I actually reinstalled from the ISO image first with the same result.
I then went hunting through boxes of MSDN disks till I found the real disks, and reinstalled from the MSDN media. Once installed from the MSDN media the service pack installed first go.
|||We have ugraded one server with SP1 for SQL 2005 without any issues,but when we performed the same task on other server with the same sp1 setup,we encountered the problem which you experienced "The following unexpected error occurred" Both the servers are installed from the same ISO image of MSDN, Herby I attach the log of the hotfix which I executed.
05/26/2006 10:24:48.546 ================================================================================
05/26/2006 10:24:48.546 Hotfix package launched
05/26/2006 10:24:48.546 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
05/26/2006 10:24:48.546 Successfully read registry key: CommonFilesDir, string value = C:\Program Files\Common Files
05/26/2006 10:24:48.546 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
05/26/2006 10:24:48.546 Successfully read registry key: ProgramFilesDir, string value = C:\Program Files
05/26/2006 10:24:48.609 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
05/26/2006 10:24:48.609 Successfully read registry key: CommonFilesDir, string value = C:\Program Files\Common Files
05/26/2006 10:24:48.609 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
05/26/2006 10:24:48.609 Successfully read registry key: ProgramFilesDir, string value = C:\Program Files
05/26/2006 10:24:48.609 Local Computer:
05/26/2006 10:24:48.609 Target Details: SRC-R-AS10
05/26/2006 10:24:48.609 commonfilesdir = C:\Program Files\Common Files
05/26/2006 10:24:48.609 lcidsupportdir = s:\0596decce11fd6b46f501bbd4cfb\1033
05/26/2006 10:24:48.609 programfilesdir = C:\Program Files
05/26/2006 10:24:48.609 supportdir = \\SRC-R-AS10\s$\0596decce11fd6b46f501bbd4cfb
05/26/2006 10:24:48.609 supportdirlocal = s:\0596decce11fd6b46f501bbd4cfb
05/26/2006 10:24:48.609 windir = C:\WINDOWS
05/26/2006 10:24:48.609 winsysdir = C:\WINDOWS\system32
05/26/2006 10:24:48.609
05/26/2006 10:24:48.656 Enumerating applicable products for this patch
05/26/2006 10:24:53.046 The patch installation could not proceed due to unexpected errors
05/26/2006 10:24:53.046
05/26/2006 10:24:53.046 Product Status Summary:
05/26/2006 10:24:53.171 Hotfix package closed
We experience the same error while installing the hotfix AS2005-KB914595-x86-ENU.exe (this hotfix was installed on other server which upgraded successfully.)
Reinstalling is not a feasible option for our environment.Kindly suggest.
~Vishal
|||I received the same "A recently applied update, NULL, failed to install." error when attempting to install SP1 on SQL Server 2005 Enterprise version. As previously noted, I also uninstalled the ISO version from MSDN and re-installed from the MSDN DVD.
Following the reinstall, I attempted to run the individual hotfixes, starting with sql2005-kb918222-x86-enu.exe. Once again, I received the error.
Finally, I attempted to run the fixed patch noted in KB914595 and received a slightly different message but with the same, unsatisfactory results. The message has now changed to "Uninstallation of the 'NULL' update failed". Note that the installer chose to place the support directory on a SAN connection (F:). Could this be a lead toward a solution?
Here's the log file from the %WINDIR%\Hotfix folder:
08/05/2006 16:22:34.875 ================================================================================
08/05/2006 16:22:34.875 Hotfix package launched
08/05/2006 16:22:34.890 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
08/05/2006 16:22:34.890 Successfully read registry key: CommonFilesDir, string value = C:\Program Files\Common Files
08/05/2006 16:22:34.890 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
08/05/2006 16:22:34.890 Successfully read registry key: ProgramFilesDir, string value = C:\Program Files
08/05/2006 16:22:35.031 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
08/05/2006 16:22:35.031 Successfully read registry key: CommonFilesDir, string value = C:\Program Files\Common Files
08/05/2006 16:22:35.031 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
08/05/2006 16:22:35.031 Successfully read registry key: ProgramFilesDir, string value = C:\Program Files
08/05/2006 16:22:35.031 Local Computer:
08/05/2006 16:22:35.031 Target Details: EMERALD
08/05/2006 16:22:35.031 commonfilesdir = C:\Program Files\Common Files
08/05/2006 16:22:35.031 lcidsupportdir = f:\074fcbd1c0e3fd87748b2a9b748b9208\1033
08/05/2006 16:22:35.031 programfilesdir = C:\Program Files
08/05/2006 16:22:35.031 supportdir = \\EMERALD\f$\074fcbd1c0e3fd87748b2a9b748b9208
08/05/2006 16:22:35.031 supportdirlocal = f:\074fcbd1c0e3fd87748b2a9b748b9208
08/05/2006 16:22:35.031 windir = C:\WINDOWS
08/05/2006 16:22:35.031 winsysdir = C:\WINDOWS\system32
08/05/2006 16:22:35.031
08/05/2006 16:22:35.093 Enumerating applicable products for this patch
08/05/2006 16:22:36.625 The patch installation could not proceed due to unexpected errors
08/05/2006 16:22:36.625
08/05/2006 16:22:36.625 Product Status Summary:
08/05/2006 16:22:36.843 Hotfix package closed
I think I have a solution to the problem
After installing/uninstalling SQL Server several times and trying to install SP1, about a dozen or more times, I noticed that in the Hotfix log details (see my earlier posting) that the "lcidsupportdir", "supportdirlocal" and "supportdir" all referred to drive "F:". In the Hotfix log details posted by Vishal_LogicalCMG, these same values refer to drive "S:". On the system where SP-1 was failing, drive "F:" is connected to a SAN (I wonder if there's significance to the drive letter "S:" in Vishal's post?).
Since Hotfix.exe doesn't provide a parameter to specify these values, I guessed that Hotfix.exe simply looks for the drive with the most free space for its workspace. On my system, the SAN had a lot more space than the boot drive. I quickly whipped up a script that created a 1GB file and repeatedly made copies of it on drive F: until drive C: had more free space. (I know it's a hack, but I was desparate by now!)
You guessed it - Hotfix.exe now placed its workfiles on the root drive C: and SQL Server 2005 Enterprise version is running with SP-1 installed! It looks like Hotfix.exe doesn't like to work across a SAN.
Microsoft ... are you listening?
Dave
|||Thank you for help. I had the same problem with installing SP1 for SQL Server 2005. It was caused by running installation from USB harddrive. When I coppied SP1 installation to C: then SP1 and post-SP1 hotfixes installed successfully.|||Dave,
Thanks for your post. I had the same issue, but I was not using a SANS, i just had a second drive that had more free space than the C drive. When i filled up that drive the install worked just fine.
Gotta love the QA over at Microsoft sometimes...... DUH.
Thanks again.
|||An easy workaround is to set up a 1 kb Disk Quota on the Larger Drive, which will force the MSI installer to use the C: drive. :)|||Thanks a million Dave! Genious Stuff
I would have never thought of this solution...but finally I too managed to fix this weird issue with the help of your post.
Cheers!
|||The disk quote workaround did not work for me....
However, here is a slightly less kludgy solution is to go to the properties for each SAN drive (M:, N:, O:, etc) -> Security -> Add
your current username to the list, and Deny -> Full Control. Do this for
each SAN drive and the installer will be forced to use the C: drive. Yay
Caleb
Obscure error while attempting to install SQL Server 2005 SP1
I successfully installed SQL Server 2005 SP1 on one PC. When I attempt to install it on a second PC, however, I get the following obscure error:
Unexpected Error Occurred
The following unexpected error occurred:
That's right, there's nothing else listed, other an OK button. D'oh! When I press the OK button, the following error report dialog box appears:
Hotfix.exe
A recently applied update, NULL, failed to install.
There are no pending updates that I'm aware of, as I just booted the PC. I sent the error report.
I notice on this particular PC that the SP1 install EXE is expanding/writing to an external SCSI drive; would that make a difference? I don't see a way to specify a different (i.e., internal ATA drive).
Any other ideas?
You need to look in the setup log files to track down the problem. They're located here:
%\Program Files%\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files
There's also a summary.txt file up one directory and it may point you to the right log file. If you have trouble interpreting the results, please post the errors here and we'll see if we can help you out.
Paul
|||Thanks. Before I delve further into the log file, I noticed that Summary.txt indicates everything is fine, although I don't understand the references to "Uninstall":
Microsoft SQL Server 2005 9.00.1399.06
==============================
OS Version : Microsoft Windows XP Professional Service Pack 2 (Build 2600)
Time : Fri Apr 14 00:07:10 2006
Machine : PC001
Product : Microsoft SQL Server 2005 Express Edition
Product Version : 9.00.1399.06
Uninstall : Successful , Reboot required
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0003_PC001_SQL.log
--
Machine : PC001
Product : Microsoft SQL Server Setup Support Files (English)
Product Version : 9.00.1399.06
Uninstall : Successful
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0003_PC001_SQLSupport_1.log
--
Machine : PC001
Product : Microsoft SQL Server VSS Writer
Product Version : 9.00.1399.06
Uninstall : Successful
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0003_PC001_SqlWriter_1.log
--
Machine : PC001
Product : MSXML 6.0 Parser
Product Version : 6.00.3883.8
Uninstall : Successful
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0003_PC001_MSXML6_1.log
--
Setup succeeded with the installation, inspect the log file completely for status on all the components.Time : Fri Apr 14 01:30:27 2006
I'm not sure why the first section says "Microsoft SQL Server 2005 Express Edition". I don't have the Express Edition installed on this PC, although I installed and uninstalled it a few months ago. Indeed, the Express Edition doesn't appear within the "Add or Remove Programs" applet.
I welcome your suggestions as to how to proceed.
|||If it's any comfort, you are not the only one with this error.
Installed fine the first time on my development server where it is a clean install of Windows 2003 and SQL 2005. However on the pc’s I have attempted to install on, where there is Visual Studio and Office installed it refuses to install.
I have tried on 3 other PC's and they all suffer the same way.
|||
Problem solved.
I took the time over the weekend ( I need to get a life) to build a clean OS install and install SQL 2005 developer edition and then install the service pack to see when and where it broke.
The issue seems to be associated with the fact that the SQL 2005 developer edition I was trying to upgrade was from the ISO image downloaded from the MSDN website. I uninstalled the ISO image and installed SQL from the MSDN subscription CD's and the service pack installs fine.
Can someone at Microsoft please confirm this is an issue!
|||
Interesting observation, Des; thanks. My install came from the ISO image downloaded from the MSDN website, also.
How do you know that simply uninstalling and re-installing didn't resolve the problem?
|||I actually reinstalled from the ISO image first with the same result.
I then went hunting through boxes of MSDN disks till I found the real disks, and reinstalled from the MSDN media. Once installed from the MSDN media the service pack installed first go.
|||We have ugraded one server with SP1 for SQL 2005 without any issues,but when we performed the same task on other server with the same sp1 setup,we encountered the problem which you experienced "The following unexpected error occurred" Both the servers are installed from the same ISO image of MSDN, Herby I attach the log of the hotfix which I executed.
05/26/2006 10:24:48.546 ================================================================================
05/26/2006 10:24:48.546 Hotfix package launched
05/26/2006 10:24:48.546 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
05/26/2006 10:24:48.546 Successfully read registry key: CommonFilesDir, string value = C:\Program Files\Common Files
05/26/2006 10:24:48.546 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
05/26/2006 10:24:48.546 Successfully read registry key: ProgramFilesDir, string value = C:\Program Files
05/26/2006 10:24:48.609 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
05/26/2006 10:24:48.609 Successfully read registry key: CommonFilesDir, string value = C:\Program Files\Common Files
05/26/2006 10:24:48.609 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
05/26/2006 10:24:48.609 Successfully read registry key: ProgramFilesDir, string value = C:\Program Files
05/26/2006 10:24:48.609 Local Computer:
05/26/2006 10:24:48.609 Target Details: SRC-R-AS10
05/26/2006 10:24:48.609 commonfilesdir = C:\Program Files\Common Files
05/26/2006 10:24:48.609 lcidsupportdir = s:\0596decce11fd6b46f501bbd4cfb\1033
05/26/2006 10:24:48.609 programfilesdir = C:\Program Files
05/26/2006 10:24:48.609 supportdir = \\SRC-R-AS10\s$\0596decce11fd6b46f501bbd4cfb
05/26/2006 10:24:48.609 supportdirlocal = s:\0596decce11fd6b46f501bbd4cfb
05/26/2006 10:24:48.609 windir = C:\WINDOWS
05/26/2006 10:24:48.609 winsysdir = C:\WINDOWS\system32
05/26/2006 10:24:48.609
05/26/2006 10:24:48.656 Enumerating applicable products for this patch
05/26/2006 10:24:53.046 The patch installation could not proceed due to unexpected errors
05/26/2006 10:24:53.046
05/26/2006 10:24:53.046 Product Status Summary:
05/26/2006 10:24:53.171 Hotfix package closed
We experience the same error while installing the hotfix AS2005-KB914595-x86-ENU.exe (this hotfix was installed on other server which upgraded successfully.)
Reinstalling is not a feasible option for our environment.Kindly suggest.
~Vishal
|||I received the same "A recently applied update, NULL, failed to install." error when attempting to install SP1 on SQL Server 2005 Enterprise version. As previously noted, I also uninstalled the ISO version from MSDN and re-installed from the MSDN DVD.
Following the reinstall, I attempted to run the individual hotfixes, starting with sql2005-kb918222-x86-enu.exe. Once again, I received the error.
Finally, I attempted to run the fixed patch noted in KB914595 and received a slightly different message but with the same, unsatisfactory results. The message has now changed to "Uninstallation of the 'NULL' update failed". Note that the installer chose to place the support directory on a SAN connection (F:). Could this be a lead toward a solution?
Here's the log file from the %WINDIR%\Hotfix folder:
08/05/2006 16:22:34.875 ================================================================================
08/05/2006 16:22:34.875 Hotfix package launched
08/05/2006 16:22:34.890 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
08/05/2006 16:22:34.890 Successfully read registry key: CommonFilesDir, string value = C:\Program Files\Common Files
08/05/2006 16:22:34.890 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
08/05/2006 16:22:34.890 Successfully read registry key: ProgramFilesDir, string value = C:\Program Files
08/05/2006 16:22:35.031 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
08/05/2006 16:22:35.031 Successfully read registry key: CommonFilesDir, string value = C:\Program Files\Common Files
08/05/2006 16:22:35.031 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
08/05/2006 16:22:35.031 Successfully read registry key: ProgramFilesDir, string value = C:\Program Files
08/05/2006 16:22:35.031 Local Computer:
08/05/2006 16:22:35.031 Target Details: EMERALD
08/05/2006 16:22:35.031 commonfilesdir = C:\Program Files\Common Files
08/05/2006 16:22:35.031 lcidsupportdir = f:\074fcbd1c0e3fd87748b2a9b748b9208\1033
08/05/2006 16:22:35.031 programfilesdir = C:\Program Files
08/05/2006 16:22:35.031 supportdir = \\EMERALD\f$\074fcbd1c0e3fd87748b2a9b748b9208
08/05/2006 16:22:35.031 supportdirlocal = f:\074fcbd1c0e3fd87748b2a9b748b9208
08/05/2006 16:22:35.031 windir = C:\WINDOWS
08/05/2006 16:22:35.031 winsysdir = C:\WINDOWS\system32
08/05/2006 16:22:35.031
08/05/2006 16:22:35.093 Enumerating applicable products for this patch
08/05/2006 16:22:36.625 The patch installation could not proceed due to unexpected errors
08/05/2006 16:22:36.625
08/05/2006 16:22:36.625 Product Status Summary:
08/05/2006 16:22:36.843 Hotfix package closed
I think I have a solution to the problem
After installing/uninstalling SQL Server several times and trying to install SP1, about a dozen or more times, I noticed that in the Hotfix log details (see my earlier posting) that the "lcidsupportdir", "supportdirlocal" and "supportdir" all referred to drive "F:". In the Hotfix log details posted by Vishal_LogicalCMG, these same values refer to drive "S:". On the system where SP-1 was failing, drive "F:" is connected to a SAN (I wonder if there's significance to the drive letter "S:" in Vishal's post?).
Since Hotfix.exe doesn't provide a parameter to specify these values, I guessed that Hotfix.exe simply looks for the drive with the most free space for its workspace. On my system, the SAN had a lot more space than the boot drive. I quickly whipped up a script that created a 1GB file and repeatedly made copies of it on drive F: until drive C: had more free space. (I know it's a hack, but I was desparate by now!)
You guessed it - Hotfix.exe now placed its workfiles on the root drive C: and SQL Server 2005 Enterprise version is running with SP-1 installed! It looks like Hotfix.exe doesn't like to work across a SAN.
Microsoft ... are you listening?
Dave
|||Thank you for help. I had the same problem with installing SP1 for SQL Server 2005. It was caused by running installation from USB harddrive. When I coppied SP1 installation to C: then SP1 and post-SP1 hotfixes installed successfully.|||Dave,
Thanks for your post. I had the same issue, but I was not using a SANS, i just had a second drive that had more free space than the C drive. When i filled up that drive the install worked just fine.
Gotta love the QA over at Microsoft sometimes...... DUH.
Thanks again.
|||An easy workaround is to set up a 1 kb Disk Quota on the Larger Drive, which will force the MSI installer to use the C: drive. :)|||Thanks a million Dave! Genious Stuff
I would have never thought of this solution...but finally I too managed to fix this weird issue with the help of your post.
Cheers!
However, here is a slightly less kludgy solution is to go to the properties for each SAN drive (M:, N:, O:, etc) -> Security -> Add your current username to the list, and Deny -> Full Control. Do this for each SAN drive and the installer will be forced to use the C: drive. Yay
Caleb
OBJECTPROPERTY problem
Why does the first query work properly and return 1 for the IsMsShipped
column when the second and third query do not?
use Northwind
go
SELECT convert(varchar,name) as ObjectName, id, OBJECTPROPERTY(id,
N'IsMSShipped') as IsMSShipped
FROM sysobjects
WHERE name='dt_adduserobject'
use Model
go
SELECT convert(varchar,name) as ObjectName, id, OBJECTPROPERTY(id,
N'IsMSShipped') as IsMSShipped
FROM Northwind..sysobjects
WHERE name='dt_adduserobject'
SELECT convert(varchar,name) as ObjectName, id,
OBJECTPROPERTY(1157579162, N'IsMSShipped') as IsMSShipped
FROM Northwind..sysobjects
WHERE name='dt_adduserobject'
'dt_adduserobject' is just an example of an object that I'd like to
exclude from my results using the IsMSShipped property. I'm querying
sysobjects for every database from a stored proc that uses dynamic sql
to supply the database name. I need to exclude system objects without
resorting to sysobjects.name not like 'dt%'. Unfortunately, this
OBJECTPROPERTY function ignores the database in my FROM clause. Any
ideas?Because OBJECTPROPERTY is resolved to the current datanase. So, it is
trying to find a local object in Model, with whatever id is assigned to
dt_adduserobject in the Northwind database.
<drink.the.koolaid@.gmail.com> wrote in message
news:1140185167.395442.186560@.f14g2000cwb.googlegroups.com...
> Why does the first query work properly and return 1 for the IsMsShipped
> column when the second and third query do not?
> use Northwind
> go
> SELECT convert(varchar,name) as ObjectName, id, OBJECTPROPERTY(id,
> N'IsMSShipped') as IsMSShipped
> FROM sysobjects
> WHERE name='dt_adduserobject'
>
> use Model
> go
> SELECT convert(varchar,name) as ObjectName, id, OBJECTPROPERTY(id,
> N'IsMSShipped') as IsMSShipped
> FROM Northwind..sysobjects
> WHERE name='dt_adduserobject'
> SELECT convert(varchar,name) as ObjectName, id,
> OBJECTPROPERTY(1157579162, N'IsMSShipped') as IsMSShipped
> FROM Northwind..sysobjects
> WHERE name='dt_adduserobject'
> 'dt_adduserobject' is just an example of an object that I'd like to
> exclude from my results using the IsMSShipped property. I'm querying
> sysobjects for every database from a stored proc that uses dynamic sql
> to supply the database name. I need to exclude system objects without
> resorting to sysobjects.name not like 'dt%'. Unfortunately, this
> OBJECTPROPERTY function ignores the database in my FROM clause. Any
> ideas?
>|||Read under the Remarks of the BOL (OBJECTPROPERTY) what the cause is:
"The Database Engine assumes that object_id is in the current database
context."
HTH, Jens Suessmeyer.|||Is there any way to change the "current database context" from within a
procedure? I think I've tried "USE Northwind GO" without
success...
Can I somehow create my own OBJECTPROPERTY function that is not
sensitive to the current database context?
Can I use some other method to determine if an object is a "system
object" that doesn't depend on the current database context?
Thanks for your help.|||> Is there any way to change the "current database context" from within a
> procedure?
No. Only option would be to construct the query in a string and use dynamic
SQL. This has several
drawbacks, see the articles at www.sommarskog.se.
> Can I somehow create my own OBJECTPROPERTY function that is not
> sensitive to the current database context?
I don't think that is possible, taking into account the limitations of what
you can do inside a
function (no dynamic SQL etc), and the fact that a function is database spec
ific, not global.
> Can I use some other method to determine if an object is a "system
> object" that doesn't depend on the current database context?
You can check against some undocumented status column in the sysobjects tabl
e. Do some searching etc
and you will surely find out how to do it. But be aware that this code will
*not* port to 2005 as
the system tables has been reworked and undocumented stuff are not ported to
the compatibility
views. In 2005, you have much better ways of doing this though the new catal
og views.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<drink.the.koolaid@.gmail.com> wrote in message
news:1140199085.871112.237720@.g14g2000cwa.googlegroups.com...
> Is there any way to change the "current database context" from within a
> procedure? I think I've tried "USE Northwind GO" without
> success...
> Can I somehow create my own OBJECTPROPERTY function that is not
> sensitive to the current database context?
> Can I use some other method to determine if an object is a "system
> object" that doesn't depend on the current database context?
> Thanks for your help.
>|||I haven't tried it but you can probably use dynamic sql (sp_executesql to be
exact) and an output parameter to get what you want. You can't put a GO but
I think you can do something like this:
USE YourOtherDB
SET @.X = OBJECTPROPERTY()
wrapped in dynamic sql where @.x is the putput param.
http://www.support.microsoft.com/?id=262499 Using OutPut Params &
sp_executeSql
Andrew J. Kelly SQL MVP
<drink.the.koolaid@.gmail.com> wrote in message
news:1140199085.871112.237720@.g14g2000cwa.googlegroups.com...
> Is there any way to change the "current database context" from within a
> procedure? I think I've tried "USE Northwind GO" without
> success...
> Can I somehow create my own OBJECTPROPERTY function that is not
> sensitive to the current database context?
> Can I use some other method to determine if an object is a "system
> object" that doesn't depend on the current database context?
> Thanks for your help.
>|||I couldn't make that work via dynamic sql via EXECUTE(N'Use Northwind')
I'm also having trouble locating an informative post about
sysobjects.status... :(|||> I'm also having trouble locating an informative post about
> sysobjects.status... :(
Exactly what is it that you need to know? The type column in sysobjects is v
ery informative, for
example. And it id documented.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<drink.the.koolaid@.gmail.com> wrote in message
news:1140274997.951001.77840@.g43g2000cwa.googlegroups.com...
>I couldn't make that work via dynamic sql via EXECUTE(N'Use Northwind')
> I'm also having trouble locating an informative post about
> sysobjects.status... :(
>|||You have to put the USE and the code you want executed in that db scope all
in the same dynamic sql batch.
DECLARE @.X NVARCHAR(500)
SET @.X = 'USE NORTHWIND
SELECT convert(varchar,[name]) as [ObjectName], [id], OBJECTPROPERTY([id],
N''IsMSShipped'') as [IsMSShipped]
FROM sysobjects
WHERE name=''Customers'''
EXEC(@.x)
Andrew J. Kelly SQL MVP
<drink.the.koolaid@.gmail.com> wrote in message
news:1140274997.951001.77840@.g43g2000cwa.googlegroups.com...
>I couldn't make that work via dynamic sql via EXECUTE(N'Use Northwind')
> I'm also having trouble locating an informative post about
> sysobjects.status... :(
>|||Thank you! That work's great.
column when the second and third query do not?
use Northwind
go
SELECT convert(varchar,name) as ObjectName, id, OBJECTPROPERTY(id,
N'IsMSShipped') as IsMSShipped
FROM sysobjects
WHERE name='dt_adduserobject'
use Model
go
SELECT convert(varchar,name) as ObjectName, id, OBJECTPROPERTY(id,
N'IsMSShipped') as IsMSShipped
FROM Northwind..sysobjects
WHERE name='dt_adduserobject'
SELECT convert(varchar,name) as ObjectName, id,
OBJECTPROPERTY(1157579162, N'IsMSShipped') as IsMSShipped
FROM Northwind..sysobjects
WHERE name='dt_adduserobject'
'dt_adduserobject' is just an example of an object that I'd like to
exclude from my results using the IsMSShipped property. I'm querying
sysobjects for every database from a stored proc that uses dynamic sql
to supply the database name. I need to exclude system objects without
resorting to sysobjects.name not like 'dt%'. Unfortunately, this
OBJECTPROPERTY function ignores the database in my FROM clause. Any
ideas?Because OBJECTPROPERTY is resolved to the current datanase. So, it is
trying to find a local object in Model, with whatever id is assigned to
dt_adduserobject in the Northwind database.
<drink.the.koolaid@.gmail.com> wrote in message
news:1140185167.395442.186560@.f14g2000cwb.googlegroups.com...
> Why does the first query work properly and return 1 for the IsMsShipped
> column when the second and third query do not?
> use Northwind
> go
> SELECT convert(varchar,name) as ObjectName, id, OBJECTPROPERTY(id,
> N'IsMSShipped') as IsMSShipped
> FROM sysobjects
> WHERE name='dt_adduserobject'
>
> use Model
> go
> SELECT convert(varchar,name) as ObjectName, id, OBJECTPROPERTY(id,
> N'IsMSShipped') as IsMSShipped
> FROM Northwind..sysobjects
> WHERE name='dt_adduserobject'
> SELECT convert(varchar,name) as ObjectName, id,
> OBJECTPROPERTY(1157579162, N'IsMSShipped') as IsMSShipped
> FROM Northwind..sysobjects
> WHERE name='dt_adduserobject'
> 'dt_adduserobject' is just an example of an object that I'd like to
> exclude from my results using the IsMSShipped property. I'm querying
> sysobjects for every database from a stored proc that uses dynamic sql
> to supply the database name. I need to exclude system objects without
> resorting to sysobjects.name not like 'dt%'. Unfortunately, this
> OBJECTPROPERTY function ignores the database in my FROM clause. Any
> ideas?
>|||Read under the Remarks of the BOL (OBJECTPROPERTY) what the cause is:
"The Database Engine assumes that object_id is in the current database
context."
HTH, Jens Suessmeyer.|||Is there any way to change the "current database context" from within a
procedure? I think I've tried "USE Northwind GO" without
success...
Can I somehow create my own OBJECTPROPERTY function that is not
sensitive to the current database context?
Can I use some other method to determine if an object is a "system
object" that doesn't depend on the current database context?
Thanks for your help.|||> Is there any way to change the "current database context" from within a
> procedure?
No. Only option would be to construct the query in a string and use dynamic
SQL. This has several
drawbacks, see the articles at www.sommarskog.se.
> Can I somehow create my own OBJECTPROPERTY function that is not
> sensitive to the current database context?
I don't think that is possible, taking into account the limitations of what
you can do inside a
function (no dynamic SQL etc), and the fact that a function is database spec
ific, not global.
> Can I use some other method to determine if an object is a "system
> object" that doesn't depend on the current database context?
You can check against some undocumented status column in the sysobjects tabl
e. Do some searching etc
and you will surely find out how to do it. But be aware that this code will
*not* port to 2005 as
the system tables has been reworked and undocumented stuff are not ported to
the compatibility
views. In 2005, you have much better ways of doing this though the new catal
og views.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<drink.the.koolaid@.gmail.com> wrote in message
news:1140199085.871112.237720@.g14g2000cwa.googlegroups.com...
> Is there any way to change the "current database context" from within a
> procedure? I think I've tried "USE Northwind GO" without
> success...
> Can I somehow create my own OBJECTPROPERTY function that is not
> sensitive to the current database context?
> Can I use some other method to determine if an object is a "system
> object" that doesn't depend on the current database context?
> Thanks for your help.
>|||I haven't tried it but you can probably use dynamic sql (sp_executesql to be
exact) and an output parameter to get what you want. You can't put a GO but
I think you can do something like this:
USE YourOtherDB
SET @.X = OBJECTPROPERTY()
wrapped in dynamic sql where @.x is the putput param.
http://www.support.microsoft.com/?id=262499 Using OutPut Params &
sp_executeSql
Andrew J. Kelly SQL MVP
<drink.the.koolaid@.gmail.com> wrote in message
news:1140199085.871112.237720@.g14g2000cwa.googlegroups.com...
> Is there any way to change the "current database context" from within a
> procedure? I think I've tried "USE Northwind GO" without
> success...
> Can I somehow create my own OBJECTPROPERTY function that is not
> sensitive to the current database context?
> Can I use some other method to determine if an object is a "system
> object" that doesn't depend on the current database context?
> Thanks for your help.
>|||I couldn't make that work via dynamic sql via EXECUTE(N'Use Northwind')
I'm also having trouble locating an informative post about
sysobjects.status... :(|||> I'm also having trouble locating an informative post about
> sysobjects.status... :(
Exactly what is it that you need to know? The type column in sysobjects is v
ery informative, for
example. And it id documented.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<drink.the.koolaid@.gmail.com> wrote in message
news:1140274997.951001.77840@.g43g2000cwa.googlegroups.com...
>I couldn't make that work via dynamic sql via EXECUTE(N'Use Northwind')
> I'm also having trouble locating an informative post about
> sysobjects.status... :(
>|||You have to put the USE and the code you want executed in that db scope all
in the same dynamic sql batch.
DECLARE @.X NVARCHAR(500)
SET @.X = 'USE NORTHWIND
SELECT convert(varchar,[name]) as [ObjectName], [id], OBJECTPROPERTY([id],
N''IsMSShipped'') as [IsMSShipped]
FROM sysobjects
WHERE name=''Customers'''
EXEC(@.x)
Andrew J. Kelly SQL MVP
<drink.the.koolaid@.gmail.com> wrote in message
news:1140274997.951001.77840@.g43g2000cwa.googlegroups.com...
>I couldn't make that work via dynamic sql via EXECUTE(N'Use Northwind')
> I'm also having trouble locating an informative post about
> sysobjects.status... :(
>|||Thank you! That work's great.
Subscribe to:
Posts (Atom)