An error occurred, but no details are available

Hello All,

I have a number of 3D web reports published.
When trying to view most of them, they produce the following error in a browser "An error occurred, but no details are available".
The title bar of the browser displays: "http://esource-03/crystal/enterprise9/eportfolio/en/infoobject.cwr?action=0&id=342".
The id changes from one report to another.
Only 3 reports displays succesfully while the other 13 have this problem.

I even tried to search for the "infoobject.cwr" on the server but couldn't find it.

Any clues?

Thank you,

DiaaOpen the report and do verify database

An error occurred during the execution of the SQL file InstallRoles.sql.

Error text:

An error occurred during the execution of the SQL file 'InstallRoles.sql'. The SQL error number is 446 and the SqlException message is: Cannot resolve collation conflict for equal to operation.
Cannot resolve collation conflict for equal to operation.

Erroroccurred when running 'aspnet_regsql -Sservername-E -ddatabase -A r'
with previously installed mambership scheme ondatabase.

When googling on this issue, one post on forums.asp.net is listed but link is dead...
please help...

ADDITION:

Also I forgot to mentiona few things(thanks to Kris):
dead link url:forums.asp.net/1063397/ShowPost.aspx

An easy question?

Just had someone else ask me a question I can't seem to find the answer to anywhere.

Is there a way to count the number of instances of a particular character within a string?

He has an array that is sourced from VB that inserts values into a table with a pipe delimiter and he wants to count how many times the delimiter appears in each row.

I've looked in BOL, and can't find anything that would allow me to do this. Anyone have any suggestions?

Thanks,
Dirk

PS - this is SQL7Can you provide more information about the vb code inserting ? What are you trying to accomplish by counting the delimiter ?|||I'm not really sure why he wants to count them. Our web guy has this application and he asked me if I could help him figure this out. Everyone else he's asked told him it couldn't be done, but I disagree. I just don't know how to do it easily. I know how I could probably accomplish it in an unconventional and cumbersome manner using patindex/charindex and some math, but I was hoping there was a simple way.

The field contains answers to questions in a survey, and each answer is delimited by a pipe. Not sure why the determination to store them as an array was made either.

Dirk|||So the field stores many strings delimited by a special character. What is the data type for this field ?|||Have you thought about modifying the split function that I posted to do this ?|||Yes. Currently the type is set to nvarchar(50). My guess is that this may need to be expanded somewhere down the road, but the type will not change.

I did consider the split function, I'm just not sure how to modify it in our current environment. I'm actually working on that now.

Dirk|||The problem is that there are not that many string functions in sql server - however, there are in vb - Can you do this in vb rather than sql server ?|||Yes, he can do it in vb but for some reason he wants to create this as a function or a stored procedure. Those web guys are a little strange sometimes. Or perhaps he wants to call it in other (future) databases or something and doesn't want to rewrite it every time...

If it can't be done fairly easily, that's OK though. I can always tell him it needs to be a vb process.
Dirk|||Something like this would give you a count of a specific character in a varchar. The value is stored in @.counter. The string being sought in this case is the pipe (|). The varchar being searched is @.compareString.
Unfortunately, this is the easiest way that I found (using charindex).

declare @.foundAt int
declare @.counter int
declare @.compareString varchar(50)

set @.compareString='I|was|here|today|'
set @.counter=0
select @.foundAt=charindex('|',@.compareString)
if (@.foundAt<>0) set @.counter=@.counter+1

while @.foundAt>0
begin
select @.foundAt=charindex('|',@.compareString,@.foundAt+1)
if (@.foundAt<>0) set @.counter=@.counter+1
end
print @.counter

That might work...|||This does look like it would work, but I'm having trouble setting the variable @.comparestring to be the column in the table. I keep getting "invalid column name"??

Thanks for this sample code though - it looks very promising!
Dirk|||You could try:

select @.compareString = (select [Column Name] from [Table Name] where [Condition])

or

select @.compareString = [Column Name] from [Table Name] where [Condition]

The only catch is that your [Condition] should return exactly one value for [Column Name], otherwise it will bomb out. I haven't figured out how to put that in a loop and check multiple records yet. If you figure it out, please post here or email me.

Thanks,

Roman|||I would wrap all of this into a function that accecpts the "@.compareString" and the delimiter, the return would be the number of delimiters.

try this...
-- =============================================
-- Create scalar function (FN)
-- =============================================
IF EXISTS (SELECT * FROM sysobjects WHERE name = N'CountDelimiter')
DROP FUNCTION CountDelimiter
GO

CREATE FUNCTION CountDelimiter(
@.compareString varchar(50),
@.delimiter varchar(1))
RETURNS int
AS
BEGIN
declare @.foundAt int, @.counter int
set @.counter=0
set @.foundAt=charindex(@.delimiter,@.compareString)
if (@.foundAt<>0) set @.counter=@.counter+1
while @.foundAt>0 begin
select @.foundAt=charindex(@.delimiter,@.compareString,@.foun dAt+1)
if (@.foundAt<>0) set @.counter=@.counter+1
end
return @.counter
END
GO

-- =============================================
-- Example to execute function
-- =============================================
SELECT dbo.CountDelimiter('I|was|here|today|','|')
GO

now you can use this in a select statment or sp.|||Paul Young's way is better, but here's a more asinine way anyway :}

declare @.foundAt int
declare @.counter int
declare @.compareString varchar(50)

DECLARE abc CURSOR FOR
SELECT [Column Name] FROM [Table Name] where [Condition]

OPEN abc

FETCH NEXT FROM abc into @.compareString
WHILE (@.@.FETCH_STATUS = 0)
begin
set @.counter=0
select @.foundAt=charindex('L',@.compareString)
if (@.foundAt<>0) set @.counter=@.counter+1

while @.foundAt>0
begin
select @.foundAt=charindex('L',@.compareString,@.foundAt+1)
if (@.foundAt<>0) set @.counter=@.counter+1
end
print @.counter
FETCH NEXT FROM abc into @.compareString
end

CLOSE abc
DEALLOCATE abc
GO|||a thousand lashes with a wet noodle for suggesting the use of a cursor!!!|||:)

Whatever it takes to make it work.|||of course, I was just kidding!|||Here is what I did to get multiple rows - I just turned it into a cursor inside a procedure. Maybe not the most effective method, but I'm just happy it actually works. The only problem I have with it now is that it only returns me the delimiter value, and I also need the associated QID. I thought I accounted for it, but apparently I didn't.

Too late in the day for my brain. If anyone can help that would be great. I'll look at the above solution a little later. Thanks to all for the help!

Dirk

CREATE procedure [dbo].[countdelim]
as
declare @.qid int
declare @.fullstring varchar (100)

declare count_cursor cursor

for select QuestionID, fulltexttoparse from Table1

open count_cursor
fetch next from count_cursor into @.qid, @.fullstring
while (@.@.fetch_status = 0)
begin
--set @.qid=(select QuestionID from Table1)
declare @.foundAt int
declare @.counter int
declare @.compareString varchar(50)
set @.compareString=@.fullstring
set @.counter=0
select
--@.qid,
@.foundAt=charindex('|',@.compareString)
--from Table1
if (@.foundAt<>0) set @.counter=@.counter+1

while @.foundAt>0
begin
select @.foundAt=charindex('|',@.compareString,@.foundAt+1)
if (@.foundAt<>0) set @.counter=@.counter+1
end
print @.counter

fetch next from count_cursor into @.qid, @.fullstring
end

close count_cursor
deallocate count_cursor|||At least lashes with a wet noodle aren't fatal. Go ahead and whip me now.

Dirk|||Just add a print @.qid where you print @.counter|||Hang on big boy I don't do that for just anyone!! ;)

If a solution is not posted by tomorrow morning I will take a look at it.|||Originally posted by rnealejr
Just add a print @.qid where you print @.counter

Actually I tried this already and it didn't give me a two column result. I wanted @.QID in column1 and @.COUNTER in column 2. It jumbled everything together in a single column.

I tried PRINT @.QID, @.COUNTER which gave me an error so I added a second print line that gave the above results.

Syntax gets me every time.
Dirk|||Paul - He can't use the function since he is using ss7.

So in order for you to loop through all the records you will have to use a cursor - functions are a huge benefit in ss2k.

Print is strictly used to display back to the user - not displaying data like you see when you do a sql select statement. You can create a temporary table to store the values then do a select * from #table. If you had ss2k, you could use a table data type.|||Try the following - I put '***' before the statements I added:

CREATE procedure [dbo].[countdelim]
as
declare @.qid int
declare @.fullstring varchar (100)

***create table #hold(someid int, counter int)
-- create the temporary table - modify based on what you need to store

declare count_cursor cursor

for select QuestionID, fulltexttoparse from Table1

open count_cursor
fetch next from count_cursor into @.qid, @.fullstring
while (@.@.fetch_status = 0)
begin
--set @.qid=(select QuestionID from Table1)
declare @.foundAt int
declare @.counter int
declare @.compareString varchar(50)
set @.compareString=@.fullstring
set @.counter=0
select
--@.qid,
@.foundAt=charindex('|',@.compareString)
--from Table1
if (@.foundAt<>0) set @.counter=@.counter+1

while @.foundAt>0
begin
select @.foundAt=charindex('|',@.compareString,@.foundAt+1)
if (@.foundAt<>0) set @.counter=@.counter+1
end
print @.counter
***insert into #hold values (@.qid, @.counter)

fetch next from count_cursor into @.qid, @.fullstring
end
***select * from #hold
***drop table #hold

close count_cursor
deallocate count_cursor|||1. I DONT LIKE CURSORS AT ALL!

--creating accel counter table (faster then inline)
if object_id('dbo.ufn_SmallintSequence1_IC') is not null drop function dbo.ufn_SmallintSequence1_IC
GO
create function dbo.ufn_SmallintSequence1_IC(@.EndNumber smallint)
returns @.table table (Number smallint primary key clustered) as begin
insert @.table(Number)
select Number=convert(smallint,( X0.X + 4*X1.X + 16*X2.X + 64*X3.X + 256*X4.X + 1024*X5.X + 4096*X6.X + 16384*X7.X+1 ))
from ( select 0 as X union all select 1 union all select 2 union all select 3 ) X0 --4
cross join ( select 0 as X union all select 1 union all select 2 union all select 3 ) X1 --16
cross join ( select 0 as X union all select 1 union all select 2 union all select 3 ) X2 --64
cross join ( select 0 as X union all select 1 union all select 2 union all select 3 ) X3 --256
cross join ( select 0 as X union all select 1 union all select 2 union all select 3 ) X4 --1024
cross join ( select 0 as X union all select 1 union all select 2 union all select 3 ) X5 --4096
cross join ( select 0 as X union all select 1 union all select 2 union all select 3 ) X6 --16384
cross join ( select 0 as X union all select 1 ) X7 --32768
where convert(smallint,( X0.X + 4*X1.X + 16*X2.X + 64*X3.X + 256*X4.X + 1024*X5.X + 4096*X6.X + 16384*X7.X ))<@.EndNumber
RETURN
end
GO
--SINGLE QUARY TEMPLATE( cross join is fastest - I wonder)
select yt.YourIdCol,sum(case when charindex('|',yt.YourCol,c.Number)>0 then 1 else 0 end)
from YourTable yt
cross join dbo.ufn_SmallintSequence1(8000) c
group by yt.YourIdCol

Maybe this helped...|||ispaleny - Yes, cursors are bad ... but as already mentioned, this is ss7 - so functions and table data types are of no use.|||I was speaking with God this night. She said: Dont be in range about cursors, use replace!

select len(YourCol)-len(replace(YourCol,'|',''))
from YourTable yt|||You have got to love the simplicity - beautiful.|||RE:

I was speaking with God this night. She said: Dont be in range about cursors, use replace!

select len(YourCol)-len(replace(YourCol,'|',''))
from YourTable yt

Resplendent :)|||Originally posted by tnjazzgrass
I'm not really sure why he wants to count them. Our web guy has this application and he asked me if I could help him figure this out. Everyone else he's asked told him it couldn't be done, but I disagree. I just don't know how to do it easily. I know how I could probably accomplish it in an unconventional and cumbersome manner using patindex/charindex and some math, but I was hoping there was a simple way.

The field contains answers to questions in a survey, and each answer is delimited by a pipe. Not sure why the determination to store them as an array was made either.

Dirk|||The only restriction is that the original post indicated that the platform was SQL 7. SQL 7 does not support UDFs.

Can you modify the table to add a field and store the number of values in the field?

When the values are inserted, the developer can use the Split function and UBound to determine the number of values.

Regards,

Hugh Scott

Originally posted by Paul Young
I would wrap all of this into a function that accecpts the "@.compareString" and the delimiter, the return would be the number of delimiters.

try this...
-- =============================================
-- Create scalar function (FN)
-- =============================================
IF EXISTS (SELECT * FROM sysobjects WHERE name = N'CountDelimiter')
DROP FUNCTION CountDelimiter
GO

CREATE FUNCTION CountDelimiter(
@.compareString varchar(50),
@.delimiter varchar(1))
RETURNS int
AS
BEGIN
declare @.foundAt int, @.counter int
set @.counter=0
set @.foundAt=charindex(@.delimiter,@.compareString)
if (@.foundAt<>0) set @.counter=@.counter+1
while @.foundAt>0 begin
select @.foundAt=charindex(@.delimiter,@.compareString,@.foun dAt+1)
if (@.foundAt<>0) set @.counter=@.counter+1
end
return @.counter
END
GO

-- =============================================
-- Example to execute function
-- =============================================
SELECT dbo.CountDelimiter('I|was|here|today|','|')
GO

now you can use this in a select statment or sp.|||true, but I think ispaleny supplied the ultimate answer.|||Originally posted by ispaleny
I was speaking with God this night. She said: Dont be in range about cursors, use replace!

select len(YourCol)-len(replace(YourCol,'|',''))
from YourTable yt

Unbelievable. It's amazing how the simplest solutions can sit right under your nose and you still take the long way around. I just showed this to our web programmer and he fell out of his chair.

Thanks everyone!
Dirk

amount of Records SQL 2005 can handle

Hi.
What are the limitations of SQL 2005? I mean the number of tables,
relationships, mappings within the relationships and number of
records?
we have a customer with 64 bit AMD server used as the CRM databse
server.However, the database server is performing absolutely bad in
the recent days.
The CRM server is 32 bit.
Questions:
1- is there a problem of the read/write/create of 64 bit and 32 bit
server entegration?
2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
3- What are the limitations? I mean if there are about 300 tables,
total of 900 relationships in the database and about 3 million
records, would it do bad to the system?
ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
is used by SQL where the CPU is about 90%. the system is running at
more than 60% CPU and a few times a day it reaches 100% and the system
cannot work.
Check out "Maximum Capacity Specifications" in the BOL. However, this
sounds more like an application issue. You may want to use the profiler to
find out which queries are giving you grief and troubleshoot those.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"aduvv" <erdemerdem1797@.gmail.com> wrote in message
news:1181395208.873615.83570@.n4g2000hsb.googlegrou ps.com...
Hi.
What are the limitations of SQL 2005? I mean the number of tables,
relationships, mappings within the relationships and number of
records?
we have a customer with 64 bit AMD server used as the CRM databse
server.However, the database server is performing absolutely bad in
the recent days.
The CRM server is 32 bit.
Questions:
1- is there a problem of the read/write/create of 64 bit and 32 bit
server entegration?
2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
3- What are the limitations? I mean if there are about 300 tables,
total of 900 relationships in the database and about 3 million
records, would it do bad to the system?
ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
is used by SQL where the CPU is about 90%. the system is running at
more than 60% CPU and a few times a day it reaches 100% and the system
cannot work.
|||Hi
"aduvv" wrote:

> Hi.
> What are the limitations of SQL 2005? I mean the number of tables,
> relationships, mappings within the relationships and number of
> records?
> we have a customer with 64 bit AMD server used as the CRM databse
> server.However, the database server is performing absolutely bad in
> the recent days.
> The CRM server is 32 bit.
> Questions:
> 1- is there a problem of the read/write/create of 64 bit and 32 bit
> server entegration?
> 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> 3- What are the limitations? I mean if there are about 300 tables,
> total of 900 relationships in the database and about 3 million
> records, would it do bad to the system?
> ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> is used by SQL where the CPU is about 90%. the system is running at
> more than 60% CPU and a few times a day it reaches 100% and the system
> cannot work.
>
As well as Tom's comments..
It is not clear if SQL Server is the 32 bit edition or 64 bit version on
your system. It is obviously better for a 64bit OS to run a 64 bit version of
SQL Server. Use the query SELECT @.@.VERSION to determine this.
John
|||what's appends at the disk level? do you see high activity?
do you have other activities at the same time? (like backup, or other
scheduled tasks)
do you update your statistics at a regular basis? and/or defrag the indexes?
what is the disk subsystem?
how many disks are dedicated for the data files, how many for the log files
and how many for tempdb?
have you identify the queries which cause the issue?
for your questions:
1. there is no issue, I have some x64 and x32 servers which works fine in
any scenario
2. AMD cpu provides excellent performance for SQL Server
3. check the BOL for the limits, but I think you are far away from the
maximum capacity. We have a database with 2000 tables. and the biggest
tables contains around 10 million of rows.
"aduvv" <erdemerdem1797@.gmail.com> wrote in message
news:1181395208.873615.83570@.n4g2000hsb.googlegrou ps.com...
> Hi.
> What are the limitations of SQL 2005? I mean the number of tables,
> relationships, mappings within the relationships and number of
> records?
> we have a customer with 64 bit AMD server used as the CRM databse
> server.However, the database server is performing absolutely bad in
> the recent days.
> The CRM server is 32 bit.
> Questions:
> 1- is there a problem of the read/write/create of 64 bit and 32 bit
> server entegration?
> 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> 3- What are the limitations? I mean if there are about 300 tables,
> total of 900 relationships in the database and about 3 million
> records, would it do bad to the system?
> ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> is used by SQL where the CPU is about 90%. the system is running at
> more than 60% CPU and a few times a day it reaches 100% and the system
> cannot work.
>
|||Hello
As someone else on here has mentioned, run the SQL profiling tools,
SQL is very efficient at adding / view data. The problems start to
occur with large tables that have no indexes, requiring SQL server to
start scanning tables every time it requests a record.
Missing Indexes will exponentially slow down a database and so will
poorly written code that doesn't take advantage of SQL server
features.
The databases I work with are in the hundreds of Gigabytes, with
tables also in the 10's millions rows, constantly being added and the
performance is fast on very standard hardware (dual core, 4 gb ram
etc) - the only times I see SQL become very busy is when the code
talking to SQL is inefficient :-)
The actual limit for SQL 64 is so huge you won't get there in the next
5 years :-) and if you search google for SQL VLDB information you can
find SQL scales to very large organisations / data warehouses.
On Jun 9, 2:20 pm, aduvv <erdemerdem1...@.gmail.com> wrote:
> Hi.
> What are the limitations of SQL 2005? I mean the number of tables,
> relationships, mappings within the relationships and number of
> records?
> we have a customer with 64 bit AMD server used as the CRM databse
> server.However, the database server is performing absolutely bad in
> the recent days.
> The CRM server is 32 bit.
> Questions:
> 1- is there a problem of the read/write/create of 64 bit and 32 bit
> server entegration?
> 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> 3- What are the limitations? I mean if there are about 300 tables,
> total of 900 relationships in the database and about 3 million
> records, would it do bad to the system?
> ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> is used by SQL where the CPU is about 90%. the system is running at
> more than 60% CPU and a few times a day it reaches 100% and the system
> cannot work.
|||Hi. Thank you for the comments.
As in our case, the Database server is 64 bit and SQL is 64 bit as
well. I forgot to mention is before.
The Indexes are working fine as well. The ndexing job is working
every night and putting things in order.
The problem is in CRM actually. When people try to make a search on
CRM they wait for a serious amount of time.
When the query is run from the databse, it takes nearly no time, less
than 1 second.
but when CRM started to slow down, there was no reason. The only
difference is that we were migrating data into the system.
2 million rows have been migrated in 1 night I think. can this have an
effect?
When the data migration started, system started to slow down. Can you
make a comment on tthis?
On Jun 10, 10:58 am, "mlbro...@.googlemail.com"
<mlbro...@.googlemail.com> wrote:[vbcol=seagreen]
> Hello
> As someone else on here has mentioned, run the SQL profiling tools,
> SQL is very efficient at adding / view data. The problems start to
> occur with large tables that have no indexes, requiring SQL server to
> start scanning tables every time it requests a record.
> Missing Indexes will exponentially slow down a database and so will
> poorly written code that doesn't take advantage of SQL server
> features.
> The databases I work with are in the hundreds of Gigabytes, with
> tables also in the 10's millions rows, constantly being added and the
> performance is fast on very standard hardware (dual core, 4 gb ram
> etc) - the only times I see SQL become very busy is when the code
> talking to SQL is inefficient :-)
> The actual limit for SQL 64 is so huge you won't get there in the next
> 5 years :-) and if you search google for SQL VLDB information you can
> find SQL scales to very large organisations / data warehouses.
> On Jun 9, 2:20 pm, aduvv <erdemerdem1...@.gmail.com> wrote:
>
>
>
|||It could be that you are using a bulk insert and have used the TABLELOCK
option, which - as its name implies - locks the table. Thus, your users
can't get at it. This is an application design problem, not a SQL Server
problem.
Again, you may want to use the profiler to localize the problem.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"aduvv" <erdemerdem1797@.gmail.com> wrote in message
news:1181495227.503420.169040@.w5g2000hsg.googlegro ups.com...
Hi. Thank you for the comments.
As in our case, the Database server is 64 bit and SQL is 64 bit as
well. I forgot to mention is before.
The Indexes are working fine as well. The ndexing job is working
every night and putting things in order.
The problem is in CRM actually. When people try to make a search on
CRM they wait for a serious amount of time.
When the query is run from the databse, it takes nearly no time, less
than 1 second.
but when CRM started to slow down, there was no reason. The only
difference is that we were migrating data into the system.
2 million rows have been migrated in 1 night I think. can this have an
effect?
When the data migration started, system started to slow down. Can you
make a comment on tthis?
On Jun 10, 10:58 am, "mlbro...@.googlemail.com"
<mlbro...@.googlemail.com> wrote:[vbcol=seagreen]
> Hello
> As someone else on here has mentioned, run the SQL profiling tools,
> SQL is very efficient at adding / view data. The problems start to
> occur with large tables that have no indexes, requiring SQL server to
> start scanning tables every time it requests a record.
> Missing Indexes will exponentially slow down a database and so will
> poorly written code that doesn't take advantage of SQL server
> features.
> The databases I work with are in the hundreds of Gigabytes, with
> tables also in the 10's millions rows, constantly being added and the
> performance is fast on very standard hardware (dual core, 4 gb ram
> etc) - the only times I see SQL become very busy is when the code
> talking to SQL is inefficient :-)
> The actual limit for SQL 64 is so huge you won't get there in the next
> 5 years :-) and if you search google for SQL VLDB information you can
> find SQL scales to very large organisations / data warehouses.
> On Jun 9, 2:20 pm, aduvv <erdemerdem1...@.gmail.com> wrote:
>
>
>
|||so the issue appear during the insert of the 2 millions of records.
how do you migrate the data?
do you use the bulk insert method or row by row insert?
your users probably suffer locking issue.
what is the disk system? have setup your log files on a dedicated set of
disks?
"aduvv" <erdemerdem1797@.gmail.com> wrote in message
news:1181495227.503420.169040@.w5g2000hsg.googlegro ups.com...
> Hi. Thank you for the comments.
> As in our case, the Database server is 64 bit and SQL is 64 bit as
> well. I forgot to mention is before.
> The Indexes are working fine as well. The ndexing job is working
> every night and putting things in order.
> The problem is in CRM actually. When people try to make a search on
> CRM they wait for a serious amount of time.
> When the query is run from the databse, it takes nearly no time, less
> than 1 second.
> but when CRM started to slow down, there was no reason. The only
> difference is that we were migrating data into the system.
> 2 million rows have been migrated in 1 night I think. can this have an
> effect?
> When the data migration started, system started to slow down. Can you
> make a comment on tthis?
> On Jun 10, 10:58 am, "mlbro...@.googlemail.com"
> <mlbro...@.googlemail.com> wrote:
>
>
>
|||On Jun 9, 6:20 am, aduvv <erdemerdem1...@.gmail.com> wrote:
> Hi.
> What are the limitations of SQL 2005? I mean the number of tables,
> relationships, mappings within the relationships and number of
> records?
> we have a customer with 64 bit AMD server used as the CRM databse
> server.However, the database server is performing absolutely bad in
> the recent days.
> The CRM server is 32 bit.
> Questions:
> 1- is there a problem of the read/write/create of 64 bit and 32 bit
> server entegration?
> 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> 3- What are the limitations? I mean if there are about 300 tables,
> total of 900 relationships in the database and about 3 million
> records, would it do bad to the system?
> ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> is used by SQL where the CPU is about 90%. the system is running at
> more than 60% CPU and a few times a day it reaches 100% and the system
> cannot work.
How to see the SQL SERVER status: http://www.sqlhacks.com/faqs/simple_monitoring
SELECT @.@.CONNECTIONS AS 'Connections', @.@.CPU_BUSY AS '% usage',
@.@.ERROR AS 'Error',
@.@.IO_BUSY AS 'I/O', @.@.LANGUAGE AS 'Language', @.@.LOCK_TIMEOUT AS 'Lock
timeout',
@.@.MAX_CONNECTIONS AS 'Max Connections', @.@.MAX_PRECISION AS
'Precision',
@.@.PACK_RECEIVED AS 'Packet received', @.@.PACK_SENT AS 'Packets Sent',
@.@.PACKET_ERRORS AS 'Packet Errors', @.@.SERVERNAME AS 'Server',
@.@.SERVICENAME AS 'Services', @.@.TOTAL_ERRORS AS 'Errors',
@.@.TOTAL_READ AS 'Reads', @.@.TOTAL_WRITE AS 'Writes', @.@.VERSION AS
'Version';
This includes samples and explanations on how to do it.
Also new this week:
SQL Server index performance
SQL Server - optimization:index performance
How to group items into a fixed number of bucket with MS SQL Server
How to have a simple server monitoring in MS SQL Server
What's the current version of MS SQL Server used?
What are all the triggers used in a database - Formatting syv
What are all the views in a database in MS SQL Server?
What are all the stored procedures in a database in MS SQL Server? -
Formatting syv
What's the structure of a table with MS SQL Server?

amount of Records SQL 2005 can handle

Hi.
What are the limitations of SQL 2005? I mean the number of tables,
relationships, mappings within the relationships and number of
records?
we have a customer with 64 bit AMD server used as the CRM databse
server.However, the database server is performing absolutely bad in
the recent days.
The CRM server is 32 bit.
Questions:
1- is there a problem of the read/write/create of 64 bit and 32 bit
server entegration?
2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
3- What are the limitations? I mean if there are about 300 tables,
total of 900 relationships in the database and about 3 million
records, would it do bad to the system?
ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
is used by SQL where the CPU is about 90%. the system is running at
more than 60% CPU and a few times a day it reaches 100% and the system
cannot work.Check out "Maximum Capacity Specifications" in the BOL. However, this
sounds more like an application issue. You may want to use the profiler to
find out which queries are giving you grief and troubleshoot those.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"aduvv" <erdemerdem1797@.gmail.com> wrote in message
news:1181395208.873615.83570@.n4g2000hsb.googlegroups.com...
Hi.
What are the limitations of SQL 2005? I mean the number of tables,
relationships, mappings within the relationships and number of
records?
we have a customer with 64 bit AMD server used as the CRM databse
server.However, the database server is performing absolutely bad in
the recent days.
The CRM server is 32 bit.
Questions:
1- is there a problem of the read/write/create of 64 bit and 32 bit
server entegration?
2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
3- What are the limitations? I mean if there are about 300 tables,
total of 900 relationships in the database and about 3 million
records, would it do bad to the system?
ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
is used by SQL where the CPU is about 90%. the system is running at
more than 60% CPU and a few times a day it reaches 100% and the system
cannot work.|||Hi
"aduvv" wrote:

> Hi.
> What are the limitations of SQL 2005? I mean the number of tables,
> relationships, mappings within the relationships and number of
> records?
> we have a customer with 64 bit AMD server used as the CRM databse
> server.However, the database server is performing absolutely bad in
> the recent days.
> The CRM server is 32 bit.
> Questions:
> 1- is there a problem of the read/write/create of 64 bit and 32 bit
> server entegration?
> 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> 3- What are the limitations? I mean if there are about 300 tables,
> total of 900 relationships in the database and about 3 million
> records, would it do bad to the system?
> ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> is used by SQL where the CPU is about 90%. the system is running at
> more than 60% CPU and a few times a day it reaches 100% and the system
> cannot work.
>
As well as Tom's comments..
It is not clear if SQL Server is the 32 bit edition or 64 bit version on
your system. It is obviously better for a 64bit OS to run a 64 bit version o
f
SQL Server. Use the query SELECT @.@.VERSION to determine this.
John|||what's appends at the disk level? do you see high activity?
do you have other activities at the same time? (like backup, or other
scheduled tasks)
do you update your statistics at a regular basis? and/or defrag the indexes?
what is the disk subsystem?
how many disks are dedicated for the data files, how many for the log files
and how many for tempdb?
have you identify the queries which cause the issue?
for your questions:
1. there is no issue, I have some x64 and x32 servers which works fine in
any scenario
2. AMD cpu provides excellent performance for SQL Server
3. check the BOL for the limits, but I think you are far away from the
maximum capacity. We have a database with 2000 tables. and the biggest
tables contains around 10 million of rows.
"aduvv" <erdemerdem1797@.gmail.com> wrote in message
news:1181395208.873615.83570@.n4g2000hsb.googlegroups.com...
> Hi.
> What are the limitations of SQL 2005? I mean the number of tables,
> relationships, mappings within the relationships and number of
> records?
> we have a customer with 64 bit AMD server used as the CRM databse
> server.However, the database server is performing absolutely bad in
> the recent days.
> The CRM server is 32 bit.
> Questions:
> 1- is there a problem of the read/write/create of 64 bit and 32 bit
> server entegration?
> 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> 3- What are the limitations? I mean if there are about 300 tables,
> total of 900 relationships in the database and about 3 million
> records, would it do bad to the system?
> ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> is used by SQL where the CPU is about 90%. the system is running at
> more than 60% CPU and a few times a day it reaches 100% and the system
> cannot work.
>|||Hello
As someone else on here has mentioned, run the SQL profiling tools,
SQL is very efficient at adding / view data. The problems start to
occur with large tables that have no indexes, requiring SQL server to
start scanning tables every time it requests a record.
Missing Indexes will exponentially slow down a database and so will
poorly written code that doesn't take advantage of SQL server
features.
The databases I work with are in the hundreds of Gigabytes, with
tables also in the 10's millions rows, constantly being added and the
performance is fast on very standard hardware (dual core, 4 gb ram
etc) - the only times I see SQL become very busy is when the code
talking to SQL is inefficient :-)
The actual limit for SQL 64 is so huge you won't get there in the next
5 years :-) and if you search google for SQL VLDB information you can
find SQL scales to very large organisations / data warehouses.
On Jun 9, 2:20 pm, aduvv <erdemerdem1...@.gmail.com> wrote:
> Hi.
> What are the limitations of SQL 2005? I mean the number of tables,
> relationships, mappings within the relationships and number of
> records?
> we have a customer with 64 bit AMD server used as the CRM databse
> server.However, the database server is performing absolutely bad in
> the recent days.
> The CRM server is 32 bit.
> Questions:
> 1- is there a problem of the read/write/create of 64 bit and 32 bit
> server entegration?
> 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> 3- What are the limitations? I mean if there are about 300 tables,
> total of 900 relationships in the database and about 3 million
> records, would it do bad to the system?
> ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> is used by SQL where the CPU is about 90%. the system is running at
> more than 60% CPU and a few times a day it reaches 100% and the system
> cannot work.|||Hi. Thank you for the comments.
As in our case, the Database server is 64 bit and SQL is 64 bit as
well. I forgot to mention is before.
The Indexes are working fine as well. The ndexing job is working
every night and putting things in order.
The problem is in CRM actually. When people try to make a search on
CRM they wait for a serious amount of time.
When the query is run from the databse, it takes nearly no time, less
than 1 second.
but when CRM started to slow down, there was no reason. The only
difference is that we were migrating data into the system.
2 million rows have been migrated in 1 night I think. can this have an
effect?
When the data migration started, system started to slow down. Can you
make a comment on tthis?
On Jun 10, 10:58 am, "mlbro...@.googlemail.com"
<mlbro...@.googlemail.com> wrote:[vbcol=seagreen]
> Hello
> As someone else on here has mentioned, run the SQL profiling tools,
> SQL is very efficient at adding / view data. The problems start to
> occur with large tables that have no indexes, requiring SQL server to
> start scanning tables every time it requests a record.
> Missing Indexes will exponentially slow down a database and so will
> poorly written code that doesn't take advantage of SQL server
> features.
> The databases I work with are in the hundreds of Gigabytes, with
> tables also in the 10's millions rows, constantly being added and the
> performance is fast on very standard hardware (dual core, 4 gb ram
> etc) - the only times I see SQL become very busy is when the code
> talking to SQL is inefficient :-)
> The actual limit for SQL 64 is so huge you won't get there in the next
> 5 years :-) and if you search google for SQL VLDB information you can
> find SQL scales to very large organisations / data warehouses.
> On Jun 9, 2:20 pm, aduvv <erdemerdem1...@.gmail.com> wrote:
>
>
>
>
>
>|||It could be that you are using a bulk insert and have used the TABLELOCK
option, which - as its name implies - locks the table. Thus, your users
can't get at it. This is an application design problem, not a SQL Server
problem.
Again, you may want to use the profiler to localize the problem.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"aduvv" <erdemerdem1797@.gmail.com> wrote in message
news:1181495227.503420.169040@.w5g2000hsg.googlegroups.com...
Hi. Thank you for the comments.
As in our case, the Database server is 64 bit and SQL is 64 bit as
well. I forgot to mention is before.
The Indexes are working fine as well. The ndexing job is working
every night and putting things in order.
The problem is in CRM actually. When people try to make a search on
CRM they wait for a serious amount of time.
When the query is run from the databse, it takes nearly no time, less
than 1 second.
but when CRM started to slow down, there was no reason. The only
difference is that we were migrating data into the system.
2 million rows have been migrated in 1 night I think. can this have an
effect?
When the data migration started, system started to slow down. Can you
make a comment on tthis?
On Jun 10, 10:58 am, "mlbro...@.googlemail.com"
<mlbro...@.googlemail.com> wrote:[vbcol=seagreen]
> Hello
> As someone else on here has mentioned, run the SQL profiling tools,
> SQL is very efficient at adding / view data. The problems start to
> occur with large tables that have no indexes, requiring SQL server to
> start scanning tables every time it requests a record.
> Missing Indexes will exponentially slow down a database and so will
> poorly written code that doesn't take advantage of SQL server
> features.
> The databases I work with are in the hundreds of Gigabytes, with
> tables also in the 10's millions rows, constantly being added and the
> performance is fast on very standard hardware (dual core, 4 gb ram
> etc) - the only times I see SQL become very busy is when the code
> talking to SQL is inefficient :-)
> The actual limit for SQL 64 is so huge you won't get there in the next
> 5 years :-) and if you search google for SQL VLDB information you can
> find SQL scales to very large organisations / data warehouses.
> On Jun 9, 2:20 pm, aduvv <erdemerdem1...@.gmail.com> wrote:
>
>
>
>
>
>|||so the issue appear during the insert of the 2 millions of records.
how do you migrate the data?
do you use the bulk insert method or row by row insert?
your users probably suffer locking issue.
what is the disk system? have setup your log files on a dedicated set of
disks?
"aduvv" <erdemerdem1797@.gmail.com> wrote in message
news:1181495227.503420.169040@.w5g2000hsg.googlegroups.com...
> Hi. Thank you for the comments.
> As in our case, the Database server is 64 bit and SQL is 64 bit as
> well. I forgot to mention is before.
> The Indexes are working fine as well. The ndexing job is working
> every night and putting things in order.
> The problem is in CRM actually. When people try to make a search on
> CRM they wait for a serious amount of time.
> When the query is run from the databse, it takes nearly no time, less
> than 1 second.
> but when CRM started to slow down, there was no reason. The only
> difference is that we were migrating data into the system.
> 2 million rows have been migrated in 1 night I think. can this have an
> effect?
> When the data migration started, system started to slow down. Can you
> make a comment on tthis?
> On Jun 10, 10:58 am, "mlbro...@.googlemail.com"
> <mlbro...@.googlemail.com> wrote:
>
>
>|||On Jun 9, 6:20 am, aduvv <erdemerdem1...@.gmail.com> wrote:
> Hi.
> What are the limitations of SQL 2005? I mean the number of tables,
> relationships, mappings within the relationships and number of
> records?
> we have a customer with 64 bit AMD server used as the CRM databse
> server.However, the database server is performing absolutely bad in
> the recent days.
> The CRM server is 32 bit.
> Questions:
> 1- is there a problem of the read/write/create of 64 bit and 32 bit
> server entegration?
> 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> 3- What are the limitations? I mean if there are about 300 tables,
> total of 900 relationships in the database and about 3 million
> records, would it do bad to the system?
> ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> is used by SQL where the CPU is about 90%. the system is running at
> more than 60% CPU and a few times a day it reaches 100% and the system
> cannot work.
How to see the SQL SERVER status: [url]http://www.sqlhacks.com/faqs/simple_monitoring[/
url]
SELECT @.@.CONNECTIONS AS 'Connections', @.@.CPU_BUSY AS '% usage',
@.@.ERROR AS 'Error',
@.@.IO_BUSY AS 'I/O', @.@.LANGUAGE AS 'Language', @.@.LOCK_TIMEOUT AS 'Lock
timeout',
@.@.MAX_CONNECTIONS AS 'Max Connections', @.@.MAX_PRECISION AS
'Precision',
@.@.PACK_RECEIVED AS 'Packet received', @.@.PACK_SENT AS 'Packets Sent',
@.@.PACKET_ERRORS AS 'Packet Errors', @.@.SERVERNAME AS 'Server',
@.@.SERVICENAME AS 'Services', @.@.TOTAL_ERRORS AS 'Errors',
@.@.TOTAL_READ AS 'Reads', @.@.TOTAL_WRITE AS 'Writes', @.@.VERSION AS
'Version';
This includes samples and explanations on how to do it.
Also new this week:
SQL Server index performance
SQL Server - optimization:index performance
How to group items into a fixed number of bucket with MS SQL Server
How to have a simple server monitoring in MS SQL Server
What's the current version of MS SQL Server used?
What are all the triggers used in a database - Formatting syv
What are all the views in a database in MS SQL Server?
What are all the stored procedures in a database in MS SQL Server? -
Formatting syv
What's the structure of a table with MS SQL Server?

amount of Records SQL 2005 can handle

Hi.
What are the limitations of SQL 2005? I mean the number of tables,
relationships, mappings within the relationships and number of
records?
we have a customer with 64 bit AMD server used as the CRM databse
server.However, the database server is performing absolutely bad in
the recent days.
The CRM server is 32 bit.
Questions:
1- is there a problem of the read/write/create of 64 bit and 32 bit
server entegration?
2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
3- What are the limitations? I mean if there are about 300 tables,
total of 900 relationships in the database and about 3 million
records, would it do bad to the system?
ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
is used by SQL where the CPU is about 90%. the system is running at
more than 60% CPU and a few times a day it reaches 100% and the system
cannot work.Check out "Maximum Capacity Specifications" in the BOL. However, this
sounds more like an application issue. You may want to use the profiler to
find out which queries are giving you grief and troubleshoot those.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"aduvv" <erdemerdem1797@.gmail.com> wrote in message
news:1181395208.873615.83570@.n4g2000hsb.googlegroups.com...
Hi.
What are the limitations of SQL 2005? I mean the number of tables,
relationships, mappings within the relationships and number of
records?
we have a customer with 64 bit AMD server used as the CRM databse
server.However, the database server is performing absolutely bad in
the recent days.
The CRM server is 32 bit.
Questions:
1- is there a problem of the read/write/create of 64 bit and 32 bit
server entegration?
2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
3- What are the limitations? I mean if there are about 300 tables,
total of 900 relationships in the database and about 3 million
records, would it do bad to the system?
ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
is used by SQL where the CPU is about 90%. the system is running at
more than 60% CPU and a few times a day it reaches 100% and the system
cannot work.|||Hi
"aduvv" wrote:
> Hi.
> What are the limitations of SQL 2005? I mean the number of tables,
> relationships, mappings within the relationships and number of
> records?
> we have a customer with 64 bit AMD server used as the CRM databse
> server.However, the database server is performing absolutely bad in
> the recent days.
> The CRM server is 32 bit.
> Questions:
> 1- is there a problem of the read/write/create of 64 bit and 32 bit
> server entegration?
> 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> 3- What are the limitations? I mean if there are about 300 tables,
> total of 900 relationships in the database and about 3 million
> records, would it do bad to the system?
> ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> is used by SQL where the CPU is about 90%. the system is running at
> more than 60% CPU and a few times a day it reaches 100% and the system
> cannot work.
>
As well as Tom's comments..
It is not clear if SQL Server is the 32 bit edition or 64 bit version on
your system. It is obviously better for a 64bit OS to run a 64 bit version of
SQL Server. Use the query SELECT @.@.VERSION to determine this.
John|||what's appends at the disk level? do you see high activity?
do you have other activities at the same time? (like backup, or other
scheduled tasks)
do you update your statistics at a regular basis? and/or defrag the indexes?
what is the disk subsystem?
how many disks are dedicated for the data files, how many for the log files
and how many for tempdb?
have you identify the queries which cause the issue?
for your questions:
1. there is no issue, I have some x64 and x32 servers which works fine in
any scenario
2. AMD cpu provides excellent performance for SQL Server
3. check the BOL for the limits, but I think you are far away from the
maximum capacity. We have a database with 2000 tables. and the biggest
tables contains around 10 million of rows.
"aduvv" <erdemerdem1797@.gmail.com> wrote in message
news:1181395208.873615.83570@.n4g2000hsb.googlegroups.com...
> Hi.
> What are the limitations of SQL 2005? I mean the number of tables,
> relationships, mappings within the relationships and number of
> records?
> we have a customer with 64 bit AMD server used as the CRM databse
> server.However, the database server is performing absolutely bad in
> the recent days.
> The CRM server is 32 bit.
> Questions:
> 1- is there a problem of the read/write/create of 64 bit and 32 bit
> server entegration?
> 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> 3- What are the limitations? I mean if there are about 300 tables,
> total of 900 relationships in the database and about 3 million
> records, would it do bad to the system?
> ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> is used by SQL where the CPU is about 90%. the system is running at
> more than 60% CPU and a few times a day it reaches 100% and the system
> cannot work.
>|||Hello
As someone else on here has mentioned, run the SQL profiling tools,
SQL is very efficient at adding / view data. The problems start to
occur with large tables that have no indexes, requiring SQL server to
start scanning tables every time it requests a record.
Missing Indexes will exponentially slow down a database and so will
poorly written code that doesn't take advantage of SQL server
features.
The databases I work with are in the hundreds of Gigabytes, with
tables also in the 10's millions rows, constantly being added and the
performance is fast on very standard hardware (dual core, 4 gb ram
etc) - the only times I see SQL become very busy is when the code
talking to SQL is inefficient :-)
The actual limit for SQL 64 is so huge you won't get there in the next
5 years :-) and if you search google for SQL VLDB information you can
find SQL scales to very large organisations / data warehouses.
On Jun 9, 2:20 pm, aduvv <erdemerdem1...@.gmail.com> wrote:
> Hi.
> What are the limitations of SQL 2005? I mean the number of tables,
> relationships, mappings within the relationships and number of
> records?
> we have a customer with 64 bit AMD server used as the CRM databse
> server.However, the database server is performing absolutely bad in
> the recent days.
> The CRM server is 32 bit.
> Questions:
> 1- is there a problem of the read/write/create of 64 bit and 32 bit
> server entegration?
> 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> 3- What are the limitations? I mean if there are about 300 tables,
> total of 900 relationships in the database and about 3 million
> records, would it do bad to the system?
> ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> is used by SQL where the CPU is about 90%. the system is running at
> more than 60% CPU and a few times a day it reaches 100% and the system
> cannot work.|||Hi. Thank you for the comments.
As in our case, the Database server is 64 bit and SQL is 64 bit as
well. I forgot to mention is before.
The Indexes are working fine as well. The ndexing job is working
every night and putting things in order.
The problem is in CRM actually. When people try to make a search on
CRM they wait for a serious amount of time.
When the query is run from the databse, it takes nearly no time, less
than 1 second.
but when CRM started to slow down, there was no reason. The only
difference is that we were migrating data into the system.
2 million rows have been migrated in 1 night I think. can this have an
effect?
When the data migration started, system started to slow down. Can you
make a comment on tthis?
On Jun 10, 10:58 am, "mlbro...@.googlemail.com"
<mlbro...@.googlemail.com> wrote:
> Hello
> As someone else on here has mentioned, run the SQL profiling tools,
> SQL is very efficient at adding / view data. The problems start to
> occur with large tables that have no indexes, requiring SQL server to
> start scanning tables every time it requests a record.
> Missing Indexes will exponentially slow down a database and so will
> poorly written code that doesn't take advantage of SQL server
> features.
> The databases I work with are in the hundreds of Gigabytes, with
> tables also in the 10's millions rows, constantly being added and the
> performance is fast on very standard hardware (dual core, 4 gb ram
> etc) - the only times I see SQL become very busy is when the code
> talking to SQL is inefficient :-)
> The actual limit for SQL 64 is so huge you won't get there in the next
> 5 years :-) and if you search google for SQL VLDB information you can
> find SQL scales to very large organisations / data warehouses.
> On Jun 9, 2:20 pm, aduvv <erdemerdem1...@.gmail.com> wrote:
> > Hi.
> > What are the limitations of SQL 2005? I mean the number of tables,
> > relationships, mappings within the relationships and number of
> > records?
> > we have a customer with 64 bit AMD server used as the CRM databse
> > server.However, the database server is performing absolutely bad in
> > the recent days.
> > The CRM server is 32 bit.
> > Questions:
> > 1- is there a problem of the read/write/create of 64 bit and 32 bit
> > server entegration?
> > 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> > 3- What are the limitations? I mean if there are about 300 tables,
> > total of 900 relationships in the database and about 3 million
> > records, would it do bad to the system?
> > ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> > is used by SQL where the CPU is about 90%. the system is running at
> > more than 60% CPU and a few times a day it reaches 100% and the system
> > cannot work.|||It could be that you are using a bulk insert and have used the TABLELOCK
option, which - as its name implies - locks the table. Thus, your users
can't get at it. This is an application design problem, not a SQL Server
problem.
Again, you may want to use the profiler to localize the problem.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"aduvv" <erdemerdem1797@.gmail.com> wrote in message
news:1181495227.503420.169040@.w5g2000hsg.googlegroups.com...
Hi. Thank you for the comments.
As in our case, the Database server is 64 bit and SQL is 64 bit as
well. I forgot to mention is before.
The Indexes are working fine as well. The ndexing job is working
every night and putting things in order.
The problem is in CRM actually. When people try to make a search on
CRM they wait for a serious amount of time.
When the query is run from the databse, it takes nearly no time, less
than 1 second.
but when CRM started to slow down, there was no reason. The only
difference is that we were migrating data into the system.
2 million rows have been migrated in 1 night I think. can this have an
effect?
When the data migration started, system started to slow down. Can you
make a comment on tthis?
On Jun 10, 10:58 am, "mlbro...@.googlemail.com"
<mlbro...@.googlemail.com> wrote:
> Hello
> As someone else on here has mentioned, run the SQL profiling tools,
> SQL is very efficient at adding / view data. The problems start to
> occur with large tables that have no indexes, requiring SQL server to
> start scanning tables every time it requests a record.
> Missing Indexes will exponentially slow down a database and so will
> poorly written code that doesn't take advantage of SQL server
> features.
> The databases I work with are in the hundreds of Gigabytes, with
> tables also in the 10's millions rows, constantly being added and the
> performance is fast on very standard hardware (dual core, 4 gb ram
> etc) - the only times I see SQL become very busy is when the code
> talking to SQL is inefficient :-)
> The actual limit for SQL 64 is so huge you won't get there in the next
> 5 years :-) and if you search google for SQL VLDB information you can
> find SQL scales to very large organisations / data warehouses.
> On Jun 9, 2:20 pm, aduvv <erdemerdem1...@.gmail.com> wrote:
> > Hi.
> > What are the limitations of SQL 2005? I mean the number of tables,
> > relationships, mappings within the relationships and number of
> > records?
> > we have a customer with 64 bit AMD server used as the CRM databse
> > server.However, the database server is performing absolutely bad in
> > the recent days.
> > The CRM server is 32 bit.
> > Questions:
> > 1- is there a problem of the read/write/create of 64 bit and 32 bit
> > server entegration?
> > 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> > 3- What are the limitations? I mean if there are about 300 tables,
> > total of 900 relationships in the database and about 3 million
> > records, would it do bad to the system?
> > ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> > is used by SQL where the CPU is about 90%. the system is running at
> > more than 60% CPU and a few times a day it reaches 100% and the system
> > cannot work.|||so the issue appear during the insert of the 2 millions of records.
how do you migrate the data?
do you use the bulk insert method or row by row insert?
your users probably suffer locking issue.
what is the disk system? have setup your log files on a dedicated set of
disks?
"aduvv" <erdemerdem1797@.gmail.com> wrote in message
news:1181495227.503420.169040@.w5g2000hsg.googlegroups.com...
> Hi. Thank you for the comments.
> As in our case, the Database server is 64 bit and SQL is 64 bit as
> well. I forgot to mention is before.
> The Indexes are working fine as well. The ndexing job is working
> every night and putting things in order.
> The problem is in CRM actually. When people try to make a search on
> CRM they wait for a serious amount of time.
> When the query is run from the databse, it takes nearly no time, less
> than 1 second.
> but when CRM started to slow down, there was no reason. The only
> difference is that we were migrating data into the system.
> 2 million rows have been migrated in 1 night I think. can this have an
> effect?
> When the data migration started, system started to slow down. Can you
> make a comment on tthis?
> On Jun 10, 10:58 am, "mlbro...@.googlemail.com"
> <mlbro...@.googlemail.com> wrote:
>> Hello
>> As someone else on here has mentioned, run the SQL profiling tools,
>> SQL is very efficient at adding / view data. The problems start to
>> occur with large tables that have no indexes, requiring SQL server to
>> start scanning tables every time it requests a record.
>> Missing Indexes will exponentially slow down a database and so will
>> poorly written code that doesn't take advantage of SQL server
>> features.
>> The databases I work with are in the hundreds of Gigabytes, with
>> tables also in the 10's millions rows, constantly being added and the
>> performance is fast on very standard hardware (dual core, 4 gb ram
>> etc) - the only times I see SQL become very busy is when the code
>> talking to SQL is inefficient :-)
>> The actual limit for SQL 64 is so huge you won't get there in the next
>> 5 years :-) and if you search google for SQL VLDB information you can
>> find SQL scales to very large organisations / data warehouses.
>> On Jun 9, 2:20 pm, aduvv <erdemerdem1...@.gmail.com> wrote:
>> > Hi.
>> > What are the limitations of SQL 2005? I mean the number of tables,
>> > relationships, mappings within the relationships and number of
>> > records?
>> > we have a customer with 64 bit AMD server used as the CRM databse
>> > server.However, the database server is performing absolutely bad in
>> > the recent days.
>> > The CRM server is 32 bit.
>> > Questions:
>> > 1- is there a problem of the read/write/create of 64 bit and 32 bit
>> > server entegration?
>> > 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
>> > 3- What are the limitations? I mean if there are about 300 tables,
>> > total of 900 relationships in the database and about 3 million
>> > records, would it do bad to the system?
>> > ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
>> > is used by SQL where the CPU is about 90%. the system is running at
>> > more than 60% CPU and a few times a day it reaches 100% and the system
>> > cannot work.
>
>
>|||On Jun 9, 6:20 am, aduvv <erdemerdem1...@.gmail.com> wrote:
> Hi.
> What are the limitations of SQL 2005? I mean the number of tables,
> relationships, mappings within the relationships and number of
> records?
> we have a customer with 64 bit AMD server used as the CRM databse
> server.However, the database server is performing absolutely bad in
> the recent days.
> The CRM server is 32 bit.
> Questions:
> 1- is there a problem of the read/write/create of 64 bit and 32 bit
> server entegration?
> 2- What are the capabilities of the SQL 2005 in an AMD 64 bit server?
> 3- What are the limitations? I mean if there are about 300 tables,
> total of 900 relationships in the database and about 3 million
> records, would it do bad to the system?
> ps :the server has 8 GB ram. sometimes the server freezes, 7 GB of ram
> is used by SQL where the CPU is about 90%. the system is running at
> more than 60% CPU and a few times a day it reaches 100% and the system
> cannot work.
How to see the SQL SERVER status: http://www.sqlhacks.com/faqs/simple_monitoring
SELECT @.@.CONNECTIONS AS 'Connections', @.@.CPU_BUSY AS '% usage',
@.@.ERROR AS 'Error',
@.@.IO_BUSY AS 'I/O', @.@.LANGUAGE AS 'Language', @.@.LOCK_TIMEOUT AS 'Lock
timeout',
@.@.MAX_CONNECTIONS AS 'Max Connections', @.@.MAX_PRECISION AS
'Precision',
@.@.PACK_RECEIVED AS 'Packet received', @.@.PACK_SENT AS 'Packets Sent',
@.@.PACKET_ERRORS AS 'Packet Errors', @.@.SERVERNAME AS 'Server',
@.@.SERVICENAME AS 'Services', @.@.TOTAL_ERRORS AS 'Errors',
@.@.TOTAL_READ AS 'Reads', @.@.TOTAL_WRITE AS 'Writes', @.@.VERSION AS
'Version';
This includes samples and explanations on how to do it.
Also new this week:
SQL Server index performance
SQL Server - optimization:index performance
How to group items into a fixed number of bucket with MS SQL Server
How to have a simple server monitoring in MS SQL Server
What's the current version of MS SQL Server used?
What are all the triggers used in a database - Formatting syv
What are all the views in a database in MS SQL Server?
What are all the stored procedures in a database in MS SQL Server? -
Formatting syv
What's the structure of a table with MS SQL Server?

Am I close or way off the mark?

I am trying to select rows from a SQL2000 database table and then write a random number back into each row. I have already looked into do it all in a SP but there are well documented limitations for the SQL RAND function when called in the same batch, so I need to somehow do in .Net what I already have working in classic ASP.

I can't get the UPDATE (part two section) to compile. I don't know how to call the stored procedure inside the 'foreach' loop or extract the SP parameters. I have it working in classic asp but am having a lot of trouble converting to .Net 2.0. Is the below even close to working?

// stored procedure to write externally generated random number value into database

PROCEDURE RandomizeLinks
@.L_ID int,
@.L_Rank int
AS
UPDATE Links SET L_Rank = @.L_Rank
WHERE (L_ID = @.L_ID)

// Part One select links that need random number inserted.

public DataTable GetRandLinks()
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString1A"].ConnectionString);
SqlCommand cmd = new SqlCommand("RandomizerSelect001", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
try
{
da.Fill(ds, "Random001");
return ds.Tables["Random001"];
}
catch
{ throw new ApplicationException("Data error"); }
}

// Part Two I need two write a random number back into each row

protected void Button1_Click(object sender, EventArgs e)
{
GetRandLinks();
int LinkID; // this generates unassigned local variable "LinkID' error
int LRank; // this generates unassigned local variable "LRank' error

SqlConnection con2 = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString1A"].ConnectionString);
SqlCommand cmd2 = new SqlCommand("RandomizeLinks", con2);
cmd2.CommandType = CommandType.StoredProcedure;
cmd2.Parameters.AddWithValue("@.L_ID", LinkID);
cmd2.Parameters.AddWithValue("@.L_Rank", LRank);
SqlDataAdapter da2 = new SqlDataAdapter();

int RowIncrement;
RowIncrement = 0;
DataTable dt = GetRandLinks();
foreach (DataRow row in dt.Rows)
{
System.Random myRandom = new System.Random();
int LinkRank = myRandom.Next(25, 250);
LRank = LinkRank;
da2.UpdateCommand = cmd2;
RowIncrement++;
}

}

Question - instead of looping back to the database server for every row, can I pull the data into a DataTable, and then on the .Net web server update each row with it random number using system.Random and then finally read the updated DataTable back to update the SQL Database table? And if so is any of the code I have written useful to that end?

|||

Most of the T-SQL limitations can be gotten around by feeding the rand function the right 4 digits of the newid function.

|||

How would I incorporate that into this stored procedure? I am working on a SQL 2000 server, company hasn't got the money for SQL2007. And is there a way to limit the random number range to > 25 and < 250?

PROCEDURE spA_Random001
AS
DECLARE @.L_ID int
DECLARE @.L_Rank int
DECLARE cur CURSOR FOR
SELECT L_ID , L_Rank
FROM tblLinkInfo_OLD2
OPEN cur;
FETCH NEXT FROM cur
INTO @.L_ID, @.L_Rank
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF (@.L_Rank > 10 AND @.L_Rank < 300)
UPDATE tblLinkInfo_OLD2 SET L_Rank = Convert(int, (L_ID)*RAND()) WHERE (L_ID = @.L_ID)
FETCH NEXT FROM cur
INTO @.L_ID, @.L_Rank
END
CLOSE cur;
DEALLOCATE cur;

|||

Thank you for the suggestion, it is an excellent solution for most applications however in my cash I need a strictly limit the random value to integers between 25 and 250. So far - pulling the table data into a DataSet, using the webserver resident System.Random to generate the random number and then writing the changes up to the SQL server seems to be the solution. I have been spoiled by the drag and drop GridViews and DataLists no I have to learn what is really going one. I am going to try to get th above solution working but I am beginning to think I am way of track.