0

I have the following code:

DECLARE @CTRBOOKCODES AS TABLE_TEMP_CODE_NVARCHAR_100
INSERT INTO @CTRBOOKCODES
SELECT CODE FROM (VALUES(
--'FUTURE'     ),(
'ZZZZZ'        ),(
--'NONE'        ),(
'ZZZZ'         ),(
'ZZZ'          ),(
'ZZZ'          ),(
'ZZZ'          ),(
'ZZZ'          ),(
'ZZZZ'         ),(
'ZZZZ'         ),(
'ZZZZ'         ),(
'ZZZZ'         ),(
'ZZZZZ'        ),(
'ZZZZ'         ),(
'ZZZZ'         ),(
'ZZZZ'         ),(
'ZZZ'          ),(
'ZZZ'          ),(
'ZZZZZ'        ),(
'ZZZ'          ),(
'ZZZ'          ),(
'ZZZ ZZZZ'    ),(
'ZZZ ZZZZ'     )
) as TBL(CODE)

Then I get the following error:

Msg 1205, Level 13, State 55, Server SRVDEV\SQLEXPRESS2012, Line 65535
Transaction (Process ID 51) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

I am not sure what I'm doing wrong?

Why is mssql complaining about deadlocks, when there is no other process running that accesses the declared variable!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3750768
  • 41
  • 6
  • Why are you using `INSERT [...] SELECT [...] FROM (VALUES([...]))`? What's wrong with a bog standard `INSERT [...] VALUES ([...])`? Also, what exactly is `TABLE_TEMP_CODE_NVARCHAR_100`? A custom data type? What's the definition for it? – Bacon Bits Mar 26 '15 at 11:05
  • Basically I want to pass an argument of a list of nvarchar(100) to a stored procedure, so i'm using that table type which is basically CREATE TYPE TABLE_TEMP_CODE_NVARCHAR_100 AS TABLE(CODE NVARCHAR(100)) – user3750768 Mar 26 '15 at 12:03
  • [I think you're creating a table, not a temporary table.](http://stackoverflow.com/questions/21127745/how-do-i-create-a-temporary-table-from-a-type) – Bacon Bits Mar 26 '15 at 12:08
  • @user3750768, I don't see the line 65535 referenced in the error message, which suggests the actual script is much larger. Furthermore, the value 65535 looks suspect since it happens be exactly 64K. Are you actually inserting over 64K rows in a singe T-SQL batch? If this is from an application program, consider passing a TVP. – Dan Guzman Mar 26 '15 at 12:28
  • @DanGuzman my sp involves 21 rows, i'm trying to shorten the query to it's bare minimum, i'll post it when i'm done, btw i'm already using TVP, CMIIW, the type TABLE_TEMP_CODE_NVARCHAR_100 is defined as a TVP as per my previous comment – user3750768 Mar 26 '15 at 13:18
  • @BaconBits i don't think it effects? – user3750768 Mar 26 '15 at 13:18
  • @user3750768, I think a minimal stored procedure and the app code that passes the TVP will help. I suspect there is more going on than is evident here. – Dan Guzman Mar 27 '15 at 11:44

0 Answers0