Re: OpenDataSource SQL Server xpress problem

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

<<
In scenario 1) If I go to the Providers Tab in the 'Data Link Properties' dialog and set this to SQL Native Client and then work through the boxes it then succeeds in the Test Connection and the subsequent Mail Merge !! Weird !
>>

Assuming this created a new .odc, maybe you should use that (.odc's are human-readable HTML files so you should, for example, be able to extract the connection string if you want to use that in an OpenDataSource instead. If you post that connection string here I may be able to spot something useful.)

FWIW as long as the supporting "behaviour" file DATACONN.HTC is in the same folder as the .odc, you /may/ be able to open your data source directly in Internet Explorer, which can be helpful when you are testing. Some things to know about Word and .odc files, though:
a. When you have set up a data source using a .odc, the necessary connection info. is actually embedded in the Word document. So when you close and re-open the document, it will point at the original data source, even if (for example) you have modified the .odc so that it points to a different server. That might be a significant fact in a web-based scenario.
b. Word does not take advantage of all the facilities in a .odc (e.g. in a .odc you can define a SQL query, but I do not think Word honours it)

> Ideally I want all this to run from a Web Server but with the Word documents
> being produced on the Clients machine.

To produce the Word documents on the client machine using Word mailmerge, the client currently has to have Word running locally. Assuming you really want to host your SQL Server on your Web server, or perhaps another web-based box, the real difficulty is that Word still has to connect to that. FWIW I never managed to get Word to connect to SQL Server beyond about v. 2005 I think without using Windows authentication, so that may pose a problem in this case.

> if from VSTO. However I don't want to go down the VSTO route if I can
> help it.

VSTO is one possibility. Using .NET on the server side to construct ..docx format documents without the need to automate Word (which is in effect what VSTO does) is another. I would try to find a group where Cindy Meister is a regular contributor as she may have some useful pointers on this one.


Peter Jamieson

http://tips.pjmsn.me.uk

Poppa Bear wrote:
Thanks for all your Help. I tried your suggestions with some success.

I tried setting up the .odc file through the Word Mail Merge wizard as you suggested This seems to work OK as long as the strConnection parameter is set to 'missing' in the OpenDataSource method.

Trying your other suggestion with an empty.odc file (NOT empty.odc.txt !)

I tried the following 3 scenarios with SDBPath set to "C:\WORK\empty.odc"
in each .

1) Dim strConnect As String = "Provider=SQLOLEDB.1;Data Source=c7e6i3;
Database=dbSQLEmpTest;Integrated Security=True;"

2) Dim strConnect As String = "Provider=SQLNCLI;Data Source=c7e6i3;
Database=dbSQLEmpTest;Integrated Security=True;"

3) Dim strConnect As String = "Provider=SQLNCLI;Data Source=c7e6i3\SQLEXPRESS;
Database=dbSQLEmpTest;Integrated Security=True;"

In all 3 cases I get a 'Data Link Properties' dialog box appear.
If I work through the dialog to connect and then click the 'Test Connection' button in each case it fails to connect.

However ........
In scenario 1) If I go to the Providers Tab in the 'Data Link Properties' dialog and set this to SQL Native Client and then work through the boxes it then
succeeds in the Test Connection and the subsequent Mail Merge !! Weird !
(Providers is already set to SQLNative in the other two scenarios)

As I can find no way to get round the dialog boxes and get a successful connection I had to give up. I do have a solution (though not ideal)
in your first method for which I am very grateful.

Ideally I want all this to run from a Web Server but with the Word documents
being produced on the Clients machine. This however is I realise another ball game way beyond the scope of this forum I think. I have a book 'Visual Studio Tools for Office' by Carter/Lippert which shows how to do
if from VSTO. However I don't want to go down the VSTO route if I can help it. If you can point me in the direction of any good (non German !) books or articles, then I would be grateful. However I suspect I already
know the answer to that.

Thanks again for all your help.

Poppa

.

Relevant Pages

  • Re: AD & NAT
    ... I thought i explained the scenario we are facing in detail but i would ... The client is a demerged company of the parent. ... The natting would be done by a NAT device and not the ... understanding purpose we would call it as primary datacentre while additional ...
    (microsoft.public.windows.server.active_directory)
  • Re: Encryption Scenerio
    ... For the second scenario, using DPAPI is probably the best way to go. ... i have problems about storing KEYS that are used in encryption. ... not decide where should i store my keys or how should i store them. ... We are developing project with server / client architecture. ...
    (microsoft.public.dotnet.security)
  • Re: SQL and Filter
    ... Using Filter is not a good idea unless recordset is disconnected. ... as in your second scenario. ... client probably will not work with all these records at same time. ... > Trainers or Rooms Records but we want to retrieve the Trainers or Rooms ...
    (microsoft.public.vb.database.ado)
  • Re: open() and ESTALE error
    ... for open for reading/writing the scenario looks quite clear for me. ... of doing immediate openif the first open failed with ESTALE. ... You didn't specify client or server side, ... a new openfor "C" will result in fd2 if the ...
    (freebsd-hackers)
  • Re: SQL Server 2008 Mirroring Role Switch
    ... APIs support automatic failover (e.g. SQL native client and .NET SQL ... Do these APIs support non-Windows clients? ...
    (microsoft.public.sqlserver.server)