Re: a view based on stored procedure

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Tom Moreau (tom_at_dont.spam.me.cips.ca)
Date: 03/17/04 

Date: Wed, 17 Mar 2004 14:47:39 -0500

To make a very long story short, put the following at the beginning of the
proc:

if (1 = 2)
select
    Col1
, Col2
from
    MyTable

... only modify it to have the exact same column names and positions as the
final SELECT in your query. Same goes for the table name. The optimizer
needs to be able to sniff the metadata for your result set and this is a
workaround. 

--
Tom
---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Biva" <biva.yauchler@redprairie.com> wrote in message
news:4058a9d2$0$38876$39cecf19@news.twtelecom.net...
Hello All,
SQL 2K w/ SP3a
I am trying to create a view based on a stored procedure so I can use that
view in Crystal Reports.
When I execute the stored procedure in Query Analyzer, it returns 1083 rows
without an error.
I am trying to create the view using the following select statement:
SELECT * FROM OPENROWSET('SQLOLEDB.1',
'DRIVER={SQL Server};SERVER=gorilla;Database=Corp;UID=fin;PWD=acct',
   'EXEC rp_ResAvailPlanInfo')
When I execute the above statement from Query Analyzer, I get the following
error:
Could not process object 'EXEC rp_ResAvailPlanInfo'. The OLE DB provider
'SQLOLEDB.1' indicates that the object has no columns.
OLE DB error trace [Non-interface error:  OLE DB provider unable to process
the object:ProviderName='SQLOLEDB.1', Query=EXEC rp_ResAvailPlanInfo'].
In the stored procedure, I am selecting data from 2 views, opening a cursor,
inserting data into a permanent table and selecting data from that permanent
table.
All columns are named.
Any help in this matter would be greatly appreciated.
Thanks,
sqlgirl

Relevant Pages

  • a view based on stored procedure
    ... When I execute the stored procedure in Query Analyzer, ... OLE DB error trace [Non-interface error: OLE DB provider unable to process ... In the stored procedure, I am selecting data from 2 views, opening a cursor, ... inserting data into a permanent table and selecting data from that permanent ...
    (microsoft.public.sqlserver.programming)
  • Re: Return from store procedure.
    ... Check each OLE DB status value, ... The error is in the application code, not in the stored procedure. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: How to get list of EventClasses in MSSQLServer2000
    ... statement inside a stored procedure has completed.') ... SQL Server statement or stored procedure.') ... Plan','Displays the plan tree of the Transact-SQL statement executed.') ... Login','Occurs when a SQL Server login is added or removed; ...
    (microsoft.public.sqlserver.security)
  • Re: Is ADO.NET OleDb .NET Data Provider a COM object?
    ... Okay, okay... ... OLE DB is a one-size-fits-all set of providers that can access anything from a relational database to a tuna salad. ... in order to access SQL Server we used OLE DB to access ODBC or SQL Server. ... In a similar way, just because you can connect to MySQL, Paradox, Oracle or FarkleStar databases from an OLE DB or other OSFA provider does not mean your code can interface with these other back-ends. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Accessing FoxPro Free Table
    ... which the SQL Server service is running. ... account, ... > If you are creating a stored procedure and you want> to make sure that the procedure definition cannot be> viewed by other users, you can use the WITH ENCRYPTION> clause. ... The procedure definition is then stored in an> unreadable form. ...
    (microsoft.public.data.odbc)