Advertisements

Archive

Archive for the ‘SQL Server Questions’ Category

Seeking response on: LDAP Authentication with SQL Server 2017 on Linux

January 8, 2018 1 comment

 
Few days back I got an email from one of the reader of this blog “Amit Bhatt”. As I haven’t worked much with the AD/LDAP stuff, thus I thought to throw this question to you guys thru this blog post. I also feel this may also help other Developers/DBAs hunting for similar stuff.
 

Here he goes:

We have installed SQL Server 2017 on Linux server. I am able to connect SQL Server locally as well remotely but with local user access.

I have my AD account created and have AD server information. Can you please help me how can I connect using my AD account to SQL Server 2017 on Linux remotely?

I heard something about LDAP Authentication. Is it possible to use this concept without adding Linux server to AD domain? Our security team does not allow to add Linux server in Active Directory group.

I am stucked here since last many days, requesting assistance on urgent basis.

 

Please provide your suggestion on the comment section below.
 

–> Responses:

1. To support AD Authentication, SQL depends on SSSD, SPN and a keytab file which have the required tokens to talk to AD. Without these in place SQL cannot talk to AD which is necessary for AD authentication.
Ref: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-active-directory-authentication

2. If you are looking to login to SQL Server on linux with Windows authentication the linux server should be joined to the domain. If the server cannot be added to domain then SQL authentication is the way to go. The below link has more details to configure windows authentication, https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-active-directory-authentication


 

–> Videos on Linux:

1. Create a Linux VM on Azure

2. Install SQL Server on Linux Azure VM

3. Connect SQL Server on Linux VM from SSMS


Advertisements

Parsing nested JSON in customized SQL Tabular format – MSDN TSQL forum

June 2, 2017 Leave a comment

 
–> Question:

Basically I have a JSON file output that I want to put into a SQL table, but no matter what syntax I try it doesn’t seem to be working.

This is my sample JSON file contents:

{
   "destination_addresses":[
      "Orlando, FL, USA"
   ],
   "origin_addresses":[
      "New York, NY, USA"
   ],
   "rows":[
      {
         "elements":[
            {
               "distance":{
                  "text":"1,072 mi",
                  "value":1725756
               },
               "duration":{
                  "text":"15 hours 40 mins",
                  "value":56405
               },
               "status":"OK"
            }
         ]
      }
   ],
   "status":"OK"
}

What I want is a SQL table that has 2 columns, column 1 will be item names (Destination Address,Origin Address, Distance Text, Distance Value, Duration Text & Duration Value) and column 1 will be the actual data.

I can get OPENJSON to do what I want for destination address & origin address but when I try and using OPENJSON to read the nested items that exist in ‘rows – > elements’ I can’t seem to pick them up. Can anyone help with the right syntax?
 

–> Answer:

declare @str varchar(4000) = 
N'{
   "destination_addresses":[
      "Orlando, FL, USA"
   ],
   "origin_addresses":[
      "New York, NY, USA"
   ],
   "rows":[
      {
         "elements":[
            {
               "distance":{
                  "text":"1,072 mi",
                  "value":1725756
               },
               "duration":{
                  "text":"15 hours 40 mins",
                  "value":56405
               },
               "status":"OK"
            }
         ]
      }
   ],
   "status":"OK"
}'

SELECT 
	CONCAT_WS(' ',Destination_Addresses, Origin_Addresses,Distance_Text,
		Distance_Value,Duration_Text,Duration_Value) as Col1
	,@str as Col2
FROM OPENJSON (@str)
WITH (
    Destination_Addresses    VARCHAR(1000) '$.destination_addresses[0]',
    Origin_Addresses   VARCHAR(1000) '$.origin_addresses[0]',
    Distance_Text    VARCHAR(1000) '$.rows[0].elements[0].distance.text',
    Distance_Value   VARCHAR(1000) '$.rows[0].elements[0].distance.value',
    Duration_Text    VARCHAR(1000) '$.rows[0].elements[0].duration.text',
    Duration_Value   VARCHAR(1000) '$.rows[0].elements[0].duration.value'
) AS Orders

“SELECT *” or “SELECT 1” or “SELECT column_Name” while using EXISTS() – what shoud I use? – MSDN TSQL forum

March 9, 2016 2 comments

–> Question:

Please tell me which method is good and fast

1) select a,b from t1 where exists (select 1 from t2 where t1.t1id = t2.t1id )

2) select a,b from t1 where exists (select * from t2 where t1.t1id = t2.t1id )

3) select a,b from t1 where exists (select t2.t1id from t2 where t1.t1id = t2.t1id )
 

–> My Answer:

It does not matter what you use with SELECT inside EXISTS () statement

Because, as soon as EXIST() statement get one row it breaks/exits, and it just ignores the column’s list in SELECT clause.

You can check by this by simple example:

select a,b from t1 where exists (select 1/0 from t2 where t1.t1id = t2.t1id)

The “SELECT 1/0” ideally should give divide-by-zero error, but here inside EXISTS() it does not.

So, I use “SELECT *” happily within EXISTS() clause 🙂 like:

select a,b from t1 where exists (select * from t2 where t1.t1id = t2.t1id)

 

–> Answer By CELKO:

The optimizer is smart enough to figure out the exist () predicates are the same. However, this was not always true in early versions of SQL. Oracle originally liked using a constant or an actual column name, because it is it did not have a good optimizer. The original SQL–86 specs implied (but did not require) that the table be materialized. We really were not very smart in those days.

Today, for stylistic reasons and to make searching easier, we prefer exists (SELECT * FROM ..) In code. This makes it easier to locate the exist predicates with this text search, and shows that the operation is performed on an entire table rather than a single column.

SQL is much like a natural language; you can be understood if you speak with poor grammar, but people respect you more if you have good grammar and style.
 

Ref link.


How to import/store photos with file name and other properties into SQL Server table – MSDN TSQL forum

January 22, 2016 Leave a comment

–> Question:

I was tasked to load several thousand of photos into the SQL Server. Those photos are saved in a specific folder. I want to store the file name in one column and the photo in another column. It would take month for me to enter one by one.

Is there a way to loop through a given folder and add the file name and photo into the tables using TSQL?
 

–> Answer:

If you are on SQL Server 2012 and ahead, you can use FileTables feature, which is built on top of FileStream and very easy to implement.

FileTable feature does not store files (or images in your case) in SQL Database, but in a secure Windows File-System location which is only visible via SSMS.

Check my blog post on FileTables: https://sqlwithmanoj.com/2012/02/28/sql-server-2012-a-k-a-denali-new-feature-filetables/

After implementing FileTables feature you just need to copy-paste all these images or any kind of file on this folder and you are done.

The retrieval of data is very easy and you get all the properties stored in the metadata files.
 

Ref link.


Parse or Query XML column with XMLNAMESPACES (xmlns namespace) – MSDN TSQL forum

January 15, 2016 Leave a comment

–> Question:

We have SQL audit information.
We would like to select XML column in some user friendly way.

CREATE TABLE [dbo].[audit](
	[server_instance_name] [NVARCHAR](128) NULL,
	[statement] [NVARCHAR](4000) NULL,
	[additional_information] XML NULL
)


INSERT INTO [dbo].[audit]([server_instance_name],[statement],[additional_information]) 
VALUES('srv1','sp_addlinkedsrvlogin','')

INSERT INTO [dbo].[audit]([server_instance_name],[statement],[additional_information]) 
VALUES('srv2','','<action_info xmlns="http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data"><session><![CDATA[Audit$A]]></session><action>event enabled</action><startup_type>manual</startup_type><object><![CDATA[audit_event]]></object></action_info>')

SELECT * FROM [dbo].[audit]

Output of the XML column:

XML parse

Required Output:

XML parse2

 

–> Answer:

;WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data' as ns)
SELECT 
	 [server_instance_name]
	,[statement]
	,[additional_information]
	,t.c.value ('ns:session[1]', 'varchar(50)') AS session
	,t.c.value ('ns:action[1]', 'varchar(50)') AS action
	,t.c.value ('ns:startup_type[1]', 'varchar(50)') AS startup_type
	,t.c.value ('ns:object[1]', 'varchar(50)') AS object
FROM [audit] as a
OUTER APPLY a.additional_information.nodes('//ns:action_info') as t(c)
GO

-- OR -- 

;WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data')
SELECT 
	 [server_instance_name]
	,[statement]
	,t.c.value ('session[1]', 'varchar(50)') AS session
	,t.c.value ('action[1]', 'varchar(50)') AS action
	,t.c.value ('startup_type[1]', 'varchar(50)') AS startup_type
	,t.c.value ('object[1]', 'varchar(50)') AS object
FROM [audit] as a
OUTER APPLY a.additional_information.nodes('//action_info') as t(c)
GO

 

Drop table finally

DROP TABLE [audit]
GO

 

Ref link.