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 day of week in SQL Server 2000

Dear All,

I have one field in my table which shows the day of the week. It is
showing

Sunday = 1
Monday = 2
Tuesday = 3

But i want Monday is the first day. I know i can use DATEFIRST to
change it but it works in Query Analyzerbut when i come and see my
table it is showing the old settings. Is it possible i can change it
permanently. Any help in this regard will be higly appreciated

Regards

SHi Neha

It is not clear what you are trying to do or how your data is being held,
posting DDL (create table statements) and example data (as insert
statements) will remove any ambiguity
http://www.aspfaq.com/etiquette.asp?id=5006

If the day of the week in your table is data it will need to be updated. If
it is currently held as a value 1-7
with Sunday = 1... then try the statement

UPDATE MyTable
SET DayOfWeek = CASE WHEN DayOfWeek = 1 THEN 7 ELSE DayOfWeek - 1 END

Using SET DATEFIRST will change the settings for the current session,
therefore you will need to make sure it is set currectly before issuing your
statement. Alternatively datefirst can be change if a different default
language is specified, to check the current language use sp_helplanguage
@.@.LANGUAGE. sp_helplanguage with no parameters will show you what the values
are for each language. To change the default language use sp_configure (see
Books online for more information)

John

"Neha" <newlooks4u@.hotmail.com> wrote in message
news:76c8ae2c.0412220555.4f0326d@.posting.google.co m...
> Dear All,
> I have one field in my table which shows the day of the week. It is
> showing
> Sunday = 1
> Monday = 2
> Tuesday = 3
> But i want Monday is the first day. I know i can use DATEFIRST to
> change it but it works in Query Analyzerbut when i come and see my
> table it is showing the old settings. Is it possible i can change it
> permanently. Any help in this regard will be higly appreciated
> Regards
> S|||John Bell (jbellnewsposts@.hotmail.com) writes:
> Using SET DATEFIRST will change the settings for the current session,
> therefore you will need to make sure it is set currectly before issuing
> your statement. Alternatively datefirst can be change if a different
> default language is specified, to check the current language use
> sp_helplanguage @.@.LANGUAGE. sp_helplanguage with no parameters will show
> you what the values are for each language. To change the default
> language use sp_configure (see Books online for more information)

What you change with sp_configure is the default language for new
logins.

To change the default language for an existing login, use
sp_defaultlanguage.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Changing the Currency with Reporting Services

Hi,

I am formatting a currency field within reporting services using an expression. However i get the $ sign. I need the ￡ sign. How can i change this?

Thanks

David

Weird. It seems that once you wrap the FormatCurrency() Vb function, the localization goes out the window

Two workarounds
A)
1. Kill the FormatCurrency Wrapper function in your expression.
2. Use the Format Property of the texbox instead..so Format might equal C2 (for currency with 2 decimal places of precision.
3. Set your Language to English (United Kingdom) - found in Properties, in the International group

B)
If you simply MUST have the FormatCurrency wrapper then do the following
1. Wrap that expression in a CDBL()...so CDBL(FormatCurrency(blah))
2. Follow 2 & 3 in the steps for option A

Cheers

Changing table value being field name

Deal all,
I have a table with values like :
ProductID ProductName ProductPrice
1 Product01 1000
2 Product02 1100
3 Product03 2100
4
5...
about 113 prodcut on line (could be around 150 in the future).
I want to make a report which needs data in a row like :
Product01 Product02 Product03 ... Product113
1000 1100 2100 .. the price of Product113
Could anybody tell me how to achieve this?
TIAHI
Read this.
But let front end do front end work
http://www.aspfaq.com/show.asp?id=2462
--
Regards
R.D
--Knowledge gets doubled when shared
"Hendrick" wrote:

> Deal all,
> I have a table with values like :
> ProductID ProductName ProductPrice
> 1 Product01 1000
> 2 Product02 1100
> 3 Product03 2100
> 4
> 5...
> about 113 prodcut on line (could be around 150 in the future).
> I want to make a report which needs data in a row like :
> Product01 Product02 Product03 ... Product113
> 1000 1100 2100 .. the price of Product113
> Could anybody tell me how to achieve this?
> TIA
>
>|||Thanks, it works as intended.. However, I thought there's a solution from
SQL languange without hardcoding the 'Q' part (which is ProductName in my
case). since it could be 150 rows :(
In access I could use TRANSFORM ... PIVOT, why this nice feature not
supported in SQL Server?, Even in SQL Server 2005 is not so matching this
PIVOT easiness. SQL compliance, I guess.
Thanks anyway.
Hendrik
"R.D" <RD@.discussions.microsoft.com> wrote in message
news:748C930B-EF50-4F48-BA2E-8C4E08C7604D@.microsoft.com...
> HI
> Read this.
> But let front end do front end work
> http://www.aspfaq.com/show.asp?id=2462
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Hendrick" wrote:
>
Product113|||> In access I could use TRANSFORM ... PIVOT, why this nice feature not
> supported in SQL Server?, Even in SQL Server 2005 is not so matching this
> PIVOT easiness.
Because the smart solution is to do it in your reporting app or
presentation tier, not in the database.
David Portas
SQL Server MVP
--|||Hi Hendrick,
Could you please check your system clock.
I have you 9 hours ahead of everybody else.
...or you may have set your time zone wrong.
"Hendrick" <hbouty@.not.gmail.com> wrote in message
news:%23W5M6tE4FHA.1420@.TK2MSFTNGP09.phx.gbl...
> Deal all,
> I have a table with values like :
> ProductID ProductName ProductPrice
> 1 Product01 1000
> 2 Product02 1100
> 3 Product03 2100
> 4
> 5...
> about 113 prodcut on line (could be around 150 in the future).
> I want to make a report which needs data in a row like :
> Product01 Product02 Product03 ... Product113
> 1000 1100 2100 .. the price of Product113
> Could anybody tell me how to achieve this?
> TIA
>|||It's the time zone.
Thanks
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:ersbj7H4FHA.3592@.TK2MSFTNGP12.phx.gbl...
> Hi Hendrick,
> Could you please check your system clock.
> I have you 9 hours ahead of everybody else.
> ...or you may have set your time zone wrong.
> "Hendrick" <hbouty@.not.gmail.com> wrote in message
> news:%23W5M6tE4FHA.1420@.TK2MSFTNGP09.phx.gbl...
Product113
>|||Hmmm, and now one day ahead. Fixed.
"Hendrick" <hbouty@.not.gmail.com> wrote in message
news:#xafTcI4FHA.3636@.TK2MSFTNGP09.phx.gbl...
> It's the time zone.
> Thanks
> "Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
> news:ersbj7H4FHA.3592@.TK2MSFTNGP12.phx.gbl...
> Product113
>

Changing Remote Schemas

Running Win 2K & SQL 2K
I have 15 remote locations that all have transactional
publications that are pushed to my corp. office. I need
to add a field to one of the tables that are replicated
to the corp. office at all 15 locations. So my question
is this...
What is the easiest way of accomplishing this? I know I
can add a field to a published table and the change in
schema will be replicated to the subscriber, but what
about when there are 15 publishers and only one
subscriber? How will SQL handle this?
Any help is appreciated.
Larry...
for one publisher to many susbcribers you use sp_repladdcolumn or sp_repldropcolumn.
however you have 15 publishers replicating to a single subscriber. You have probably done a no-sync subscription as well.
I suggest the best approach would be to drop the 14 or the 15 subscriptions. Make the changes on one Publisher, when the Susbcriber has received the change and the smoke settles, dust clears, make the change on the remaining 14 subscribers, and then resyn
c again using the nosync option.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

changing ordinal position of a field

Does anyone have the idea of how to change the ordinal position of a
field using DDL statements in access 97? I know i'm posting it in
incorrect group, as this is the only active group i've visited till
now.
thanks in advance<rameshsaive@.gmail.com> wrote in message
news:1140699343.348758.133780@.e56g2000cwe.googlegroups.com...
> Does anyone have the idea of how to change the ordinal position of a
> field using DDL statements in access 97? I know i'm posting it in
> incorrect group, as this is the only active group i've visited till
> now.
> thanks in advance
>
I'm not sure what that matters to you. The database stores the data in
whatever order makes the best sense to it. That said, the easy way would
be to do something like:
SELECT col1, col2, col3 -- Use the order that you want the data
displayed in
INTO SomeTempTable
FROM OldTable
DROP OldTabe
SELECT *
INTO NewTable
FROM SomeTempTable
DROP SomeTempTable
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks Rick for your info.
There is no problem with the order of the fields in the database. But
it causing me a hell of a problems in my 100+ reports developed in
crystal reports 8.5.
I've a table named "employee", has a field named "postdesc". Initially
the size of the field "postdesc" is limited to 100 but now it has
increased to 200. Unfortunately, i've dropped the column & recreated
it using DDL Statements.
But this has changed the ordinal position of the field (it has added
the field at the end of the table). Now the reports which depends on
this field is not working properly. i know this is a problem with
crystal reports, but it is very cumbursome to change 100+ reports.
Please help me on this.|||<rameshsaive@.gmail.com> wrote in message
news:1140701965.950863.124030@.u72g2000cwu.googlegroups.com...
> Thanks Rick for your info.
> There is no problem with the order of the fields in the database. But
> it causing me a hell of a problems in my 100+ reports developed in
> crystal reports 8.5.
> I've a table named "employee", has a field named "postdesc". Initially
> the size of the field "postdesc" is limited to 100 but now it has
> increased to 200. Unfortunately, i've dropped the column & recreated
> it using DDL Statements.
> But this has changed the ordinal position of the field (it has added
> the field at the end of the table). Now the reports which depends on
> this field is not working properly. i know this is a problem with
> crystal reports, but it is very cumbursome to change 100+ reports.
> Please help me on this.
>
I understand now..
You should be able to go into Access and open the Table Designer. From
there, you should be able to move the row to where you want it in the list.
If that doesn't work, there is always VBScripting. ;-)
Rick Sawtell|||This will not fix your current problem, but ...
When at all possible use views instead of tables in crystal reports. When
the underlying table changes crystal will be unaffected.
If you change structure of the view (add or remove a field) you will still
need to go into crystal and verify the database though.
Just some general advice to make working with crystal a little bit easier.
<rameshsaive@.gmail.com> wrote in message
news:1140701965.950863.124030@.u72g2000cwu.googlegroups.com...
> Thanks Rick for your info.
> There is no problem with the order of the fields in the database. But
> it causing me a hell of a problems in my 100+ reports developed in
> crystal reports 8.5.
> I've a table named "employee", has a field named "postdesc". Initially
> the size of the field "postdesc" is limited to 100 but now it has
> increased to 200. Unfortunately, i've dropped the column & recreated
> it using DDL Statements.
> But this has changed the ordinal position of the field (it has added
> the field at the end of the table). Now the reports which depends on
> this field is not working properly. i know this is a problem with
> crystal reports, but it is very cumbursome to change 100+ reports.
> Please help me on this.
>

changing one field updates 3 other fields, use a join?

i currently have a table like this..

user_id username app_id app2_id app3_id app4_id
1 john 3 4 5 6
2 mike 4 5 6 6
3 manager 4 5 6 6
4 vicepres 5 6 6 6
5 ceo 6 6 6 6
6 board 6 6 6 6

the basic pattern is... a user has approvers, and those approvers have approvers as well... i have 4 columns of approvers.. and if my first approver is the manager, then my second approver will be the managers approver and my third approver will be the managers approver's approver and so on..

on my actual page, i have select fields for the app, app2, app3, app4 and i need it so that when i change the very first app, it'll automatically update app2, app3, and app4

any ideas how i would do this? im pretty new to sql but im thinking i would use some type of join?

thanksLook into triggers. You'd make an UPDATE trigger that executes automatically when an update occurs on the table. You can then check to see if the update is to app_id, and make changes to the other fields accordingly.

There are two types of triggers, INSTEAD OF and AFTER. You need to determine if you want to replace the original UPDATE with some SQL you want the trigger to execute, or if you want the trigger to execute after the original UPDATE occurrs.|||Look into triggers. You'd make an UPDATE trigger that executes automatically when an update occurs on the table. You can then check to see if the update is to app_id, and make changes to the other fields accordingly.

There are two types of triggers, INSTEAD OF and AFTER. You need to determine if you want to replace the original UPDATE with some SQL you want the trigger to execute, or if you want the trigger to execute after the original UPDATE occurrs.

im pretty new to sql, im reading up on triggers and examples of them right now.. they show some code for the triggers.. would that whole code be the entire SQL query or would i code that in perl ( what im using that to access the SQL database)?

thanks|||You actually store triggers at the table level of the DB. In Enterprise Manager, drill down into your DB, and down to your table. Then right click on the table you wish to apply a trigger to and select "All Tasks" then "Manage Triggers".|||You actually store triggers at the table level of the DB. In Enterprise Manager, drill down into your DB, and down to your table. Then right click on the table you wish to apply a trigger to and select "All Tasks" then "Manage Triggers".
ahh ok, found it, thanks|||ok i got it working... so now, if i update an approver for a specific user, the trigger will run and go ahead and update the app2, app3, and app4 so it does what it's suppose to.

so i still have my original table as above, but now i added this trigger to the table:

CREATE TRIGGER approvers ON users FOR UPDATE AS
IF UPDATE(approver_id)
BEGIN
DECLARE @.thisUser VARCHAR(10)
DECLARE @.newApp VARCHAR(10)
DECLARE @.newApp2 VARCHAR(10)
DECLARE @.newApp3 VARCHAR(10)
DECLARE @.newApp4 VARCHAR(10)

SELECT @.thisUser = (SELECT user_id FROM Inserted )
SELECT @.newApp = (SELECT approver_id FROM Inserted)
SELECT @.newApp2 = (SELECT approver_id from users WHERE user_id = @.newApp)
SELECT @.newApp3 = (SELECT approver_id from users WHERE user_id = @.newApp2)
SELECT @.newApp4 = (SELECT approver_id from users WHERE user_id = @.newApp3)

UPDATE users SET app2_id = @.newApp2 WHERE user_id = @.thisUser
UPDATE users SET app3_id = @.newApp3 WHERE user_id = @.thisUser
UPDATE users SET app4_id = @.newApp4 WHERE user_id = @.thisUser
END

but i just thought of a problem, say i update the approver of an approver, i.e. i update app_id of manager and change it from vice pres to the ceo, so i change app_id for the manager from 4 to 5.. then all the rows that had the manager as an app, app_2, app_3, or app_4 would need to be updated to since the managers approve is no longer the same person, the other peoples app2, wouldnt be the same and therefore their app3, and app4 wont be the same either .. i hope someone understand what im saying... how should i go about fixing this? like if i change the approver of manager, would i have to return all the rows that had manager as an app, app_2, app_3, or app_4 and then update all of those rows somehow? like looping through each of the rows returned?

im really new to sql, i hope someone can guide me through this

thanks|||Look into "Cascading Triggers". That may help you out.|||got it working now, thanks :)

Changing number to date

I have a table in SQL Server that has a date field whose properties are 'number'. I want to export this data to a table in Oracle. I can't touch the Server table to alter it to be a date. Is there some way I can do this in the Oracle table? Example- SQL Server= 20030324 (March 24,2003). In Oracle I want it to read '24/03/2003', or some other date format.declare @.d as int
set @.d = 20031105
select cast(substring(cast(@.d as varchar(8)),5,2)+ '/' +
substring(cast(@.d as varchar(8)),7,2)+ '/' +
left(cast(@.d as varchar(8)),4)as smalldatetime)|||I need to update a whole column in an Oracle table. Thanks.

Changing Index Question

Hi All,
I'm trying to desgin a table that will reset the index depending on field
values. Example:
INDX Date1 NETID JunkField
-- -- -- --
1 05/05/05 USER1 1
2 05/05/05 USER1 1
3 05/05/05 USER1 1
1 05/05/05 USER2 1
1 05/06/05 USER2 1
Every time there is a new user NETID or the DATE1 values changes, the INDX
value should reset back to 1. Could someone provide me with a simple SQL
script. I'm new to this.
Thanks.Triggers may help you acheive this. Have a look at :-
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\tsq
lref.chm::/ts_create2_7eeq.htm
--
HTH
Ryan Waight, MCDBA, MCSE
"JackV" <vituja@.consumer.org> wrote in message
news:%23guTPiRgDHA.620@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I'm trying to desgin a table that will reset the index depending on field
> values. Example:
> INDX Date1 NETID JunkField
> -- -- -- --
> 1 05/05/05 USER1 1
> 2 05/05/05 USER1 1
> 3 05/05/05 USER1 1
> 1 05/05/05 USER2 1
> 1 05/06/05 USER2 1
> Every time there is a new user NETID or the DATE1 values changes, the INDX
> value should reset back to 1. Could someone provide me with a simple SQL
> script. I'm new to this.
> Thanks.
>|||Hi Ryan,
The link doesn't seem to work. Can you resend it. Thanks.
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:eO0gI9RgDHA.2292@.TK2MSFTNGP10.phx.gbl...
> Triggers may help you acheive this. Have a look at :-
>
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\tsq
> lref.chm::/ts_create2_7eeq.htm
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "JackV" <vituja@.consumer.org> wrote in message
> news:%23guTPiRgDHA.620@.TK2MSFTNGP11.phx.gbl...
> > Hi All,
> >
> > I'm trying to desgin a table that will reset the index depending on
field
> > values. Example:
> >
> > INDX Date1 NETID JunkField
> > -- -- -- --
> > 1 05/05/05 USER1 1
> > 2 05/05/05 USER1 1
> > 3 05/05/05 USER1 1
> > 1 05/05/05 USER2 1
> > 1 05/06/05 USER2 1
> >
> > Every time there is a new user NETID or the DATE1 values changes, the
INDX
> > value should reset back to 1. Could someone provide me with a simple
SQL
> > script. I'm new to this.
> >
> > Thanks.
> >
> >
>

Changing Group BackgroundColor from Field Values

Hello Everybody,

I'am working with "Reporting Services 2005".
I would like to have specific Colors for a Group.

e.g.

+Apel: (Group, f.e. "green")

+peach: (Group, f.e. "red")
....

Apel and peach have each a color assigned in the database.
Each Report can have different Fruits on it, but they are defined by their color.

Could change the color statically and and toggle Details Colors, but nut this!

Thanks very much for any kind of help!

Have your query return the background color. THen add that color field to your group. Set the rowdetail background to the color from the dataset.|||Thanks very much, it works!

changing fieldsize in a replicated database

I am looking for a way to increase the size of a varchar field in a
replicated database, without disturbing the data of course. I do not
want to remove the replication just for this one issue. What would the
process be in writing a SQL query to accomplish this? The table name is
tblBid, and the field is bidNotes. It is a varchar(500), and I would
like it to be 900. Is there a size limitation on varchars?
Thanks.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Daniel,
Directly this can't be done. Indirectly it can, but not nicely! You could
add a new column with the new datatype (sp_repladdcolumn), do an update on
the table to populate the column, then drop the column (sp_repldropcolumn).
Do this again to create the column having the same original name.
BTW this is available directly using Alter Table in SQL 2005.
Rgds,
Paul Ibison (SQL Server MVP)
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Changing Field/Column Name

Hello:
I'm working on a Crystal report for a client and, in order for it to work
without errors, I have to change the name of a field (column) in a table.
You see, the field name is two words called "[Print Count]". The space
between these two words, believe it or not, causes errors in my report.
So, I took out the space in this field so that the field name reads
"PrintCount". That fixed my report.
Well, unfortunately, doing this caused problems elsewhere as this name
change did not update (filter to) a SQL View that a user needs in order to
conduct his daily business activities. So, I had to go back and reverse what
I did and add that space back in.
I really did not think that changing the name of a field would cause issues.
But, like I said, changing the name in this table did not update the View
that is based on this table and other tables.
How do I change this field name to be without this space and have this
change update everywhere else effectively in the database including in this
View? I tried to change it subsequently in the View but could not figure out
how to do so.
Any insights would be appreciated!
Thanks!
childofthe1980s
> But, like I said, changing the name in this table did not update the View
> that is based on this table and other tables.
Correct. This is by design.

> How do I change this field name to be without this space and have this
> change update everywhere else effectively in the database including in this
> View?
There might exist some tools that keep control of all your objects and dependencies that can handle
this for you. There's nothing built into SQL Server or any of the tools that comes with SQL Server.

> I tried to change it subsequently in the View but could not figure out
> how to do so.
ALTER VIEW. Or, of you did SELECT * (horror), you could have used sp_refreshview.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
news:D2FF25C0-D581-4027-BA45-E2D5CC40D597@.microsoft.com...
> Hello:
> I'm working on a Crystal report for a client and, in order for it to work
> without errors, I have to change the name of a field (column) in a table.
> You see, the field name is two words called "[Print Count]". The space
> between these two words, believe it or not, causes errors in my report.
> So, I took out the space in this field so that the field name reads
> "PrintCount". That fixed my report.
> Well, unfortunately, doing this caused problems elsewhere as this name
> change did not update (filter to) a SQL View that a user needs in order to
> conduct his daily business activities. So, I had to go back and reverse what
> I did and add that space back in.
> I really did not think that changing the name of a field would cause issues.
> But, like I said, changing the name in this table did not update the View
> that is based on this table and other tables.
> How do I change this field name to be without this space and have this
> change update everywhere else effectively in the database including in this
> View? I tried to change it subsequently in the View but could not figure out
> how to do so.
> Any insights would be appreciated!
> Thanks!
> childofthe1980s
|||Is the view referencing the column name directly? If so, you'd need to
change it there too. If you're
doing a SELECT * on the table within the view, then run sp_refreshview to
update the view metadata.
For columns that have spaces, put brackets around them. ie select [print
count] from mytable
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:D2FF25C0-D581-4027-BA45-E2D5CC40D597@.microsoft.com...
> Hello:
> I'm working on a Crystal report for a client and, in order for it to work
> without errors, I have to change the name of a field (column) in a table.
> You see, the field name is two words called "[Print Count]". The space
> between these two words, believe it or not, causes errors in my report.
> So, I took out the space in this field so that the field name reads
> "PrintCount". That fixed my report.
> Well, unfortunately, doing this caused problems elsewhere as this name
> change did not update (filter to) a SQL View that a user needs in order to
> conduct his daily business activities. So, I had to go back and reverse
what
> I did and add that space back in.
> I really did not think that changing the name of a field would cause
issues.
> But, like I said, changing the name in this table did not update the View
> that is based on this table and other tables.
> How do I change this field name to be without this space and have this
> change update everywhere else effectively in the database including in
this
> View? I tried to change it subsequently in the View but could not figure
out
> how to do so.
> Any insights would be appreciated!
> Thanks!
> childofthe1980s

Changing Field/Column Name

Hello:
I'm working on a Crystal report for a client and, in order for it to work
without errors, I have to change the name of a field (column) in a table.
You see, the field name is two words called "[Print Count]". The space
between these two words, believe it or not, causes errors in my report.
So, I took out the space in this field so that the field name reads
"PrintCount". That fixed my report.
Well, unfortunately, doing this caused problems elsewhere as this name
change did not update (filter to) a SQL View that a user needs in order to
conduct his daily business activities. So, I had to go back and reverse what
I did and add that space back in.
I really did not think that changing the name of a field would cause issues.
But, like I said, changing the name in this table did not update the View
that is based on this table and other tables.
How do I change this field name to be without this space and have this
change update everywhere else effectively in the database including in this
View? I tried to change it subsequently in the View but could not figure out
how to do so.
Any insights would be appreciated!
Thanks!
childofthe1980s> But, like I said, changing the name in this table did not update the View
> that is based on this table and other tables.
Correct. This is by design.
> How do I change this field name to be without this space and have this
> change update everywhere else effectively in the database including in this
> View?
There might exist some tools that keep control of all your objects and dependencies that can handle
this for you. There's nothing built into SQL Server or any of the tools that comes with SQL Server.
> I tried to change it subsequently in the View but could not figure out
> how to do so.
ALTER VIEW. Or, of you did SELECT * (horror), you could have used sp_refreshview.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
news:D2FF25C0-D581-4027-BA45-E2D5CC40D597@.microsoft.com...
> Hello:
> I'm working on a Crystal report for a client and, in order for it to work
> without errors, I have to change the name of a field (column) in a table.
> You see, the field name is two words called "[Print Count]". The space
> between these two words, believe it or not, causes errors in my report.
> So, I took out the space in this field so that the field name reads
> "PrintCount". That fixed my report.
> Well, unfortunately, doing this caused problems elsewhere as this name
> change did not update (filter to) a SQL View that a user needs in order to
> conduct his daily business activities. So, I had to go back and reverse what
> I did and add that space back in.
> I really did not think that changing the name of a field would cause issues.
> But, like I said, changing the name in this table did not update the View
> that is based on this table and other tables.
> How do I change this field name to be without this space and have this
> change update everywhere else effectively in the database including in this
> View? I tried to change it subsequently in the View but could not figure out
> how to do so.
> Any insights would be appreciated!
> Thanks!
> childofthe1980s|||Is the view referencing the column name directly? If so, you'd need to
change it there too. If you're
doing a SELECT * on the table within the view, then run sp_refreshview to
update the view metadata.
For columns that have spaces, put brackets around them. ie select [print
count] from mytable
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:D2FF25C0-D581-4027-BA45-E2D5CC40D597@.microsoft.com...
> Hello:
> I'm working on a Crystal report for a client and, in order for it to work
> without errors, I have to change the name of a field (column) in a table.
> You see, the field name is two words called "[Print Count]". The space
> between these two words, believe it or not, causes errors in my report.
> So, I took out the space in this field so that the field name reads
> "PrintCount". That fixed my report.
> Well, unfortunately, doing this caused problems elsewhere as this name
> change did not update (filter to) a SQL View that a user needs in order to
> conduct his daily business activities. So, I had to go back and reverse
what
> I did and add that space back in.
> I really did not think that changing the name of a field would cause
issues.
> But, like I said, changing the name in this table did not update the View
> that is based on this table and other tables.
> How do I change this field name to be without this space and have this
> change update everywhere else effectively in the database including in
this
> View? I tried to change it subsequently in the View but could not figure
out
> how to do so.
> Any insights would be appreciated!
> Thanks!
> childofthe1980s

Changing Field/Column Name

Hello:
I'm working on a Crystal report for a client and, in order for it to work
without errors, I have to change the name of a field (column) in a table.
You see, the field name is two words called "[Print Count]". The space
between these two words, believe it or not, causes errors in my report.
So, I took out the space in this field so that the field name reads
"PrintCount". That fixed my report.
Well, unfortunately, doing this caused problems elsewhere as this name
change did not update (filter to) a SQL View that a user needs in order to
conduct his daily business activities. So, I had to go back and reverse wha
t
I did and add that space back in.
I really did not think that changing the name of a field would cause issues.
But, like I said, changing the name in this table did not update the View
that is based on this table and other tables.
How do I change this field name to be without this space and have this
change update everywhere else effectively in the database including in this
View? I tried to change it subsequently in the View but could not figure ou
t
how to do so.
Any insights would be appreciated!
Thanks!
childofthe1980s> But, like I said, changing the name in this table did not update the View
> that is based on this table and other tables.
Correct. This is by design.

> How do I change this field name to be without this space and have this
> change update everywhere else effectively in the database including in thi
s
> View?
There might exist some tools that keep control of all your objects and depen
dencies that can handle
this for you. There's nothing built into SQL Server or any of the tools that
comes with SQL Server.

> I tried to change it subsequently in the View but could not figure out
> how to do so.
ALTER VIEW. Or, of you did SELECT * (horror), you could have used sp_refresh
view.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in messa
ge
news:D2FF25C0-D581-4027-BA45-E2D5CC40D597@.microsoft.com...
> Hello:
> I'm working on a Crystal report for a client and, in order for it to work
> without errors, I have to change the name of a field (column) in a table.
> You see, the field name is two words called "[Print Count]". The spac
e
> between these two words, believe it or not, causes errors in my report.
> So, I took out the space in this field so that the field name reads
> "PrintCount". That fixed my report.
> Well, unfortunately, doing this caused problems elsewhere as this name
> change did not update (filter to) a SQL View that a user needs in order to
> conduct his daily business activities. So, I had to go back and reverse w
hat
> I did and add that space back in.
> I really did not think that changing the name of a field would cause issue
s.
> But, like I said, changing the name in this table did not update the View
> that is based on this table and other tables.
> How do I change this field name to be without this space and have this
> change update everywhere else effectively in the database including in thi
s
> View? I tried to change it subsequently in the View but could not figure
out
> how to do so.
> Any insights would be appreciated!
> Thanks!
> childofthe1980s|||Is the view referencing the column name directly? If so, you'd need to
change it there too. If you're
doing a SELECT * on the table within the view, then run sp_refreshview to
update the view metadata.
For columns that have spaces, put brackets around them. ie select [pr
int
count] from mytable
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:D2FF25C0-D581-4027-BA45-E2D5CC40D597@.microsoft.com...
> Hello:
> I'm working on a Crystal report for a client and, in order for it to work
> without errors, I have to change the name of a field (column) in a table.
> You see, the field name is two words called "[Print Count]". The spac
e
> between these two words, believe it or not, causes errors in my report.
> So, I took out the space in this field so that the field name reads
> "PrintCount". That fixed my report.
> Well, unfortunately, doing this caused problems elsewhere as this name
> change did not update (filter to) a SQL View that a user needs in order to
> conduct his daily business activities. So, I had to go back and reverse
what
> I did and add that space back in.
> I really did not think that changing the name of a field would cause
issues.
> But, like I said, changing the name in this table did not update the View
> that is based on this table and other tables.
> How do I change this field name to be without this space and have this
> change update everywhere else effectively in the database including in
this
> View? I tried to change it subsequently in the View but could not figure
out
> how to do so.
> Any insights would be appreciated!
> Thanks!
> childofthe1980s

Changing field type

I'm using my own data processing extension to provide a dataset to a report -
the dataset is beging generatied by VB code and passed in form of XML to the
report as a parameter. Everything works fine except from that type of all
fields is String (although they are passed as doubles in the code), so I'm
not able to show graphs on the report. I've tried to solve the problem by
adding following line:
<rd:TypeName>System.Double</rd:TypeName>
to each datafield in the RDL-file. But it didn't help.
Is it something else I could try to solve this problem?Right-click on the fields list and click "add...". Give it a name and
specify a calculated field of:
=cint(Fields!fieldname.Value)
Mike G.
"Anna" <Anna@.discussions.microsoft.com> wrote in message
news:75B8EF07-1159-4824-93F2-FDF74863DF20@.microsoft.com...
> I'm using my own data processing extension to provide a dataset to a
> report -
> the dataset is beging generatied by VB code and passed in form of XML to
> the
> report as a parameter. Everything works fine except from that type of all
> fields is String (although they are passed as doubles in the code), so I'm
> not able to show graphs on the report. I've tried to solve the problem by
> adding following line:
> <rd:TypeName>System.Double</rd:TypeName>
> to each datafield in the RDL-file. But it didn't help.
> Is it something else I could try to solve this problem?|||Anna,
Maybe it would work if you create a fuction in the Custom Code section,
which casts the strings to double's
Something like
Function ConvertToDouble(sValue as String)
return Double.Parse(sValue)
end Function
And as your chart data value you sould add
'=Code.ConvertToDouble(Fields!stringValue)' to use the convert values as your
chart input.
Jan Pieter Posthuma
"Anna" wrote:
> I'm using my own data processing extension to provide a dataset to a report -
> the dataset is beging generatied by VB code and passed in form of XML to the
> report as a parameter. Everything works fine except from that type of all
> fields is String (although they are passed as doubles in the code), so I'm
> not able to show graphs on the report. I've tried to solve the problem by
> adding following line:
> <rd:TypeName>System.Double</rd:TypeName>
> to each datafield in the RDL-file. But it didn't help.
> Is it something else I could try to solve this problem?|||Sorry, didn't notice the "double" until I read Jan's post. Of course, that
means using cdbl instead of cint. (or using Jan's suggestion and creating a
function)
Mike G.
"Mike G." <theNOSPAMjunkbox@.comcast.net> wrote in message
news:e6Mh5j0TFHA.584@.TK2MSFTNGP15.phx.gbl...
> Right-click on the fields list and click "add...". Give it a name and
> specify a calculated field of:
> =cint(Fields!fieldname.Value)
> Mike G.
>
> "Anna" <Anna@.discussions.microsoft.com> wrote in message
> news:75B8EF07-1159-4824-93F2-FDF74863DF20@.microsoft.com...
>> I'm using my own data processing extension to provide a dataset to a
>> report -
>> the dataset is beging generatied by VB code and passed in form of XML to
>> the
>> report as a parameter. Everything works fine except from that type of all
>> fields is String (although they are passed as doubles in the code), so
>> I'm
>> not able to show graphs on the report. I've tried to solve the problem by
>> adding following line:
>> <rd:TypeName>System.Double</rd:TypeName>
>> to each datafield in the RDL-file. But it didn't help.
>> Is it something else I could try to solve this problem?
>

Changing field props in SQL Management Studio

Hi,
I have average database with something like 15M records. I have to change
some fields property of tables with average 4M records. On smaller tables I
can change one field at time and save design would be successful but on
larger tables I got timeout something like 25 seconds after pressing OK.
Where I can change timeout period or disable it?
Tried to put 0 on Connection window, changed to 0 remote query timeout, but
it does not change anything.
Thanks
RobertI strongly encourage you to do these changes using ALTER TABLE instead of the GUI since the GUI can
be quite clumsy regarding the SQL it generates.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Piasecznik" <WYpiasecznikWAL@.op.pl> wrote in message news:frdfec$1gmi$1@.news2.ipartners.pl...
> Hi,
> I have average database with something like 15M records. I have to change some fields property of
> tables with average 4M records. On smaller tables I can change one field at time and save design
> would be successful but on larger tables I got timeout something like 25 seconds after pressing
> OK.
> Where I can change timeout period or disable it?
> Tried to put 0 on Connection window, changed to 0 remote query timeout, but it does not change
> anything.
> Thanks
> Robert
>

Changing Field names is SQL Server 2000

Why is it that when you change a field name is SQL Server it sometimes completely messes things up. I renamed a field in one of my tables from Emp# to EmpNumber. I had a view based on this table and naturally I knew I would have to change a view I had based on the table. I opened the view and changed the field from Emp# to EmpNumber but when I tried to open the view I got an error Invalid column Name EMP#. I have not been able to fix this. I have dropped and recreated the view, refreshed all the objects using enterprise manager, refreshed all the objects using Query Analyzer, shut down and restarted my computer, taken my database offline and put it back on line. The field name EMP# is not in any tables in my database and not referenced any views or procs . I am just starting on this database so I could verify this very easily because I only have a few views and procs.

Has anyone had this problem or more importantly does anyone know why this is happening or how to fix it?Not had this issue before. Did you try querying syscolumns for Emp#?

Regards,

hmscott|||I just queried syscolumns and there was a field name Emp# in an adhoc view that was unrelated to any other queries. I deleted the adhoc view but I'm still getting the the error on my other view for invalid column name Emp#.

One thing I didn't mention in my other post is that I'm also using an MS Access ADP with this database. I've heard that access can sometimes cause problems with SQL Server. I've been making view, procs and table design in both MS Access and SQL Server - it depends on what I'm doing. I don't Access could possibly cause this kind of problems.

Any other advice or possible solutions to this problem would be appreciated.|||When you say you opened the view and changed the view, what were you using? Where is the error message coming from? From Access, from SSMS (or SQL EM)?

Is there an associated error number?

If you are using MS Access and the view is defined as a linked table, you will have to delete and recreate the link.

Regards,

hmscott|||Thanks for your imput hmscott but Im fairly new to SQL Server so I really dont know what youre talking about when you say SSMS (or SQL EM)?

I dont remember whether or not I actually changed the view in Access or Enterprise manager. Im using an Access project that connects directly to the SQL Server database, its not like ODBC linked tables, I can make changes to any of the sql database objects directly through the Access UI.

I dont receive an error number , all I get is the error message Invalid field name. I get the same error in Access when I try to open the view -- when I try to open the view in enterprise manager -- and I have even copied the sql statement for the view and run it in Query analyzer and still get the same error.

Im completely baffled.

Thanks,
GEM|||As was said, you need to drop and recreate the table link. This is an Access problem, not a SQL Server problem.|||As I said before in my previous post - there is no link to drop. This is a MS Access Project (an ADP) not an ms access database (an .mdb) that has SQL tables and views linked via ODBC. If you are not familar with what an Access Project(ADP) is, then check it out - it's different than just having ODBC linked tables and views.

I get the same error regardless of whether it's through the MS Access Project(ADP), enterprise manager or run the sql through query analyzer....|||Sorry. Did not notice you were using an Access Data Project.

Post the code for the view that is giving you the error.|||Thanks for your imput hmscott but Im fairly new to SQL Server so I really dont know what youre talking about when you say SSMS (or SQL EM)?EM = ENterprise Manager (SQL 2000). SSMS = SQL Server Management Studio (SQL 2005). In case anyone blindsides with another, QA = Query Analyser (SQL 2000) :)|||EM = ENterprise Manager (SQL 2000). SSMS = SQL Server Management Studio (SQL 2005). In case anyone blindsides with another, QA = Query Analyser (SQL 2000) :)

EMP - Elecromagnetic Pulse
BOHICA - Bend Over, Here It Comes Again
OSWATB - Oh, sh##! Where are the backups?

:D

Regards,

hmscott

Changing Field name of an existing Table

Hi,
Is it possible to change any fieldname of an existing table?I mean to say
by TSQL statement.We know that we can alter the data type and width etc.
But I haven't got any info about filedname change.So if it is possible
Please help...
And Is there any TSQL command to alter multiple columns in a single statement?

Please help...
Thanks!!
Joydeepif it is not a primary or a foriegn key you can do something like so...

ALTER TABLE Mytable
ADD INQUIRYID2 BIGINT
GO
UPDATE MyTable SET INQUIRYID2 = INQUIRYID
GO
ALTER TABLE MyTable
DROP COLUMN INQUIRYID

But this a bad idea. What about the existing database object that reference the existing column. Oh well too late. A thousand application buga have just been created. Update your resume. You want biggie size|||if you can connect the db thru EM, just type the new name....|||if you can connect the db thru EM, just type the new name....

The Holy Book says ...

sp_rename

Changes the name of a user-created object (for example, table, column, or user-defineddata type) in the current database.
Syntax

sp_rename [ @.objname = ] 'object_name' ,
[ @.newname = ] 'new_name'
[ , [ @.objtype = ] 'object_type' ]
Arguments

[@.objname =] 'object_name'
Is the current name of the user object (table, view, column, stored procedure, trigger, default, database, object, or rule) or data type. If the object to be renamed is a column in a table, object_name must be in the form table.column. If the object to be renamed is an index, object_name must be in the form table.index. object_name is nvarchar(776), with no default.
[@.newname =] 'new_name'
Is the new name for the specified object. new_name must be a one-part name and must follow the rules for identifiers. newname is sysname, with no default.
[@.objtype =] 'object_type'
Is the type of object being renamed. object_type is varchar(13), with a default of NULL, and can be one of these values.
ValueDescriptionCOLUMNA column to be renamed.DATABASEA user-defined database. This option is required when renaming a database.INDEXA user-defined index.OBJECTAn item of a type tracked in sysobjects. For example, OBJECT could be used to rename objects including constraints (CHECK, FOREIGN KEY, PRIMARY/UNIQUE KEY), user tables, views, stored procedures, triggers, and rules.USERDATATYPEA user-defined data type added by executing sp_addtype.


I don't know why you are providing a round about way of doing the same thing.|||You can try out this one.

EXECUTE sp_rename N'MyTable.MyCurrFName', N'MyNewFName', 'COLUMN'

MyTable has a Field MyCurrFName. It will be renamed to MyNewFName|||You are better off either a). Create a view that has the name you want, or b). unloading, dropping, create, and load

There was a very good article that discusses why do the alter causes sql server to waste a lot of space on the data pages...Now if I can find on Nigel's web site I'll post a link|||Here's the link

http://www.mindsdoor.net/SQLAdmin/AlterTableProblems.html|||thanks Brett, thats really a good link. never thought of it.

Changing field length whilst replicating

Hi
I have two databases that are merged using replication, and I want to change the length of one of the fields. Can anyone think of a way of doing it that doesn't require dropping the whole publication and rebuilding it? Thanks EdYou don't have to drop the entire publication. You have two options, one is to drop the subscription and the article to change the DDL and add the subscription then intialize it. The other is to use sp_repldropcolumn and sp_repladdcolumn to drop the old column and add it back with the length you want without dropping the subscription and the article. Of course, you need to save the data somewhere to add it backup in when the new column got recreated.|||Brilliant, Thanks Ed

Changing field length for Merge

Hello -
I am using Merge replication, pushing subscriptions to clients, and SQL
Server 2000.
I need to change the field size in one of the tables that is in a
publication. Basically, from nvarchar[4] to nvarchar[8].
Can I do this without having to drop/add all subscribers?
I'm hoping that the schema changes can be propagated to clients with a
reinit-subscription.
Thanks.
Regards,
bruce
Please have a look at this article:
http://www.replicationanswers.com/AddColumn.asp. BTW, such changes are much
simpler (just Alter Table...) in SQL Server 2005.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi, I have the same problem than Bruce, I'm using a merge replication but
with a sligtly difference, the field I must change is part of the table's
primary key... I read the Paul Ibison's article but it look fine for fields
doesn't belongs to the primary key, because if I drop the field in order to
made the change on the field, it will throw an duplicate key error. So, what
else can I do?
Thanks in advance
"Paul Ibison" wrote:

> Please have a look at this article:
> http://www.replicationanswers.com/AddColumn.asp. BTW, such changes are much
> simpler (just Alter Table...) in SQL Server 2005.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>