Feedback after one week of using

Hi all,
Just installed the software on Monday and I already have 2 reports in the
production. This software is so amazing, I did a little demo today at work
and everyone was shocked to see that it took me only 15 minutes to author
one simple report with no line of code! It's very easy to use, I think
coming up with the query is hard. I still think it needs some improvements,
i.e. calendar control (this is a must in any report, I don't believe they
didn't think of it when they released the software!)
I still have some questions though:
1. Since we have SQL and Web servers in the DMZ (they belong to DMZ domain),
we don't want to have users (on separate domain) log on (using DMZ domain
account - no trust allowed) to view and subscribe reports. Is there a way to
set anonymous access to default reports, except for few reports and certain
features that we want to lock out from anonymous users? This helps us a lot
with maintenance and support.
2. Can I connect to 2 or more datasources in the same report?
Thanks in advance for the help.
SQL Server Reporting Services ROCKS!Thanks for using our product.
1. Allowing both anonymous and authenticated access would require to
configure IIS to accept both anonymous and windows integrated
authentication. We found this configuration to be very confusing and asking
customers to avoid it. If you allow both, internet explorer will not know
which one to use. This will cause unstable behavior, sometimes it will try
to go as anonymous and sometimes will ask for user name and password.
One way to solve this problem is to install another report server and point
it to the same database. Then allow only anonymous access on one RS and only
integrated authentication on another. After that you can give rights to
account under which IIS runs anonymous user for some reports. Note that if
you use different boxes for this, you'll have to make this used a domain
user known to both boxes and SQL.
2. Yes, this is possible.
--
Dmitry Vasilevsky, SQL Server Reporting Services Developer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
---
"VNN" <VNN@.hotmail.com> wrote in message
news:enXXJ7lCFHA.1268@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> Just installed the software on Monday and I already have 2 reports in the
> production. This software is so amazing, I did a little demo today at work
> and everyone was shocked to see that it took me only 15 minutes to author
> one simple report with no line of code! It's very easy to use, I think
> coming up with the query is hard. I still think it needs some
improvements,
> i.e. calendar control (this is a must in any report, I don't believe they
> didn't think of it when they released the software!)
> I still have some questions though:
> 1. Since we have SQL and Web servers in the DMZ (they belong to DMZ
domain),
> we don't want to have users (on separate domain) log on (using DMZ domain
> account - no trust allowed) to view and subscribe reports. Is there a way
to
> set anonymous access to default reports, except for few reports and
certain
> features that we want to lock out from anonymous users? This helps us a
lot
> with maintenance and support.
> 2. Can I connect to 2 or more datasources in the same report?
> Thanks in advance for the help.
> SQL Server Reporting Services ROCKS!
>|||Hello Dmitry,
I can echo the comments of VNN. I am into my second week using RS,
apart from initial hiccups of installation, development has been rapid.
I am eagerly awaiting SP2 to see if they can add some features which i
feel can make my life easier.
As you mentioned multiple installations of RS pointing to same DB, I
would like to know more about this.
I am trying to deploy my reports on a remote server. But i am not sure
to use the existing RS DB on a machine in my domain or
Create a new RS db on the server.
Would like to know how to go about
1. Using the existing DB
2. Creating a new DB on the server.
In case of creating a new DB is there a way I can export all the
reports I created on my local machine to the new report server ?
What are the security/account types/auth/IIS issues I need to take care
of.
I am gonna do the installation tommorow and trying to get as much info
as possible.
Thx
Bofo|||When multiple servers are sharing the same SQL DB that is called a web farm
configuration. It is more complicated to setup and it is more expensive (you
have to have an enterprise server license). For what you are doing you
definitely do not need to do this and I would recommend against it (even if
you are already using enterprise). In your case it is best to have
development and production separate. It is very easy to deploy all reports
again. RS has multiple configurations you can deploy to (I have three). You
can deploy all your reports or you can deploy just the ones you need (select
the ones to deploy, right mouse click deploy).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"RemoteDeploy" <bofobofo@.yahoo.com> wrote in message
news:1107497967.753916.202930@.l41g2000cwc.googlegroups.com...
> Hello Dmitry,
> I can echo the comments of VNN. I am into my second week using RS,
> apart from initial hiccups of installation, development has been rapid.
> I am eagerly awaiting SP2 to see if they can add some features which i
> feel can make my life easier.
> As you mentioned multiple installations of RS pointing to same DB, I
> would like to know more about this.
> I am trying to deploy my reports on a remote server. But i am not sure
> to use the existing RS DB on a machine in my domain or
> Create a new RS db on the server.
> Would like to know how to go about
> 1. Using the existing DB
> 2. Creating a new DB on the server.
> In case of creating a new DB is there a way I can export all the
> reports I created on my local machine to the new report server ?
> What are the security/account types/auth/IIS issues I need to take care
> of.
> I am gonna do the installation tommorow and trying to get as much info
> as possible.
>
> Thx
> Bofo
>

Fatal Error Handling

Hello,

I am currently writing a T-SQL template that will be used at client sites to update their databases whenever our software product requires backend changes.

The goal of the template is to wrap up all DDL/DML with error handling, and if an error occurs during the execution of the script at a remote site, SQLMail sends our office an email with the time/cause/site/....

A pseudo version of the template is:

CREATE TABLE #ERROR_STORE AS xxxxxxxxxxxxxxxx

BEGIN TRANSACTION "xxxxxxxxx"

BEGIN TRY

DDL/DML

END TRY

BEGIN CATCH

INSERT INTO #ERROR_STORE (@.@.ERROR, xxxxxxxxxxxxx)

END CATCH

IF RECORDS_EXIST_IN(#ERROR_STORE) BEGIN

ROLLBACK TRANSACTION

SQLMAIL("send me all errors in #ERROR_STORE")

END

ELSE BEGIN

COMMIT TRANSACTION

END

DROP TABLE #ERROR_STORE

--

The problem with this approach is that any fatal errors will kill the execution of the entire query. So anything like "select * from A_TABLE_THAT_DOESNT_EXIST" will leave me helpless

I need a way (is there a way..) to manage/catch/detect a fatal error that occurs when a script of this nature is executed.

Thanks.

The answer to your question is NO, you cannot trap "table does not

exist" by any method other than checking to see if it exists first.

The error handling in SQL 2000 and 2005 is EXTREMELY limited. This is a HUGE failing of MS to fix. The TRY/CATCH in 2005 is a step in the right direction, but it only catches a limitted amount of errors, basically the things that set @.@.ERROR in 2000.

Most SQL errors are TERMINAL and stop the batch from running and you cannot trap them at all. Worse, if you have a parent stored proc calling a child stored proc, and the child fails, lets say for "table does not exist", the child proc TERMINATES on the line that caused the error, and returns to the parent as if nothing happened.|||

Yeah, the error handling in 2005 is more oriented to DML errors than DDL errors. I would look at what RedGate does with their SQL Compare tool as a good idea of how to do things (you can get their tool and look at the output, and use it too, it is a nice tool for building these kinds of differential scripts from version to version.)

Bottom line is that I would consider building a loader program that runs your scripts in an installer-like fashion and probably not just provide scripts for the user to run. Then you have error handling power at the client level.

|||

Oddly enough, my company uses SQL Compare... I was creating a console app that would clean out a few things I didnt like about it and add in a few bits that I needed (e.g. SQL Mail if errors occurred).

Correct me if I am wrong, but they wrap every DDL/DML statement into its own transaction, so some of the script can commit where other parts of the script could fail... I really dont think they would do this but thats what it looked like in the script...

BEGIN TRANSACTION
GO
PRINT N'Creating [dbo].[TimeEntries]'
GO
CREATE TABLE [dbo].[TimeEntries]
(
....
)

GO
IF @.@.ERROR<>0 AND @.@.TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @.@.TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[SetTimeEntry]'
GO

etc. etc.

Also, I am going to be automating all of this eventually (not passing scripts to client site IT people), so having a loader program isnt out of the question. But how would the error detection improve by going in this direction?

Thanks!

|||Yes, that is what they do. And roll it back at the end of each of the batches if there has been an error. This way nothing gets committed, and the next transactions don't get committed either..

Fatal Error Handling

Hello,

I am currently writing a T-SQL template that will be used at client sites to update their databases whenever our software product requires backend changes.

The goal of the template is to wrap up all DDL/DML with error handling, and if an error occurs during the execution of the script at a remote site, SQLMail sends our office an email with the time/cause/site/....

A pseudo version of the template is:

CREATE TABLE #ERROR_STORE AS xxxxxxxxxxxxxxxx

BEGIN TRANSACTION "xxxxxxxxx"

BEGIN TRY

DDL/DML

END TRY

BEGIN CATCH

INSERT INTO #ERROR_STORE (@.@.ERROR, xxxxxxxxxxxxx)

END CATCH

IF RECORDS_EXIST_IN(#ERROR_STORE) BEGIN

ROLLBACK TRANSACTION

SQLMAIL("send me all errors in #ERROR_STORE")

END

ELSE BEGIN

COMMIT TRANSACTION

END

DROP TABLE #ERROR_STORE

--

The problem with this approach is that any fatal errors will kill the execution of the entire query. So anything like "select * from A_TABLE_THAT_DOESNT_EXIST" will leave me helpless

I need a way (is there a way..) to manage/catch/detect a fatal error that occurs when a script of this nature is executed.

Thanks.

The answer to your question is NO, you cannot trap "table does not exist" by any method other than checking to see if it exists first.
The error handling in SQL 2000 and 2005 is EXTREMELY limited. This is a HUGE failing of MS to fix. The TRY/CATCH in 2005 is a step in the right direction, but it only catches a limitted amount of errors, basically the things that set @.@.ERROR in 2000.

Most SQL errors are TERMINAL and stop the batch from running and you cannot trap them at all. Worse, if you have a parent stored proc calling a child stored proc, and the child fails, lets say for "table does not exist", the child proc TERMINATES on the line that caused the error, and returns to the parent as if nothing happened.

|||

Yeah, the error handling in 2005 is more oriented to DML errors than DDL errors. I would look at what RedGate does with their SQL Compare tool as a good idea of how to do things (you can get their tool and look at the output, and use it too, it is a nice tool for building these kinds of differential scripts from version to version.)

Bottom line is that I would consider building a loader program that runs your scripts in an installer-like fashion and probably not just provide scripts for the user to run. Then you have error handling power at the client level.

|||

Oddly enough, my company uses SQL Compare... I was creating a console app that would clean out a few things I didnt like about it and add in a few bits that I needed (e.g. SQL Mail if errors occurred).

Correct me if I am wrong, but they wrap every DDL/DML statement into its own transaction, so some of the script can commit where other parts of the script could fail... I really dont think they would do this but thats what it looked like in the script...

BEGIN TRANSACTION
GO
PRINT N'Creating [dbo].[TimeEntries]'
GO
CREATE TABLE [dbo].[TimeEntries]
(
....
)

GO
IF @.@.ERROR<>0 AND @.@.TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @.@.TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[SetTimeEntry]'
GO

etc. etc.

Also, I am going to be automating all of this eventually (not passing scripts to client site IT people), so having a loader program isnt out of the question. But how would the error detection improve by going in this direction?

Thanks!

|||Yes, that is what they do. And roll it back at the end of each of the batches if there has been an error. This way nothing gets committed, and the next transactions don't get committed either..