best tutorial for sql reporting service 2005

i am asked to develop a web application where i have to display reports in a seperate window from a hyperlink .i have to display the report in grid format and show a pie chart within the report layout .

i need to give user credential like print,save the report in excel or doc format and email report.

can anybody give the links for the best tutorial to achieve my purpose.

thanks

sally

Hi there,

There are many resources available. Try books online.

Here is a great guide to building a report.

http://www.eggheadcafe.com/articles/20040823.asp

Here is a guide to building a report + a web interface.

http://www.15seconds.com/Issue/041013.htm

Try MS downloads for further samples.

cheers,

Andrew

|||

Reporting Services Tutorials >>> http://msdn2.microsoft.com/en-us/library/ms170246.aspx

Best Tool to Create Web Page?

I am looking to extend some customer data from my SQL Server out to the Web.
I am looking for the best utility to help create these pages\sites quickly.
We are running SQL Server 2000 and IIS 5.0
Thanks..Ted,
Refer sp_makewebtask in BooksOnLine.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Ted Appleberry" <ted@.appleberry.com> wrote in message
news:efO3YkNQDHA.2832@.TK2MSFTNGP10.phx.gbl...
> I am looking to extend some customer data from my SQL Server out to the
Web.
> I am looking for the best utility to help create these pages\sites
quickly.
> We are running SQL Server 2000 and IIS 5.0
> Thanks..
>|||You could use free community supported tool Web Matrix.
http://www.asp.net/webmatrix/default.aspx?tabindex=4&tabid=46
Thanks
sethu
"Ted Appleberry" <ted@.appleberry.com> wrote in message
news:OgY8aHOQDHA.1752@.TK2MSFTNGP12.phx.gbl...
> I am looking for a nice drag and drop interface or something. Third
Party..
> Don't mind spending $$$. Don't want to write a bunch of code if I can
avoid
> it.. Yes, I know.. I am a wimp..
> "Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
> news:eN3$erNQDHA.1988@.TK2MSFTNGP12.phx.gbl...
> > Ted,
> >
> > Refer sp_makewebtask in BooksOnLine.
> >
> > --
> > Dinesh.
> > SQL Server FAQ at
> > http://www.tkdinesh.com
> >
> > "Ted Appleberry" <ted@.appleberry.com> wrote in message
> > news:efO3YkNQDHA.2832@.TK2MSFTNGP10.phx.gbl...
> > > I am looking to extend some customer data from my SQL Server out to
the
> > Web.
> > > I am looking for the best utility to help create these pages\sites
> > quickly.
> > > We are running SQL Server 2000 and IIS 5.0
> > > Thanks..
> > >
> > >
> >
> >
>

Best tool for Performance Tuning

Which is the best third party tool for SQL Server 2005 Performance Tuning/Optimization.

We purchased the Toad for SQL Server from Quest sofwater but it works only with dbo schemas....So if ur users were in schema xyz then it would not recognize it because they develop the tool for 2005 based on SQL Server 2000 where the schema ownesrhip is tied to the users.

Have you tried the Database Engine Tuning Advisor in Management Studio? I've not used it a great deal but what I have done seemed quite good. It also doesn't require an extra purchase.

sql

Best tool for OLAP browsing in SQL 2005 ?

Hi all,

Although SQL 2005 is well designed database and SQL 2005 AS is a good BI Platform. I have some bad feelings about the client tools taht supoorts these systems.

We use Proclarity 5.2 and 5.3 versions for browsing 2000 AS cubes.

But if i am not wrong, There Proclairty versions do not connect to SQL AS 2005 services. So I should use Proclartiy Analytics Server or 6.3 versions of Proclarity Desktop Professionals.

Additionaly, although we are fully licensed these Procalirty Desktop Professionals. I could not get any support from our country Partner...in TURKEY..

So I want to learn which desktop clients other AS 2005 user have for browsing 2005 cubes.

Microsoft developed a good platform for BI business as SQL 2005 and also had Proclarity for browsing cubes. But previous versions can not use SQL 2005 AS features.

So what is your opinion our solutions for browsing SQL 2005 cubes by front-office desktop users.

I need feasible any suggestions or solutions for my client users.

Thank you for all, kind responses.

ayhan akgun

MCSD .Net

Try installing AS OLEDB 9 to get your existing ProClarity to connect to Anlaysis Services 2005. You can get it from http://www.microsoft.com/downloads/details.aspx?familyid=50b97994-8453-4998-8226-fa42ec403d17&displaylang=en

Of cource new version of ProClarity is going to have great features to work with new version of Analysis Services but you should definitely try and see if there is lots of things missing with existing ProClarity connecting to AS2005.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi Edward,

Thank you for your quick response. I have downloaded and installed AS OLEDB to my client pc. Unfortunately, i still can not access 2005 analysis services. But if i use version 6.3 Proclarity i can access and browse 2005 cubes.

I get Login failed error while trying to access 2005 cubes by using 5.2.111 version,?

Any suddesions.?

Ayhan

|||

Ayhan,

I would suggest you to look also at Excel 2007. From what I heared Microsoft plans even Proclarity to work on top of Excel in the not so distance future. You will see that Excel 2007 has a lot of great features, and you can also use it with Sharepoint and Excel services.

I also compiled a list of known to me Analysis Services clients here: http://www.ssas-info.com/ssas_products.html

Vidas Matelis

|||

Hi,

It is exactly ok now. Although before installing that package, Proclarity says taht it uses OLEDB 9.0 version. It is exactly wrong.

To be able to connect SQL 2005 AS every Proclarity users must again install new that verison of driver.

Thank you, it is very important point.

Ayhan Akgün|||

Ayhan

ProClarity is now a Microsoft product and so you can get licenses and help direct from them.

Excel 2007 is also really good against SSAS, but is obviously a desktop install.

Finally you can get Reporting services to build reports against the cube but either direclty or by genretaing a report model from the cube (create a connection to the cube in report manager and then from the properties of the connection you can create a model) and then you can use report builder to build ad hoc rpeorts against the cube. This isn't olap of course.

Good Luck

Best to clear users before detaching a database.

What is the best way to clear users before using the stored procedure to
detach a database? I'm thinking about just writting a script to kill their
process before doing the detach command.
Thanks
DonAlter Database FooDB set offline with rollback immediate
go
Alter Database FooDB set online
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Don" <Don@.discussions.microsoft.com> wrote in message
news:70D2D8A6-80A4-4A61-81A7-169AFED7AECB@.microsoft.com...
> What is the best way to clear users before using the stored procedure to
> detach a database? I'm thinking about just writting a script to kill
> their
> process before doing the detach command.
> Thanks
> Don|||Don,
EM provides this option (CLEAR) when you detach a database graphically -
also asks if notificition should passed on to disconnected users.
HTH
Jerry
"Don" <Don@.discussions.microsoft.com> wrote in message
news:70D2D8A6-80A4-4A61-81A7-169AFED7AECB@.microsoft.com...
> What is the best way to clear users before using the stored procedure to
> detach a database? I'm thinking about just writting a script to kill
> their
> process before doing the detach command.
> Thanks
> Don

Best to clear users before detaching a database.

What is the best way to clear users before using the stored procedure to
detach a database? I'm thinking about just writting a script to kill their
process before doing the detach command.
Thanks
DonAlter Database FooDB set offline with rollback immediate
go
Alter Database FooDB set online
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Don" <Don@.discussions.microsoft.com> wrote in message
news:70D2D8A6-80A4-4A61-81A7-169AFED7AECB@.microsoft.com...
> What is the best way to clear users before using the stored procedure to
> detach a database? I'm thinking about just writting a script to kill
> their
> process before doing the detach command.
> Thanks
> Don|||Don,
EM provides this option (CLEAR) when you detach a database graphically -
also asks if notificition should passed on to disconnected users.
HTH
Jerry
"Don" <Don@.discussions.microsoft.com> wrote in message
news:70D2D8A6-80A4-4A61-81A7-169AFED7AECB@.microsoft.com...
> What is the best way to clear users before using the stored procedure to
> detach a database? I'm thinking about just writting a script to kill
> their
> process before doing the detach command.
> Thanks
> Don

Best to clear users before detaching a database.

What is the best way to clear users before using the stored procedure to
detach a database? I'm thinking about just writting a script to kill their
process before doing the detach command.
Thanks
Don
Alter Database FooDB set offline with rollback immediate
go
Alter Database FooDB set online
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Don" <Don@.discussions.microsoft.com> wrote in message
news:70D2D8A6-80A4-4A61-81A7-169AFED7AECB@.microsoft.com...
> What is the best way to clear users before using the stored procedure to
> detach a database? I'm thinking about just writting a script to kill
> their
> process before doing the detach command.
> Thanks
> Don
|||Don,
EM provides this option (CLEAR) when you detach a database graphically -
also asks if notificition should passed on to disconnected users.
HTH
Jerry
"Don" <Don@.discussions.microsoft.com> wrote in message
news:70D2D8A6-80A4-4A61-81A7-169AFED7AECB@.microsoft.com...
> What is the best way to clear users before using the stored procedure to
> detach a database? I'm thinking about just writting a script to kill
> their
> process before doing the detach command.
> Thanks
> Don

Best Technology for Reporting.

Current Environment:
SQL 2000 SP4 EE on Windows 2003 SP1
I need some suggestion on some of the currently available options for
reporting.
We have a transactional database where lots of transactions come throughout
the day. Some of the tables can have over 200 k records added. We need to
aggregate data on another server for reporting purposes and currently all
this is done via DTS jobs which do lots of locking and time consuming.
Since we are looking into re-architecting this solution and go away from
DTS, I am looking for various options that we can set up in test environment
and see which one supercedes over another one. Also, should I look into SQL
2005 or stay with SQL 2000 only.
Please advice.
Thanks in Advance."Mark" <Mark@.discussions.microsoft.com> wrote in message
news:8F1E6BD8-A36D-4D3E-8799-C67DF4F19D9D@.microsoft.com...
> Current Environment:
> SQL 2000 SP4 EE on Windows 2003 SP1
> I need some suggestion on some of the currently available options for
> reporting.
> We have a transactional database where lots of transactions come
> throughout
> the day. Some of the tables can have over 200 k records added. We need to
> aggregate data on another server for reporting purposes and currently all
> this is done via DTS jobs which do lots of locking and time consuming.
> Since we are looking into re-architecting this solution and go away from
> DTS, I am looking for various options that we can set up in test
> environment
> and see which one supercedes over another one. Also, should I look into
> SQL
> 2005 or stay with SQL 2000 only.
> Please advice.
> Thanks in Advance.
If the jobs are doing that much locking, then I would probably look at
rearchitecting the jobs themselves. Take advantage of the WITH (NOLOCK)
hints where appropriate etc.
Having your reporting done on the live data with the same aggregations that
your DTS jobs are doing doesn't seem like a particularly good idea to me.
Your performance will be better on the aggregated data already stored in the
reporting database server.
Just my .02
Rick Sawtell
MCT, MCSD, MCDBAsql

Best techniques for databse creation ?

Dear Experts,
Any one knows any link regarding best practices for schema and database crea
tion in SQL server 2000 in detail .
Faheem Latif
NETWORK SOLUTIONFaheem,
What specifically are you looking for? There are many things on the web if
you search google. However I recommend these links as a start:
SQL Server 2000 Operations Guide
http://www.microsoft.com/technet/pr...in/sqlops0.mspx
Vyas's article on Best Practices
http://www.sql-server-performance.c...t_practices.asp
Microsoft SQL Server 2000 Best Practices Analyzer 1.0 Beta
E07339C1F22&displaylang=en" target="_blank">http://www.microsoft.com/downloads/...&displaylang=en
Tinyurl: http://tinyurl.com/upzi
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Faheem" <anonymous@.discussions.microsoft.com> wrote in message
news:189C9EC5-9492-4B34-A0D7-C9908D15BA0E@.microsoft.com...
> Dear Experts,
> Any one knows any link regarding best practices for schema and database
creation in SQL server 2000 in detail .
> Faheem Latif
> NETWORK SOLUTION
>

Best techniques for databse creation ?

Dear Experts
Any one knows any link regarding best practices for schema and database creation in SQL server 2000 in detail
Faheem Latif
NETWORK SOLUTIONFaheem,
What specifically are you looking for? There are many things on the web if
you search google. However I recommend these links as a start:
SQL Server 2000 Operations Guide
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops0.mspx
Vyas's article on Best Practices
http://www.sql-server-performance.com/vk_sql_best_practices.asp
Microsoft SQL Server 2000 Best Practices Analyzer 1.0 Beta
http://www.microsoft.com/downloads/details.aspx?FamilyId=B352EB1F-D3CA-44EE-893E-9E07339C1F22&displaylang=en
Tinyurl: http://tinyurl.com/upzi
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Faheem" <anonymous@.discussions.microsoft.com> wrote in message
news:189C9EC5-9492-4B34-A0D7-C9908D15BA0E@.microsoft.com...
> Dear Experts,
> Any one knows any link regarding best practices for schema and database
creation in SQL server 2000 in detail .
> Faheem Latif
> NETWORK SOLUTION
>

Best techniques for databse creation ?

Dear Experts,
Any one knows any link regarding best practices for schema and database creation in SQL server 2000 in detail .
Faheem Latif
NETWORK SOLUTION
Faheem,
What specifically are you looking for? There are many things on the web if
you search google. However I recommend these links as a start:
SQL Server 2000 Operations Guide
http://www.microsoft.com/technet/pro...n/sqlops0.mspx
Vyas's article on Best Practices
http://www.sql-server-performance.co..._practices.asp
Microsoft SQL Server 2000 Best Practices Analyzer 1.0 Beta
http://www.microsoft.com/downloads/d...displaylang=en
Tinyurl: http://tinyurl.com/upzi
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Faheem" <anonymous@.discussions.microsoft.com> wrote in message
news:189C9EC5-9492-4B34-A0D7-C9908D15BA0E@.microsoft.com...
> Dear Experts,
> Any one knows any link regarding best practices for schema and database
creation in SQL server 2000 in detail .
> Faheem Latif
> NETWORK SOLUTION
>

Best technique for Replication

thx Christian and Paul fro previous answers!
I have a SQL2000 server that hosts our accounting database and I want to
replicate it to another SQL2000 server so I can write reports etc, and do not
want to use the production server for these tasks. I tried to setup a
"snapshot" and was somewhat successful. My question is would it be better to
do a snapshot or transactional replication? The snapshot seemed to take along
time to run. I also do log backups every 15 minutes.
ps Paul I ordered your book...hope it will help also.
Rick Rushing
System Administrator
SQL Newbie
D & J Construction
Rick,
snapshot is sometimes OK but it'll lock the tables and you can't really do
this regularly, and unless all your data is changing it is overkill.
Transactional would be more appropriate. Log shipping is sometimes used, but
you have to be out of the standby server when the log is restored, which in
most cases rules it out.
Rgds,
Paul Ibison, SQL Server MVP
BTW the only book I've written is my address book - Hilary's the man for
replication books and it's a very useful resource.
|||Paul,
There will be about 20 users on the SQL production server and only myself
using the other SQL server. My goal is to find a way to have a copy of the
database on the production server to the other server for use in writing
reports etc. So you think transaction would be a better approach?
ps you are right..it was Hilary's bok on replication.
Rick Rushing
System Administrator
D & J Construction
"Paul Ibison" wrote:

> Rick,
> snapshot is sometimes OK but it'll lock the tables and you can't really do
> this regularly, and unless all your data is changing it is overkill.
> Transactional would be more appropriate. Log shipping is sometimes used, but
> you have to be out of the standby server when the log is restored, which in
> most cases rules it out.
> Rgds,
> Paul Ibison, SQL Server MVP
> BTW the only book I've written is my address book - Hilary's the man for
> replication books and it's a very useful resource.
>
>
|||Rick - yes this is quite a common approach, and gives you the control to
decide exactly which tables are selected. BTW the Database snapshots in SQL
Server 2005 will offer an interesting alternative.
Rgds,
Paul Ibison
|||Thanks Paul
I will pursue using transactional when my book arrives. We just installed
these SQL servers back in October so it will be a while before we upgrade.
thanks again for the input.
Rick Rushing
System Administrator
D & J Construction
"Paul Ibison" wrote:

> Rick - yes this is quite a common approach, and gives you the control to
> decide exactly which tables are selected. BTW the Database snapshots in SQL
> Server 2005 will offer an interesting alternative.
> Rgds,
> Paul Ibison
>
>

Best strategy? How to filter Report results

Hi,

I’m using SQL Server 2000.

I need some help on what strategy to adopt in order to filter some existing reports that I have written. I’m basically a web developer (ASP.NET) with some ? T-SQL skills (therefore I need advice on which is the best strategy please).

The business system I’m working on has 2 levels of access: Administrators (there can be several) and Users (20,000+).

Users can login and do stuff (do a task).

Administrators can login and give tasks to the Users to do. Administrators can also run reports to see the status of the Users relating to a particular task. A report on task X will show a list of all Users who have been given that same task (example of a task: “attend to a conference”) and next to each User will be the status (e.g. “Completed” or “Not yet completed”).

That is the existing system (above). Now I need to add a new type of person who can login and view the same reports as the administrator. This type of person will be called a “Manager”. The difference is that the Manager will only be able to view information on a defined set of Users. There might be a few hundred “Managers”.

So my “Wizard web page” code that allows an Administrator to create a Manager account will allow the Administrator to select (from a list of Users) which Users this Manager is allowed to see.

This means I’m going to have a new TABLE to relate the Manager to the User. I’ll call this the tblReporterUser table.

This table will have a ReporterID and UserID (read strategy #1 for reason this is not called “ManagerID”).

Here are the strategies I can think of. Which is best or is there one even better?

Strategy #1

As well as filling the tblReporterUser with Manager to User relationships, also fill it will Administrator to User relationships.

This means that when I call the Stored Procedure to select the rows of my report I need to pass in the ReporterID (which will actually be a ManagerID if it’s a Manager running the report, or an AdministratorID if it is an Administrator).

In order to filter Users out of the report I would then use this ReporterID to JOIN against the tblReporterUser TABLE.

Disadvantages: Each time Users are added to the system I have to make sure all administrators get permission to view the new User (add a row to tblReporterUser TABLE for each administrator with the new UserID).

Also it might make reporting for administrators slower?

Strategy #2

Explained with some pseudo-code (I don’t like this because of the “repeated code” as its easy to get into maintenance problems):

If (RoleIsAdministrator) THEN

-- role is Administrator

Existing report code

ELSE

-- role is Manager

Existing report code

JOIN on the tblReporterUser using the ManagerID as the ReporterID

ENDIF

Strategy #3

Is there a way to do a “conditional” JOIN ON the tblReporterUser if the “role” is Manager (but don’t do the JOIN if the role is Administrator).

If there is a way then this sounds like the best solution.

I have no idea how to do a conditional join – could someone give me an example please?

Massive thanks in advance to anyone replying with any amount of help.

Cheers,

Just back from 5 days camping. It rained! :D But in Australia that is a good thing!!!!

I guess this is a *BUMP* for this thread. If anyone has any ideas about "conditional JOINs" please help.

Many thanks,

Chiz

sql

Best Strategy to backup the system db

Hi,
I am trying to approach method to copy my system database using enterprise
tool. I have researched on Database Mainteance tool and Backup database. I
would like to use one of the method to copy my database without any
transcational log since we don't have any transcational going on and not
growing my database too much.
how would i accomplish this?
any comments would be appreciate
Pooja,
You might want to clarify whether or not this is a system database i.e.,
Master, MSDB, etc... or a user-defined database. When you say "my" database
I'm going to assume it is a user-defined database. If you do not require
the transaction logs to be backed up as part of your database recovery
strategy, you can set the recovery mode for the database to SIMPLE (the
t-log will be automatically truncated). Then schedule your full database
backup as a job or use a Maintenance Plan.
HTH
Jerry
"Pooja" <Pooja@.discussions.microsoft.com> wrote in message
news:8F05EF72-186D-4B93-BA20-E81364824F29@.microsoft.com...
> Hi,
> I am trying to approach method to copy my system database using enterprise
> tool. I have researched on Database Mainteance tool and Backup database. I
> would like to use one of the method to copy my database without any
> transcational log since we don't have any transcational going on and not
> growing my database too much.
> how would i accomplish this?
> any comments would be appreciate

Best Strategy to backup the system db

Hi,
I am trying to approach method to copy my system database using enterprise
tool. I have researched on Database Mainteance tool and Backup database. I
would like to use one of the method to copy my database without any
transcational log since we don't have any transcational going on and not
growing my database too much.
how would i accomplish this?
any comments would be appreciatePooja,
You might want to clarify whether or not this is a system database i.e.,
Master, MSDB, etc... or a user-defined database. When you say "my" database
I'm going to assume it is a user-defined database. If you do not require
the transaction logs to be backed up as part of your database recovery
strategy, you can set the recovery mode for the database to SIMPLE (the
t-log will be automatically truncated). Then schedule your full database
backup as a job or use a Maintenance Plan.
HTH
Jerry
"Pooja" <Pooja@.discussions.microsoft.com> wrote in message
news:8F05EF72-186D-4B93-BA20-E81364824F29@.microsoft.com...
> Hi,
> I am trying to approach method to copy my system database using enterprise
> tool. I have researched on Database Mainteance tool and Backup database. I
> would like to use one of the method to copy my database without any
> transcational log since we don't have any transcational going on and not
> growing my database too much.
> how would i accomplish this?
> any comments would be appreciate

Best Strategy to backup the system db

Hi,
I am trying to approach method to copy my system database using enterprise
tool. I have researched on Database Mainteance tool and Backup database. I
would like to use one of the method to copy my database without any
transcational log since we don't have any transcational going on and not
growing my database too much.
how would i accomplish this?
any comments would be appreciatePooja,
You might want to clarify whether or not this is a system database i.e.,
Master, MSDB, etc... or a user-defined database. When you say "my" database
I'm going to assume it is a user-defined database. If you do not require
the transaction logs to be backed up as part of your database recovery
strategy, you can set the recovery mode for the database to SIMPLE (the
t-log will be automatically truncated). Then schedule your full database
backup as a job or use a Maintenance Plan.
HTH
Jerry
"Pooja" <Pooja@.discussions.microsoft.com> wrote in message
news:8F05EF72-186D-4B93-BA20-E81364824F29@.microsoft.com...
> Hi,
> I am trying to approach method to copy my system database using enterprise
> tool. I have researched on Database Mainteance tool and Backup database. I
> would like to use one of the method to copy my database without any
> transcational log since we don't have any transcational going on and not
> growing my database too much.
> how would i accomplish this?
> any comments would be appreciate

Best strategy for multiple sites

I have a client that has a central database. Users are dispersed at regional
offices, and should only have access to data pertaining to their own office.
I'm trying to figure out the best way to secure data access for each group
of users. I thought of maintaining a table of users and the regional office
they belong to. Then a UDF would only return the appropriate data. This
means doing everything at the database level, regardless of the front end
(in this case, SSRS).
Another strategy would be to do all the filtering within SSRS, using the
user global variable, and filter the data dynamically based on the user
value (with a query expression). One thing I don't like about this approach
is that any other front end (web or Windows based) could access data without
filtering on a regional site basis.
Any thoughts?
--
Thank you,
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.comIt looks to me like you are currently planning to have a one to one match
between SQL Server logins and users. This can be a real pain. Also, you lose
the chance for connection pooling. For connection pooling to work the
connection must be exactly the same. If SQL Server can be run in mixed mode
I suggest creating a read only user that is used just for reporting. Then
when you have stored procedure you just have to give this execution rights.
Then combine that with using the user global variable. Don't filter it
locally, that will require a lot more data to be processed and you are much
better off to limit the amount of data coming to RS. Instead you can have a
hidden parameter that takes the user variable does a query and uses the
first aggregate to return the office. Then for all queries that need the
office you then pass that to the query.
Another possibility is you have a database per office. Then create views to
the database that has the data. Some views are one to one, others use the
appropriate office. Then use the user in an expression for the datasource.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Alain Quesnel" <alainsansspam@.logiquel.com> wrote in message
news:%23vUJ5XpNIHA.5720@.TK2MSFTNGP04.phx.gbl...
>I have a client that has a central database. Users are dispersed at
>regional offices, and should only have access to data pertaining to their
>own office. I'm trying to figure out the best way to secure data access for
>each group of users. I thought of maintaining a table of users and the
>regional office they belong to. Then a UDF would only return the
>appropriate data. This means doing everything at the database level,
>regardless of the front end (in this case, SSRS).
> Another strategy would be to do all the filtering within SSRS, using the
> user global variable, and filter the data dynamically based on the user
> value (with a query expression). One thing I don't like about this
> approach is that any other front end (web or Windows based) could access
> data without filtering on a regional site basis.
> Any thoughts?
> --
> Thank you,
> Alain Quesnel
> alainsansspam@.logiquel.com
> www.logiquel.com
>

Best SSIS scripting tome?

Is The Rational Guide to Scripting SQL Server 2005 Integration Services Beta Preview by Donald Farmer the best way to learn how to use scripting in SSIS as of late 2006? I'm not a .NET developer, although I come from a Java and C++ background.

I already own Professional SQL Server 2005 Integration Server, but that one doesn't cover scripting so much.

Thanks in advance.

Donald is a frequent poster on this site. However, this question might be hard for him (and me) to answer because of conflict of interest issues.

I work with Donald and I can assure you that he is quite knowledgeable in the uses, misuses and intricacies of SSIS.

As one of our primary points of contact with customers he also owns a wealth of experience in typical and interesting uses for scripting.

I hope this helps inform your decision.

|||

I've read that book.. It gives you a nice understanding of the ScriptTask and ScriptComponent ( http://msdn2.microsoft.com/fr-fr/library/ms345171.aspx ) and gives you some tips and advices throughout the book. It covers input/ output variables and how to access them in the ScriptTask etc... The book has some practical realworld scenarios at the end.

However the real power of SSIS is hidden in the CustomComponent, but that's not even mentioned - well atleast imho (it's a lot easier to debug CustomComponents contra ScriptComponent which are nearly impossible)

Overall I was very happy with the chapters, the writing is clear and easy to understand. And if you understand Java it should be easy to get started with.

BEST SSIS equivalent for DTS2000 CURSORS

Hi everyone,

Right now we are trying to replace all stored procedures by SSIS logic.

One of the stored procedures does the following,

1. For Every record in PSTREELEAF find the Hierarchy FROM
PSTREENODE and store them in local valriables.

First the stored procedure pulls all the data from the PSTREELEAF table based on join condition into local variables using cursor. Then for every record in the cursor it finds the corresponding records from PSTREENODE table. This is just a part of the stored procedure.

can anyone tell me how can i do this in SSIS? and also tell me which task would be a perfect replacement for CURSORS in SSIS?

Thanks,

Praveen

Hi,
Do not use cursors if you can avoid it and do not attempt to replicate cursors.
Use Set based logic or derived tables or subqueries or Update statements or affect values to columns using subqueries as columns (top 1 or distinct).

Please post some more explicit examples and we may suggest another approach.

you can create loops in SSSI but the point is to make sure you really need this in the first place.

Philippe|||

Hi ,

thanks Phillippe.... here is the code which i need to change it to SSIS logic.

DECLARE NodeCursor CURSOR LOCAL FOR SELECT DISTINCT Tree_node_num,Range_from,Range_to,EFFDT
FROM FINANCEODS.DBO.PSTREELEAF
WHERE TREE_NAME='ACCTROLLUP' AND EffDt>=@.effDt AND INACTIVE_DATE IS NULL

OPEN NodeCursor

/*********************************************************************************
fetch nodes one by one and find out the hierarchy of the nodes
*********************************************************************************/

FETCH NEXT FROM NodeCursor
INTO @.Tree_node_num,@.LeafFrom,@.LeafTo,@.effDt
SET @.Parent_node_num=@.Tree_node_num

IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
SET @.p_errNum = 50505
SET @.p_errMsg = 'SPUpdateAccounts: Error Fetching Node Cursor.'
RETURN Exit Stored Procedure
END
WHILE @.@.FETCH_STATUS = 0
BEGIN
/*****************************************************
While Loop to find out all the Hierarchy information

******************************************************/
WHILE (@.Parent_node_num)<>0

BEGIN

/************************************************************************************
Find the Parent node and its level(hierarchy)
*************************************************************************************/
SELECT @.Parent_node_num=Parent_node_num, @.Tree_node_num=Tree_node_num,@.TreeLevel=TREE_LEVEL_NUM ,@.TreeNode=ISNULL(B.DESCR,A.TREE_NODE)
FROM FINANCEODS.DBO.PSTREENODE A
LEFT JOIN ( SELECT MAX(EFFDT) AS EFFDT,DESCR,TREE_NODE,INACTIVE_DATE FROM FINANCEODS.DBO.PS_TREE_NODE_TBL WHERE EFFDT<=@.EFFDT GROUP BY DESCR,TREE_NODE,INACTIVE_DATE ) B ON A.TREE_NODE=B.TREE_NODE
WHERE A.Tree_node_num=@.Tree_node_num
AND A.TREE_NAME='ACCTROLLUP' AND A.EFFDT=@.EFFDT AND A.INACTIVE_DATE IS NULL AND B.INACTIVE_DATE IS NULL
/************************************************************************************
Store the Hierarchy level information in local variables.
*************************************************************************************/
SELECT @.Source= (CASE @.TreeLevel WHEN 6 THEN @.TreeNode ELSE @.Source END),
@.Type= (CASE @.TreeLevel WHEN 5 THEN @.TreeNode ELSE @.Type END),
@.Element= (CASE @.TreeLevel WHEN 4 THEN @.TreeNode ELSE @.Element END),
@.Component= (CASE @.TreeLevel WHEN 3 THEN @.TreeNode ELSE @.Component END),
@.FinStatement= (CASE @.TreeLevel WHEN 2 THEN @.TreeNode ELSE @.FinStatement END)
SET @.Tree_node_num=@.Parent_node_num
END

Best SQL Magazine

Folks
I wanted to subscribe best online SQL magazine or printed material also
should be fine. There are so many magazines out there. I just wanted to
subscribe one and that got to be the best one. Can anyone suggest one that
you find it very useful, has real life tips, best practices etc?
Thank a lot!
I would suggest the SQL Server Magazine.
HTH, Jens Suessmeyer.
"rupart" <rupart@.discussions.microsoft.com> schrieb im Newsbeitrag
news:8A37A59F-CC48-4862-A7B1-9171AA44DAF1@.microsoft.com...
> Folks
> I wanted to subscribe best online SQL magazine or printed material also
> should be fine. There are so many magazines out there. I just wanted to
> subscribe one and that got to be the best one. Can anyone suggest one that
> you find it very useful, has real life tips, best practices etc?
> Thank a lot!
|||Best...well that's a hard question to answer because what's best for
me would be one that meets my needs. Since my needs would differ from
yours, my best would differ from yours.
Having said that, I find SQL Server Standard and SQL Server Magazine
to be useful. Both are relatively inexpensive and tend to offer good
quality.
SQL Server Magazine provides articles written by a handful of experts
with LOTS of technical expertise and knowledge.
SQL Server Central tends to offer a much more diverse set of authors
with varying levels of expertise and experiences. It's more of a users
helping users kind of approach.
I don't think you'll go wrong with either. Probably even good idea to
get both.
HTH...
Joe Webb
SQL Server MVP
~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg...l/-/0972688811
I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)
On Fri, 24 Jun 2005 01:17:02 -0700, rupart
<rupart@.discussions.microsoft.com> wrote:

>Folks
>I wanted to subscribe best online SQL magazine or printed material also
>should be fine. There are so many magazines out there. I just wanted to
>subscribe one and that got to be the best one. Can anyone suggest one that
>you find it very useful, has real life tips, best practices etc?
>Thank a lot!