How to change the error log location?

How can you change the location of the Server's error log? I need to get it
off of the c: drive!
Thanks!
It is in the registry. To change from EM: Right-click the server, properties, startup parameters.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:475F8B64-872F-4285-AE4C-14D6255B0379@.microsoft.com...
> How can you change the location of the Server's error log? I need to get it
> off of the c: drive!
> Thanks!
|||Hi ,
I found the following article that talks about the same. Read the summary
below for breif info .
SUMMARY:
==============
This article describes how to change the location of the data and log files
for any SQL Server 7.0 or SQL Server 2000 database.
Moving SQL Server databases to a new location with Detach/Attach :
================================================== ===============
Microsoft Knowledge Base Article - 224071 :
http://support.microsoft.com/default...b;EN-US;224071
Moving the master database:
===========================
Change the path for the master data and log files in SQL Server Enterprise
Manager.
Note You may optionally change the location of the error log here as well.
Right-click the SQL Server in Enterprise Manager and click Properties.
Click the Startup Parameters button and you will see the following entries:
-dD:\MSSQL7\data\master.mdf
-eD:\MSSQL7\log\ErrorLog
-lD:\MSSQL7\data\mastlog.ldf
-d is the fully qualified path for the master database data file.
-e is the fully qualified path for the error log file.
-l is the fully qualified path for the master database log file.
Change these values as follows:
Remove the current entries for the Master.mdf and Mastlog.ldf files.
Add new entries specifying the new location: -dE:\SQLDATA\master.mdf
-lE:\SQLDATA\mastlog.ldf
Stop SQL Server.
Copy the Master.mdf and Mastlog.ldf files to the new location (E:\Sqldata).
Restart SQL Server.
Regards,
Venkat.

How to change replication agent's default profile properties....

Hi,
my problem is: we have poor line between servers. To avoid this issue i
created new profile for distribution agent. This profile has been
created with bigger QieryTimeout and LogonTimeout values. After that i
assigned this profile to distribution agent and SQL Server agent was
restarted after this. According to new settings replication works fine.
But: i've found, that may be my boss, may be somebody else rolled back
all my changes and restored "system default" profile for distribution
agent. After this replicaion fault every time. So every morning i
assigned my profile, every night "default" profile has been restored.
My question is: is it possible to change "default" profile settings? I
saw MSdistribution_agent table, found job description, where profile_id
= 4. Where i should looking for details of profile settings?
Thanks a lot in advance.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
We do not recommend updating the default profile, I'm not quite sure if i
understand what you mean by "rolled back" changes.
What you did is the recommended way of changing the profile values.
thanks
gopal
|||Many thanks for all.
"roll back" means, that i assigned my profile to distribution agent, but
next morning i saw "default" profile assigned to the same agent.
This issue now sorted out because i found MSagent_profiles,
MSagent_properties tables in msdb.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

How to change login in linked server (From Sql Security to Integrated Sec)

We are having some commercial applications that are running on sql servers at different sites, all with sql security. The software vendor wants to copy data into staging tables on our BI server. But our BI server only support Integrated security.

But how can i say, in the definition of a linked server (at the remote machines) to swicth security system ?

If it is possible i could enter a fixed domain-account in that users fields, but i suppose that this field was intended for SqlServer security.

Hi,

Create a login say (Remoteaccess) on both the server and give permission on the database(fullrights).

Use this remote login in ur linked server.

Then u can permorm ETL process on both the server.

The same way u can do on other servers also.

Another way is u can setup replication(but it will increase overhead).

regards

Mohd Sufian

|||

You can drop the existing linked server login mapping using sp_droplinkedsrvlogin and recreate them using sp_addlinkedsrvlogin. In the new login mapping, you can set the @.useself parameter to true.

Let us know if this works!

sql

how to change dts owner name

hi,
does anyone know how to change a dts packeage's owner name
on sql 2000?
we have individuals that creates dts packages on
production servers, and we would like to change the dts
owner name from the individual accounts into server's own
account name.
many thanks!
JJ
sp_changeobjectowner
I've never had to change a dts owner, but this is where I would look first.
John
"JJ Wang" wrote:

> hi,
> does anyone know how to change a dts packeage's owner name
> on sql 2000?
> we have individuals that creates dts packages on
> production servers, and we would like to change the dts
> owner name from the individual accounts into server's own
> account name.
> many thanks!
> JJ
>
|||Check out http://www.sqldts.com/default.aspx?212.
Hope this helps.
Dan Guzman
SQL Server MVP
"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:03d001c4a74b$4ce490d0$a501280a@.phx.gbl...
> hi,
> does anyone know how to change a dts packeage's owner name
> on sql 2000?
> we have individuals that creates dts packages on
> production servers, and we would like to change the dts
> owner name from the individual accounts into server's own
> account name.
> many thanks!
> JJ
|||sp_changeobjectowner is intended for SQL Server database objects and can't
be used for DTS packages.
Hope this helps.
Dan Guzman
SQL Server MVP
"John Cappelletti" <JohnCappelletti@.discussions.microsoft.com> wrote in
message news:7D4B53C1-5AAD-42F5-A8FE-C1DB9DB37508@.microsoft.com...[vbcol=seagreen]
> sp_changeobjectowner
> I've never had to change a dts owner, but this is where I would look
> first.
> John
>
> "JJ Wang" wrote:
|||I use:
use msdb
go
sp_reassign_dtspackageowner [@.name =] 'name',
[@.id =] 'id',
[@.newloginname =] 'newloginname'
--to find dts package id :
use msdb
go
select * from sysdtspackages
go
"JJ Wang" wrote:

> hi,
> does anyone know how to change a dts packeage's owner name
> on sql 2000?
> we have individuals that creates dts packages on
> production servers, and we would like to change the dts
> owner name from the individual accounts into server's own
> account name.
> many thanks!
> JJ
>

how to change dts owner name

hi,
does anyone know how to change a dts packeage's owner name
on sql 2000?
we have individuals that creates dts packages on
production servers, and we would like to change the dts
owner name from the individual accounts into server's own
account name.
many thanks!
JJsp_changeobjectowner
I've never had to change a dts owner, but this is where I would look first.
John
"JJ Wang" wrote:
> hi,
> does anyone know how to change a dts packeage's owner name
> on sql 2000?
> we have individuals that creates dts packages on
> production servers, and we would like to change the dts
> owner name from the individual accounts into server's own
> account name.
> many thanks!
> JJ
>|||Check out http://www.sqldts.com/default.aspx?212.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:03d001c4a74b$4ce490d0$a501280a@.phx.gbl...
> hi,
> does anyone know how to change a dts packeage's owner name
> on sql 2000?
> we have individuals that creates dts packages on
> production servers, and we would like to change the dts
> owner name from the individual accounts into server's own
> account name.
> many thanks!
> JJ|||sp_changeobjectowner is intended for SQL Server database objects and can't
be used for DTS packages.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"John Cappelletti" <JohnCappelletti@.discussions.microsoft.com> wrote in
message news:7D4B53C1-5AAD-42F5-A8FE-C1DB9DB37508@.microsoft.com...
> sp_changeobjectowner
> I've never had to change a dts owner, but this is where I would look
> first.
> John
>
> "JJ Wang" wrote:
>> hi,
>> does anyone know how to change a dts packeage's owner name
>> on sql 2000?
>> we have individuals that creates dts packages on
>> production servers, and we would like to change the dts
>> owner name from the individual accounts into server's own
>> account name.
>> many thanks!
>> JJ|||I use:
use msdb
go
sp_reassign_dtspackageowner [@.name =] 'name',
[@.id =] 'id',
[@.newloginname =] 'newloginname'
--to find dts package id :
use msdb
go
select * from sysdtspackages
go
"JJ Wang" wrote:
> hi,
> does anyone know how to change a dts packeage's owner name
> on sql 2000?
> we have individuals that creates dts packages on
> production servers, and we would like to change the dts
> owner name from the individual accounts into server's own
> account name.
> many thanks!
> JJ
>|||wow, thanks to you all for the quick response, tons of
good tips here. you solved my problem!!
thank you so much!!!!
JJ
>--Original Message--
>hi,
>does anyone know how to change a dts packeage's owner
name
>on sql 2000?
>we have individuals that creates dts packages on
>production servers, and we would like to change the dts
>owner name from the individual accounts into server's own
>account name.
>many thanks!
>JJ
>.
>

How to call SP of one SQL server from other SQL server

Hi,

In my application environment, we have data will be in available in different SQL servers.

I hava requirement to call the SP of on SQL server from the SP in the other SQL server.

Can any one help me out for a solution which will be faster?

Please note that I cant go for the linked servers as the other SQL severs credentials will be stored in a Database table and it has to be used in run time of the SPs.

Awaiting quick response from you all..

Thanks in advance.

-Thams

YOu can use the openrowset for that:

DECLARE @.USERNAME VARCHAR(50)

DECLARE @.Password VARCHAR(50)

DECLARE @.SQLString VARCHAR(500)

SET @.USERNAME = 'SomeUser'

SET @.Password = 'SomePassword'

SET @.SQLString = '

SELECT a.*

FROM OPENROWSET(''SQLNCLI'', ''Data Source=.;UserName=' + @.USERNAME + ';Pwd=' + @.Password + ';'',

''EXEC Northwind.dbo.[Ten Most Expensive Products]'') AS a;

'

SELECT @.SQLString

EXEC(@.SQLString)

Jens K. Suessmeyer

http://www.sqlserver2005.de

How to bulk deploy reports to multiple servers?

Hi,

I'm just wondering if there is any easy way to deploy reports to multiple servers. My current issue is that I need to deploy tens of reports onto tens of production server. Does anyone know how to do that?

Thanks a lot.

Cheers,

You can do that by using the command prompt utility and rss files. Look into the following link for more details:

http://msdn2.microsoft.com/en-us/library/ms162839.aspx

Then you can put multiple rs command lines in one single batch file (.bat) and then execute that file for deploying to multiple servers.

Shyam

How to bring Second SQL Log Ship Server Online

Hi -
I have log shipping configured and working.(SQL 2005 SP2). Both
shared directories on the Primary and Secondary servers are full of
logs and the status of the Secondary database is always showing
(Restoring...). Now the Primary server just took a hard dump. Could
someone tell me how to bring the Secondary server on line so that the
application can access it?
Thanks
RSC
www.schmooseme.net
http://sqlserveruniverse.com/content/ADMN0100111132007LogShipping.aspx
"RSC" <rsceliberti@.hotmail.com> wrote in message
news:84700042-9e32-48fd-bda3-730434aa8619@.y5g2000hsf.googlegroups.com...
> Hi -
> I have log shipping configured and working.(SQL 2005 SP2). Both
> shared directories on the Primary and Secondary servers are full of
> logs and the status of the Secondary database is always showing
> (Restoring...). Now the Primary server just took a hard dump. Could
> someone tell me how to bring the Secondary server on line so that the
> application can access it?
> Thanks
> RSC
> www.schmooseme.net
|||The documents says to restore the logs with NORECOVERY. I have about
100 logs or so, I'm doing the default every 15 min. Do I need to
restore all of them? Or what if I ship every 2 hours, does that mean
at anytime I can lose the last 2 hours when I fail over?
RSC
|||You should restore your lastest log with RECOVERY to bring your Secondary
database online in case of a failure that may happen on your Primary
database.
You did not mention what the time period is to restore the logs that are
brought to your secondary server from the primary?
For example:
Step1= Backup Logs on the Primary Server
Step2= Copy Log backups to the Secondary Server from the Primary Server
Step3= Restore copied Logs on the Seconfary Server
There are time intervals between these jobs.
For example: if you perform step1 every 15 min. step2 every 15 min and
perform the step3 every 30 min. (Which is 15 min. for all steps by default)
Then:
13:00 = step1 + step2 + step3
13.15 = step1 + step2
13.30 = step1 + step2 + step3
13.45 = step1 + step2
13.55 = Primary Failed
As the lastest copied transaction log arrived to the Secondary server at
13.45, you'll have it on your Secondary server (if you designated a folder
as a destination copy folder on Secondary server) And you'll just restore
that lastest transaction log because according to this scenario older ones
are already restored (at last at l3.30 in this example)
If you have a chance to take tail log backup from the primary server then
you'll go to your primary server and take tail log backup of your database
and you'll restore it WITH RECOVERY on your Secondary database. If you don't
have this chance then you'll restore your log backup which was copied to
your secondary server at 13.45 using WITH RECOVERY to bring your secondary
database online.
Again, you should apply all availabled and not restored-before transactions
WITH NORECOVERY until the lastest one. When you come to the lastest log
which could be the lastest copied log from the Primary database or Tail Log
from the Primary database, you'll restore it using WITH RECOVERY to bring
your Secondary database online.
Ekrem nsoy
"RSC" <rsceliberti@.hotmail.com> wrote in message
news:9d8cc6ff-57bf-4587-8283-c5725415d1a9@.e67g2000hsc.googlegroups.com...
> The documents says to restore the logs with NORECOVERY. I have about
> 100 logs or so, I'm doing the default every 15 min. Do I need to
> restore all of them? Or what if I ship every 2 hours, does that mean
> at anytime I can lose the last 2 hours when I fail over?
> RSC
|||Excellent information!
I have step1 and step2 automated, but I see now that I should automate
step3 on the secondary server, then do a log restore RECOVERY at when
the Primary goes off line. Last but not least; how easy is it to get
the Primary back on line once it is repaired, say after a power supply
failure? I'm thinking, backup the database and restore it on the
Primary, then re-setup the log shipping routine as it was before the
Secondary was recovered. Now I'm back to the begining.
RSC
|||After such a failure, your database could be corrupted or damaged. It
probably would be the best to restore the most up to date version of that
database's backup. In your situation the most up to date backup is your
ex-secondary database. And yes, after this you can resetup Log Shipping and
live happily after.
Log shipping is Warm Standby HA system so you must do this stuff manually...
Ekrem nsoy
"RSC" <rsceliberti@.hotmail.com> wrote in message
news:1a50298e-029c-40bd-8e05-01abdf566052@.d4g2000prg.googlegroups.com...
> Excellent information!
> I have step1 and step2 automated, but I see now that I should automate
> step3 on the secondary server, then do a log restore RECOVERY at when
> the Primary goes off line. Last but not least; how easy is it to get
> the Primary back on line once it is repaired, say after a power supply
> failure? I'm thinking, backup the database and restore it on the
> Primary, then re-setup the log shipping routine as it was before the
> Secondary was recovered. Now I'm back to the begining.
> RSC
>
|||Thanks for your advice, I think this is my best option. The
application is Websense reporting so any lost data during the failover
or re-setup is not much of a loss anyway.
Thanks Again!
RSC

How to bring Second SQL Log Ship Server Online

Hi -
I have log shipping configured and working.(SQL 2005 SP2). Both
shared directories on the Primary and Secondary servers are full of
logs and the status of the Secondary database is always showing
(Restoring...). Now the Primary server just took a hard dump. Could
someone tell me how to bring the Secondary server on line so that the
application can access it?
Thanks
RSC
www.schmooseme.nethttp://sqlserveruniverse.com/content/ADMN0100111132007LogShipping.aspx
"RSC" <rsceliberti@.hotmail.com> wrote in message
news:84700042-9e32-48fd-bda3-730434aa8619@.y5g2000hsf.googlegroups.com...
> Hi -
> I have log shipping configured and working.(SQL 2005 SP2). Both
> shared directories on the Primary and Secondary servers are full of
> logs and the status of the Secondary database is always showing
> (Restoring...). Now the Primary server just took a hard dump. Could
> someone tell me how to bring the Secondary server on line so that the
> application can access it?
> Thanks
> RSC
> www.schmooseme.net|||The documents says to restore the logs with NORECOVERY. I have about
100 logs or so, I'm doing the default every 15 min. Do I need to
restore all of them? Or what if I ship every 2 hours, does that mean
at anytime I can lose the last 2 hours when I fail over?
RSC|||You should restore your lastest log with RECOVERY to bring your Secondary
database online in case of a failure that may happen on your Primary
database.
You did not mention what the time period is to restore the logs that are
brought to your secondary server from the primary?
For example:
Step1= Backup Logs on the Primary Server
Step2= Copy Log backups to the Secondary Server from the Primary Server
Step3= Restore copied Logs on the Seconfary Server
There are time intervals between these jobs.
For example: if you perform step1 every 15 min. step2 every 15 min and
perform the step3 every 30 min. (Which is 15 min. for all steps by default)
Then:
13:00 = step1 + step2 + step3
13.15 = step1 + step2
13.30 = step1 + step2 + step3
13.45 = step1 + step2
13.55 = Primary Failed
As the lastest copied transaction log arrived to the Secondary server at
13.45, you'll have it on your Secondary server (if you designated a folder
as a destination copy folder on Secondary server) And you'll just restore
that lastest transaction log because according to this scenario older ones
are already restored (at last at l3.30 in this example)
If you have a chance to take tail log backup from the primary server then
you'll go to your primary server and take tail log backup of your database
and you'll restore it WITH RECOVERY on your Secondary database. If you don't
have this chance then you'll restore your log backup which was copied to
your secondary server at 13.45 using WITH RECOVERY to bring your secondary
database online.
Again, you should apply all availabled and not restored-before transactions
WITH NORECOVERY until the lastest one. When you come to the lastest log
which could be the lastest copied log from the Primary database or Tail Log
from the Primary database, you'll restore it using WITH RECOVERY to bring
your Secondary database online.
--
Ekrem Önsoy
"RSC" <rsceliberti@.hotmail.com> wrote in message
news:9d8cc6ff-57bf-4587-8283-c5725415d1a9@.e67g2000hsc.googlegroups.com...
> The documents says to restore the logs with NORECOVERY. I have about
> 100 logs or so, I'm doing the default every 15 min. Do I need to
> restore all of them? Or what if I ship every 2 hours, does that mean
> at anytime I can lose the last 2 hours when I fail over?
> RSC|||Excellent information!
I have step1 and step2 automated, but I see now that I should automate
step3 on the secondary server, then do a log restore RECOVERY at when
the Primary goes off line. Last but not least; how easy is it to get
the Primary back on line once it is repaired, say after a power supply
failure? I'm thinking, backup the database and restore it on the
Primary, then re-setup the log shipping routine as it was before the
Secondary was recovered. Now I'm back to the begining.
RSC|||After such a failure, your database could be corrupted or damaged. It
probably would be the best to restore the most up to date version of that
database's backup. In your situation the most up to date backup is your
ex-secondary database. And yes, after this you can resetup Log Shipping and
live happily after.
Log shipping is Warm Standby HA system so you must do this stuff manually...
--
Ekrem Önsoy
"RSC" <rsceliberti@.hotmail.com> wrote in message
news:1a50298e-029c-40bd-8e05-01abdf566052@.d4g2000prg.googlegroups.com...
> Excellent information!
> I have step1 and step2 automated, but I see now that I should automate
> step3 on the secondary server, then do a log restore RECOVERY at when
> the Primary goes off line. Last but not least; how easy is it to get
> the Primary back on line once it is repaired, say after a power supply
> failure? I'm thinking, backup the database and restore it on the
> Primary, then re-setup the log shipping routine as it was before the
> Secondary was recovered. Now I'm back to the begining.
> RSC
>|||Thanks for your advice, I think this is my best option. The
application is Websense reporting so any lost data during the failover
or re-setup is not much of a loss anyway.
Thanks Again!
RSC

How to bring Second SQL Log Ship Server Online

Hi -
I have log shipping configured and working.(SQL 2005 SP2). Both
shared directories on the Primary and Secondary servers are full of
logs and the status of the Secondary database is always showing
(Restoring...). Now the Primary server just took a hard dump. Could
someone tell me how to bring the Secondary server on line so that the
application can access it?
Thanks
RSC
www.schmooseme.nethttp://sqlserveruniverse.com/conten...ogShipping.aspx
"RSC" <rsceliberti@.hotmail.com> wrote in message
news:84700042-9e32-48fd-bda3-730434aa8619@.y5g2000hsf.googlegroups.com...
> Hi -
> I have log shipping configured and working.(SQL 2005 SP2). Both
> shared directories on the Primary and Secondary servers are full of
> logs and the status of the Secondary database is always showing
> (Restoring...). Now the Primary server just took a hard dump. Could
> someone tell me how to bring the Secondary server on line so that the
> application can access it?
> Thanks
> RSC
> www.schmooseme.net|||The documents says to restore the logs with NORECOVERY. I have about
100 logs or so, I'm doing the default every 15 min. Do I need to
restore all of them? Or what if I ship every 2 hours, does that mean
at anytime I can lose the last 2 hours when I fail over?
RSC|||You should restore your lastest log with RECOVERY to bring your Secondary
database online in case of a failure that may happen on your Primary
database.
You did not mention what the time period is to restore the logs that are
brought to your secondary server from the primary?
For example:
Step1= Backup Logs on the Primary Server
Step2= Copy Log backups to the Secondary Server from the Primary Server
Step3= Restore copied Logs on the Seconfary Server
There are time intervals between these jobs.
For example: if you perform step1 every 15 min. step2 every 15 min and
perform the step3 every 30 min. (Which is 15 min. for all steps by default)
Then:
13:00 = step1 + step2 + step3
13.15 = step1 + step2
13.30 = step1 + step2 + step3
13.45 = step1 + step2
13.55 = Primary Failed
As the lastest copied transaction log arrived to the Secondary server at
13.45, you'll have it on your Secondary server (if you designated a folder
as a destination copy folder on Secondary server) And you'll just restore
that lastest transaction log because according to this scenario older ones
are already restored (at last at l3.30 in this example)
If you have a chance to take tail log backup from the primary server then
you'll go to your primary server and take tail log backup of your database
and you'll restore it WITH RECOVERY on your Secondary database. If you don't
have this chance then you'll restore your log backup which was copied to
your secondary server at 13.45 using WITH RECOVERY to bring your secondary
database online.
Again, you should apply all availabled and not restored-before transactions
WITH NORECOVERY until the lastest one. When you come to the lastest log
which could be the lastest copied log from the Primary database or Tail Log
from the Primary database, you'll restore it using WITH RECOVERY to bring
your Secondary database online.
Ekrem nsoy
"RSC" <rsceliberti@.hotmail.com> wrote in message
news:9d8cc6ff-57bf-4587-8283-c5725415d1a9@.e67g2000hsc.googlegroups.com...
> The documents says to restore the logs with NORECOVERY. I have about
> 100 logs or so, I'm doing the default every 15 min. Do I need to
> restore all of them? Or what if I ship every 2 hours, does that mean
> at anytime I can lose the last 2 hours when I fail over?
> RSC|||Excellent information!
I have step1 and step2 automated, but I see now that I should automate
step3 on the secondary server, then do a log restore RECOVERY at when
the Primary goes off line. Last but not least; how easy is it to get
the Primary back on line once it is repaired, say after a power supply
failure? I'm thinking, backup the database and restore it on the
Primary, then re-setup the log shipping routine as it was before the
Secondary was recovered. Now I'm back to the begining.
RSC|||After such a failure, your database could be corrupted or damaged. It
probably would be the best to restore the most up to date version of that
database's backup. In your situation the most up to date backup is your
ex-secondary database. And yes, after this you can resetup Log Shipping and
live happily after.
Log shipping is Warm Standby HA system so you must do this stuff manually...
Ekrem nsoy
"RSC" <rsceliberti@.hotmail.com> wrote in message
news:1a50298e-029c-40bd-8e05-01abdf566052@.d4g2000prg.googlegroups.com...
> Excellent information!
> I have step1 and step2 automated, but I see now that I should automate
> step3 on the secondary server, then do a log restore RECOVERY at when
> the Primary goes off line. Last but not least; how easy is it to get
> the Primary back on line once it is repaired, say after a power supply
> failure? I'm thinking, backup the database and restore it on the
> Primary, then re-setup the log shipping routine as it was before the
> Secondary was recovered. Now I'm back to the begining.
> RSC
>|||Thanks for your advice, I think this is my best option. The
application is Websense reporting so any lost data during the failover
or re-setup is not much of a loss anyway.
Thanks Again!
RSC