Help Backup: message

Showing posts with label message. Show all posts

Monday, March 12, 2012

Heterogeneous queries error

This is a multi-part message in MIME format.
--=_NextPart_000_002E_01C3F654.828ED3B0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi,
I've a dynamic query that works on linked server but it keep giving me = this error "Heterogeneous queries require the ANSI_NULLS and = ANSI_WARNINGS options to be set for the connection. This ensures = consistent query semantics. Enable these options and then reissue your = query."
I've set all the ANSI_DEFAULT on but still doesn't work. Can someone = help me out?
Thx.
----= --
CREATE PROCEDURE a_sp_check_record
@.serverDB_name VARCHAR(100),
@.storeID VARCHAR(2),
@.cutoff_date VARCHAR(30)
AS
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
SET ANSI_DEFAULTS ON
DECLARE @.sqlString NVARCHAR(4000)
DECLARE @.recCount INT
-- exist in remote but missing in HQ
SET @.sqlString =3D'SELECT DISTINCT po_number, invoice_number, = received_date, upc ' +'FROM ' + @.serverDB_name + = '.dbo.receive_order_transactions REMO '
+'WHERE received_date>''' + @.cutoff_date + ''' AND '
+ 'store_id=3D' + @.storeID + ' '
+ 'AND NOT EXISTS (SELECT * FROM = [tm341].dbo.receive_order_transactions HQ '
+ 'WHERE HQ.po_number=3DREMO.po_number AND = HQ.invoice_number=3DREMO.invoice_number AND '
+ 'HQ.received_date=3DREMO.received_date AND = HQ.store_id=3DREMO.store_id AND ' + 'HQ.store_id=3D' + @.storeID + ' AND = REMO.store_id=3D' + @.storeID +')'
EXEC SP_EXECUTESQL @.sqlString
IF @.@.rowCOUNT =3D 0
print 'remote have all HQ rcv'
ELSE
print 'remote have missing HQ rcv'
GO
--=_NextPart_000_002E_01C3F654.828ED3B0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hi,

I've a dynamic query that works on linked server but = it keep giving me this error "Heterogeneous = queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the = connection. This ensures consistent query semantics. Enable these options and then = reissue your query."

I've set all the ANSI_DEFAULT on but still doesn't = work. Can someone help me out?

Thx.

CREATE PROCEDURE a_sp_check_record
@.serverDB_name VARCHAR(100),@.storeID &= nbsp; VARCHAR(2),@.cutoff_date VARCHAR(30)

AS

SET ANSI_NULLS ONSET ANSI_WARNINGS ONSET = ANSI_DEFAULTS ON
DECLARE @.sqlString NVARCHAR(4000)DECLARE @.recCount INT
-- exist in remote but missing in = HQSET @.sqlString =3D'SELECT DISTINCT po_number, invoice_number, = received_date, upc ' +'FROM ' + @.serverDB_name + '.dbo.receive_order_transactions REMO ' &n= bsp; +'WHERE received_date>''' + @.cutoff_date + ''' AND ' &n= bsp; + 'store_id=3D' + @.storeID + ' ' &n= bsp; + 'AND NOT EXISTS (SELECT * FROM [tm341].dbo.receive_order_transactions HQ ' &n= bsp; + 'WHERE HQ.po_number=3DREMO.po_number AND = HQ.invoice_number=3DREMO.invoice_number AND ' &n= bsp; + &n= bsp; 'HQ.received_date=3DREMO.received_date AND HQ.store_id=3DREMO.store_id = AND ' &n= bsp; + &n= bsp; 'HQ.store_id=3D' + @.storeID + ' AND REMO.store_id=3D' + @.storeID = +')'

EXEC SP_EXECUTESQL @.sqlStringIF = @.@.rowCOUNT =3D 0 print 'remote have all HQ rcv'ELSE = print 'remote have missing HQ rcv'GO

--=_NextPart_000_002E_01C3F654.828ED3B0--You the ANSI_NULLS & ANSI_WARNINGS settings within the stored procedure.
Instead apply these settings while creating the procedure itself.
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE usp_test(...
GO
--
Anith

Friday, March 9, 2012

HelpCannot open Crystal Reports

I get the following message boxes when i open a report:

Seagate Crystal Reports: Database Error

Can not create DAO DBEngine.

and

Seagate Crystal Reports: Database Error

Error opening file.
File couldn not be opened: "Report", at file location: "Report"

I'm using VB. Please help!Check the path of Database if its Access other wise in case of SQL Server check the Connections String or whether any Database System is installed or not!!!!!!!!!!!11

Wednesday, March 7, 2012

Help:I cannot subscription report

Dear all

I cannot subscription report when I finished setup that the following is the error message

An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help The specified @.category_id ('101') does not exist.

The SQL agent have already start. I don't know what happen.

Can you look in the SSRS Logs and Event logs and grab some relevant information for us to digest?|||

I have already checked the log but it have any log for it.

How can I do?

Help: why SQL 2005 is slow?

This is a multi-part message in MIME format.
--=_NextPart_000_0008_01C7025D.34339BC0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi,
During the weekend, I heard that SQL Server 2000 is very slow with = tables containing rows over millions. So I did some tests with our new database on SQL Server 2005 Standard Edition. The = machine is Windows 2003 Standard Server box with 3.5 GB RAM and single 3.0GHZ P4 CPU. There is no RAID = configuration. It has drives: C: with 11GB free space and D: with 169GB free space. SQL 2005 was installed on D: drive.
The size of the database is 10GB containing 47 tables. The main table, = Items, contains 96 columns and 30 millions rows.
All columns are in varchar data type, and 2 of them are in = varchar(2500). There is no any index setup in the table neither.
The time consumed for some SQL statements with this table are the = followings:
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D
No. SQL Statement = Time
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D = =3D=3D=3D=3D=3D=3D=3D=3D=3D
1 SELECT * FROM Items WHERE item_num=3D'10029' 16 minutes
----= -- --
2 SELECT COUNT(*) FROM Items 13 = minutes
----= -- --
3 ALTER TABLE Items ADD rid INT PRIMARY KEY IDENTITY(1,1) 10 = hours 50 minutes
----= -- --
4 SELECT COUNT(*) FROM Items 20 = minutes
----= -- --
5 SELECT * FROM Items WHERE item_num=3D'10029' 18 minutes
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D
The first 2 statements were run without primary key in the table. The = statements #4 and #5 were run after "rid" was added as primary key.
This is the first time I work with a database in such size. But the = performance of SQL Server 2005 surprised me.
Would you please tell me:
1. Is such performance normal with such number of rows? 2. Do I have to do something to improve the performance with such simple = statement when the number of rows>1 million or >10 millions? 3. Is SQL Server 2005 the right one to handle a database with such big = table, or should I consider DB2 9 or Oracle 10g?
Thank you
Hongbo
--=_NextPart_000_0008_01C7025D.34339BC0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hi,

During the weekend, I heard that SQL = Server 2000 is very slow with tables containing rows over millions. So I did some = tests with our new database on SQL Server 2005 Standard Edition. The machine = is Windows 2003 Standard Server box with 3.5 GB RAM and single 3.0GHZ = P4 CPU. There is no RAID configuration. It has drives: C: with 11GB free space = and D: with 169GB free space. SQL 2005 was installed on D: drive. =

The size of the database is 10GB = containing 47 tables. The main table, Items, contains 96 columns and 30 millions = rows.All columns are in varchar data type, and 2 of them are in varchar(2500). = There is no any index setup in the table neither.

The time consumed for some SQL = statements with this table are the followings:=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3DNo. &nb= sp; SQL Statement &nbs= p; = ; = &= nbsp; Time=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D &nbs= p; =3D=3D=3D=3D=3D=3D=3D=3D=3D 1 SELECT * FROM = Items WHERE item_num=3D'10029' 16 minutes---= -- -- 2 SELECT COUNT(*) FROM Items &n= bsp; &nb= sp; 13 minutes---= -- -- 3 ALTER TABLE Items ADD rid INT PRIMARY KEY IDENTITY(1,1) = 10 hours 50 minutes---= -- -- 4 SELECT COUNT(*) FROM Items &n= bsp; &nb= sp; 20 minutes---= -- -- 5 SELECT * FROM Items = WHERE item_num=3D'10029' 18 minutes=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D

The first 2 statements were run without = primary key in the table. The statements #4 and #5 were run after "rid" was added as = primary key.

This is the first time I work with a = database in such size. But the performance of SQL Server 2005 surprised = me.

Would you please tell me:1. Is such = performance normal with such number of rows? 2. Do I have to do something to = improve the performance with such simple statement when the number of rows>1 = million or >10 millions? 3. Is SQL Server 2005 the right one to handle a = database with such big table, or should I consider DB2 9 or Oracle = 10g?

Thank you

Hongbo
--=_NextPart_000_0008_01C7025D.34339BC0--This is a multi-part message in MIME format.
--=_NextPart_000_003D_01C70287.CDE22960
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Try to find out your system having any IO or memory botleneck, if =possible post SHOWPLAN_ALL result as well
vinu
"Hongbo" <hongbo@.goodoffices.com> wrote in message =news:uKS%23AXoAHHA.3836@.TK2MSFTNGP02.phx.gbl...
Hi,
During the weekend, I heard that SQL Server 2000 is very slow with =tables containing rows over millions. So I did some tests with our new database on SQL Server 2005 Standard Edition. The =machine is Windows 2003 Standard Server box with 3.5 GB RAM and single 3.0GHZ P4 CPU. There is no RAID =configuration. It has drives: C: with 11GB free space and D: with 169GB free space. SQL 2005 was installed on D: drive.
The size of the database is 10GB containing 47 tables. The main table, =Items, contains 96 columns and 30 millions rows.
All columns are in varchar data type, and 2 of them are in =varchar(2500). There is no any index setup in the table neither.
The time consumed for some SQL statements with this table are the =followings:
==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D
No. SQL Statement = Time
==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D ==3D=3D=3D=3D=3D=3D=3D=3D=3D
1 SELECT * FROM Items WHERE item_num=3D'10029' 16 minutes
=----=-- --
2 SELECT COUNT(*) FROM Items 13 =minutes
=----=-- --
3 ALTER TABLE Items ADD rid INT PRIMARY KEY IDENTITY(1,1) 10 =hours 50 minutes
=----=-- --
4 SELECT COUNT(*) FROM Items 20 =minutes
=----=-- --
5 SELECT * FROM Items WHERE item_num=3D'10029' 18 minutes
==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D
The first 2 statements were run without primary key in the table. The =statements #4 and #5 were run after "rid" was added as primary key.
This is the first time I work with a database in such size. But the =performance of SQL Server 2005 surprised me.
Would you please tell me:
1. Is such performance normal with such number of rows? 2. Do I have to do something to improve the performance with such =simple statement when the number of rows>1 million or >10 millions? 3. Is SQL Server 2005 the right one to handle a database with such big =table, or should I consider DB2 9 or Oracle 10g?
Thank you
Hongbo
--=_NextPart_000_003D_01C70287.CDE22960
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Try to find out your system having any =IO or memory botleneck, if possible post SHOWPLAN_ALL result as well
vinu
"Hongbo" =wrote in message news:uKS%23AXoAHHA.=3836@.TK2MSFTNGP02.phx.gbl...
Hi,

During the weekend, I heard that SQL =Server 2000 is very slow with tables containing rows over millions. So I did some tests with our new database on SQL Server 2005 Standard Edition. =The machine is Windows 2003 Standard Server box with 3.5 GB RAM and =single 3.0GHZ P4 CPU. There is no RAID configuration. It has drives: C: with =11GB free space and D: with 169GB free space. SQL 2005 was installed on =D: drive.

The size of the database is 10GB =containing 47 tables. The main table, Items, contains 96 columns and 30 millions rows.All columns are in varchar data type, and 2 of them are in varchar(2500). There is no any index setup in the table =neither.

The time consumed for some SQL =statements with this table are the =followings:=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3DNo. &nb=sp; SQL =Statement &nbs=p; =; = &=nbsp; =Time=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D &nbs=p; =3D=3D=3D=3D=3D=3D=3D=3D=3D 1 SELECT * FROM =Items WHERE item_num=3D'10029' 16 =minutes---=-- -- 2 SELECT COUNT(*) FROM =Items &n=bsp; &nb=sp; 13 =minutes---=-- -- 3 ALTER TABLE Items ADD rid INT PRIMARY KEY =IDENTITY(1,1) = 10 hours 50 =minutes---=-- -- 4 SELECT COUNT(*) FROM =Items &n=bsp; &nb=sp; 20 =minutes---=-- -- 5 SELECT * FROM Items =WHERE item_num=3D'10029' 18 =minutes=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D

The first 2 statements were run =without primary key in the table. The statements #4 and #5 were run after "rid" was =added as primary key.

This is the first time I work with a =database in such size. But the performance of SQL Server 2005 surprised =me.

Would you please tell me:1. Is =such performance normal with such number of rows? 2. Do I have to do =something to improve the performance with such simple statement when the number =of rows>1 million or >10 millions? 3. Is SQL Server 2005 the =right one to handle a database with such big table, or should I consider DB2 9 =or Oracle 10g?

Thank you

Hongbo

--=_NextPart_000_003D_01C70287.CDE22960--|||Hongbo wrote:
> Hi,
> During the weekend, I heard that SQL Server 2000 is very slow with
> tables containing rows over millions. So I did some
> tests with our new database on SQL Server 2005 Standard Edition. The
> machine is Windows 2003 Standard Server box
> with 3.5 GB RAM and single 3.0GHZ P4 CPU. There is no RAID
> configuration. It has drives: C: with 11GB free space
> and D: with 169GB free space. SQL 2005 was installed on D: drive.
> The size of the database is 10GB containing 47 tables. The main table,
> Items, contains 96 columns and 30 millions rows.
> All columns are in varchar data type, and 2 of them are in
> varchar(2500). There is no any index setup in the table neither.
> The time consumed for some SQL statements with this table are the
> followings:
> ======================================================> No. SQL
> Statement Time
> ========================================= =========> 1 SELECT * FROM Items WHERE item_num='10029' 16 minutes
> ----
> --
> 2 SELECT COUNT(*) FROM Items 13
> minutes
> ----
> --
> 3 ALTER TABLE Items
> ADD rid INT PRIMARY KEY IDENTITY(1,1) 10
> hours 50 minutes
> ----
> --
> 4 SELECT COUNT(*) FROM Items 20
> minutes
> ----
> --
> 5 SELECT * FROM Items WHERE item_num='10029' 18 minutes
> ======================================================> The first 2 statements were run without primary key in the table. The
> statements #4 and #5 were run after "rid" was added as primary key.
> This is the first time I work with a database in such size. But the
> performance of SQL Server 2005 surprised me.
> Would you please tell me:
> 1. Is such performance normal with such number of rows?
> 2. Do I have to do something to improve the performance with such
> simple statement when the number of rows>1 million or >10 millions?
> 3. Is SQL Server 2005 the right one to handle a database with such big
> table, or should I consider DB2 9 or Oracle 10g?
> Thank you
> Hongbo
Do I read this right, you have 30 million rows an no indexes? I would
think about adding a few to help you out. SQL 2005 can handle that with
proper design and hardware.
I believe there is a tremendous amount of paging going on there to
accommodate your full table scans and on a single drive, hence your poor
performance.
--
Ryan Sanders
http://ryanlsanders.blogspot.com|||This is a multi-part message in MIME format.
--=_NextPart_000_0015_01C703E5.BD4BE410
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi Vinu and Ryan,
Thank you for your responses.
I know that some measures could improve the performance.
My question is: Are the results normal in SQL Server 2005 before I take =any other measures to improve the performance?
"Hongbo" <hongbo@.goodoffices.com> wrote in message =news:uKS%23AXoAHHA.3836@.TK2MSFTNGP02.phx.gbl...
Hi,
During the weekend, I heard that SQL Server 2000 is very slow with =tables containing rows over millions. So I did some tests with our new database on SQL Server 2005 Standard Edition. The =machine is Windows 2003 Standard Server box with 3.5 GB RAM and single 3.0GHZ P4 CPU. There is no RAID =configuration. It has drives: C: with 11GB free space and D: with 169GB free space. SQL 2005 was installed on D: drive.
The size of the database is 10GB containing 47 tables. The main table, =Items, contains 96 columns and 30 millions rows.
All columns are in varchar data type, and 2 of them are in =varchar(2500). There is no any index setup in the table neither.
The time consumed for some SQL statements with this table are the =followings:
==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D
No. SQL Statement = Time
==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D ==3D=3D=3D=3D=3D=3D=3D=3D=3D
1 SELECT * FROM Items WHERE item_num=3D'10029' 16 minutes
=----=-- --
2 SELECT COUNT(*) FROM Items 13 =minutes
=----=-- --
3 ALTER TABLE Items ADD rid INT PRIMARY KEY IDENTITY(1,1) 10 =hours 50 minutes
=----=-- --
4 SELECT COUNT(*) FROM Items 20 =minutes
=----=-- --
5 SELECT * FROM Items WHERE item_num=3D'10029' 18 minutes
==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D
The first 2 statements were run without primary key in the table. The =statements #4 and #5 were run after "rid" was added as primary key.
This is the first time I work with a database in such size. But the =performance of SQL Server 2005 surprised me.
Would you please tell me:
1. Is such performance normal with such number of rows? 2. Do I have to do something to improve the performance with such =simple statement when the number of rows>1 million or >10 millions? 3. Is SQL Server 2005 the right one to handle a database with such big =table, or should I consider DB2 9 or Oracle 10g?
Thank you
Hongbo
--=_NextPart_000_0015_01C703E5.BD4BE410
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Hi Vinu and Ryan,
Thank you for your =responses.
I know that some measures could improve =the performance.
My question is: Are the results =normal in SQL Server 2005 before I take any other measures to improve the performance?
"Hongbo" =wrote in message news:uKS%23AXoAHHA.=3836@.TK2MSFTNGP02.phx.gbl...
Hi,

During the weekend, I heard that SQL =Server 2000 is very slow with tables containing rows over millions. So I did some tests with our new database on SQL Server 2005 Standard Edition. =The machine is Windows 2003 Standard Server box with 3.5 GB RAM and =single 3.0GHZ P4 CPU. There is no RAID configuration. It has drives: C: with =11GB free space and D: with 169GB free space. SQL 2005 was installed on =D: drive.

The size of the database is 10GB =containing 47 tables. The main table, Items, contains 96 columns and 30 millions rows.All columns are in varchar data type, and 2 of them are in varchar(2500). There is no any index setup in the table =neither.

The time consumed for some SQL =statements with this table are the =followings:=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3DNo. &nb=sp; SQL =Statement &nbs=p; =; = &=nbsp; =Time=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D &nbs=p; =3D=3D=3D=3D=3D=3D=3D=3D=3D 1 SELECT * FROM =Items WHERE item_num=3D'10029' 16 =minutes---=-- -- 2 SELECT COUNT(*) FROM =Items &n=bsp; &nb=sp; 13 =minutes---=-- -- 3 ALTER TABLE Items ADD rid INT PRIMARY KEY =IDENTITY(1,1) = 10 hours 50 =minutes---=-- -- 4 SELECT COUNT(*) FROM =Items &n=bsp; &nb=sp; 20 =minutes---=-- -- 5 SELECT * FROM Items =WHERE item_num=3D'10029' 18 =minutes=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D

The first 2 statements were run =without primary key in the table. The statements #4 and #5 were run after "rid" was =added as primary key.

This is the first time I work with a =database in such size. But the performance of SQL Server 2005 surprised =me.

Would you please tell me:1. Is =such performance normal with such number of rows? 2. Do I have to do =something to improve the performance with such simple statement when the number =of rows>1 million or >10 millions? 3. Is SQL Server 2005 the =right one to handle a database with such big table, or should I consider DB2 9 =or Oracle 10g?

Thank you

Hongbo

--=_NextPart_000_0015_01C703E5.BD4BE410--|||This is a multi-part message in MIME format.
--=_NextPart_000_0404_01C706F9.89BE1E30
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
"Hongbo" <hongbo@.goodoffices.com> wrote in message =news:ecxxF5ABHHA.5060@.TK2MSFTNGP02.phx.gbl...
Hi Vinu and Ryan,
Thank you for your responses.
I know that some measures could improve the performance.
My question is: Are the results normal in SQL Server 2005 before I =take any other measures to improve the performance?
(please refrain from posting in HTML, most newsreaders don't like =it...)
Anyway...that performance seems about right given your setup.
First, if this database is important, get something with RAID, =otherwise a single disk failure will kill you.
Also, move your LOG file to a different physical drive (or set of =drives). This will increase your update/insert/delete statements.
Finally, Step 3... what is RID? Does it match anything in the outside =world? If not, you may prefer to find a natural key. Perhaps item_num.
If you're selecting against item_num, you will want that as at least =one of your indices.
What other indices you will want will depend greatly on how your =normally access your data.
Also, I would hope you're not using select * in production code.
Finally, 96 columns is a fairly wide database. is this normalized? =If not, why not? What's the average width of a road. You mentioned =varchar, but not what the average use is.
Personally, if the item_num is unique and a good candidate key, I'd =drop the rid column you added, make item_num my primary key and then go =from there.
"Hongbo" <hongbo@.goodoffices.com> wrote in message =news:uKS%23AXoAHHA.3836@.TK2MSFTNGP02.phx.gbl...
Hi,
During the weekend, I heard that SQL Server 2000 is very slow with =tables containing rows over millions. So I did some tests with our new database on SQL Server 2005 Standard Edition. The =machine is Windows 2003 Standard Server box with 3.5 GB RAM and single 3.0GHZ P4 CPU. There is no RAID =configuration. It has drives: C: with 11GB free space and D: with 169GB free space. SQL 2005 was installed on D: drive.
The size of the database is 10GB containing 47 tables. The main =table, Items, contains 96 columns and 30 millions rows.
All columns are in varchar data type, and 2 of them are in =varchar(2500). There is no any index setup in the table neither.
The time consumed for some SQL statements with this table are the =followings:
==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D
No. SQL Statement = Time
==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D ==3D=3D=3D=3D=3D=3D=3D=3D=3D
1 SELECT * FROM Items WHERE item_num=3D'10029' 16 =minutes
=----=-- --
2 SELECT COUNT(*) FROM Items =13 minutes
=----=-- --
3 ALTER TABLE Items ADD rid INT PRIMARY KEY IDENTITY(1,1) 10 =hours 50 minutes
=----=-- --
4 SELECT COUNT(*) FROM Items =20 minutes
=----=-- --
5 SELECT * FROM Items WHERE item_num=3D'10029' 18 =minutes
==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D
The first 2 statements were run without primary key in the table. =The statements #4 and #5 were run after "rid" was added as primary key.
This is the first time I work with a database in such size. But the =performance of SQL Server 2005 surprised me.
Would you please tell me:
1. Is such performance normal with such number of rows? 2. Do I have to do something to improve the performance with such =simple statement when the number of rows>1 million or >10 millions? 3. Is SQL Server 2005 the right one to handle a database with such =big table, or should I consider DB2 9 or Oracle 10g?
Thank you
Hongbo
--=_NextPart_000_0404_01C706F9.89BE1E30
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
"Hongbo" =wrote in message news:ecxxF5ABHHA.5060=@.TK2MSFTNGP02.phx.gbl...
Hi Vinu and Ryan,

Thank you for your =responses.

I know that some measures could =improve the performance.

My question is: Are the results =normal in SQL Server 2005 before I take any other measures to improve the performance?

(please refrain from posting in HTML, =most newsreaders don't like it...)

Anyway...that performance seems =about right given your setup.

First, if this database is important, =get something with RAID, otherwise a single disk failure will kill you.

Also, move your LOG file to a =different physical drive (or set of drives). This will increase your =update/insert/delete statements.

Finally, Step 3... what is RID? =Does it match anything in the outside world? If not, you may prefer to =find a natural key. Perhaps item_num.

If you're selecting against item_num, =you will want that as at least one of your indices.

What other indices you will want will =depend greatly on how your normally access your data.

Also, I would hope you're not using =select * in production code.

Finally, 96 columns is a fairly wide database. is this normalized? If not, why not? =What's the average width of a road. You mentioned varchar, but not what the =average use is.

Personally, if the item_num is unique =and a good candidate key, I'd drop the rid column you added, make item_num my =primary key and then go from there.

"Hongbo" =wrote in message news:uKS%23AXoAHHA.=3836@.TK2MSFTNGP02.phx.gbl...
Hi,

During the weekend, I heard that =SQL Server 2000 is very slow with tables containing rows over millions. So I =did some tests with our new database on SQL Server 2005 Standard Edition. =The machine is Windows 2003 Standard Server box with 3.5 GB RAM and =single 3.0GHZ P4 CPU. There is no RAID configuration. It has drives: C: =with 11GB free space and D: with 169GB free space. SQL 2005 was installed =on D: drive.

The size of the database is 10GB =containing 47 tables. The main table, Items, contains 96 columns and 30 millions rows.All columns are in varchar data type, and 2 of them are in varchar(2500). There is no any index setup in the table neither.

The time consumed for some SQL =statements with this table are the =followings:=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3DNo. &nb=sp; SQL =Statement &nbs=p; =; = &=nbsp; =Time=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D &nbs=p; =3D=3D=3D=3D=3D=3D=3D=3D=3D 1 SELECT * =FROM Items WHERE item_num=3D'10029' =16 =minutes---=-- -- 2 SELECT COUNT(*) FROM = =Items &n=bsp; &nb=sp; 13 =minutes---=-- -- 3 ALTER TABLE Items ADD rid INT PRIMARY KEY =IDENTITY(1,1) = 10 hours 50 =minutes---=-- -- 4 SELECT COUNT(*) FROM = =Items &n=bsp; &nb=sp; 20 =minutes---=-- -- 5 SELECT * FROM Items =WHERE item_num=3D'10029' =18 =minutes=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D

The first 2 statements were run =without primary key in the table. The statements #4 and #5 were run after "rid" was =added as primary key.

This is the first time I work with =a database in such size. But the performance of SQL Server 2005 surprised me.

Would you please tell me:1. Is =such performance normal with such number of rows? 2. Do I have to do something to improve the performance with such simple statement when =the number of rows>1 million or >10 millions? 3. Is SQL Server =2005 the right one to handle a database with such big table, or should I =consider DB2 9 or Oracle 10g?

Thank you

Hongbo

--=_NextPart_000_0404_01C706F9.89BE1E30--|||This is a multi-part message in MIME format.
--=_NextPart_000_0041_01C70705.B2737350
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi, Greg,
Thank you for your message.
Regardless what kind of measure should be taken to improve the =performance,
would you please tell whether the result I got is normal under the given =circumstance?
Thank you
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in =message news:OM6hzNyBHHA.4844@.TK2MSFTNGP02.phx.gbl...
"Hongbo" <hongbo@.goodoffices.com> wrote in message =news:ecxxF5ABHHA.5060@.TK2MSFTNGP02.phx.gbl...
Hi Vinu and Ryan,
Thank you for your responses.
I know that some measures could improve the performance.
My question is: Are the results normal in SQL Server 2005 before I =take any other measures to improve the performance?
(please refrain from posting in HTML, most newsreaders don't like =it...)
Anyway...that performance seems about right given your setup.
First, if this database is important, get something with RAID, =otherwise a single disk failure will kill you.
Also, move your LOG file to a different physical drive (or set of =drives). This will increase your update/insert/delete statements.
Finally, Step 3... what is RID? Does it match anything in the =outside world? If not, you may prefer to find a natural key. Perhaps =item_num.
If you're selecting against item_num, you will want that as at least =one of your indices.
What other indices you will want will depend greatly on how your =normally access your data.
Also, I would hope you're not using select * in production code.
Finally, 96 columns is a fairly wide database. is this normalized? =If not, why not? What's the average width of a road. You mentioned =varchar, but not what the average use is.
Personally, if the item_num is unique and a good candidate key, I'd =drop the rid column you added, make item_num my primary key and then go =from there.
"Hongbo" <hongbo@.goodoffices.com> wrote in message =news:uKS%23AXoAHHA.3836@.TK2MSFTNGP02.phx.gbl...
Hi,
During the weekend, I heard that SQL Server 2000 is very slow with =tables containing rows over millions. So I did some tests with our new database on SQL Server 2005 Standard Edition. =The machine is Windows 2003 Standard Server box with 3.5 GB RAM and single 3.0GHZ P4 CPU. There is no RAID =configuration. It has drives: C: with 11GB free space and D: with 169GB free space. SQL 2005 was installed on D: drive.
The size of the database is 10GB containing 47 tables. The main =table, Items, contains 96 columns and 30 millions rows.
All columns are in varchar data type, and 2 of them are in =varchar(2500). There is no any index setup in the table neither.
The time consumed for some SQL statements with this table are the =followings:
==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D
No. SQL Statement = Time
==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D ==3D=3D=3D=3D=3D=3D=3D=3D=3D
1 SELECT * FROM Items WHERE item_num=3D'10029' 16 =minutes
=----=-- --
2 SELECT COUNT(*) FROM Items = 13 minutes
=----=-- --
3 ALTER TABLE Items ADD rid INT PRIMARY KEY IDENTITY(1,1) =10 hours 50 minutes
=----=-- --
4 SELECT COUNT(*) FROM Items = 20 minutes
=----=-- --
5 SELECT * FROM Items WHERE item_num=3D'10029' 18 =minutes
==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D
The first 2 statements were run without primary key in the table. =The statements #4 and #5 were run after "rid" was added as primary key.
This is the first time I work with a database in such size. But =the performance of SQL Server 2005 surprised me.
Would you please tell me:
1. Is such performance normal with such number of rows? 2. Do I have to do something to improve the performance with such =simple statement when the number of rows>1 million or >10 millions? 3. Is SQL Server 2005 the right one to handle a database with such =big table, or should I consider DB2 9 or Oracle 10g?
Thank you
Hongbo
--=_NextPart_000_0041_01C70705.B2737350
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Hi, Greg,
Thank you for your =message.
Regardless what kind of measure should =be taken to improve the performance,
would you please tell whether the =result I got is normal under the given circumstance?
Thank you
"Greg D. Moore (Strider)" wrote in message news:OM6hzNyBHHA.4844=@.TK2MSFTNGP02.phx.gbl...

"Hongbo" =wrote in message news:ecxxF5ABHHA.5060=@.TK2MSFTNGP02.phx.gbl...
Hi Vinu and Ryan,

Thank you for your =responses.

I know that some measures could =improve the performance.

My question is: Are the =results normal in SQL Server 2005 before I take any other measures to improve the performance?

(please refrain from posting in =HTML, most newsreaders don't like it...)

Anyway...that performance seems =about right given your setup.

First, if this database is =important, get something with RAID, otherwise a single disk failure will kill you.

Also, move your LOG file to a =different physical drive (or set of drives). This will increase your update/insert/delete statements.

Finally, Step 3... what is =RID? Does it match anything in the outside world? If not, you may prefer to =find a natural key. Perhaps item_num.

If you're selecting against =item_num, you will want that as at least one of your indices.

What other indices you will want =will depend greatly on how your normally access your data.

Also, I would hope you're not using =select * in production code.

Finally, 96 columns is a fairly =wide database. is this normalized? If not, why not? =What's the average width of a road. You mentioned varchar, but not what =the average use is.

Personally, if the item_num is =unique and a good candidate key, I'd drop the rid column you added, make item_num =my primary key and then go from there.

"Hongbo" =wrote in message news:uKS%23AXoAHHA.=3836@.TK2MSFTNGP02.phx.gbl...
Hi,

During the weekend, I heard that =SQL Server 2000 is very slow with tables containing rows over millions. So I =did some tests with our new database on SQL Server 2005 Standard =Edition. The machine is Windows 2003 Standard Server box with 3.5 GB RAM =and single 3.0GHZ P4 CPU. There is no RAID configuration. It has drives: C: =with 11GB free space and D: with 169GB free space. SQL 2005 was =installed on D: drive.

The size of the database is 10GB =containing 47 tables. The main table, Items, contains 96 columns and 30 =millions rows.All columns are in varchar data type, and 2 of them are =in varchar(2500). There is no any index setup in the table neither.

The time consumed for some SQL =statements with this table are the =followings:=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3DNo. &nb=sp; SQL =Statement &nbs=p; =; = &=nbsp; =Time=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D &nbs=p; =3D=3D=3D=3D=3D=3D=3D=3D=3D 1 SELECT * =FROM Items WHERE item_num=3D'10029' =16 =minutes---=-- -- 2 SELECT COUNT(*) =FROM =Items &n=bsp; &nb=sp; 13 =minutes---=-- -- 3 ALTER TABLE Items ADD rid INT PRIMARY KEY =IDENTITY(1,1) = 10 hours 50 =minutes---=-- -- 4 SELECT COUNT(*) =FROM =Items &n=bsp; &nb=sp; 20 =minutes---=-- -- 5 SELECT * FROM =Items WHERE item_num=3D'10029' =18 =minutes=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D

The first 2 statements were run =without primary key in the table. The statements #4 and #5 were run after ="rid" was added as primary key.

This is the first time I work =with a database in such size. But the performance of SQL Server 2005 surprised me.

Would you please tell me:1. =Is such performance normal with such number of rows? 2. Do I have to =do something to improve the performance with such simple statement =when the number of rows>1 million or >10 millions? 3. Is SQL =Server 2005 the right one to handle a database with such big table, or should =I consider DB2 9 or Oracle 10g?

Thank you

Hongbo

--=_NextPart_000_0041_01C70705.B2737350--|||Hongbo wrote:
> Hi Vinu and Ryan,
> Thank you for your responses.
> I know that some measures could improve the performance.
> My question is: Are the results normal in SQL Server 2005 before I take
> any other measures to improve the performance?
> "Hongbo" <hongbo@.goodoffices.com <mailto:hongbo@.goodoffices.com>>
> wrote in message news:uKS%23AXoAHHA.3836@.TK2MSFTNGP02.phx.gbl...
> Hi,
> During the weekend, I heard that SQL Server 2000 is very slow with
> tables containing rows over millions. So I did some
> tests with our new database on SQL Server 2005 Standard Edition. The
> machine is Windows 2003 Standard Server box
> with 3.5 GB RAM and single 3.0GHZ P4 CPU. There is no RAID
> configuration. It has drives: C: with 11GB free space
> and D: with 169GB free space. SQL 2005 was installed on D: drive.
> The size of the database is 10GB containing 47 tables. The main
> table, Items, contains 96 columns and 30 millions rows.
> All columns are in varchar data type, and 2 of them are in
> varchar(2500). There is no any index setup in the table neither.
> The time consumed for some SQL statements with this table are the
> followings:
> ======================================================> No. SQL
> Statement Time
> ========================================= =========> 1 SELECT * FROM Items WHERE item_num='10029' 16 minutes
> ----
> --
> 2 SELECT COUNT(*) FROM Items
> 13 minutes
> ----
> --
> 3 ALTER TABLE Items
> ADD rid INT PRIMARY KEY IDENTITY(1,1) 10
> hours 50 minutes
> ----
> --
> 4 SELECT COUNT(*) FROM Items
> 20 minutes
> ----
> --
> 5 SELECT * FROM Items WHERE item_num='10029' 18 minutes
> ======================================================> The first 2 statements were run without primary key in the table.
> The statements #4 and #5 were run after "rid" was added as primary key.
> This is the first time I work with a database in such size. But the
> performance of SQL Server 2005 surprised me.
> Would you please tell me:
> 1. Is such performance normal with such number of rows?
> 2. Do I have to do something to improve the performance with such
> simple statement when the number of rows>1 million or >10 millions?
> 3. Is SQL Server 2005 the right one to handle a database with such
> big table, or should I consider DB2 9 or Oracle 10g?
> Thank you
> Hongbo
Yes, without any index set up, this is normal performance. Even if you
move this index-less design over to DB2 or 10g, you will see the same
performance.|||This is a multi-part message in MIME format.
--=_NextPart_000_0610_01C70762.EED7AD90
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
"Hongbo" <hongbo@.goodoffices.com> wrote in message =news:%23yeU24yBHHA.4292@.TK2MSFTNGP02.phx.gbl...
Hi, Greg,
Thank you for your message.
Regardless what kind of measure should be taken to improve the =performance,
would you please tell whether the result I got is normal under the =given circumstance?
Quite possibly yes.
Thank you
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in =message news:OM6hzNyBHHA.4844@.TK2MSFTNGP02.phx.gbl...
"Hongbo" <hongbo@.goodoffices.com> wrote in message =news:ecxxF5ABHHA.5060@.TK2MSFTNGP02.phx.gbl...
Hi Vinu and Ryan,
Thank you for your responses.
I know that some measures could improve the performance.
My question is: Are the results normal in SQL Server 2005 before I =take any other measures to improve the performance?
(please refrain from posting in HTML, most newsreaders don't like =it...)
Anyway...that performance seems about right given your setup.
First, if this database is important, get something with RAID, =otherwise a single disk failure will kill you.
Also, move your LOG file to a different physical drive (or set of =drives). This will increase your update/insert/delete statements.
Finally, Step 3... what is RID? Does it match anything in the =outside world? If not, you may prefer to find a natural key. Perhaps =item_num.
If you're selecting against item_num, you will want that as at =least one of your indices.
What other indices you will want will depend greatly on how your =normally access your data.
Also, I would hope you're not using select * in production code.
Finally, 96 columns is a fairly wide database. is this =normalized? If not, why not? What's the average width of a road. You =mentioned varchar, but not what the average use is.
Personally, if the item_num is unique and a good candidate key, =I'd drop the rid column you added, make item_num my primary key and then =go from there.
"Hongbo" <hongbo@.goodoffices.com> wrote in message =news:uKS%23AXoAHHA.3836@.TK2MSFTNGP02.phx.gbl...
Hi,
During the weekend, I heard that SQL Server 2000 is very slow =with tables containing rows over millions. So I did some tests with our new database on SQL Server 2005 Standard Edition. =The machine is Windows 2003 Standard Server box with 3.5 GB RAM and single 3.0GHZ P4 CPU. There is no RAID =configuration. It has drives: C: with 11GB free space and D: with 169GB free space. SQL 2005 was installed on D: =drive.
The size of the database is 10GB containing 47 tables. The main =table, Items, contains 96 columns and 30 millions rows.
All columns are in varchar data type, and 2 of them are in =varchar(2500). There is no any index setup in the table neither.
The time consumed for some SQL statements with this table are =the followings:
==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D
No. SQL Statement = Time
==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D ==3D=3D=3D=3D=3D=3D=3D=3D=3D
1 SELECT * FROM Items WHERE item_num=3D'10029' 16 =minutes
=----=-- --
2 SELECT COUNT(*) FROM Items = 13 minutes
=----=-- --
3 ALTER TABLE Items ADD rid INT PRIMARY KEY IDENTITY(1,1) =10 hours 50 minutes
=----=-- --
4 SELECT COUNT(*) FROM Items = 20 minutes
=----=-- --
5 SELECT * FROM Items WHERE item_num=3D'10029' 18 =minutes
==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D
The first 2 statements were run without primary key in the =table. The statements #4 and #5 were run after "rid" was added as =primary key.
This is the first time I work with a database in such size. But =the performance of SQL Server 2005 surprised me.
Would you please tell me:
1. Is such performance normal with such number of rows? 2. Do I have to do something to improve the performance with =such simple statement when the number of rows>1 million or >10 millions? =
3. Is SQL Server 2005 the right one to handle a database with =such big table, or should I consider DB2 9 or Oracle 10g?
Thank you
Hongbo
--=_NextPart_000_0610_01C70762.EED7AD90
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
"Hongbo" =wrote in message news:%23yeU24yBHHA.=4292@.TK2MSFTNGP02.phx.gbl...
Hi, Greg,

Thank you for your =message.

Regardless what kind of measure =should be taken to improve the performance,
would you please tell whether the =result I got is normal under the given circumstance?

Quite possibly yes.

Thank you
"Greg D. Moore (Strider)" wrote in message news:OM6hzNyBHHA.4844=@.TK2MSFTNGP02.phx.gbl...

"Hongbo" =wrote in message news:ecxxF5ABHHA.5060=@.TK2MSFTNGP02.phx.gbl...
Hi Vinu and Ryan,

Thank you for your =responses.

I know that some measures could =improve the performance.

My question is: Are the =results normal in SQL Server 2005 before I take any other measures to improve the = performance?

(please refrain from posting in =HTML, most newsreaders don't like it...)

Anyway...that performance seems =about right given your setup.

First, if this database is =important, get something with RAID, otherwise a single disk failure will kill you.

Also, move your LOG file to a =different physical drive (or set of drives). This will increase your update/insert/delete statements.

Finally, Step 3... what is =RID? Does it match anything in the outside world? If not, you may prefer =to find a natural key. Perhaps item_num.

If you're selecting against =item_num, you will want that as at least one of your indices.

What other indices you will want =will depend greatly on how your normally access your data.

Also, I would hope you're not =using select * in production code.

Finally, 96 columns is a fairly =wide database. is this normalized? If not, why not? =What's the average width of a road. You mentioned varchar, but not =what the average use is.

Personally, if the item_num is =unique and a good candidate key, I'd drop the rid column you added, make =item_num my primary key and then go from there.

"Hongbo" wrote in message news:uKS%23AXoAHHA.=3836@.TK2MSFTNGP02.phx.gbl...
Hi,

During the weekend, I heard =that SQL Server 2000 is very slow with tables containing rows over millions. So =I did some tests with our new database on SQL Server 2005 Standard = Edition. The machine is Windows 2003 Standard Server box =with 3.5 GB RAM and single 3.0GHZ P4 CPU. There is no RAID configuration. It =has drives: C: with 11GB free space and D: with 169GB free =space. SQL 2005 was installed on D: drive.

The size of the database is =10GB containing 47 tables. The main table, Items, contains 96 columns and 30 =millions rows.All columns are in varchar data type, and 2 of them are =in varchar(2500). There is no any index setup in the table neither.

The time consumed for some SQL =statements with this table are the =followings:=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3DNo. &nb=sp; SQL =Statement &nbs=p; =; = &=nbsp; =Time=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D &nbs=p; =3D=3D=3D=3D=3D=3D=3D=3D=3D 1 SELECT =* FROM Items WHERE =item_num=3D'10029' 16 =minutes---=-- -- 2 SELECT COUNT(*) =FROM =Items &n=bsp; &nb=sp; 13 =minutes---=-- -- 3 ALTER TABLE =Items ADD rid INT PRIMARY KEY = =IDENTITY(1,1) = 10 hours 50 =minutes---=-- -- 4 SELECT COUNT(*) =FROM =Items &n=bsp; &nb=sp; 20 =minutes---=-- -- 5 SELECT * FROM =Items WHERE =item_num=3D'10029' 18 =minutes=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D

The first 2 statements were run =without primary key in the table. The statements #4 and #5 were run =after "rid" was added as primary key.

This is the first time I work =with a database in such size. But the performance of SQL Server 2005 =surprised me.

Would you please tell me:1. =Is such performance normal with such number of rows? 2. Do I have to =do something to improve the performance with such simple statement =when the number of rows>1 million or >10 millions? 3. Is SQL =Server 2005 the right one to handle a database with such big table, or =should I consider DB2 9 or Oracle 10g?

Thank you

Hongbo

--=_NextPart_000_0610_01C70762.EED7AD90--

HELP: Updating column with data from another column

I post a message about this several months ago, but I may have misstated the
problem. So here goes again. My apologies for the duplicate.
I have two databases, each with identical schema. I need to update about
1,000 columns in DB1.table1, which are currently null, with data from the
same columns in DB2.table1. The tables essentially contain the same data,
except for the columns I need to update.
I'm using a unique ID column as the key. E.g., the value of the ID column
in DB1.table1.row1 is identical to the value of the ID in DB2.table1.row1.
I'm not the strongest at writing queries, but I tried using this query:
update DB1.dbo.table1
set DB1.dbo.table1.textcol = Db2.dbo.table1.textcol
where
(select IDcol from DB2.dbo.table1
where IDcol in (select IDcol from DB1.dbo.table1)
and DB2.dbo.table1.textcol like
'%textstring1'+char(13)+char(10)+'textstring2%'
It errored out with this:
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near 'textstring2%'.
It seems like a simple thing to do. Can anyone shed some light on what I'm
doing wrong?
Thanks!
John
update DB1.dbo.table1
set textcol = Db2.dbo.table1.textcol
from DB1.dbo.table1
inner join
DB2.dbo.table1
on DB1.dbo.table1.IDCol = DB2.dbo.table1.IDCol
where DB2.dbo.table1.textcol like
'%textstring1'+char(13)+char(10)+'textstring2%'
Russel Loski, MCSD.Net
|||Thanks for the quick reply, Russell! That makes better sense. I ran the
query and am now getting this error:
Server: Msg 7202, Level 11, State 2, Line 2
Could not find server 'DB2' in sysservers. Execute sp_addlinkedserver to add
the server to sysservers.
Since DB2 is a database on the same server as DB1, and not a server, could I
be referring to the database incorrectly?
Thanks,
John
"RLoski" wrote:

> update DB1.dbo.table1
> set textcol = Db2.dbo.table1.textcol
> from DB1.dbo.table1
> inner join
> DB2.dbo.table1
> on DB1.dbo.table1.IDCol = DB2.dbo.table1.IDCol
> where DB2.dbo.table1.textcol like
> '%textstring1'+char(13)+char(10)+'textstring2%'
> --
> Russel Loski, MCSD.Net
>
|||That sounds like you have two periods together or you have a four part
(db.owner.table.column) where a table is expected (which would be
interpretted as server.db.owner.table).
Russel Loski, MCSD.Net
"John Steen" wrote:

> Thanks for the quick reply, Russell! That makes better sense. I ran the
> query and am now getting this error:
> Server: Msg 7202, Level 11, State 2, Line 2
> Could not find server 'DB2' in sysservers. Execute sp_addlinkedserver to add
> the server to sysservers.
> Since DB2 is a database on the same server as DB1, and not a server, could I
> be referring to the database incorrectly?
> Thanks,
> John
>
|||Thanks, Again, Russell. I'll check it out.
John
"RLoski" wrote:

> That sounds like you have two periods together or you have a four part
> (db.owner.table.column) where a table is expected (which would be
> interpretted as server.db.owner.table).
> --
> Russel Loski, MCSD.Net
>
> "John Steen" wrote:
>

HELP: Updating column with data from another column

I post a message about this several months ago, but I may have misstated the
problem. So here goes again. My apologies for the duplicate.
I have two databases, each with identical schema. I need to update about
1,000 columns in DB1.table1, which are currently null, with data from the
same columns in DB2.table1. The tables essentially contain the same data,
except for the columns I need to update.
I'm using a unique ID column as the key. E.g., the value of the ID column
in DB1.table1.row1 is identical to the value of the ID in DB2.table1.row1.
I'm not the strongest at writing queries, but I tried using this query:
update DB1.dbo.table1
set DB1.dbo.table1.textcol = Db2.dbo.table1.textcol
where
(select IDcol from DB2.dbo.table1
where IDcol in (select IDcol from DB1.dbo.table1)
and DB2.dbo.table1.textcol like
'%textstring1'+char(13)+char(10)+'textstring2%'
It errored out with this:
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near 'textstring2%'.
It seems like a simple thing to do. Can anyone shed some light on what I'm
doing wrong?
Thanks!
Johnupdate DB1.dbo.table1
set textcol = Db2.dbo.table1.textcol
from DB1.dbo.table1
inner join
DB2.dbo.table1
on DB1.dbo.table1.IDCol = DB2.dbo.table1.IDCol
where DB2.dbo.table1.textcol like
'%textstring1'+char(13)+char(10)+'textstring2%'
--
Russel Loski, MCSD.Net|||Thanks for the quick reply, Russell! That makes better sense. I ran the
query and am now getting this error:
Server: Msg 7202, Level 11, State 2, Line 2
Could not find server 'DB2' in sysservers. Execute sp_addlinkedserver to add
the server to sysservers.
Since DB2 is a database on the same server as DB1, and not a server, could I
be referring to the database incorrectly?
Thanks,
John
"RLoski" wrote:
> update DB1.dbo.table1
> set textcol = Db2.dbo.table1.textcol
> from DB1.dbo.table1
> inner join
> DB2.dbo.table1
> on DB1.dbo.table1.IDCol = DB2.dbo.table1.IDCol
> where DB2.dbo.table1.textcol like
> '%textstring1'+char(13)+char(10)+'textstring2%'
> --
> Russel Loski, MCSD.Net
>|||That sounds like you have two periods together or you have a four part
(db.owner.table.column) where a table is expected (which would be
interpretted as server.db.owner.table).
--
Russel Loski, MCSD.Net
"John Steen" wrote:
> Thanks for the quick reply, Russell! That makes better sense. I ran the
> query and am now getting this error:
> Server: Msg 7202, Level 11, State 2, Line 2
> Could not find server 'DB2' in sysservers. Execute sp_addlinkedserver to add
> the server to sysservers.
> Since DB2 is a database on the same server as DB1, and not a server, could I
> be referring to the database incorrectly?
> Thanks,
> John
>|||Thanks, Again, Russell. I'll check it out.
John
"RLoski" wrote:
> That sounds like you have two periods together or you have a four part
> (db.owner.table.column) where a table is expected (which would be
> interpretted as server.db.owner.table).
> --
> Russel Loski, MCSD.Net
>
> "John Steen" wrote:
> > Thanks for the quick reply, Russell! That makes better sense. I ran the
> > query and am now getting this error:
> >
> > Server: Msg 7202, Level 11, State 2, Line 2
> > Could not find server 'DB2' in sysservers. Execute sp_addlinkedserver to add
> > the server to sysservers.
> >
> > Since DB2 is a database on the same server as DB1, and not a server, could I
> > be referring to the database incorrectly?
> >
> > Thanks,
> > John
> >
>

HELP: Updating column with data from another column

I post a message about this several months ago, but I may have misstated the
problem. So here goes again. My apologies for the duplicate.
I have two databases, each with identical schema. I need to update about
1,000 columns in DB1.table1, which are currently null, with data from the
same columns in DB2.table1. The tables essentially contain the same data,
except for the columns I need to update.
I'm using a unique ID column as the key. E.g., the value of the ID column
in DB1.table1.row1 is identical to the value of the ID in DB2.table1.row1.
I'm not the strongest at writing queries, but I tried using this query:
update DB1.dbo.table1
set DB1.dbo.table1.textcol = Db2.dbo.table1.textcol
where
(select IDcol from DB2.dbo.table1
where IDcol in (select IDcol from DB1.dbo.table1)
and DB2.dbo.table1.textcol like
'%textstring1'+char(13)+char(10)+'textst
ring2%'
It errored out with this:
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near 'textstring2%'.
It seems like a simple thing to do. Can anyone shed some light on what I'm
doing wrong?
Thanks!
Johnupdate DB1.dbo.table1
set textcol = Db2.dbo.table1.textcol
from DB1.dbo.table1
inner join
DB2.dbo.table1
on DB1.dbo.table1.IDCol = DB2.dbo.table1.IDCol
where DB2.dbo.table1.textcol like
'%textstring1'+char(13)+char(10)+'textst
ring2%'
Russel Loski, MCSD.Net|||Thanks for the quick reply, Russell! That makes better sense. I ran the
query and am now getting this error:
Server: Msg 7202, Level 11, State 2, Line 2
Could not find server 'DB2' in sysservers. Execute sp_addlinkedserver to add
the server to sysservers.
Since DB2 is a database on the same server as DB1, and not a server, could I
be referring to the database incorrectly?
Thanks,
John
"RLoski" wrote:

> update DB1.dbo.table1
> set textcol = Db2.dbo.table1.textcol
> from DB1.dbo.table1
> inner join
> DB2.dbo.table1
> on DB1.dbo.table1.IDCol = DB2.dbo.table1.IDCol
> where DB2.dbo.table1.textcol like
> '%textstring1'+char(13)+char(10)+'textst
ring2%'
> --
> Russel Loski, MCSD.Net
>|||That sounds like you have two periods together or you have a four part
(db.owner.table.column) where a table is expected (which would be
interpretted as server.db.owner.table).
--
Russel Loski, MCSD.Net
"John Steen" wrote:

> Thanks for the quick reply, Russell! That makes better sense. I ran the
> query and am now getting this error:
> Server: Msg 7202, Level 11, State 2, Line 2
> Could not find server 'DB2' in sysservers. Execute sp_addlinkedserver to a
dd
> the server to sysservers.
> Since DB2 is a database on the same server as DB1, and not a server, could
I
> be referring to the database incorrectly?
> Thanks,
> John
>|||Thanks, Again, Russell. I'll check it out.
John
"RLoski" wrote:

> That sounds like you have two periods together or you have a four part
> (db.owner.table.column) where a table is expected (which would be
> interpretted as server.db.owner.table).
> --
> Russel Loski, MCSD.Net
>
> "John Steen" wrote:
>
>

Friday, February 24, 2012

Help: Odd Error Mess :The table terms has been created but its max rowsize(8850)

Hi, I've come across a error message and I'm not sure what to do.

Warning: The table 'terms' has been created but its maximum row size (8850) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

I've tried playing with odd and ends, but I'm still getting the message does anyone know hwo you expand the maxium number of bytes per row.

Thanks For any help.You obviously have too much information to fit in one row.

This error message is caused by either a table with too many columns or a few very wide columns.

The best solution is to break up your table in to 2, 3 smaller tables and have a one-to-one relationship. The combination of the matching rows will meet your larger rowsize requirement.|||SQL Server has a 8K block size, which means that a single row cannot exceed this size (8060 bytes). When all column sizes are added up, SQL is telling you that that number potentially exceeds that size (this is because of varchars). You only have 2 options, 1 - reduce the size of your columns until the total is below 8060, or 2 - split the table up - as the previous post suggests.|||You cannot "force" sql to overcome the limit of 8060 bytes per row.

Anyway you'll get an error ONLY IF your actual data is larger that 8060 bytes.
For example if your table is made of two columns of VARCHAR(5000) you'll get the warning you already know.
Than you can insert values in your table with no problem, expect for that rows that are bigger that the near 8Kb limit.

If you really need to have not virtual limits you can use the TEXT or IMAGE data types.

Help: GetTextExtentPointIX could not be located?

Hi All,
While trying to create a new dts package in SQL Server
2000 the following error message appeared:
mmc.exe - Entry Point Not Found
The procedure entry point GetTextExtentPointIX could not
be located in the dynamic link library MSDART.dll.
I reinstalled MS Servicepack 3a for SQL Server, but this
didn't solve the problem.
I tried to use system restore, but without any luck
either.
I installed Visual Studio .NET Whidbey the day before
yesterday, could this have caused the problem, and if so,
how could I resolve this error?
Thank you in advance,
bENI had a similar problem earlier and had to uninstall Whidbey and then
re-install SQL Server. Not sure if any workaround is present.
I would recommend that you install Whidbey on a seperate machine.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"bEN" <benvkm@.nospam4.net> wrote in message
news:091b01c3b025$1036a100$a401280a@.phx.gbl...
> Hi All,
> While trying to create a new dts package in SQL Server
> 2000 the following error message appeared:
> mmc.exe - Entry Point Not Found
> The procedure entry point GetTextExtentPointIX could not
> be located in the dynamic link library MSDART.dll.
> I reinstalled MS Servicepack 3a for SQL Server, but this
> didn't solve the problem.
> I tried to use system restore, but without any luck
> either.
> I installed Visual Studio .NET Whidbey the day before
> yesterday, could this have caused the problem, and if so,
> how could I resolve this error?
> Thank you in advance,
> bEN
>|||Hello,
Thanks for your post. According to the error message, it usually indicates
a problem with the MSDART.dll file. I would like you to try installing MDAC
2.7 SP1a. After rebooting, then apply SQL2K SP3a and rebooted again. Does
it solve your problem?
For additional information regarding MDAC 2.7sp1, please refer to the
following articles:
Microsoft Data Access Components (MDAC) 2.7 Service Pack 1 Refresh
http://www.microsoft.com/downloads/details.aspx?FamilyID=9ad000f2-cae7-493d-
b0f3-ae36c570ade8&DisplayLang=en
Also, please test the suggestions in the following articles to see if it
helps.
http://dbforums.com/archives/t128594.html
http://dbforums.com/showthread.php?threadid=319606
This document contains references to a third party World Wide Web site.
Microsoft is providing this information as a convenience to you. Microsoft
does not control these sites and has not tested any software or information
found on these sites; therefore, Microsoft cannot make any representations
regarding the quality, safety, or suitability of any software or
information found there. There are inherent dangers in the use of any
software found on the Internet, and Microsoft cautions you to make sure
that you completely understand the risk before retrieving any software from
the Internet.
Please feel free to post in the group if this solves your problem or if you
would like further assistance.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Hi All,
Thank you for the possible solutions.
Saldy enough, the problem isn't solved yet.
I installed 2.7 SP1a. Rebooted, didn't work
I installed 2.8. Rebooted, didn't work.
I also tried the suggestions in the following articles
>http://dbforums.com/archives/t128594.html
>http://dbforums.com/showthread.php?threadid=319606
Didn't work either.
I unistalleded sql server, didn't work.
I installed the 2.7 SP1a, rebooted, tried, than installed
the 2.8 and it didn't help.
I installed Visual Studio .NET 2003 again, didn't solve
the problem.
I deleted the whole C:\Program Files\Common
Files\Microsoft Shared\MSDesigners98 directory on my hard
drive, and reinstalled SQL Server 2000 with SP3a, didn't
solve the problem.
But now something that I just noticed. MS office 2003 is
also installed on that machine, when I loged in with a
fresh create user account (thinking a different profile
could help) and started outlook, the same error came up,
when it tried to create a new outlook profile?
Guess I could reinstall ms office 2003 now, but don't
know if that would help...
Hope you guys might could give me some more advice?
Thank you in advance,
bEN
>--Original Message--
>Hello,
>Thanks for your post. According to the error message, it
usually indicates
>a problem with the MSDART.dll file. I would like you to
try installing MDAC
>2.7 SP1a. After rebooting, then apply SQL2K SP3a and
rebooted again. Does
>it solve your problem?
>For additional information regarding MDAC 2.7sp1, please
refer to the
>following articles:
>Microsoft Data Access Components (MDAC) 2.7 Service Pack
1 Refresh
>http://www.microsoft.com/downloads/details.aspx?
FamilyID=9ad000f2-cae7-493d-
>b0f3-ae36c570ade8&DisplayLang=en
>Also, please test the suggestions in the following
articles to see if it
>helps.
>http://dbforums.com/archives/t128594.html
>http://dbforums.com/showthread.php?threadid=319606
>This document contains references to a third party World
Wide Web site.
>Microsoft is providing this information as a convenience
to you. Microsoft
>does not control these sites and has not tested any
software or information
>found on these sites; therefore, Microsoft cannot make
any representations
>regarding the quality, safety, or suitability of any
software or
>information found there. There are inherent dangers in
the use of any
>software found on the Internet, and Microsoft cautions
you to make sure
>that you completely understand the risk before
retrieving any software from
>the Internet.
>Please feel free to post in the group if this solves
your problem or if you
>would like further assistance.
>Regards,
>Michael Shao
>Microsoft Online Partner Support
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and
confers no rights.
>.
>|||Hi Ben,
Thanks for your response. You wrote "the same error came up, when it tried
to create a new outlook profile?" Do you mean the error message is exactly
the same as the one occurring on the SQL Server? If not, please provide the
detailed error message. Based on your description, the error is mostly
caused by the version mess of the MSDART.dll file.
Please provide the following information so that I can perform further
research.
1. What is the Operation System on your side?
2. When you searched for the MSDART.dll file on your computer, how many
files did you find and what were their versions?
Before searching for the MSDART.dll file, please go to tools --> folder
options --> view tab and turn on "show hidden files and folders". UNCHECK
"hide extensions for know file types" and "hide protected operating system
files".
3. I would like you to check the MDAC version on your machine using
component checker.
It also helps us to find files that mismatch the current version of MDAC.
Please download the latest version of component checker from this URL:
http://www.microsoft.com/downloads/details.aspx?FamilyId=8F0A8DF6-4A21-4B43-
BF53-14332EF092C9&displaylang=en
Can you find the MSDART.dll file in the mismatched files list? If so, I
would like you to provide all the related information. It is best to
provide a screen shot.
Try to obtain the screen shot of the information.
1. Press the "Pr Scrn" button on the keyboard.
2. Run the Paint tools (Start?All programs?Accessories?Paint).
3. Press Ctrl+V to copy the screen shot from the memory.
4. Save as a JPEG file.
Also, such issues tend to be complex and take up extensive research time.
I'd like to set your expectations appropriately, knowing that it may take a
while for us to help you narrow down the problem. In addition, we may
eventually suggest that you reinstall the operation system. If this is
critical, I'd recommend contacting PSS and opening a Support incident
troubleshoot this further. If you need any help in this regard, please let
me know.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||I experienced the exact same problem. I was running Win2K & Sql Server sp3a. Everything was fine. I upgrade
to Win Server 2003. I got warned about running Sql Server 2000 sp2 or lower. I continued. Upgraded Win Server
2003 with all current critical patches. I run Sql Server 2003. Try to open up DTS designer and get GetTextExtentPointI
could not be located in msdart.dll. Try to open a table and I get "Unknown error: 8007007F"
msdart.dll ver is 2.80.1022.0|||Same problem for me on 3 servers that I upgraded from Windows 2000 Server to Windows 2003 Server. The SQL Enterprise Manager's DTS Package Designer will complain about not finding GetTextExtentPointI in MSDART.DLL. I also can't create a .UDL file anymore and get the same error message
My msdart.dll version is 2.80.1022.0.|||Hello,
Thanks for your post. What is the version of SQL Server (Service pack) on
your side? Based on my experience, MSDART.DLL is a MDAC component. Please
try to run MDAC component checker which is downloaded from
MDAC Utility: Component Checker
http://www.microsoft.com/downloads/details.aspx?FamilyId=8F0A8DF6-4A21-4B43-
BF53-14332EF092C9&displaylang=en
Check to see if there are some different dlls were overwritten by 3rd party
software.
Also, in order to concentrate fully on your issue, it is best that you can
post it as a separate thread. On that way each issue can receive full
attention and will also make the thread more clear and consistent for
others reference. Please feel free to open a new post for this issue and we
will be very glad to work with you. Thanks for your understanding.
Thanks again for posting in community.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||I've figured out a fix to this problem.
On a machine running Windows 2003 Server that was upgraded from Windows 2000
Server, look for OLEDB32.DLL in "c:\Program Files\Common Files\System\Ole
DB".
Check the version of OLEDB32.DLL. If it is the correct version for Windows
2003 Server, it should be (at least) version 2.80.1022.0 according to the
MDAC 2.8 manifest.
On my servers that were upgraded from Windows 2000 Server to Windows 2003
Server, that OLEDB32.DLL file was version 2.71.9042.0. The wrong version.
I copied the OLEDB32.DLL that was version 2.8.1022.0 to c:\Program
Files\Common Files\System\Ole DB on the upgraded machines, and it seems to
have solved the "The procedure entry point GetTextExtentPointI could not be
located in the DLL MSDART.dll" problem.
The correct OLEDB32.DLL can be found from the MDAC 2.8 redistributable, or
from a cleanly installed Windows 2003 Server.
""Yuan Shao"" <v-yshao@.online.microsoft.com> wrote in message
news:3AZnnvp%23DHA.2848@.cpmsftngxa06.phx.gbl...
> Hello,
> Thanks for your post. What is the version of SQL Server (Service pack) on
> your side? Based on my experience, MSDART.DLL is a MDAC component. Please
> try to run MDAC component checker which is downloaded from
> MDAC Utility: Component Checker
>
http://www.microsoft.com/downloads/details.aspx?FamilyId=8F0A8DF6-4A21-4B43-
> BF53-14332EF092C9&displaylang=en
> Check to see if there are some different dlls were overwritten by 3rd
party
> software.
> Also, in order to concentrate fully on your issue, it is best that you can
> post it as a separate thread. On that way each issue can receive full
> attention and will also make the thread more clear and consistent for
> others reference. Please feel free to open a new post for this issue and
we
> will be very glad to work with you. Thanks for your understanding.
> Thanks again for posting in community.
> Regards,
> Michael Shao
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
>

Monday, March 12, 2012

Heterogeneous queries error

Friday, March 9, 2012

HelpCannot open Crystal Reports

Wednesday, March 7, 2012

Help:I cannot subscription report

Help: why SQL 2005 is slow?

HELP: Updating column with data from another column

HELP: Updating column with data from another column

HELP: Updating column with data from another column

Friday, February 24, 2012

Help: Odd Error Mess :The table terms has been created but its max rowsize(8850)

Help: GetTextExtentPointIX could not be located?

Help Backup

Blog Archive

About Me