Feb 25 2014

Error : Cannot Open User Default Database in MsSQL Server

Category: Administration,Errors and SolutionsFatih Acar @ 17:21

If you delete default database of user, users of default database can not connect to database. You have to connect with sqlcmd on master database. After you have to change default database of user.


Cannot open user default database. Login failed.


Open CMD prompt
C:\>sqlcmd -E -d master
1> alter login [domain\LoginUser] with default_database = master
2> go
3> quit

19,810 total views, no views today

Tags: Database Administration, MsSQL Server, SQL Server Administration

Oct 01 2013

Create Linked Server for Oracle in SQL Server 2008 R2

Category: Administration,AdministrationFatih Acar @ 15:19

If you want to fetch data from Oracle to SQL Server, you can use Linked Servers features in SQL Server Database. Linked Server is like a database link (Dblink).

Installation steps

  • Install Oracle Client on SQL Server database server
  • Restart SQL Server database service
  • Configure OraOLEDB.Oracle provider in SQL Server database
  • Create user for remote connection in Oracle database
  • Create new linked server in SQL Server database
  • Add remote login for connect Oracle database in SQL Server
  • Query Example

Install Oracle Client on SQL Server database server

You can find Oracle client application on Oracle.com. You can install Oracle client application after download. I will not tell how to install.

Restart SQL Server database service

You can restart SQL Server database service on services.msc

Configure OraOLEDB.Oracle provider in SQL Server database

After Oracle client install, you have to find Oracle provider in SQL Server database Linked Servers as OraOLEDB.Oracle

SQL Server Database

OraOLEDB.Oracle properties configure like below;

SQL Server Database

Create user for remote connection in Oracle database

SQL> create user oraconnuser identified by oraconnuser;
SQL> grant create session to oraconnuser;
SQL> grant select on table1 to oraconnuser;

Create new linked server in SQL Server database

exec sp_addlinkedserver @LinkName, @SrvProduct, @Provider, @ConnectionString

exec sp_addlinkedserver ‘ORALINK’, ‘Oracle’, ‘OraOLEDB.Oracle’, ‘OraIP:1521\DBNAME’

Add remote login for connect Oracle database in SQL Server

exec sp_addlinkedsrvlogin @LinkName, @useself, @LocalLogin, @OraUser, @OraPassword

exec sp_addlinkedsrvlogin ‘ORALINK’, ‘FALSE’, NULL, ‘oraconnuser’, ‘oraconnuser’

Query Example

select * from openquery(ORALINK,’select * from table1′)
where table1column = ‘example’

69,863 total views, 4 views today

Tags: Database Administration, MsSQL Server, Oracle, Oracle Administration, SQL Server Administration, SQL Server to Oracle DbLink

Sep 16 2013

MsSQL Server Recovery Models Features

Category: AdministrationFatih Acar @ 16:11

SQL Server backup and restore operations occur within the context of the recovery model of the database. Recovery models are designed to control transaction log maintenance. A recovery model is a database property that controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available. Three recovery models exist: simple, full, and bulk-logged. Typically, a database uses the full recovery model or simple recovery model. A database can be switched to another recovery model at any time.

1- Simple

No log backups.
You can recover only to the end of a backup. Automatically reclaims log space to keep space requirements small, essentially eliminating the need to manage the transaction log space. For information about database backups under the simple recovery model, see Full Database Backups (SQL Server).
Operations that require transaction log backups are not supported by the simple recovery model. The following features cannot be used in simple recovery mode:

  • Log shipping
  • AlwaysOn or Database mirroring
  • Media recovery without data loss
  • Point-in-time restores

2- Full

You can recovery point in time.
Requires log backups.
No work is lost due to a lost or damaged data file.

3- Bulk-logged

You can recover only to the end of a backup.
Requires log backups.
An adjunct of the full recovery model that permits high-performance bulk copy operations.
Reduces log space usage by using minimal logging for most bulk operations.

Source : Microsoft Offical Documents

16,114 total views, no views today

Tags: Database Administration, MsSQL Server

Mar 28 2013

How to Install .Net Framework 3.5 on Windows Server 2012

Category: WindowsFatih Acar @ 10:21

You have to insert Windows Server 2012 DVD into DVD Reader. After you can install .Net FrameWork 3.5.

Installation Steps

  • insert Windows Server 2012 DVD or iso to D Drive.
  • Open Add Roles and Features Wizard From Server Manager.
  • Select .NET Framework 3.5 Features.
  • Select Specify an alternate source path and select path that is D:\Sources\SxS.
  • Finally click Install button.

64,025 total views, no views today

Tags: MsSQL Server, System Administration, Windows Server

Dec 19 2012

Create Schedule Job For Execute Procedure in SQL Server 2008

Category: Administration,SQLFatih Acar @ 20:22

If you want to run procedure or any query as scheduling, you can use job in SQL Server Database. For this process, you can use below operations.

Right Click On SQL Server Agent > New > Job


Continue reading “Create Schedule Job For Execute Procedure in SQL Server 2008”

43,198 total views, no views today

Tags: Database Administration, MsSQL Server

Jun 20 2012

SQL Server 2012 Top 12 Features

Category: MsSQL ServerFatih Acar @ 16:45

Microsoft has introduced SQL Server 2012 to the world and it’s time for IT professionals to start to come to speed on what’s new in this highly anticipated version of SQL Server.


1. AlwaysOn Availability Groups — This feature takes database mirroring to a whole new level. With AlwaysOn, users will be able to fail over multiple databases in groups instead of individually. Also, secondary copies will be readable, and can be used for database backups. The big win is that your DR environment no longer needs to sit idle.

2. Windows Server Core Support — If you don’t know what Windows Server Core is, you may want to come up to speed before Windows 8 (MS is making a push back to the command line for server products). Core is the GUI-less version of Windows that uses DOS and PowerShell for user interaction. It has a much lower footprint (50% less memory and disk space utilization), requires fewer patches, and is more secure than the full install. Starting with SQL 2012, it is supported for SQL Server.

3. Columnstore Indexes — This a cool new feature that is completely unique to SQL Server. They are special type of read-only index designed to be use with Data Warehouse queries. Basically, data is grouped and stored in a flat, compressed column index, greatly reducing I/O and memory utilization on large queries.

4. User-Defined Server Roles — DBAs have always had the ability to create custom database role, but never server wide. For example, if the DBA wanted to give a development team read/write access to every database on a shared server, traditionally the only ways to do it were either manually, or using undocumented procedures. Neither of which were good solutions. Now, the DBA can create a role, which has read/write access on every DB on the server, or any other custom server wide role.

5. Enhanced Auditing Features — Audit is now available in all editions of SQL Server. Additionally, users can define custom audit specifications to write custom events into the audit log. New filtering features give greater flexibility in choosing which events to write to the log.

6. BI Semantic Model — This is replacing the Analysis Services Unified Dimensional Model (or cubes most people referred to them). It’s a hybrid model that allows one data model will support all BI experiences in SQL Server. Additionally, this will allow for some really neat text infographics

7. Sequence Objects — For those folks who have worked with Oracle, this has been a long requested feature. A sequence is just an object that is a counter — a good example of it’s use would be to increment values in a table, based a trigger. SQL has always had similar functionality with identity columns, but now this is a discrete object.

8. Enhanced PowerShell Support — Windows and SQL Server admins should definitely start brushing up on their PowerShell scripting skills. Microsoft is driving a lot of development effort into instrumenting all of their server-based products with PowerShell. SQL 2008 gave DBAs some exposure to it, but there are many more in cmdlets in SQL 2012.
Continue reading “SQL Server 2012 Top 12 Features”

8,689 total views, 2 views today

Tags: MsSQL Server

« Previous Page