Ciao,
I have created some cubes in my Analysis Server (SQL2000
Server) and try to retrieve the cube data by Excel2000.
In my own computer, when I create the data source in Excel, I
have not the options for "Microsoft OLE DB Provider for OLAP
Services" and "Microsoft OLE DB Provider for OLAP Services
8.0".
I have installed the Analisys Services..
Can anyone help? Thanks!
FabrizioHi Fabrizio,
below is the step i use retrive cube data from excal 2000.
1) in Excel 2000 click Data --> PivotTable and PivotChart Report
2) in pivot wizard step 1, choose external datasource.
3) click get data
4) click <new datasource> at OLAP CUBE tab
5) select OLAP Provider.
6) select Microsoft OLE DB Provider for Olap Services 8.0
7) click connect --> analysis server --> enter server name
8) it should display a list of OLAP database, select your database & cube.
cheers,
suhfui|||Sorry but I haven't Microsoft OLE DB Provider for Olap Services 8.0.
Why?
Thank you|||You need to install PTSFull (Pivot Table Services) on your client as well.
It can be found in SP3 for Analysis Services in the folder
sql2kasp3\msolap\install\PTS
the 8.0 version is available after that.
HTH Lutz
Showing posts with label sql2000. Show all posts
Showing posts with label sql2000. Show all posts
Monday, March 12, 2012
Saturday, February 25, 2012
Problem with deadocks
Hi list, iam very new to SQL. i have a developer that is making a very
complex query over a SQL2000 + SP3 database, with a lots of union queries.
He recieves this error:
Transaction (Process ID 97) was deadlocked on lock | communication buffer
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction.
I dont know how to proceed with this, how can try to discover the problem?
Could be a resources problem?
Thanks for the advices...Aswers in microsoft.public.sqlserver.server
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Tinchos" wrote:
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...|||http://www.microsoft.com/communitie...80-e67d8812bd2d
AMB
"Tinchos" wrote:
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...|||In your from statement use WITH (NOLOCKS) when you are selecting data.
Rick
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:B5CBFB12-BF8F-4BA8-B71A-8D9A893C66F6@.microsoft.com...
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...
complex query over a SQL2000 + SP3 database, with a lots of union queries.
He recieves this error:
Transaction (Process ID 97) was deadlocked on lock | communication buffer
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction.
I dont know how to proceed with this, how can try to discover the problem?
Could be a resources problem?
Thanks for the advices...Aswers in microsoft.public.sqlserver.server
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Tinchos" wrote:
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...|||http://www.microsoft.com/communitie...80-e67d8812bd2d
AMB
"Tinchos" wrote:
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...|||In your from statement use WITH (NOLOCKS) when you are selecting data.
Rick
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:B5CBFB12-BF8F-4BA8-B71A-8D9A893C66F6@.microsoft.com...
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...
Problem with Deadlocks
Hi list, iam very new to SQL. i have a developer that is making a very
complex query over a SQL2000 + SP3 database, with a lots of union queries.
He recieves this error:
Transaction (Process ID 97) was deadlocked on lock | communication buffer
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction.
I dont know how to proceed with this, how can try to discover the problem?
Could be a resources problem?
Thanks for the advices...
See if this helps:
Tracing Deadlocks
http://www.sqlservercentral.com/colu...gdeadlocks.asp
AMB
"Tinchos" wrote:
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...
|||Hi
Have a look as this thread:
http://groups.google.ch/group/micros...1e5ad772e285b7
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Tinchos" wrote:
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...
|||There is a lot of information about troubleshooting deadlocks in SQL Server
Books Online. Check out the following topics:
Troubleshooting Deadlocks
Troubleshooting Locking
Minimizing Deadlocks
Detecting and Ending Deadlocks
Deadlocking
Handling Deadlocks
SET DEADLOCK_PRIORITY
Deadlocks Involving Locks
Deadlocks Involving Parallelism
Deadlocks Involving Threads
Also see:
http://vyaskn.tripod.com/administration_faq.htm#q14
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:3EF15EC2-E654-4E76-A516-1B6BA8A7C5D3@.microsoft.com...
Hi list, iam very new to SQL. i have a developer that is making a very
complex query over a SQL2000 + SP3 database, with a lots of union queries.
He recieves this error:
Transaction (Process ID 97) was deadlocked on lock | communication buffer
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction.
I dont know how to proceed with this, how can try to discover the problem?
Could be a resources problem?
Thanks for the advices...
|||First some questions :
How often does the deadlock occure ?
Can you reproduce the deadlock at will ?
If the deadlock occures often, it will be easier to get more information,
with the profiler you can get more information by trapping the deadlock.
(See Troubleshooting Deadlocks, Using Trace Flag 1204 in Books on Line).
If the deadlock occures all the time, it is very likely that the application
is deadlocking itself. (E.g. by reading the data in one connection and
by writing the 'same' data in another connection.)
With deadlocks there are at least two processes involved. But both
can be from one application. If they are from different applications
and the deadlocks are (very) rare, you can build coding which can
recover from a deadlock. (Rollback the transaction and try the
same transaction again).
If the deadlocks are caused within a single applicatioin, then the
application has to be altered. (Because of different concurrency
systems some Oracle programmers working in SQL-server do
not realise that read actions can block write actions and can
therefore cause deadlocks within one application).
If the deadlock occurs between two applications and you can not
alter one application, but you can alter the other application, you
can set a preverence which application should be used as a
deadlock victim. (Set deadlock priority).
Good luck,
and keep us informed.
Ben Brugman
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:3EF15EC2-E654-4E76-A516-1B6BA8A7C5D3@.microsoft.com...
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...
complex query over a SQL2000 + SP3 database, with a lots of union queries.
He recieves this error:
Transaction (Process ID 97) was deadlocked on lock | communication buffer
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction.
I dont know how to proceed with this, how can try to discover the problem?
Could be a resources problem?
Thanks for the advices...
See if this helps:
Tracing Deadlocks
http://www.sqlservercentral.com/colu...gdeadlocks.asp
AMB
"Tinchos" wrote:
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...
|||Hi
Have a look as this thread:
http://groups.google.ch/group/micros...1e5ad772e285b7
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Tinchos" wrote:
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...
|||There is a lot of information about troubleshooting deadlocks in SQL Server
Books Online. Check out the following topics:
Troubleshooting Deadlocks
Troubleshooting Locking
Minimizing Deadlocks
Detecting and Ending Deadlocks
Deadlocking
Handling Deadlocks
SET DEADLOCK_PRIORITY
Deadlocks Involving Locks
Deadlocks Involving Parallelism
Deadlocks Involving Threads
Also see:
http://vyaskn.tripod.com/administration_faq.htm#q14
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:3EF15EC2-E654-4E76-A516-1B6BA8A7C5D3@.microsoft.com...
Hi list, iam very new to SQL. i have a developer that is making a very
complex query over a SQL2000 + SP3 database, with a lots of union queries.
He recieves this error:
Transaction (Process ID 97) was deadlocked on lock | communication buffer
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction.
I dont know how to proceed with this, how can try to discover the problem?
Could be a resources problem?
Thanks for the advices...
|||First some questions :
How often does the deadlock occure ?
Can you reproduce the deadlock at will ?
If the deadlock occures often, it will be easier to get more information,
with the profiler you can get more information by trapping the deadlock.
(See Troubleshooting Deadlocks, Using Trace Flag 1204 in Books on Line).
If the deadlock occures all the time, it is very likely that the application
is deadlocking itself. (E.g. by reading the data in one connection and
by writing the 'same' data in another connection.)
With deadlocks there are at least two processes involved. But both
can be from one application. If they are from different applications
and the deadlocks are (very) rare, you can build coding which can
recover from a deadlock. (Rollback the transaction and try the
same transaction again).
If the deadlocks are caused within a single applicatioin, then the
application has to be altered. (Because of different concurrency
systems some Oracle programmers working in SQL-server do
not realise that read actions can block write actions and can
therefore cause deadlocks within one application).
If the deadlock occurs between two applications and you can not
alter one application, but you can alter the other application, you
can set a preverence which application should be used as a
deadlock victim. (Set deadlock priority).
Good luck,
and keep us informed.
Ben Brugman
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:3EF15EC2-E654-4E76-A516-1B6BA8A7C5D3@.microsoft.com...
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...
Problem with Deadlocks
Hi list, iam very new to SQL. i have a developer that is making a very
complex query over a SQL2000 + SP3 database, with a lots of union queries.
He recieves this error:
Transaction (Process ID 97) was deadlocked on lock | communication buffer
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction.
I dont know how to proceed with this, how can try to discover the problem?
Could be a resources problem?
Thanks for the advices...See if this helps:
Tracing Deadlocks
http://www.sqlservercentral.com/columnists/skumar/tracingdeadlocks.asp
AMB
"Tinchos" wrote:
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...|||Hi
Have a look as this thread:
http://groups.google.ch/group/microsoft.public.sqlserver.programming/browse_thread/thread/6f618594592ab8d7/381e5ad772e285b7?lnk=st&q=tibor+deadlock&rnum=1&hl=en#381e5ad772e285b7
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Tinchos" wrote:
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...|||There is a lot of information about troubleshooting deadlocks in SQL Server
Books Online. Check out the following topics:
Troubleshooting Deadlocks
Troubleshooting Locking
Minimizing Deadlocks
Detecting and Ending Deadlocks
Deadlocking
Handling Deadlocks
SET DEADLOCK_PRIORITY
Deadlocks Involving Locks
Deadlocks Involving Parallelism
Deadlocks Involving Threads
Also see:
http://vyaskn.tripod.com/administration_faq.htm#q14
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:3EF15EC2-E654-4E76-A516-1B6BA8A7C5D3@.microsoft.com...
Hi list, iam very new to SQL. i have a developer that is making a very
complex query over a SQL2000 + SP3 database, with a lots of union queries.
He recieves this error:
Transaction (Process ID 97) was deadlocked on lock | communication buffer
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction.
I dont know how to proceed with this, how can try to discover the problem?
Could be a resources problem?
Thanks for the advices...|||First some questions :
How often does the deadlock occure ?
Can you reproduce the deadlock at will ?
If the deadlock occures often, it will be easier to get more information,
with the profiler you can get more information by trapping the deadlock.
(See Troubleshooting Deadlocks, Using Trace Flag 1204 in Books on Line).
If the deadlock occures all the time, it is very likely that the application
is deadlocking itself. (E.g. by reading the data in one connection and
by writing the 'same' data in another connection.)
With deadlocks there are at least two processes involved. But both
can be from one application. If they are from different applications
and the deadlocks are (very) rare, you can build coding which can
recover from a deadlock. (Rollback the transaction and try the
same transaction again).
If the deadlocks are caused within a single applicatioin, then the
application has to be altered. (Because of different concurrency
systems some Oracle programmers working in SQL-server do
not realise that read actions can block write actions and can
therefore cause deadlocks within one application).
If the deadlock occurs between two applications and you can not
alter one application, but you can alter the other application, you
can set a preverence which application should be used as a
deadlock victim. (Set deadlock priority).
Good luck,
and keep us informed.
Ben Brugman
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:3EF15EC2-E654-4E76-A516-1B6BA8A7C5D3@.microsoft.com...
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...
complex query over a SQL2000 + SP3 database, with a lots of union queries.
He recieves this error:
Transaction (Process ID 97) was deadlocked on lock | communication buffer
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction.
I dont know how to proceed with this, how can try to discover the problem?
Could be a resources problem?
Thanks for the advices...See if this helps:
Tracing Deadlocks
http://www.sqlservercentral.com/columnists/skumar/tracingdeadlocks.asp
AMB
"Tinchos" wrote:
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...|||Hi
Have a look as this thread:
http://groups.google.ch/group/microsoft.public.sqlserver.programming/browse_thread/thread/6f618594592ab8d7/381e5ad772e285b7?lnk=st&q=tibor+deadlock&rnum=1&hl=en#381e5ad772e285b7
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Tinchos" wrote:
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...|||There is a lot of information about troubleshooting deadlocks in SQL Server
Books Online. Check out the following topics:
Troubleshooting Deadlocks
Troubleshooting Locking
Minimizing Deadlocks
Detecting and Ending Deadlocks
Deadlocking
Handling Deadlocks
SET DEADLOCK_PRIORITY
Deadlocks Involving Locks
Deadlocks Involving Parallelism
Deadlocks Involving Threads
Also see:
http://vyaskn.tripod.com/administration_faq.htm#q14
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:3EF15EC2-E654-4E76-A516-1B6BA8A7C5D3@.microsoft.com...
Hi list, iam very new to SQL. i have a developer that is making a very
complex query over a SQL2000 + SP3 database, with a lots of union queries.
He recieves this error:
Transaction (Process ID 97) was deadlocked on lock | communication buffer
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction.
I dont know how to proceed with this, how can try to discover the problem?
Could be a resources problem?
Thanks for the advices...|||First some questions :
How often does the deadlock occure ?
Can you reproduce the deadlock at will ?
If the deadlock occures often, it will be easier to get more information,
with the profiler you can get more information by trapping the deadlock.
(See Troubleshooting Deadlocks, Using Trace Flag 1204 in Books on Line).
If the deadlock occures all the time, it is very likely that the application
is deadlocking itself. (E.g. by reading the data in one connection and
by writing the 'same' data in another connection.)
With deadlocks there are at least two processes involved. But both
can be from one application. If they are from different applications
and the deadlocks are (very) rare, you can build coding which can
recover from a deadlock. (Rollback the transaction and try the
same transaction again).
If the deadlocks are caused within a single applicatioin, then the
application has to be altered. (Because of different concurrency
systems some Oracle programmers working in SQL-server do
not realise that read actions can block write actions and can
therefore cause deadlocks within one application).
If the deadlock occurs between two applications and you can not
alter one application, but you can alter the other application, you
can set a preverence which application should be used as a
deadlock victim. (Set deadlock priority).
Good luck,
and keep us informed.
Ben Brugman
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:3EF15EC2-E654-4E76-A516-1B6BA8A7C5D3@.microsoft.com...
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...
Problem with Deadlocks
Hi list, iam very new to SQL. i have a developer that is making a very
complex query over a SQL2000 + SP3 database, with a lots of union queries.
He recieves this error:
Transaction (Process ID 97) was deadlocked on lock | communication buffer
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction.
I dont know how to proceed with this, how can try to discover the problem?
Could be a resources problem?
Thanks for the advices...See if this helps:
Tracing Deadlocks
http://www.sqlservercentral.com/col...ngdeadlocks.asp
AMB
"Tinchos" wrote:
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...|||Hi
Have a look as this thread:
e5ad772e285b7" target="_blank">http://groups.google.ch/group/micro...
e5ad772e285b7
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Tinchos" wrote:
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...|||There is a lot of information about troubleshooting deadlocks in SQL Server
Books Online. Check out the following topics:
Troubleshooting Deadlocks
Troubleshooting Locking
Minimizing Deadlocks
Detecting and Ending Deadlocks
Deadlocking
Handling Deadlocks
SET DEADLOCK_PRIORITY
Deadlocks Involving Locks
Deadlocks Involving Parallelism
Deadlocks Involving Threads
Also see:
http://vyaskn.tripod.com/administration_faq.htm#q14
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:3EF15EC2-E654-4E76-A516-1B6BA8A7C5D3@.microsoft.com...
Hi list, iam very new to SQL. i have a developer that is making a very
complex query over a SQL2000 + SP3 database, with a lots of union queries.
He recieves this error:
Transaction (Process ID 97) was deadlocked on lock | communication buffer
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction.
I dont know how to proceed with this, how can try to discover the problem?
Could be a resources problem?
Thanks for the advices...|||First some questions :
How often does the deadlock occure ?
Can you reproduce the deadlock at will ?
If the deadlock occures often, it will be easier to get more information,
with the profiler you can get more information by trapping the deadlock.
(See Troubleshooting Deadlocks, Using Trace Flag 1204 in Books on Line).
If the deadlock occures all the time, it is very likely that the application
is deadlocking itself. (E.g. by reading the data in one connection and
by writing the 'same' data in another connection.)
With deadlocks there are at least two processes involved. But both
can be from one application. If they are from different applications
and the deadlocks are (very) rare, you can build coding which can
recover from a deadlock. (Rollback the transaction and try the
same transaction again).
If the deadlocks are caused within a single applicatioin, then the
application has to be altered. (Because of different concurrency
systems some Oracle programmers working in SQL-server do
not realise that read actions can block write actions and can
therefore cause deadlocks within one application).
If the deadlock occurs between two applications and you can not
alter one application, but you can alter the other application, you
can set a preverence which application should be used as a
deadlock victim. (Set deadlock priority).
Good luck,
and keep us informed.
Ben Brugman
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:3EF15EC2-E654-4E76-A516-1B6BA8A7C5D3@.microsoft.com...
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...
complex query over a SQL2000 + SP3 database, with a lots of union queries.
He recieves this error:
Transaction (Process ID 97) was deadlocked on lock | communication buffer
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction.
I dont know how to proceed with this, how can try to discover the problem?
Could be a resources problem?
Thanks for the advices...See if this helps:
Tracing Deadlocks
http://www.sqlservercentral.com/col...ngdeadlocks.asp
AMB
"Tinchos" wrote:
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...|||Hi
Have a look as this thread:
e5ad772e285b7" target="_blank">http://groups.google.ch/group/micro...
e5ad772e285b7
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Tinchos" wrote:
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...|||There is a lot of information about troubleshooting deadlocks in SQL Server
Books Online. Check out the following topics:
Troubleshooting Deadlocks
Troubleshooting Locking
Minimizing Deadlocks
Detecting and Ending Deadlocks
Deadlocking
Handling Deadlocks
SET DEADLOCK_PRIORITY
Deadlocks Involving Locks
Deadlocks Involving Parallelism
Deadlocks Involving Threads
Also see:
http://vyaskn.tripod.com/administration_faq.htm#q14
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:3EF15EC2-E654-4E76-A516-1B6BA8A7C5D3@.microsoft.com...
Hi list, iam very new to SQL. i have a developer that is making a very
complex query over a SQL2000 + SP3 database, with a lots of union queries.
He recieves this error:
Transaction (Process ID 97) was deadlocked on lock | communication buffer
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction.
I dont know how to proceed with this, how can try to discover the problem?
Could be a resources problem?
Thanks for the advices...|||First some questions :
How often does the deadlock occure ?
Can you reproduce the deadlock at will ?
If the deadlock occures often, it will be easier to get more information,
with the profiler you can get more information by trapping the deadlock.
(See Troubleshooting Deadlocks, Using Trace Flag 1204 in Books on Line).
If the deadlock occures all the time, it is very likely that the application
is deadlocking itself. (E.g. by reading the data in one connection and
by writing the 'same' data in another connection.)
With deadlocks there are at least two processes involved. But both
can be from one application. If they are from different applications
and the deadlocks are (very) rare, you can build coding which can
recover from a deadlock. (Rollback the transaction and try the
same transaction again).
If the deadlocks are caused within a single applicatioin, then the
application has to be altered. (Because of different concurrency
systems some Oracle programmers working in SQL-server do
not realise that read actions can block write actions and can
therefore cause deadlocks within one application).
If the deadlock occurs between two applications and you can not
alter one application, but you can alter the other application, you
can set a preverence which application should be used as a
deadlock victim. (Set deadlock priority).
Good luck,
and keep us informed.
Ben Brugman
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:3EF15EC2-E654-4E76-A516-1B6BA8A7C5D3@.microsoft.com...
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...
Subscribe to:
Posts (Atom)