RMO Replication problems with Pull Subscriptions

Hello,

I have the following situation. I have a single publication on my publisher Server. This publication is created using SQL Server Manager. Snapshot is created completly. Now I want several Pull subscriptions from several machines to work with this publication (One subscription per machine). I'm creating these subscription using RMO. I'm synchronizing data using RMO again. My code workflow is:
Syncronize -> success -> do nothing
-> fail -> Check if everything with Publisher and Publication is ok -> Generate Snapshot -> Create Subsscription -> Sync again. (I tried to eliminate Generate Snapshot step but couldn't because I receive error that I must rerun Snapshot Generation.)

When I tested my code per single machine it's working. Next test I tried was to run my program on 2 machines simultaneously.

The result is:

> System.Data.SqlClient.SqlException: Another snapshot agent for the subscription(s) is running or the server is working on a previous request by the same agent.

or:

> System.Data.SqlClient.SqlException: Unable to acquire the replication merge administrative application lock for database 'XXX'. This could be due an active snapshot running while the schema change (DDL) or the administrative proc change was attempted.
Replication merge admin stored procedure 'sp_changemergepublication' failed for publication 'YYY'. This could be due an active snapshot running while the admin proc was called.

The problem is obvious but because I'm new to the replication I'm not sure if I did general mistake in what I want to achieve. Any advices how can I fix my problem will be highly appreciated.Snapshot need not be run for every subscription. Eliminate that step from your list. Run it once, you should be good.|||Thank you for reply.

To be honest I thought something similar. In my first attempts I generated snapshot through SQL Server Manager once. But every time I ran synchronization after I received error that I must rerun Snapshot Generation. I can post the exact error tomorrow. The facts I can post now for this case are that publication properties: SnapshotAgentExists returns true, but SnapshotAvailable returns false.

Here is the exact error:

You must rerun snapshot because current snapshot files are obsolete.
at Microsoft.SqlServer.Replication.MergeSynchronizationAgent.Initialize()
at Microsoft.SqlServer.Replication.MergeSynchronizationAgent.Synchronize()|||Thank you a lot once again.
I did your way and now It seems it's working. I'm waiting for results of the extended testing tomorrow. I found the reason why I needed to rerun snapshot every time (Leaked code from first implementations where I needed to setup publisher and publication. The code modifies publication). So now snapshot generation is removed from code and my task looks complete.

Returning TSQL Results straight to file.

Hi All
I'm creating a stored procedure to pull a report every morning for my
manager. The thing is she just wants to double click and open a file. I
want to run a schedule every morning before she gets in, I want the
stored procedure to write it directly to the file. Is this possible or
is there another way to do it?
Thanks
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!
You could possibly do something with xp_cmdshell, but it probably wouldn't
be a great idea (for all sorts of reasons relating to security,
maintainability, etc.)
How are you currently calling the SProc - presumably there's some sort of
client application? Could you adapt that?
If not, your best bet might be to create a simple client app (even just a
simple VB Script app) that connects to the database, runs the SProc, and
writes the results to a file.
Here's a simple example that executes a SPROC called GetReport (which
returns a FOR XML query):
Const DBGUID_SQL = "{C8B522D7-5CF3-11CE-ADE5-00AA0044773D}"
Const adExecuteStream = 1024
Const adCmdStoredProc = 4
Dim conn
Set conn = CreateObject("ADODB.Connection")
conn.Provider = "SQLXMLOLEDB.3.0"
conn.ConnectionString = "DATA PROVIDER=SQLOLEDB;" & _
"SERVER=(local);DATABASE=northwind;INTEGRATED SECURITY=sspi;"
conn.Open
Dim cmd
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
'Set the dialect
cmd.Dialect = DBGUID_SQL
'Create DOMDocument object for results.
Dim xmlDoc
Set xmlDoc= CreateObject("MSXML2.DOMDocument")
'Assign the output stream.
cmd.Properties("Output Stream") = xmlDoc
'Specify the command
cmd.CommandText = "GetReport"
cmd.CommandType = adCmdStoredProc
'Specify the root tag
cmd.Properties("xml root") = "ReportData"
'Execute the command returning the data to the DOM
cmd.Execute, , adExecuteStream
'Save the report
xmlDoc.Save "C:\Report.xml"
MsgBox "Report Saved!"
Cheers,
Graeme
Graeme Malcolm
Principal Technologist
Content Master Ltd.
"Da Olive" <doliver@.mailbox.co.za> wrote in message
news:eTM2xElEEHA.3748@.TK2MSFTNGP11.phx.gbl...
> Hi All
> I'm creating a stored procedure to pull a report every morning for my
> manager. The thing is she just wants to double click and open a file. I
> want to run a schedule every morning before she gets in, I want the
> stored procedure to write it directly to the file. Is this possible or
> is there another way to do it?
> Thanks
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
|||"Da Olive" <doliver@.mailbox.co.za> wrote in message
news:eTM2xElEEHA.3748@.TK2MSFTNGP11.phx.gbl...
> Hi All
> I'm creating a stored procedure to pull a report every morning for my
> manager. The thing is she just wants to double click and open a file. I
> want to run a schedule every morning before she gets in, I want the
> stored procedure to write it directly to the file. Is this possible or
> is there another way to do it?
Write a DTS script and schedule it to run every morning:
http://www.sqlxml.org/faqs.aspx?faq=10
Bryant