Add linked server at Excel VBA

I had SQLDMO at reference library.

Private Sub test()
Dim s As SQLDMO.SQLServer
Dim ls As SQLDMO.LinkedServer
Set s = New SQLDMO.SQLServer
s.Connect "Server1", "ID", "Password"
Set ls = New SQLDMO.LinkedServer
With ls
.Name = "Server2"
.ProviderName = "SQLOLEDB"
.DataSource = "Server2"
' .ProviderString = ""

End With
s.LinkedServers.Add ls
s.Close

'End Sub

I block ProviderString as i don't know what is it.

I got an error message when it's running the line "s.LinkedServers.Add ls" that

"Run-time error '-2147206257 (80043b8f)':

Automation error"

My question:

1. What is providerstring? what should be put here?

2. How to fix the error

Hi there,

Provider string should look something like the following:

@.provstr=N'DRIVER={<driver name of your linked server, in this case Excel} ;SERVER=<address if your server where the driver resides>;DATABASE=<database name>; USER=<username>; PASSWORD=<password>; OPTION=3'

I found this here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=304918&SiteID=1

Also, this may help: http://www.databasejournal.com/features/mssql/article.php/10894_3085211_2

Since you're not executing within the QA of management studio, I'm guessing that you only need to put Excel's driver name in your provider string, but don't take my word as I haven't tried doing this in VB.

Hope this helps!

Isa

ActveX Pull error

I have a publication created using SQLDMO on the server.
.PublicationAttributes = SQLDMO_PUBATTRIB_TYPE.SQLDMOPubAttrib_AllowPull
+ _
SQLDMO_PUBATTRIB_TYPE.SQLDMOPubAttrib_ImmediateSyn c + _
SQLDMO_PUBATTRIB_TYPE.SQLDMOPubAttrib_IndependentA gent + _
SQLDMO_PUBATTRIB_TYPE.SQLDMOPubAttrib_AllowAnonymo us
I am trying to get a Pull to work from a laptop (in test mode connected
to the main SQL machine via ethernet, but in the end will be connected
via the internet).
The Pull is done via ActiveX:
With myMergeObj
.Distributor = "IIS-SQL"
.DistributorLogin = login
.DistributorPassword = password
.DistributorSecurityMode = SECURITY_TYPE.DB_AUTHENTICATION
.Publisher = inxRS.Globals.gServer
.PublisherDatabase = "SAMPLE"
.Publication = "SAMPLE-RS"
.PublisherLogin = login
.PublisherPassword = password
.PublisherSecurityMode = SECURITY_TYPE.DB_AUTHENTICATION
.Subscriber = Trim(Environment.MachineName)
.SubscriberDatabase = "SAMPLE"
.SubscriberSecurityMode = SECURITY_TYPE.DB_AUTHENTICATION
.SubscriptionType = SUBSCRIPTION_TYPE.PULL
.SubscriberLogin = login
.SubscriberPassword = password
.SubscriptionName = "SAMPLE-RS"
.HostName = Trim(inxRS.Globals.gLogin)
.ExchangeType = EXCHANGE_TYPE.BIDIRECTIONAL
End With
myMergeObj.Initialize()
myMergeObj.Run()
myMergeObj.Terminate()
This errors with:
Server 'IIS-LAPTOP' is not registered at server 'IIS-SQL'.
I thought for PULL all I had to do was create the subscription for
anonymous access. What am I missing?
Thanks.
Darin
*** Sent via Developersdex http://www.codecomments.com ***
You will need to use the PublisherNetwork and DistributorNetwork and set
them to TCPIP_SOCKETS (a value of 1), and set the PublisherAddress and
DistributorAddress to the IP address of the Publisher or its FQDN (i.e.
Publisher.Microsoft.com).
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
"Darin" <darin_nospam@.nospamever> wrote in message
news:OApUxNvgFHA.3220@.TK2MSFTNGP10.phx.gbl...
> I have a publication created using SQLDMO on the server.
> PublicationAttributes = SQLDMO_PUBATTRIB_TYPE.SQLDMOPubAttrib_AllowPull
> + _
> SQLDMO_PUBATTRIB_TYPE.SQLDMOPubAttrib_ImmediateSyn c + _
> SQLDMO_PUBATTRIB_TYPE.SQLDMOPubAttrib_IndependentA gent + _
> SQLDMO_PUBATTRIB_TYPE.SQLDMOPubAttrib_AllowAnonymo us
> I am trying to get a Pull to work from a laptop (in test mode connected
> to the main SQL machine via ethernet, but in the end will be connected
> via the internet).
> The Pull is done via ActiveX:
> With myMergeObj
> .Distributor = "IIS-SQL"
> .DistributorLogin = login
> .DistributorPassword = password
> .DistributorSecurityMode = SECURITY_TYPE.DB_AUTHENTICATION
> .Publisher = inxRS.Globals.gServer
> .PublisherDatabase = "SAMPLE"
> .Publication = "SAMPLE-RS"
> .PublisherLogin = login
> .PublisherPassword = password
> .PublisherSecurityMode = SECURITY_TYPE.DB_AUTHENTICATION
> .Subscriber = Trim(Environment.MachineName)
> .SubscriberDatabase = "SAMPLE"
> .SubscriberSecurityMode = SECURITY_TYPE.DB_AUTHENTICATION
> .SubscriptionType = SUBSCRIPTION_TYPE.PULL
> .SubscriberLogin = login
> .SubscriberPassword = password
> .SubscriptionName = "SAMPLE-RS"
> .HostName = Trim(inxRS.Globals.gLogin)
> .ExchangeType = EXCHANGE_TYPE.BIDIRECTIONAL
> End With
> myMergeObj.Initialize()
> myMergeObj.Run()
> myMergeObj.Terminate()
>
> This errors with:
> Server 'IIS-LAPTOP' is not registered at server 'IIS-SQL'.
> I thought for PULL all I had to do was create the subscription for
> anonymous access. What am I missing?
> Thanks.
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***
|||I got further, thanks. But, no I get the error:
The schema script
'c:\inware\data\unc\IIS-SQL_inware_inware-RS\20050707140259\UTCompany_1.
sch' could not be propagated to the subscriber.
The process could not read file 'same file as above' due to OS error 3.
The system cannot find the path specified.
Darin
*** Sent via Developersdex http://www.codecomments.com ***
|||Darin,
the problem would appear to be with the location of the working folder -
it's using the c drive of the distributor. If you change it to use a file
share, reinitialize, run the snapshot agent then synchronize it should be
fine.
HTH
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)