altering a column of a published table (trans repl)

Hi,
In BOL there is reference only to sp_repladdcolumn and sp_repldropcolumn
(add and drop), but nothing about altering a column, or have I missed it?
I have to change the collation of a column (that belongs to a table that is
transactionally replicated ) from sensitive to insensitive. Is the only way
to do that is to:
1 - add a New column with the correct collation (sp_repladdcolumn )
2 - copy the data from the old column into the New column (update statement)
3 - drop the old column (sp_repldropcolumn)
Any better way? or have I missed anything?
Thanks
You didn't miss anything - that's a problem we all have faced at some time
or other . Actually there's another level of iteration you missed out, as
your table will be missing the column with the oldname, so if this is to be
maintained you have to do the whole process again. There is an alternative
of dropping the subscriptions to the table, removing the table from the
publication, altering the table then readding to the publication then adding
subscriptions to this table. In this way you can effectively reinitialize on
a table basis. All MUCH easier in SQL Server 2005 of course - the Alter
Table statement will itself be sufficient for most things.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul,
right, since add comes before drop, I would need 2 pairs of (add, drop ) ;
the first to alter the collation, the second to alter the column name (to
set it back to the original name) - correct?
and probably to have these 4 sp_Replxxx bracketed by Begin Tran - Commit
Tran.
is the alternative you indicated better in some ways (safer, faster, ...) ?
Thanks
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:#CM4dxyyFHA.2212@.TK2MSFTNGP15.phx.gbl...
> You didn't miss anything - that's a problem we all have faced at some time
> or other . Actually there's another level of iteration you missed out,
as
> your table will be missing the column with the oldname, so if this is to
be
> maintained you have to do the whole process again. There is an alternative
> of dropping the subscriptions to the table, removing the table from the
> publication, altering the table then readding to the publication then
adding
> subscriptions to this table. In this way you can effectively reinitialize
on
> a table basis. All MUCH easier in SQL Server 2005 of course - the Alter
> Table statement will itself be sufficient for most things.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||It's potentially less processing time - largely depends on the 'width' of
your table, ie for a table not especially wide then I'd do the drop method.
if I had 200 columns, I'd do the column technique.
Cheers,
Paul Ibison
|||Thank you very much !
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:#A2HWS1yFHA.464@.TK2MSFTNGP15.phx.gbl...
> It's potentially less processing time - largely depends on the 'width' of
> your table, ie for a table not especially wide then I'd do the drop
method.
> if I had 200 columns, I'd do the column technique.
> Cheers,
> Paul Ibison
>
|||Hi Paul,
Now I am in troubleshooting mode
I dropped the subscriptions to the table to alter, dropped the article (tale
from the publication), altered the table, added the article back to the
publication, and added the subscriptions to the publications. The schema
changes to the table became effective and were replicated to the destination
table (in the subscription), but the snapshot agent failed when I ran it.
The error is: The process could not create file....
I searched on MS and found a couple items (285997, 821480), but I am not
sure.
Any ideas?
The details:
I have 3 publications, each has few articles. There is one subscriber, and
only one subscription to all publications.
I did the following,
EXEC sp_dropsubscription @.publication = 'Pub_2'
, @.article = 'Orders_2'
, @.subscriber = 'SubscriberServer'
, @.destination_db = 'Dest_DB'
EXEC sp_droparticle @.publication = 'Pub_2'
, @.article = 'Orders_2'
ALTER TABLE Orders_2 ALTER COLUMN ....
EXEC sp_addarticle @.publication = 'Pub_2'
, @.article = 'Orders_2'
, @.source_table = 'Orders_2'
, @.destination_table = 'Orders_2'
, @.force_invalidate_snapshot = 1
-- the next is from scripting out the publication (prior to making the
changes)
EXEC sp_addsubscription @.publication = N'Pub_2'
, @.article = N'all'
, @.subscriber = N'SubscriberServer'
, @.destination_db = N'Dest_DB'
, @.sync_type = N'automatic'
, @.update_mode = N'read only'
, @.offloadagent = 0
, @.dts_package_location = N'distributor'
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:#CM4dxyyFHA.2212@.TK2MSFTNGP15.phx.gbl...
> You didn't miss anything - that's a problem we all have faced at some time
> or other . Actually there's another level of iteration you missed out,
as
> your table will be missing the column with the oldname, so if this is to
be
> maintained you have to do the whole process again. There is an alternative
> of dropping the subscriptions to the table, removing the table from the
> publication, altering the table then readding to the publication then
adding
> subscriptions to this table. In this way you can effectively reinitialize
on
> a table basis. All MUCH easier in SQL Server 2005 of course - the Alter
> Table statement will itself be sufficient for most things.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Ramadan,
please check to see if there is an automatic virus-scanner set up. If so,
disable scanning of the repldata folder.
Also, check that there is space in the distribution working folder to create
the file.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul,
it was as simple as a missing folder could be.
Thank you very much for your help.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:Oa2$isDzFHA.1132@.TK2MSFTNGP10.phx.gbl...
> Ramadan,
> please check to see if there is an automatic virus-scanner set up. If so,
> disable scanning of the repldata folder.
> Also, check that there is space in the distribution working folder to
create
> the file.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>

Allow reference to dynamically-created DataColumn from reportHelp?

Hi everyone,

I currently have a strongly-typed dataset that, in code, will expand tables according to relationships in that dataset in order to bind it to my ReportViewer. My problem now is whenever I try to run it, I get an error looking like the following:

An error occurred during local report processing.
The definition of the report 'Main Report' is invalid.
The Value expression for the textbox `textbox15` refers to the field `Parent_FullName`. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data scope.

The reason I'm dynamically creating this dataset on the fly is because I couldn't find how to do a one-to-one relationship (bind a column in one report table to a column in a different dataset table than everything else on that report table). I realize the problem is that in the report's "DataSets" node, it doesn't include a definition of these generated columns... but is there any way around this?

Time is of the essence, but I'd appreciate any help at all!

TIA! =)

Hello,

You may want to look into using calculated fields. These allow you to use expressions as the source of data fields and are used just like regular fields.

For more information about calculated fields:
http://msdn2.microsoft.com/en-us/library/ms156295.aspx

Also, you should find more helpful information if you search for "calculated fields" in this forum and the web.

Ian|||

Hi Ian,

That's exactly what I'm doing. When the dataset is passed in to the form, I have the form automatically expand on its existing relationships and add calculated fields to the datatables at runtime. I can't do this at design-time because I'm using this one form to eventually run 100's of reports--all using various strongly-typed datasets, etc. I think I found the problem, in that it holds the dataset information in the .rdlc file itself and expects ALL the fields to be there. Unless someone knows a way to override it -requiring- all fields to be there(?), I assume I'll have to parse the XML in the .rdlc file at runtime also and add in the XML for all the calculated fields I add as it adds them to the datatables themselves. Any better suggestions are appreciated though. =)

Thanks!

|||Okay, dynamically adding the columns worked for my immediate needs, but that soon passed. :-\ Is there ANY way at all to link different datatables in the same dataset together? I need to take a value from a field in the current scope (i.e. "FamilyID" in the dsMember_Entity dataset) and link it up with a field in another dataset ("FamilyName" in dsMember_Family where dsMember_Entity.FamilyID = dsMember_Family.FamilyID). There are a LOT of fields that will be worked this way, so are there any functions that would allow me to do this using calculated fields? If not, are there any suggestions how one might go about this?|||There are no built-in mechanisims in RS for joining multiple datasets together. Is it possible to join the entity and family table together in the SQL query, so that all fields are avaliable? If not, then you may want to look at writing a custom data extenstion that joins the data tables into one dataset. Or you may want to look into using custom code to return the appropriate value from one dataset given a value from another.

Let me know if you want more information about any of these topics.

Ian|||

Hi Ian,

The tables are joined in to one dataset. We're using strongly-typed datasets, and have all of the relationships already set up. Is there any way to use the existing relationships to get the values I'd like?

|||With the built-in data extensions, you can only access one table per dataset, so the tables need to be joined into one table based on the relationships already set up to be used in a data region. (This is without the use of custom lookup code and secondary datasets.) Did you join the tables on FamilyID, so each row would have the rows from the dsMember_Family table joined with the appropriate rows of the dsMember_Entity table? If so, the fields should be available in the same fields collection.

Also, how are you creating and accessing the strongly typed dataset?

Ian

|||

The dataset has multiple tables, as some of the results are many-to-one we can't just lop all the data onto one table. Foreign key relationships exist in that strongly-typed dataset, however.

We have an internal utility that creates the strongly-typed dataset from a schema it gets from stored procedures. I.e., a "GetEntityData" stored procedure will return several different resultsets--our schema generator checks out these results and writes a strongly-typed dataset based on it... then we just add them into our project and can do any changes we require.

The datasets are accessed in the WinForm's code; we want to use one form for reports instead of one form per report, so we just pass in the dataset we need directly to the form and it handles the binding.

ALL IN ONE SQL STATEMENT?

I am using one SQL (View) to get my sums on various fields. I then use a
second SQL (View) to reference the first View to do my calculations. Is ther
e
a way to combine this all in one SQL View? I want to use the second View and
be able to pass it date range variables from a Web page form.
I am new at this and appreciate your help in advance...
View 1.
SELECT TOP 1000 Site, SUM(NchQty) AS SumOfNchQty, SUM(SchdOpenSecsQty) A
S
SumOfSchdOpenSecsQty, SUM(LogOnSecsQty)
AS SumOfLogOnSecsQty, SUM(InAdherenceSecsQty) AS
SumOfInAdherenceSecsQty, SUM(OutOfAdherenceSecsQty) AS
SumOfOutOfAdherenceSecsQty,
SUM(HoldSecsQty) AS SumOfHoldSecsQty, SUM
(TotalHandleTime) AS SumOfTotalHandleTime, SUM(TalkHoldAvailable) AS
SumOfTalkHoldAvailable,
[Date]
FROM dbo.[National Call Stats]
WHERE ([Date] >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))
GROUP BY Site, [Date]
ORDER BY Site
View 2.
SELECT Site, SumOfInAdherenceSecsQty / (SumOfInAdherenceSecsQty +
SumOfOutOfAdherenceSecsQty) AS Adherence, [Date]
FROM dbo.View1
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1You can use a derived table for this purpose:
SELECT ...
FROM (SELECT ... FROM ...) AS D
Also, if you need to parameterize the query, instead of using a view, you
can use an inline table-valued function:
CREATE FUNCTION dbo.f1
(
@.from_dt AS DATETIME,
@.to_dt AS DATETIME
)
RETURNS TABLE
AS
RETURN
SELECT ... FROM ... WHERE dt >= @.from_dt AND dt < @.to_dt
GO
SELECT ... FROM dbo.f1('20040101', '20050101') AS F;
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"Chamark via webservertalk.com" <u21870@.uwe> wrote in message
news:615ef24bad050@.uwe...
>I am using one SQL (View) to get my sums on various fields. I then use a
> second SQL (View) to reference the first View to do my calculations. Is
> there
> a way to combine this all in one SQL View? I want to use the second View
> and
> be able to pass it date range variables from a Web page form.
> I am new at this and appreciate your help in advance...
> View 1.
> SELECT TOP 1000 Site, SUM(NchQty) AS SumOfNchQty, SUM(SchdOpenSecsQty)
> AS
> SumOfSchdOpenSecsQty, SUM(LogOnSecsQty)
> AS SumOfLogOnSecsQty, SUM(InAdherenceSecsQty) AS
> SumOfInAdherenceSecsQty, SUM(OutOfAdherenceSecsQty) AS
> SumOfOutOfAdherenceSecsQty,
> SUM(HoldSecsQty) AS SumOfHoldSecsQty, SUM
> (TotalHandleTime) AS SumOfTotalHandleTime, SUM(TalkHoldAvailable) AS
> SumOfTalkHoldAvailable,
> [Date]
> FROM dbo.[National Call Stats]
> WHERE ([Date] >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))
> GROUP BY Site, [Date]
> ORDER BY Site
> View 2.
> SELECT Site, SumOfInAdherenceSecsQty / (SumOfInAdherenceSecsQty +
> SumOfOutOfAdherenceSecsQty) AS Adherence, [Date]
> FROM dbo.View1
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||I appreciate your response, I still need a little more clarification, so
thank you in advance for your patience. In your example are you using
referring to SELECT the code in view 2 FROM(SELECT view 1)? I haven't gotten
into creating functions yet, but thanks though.
Itzik Ben-Gan wrote:
>You can use a derived table for this purpose:
>SELECT ...
>FROM (SELECT ... FROM ...) AS D
>Also, if you need to parameterize the query, instead of using a view, you
>can use an inline table-valued function:
>CREATE FUNCTION dbo.f1
>(
> @.from_dt AS DATETIME,
> @.to_dt AS DATETIME
> )
>RETURNS TABLE
>AS
>RETURN
> SELECT ... FROM ... WHERE dt >= @.from_dt AND dt < @.to_dt
>GO
>SELECT ... FROM dbo.f1('20040101', '20050101') AS F;
>
>[quoted text clipped - 25 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||Yes. Something like this:
CREATE VIEW dbo.MyView
AS
SELECT Site, SumOfInAdherenceSecsQty / (SumOfInAdherenceSecsQty +
SumOfOutOfAdherenceSecsQty) AS Adherence, [Date]
FROM
(
SELECT TOP 1000 Site, SUM(NchQty) AS SumOfNchQty, SUM(SchdOpenSecsQty)
AS
SumOfSchdOpenSecsQty, SUM(LogOnSecsQty)
AS SumOfLogOnSecsQty, SUM(InAdherenceSecsQty) AS
SumOfInAdherenceSecsQty, SUM(OutOfAdherenceSecsQty) AS
SumOfOutOfAdherenceSecsQty,
SUM(HoldSecsQty) AS SumOfHoldSecsQty, SUM
(TotalHandleTime) AS SumOfTotalHandleTime, SUM(TalkHoldAvailable) AS
SumOfTalkHoldAvailable,
[Date]
FROM dbo.[National Call Stats]
WHERE ([Date] >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))
GROUP BY Site, [Date]
) AS D
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"Chamark via webservertalk.com" <u21870@.uwe> wrote in message
news:615f8daa4774b@.uwe...
>I appreciate your response, I still need a little more clarification, so
> thank you in advance for your patience. In your example are you using
> referring to SELECT the code in view 2 FROM(SELECT view 1)? I haven't
> gotten
> into creating functions yet, but thanks though.
> Itzik Ben-Gan wrote:
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200606/1