Hide Find/Next, View report and some export formats in report view

Hi,
1. As Chris G asked 9/7/2006 it is not possible to change text on "view
report" and "Find/Next", but is it possible to hide those fields (or at least
find/next) for all users. (this a language issue, better for us to hide
fields than show them in english).
2. Is it possible to hide som export formats to alla users, we only use PDF
and excel, and also change text "Select a format" ?
/JeromeYou only need to change in one place, rsreportserver.config (Save a copy of
the file before making any changes). Reboot after the change. The below
shows commenting out the existing CSV export.
<!--
<Extension Name="CSV"
Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering"/>
-->
The above shows how you comment out a section. If you do the above then CSV
would not be available. However, you might want to leave CSV in. Sometimes
for a large amout of data CSV is faster than Excel. If you comment out like
above and put this in:
<Extension Name="CSV"
Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
<Configuration>
<DeviceInfo>
<Encoding>ASCII</Encoding>
</DeviceInfo>
</Configuration>
</Extension>
It will change CSV export so it uses ASCII instead of Unicode. This allows
Excel to automatically open up properly for the CSV. With Unicode it all
gets put into one column and then you have to split the data.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"/jerome k" <jeromek@.discussions.microsoft.com> wrote in message
news:C1F26C7C-C276-4CD7-B9FD-D7D8B134DC80@.microsoft.com...
> Hi,
> 1. As Chris G asked 9/7/2006 it is not possible to change text on "view
> report" and "Find/Next", but is it possible to hide those fields (or at
> least
> find/next) for all users. (this a language issue, better for us to hide
> fields than show them in english).
> 2. Is it possible to hide som export formats to alla users, we only use
> PDF
> and excel, and also change text "Select a format" ?
> /Jerome|||Great, thank you...
Any ideas how to hide (or translate) the "View Report"-button,
"Find/Next"-fields and "Select a format"-text ? The Refresh icon or the
"Enter"-key is enough for refreshing, and Find/Next is a standard feature in
the browser menue.
/J
"Bruce L-C [MVP]" wrote:
> You only need to change in one place, rsreportserver.config (Save a copy of
> the file before making any changes). Reboot after the change. The below
> shows commenting out the existing CSV export.
> <!--
> <Extension Name="CSV"
> Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering"/>
> -->
> The above shows how you comment out a section. If you do the above then CSV
> would not be available. However, you might want to leave CSV in. Sometimes
> for a large amout of data CSV is faster than Excel. If you comment out like
> above and put this in:
> <Extension Name="CSV"
> Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
> <Configuration>
> <DeviceInfo>
> <Encoding>ASCII</Encoding>
> </DeviceInfo>
> </Configuration>
> </Extension>
> It will change CSV export so it uses ASCII instead of Unicode. This allows
> Excel to automatically open up properly for the CSV. With Unicode it all
> gets put into one column and then you have to split the data.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "/jerome k" <jeromek@.discussions.microsoft.com> wrote in message
> news:C1F26C7C-C276-4CD7-B9FD-D7D8B134DC80@.microsoft.com...
> > Hi,
> >
> > 1. As Chris G asked 9/7/2006 it is not possible to change text on "view
> > report" and "Find/Next", but is it possible to hide those fields (or at
> > least
> > find/next) for all users. (this a language issue, better for us to hide
> > fields than show them in english).
> > 2. Is it possible to hide som export formats to alla users, we only use
> > PDF
> > and excel, and also change text "Select a format" ?
> >
> > /Jerome
>
>|||Sorry, no.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"/jerome k" <jeromek@.discussions.microsoft.com> wrote in message
news:83377ADE-3AF9-4614-AB2D-9D9CDF9B747F@.microsoft.com...
> Great, thank you...
> Any ideas how to hide (or translate) the "View Report"-button,
> "Find/Next"-fields and "Select a format"-text ? The Refresh icon or the
> "Enter"-key is enough for refreshing, and Find/Next is a standard feature
> in
> the browser menue.
> /J
> "Bruce L-C [MVP]" wrote:
>> You only need to change in one place, rsreportserver.config (Save a copy
>> of
>> the file before making any changes). Reboot after the change. The below
>> shows commenting out the existing CSV export.
>> <!--
>> <Extension Name="CSV"
>> Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering"/>
>> -->
>> The above shows how you comment out a section. If you do the above then
>> CSV
>> would not be available. However, you might want to leave CSV in.
>> Sometimes
>> for a large amout of data CSV is faster than Excel. If you comment out
>> like
>> above and put this in:
>> <Extension Name="CSV"
>> Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
>> <Configuration>
>> <DeviceInfo>
>> <Encoding>ASCII</Encoding>
>> </DeviceInfo>
>> </Configuration>
>> </Extension>
>> It will change CSV export so it uses ASCII instead of Unicode. This
>> allows
>> Excel to automatically open up properly for the CSV. With Unicode it all
>> gets put into one column and then you have to split the data.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "/jerome k" <jeromek@.discussions.microsoft.com> wrote in message
>> news:C1F26C7C-C276-4CD7-B9FD-D7D8B134DC80@.microsoft.com...
>> > Hi,
>> >
>> > 1. As Chris G asked 9/7/2006 it is not possible to change text on
>> > "view
>> > report" and "Find/Next", but is it possible to hide those fields (or at
>> > least
>> > find/next) for all users. (this a language issue, better for us to hide
>> > fields than show them in english).
>> > 2. Is it possible to hide som export formats to alla users, we only use
>> > PDF
>> > and excel, and also change text "Select a format" ?
>> >
>> > /Jerome
>>

hidden textbox

Hi,
I want to hide/show some text in a textbox based on a condition.
It works fine , but the problem is when the text is hidden it also hides the
borders (top, left, right, bottom) with it.
Is there a way to hide only text and not the borders.
Thankyou.What you can do is set the value of the textbox to nothing based on the
condition
instead of hiding the text box itself. That way you can have the borders
around the text box and its content set as nothing.
"Ni" wrote:
> Hi,
> I want to hide/show some text in a textbox based on a condition.
> It works fine , but the problem is when the text is hidden it also hides the
> borders (top, left, right, bottom) with it.
> Is there a way to hide only text and not the borders.
> Thankyou.
>|||Put the textbox inside of rectangle. Have border on rectangle. Hide textbox
only.
--
Alex Mineev
Software Design Engineer. Report expressions; Code Access Security; Xml;
SQE.
This posting is provided "AS IS" with no warranties, and confers no rights
"Ni" <Ni@.discussions.microsoft.com> wrote in message
news:00BCADBC-5B16-4916-A265-C7E8288529B6@.microsoft.com...
> Hi,
> I want to hide/show some text in a textbox based on a condition.
> It works fine , but the problem is when the text is hidden it also hides
the
> borders (top, left, right, bottom) with it.
> Is there a way to hide only text and not the borders.
> Thankyou.
>

Hidden Property - "Deserialization failed: is not a Valid Value" E

If I add an expression to the visibility property on a text box, table row,
etc.. and then remove that same expression, I get the following error message
in preview:
"deserialization failed: is not a valid value..."
I get an option to edit the XML code. When I look at the code, I find the
<Hidden /> tag. But get this, there is no </Hidden> tag. Apparently, VS is
removing only one side of the Hidden tag when I delete the expression.
I used to be able to edit the code, but now I can't even edit the code to
take the remaining tag out. With this error my rdl file became, for all
intents and purposes, corrupt. Luckily I had posted it to a dev Report
Manager site earlier and was able to recover it.
Has anyone seen this?
Also, not being able to close VS2005 because of the the "Modal Dialog
Open..." box is killing me. Anyone have a solution to that yet besides
killing the devenv process?
Thanks in Advance,
bsod55I just had a similar error - I had to open the file and remove the Hidden
tags. In the IDE I right clicked on the file and selected Open from the menu
- this gave me the XML version and allowed me to remove the tags.
--
mal
"bsod55" wrote:
> If I add an expression to the visibility property on a text box, table row,
> etc.. and then remove that same expression, I get the following error message
> in preview:
> "deserialization failed: is not a valid value..."
> I get an option to edit the XML code. When I look at the code, I find the
> <Hidden /> tag. But get this, there is no </Hidden> tag. Apparently, VS is
> removing only one side of the Hidden tag when I delete the expression.
> I used to be able to edit the code, but now I can't even edit the code to
> take the remaining tag out. With this error my rdl file became, for all
> intents and purposes, corrupt. Luckily I had posted it to a dev Report
> Manager site earlier and was able to recover it.
> Has anyone seen this?
> Also, not being able to close VS2005 because of the the "Modal Dialog
> Open..." box is killing me. Anyone have a solution to that yet besides
> killing the devenv process?
> Thanks in Advance,
> bsod55

Hi i have var which has more than 8000 characters

Hi i heard that in sql server 2005 they introduced MAX to replace text data
type like strtemp varchar(MAX)... i have sql server 2005 and i am trying to
use it but does seem like working ... i guess the only reason is... my
database server still on sql server 2000 but on client i have sql server 200
5
where i am creating store procs tables view etcsss.
kindly any one can tell me how to solve the problem more than 8000 ... thank
samjad,
Can you tell us what are you trying to accomplish?.
AMB
"amjad" wrote:
> Hi i heard that in sql server 2005 they introduced MAX to replace text dat
a
> type like strtemp varchar(MAX)... i have sql server 2005 and i am trying t
o
> use it but does seem like working ... i guess the only reason is... my
> database server still on sql server 2000 but on client i have sql server 2
005
> where i am creating store procs tables view etcsss.
> kindly any one can tell me how to solve the problem more than 8000 ... thanks[/col
or]|||Hi i have two database one table called source and the other is dest...
dest is identifical of source table... both table has 104 fields when their
changed in source table then i wrote a script to track the changes and updat
e
the dest table... their is too many fields in both table like i am writing
dynamic sql query
first i create a cursor which has columnsname in while loop i do some thing
like
SET @.SQL='SELECT ' + @.DestID + ' FROM ' + @.Source_Table + 'INNER JOIN ' +
@.Full_DesTable_Name + ' ON ' + @.SourceID + ' = ' +
@.DestID + 'Where ('
fetch next from cur into @.columnname
while
begin
set @.sql=@.sql + @.Source_Table + '.[' + @.columnname + ']<> ' +
@.Full_DesTable_Name + '.[' + @.columnname + '] Or '
fetch next from cur into @.columnname
end
due to large amount of columns and each columns name is more than 80
character produce a larger string which in my case is 12000
if i dont do that method i have to create view to get changed fields ...in
which i have to set up each field in source to each field in destination mea
n
i have to write a very large where clause which is not possible... this
store proc create a sql query for me where i get the column info..... and
then put in that string in such a way which generate ...
ok i have done this method with two varchar(8000) variable but if i can do
that in one variable that will be great thanks
"Alejandro Mesa" wrote:
> amjad,
> Can you tell us what are you trying to accomplish?.
>
> AMB
> "amjad" wrote:
>|||This frustrates me at times as well. One idea I've had, but have never
put into practice is to store my pieces of my statements as rows in a
temporary table, then have some dynamic sql to build up the dynamic sql
in the table into a format exec (@.sql1 + @.sql2... etc.) then run that,
but at this point it's getting silly, so I've never actually tried it.
It might make for quite a re-usable bit of code though ;)
Cheers
Will|||amjad,
May be functions "checksum" or "binary_checksum" can help you.
Example:
use northwind
go
create table t1 (
c1 int primary key,
c2 char(1)
)
go
create table t2 (
c1 int primary key,
c2 char(1)
)
go
create view v1
as
select c1, c2, binary_checksum(*) as c3
from t1
go
create view v2
as
select c1, c2, binary_checksum(*) as c3
from t2
go
insert into t1 values(1, 'a')
insert into t1 values(2, 'b')
insert into t1 values(3, 'A')
insert into t2 values(1, 'a')
insert into t2 values(2, 'a')
insert into t2 values(3, 'a')
select v1.*, v2.*
from v1 inner join v2
on v1.c1 = v2.c1
where v1.c3 != v2.c3
go
drop view v1, v2
drop table t1, t2
go
I can not assure that this method is reliable.
AMB
"amjad" wrote:
> Hi i have two database one table called source and the other is dest...
> dest is identifical of source table... both table has 104 fields when the
ir
> changed in source table then i wrote a script to track the changes and upd
ate
> the dest table... their is too many fields in both table like i am writing
> dynamic sql query
> first i create a cursor which has columnsname in while loop i do some thin
g
> like
> SET @.SQL='SELECT ' + @.DestID + ' FROM ' + @.Source_Table + 'INNER JOIN ' +
> @.Full_DesTable_Name + ' ON ' + @.SourceID + ' = ' +
> @.DestID + 'Where ('
> fetch next from cur into @.columnname
> while
> begin
> set @.sql=@.sql + @.Source_Table + '.[' + @.columnname + ']<> ' +
> @.Full_DesTable_Name + '.[' + @.columnname + '] Or '
> fetch next from cur into @.columnname
> end
> due to large amount of columns and each columns name is more than 80
> character produce a larger string which in my case is 12000
>
> if i dont do that method i have to create view to get changed fields ...i
n
> which i have to set up each field in source to each field in destination m
ean
> i have to write a very large where clause which is not possible... this
> store proc create a sql query for me where i get the column info..... and
> then put in that string in such a way which generate ...
> ok i have done this method with two varchar(8000) variable but if i can do
> that in one variable that will be great thanks
> "Alejandro Mesa" wrote:
>

hi , query for displaying data

i want to show selected data from my database table in the text boxes using select statment, can anyone help me out pl for displayin selected data from database directly in text boxes..It depends on what language you are programming (C#, VB, Java).
So, please post the question on the specific forum, to get the right answer quicker.|||sorry m new to this site, so didnt knew..
i m using vb.net language in my asp.net project.. can u help me or show me code that can shows the database rows into textboxes in asp.net page with select query..

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

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: update a table from a text file

I have a field in a DB that I want to update with the contents of a
text file. I retrieved the contents of the text file FROM the entry
but now that I edited them, I want to put them back.
This text file has only a single entry in it. No row/column
delimeters needed.I tried using the Enterprise Manager import data
wizard but it doesn't do what I want.
I guess the sql statement would look something like this:
Update mytable
Set data = contents of textfile
WHERE data LIKE '%uniquething%'
HELP
JohnYou could set up a linked server to the text file and read the value in
TSQL. Pls have a look at the example "F. Using the Microsoft OLE DB Provider
for Jet to access a text file" at
http://msdn2.microsoft.com/en-us/library/ms190479.aspx for more info.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

HELP: update a table from a text file

I have a field in a DB that I want to update with the contents of a
text file. I retrieved the contents of the text file FROM the entry
but now that I edited them, I want to put them back.
This text file has only a single entry in it. No row/column
delimeters needed.I tried using the Enterprise Manager import data
wizard but it doesn't do what I want.
I guess the sql statement would look something like this:
Update mytable
Set data = contents of textfile
WHERE data LIKE '%uniquething%'
HELP
John
You could set up a linked server to the text file and read the value in
TSQL. Pls have a look at the example "F. Using the Microsoft OLE DB Provider
for Jet to access a text file" at
http://msdn2.microsoft.com/en-us/library/ms190479.aspx for more info.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

HELP: Text Field data type in SQL Server

I have a text field in a txt file.
It has multi-line.
Id like to copy / paste this text in a SQL Server table field.
I guess the field must be of a Text Data Type.
I tried it and it retains only the first line.
How can I paste that multimeline text from the SQL Server tools?
I tried from Management Studio but it does not work.
txs for your answer
In past I have used bulk insert to copy text file data into SQL server
text field. Are you specifying any delimiter or terminator for text
file?

HELP: Text Field data type in SQL Server

I have a text field in a txt file.
It has multi-line.
Id like to copy / paste this text in a SQL Server table field.
I guess the field must be of a Text Data Type.
I tried it and it retains only the first line.
How can I paste that multimeline text from the SQL Server tools?
I tried from Management Studio but it does not work.
txs for your answerIn past I have used bulk insert to copy text file data into SQL server
text field. Are you specifying any delimiter or terminator for text
file?

HELP: Text Field data type in SQL Server

I have a text field in a txt file.
It has multi-line.
Id like to copy / paste this text in a SQL Server table field.
I guess the field must be of a Text Data Type.
I tried it and it retains only the first line.
How can I paste that multimeline text from the SQL Server tools?
I tried from Management Studio but it does not work.
txs for your answerIn past I have used bulk insert to copy text file data into SQL server
text field. Are you specifying any delimiter or terminator for text
file?

HELP: Exporting for use in MySQL

Ok, anyone know anything about MySQL? The only way I see to use data within
tables for use in MySQL is to export as text file but I dont see a way to
import the data from the MySQL Console Manager.
Then my next consideration would be a query to create the tables and insert
it into te MySQL database.
Thoughts anyone?
"Matrix"
Vice President
Permian Basin Chapter
Bikers Against Child AbuseLots on MySQL here:
http://www.mysql.com/documentation/index.html
--
David Portas
--
Please reply only to the newsgroup
--
"Matrix" <matrix@.!nospam.visualsun.ws> wrote in message
news:vnjs143fb0q9a1@.corp.supernews.com...
> Ok, anyone know anything about MySQL? The only way I see to use data
within
> tables for use in MySQL is to export as text file but I dont see a way to
> import the data from the MySQL Console Manager.
> Then my next consideration would be a query to create the tables and
insert
> it into te MySQL database.
> Thoughts anyone?
> "Matrix"
> Vice President
> Permian Basin Chapter
> Bikers Against Child Abuse
>

HELP: "text and varchar are incompatible" agrivation!

I am trying to do a simple select statement on my db but keep getting the fallowing exception being thrown ...

System.Web.Services.Protocols.SoapException was unhandled
Actor=""
Lang=""
Message="System.Web.Services.Protocols.SoapException: Server was unable to process request. --> System.Data.SqlClient.SqlException: The data types text and varchar are incompatible in the equal to operator.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at RelayService.ValidAccessID(String ID) in C:\Documents and Settings\Dean\My Documents\Visual Studio 2005\Projects\MsgSRX\MsgSRXService\App_Code\RelayService.vb:line 193
at RelayService.SendMessage(String msg, String AccessID, Int64& MsgID) in C:\Documents and Settings\Dean\My Documents\Visual Studio 2005\Projects\MsgSRX\MsgSRXService\App_Code\RelayService.vb:line 377
-- End of inner exception stack trace --"
Node=""
Role=""
Source="System.Web.Services"
StackTrace:
at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at ChatClient.MsgSRX.RelayService.SendMessage(String msg, String AccessID, Int64& MsgID) in C:\Documents and Settings\Dean\My Documents\Visual Studio 2005\Projects\MsgSRX\ChatClient\Web References\MsgSRX\Reference.vb:line 342
at ChatClient.Form1.btnSend_Click(Object sender, EventArgs e) in C:\Documents and Settings\Dean\My Documents\Visual Studio 2005\Projects\MsgSRX\ChatClient\Form1.vb:line 13
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(ApplicationContext context)
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
at ChatClient.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()

... Here is the source code to the method I am using ...

Private Function ValidAccessID(ByVal ID As String) As Boolean
Dim conn As New SqlConnection()
conn.ConnectionString = ConfigurationManager.ConnectionStrings("dbConnect").ConnectionString

Dim cmd As String
cmd = "SELECT AccessID, ExpireTime "
cmd &= "FROM AccessIDNumbers "
cmd &= "WHERE AccessID IN ('" & ID & "')"
' cmd &= "WHERE AccessID ='@.ID'"
Dim C As New SqlCommand(cmd, conn)
' C.Parameters.AddWithValue("@.ID", New SqlTypes.SqlString(ID))
' C.Parameters.Item("@.ID").SqlDbType = SqlDbType.Text

conn.Open()

Dim Count As Integer = 0
Dim reader As SqlDataReader = C.ExecuteReader
Dim expired As Boolean = False
If reader.HasRows Then
While reader.Read
Count += 1
Dim et As SqlTypes.SqlDateTime
et = SqlTypes.SqlDateTime.Parse(reader("ExpireTime"))
Dim ct As New SqlTypes.SqlDateTime(Now)
If ct > et AndAlso expired = False Then expired = True
End While
End If

conn.Close()
C.Dispose()
C = Nothing
conn.Dispose()
conn = Nothing

Return expired = False
End Function

... the problem is with the cmd &= "WHERE AccessID IN ('" & ID & "')" statement. How Can I get this to work properly!!! My data types for the AccessIDNumbers table are as fallows ..

UserName -> text -> nulls not allowedAccessID -> text -> nulls not allowedCreationTime -> datatime -> nulls not allowedExpireTime -> datatime -> nulls not allowedCurrentTable -> text -> nulls allowed

I don't understand why I'm getting this error!!!

Are you actually using 'text' columns, or 'varchar' columns?

|||I'm using text columns. What is the difference between text and varchar? They seem like they should be basically the same thing and as such the db engine should be able to compare them?|||

The 'text' datatype is a BLOB, which in short means that it is stored in a separate location than the rest of the data in the record. Instead of storing the actual value, the record stores a 16-bit pointer that directs SQL to the actual file that stores this data (by default, anyway. this can be changed). The max for this type of field is 2 GB.

'varchar,' on the other hand, is a standard type, which means it is stored with the actual record data. The limit for this type is 8,000 characters (=8 KB). This is actually preferable to a 'text' column if you are using 8,000 or fewer characters.

If you still want to use the text datatype, you may need to do conversions in your SQL statements (I haven't tried this--just a suggestion to see if you can get it to work).

' instead of thiscmd &="WHERE AccessID IN ('" & ID & "')"' try thiscmd &= "WHERE CAST(AccessID AS VARCHAR) ='" & ID & "'"