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

Need Help: SP fast in QA, slow in APP

I have a strored procedure which takes .016 seconds in Query Analyzer,
but takes 16.8 seconds when run in my ASP.NET application. [yes, you rea
d
that right]
I've run profiler to find this info.
The machine has all the updates and service packs.
Where should I look next for an answer?
Thanks in advance.
Server:
Windows 2000 Server
SQL Server 2000 Standard
SP3I suspect that your problem might be related to parameter sniffing. Here
are a few links:
http://groups-beta.google.com/group...e4a2438bed08aca
http://groups-beta.google.com/group...eb556c8dfb6a82c
Keith
"Rich Miller" <rooster575@.hotmail.com> wrote in message
news:OZ8bHOvLFHA.3844@.TK2MSFTNGP14.phx.gbl...
> I have a strored procedure which takes .016 seconds in Query Analyzer,
> but takes 16.8 seconds when run in my ASP.NET application. [yes, you r
ead
> that right]
> I've run profiler to find this info.
> The machine has all the updates and service packs.
> Where should I look next for an answer?
> Thanks in advance.
> Server:
> Windows 2000 Server
> SQL Server 2000 Standard
> SP3
>
>|||"Rich Miller" <rooster575@.hotmail.com> wrote in message
news:OZ8bHOvLFHA.3844@.TK2MSFTNGP14.phx.gbl...
>I have a strored procedure which takes .016 seconds in Query Analyzer,
> but takes 16.8 seconds when run in my ASP.NET application. [yes, you r
ead
> that right]
> I've run profiler to find this info.
> The machine has all the updates and service packs.
> Where should I look next for an answer?
>
Also look at the SET settings used by ASP.NET. If you have any indexed
views or indexes on computed columns they cannot be used unless your
connection settings are correct.
Also when you say that it runs in .016 seconds in QA, how are you running
it? Just running the body of the stored procedure with hard-coded values is
not the same. You can always capture application's activity from Profiler
and replay it in QA.
David|||David:
Thanks for the response.
To answer your questions:
What I am doing is running Profiler,
1) watching the line " exec mySP_getMatch @.ClientID=2, @.thisOther=1 " on
Profiler, as the web app fires the sp.
2) Cutting and pasting the exact line that was run with ASP.NET into Query
Analyzer
3) Running the query. [returns a value immediately]
Also, the SP is just grabbing values from a table, with indexes where
necessary.
No views or computed columns.
What do you mean by the "SET" settings?
This one has me perplexed.
I've never had a case where performance what night and day when comparing a
query run in asp.net vs. query analyzer.
Thanks,
Rich
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:uf2pAhvLFHA.3868@.TK2MSFTNGP10.phx.gbl...
> "Rich Miller" <rooster575@.hotmail.com> wrote in message
> news:OZ8bHOvLFHA.3844@.TK2MSFTNGP14.phx.gbl...
>
> Also look at the SET settings used by ASP.NET. If you have any indexed
> views or indexes on computed columns they cannot be used unless your
> connection settings are correct.
> Also when you say that it runs in .016 seconds in QA, how are you running
> it? Just running the body of the stored procedure with hard-coded values
> is not the same. You can always capture application's activity from
> Profiler and replay it in QA.
> David
>

Need Help: SP fast in QA, slow in APP

I have a strored procedure which takes .016 seconds in Query Analyzer,
but takes 16.8 seconds when run in my ASP.NET application. [yes, you read
that right]
I've run profiler to find this info.
The machine has all the updates and service packs.
Where should I look next for an answer?
Thanks in advance.
Server:
Windows 2000 Server
SQL Server 2000 Standard
SP3
I suspect that your problem might be related to parameter sniffing. Here
are a few links:
http://groups-beta.google.com/group/...4a2438bed08aca
http://groups-beta.google.com/group/...b556c8dfb6a82c
Keith
"Rich Miller" <rooster575@.hotmail.com> wrote in message
news:OZ8bHOvLFHA.3844@.TK2MSFTNGP14.phx.gbl...
> I have a strored procedure which takes .016 seconds in Query Analyzer,
> but takes 16.8 seconds when run in my ASP.NET application. [yes, you read
> that right]
> I've run profiler to find this info.
> The machine has all the updates and service packs.
> Where should I look next for an answer?
> Thanks in advance.
> Server:
> Windows 2000 Server
> SQL Server 2000 Standard
> SP3
>
>
|||"Rich Miller" <rooster575@.hotmail.com> wrote in message
news:OZ8bHOvLFHA.3844@.TK2MSFTNGP14.phx.gbl...
>I have a strored procedure which takes .016 seconds in Query Analyzer,
> but takes 16.8 seconds when run in my ASP.NET application. [yes, you read
> that right]
> I've run profiler to find this info.
> The machine has all the updates and service packs.
> Where should I look next for an answer?
>
Also look at the SET settings used by ASP.NET. If you have any indexed
views or indexes on computed columns they cannot be used unless your
connection settings are correct.
Also when you say that it runs in .016 seconds in QA, how are you running
it? Just running the body of the stored procedure with hard-coded values is
not the same. You can always capture application's activity from Profiler
and replay it in QA.
David
|||David:
Thanks for the response.
To answer your questions:
What I am doing is running Profiler,
1) watching the line " exec mySP_getMatch @.ClientID=2, @.thisOther=1 " on
Profiler, as the web app fires the sp.
2) Cutting and pasting the exact line that was run with ASP.NET into Query
Analyzer
3) Running the query. [returns a value immediately]
Also, the SP is just grabbing values from a table, with indexes where
necessary.
No views or computed columns.
What do you mean by the "SET" settings?
This one has me perplexed.
I've never had a case where performance what night and day when comparing a
query run in asp.net vs. query analyzer.
Thanks,
Rich
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:uf2pAhvLFHA.3868@.TK2MSFTNGP10.phx.gbl...
> "Rich Miller" <rooster575@.hotmail.com> wrote in message
> news:OZ8bHOvLFHA.3844@.TK2MSFTNGP14.phx.gbl...
>
> Also look at the SET settings used by ASP.NET. If you have any indexed
> views or indexes on computed columns they cannot be used unless your
> connection settings are correct.
> Also when you say that it runs in .016 seconds in QA, how are you running
> it? Just running the body of the stored procedure with hard-coded values
> is not the same. You can always capture application's activity from
> Profiler and replay it in QA.
> David
>

Need Help: SP fast in QA, slow in APP

I have a strored procedure which takes .016 seconds in Query Analyzer,
but takes 16.8 seconds when run in my ASP.NET application. [yes, you read
that right]
I've run profiler to find this info.
The machine has all the updates and service packs.
Where should I look next for an answer?
Thanks in advance.
Server:
Windows 2000 Server
SQL Server 2000 Standard
SP3I suspect that your problem might be related to parameter sniffing. Here
are a few links:
http://groups-beta.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/a8f61dc6ac3aedcd/1e4a2438bed08aca?q=parameter+sniffing+group:microsoft.public.sqlserver.*#1e4a2438bed08aca
http://groups-beta.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/b6bf1ec648ae8815/3eb556c8dfb6a82c?q=parameter+sniffing+group:microsoft.public.sqlserver.*#3eb556c8dfb6a82c
--
Keith
"Rich Miller" <rooster575@.hotmail.com> wrote in message
news:OZ8bHOvLFHA.3844@.TK2MSFTNGP14.phx.gbl...
> I have a strored procedure which takes .016 seconds in Query Analyzer,
> but takes 16.8 seconds when run in my ASP.NET application. [yes, you read
> that right]
> I've run profiler to find this info.
> The machine has all the updates and service packs.
> Where should I look next for an answer?
> Thanks in advance.
> Server:
> Windows 2000 Server
> SQL Server 2000 Standard
> SP3
>
>|||"Rich Miller" <rooster575@.hotmail.com> wrote in message
news:OZ8bHOvLFHA.3844@.TK2MSFTNGP14.phx.gbl...
>I have a strored procedure which takes .016 seconds in Query Analyzer,
> but takes 16.8 seconds when run in my ASP.NET application. [yes, you read
> that right]
> I've run profiler to find this info.
> The machine has all the updates and service packs.
> Where should I look next for an answer?
>
Also look at the SET settings used by ASP.NET. If you have any indexed
views or indexes on computed columns they cannot be used unless your
connection settings are correct.
Also when you say that it runs in .016 seconds in QA, how are you running
it? Just running the body of the stored procedure with hard-coded values is
not the same. You can always capture application's activity from Profiler
and replay it in QA.
David|||David:
Thanks for the response.
To answer your questions:
What I am doing is running Profiler,
1) watching the line " exec mySP_getMatch @.ClientID=2, @.thisOther=1 " on
Profiler, as the web app fires the sp.
2) Cutting and pasting the exact line that was run with ASP.NET into Query
Analyzer
3) Running the query. [returns a value immediately]
Also, the SP is just grabbing values from a table, with indexes where
necessary.
No views or computed columns.
What do you mean by the "SET" settings?
This one has me perplexed.
I've never had a case where performance what night and day when comparing a
query run in asp.net vs. query analyzer.
Thanks,
Rich
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:uf2pAhvLFHA.3868@.TK2MSFTNGP10.phx.gbl...
> "Rich Miller" <rooster575@.hotmail.com> wrote in message
> news:OZ8bHOvLFHA.3844@.TK2MSFTNGP14.phx.gbl...
>>I have a strored procedure which takes .016 seconds in Query Analyzer,
>> but takes 16.8 seconds when run in my ASP.NET application. [yes, you read
>> that right]
>> I've run profiler to find this info.
>> The machine has all the updates and service packs.
>> Where should I look next for an answer?
>
> Also look at the SET settings used by ASP.NET. If you have any indexed
> views or indexes on computed columns they cannot be used unless your
> connection settings are correct.
> Also when you say that it runs in .016 seconds in QA, how are you running
> it? Just running the body of the stored procedure with hard-coded values
> is not the same. You can always capture application's activity from
> Profiler and replay it in QA.
> David
>