Hi all i am using crystal report with asp .net.
i am using report files already created an configured with a dsn.
when i try to view the report using crystal report viewer in a web form i am getting the error
"CrystalDecisions.CrystalReports.Engine.LogOnException: Logon failed."
i am using
<code>
ConnectionInfo cnnInfo = new ConnectionInfo ( ) ;
cnnInfo.ServerName = strConnInformation[0] ;
cnnInfo.DatabaseName = strConnInformation[1] ;
cnnInfo.UserID = strConnInformation[2] ;
cnnInfo.Password = strConnInformation[3] ;
ReportDocument rptDoc = new ReportDocument ( ) ;
rptDoc.Load ( Server.MapPath ( "..//Reports//" + reportFileName ),
OpenReportMethod.OpenReportByDefault ) ;
//CrystalDecisions.CrystalReports.Engine.Table table = rptDoc.Database.Tables[0] ;
CrystalDecisions.CrystalReports.Engine.Tables rptTables = rptDoc.Database.Tables ;
foreach ( CrystalDecisions.CrystalReports.Engine.Table tbl in rptTables )
{
tblLogOnInfo = tbl.LogOnInfo ;
tblLogOnInfo.ConnectionInfo = cnnInfo ;
tbl.ApplyLogOnInfo ( tblLogOnInfo ) ;
if ( tbl.Location.IndexOf ( "." ) > 0 )
{
tbl.Location = tbl.Location.Substring (
tbl.Location.LastIndexOf(".") + 1 ) ;
}
else
{
//tbl.Location = tbl.Location ;
}
}
</code>
its working fine when i try to execute a report file which configured direct to a database in another machine by specifying the server name, database name, user id and the password,
its showing the error only when i try to execute the report which configured using dsn. dsn is created in my machine and the actual database server is another one.
can any one help me in this matter as i have to use the reports with dsn only in my application.
i got the informations from the net that its same for both type of connectivity to use the ConnectionInfo object and the TableLogonInfo objects.
what my be the problem with me.
is its related to any permision issues.Hai Vinod,
Even i faced the same problem with my C# and Access .
But this prblem arised only when i have a subreport in my main report.
Do u have any subreports in ur appli...If so u have to provide login information to those reports also.
regards,
Kolluru.|||Thanks for your reply kolluru. i will try this and will get back :)|||Do u have any subreports in ur appli...If so u have to provide login information to those reports also.
Im having the same problem and I have subreports. How do you provide the login details for the subreports?sql
Showing posts with label report. Show all posts
Showing posts with label report. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
Log Import Problem using Reporting Services
Hi,
I am creating a report using MSS and the Microsoft utility
MSSLogToDatabase utility. The utility seems to work great until there
is an attempt to specify a subdirectory for the /reportfolder
parameter.
I have searched far and wide in diverse newsgroups, google, microsoft's
site and etc. but can't seem to get a definitive answer as to whether
or not there is a way to have the report created on the Reporting
Server Website that is contained within another folder.
Documentation is very concise and I can't find any additional
information for using the utility. The description for the
/reportfolder:value switch is "Specifies a name for the target
Reporting Services folder. Optional. If unspecified, Speech Application
Reports is used."
I have tried everything in terms of different methods of designating a
subfolder such as:
/reportfolder:myReportFolder/myReport
/reportfolder:myReportFolder\myReport
/reportfolder:myReportFolder%2fmyReport
Any attempt to create a subdirectory structure on the Reporting Server
has either failed altogether with this excerpt error:
Failed to create folder for reports on the specified SQL Reporting
Services host:
The name of the item 'myReportFolder/my
Report is not valid. The name must be less than 260 characters long,
must not start with slash; other restrictions apply. Check the
documentation for complete set of restrictions.
Or it creates a single folder on the homepage with a name like,
myReportFolder%2fmyReport.
Unfortunately, I have suspicion that the error is very vague and a bit
too generic to indicate the actual cause of failure since complying
with the character restraints still doesn't produce the subdirectory
structure.
I have even explored the data structure and layout for the webcontent
in the catalogs table of the Reports database to hopefully find a clue
but to no satisfactory resolve.
In a nutshell, there is only one of 2 answers for this problem.
1. The utility is limited in this capacity and since there isn't any
method of resolving or working around the problem to get the desired
results.
2. The utility is capable of performing the requested action, but the
documentation is very poor when it comes to more elaborative
descriptions of its features.
If it is the latter of the 2 and someone can provide a viable
workaround or answer, that would be great!!!!
Thanks,
MarkWish that I could help.. I've got some reports that have paths that are
~100 characters; etc and they are coughing on this same error message;
but it's not specific to MSSLogToDatabase utility
I had heard it's a bug with a bad error description; I wish I could
find the resolution.
Does anyone know which characters we're allowed to use in a report
title'
-Aaron
mivey4 wrote:
> Hi,
> I am creating a report using MSS and the Microsoft utility
> MSSLogToDatabase utility. The utility seems to work great until there
> is an attempt to specify a subdirectory for the /reportfolder
> parameter.
> I have searched far and wide in diverse newsgroups, google, microsoft's
> site and etc. but can't seem to get a definitive answer as to whether
> or not there is a way to have the report created on the Reporting
> Server Website that is contained within another folder.
> Documentation is very concise and I can't find any additional
> information for using the utility. The description for the
> /reportfolder:value switch is "Specifies a name for the target
> Reporting Services folder. Optional. If unspecified, Speech Application
> Reports is used."
> I have tried everything in terms of different methods of designating a
> subfolder such as:
> /reportfolder:myReportFolder/myReport
> /reportfolder:myReportFolder\myReport
> /reportfolder:myReportFolder%2fmyReport
> Any attempt to create a subdirectory structure on the Reporting Server
> has either failed altogether with this excerpt error:
> Failed to create folder for reports on the specified SQL Reporting
> Services host:
> The name of the item 'myReportFolder/my
> Report is not valid. The name must be less than 260 characters long,
> must not start with slash; other restrictions apply. Check the
> documentation for complete set of restrictions.
> Or it creates a single folder on the homepage with a name like,
> myReportFolder%2fmyReport.
> Unfortunately, I have suspicion that the error is very vague and a bit
> too generic to indicate the actual cause of failure since complying
> with the character restraints still doesn't produce the subdirectory
> structure.
> I have even explored the data structure and layout for the webcontent
> in the catalogs table of the Reports database to hopefully find a clue
> but to no satisfactory resolve.
> In a nutshell, there is only one of 2 answers for this problem.
> 1. The utility is limited in this capacity and since there isn't any
> method of resolving or working around the problem to get the desired
> results.
> 2. The utility is capable of performing the requested action, but the
> documentation is very poor when it comes to more elaborative
> descriptions of its features.
> If it is the latter of the 2 and someone can provide a viable
> workaround or answer, that would be great!!!!
> Thanks,
> Mark|||Hi Aaron,
I may be able to provide some assistance to you concerning your
inquiry.
I have found that the following set of rules apply to virtually any
path designated within MSS because it is based on a web-based platform:
A. The full name of the item (including the path) cannot exceed 260
characters.
B. Names cannot be null, consist of any empty string
C. Names cannot include any of these reserved characters: (for URL
paths)
: ? ; @. ' = + $ , \ * > < | . " : =D. You cannot end an item name with a backslash character.
Although my issue was a bit different the same sets of rules still
applied because I was attempting to specify a path to the report via
the use of \.
When I attempted to use / I believe the error was thrown simply because
the tool was limited to creating a directory structure in the root only
in addition to the fact that the / was probably reserved as a switch
parameter at the command line from which the utility was run as well,
because the / wasn't an invalid URL character.
As far as naming your reports goes, there aren't any restrictions that
I am aware of that would potentially prohibit you from naming your
reports whatever you desire. I proved this by enclosing the report name
in double quotes which explicitly designate every character within the
quotes as being a literal string. e.g. "Reports/myReports" and
"Reports\myReports".
The problem was by doing so, the intended path was interpreted as a
literal string and not as the path. I was then left with a Report
called Reports/myReports.
I couldn't resolve the problem since it was apparently not a problem
but a mere limitation of the utility. I did confirm that short of this
issue, the operation didn't totally crash but continued gracefully with
the remaining tasks.
I hope I have helped clear up some of your questions. If not, e-mail me
and I'll assist in anyway I can.
Thanks!
Mark
aaron.kempf@.gmail.com wrote:
> Wish that I could help.. I've got some reports that have paths that are
> ~100 characters; etc and they are coughing on this same error message;
> but it's not specific to MSSLogToDatabase utility
> I had heard it's a bug with a bad error description; I wish I could
> find the resolution.
> Does anyone know which characters we're allowed to use in a report
> title'
> -Aaron
>
> mivey4 wrote:
> > Hi,
> >
> > I am creating a report using MSS and the Microsoft utility
> > MSSLogToDatabase utility. The utility seems to work great until there
> > is an attempt to specify a subdirectory for the /reportfolder
> > parameter.
> >
> > I have searched far and wide in diverse newsgroups, google, microsoft's
> > site and etc. but can't seem to get a definitive answer as to whether
> > or not there is a way to have the report created on the Reporting
> > Server Website that is contained within another folder.
> >
> > Documentation is very concise and I can't find any additional
> > information for using the utility. The description for the
> > /reportfolder:value switch is "Specifies a name for the target
> > Reporting Services folder. Optional. If unspecified, Speech Application
> > Reports is used."
> >
> > I have tried everything in terms of different methods of designating a
> > subfolder such as:
> > /reportfolder:myReportFolder/myReport
> > /reportfolder:myReportFolder\myReport
> > /reportfolder:myReportFolder%2fmyReport
> >
> > Any attempt to create a subdirectory structure on the Reporting Server
> > has either failed altogether with this excerpt error:
> > Failed to create folder for reports on the specified SQL Reporting
> > Services host:
> >
> > The name of the item 'myReportFolder/my
> > Report is not valid. The name must be less than 260 characters long,
> > must not start with slash; other restrictions apply. Check the
> > documentation for complete set of restrictions.
> >
> > Or it creates a single folder on the homepage with a name like,
> > myReportFolder%2fmyReport.
> >
> > Unfortunately, I have suspicion that the error is very vague and a bit
> > too generic to indicate the actual cause of failure since complying
> > with the character restraints still doesn't produce the subdirectory
> > structure.
> >
> > I have even explored the data structure and layout for the webcontent
> > in the catalogs table of the Reports database to hopefully find a clue
> > but to no satisfactory resolve.
> >
> > In a nutshell, there is only one of 2 answers for this problem.
> >
> > 1. The utility is limited in this capacity and since there isn't any
> > method of resolving or working around the problem to get the desired
> > results.
> > 2. The utility is capable of performing the requested action, but the
> > documentation is very poor when it comes to more elaborative
> > descriptions of its features.
> >
> > If it is the latter of the 2 and someone can provide a viable
> > workaround or answer, that would be great!!!!
> >
> > Thanks,
> > Mark|||lol it was actually a SPACE
filename_.rdl with the underscore representing a SPACE
dont know how I missed it; seems like it should have automatically
fixed itself; but i found it; fixed a half dozen reports with minimal
effort
-Aaron
mivey4 wrote:
> Hi Aaron,
> I may be able to provide some assistance to you concerning your
> inquiry.
> I have found that the following set of rules apply to virtually any
> path designated within MSS because it is based on a web-based platform:
> A. The full name of the item (including the path) cannot exceed 260
> characters.
> B. Names cannot be null, consist of any empty string
> C. Names cannot include any of these reserved characters: (for URL
> paths)
> : ? ; @. ' = + $ , \ * > < | . " : => D. You cannot end an item name with a backslash character.
> Although my issue was a bit different the same sets of rules still
> applied because I was attempting to specify a path to the report via
> the use of \.
> When I attempted to use / I believe the error was thrown simply because
> the tool was limited to creating a directory structure in the root only
> in addition to the fact that the / was probably reserved as a switch
> parameter at the command line from which the utility was run as well,
> because the / wasn't an invalid URL character.
> As far as naming your reports goes, there aren't any restrictions that
> I am aware of that would potentially prohibit you from naming your
> reports whatever you desire. I proved this by enclosing the report name
> in double quotes which explicitly designate every character within the
> quotes as being a literal string. e.g. "Reports/myReports" and
> "Reports\myReports".
> The problem was by doing so, the intended path was interpreted as a
> literal string and not as the path. I was then left with a Report
> called Reports/myReports.
> I couldn't resolve the problem since it was apparently not a problem
> but a mere limitation of the utility. I did confirm that short of this
> issue, the operation didn't totally crash but continued gracefully with
> the remaining tasks.
> I hope I have helped clear up some of your questions. If not, e-mail me
> and I'll assist in anyway I can.
> Thanks!
> Mark
>
> aaron.kempf@.gmail.com wrote:
> > Wish that I could help.. I've got some reports that have paths that are
> > ~100 characters; etc and they are coughing on this same error message;
> > but it's not specific to MSSLogToDatabase utility
> >
> > I had heard it's a bug with a bad error description; I wish I could
> > find the resolution.
> >
> > Does anyone know which characters we're allowed to use in a report
> > title'
> >
> > -Aaron
> >
> >
> >
> > mivey4 wrote:
> > > Hi,
> > >
> > > I am creating a report using MSS and the Microsoft utility
> > > MSSLogToDatabase utility. The utility seems to work great until there
> > > is an attempt to specify a subdirectory for the /reportfolder
> > > parameter.
> > >
> > > I have searched far and wide in diverse newsgroups, google, microsoft's
> > > site and etc. but can't seem to get a definitive answer as to whether
> > > or not there is a way to have the report created on the Reporting
> > > Server Website that is contained within another folder.
> > >
> > > Documentation is very concise and I can't find any additional
> > > information for using the utility. The description for the
> > > /reportfolder:value switch is "Specifies a name for the target
> > > Reporting Services folder. Optional. If unspecified, Speech Application
> > > Reports is used."
> > >
> > > I have tried everything in terms of different methods of designating a
> > > subfolder such as:
> > > /reportfolder:myReportFolder/myReport
> > > /reportfolder:myReportFolder\myReport
> > > /reportfolder:myReportFolder%2fmyReport
> > >
> > > Any attempt to create a subdirectory structure on the Reporting Server
> > > has either failed altogether with this excerpt error:
> > > Failed to create folder for reports on the specified SQL Reporting
> > > Services host:
> > >
> > > The name of the item 'myReportFolder/my
> > > Report is not valid. The name must be less than 260 characters long,
> > > must not start with slash; other restrictions apply. Check the
> > > documentation for complete set of restrictions.
> > >
> > > Or it creates a single folder on the homepage with a name like,
> > > myReportFolder%2fmyReport.
> > >
> > > Unfortunately, I have suspicion that the error is very vague and a bit
> > > too generic to indicate the actual cause of failure since complying
> > > with the character restraints still doesn't produce the subdirectory
> > > structure.
> > >
> > > I have even explored the data structure and layout for the webcontent
> > > in the catalogs table of the Reports database to hopefully find a clue
> > > but to no satisfactory resolve.
> > >
> > > In a nutshell, there is only one of 2 answers for this problem.
> > >
> > > 1. The utility is limited in this capacity and since there isn't any
> > > method of resolving or working around the problem to get the desired
> > > results.
> > > 2. The utility is capable of performing the requested action, but the
> > > documentation is very poor when it comes to more elaborative
> > > descriptions of its features.
> > >
> > > If it is the latter of the 2 and someone can provide a viable
> > > workaround or answer, that would be great!!!!
> > >
> > > Thanks,
> > > Mark
I am creating a report using MSS and the Microsoft utility
MSSLogToDatabase utility. The utility seems to work great until there
is an attempt to specify a subdirectory for the /reportfolder
parameter.
I have searched far and wide in diverse newsgroups, google, microsoft's
site and etc. but can't seem to get a definitive answer as to whether
or not there is a way to have the report created on the Reporting
Server Website that is contained within another folder.
Documentation is very concise and I can't find any additional
information for using the utility. The description for the
/reportfolder:value switch is "Specifies a name for the target
Reporting Services folder. Optional. If unspecified, Speech Application
Reports is used."
I have tried everything in terms of different methods of designating a
subfolder such as:
/reportfolder:myReportFolder/myReport
/reportfolder:myReportFolder\myReport
/reportfolder:myReportFolder%2fmyReport
Any attempt to create a subdirectory structure on the Reporting Server
has either failed altogether with this excerpt error:
Failed to create folder for reports on the specified SQL Reporting
Services host:
The name of the item 'myReportFolder/my
Report is not valid. The name must be less than 260 characters long,
must not start with slash; other restrictions apply. Check the
documentation for complete set of restrictions.
Or it creates a single folder on the homepage with a name like,
myReportFolder%2fmyReport.
Unfortunately, I have suspicion that the error is very vague and a bit
too generic to indicate the actual cause of failure since complying
with the character restraints still doesn't produce the subdirectory
structure.
I have even explored the data structure and layout for the webcontent
in the catalogs table of the Reports database to hopefully find a clue
but to no satisfactory resolve.
In a nutshell, there is only one of 2 answers for this problem.
1. The utility is limited in this capacity and since there isn't any
method of resolving or working around the problem to get the desired
results.
2. The utility is capable of performing the requested action, but the
documentation is very poor when it comes to more elaborative
descriptions of its features.
If it is the latter of the 2 and someone can provide a viable
workaround or answer, that would be great!!!!
Thanks,
MarkWish that I could help.. I've got some reports that have paths that are
~100 characters; etc and they are coughing on this same error message;
but it's not specific to MSSLogToDatabase utility
I had heard it's a bug with a bad error description; I wish I could
find the resolution.
Does anyone know which characters we're allowed to use in a report
title'
-Aaron
mivey4 wrote:
> Hi,
> I am creating a report using MSS and the Microsoft utility
> MSSLogToDatabase utility. The utility seems to work great until there
> is an attempt to specify a subdirectory for the /reportfolder
> parameter.
> I have searched far and wide in diverse newsgroups, google, microsoft's
> site and etc. but can't seem to get a definitive answer as to whether
> or not there is a way to have the report created on the Reporting
> Server Website that is contained within another folder.
> Documentation is very concise and I can't find any additional
> information for using the utility. The description for the
> /reportfolder:value switch is "Specifies a name for the target
> Reporting Services folder. Optional. If unspecified, Speech Application
> Reports is used."
> I have tried everything in terms of different methods of designating a
> subfolder such as:
> /reportfolder:myReportFolder/myReport
> /reportfolder:myReportFolder\myReport
> /reportfolder:myReportFolder%2fmyReport
> Any attempt to create a subdirectory structure on the Reporting Server
> has either failed altogether with this excerpt error:
> Failed to create folder for reports on the specified SQL Reporting
> Services host:
> The name of the item 'myReportFolder/my
> Report is not valid. The name must be less than 260 characters long,
> must not start with slash; other restrictions apply. Check the
> documentation for complete set of restrictions.
> Or it creates a single folder on the homepage with a name like,
> myReportFolder%2fmyReport.
> Unfortunately, I have suspicion that the error is very vague and a bit
> too generic to indicate the actual cause of failure since complying
> with the character restraints still doesn't produce the subdirectory
> structure.
> I have even explored the data structure and layout for the webcontent
> in the catalogs table of the Reports database to hopefully find a clue
> but to no satisfactory resolve.
> In a nutshell, there is only one of 2 answers for this problem.
> 1. The utility is limited in this capacity and since there isn't any
> method of resolving or working around the problem to get the desired
> results.
> 2. The utility is capable of performing the requested action, but the
> documentation is very poor when it comes to more elaborative
> descriptions of its features.
> If it is the latter of the 2 and someone can provide a viable
> workaround or answer, that would be great!!!!
> Thanks,
> Mark|||Hi Aaron,
I may be able to provide some assistance to you concerning your
inquiry.
I have found that the following set of rules apply to virtually any
path designated within MSS because it is based on a web-based platform:
A. The full name of the item (including the path) cannot exceed 260
characters.
B. Names cannot be null, consist of any empty string
C. Names cannot include any of these reserved characters: (for URL
paths)
: ? ; @. ' = + $ , \ * > < | . " : =D. You cannot end an item name with a backslash character.
Although my issue was a bit different the same sets of rules still
applied because I was attempting to specify a path to the report via
the use of \.
When I attempted to use / I believe the error was thrown simply because
the tool was limited to creating a directory structure in the root only
in addition to the fact that the / was probably reserved as a switch
parameter at the command line from which the utility was run as well,
because the / wasn't an invalid URL character.
As far as naming your reports goes, there aren't any restrictions that
I am aware of that would potentially prohibit you from naming your
reports whatever you desire. I proved this by enclosing the report name
in double quotes which explicitly designate every character within the
quotes as being a literal string. e.g. "Reports/myReports" and
"Reports\myReports".
The problem was by doing so, the intended path was interpreted as a
literal string and not as the path. I was then left with a Report
called Reports/myReports.
I couldn't resolve the problem since it was apparently not a problem
but a mere limitation of the utility. I did confirm that short of this
issue, the operation didn't totally crash but continued gracefully with
the remaining tasks.
I hope I have helped clear up some of your questions. If not, e-mail me
and I'll assist in anyway I can.
Thanks!
Mark
aaron.kempf@.gmail.com wrote:
> Wish that I could help.. I've got some reports that have paths that are
> ~100 characters; etc and they are coughing on this same error message;
> but it's not specific to MSSLogToDatabase utility
> I had heard it's a bug with a bad error description; I wish I could
> find the resolution.
> Does anyone know which characters we're allowed to use in a report
> title'
> -Aaron
>
> mivey4 wrote:
> > Hi,
> >
> > I am creating a report using MSS and the Microsoft utility
> > MSSLogToDatabase utility. The utility seems to work great until there
> > is an attempt to specify a subdirectory for the /reportfolder
> > parameter.
> >
> > I have searched far and wide in diverse newsgroups, google, microsoft's
> > site and etc. but can't seem to get a definitive answer as to whether
> > or not there is a way to have the report created on the Reporting
> > Server Website that is contained within another folder.
> >
> > Documentation is very concise and I can't find any additional
> > information for using the utility. The description for the
> > /reportfolder:value switch is "Specifies a name for the target
> > Reporting Services folder. Optional. If unspecified, Speech Application
> > Reports is used."
> >
> > I have tried everything in terms of different methods of designating a
> > subfolder such as:
> > /reportfolder:myReportFolder/myReport
> > /reportfolder:myReportFolder\myReport
> > /reportfolder:myReportFolder%2fmyReport
> >
> > Any attempt to create a subdirectory structure on the Reporting Server
> > has either failed altogether with this excerpt error:
> > Failed to create folder for reports on the specified SQL Reporting
> > Services host:
> >
> > The name of the item 'myReportFolder/my
> > Report is not valid. The name must be less than 260 characters long,
> > must not start with slash; other restrictions apply. Check the
> > documentation for complete set of restrictions.
> >
> > Or it creates a single folder on the homepage with a name like,
> > myReportFolder%2fmyReport.
> >
> > Unfortunately, I have suspicion that the error is very vague and a bit
> > too generic to indicate the actual cause of failure since complying
> > with the character restraints still doesn't produce the subdirectory
> > structure.
> >
> > I have even explored the data structure and layout for the webcontent
> > in the catalogs table of the Reports database to hopefully find a clue
> > but to no satisfactory resolve.
> >
> > In a nutshell, there is only one of 2 answers for this problem.
> >
> > 1. The utility is limited in this capacity and since there isn't any
> > method of resolving or working around the problem to get the desired
> > results.
> > 2. The utility is capable of performing the requested action, but the
> > documentation is very poor when it comes to more elaborative
> > descriptions of its features.
> >
> > If it is the latter of the 2 and someone can provide a viable
> > workaround or answer, that would be great!!!!
> >
> > Thanks,
> > Mark|||lol it was actually a SPACE
filename_.rdl with the underscore representing a SPACE
dont know how I missed it; seems like it should have automatically
fixed itself; but i found it; fixed a half dozen reports with minimal
effort
-Aaron
mivey4 wrote:
> Hi Aaron,
> I may be able to provide some assistance to you concerning your
> inquiry.
> I have found that the following set of rules apply to virtually any
> path designated within MSS because it is based on a web-based platform:
> A. The full name of the item (including the path) cannot exceed 260
> characters.
> B. Names cannot be null, consist of any empty string
> C. Names cannot include any of these reserved characters: (for URL
> paths)
> : ? ; @. ' = + $ , \ * > < | . " : => D. You cannot end an item name with a backslash character.
> Although my issue was a bit different the same sets of rules still
> applied because I was attempting to specify a path to the report via
> the use of \.
> When I attempted to use / I believe the error was thrown simply because
> the tool was limited to creating a directory structure in the root only
> in addition to the fact that the / was probably reserved as a switch
> parameter at the command line from which the utility was run as well,
> because the / wasn't an invalid URL character.
> As far as naming your reports goes, there aren't any restrictions that
> I am aware of that would potentially prohibit you from naming your
> reports whatever you desire. I proved this by enclosing the report name
> in double quotes which explicitly designate every character within the
> quotes as being a literal string. e.g. "Reports/myReports" and
> "Reports\myReports".
> The problem was by doing so, the intended path was interpreted as a
> literal string and not as the path. I was then left with a Report
> called Reports/myReports.
> I couldn't resolve the problem since it was apparently not a problem
> but a mere limitation of the utility. I did confirm that short of this
> issue, the operation didn't totally crash but continued gracefully with
> the remaining tasks.
> I hope I have helped clear up some of your questions. If not, e-mail me
> and I'll assist in anyway I can.
> Thanks!
> Mark
>
> aaron.kempf@.gmail.com wrote:
> > Wish that I could help.. I've got some reports that have paths that are
> > ~100 characters; etc and they are coughing on this same error message;
> > but it's not specific to MSSLogToDatabase utility
> >
> > I had heard it's a bug with a bad error description; I wish I could
> > find the resolution.
> >
> > Does anyone know which characters we're allowed to use in a report
> > title'
> >
> > -Aaron
> >
> >
> >
> > mivey4 wrote:
> > > Hi,
> > >
> > > I am creating a report using MSS and the Microsoft utility
> > > MSSLogToDatabase utility. The utility seems to work great until there
> > > is an attempt to specify a subdirectory for the /reportfolder
> > > parameter.
> > >
> > > I have searched far and wide in diverse newsgroups, google, microsoft's
> > > site and etc. but can't seem to get a definitive answer as to whether
> > > or not there is a way to have the report created on the Reporting
> > > Server Website that is contained within another folder.
> > >
> > > Documentation is very concise and I can't find any additional
> > > information for using the utility. The description for the
> > > /reportfolder:value switch is "Specifies a name for the target
> > > Reporting Services folder. Optional. If unspecified, Speech Application
> > > Reports is used."
> > >
> > > I have tried everything in terms of different methods of designating a
> > > subfolder such as:
> > > /reportfolder:myReportFolder/myReport
> > > /reportfolder:myReportFolder\myReport
> > > /reportfolder:myReportFolder%2fmyReport
> > >
> > > Any attempt to create a subdirectory structure on the Reporting Server
> > > has either failed altogether with this excerpt error:
> > > Failed to create folder for reports on the specified SQL Reporting
> > > Services host:
> > >
> > > The name of the item 'myReportFolder/my
> > > Report is not valid. The name must be less than 260 characters long,
> > > must not start with slash; other restrictions apply. Check the
> > > documentation for complete set of restrictions.
> > >
> > > Or it creates a single folder on the homepage with a name like,
> > > myReportFolder%2fmyReport.
> > >
> > > Unfortunately, I have suspicion that the error is very vague and a bit
> > > too generic to indicate the actual cause of failure since complying
> > > with the character restraints still doesn't produce the subdirectory
> > > structure.
> > >
> > > I have even explored the data structure and layout for the webcontent
> > > in the catalogs table of the Reports database to hopefully find a clue
> > > but to no satisfactory resolve.
> > >
> > > In a nutshell, there is only one of 2 answers for this problem.
> > >
> > > 1. The utility is limited in this capacity and since there isn't any
> > > method of resolving or working around the problem to get the desired
> > > results.
> > > 2. The utility is capable of performing the requested action, but the
> > > documentation is very poor when it comes to more elaborative
> > > descriptions of its features.
> > >
> > > If it is the latter of the 2 and someone can provide a viable
> > > workaround or answer, that would be great!!!!
> > >
> > > Thanks,
> > > Mark
Monday, February 20, 2012
Locks, Scope & Performance
I'm getting LOCKS caused by a particular report that uses two sProcs to rend
er a
report.
I'm
as to why the following should be locking my contact calendar a
nd
history tables, they just do a select into a table, shouldn't this be read o
nly
and not cause any locks?
Is there a way to be sure that I exec as read only and do not create a table
lock?
Details....
The 1st sProc is called (nested) from within the second as
create proc my_sProc_Main
declare some vars, @.tmpTbl varchar(36)
set @.tmpTbl = replace('-',newID(),'9')
create a real table.... exec(create table table tblWorkingName'+@. tmpTbl)
Insert myTable exec my_sProc_Sub
(my_sProc_Sub - select complex conditions, union another set of complex
conditions, scan 1m row table return 1200 rows)
create another real results table.... exec(create table tblResultsName'+@.
tmpTbl)
Evaluate data in the first table (containing 1200 rows) and insert into the
second table about 20 resulting rows
return the results of the second table
drop both tables
Total process time between 13 and 56 seconds
I run similar processes, some as complex but not using two sProcs, however t
his
one report brings my server to its knees.
The production server is a Compaq dual 700ghz, 1gb Ram
My dev box is a single 1.4ghz, 1gb Ram and it execs somewhat faster and with
half the server killing effects.
TIA
JeffP....use NO LOCK hints on your SELECT statements
OR change isolation level to READ UNCOMMITTED
Greg Jackson
Portland, OR|||SELECT still issues shared resorce locks and INSERT EXEC runs the entire sub
procedure within the INSERT transaction. This combined with the fact you are
selecting from 1M rows is most likely your culprit.
You could override the locking policy on certian tables and tell it to use
page locks or no locks instead. or change the transaction isolation level to
a more lax level.
You may want to consider a different way of storing the intermediate data,
e.g. temp table which can be filled within the sub procedure and then the
calling procedure would get recompiled if necessary for the next step.
This would address the cause rather than the symptom and probably give a
significant performance boost into the bargain.
Mr Tea
"JDP@.Work" <JPGMTNoSpam@.sbcglobal.net> wrote in message
news:%23wEsvHuEFHA.628@.TK2MSFTNGP15.phx.gbl...
> I'm getting LOCKS caused by a particular report that uses two sProcs to
> render a
> report.
> I'm as to why the following should be locking my contact calendar
> and
> history tables, they just do a select into a table, shouldn't this be read
> only
> and not cause any locks?
> Is there a way to be sure that I exec as read only and do not create a
> table
> lock?
> Details....
> The 1st sProc is called (nested) from within the second as
> create proc my_sProc_Main
> declare some vars, @.tmpTbl varchar(36)
> set @.tmpTbl = replace('-',newID(),'9')
> create a real table.... exec(create table table tblWorkingName'+@. tmpTbl)
> Insert myTable exec my_sProc_Sub
> (my_sProc_Sub - select complex conditions, union another set of complex
> conditions, scan 1m row table return 1200 rows)
> create another real results table.... exec(create table tblResultsName'+@.
> tmpTbl)
>
> Evaluate data in the first table (containing 1200 rows) and insert into
> the
> second table about 20 resulting rows
> return the results of the second table
> drop both tables
> Total process time between 13 and 56 seconds
> I run similar processes, some as complex but not using two sProcs, however
> this
> one report brings my server to its knees.
> The production server is a Compaq dual 700ghz, 1gb Ram
> My dev box is a single 1.4ghz, 1gb Ram and it execs somewhat faster and
> with
> half the server killing effects.
> TIA
> JeffP....
>|||Thanks to pdxJaxon, I'm looking into with NOLOCK
if my exec(@.str) from looks like...
from
'+@.db+'.dbo.CONTACT1 contact1
,'+@.db+'.dbo.CONTACT2 contact2
,'+@.db+'.dbo.CAL cal
,'+@.db+'.dbo.conthist ch
,'+@.db+'.dbo.users
where
..adding...
from
'+@.db+'.dbo.CONTACT1 contact1 with NOLOCK
,'+@.db+'.dbo.CONTACT2 contact2 with NOLOCK
,'+@.db+'.dbo.CAL cal with NOLOCK
,'+@.db+'.dbo.conthist ch with NOLOCK
,'+@.db+'.dbo.users with NOLOCK
where
Lee,
I moved away from using the temp db #mytable to creating a table dynamically
named as noted in my pseudo snipit.
I did this because it improved performance from some now in place processes
that
used to bring the server to it's knees.
However I'm still interested and would appreciate a clarification
> e.g. temp table which can be filled within the sub procedure and then the
> calling procedure would get recompiled if necessary for the next step.
1. Is there any difference between using a table dynamically named within my
outer sProc and #myTemp?
2. Would the process you are suggesting look like a proc that calls a sub pr
oc
and creates w/recompile an inner sProc?
create proc my_sProc_Main_Outer
exec mysProc_sub inserting rows to a (temp) table
then...
create proc my_sProc_Main_Inner
with recomplike
as
....
return
drop table(s)
return
Exec'ing the sProc for the report would look something like....
exec my_sProc_Main_Outer @.db ,@.w_str ,@.action
TIA
JeffP...
"Lee Tudor" <mr_tea@.ntlworld.com> wrote in message
news:A5aQd.547$Se3.306@.newsfe5-win.ntli.net...
> SELECT still issues shared resorce locks and INSERT EXEC runs the entire s
ub
> procedure within the INSERT transaction. This combined with the fact you a
re
> selecting from 1M rows is most likely your culprit.
> You could override the locking policy on certian tables and tell it to use
> page locks or no locks instead. or change the transaction isolation level
to
> a more lax level.
> You may want to consider a different way of storing the intermediate data,
> e.g. temp table which can be filled within the sub procedure and then the
> calling procedure would get recompiled if necessary for the next step.
> This would address the cause rather than the symptom and probably give a
> significant performance boost into the bargain.
> Mr Tea
> "JDP@.Work" <JPGMTNoSpam@.sbcglobal.net> wrote in message
> news:%23wEsvHuEFHA.628@.TK2MSFTNGP15.phx.gbl...
>|||Understand that NOLOCK can give you invalid results, since it can see dirty
data. If having consistent results are critical, this is not a good idea.
If you can live with possible spurrious results, based on rows being picked
up that turn out to have been invalid, then it is fine. I don't know about
your architecture, but chances are you will have very litte of this unless
you have quite high throughput, but it is a concern.
A million rows is quite a few, but it is not an incredible amount. Consider
optimizing your query, and you might only have blocks that last a second or
two if any. A query that brings the server to its knees is often a sign
that you have table scans that are slowly walking through your table. It
might also be taking a table lock rather than rowlocks based on resource
utilization. This query could be the problem, but it could also be your
update queries or others. Can you tell us which query is blocking which?
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"JDP@.Work" <JPGMTNoSpam@.sbcglobal.net> wrote in message
news:ezFTA9uEFHA.3200@.TK2MSFTNGP10.phx.gbl...
> Thanks to pdxJaxon, I'm looking into with NOLOCK
> if my exec(@.str) from looks like...
> from
> '+@.db+'.dbo.CONTACT1 contact1
> ,'+@.db+'.dbo.CONTACT2 contact2
> ,'+@.db+'.dbo.CAL cal
> ,'+@.db+'.dbo.conthist ch
> ,'+@.db+'.dbo.users
> where
> ..adding...
> from
> '+@.db+'.dbo.CONTACT1 contact1 with NOLOCK
> ,'+@.db+'.dbo.CONTACT2 contact2 with NOLOCK
> ,'+@.db+'.dbo.CAL cal with NOLOCK
> ,'+@.db+'.dbo.conthist ch with NOLOCK
> ,'+@.db+'.dbo.users with NOLOCK
> where
> Lee,
> I moved away from using the temp db #mytable to creating a table
> dynamically
> named as noted in my pseudo snipit.
> I did this because it improved performance from some now in place
> processes that
> used to bring the server to it's knees.
> However I'm still interested and would appreciate a clarification
>
> 1. Is there any difference between using a table dynamically named within
> my
> outer sProc and #myTemp?
> 2. Would the process you are suggesting look like a proc that calls a sub
> proc
> and creates w/recompile an inner sProc?
> create proc my_sProc_Main_Outer
> exec mysProc_sub inserting rows to a (temp) table
> then...
> create proc my_sProc_Main_Inner
> with recomplike
> as
> ....
> return
> drop table(s)
> return
> Exec'ing the sProc for the report would look something like....
> exec my_sProc_Main_Outer @.db ,@.w_str ,@.action
> TIA
> JeffP...
>
> "Lee Tudor" <mr_tea@.ntlworld.com> wrote in message
> news:A5aQd.547$Se3.306@.newsfe5-win.ntli.net...
>|||When the Crystal (pronounced cryst-hel) report runs we see that all the tabl
es
are blocking other requests.
We have less than 25 users in the system and about another 50+/- running
reports, but another 500 who syncronize their data once a day this occurs ne
arly
all day long, someone is sync'ing with the server at most any time of the da
y.
My sProcs provide data for reports, if they miss an item on a report that th
ey
expect to see their first recourse is to think that the data has not sync'd
with
the server and they will re-sync, running the query after a moment should no
w
include any data that was being updated. Most users only have their own dat
a,
however it's not uncommon for our other daily updating and sales-process-cap
ture
to lock tables as well.
It was more pronounced that when one of the regional VPs would try to run th
e
report for the entire region we'd see that there were blocks and processes w
ould
hang as well as my dynamically named webReprt3984023984023840238402 tables w
ould
still be there due to the proc not completing or the user canceling.
The report takes a little over 2 min to render for an entire market center a
nd
little less than 20 seconds for an individual salesperson.
Another issue is that each time I optimize the sProcs' they add another colu
mn,
some are complex and are dependent on other table info to validate the resul
ts
data, this cross table, cross db stuff is just way too intense for such a lo
w
power system and I hear that we are getting a new production server in the n
ext
month or so.
I also see that workning on a low power system has a side benefit to encoura
ge
us to squeze as much as we can. Another issue is bandwidth which in a word
sucks.
Any I was able to get my outer sProc to run in as little as 4 seconds for a
sales person down from 1min44seconds to 19 seconds and now the 4 seconds.
I know that there must be substantially more optimizations available, howeve
r we
don't have a budget, so we are working for nearly free at this point just sa
ving
face that our stuff works at all. The client knows that they have a margina
l
network, a low power server and very complex reports.
This report for example is a whiteboard, filling in the data in each column
not
respectful of rows except that a particular column in that row is filled, it
loads data just as a human would fill in a sales whiteboard in the office fo
r
each salesperson, with first time visits scheduled, along with columns or %
probablity of closing columns for each pending sale, or targeted prospect.
Thanks to all.
JeffP...
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:eRIEKGxEFHA.3928@.TK2MSFTNGP15.phx.gbl...
> Understand that NOLOCK can give you invalid results, since it can see dirt
y
> data. If having consistent results are critical, this is not a good idea.
> If you can live with possible spurrious results, based on rows being picke
d
> up that turn out to have been invalid, then it is fine. I don't know abou
t
> your architecture, but chances are you will have very litte of this unless
> you have quite high throughput, but it is a concern.
> A million rows is quite a few, but it is not an incredible amount. Consid
er
> optimizing your query, and you might only have blocks that last a second o
r
> two if any. A query that brings the server to its knees is often a sign
> that you have table scans that are slowly walking through your table. It
> might also be taking a table lock rather than rowlocks based on resource
> utilization. This query could be the problem, but it could also be your
> update queries or others. Can you tell us which query is blocking which?
>
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "JDP@.Work" <JPGMTNoSpam@.sbcglobal.net> wrote in message
> news:ezFTA9uEFHA.3200@.TK2MSFTNGP10.phx.gbl...
>|||@.db? Now you're getting serious :)
I would try and acheive the desired result without too much dynamic SQL as
its pretty hard to tune. one way would be to put my_sProc_Main_Outer within
the master db and then call it in the context of a specific DB using
SET @.sp=@.db+'..sp_my_sProc_Main_Outer'
EXEC @.sp @.w_str ,@.action
if you dont like that then you will have to ditch the sp_ prefixes and put
the procs on each database
then your outer sproc would do its stuff (you may be able to simplify this
as well):
CREATE TABLE #tmp
EXEC sp_my_sProc_Main_Inner
Do something with data in #tmp.
your sproc inner would just add data to the temporary table:
insert #tmp select col001...coln FROM dbo. ...
when you return to the inner table, the db will check the stats on the
temporary table data and recompile it for better execution if necesary (if
you know that there is no need for dynamic recompilation then you can look
at some other way of storing the data, e.g. table variable and inner UDF)
getting rid of those dynamic clauses, if possible, allowing you to view and
check the query plan.
Mr Tea
"JDP@.Work" <JPGMTNoSpam@.sbcglobal.net> wrote in message
news:ezFTA9uEFHA.3200@.TK2MSFTNGP10.phx.gbl...
> Thanks to pdxJaxon, I'm looking into with NOLOCK
> if my exec(@.str) from looks like...
> from
> '+@.db+'.dbo.CONTACT1 contact1
> ,'+@.db+'.dbo.CONTACT2 contact2
> ,'+@.db+'.dbo.CAL cal
> ,'+@.db+'.dbo.conthist ch
> ,'+@.db+'.dbo.users
> where
> ..adding...
> from
> '+@.db+'.dbo.CONTACT1 contact1 with NOLOCK
> ,'+@.db+'.dbo.CONTACT2 contact2 with NOLOCK
> ,'+@.db+'.dbo.CAL cal with NOLOCK
> ,'+@.db+'.dbo.conthist ch with NOLOCK
> ,'+@.db+'.dbo.users with NOLOCK
> where
> Lee,
> I moved away from using the temp db #mytable to creating a table
> dynamically
> named as noted in my pseudo snipit.
> I did this because it improved performance from some now in place
> processes that
> used to bring the server to it's knees.
> However I'm still interested and would appreciate a clarification
>
> 1. Is there any difference between using a table dynamically named within
> my
> outer sProc and #myTemp?
> 2. Would the process you are suggesting look like a proc that calls a sub
> proc
> and creates w/recompile an inner sProc?
> create proc my_sProc_Main_Outer
> exec mysProc_sub inserting rows to a (temp) table
> then...
> create proc my_sProc_Main_Inner
> with recomplike
> as
> ....
> return
> drop table(s)
> return
> Exec'ing the sProc for the report would look something like....
> exec my_sProc_Main_Outer @.db ,@.w_str ,@.action
> TIA
> JeffP...
>
> "Lee Tudor" <mr_tea@.ntlworld.com> wrote in message
> news:A5aQd.547$Se3.306@.newsfe5-win.ntli.net...
>|||I'm sorry I was so long...
I moved away from #tmp tables as it alone with some earlier report sProcs wa
s
dogging the server and using the dynamic real tables helped.
As far as the location of the sProcs as often as possible we've placed all t
he
sProcs in the Reports db. There are now 4 regional plus another 2 reported
db's
and to keep all the versions updated has been another challenge, so keeping
them
in the reports db has been a benefit to Q&A.
The way I optimize is...
1. Add elements like the new "with (nolock) and see if it doesn't exec faste
r,
it does, in fact my partner was slightly amazed that I was able to run the
sProc/Report for an entire region and market center without locking. We'll
see
in the next few days if it's truely helped.
2. During dev, I run my exec's as select @.str's take the results and run the
m in
the QA with execPlan on.
There isn't often much I can do, we are working in part with an out'a the bo
x
app and adding indexes without pain can be difficult.
I've no real experience with @.table vars and I thought that their scope was
only
within the calling sProc, could the Inner sProc return a @.table ?
Also, with memory a premium, would the @.table var be an improvement over
creating a real table dynamically named? (FYI: I dynamicaly name my tables s
o
that each user who is running a report only see's their stuff)
UDF, I have a few UDF's, a fnProperCase and fnParse which parses any string
by
any separator, anyway I was recently reading about using a UDF to return a t
able
as a parameterized UDF. Anyone have a practical link?
TIA
JeffP....
"Lee Tudor" <mr_tea@.ntlworld.com> wrote in message
news:5LiQd.128$dv2.54@.newsfe5-win.ntli.net...
> @.db? Now you're getting serious :)
> I would try and acheive the desired result without too much dynamic SQL as
> its pretty hard to tune. one way would be to put my_sProc_Main_Outer withi
n
> the master db and then call it in the context of a specific DB using
> SET @.sp=@.db+'..sp_my_sProc_Main_Outer'
> EXEC @.sp @.w_str ,@.action
> if you dont like that then you will have to ditch the sp_ prefixes and put
> the procs on each database
> then your outer sproc would do its stuff (you may be able to simplify this
> as well):
> CREATE TABLE #tmp
> EXEC sp_my_sProc_Main_Inner
> Do something with data in #tmp.
>
> your sproc inner would just add data to the temporary table:
> insert #tmp select col001...coln FROM dbo. ...
> when you return to the inner table, the db will check the stats on the
> temporary table data and recompile it for better execution if necesary (if
> you know that there is no need for dynamic recompilation then you can look
> at some other way of storing the data, e.g. table variable and inner UDF)
> getting rid of those dynamic clauses, if possible, allowing you to view an
d
> check the query plan.
> Mr Tea
> "JDP@.Work" <JPGMTNoSpam@.sbcglobal.net> wrote in message
> news:ezFTA9uEFHA.3200@.TK2MSFTNGP10.phx.gbl...
>
er a
report.
I'm
as to why the following should be locking my contact calendar and
history tables, they just do a select into a table, shouldn't this be read o
nly
and not cause any locks?
Is there a way to be sure that I exec as read only and do not create a table
lock?
Details....
The 1st sProc is called (nested) from within the second as
create proc my_sProc_Main
declare some vars, @.tmpTbl varchar(36)
set @.tmpTbl = replace('-',newID(),'9')
create a real table.... exec(create table table tblWorkingName'+@. tmpTbl)
Insert myTable exec my_sProc_Sub
(my_sProc_Sub - select complex conditions, union another set of complex
conditions, scan 1m row table return 1200 rows)
create another real results table.... exec(create table tblResultsName'+@.
tmpTbl)
Evaluate data in the first table (containing 1200 rows) and insert into the
second table about 20 resulting rows
return the results of the second table
drop both tables
Total process time between 13 and 56 seconds
I run similar processes, some as complex but not using two sProcs, however t
his
one report brings my server to its knees.
The production server is a Compaq dual 700ghz, 1gb Ram
My dev box is a single 1.4ghz, 1gb Ram and it execs somewhat faster and with
half the server killing effects.
TIA
JeffP....use NO LOCK hints on your SELECT statements
OR change isolation level to READ UNCOMMITTED
Greg Jackson
Portland, OR|||SELECT still issues shared resorce locks and INSERT EXEC runs the entire sub
procedure within the INSERT transaction. This combined with the fact you are
selecting from 1M rows is most likely your culprit.
You could override the locking policy on certian tables and tell it to use
page locks or no locks instead. or change the transaction isolation level to
a more lax level.
You may want to consider a different way of storing the intermediate data,
e.g. temp table which can be filled within the sub procedure and then the
calling procedure would get recompiled if necessary for the next step.
This would address the cause rather than the symptom and probably give a
significant performance boost into the bargain.
Mr Tea
"JDP@.Work" <JPGMTNoSpam@.sbcglobal.net> wrote in message
news:%23wEsvHuEFHA.628@.TK2MSFTNGP15.phx.gbl...
> I'm getting LOCKS caused by a particular report that uses two sProcs to
> render a
> report.
> I'm
as to why the following should be locking my contact calendar> and
> history tables, they just do a select into a table, shouldn't this be read
> only
> and not cause any locks?
> Is there a way to be sure that I exec as read only and do not create a
> table
> lock?
> Details....
> The 1st sProc is called (nested) from within the second as
> create proc my_sProc_Main
> declare some vars, @.tmpTbl varchar(36)
> set @.tmpTbl = replace('-',newID(),'9')
> create a real table.... exec(create table table tblWorkingName'+@. tmpTbl)
> Insert myTable exec my_sProc_Sub
> (my_sProc_Sub - select complex conditions, union another set of complex
> conditions, scan 1m row table return 1200 rows)
> create another real results table.... exec(create table tblResultsName'+@.
> tmpTbl)
>
> Evaluate data in the first table (containing 1200 rows) and insert into
> the
> second table about 20 resulting rows
> return the results of the second table
> drop both tables
> Total process time between 13 and 56 seconds
> I run similar processes, some as complex but not using two sProcs, however
> this
> one report brings my server to its knees.
> The production server is a Compaq dual 700ghz, 1gb Ram
> My dev box is a single 1.4ghz, 1gb Ram and it execs somewhat faster and
> with
> half the server killing effects.
> TIA
> JeffP....
>|||Thanks to pdxJaxon, I'm looking into with NOLOCK
if my exec(@.str) from looks like...
from
'+@.db+'.dbo.CONTACT1 contact1
,'+@.db+'.dbo.CONTACT2 contact2
,'+@.db+'.dbo.CAL cal
,'+@.db+'.dbo.conthist ch
,'+@.db+'.dbo.users
where
..adding...
from
'+@.db+'.dbo.CONTACT1 contact1 with NOLOCK
,'+@.db+'.dbo.CONTACT2 contact2 with NOLOCK
,'+@.db+'.dbo.CAL cal with NOLOCK
,'+@.db+'.dbo.conthist ch with NOLOCK
,'+@.db+'.dbo.users with NOLOCK
where
Lee,
I moved away from using the temp db #mytable to creating a table dynamically
named as noted in my pseudo snipit.
I did this because it improved performance from some now in place processes
that
used to bring the server to it's knees.
However I'm still interested and would appreciate a clarification
> e.g. temp table which can be filled within the sub procedure and then the
> calling procedure would get recompiled if necessary for the next step.
1. Is there any difference between using a table dynamically named within my
outer sProc and #myTemp?
2. Would the process you are suggesting look like a proc that calls a sub pr
oc
and creates w/recompile an inner sProc?
create proc my_sProc_Main_Outer
exec mysProc_sub inserting rows to a (temp) table
then...
create proc my_sProc_Main_Inner
with recomplike
as
....
return
drop table(s)
return
Exec'ing the sProc for the report would look something like....
exec my_sProc_Main_Outer @.db ,@.w_str ,@.action
TIA
JeffP...
"Lee Tudor" <mr_tea@.ntlworld.com> wrote in message
news:A5aQd.547$Se3.306@.newsfe5-win.ntli.net...
> SELECT still issues shared resorce locks and INSERT EXEC runs the entire s
ub
> procedure within the INSERT transaction. This combined with the fact you a
re
> selecting from 1M rows is most likely your culprit.
> You could override the locking policy on certian tables and tell it to use
> page locks or no locks instead. or change the transaction isolation level
to
> a more lax level.
> You may want to consider a different way of storing the intermediate data,
> e.g. temp table which can be filled within the sub procedure and then the
> calling procedure would get recompiled if necessary for the next step.
> This would address the cause rather than the symptom and probably give a
> significant performance boost into the bargain.
> Mr Tea
> "JDP@.Work" <JPGMTNoSpam@.sbcglobal.net> wrote in message
> news:%23wEsvHuEFHA.628@.TK2MSFTNGP15.phx.gbl...
>|||Understand that NOLOCK can give you invalid results, since it can see dirty
data. If having consistent results are critical, this is not a good idea.
If you can live with possible spurrious results, based on rows being picked
up that turn out to have been invalid, then it is fine. I don't know about
your architecture, but chances are you will have very litte of this unless
you have quite high throughput, but it is a concern.
A million rows is quite a few, but it is not an incredible amount. Consider
optimizing your query, and you might only have blocks that last a second or
two if any. A query that brings the server to its knees is often a sign
that you have table scans that are slowly walking through your table. It
might also be taking a table lock rather than rowlocks based on resource
utilization. This query could be the problem, but it could also be your
update queries or others. Can you tell us which query is blocking which?
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"JDP@.Work" <JPGMTNoSpam@.sbcglobal.net> wrote in message
news:ezFTA9uEFHA.3200@.TK2MSFTNGP10.phx.gbl...
> Thanks to pdxJaxon, I'm looking into with NOLOCK
> if my exec(@.str) from looks like...
> from
> '+@.db+'.dbo.CONTACT1 contact1
> ,'+@.db+'.dbo.CONTACT2 contact2
> ,'+@.db+'.dbo.CAL cal
> ,'+@.db+'.dbo.conthist ch
> ,'+@.db+'.dbo.users
> where
> ..adding...
> from
> '+@.db+'.dbo.CONTACT1 contact1 with NOLOCK
> ,'+@.db+'.dbo.CONTACT2 contact2 with NOLOCK
> ,'+@.db+'.dbo.CAL cal with NOLOCK
> ,'+@.db+'.dbo.conthist ch with NOLOCK
> ,'+@.db+'.dbo.users with NOLOCK
> where
> Lee,
> I moved away from using the temp db #mytable to creating a table
> dynamically
> named as noted in my pseudo snipit.
> I did this because it improved performance from some now in place
> processes that
> used to bring the server to it's knees.
> However I'm still interested and would appreciate a clarification
>
> 1. Is there any difference between using a table dynamically named within
> my
> outer sProc and #myTemp?
> 2. Would the process you are suggesting look like a proc that calls a sub
> proc
> and creates w/recompile an inner sProc?
> create proc my_sProc_Main_Outer
> exec mysProc_sub inserting rows to a (temp) table
> then...
> create proc my_sProc_Main_Inner
> with recomplike
> as
> ....
> return
> drop table(s)
> return
> Exec'ing the sProc for the report would look something like....
> exec my_sProc_Main_Outer @.db ,@.w_str ,@.action
> TIA
> JeffP...
>
> "Lee Tudor" <mr_tea@.ntlworld.com> wrote in message
> news:A5aQd.547$Se3.306@.newsfe5-win.ntli.net...
>|||When the Crystal (pronounced cryst-hel) report runs we see that all the tabl
es
are blocking other requests.
We have less than 25 users in the system and about another 50+/- running
reports, but another 500 who syncronize their data once a day this occurs ne
arly
all day long, someone is sync'ing with the server at most any time of the da
y.
My sProcs provide data for reports, if they miss an item on a report that th
ey
expect to see their first recourse is to think that the data has not sync'd
with
the server and they will re-sync, running the query after a moment should no
w
include any data that was being updated. Most users only have their own dat
a,
however it's not uncommon for our other daily updating and sales-process-cap
ture
to lock tables as well.
It was more pronounced that when one of the regional VPs would try to run th
e
report for the entire region we'd see that there were blocks and processes w
ould
hang as well as my dynamically named webReprt3984023984023840238402 tables w
ould
still be there due to the proc not completing or the user canceling.
The report takes a little over 2 min to render for an entire market center a
nd
little less than 20 seconds for an individual salesperson.
Another issue is that each time I optimize the sProcs' they add another colu
mn,
some are complex and are dependent on other table info to validate the resul
ts
data, this cross table, cross db stuff is just way too intense for such a lo
w
power system and I hear that we are getting a new production server in the n
ext
month or so.
I also see that workning on a low power system has a side benefit to encoura
ge
us to squeze as much as we can. Another issue is bandwidth which in a word
sucks.
Any I was able to get my outer sProc to run in as little as 4 seconds for a
sales person down from 1min44seconds to 19 seconds and now the 4 seconds.
I know that there must be substantially more optimizations available, howeve
r we
don't have a budget, so we are working for nearly free at this point just sa
ving
face that our stuff works at all. The client knows that they have a margina
l
network, a low power server and very complex reports.
This report for example is a whiteboard, filling in the data in each column
not
respectful of rows except that a particular column in that row is filled, it
loads data just as a human would fill in a sales whiteboard in the office fo
r
each salesperson, with first time visits scheduled, along with columns or %
probablity of closing columns for each pending sale, or targeted prospect.
Thanks to all.
JeffP...
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:eRIEKGxEFHA.3928@.TK2MSFTNGP15.phx.gbl...
> Understand that NOLOCK can give you invalid results, since it can see dirt
y
> data. If having consistent results are critical, this is not a good idea.
> If you can live with possible spurrious results, based on rows being picke
d
> up that turn out to have been invalid, then it is fine. I don't know abou
t
> your architecture, but chances are you will have very litte of this unless
> you have quite high throughput, but it is a concern.
> A million rows is quite a few, but it is not an incredible amount. Consid
er
> optimizing your query, and you might only have blocks that last a second o
r
> two if any. A query that brings the server to its knees is often a sign
> that you have table scans that are slowly walking through your table. It
> might also be taking a table lock rather than rowlocks based on resource
> utilization. This query could be the problem, but it could also be your
> update queries or others. Can you tell us which query is blocking which?
>
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "JDP@.Work" <JPGMTNoSpam@.sbcglobal.net> wrote in message
> news:ezFTA9uEFHA.3200@.TK2MSFTNGP10.phx.gbl...
>|||@.db? Now you're getting serious :)
I would try and acheive the desired result without too much dynamic SQL as
its pretty hard to tune. one way would be to put my_sProc_Main_Outer within
the master db and then call it in the context of a specific DB using
SET @.sp=@.db+'..sp_my_sProc_Main_Outer'
EXEC @.sp @.w_str ,@.action
if you dont like that then you will have to ditch the sp_ prefixes and put
the procs on each database
then your outer sproc would do its stuff (you may be able to simplify this
as well):
CREATE TABLE #tmp
EXEC sp_my_sProc_Main_Inner
Do something with data in #tmp.
your sproc inner would just add data to the temporary table:
insert #tmp select col001...coln FROM dbo. ...
when you return to the inner table, the db will check the stats on the
temporary table data and recompile it for better execution if necesary (if
you know that there is no need for dynamic recompilation then you can look
at some other way of storing the data, e.g. table variable and inner UDF)
getting rid of those dynamic clauses, if possible, allowing you to view and
check the query plan.
Mr Tea
"JDP@.Work" <JPGMTNoSpam@.sbcglobal.net> wrote in message
news:ezFTA9uEFHA.3200@.TK2MSFTNGP10.phx.gbl...
> Thanks to pdxJaxon, I'm looking into with NOLOCK
> if my exec(@.str) from looks like...
> from
> '+@.db+'.dbo.CONTACT1 contact1
> ,'+@.db+'.dbo.CONTACT2 contact2
> ,'+@.db+'.dbo.CAL cal
> ,'+@.db+'.dbo.conthist ch
> ,'+@.db+'.dbo.users
> where
> ..adding...
> from
> '+@.db+'.dbo.CONTACT1 contact1 with NOLOCK
> ,'+@.db+'.dbo.CONTACT2 contact2 with NOLOCK
> ,'+@.db+'.dbo.CAL cal with NOLOCK
> ,'+@.db+'.dbo.conthist ch with NOLOCK
> ,'+@.db+'.dbo.users with NOLOCK
> where
> Lee,
> I moved away from using the temp db #mytable to creating a table
> dynamically
> named as noted in my pseudo snipit.
> I did this because it improved performance from some now in place
> processes that
> used to bring the server to it's knees.
> However I'm still interested and would appreciate a clarification
>
> 1. Is there any difference between using a table dynamically named within
> my
> outer sProc and #myTemp?
> 2. Would the process you are suggesting look like a proc that calls a sub
> proc
> and creates w/recompile an inner sProc?
> create proc my_sProc_Main_Outer
> exec mysProc_sub inserting rows to a (temp) table
> then...
> create proc my_sProc_Main_Inner
> with recomplike
> as
> ....
> return
> drop table(s)
> return
> Exec'ing the sProc for the report would look something like....
> exec my_sProc_Main_Outer @.db ,@.w_str ,@.action
> TIA
> JeffP...
>
> "Lee Tudor" <mr_tea@.ntlworld.com> wrote in message
> news:A5aQd.547$Se3.306@.newsfe5-win.ntli.net...
>|||I'm sorry I was so long...
I moved away from #tmp tables as it alone with some earlier report sProcs wa
s
dogging the server and using the dynamic real tables helped.
As far as the location of the sProcs as often as possible we've placed all t
he
sProcs in the Reports db. There are now 4 regional plus another 2 reported
db's
and to keep all the versions updated has been another challenge, so keeping
them
in the reports db has been a benefit to Q&A.
The way I optimize is...
1. Add elements like the new "with (nolock) and see if it doesn't exec faste
r,
it does, in fact my partner was slightly amazed that I was able to run the
sProc/Report for an entire region and market center without locking. We'll
see
in the next few days if it's truely helped.
2. During dev, I run my exec's as select @.str's take the results and run the
m in
the QA with execPlan on.
There isn't often much I can do, we are working in part with an out'a the bo
x
app and adding indexes without pain can be difficult.
I've no real experience with @.table vars and I thought that their scope was
only
within the calling sProc, could the Inner sProc return a @.table ?
Also, with memory a premium, would the @.table var be an improvement over
creating a real table dynamically named? (FYI: I dynamicaly name my tables s
o
that each user who is running a report only see's their stuff)
UDF, I have a few UDF's, a fnProperCase and fnParse which parses any string
by
any separator, anyway I was recently reading about using a UDF to return a t
able
as a parameterized UDF. Anyone have a practical link?
TIA
JeffP....
"Lee Tudor" <mr_tea@.ntlworld.com> wrote in message
news:5LiQd.128$dv2.54@.newsfe5-win.ntli.net...
> @.db? Now you're getting serious :)
> I would try and acheive the desired result without too much dynamic SQL as
> its pretty hard to tune. one way would be to put my_sProc_Main_Outer withi
n
> the master db and then call it in the context of a specific DB using
> SET @.sp=@.db+'..sp_my_sProc_Main_Outer'
> EXEC @.sp @.w_str ,@.action
> if you dont like that then you will have to ditch the sp_ prefixes and put
> the procs on each database
> then your outer sproc would do its stuff (you may be able to simplify this
> as well):
> CREATE TABLE #tmp
> EXEC sp_my_sProc_Main_Inner
> Do something with data in #tmp.
>
> your sproc inner would just add data to the temporary table:
> insert #tmp select col001...coln FROM dbo. ...
> when you return to the inner table, the db will check the stats on the
> temporary table data and recompile it for better execution if necesary (if
> you know that there is no need for dynamic recompilation then you can look
> at some other way of storing the data, e.g. table variable and inner UDF)
> getting rid of those dynamic clauses, if possible, allowing you to view an
d
> check the query plan.
> Mr Tea
> "JDP@.Work" <JPGMTNoSpam@.sbcglobal.net> wrote in message
> news:ezFTA9uEFHA.3200@.TK2MSFTNGP10.phx.gbl...
>
Locks the tables while running Report...Very Very Urgent....
Hi all ,
When i Run the report in reporting services, it locks the tables.
so is there any option to Unlock the tables. I m using just select query to run the report but when i run the report it locks the tables.
I used with(nolock) option in select query but it didnt work...still showing me lock on the tables.
Pls help...its urgent
Thanking You,
Rupali Rane.
If this is SQL server you could try changing the query to a stored procedure and putting "set transaction isolation level read uncommitted" in the beginning of the procedure.
|||"set transaction isolation level read uncommitted" is the equivelant of specifying (NOLOCK) on all tables.
rupamp- Where are you seeing the locks, what type of locks, and do you have a (NOLOCK) on all tables, even in subqueries/derived tables?
As stated above, if you specify "set transaction isolation level read uncommitted" at the beginning of the query, you can skip the (NOLOCK) in the query.
BobP
Subscribe to:
Posts (Atom)