|
|
|
|
|
|
|
|
Question of the Day |
Today's question (by Steve Jones - SSC Editor): | |
Adding a FK in a CREATE TABLE | |
I want to create a table with a self-referencing FK. Can I do this in one CREATE TABLE statement or do I need an ALTER TABLE to add the FK? | |
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) |
Extracting JSON I have this code: DECLARE @JSON NVARCHAR(MAX) = '{ "ChannelIDs": [11,14,15,16], "SerialNumber": "939029", "ReadingStartDate": "2022-05-20T13:49:13", "ReadingEndDate": "2023-01-09T17:44:05", "Amount": 50, "SortOrder": "Ascending", "IncludeFirstDate": 1 }' Which query will get me a list of the ChannelIDs as separate rows? Answer: select oj.[value] AS ChannelID from openjson(@json, '$.ChannelIDs') oj; Explanation: The openjson function will return a table that you can query and get the rows. In this case, giving the path to the ChannelIDs array lets you deserialize these into separate rows. The other queries do not work. Ref: OPENJSON - https://learn.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql?view=sql-server-ver16 |
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 - Development |
Can’t return role based on employee no from three columns? - I need to write a SQL Server query return only one role from table dbo.F6000059 based on employee no, but I face issue I don't know how to return the correct role. I will return only one role from table dbo.F6000059 if "employee no" exists in columns REAN82 or REAN83 or REAN84. And role name will return will be only one roles from below […] |
SQL Server 2016 - Development and T-SQL |
Delete large number of rows help - I have a batch process where i have to delete around 75 MIL rows out of 1.3 Billion rows table. And then insert the new rows back. I have been told that there is no way to do incremental load on these. I believe the DELETE operation is taking very long (2-3 hours). Database is […] |
SQL 2012 - General |
how to calculate the RAM used while running a query - how to calculate the RAM used while running a query |
SQL Server 2012 - T-SQL |
Adding records between records - Hi, Trying to figure out how to approach this... I will have data where these is an ID and a date. I want to add records between the two dates Max up to 6 days between the actual dates. example DATA: ID, SomeDate, Flag 3642570, 2023-09-01 00:00:00.000, X 3642570, 2023-09-06 00:00:00.000, X 3642570, 2023-09-13 00:00:00.000, […] |
SQL Server 2019 - Administration |
How to enable Always Encryption in SQL Server Agent Jobs - I am trying to run a SQL Agent job to select an encrypted column, but it's still shows as encrypted. How can I run SQL agent job to run with Always Encryption enabled. |
SQL SSIS job failing with out of memory error - Hi All, Most of the time SQL SSIS job failing with out of memory error. The package are hosted in SSIS catalog. My question is - Will SSIS & SSAS use memory within SQL max and min memory (or) out of SQL server? I have SQL SSIS, SSAS & two instance of DB engine. I […] |
Ola IndexOptimize - USER_DATABASES failed - EXECUTE [dbo].[IndexOptimize] @Databases = 'USER_DATABASES', @LogToTable = 'Y' Job Name IndexOptimize - USER_DATABASES Step Name IndexOptimize - USER_DATABASES Duration 01:00:14 Sql Severity 16 Sql Message ID 50000 Operator Emailed Operator Net sent Operator Paged Retries Attempted 0 Message Executed as user: NT SERVICE\SQLSERVERAGENT. ...0) Server: SERVER1 [SQLSTATE 01000] (Message 50000) Version: 15.0.4316.3 [SQLSTATE 01000] […] |
Ola IndexOptimize - USER_DATABASES failed - EXECUTE [dbo].[IndexOptimize] @Databases = 'USER_DATABASES', @LogToTable = 'Y' Job Name IndexOptimize - USER_DATABASES Step Name IndexOptimize - USER_DATABASES Duration 01:00:14 Sql Severity 16 Sql Message ID 50000 Operator Emailed Operator Net sent Operator Paged Retries Attempted 0 Message Executed as user: NT SERVICE\SQLSERVERAGENT. ...0) Server: SERVERABC [SQLSTATE 01000] (Message 50000) Version: 15.0.4316.3 [SQLSTATE 01000] […] |
Expand disk size for SQL 2019 High Avalability Group - Hi, Due to the data growth, we have to increase the disk size where the data files resides. Our SQL 2019 high availability group is running in VMWare v.7. 2 nodes. Do I right click the VM, Edit Settings, then increase the disk size, then expand it in disk manager? I assume I have to […] |
KB5029375 - SQL Server 2017 GDR - I have been trying, on and off, to install the latest CU/GDRs on test servers since Friday by obtaining the updates from: Latest updates and version history for SQL Server - SQL Server | Microsoft Learn I have had no problem with SQL2019 CU23 or the SQL2016 GDR. I cannot get the SQL2017 GDR to […] |
SQL Server 2008 - General |
MSSqlServer 10.50.6000.34 Event-ID 18456 LoginError - Hi Experts, how can I fix this error NT-AUTHORITY\SYSTEM Cause: Error opening the explicitly specified database. [CLIENT: ] Thank you, Stephan |
MSSqlServer 10.50.6000.34 Event-ID 18456 LoginError - Dear Experts, Nothing special, just an annoying returning entry in Windows-Event- Protocol. => SQL Server 2008 However, the Event viewer is throwing permanently an error: Error logging in for the user 'NT-AUTHORITY\SYSTEM'. Cause: Error opening the explicitly specified database. [CLIENT: ] - System - Provider [ Name] MSSQLSERVER - EventID 18456 [ Qualifiers] 49152 […] |
SSRS 2016 |
100% stack bar chart with periods and time axis - Hello, I am trying to develop a report that would display periods of time (segments) on time axis and grouped by equipment, like below: Colored bars are segments (Duration in seconds). On X-Axis i would like to display time. But as long as i am using X-Axis as a default 1-100 i can see segments, […] |
Integration Services |
Trouble Upgrading SSIS Project and Packages from Targeting SQL 2014 to 2022 - Taking an existing Visual Studio SSIS Project that targets SQL Server 2014 to now target SQL Server 2022 through the suggested course of the "Upgrade All Packages" has been unsuccessful. The reason being was we were trying to avoid having to recreate the project and packages from scratch. We are using Visual Studio 2019 version […] |
SQL Server 2022 - Development |
Extract file in zip format - Good afternoon, Devs. My name is André, and I'm new here. I'd like to ask for your assistance with a problem I'm currently facing. I've taken over the migration of an application where the database is in SQL Server, and there's a table containing files stored in a column of type "ntext." According to the […] |
| |
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com |