none
Field DATABASE and SQL Function / Stored Procedure RRS feed

  • Question

  • Hi All

    Techonlogy used: Word 2010, Win7-64bit and SQL Server 2005.

    In our current solution with Word 2000 we used a lot of SQL multi statement table value functions and stored procedures to centralize the business logic.

    Since Word 2003 there have been made some changes to the fact on what kind of SQL one can use within the field DATABASE.

    With that we do have troubles. Consider the following function:

    create function dbo.WordShell(@cmd varchar(4000)) 
    returns table 
    as return 

     select * from SUB where SubNr = @cmd 


    Within the SQL Server Management Studio the result is as required; I do get back one row of a table. If I’m using this command within a DATABASE field

    { DATABASE  \d "C:\\Dummy.odc" \c " DSN=AKIS;trusted_connection=yes;DATABASE=BETA " \s " select * from BETA.dbo.WordShell('100211')" \h }

    I do get the error message


    English: "This table can not be opened. The Tablename contains unvalid characters."

    Just to clarify that the field declaration within Word is correct, the following field returns the data just nicely.

    {DATABASE  \d "C:\\Dummy.odc" \c " DSN=AKIS;trusted_connection=yes;DATABASE=BETA " \s " select * from SUB where SubNr = 100211" \h }

    Thanks for any help!
    Martin
    Monday, October 10, 2011 9:49 AM

Answers

  • Martin,

    I had a deeper dive into the Profiler (still using Word 2003 with an OLE DB connection) and turned on display of everything I could find.

    Error 156

    Incorrect syntax near the keyword 'SET'

    Error 16397

    A server cursor cannot be opened on the given statement or statements. Use a default result set or client cursor.

    As far as I can tell, this means that Word's (ODSO's) code is opening a Server-side cursor which cannot process multiple statements (after SQL Server 6) and that Word (ODSO) should be using a "default result set" where a client-side cursor is used.

    The following KB article may be rather old, but it is highly likely that Word's code in this area also dates back to around 2000 (i.e. they introduced OLE DB support and ODSO (AFAIK) after Word 2000 so I would guess the code dates from around then). 

    http://support.microsoft.com/kb/156500

    <<

    Do you have any access or know of anybody who could help me to get further with this issue? Do you have contacts to an SQL Server MVP?

    >>
    I don't, but I'll ask Cindy (who may see this anyway).


    Peter Jamieson
    • Marked as answer by Martin Koenig Wednesday, March 23, 2016 9:10 AM
    Wednesday, October 12, 2011 8:04 AM

All replies

  • I think the problem is that the Office Data Source Object that Word uses to process queries expects syntax that should not have to be there. I also think that it has been changed in different versions of Office.

    So, e.g., ODSO expects a table name to be "quoted", either by [ ] or perhaps " " or ` `. But if it finds those, it strips them off. But if you do not have those characters, it strips off the first and last character of the "table name" anyway!

    So if you send the following query to the server

    select * from BETA.dbo.WordShell('100211')

    The server actually receives

    select * from ETA.dbo.WordShell('100211'


     

    But surrounding the function call with [] or "" doesn't work either. What does seem to work is specifying a table alias, e.g.

     

     

     

    select W.* from BETA.dbo.WordShell('100211') B

     

     

     

     

    (Whether that will work via the ODBC driver I do not know - I have been testing using the OLE DB provider).


    Peter Jamieson
    • Marked as answer by Martin Koenig Tuesday, October 11, 2011 6:36 AM
    • Unmarked as answer by Martin Koenig Tuesday, October 11, 2011 7:15 AM
    Tuesday, October 11, 2011 12:50 AM
  • Peter, thanks a lot. You're the champ!

    Martin

    Tuesday, October 11, 2011 6:36 AM
  • Peter

    Sorry to bother again. I was so excited about the solution to call a function I completely forgot about the stored procedure, sorry!

    If I call this stored procedure

    create procedure dbo.prcWordShell
    @SubNr int
    as 
        declare @Name varchar(35)
        set @Name = 'Peter'
        select @Name

    With this field parameters

    DATABASE  \d "C:\\Dummy.odc" \c " DSN=AKIS;trusted_connection=yes;DATABASE=BETA " \s " exec dbo.prcWordShell @SubNr = 1 " \h

    I do get the same error as with the function without an alias.

    I tried to reformat the SQL like this:

    execute dbo.prcWordShell @SubNr = 1

    execute('dbo.prcWordShell @SubNr = 1')

    But none of them worked.

    Would you have any idea how to address this issue.

    Thanks, Martin

    Tuesday, October 11, 2011 7:14 AM
  • Martin,

    Yes, I think the problem in this case is a different one to do with multistatement Stored Procedures. But I have never been completely sure, and in any case, I do not know how to solve it.

    If you do

    create procedure dbo.prcWordShell
    @SubNr int
    as  
        select 'Peter' AS [theName]

    and use something like

    execute [dbo].[prcWordShell] 1

    or something more like what you already have:

    execute dbo.prcWordShell @SubNr = 1

    then Word seems to do the right thing.

    However, if you even do this:

    create procedure dbo.prcWordShell
    @SubNr int
    as  

        SET NOCOUNT ON
        select 'Peter' AS [theName]

    then Word returns an error (right now, I am actually using Word 2003, OLE DB and OpenDataSource rather than a DATABASE field to lok at this, but I suspect the problem is the same as the one you are seeing)

    AIUI the "SET NOCOUNT ON" is supposed to prevent Transact-SQL from sending row counts back to the client, or some such, but even executing that one statement before anything else appears to cause problems.

    I thought the problem might have something to do with "Multiple Active Result Sets" and tried setting MARS Connection (or MARS_Connection) to true (and false), or MARSConn to yes or no (which one to use depends partly on the driver/provider etc.). But nothing made any difference. So either it /is/ something to do with multiple responses, and Word just does not know how to do the right thing, or it is something else. Either way, I do not know how to solve the problem - whenever anyone has asked about this, I have said that AFAIK using table-valued functions is the only way to go.

    FWIW I have also tried stuff like

    SET NOCOUNT ON;EXECUTE dbo.prcWordShell @SubNr = 1

    but that does not work here either.

    Perhaps someone who has a better understanding of what SQL Server is actually returning might have some suggestions, but

     a. Excel gets this sort of stuff right (AFAICR)

     b. Word gets this sort of stuff wrong

    Hardly surprising considering that Excel is more data-oriented, but it does suggest that the problem is on the client side, not the server side, and that a fix in Word or ODSO is needed. I think I mentioned this stuff a long time ago to MS, but I think it would take a problem report from a supported user (I am not) to get any further.

     

     

     


    Peter Jamieson
    Tuesday, October 11, 2011 10:10 AM
  • Hey Peter

    Thanks so much for getting back to me so quickly. Your finding is excellent. I wouldn't have reduced my stored procedure to this nor realized the difference.

    I did some digging and watched the communication between Word and SQL Server with the SQL Server Profiler, excellent tool.

    Word 2000 sends the SQL statement from the parameter \s as is straigth to the SQL Server.

    Word 2010 is unsing an external function (from an .dll) called sp_cursoropen:

    declare @p1 int set @p1=@p1
    declare @p3 int set @p3=8
    declare @p4 int set @p4=1
    declare @p5 int set @p5=2
    exec sp_cursoropen @p1 output,N'execute dbo.prcWordShell',@p3 output,@p4 output,@p5 output
    select @p1 'Cursor handle', @p3 'Scroll options', @p4 'Concurrency Options', @p5 'Rowcount'

    With only one select statement in the stored procedure you get this result.


    •     The first resultset contains nothing.
    •     The second resultset contains the rowcount variable = 1

    After the a fetch and a close will be sent to the database:

    -- Statement
    exec sp_cursorfetch @p1,16,1,1
    -- Statement
    set @p3=1
    set @p4=2
    exec sp_cursorfetch @p1,256,@p3 output,@p4 output
    select @p3, @p4
    -- Statement
    exec sp_cursorclose @p1

    Everything is hanky-dory.

    With more than only one select statement in the stored procedure (like a declaration of a variable) you get this result.

    declare @p1 int set @p1=0
    declare @p3 int set @p3=8
    declare @p4 int set @p4=8193
    declare @p5 int set @p5=0
    exec sp_cursoropen @p1 output,N'execute dbo.prcWordShell',@p3 output,@p4 output,@p5 output
    select @p1 'Cursor handle', @p3 'Scroll options', @p4 'Concurrency Options', @p5 'Rowcount'



    • The procedure "sp_cursoropen" dumps the resultset in the first reply.
    • Falsly it has a RowCount = 0 which will cause ODSO to stop and do not fetch any data. Maybe sp_cursoropen is not setting up server side data to fetch.


    I strongly believe that the error can be fixed in the implementation of the external function "sp_cursoropen".

    Do you have any access or know of anybody who could help me to get further with this issue? Do you have contacts to an SQL Server MVP?

    Thanks so much for your help!

    Martin

    Tuesday, October 11, 2011 2:51 PM
  • Martin,

    I had a deeper dive into the Profiler (still using Word 2003 with an OLE DB connection) and turned on display of everything I could find.

    Error 156

    Incorrect syntax near the keyword 'SET'

    Error 16397

    A server cursor cannot be opened on the given statement or statements. Use a default result set or client cursor.

    As far as I can tell, this means that Word's (ODSO's) code is opening a Server-side cursor which cannot process multiple statements (after SQL Server 6) and that Word (ODSO) should be using a "default result set" where a client-side cursor is used.

    The following KB article may be rather old, but it is highly likely that Word's code in this area also dates back to around 2000 (i.e. they introduced OLE DB support and ODSO (AFAIK) after Word 2000 so I would guess the code dates from around then). 

    http://support.microsoft.com/kb/156500

    <<

    Do you have any access or know of anybody who could help me to get further with this issue? Do you have contacts to an SQL Server MVP?

    >>
    I don't, but I'll ask Cindy (who may see this anyway).


    Peter Jamieson
    • Marked as answer by Martin Koenig Wednesday, March 23, 2016 9:10 AM
    Wednesday, October 12, 2011 8:04 AM
  • Hallo Martin

    My advice would be for you to answer once more, re-stating what, exactly, is "still not working". Then everyone leave the message alone so that the Contingent Staff pick it up and can easily see what's still not good. They have access to internal information and may be able to track this down.

    FWIW, I suspect Peter's assessment is close to the mark as far as the liklihood of this being a "technology mismatch" between Word's older mail merge capabilities and what newer SQL Server technologies are doing.

    I really haven't a clue about SQL Server, nor do I know anyone in that technology. But perhaps it has some kind of "compatibility mode" you could use for the sp's used for mail merge.

    The other possible workaround that occurs to me would be to link the sp's into Access as tables or queries then mail merge to that...


    Cindy Meister, VSTO/Word MVP
    Wednesday, October 12, 2011 11:45 AM
    Moderator
  • Hello Cindy and Peter

    Thanks so much for your effort. I would like to follow Cindy's advise and post this Reply for the Contingent Staff to pick it up.

    So please everybody leave this "message-honeypot" alone so the Contingent Staff picks it up. Thanks!

    I belive that Peter has pinned the problem down that there is a problem to deal with multiple statements within a stored procedure. The following scenario describes how to reproduce the problem.

    -----------------------------

    I'm using Word 2010 to call a stored procedure via the field DATABASE:

    {DATABASE  \d "C:\\Dummy.odc" \c " DSN=AKIS;trusted_connection=yes;DATABASE=BETA " \s " exec dbo.prcWordShell @SubNr = 1 " \h}

    This is the code of the stored procedure:

    create procedure dbo.prcWordShell
    @SubNr int
    as
    select 'Peter' AS [theName]

    The result is fine.

    As soon as I have more then one statement in the stored procedure I receive an error in Word 2010 when I update the DATABASE field.

    create procedure dbo.prcWordShell
    @SubNr int
    as 
        SET NOCOUNT ON
        select 'Peter' AS [theName]

    -----------------------------

    I did some digging and watched the communication between Word and SQL Server with the SQL Server Profiler, an excellent tool.

    Word 2000 sends the SQL statement from the parameter \s as is straigth to the SQL Server.

    Word 2010 is unsing an external function (from an .dll) called sp_cursoropen:

    declare @p1 int set @p1=@p1
    declare @p3 int set @p3=8
    declare @p4 int set @p4=1
    declare @p5 int set @p5=2
    exec sp_cursoropen @p1 output,N'execute dbo.prcWordShell',@p3 output,@p4 output,@p5 output
    select @p1 'Cursor handle', @p3 'Scroll options', @p4 'Concurrency Options', @p5 'Rowcount'

    With only one select statement in the stored procedure you get this result.

    • The first resultset contains nothing.
    • The second resultset contains the rowcount variable = 1

    After that a fetch and a close will be sent to the database to retrieve the data:

    -- Statement
    exec sp_cursorfetch @p1,16,1,1
    -- Statement
    set @p3=1
    set @p4=2
    exec sp_cursorfetch @p1,256,@p3 output,@p4 output
    select @p3, @p4
    -- Statement
    exec sp_cursorclose @p1

    Everything is hanky-dory.

    With more than only one select statement in the stored procedure (like a declaration of a variable) you get this result.

    declare @p1 int set @p1=0
    declare @p3 int set @p3=8
    declare @p4 int set @p4=8193
    declare @p5 int set @p5=0
    exec sp_cursoropen @p1 output,N'execute dbo.prcWordShell',@p3 output,@p4 output,@p5 output
    select @p1 'Cursor handle', @p3 'Scroll options', @p4 'Concurrency Options', @p5 'Rowcount'

    • The procedure "sp_cursoropen" dumps the resultset in the first reply.
    • Falsly it has a RowCount = 0 which will cause ODSO to stop and do not fetch any data. Maybe sp_cursoropen is not setting up server side data to fetch.

    I strongly believe that the error can be fixed in the implementation of the external function "sp_cursoropen".

    Thanks so much for your help!

    Martin

     

    Friday, October 21, 2011 8:02 AM