SET XACT_ABORT ON; -- In order to enable the distributed transactions
BEGIN DISTRIBUTED TRANSACTION
-- do something
ROLLBACK/COMMIT TRANSACTION
SET XACT_ABORT OFF;
The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values
Error message:
The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values.
Problem:
As written... INSERT statement exceeds the maximum allowed number of 1000 row values.
Surprised, but INSERT statement using 'VALUES' (not insert with a select) - have limitation of 1000 inserted records.
Solution:
Anything that will use INSERTs with maximum 1000 records: separate to few INSERTs, insert the new data to temp table and insert using select from the temp table and so on.
exec query on other database
DECLARE @statement NVARCHAR(MAX) = N'select * from dbo.MyTable;
DECLARE @sql NVARCHAR(MAX) = QUOTENAME('OtherDbName') + '.sys.sp_executesql';
EXEC @sql @statement;
Merge with two options for MATCHED case
Remainder for MERGE syntax:
If there are 2 options what to do when we have a MATCHED between the source and he target tables - most of the times it will be deletion and update - we can do that like this:
MERGE <target_table> [AS TARGET]
.....
WHEN MATCHED AND <condition>
THEN DELETE
WHEN MATCHED -- all the other cases of MATCHED other then the condition above
THEN UPDATE
SET ColumnA = s.SomeValue
.....
FOR XML PATH(''), TYPE).value('.','NVARCHAR(max)')
An encoding issue with “FOR XML PATH”:
DECLARE @str NVARCHAR(MAX)= N'h&s_dor, Zoe&lee88AB'
select stuff(( select ',' + t.Alias from split ( @str, ',') t
FOR XML PATH(''))
,1,1,'') EncStr
Solution:
FOR XML PATH(''), TYPE).value('.','NVARCHAR(max)')
select stuff((( select ',' + t.Alias from split ( @str, ',') t
FOR XML PATH(''), TYPE).value('.','NVARCHAR(max)
,1,1,'') EncStr
Check constraint on a column value for each ID in the table
Our goal: to check in the DB level that for each ID (or other column) will be other column with a specific value not more than one time.
Wow - long statement… Let's do it with an example :)
CREATE TABLE [dbo].[SpecialCheckConst]
( SomeID int,
SomeStatusID int, -- we will check vaalues of this column
SomeOtherColumn nvarchar(50) -- or any other columns
)
We want to check that for each SomeID there is no more than one record of SomeStatusID 1.
INSERT INTO dbo.SpecialCheckConst
( SomeID, SomeStatusID, SomeOtherColumn)
VALUES
( 2, 3, 'aaa'), (5, 1, 'sss')
When we will try to insert new record with SomeStatusID 1 for SomeID 2 - we should success.
When we will try to insert new record with SomeStatusID 1 for SomeID 5 - we should fail (because there is a record for this SomeID with status 1).
When we will try to insert new record with new SomeID - of course that SomeStatusID 1 is OK.
Solution:
CHECK CONSTRAINT that calls a function that count the number of SomeStatusID 1 for the SomeID:
CREATE FUNCTION [dbo].[fn_SpecialCheckConst_
(
@SomeID int
)
RETURNS INT
AS
BEGIN
DECLARE @SomeStatusIDOne int
SELECT @SomeStatusIDOne = COUNT(*)
FROM dbo.SpecialCheckConst
WHERE SomeID = @SomeID
AND SomeStatusID = 1
RETURN @SomeStatusIDOne
END
GO
------------------------------
ALTER TABLE [dbo].[SpecialCheckConst] WITH CHECK ADD CONSTRAINT [CHK_dbo_SpecialCheckConst_
CHECK (NOT ([dbo].[fn_SpecialCheckConst_
GO
ALTER TABLE [dbo].[SpecialCheckConst] CHECK CONSTRAINT [CHK_dbo_SpecialCheckConst_
GO
------------------------------
And ….. It’s work!
You can drop the example temp table:
--DROP TABLE dbo.SpecialCheckConst
--go
update Unicode strings in database
In SQL, in order to update Unicode values, you should add ‘N’ before the string
update [dbo].[Player]
set address1=N'aąbcćdeęfghijklłmnńoóprsśtuwyzźż',
address2=N'AĄBCĆDEĘFGHIJKLŁMNŃOÓPRSŚTUWYZŹŻ'
where alias=N'Auto77086780' or Address1=N'tägelîch'
Here an example how an update statement without this ‘N’ will update wrong value (first update),
and how the same update statement but with ‘N’ will update the correct value (second update).
(BTW - the text is in Polish.)
Subscribe to:
Posts (Atom)