Professional Web Applications Themes

Stored procedures and UNION (SQL Server 2000) - Microsoft SQL / MS SQL Server

SELECT * FROM OPENQUERY (DEVSVR1, 'EXEC MSIS.DBO.X 1,2,6,"20030101","20031231",1') UNION SELECT * FROM OPENQUERY (DEVSVR1, 'EXEC MSIS.DBO.X 1,6,10,"20030101","20031231",2') This will come with message: Could not execute query against OLE DB provider 'SQLOLEDB'. The provider could not support a required property. The provider indicates that conflicts occurred with other properties or requirements. [OLE/DB provider returned message: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.] OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ICommandText::Execute returned 0x80040e21: EXEC MSIS.DBO.X 1,6,10,"20030101","20031231",2[PROPID=DBPROP_SERVERCURSOR VALUE=True STATUS=DBPROPSTATUS_CONFLICTING]]. Each part of UNION will execute without problems. Any ideas how to do ...

  1. #1

    Default Stored procedures and UNION (SQL Server 2000)

    SELECT *
    FROM
    OPENQUERY (DEVSVR1,
    'EXEC MSIS.DBO.X 1,2,6,"20030101","20031231",1')

    UNION

    SELECT *
    FROM
    OPENQUERY (DEVSVR1,
    'EXEC MSIS.DBO.X 1,6,10,"20030101","20031231",2')

    This will come with message:

    Could not execute query against OLE DB provider 'SQLOLEDB'. The provider
    could not support a required property. The provider indicates that conflicts
    occurred with other properties or requirements.
    [OLE/DB provider returned message: Multiple-step OLE DB operation generated
    errors. Check each OLE DB status value, if available. No work was done.]
    OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ICommandText::Execute
    returned 0x80040e21: EXEC MSIS.DBO.X
    1,6,10,"20030101","20031231",2[PROPID=DBPROP_SERVERCURSOR VALUE=True
    STATUS=DBPROPSTATUS_CONFLICTING]].


    Each part of UNION will execute without problems.

    Any ideas how to do UNION on SPs?

    Thanks

    MH


    Marius Horak Guest

  2. #2

    Default Re: Stored procedures and UNION (SQL Server 2000)

    Insert the results into a temp table and then union the temp tables.

    --

    Andrew J. Kelly
    SQL Server MVP


    "Marius Horak" <someoneeurope.eu> wrote in message
    news:Ocn73UhQDHA.2224TK2MSFTNGP12.phx.gbl...
    > SELECT *
    > FROM
    > OPENQUERY (DEVSVR1,
    > 'EXEC MSIS.DBO.X 1,2,6,"20030101","20031231",1')
    >
    > UNION
    >
    > SELECT *
    > FROM
    > OPENQUERY (DEVSVR1,
    > 'EXEC MSIS.DBO.X 1,6,10,"20030101","20031231",2')
    >
    > This will come with message:
    >
    > Could not execute query against OLE DB provider 'SQLOLEDB'. The provider
    > could not support a required property. The provider indicates that
    conflicts
    > occurred with other properties or requirements.
    > [OLE/DB provider returned message: Multiple-step OLE DB operation
    generated
    > errors. Check each OLE DB status value, if available. No work was done.]
    > OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ICommandText::Execute
    > returned 0x80040e21: EXEC MSIS.DBO.X
    > 1,6,10,"20030101","20031231",2[PROPID=DBPROP_SERVERCURSOR VALUE=True
    > STATUS=DBPROPSTATUS_CONFLICTING]].
    >
    >
    > Each part of UNION will execute without problems.
    >
    > Any ideas how to do UNION on SPs?
    >
    > Thanks
    >
    > MH
    >
    >

    Andrew J. Kelly Guest

  3. #3

    Default Re: Stored procedures and UNION (SQL Server 2000)

    Hi Andrew,

    Thank you for answer but my question about UNION and SP.

    MH

    "Andrew J. Kelly" <sqlmvpnooospamshadhawk.com> wrote in message
    news:ekJlneiQDHA.1072TK2MSFTNGP10.phx.gbl...
    > Insert the results into a temp table and then union the temp tables.
    >
    > --
    >
    > Andrew J. Kelly
    > SQL Server MVP
    >
    >
    > "Marius Horak" <someoneeurope.eu> wrote in message
    > news:Ocn73UhQDHA.2224TK2MSFTNGP12.phx.gbl...
    > > SELECT *
    > > FROM
    > > OPENQUERY (DEVSVR1,
    > > 'EXEC MSIS.DBO.X 1,2,6,"20030101","20031231",1')
    > >
    > > UNION
    > >
    > > SELECT *
    > > FROM
    > > OPENQUERY (DEVSVR1,
    > > 'EXEC MSIS.DBO.X 1,6,10,"20030101","20031231",2')
    > >
    > > This will come with message:
    > >
    > > Could not execute query against OLE DB provider 'SQLOLEDB'. The
    provider
    > > could not support a required property. The provider indicates that
    > conflicts
    > > occurred with other properties or requirements.
    > > [OLE/DB provider returned message: Multiple-step OLE DB operation
    > generated
    > > errors. Check each OLE DB status value, if available. No work was done.]
    > > OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ICommandText::Execute
    > > returned 0x80040e21: EXEC MSIS.DBO.X
    > > 1,6,10,"20030101","20031231",2[PROPID=DBPROP_SERVERCURSOR VALUE=True
    > > STATUS=DBPROPSTATUS_CONFLICTING]].
    > >
    > >
    > > Each part of UNION will execute without problems.
    > >
    > > Any ideas how to do UNION on SPs?
    > >
    > > Thanks
    > >
    > > MH
    > >
    > >
    >
    >

    Marius Horak Guest

  4. #4

    Default Re: Stored procedures and UNION (SQL Server 2000)

    Yes I understand that. I assumed you tried the queries independently
    (without the union) and they work, but when you add the union they don't.
    If that is the case then for some reason the provider does not allow unions
    between executed sp's so I suggested you place the results of each sp into a
    temp table and then join those as a work around. I don't do enough with
    linked servers to know for sure if what you are trying to do is directly
    possible or not.

    --

    Andrew J. Kelly
    SQL Server MVP


    "Marius Horak" <someoneeurope.eu> wrote in message
    news:el7KHdFRDHA.3664tk2msftngp13.phx.gbl...
    > Hi Andrew,
    >
    > Thank you for answer but my question about UNION and SP.
    >
    > MH
    >
    > "Andrew J. Kelly" <sqlmvpnooospamshadhawk.com> wrote in message
    > news:ekJlneiQDHA.1072TK2MSFTNGP10.phx.gbl...
    > > Insert the results into a temp table and then union the temp tables.
    > >
    > > --
    > >
    > > Andrew J. Kelly
    > > SQL Server MVP
    > >
    > >
    > > "Marius Horak" <someoneeurope.eu> wrote in message
    > > news:Ocn73UhQDHA.2224TK2MSFTNGP12.phx.gbl...
    > > > SELECT *
    > > > FROM
    > > > OPENQUERY (DEVSVR1,
    > > > 'EXEC MSIS.DBO.X 1,2,6,"20030101","20031231",1')
    > > >
    > > > UNION
    > > >
    > > > SELECT *
    > > > FROM
    > > > OPENQUERY (DEVSVR1,
    > > > 'EXEC MSIS.DBO.X 1,6,10,"20030101","20031231",2')
    > > >
    > > > This will come with message:
    > > >
    > > > Could not execute query against OLE DB provider 'SQLOLEDB'. The
    > provider
    > > > could not support a required property. The provider indicates that
    > > conflicts
    > > > occurred with other properties or requirements.
    > > > [OLE/DB provider returned message: Multiple-step OLE DB operation
    > > generated
    > > > errors. Check each OLE DB status value, if available. No work was
    done.]
    > > > OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ICommandText::Execute
    > > > returned 0x80040e21: EXEC MSIS.DBO.X
    > > > 1,6,10,"20030101","20031231",2[PROPID=DBPROP_SERVERCURSOR VALUE=True
    > > > STATUS=DBPROPSTATUS_CONFLICTING]].
    > > >
    > > >
    > > > Each part of UNION will execute without problems.
    > > >
    > > > Any ideas how to do UNION on SPs?
    > > >
    > > > Thanks
    > > >
    > > > MH
    > > >
    > > >
    > >
    > >
    >
    >

    Andrew J. Kelly Guest

  5. #5

    Default Re: Stored procedures and UNION (SQL Server 2000)

    Hi Andrew,

    Thank you for answer.
    I do not have a problem with getting data. I can get it in a number of ways.
    My problem is: why UNION and SP do not work in the case I described and how
    to fix it (not how to get data).
    I have a master degree in computing and now I need it (UNION and SP) for my
    PhD.

    Regards,


    MH



    Marius Horak Guest

Similar Threads

  1. dt_ Stored Procedures
    By chopper in forum ASP Database
    Replies: 2
    Last Post: July 20th, 04:06 PM
  2. turning Resultsets Using Sql Server Stored Procedures...
    By Amber in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: October 7th, 03:05 AM
  3. Replies: 7
    Last Post: September 11th, 07:03 PM
  4. Stored Procedures and 4GL
    By Ahmer Sajjad in forum Informix
    Replies: 1
    Last Post: September 9th, 01:23 PM
  5. Can "Computed Column" be a stored procedure in SQL Server 2000?
    By Bill in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 8th, 06:47 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139