Re: "EXEC" in SQL Server 2000 Views

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

From: Kalen Delaney (replies_at_public_newsgroups.com)
Date: 01/19/05 

Date: Wed, 19 Jan 2005 15:31:28 -0800

Hi Sue

You misunderstood something, somewhere. It is NOT possible to include an
EXEC as part of a view definition, in ANY version.

You say you cannot issue the SELECT against the view, but you haven't told
us why not. Do you get an error message? What does it say?

You can issue a view against remote tables:

CREATE VIEW dbo.Test
 AS
 SELECT a.Incident_no, a.Description,
                 b.Person_ID AS Reportedbyid, c.Name as Reportedby
   FROM LinkedServerName...a
   INNER JOIN LinkedServerName...b
... etc. 

-- 
HTH
----------------
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Sue" <Sue@discussions.microsoft.com> wrote in message 
news:5A64E47D-F208-4428-9BA7-9097EF968D84@microsoft.com...
> From a View in my SQL Server 2000 db I need to either EXEC a pass-through
> query (to an Access 97 db) or EXEC a stored procedure which itself 
> executes
> the pass-through query (via the OPENQUERY command and a Linked Server).
>
> I understand that it is possible to include an EXEC statement in a View in
> SQL Server 2000 where it wasn't in SQL 7. I can CREATE the following View:
>
> CREATE VIEW dbo.Test
> AS
> SELECT * FROM openquery(LinkedServerName,
> 'SELECT a.Incident_no,
> a.Description,
>                 b.Person_ID AS Reportedbyid,
> c.Name as Reportedby
>   FROM   (a
>   INNER JOIN b
>   ON a.Incident_no = b.Incident_No)
>   INNER JOIN c
>   ON c.Person_id = b.Person_id
>   WHERE  (b.Involvement_Type = "REPTBY"
>     AND  a.Completed = 0)
> ')
>
> The View creates OK but then I cannot issue a SELECT statement against it 
> in
> Query Analyzer. I have put the pass-through query in a stored procedure 
> and
> tried to create the View as follows:
>
> CREATE VIEW dbo.Test
> AS
> EXEC Test_SP
>
> but this doesn't work.
>
> Does anyone have any idea how I can create this View to execute the
> pass-through query or the stored procedure?
>
> Thanks for your assistance,
>
> Sue
>

Relevant Pages

  • Re: HIT and MISS
    ... clear both data cache and buffer cache but still does ... Create a stored procedure demo_sp as dbo, and grant exec rights to some ... This is because SQL Server first looks up if the user owns a procedure ...
    (comp.databases.ms-sqlserver)
  • Re: FOR XML EXPLICIT Over Multiple Stored Procedures
    ... I don't think this is easily possible in SQL Server 2000. ... > To call one stored procedure, eg. sp_Referrals, which in turn calls ... > two stored procedures that each return their own XML structure. ... > exec sp_GetReferenceData 'Titles' ...
    (microsoft.public.sqlserver.xml)
  • Re: sp_is_sqlagent_starting does not work
    ... temporary table and then check the existence of sqlserveragent in the ... INSERT serv EXEC master..xp_cmdshell 'NET START' ... SQL Server MVP ... > From a SQL stored procedure, is there a way to find out if the ...
    (microsoft.public.sqlserver.programming)
  • Re: SELECT TOP with variable not working
    ... > Columnist, SQL Server Professional ... > You can certainly execute the SQL with just the EXEC command inthe ... > stored procedure, but I prefer using sp_executesql, especially since I ...
    (microsoft.public.sqlserver.server)
  • Re: noise words, @@ERROR, and stop and resume indexing
    ... EXEC sp_fulltext_catalog 'adsfull', 'stop' ... > 1) check noise words inside stored procedure ... > can be solved by changing the language specific file with noise words ... ...
    (microsoft.public.sqlserver.fulltext)