convert a number into words

can anyone help me how to convert a number into words using crystal report formula?
thanksuse Towords() function.|||hi.. vidubhushan, thanx for your reply|||Also you need to truncate the last xx/100 part if it exists

convert a non-partitioned tables into partitioned Options

Hi,
we have a large database containing many tables and lots of data. now
because of the number of records we decided to use partitions.
for this we have created partition function and partition scheme. the
problem is : How can we convert non-partitioned table into
partitioned one according to a partition scheme?
in the documentation it introduced 2 methods but I don't know are the
usable or not? if they are, how can I use them?
the clustered index is the primary key and there are some
foreign keys which are dependent on this one so it can't be droped.
and the base field which the table is going to be partitioned on that
is not primary key.
the other question is: when creating a table if we do not have primary
key , it
accepts the "ON PartitionScheme(FieldName)" but whenever we have
primary key then it says it is created on [primary] istead of the
partition scheme provided.
by the way, the field which the table is going to be partitioned
based
on it is a foreign key.
Thanks a lot,
Ali
> we have a large database containing many tables and lots of data. now
> because of the number of records we decided to use partitions.
> for this we have created partition function and partition scheme.
Just because you have a lot of rows doesn't mean you need to partition.
Partitioning can help manageability of tables that are difficult to manage
due to size and can help performance of specialized operations. If your
tables are large and performance is slow, index and query tuning is the best
initial approach. Adding partitioning to the mix without tuning can slow
queries further.

> the clustered index is the primary key and there are some
> foreign keys which are dependent on this one so it can't be dropped.
> and the base field which the table is going to be partitioned on that
> is not primary key.
The definition of a partitioned table is either a partitioned heap or a
table with a partitioned clustered index. If you want to partition this
table on the FK column, you'll need to either add the partitioning column to
the primary key or create a partitioned clustered index that includes the
partitioning column and change the primary key to a non-partitioned
non-clustered index. Note that when you partition non-clustered indexes
differently than the base table, you lose some of the manageability
benefits.

> the other question is: when creating a table if we do not have primary
> key , it
> accepts the "ON PartitionScheme(FieldName)" but whenever we have
> primary key then it says it is created on [primary] istead of the
> partition scheme provided.
The clustered primary key specification takes precedence over the table
create ON clause. If the primary key is non-clustered, then you'll end up
with a partitioned table and a non-partitioned index.
In addition to the Books Online, see Kimberly Tripp's white paper for a
thorough partitioning discussion:
[url]http://www.sqlskills.com/resources/Whitepapers/Partitioning%20in%20SQL%20Server%202005%20Beta%20I I.htm[/url]
Hope this helps.
Dan Guzman
SQL Server MVP
"Ali" <nikzad.a@.gmail.com> wrote in message
news:4bace43a-e047-4305-bf7a-de96542feb5b@.v4g2000hsf.googlegroups.com...
> Hi,
> we have a large database containing many tables and lots of data. now
> because of the number of records we decided to use partitions.
> for this we have created partition function and partition scheme. the
> problem is : How can we convert non-partitioned table into
> partitioned one according to a partition scheme?
> in the documentation it introduced 2 methods but I don't know are the
> usable or not? if they are, how can I use them?
> the clustered index is the primary key and there are some
> foreign keys which are dependent on this one so it can't be droped.
> and the base field which the table is going to be partitioned on that
> is not primary key.
> the other question is: when creating a table if we do not have primary
> key , it
> accepts the "ON PartitionScheme(FieldName)" but whenever we have
> primary key then it says it is created on [primary] istead of the
> partition scheme provided.
>
> by the way, the field which the table is going to be partitioned
> based
> on it is a foreign key.
>
>
> Thanks a lot,
> Ali
>

convert a non-partitioned tables into partitioned Options

Hi,
we have a large database containing many tables and lots of data. now
because of the number of records we decided to use partitions.
for this we have created partition function and partition scheme. the
problem is : How can we convert non-partitioned table into
partitioned one according to a partition scheme?
in the documentation it introduced 2 methods but I don't know are the
usable or not? if they are, how can I use them?
the clustered index is the primary key and there are some
foreign keys which are dependent on this one so it can't be droped.
and the base field which the table is going to be partitioned on that
is not primary key.
the other question is: when creating a table if we do not have primary
key , it
accepts the "ON PartitionScheme(FieldName)" but whenever we have
primary key then it says it is created on [primary] istead of the
partition scheme provided.
by the way, the field which the table is going to be partitioned
based
on it is a foreign key.
Thanks a lot,
Ali> we have a large database containing many tables and lots of data. now
> because of the number of records we decided to use partitions.
> for this we have created partition function and partition scheme.
Just because you have a lot of rows doesn't mean you need to partition.
Partitioning can help manageability of tables that are difficult to manage
due to size and can help performance of specialized operations. If your
tables are large and performance is slow, index and query tuning is the best
initial approach. Adding partitioning to the mix without tuning can slow
queries further.
> the clustered index is the primary key and there are some
> foreign keys which are dependent on this one so it can't be dropped.
> and the base field which the table is going to be partitioned on that
> is not primary key.
The definition of a partitioned table is either a partitioned heap or a
table with a partitioned clustered index. If you want to partition this
table on the FK column, you'll need to either add the partitioning column to
the primary key or create a partitioned clustered index that includes the
partitioning column and change the primary key to a non-partitioned
non-clustered index. Note that when you partition non-clustered indexes
differently than the base table, you lose some of the manageability
benefits.
> the other question is: when creating a table if we do not have primary
> key , it
> accepts the "ON PartitionScheme(FieldName)" but whenever we have
> primary key then it says it is created on [primary] istead of the
> partition scheme provided.
The clustered primary key specification takes precedence over the table
create ON clause. If the primary key is non-clustered, then you'll end up
with a partitioned table and a non-partitioned index.
In addition to the Books Online, see Kimberly Tripp's white paper for a
thorough partitioning discussion:
http://www.sqlskills.com/resources/Whitepapers/Partitioning%20in%20SQL%20Server%202005%20Beta%20II.htm
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ali" <nikzad.a@.gmail.com> wrote in message
news:4bace43a-e047-4305-bf7a-de96542feb5b@.v4g2000hsf.googlegroups.com...
> Hi,
> we have a large database containing many tables and lots of data. now
> because of the number of records we decided to use partitions.
> for this we have created partition function and partition scheme. the
> problem is : How can we convert non-partitioned table into
> partitioned one according to a partition scheme?
> in the documentation it introduced 2 methods but I don't know are the
> usable or not? if they are, how can I use them?
> the clustered index is the primary key and there are some
> foreign keys which are dependent on this one so it can't be droped.
> and the base field which the table is going to be partitioned on that
> is not primary key.
> the other question is: when creating a table if we do not have primary
> key , it
> accepts the "ON PartitionScheme(FieldName)" but whenever we have
> primary key then it says it is created on [primary] istead of the
> partition scheme provided.
>
> by the way, the field which the table is going to be partitioned
> based
> on it is a foreign key.
>
>
> Thanks a lot,
> Ali
>sqlsql

convert a non-partitioned tables into partitioned Options

Hi,
we have a large database containing many tables and lots of data. now
because of the number of records we decided to use partitions.
for this we have created partition function and partition scheme. the
problem is : How can we convert non-partitioned table into
partitioned one according to a partition scheme?
in the documentation it introduced 2 methods but I don't know are the
usable or not? if they are, how can I use them?
the clustered index is the primary key and there are some
foreign keys which are dependent on this one so it can't be droped.
and the base field which the table is going to be partitioned on that
is not primary key.
the other question is: when creating a table if we do not have primary
key , it
accepts the "ON PartitionScheme(FieldName)" but whenever we have
primary key then it says it is created on [primary] istead of the
partition scheme provided.
by the way, the field which the table is going to be partitioned
based
on it is a foreign key.
Thanks a lot,
Ali> we have a large database containing many tables and lots of data. now
> because of the number of records we decided to use partitions.
> for this we have created partition function and partition scheme.
Just because you have a lot of rows doesn't mean you need to partition.
Partitioning can help manageability of tables that are difficult to manage
due to size and can help performance of specialized operations. If your
tables are large and performance is slow, index and query tuning is the best
initial approach. Adding partitioning to the mix without tuning can slow
queries further.

> the clustered index is the primary key and there are some
> foreign keys which are dependent on this one so it can't be dropped.
> and the base field which the table is going to be partitioned on that
> is not primary key.
The definition of a partitioned table is either a partitioned heap or a
table with a partitioned clustered index. If you want to partition this
table on the FK column, you'll need to either add the partitioning column to
the primary key or create a partitioned clustered index that includes the
partitioning column and change the primary key to a non-partitioned
non-clustered index. Note that when you partition non-clustered indexes
differently than the base table, you lose some of the manageability
benefits.

> the other question is: when creating a table if we do not have primary
> key , it
> accepts the "ON PartitionScheme(FieldName)" but whenever we have
> primary key then it says it is created on [primary] istead of the
> partition scheme provided.
The clustered primary key specification takes precedence over the table
create ON clause. If the primary key is non-clustered, then you'll end up
with a partitioned table and a non-partitioned index.
In addition to the Books Online, see Kimberly Tripp's white paper for a
thorough partitioning discussion:
http://www.sqlskills.com/resources/...20Beta%20II.htm
Hope this helps.
Dan Guzman
SQL Server MVP
"Ali" <nikzad.a@.gmail.com> wrote in message
news:4bace43a-e047-4305-bf7a-de96542feb5b@.v4g2000hsf.googlegroups.com...
> Hi,
> we have a large database containing many tables and lots of data. now
> because of the number of records we decided to use partitions.
> for this we have created partition function and partition scheme. the
> problem is : How can we convert non-partitioned table into
> partitioned one according to a partition scheme?
> in the documentation it introduced 2 methods but I don't know are the
> usable or not? if they are, how can I use them?
> the clustered index is the primary key and there are some
> foreign keys which are dependent on this one so it can't be droped.
> and the base field which the table is going to be partitioned on that
> is not primary key.
> the other question is: when creating a table if we do not have primary
> key , it
> accepts the "ON PartitionScheme(FieldName)" but whenever we have
> primary key then it says it is created on [primary] istead of the
> partition scheme provided.
>
> by the way, the field which the table is going to be partitioned
> based
> on it is a foreign key.
>
>
> Thanks a lot,
> Ali
>

convert a negative number to 0

Is it possible to convert a negative number to 0?

for example -4 need to be 0 and 3 needs to be 3.

try this

SELET (CASE WHEN MyField <= 0 THEN 0 ELSE MyField END) AS MyNonNegativeField
FROM MyTable|||That will work, except that I have a very lengthy expression for "MyField". I don't want to have to duplicate that expression.|||

If you really don't want to repeat the expression then you could turn your query into a derived table and then SELECT from the derived table, incorporating the CASE logic into that SELECT list.

SELECT t.Column1,
CASE WHEN t.Column2 < 0 THEN 0 ELSE t.Column2 END

FROM (SELECT <My Complex Expression1> AS Column1,

<My Complex Expression2> AS Column2

FROM etc...) t

Chris

|||

Another very simple way is UDF,

Create Function dbo.NegativeToZero(@.Value as int)

returns Int

as

Begin

Return Case When @.Value < 0 Then 0 else @.Value End;

End

Go

Select {Your Columns}, ....., dbo.NegativeToZero(Your Lengthy Expression) as Data

From

YourTable

|||

Just be aware that introducing a UDF into a query often has a negative impact on performance. Better performance is usually obtained by incorporating a UDF's code into the body of the query (although I appreciate that this isn't always possible).

Chris

Conversion of DTS to SSIS command Line

I am trying to convert a command line using the dtexecui utility. I need to pass three parameters ; account number ,begin and end date to project.

What am i doing wrong ?

DTEXEC /DTS "\File System\Archive Data" /SERVER SRV2 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW \package /SET "Account_Number";"'00001'" /SET "File_Name";"'C:\Inetpub\wwwroot\output\Archive\'" /SET "Begin_Date";"'04/03/2006'" /SET "End_Date";"'04/04/2006'"

Error I get

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 9:52:49 AM
Warning: 2006-04-05 09:52:51.58
Code: 0x80012018
Source: Archive Data
Description: The configuration entry, "Account_Number", has an incorrect form
at because it does not begin with the package delimiter. Prepend "\package" to t
he package path.
End Warning
Warning: 2006-04-05 09:52:51.58
Code: 0x80012017
Source: Archive Data
Description: The package path referenced an object that cannot be found: "Acc
ount_Number". This occurs when an attempt is made to resolve a package path to a
n object that cannot be found.
End Warning
DTExec: Could not set Account_Number value to '00001'.
Started: 9:52:49 AM
Finished: 9:52:51 AM
Elapsed: 2.172 seconds

Your command line is not correct as each set command needs a package path starting with \package just as the error message indicates. As I don't know tasks these properties belong to I can't give you the exact path but in general the set option should look something like "\Package.rest_of_path_to_property". You can use the configurations on the package to identify what the package path should look like. You should also remove the \package from the command line outside of the set because that is invalid.

HTH,

Matt

Conversion from SQL 7.0 to SQL 2000

We have recently converted a SQL 7.0 database to SQL
2000. There are a number of tables that have default
values set for inserting data. When accessing via the
front-end application (ASP page), the tables are not
inserting the default values instead it is inserting
<NULL> into the field. Any insight?Tom,
Thanks for the quick response. I did include the defaults
in the migration and the tables indicates the default
values (as well as the sp_help on the table). Our front
end application was running successfully when using the
SQL 7.0 database. We are not explicitly inserting the
NULL values into the table. If there are no values for
the specific fields it should just insert the default
values assigned in the tables. Am I missing something?
Laura
>--Original Message--
>Two possibilities:
>1. The defaults were not added in the migration. Use
sp_help on the table in question.
>2. The front-end is actually explicitly inserting
NULL's. Use the Profiler to find out.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Laura Reynolds" <lreynolds@.dialamerica.com> wrote in
message news:0a6901c35762$54949120$a601280a@.phx.gbl...
>We have recently converted a SQL 7.0 database to SQL
>2000. There are a number of tables that have default
>values set for inserting data. When accessing via the
>front-end application (ASP page), the tables are not
>inserting the default values instead it is inserting
><NULL> into the field. Any insight?
>|||This is a multi-part message in MIME format.
--=_NextPart_000_000D_01C35787.64B12BC0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
This isn't an INSERT statement, since the word INSERT does not appear =anywhere in it. I'd be looking for something along the lines of:
INSERT MyTable (Col1, Col2) VALUES ('X', 3)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"jp" <jp@.p.com> wrote in message =news:eADPuc6VDHA.1676@.TK2MSFTNGP10.phx.gbl...
Tom,
this is the insert statement:
exec sp_cursor 180150001, 4, 0, N'tbl_Worksheets', @.L2 =3D '01', @.RepID ==3D '886048', @.SalesWk =3D 30, @.Name =3D 'FORTUNE, AMY = ', @.BranchNo =3D '239', @.Job1 =3D '32', @.Hours1 =3D =36.00, @.WMU1 =3D 0.00, @.Comp1 =3D 453.60, @.Sales1 =3D 63, @.Sales_Comp1 ==3D 165.60, @.Sales_UC1 =3D 70.55, @.Cancels1 =3D 8, @.Cancels_Comp1 =3D =0.00, @.Cancels_UC1 =3D 8.75, @.Job2 =3D NULL, @.Hours2 =3D NULL, @.WMU2 =3D =NULL, @.Comp2 =3D NULL, @.Sales2 =3D NULL, @.Sales_Comp2 =3D NULL, =@.Sales_UC2 =3D NULL, @.Cancels2 =3D NULL, @.Cancels_Comp2 =3D NULL, =@.Cancels_UC2 =3D NULL, @.Job3 =3D NULL, @.Hours3 =3D NULL, @.WMU3 =3D NULL, =@.Comp3 =3D NULL, @.Sales3 =3D NULL, @.Sales_Comp3 =3D NULL, @.Sales_UC3 =3D =NULL, @.Cancels3 =3D NULL, @.Cancels_Comp3 =3D NULL, @.Cancels_UC3 =3D =NULL, @.Downtime =3D 0.00, @.TWIG =3D 0.00, @.Referral_Bonus =3D 0.00, =@.Add_Comp =3D 60.00, @.Inc_Bonus =3D 0.00, @.Other_Bonus =3D 0.00, =@.Other_Doe =3D ' ', @.Guarantee =3D 8.00, @.TimeStamp =3D NULL
thanks
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:O768Gn2VDHA.1676@.TK2MSFTNGP10.phx.gbl...
What did the Profiler trace give you, i.e. what was the exact T-SQL =INSERT statement going into SQL Server. Also, have you tried something =like the following in QA?:
insert MyTable default values
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Laura Reynolds" <lreynolds@.dialamerica.com> wrote in message =news:5a6201c35767$b817c780$a001280a@.phx.gbl...
Tom,
Thanks for the quick response. I did include the defaults in the migration and the tables indicates the default values (as well as the sp_help on the table). Our front end application was running successfully when using the SQL 7.0 database. We are not explicitly inserting the NULL values into the table. If there are no values for the specific fields it should just insert the default values assigned in the tables. Am I missing something?
Laura
>--Original Message--
>Two possibilities:
>
>1. The defaults were not added in the migration. Use sp_help on the table in question.
>2. The front-end is actually explicitly inserting NULL's. Use the Profiler to find out.
>
>-- >Tom
>
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>
>"Laura Reynolds" <lreynolds@.dialamerica.com> wrote in message news:0a6901c35762$54949120$a601280a@.phx.gbl...
>We have recently converted a SQL 7.0 database to SQL >2000. There are a number of tables that have default >values set for inserting data. When accessing via the >front-end application (ASP page), the tables are not >inserting the default values instead it is inserting ><NULL> into the field. Any insight?
> --=_NextPart_000_000D_01C35787.64B12BC0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

This isn't an INSERT statement, since =the word INSERT does not appear anywhere in it. I'd be looking for =something along the lines of:
INSERT MyTable (Col1, Col2) =VALUES ('X', 3)
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"jp" wrote in =message news:eADPuc6VDHA.1676=@.TK2MSFTNGP10.phx.gbl...
Tom,
this is the insert =statement:
exec sp_cursor 180150001, 4, 0, =N'tbl_Worksheets', @.L2 =3D '01', @.RepID =3D '886048', @.SalesWk =3D 30, @.Name =3D ='FORTUNE, AMY &nbs=p;  =; = ', @.BranchNo =3D '239', @.Job1 =3D '32', @.Hours1 =3D 36.00, @.WMU1 =3D =0.00, @.Comp1 =3D 453.60, @.Sales1 =3D 63, @.Sales_Comp1 =3D 165.60, @.Sales_UC1 =3D 70.55, =@.Cancels1 =3D 8, @.Cancels_Comp1 =3D 0.00, @.Cancels_UC1 =3D 8.75, @.Job2 =3D NULL, @.Hours2 ==3D NULL, @.WMU2 =3D NULL, @.Comp2 =3D NULL, @.Sales2 =3D NULL, @.Sales_Comp2 =3D NULL, =@.Sales_UC2 =3D NULL, @.Cancels2 =3D NULL, @.Cancels_Comp2 =3D NULL, @.Cancels_UC2 =3D NULL, =@.Job3 =3D NULL, @.Hours3 =3D NULL, @.WMU3 =3D NULL, @.Comp3 =3D NULL, @.Sales3 =3D NULL, =@.Sales_Comp3 =3D NULL, @.Sales_UC3 =3D NULL, @.Cancels3 =3D NULL, @.Cancels_Comp3 =3D NULL, =@.Cancels_UC3 =3D NULL, @.Downtime =3D 0.00, @.TWIG =3D 0.00, @.Referral_Bonus =3D 0.00, =@.Add_Comp =3D 60.00, @.Inc_Bonus =3D 0.00, @.Other_Bonus =3D 0.00, @.Other_Doe =3D ' ', =@.Guarantee =3D 8.00, @.TimeStamp =3D NULL
thanks
"Tom Moreau" = wrote in message news:O768Gn2VDHA.1676=@.TK2MSFTNGP10.phx.gbl...
What did the Profiler trace give =you, i.e. what was the exact T-SQL INSERT statement going into SQL Server. =Also, have you tried something like the following in QA?:

insert MyTable default values
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"Laura Reynolds" --Original Message-->Two =possibilities:>>1. The defaults were not added in the migration. Use sp_help on the =table in question.>2. The front-end is actually =explicitly inserting NULL's. Use the Profiler to find =out.>>-- =>Tom>>---=---->Thomas A. Moreau, BSc, PhD, MCSE, MCDBA>SQL Server =MVP>Columnist, SQL Server Professional>Toronto, ON =Canada>www.pinnaclepublishing.com/sql>>>"Laur=a Reynolds" We have recently converted a SQL 7.0 database to SQL >2000. =There are a number of tables that have default >values set for =inserting data. When accessing via the >front-end application (ASP =page), the tables are not >inserting the default values instead it is inserting > into the field. Any insight?>

---

--=_NextPart_000_000D_01C35787.64B12BC0--

conversion

Hi All,
I am pretty new to sql server. I have a column with data in the format
of #ddd,dddd
where d is a number between 0 and 9.
e.g. one entry of column #34,56
What is the easiest way to convert it to integer? #56,5-->565
Thanks a lot
Thanks.So the comma is not significant? If so, try:
select
cast (replace (replace (MyCol, '#', ''), ',', '') as int)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
<jack.smith.sam@.gmail.com> wrote in message
news:1162070389.388509.50190@.m7g2000cwm.googlegroups.com...
Hi All,
I am pretty new to sql server. I have a column with data in the format
of #ddd,dddd
where d is a number between 0 and 9.
e.g. one entry of column #34,56
What is the easiest way to convert it to integer? #56,5-->565
Thanks a lot
Thanks.|||If you want to permanently convert the column datatype to integer, use
Tom's suggestion to clear the non-numeric characters out of the current
column, and then use
ALTER TABLE MyTable
ALTER COLUMN Mycolumn int
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
<jack.smith.sam@.gmail.com> wrote in message
news:1162070389.388509.50190@.m7g2000cwm.googlegroups.com...
> Hi All,
> I am pretty new to sql server. I have a column with data in the format
> of #ddd,dddd
> where d is a number between 0 and 9.
> e.g. one entry of column #34,56
> What is the easiest way to convert it to integer? #56,5-->565
> Thanks a lot
> Thanks.
>sqlsql

conversion

Hi All,
I am pretty new to sql server. I have a column with data in the format
of #ddd,dddd
where d is a number between 0 and 9.
e.g. one entry of column #34,56
What is the easiest way to convert it to integer? #56,5-->565
Thanks a lot
Thanks.So the comma is not significant? If so, try:
select
cast (replace (replace (MyCol, '#', ''), ',', '') as int)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
<jack.smith.sam@.gmail.com> wrote in message
news:1162070389.388509.50190@.m7g2000cwm.googlegroups.com...
Hi All,
I am pretty new to sql server. I have a column with data in the format
of #ddd,dddd
where d is a number between 0 and 9.
e.g. one entry of column #34,56
What is the easiest way to convert it to integer? #56,5-->565
Thanks a lot
Thanks.|||If you want to permanently convert the column datatype to integer, use
Tom's suggestion to clear the non-numeric characters out of the current
column, and then use
ALTER TABLE MyTable
ALTER COLUMN Mycolumn int
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
<jack.smith.sam@.gmail.com> wrote in message
news:1162070389.388509.50190@.m7g2000cwm.googlegroups.com...
> Hi All,
> I am pretty new to sql server. I have a column with data in the format
> of #ddd,dddd
> where d is a number between 0 and 9.
> e.g. one entry of column #34,56
> What is the easiest way to convert it to integer? #56,5-->565
> Thanks a lot
> Thanks.
>

Controlling toolbar appearance in Webforms Report Viewer Control

I see that the Report Viewer Control has a number of properties that can be
set to control things like the control's border color and width, the
background color of the toolbars, the border and color of the buttons, etc.
However I have some additional formatting requirements:
1. How do I control the font properties (face, color, etc.) for the report
parameter labels and other text in the toolbars?
2. Is there a way that I can force the report parameters to render in a
single column or otherwise control their layout? They normally render across
the page and then down which sometimes results in an unusual order for the
user to see them. I know I could create custom report parameter forms but I
am trying to avoid that.
--
Chris, SSSIHi Steven,
Thank you for your reply. I have some follow up questions:
>>Based on my test, all the text's Font settings can be configured through
>>the ReportViewer control's "Font" property.
1) I see those settings in the Properties window, but how do I set these
font settings programatically?
2) The Microsoft.Reporting.WebForms.ReportViewer class has a Font property,
but it is ReadOnly. So how do I set these settings in code at runtime? Can
the font settings only be set at design time?
>>I've attached a screenshot on the setting of this Font property on my test page.
3) I do not see a screenshot attached to your posting. Did you forget to
attach it?
>>so it won't affect our rendered report's text font.
That is fine and actually the behavior I want.
--
Chris, SSSI
"Steven Cheng[MSFT]" wrote:
> Hello Chris,
> As for the two questions you mentioned, here are my understanding and
> suggestion:
> 1. How do I control the font properties (face, color, etc.) for the report
> parameter labels and other text in the toolbars?
> ==============================================> Based on my test, all the text's Font settings can be configured through
> the ReportViewer control's "Font" property. This property will affect the
> text on every element on reportviewer toolbar, include parameter label,
> parameter input textbox, buttons ...... I've attached a screenshot on
> the setting of this Font property on my test page. BTW, so far I only find
> this very Font setting for the ReportViewer which affect the text on the
> reportViewer's non-ReportClient region, so it won't affect our rendered
> report's text font.
>
> 2. Is there a way that I can force the report parameters to render in a
> single column or otherwise control their layout? They normally render
> across
> the page and then down which sometimes results in an unusual order for the
> user to see them. I know I could create custom report parameter forms but I
> am trying to avoid that.
> ===============================================> I've performed some test and research, it seems the Report parameters panel
> is fixed above the reportViewer control and there is no much individual
> properties to customize it. So far what I can get is the following option:
> 1. Make the "ShowParameterPrompts" and "ShowRefreshButton" to "false" so
> that they won't display on the reportviewr.
> 2. We can add create our own UI (through ASP.NET controls ) to accept
> parameter values
> 3. Also, we need to add our own button and use code to programmtically set
> the parameter from our custom controls into ReportViewer.ServerReport and
> call its Refresh method to rerender it.
> Please let me know if there is anything else you wonder.
> Sincerely,
> Steven Cheng
> Microsoft MSDN Online Support Lead
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
>
>|||Thanks for your reply Chris,
For your further question:
Yes, we can also programmatically adjust the ReportViewer.Font property
settings. For example:
page classs
{
.............
protected void btnStyle_Click(object sender, EventArgs e)
{
ReportViewer1.Font.Bold = true;
ReportViewer1.Font.Name = "Verdana";
ReportViewer1.Font.Size = FontUnit.Point(24);
ReportViewer1.Font.Underline = true;
}
}
Hope this also helps.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.|||Steven,
So then the Intellisense tooltip which says ReportViewer.Font is ReadOnly is
incorrect? Is this bug being fixed?
-- Chris
Chris, SSSI
"Steven Cheng[MSFT]" wrote:
> Thanks for your reply Chris,
> For your further question:
>
> Yes, we can also programmatically adjust the ReportViewer.Font property
> settings. For example:
> page classs
> {
> ..............
> protected void btnStyle_Click(object sender, EventArgs e)
> {
> ReportViewer1.Font.Bold = true;
> ReportViewer1.Font.Name = "Verdana";
> ReportViewer1.Font.Size = FontUnit.Point(24);
> ReportViewer1.Font.Underline = true;
> }
> }
> Hope this also helps.
> Sincerely,
> Steven Cheng
> Microsoft MSDN Online Support Lead
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi Chris,
Thanks for the reply.
As for the "ReportViewer.Font" property, it is derived from the
"WebControl" class and the VS IDE's intellisense is correct, the property
does be "ReadOnly". This is a common design for some complex properties of
Webcontrol class, such as "WebControl.ControlStyle" and
"WebControl.ControlStyle". For these complex properties, we should
modify their values through their sub-properties instead of directly modify
the property instance itself. This design is also for performance
consideration since replacing a whole complex property instance is much
more expensive than just replacing those sub properties.
Therefore, when we want to customize any webcontrol's "Font" property, we
should access its sub properties instead, like:
ReportViewer1.Font.Bold = true;
ReportViewer1.Font.Name = "Verdana";
#WebControl.Font
http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.webcontro
l.font.aspx
BTW, it is recommended that we also follow this pattern when we add custom
properties for webcontrol which is of such complex type.
Hope this helps.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Chris,
Have you got the issue resolved? Please feel free to let me know if there
is anything else we can help.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.

Controlling the X-Axis

Hi guys...
I would like to do the following with my chart:
1. Have the label always appear vertically, and;
2. Limit the number of labels to 20 or less.
Any suggestions?
Thanks,
Forch#1: the chart control uses automatic label positioning based on the
available space. Vertical labels are only used if they don't fit
horizontally.
#2: Assuming you have the "numeric or time-scale" option turned on for the
x-axis, you could probably achieve this by setting the x-axis major interval
and/or min and max values.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Forch" <Forch@.discussions.microsoft.com> wrote in message
news:61AA4890-A0B4-4BB2-BF5C-7006FC755650@.microsoft.com...
> Hi guys...
> I would like to do the following with my chart:
> 1. Have the label always appear vertically, and;
> 2. Limit the number of labels to 20 or less.
> Any suggestions?
> Thanks,
> Forch

Controlling the resultset size.

I'm using a report basically as a form. In this form I want the same number of lineitems no matter if I have 2 rows of data or 5 rows of data in my resultset. I basically want extra blank lines to roundout the resultset. The reason, is that with extra rows people printing the form can write additional data. Also, it will preseve the formatting. So I either need a way to tell a table in SSRS that it needs to have a minimum number of rows, OR, I need a way to add extra blank/null rows to a result set. Any ideas?

Hello,

I just developed a working solution to the same problem a few minutes ago. My problem was that the footer in the table was appearing in random places depending on the number of rows returned. I can fit 7 rows, so I needed to pad out additional rows to equal 7. So, I had to add additional data to the SQL resultset to do this. After my stored procedure produced a resultset, I immediately invoked this function to add more rows before returning to the report. Hope this helps. If anyone has an elegant solution, please post, I would like to implement it!

ALTER FUNCTION [dbo].[fCreateBlankTblRows]
(
@.nbrRows //my stored procedure figured out how many rows were returned and how many more were needed.
)

RETURNS
@.my_temp_tbl TABLE
(
id int,
id2 int,
xyx int
)
AS
BEGIN
Declare @.loopCount int;
set @.loopCount = 0;
WHILE (@.loopCount < @.nbrRows)
Begin
INSERT INTO @.my_temp_tbl
(id, id2, xyz)
VALUES (null,null,null);
set @.loopCount = @.loopCount + 1;
End;
RETURN
END

Controlling number of rows to display in a table and matrix on one page

Is there a way to control how many Detail Rows are displayed on one page in Table and Matrix controls?

hii

do you mean to say if there are 100 rows and you want only 10 rows to be shown in one page right without any change in the formatting?

i think the rough way is that you can increase the hight of the detail section but it will change the formatting of the page.still am trying with the issue .

Thanks

Mahasweta

Controlling number of Matrix rows displayed

Here's hopefully a simple one and I'm just not looking in the right places for the documentation:

I have a matrix and the result set it displays can range from 20 rows to 200+ rows. The columns range from 25 to possibly 100+. I need a way to set the maximum number of rows displayed per page so that if there are 200 rows returned (or some other arbitrary number) and the user wants to see 25 rows per page, then 8 pages will be returned.

Is there a way to do this?

Wayne E. Pfeffer

im also facing the same issue.. have u found the answer. if so do help me.|||

Not much help, but it may point you in the right direction:

http://www.microsoft.com/technet/prodtechnol/sql/2005/rsdesign.mspx

|||

Here is another link which might help:

http://blogs.msdn.com/chrishays/archive/2004/07/23/HorizontalTables.aspx

Control the paging functionality manually

hi all,

In my Reports, i want to handle the paging functionality manually, c i have a query called "Select * from employees" it has a total number of records of 100, in which it is displaying some 50 rows in one page and the next in the other page, wat my requirement is , i want to display 10 rows per page and the remaining in the next pages. this is killing me in the look and feel, so pls help me in this, wating for a reply as soon as possible, how to do it ?

Thanks in advance

Venkat.

check this:http://aspnet.4guysfromrolla.com/articles/031506-1.aspx

HTH|||

Hi,

Checked the link,its not related with reporting services, i like to do custom paging in the report (.rdl) in sql server 2000 reporting services

waiting for a reply

thanks in advance

Venkat.

|||Just setting the height of the report page will do it .

Control Report Parameters Display

Is there a way to control the way report parameters are displayed in terms of number of columns etc. The reason we need that is we have like 15 parameters in one of the report and then it just looks ugly.

Thanks.

Not if you are viewing the Reports via Report Manager. If you are using the Viewer Controls that ship with VS 2005 you could but it would mean writing your own parameter area.

Control number of report copies within Crystal

Hi all,

I need some way to control the number of report copies within Crystal. For example, is there any way i can set a formular so that CR will produce two copies of the report with the first copy will have a text field "Office copy" and the second copy will have a text field "Factory Copy" ??

Thanks and CheersYes it is possible (by passing your text as a parameter, for example).
You didn't mention if you are running report from CR or from a custom application, but in either case, you will have to run the report twice.

Control number of options selected in a multi select parameter

Hi All

I have a report which has a multi-value parameter. Problem is, it can contain up to 100 options.

Is there a way to limit the number of options that is passed to the SQL statement?. EG list has 100 options, user selects 10 but only the first 4 selected options are passed to the SQL statement.


Many Thanks
Delli
I will try an expression for the query parameter (Parameters tab in the dataset properties) which passes your parameter to code-behind function that in turn filters out the parameter values accordingly.|||

After looking into this some more, I’ve found a split

function for MS SQL server, and in PL/SQL. Have system in both databases

:( grrrrrrr

The split function takes in the multi select parameter as a comma separated

list, and creates a virtual table of the results.

By using SQL code similar to the following: select top 10 element

dbo.split('string,split,code',',') I could stop the SQL engine running

for too many selected parameters. A search on Google or msn search ;) will find

codes examples for these functions. Keywords: SQL split function or PL/SQL

split function.

Also helps to inform your users on the front page of the report you have

done this!!!

Control names of fields when exporting to CSV

My query returns fields that have spaces in the names, for example:

select o.OrderID as 'INVOICE NUMBER', etc..

When I created the DataSet for this query in report designer, it changed all spaces in the field names to underscores, i.e. INVOICE_NUMBER.

This report must be exported to CSV, and the names of the fields (first row in csv) must have the spaces, not underscores. But it appears RS uses the field names (not the names in the column headers of the report table control) as the field names. Is there any way for me to force the field names in the first row of the csv to something other than the field names in the DataSet?

Thanks!

I think I you should be able to rename the column headers to change the results in the CSV file.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

ALFKI,

You can go to the column properties data output tab and enter the "Element Name" and select output as "Yes" this is how I can my export to csv files.

Ham

|||

should read

"how I change my export column names in the csv files"

|||Thanks - the only problem is, Element Name cannot contain a space , hence the underscores. Oh, well...|||

ALFKI,

Sorry, Yes, you are still bounded by the textbox property field names. You could not for instance name a textbox "My Textbox Value" is must be My_TextBox_Value. I mistaken thought you were just trying to rename the field.

Ham

continually increasing number of open connections

Hi,
I have an application that uses batch updates to insert data into the
database.
The sequence is basically:
Statement.prepareCall()
loopForever
{
GetDataFromSomewhere
CallableStatement.clearBatch()
CallableStatement.addBatch()
CallableStatement.addBatch()
...
CallableStatement.addBatch()
CallableStatement.executeBatch()
}
The application uses the same Statement object which is never closed.
However, the number of connections is constantly growing (netstat & lsof
shows 100,000 connections to the database after several hours and after that
the machine crashed...).
It appears that the driver creates a connection for every statement in the
batch (or at least for each batch). Should it work in this way? is there a
way to use the same connection for the entire batch?
Also, is there way to prevent this connections leak? I'd like to use the
same Statement object to eliminate the need for prepareCall() on each batch
in order to get improved performance.
Any help will be appreciated.
Thanks,
Noam
Noam,
In "selectMode=direct" the MS driver (and all other DataDirect based
drivers) create a "cloned" connection (i.e. a new physical connection)
for every new statement. That may be the source of your problem if you
are creating Statement objects inside a loop.
If you are absolutely sure you are not doing that and that there are no
other places in your code where this might happen, then I can only
recommend you try another (non-DataDirect) driver and see if the same
happens.
Alin.
|||I've tried using selectMode=cursor but it does not help. This is a single
thread that access the database so there are no other places that create new
statenet. Also, this started to happen when I decied to imrpove the code and
use batch mode.
In the mean time, I'm creating a new statement inside the loop (and closing
it at the end if the look) and it works OK.
Thanks,
Noam
|||"Noam Ambar" <NoamAmbar@.discussions.microsoft.com> schrieb im Newsbeitrag
news:37218689-6264-4B3C-B8D9-5EDC05385136@.microsoft.com...
> I've tried using selectMode=cursor but it does not help. This is a
single
> thread that access the database so there are no other places that create
new
> statenet. Also, this started to happen when I decied to imrpove the code
and
> use batch mode.
> In the mean time, I'm creating a new statement inside the loop (and
closing
> it at the end if the look) and it works OK.
Do you ever commit in between? Or is this autocommitted?
robert
|||It is true that the Microsoft SQL Server JDBC driver creates "cloned
connections" and that earlier versions of the DataDirect SQL Server JDBC
driver did as well. The current 3.4 DataDirect SQL Server JDBC driver does
not clone connections.
Sue Purkis
DataDirect Technologies
our current 3.4 SQL Server JDBC driver does not clone connections anymore
"Alin Sinpalean" <alin@.earthling.net> wrote in message
news:1109516553.438410.284260@.o13g2000cwo.googlegr oups.com...
> Noam,
> In "selectMode=direct" the MS driver (and all other DataDirect based
> drivers) create a "cloned" connection (i.e. a new physical connection)
> for every new statement. That may be the source of your problem if you
> are creating Statement objects inside a loop.
> If you are absolutely sure you are not doing that and that there are no
> other places in your code where this might happen, then I can only
> recommend you try another (non-DataDirect) driver and see if the same
> happens.
> Alin.
>
|||Sue Purkis wrote:
> It is true that the Microsoft SQL Server JDBC driver creates "cloned
> connections" and that earlier versions of the DataDirect SQL Server
JDBC
> driver did as well. The current 3.4 DataDirect SQL Server JDBC
driver does
> not clone connections.
Sue,
Thanks for the update; I didn't know about that. So does this mean that
"selectMethod=direct" now supports transactions (autoCommit == false)
too?
Alin,
The jTDS Project.
|||Alin,
Yes, it is true that selectMethod=direct now supports transactions with
the DataDirect 3.4 SQL Server driver.
Sue
DataDirect Technologies
"Alin Sinpalean" <alin@.earthling.net> wrote in message
news:1109930907.961426.268700@.g14g2000cwa.googlegr oups.com...
> Sue Purkis wrote:
> JDBC
> driver does
> Sue,
> Thanks for the update; I didn't know about that. So does this mean that
> "selectMethod=direct" now supports transactions (autoCommit == false)
> too?
> Alin,
> The jTDS Project.
>