Derived Columns in one to many relationships

I'm trying to write a query that concatenates multiple records into one
derived column. Let's say I have an author (Joe Writer) who has written
three books (Book 1, Book2 and Book 3). The author is in tblAuthors, his
books are in the tblBooks and they are joined by the AuthorID field
(number). If I use a simple select query to give me the author name and the
title, I will get three records, one for each book written.

What I want is to have all three books combined into one derived column. So
if I do the select statement, I will get one column with the author name,
and the second column will put together all three names of the book
separated by a column. So it will look like:

Author Title

Joe Writer Book 1, Book 2, Book 3,

Rather than having it appear as 3 records:

Joe Writer Book 1
Joe Writer Book 2
Joe Writer Book 3

Could someone help me with the SQL involved in this?

Thanks for the help.

Cheers,

MikeOne approach is shown in http://www.mvps.org/access/modules/mdl0008.htm at
"The Access Web"

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Big Time" <big-time-grizz@.remove-for-spam-hotmail.com> wrote in message
news:cfu2e7$18gm$1@.lettuce.bcit.ca...
> I'm trying to write a query that concatenates multiple records into one
> derived column. Let's say I have an author (Joe Writer) who has written
> three books (Book 1, Book2 and Book 3). The author is in tblAuthors, his
> books are in the tblBooks and they are joined by the AuthorID field
> (number). If I use a simple select query to give me the author name and
the
> title, I will get three records, one for each book written.
> What I want is to have all three books combined into one derived column.
So
> if I do the select statement, I will get one column with the author name,
> and the second column will put together all three names of the book
> separated by a column. So it will look like:
> Author Title
> Joe Writer Book 1, Book 2, Book 3,
> Rather than having it appear as 3 records:
> Joe Writer Book 1
> Joe Writer Book 2
> Joe Writer Book 3
> Could someone help me with the SQL involved in this?
> Thanks for the help.
> Cheers,
> Mike|||Mike,

read this article...

http://www.mvps.org/access/modules/mdl0004.htm

It has code that does this.|||Try this out
DECLARE @.BookNames varchar(1000)
SET @.BookNames = ''
SELECT @.BookNames = @.BookNames +Book + ', ' FROM Books
where author = 'Joe Writer'
SELECT 'Joe Writer',@.BookNames|||JK (jaikrishnan_nair@.hotmail.com) writes:
> Try this out
> DECLARE @.BookNames varchar(1000)
> SET @.BookNames = ''
> SELECT @.BookNames = @.BookNames +Book + ', ' FROM Books
> where author = 'Joe Writer'
> SELECT 'Joe Writer',@.BookNames

This may work. Or not work. The result of this sort of operation is
undefined in SQL Server. This is one of the few situations where
iterating over the data is a better option.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Deploying System DSN to Multiple PCs

Is there any way to deploy System DSNs to multiple PCs?
I'm in a windows xp client environment with sql server 2000 running on a
windows server 200 box.
Thanks for any help.
System DSNs are stored in the registry under
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI. Try exporting the registry node
for the data source and run it on another computer to see if it works.
Roman
Roman Rehak, MCSD, MCDBA, MCSA
SQL Server Specialist
Competitive Computing
http://sqljunkies.com/WebLog/Roman
"Josh" <Josh@.discussions.microsoft.com> wrote in message
news:FA14A0DA-A2C4-4779-ADDC-49F79016EF6E@.microsoft.com...
> Is there any way to deploy System DSNs to multiple PCs?
> I'm in a windows xp client environment with sql server 2000 running on a
> windows server 200 box.
> Thanks for any help.
|||Thanks for replying but that didn't work. I was thinking maybe there might
be a way to create a vbscript to create the system dsns.
"Roman Rehak" wrote:

> System DSNs are stored in the registry under
> HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI. Try exporting the registry node
> for the data source and run it on another computer to see if it works.
> Roman
>
> Roman Rehak, MCSD, MCDBA, MCSA
> SQL Server Specialist
> Competitive Computing
> http://sqljunkies.com/WebLog/Roman
>
> "Josh" <Josh@.discussions.microsoft.com> wrote in message
> news:FA14A0DA-A2C4-4779-ADDC-49F79016EF6E@.microsoft.com...
>
>
|||These articles might help you get going with this:
How To Create and Remove a DSN in Visual Basic
http://support.microsoft.com/?id=171146
How To Programmatically Create a DSN for SQL Server with VB
http://support.microsoft.com/?id=184608
-Sue
On Sun, 27 Feb 2005 21:47:01 -0800, "Josh"
<Josh@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks for replying but that didn't work. I was thinking maybe there might
>be a way to create a vbscript to create the system dsns.
>
>"Roman Rehak" wrote:

Deploying Scripts to Multiple SQL Server Instances

Hello everyone,
I work for a software development company that has over 20 MSSQL2000
developer and support instances/environments in which I frequently
apply incremental updates to. The updates are SQL scripts given to me
by developers that, for example, add columns, drop and create updated
or new stored procedures or views. Sometimes a specific version of our
software may have 15-20 incremental updates to deploy. These updates
need to be applied somewhat immediatly to most of the data instances.
I've been running these manually for quite sometime via SQL Query
Analizer. In case you are wondering, as for applying permissions to
these objects, I've already got a script that sets permissions for
specified users for specified databases that i can run for each
instance in the click of a mouse.
What I am longing for is some sort of utility that may exist out there
for doing exactly what I am doing with these incremental updates, yet
more automated. We name our scripts specifically so that they can be
thrown into a single folder and they are already sorted by date and
type, so that a stored procedure update won't reference a table update
that has not been applied yet, etc.
Anyone have any directions they could point me in? I've tried
searching the newsgroups and net with no luck so far.This is a multi-part message in MIME format.
--=_NextPart_000_02AB_01C3AD1B.1AEF2BB0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Check out "master server" in the BOL.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Matt Norwood" <littletinymonkey@.hotmail.com> wrote in message
news:cc80cca0.0311171148.7e43bcff@.posting.google.com...
Hello everyone,
I work for a software development company that has over 20 MSSQL2000
developer and support instances/environments in which I frequently
apply incremental updates to. The updates are SQL scripts given to me
by developers that, for example, add columns, drop and create updated
or new stored procedures or views. Sometimes a specific version of our
software may have 15-20 incremental updates to deploy. These updates
need to be applied somewhat immediatly to most of the data instances.
I've been running these manually for quite sometime via SQL Query
Analizer. In case you are wondering, as for applying permissions to
these objects, I've already got a script that sets permissions for
specified users for specified databases that i can run for each
instance in the click of a mouse.
What I am longing for is some sort of utility that may exist out there
for doing exactly what I am doing with these incremental updates, yet
more automated. We name our scripts specifically so that they can be
thrown into a single folder and they are already sorted by date and
type, so that a stored procedure update won't reference a table update
that has not been applied yet, etc.
Anyone have any directions they could point me in? I've tried
searching the newsgroups and net with no luck so far.
--=_NextPart_000_02AB_01C3AD1B.1AEF2BB0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Check out "master server" in the =BOL.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Matt Norwood" wrote in message news:cc80cc=a0.0311171148.7e43bcff@.posting.google.com...Hello everyone,I work for a software development company that has over =20 MSSQL2000developer and support instances/environments in which I frequentlyapply incremental updates to. The updates are SQL scripts =given to meby developers that, for example, add columns, drop and create updatedor new stored procedures or views. Sometimes a specific =version of oursoftware may have 15-20 incremental updates to deploy. These updatesneed to be applied somewhat immediatly to most of the data instances.I've been running these manually for quite sometime via =SQL QueryAnalizer. In case you are wondering, as for applying =permissions tothese objects, I've already got a script that sets permissions forspecified users for specified databases that i can run for eachinstance in the click of a mouse.What I am longing for =is some sort of utility that may exist out therefor doing exactly what I am =doing with these incremental updates, yetmore automated. We name our =scripts specifically so that they can bethrown into a single folder and they =are already sorted by date andtype, so that a stored procedure update =won't reference a table updatethat has not been applied yet, =etc.Anyone have any directions they could point me in? I've triedsearching the newsgroups and net with no luck so far.

---

--=_NextPart_000_02AB_01C3AD1B.1AEF2BB0--|||have a read on www.dbghost.com it may be the help you need.
>--Original Message--
>Check out "master server" in the BOL.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Matt Norwood" <littletinymonkey@.hotmail.com> wrote in
message
>news:cc80cca0.0311171148.7e43bcff@.posting.google.com...
>Hello everyone,
>I work for a software development company that has over
20 MSSQL2000
>developer and support instances/environments in which I
frequently
>apply incremental updates to. The updates are SQL scripts
given to me
>by developers that, for example, add columns, drop and
create updated
>or new stored procedures or views. Sometimes a specific
version of our
>software may have 15-20 incremental updates to deploy.
These updates
>need to be applied somewhat immediatly to most of the
data instances.
>I've been running these manually for quite sometime via
SQL Query
>Analizer. In case you are wondering, as for applying
permissions to
>these objects, I've already got a script that sets
permissions for
>specified users for specified databases that i can run
for each
>instance in the click of a mouse.
>What I am longing for is some sort of utility that may
exist out there
>for doing exactly what I am doing with these incremental
updates, yet
>more automated. We name our scripts specifically so that
they can be
>thrown into a single folder and they are already sorted
by date and
>type, so that a stored procedure update won't reference a
table update
>that has not been applied yet, etc.
>Anyone have any directions they could point me in? I've
tried
>searching the newsgroups and net with no luck so far.
>|||thanks!

deploying reports database to multiple servers

Hi,
i have some 35 customers, all of whom need a copy of the reports i have designed
how do i quickly and simply roll out my reports. the databases are generally , but not
always given the same name, and the user credentials will be different
but the reports need to be the same.

is this possible?

Regards,

Russ

You have two basic options:

1.) Script out creating the reports in the database and provide the scripts to your users.

2.) Detach your database, and give it to your users. They can then attach the database, and configure RS to use that database via the config tool. If they do this, they have to be sure to leave the name of the databases that same.

|||

Cheers John,

im wondering how easy it is to do option 1. is there an example available that you know of?

Regards,

Russ

|||

Try rdlscripter, it is very easy to do this.

http://www.sqldbatips.com/showarticle.asp?ID=62

deploying reports database to multiple servers

Hi,
i have some 35 customers, all of whom need a copy of the reports i have designed
how do i quickly and simply roll out my reports. the databases are generally , but not
always given the same name, and the user credentials will be different
but the reports need to be the same.

is this possible?

Regards,

Russ

You have two basic options:

1.) Script out creating the reports in the database and provide the scripts to your users.

2.) Detach your database, and give it to your users. They can then attach the database, and configure RS to use that database via the config tool. If they do this, they have to be sure to leave the name of the databases that same.

|||

Cheers John,

im wondering how easy it is to do option 1. is there an example available that you know of?

Regards,

Russ

|||

Try rdlscripter, it is very easy to do this.

http://www.sqldbatips.com/showarticle.asp?ID=62

Deploying Reports

Hi All,
I am having a strange problem. I have created a shared data source and
multiple datasets for my reports. The multiple datasets are created to get
data for the different parameters in the report. The report works fine in
Visual Studio. But I don't see the data source listed and when I try to
deploy the report and run it on my webserver. I get the following error
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'CWPresales'.
(rsErrorOpeningConnection)
For more information about this error navigate to the report server on the
local server machine, or enable remote errors
Can anyone help me with this. I am confused on why I cannot see the
datasource listed in the solution explorer.
Thanks
ShriI am not sure what you mean by solution explorer? I use Report Manager for
this sort of thing. When you deploy the data sources get created BUT they
get created without credentials. You need to use Report Manager to view the
properties of the data source and set the credentials.
Data sources in RS2005 can either be in the same folder as your reports or
in a special folder called Data Sources. Just in case they are hidden in
List View then click on the Show Details link in Report Manager.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"formcreator" <formcreator@.discussions.microsoft.com> wrote in message
news:44C8FFDC-B340-43E5-8244-9AAEA63902D1@.microsoft.com...
> Hi All,
> I am having a strange problem. I have created a shared data source and
> multiple datasets for my reports. The multiple datasets are created to get
> data for the different parameters in the report. The report works fine in
> Visual Studio. But I don't see the data source listed and when I try to
> deploy the report and run it on my webserver. I get the following error
>
> An error has occurred during report processing. (rsProcessingAborted)
> Cannot create a connection to data source 'CWPresales'.
> (rsErrorOpeningConnection)
> For more information about this error navigate to the report server on the
> local server machine, or enable remote errors
> Can anyone help me with this. I am confused on why I cannot see the
> datasource listed in the solution explorer.
> Thanks
> Shri

Deploying report to multiple locations

We would like to be able to publish a report to more than 1 directory at the
same time. Is this or would this be possible? Currently, we deploy the base
report to a central location and create linked reports to all other locations.The benefit of using linked reports is that updating the report contents of
the original report will update the linked reports automatically. Note:
report parameters and data source settings won't get updated because they
are administrated separately for linked reports.
You may also want to check the following tool courtesy Jasper Smith (a SQL
Server MVP): http://www.sqldbatips.com/showarticle.asp?ID=62
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"clutch" <clutch@.discussions.microsoft.com> wrote in message
news:C16C6284-800A-41C1-822F-6C5FD518B727@.microsoft.com...
> We would like to be able to publish a report to more than 1 directory at
> the
> same time. Is this or would this be possible? Currently, we deploy the
> base
> report to a central location and create linked reports to all other
> locations.|||Thanks for the feedback. I was hoping you could answer one more thing.
Will it be possible to create a linked report, but have the ability to
select multiple locations at the same time? Rather than having to do each one
individually.
Thank you!
"Robert Bruckner [MSFT]" wrote:
> The benefit of using linked reports is that updating the report contents of
> the original report will update the linked reports automatically. Note:
> report parameters and data source settings won't get updated because they
> are administrated separately for linked reports.
> You may also want to check the following tool courtesy Jasper Smith (a SQL
> Server MVP): http://www.sqldbatips.com/showarticle.asp?ID=62
>
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "clutch" <clutch@.discussions.microsoft.com> wrote in message
> news:C16C6284-800A-41C1-822F-6C5FD518B727@.microsoft.com...
> > We would like to be able to publish a report to more than 1 directory at
> > the
> > same time. Is this or would this be possible? Currently, we deploy the
> > base
> > report to a central location and create linked reports to all other
> > locations.
>
>|||No, you can only create one linked report at a time in report manager. But
since creating a linked report is just a SOAP-call (look for
CreateLinkedReport in BOL), you could write a RSS script which creates
multiple linked reports in a sequence.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"clutch" <clutch@.discussions.microsoft.com> wrote in message
news:08A92FF1-5327-435D-9877-D6A0179F0866@.microsoft.com...
> Thanks for the feedback. I was hoping you could answer one more thing.
> Will it be possible to create a linked report, but have the ability to
> select multiple locations at the same time? Rather than having to do each
> one
> individually.
> Thank you!
> "Robert Bruckner [MSFT]" wrote:
>> The benefit of using linked reports is that updating the report contents
>> of
>> the original report will update the linked reports automatically. Note:
>> report parameters and data source settings won't get updated because they
>> are administrated separately for linked reports.
>> You may also want to check the following tool courtesy Jasper Smith (a
>> SQL
>> Server MVP): http://www.sqldbatips.com/showarticle.asp?ID=62
>>
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "clutch" <clutch@.discussions.microsoft.com> wrote in message
>> news:C16C6284-800A-41C1-822F-6C5FD518B727@.microsoft.com...
>> > We would like to be able to publish a report to more than 1 directory
>> > at
>> > the
>> > same time. Is this or would this be possible? Currently, we deploy the
>> > base
>> > report to a central location and create linked reports to all other
>> > locations.
>>

Deploying multiple web parts together

Hi,
My problem is that i want to add Web Part library project so that i
could add more than one web part into one solution.This will
facilitate me in deploying just one solution on Sharepoint server and
all webparts would be deployed.I searched through Google and found
that it was possible in vs.net 2003 through Web Part Template Library
project.But i could not find out any method of doing so in vs.net
2005.Could any one help..
I am using Sharepoint Server 2007 and vs.net 2005
Amit
Hi
"Amit" wrote:

> Hi,
> My problem is that i want to add Web Part library project so that i
> could add more than one web part into one solution.This will
> facilitate me in deploying just one solution on Sharepoint server and
> all webparts would be deployed.I searched through Google and found
> that it was possible in vs.net 2003 through Web Part Template Library
> project.But i could not find out any method of doing so in vs.net
> 2005.Could any one help..
> I am using Sharepoint Server 2007 and vs.net 2005
> Amit
>
This is not really a SQL Server question, you should post to a more
appropriate group such as microsoft.public.sharepoint.* or go to
http://www.microsoft.com/technet/community/newsgroups/server/sharepoint.mspx
and choose a group.
John

Deploying multiple web parts together

Hi,
My problem is that i want to add Web Part library project so that i
could add more than one web part into one solution.This will
facilitate me in deploying just one solution on Sharepoint server and
all webparts would be deployed.I searched through Google and found
that it was possible in vs.net 2003 through Web Part Template Library
project.But i could not find out any method of doing so in vs.net
2005.Could any one help..
I am using Sharepoint Server 2007 and vs.net 2005
AmitHi
"Amit" wrote:
> Hi,
> My problem is that i want to add Web Part library project so that i
> could add more than one web part into one solution.This will
> facilitate me in deploying just one solution on Sharepoint server and
> all webparts would be deployed.I searched through Google and found
> that it was possible in vs.net 2003 through Web Part Template Library
> project.But i could not find out any method of doing so in vs.net
> 2005.Could any one help..
> I am using Sharepoint Server 2007 and vs.net 2005
> Amit
>
This is not really a SQL Server question, you should post to a more
appropriate group such as microsoft.public.sharepoint.* or go to
http://www.microsoft.com/technet/community/newsgroups/server/sharepoint.mspx
and choose a group.
John

Deploying multiple web parts together

Hi,
My problem is that i want to add Web Part library project so that i
could add more than one web part into one solution.This will
facilitate me in deploying just one solution on Sharepoint server and
all webparts would be deployed.I searched through Google and found
that it was possible in vs.net 2003 through Web Part Template Library
project.But i could not find out any method of doing so in vs.net
2005.Could any one help..
I am using Sharepoint Server 2007 and vs.net 2005
AmitHi
"Amit" wrote:

> Hi,
> My problem is that i want to add Web Part library project so that i
> could add more than one web part into one solution.This will
> facilitate me in deploying just one solution on Sharepoint server and
> all webparts would be deployed.I searched through Google and found
> that it was possible in vs.net 2003 through Web Part Template Library
> project.But i could not find out any method of doing so in vs.net
> 2005.Could any one help..
> I am using Sharepoint Server 2007 and vs.net 2005
> Amit
>
This is not really a SQL Server question, you should post to a more
appropriate group such as microsoft.public.sharepoint.* or go to
http://www.microsoft.com/technet/co...sharepoint.mspx
and choose a group.
John

Deploying multiple projects

Is it possible to deploy multiple Analysis Services projects into one OLAP database, or you have to create all cubes into same project?

Thanks from answers!

Peter

Hello. The problem is that you will overwrite the existing cubes and other objects with the latest project that you deploy to an existing database. I think the answere is no.

Regards

Thomas Ivarsson

|||

Jeah, that is problem, but I thought there is trick, so unfortunately there isn't..

Thanks.

|||

If it would work you could have problems with name collisions like dimensions with the same name. I also tried copy paste from one project in BIDS to another, still without any luck.

Regards

Thomas Ivarsson

Deploying multiple files through a package

Hi

I am working on on SQL Server Integration Services and facing few problems in it.

Actually am supposed to create a package that would automatically pick excel spreadsheets with a specific format and import it onto the SQL server.
(Lets say , there is a company named AB and they have got various products named CD,EF and GH and each product has its own spreadsheet in which its monthly sales(JAN, FEB,...NOV, DEC) is given. So i have to build a generic package for each product (lets say CD) so that i don't have to import every spreadsheet seperately for each month.
To summarize i just have to build a package where i can deploy the various spreadshhets again and agian instead of building a package for each and every month spreadsheet seperately.

I have tried and used lots of combinations like Loop
conatiners etc. but still am not able to find a solution to it.

Please help me out on this.

If the metadata (i.e. Number of columns, column names, column types) of each file is different then you cannot load everything using the same data-flow. You need a seperate data-flow for each one.

You can still put everything into one package. You need to decide, at execution-time, which data-flow(s) to execute based on which file you want to process. In other words, you need conditional workflow: http://www.sqlis.com/default.aspx?306

-Jamie

|||

But the format of all the monthly spreadsheets are the same. So still i have to cosider conditional flow or it can be done through any other way also.

Cheers

|||

Oh OK, well you're all right then. You just need to set the name of the file that you want to process dynamically. You do this by putting a property expression on the Excel Connection Manager's ConnectionString property.

-Jamie

|||

Hi

I am really sorry to bother again but actually ,as i am new to these services, can you please guide me how to do it or may be direct me to some useful article.

Thanks a lot .Its been really helpful though.

Cheers

|||

Sure, this: http://blogs.conchango.com/jamiethomson/archive/2005/05/30/1489.aspx provides all that you need to know.

There's probably more in there than you need but you can pick out the bits that are important. Make sure you download the demo which will show you how all this works.

-Jamie

|||

Thanks a lot. I really appreciate it. Its working fine now.

Actually i have twisted the previous question a bit. I don't know why but i am just keen to learn whatever comes my way.

Lets say we have a database table(eg XYZ) with certain attributes (A,B,C,D,E,F) and now we want to build a package which would take excel file(eg XUV) as a source and export the data onto the table which has already been created in the database(i.e XYZ). But in the excel file we only have few attributes that match the destination column(lets say A,C,F). Now in order to get it working we have to map the columns manually. Then perform the normal procedures and it would be loaded.

This package would deploy only one source file(i.e XUV). Actually what i was curious about is that if there are hundreds of excel source files(with the same attributes like the former excel source XUV) waiting to be loaded then am i supposed to do mapping for each and every file.

Thanks in advance.

|||

Not quite sure I fully understand what you're saying but the important thing is you have to build a data-flow for each type of source file. By "type" I mean all files that have the same metadata are of the same type.

-Jamie

|||

Hi

Actually am still facing some problem with my last query.

There is no connection manager in the property listings in the expressions.

|||

Where are you looking? What object are you looking at the properties of?

You have to change the ConnectionString property of the connection manager.

-Jamie

|||Really sorry was a mistake.

Deploying cube changes to multiple sites

Hi,

How does one deploy cube changes (say adding a new field into a dimension) into multiple customer databases without overwriting their Kpis?

We have a SSAS application which will snap into our existing application and allow customers to define Environmental, Health & Safety Kpis. Each customer will have different Kpis, so how do we deploy incremental updates, without overwriting those Kpis?

I know we can download a customers SSAS database into a new VS project, and copy the Kpis into an XMLA deployment script, but that is a lot of work for each customer.

What I am hoping to find is something like a SQL Script that makes the schema changes, but does not touch the data (Kpis etc.). Of course, my SQL knowledge has only got me into trouble in SSAS, so who knows.

Thanks,

--Phil

Probably the best approach would be to write a script which uses DISCOVER_XML_METADATA to retrieve the cube's metadata, and then changes the XML my injecting your KPIs and then sending this XMLA script inside <Alter> command.

Deploying a database to multiple servers - smooth sailing?

Hello,
I need to copy a database to multiple servers, all of this using SQL Server
2000. -- this will enable the testing of an application with identical
databases. I'm concerned here with the original installation of the database
on the target servers and subsequent updates using restoration of the
database from the master server
I'm contemplating having the master database on one server and using data
management objects to make a database backup on that machine. That backed-up
database would be placed in a share with some scripts that would perform the
restore on the target servers - to be initiated by the users.
This works well on my master server but can I expect a smooth distribution
using this approach or is there a better approach. I like this method as
it's simple. The target servers will all be SQL Server 2000, possibly with
different service patches. The target servers would has the same local
administrators account.
Is there anything internal to the back up or the target server that would
prevent this operation? There's no dependencies such as a database can only
be restored to a server it was backed up on, is there?
TIA,
Rod
> This works well on my master server but can I expect a smooth distribution
> using this approach or is there a better approach. I like this method as
> it's simple. The target servers will all be SQL Server 2000, possibly
> with
> different service patches. The target servers would has the same local
> administrators account.
Your servers should all be at the same patch level. Occassionally, there
are differences in the structure of the backup that are not backward
compatible.

> Is there anything internal to the back up or the target server that would
> prevent this operation? There's no dependencies such as a database can
> only
> be restored to a server it was backed up on, is there?
No.
|||Thank you
"Scott Morris" wrote:

> Your servers should all be at the same patch level. Occassionally, there
> are differences in the structure of the backup that are not backward
> compatible.
>
> No.
>
>

Deploying a database to multiple servers - smooth sailing?

Hello,
I need to copy a database to multiple servers, all of this using SQL Server
2000. -- this will enable the testing of an application with identical
databases. I'm concerned here with the original installation of the database
on the target servers and subsequent updates using restoration of the
database from the master server
I'm contemplating having the master database on one server and using data
management objects to make a database backup on that machine. That backed-up
database would be placed in a share with some scripts that would perform the
restore on the target servers - to be initiated by the users.
This works well on my master server but can I expect a smooth distribution
using this approach or is there a better approach. I like this method as
it's simple. The target servers will all be SQL Server 2000, possibly with
different service patches. The target servers would has the same local
administrators account.
Is there anything internal to the back up or the target server that would
prevent this operation? There's no dependencies such as a database can only
be restored to a server it was backed up on, is there?
TIA,
Rod> This works well on my master server but can I expect a smooth distribution
> using this approach or is there a better approach. I like this method as
> it's simple. The target servers will all be SQL Server 2000, possibly
> with
> different service patches. The target servers would has the same local
> administrators account.
Your servers should all be at the same patch level. Occassionally, there
are differences in the structure of the backup that are not backward
compatible.
> Is there anything internal to the back up or the target server that would
> prevent this operation? There's no dependencies such as a database can
> only
> be restored to a server it was backed up on, is there?
No.|||Thank you
"Scott Morris" wrote:
> > This works well on my master server but can I expect a smooth distribution
> > using this approach or is there a better approach. I like this method as
> > it's simple. The target servers will all be SQL Server 2000, possibly
> > with
> > different service patches. The target servers would has the same local
> > administrators account.
> Your servers should all be at the same patch level. Occassionally, there
> are differences in the structure of the backup that are not backward
> compatible.
> > Is there anything internal to the back up or the target server that would
> > prevent this operation? There's no dependencies such as a database can
> > only
> > be restored to a server it was backed up on, is there?
> No.
>
>

Deploying a database to multiple servers - smooth sailing?

Hello,
I need to copy a database to multiple servers, all of this using SQL Server
2000. -- this will enable the testing of an application with identical
databases. I'm concerned here with the original installation of the databas
e
on the target servers and subsequent updates using restoration of the
database from the master server
I'm contemplating having the master database on one server and using data
management objects to make a database backup on that machine. That backed-u
p
database would be placed in a share with some scripts that would perform the
restore on the target servers - to be initiated by the users.
This works well on my master server but can I expect a smooth distribution
using this approach or is there a better approach. I like this method as
it's simple. The target servers will all be SQL Server 2000, possibly with
different service patches. The target servers would has the same local
administrators account.
Is there anything internal to the back up or the target server that would
prevent this operation? There's no dependencies such as a database can only
be restored to a server it was backed up on, is there?
TIA,
Rod> This works well on my master server but can I expect a smooth distribution
> using this approach or is there a better approach. I like this method as
> it's simple. The target servers will all be SQL Server 2000, possibly
> with
> different service patches. The target servers would has the same local
> administrators account.
Your servers should all be at the same patch level. Occassionally, there
are differences in the structure of the backup that are not backward
compatible.

> Is there anything internal to the back up or the target server that would
> prevent this operation? There's no dependencies such as a database can
> only
> be restored to a server it was backed up on, is there?
No.|||Thank you
"Scott Morris" wrote:

> Your servers should all be at the same patch level. Occassionally, there
> are differences in the structure of the backup that are not backward
> compatible.
>
> No.
>
>

Deployed Report Fials with Multiple Pages

Using SQL RS 2000
My report runs fine in the designer but when deployed to the server it will
only display the first page. When move to page 2 or the last page it gives
the error:
"An unexpected error occurred in Report Processing. (rsUnexpectedError) "
Also, the report calls a subreport and if I remove the subreport it works
fine.
Any ideas?
ThanksI am having a very similar problem in RS 2005.
When I include a subreport, the main report will only display the first page
(correctly) but gives me the same error when navigating to another page.
This is occurring on one particular report only so far - a reasonably
complex report.
I have raised a technical support issue with Microsoft but they have not yet
been able to solve it.
Colin
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:O$Ek5eGgGHA.3996@.TK2MSFTNGP03.phx.gbl...
> Using SQL RS 2000
> My report runs fine in the designer but when deployed to the server it
> will only display the first page. When move to page 2 or the last page it
> gives the error:
> "An unexpected error occurred in Report Processing. (rsUnexpectedError) "
> Also, the report calls a subreport and if I remove the subreport it works
> fine.
> Any ideas?
> Thanks
>

Deploy from visual studio doesn't work

Hello, I created a new Rs server, but when I try to deploy from visual studio, it asks me for username and password multiple times and never deploys.

I am an administrator on the RS server, even on the report folders.

I can access via IE to the report manager.

When you access the Report Manager or Report Server (http://servername/ReportServer) via IE, does IE ask you to authenticate? In addition, what deployment settings have been specified in the BIDS project properties?|||

I have the same problem. I can download reports with IE. IE don't ask me for my login or password (it uses the windows session login).

But when I will deploy reports with Visual Studio, the software ask me for my login and password.

I can fill all the possibility (domain\login, login, upper case, lower case, ...) the studio ask me always for my login.

Cant' visual studio use the windows session for login?

Or must I have a particular role?

Regards,

|||VS.NET shouldn't ask you for user id and password. Probably, there is something wrong with your domain setup. What server ahve you specified on the project properties? Did you use the server name or IP address? What happens when you deploy to localhost?|||

I can deploy on a local server.

I use the server name.

The server and my computer (on which is install VS.NET) are on the same domain.

|||Then, for some reason, the server is rejecting the Windows integrated security handshake. Using a trace listener, such as tcpTrace, will help you see the error page returned from the server. I am suprised though that this doesn't happen when you use IE to browse the server, e.g. http://server/reports. If IE doesn't prompt you then I am out of options.|||The strange this is that I disabled the proxy settings on Internet Explorer and now I can deploy from visual studio.|||You may try putting that server name in the IE trusted zone.|||

I will first say that I use SQL Server for the first time.

I have understand my problem and logicaly resolv it. There is two URL, one for the Web site (In my case http://server/Reports) and an other one for the web service (in my case http://server/ReportManager).

So I have tried to deploy report using the Web site URL. When I use the web service URL all it's done.

Thank you every one for our answer.

|||

I'm pretty new to Reporting Services and Sharepoint. I am having the same problem, with the Reporting Services login box displaying continuosly during deploy.

Although I have read this thread, and made the changes (or attempted but did it incorrectly) I am still having difficulty. I am running RS in integrated mode (with Sharepoint).

I can browse out to Report Services (IE6) http://servername:8080/reportserver. There is a directory there that matches the server name. Browsing into that folder I see directories for each of the sharepoint subsites including one called "MYReports" which is where I set up Reporting Services. When I browse into that folder there is one for Report Services Reports (which is the library I set up to hold all the reports). When I browse into that folder I see the reports I have gotten into Sharepoint by uploading them from Sharepoint. At this point the path shown (on the screen - not in the website address portion - that one has lots of strange characters in it) is http://servername/MyReports/Report Services Reports.

The url to get the main Sharepoint site is: http://servername/default.aspx

When I set the TargetServerUrl to http://servername:8080/reportserver - it tells me (essentially) that that isn't supported in integrated with sharepoint mode. When I use http://servername:8080 I get the repeated login prompts.

I have the TargetReportFolder set to: http://servername:8080/MyReports/Report Services Reports/ (just like when I browse there)

the targetDataSourceFolder is set to: http://servername:8080/MyReports/Report Services Reports/Data Sources (a folder to store all the data sources in).

Any help with what I am doing wrong is appreciated.

Shirley

|||

In SharePoint Integrated mode you need TargetReportFolder and TargetDataSourceFolder to point directly to SharePoint Document Library URLs.

You may want to use the following approach:

TargetServerURL http://servername/

TargetReportFolder http://servername/MyReports/Report Services Reports/

TargetDataSourceFolder http://servername/MyReports/Report Services Reports/Data Sources

Additionally, does info in the following thread help?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1156609&SiteID=1

HTH

Prash

Deploy from visual studio doesn't work

Hello, I created a new Rs server, but when I try to deploy from visual studio, it asks me for username and password multiple times and never deploys.

I am an administrator on the RS server, even on the report folders.

I can access via IE to the report manager.

When you access the Report Manager or Report Server (http://servername/ReportServer) via IE, does IE ask you to authenticate? In addition, what deployment settings have been specified in the BIDS project properties?|||

I have the same problem. I can download reports with IE. IE don't ask me for my login or password (it uses the windows session login).

But when I will deploy reports with Visual Studio, the software ask me for my login and password.

I can fill all the possibility (domain\login, login, upper case, lower case, ...) the studio ask me always for my login.

Cant' visual studio use the windows session for login?

Or must I have a particular role?

Regards,

|||VS.NET shouldn't ask you for user id and password. Probably, there is something wrong with your domain setup. What server ahve you specified on the project properties? Did you use the server name or IP address? What happens when you deploy to localhost?|||

I can deploy on a local server.

I use the server name.

The server and my computer (on which is install VS.NET) are on the same domain.

|||Then, for some reason, the server is rejecting the Windows integrated security handshake. Using a trace listener, such as tcpTrace, will help you see the error page returned from the server. I am suprised though that this doesn't happen when you use IE to browse the server, e.g. http://server/reports. If IE doesn't prompt you then I am out of options.|||The strange this is that I disabled the proxy settings on Internet Explorer and now I can deploy from visual studio.|||You may try putting that server name in the IE trusted zone.|||

I will first say that I use SQL Server for the first time.

I have understand my problem and logicaly resolv it. There is two URL, one for the Web site (In my case http://server/Reports) and an other one for the web service (in my case http://server/ReportManager).

So I have tried to deploy report using the Web site URL. When I use the web service URL all it's done.

Thank you every one for our answer.

|||

I'm pretty new to Reporting Services and Sharepoint. I am having the same problem, with the Reporting Services login box displaying continuosly during deploy.

Although I have read this thread, and made the changes (or attempted but did it incorrectly) I am still having difficulty. I am running RS in integrated mode (with Sharepoint).

I can browse out to Report Services (IE6) http://servername:8080/reportserver. There is a directory there that matches the server name. Browsing into that folder I see directories for each of the sharepoint subsites including one called "MYReports" which is where I set up Reporting Services. When I browse into that folder there is one for Report Services Reports (which is the library I set up to hold all the reports). When I browse into that folder I see the reports I have gotten into Sharepoint by uploading them from Sharepoint. At this point the path shown (on the screen - not in the website address portion - that one has lots of strange characters in it) is http://servername/MyReports/Report Services Reports.

The url to get the main Sharepoint site is: http://servername/default.aspx

When I set the TargetServerUrl to http://servername:8080/reportserver - it tells me (essentially) that that isn't supported in integrated with sharepoint mode. When I use http://servername:8080 I get the repeated login prompts.

I have the TargetReportFolder set to: http://servername:8080/MyReports/Report Services Reports/ (just like when I browse there)

the targetDataSourceFolder is set to: http://servername:8080/MyReports/Report Services Reports/Data Sources (a folder to store all the data sources in).

Any help with what I am doing wrong is appreciated.

Shirley

|||

In SharePoint Integrated mode you need TargetReportFolder and TargetDataSourceFolder to point directly to SharePoint Document Library URLs.

You may want to use the following approach:

TargetServerURL http://servername/

TargetReportFolder http://servername/MyReports/Report Services Reports/

TargetDataSourceFolder http://servername/MyReports/Report Services Reports/Data Sources

Additionally, does info in the following thread help?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1156609&SiteID=1

HTH

Prash

Deploy from visual studio doesnt work

Hello, I created a new Rs server, but when I try to deploy from visual studio, it asks me for username and password multiple times and never deploys.

I am an administrator on the RS server, even on the report folders.

I can access via IE to the report manager.

When you access the Report Manager or Report Server (http://servername/ReportServer) via IE, does IE ask you to authenticate? In addition, what deployment settings have been specified in the BIDS project properties?|||

I have the same problem. I can download reports with IE. IE don't ask me for my login or password (it uses the windows session login).

But when I will deploy reports with Visual Studio, the software ask me for my login and password.

I can fill all the possibility (domain\login, login, upper case, lower case, ...) the studio ask me always for my login.

Cant' visual studio use the windows session for login?

Or must I have a particular role?

Regards,

|||VS.NET shouldn't ask you for user id and password. Probably, there is something wrong with your domain setup. What server ahve you specified on the project properties? Did you use the server name or IP address? What happens when you deploy to localhost?|||

I can deploy on a local server.

I use the server name.

The server and my computer (on which is install VS.NET) are on the same domain.

|||Then, for some reason, the server is rejecting the Windows integrated security handshake. Using a trace listener, such as tcpTrace, will help you see the error page returned from the server. I am suprised though that this doesn't happen when you use IE to browse the server, e.g. http://server/reports. If IE doesn't prompt you then I am out of options.|||The strange this is that I disabled the proxy settings on Internet Explorer and now I can deploy from visual studio.|||You may try putting that server name in the IE trusted zone.|||

I will first say that I use SQL Server for the first time.

I have understand my problem and logicaly resolv it. There is two URL, one for the Web site (In my case http://server/Reports) and an other one for the web service (in my case http://server/ReportManager).

So I have tried to deploy report using the Web site URL. When I use the web service URL all it's done.

Thank you every one for our answer.

|||

I'm pretty new to Reporting Services and Sharepoint. I am having the same problem, with the Reporting Services login box displaying continuosly during deploy.

Although I have read this thread, and made the changes (or attempted but did it incorrectly) I am still having difficulty. I am running RS in integrated mode (with Sharepoint).

I can browse out to Report Services (IE6) http://servername:8080/reportserver. There is a directory there that matches the server name. Browsing into that folder I see directories for each of the sharepoint subsites including one called "MYReports" which is where I set up Reporting Services. When I browse into that folder there is one for Report Services Reports (which is the library I set up to hold all the reports). When I browse into that folder I see the reports I have gotten into Sharepoint by uploading them from Sharepoint. At this point the path shown (on the screen - not in the website address portion - that one has lots of strange characters in it) is http://servername/MyReports/Report Services Reports.

The url to get the main Sharepoint site is: http://servername/default.aspx

When I set the TargetServerUrl to http://servername:8080/reportserver - it tells me (essentially) that that isn't supported in integrated with sharepoint mode. When I use http://servername:8080 I get the repeated login prompts.

I have the TargetReportFolder set to: http://servername:8080/MyReports/Report Services Reports/ (just like when I browse there)

the targetDataSourceFolder is set to: http://servername:8080/MyReports/Report Services Reports/Data Sources (a folder to store all the data sources in).

Any help with what I am doing wrong is appreciated.

Shirley

|||

In SharePoint Integrated mode you need TargetReportFolder and TargetDataSourceFolder to point directly to SharePoint Document Library URLs.

You may want to use the following approach:

TargetServerURL http://servername/

TargetReportFolder http://servername/MyReports/Report Services Reports/

TargetDataSourceFolder http://servername/MyReports/Report Services Reports/Data Sources

Additionally, does info in the following thread help?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1156609&SiteID=1

HTH

Prash