Objects related to DB: following

Showing posts with label following. Show all posts

Friday, March 30, 2012

ODBC Error

Hi All,
I am getting the following error message when I am trying to connect to SQL 2000 Server from SQL 7 Server using BCP/OSQL/ISQL etc., Can any one help me in fixing this problem.
NULL
COULD NOT CONNECT TO SQL SERVER
NULL
SQLState: 08001 Native Error: 6
Info. Message: [Microsoft][ODBC SQL Server Driver][Named Pipes]Specified SQL server not found.
NULL
SQLState: 01000 Native Error: 53
Info. Message: [Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionOpen (CreateFile()).
Thanks & Regards
Balaji Prabhu.TBalaji,
What is the respective OS?Do you have only 'Named pipes' enabled?
--
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Balaji Prabhu.T" <Balaji Prabhu.T@.discussions.microsoft.com> wrote in
message news:C15C3280-F0A5-418B-912B-018EE518AF14@.microsoft.com...
> Hi All,
> I am getting the following error message when I am trying to connect to
SQL 2000 Server from SQL 7 Server using BCP/OSQL/ISQL etc., Can any one help
me in fixing this problem.
> NULL
> COULD NOT CONNECT TO SQL SERVER
> NULL
> SQLState: 08001 Native Error: 6
> Info. Message: [Microsoft][ODBC SQL Server Driver][Named Pipes]Specified
SQL server not found.
> NULL
> SQLState: 01000 Native Error: 53
> Info. Message: [Microsoft][ODBC SQL Server Driver][Named
Pipes]ConnectionOpen (CreateFile()).
> Thanks & Regards
> Balaji Prabhu.T
>

odbc error

Getting the following error:
SQL error. Stmt #: 1568 Error Position: 0 Return: 8602 -
[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB
provider returned message: Errors occurred] (SQLSTATE
01000) 7312
Scenario: application/database sitting on win nt4 server
running sql7 linked to a Windows 2000 server running sql
2000. Trigger on serverA trying to update table on
ServerB via linked server.Turn on trace flag 7300 to try to get better information.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

odbc error

Windows 2000 sp 3, sql 7.0 sp 4. When I try to go into
odbc under Admin tools, I get the following error:
Unable to load sql server odbc driver resource dll. The
application cannot continue.
I am not seeing any error in event viewer.
ThanksThe error is generally due to either a missing .rll file -
file is sqlsrv32.rll - or a version mismatch with the
corresponding dll file (sqlsrv32.dll).
Try running the component checker tool to verify your mdac
installation. It will help in diagnosing installation
issues. You can download the tool from:
http://msdn.microsoft.com/data/mdac/default.aspx
-Sue
On Mon, 24 May 2004 18:53:49 -0700, "Sue"
<anonymous@.discussions.microsoft.com> wrote:

>Windows 2000 sp 3, sql 7.0 sp 4. When I try to go into
>odbc under Admin tools, I get the following error:
>
>Unable to load sql server odbc driver resource dll. The
>application cannot continue.
>I am not seeing any error in event viewer.
>Thanks|||It listed Mdac 2.6 sp 2.
Sue
>--Original Message--
>The error is generally due to either a missing .rll
file -
>file is sqlsrv32.rll - or a version mismatch with the
>corresponding dll file (sqlsrv32.dll).
>Try running the component checker tool to verify your
mdac
>installation. It will help in diagnosing installation
>issues. You can download the tool from:
>http://msdn.microsoft.com/data/mdac/default.aspx
>-Sue
>On Mon, 24 May 2004 18:53:49 -0700, "Sue"
><anonymous@.discussions.microsoft.com> wrote:
>
The[vbcol=seagreen]
>.
>|||Mdac 2.8 fixed the problem.

>--Original Message--
>It listed Mdac 2.6 sp 2.
>Sue
>file -
>mdac
into[vbcol=seagreen]
>The
>.
>|||Thanks for posting back. Updating your version of MDAC is
probably easier than fixing the current installation
problems.
-Sue
On Tue, 25 May 2004 11:36:53 -0700,
<anonymous@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Mdac 2.8 fixed the problem.
>
>into

ODBC Error

I get the following error (from my front-end Access app AND Query Analyzer)
when accessing a particular table. It is a rather large table, ~1,750,000
rows in it. I tried looking through "limitations" in BOL and couldn't find
anything. Is this a server setting or ODBC setting or... ?
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionChe
ckForData
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection BrokenWhat are you trying to do with that much data? Access isn't designed
to handle scrolling through millions of rows, although you may be
having network issues, as the error message suggests. Restrict the
data fetched with a WHERE clause, or if it's a report, write a stored
procedure and call it through a pass-through query.
--mary
On Thu, 15 Apr 2004 17:03:47 -0700, "Ron Hinds"
<__NoSpam@.__NoSpamramac.com> wrote:

>I get the following error (from my front-end Access app AND Query Analyzer)
>when accessing a particular table. It is a rather large table, ~1,750,000
>rows in it. I tried looking through "limitations" in BOL and couldn't find
>anything. Is this a server setting or ODBC setting or... ?
>[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionCh
eckForData
>(CheckforData()).
>Server: Msg 11, Level 16, State 1, Line 0
>General network error. Check your network documentation.
>Connection Broken
>|||What I'm trying to do is populate a local table (BackOrders) that's actually
used in the form from a server table (SalesDetail). There is a constraint
(WHERE Customer=x AND QtyAvailable>0) but using DAO it still tries to fetch
the entire table. Here is the original SQL (pure Access 97 app):
INSERT INTO BackOrders (InvoiceNumber, InvoiceDate, Part, Price, QtyOrdered,
QtyAvailable, Description) SELECT SalesDetail.InvoiceNumber,
SalesDetail.InvoiceDate, SalesDetail.Part, SalesDetail.Price,
SalesDetail.QtyOrdered, Inventory.QtyAvailable, Inventory.Description FROM
SalesDetail INNER JOIN Inventory ON SalesDetail.Part = Inventory.Part WHERE
SalesDetail.Customer=x AND SalesDetail.QtyOrdered > SalesDetail.QtyShipped
AND Inventory.Qty - Inventory.QtyCommitted > 0 AND SalesDetail.BackOrder > 0
ORDER BY SalesDetail.invoiceDate DESC
Even if this worked as-is with SQL Server (it doesn't - gives the same
error), it would be horribly slow, so I wanted to optimize it by creating a
View of the server tables. But I don't know the value of 'x' (Customer)
until run time. So I used the following DDL to create the View on SQL
Server:
USE Prototype
GO
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'vBackOrder')
DROP VIEW vBackOrder
GO
CREATE VIEW vBackOrder AS
SELECT TOP 100 PERCENT SalesDetail.invoiceNumber, SalesDetail.invoiceDate,
SalesDetail.Part, SalesDetail.Price, SalesDetail.QtyOrdered, Inventory.Qty -
Inventory.QtyCommitted AS QtyAvailable, Inventory.Description
SalesDetail.Customer FROM SalesDetail INNER JOIN Inventory ON
SalesDetail.itemID = Inventory.invItemID WHERE SalesDetail.QtyOrdered >
SalesDetail.QtyShipped AND Inventory.Qty - Inventory.QtyCommitted > 0 AND
SalesDetail.BackOrder > 0 ORDER BY SalesDetail.invoiceDate DESC
GO
I linked vBackOrder in Access then used this DAO code to populate local
table BackOrders:
INSERT INTO BackOrders (InvoiceNumber, InvoiceDate, Part, Price, QtyOrdered,
QtyAvailable, Description) SELECT vBackOrder.InvoiceNumber,
vBackOrder.InvoiceDate, vBackOrder.Part, vBackOrder.Price,
vBackOrder.QtyOrdered, vBackOrder.QtyAvailable, vBackOrder.Description FROM
vBackOrder WHERE vBackOrder.Customer=x
This also gives the same error, as does attempting to open the linked
vBackOrder in Access. I then went to QA and tried just the SELECT portion of
the CREATE VIEW with the same result. To narrow it down, I tried SELECTing *
FROM Inventory - a little slow but no problem. I then tried the same thing
with SalesDetail and again get the same error. I have >300 server tables of
varying sizes in this app. SalesDetail is by far the largest, and it is the
*only* one I get the error on. So I'm assuming it has something to do with
the size.
"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
news:pflv701qqge68qf7u94r6bnetfkb1iuc53@.
4ax.com...
> What are you trying to do with that much data? Access isn't designed
> to handle scrolling through millions of rows, although you may be
> having network issues, as the error message suggests. Restrict the
> data fetched with a WHERE clause, or if it's a report, write a stored
> procedure and call it through a pass-through query.
> --mary
> On Thu, 15 Apr 2004 17:03:47 -0700, "Ron Hinds"
> <__NoSpam@.__NoSpamramac.com> wrote:
>
Analyzer)[vbcol=seagreen]
find[vbcol=seagreen]
>|||DAO is the problem. You're loading the Jet engine and using it for SQL
Server data operations, something it was never designed or optimized
to do. Create a stored procedure instead of a buinch of views. Stored
procedures support parameters and complex logic, and return a
read-only result set which you can use to populate your local table.
Call the stored procedure from a pass-through query where you set the
SQL syntax to something like this in your code (you can use DAO to set
properties of a QueryDef object and execute it):
qdef.SQL = "EXEC myproc 'paramvalue1', val2" etc.
qdef.Execute
Pass-through queries bypass the Jet engine when they're executed and
are the most efficient way of getting back large result sets since all
of the processing takes place on the server, not in Jet. You then
create either an Insert or Update query that selects from your
pass-through query into the local table. When you call the
insert/update query it will automatically execute the pass-through
query to get the records.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Fri, 16 Apr 2004 17:09:15 -0700, "Ron Hinds"
<__NoSpam@.__NoSpamramac.com> wrote:

>What I'm trying to do is populate a local table (BackOrders) that's actuall
y
>used in the form from a server table (SalesDetail). There is a constraint
>(WHERE Customer=x AND QtyAvailable>0) but using DAO it still tries to fetch
>the entire table. Here is the original SQL (pure Access 97 app):
>INSERT INTO BackOrders (InvoiceNumber, InvoiceDate, Part, Price, QtyOrdered
,
>QtyAvailable, Description) SELECT SalesDetail.InvoiceNumber,
>SalesDetail.InvoiceDate, SalesDetail.Part, SalesDetail.Price,
>SalesDetail.QtyOrdered, Inventory.QtyAvailable, Inventory.Description FROM
>SalesDetail INNER JOIN Inventory ON SalesDetail.Part = Inventory.Part WHERE
>SalesDetail.Customer=x AND SalesDetail.QtyOrdered > SalesDetail.QtyShipped
>AND Inventory.Qty - Inventory.QtyCommitted > 0 AND SalesDetail.BackOrder >
0
>ORDER BY SalesDetail.invoiceDate DESC
>Even if this worked as-is with SQL Server (it doesn't - gives the same
>error), it would be horribly slow, so I wanted to optimize it by creating a
>View of the server tables. But I don't know the value of 'x' (Customer)
>until run time. So I used the following DDL to create the View on SQL
>Server:
>USE Prototype
>GO
>IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
> WHERE TABLE_NAME = 'vBackOrder')
> DROP VIEW vBackOrder
>GO
>CREATE VIEW vBackOrder AS
>SELECT TOP 100 PERCENT SalesDetail.invoiceNumber, SalesDetail.invoiceDate,
>SalesDetail.Part, SalesDetail.Price, SalesDetail.QtyOrdered, Inventory.Qty
-
>Inventory.QtyCommitted AS QtyAvailable, Inventory.Description
>SalesDetail.Customer FROM SalesDetail INNER JOIN Inventory ON
>SalesDetail.itemID = Inventory.invItemID WHERE SalesDetail.QtyOrdered >
>SalesDetail.QtyShipped AND Inventory.Qty - Inventory.QtyCommitted > 0 AND
>SalesDetail.BackOrder > 0 ORDER BY SalesDetail.invoiceDate DESC
>GO
>I linked vBackOrder in Access then used this DAO code to populate local
>table BackOrders:
>INSERT INTO BackOrders (InvoiceNumber, InvoiceDate, Part, Price, QtyOrdered
,
>QtyAvailable, Description) SELECT vBackOrder.InvoiceNumber,
>vBackOrder.InvoiceDate, vBackOrder.Part, vBackOrder.Price,
>vBackOrder.QtyOrdered, vBackOrder.QtyAvailable, vBackOrder.Description FROM
>vBackOrder WHERE vBackOrder.Customer=x
>This also gives the same error, as does attempting to open the linked
>vBackOrder in Access. I then went to QA and tried just the SELECT portion o
f
>the CREATE VIEW with the same result. To narrow it down, I tried SELECTing
*
>FROM Inventory - a little slow but no problem. I then tried the same thing
>with SalesDetail and again get the same error. I have >300 server tables of
>varying sizes in this app. SalesDetail is by far the largest, and it is the
>*only* one I get the error on. So I'm assuming it has something to do with
>the size.
>"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
> news:pflv701qqge68qf7u94r6bnetfkb1iuc53@.
4ax.com...
>Analyzer)
>find
>|||Hi Mary,
BTW I have your book and it is very good. Unfortunately, for this project I
am stuck with Access 97 and the book seems to reference Access 2000 +.
Thanks for your help and that not only works but it is *much* faster! Looks
like I'll be retrofitting all of those Views I created! Thanks again!
Regards,
Ron Hinds
"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
news:c7d280ledkacc75n7ugk5jgd8v7bibuepa@.
4ax.com...
> DAO is the problem. You're loading the Jet engine and using it for SQL
> Server data operations, something it was never designed or optimized
> to do. Create a stored procedure instead of a buinch of views. Stored
> procedures support parameters and complex logic, and return a
> read-only result set which you can use to populate your local table.
> Call the stored procedure from a pass-through query where you set the
> SQL syntax to something like this in your code (you can use DAO to set
> properties of a QueryDef object and execute it):
> qdef.SQL = "EXEC myproc 'paramvalue1', val2" etc.
> qdef.Execute
> Pass-through queries bypass the Jet engine when they're executed and
> are the most efficient way of getting back large result sets since all
> of the processing takes place on the server, not in Jet. You then
> create either an Insert or Update query that selects from your
> pass-through query into the local table. When you call the
> insert/update query it will automatically execute the pass-through
> query to get the records.
> -- Mary
> Microsoft Access Developer's Guide to SQL Server
> http://www.amazon.com/exec/obidos/ASIN/0672319446
> On Fri, 16 Apr 2004 17:09:15 -0700, "Ron Hinds"
> <__NoSpam@.__NoSpamramac.com> wrote:
>
actually[vbcol=seagreen]
fetch[vbcol=seagreen]
QtyOrdered,[vbcol=seagreen]
FROM[vbcol=seagreen]
WHERE[vbcol=seagreen]
SalesDetail.QtyShipped[vbcol=seagreen]
> 0
a[vbcol=seagreen]
SalesDetail.invoiceDate,[vbcol=seagreen]
Inventory.Qty -[vbcol=seagreen]
QtyOrdered,[vbcol=seagreen]
FROM[vbcol=seagreen]
of[vbcol=seagreen]
SELECTing *[vbcol=seagreen]
thing[vbcol=seagreen]
of[vbcol=seagreen]
the[vbcol=seagreen]
with[vbcol=seagreen]
~1,750,000[vbcol=seagreen]
Sockets]ConnectionCheckForData[vbcol=sea
green]
>|||Although the book is for a newer version, the basic concepts remain
the same, and always will, which are: fetch only needed data and
perform as much data processing on the back end. Let the FE do
presentation tasks like formatting, etc. All of the code in the
chapters for linked tables will work pretty much as-is in Access 97.
--Mary
On Mon, 19 Apr 2004 14:08:04 -0700, "Ron Hinds"
<__NoSpam@.__NoSpamramac.com> wrote:

>Hi Mary,
>BTW I have your book and it is very good. Unfortunately, for this project I
>am stuck with Access 97 and the book seems to reference Access 2000 +.
>Thanks for your help and that not only works but it is *much* faster! Looks
>like I'll be retrofitting all of those Views I created! Thanks again!
>Regards,
>Ron Hinds
>
>"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
> news:c7d280ledkacc75n7ugk5jgd8v7bibuepa@.
4ax.com...
>actually
>fetch
>QtyOrdered,
>FROM
>WHERE
>SalesDetail.QtyShipped
>a
>SalesDetail.invoiceDate,
>Inventory.Qty -
>QtyOrdered,
>FROM
>of
>SELECTing *
>thing
>of
>the
>with
>~1,750,000
>Sockets]ConnectionCheckForData
>|||Ron,
Switch to an Access 2002 data project and dump the mdb. You are looking at
a complete rewrite. Access 2002 ADPs are VERY EASY to use. Access linked t
ables create multiple connections and are prone to creating deadlocks and mu
ltitudes of other problems.
Linked tables in Access 97 worked 'OK', linked tables in 2000 and on are not
usable in a production system.
"Ron Hinds" wrote:

> I get the following error (from my front-end Access app AND Query Analyzer
)
> when accessing a particular table. It is a rather large table, ~1,750,000
> rows in it. I tried looking through "limitations" in BOL and couldn't find
> anything. Is this a server setting or ODBC setting or... ?
> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionC
heckForData
> (CheckforData()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken
>
>

ODBC driver problem occurs on table create

Hi all!

I get the following error when trying to create a table:
[Microsoft][ODBC SQL Server Driver][Named Pipes]SQL Server does not exist or access denied.[Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionOpen (Connect()).

I use the same connection for table updates and selects and never have a problem. This only happens on table create. It also only happens on one machine (Win2K with SQL server 2000 Enterprise Edition). I've run this app without getting this problem on at least ten other machines (mostly WinXP, but also a few Win2K).

Any ideas?

nscdDoes the account that you're using have permissions on that SQL Server to create new tables?|||Yes it does.

I've also changed it so that I connect using the sa account, but still get the same problem.

Cheers

nscd|||Hi all

Further investigation has revealed that my client's machine was fairly out of date and installing the latest MDAC patch from MS resolved the problem.

Cheers

nscd

Friday, March 23, 2012

ODBC Connection Problems

I'm having problems connecting to an MSDE database through ODBC.
The following message appears below:

Connection Failed:
SQL State: '01000'
SQL Server Error: 10061
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]
Connection Open (Connect())
Connection Failed:
SQL State '08001'
SQL Server Error 17
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL
Server does not exist or access denied

I've tried the following
1. Downloading the lastest version of MDAC.
2. Tried Pinging the server which was successful.
3. Setting up an ODBC connection to the server from another client which was successful.
4. Adding an entry in my hosts file to see the server.
5. Searching the archives for a solution.

Does anyone have any other ideas as I stumped?

Thanks.Are you sure you have declared the full server (and instance) name for the MSDE instance? Check services on the MSDE box, and look for MSSQL$___. The ___ is the instance name. Also, are you able to connect to the MSDE instance with any other tools (Query Analyzer, OSQL, etc.)?|||It's quite embarrassing but someone put the wrong IP address label on the server. Thanks for your help.|||As fate would have it, I am encountering a very similar problem. Everything was working fine and dandy up until two days ago when suddenly you were unable to connect to SQLServer.

At first, you were able to connect via EM but not Query Analyzer. Now suddenly you're unable to connect using either. This literally happened overnight. I'm gettign the same error message as referenced above. I'm able to ping the box just fine, but cannot isql into it or use Query Analyzer/EM.

I'm running SQL2K on SP3 / Windows 2000 Advanced Server.

ANY suggestions would be greatly appreciated.

P.S. - One of the boxes is a development box and one is a production machine. I have considered installing MDAC 8 on the dev machine to see if this does the trick. Has this worked for anyone else who was having a similar problem, and what - if any - were the side effects?

Thanks again!|||...also forgot to mention that I'm unable to create an ODBC connection to the servers as well.

Thanx

Monday, March 19, 2012

ODBC Bug in SQL Server 8?

Hi all
If I prepare the following statement:
SELECT zA."ID" AS fA_A
, zA."IDFirma" AS fA_B
, zA."Name" AS fA_C
, zA."Vorname" AS fA_D
, zA."IDMandant" AS fA_E
, zB."ID" AS fB_A
FROM AtlasTest.Adresse AS zA
INNER JOIN AtlasTest.Mandant AS zB
ON zB."ID" = zA."IDMandant" AND (zB."ID" = ?)
WHERE zA."IDFirma" = 1
ORDER BY 1
the ODBC driver then returns 0 in SQLNumResultCols. If I change the "?"
to "1", however, everything works ok (6 cols). It even works if I use a
"?" in the where clause.
SQL Server Version is 8.00.760 (SP3).
Any clues?
Regards,
Peter Arrenbrecht
Opus Software AG
Unfortunately the SQL Server driver doesn't handle these cases very well.
Are you binding the parameter before preparing the statement?
Brannon
"Peter Arrenbrecht" <arrenbrecht@.NOXXX.opus.ch> wrote in message
news:41502254.2040509@.NOXXX.opus.ch...
> Hi all
> If I prepare the following statement:
> SELECT zA."ID" AS fA_A
> , zA."IDFirma" AS fA_B
> , zA."Name" AS fA_C
> , zA."Vorname" AS fA_D
> , zA."IDMandant" AS fA_E
> , zB."ID" AS fB_A
> FROM AtlasTest.Adresse AS zA
> INNER JOIN AtlasTest.Mandant AS zB
> ON zB."ID" = zA."IDMandant" AND (zB."ID" = ?)
> WHERE zA."IDFirma" = 1
> ORDER BY 1
> the ODBC driver then returns 0 in SQLNumResultCols. If I change the "?"
> to "1", however, everything works ok (6 cols). It even works if I use a
> "?" in the where clause.
> SQL Server Version is 8.00.760 (SP3).
> Any clues?
> Regards,
> Peter Arrenbrecht
> Opus Software AG
|||No, after.
Brannon Jones wrote:
> Unfortunately the SQL Server driver doesn't handle these cases very well.
> Are you binding the parameter before preparing the statement?
> Brannon
> "Peter Arrenbrecht" <arrenbrecht@.NOXXX.opus.ch> wrote in message
> news:41502254.2040509@.NOXXX.opus.ch...
>
>
|||Ah. When you prepare the statement, the driver needs to know certain
information about each parameter (ie. data type,precision,etc). If you
don't bind the parameter, then the driver will try to describe it on
it's own. In a lot of cases this will fail (like using a parameter in
a sub-select). If you bind the parameter first, then the driver can
use the binding information you specified to describe the parameter.
In most cases, this should resolve any syntax errors you are getting.
Brannon
|||Thanks! I shall try this. It is a little annoying, though, as we relied
on the data type information from the server to cast our param values
properly. That will need some rethinking.
peo
brannonj@.gmail.com wrote:
> Ah. When you prepare the statement, the driver needs to know certain
> information about each parameter (ie. data type,precision,etc). If you
> don't bind the parameter, then the driver will try to describe it on
> it's own. In a lot of cases this will fail (like using a parameter in
> a sub-select). If you bind the parameter first, then the driver can
> use the binding information you specified to describe the parameter.
> In most cases, this should resolve any syntax errors you are getting.
> Brannon
>

Monday, March 12, 2012

ODBC Access Driver error in Vista

I am migrating my development environment from XP Pro to Vista, and my website is now serving up the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Microsoft Access Driver] Disk or network error.

The code in question is as follows:

set dbConn = Server.CreateObject("ADODB.Connection")
szPath=Server.MapPath("http://../")
szProvider="Driver=Microsoft Access Driver (*.mdb); DBQ=" & szPath
szProvider=szProvider & "\database\" & szDir & "\" & szDB & ".mdb;"
if bDebug then fpDebug.WriteLine("szProvider: " & szProvider)
dbConn.Open szProvider

The error happens on the last line where I try to open the connection. I've tried also using a dbConn.Provider="Microsoft.Jet.OLEDB.4.0" line, to no avail. The item that is passed to the Open command is

Driver=Microsoft Access Driver (*.mdb); DBQ=C:\bobstuff\database\demo\HMGA.mdb;

I'm not familiar enough with DNS and IIS to figure this out. Any suggestions -- other than porting over to SQL and .NET? You don't buy a Ferrari when you only drive in a 30 MPH town.

I'm having a similar problem. I checked my installed drivers and noticed I don't have a standard MS ODBC driver. You might want to make sure you have one. Control Panel --> Administrative Tools --> Data Sources --> Drivers Tab. MDAC doesn't seem to install on my Win Vista (x64) machine so I'm not sure where to go from here.

|||Does anyone have a solution to this issue. I have given IISUser full permissions to the Access file in the directory. I have checked the read permissions for IISUser and actually have given everyone read to the directory and the file in question. I can open the file in MS Access on my computer and update and change information in it. The same code works fine on my XP SP2 machine. I am at a loss.|||I am having exactly the same problem today... Vista ultimate, was fine on xp|||This article worked for me.

http://mikeplate.wordpress.com/2006/11/24/running-legacy-asp-scripts-on-vista-and-iis-70/|||

Thanks

This worked for me. The MS article at the bottom of the blog did not.

|||You beautiful geeks!!! I have spent hours talking to MS Tech Support, and they wanted to charge me $99 for email support, or $245 for phone support as this was a "pro-level" problem. Yes, I plan to convert over to .NET, but as a one guy development team who has over 50K lines of code, I don't want to do it right now. MSFT has a KB article that says the same thing (926939), but it uses command line instead of the GUI. Thanks for giving me a solution.

ODBC Access Driver error in Vista

I am migrating my development environment from XP Pro to Vista, and my website is now serving up the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Microsoft Access Driver] Disk or network error.

The code in question is as follows:

Driver=Microsoft Access Driver (*.mdb); DBQ=C:\bobstuff\database\demo\HMGA.mdb;

I'm not familiar enough with DNS and IIS to figure this out. Any suggestions -- other than porting over to SQL and .NET? You don't buy a Ferrari when you only drive in a 30 MPH town.

http://mikeplate.wordpress.com/2006/11/24/running-legacy-asp-scripts-on-vista-and-iis-70/|||

Thanks

This worked for me. The MS article at the bottom of the blog did not.

ODBC Access Driver error in Vista

I am migrating my development environment from XP Pro to Vista, and my website is now serving up the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Microsoft Access Driver] Disk or network error.

The code in question is as follows:

Driver=Microsoft Access Driver (*.mdb); DBQ=C:\bobstuff\database\demo\HMGA.mdb;

I'm not familiar enough with DNS and IIS to figure this out. Any suggestions -- other than porting over to SQL and .NET? You don't buy a Ferrari when you only drive in a 30 MPH town.

http://mikeplate.wordpress.com/2006/11/24/running-legacy-asp-scripts-on-vista-and-iis-70/|||

Thanks

This worked for me. The MS article at the bottom of the blog did not.

Friday, March 9, 2012

ODBC

When trying to open a link from the main Web Page of
TimberHunt I get the following information:
Microsoft OLE DB Provider for ODBC Drivers
error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Error
converting data type varchar to numeric.
/timber_trade/comp_trade_board.asp, line 60
Anyone available to help solve the problem?
Will appreciate your support.
RicardoHello,
<%
set conn1=server.CreateObject("adodb.connection")
conn1.cursorlocation=3
conn1.Open "Provider=sqloledb;" & _
"Data Source=ayaz;" & _
"Initial Catalog=ayaz;" & _
"User Id=ayaz;" & _
"Password=ayaz"
%>
try this.
Warm Regards,
Ayaz Ahmed
Software Engineer & Web Developer
Creative Chaos (Pvt.) Ltd.
"Managing Your Digital Risk"
http://www.csquareonline.com
Karachi, Pakistan
Mobile +92 300 2280950
Office +92 21 455 2414
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!

OCI-22053: overflow error

I have a report that runs the following in it's query: " (SELECT cast(d
.ACT_DT as TIMESTAMP)
FROM GPROD.VSL_ACT d
WHERE d .ses_num = gprod.vsl_act.ses_num
AND d .seq_num = gprod.vsl_act.seq_num AND d .act_dt > gprod.vsl_act.act_dt
AND
ROWNUM <= 1 AND d
.ACT_TYP_TXT = 'DEPS') - CAST(act_dt AS TIMESTAMP) ElpsTime,
(to_date(to_char((SELECT cast(d .ACT_DT as TIMESTAMP)
FROM GPROD.VSL_ACT d
WHERE d .ses_num = gprod.vsl_act.ses_num
AND d .seq_num = gprod.vsl_act.seq_num AND d .act_dt > gprod.vsl_act.act_dt
AND
ROWNUM <= 1 AND d
.ACT_TYP_TXT = 'DEPS'),'mm/dd/yyyy hh24:mi:ss'), 'mm/dd/yyyy
hh24:mi:ss')-to_date(to_char(act_dt,'mm/dd/yyyy hh24:mi:ss'), 'mm/dd/yyyy
hh24:mi:ss')) * 24 * 60 * 60 sum_seconds"
Basically, this takes two date fields, subtracting one from the other. It
works 99% of the time, but for some records, it get #ERROR in this calculated
field. I look at the data, and it makes no sense because I get the error
when the difference could be 10 seconds, or 3 minutes, or whatever. I don't
see any logic to it. The database is an Oracle 10g database. I am using VS
2005 sp1. I have created a new project as per a previous post, but still get
the error. Here is the full error:
The data set â'dsShuntâ' contains a definition for the Field â'SUM_SECONDSâ'.
The data extension returned an error during reading the field.
System.Data.OracleClient.OracleException: OCI-22053: overflow error
Thanks for any help.
Darryl.On Apr 11, 7:14 am, Darryl <Dar...@.discussions.microsoft.com> wrote:
> I have a report that runs the following in it's query: " (SELECT cast(d
> .ACT_DT as TIMESTAMP)
> FROM GPROD.VSL_ACT d
> WHERE d .ses_num = gprod.vsl_act.ses_num
> AND d .seq_num = gprod.vsl_act.seq_num AND d .act_dt > gprod.vsl_act.act_dt
> AND
> ROWNUM <= 1 AND d
> .ACT_TYP_TXT = 'DEPS') - CAST(act_dt AS TIMESTAMP) ElpsTime,
> (to_date(to_char((SELECT cast(d .ACT_DT as TIMESTAMP)
> FROM GPROD.VSL_ACT d
> WHERE d .ses_num = gprod.vsl_act.ses_num
> AND d .seq_num = gprod.vsl_act.seq_num AND d .act_dt > gprod.vsl_act.act_dt
> AND
> ROWNUM <= 1 AND d
> .ACT_TYP_TXT = 'DEPS'),'mm/dd/yyyy hh24:mi:ss'), 'mm/dd/yyyy
> hh24:mi:ss')-to_date(to_char(act_dt,'mm/dd/yyyy hh24:mi:ss'), 'mm/dd/yyyy
> hh24:mi:ss')) * 24 * 60 * 60 sum_seconds"
> Basically, this takes two date fields, subtracting one from the other. It
> works 99% of the time, but for some records, it get #ERROR in this calculated
> field. I look at the data, and it makes no sense because I get the error
> when the difference could be 10 seconds, or 3 minutes, or whatever. I don't
> see any logic to it. The database is an Oracle 10g database. I am using VS
> 2005 sp1. I have created a new project as per a previous post, but still get
> the error. Here is the full error:
> The data set 'dsShunt' contains a definition for the Field 'SUM_SECONDS'.
> The data extension returned an error during reading the field.
> System.Data.OracleClient.OracleException: OCI-22053: overflow error
> Thanks for any help.
> Darryl.
This link might provide some insight:
http://groups.google.com/group/microsoft.public.dotnet.framework.adonet/browse_thread/thread/185b831ab7a267b0/c0106784db18d83f?lnk=st&q=System.Data.OracleClient.OracleException%3A+OCI-22053%3A+overflow+error&rnum=3#c0106784db18d83f
Regards,
Enrique Martinez
Sr. Software Consultant

Occasional Error When Executing CLR Stored Procedure

This CLR stored procedure executes without fail 99% of the time. However, occasionally it will fail with the following error:

A .NET Framework error occurred during execution of user-defined routine or aggregate "Run_SRS_Report": System.Exception: Attempt to perform native server operation (AllocateNativeRequest) outside of its valid scope.

Below is the section of code that fails:

<code>

Private Shared Sub GetReportParameters()
Dim drReportParameters As SqlDataReader

Try
' set the parameters for the command request.
_SQLCommandRequest = _SQLConnection.CreateCommand()

_SQLCommandRequest.CommandText = _spReportParms

_SQLCommandRequest.CommandType = CommandType.StoredProcedure

' get the information for the data reader request.
drReportParameters = _SQLCommandRequest.ExecuteReader

' make sure that we have something first.
If Not drReportParameters Is Nothing Then

' find out if we have any rows returned.
If drReportParameters.HasRows Then

' read each of the rows looking for the respective value.
While drReportParameters.Read

' make sure that we can get the appropriate code id.
If (Not IsDBNull(drReportParameters.Item("Code_ID"))) AndAlso (Not IsDBNull(drReportParameters.Item("Display_Text"))) Then

' set the parameters for each of the following internal variables.
Select Case drReportParameters.Item("Code_ID").ToString
Case Is = "PDFP Path"
_FileLocationPDFP = drReportParameters.Item("Display_Text").ToString
Case Is = "PDF Temp Path"
_FileLocationPDFTemp = drReportParameters.Item("Display_Text").ToString
Case Is = "LogFile Path"
'''_SqlPipe.Send("Path from sys codes: " & drReportParameters.Item("Display_Text").ToString)
_FileLocationLogFile = Path.Combine(drReportParameters.Item("Display_Text").ToString, "SRSReportLog_" & Now.ToString("HHmmss") & ".txt")
Case Is = "LogFile Flag"
_swLogFlag = IIf(drReportParameters.Item("Display_Text").ToString = "False", False, True)
Case Is = "Delete PDF"
_DeletePDFFlag = IIf(drReportParameters.Item("Display_Text").ToString = "False", False, True)
Case Is = "PrintTool"
_PrintTool = drReportParameters.Item("Display_Text").ToString
Case Is = "BPP Path"
_BppPath = drReportParameters.Item("CharVar1").ToString
Case Is = "SendToPrinter"
_SendToPrinterFlag = IIf(drReportParameters.Item("Display_Text").ToString = "False", False, True)
Case Else
' ignore it.
End Select

End If

End While

End If

Catch ex As Exception
' throw a new exception to trip up the PrintReport() function.
Throw New Exception(ex.Message)

Finally

' make sure that we close out the datareader, regardless.
If Not drReportParameters Is Nothing Then
If Not drReportParameters.IsClosed Then drReportParameters.Close()
End If

End Try

End Sub

</code>

Any ideas why this would fail only occasionally (maybe 1% of the time it's executed)?

_SQLCommandRequest is a shared (that is, static) variable, right? You are seeing this error because your proceduce is being executed on multiple threads at the same time, and multiple threads are not allowed to use the same SqlConnection at the same time. (The error message is fixed in the next version of SQL Server to be more clear what the problem is).

It looks like you frequently use static variables within your SP, so even if you fix this problem by using a local, non-shared SqlCommand object, you will run into other problems due to multiple threads accessing the same shared state.

Steven
|||

Understood. Thanks for the explanation.

So all I have to do is make everything local, non-shared then right?

Except for the main sub procedure declaration below which has to remain shared right?:

<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub Run_SRS_Report(ByVal ip_URL As String _

Am I on the right track?

|||

Yes, everything should be a local or class instance field, unless it is a constant, readonly value. Ideally, you should try to deploy the assembly under SAFE or EXTERNAL_ACCESS permission set, which disallows the use of static variables to prevent this type of problem.

|||

Thanks Steven. I've changed everything up so there are no static variables and re-deployed. Hopefully that will fix the problem. If not I'll post again.

Occasional Error When Executing CLR Stored Procedure

This CLR stored procedure executes without fail 99% of the time. However, occasionally it will fail with the following error:

A .NET Framework error occurred during execution of user-defined routine or aggregate "Run_SRS_Report": System.Exception: Attempt to perform native server operation (AllocateNativeRequest) outside of its valid scope.

Below is the section of code that fails:

<code>

Private Shared Sub GetReportParameters()
Dim drReportParameters As SqlDataReader

Try
' set the parameters for the command request.
_SQLCommandRequest = _SQLConnection.CreateCommand()

_SQLCommandRequest.CommandText = _spReportParms

_SQLCommandRequest.CommandType = CommandType.StoredProcedure

' get the information for the data reader request.
drReportParameters = _SQLCommandRequest.ExecuteReader

' make sure that we have something first.
If Not drReportParameters Is Nothing Then

' find out if we have any rows returned.
If drReportParameters.HasRows Then

' read each of the rows looking for the respective value.
While drReportParameters.Read

' make sure that we can get the appropriate code id.
If (Not IsDBNull(drReportParameters.Item("Code_ID"))) AndAlso (Not IsDBNull(drReportParameters.Item("Display_Text"))) Then

End If

End While

End If

Catch ex As Exception
' throw a new exception to trip up the PrintReport() function.
Throw New Exception(ex.Message)

Finally

' make sure that we close out the datareader, regardless.
If Not drReportParameters Is Nothing Then
If Not drReportParameters.IsClosed Then drReportParameters.Close()
End If

End Try

End Sub

</code>

Any ideas why this would fail only occasionally (maybe 1% of the time it's executed)?

Steven
|||

Understood. Thanks for the explanation.

So all I have to do is make everything local, non-shared then right?

Except for the main sub procedure declaration below which has to remain shared right?:

<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub Run_SRS_Report(ByVal ip_URL As String _

Am I on the right track?

|||

Thanks Steven. I've changed everything up so there are no static variables and re-deployed. Hopefully that will fix the problem. If not I'll post again.

Wednesday, March 7, 2012

Obtaining different timings for the same process?

Dear fellows,
The following loop bring me differents results, execute one after one (among
them a truncate table, of course):
DECLARE @.loop as integer
set @.loop = 1
while @.loop < 10000
begin
insert into A_test(id,nombre,ape) values(@.loop,'a','aadfasdf')
set @.loop = @.loop + 1
end
1st: 26 sec.
2nd: 30 sec.
3rd: 32 sec.
What's happening?
Any input would be much appreciated.
Enric"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:F66A1DC3-E5CD-4B06-AE4E-53818843E3C5@.microsoft.com...
> Dear fellows,
> The following loop bring me differents results, execute one after one
> (among
> them a truncate table, of course):
> DECLARE @.loop as integer
> set @.loop = 1
> while @.loop < 10000
> begin
> insert into A_test(id,nombre,ape) values(@.loop,'a','aadfasdf')
> set @.loop = @.loop + 1
> end
>
> 1st: 26 sec.
> 2nd: 30 sec.
> 3rd: 32 sec.
> What's happening?
> Any input would be much appreciated.
> Enric
What other processes are running on your server while this is running.
Page splitting or allocation of new extents to store your data may be
happening.
Indexes may be getting updated
Statistics may be getting updated
It's hard to say what the real culprit is. These are some things that I
would check however.
Rick Sawtell
MCT, MCSD, MCDBA|||Well, what does your Profiler say?
ML
http://milambda.blogspot.com/|||Thanks to both for the quick responses but I was wondering about the
difference among them. It seems very high. We are talking about 6 or 7
seconds for a total of 35 more or less..
"Rick Sawtell" wrote:

> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:F66A1DC3-E5CD-4B06-AE4E-53818843E3C5@.microsoft.com...
> What other processes are running on your server while this is running.
> Page splitting or allocation of new extents to store your data may be
> happening.
> Indexes may be getting updated
> Statistics may be getting updated
>
> It's hard to say what the real culprit is. These are some things that I
> would check however.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

Friday, February 24, 2012

Obtaining all the dates

Dear all,
According to a date introduce I would need to obtain all the periodDesc of
the following table:
CREATE TABLE [dbo].[tbl_Periods] (
[sinStudyID] [smallint] NOT NULL ,
[strStudy] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[boldone] [bit] NOT NULL ,
[sinPeriodID] [smallint] NOT NULL ,
[strPeriodDesc] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[datPeriodBegin] [datetime] NULL ,
[datPeriodEnd] [datetime] NULL ,
)
If I have got as input '2005-01-02' I need obtain this set of rows:
begin end
4 COW 0 203 W2005006 2005-01-30 2005-01-31
4 COW 0 202 W2005005 2005-01-23 2005-01-29
4 COW 0 201 W2005004 2005-01-16 2005-01-22
4 COW 0 196 W2005003 2005-01-09 2005-01-15
4 COW 0 195 W2005002 2005-01-02 2005-01-08
4 COW 0 194 W2005001 2005-01-01 2005-01-01
Any advice will be well received.
Regards,SELECT <column lists> FROM Table
WHERE datPeriodBegin >=@.dt AND datPeriodEnd < dateadd(day,1,@.dt)
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:BD321F1D-7679-4CFF-AFAB-137307FDCC84@.microsoft.com...
> Dear all,
> According to a date introduce I would need to obtain all the periodDesc of
> the following table:
>
> CREATE TABLE [dbo].[tbl_Periods] (
> [sinStudyID] [smallint] NOT NULL ,
> [strStudy] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [boldone] [bit] NOT NULL ,
> [sinPeriodID] [smallint] NOT NULL ,
> [strPeriodDesc] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [datPeriodBegin] [datetime] NULL ,
> [datPeriodEnd] [datetime] NULL ,
> )
> If I have got as input '2005-01-02' I need obtain this set of rows:
> begin end
> 4 COW 0 203 W2005006 2005-01-30 2005-01-31
> 4 COW 0 202 W2005005 2005-01-23 2005-01-29
> 4 COW 0 201 W2005004 2005-01-16 2005-01-22
> 4 COW 0 196 W2005003 2005-01-09 2005-01-15
> 4 COW 0 195 W2005002 2005-01-02 2005-01-08
> 4 COW 0 194 W2005001 2005-01-01 2005-01-01
> Any advice will be well received.
> Regards,|||And the date you provide is related to the datPeriodBegin and datPeriodEnd
in which way? Is it related to datPeriodBegin only, datPeriodEnd only or
both?
Jacco Schalkwijk
SQL Server MVP
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:BD321F1D-7679-4CFF-AFAB-137307FDCC84@.microsoft.com...
> Dear all,
> According to a date introduce I would need to obtain all the periodDesc of
> the following table:
>
> CREATE TABLE [dbo].[tbl_Periods] (
> [sinStudyID] [smallint] NOT NULL ,
> [strStudy] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [boldone] [bit] NOT NULL ,
> [sinPeriodID] [smallint] NOT NULL ,
> [strPeriodDesc] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [datPeriodBegin] [datetime] NULL ,
> [datPeriodEnd] [datetime] NULL ,
> )
> If I have got as input '2005-01-02' I need obtain this set of rows:
> begin end
> 4 COW 0 203 W2005006 2005-01-30 2005-01-31
> 4 COW 0 202 W2005005 2005-01-23 2005-01-29
> 4 COW 0 201 W2005004 2005-01-16 2005-01-22
> 4 COW 0 196 W2005003 2005-01-09 2005-01-15
> 4 COW 0 195 W2005002 2005-01-02 2005-01-08
> 4 COW 0 194 W2005001 2005-01-01 2005-01-01
> Any advice will be well received.
> Regards,|||Hi,
Only could be this, nothing else:
2005-01-30
2005-01-23
2005-01-16
2005-01-09
2005-01-02
2005-01-01
Thanks,
"Jacco Schalkwijk" wrote:

> And the date you provide is related to the datPeriodBegin and datPeriodEnd
> in which way? Is it related to datPeriodBegin only, datPeriodEnd only or
> both?
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:BD321F1D-7679-4CFF-AFAB-137307FDCC84@.microsoft.com...
>
>|||Sorry, these former values are efectively just for datPeriodBegin
Best wishes,
"Enric" wrote:
> Hi,
>
> Only could be this, nothing else:
> 2005-01-30
> 2005-01-23
> 2005-01-16
> 2005-01-09
> 2005-01-02
> 2005-01-01
>
> Thanks,
> "Jacco Schalkwijk" wrote:
>|||And how does that list of dates relate to 2005-01-02? All in the same month?
In that case:
DECLARE @.date DATETIME
SET @.date = '20050102'
SELECT [sinStudyID] , [strStudy], [boldone], [sinPeriodID],
[strPeriodDesc], [datPeriodBegin], [datPeriodEnd]
FROM [tbl_Periods]
WHERE datPeriodBegin >= DATEADD(dd, 1 - DAY(@.dt), @.dt)
AND datPeriodBegin < DATEADD(mm, 1, DATEADD(dd, 1 - DAY(@.dt), @.dt))
Jacco Schalkwijk
SQL Server MVP
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:9540B0BB-7599-4C48-A6B4-EE78E9585884@.microsoft.com...
> Sorry, these former values are efectively just for datPeriodBegin
> Best wishes,
> "Enric" wrote:
>|||It works amazingly.
Thanks a lot man,
"Jacco Schalkwijk" wrote:

> And how does that list of dates relate to 2005-01-02? All in the same mont
h?
> In that case:
> DECLARE @.date DATETIME
> SET @.date = '20050102'
> SELECT [sinStudyID] , [strStudy], [boldone], [sinPeriodID],
> [strPeriodDesc], [datPeriodBegin], [datPeriodEnd]
> FROM [tbl_Periods]
> WHERE datPeriodBegin >= DATEADD(dd, 1 - DAY(@.dt), @.dt)
> AND datPeriodBegin < DATEADD(mm, 1, DATEADD(dd, 1 - DAY(@.dt), @.dt))
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:9540B0BB-7599-4C48-A6B4-EE78E9585884@.microsoft.com...
>
>

Obtained an error when performing a dbcc on an sms database

An error occurred while executing the following
query: "DBCC CHECKCATALOG([sms_055])".
SQL error number: "09E8".
SQL error message: "DBCC results for 'sms_055'.
".
Thanks for your help in advance!!It seems you didn't execute the DBCC from Query Analyzer (QA doesn't return error number in hex).
The actual error from SQL Server isn't included here, so we cannot comment on what the problem might
be. I suggest that you execute the DBCC from Query Analyzer and post the full output here. Pls add
WITH NO_INFOMSGS so you don't get all those informational messages.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Maria Garcia" <garcim@.miamidade.gov> wrote in message
news:472801c3e438$9778be40$a001280a@.phx.gbl...
> An error occurred while executing the following
> query: "DBCC CHECKCATALOG([sms_055])".
> SQL error number: "09E8".
> SQL error message: "DBCC results for 'sms_055'.
> ".
> Thanks for your help in advance!!

Obtain unit percent with unit count divided by total count in query

The following query returns a value of 0 for the unit percent when I do a count/subquery count. Is there a way to get the percent count using a subquery? Another section of the query using the sum() works.

Here is a test code snippet:

--Test Count/Count subquery

declare @.Date datetime

set @.date = '8/15/2007'

select

-- count returns unit data

Count(substring(m.PTNumber,3,3)) as PTCnt,

-- count returns total for all units

(select Count(substring(m1.PTNumber,3,3))

from tblVGD1_Master m1

left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID

Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9

and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0

and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)

and v1.[Date] between DateAdd(dd,-90,@.Date) and @.Date) as TotalCnt,

-- attempting to calculate the percent by PTCnt/TotalCnt returns 0

(Count(substring(m.PTNumber,3,3)) /

(select Count(substring(m1.PTNumber,3,3))

from tblVGD1_Master m1

left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID

Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9

and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0

and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)

and v1.[Date] between DateAdd(dd,-90,@.Date) and @.Date)) as AUPct

-- main select

from tblVGD1_Master m

left join tblVGD1_ClassIII v on m.SlotNum_ID = v.SlotNum_ID

Where left(m.PTNumber,2) = 'PT' and m.Denom_ID <> 9

and v.Act = 1 and m.Active = 1 and v.MnyPlyd <> 0

and not (v.MnyPlyd = v.MnyWon and v.ActWin = 0)

and v.[Date] between DateAdd(dd,-90,@.Date) and @.Date

group by substring(m.PTNumber, 3,3)

order by AUPct Desc

Thanks. Dan

I figured out my solution - The top integer needs to be cast as a decimal:

(CAST(Count(substring(m.PTNumber,3,3)) as Decimal(15,5))/

(select Count(substring(m1.PTNumber,3,3)) from tblVGD1_Master m1

left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID

Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9

and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0

and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)

and v1.[Date] between DateAdd(dd,-90,@.Date) and @.Date)) * 100 as AUPct

obtain first and last occurance of contiguous blocks of data

Hello everyone, I am in need of some assistance, and was hoping someone
could help me out.
Essentially my problem boils down to the following...
I have a table that contains a date variable and an indication whether the
system is on or off:
[system] [date] [isOn]
A 01 0
A 04 1
A 05 1
A 06 0
A 20 1
A 21 0
A 25 0
A 27 1
A 32 1
A 33 1
A 34 0
A 40 1
B 41 1
B 45 0
B 49 1
B 50 1
B 51 1
B 53 1
B 67 0
What I need to derive is a table that shows the following
[system] [date switched ON] [date switched OFF]
A 04 06
A 20 21
A 27 34
A 40 NULL
B 41 45
B 49 67
So each row contains three variable:
0. first variable: The system concerned
1. second variable: The date the system was switched ON: i.e. the first
member of an occurance a contiguous block of 1's
2. third variable: The date the system was switched OFF: i.e. the subsequent
first member of an occurance of a contiguous block of 0's
... then the next row contains the next member of an occurance of contig.
block of 1's, and subsequent first member of an occurance of a contiguous
block of 0's etc etc
A NULL is placed where for a given system, the date switched OFF is not
defined
I am thinking of utilizing a cursor to run through the list.
Would anyone have a non-sequencial way of looking at addressing the problem
using normal SQL?
Any help most appreciated!
Many thanks
wileyYour options are to use a complicated correlated subquery, or to use a
cursor. This is one of the very few cases where a cursor can provide better
performance than a set-based operation. Use a table variable and insert the
values into it as you walk through the table.
"wiley" <w.smith@.nospam.com> wrote in message
news:O2uIWVQFGHA.376@.TK2MSFTNGP12.phx.gbl...
> Hello everyone, I am in need of some assistance, and was hoping someone
> could help me out.
> Essentially my problem boils down to the following...
> I have a table that contains a date variable and an indication whether the
> system is on or off:
> [system] [date] [isOn]
> A 01 0
> A 04 1
> A 05 1
> A 06 0
> A 20 1
> A 21 0
> A 25 0
> A 27 1
> A 32 1
> A 33 1
> A 34 0
> A 40 1
> B 41 1
> B 45 0
> B 49 1
> B 50 1
> B 51 1
> B 53 1
> B 67 0
>
> What I need to derive is a table that shows the following
> [system] [date switched ON] [date switched OFF]
> A 04 06
> A 20 21
> A 27 34
> A 40 NULL
> B 41 45
> B 49 67
> So each row contains three variable:
> 0. first variable: The system concerned
> 1. second variable: The date the system was switched ON: i.e. the first
> member of an occurance a contiguous block of 1's
> 2. third variable: The date the system was switched OFF: i.e. the
> subsequent first member of an occurance of a contiguous block of 0's
> ... then the next row contains the next member of an occurance of contig.
> block of 1's, and subsequent first member of an occurance of a contiguous
> block of 0's etc etc
> A NULL is placed where for a given system, the date switched OFF is not
> defined
> I am thinking of utilizing a cursor to run through the list.
> Would anyone have a non-sequencial way of looking at addressing the
> problem using normal SQL?
> Any help most appreciated!
> Many thanks
> wiley
>|||Thanks for the suggestion! I knew id be wasting my time trying to find a
non-sequential algorithm.
I was working on the cursor-based solution as soon as i sent my post. I
really took a step back and mapped out the sequence via a flowchart and used
simple goto statements in my solution. I tested and it seems to be working.
I know... I really should be use WHILE statements etc. but i had to hack
this fast for a DTS process algorithm i need to implement tomorrow. I'll
fashion it in terms of WHILE statements, comments, and decent variables etc
in time...
declare @.system char(1), @.currentsystem char(1)
declare @.date int, @.begin int, @.end int
declare @.systemon int, @.previous int
delete resulttable
declare system_cursor cursor for
select [system], [date], systemon
from contract1
order by 1, 2
open system_cursor
a:
fetch next from system_cursor into @.system, @.date, @.systemon
if @.@.FETCH_STATUS = -1
goto z
b:
set @.currentsystem = @.system
if @.systemon <> 1
goto a
c:
set @.begin = @.date
d:
set @.previous = 1
fetch next from system_cursor into @.system, @.date, @.systemon
if @.@.FETCH_STATUS = -1
goto z
if @.currentsystem <> @.system
begin
set @.end = NULL
insert into resulttable select @.currentsystem, @.begin, @.end
goto b
end
if (@.systemon = 1 and @.previous = 1)
goto d
set @.end = @.date
insert into resulttable select @.currentsystem, @.begin, @.end
e:
set @.previous = 0
fetch next from system_cursor into @.system, @.date, @.systemon
if @.@.FETCH_STATUS = -1
goto z
if @.currentsystem <> @.system
goto b
if (@.systemon = 0 and @.previous = 0)
goto e
goto c
z:
close system_cursor
deallocate system_cursor
---
cheers, wiley
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:utWcAzQFGHA.3308@.TK2MSFTNGP10.phx.gbl...
> Your options are to use a complicated correlated subquery, or to use a
> cursor. This is one of the very few cases where a cursor can provide
> better performance than a set-based operation. Use a table variable and
> insert the values into it as you walk through the table.
> "wiley" <w.smith@.nospam.com> wrote in message
> news:O2uIWVQFGHA.376@.TK2MSFTNGP12.phx.gbl...
>|||Why not doing such reports on the client side?
"wiley" <w.smith@.nospam.com> wrote in message
news:u7l0YmRFGHA.216@.TK2MSFTNGP15.phx.gbl...
> Thanks for the suggestion! I knew id be wasting my time trying to find a
> non-sequential algorithm.
> I was working on the cursor-based solution as soon as i sent my post. I
> really took a step back and mapped out the sequence via a flowchart and
> used simple goto statements in my solution. I tested and it seems to be
> working. I know... I really should be use WHILE statements etc. but i had
> to hack this fast for a DTS process algorithm i need to implement
> tomorrow. I'll fashion it in terms of WHILE statements, comments, and
> decent variables etc in time...
> --
> declare @.system char(1), @.currentsystem char(1)
> declare @.date int, @.begin int, @.end int
> declare @.systemon int, @.previous int
> delete resulttable
> declare system_cursor cursor for
> select [system], [date], systemon
> from contract1
> order by 1, 2
> open system_cursor
> a:
> fetch next from system_cursor into @.system, @.date, @.systemon
> if @.@.FETCH_STATUS = -1
> goto z
> b:
> set @.currentsystem = @.system
> if @.systemon <> 1
> goto a
> c:
> set @.begin = @.date
> d:
> set @.previous = 1
> fetch next from system_cursor into @.system, @.date, @.systemon
> if @.@.FETCH_STATUS = -1
> goto z
> if @.currentsystem <> @.system
> begin
> set @.end = NULL
> insert into resulttable select @.currentsystem, @.begin, @.end
> goto b
> end
> if (@.systemon = 1 and @.previous = 1)
> goto d
> set @.end = @.date
> insert into resulttable select @.currentsystem, @.begin, @.end
> e:
> set @.previous = 0
> fetch next from system_cursor into @.system, @.date, @.systemon
> if @.@.FETCH_STATUS = -1
> goto z
> if @.currentsystem <> @.system
> goto b
> if (@.systemon = 0 and @.previous = 0)
> goto e
> goto c
> z:
> close system_cursor
> deallocate system_cursor
> ---
> cheers, wiley
>
> "Brian Selzer" <brian@.selzer-software.com> wrote in message
> news:utWcAzQFGHA.3308@.TK2MSFTNGP10.phx.gbl...
>|||I need this algorithm to pre-process data for an olap cube. Hence the DTS
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23X2pbuRFGHA.532@.TK2MSFTNGP15.phx.gbl...
> Why not doing such reports on the client side?
>
>
> "wiley" <w.smith@.nospam.com> wrote in message
> news:u7l0YmRFGHA.216@.TK2MSFTNGP15.phx.gbl...
>|||Wiley,
You may want to give the following query a whirl before you opt for cursors:
select system,
date as [date switched ON],
(select top 1 date
from t1 as sysoff
where sysoff.system = syson.system
and sysoff.isOn = 0
and sysoff.date > syson.date
order by date) as [date switched OFF]
from t1 as syson
where isOn = 1
and coalesce(
(select top 1 isOn
from t1 as prev
where prev.system = syson.system
and prev.date < syson.date
order by date desc), 0) <> 1;
Though I'd definitely compare its performance to cursor code, as the cursor
code may end up being faster in this case.
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
"wiley" <w.smith@.nospam.com> wrote in message
news:O2uIWVQFGHA.376@.TK2MSFTNGP12.phx.gbl...
> Hello everyone, I am in need of some assistance, and was hoping someone
> could help me out.
> Essentially my problem boils down to the following...
> I have a table that contains a date variable and an indication whether the
> system is on or off:
> [system] [date] [isOn]
> A 01 0
> A 04 1
> A 05 1
> A 06 0
> A 20 1
> A 21 0
> A 25 0
> A 27 1
> A 32 1
> A 33 1
> A 34 0
> A 40 1
> B 41 1
> B 45 0
> B 49 1
> B 50 1
> B 51 1
> B 53 1
> B 67 0
>
> What I need to derive is a table that shows the following
> [system] [date switched ON] [date switched OFF]
> A 04 06
> A 20 21
> A 27 34
> A 40 NULL
> B 41 45
> B 49 67
> So each row contains three variable:
> 0. first variable: The system concerned
> 1. second variable: The date the system was switched ON: i.e. the first
> member of an occurance a contiguous block of 1's
> 2. third variable: The date the system was switched OFF: i.e. the
> subsequent first member of an occurance of a contiguous block of 0's
> ... then the next row contains the next member of an occurance of contig.
> block of 1's, and subsequent first member of an occurance of a contiguous
> block of 0's etc etc
> A NULL is placed where for a given system, the date switched OFF is not
> defined
> I am thinking of utilizing a cursor to run through the list.
> Would anyone have a non-sequencial way of looking at addressing the
> problem using normal SQL?
> Any help most appreciated!
> Many thanks
> wiley
>|||Wiley,
try the following:
SET NOCOUNT ON;
SET ANSI_NULLS ON;
USE Your_DB;
IF EXISTS(SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE table_name='SwitchHistory') DROP TABLE SwitchHistory;
CREATE TABLE SwitchHistory(
system_id CHAR(1) NOT NULL,
switch_date INTEGER NOT NULL CHECK(switch_date > 0),
system_state INTEGER NOT NULL CHECK(system_state IN (0, 1)),
PRIMARY KEY(system_id, switch_date));
INSERT INTO switchHistory
SELECT 'A', 1, 0 UNION ALL
SELECT 'A', 4, 1 UNION ALL
SELECT 'A', 5, 1 UNION ALL
SELECT 'A', 6, 0 UNION ALL
SELECT 'A', 20, 1 UNION ALL
SELECT 'A', 21, 0 UNION ALL
SELECT 'A', 25, 0 UNION ALL
SELECT 'A', 27, 1 UNION ALL
SELECT 'A', 32, 1 UNION ALL
SELECT 'A', 33, 1 UNION ALL
SELECT 'A', 34, 0 UNION ALL
SELECT 'A', 40, 1 UNION ALL
SELECT 'B', 41, 1 UNION ALL
SELECT 'B', 45, 0 UNION ALL
SELECT 'B', 49, 1 UNION ALL
SELECT 'B', 50, 1 UNION ALL
SELECT 'B', 51, 1 UNION ALL
SELECT 'B', 53, 1 UNION ALL
SELECT 'B', 67, 0;
SELECT H2.system_id,
MIN(H1.switch_date) AS switch_to_on,
MIN(H6.switch_date) AS switch_to_off
FROM switchHistory AS H1
INNER JOIN switchHistory AS H2
ON H2.system_id = H1.system_id
AND H2.switch_date >= H1.switch_date
AND H2.system_state = H1.system_state
LEFT OUTER JOIN switchHistory AS H6
ON H6.system_id = H2.system_id
AND H6.switch_date > H2.switch_date
AND H6.system_state = 0
WHERE H1.system_state = 1
AND NOT EXISTS(SELECT *
FROM switchHistory AS H3
WHERE H3.system_id = H2.system_id
AND H3.switch_date
BETWEEN H1.switch_date AND H2.switch_date
AND H3.system_state = 0)
AND NOT EXISTS(SELECT *
FROM switchHistory AS H4
WHERE H4.system_id = H2.system_id
AND H4.switch_date = (SELECT MIN(H5.switch_date)
FROM switchHistory AS H5
WHERE H5.switch_date >
H2.switch_date)
AND H4.system_state = 1)
GROUP BY H2.system_id, H2.switch_date;
Andrey Odegov
avodeGOV@.yandex.ru
(remove GOV to respond)
"wiley" <w.smith@.nospam.com>: news:O2uIWVQFGHA.376@.TK2MSFTNGP12.phx.gbl...
> Hello everyone, I am in need of some assistance, and was hoping someone
> could help me out.
> Essentially my problem boils down to the following...
> I have a table that contains a date variable and an indication whether the
> system is on or off:
> [system] [date] [isOn]
> A 01 0
> A 04 1
> A 05 1
> A 06 0
> A 20 1
> A 21 0
> A 25 0
> A 27 1
> A 32 1
> A 33 1
> A 34 0
> A 40 1
> B 41 1
> B 45 0
> B 49 1
> B 50 1
> B 51 1
> B 53 1
> B 67 0
>
> What I need to derive is a table that shows the following
> [system] [date switched ON] [date switched OFF]
> A 04 06
> A 20 21
> A 27 34
> A 40 NULL
> B 41 45
> B 49 67
> So each row contains three variable:
> 0. first variable: The system concerned
> 1. second variable: The date the system was switched ON: i.e. the first
> member of an occurance a contiguous block of 1's
> 2. third variable: The date the system was switched OFF: i.e. the
> subsequent first member of an occurance of a contiguous block of 0's
> ... then the next row contains the next member of an occurance of contig.
> block of 1's, and subsequent first member of an occurance of a contiguous
> block of 0's etc etc
> A NULL is placed where for a given system, the date switched OFF is not
> defined
> I am thinking of utilizing a cursor to run through the list.
> Would anyone have a non-sequencial way of looking at addressing the
> problem using normal SQL?
> Any help most appreciated!
> Many thanks
> wiley
>|||Itzik,
Thank you so much for the algorithm you put forward. I have tested it and it
performs far better as compared to my cursor implementation. On a set of
company data containing in excess of 1.5 million rows, your algorithm takes
approx. 1.5 min, whereas mine takes an average of 45 min. My algorithm
doesnt have a proper exiting feature based on instance where the last row of
data read contains isOn = 1 (error on my part). Yours however picks up on
this which is great!
Thanks so much again! much appreciated!
cheers
wiley
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:ey9Y3ySFGHA.2708@.TK2MSFTNGP11.phx.gbl...
> Wiley,
> You may want to give the following query a whirl before you opt for
> cursors:
> select system,
> date as [date switched ON],
> (select top 1 date
> from t1 as sysoff
> where sysoff.system = syson.system
> and sysoff.isOn = 0
> and sysoff.date > syson.date
> order by date) as [date switched OFF]
> from t1 as syson
> where isOn = 1
> and coalesce(
> (select top 1 isOn
> from t1 as prev
> where prev.system = syson.system
> and prev.date < syson.date
> order by date desc), 0) <> 1;
> Though I'd definitely compare its performance to cursor code, as the
> cursor code may end up being faster in this case.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> www.insidetsql.com
>
> "wiley" <w.smith@.nospam.com> wrote in message
> news:O2uIWVQFGHA.376@.TK2MSFTNGP12.phx.gbl...
>|||I modified my query:
SET STATISTICS IO ON;
SELECT H1.system_id, H1.switch_date AS switch_to_on,
MIN(H4.switch_date) AS switch_to_off
FROM SwitchHistory AS H1
LEFT OUTER JOIN SwitchHistory AS H4
ON H4.system_state = 0
AND H4.system_id = H1.system_id
AND H4.switch_date > H1.switch_date
WHERE H1.system_state = 1
AND NOT EXISTS(SELECT *
FROM SwitchHistory AS H2
WHERE H2.system_state = 1 AND H2.system_id = H1.system_id
AND H2.switch_date = (SELECT MAX(H3.switch_date)
FROM SwitchHistory AS H3
WHERE H3.system_id =
H1.system_id
AND H3.switch_date <
H1.switch_date))
GROUP BY H1.system_id, H1.switch_date;
SET STATISTICS IO OFF;
Andrey Odegov
avodeGOV@.yandex.ru
(remove GOV to respond)
"Andrey Odegov" <avodeGOV@.yandex.ru> wrote in message
news:%23$1mHbVFGHA.208@.tk2msftngp13.phx.gbl...
> Wiley,
> try the following:
> SET NOCOUNT ON;
> SET ANSI_NULLS ON;
> USE Your_DB;
> IF EXISTS(SELECT *
> FROM INFORMATION_SCHEMA.TABLES
> WHERE table_name='SwitchHistory') DROP TABLE SwitchHistory;
> CREATE TABLE SwitchHistory(
> system_id CHAR(1) NOT NULL,
> switch_date INTEGER NOT NULL CHECK(switch_date > 0),
> system_state INTEGER NOT NULL CHECK(system_state IN (0, 1)),
> PRIMARY KEY(system_id, switch_date));
> INSERT INTO switchHistory
> SELECT 'A', 1, 0 UNION ALL
> SELECT 'A', 4, 1 UNION ALL
> SELECT 'A', 5, 1 UNION ALL
> SELECT 'A', 6, 0 UNION ALL
> SELECT 'A', 20, 1 UNION ALL
> SELECT 'A', 21, 0 UNION ALL
> SELECT 'A', 25, 0 UNION ALL
> SELECT 'A', 27, 1 UNION ALL
> SELECT 'A', 32, 1 UNION ALL
> SELECT 'A', 33, 1 UNION ALL
> SELECT 'A', 34, 0 UNION ALL
> SELECT 'A', 40, 1 UNION ALL
> SELECT 'B', 41, 1 UNION ALL
> SELECT 'B', 45, 0 UNION ALL
> SELECT 'B', 49, 1 UNION ALL
> SELECT 'B', 50, 1 UNION ALL
> SELECT 'B', 51, 1 UNION ALL
> SELECT 'B', 53, 1 UNION ALL
> SELECT 'B', 67, 0;
> SELECT H2.system_id,
> MIN(H1.switch_date) AS switch_to_on,
> MIN(H6.switch_date) AS switch_to_off
> FROM switchHistory AS H1
> INNER JOIN switchHistory AS H2
> ON H2.system_id = H1.system_id
> AND H2.switch_date >= H1.switch_date
> AND H2.system_state = H1.system_state
> LEFT OUTER JOIN switchHistory AS H6
> ON H6.system_id = H2.system_id
> AND H6.switch_date > H2.switch_date
> AND H6.system_state = 0
> WHERE H1.system_state = 1
> AND NOT EXISTS(SELECT *
> FROM switchHistory AS H3
> WHERE H3.system_id = H2.system_id
> AND H3.switch_date
> BETWEEN H1.switch_date AND H2.switch_date
> AND H3.system_state = 0)
> AND NOT EXISTS(SELECT *
> FROM switchHistory AS H4
> WHERE H4.system_id = H2.system_id
> AND H4.switch_date = (SELECT MIN(H5.switch_date)
> FROM switchHistory AS H5
> WHERE H5.switch_date >
> H2.switch_date)
> AND H4.system_state = 1)
> GROUP BY H2.system_id, H2.switch_date;
> --
> Andrey Odegov
> avodeGOV@.yandex.ru
> (remove GOV to respond)
> "wiley" <w.smith@.nospam.com>: news:O2uIWVQFGHA.376@.TK2MSFTNGP12.phx.gbl...
>|||Thanks for the query Andrey!
I will be testing it tomorrow. Many thanks!
cheers, wiley
"Andrey Odegov" <avodeGOV@.mail.ru> wrote in message
news:OoqSNpeFGHA.1124@.TK2MSFTNGP10.phx.gbl...
>I modified my query:
> SET STATISTICS IO ON;
> SELECT H1.system_id, H1.switch_date AS switch_to_on,
> MIN(H4.switch_date) AS switch_to_off
> FROM SwitchHistory AS H1
> LEFT OUTER JOIN SwitchHistory AS H4
> ON H4.system_state = 0
> AND H4.system_id = H1.system_id
> AND H4.switch_date > H1.switch_date
> WHERE H1.system_state = 1
> AND NOT EXISTS(SELECT *
> FROM SwitchHistory AS H2
> WHERE H2.system_state = 1 AND H2.system_id =
> H1.system_id
> AND H2.switch_date = (SELECT MAX(H3.switch_date)
> FROM SwitchHistory AS H3
> WHERE H3.system_id =
> H1.system_id
> AND H3.switch_date <
> H1.switch_date))
> GROUP BY H1.system_id, H1.switch_date;
> SET STATISTICS IO OFF;
> --
> Andrey Odegov
> avodeGOV@.yandex.ru
> (remove GOV to respond)
> "Andrey Odegov" <avodeGOV@.yandex.ru> wrote in message
> news:%23$1mHbVFGHA.208@.tk2msftngp13.phx.gbl...
>

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

Friday, March 30, 2012

ODBC Error

odbc error

odbc error

ODBC Error

ODBC driver problem occurs on table create

Friday, March 23, 2012

ODBC Connection Problems

Monday, March 19, 2012

ODBC Bug in SQL Server 8?

Monday, March 12, 2012

ODBC Access Driver error in Vista

ODBC Access Driver error in Vista

ODBC Access Driver error in Vista

Friday, March 9, 2012

ODBC

OCI-22053: overflow error

Occasional Error When Executing CLR Stored Procedure

Occasional Error When Executing CLR Stored Procedure

Wednesday, March 7, 2012

Obtaining different timings for the same process?

Friday, February 24, 2012

Obtaining all the dates

Obtained an error when performing a dbcc on an sms database

Obtain unit percent with unit count divided by total count in query

obtain first and last occurance of contiguous blocks of data

Monday, February 20, 2012

Obscure error while attempting to install SQL Server 2005 SP1

Objects related to DB

Blog Archive

About Me