Datasource Change HELP!

I am working in a dev environment. My report uses 20 different datasets but
they all point to the same datasource. I am moving from Dev to a UAT
environment which points to a different DB. Is there a way to change the
datasource for all 20 datasets from DevDB to UATDB at once or am I stuck with
going in and changing all the datasources for each of the datasets one by one?
Thanks in advance
OSTry opening XML <F7> and doing a find/replace on the DB name. This is
assuming that the new datasource is already defined in the solution and the
table names are the same.
"OriginalStealth" wrote:
> I am working in a dev environment. My report uses 20 different datasets but
> they all point to the same datasource. I am moving from Dev to a UAT
> environment which points to a different DB. Is there a way to change the
> datasource for all 20 datasets from DevDB to UATDB at once or am I stuck with
> going in and changing all the datasources for each of the datasets one by one?
> Thanks in advance
> OS|||Hi,
Have you used as a "shared resources" for all your reports? then it should
not be a problem since you need to just change the database and user
name/password etc.. details at your datasource and you will be up and
running.
Amarnath
"OriginalStealth" wrote:
> I am working in a dev environment. My report uses 20 different datasets but
> they all point to the same datasource. I am moving from Dev to a UAT
> environment which points to a different DB. Is there a way to change the
> datasource for all 20 datasets from DevDB to UATDB at once or am I stuck with
> going in and changing all the datasources for each of the datasets one by one?
> Thanks in advance
> OS|||William,
This really helped me! Thanx a bunch!
OS
"William" wrote:
> Try opening XML <F7> and doing a find/replace on the DB name. This is
> assuming that the new datasource is already defined in the solution and the
> table names are the same.
> "OriginalStealth" wrote:
> > I am working in a dev environment. My report uses 20 different datasets but
> > they all point to the same datasource. I am moving from Dev to a UAT
> > environment which points to a different DB. Is there a way to change the
> > datasource for all 20 datasets from DevDB to UATDB at once or am I stuck with
> > going in and changing all the datasources for each of the datasets one by one?
> >
> > Thanks in advance
> > OS|||How can we programatically change the DB name?
Or
Change them dynamically in the URL?
Prad
Amarnath wrote:
> Hi,
> Have you used as a "shared resources" for all your reports? then it should
> not be a problem since you need to just change the database and user
> name/password etc.. details at your datasource and you will be up and
> running.
> Amarnath
>
> "OriginalStealth" wrote:
> > I am working in a dev environment. My report uses 20 different datasets but
> > they all point to the same datasource. I am moving from Dev to a UAT
> > environment which points to a different DB. Is there a way to change the
> > datasource for all 20 datasets from DevDB to UATDB at once or am I stuck with
> > going in and changing all the datasources for each of the datasets one by one?
> >
> > Thanks in advance
> > OS|||What you should be doing is to have a shared datasource. The first time you
deploy the datasource is deployed too. Then you go to the Report Manager and
change the datasource to point to the production data. From then on when you
deploy the default is that the datasource is not overwritten so you only
need to do this once.
So there should only be one datasource that you have to change once in this
process.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<pradeepmm@.gmail.com> wrote in message
news:1144344167.425837.308400@.u72g2000cwu.googlegroups.com...
> How can we programatically change the DB name?
> Or
> Change them dynamically in the URL?
> Prad
> Amarnath wrote:
>> Hi,
>> Have you used as a "shared resources" for all your reports? then it
>> should
>> not be a problem since you need to just change the database and user
>> name/password etc.. details at your datasource and you will be up and
>> running.
>> Amarnath
>>
>> "OriginalStealth" wrote:
>> > I am working in a dev environment. My report uses 20 different
>> > datasets but
>> > they all point to the same datasource. I am moving from Dev to a UAT
>> > environment which points to a different DB. Is there a way to change
>> > the
>> > datasource for all 20 datasets from DevDB to UATDB at once or am I
>> > stuck with
>> > going in and changing all the datasources for each of the datasets one
>> > by one?
>> >
>> > Thanks in advance
>> > OS
>|||Is there any way to change the datasource for a report on an
execution-by-exection basis?
I have a situation where there are a large numbre of databases and
users need to be able to execute a single report against any of the
databases.
Is there a way to supply the database connection string to the report
when it is executed?sql

Datafiles

I am having trouble moving BLOBs (text fields).
I have a 200Gb database in a single data file and want to split it into 4
even sized files (I have my reasons). To achieve this I plan on adding 3 data
files to the Primary filegroup. From there I will issue a DBCC dbreindex on
all my tables, which effectively recreates the index (or table if its a
clustered index) across all datafiles and I get my even distribution.
My plan comes unstuck when I encounter text fields and other BLOBs (as the
clustered index just contains pointers doesnt it'). How can I move them? Do
I have to do a select into or bcp? Can I just issue a shrink file on the
oringal large file or does that just push pages from the end of that data
file to the beginning and not to other datafiles'
Any suggestions would be great, I don't really want to create another
filegroup just yet.
Thanks :)"Malarb" <Malarb@.discussions.microsoft.com> wrote in message
news:BA0A6D10-4377-4247-A2A3-195FB60B308B@.microsoft.com...
>I am having trouble moving BLOBs (text fields).
> I have a 200Gb database in a single data file and want to split it into 4
> even sized files (I have my reasons). To achieve this I plan on adding 3
> data
> files to the Primary filegroup. From there I will issue a DBCC dbreindex
> on
> all my tables, which effectively recreates the index (or table if its a
> clustered index) across all datafiles and I get my even distribution.
> My plan comes unstuck when I encounter text fields and other BLOBs (as the
> clustered index just contains pointers doesnt it'). How can I move them?
> Do
> I have to do a select into or bcp? Can I just issue a shrink file on the
> oringal large file or does that just push pages from the end of that data
> file to the beginning and not to other datafiles'
> Any suggestions would be great, I don't really want to create another
> filegroup just yet.
I would create a new filegroup with the desired number and layout of files,
mark it as primary, and move all your objects there. Then drop the old
filegroup.
David|||You will need to create a new filegroup so you can create a new table on
this filegroup. Copy the old data to the new table. Drop the old table.
Rename the new tb to the old name. And then shrink the db to reclaim the
space.
--
-oj
"Malarb" <Malarb@.discussions.microsoft.com> wrote in message
news:BA0A6D10-4377-4247-A2A3-195FB60B308B@.microsoft.com...
>I am having trouble moving BLOBs (text fields).
> I have a 200Gb database in a single data file and want to split it into 4
> even sized files (I have my reasons). To achieve this I plan on adding 3
> data
> files to the Primary filegroup. From there I will issue a DBCC dbreindex
> on
> all my tables, which effectively recreates the index (or table if its a
> clustered index) across all datafiles and I get my even distribution.
> My plan comes unstuck when I encounter text fields and other BLOBs (as the
> clustered index just contains pointers doesnt it'). How can I move them?
> Do
> I have to do a select into or bcp? Can I just issue a shrink file on the
> oringal large file or does that just push pages from the end of that data
> file to the beginning and not to other datafiles'
> Any suggestions would be great, I don't really want to create another
> filegroup just yet.
> Thanks :)|||Hi,
I have the same problem, but i´m not a dba expert. How is sintaxe to create
a new table and accurate the it´s will in the new files group.
Could use the following command to creat a copy table'
select * into COPY_TABLE
from SOURCE_TABLE
WHERE 1=0
Thanks
"oj" wrote:
> You will need to create a new filegroup so you can create a new table on
> this filegroup. Copy the old data to the new table. Drop the old table.
> Rename the new tb to the old name. And then shrink the db to reclaim the
> space.
> --
> -oj
>
> "Malarb" <Malarb@.discussions.microsoft.com> wrote in message
> news:BA0A6D10-4377-4247-A2A3-195FB60B308B@.microsoft.com...
> >I am having trouble moving BLOBs (text fields).
> >
> > I have a 200Gb database in a single data file and want to split it into 4
> > even sized files (I have my reasons). To achieve this I plan on adding 3
> > data
> > files to the Primary filegroup. From there I will issue a DBCC dbreindex
> > on
> > all my tables, which effectively recreates the index (or table if its a
> > clustered index) across all datafiles and I get my even distribution.
> >
> > My plan comes unstuck when I encounter text fields and other BLOBs (as the
> > clustered index just contains pointers doesnt it'). How can I move them?
> > Do
> > I have to do a select into or bcp? Can I just issue a shrink file on the
> > oringal large file or does that just push pages from the end of that data
> > file to the beginning and not to other datafiles'
> >
> > Any suggestions would be great, I don't really want to create another
> > filegroup just yet.
> >
> > Thanks :)
>
>|||select/into does not allow you to specify the filegroup. You will need to
create a new table then do update.
e.g.
create table newtab(...) on filegroup
update newtab
set ...= old...
from newtab,old
where newtab.pk=old.pk
-oj
"Fabio Rebelo" <FabioRebelo@.discussions.microsoft.com> wrote in message
news:07896DF0-B18B-4ACB-A854-EFBEE387E32D@.microsoft.com...
> Hi,
> I have the same problem, but i´m not a dba expert. How is sintaxe to
> create
> a new table and accurate the it´s will in the new files group.
> Could use the following command to creat a copy table'
> select * into COPY_TABLE
> from SOURCE_TABLE
> WHERE 1=0
> Thanks
>
> "oj" wrote:
>> You will need to create a new filegroup so you can create a new table on
>> this filegroup. Copy the old data to the new table. Drop the old table.
>> Rename the new tb to the old name. And then shrink the db to reclaim the
>> space.
>> --
>> -oj
>>
>> "Malarb" <Malarb@.discussions.microsoft.com> wrote in message
>> news:BA0A6D10-4377-4247-A2A3-195FB60B308B@.microsoft.com...
>> >I am having trouble moving BLOBs (text fields).
>> >
>> > I have a 200Gb database in a single data file and want to split it into
>> > 4
>> > even sized files (I have my reasons). To achieve this I plan on adding
>> > 3
>> > data
>> > files to the Primary filegroup. From there I will issue a DBCC
>> > dbreindex
>> > on
>> > all my tables, which effectively recreates the index (or table if its a
>> > clustered index) across all datafiles and I get my even distribution.
>> >
>> > My plan comes unstuck when I encounter text fields and other BLOBs (as
>> > the
>> > clustered index just contains pointers doesnt it'). How can I move
>> > them?
>> > Do
>> > I have to do a select into or bcp? Can I just issue a shrink file on
>> > the
>> > oringal large file or does that just push pages from the end of that
>> > data
>> > file to the beginning and not to other datafiles'
>> >
>> > Any suggestions would be great, I don't really want to create another
>> > filegroup just yet.
>> >
>> > Thanks :)
>>

Datafiles

I am having trouble moving BLOBs (text fields).
I have a 200Gb database in a single data file and want to split it into 4
even sized files (I have my reasons). To achieve this I plan on adding 3 dat
a
files to the Primary filegroup. From there I will issue a DBCC dbreindex on
all my tables, which effectively recreates the index (or table if its a
clustered index) across all datafiles and I get my even distribution.
My plan comes unstuck when I encounter text fields and other BLOBs (as the
clustered index just contains pointers doesnt it'). How can I move them? Do
I have to do a select into or bcp? Can I just issue a shrink file on the
oringal large file or does that just push pages from the end of that data
file to the beginning and not to other datafiles'
Any suggestions would be great, I don't really want to create another
filegroup just yet.
Thanks "Malarb" <Malarb@.discussions.microsoft.com> wrote in message
news:BA0A6D10-4377-4247-A2A3-195FB60B308B@.microsoft.com...
>I am having trouble moving BLOBs (text fields).
> I have a 200Gb database in a single data file and want to split it into 4
> even sized files (I have my reasons). To achieve this I plan on adding 3
> data
> files to the Primary filegroup. From there I will issue a DBCC dbreindex
> on
> all my tables, which effectively recreates the index (or table if its a
> clustered index) across all datafiles and I get my even distribution.
> My plan comes unstuck when I encounter text fields and other BLOBs (as the
> clustered index just contains pointers doesnt it'). How can I move them?
> Do
> I have to do a select into or bcp? Can I just issue a shrink file on the
> oringal large file or does that just push pages from the end of that data
> file to the beginning and not to other datafiles'
> Any suggestions would be great, I don't really want to create another
> filegroup just yet.
I would create a new filegroup with the desired number and layout of files,
mark it as primary, and move all your objects there. Then drop the old
filegroup.
David|||You will need to create a new filegroup so you can create a new table on
this filegroup. Copy the old data to the new table. Drop the old table.
Rename the new tb to the old name. And then shrink the db to reclaim the
space.
-oj
"Malarb" <Malarb@.discussions.microsoft.com> wrote in message
news:BA0A6D10-4377-4247-A2A3-195FB60B308B@.microsoft.com...
>I am having trouble moving BLOBs (text fields).
> I have a 200Gb database in a single data file and want to split it into 4
> even sized files (I have my reasons). To achieve this I plan on adding 3
> data
> files to the Primary filegroup. From there I will issue a DBCC dbreindex
> on
> all my tables, which effectively recreates the index (or table if its a
> clustered index) across all datafiles and I get my even distribution.
> My plan comes unstuck when I encounter text fields and other BLOBs (as the
> clustered index just contains pointers doesnt it'). How can I move them?
> Do
> I have to do a select into or bcp? Can I just issue a shrink file on the
> oringal large file or does that just push pages from the end of that data
> file to the beginning and not to other datafiles'
> Any suggestions would be great, I don't really want to create another
> filegroup just yet.
> Thanks |||Hi,
I have the same problem, but i′m not a dba expert. How is sintaxe to create
a new table and accurate the it′s will in the new files group.
Could use the following command to creat a copy table'
select * into COPY_TABLE
from SOURCE_TABLE
WHERE 1=0
Thanks
"oj" wrote:

> You will need to create a new filegroup so you can create a new table on
> this filegroup. Copy the old data to the new table. Drop the old table.
> Rename the new tb to the old name. And then shrink the db to reclaim the
> space.
> --
> -oj
>
> "Malarb" <Malarb@.discussions.microsoft.com> wrote in message
> news:BA0A6D10-4377-4247-A2A3-195FB60B308B@.microsoft.com...
>
>|||select/into does not allow you to specify the filegroup. You will need to
create a new table then do update.
e.g.
create table newtab(...) on filegroup
update newtab
set ...= old...
from newtab,old
where newtab.pk=old.pk
-oj
"Fabio Rebelo" <FabioRebelo@.discussions.microsoft.com> wrote in message
news:07896DF0-B18B-4ACB-A854-EFBEE387E32D@.microsoft.com...[vbcol=seagreen]
> Hi,
> I have the same problem, but im not a dba expert. How is sintaxe to
> create
> a new table and accurate the its will in the new files group.
> Could use the following command to creat a copy table'
> select * into COPY_TABLE
> from SOURCE_TABLE
> WHERE 1=0
> Thanks
>
> "oj" wrote:
>

Datacenter move.. Plan for SQL Servers

We will be moving to a new datacenter and wondering if there was any article
out there to help me get started on the planning required..
Thanks
This should be no difference than planning to move an instance/database to
another server, assuming you are going to a new server in the new datacenter.
Linchi
"Hassan" wrote:

> We will be moving to a new datacenter and wondering if there was any article
> out there to help me get started on the planning required..
> Thanks
>
>
|||Take a look into the article:-
http://www.forsythe.com/infrastrat&man/dcr/documents/MovingTheDataCenter.pdf
http://www.shunra.com/articles.aspx?articleId=10
Thanks
Hari
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23CTPtSgOHHA.4484@.TK2MSFTNGP02.phx.gbl...
> We will be moving to a new datacenter and wondering if there was any
> article out there to help me get started on the planning required..
> Thanks
>

Datacenter move.. Plan for SQL Servers

We will be moving to a new datacenter and wondering if there was any article
out there to help me get started on the planning required..
ThanksThis should be no difference than planning to move an instance/database to
another server, assuming you are going to a new server in the new datacenter.
Linchi
"Hassan" wrote:
> We will be moving to a new datacenter and wondering if there was any article
> out there to help me get started on the planning required..
> Thanks
>
>|||Take a look into the article:-
http://www.forsythe.com/infrastrat&man/dcr/documents/MovingTheDataCenter.pdf
http://www.shunra.com/articles.aspx?articleId=10
Thanks
Hari
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23CTPtSgOHHA.4484@.TK2MSFTNGP02.phx.gbl...
> We will be moving to a new datacenter and wondering if there was any
> article out there to help me get started on the planning required..
> Thanks
>

Datacenter move.. Plan for SQL Servers

We will be moving to a new datacenter and wondering if there was any article
out there to help me get started on the planning required..
ThanksThis should be no difference than planning to move an instance/database to
another server, assuming you are going to a new server in the new datacenter
.
Linchi
"Hassan" wrote:

> We will be moving to a new datacenter and wondering if there was any artic
le
> out there to help me get started on the planning required..
> Thanks
>
>|||Take a look into the article:-
http://www.forsythe.com/infrastrat&...eDataCenter.pdf
http://www.shunra.com/articles.aspx?articleId=10
Thanks
Hari
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23CTPtSgOHHA.4484@.TK2MSFTNGP02.phx.gbl...
> We will be moving to a new datacenter and wondering if there was any
> article out there to help me get started on the planning required..
> Thanks
>

Databases missing from Backup Task of Maintenance Plan

I'm moving databases from SQL7 to another server with SQL2005.

I have created new databases in SQL2005 via a restore from backup files created in the old server. Now I want to back up these new databases but they don't show in the Backup Task of a new or existing Maintenance Plan in Management Studio. I can backup them manually though. Any ideas?

I assume you have restored this database to a local 2005 server. If this is the case, they should show up. Maintenance plans can not make connections to SQL 7. Can you see this database via Management Studio?|||

Rob,

Yes, that's correct. I restored the databases to another server running SQL2005. None of the databases show up in any task of Management Studio's Maintenance Plans (eg Backup, Shrink, ReOrg)

-Bill

|||

Found the problem - the compatibility level was set to SQL Server 7.0 by default ( under database properties->options). As soon as I changed the compatiblity level to SQL Server 2000, the databases showed up in the Maintenance Plan tasks.

Thank you for your responses.