Re: "EXEC" in SQL Server 2000 Views
From: Kalen Delaney (replies_at_public_newsgroups.com)
Date: 01/20/05
- Next message: Shahriar: "Re: SQL Mail"
- Previous message: oj: "Re: Proxy Account"
- In reply to: Sue: "Re: "EXEC" in SQL Server 2000 Views"
- Next in thread: oj: "Re: "EXEC" in SQL Server 2000 Views"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 19 Jan 2005 22:36:59 -0800
Have you successfully run the SELECT by itself? You need to make sure that
works before trying to create a view.
-- HTH ---------------- Kalen Delaney SQL Server MVP www.SolidQualityLearning.com "Sue" <Sue@discussions.microsoft.com> wrote in message news:F58BADC3-4E88-46AD-97E3-A0C1B39E3C0B@microsoft.com... > Hi Kalen, > > Thanks for this clarification. If I create the View with the pass-through > query in it as follows: > CREATE VIEW dbo.TestReportedby > 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 perfectly OK but when I try to select using the view by > > select * from testreportedby > > I get the following error: > > Could not execute query against OLE DB provider 'MSDASQL'. > OLE DB error trace [OLE/DB Provider 'MSDASQL' ICommandText::Execute > returned > 0x80040e10]. > > with no other details. > > Also, I tried to create the query in the view as you suggested by > referencing the tables in the linked server directly as in: > > SELECT a.Incident_no, > a.Description, > b.Person_ID AS Reportedbyid, > c.Name as Reportedby > FROM (LinkedServerName.DBName.Owner.Table1 a > INNER JOIN LinkedServerName.DBName.Owner.Table2 b > ON a.Incident_no = b.Incident_No) > INNER JOIN LinkedServerName.DBName.Owner.Table3 c > ON c.Person_id = b.Person_id > WHERE (b.Involvement_Type = 'REPTBY' > AND a.Completed = 0) > > but first get the message: > > Invalid use of schema and/or catalog for OLE DB provider 'MSDASQL'. A > four-part name was supplied, but the provider does not expose the > necessary > interfaces to use a catalog and/or schema. > OLE DB error trace [Non-interface error]. > > If I remove the Owner and/or DBName I get: > > Server: Msg 208, Level 16, State 1, Procedure TestReportedby, Line 5 > Invalid object name 'LinkedServerName.Table1'. > Server: Msg 208, Level 16, State 1, Procedure TestReportedby, Line 5 > Invalid object name 'LinkedServerName.Table2'. > Server: Msg 208, Level 16, State 1, Procedure TestReportedby, Line 5 > Invalid object name 'LinkedServerName.Table3'. > > I can't seem to find any information about how I specify the > fully-qualified > object name for an Access 97 table. Obviously the SQL one > (linked_server_name.catalog.schema.object_name) isn't valid for Access. > Are > you able to help me with this? > > Much appreciated. > > Sue > > > "Kalen Delaney" wrote: > >> 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 >> > >> >> >>
- Next message: Shahriar: "Re: SQL Mail"
- Previous message: oj: "Re: Proxy Account"
- In reply to: Sue: "Re: "EXEC" in SQL Server 2000 Views"
- Next in thread: oj: "Re: "EXEC" in SQL Server 2000 Views"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
