|
|
|
|
|
|
|
|
Question of the Day |
Today's question (by Steve Jones - SSC Editor): | |
The Upgraded Stored Procedure | |
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 OPENJSON('{ "key":1 }')What happens? | |
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) |
Tracking Backup to Nul I have a SQL Server 2019 database and I run this command: BACKUP DATABASE way0utwest TO disk = 'nul' If I check msdb.dbo.backupset, what do I see for this backup? Answer: The backup is listed with a filesize the same size as a full backup Explanation: Whenever a backup is run, it is recorded in msdb. This includes backups to the nul device. The sizes listed are the same as a backup to a disk file. Ref: BACKUP - https://learn.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-ver16#arguments |
Featured Script |
Girish Ganesamanian Tirunelveli from SQLServerCentral Csv files must frequently be joined. It would be fantastic if we could connect CSV files using the power of SQL. This script accomplishes that.
|
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 |
shrinking a large data file - Hi All, One of our drive got filled (E: drive) and we decided to shrink the data file from 1.6TB to 500GB. While trying to shrink the file with didnt work. USE [dbname] GO DBCC SHRINKFILE (N'db_name_dat' , 0, TRUNCATEONLY) GO it completed successfully but it didnt release any space to OS. --then, tried below. […] |
unable to see job history for some jobs - Hi All, I am unable to see job history for some jobs. Usually, we run some maintenance jobs. for example) index maintenance and updating stats. Why it is not showing up? anything needs to be changed in SQL Agent Properties? Regards, Sam |
SQL Server 2016 - Administration |
BCP with ODBC 18? - Hello all. My org has identified ODBC driver version 17 as having security vulnerabilities. It looks as though 17.10.4.1 remedies these, but we're considering v18 anyways. It looks as though bcp requires version 17. Is this the case? Surely not, though I don't see otherwise. |
Administration - SQL Server 2014 |
Maintenance Task Failed - how to log the error - I've a Multi step Maintenance plan of 5 steps called "Reindex". This automatically created an Agent job called "Reindex..Subplan_1" There's an email alert on failure of the Agent job. Is there any place in maintenance task design to put an alert in or do I always have to use add the alert to Agent task […] |
Development - SQL Server 2014 |
Replace bad characters - Hi all I need help to replace bad characters with space in string. select col1, patindex('%[^ !-~]%' COLLATE Latin1_General_BIN,col1) as [Position], substring(col1,patindex('%[^ !-~]%' COLLATE Latin1_General_BIN,col1),1) as [InvalidCharacter], ascii(substring(col1,patindex('%[^ !-~]%' COLLATE Latin1_General_BIN,col1),1)) as [ASCIICode] from myTable where patindex('%[^ !-~]%' COLLATE Latin1_General_BIN,col1) >0 Here is the result: Col1 […] |
SQL Server 2019 - Development |
MS Sync Framework Error - Failed to Execute the command BulkUpdateCommand - It turns out that the error is in a trigger on the Transfers table, so this can be ignored. Sorry if you've wasted your time reading this. Hi, I've got two databases at distant locations that sync evey few minutes using the Ms Sync Framework 2.1. I'm getting this error occurring: ERROR , SyncBPO, […] |
SQL Azure - Administration |
Azure SQL database: How additional storage (beyond 250GB) being charged? - Hi All, I have a question on how additional storage (beyond 250GB) will be charged for Azure SQL databases. Is it charged based on Max storage limit (MAXSIZE) or is it based on Allocated space? Thanks. |
Reporting Services |
Tabs in SSRS Report - Hi, I have 2 tabs created in SSRS Report.There is calendar Day prompt in the report. By Selecting the Calendar Day Prompt the 2nd tab displayed in the report varies from 2nd Page and 3rd page. Is there any option to display the 2nd tab of the Report to display in 3rd page irrespective of […] |
SSRS 2019 Custom Authentication: error occurred when invoking the authorization - Hi, I am upgrading SSRS 2014 to 2019 and custom form based authentication is implemented with SSRS 2014. I followed the steps https://github.com/microsoft/Reporting-Services/tree/master/CustomSecuritySample and implemented same for SSRS 2019. While Web Service URL works fine but while accessing Web Portal URL I am getting below error and the web page displays. The service is not available. An […] |
Migrating Reports from SQL Server 2014 to SQL Server 2022 via Visual Studio 2022 - Migrating Reports from SQL Server 2014 to SQL Server 2022 via Visual Studio 2022 with Reporting Services extension installed. I am having trouble with parameters and the error message: "Must declare the scalar variable @TellMeWhyIdontlikeThisMonday". I'm also getting the error message: "Custom parameter layout was removed from the report. SQL Server 2014 Reporting Services and […] |
SQL Server 2022 - Administration |
issue with backup cleanup step - I have a maintenance plan for my nightly backups. It consists of three steps: Back up my databases to a folder on another server - in this case it's literally just a Windows server with a lot of drive space. Run DBCC check db against my databases. Cleanup - delete any backups older than five […] |
SQL Server 2022 - Development |
SQL2022 UPDATED FROM SQL2014 - PARALLELISM ISSUES - Hi We just updated from SQL2014 to SQL2022. Most everything went fairly well - except I have about 6 stored procs that move allot of data around that are suddenly not working. When looking at whoisactive they are generating CX waits. They stall all day with no results. They ran fine in SQL2014. I enabled […] |
Need Assistance- 32-bit SSIS Package and Analyzing Shift-wise Data - I'm currently working with SQL Server 2022 - Development edition and I'm facing a couple of challenges while dealing with a 32-bit SSIS package. This package involves processing data that spans across different shifts, and I need to perform some analysis on it. My dataset includes values recorded across different shifts, and I need to […] |
Running 32 bit SSIS package - This is my first post in this forum, so hello everyone! Would be most grateful for help with the following issue: a simple SSIS package (created in Visual Studio 2019) imports a table from an ODBC Source (MySQL Server 5.xxx) into a database in a SQL Server 2022. ODBC Source is 32-bit, the package runs […] |
Searching in date field provides inconsistent results - I have been working with SQL Server for too many years to count and this is baffling; perhaps someone has a clue on the cause. I am running this on SQL Server 2022. I put together a simple function to return a primary key integer for a lookup table that has mileage rates that are […] |
| |
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com |