I am using a script to automatically update a database daily to sync Azure Blob Storage with a queryable Azure SQL Server database. Among deleting and updating records, I will insert brand new records that meet a certain criteria. The following is an insert statement that I am working with currently.
The first record is inserted into the database without issue, however, the second record throws an error:
Conversion failed when converting the nvarchar value '[{"Date":"2019-06-17"},{"Index":"2"},{"PAN":"987"},{"Phase":"Construction"},{"Primary":"False"},{"Project ID":"3484"}]' to data type int.
The column that this nvarchar
is referencing is defnied as a nvarchar(MAX)
, not an int
.
Database schema: https://gyazo.com/b95367d03e71b6f1138111668bf56e05
Create a table...
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [BlobSync].[BlobInventory]
(
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[AccountName] [nvarchar](max) NOT NULL,
[Name] [nvarchar](max) NOT NULL,
[CreationTime] [datetime] NOT NULL,
[LastModified] [datetime] NOT NULL,
[Etag] [nvarchar](max) NULL,
[ContentLength] [bigint] NULL,
[ContentType] [nvarchar](max) NULL,
[ContentEncoding] [nvarchar](max) NULL,
[ContentLanguage] [nvarchar](max) NULL,
[BlobType] [nvarchar](max) NULL,
[AccessTier] [nvarchar](max) NULL,
[Metadata] [nvarchar](max) NULL,
[Deleted] [bit] NULL,
[RemainingRetentionDays] [int] NULL,
[TagCount] [int] NULL,
[Tags] [nvarchar](max) NULL,
[LastUpdatedUTC] [datetime] NULL,
CONSTRAINT [PK_BlobInventory]
PRIMARY KEY CLUSTERED ([ID] ASC)
WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
DROP TABLE IF EXISTS #TempTableInsert;
SELECT *
INTO #TempTableInsert
FROM [BlobSync].[BlobInventory]
WHERE [ID] < 0;
INSERT INTO #TempTableInsert (AccountName, Name, CreationTime, LastModified, Etag, ContentLength, ContentType, ContentEncoding, ContentLanguage, BlobType, AccessTier, Metadata, Deleted, RemainingRetentionDays, Tags, TagCount, LastUpdatedUTC)
VALUES
(
'blobstoragename'
, 'project-images/bdb70f3e-8d8b-41f1-b357-66e1792280e9'
, '7/22/2024 2:28:44 PM'
, '7/23/2024 12:53:18 PM'
, '0x8DCAB597AF04A5D'
, 2380580
, 'image/avif'
, null
, null
, 'BlockBlob'
, 'Cool'
, '{"Description":"This is a description.","FileName":"20190611_120704.jpg"}'
, null
, null
, '[{"Date":"2019-06-17"},{"Index":"1"},{"PAN":"23567"},{"Phase":"Design"},{"Primary":"False"},{"Project ID":"3484"}]'
, 6
, null),
('blobstoragename'
, 'project-images/dd7f2b7e-a824-4ab9-992f-4eae791f8bc6'
, '7/22/2024 2:28:44 PM'
, '7/22/2024 2:28:45 PM'
, '0x8DCAA9DA602D97F'
, 1668161
, 'image/avif'
, null
, null
, 'BlockBlob'
, 'Cool'
, '{"Description":"TEST2","FileName":"20190611_120706.jpg"}'
, null
, null
, '[{"Date":"2019-06-17"},{"Index":"2"},{"PAN":"987"},{"Phase":"Construction"},{"Primary":"False"},{"Project ID":"3484"}]'
, 6
, null);
INSERT INTO [BlobSync].[BlobInventory]
SELECT
AccountName, [Name], [CreationTime], [LastModified],
[Etag], [ContentLength], [ContentType], [ContentEncoding],
[ContentLanguage], [BlobType], [AccessTier], [Metadata],
[Deleted], [RemainingRetentionDays], [Tags], [TagCount],
[LastUpdatedUTC]
FROM
#TempTableInsert
WHERE
(#TempTableInsert.[AccountName] + '/' + #TempTableInsert.[Name] NOT IN (SELECT [BlobInventory].[AccountName] + '/' + [BlobInventory].[Name] FROM [BlobSync].[BlobInventory]));
I have used this script before without error and it has worked, for an unknown reason this SQL code will not work in my C# script or when I use it in SSMS (For the second record).
The TagCount
and Tags
fields are reversed between the column list and the column data. So you're putting the value 6
into Tags
and the long value [{"Date":"2019-0...
into TagCount
.
While I'm here, it is not correct to provide datetime values as SQL literals like this: 7/22/2024 2:28:44 PM
. Different languages and cultures have their own expectations around how date and times are formatted. The SQL language is no different just because it's a programming language.
The value should be formatted in one of these two ways:
2024-07-22T14:28:44 (ie: yyyy-MM-ddTHH:mm:ss) 20240722 14:28:44 (ie: yyyyMMdd HH:mm:ss)
If you have only the date (no time), the only correct option looks like this:
20240722 (ie: yyyyMMdd - no seperators!)
Anything else is wrong, and leaves you open to potential invalid input or reading the wrong date.