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

Number of users

Quick question, is there a limitation on the number of users that can get into SQL server at one time? Say 1500 or 2000 users? If not, I wonder how many users can actually cause the performance of the server to have a significant drop.
Thanks
SHKThere is a pretty hard limit at 32000 users, but I think it could be broken given sufficient hardware. I'm not sure that it makes any sense to even go there, other than as an academic exercise because it smacks of poor system design. A federated replicated approach allows practically infinite growth from an application perspective.

Most servers will crunch LONG before you even begin to approach the limits of the database engine. The number of supportable users usually depends on the RAM use more than anything, although the I/O bus often factors into that decision too.

-PatP|||Are you talking about connections or users?

You should keep your transactions short AND your connections as few as possible...

Get in Get out...

I use connection pooling, and even though I may have thousands of users, I'll only up a set number of connections..that stay open...for connection pooling purposes...

Why do you ask?

Sounds like a web app

Number of Users

Hello,
Need a quick help.
Our SQL 2K server on WIN2K is configured with 10 seat
licenses. "PROCESS INFO" of EM is showing more than 10
(15) users connections. (15 different network addresses).
Why is that?SQL has some connections which are overhead...they do not count... If a user
connects multiple times, that still counts as a single connection.. But if
you are getting 15 different mac addresses - that is interesting..
Make sure license manager is running.
Check the NT event log to see if there are license violation messages.
(check SQL log as well.)
Lastly using SQL enterprise manager, check the number of connections, the
default is unlimited, you may wish to lower it.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"dennis" <dennis_marco@.hotmail.com> wrote in message
news:060901c3814a$3e3c5450$a401280a@.phx.gbl...
> Hello,
> Need a quick help.
> Our SQL 2K server on WIN2K is configured with 10 seat
> licenses. "PROCESS INFO" of EM is showing more than 10
> (15) users connections. (15 different network addresses).
> Why is that?

Number of users

I want to know what is the max number of users that the MSSQL Server 7.0 can allow without chash or be slowly.
I have a server with 950 users conected and it=B4s very slowly.see
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/architec/8_ar_ts_8dbn.asp
950 is no where near the max capacity.
Running slowly will depend on the design and
implementation rather than the product.
You can build something that couldn't cope withy 2 users
if you try.

Number of IOs Per Second?

I need to size a storage system. I know that I will have two tables, each
with eight million records. I will have 10 users, each of whom will be
doing complex reports simultaneously, each of which does joins against those
eight million record tables.
What are some guidelines for best and and worst case, about the number of
IOs per second that this system will generate? I need to make sure my
RAID arrays have enough physical disks to satisfy this number of IOs.
If the database is 2 TB in size, what is the desired amount of cache memory?
Will
westes AT earthbroadcast.com
I also found a more recent article
http://www.microsoft.com/technet/pro.../rdbmspft.mspx
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CHANGE USERNAME TO westes" <DELETE_westes@.earthbroadcast.com> wrote in
message news:u0fS9ExfEHA.1656@.TK2MSFTNGP10.phx.gbl...
> I need to size a storage system. I know that I will have two tables,
each
> with eight million records. I will have 10 users, each of whom will be
> doing complex reports simultaneously, each of which does joins against
those
> eight million record tables.
> What are some guidelines for best and and worst case, about the number of
> IOs per second that this system will generate? I need to make sure
my
> RAID arrays have enough physical disks to satisfy this number of IOs.
> If the database is 2 TB in size, what is the desired amount of cache
memory?
> --
> Will
> westes AT earthbroadcast.com
>
|||There is a great article by Henry Lau about this. it is targeted at SQL 7,
but the concepts still apply
http://msdn.microsoft.com/library/de...l7perftune.asp
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CHANGE USERNAME TO westes" <DELETE_westes@.earthbroadcast.com> wrote in
message news:u0fS9ExfEHA.1656@.TK2MSFTNGP10.phx.gbl...
> I need to size a storage system. I know that I will have two tables,
each
> with eight million records. I will have 10 users, each of whom will be
> doing complex reports simultaneously, each of which does joins against
those
> eight million record tables.
> What are some guidelines for best and and worst case, about the number of
> IOs per second that this system will generate? I need to make sure
my
> RAID arrays have enough physical disks to satisfy this number of IOs.
> If the database is 2 TB in size, what is the desired amount of cache
memory?
> --
> Will
> westes AT earthbroadcast.com
>
sql

Number of IOs Per Second?

I need to size a storage system. I know that I will have two tables, each
with eight million records. I will have 10 users, each of whom will be
doing complex reports simultaneously, each of which does joins against those
eight million record tables.
What are some guidelines for best and and worst case, about the number of
IOs per second that this system will generate? I need to make sure my
RAID arrays have enough physical disks to satisfy this number of IOs.
If the database is 2 TB in size, what is the desired amount of cache memory?
Will
westes AT earthbroadcast.comI also found a more recent article
http://www.microsoft.com/technet/pr...n/rdbmspft.mspx
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CHANGE USERNAME TO westes" <DELETE_westes@.earthbroadcast.com> wrote in
message news:u0fS9ExfEHA.1656@.TK2MSFTNGP10.phx.gbl...
> I need to size a storage system. I know that I will have two tables,
each
> with eight million records. I will have 10 users, each of whom will be
> doing complex reports simultaneously, each of which does joins against
those
> eight million record tables.
> What are some guidelines for best and and worst case, about the number of
> IOs per second that this system will generate? I need to make sure
my
> RAID arrays have enough physical disks to satisfy this number of IOs.
> If the database is 2 TB in size, what is the desired amount of cache
memory?
> --
> Will
> westes AT earthbroadcast.com
>|||There is a great article by Henry Lau about this. it is targeted at SQL 7,
but the concepts still apply
sql7perftune.asp" target="_blank">http://msdn.microsoft.com/library/d...ql7perftune.asp
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CHANGE USERNAME TO westes" <DELETE_westes@.earthbroadcast.com> wrote in
message news:u0fS9ExfEHA.1656@.TK2MSFTNGP10.phx.gbl...
> I need to size a storage system. I know that I will have two tables,
each
> with eight million records. I will have 10 users, each of whom will be
> doing complex reports simultaneously, each of which does joins against
those
> eight million record tables.
> What are some guidelines for best and and worst case, about the number of
> IOs per second that this system will generate? I need to make sure
my
> RAID arrays have enough physical disks to satisfy this number of IOs.
> If the database is 2 TB in size, what is the desired amount of cache
memory?
> --
> Will
> westes AT earthbroadcast.com
>

Number of IOs Per Second?

I need to size a storage system. I know that I will have two tables, each
with eight million records. I will have 10 users, each of whom will be
doing complex reports simultaneously, each of which does joins against those
eight million record tables.
What are some guidelines for best and and worst case, about the number of
IOs per second that this system will generate? I need to make sure my
RAID arrays have enough physical disks to satisfy this number of IOs.
If the database is 2 TB in size, what is the desired amount of cache memory?
--
Will
westes AT earthbroadcast.comI also found a more recent article
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/rdbmspft.mspx
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CHANGE USERNAME TO westes" <DELETE_westes@.earthbroadcast.com> wrote in
message news:u0fS9ExfEHA.1656@.TK2MSFTNGP10.phx.gbl...
> I need to size a storage system. I know that I will have two tables,
each
> with eight million records. I will have 10 users, each of whom will be
> doing complex reports simultaneously, each of which does joins against
those
> eight million record tables.
> What are some guidelines for best and and worst case, about the number of
> IOs per second that this system will generate? I need to make sure
my
> RAID arrays have enough physical disks to satisfy this number of IOs.
> If the database is 2 TB in size, what is the desired amount of cache
memory?
> --
> Will
> westes AT earthbroadcast.com
>|||There is a great article by Henry Lau about this. it is targeted at SQL 7,
but the concepts still apply
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql7/html/msdn_sql7perftune.asp
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CHANGE USERNAME TO westes" <DELETE_westes@.earthbroadcast.com> wrote in
message news:u0fS9ExfEHA.1656@.TK2MSFTNGP10.phx.gbl...
> I need to size a storage system. I know that I will have two tables,
each
> with eight million records. I will have 10 users, each of whom will be
> doing complex reports simultaneously, each of which does joins against
those
> eight million record tables.
> What are some guidelines for best and and worst case, about the number of
> IOs per second that this system will generate? I need to make sure
my
> RAID arrays have enough physical disks to satisfy this number of IOs.
> If the database is 2 TB in size, what is the desired amount of cache
memory?
> --
> Will
> westes AT earthbroadcast.com
>

Number of fields in a table?

My users login from the tbl_users table.

I would like to store all information on them in this table but that amounts to quite a lot of fields (about 50) and some of these will store a lot of HTML text in them.

Is it best to split the table up for performance reasons or will it make little difference?

Thanks,
JBIf thoes are 50 text fields. Then I would prefer to split. I had faced difficulity with it!|||sreedhark thanks,

Actually only 3 fields are text the others are pretty small fields holding phone numbers, addresses etc.

Would you still split?

Is there a way to query the size of field allocation somehow and is there a magic number that is deemed to be too big in performance terms for the web?

Thanks,
JB|||Well, for text field, using of DataLength you can get how much data is stored in it!

3 fields, I am not sure, It's depend on how much data you are storing! If you are not storing not too large chuncks of data, then we can impliment.

Hope it helps!|||I'd still split. I don't know why I'm fond of it. I guess it makes an easier design to split the login info, and the user data. I'm particular to how I design my databases, but it should be ok to leave them all on one table. However, I personally would put the user data on another table, and have a link table for the login/password, and store the last time of login on that table as well as other info on just logins like remote_addr, and session id.

number of database users

Hi,
Does anybody know whether performance is affected if all database
connections are done through the same database user name? Ie will performanc
e
be better if I had 20-30 different database users instead of one I am using
at the moment.
Personally, I am not expecting better performance!. Thanks a lot.
Panos.Rather broad question, but if you are using Connection Pooling somewhere
along the line you should see an increase in "performance" by using a single
user as opposed to multiple users since new connections may not need to be
created or maintained - SQL Server can just keep using the same one(s) over
and over- though with only 20-30 users you probably won't see much of an
increase in performance (if anything). The actual overhead on the processor
s
for 20-30 users vs one singe user probably won't be noticable, and the
queries themselves won't care.
HTH
"Panos Stavroulis." wrote:

> Hi,
> Does anybody know whether performance is affected if all database
> connections are done through the same database user name? Ie will performa
nce
> be better if I had 20-30 different database users instead of one I am usin
g
> at the moment.
> Personally, I am not expecting better performance!. Thanks a lot.
> Panos.|||You will not get worse performance using same user. You might get better per
f, if using the same
user will mean better cache hits for proc and query plans (depends on how we
ll written your SQL is).
There are other implications, of course, like traceability.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in mes
sage
news:FEB14413-F110-4C8F-8E4A-294AC45BCB5C@.microsoft.com...
> Hi,
> Does anybody know whether performance is affected if all database
> connections are done through the same database user name? Ie will performa
nce
> be better if I had 20-30 different database users instead of one I am usin
g
> at the moment.
> Personally, I am not expecting better performance!. Thanks a lot.
> Panos.

Number of concurrent users

Hello,

There was a 5 concurrent user limitation in MSDE (the version prior to SQL Express). Does that same limitation exist with SQL Express 2005?

Thanks.

Hi Val,

First off, there was not actually a 5 concurrent user limitation in MSDE, this was a myth. Check out Euan Garden's blog describing the true functionality of the MSDE workload governer if you're interested in the history of all this: http://blogs.msdn.com/euanga/archive/2006/03/09/545576.aspx.

As for SQL Express, there is no limit to the number of users; the workload governer has been completely removed. We do limit you to a single CPU, 1 GB of memory and 4 GB of user data per database.

Regards,

Mike Wachal
SQL Express team

|||Mike,

Thanks a lot for your reply and for the link to the Blog. Very interesting.

Number formating

Just moving from Access to SQL. My end goal is to have SQL mail all my fini
shed reports for me but my users need to having numbers separated by commas
and all monetary values prefixed with ￡. Have looked at books on line for
formats and number separat
ors but cannot find anything.
SELECT tblTradeData.[Date] AS Today,
SUM(TradeData.Units) AS TotalUnits,
SUM(tblTradeData.[Value]) AS TotalValue,
SUM(tblTradeData.Cost) AS TotalCost
FROM qryToday INNER JOIN
tblTradeData ON qryToday.TodayDate = tblTradeData.[Date]
GROUP BY tblTradeData.[Date]SQL Server can easily format float, real, money and datetimes... ( See the
convert command in books on line.)... For other formatting, as you want, you
will either have to do it manually using replace, etc... or (probably a
better solution) use a report writing tool. (Crystal, MS Report Services.)
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:66329495-D5D4-440D-A985-7C4DFED5CA13@.microsoft.com...

quote:

---

> Just moving from Access to SQL. My end goal is to have SQL mail all my

---

finished reports for me but my users need to having numbers separated by
commas and all monetary values prefixed with . Have looked at books on
line for formats and number separators but cannot find anything.

quote:

---

>
> SELECT tblTradeData.[Date] AS Today,
> SUM(TradeData.Units) AS TotalUnits,
> SUM(tblTradeData.[Value]) AS TotalValue,
> SUM(tblTradeData.Cost) AS TotalCost
> FROM qryToday INNER JOIN
> tblTradeData ON qryToday.TodayDate =

---

tblTradeData.[Date]

quote:

---

> GROUP BY tblTradeData.[Date]

---

|||I' am not completely sure what Kevin ask's but here is a suggestion :
SELECT
'$ <<-use Pound sign instead '+ convert(varchar(20), tblTradeData.[Date])
AS Today,
', $ '+ convert(varchar(20), SUM(TradeData.Units)) AS TotalUnits,
', $ '+ convert(varchar(20), SUM(tblTradeData.[Value])) AS TotalValue,
', $ '+ convert(varchar(20), SUM(tblTradeData.Cost)) AS TotalCost
FROM qryToday INNER JOIN
tblTradeData ON qryToday.TodayDate =
tblTradeData.[Date]
GROUP BY tblTradeData.[Date]
Depending on the length of the 'values' varchar(20) can be altered to
a longer or shorter string, for fixed length char can also be used instead
of
varchar.
Does this help ?
ben brugman
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:#sJJK2#6DHA.2480@.TK2MSFTNGP12.phx.gbl...

quote:

---

> SQL Server can easily format float, real, money and datetimes... ( See the
> convert command in books on line.)... For other formatting, as you want,

---

you

quote:

---

> will either have to do it manually using replace, etc... or (probably a
> better solution) use a report writing tool. (Crystal, MS Report Services.)
>
> "Kevin" <anonymous@.discussions.microsoft.com> wrote in message
> news:66329495-D5D4-440D-A985-7C4DFED5CA13@.microsoft.com...
> finished reports for me but my users need to having numbers separated by
> commas and all monetary values prefixed with . Have looked at books on
> line for formats and number separators but cannot find anything.
> tblTradeData.[Date]
>

---

|||In addition to my last mail.
The [no lock] hint, does increase the change on problems,
because of this hint, if there is a update on the number
from another process which has not committed yet, the
code will keep looping.
If the [no lock] hint is taken out the process will wait in
this place until the other process finishes and then continue.
This will consume less CPU.
If the [no lock] was placed because of a (logical) deadlock,
(because two processes both ask for two numbers but
in a different order, one asks first for the Tracking_id and then
for the Info_ID, the other process the other way round) and
both are done in a transaction context then these two processes
keep the stored procedure looping. (Both have one number and
won't stop till they get the other number). With the [no lock] hint
taken out the sql-server will detect a deadlock and one of the
processes will be choosen as a deadlock victim.
(A deadlock victim is better than looping processes).
Please keep us informed, of what your problem was and which
solution you have choosen,
ben brugman
"ben brugman" <ben@.niethier.nl> wrote in message
news:#QEjaS$6DHA.2628@.TK2MSFTNGP10.phx.gbl...

quote:

---

> I' am not completely sure what Kevin ask's but here is a suggestion :
> SELECT
> '$ <<-use Pound sign instead '+ convert(varchar(20), tblTradeData.[Date])
> AS Today,
> ', $ '+ convert(varchar(20), SUM(TradeData.Units)) AS TotalUnits,
> ', $ '+ convert(varchar(20), SUM(tblTradeData.[Value])) AS TotalValue,
> ', $ '+ convert(varchar(20), SUM(tblTradeData.Cost)) AS TotalCost
> FROM qryToday INNER JOIN
> tblTradeData ON qryToday.TodayDate =
> tblTradeData.[Date]
> GROUP BY tblTradeData.[Date]
> Depending on the length of the 'values' varchar(20) can be altered to
> a longer or shorter string, for fixed length char can also be used instead
> of
> varchar.
> Does this help ?
> ben brugman
>
> "Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
> news:#sJJK2#6DHA.2480@.TK2MSFTNGP12.phx.gbl...
the[QUOTE]
> you
Services.)[QUOTE]
my[QUOTE]
by[QUOTE]
>

---

Number formating

Just moving from Access to SQL. My end goal is to have SQL mail all my finished reports for me but my users need to having numbers separated by commas and all monetary values prefixed with £. Have looked at books on line for formats and number separators but cannot find anything
SELECT tblTradeData.[Date] AS Today,
SUM(TradeData.Units) AS TotalUnits,
SUM(tblTradeData.[Value]) AS TotalValue,
SUM(tblTradeData.Cost) AS TotalCos
FROM qryToday INNER JOI
tblTradeData ON qryToday.TodayDate = tblTradeData.[Date
GROUP BY tblTradeData.[Date]SQL Server can easily format float, real, money and datetimes... ( See the
convert command in books on line.)... For other formatting, as you want, you
will either have to do it manually using replace, etc... or (probably a
better solution) use a report writing tool. (Crystal, MS Report Services.)
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:66329495-D5D4-440D-A985-7C4DFED5CA13@.microsoft.com...
> Just moving from Access to SQL. My end goal is to have SQL mail all my
finished reports for me but my users need to having numbers separated by
commas and all monetary values prefixed with £. Have looked at books on
line for formats and number separators but cannot find anything.
>
> SELECT tblTradeData.[Date] AS Today,
> SUM(TradeData.Units) AS TotalUnits,
> SUM(tblTradeData.[Value]) AS TotalValue,
> SUM(tblTradeData.Cost) AS TotalCost
> FROM qryToday INNER JOIN
> tblTradeData ON qryToday.TodayDate =tblTradeData.[Date]
> GROUP BY tblTradeData.[Date]|||I' am not completely sure what Kevin ask's but here is a suggestion :
SELECT
'$ <<-use Pound sign instead '+ convert(varchar(20), tblTradeData.[Date])
AS Today,
', $ '+ convert(varchar(20), SUM(TradeData.Units)) AS TotalUnits,
', $ '+ convert(varchar(20), SUM(tblTradeData.[Value])) AS TotalValue,
', $ '+ convert(varchar(20), SUM(tblTradeData.Cost)) AS TotalCost
FROM qryToday INNER JOIN
tblTradeData ON qryToday.TodayDate = tblTradeData.[Date]
GROUP BY tblTradeData.[Date]
Depending on the length of the 'values' varchar(20) can be altered to
a longer or shorter string, for fixed length char can also be used instead
of
varchar.
Does this help ?
ben brugman
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:#sJJK2#6DHA.2480@.TK2MSFTNGP12.phx.gbl...
> SQL Server can easily format float, real, money and datetimes... ( See the
> convert command in books on line.)... For other formatting, as you want,
you
> will either have to do it manually using replace, etc... or (probably a
> better solution) use a report writing tool. (Crystal, MS Report Services.)
>
> "Kevin" <anonymous@.discussions.microsoft.com> wrote in message
> news:66329495-D5D4-440D-A985-7C4DFED5CA13@.microsoft.com...
> > Just moving from Access to SQL. My end goal is to have SQL mail all my
> finished reports for me but my users need to having numbers separated by
> commas and all monetary values prefixed with £. Have looked at books on
> line for formats and number separators but cannot find anything.
> >
> >
> > SELECT tblTradeData.[Date] AS Today,
> > SUM(TradeData.Units) AS TotalUnits,
> > SUM(tblTradeData.[Value]) AS TotalValue,
> > SUM(tblTradeData.Cost) AS TotalCost
> > FROM qryToday INNER JOIN
> > tblTradeData ON qryToday.TodayDate => tblTradeData.[Date]
> > GROUP BY tblTradeData.[Date]
>|||In addition to my last mail.
The [no lock] hint, does increase the change on problems,
because of this hint, if there is a update on the number
from another process which has not committed yet, the
code will keep looping.
If the [no lock] hint is taken out the process will wait in
this place until the other process finishes and then continue.
This will consume less CPU.
If the [no lock] was placed because of a (logical) deadlock,
(because two processes both ask for two numbers but
in a different order, one asks first for the Tracking_id and then
for the Info_ID, the other process the other way round) and
both are done in a transaction context then these two processes
keep the stored procedure looping. (Both have one number and
won't stop till they get the other number). With the [no lock] hint
taken out the sql-server will detect a deadlock and one of the
processes will be choosen as a deadlock victim.
(A deadlock victim is better than looping processes).
Please keep us informed, of what your problem was and which
solution you have choosen,
ben brugman
"ben brugman" <ben@.niethier.nl> wrote in message
news:#QEjaS$6DHA.2628@.TK2MSFTNGP10.phx.gbl...
> I' am not completely sure what Kevin ask's but here is a suggestion :
> SELECT
> '$ <<-use Pound sign instead '+ convert(varchar(20), tblTradeData.[Date])
> AS Today,
> ', $ '+ convert(varchar(20), SUM(TradeData.Units)) AS TotalUnits,
> ', $ '+ convert(varchar(20), SUM(tblTradeData.[Value])) AS TotalValue,
> ', $ '+ convert(varchar(20), SUM(tblTradeData.Cost)) AS TotalCost
> FROM qryToday INNER JOIN
> tblTradeData ON qryToday.TodayDate => tblTradeData.[Date]
> GROUP BY tblTradeData.[Date]
> Depending on the length of the 'values' varchar(20) can be altered to
> a longer or shorter string, for fixed length char can also be used instead
> of
> varchar.
> Does this help ?
> ben brugman
>
> "Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
> news:#sJJK2#6DHA.2480@.TK2MSFTNGP12.phx.gbl...
> > SQL Server can easily format float, real, money and datetimes... ( See
the
> > convert command in books on line.)... For other formatting, as you want,
> you
> > will either have to do it manually using replace, etc... or (probably a
> > better solution) use a report writing tool. (Crystal, MS Report
Services.)
> >
> >
> > "Kevin" <anonymous@.discussions.microsoft.com> wrote in message
> > news:66329495-D5D4-440D-A985-7C4DFED5CA13@.microsoft.com...
> > > Just moving from Access to SQL. My end goal is to have SQL mail all
my
> > finished reports for me but my users need to having numbers separated
by
> > commas and all monetary values prefixed with £. Have looked at books on
> > line for formats and number separators but cannot find anything.
> > >
> > >
> > > SELECT tblTradeData.[Date] AS Today,
> > > SUM(TradeData.Units) AS TotalUnits,
> > > SUM(tblTradeData.[Value]) AS TotalValue,
> > > SUM(tblTradeData.Cost) AS TotalCost
> > > FROM qryToday INNER JOIN
> > > tblTradeData ON qryToday.TodayDate => > tblTradeData.[Date]
> > > GROUP BY tblTradeData.[Date]
> >
> >
>

Number Format after Exporting to Excel from Reporting Services

I suddenly have a large amount of users asking me why they can't do
sums on Excel 2003 exported Reporting Services reports. The numbers
in the cells are not formatted as numbers nor does it appear like you
can change it within Excel. Some are retypeing number into each
column.
Is this something I can control within Reporting Services or does
something need to be done within Excel after the export.
Any help would be great.
Thanks,
MikeOn Jul 13, 12:52 pm, michael h <mikej...@.gmail.com> wrote:
> I suddenly have a large amount of users asking me why they can't do
> sums on Excel 2003 exported Reporting Services reports. The numbers
> in the cells are not formatted as numbers nor does it appear like you
> can change it within Excel. Some are retypeing number into each
> column.
> Is this something I can control within Reporting Services or does
> something need to be done within Excel after the export.
> Any help would be great.
> Thanks,
> Mike
You might be able to convert the numbers via an expression and that
way it can export and be recognized as an integer/long value. An
expression similar to this might work.
=CInt(Fields!FieldName.Value)
-or-
=CLng(Fields!FieldName.Value)
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

NULL displays in report

I have a reports that basically prints the contents of a table so the users
can export the dat to excel. In the areas where there is a NULL value in the
table, the word NULL shows up on the report and when it is exported to Excel.
Is there an easy way to change this so that the values show up as empty?Okay, so you're probably confused as to why this post is here. It was
because I was confused as to why the NULL would actually appear in the report
since I never had that problem before and Null values should appear blank in
the fields. I tried writing an expression IIF(Field!mydata.value = Nothing,
"", Field!mydata.value) and it didn't work. Then I got the crazy notion that
just maybe some enter the word 'NULL' as text into that field. Preposterous,
you say? Well, you're wrong. Someone actually went in and updated all the
empty values with the the text NULL.
Perhaps someone was bored or playing a joke... Maybe.
Perhaps someone is actually that stupid... Likely.
Perhaps someone is trying to make my head explode on a Friday afternoon...
Definitely.
"Andy" wrote:
> I have a reports that basically prints the contents of a table so the users
> can export the dat to excel. In the areas where there is a NULL value in the
> table, the word NULL shows up on the report and when it is exported to Excel.
> Is there an easy way to change this so that the values show up as empty?