Alter failed for Database 'databasename'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The server network address "TCP://Mirrorserver.domainname.local:5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=1418&LinkId=20476
when I tried to mirror database giving principal and mirror server names every details I am getting the error
'Database mirroring is disabled by default. Database mirroring is currently provided for evaluation purposes only and is not to be used in production environments.To enable database mirroring for evaluation purposes, use trace flag 1400 during startup.'
How to user trace flag 1400 during startup?
Since I am newbie Kindly guide me with step by step procedure.
When I configured database mirroring between two servers in separate DMZ regions, I get the following error on the principle server when I execute the ALTER DATABASE xxxxxxxx SET PARTNER = '****':
The server network address %%% can not be reached or does not exist. Check the network address name and reissue the command. (Microsoft SQL Server, Error: 1418)
Ports have been opened on both machines and I can TELNET both without any problems. I have included the steps that I used.
DATABASE MIRRORING TEST CONFIGURATION SQL Server 2005 STD Principal: AA-AAA-AA01 Mirror: BB-BBB-BB07
A. Create certificates for outbound connection on principal server. Refer to http://msdn2.microsoft.com/en-us/library/ms186384.aspx. 1. In the master database, create a database Master Key. M:DBADMMIRRORINGCREATE_MAST_KEY_ENCRY USE master; CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<1_Strong_Password!>'; GO
2. In the master database, create an encrypted certificate on the server instance. M:DBADMMIRRORINGCREATE_AA01_MIRRORING_CERT USE master; CREATE CERTIFICATE AA_AAA_AA01_MIRRORING_CERT WITH SUBJECT = 'AA-AAA-AA01 certificate for database mirroring'; GO SELECT * FROM SYS.CERTIFICATES GO
3. Create an endpoint for the server instance using its certificate. M:DBADMMIRRORINGCREATE_AA01_ENDPOINT CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED AS TCP ( LISTENER_PORT=5999 , LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE AA_AAA_AA01_MIRRORING_CERT , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL ); GO
4. Back up the certificate to a file and securely copy it to the other system or systems. M:DBADMMIRRORINGCREATE_AA01_CERT_BKUP BACKUP CERTIFICATE AA_AAA_AA01_MIRRORING_CERT TO FILE = 'M:DBADMMIRRORINGAA_AAA_AA01_MIRRORING_CERT_BKUP.cer'; GO
B. Create certificates for outbound connection on mirror server. Refer to http://msdn2.microsoft.com/en-us/library/ms186384.aspx. 1. In the master database, create a database Master Key. M:DBADMMIRRORINGCREATE_MAST_KEY_ENCRY USE master; CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<1_Strong_Password!>'; GO
2. In the master database, create an encrypted certificate on the server instance. M:DBADMMIRRORINGCREATE_BB07_MIRRORING_CERT USE master; CREATE CERTIFICATE BB_BBB_BB07_MIRRORING_CERT WITH SUBJECT = 'BB-BBB-BB07 certificate for database mirroring'; GO SELECT * FROM SYS.CERTIFICATES GO
3. Create an endpoint for the server instance using its certificate. M:DBADMMIRRORINGCREATE_BB07_ENDPOINT CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED AS TCP ( LISTENER_PORT=5999 , LISTENER_IP = PARNTER ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE BB_BBB_BB07_MIRRORING_CERT , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL ); GO
4. Back up the certificate to a file and securely copy it to the other system or systems. M:DBADMMIRRORINGCREATE_BB07_CERT_BKUP BACKUP CERTIFICATE BB_BBB_BB07_MIRRORING_CERT TO FILE = 'M:DBADMMIRRORINGBB_BBB_BB07_MIRRORING_CERT_BKUP.cer'; GO
C. Configure server instances inbound mirroring connections on principal server. Refer to http://msdn2.microsoft.com/en-us/library/ms187671.aspx.
1. Create a login for other system. M:DBADMMIRRORINGCREATE_BB07_LOGIN USE master; CREATE LOGIN BB07_MIRROR_ADMIN WITH PASSWORD = '1Sample_Strong_Password!@#'; GO
2. Create a user for that login. M:DBADMMIRRORINGCREATE_BB07_USER USE master; CREATE USER BB07_MIRROR_ADMIN FOR LOGIN BB07_MIRROR_ADMIN; GO
3. Obtain the certificate for the mirroring endpoint of the other server instance.
4. Associate the certificate with the user created in step 2. M:DBADMMIRRORINGCREATE_BB07_USER_CERT_LINK USE master; CREATE CERTIFICATE BB_BBB_BB07_MIRRORING_CERT AUTHORIZATION BB07_MIRROR_ADMIN FROM FILE = 'M:DBADMMIRRORINGBB_BBB_BB07_MIRRORING_CERT_BKUP.cer' GO
5. Grant CONNECT permission on the login for that mirroring endpoint. M:DBADMMIRRORINGGRANT_BB07_LOG_ACCESS USE master; GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO BB07_MIRROR_ADMIN; GO
D. Configure server instances inbound mirroring connections on master server. Refer to http://msdn2.microsoft.com/en-us/library/ms187671.aspx.
1. Create a login for other system. M:DBADMMIRRORINGCREATE_AA01_LOGIN USE master; CREATE LOGIN AA01_MIRROR_ADMIN WITH PASSWORD = '1Sample_Strong_Password!@#'; GO
2. Create a user for that login. M:DBADMMIRRORINGCREATE_AA01_USER USE master; CREATE USER AA01_MIRROR_ADMIN FOR LOGIN AA01_MIRROR_ADMIN; GO
3. Obtain the certificate for the mirroring endpoint of the other server instance.
4. Associate the certificate with the user created in step 2.
M:DBADMMIRRORINGCREATE_AA01_USER_CERT_LINK USE master; CREATE CERTIFICATE AA_AAA_AA01_MIRRORING_CERT AUTHORIZATION AA01_MIRROR_ADMIN FROM FILE = 'M:DBADMMIRRORINGAA_AAA_AA01_MIRRORING_CERT_BKUP.cer' GO
5. Grant CONNECT permission on the login for that mirroring endpoint. M:DBADMMIRRORINGGRANT_AA01_LOG_ACCESS USE master; GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO AA01_MIRROR_ADMIN; GO
E. Back up the Principal Database and copy backup files to mirror server.
F. Restore the Principal Database on the standby database server (use NORECOVERY).
G. Configure the mirroring partners. Refer to http://msdn2.microsoft.com/en-us/library/ms191140.aspx.
1. On the mirror server instance on BB-BBB-BB07, set the server instance on AA-AAA-AA01 as the partner (making it the initial principal server instance).
M:DBADMMIRRORINGACTIVATE_MIRROR_FOR_AA01 --At BB-BBB-BB07, set server instance on AA-AAA-AA01 as partner (principal server): ALTER DATABASE BESmgmt SET PARTNER = 'TCP://AA-AAA-AA01.mycompany.com:5999'; GO
2. On the principal server instance on AA-AAA-AA01, set the server instance on BB-BBB-BB07 as the partner (making it the initial mirror server instance).
M:DBADMMIRRORINGACTIVATE_MIRROR_FOR_BB07 --At AA-AAA-AA01, set server instance on BB-BBB-BB07 as partner (mirror server). ALTER DATABASE BESmgmt SET PARTNER = 'TCP://BB-BBB-BB07.mycompany.com:5999'; GO
3. Execute the following SELECT on both servers to review mirroring information. SELECT * FROM SYS.DATABASE_MIRRORING
OS:Windows XP Professional Edition SQLServer: 2005(DE).SP2(Have installed 2 instances of 2005 in my local system) I am trying to do database mirroring,via SQL Server Management Studio. 1)configure security setup,(showed success). 2)when i start mirroring,i am getting the following error.
Alter failed for Database 'MIRROR_DEMO'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The server network address "TCP://tp190.DELL.servers.corp:5023" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=1418&LinkId=20476
------------------------------ BUTTONS:
OK ------------------------------
I have followed the microsoft link and i have enabled the port numbers aswell.
We have recently set up two SQL Server 2005 Standard Edition serverswith database mirroring. The mirrors function and fail over without aproblem, but the Database Mirroring Monitor Job fails every time withthe following error message:Incorrect syntax near '.'. [SQLSTATE 42000] (Error 102) Incorrectsyntax near the keyword 'with'. If this statement is a common tableexpression or an xmlnamespaces clause, the previous statement must beterminated with a semicolon. [SQLSTATE 42000] (Error 319). The stepfailed.Both SQL Servers are running SP2 with the latest patches.Can anyone help with the resolutionto this this issue?Thank you!Bosko
We have set up two SQL Server 2005 Standard Edition servers with database mirroring. The mirrors function and fail over without a problem, but the Database Mirroring Monitor Job fails every time with the following error message:
Incorrect syntax near 'Virtual'. [SQLSTATE 42000] (Error 102) Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause<c/> the previous statement must be terminated with a semicolon. [SQLSTATE 42000] (Error 319). The step failed.,00:00:01,15,319,,,,0 Both SQL Servers are running SP2 with the latest patches.
I am trying to configure Database Mirroring. I had do set dbcc traceon(1400,-1) manually using tsql as the server does not start when configring the startup parameter using the advanced properties of sql server configuration using -T 1400.
After using the db-mirror properties and the wizard in management studio i get an error "database mirroring transport is disabled in the endpoint configuration". I also get this error when executing
Hello, I am trying to mirror two databases and i receive the following error to the principal error log. "Error: 1443, Severity: 16, State: 2 ".
I HAVE: 1) SQL server 2005 enterprise SP2 2)ServerA And ServerB in the domain. 3)I have installed both ServerA and ServerB sql server with network system account and the services run both as NT AUTHORITYNETWORK SECURITY. 4) The principal database "Test" is on ServerA, i create a full backup and log backup and restore to ServerB the Full backup and the Log with NORECOVERY.So the database "Test" in ServerB is Restoring...... 5)I Have created to both Servers Endpoint to Port 5022 with AUTHORIZATION=NT AUTHORITYNETWORK SECURITY. 6)The endpoint are both operational (I have tested through telnet command).... 7)I have added NT AUTHORITYNETWORK SECURITY user to both Security>logins and i gave full permittions sysadmin to the user. 8)I am executing the query "Alter database [TEST] set partner= N'TCP://xx.xx.xx.xx:5022'"; successfully from ServerB (Mirror) but from ServerA i receive that error!!!!
I believe it's network account security issue, but i cannot find a solution for that..... (I also tried to add DCServerA$ user to logins on ServerB AND DCServerB$ user to logins on ServerA cause previously i recaieved an login security error!!!)
I am getting the following error when creating a endpoint (that will be used for allwayson) The Database Mirroring endpoint cannot listen for connections due to the following error: '10049(The requested address is not valid in its context.)'.
My physical network card is configured with:
10.9.255.170
and iam creating my endpoint with 10.9.255.82
The creation of endpoint is being done with;
CREATE ENDPOINT [Hadr_endpoint] AS TCP (LISTENER_PORT = 5022, LISTENER_IP = (10.9.255.82) FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES) GO IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') <> 0 BEGIN ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED END GO use [master] GO GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [account] GO
I attempted to setup database mirroring using a High Availability scenario but when I installed SQL is chose to use local system accounts for all the services. Consequently, I stubled upon a microsoft article explaining how to setup mirroring using local system accounts and certificate authentication but I am stil not able to get it to work. When I try ti initiate the mirror from the mirror server I receive an error stating "Neither the partner nor the witness server instance for database "EDENLive" is available. Reissue the command when at least one of the instances becomes available." I have checked all the endpoints and everything seems to be in order. I even checked to make sure that each server was listening on the appropriate ports and I AM able to telnet to the ports. Please help!
Using SQL Server 2008, we would like propose mirroring between two servers of a critical database. Since we initiate, may require to clarify on its purpose and also required changes from application end.Any changes required from OS Level? (I believe both servers IP or Host name should be added in host entries. Mirroring ports should be allowed/open including Principal and mirror server IP Addresses): Windows Team.Any changes required from Application? (Instance name, authentication: user name and its password should be added in web config files): Application Team.Any changes required from Network Team?Also for mirroring both the principal and mirror servers should be with same version, does it only mean SQL Server 2008 versions are enough or does it also mean to say build numbers 10.00.4000 should also be same.URL....
While configuring reporting services, in the database setup section, I am entering the instance name connecting to, and the user-id details. On applying the changes, i am getting an error that "Using other edition of database for reporting data sources and/or report server databases is not allowed in this version of Reporting services"
I've two servers on which there is the SQL 2000 Server and the IIS 6.0.
I installed the SQL Server Client Tools on web server.
And created the nwind as told in booksonline.
I declared the SQLXML extention and allowed it using IIS 6.
But there is a problem with the virtual directory I created using the tool "Configure XML Support in IIS". Web server doesn't realize it as a virtual directory. Even a jpg or a text file is not served by the IIS.
The web server shows the following error.
ERROR: 500 Internal Server Error HResult: 0x800706f4 Source: Microsoft SQL isapi extension Description: Invalid virtual root settings
I am trying to configure DB mirrorring using the SQLCMD & am getting an error :
Msg 1416, Level 16, State 31, Line 1
Database "Test" is not configured for database mirroring.
All the other steps execute perfectly fine.. the endpoints are created properly... & this fails when I try to give a network server addres with the Port No on the Principal ... I have given it first on the Mirror & that runs fine....
Its fails on the Principal .... also .. when I run the DB Mirrorring Wizard it runs fine .. there is no problem ... want to do it via script .. can some one please help...
Thanks
-- Declare variabled for the Principal, Mirror Servers & the DB that needs to be Mirrorred alongwith the backup path. ETVAR MyPrincipalServer "ServerMY2005" ETVAR MyMirrorServer "ServerMY2005QA" ETVAR Database2Mirror "Test" ETVAR BackupPath "C:" ETVAR Permit "::[Mirroring]"
-- Create the Endpoints on the Principal & the Mirror Server :CONNECT $(MyPrincipalServer) CREATE ENDPOINT [Mirroring] AS TCP (LISTENER_PORT = 5022) FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION = REQUIRED ALGORITHM RC4) GO ALTER ENDPOINT [Mirroring] STATE = STARTED GO
:CONNECT $(MyMirrorServer) CREATE ENDPOINT [Mirroring] AS TCP (LISTENER_PORT = 5023) FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION = REQUIRED ALGORITHM RC4) GO ALTER ENDPOINT [Mirroring] STATE = STARTED GO
-- COnnect to the Principal & Set Recvery Model & Take FUll Backup of the DB :CONNECT $(MyPrincipalServer) GRANT CONNECT ON ENDPOINT $(PERMIT) TO [ServiceAccount] go ALTER DATABASE $(Database2Mirror) SET RECOVERY FULL GO CHECKPOINT GO BACKUP DATABASE $(Database2Mirror) TO DISK = '$(BackupPath)$(Database2Mirror).bak' WITH INIT GO
-- Restoring the Backup file to the Mirror Server with NORECOVERY Need to take care of the With MOVE Option. :CONNECT $(MyMirrorServer) GRANT CONNECT ON ENDPOINT $(PERMIT) TO [ServiceAccount] go RESTORE DATABASE $(Database2Mirror) FROM DISK = '$(BackupPath)$(Database2Mirror).bak' with norecovery, move 'Test' to 'C:Test.mdf', move 'Test_log' to 'c:Test.ldf' GO
:CONNECT $(MyPrincipalServer) BACKUP Log $(Database2Mirror) TO DISK = '$(BackupPath)$(Database2Mirror).Trn' GO
-- Restoring the Backup file to the Mirror Server with NORECOVERY Need to take care of the With MOVE Option. :CONNECT $(MyMirrorServer) RESTORE Log $(Database2Mirror) FROM DISK = '$(BackupPath)$(Database2Mirror).Trn' with norecovery --move 'MyTestDB' to 'C:mytesdb.mdf', --move 'MytestDB_log' to 'c:mytestdb.ldg' GO
-- Specify Server Network Address :CONNECT $(MyMirrorServer) Use Master go ALTER DATABASE $(Database2Mirror) SET PARTNER = 'TCP://136.50.85.60:5022'
:CONNECT $(MyPrincipalServer) Use Master go ALTER DATABASE $(Database2Mirror) SET PARTNER = 'TCP://136.50.85.60:5023'
I need to connect my Local Database from SQL Server to the Web Database. I need to know what all things to be done for completing this process. Some one please please help me with this.
Ad if this information is not enough, please do ask me any question regarding this and iam ready to answer them.
One more thing, iam not that familiar with SQL Server 2000 but i can manage to do things.
I am learning dotnet thru Microsoft Official Curriculum and I got stuck up at one point. That is I couldn't configure the required databases according to the sql server I have installed and configured. I created a doctors database and while configuring using the below code I couldn't know what is 'ASPNET' in "SELECT @s = @@servername + 'ASPNET'" Also what is 'webuser'
Hello, I have specified a default database in my web.config like this: <dataConfiguration defaultDatabase="scsLocal"/> <connectionStrings> <add name="scsLocal" connectionString="server=DRLSWARTEBRV;database=SCS;uid=******;password=******;" providerName="System.Data.SqlClient"/> </connectionStrings> Now I would like to configure a SQLDataSource control to use this default database, yet it prompts me for a connectionstring. I know I can program it in the code behind file (if I ommit connection string, it will use the default), but then I can make no use of the wizard for configuring the SQLDataSource which would safe me a lot of coding. Is there a way to configure the SQLDataSource to use the default database that is specified in the web.config? Thanks! Veerle
Hello everyone, I have created my web application in VS 2005 using asp.net 2.0 and the built in sql .mdf database creater that comes with visual studio. After I developed my website, I then wanted to move it to one of our servers on the network, which I did without a problem. The website works fine but here is my challange. I need users to be able to connect to the database via login script and the script inserts computer data into the database. So I figured I need to attach the Database to SQL Server 2005. When I try to do this it gives me this database is in use. So I stop the website in IIS, and it allows me to add the database into the SQL Server. Once I do this, and start the website again, the website no longer works and I get a bunch of database errors, that do not get fixed untill I re-copy the website to the server. Is there a way to just connect to a .mdf file on the network that is not attached to a sql server, through a connection string in a login script? Maybe there is something else I need to do to the database before copying to the server? Or should I re-programm the website with a newly created database in SQL Server, and use that as the datasource?
I didn't know exactly which forum to post this in, so if I need to post it into another one, please let me know. Also, I have absolutely no SQL or DB experience. I am learning as I go.
The Situation: I created a virtual network for my company which included MS CRM, which uses SQL Server 2005. Everything was working fine until today. My company asked me to make a change to the domain name, as we are changing company names. I followed the processes through microsoft's documentation that I found online. The rename went fine. Since then, we can no longer access CRM. When I run the environment checks, it gets hung up on the SQL Server components. It says that it can't confirm the MSSQLSERVER service is running (it is) and it can't connect to the database. As best as I can tell, the database is looking for the old domain name, which doesn't exist. How do I edit or configure the existing database to reflect the new domain name. It's important to do this as we have data in that database that we need to access. Any help in this matter is very much appreciated!!!
I am trying to configure a connection manager for a database that is still using SQL Server 6.5 But it is not working properly. I put the server name, user name and pass word, I select the Native OLE DBMicrosoft OLE DB Provider for SQL Server as my provider.
When I click Test Connection it says the connection is fine but then the drop down list for the list of databse names hows nothing!
I have been trying to configure SQL Server 2005 Report Services. Every time I create the ReportServer database, the systems asks me if I want to "update" the database. It allows me no other option. When I click "apply", I get the message that the database version does not match my installation version. Does anybody have any insight into what happening with the configuration? What's more, do you have a solution?
I have searched the web and have not had any luck on finding the issue to my problem. I'm sure it's probably simple, so here it goes.
When receiving the following error "The permissions granted to user '' are insufficient for performing this operation"
Now, I would like to add to the error message shown above an extra line below it on the web page to reflect something like "Please contact XXX @ XXX if you feel that you need to have your login issue resolved" ...or something meaningful in addition to the default error messages.
I am not getting this error normally, as I have made a typo when login in on purpose! I am just trying to find a way to add more detail to the current error message or change the message to what I want it to say. This would go for any error message received by an end user received through SQL Server 2005 Reporting Services.
Do I have to create some sort of filter to read the message coming back from within SOAP? Are these messages stored in ReportingServices database or htm file?
I have created a SSIS package programmatically using C#.
The package should do the following take data from source A, and place rows into destination B, if there are any error rows then redirect the rows to destination C. In my package I have the following components:
DTSAdapter.OLEDBSource.1 - Used as the Source
DTSAdapter.OLEDBDestination.1 - Used for the Destination Output - (let me call this normalOutput)
DTSAdapter.OLEDBDestination.1 - Used for the Destination Error Output - (let me call this errorOutput)
All my mappings appear to be correct, I build and save the package and receive a Successful validation and Success on Execution.
However, When I open the application using the Execute Package Utility I get the warning:
Warning:No rows will be sent to the error output(s). Configure error or truncation dispositions to redirect rows to the error output(s), or delete data flow transformations or destinations that are attached to the error output(s)
How do I get around this?
I have placed on the DTSAdapter.OLEDBDestination.1 (Used for the Destination Output), on the input collection I have placed:
However, the above scenario does not pass the package validation, in the Execute Package Utiltity, I get the wanring mentioned above and also the error:
Error: The input "OLE DB Destination Input" (16) has an invalid error or truncation row disposition.
So my question is what are the correct configuration settings to have in this scenario?
I have created profiles in my database as mentioned below. now i want to delete the profile created in database. can some one tell me how to proceed furthur.
i need to delete them permenantly. EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = 'MyMailAccount', @description = 'Mail account for Database Mail', @email_address = 'makclaire@optonline.net', @display_name = 'MyAccount', @username='makclaire@optonline.net', @password='abc123', @mailserver_name = 'mail.optonline.net' ----------------------------------------------------- EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = 'MyMailProfile', @description = 'Profile used for database mail' --------------------------------------------------------EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @profile_name = 'MyMailProfile', @account_name = 'MyMailAccount', @sequence_number = 1
I am struggling with configuring SSB network routes to a mirror database. What I want to accomplish is to configure a SSB application to work in a database mirroring setup but I am not looking for a load balancing solution.
According to SQL Server 2005 Books Online (http://msdn2.microsoft.com/en-us/library/ms166090.aspx) the typical routing configuration for a service hosted by a mirrored database is set by specifying the 'mirror_address' field but leaving the 'broker_instance' field empty (i.e. NULL) in the sys.routes table (see Example 3 in link above). I haven't seen how this is possible because if you specify the MIRROR_ADDRESS parameter in the CREATE/ALTER ROUTE command then you must also specify the BROKER_INSTANCE parameter.
I haven't found a way to set the mirror_address field to a valid value and the broker_instance field as NULL, is this possible? If not I must set the broker_instance as the guid of my broker instance but my understanding is that it should primarily be used for load balancing configuration. My preference would be to not set the BROKER_INSTANCE parameter, is this possible?
- Microsoft SQL Server 2005 Standard Edition running on Microsoft Windows Server 2003 Standard Edition - Client Service for Netware installed. - Access 2000 database in a Novell Directory Services (NDS) Environment - SQL Server service running under local Administrator account.
Linked server configured as follow:
Linked server: MyDB Provider: Microsoft Jet 4.0 OLE DB Provider Product name: Access 2000 Data Source: UNC path to my Access database
Security: Admin with no password (default Access behaviour)
I can connect to my access database by following the next steps:
- Open Management studio on the SQL Server itself (where I'm logged in as Administrator) - Connect using Windows Authentication (Administrator) - Create a new query, run EXEC sp_tables_ex 'MyDB'
This will return all the tables from the Access database.
I can also make a view pointing to MyDB...MyTable.
Enough prove to me It can work....
So far so good
But now the tricky part.
When I login as 'sa', I can't connect and will receive the following message:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "MyDB" returned message "'Full UNC to my access database' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.".Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "MyDB".
(In real life I want to connect to my SQL 2005 Server from a ASP.NET (2.0) application using SQL authentication, but as long as I can't get the above working this is not important for now)
I am trying to mirror the databse with a witness server.
I have sql server 2005 with SP1 or both my mirror and principle and sql express on my witness.
The problem is when I click start mirroring, I get the following error
The server network address"TCP://serveraddress:7026" can not be reached or does not exist. Check the network address name and that the ports for the local and remote epoints are operational.
(Microsoft SQL server, error:1418)
I have pinged the address it works fine . The telnet comes up with a blank screen.
When I look at the the server log file viewer, I get the following message
An error occured while receiving data: 10054 (An existing connection was forcibly closed by the remote host)
I'm Getting "Report Server WMI Provider error: Not Found" when trying to Grant Database Access while configuring the Reporting Sevices Integration. Logging in fine to the DB. Tried all the WMI troublshooting and can't find any issues there. Any tips?
I have just finished configuring my first test mirrored environment (High safety mode). I setup the database engine service accounts on each of the servers with domainuser. I inherited a production mirrored environment set up by someone else. On the production servers the database engine service account is NT Authorityuser a local account. I am trying to practice installing Windows updates within a mirrored environment and I not sure how to proceed when the service account is NT Authority user account. should I change the service account to a domainuser?
ALTER DATABASE foo set PARTNER = 'TCP://10.3.3.1:1234' I get this error message:
The database is being closed before database mirroring is fully initialized. The ALTER DATABASE command failed. What does that mean, and how do I fix it?
