Updating Table Containing Xml Column via LinkedServer

If you are trying to update a table containing XML column via Linked Server in SQL Server, and you are not able to, you are not alone.

There are 2 main issues in this scenario:
1. Accessing a table containing XML column via Linked Server
2. Updating the XML value

When trying to select the XML column of a table via linked server:

SELECT col1, col2, XmlColumn
FROM [LinkedServer].[DatabaseName].dbo.TableName

Error encountered:

Xml data type is not supported in distributed queries. Remote object '[LinkedServer].[DatabaseName].dbo.TableName' has xml column(s).

There are a few workarounds to resolve this (we will focus on using view in this post):

1. Use OPENQUERY

2. Create a view that selects a converted non-XML value from the XML column in the destination DB.

--In destination DB
CREATE VIEW vw_NonXmlColumn
AS
SELECT col1, col2,
    XmlColumn = CONVERT(NVARCHAR(MAX), XmlColumn)
FROM TableName WITH(NOLOCK)

Then, on the source DB where you create the Linked Server, you can access the view instead:

--In source DB
select col1, col2, XmlColumn
from [LinkedServer].[DatabaseName].dbo.vw_NonXmlColumn

This resolves our first issue: accessing a table containing XML column via Linked Server.

Read More

A Severe Error Occurred on The Current Command

SQL Error:
A severe error occurred on the current command. The results, if any, should be discarded.

Solution:
Disconnect and connect to the SQL database again.

Source: Stackoverflow

Read More

XML Data Type is Not Supported in Distributed Queries

Error: Xml data type is not supported in distributed queries. Remote object '<ServerIP>\<ServerInstance>.<DatabaseName>.<Owner>.<TableName>' has xml column(s).

Problem:
This error occurs when trying to select data from table on different server (using linked server) containing XML data type (even though the column with XML data type is not selected).

Solution:
Create a view on the remote server, and then select the view instead of directly selecting from the table containing XML data type.

e.g.
<ServerInstance> = 10.0.0.101
<DatabaseName> = DB1
<Owner> = dbo
<TableName> = Table1

Instead of using:

SELECT * FROM [10.0.0.101.DB1].dbo.Table1

Create a view on the remote server:

CREATE VIEW vGetTable1
AS
SELECT * FROM dbo.Table1

And call the view:

SELECT * FROM [10.0.0.101.DB1].dbo.vGetTable1

Read More

Mapping User Login and Database Roles to Existing User

Error:
User, group, or role 'xxx' already exists in the current database. (.Net SqlClient Data Provider)

Explanation:
This error happens when you try to add user mapping to the server logins using username which already exists in the database to be assigned.

In my case, the user exists in both server logins and database logins, but the user mapping and database roles on the server logins for this user cannot be modified although the user mapping is incorrect, because it already exists on the database logins.

Solution:
Run this script to update the user mapping accordingly.

USE <database_name>
EXEC sp_change_Users_login 'update_one', '<login_username>', '<login_username>'

This will update the user mapping and database roles on server logins to follow the ones on the database logins.

Read More

Directory Lookup Failed on SQL Server

When executing script on SQL server, this error occurs:
Directory lookup for the file <filepath> failed with the operating system error 2(The system cannot find the file specified.)

Directory lookup for the file <filepath> failed with the operating system error 3(The system cannot find the path specified.)

Solution:
Check the directory used in the SQL script. Make sure that the directory used in the SQL script point to the server location, if the script is not directly executed from the server.

Read More

Parameter Sniffing

Parameter sniffing has a weird symptom which can take your hours to find out what actually is happening. The stored procedure you created is running on one side, but when you deploy it on another machine or server, it never ends executing despite the fact that everything is the same.

This problem usually occurs when the query contains LIKE condition. However, it is not limited to this case. Query without LIKE condition can also face this problem.

For example, here is the normal stored procedure:

CREATE PROCEDURE dbo.spr_GetData
@Parameter1 INT,
@Parameter2 VARCHAR(200)
AS
SELECT *
FROM dbo.Table1
WHERE Column1 = @Parameter1
AND Column2 LIKE '%' + @Parameter2 + '%'

To avoid parameter sniffing, there is a need to create new variables to store the values fetched from the parameters.

CREATE PROCEDURE dbo.spr_GetData
@Parameter1 INT,
@Parameter2 VARCHAR(200)
AS

DECLARE
@DummyParameter1 INT = @Parameter1,
@DummyParameter2 VARCHAR(200) = @Parameter2

SELECT *
FROM dbo.Table1
WHERE Column1 = @DummyParameter1
AND Column2 LIKE '%' + @DummyParameter2 + '%'

Read More

Login fail for Sql User

Error: server: msg 18452, level 16, state 1 [microsoft][odbc sql server driver][sql server]login failed for user 'user_name'. Reason: not associated with a trusted sql server connection

Solution: Open Sql Server Enterprise Manager, go to SQL Server Group -> Choose the server -> right-click, Properties -> choose tab Security -> Choose Sql Server and Windows for Authentication, then click OK, and don't forget to restart the service to make sure the new settings work.

Read More