Database Mirroring Transport Error
May 24, 2006
Hi
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
alter database set partner="..."
Am I missing something?
Thanks
View 1 Replies
ADVERTISEMENT
Aug 3, 2006
Hi.
I am getting this error.
Msg 1486, Level 14, State 2, Line 2
Database Mirroring Transport is disabled in the endpoint configuration.
Someone please help me.It's urgent.
I am using same PC with 2 different intsances.
This is how i have done it:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'test@#56'
GO
-- CREATE CERTIFICATE PRINCIPAL_CERT
CREATE CERTIFICATE PRINCIPAL_CERT
WITH SUBJECT = 'PRINCIPAL CERTIFICATE',
START_DATE = '03/07/2006',
EXPIRY_DATE = '01/01/2010'
GO
-- CREATE ENDPOINT NAMED (EPMIRRORING) USING CERTIFICATE AND ALGORITHM
CREATE ENDPOINT EPMIRRORING
STATE = STARTED
AS TCP(LISTENER_PORT = 6025, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ENCRYPTION = REQUIRED ALGORITHM RC4, ROLE = ALL,
AUTHENTICATION = CERTIFICATE PRINCIPAL_CERT)
GO
Here is the of Event Log.
Date,Source,Severity,Message
08/03/2006 15:54:35,spid52,Unknown,The Database Mirroring protocol transport is now listening for connections.
08/03/2006 15:54:35,spid52,Unknown,Server is listening on [ 'any' <ipv4> 5122].
08/03/2006 15:52:57,spid53,Unknown,The Database Mirroring protocol transport is disabled or not configured.
08/03/2006 15:52:55,spid53,Unknown,The Database Mirroring protocol transport has stopped listening for connections.
08/03/2006 14:14:54,spid52,Unknown,The Database Mirroring protocol transport is now listening for connections.
08/03/2006 14:14:54,spid52,Unknown,Server is listening on [ 'any' <ipv4> 5122].
08/03/2006 14:14:37,spid53,Unknown,The Database Mirroring protocol transport is disabled or not configured.
08/03/2006 14:14:35,spid53,Unknown,The Database Mirroring protocol transport has stopped listening for connections.
08/03/2006 14:03:48,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]
08/03/2006 14:03:46,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]
08/03/2006 14:03:43,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]
08/03/2006 14:03:41,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]
08/03/2006 14:03:38,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]
08/03/2006 14:03:36,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]
08/03/2006 14:03:34,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]
08/03/2006 14:03:33,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]
08/03/2006 14:03:32,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]
08/03/2006 14:03:30,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]
08/03/2006 14:03:15,spid54,Unknown,The Database Mirroring protocol transport is now listening for connections.
08/03/2006 14:03:15,spid54,Unknown,Server is listening on [ 'any' <ipv4> 5022].
Any solution ?
Thanks
Mihir
View 5 Replies
View Related
Oct 16, 2007
Hi,
i have configured a mirroring database using three different server instances on same domain. i'm able to configure the principal, mirror and witness using the configure database mirroring security wizard. I leave the service accounts blank and complete the process successfully but when i hit on start mirroring button it gives me error i.e 'database Mirroring Transport is disabled in the endpoint configuration.' Kindly help.
Regards
View 3 Replies
View Related
Jul 13, 2007
Hi,
I received an alert 'The SQL Server Service Broker or Database Mirroring transport is disabled or not configured'
How can I check the status of the two? and if they are disabled how do I enable them?
View 6 Replies
View Related
Oct 14, 2006
Hi,
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.
thanks
View 3 Replies
View Related
Aug 23, 2007
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
View 9 Replies
View Related
Sep 11, 2007
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.
TITLE: Database Properties
------------------------------
An error occurred while starting mirroring.
------------------------------
ADDITIONAL INFORMATION:
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.
View 6 Replies
View Related
Apr 13, 2007
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
View 5 Replies
View Related
Apr 18, 2008
Hello,
Database Mirroring Monitor Job Error
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.
Does any one have any idea about this error?
View 12 Replies
View Related
Dec 18, 2007
Hi all,
I am trying to set up Database mirroring on my test & DR server. But , I am getting the following Error:
( On local PC Mirroring is successful).
Both the test & DR have same service accounts for SQL Server.
Thanks for all your help.
Thanks.
Error:
TITLE: Database Properties
------------------------------
An error occurred while starting mirroring.
------------------------------
ADDITIONAL INFORMATION:
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
------------------------------
BUTTONS:
OK
------------------------------
View 3 Replies
View Related
May 7, 2007
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!!!)
View 3 Replies
View Related
May 3, 2008
Server A = primary SQL DBs (mirroring origination)
Server B = failover SQL DBs (mirroring destination)
For database mirroring a witness is required.
Can the witness live in another instance of SQL on server B?
View 7 Replies
View Related
Jan 16, 2007
Ok, some background information:
I'm running an asp.net application.ASP.NET 2.0I'm using a SQL Server 2000 database.I don't have very many problems with the application, but when I do, I get this error message:
Inner Source
.Net SqlClient Data Provider
Inner Message
A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
Here is what my connection string looks like, although from the stacktrace, it looks like its failing when I do an ExecuteReader. Any ideas why I'm getting this error?
connection_string = "Data Source=database;Initial Catalog=catalog;User ID=XXXX;Password=XXXX;min pool size=1; max pool size=50";
Thanks,
Khanathor
View 1 Replies
View Related
Apr 21, 2006
Hi:
I exec local procs of archiving databases on 3 SQL 2000 Enterprise Servers. All three servers with following same error messages in the middle of processing(each had processed from 10 to 100 DBs already).
----------------------------------------------------------
Msg 64, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)
There is no error message in above 3 SQL's error log and all of them are up running fine.
Since the procs are in local, I did not use hostname or '.' to refer as 'Local'.
Any idea?
thanks
-D
View 1 Replies
View Related
Dec 22, 2006
What is the best way to transport a complete database (including tables and stored procedures) to another PC?
View 2 Replies
View Related
Jul 5, 2015
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
View 8 Replies
View Related
Apr 5, 2006
I am getting the following error.A transport-level error has occurred when sending the request to theserver. (provider: Shared Memory Provider, error: 0 - The system cannotopen the file.)On performing the same operation again, it happens fine without anyerror.Please help.Regards,Shilpa
View 5 Replies
View Related
May 21, 2008
I have an SSIS package that calls a stored procedure via an ADO.NET Execute SQL Task. This stored procedure is long running - up to an hour. I am running the SSIS package on the same SQL Server that the SQL Task is connecting to.
I started to receive an error last night when calling it on my DEV box: "The semaphore timeout period has expired." I rebooted the server and tried again. Same error. I added some additional logging to see where I am within my stored procedure when the error happens and rebooted again. Same error. I made some other minor changes and rebooted and tried again. This time the error changed to: "The specified network name is no longer available".
This is running on a SQL Server 2K3 box. With the latest patches, including 4 that were installed this week.
Am I correct in interpretting these errors to say that the SSIS component is unable to maintain the SQL connection (to the same box) for the duration of the stored procedure run? Any other questions/advice?
Here are the 2 complete error messages:
Error: 0xC002F210 at Transform Invoice SQL Task, Execute SQL Task: Executing the query "EXEC dbo.sp_TransformInvoice @JobBatch_id = @JobBatch_id" failed with the following error: "A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Error: 0xC002F210 at Transform Invoice SQL Task, Execute SQL Task: Executing the query "EXEC dbo.sp_TransformInvoice @JobBatch_id = @JobBatch_id" failed with the following error: "A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Thanks,
Chris
View 9 Replies
View Related
Jan 10, 2007
Hi all,
I'm having a little problem with my sqlserver 2005.
I'm trying to crate a user in Microsoft SQL Server Management Studio throug a query.
Like this:
-------------------------------------------------------------------------------------------
USE [master]
GO
CREATE LOGIN [MYUSER] WITH PASSWORD=N'MYPASSWORD', DEFAULT_DATABASE=[MYDB],
DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER LOGIN [MYUSER] ENABLE
GO
USE [msdb]
GO
CREATE USER [MYUSER] FOR LOGIN [MYUSER];
GO
GRANT EXECUTE ON [sp_start_job] TO [MYUSER]
GO
EXEC sp_addrolemember 'db_datareader', 'MYUSER'
GO
EXEC sp_addrolemember 'db_datawriter', 'MYUSER'
GO
EXEC sp_addrolemember 'SQLAgentOperatorRole', 'MYUSER'
GO
EXEC sp_addrolemember 'SQLAgentUserRole', 'MYUSER'
GO
EXEC sp_addrolemember 'SQLAgentReaderRole', 'MYUSER'
GO
Use [MYDB]
GO
CREATE USER [MYUSER] FOR LOGIN [MYUSER]
GO
EXEC sp_addrolemember 'db_datareader', 'MYUSER'
GO
EXEC sp_addrolemember 'db_datawriter', 'MYUSER'
GO
------------------------------------------------------------------------------------------
And drops the user by executing following:
Use [MYDB]
GO
EXEC sp_dropuser 'MYUSER'
GO
USE [msdb]
GO
REVOKE EXECUTE ON [sp_start_job] TO [MYUSER]
GO
EXEC sp_droprolemember 'SQLAgentOperatorRole', 'MYUSER'
GO
EXEC sp_droprolemember 'SQLAgentUserRole', 'MYUSER'
GO
EXEC sp_droprolemember 'SQLAgentReaderRole', 'MYUSER'
GO
EXEC sp_dropuser 'MYUSER'
GO
USE [master]
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'MYUSER')
DROP LOGIN [MYUSER]
------------------------------------------------------------------------------------------
-Then if I from a query, first creates the user, then drops the user, I can't create him again?? I get's the following error when trying:
Msg 10054, Level 20, State 0, Line 0
A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
I'f i then runs the createscript again, the user is created correctly.
Is there a way to avoid this foced lockout? Or how do i "refreash" my connection, without dataloss?
Best Regards
Henrik
View 10 Replies
View Related
Mar 9, 2008
When i try to use SqlBulkCopy in vb.net to import 499 records i get the following error returned?
A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - The pipe has been ended.)
This then results in SQL Server services stopping - and forcing me to do a reset?
does any one know what would cause this and also how to fix it?
View 2 Replies
View Related
Oct 21, 2007
How do I take a database developed and operating and get into the 2005 SQL Server?
View 4 Replies
View Related
Apr 9, 2007
I just installed Microsoft SQL Server Express SP2. I setup a linked server to an Oracle database using the followings commands:
EXEC sp_addlinkedserver 'Oracle_DEV', 'Oracle', 'MSDAORA', 'DEV'
EXEC sp_addlinkedsrvlogin 'Oracle_DEV ', false, NULL, 'oracledbuser', 'dbuserpassword'
When I try and query the linked database I get the following error message:
Msg 109, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - The pipe has been ended.)
I've been searching the web and have gotten a lot of hits on the error message but have yet to uncover anything specifically related to linked servers or anything closely related enough to help me resolve this problem. Any advice would be appreciated. Thanks.
View 3 Replies
View Related
May 24, 2006
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!
View 1 Replies
View Related
Jan 24, 2007
Hi,
I am using SQL Server 2005,
while trying to retrieve data from the database; I am getting the following
error:
A transport-level error has occurred when receiving results
from the server. (Provider: TCP Provider, error: 0 - The handle is invalid.)
But I am getting this error randomly.
Can some one help me out?
Waiting for your response
Sudhakar
View 7 Replies
View Related
Mar 8, 2007
Hi,
Basically the error that I am getting is in our test automation when running as non-admin on the box (regular user). We use .Net C# SQLConnection class to connect to SQL express 2005 impersonating with admin credentials. After getting the connection we try to execute a select command and it some time fails with following error:
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The handle is invalid.) at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error) at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32 bytesExpected) at System.Data.SqlClient.TdsParserStateObject.ReadBuffer() at System.Data.SqlClient.TdsParserStateObject.ReadByte() at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader()
This happen only in the non-admin scenario mentioned above. Any idea what is triggering this?
Thanks in advance for all replies.
View 3 Replies
View Related
Sep 27, 2007
Hi,
A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
I am using a direct connection from my Gridview to perform a huge database search. the stored procedure will take 10- 60 seconds to execute the query.
What the problem i am facing is when the sp takes long time (more than 1 minutes), then when i try any other operation like calling another sp frm another page, i am getting the above specified error.
Anybody can tell why is it happening?
here my code snippet goes
<asp:GridView ID="searchGV" runat="server" AllowPaging="True" AllowSorting="True"
BorderColor="#999999" BorderStyle="None" CellPadding="3" Width="100%" AutoGenerateColumns="False" DataSourceID="searchObj" DataKeyNames="Recordnumber" OnDataBound="searchGV_DataBound" OnPageIndexChanged="searchGV_PageIndexChanged">
<RowStyle CssClass="gridRow" />
<PagerStyle HorizontalAlign="Center" BackColor="#999999" ForeColor="Black" />
<HeaderStyle CssClass="gridHeader" />
<AlternatingRowStyle CssClass="gridAlternateRow" />
<Columns>
<asp:TemplateField HeaderText="All">
<HeaderTemplate>
<asp:CheckBox ID="chkid" AutoPostBack="true" OnCheckedChanged ="OnCheckChangedEvent" runat="server" />
</HeaderTemplate>
<EditItemTemplate>
<asp:CheckBox ID="chkSelect" runat="server" />
</EditItemTemplate>
<ItemTemplate>
<asp:CheckBox ID="chkSelect" runat="server"/>
</ItemTemplate>
<ControlStyle Height="18px" Width="15px" />
<ItemStyle Width="5px" />
</asp:TemplateField>
<asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title">
<ItemStyle Width="150px" />
</asp:BoundField>
<asp:BoundField DataField="Artist" HeaderText="Artist(s)" SortExpression="Artist"/>
<asp:BoundField DataField="Album" HeaderText="Album" SortExpression="Album"/>
<asp:BoundField DataField="Writer" HeaderText="Writer(s)" SortExpression="Writer" ><ItemStyle Width="150px" /></asp:BoundField>
<asp:BoundField DataField="ISRC" HeaderText="ISRC" SortExpression="ISRC" />
<asp:BoundField DataField="UPC" HeaderText="UPC" SortExpression="UPC"/>
<asp:BoundField DataField="PLineLabel" HeaderText="P-Line Label" SortExpression="PLineLabel" ><ItemStyle Width="100px" /></asp:BoundField>
<asp:BoundField DataField="DistLabel" HeaderText="Distribution Label" SortExpression="DistLabel" >
<ItemStyle Width="100px" />
</asp:BoundField>
<asp:BoundField DataField="PublishInfo" HeaderText="Publisher(s)" SortExpression="PublishInfo" />
<asp:BoundField DataField="Recordnumber" HeaderText="Record Number" SortExpression="Recordnumber" />
<%-- <asp:BoundField DataField="UID" HeaderText="UID" Visible="false" />--%>
</Columns>
</asp:GridView>
with adapter
<asp:SqlDataSource ID="searchObj" runat="server" SelectCommand="gsp_titleSearchResult" SelectCommandType="StoredProcedure" ConnectionString="<%$ ConnectionStrings:ConnStr %>">
<SelectParameters>
<asp:ControlParameter ControlID="HuserID" PropertyName="Value" Name="Userid" Type="String" Size="50" DefaultValue="" />
<asp:ControlParameter ControlID="Htitle" PropertyName="Value" Name="title" Type="String" Size="50" DefaultValue=" " />
<asp:ControlParameter ControlID="Hartist" PropertyName="Value" Name="artist" Type="String" Size="50" DefaultValue=" " />
<asp:ControlParameter ControlID="Halbum" PropertyName="Value" Name="album" Type="String" Size="50" DefaultValue=" " />
<asp:ControlParameter ControlID="Hwriter" PropertyName="Value" Name="writer" Type="String" Size="50" DefaultValue=" " />
<asp:ControlParameter ControlID="Hisrc" PropertyName="Value" Name="isrc" Type="String" Size="50" DefaultValue=" " />
<asp:ControlParameter ControlID="Hupc" PropertyName="Value" Name="upc" Type="String" Size="50" DefaultValue=" " />
<asp:ControlParameter ControlID="Hpline" PropertyName="Value" Name="pline" Type="String" Size="50" DefaultValue=" " />
<asp:ControlParameter ControlID="Hdist" PropertyName="Value" Name="distlabel" Type="String" Size="50" DefaultValue=" " />
<asp:ControlParameter ControlID="Hpublish" PropertyName="Value" Name="publisher" Type="String" Size="50" DefaultValue=" " />
<asp:ControlParameter ControlID="ReleaseID" PropertyName="Value" Name="Releaseid" Type="Int32" Size="50" DefaultValue="0" />
<asp:ControlParameter ControlID="RecordLabelID" PropertyName="Value" Name="recordlabelid" Type="Int32" Size="50" DefaultValue="0" />
<asp:ControlParameter ControlID="PublisherID" PropertyName="Value" Name="Publisherid" Type="Int32" Size="50" DefaultValue="0" />
<asp:ControlParameter ControlID="ArtistID" PropertyName="Value" Name="Artistid" Type="Int32" Size="50" DefaultValue="0" />
</SelectParameters>
</asp:SqlDataSource>
Advance thanks
Anvar Sadath
View 1 Replies
View Related
Apr 7, 2006
Hi all,
I am new to SQL 2005. I have one issue and it is as follows:
I write a simple query and run it; it works fine.
Now I break the network connection and run the query; in this case it runs fine.
But when I reconnect the network and run the query, then I get an error as follows:
"A transport-level error has occurred when sending the request to the server. (provider: Named Pipes Provider, error: 0 - An unexpected network error occurred.) "
Every thing is local here. So how the network issue comes here?
View 14 Replies
View Related
Nov 27, 2006
Hi all,
I
am trying to run a stored procedure which retreives 3 lakhs of records
and updates the data and moves few of those records to some tables.
Since the number of records are more , the time taken for the storede
procedure is around 30 minutes when i directly execute it in query
analyzer.
But I need to execute it from Visula Studio.Net 2005
(c#). Whole of the application contains only one form with a single
button.When I click on the button , this stored procure has to be
executed. But I am getting an error as shown below.
"A
transport-level error has occurred when receiving results from the
server. (provider: TCP Provider, error: 0 - The specified network name
is no longer available.)"
Database used is SqlServer 2000
How to solve this error?. Any ideas are really appreciated.
Thanks and Regards,
Sukanya.
View 4 Replies
View Related
Oct 16, 2006
I've tried to search on the web for a solution for this error but i didn't find any working solutions for this problem. We have 2 servers. The first one is the server we use to develop our ASP.NET 2 application. On this server we don't have this error. On the other server (use by our client) we have this error sometime and i don't know why. Both servers have the same configuration and both application have the same web.config file.
I've tried to add a try-catch and retry the query when the error occured. This seems to be working but we don't want to have to change all our connections and since we don't have this problem on the other server we want to find the source of the problem.
Any idea ?
Thanks !
View 1 Replies
View Related
Oct 12, 2015
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....
View 5 Replies
View Related
Nov 30, 2007
Greetings:
I have a database that is 1.7terabyte in size with 136gb free and throws a "transport level error" telling me to discard the results when I run dbccshrinkfile ('DBNAME', size). I have tried various increments of size, from truncateonly to 1MB below its current value, and nothing works. I have tried to detach and reattach the db, restart the service, restart the server, and none have provided a solution. Any ideas?
Regards,
Derek
View 1 Replies
View Related
Sep 27, 2006
We've devoted a resource to this today, but I have to believe it's something easy that we're overlooking. The scneario is that we have a production Web application that until last weekend had a SQL 2000 back end. This weekend we installed a new instance of SQL 2005 and everything works (we tested in a sandbox environment, but someone must not have load tested enough) and never saw these exceptions. So, after the upgrade we now receive 100's of thexe SQL excptions per day:A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)Does anyone know what we've overlooked that's causing this issue?Thanks for any help!
View 2 Replies
View Related
Aug 16, 2006
Hi,
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)
for 'TCP://serveraddress:7026'.
Please help me soon
View 6 Replies
View Related
