What I mean is that you now have two connection strings: one in the .odc
which is an OLE DB connection string, and one in the OpenDataSource call,
which is actually an ODBC connection string. I /think/ in this case you will
get an OLE DB connection that uses the SQLOLEDB.1 provider, rather than an
ODBC connection, but I can't be sure with out checking. If word is actually
using the ODBC connection string, you might get your data but you would
probably lose the Unicode string values in your data.
I would try one or more of the following:
a. remove this altogether:
Connection:=DSN=Select;DATABASE=demoDB;uid=demo;pw d=demoPword;,
b. use the following instead (you may need to remove stuff from this string
if it exceeds 255 characters - e.g. you probably do not actually need the
Packet Size, Use encryption or tag with column collation values unless you
want to change the defaults. You probably do not need the Workstation ID
either. Or the User ID, if you are using integrated security.
Connection:="Provider=SQLOLEDB.1;Persist Security Info=True;User
ID=demo;Data Source=SQL-TEST-01;Use Procedure for Prepare=1;Auto
Translate=True;Packet Size=4096;Workstation ID=DEMO01;Use Encryption for
Data=False;Tag with column collation when possible=False;Initial
Catalog=demoDB
c. If you do (b), you can also try specifying a completely empty .odc file
and see if it gives you the results you need. The advantage is that you
don't need to have different .odc files for every connection (and frankly,
the .odc files do not usually work very hard for you).
--
Peter Jamieson
http://tips.pjmsn.me.uk
"rc" wrote in message
...
Thanks for the response, I am not sure what you mean about specifying the
connection string, do you have an example?
my open datasource is wrdMailMerge.OpenDataSource(Name:=C:\Select.odc,
Connection:=DSN=Select;DATABASE=demoDB;uid=demo;pw d=demoPword;,
SQLStatement:=SELECT * FROM "FullfillTemp")
and the odc file has the following
Provider=SQLOLEDB.1;Persist Security Info=True;User ID=demo;Data
Source=SQL-TEST-01;Use Procedure for Prepare=1;Auto Translate=True;Packet
Size=4096;Workstation ID=DEMO01;Use Encryption for Data=False;Tag with
column
collation when possible=False;Initial Catalog=demoDB
"Peter Jamieson" wrote:
r.Report.DSNConnection = a valid connection string
Just out of interest, are you using an ODBC connection string or an OLE
DB
connection string?
Because if you are using an ODBC connection string, that may explain why
you
are only getting numeric results: if your text fields in SQL Server are
unicode fields (NVARCHAR etc.) the OLE DB provider will return them, but
the
ODBC driver will not.
--
Peter Jamieson
http://tips.pjmsn.me.uk
"Peter Jamieson" wrote in message
...
appreciated, in particular I would prefer not to use an ODC file but
it
appears to be the only option
For an OLE DB connection you can't avoid a .odc or .udl, but you may be
able to use a completely empty .odc and specify everything else in the
OpenDataSource.
secondly whilst
exploring the functionality I have tried use calls to stored
procedures
in
the SQL and whilst the connection seems to be established the only
merge
fields that get populated are the number fields
I have never managed to call a stored procedure successfully in any
version of Word after 2000 (which would need ODBC). The only way you
could
do it in Word 2000 was to use an ODBC exec to do it (e.g. you use "{
exec
procedurename param1, param2 }" instead of "SELECT * FROM something".
In
theory that should be possible with OLE DB too, because the provider
supports that syntax, but in practice it does not seem to work. I think
what happens is that most procedures actually return several result
sets
(it is quite hard to define a procedure that does anything useful that
does not do that) and WOrd does not seem to be able to deal with those
results.
In fact I find it interesting that you managed to get any stored
procedure
to work at all!
However, it does seem to be possible to use Table-valued TRANSACT-SQL
functions as data sources. I would have to check, but in that case I
think
the SQL in OpenDataSource looks something like
"SELECT * FROM mytablefunction(myparam)"
r.Report.SQL= "SELECT * FROM ""FullfillTemp"""
it dosnt make much difference if I add some where clause allthough I
would
like to change to "exec storedProc p1,p2 etc".
and why do you need so many
quotes?
I agree that these quotes should not be needed. Unfortunately, Word (or
the so-called "Office Data Source Object" that it uses to get OLE DB
data), seems to need them. I usually use the [ ] syntax instead,
although
I suspect it is not portable outside Microsoft.
"SELECT * FROM [FulfillTemp]"
Sometimes Word/ODSO also seems to insist that you use table alias names
that are also syntactically unnecessary. Also, you may need to name the
table using a full table reference e.g. [Database].[Owner].[Table]
(There
has been a recent change in Word 2003 SP3 in this area I think).
(NB, I also do not believe that there is a way to get a reference to
the
ODSO object that Word uses when it connects to a data source, so you
cannot "fix" the way Word works in there).
r.Report.DSNConnection = a valid connection string
Just out of interest, are you using an ODBC connection string or an OLE
DB
connection string?
--
Peter Jamieson
http://tips.pjmsn.me.uk
"rc" wrote in message
...
I am trying to work with the word object model via .Net and have
managed
with
some success in setting a mail merge using SQL Sever successfully, I
am
now
receiving unable to open data source, although when I use word
directly
using
the ODC file, and view I have created I have success, secondly whilst
exploring the functionality I have tried use calls to stored
procedures
in
the SQL and whilst the connection seems to be established the only
merge
fields that get populated are the number fields, if anyone can shed
some
light on the limitations correct method of doing this it would be much
appreciated, in particular I would prefer not to use an ODC file but
it
appears to be the only option and would really like to call a stored
proc.
any eamples would be great.
In anticipation thanks for your help
r.Report.SQL= "SELECT * FROM ""FullfillTemp"""
it dosnt make much difference if I add some where clause allthough I
would
like to change to "exec storedProc p1,p2 etc". and why do you need so
many
quotes?
r.Report.DBodcConnection= a valid ODC file
r.Report.DSNConnection = a valid connection string
Code BlockDim wrdSelection As Word.Selection
Dim wrdMailMerge As Word.MailMerge
Dim wrdMergeFields As Word.MailMergeFields
Try
' Create an instance of Word and make it visible.
wrdApp = CreateObject("Word.Application")
wrdApp.Visible = True
wrdApp.ActivePrinter = printerName
' Add a new document.
wrdDoc = wrdApp.Documents.Add(r.Report.WordDocTemplate)
wrdDoc.Select()
wrdMailMerge = wrdDoc.MailMerge()
'' Create MailMerge Data file.
'CreateMailMergeDataFileDocument()
Dim mmd As Word.MailMergeDataSource
mmd = wrdMailMerge.DataSource
wrdMailMerge.OpenDataSource(Name:=r.Report.DBodcCo nnection,
Connection:=r.Report.DSNConnection, SQLStatement:=r.Report.SQL)
With wrdMailMerge.DataSource
.FirstRecord = Word.WdMailMergeDefaultRecord.wdDefaultFirstRecord
.LastRecord = Word.WdMailMergeDefaultRecord.wdDefaultLastRecord
End With
' Perform mail merge.
If Preview = False Then
wrdMailMerge.Destination = Word.WdMailMergeDestination.wdSendToPrinter
Else
wrdMailMerge.Destination =
Word.WdMailMergeDestination.wdSendToNewDocument
wrdApp.Visible = True
End If
wrdMailMerge.SuppressBlankLines = True
wrdMailMerge.Execute(True)
' Close the original form document.
wrdDoc.Saved = True
'' Clean up temp file.
'System.IO.File.Delete(cMergeDataDoc)
Catch ex As Exception
Me.Cursor = Cursors.Default
MessageBox.Show(ex.Message)
Finally
Try
'Close the main document
wrdDoc.Close(False)
Catch ex As Exception
'dosnt matter if this falls through
End Try
' Release References.
wrdSelection = Nothing
wrdMailMerge = Nothing
wrdMergeFields = Nothing
wrdDoc = Nothing
wrdApp = Nothing
Me.Cursor = Cursors.Default
End Try