Laden...
|
|
|
|
|
|
|
Question of the Day |
Today's question (by Steve Jones - SSC Editor): | |
Creating Two Keys | |
On SQL Server 2019, I run this code. What happens? CREATE SYMMETRIC KEY PIISymKey WITH ALGORITHM = AES_128 , IDENTITY_VALUE = 'S0methingN3w' , KEY_SOURCE = 'Someth!ngBl&e' ENCRYPTION BY PASSWORD = 'MyS3cr#tP@ssword'; CREATE SYMMETRIC KEY HIPPASymKey WITH ALGORITHM = AES_128 , IDENTITY_VALUE = 'S0methingN3w' , KEY_SOURCE = 'Someth!ngBl&e' ENCRYPTION BY PASSWORD = 'MyS3cr#tP@ssword'; | |
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 Upgraded Procedure part 2 I had a SQL Server 2014 database. In it, I created this function: CREATE FUNCTION OPENJSON (@json varchar(1000)) RETURNS TABLE AS RETURN SELECT CASE WHEN SUBSTRING (@json, 1, 1) = '{' THEN 1 ELSE 0 END AS json_string;This works, and this call: SELECT * FROM OPENJSON('{ "key":1 }')Returns this result: json_string -------------- 1Now I restore this database on a SQL Server 2019 instance and set the compatibility level to 150. I run this code: SELECT * FROM dbo.OPENJSON('{ "key":1 }')What happens? Answer: I get an incorrect syntax error Explanation: Once I upgrade the database and compatibility level, the OPENJSON function becomes the valid syntax. The UDF is not longer valid. Ref: ALTER DATABASE - https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?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 - Administration |
Public Role Permissions concern - All, I've been double-checking security for one of my servers and I found the below permissions on the public role. I'm trying to decide if those are default / system-granted permissions that are necessary for the functioning of SQL Server or if these might have been granted by someone some time ago. Does anyone know? […] |
SQL Server 2016 - Administration |
SSRS on Alwayson Availability Group - Hi, Installed SSRS feature in Primary replica and configured ReportServer and ReportTEMPDB by using SQL listener name. ReportServer and ReportTEMPDB brought into Availability Database and two databases are synchronized between two primary and secondary replica. SSRS reports rdl files kept it Node1 and able access reports http://Node1/Reports/browse/ working as expected and tested AG failover between […] |
SQL Server 2016 - Development and T-SQL |
How to get total sum across the table and sum grouped by date ranges - Here is sample code and what I tried to achieve, but not getting the output, any advice? select k.charges, sum(case when k.range>='101' then charges else 0 end) as '101charges', sum(case when k.range>='201' then charges else 0 end) as '201charge' from (select charges,substring(daterange,1,3) as range from dbo.charges, )k CREATE TABLE charges ( charges money […] |
Converting file name MMDDYYYY.txt to a Date Field - Hello, Converting file name MMDDYYYY.txt to a Date Field but CONVERT or CAST do not seem to work perhaps because I also have a REPLACE? Can someone help me understand how to do this if it is possible? See sample code below and many thanks in advance! CREATE TABLE #t (getFileName varchar(100)) INSERT INTO #T […] |
SQL Server 2019 - Development |
extracting data from JSON with TSQL - Hi, I need help extracting data from the below JSON. What I need is the data from the "frequencyData" array. on the first row of the resultset I would like the first column to contain the first data sample of the "frequency"child array, the second column should contain the first data sample of the "df" […] |
Value does not fall within the expected range. (Microsoft.SqlServer.ManagedDTS) - Good morning experts, Over the past few releases, my team and I have been running into this issue where when we deploy an SSIS Project from one SQL Server to another we get this error on the "Changing Protection Level part: Value does not fall within the expected range. (Microsoft.SqlServer.ManagedDTS) We have about 15 projects […] |
Script for deleting data from a table and corresponding FKTables - Hi, Looking for a script to delete data from a table and also from the FK tables of data exists. I have seen some online using procedures and function but just looking for a base script |
SQL Server 2008 - General |
P2V os sql vm to new host - I have used P2V to replicate my sql vm to a new host alongside all its disks storage, the problem is that these disks are part of a failover cluster and when I try to bring them online on the new host the following error occurs: The disk must be in cluster maintenance mode and […] |
SSDT |
Partial Model Project With Same DB Reference - Hello, I am trying to create an SSDT project (in VS2022) for a database whose objects are primarily owned by a vendor application. This application dynamically creates views that we interface with from 'under-the-hood' tables that have non human-readable names. We have some custom integration code including some procedures, views, and functions, which are in-house […] |
Analysis Services |
How to connect SSAS TABULER with mysql data source - Hello I have a mysql data source and I want to use it as data source to my analysis service… I could connect it through ssis and ssrs using odbc data source but I face a hard time finding odbc or ado.net data source in my ssas… can anyone help me with steps and photos […] |
SQL Server 2022 - Administration |
Kubernetes Hands-On Courses - Hello, All I appreciate it may not be directly relevant to SQL Server, but I will be grateful if someone can recommend me (a company I work for are ready to chip in) a good Kubernetes course with practical element in it, please. There are plenty of free courses over the internet or event on […] |
SQL Job on server set to UTC. Are jobs that perform GETDATE using UTC? - This is a scenario I haven't had to consider. We have a UTC time set SQL Server running jobs. We have set the jobs to run in based on UTC time converted from the timezone we're in (+10 hours) eg 6:45pm UTC (4:45am AEST) However one of the jobs calls a view that uses a […] |
SQL Server 2022 - Development |
Both values between Start and End date - good afternoon, I'm trying to determine if both values (101 and 102) both occur within a 'Shift' (startdate/enddate). It originally just needed 101, but our facility added 102 and for the 'goal' to be compliant (1), they need to be both present within a shift. I'm having a bit of trouble working this out within […] |
Creating a subquery which conflicts with the where clause... - Hi All, I have created a table named issues where incidents are being tracked. Every record has a master indicator column ("Y" or "N")which denotes that its the master incident. If the same incident type is created by another user, then the Master Indicator will be set to N and the master_issue column will be […] |
Database Query Optimization: How to Improve Query Performance in SQL - I have a SQL database that stores a large amount of data, and I'm experiencing slow query performance when retrieving information. What are some strategies and best practices for optimizing database queries in SQL to achieve better performance? Here's an example query I'm working with: SELECT first_name, last_name, email FROM users WHERE registration_date >= '2023-01-01' […] |
| |
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. [email protected] |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Laden...
Laden...