a faster way to update a big table

hi
well I am working on a project that our database has 2, 8000 record table that they have triggers on the update of their feilds
our application must update these tables but it takes a long time to do this
I know there is something named bulk insert but I couldn't find sth similar to this command for update
so would you please help me to find a faster way to update these tables?
thanks for your attention
Best Regards
EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com
hi,
netman Mo wrote:
> hi
> well I am working on a project that our database has 2, 8000 record
> table that they have triggers on the update of their feilds our
> application must update these tables but it takes a long time to do
> this
> I know there is something named bulk insert but I couldn't find sth
> similar to this command for update
> so would you please help me to find a faster way to update these
> tables?
nope.. update syntax is not overloaded with bulk operators..
if the cause of your delay is dependent on the trigger fired by the update
statement, you should perhaps check it's code... or... if you are sure the
updates you are performing do not involve the trigger check, you can disable
it before executing the statements..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.bizhttp://italy.mvps.org
DbaMgr2k ver 0.20.0 - DbaMgr ver 0.64.0 and further SQL Tools
-- remove DMO to reply

A error in partition table ,could you tell me ?

1 HIS_HTTP_LOG a partition table
2 REL_HTTP_LOG not a partition table,the same structure of HIS_HTTP_LOG；
3 When HIS_HTTP_LOG doesn't exist any index
the following executed succeed

ALTER PARTITION SCHEME PS_HIS_HTTP_LOG NEXT USED [FG_03]
ALTER PARTITION FUNCTION PF_HIS_HTTP_LOG() SPLIT RANGE ('20070331 23:59:59.997')
ALTER TABLE TMP_HTTP_LOG SWITCH TO HIS_HTTP_LOG PARTITION 3

4 However when I added the index in HIS_HTTP_LOG and execute the step 3,It made error:
a) CREATE INDEX IDX_HIS_HTTP_LOG_001 ON HIS_HTTP_LOG(USERID)ON PS_HIS_HTTP_LOG (STARTIME)
b) ALTER PARTITION SCHEME PS_HIS_HTTP_LOG NEXT USED [FG_03]
ALTER PARTITION FUNCTION PF_HIS_HTTP_LOG() SPLIT RANGE ('20070331 23:59:59.997')
ALTER TABLE TMP_HTTP_LOG SWITCH TO HIS_HTTP_LOG PARTITION 3

========================= Error messages================================================
"ALTER TABLE SWITCH statement failed. There is no identical index in source table 'TMP_HTTP_LOG SWITCH ' for the index 'IDX_HIS_HTTP_LOG_001' in target table 'HIS_HTTP_LOG' ."

When I added index in REL_HTTP_LOG ,it gave me the same error message

Could you tell me how can I solve the problem !

The error says you need to create anidentical index on?TMP_HTTP_LOG SWITCH,?as?'IDX_HIS_HTTP_LOG_001' in target table 'HIS_HTTP_LOG' .?So?create?such?an?index?and?try?again.

When?use?ALTER?TABLE?SWITCH?to?transfer?schema, there is no physical data movement, only metadata change, the partitions and tables involved in the switching are required to be homogeneous. They must have the same columns of the same data type, name, order, and collation on the same filegroup.

A domain error occurred. from SQL statement, only when above 10

Attempting to run a query that is looping through cursor, anything over
choosing the TOP 10 from a table for the cursor results in a
"A domain error occurred."
error message? below 10 all runs fine. What is this message caused by?It usually happens when performing mathematical functions
and using values outside of acceptable ranges.
-Sue
On Thu, 20 Nov 2003 11:51:01 -0600, "Kory"
<kory@.removeme-mlsc.com> wrote:
>Attempting to run a query that is looping through cursor, anything over
>choosing the TOP 10 from a table for the cursor results in a
>"A domain error occurred."
>error message? below 10 all runs fine. What is this message caused by?
>

A Dimension Attribute with Double DataType converts a zero member to "(blank)"

In one of our SSAS 2005 sp2 cubes, we have a Dimension sourced from an Oracle table, with an Attribute that has a DataType of Double. When we process the Dimension and browse the Attribute, we see the following members:

All

-.01

-.002

.00000000001

.0000000001

Unknown

Notice how its blank where the '0' (zero) member should be. The underlying Oracle table does have '0' (zero) values for this column (you can see them when you explore the data in the DSV). To make things even more confusing,

the Unique_Name for this blank member is: [Dimension].[Attribute].&[0]

its Member_Value is: 0

its Member_Caption is: Null

its Member_Key is: 0

Has anyone seen this behaviour before? If so, how do we fix it?

Thank you.

Hi,

Have you set the NameColumn to look at the same field? And is it a datatype of WChar?

Matt|||

Hi Matt,

Thanks for the reply. Yes, setting the NameColumn to the field was one of the things i tried. The database field is NUMERIC(15,9), the KeyColumn is a datatype of Double and the NameColumn is a datatype of WChar. Even with the NameColumn set to the same field, I still get a blank where the 0 value should be.

-Robbie

A cursor performance related question

Hi All,
I have 800 K records need to be processed one by one in a large table daily
using cursor way to update the default columns. The rest of queries are only
read information from this table. The records in this table could be very
large later on.
My question is what's the best way to use cursor to duel with this table.
here is my options:
1.Use a BIG cursor to lock all the un-processed records and use a singal
connection from Query Analyzer
2. Try to break /subgroup them with flags and run the same procedure above
with muti-Query Analyzer Connections. Each connection only duel part of the
record set.
I only have one SQL server . The testing result is Option 1 has the best
performance.
Is SQL server not good at running query parallelly with muti-connections? or
I need to improve the SQL server hardware staff by adding more memory ?
Any expert can point me to the right way?
Many Thanks,
Stevenews.microsoft.com (stevenxiu@.yahoo.com) writes:
> I have 800 K records need to be processed one by one in a large table
> daily using cursor way to update the default columns. The rest of
> queries are only read information from this table. The records in this
> table could be very large later on.
> My question is what's the best way to use cursor to duel with this
> table. here is my options:
> 1.Use a BIG cursor to lock all the un-processed records and use a singal
> connection from Query Analyzer
> 2. Try to break /subgroup them with flags and run the same procedure
> above with muti-Query Analyzer Connections. Each connection only duel
> part of the record set.
> I only have one SQL server . The testing result is Option 1 has the best
> performance.
> Is SQL server not good at running query parallelly with
> muti-connections? or I need to improve the SQL server hardware staff by
> adding more memory ?
With only this abstract narrative it is impossible to say very much. One
possibility is that you have poor indexing, cause the multiple connections
to block each other.
To get any accurate response you would need to post:
o CREATE TABLE and CREATE INDEX statements for the table.
o The code for the two options you are using.
o Some background on what the code is actually doing.
However, there is fair chance that the answer is option 0: don't use a
cursor at all, but apply set-based logic. This usually improves performance
with magnitudes.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"news.microsoft.com" <stevenxiu@.yahoo.com> wrote in message
news:u9WHOCx8FHA.3044@.TK2MSFTNGP10.phx.gbl...
> I have 800 K records need to be processed one by one in a large table
> daily
> using cursor way to update the default columns.
Chances are that you don't need to do it "one by one" and that it will be
more efficient without a cursor. We won't know for sure unless you post a
better description of your problem. See:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
But based on a few decades with SQL, I have written only five cursors
and I know that I could have avoided three of them if I had the CASE
expression back in the old days.
The fact that you do not know the differences between records and rows
tells me your approach is probably not relational and that yoiur
mindset is still stuck in sequential file processing.|||You should avoid using cursors to perform updates. Set-based updates
perform better for a number of reasons:
(1) Set-based updates write to the transaction log more efficiently. Each
update incurs a certain amount of overhead in the transaction log to mark
the start and end of each write to each affected object in the database.
This includes not only writes to the heap or clustered index, but also
writes to each nonclustered index. In addition, if the same index page is
updated more than once during the thousands of individual updates, then that
page will be recorded in the transaction log once for each related update,
causing the log to grow faster than is necessary. Frequent disk allocations
can hugely affect performance. (This last shouldn't be a problem if you
pre-allocate log space.)
(2) With set-based updates, indexes can be updated en-mass--meaning fewer
costly page splits and fewer writes to each affected database object thus
reducing the frequency of disk head ss.
(3) Locking is more efficient. Set-based updates obtain locks on all of the
affected rows before beginning the write, and then release them as soon as
the changes have been committed. Thousands of individual updates requires
the server to go through the process of obtaining each individual exclusive
lock on each individual row. With set-based updates, locks are more likely
to be escalated when necessary, thereby reducing overhead.
(4) With set-based updates, the system is tasked with writing and writing
only. Thousands of individual updates usually means that several additional
reads are interspersed within the writes. This can cause a lot more disk
activity and in particular, a significant increase in costly disk ss.
If you must use a cursor (I'm not of the opinion that they are always bad;
however, they should only be used as a last resort.), then you should cache
the updates in a temp table or table variable and then flush them using
set-based updates. Inserting individual rows into a table with no indexes
(a heap) or appending to a table with only a clustered index performs pretty
well, and if you have enough memory, temp tables and table variables remain
for the most part in memory. (Writes to tempdb are eventually flushed out
to disk, provided the affected rows and objects still exist by the time the
system gets around to initiating the write.) By appending, I mean that
inserts occur in the same order as the clustered index key--that is, with an
ascending index, each row inserted has a key value that is greater than the
key value in any existing row in the table.
"news.microsoft.com" <stevenxiu@.yahoo.com> wrote in message
news:u9WHOCx8FHA.3044@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I have 800 K records need to be processed one by one in a large table
> daily
> using cursor way to update the default columns. The rest of queries are
> only
> read information from this table. The records in this table could be very
> large later on.
> My question is what's the best way to use cursor to duel with this table.
> here is my options:
> 1.Use a BIG cursor to lock all the un-processed records and use a singal
> connection from Query Analyzer
> 2. Try to break /subgroup them with flags and run the same procedure above
> with muti-Query Analyzer Connections. Each connection only duel part of
> the
> record set.
> I only have one SQL server . The testing result is Option 1 has the best
> performance.
> Is SQL server not good at running query parallelly with muti-connections?
> or
> I need to improve the SQL server hardware staff by adding more memory ?
> Any expert can point me to the right way?
> Many Thanks,
> Steve
>sql

A curious error message, local temp vs. global temp tables?!?!?

Hi all,

Looking at BOL for temp tables help, I discover that a local temp table (I want to only have life within my stored proc) SHOULD be visible to all (child) stored procs called by the papa stored proc.

However, the following code works just peachy when I use a GLOBAL temp table (i.e., ##MyTempTbl) but fails when I use a local temp table (i.e., #MyTempTable). Through trial and error, and careful weeding efforts, I know that the error I get on the local version is coming from the xp_sendmail call. The error I get is: ODBC error 208 (42S02) Invalid object name '#MyTempTbl'.

Here is the code that works:SET NOCOUNT ON

CREATE TABLE ##MyTempTbl (SeqNo int identity, MyWords varchar(1000))
INSERT ##MyTempTbl values ('Put your long message here.')
INSERT ##MyTempTbl values ('Put your second long message here.')
INSERT ##MyTempTbl values ('put your really, really LONG message (yeah, every guy says his message is the longest...whatever!')
DECLARE @.cmd varchar(256)
DECLARE @.LargestEventSize int
DECLARE @.Width int, @.Msg varchar(128)
SELECT @.LargestEventSize = Max(Len(MyWords))
FROM ##MyTempTbl

SET @.cmd = 'SELECT Cast(MyWords AS varchar(' +
CONVERT(varchar(5), @.LargestEventSize) +
')) FROM ##MyTempTbl order by SeqNo'
SET @.Width = @.LargestEventSize + 1
SET @.Msg = 'Here is the junk you asked about' + CHAR(13) + '---------'
EXECUTE Master.dbo.xp_sendmail
'YoMama@.WhoKnows.com',
@.query = @.cmd,
@.no_header= 'TRUE',
@.width = @.Width,
@.dbuse = 'MyDB',
@.subject='none of your darn business',
@.message= @.Msg
DROP TABLE ##MyTempTbl

The only thing I change to make it fail is the table name, change it from ##MyTempTbl to #MyTempTbl, and it dashes the email hopes of the stored procedure upon the jagged rocks of electronic despair.

Any insight anyone? Or is BOL just full of...well..."stuff"?I would still like to hear if anyone knows anything different, but while looking into Des' sendmail problem, I found this lil' tidbit in BOL for xp_sendmail If query is specified, xp_sendmail logs in to SQL Server as a client and executes the specified query. SQL Mail makes a separate connection to SQL Server; it does not share the same connection as the original client connection issuing xp_sendmail. I suspect the "separate connection to SQL Server" is the issue here?!?!?! Hmmmm...perhaps the local temp table can be seen by child processes called by the proc that creates the table EXCEPT in xp_sendmail, etc.|||You hit the problem right on the head... xp_sendmail does execute the query in a different context, meaning that it can't see local variables, settings, or temp tables. You can think of it almost as though xp_sendmail were cranking up OSQL.EXE to execute your query (that isn't what actually happens, but it is logically pretty close).

-PatP

A counting question

I'm hoping that someone has the answer. I guess I may be missing the obvious but I'm struggling to do this.

Simplified, I have two tables:- One table contains a list of Customers and the other table a list of Products that they have purchased. They are linked by a customer reference no, a one-to-many relationship.

What I want to be able to do is count have how many Customers have purchased both Product A and Product B.

Can anyone tell me the SQL statement that would return the answer as a single number.

Thanks in expectation

Ventus:

I am going to assume that the customer records are unique by customer. I am going to use exists clause because it is likely that a customer
has made multiple purchases of a particular product:

select count(*)
from CUSTOMER a
where exists (select 0 from product b where a.cust_id = b.cust_id and b.product = 'Product A')
and exists (select 0 from product c where a.cust_id = c.cust_id and c.product = 'Product B')

Dave

|||

Dave thanks. Still struggling and think the suggested solution may be too complex for what I want to do.

Imagine two tables, Customer and Product,

Customer has the field CUST_ID and Product the fields CUST_ID and PRODUCT_BOUGHT (Customer and Product are linked by a one-to-many relationship on Customer.CUST_ID = Product.CUST_ID)

The contents of Customer.CUST_ID are: 1; 2; 3; 4;

The contents of Product.CUST_ID, Product.PRODUCT_BOUGHT are: 1, A; 1, B, 2, A; 3, A; 3, B; 4, A;

The SQL statement I need counts the number of Customer.CUST_ID who bought both product A and B.

So in this case the answer returned would be 2.

Sorry if I'm not explaining myself all that well, or formatting the question in the correct way.

Thanks again for taking the time to help

|||

Here is generic way to do this query (assumes that CUST_ID, PRODUCT_BOUGHT is unique):

select p.CUST_ID

from Product as p
where p.PRODUCT_BOUGHT IN ( <list of products> )

group by p.CUST_ID

having count(p.PRODUCT_BOUGHT) = <number of products>

For your example, to get customers who bought only A & B together you would do:

select p.CUST_ID

from Product as p
where p.PRODUCT_BOUGHT IN ( 'A', 'B' )

group by p.CUST_ID

having count(p.PRODUCT_BOUGHT) = 2

There are other questions you can formulate and answer using the same query. To make it generic, you can pass the products as a list and do:

select p.CUST_ID

from Product as p
where p.PRODUCT_BOUGHT IN ( select s.value from SplitStr('A, B') as s )

group by p.CUST_ID

having count(p.PRODUCT_BOUGHT) = (select count(*) from SplitStr('A, B') as s)

The COUNT(DISTINCT) can be made to COUNT(*) >= @.cnt if you want to get customers who bought A & B only or A & B with other products. Ex:

-- Obtain customers who bought A & B only or A & B with some other product(s)

select p.CUST_ID

from Product as p
where p.PRODUCT_BOUGHT IN ( select s.value from SplitStr('A, B') as s )

group by p.CUST_ID

having count(p.PRODUCT_BOUGHT) >= (select count(*) from SplitStr('A, B') as s)

|||

Umachandar

Thanks, that was just the guidance I needed.

I've got my solution sorted now

Most helpful

A Count of Counts?

I'm using Oracle. I have an inventory table with each record being a different inventory record, and each record includes the location within my warehouse. I need to get a count of locations that have 5 or more inventory records pointing to it.

Any pointers greatly appreciated!

Thanks,
jdkselect count(*)
from
where
group by xxx having count(*) > 5|||Originally posted by N-ary
select count(*)
from
where
group by xxx having count(*) > 5

Thanks!! I'm still validating results, but this appears to have been just what I needed.

jdk

A Challenging Query

Hi EveryBody,
Here is a very interesting SQL which I failed to solve.
I have two tables called Main and Notes.Table notes has id field which is
primary key of the Main table and foreign key in notes table. Notes table has
an identity column called NotesId.
What I have to do is that I have to show all data of main table but they
will be ordered depending upon a particular value of the note field in the
notes table. What I mean is : say there is ID 100,101,102,103 existing in the
main table. They may have several entries in the notes table and some of
those entries containing say "Desired match" in their note field.
What I want : in time of selection those entries who have "Desired Match" in
their notes field they will be coming first in their chronological order.
But their are some constraints : you cannot use any group by or distinct
clause in the query. and the resultant data cannot have any duplicate rows
Here is what I tried :
select top 100 percent main.*,notes.note,notes.date
from main left join notes on main.id=notes.id
order by convert(
numeric,
case
when notes.note like('Desired%') then '100000'
else
'500'
end
) desc,notes.date asc
But problem is that this result set contains duplicate data
Any new or better Idea.
Thanks
Kaushik
On Wed, 23 Mar 2005 22:23:01 -0800, Kaushik wrote:

>Hi EveryBody,
>Here is a very interesting SQL which I failed to solve.
(snip)
Hi Kaushik,
I love solving SQL challenges. But I'm not quite as good at trying to
understand verbose descriptions. I'll refer you to a website that
explains what information you should include in a post in order for us
to help you: www.aspfaq.com/5006. If you follow the guidelines given
there, I'll probably be able to help you.

>But their are some constraints : you cannot use any group by or distinct
>clause in the query. and the resultant data cannot have any duplicate rows
I understand the need to eliminate duplicate rows, but the constraints
to not use GROUP BY or DISTINCT makes no sense to me. Can you explain
the reason for this restriction? Because I really don't understand why
any SQL coder would voluntarily part with part of the tools he needs to
do his job.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

a bulk insert question

I was given a text file to use to insert bulk-ly into a table.

The file is made up in a way that at the end of each line it has a very very long line of spaces (i.e a string). The last column in the table is supposed to accepted an nvarchar no bigger than 3. I know there's a possibility to increase the size of the nvarchar, but I want to avoid putting the string of spaces in the table.

How can I over come the problem with the file? obviously deleting each row of spaces is too much trouble for a file that has over 40000 rows...

I hope you can help me.

hi,
you can insert bulk insert the it first to a temporary table with the last column has a bigger size then have a SP that cleanse the data and insert it to the destination table.

- clintz|||

If it is just leading blanks then you need not to worry..

Instead of BULK INSERT use the OPENROWSET..

It will automatically remove the leading blanks..

SELECT

*

FROM

OPENROWSET

(

'MSDASQL',

'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=c:\','SELECT * FROM Data.csv'

);

|||

I often consider it a good practice to first load data to a 'staging' table, a table with no constraints, indexes, etc.

Then after the data has been loaded, it is easy to have other procedures that 'cleanse' or conform the data before loading into the destination table. I can identify exceptions and set them aside for further inspection, I can 'transform' data into whatever form is required by the database, for example, 0 and 1 may be stored as 'M' and 'F' in the database (or vice-versa.)

I never assume that imported data will always meet the requirements of the database.

A Bug With Running Total Query?

Using SQL Server 2005
Query Result From 1 Table with 3 Cols:
Date Amount RunningTotal
6/1/2005 730.0000 730.0000
7/1/2005 415.0000 830.0000
8/1/2005 415.0000 1245.0000
9/1/2005 415.0000 1660.0000
10/1/2005 415.0000 2075.0000
11/1/2005 415.0000 2490.0000
12/1/2005 415.0000 2905.0000
1/1/2006 415.0000 3320.0000
2/1/2006 415.0000 3735.0000
3/1/2006 415.0000 4150.0000
4/1/2006 415.0000 4565.0000
5/1/2006 415.0000 4980.0000
Query:
SELECT Table1.AmountDate, Table1.Amount, SUM(Table1.Amount) AS
RunningTotal
FROM AmountTable AS Table1 INNER JOIN
AmountTable AS Table2 ON Table1.AmountDate >=
Table2.AmountDate
GROUP BY Table1.AmountDate, Table1.Amount
ORDER BY Table1.AmountDate
The problem is with the 7/1 date sum. It only added 100 and not 415
Does anybody see a problem I did or is this something I should post to MSDN
Product FeedBack?
The query was a bit more complex when I unioned 2 tables and the amount was
not coming out right and I broke it down very simply and found this to be a
n
issue.
On a side note relating to the union relation and doing a running total, I
only had 1 Amount in the other table for now and for some reason, that wasn'
t
summing with the rest of the set.
SELECT AmountTable1.AmountDate, SUM(AmountTable1.AmountAmount)
FROM (SELECT Table1Amount.AmountDate, Table1Amount.AmountAmount
FROM Table1Amount
INNER JOIN
Table1 ON Table1Amount.Table1ID = Table1.Table1ID
UNION
SELECT Table2Amount.AmountDate, Table2Amount.AmountAmount
FROM Table2Amount INNER JOIN
Table1 AS Table1_1 ON Table2Amount.Table2ID =
Table1_1.Table2ID
WHERE (Table2Amount.Table2ID = '1')) AS AmountTable1
CROSS JOIN
(SELECT Table1Amount.AmountDate, Table1Amount.AmountAmount
FROM Table1Amount INNER JOIN
Table1 ON Table1Amount.Table1ID = Table1.Table1ID
UNION
SELECT Table2Amount.AmountDate, Table2Amount.AmountAmount
FROM Table2Amount INNER JOIN
Table1 AS Table1_1 ON Table2Amount.Table2ID =
Table1_1.Table2ID
WHERE (Table2Amount.Table2ID = '1')) AS AmountTable2
WHERE (AmountTable1.AmountDate >= AmountTable2.AmountDate)
GROUP BY AmountTable1.AmountDate, AmountTable1.AmountAmount
ORDER BY AmountTable1.AmountDate
Produces:
Date Amount RunningTotal
6/1/2005 730.0000 730.0000
6/3/2005 10.0000 20.0000*****From Table1
7/1/2005 415.0000 1245.0000
8/1/2005 415.0000 1660.0000
9/1/2005 415.0000 2075.0000
10/1/2005 415.0000 2490.0000
11/1/2005 415.0000 2905.0000
12/1/2005 415.0000 3320.0000
1/1/2006 415.0000 3735.0000
2/1/2006 415.0000 4150.0000
3/1/2006 415.0000 4565.0000
4/1/2006 415.0000 4980.0000
5/1/2006 415.0000 5395.0000
6/1/2006 415.0000 5810.0000
So that 6/3 Amount from Table1 is excluded from summing with the rest of the
set.
I thought it would have been included with the Union.
Any input is welcome,
NathanHi
This should be posted to the beta newsgroups along with version number, DDL
and sample data so that the problem can be recreated.
http://communities.microsoft.com/ne...onalserver.tsql
John
"Nathan" <Nathan@.discussions.microsoft.com> wrote in message
news:C771A09A-69DA-49D0-B71C-5C541E15EC51@.microsoft.com...
> Using SQL Server 2005
> Query Result From 1 Table with 3 Cols:
> Date Amount RunningTotal
> 6/1/2005 730.0000 730.0000
> 7/1/2005 415.0000 830.0000
> 8/1/2005 415.0000 1245.0000
> 9/1/2005 415.0000 1660.0000
> 10/1/2005 415.0000 2075.0000
> 11/1/2005 415.0000 2490.0000
> 12/1/2005 415.0000 2905.0000
> 1/1/2006 415.0000 3320.0000
> 2/1/2006 415.0000 3735.0000
> 3/1/2006 415.0000 4150.0000
> 4/1/2006 415.0000 4565.0000
> 5/1/2006 415.0000 4980.0000
> Query:
> SELECT Table1.AmountDate, Table1.Amount, SUM(Table1.Amount) AS
> RunningTotal
> FROM AmountTable AS Table1 INNER JOIN
> AmountTable AS Table2 ON Table1.AmountDate >=
> Table2.AmountDate
> GROUP BY Table1.AmountDate, Table1.Amount
> ORDER BY Table1.AmountDate
> The problem is with the 7/1 date sum. It only added 100 and not 415
> Does anybody see a problem I did or is this something I should post to
> MSDN
> Product FeedBack?
> The query was a bit more complex when I unioned 2 tables and the amount
> was
> not coming out right and I broke it down very simply and found this to be
> an
> issue.
> On a side note relating to the union relation and doing a running total, I
> only had 1 Amount in the other table for now and for some reason, that
> wasn't
> summing with the rest of the set.
> SELECT AmountTable1.AmountDate, SUM(AmountTable1.AmountAmount)
> FROM (SELECT Table1Amount.AmountDate, Table1Amount.AmountAmount
> FROM Table1Amount
> INNER JOIN
> Table1 ON Table1Amount.Table1ID = Table1.Table1ID
> UNION
> SELECT Table2Amount.AmountDate, Table2Amount.AmountAmount
> FROM Table2Amount INNER JOIN
> Table1 AS Table1_1 ON Table2Amount.Table2ID =
> Table1_1.Table2ID
> WHERE (Table2Amount.Table2ID = '1')) AS AmountTable1
> CROSS JOIN
> (SELECT Table1Amount.AmountDate, Table1Amount.AmountAmount
> FROM Table1Amount INNER JOIN
> Table1 ON Table1Amount.Table1ID = Table1.Table1ID
> UNION
> SELECT Table2Amount.AmountDate,
> Table2Amount.AmountAmount
> FROM Table2Amount INNER JOIN
> Table1 AS Table1_1 ON Table2Amount.Table2ID =
> Table1_1.Table2ID
> WHERE (Table2Amount.Table2ID = '1')) AS AmountTable2
> WHERE (AmountTable1.AmountDate >= AmountTable2.AmountDate)
> GROUP BY AmountTable1.AmountDate, AmountTable1.AmountAmount
> ORDER BY AmountTable1.AmountDate
> Produces:
> Date Amount RunningTotal
> 6/1/2005 730.0000 730.0000
> 6/3/2005 10.0000 20.0000*****From Table1
> 7/1/2005 415.0000 1245.0000
> 8/1/2005 415.0000 1660.0000
> 9/1/2005 415.0000 2075.0000
> 10/1/2005 415.0000 2490.0000
> 11/1/2005 415.0000 2905.0000
> 12/1/2005 415.0000 3320.0000
> 1/1/2006 415.0000 3735.0000
> 2/1/2006 415.0000 4150.0000
> 3/1/2006 415.0000 4565.0000
> 4/1/2006 415.0000 4980.0000
> 5/1/2006 415.0000 5395.0000
> 6/1/2006 415.0000 5810.0000
> So that 6/3 Amount from Table1 is excluded from summing with the rest of
> the
> set.
> I thought it would have been included with the Union.
> Any input is welcome,
> Nathan|||
Nathan wrote:

>Using SQL Server 2005
>Query Result From 1 Table with 3 Cols:
>Date Amount RunningTotal
>6/1/2005 730.0000 730.0000
>7/1/2005 415.0000 830.0000
>8/1/2005 415.0000 1245.0000
>9/1/2005 415.0000 1660.0000
>10/1/2005 415.0000 2075.0000
>11/1/2005 415.0000 2490.0000
>12/1/2005 415.0000 2905.0000
>1/1/2006 415.0000 3320.0000
>2/1/2006 415.0000 3735.0000
>3/1/2006 415.0000 4150.0000
>4/1/2006 415.0000 4565.0000
>5/1/2006 415.0000 4980.0000
>Query:
>SELECT Table1.AmountDate, Table1.Amount, SUM(Table1.Amount) AS
>RunningTotal
>FROM AmountTable AS Table1 INNER JOIN
> AmountTable AS Table2 ON Table1.AmountDate >=
>Table2.AmountDate
>GROUP BY Table1.AmountDate, Table1.Amount
>ORDER BY Table1.AmountDate
>
Nathan,
The results are correct for this query. You are grouping by and summing
the same column: Table1.Amount. Table2 only determines how many times
each Table1 row is repeated, so SUM(Table1.Amount) is just a multiple of
Table1.Amount:
Table1.Amount*(number of rows in the table with earlier or the same date).
Note that your "running sums" are not that at all. They are multiples:
730*1 = 730
415*2 = 830
415*3 = 1245
415*4 = 1660
...
415*12 = 4980
If you want running sums, you will need SUM(Table2.Amount).
Steve Kass
Drew University

>The problem is with the 7/1 date sum. It only added 100 and not 415
>Does anybody see a problem I did or is this something I should post to MSDN
>Product FeedBack?
>The query was a bit more complex when I unioned 2 tables and the amount was
>not coming out right and I broke it down very simply and found this to be
an
>issue.
>On a side note relating to the union relation and doing a running total, I
>only had 1 Amount in the other table for now and for some reason, that wasn
't
>summing with the rest of the set.
>SELECT AmountTable1.AmountDate, SUM(AmountTable1.AmountAmount)
>FROM (SELECT Table1Amount.AmountDate, Table1Amount.AmountAmount
> FROM Table1Amount
> INNER JOIN
> Table1 ON Table1Amount.Table1ID = Table1.Table1ID
> UNION
> SELECT Table2Amount.AmountDate, Table2Amount.AmountAmount
> FROM Table2Amount INNER JOIN
> Table1 AS Table1_1 ON Table2Amount.Table2ID =
>Table1_1.Table2ID
> WHERE (Table2Amount.Table2ID = '1')) AS AmountTable1
> CROSS JOIN
> (SELECT Table1Amount.AmountDate, Table1Amount.AmountAmount
> FROM Table1Amount INNER JOIN
> Table1 ON Table1Amount.Table1ID = Table1.Table1ID
> UNION
> SELECT Table2Amount.AmountDate, Table2Amount.AmountAmou
nt
> FROM Table2Amount INNER JOIN
> Table1 AS Table1_1 ON Table2Amount.Table2ID =
>Table1_1.Table2ID
> WHERE (Table2Amount.Table2ID = '1')) AS AmountTable2
>WHERE (AmountTable1.AmountDate >= AmountTable2.AmountDate)
>GROUP BY AmountTable1.AmountDate, AmountTable1.AmountAmount
>ORDER BY AmountTable1.AmountDate
>Produces:
>Date Amount RunningTotal
>6/1/2005 730.0000 730.0000
>6/3/2005 10.0000 20.0000*****From Table1
>7/1/2005 415.0000 1245.0000
>8/1/2005 415.0000 1660.0000
>9/1/2005 415.0000 2075.0000
>10/1/2005 415.0000 2490.0000
>11/1/2005 415.0000 2905.0000
>12/1/2005 415.0000 3320.0000
>1/1/2006 415.0000 3735.0000
>2/1/2006 415.0000 4150.0000
>3/1/2006 415.0000 4565.0000
>4/1/2006 415.0000 4980.0000
>5/1/2006 415.0000 5395.0000
>6/1/2006 415.0000 5810.0000
>So that 6/3 Amount from Table1 is excluded from summing with the rest of th
e
>set.
>I thought it would have been included with the Union.
>Any input is welcome,
>Nathan
>|||AAAAHHHHHH!!!!
I can't believe I didn't catch that one. I was working on the original issue
for so long that ended up going blind.
Kudos for the good catch Steve
Nathan
"Steve Kass" wrote:

>
> Nathan wrote:
>
> Nathan,
> The results are correct for this query. You are grouping by and summing
> the same column: Table1.Amount. Table2 only determines how many times
> each Table1 row is repeated, so SUM(Table1.Amount) is just a multiple of
> Table1.Amount:
> Table1.Amount*(number of rows in the table with earlier or the same date).
> Note that your "running sums" are not that at all. They are multiples:
> 730*1 = 730
> 415*2 = 830
> 415*3 = 1245
> 415*4 = 1660
> ...
> 415*12 = 4980
>
> If you want running sums, you will need SUM(Table2.Amount).
> Steve Kass
> Drew University
>
>

a beginner question

if i have a table like this:
=========
id units tag
1 2 x
1 3 x
1 10 y
2 5 x
2 5 x
2 2 x
3 10 y
3 3 x
3 1 y
=========

basiclly the goal is to sum up all the units which has same tag for each id
so that the output will be some thing like:
============
id units tag
1 5 x
1 10 y
2 12 x
3 11 y
3 3 x
============
i am supose to use simple queries to do this job, but i just have no idea how to
can any one give me an idea?

thanks

Quote:

Originally Posted by temptgan

if i have a table like this:
=========
id units tag
1 2 x
1 3 x
1 10 y
2 5 x
2 5 x
2 2 x
3 10 y
3 3 x
3 1 y
=========

basiclly the goal is to sum up all the units which has same tag for each id
so that the output will be some thing like:
============
id units tag
1 5 x
1 10 y
2 12 x
3 11 y
3 3 x
============
i am supose to use simple queries to do this job, but i just have no idea how to
can any one give me an idea?

thanks

select id,sum(units) as units,tag from table_above
group by id, tags|||

Quote:

Originally Posted by temptgan

if i have a table like this:
=========
id units tag
1 2 x
1 3 x
1 10 y
2 5 x
2 5 x
2 2 x
3 10 y
3 3 x
3 1 y
=========

basiclly the goal is to sum up all the units which has same tag for each id
so that the output will be some thing like:
============
id units tag
1 5 x
1 10 y
2 12 x
3 11 y
3 3 x
============
i am supose to use simple queries to do this job, but i just have no idea how to
can any one give me an idea?

thanks

select id,sum(units) as units,tag from table_above
group by id, tag

A basic sql query problem

Suppose we get two following tables:

Table A:
[USER_ID] [varchar] (11) NOT NULL ,
[COURSE_ID] [varchar] (11) NOT NULL ,

Table B:

[COURSE_ID] [varchar] (11) NOT NULL ,
[COURSE_NAME] [varchar] (50) NOT NULL ,
[COURSE_NO] [varchar] (15) NULL ,
[BEGIN_DATE] [datetime] NULL ,
[END_DATE] [datetime] NULL ,
[CREATER] [varchar] (11) NOT NULL ,

and during the execution of my program, I can get the current use's id (USER_ID), sayU0001.

How can I retrieve the result set containing[COURSE_NAME],[COURSE_ID],but the current user's id (U0001) have Not been assigned in Table A.

Thanks in advance.

Ricky.

if u know what the course will be taken by the userid U0001 then just query from table B and insert the data of user in the table A.say u want to assign the user U0001 to "Eng01" query with select course_id,course_name from tableB where course_name ='Eng01' and then assign this course_id and user_id to tableA...it is not clear to me that what u wants to do ?|||

Are you talking about joining between two table?

If yes, try SELECT TableA.User_ID, TableB.CourseID, TableB.Course_Name FROM TableB LEFT OUTER JOIN TableA ON TableA.CourseID = TableB.CourseID

I haven't test it yet, but try searching for "LEFT OUTER JOIN" if it doesn't work.

Hope that help.

|||

Sorry for my unclear description of my question first.

And nr2ea, you provide me helpful hint.

I have one more question: is there any difference between LEFT JOIN and LEFT OUTER JOIN?

Ricky.

A basic question?

Hello all!,
This is probably pretty simple. I think I'm trying to hard on this, and
can't see the logic. If I have a table(products) with a productID, prodname.
And a another table called user, with UserID and name. And I add a UserID
column to my Product table. How can link the userID to the product ID. So i
f
user1 sells, apples and grapes, apple and grapes will have UserID1 matched t
o
it, just like userid2 sells Oranges, and so on. Hopethis makes sense!
Thanks!!
RudyHi Ruby
This looks like a database design issue. To reduce the redundancy,
to can create a new Table UserProducts and add UserID and ProdID to it
Ex:
User ID | Name
1 | User-1
2 | User-2
3 | User-3
ProdID | Name
1 | Apples
2 | Grapes
3 | Oranges
UserID | ProdID
1 | 1
1 | 2
3 | 3
Hope this answers the question
thanks and regards
Chandra
"Rudy" wrote:

> Hello all!,
> This is probably pretty simple. I think I'm trying to hard on this, and
> can't see the logic. If I have a table(products) with a productID, prodnam
e.
> And a another table called user, with UserID and name. And I add a UserID
> column to my Product table. How can link the userID to the product ID. So
if
> user1 sells, apples and grapes, apple and grapes will have UserID1 matched
to
> it, just like userid2 sells Oranges, and so on. Hopethis makes sense!
> Thanks!!
> Rudy|||Thank you! That does make sence. I knew I was making it more difficult tha
n
it had to be.
Thank You!!
Rudy
"Chandra" wrote:
> Hi Ruby
> This looks like a database design issue. To reduce the redundancy,
> to can create a new Table UserProducts and add UserID and ProdID to it
> Ex:
> User ID | Name
> 1 | User-1
> 2 | User-2
> 3 | User-3
> ProdID | Name
> 1 | Apples
> 2 | Grapes
> 3 | Oranges
> UserID | ProdID
> 1 | 1
> 1 | 2
> 3 | 3
> Hope this answers the question
> thanks and regards
> Chandra
>
>
> "Rudy" wrote:
>

A basic question: Removing duplicate results from Max function

Hi,

Say I have a table Job with columns name, date, salary . I want to get
the name ,date and salary for the date when that person earned maximum
salary. I am using something like

SELECT X.name,X.date,X.salary
FROM job X
WHERE X.salary IN
(SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);

The problem is ; if a person earns maximum salary on two dates, both of
the dates are printed. I just want to get any one of those two rows.
I tried

SELECT X.name,Min(X.date),X.salary
FROM job X
WHERE X.salary IN
(SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);

but it gives error.
Can anybody please suggest a solution?

Regards,
Aamir(aamircheema@.gmail.com) writes:
> Say I have a table Job with columns name, date, salary . I want to get
> the name ,date and salary for the date when that person earned maximum
> salary. I am using something like
>
> SELECT X.name,X.date,X.salary
> FROM job X
> WHERE X.salary IN
> (SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);
>
> The problem is ; if a person earns maximum salary on two dates, both of
> the dates are printed. I just want to get any one of those two rows.
> I tried
> SELECT X.name,Min(X.date),X.salary
> FROM job X
> WHERE X.salary IN
> (SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);
> but it gives error.

SELECT a.name, a.date, a.salary
FROM tbl a
JOIN (SELECT b.name, date = MAX(b.date)
FROM tbl b
JOIN (SELECT name, salary = MAX(salary)
FROM tbl
GROUP BY name) c ON c.name = b.name
AND c.salary = b.salary
GROUP BY a1.name) b ON a.name = b.name
AND a.date = b.date

This presumes that (name, date) is unique, and a person does not have
two salaries the same day.

The inner selects are derived tables - sort of virtual temp tables within
the query. A very powerful tool to write complex queries. A derived table
is independent of the outer query, and this why the alias b can be reused.
Note that they are not necessarily computed in whole - the optimizer often
recast computation order for a very very effceient query plan.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||aamircheema@.gmail.com wrote:
> Hi,
> Say I have a table Job with columns name, date, salary . I want to get
> the name ,date and salary for the date when that person earned maximum
> salary. I am using something like
>
> SELECT X.name,X.date,X.salary
> FROM job X
> WHERE X.salary IN
> (SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);
>
> The problem is ; if a person earns maximum salary on two dates, both of
> the dates are printed. I just want to get any one of those two rows.
> I tried
> SELECT X.name,Min(X.date),X.salary
> FROM job X
> WHERE X.salary IN
> (SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);
> but it gives error.
> Can anybody please suggest a solution?
> Regards,
> Aamir

In SQL Server 2005:

WITH j AS
(SELECT name, date, salary,
RANK() OVER (PARTITION BY name ORDER BY salary DESC, date DESC) rnk
FROM job)
SELECT name, date, salary
FROM j
WHERE rnk = 1 ;

That assumes the combination of (name,salary,date) is unique. If it
isn't then just add other columns to the ORDER BY specification to make
a key.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||On 10 Jun 2006 07:48:38 -0700, aamircheema@.gmail.com wrote:

(snip)
>I tried
>SELECT X.name,Min(X.date),X.salary
>FROM job X
>WHERE X.salary IN
> (SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);
>but it gives error.
>Can anybody please suggest a solution?

Hi Aamir,

Here's a third suggestion:

SELECT X.name,Min(X.date),X.salary
FROM job X
WHERE X.salary IN
(SELECT MAX(Y.salary) FROM job Y where Y.name= X.name)
GROUP BY X.name, X.salary;

(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)

--
Hugo Kornelis, SQL Server MVP|||
Thanks Everybody. That helped a lot

A baffeling problem between a text box and database table.

I have a web form with a text box that when submited gets put in a field in a table in an SQL database. Only 50 characters were being passed to the table. I gave the text box a max size of 200 characters and also found that the field in my table was set at 50 char. I had my SA set the field to 200 char. I rebuilt the solution and yet I am still only getting 50 charecters in the table. Any ideas?Show how you are setting the field in SQL Server.|||This is what I am doing to update my database.
'comments' is the field in question.
_______________________________________

Private Sub Update_Dataset()
Dim dr As DataRow = DataSet11.Tables("Results").NewRow()
dr("Area") = Area.SelectedItem
dr("Q1") = Q1.SelectedIndex + 1
dr("Q2") = Q2.SelectedIndex + 1
dr("Q3") = Q3.SelectedIndex + 1
dr("Q4") = Q4.SelectedIndex + 1
dr("Q5") = Q5.SelectedIndex + 1
dr("Q6") = Q6.SelectedIndex + 1
dr("Q7") = Q7.SelectedIndex + 1
dr("Q8") = Q8.SelectedIndex + 1
dr("Q9") = Q9.SelectedIndex + 1
dr("Comments") = Comments.Text
dr("ID") = 0
DataSet11.Tables("Results").Rows.Add(dr)
OleDbDataAdapter1.Update(DataSet11, "Results")

End Sub

__________________________________________________________

9 million rows in msrepl_commands? I don't even have that many records!

Does more than one msrepl_commands record exist for each record updated?
How can I have so many rows in this table? Does anyone know how to
translate the command fields so I can see what the commands are... with so
many records sp_browsereplcmds isn't even responding.
Thanks
the msrepl_command will also contain the sync commands which are necessary
to deploy your snapshot on the subscriber. If the particular command is very
large it may wrap over one row.
However what probably has caused so many rows to fill up this table is the
fact that all transactions that occur on the publisher are converted into
singletons, so a single insert/update/delete statement that affects 100 rows
on the publisher will be converted into 100 insert/update/delete statements
on the publisher.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Ray Price" <ray.price@.gartner.com> wrote in message
news:OqL%23Fl$UEHA.3944@.tk2msftngp13.phx.gbl...
> Does more than one msrepl_commands record exist for each record updated?
> How can I have so many rows in this table? Does anyone know how to
> translate the command fields so I can see what the commands are... with so
> many records sp_browsereplcmds isn't even responding.
> Thanks
>

800a0cb3 Error in DTS Package

I've been working on a DTS Package that runs every 10 minutes to check for new records to a table and sends out notification emails accordingly. After the email is sent, the new record is updated with a flag so it will not be pulled and emailed again.

This package worked fine for a week (and is currently working - exact same code - in another SQL Server).

As of last night, this process started throwing the following error:

Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:Error Code: 0
Error Source= ADODB.Recordset
Error Description: Current Recordset does not support updating. This may be a limitation of the provider,
or of the selected locktype.

Error on Line 110
(ADODB.Recordset (800a0cb3): Current Recordset does not support updating. This may be a limitation
of the provider, or of the selected locktype.)
Step Error code: 800403FE
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100

Thinking I may have screwed something up, I moved the functioning code from the other SQL Server back to the non-functioning SQL Server, and the problem persists. I stopped and restarted the SQL Server (but have not yet rebooted the server it's on), also with no results.

The package is a single ActiveX object written in VB to open up a recordset of unprocessed records (KeySet and Lock Optomistic), send out notification emails, flag the files, then close. The error occurs at the updating of the flag.

What is even more strange, is that this is happening on our development SQL server. Our production SQL server seems unaffected. I originally developed and tested this code on the dev box, and when I was satisfied, I moved it to production for testing. Then last night the dev server stopped functioning, but the production server shows no sign of problems.

I was curious if anyone else has had an experience with a DTS package throwing a similar error.

Any ideas?I took the ActiveX code and copied it to VB and ran it.. when it's pointing to the server in question, the error occurs... when I change the server name to point to the production server, the same code functions properly... amazing....|||I temporarily managed to resolve the problem by rebuilding the packages from scratch. I do not know if this fixed it, or if it was just coincidence that I did this at the same time something else happened, but it began working again.

Unfortunately, as of this afternoon, the production and staging environments redeveloped the problem and my "solution" did not fix it.

Thinking my transaction log may be filled I checked the settings. The DB and the Transaction log are both set to unlimited growth and to grow automatically.

At this point I'm baffled. I have a DTS package on 3 servers... all three running successfully for days on end... spontaneously two of them stop working giving the error in the original post... very very troubling...|||Would you believe I came across something that may have fixed it right after I posted? It wouldn't be the first time...

I found a reference to the same error code, but different error message, in regard to an Oracle error. The solution was to make the recordset a client-side recordset. I had originally thought of this, but the DTS package is in SQL Server, so my thinking was, when this runs, there is no client, so it has to run as a server-side recordset. When I changed ADO to use a client-side recordset (which is still the SQL Server), it started working again. I changed it back, it failed, put it back in again, it worked.

Very strange... why would you need to declare a recordset to be client-side, even though it's running in the SQL Server itself? Since DTS runs under a separate agent, does that count as a "client"?

'8007007F' error

Hi,
I have a problem with my SQL Server 2000.
When I try to display the rows from a table with enterprise manager, I get a
'8007007F' error
I don't have this problem if I read the table from a Query using the Query
Anlyzer.
I use Windows 2003 Server and I installed the MDAC 8.x
This problem also occurs when I try to open databases from Active Server
pages
Any ideas ?
Stan.I'm experiencing the same issues after upgrading from Windows Server 2000 to
Windows Server 2003. I receive error '8007007F' when i try to query an ASP
page|||Hi Shaheen,
My problem was not a ASP problem but a DLL problem
I was not able to view my datatable with Enterprise Manager.
If you have the same problem in this case, check this url out.
http://groups.google.com/groups?q=%...0phx.gbl&rnum=1
Hope that helps.
Stan
"Shaheen" <anonymous@.discussions.microsoft.com> a crit dans le message de
news:AE4ADF75-0850-4D7D-9488-EF484AFC77A3@.microsoft.com...
> I'm experiencing the same issues after upgrading from Windows Server 2000
to Windows Server 2003. I receive error '8007007F' when i try to query an
ASP page|||I had the same problem after upgrading to Windows 2003 Server. I called Mic
rosoft and here is the fix:
Symptoms:
After upgrading from Windows 2000 to Windows 2003 attempting to access a dat
abase or data component will result in a '8007007f' or "The specified proced
ure could not be found" error.
Status:
This is a known issue with some installations of Windows 2003
Workaround:
Extract oledb32.dll from the zip file into these two directories. It's impo
rtant that it be done in this order:
1) C:\WINNT\system32\dllCache
2) C:\Program Files\Common Files\System\OLE DB
3) Reboot the server
Cause:
This issue is caused when the Windows 2003 installer did not update the oled
b32.dll file.
You can dowload the oledb32.dll file here: [url]http://www.promiseweb.com/oledb32
.zip[/
url]
This is per Malcolm Stewart at Microsoft Developer Support

'8007007F' error

Hi,
I have a problem with my SQL Server 2000.
When I try to display the rows from a table with enterprise manager, I get a
'8007007F' error
I don't have this problem if I read the table from a Query using the Query
Anlyzer.
I use Windows 2003 Server and I installed the MDAC 8.x
This problem also occurs when I try to open databases from Active Server
pages
Any ideas ?
Stan.I'm experiencing the same issues after upgrading from Windows Server 2000 to Windows Server 2003. I receive error '8007007F' when i try to query an ASP page|||Hi Shaheen,
My problem was not a ASP problem but a DLL problem
I was not able to view my datatable with Enterprise Manager.
If you have the same problem in this case, check this url out.
http://groups.google.com/groups?q=%228007007F+the+fix%22&hl=fr&lr=&ie=UTF-8&selm=081601c309fd%242ac4c3e0%24a001280a%40phx.gbl&rnum=1
Hope that helps.
Stan
"Shaheen" <anonymous@.discussions.microsoft.com> a écrit dans le message de
news:AE4ADF75-0850-4D7D-9488-EF484AFC77A3@.microsoft.com...
> I'm experiencing the same issues after upgrading from Windows Server 2000
to Windows Server 2003. I receive error '8007007F' when i try to query an
ASP page