Correct Edition of Reporting Services

Hi,

We are in the process of replacing our Microsoft Access(.adp)/SQL2000 based reporting tool to ReportingServices/SQL2000 one. Our development team would be in charge of converting the existing Access based reports to Reporting Service format and and any new reports thereafter. Keeping that in mind which of the existing version(Standard, Enterprise, Developer) of Reporting Services would best suit our environment? The final picture would be something like all the reports will be residing on the Reporting server and it will be accessed by clients with the help of a custom reporting application(made in .net) that calls these reports based on user level permissions to these reports.

Will this be possible by using a Standard edition of RS?

Looking forward to your replies..

sk

Do you mean SQL 2005? I've never used SQL 2000 RS so I couldn't really comment.

|||

No, i meant SQL2000. I know that RS is bundled into the SQL2005 installation media but as far as SQL2000 is concerned, it was only released quite some time after SQL Server was released. I have read in the help file that the Standard edition of SQL2000 does support the standard as well as enterprise edition of RS2000. Therefore i was looking for the correct edition that best suits our requirement which i have mentioned in my earlier post...

sk

|||

Here is the feature mix information for SQL Server 2005 - it is applicable to SQL 2000 also with the exception of features that were new to 2005 (Report Builder, for example).

http://www.microsoft.com/sql/technologies/reporting/rsfeatures.mspx

|||

Thanks Carolyn for the link. So can i safely presume that i could deploy my reports created with Standard Reporting Services for my my users to access in a client-server setup?

Last But not least, I had downloaded the 120 days Eval copy of RS for SQL2000. Since my SQL2000 server is already licensed, should i need extra licensing to use Reporting services?

sk

|||

You should be able to use the standard edition of SRS 2000 to accomplish what you require. The version that you are licensed for matches the version of SQL Server that you have. Developer edition is never licensed for production work.

If you are developing using VS 2005, you have access to the ReportViewer control even if you don't have SSRS 2005 in your environment. You could also embed your reports into your application using it. From your post I don't know if you are doing a WinForm or WebForm, but the ReportViewer control works well in either case. You could source data from your SQL Server 2000 instance, but I don't believe you will be able to display SRS 2000 reports from it directly.

You will need the Visual Studio 2003 environment for anyone you want to be able to create reports. That yet another big difference between SRS 2000 and SSRS 2005. SSRS 2005 does not require VS to be installed, it is capable of installing the pieces it requires.

RE the licensing, short answer: SRS is a component of SQL Server. Anywhere you are licensed to have SQL Server installed, you can also have SRS installed. Here is a page that describes some of the different scenarios:

http://www.microsoft.com/sql/prodinfo/previousversions/rs/howtolicensers.mspx

Regarding your eval copy, if you do not want an interruption of services, go to the link below and order your copy of SRS for S/H cost. Do it now, before the eval expires.

http://www.microsoft.com/sql/prodinfo/previousversions/rs/retailfulfillment.mspx

Make sure to get SP2 for SRS 2000 (http://www.microsoft.com/downloads/details.aspx?FamilyId=502C0D89-1308-4662-8F58-CEC55EF1235B&displaylang=en) as it expands the feature set (adding client printing) as well as fixes some bugs.

Good luck with your migration!

Larry Smithmier

|||

Thanks for your input Larry.

So once i get the licensed copy of SRS2000, i would have to un-install the existing eval copy and install the licensed one?

If thats the case i would want to warn my developers against deploying anything on the server untill a licensed copy is avalable...

Would i have to re-install the client components too on the developers machines? And finally, would i have to apply the SRS2000 SP2 to the developers machines too?

sk

|||

I believe that installing the full version over the evaluation should upgrade the instance in-place. You should not have to uninstall the eval copy.

Let your developers deploy at will. I believe that the installation will upgrade your version. The worst case scenario would be that your developers would need to re-deploy the reports after you uninstalled and reinstalled.

Yes, you would want to apply SP2 to development boxes also.

Larry Smithmier

sql

Correct Edition of Reporting Services

Hi,

We are in the process of replacing our Microsoft Access(.adp)/SQL2000 based reporting tool to ReportingServices/SQL2000 one. Our development team would be in charge of converting the existing Access based reports to Reporting Service format and and any new reports thereafter. Keeping that in mind which of the existing version(Standard, Enterprise, Developer) of Reporting Services would best suit our environment? The final picture would be something like all the reports will be residing on the Reporting server and it will be accessed by clients with the help of a custom reporting application(made in .net) that calls these reports based on user level permissions to these reports.

Will this be possible by using a Standard edition of RS?

Looking forward to your replies..

sk

Do you mean SQL 2005? I've never used SQL 2000 RS so I couldn't really comment.

|||

No, i meant SQL2000. I know that RS is bundled into the SQL2005 installation media but as far as SQL2000 is concerned, it was only released quite some time after SQL Server was released. I have read in the help file that the Standard edition of SQL2000 does support the standard as well as enterprise edition of RS2000. Therefore i was looking for the correct edition that best suits our requirement which i have mentioned in my earlier post...

sk

|||

Here is the feature mix information for SQL Server 2005 - it is applicable to SQL 2000 also with the exception of features that were new to 2005 (Report Builder, for example).

http://www.microsoft.com/sql/technologies/reporting/rsfeatures.mspx

|||

Thanks Carolyn for the link. So can i safely presume that i could deploy my reports created with Standard Reporting Services for my my users to access in a client-server setup?

Last But not least, I had downloaded the 120 days Eval copy of RS for SQL2000. Since my SQL2000 server is already licensed, should i need extra licensing to use Reporting services?

sk

|||

You should be able to use the standard edition of SRS 2000 to accomplish what you require. The version that you are licensed for matches the version of SQL Server that you have. Developer edition is never licensed for production work.

If you are developing using VS 2005, you have access to the ReportViewer control even if you don't have SSRS 2005 in your environment. You could also embed your reports into your application using it. From your post I don't know if you are doing a WinForm or WebForm, but the ReportViewer control works well in either case. You could source data from your SQL Server 2000 instance, but I don't believe you will be able to display SRS 2000 reports from it directly.

You will need the Visual Studio 2003 environment for anyone you want to be able to create reports. That yet another big difference between SRS 2000 and SSRS 2005. SSRS 2005 does not require VS to be installed, it is capable of installing the pieces it requires.

RE the licensing, short answer: SRS is a component of SQL Server. Anywhere you are licensed to have SQL Server installed, you can also have SRS installed. Here is a page that describes some of the different scenarios:

http://www.microsoft.com/sql/prodinfo/previousversions/rs/howtolicensers.mspx

Regarding your eval copy, if you do not want an interruption of services, go to the link below and order your copy of SRS for S/H cost. Do it now, before the eval expires.

http://www.microsoft.com/sql/prodinfo/previousversions/rs/retailfulfillment.mspx

Make sure to get SP2 for SRS 2000 (http://www.microsoft.com/downloads/details.aspx?FamilyId=502C0D89-1308-4662-8F58-CEC55EF1235B&displaylang=en) as it expands the feature set (adding client printing) as well as fixes some bugs.

Good luck with your migration!

Larry Smithmier

|||

Thanks for your input Larry.

So once i get the licensed copy of SRS2000, i would have to un-install the existing eval copy and install the licensed one?

If thats the case i would want to warn my developers against deploying anything on the server untill a licensed copy is avalable...

Would i have to re-install the client components too on the developers machines? And finally, would i have to apply the SRS2000 SP2 to the developers machines too?

sk

|||

I believe that installing the full version over the evaluation should upgrade the instance in-place. You should not have to uninstall the eval copy.

Let your developers deploy at will. I believe that the installation will upgrade your version. The worst case scenario would be that your developers would need to re-deploy the reports after you uninstalled and reinstalled.

Yes, you would want to apply SP2 to development boxes also.

Larry Smithmier

Correct Edition of Reporting Services

Hi,

We are in the process of replacing our Microsoft Access(.adp)/SQL2000 based reporting tool to ReportingServices/SQL2000 one. Our development team would be in charge of converting the existing Access based reports to Reporting Service format and and any new reports thereafter. Keeping that in mind which of the existing version(Standard, Enterprise, Developer) of Reporting Services would best suit our environment? The final picture would be something like all the reports will be residing on the Reporting server and it will be accessed by clients with the help of a custom reporting application(made in .net) that calls these reports based on user level permissions to these reports.

Will this be possible by using a Standard edition of RS?

Looking forward to your replies..

sk

Do you mean SQL 2005? I've never used SQL 2000 RS so I couldn't really comment.

|||

No, i meant SQL2000. I know that RS is bundled into the SQL2005 installation media but as far as SQL2000 is concerned, it was only released quite some time after SQL Server was released. I have read in the help file that the Standard edition of SQL2000 does support the standard as well as enterprise edition of RS2000. Therefore i was looking for the correct edition that best suits our requirement which i have mentioned in my earlier post...

sk

|||

Here is the feature mix information for SQL Server 2005 - it is applicable to SQL 2000 also with the exception of features that were new to 2005 (Report Builder, for example).

http://www.microsoft.com/sql/technologies/reporting/rsfeatures.mspx

|||

Thanks Carolyn for the link. So can i safely presume that i could deploy my reports created with Standard Reporting Services for my my users to access in a client-server setup?

Last But not least, I had downloaded the 120 days Eval copy of RS for SQL2000. Since my SQL2000 server is already licensed, should i need extra licensing to use Reporting services?

sk

|||

You should be able to use the standard edition of SRS 2000 to accomplish what you require. The version that you are licensed for matches the version of SQL Server that you have. Developer edition is never licensed for production work.

If you are developing using VS 2005, you have access to the ReportViewer control even if you don't have SSRS 2005 in your environment. You could also embed your reports into your application using it. From your post I don't know if you are doing a WinForm or WebForm, but the ReportViewer control works well in either case. You could source data from your SQL Server 2000 instance, but I don't believe you will be able to display SRS 2000 reports from it directly.

You will need the Visual Studio 2003 environment for anyone you want to be able to create reports. That yet another big difference between SRS 2000 and SSRS 2005. SSRS 2005 does not require VS to be installed, it is capable of installing the pieces it requires.

RE the licensing, short answer: SRS is a component of SQL Server. Anywhere you are licensed to have SQL Server installed, you can also have SRS installed. Here is a page that describes some of the different scenarios:

http://www.microsoft.com/sql/prodinfo/previousversions/rs/howtolicensers.mspx

Regarding your eval copy, if you do not want an interruption of services, go to the link below and order your copy of SRS for S/H cost. Do it now, before the eval expires.

http://www.microsoft.com/sql/prodinfo/previousversions/rs/retailfulfillment.mspx

Make sure to get SP2 for SRS 2000 (http://www.microsoft.com/downloads/details.aspx?FamilyId=502C0D89-1308-4662-8F58-CEC55EF1235B&displaylang=en) as it expands the feature set (adding client printing) as well as fixes some bugs.

Good luck with your migration!

Larry Smithmier

|||

Thanks for your input Larry.

So once i get the licensed copy of SRS2000, i would have to un-install the existing eval copy and install the licensed one?

If thats the case i would want to warn my developers against deploying anything on the server untill a licensed copy is avalable...

Would i have to re-install the client components too on the developers machines? And finally, would i have to apply the SRS2000 SP2 to the developers machines too?

sk

|||

I believe that installing the full version over the evaluation should upgrade the instance in-place. You should not have to uninstall the eval copy.

Let your developers deploy at will. I believe that the installation will upgrade your version. The worst case scenario would be that your developers would need to re-deploy the reports after you uninstalled and reinstalled.

Yes, you would want to apply SP2 to development boxes also.

Larry Smithmier

Copying Data Problem with Export data tool

I Have a problem when copying data from one server to another in Management studio, I need to create and exact copy of the original because of primary key relationships,

Currently when I export the data the data will run through an insert type statement, which means that all PKs are reissued, rather than being duplicated from the original, How can I be sure that the data will be copied exactly how it is on one server to the other.It sounds as though the table that you want to copy the data into, has an identity column assigned to it. For the situation that you have described, there are two solutions available, each depends on how this secondary table will be used.

If the new table will be used in a transactional environment and it must maintain correct relationships with other tables, then you will need to maintain the same list of primary key values. To do this, we need to understand how your values for the primary key column is generated. I'm assuming a non-composite (single column) key for your system.

If it's an identity column, then you will want to maintain this property for future inserts directly into the new table, but you will also need to insert existing rows from the current table with the correct ID values. This situation is common and can be solved elegantly using the identity_insert option.

Using identity_insert, you can override the SQL Server automatic generation process of the column value for the identity column of table, and explicitly supply your own values. Once you have finished inserted these values, you can turn the identity_insert column off, to allow the column to behave normally and generate sequential identity values. You will have to research this particular aspect of behaviour to understand exactly how the identity column will respond after you turn identity_insert back off, and having inserted a random series of values. From experience a few years ago mind you, I don't believe there is any problem here and that SQL Server just resume the identity column counter by adding one to the maximum integer value in the column.

If on the other hand your table will be used in a more static context, for example bespoke data analysis, then I would suggest creating a table without the identity column. The corresponding column in the new table will have the same data type as the source table and will maintain a foreign key relationship back to the source column to ensure integrity throughout the lifetime of the table use in analysis.

Using this approach, without the identity column, you can copy the data using a simple multiple row insert operation. A example of this is below:

insert into destinationTable
columnA,
columnB,
columnC
select
columnA,
columnB,
columnC
from
sourceTable

It's important to remember that there are no restrictions, or very very few, that apply to a select statement when used as the source for a multiple row insert. Therefore, you should not hesitate to use any conditional constructs any other elements of the SQL language to ensure you insert only the data that you want. Often this feature is overlooked and people forget that the select statement need not be a simple one set query.

Regards,|||I think it is the identity insert option I need to use, I will give it a go in three days when I do the test transfer, then post on the outcome,

There is the Identity insert option on the Export data tool, Is this the option your talking about? or is there an option in the table properties?|||There is only one way to apply identity_insert, which is as a table option applicable only to the current session and for the duration of that session or until the option is explicitly turned off.

I would say that the option to enable identity_insert from within an ETL package is accomplished by the tool transparently issuing the option directly to SQL Server on your session's behalf. In this way, the ETL tool serves as just a GUI to execute SQL DDL and DML.

Nonetheless, you are correct in your thinking to investigate the identity_insert option. Just remember that column names must be specified when using this option, a requirement that often many people overlook and which can cause unnecessary frustration. This is one area where a tool similar to the one you describe can be helpful, in ensuring little compliance issues like this.

Regards,|||Got it worked a Treat, Use The Export Data tool in Management Studio, Not sure what I did differently to before, but I set the

Delete Rows in Destination table to true (even though it was empty)
And Enable Identity insert to true

Copied all the data as it was, missing all the PKs It had been including, and the PK count after the insert of the data starts at the last record it doesn't fill in the gaps, thanks for your help.

copy tables along with constraints in DTS

Hi All,

We are using DTS tool for importing data from one database which is present in one server to another present in another server. the structure and the data is getting copied where as the constraints are not getting copied. pls suggest me as to how should i go about to to complete the job.

waiting for ur reply.

TIA
regards
AdilWhat exactly are your requirements for this ? Are you copying tables/databases ... you need indexes, constraints ...|||In Enterprise Manager, right click on the database and select "All Tasks - Import/Export", ... , select "copy objects and data between SQL Server database", click on "next", you have the option to copy all database objects include constraints into destination database.|||Hi Adil,

Would encourage you to use a free tool for DTS, which copies tables along with constraints. Please try Vaman DataServer from the following link. http://www.vaman.net/vmndataserver.asp
You may need to login for downloading.
Hope this helps...

popeye.

Originally posted by aadil
Hi All,

We are using DTS tool for importing data from one database which is present in one server to another present in another server. the structure and the data is getting copied where as the constraints are not getting copied. pls suggest me as to how should i go about to to complete the job.

waiting for ur reply.

TIA
regards
Adil