-->
SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Articles by Nani

Finished Masters in Computer applications and started the career with SQL application and DBA support. Have experience working with different versions of SQL starting from SQL 2000 to 2014. Also worked on SSRS and SSIS. Currently working in US based company as SQL DBA and Developer. I am very much interested in documenting SQL Server information and doing it everywhere as required. I love learning, working and documenting SQL Server.

Oracle-SQL Datatype conversion issues through linked server

Msg 9803, Level 16, State 1, Line 1 Invalid data for type"numeric".

Fix: Use TO_CHAR function. See below example

SELECT * FROM OPENQUERY(NHRTEST,'SELECT PRIMARY_ID,
ORGANIZATION,
SUB_LINE_STATUS_CODE,
ORGANIZATION_ID,
CONTRACT_NUMBER,
CONTRACT_ID,
RENEWAL_MODIFIER,
CACT_STATUS,
START_DATE,
END_DATE,
CREATION_DATE,
LAST_UPDATE_DATE,
DATE_APPROVED,
DATE_SIGNED,
ORG_ID,
SHORT_DESCRIPTION,
CURRENCY_CODE,
CONVERSION_RATE,

Read more

0 comments, 190 reads

Posted in SQL Articles by Nani on 6 September 2017

Finding Default and User Traces running in SQL Server

I have heard one issue from my manager saying one of SQL instance is taking almost all memory, what is causing this high load.

I starting thinking what can create this load, no SQL jobs running at this time. Then I got light which is any of developer running SQL…

Read more

0 comments, 222 reads

Posted in SQL Articles by Nani on 13 December 2016

Attach .mdf files to create Database in SQL Server

I have got a request to create databases by giving .mdf and .ldf files. Used below steps to create databases.

Attach database method.

Connect to the SQL instance you want to create these databases and right click on database and click on Attach... option.


Click on Add button and give…

Read more

0 comments, 267 reads

Posted in SQL Articles by Nani on 11 December 2016

Steps to reset sa password of SQL Server instance

Connect SQL Server Configuration Manager to set startup parameters to start SQL server in single user mode.

Click all programs and click on SQL Server, go to Configuration Tools folder and click on SQL Server Configuration manager.











Right click on SQL Server and click properties. Click on Startup Parameters tab.…

Read more

1 comments, 1,905 reads

Posted in SQL Articles by Nani on 10 December 2016

Oracle Linked Server errors

Stuck with the below error right now..any ideas

Trying to pull data from Oracle view using linked server and getting below error. It was only when I try with particular Oracle instance, when I try to pull data from other instance using the same view it is working fine.

Any…

Read more

0 comments, 250 reads

Posted in SQL Articles by Nani on 7 December 2016


Error:  The server principal "IncidentManagement" is not able to access the database "DatabaseName" under the current security context.
for conection string: "Data Source=SBCAPPDEV1.curvature.com;Initial Catalog=DatabaseName ;User Id=IncidentManagement ;Password=wuwaye6U5+uz;" providerName="System.Data.SqlClient"


Fix: This error you receive when the service account using from .net application doesn’t have sufficient permissions to access the…

Read more

0 comments, 0 reads

Posted in SQL Articles by Nani on 2 December 2016

Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)



Issue: I see this issue when I try to expand the databases and any other things in object explorer of secondary replica of AlwaysOn SQL instance. I have seen few blogs which suggests to see sp_who2 results and kill blocking. Unfortunately this is  read only database and no user connections…

Read more

0 comments, 945 reads

Posted in SQL Articles by Nani on 2 December 2016

Adding database back to Always On High Availability Group

            
Summary: If databases in secondary replica are out of sync with primary then below is the workaround which helps to fix.

1)      At very first take the database out of Always on availability group on primary replica by right click on the…

Read more

1 comments, 1,573 reads

Posted in SQL Articles by Nani on 2 December 2016

VIEW SERVER STATE permission was denied on object 'server', database 'master'. (Microsoft SQL Server, Error: 300)

Issue: I had to create few users for the first time and give access to execute views from user database. I have created logins for them and gated access to select from views and tables at object level but see below error when they select from views.

Error:

TITLE: Microsoft…

Read more

3 comments, 5,016 reads

Posted in SQL Articles by Nani on 2 December 2016

The transaction log for database '' is full due to 'AVAILABILITY_REPLICA'. [SQLSTATE 42000] (Error 9002). The step failed. SQL 2014


I have noticed that our SQL jobs are failing due to this error.


Message
Executed as user: ''. The transaction log for database '' is full due to 'AVAILABILITY_REPLICA'. [SQLSTATE 42000] (Error 9002).  The step failed.


Below is the SQL configuration :

SQL version is 2014
One Primary and one…

Read more

2 comments, 1,748 reads

Posted in SQL Articles by Nani on 23 May 2016

Which method is best to import data from Oracle database to SQL

Hi Guys,

I would like to start a discussion through my blog to know more about this topic.

Below options I know to import data from Oracle to SQL..

1) Import/Export
2) Linked Server (Using OpenQuery)
3) SSIS Packages

The fastest among these 3 I noticed is the 3rd one…

Read more

0 comments, 323 reads

Posted in SQL Articles by Nani on 22 May 2016

Identity increment is jumping in SQL Server database


Please look at the below screenshot to understand what jumping of identity value is (It is taken from SQL 2014 Database table.)

Observe records below the red line..the identity value jumped from 17 to 1014 and started continuing from there.

Cause: This is not actually the issue or bug. It…

Read more

3 comments, 3,006 reads

Posted in SQL Articles by Nani on 20 May 2016

Table changes not automatically reflected in a SQL Server View

For example you have added new columns to the tables which are used by SQL view. Nothing is changed in the view but when you execute the view or any other object which is using the view you will see columns not found error message.

To fix that, we need…

Read more

0 comments, 331 reads

Posted in SQL Articles by Nani on 18 May 2016

Check In Database Scripts in to Team Foundation Server (Using VS 2015)

After TFS server is ready with folders created for databases, you need to take database scripts in your own style and check in to TFS. This is one of the database version control methods.
 Install Visual Studio 2015 and once you launch you need to click on Team (5th

Read more

1 comments, 1,184 reads

Posted in SQL Articles by Nani on 13 May 2016

Error: THE SERVER PRINCIPAL ALREADY EXISTS – MSG 15025, LEVEL 16, STATE 2, LINE 1



I cannot find the login physically though but getting the error while trying to create a new login. 
Use below scripts to fix the issue

 select suser_sid ('Domain\loginName');
go

Result: 0x0105000000000005150000000C04XXXXXXXXXXXXXXXXXXXXXXXXX


select * from sys.server_principals sp
where sid

Read more

0 comments, 2,283 reads

Posted in SQL Articles by Nani on 13 May 2016

Default Value or Binding blank vs single quotes with no space in the middle.





Got an issue in the SQL table that it is by default inserting NULL values but I need blank values instead NULLs.


You would see this when you right click on table on object explorer. Right Click on table name and click design and highlight the column you are looking…

Read more

0 comments, 134 reads

Posted in SQL Articles by Nani on 12 May 2016

Common Mistake while creating Operators in SQL Server Agent services

The common mistake people do when creating operator for the first time or adding new email IDs in the Email-name list.

Creating Operators:
Below screenshots give over view on creating Operators in SQL Server.



    1)      Right click on Operators folder and click New Operator.
    2)

Read more

0 comments, 137 reads

Posted in SQL Articles by Nani on 12 May 2016

Basic Insert Trigger Example


Below is the trigger which triggers every time a record insert in to the table to update below mentioned columns.


CREATE TRIGGERTrg_ServiceTickets ON DBO.ServiceTickets
FOR INSERT
AS
    DECLARE  @ServiceAddress         VARCHAR(481)
             ,@ServiceAddressCity     VARCHAR(60)
             ,@ServiceAddressState    VARCHAR

Read more

0 comments, 186 reads

Posted in SQL Articles by Nani on 12 May 2016

Error while creating a new Database by Restore the backup file.


This is the known old issue. I was trying to make a local copy of my business database. I have First created the database and tried to restore the backup and it gave me the below error. Even when I check the Restore Option Overwrite the existing database (WITH REPLACE).

Read more

0 comments, 163 reads

Posted in SQL Articles by Nani on 26 July 2015

INSERTING AN IDENTITY COLUMN IN THE SQL TABLE FROM EXCEL SPREAD SHEET USING SQL SERVER IMPORT AND EXPORT


I had to import data to a SQL table from excel sheet couple of days ago. As the data is huge I cannot manually insert so I have decided to use SQL Server Import/Export Wizard.

Started importing the spread sheet by click on Enable Identity Insert option in the wizard…

Read more

0 comments, 151 reads

Posted in SQL Articles by Nani on 21 May 2015

Older posts