|
|
|
|
|
|
|
Question of the Day |
Today's question (by Steve Jones - SSC Editor): | |
Getting the JSON Index | |
I have this JSON document:
DECLARE @Json NVARCHAR(MAX) = N' [ { "Name": "Broncos", "Division": "AFC West" }, { "Name": "Chiefs", "Division": "AFC West" }, { "Name": "Steelers", "Division": "AFC North" }, { "Name": "Colts", "Division": "AFC South" } ];';I want to query this and get a result set like this: Element Team Division -------- -------- ------------------------------------------------ 0 Broncos AFC West 1 Chiefs AFC West 2 Steelers AFC North 3 Colts AFC SouthI have this query, but what do I put in place of "xxx" to get the index value of the data? SELECT A.[xxx] AS Element, JSON_VALUE(A.Value, '$.Name') AS Team, JSON_VALUE(A.Value, '$.Division') AS Division FROM OPENJSON(@Json) AS A; | |
Think you know the answer? Click here, and find out if you are right. |
Yesterday's Question of the Day (by Steve Jones - SSC Editor) |
The Firewall Issue What's the issue here? Answer: The datatype is wrong. Explanation: The problem is that the first parameter, the rule name, is an NVARCHAR() type. Since this procedure is actually an extended stored procedure in Azure SQL Database, the data types must match. This code would work: exec sp_set_firewall_rule N'Test Rule', '1.1.1.1', '1.1.1.1' Ref: sp_set_firewall_rule - https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-set-firewall-rule-azure-sql-database?view=azure-sqldw-latest |
Database Pros Who Need Your Help |
Here's a few of the new posts today on the forums. To see more, visit the forums. |
SQL Server 2017 - Administration |
Wait stats - I am not seeing any high resource utilization like CPU/Memory and Disk queue length. But seeing a waits during the performance issues. Is that be related to the issue? Please advise? |
SQL Server 2017 - Development |
Need help on ( Keep it existing, Add new ) & ( Remove existing, Add new ) - I've table & EXISTING data as below, Scripts as following, CREATE TABLE [dbo].[IncidentProgressAssignPeople]( [Id] [int] IDENTITY(1,1) NOT NULL, [IncidentProgressId] [int] NOT NULL, [ToUserId] [nvarchar](450) NOT NULL, [ReplyDate] [datetime] NOT NULL, [isInProgress] [bit] NULL, [isInProgressDate] [datetime] NULL, [isResolved] [bit] NULL, [isResolvedDate] [datetime] NULL, [dtVersion] [timestamp] NULL, CONSTRAINT [PK_IncidentProgressAssignPeople] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX […] |
SQL Server 2016 - Administration |
AD Group SQL Login Access Details - Good Morning, We have AD Group with members. The AD Group is created as SQL Server login. I would like to know what all access this login has. Please let me know. |
Default Trace File Q - Are Does Not Exist and Do Not Have Permission events inc - SQL SERVER 2016 (13.0.5026.0) NOTE: The reference SQL code is not something I/We created but was setup by Prophix when they implemented the system for us. I am only responsible for trouble-shooting it when things fail, not for the T-SQL code it uses. We have a B2B app named Prophix that drops an existing table […] |
Administration - SQL Server 2014 |
Probleme With Access to Reporting Services SSRS - Hi I have a problem when i try tio display Report, (Win Server 2008R2 And SQL 2014 ) i can access only with a Local account, but for the athers AD account it display an error like see attach, But before it s working correctly. Withe the local account i can display the page, but […] |
3 node Always On but only 1 node points to correct listener IP - Hello, I have 3 node SQL server 2014 SP3-CU4-GDR, and each server is on a different subnet: server1: 192.168.1.25 server2: 192.168.2.25 server3: 192.168.3.25 and I have the listener with mutliple subnets configured like so: 192.168.1.0/24 - ip address 192.168.1.26 192.168.2.0/24 - ip address 192.168.2.26 192.168.3.0/24 - ip address 192.168.3.26 192.168.4.0/24 - ip address 192.168.4.26 so […] |
SQL 2012 - General |
What makes MS SQL Server worth paying for over MySQL? - Considering well designed, large DBs that have high usage, what do MS SQL Server and another commercial DB systems offer that MySQL and other free systems don't? Is it a performance thing? security, backup, redundancy? |
How to use inner join instead of where exists statment ? - I work on sql server 2012 i face issue ican't replace (where exists) by inner join so How to do it SELECT pr.partid from parts.Nop_Part pr with(nolock) inner join extractreports.dbo.RequiredPartsPL rp with(nolock) on rp.partid=pr.partid inner join Parts.Nop_PackageAttribute pa WITH(NOLOCK) on pa.packageid=pr.packageid inner JOIN dbo.Core_DataDefinitionDetails dd WITH(NOLOCK) ON pa.[Key] = dd.ColumnNumber --and dd.acceptedvalueid=64 where exists(select 1 […] |
SQL Server 2019 - Development |
Copy files from folder using SQL Server. - Good day everyone. I hope someone can help me on that one. I have a database that include file folder and document location into tables and the actual document is store on a document server. I would like to copy specific documents to a different location and rename the document to a more readable name […] |
Merge Conflicts with SQL Server Database Project - I hope someone more familiar with database projects can provide me some guidance. I am currently trying to introduce a better source control solution and automated deployment solution for a project I have joined. The current state is I have a database project in Visual Studio 2019 which will be used to maintain an Azure […] |
Implicit conversion on hash key column is causes very slow insert - I am trying to insert some data into some stage tables where the insert is taking far too long. For example a table containing up 600000 records is taking nearly an hour to complete. In the select part of the query we are are creating a hash of the columns which is later used for […] |
SQL Azure - Development |
Unable to add data disk in Azure SQL Virtual Machine - Trying to extend my data disk to get some free space in the drive. But I couldn't able to resize the data disk in my SQL Virtual Machine in Azure as the resize option greyed out. And the disk is managed disks only. Please some one can suggest me on this...., it would be greatly […] |
Amazon AWS and other cloud vendors |
Does AWS RDS SQL SERVER support to file access from EC2 server local path? - I would like see is there any option to access the EC2 machine file from RSD SQL Server? Notes: EC2 and RDS were in the same VPC. I am using RDS SQL server Standard Edition. Can you please help me on this? if it doesn't are there any plans to? Thanks in advance! |
General Cloud Computing Questions |
anyone using vercel sqlite3? - Hello! I am trying to use sqlite3 on Vercel just to setup my environment, but I don't know how. I saw that when deploying python app, Vercel installs sqlite3 on their own but I can't find a way to access it ... my python app can't find sqlite3 module . How can I get an […] |
SSDT |
Execute sql task behave differently when run in isolation versus part of package - SQL Server 2019 Azure. I have a basic fact load package that someone has tagged an execute sql object onto the end of that updates various fields in a fact table. when I run the whole package, 2 out of the 6 columns do not update and when selecting from the table, they appear as […] |
| |
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com |