[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