Advertisements

Archive

Posts Tagged ‘MSDN TSQL forum’

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


 
–> 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

Advertisements

Which SQL Server Certification should I take: 98-364, 70-461 or 70-761 ? – MSDN TSQL forum

February 18, 2017 3 comments

 
–> Question:

I just started learning SQL Server, and I want to get certified, I checked internet, but I got confused.

There is exam 98-364, 70-461, 70-761, and i want to get MCSA in SQL

could some one explain to me what should i do …
 

–> My Answer:

>> MCSA or Microsoft Certified Solutions Associate in SQL Server can be earned on following areas with respective exams:

– Database Development (761, 762)
– Database Administration (764, 765)
– Business Intelligence (767, 768)

>> Exam 98-364 is to get Microsoft Technology Associate (MTA) certification. It is the most basic exam that focuses just on basic SQL, and is for people new to technology, who need to validate their knowledge of fundamental concepts before moving on to more advanced certifications. This exam covers:

1. RDBMS, DBMS concepts and terms, like Normanization, PK, FK, etc.
2. Plain SQL querying knowledge, like DDL, DML, etc. and indexes.
3. Simple DBA stuff, like security, backup/restore, etc.

Link: https://www.microsoft.com/en-in/learning/exam-98-364.aspx

>> Exam 70-761 is also an advanced exam which is based upon SQL Server 2016, which is the latest version of SQL Server as of today. To earn MCSA you have to take 70-762 exam as well as mentioned above, check this link for MCSA SQL Server 2016: https://www.microsoft.com/en-in/learning/mcsa-sql-2016-certification.aspx

>> Exam 70-461 is also an advance level exam but now older based upon SQL Server 2012 or 2014. Check this link for MCSA SQL Server 2012/2014: https://www.microsoft.com/en-in/learning/mcsa-sql-certification.aspx

Please note: Exam 98-364 is not required here, and as mentioned above its just to make sure you have knowledge in working with basic SQL Querying.

Ref link.


SQL Server CROSS APPLY and OUTER APPLY usage – MSDN TSQL forum


–> Question:

I need to see two small scenario when people should use CROSS APPLY and OUTER APPLY.

Please discuss the scenario with code and example.

Thanks !
 

–> My Answer:

CROSS APPLY acts like an INNER JOIN, and OUTER APPLY acts like a LEFT OUTER JOIN.

–> The APPLY clause (irrespective of CROSS/OUTER option) gives you flexibility to pass table’s columns as parameters to UDFs/functions while Joining while that table. It was not possible with JOINS. The function will execute for each row value passed to the UDF as parameter. But the JOIN works as a whole set.

Check the blog post on CROSS APPLY vs OUTER APPLY operators, https://sqlwithmanoj.com/2010/12/11/cross-apply-outer-apply/

–> Apart from this you can also use APPLY clause with Tables/SubQueries, like if you want to get top 5 products sold by sales persons, or get top 10 populated Cities from all States.

Check here: Using CROSS APPLY & OUTER APPLY operators with UDFs, Derived-Tables/Sub-Queries & XML data, https://sqlwithmanoj.com/2012/01/03/using-cross-apply-outer-apply-operators-with-udfs-derived-tables-xml-data/
 

–> Answer from Russ Loski:

First let’s start with the use for APPLY.

You would use APPLY if you need to use a column from a table as an argument in a derived table or function. For example, this query from http://blog.sqlauthority.com/2009/08/21/sql-server-get-query-plan-along-with-query-text-and-execution-count/:

SELECT cp.objtype AS ObjectType,
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
cp.usecounts AS ExecutionCount,
st.TEXT AS QueryText,
qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
--WHERE OBJECT_NAME(st.objectid,st.dbid) = 'YourObjectName'

I need to use columns from dm_exec_cahced_plans to pass to two functions to get rows from those functions. I have to use the APPLY keyword (rather than Join) to be able to do that.

I can do the same with a derived table:

SELECT * 
FROM TableA 
outer apply (
SELECT * from TableB
where TableA.id = TableB.id
) tb2

That is a horrible example (you can do the same using standard join syntax). But there are very rare circumstances where I need to use a column in a where clause in a derived table, but I can’t use a join.

The difference between CROSS APPLY and OUTER APPLY is the difference between INNER JOIN and OUTER JOIN. CROSS APPLY will only return rows where there is a row in both the first table and the second table/function, while OUTER APPLY returns a row if there is a row in the first Table even if the second table/function returns no rows.
 

Ref link.


SQL Server 2012: Error handling for multiple errors – MSDN TSQL forum


–> Question:

I have a stored procedure which steps through a list of transaction log backups and applies them to a DR database with NORECOVERY. As part of our DR test process, it is sometimes possible that the DR database gets ahead of the transaction log list. In this circumstance I get the error:

Msg 4326, Level 16, State 1, Line 8
The log in this backup set terminates at LSN 74000000023300001, which is too early to apply to the database. A more recent log backup that includes LSN 74000000025200001 can be restored.

Msg 3013, Level 16, State 1, Line 8
RESTORE LOG is terminating abnormally.

This is fine and I’m happy to ignore this log and move on to the next one. However there appears to be no way to catch the 4326 error. Wrapping the restore in a TRY … CATCH only identifies the 3013 error number and that could be caused by many issues that I don’t want to ignore. If my TRY … CATCH uses the THROW command, both the 4326 and 3013 errors are displayed so my session clearly has a handle to them both.

My questions are:

1. Can I catch the first error thrown?
2. Can I review all the errors thrown?
3. Can the output from THROW be captured in a variable so I can parse it?
 

–> Answer:

While using RAISERROR it will only catch the last error thrown, it won’t catch and return all the errors.

The new THROW keyword introduced in SQL Server 2012 returns all the errors, check this link: https://sqlwithmanoj.com/2015/02/04/capture-multiple-errors-in-try-catch-by-using-throw-statement/

But I don’t think if there is any mechanism to store both the errors returned by any script as after THROW the execution ends and control is transferred to the client.

Other than using DBCC OUTBUFFER(@@spid), you will need to parse the multiple error details spread through several rows. 

Ref link.


How resolve error OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)” returned message “Unspecified error – MSDN TSQL forum


–> Question:

Im getting following error:

OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)” returned message “Unspecified error”.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)”.

I am using the following query:

select * 
from openrowset('Microsoft.ACE.OLEDB.12.0','Excel 12.0;
Database=\\abc\k\excel.xlsx',[sheet1$])

driver Microsoft.ACE.OLEDB.12.0 installed and configure properly.

Any help ?
 

–> Answer:

Check this link: https://sqlwithmanoj.com/2012/07/10/querying-excel-2010-from-sql-server-in-64-bit-environment/

You might need to register the ACE OLEDB provider, go thru all the steps I’ve mentioned in my blog, may help.
 

Ref link.