[RC]

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

[Peter Jamieson]

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

[Peter Jamieson]

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

[RC]

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

[Peter Jamieson]

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

[RC]

Thanks Peter, I have stripped out the connection string and reduced it to it
bare minimum with an oledb provider as sugested with an empty odc file and it
appears to be working, just tring to get the function stuff working now, is
there a limit to the size of the SQL also?

"Peter Jamieson" wrote:

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

[RC]

Ok, somewhat confused now! the sample I am working with now has a function, a
stored Proc and a view, all returning exactley the same data,
The view vorks by using
"SELECT * FROM [FullfillRenewals]"

the stored proc is called using either
"EXEC [Select].[dbo].[getRenewals] "
or "{EXEC [Select].[dbo].[getRenewals]}"
both return "unable to open datasource"
So I have also tried creating a function as sugested and put the call as
follows
"SELECT * FROM [Select].[dbo].[fn_GetRenewals] (31, '17 Dec 2007','17 Dec
2007')"
again the return is "unable to open datasource"
all are using the oledb connection string and going to a SQL 2000 database,
any sugestions?


"Peter Jamieson" wrote:

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

[Peter Jamieson]

There's a limit to the size of the SQL /in Word/ - typically either 255 or
511 characters. If you are in a position to define your functions then I
guess that all you will need is SELECT * FROM (something), and the thing
that would increase the length of the SQL would be the length of any
parameters you needed to pass. But I'd establish the basic feasibility
first.

--
Peter Jamieson
http://tips.pjmsn.me.uk

"rc" wrote in message
...
Thanks Peter, I have stripped out the connection string and reduced it to
it
bare minimum with an oledb provider as sugested with an empty odc file and
it
appears to be working, just tring to get the function stuff working now,
is
there a limit to the size of the SQL also?

"Peter Jamieson" wrote:

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

[Peter Jamieson]

As far as I know, you /must/ use an alias name, i.e. try:

"SELECT fn.* FROM [Select].[dbo].[fn_GetRenewals] (31, '17 Dec 2007','17
Dec 2007') fn"

--
Peter Jamieson
http://tips.pjmsn.me.uk

"rc" wrote in message
...
Ok, somewhat confused now! the sample I am working with now has a
function, a
stored Proc and a view, all returning exactley the same data,
The view vorks by using
"SELECT * FROM [FullfillRenewals]"

the stored proc is called using either
"EXEC [Select].[dbo].[getRenewals] "
or "{EXEC [Select].[dbo].[getRenewals]}"
both return "unable to open datasource"
So I have also tried creating a function as sugested and put the call as
follows
"SELECT * FROM [Select].[dbo].[fn_GetRenewals] (31, '17 Dec 2007','17 Dec
2007')"
again the return is "unable to open datasource"
all are using the oledb connection string and going to a SQL 2000
database,
any sugestions?


"Peter Jamieson" wrote:

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