Help on report subscription

I got error as below:

Failure sending mail: The report server has encountered a configuration error. See the report server log files for more information.

Then i found error in log file as below:

ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. See the report server log files for more information., AuthzInitializeContextFromSid: Win32 error: 5; possible reason - service account doesn't have rights to check domain user SIDs.;
Info:

Does that mean sth to do with SQL agent running account or SQl Server Report Service running account ?

Thanks

Nick

http://support.microsoft.com/?kbid=842423|||

Thanks for you information. Teo

when i change the Local account for running SQL Server Report Services to dominon account, then i got http://servername/reports to view report, then i got error below:

An error has occurred during report processing. (rsProcessingAborted)
The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled) (rsRPCError)
Bad Data. (Exception from HRESULT: 0x80090005)

What i am understand is that when i create this report it's under local system account , so this report doesn't work when i change the user account. is that right? and how can i handle it ?

Cheers

Nick

|||You will need to re-initialize the server. Assuming RS 2005, use the Reporting Services Configuration (found under the Configuration Tools program group in Microsoft SQL Server 2005 group) to do so. Or, you can drop the decrypted content by running rskeymgmt -d (you will need to reset your data source connection strings after this).|||

when you say re-initialize the server , does that mean i need to delete the existing isntance from reporting service configruation , and create a new one. not just click the initialize button .

is that right understanding?

Cheers

Nick

|||By just clicking on the Initialization button in the Reporting Services Configuration tool.|||

i got erroer when i click initialize button in Report service configuration tool,

"Joining report server ac553f4....... to the web farm of the local instance" The task failed.

Exception Details"

ReportServicesConfigUI.WMIProvider.WMIProviderException: The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled)
at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ThrowOnError(ManagementBaseObject mo)
at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.InitializeReportServer(String installationId)

Thanks

Nick

|||it looks like the only option is to drop the encryption content using rskeymgmt -d. You will need to reset your data source credentials after this.|||

Hi all, I have seen that I have a similar problem with Encryption Key working with reports. My problem is that I′m working with VSt2003 and I have some reports made, but yesterday I was just testing some ASP.NET examples with VS2005. This morning when I continued working with reports in VS2003, I recieved this error:

The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled)

And it wasn′t possible to see the reports.

Do you now the way to fix this problem?

Thanks in Advance

|||I don't know what happened yesterday but somehow you managed to deactivate the server. Re-initializing ASP.NET (aspnet_regiis) will do the trick. Anyway, if you have backed up the encryption key (you did, didn't you?), now is the perfect time to use the Reporting Services Configuration utility and restore it. If you haven't, do rskeymgmt -d.sql

Help on report subscription

I got error as below:

Failure sending mail: The report server has encountered a configuration error. See the report server log files for more information.

Then i found error in log file as below:

ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. See the report server log files for more information., AuthzInitializeContextFromSid: Win32 error: 5; possible reason - service account doesn't have rights to check domain user SIDs.;
Info:

Does that mean sth to do with SQL agent running account or SQl Server Report Service running account ?

Thanks

Nick

http://support.microsoft.com/?kbid=842423|||

Thanks for you information. Teo

when i change the Local account for running SQL Server Report Services to dominon account, then i got http://servername/reports to view report, then i got error below:

An error has occurred during report processing. (rsProcessingAborted)
The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled) (rsRPCError)
Bad Data. (Exception from HRESULT: 0x80090005)

What i am understand is that when i create this report it's under local system account , so this report doesn't work when i change the user account. is that right? and how can i handle it ?

Cheers

Nick

|||You will need to re-initialize the server. Assuming RS 2005, use the Reporting Services Configuration (found under the Configuration Tools program group in Microsoft SQL Server 2005 group) to do so. Or, you can drop the decrypted content by running rskeymgmt -d (you will need to reset your data source connection strings after this).|||

when you say re-initialize the server , does that mean i need to delete the existing isntance from reporting service configruation , and create a new one. not just click the initialize button .

is that right understanding?

Cheers

Nick

|||By just clicking on the Initialization button in the Reporting Services Configuration tool.|||

i got erroer when i click initialize button in Report service configuration tool,

"Joining report server ac553f4....... to the web farm of the local instance" The task failed.

Exception Details"

ReportServicesConfigUI.WMIProvider.WMIProviderException: The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled)
at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ThrowOnError(ManagementBaseObject mo)
at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.InitializeReportServer(String installationId)

Thanks

Nick

|||it looks like the only option is to drop the encryption content using rskeymgmt -d. You will need to reset your data source credentials after this.|||

Hi all, I have seen that I have a similar problem with Encryption Key working with reports. My problem is that I′m working with VSt2003 and I have some reports made, but yesterday I was just testing some ASP.NET examples with VS2005. This morning when I continued working with reports in VS2003, I recieved this error:

The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled)

And it wasn′t possible to see the reports.

Do you now the way to fix this problem?

Thanks in Advance

|||I don't know what happened yesterday but somehow you managed to deactivate the server. Re-initializing ASP.NET (aspnet_regiis) will do the trick. Anyway, if you have backed up the encryption key (you did, didn't you?), now is the perfect time to use the Reporting Services Configuration utility and restore it. If you haven't, do rskeymgmt -d.

Help on 2 Issues

I start SQL with startup parameters -eD:\MSSQL\Log\ERRORLog to put all
general SQL server logs here.
We experienced an error this morning, log entries follow;
2005-10-13 09:12:55.50 spid75 Open of fault log
E:\MSSQL\log\exception.log failed.
2005-10-13 09:12:55.54 spid75 Using 'dbghelp.dll' version '4.0.5'
2005-10-13 09:12:55.56 spid75 Error: 0, Severity: 19, State: 0
2005-10-13 09:12:55.56 spid75 SqlDumpExceptionHandler: Process 75
generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is
terminating this process..
One, any idea what this error could be casued by ... and two, noticed it is
trying to writte teh exception.log to E:\MSSQL\log which (the folder) did
not exist. Shouldn't it be writing to where the rest of the logs go? Is
the -e startup parameter configured wrong?
Also, yesterday during the middle of the day we lost the ability for domain
users to log in. Anyone with an exisitng login was fine. Any users using SQL
server logins were fine, but any domain users that needed to connect could
not.
We were getting the following error.
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
'(null)'. Reason: Not associated with a trusted SQL Server connection.
[Microsoft][ODBC SQL Server Driver]Invalid connection string attribute
A restart of SQL service cleared up the issue, but any idea why this could
happen?
Any assistance is greatly appreciated.Hi
I assume that there is no exception.log in D:\MSSQL\Log\ERRORLog and the
message is incorrect?
You may want to contact PSS regarding this and possibly try running SQLdiag
during the day. Check the windows event log to see if there are any other
messages around this time, you lack of connectivity for windows
authentication may imply your network has a problem.
John
"Mike" wrote:
> I start SQL with startup parameters -eD:\MSSQL\Log\ERRORLog to put all
> general SQL server logs here.
> We experienced an error this morning, log entries follow;
> 2005-10-13 09:12:55.50 spid75 Open of fault log
> E:\MSSQL\log\exception.log failed.
> 2005-10-13 09:12:55.54 spid75 Using 'dbghelp.dll' version '4.0.5'
> 2005-10-13 09:12:55.56 spid75 Error: 0, Severity: 19, State: 0
> 2005-10-13 09:12:55.56 spid75 SqlDumpExceptionHandler: Process 75
> generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is
> terminating this process..
> One, any idea what this error could be casued by ... and two, noticed it is
> trying to writte teh exception.log to E:\MSSQL\log which (the folder) did
> not exist. Shouldn't it be writing to where the rest of the logs go? Is
> the -e startup parameter configured wrong?
> Also, yesterday during the middle of the day we lost the ability for domain
> users to log in. Anyone with an exisitng login was fine. Any users using SQL
> server logins were fine, but any domain users that needed to connect could
> not.
> We were getting the following error.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
> '(null)'. Reason: Not associated with a trusted SQL Server connection.
> [Microsoft][ODBC SQL Server Driver]Invalid connection string attribute
> A restart of SQL service cleared up the issue, but any idea why this could
> happen?
> Any assistance is greatly appreciated.
>
>sql

Help on 2 Issues

I start SQL with startup parameters -eD:\MSSQL\Log\ERRORLog to put all
general SQL server logs here.
We experienced an error this morning, log entries follow;
2005-10-13 09:12:55.50 spid75 Open of fault log
E:\MSSQL\log\exception.log failed.
2005-10-13 09:12:55.54 spid75 Using 'dbghelp.dll' version '4.0.5'
2005-10-13 09:12:55.56 spid75 Error: 0, Severity: 19, State: 0
2005-10-13 09:12:55.56 spid75 SqlDumpExceptionHandler: Process 75
generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is
terminating this process..
One, any idea what this error could be casued by ... and two, noticed it is
trying to writte teh exception.log to E:\MSSQL\log which (the folder) did
not exist. Shouldn't it be writing to where the rest of the logs go? Is
the -e startup parameter configured wrong?
Also, yesterday during the middle of the day we lost the ability for domain
users to log in. Anyone with an exisitng login was fine. Any users using SQL
server logins were fine, but any domain users that needed to connect could
not.
We were getting the following error.
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for
user
'(null)'. Reason: Not associated with a trusted SQL Server connection.
[Microsoft][ODBC SQL Server Driver]Invalid connection string attribu
te
A restart of SQL service cleared up the issue, but any idea why this could
happen?
Any assistance is greatly appreciated.Hi
I assume that there is no exception.log in D:\MSSQL\Log\ERRORLog and the
message is incorrect?
You may want to contact PSS regarding this and possibly try running SQLdiag
during the day. Check the windows event log to see if there are any other
messages around this time, you lack of connectivity for windows
authentication may imply your network has a problem.
John
"Mike" wrote:

> I start SQL with startup parameters -eD:\MSSQL\Log\ERRORLog to put all
> general SQL server logs here.
> We experienced an error this morning, log entries follow;
> 2005-10-13 09:12:55.50 spid75 Open of fault log
> E:\MSSQL\log\exception.log failed.
> 2005-10-13 09:12:55.54 spid75 Using 'dbghelp.dll' version '4.0.5'
> 2005-10-13 09:12:55.56 spid75 Error: 0, Severity: 19, State: 0
> 2005-10-13 09:12:55.56 spid75 SqlDumpExceptionHandler: Process 75
> generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
is
> terminating this process..
> One, any idea what this error could be casued by ... and two, noticed it i
s
> trying to writte teh exception.log to E:\MSSQL\log which (the folder) did
> not exist. Shouldn't it be writing to where the rest of the logs go? Is
> the -e startup parameter configured wrong?
> Also, yesterday during the middle of the day we lost the ability for domai
n
> users to log in. Anyone with an exisitng login was fine. Any users using S
QL
> server logins were fine, but any domain users that needed to connect could
> not.
> We were getting the following error.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed fo
r user
> '(null)'. Reason: Not associated with a trusted SQL Server connection.
> [Microsoft][ODBC SQL Server Driver]Invalid connection string attri
bute
> A restart of SQL service cleared up the issue, but any idea why this could
> happen?
> Any assistance is greatly appreciated.
>
>

Help on 2 Issues

I start SQL with startup parameters -eD:\MSSQL\Log\ERRORLog to put all
general SQL server logs here.
We experienced an error this morning, log entries follow;
2005-10-13 09:12:55.50 spid75 Open of fault log
E:\MSSQL\log\exception.log failed.
2005-10-13 09:12:55.54 spid75 Using 'dbghelp.dll' version '4.0.5'
2005-10-13 09:12:55.56 spid75 Error: 0, Severity: 19, State: 0
2005-10-13 09:12:55.56 spid75 SqlDumpExceptionHandler: Process 75
generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is
terminating this process..
One, any idea what this error could be casued by ... and two, noticed it is
trying to writte teh exception.log to E:\MSSQL\log which (the folder) did
not exist. Shouldn't it be writing to where the rest of the logs go? Is
the -e startup parameter configured wrong?
Also, yesterday during the middle of the day we lost the ability for domain
users to log in. Anyone with an exisitng login was fine. Any users using SQL
server logins were fine, but any domain users that needed to connect could
not.
We were getting the following error.
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
'(null)'. Reason: Not associated with a trusted SQL Server connection.
[Microsoft][ODBC SQL Server Driver]Invalid connection string attribute
A restart of SQL service cleared up the issue, but any idea why this could
happen?
Any assistance is greatly appreciated.
Hi
I assume that there is no exception.log in D:\MSSQL\Log\ERRORLog and the
message is incorrect?
You may want to contact PSS regarding this and possibly try running SQLdiag
during the day. Check the windows event log to see if there are any other
messages around this time, you lack of connectivity for windows
authentication may imply your network has a problem.
John
"Mike" wrote:

> I start SQL with startup parameters -eD:\MSSQL\Log\ERRORLog to put all
> general SQL server logs here.
> We experienced an error this morning, log entries follow;
> 2005-10-13 09:12:55.50 spid75 Open of fault log
> E:\MSSQL\log\exception.log failed.
> 2005-10-13 09:12:55.54 spid75 Using 'dbghelp.dll' version '4.0.5'
> 2005-10-13 09:12:55.56 spid75 Error: 0, Severity: 19, State: 0
> 2005-10-13 09:12:55.56 spid75 SqlDumpExceptionHandler: Process 75
> generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is
> terminating this process..
> One, any idea what this error could be casued by ... and two, noticed it is
> trying to writte teh exception.log to E:\MSSQL\log which (the folder) did
> not exist. Shouldn't it be writing to where the rest of the logs go? Is
> the -e startup parameter configured wrong?
> Also, yesterday during the middle of the day we lost the ability for domain
> users to log in. Anyone with an exisitng login was fine. Any users using SQL
> server logins were fine, but any domain users that needed to connect could
> not.
> We were getting the following error.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
> '(null)'. Reason: Not associated with a trusted SQL Server connection.
> [Microsoft][ODBC SQL Server Driver]Invalid connection string attribute
> A restart of SQL service cleared up the issue, but any idea why this could
> happen?
> Any assistance is greatly appreciated.
>
>

Help needed to restore SQL Database

Hi Gurus,

Can anyone assist?

How to recover the database if I have only the current transaction log and a older version of .mdf & .ldf files. No backup had being performed via Enterprise Manager.

I have installed MSSQL2000 on Windows 2003 Server. Created a new database 'sample' and inserted some data into the 'sample' db. Afterwhich, I offline the 'sample' db and copied the .mdf & .ldf to another backup directory.

Restart the database by bring it online again and continue to insert more data ... (approx 100,000 rows of data).

Afterwhich, I tried to damage the running database .mdf file and the database went into offline / suspect mode.

How can I recovered to the point in time of failure based on just current transactional log & older version of .mdf & .ldf files?

Can any gurus out there advises?If you have not been making backups, you will not be able to recover to a point in time. Recovering to a point in time requires that the database be in the 'Full' recovery model and that you make periodic full backups combined with transaction log backups. The best that you *might* be able to do in this scenario is to perform a single file attach (see sp_attachdb).

Regards,

hmscott

Help needed to attach a DB

I detached a DB, now when I am trying to reattch it I cannot find the
data or the log file. I know I have not deleted anything from the
drives the only thing I am not sure exactly where they were located
prior to the dettach. This is on a test box but we still need to
restore the db. Is there anyway from system tables I could find where
the data file is. I looked for *.mdf files but cannot find the correct
.mdf file. Is it possiblefor the data file to have a differnt
extension? Is it possible for the data file to be located in any
network drive, I did not think this was possible in 2000 but I am using
2005 so I thought I should throw that out there ? Finally will it be
possible to restore the master db then look at the sysdatabse table and
see where it was located prior to the detach, i want to do this only if
it is possible to do so without effecting any other db's on this box.
Any ideas how to troubleshoot this, I am just baffled.
ThanksHi,
By default SQL 2005 uses MDF and LDF extension only. Once you detach the
database all the reference
for that database willl be moved from SQL Server. For me the only way to
identify the location is by restoring the Master database into a new SQL
Server 2005 instance. (Master can be restored only above a Master database in
single user mode). After resting execute the below query to get the
file location of the database you deleted.
select * from sys.master_files
Thanks
Hari
SQL Server MVP
"shub" wrote:
> I detached a DB, now when I am trying to reattch it I cannot find the
> data or the log file. I know I have not deleted anything from the
> drives the only thing I am not sure exactly where they were located
> prior to the dettach. This is on a test box but we still need to
> restore the db. Is there anyway from system tables I could find where
> the data file is. I looked for *.mdf files but cannot find the correct
> ..mdf file. Is it possiblefor the data file to have a differnt
> extension? Is it possible for the data file to be located in any
> network drive, I did not think this was possible in 2000 but I am using
> 2005 so I thought I should throw that out there ? Finally will it be
> possible to restore the master db then look at the sysdatabse table and
> see where it was located prior to the detach, i want to do this only if
> it is possible to do so without effecting any other db's on this box.
> Any ideas how to troubleshoot this, I am just baffled.
> Thanks
>|||Hi
If you have a backup of the database you could use RESTORE FILELISTONLY
command to get the file locations.
John
"shub" wrote:
> I detached a DB, now when I am trying to reattch it I cannot find the
> data or the log file. I know I have not deleted anything from the
> drives the only thing I am not sure exactly where they were located
> prior to the dettach. This is on a test box but we still need to
> restore the db. Is there anyway from system tables I could find where
> the data file is. I looked for *.mdf files but cannot find the correct
> ..mdf file. Is it possiblefor the data file to have a differnt
> extension? Is it possible for the data file to be located in any
> network drive, I did not think this was possible in 2000 but I am using
> 2005 so I thought I should throw that out there ? Finally will it be
> possible to restore the master db then look at the sysdatabse table and
> see where it was located prior to the detach, i want to do this only if
> it is possible to do so without effecting any other db's on this box.
> Any ideas how to troubleshoot this, I am just baffled.
> Thanks
>

Help needed to attach a DB

I detached a DB, now when I am trying to reattch it I cannot find the
data or the log file. I know I have not deleted anything from the
drives the only thing I am not sure exactly where they were located
prior to the dettach. This is on a test box but we still need to
restore the db. Is there anyway from system tables I could find where
the data file is. I looked for *.mdf files but cannot find the correct
.mdf file. Is it possiblefor the data file to have a differnt
extension? Is it possible for the data file to be located in any
network drive, I did not think this was possible in 2000 but I am using
2005 so I thought I should throw that out there ? Finally will it be
possible to restore the master db then look at the sysdatabse table and
see where it was located prior to the detach, i want to do this only if
it is possible to do so without effecting any other db's on this box.
Any ideas how to troubleshoot this, I am just baffled.
ThanksHi,
By default SQL 2005 uses MDF and LDF extension only. Once you detach the
database all the reference
for that database willl be moved from SQL Server. For me the only way to
identify the location is by restoring the Master database into a new SQL
Server 2005 instance. (Master can be restored only above a Master database i
n
single user mode). After resting execute the below query to get the
file location of the database you deleted.
select * from sys.master_files
Thanks
Hari
SQL Server MVP
"shub" wrote:

> I detached a DB, now when I am trying to reattch it I cannot find the
> data or the log file. I know I have not deleted anything from the
> drives the only thing I am not sure exactly where they were located
> prior to the dettach. This is on a test box but we still need to
> restore the db. Is there anyway from system tables I could find where
> the data file is. I looked for *.mdf files but cannot find the correct
> ..mdf file. Is it possiblefor the data file to have a differnt
> extension? Is it possible for the data file to be located in any
> network drive, I did not think this was possible in 2000 but I am using
> 2005 so I thought I should throw that out there ? Finally will it be
> possible to restore the master db then look at the sysdatabse table and
> see where it was located prior to the detach, i want to do this only if
> it is possible to do so without effecting any other db's on this box.
> Any ideas how to troubleshoot this, I am just baffled.
> Thanks
>|||Hi
If you have a backup of the database you could use RESTORE FILELISTONLY
command to get the file locations.
John
"shub" wrote:

> I detached a DB, now when I am trying to reattch it I cannot find the
> data or the log file. I know I have not deleted anything from the
> drives the only thing I am not sure exactly where they were located
> prior to the dettach. This is on a test box but we still need to
> restore the db. Is there anyway from system tables I could find where
> the data file is. I looked for *.mdf files but cannot find the correct
> ..mdf file. Is it possiblefor the data file to have a differnt
> extension? Is it possible for the data file to be located in any
> network drive, I did not think this was possible in 2000 but I am using
> 2005 so I thought I should throw that out there ? Finally will it be
> possible to restore the master db then look at the sysdatabse table and
> see where it was located prior to the detach, i want to do this only if
> it is possible to do so without effecting any other db's on this box.
> Any ideas how to troubleshoot this, I am just baffled.
> Thanks
>

Help needed in a Query

I have a query that need to do something like this.

About the data:
-----
I have a rules table which has got all the rules, and a log table that has the logs which says whether the rules are satisfied or not over a point of time.

select rules, sum(decode(status,'pass','1')) as passed, sum(decode(status,'fail',1)) as failed from rulestable,logstable where rulestable.logid=logstable.id

My expected Output
----------
Rule1 passed= 10 failed=12
Rule2 passed=11 failed=15

But i donot have decode() function in mssql nor can I use IF ELSE here. Can anyone please tell me how this can be implemented here?

Thanksselect rules, sum(decode(status,'pass','1')) as passed, sum(decode(status,'fail',1)) as failed from rulestable,logstable where rulestable.logid=logstable.id

But i donot have decode() function in mssql nor can I use IF ELSE here. Can anyone please tell me how this can be implemented here?

Thanks select rules,
sum(CASE status WHEN 'pass' THEN 1 ELSE 0 END) as passed,
sum(CASE status WHEN 'failed' THEN 1 ELSE 0 END) as failed
from rulestable INNER JOIN logstable ON rulestable.logid=logstable.id
ANSII Joins used too. HTH|||Thanks a lot. It worked.

help needed for Recovring database

I wanted to move my database log file to another drive, but unfortunately I
executed wrong command .
alter database ABC modify file
(name = 'ABC_log',filename= 'D:\Program Files\Microsoft SQL
Server\MSSQL\data\ABC_log.LDF')
instead of
alter database ABC2 modify file
(name = 'ABC2_log',filename= 'D:\Program Files\Microsoft SQL
Server\MSSQL\data\ABC2_log.LDF')
After this, I found my database in Suspect mode, so I executed these commands
sp_configure 'allow updates', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_resetstatus 'ABC2'
And restarted DB services, but database was still in suspect mode, after
checking every possibility, I detached that database, I don’t know why I did
this.
But now I am not able to attach database back. When I try doing this it’s
give me following error
Error 5173: Cannot associate files with different databases.
By the way when I attach this mdf file, it changes the logical file name to
ABC_Data.mdf
But when I execute following query, it displays different logical file name
with ABC.mdf
dbcc checkprimaryfile (N'D:\Program Files\Microsoft SQL
Server\MSSQL\Data\ABC2.mdf', 3)
I believe if I change this logical file name I’ll be able to attach database
back.
Kindly help me the recovering database.
Note: I do not have the transaction log backup & current DB backup.
Farhan Iqbal
Error 5173 means, you are trying to attach files that belong to another
database. Check the file names that you are using for your attach.
"Farhan" <Farhan@.discussions.microsoft.com> wrote in message
news:89333AE6-EE72-4863-A73B-C9419112318C@.microsoft.com...
>I wanted to move my database log file to another drive, but unfortunately I
> executed wrong command .
> alter database ABC modify file
> (name = 'ABC_log',filename= 'D:\Program Files\Microsoft SQL
> Server\MSSQL\data\ABC_log.LDF')
> instead of
> alter database ABC2 modify file
> (name = 'ABC2_log',filename= 'D:\Program Files\Microsoft SQL
> Server\MSSQL\data\ABC2_log.LDF')
>
> After this, I found my database in Suspect mode, so I executed these
> commands
> sp_configure 'allow updates', 1
> GO
> RECONFIGURE WITH OVERRIDE
> GO
> EXEC sp_resetstatus 'ABC2'
> And restarted DB services, but database was still in suspect mode, after
> checking every possibility, I detached that database, I don't know why I
> did
> this.
> But now I am not able to attach database back. When I try doing this it's
> give me following error
> Error 5173: Cannot associate files with different databases.
> By the way when I attach this mdf file, it changes the logical file name
> to
> ABC_Data.mdf
> But when I execute following query, it displays different logical file
> name
> with ABC.mdf
> dbcc checkprimaryfile (N'D:\Program Files\Microsoft SQL
> Server\MSSQL\Data\ABC2.mdf', 3)
> I believe if I change this logical file name I'll be able to attach
> database
> back.
> Kindly help me the recovering database.
> Note: I do not have the transaction log backup & current DB backup.
> Farhan Iqbal
>

Help me! The log file for database is full

Hi,
Im working with a sql server 2000 bd and i have a bd with simple recovery model. Each day i have the next error:

"The log file for database x is full. Backup the transaction log for the database to free up some log space"

I tried to limit the transaction log file to 500Mb but then I have this error. I have done the reduction manually of transaction log file but the next day i have got the same error. If i dont try to limit, this file grows a lot of (1GB) and then i havent got enough disk space. Can you help me, please?

Thanks a lot.
MemupiI will offer you 2 solutions

1- if you wish to retain the log file information, then bak it up regularly. This will mean that SQL server will reuse the log file space it has backed up. The log file may still grow, although it should level out.

2 - if you don't care about the information held in the log file (I suggest this is true based on your simple recovery model) then you can set this via Query Analyzer

exec sp_dboption $DB, 'trunc. log on chkpt.', 'on'

This will throw away log file segments where all the transactions are committed, and as such keep your log file small. Note it then means that you cannot recover using the transaction log file using this method.

Hope this answers your question|||I have tested the value of this option using the following select:
SELECT DATABASEPROPERTY ('Northwind', 'IsTruncLog')

and the returned value was '1'.

Also if i see the options of database i can see that the option "autoshrink" also is set.

Then, i dont know what is the problem.

Thanks a lot.

Originally posted by dbabren
I will offer you 2 solutions

1- if you wish to retain the log file information, then bak it up regularly. This will mean that SQL server will reuse the log file space it has backed up. The log file may still grow, although it should level out.

2 - if you don't care about the information held in the log file (I suggest this is true based on your simple recovery model) then you can set this via Query Analyzer

exec sp_dboption $DB, 'trunc. log on chkpt.', 'on'

This will throw away log file segments where all the transactions are committed, and as such keep your log file small. Note it then means that you cannot recover using the transaction log file using this method.

Hope this answers your question|||OK - try dbcc loginfo in Query Analyzer in the db you are having probs with. This returns a status field (amoungst others) - 2 is active 0 is inactive. If all the segments are active then the log file will have to grow - it also suggestes that the truncate is not happening. Inactive segments will be reused

Another point to note (re autoshrink) - the log file can only shrink from the end backwards - ie if the active segement is at the end of the file it eill not shrink.|||I have executed loginfo query and the result is only one active segment. But, this segment is the last. Then, the shrink is not effective?? What can i do at this point?

But if i'd execute a manual command to shrink only the log transaction directly from Sql enterprise, the log file would be shorter. What is the reason? I can test this point.

On the other hand, i have limited the file to 500Mb. What happens if i execute a big transaction and i dont have enough space in the log file to save all ? Can i have this problems? When i didnt limite the space of log file i didnt have any error (the problem of space disk, of course).

Originally posted by dbabren
OK - try dbcc loginfo in Query Analyzer in the db you are having probs with. This returns a status field (amoungst others) - 2 is active 0 is inactive. If all the segments are active then the log file will have to grow - it also suggestes that the truncate is not happening. Inactive segments will be reused

Another point to note (re autoshrink) - the log file can only shrink from the end backwards - ie if the active segement is at the end of the file it eill not shrink.|||where the active segment is last, I usually create a dummy table and update the columns in it until the active segment "moves". Using the dbcc command I can track this.

As for a log file of 500Mb - few transactions would require this much space I think, althoughI suspect the transaction would fail if you exceed your imposed limit - def if there is no disk space|||I have tested this, and always that the active segment changes it become a new segment that it is the last. I refer to the last segment as the last FSegNo.

Can i remove the transaction log or config to not use?

Originally posted by dbabren
where the active segment is last, I usually create a dummy table and update the columns in it until the active segment "moves". Using the dbcc command I can track this.

As for a log file of 500Mb - few transactions would require this much space I think, althoughI suspect the transaction would fail if you exceed your imposed limit - def if there is no disk space|||Or how can i change the transaction file to another disk? Then i could not limit the log.

Originally posted by memupi
I have tested this, and always that the active segment changes it become a new segment that it is the last. I refer to the last segment as the last FSegNo.

Can i remove the transaction log or config to not use?|||Can't not use a transaction log - not possible

You can add a second log file through ent manager quite easily. Never actually moved a log file although should be able to - have a look in BOL - alter database command perhaps.|||OK. Thanks for all. You has helped me a lot.

Originally posted by dbabren
Can't not use a transaction log - not possible

You can add a second log file through ent manager quite easily. Never actually moved a log file although should be able to - have a look in BOL - alter database command perhaps.|||Hi

since ur drive is running out of space.
u can free up some space in which the log file is already present
else look out for another which is free of space
1. create a folder to store logfiles
2. go to the properties of the job that is taking the backup of log
3. go to edit and change the location (drive) from previous to present drive where there is enough space

hope it will work

help me Please!

Hi There!!

I have a code that runs from a different machine and a different site and i am facing a problem that i can not log in to the database.I installed SQL Server on my machine and I created a user "fadila" and SQL Server Authentication and the password is "fadil1977" and the database is "otters" and it is installed as tables and stored procedures but there is not data on these tables.

I used the method provided in the code so, i only change the "connStr" in one place rather than in 20 places and my code is as below. Can you please Help me to connect to the database. I Really..Really appreciate it if you help me to solve it as it causeing me a big head-ache and still get the error message "SQL Server does not exist or access denied" .. please help!!!!

 Friend Shared ReadOnly Property connStr() As String
 Get
 Return String.Format( _
 "Data Source={0};Initial Catalog=Otters;User ID=fadila;Password=fadil1977", _
 DatabaseMachine)
 End Get
 End Property

your help is appreciated!!!!!!Sure you can connect to the sql server?

Possible reasons:

* Firewall in between.
* Stupid admin. One who did not read that syou need a special service pack (SP3?) to run on 2003, and Winwodows Server 2003 actively blocks SQL Server ports unless this sp is installewd (at least).
* Bad setup for the network, not allowing name resolution.
* Bad database machine name?

Do you homework - there is nothing we can do here to help you, simply because we have no clue what the error is, simply because you do not provide enough info.|||Hi Thona!!

Thank you very much for your help and answering my question. You just said there is no enough information provided, can you please tell me what information is needed so i can post up for you. I just want to make one point, I used before in the previous project the dataset and connect to the database without any problem so, is that what you told me, it does not apply to the dataset and SqlAdaptor as it is a different way toconnect so, i will not have this problem.

I will speak to the IT admin about the you told me and get back to you so, please keep looking to this thread until you solve it for me or you get fed up :)

Thanks again