Can we have a Subreport inside another subreport

I need to have a subreport inside another subreport. Is it possible directly, if not through any other way. Also could anyone give/send me some tutorials on Crystal Reports, even website will be helpful.
Thanks GuysNever tried it, can't see why not. Easiest way to find out would be to give it a go...?|||I think it is possible|||Madhi,

Could you please let me know how? I am also facing the same issue.

Thanks in advace.

MR|||Madhi,

Could you please let me know how? I am also facing the same issue.

Thanks in advace.

MR

Consider you have three reports say Report1, Report2 and Report3
Suppose you want Report3 as subreport of report2 and report2 is subreport of report1

First Design Report1 and Insert report2 as Subreport, then Goto preview of Report2 and now insert report3 as subreport of report2. Save Report1

Now Report1 has the subreport report2 which has other subreport report3|||i would like explain the working as Madhi told.

first create all the three reports individually.
like Report1, Report2, Report3
Report1 (say) Your Main report.
Report2 (Say) Sub Report of Main Report.
Report3 (Say) Sub Report of Report2.

once u have created all the three reports individually you can make them sub of others.

Right Click in the PageFooter of Report1 (Main Report).
Insert new section.
now Right Click on the newly created section.
goto Insert --> SubReport.

if u have parameters the must be same linked togather.

to link sub report with main report RightClick on the subreport section in the main report.
goto Change SubReport Link...
Add the Field u want to link with (Parameter of Main Report).


You Have Gone through process of Adding SubReport.

Similarly u can add sub report of sub report. :cool: :thumb:

Silly Star





Consider you have three reports say Report1, Report2 and Report3
Suppose you want Report3 as subreport of report2 and report2 is subreport of report1

First Design Report1 and Insert report2 as Subreport, then Goto preview of Report2 and now insert report3 as subreport of report2. Save Report1

Now Report1 has the subreport report2 which has other subreport report3|||In theory, your answer sounds correct... but I can't make it work. I'm working in Crystal Reports 10 with DB2 databases. I have 3 reports that I need to join together, which I'll call the primary(A), subreport(B) and mini-subreport(C).

Before realizing I needed 3 reports (thought 2 would do), I had the subreport(B) and mini-subreport(C) joined together just fine. The fields in subreport(B) and the mini-subreport(C) are in the group footer section.

I created a new "outer shell" as the primary(A) report and inserted subreport(B) [with the embedded mini-subreport(C)] in the report header. The mini-subreport(C) disappeared. I am not able to re-insert the mini-subreport(C) into subreport(B) because all insert->subreport options are grayed out.

Can anyone tell me how to have 2 (or more) nested subreports? Do they have to be in a certain section? Is there an order in which subreports need to be added? Any advice would be greatly appreciated.

Thank you!
Gloria|||No you can't have a subreport with in a sub report. A main report may have n number of Sub reports|||No you can't have a subreport with in a sub report. A main report may have n number of Sub reports

It is possible. Did you try the method I suggested?|||It is possible? I tried what you suggested, but options are grayed out when I try to insert a subreport within a subreport. What am I missing?|||I am facing the same problem!! I am using CR-11 with ASP.NET
I have a main report (A) that has one subreport (B); the subreport has two subreports (C and D);
I cannot break this hirerchy because the first subreport (B) can appear independently from some other place.

I tried both ways i.e. If you use the edit subreport from the main report then the Insert subreport option is grayed out;
If you work on subreport then then insert it into main report (A) then also the third subreport is not being displayed.

Any solution for this!!|||I have been struggling with this problem for a few days..

Crystal's support site (the Recommended Reading by support staff (http://support.businessobjects.com/fix/tsreccr.asp) makes it very clear:

LIMITATIONS:
You cannot insert a subreport within a subreport.
You cannot link a subreport to another subreport.

HTH... If someone has a way around this I would love to know - we are reverting to HTML text strings of tables ... argh|||Would you mind sharing your HTML work-around? That sounds like it might work for us.

Thanks!

Can views be used in query builder?

I tried it but got an invalid object error. I then copied and pasted the sql statement contained in the view but it doesn't run correctly inside query builder and only shows 1 valid row, and a 2nd row that's blank the the first column and has a number 1 in the second column. The view runs correctly in SQL Server Management Studio Express.

How about wrapping the view inside a stored procedure?

|||

Stored procedures can't be used in query builder either. All I can do is put in a dummy SQL statement (like Select 1 as column1, 2 as whatever) and then change it to a stored procedure later when I have access to the "data" tab in [Design].

That doesn't seem right but is the only way I can figure out how to do it.

can view be dynamically filtered like an MSAccess query?

In MSAccess you can dynamically filter a query by adding square brackets [ ]
to the criteria section and a prompt inside the brackets [enter filter code]
.
Then you can open the query directly and be prompted for a filter. I tried
this in the view without any success.
I was approached today by an End user who uses an Access ADP to interface
with our sql server. The user needs to pull/look at data from a large
table, and currently there is no mechanism for filtering the respective tabl
e
in the Access ADP. Since several users are using the same ADP, I don't want
to modify it. I was thinking of creating a view that could be opened from
the ADP. But rather than having the user call me everytime he needs to
change the criteria, is there a way to make the view dynamic?
Thanks,
RichRich,
You can use an inline table-valued function. You can not pass parameters to
a view.
Inline User-Defined Functions
http://msdn.microsoft.com/library/d...>
_08_73lf.asp
AMB
"Rich" wrote:

> In MSAccess you can dynamically filter a query by adding square brackets [
]
> to the criteria section and a prompt inside the brackets [enter filter cod
e].
> Then you can open the query directly and be prompted for a filter. I tri
ed
> this in the view without any success.
> I was approached today by an End user who uses an Access ADP to interface
> with our sql server. The user needs to pull/look at data from a large
> table, and currently there is no mechanism for filtering the respective ta
ble
> in the Access ADP. Since several users are using the same ADP, I don't wa
nt
> to modify it. I was thinking of creating a view that could be opened from
> the ADP. But rather than having the user call me everytime he needs to
> change the criteria, is there a way to make the view dynamic?
> Thanks,
> Rich|||Thanks - very . I will give this a shot. The only thing is that - afte
r
reading the article - it looks like the user needs to write a sql statement.
If the user is using an Access ADP - where does he write the statement to us
e
the inline function? In the article it looked like the user had QA on his
desktop.
"Alejandro Mesa" wrote:
> Rich,
> You can use an inline table-valued function. You can not pass parameters t
o
> a view.
> Inline User-Defined Functions
> http://msdn.microsoft.com/library/d...
es_08_73lf.asp
>
> AMB
> "Rich" wrote:
>|||I figured it out. The function will show up in the user interface. The use
r
can invoke the function the same way as a query in MSAccess and will be
prompted for input. Very . Thanks again for the suggestion.
"Rich" wrote:
> Thanks - very . I will give this a shot. The only thing is that - af
ter
> reading the article - it looks like the user needs to write a sql statemen
t.
> If the user is using an Access ADP - where does he write the statement to
use
> the inline function? In the article it looked like the user had QA on his
> desktop.
> "Alejandro Mesa" wrote:
>

Can this stored procedure be optimised?

Hi there,
I hoping someone can help me reduce the number of line of code I'm
using, as these IF's are nested inside a bigger one.
The main problem I have is I need to add another variable to the IF and
don't want to copy and paste and make this statement even larger.
I have tried playing about with EXEC but with no joy. As far as I'm
aware it's not possible to do @.CSOrder > 0 ? CSPerson = @.CSOrder :
CSPerson > 0
I'm using SQL Server 2000 SP4.
Hopefully I'm missing the obvious, although any help or suggestions are
welcome.
-- snippet --
IF @.CSOrder > 0 AND @.SalesOrder > 0
SELECT * FROM MainOrder
LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
WHERE JobFlow.Component=6
AND (Status = @.InProgress
OR Status = @.Complete
OR Status = @.Cancelled
OR Status = @.Acknowledged)
AND CSPerson = @.CSOrder
AND SalesPerson = @.SalesOrder
ORDER BY DateToArrive, Status
ELSE IF @.CSOrder > 0
SELECT * FROM MainOrder
LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
WHERE JobFlow.Component=6
AND (Status = @.InProgress
OR Status = @.Complete
OR Status = @.Cancelled
OR Status = @.Acknowledged)
AND CSPerson = @.CSOrder
ORDER BY DateToArrive, Status
ELSE IF @.SalesOrder > 0
SELECT * FROM MainOrder
LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
WHERE JobFlow.Component=6
AND (Status = @.InProgress
OR Status = @.Complete
OR Status = @.Cancelled
OR Status = @.Acknowledged)
AND SalesPerson = @.SalesOrder
ORDER BY DateToArrive, Status
ELSE
SELECT * FROM MainOrder
LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
WHERE JobFlow.Component=6
AND (Status = @.InProgress
OR Status = @.Complete
OR Status = @.Cancelled
OR Status = @.Acknowledged)
ORDER BY DateToArrive, StatusWhat you can do is something like this.
SELECT * FROM MainOrder
LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
WHERE JobFlow.Component=6
AND (Status = @.InProgress
OR Status = @.Complete
OR Status = @.Cancelled
OR Status = @.Acknowledged)
AND CSPerson = CSAE @.CSOrder WHEN 0 THEN CSPerson ELSE @.CSOrder
END
AND SalesPerson = CASE @.SalesOrder WHEN 0 THEN SalesPerson ELSE
@.SalesOrder END
AND ... -- add more
AND SalesPerson = @.SalesOrder
ORDER BY DateToArrive, Status|||Mia,
Try something like this
DECLARE @.strWHERE varchar(200)
SET @.strWHERE = ''
IF @.CSOrder > 0 AND @.SalesOrder > 0 THEN @.strWHERE = 'AND CSPerson =
@.CSOrder AND SalesPerson = @.SalesOrder'
IF @.CSOrder > 0 AND @.SalesOrder <> 0 THEN @.strWHERE = 'AND CSPerson =
@.CSOrder'
IF @.CSOrder <> 0 AND @.SalesOrder > 0 THEN @.strWHERE = 'AND SalesPerson =
@.SalesOrder'
DECLARE @.sSQL varchar(2000)
SET @.sSQL = ''
SET @.sSQL = @.sSQL + ' SELECT * FROM MainOrder '
SET @.sSQL = @.sSQL + ' LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id] '
SET @.sSQL = @.sSQL + ' LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id] '
SET @.sSQL = @.sSQL + ' WHERE JobFlow.Component=6 '
SET @.sSQL = @.sSQL + ' AND (Status = @.InProgress '
SET @.sSQL = @.sSQL + ' OR Status = @.Complete '
SET @.sSQL = @.sSQL + ' OR Status = @.Cancelled '
SET @.sSQL = @.sSQL + ' OR Status = @.Acknowledged) '
SET @.sSQL = @.sSQL + @.strWHERE
SET @.sSQL = @.sSQL + ' AND SalesPerson = @.SalesOrder '
SET @.sSQL = @.sSQL + ' AORDER BY DateToArrive, Status '
EXEC (@.sSQL)
Hope this assists,
Tony
"mia_cid@.hotmail.com" wrote:

> Hi there,
> I hoping someone can help me reduce the number of line of code I'm
> using, as these IF's are nested inside a bigger one.
> The main problem I have is I need to add another variable to the IF and
> don't want to copy and paste and make this statement even larger.
> I have tried playing about with EXEC but with no joy. As far as I'm
> aware it's not possible to do @.CSOrder > 0 ? CSPerson = @.CSOrder :
> CSPerson > 0
> I'm using SQL Server 2000 SP4.
> Hopefully I'm missing the obvious, although any help or suggestions are
> welcome.
> -- snippet --
> IF @.CSOrder > 0 AND @.SalesOrder > 0
> SELECT * FROM MainOrder
> LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
> LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
> WHERE JobFlow.Component=6
> AND (Status = @.InProgress
> OR Status = @.Complete
> OR Status = @.Cancelled
> OR Status = @.Acknowledged)
> AND CSPerson = @.CSOrder
> AND SalesPerson = @.SalesOrder
> ORDER BY DateToArrive, Status
> ELSE IF @.CSOrder > 0
> SELECT * FROM MainOrder
> LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
> LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
> WHERE JobFlow.Component=6
> AND (Status = @.InProgress
> OR Status = @.Complete
> OR Status = @.Cancelled
> OR Status = @.Acknowledged)
> AND CSPerson = @.CSOrder
> ORDER BY DateToArrive, Status
> ELSE IF @.SalesOrder > 0
> SELECT * FROM MainOrder
> LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
> LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
> WHERE JobFlow.Component=6
> AND (Status = @.InProgress
> OR Status = @.Complete
> OR Status = @.Cancelled
> OR Status = @.Acknowledged)
> AND SalesPerson = @.SalesOrder
> ORDER BY DateToArrive, Status
> ELSE
> SELECT * FROM MainOrder
> LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
> LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
> WHERE JobFlow.Component=6
> AND (Status = @.InProgress
> OR Status = @.Complete
> OR Status = @.Cancelled
> OR Status = @.Acknowledged)
> ORDER BY DateToArrive, Status
>|||Ooops,
take the following line out of the code:::
SET @.sSQL = @.sSQL + ' AND SalesPerson = @.SalesOrder '
it was an oversight on my part, sorry,
Tony
"Tony Scott" wrote:
> Mia,
> Try something like this
>
> DECLARE @.strWHERE varchar(200)
> SET @.strWHERE = ''
> IF @.CSOrder > 0 AND @.SalesOrder > 0 THEN @.strWHERE = 'AND CSPerson =
> @.CSOrder AND SalesPerson = @.SalesOrder'
> IF @.CSOrder > 0 AND @.SalesOrder <> 0 THEN @.strWHERE = 'AND CSPerson =
> @.CSOrder'
> IF @.CSOrder <> 0 AND @.SalesOrder > 0 THEN @.strWHERE = 'AND SalesPerson =
> @.SalesOrder'
> DECLARE @.sSQL varchar(2000)
> SET @.sSQL = ''
> SET @.sSQL = @.sSQL + ' SELECT * FROM MainOrder '
> SET @.sSQL = @.sSQL + ' LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id] '
> SET @.sSQL = @.sSQL + ' LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id] '
> SET @.sSQL = @.sSQL + ' WHERE JobFlow.Component=6 '
> SET @.sSQL = @.sSQL + ' AND (Status = @.InProgress '
> SET @.sSQL = @.sSQL + ' OR Status = @.Complete '
> SET @.sSQL = @.sSQL + ' OR Status = @.Cancelled '
> SET @.sSQL = @.sSQL + ' OR Status = @.Acknowledged) '
> SET @.sSQL = @.sSQL + @.strWHERE
> SET @.sSQL = @.sSQL + ' AND SalesPerson = @.SalesOrder '
> SET @.sSQL = @.sSQL + ' AORDER BY DateToArrive, Status '
> EXEC (@.sSQL)
>
> Hope this assists,
> Tony
> "mia_cid@.hotmail.com" wrote:
>|||Thanks for the help guys,
Patrik I liked your idea, only I need:
AND CSPerson = CASE @.CSOrder WHEN > 0 THEN CSPerson ELSE @.CSOrder
Not
AND CSPerson = CASE @.CSOrder WHEN 0 THEN CSPerson ELSE @.CSOrder
Or
AND CSPerson >= CASE @.CSOrder WHEN 0 THEN CSPerson ELSE @.CSOrder
As if @.CSOrder = 0, then I want to select everything, so CSPerson needs
to be greater than 0.
Tony,
I had a couple of errors with your original code, so now have:
DECLARE @.strWHERE varchar(200)
SET @.strWHERE = ''
IF @.CSOrder > 0 AND @.SalesOrder > 0
SET @.strWHERE = 'AND CSPerson = @.CSOrder AND SalesPerson =
@.SalesOrder'
IF @.CSOrder > 0 AND @.SalesOrder <> 0
SET @.strWHERE = 'AND CSPerson = @.CSOrder'
IF @.CSOrder <> 0 AND @.SalesOrder > 0
SET @.strWHERE = 'AND SalesPerson = @.SalesOrder'
DECLARE @.sSQL varchar(2000)
SET @.sSQL = ''
SET @.sSQL = @.sSQL + ' SELECT * FROM MainOrder '
SET @.sSQL = @.sSQL + ' LEFT JOIN AnCJobs ON MainOrder.[id] =
AnCJobs.[id] '
SET @.sSQL = @.sSQL + ' LEFT JOIN JobFlow ON JobFlow.[id] =
MainOrder.[id] '
SET @.sSQL = @.sSQL + ' WHERE JobFlow.Component=6 '
SET @.sSQL = @.sSQL + ' AND (Status = @.InProgress '
SET @.sSQL = @.sSQL + ' OR Status = @.Complete '
SET @.sSQL = @.sSQL + ' OR Status = @.Cancelled '
SET @.sSQL = @.sSQL + ' OR Status = @.Acknowledged) '
SET @.sSQL = @.sSQL + @.strWHERE
SET @.sSQL = @.sSQL + ' ORDER BY DateToArrive, Status'
EXEC (@.sSQL)
Only I've getting a syntax error near the keyword 'ELSE'. If I comment
this code out and put a standard SELECT in it works okay.
Any ideas?|||You can do that.
AND CSPerson = CASE WHEN @.CSOrder > 0 THEN CSPerson ELSE @.CSOrder END
Although, it would probably be more efficient with dynamic sql in this
case.
I suggest that you use sp_executesql instaead of exec.
Read sommarskogs really good article about using dynamic sql.
http://www.sommarskog.se/dynamic_sql.html|||Thanks for that Partrik,
I just needed it the other way round:
AND CSPerson = CASE WHEN @.CSOrder > 0 THEN @.CSOrder ELSE CSPerson END
It works a treat, for the time being I'm going to stick with it as
it'll be running over a LAN, have bookmarked the site you recommended
and will have a read when I get the chance.
Thanks again to Tony for his help as well.

Can the .mdb file (include VBA code) be indexed?

Hi GURU, I wonder if the code inside the .mdb or .adp file can be indexed? if
yes, what iFilter can do that? Thanks.
Raymond,
If memory servers me correctly, both of those file extensions are Microsoft
Access related. Assuming so, I do not believe that there are any known /
public IFilters for this files types. However, the IFilter API is well
known, and if you know the internal structure of these MS propriety file
formats, then by all means write your own! <g>
-- John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Raymond" <Raymond@.discussions.microsoft.com> wrote in message
news:015B1CD4-7874-464E-B154-CAA1201583B4@.microsoft.com...
> Hi GURU, I wonder if the code inside the .mdb or .adp file can be indexed?
> if
> yes, what iFilter can do that? Thanks.

can SQL Profiler ('05) look into User Defined Functions (UDFs)?

particularly table valued UDFs that may cause other udfs, etc.

what settings/events do I need to see inside and get each statement or at least each seperate UDF that is executed internally?

Try SP:StmtStarting, SP:StmtCompleted events. They are under "Stored Procedures" event category.

Can OPTION (ROBUST PLAN) be used inside of a VIEW?

(SQL Server 2000, SP3)
Hello all!
I've got a VIEW whose use seems to require the OPTION (ROBUST PLAN) query hint.
(Otherwise it's very dependant on the "width" of my data -- and I'll get errors if it
exceeds 8094 bytes.)
Can this hint be incorporated into the VIEW itself? When I try that, it seems to fail,
but I'm wondering if I'm putting it in the right place?
However, if I do it outside the VIEW:
select * from MyView option (robust plan)
Then it works. But, I'd prefer not to have to change my code to incorporate the OPTION
(ROBUST PLAN) query hint everywhere that I reference this VIEW.
Any suggestions?
Thanks!
John PetersonI don't see anything that states you can't use this in a view but your issue
may be outside the view itself. When you select against a view that selects
from views the optimizer tries to look at them all as a whole to get the
best plan. The view by itself may not run into this issue. I would try to
cut down the columns first and see if that helps.
--
Andrew J. Kelly
SQL Server MVP
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:ej$ah9IdDHA.2932@.tk2msftngp13.phx.gbl...
> (SQL Server 2000, SP3)
> Hello all!
> I've got a VIEW whose use seems to require the OPTION (ROBUST PLAN) query
hint.
> (Otherwise it's very dependant on the "width" of my data -- and I'll get
errors if it
> exceeds 8094 bytes.)
> Can this hint be incorporated into the VIEW itself? When I try that, it
seems to fail,
> but I'm wondering if I'm putting it in the right place?
> However, if I do it outside the VIEW:
> select * from MyView option (robust plan)
> Then it works. But, I'd prefer not to have to change my code to
incorporate the OPTION
> (ROBUST PLAN) query hint everywhere that I reference this VIEW.
> Any suggestions?
> Thanks!
> John Peterson
>|||Hello Andrew!
Thanks for your help -- yeah, if I try to create a VIEW like:
create view Test
as
select * from sysobjects option (robust plan)
go
It will fail to create the VIEW (citing a syntax error near "option"). It's almost like I
can only have that OPTION clause "outside" of the VIEW. But, I don't want to have to
change a lot of code to explicitly put the OPTION clause in place for those objects that
are calling the VIEW -- I'd like to be able to do that in *one* place if at all possible.
Additional thoughts?
John Peterson
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uv9%23UBKdDHA.652@.tk2msftngp13.phx.gbl...
> I don't see anything that states you can't use this in a view but your issue
> may be outside the view itself. When you select against a view that selects
> from views the optimizer tries to look at them all as a whole to get the
> best plan. The view by itself may not run into this issue. I would try to
> cut down the columns first and see if that helps.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:ej$ah9IdDHA.2932@.tk2msftngp13.phx.gbl...
> > (SQL Server 2000, SP3)
> >
> > Hello all!
> >
> > I've got a VIEW whose use seems to require the OPTION (ROBUST PLAN) query
> hint.
> > (Otherwise it's very dependant on the "width" of my data -- and I'll get
> errors if it
> > exceeds 8094 bytes.)
> >
> > Can this hint be incorporated into the VIEW itself? When I try that, it
> seems to fail,
> > but I'm wondering if I'm putting it in the right place?
> >
> > However, if I do it outside the VIEW:
> >
> > select * from MyView option (robust plan)
> >
> > Then it works. But, I'd prefer not to have to change my code to
> incorporate the OPTION
> > (ROBUST PLAN) query hint everywhere that I reference this VIEW.
> >
> > Any suggestions?
> >
> > Thanks!
> >
> > John Peterson
> >
> >
>|||Thanks, Olaf -- that's exactly what I tried. Unfortunately, that doesn't appear to work
either. The only way to get that OPTION clause seems to be in a stored procedure of the
"base" call. :-(
"Olaf Pietsch" <olaf_pietsch@.t-online.de> wrote in message
news:ewq$B7OdDHA.2296@.TK2MSFTNGP09.phx.gbl...
> "John Peterson" <j0hnp@.comcast.net> wrote
> > create view Test
> > as
> > select * from sysobjects option (robust plan)
> > go
> >
> > It will fail to create the VIEW (citing a syntax error near
> > "option"). It's almost like I can only have that OPTION clause
> > "outside" of the VIEW. But, I don't want to have to change a lot of
> > code to explicitly put the OPTION clause in place for those objects
> > that are calling the VIEW -- I'd like to be able to do that in *one*
> > place if at all possible.
> If you can use an UDF:
> CREATE FUNCTION test_function ()
> RETURNS @.table_var TABLE (n varchar(100), id int)
> AS
> BEGIN
> INSERT @.table_var SELECT [name], id from sysobjects option (robust plan)
> RETURN
> END
> GO
> CREATE VIEW test
> AS
> SELECT * FROM [dbo].[test_function]()
> GO
> --
> Olaf
>
>
>|||Hi John,
"John Peterson" <j0hnp@.comcast.net> wrote
news:OpQaFVPdDHA.2748@.tk2msftngp13.phx.gbl
> Thanks, Olaf -- that's exactly what I tried. Unfortunately, that
> doesn't appear to work either. The only way to get that OPTION
> clause seems to be in a stored procedure of the "base" call. :-(
I tested my suggestion and is running successful, I use SQL 2000 SP3.
>> If you can use an UDF:
>> CREATE FUNCTION test_function ()
>> RETURNS @.table_var TABLE (n varchar(100), id int)
>> AS
>> BEGIN
>> INSERT @.table_var SELECT [name], id from sysobjects option (robust
>> plan) RETURN
>> END
>> GO
>> CREATE VIEW test
>> AS
>> SELECT * FROM [dbo].[test_function]()
>> GO
Sorry, did you test the same version really?
The following raises an error:
CREATE FUNCTION dbo.test_fn ()
RETURNS TABLE
AS
RETURN (
SELECT [name], id from sysobjects option (robust plan)
)
Server: Msg 156, Level 15, State 1, Procedure test_fn, Line 8
Incorrect syntax near the keyword 'option'.
--
Olaf|||Oh...you know what I did:
create function Test()
returns table
as
return(select * from sysobjects option (robust plan))
go
And *that* didn't work. I had hoped that I wouldn't have to define the table structure
and just be an inline function (IsInlineFunction as opposed to IsTableFunction). But I
guess that doesn't quite work.
If all else fails, I'll probably try your suggested technique. It really bugs me that the
VIEW needs the OPTION hint -- I'm hopeful to refactor it so that it's not required.
Thanks for your help!
"Olaf Pietsch" <olaf_pietsch@.t-online.de> wrote in message
news:u8C7ZFSdDHA.728@.TK2MSFTNGP11.phx.gbl...
> Hi John,
> "John Peterson" <j0hnp@.comcast.net> wrote
> news:OpQaFVPdDHA.2748@.tk2msftngp13.phx.gbl
> > Thanks, Olaf -- that's exactly what I tried. Unfortunately, that
> > doesn't appear to work either. The only way to get that OPTION
> > clause seems to be in a stored procedure of the "base" call. :-(
> I tested my suggestion and is running successful, I use SQL 2000 SP3.
> >> If you can use an UDF:
> >>
> >> CREATE FUNCTION test_function ()
> >> RETURNS @.table_var TABLE (n varchar(100), id int)
> >> AS
> >> BEGIN
> >> INSERT @.table_var SELECT [name], id from sysobjects option (robust
> >> plan) RETURN
> >> END
> >> GO
> >>
> >> CREATE VIEW test
> >> AS
> >> SELECT * FROM [dbo].[test_function]()
> >> GO
> Sorry, did you test the same version really?
> The following raises an error:
> CREATE FUNCTION dbo.test_fn ()
> RETURNS TABLE
> AS
> RETURN (
> SELECT [name], id from sysobjects option (robust plan)
> )
> Server: Msg 156, Level 15, State 1, Procedure test_fn, Line 8
> Incorrect syntax near the keyword 'option'.
> --
> Olaf
>

can not open file

Hello,

Using a foreach loop which contains dataflow. Inside dataflow there is a flatfile source.

The following error is produced inthe flat file source.

Error: 0xC020200E at Data Flow Task, Flat File Source [446]: Cannot open the datafile "EqRonnieHK".

Error: 0xC004701A at Data Flow Task, DTS.Pipeline: component "Flat File Source" (446) failed the pre-execute phase and returned error code 0xC020200E.

Please note, the file does indeed exist

Thanks

Are you using a fully qualified path though? Doesn't look like it. Always use fully qualified paths.