question with type of replication

Hello,
I have a question on the subscriptions and type of replication to use.
We have about 5 subscriptions to a published db setup as merge replication
via the websync on sql2k5. We would actually like the subscriptions (all
running sql express) to just download changes to the publisher. We do not
need the publisher to pass any data up to the subscribers. Is there a way to
do this? We need to use the functionality of web synchronization, if at all
possible. Thanks in advance.
Jake
Use the exchangetype of uploadonly.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jake" <noreply@.nowhere.com> wrote in message
news:uOdUdXyRGHA.3052@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a question on the subscriptions and type of replication to use.
> We have about 5 subscriptions to a published db setup as merge replication
> via the websync on sql2k5. We would actually like the subscriptions (all
> running sql express) to just download changes to the publisher. We do not
> need the publisher to pass any data up to the subscribers. Is there a way
> to do this? We need to use the functionality of web synchronization, if at
> all possible. Thanks in advance.
> Jake
>
|||Hilary,
Where would I add this script setting in SQL2k5? Since the clients are
sql express there really isn't a scheduled agent to run the replication,
we're using a .net app to start the replication process. Prior to this we
are running the following scripts to setup replication. Any further
clarification would be appreciated. Thanks in advance.
--BEGIN: Script to be run at Subscriber FOR
WAN--
sp_addmergepullsubscription
@.publication = 'pub_subscriber_db', --the name given to the publication
during the creation of the publication via the wizard
@.publisher_db = 'subscriber_db', --the name of the actual database running
on the server instance
@.publisher = 'computer-db', --the name of the server instance
@.subscriber_type = 'global',
@.sync_type='automatic'
--BEGIN: Script to be run at Publisher FOR
WAN--
use [subscriber_db]
exec sp_addmergesubscription @.publication = N'pub_wcpc_arizona',
@.subscriber = N'subscriber-01\subscriber',
@.subscriber_db = N'subscriber_db',
@.subscription_type = N'pull',
@.subscriber_type = N'global',
@.subscription_priority = 0,
@.sync_type = N'Automatic'
GO
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23PnLtc3RGHA.5924@.TK2MSFTNGP09.phx.gbl...
> Use the exchangetype of uploadonly.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Jake" <noreply@.nowhere.com> wrote in message
> news:uOdUdXyRGHA.3052@.TK2MSFTNGP09.phx.gbl...
>
|||Jake,
in SQL Server 2005 there is the @.subscriber_upload_options parameter of
sp_addmergearticle which takes the following values:
0 : No restrictions. Changes made at the Subscriber are uploaded to the
Publisher.
1 : Changes are allowed at the Subscriber, but they are not uploaded to the
Publisher.
2 : Changes are not allowed at the Subscriber
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Is there a way to have changes made on the subscriber downloaded the the
publisher, but additions on the publisher are not uploaded to the
subscriber?
Jake
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23bIDV73RGHA.2300@.TK2MSFTNGP11.phx.gbl...
> Jake,
> in SQL Server 2005 there is the @.subscriber_upload_options parameter of
> sp_addmergearticle which takes the following values:
> 0 : No restrictions. Changes made at the Subscriber are uploaded to the
> Publisher.
> 1 : Changes are allowed at the Subscriber, but they are not uploaded to
> the Publisher.
> 2 : Changes are not allowed at the Subscriber
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Paul,
Let me re-phrase.
Scenario:
We have two (actually more but there shouldn't be a difference between two
or more) subscribers and want their data to download to the publisher, but
we do not want subscriber 1 data to be uploaded to subscriber 2 if
subscriber 2 replicates after subscriber 1 has.
Jake
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23bIDV73RGHA.2300@.TK2MSFTNGP11.phx.gbl...
> Jake,
> in SQL Server 2005 there is the @.subscriber_upload_options parameter of
> sp_addmergearticle which takes the following values:
> 0 : No restrictions. Changes made at the Subscriber are uploaded to the
> Publisher.
> 1 : Changes are allowed at the Subscriber, but they are not uploaded to
> the Publisher.
> 2 : Changes are not allowed at the Subscriber
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Jake,
you could possibly modift the exchangetype parameter on the commandline
dynamically when initiating a pull subscription. However I'd be afraid that
the data will soon become corrupt this way. Merge is really designed for
data to be fully partitioned amongst all the nodes involved. Partitioning is
ok, and not replicating deletes to a subscriber is also ok - but what you
are suggesting could lead to a complete spaghetti of data. Perhaps you could
explain further the business requirements and we might arrive at a different
way of realising them.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Thanks for the responses so far. I think we have it. We're going to add
a column for the workstation and use filter rows with the host_name. If I am
correct this will only replicate the items for that subscriber to the
publisher, back and forth. Is this correct?
Also is there a way to limit the amount of RAM that replication can use? We
have a couple of subscribers, using sql express with little RAM and are
running out of VM. My thought is if we can limit the RAM being used it will
avoid this issue, but increase the amount of time it takes to replicate.
What are your thoughts?
Jake
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uSj%23pL4RGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Jake,
> you could possibly modift the exchangetype parameter on the commandline
> dynamically when initiating a pull subscription. However I'd be afraid
> that the data will soon become corrupt this way. Merge is really designed
> for data to be fully partitioned amongst all the nodes involved.
> Partitioning is ok, and not replicating deletes to a subscriber is also
> ok - but what you are suggesting could lead to a complete spaghetti of
> data. Perhaps you could explain further the business requirements and we
> might arrive at a different way of realising them.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
|||Jake,
for the first part, you could use dynamic filtering, but if you only have a
few subscribers, a separate publication for each subscriber works better.
Limiting the RAM for the replication exe - interesting. To be honest, I
don't know how this can be done. I'll look into it though.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Thanks for the help so far. If you find a way to limit the Ram I'd be
very appreciative.
Jake
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23mgw854RGHA.5656@.TK2MSFTNGP11.phx.gbl...
> Jake,
> for the first part, you could use dynamic filtering, but if you only have
> a few subscribers, a separate publication for each subscriber works
> better. Limiting the RAM for the replication exe - interesting. To be
> honest, I don't know how this can be done. I'll look into it though.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>

Question Regarding Stored Procedure??OUTPUTS

I have a stored procedure that I just need to return the output to my program.It is a Select All type statement.I will post my vb code that works when I use both inputs and outputs but not for all output procedure...I dont get it.
Here is the Stored Procedure....

CREATE procedure dbo.IDXAppt_Settings_NET
(
@.SQLADD nvarchar(15)Output,
@.SQLDatabase nvarchar(20)Output,
@.SQLLogin nvarchar(20)Output,
@.SQLPass nvarchar(20)Output
)
as
select
@.SQLADD=SQLAddress,
@.SQLDatabase=SQLDatabase,
@.SQLLogin=SQLLogin,
@.SQLPass=SQLPassword

from
Clinic_Settings

GO

Here is the Vb.Net Code......
To retrieve the elements that does not give me an error just gives me no data...

Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim consql As New SqlConnection("server=myserver,database=APPOINTMENTS;uid=webtest;pwd=webtest")
Dim cmdsql As New SqlCommand

Dim parmSQLAddress As SqlParameter
Dim parmDatabase As SqlParameter
Dim parmLogin As SqlParameter
Dim parmSqlPass As SqlParameter

Dim strtest As String
Dim db As String
Dim login As String
Dim pass As String

cmdsql = New SqlCommand("Appt_Settings_NET", consql)
cmdsql.CommandType = CommandType.StoredProcedure
parmDatabase = cmdsql.Parameters.Add("@.SQLData", SqlDbType.NVarChar)
parmDatabase.Size = 20
parmDatabase.Direction = ParameterDirection.Output
db = cmdsql.Parameters("@.SQLData").Value

parmLogin = cmdsql.Parameters.Add("@.SQLLogin", SqlDbType.NVarChar)
parmLogin.Size = 20
parmLogin.Direction = ParameterDirection.Output
login = cmdsql.Parameters("@.SQLLogin").Value

parmSqlPass = cmdsql.Parameters.Add("@.SQLPass", SqlDbType.NVarChar)
parmSqlPass.Size = 20
parmSqlPass.Direction = ParameterDirection.Output
pass = cmdsql.Parameters("@.SQLPass").Value

parmSQLAddress = cmdsql.Parameters.Add("@.SQLADD", SqlDbType.NVarChar)
parmSQLAddress.Size = 15
parmSQLAddress.Direction = ParameterDirection.Output
strtest = cmdsql.Parameters("@.SQLADD").Value
consql.Open()
cmdsql.ExecuteNonQuery()

Label1.Text = strtest
End SubYou may get the output value after the stored procedure executed.|||Doesn't look like you want to use OUTPUTs here. You're query will get every row back from the table, so only the last (or is it the first - last I think) row will go into your output params.

Question re: SSIS "Script Task" + VB.NET keyboard configuration

I was pretty excited when my first script ran in this type of task. But I soon noticed that I couldn't find the "watch" or "immediate" windows I was used to in standard VB.NET. Did I miss them or are they simply not available in the Script Task editor?

TIA,

barker

P.S. Also under the VB.NET environment, I can Import the VB.NET default keyboard settings (e.g. Step Into is F8; Step Over is Shift+F8) into the IDE. Is the same option available for the Script task editor?

They are just not visible by default. After you hit the breakpoint you can go to the menu "Debug>>>Windows>>Locals" for example.

The script task editor is just VS wired up for VSA and VB.net. So you should be able to accomplish this though I admit I have not explored that. I know I have re-assigned my own hot keys to things via "tools>>options>>keyboard"

sql

question on upper bound primary key of type int

I have several tables in a deployed database in which the primary key is of type int, and autoincrements by 1 each time a record is added. My question is, since ints are 32-bit, what happens when its value reaches 4,294,967,296? I know that seems like an extrememly large amount of records, but when we imported the data into the database it started at key value 1,000,000. I don't know how to make it use lower numbers which are currently not being used (numbers below 1,000,000), and I am worried I will have problems when I reach the upper bound. What kind of problems could this cause? Should I change the primary key's type?

Thanks!

the upper bound is somewhere around 2.1 bill. Yes when you reach that limit your application will fail. You cannot insert any new data. You could put some alert in place to identify or predict when the storm is coming. you could create a job that gets the MAX(ID) every week and you can monitor the growth of the table. Once the ID reaches closer to 2 bil you can increase your frequency of monitoring. To fix it, you need modify the column and change it to BigInt. Please do not even bother to try ALTER TABLE...ALTER COLUMN...the server will hang.|||Ok, I'll just modify the primary key type. Thanks for the response.sql

Question on fact(degenerate) type of fact table relationship

Hi, all,

Could any of you give me some guidance on the fact(degenerate) type of fact tabel relationship between a dimension table and a fact table? Though its definition as: the dimension members are derived from the fact table. How does it really work?

We knwo there are degenerate dimensions which are included in fact tables e.g TransactionID etc in the underlying relational database. What is the idea of dealing with these scenario where we still want to see the degenerate dimensions in the cube dimensions instead of residing meaningless in the cube measure groups? Any best practices or suggestions?

Hope my question is clear for your help and I am again looking forward to hearing from you shortly for your advices.

With best regards,

Yours sincerely,

Hello Helen. When you build a dimension with the fact table as a data source these dimensions will not be different from dimensions with separate tables as data sources.

So you can start the dimension wizard and choose the fact table as a data source and pick the attributes that will be part of the degenerate dimension.

You can only have one degenerate dimension in a cube.

When you put them in a separate table you will have a one-to-one relation with the fact table at the leaf level so that approach is not that common.

Have a look at www.kimballgroup.com or google on degenerate dimensions.

From what I have seen they will take a little longer time to process than one-to-many dimensions.

HTH

Thomas Ivarsson

|||

Hi, Thomas,

Thanks a lot for your advices. So it works for all attributes in the dimension wizard with fact table selected as the data source? E.g. for some attibutes with only a few distinctive values (which we can put them into junk dimensions, then in this case, we dont necessary to create separate junk dimensions for these attributes which are originally residing in the fact table, instead we can use the dimension wizard to create new cube dimension with the fact table as data source)?

Therefore, in this case, we have to define the fact relationship as fact(degenerate) relationship for all cases where the dimension memembers are derived from the fact table?

Hope my question is clear.

With best regards,

Yours sincerely,

|||

Hi Helen.

You can create standard dimensions with attributes from the fact table without marking them as fact table dimension in the dimension usage tab. It also possible to mark one relation between the fact table /measure group and a dimension as a fact table dimension. Both ways will work and if you avoid the fact table relation type(dimension usage) you can make more than one.

Attributes with a few distinct values normally qualifies as standard dimensions and can be placed in a dimension table with a one-to-many relation with the fact table.

Junk or fact table dimensions normally have a one-to-one relation with the fact table.

HTH

Thomas Ivarsson

|||

Hi, Thomas,

Thank you very much for your very kind advices and help.

It then sounds like it is better to create standard dimensions with attributes from fact table which will enable more than one relationship types between the fact table and the derived dimension table.

Hopefully I am right.

Thank you again.

With best regards,

Yours sincerely,

Question on DTS

HI all,
Which is better, to use DTS for CSV type file imports or to write your own
import app.
Considering the following that it is most likely that the structure of the
import file will change a few times within the next year, and that this is
not inhouse but for an outside client, and that they would most probably be
using SQLXpress
Value your thoughts
Thanks
RobertDTS is the right tool for this task.
Although I would not be surprised if a very experienced C# developer with
basic DTS knowledge can write this application a lot faster than writing the
DTS package.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Robert Bravery" wrote:

> HI all,
> Which is better, to use DTS for CSV type file imports or to write your own
> import app.
> Considering the following that it is most likely that the structure of the
> import file will change a few times within the next year, and that this is
> not inhouse but for an outside client, and that they would most probably b
e
> using SQLXpress
> Value your thoughts
> Thanks
> Robert
>
>|||Thanks Ben,
Next question:
How easy would it be to update the dts package on a clients machine when and
if structure and protocol changes.
Thanks
Robert
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:256513B7-AABF-4276-9E7F-2343D256C30B@.microsoft.com...
> DTS is the right tool for this task.
> Although I would not be surprised if a very experienced C# developer with
> basic DTS knowledge can write this application a lot faster than writing
the
> DTS package.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Robert Bravery" wrote:
>
own
the
is
be|||Using the DTS GUI Interface (DTS Designer) is very easy. Depending on the
complexity of your DTS package you may want to document what to do to
implement these future changes, instead of trying to understand how the
package works after you have not seen it for ws or months.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Robert Bravery" wrote:

> Thanks Ben,
> Next question:
> How easy would it be to update the dts package on a clients machine when a
nd
> if structure and protocol changes.
> Thanks
> Robert
> "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> news:256513B7-AABF-4276-9E7F-2343D256C30B@.microsoft.com...
> the
> own
> the
> is
> be
>
>

question on crystal reports

Hi,
there is a requirement of making Login name field just 4 characters. This is Group name type field which shows login names. We have to decrease the length of login names to save space. How shd I proceed?
ThanksSorry, not clear on the question, are you trying to reduce the length in the report, or in the database ?

Question on changing data types on a table

Hi Guys,
I have always wondered what SQL Server actually does when I try to change
the data type on any column in a table. I know that SQL Server will rebuild
the whole table when its changing the data type, so if I had multiple columns
that I wanted to change data types for, this is the only way I have been able
to do it using TSQL:
ALTER TABLE TEST
ALTER COLUMN A (char(4))
ALTER TABLE TEST
ALTER COLUMN B (INT)
ALTER TABLE TEST
ALTER COLUMN C (FLOAT)
Does the above mean that SQL Server has to rebuild my table 3 different
times? How could I rewrite this code so that SQL Server can convert all my
columns with one rebuild? Thanks guys!well! u dont have that flexibility :(
u can add multiple columns using ALTER
--
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Shishir Viriyala" wrote:
> Hi Guys,
> I have always wondered what SQL Server actually does when I try to change
> the data type on any column in a table. I know that SQL Server will rebuild
> the whole table when its changing the data type, so if I had multiple columns
> that I wanted to change data types for, this is the only way I have been able
> to do it using TSQL:
> ALTER TABLE TEST
> ALTER COLUMN A (char(4))
> ALTER TABLE TEST
> ALTER COLUMN B (INT)
> ALTER TABLE TEST
> ALTER COLUMN C (FLOAT)
> Does the above mean that SQL Server has to rebuild my table 3 different
> times? How could I rewrite this code so that SQL Server can convert all my
> columns with one rebuild? Thanks guys!
>|||Okay, how about doing it in Enterprise Manager's Design Table section? I can
see all the columns with their data types at once... if I changed multiple
data types there, does SQL Server rebuild the table for each column, or does
do everything in one shot? Appreciate the help!
"Chandra" wrote:
> well! u dont have that flexibility :(
> u can add multiple columns using ALTER
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Shishir Viriyala" wrote:
> > Hi Guys,
> >
> > I have always wondered what SQL Server actually does when I try to change
> > the data type on any column in a table. I know that SQL Server will rebuild
> > the whole table when its changing the data type, so if I had multiple columns
> > that I wanted to change data types for, this is the only way I have been able
> > to do it using TSQL:
> >
> > ALTER TABLE TEST
> > ALTER COLUMN A (char(4))
> >
> > ALTER TABLE TEST
> > ALTER COLUMN B (INT)
> >
> > ALTER TABLE TEST
> > ALTER COLUMN C (FLOAT)
> >
> > Does the above mean that SQL Server has to rebuild my table 3 different
> > times? How could I rewrite this code so that SQL Server can convert all my
> > columns with one rebuild? Thanks guys!
> >|||it rebuilds for each column.
--
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Shishir Viriyala" wrote:
> Okay, how about doing it in Enterprise Manager's Design Table section? I can
> see all the columns with their data types at once... if I changed multiple
> data types there, does SQL Server rebuild the table for each column, or does
> do everything in one shot? Appreciate the help!
> "Chandra" wrote:
> > well! u dont have that flexibility :(
> > u can add multiple columns using ALTER
> >
> > --
> > best Regards,
> > Chandra
> > http://chanduas.blogspot.com/
> > http://groups.msn.com/SQLResource/
> > ---
> >
> >
> >
> > "Shishir Viriyala" wrote:
> >
> > > Hi Guys,
> > >
> > > I have always wondered what SQL Server actually does when I try to change
> > > the data type on any column in a table. I know that SQL Server will rebuild
> > > the whole table when its changing the data type, so if I had multiple columns
> > > that I wanted to change data types for, this is the only way I have been able
> > > to do it using TSQL:
> > >
> > > ALTER TABLE TEST
> > > ALTER COLUMN A (char(4))
> > >
> > > ALTER TABLE TEST
> > > ALTER COLUMN B (INT)
> > >
> > > ALTER TABLE TEST
> > > ALTER COLUMN C (FLOAT)
> > >
> > > Does the above mean that SQL Server has to rebuild my table 3 different
> > > times? How could I rewrite this code so that SQL Server can convert all my
> > > columns with one rebuild? Thanks guys!
> > >|||Check and see :-). Press the "save change script" button and you will see the SQL that EM is about
to execute.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Shishir Viriyala" <ShishirViriyala@.discussions.microsoft.com> wrote in message
news:F183A811-5BBF-4BA0-9E6D-BE671FDB8837@.microsoft.com...
> Okay, how about doing it in Enterprise Manager's Design Table section? I can
> see all the columns with their data types at once... if I changed multiple
> data types there, does SQL Server rebuild the table for each column, or does
> do everything in one shot? Appreciate the help!
> "Chandra" wrote:
>> well! u dont have that flexibility :(
>> u can add multiple columns using ALTER
>> --
>> best Regards,
>> Chandra
>> http://chanduas.blogspot.com/
>> http://groups.msn.com/SQLResource/
>> ---
>>
>> "Shishir Viriyala" wrote:
>> > Hi Guys,
>> >
>> > I have always wondered what SQL Server actually does when I try to change
>> > the data type on any column in a table. I know that SQL Server will rebuild
>> > the whole table when its changing the data type, so if I had multiple columns
>> > that I wanted to change data types for, this is the only way I have been able
>> > to do it using TSQL:
>> >
>> > ALTER TABLE TEST
>> > ALTER COLUMN A (char(4))
>> >
>> > ALTER TABLE TEST
>> > ALTER COLUMN B (INT)
>> >
>> > ALTER TABLE TEST
>> > ALTER COLUMN C (FLOAT)
>> >
>> > Does the above mean that SQL Server has to rebuild my table 3 different
>> > times? How could I rewrite this code so that SQL Server can convert all my
>> > columns with one rebuild? Thanks guys!
>> >|||Aha! Thanks for the tip Tibor! I have included what the change script was
showing me. The two columns for which I changed data types were CASEID and
DX. Note that both those columns are being converted in one statement.
Using EP is definitely the way to go!
Thanks to you and Chandra for the input......
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_ip_adm_dx
(
CASEID int NULL,
DX varchar(5) NULL,
DXNO int NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.ip_adm_dx)
EXEC('INSERT INTO dbo.Tmp_ip_adm_dx (CASEID, DX, DXNO)
SELECT CONVERT(int, CASEID), CONVERT(varchar(5), DX), DXNO FROM
dbo.ip_adm_dx TABLOCKX')
GO
DROP TABLE dbo.ip_adm_dx
GO
EXECUTE sp_rename N'dbo.Tmp_ip_adm_dx', N'ip_adm_dx', 'OBJECT'
GO
COMMIT
"Tibor Karaszi" wrote:
> Check and see :-). Press the "save change script" button and you will see the SQL that EM is about
> to execute.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Shishir Viriyala" <ShishirViriyala@.discussions.microsoft.com> wrote in message
> news:F183A811-5BBF-4BA0-9E6D-BE671FDB8837@.microsoft.com...
> > Okay, how about doing it in Enterprise Manager's Design Table section? I can
> > see all the columns with their data types at once... if I changed multiple
> > data types there, does SQL Server rebuild the table for each column, or does
> > do everything in one shot? Appreciate the help!
> >
> > "Chandra" wrote:
> >
> >> well! u dont have that flexibility :(
> >> u can add multiple columns using ALTER
> >>
> >> --
> >> best Regards,
> >> Chandra
> >> http://chanduas.blogspot.com/
> >> http://groups.msn.com/SQLResource/
> >> ---
> >>
> >>
> >>
> >> "Shishir Viriyala" wrote:
> >>
> >> > Hi Guys,
> >> >
> >> > I have always wondered what SQL Server actually does when I try to change
> >> > the data type on any column in a table. I know that SQL Server will rebuild
> >> > the whole table when its changing the data type, so if I had multiple columns
> >> > that I wanted to change data types for, this is the only way I have been able
> >> > to do it using TSQL:
> >> >
> >> > ALTER TABLE TEST
> >> > ALTER COLUMN A (char(4))
> >> >
> >> > ALTER TABLE TEST
> >> > ALTER COLUMN B (INT)
> >> >
> >> > ALTER TABLE TEST
> >> > ALTER COLUMN C (FLOAT)
> >> >
> >> > Does the above mean that SQL Server has to rebuild my table 3 different
> >> > times? How could I rewrite this code so that SQL Server can convert all my
> >> > columns with one rebuild? Thanks guys!
> >> >
>|||On Fri, 5 Aug 2005 08:15:38 -0700, Shishir Viriyala wrote:
>Okay, how about doing it in Enterprise Manager's Design Table section? I can
>see all the columns with their data types at once... if I changed multiple
>data types there, does SQL Server rebuild the table for each column, or does
>do everything in one shot? Appreciate the help!
Hi Shishir,
Neither. Enterprise Manager will simply create a new table, copy over
the data, then drop the old table. If the table is involved in any
foreign key relationships, it will drop and recreate them as well. And
if the table has any triggers, they too will be recreated.
Copying the data over to a new table might be the best strategy in your
specific case, but in general, the methods chosen by EM to apply changes
tend to be overkill. (And very slow on large tables!)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Question on changing data types on a table

Hi Guys,
I have always wondered what SQL Server actually does when I try to change
the data type on any column in a table. I know that SQL Server will rebuild
the whole table when its changing the data type, so if I had multiple column
s
that I wanted to change data types for, this is the only way I have been abl
e
to do it using TSQL:
ALTER TABLE TEST
ALTER COLUMN A (char(4))
ALTER TABLE TEST
ALTER COLUMN B (INT)
ALTER TABLE TEST
ALTER COLUMN C (FLOAT)
Does the above mean that SQL Server has to rebuild my table 3 different
times? How could I rewrite this code so that SQL Server can convert all my
columns with one rebuild? Thanks guys!well! u dont have that flexibility
u can add multiple columns using ALTER
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Shishir Viriyala" wrote:

> Hi Guys,
> I have always wondered what SQL Server actually does when I try to change
> the data type on any column in a table. I know that SQL Server will rebui
ld
> the whole table when its changing the data type, so if I had multiple colu
mns
> that I wanted to change data types for, this is the only way I have been a
ble
> to do it using TSQL:
> ALTER TABLE TEST
> ALTER COLUMN A (char(4))
> ALTER TABLE TEST
> ALTER COLUMN B (INT)
> ALTER TABLE TEST
> ALTER COLUMN C (FLOAT)
> Does the above mean that SQL Server has to rebuild my table 3 different
> times? How could I rewrite this code so that SQL Server can convert all m
y
> columns with one rebuild? Thanks guys!
>|||Okay, how about doing it in Enterprise Manager's Design Table section? I ca
n
see all the columns with their data types at once... if I changed multiple
data types there, does SQL Server rebuild the table for each column, or does
do everything in one shot? Appreciate the help!
"Chandra" wrote:
[vbcol=seagreen]
> well! u dont have that flexibility
> u can add multiple columns using ALTER
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Shishir Viriyala" wrote:
>|||it rebuilds for each column.
--
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Shishir Viriyala" wrote:
[vbcol=seagreen]
> Okay, how about doing it in Enterprise Manager's Design Table section? I
can
> see all the columns with their data types at once... if I changed multipl
e
> data types there, does SQL Server rebuild the table for each column, or do
es
> do everything in one shot? Appreciate the help!
> "Chandra" wrote:
>|||Check and see :-). Press the "save change script" button and you will see th
e SQL that EM is about
to execute.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Shishir Viriyala" <ShishirViriyala@.discussions.microsoft.com> wrote in mess
age
news:F183A811-5BBF-4BA0-9E6D-BE671FDB8837@.microsoft.com...[vbcol=seagreen]
> Okay, how about doing it in Enterprise Manager's Design Table section? I
can
> see all the columns with their data types at once... if I changed multipl
e
> data types there, does SQL Server rebuild the table for each column, or do
es
> do everything in one shot? Appreciate the help!
> "Chandra" wrote:
>|||Aha! Thanks for the tip Tibor! I have included what the change script was
showing me. The two columns for which I changed data types were CASEID and
DX. Note that both those columns are being converted in one statement.
Using EP is definitely the way to go!
Thanks to you and Chandra for the input......
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_ip_adm_dx
(
CASEID int NULL,
DX varchar(5) NULL,
DXNO int NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.ip_adm_dx)
EXEC('INSERT INTO dbo.Tmp_ip_adm_dx (CASEID, DX, DXNO)
SELECT CONVERT(int, CASEID), CONVERT(varchar(5), DX), DXNO FROM
dbo.ip_adm_dx TABLOCKX')
GO
DROP TABLE dbo.ip_adm_dx
GO
EXECUTE sp_rename N'dbo.Tmp_ip_adm_dx', N'ip_adm_dx', 'OBJECT'
GO
COMMIT
"Tibor Karaszi" wrote:

> Check and see :-). Press the "save change script" button and you will see
the SQL that EM is about
> to execute.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Shishir Viriyala" <ShishirViriyala@.discussions.microsoft.com> wrote in me
ssage
> news:F183A811-5BBF-4BA0-9E6D-BE671FDB8837@.microsoft.com...
>|||On Fri, 5 Aug 2005 08:15:38 -0700, Shishir Viriyala wrote:

>Okay, how about doing it in Enterprise Manager's Design Table section? I c
an
>see all the columns with their data types at once... if I changed multiple
>data types there, does SQL Server rebuild the table for each column, or doe
s
>do everything in one shot? Appreciate the help!
Hi Shishir,
Neither. Enterprise Manager will simply create a new table, copy over
the data, then drop the old table. If the table is involved in any
foreign key relationships, it will drop and recreate them as well. And
if the table has any triggers, they too will be recreated.
Copying the data over to a new table might be the best strategy in your
specific case, but in general, the methods chosen by EM to apply changes
tend to be overkill. (And very slow on large tables!)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Question on changing data types on a table

Hi Guys,
I have always wondered what SQL Server actually does when I try to change
the data type on any column in a table. I know that SQL Server will rebuild
the whole table when its changing the data type, so if I had multiple columns
that I wanted to change data types for, this is the only way I have been able
to do it using TSQL:
ALTER TABLE TEST
ALTER COLUMN A (char(4))
ALTER TABLE TEST
ALTER COLUMN B (INT)
ALTER TABLE TEST
ALTER COLUMN C (FLOAT)
Does the above mean that SQL Server has to rebuild my table 3 different
times? How could I rewrite this code so that SQL Server can convert all my
columns with one rebuild? Thanks guys!
well! u dont have that flexibility
u can add multiple columns using ALTER
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
"Shishir Viriyala" wrote:

> Hi Guys,
> I have always wondered what SQL Server actually does when I try to change
> the data type on any column in a table. I know that SQL Server will rebuild
> the whole table when its changing the data type, so if I had multiple columns
> that I wanted to change data types for, this is the only way I have been able
> to do it using TSQL:
> ALTER TABLE TEST
> ALTER COLUMN A (char(4))
> ALTER TABLE TEST
> ALTER COLUMN B (INT)
> ALTER TABLE TEST
> ALTER COLUMN C (FLOAT)
> Does the above mean that SQL Server has to rebuild my table 3 different
> times? How could I rewrite this code so that SQL Server can convert all my
> columns with one rebuild? Thanks guys!
>
|||Okay, how about doing it in Enterprise Manager's Design Table section? I can
see all the columns with their data types at once... if I changed multiple
data types there, does SQL Server rebuild the table for each column, or does
do everything in one shot? Appreciate the help!
"Chandra" wrote:
[vbcol=seagreen]
> well! u dont have that flexibility
> u can add multiple columns using ALTER
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
>
> "Shishir Viriyala" wrote:
|||it rebuilds for each column.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
"Shishir Viriyala" wrote:
[vbcol=seagreen]
> Okay, how about doing it in Enterprise Manager's Design Table section? I can
> see all the columns with their data types at once... if I changed multiple
> data types there, does SQL Server rebuild the table for each column, or does
> do everything in one shot? Appreciate the help!
> "Chandra" wrote:
|||Check and see :-). Press the "save change script" button and you will see the SQL that EM is about
to execute.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Shishir Viriyala" <ShishirViriyala@.discussions.microsoft.com> wrote in message
news:F183A811-5BBF-4BA0-9E6D-BE671FDB8837@.microsoft.com...[vbcol=seagreen]
> Okay, how about doing it in Enterprise Manager's Design Table section? I can
> see all the columns with their data types at once... if I changed multiple
> data types there, does SQL Server rebuild the table for each column, or does
> do everything in one shot? Appreciate the help!
> "Chandra" wrote:
|||Aha! Thanks for the tip Tibor! I have included what the change script was
showing me. The two columns for which I changed data types were CASEID and
DX. Note that both those columns are being converted in one statement.
Using EP is definitely the way to go!
Thanks to you and Chandra for the input......
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_ip_adm_dx
(
CASEID int NULL,
DX varchar(5) NULL,
DXNO int NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.ip_adm_dx)
EXEC('INSERT INTO dbo.Tmp_ip_adm_dx (CASEID, DX, DXNO)
SELECT CONVERT(int, CASEID), CONVERT(varchar(5), DX), DXNO FROM
dbo.ip_adm_dx TABLOCKX')
GO
DROP TABLE dbo.ip_adm_dx
GO
EXECUTE sp_rename N'dbo.Tmp_ip_adm_dx', N'ip_adm_dx', 'OBJECT'
GO
COMMIT
"Tibor Karaszi" wrote:

> Check and see :-). Press the "save change script" button and you will see the SQL that EM is about
> to execute.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Shishir Viriyala" <ShishirViriyala@.discussions.microsoft.com> wrote in message
> news:F183A811-5BBF-4BA0-9E6D-BE671FDB8837@.microsoft.com...
>
|||On Fri, 5 Aug 2005 08:15:38 -0700, Shishir Viriyala wrote:

>Okay, how about doing it in Enterprise Manager's Design Table section? I can
>see all the columns with their data types at once... if I changed multiple
>data types there, does SQL Server rebuild the table for each column, or does
>do everything in one shot? Appreciate the help!
Hi Shishir,
Neither. Enterprise Manager will simply create a new table, copy over
the data, then drop the old table. If the table is involved in any
foreign key relationships, it will drop and recreate them as well. And
if the table has any triggers, they too will be recreated.
Copying the data over to a new table might be the best strategy in your
specific case, but in general, the methods chosen by EM to apply changes
tend to be overkill. (And very slow on large tables!)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)