SQLState: 01000 SQL Server Error: 10061

Problem

Connection failed:
SQLState: 01000
SQL Server Error: 10061
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (connect())

Connection failed:
SQLState: 08001
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied.


Solution:

1.Make sure that you have configured the firewall on the computer to allow this instance of SQL Server to accept connections.

2.Use the SQL Server Configuration Manager tool to allow SQL Server to accept remote connections.

Reference :

https://social.msdn.microsoft.com/forums/sqlserver/en-US/be38b076-6f62-4dce-a2cb-bd1a3bb33c6b/odbc-connection-failure

Msg 7399, Level 16, State 1, Line 1 / Msg 7303, Level 16, State 1, Line 1 in MS SQL Server dblink

Error:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "ORCL_TEST" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "ORCL_TEST".


Solution:

The solution to troubleshoot error is to set OraOleDB.Oracle provider Allow inprocess option.In order to configure Allow inprocess option goto OraOleDB Oracle provider properties.

Be sure that the Allow inprocess option is marked.

Allow inprocess

SQL Server allows the provider to be instantiated as an in-process server. When this option is not set, the default behavior is to instantiate the provider outside the SQL Server process. Instantiating the provider outside the SQL Server process protects the SQL Server process from errors in the provider. When the provider is instantiated outside the SQL Server process, updates or inserts referencing long columns (text, ntext, or image) are not allowed.

Now execute the following sql statements on Oracle databases using SQL Server Linked Server.

SELECT
 [FIRST_NAME], [LAST_NAME]
FROM [ORCL_TEST]..[HR].[EMPLOYEES]

step by step to create dblink in MS SQL database to connect Oracle Database

Step1: Download Oracle client

Step2: Install Oracle Client in the server where MS SQL Database installed 

Step3: Configure tnsnames.ora file in Oracle client

Step4: Start SQL Server Management Studio

Step5:  Enter the login name and password for a SQL administrator account such as sa. 

Step6: Click to expand the SQL Server | Server Objects.
Right-click Linked Server and click New Linked Server.

 Step7 : Create a SQL Server Linked Server Object

1. Start SQL Server Management Studio
2. Expand Server Objects
3. Expand Linked Servers
4. Expand Providers

5. Make Sure MSDASQL (Microsoft OLE DB Provider for ODBC Drivers) is installed on the server
6. Right Click on Linked Servers
7. Select New Linked Server...
8. Assign a name to the Linked Server; I suggest a single word name like oracle_db.
9. Select Microsoft OLE DB Provider for Oracle
10. Set the Product Name to Oracle Database.
11. Set the Data Source to the tnsname

12. Select Security page
13. Select Be made using this security context
14. Set the Remote login field to the Oracle Database User Id
15. Set the With password field to the Oracle Database Password

16. Click OK button

Step8 : Test the new Linked Server

How to purge Log in SqlServer Database

Step1: Open the SQL Server Management Studio by choosing Start > Programs > Microsoft SQL Server 2008 > SQL Server Management Studio.

Step2:  right-click on Database and open new sql query and execute following query to shrink Database log file to 20MB

USE [DB_NAME]
GO
ALTER DATABASE [DB_NAME] SET RECOVERY SIMPLE WITH NO_WAIT
dbcc shrinkfile(DB_Log_File_Name,20)
ALTER DATABASE [DB_NAME] SET RECOVERY FULL WITH NO_WAIT
GO

Note: Above colored texts are variable

USE [servicedesk]
GO
ALTER DATABASE [servicedesk] SET RECOVERY SIMPLE WITH NO_WAIT
dbcc shrinkfile(servicedesk_log,20)
ALTER DATABASE [servicedesk] SET RECOVERY FULL WITH NO_WAIT
GO

Database version check in SQL Server Database

Goal:

Following SQL command will show the version of MS SQL Database

-----MS SQL DB Version Check-----

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

Windows Authentication User Creation in MS SQL Database

Goal:

Following SQL will execute windows authentication Database user

--Windows Authentication User Creation----

CREATE LOGIN [NOVOTEL-BD\md.saiduzzaman] FROM WINDOWS;
GO

Error when using MS SQL Database Link Server

The execute permission was denied on the object ‘xp_prop_oledb_provider’

Fix::
GRANT EXECUTE ON xp_prop_oledb_provider TO [UserDomain\Account Name];

setupadmin and sysadmin should have in server role for the UserDomain\Account Name user.

Renabling Maintenance Plans in SQL Server 2005 and 2008

Problem:
'Agent XPs' component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Agent XPs' by using sp_configure. For more information about enabling 'Agent XPs', see "Surface Area Configuration" in SQL Server Books Online. (ObjectExplorer)

Fix:
1. Execute the following procedure in master Database

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO

library not registered error during creating Maintenance Plan through SQL Server Management Studio

When creating Maintenance Plan through SQL Server Management Studio showing "library not registered"

Registering these 2 DLLs should fix your problem.

regsvr32 msxml6.dll
regsvr32 msxml3.dll

Cheer!!!!!!!!!!!

What are the basic functions for master, msdb, model, tempdb and resource databases?

What are the basic functions for master, msdb, model, tempdb and resource databases?

The master database holds information for all databases located on the SQL Server instance and is the glue that holds the engine together. Because SQL Server cannot start without a functioning master database, you must administer this database with care.
The msdb database stores information regarding database backups, SQL Agent information, DTS packages, SQL Server jobs, and some replication information such as for log shipping.
The tempdb holds temporary objects such as global and local temporary tables and stored procedures.
The model is essentially a template database used in the creation of any new user database created in the instance.
The resoure Database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata.

What is Service Broker?

Service Broker is a message-queuing technology in SQL Server that allows developers to integrate SQL Server fully into distributed applications. Service Broker is feature which provides facility to SQL Server to send an asynchronous, transactional message. it allows a database to send a message to another database without waiting for the response, so the application will continue to function if the remote database is temporarily unavailable.

Where SQL server user names and passwords are stored in SQL server?

They get stored in System Catalog Views sys.server_principals and sys.sql_logins.

Best Practices for Backup and Restore in SQL Server 2005

Backup Targets

Besides the backup type, SQL Server allows four different backup targets: Database, Filegroup, File and Partial. These targets only apply to the Full and Differential Backup types; they do not apply to the log backup.

Database Backup

A database backup makes a copy of the entire database. If you are using a Full database backup it includes all used database pages in the database; if you are using a Differential Database backup, it includes all changed database pages of the database that have changed since the last full backup. Some examples of how to perform a Full and Differential Database backup are:

-- Full Database Backup

BACKUP DATABASE AdventureWorks

  TO  DISK = N’C:\Backup\AdventureWorks.bak’ 

-- Differential Database Backup

BACKUP DATABASE AdventureWorks

  TO  DISK = N’C:\Backup\AdventureWorks.bak’

  WITH DIFFERENTIAL

Differential Backup

Differential backups are designed to reduce the amount of time it takes to perform full backups. Instead of copying all used pages, the differential backup copies only pages that have changed since the last full backup. Similar to the Full Backup, differential backups also copy a part of the transaction log to maintain the transactional integrity when the backup is restored. The following code is an example of how to create a Differential Backup. 

BACKUP DATABASE AdventureWorks

  TO DISK = N’C:\Backup\AdventureWorks.bak’

  WITH DIFFERENTIAL

Log Backup

The only type of backup that does not actually copy database pages is the Log backup. The Log backup copies the transaction log of the database. After it copies the log, the backup truncates the part of the log that is not required by active transactions or transactional replication. The syntax to back up the Log is:

BACKUP LOG AdventureWorks

TO  DISK = N’C:\Backup\AdventureWorks.bak’

File Backup

File backups do not copy the database; instead the file backup aim is to back up only one or more files that are part the database.Databases in SQL server 2005 have three different types of files: Primary, Secondary, or Log Files. Primary and Secondary files hold database pages; in contrast, Log files hold the transaction log. File backups can make copies of pages from Primary and Secondary Files.This type of backup can be used to speed up the restora-tion process, when the device that stores one of the files

fails but the rest of the devices are working correctly. In this case, there is no need to restore the full database; only the affected file.  The following SQL code performs Full and Differential File backups:

-- Full File Backup

BACKUP DATABASE AdventureWorks  FILE=’AdventureWorks_Data’

TO  DISK = N’C:\Backup\AdventureWorks.bak’ 

-- Differential File Backup

BACKUP DATABASE AdventureWorks  FILE=’AdventureWorks_Data’

TO  DISK = N’C:\Backup\AdventureWorks.bak’

  WITH DIFFERENTIAL

One important element to remember when using File Backups is that they should either be read only (the file-group they belong to) or combined with Log Backups to restore transactional integrity of the database. File backups do not automatically back up the transaction log.