Troubleshooting MS SQL Server: September 2017

Sep 18, 2017

message "ORA-12514: TNS:listener does not currently know of service requested in connect descriptor". [SQLSTATE 01000] (Error 7412). The step failed.

Message

Executed as user:. Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "NHRTRNG". [SQLSTATE 42000] (Error 7303) OLE DB provider "OraOLEDB.Oracle" for linked server "NHRTRNG" returned message "ORA-12514: TNS:listener does not currently know of service requested in connect descriptor". [SQLSTATE 01000] (Error 7412). The step failed.

SQL jobs failing with above error message. This is Oracle linked server issue.

To double check that go to Server Objects-> Linked Servers and right click on Oracle linked server used by the SQL job and click on test connection.

You will see below error message.

TITLE: Microsoft SQL Server Management Studio

------------------------------

The test connection to the linked server failed.

------------------------------

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "NHRTRNG".

OLE DB provider "OraOLEDB.Oracle" for linked server "NHRTRNG" returned message "ORA-12514: TNS:listener does not currently know of service requested in connect descriptor". (Microsoft SQL Server, Error: 7303)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.4232&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476

------------------------------

BUTTONS:

Contact Oracle team to fix the Oracle instance issue.

Sep 14, 2017

Msg 245, Level 16, State 1, Line 6 Conversion failed when converting the nvarchar value 'N' to data type bit.

Msg 245, Level 16, State 1, Line 6

Conversion failed when converting the nvarchar value 'N' to data type bit.

Sometimes we get bit values as Y and N also instead of 0 and 1. If we declare the column as Bit and we receive values as Y or N we get the above error.

Alter the column by updating the column data type to varchar(1) will fix the issue.

alter table contractlinesummary

alter column [ThirdPartyFlag] varchar(1) null

Removing multiple image tags from Text using T-SQL

I have got a requirement to remove entire text between<img and /img> from the text given with html tags without touching any other tags or text. Need to replace <img..../img> with space only to make sure all other tags will not break.

Create a table and insert the given text

use work
go

create table dbo.[text](
text varchar(max) not null
)

insert into text(text)
values(' formatted text to preserve <a class="accountMru" href="https://na4.salesforce.com/001600000012345" style="color: rgb(51, 52, 53); font-family: Arial, Helvetica, sans-serif; background-color: rgb(207, 238, 248);" title="[Alt+1]" target="_blank">link text to linked account</a> (Merged)
<img alt="alt text" height="375" src="https://c.na53.content.force.com/servlet/rtaImage?eid=001600000012345&feoid=00N600000012345&refid=0EM600000001234" width="500"></img><a href=www.test.com>testwording</a>')
,(' formatted text <img src="http://www.externalwebsite.com/img/ c6267244.jpg"></img> formatted text formatted text ')

select * from text

Create function using below code.

CREATE FUNCTION [dbo].[udf_CutImageTags] (@HTMLText VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @Start INT;
DECLARE @End INT;
DECLARE @Length INT;
SET @Start = CHARINDEX('<img', @HTMLText);
SET @End = CHARINDEX('img>', @HTMLText, CHARINDEX('<img', @HTMLText));
SET @Length = (@End - @Start) + 4;
WHILE @Start > 0
AND @End > 0
AND @Length > 0
BEGIN
SET @HTMLText = STUFF(@HTMLText, @Start, @Length, '');
SET @Start = CHARINDEX('img<', @HTMLText);
SET @End = CHARINDEX('img>', @HTMLText, CHARINDEX('<img', @HTMLText));
SET @Length = (@End - @Start) + 4;
END;
RETURN LTRIM(RTRIM(@HTMLText));
END;

Execute this function by passing values from table text and compare the results.

SELECT dbo.udf_CutImageTags2([text]) FROM dbo.text

Let me know if it works for you as well.

Sep 13, 2017

Display Processed Day:Error: The Script Task is corrupted.The Script Task "ST_31c133622a274959a9a421082b570cbf" uses version 14.0 script that is not supported in this release of Integration Services.

Display Processed Day:Error: The Script Task is corrupted.

Display Processed Day:Error: There were errors during task validation.

Display Processed Day_disabled:Error: There was an exception while loading Script Task from XML: System.Exception: The Script Task "ST_31c133622a274959a9a421082b570cbf" uses version 14.0 script that is not supported in this release of Integration Services. To run the package, use the Script Task to create a new VSTA script. In most cases, scripts are converted automatically to use a supported version, when you open a SQL Server Integration Services package in %SQL_PRODUCT_SHORT_NAME% Integration Services.
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask.LoadFromXML(XmlElement elemProj, IDTSInfoEvents events)

This error occurred after updating SSIS packages from Visual Studio 2015 and deploy back to the SQL server 2014. I was able to execute the packages successfully from Visual studio but when I execute from SSMS it throws below errors.

I have noticed the SQL version selected as SQL 2016 for these packages when I the project properties in VS 2015. Change it to SQL Server 2014 or 2012 as same as your SQL Server version, build the project and deploy back to SQL Server to fix the issue.

Check below screenshot where you can find the above mentioned setting.

Msg 8152, Level 16, State 13, Line 27 Error when insert into SQL table from another object of SQL or from Oracle using linked server.

Msg 8152, Level 16, State 13, Line 27

String or binary data would be truncated.

The statement has been terminated.

Cause: Inserting bigger value than the declared size.

Fix: Find the column, which is error out, compare with the source column size, and increase it in the destination object. Try again inserting the data.

To find the problematic column try inserting small subset by keep adding the additional columns. That is the easy way to troubleshoot.

INSERT INTO [dbo].[TestSummary]

([PrimaryID]

,[Organization]

,[SubLineStatusCode]

,[OrganizationID]

,[ContractNumber]

)

SELECT * FROM OPENQUERY(NHRTEST,'SELECT PRIMARY_ID,

ORGANIZATION,

SUB_LINE_STATUS_CODE,

ORGANIZATION_ID,

CONTRACT_NUMBER,

FROM ~~APPS.XXNHR_SERVICE_CONTRACT_V~~')

Sep 12, 2017

Reverse Engineering using Visio 2010

Reverse Engineering is a very good option Microsoft given us to pull database objects into Visio to generate different model diagrams. Unfortunately this was removed after 2010 edition. I have installed Visio 2010 to get one ER diagram from our warehouse. Please see steps I followed.

1) Connect to Visio 2010 and click New and Software and Database template.