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 Line Breaks in Fixed Width File

Hello,

I have a series of fixed width files, all with the same schema. I need to import the data into a SQL Server table. Each record in the flat file begins with 'D1'. The length of each record (string) is 380. There are cases where the record ends after position 193, and a new record appears in the current string beginning at position 194. So at position 194 'D' appears, and '1' appears at position 195.

In the flat file, I need to insert a line break after position 193 if position 194 = 'D' and if position 195 = '1'. I'm guessing I would do this with a Script Component Transformation. Once the file is edited, then I can bring the data into the table.

What might the script look like? If you have any suggestions, samples, or know of examples on the web you can point me to, please share.

Thank you for your help!

cdun2

Do you really need to insert CR/LF? For what reason?

If all you want is to read this file, use a "Data Task" and use the Flat File connection with a format type of "Fixed Width". This will allow you to define a file import for the file without cr/lf.|||

Thanks for your response. There are cases in the record strings where a new record begins after character 193 instead of at character 1. I need to insert the line break after character 193 so that the records that start at character 194 will correctly start at character 1. Does that make sense?

I suppose I could do a conditional split of some kind, or find some way to separate out the records that start after 193.

I hope that clarifies things.

cdun2

Need Line Breaks in Fixed Width File

Hello,

I have a series of fixed width files, all with the same schema. I need to import the data into a SQL Server table. Each record in the flat file begins with 'D1'. The length of each record (string) is 380. There are cases where the record ends after position 193, and a new record appears in the current string beginning at position 194. So at position 194 'D' appears, and '1' appears at position 195.

In the flat file, I need to insert a line break after position 193 if position 194 = 'D' and if position 195 = '1'. I'm guessing I would do this with a Script Component Transformation. Once the file is edited, then I can bring the data into the table.

What might the script look like? If you have any suggestions, samples, or know of examples on the web you can point me to, please share.

Thank you for your help!

cdun2

Do you really need to insert CR/LF? For what reason?

If all you want is to read this file, use a "Data Task" and use the Flat File connection with a format type of "Fixed Width". This will allow you to define a file import for the file without cr/lf.|||

Thanks for your response. There are cases in the record strings where a new record begins after character 193 instead of at character 1. I need to insert the line break after character 193 so that the records that start at character 194 will correctly start at character 1. Does that make sense?

I suppose I could do a conditional split of some kind, or find some way to separate out the records that start after 193.

I hope that clarifies things.

cdun2