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 How to move database files

If you are able to detach/ attach succesfully, whats the
problem?

>--Original Message--
>For performance reason, I have decided to move my
database files around. I
>have tried using Attach and Detach database to move
physical location of the
>files successfully.
>What I want to do is to move the transaction log file to
a different
>location or even better get rid of it and create a new
log file in its new
>location. I have tried dbcc shrinkfile to truncate the
log file but does
>not allow me to remove it or empty it into another log
file that I have
>created in a new location. Any suggestion on
moving/removing database
>files are appreciated.
>- Mac
>
>.
>I want to drop the log file altogether and start with a new one in a new
location. I could not do this with Detach/Attach as far as I can tell.
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:15fd01c48c53$0680b910$a601280a@.phx.gbl...[vbcol=seagreen]
> If you are able to detach/ attach succesfully, whats the
> problem?
>
> database files around. I
> physical location of the
> a different
> log file in its new
> log file but does
> file that I have
> moving/removing database|||If Im not mistaken, you would:
Detach.
Turn off SQL.
Delete log file.
Turn on SQL.
Attach single file with sp_attach_single_file_db.
DO THIS IN TEST FIRST. IVE NEVER DONE IT!!!

>--Original Message--
>I want to drop the log file altogether and start with a
new one in a new
>location. I could not do this with Detach/Attach as far
as I can tell.
>
>"ChrisR" <anonymous@.discussions.microsoft.com> wrote in
message
>news:15fd01c48c53$0680b910$a601280a@.phx.gbl...
to[vbcol=seagreen]
>
>.
>|||ChrisR wrote:
> If Im not mistaken, you would:
> Detach.
> Turn off SQL.
> Delete log file.
> Turn on SQL.
> Attach single file with sp_attach_single_file_db.
> DO THIS IN TEST FIRST. IVE NEVER DONE IT!!!
I've never had any problems doing this. I'd certainly make a copy of both
the MDF and LDF files before doing it, though.
John.|||Thank you Chris for the suggestion and John confirming the approach.
What do you mean by turn off SQL ? When I Detach a database, isn't it off
?
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:OOOGklFjEHA.3972@.tk2msftngp13.phx.gbl...
> ChrisR wrote:
> I've never had any problems doing this. I'd certainly make a copy of both
> the MDF and LDF files before doing it, though.
> John.
>|||I mean in order to delete the .ldf file after you detach
the db I beleive your going to need to turn off the SQL
service.

>--Original Message--
>Thank you Chris for the suggestion and John confirming
the approach.
>What do you mean by turn off SQL ? When I Detach a
database, isn't it off
>?
>"John McLusky" <jmclusky@.community.nospam> wrote in
message
>news:OOOGklFjEHA.3972@.tk2msftngp13.phx.gbl...
make a copy of both[vbcol=seagreen]
>
>.
>|||Why do you need to start a new log file? If the log file is too large, you
can make it smaller with DBCC SHRINKFILE. You can relocate the log file
using sp_attach_db.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:cgnof3$1uat$1@.si05.rsvl.unisys.com...
>I want to drop the log file altogether and start with a new one in a new
> location. I could not do this with Detach/Attach as far as I can tell.
>
> "ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
> news:15fd01c48c53$0680b910$a601280a@.phx.gbl...
>|||Thanks Dan for your reply. You are right, I do not need to remove it any
longer. I just did not know how to move the file.
-- Mac
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e%23sHGYjjEHA.2340@.TK2MSFTNGP11.phx.gbl...
> Why do you need to start a new log file? If the log file is too large,
you
> can make it smaller with DBCC SHRINKFILE. You can relocate the log file
> using sp_attach_db.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> news:cgnof3$1uat$1@.si05.rsvl.unisys.com...
>

HELP on How to move database files

For performance reason, I have decided to move my database files around. I
have tried using Attach and Detach database to move physical location of the
files successfully.
What I want to do is to move the transaction log file to a different
location or even better get rid of it and create a new log file in its new
location. I have tried dbcc shrinkfile to truncate the log file but does
not allow me to remove it or empty it into another log file that I have
created in a new location. Any suggestion on moving/removing database
files are appreciated.
- Machttp://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:cgnlre$1sd6$1@.si05.rsvl.unisys.com...
> For performance reason, I have decided to move my database files around.
I
> have tried using Attach and Detach database to move physical location of
the
> files successfully.
> What I want to do is to move the transaction log file to a different
> location or even better get rid of it and create a new log file in its new
> location. I have tried dbcc shrinkfile to truncate the log file but does
> not allow me to remove it or empty it into another log file that I have
> created in a new location. Any suggestion on moving/removing database
> files are appreciated.
> - Mac
>

HELP on How to move database files

For performance reason, I have decided to move my database files around. I
have tried using Attach and Detach database to move physical location of the
files successfully.
What I want to do is to move the transaction log file to a different
location or even better get rid of it and create a new log file in its new
location. I have tried dbcc shrinkfile to truncate the log file but does
not allow me to remove it or empty it into another log file that I have
created in a new location. Any suggestion on moving/removing database
files are appreciated.
- MacIf you are able to detach/ attach succesfully, whats the
problem?
>--Original Message--
>For performance reason, I have decided to move my
database files around. I
>have tried using Attach and Detach database to move
physical location of the
>files successfully.
>What I want to do is to move the transaction log file to
a different
>location or even better get rid of it and create a new
log file in its new
>location. I have tried dbcc shrinkfile to truncate the
log file but does
>not allow me to remove it or empty it into another log
file that I have
>created in a new location. Any suggestion on
moving/removing database
>files are appreciated.
>- Mac
>
>.
>|||I want to drop the log file altogether and start with a new one in a new
location. I could not do this with Detach/Attach as far as I can tell.
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:15fd01c48c53$0680b910$a601280a@.phx.gbl...
> If you are able to detach/ attach succesfully, whats the
> problem?
>
> >--Original Message--
> >For performance reason, I have decided to move my
> database files around. I
> >have tried using Attach and Detach database to move
> physical location of the
> >files successfully.
> >
> >What I want to do is to move the transaction log file to
> a different
> >location or even better get rid of it and create a new
> log file in its new
> >location. I have tried dbcc shrinkfile to truncate the
> log file but does
> >not allow me to remove it or empty it into another log
> file that I have
> >created in a new location. Any suggestion on
> moving/removing database
> >files are appreciated.
> >
> >- Mac
> >
> >
> >.
> >|||If Im not mistaken, you would:
Detach.
Turn off SQL.
Delete log file.
Turn on SQL.
Attach single file with sp_attach_single_file_db.
DO THIS IN TEST FIRST. IVE NEVER DONE IT!!!
>--Original Message--
>I want to drop the log file altogether and start with a
new one in a new
>location. I could not do this with Detach/Attach as far
as I can tell.
>
>"ChrisR" <anonymous@.discussions.microsoft.com> wrote in
message
>news:15fd01c48c53$0680b910$a601280a@.phx.gbl...
>> If you are able to detach/ attach succesfully, whats the
>> problem?
>>
>> >--Original Message--
>> >For performance reason, I have decided to move my
>> database files around. I
>> >have tried using Attach and Detach database to move
>> physical location of the
>> >files successfully.
>> >
>> >What I want to do is to move the transaction log file
to
>> a different
>> >location or even better get rid of it and create a new
>> log file in its new
>> >location. I have tried dbcc shrinkfile to truncate the
>> log file but does
>> >not allow me to remove it or empty it into another log
>> file that I have
>> >created in a new location. Any suggestion on
>> moving/removing database
>> >files are appreciated.
>> >
>> >- Mac
>> >
>> >
>> >.
>> >
>
>.
>|||ChrisR wrote:
> If Im not mistaken, you would:
> Detach.
> Turn off SQL.
> Delete log file.
> Turn on SQL.
> Attach single file with sp_attach_single_file_db.
> DO THIS IN TEST FIRST. IVE NEVER DONE IT!!!
I've never had any problems doing this. I'd certainly make a copy of both
the MDF and LDF files before doing it, though.
John.|||http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:cgnlre$1sd6$1@.si05.rsvl.unisys.com...
> For performance reason, I have decided to move my database files around.
I
> have tried using Attach and Detach database to move physical location of
the
> files successfully.
> What I want to do is to move the transaction log file to a different
> location or even better get rid of it and create a new log file in its new
> location. I have tried dbcc shrinkfile to truncate the log file but does
> not allow me to remove it or empty it into another log file that I have
> created in a new location. Any suggestion on moving/removing database
> files are appreciated.
> - Mac
>|||Thank you Chris for the suggestion and John confirming the approach.
What do you mean by turn off SQL ? When I Detach a database, isn't it off
?
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:OOOGklFjEHA.3972@.tk2msftngp13.phx.gbl...
> ChrisR wrote:
> > If Im not mistaken, you would:
> >
> > Detach.
> > Turn off SQL.
> > Delete log file.
> > Turn on SQL.
> > Attach single file with sp_attach_single_file_db.
> >
> > DO THIS IN TEST FIRST. IVE NEVER DONE IT!!!
> I've never had any problems doing this. I'd certainly make a copy of both
> the MDF and LDF files before doing it, though.
> John.
>|||I mean in order to delete the .ldf file after you detach
the db I beleive your going to need to turn off the SQL
service.
>--Original Message--
>Thank you Chris for the suggestion and John confirming
the approach.
>What do you mean by turn off SQL ? When I Detach a
database, isn't it off
>?
>"John McLusky" <jmclusky@.community.nospam> wrote in
message
>news:OOOGklFjEHA.3972@.tk2msftngp13.phx.gbl...
>> ChrisR wrote:
>> > If Im not mistaken, you would:
>> >
>> > Detach.
>> > Turn off SQL.
>> > Delete log file.
>> > Turn on SQL.
>> > Attach single file with sp_attach_single_file_db.
>> >
>> > DO THIS IN TEST FIRST. IVE NEVER DONE IT!!!
>> I've never had any problems doing this. I'd certainly
make a copy of both
>> the MDF and LDF files before doing it, though.
>> John.
>>
>
>.
>|||Why do you need to start a new log file? If the log file is too large, you
can make it smaller with DBCC SHRINKFILE. You can relocate the log file
using sp_attach_db.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:cgnof3$1uat$1@.si05.rsvl.unisys.com...
>I want to drop the log file altogether and start with a new one in a new
> location. I could not do this with Detach/Attach as far as I can tell.
>
> "ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
> news:15fd01c48c53$0680b910$a601280a@.phx.gbl...
>> If you are able to detach/ attach succesfully, whats the
>> problem?
>>
>> >--Original Message--
>> >For performance reason, I have decided to move my
>> database files around. I
>> >have tried using Attach and Detach database to move
>> physical location of the
>> >files successfully.
>> >
>> >What I want to do is to move the transaction log file to
>> a different
>> >location or even better get rid of it and create a new
>> log file in its new
>> >location. I have tried dbcc shrinkfile to truncate the
>> log file but does
>> >not allow me to remove it or empty it into another log
>> file that I have
>> >created in a new location. Any suggestion on
>> moving/removing database
>> >files are appreciated.
>> >
>> >- Mac
>> >
>> >
>> >.
>> >
>|||Thanks Dan for your reply. You are right, I do not need to remove it any
longer. I just did not know how to move the file.
-- Mac
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e%23sHGYjjEHA.2340@.TK2MSFTNGP11.phx.gbl...
> Why do you need to start a new log file? If the log file is too large,
you
> can make it smaller with DBCC SHRINKFILE. You can relocate the log file
> using sp_attach_db.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> news:cgnof3$1uat$1@.si05.rsvl.unisys.com...
> >I want to drop the log file altogether and start with a new one in a new
> > location. I could not do this with Detach/Attach as far as I can tell.
> >
> >
> > "ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
> > news:15fd01c48c53$0680b910$a601280a@.phx.gbl...
> >> If you are able to detach/ attach succesfully, whats the
> >> problem?
> >>
> >>
> >> >--Original Message--
> >> >For performance reason, I have decided to move my
> >> database files around. I
> >> >have tried using Attach and Detach database to move
> >> physical location of the
> >> >files successfully.
> >> >
> >> >What I want to do is to move the transaction log file to
> >> a different
> >> >location or even better get rid of it and create a new
> >> log file in its new
> >> >location. I have tried dbcc shrinkfile to truncate the
> >> log file but does
> >> >not allow me to remove it or empty it into another log
> >> file that I have
> >> >created in a new location. Any suggestion on
> >> moving/removing database
> >> >files are appreciated.
> >> >
> >> >- Mac
> >> >
> >> >
> >> >.
> >> >
> >
> >
>

HELP on How to move database files

If you are able to detach/ attach succesfully, whats the
problem?

>--Original Message--
>For performance reason, I have decided to move my
database files around. I
>have tried using Attach and Detach database to move
physical location of the
>files successfully.
>What I want to do is to move the transaction log file to
a different
>location or even better get rid of it and create a new
log file in its new
>location. I have tried dbcc shrinkfile to truncate the
log file but does
>not allow me to remove it or empty it into another log
file that I have
>created in a new location. Any suggestion on
moving/removing database
>files are appreciated.
>- Mac
>
>.
>
I want to drop the log file altogether and start with a new one in a new
location. I could not do this with Detach/Attach as far as I can tell.
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:15fd01c48c53$0680b910$a601280a@.phx.gbl...[vbcol=seagreen]
> If you are able to detach/ attach succesfully, whats the
> problem?
>
> database files around. I
> physical location of the
> a different
> log file in its new
> log file but does
> file that I have
> moving/removing database
|||If Im not mistaken, you would:
Detach.
Turn off SQL.
Delete log file.
Turn on SQL.
Attach single file with sp_attach_single_file_db.
DO THIS IN TEST FIRST. IVE NEVER DONE IT!!!

>--Original Message--
>I want to drop the log file altogether and start with a
new one in a new
>location. I could not do this with Detach/Attach as far
as I can tell.
>
>"ChrisR" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:15fd01c48c53$0680b910$a601280a@.phx.gbl...
to
>
>.
>
|||ChrisR wrote:
> If Im not mistaken, you would:
> Detach.
> Turn off SQL.
> Delete log file.
> Turn on SQL.
> Attach single file with sp_attach_single_file_db.
> DO THIS IN TEST FIRST. IVE NEVER DONE IT!!!
I've never had any problems doing this. I'd certainly make a copy of both
the MDF and LDF files before doing it, though.
John.
|||Thank you Chris for the suggestion and John confirming the approach.
What do you mean by turn off SQL ? When I Detach a database, isn't it off
?
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:OOOGklFjEHA.3972@.tk2msftngp13.phx.gbl...
> ChrisR wrote:
> I've never had any problems doing this. I'd certainly make a copy of both
> the MDF and LDF files before doing it, though.
> John.
>
|||I mean in order to delete the .ldf file after you detach
the db I beleive your going to need to turn off the SQL
service.

>--Original Message--
>Thank you Chris for the suggestion and John confirming
the approach.
>What do you mean by turn off SQL ? When I Detach a
database, isn't it off
>?
>"John McLusky" <jmclusky@.community.nospam> wrote in
message[vbcol=seagreen]
>news:OOOGklFjEHA.3972@.tk2msftngp13.phx.gbl...
make a copy of both
>
>.
>
|||Why do you need to start a new log file? If the log file is too large, you
can make it smaller with DBCC SHRINKFILE. You can relocate the log file
using sp_attach_db.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:cgnof3$1uat$1@.si05.rsvl.unisys.com...
>I want to drop the log file altogether and start with a new one in a new
> location. I could not do this with Detach/Attach as far as I can tell.
>
> "ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
> news:15fd01c48c53$0680b910$a601280a@.phx.gbl...
>
|||Thanks Dan for your reply. You are right, I do not need to remove it any
longer. I just did not know how to move the file.
-- Mac
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e%23sHGYjjEHA.2340@.TK2MSFTNGP11.phx.gbl...
> Why do you need to start a new log file? If the log file is too large,
you
> can make it smaller with DBCC SHRINKFILE. You can relocate the log file
> using sp_attach_db.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> news:cgnof3$1uat$1@.si05.rsvl.unisys.com...
>

HELP on How to move database files

For performance reason, I have decided to move my database files around. I
have tried using Attach and Detach database to move physical location of the
files successfully.
What I want to do is to move the transaction log file to a different
location or even better get rid of it and create a new log file in its new
location. I have tried dbcc shrinkfile to truncate the log file but does
not allow me to remove it or empty it into another log file that I have
created in a new location. Any suggestion on moving/removing database
files are appreciated.
- Mac
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:cgnlre$1sd6$1@.si05.rsvl.unisys.com...
> For performance reason, I have decided to move my database files around.
I
> have tried using Attach and Detach database to move physical location of
the
> files successfully.
> What I want to do is to move the transaction log file to a different
> location or even better get rid of it and create a new log file in its new
> location. I have tried dbcc shrinkfile to truncate the log file but does
> not allow me to remove it or empty it into another log file that I have
> created in a new location. Any suggestion on moving/removing database
> files are appreciated.
> - Mac
>
sql

Help needed with importing text files

I have a load (180,000+) of text files whose contents need to go into a SQL server database.
Whats the best way of doing this? Using a c# console program and if so, using FileStream or StreamReader? Or using a feature of SQL server itself. The text files themselves are less that 1k and are literally less than 200 characters.
The problem is, I've tried a WinForm and although I can detect what files are there, as soon as I attempt to open one for reading, everything stops working and won't insert anything to the database.

Try this link to use DTS with BCP(bulk copy). Hope this helps.
http://www.sqldts.com/default.aspx?237

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 bring in text files..

background: sql2k on nt5.
can someone give me some advice on how to bring in text files located on a
file server directory into a sql server database?
the text files will always have the same structure, but the file names are
not static (but following a naming standard, such as
location+date.txt, such as
or_05102004
wa_05122004
and so on.
so each file will be broung in as a table such as or_05102004, and
wa_05122004 table.
thank you.
Will you be loading all of the text files into one table, or does each =
type (or, wa, ...) have a different table that it should be loaded into? =
=20
Perhaps you could use BCP or BULK INSERT to import the text files. You =
can define a format file if you choose to use BCP. This format file =
will define the layout of the file and it will determine how it gets =
loaded into the destination table. You could create a stored procedure =
that would take a filename as a param and use a bit of dynamic sql to =
generate the appropriate BCP statement that uses the appropriate format =
file.
Another method would be DTS. You could create a DTS package to insert =
the data into SQL Server. This solution might be difficult to maintain =
if the file names change often. Then again, you could create a routine =
that imports from a standard file name. When you get a new file (on the =
network share) you could copy it to the specific location and file name =
that your DTS package knows about.
--=20
Keith
"=3D=3D Steve Pdx=3D=3D" <lins@.nospam.portptld.com> wrote in message =
news:uCHOz%23sNEHA.1196@.TK2MSFTNGP11.phx.gbl...
> background: sql2k on nt5.
>=20
> can someone give me some advice on how to bring in text files located =
on a
> file server directory into a sql server database?
> the text files will always have the same structure, but the file names =
are
> not static (but following a naming standard, such as
> location+date.txt, such as
> or_05102004
> wa_05122004
> and so on.
>=20
> so each file will be broung in as a table such as or_05102004, and
> wa_05122004 table.
>=20
> thank you.
>=20
>

help needed to bring in text files..

background: sql2k on nt5.
can someone give me some advice on how to bring in text files located on a
file server directory into a sql server database?
the text files will always have the same structure, but the file names are
not static (but following a naming standard, such as
location+date.txt, such as
or_05102004
wa_05122004
and so on.
so each file will be broung in as a table such as or_05102004, and
wa_05122004 table.
thank you.Will you be loading all of the text files into one table, or does each =
type (or, wa, ...) have a different table that it should be loaded into? =
=20
Perhaps you could use BCP or BULK INSERT to import the text files. You =
can define a format file if you choose to use BCP. This format file =
will define the layout of the file and it will determine how it gets =
loaded into the destination table. You could create a stored procedure =
that would take a filename as a param and use a bit of dynamic sql to =
generate the appropriate BCP statement that uses the appropriate format =
file.
Another method would be DTS. You could create a DTS package to insert =
the data into SQL Server. This solution might be difficult to maintain =
if the file names change often. Then again, you could create a routine =
that imports from a standard file name. When you get a new file (on the =
network share) you could copy it to the specific location and file name =
that your DTS package knows about.
--=20
Keith
"=3D=3D Steve Pdx=3D=3D" <lins@.nospam.portptld.com> wrote in message =
news:uCHOz%23sNEHA.1196@.TK2MSFTNGP11.phx.gbl...
> background: sql2k on nt5.
>=20
> can someone give me some advice on how to bring in text files located =
on a
> file server directory into a sql server database?
> the text files will always have the same structure, but the file names =
are
> not static (but following a naming standard, such as
> location+date.txt, such as
> or_05102004
> wa_05122004
> and so on.
>=20
> so each file will be broung in as a table such as or_05102004, and
> wa_05122004 table.
>=20
> thank you.
>=20
>

help needed to bring in text files..

background: sql2k on nt5.
can someone give me some advice on how to bring in text files located on a
file server directory into a sql server database?
the text files will always have the same structure, but the file names are
not static (but following a naming standard, such as
location+date.txt, such as
or_05102004
wa_05122004
and so on.
so each file will be broung in as a table such as or_05102004, and
wa_05122004 table.
thank you.Will you be loading all of the text files into one table, or does each =type (or, wa, ...) have a different table that it should be loaded into? =
Perhaps you could use BCP or BULK INSERT to import the text files. You =can define a format file if you choose to use BCP. This format file =will define the layout of the file and it will determine how it gets =loaded into the destination table. You could create a stored procedure =that would take a filename as a param and use a bit of dynamic sql to =generate the appropriate BCP statement that uses the appropriate format =file.
Another method would be DTS. You could create a DTS package to insert =the data into SQL Server. This solution might be difficult to maintain =if the file names change often. Then again, you could create a routine =that imports from a standard file name. When you get a new file (on the =network share) you could copy it to the specific location and file name =that your DTS package knows about.
-- Keith
"=3D=3D Steve Pdx=3D=3D" <lins@.nospam.portptld.com> wrote in message =news:uCHOz%23sNEHA.1196@.TK2MSFTNGP11.phx.gbl...
> background: sql2k on nt5.
> > can someone give me some advice on how to bring in text files located =on a
> file server directory into a sql server database?
> the text files will always have the same structure, but the file names =are
> not static (but following a naming standard, such as
> location+date.txt, such as
> or_05102004
> wa_05122004
> and so on.
> > so each file will be broung in as a table such as or_05102004, and
> wa_05122004 table.
> > thank you.
> >

Help needed on sum query

i have two table, one containing banking files header (a) information and a
second table containing the banking transactions (b) they are linked by a
LedgerKey field
The banking transaction my have a trans code of either 99 0r 17. Im trying
to get the sum of the header files where transaction trans code = 99, as i
need to knw what the client credit is. There can be several Header records,
but none of the transaction codes are mixed ie one file header will only
have transcode 99 or transcode 17
Select SUM((Cast(a.TransValue as MONEY(11,2))/100))
from Table1 a
JOIN Table2 b
on a.LedgerKey = b.LedgerKey
where a.Licence = 123456
AND b.TransCode = '99'
a.TransValue varchar(11)
a.LedgerKey Varchar(12)
a.Licence Varchar(6)
b.LedgerKey varchar(12)
b.TransCode varchar (2)
in this instance there are 7 records in table b and im getting a SUM result
of 7 x a.TransValue instread if 1 X a.TransValue
Im a bit reluctant to add the ddl as it wil take a few hours to create
sample date, as for ovious reasons i can not post up live account details.Peter Newman wrote:
> i have two table, one containing banking files header (a)
> information and a second table containing the banking transactions
> (b) they are linked by a LedgerKey field
> The banking transaction my have a trans code of either 99 0r 17. Im
> trying to get the sum of the header files where transaction trans
> code = 99, as i need to knw what the client credit is. There can be
> several Header records, but none of the transaction codes are mixed
> ie one file header will only have transcode 99 or transcode 17
> Select SUM((Cast(a.TransValue as MONEY(11,2))/100))
> from Table1 a
> JOIN Table2 b
> on a.LedgerKey = b.LedgerKey
> where a.Licence = 123456
> AND b.TransCode = '99'
> a.TransValue varchar(11)
> a.LedgerKey Varchar(12)
> a.Licence Varchar(6)
> b.LedgerKey varchar(12)
> b.TransCode varchar (2)
> in this instance there are 7 records in table b and im getting a SUM
> result of 7 x a.TransValue instread if 1 X a.TransValue
> Im a bit reluctant to add the ddl as it wil take a few hours to create
> sample date, as for ovious reasons i can not post up live account
> details.
A few hours? It took me five minutes to simulate your situation:
LedgerKey Varchar(12),
TransValue varchar(11),
Licence Varchar(6))
create table #trans (
LedgerKey varchar(12),
TransCode varchar (2))
insert into #header
select 'abc','1000.00','123456'
insert into #trans
select 'abc','99'
union all
select 'abc','99'
union all
select 'abc','99'
union all
select 'abc','99'
union all
select 'abc','99'
union all
select 'abc','99'
union all
select 'abc','99'
go
Select SUM((Cast(a.TransValue as MONEY(11,2))/100))
from #header a
WHERE EXISTS (Select * FROM #trans b
where a.LedgerKey = b.LedgerKey and
a.Licence = 123456
AND b.TransCode = '99')
drop table #header
drop table #trans
HTH,
Bob Barrows
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Help me please! (production DB marked as suspicious)

Hi everybody,

DBMS - MS SQL 6.5
I have removed physical files: Name.DAT and NameLog.DAT
from folder and put older files there with the same names.
After restarting the engine has marked my DB as suspicious.

Then I moved previous files back - database still suspitious!!!

I have no idea how to restore data base status after removing phisical files?

I need help as my MS SQL 6.5 marked as suspicious my production
database.If you can replace the DB by this way,why MS give some backup,restore function?|||If you don't have a valid backup and all you have is the .Dat and .Log files. Try attaching those rather than just replacing the old files. Otherwise have a look at sp_resetstatus.

Cheers|||Sorry, I just noticed that you are on version 6.5. I'm not sure if the info will be valid for that version, you will have to try it to find out.

Thanks,

Originally posted by bmalar
If you don't have a valid backup and all you have is the .Dat and .Log files. Try attaching those rather than just replacing the old files. Otherwise have a look at sp_resetstatus.

Cheers|||True you should use SP_RESETSTATUS, before that use:

sp_configure 'allow updates',1
go
reconfigure with override
go

After the procedure is created, immediately disable updates to the system tables.
sp_configure 'allow updates',0
go

reconfigure with override
go

... then use SP_RESETSTATUS and reboot the server.