Database Migration

I have a SQL database called "Acts" on SQL2000 server that
was created by installation of an application. On that SQL
server we have created Logins for this particular "Acts"
database so users can use the application. Management has
now asked us to move this database to another server with
a different server name. My question is can I just
backup "Acts" database on the old server and restore it on
the new server. Will that bring over the logins ID and
their info or if it doesn't what would I have to do to get
that info. Or would I have to backup the entire SQL2000
databases(master, acts, model,msdb,norhtwind,pubs,tempdb)
from my old server and than restore all these databases on
my new server.
Any help is appreciated...
thanks,
JayBackup and restore is a good method to migrate a database. You can also use
sp_detach_db and sp_attach_db and copy the actual database files.
Regardless of which method you choose, you will need to create the logins
for the 'Acts' users on your new server and then link them to the users in
your newly migrated database. Look up sp_change_users_login in BOL for
details.
I strongly discourage migrating system databases between servers. If there
are any scheduled jobs, you can script them and apply them to the new server
much easier than you can fix a migrated MSDB database.
--
Geoff N. Hiten
SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Jay" <desiguys@.hotmail.com> wrote in message
news:0df101c35769$5b6d53e0$a301280a@.phx.gbl...
> I have a SQL database called "Acts" on SQL2000 server that
> was created by installation of an application. On that SQL
> server we have created Logins for this particular "Acts"
> database so users can use the application. Management has
> now asked us to move this database to another server with
> a different server name. My question is can I just
> backup "Acts" database on the old server and restore it on
> the new server. Will that bring over the logins ID and
> their info or if it doesn't what would I have to do to get
> that info. Or would I have to backup the entire SQL2000
> databases(master, acts, model,msdb,norhtwind,pubs,tempdb)
> from my old server and than restore all these databases on
> my new server.
>
> Any help is appreciated...
> thanks,
> Jay
>|||These should help:
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 Server MVP
"Jay" <desiguys@.hotmail.com> wrote in message
news:0df101c35769$5b6d53e0$a301280a@.phx.gbl...
> I have a SQL database called "Acts" on SQL2000 server that
> was created by installation of an application. On that SQL
> server we have created Logins for this particular "Acts"
> database so users can use the application. Management has
> now asked us to move this database to another server with
> a different server name. My question is can I just
> backup "Acts" database on the old server and restore it on
> the new server. Will that bring over the logins ID and
> their info or if it doesn't what would I have to do to get
> that info. Or would I have to backup the entire SQL2000
> databases(master, acts, model,msdb,norhtwind,pubs,tempdb)
> from my old server and than restore all these databases on
> my new server.
>
> Any help is appreciated...
> thanks,
> Jay
>

Database Mail Error "Invalid Parameter"

Hello,

We currently have the 64-bit SQL 2005 SP2 running on our machine. Prior to installation of SP2 we were able to send mail through Database Mail without any problem. Now the mail is successfully cued but not sent. Here is the message in the log:

Description:
The activated proc [dbo].[sp_sysmail_activate] running on queue msdb.dbo.ExternalMailQueue output the following: 'Error executing extended stored procedure: Invalid Parameter'

Does anyone have any thoughts?

Thanks in advance.

Simone

Have you checked the log file after the SP2 application?|||Thank you for the response. I did check the logs and saw nothing out of the ordinary.|||

Have you physically restarted the server?

We find we Database Mail stops working on our 64 bit versions of 2005 with any config change or SP install until after we reboot - then all works okay again.

Regards,

Gary.

|||Interesting. We will try the reboot this weekend. I'll post the result on Monday. Thanks for the response.|||Try restarting SQLAgent alone without the whole SQL services, as this sometimes clears the log behind the process.|||

We restarted the server over the weekend but this did not help. As advised by Satya, I restarted only SQLAgent. These are the only two messages in the log:

configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.

configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.

Mail is still failing to be sent. I am lost.

|||How to configure SQL Server Agent to send job status notifications and alert notifications in SQL Server 2005 64-bit editions

http://support.microsoft.com/kb/908360

SQL Server Agent is not integrated with Database Mail or with SQL Mail in Microsoft SQL Server 2005 64-bit editions. To send any notifications, such as job status notifications and alert notifications, follow the steps in this article to manually configure MAPI for use by SQL Server Agent Mail.

Note In SQL Server 2005 Service Pack 1 (SP1), you can use SQL Server Agent to interact with Database Mail as the mailing system on 64-bit operating systems.

|||I'm not actually using Sql Agent Mail, we are using Database Mail. It did work on this machine a few weeks ago, it just has since stopped working.|||

I believe I found our issue. We didn't receive any error messages stating that SP2 failed, but something did. Procedures in the msdb database were updated to allow for customized parameters (below taken from "What's New in SQL Server 2005 SP2 - http://download.microsoft.com/download/2/B/5/2B5E5D37-9B17-423D-BC8F-B11ECD4195B4/WhatsNewSQL2005SP2.htm):

Database Mail

You can now customize parameters that are passed to DatabaseMail90.exe

Customers and SQL Server Customer Support had difficulty in quickly identifying Database mail misconfigurations. Error reporting has been enhanced. The user will be given much more context to the underlying problems.|||

Although we received no errors when installing SP2, something did fail. The stored procedures did not all copy over. After reinstallation, database mail works as expected. Thank you to all.

Simone

|||
Thanks, we were experiencing the same problem with SP2 intallation failure and Database Mail invalid parameter error message.

Database Mail Error "Invalid Parameter"

Hello,

We currently have the 64-bit SQL 2005 SP2 running on our machine. Prior to installation of SP2 we were able to send mail through Database Mail without any problem. Now the mail is successfully cued but not sent. Here is the message in the log:

Description:
The activated proc [dbo].[sp_sysmail_activate] running on queue msdb.dbo.ExternalMailQueue output the following: 'Error executing extended stored procedure: Invalid Parameter'

Does anyone have any thoughts?

Thanks in advance.

Simone

Have you checked the log file after the SP2 application?|||Thank you for the response. I did check the logs and saw nothing out of the ordinary.|||

Have you physically restarted the server?

We find we Database Mail stops working on our 64 bit versions of 2005 with any config change or SP install until after we reboot - then all works okay again.

Regards,

Gary.

|||Interesting. We will try the reboot this weekend. I'll post the result on Monday. Thanks for the response.|||Try restarting SQLAgent alone without the whole SQL services, as this sometimes clears the log behind the process.|||

We restarted the server over the weekend but this did not help. As advised by Satya, I restarted only SQLAgent. These are the only two messages in the log:

configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.

configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.

Mail is still failing to be sent. I am lost.

|||How to configure SQL Server Agent to send job status notifications and alert notifications in SQL Server 2005 64-bit editions

http://support.microsoft.com/kb/908360

SQL Server Agent is not integrated with Database Mail or with SQL Mail in Microsoft SQL Server 2005 64-bit editions. To send any notifications, such as job status notifications and alert notifications, follow the steps in this article to manually configure MAPI for use by SQL Server Agent Mail.

Note In SQL Server 2005 Service Pack 1 (SP1), you can use SQL Server Agent to interact with Database Mail as the mailing system on 64-bit operating systems.

|||I'm not actually using Sql Agent Mail, we are using Database Mail. It did work on this machine a few weeks ago, it just has since stopped working.|||

I believe I found our issue. We didn't receive any error messages stating that SP2 failed, but something did. Procedures in the msdb database were updated to allow for customized parameters (below taken from "What's New in SQL Server 2005 SP2 - http://download.microsoft.com/download/2/B/5/2B5E5D37-9B17-423D-BC8F-B11ECD4195B4/WhatsNewSQL2005SP2.htm):

Database Mail

You can now customize parameters that are passed to DatabaseMail90.exe

Customers and SQL Server Customer Support had difficulty in quickly identifying Database mail misconfigurations. Error reporting has been enhanced. The user will be given much more context to the underlying problems.|||

Although we received no errors when installing SP2, something did fail. The stored procedures did not all copy over. After reinstallation, database mail works as expected. Thank you to all.

Simone

|||
Thanks, we were experiencing the same problem with SP2 intallation failure and Database Mail invalid parameter error message.

Database Mail Error "Invalid Parameter"

Hello,

We currently have the 64-bit SQL 2005 SP2 running on our machine. Prior to installation of SP2 we were able to send mail through Database Mail without any problem. Now the mail is successfully cued but not sent. Here is the message in the log:

Description:
The activated proc [dbo].[sp_sysmail_activate] running on queue msdb.dbo.ExternalMailQueue output the following: 'Error executing extended stored procedure: Invalid Parameter'

Does anyone have any thoughts?

Thanks in advance.

Simone

Have you checked the log file after the SP2 application?|||Thank you for the response. I did check the logs and saw nothing out of the ordinary.|||

Have you physically restarted the server?

We find we Database Mail stops working on our 64 bit versions of 2005 with any config change or SP install until after we reboot - then all works okay again.

Regards,

Gary.

|||Interesting. We will try the reboot this weekend. I'll post the result on Monday. Thanks for the response.|||Try restarting SQLAgent alone without the whole SQL services, as this sometimes clears the log behind the process.|||

We restarted the server over the weekend but this did not help. As advised by Satya, I restarted only SQLAgent. These are the only two messages in the log:

configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.

configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.

Mail is still failing to be sent. I am lost.

|||How to configure SQL Server Agent to send job status notifications and alert notifications in SQL Server 2005 64-bit editions

http://support.microsoft.com/kb/908360

SQL Server Agent is not integrated with Database Mail or with SQL Mail in Microsoft SQL Server 2005 64-bit editions. To send any notifications, such as job status notifications and alert notifications, follow the steps in this article to manually configure MAPI for use by SQL Server Agent Mail.

Note In SQL Server 2005 Service Pack 1 (SP1), you can use SQL Server Agent to interact with Database Mail as the mailing system on 64-bit operating systems.

|||I'm not actually using Sql Agent Mail, we are using Database Mail. It did work on this machine a few weeks ago, it just has since stopped working.|||

I believe I found our issue. We didn't receive any error messages stating that SP2 failed, but something did. Procedures in the msdb database were updated to allow for customized parameters (below taken from "What's New in SQL Server 2005 SP2 - http://download.microsoft.com/download/2/B/5/2B5E5D37-9B17-423D-BC8F-B11ECD4195B4/WhatsNewSQL2005SP2.htm):

Database Mail

You can now customize parameters that are passed to DatabaseMail90.exe

Customers and SQL Server Customer Support had difficulty in quickly identifying Database mail misconfigurations. Error reporting has been enhanced. The user will be given much more context to the underlying problems.|||

Although we received no errors when installing SP2, something did fail. The stored procedures did not all copy over. After reinstallation, database mail works as expected. Thank you to all.

Simone

|||
Thanks, we were experiencing the same problem with SP2 intallation failure and Database Mail invalid parameter error message.

Database Mail

I am trying to configure Database Mail on a new installation of SQL Server 2005 and the 'DatabaseMailUserRole' does not exist.

How do I add this role to the server?
Hi,

mhmm, strange thing, but use this script to recreate the Role:

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'DatabaseMailUserRole' AND type = 'R')

CREATE ROLE [DatabaseMailUserRole] AUTHORIZATION [dbo]

HTH, Jens Suessmeyer

http://www.sqlserver2005.de

|||Jens,

Thanks for the reply.

I was able to add the role using your script and added a user with the following script -

sp_addrolemember @.rolename = 'DatabaseMailUserRole',
@.membername = '<user name>'

However, it only added the role to the default database. Is there a way to make this a server role that shows up for each database?

Thanks for your help. I'm sure glad there are smart people in the world.
|||Hi,

you only need the role in the msdb database in order to send mails.

HTH, Jens Suessmeyer.

http://wwww.sqlserver2005.de
|||

Jens,

Thanks very much. I think everything is setup now.

Regards,

Dan Holsonback

|||I am having similar problems - I just added the (missing) DatabaseMailUserRole using the example provided. Now I am trying to execute the code in the 'troubleshooting' box

sp_addrolemember @.rolename = "DatabaseMailUserRole',
@.membername = "administrator" (i'm trying to use the administrator email account).

Now I get the error "User or role 'administrator@.avnnb.ca' does not exist in this database." Just what exactly am I supposed to use as membername? I can't find a tiny bit of documentation to help me in this regard.

|||Sorry. no real problem. there was an antivirus program running on mail server, which was stopping the email from getting through.

Database Mail

I am trying to configure Database Mail on a new installation of SQL Server 2005 and the 'DatabaseMailUserRole' does not exist.

How do I add this role to the server?
Hi,

mhmm, strange thing, but use this script to recreate the Role:

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'DatabaseMailUserRole' AND type = 'R')

CREATE ROLE [DatabaseMailUserRole] AUTHORIZATION [dbo]

HTH, Jens Suessmeyer

http://www.sqlserver2005.de

|||Jens,

Thanks for the reply.

I was able to add the role using your script and added a user with the following script -

sp_addrolemember @.rolename = 'DatabaseMailUserRole',
@.membername = '<user name>'

However, it only added the role to the default database. Is there a way to make this a server role that shows up for each database?

Thanks for your help. I'm sure glad there are smart people in the world.
|||Hi,

you only need the role in the msdb database in order to send mails.

HTH, Jens Suessmeyer.

http://wwww.sqlserver2005.de
|||

Jens,

Thanks very much. I think everything is setup now.

Regards,

Dan Holsonback

|||I am having similar problems - I just added the (missing) DatabaseMailUserRole using the example provided. Now I am trying to execute the code in the 'troubleshooting' box

sp_addrolemember @.rolename = "DatabaseMailUserRole',
@.membername = "administrator" (i'm trying to use the administrator email account).

Now I get the error "User or role 'administrator@.avnnb.ca' does not exist in this database." Just what exactly am I supposed to use as membername? I can't find a tiny bit of documentation to help me in this regard.

|||Sorry. no real problem. there was an antivirus program running on mail server, which was stopping the email from getting through.

Database location in crystal report

Hello
i am totally new to crystal reports
Do we need to change database location of crystal report after installation at client side
If this is case then if there are 1000 reports, database location has to be changed for each report?
any there some shortcut to do this programmatically or any other wayHi,

use a DataSethttp://support.businessobjects.com/communityCS/TechnicalPapers/rtm_reportingoffadonetdatasets.pdf

or a ReportIni like this:
Private Sub ReportInit(ByVal blRefreshLocation As Boolean)

Dim crSections As Sections
Dim crSection As Section
Dim crReportObjects As ReportObjects
Dim crReportObject As ReportObject
Dim crSubreportObject As SubreportObject

Dim crReportDocument As ReportDocument
Dim crSubreportDocument As ReportDocument

Dim crDatabase As Database
Dim crTables As Tables
Dim crTable As Table
Dim crTableLogOnInfo As TableLogOnInfo
Dim crConnectioninfo As ConnectionInfo

Dim myArrayList As ArrayList = New ArrayList

'declare an instance of the report and the connectionInfo object

crReportDocument = New ReportDocument
crConnectioninfo = New ConnectionInfo

crReportDocument.Load(mstrReportName)

'pass the necessary parameters to the connectionInfo object
With crConnectioninfo
.ServerName = mstrDBServerName
.DatabaseName = mstrDataBaseName
End With

'set up the database and tables objects to refer to the current report
crDatabase = crReportDocument.Database
crTables = crDatabase.Tables

'loop through all the tables and pass in the connection info
For Each crTable In crTables
crTableLogOnInfo = crTable.LogOnInfo
crTableLogOnInfo.ConnectionInfo = crConnectioninfo
If blRefreshLocation Then
crTable.Location = mstrDataBaseName & ".dbo." &
crTable.Location.Substring(crTable.Location.LastIndexOf(".") + 1)
End If
crTable.ApplyLogOnInfo(crTableLogOnInfo)
Next

'set the crSections object to the current report's sections
crSections = crReportDocument.ReportDefinition.Sections

'loop through all the sections to find all the report objects
For Each crSection In crSections
crReportObjects = crSection.ReportObjects
'loop through all the report objects to find all the subreports
For Each crReportObject In crReportObjects
If crReportObject.Kind = ReportObjectKind.SubreportObject Then
'you will need to typecast the reportobject to a
subreport
'object once you find it
crSubreportObject = CType(crReportObject, SubreportObject)

'open the subreport object
crSubreportDocument =
crSubreportObject.OpenSubreport(crSubreportObject.SubreportName)

'set the database and tables objects to work with the
subreport
crDatabase = crSubreportDocument.Database
crTables = crDatabase.Tables

'loop through all the tables in the subreport and
'set up the connection info and apply it to the tables
For Each crTable In crTables
With crConnectioninfo
.ServerName = mstrDBServerName
End With
crTableLogOnInfo = crTable.LogOnInfo
crTableLogOnInfo.ConnectionInfo = crConnectioninfo
If blRefreshLocation Then
crTable.Location = mstrDataBaseName & ".dbo." &
crTable.Location.Substring(crTable.Location.LastIndexOf(".") + 1)
End If
crTable.ApplyLogOnInfo(crTableLogOnInfo)
Next
End If
Next
Next

crystalReportViewer.ReportSource = crReportDocument

End Sub|||You need to make database connection suggested above or Try to use DSN name