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 tables based on information

I have a report generated in Crystal 10 that is exported to the SQL server
into its own DB. I finally go the report filtered out enough to where it put
s
each person with their designated type (i.e. Store location). My question
isthis: is it possible to have a query of some sort to look at the employee
type and copy all corresponding people in subsequesnt tables that are
associated with that type to another Table that actually corresponds with
their actual store? The 4 Fields that are generated by Crystal
are:DE_Employee_Type, DE_Employee_EmployeeID, DE_Employee_EmployeeName,
DE_Employee_EmployeeIsInactive. I guess the query I am looking for if it is
possible to analyse the Employee_Type column, then any thing that is
corresponding with says DA and has coresponding information in the tables
across from it and then copy the information from those columns to the
correspoding columns in another table?"Chris Chandler" <see@.top.com> wrote in message
news:Xns959C8912B154Dnospmnet@.207.46.248.16...
>I have a report generated in Crystal 10 that is exported to the SQL server
> into its own DB. I finally go the report filtered out enough to where it
> puts
> each person with their designated type (i.e. Store location). My question
> isthis: is it possible to have a query of some sort to look at the
> employee
> type and copy all corresponding people in subsequesnt tables that are
> associated with that type to another Table that actually corresponds with
> their actual store? The 4 Fields that are generated by Crystal
> are:DE_Employee_Type, DE_Employee_EmployeeID, DE_Employee_EmployeeName,
> DE_Employee_EmployeeIsInactive. I guess the query I am looking for if it
> is
> possible to analyse the Employee_Type column, then any thing that is
> corresponding with says DA and has coresponding information in the tables
> across from it and then copy the information from those columns to the
> correspoding columns in another table?
It is probably possible, but it would be much easier to help you if you
could post the DDL and some INSERTS with sample data.
What does the Crystal table look like when it is finished? Give us the
CREATE TABLE statement and some sample INSERTs.
What do your other tables look like that you would like to pull the data
from based on the crystal table's TYPE column.
What do you want your final output to look like or your final table? Give
us the CREATE TABLE statement for that as well and we should be able to help
you out.
Rick Sawtell
MCT, MCSD, MCDBA|||"Rick Sawtell" <quickening@.msn.com> wrote in
news:#WbDFFqxEHA.1296@.TK2MSFTNGP10.phx.gbl:

> "Chris Chandler" <see@.top.com> wrote in message
> news:Xns959C8912B154Dnospmnet@.207.46.248.16...
> It is probably possible, but it would be much easier to help you if you
> could post the DDL and some INSERTS with sample data.
> What does the Crystal table look like when it is finished? Give us the
> CREATE TABLE statement and some sample INSERTs.
> What do your other tables look like that you would like to pull the data
> from based on the crystal table's TYPE column.
> What do you want your final output to look like or your final table?
> Give us the CREATE TABLE statement for that as well and we should be
> able to help you out.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
Hm I would gladyl do so but I am so new to SQL and all that I am not sure
where or how to get that exact info. When I go to export it, I just use the
Export Wizard|||"Rick Sawtell" <quickening@.msn.com> wrote in
news:#WbDFFqxEHA.1296@.TK2MSFTNGP10.phx.gbl:

> "Chris Chandler" <see@.top.com> wrote in message
> news:Xns959C8912B154Dnospmnet@.207.46.248.16...
> It is probably possible, but it would be much easier to help you if you
> could post the DDL and some INSERTS with sample data.
> What does the Crystal table look like when it is finished? Give us the
> CREATE TABLE statement and some sample INSERTs.
> What do your other tables look like that you would like to pull the data
> from based on the crystal table's TYPE column.
> What do you want your final output to look like or your final table?
> Give us the CREATE TABLE statement for that as well and we should be
> able to help you out.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
Also, what happens is when Crystal generates the report, it has 1 table
with 4 fields as stated above. These fields have about 1400 records per
field. They are sorted by Employee Type (ie the store they work at). Again
this is all stored by type in one field. To a point this is fine, but is
there a way to either get Crystal to create 11 tables out of the one big
report or do I need some sort of code or query to break this down when it
is exported?
I am real real new to this so have patience with me. I am looking thru al
teh help files and even going thru the SBS SQL Server 2000 programming book
from Microsoft.

Copying tables based on information

I have a report generated in Crystal 10 that is exported to the SQL server
into its own DB. I finally go the report filtered out enough to where it puts
each person with their designated type (i.e. Store location). My question
isthis: is it possible to have a query of some sort to look at the employee
type and copy all corresponding people in subsequesnt tables that are
associated with that type to another Table that actually corresponds with
their actual store? The 4 Fields that are generated by Crystal
are:DE_Employee_Type, DE_Employee_EmployeeID, DE_Employee_EmployeeName,
DE_Employee_EmployeeIsInactive. I guess the query I am looking for if it is
possible to analyse the Employee_Type column, then any thing that is
corresponding with says DA and has coresponding information in the tables
across from it and then copy the information from those columns to the
correspoding columns in another table?"Chris Chandler" <see@.top.com> wrote in message
news:Xns959C8912B154Dnospmnet@.207.46.248.16...
>I have a report generated in Crystal 10 that is exported to the SQL server
> into its own DB. I finally go the report filtered out enough to where it
> puts
> each person with their designated type (i.e. Store location). My question
> isthis: is it possible to have a query of some sort to look at the
> employee
> type and copy all corresponding people in subsequesnt tables that are
> associated with that type to another Table that actually corresponds with
> their actual store? The 4 Fields that are generated by Crystal
> are:DE_Employee_Type, DE_Employee_EmployeeID, DE_Employee_EmployeeName,
> DE_Employee_EmployeeIsInactive. I guess the query I am looking for if it
> is
> possible to analyse the Employee_Type column, then any thing that is
> corresponding with says DA and has coresponding information in the tables
> across from it and then copy the information from those columns to the
> correspoding columns in another table?
It is probably possible, but it would be much easier to help you if you
could post the DDL and some INSERTS with sample data.
What does the Crystal table look like when it is finished? Give us the
CREATE TABLE statement and some sample INSERTs.
What do your other tables look like that you would like to pull the data
from based on the crystal table's TYPE column.
What do you want your final output to look like or your final table? Give
us the CREATE TABLE statement for that as well and we should be able to help
you out.
Rick Sawtell
MCT, MCSD, MCDBA|||"Rick Sawtell" <quickening@.msn.com> wrote in
news:#WbDFFqxEHA.1296@.TK2MSFTNGP10.phx.gbl:
> "Chris Chandler" <see@.top.com> wrote in message
> news:Xns959C8912B154Dnospmnet@.207.46.248.16...
>>I have a report generated in Crystal 10 that is exported to the SQL
>>server
>> into its own DB. I finally go the report filtered out enough to where
>> it puts
>> each person with their designated type (i.e. Store location). My
>> question isthis: is it possible to have a query of some sort to look at
>> the employee
>> type and copy all corresponding people in subsequesnt tables that are
>> associated with that type to another Table that actually corresponds
>> with their actual store? The 4 Fields that are generated by Crystal
>> are:DE_Employee_Type, DE_Employee_EmployeeID, DE_Employee_EmployeeName,
>> DE_Employee_EmployeeIsInactive. I guess the query I am looking for if
>> it is
>> possible to analyse the Employee_Type column, then any thing that is
>> corresponding with says DA and has coresponding information in the
>> tables across from it and then copy the information from those columns
>> to the correspoding columns in another table?
> It is probably possible, but it would be much easier to help you if you
> could post the DDL and some INSERTS with sample data.
> What does the Crystal table look like when it is finished? Give us the
> CREATE TABLE statement and some sample INSERTs.
> What do your other tables look like that you would like to pull the data
> from based on the crystal table's TYPE column.
> What do you want your final output to look like or your final table?
> Give us the CREATE TABLE statement for that as well and we should be
> able to help you out.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
Hm I would gladyl do so but I am so new to SQL and all that I am not sure
where or how to get that exact info. When I go to export it, I just use the
Export Wizard|||"Rick Sawtell" <quickening@.msn.com> wrote in
news:#WbDFFqxEHA.1296@.TK2MSFTNGP10.phx.gbl:
> "Chris Chandler" <see@.top.com> wrote in message
> news:Xns959C8912B154Dnospmnet@.207.46.248.16...
>>I have a report generated in Crystal 10 that is exported to the SQL
>>server
>> into its own DB. I finally go the report filtered out enough to where
>> it puts
>> each person with their designated type (i.e. Store location). My
>> question isthis: is it possible to have a query of some sort to look at
>> the employee
>> type and copy all corresponding people in subsequesnt tables that are
>> associated with that type to another Table that actually corresponds
>> with their actual store? The 4 Fields that are generated by Crystal
>> are:DE_Employee_Type, DE_Employee_EmployeeID, DE_Employee_EmployeeName,
>> DE_Employee_EmployeeIsInactive. I guess the query I am looking for if
>> it is
>> possible to analyse the Employee_Type column, then any thing that is
>> corresponding with says DA and has coresponding information in the
>> tables across from it and then copy the information from those columns
>> to the correspoding columns in another table?
> It is probably possible, but it would be much easier to help you if you
> could post the DDL and some INSERTS with sample data.
> What does the Crystal table look like when it is finished? Give us the
> CREATE TABLE statement and some sample INSERTs.
> What do your other tables look like that you would like to pull the data
> from based on the crystal table's TYPE column.
> What do you want your final output to look like or your final table?
> Give us the CREATE TABLE statement for that as well and we should be
> able to help you out.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
Also, what happens is when Crystal generates the report, it has 1 table
with 4 fields as stated above. These fields have about 1400 records per
field. They are sorted by Employee Type (ie the store they work at). Again
this is all stored by type in one field. To a point this is fine, but is
there a way to either get Crystal to create 11 tables out of the one big
report or do I need some sort of code or query to break this down when it
is exported?
I am real real new to this so have patience with me. I am looking thru al
teh help files and even going thru the SBS SQL Server 2000 Programming book
from Microsoft.

Copying tables based on information

I have a report generated in Crystal 10 that is exported to the SQL server
into its own DB. I finally go the report filtered out enough to where it puts
each person with their designated type (i.e. Store location). My question
isthis: is it possible to have a query of some sort to look at the employee
type and copy all corresponding people in subsequesnt tables that are
associated with that type to another Table that actually corresponds with
their actual store? The 4 Fields that are generated by Crystal
are:DE_Employee_Type, DE_Employee_EmployeeID, DE_Employee_EmployeeName,
DE_Employee_EmployeeIsInactive. I guess the query I am looking for if it is
possible to analyse the Employee_Type column, then any thing that is
corresponding with says DA and has coresponding information in the tables
across from it and then copy the information from those columns to the
correspoding columns in another table?
"Chris Chandler" <see@.top.com> wrote in message
news:Xns959C8912B154Dnospmnet@.207.46.248.16...
>I have a report generated in Crystal 10 that is exported to the SQL server
> into its own DB. I finally go the report filtered out enough to where it
> puts
> each person with their designated type (i.e. Store location). My question
> isthis: is it possible to have a query of some sort to look at the
> employee
> type and copy all corresponding people in subsequesnt tables that are
> associated with that type to another Table that actually corresponds with
> their actual store? The 4 Fields that are generated by Crystal
> are:DE_Employee_Type, DE_Employee_EmployeeID, DE_Employee_EmployeeName,
> DE_Employee_EmployeeIsInactive. I guess the query I am looking for if it
> is
> possible to analyse the Employee_Type column, then any thing that is
> corresponding with says DA and has coresponding information in the tables
> across from it and then copy the information from those columns to the
> correspoding columns in another table?
It is probably possible, but it would be much easier to help you if you
could post the DDL and some INSERTS with sample data.
What does the Crystal table look like when it is finished? Give us the
CREATE TABLE statement and some sample INSERTs.
What do your other tables look like that you would like to pull the data
from based on the crystal table's TYPE column.
What do you want your final output to look like or your final table? Give
us the CREATE TABLE statement for that as well and we should be able to help
you out.
Rick Sawtell
MCT, MCSD, MCDBA
|||"Rick Sawtell" <quickening@.msn.com> wrote in
news:#WbDFFqxEHA.1296@.TK2MSFTNGP10.phx.gbl:

> "Chris Chandler" <see@.top.com> wrote in message
> news:Xns959C8912B154Dnospmnet@.207.46.248.16...
> It is probably possible, but it would be much easier to help you if you
> could post the DDL and some INSERTS with sample data.
> What does the Crystal table look like when it is finished? Give us the
> CREATE TABLE statement and some sample INSERTs.
> What do your other tables look like that you would like to pull the data
> from based on the crystal table's TYPE column.
> What do you want your final output to look like or your final table?
> Give us the CREATE TABLE statement for that as well and we should be
> able to help you out.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
Hm I would gladyl do so but I am so new to SQL and all that I am not sure
where or how to get that exact info. When I go to export it, I just use the
Export Wizard
|||"Rick Sawtell" <quickening@.msn.com> wrote in
news:#WbDFFqxEHA.1296@.TK2MSFTNGP10.phx.gbl:

> "Chris Chandler" <see@.top.com> wrote in message
> news:Xns959C8912B154Dnospmnet@.207.46.248.16...
> It is probably possible, but it would be much easier to help you if you
> could post the DDL and some INSERTS with sample data.
> What does the Crystal table look like when it is finished? Give us the
> CREATE TABLE statement and some sample INSERTs.
> What do your other tables look like that you would like to pull the data
> from based on the crystal table's TYPE column.
> What do you want your final output to look like or your final table?
> Give us the CREATE TABLE statement for that as well and we should be
> able to help you out.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
Also, what happens is when Crystal generates the report, it has 1 table
with 4 fields as stated above. These fields have about 1400 records per
field. They are sorted by Employee Type (ie the store they work at). Again
this is all stored by type in one field. To a point this is fine, but is
there a way to either get Crystal to create 11 tables out of the one big
report or do I need some sort of code or query to break this down when it
is exported?
I am real real new to this so have patience with me. I am looking thru al
teh help files and even going thru the SBS SQL Server 2000 Programming book
from Microsoft.
sql

Copying Data to a File

Hi Guys,
I am trying to fix the following problem:
I need to create a .txt for input into a unix based system (sybase).
This has been done using BCP, and not DTS. Once BCP'ing data into the
.txt file, there are <CR><LF> data at the end of every row. Once the
data is imported into the unix system, these are showing up in the
database.
I've tried BCP with the:
-r \n
-r \r
-r \n \r
With no success (the characters still appear in sybase).
Is there any way to do this with BCP, and not have the characters
appear?
thanks,
Justinjustin.drennan@.gmail.com wrote:
> Hi Guys,
> I am trying to fix the following problem:
> I need to create a .txt for input into a unix based system (sybase).
> This has been done using BCP, and not DTS. Once BCP'ing data into the
> .txt file, there are <CR><LF> data at the end of every row. Once the
> data is imported into the unix system, these are showing up in the
> database.
> I've tried BCP with the:
> -r \n
> -r \r
> -r \n \r
> With no success (the characters still appear in sybase).
> Is there any way to do this with BCP, and not have the characters
> appear?
You might want to try the tr command in *nix:
in_file | tr -d \r > out_file
Delete the carriage return character from the in_file and output to the
out_file.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

Copying data across rows

I have one table where I am trying to copy a number from one field
in one row into another field in another row based on two conditions.
More specifically, I need to copy the number from column DIFF for
group LY into column DIFF_CO for group L+. This is what I would like
to do:
UPDATE mytable
SET diff_co = (SELECT diff FROM mytable WHERE group=LY and name=name1)
WHERE name=name1 and group=L+
... so that my end result looks like this:
GROUP NAME DIFF DIFF_CO
LY Name1 9.9
L+ Name1 10.2 9.9
Where I am running into difficulty is that the select statement returns
242 results, and thus the "Subquery returned more than 1 value" error.
Any suggestions on how I can do this?
Hope that following can help you:
1. Verify how many rows are returned by following query:
SELECT distinct diff FROM mytable WHERE group=LY and name=name1
2. If the above query returns only 1 row, then use the following query to
'copy data across rows':
UPDATE mytable
SET diff_co = (SELECT distinct diff FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+
"nicole" wrote:

> I have one table where I am trying to copy a number from one field
> in one row into another field in another row based on two conditions.
> More specifically, I need to copy the number from column DIFF for
> group LY into column DIFF_CO for group L+. This is what I would like
> to do:
> UPDATE mytable
> SET diff_co = (SELECT diff FROM mytable WHERE group=LY and name=name1)
> WHERE name=name1 and group=L+
>
> ... so that my end result looks like this:
> GROUP NAME DIFF DIFF_CO
> LY Name1 9.9
> L+ Name1 10.2 9.9
>
> Where I am running into difficulty is that the select statement returns
> 242 results, and thus the "Subquery returned more than 1 value" error.
> Any suggestions on how I can do this?
|||> Hope that following can help you:
Thanks for the reply!!

> 1. Verify how many rows are returned by following query:
> SELECT distinct diff FROM mytable WHERE group=LY and name=name1
> 2. If the above query returns only 1 row, then use the following query to
> 'copy data across rows':
Unfortunately, the distinct query returns multiple rows.
Any other suggestions?
|||since it gives multiple diff values you have to choose which diff value
you want to use for update
You can do this by either using max, min or top 1 in the subquery.
UPDATE mytable
SET diff_co = (SELECT max(diff) FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+
or
UPDATE mytable
SET diff_co = (SELECT min(diff) FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+
or
UPDATE mytable
SET diff_co = (SELECT top 1 (diff) FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+

Copying data across rows

I have one table where I am trying to copy a number from one field
in one row into another field in another row based on two conditions.
More specifically, I need to copy the number from column DIFF for
group LY into column DIFF_CO for group L+. This is what I would like
to do:
UPDATE mytable
SET diff_co = (SELECT diff FROM mytable WHERE group=LY and name=name1)
WHERE name=name1 and group=L+
... so that my end result looks like this:
GROUP NAME DIFF DIFF_CO
LY Name1 9.9
L+ Name1 10.2 9.9
Where I am running into difficulty is that the select statement returns
242 results, and thus the "Subquery returned more than 1 value" error.
Any suggestions on how I can do this?Hope that following can help you:
1. Verify how many rows are returned by following query:
SELECT distinct diff FROM mytable WHERE group=LY and name=name1
2. If the above query returns only 1 row, then use the following query to
'copy data across rows':
UPDATE mytable
SET diff_co = (SELECT distinct diff FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+
"nicole" wrote:

> I have one table where I am trying to copy a number from one field
> in one row into another field in another row based on two conditions.
> More specifically, I need to copy the number from column DIFF for
> group LY into column DIFF_CO for group L+. This is what I would like
> to do:
> UPDATE mytable
> SET diff_co = (SELECT diff FROM mytable WHERE group=LY and name=name1)
> WHERE name=name1 and group=L+
>
> ... so that my end result looks like this:
> GROUP NAME DIFF DIFF_CO
> LY Name1 9.9
> L+ Name1 10.2 9.9
>
> Where I am running into difficulty is that the select statement returns
> 242 results, and thus the "Subquery returned more than 1 value" error.
> Any suggestions on how I can do this?|||> Hope that following can help you:
Thanks for the reply!!

> 1. Verify how many rows are returned by following query:
> SELECT distinct diff FROM mytable WHERE group=LY and name=name1
> 2. If the above query returns only 1 row, then use the following query to
> 'copy data across rows':
Unfortunately, the distinct query returns multiple rows.
Any other suggestions?|||since it gives multiple diff values you have to choose which diff value
you want to use for update
You can do this by either using max, min or top 1 in the subquery.
UPDATE mytable
SET diff_co = (SELECT max(diff) FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+
or
UPDATE mytable
SET diff_co = (SELECT min(diff) FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+
or
UPDATE mytable
SET diff_co = (SELECT top 1 (diff) FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+

Copying data across rows

I have one table where I am trying to copy a number from one field
in one row into another field in another row based on two conditions.
More specifically, I need to copy the number from column DIFF for
group LY into column DIFF_CO for group L+. This is what I would like
to do:
UPDATE mytable
SET diff_co = (SELECT diff FROM mytable WHERE group=LY and name=name1)
WHERE name=name1 and group=L+
... so that my end result looks like this:
GROUP NAME DIFF DIFF_CO
LY Name1 9.9
L+ Name1 10.2 9.9
Where I am running into difficulty is that the select statement returns
242 results, and thus the "Subquery returned more than 1 value" error.
Any suggestions on how I can do this?Hope that following can help you:
1. Verify how many rows are returned by following query:
SELECT distinct diff FROM mytable WHERE group=LY and name=name1
2. If the above query returns only 1 row, then use the following query to
'copy data across rows':
UPDATE mytable
SET diff_co = (SELECT distinct diff FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+
"nicole" wrote:
> I have one table where I am trying to copy a number from one field
> in one row into another field in another row based on two conditions.
> More specifically, I need to copy the number from column DIFF for
> group LY into column DIFF_CO for group L+. This is what I would like
> to do:
> UPDATE mytable
> SET diff_co = (SELECT diff FROM mytable WHERE group=LY and name=name1)
> WHERE name=name1 and group=L+
>
> ... so that my end result looks like this:
> GROUP NAME DIFF DIFF_CO
> LY Name1 9.9
> L+ Name1 10.2 9.9
>
> Where I am running into difficulty is that the select statement returns
> 242 results, and thus the "Subquery returned more than 1 value" error.
> Any suggestions on how I can do this?|||> Hope that following can help you:
Thanks for the reply!!
> 1. Verify how many rows are returned by following query:
> SELECT distinct diff FROM mytable WHERE group=LY and name=name1
> 2. If the above query returns only 1 row, then use the following query to
> 'copy data across rows':
Unfortunately, the distinct query returns multiple rows.
Any other suggestions?|||since it gives multiple diff values you have to choose which diff value
you want to use for update
You can do this by either using max, min or top 1 in the subquery.
UPDATE mytable
SET diff_co = (SELECT max(diff) FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+
or
UPDATE mytable
SET diff_co = (SELECT min(diff) FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+
or
UPDATE mytable
SET diff_co = (SELECT top 1 (diff) FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+

Copy Specific Cells between Tables based on Login ID

I want to copy FirstName and LastName cells from table1 to table2. Which FirstName and LastName cells depend on the username, i.e. which person has logged in.

Q1) How can this be done? (I have chosen to SELECT from Table1 and output to variables (C# code); then INSERT these variables into Table2. [Finding it very difficult]).

Any suggestions/comments would be appreciated. Thank you in advance.

You can achieve what you need in a single statement

In SQL Server and Oracle

insert into test3 (lib_id, lib_name, lib_desc)
select lib_id, lib_name, lib_desc from test2 where lib_id=1