[SQL 2005] Transactionnal replication via FTP

Here is the context, in short. I have 2 remote sites with SQL Server 2005.
Each server can't see the other one. I need to replicate data between them.
I use MSSQL Management Studio with SQL Server Developer Edition.
When creating a publication, I can say that I want the snapshot to be put on
a FTP in the publication option. no problem here. When I want to create a
subscribtion, I must give the publication to use, and there is the problem
because I can't see the remote server.
The only thing I need to do is to say the subscriber to take the snapshot on
the FTP, but I've not found how I can do this.
If someone can help me ^^
Thanks in advance.
P.S. : sorry for my bad english, I'm not very good with foreign language :p
Divid,
you'll need some sort of connectivity. This could be a VPN, HTTPS or just
TCP/IP (non-trusted). Somehow, the subscriber will need to contact the
publisher. If you want a purely FTP solution then you must use another
technology.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||VPN, in a wonderfull dream. Maybe one day ... or not :p
Merge replication can use HTTPS but the merge replication is not suitable in
my situation. Transactionnal replication can't use HTTPS as far as I know.
For the TCP/IP (non trusted), can you explain about this a little more. I
don't know what is it so maybe it could do the work.
Thanks again.
|||David,
this should help a bit:
http://www.replicationanswers.com/InternetArticle.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||It doesn't really help. I've add the alias but I can't add a "virtual"
publisher (the alias), and then I can't subscribe it, in Management Studio.
Seems that I'm only able to work on local because local publishing/local
subscribtion are the only options in Management Studio.
|||David,
unfortunately I can't repro this at the moment as we only have one SQL
Server 2005 installation. As soon as another is added I'll give it a go, but
hopefully someone else will take up the thread before then.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

[ODBC SQL Server Driver]Connot generate SSPI Context Anybody ...

We get the following error: Connection Failed:
SQLState: 'HY000' SQL Server Error:
1060 [microsoft][ODBC SQL Server Driver]Connot generate
SSPI Context Anybody ...
client : windows nt4
server : Windows 2000 sql server 7.x
but on Windows XP it goes very well.
any ideas ?Check the following Knowledge Base article:
Missing Windows NT LM Security Support Provider registry key
causes "Cannot Generate SSPI Context" error message when you
connect to SQL Server
http://support.microsoft.com/?id=269541
-Sue
On Fri, 2 Apr 2004 08:36:22 -0800, "amichaud"
<anonymous@.discussions.microsoft.com> wrote:

>We get the following error: Connection Failed:
>SQLState: 'HY000' SQL Server Error:
>1060 [microsoft][ODBC SQL Server Driver]Connot generate
>SSPI Context Anybody ...
>client : windows nt4
>server : Windows 2000 sql server 7.x
>
>but on Windows XP it goes very well.
>any ideas ?

[ODBC SQL Server Driver]Connot generate SSPI Context Anybody ...

We get the following error: Connection Failed:
SQLState: 'HY000' SQL Server Error:
1060 [microsoft][ODBC SQL Server Driver]Connot generate
SSPI Context Anybody ...
client : windows nt4
server : windows 2000 sql server 7.x
but on windows xp it goes very well.
any ideas ?
Check the following Knowledge Base article:
Missing Windows NT LM Security Support Provider registry key
causes "Cannot Generate SSPI Context" error message when you
connect to SQL Server
http://support.microsoft.com/?id=269541
-Sue
On Fri, 2 Apr 2004 08:36:22 -0800, "amichaud"
<anonymous@.discussions.microsoft.com> wrote:

>We get the following error: Connection Failed:
>SQLState: 'HY000' SQL Server Error:
>1060 [microsoft][ODBC SQL Server Driver]Connot generate
>SSPI Context Anybody ...
>client : windows nt4
>server : windows 2000 sql server 7.x
>
>but on windows xp it goes very well.
>any ideas ?

@@IDENTITY vs. SCOPE_IDENTITY() on CLR Context Connection

Dear all,

I am trying to use SCOPE_IDENTITY() on the CLR Context Connection since it is limited to insertions in a more narrow scope than @.@.IDENTITY.

The connection string in the .NET Assembly is:
Using connection As New SqlConnection("context connection=true;"),

Onwards, I insert a new row to a table with an int(4) identity column, but the following returns zero (0):
Using command2 As New SqlCommand("SCOPE_IDENTITY() ", connection)
Try
NewTagID = CInt(command2.ExecuteScalar().ToString)

However, the following code returns the actual identity value:
Using command2 As New SqlCommand("SELECT @.@.IDENTITY", connection)

Try

NewTagID = CInt(command2.ExecuteScalar().ToString)

Why doesn't the SCOPE_IDENTITY() work on the context connection? In the meantime, I assume that @.@.IDENTITY would be the better option.

Thankful in advance for advice.Shouldn′t you use the SELECT before the SCOPE_IDENTITY() ? Perhaps you are returning 0 (rows affected) rather than the identity Value. If no identity value is available normally NULL is given back to the caller.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||Thanks for the suggestion, and yes, I am already using SELECT in front of SCOPE_IDENTITY(). And there should be an identity value available because I can successfully do this in Query Analyzer:

INSERT INTO mytable (col1, col2, col3)
VALUES ('', '', 1)
GO
SELECT SCOPE_IDENTITY()
GO

And I get a 1x1 grid with unnamed column showing the new ID and two rows of messages, each saying (1 row(s) affected).

I get exactly the same result (grid as well as messages) by isusing:

INSERT INTO mytable (col1, col2, col3)

VALUES ('', '', 1)

GO

SELECT @.@.IDENTITY

GO

In VB2005 using CLR, I can use command.ExecuteScalar() after "select @.@.identity" to retrieve the value and it works. But "select scope_identity()" does not work to retrieve the scalar. I was confused because it is not consistent with the behavior in Query Analyzer. All in all, it is not a big deal since @.@.identity works fine but perhaps there is some fundamental difference on the context connection which could be useful to be aware of.|||

There are better way to do this, with SQL Server 2005 you are able to use the OUTPUT clause which will directly and inline′will enable you to pass back a parameter from within the query. Soo the BOL for more Information and the syntax for that.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

@@IDENTITY vs. SCOPE_IDENTITY() on CLR Context Connection

Dear all,

I am trying to use SCOPE_IDENTITY() on the CLR Context Connection since it is limited to insertions in a more narrow scope than @.@.IDENTITY.

The connection string in the .NET Assembly is:
Using connection As New SqlConnection("context connection=true;"),

Onwards, I insert a new row to a table with an int(4) identity column, but the following returns zero (0):
Using command2 As New SqlCommand("SCOPE_IDENTITY() ", connection)
Try
NewTagID = CInt(command2.ExecuteScalar().ToString)

However, the following code returns the actual identity value:
Using command2 As New SqlCommand("SELECT @.@.IDENTITY", connection)

Try

NewTagID = CInt(command2.ExecuteScalar().ToString)

Why doesn't the SCOPE_IDENTITY() work on the context connection? In the meantime, I assume that @.@.IDENTITY would be the better option.

Thankful in advance for advice.Shouldn′t you use the SELECT before the SCOPE_IDENTITY() ? Perhaps you are returning 0 (rows affected) rather than the identity Value. If no identity value is available normally NULL is given back to the caller.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||Thanks for the suggestion, and yes, I am already using SELECT in front of SCOPE_IDENTITY(). And there should be an identity value available because I can successfully do this in Query Analyzer:

INSERT INTO mytable (col1, col2, col3)
VALUES ('', '', 1)
GO
SELECT SCOPE_IDENTITY()
GO

And I get a 1x1 grid with unnamed column showing the new ID and two rows of messages, each saying (1 row(s) affected).

I get exactly the same result (grid as well as messages) by isusing:

INSERT INTO mytable (col1, col2, col3)

VALUES ('', '', 1)

GO

SELECT @.@.IDENTITY

GO

In VB2005 using CLR, I can use command.ExecuteScalar() after "select @.@.identity" to retrieve the value and it works. But "select scope_identity()" does not work to retrieve the scalar. I was confused because it is not consistent with the behavior in Query Analyzer. All in all, it is not a big deal since @.@.identity works fine but perhaps there is some fundamental difference on the context connection which could be useful to be aware of.|||

There are better way to do this, with SQL Server 2005 you are able to use the OUTPUT clause which will directly and inline′will enable you to pass back a parameter from within the query. Soo the BOL for more Information and the syntax for that.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de