Changing config table location on the fly

Hi everyone,

I am trying to create a proof of concept to show that we can have packages deployed on 3 different servers and all we need to do is tell the package, upon execution/scheduled task, where to go fetch it's configurations from.

The configurations are using SQL Server as the package configuration. This makes it easier for DBA's to maintain since DBA's are responsible for package executions and job scheduling.

For example, the configuration database and all package configurations would be found on server1, server2 and server3 The difference in values is that on Server1, the configuration for the source data and the destination data point to Server1\DatabaseSource and Server1\DatabaseDestination and on server2, the configs point the source to Server2\DatabaseSource and Server2\DatabaseDestination and for server 3, the same thing but pointing to server3.

There is also a connection for the SSIS_Config database we're getting the configurations from. In theory, if we specify a different server where this SSIS_Config database is found, it should override the settings in the package. No?

When I schedule OR execute the package, it's always getting it's configs from the config specified in the package independent of wether I specify it in the Connection Managers of the Execute Package Utility when I manually execute it OR in the data sources tab when I configure the package to be run as a job in SQL Server 2005.

Am I missing something here?

Thanks,

Rob

You use an XML file to set the "config database" location dynamically. You can't use a SQL Server based config table to control where the "config database" resides because that is what you're trying to change.

So, use an XML file, place it on all of the servers, each pointing to the correct "config database" for that environment.|||

Rob,

it sounds like you want to decide, at execution-time, which set of configurations it should use. Right?

So, in essence you need a way of "parameterising" your package so it needs which set of configurations to use. Right?

The best way to do this is to use the /SET option of dtexec.exe

Regards

Jamie

|||

Thats the one!

Thank you Jamie

|||

Jamie,

I've tried using the /SET option but I can't get it to change the set of configurations to use.

This is the command line options I've used, did I miss something?

/FILE "D:\Documents and Settings\forestr\Desktop\Deployment Test\Deployment Test\bin\Deployment\Package.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI /SET "\Package.Connections[b-ncrsql1.SSIS_CONFIG].Properties[ConnectionString]";"Data Source=b-ncrsql1\test;Initial Catalog=SSIS_CONFIG;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"

|||

Jamie,

I've tried using the /SET option but I can't get it to change the set of configurations to use.

This is the command line options I've used, did I miss something?

/FILE "D:\Documents and Settings\forestr\Desktop\Deployment Test\Deployment Test\bin\Deployment\Package.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI /SET "\Package.Connections[b-ncrsql1.SSIS_CONFIG].Properties[ConnectionString]";"Data Source=b-ncrsql1\test;Initial Catalog=SSIS_CONFIG;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"

|||

Hmmm, I wonder if the order in which things get applied is a problem? Davide Mauri talks a little avout this here: http://weblogs.sqlteam.com/dmauri/archive/2006/04/02/9489.aspx but I don't think it'll help in your case.

Sorry. I'm a bit stumped. Phil...any ideas?

-Jamie

|||

I've tried a different approach.

Storing the source and destination servers in my SSIS_Config tables on all three servers and storing the source server name for the package configurations in an xml file.

After altering between server1, server2 and server3 in the xml config file, the package always goes directly to the initial location set up in the package: server1.

So I'm confused, no matter what method I use, the configuration I tell the package to use is overridden with the initial configuration in the package.

If I edit the package and set the configuration table to point to server 2, that works but now I can't get it pointing to server 1 or server 3.

There's got to be something I am missing... but what?!

|||

r4enterprises wrote:

I've tried a different approach.

Storing the source and destination servers in my SSIS_Config tables on all three servers and storing the source server name for the package configurations in an xml file.

After altering between server1, server2 and server3 in the xml config file, the package always goes directly to the initial location set up in the package: server1.

So I'm confused, no matter what method I use, the configuration I tell the package to use is overridden with the initial configuration in the package.

If I edit the package and set the configuration table to point to server 2, that works but now I can't get it pointing to server 1 or server 3.

There's got to be something I am missing... but what?!

Make sure that you have that config file listed under "Package Configurations" and that you have package configurations enabled. It works, trust me! I use it all of the time. If it doesn't work for you, then something is wrong.

Know that you must use an absolute file path when pointing to the XML config file.|||If you're going to change the connection string of a connection, I'd use the /CONN switch.|||

Phil,

thanks for your insight. the absolute path might be the problem as when I would build the package, I'd go change the config settings of the xml file that is found in the /bin/Deployment directory and not in the project's folder. I never thought that it would reference an absolute path like that... kinda dumb since that would make packages non-movable between locations.

Please note that when you execute a package with the /conn, that didn't seem to work either. its still pointing to its original location.

I'll try a combination of things when I get to that and keep you guys posted.

Thanks for the help,

Robin

|||

r4enterprises wrote:

I never thought that it would reference an absolute path like that... kinda dumb since that would make packages non-movable between locations.

What if the package is stored in SQL Server? What is a "relative path" and what should it point to? This is why absolute paths are required.sql

changing a database location for a report

hi,
i am new to crystal. i have a report that uses a stored procedure from a database A. I have the same stored proc. in a database B.
Please guide me through the steps to point my report to the Stored proc in database B.this is a lil urgent. Thanks,
matuGoto Database-->Set Location and choose the Stored Procedure

Change XML/XSD Source Location - Gives Lineage Errors!

Hi,

I have developed a SSIS package on my desktop and the package involves loading of XML data into a database. The XML does not have inline schema and I generated the XSD file from SSIS.

I used Derived Column and Data Conversion to load the data into the database.

Now, i want to migrate the package to a server. Now, when i change the path of the XML and XSD files, all the tasks show error such as

"Input column 'Last_Updated' (4433) has lineage id 3586 that was not previously used in data flow...."

Why is this so? I am using the same XML/XSD files after i moved to the server.

Please advice a fix for this?

Regards,

Vikram

I was looking into this and i think i understand why this is happenning:

When we actually select a XML/XSD file, for every column which flows throught he data flow, SSIS assigns it a Lineage ID which is Unique and this Lineage ID is used internally.

When i change the XML/XSD file location, SSIS is assigning new Lineage ID's for the columns in the XML file.

The Data Flow tasks down of the XML source still try to look for the Lineage ID which was generated while the tasks were created.

As the Lineage ID's changed, they thrown error such as "Input column 'Last_Updated' (4433) has lineage id 3586 that was not previously used in data flow....". Here, 3586 is the Lineage ID which was created initially.

I am not sure how to avoid this error but once this error occurs, we have the open the immediate task after the XML source and fix it.

If anyone has an idea how to avoid this (This might help in large packages during migrations), please enlighten us.

Regards,

Vikram

Change UNC of DB Backup?

What is the easiest way to change the location where I backup my databases?
Can I just modify the command field of the sysjobsteps table or is there a
stored proc I can use instead? They're setup using the DB Maint plan at the
moment and I have about 20 to change on 3 servers, so I'd prefer to not do
it manually. This will be on 2 2k servers and one sql7 server.
TIA
Matt
>
> Take a look into the system stored procedure sp_update_jobstep in MSDB
> database.
>
Hari-
I've been reading about the sp_update_jobstep proc in BOL but I'm not sure
how can I take what is already there and change the command field to reflect
the new UNC. The command line for that is huge and there are 2 places that
the UNC needs to be changed in it. Can I run a select statement and exec
sp_update_jobstep on each row returned from the select using the Replace()
function?
|||One thing to note is that backing up to a UNC share has permission
implications - you may need to change the credentials of the SQL Server
and Agent services to a domain one.
Regards
Stuart
Matt Williamson wrote:
> Hari-
> I've been reading about the sp_update_jobstep proc in BOL but I'm not sure
> how can I take what is already there and change the command field to reflect
> the new UNC. The command line for that is huge and there are 2 places that
> the UNC needs to be changed in it. Can I run a select statement and exec
> sp_update_jobstep on each row returned from the select using the Replace()
> function?
|||Hello,
Execute the sp_update_jobstep using the parameters @.Jobname,@.JobStep_id and
@.command. If you give an UNC path ensure that the SQL Server Startup account
have
rights to read and write that partcular share and directory.
Thanks
Hari
"Matt Williamson" <ih8spam@.spamsux.org> wrote in message
news:OCXSGq2LHHA.2028@.TK2MSFTNGP03.phx.gbl...
> Hari-
> I've been reading about the sp_update_jobstep proc in BOL but I'm not sure
> how can I take what is already there and change the command field to
> reflect the new UNC. The command line for that is huge and there are 2
> places that the UNC needs to be changed in it. Can I run a select
> statement and exec sp_update_jobstep on each row returned from the select
> using the Replace() function?
>
>
|||Thanks for the input. That's a non-issue though. It's already a UNC path.
I'm just changing it to a different one. The service that runs the job is
already a domain level account with appropriate permissions.
"NonNB" <nonnb@.webmail.co.za> wrote in message
news:1167905284.272432.183690@.i80g2000cwc.googlegr oups.com...
> One thing to note is that backing up to a UNC share has permission
> implications - you may need to change the credentials of the SQL Server
> and Agent services to a domain one.
> Regards
> Stuart
> Matt Williamson wrote:
>
|||> Execute the sp_update_jobstep using the parameters @.Jobname,@.JobStep_id
> and @.command. If you give an UNC path ensure that the SQL Server Startup
> account have
> rights to read and write that partcular share and directory.
Would there be any disadvantage to running something like this?
Update sysjobsteps
set command = replace(command, '\\OLDSERVER', '\\NEWSERVER')
where command like 'EXECUTE%'
In a test table that I created using a select from sysjobsteps, it worked
exactly how I wanted. I haven't tried it on the actual sysjobsteps table in
the msdb database yet though.

Change UNC of DB Backup?

What is the easiest way to change the location where I backup my databases?
Can I just modify the command field of the sysjobsteps table or is there a
stored proc I can use instead? They're setup using the DB Maint plan at the
moment and I have about 20 to change on 3 servers, so I'd prefer to not do
it manually. This will be on 2 2k servers and one sql7 server.
TIA
Matt>
> Take a look into the system stored procedure sp_update_jobstep in MSDB
> database.
>
Hari-
I've been reading about the sp_update_jobstep proc in BOL but I'm not sure
how can I take what is already there and change the command field to reflect
the new UNC. The command line for that is huge and there are 2 places that
the UNC needs to be changed in it. Can I run a select statement and exec
sp_update_jobstep on each row returned from the select using the Replace()
function?|||One thing to note is that backing up to a UNC share has permission
implications - you may need to change the credentials of the SQL Server
and Agent services to a domain one.
Regards
Stuart
Matt Williamson wrote:
> >
> > Take a look into the system stored procedure sp_update_jobstep in MSDB
> > database.
> >
> Hari-
> I've been reading about the sp_update_jobstep proc in BOL but I'm not sure
> how can I take what is already there and change the command field to reflect
> the new UNC. The command line for that is huge and there are 2 places that
> the UNC needs to be changed in it. Can I run a select statement and exec
> sp_update_jobstep on each row returned from the select using the Replace()
> function?|||Hello,
Execute the sp_update_jobstep using the parameters @.Jobname,@.JobStep_id and
@.command. If you give an UNC path ensure that the SQL Server Startup account
have
rights to read and write that partcular share and directory.
Thanks
Hari
"Matt Williamson" <ih8spam@.spamsux.org> wrote in message
news:OCXSGq2LHHA.2028@.TK2MSFTNGP03.phx.gbl...
> >
>> Take a look into the system stored procedure sp_update_jobstep in MSDB
>> database.
> Hari-
> I've been reading about the sp_update_jobstep proc in BOL but I'm not sure
> how can I take what is already there and change the command field to
> reflect the new UNC. The command line for that is huge and there are 2
> places that the UNC needs to be changed in it. Can I run a select
> statement and exec sp_update_jobstep on each row returned from the select
> using the Replace() function?
>
>|||Thanks for the input. That's a non-issue though. It's already a UNC path.
I'm just changing it to a different one. The service that runs the job is
already a domain level account with appropriate permissions.
"NonNB" <nonnb@.webmail.co.za> wrote in message
news:1167905284.272432.183690@.i80g2000cwc.googlegroups.com...
> One thing to note is that backing up to a UNC share has permission
> implications - you may need to change the credentials of the SQL Server
> and Agent services to a domain one.
> Regards
> Stuart
> Matt Williamson wrote:
>> >
>> > Take a look into the system stored procedure sp_update_jobstep in MSDB
>> > database.
>> >
>> Hari-
>> I've been reading about the sp_update_jobstep proc in BOL but I'm not
>> sure
>> how can I take what is already there and change the command field to
>> reflect
>> the new UNC. The command line for that is huge and there are 2 places
>> that
>> the UNC needs to be changed in it. Can I run a select statement and exec
>> sp_update_jobstep on each row returned from the select using the
>> Replace()
>> function?
>|||> Execute the sp_update_jobstep using the parameters @.Jobname,@.JobStep_id
> and @.command. If you give an UNC path ensure that the SQL Server Startup
> account have
> rights to read and write that partcular share and directory.
Would there be any disadvantage to running something like this?
Update sysjobsteps
set command = replace(command, '\\OLDSERVER', '\\NEWSERVER')
where command like 'EXECUTE%'
In a test table that I created using a select from sysjobsteps, it worked
exactly how I wanted. I haven't tried it on the actual sysjobsteps table in
the msdb database yet though.

Change UNC of DB Backup?

What is the easiest way to change the location where I backup my databases?
Can I just modify the command field of the sysjobsteps table or is there a
stored proc I can use instead? They're setup using the DB Maint plan at the
moment and I have about 20 to change on 3 servers, so I'd prefer to not do
it manually. This will be on 2 2k servers and one sql7 server.
TIA
MattHello,
Take a look into the system stored procedure sp_update_jobstep in MSDB
database.
Thanks
Hari
"Matt Williamson" wrote:

> What is the easiest way to change the location where I backup my databases
?
> Can I just modify the command field of the sysjobsteps table or is there a
> stored proc I can use instead? They're setup using the DB Maint plan at th
e
> moment and I have about 20 to change on 3 servers, so I'd prefer to not do
> it manually. This will be on 2 2k servers and one sql7 server.
> TIA
> Matt
>
>|||>
> Take a look into the system stored procedure sp_update_jobstep in MSDB
> database.
>
Hari-
I've been reading about the sp_update_jobstep proc in BOL but I'm not sure
how can I take what is already there and change the command field to reflect
the new UNC. The command line for that is huge and there are 2 places that
the UNC needs to be changed in it. Can I run a select statement and exec
sp_update_jobstep on each row returned from the select using the Replace()
function?|||One thing to note is that backing up to a UNC share has permission
implications - you may need to change the credentials of the SQL Server
and Agent services to a domain one.
Regards
Stuart
Matt Williamson wrote:
> Hari-
> I've been reading about the sp_update_jobstep proc in BOL but I'm not sure
> how can I take what is already there and change the command field to refle
ct
> the new UNC. The command line for that is huge and there are 2 places that
> the UNC needs to be changed in it. Can I run a select statement and exec
> sp_update_jobstep on each row returned from the select using the Replace()
> function?|||Hello,
Execute the sp_update_jobstep using the parameters @.Jobname,@.JobStep_id and
@.command. If you give an UNC path ensure that the SQL Server Startup account
have
rights to read and write that partcular share and directory.
Thanks
Hari
"Matt Williamson" <ih8spam@.spamsux.org> wrote in message
news:OCXSGq2LHHA.2028@.TK2MSFTNGP03.phx.gbl...
> Hari-
> I've been reading about the sp_update_jobstep proc in BOL but I'm not sure
> how can I take what is already there and change the command field to
> reflect the new UNC. The command line for that is huge and there are 2
> places that the UNC needs to be changed in it. Can I run a select
> statement and exec sp_update_jobstep on each row returned from the select
> using the Replace() function?
>
>|||Thanks for the input. That's a non-issue though. It's already a UNC path.
I'm just changing it to a different one. The service that runs the job is
already a domain level account with appropriate permissions.
"NonNB" <nonnb@.webmail.co.za> wrote in message
news:1167905284.272432.183690@.i80g2000cwc.googlegroups.com...
> One thing to note is that backing up to a UNC share has permission
> implications - you may need to change the credentials of the SQL Server
> and Agent services to a domain one.
> Regards
> Stuart
> Matt Williamson wrote:
>|||> Execute the sp_update_jobstep using the parameters @.Jobname,@.JobStep_id
> and @.command. If you give an UNC path ensure that the SQL Server Startup
> account have
> rights to read and write that partcular share and directory.
Would there be any disadvantage to running something like this?
Update sysjobsteps
set command = replace(command, '\\OLDSERVER', '\\NEWSERVER')
where command like 'EXECUTE%'
In a test table that I created using a select from sysjobsteps, it worked
exactly how I wanted. I haven't tried it on the actual sysjobsteps table in
the msdb database yet though.

Change TL Drive location

hi,
I have the data file and transaction log file on the same
drive, SQl Server 7.0, how can i change de TL location? i
have the files on drive e:\ for instance and i want to put
only the TL files on drive d:\, thanks a lot in advance
Regards,
CMLCINF: Moving SQL Server Databases to a New Location with Detach/Attach
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q224071
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Miguel" <cmlcorreia@.netcabo.pt> wrote in message
news:01ad01c3424b$88afd130$a101280a@.phx.gbl...
hi,
I have the data file and transaction log file on the same
drive, SQl Server 7.0, how can i change de TL location? i
have the files on drive e:\ for instance and i want to put
only the TL files on drive d:\, thanks a lot in advance
Regards,
CMLC

Change the location of the tempdb and the model databases

Is there a way to change the location of the tempdb and the model database files. I tried to do a restore with move with no luck and I cannot seem to get the syntax for an Alter database. Any suggestions?Look up Atricle Q224071 in Microsoft's knowledge base, it addresses your questions.

Change the location of the database files

Hi,
I would like to move my base from drive C: to drive D: .How can I do this
with MSDE (I don't have the MMC installed on the computer where I want to do
this)
Refer to following url
http://support.microsoft.com/default...en-us%3B224071
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
"Gal" <W32.virus@.wanadoo.fr> wrote in message
news:uM8J5PqMEHA.3596@.tk2msftngp13.phx.gbl...
> Hi,
> I would like to move my base from drive C: to drive D: .How can I do this
> with MSDE (I don't have the MMC installed on the computer where I want to
do
> this)
>
|||hi Gal,
"Gal" <W32.virus@.wanadoo.fr> ha scritto nel messaggio
news:uM8J5PqMEHA.3596@.tk2msftngp13.phx.gbl...
> Hi,
> I would like to move my base from drive C: to drive D: .How can I do this
> with MSDE (I don't have the MMC installed on the computer where I want to
do
> this)
you can do it detaching the desired database, moving the database files to
the new desired position a,d reattacchin the database...
log into oSql.exe..
1>EXEC sp_detach_db db_name
2>GO
move the file(s) to new location
1>EXEC sp_attach_db @.dbname = 'db_name' ,
2>@.filename1 = 'd:\:fulldatapath_of_DataFile.Mdf' ,
3>@.filename2 = 'd:\:fulldatapath_of_LogFile.Ldf'
4>GO
for your convenienence, you can have a look at a free prj of mine, available
at the link following my sign., which provide a user interface similar to
Enterprise Manager for full MSDE administration...
hth
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Change the location of the base

Hi,
I would like to move my base from drive C: to drive D: .How can I do this
with MSDE (I don't have the MMC installed on the computer where I want to do
this)
Try this article:
http://support.microsoft.com/default...b;en-us;224071
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Gal" <W32.virus@.wanadoo.fr> wrote in message
news:ecXhZPqMEHA.2704@.TK2MSFTNGP10.phx.gbl...
Hi,
I would like to move my base from drive C: to drive D: .How can I do this
with MSDE (I don't have the MMC installed on the computer where I want to do
this)

change the location of dat & log files**

Hi
I'm working with SQL server 2000
and I've a db called db1 and it's dat and log
files(for example) are located in root of d: directory in a server and
there are so many users working with this db.
and now I want to change the location (for example
move it to c:\sqldata,maybe because of not empty space in current
drive),how is it possible?
my second question is ,if I want to define the
second dat file and log file for a db which are placed in
different location how is it possible? and if I do
it successfully,is there anyway when I want to restore
the backup of this db ,make dat files or log files ,each of them in one
file beacause there is enough space in one drive in destination?
any help would be greatly thanked.RM
[url]http://support.microsoft.com/directory/article.asp?ID=kb;en-us;Q224071--[/url
]
Moving SQL Server Databases to a New Location
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opsactt6e0hqligo@.msnews.microsoft.com...
> Hi
> I'm working with SQL server 2000
> and I've a db called db1 and it's dat and log
> files(for example) are located in root of d: directory in a server and
> there are so many users working with this db.
> and now I want to change the location (for example
> move it to c:\sqldata,maybe because of not empty space in current
> drive),how is it possible?
> my second question is ,if I want to define the
> second dat file and log file for a db which are placed in
> different location how is it possible? and if I do
> it successfully,is there anyway when I want to restore
> the backup of this db ,make dat files or log files ,each of them in one
> file beacause there is enough space in one drive in destination?
>
> any help would be greatly thanked.|||1. To move files, read KB 224071.
2. To add a database file, use ALTER DATABASE ADD FILE...
3. No, when you RESTORE, you have to have as many files as when you took the
backup, each file need
to have the same logical name, and be at least as big as when you took the b
ackup...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RM" <m_r1824@.yahoo.co.uk> wrote in message news:opsactt6e0hqligo@.msnews.microsoft.com...[vb
col=seagreen]
> Hi
> I'm working with SQL server 2000
> and I've a db called db1 and it's dat and log
> files(for example) are located in root of d: directory in a server and
> there are so many users working with this db.
> and now I want to change the location (for example
> move it to c:\sqldata,maybe because of not empty space in current
> drive),how is it possible?
> my second question is ,if I want to define the
> second dat file and log file for a db which are placed in
> different location how is it possible? and if I do
> it successfully,is there anyway when I want to restore
> the backup of this db ,make dat files or log files ,each of them in one
> file beacause there is enough space in one drive in destination?
>
> any help would be greatly thanked.[/vbcol]

change the location of dat & log files**

Hi
I'm working with SQL server 2000
and I've a db called db1 and it's dat and log
files(for example) are located in root of d: directory in a server and
there are so many users working with this db.
and now I want to change the location (for example
move it to c:\sqldata,maybe because of not empty space in current
drive),how is it possible?
my second question is ,if I want to define the
second dat file and log file for a db which are placed in
different location how is it possible? and if I do
it successfully,is there anyway when I want to restore
the backup of this db ,make dat files or log files ,each of them in one
file beacause there is enough space in one drive in destination?
any help would be greatly thanked.
RM
http://support.microsoft.com/directo...Q224071--
Moving SQL Server Databases to a New Location
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opsactt6e0hqligo@.msnews.microsoft.com...
> Hi
> I'm working with SQL server 2000
> and I've a db called db1 and it's dat and log
> files(for example) are located in root of d: directory in a server and
> there are so many users working with this db.
> and now I want to change the location (for example
> move it to c:\sqldata,maybe because of not empty space in current
> drive),how is it possible?
> my second question is ,if I want to define the
> second dat file and log file for a db which are placed in
> different location how is it possible? and if I do
> it successfully,is there anyway when I want to restore
> the backup of this db ,make dat files or log files ,each of them in one
> file beacause there is enough space in one drive in destination?
>
> any help would be greatly thanked.
|||RM
http://support.microsoft.com/directo...Q224071--
Moving SQL Server Databases to a New Location
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opsactt6e0hqligo@.msnews.microsoft.com...
> Hi
> I'm working with SQL server 2000
> and I've a db called db1 and it's dat and log
> files(for example) are located in root of d: directory in a server and
> there are so many users working with this db.
> and now I want to change the location (for example
> move it to c:\sqldata,maybe because of not empty space in current
> drive),how is it possible?
> my second question is ,if I want to define the
> second dat file and log file for a db which are placed in
> different location how is it possible? and if I do
> it successfully,is there anyway when I want to restore
> the backup of this db ,make dat files or log files ,each of them in one
> file beacause there is enough space in one drive in destination?
>
> any help would be greatly thanked.
|||1. To move files, read KB 224071.
2. To add a database file, use ALTER DATABASE ADD FILE...
3. No, when you RESTORE, you have to have as many files as when you took the backup, each file need
to have the same logical name, and be at least as big as when you took the backup...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RM" <m_r1824@.yahoo.co.uk> wrote in message news:opsactt6e0hqligo@.msnews.microsoft.com...
> Hi
> I'm working with SQL server 2000
> and I've a db called db1 and it's dat and log
> files(for example) are located in root of d: directory in a server and
> there are so many users working with this db.
> and now I want to change the location (for example
> move it to c:\sqldata,maybe because of not empty space in current
> drive),how is it possible?
> my second question is ,if I want to define the
> second dat file and log file for a db which are placed in
> different location how is it possible? and if I do
> it successfully,is there anyway when I want to restore
> the backup of this db ,make dat files or log files ,each of them in one
> file beacause there is enough space in one drive in destination?
>
> any help would be greatly thanked.
|||1. To move files, read KB 224071.
2. To add a database file, use ALTER DATABASE ADD FILE...
3. No, when you RESTORE, you have to have as many files as when you took the backup, each file need
to have the same logical name, and be at least as big as when you took the backup...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RM" <m_r1824@.yahoo.co.uk> wrote in message news:opsactt6e0hqligo@.msnews.microsoft.com...
> Hi
> I'm working with SQL server 2000
> and I've a db called db1 and it's dat and log
> files(for example) are located in root of d: directory in a server and
> there are so many users working with this db.
> and now I want to change the location (for example
> move it to c:\sqldata,maybe because of not empty space in current
> drive),how is it possible?
> my second question is ,if I want to define the
> second dat file and log file for a db which are placed in
> different location how is it possible? and if I do
> it successfully,is there anyway when I want to restore
> the backup of this db ,make dat files or log files ,each of them in one
> file beacause there is enough space in one drive in destination?
>
> any help would be greatly thanked.

change the location of dat & log files**

Hi
I'm working with SQL server 2000
and I've a db called db1 and it's dat and log
files(for example) are located in root of d: directory in a server and
there are so many users working with this db.
and now I want to change the location (for example
move it to c:\sqldata,maybe because of not empty space in current
drive),how is it possible?
my second question is ,if I want to define the
second dat file and log file for a db which are placed in
different location how is it possible? and if I do
it successfully,is there anyway when I want to restore
the backup of this db ,make dat files or log files ,each of them in one
file beacause there is enough space in one drive in destination?
any help would be greatly thanked.RM
http://support.microsoft.com/directory/article.asp?ID=kb;en-us;Q224071--
Moving SQL Server Databases to a New Location
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opsactt6e0hqligo@.msnews.microsoft.com...
> Hi
> I'm working with SQL server 2000
> and I've a db called db1 and it's dat and log
> files(for example) are located in root of d: directory in a server and
> there are so many users working with this db.
> and now I want to change the location (for example
> move it to c:\sqldata,maybe because of not empty space in current
> drive),how is it possible?
> my second question is ,if I want to define the
> second dat file and log file for a db which are placed in
> different location how is it possible? and if I do
> it successfully,is there anyway when I want to restore
> the backup of this db ,make dat files or log files ,each of them in one
> file beacause there is enough space in one drive in destination?
>
> any help would be greatly thanked.|||1. To move files, read KB 224071.
2. To add a database file, use ALTER DATABASE ADD FILE...
3. No, when you RESTORE, you have to have as many files as when you took the backup, each file need
to have the same logical name, and be at least as big as when you took the backup...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RM" <m_r1824@.yahoo.co.uk> wrote in message news:opsactt6e0hqligo@.msnews.microsoft.com...
> Hi
> I'm working with SQL server 2000
> and I've a db called db1 and it's dat and log
> files(for example) are located in root of d: directory in a server and
> there are so many users working with this db.
> and now I want to change the location (for example
> move it to c:\sqldata,maybe because of not empty space in current
> drive),how is it possible?
> my second question is ,if I want to define the
> second dat file and log file for a db which are placed in
> different location how is it possible? and if I do
> it successfully,is there anyway when I want to restore
> the backup of this db ,make dat files or log files ,each of them in one
> file beacause there is enough space in one drive in destination?
>
> any help would be greatly thanked.

Change temporary tables location

Hi:
I was wondering if it is possible, in SQL Server, change the database
where temp tables are generated by default.
I know that temp tables are generated on tempdb, but this is very bad
when there's much concurrency...
I need some "magic configuration" that allows me to specify the
temporary tables location for each database, like in Oracle.
Is there anything like that in SQL Server?
Thanks,
Ravi Wallau
nospam@.nospam.orgThere magic is anywhere else, but not in this settings of SQL Server.
HTH, Jens Suessmeyer.|||As far as I am aware there isn't a way of doing what your asking inside SQL
Server
---
Retrograde
"Ravi Ambros Wallau" wrote:

> Hi:
> I was wondering if it is possible, in SQL Server, change the database
> where temp tables are generated by default.
> I know that temp tables are generated on tempdb, but this is very bad
> when there's much concurrency...
> I need some "magic configuration" that allows me to specify the
> temporary tables location for each database, like in Oracle.
> Is there anything like that in SQL Server?
> Thanks,
> --
> Ravi Wallau
> nospam@.nospam.org
>
>

Change temporary tables location

Hi:
I was wondering if it is possible, in SQL Server, change the database
where temp tables are generated by default.
I know that temp tables are generated on tempdb, but this is very bad
when there's much concurrency...
I need some "magic configuration" that allows me to specify the
temporary tables location for each database, like in Oracle.
Is there anything like that in SQL Server?
Thanks,
Ravi Wallau
nospam@.nospam.org
There magic is anywhere else, but not in this settings of SQL Server.
HTH, Jens Suessmeyer.
|||As far as I am aware there isn't a way of doing what your asking inside SQL
Server
Retrograde
"Ravi Ambros Wallau" wrote:

> Hi:
> I was wondering if it is possible, in SQL Server, change the database
> where temp tables are generated by default.
> I know that temp tables are generated on tempdb, but this is very bad
> when there's much concurrency...
> I need some "magic configuration" that allows me to specify the
> temporary tables location for each database, like in Oracle.
> Is there anything like that in SQL Server?
> Thanks,
> --
> Ravi Wallau
> nospam@.nospam.org
>
>

Change temporary tables location

Hi:
I was wondering if it is possible, in SQL Server, change the database
where temp tables are generated by default.
I know that temp tables are generated on tempdb, but this is very bad
when there's much concurrency...
I need some "magic configuration" that allows me to specify the
temporary tables location for each database, like in Oracle.
Is there anything like that in SQL Server?
Thanks,
--
Ravi Wallau
nospam@.nospam.orgThere magic is anywhere else, but not in this settings of SQL Server.
HTH, Jens Suessmeyer.|||As far as I am aware there isn't a way of doing what your asking inside SQL
Server
---
Retrograde
"Ravi Ambros Wallau" wrote:
> Hi:
> I was wondering if it is possible, in SQL Server, change the database
> where temp tables are generated by default.
> I know that temp tables are generated on tempdb, but this is very bad
> when there's much concurrency...
> I need some "magic configuration" that allows me to specify the
> temporary tables location for each database, like in Oracle.
> Is there anything like that in SQL Server?
> Thanks,
> --
> Ravi Wallau
> nospam@.nospam.org
>
>

change reporting services location (2000)

hi,
our reporting services DB moved and I need to tell the report manager where to find it
isn't it in a config file somewhere, i can't find it?
do i have to reinstall?RSConfig -c -s Server name -d Reporting Services database name -a Sql|Windows -u User name -p User password

nevermind guys|||hrmm it still wont work, says it cant find the db

ugh...|||ok im talking to myself...

now a different microsoft sites says i cannot specify a named instance with this utility... but it let me install it like that (go figure)

now what, i guess i have to reinstall|||hrmm it still wont work, says it cant find the db

ugh...
See the Report Server database can be installed on only one instance of SQL Server per physical database sever computer. The database need not reside on the local Report Server computer but the server must be a member of the Windows domain or a server trusted by the domain.So somebody has moved it and if your reportings were okay so you can easily understand its on the member or a trusted domain.If your reports are not working its better to install it again.|||the ip address of the server changed, so really i shouldnt have to do anything - BUT, they did not open the port that allows dynamic ports to be found (UDP 1434). so i have to redo it and specify the port number i.e. Server\Instance, Port.

the ms website says rsconfig does not support named instances, but the installation GUI did, so that does not make sense

i do not want to reinstall it, it will be a pain, the rsconfig utility should support named instances otherwise what is the point