Microsoft OLE DB Provider for SQL Server error '80004005' - Unspecified error

Hi,
I have an ASP code that calls a stored procedure. I use a Command
object to execute the stored procedure. The Stored procedure returns
data when executed in Query analyser. But it takes around 1 minute for
this. The stored procedure involves querying a huge amount of data.
When I try to open the ASP page, I get the following error :
Microsoft OLE DB Provider for SQL Server error '80004005'
Unspecified error
Any suggestions?
TIA,
AnishyaHi
Have you checked with SQL Profiler that the procedure is being called? Check
that the login/user you are using has the correct permissions. If this has
timed out then I would expect that a TIMEOUT error message. You may also want
to check http://www.aspfaq.com/show.asp?id=2009
John
"Ani" wrote:
> Hi,
> I have an ASP code that calls a stored procedure. I use a Command
> object to execute the stored procedure. The Stored procedure returns
> data when executed in Query analyser. But it takes around 1 minute for
> this. The stored procedure involves querying a huge amount of data.
> When I try to open the ASP page, I get the following error :
> Microsoft OLE DB Provider for SQL Server error '80004005'
> Unspecified error
> Any suggestions?
> TIA,
> Anishya
>|||Hi,
The procedure is being called in the Profiler. I tried executing the
same stored procedure call in query analyser and saw that it works
fine. The only problem is that it takes around 1 minute. So I am sure
this is why the ASP page gives the error. The stored proc makes the use
of a temporary table. I tried executing the stored proc by using a
permanent table instead of the temporary table. It doesnt take much
time. But doesn't permanent tables involve more performance overheads
than temporary tables? I am trying to find another alternative. Is
there any way that I could increase the timeout period for ASP pages?
Thanks,
Anishya|||Hi
You could be seeing contention on tempdb if a lot of people are
simulataneously creating temporary tables, see
http://support.microsoft.com/kb/328551. You should see if the temporary table
is really necessary or possibly use a table variable as an alternative.
You may also want to change the timeout on your command object
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdprocommandtimeout.asp
John
"Ani" wrote:
> Hi,
> The procedure is being called in the Profiler. I tried executing the
> same stored procedure call in query analyser and saw that it works
> fine. The only problem is that it takes around 1 minute. So I am sure
> this is why the ASP page gives the error. The stored proc makes the use
> of a temporary table. I tried executing the stored proc by using a
> permanent table instead of the temporary table. It doesnt take much
> time. But doesn't permanent tables involve more performance overheads
> than temporary tables? I am trying to find another alternative. Is
> there any way that I could increase the timeout period for ASP pages?
> Thanks,
> Anishya
>

Micorsoft ActiveX Data object could not be loaded

Hi all,

I wanna use the Microsoft ActiveX Data Object (Multi-dimensional)2.6 Library component
in my VB application, but it returns:
" 'C:\Program Files\Common Files\System\ado\msadomd.dll' could not be loaded "

How to solve this problem?
I am using:
SQL Server 2000 SP2
Analysis Service Sp2

RickYour dll is probably not registered properly. Try to unregister the dll and re-register it. Or download the ado 2.7 version and install it.|||I am running on win2k server, is it safe to use 2.7?
I'm now using MDAC 2.6 SP2 (2.62.7926.1)
thanks~

Rick|||If you feel uncomfortable installing 2.7, I would try unregistering the dll and re-registering. If that does not work, try to reinstall 2.6. One of the sps for sql server may update the dll, so make a note as to which version you are currently using - it may be as simple as an unregistered or corrupted dll.

Good luck.|||Thanks again!
I have use the MDAC Component Checker from the M$ website,
it seems that my version is 2.6 SP2 (2.62.7926.1) and
meets the version in register,

I have unreg and reg the dlls,
but I still got the following components errors:

1. Microsoft ActiveX Data Object (Multi-dimensional)2.6 Library Component
'C:\Program Files\Common Files\System\ado\msadomd.dll'
could not be loaded

2 OLAP Manager Cube Browser
'C:\...\Microsoft Analysis Services\Bin\msmdcb.ocx' could
not be loaded

I'm using SQL2000 Sp2, Analysis Services Sp2, VB 6 SP5
Will it look like a Ananlysis Services problem?

Rick|||Well, your last choices are to try re-install 2.6 and/or installing 2.7. What is the date/time size on these files which are failing ?

Meta-Information about Stored-Procedures

Hi ,

I am looking for meta-information about the return recordset of a
stored-procedure. The procedure returns a resultset that contains columns of
more tables joined together. In all tables, I use, there is a
Record-Creation-Timestamp-Attribute. When joining two or more tables these
attribute-names appear in ther resultset but
i found no way to distinguish them.

I there a way to retrieve meta-information about the result-recordset of
such a stored-procedure?

here some details:

the tables
=======
CREATE TABLE [dbo].[Table1] (
[Table1ID] [int] IDENTITY (1, 1) NOT NULL ,
[FK_Tab2ID] [int] NULL ,
[CreatedAt] [datetime] NULL )

CREATE TABLE [dbo].[Table2] (
[Table2ID] [int] IDENTITY (1, 1) NOT NULL ,
[Description] [varchar] (35) NULL ,
[CreatedAt] [datetime] NULL)

the stored-procedure:
===============

CREATE PROCEDURE dbo.sp_Test_RetrieveData
@.ID int
AS
SET NOCOUNT ON

select * from table1 inner join table2 on (FK_Tab2ID = Table2ID)
where table1.ID = @.ID
GO

the resultset:
==========
Table1ID,FK_Tab2ID,CreatedAt,Table2ID,Description, CreatedAt
(the attribute CreatedAt appears twice.)

--= Posted via Newsfeeds.Com, Uncensored Usenet News =--
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
--== Over 100,000 Newsgroups - 19 Different Servers! =--KG wrote:
> CREATE PROCEDURE dbo.sp_Test_RetrieveData
> @.ID int
> AS
> SET NOCOUNT ON
> select * from table1 inner join table2 on (FK_Tab2ID = Table2ID)
> where table1.ID = @.ID
> GO
> the resultset:
> ==========
> Table1ID,FK_Tab2ID,CreatedAt,Table2ID,Description, CreatedAt
> (the attribute CreatedAt appears twice.)

Try this instead:

CREATE PROCEDURE dbo.sp_Test_RetrieveData
@.ID int
AS
SET NOCOUNT ON

select Table1ID, FK_Tab2ID, Table1.CreatedAt as Table1CreatedAt, Table2ID,
Description, Table2.CreatedAt as Table2CreatedAt
from table1 inner join table2 on (FK_Tab2ID = Table2ID)
where table1.ID = @.ID
GO

you will get resultset:

Table1ID, FK_Tab2ID, Table1CreatedAt, Table2ID, Description, Table2CreatedAt

--
Steve Troxell
Krell Software - Database Tools for MS SQL Server
http://www.krell-software.com|||KG (kg@.greenmail.ch) writes:
> I am looking for meta-information about the return recordset of a
> stored-procedure. The procedure returns a resultset that contains
> columns of more tables joined together. In all tables, I use, there is a
> Record-Creation-Timestamp-Attribute. When joining two or more tables
> these attribute-names appear in ther resultset but i found no way to
> distinguish them.
> I there a way to retrieve meta-information about the result-recordset of
> such a stored-procedure?

It would have helped if you have told in which environment you are working.
Are you using ADO?

In ADO, there are some properties on the Fields on object which may have
this information.

But it is kind of obscure programming to access this data. Better is to
use column aliases. Generally, SELECT * should not be used in production
code.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Metadata Inheritance

Hello,
I have written a mechanism which returns meta data back to a client for
different views. For example, one meta data item on a column is the Display
Name.
Now, so I don't have to keep attaching meta data to a particular column
every time I create a new view, I "inherit" the meta data from a parent view
or table by looking at all the same named columns in any views or tables
that the table depends (sysdepends) on.
The problem is, sometimes it make sense to change the name of a column in a
view from the underlying table (e.g. company table has a "name" column, but
in the view, you want to call it "company_name").
My question is, is there an easy way to determine where a view's column
really came from?
Thanks,
-PaulNever mind, I forgot to look at the depnumber column in sysdepends!
"Paul" <a@.b.com> wrote in message
news:eWN9knVCFHA.3940@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have written a mechanism which returns meta data back to a client for
> different views. For example, one meta data item on a column is the
> Display Name.
> Now, so I don't have to keep attaching meta data to a particular column
> every time I create a new view, I "inherit" the meta data from a parent
> view or table by looking at all the same named columns in any views or
> tables that the table depends (sysdepends) on.
> The problem is, sometimes it make sense to change the name of a column in
> a view from the underlying table (e.g. company table has a "name" column,
> but in the view, you want to call it "company_name").
> My question is, is there an easy way to determine where a view's column
> really came from?
> Thanks,
> -Paul
>|||Okay, now the hard question.
How do I correlate a column in the sysdepends table back to a column in the
resulting view's columns?
Thanks,
-Paul
"Paul" <a@.b.com> wrote in message
news:OBXyfyVCFHA.1836@.tk2msftngp13.phx.gbl...
> Never mind, I forgot to look at the depnumber column in sysdepends!
>
> "Paul" <a@.b.com> wrote in message
> news:eWN9knVCFHA.3940@.TK2MSFTNGP09.phx.gbl...
>|||On Wed, 2 Feb 2005 15:27:44 -0500, Paul wrote:

>Okay, now the hard question.
>How do I correlate a column in the sysdepends table back to a column in the
>resulting view's columns?
Hi Paul,
You don't.
You are probably thinking right now of fairly straight-forward views,
where such a mapping would be viable. But views can be fairly complex!
CREATE VIEW test
AS SELECT Col1 - Col2 AS A,
Col1 + Col2 AS B,
CASE WHEN EXISTS (SELECT *
FROM YZ
WHERE Foo = SomeTable.Bar)
THEN 1
ELSE 2
END AS C,
'Constant value' AS D,
Col3 + 1 AS E,
Col3 - 1 AS F
FROM SomeTable
How should each of the view's columns be correlated to each of the
underlying table's columns? And remember that this is also a fairly simple
example - I could come up with much more contrived views (some of my real
views for projects I do are much more contrived!)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)