changing the types of particular columns in particular table in the database

hi,
i have more than 300 tables in the database. Out of that tables some table has column whose type is "char".I want to change only those tables' column to the type "nvarchar".
Is it possible to make the modification all at a once?
since i am changing each and every table , this takes a lots of time.

thanks in advance,
by
singam

Hi,

first of all there is no built in function for this. YOu have to do that one by one in the script, there is sure a chance to do this automagically but I am no fan of a full automatic procedure to modify the schema of a bunch of tables.

You can (as I always do ) let the SQL Server create the script for you and execute this on your own in bacthes for better error handling (as you didn′t point out if error handling is applicable for you like in SQL Server 2005)

SELECT 'ALTER TABLE ' + C.TABLE_NAME +' ALTER COLUMN ' + C.COLUMN_NAME + ' VARCHAR(50)' --New data type and length
FROM INFORMATION_SCHEMA.Columns C
INNER JOIN INFORMATION_SCHEMA.Tables T
ON
T.TABLE_CATALOG = C.TABLE_CATALOG AND
T.TABLE_SCHEMA = C.TABLE_SCHEMA AND
T.TABLE_NAME = C.TABLE_NAME
WHERE
DATA_TYPE = 'NVARCHAR' AND --Old Type
CHARACTER_MAXIMUM_LENGTH = 50 AND --Old lenght
TABLE_TYPE = 'BASE TABLE'

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Changing the table schema in Subscriber (only)?

Hi,
I have to alter a column on a replicated table and because I cannot
have any downtime, I'm thinking about just alter a column in Subscriber
only. I tested out in the test environment if I could just alter a
column in subscriber only and it worked. Now i'm wondering if this way
is ok or not.
Thanks in advance
-December
This is a transactional replication (immediate updating).
|||This is a transactional replication (immediate updating).
|||december wrote:

> This is a transactional replication (immediate updating).
which version of SQL Server you are using ?
SQL Server 2000 or 2005?
If you have SQL Server 2005 then alter table will work for most
changes.
Regards
Amish Shah
http://shahamishm.tripod.com
|||I'm using SQL Server 2000
amish wrote:
> december wrote:
> which version of SQL Server you are using ?
> SQL Server 2000 or 2005?
> If you have SQL Server 2005 then alter table will work for most
> changes.
> Regards
> Amish Shah
> http://shahamishm.tripod.com
|||december wrote:
[vbcol=seagreen]
> I'm using SQL Server 2000
> amish wrote:
You can use sp_repladdcolumn or sp_repldropcolumn in SQL Server 2000.
If you are changing only at subscriber you can not get its effect on
your publisher.
Regards
Amish Shah
http://shahamishm.tripod.com

Changing the table schema in Subscriber (only)?

Hi,
I have to alter a column on a replicated table and because I cannot
have any downtime, I'm thinking about just alter a column in Subscriber
only. I tested out in the test environment if I could just alter a
column in subscriber only and it worked. Now i'm wondering if this way
is ok or not.
Thanks in advance
-DecemberThis is a transactional replication (immediate updating).|||This is a transactional replication (immediate updating).|||december wrote:

> This is a transactional replication (immediate updating).
which version of SQL Server you are using ?
SQL Server 2000 or 2005?
If you have SQL Server 2005 then alter table will work for most
changes.
Regards
Amish Shah
http://shahamishm.tripod.com|||I'm using SQL Server 2000
amish wrote:
> december wrote:
>
> which version of SQL Server you are using ?
> SQL Server 2000 or 2005?
> If you have SQL Server 2005 then alter table will work for most
> changes.
> Regards
> Amish Shah
> http://shahamishm.tripod.com|||december wrote:
[vbcol=seagreen]
> I'm using SQL Server 2000
> amish wrote:
You can use sp_repladdcolumn or sp_repldropcolumn in SQL Server 2000.
If you are changing only at subscriber you can not get its effect on
your publisher.
Regards
Amish Shah
http://shahamishm.tripod.comsql

Changing the table schema in Subscriber (only)?

Hi,
I have to alter a column on a replicated table and because I cannot
have any downtime, I'm thinking about just alter a column in Subscriber
only. I tested out in the test environment if I could just alter a
column in subscriber only and it worked. Now i'm wondering if this way
is ok or not.
Thanks in advance
-DecemberThis is a transactional replication (immediate updating).|||This is a transactional replication (immediate updating).|||december wrote:
> This is a transactional replication (immediate updating).
which version of SQL Server you are using ?
SQL Server 2000 or 2005?
If you have SQL Server 2005 then alter table will work for most
changes.
Regards
Amish Shah
http://shahamishm.tripod.com|||I'm using SQL Server 2000
amish wrote:
> december wrote:
> > This is a transactional replication (immediate updating).
> which version of SQL Server you are using ?
> SQL Server 2000 or 2005?
> If you have SQL Server 2005 then alter table will work for most
> changes.
> Regards
> Amish Shah
> http://shahamishm.tripod.com|||december wrote:
> I'm using SQL Server 2000
> amish wrote:
> > december wrote:
> >
> > > This is a transactional replication (immediate updating).
> > which version of SQL Server you are using ?
> > SQL Server 2000 or 2005?
> >
> > If you have SQL Server 2005 then alter table will work for most
> > changes.
> >
> > Regards
> > Amish Shah
> > http://shahamishm.tripod.com
You can use sp_repladdcolumn or sp_repldropcolumn in SQL Server 2000.
If you are changing only at subscriber you can not get its effect on
your publisher.
Regards
Amish Shah
http://shahamishm.tripod.com

Changing the size of a Varchar Field

We have a small table of about 13 million rows that needs altered. A column in the table needs to be changed from a varchar(20) to a varchar(500). When we ran the alter table script, 3 hrs later and it wasn't done running. Any suggestions on what we can do to speed up the process?

Thanks ahead of time
DMW

Edit:
We are running SQL Server 2000 and the db at the time was running in simple moodBulk copy it out, redefine the table without indexes (except clustered or primary key) or triggers, bulk copy back in with a batchsize set to keep the log from growing too large, reapply the indexes and repost the triggers.sql

Changing the identity seed & increment programatically

Hi,
I want to change the identity values of an identity column of a table
that already has rows in it, such that, it begins at zero and goes up
in even numbers - that is, like 0, 2, 4, 6, 8, etc... instead of the
usual 1, 2, 3, 4, etc...
The current values of the identity seed & identity increment are the
default values of "1" respectively.
I'm going to use the following commands. Let me know if you think
these commands will do the trick will you please?. The table name is
"A" for the sake of simplicity:-
Firstly change the identity increment value to 2
ALTER TABLE A
ALTER COLUMN identityColumn
IDENTITY (1, 2)
Then reseed all values so that odd become
DBCC CHECKIDENT('A', RESEED, 0)
Comments/corrections/suggestions much appreciated.
Al.Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<almurph@.altavista.com> wrote in message
news:1157033831.447625.273890@.b28g2000cwb.googlegroups.com...
> Hi,
>
> I want to change the identity values of an identity column of a table
> that already has rows in it, such that, it begins at zero and goes up
> in even numbers - that is, like 0, 2, 4, 6, 8, etc... instead of the
> usual 1, 2, 3, 4, etc...
> The current values of the identity seed & identity increment are the
> default values of "1" respectively.
> I'm going to use the following commands. Let me know if you think
> these commands will do the trick will you please?. The table name is
> "A" for the sake of simplicity:-
> Firstly change the identity increment value to 2
> ALTER TABLE A
> ALTER COLUMN identityColumn
> IDENTITY (1, 2)
> Then reseed all values so that odd become
> DBCC CHECKIDENT('A', RESEED, 0)
>
> Comments/corrections/suggestions much appreciated.
> Al.
>|||I think that the best option for you is to create a new table with the
IDENTITY seed value you desire, and copy the current table (without the
IDENTITY column) to the new table, then drop the old table and rename the
new table to the old name.
I don't think that DBCC CHECKIDENT() will renumber the existing data.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<almurph@.altavista.com> wrote in message
news:1157033831.447625.273890@.b28g2000cwb.googlegroups.com...
> Hi,
>
> I want to change the identity values of an identity column of a table
> that already has rows in it, such that, it begins at zero and goes up
> in even numbers - that is, like 0, 2, 4, 6, 8, etc... instead of the
> usual 1, 2, 3, 4, etc...
> The current values of the identity seed & identity increment are the
> default values of "1" respectively.
> I'm going to use the following commands. Let me know if you think
> these commands will do the trick will you please?. The table name is
> "A" for the sake of simplicity:-
> Firstly change the identity increment value to 2
> ALTER TABLE A
> ALTER COLUMN identityColumn
> IDENTITY (1, 2)
> Then reseed all values so that odd become
> DBCC CHECKIDENT('A', RESEED, 0)
>
> Comments/corrections/suggestions much appreciated.
> Al.
>|||Hi Arnie,
Thanks for that but do you know how I would renumber the values
from 1, 2, 3, 4, to the 1, 3, 5, 7 sequence?
Puzzled,
Al.|||On the new table, set the IDENTITY seed and increment values as you wish
before you copy the old table values.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<almurph@.altavista.com> wrote in message
news:1157046915.027819.46040@.b28g2000cwb.googlegroups.com...
> Hi Arnie,
> Thanks for that but do you know how I would renumber the values
> from 1, 2, 3, 4, to the 1, 3, 5, 7 sequence?
> Puzzled,
> Al.
>

Changing the identity seed & increment programatically

Hi,
I want to change the identity values of an identity column of a table
that already has rows in it, such that, it begins at zero and goes up
in even numbers - that is, like 0, 2, 4, 6, 8, etc... instead of the
usual 1, 2, 3, 4, etc...
The current values of the identity seed & identity increment are the
default values of "1" respectively.
I'm going to use the following commands. Let me know if you think
these commands will do the trick will you please?. The table name is
"A" for the sake of simplicity:-
Firstly change the identity increment value to 2
ALTER TABLE A
ALTER COLUMN identityColumn
IDENTITY (1, 2)
Then reseed all values so that odd become
DBCC CHECKIDENT('A', RESEED, 0)
Comments/corrections/suggestions much appreciated.
Al.--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<almurph@.altavista.com> wrote in message
news:1157033831.447625.273890@.b28g2000cwb.googlegroups.com...
> Hi,
>
> I want to change the identity values of an identity column of a table
> that already has rows in it, such that, it begins at zero and goes up
> in even numbers - that is, like 0, 2, 4, 6, 8, etc... instead of the
> usual 1, 2, 3, 4, etc...
> The current values of the identity seed & identity increment are the
> default values of "1" respectively.
> I'm going to use the following commands. Let me know if you think
> these commands will do the trick will you please?. The table name is
> "A" for the sake of simplicity:-
> Firstly change the identity increment value to 2
> ALTER TABLE A
> ALTER COLUMN identityColumn
> IDENTITY (1, 2)
> Then reseed all values so that odd become
> DBCC CHECKIDENT('A', RESEED, 0)
>
> Comments/corrections/suggestions much appreciated.
> Al.
>|||I think that the best option for you is to create a new table with the
IDENTITY seed value you desire, and copy the current table (without the
IDENTITY column) to the new table, then drop the old table and rename the
new table to the old name.
I don't think that DBCC CHECKIDENT() will renumber the existing data.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<almurph@.altavista.com> wrote in message
news:1157033831.447625.273890@.b28g2000cwb.googlegroups.com...
> Hi,
>
> I want to change the identity values of an identity column of a table
> that already has rows in it, such that, it begins at zero and goes up
> in even numbers - that is, like 0, 2, 4, 6, 8, etc... instead of the
> usual 1, 2, 3, 4, etc...
> The current values of the identity seed & identity increment are the
> default values of "1" respectively.
> I'm going to use the following commands. Let me know if you think
> these commands will do the trick will you please?. The table name is
> "A" for the sake of simplicity:-
> Firstly change the identity increment value to 2
> ALTER TABLE A
> ALTER COLUMN identityColumn
> IDENTITY (1, 2)
> Then reseed all values so that odd become
> DBCC CHECKIDENT('A', RESEED, 0)
>
> Comments/corrections/suggestions much appreciated.
> Al.
>|||Hi Arnie,
Thanks for that but do you know how I would renumber the values
from 1, 2, 3, 4, to the 1, 3, 5, 7 sequence?
Puzzled,
Al.|||On the new table, set the IDENTITY seed and increment values as you wish
before you copy the old table values.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<almurph@.altavista.com> wrote in message
news:1157046915.027819.46040@.b28g2000cwb.googlegroups.com...
> Hi Arnie,
> Thanks for that but do you know how I would renumber the values
> from 1, 2, 3, 4, to the 1, 3, 5, 7 sequence?
> Puzzled,
> Al.
>

changing the default value for a column

Does anybody know how I can change the default value for a column?
I was trying to remove the default value in order to add the new one
afterwards. This is what I tried:

alter table /table-name/ drop default for /column-name/
alter table /table-name/ alter column /column-name/
(/new-decl-without-default/)

It did not work. I cannot find a solution in the documentation. Maybe
you can help me out?

Thank you,
JohanALTER TABLE table_name
DROP CONSTRAINT name_of_default_constraint

ALTER TABLE table_name
ADD CONSTRAINT name_of_default_constraint DEFAULT 123 FOR column_name
--
David Portas
SQL Server MVP
--|||David Portas schreef:
> ALTER TABLE table_name
> DROP CONSTRAINT name_of_default_constraint

Thank you for the quick reply. But how do I know the
name_of_default_constraint? I created the default value like this:

alter table /table_name/ add default /default_value/ for /column_name/

Johan|||It's a good idea to give constraints meaningful names when you create
them, otherwise the server assigns them an obscure unique identifier
name. Use a consistent naming convention and then you'll know the
constraint names for tables and columns.

You can find the name of an existing default using the Object Browser
in Query Analyzer or in the output of sp_help 'table_name'
--
David Portas
SQL Server MVP
--|||Hi,

something like this could meet your requirements:

declare @.myStatement nvarchar(4000)

select @.myStatement=('ALTER TABLE RP_CUSTOM_ATTRIBUTE DROP CONSTRAINT ' +
(select so.name
from sysobjects so, syscolumns sc
where sc.name = 'YOUR_COLUMN_NAME' and sc.cdefault = so.id))

EXEC sp_executesql @.myStatement
go

Karsten

"Johan Vervloet" <johanv@.chiro.be> schrieb im Newsbeitrag
news:41fe1db7$0$28977$e4fe514c@.news.xs4all.nl...
> David Portas schreef:
> > ALTER TABLE table_name
> > DROP CONSTRAINT name_of_default_constraint
> Thank you for the quick reply. But how do I know the
> name_of_default_constraint? I created the default value like this:
> alter table /table_name/ add default /default_value/ for /column_name/
> Johan

Changing the datatype of a column which is part of a PK

Hi All
I have a table made up of 2 columns as the PK. I am trying to write a script
that changes the datatype of one of the columns which is part of the primary
key. This is what i have tried but i seem not to be getting anywhere:
ALTER TABLE TB1 DROP CONSTRAINT PK_TB1
ALTER COLUMN ACCOUNT VARCHAR(20) NOT NULL
Is there a way to do this? I tried to drop the PKs and then do the changes
and then recreate the PK again. All this gave me errors. Thank you in
advance.> Is there a way to do this? I tried to drop the PKs and then do the changes
> and then recreate the PK again. All this gave me errors.
What does "tried" mean? What errors?
This is the way you do it. You drop the constraints (both primary *and*
foreign key), you change the column, you re-apply the constraints.|||Thank you. It worked.
"Aaron Bertrand [SQL Server MVP]" wrote:

> What does "tried" mean? What errors?
> This is the way you do it. You drop the constraints (both primary *and*
> foreign key), you change the column, you re-apply the constraints.
>
>

Changing the data schema in Transactional replication

I am using concurrent snapshot - transactional replication. If I need to add
the column to the replicated table, do I need to drop and re-create
subscription everytime? The database is about 20Gb, it takes up to 4 hours to
re-create two subscriptions. Is there any easier way of changing the data
schema with this configuration?
Please look at sp_repladdcolumn in BOL. If you are using SQL Server 2005,
ALTER TABLE will do it.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Nope, it will not help. The column need to be added into the table through
differnet software and then populated. I was doing drop article -
subscribtion then addarticle- subscription. But I had tables locked during
the snapshot creation. Then I unchecked the tables lock and received
concurrent snapshot. Drop article-subscription wokrs well, then add article
works too, but then when I add this article to the subscription I receive an
error message: needed to specify all articles? what about if I have about
100 tables-articles?
"Paul Ibison" wrote:

> Please look at sp_repladdcolumn in BOL. If you are using SQL Server 2005,
> ALTER TABLE will do it.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||Please can you post up the entire error message. Also, in order for me to
repro, can you tell me if there are any extra parts to the setup eg
anonymous subscribers that I need to know about. In fact, can you script out
the publication and I'll set up something similar tomorrow.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Changing the column color depending on a dynamic date

Hi, I have a series of columns which I need to be able to change the
background color of dynamically depending on a date which is computed
dynamically when the report is created.
I a using a textbox to display the dynamically calculated date
(e.g. =DateAdd(DateInterval.Day,5,Today).ToString("dd-MMM") )
But I cannot seem to reference this textbox object at runtime.
I also need to know how to get the actual dayname given the calculated
date above.
Any ideas about how I can do this'
Thanks
MarkusOn Apr 11, 7:19 pm, Markus...@.gmail.com wrote:
> Hi, I have a series of columns which I need to be able to change the
> background color of dynamically depending on a date which is computed
> dynamically when the report is created.
> I a using a textbox to display the dynamically calculated date
> (e.g. =DateAdd(DateInterval.Day,5,Today).ToString("dd-MMM") )
> But I cannot seem to reference this textbox object at runtime.
> I also need to know how to get the actual dayname given the calculated
> date above.
> Any ideas about how I can do this'
> Thanks
> Markus
You should use something like this in a new dataset that populates a
hidden report parameter:
SELECT DATENAME(DW, GETDATE())
Then reference the hidden parameter as part of the table's column
background property. Something like this should work:
=iif(Parameters!HiddenParameterName.Value = "Monday", "Red", "White")
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||On Apr 12, 1:07 pm, "EMartinez" <emartinez...@.gmail.com> wrote:
> On Apr 11, 7:19 pm, Markus...@.gmail.com wrote:
>
>
> > Hi, I have a series of columns which I need to be able to change the
> > background color of dynamically depending on a date which is computed
> > dynamically when the report is created.
> > I a using a textbox to display the dynamically calculated date
> > (e.g. =DateAdd(DateInterval.Day,5,Today).ToString("dd-MMM") )
> > But I cannot seem to reference this textbox object at runtime.
> > I also need to know how to get the actual dayname given the calculated
> > date above.
> > Any ideas about how I can do this'
> > Thanks
> > Markus
> You should use something like this in a new dataset that populates a
> hidden report parameter:
> SELECT DATENAME(DW, GETDATE())
> Then reference the hidden parameter as part of the table's column
> background property. Something like this should work:
> =iif(Parameters!HiddenParameterName.Value = "Monday", "Red", "White")
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant- Hide quoted text -
> - Show quoted text -
Thanks Enrique, I will give that a shot
Cheers
Markus|||On Apr 12, 5:00 pm, Markus...@.gmail.com wrote:
> On Apr 12, 1:07 pm, "EMartinez" <emartinez...@.gmail.com> wrote:
>
> > On Apr 11, 7:19 pm, Markus...@.gmail.com wrote:
> > > Hi, I have a series of columns which I need to be able to change the
> > > background color of dynamically depending on a date which is computed
> > > dynamically when the report is created.
> > > I a using a textbox to display the dynamically calculated date
> > > (e.g. =DateAdd(DateInterval.Day,5,Today).ToString("dd-MMM") )
> > > But I cannot seem to reference this textbox object at runtime.
> > > I also need to know how to get the actual dayname given the calculated
> > > date above.
> > > Any ideas about how I can do this'
> > > Thanks
> > > Markus
> > You should use something like this in a new dataset that populates a
> > hidden report parameter:
> > SELECT DATENAME(DW, GETDATE())
> > Then reference the hidden parameter as part of the table's column
> > background property. Something like this should work:
> > =iif(Parameters!HiddenParameterName.Value = "Monday", "Red", "White")
> > Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant- Hide quoted text -
> > - Show quoted text -
> Thanks Enrique, I will give that a shot
> Cheers
> Markus
You're welcome. Let me know if you need further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Changing the 6th character?

In a column I have some values for part names. The 6th character tells
you where the part came from, and this is the same scheme for every
single part in the database.
If I want to do something like return the basic name of a given part,
without the factory identifier character, I need to replace that
character with a '_' character. (So for instance '11256CA' and
'11265AA' and '11256MA' would all just get turned into '11256_A' and
only one row would be returned in the SELECT DISTINCT statement)
I know how to replace an instance of a given character using replace(),
but how can I alter a specific character in a string if all I know is
the index of the character within the string?
TIA,
-CS
scholzie wrote:
> In a column I have some values for part names. The 6th character tells
> you where the part came from, and this is the same scheme for every
> single part in the database.
> If I want to do something like return the basic name of a given part,
> without the factory identifier character, I need to replace that
> character with a '_' character. (So for instance '11256CA' and
> '11265AA' and '11256MA' would all just get turned into '11256_A' and
> only one row would be returned in the SELECT DISTINCT statement)
> I know how to replace an instance of a given character using
> replace(), but how can I alter a specific character in a string if
> all I know is the index of the character within the string?
> TIA,
> -CS
Declare @.s VARCHAR(10)
Set @.s = '1234567890'
SELECT STUFF(@.s, 6, 1, '_') -- 12345_7890
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Use STUFF...
SELECT STUFF('11265AA', 6, 1, '_')
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"scholzie" <scholzie@.gmail.com> wrote in message
news:1129066851.089291.37230@.o13g2000cwo.googlegro ups.com...
> In a column I have some values for part names. The 6th character tells
> you where the part came from, and this is the same scheme for every
> single part in the database.
> If I want to do something like return the basic name of a given part,
> without the factory identifier character, I need to replace that
> character with a '_' character. (So for instance '11256CA' and
> '11265AA' and '11256MA' would all just get turned into '11256_A' and
> only one row would be returned in the SELECT DISTINCT statement)
> I know how to replace an instance of a given character using replace(),
> but how can I alter a specific character in a string if all I know is
> the index of the character within the string?
> TIA,
> -CS
>
|||Wow, this is great! Someone recommended using substring and some ugly
concatenations but this works much nicer.
Thanks!
|||Why stuff and why not the replace function?
|||Got it... REPLACE doesnt take a index arugument. Sorry.

Changing the 6th character?

In a column I have some values for part names. The 6th character tells
you where the part came from, and this is the same scheme for every
single part in the database.
If I want to do something like return the basic name of a given part,
without the factory identifier character, I need to replace that
character with a '_' character. (So for instance '11256CA' and
'11265AA' and '11256MA' would all just get turned into '11256_A' and
only one row would be returned in the SELECT DISTINCT statement)
I know how to replace an instance of a given character using replace(),
but how can I alter a specific character in a string if all I know is
the index of the character within the string?
TIA,
-CSscholzie wrote:
> In a column I have some values for part names. The 6th character tells
> you where the part came from, and this is the same scheme for every
> single part in the database.
> If I want to do something like return the basic name of a given part,
> without the factory identifier character, I need to replace that
> character with a '_' character. (So for instance '11256CA' and
> '11265AA' and '11256MA' would all just get turned into '11256_A' and
> only one row would be returned in the SELECT DISTINCT statement)
> I know how to replace an instance of a given character using
> replace(), but how can I alter a specific character in a string if
> all I know is the index of the character within the string?
> TIA,
> -CS
Declare @.s VARCHAR(10)
Set @.s = '1234567890'
SELECT STUFF(@.s, 6, 1, '_') -- 12345_7890
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Use STUFF...
SELECT STUFF('11265AA', 6, 1, '_')
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"scholzie" <scholzie@.gmail.com> wrote in message
news:1129066851.089291.37230@.o13g2000cwo.googlegroups.com...
> In a column I have some values for part names. The 6th character tells
> you where the part came from, and this is the same scheme for every
> single part in the database.
> If I want to do something like return the basic name of a given part,
> without the factory identifier character, I need to replace that
> character with a '_' character. (So for instance '11256CA' and
> '11265AA' and '11256MA' would all just get turned into '11256_A' and
> only one row would be returned in the SELECT DISTINCT statement)
> I know how to replace an instance of a given character using replace(),
> but how can I alter a specific character in a string if all I know is
> the index of the character within the string?
> TIA,
> -CS
>|||Wow, this is great! Someone recommended using substring and some ugly
concatenations but this works much nicer.
Thanks!|||Why stuff and why not the replace function?|||Got it... REPLACE doesnt take a index arugument. Sorry.sql

Changing the 6th character?

In a column I have some values for part names. The 6th character tells
you where the part came from, and this is the same scheme for every
single part in the database.
If I want to do something like return the basic name of a given part,
without the factory identifier character, I need to replace that
character with a '_' character. (So for instance '11256CA' and
'11265AA' and '11256MA' would all just get turned into '11256_A' and
only one row would be returned in the SELECT DISTINCT statement)
I know how to replace an instance of a given character using replace(),
but how can I alter a specific character in a string if all I know is
the index of the character within the string?
TIA,
-CSscholzie wrote:
> In a column I have some values for part names. The 6th character tells
> you where the part came from, and this is the same scheme for every
> single part in the database.
> If I want to do something like return the basic name of a given part,
> without the factory identifier character, I need to replace that
> character with a '_' character. (So for instance '11256CA' and
> '11265AA' and '11256MA' would all just get turned into '11256_A' and
> only one row would be returned in the SELECT DISTINCT statement)
> I know how to replace an instance of a given character using
> replace(), but how can I alter a specific character in a string if
> all I know is the index of the character within the string?
> TIA,
> -CS
Declare @.s VARCHAR(10)
Set @.s = '1234567890'
SELECT STUFF(@.s, 6, 1, '_') -- 12345_7890
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Use STUFF...
SELECT STUFF('11265AA', 6, 1, '_')
--
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"scholzie" <scholzie@.gmail.com> wrote in message
news:1129066851.089291.37230@.o13g2000cwo.googlegroups.com...
> In a column I have some values for part names. The 6th character tells
> you where the part came from, and this is the same scheme for every
> single part in the database.
> If I want to do something like return the basic name of a given part,
> without the factory identifier character, I need to replace that
> character with a '_' character. (So for instance '11256CA' and
> '11265AA' and '11256MA' would all just get turned into '11256_A' and
> only one row would be returned in the SELECT DISTINCT statement)
> I know how to replace an instance of a given character using replace(),
> but how can I alter a specific character in a string if all I know is
> the index of the character within the string?
> TIA,
> -CS
>|||Wow, this is great! Someone recommended using substring and some ugly
concatenations but this works much nicer.
Thanks!|||Why stuff and why not the replace function?|||Got it... REPLACE doesnt take a index arugument. Sorry.

Changing the 6th character of a string?

In a column I have some values for part names. The 6th character tells
you where the part came from, and this is the same scheme for every
single part in the database.

If I want to do something like return the basic name of a given part,
without the factory identifier character, I need to replace that
character with a '_' character. (So for instance '11256CA' and
'11265AA' and '11256MA' would all just get turned into '11256_A' and
only one row would be returned in the SELECT DISTINCT statement)

I know how to replace an instance of a given character using replace(),
but how can I alter a specific character in a string if all I know is
the index of the character within the string?

TIA,
-CSscholzie (scholzie@.gmail.com) writes:
> In a column I have some values for part names. The 6th character tells
> you where the part came from, and this is the same scheme for every
> single part in the database.
> If I want to do something like return the basic name of a given part,
> without the factory identifier character, I need to replace that
> character with a '_' character. (So for instance '11256CA' and
> '11265AA' and '11256MA' would all just get turned into '11256_A' and
> only one row would be returned in the SELECT DISTINCT statement)
> I know how to replace an instance of a given character using replace(),
> but how can I alter a specific character in a string if all I know is
> the index of the character within the string?

Have you looked at substring()?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||CREATE VIEW Parts (.., generic-part, ..)
AS
SELECT .. SUBSTRING part_id,1,5) + '_'[ + SUBSTRING part_id, 7, 1) ,
...
FROM Inventory;|||Thanks. I thought substring() was for finding the instance of a
character, but I guess I read wrong.

Appreciate the help!

changing textbox expression based on column value

Please forgive what I'm sure is a novice question but I've only been
playing around with Reporting Svcs for a few days.
I have a report that's rendered from a stored procedure (no prob there
- works quite nicely). One of the grouping variables, Type, has values
such as "SS_F", "SS_P" and the like.
Naturally what I'd like to do is assign meaningful labels to these
values. Since there are only a few of them and they're static for the
report - that is, the same 4 values (well, up to 4 .. perhaps only 2 or
3 in any given run) would be returned on any run of the stored proc.
Where do I do that' Or do I need to make the assignments in code? If
so, how do I get there? And before suggesting that I do them in the
sp, I can't - it's not mine to touch :)
Thanks so much for any help!
-Steve LordYou would put in a nested iif statement. RS 2005 books online didn't have an
index listing for that but you could search on the phrase conditional
formatting
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Steve L" <slord@.circlesofcare.org> wrote in message
news:1141419699.794514.9160@.e56g2000cwe.googlegroups.com...
> Please forgive what I'm sure is a novice question but I've only been
> playing around with Reporting Svcs for a few days.
> I have a report that's rendered from a stored procedure (no prob there
> - works quite nicely). One of the grouping variables, Type, has values
> such as "SS_F", "SS_P" and the like.
> Naturally what I'd like to do is assign meaningful labels to these
> values. Since there are only a few of them and they're static for the
> report - that is, the same 4 values (well, up to 4 .. perhaps only 2 or
> 3 in any given run) would be returned on any run of the stored proc.
> Where do I do that' Or do I need to make the assignments in code? If
> so, how do I get there? And before suggesting that I do them in the
> sp, I can't - it's not mine to touch :)
> Thanks so much for any help!
> -Steve Lord
>|||Here's what I had for changing the image used in a report:
=Switch(Fields!PDPType.Value="Bug", "http://localhost/images/bug.gif",
Fields!PDPType.Value="Customization Request",
"http://localhost/images/customization.gif", Fields!PDPType.Value="New
Feature/Enhancement", "http://localhost/images/enhancement.gif",
Fields!PDPType.Value="Script Request", "http://localhost/images/script.gif",
Fields!PDPType.Value="Support Request", http://localhost/images/support.GIF)
Should work for a text field too, I think. Sorry if I'm not understanding
your original question, though, or if you run into another snag. I'm rather
a newbie at the RS stuff myself.
Clint
"Steve L" <slord@.circlesofcare.org> wrote in message
news:1141419699.794514.9160@.e56g2000cwe.googlegroups.com...
> Please forgive what I'm sure is a novice question but I've only been
> playing around with Reporting Svcs for a few days.
> I have a report that's rendered from a stored procedure (no prob there
> - works quite nicely). One of the grouping variables, Type, has values
> such as "SS_F", "SS_P" and the like.
> Naturally what I'd like to do is assign meaningful labels to these
> values. Since there are only a few of them and they're static for the
> report - that is, the same 4 values (well, up to 4 .. perhaps only 2 or
> 3 in any given run) would be returned on any run of the stored proc.
> Where do I do that' Or do I need to make the assignments in code? If
> so, how do I get there? And before suggesting that I do them in the
> sp, I can't - it's not mine to touch :)
> Thanks so much for any help!
> -Steve Lord
>

Changing Table Name to Write data dynamically.

Hello there, folks.
I have 5 tables with exactly same column types, names, and constraints
but with differnt names.(e.g. table1, table2, ..., table5)
I have a stored procedure(sp_example) that writes some calculated data to
"table1".
but I would like to modify the stored procedure to write the similiar data
(but from different sources) to other tables(table2,... table5)
depending on value passed to the stored procedure
(e.g, exec sp_example '2' will write to table2, exec sp_example '3' will
write to table3 and so on)...
I have tried to use a pattern like
==============================
create procedure sp_example @.param
as
declare @.cmd as Varchar(8000)
set @.cmd = @.cmd + 'INSERT INTO ' + getTableName(@.param)
set @.cmd = @.cmd + ' SELECT * From sometable '
Exec(@.cmd)
==============================
But the problem is that stored procedure i am working with is roughly 1000
lines
and having to write "set @.cmd = @.cmd + '...'" seems like an overkill.
So is there any other way to change the "table" name only dynamically?
Thank you in advance.> I have 5 tables with exactly same column types, names, and
constraints
> but with differnt names.(e.g. table1, table2, ..., table5)
Why? Sounds like a design flaw. Use one table and add an extra column
for whatever attribute is represented by the different table names. You
can still create views with the original table names so you shouldn't
even need to change your code.
Failing that, you could consider using a partitioned view. See Books
Online for details. Good design is the right solution rather than messy
Dynamic SQL.
David Portas
SQL Server MVP
--|||> Why? Sounds like a design flaw. Use one table and add an extra column
Yeah, it seems like so but as a programmer, i have to deal with badly
designed table structures...

> Failing that, you could consider using a partitioned view. See Books
> Online for details. Good design is the right solution rather than messy
I don't think partitioned views can be applied to the problem i have after
going through the online book...

Changing table column size

I'm new to mssql.

I need to change the size of a column of a table from char(255) to char(500). I used the line:

alter table table_name alter column column_name char(500)

When I run that command, I get a message that it was sucessful. However, when I try to enter data into the changed column, the number of characters I can enter is still 255. I check the information schema for the column and the 'character_maximum_length' field is 500.

What is the problem here? Is the maximum allowable length for char 255? How can I get a column to have 500 characters?Really?

USE Northwind
GO

CREATE TABLE myTable99(Col1 char(250))
GO

INSERT INTO myTable99(Col1)
SELECT REPLICATE('x',250) UNION ALL
SELECT REPLICATE('x',25) UNION ALL
SELECT REPLICATE('x',2)
GO

SELECT LEN(Col1) FROM myTable99
GO

-- Will Fail
INSERT INTO myTable99(Col1)
SELECT REPLICATE('x',500)
GO

ALTER TABLE myTable99 ALTER COLUMN Col1 char(500)
GO

INSERT INTO myTable99(Col1)
SELECT REPLICATE('x',500)
GO

SELECT LEN(Col1) FROM myTable99
GO

DROP TABLE myTable99
GO|||xiphias, you're probably testing the number of characters inserted by doing SELECT col_name FROM table. At this point you see only 255 characters, right? Check your QA settings (Tools/Options/Results/Maximum characters per column)|||Haha. That was the problem. I changed the settings and now I can see all the characters.

Now I try to do queries with the database with PHP and the number of characters return from the query is 255 again. Is there some setting file that I have to change in PHP?|||Your's is an interface problem...has nothing to do with sql server...

What's PHP?|||Sorry, I didn't know what was wrong..

PHP is a programming language for dynamic web pages. I guess I'll have to ask that in a different forum.

Thanks for the help.|||True, it seems you need to control from PHP point of view also.sql

Changing result set - Abbreviations

Hello,
I have a table that stores Customer data (e.g. ID, Name,
Type, Address etc)
In the column 'Type' I store either 'B' for Business
or 'P' for private.
When I return a result set I would like to see the
words 'Business' or 'Private', rather than 'B' or 'P'
How would I do this?
Thanks,
BillyThis is a multi-part message in MIME format.
--=_NextPart_000_0206_01C3600A.432E19A0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Try:
select
case
when Type =3D 'B' then 'Business'
when Type =3D 'P' then 'Private'
end
from
MyTable
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Billy" <billyrotorwing@.hotmail.com> wrote in message =news:079b01c3602a$61c354f0$a001280a@.phx.gbl...
Hello,
I have a table that stores Customer data (e.g. ID, Name, Type, Address etc)
In the column 'Type' I store either 'B' for Business or 'P' for private.
When I return a result set I would like to see the words 'Business' or 'Private', rather than 'B' or 'P'
How would I do this?
Thanks,
Billy
--=_NextPart_000_0206_01C3600A.432E19A0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Try:
select
=case
= when Type =3D 'B' then 'Business'
= when Type =3D 'P' then 'Private'
=end
from
=MyTable
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Billy" wrote in message news:079b01c3602a$61=c354f0$a001280a@.phx.gbl...Hello,I have a table that stores Customer data (e.g. ID, Name, Type, Address =etc)In the column 'Type' I store either 'B' for Business or ='P' for private. When I return a result set I would like to see the =words 'Business' or 'Private', rather than 'B' or 'P'How would I do this?Thanks,Billy

---

--=_NextPart_000_0206_01C3600A.432E19A0--

Changing Result Column Name

How do I change the column name returned in the result set of a For-
Xml-Explicit query?
Currently I get
XML_F52E2B61-18A1-11d1-B10500805F49916B
---
<root><child/>...</root>
I've tried select ( select .....for xml explicit ) AS MyName, but
that doesn't fly with query analyzer.
Cheers,
AedenAre you on SQL 2000 or 2005?
<aeden.jameson@.gmail.com> wrote in message
news:1189889127.677407.265150@.n39g2000hsh.googlegroups.com...
> How do I change the column name returned in the result set of a For-
> Xml-Explicit query?
> Currently I get
> XML_F52E2B61-18A1-11d1-B10500805F49916B
> ---
> <root><child/>...</root>
>
> I've tried select ( select .....for xml explicit ) AS MyName, but
> that doesn't fly with query analyzer.
> Cheers,
> Aeden
>|||SQL 2k
On Sep 15, 5:48 pm, "Mike C#" <x...@.xyz.com> wrote:
> Are you on SQL 2000 or 2005?
> <aeden.jame...@.gmail.com> wrote in message
> news:1189889127.677407.265150@.n39g2000hsh.googlegroups.com...
>
> > How do I change the column name returned in the result set of a For-
> > Xml-Explicit query?
> > Currently I get
> > XML_F52E2B61-18A1-11d1-B10500805F49916B
> > ---
> > <root><child/>...</root>
> > I've tried select ( select .....for xml explicit ) AS MyName, but
> > that doesn't fly with query analyzer.
> > Cheers,
> > Aeden- Hide quoted text -
> - Show quoted text -|||The only thing I can think of in SQL 2K would be to put the result into a
temporary table first and select from that. You could probably do something
with an XML variable in 2005, which is why I asked. FOR XML EXPLICIT is
deprecated in SQL 2005 and 2008. If you're pulling the result client-side,
the column name might not matter since you can usually reference the result
set columns by ordinal number.
<aeden.jameson@.gmail.com> wrote in message
news:1189916977.141263.183740@.n39g2000hsh.googlegroups.com...
> SQL 2k
>
> On Sep 15, 5:48 pm, "Mike C#" <x...@.xyz.com> wrote:
>> Are you on SQL 2000 or 2005?
>> <aeden.jame...@.gmail.com> wrote in message
>> news:1189889127.677407.265150@.n39g2000hsh.googlegroups.com...
>>
>> > How do I change the column name returned in the result set of a For-
>> > Xml-Explicit query?
>> > Currently I get
>> > XML_F52E2B61-18A1-11d1-B10500805F49916B
>> > ---
>> > <root><child/>...</root>
>> > I've tried select ( select .....for xml explicit ) AS MyName, but
>> > that doesn't fly with query analyzer.
>> > Cheers,
>> > Aeden- Hide quoted text -
>> - Show quoted text -
>