Accessing more than: column

Showing posts with label column. Show all posts

Thursday, March 29, 2012

Adding a subtotal column to a matrix

Can anyone direct me on how to add a subtotal column to a matrix?
I have dates in each column header and I want to add another column that has
"Total" in its header and sums up the other columns.
ThanksRight-click on the column header and check the "Subtotal" option. In order
to access the subtotal formatting properties in the VS properties window,
you have to click on the little green triangle after you added the subtotal.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"M" <M@.discussions.microsoft.com> wrote in message
news:1B939293-22C6-4B2B-A1E9-F6C0548C314B@.microsoft.com...
> Can anyone direct me on how to add a subtotal column to a matrix?
> I have dates in each column header and I want to add another column that
has
> "Total" in its header and sums up the other columns.
> Thankssql

Tuesday, March 27, 2012

Adding a not null column to replicated table

Hi,

I'm merge replicating a SQL Server 2005 database (publisher) to SQL Compact databases (subscribers) on mobile devices. I understood that I could add a "not null" column to a replicated table on the server as long as I specified a default value, but it seems this is not possible. I ran the following script on the server database:

ALTER TABLE Activity ADD ActivityRequiresProject bit not null default(0)

which executed OK. When I went to synchronize the db on the mobile device I got the following error:

Alter table only allows columns to be added which can contain null values. The column cannot be added to the table because it does not allow null values.
The SQL statement failed to execute. If this occurred while using merge replication, this is an internal error. If this occurred while using RDA, then the SQL statement is invalid either on the PULL statement or on the SubmitSQL statement. [ SQL statement = alter table "Activity" add "ActivityRequiresProject" bit not NULL constraint "DF__Activity__Activi__4A47DDAE" default ( ( 0 ) ) ]

Does anyone know if this is a valid error? Is is possible to add a not null column with default, and if not how do I update the schema on a replicated database?

Regards,

Greg

I doubt this is a replication problem, but I don't have a SQL Compact db handy, can you try to create a new table on the compact db and run the TSQL command to see if SQL Compact even allows this?|||

Hi Greg,

Thanks for your reply. I created a new database on the device (not replicated), created a table "Activity", added some records and then executed the same script above in Query Analyzer and it worked fine. It only seems to be a problem when the command is run by the replication agent. Any ideas?

Regards,

Greg

|||can you tell me what version of ce you're using?|||

Hi,

I'm running SQL Server Compact (3.1) on Windows Mobile 5.0 (HP iPaq), and the server is SQL Server 2005 SP2.

Regards,

Greg

|||

Some extra information: I tried to get the subscriber to synchronize by reinitializing all subscriptions (with a new snapshot and "upload unsynchronized changes" set to yes). I had thought that this would regenerate the database at the subscriber after uploading subscriber changes, but it still comes up with the original error message. It's trying to add the not null column but seems to ignore the default constraint.

Any help on this would be greatly appreciated. At this stage I'm not really able to make any db schema changes on the server without deleting subscriber dbs and starting from scratch.

Regards,

Greg

|||We're trying to track down some answers for you, so please be patient.|||

This is a confirmed bug which we're trying to nail the root cause, thanks for raising this issue.

To get around your problem, you should do the following:

1. Reinitialize your subscriptions

2. Generate a new snapshot

3. Rerun your sync agents to apply the new snapshot

If you do this in order, you shouldn't hit the error because the new snapshot should generate a table with the new column. let me know if this still doesn't work.

|||

Thanks Greg,

As per my previous post, the main issue is that this problem is not resolved by reinitializing subscriptions with a new snapshot. I still get the error. The only solution I've found so far is to delete the subscriber database and synchronize again.

Regards,

Greg

|||

Hi,

This problem was seriously affecting the development and testing of this application, so I opened a support incident with Microsoft. The tech advised that I should use the stored procedure sp_repladdcolumn to add the column. It worked! Despite the documentation saying that this method is deprecated and should only be used with SQL Server 2000, it seems that this is only way to add a not null column with a default value to a SQL Server 2005 db table and have it successfully replicate to SQL Mobile/Compact (if anyone has any alternatives to this, please advise). This also seems to be a solution to another problem I've had with "could not enumerate changes at the subscriber" error when synchronizing after changing the db schema. Using the old stored procedures instead of "alter table" commands seems to fix this.

I hope MS will add a KB article about this so others won't have spend as much time as I have trying to get an answer.

Regards,

Greg

|||It's a bug in CE, not replication. Earlier I asked if you could run the alter table statement on your CE device to see if that failed, you said it passed but I think you ran it on your SQL Server 2005 server and not the CE. Running the alter table statement explicitly on the CE device will fail, if you remove the default name, then it works - CE should not choke like this. I'm glad you found a solution, a bug was already file, CE folks will be fixing this hopefully soon.

Adding a new VarBinaryMax column to an existing table using SMO.

Consider the following code.

Table applicationSettingsTable = database.Tables["application_settings"];

Column logoColumn = new Column(applicationSettingsTable, "logo", DataType.VarBinaryMax);

applicationSettingsTable.Columns.Add(logoColumn);

applicationSettingsTable.Alter();

I don't understand why - but when I execute this code I end up with a column that has the data type varbinary(1) in the database rather than varbinary(MAX). I can't see anything in the msdn documentation that tells me I need to do something special for varbinary(max) columns, but maybe I do?

Any help much appreciated.

This appears to work tho...

Column logoColumn = new Column(applicationSettingsTable, "logo", DataType.VarBinary(-1));

adding a new line

Hello,

I have a column in my table that is ntext data type and it has html data. I need to strip all the html tags, which is pretty easy to do, but I also need to add a new line for every instance of "

". Do any of you know of a function or anything else that does that?

11/21/06 - Position closed and filled by competition.

11/3/2006 ADDITIONAL RESUMES NEEDED. HEAVY UNIX/LINUX Update 9/15/06 - Manager is still looking for qualified candidates for this position. Please respond NEW POSITION FOR ETS Location: Princeton, NJ Duration: 6 months New Max Rate: $55.00 Recruiter: Steve McDowell

If you are using SQL Server 2005 then convert the column to nvarchar(max). You can then use any of the built-in string functions to replace the values. However, these are operations that is not meant for SQL Server. It is more appropriate to clean the data on the client side before saving it to the database. If you are using SQL Server 2000 then you can use PATINDEX to search for string

and replace it. Below are some scripts that will help.

http://www.umachandar.com/technical/SQL6x70Scripts/Main62.htm -- strips html tags from varchar field. You have to modify it a bit to use it with ntext

http://www.umachandar.com/technical/SQL6x70Scripts/Main53.htm -- shows how to use updatetext

|||

Hello Uma,

First of, Thanks for your help!

I went through the code and I still dont understand how the code will add a new line for

or <n></n> instances. When you look at the data using IE you will see the columns appears like this:

11/21/06 - Position closed and filled by competition.

11/3/2006 ADDITIONAL RESUMES NEEDED. HEAVY UNIX/LINUX Update 9/15/06 - Manager is still looking for qualified candidates for this position.

But in my table you see the column appears with all the html tags and comes out like this:

11/21/06 - Position closed and filled by competition.

11/3/2006 ADDITIONAL RESUMES NEEDED. HEAVY UNIX/LINUX Update 9/15/06 - Manager is still looking for qualified candidates for this position.

I need to clean up the data and make it appear as simple text in my table without all the html tags but when that column (the new col) is view using IE, it needs to still show the new line for every

or <n></n>.

Can you help? Please let me know if you have any questions.

adding a new key; planning for millions of records

Hello all,
i'm going to be adding a new column (1 byte) that will become a non-unique
indexed value into a table that i'm adding records into. i know for a fact
that this table will grow from the current thousands to millions of records
over the span of a few w

s and have allocated the database file
accordingly.
since i already have the primary key as clustered is there something i can
do so that adding this key as part of insert operations will be done
efficiently? I'm adding tens of thousands of records a day. In a previous
incarnation of this database i had had problems with having to rebuild the
keys occasionally or insert operations would timeout. I know that part of
the problem was fragmentation of the database, but i'd like to give it a
'heads up' to prepare for a large B-tree for this key.
is there a knob that will control this when i create the index?
thanks,
johnJohn,
I'm assuming you're refering to index fragmentation for the 1 byte index.
Might try creating the index with a lower FILLFACTOR to help minimize
fragmentation.
HTH
Jerry
"John Mott" <johnmott59@.hotmail.com> wrote in message
news:usOEf6$0FHA.1132@.TK2MSFTNGP10.phx.gbl...
> Hello all,
> i'm going to be adding a new column (1 byte) that will become a non-unique
> indexed value into a table that i'm adding records into. i know for a fact
> that this table will grow from the current thousands to millions of
> records
> over the span of a few w

s and have allocated the database file
> accordingly.
> since i already have the primary key as clustered is there something i can
> do so that adding this key as part of insert operations will be done
> efficiently? I'm adding tens of thousands of records a day. In a previous
> incarnation of this database i had had problems with having to rebuild the
> keys occasionally or insert operations would timeout. I know that part of
> the problem was fragmentation of the database, but i'd like to give it a
> 'heads up' to prepare for a large B-tree for this key.
> is there a knob that will control this when i create the index?
> thanks,
> john
>sql

adding a new data column (not derived) midway thru a data flow

I need to know what a table's max row Identity is part way thru a data flow. I can't get it at the beginning of the data flow. I need to either (1) add it to the data buffer part way thru or (2) set it into a package variable and then reference the var in a script component.

I've not found a way to add a database column to the data buffer without doing a lookup for each row (too slow and not appropriate here) or some goofy oledb source and then merge join into the data buffer on a contrived join.

I've read questions about referencing package vars in scripts but I can't get that to work. DTS.Variables("varname").Value isn't recognised when I code it up.

Anyone have an idea or solution for either one of these? If you're gonna explain the script code, please include the entire snipet including the INCLUDEs, etc.

While I am not convinced that the MergeJoin is contrived or the oledb source is goofy you should check to see that the variable varname is in the ReadOnlyVariables property of the script component. If it isn't then add it and then the DTS.Variables("varname").Value should work. If it is then can you provide the error you are receiving.

Thanks,

Matt

|||

Matt,

I wasn't saying that the MergeJoin is contrived or that the OLEDB source is goofy, I was saying that the way I'd be using them to solve my dilema is. I have used both of these controls and am happy with them, but they aren't what I'd like to use to solve this problem.

Regarding the scripting problem, I have put varname in the ReadOnlyVariables property. In the script window when I enter DTS.Variables("varname").Value the interpreter places the squigly underline under DTS and says that "Name 'DTS' is not declared". There is no intelisense when I type the period between DTS & Variables. So I'm thinking that something needs to be declared. Here's my code:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
Inherits UserComponent
Dim counter As Integer
Dim FirstTime As Boolean

Public Sub New() 'This method gets called only once per execution
FirstTime = True
End Sub

Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)
If FirstTime Then
counter = Row.MaxRowId
FirstTime = False
End If
counter = dts.variables("dataset").value 'dts not recognized
Row.SubCntrctId = counter
End Sub
End Class

|||

Gordon,

The syntax for accessing variables in the script component is different from in the script task. The syntax is:

Me.Variables.<var-name>|||Somebody would want to tell the boys in Wrox about this... their

chapter on scripting is very light on the differences between Task and

Component...

They only mention variables in relation to the script task... so you

(ok, well.. I, at least . ) end up supposing that, since no one

mentioned otherwise, the variable access must be the same in a

script component... sigh...

But, thankfully, we have good ole Jamie to set us straight...

Thanks Jamie,

PJ|||

Gordon, I know what you mean -- I'm facing the same problem in SSIS Designer. Usually I work around it in the data source, doing an explicit join right off the bat to get the extra column with the needed value. But now I have a case where the data source is a flat file but the value needed for the extra column is still determined by a SQL select statement (and still independent of the rows in the primary data source).

So I've got a multi-row, multi-column data stream X and a single-row, single-column data stream Y. There should be a simple way in SSIS Designer to (outer) join them (i.e. with no condition):

select X.x1, X.x2, Y.y1 from X, Y

But all the Merge/Join/Lookup etc. data flow transformations require (as you said) join columns to be identified.

This could be done with the Derived Column transformation, except that its expression cannot be a SQL statement and it doesn't need to be evaluated it for every row of X anyway. Which leads me to a similar conclusion as you: that since a variable could be used in the expression, the result of the query should be saved in a variable. But in SSIS Designer, a variable (in this case, scoped to the data flow task) cannot be specified as an SQL statement.

Help! What is the best practice in this situation?

|||

Kevin,

Your request is basically to be able to do an OUTER JOIN. Is that correct?

I have lamented the lack of an OUTER JOIN component in the past and have requested one for future versions. Unfortunately I did that during beta and that request hasn't made it through to Microsoft Connect. Can I suggest that you go to Microsoft Connect and put in a request for it and then put a link to it up here so that I can vote for it.

If you dont ask then you won't get!

The only way to accomplish what you need in ths instance is to use a script component.

-Jamie

|||

Hello all

There are two ways I could find to tackle this problem.

1) This is very slow and consumes a lot of memory

create a dummy derived column in both tables you want to join and insert a dummy 1(you may need to sort on this column which is the real performance killer). Then use merge join on this field so resulting new table contains the new column coming from the other rowset.

2) Split the DataFlow task into several pieces. Populate a variable (I am assuming the value you want to add as the new column will come from a rowset of count 1) with the first pass. Then use a derived column to insert the new value using this variable.

I am not sure if these are the recommended ways to do it but they worked for me.

Batu

adding a new data column (not derived) midway thru a data flow

I've read questions about referencing package vars in scripts but I can't get that to work. DTS.Variables("varname").Value isn't recognised when I code it up.

Anyone have an idea or solution for either one of these? If you're gonna explain the script code, please include the entire snipet including the INCLUDEs, etc.

Thanks,

Matt

|||

Matt,

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
Inherits UserComponent
Dim counter As Integer
Dim FirstTime As Boolean

Public Sub New() 'This method gets called only once per execution
FirstTime = True
End Sub

|||

Gordon,

The syntax for accessing variables in the script component is different from in the script task. The syntax is:

Me.Variables.<var-name>|||Somebody would want to tell the boys in Wrox about this... their chapter on scripting is very light on the differences between Task and Component...
They only mention variables in relation to the script task... so you (ok, well.. I, at least . ) end up supposing that, since no one mentioned otherwise, the variable access must be the same in a script component... sigh...
But, thankfully, we have good ole Jamie to set us straight...
Thanks Jamie,
PJ
|||

So I've got a multi-row, multi-column data stream X and a single-row, single-column data stream Y. There should be a simple way in SSIS Designer to (outer) join them (i.e. with no condition):

select X.x1, X.x2, Y.y1 from X, Y

But all the Merge/Join/Lookup etc. data flow transformations require (as you said) join columns to be identified.

Help! What is the best practice in this situation?

|||

Kevin,

Your request is basically to be able to do an OUTER JOIN. Is that correct?

If you dont ask then you won't get!

The only way to accomplish what you need in ths instance is to use a script component.

-Jamie

|||

Hello all

There are two ways I could find to tackle this problem.

1) This is very slow and consumes a lot of memory

I am not sure if these are the recommended ways to do it but they worked for me.

Batu

adding a new data column (not derived) midway thru a data flow

I've read questions about referencing package vars in scripts but I can't get that to work. DTS.Variables("varname").Value isn't recognised when I code it up.

Anyone have an idea or solution for either one of these? If you're gonna explain the script code, please include the entire snipet including the INCLUDEs, etc.

Thanks,

Matt

|||

Matt,

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
Inherits UserComponent
Dim counter As Integer
Dim FirstTime As Boolean

Public Sub New() 'This method gets called only once per execution
FirstTime = True
End Sub

|||

Gordon,

The syntax for accessing variables in the script component is different from in the script task. The syntax is:

Me.Variables.<var-name>|||Somebody would want to tell the boys in Wrox about this... their

chapter on scripting is very light on the differences between Task and

Component...

They only mention variables in relation to the script task... so you

(ok, well.. I, at least . ) end up supposing that, since no one

mentioned otherwise, the variable access must be the same in a

script component... sigh...

But, thankfully, we have good ole Jamie to set us straight...

Thanks Jamie,

PJ|||

So I've got a multi-row, multi-column data stream X and a single-row, single-column data stream Y. There should be a simple way in SSIS Designer to (outer) join them (i.e. with no condition):

select X.x1, X.x2, Y.y1 from X, Y

But all the Merge/Join/Lookup etc. data flow transformations require (as you said) join columns to be identified.

Help! What is the best practice in this situation?

|||

Kevin,

Your request is basically to be able to do an OUTER JOIN. Is that correct?

If you dont ask then you won't get!

The only way to accomplish what you need in ths instance is to use a script component.

-Jamie

|||

Hello all

There are two ways I could find to tackle this problem.

1) This is very slow and consumes a lot of memory

I am not sure if these are the recommended ways to do it but they worked for me.

Batu

sql

adding a new column to a table involved in merge replication

Hi everyone,
I would like to know if there is some way to add a new column to an
existing table in a database published via merge replication that does
not require reinitialization of the articles.
Thanks.
Aramid
Aramid,
have a look at sp_repladdcolumn in BOL. This can also be done through the
GUI in Enterprise manager. In SQL Server 2005 the same effect is achieved
directly using Alter Table statements (provided the subscriber is also SQL
2005).
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

adding a new column to a large table

SQL Server 2000
Windows 2000 Server
I need to add a new column to a table that will likely have millions of rows
at most customer sites. The table currently has one FK and 2 unclustered
indexes. The new column is an int column. When I run the script to add the
column on our test server (table has about 5 million rows) it takes about
1/2 hour. Unfortunately, we can't have that kind of down time on that
table.
Is there a typical method for doing this more efficiently? Would it help to
drop the indexes before and recreate them after or would that end up taking
the same amount of time anyway? What about creating a temp table, copying
that data into the new table and renaming it?Are you trying to add this column in the middle of the other columns, or at
the end of the other columns? If the middle, try adding to the end and see
if that sppeds things up, Im betting thats whats going on.
"Joel Lyons" <NOSPAMJOELL@.NOVARAD.NET> wrote in message
news:uyRcotfZFHA.1456@.TK2MSFTNGP15.phx.gbl...
> SQL Server 2000
> Windows 2000 Server
> I need to add a new column to a table that will likely have millions of
> rows at most customer sites. The table currently has one FK and 2
> unclustered indexes. The new column is an int column. When I run the
> script to add the column on our test server (table has about 5 million
> rows) it takes about 1/2 hour. Unfortunately, we can't have that kind of
> down time on that table.
> Is there a typical method for doing this more efficiently? Would it help
> to drop the indexes before and recreate them after or would that end up
> taking the same amount of time anyway? What about creating a temp table,
> copying that data into the new table and renaming it?
>|||Here's the script:
ALTER TABLE [dbo].[Images] ADD Version tinyint NOT NULL CONSTRAINT
DF_Images_Version DEFAULT 1
I'd guess that adds to the end, no?
"ChrisR" <noemail@.bla.com> wrote in message
news:%23caxC6fZFHA.3364@.TK2MSFTNGP12.phx.gbl...
> Are you trying to add this column in the middle of the other columns, or
> at the end of the other columns? If the middle, try adding to the end and
> see if that sppeds things up, Im betting thats whats going on.
>
> "Joel Lyons" <NOSPAMJOELL@.NOVARAD.NET> wrote in message
> news:uyRcotfZFHA.1456@.TK2MSFTNGP15.phx.gbl...
>> SQL Server 2000
>> Windows 2000 Server
>> I need to add a new column to a table that will likely have millions of
>> rows at most customer sites. The table currently has one FK and 2
>> unclustered indexes. The new column is an int column. When I run the
>> script to add the column on our test server (table has about 5 million
>> rows) it takes about 1/2 hour. Unfortunately, we can't have that kind of
>> down time on that table.
>> Is there a typical method for doing this more efficiently? Would it help
>> to drop the indexes before and recreate them after or would that end up
>> taking the same amount of time anyway? What about creating a temp table,
>> copying that data into the new table and renaming it?
>>
>|||Yes it does. I just realized you are adding these to customer sires. Are you
logging onto the boxes locally, or over the WAN?
"Joel Lyons" <NOSPAMJOELL@.NOVARAD.NET> wrote in message
news:utoFrFgZFHA.3876@.TK2MSFTNGP12.phx.gbl...
> Here's the script:
> ALTER TABLE [dbo].[Images] ADD Version tinyint NOT NULL CONSTRAINT
> DF_Images_Version DEFAULT 1
> I'd guess that adds to the end, no?
> "ChrisR" <noemail@.bla.com> wrote in message
> news:%23caxC6fZFHA.3364@.TK2MSFTNGP12.phx.gbl...
>> Are you trying to add this column in the middle of the other columns, or
>> at the end of the other columns? If the middle, try adding to the end and
>> see if that sppeds things up, Im betting thats whats going on.
>>
>> "Joel Lyons" <NOSPAMJOELL@.NOVARAD.NET> wrote in message
>> news:uyRcotfZFHA.1456@.TK2MSFTNGP15.phx.gbl...
>> SQL Server 2000
>> Windows 2000 Server
>> I need to add a new column to a table that will likely have millions of
>> rows at most customer sites. The table currently has one FK and 2
>> unclustered indexes. The new column is an int column. When I run the
>> script to add the column on our test server (table has about 5 million
>> rows) it takes about 1/2 hour. Unfortunately, we can't have that kind
>> of down time on that table.
>> Is there a typical method for doing this more efficiently? Would it
>> help to drop the indexes before and recreate them after or would that
>> end up taking the same amount of time anyway? What about creating a
>> temp table, copying that data into the new table and renaming it?
>>
>>
>|||We haven't run the script on an actual customer site yet, only on our test
server which is a close resemblance. When we upgrade the customers, we will
likely remote in and run the script on the actual server.
(thanks for taking the time to help)
"ChrisR" <noemail@.bla.com> wrote in message
news:uOYoAMgZFHA.580@.TK2MSFTNGP15.phx.gbl...
> Yes it does. I just realized you are adding these to customer sires. Are
> you logging onto the boxes locally, or over the WAN?
>
> "Joel Lyons" <NOSPAMJOELL@.NOVARAD.NET> wrote in message
> news:utoFrFgZFHA.3876@.TK2MSFTNGP12.phx.gbl...
>> Here's the script:
>> ALTER TABLE [dbo].[Images] ADD Version tinyint NOT NULL CONSTRAINT
>> DF_Images_Version DEFAULT 1
>> I'd guess that adds to the end, no?
>> "ChrisR" <noemail@.bla.com> wrote in message
>> news:%23caxC6fZFHA.3364@.TK2MSFTNGP12.phx.gbl...
>> Are you trying to add this column in the middle of the other columns, or
>> at the end of the other columns? If the middle, try adding to the end
>> and see if that sppeds things up, Im betting thats whats going on.
>>
>> "Joel Lyons" <NOSPAMJOELL@.NOVARAD.NET> wrote in message
>> news:uyRcotfZFHA.1456@.TK2MSFTNGP15.phx.gbl...
>> SQL Server 2000
>> Windows 2000 Server
>> I need to add a new column to a table that will likely have millions of
>> rows at most customer sites. The table currently has one FK and 2
>> unclustered indexes. The new column is an int column. When I run the
>> script to add the column on our test server (table has about 5 million
>> rows) it takes about 1/2 hour. Unfortunately, we can't have that kind
>> of down time on that table.
>> Is there a typical method for doing this more efficiently? Would it
>> help to drop the indexes before and recreate them after or would that
>> end up taking the same amount of time anyway? What about creating a
>> temp table, copying that data into the new table and renaming it?
>>
>>
>>
>|||I just realized...what about the default value? It has to apply that
default value to all the existing rows. Maybe that's what's taking a long
time. I wonder if disabling some level of logging during this update would
speed that up.
"ChrisR" <noemail@.bla.com> wrote in message
news:uOYoAMgZFHA.580@.TK2MSFTNGP15.phx.gbl...
> Yes it does. I just realized you are adding these to customer sires. Are
> you logging onto the boxes locally, or over the WAN?
>
> "Joel Lyons" <NOSPAMJOELL@.NOVARAD.NET> wrote in message
> news:utoFrFgZFHA.3876@.TK2MSFTNGP12.phx.gbl...
>> Here's the script:
>> ALTER TABLE [dbo].[Images] ADD Version tinyint NOT NULL CONSTRAINT
>> DF_Images_Version DEFAULT 1
>> I'd guess that adds to the end, no?
>> "ChrisR" <noemail@.bla.com> wrote in message
>> news:%23caxC6fZFHA.3364@.TK2MSFTNGP12.phx.gbl...
>> Are you trying to add this column in the middle of the other columns, or
>> at the end of the other columns? If the middle, try adding to the end
>> and see if that sppeds things up, Im betting thats whats going on.
>>
>> "Joel Lyons" <NOSPAMJOELL@.NOVARAD.NET> wrote in message
>> news:uyRcotfZFHA.1456@.TK2MSFTNGP15.phx.gbl...
>> SQL Server 2000
>> Windows 2000 Server
>> I need to add a new column to a table that will likely have millions of
>> rows at most customer sites. The table currently has one FK and 2
>> unclustered indexes. The new column is an int column. When I run the
>> script to add the column on our test server (table has about 5 million
>> rows) it takes about 1/2 hour. Unfortunately, we can't have that kind
>> of down time on that table.
>> Is there a typical method for doing this more efficiently? Would it
>> help to drop the indexes before and recreate them after or would that
>> end up taking the same amount of time anyway? What about creating a
>> temp table, copying that data into the new table and renaming it?
>>
>>
>>
>|||Yes, the defualt value will take a bit. But it doesnt seems to me like Ive
ever experienced that severe of slowness, which is what led to my other
questions. Creating with NULL will be much faster of course... but still
thats a long time. I guess try just to add with NULL for timing tests.
"Joel Lyons" <NOSPAMJOELL@.NOVARAD.NET> wrote in message
news:eLFnFbhZFHA.2520@.TK2MSFTNGP09.phx.gbl...
>I just realized...what about the default value? It has to apply that
>default value to all the existing rows. Maybe that's what's taking a long
>time. I wonder if disabling some level of logging during this update would
>speed that up.
> "ChrisR" <noemail@.bla.com> wrote in message
> news:uOYoAMgZFHA.580@.TK2MSFTNGP15.phx.gbl...
>> Yes it does. I just realized you are adding these to customer sires. Are
>> you logging onto the boxes locally, or over the WAN?
>>
>> "Joel Lyons" <NOSPAMJOELL@.NOVARAD.NET> wrote in message
>> news:utoFrFgZFHA.3876@.TK2MSFTNGP12.phx.gbl...
>> Here's the script:
>> ALTER TABLE [dbo].[Images] ADD Version tinyint NOT NULL CONSTRAINT
>> DF_Images_Version DEFAULT 1
>> I'd guess that adds to the end, no?
>> "ChrisR" <noemail@.bla.com> wrote in message
>> news:%23caxC6fZFHA.3364@.TK2MSFTNGP12.phx.gbl...
>> Are you trying to add this column in the middle of the other columns,
>> or at the end of the other columns? If the middle, try adding to the
>> end and see if that sppeds things up, Im betting thats whats going on.
>>
>> "Joel Lyons" <NOSPAMJOELL@.NOVARAD.NET> wrote in message
>> news:uyRcotfZFHA.1456@.TK2MSFTNGP15.phx.gbl...
>> SQL Server 2000
>> Windows 2000 Server
>> I need to add a new column to a table that will likely have millions
>> of rows at most customer sites. The table currently has one FK and 2
>> unclustered indexes. The new column is an int column. When I run the
>> script to add the column on our test server (table has about 5 million
>> rows) it takes about 1/2 hour. Unfortunately, we can't have that kind
>> of down time on that table.
>> Is there a typical method for doing this more efficiently? Would it
>> help to drop the indexes before and recreate them after or would that
>> end up taking the same amount of time anyway? What about creating a
>> temp table, copying that data into the new table and renaming it?
>>
>>
>>
>>
>|||On Tue, 31 May 2005 10:50:16 -0600, Joel Lyons wrote:
>Here's the script:
>ALTER TABLE [dbo].[Images] ADD Version tinyint NOT NULL CONSTRAINT
>DF_Images_Version DEFAULT 1
Hi Joel,
You're right (in your other post) - adding the default is what takes
time.
Consider breaking it in smaller steps:
1. Add a column, keep it nullable - should be fast.
ALTER TABLE dbo.Images
ADD Version tinyint
go
2. Fill the default value. This will take time, but if you use smallish
batches to process the data, locks will be released in between, so your
other applications will be able to proceed. If you have full recovery
model, then make sure that you back up the transaction log in between
(unless you don't mind it growing to accomodate all the changes).
SET ROWCOUNT 50000 -- experiment to get the optimal setting
DECLARE @.rc int
SET @.rc = 1
WHILE @.rc > 0
BEGIN
UPDATE dbo.Images
SET Version = 1
WHERE Version IS NULL
SET @.rc = @.@.ROWCOUNT
-- BACKUP LOG MyDatabase TO MyDatabaseLog
END
SET ROWCOUNT 0 -- never forget to reset ROWCOUNT!!
3. When all rows have been filled, set the NOT NULL constraint and the
DEFAULT:
ALTER TABLE dbo.Images
ALTER COLUMN Version tinyint NOT NULL
ALTER TABLE dbo.Images
ADD DEFAULT 1 FOR Version
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||When you allow nulls and do not provide a default value, it will be very
very quick, because the new rows are not materialized until the page is
re-written... It works sort of like a virtual column... when you provide a
default value, the values must ACTUALLY be written, so the time it would
take is relative to the IO speed.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Joel Lyons" <NOSPAMJOELL@.NOVARAD.NET> wrote in message
news:uyRcotfZFHA.1456@.TK2MSFTNGP15.phx.gbl...
> SQL Server 2000
> Windows 2000 Server
> I need to add a new column to a table that will likely have millions of
> rows at most customer sites. The table currently has one FK and 2
> unclustered indexes. The new column is an int column. When I run the
> script to add the column on our test server (table has about 5 million
> rows) it takes about 1/2 hour. Unfortunately, we can't have that kind of
> down time on that table.
> Is there a typical method for doing this more efficiently? Would it help
> to drop the indexes before and recreate them after or would that end up
> taking the same amount of time anyway? What about creating a temp table,
> copying that data into the new table and renaming it?
>|||Very interesting info - thanks all!
I'll try some of these ideas and let you know.
-Joel
"Joel Lyons" <NOSPAMJOELL@.NOVARAD.NET> wrote in message
news:uyRcotfZFHA.1456@.TK2MSFTNGP15.phx.gbl...
> SQL Server 2000
> Windows 2000 Server
> I need to add a new column to a table that will likely have millions of
> rows at most customer sites. The table currently has one FK and 2
> unclustered indexes. The new column is an int column. When I run the
> script to add the column on our test server (table has about 5 million
> rows) it takes about 1/2 hour. Unfortunately, we can't have that kind of
> down time on that table.
> Is there a typical method for doing this more efficiently? Would it help
> to drop the indexes before and recreate them after or would that end up
> taking the same amount of time anyway? What about creating a temp table,
> copying that data into the new table and renaming it?
>|||Joel,
Are you adding the new column executing a script in QA or doing it manually
in EM?
Do it in QA, using "alter table".
AMB
"Joel Lyons" wrote:
> SQL Server 2000
> Windows 2000 Server
> I need to add a new column to a table that will likely have millions of rows
> at most customer sites. The table currently has one FK and 2 unclustered
> indexes. The new column is an int column. When I run the script to add the
> column on our test server (table has about 5 million rows) it takes about
> 1/2 hour. Unfortunately, we can't have that kind of down time on that
> table.
> Is there a typical method for doing this more efficiently? Would it help to
> drop the indexes before and recreate them after or would that end up taking
> the same amount of time anyway? What about creating a temp table, copying
> that data into the new table and renaming it?
>
>|||Could you please show the script you use to add a column ?
Regards.
"Joel Lyons" wrote:
> SQL Server 2000
> Windows 2000 Server
> I need to add a new column to a table that will likely have millions of rows
> at most customer sites. The table currently has one FK and 2 unclustered
> indexes. The new column is an int column. When I run the script to add the
> column on our test server (table has about 5 million rows) it takes about
> 1/2 hour. Unfortunately, we can't have that kind of down time on that
> table.
> Is there a typical method for doing this more efficiently? Would it help to
> drop the indexes before and recreate them after or would that end up taking
> the same amount of time anyway? What about creating a temp table, copying
> that data into the new table and renaming it?
>
>|||Hi Joel,
DEFAULT constraint in your script is not an issue,
default value will only apply on new records, not existent records in your
table.
The real issue is NOT NULL - this will require prety huge I/O.
What you can do, run your script to add a new column without NOT NULL.
This will let you use a new column and populate it.
Then when you will able to schedule a time, make it NOT NULL.
Regards.
"Joel Lyons" wrote:
> SQL Server 2000
> Windows 2000 Server
> I need to add a new column to a table that will likely have millions of rows
> at most customer sites. The table currently has one FK and 2 unclustered
> indexes. The new column is an int column. When I run the script to add the
> column on our test server (table has about 5 million rows) it takes about
> 1/2 hour. Unfortunately, we can't have that kind of down time on that
> table.
> Is there a typical method for doing this more efficiently? Would it help to
> drop the indexes before and recreate them after or would that end up taking
> the same amount of time anyway? What about creating a temp table, copying
> that data into the new table and renaming it?
>
>|||Hi Joel,
DEFAULT constraint in your script is not an issue,
default value will only apply on new records, not existent records in your
table.
The real issue is NOT NULL - this will require prety huge I/O.
What you can do, run your script to add a new column without NOT NULL.
This will let you use a new column and populate it.
Then when you will able to schedule a time, make it NOT NULL.
Regards.
"Joel Lyons" wrote:
> SQL Server 2000
> Windows 2000 Server
> I need to add a new column to a table that will likely have millions of rows
> at most customer sites. The table currently has one FK and 2 unclustered
> indexes. The new column is an int column. When I run the script to add the
> column on our test server (table has about 5 million rows) it takes about
> 1/2 hour. Unfortunately, we can't have that kind of down time on that
> table.
> Is there a typical method for doing this more efficiently? Would it help to
> drop the indexes before and recreate them after or would that end up taking
> the same amount of time anyway? What about creating a temp table, copying
> that data into the new table and renaming it?
>
>

Adding a new column of a UDT type fails on "invalid data type"

Hi all,

A strange thing happened to me a few days ago - I tried to modify a table, using the SQL server 2005 management studio, in order to add a new column to an existing table. I've entered the column's name and selected one of my UDTs for the column type. When I pressed "enter", I got the following message "invalid data type" !

Needless to say that shouldn't have happen, didn't happen it the previous version (CTP) and for some reason it works on other computers having the same version !

BTW, the database is sql server 2000.

Any ideas what this works on other computers but not on mine ?

Should I re-install the SQL server ? or the framework ?

Thanks,

Ido.

I'm still getting the error - currently on 2 out of 3 computers.

Any ideas ?

Ido.

sql

Adding a new column is DBNULL

Hello,

VS 2005

I am developing the database application. This is a live database and is being used by the customer. I am to release a new version and I had to add new columns to fit the requirements DateStarted (DateTime), and TotalHours(Int) into a database table.There are already over a 1000 rows in this table.

When the customer wants to look at a record in this table and insert the value into the text boxes (Front-end), when it gets to the DateStarted or TotalHours it comes up with a error message:"

The value for column 'DateStarted' in table 'IncidentTask' is DBNull."The method for inserting is:

Code Snippet

Me.dtDateStarted.Value = Me.DsIncidentsControl.IncidentTask(0).DateStarted

What are the possible solutions to this problem? Would it mean checking for a DBNULL before displaying in the textboxes? Or updating the new column rows with an date:

Code Snippet

UPDATE IncidentTask SET DateStarted = '1/1/2005' WHERE (DateStarted IS NULL)

Many thanks for any suggestions,

Steve

You can either have the application check for null values and display an empty string when dbnull,

OR,

UPDATE the table and put a 'dummy' date in all null rows.

Adding a new column in a certain position....

Hi,
I am writing some updates scripts for a database and need to add a new
column at a specific position in the table. I am using the 'ALTER TABLE'
command but this just tags it on the end. Is there any way of doing (via
TSQL) this without rebuilding the table?
Thanks in advance,
Stuno, there isn't. but there isn't a good reason for doing this in the first
place - at least not the one i know of. why is the order of columns in base
table important to you?
dean
"Stu" <s.lock@.cergis.com> wrote in message
news:uL%230ep9KGHA.3836@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am writing some updates scripts for a database and need to add a new
> column at a specific position in the table. I am using the 'ALTER TABLE'
> command but this just tags it on the end. Is there any way of doing (via
> TSQL) this without rebuilding the table?
> Thanks in advance,
> Stu
>
>|||Stu skrev:

> Hi,
> I am writing some updates scripts for a database and need to add a new
> column at a specific position in the table. I am using the 'ALTER TABLE'
> command but this just tags it on the end. Is there any way of doing (via
> TSQL) this without rebuilding the table?
> Thanks in advance,
> Stu
This is a common question, and the anwer is 'no' :)
Search for it on Google for example, then you'll learn that you should
not really care about in which order the columns are in a table.
/impslayer, aka Birger Johansson|||Stu,
There is no good reason (that I have come across) to need the columns in any
particular order (except, perhaps, for clarity at some stage), but
operationally, they should make no difference whatsoever.
As a side point, they may make a difference should you use the SELECT * FROM
tblMyTable, but then again, that is not good practise to do, I always state
my Selected column names, for both clarity and to ensure that should a schem
a
change occur in the underlaying table (e.g. a column added) then the same,
correct, columns are returned each time.
Hope this assists,
Tony
"Stu" wrote:

> Hi,
> I am writing some updates scripts for a database and need to add a new
> column at a specific position in the table. I am using the 'ALTER TABLE'
> command but this just tags it on the end. Is there any way of doing (via
> TSQL) this without rebuilding the table?
> Thanks in advance,
> Stu
>
>|||http://www.aspfaq.com/2528
"Stu" <s.lock@.cergis.com> wrote in message
news:uL%230ep9KGHA.3836@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am writing some updates scripts for a database and need to add a new
> column at a specific position in the table. I am using the 'ALTER TABLE'
> command but this just tags it on the end. Is there any way of doing (via
> TSQL) this without rebuilding the table?
> Thanks in advance,
> Stu
>
>

Adding a new Column after a particular column

Hi,
I have a table, and i have say 3 columns, say c1,c2,c3 . Now i want to insert one more column say c2a after the column c2.How to do this, i tried with all possible ways, i cldn do, in mysql, the "AFTER" clause is ther, so i did with that, but in
MS Sql , i cdln make it...
Help me..\

ThanksStrange request, but if you absolutely have to put your new column between 2 others, then you will have to:

1. make a back up of your table
2. select * into a temp table
3. drop your table
4. create new table with cols in order you want
5. select * from temp table into your new table
6. drop temp table

happy SQLing

adding a new column

Dear all,
What would be the fastest way to add a new column (a timestamp column
to be exact) to a table with about 15M records?
We actually tried doing that earlier, but it was already running for 6
hours and the script has not yet finished executing. We had to kill
the transaction as it was already severely prolonging the downtime.
The rollback process, of course, incurred almost 4.5 hours in itself.
:(
Since we will be doing this again (as we need the timestamp table),
any tips would be appreciated.
Thanks.
AramidHow did you do it the first time? I assume you used Enterprise Manager
which is a real no-no when it comes to modifying tables. The alter table
command from Query analyzer would have been much better. It's hard to say
how long it should take without knowing a lot more of your hardware and
configurations. Another option is to BCP out all the data from that table.
Truncate it. Set the recovery mode to Simple. Alter the table to add the
column. Then BCP it back in. You may need a format file to BCP back in
since the structure will change. In any case make sure you do a full backup
first and set the recovery mode back to what it was if changed.
--
Andrew J. Kelly SQL MVP
"Aramid" <aramid@.hotmail.com> wrote in message
news:vod5m11210l0l0v8568nik7nvlfsjf9gno@.4ax.com...
> Dear all,
> What would be the fastest way to add a new column (a timestamp column
> to be exact) to a table with about 15M records?
> We actually tried doing that earlier, but it was already running for 6
> hours and the script has not yet finished executing. We had to kill
> the transaction as it was already severely prolonging the downtime.
> The rollback process, of course, incurred almost 4.5 hours in itself.
> :(
> Since we will be doing this again (as we need the timestamp table),
> any tips would be appreciated.
> Thanks.
> Aramid|||On Fri, 28 Oct 2005 20:58:36 -0400, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:
>How did you do it the first time? I assume you used Enterprise Manager
>which is a real no-no when it comes to modifying tables. The alter table
>command from Query analyzer would have been much better. It's hard to say
>how long it should take without knowing a lot more of your hardware and
>configurations. Another option is to BCP out all the data from that table.
>Truncate it. Set the recovery mode to Simple. Alter the table to add the
>column. Then BCP it back in. You may need a format file to BCP back in
>since the structure will change. In any case make sure you do a full backup
>first and set the recovery mode back to what it was if changed.
Hi Andrew,
I actually used the Alter Table command via QA. The database is
running on a virtual instance of a SQL cluster. The machine is a
dual-Xeon 2.8Ghz, and the shared array is a RAID-5 of 3 x 76Gb U320
drives. Based on perfmon output, the bottleneck is disk IO, and in
fact, the CPU stayed almost less than 10% the whole time.
Actually, we executed the same script on a recently restored copy of
the same database on the other node/virtual instance of the
active/active cluster (so the hardware would be the same). The script
only took about 30 minutes to finish. I am thinking that the large
difference in run time may be caused by physical fragmentation on the
production copy of the database. Will this be possible given the
large time difference?
I will do tests using your recommended BCP approach and see how it
performs on my machine.
Thanks.
Aramid|||Best is backup the DB , empty the table and then use alter script to
change the column name and load it back.
--
Regards ,
C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
http://www.geocities.com/dotnetinterviews/
My Interview Blog
http://spaces.msn.com/members/dotnetinterviews/|||A 3 disk Raid 5 is the worst Raid you can have when it comes to Writes.
This is especially true f the log file is also on the Raid 5 along with the
data files. Adding a raid 1 for the log files will dramatically speed up
this process. Is the other node using the same Raid array with a different
logical drive or does it have it's own Raid 5? If they are really two
different ones then you may have a bad disk which will dramatically decrease
both read and write performance. Fragmentation can account for some of the
issues but I wouldn't expect it to be that much.
--
Andrew J. Kelly SQL MVP
"Aramid" <aramid@.hotmail.com> wrote in message
news:4si5m114t13uq7tauetj881ol4vamkoo7b@.4ax.com...
> On Fri, 28 Oct 2005 20:58:36 -0400, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote:
>>How did you do it the first time? I assume you used Enterprise Manager
>>which is a real no-no when it comes to modifying tables. The alter table
>>command from Query analyzer would have been much better. It's hard to say
>>how long it should take without knowing a lot more of your hardware and
>>configurations. Another option is to BCP out all the data from that
>>table.
>>Truncate it. Set the recovery mode to Simple. Alter the table to add the
>>column. Then BCP it back in. You may need a format file to BCP back in
>>since the structure will change. In any case make sure you do a full
>>backup
>>first and set the recovery mode back to what it was if changed.
> Hi Andrew,
> I actually used the Alter Table command via QA. The database is
> running on a virtual instance of a SQL cluster. The machine is a
> dual-Xeon 2.8Ghz, and the shared array is a RAID-5 of 3 x 76Gb U320
> drives. Based on perfmon output, the bottleneck is disk IO, and in
> fact, the CPU stayed almost less than 10% the whole time.
> Actually, we executed the same script on a recently restored copy of
> the same database on the other node/virtual instance of the
> active/active cluster (so the hardware would be the same). The script
> only took about 30 minutes to finish. I am thinking that the large
> difference in run time may be caused by physical fragmentation on the
> production copy of the database. Will this be possible given the
> large time difference?
> I will do tests using your recommended BCP approach and see how it
> performs on my machine.
> Thanks.
> Aramid

adding a new column

Since we will be doing this again (as we need the timestamp table),
any tips would be appreciated.
Thanks.
Aramid
How did you do it the first time? I assume you used Enterprise Manager
which is a real no-no when it comes to modifying tables. The alter table
command from Query analyzer would have been much better. It's hard to say
how long it should take without knowing a lot more of your hardware and
configurations. Another option is to BCP out all the data from that table.
Truncate it. Set the recovery mode to Simple. Alter the table to add the
column. Then BCP it back in. You may need a format file to BCP back in
since the structure will change. In any case make sure you do a full backup
first and set the recovery mode back to what it was if changed.
Andrew J. Kelly SQL MVP
"Aramid" <aramid@.hotmail.com> wrote in message
news:vod5m11210l0l0v8568nik7nvlfsjf9gno@.4ax.com...
> Dear all,
> What would be the fastest way to add a new column (a timestamp column
> to be exact) to a table with about 15M records?
> We actually tried doing that earlier, but it was already running for 6
> hours and the script has not yet finished executing. We had to kill
> the transaction as it was already severely prolonging the downtime.
> The rollback process, of course, incurred almost 4.5 hours in itself.
>

> Since we will be doing this again (as we need the timestamp table),
> any tips would be appreciated.
> Thanks.
> Aramid
|||On Fri, 28 Oct 2005 20:58:36 -0400, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:

>How did you do it the first time? I assume you used Enterprise Manager
>which is a real no-no when it comes to modifying tables. The alter table
>command from Query analyzer would have been much better. It's hard to say
>how long it should take without knowing a lot more of your hardware and
>configurations. Another option is to BCP out all the data from that table.
>Truncate it. Set the recovery mode to Simple. Alter the table to add the
>column. Then BCP it back in. You may need a format file to BCP back in
>since the structure will change. In any case make sure you do a full backup
>first and set the recovery mode back to what it was if changed.
Hi Andrew,
I actually used the Alter Table command via QA. The database is
running on a virtual instance of a SQL cluster. The machine is a
dual-Xeon 2.8Ghz, and the shared array is a RAID-5 of 3 x 76Gb U320
drives. Based on perfmon output, the bottleneck is disk IO, and in
fact, the CPU stayed almost less than 10% the whole time.
Actually, we executed the same script on a recently restored copy of
the same database on the other node/virtual instance of the
active/active cluster (so the hardware would be the same). The script
only took about 30 minutes to finish. I am thinking that the large
difference in run time may be caused by physical fragmentation on the
production copy of the database. Will this be possible given the
large time difference?
I will do tests using your recommended BCP approach and see how it
performs on my machine.
Thanks.
Aramid
|||Best is backup the DB , empty the table and then use alter script to
change the column name and load it back.
Regards ,
C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
http://www.geocities.com/dotnetinterviews/
My Interview Blog
http://spaces.msn.com/members/dotnetinterviews/
|||A 3 disk Raid 5 is the worst Raid you can have when it comes to Writes.
This is especially true f the log file is also on the Raid 5 along with the
data files. Adding a raid 1 for the log files will dramatically speed up
this process. Is the other node using the same Raid array with a different
logical drive or does it have it's own Raid 5? If they are really two
different ones then you may have a bad disk which will dramatically decrease
both read and write performance. Fragmentation can account for some of the
issues but I wouldn't expect it to be that much.
Andrew J. Kelly SQL MVP
"Aramid" <aramid@.hotmail.com> wrote in message
news:4si5m114t13uq7tauetj881ol4vamkoo7b@.4ax.com...
> On Fri, 28 Oct 2005 20:58:36 -0400, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote:
>
> Hi Andrew,
> I actually used the Alter Table command via QA. The database is
> running on a virtual instance of a SQL cluster. The machine is a
> dual-Xeon 2.8Ghz, and the shared array is a RAID-5 of 3 x 76Gb U320
> drives. Based on perfmon output, the bottleneck is disk IO, and in
> fact, the CPU stayed almost less than 10% the whole time.
> Actually, we executed the same script on a recently restored copy of
> the same database on the other node/virtual instance of the
> active/active cluster (so the hardware would be the same). The script
> only took about 30 minutes to finish. I am thinking that the large
> difference in run time may be caused by physical fragmentation on the
> production copy of the database. Will this be possible given the
> large time difference?
> I will do tests using your recommended BCP approach and see how it
> performs on my machine.
> Thanks.
> Aramid
sql

adding a new column

Since we will be doing this again (as we need the timestamp table),
any tips would be appreciated.
Thanks.
AramidHow did you do it the first time? I assume you used Enterprise Manager
which is a real no-no when it comes to modifying tables. The alter table
command from Query analyzer would have been much better. It's hard to say
how long it should take without knowing a lot more of your hardware and
configurations. Another option is to BCP out all the data from that table.
Truncate it. Set the recovery mode to Simple. Alter the table to add the
column. Then BCP it back in. You may need a format file to BCP back in
since the structure will change. In any case make sure you do a full backup
first and set the recovery mode back to what it was if changed.
Andrew J. Kelly SQL MVP
"Aramid" <aramid@.hotmail.com> wrote in message
news:vod5m11210l0l0v8568nik7nvlfsjf9gno@.
4ax.com...
> Dear all,
> What would be the fastest way to add a new column (a timestamp column
> to be exact) to a table with about 15M records?
> We actually tried doing that earlier, but it was already running for 6
> hours and the script has not yet finished executing. We had to kill
> the transaction as it was already severely prolonging the downtime.
> The rollback process, of course, incurred almost 4.5 hours in itself.
>

> Since we will be doing this again (as we need the timestamp table),
> any tips would be appreciated.
> Thanks.
> Aramid|||On Fri, 28 Oct 2005 20:58:36 -0400, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:

>How did you do it the first time? I assume you used Enterprise Manager
>which is a real no-no when it comes to modifying tables. The alter table
>command from Query analyzer would have been much better. It's hard to say
>how long it should take without knowing a lot more of your hardware and
>configurations. Another option is to BCP out all the data from that table.
>Truncate it. Set the recovery mode to Simple. Alter the table to add the
>column. Then BCP it back in. You may need a format file to BCP back in
>since the structure will change. In any case make sure you do a full backu
p
>first and set the recovery mode back to what it was if changed.
Hi Andrew,
I actually used the Alter Table command via QA. The database is
running on a virtual instance of a SQL cluster. The machine is a
dual-Xeon 2.8Ghz, and the shared array is a RAID-5 of 3 x 76Gb U320
drives. Based on perfmon output, the bottleneck is disk IO, and in
fact, the CPU stayed almost less than 10% the whole time.
Actually, we executed the same script on a recently restored copy of
the same database on the other node/virtual instance of the
active/active cluster (so the hardware would be the same). The script
only took about 30 minutes to finish. I am thinking that the large
difference in run time may be caused by physical fragmentation on the
production copy of the database. Will this be possible given the
large time difference?
I will do tests using your recommended BCP approach and see how it
performs on my machine.
Thanks.
Aramid|||Best is backup the DB , empty the table and then use alter script to
change the column name and load it back.
Regards ,
C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
http://www.geocities.com/dotnetinterviews/
My Interview Blog
http://spaces.msn.com/members/dotnetinterviews/|||A 3 disk Raid 5 is the worst Raid you can have when it comes to Writes.
This is especially true f the log file is also on the Raid 5 along with the
data files. Adding a raid 1 for the log files will dramatically speed up
this process. Is the other node using the same Raid array with a different
logical drive or does it have it's own Raid 5? If they are really two
different ones then you may have a bad disk which will dramatically decrease
both read and write performance. Fragmentation can account for some of the
issues but I wouldn't expect it to be that much.
Andrew J. Kelly SQL MVP
"Aramid" <aramid@.hotmail.com> wrote in message
news:4si5m114t13uq7tauetj881ol4vamkoo7b@.
4ax.com...
> On Fri, 28 Oct 2005 20:58:36 -0400, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote:
>
> Hi Andrew,
> I actually used the Alter Table command via QA. The database is
> running on a virtual instance of a SQL cluster. The machine is a
> dual-Xeon 2.8Ghz, and the shared array is a RAID-5 of 3 x 76Gb U320
> drives. Based on perfmon output, the bottleneck is disk IO, and in
> fact, the CPU stayed almost less than 10% the whole time.
> Actually, we executed the same script on a recently restored copy of
> the same database on the other node/virtual instance of the
> active/active cluster (so the hardware would be the same). The script
> only took about 30 minutes to finish. I am thinking that the large
> difference in run time may be caused by physical fragmentation on the
> production copy of the database. Will this be possible given the
> large time difference?
> I will do tests using your recommended BCP approach and see how it
> performs on my machine.
> Thanks.
> Aramid

Sunday, March 25, 2012

Adding A default value in a column in a declare statment

Hi Can anybody help me.

I have this querey at the minute but when I attempt to put the default value of IND in the Sales Rep Code field I get an error Invalid column name, if I try and put 'IND' AS [Sales Rep Code] I get an incorrect syntax error.

DECLARE @.Query nVarchar(1000)
SET @.Query = N'SELECT NULL AS [GEO UNIT], NULL AS [PMC Invoice Date], IND AS [Sales Rep Code] FROM '+ 'Test' + RIGHT(DATEPART(yy, GETDATE()), 2) + '_' + CASE WHEN DATEPART(m, GETDATE()) IN ('11', '12', '1')
THEN 'Q1' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('2', '3', '4') THEN 'Q2' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('5', '6', '7')
THEN 'Q3' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('8', '9', '10') THEN 'Q4' END END END END + '.dbo.all_data' EXECUTE sp_executesql @.Query, N'@.level tinyint', @.level = 35

Can anybody please help on this.

ThanksHi Can anybody help me.

I have this querey at the minute but when I attempt to put the default value of IND in the Sales Rep Code field I get an error Invalid column name, if I try and put 'IND' AS [Sales Rep Code] I get an incorrect syntax error.

DECLARE @.Query nVarchar(1000)
SET @.Query = N'SELECT NULL AS [GEO UNIT], NULL AS [PMC Invoice Date], IND AS [Sales Rep Code] FROM '+ 'Test' + RIGHT(DATEPART(yy, GETDATE()), 2) + '_' + CASE WHEN DATEPART(m, GETDATE()) IN ('11', '12', '1')
THEN 'Q1' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('2', '3', '4') THEN 'Q2' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('5', '6', '7')
THEN 'Q3' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('8', '9', '10') THEN 'Q4' END END END END + '.dbo.all_data' EXECUTE sp_executesql @.Query, N'@.level tinyint', @.level = 35

Can anybody please help on this.

Thanks

would help us if u could post the ddl for the table|||what is the dataytpe of [Sales Rep Code]?
Off the cuff, it looks like your IND value is supposed to be a string.
Your example would be OK if you use 'IND' as [Sales Rep Code] - with the single quotes around IND.|||hi,

The datatype for SalesRep code is not defined as its in a view and I am creating the column [Sales Rep Code] with the default value of IND,

e.g

Select 'IND' AS [Sales Rep Code] from test would create a [Sales Rep Code] populated by IND for each row.

I have tried the 'IND' and it appears that it is incorrect syntax, this is beacuse it is closing the string value of the declare statment at the fist ' but I dont have a way around this.

Any Ideas|||Hi thanks for everyones help finally got it working but I still have one problem in that it is over 4000 carachters long and the @.test has a maximum of 4000 carachters is there any way around this.

Thanks

Thursday, March 29, 2012

Adding a subtotal column to a matrix

Tuesday, March 27, 2012

Adding a not null column to replicated table

Adding a new VarBinaryMax column to an existing table using SMO.

adding a new line

adding a new key; planning for millions of records

adding a new data column (not derived) midway thru a data flow

adding a new data column (not derived) midway thru a data flow

adding a new data column (not derived) midway thru a data flow

adding a new column to a table involved in merge replication

adding a new column to a large table

adding a new column to a large table

adding a new column to a large table

Adding a new column of a UDT type fails on "invalid data type"

Adding a new column is DBNULL

Adding a new column in a certain position....

Adding a new Column after a particular column

adding a new column

adding a new column

adding a new column

Sunday, March 25, 2012

Adding A default value in a column in a declare statment

Accessing more than

Blog Archive

About Me