Programming continuous merge replication

Hello,
This projects needs to setup automatically a few MSDE subscribers to a main
Distributor/Publisher.
I am using the ActiveX SQL Merge object to set up the merge replication, but
there are 3 problems:
1) There is not a property to set Continuous mode
2) If I set the ExchangeType to upload, when I browse for the properties
using Enterprise Manager, the “ExchangeType 1” is not present in the Agent
step
3) (Project killer) Assume I manage to set Continuous… After running the
SqlMerge object, the status shows “initializing in progress” but it never
finishes and I have to start again the agent from Enterprise Manager… after
selecting Start (from Enterprise Manager) the synchronization runs as
expected… but I cannot start it by program.
Here are the properties and methods that I am calling after everything is
already registered and ready:
//set up the publisher
m_oSqlMerge.Publisher = m_sDistServerName;
m_oSqlMerge.PublisherSecurityMode =
SQLMERGXLib.SECURITY_TYPE.DB_AUTHENTICATION;
m_oSqlMerge.PublisherDatabase = m_sDbName;
m_oSqlMerge.PublisherPassword = m_sDistPassword;
m_oSqlMerge.PublisherLogin = "sa";
m_oSqlMerge.Publication = m_sDbName;
//set up the distributor
m_oSqlMerge.Distributor = m_sDistServerName;
m_oSqlMerge.DistributorSecurityMode =
SQLMERGXLib.SECURITY_TYPE.NT_AUTHENTICATION;
//set up the subscriber
m_oSqlMerge.Subscriber = oSub.ServerName;
m_oSqlMerge.SubscriberDatabase = oSub.DatabaseName;
m_oSqlMerge.SubscriberDatasourceType =
SQLMERGXLib.DATASOURCE_TYPE.SQL_SERVER;
m_oSqlMerge.SubscriberSecurityMode =
SQLMERGXLib.SECURITY_TYPE.DB_AUTHENTICATION;
m_oSqlMerge.SubscriberLogin = "sa";
m_oSqlMerge.SubscriberPassword = sPassword;
//set up the subscription
m_oSqlMerge.SubscriptionType = SQLMERGXLib.SUBSCRIPTION_TYPE.PULL;
m_oSqlMerge.SynchronizationType =
SQLMERGXLib.SYNCHRONIZATION_TYPE.AUTOMATIC;
m_oSqlMerge.ExchangeType = SQLMERGXLib.EXCHANGE_TYPE.UPLOAD;
ProgressValue = 0;
//Initialize
m_oSqlMerge.Initialize();
m_oSqlMerge.Run();
//m_oSqlMerge.Terminate(); //No need to terminate if continuous
When you create a subscription to a merge publication using Enterprise
Manager, a SQL Server Agent job is created that, when run, synchronizes the
subscription. This job is used whenever you synchronize the subscription from
Enterprise Manager. However, when you start the synchronization
programmatically this agent job is not used and the Merge Agent is started
with the properties you set for the object instance. This is why setting
m_oSqlMerge.ExchangeType = SQLMERGXLib.EXCHANGE_TYPE.UPLOAD doesn’t affect
what you see in Enterprise Manager (which is the agent job). There is no
continuous mode for the Merge Agent, however, you can easily use a timer
control or program a method that calls the Run method at regular intervals to
synchronize the subscription. You should do some inserts at the Subscriber
and run the Merge Agent using your code to see if they make it to the
Publisher. Handling the Status event is a good way to get all of the agent
status and messages. For a good example of how to implement the Status event,
see http://www.winnetmag.com/Article/Art...79/39079.html. (Note you
need to be on at least SP3.)
Best Wishes,
Glenn Gailey [MS]
SQL Server User Education
"This posting is provided "AS IS" with no warranties, and confers no rights"
"uk" wrote:

> Hello,
> This projects needs to setup automatically a few MSDE subscribers to a main
> Distributor/Publisher.
> I am using the ActiveX SQL Merge object to set up the merge replication, but
> there are 3 problems:
> 1) There is not a property to set Continuous mode
> 2) If I set the ExchangeType to upload, when I browse for the properties
> using Enterprise Manager, the “ExchangeType 1” is not present in the Agent
> step
> 3) (Project killer) Assume I manage to set Continuous… After running the
> SqlMerge object, the status shows “initializing in progress” but it never
> finishes and I have to start again the agent from Enterprise Manager… after
> selecting Start (from Enterprise Manager) the synchronization runs as
> expected… but I cannot start it by program.
> Here are the properties and methods that I am calling after everything is
> already registered and ready:
> //set up the publisher
> m_oSqlMerge.Publisher = m_sDistServerName;
> m_oSqlMerge.PublisherSecurityMode =
> SQLMERGXLib.SECURITY_TYPE.DB_AUTHENTICATION;
> m_oSqlMerge.PublisherDatabase = m_sDbName;
> m_oSqlMerge.PublisherPassword = m_sDistPassword;
> m_oSqlMerge.PublisherLogin = "sa";
> m_oSqlMerge.Publication = m_sDbName;
> //set up the distributor
> m_oSqlMerge.Distributor = m_sDistServerName;
> m_oSqlMerge.DistributorSecurityMode =
> SQLMERGXLib.SECURITY_TYPE.NT_AUTHENTICATION;
>
> //set up the subscriber
> m_oSqlMerge.Subscriber = oSub.ServerName;
> m_oSqlMerge.SubscriberDatabase = oSub.DatabaseName;
> m_oSqlMerge.SubscriberDatasourceType =
> SQLMERGXLib.DATASOURCE_TYPE.SQL_SERVER;
> m_oSqlMerge.SubscriberSecurityMode =
> SQLMERGXLib.SECURITY_TYPE.DB_AUTHENTICATION;
> m_oSqlMerge.SubscriberLogin = "sa";
> m_oSqlMerge.SubscriberPassword = sPassword;
> //set up the subscription
> m_oSqlMerge.SubscriptionType = SQLMERGXLib.SUBSCRIPTION_TYPE.PULL;
> m_oSqlMerge.SynchronizationType =
> SQLMERGXLib.SYNCHRONIZATION_TYPE.AUTOMATIC;
> m_oSqlMerge.ExchangeType = SQLMERGXLib.EXCHANGE_TYPE.UPLOAD;
> ProgressValue = 0;
> //Initialize
> m_oSqlMerge.Initialize();
> m_oSqlMerge.Run();
> //m_oSqlMerge.Terminate(); //No need to terminate if continuous
>

Programmatically Set Sever Timeout Using AMO Classes and Objects

Using DSO object model, We use to Specify the Server Timeout for 4 hours as follows:

oServer.Timeout = 14400

But using the AMO Classes and Objects, How will I set the Server timeout programmatically?

Many Thanks

Subhash Subramanyam

hello Subhash,

i think you can specify the Timeout property in connection string. I.e. something like this:

Server server = new Server();

server.Connect("Data Source=localhost; Timeout=14400;");

// do something

server.Disconnect();

(there is also a Timeout property on DataSource object - though that one affects data source connection i think).

hope this helps,

|||

Hi Mary,

Thanks for the reply, I got what I was looking for.

Programmatically altering object code

Has anyone developed a way to programmatically alter the code for multiple
stored procs, functions, etc.?
For example: suppose your database contains hundreds of stored procedures
which access a certain table. Along comes a requirement to rename this
table. It's a lot of tedious work to have to individually open each proc in
QA/EM and change the code. I would like to be able to simply type something
like:
EXEC sp_alter_proc_code 'OldTable', 'NewTable'
And presto, all procs that contain SQL statements that query OldTable are
now pointing to NewTable!
Note that you cannot run an UPDATE statement against syscomment's text field
(e.g., UPDATE syscomments SET text = REPLACE(text, 'OldTable', 'NewTable')),
since this is a computed field.
One solution that I had experimented with was to capture the contents of
syscomment's text column into a variable, run REPLACE against that variable,
and EXEC it. But the problem with that is, a variable can hold only 8000
characters.
I would be interested to hear if anyone has developed a workable solution
for this.>It's a lot of tedious work to have to individually open each proc in
>QA/EM and change the code.
Script all the procs out into a single file as CREATEs (without the
delete!) Open the script in Query Analyzer. Change all the CREATE
PROCs to ALTER PROC. Change all occurances of the old table name to
the new table name - there is a REPLACE ALL feature.
Execute the script.
Roy Harvey
Beacon Falls, CT|||CadeBryant wrote:
> Has anyone developed a way to programmatically alter the code for
> multiple stored procs, functions, etc.?
> For example: suppose your database contains hundreds of stored
> procedures which access a certain table. Along comes a requirement
> to rename this table. It's a lot of tedious work to have to
> individually open each proc in QA/EM and change the code. I would
> like to be able to simply type something like:
> EXEC sp_alter_proc_code 'OldTable', 'NewTable'
> And presto, all procs that contain SQL statements that query OldTable
> are now pointing to NewTable!
> Note that you cannot run an UPDATE statement against syscomment's
> text field (e.g., UPDATE syscomments SET text = REPLACE(text,
> 'OldTable', 'NewTable')), since this is a computed field.
> One solution that I had experimented with was to capture the contents
> of syscomment's text column into a variable, run REPLACE against that
> variable, and EXEC it. But the problem with that is, a variable can
> hold only 8000 characters.
> I would be interested to hear if anyone has developed a workable
> solution for this.
If you are managing your stored procedures using version control
software outside of SQL Server, then you can simply perform a find and
replace. You really should not be managing your procedures using SQL
Server. If you are, I would recommend you extract all procedures and get
them in some form of version control software before beginning this
process.
David Gugick - SQL Server MVP
Quest Software

Programmatic Render of Reports into PDF format

Hi,
Can anybody please convert the following C# code into VB.Net Code.I tried
but its saying Object Reference not set to an instance of an object.
Or give me VB.NET(Web Application, CodeBehind:VB.Net) code to
programmatically render reports into PDF format.
// Prepare report parameter.
ParameterValue[] parameters = new ParameterValue[1];
parameters[0] = new ParameterValue();
parameters[0].Name = "Parameter1";
parameters[0].Value = Request.QueryString["id"];
Thanks
Rajesh YDim Parameters(1) As New ParameterValue
Parameters(0) = New ParameterValue
Parameters(0).Name = "Parameter1"
Parameters(0).Value = Request.QueryString("id")
"Rajesh Yennam" wrote:
> Hi,
> Can anybody please convert the following C# code into VB.Net Code.I tried
> but its saying Object Reference not set to an instance of an object.
> Or give me VB.NET(Web Application, CodeBehind:VB.Net) code to
> programmatically render reports into PDF format.
> // Prepare report parameter.
> ParameterValue[] parameters = new ParameterValue[1];
> parameters[0] = new ParameterValue();
> parameters[0].Name = "Parameter1";
> parameters[0].Value = Request.QueryString["id"];
> Thanks
> Rajesh Y|||Hi Rajesh:
I'd suspect the line of code :
parameters[0].Value = Request.QueryString["id"];
I'd guess you do not have an id parameter in the query string. Double
check the query string.
HTH,
--
Scott
http://www.OdeToCode.com/blogs/scott/
On Fri, 29 Oct 2004 03:43:01 -0700, "Rajesh Yennam"
<RajeshYennam@.discussions.microsoft.com> wrote:
>Hi,
>Can anybody please convert the following C# code into VB.Net Code.I tried
>but its saying Object Reference not set to an instance of an object.
>Or give me VB.NET(Web Application, CodeBehind:VB.Net) code to
>programmatically render reports into PDF format.
>// Prepare report parameter.
> ParameterValue[] parameters = new ParameterValue[1];
> parameters[0] = new ParameterValue();
> parameters[0].Name = "Parameter1";
> parameters[0].Value = Request.QueryString["id"];
>Thanks
>Rajesh Y

Programmatic "Script Table as Create to..." in T-SQL?

Is there a way to write a T-SQL query against the database object model to create the same output as you would get when you right-click the object in Management Studio and select 'Script table as Create to.."?

Or better yet, is there a way to retrieve the DDL that was used to create a table using a T-SQL query?

Thanks in advance,
-Preston M. Price

Perhaps something like this:

SELECT text

FROM sys.syscomments

WHERE id = object_id( 'MyStoredProcedure' )

|||

There is no way to query DDL from the database (beside the Routine_definition your get from INFORMATION_SCHEMA.Routines table or the sysobejcts as Arnie pointed out), you will have to either write your own way to do this or you can use API of SMO to do the stuff with the scripter object.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Programmatic "Script Table as Create to..." in T-SQL?

Is there a way to write a T-SQL query against the database object model to create the same output as you would get when you right-click the object in Management Studio and select 'Script table as Create to.."?

Or better yet, is there a way to retrieve the DDL that was used to create a table using a T-SQL query?

Thanks in advance,
-Preston M. Price

Perhaps something like this:

SELECT text

FROM sys.syscomments

WHERE id = object_id( 'MyStoredProcedure' )

|||

There is no way to query DDL from the database (beside the Routine_definition your get from INFORMATION_SCHEMA.Routines table or the sysobejcts as Arnie pointed out), you will have to either write your own way to do this or you can use API of SMO to do the stuff with the scripter object.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Programmaing with SSIS Object model

Hi there,

Can anyone point me to some sample source codes or any articles that describes how I can programmatically create a package which will import data from a flatfile (csv) to Sql server database.

I know there is some example that describes exporting data from sql server to flatfiles. Anyway I have failed to accomplish my goal by following those examples.

If anyone have a code snippet to do that please help me with that.

Thanks in advance

Moim Hossain

If you want, post the code you have already to build the SSIS package (or a link to it). This response is not the code snippet you're looking for, but it sounds like the package builder code you already have (from the other post), is not far from working.|||

jaegd ,

Sorry I did not understand. Should I post my code to you?

I have already posted my code here into another posting. you can take a look on my code here http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1073656&SiteID=1

Can you tell me where I am going wrong?

Thanks

Moim

Programatically Evaluating SSIS Expression

Is there an object in the DTS object model that will allow me to evaluate an SSIS expression? I am trying to build a custom task that will require re-evaluation of an expression multiple times within the execute method and I can't seem to find a way to do this.

Thanks,

Adam

Add a reference to Microsoft.DataTransformationServices.Controls

Use the Microsoft.SqlServer.Dts.Runtime.Wrapper.ExpressionEvaluatorClass class. You will want to use DTSInfoEvents to capture error details when calling Evaluate or Validate. Pass the events to the Events property of the ExpressionEvaluatorClass.

Take a look at the File Watcher Task (http://www.sqlis.com/) for an example of this in action, just set an expression through the task UI to see the Expression Editor Dialog we have built in action. It use the ExpressionEvaluatorClass behind the scenes to provide the evaluation functionality.

Not documented, so not supported, but it works.|||As Darren said, this is not documented and will not be supported by Microsoft. It could change at anytime and break your component if you use it.
User beware.|||

I have logged a bug to document and publicly expose this, but it was on Beta Place, so if someone wants to do the Product Feedback thing I'll vote for it.