Stored procedure with in a Sub Report

[Guest]

I tested the sub report with the Stored procedure and it works. As soon as I
run the main report I get the following error: "Incorrect Syntax near the
keyword 'Exec' With in the subreport Recordsource I have the following text
exec [GetRepGroups]. Any ideas on what I need to do?


Thanks
ToniS

 

[Sylvain Lafontaine]

Probably because ADP is trying to put a server filter on an EXEC statement
when the subreport is used as a subreport; not as a main report (as it is
when you try it independantly).

Remove the EXEC word and the paramaters that follow, set the RecordSource to
the name of the stored procedure, the Record Source Qualifier to dbo and use
only parameters for the SP of the subreport that are either present as
fields on the RecordSource or as bound controls (name) of the main report
(names and fields without the @ of the parameter(s)).

If I remember correctly, the Link Child and Master Fields are useless when a
stored procedure is used as the record source of a subreport (they are only
good when using a table or a view).

 

[Guest]

thank you for the quick response. I did what you suggested, and it seems to
be working for the most part (prints all of the lines for each Exhibitor).
Looks like you are correct about the link child and master fields not
working.. I think I will be able to get around that by passing in a parmater
in the Stored Procedure? I will rsearch to see what the syntext will be to
do that.

Thanks again.

Probably because ADP is trying to put a server filter on an EXEC statement
when the subreport is used as a subreport; not as a main report (as it is
when you try it independantly).

Remove the EXEC word and the paramaters that follow, set the RecordSource to
the name of the stored procedure, the Record Source Qualifier to dbo and use
only parameters for the SP of the subreport that are either present as
fields on the RecordSource or as bound controls (name) of the main report
(names and fields without the @ of the parameter(s)).

If I remember correctly, the Link Child and Master Fields are useless when a
stored procedure is used as the record source of a subreport (they are only
good when using a table or a view).

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

I tested the sub report with the Stored procedure and it works. As soon as
I
run the main report I get the following error: "Incorrect Syntax near the
keyword 'Exec' With in the subreport Recordsource I have the following
text
exec [GetRepGroups]. Any ideas on what I need to do?


Thanks
ToniS

 

[Sylvain Lafontaine]

The syntaxe for SP with parameters is easy:

Create Procedure dbo.MySP (@IdKey int)
AS
Set NoCount ON
Select * from MyTable where MyTable.IdKey = @IdKey
GO

It's always a good idea to put SET NOCOUNT ON at the beginning of stored
procedures (SP) that will be used with ADP because SP with multi-select
statements won't always be compatible with ADP otherwise. The above example
is not a multi-selects SP but it doesn't hurt to always put it there so you
won't forget it later if you ever need it (ie. if the SP ever become more
complexe). If you don't know what you are doing, it would also be a good
idea to either add the WITH RECOMPILE option or to use intermediary
variables in order to eliminate parameters sniffing from SQL-Server; which
is something that could seriously hurts performance:

Create Procedure dbo.MySP (@IdKey int) WITH RECOMPILE
AS
Set NoCount ON
Select * from MyTable where MyTable.IdKey = @IdKey
GO

or:

Create Procedure dbo.MySP (@IdKey int)
AS
Set NoCount ON

Declare @IdKey2 int
Set @IdKey2 = @IdKey

Select * from MyTable where MyTable.IdKey = @IdKey2
GO

You don't need to use both.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

thank you for the quick response. I did what you suggested, and it seems
to
be working for the most part (prints all of the lines for each Exhibitor).
Looks like you are correct about the link child and master fields not
working.. I think I will be able to get around that by passing in a
parmater
in the Stored Procedure? I will rsearch to see what the syntext will be
to
do that.

Thanks again.

Probably because ADP is trying to put a server filter on an EXEC
statement
when the subreport is used as a subreport; not as a main report (as it is
when you try it independantly).

Remove the EXEC word and the paramaters that follow, set the RecordSource
to
the name of the stored procedure, the Record Source Qualifier to dbo and
use
only parameters for the SP of the subreport that are either present as
fields on the RecordSource or as bound controls (name) of the main report
(names and fields without the @ of the parameter(s)).

If I remember correctly, the Link Child and Master Fields are useless
when a
stored procedure is used as the record source of a subreport (they are
only
good when using a table or a view).

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

I tested the sub report with the Stored procedure and it works. As soon
as
I
run the main report I get the following error: "Incorrect Syntax near
the
keyword 'Exec' With in the subreport Recordsource I have the following
text
exec [GetRepGroups]. Any ideas on what I need to do?


Thanks
ToniS

 

[Guest]

Thank you so much for the details, I am learning a lot from you.
Because the master and child links no longer work when using a SP, I am
guessing I would modifiy the SP to handle Parm and to pass in that
information? The link field for the main and subreport was ExhibitorShowID.
Currently I do not have that information printing on the main report... I
would need to add it on the main report, but not display it and pass that
information to the subreport? Would that work? How would I pass that info
to the subreport SP?

Thanks Again.

The syntaxe for SP with parameters is easy:

Create Procedure dbo.MySP (@IdKey int)
AS
Set NoCount ON
Select * from MyTable where MyTable.IdKey = @IdKey
GO

It's always a good idea to put SET NOCOUNT ON at the beginning of stored
procedures (SP) that will be used with ADP because SP with multi-select
statements won't always be compatible with ADP otherwise. The above example
is not a multi-selects SP but it doesn't hurt to always put it there so you
won't forget it later if you ever need it (ie. if the SP ever become more
complexe). If you don't know what you are doing, it would also be a good
idea to either add the WITH RECOMPILE option or to use intermediary
variables in order to eliminate parameters sniffing from SQL-Server; which
is something that could seriously hurts performance:

Create Procedure dbo.MySP (@IdKey int) WITH RECOMPILE
AS
Set NoCount ON
Select * from MyTable where MyTable.IdKey = @IdKey
GO

or:

Create Procedure dbo.MySP (@IdKey int)
AS
Set NoCount ON

Declare @IdKey2 int
Set @IdKey2 = @IdKey

Select * from MyTable where MyTable.IdKey = @IdKey2
GO

You don't need to use both.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

thank you for the quick response. I did what you suggested, and it seems
to
be working for the most part (prints all of the lines for each Exhibitor).
Looks like you are correct about the link child and master fields not
working.. I think I will be able to get around that by passing in a
parmater
in the Stored Procedure? I will rsearch to see what the syntext will be
to
do that.

Thanks again.

Probably because ADP is trying to put a server filter on an EXEC
statement
when the subreport is used as a subreport; not as a main report (as it is
when you try it independantly).

Remove the EXEC word and the paramaters that follow, set the RecordSource
to
the name of the stored procedure, the Record Source Qualifier to dbo and
use
only parameters for the SP of the subreport that are either present as
fields on the RecordSource or as bound controls (name) of the main report
(names and fields without the @ of the parameter(s)).

If I remember correctly, the Link Child and Master Fields are useless
when a
stored procedure is used as the record source of a subreport (they are
only
good when using a table or a view).

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


I tested the sub report with the Stored procedure and it works. As soon
as
I
run the main report I get the following error: "Incorrect Syntax near
the
keyword 'Exec' With in the subreport Recordsource I have the following
text
exec [GetRepGroups]. Any ideas on what I need to do?


Thanks
ToniS

 

[Sylvain Lafontaine]

To my knowledge, the fact that ExhibitorShowID is not printed on the main
report is not important (but if you find that this the case, then all you'll
have to do is to simply add an hidden text field bound to this value). This
value will be passed automatically to the subreport by Access if it's
present in the parameters collection of its record source.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Thank you so much for the details, I am learning a lot from you.
Because the master and child links no longer work when using a SP, I am
guessing I would modifiy the SP to handle Parm and to pass in that
information? The link field for the main and subreport was
ExhibitorShowID.
Currently I do not have that information printing on the main report... I
would need to add it on the main report, but not display it and pass that
information to the subreport? Would that work? How would I pass that
info
to the subreport SP?

Thanks Again.

The syntaxe for SP with parameters is easy:

Create Procedure dbo.MySP (@IdKey int)
AS
Set NoCount ON
Select * from MyTable where MyTable.IdKey = @IdKey
GO

It's always a good idea to put SET NOCOUNT ON at the beginning of stored
procedures (SP) that will be used with ADP because SP with multi-select
statements won't always be compatible with ADP otherwise. The above
example
is not a multi-selects SP but it doesn't hurt to always put it there so
you
won't forget it later if you ever need it (ie. if the SP ever become more
complexe). If you don't know what you are doing, it would also be a good
idea to either add the WITH RECOMPILE option or to use intermediary
variables in order to eliminate parameters sniffing from SQL-Server;
which
is something that could seriously hurts performance:

Create Procedure dbo.MySP (@IdKey int) WITH RECOMPILE
AS
Set NoCount ON
Select * from MyTable where MyTable.IdKey = @IdKey
GO

or:

Create Procedure dbo.MySP (@IdKey int)
AS
Set NoCount ON

Declare @IdKey2 int
Set @IdKey2 = @IdKey

Select * from MyTable where MyTable.IdKey = @IdKey2
GO

You don't need to use both.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

thank you for the quick response. I did what you suggested, and it
seems
to
be working for the most part (prints all of the lines for each
Exhibitor).
Looks like you are correct about the link child and master fields not
working.. I think I will be able to get around that by passing in a
parmater
in the Stored Procedure? I will rsearch to see what the syntext will
be
to
do that.

Thanks again.

:

Probably because ADP is trying to put a server filter on an EXEC
statement
when the subreport is used as a subreport; not as a main report (as it
is
when you try it independantly).

Remove the EXEC word and the paramaters that follow, set the
RecordSource
to
the name of the stored procedure, the Record Source Qualifier to dbo
and
use
only parameters for the SP of the subreport that are either present as
fields on the RecordSource or as bound controls (name) of the main
report
(names and fields without the @ of the parameter(s)).

If I remember correctly, the Link Child and Master Fields are useless
when a
stored procedure is used as the record source of a subreport (they are
only
good when using a table or a view).

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


I tested the sub report with the Stored procedure and it works. As
soon
as
I
run the main report I get the following error: "Incorrect Syntax
near
the
keyword 'Exec' With in the subreport Recordsource I have the
following
text
exec [GetRepGroups]. Any ideas on what I need to do?


Thanks
ToniS

 

[Sylvain Lafontaine]

Also, don't forget to use the Refresh command for the
Queries/Views/Functions window when making any change to the list of
parameters of a SP or to the list of fields returned by this SP.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

To my knowledge, the fact that ExhibitorShowID is not printed on the main
report is not important (but if you find that this the case, then all
you'll have to do is to simply add an hidden text field bound to this
value). This value will be passed automatically to the subreport by
Access if it's present in the parameters collection of its record source.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Thank you so much for the details, I am learning a lot from you.
Because the master and child links no longer work when using a SP, I am
guessing I would modifiy the SP to handle Parm and to pass in that
information? The link field for the main and subreport was
ExhibitorShowID.
Currently I do not have that information printing on the main report... I
would need to add it on the main report, but not display it and pass that
information to the subreport? Would that work? How would I pass that
info
to the subreport SP?

Thanks Again.

The syntaxe for SP with parameters is easy:

Create Procedure dbo.MySP (@IdKey int)
AS
Set NoCount ON
Select * from MyTable where MyTable.IdKey = @IdKey
GO

It's always a good idea to put SET NOCOUNT ON at the beginning of stored
procedures (SP) that will be used with ADP because SP with multi-select
statements won't always be compatible with ADP otherwise. The above
example
is not a multi-selects SP but it doesn't hurt to always put it there so
you
won't forget it later if you ever need it (ie. if the SP ever become
more
complexe). If you don't know what you are doing, it would also be a
good
idea to either add the WITH RECOMPILE option or to use intermediary
variables in order to eliminate parameters sniffing from SQL-Server;
which
is something that could seriously hurts performance:

Create Procedure dbo.MySP (@IdKey int) WITH RECOMPILE
AS
Set NoCount ON
Select * from MyTable where MyTable.IdKey = @IdKey
GO

or:

Create Procedure dbo.MySP (@IdKey int)
AS
Set NoCount ON

Declare @IdKey2 int
Set @IdKey2 = @IdKey

Select * from MyTable where MyTable.IdKey = @IdKey2
GO

You don't need to use both.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


thank you for the quick response. I did what you suggested, and it
seems
to
be working for the most part (prints all of the lines for each
Exhibitor).
Looks like you are correct about the link child and master fields not
working.. I think I will be able to get around that by passing in a
parmater
in the Stored Procedure? I will rsearch to see what the syntext will
be
to
do that.

Thanks again.

:

Probably because ADP is trying to put a server filter on an EXEC
statement
when the subreport is used as a subreport; not as a main report (as
it is
when you try it independantly).

Remove the EXEC word and the paramaters that follow, set the
RecordSource
to
the name of the stored procedure, the Record Source Qualifier to dbo
and
use
only parameters for the SP of the subreport that are either present
as
fields on the RecordSource or as bound controls (name) of the main
report
(names and fields without the @ of the parameter(s)).

If I remember correctly, the Link Child and Master Fields are useless
when a
stored procedure is used as the record source of a subreport (they
are
only
good when using a table or a view).

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


I tested the sub report with the Stored procedure and it works. As
soon
as
I
run the main report I get the following error: "Incorrect Syntax
near
the
keyword 'Exec' With in the subreport Recordsource I have the
following
text
exec [GetRepGroups]. Any ideas on what I need to do?


Thanks
ToniS