Pushing 2000 merge publication to 2005 server

I am attempting to push a SQL 2000 publication to a SQL 2005 server. I
realize that Microsoft does not recommend this, but I haven't found anything
that says that it can not be done.
When I try to register the SQL 2005 server in my Enterprise Manager I get
"To connect to this server you must use SQL Server Management Studio or SQL
Server Management Objects (SMO)".
Any ideas on how I can get around this?
I have over 100 locations that I run merge replication with.
Thanks.
Gary
Connect to all servers through SSMS (SQL Server Management Studio).
Hilary Cotter
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
"gmac2" <gmac2@.discussions.microsoft.com> wrote in message
news:8F874E11-E1D1-4A6C-86EE-07C2FCE1DB35@.microsoft.com...
> I am attempting to push a SQL 2000 publication to a SQL 2005 server. I
> realize that Microsoft does not recommend this, but I haven't found
> anything
> that says that it can not be done.
> When I try to register the SQL 2005 server in my Enterprise Manager I get
> "To connect to this server you must use SQL Server Management Studio or
> SQL
> Server Management Objects (SMO)".
> Any ideas on how I can get around this?
> I have over 100 locations that I run merge replication with.
> Thanks.
> Gary
|||I am trying to do the push from the SQL 2000 end, I do not have access to the
2005 server as it belongs to a vendor. Any other ways around this?
I did get around registering the server throught publishing the database (I
had the other party create an empty database on their end) but then when I
try to push the subscriotion it can not deliver the snapshot as it gets the
sp_Msupdatesysmergeartciles has too many arguments specified (8144 error).
Thanks,
Gary
"gmac2" wrote:

> I am attempting to push a SQL 2000 publication to a SQL 2005 server. I
> realize that Microsoft does not recommend this, but I haven't found anything
> that says that it can not be done.
> When I try to register the SQL 2005 server in my Enterprise Manager I get
> "To connect to this server you must use SQL Server Management Studio or SQL
> Server Management Objects (SMO)".
> Any ideas on how I can get around this?
> I have over 100 locations that I run merge replication with.
> Thanks.
> Gary

push data to crystal report asp.net

I am attempting to use the push model for a crystal report from a asp.net project.
I am having two problems. First when I attempt to use a sql command I am able to create the report
but when I attempt to push the data to it I get a login failed error. When I correct this I get the original
data that was used to format the report not the modified data that I am attempting to push to it.
Here is the code.

Sub BindReport2()
Dim oRpt As New CrystalReport1

Dim myConnection As New SqlClient.SqlConnection
myConnection.ConnectionString = "Server=WOODS1;Database=WEB_TEST;User ID=WebRep;Password=webrep;Trusted_Connection=False"
Dim MyCommand As New SqlClient.SqlCommand
MyCommand.Connection = myConnection
MyCommand.CommandText = "select fname, lname, address1, city, state " & _
"from T1, T2 " & _
"where T1.empid = T2.empid and state = 'New York' "

MyCommand.CommandType = CommandType.Text
Dim MyDA As New SqlClient.SqlDataAdapter
MyDA.SelectCommand = MyCommand
Dim myDS As New DataSet
'This is our DataSet created at Design Time
MyDA.Fill(myDS, "Command")
'You have to use the same name as that of your Dataset that you created during design time

'oRpt.Database.Tables.Item("Command").SetDataSource(myDS)
' This is the Crystal Report file created at Design Time
oRpt.SetDataSource(myDS)
' Set the SetDataSource property of the Report to the Dataset
CrystalReportViewer1.ReportSource = oRpt
'oRpt.SetDatabaseLogon("user","password","server","database")
oRpt.SetDatabaseLogon("WebRep", "webrep", "WOODS1", "web_test")
CrystalReportViewer1.RefreshReport()

' Set the Crystal Report Viewer's property to the oRpt Report object that we created
End Sub

My Second issue is similar though involves using a dataset. I am able to push the data to the report if
my dataset only contains one table if it contains more than one I get no data returned to the report.
Here is the code.

Sub BindRep()
Dim oRpt As New CrystalReport2
Dim myConnection As New SqlClient.SqlConnection
myConnection.ConnectionString = "Server=WOODS1;Database=WEB_TEST;User ID=WebRep;Password=webrep;Trusted_Connection=False"

Dim strSQL As String = "select fname, lname, address1, city, state " & _
"from T1, T2 " & _
"where T1.empid = T2.empid and state = 'New York' "

Dim objAdapter As New SqlClient.SqlDataAdapter(strSQL, myConnection)

Dim objDataSet As New Dataset1
objAdapter.Fill(objDataSet, "T1, T2")

oRpt.SetDataSource(objDataSet)
' Set the SetDataSource property of the Report to the Dataset
CrystalReportViewer1.ReportSource = oRpt
End Sub

I have been able with the above code using a dataset that contains only one table to push the correct data
to the report. Any help on this issue whould be greatly appreciated.I dunno what database you're using, but I would have thought your problem occurs becuse of your SQL syntax/structure, where's your:
AS T1 INNER JOIN T2 ON (T1.empid = T2.empid )
???

If you don't join them on at least one field, the SQL parser can't resolve the WHERE clause.

Davesql

Pulling data across multiple tables.

I am attempting to pull all users over the age of 30 from a table. I want the results to be returned off of their membership status and recent activity. Their membership status is stored in a seperate table (Payment). Some users have more than one membership status record, while others might not have any at all.

LEFT JOIN seems like a possible solution, however, if a user has more than one membership status record, it is returning multiple results for that user (when I only want 1 result for that user).

Here is the code that I have so far.

SELECT Users.UserID FROM Users LEFT JOIN Payment ON Users.UserID = Payment.UserID AND Users.Age > 30 AND Payment.PaymentExpirationDate > '3/28/2004' ORDER BY Payment.MembershipNumber DESC, Users.LastActive DESC

Payment.MembershipNumber - INT Field. This is the membership status for the user, if the user has any membership records.
Users.LastActive - Date/Time Field. This is the recent active for the user.

The data is being returned right now is:
UserID
=====
3
8
12
12
12
9
9
1
... and so on, when it should look like ...
UserID
=====
3
8
12
9
1

I hope this makes sense. Thanks for your time.

Jamesif a user has more than one membership status record, which one do you want?|||I need the first membership status record that has a PaymentExpirationDate closest to '3/28/2004'.|||select U.UserID
, U.lastActive
, P.PaymentExpirationDate
from Users U
left
join Payment P
on U.UserID
= P.UserID
and P.PaymentExpirationDate
> '3/28/2004'
where U.Age > 30
and P.PaymentExpirationDate
= ( select min(PaymentExpirationDate)
from Payment
where UserID = P.UserID
and PaymentExpirationDate
> '3/28/2004' )
order
by U.LastActive desc

Pull Merge replication between two SQL Server machines in different, but trusted domains

Hi,
We are attempting to setup Pull Merge replication between two SQL Server
(SQL 2K sp3a, Windows Authentication) machines in different domains. We have
been able to get Pull Merge replication to work within the same domain.
While we are running Win2k on both machines, two one way trusts were setup
between them. The server are linked to each other, but cannot initiate the
replication agents. The message we get is:
SQL Server Agent could not access the replication agent. Use the DCOMCNFG
utility to confirm that the SQL Server Agent Windows account has permissions
to launch the replication agent. The step failed.
I did not spot any in DCOMCNFG, but I am not familiar with it. Any
suggections?
Tia
Paul
Paul,
here is an MSDN article which shows how to enable the merge agent to be run
remotely:
http://msdn.microsoft.com/library/de...us/howtosql/ht
_replctem_6c1l.asp
HTH,
Paul Ibison
|||I have a feeling that the error message may be misleading me away from the
true problem. It appears that DCOMCNFG is used for running agents remotely.
I am looking at having the push replication run at the distributor and pull
replications to run at the subscriber. Besides, I couldn't get it to work by
using DCOMCNFG.
I want to have one merge publication which is pushed to subscribers which
will not be intentionally disconnected from the network. And, the same
publication pulled to subscribers which will be removed from the network for
possibly weeks. This currently works as long as long as all the servers are
in the same domain. The problem is that the pull subscribers will be in
different domains with trust relationship setup between them and the
distributor.
When I take the existing scripts that work when all of the server are in the
same domain and run them with the pull subscriber in different domains I get
the error:
SQL Server Agent could not access the replication agent. Use the DCOMCNFG
utility to confirm that the SQL Server Agent Windows account has permissions
to launch the replication agent. The step failed.
I don't think this has any thing to do with running the agent remotely, but
I'm not sure. Any suggestions?
Thanks
Paul
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uQmkhv7FEHA.744@.TK2MSFTNGP09.phx.gbl...
> Paul,
> here is an MSDN article which shows how to enable the merge agent to be
run
> remotely:
>
http://msdn.microsoft.com/library/de...us/howtosql/ht
> _replctem_6c1l.asp
> HTH,
> Paul Ibison
>
|||PaulW,
The easiest way to set up replication between different domains is to create a local user account on both the servers with the same username and password. Make the account an administrator of the local machine. Then set this account as the startup account
for the SQL Server Agent. Restart the SQL Server agent and start the merge agent. This should fix your problem.
Regards
Chris R
|||Thanks for the response. I saw that option for non-trusted domains and when
I presented it, the option was quickly shot down because the production
system will be administered by different companies and they didn't want to
be forced to share passwords..
Thanks,
Paul
"Chris R" <crichardson@.iir-central.com> wrote in message
news:7CE505D6-BE9C-40E4-80D4-75F7287AAB3C@.microsoft.com...
> PaulW,
> The easiest way to set up replication between different domains is to
create a local user account on both the servers with the same username and
password. Make the account an administrator of the local machine. Then set
this account as the startup account for the SQL Server Agent. Restart the
SQL Server agent and start the merge agent. This should fix your problem.
> Regards
> Chris R
|||Non-trusted domains can also be set up using the Guest account, but I would assume that this is even less likely to be allowed :-)
Regards,
Paul Ibison
|||I already know that the guest account has been disabled. There will be a two
way trust between the domains. I don't know how that got approved. There
must be some way to perform pull subscriptions in a trusted relationship
without matching accounts and passwords.
Paul
|||Paul,
I have used pass-through security, or the guest account, because of working
in a non-trusted environment but in your case you should be able to use
impersonation. Have a look at this article:
http://support.microsoft.com/?id=321822.
Regards,
Paul Ibison
|||I think I got it. We started having net work troubles once it appeared to be
working, so I'm not sure. The problem was that the SQL Server agent accounts
had the same name on both machines, but different passwords. Once I changed
the name of the account on one machine and gave it dbo access to the other
machine's database it looked like it was working. Most of the initial
snapshot was applied, but it failed due to the retry count. Before this
change, it never get started.
Thanks for the help.
Paul