Need paragraph breaks in pass through query

Hello, I'm trying to use a pass through query in which I want to
recreate something that I can do in the query analyzer. I'm using
Access 2K as front end and MS SQL Server 2000 as back end.
I want to create a view, use a select statement using this view linked
to a table and then I want to drop the view.
In the query analyzer it works perfectly. This is a shrunk version of
what I have there
CREATE VIEW vwInventory AS SELECT InventoryID, Type, Class, Color WHERE
Type = 'Textiles'
GO
SELECT OrderID, InventoryID, OrderDate, OrderNumber, Status FROM Orders
LEFT JOIN vwInventory ON Orders.InventoryID = vwInventory.InventoryID
WHERE Orders.Status = 'Finished'
GO
DROP VIEW vwInventory
GO
As I said before, I do get the records I'm supposed to get, but when I
combine all of this into a single sentence (to create the pass through
query) it does not. For some reason the whole thing does not work
without the paragraph breaks.
This is what I have in my pass through query:
loQdf.SQL ="CREATE VIEW vwInventory AS SELECT InventoryID, Type, Class,
Color WHERE InventoryID = 45093 GO SELECT OrderID, InventoryID,
OrderDate, OrderNumber, Status FROM Orders LEFT JOIN vwInventory ON
Orders.InventoryID = vwInventory.InventoryID WHERE Orders.Status =
'Finished' GO DROP VIEW vwInventory GO"
Could anyone tell me if I need to add a special character in order to
break these lines as paragraphs in the pass through query so all 3
items can work?
Thanks.The only reason it works in QA is because each batch (separated by GO) is
executed individually and in order. Note that "GO" is a batch separator
understood by QA - it has no formal definition within the t-sql language.
A pass-through query is a single batch. Therefore your approach is not
possible - or pratical for that matter. Why do you need to create a
single-use view? What exactly do you hope to gain from this other than
requiring special priveleges for the user attempting to execute such a
query? Everything you have shown can be more easily done within a single
query - there is nothing complicated about the view.
Note that your view definition is incomplete and should generate an error
since it has no FROM clause.|||Scott Morris wrote:
> The only reason it works in QA is because each batch (separated by GO) is
> executed individually and in order. Note that "GO" is a batch separator
> understood by QA - it has no formal definition within the t-sql language.
> A pass-through query is a single batch. Therefore your approach is not
> possible - or pratical for that matter. Why do you need to create a
> single-use view? What exactly do you hope to gain from this other than
> requiring special priveleges for the user attempting to execute such a
> query? Everything you have shown can be more easily done within a single
> query - there is nothing complicated about the view.
> Note that your view definition is incomplete and should generate an error
> since it has no FROM clause.
Hello Scott, thanks for replying. I needed to create the view on the
fly because I need to change the parameter in the where clause.
I understand about the GO being only good for the Query Analyzer and
that a pass through query is a single batch. Therefore, I was able to
accomplish what I needed by running my statement as 3 queries, 1
action, 1 normal, and 1 action. By running them separately, the whole
thing works.
Thanks for all you help.
JR.|||ILCSP@.NETZERO.NET,
1-

> As I said before, I do get the records I'm supposed to get, but when I
> combine all of this into a single sentence (to create the pass through
> query) it does not. For some reason the whole thing does not work
> without the paragraph breaks.
why to use a "pass through query" and not a stored procedure?
2-
if you insist, try:
SELECT OrderID, InventoryID, OrderDate, OrderNumber, Status
FROM
Orders
LEFT JOIN
(
SELECT InventoryID, Type, Class, Color
from table_used_in_the_view
WHERE Type = 'Textiles'
) as vwInventory
ON Orders.InventoryID = vwInventory.InventoryID
WHERE
Orders.Status = 'Finished'
GO
AMB
"ILCSP@.NETZERO.NET" wrote:

> Hello, I'm trying to use a pass through query in which I want to
> recreate something that I can do in the query analyzer. I'm using
> Access 2K as front end and MS SQL Server 2000 as back end.
> I want to create a view, use a select statement using this view linked
> to a table and then I want to drop the view.
> In the query analyzer it works perfectly. This is a shrunk version of
> what I have there
> CREATE VIEW vwInventory AS SELECT InventoryID, Type, Class, Color WHERE
> Type = 'Textiles'
> GO
> SELECT OrderID, InventoryID, OrderDate, OrderNumber, Status FROM Orders
> LEFT JOIN vwInventory ON Orders.InventoryID = vwInventory.InventoryID
> WHERE Orders.Status = 'Finished'
> GO
> DROP VIEW vwInventory
> GO
> As I said before, I do get the records I'm supposed to get, but when I
> combine all of this into a single sentence (to create the pass through
> query) it does not. For some reason the whole thing does not work
> without the paragraph breaks.
> This is what I have in my pass through query:
> loQdf.SQL ="CREATE VIEW vwInventory AS SELECT InventoryID, Type, Class,
> Color WHERE InventoryID = 45093 GO SELECT OrderID, InventoryID,
> OrderDate, OrderNumber, Status FROM Orders LEFT JOIN vwInventory ON
> Orders.InventoryID = vwInventory.InventoryID WHERE Orders.Status =
> 'Finished' GO DROP VIEW vwInventory GO"
> Could anyone tell me if I need to add a special character in order to
> break these lines as paragraphs in the pass through query so all 3
> items can work?
> Thanks.
>|||(ILCSP@.NETZERO.NET) writes:
> Hello Scott, thanks for replying. I needed to create the view on the
> fly because I need to change the parameter in the where clause.
Create am inline table function instead. That is essentially a
parameterised view:
CREATE FUNCTION vwInventory(@.InventoryID int) RETURNS TABLE AS
RETURN (SELECT InventoryID, Type, Class, Color
WHERE InventoryID = @.InventoryID)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On 17 Jan 2006 13:11:26 -0800, ILCSP@.NETZERO.NET wrote:
(snip)
>Hello Scott, thanks for replying. I needed to create the view on the
>fly because I need to change the parameter in the where clause.
Hi ILCSP,
You don't need to recreate the view just to change the parameter. You
can include that i the actual query.
Just create your view (once!!) as
CREATE VIEW vwInventory
AS SELECT InventoryID, Type, Class, Color
FROM SomeTable -- not specified in your original post
Then change the SELECT statement to:
SELECT OrderID, InventoryID, OrderDate, OrderNumber, Status
FROM Orders
LEFT JOIN vwInventory
ON Orders.InventoryID = vwInventory.InventoryID
AND vwInventory.Type = 'Textiles'
WHERE Orders.Status = 'Finished'
Or, you can ditch the view completely and do a straight select from the
base tables:
SELECT OrderID, InventoryID, OrderDate, OrderNumber, Status
FROM Orders
LEFT JOIN SomeTable -- not specified in your original post
ON Orders.InventoryID = SomeTable.InventoryID
AND SomeTable.Type = 'Textiles'
WHERE Orders.Status = 'Finished'
Hugo Kornelis, SQL Server MVP