Re: OpenDataSource & stored procedure

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Michael (Michael_at_online.nospam)
Date: 10/12/04 

Date: Tue, 12 Oct 2004 10:47:02 -0700

I think you cracked it!

Simple stored procedures work with or without parameters. The procedure I
need to call is quite complicated and creates two temporary tables before
doing a select on one of them to return the results.

Back to the drawing board I guess.

Many thanks for taking the time to look at this. If you have any other
insights as to why Word can't cope with this I'd be very interested to hear.
Michael

"Peter Jamieson" wrote:

> > When MSQuery returns data to Word does it return the recordset or the
> > connection details and SQLStatement for Word to rerun the query?
>
> As far as I am aware, the latter.
>
> At the moment I can't think of any reason why this would not work except
> a. I've noticed prtoblems in the past when Word tries to execute procedures
> that create more than one result table. It's a while since I looked at this
> so I can't remember the details. I would certainly try creating a really
> simple test procedure if that is not what you are already doing.
> b. security, as suggested. But if you are using Integrated Security and the
> DSN is set up to say that, you should be OK, assuming of couse that your
> login has the necessary permissions.
>
> Do parameterless procedures work?
>
> --
> Peter Jamieson
>
> "Michael" <Michael@online.nospam> wrote in message
> news:F2F39DF5-03E7-4DC9-93D3-E67FAFEBEC4C@microsoft.com...
> > Hi Peter,
> >
> > The call works in MSQuery but won't return the data to Word.
> >
> > I'm using Word 2003.
> >
> > I start Word, click OpenDataSource on the MailMerge toolbar and then
> select
> > MSQuery from the dropdown Tools button. When MSQuery starts, the 'Choose
> > Data Source' dialog appears and I select ASDBdsn. I close the 'Add
> Tables'
> > dialog, then click the SQL button and enter the stored procedure call.
> >
> > MSQuery qives trhe warning "SQL Query can't be represented graphically",
> the
> > call succeeds and the records are displayed. I then select File->Return
> Data
> > to Microsoft Word, MSQuery closes but Word gives the error message: "Word
> was
> > unable to open the data source".
> >
> > The behaviour is identical if I use :-
> > exec ASDB.dbo.spGetListContacts 'Year 3'
> >
> > When MSQuery returns data to Word does it return the recordset or the
> > connection details and SQLStatement for Word to rerun the query?
> >
> > Michael
> >
> >
> >
> > "Peter Jamieson" wrote:
> >
> > > A call with the same syntax works OK here (Word 2002 SP2 and Word 2003).
> At
> > > the moment I can't easily test with Word 2000
> > >
> > > Could there be a security problem - e.g. on your Word workstation do you
> > > have the necessary permissions for the prcedure as well as ListContacts
> and
> > > ListName?
> > >
> > >
> > > What happens if you issue the same syntax in MS Query (as opposed to
> Query
> > > Analyzer) on the workstation where you are running Word - create a
> query,
> > > click the SQL button and replace whatever SQL is there by
> > >
> > > { call ASDB.dbo.spGetListContacts ('Year 3') }
> > >
> > > --
> > > Peter Jamieson
> > >
> > > "Michael" <Michael@online.nospam> wrote in message
> > > news:D3701DE2-30C1-4213-96EF-7E6632CDA2B7@microsoft.com...
> > > > This works :-
> > > > OpenDataSource _
> > > > Name:="", Connection:= "DSN=ASDBdsn;" _
> > > > SQLStatement:= "SELECT * FROM [ListContacts] WHERE [ListName] =
> 'Year
> > > 3'"
> > > >
> > > > This doesn't work :-
> > > > OpenDataSource _
> > > > Name:="", Connection:= "DSN=ASDBdsn;" _
> > > > SQLStatement:= "{ call ASDB.dbo.spGetListContacts ('Year 3') }"
> > > >
> > > > The stored procedure works fine in Query Analyzer.
> > > >
> > > > Any ideas what could be wrong?
> > > >
> > > > Thanks
> > > > Michael
> > > >
> > > >
> > >
> > >
> > >
>
>
>

Quantcast