Adding a subscriber which already has the schema and data

Hello,
I use a dynamically filtered publication.
I want to add a subscriber that already has the schema and data. So I create
a new subscription and set the option 'No, the Subscriber already has the
schema and data'.
When I start the new merge agent for the first time, it replicates all
historical data changes since the creation of the publication.
I don't want this behaviour, because the subscriber has already updated data.
I want the merge agent to replicate all changes since the creation of the
subscription.
Does someone has ideas?
thanks in advance, Marco
The merge replication process requires this metadata in order to track
changes. If you run a sp_mergemetadataretentioncleanup this could clean up
your metadata on your publisher and all subscriber, so you might be able to
start with metadata replication tables with no row in them.
You need sp 1 and above for this proc.
"Marco Broenink" <MarcoBroenink@.discussions.microsoft.com> wrote in message
news:491BE7CE-2FD2-4885-908B-355460FAF06C@.microsoft.com...
> Hello,
> I use a dynamically filtered publication.
> I want to add a subscriber that already has the schema and data. So I
create
> a new subscription and set the option 'No, the Subscriber already has the
> schema and data'.
> When I start the new merge agent for the first time, it replicates all
> historical data changes since the creation of the publication.
> I don't want this behaviour, because the subscriber has already updated
data.
> I want the merge agent to replicate all changes since the creation of the
> subscription.
> Does someone has ideas?
> thanks in advance, Marco
>
>

Adding a column to a table dynamically against a database?

I have the folowing databases DB1,DB2,DB3,D4,DB5......

I have to loop through each of the databases and find out if the database has a tablename with the word 'Documents'( like 'tbdocuments' or 'tbemployeedocuments' and so on.....)

If the tablename having the word 'Documents' is found in that database i have to add a column named 'IsValid varchar(100)' against that table in that database and there can be more than 1 'Documents' table in a database.

can someone show me the script to do it?

Thanks.

This script should do the trick, the specific line that does the ALTER TABLE is commented you would have to remove the comment. Additionaly, I also added a commented line that lets you specify which databases to search, if this line is documented it will search all databases except system databases. IMPORTANT: The script is written for SQL 2005, if you need it to work in 2000 just reply on the thread the change should be fairly simple.

 -------------------------------------------

DECLARE ALL_DATABASESCURSORREAD_ONLYFOR SELECT nameFROM SYS.DATABASESWHERE name NOT IN ('MASTER','MSDB','MODEL','TEMPDB','DISTRIBUTION')
--ANDname IN ('DB1','DB2','DB3') -- UNCOMMENT THIS LINE TO SPECIFY SPECIFIC DATABASES OTHERWISE ALLDECLARE @.DB_NAMENVARCHAR(255)DECLARE @.TABLE_NAMENVARCHAR(255)-- OTHERDECLARE @.SQL1NVARCHAR(MAX)DECLARE @.SQL2NVARCHAR(MAX)-- TEMPORARY TABLE TO HOLD ALL TABLES, FOR A DATABASEDECLARE @.TEMPORARY_SYS_TABLESTABLE (TABLE_NAMENVARCHAR(255))OPEN ALL_DATABASESFETCH NEXT FROM ALL_DATABASESINTO @.DB_NAMEWHILE (@.@.fetch_status <> -1)BEGINIF (@.@.fetch_status <> -2)BEGIN-- OUTPUT DATABASE NAMEPRINT'BEG------ (' + @.DB_NAME + ') --------'-- BUILD QUERY FOR TABLESSET @.SQL1 = 'SELECT name FROM ['+ @.DB_NAME +'].[SYS].[TABLES]WHERE NAMELIKE''%DOCUMENTS%'''-- TRUNCATEDELETE @.TEMPORARY_SYS_TABLES-- LOAD WITH DATAINSERT @.TEMPORARY_SYS_TABLES(TABLE_NAME)EXECUTE SP_EXECUTESQL @.SQL1DECLARE ALL_MMR_TABLES CURSORREAD_ONLYFOR SELECT TABLE_NAMEFROM @.TEMPORARY_SYS_TABLESOPEN ALL_MMR_TABLESFETCH NEXT FROM ALL_MMR_TABLES INTO @.TABLE_NAMEWHILE (@.@.fetch_status <> -1)BEGINIF (@.@.fetch_status <> -2)BEGIN-- BUILD SQLSET @.SQL2 = 'USE' + @.DB_NAME + ';GO;ALTER TABLE' + @.TABLE_NAME + 'ADD IS_VALIDNVARCHAR(100);'-- PRINTPRINT @.SQL2-- EXECUTE-- EXECUTE SP_EXECUTESQL @.SQL2 -- REMOVE THIS COMMENT TO PERFORM THE ADD OF THE COLUMNENDFETCH NEXT FROM ALL_MMR_TABLES INTO @.TABLE_NAMEENDCLOSE ALL_MMR_TABLESDEALLOCATE ALL_MMR_TABLES-- OUTPUT DATABASE NAMEPRINT 'END------ (' + @.DB_NAME + ') --------'FETCH NEXT FROM ALL_DATABASESINTO @.DB_NAMEENDENDCLOSE ALL_DATABASESDEALLOCATE ALL_DATABASES

-------------------------------------------

Hope this helps,

|||

Thanks for replying.

I am using SQL Server 2000.

I tried executing the statement as per your script above, in master database

SET @.SQL2 = 'USE' + @.DB_NAME + ';GO;ALTER TABLE' + @.TABLE_NAME + 'ADD IS_VALIDNVARCHAR(100);'
PRINT @.SQL2
EXECUTE SP_EXECUTESQL @.SQL2

and it is throwing this error:

Msg 170, Level 15, State 1, Line 1

Line 1: Incorrect syntax near 'GO'.

|||

Apparently all that is required is that you remove the GO. The documentation for the Stored Procedure SP_EXECUTESQL says it can only operate within a single BATCH, I guess that is why GO is not allowed.

SET @.SQL2 = 'USE' + @.DB_NAME + ';ALTER TABLE' + @.TABLE_NAME + 'ADD IS_VALIDNVARCHAR(100);'

Full Script for SQL 2000, and you should definitely run from 'master' database.

___________________________________________________________________________________________

DECLARE ALL_DATABASESCURSORREAD_ONLYFOR SELECT nameFROM MASTER.DBO.SYSDATABASES--WHERE name NOT IN ('MASTER','MSDB','MODEL','TEMPDB','DISTRIBUTION')--AND name IN ('DB1','DB2','DB3') -- UNCOMMENT THIS LINE TO SPECIFY SPECIFIC DATABASES OTHERWISE ALLDECLARE @.DB_NAMENVARCHAR(255)DECLARE @.TABLE_NAMENVARCHAR(255)-- OTHERDECLARE @.SQL1NVARCHAR(MAX)DECLARE @.SQL2NVARCHAR(MAX)-- TEMPORARY TABLE TO HOLD ALL TABLES, FOR A DATABASEDECLARE @.TEMPORARY_SYS_TABLESTABLE (TABLE_NAMENVARCHAR(255))OPEN ALL_DATABASESFETCH NEXT FROM ALL_DATABASESINTO @.DB_NAMEWHILE (@.@.fetch_status <> -1)BEGINIF (@.@.fetch_status <> -2)BEGIN-- OUTPUT DATABASE NAMEPRINT'BEG------ (' + @.DB_NAME + ') --------'-- BUILD QUERY FOR TABLESSET @.SQL1 = 'SELECT TABLE_NAMEFROM ['+ @.DB_NAME +'].[INFORMATION_SCHEMA].[TABLES]WHERE TABLE_NAMELIKE''%DOCUMENTS%'''-- TRUNCATEDELETE @.TEMPORARY_SYS_TABLES-- LOAD WITH DATAINSERT @.TEMPORARY_SYS_TABLES(TABLE_NAME)EXECUTE SP_EXECUTESQL @.SQL1DECLARE ALL_MMR_TABLES CURSORREAD_ONLYFOR SELECT TABLE_NAMEFROM @.TEMPORARY_SYS_TABLESOPEN ALL_MMR_TABLESFETCH NEXT FROM ALL_MMR_TABLES INTO @.TABLE_NAMEWHILE (@.@.fetch_status <> -1)BEGINIF (@.@.fetch_status <> -2)BEGIN-- BUILD SQLSET @.SQL2 = 'USE' + @.DB_NAME + ';ALTER TABLE' + @.TABLE_NAME + 'ADD IS_VALIDNVARCHAR(100);'-- PRINTPRINT @.SQL2-- EXECUTE-- EXECUTE SP_EXECUTESQL @.SQL2 -- REMOVE THIS COMMENT TO PERFORM THE ADD OF THE COLUMNENDFETCH NEXT FROM ALL_MMR_TABLES INTO @.TABLE_NAMEENDCLOSE ALL_MMR_TABLESDEALLOCATE ALL_MMR_TABLES-- OUTPUT DATABASE NAMEPRINT 'END------ (' + @.DB_NAME + ') --------'FETCH NEXT FROM ALL_DATABASESINTO @.DB_NAMEENDENDCLOSE ALL_DATABASESDEALLOCATE ALL_DATABASES-------------------------------------------

Hope this helps,

|||

Although the script complies successfully, changes do not take place in the desried table.

Any clue?

|||

Just remember to remove the comment from the following line of code.

-- EXECUTE SP_EXECUTESQL @.SQL2 -- REMOVE THIS COMMENT TO PERFORM THE ADD OF THE COLUMN

It should look like this.

EXECUTE SP_EXECUTESQL @.SQL2 -- REMOVE THIS COMMENT TO PERFORM THE ADD OF THE COLUMN

|||

Thanks!

sql

Add/Remove Column/Row Groups dynamically in Reporting Services

Hi
Presently I'm working with OLAP and Reporting Services. In which users
are allowed to drag and drop the dimensions and the measures from the
list available for the cube , On droping of the dimension/measure on
Row/Column. I need to generate the matrix. Is it possible in RS, If it
menas, give me idea on this else notes for how to proceed with this.
Regards
Santhimurthy DessingouIf you're using SQL Server 2005, you could probably do this with the new
Report Builder tool, you just need to create a Report Model with all the
dimensions and measures you need.
Alternatively, try searching for a thread called "RS/MDX - Column Parameter
/ Field name in dataset problem"
Or if you use Google groups, you find it at
http://groups.google.no/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/642b5c0d703335b6/568564e89b6bc038?lnk=st&q=Re%3A+RS%2FMDX+-+Column+Parameter+%2F+Field+name+in+dataset+problem&rnum=1&hl=no#568564e89b6bc038
It's a suggestion on how to create a dynamic report. It's limited by a given
number of columns, but it might be possible to elaborate on the ideas to get
what you want.
Kaisa M. Lindahl Lervik
<santhimurthyd@.gmail.com> wrote in message
news:1140600388.351554.135920@.g44g2000cwa.googlegroups.com...
> Hi
> Presently I'm working with OLAP and Reporting Services. In which users
> are allowed to drag and drop the dimensions and the measures from the
> list available for the cube , On droping of the dimension/measure on
> Row/Column. I need to generate the matrix. Is it possible in RS, If it
> menas, give me idea on this else notes for how to proceed with this.
> Regards
> Santhimurthy Dessingou
>

Add filters dynamically

Hi,

I am using a ReportViewer control on the windows form to connect to Report Server, process the report remotely (ServerReport) and render the report in PDF format.

The user wants to add some fiter(criteria) each time before the report is rendered. The filter (criteria) is generated separately. The original content of RDL should not be changed. I know, Report Builder is an option available. But, the end-user doesnt want to use Report builder to design the report.

I need to know, is there anyway I can retrieve the dataset->commandtext of the report(published on REport Server), edit and set it back dynamically, before rendering to PDF or any API, which can allow to add filters programmatically.

Thanks in advance.

You can pass it as report parameter. But is must be done carefully to prevent SQL injections)

Add filters dynamically

Hi,

I am using a ReportViewer control on the windows form to connect to Report Server, process the report remotely (ServerReport) and render the report in PDF format.

The user wants to add some fiter(criteria) each time before the report is rendered. The filter (criteria) is generated separately. The original content of RDL should not be changed. I know, Report Builder is an option available. But, the end-user doesnt want to use Report builder to design the report.

I need to know, is there anyway I can retrieve the dataset->commandtext of the report(published on REport Server), edit and set it back dynamically, before rendering to PDF or any API, which can allow to add filters programmatically.

Thanks in advance.

You can pass it as report parameter. But is must be done carefully to prevent SQL injections)

add entities to report model programmatically ?

Hi Friends
is it possible to add entities to a report model dynamically ,probably thru SOAP api ?
we've c# app and we want to be able to add entities dynamically .
Thanks for the helpNo, the only API's are for updating the entire model.|||Thanks BOB.
thats what i figured out finally. BUT i found a way of adding attributes to existing entities .am reading .smdl file using a c# program and adding a attribute under a specific entity.
if i re-publish the model everything worked fine as normal.

i need to do this as our clients want to report on "user defined tables" which we're not aware of when creating report model!
i have not tested my approach enough and i appreciate ur advise here. is there any better way of doing this.
Your advise really appreciated.
Thanks again|||That looks like your best option.|||

Thanks for the reply Bob.

Actually i was not sure before, now after ur post i've got some confidence to try this solution in real world. Thanks again :)

Add dynamically series in Dundas chart

Hi!
Does anybody know how to add dynamically at run-time a new series in
Dundas chart and bind it to the data in dataset?
For example, I have the following dataset:
LineType PoinNo PointX PointY
1 1 10 20
1 2 15 40
...
1 20 100 30
2 1 5 15
...
2 40 30 20
and so on.
I want Dundas to draw me one line (Series) per LineType value. Of
couse, I can filter out points by LineType for each Series (specifying
its values), but at design time I dont know how many LineTypes there
will bee in the dataset.
One solution is to define a lot of series at design type and show/hide
them at run-time, but it is nogood enough.
Does anybody have any idea?
P.S. Of course it is Dundas chart .NET for Reporting Services version
Thank you in advance!Drop the value you wish to have as the Y Value in the Data Values drop
zone (top). For the series groupings, drop the LineType field in the
Series Grouping field (right) -- this will create a new series defined
by the Y Value field for each new LineType.
Open the chart Values editor associated with the YValue to change the
chart type from the default Column Type to a Line or another series
type. You may also define the XValue for the data or drag the XValue
field in the Category Grouping landing zone (bottom).
If you have further questions, please send an email to
support@.dundas.com.
Thanks
Andrew Bryan
Dundas Software
vladsm@.rambler.ru wrote:
> Hi!
> Does anybody know how to add dynamically at run-time a new series in
> Dundas chart and bind it to the data in dataset?
> For example, I have the following dataset:
> LineType PoinNo PointX PointY
> 1 1 10 20
> 1 2 15 40
> ...
> 1 20 100 30
> 2 1 5 15
> ...
> 2 40 30 20
> and so on.
> I want Dundas to draw me one line (Series) per LineType value. Of
> couse, I can filter out points by LineType for each Series (specifying
> its values), but at design time I dont know how many LineTypes there
> will bee in the dataset.
> One solution is to define a lot of series at design type and show/hide
> them at run-time, but it is nogood enough.
> Does anybody have any idea?
> P.S. Of course it is Dundas chart .NET for Reporting Services version
> Thank you in advance!

Add database table

Hello everyone, I was wondering if there is a way to dynamically add a table to a SQL database off of a button click coded in C#. I'm not asking for all of the code, I just need a small push in the right direction.

Thank you,
Ryan

You can write up some dynamic SQL in a proc passing the table name as parameter and create the table. But I would be concerned about letting users create objects on the fly. Can you explain more about the business logic? Do you have any process in place to clean up such tables on a periodic basis?

|||

Ok, well I am making a quiz engine and with this I want each client that makes an account on the website to be able to create, edit, and deploy their own quiz on my site. By allowing users to create their quiz in separate tables they will be able to easily edit their quiz because of the separate tables. If I mashed many quizzes into one table I feel it would get too unorganized. The number of quizzes/tables each user can make will be limited so a client cannot easily flood the database. An admin account will be able to edit and or delete every quiz made by any user as well.

Would you mind elaborating more on writing dynamic SQL like you said above. Maybe some code is required here because I'm not very experienced in anything SQL. I understand what you said, but I have no idea where to start.

|||

That doesnt sound like a very good design. You can create the records in one table and identify them by userId or username so when you have to pull out the records you can do so by username. Otherwise there will be LOT of dynamic SQL and it will make the server crawl.. because you dont know what table to select from.

This is an important read before you think of dynamic SQL:The Curse and Blessings of Dynamic SQL

|||

Thank you for pointing out the flaws in my design, I have taken your advice and will be calling rows based on each username. I've already had issues with my new way but I'll make a new post in the correct section. Thank you for the read as well.

Add a column to table from stored proc

Hello all,

I want to alter a table in a stored proc to add a column dynamically. I am passing the name of the new column to the stored proc param. Does anyone know the syntax for this? Here is the code:

CREATE PROCEDURE procAddNewOptionType

-- new option type parameter

@.OptionName varchar(50)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert new option type into list of types

INSERTINTO dbo.tblClientOptionList(strOptionName)VALUES(@.OptionName);

-- alter client options table to add new column passed to proc as @.OptionName

ALTERTABLE dbo.tblClientOptions ADD @.OptionName BIT;

RETURN 1

END

Thx,

Jason

Hi jason,

You'd have to execute the ALTER TABLE statements dynamically since your column depends on the OptionName being a variable.

declare @.sql nvarchar(100)

SET @.sql = 'alter table dbo.tblClientOptions add ' + @.optionName + ' BIT'
EXEC sp_executesql @.sql

Although, you'd have to take note possible permissions priviledge given by your DBA(ie. they might revoke ALTER TABLE permission)

hth|||

Thank you very much. That works perfectly!

Jason

Add a column to table from stored proc

Hello all,

I want to alter a table in a stored proc to add a column dynamically. I am passing the name of the new column to the stored proc param. Does anyone know the syntax for this? Here is the code:

CREATE PROCEDURE procAddNewOptionType

-- new option type parameter

@.OptionName varchar(50)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert new option type into list of types

INSERTINTO dbo.tblClientOptionList(strOptionName)VALUES(@.OptionName);

-- alter client options table to add new column passed to proc as @.OptionName

ALTERTABLE dbo.tblClientOptions ADD @.OptionName BIT;

RETURN 1

END

Thx,

Jason

Hi jason,

You'd have to execute the ALTER TABLE statements dynamically since your column depends on the OptionName being a variable.

declare @.sql nvarchar(100)

SET @.sql = 'alter table dbo.tblClientOptions add ' + @.optionName + ' BIT'
EXEC sp_executesql @.sql

Although, you'd have to take note possible permissions priviledge given by your DBA(ie. they might revoke ALTER TABLE permission)

hth|||

Thank you very much. That works perfectly!

Jason