Hi,
I have a big database with most of the columns having the datatype char
(char(10), (char(50) etc.)
I would like to change all these columns to varchar (varchar(10),
varchar(50), ...), and have all the spaces on the right trimmed of. So
instead of 'ABC ' I want to have 'ABC'.
Is there a 'nice' way to do this? Changing all the char-columns to varchar
doesn't seem too difficult to me, but would there be a way that automaticly
cuts of this trailing spaces? Or is there some script that exists that does
this for me?
The biggest problem in my opinion is that some of these columns are used as
foreign keys. So I don't know if it is possible that they will be trimmed in
one table, but not yet in the foreign-key-table?
Any help our hints would be really appreciated!
Thanks a lot in advance,
PieterHello, Pieter
To change the data types of the columns, you need to drop the foreign
keys, change all the columns involved (using "ALTER TABLE tbl ALTER
COLUMN col varchar(n)") and then recreate the foreign keys. That's
because a foreign key requires the referencing columns to have the same
data type as the referenced columns (additionally, a column involved in
any kind of constraint cannot be altered).
The trimming can be done before or after recreating the foreign keys,
because when SQL Server compares 'a' with 'a ', they will be equal. For
example, the following would work just fine:
USE tempdb
CREATE TABLE t1 (x varchar(10) primary key)
CREATE TABLE t2 (y varchar(10) references t1)
INSERT INTO t1 VALUES('a')
INSERT INTO t2 VALUES('a ')
SELECT x+'!', y+'!' FROM t1 INNER JOIN t2 ON x=y
DROP TABLE t2,t1
Razvan
Showing posts with label datatype. Show all posts
Showing posts with label datatype. Show all posts
Tuesday, March 27, 2012
Change from char to varchar and RTRIM
Hi,
I have a big database with most of the columns having the datatype char
(char(10), (char(50) etc.)
I would like to change all these columns to varchar (varchar(10),
varchar(50), ...), and have all the spaces on the right trimmed of. So
instead of 'ABC ' I want to have 'ABC'.
Is there a 'nice' way to do this? Changing all the char-columns to varchar
doesn't seem too difficult to me, but would there be a way that automaticly
cuts of this trailing spaces? Or is there some script that exists that does
this for me?
The biggest problem in my opinion is that some of these columns are used as
foreign keys. So I don't know if it is possible that they will be trimmed in
one table, but not yet in the foreign-key-table?
Any help our hints would be really appreciated!
Thanks a lot in advance,
PieterHello, Pieter
To change the data types of the columns, you need to drop the foreign
keys, change all the columns involved (using "ALTER TABLE tbl ALTER
COLUMN col varchar(n)") and then recreate the foreign keys. That's
because a foreign key requires the referencing columns to have the same
data type as the referenced columns (additionally, a column involved in
any kind of constraint cannot be altered).
The trimming can be done before or after recreating the foreign keys,
because when SQL Server compares 'a' with 'a ', they will be equal. For
example, the following would work just fine:
USE tempdb
CREATE TABLE t1 (x varchar(10) primary key)
CREATE TABLE t2 (y varchar(10) references t1)
INSERT INTO t1 VALUES('a')
INSERT INTO t2 VALUES('a ')
SELECT x+'!', y+'!' FROM t1 INNER JOIN t2 ON x=y
DROP TABLE t2,t1
Razvan
I have a big database with most of the columns having the datatype char
(char(10), (char(50) etc.)
I would like to change all these columns to varchar (varchar(10),
varchar(50), ...), and have all the spaces on the right trimmed of. So
instead of 'ABC ' I want to have 'ABC'.
Is there a 'nice' way to do this? Changing all the char-columns to varchar
doesn't seem too difficult to me, but would there be a way that automaticly
cuts of this trailing spaces? Or is there some script that exists that does
this for me?
The biggest problem in my opinion is that some of these columns are used as
foreign keys. So I don't know if it is possible that they will be trimmed in
one table, but not yet in the foreign-key-table?
Any help our hints would be really appreciated!
Thanks a lot in advance,
PieterHello, Pieter
To change the data types of the columns, you need to drop the foreign
keys, change all the columns involved (using "ALTER TABLE tbl ALTER
COLUMN col varchar(n)") and then recreate the foreign keys. That's
because a foreign key requires the referencing columns to have the same
data type as the referenced columns (additionally, a column involved in
any kind of constraint cannot be altered).
The trimming can be done before or after recreating the foreign keys,
because when SQL Server compares 'a' with 'a ', they will be equal. For
example, the following would work just fine:
USE tempdb
CREATE TABLE t1 (x varchar(10) primary key)
CREATE TABLE t2 (y varchar(10) references t1)
INSERT INTO t1 VALUES('a')
INSERT INTO t2 VALUES('a ')
SELECT x+'!', y+'!' FROM t1 INNER JOIN t2 ON x=y
DROP TABLE t2,t1
Razvan
Tuesday, March 20, 2012
Change datatype var to nvar but log gets full
I have a table with a column of datatype var and it has to be changed to nvar.
The table contains millions of records and with an alter table my log get's
full, starts growing and in the end I my query stops with no result.
What is the best way to accomplish this change while keeping the data and
having not to much logging?
Any help is appreciated.
I was thinking of an insert into a new table...
Make sure you have good backup firstly before doing what I can think out:
1) If there are index on the column, drop them before changing its datatype.
2) change the db recovery mode to 'simple'.
3) shrink the db size and some other db size (including tempdb, or
considering change their recovery mode to 'simple' before shrinking) to free
more disk space.
4) do the altering
5) re-create the index.
James
"Zekske" wrote:
> I have a table with a column of datatype var and it has to be changed to nvar.
> The table contains millions of records and with an alter table my log get's
> full, starts growing and in the end I my query stops with no result.
> What is the best way to accomplish this change while keeping the data and
> having not to much logging?
> Any help is appreciated.
> I was thinking of an insert into a new table...
|||Notice my suggestion might still fail finally, and you should be sure of the
implication of changing recovery model.
James
"James Ma" wrote:
[vbcol=seagreen]
> Make sure you have good backup firstly before doing what I can think out:
> 1) If there are index on the column, drop them before changing its datatype.
> 2) change the db recovery mode to 'simple'.
> 3) shrink the db size and some other db size (including tempdb, or
> considering change their recovery mode to 'simple' before shrinking) to free
> more disk space.
> 4) do the altering
> 5) re-create the index.
> James
> "Zekske" wrote:
|||I did change the recovery model to simple so that doesn't help.
That's why I was thinking about doing a bulk import or something like that
but I'm not sure it's possible.
Thanks anyway
"James Ma" wrote:
[vbcol=seagreen]
> Notice my suggestion might still fail finally, and you should be sure of the
> implication of changing recovery model.
> James
> "James Ma" wrote:
|||Did you use EM or ALTER TABLE to do the change. EM tends to do these changes in a very clumsy way.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Zekske" <Zekske@.discussions.microsoft.com> wrote in message
news:CEF96044-B288-4DF0-95C4-486F3CC4B7F6@.microsoft.com...[vbcol=seagreen]
>I did change the recovery model to simple so that doesn't help.
> That's why I was thinking about doing a bulk import or something like that
> but I'm not sure it's possible.
> Thanks anyway
> "James Ma" wrote:
|||We used alter table...
"Tibor Karaszi" wrote:
> Did you use EM or ALTER TABLE to do the change. EM tends to do these changes in a very clumsy way.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Zekske" <Zekske@.discussions.microsoft.com> wrote in message
> news:CEF96044-B288-4DF0-95C4-486F3CC4B7F6@.microsoft.com...
>
>
The table contains millions of records and with an alter table my log get's
full, starts growing and in the end I my query stops with no result.
What is the best way to accomplish this change while keeping the data and
having not to much logging?
Any help is appreciated.
I was thinking of an insert into a new table...
Make sure you have good backup firstly before doing what I can think out:
1) If there are index on the column, drop them before changing its datatype.
2) change the db recovery mode to 'simple'.
3) shrink the db size and some other db size (including tempdb, or
considering change their recovery mode to 'simple' before shrinking) to free
more disk space.
4) do the altering
5) re-create the index.
James
"Zekske" wrote:
> I have a table with a column of datatype var and it has to be changed to nvar.
> The table contains millions of records and with an alter table my log get's
> full, starts growing and in the end I my query stops with no result.
> What is the best way to accomplish this change while keeping the data and
> having not to much logging?
> Any help is appreciated.
> I was thinking of an insert into a new table...
|||Notice my suggestion might still fail finally, and you should be sure of the
implication of changing recovery model.
James
"James Ma" wrote:
[vbcol=seagreen]
> Make sure you have good backup firstly before doing what I can think out:
> 1) If there are index on the column, drop them before changing its datatype.
> 2) change the db recovery mode to 'simple'.
> 3) shrink the db size and some other db size (including tempdb, or
> considering change their recovery mode to 'simple' before shrinking) to free
> more disk space.
> 4) do the altering
> 5) re-create the index.
> James
> "Zekske" wrote:
|||I did change the recovery model to simple so that doesn't help.
That's why I was thinking about doing a bulk import or something like that
but I'm not sure it's possible.
Thanks anyway
"James Ma" wrote:
[vbcol=seagreen]
> Notice my suggestion might still fail finally, and you should be sure of the
> implication of changing recovery model.
> James
> "James Ma" wrote:
|||Did you use EM or ALTER TABLE to do the change. EM tends to do these changes in a very clumsy way.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Zekske" <Zekske@.discussions.microsoft.com> wrote in message
news:CEF96044-B288-4DF0-95C4-486F3CC4B7F6@.microsoft.com...[vbcol=seagreen]
>I did change the recovery model to simple so that doesn't help.
> That's why I was thinking about doing a bulk import or something like that
> but I'm not sure it's possible.
> Thanks anyway
> "James Ma" wrote:
|||We used alter table...
"Tibor Karaszi" wrote:
> Did you use EM or ALTER TABLE to do the change. EM tends to do these changes in a very clumsy way.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Zekske" <Zekske@.discussions.microsoft.com> wrote in message
> news:CEF96044-B288-4DF0-95C4-486F3CC4B7F6@.microsoft.com...
>
>
Change datatype var to nvar but log gets full
I have a table with a column of datatype var and it has to be changed to nvar.
The table contains millions of records and with an alter table my log get's
full, starts growing and in the end I my query stops with no result.
What is the best way to accomplish this change while keeping the data and
having not to much logging?
Any help is appreciated.
I was thinking of an insert into a new table...Make sure you have good backup firstly before doing what I can think out:
1) If there are index on the column, drop them before changing its datatype.
2) change the db recovery mode to 'simple'.
3) shrink the db size and some other db size (including tempdb, or
considering change their recovery mode to 'simple' before shrinking) to free
more disk space.
4) do the altering
5) re-create the index.
James
"Zekske" wrote:
> I have a table with a column of datatype var and it has to be changed to nvar.
> The table contains millions of records and with an alter table my log get's
> full, starts growing and in the end I my query stops with no result.
> What is the best way to accomplish this change while keeping the data and
> having not to much logging?
> Any help is appreciated.
> I was thinking of an insert into a new table...|||Notice my suggestion might still fail finally, and you should be sure of the
implication of changing recovery model.
James
"James Ma" wrote:
> Make sure you have good backup firstly before doing what I can think out:
> 1) If there are index on the column, drop them before changing its datatype.
> 2) change the db recovery mode to 'simple'.
> 3) shrink the db size and some other db size (including tempdb, or
> considering change their recovery mode to 'simple' before shrinking) to free
> more disk space.
> 4) do the altering
> 5) re-create the index.
> James
> "Zekske" wrote:
> > I have a table with a column of datatype var and it has to be changed to nvar.
> > The table contains millions of records and with an alter table my log get's
> > full, starts growing and in the end I my query stops with no result.
> > What is the best way to accomplish this change while keeping the data and
> > having not to much logging?
> >
> > Any help is appreciated.
> > I was thinking of an insert into a new table...|||I did change the recovery model to simple so that doesn't help.
That's why I was thinking about doing a bulk import or something like that
but I'm not sure it's possible.
Thanks anyway
"James Ma" wrote:
> Notice my suggestion might still fail finally, and you should be sure of the
> implication of changing recovery model.
> James
> "James Ma" wrote:
> > Make sure you have good backup firstly before doing what I can think out:
> > 1) If there are index on the column, drop them before changing its datatype.
> > 2) change the db recovery mode to 'simple'.
> > 3) shrink the db size and some other db size (including tempdb, or
> > considering change their recovery mode to 'simple' before shrinking) to free
> > more disk space.
> > 4) do the altering
> > 5) re-create the index.
> >
> > James
> >
> > "Zekske" wrote:
> >
> > > I have a table with a column of datatype var and it has to be changed to nvar.
> > > The table contains millions of records and with an alter table my log get's
> > > full, starts growing and in the end I my query stops with no result.
> > > What is the best way to accomplish this change while keeping the data and
> > > having not to much logging?
> > >
> > > Any help is appreciated.
> > > I was thinking of an insert into a new table...|||Did you use EM or ALTER TABLE to do the change. EM tends to do these changes in a very clumsy way.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Zekske" <Zekske@.discussions.microsoft.com> wrote in message
news:CEF96044-B288-4DF0-95C4-486F3CC4B7F6@.microsoft.com...
>I did change the recovery model to simple so that doesn't help.
> That's why I was thinking about doing a bulk import or something like that
> but I'm not sure it's possible.
> Thanks anyway
> "James Ma" wrote:
>> Notice my suggestion might still fail finally, and you should be sure of the
>> implication of changing recovery model.
>> James
>> "James Ma" wrote:
>> > Make sure you have good backup firstly before doing what I can think out:
>> > 1) If there are index on the column, drop them before changing its datatype.
>> > 2) change the db recovery mode to 'simple'.
>> > 3) shrink the db size and some other db size (including tempdb, or
>> > considering change their recovery mode to 'simple' before shrinking) to free
>> > more disk space.
>> > 4) do the altering
>> > 5) re-create the index.
>> >
>> > James
>> >
>> > "Zekske" wrote:
>> >
>> > > I have a table with a column of datatype var and it has to be changed to nvar.
>> > > The table contains millions of records and with an alter table my log get's
>> > > full, starts growing and in the end I my query stops with no result.
>> > > What is the best way to accomplish this change while keeping the data and
>> > > having not to much logging?
>> > >
>> > > Any help is appreciated.
>> > > I was thinking of an insert into a new table...|||We used alter table...
"Tibor Karaszi" wrote:
> Did you use EM or ALTER TABLE to do the change. EM tends to do these changes in a very clumsy way.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Zekske" <Zekske@.discussions.microsoft.com> wrote in message
> news:CEF96044-B288-4DF0-95C4-486F3CC4B7F6@.microsoft.com...
> >I did change the recovery model to simple so that doesn't help.
> > That's why I was thinking about doing a bulk import or something like that
> > but I'm not sure it's possible.
> >
> > Thanks anyway
> >
> > "James Ma" wrote:
> >
> >> Notice my suggestion might still fail finally, and you should be sure of the
> >> implication of changing recovery model.
> >>
> >> James
> >>
> >> "James Ma" wrote:
> >>
> >> > Make sure you have good backup firstly before doing what I can think out:
> >> > 1) If there are index on the column, drop them before changing its datatype.
> >> > 2) change the db recovery mode to 'simple'.
> >> > 3) shrink the db size and some other db size (including tempdb, or
> >> > considering change their recovery mode to 'simple' before shrinking) to free
> >> > more disk space.
> >> > 4) do the altering
> >> > 5) re-create the index.
> >> >
> >> > James
> >> >
> >> > "Zekske" wrote:
> >> >
> >> > > I have a table with a column of datatype var and it has to be changed to nvar.
> >> > > The table contains millions of records and with an alter table my log get's
> >> > > full, starts growing and in the end I my query stops with no result.
> >> > > What is the best way to accomplish this change while keeping the data and
> >> > > having not to much logging?
> >> > >
> >> > > Any help is appreciated.
> >> > > I was thinking of an insert into a new table...
>
>
The table contains millions of records and with an alter table my log get's
full, starts growing and in the end I my query stops with no result.
What is the best way to accomplish this change while keeping the data and
having not to much logging?
Any help is appreciated.
I was thinking of an insert into a new table...Make sure you have good backup firstly before doing what I can think out:
1) If there are index on the column, drop them before changing its datatype.
2) change the db recovery mode to 'simple'.
3) shrink the db size and some other db size (including tempdb, or
considering change their recovery mode to 'simple' before shrinking) to free
more disk space.
4) do the altering
5) re-create the index.
James
"Zekske" wrote:
> I have a table with a column of datatype var and it has to be changed to nvar.
> The table contains millions of records and with an alter table my log get's
> full, starts growing and in the end I my query stops with no result.
> What is the best way to accomplish this change while keeping the data and
> having not to much logging?
> Any help is appreciated.
> I was thinking of an insert into a new table...|||Notice my suggestion might still fail finally, and you should be sure of the
implication of changing recovery model.
James
"James Ma" wrote:
> Make sure you have good backup firstly before doing what I can think out:
> 1) If there are index on the column, drop them before changing its datatype.
> 2) change the db recovery mode to 'simple'.
> 3) shrink the db size and some other db size (including tempdb, or
> considering change their recovery mode to 'simple' before shrinking) to free
> more disk space.
> 4) do the altering
> 5) re-create the index.
> James
> "Zekske" wrote:
> > I have a table with a column of datatype var and it has to be changed to nvar.
> > The table contains millions of records and with an alter table my log get's
> > full, starts growing and in the end I my query stops with no result.
> > What is the best way to accomplish this change while keeping the data and
> > having not to much logging?
> >
> > Any help is appreciated.
> > I was thinking of an insert into a new table...|||I did change the recovery model to simple so that doesn't help.
That's why I was thinking about doing a bulk import or something like that
but I'm not sure it's possible.
Thanks anyway
"James Ma" wrote:
> Notice my suggestion might still fail finally, and you should be sure of the
> implication of changing recovery model.
> James
> "James Ma" wrote:
> > Make sure you have good backup firstly before doing what I can think out:
> > 1) If there are index on the column, drop them before changing its datatype.
> > 2) change the db recovery mode to 'simple'.
> > 3) shrink the db size and some other db size (including tempdb, or
> > considering change their recovery mode to 'simple' before shrinking) to free
> > more disk space.
> > 4) do the altering
> > 5) re-create the index.
> >
> > James
> >
> > "Zekske" wrote:
> >
> > > I have a table with a column of datatype var and it has to be changed to nvar.
> > > The table contains millions of records and with an alter table my log get's
> > > full, starts growing and in the end I my query stops with no result.
> > > What is the best way to accomplish this change while keeping the data and
> > > having not to much logging?
> > >
> > > Any help is appreciated.
> > > I was thinking of an insert into a new table...|||Did you use EM or ALTER TABLE to do the change. EM tends to do these changes in a very clumsy way.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Zekske" <Zekske@.discussions.microsoft.com> wrote in message
news:CEF96044-B288-4DF0-95C4-486F3CC4B7F6@.microsoft.com...
>I did change the recovery model to simple so that doesn't help.
> That's why I was thinking about doing a bulk import or something like that
> but I'm not sure it's possible.
> Thanks anyway
> "James Ma" wrote:
>> Notice my suggestion might still fail finally, and you should be sure of the
>> implication of changing recovery model.
>> James
>> "James Ma" wrote:
>> > Make sure you have good backup firstly before doing what I can think out:
>> > 1) If there are index on the column, drop them before changing its datatype.
>> > 2) change the db recovery mode to 'simple'.
>> > 3) shrink the db size and some other db size (including tempdb, or
>> > considering change their recovery mode to 'simple' before shrinking) to free
>> > more disk space.
>> > 4) do the altering
>> > 5) re-create the index.
>> >
>> > James
>> >
>> > "Zekske" wrote:
>> >
>> > > I have a table with a column of datatype var and it has to be changed to nvar.
>> > > The table contains millions of records and with an alter table my log get's
>> > > full, starts growing and in the end I my query stops with no result.
>> > > What is the best way to accomplish this change while keeping the data and
>> > > having not to much logging?
>> > >
>> > > Any help is appreciated.
>> > > I was thinking of an insert into a new table...|||We used alter table...
"Tibor Karaszi" wrote:
> Did you use EM or ALTER TABLE to do the change. EM tends to do these changes in a very clumsy way.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Zekske" <Zekske@.discussions.microsoft.com> wrote in message
> news:CEF96044-B288-4DF0-95C4-486F3CC4B7F6@.microsoft.com...
> >I did change the recovery model to simple so that doesn't help.
> > That's why I was thinking about doing a bulk import or something like that
> > but I'm not sure it's possible.
> >
> > Thanks anyway
> >
> > "James Ma" wrote:
> >
> >> Notice my suggestion might still fail finally, and you should be sure of the
> >> implication of changing recovery model.
> >>
> >> James
> >>
> >> "James Ma" wrote:
> >>
> >> > Make sure you have good backup firstly before doing what I can think out:
> >> > 1) If there are index on the column, drop them before changing its datatype.
> >> > 2) change the db recovery mode to 'simple'.
> >> > 3) shrink the db size and some other db size (including tempdb, or
> >> > considering change their recovery mode to 'simple' before shrinking) to free
> >> > more disk space.
> >> > 4) do the altering
> >> > 5) re-create the index.
> >> >
> >> > James
> >> >
> >> > "Zekske" wrote:
> >> >
> >> > > I have a table with a column of datatype var and it has to be changed to nvar.
> >> > > The table contains millions of records and with an alter table my log get's
> >> > > full, starts growing and in the end I my query stops with no result.
> >> > > What is the best way to accomplish this change while keeping the data and
> >> > > having not to much logging?
> >> > >
> >> > > Any help is appreciated.
> >> > > I was thinking of an insert into a new table...
>
>
Change datatype var to nvar but log gets full
I have a table with a column of datatype var and it has to be changed to nva
r.
The table contains millions of records and with an alter table my log get's
full, starts growing and in the end I my query stops with no result.
What is the best way to accomplish this change while keeping the data and
having not to much logging?
Any help is appreciated.
I was thinking of an insert into a new table...Make sure you have good backup firstly before doing what I can think out:
1) If there are index on the column, drop them before changing its datatype.
2) change the db recovery mode to 'simple'.
3) shrink the db size and some other db size (including tempdb, or
considering change their recovery mode to 'simple' before shrinking) to free
more disk space.
4) do the altering
5) re-create the index.
James
"Zekske" wrote:
> I have a table with a column of datatype var and it has to be changed to n
var.
> The table contains millions of records and with an alter table my log get'
s
> full, starts growing and in the end I my query stops with no result.
> What is the best way to accomplish this change while keeping the data and
> having not to much logging?
> Any help is appreciated.
> I was thinking of an insert into a new table...|||Notice my suggestion might still fail finally, and you should be sure of the
implication of changing recovery model.
James
"James Ma" wrote:
[vbcol=seagreen]
> Make sure you have good backup firstly before doing what I can think out:
> 1) If there are index on the column, drop them before changing its datatyp
e.
> 2) change the db recovery mode to 'simple'.
> 3) shrink the db size and some other db size (including tempdb, or
> considering change their recovery mode to 'simple' before shrinking) to fr
ee
> more disk space.
> 4) do the altering
> 5) re-create the index.
> James
> "Zekske" wrote:
>|||I did change the recovery model to simple so that doesn't help.
That's why I was thinking about doing a bulk import or something like that
but I'm not sure it's possible.
Thanks anyway
"James Ma" wrote:
[vbcol=seagreen]
> Notice my suggestion might still fail finally, and you should be sure of t
he
> implication of changing recovery model.
> James
> "James Ma" wrote:
>|||Did you use EM or ALTER TABLE to do the change. EM tends to do these changes
in a very clumsy way.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Zekske" <Zekske@.discussions.microsoft.com> wrote in message
news:CEF96044-B288-4DF0-95C4-486F3CC4B7F6@.microsoft.com...[vbcol=seagreen]
>I did change the recovery model to simple so that doesn't help.
> That's why I was thinking about doing a bulk import or something like that
> but I'm not sure it's possible.
> Thanks anyway
> "James Ma" wrote:
>|||We used alter table...
"Tibor Karaszi" wrote:
> Did you use EM or ALTER TABLE to do the change. EM tends to do these chang
es in a very clumsy way.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Zekske" <Zekske@.discussions.microsoft.com> wrote in message
> news:CEF96044-B288-4DF0-95C4-486F3CC4B7F6@.microsoft.com...
>
>sql
r.
The table contains millions of records and with an alter table my log get's
full, starts growing and in the end I my query stops with no result.
What is the best way to accomplish this change while keeping the data and
having not to much logging?
Any help is appreciated.
I was thinking of an insert into a new table...Make sure you have good backup firstly before doing what I can think out:
1) If there are index on the column, drop them before changing its datatype.
2) change the db recovery mode to 'simple'.
3) shrink the db size and some other db size (including tempdb, or
considering change their recovery mode to 'simple' before shrinking) to free
more disk space.
4) do the altering
5) re-create the index.
James
"Zekske" wrote:
> I have a table with a column of datatype var and it has to be changed to n
var.
> The table contains millions of records and with an alter table my log get'
s
> full, starts growing and in the end I my query stops with no result.
> What is the best way to accomplish this change while keeping the data and
> having not to much logging?
> Any help is appreciated.
> I was thinking of an insert into a new table...|||Notice my suggestion might still fail finally, and you should be sure of the
implication of changing recovery model.
James
"James Ma" wrote:
[vbcol=seagreen]
> Make sure you have good backup firstly before doing what I can think out:
> 1) If there are index on the column, drop them before changing its datatyp
e.
> 2) change the db recovery mode to 'simple'.
> 3) shrink the db size and some other db size (including tempdb, or
> considering change their recovery mode to 'simple' before shrinking) to fr
ee
> more disk space.
> 4) do the altering
> 5) re-create the index.
> James
> "Zekske" wrote:
>|||I did change the recovery model to simple so that doesn't help.
That's why I was thinking about doing a bulk import or something like that
but I'm not sure it's possible.
Thanks anyway
"James Ma" wrote:
[vbcol=seagreen]
> Notice my suggestion might still fail finally, and you should be sure of t
he
> implication of changing recovery model.
> James
> "James Ma" wrote:
>|||Did you use EM or ALTER TABLE to do the change. EM tends to do these changes
in a very clumsy way.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Zekske" <Zekske@.discussions.microsoft.com> wrote in message
news:CEF96044-B288-4DF0-95C4-486F3CC4B7F6@.microsoft.com...[vbcol=seagreen]
>I did change the recovery model to simple so that doesn't help.
> That's why I was thinking about doing a bulk import or something like that
> but I'm not sure it's possible.
> Thanks anyway
> "James Ma" wrote:
>|||We used alter table...
"Tibor Karaszi" wrote:
> Did you use EM or ALTER TABLE to do the change. EM tends to do these chang
es in a very clumsy way.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Zekske" <Zekske@.discussions.microsoft.com> wrote in message
> news:CEF96044-B288-4DF0-95C4-486F3CC4B7F6@.microsoft.com...
>
>sql
change datatype of a column
what is the best (and fast) why to change a column's datatype from varchar to decimal ?
the table has 3 million records and the column is filled with data (no problem with converting the data to numeric).alter table tablename
alter column columnname float null|||tnx
the table has 3 million records and the column is filled with data (no problem with converting the data to numeric).alter table tablename
alter column columnname float null|||tnx
Change datatype from varchar to bigint not working
Hello,
I would like to change the datatype on a particular column from varchar to bigint across 100's of tables within a database.
I have the command ready which is:
ALTER TABLE tablename ALTER COLUMN columnname BIGINT
The problem happening is that it seems there are constraints across all the columns in every tables.
The error message is:
Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF__tablename__columnname__0ABD916C' is dependent on column 'columnname'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN columnname failed because one or more objects access this column.
I understand that if I delete this constraint, then it will let me modify the datatype of the column, but since there are tons of them and they are randomly named, how do I achive changing the datatype across multiple tables in bulk.Hi
This should help you:
http://www.sqlteam.com/article/default-constraint-names
I would like to change the datatype on a particular column from varchar to bigint across 100's of tables within a database.
I have the command ready which is:
ALTER TABLE tablename ALTER COLUMN columnname BIGINT
The problem happening is that it seems there are constraints across all the columns in every tables.
The error message is:
Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF__tablename__columnname__0ABD916C' is dependent on column 'columnname'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN columnname failed because one or more objects access this column.
I understand that if I delete this constraint, then it will let me modify the datatype of the column, but since there are tons of them and they are randomly named, how do I achive changing the datatype across multiple tables in bulk.Hi
This should help you:
http://www.sqlteam.com/article/default-constraint-names
Sunday, March 11, 2012
Change column datatype "$2,000" to 2000
Hello,
How do I change the datatype of a column in a CSV file. Preferably in the select statement (apparently Cast, Convert, & Replace functions don't work when selecting from a CSV).
I have a page where users upload their CSV files to and then I use SQLBulkCopy to insert all the records into my sql table. The problem is with columns of money data.
EX: "$2,000" >> 2000
The CSV file interprets the "$2,000" as a string of text. How do I convert it to a decimal so I can insert it into my sql database?
The CSV doesn't interpret anything, CSV files have no data types, it's all just text. You can use a text editor to do a search and replace and remove all the dollar signs and periods if you wish. You should be able to set the SQL data type for currency as well. Or use a DTS job to change the data format instead of doing a bulk copy.
Jeff
|||Hi Jeff,
As I said, users are uploading CSV files to my site where I use SQLBulkCopy to automatically upload the records to a SQL table. So that means no manual editing of the csv file and no using DTS.
Or can you do a "DTS job" via code? If so I've never done that before and a code example would be much appreciated.
You're right, I shouldn't have said CSV interprets it. I meant while reading from the CSV to a datatable, etc it is interpreted as string data.
Thanks a bunch,
|||Moderators: please stop marking this post as resolved/answered. It is obvious Jeff's response is NOT the answer. Marking it so only removes the chance of my question actually being answered.
|||You can probably trigger a DTS job via code, I've never looked at whether this could be done or how. You may need to parse the CSV file in code as it is uploladed, before the bulk copy, to strip the string portions out and format the currency as a number. I'm not sure if a SQLBulkCopy can change data types, I'm not familiar enough with it to say.
Jeff
PS: This isn't the answer either I'm afraid... :)
Change collation of column with User-Defined Datatype
I need to change the collation of all columns in a table by using ALTER
TABLE...
One of the columns consists of a user-defined data type. How do I change the
collation of the column?Hi
"DBA72" wrote:
> I need to change the collation of all columns in a table by using ALTER
> TABLE...
> One of the columns consists of a user-defined data type. How do I change t
he
> collation of the column?
User defined types do no support COLLATE to change the collation in the
definition of the type or when they are used in the definition of the
collumn. They will therefore pick up the datatype from the database at the
time the table was created.
e.g.
CREATE DATABASE MyTest COLLATE Latin1_General_CI_AS
GO
USE MYtest
GO
sp_addtype 'udt_test', 'varchar(10)', null
GO
CREATE TABLE mYtest1 ( col1 udt_test )
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest1'
GO
USE MASTER
GO
ALTER DATABASE MyTest COLLATE SQL_Latin1_General_CP1_CI_AS
GO
USE MYtest
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest1'
GO
CREATE TABLE mYtest2 ( col1 udt_test )
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest2'
GO
-- To change the collation to the current database default then alter the
table
ALTER TABLE mYtest1 ALTER COLUMN col1 udt_test
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest1'
GO
USE MASTER
GO
DROP DATABASE MyTest
GO
John
TABLE...
One of the columns consists of a user-defined data type. How do I change the
collation of the column?Hi
"DBA72" wrote:
> I need to change the collation of all columns in a table by using ALTER
> TABLE...
> One of the columns consists of a user-defined data type. How do I change t
he
> collation of the column?
User defined types do no support COLLATE to change the collation in the
definition of the type or when they are used in the definition of the
collumn. They will therefore pick up the datatype from the database at the
time the table was created.
e.g.
CREATE DATABASE MyTest COLLATE Latin1_General_CI_AS
GO
USE MYtest
GO
sp_addtype 'udt_test', 'varchar(10)', null
GO
CREATE TABLE mYtest1 ( col1 udt_test )
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest1'
GO
USE MASTER
GO
ALTER DATABASE MyTest COLLATE SQL_Latin1_General_CP1_CI_AS
GO
USE MYtest
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest1'
GO
CREATE TABLE mYtest2 ( col1 udt_test )
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest2'
GO
-- To change the collation to the current database default then alter the
table
ALTER TABLE mYtest1 ALTER COLUMN col1 udt_test
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest1'
GO
USE MASTER
GO
DROP DATABASE MyTest
GO
John
Change collation of column with User-Defined Datatype
I need to change the collation of all columns in a table by using ALTER
TABLE...
One of the columns consists of a user-defined data type. How do I change the
collation of the column?
Hi
"DBA72" wrote:
> I need to change the collation of all columns in a table by using ALTER
> TABLE...
> One of the columns consists of a user-defined data type. How do I change the
> collation of the column?
User defined types do no support COLLATE to change the collation in the
definition of the type or when they are used in the definition of the
collumn. They will therefore pick up the datatype from the database at the
time the table was created.
e.g.
CREATE DATABASE MyTest COLLATE Latin1_General_CI_AS
GO
USE MYtest
GO
sp_addtype 'udt_test', 'varchar(10)', null
GO
CREATE TABLE mYtest1 ( col1 udt_test )
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest1'
GO
USE MASTER
GO
ALTER DATABASE MyTest COLLATE SQL_Latin1_General_CP1_CI_AS
GO
USE MYtest
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest1'
GO
CREATE TABLE mYtest2 ( col1 udt_test )
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest2'
GO
-- To change the collation to the current database default then alter the
table
ALTER TABLE mYtest1 ALTER COLUMN col1 udt_test
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest1'
GO
USE MASTER
GO
DROP DATABASE MyTest
GO
John
TABLE...
One of the columns consists of a user-defined data type. How do I change the
collation of the column?
Hi
"DBA72" wrote:
> I need to change the collation of all columns in a table by using ALTER
> TABLE...
> One of the columns consists of a user-defined data type. How do I change the
> collation of the column?
User defined types do no support COLLATE to change the collation in the
definition of the type or when they are used in the definition of the
collumn. They will therefore pick up the datatype from the database at the
time the table was created.
e.g.
CREATE DATABASE MyTest COLLATE Latin1_General_CI_AS
GO
USE MYtest
GO
sp_addtype 'udt_test', 'varchar(10)', null
GO
CREATE TABLE mYtest1 ( col1 udt_test )
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest1'
GO
USE MASTER
GO
ALTER DATABASE MyTest COLLATE SQL_Latin1_General_CP1_CI_AS
GO
USE MYtest
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest1'
GO
CREATE TABLE mYtest2 ( col1 udt_test )
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest2'
GO
-- To change the collation to the current database default then alter the
table
ALTER TABLE mYtest1 ALTER COLUMN col1 udt_test
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest1'
GO
USE MASTER
GO
DROP DATABASE MyTest
GO
John
Change collation of column with User-Defined Datatype
I need to change the collation of all columns in a table by using ALTER
TABLE...
One of the columns consists of a user-defined data type. How do I change the
collation of the column?Hi
"DBA72" wrote:
> I need to change the collation of all columns in a table by using ALTER
> TABLE...
> One of the columns consists of a user-defined data type. How do I change the
> collation of the column?
User defined types do no support COLLATE to change the collation in the
definition of the type or when they are used in the definition of the
collumn. They will therefore pick up the datatype from the database at the
time the table was created.
e.g.
CREATE DATABASE MyTest COLLATE Latin1_General_CI_AS
GO
USE MYtest
GO
sp_addtype 'udt_test', 'varchar(10)', null
GO
CREATE TABLE mYtest1 ( col1 udt_test )
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest1'
GO
USE MASTER
GO
ALTER DATABASE MyTest COLLATE SQL_Latin1_General_CP1_CI_AS
GO
USE MYtest
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest1'
GO
CREATE TABLE mYtest2 ( col1 udt_test )
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest2'
GO
-- To change the collation to the current database default then alter the
table
ALTER TABLE mYtest1 ALTER COLUMN col1 udt_test
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest1'
GO
USE MASTER
GO
DROP DATABASE MyTest
GO
John
TABLE...
One of the columns consists of a user-defined data type. How do I change the
collation of the column?Hi
"DBA72" wrote:
> I need to change the collation of all columns in a table by using ALTER
> TABLE...
> One of the columns consists of a user-defined data type. How do I change the
> collation of the column?
User defined types do no support COLLATE to change the collation in the
definition of the type or when they are used in the definition of the
collumn. They will therefore pick up the datatype from the database at the
time the table was created.
e.g.
CREATE DATABASE MyTest COLLATE Latin1_General_CI_AS
GO
USE MYtest
GO
sp_addtype 'udt_test', 'varchar(10)', null
GO
CREATE TABLE mYtest1 ( col1 udt_test )
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest1'
GO
USE MASTER
GO
ALTER DATABASE MyTest COLLATE SQL_Latin1_General_CP1_CI_AS
GO
USE MYtest
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest1'
GO
CREATE TABLE mYtest2 ( col1 udt_test )
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest2'
GO
-- To change the collation to the current database default then alter the
table
ALTER TABLE mYtest1 ALTER COLUMN col1 udt_test
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest1'
GO
USE MASTER
GO
DROP DATABASE MyTest
GO
John
Thursday, March 8, 2012
Change buffer size for the text datafile data
When populating a table with a column of TEXT datatype, an error
message was shown, indicating that the data is too large for the
specified buffer size. How we I change the buffer size from the SQL
Server Enterprise Manager?
TIA,
Jeffrey
what mechanism where you using to populate your table?
Does this kb apply
http://support.microsoft.com/default...&Product=sql2k
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Jeffrey" <cjeffwang@.gmail.com> wrote in message
news:bb2899d2.0410011423.5bf86f40@.posting.google.c om...
> When populating a table with a column of TEXT datatype, an error
> message was shown, indicating that the data is too large for the
> specified buffer size. How we I change the buffer size from the SQL
> Server Enterprise Manager?
> TIA,
> Jeffrey
|||Jeffery,
Could you provide more information on exactly what error message and error
message number you received?
How were you populating the column with the TEXT datatype? Via an INSERT
statement, BULK INSERT or BCP?
The more precise information we have, the better and faster we can answer
your question!
Thanks,
John
"Jeffrey" <cjeffwang@.gmail.com> wrote in message
news:bb2899d2.0410011423.5bf86f40@.posting.google.c om...
> When populating a table with a column of TEXT datatype, an error
> message was shown, indicating that the data is too large for the
> specified buffer size. How we I change the buffer size from the SQL
> Server Enterprise Manager?
> TIA,
> Jeffrey
|||I tried to populate a table with an Excel data file. To import the data
file I opened the DTS Import/Export wizard from Enterprise Manager. The
datatype is TEXT. Thanks.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||John,
I tried to populate a table of a SQL Server database with an Excel
data file. To import the data file I opened the DTS Import/Export
wizard from Enterprise Manager. The datatype of the column is TEXT
and the error message indicated that the text data is too large for
the specified buffer size.
How do I cahnge the buffer size of the database or the column?
Thanks,
Jeffrey
"John Kane" <jt-kane@.comcast.net> wrote in message news:<#FVNToAqEHA.592@.TK2MSFTNGP11.phx.gbl>...[vbcol=seagreen]
> Jeffery,
> Could you provide more information on exactly what error message and error
> message number you received?
> How were you populating the column with the TEXT datatype? Via an INSERT
> statement, BULK INSERT or BCP?
> The more precise information we have, the better and faster we can answer
> your question!
> Thanks,
> John
>
> "Jeffrey" <cjeffwang@.gmail.com> wrote in message
> news:bb2899d2.0410011423.5bf86f40@.posting.google.c om...
message was shown, indicating that the data is too large for the
specified buffer size. How we I change the buffer size from the SQL
Server Enterprise Manager?
TIA,
Jeffrey
what mechanism where you using to populate your table?
Does this kb apply
http://support.microsoft.com/default...&Product=sql2k
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Jeffrey" <cjeffwang@.gmail.com> wrote in message
news:bb2899d2.0410011423.5bf86f40@.posting.google.c om...
> When populating a table with a column of TEXT datatype, an error
> message was shown, indicating that the data is too large for the
> specified buffer size. How we I change the buffer size from the SQL
> Server Enterprise Manager?
> TIA,
> Jeffrey
|||Jeffery,
Could you provide more information on exactly what error message and error
message number you received?
How were you populating the column with the TEXT datatype? Via an INSERT
statement, BULK INSERT or BCP?
The more precise information we have, the better and faster we can answer
your question!
Thanks,
John
"Jeffrey" <cjeffwang@.gmail.com> wrote in message
news:bb2899d2.0410011423.5bf86f40@.posting.google.c om...
> When populating a table with a column of TEXT datatype, an error
> message was shown, indicating that the data is too large for the
> specified buffer size. How we I change the buffer size from the SQL
> Server Enterprise Manager?
> TIA,
> Jeffrey
|||I tried to populate a table with an Excel data file. To import the data
file I opened the DTS Import/Export wizard from Enterprise Manager. The
datatype is TEXT. Thanks.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||John,
I tried to populate a table of a SQL Server database with an Excel
data file. To import the data file I opened the DTS Import/Export
wizard from Enterprise Manager. The datatype of the column is TEXT
and the error message indicated that the text data is too large for
the specified buffer size.
How do I cahnge the buffer size of the database or the column?
Thanks,
Jeffrey
"John Kane" <jt-kane@.comcast.net> wrote in message news:<#FVNToAqEHA.592@.TK2MSFTNGP11.phx.gbl>...[vbcol=seagreen]
> Jeffery,
> Could you provide more information on exactly what error message and error
> message number you received?
> How were you populating the column with the TEXT datatype? Via an INSERT
> statement, BULK INSERT or BCP?
> The more precise information we have, the better and faster we can answer
> your question!
> Thanks,
> John
>
> "Jeffrey" <cjeffwang@.gmail.com> wrote in message
> news:bb2899d2.0410011423.5bf86f40@.posting.google.c om...
Change buffer size for the text datafile data
When populating a table with a column of TEXT datatype, an error
message was shown, indicating that the data is too large for the
specified buffer size. How we I change the buffer size from the SQL
Server Enterprise Manager?
TIA,
Jeffrey
what mechanism where you using to populate your table?
Does this kb apply
http://support.microsoft.com/default...&Product=sql2k
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Jeffrey" <cjeffwang@.gmail.com> wrote in message
news:bb2899d2.0410011423.5bf86f40@.posting.google.c om...
> When populating a table with a column of TEXT datatype, an error
> message was shown, indicating that the data is too large for the
> specified buffer size. How we I change the buffer size from the SQL
> Server Enterprise Manager?
> TIA,
> Jeffrey
|||Jeffery,
Could you provide more information on exactly what error message and error
message number you received?
How were you populating the column with the TEXT datatype? Via an INSERT
statement, BULK INSERT or BCP?
The more precise information we have, the better and faster we can answer
your question!
Thanks,
John
"Jeffrey" <cjeffwang@.gmail.com> wrote in message
news:bb2899d2.0410011423.5bf86f40@.posting.google.c om...
> When populating a table with a column of TEXT datatype, an error
> message was shown, indicating that the data is too large for the
> specified buffer size. How we I change the buffer size from the SQL
> Server Enterprise Manager?
> TIA,
> Jeffrey
|||John,
I tried to populate a table of a SQL Server database with an Excel
data file. To import the data file I opened the DTS Import/Export
wizard from Enterprise Manager. The datatype of the column is TEXT
and the error message indicated that the text data is too large for
the specified buffer size.
How do I cahnge the buffer size of the database or the column?
Thanks,
Jeffrey
"John Kane" <jt-kane@.comcast.net> wrote in message news:<#FVNToAqEHA.592@.TK2MSFTNGP11.phx.gbl>...[vbcol=seagreen]
> Jeffery,
> Could you provide more information on exactly what error message and error
> message number you received?
> How were you populating the column with the TEXT datatype? Via an INSERT
> statement, BULK INSERT or BCP?
> The more precise information we have, the better and faster we can answer
> your question!
> Thanks,
> John
>
> "Jeffrey" <cjeffwang@.gmail.com> wrote in message
> news:bb2899d2.0410011423.5bf86f40@.posting.google.c om...
message was shown, indicating that the data is too large for the
specified buffer size. How we I change the buffer size from the SQL
Server Enterprise Manager?
TIA,
Jeffrey
what mechanism where you using to populate your table?
Does this kb apply
http://support.microsoft.com/default...&Product=sql2k
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Jeffrey" <cjeffwang@.gmail.com> wrote in message
news:bb2899d2.0410011423.5bf86f40@.posting.google.c om...
> When populating a table with a column of TEXT datatype, an error
> message was shown, indicating that the data is too large for the
> specified buffer size. How we I change the buffer size from the SQL
> Server Enterprise Manager?
> TIA,
> Jeffrey
|||Jeffery,
Could you provide more information on exactly what error message and error
message number you received?
How were you populating the column with the TEXT datatype? Via an INSERT
statement, BULK INSERT or BCP?
The more precise information we have, the better and faster we can answer
your question!
Thanks,
John
"Jeffrey" <cjeffwang@.gmail.com> wrote in message
news:bb2899d2.0410011423.5bf86f40@.posting.google.c om...
> When populating a table with a column of TEXT datatype, an error
> message was shown, indicating that the data is too large for the
> specified buffer size. How we I change the buffer size from the SQL
> Server Enterprise Manager?
> TIA,
> Jeffrey
|||John,
I tried to populate a table of a SQL Server database with an Excel
data file. To import the data file I opened the DTS Import/Export
wizard from Enterprise Manager. The datatype of the column is TEXT
and the error message indicated that the text data is too large for
the specified buffer size.
How do I cahnge the buffer size of the database or the column?
Thanks,
Jeffrey
"John Kane" <jt-kane@.comcast.net> wrote in message news:<#FVNToAqEHA.592@.TK2MSFTNGP11.phx.gbl>...[vbcol=seagreen]
> Jeffery,
> Could you provide more information on exactly what error message and error
> message number you received?
> How were you populating the column with the TEXT datatype? Via an INSERT
> statement, BULK INSERT or BCP?
> The more precise information we have, the better and faster we can answer
> your question!
> Thanks,
> John
>
> "Jeffrey" <cjeffwang@.gmail.com> wrote in message
> news:bb2899d2.0410011423.5bf86f40@.posting.google.c om...
Wednesday, March 7, 2012
challenge...
How can i programmatically via Tsql change the datatype of all the columns of a table to varchar(1000)?
Like I have a table employee
Employee
(
colA int
colB int
colC varchar
)
If i run the tsql..
it should give me
Employee
(
colA varchar
colB varchar
colC varchar
)
Is the table empty? If not, what is the disposition of the data that is already in the table?
|||Well you could try using a change script generated by Enterprise Manager, like this one:
Code Snippet
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_Employee
(
ColA varchar(1000) NULL,
ColB varchar(1000) NULL,
ColC varchar(1000) NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.Employee)
EXEC('INSERT INTO dbo.Tmp_Employee (ColA, ColB, ColC)
SELECT CONVERT(varchar(1000), ColA), CONVERT(varchar(1000), ColB), ColC FROM dbo.Employee (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.Employee
GO
EXECUTE sp_rename N'dbo.Tmp_Employee', N'Employee', 'OBJECT'
GO
COMMIT
Please don't use the graphical tools to make schema changes. It can generate scripts that are inefficient and unnecessary. You can just use ALTER TABLE to change the column from int to varchar in this case. If you do use the tools then please make sure to review the scripts because there is lot of things that can be simplified or improved. For example, in above case there is no reason to do CREATE TABLE and INSERT. You can do SELECT...INTO - this can perform minimally logged operations & can run magnitudes of time faster.
Sunday, February 12, 2012
Casting or Converting Smallint datatype to Datetime
A SQL Server 2005 db has three date related-columns (MonthGiven,DayGiven, YearGiven) each as smallint datatype. I would like tocreate a DocDate column (datetime datatype) that combines the data fromthe three existing date-related columns. I have tried casting andsimple concatentation without success.
ALTER TABLE Details ADD DocDate DateTime NULL
UPDATE Details SET DocDate = CAST(MonthGiven AS DateTime)+ '/' + CAST(DayGiven AS DateTime) + "/" Cast(YearGiven As DateTime)
I think I need to be doing a Conversion instead of casting buthave been unable to implement info I have found in the SQL ServerDeveloper Center in my situation.
I think this should work, it works for me
UPDATE Details SET DocDate = CAST(MonthGiven AS Varchar)+ '/' + CAST(DayGiven AS Varchar) +'/' +Cast(YearGiven As Varchar)
You can also use
UPDATE Details SET DocDate = CONVERT(DateTime, CAST(MonthGiven AS Varchar)+ '/' + CAST(DayGiven AS Varchar) +'/' +Cast(YearGiven As Varchar), 101)
The last parameter (style) will change based on the input string to convert
http://msdn2.microsoft.com/en-us/library/ms187928.aspx
|||
Thanks for your prompt suggestions. I tried both and got the same error message:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Sinceboth produced the same error, I must assume that either Imisrepresented my data or there is something within the data itselfthat is producing the error. I rechecked the datatypes, etc of myoriginal post and think that is ok.
I queried the data: SELECT MonthGiven, DayGiven, YearGiven FROM Details
When a month, day, or year is unknown the data value = 0
Iam guessing that is causing the error. If so, should i replacethose values with NULL or a string of 0s (Month = 00, Day = 00, Year =0000)?
Thanks again for your input and any further assistance you can provide.
|||
To handle the null values, you can add a where clause to the update statement (add extra conditions in case other illegal values are expected)
Update ...
where MonthGiven is not null and DayGiven is not null and YearGiven is not null
Prashant,
Here is my TSQL:
ALTER TABLE Details ADD DocDate DateTime NULL
UPDATE Details SET DocDate = CONVERT(DateTime, CAST(MonthGiven ASVarchar)+ '/' + CAST(DayGiven AS Varchar) + '/' + Cast(YearGivenAs Varchar), 101) WHERE MonthGiven is not null and DayGiven isnot null and YearGiven is not null
Here is the error:
Conversion failed when converting datetime from character string.
|||
vish4forum:
I think this should work, it works for me
UPDATE Details SET DocDate = CAST(MonthGiven AS Varchar)+ '/' + CAST(DayGiven AS Varchar) +'/' +Cast(YearGiven As Varchar)
I think you can modify your table this way:
decare @.time varchar(100)
set @.time=CAST(MonthGiven AS Varchar)+ '/' + CAST(DayGiven AS Varchar) +'/' +Cast(YearGiven As Varchar)
update details set docdate=convert(datetime,@.time)
This should work well.
Hope my suggestion helps
|||Hi B.C.,
I corrected spelling of declare and ran your query with this result:
Msg 207, Level 16, State 1, Line 3
Invalid column name 'MonthGiven'.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'DayGiven'.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'YearGiven'.
|||
I am trying another approach which may simplify this problem.
ALTER TABLE Details ADD DocDate varchar(20) NULL
UPDATE Details SET DocDate = CAST(MonthGiven AS VARCHAR(5) )+ '/' +CAST(DayGiven AS VARCHAR(5)) + '/' + CAST(YearGiven AS VARCHAR(5))
Sonow I have a DocDate column in the Details table with varchar data(e.g., 9/12/2007)) that simply needs to be cast as datetime. Howcan I do that?
|||
This query executed successfully, but in Object Explorer the data incolumn DocDate still shows as VarChar(20) after refreshing thetable.
UPDATE Details SET DocDate=CONVERT(Datetime,101)
Iwas expecting it to be datetime. Any thoughts on why the queryexecuted successfully but the datatype did not change in ObjectExplorer?
|||
Hi moonshadow,
My fault. The solution i gave above is wrong. You cannot use a intermedia variable in this case.
You can try the suggestionPrashant Kumar provided above. That should work. And as to your question, "UPDATE Details SET DocDate=CONVERT(Datetime,101)
I was expecting it to be datetime " , you cannot do that-- the string which you want to convert to datetime must follow a certain format, for example, 9/12/2007 or 2007-9-12. Based on my understanding, i think convert 101 to datetime value dosn't make sense.The right format is : convert(datetime, '9/12/2007') after which you will get a datetime value.
I would suggest you reading some materials on sql datetime. You will find that's very helpful to solve your problem. thanks
|||
Thanks to all for your patience and very helpful advice.
Casting bit data type to represent something else
HI all,
I have inhereted a table that has a bit datatype with 0 representing Male
gender, and 1 Representing female gender
Is there a way I can cast/convert this to have it display M ... or F in my
select statement
Thanks
RobertSelect CASE columnName WHEN 0 THEN 'M' ELSE 'F' END AS gender ...
"Robert Bravery" <me@.u.com> wrote in message
news:u5zANm2JGHA.3936@.TK2MSFTNGP12.phx.gbl...
> HI all,
> I have inhereted a table that has a bit datatype with 0 representing Male
> gender, and 1 Representing female gender
> Is there a way I can cast/convert this to have it display M ... or F in my
> select statement
> Thanks
> Robert
>|||A bit column is nullable, so we need to adjust the select case like so:
Select CASE columnName WHEN 0 THEN 'M' WHEN 1 THEN 'F' ELSE columnName END
AS gender ...
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:e62Ywo2JGHA.528@.TK2MSFTNGP12.phx.gbl...
> Select CASE columnName WHEN 0 THEN 'M' ELSE 'F' END AS gender ...
> "Robert Bravery" <me@.u.com> wrote in message
> news:u5zANm2JGHA.3936@.TK2MSFTNGP12.phx.gbl...
>|||In his post he never mentionned NULL, so I didn't include it.
Maybe the column is not Nullable.
...so there!!! :-|
"JT" <someone@.microsoft.com> wrote in message
news:eCPq%2312JGHA.500@.TK2MSFTNGP15.phx.gbl...
>A bit column is nullable, so we need to adjust the select case like so:
> Select CASE columnName WHEN 0 THEN 'M' WHEN 1 THEN 'F' ELSE columnName END
> AS gender ...
>
> "Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
> news:e62Ywo2JGHA.528@.TK2MSFTNGP12.phx.gbl...
>|||Robert Bravery wrote:
> HI all,
> I have inhereted a table that has a bit datatype with 0 representing Male
> gender, and 1 Representing female gender
> Is there a way I can cast/convert this to have it display M ... or F in my
> select statement
> Thanks
> Robert
SELECT
CASE gender
WHEN 0 THEN 'M'
WHEN 1 THEN 'F'
FROM your_table ;
Fix the column. The proper ISO codes for gender are 0 = Unknown, 1 =
Male , 2 = Female and 9 = Not Applicable so the values for your bit
column are potentially very confusing. Personally I'd use M and F in a
CHAR(1) column.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi
Thanks a million,
Robert
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:e62Ywo2JGHA.528@.TK2MSFTNGP12.phx.gbl...
> Select CASE columnName WHEN 0 THEN 'M' ELSE 'F' END AS gender ...
> "Robert Bravery" <me@.u.com> wrote in message
> news:u5zANm2JGHA.3936@.TK2MSFTNGP12.phx.gbl...
Male
my
>|||HI,
Yes you are correct. NULL's not allowed
Thanks
Robert
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:eKlw872JGHA.208@.tk2msftngp13.phx.gbl...
> In his post he never mentionned NULL, so I didn't include it.
> Maybe the column is not Nullable.
> ...so there!!! :-|
> "JT" <someone@.microsoft.com> wrote in message
> news:eCPq%2312JGHA.500@.TK2MSFTNGP15.phx.gbl...
END
in
>|||I was just razzing JT for going pedantic on me. :-)
"Robert Bravery" <me@.u.com> wrote in message
news:%23p2jAL3JGHA.344@.TK2MSFTNGP11.phx.gbl...
> HI,
> Yes you are correct. NULL's not allowed
> Thanks
> Robert|||Ok. I'm new here so don't know all the politics
:)
Robert
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:OP9j7P3JGHA.1760@.TK2MSFTNGP10.phx.gbl...
> I was just razzing JT for going pedantic on me. :-)
> "Robert Bravery" <me@.u.com> wrote in message
> news:%23p2jAL3JGHA.344@.TK2MSFTNGP11.phx.gbl...
>|||Hi David,
Thanks for the reply.
Not my table, inherited it, so stuck with these values
Thanks
Robert
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1138824296.130937.296000@.g44g2000cwa.googlegroups.com...
> Robert Bravery wrote:
Male
my
> SELECT
> CASE gender
> WHEN 0 THEN 'M'
> WHEN 1 THEN 'F'
> FROM your_table ;
> Fix the column. The proper ISO codes for gender are 0 = Unknown, 1 =
> Male , 2 = Female and 9 = Not Applicable so the values for your bit
> column are potentially very confusing. Personally I'd use M and F in a
> CHAR(1) column.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
I have inhereted a table that has a bit datatype with 0 representing Male
gender, and 1 Representing female gender
Is there a way I can cast/convert this to have it display M ... or F in my
select statement
Thanks
RobertSelect CASE columnName WHEN 0 THEN 'M' ELSE 'F' END AS gender ...
"Robert Bravery" <me@.u.com> wrote in message
news:u5zANm2JGHA.3936@.TK2MSFTNGP12.phx.gbl...
> HI all,
> I have inhereted a table that has a bit datatype with 0 representing Male
> gender, and 1 Representing female gender
> Is there a way I can cast/convert this to have it display M ... or F in my
> select statement
> Thanks
> Robert
>|||A bit column is nullable, so we need to adjust the select case like so:
Select CASE columnName WHEN 0 THEN 'M' WHEN 1 THEN 'F' ELSE columnName END
AS gender ...
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:e62Ywo2JGHA.528@.TK2MSFTNGP12.phx.gbl...
> Select CASE columnName WHEN 0 THEN 'M' ELSE 'F' END AS gender ...
> "Robert Bravery" <me@.u.com> wrote in message
> news:u5zANm2JGHA.3936@.TK2MSFTNGP12.phx.gbl...
>|||In his post he never mentionned NULL, so I didn't include it.
Maybe the column is not Nullable.
...so there!!! :-|
"JT" <someone@.microsoft.com> wrote in message
news:eCPq%2312JGHA.500@.TK2MSFTNGP15.phx.gbl...
>A bit column is nullable, so we need to adjust the select case like so:
> Select CASE columnName WHEN 0 THEN 'M' WHEN 1 THEN 'F' ELSE columnName END
> AS gender ...
>
> "Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
> news:e62Ywo2JGHA.528@.TK2MSFTNGP12.phx.gbl...
>|||Robert Bravery wrote:
> HI all,
> I have inhereted a table that has a bit datatype with 0 representing Male
> gender, and 1 Representing female gender
> Is there a way I can cast/convert this to have it display M ... or F in my
> select statement
> Thanks
> Robert
SELECT
CASE gender
WHEN 0 THEN 'M'
WHEN 1 THEN 'F'
FROM your_table ;
Fix the column. The proper ISO codes for gender are 0 = Unknown, 1 =
Male , 2 = Female and 9 = Not Applicable so the values for your bit
column are potentially very confusing. Personally I'd use M and F in a
CHAR(1) column.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi
Thanks a million,
Robert
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:e62Ywo2JGHA.528@.TK2MSFTNGP12.phx.gbl...
> Select CASE columnName WHEN 0 THEN 'M' ELSE 'F' END AS gender ...
> "Robert Bravery" <me@.u.com> wrote in message
> news:u5zANm2JGHA.3936@.TK2MSFTNGP12.phx.gbl...
Male
my
>|||HI,
Yes you are correct. NULL's not allowed
Thanks
Robert
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:eKlw872JGHA.208@.tk2msftngp13.phx.gbl...
> In his post he never mentionned NULL, so I didn't include it.
> Maybe the column is not Nullable.
> ...so there!!! :-|
> "JT" <someone@.microsoft.com> wrote in message
> news:eCPq%2312JGHA.500@.TK2MSFTNGP15.phx.gbl...
END
in
>|||I was just razzing JT for going pedantic on me. :-)
"Robert Bravery" <me@.u.com> wrote in message
news:%23p2jAL3JGHA.344@.TK2MSFTNGP11.phx.gbl...
> HI,
> Yes you are correct. NULL's not allowed
> Thanks
> Robert|||Ok. I'm new here so don't know all the politics
:)
Robert
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:OP9j7P3JGHA.1760@.TK2MSFTNGP10.phx.gbl...
> I was just razzing JT for going pedantic on me. :-)
> "Robert Bravery" <me@.u.com> wrote in message
> news:%23p2jAL3JGHA.344@.TK2MSFTNGP11.phx.gbl...
>|||Hi David,
Thanks for the reply.
Not my table, inherited it, so stuck with these values
Thanks
Robert
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1138824296.130937.296000@.g44g2000cwa.googlegroups.com...
> Robert Bravery wrote:
Male
my
> SELECT
> CASE gender
> WHEN 0 THEN 'M'
> WHEN 1 THEN 'F'
> FROM your_table ;
> Fix the column. The proper ISO codes for gender are 0 = Unknown, 1 =
> Male , 2 = Female and 9 = Not Applicable so the values for your bit
> column are potentially very confusing. Personally I'd use M and F in a
> CHAR(1) column.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
CASTing a datatype returned by CASE Statement
I realize that the CASE statement doesn't like different datatypes as return values but if I want to format the "0" in the second WHEN condition below to "000", how do I do that? I have a "Region" that is "000" and would like it to show up that way at the very top of my report. I have the GROUP BY and ORDER BY to work fine, it just shows up as "0" and I can't change it. I realize it is being read as an int but am having trouble with the CAST and where to place it. Thanks again, you guys are great.
ddave
SELECT Region =
CASE WHEN branch_num IN(48,53,78,173,186,198,208,212,257,286,287,317,35 3,398,440,
478,571,572,610,1069) THEN 44
WHEN branch_num IN(484,532,841,864,7001,7101,7102,7103,7104,9031) THEN 0
ELSE 999
ENDThat depends (doesn't it always?) on what you really want. If you want the other regions to show using normal INT formatting, but 0 to be a special case That is one thing, if you want all the region numbers to be zero filled, that is something different. If you want something I haven't thought of yet, then that's probably different too.
The quick and dirty would be to use:SELECT Region =
CASE
WHEN branch_num IN(48,53,78,173,186,198,208,212,257,286,287,317,35 3,398,440,
478,571,572,610,1069) THEN ' 44'
WHEN branch_num IN(484,532,841,864,7001,7101,7102,7103,7104,9031) THEN '000'
ELSE '999'
END
-PatP|||Pat,
Once again, "You da Man!!". It works perfectly. I decided to use '000', ' 1', ' 78', etc. I spent over an hour on it and I knew it was something easy. I mean I don't expect a medal or anything but you can be lost w/o "the little details". Thanks again.
ddave|||If I want the format to show the Region field just once, is there a way to do that? My current report has a Region field immediately to the left of BranchNo. Branches are contained within the Regions. I got it to list Region every time I show a record but just in case the manager wants it formatted the way I mention I want to be prepared. The example I was to follow has Region just once.
This is an example of what I have now:
code:--------------------
Region BranchNo OrderNo ErrorCode1 ErrorCode2 ErrorCode3
000 478 111 0 1 1
000 478 112 0 0 0
000 478 113 1 0 0
001 610 119 0 0 0
001 610 120 1 0 0
----------------------
This is an example of what I wish to try:
code:--------------------
Region BranchNo OrderNo ErrorCode1 ErrorCode2 ErrorCode3
000 478 111 0 1 1
478 112 0 0 0
478 113 1 0 0
001 610 119 0 0 0
610 120 1 0 0
----------------------
ddave|||What reporting tool are you using? Hopefully this isn't 100% Transact-SQL based, right?
-PatP|||Well, I am looking at the data in Query Analyzer but that is a good question. I guess the real answer is that we haven't decided yet. I can use Access though I have to figure out the mechanics which I know won't be difficult. I can even stick it on an Excel spreadsheet as long as it looks good. I say Access because that is "what the others did" but it is not an issue.
ddave|||It's a presntation issue, and Access is very good at it, and can easily do what your asking...
I'd love to setup reporting services though...
Anyone seen it?
What's the installation like?
What's the interface?
Can you use the same box as sql server?
PS. If they say Crystal...run...|||Reporting Services is quite cool, but it is rather complex and it requires Visual Studio to develop reports.
MS-Access would be beauteous, and would make the formatting, grouping, etc rather simple. I'm not nearly as alergic to Crystal Reports as most folks around here seem to be, but I would STRONGLY advise using Access unless you have another tool of choice.
-PatP
ddave
SELECT Region =
CASE WHEN branch_num IN(48,53,78,173,186,198,208,212,257,286,287,317,35 3,398,440,
478,571,572,610,1069) THEN 44
WHEN branch_num IN(484,532,841,864,7001,7101,7102,7103,7104,9031) THEN 0
ELSE 999
ENDThat depends (doesn't it always?) on what you really want. If you want the other regions to show using normal INT formatting, but 0 to be a special case That is one thing, if you want all the region numbers to be zero filled, that is something different. If you want something I haven't thought of yet, then that's probably different too.
The quick and dirty would be to use:SELECT Region =
CASE
WHEN branch_num IN(48,53,78,173,186,198,208,212,257,286,287,317,35 3,398,440,
478,571,572,610,1069) THEN ' 44'
WHEN branch_num IN(484,532,841,864,7001,7101,7102,7103,7104,9031) THEN '000'
ELSE '999'
END
-PatP|||Pat,
Once again, "You da Man!!". It works perfectly. I decided to use '000', ' 1', ' 78', etc. I spent over an hour on it and I knew it was something easy. I mean I don't expect a medal or anything but you can be lost w/o "the little details". Thanks again.
ddave|||If I want the format to show the Region field just once, is there a way to do that? My current report has a Region field immediately to the left of BranchNo. Branches are contained within the Regions. I got it to list Region every time I show a record but just in case the manager wants it formatted the way I mention I want to be prepared. The example I was to follow has Region just once.
This is an example of what I have now:
code:--------------------
Region BranchNo OrderNo ErrorCode1 ErrorCode2 ErrorCode3
000 478 111 0 1 1
000 478 112 0 0 0
000 478 113 1 0 0
001 610 119 0 0 0
001 610 120 1 0 0
----------------------
This is an example of what I wish to try:
code:--------------------
Region BranchNo OrderNo ErrorCode1 ErrorCode2 ErrorCode3
000 478 111 0 1 1
478 112 0 0 0
478 113 1 0 0
001 610 119 0 0 0
610 120 1 0 0
----------------------
ddave|||What reporting tool are you using? Hopefully this isn't 100% Transact-SQL based, right?
-PatP|||Well, I am looking at the data in Query Analyzer but that is a good question. I guess the real answer is that we haven't decided yet. I can use Access though I have to figure out the mechanics which I know won't be difficult. I can even stick it on an Excel spreadsheet as long as it looks good. I say Access because that is "what the others did" but it is not an issue.
ddave|||It's a presntation issue, and Access is very good at it, and can easily do what your asking...
I'd love to setup reporting services though...
Anyone seen it?
What's the installation like?
What's the interface?
Can you use the same box as sql server?
PS. If they say Crystal...run...|||Reporting Services is quite cool, but it is rather complex and it requires Visual Studio to develop reports.
MS-Access would be beauteous, and would make the formatting, grouping, etc rather simple. I'm not nearly as alergic to Crystal Reports as most folks around here seem to be, but I would STRONGLY advise using Access unless you have another tool of choice.
-PatP
Subscribe to:
Posts (Atom)