Nummerous Deadlocks

I receive numerous deadlocks while processing transactions with more than 2
users in SQLServer 2000 SP3. While these does not happen often, it does
happen often enough where it has become an annoyance. The error that the
users are getting are very similar or identical to the one below:
Microsoft SQL Server:1205[Microsoft][ODBC SQL Server Driver][SQL
Server]Transaction (Process ID 53) was deadlocked on lock resources with
another process and has been chosen as the deadlock
Does anybody have an idea in how to correct this issue.
Start here
http://www.sql-server-performance.com/deadlocks.asp
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Pete Ocasio" <pete.ocasio@.execupay.com> wrote in message
news:uIJgrapuEHA.2016@.TK2MSFTNGP15.phx.gbl...
>I receive numerous deadlocks while processing transactions with more than 2
> users in SQLServer 2000 SP3. While these does not happen often, it does
> happen often enough where it has become an annoyance. The error that the
> users are getting are very similar or identical to the one below:
> Microsoft SQL Server:1205[Microsoft][ODBC SQL Server Driver][SQL
> Server]Transaction (Process ID 53) was deadlocked on lock resources with
> another process and has been chosen as the deadlock
> Does anybody have an idea in how to correct this issue.
>
>

Nummerous Deadlocks

I receive numerous deadlocks while processing transactions with more than 2
users in SQLServer 2000 SP3. While these does not happen often, it does
happen often enough where it has become an annoyance. The error that the
users are getting are very similar or identical to the one below:
Microsoft SQL Server:1205[Microsoft][ODBC SQL Server Driver][SQL
Server]Transaction (Process ID 53) was deadlocked on lock resources with
another process and has been chosen as the deadlock
Does anybody have an idea in how to correct this issue.
Start here
http://www.sql-server-performance.com/deadlocks.asp
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Pete Ocasio" <pete.ocasio@.execupay.com> wrote in message
news:uIJgrapuEHA.2016@.TK2MSFTNGP15.phx.gbl...
>I receive numerous deadlocks while processing transactions with more than 2
> users in SQLServer 2000 SP3. While these does not happen often, it does
> happen often enough where it has become an annoyance. The error that the
> users are getting are very similar or identical to the one below:
> Microsoft SQL Server:1205[Microsoft][ODBC SQL Server Driver][SQL
> Server]Transaction (Process ID 53) was deadlocked on lock resources with
> another process and has been chosen as the deadlock
> Does anybody have an idea in how to correct this issue.
>
>

Nummerous Deadlocks

I receive numerous deadlocks while processing transactions with more than 2
users in SQLServer 2000 SP3. While these does not happen often, it does
happen often enough where it has become an annoyance. The error that the
users are getting are very similar or identical to the one below:
Microsoft SQL Server:1205[Microsoft][ODBC SQL Server Driver][SQL
Server]Transaction (Process ID 53) was deadlocked on lock resources with
another process and has been chosen as the deadlock
Does anybody have an idea in how to correct this issue.
Start here
http://www.sql-server-performance.com/deadlocks.asp
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Pete Ocasio" <pete.ocasio@.execupay.com> wrote in message
news:uIJgrapuEHA.2016@.TK2MSFTNGP15.phx.gbl...
>I receive numerous deadlocks while processing transactions with more than 2
> users in SQLServer 2000 SP3. While these does not happen often, it does
> happen often enough where it has become an annoyance. The error that the
> users are getting are very similar or identical to the one below:
> Microsoft SQL Server:1205[Microsoft][ODBC SQL Server Driver][SQL
> Server]Transaction (Process ID 53) was deadlocked on lock resources with
> another process and has been chosen as the deadlock
> Does anybody have an idea in how to correct this issue.
>
>
|||hi
you just check the join.if there is a join and condtion
is there then you have to applay where condtion for both
biswajit das

Nummerous Deadlocks

I receive numerous deadlocks while processing transactions with more than 2
users in SQLServer 2000 SP3. While these does not happen often, it does
happen often enough where it has become an annoyance. The error that the
users are getting are very similar or identical to the one below:
Microsoft SQL Server:1205[Microsoft][ODBC SQL Server Driver][SQL
Server]Transaction (Process ID 53) was deadlocked on lock resources with
another process and has been chosen as the deadlock
Does anybody have an idea in how to correct this issue.Start here
http://www.sql-server-performance.com/deadlocks.asp
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Pete Ocasio" <pete.ocasio@.execupay.com> wrote in message
news:uIJgrapuEHA.2016@.TK2MSFTNGP15.phx.gbl...
>I receive numerous deadlocks while processing transactions with more than 2
> users in SQLServer 2000 SP3. While these does not happen often, it does
> happen often enough where it has become an annoyance. The error that the
> users are getting are very similar or identical to the one below:
> Microsoft SQL Server:1205[Microsoft][ODBC SQL Server Driver][SQL
> Server]Transaction (Process ID 53) was deadlocked on lock resources with
> another process and has been chosen as the deadlock
> Does anybody have an idea in how to correct this issue.
>
>sql

Nummerous Deadlocks

I receive numerous deadlocks while processing transactions with more than 2
users in SQLServer 2000 SP3. While these does not happen often, it does
happen often enough where it has become an annoyance. The error that the
users are getting are very similar or identical to the one below:
Microsoft SQL Server:1205[Microsoft][ODBC SQL Server Driver][SQL
Server]Transaction (Process ID 53) was deadlocked on lock resources with
another process and has been chosen as the deadlock
Does anybody have an idea in how to correct this issue.
Start here
http://www.sql-server-performance.com/deadlocks.asp
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Pete Ocasio" <pete.ocasio@.execupay.com> wrote in message
news:uIJgrapuEHA.2016@.TK2MSFTNGP15.phx.gbl...
>I receive numerous deadlocks while processing transactions with more than 2
> users in SQLServer 2000 SP3. While these does not happen often, it does
> happen often enough where it has become an annoyance. The error that the
> users are getting are very similar or identical to the one below:
> Microsoft SQL Server:1205[Microsoft][ODBC SQL Server Driver][SQL
> Server]Transaction (Process ID 53) was deadlocked on lock resources with
> another process and has been chosen as the deadlock
> Does anybody have an idea in how to correct this issue.
>
>

Nummerous Deadlocks

I receive numerous deadlocks while processing transactions with more than 2
users in SQLServer 2000 SP3. While these does not happen often, it does
happen often enough where it has become an annoyance. The error that the
users are getting are very similar or identical to the one below:
Microsoft SQL Server:1205[Microsoft][ODBC SQL Server Driver][SQL
Server]Transaction (Process ID 53) was deadlocked on lock resources with
another process and has been chosen as the deadlock
Does anybody have an idea in how to correct this issue.Start here
http://www.sql-server-performance.com/deadlocks.asp
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Pete Ocasio" <pete.ocasio@.execupay.com> wrote in message
news:uIJgrapuEHA.2016@.TK2MSFTNGP15.phx.gbl...
>I receive numerous deadlocks while processing transactions with more than 2
> users in SQLServer 2000 SP3. While these does not happen often, it does
> happen often enough where it has become an annoyance. The error that the
> users are getting are very similar or identical to the one below:
> Microsoft SQL Server:1205[Microsoft][ODBC SQL Server Driver][S
QL
> Server]Transaction (Process ID 53) was deadlocked on lock resources with
> another process and has been chosen as the deadlock
> Does anybody have an idea in how to correct this issue.
>
>

NUMERIC or INTEGER ?

Hi,
Is there any difference between NUMERIC and INTEGER datatype?
I am currently migrating my application from oracle to sqlserver hence i need to know the best equivalent:
Oracle
var_int int(38);
SQLServer:
@.var_int numeric(38) or @.var_integer integer
Please let me know the best equivalent
--SrikSrik
The basic different between numeric and interger is the size that u want to allocate to the fields

Just try to do this
--ERROR
create table test
(
p numeric(38),
x integer(38) --This will not table the width mean its fixed and it cannot be changed.
)
go

--CORRECT
create table test
(
p numeric(38),
x integer
)
go

and when u display the structure.

p numeric no 17 38 0 yes (n/a) (n/a) NULL
x int no 4 10 0 yes (n/a) (n/a) NULL
17 and 4 is the length of the field
38 and 10 is the precision of the fields respectively.

So it is better we use numeric instead of integer when converting Oracle to Sql server.

Hope this helps you|||Use integer in SQL Server for Oracle int. NUMBER is close to NUMERIC. Numeric implementation between Oracle and SQL Server is also slightly different. It has to do with storage and how we calculate precision/scale of resulting expression. It is best to understand those rules before using numeric otherwise you may get truncated or wrong results.|||thanks Dinesh and Umachandar for the info

numeric data types in sqlserver

Whats the difference between int,float and decimal in sql server.
WHich data type takes how many bytes?
What data type i should use to store the following sets of data
1set--100000,854275,74892734
2set--6538726.98765923,762.659325
Thanks.int is for integers. 4-byte. Max value is 2^31. This looks adequate for your first set although if the numbers will exceed 2^31 you should use bigint (8 bytes)

float is for floating point numbers (non-integers). You will need floating point for the second set. I would suggest the double type for that set.

decimal, numeric, and float all support customizable precision levels. BOL has pretty good documentation on this.

Number of SQL Client is . . .

SQLServer 10Client; the number 10 is:
- a physical limit of connections (ADODB.Connection)?
- a licences limit ?
- a PCs limit?
- a number of processes limit?
- a number of served query and the next queries are queued?
Some ideas?
Tnx in advance.
Sorry for my bad english...
Alfredo.A licenses limit.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Alfredo Volpari" <alfredo@.siam.it> wrote in message
news:bq4jds$r8n$1@.fata.cs.interbusiness.it...
> SQLServer 10Client; the number 10 is:
> - a physical limit of connections (ADODB.Connection)?
> - a licences limit ?
> - a PCs limit?
> - a number of processes limit?
> - a number of served query and the next queries are queued?
> Some ideas?
> Tnx in advance.
> Sorry for my bad english...
> Alfredo.
>

number of connections

Hi,
I would like to know if I can over the limit of user connection using the
sqlserver 2005, that is 32,767 user connections(I know that I need some
hardware resource as memory, etc).
Thanks,
Serge"Serge" <Serge@.discussions.microsoft.com> wrote in message
news:160EB448-A632-423D-BF14-7A372277BBF3@.microsoft.com...
> Hi,
> I would like to know if I can over the limit of user connection using the
> sqlserver 2005, that is 32,767 user connections(I know that I need some
> hardware resource as memory, etc).
>
I don't know of anyway.
But I'd be surprsied if you ever really needed to. That would be a MASSIVE
system that required that many simultaneous connections.
I'm curious what you're end-goal is.
> Thanks,
> Serge
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Number Formatting in SQLSERVER

say, i have a column in the database that has number values. I want to display these number with comma separators (Eg: if the column values is 1654, then i want to display it as 1,654).
How can i achieve this in my sQL queryFormatting is done on the client. The simplest answer is to use a client such as MS-Access, MS-Excel, Crystal Reports, etc that does this formatting for you.

You can choose to do the formatting on the server, but this is a really poor choice.

-PatP|||it's a really poor choice, but it's also a fun challenge for a monday morning

w00h00!!
create table teststuff
( id tinyint not null primary key identity
, foo integer
);
insert into teststuff(foo) values(-1)
insert into teststuff(foo) values(0)
insert into teststuff(foo) values(937)
insert into teststuff(foo) values(1000)
insert into teststuff(foo) values(345678)
insert into teststuff(foo) values(1234567890)
insert into teststuff(foo) values(-111111111)
insert into teststuff(foo) values(3456789)
insert into teststuff(foo) values(-1000)
insert into teststuff(foo) values(-11444)
insert into teststuff(foo) values(-1555666)
insert into teststuff(foo) values(-1333555777)

select id, foo
, case when foo > -999 and foo < 999
then right(space(14)
+cast(foo as varchar)
,14)
when foo > -999999 and foo < 999999
then right(space(14)
+reverse(
stuff(reverse(foo),4,0,','))
,14)
when foo > -999999999 and foo < 999999999
then right(space(14)
+reverse(
stuff(
stuff(reverse(foo),7,0,',')
,4,0,','))
,14)
else right(space(14)
+reverse(
stuff(
stuff(
stuff(reverse(foo),10,0,',')
,7,0,',')
,4,0,','))
,14)
end as formatted
from teststuff

1 -1 -1
2 0 0
3 937 937
4 1000 1,000
5 345678 345,678
6 1234567890 1,234,567,890
7 -111111111 -111,111,111
8 3456789 3,456,789
9 -1000 -1,000
10 -11444 -11,444
11 -1555666 -1,555,666
12 -1333555777 -1,333,555,777

NullPointerException in CallableStatement

Hi,

First of all sorry for my pour english.

My development work is SqlServer 2000 + Web Server + JDBC
I have in my db a simple store procedure which inserts in a table rows.
My problem is when I try to insert over 1030 rows more or less. In my program the JDBC returns to me a NullPointerException in the Connection object, but when i try to insert for example 100 rows everything is ok.

I have try it with some web servers like JRun, Tomcat, etc.. and with differents jdbc too and its the same, so I think that the problem is in my database.

This Store Procedure doesnt work:
------------
declare @.i int
set @.i = 0
while @.i<10000
begin
insert into mig values (getdate(), 'p', @.i)
set @.i = @.i + 1
end
--------

And this Store Procedure works:
--------
declare @.i int
set @.i = 0
while @.i<1000 (<1030 +o-)
begin
insert into mig select getdate(), 'p', 1 from sanresco where reciae between 1 and 100000
set @.i = @.i + 1
end
----------

So the problem is when the db tries to do over 1023 individual inserts.

Any idea??

Thanks.Sure it does...

USE Northwind
GO

CREATE TABLE mig (col1 datetime, col2 char(1), col3 int)
GO

CREATE PROC mySPROC AS

SET NOCOUNT ON
DECLARE @.i int
SET @.i = 0
WHILE @.i < 10000
BEGIN
INSERT INTO mig (col1, col2, col3)
VALUES (getdate(), 'p', @.i)
SET @.i = @.i + 1
END
SET NOCOUNT OFF
GO

EXEC mySPROC
GO

SELECT COUNT(*) FROM mig
GO

DROP TABLE mig
GO

DROP PROC mySPROC
GO

Why not post the DDL of the table...any triggers? Are you timing out on the front end?|||Thanks a lot!!!!!!!
I put "set nocount on" in the sp and it works!!!!!
I had very long time ago with this problem.
Greetings.

Originally posted by Brett Kaiser
Sure it does...

USE Northwind
GO

CREATE TABLE mig (col1 datetime, col2 char(1), col3 int)
GO

CREATE PROC mySPROC AS

SET NOCOUNT ON
DECLARE @.i int
SET @.i = 0
WHILE @.i < 10000
BEGIN
INSERT INTO mig (col1, col2, col3)
VALUES (getdate(), 'p', @.i)
SET @.i = @.i + 1
END
SET NOCOUNT OFF
GO

EXEC mySPROC
GO

SELECT COUNT(*) FROM mig
GO

DROP TABLE mig
GO

DROP PROC mySPROC
GO

Why not post the DDL of the table...any triggers? Are you timing out on the front end?