Database blocking

We have several large reports that access tables with millions of records. In
such cases we expect that blocking may occur; however, the report server will
perform specific commands that lock the database as well. These commands
include UPDATE,DELETE,AWAITING COMMAND. We have a program running to monitor
blocking on our databases so we are able to see when the blocking occurs but
we are unable to find the cause of this problem. The program allows us to see
when a user is causing blocking by running a large report or performing some
other action. The blocking that concerns us is not generated by a user but by
the Report Server itself. It appears that the Report Server performs these
actions after a large report is run or after a report is deployed to the
server.I forgot to ask our question. Does anyone know what process is causing the
blocking and what can we do about it?|||Are you sure this activity you are seeing isn't against its own database.
Report server uses its own database extensively but that should not affect
the database you are reporting against. I go against 1-10 million row tables
all the time, I have an 80 million row table as well. Report server has
never caused a problem with any other application going against the
database. There is no way that Report server is doing an update or delete
against your database. The reports run under the credentials you apply them.
The credentials I use is a readonly account on SQL Server (and Sybase, I
report against both).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Clark Kent" <ClarkKent@.discussions.microsoft.com> wrote in message
news:F583BA12-3E2C-4C65-A9B5-291FDCEAB3DD@.microsoft.com...
> We have several large reports that access tables with millions of records.
> In
> such cases we expect that blocking may occur; however, the report server
> will
> perform specific commands that lock the database as well. These commands
> include UPDATE,DELETE,AWAITING COMMAND. We have a program running to
> monitor
> blocking on our databases so we are able to see when the blocking occurs
> but
> we are unable to find the cause of this problem. The program allows us to
> see
> when a user is causing blocking by running a large report or performing
> some
> other action. The blocking that concerns us is not generated by a user but
> by
> the Report Server itself. It appears that the Report Server performs these
> actions after a large report is run or after a report is deployed to the
> server.|||Our production databases are on the same server as the report server so when
the report server causes blocking in the master database we are getting
blocking issues server-wide. Is there a way to fine tune the processes that
are blocking or schedule them to run during down times?
"Bruce L-C [MVP]" wrote:
> Are you sure this activity you are seeing isn't against its own database.
> Report server uses its own database extensively but that should not affect
> the database you are reporting against. I go against 1-10 million row tables
> all the time, I have an 80 million row table as well. Report server has
> never caused a problem with any other application going against the
> database. There is no way that Report server is doing an update or delete
> against your database. The reports run under the credentials you apply them.
> The credentials I use is a readonly account on SQL Server (and Sybase, I
> report against both).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Clark Kent" <ClarkKent@.discussions.microsoft.com> wrote in message
> news:F583BA12-3E2C-4C65-A9B5-291FDCEAB3DD@.microsoft.com...
> > We have several large reports that access tables with millions of records.
> > In
> > such cases we expect that blocking may occur; however, the report server
> > will
> > perform specific commands that lock the database as well. These commands
> > include UPDATE,DELETE,AWAITING COMMAND. We have a program running to
> > monitor
> > blocking on our databases so we are able to see when the blocking occurs
> > but
> > we are unable to find the cause of this problem. The program allows us to
> > see
> > when a user is causing blocking by running a large report or performing
> > some
> > other action. The blocking that concerns us is not generated by a user but
> > by
> > the Report Server itself. It appears that the Report Server performs these
> > actions after a large report is run or after a report is deployed to the
> > server.
>
>|||"Bruce L-C [MVP]" wrote:
> Are you sure this activity you are seeing isn't against its own database.
> Report server uses its own database extensively but that should not affect
> the database you are reporting against. I go against 1-10 million row tables
> all the time, I have an 80 million row table as well. Report server has
> never caused a problem with any other application going against the
> database. There is no way that Report server is doing an update or delete
> against your database. The reports run under the credentials you apply them.
> The credentials I use is a readonly account on SQL Server (and Sybase, I
> report against both).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Clark Kent" <ClarkKent@.discussions.microsoft.com> wrote in message
> news:F583BA12-3E2C-4C65-A9B5-291FDCEAB3DD@.microsoft.com...
> > We have several large reports that access tables with millions of records.
> > In
> > such cases we expect that blocking may occur; however, the report server
> > will
> > perform specific commands that lock the database as well. These commands
> > include UPDATE,DELETE,AWAITING COMMAND. We have a program running to
> > monitor
> > blocking on our databases so we are able to see when the blocking occurs
> > but
> > we are unable to find the cause of this problem. The program allows us to
> > see
> > when a user is causing blocking by running a large report or performing
> > some
> > other action. The blocking that concerns us is not generated by a user but
> > by
> > the Report Server itself. It appears that the Report Server performs these
> > actions after a large report is run or after a report is deployed to the
> > server.
>
>|||Let me rephrase the whole scenario.
All of our production databases are on the same server as our Reporting
Services database. The reporting services database is causing blocking that
we monitor via our app. Blocking occurs at random times during the day that
do not directly corresponding to pulling a report. For example, if I deploy a
new report to the server, I may get a blocking indicator that the reporting
server is AWAITING COMMAND, or UPDATE. I have had blocking issues when
clicking the Print Preview button as well. It is not necessarily the report
data that is causing the blocking as I mentioned earlier, but it is some
process that the Reporting Services database is performing.
Without getting to far into the Reporting Services database structure, can
anyone tell me what processes are being performed by Reporting Services that
could cause this blocking? Does the database create a cache when you deploy a
new report or print preview a report?
Anyone? Anyone? Beuhler?
If no one knows, then we can live with it; however, it would be nice to
eliminate the blocking altogether.
"BootieNH" wrote:
>
> "Bruce L-C [MVP]" wrote:
> > Are you sure this activity you are seeing isn't against its own database.
> > Report server uses its own database extensively but that should not affect
> > the database you are reporting against. I go against 1-10 million row tables
> > all the time, I have an 80 million row table as well. Report server has
> > never caused a problem with any other application going against the
> > database. There is no way that Report server is doing an update or delete
> > against your database. The reports run under the credentials you apply them.
> > The credentials I use is a readonly account on SQL Server (and Sybase, I
> > report against both).
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Clark Kent" <ClarkKent@.discussions.microsoft.com> wrote in message
> > news:F583BA12-3E2C-4C65-A9B5-291FDCEAB3DD@.microsoft.com...
> > > We have several large reports that access tables with millions of records.
> > > In
> > > such cases we expect that blocking may occur; however, the report server
> > > will
> > > perform specific commands that lock the database as well. These commands
> > > include UPDATE,DELETE,AWAITING COMMAND. We have a program running to
> > > monitor
> > > blocking on our databases so we are able to see when the blocking occurs
> > > but
> > > we are unable to find the cause of this problem. The program allows us to
> > > see
> > > when a user is causing blocking by running a large report or performing
> > > some
> > > other action. The blocking that concerns us is not generated by a user but
> > > by
> > > the Report Server itself. It appears that the Report Server performs these
> > > actions after a large report is run or after a report is deployed to the
> > > server.
> >
> >
> >|||Bruce, excuse me for hijacking this thread, but I am currently
troubleshooting a deadlock problem occurring between our application and
ReportServer. I've isolated that our application process, which is invoking
a stored procedure and passing XML to perform either an INSERT or UPDATE, is
deadlocking with a report SELECT query against a view of that database.
Examining the locking detail shows that our UPDATE query is holding an
exclusive intent lock (LCK_M_IX) on the table [inside a transaction] in order
to perform the update, while Report Services is holding a shared lock
(LCK_M_S) on a SELECT of a view. These locks are incompatible, so a deadlock
occurs, and the lower priority transaction is terminated. At least that's my
understanding. I'm wondering how I need to avoid this problem. I'm
surprised I'm not seeing more threads on this topic, I would have expected
more people to be experiencing this. Is it necessary to configure a lock
timeout for SQL Server to avoid this issue? Thanks!
"Bruce L-C [MVP]" wrote:
> Are you sure this activity you are seeing isn't against its own database.
> Report server uses its own database extensively but that should not affect
> the database you are reporting against. I go against 1-10 million row tables
> all the time, I have an 80 million row table as well. Report server has
> never caused a problem with any other application going against the
> database. There is no way that Report server is doing an update or delete
> against your database. The reports run under the credentials you apply them.
> The credentials I use is a readonly account on SQL Server (and Sybase, I
> report against both).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Clark Kent" <ClarkKent@.discussions.microsoft.com> wrote in message
> news:F583BA12-3E2C-4C65-A9B5-291FDCEAB3DD@.microsoft.com...
> > We have several large reports that access tables with millions of records.
> > In
> > such cases we expect that blocking may occur; however, the report server
> > will
> > perform specific commands that lock the database as well. These commands
> > include UPDATE,DELETE,AWAITING COMMAND. We have a program running to
> > monitor
> > blocking on our databases so we are able to see when the blocking occurs
> > but
> > we are unable to find the cause of this problem. The program allows us to
> > see
> > when a user is causing blocking by running a large report or performing
> > some
> > other action. The blocking that concerns us is not generated by a user but
> > by
> > the Report Server itself. It appears that the Report Server performs these
> > actions after a large report is run or after a report is deployed to the
> > server.
>
>|||Clark, I've spent the last 3 days muddling through extensive locking detail
trying to debug my issue. I think Bruce was implying that Report Server is
doing tons of stuff in its own database, and that shouldn't conflict with
your database. And of course your reports would only be running SELECT
queries. But I'm currently debugging locking issues with exactly this
scenario. Have you looked in detail at the SPIDs of the blocking? Is it
causing behavior problems? I'm trying to figure this out too, and it's not
fun.
"Clark Kent" wrote:
> Let me rephrase the whole scenario.
> All of our production databases are on the same server as our Reporting
> Services database. The reporting services database is causing blocking that
> we monitor via our app. Blocking occurs at random times during the day that
> do not directly corresponding to pulling a report. For example, if I deploy a
> new report to the server, I may get a blocking indicator that the reporting
> server is AWAITING COMMAND, or UPDATE. I have had blocking issues when
> clicking the Print Preview button as well. It is not necessarily the report
> data that is causing the blocking as I mentioned earlier, but it is some
> process that the Reporting Services database is performing.
> Without getting to far into the Reporting Services database structure, can
> anyone tell me what processes are being performed by Reporting Services that
> could cause this blocking? Does the database create a cache when you deploy a
> new report or print preview a report?
> Anyone? Anyone? Beuhler?
> If no one knows, then we can live with it; however, it would be nice to
> eliminate the blocking altogether.
> "BootieNH" wrote:
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > Are you sure this activity you are seeing isn't against its own database.
> > > Report server uses its own database extensively but that should not affect
> > > the database you are reporting against. I go against 1-10 million row tables
> > > all the time, I have an 80 million row table as well. Report server has
> > > never caused a problem with any other application going against the
> > > database. There is no way that Report server is doing an update or delete
> > > against your database. The reports run under the credentials you apply them.
> > > The credentials I use is a readonly account on SQL Server (and Sybase, I
> > > report against both).
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "Clark Kent" <ClarkKent@.discussions.microsoft.com> wrote in message
> > > news:F583BA12-3E2C-4C65-A9B5-291FDCEAB3DD@.microsoft.com...
> > > > We have several large reports that access tables with millions of records.
> > > > In
> > > > such cases we expect that blocking may occur; however, the report server
> > > > will
> > > > perform specific commands that lock the database as well. These commands
> > > > include UPDATE,DELETE,AWAITING COMMAND. We have a program running to
> > > > monitor
> > > > blocking on our databases so we are able to see when the blocking occurs
> > > > but
> > > > we are unable to find the cause of this problem. The program allows us to
> > > > see
> > > > when a user is causing blocking by running a large report or performing
> > > > some
> > > > other action. The blocking that concerns us is not generated by a user but
> > > > by
> > > > the Report Server itself. It appears that the Report Server performs these
> > > > actions after a large report is run or after a report is deployed to the
> > > > server.
> > >
> > >
> > >|||I was hoping that I could avoid getting into the spid detail, but I will look
into it further. As for interference, I can't recall specific instances of
non-reporting service transactions being blocked, but I am pretty sure it has
happened. I will have to pay closer attention to it to make sure. I was
hoping to find a quick answer to all this, but it doesn't look like that will
happen soon. It appears that reporting services is blocking itself the
majority of the time. I will let you know if I find out anything useful.
"BootieNH" wrote:
> Clark, I've spent the last 3 days muddling through extensive locking detail
> trying to debug my issue. I think Bruce was implying that Report Server is
> doing tons of stuff in its own database, and that shouldn't conflict with
> your database. And of course your reports would only be running SELECT
> queries. But I'm currently debugging locking issues with exactly this
> scenario. Have you looked in detail at the SPIDs of the blocking? Is it
> causing behavior problems? I'm trying to figure this out too, and it's not
> fun.
> "Clark Kent" wrote:
> > Let me rephrase the whole scenario.
> >
> > All of our production databases are on the same server as our Reporting
> > Services database. The reporting services database is causing blocking that
> > we monitor via our app. Blocking occurs at random times during the day that
> > do not directly corresponding to pulling a report. For example, if I deploy a
> > new report to the server, I may get a blocking indicator that the reporting
> > server is AWAITING COMMAND, or UPDATE. I have had blocking issues when
> > clicking the Print Preview button as well. It is not necessarily the report
> > data that is causing the blocking as I mentioned earlier, but it is some
> > process that the Reporting Services database is performing.
> >
> > Without getting to far into the Reporting Services database structure, can
> > anyone tell me what processes are being performed by Reporting Services that
> > could cause this blocking? Does the database create a cache when you deploy a
> > new report or print preview a report?
> >
> > Anyone? Anyone? Beuhler?
> >
> > If no one knows, then we can live with it; however, it would be nice to
> > eliminate the blocking altogether.
> >
> > "BootieNH" wrote:
> >
> > >
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > > > Are you sure this activity you are seeing isn't against its own database.
> > > > Report server uses its own database extensively but that should not affect
> > > > the database you are reporting against. I go against 1-10 million row tables
> > > > all the time, I have an 80 million row table as well. Report server has
> > > > never caused a problem with any other application going against the
> > > > database. There is no way that Report server is doing an update or delete
> > > > against your database. The reports run under the credentials you apply them.
> > > > The credentials I use is a readonly account on SQL Server (and Sybase, I
> > > > report against both).
> > > >
> > > >
> > > > --
> > > > Bruce Loehle-Conger
> > > > MVP SQL Server Reporting Services
> > > >
> > > > "Clark Kent" <ClarkKent@.discussions.microsoft.com> wrote in message
> > > > news:F583BA12-3E2C-4C65-A9B5-291FDCEAB3DD@.microsoft.com...
> > > > > We have several large reports that access tables with millions of records.
> > > > > In
> > > > > such cases we expect that blocking may occur; however, the report server
> > > > > will
> > > > > perform specific commands that lock the database as well. These commands
> > > > > include UPDATE,DELETE,AWAITING COMMAND. We have a program running to
> > > > > monitor
> > > > > blocking on our databases so we are able to see when the blocking occurs
> > > > > but
> > > > > we are unable to find the cause of this problem. The program allows us to
> > > > > see
> > > > > when a user is causing blocking by running a large report or performing
> > > > > some
> > > > > other action. The blocking that concerns us is not generated by a user but
> > > > > by
> > > > > the Report Server itself. It appears that the Report Server performs these
> > > > > actions after a large report is run or after a report is deployed to the
> > > > > server.
> > > >
> > > >
> > > >|||This is really not a RS issue but a generic database question. So I posted
your question on the private SQL Server MVP newsgroup and got this answer
back for you:
>>>>>>>>>
Don't use lock timeouts or you will end up with broken transactions. I
suggest using the NOLOCK hint on the select query. You may end up with
imperfect data, but this may not be critical depending on your application
and report. For example, if a weekly call center activity report is off by
one or two calls, it really doesn't matter.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
>>>>>>>>>>
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"BootieNH" <BootieNH@.discussions.microsoft.com> wrote in message
news:9B48D882-7479-4F90-BC8A-5D7308232496@.microsoft.com...
> Bruce, excuse me for hijacking this thread, but I am currently
> troubleshooting a deadlock problem occurring between our application and
> ReportServer. I've isolated that our application process, which is
> invoking
> a stored procedure and passing XML to perform either an INSERT or UPDATE,
> is
> deadlocking with a report SELECT query against a view of that database.
> Examining the locking detail shows that our UPDATE query is holding an
> exclusive intent lock (LCK_M_IX) on the table [inside a transaction] in
> order
> to perform the update, while Report Services is holding a shared lock
> (LCK_M_S) on a SELECT of a view. These locks are incompatible, so a
> deadlock
> occurs, and the lower priority transaction is terminated. At least that's
> my
> understanding. I'm wondering how I need to avoid this problem. I'm
> surprised I'm not seeing more threads on this topic, I would have expected
> more people to be experiencing this. Is it necessary to configure a lock
> timeout for SQL Server to avoid this issue? Thanks!
> "Bruce L-C [MVP]" wrote:
>> Are you sure this activity you are seeing isn't against its own database.
>> Report server uses its own database extensively but that should not
>> affect
>> the database you are reporting against. I go against 1-10 million row
>> tables
>> all the time, I have an 80 million row table as well. Report server has
>> never caused a problem with any other application going against the
>> database. There is no way that Report server is doing an update or delete
>> against your database. The reports run under the credentials you apply
>> them.
>> The credentials I use is a readonly account on SQL Server (and Sybase, I
>> report against both).
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Clark Kent" <ClarkKent@.discussions.microsoft.com> wrote in message
>> news:F583BA12-3E2C-4C65-A9B5-291FDCEAB3DD@.microsoft.com...
>> > We have several large reports that access tables with millions of
>> > records.
>> > In
>> > such cases we expect that blocking may occur; however, the report
>> > server
>> > will
>> > perform specific commands that lock the database as well. These
>> > commands
>> > include UPDATE,DELETE,AWAITING COMMAND. We have a program running to
>> > monitor
>> > blocking on our databases so we are able to see when the blocking
>> > occurs
>> > but
>> > we are unable to find the cause of this problem. The program allows us
>> > to
>> > see
>> > when a user is causing blocking by running a large report or performing
>> > some
>> > other action. The blocking that concerns us is not generated by a user
>> > but
>> > by
>> > the Report Server itself. It appears that the Report Server performs
>> > these
>> > actions after a large report is run or after a report is deployed to
>> > the
>> > server.
>>sql

Database and Report Server in Different Instances?

I have a database in the SQL 2005 default instance and a report server in a named instance. The database has a symmetric key for decrypting one of the columns. When the report server queries the database I get this error: "Cannot find the symmetric key 'key', because it does not exist or you do not have permission."

I know the symmtric key is there because it works when queried from the same instance. Should the database and report server be in the same instance? Or is there a way to change the permissions so that the report server can access the symmetric key in the other instance?

I might note that the report server is able to access unencrypted data in the other instance. The error only occurs when I try to decrypt data using the symmetric key.

I might follow up with this information. I believe that I need to grant permission on the symmetric key. However, I don't know the syntax for granting permission for the report server to access the key. It should be something like this:

grant references on symmetric key :: PasswordKey touser

Who is the user if the report server is in an instance named sqlexpress?

|||

The problem was with the user who was logging into the symmetric key. I used a SQLExpress profiler found athttp://sqlprofiler.googlepages.com/ to find out who was logging in when the report ran. Then I granted permissions to the symmetric key like this: grant references on symmetric key::PasswordKey touser

Database analysis

Hi,
Is there a tool to give me a report on all tables contained within a
microsoft DB. Looking to see total rows in each table, total size of
table.
Many Thanks
GarethOn Sep 18, 1:52 pm, payneless <gpayne_one...@.hotmail.com> wrote:
> Hi,
> Is there a tool to give me a report on all tables contained within a
> microsoft DB. Looking to see total rows in each table, total size of
> table.
> Many Thanks
> Gareth
you can try the below query
DECLARE @.Table varchar(255)
DECLARE @.NumberOfRows int
SET @.Table = 'titles'
SET @.NumberOfRows = 1
CREATE TABLE #t (name varchar(255),rows int, reserved
varchar(100),data varchar(100),index_size varchar(100), unused
varchar(100))
INSERT INTO #t
EXEC sp_MSforeachtable @.command1="sp_spaceused '?'"
SELECT * FROM #t
DROP TABLE #t
Thanks
Vijayasekar|||If you are in a SQL Server 2005 Environment and SSMS is installed then go to
a database and right click on it. Choose Reports from the popup menu and
select "Disk Usage by Table" or try other choices for alternative results.
--
Ekrem Önsoy
"payneless" <gpayne_onetel@.hotmail.com> wrote in message
news:1190105577.773630.209660@.50g2000hsm.googlegroups.com...
> Hi,
> Is there a tool to give me a report on all tables contained within a
> microsoft DB. Looking to see total rows in each table, total size of
> table.
> Many Thanks
> Gareth
>|||If you are in SQL Server install SQL Server 2005 Performance Dashboard
Reports
http://www.microsoft.com/downloads/details.aspx?FamilyID=1d3a4a0d-7e0c-4730-8204-e419218c1efc&DisplayLang=en
"payneless" <gpayne_onetel@.hotmail.com> wrote in message
news:1190105577.773630.209660@.50g2000hsm.googlegroups.com...
> Hi,
> Is there a tool to give me a report on all tables contained within a
> microsoft DB. Looking to see total rows in each table, total size of
> table.
> Many Thanks
> Gareth
>

database access with custom assembly

Hi,
I got a problem accessing the database from within the deployed report.
The report works perfectly well within my IDE (VS 2005 v8.0.5x) but when I
deploy it to the Report Server (SQL Server 2005, v 9.00.1399) no access to
the database can be established; the field containg the value shows '#ERROR'.
I adjusted the following things:
1) C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
Services\ReportServer\rssrvpolicy.config
<CodeGroup class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust"
Name="RSHelperCodeGroup"
Description="Code group for InfoFrame RS help functions">
<IMembershipCondition class="UrlMembershipCondition"
version="1"
Url="C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
Services\ReportServer\bin\RSHelpFunction.dll" />
</CodeGroup>
This entry follows the following entry:
<CodeGroup
class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust">
<IMembershipCondition
class="UrlMembershipCondition"
version="1"
Url="$CodeGen$/*" />
</CodeGroup>
2) function within dll:
public static string getData (string id)
{
private static string connstring = "Data Source=localhost;Network
Library=DBMSSOCN;Initial Catalog=TPP;User ID=userID;Password=password;";
SqlConnection connection = new SqlConnection(connstring);
string resultString = "";
try
{
connection.Open();
string select = "SELECT Text FROM tblXY where tblXY.ID = " + id;
SqlCommand comm = new SqlCommand(select, connection);
SqlDataReader reader = comm.ExecuteReader();
bool r = reader.Read();
//...
resultString = (string)reader.GetSqlString(0);
//some checks following
...
}
finally
{
if (connection != null)
connection.Close();
}
return resultString;
}
3) Assembly
I compiled the assembly (dll) with the following attribute:
[assembly: AllowPartiallyTrustedCallers]
4) Path for the dll
I copied the dll into the following folders:
C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
Services\ReportServer\bin\RSHelpFunction.dll
and
C:\Program Files\Microsoft Visual Studio
8\Common7\IDE\PrivateAssemblies\RSHelpFunction.dll
It seems to me that this is a problem concerning the permission required for
executing the report on the server.
Any help is appreciated.
Thanks in advance.
HaraldHi,
the solution to my problem is, as I suspected, a security point.
I had to give special rights to the database access before opening the
connection to the database within the dll:
try {
SqlClientPermission perm = new
SqlClientPermission(System.Security.Permissions.PermissionState.Unrestricted);
perm.Assert();
connection.Open();
...
} ...
The rest of my coding and adjustments (i.e. CodeGroup,...) were ok.
Then everthing works fine even for the deployed report.
Regards
Harald
"Harald" wrote:
> Hi,
> I got a problem accessing the database from within the deployed report.
> The report works perfectly well within my IDE (VS 2005 v8.0.5x) but when I
> deploy it to the Report Server (SQL Server 2005, v 9.00.1399) no access to
> the database can be established; the field containg the value shows '#ERROR'.
> I adjusted the following things:
> 1) C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
> Services\ReportServer\rssrvpolicy.config
> <CodeGroup class="UnionCodeGroup"
> version="1"
> PermissionSetName="FullTrust"
> Name="RSHelperCodeGroup"
> Description="Code group for InfoFrame RS help functions">
> <IMembershipCondition class="UrlMembershipCondition"
> version="1"
> Url="C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
> Services\ReportServer\bin\RSHelpFunction.dll" />
> </CodeGroup>
> This entry follows the following entry:
> <CodeGroup
> class="UnionCodeGroup"
> version="1"
> PermissionSetName="FullTrust">
> <IMembershipCondition
> class="UrlMembershipCondition"
> version="1"
> Url="$CodeGen$/*" />
> </CodeGroup>
> 2) function within dll:
> public static string getData (string id)
> {
> private static string connstring = "Data Source=localhost;Network
> Library=DBMSSOCN;Initial Catalog=TPP;User ID=userID;Password=password;";
> SqlConnection connection = new SqlConnection(connstring);
> string resultString = "";
> try
> {
> connection.Open();
> string select = "SELECT Text FROM tblXY where tblXY.ID = " + id;
> SqlCommand comm = new SqlCommand(select, connection);
> SqlDataReader reader = comm.ExecuteReader();
> bool r = reader.Read();
> //...
> resultString = (string)reader.GetSqlString(0);
> //some checks following
> ...
> }
> finally
> {
> if (connection != null)
> connection.Close();
> }
> return resultString;
> }
> 3) Assembly
> I compiled the assembly (dll) with the following attribute:
> [assembly: AllowPartiallyTrustedCallers]
> 4) Path for the dll
> I copied the dll into the following folders:
> C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
> Services\ReportServer\bin\RSHelpFunction.dll
> and
> C:\Program Files\Microsoft Visual Studio
> 8\Common7\IDE\PrivateAssemblies\RSHelpFunction.dll
> It seems to me that this is a problem concerning the permission required for
> executing the report on the server.
> Any help is appreciated.
> Thanks in advance.
> Harald

Database access log

SQL 2K
We developed a VB.NET application (SQL 2k db) where users can log into the
system and extract reports. We are planning to create a report with user
list who accessed the application in last 30 days.
Can this be achieved by querying directly to the database '
Thanks
JohnHello John,
This will have to be done when your application authenticates. It will need
to create an audit of who logs in and when. This isn't a function of the
database but a function of the way your application is designed.
Aaron Weiker
http://aaronweiker.com/

> SQL 2K
> We developed a VB.NET application (SQL 2k db) where users can log into
> the system and extract reports. We are planning to create a report
> with user list who accessed the application in last 30 days.
> Can this be achieved by querying directly to the database '
> Thanks
> John|||Hi John,
You can get this information, if you would have done one of the following;
(a) Enabled logging in your VB app (the user and datetime)
(b) Logging in the database tables. Again this should have been done with
your application. (the user and datetime)
(c) Enabled Auditing in the database server.
Are you using one super user to connect to SQL Server for all the
application users or are you connecting to SQL Server for every user
accessing your VB application?
In the former case, you will always see one user at the database level.
Thanks
Yogish

Database access log

SQL 2K
We developed a VB.NET application (SQL 2k db) where users can log into the
system and extract reports. We are planning to create a report with user
list who accessed the application in last 30 days.
Can this be achieved by querying directly to the database '
Thanks
JohnHi John,
You can get this information, if you would have done one of the following;
(a) Enabled logging in your VB app (the user and datetime)
(b) Logging in the database tables. Again this should have been done with
your application. (the user and datetime)
(c) Enabled Auditing in the database server.
Are you using one super user to connect to SQL Server for all the
application users or are you connecting to SQL Server for every user
accessing your VB application?
In the former case, you will always see one user at the database level.
Thanks
Yogish

Database access log

SQL 2K
We developed a VB.NET application (SQL 2k db) where users can log into the
system and extract reports. We are planning to create a report with user
list who accessed the application in last 30 days.
Can this be achieved by querying directly to the database ?
Thanks
John
Hi John,
You can get this information, if you would have done one of the following;
(a) Enabled logging in your VB app (the user and datetime)
(b) Logging in the database tables. Again this should have been done with
your application. (the user and datetime)
(c) Enabled Auditing in the database server.
Are you using one super user to connect to SQL Server for all the
application users or are you connecting to SQL Server for every user
accessing your VB application?
In the former case, you will always see one user at the database level.
Thanks
Yogish

Database access log

SQL 2K
We developed a VB.NET application (SQL 2k db) where users can log into the
system and extract reports. We are planning to create a report with user
list who accessed the application in last 30 days.
Can this be achieved by querying directly to the database '
Thanks
JohnHi John,
You can get this information, if you would have done one of the following;
(a) Enabled logging in your VB app (the user and datetime)
(b) Logging in the database tables. Again this should have been done with
your application. (the user and datetime)
(c) Enabled Auditing in the database server.
Are you using one super user to connect to SQL Server for all the
application users or are you connecting to SQL Server for every user
accessing your VB application?
In the former case, you will always see one user at the database level.
Thanks
Yogish

Database access in custom code

Hi, I'm trying to access a db from the custom code, but it doesn't seem
to be possible, because if i try to preview the report i get errors:
[BC30002] 'DAO.Recordset' type not defined, and some other similar
errors if i try to use SqlConnection ect.
Is it a strange behavior of my reporting services installation?
If not how can I access db from my custom code?I can do it fine so it must be something in your code.
A tip would be to try the customer code out by writing a simple windows
application that uses the code. If this works then the report server should
also work (unless you have security problems).
If you are writing the code in the Code properties of the report then be
sure to add the appropriate references in the references tab.
Craig
"Tur" <cenci.cristiano@.gmail.com> wrote in message
news:1141376485.288962.263920@.v46g2000cwv.googlegroups.com...
> Hi, I'm trying to access a db from the custom code, but it doesn't seem
> to be possible, because if i try to preview the report i get errors:
> [BC30002] 'DAO.Recordset' type not defined, and some other similar
> errors if i try to use SqlConnection ect.
> Is it a strange behavior of my reporting services installation?
> If not how can I access db from my custom code?
>|||Hi Craig, thank you so much for your answer!
I'm writing my custom code in the Code properties of the report, as you
say,
but I'm not sure how to add the appropriate references in reference
tab.
An example:
function MyFun(myStr as String) as String
Dim temp as DAO.Recordset
return "Nothing"
end function
raises an error: "d:\myPath\myRDL.rdl : [BC30002] 'DAO.Recordset' type
not defined."
I'm not sure of what is the problem...|||Hi Craig, thank you so much for your answer!
I'm writing my custom code in the Code properties of the report, as you
say,
but I'm not sure how to add the appropriate references in reference
tab.
An example:
function MyFun(myStr as String) as String
Dim temp as DAO.Recordset
return "Nothing"
end function
raises an error: "d:\myPath\myRDL.rdl : [BC30002] 'DAO.Recordset' type
not defined."
I'm not sure of what is the problem...|||Hi Craig, thank you so much for your answer!
I'm writing my custom code in the Code properties of the report, as you
say,
but I'm not sure how to add the appropriate references in reference
tab.
An example:
function MyFun(myStr as String) as String
Dim temp as DAO.Recordset
return "Nothing"
end function
raises an error: "d:\myPath\myRDL.rdl : [BC30002] 'DAO.Recordset' type
not defined."
I'm not sure of what is the problem...|||Firstly I urge you to try out your custom code from outside of Reporting
Services. This will give you 100 times better error messages plus you will
be able to debug your code which you can't do if you are writing the code in
the reports code.
After you have completed this and its all working fine then, and only then,
copy the code from the function in to the Code properties of the report.
Now the error you are currently getting is to do with a reference that the
report server is not aware of. To add this reference go to the
Report>Report Properties...>References and click on the "..." to add a new
reference.
Choose the DLL that contains the namespace for the DAO.Recordset and add it
to your report.
Craig
"Tur" <cenci.cristiano@.gmail.com> wrote in message
news:1141722816.893159.67500@.j33g2000cwa.googlegroups.com...
> Hi Craig, thank you so much for your answer!
> I'm writing my custom code in the Code properties of the report, as you
> say,
> but I'm not sure how to add the appropriate references in reference
> tab.
>
> An example:
> function MyFun(myStr as String) as String
> Dim temp as DAO.Recordset
> return "Nothing"
> end function
>
> raises an error: "d:\myPath\myRDL.rdl : [BC30002] 'DAO.Recordset' type
> not defined."
>
> I'm not sure of what is the problem...
>

Database Access from Assembly

I have an assembly that I call to supply parameters to a report. Within the assembly, I make a connection to SQL server to supply the parameter.
The report runs fine from my local machine, but when I deploy to the report server, I get nothing back.
Is this a permission issue on the DLL, and if so, how do I get around it?
Thanks!
Dave
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.Did you read this article and apply proper settings in the .config files of
report server?
http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/dngrfCodeAccessSecurityInSQLServer2000ReportingServices.asp
You will also need to assert security permissions inside the custom
assembly. Every call that requires certain permissions (e.g. opening a
database connection) will fail on the server unless the required security
permissions for that call (documented in MSDN) have been asserted.
Example for opening a connection to a SQL Server:
...
SqlClientPermission permission = new
SqlClientPermission(PermissionState.Unrestricted);
try
{
permission.Assert(); // Assert security permission!
SqlConnection con = new SqlConnection("...");
con.Open();
...
}
You might also want to check out these links:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_rdl_0so6.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconAssert.asp
http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_rdl_8wyq.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:%23C2jfaQXEHA.4000@.TK2MSFTNGP09.phx.gbl...
> I have an assembly that I call to supply parameters to a report. Within
the assembly, I make a connection to SQL server to supply the parameter.
> The report runs fine from my local machine, but when I deploy to the
report server, I get nothing back.
> Is this a permission issue on the DLL, and if so, how do I get around it?
> Thanks!
> Dave
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.

Data will not show on report preview

I have a proc that combines the results from 2 queries. I can see all of the
data in QA and the Data tab of SRS there are no issues with that part. When
I preview the report only the second part shows up, nothing from the first
query. I have started over a few times, still the same problem. Any ideas'
ThanksMake sure your SP returns only one resultset. RS does not handle multiple
resultsets. I.e. you should only have one select statement. If you have
multiple it will only use the first one.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"darwin" <darwin@.discussions.microsoft.com> wrote in message
news:4AE2623F-3AC3-456C-BC85-72B228C0C1FD@.microsoft.com...
> I have a proc that combines the results from 2 queries. I can see all of
the
> data in QA and the Data tab of SRS there are no issues with that part.
When
> I preview the report only the second part shows up, nothing from the first
> query. I have started over a few times, still the same problem. Any
ideas'
> Thanks|||There is only one select statement, it joins 2 temp tables. The record set
looks fine in SRS Data tab and in QA, just not in the actual report.
"Bruce L-C [MVP]" wrote:
> Make sure your SP returns only one resultset. RS does not handle multiple
> resultsets. I.e. you should only have one select statement. If you have
> multiple it will only use the first one.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "darwin" <darwin@.discussions.microsoft.com> wrote in message
> news:4AE2623F-3AC3-456C-BC85-72B228C0C1FD@.microsoft.com...
> > I have a proc that combines the results from 2 queries. I can see all of
> the
> > data in QA and the Data tab of SRS there are no issues with that part.
> When
> > I preview the report only the second part shows up, nothing from the first
> > query. I have started over a few times, still the same problem. Any
> ideas'
> > Thanks
>
>|||Try it using the generic query designer in the data tab. The code used there
is exactly what is used during report preview time.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"darwin" <darwin@.discussions.microsoft.com> wrote in message
news:913433BC-A552-4559-8CD8-7FC508719402@.microsoft.com...
> There is only one select statement, it joins 2 temp tables. The record set
> looks fine in SRS Data tab and in QA, just not in the actual report.
> "Bruce L-C [MVP]" wrote:
>> Make sure your SP returns only one resultset. RS does not handle multiple
>> resultsets. I.e. you should only have one select statement. If you have
>> multiple it will only use the first one.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "darwin" <darwin@.discussions.microsoft.com> wrote in message
>> news:4AE2623F-3AC3-456C-BC85-72B228C0C1FD@.microsoft.com...
>> > I have a proc that combines the results from 2 queries. I can see all
>> > of
>> the
>> > data in QA and the Data tab of SRS there are no issues with that part.
>> When
>> > I preview the report only the second part shows up, nothing from the
>> > first
>> > query. I have started over a few times, still the same problem. Any
>> ideas'
>> > Thanks
>>|||This should work. One thing to make sure of with your SP is that you do not
explicitly drop your temp tables. Just let them fall out of scope. Also,
have your select be the last statement.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"darwin" <darwin@.discussions.microsoft.com> wrote in message
news:913433BC-A552-4559-8CD8-7FC508719402@.microsoft.com...
> There is only one select statement, it joins 2 temp tables. The record set
> looks fine in SRS Data tab and in QA, just not in the actual report.
> "Bruce L-C [MVP]" wrote:
>> Make sure your SP returns only one resultset. RS does not handle multiple
>> resultsets. I.e. you should only have one select statement. If you have
>> multiple it will only use the first one.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "darwin" <darwin@.discussions.microsoft.com> wrote in message
>> news:4AE2623F-3AC3-456C-BC85-72B228C0C1FD@.microsoft.com...
>> > I have a proc that combines the results from 2 queries. I can see all
>> > of
>> the
>> > data in QA and the Data tab of SRS there are no issues with that part.
>> When
>> > I preview the report only the second part shows up, nothing from the
>> > first
>> > query. I have started over a few times, still the same problem. Any
>> ideas'
>> > Thanks
>>|||I started all over again, this time I created the parameter dataset first
then added the call to the stored proc. This worked, no clue as to why.. as
to the temp tables I have always droped them after the last select with no
issues.
thanks all ;)
"Bruce L-C [MVP]" wrote:
> This should work. One thing to make sure of with your SP is that you do not
> explicitly drop your temp tables. Just let them fall out of scope. Also,
> have your select be the last statement.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "darwin" <darwin@.discussions.microsoft.com> wrote in message
> news:913433BC-A552-4559-8CD8-7FC508719402@.microsoft.com...
> > There is only one select statement, it joins 2 temp tables. The record set
> > looks fine in SRS Data tab and in QA, just not in the actual report.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> Make sure your SP returns only one resultset. RS does not handle multiple
> >> resultsets. I.e. you should only have one select statement. If you have
> >> multiple it will only use the first one.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >>
> >> "darwin" <darwin@.discussions.microsoft.com> wrote in message
> >> news:4AE2623F-3AC3-456C-BC85-72B228C0C1FD@.microsoft.com...
> >> > I have a proc that combines the results from 2 queries. I can see all
> >> > of
> >> the
> >> > data in QA and the Data tab of SRS there are no issues with that part.
> >> When
> >> > I preview the report only the second part shows up, nothing from the
> >> > first
> >> > query. I have started over a few times, still the same problem. Any
> >> ideas'
> >> > Thanks
> >>
> >>
> >>
>
>

Data validation for datetime parameter in SSRS

Hi,

I wanted to know more about validation of SSRS parameters. I have a simple report which has a parameter called startdate of DateTime datatype. The datetime parameter in SSRS takes manual input as well. So, the user can enter any junk value. I want to ensure that the input parameter is in correct format and I want to display an error msg when the format is incorrect. My report has the following VB code for validation:

Public Function Validate( ByVal startdate As String) As Boolean
If IsDate(startdate) = True Then
Return True
Else
Return False
End If
End Function

And my report has a textbox which has the expression property set to;

=Code.Validate(Parameters!startdate.Value)

the textbox on the report has to display if the entered date is valid or not.

But, when i enter an erroneous date, SSRS doesn't render the report and throws a generic error. This happens even before the code written for validating the parameter executes.

Also couldn't find a way to disable the manual input for the datetime parameter. Even that would solve the problem.

Another alternative was to make the startdate parameter as string, but i want the calendar control button to be provided for the user.

The function below checks for the Start and End date ranges .

Function:

Function CheckDateParameters(StartDate as Date, EndDate as Date) as Integer
Dim msg as String
msg = ""
If (StartDate > EndDate) Then
msg="Start Date should not be later than End Date"
End If
If msg <> "" Then
MsgBox(msg, 16, "Report Validation")
Err.Raise(6,Report) 'Raise an overflow
End If
End Function

Steps:

1.) Go the Report Parameters and add a parameter with the datatype is string.

2.) Check the Hidden checkbox and Allow blank value ckeckbox.

3.) From Default Values choose Non-Queried radio button and then press the FX button and paste this code.

=CODE.CheckDateParameters(<parameterStartdate>.Value,<parameterEnddate>.Value)

Then press OK.

Hope this helps......|||

In the sample that you provided, if we enter an improper date as 02/31/2003 [mm/dd/yyyy], then the report execution fails and an error is thrown right away "error occured during the processing of report parameter". I was talking of handling such errors.

I feel that reporting services, initially validates the entered value matches the datatype of the parameter and then proceeds with execution of any VB code and finally renders the report.

When I said validation, I wanted a functionality similar to the client side validation in an asp page, where improper date formats like 12/31/235668 etc can be taken care of.

Data types error!Please help!

I am developing on SQL server and Crystal Report environment as usual. But I realize today during a new report development that there is a field called Accountingdate that reside in my SQL server with data type datetime which is in View1 appears as datatype number when I opened it in Crystal Report. Is there any reason behind these?

Summarized

In SQL Server I can view it as date time(4/24/2006) but in Crystal Report is number(38738) and the data in it i s totally differentwhat is the datatpye in table? Is it Datetime?
format that to have the format you want|||Data type that I want is datetime exactly what I want. But the data type that appeared is int and data in it is also different|||Right click on the field. Go to Format section and format it mm/dd/yyyy or whichever format you want

Data Type - DateTime

After adding four datetime related parameters, the following error is
displayed when attempting to run the report. The first two datetime
parameters causes no challenges until the third datetime parameter is added.
An error occurred during local report processing.
An error has occurred during report processing.
Cannot read next data row for the data set IPC_VISION_BCL.
Conversion failed when converting datetime from character string.Hi,
do the parameters are configured as datetime on the VS'
How do u get your dataset by SP'
usually i configure the date parameters as string and in the sp convert them
to the wright format...
"Terry" wrote:
> After adding four datetime related parameters, the following error is
> displayed when attempting to run the report. The first two datetime
> parameters causes no challenges until the third datetime parameter is added.
> An error occurred during local report processing.
> An error has occurred during report processing.
> Cannot read next data row for the data set IPC_VISION_BCL.
> Conversion failed when converting datetime from character string.
>
>