"Pages breaks" when I add a page footer to the report

i have a report that calls several subreports. I had everything working fine
until I added a page footer to the main report. All I put in it was a small
textbox that is going to hold page numbers...
Anybody know why its page breaking? I hope it has nothing to do with the
implicit keep together when calling subreports...
Let me know if anybody has any ideas.. I've tried many things an nothing
works..
JoshuaThe length of the page exceeds the page size so it "page breaks" so try
checking the length of the page and the page you selected say A4 former
should be always less than later otherwise page break occurs.
Amarnath
"gdjoshua" wrote:
> i have a report that calls several subreports. I had everything working fine
> until I added a page footer to the main report. All I put in it was a small
> textbox that is going to hold page numbers...
> Anybody know why its page breaking? I hope it has nothing to do with the
> implicit keep together when calling subreports...
> Let me know if anybody has any ideas.. I've tried many things an nothing
> works..
> Joshua|||Not quite sure I understand what your saying... My main report in the
designer fits within the margins I have set up...
Explain further...
"Amarnath" wrote:
> The length of the page exceeds the page size so it "page breaks" so try
> checking the length of the page and the page you selected say A4 former
> should be always less than later otherwise page break occurs.
> Amarnath
> "gdjoshua" wrote:
> > i have a report that calls several subreports. I had everything working fine
> > until I added a page footer to the main report. All I put in it was a small
> > textbox that is going to hold page numbers...
> >
> > Anybody know why its page breaking? I hope it has nothing to do with the
> > implicit keep together when calling subreports...
> >
> > Let me know if anybody has any ideas.. I've tried many things an nothing
> > works..
> >
> > Joshua

"Must declare variable" error caused by stored procedure

I'm having the most difficult time trying to generate a report that
first calls a stored procedure and then retrieves the data produced
from it.
I get the error, "An error has occurred during report
processing...query execution failed for data set dsOrgs...Must declare
the variable @.INum" when I try to run the report.
The dataset below (dsOrgs) first calls a stored procedure
(SV_GetSubordinates) that populates a table with hierarchical data.
The second part (the Select statement) then retrieves the data
produced by the stored procedure. I have no problem running this set
of SQL statements in the Data view of the Reporting Services Report
Designer.
EXEC SV_GetSubordinates @.INum,'Groups',@.OrgNum
SELECT v_Orgs.*
FROM v_Orgs, SVSiblings
WHERE
v_Orgs.INum = @.INum AND
v_Orgs.INum = SVSiblings.INum AND
v_Orgs.OrgNum = SVSiblings.Num
By the way, @.INum is a parameter that will be passed to the report in
a URL string eventually. But for now, I have to use both the Preview
capability of the Report Designer and the Report Manager rendering
engine to test out my report.
I have another dataset that gets the @.OrgNum parameter value from a
selection in a drop down in my report. Here is the query for that
data set...
SELECT
NULL AS OrgNum,
'-- ALL Orgs --' AS [Description]
FROM SVGroupDefs
WHERE
INum = @.INum
UNION
SELECT
OrgNum,
[Description]
FROM SVGroupDefs
WHERE
INum = @.INum
ORDER BY [Description]
As you can see, I'm using @.INum in this dataset first so I can
populate my drop down list. When the user selects an Organization
from the drop down list, the selection returns the value for the
parameter @.OrgNum, which is used in my dsOrgs dataset along with @.INum
to retrieve the hierarchical data for my report.
You may be asking why I need to get hierarchical data when the table
object in the report designer uses a parent-child relationship. The
reason why I'm going through all this pain is because I need to
recursively get all the children from a starting parent level, which
@.OrgNum supplies. SQL Server does not natively support a way to
recursively get all the children in a hierarchy. The only way to do
this is to run through my stored procedure, which recursively calls
itself and then populates a table with the child OrgNum values
(fortunately Yukon has solved this recursive nightmare).
Anyway, how can I generate my report when the error states I must
first declare @.INum?I guess thats because the SPs run independently.
BTW have you considered using cursors in your SP?
>--Original Message--
>I'm having the most difficult time trying to generate a
report that
>first calls a stored procedure and then retrieves the
data produced
>from it.
>I get the error, "An error has occurred during report
>processing...query execution failed for data set
dsOrgs...Must declare
>the variable @.INum" when I try to run the report.
>The dataset below (dsOrgs) first calls a stored procedure
>(SV_GetSubordinates) that populates a table with
hierarchical data.
>The second part (the Select statement) then retrieves the
data
>produced by the stored procedure. I have no problem
running this set
>of SQL statements in the Data view of the Reporting
Services Report
>Designer.
>EXEC SV_GetSubordinates @.INum,'Groups',@.OrgNum
>SELECT v_Orgs.*
>FROM v_Orgs, SVSiblings
>WHERE
> v_Orgs.INum = @.INum AND
> v_Orgs.INum = SVSiblings.INum AND
> v_Orgs.OrgNum = SVSiblings.Num
>By the way, @.INum is a parameter that will be passed to
the report in
>a URL string eventually. But for now, I have to use both
the Preview
>capability of the Report Designer and the Report Manager
rendering
>engine to test out my report.
>I have another dataset that gets the @.OrgNum parameter
value from a
>selection in a drop down in my report. Here is the query
for that
>data set...
>SELECT
> NULL AS OrgNum,
> '-- ALL Orgs --' AS [Description]
>FROM SVGroupDefs
>WHERE
> INum = @.INum
>UNION
>SELECT
> OrgNum,
> [Description]
>FROM SVGroupDefs
>WHERE
> INum = @.INum
>ORDER BY [Description]
>As you can see, I'm using @.INum in this dataset first so
I can
>populate my drop down list. When the user selects an
Organization
>from the drop down list, the selection returns the value
for the
>parameter @.OrgNum, which is used in my dsOrgs dataset
along with @.INum
>to retrieve the hierarchical data for my report.
>You may be asking why I need to get hierarchical data
when the table
>object in the report designer uses a parent-child
relationship. The
>reason why I'm going through all this pain is because I
need to
>recursively get all the children from a starting parent
level, which
>@.OrgNum supplies. SQL Server does not natively support a
way to
>recursively get all the children in a hierarchy. The
only way to do
>this is to run through my stored procedure, which
recursively calls
>itself and then populates a table with the child OrgNum
values
>(fortunately Yukon has solved this recursive nightmare).
>Anyway, how can I generate my report when the error
states I must
>first declare @.INum?
>.
>|||Ravi, the stored procedure must call itself recursively as it gets the
children for each parent. For example, here's my hierarchy
OrgNum ParentOrgNum
1001 NULL
1002 1001
1003 1002
1004 1002
1005 1001
1006 1005
1007 1006
1008 1006
If I want all the children for OrgNum 1005, the sproc first runs and
gets OrgNum 1006 as a child. Then 1006 becomes the parent and the
sproc calls itself to get all the children for 1006, which are 1007
and 1008. When the sproc tries to get their children, there are no
more, and the sproc terminates. So, I end up with 1006, 1007, and
1008 as children for 1005. During each iteration of the sproc, I
insert the child OrgNum values into a table.
So, with my dsOrgs dataset, I first run the sproc, which does the
stuff above. Then I run a select statement which retrieves all the
children from the table I populated from my sproc. All that works
fine when I run everything in the data area of Reporting Services'
Report Builder.
But that's not the problem. The problem is that I get an error
stating that I have to declare a variable, @.INum. That's what my
first message covers in detail, and the problem to which I'm seeking a
solution.
"Ravi" <ravikantkv@.rediffmail.com> wrote in message news:<549501c49175$e1e4dcd0$a601280a@.phx.gbl>...
> I guess thats because the SPs run independently.
> BTW have you considered using cursors in your SP?
> >--Original Message--
> >I'm having the most difficult time trying to generate a
> report that
> >first calls a stored procedure and then retrieves the
> data produced
> >from it.
> >
> >I get the error, "An error has occurred during report
> >processing...query execution failed for data set
> dsOrgs...Must declare
> >the variable @.INum" when I try to run the report.
> >
> >The dataset below (dsOrgs) first calls a stored procedure
> >(SV_GetSubordinates) that populates a table with
> hierarchical data.
> >The second part (the Select statement) then retrieves the
> data
> >produced by the stored procedure. I have no problem
> running this set
> >of SQL statements in the Data view of the Reporting
> Services Report
> >Designer.
> >
> >EXEC SV_GetSubordinates @.INum,'Groups',@.OrgNum
> >SELECT v_Orgs.*
> >FROM v_Orgs, SVSiblings
> >WHERE
> > v_Orgs.INum = @.INum AND
> > v_Orgs.INum = SVSiblings.INum AND
> > v_Orgs.OrgNum = SVSiblings.Num
> >
> >By the way, @.INum is a parameter that will be passed to
> the report in
> >a URL string eventually. But for now, I have to use both
> the Preview
> >capability of the Report Designer and the Report Manager
> rendering
> >engine to test out my report.
> >
> >I have another dataset that gets the @.OrgNum parameter
> value from a
> >selection in a drop down in my report. Here is the query
> for that
> >data set...
> >
> >SELECT
> > NULL AS OrgNum,
> > '-- ALL Orgs --' AS [Description]
> >FROM SVGroupDefs
> >WHERE
> > INum = @.INum
> >UNION
> >SELECT
> > OrgNum,
> > [Description]
> >FROM SVGroupDefs
> >WHERE
> > INum = @.INum
> >ORDER BY [Description]
> >
> >As you can see, I'm using @.INum in this dataset first so
> I can
> >populate my drop down list. When the user selects an
> Organization
> >from the drop down list, the selection returns the value
> for the
> >parameter @.OrgNum, which is used in my dsOrgs dataset
> along with @.INum
> >to retrieve the hierarchical data for my report.
> >
> >You may be asking why I need to get hierarchical data
> when the table
> >object in the report designer uses a parent-child
> relationship. The
> >reason why I'm going through all this pain is because I
> need to
> >recursively get all the children from a starting parent
> level, which
> >@.OrgNum supplies. SQL Server does not natively support a
> way to
> >recursively get all the children in a hierarchy. The
> only way to do
> >this is to run through my stored procedure, which
> recursively calls
> >itself and then populates a table with the child OrgNum
> values
> >(fortunately Yukon has solved this recursive nightmare).
> >
> >Anyway, how can I generate my report when the error
> states I must
> >first declare @.INum?
> >.
> >|||Hi Steve,
I am doing something sort of similar... I have created a
stored procedure that takes in a couple of parameters and
passes them to the database and creates a table. I then
have a query that selects the data from the table. I
created two seperate datasets one for the stored
procedure and one for the select statement and it seems
to be working. I'm not sure how you tell it what to
execute first but another report writer here is doing the
same but she had to increase the timeout to give the
stored procedure a chance to finish otherwise it was
throwing errors. Sorry I'm not much help but I'm curious
if you've tried to use the parent group within the group
that is suppose to recursively search in a parent-child
relationship? I have the same exact thing to do that you
are doing and I would love to hear any lessons learned.
Thanks!!
>--Original Message--
>I'm having the most difficult time trying to generate a
report that
>first calls a stored procedure and then retrieves the
data produced
>from it.
>I get the error, "An error has occurred during report
>processing...query execution failed for data set
dsOrgs...Must declare
>the variable @.INum" when I try to run the report.
>The dataset below (dsOrgs) first calls a stored procedure
>(SV_GetSubordinates) that populates a table with
hierarchical data.
>The second part (the Select statement) then retrieves
the data
>produced by the stored procedure. I have no problem
running this set
>of SQL statements in the Data view of the Reporting
Services Report
>Designer.
>EXEC SV_GetSubordinates @.INum,'Groups',@.OrgNum
>SELECT v_Orgs.*
>FROM v_Orgs, SVSiblings
>WHERE
> v_Orgs.INum = @.INum AND
> v_Orgs.INum = SVSiblings.INum AND
> v_Orgs.OrgNum = SVSiblings.Num
>By the way, @.INum is a parameter that will be passed to
the report in
>a URL string eventually. But for now, I have to use
both the Preview
>capability of the Report Designer and the Report Manager
rendering
>engine to test out my report.
>I have another dataset that gets the @.OrgNum parameter
value from a
>selection in a drop down in my report. Here is the
query for that
>data set...
>SELECT
> NULL AS OrgNum,
> '-- ALL Orgs --' AS [Description]
>FROM SVGroupDefs
>WHERE
> INum = @.INum
>UNION
>SELECT
> OrgNum,
> [Description]
>FROM SVGroupDefs
>WHERE
> INum = @.INum
>ORDER BY [Description]
>As you can see, I'm using @.INum in this dataset first so
I can
>populate my drop down list. When the user selects an
Organization
>from the drop down list, the selection returns the value
for the
>parameter @.OrgNum, which is used in my dsOrgs dataset
along with @.INum
>to retrieve the hierarchical data for my report.
>You may be asking why I need to get hierarchical data
when the table
>object in the report designer uses a parent-child
relationship. The
>reason why I'm going through all this pain is because I
need to
>recursively get all the children from a starting parent
level, which
>@.OrgNum supplies. SQL Server does not natively support
a way to
>recursively get all the children in a hierarchy. The
only way to do
>this is to run through my stored procedure, which
recursively calls
>itself and then populates a table with the child OrgNum
values
>(fortunately Yukon has solved this recursive nightmare).
>Anyway, how can I generate my report when the error
states I must
>first declare @.INum?
>.
>|||Melissa,
Reporting Services does a fine job handling parent-child sets of
data in tables and what not...but that's assuming you have a data set
with all the data you want. What I have is an entire hierarchy, of
which, only one part I might want to retrieve in the data set for the
report (e.g. pulling back a region and their branches vs. the whole
entire organization). So, the whole problem here is about retrieving
the data and not about how Reporting Services will handle it after the
data is retrieved.
With that being said, I've already tried to break out the stored
procedure that gets all the children vs. the SQL query that retrieves
the data produced by the stored procedure into two separate data sets.
But how would Reporting Services know when the stored procedure has
finished in order to run the second query, which retrieves the data?
By arbitrarily setting a timeout? I find that method too unreliable.
I've already broken out my stored procedure and select statement into
two data sets, but that doesn't work.
So, what else have I done? I've tried to do a recursive SQL
function, but to no avail (functions can't recursive do selects of
data), a recursive stored procedure with a varying output parameter
(Reporting Services allows only 1 value per parameter for the current
release), but to no avail, setting the output of my stored procedure
to a temp table (can't seem to get that to work), but to no avail,
using a global temp table within my stored procedure (doesn't work
because the stored procedure recursively calls itself and you can only
declare the global temp table once), but to no avail, and a bunch of
other techniques in order to recursively grab all the children for the
region I select for my data set. The Yukon release of SQL Server will
solve my problem, because I will be able to execute a single
expression and retrieve the recursive data I need in a single
operation...but I need something in the meantime. (By the way, Oracle
already supports recursion with their Connect method).
So, I ended up having to pair the stored procedure with my select in
the same dataset in order to 1) generate a list of child values and 2)
retrieve that list of values AFTER they are generated. The dataset
refreshes no problem, but I get that stupid "need to declare @.INum
first" error, which I can't get rid of...it's so frustrating. I wish
I could speak to one of the Reporting Services developers over the
phone and figure this out.
"Melissa" <anonymous@.discussions.microsoft.com> wrote in message news:<028801c491fd$3c204e00$a401280a@.phx.gbl>...
> Hi Steve,
> I am doing something sort of similar... I have created a
> stored procedure that takes in a couple of parameters and
> passes them to the database and creates a table. I then
> have a query that selects the data from the table. I
> created two seperate datasets one for the stored
> procedure and one for the select statement and it seems
> to be working. I'm not sure how you tell it what to
> execute first but another report writer here is doing the
> same but she had to increase the timeout to give the
> stored procedure a chance to finish otherwise it was
> throwing errors. Sorry I'm not much help but I'm curious
> if you've tried to use the parent group within the group
> that is suppose to recursively search in a parent-child
> relationship? I have the same exact thing to do that you
> are doing and I would love to hear any lessons learned.
> Thanks!!
> >--Original Message--
> >I'm having the most difficult time trying to generate a
> report that
> >first calls a stored procedure and then retrieves the
> data produced
> >from it.
> >
> >I get the error, "An error has occurred during report
> >processing...query execution failed for data set
> dsOrgs...Must declare
> >the variable @.INum" when I try to run the report.
> >
> >The dataset below (dsOrgs) first calls a stored procedure
> >(SV_GetSubordinates) that populates a table with
> hierarchical data.
> >The second part (the Select statement) then retrieves
> the data
> >produced by the stored procedure. I have no problem
> running this set
> >of SQL statements in the Data view of the Reporting
> Services Report
> >Designer.
> >
> >EXEC SV_GetSubordinates @.INum,'Groups',@.OrgNum
> >SELECT v_Orgs.*
> >FROM v_Orgs, SVSiblings
> >WHERE
> > v_Orgs.INum = @.INum AND
> > v_Orgs.INum = SVSiblings.INum AND
> > v_Orgs.OrgNum = SVSiblings.Num
> >
> >By the way, @.INum is a parameter that will be passed to
> the report in
> >a URL string eventually. But for now, I have to use
> both the Preview
> >capability of the Report Designer and the Report Manager
> rendering
> >engine to test out my report.
> >
> >I have another dataset that gets the @.OrgNum parameter
> value from a
> >selection in a drop down in my report. Here is the
> query for that
> >data set...
> >
> >SELECT
> > NULL AS OrgNum,
> > '-- ALL Orgs --' AS [Description]
> >FROM SVGroupDefs
> >WHERE
> > INum = @.INum
> >UNION
> >SELECT
> > OrgNum,
> > [Description]
> >FROM SVGroupDefs
> >WHERE
> > INum = @.INum
> >ORDER BY [Description]
> >
> >As you can see, I'm using @.INum in this dataset first so
> I can
> >populate my drop down list. When the user selects an
> Organization
> >from the drop down list, the selection returns the value
> for the
> >parameter @.OrgNum, which is used in my dsOrgs dataset
> along with @.INum
> >to retrieve the hierarchical data for my report.
> >
> >You may be asking why I need to get hierarchical data
> when the table
> >object in the report designer uses a parent-child
> relationship. The
> >reason why I'm going through all this pain is because I
> need to
> >recursively get all the children from a starting parent
> level, which
> >@.OrgNum supplies. SQL Server does not natively support
> a way to
> >recursively get all the children in a hierarchy. The
> only way to do
> >this is to run through my stored procedure, which
> recursively calls
> >itself and then populates a table with the child OrgNum
> values
> >(fortunately Yukon has solved this recursive nightmare).
> >
> >Anyway, how can I generate my report when the error
> states I must
> >first declare @.INum?
> >.
> >|||In case anyone cares, I solved this problem myself.
The problem was related to a dataset that first calls a stored
procedure to populate a table with recursive data and then runs a
select statement to retrieve a set of data filtered by the recursive
data. What was happening was that Reporting Services was erroring
because my select was trying to fire before my stored procedure
finished. I ended up gettting a "Must declare variable" error (among
other things).
The trick is to make the select statement "wait" for the stored
procedure. How do you do this, you ask? It's simple.
You have to declare a variable and then set the execution of the
stored procedure to the variable. The select statement that follows
has to wait for the variable above to get populated with a "0", which
means the stored procedure executed properly. Then the select will
fire.
Here's a sample dataset query that uses the variable wait method:
DECLARE @.ResultValue INT
EXEC @.ResultNum = SV_GetSubordinates @.INum,'Goals',@.GoalNum
SELECT *
FROM v_Goals
WHERE
GoalNum IN (
SELECT Num
FROM SVSiblings
WHERE
INum = @.INum AND
Type = 'Goals' AND
UserID = USER_ID())
See the part about "EXEC @.ResultNum = "? That's the key to avoiding
the "Must declare variable" error I encountered and the misfiring of
the select statement. The select part of the query waits for
@.ResultNum to be populated with a "0" before getting run.
steve.pantazis@.salesviz.com (Steve Pantazis) wrote in message news:<45c5a039.0409032037.1e978da4@.posting.google.com>...
> Melissa,
> Reporting Services does a fine job handling parent-child sets of
> data in tables and what not...but that's assuming you have a data set
> with all the data you want. What I have is an entire hierarchy, of
> which, only one part I might want to retrieve in the data set for the
> report (e.g. pulling back a region and their branches vs. the whole
> entire organization). So, the whole problem here is about retrieving
> the data and not about how Reporting Services will handle it after the
> data is retrieved.
> With that being said, I've already tried to break out the stored
> procedure that gets all the children vs. the SQL query that retrieves
> the data produced by the stored procedure into two separate data sets.
> But how would Reporting Services know when the stored procedure has
> finished in order to run the second query, which retrieves the data?
> By arbitrarily setting a timeout? I find that method too unreliable.
> I've already broken out my stored procedure and select statement into
> two data sets, but that doesn't work.
> So, what else have I done? I've tried to do a recursive SQL
> function, but to no avail (functions can't recursive do selects of
> data), a recursive stored procedure with a varying output parameter
> (Reporting Services allows only 1 value per parameter for the current
> release), but to no avail, setting the output of my stored procedure
> to a temp table (can't seem to get that to work), but to no avail,
> using a global temp table within my stored procedure (doesn't work
> because the stored procedure recursively calls itself and you can only
> declare the global temp table once), but to no avail, and a bunch of
> other techniques in order to recursively grab all the children for the
> region I select for my data set. The Yukon release of SQL Server will
> solve my problem, because I will be able to execute a single
> expression and retrieve the recursive data I need in a single
> operation...but I need something in the meantime. (By the way, Oracle
> already supports recursion with their Connect method).
> So, I ended up having to pair the stored procedure with my select in
> the same dataset in order to 1) generate a list of child values and 2)
> retrieve that list of values AFTER they are generated. The dataset
> refreshes no problem, but I get that stupid "need to declare @.INum
> first" error, which I can't get rid of...it's so frustrating. I wish
> I could speak to one of the Reporting Services developers over the
> phone and figure this out.
>
> "Melissa" <anonymous@.discussions.microsoft.com> wrote in message news:<028801c491fd$3c204e00$a401280a@.phx.gbl>...
> > Hi Steve,
> > I am doing something sort of similar... I have created a
> > stored procedure that takes in a couple of parameters and
> > passes them to the database and creates a table. I then
> > have a query that selects the data from the table. I
> > created two seperate datasets one for the stored
> > procedure and one for the select statement and it seems
> > to be working. I'm not sure how you tell it what to
> > execute first but another report writer here is doing the
> > same but she had to increase the timeout to give the
> > stored procedure a chance to finish otherwise it was
> > throwing errors. Sorry I'm not much help but I'm curious
> > if you've tried to use the parent group within the group
> > that is suppose to recursively search in a parent-child
> > relationship? I have the same exact thing to do that you
> > are doing and I would love to hear any lessons learned.
> > Thanks!!
> > >--Original Message--
> > >I'm having the most difficult time trying to generate a
> report that
> > >first calls a stored procedure and then retrieves the
> data produced
> > >from it.
> > >
> > >I get the error, "An error has occurred during report
> > >processing...query execution failed for data set
> dsOrgs...Must declare
> > >the variable @.INum" when I try to run the report.
> > >
> > >The dataset below (dsOrgs) first calls a stored procedure
> > >(SV_GetSubordinates) that populates a table with
> hierarchical data.
> > >The second part (the Select statement) then retrieves
> the data
> > >produced by the stored procedure. I have no problem
> running this set
> > >of SQL statements in the Data view of the Reporting
> Services Report
> > >Designer.
> > >
> > >EXEC SV_GetSubordinates @.INum,'Groups',@.OrgNum
> > >SELECT v_Orgs.*
> > >FROM v_Orgs, SVSiblings
> > >WHERE
> > > v_Orgs.INum = @.INum AND
> > > v_Orgs.INum = SVSiblings.INum AND
> > > v_Orgs.OrgNum = SVSiblings.Num
> > >
> > >By the way, @.INum is a parameter that will be passed to
> the report in
> > >a URL string eventually. But for now, I have to use
> both the Preview
> > >capability of the Report Designer and the Report Manager
> rendering
> > >engine to test out my report.
> > >
> > >I have another dataset that gets the @.OrgNum parameter
> value from a
> > >selection in a drop down in my report. Here is the
> query for that
> > >data set...
> > >
> > >SELECT
> > > NULL AS OrgNum,
> > > '-- ALL Orgs --' AS [Description]
> > >FROM SVGroupDefs
> > >WHERE
> > > INum = @.INum
> > >UNION
> > >SELECT
> > > OrgNum,
> > > [Description]
> > >FROM SVGroupDefs
> > >WHERE
> > > INum = @.INum
> > >ORDER BY [Description]
> > >
> > >As you can see, I'm using @.INum in this dataset first so
> I can
> > >populate my drop down list. When the user selects an
> Organization
> > >from the drop down list, the selection returns the value
> for the
> > >parameter @.OrgNum, which is used in my dsOrgs dataset
> along with @.INum
> > >to retrieve the hierarchical data for my report.
> > >
> > >You may be asking why I need to get hierarchical data
> when the table
> > >object in the report designer uses a parent-child
> relationship. The
> > >reason why I'm going through all this pain is because I
> need to
> > >recursively get all the children from a starting parent
> level, which
> > >@.OrgNum supplies. SQL Server does not natively support
> a way to
> > >recursively get all the children in a hierarchy. The
> only way to do
> > >this is to run through my stored procedure, which
> recursively calls
> > >itself and then populates a table with the child OrgNum
> values
> > >(fortunately Yukon has solved this recursive nightmare).
> > >
> > >Anyway, how can I generate my report when the error
> states I must
> > >first declare @.INum?
> > >.
> > >

"dbuse" fails when database name is larger than 28 characters

Hi,

We have troubles when we try to use the 'dbuse' calls with databases
larger than 28 characters, looks like the dbuse truncates the name
after it.

Any ideas ?[posted and mailed, please reply in news]

Eyal Goren (eyal_goren@.bmc.com) writes:
> We have troubles when we try to use the 'dbuse' calls with databases
> larger than 28 characters, looks like the dbuse truncates the name
> after it.

Are you really sure that it is 28? I would expect the limit to be 30,
and testing confirms that.

DB-Library is a client library that Microsoft has deprecated and has
not developed since the relase of SQL Server 6.5. This means that you
do not have support for features added in SQL 7 or later. In 6.5 the
maximum length of an object name (database, table, stored procedure etc)
was 30 characters. This was increased to 127 characters in SQL7. However
DB-Library does not know about this and truncates after 30 chars.

In this particular case there is a fairly simple workaround: use dbcmd
and dbsqlexec to pass a USE command instead.

For the long term, I would recommend that you plan for migrating your
DB-library apps to some other client library.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi,

Thanks for the workaround, it seems to work.

Just one small question:

I added the following code-

r = dbcmd (login_rec->dbproc, command);
if (r == SUCCEED) r = dbsqlexec (login_rec->dbproc);

dbcancel (login_rec->dbproc);

I added the dbcancel to clear the buffer, is that O.K. for this purpose ?

Thanks
Eyal|||Eyal Goren (eyal_goren@.bmc.com) writes:
> Thanks for the workaround, it seems to work.
> Just one small question:
> I added the following code-
> r = dbcmd (login_rec->dbproc, command);
> if (r == SUCCEED) r = dbsqlexec (login_rec->dbproc);
>
> dbcancel (login_rec->dbproc);
> I added the dbcancel to clear the buffer, is that O.K. for this purpose
> ?

I can't see that there could be any problems in this case. But personally
I would have done the regular dbresults() loop instead. I've seen ugly
things happen with canceling queries.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns94ED6DB081AE7Yazorman@.127.0.0.1>...
> Eyal Goren (eyal_goren@.bmc.com) writes:
> > Thanks for the workaround, it seems to work.
> > Just one small question:
> > I added the following code-
> > r = dbcmd (login_rec->dbproc, command);
> > if (r == SUCCEED) r = dbsqlexec (login_rec->dbproc);
> > dbcancel (login_rec->dbproc);
> > I added the dbcancel to clear the buffer, is that O.K. for this purpose
> > ?
> I can't see that there could be any problems in this case. But personally
> I would have done the regular dbresults() loop instead. I've seen ugly
> things happen with canceling queries.

Thanks for your help!!!

"Cannot find keycodev2.dll or invalid keycode" for certain user account

Hi,

I've created program that sits on a server and provides some reporting functionality. The user submits a SQL Server job that calls this program, passing in some command args.

The SQL Server job is submitted by a C# program that is using a non-sysadmin connection account, therefore it is calling SETUSER to change the job mapping to a local user account, called MYSERVER\SQLServerJobs - I've granted it local admin rights.

Whenever SQL Server attempts to run this job, it's failing with the "Cannot find keycodev2.dll or invalid keycode" error. It's definitely not an install problem, I can run the program from command line using a domain account and it's fine. I guess its a permissions problem with the local account, but I can't put my finger on it!

Again, the local account is in the Administrators group & I've even explicitly granted it rights to the two registry keys

HKEY_CLASSES_ROOT\Interface\{4A4D268A-DF9B-4FC1-8301-D9FEEEF69F9C}

- HKEY_CLASSES_ROOT\TypeLib\{8D43D0B9-C14E-4577-9E67-A9A1EEF82302}

as detailed in some Crystal troubleshooting documents.

Anything else I should check for??

ThanksSOLVED:

I went back onto the machine, logging on as the local user account SQLServerJobs. I then reinstalled the 'CrystallInstaller' setup I had created (just all the merge modules that are required) & made sure the 'For everyone' box was checked - Now works fine.

I reckon i flew through the install the first time round and didn't check the 'For Everyone' box - can't remember to be honest, but would make sense!!