Invalid Cursor Position - No Cursor in Use...

Question

Invalid Cursor Position - No Cursor in Use...

pixeldude

SSC Veteran

Points: 268
More actions
February 26, 2014 at 4:12 pm

#290360

Hi everybody-
Long time lurker, first time poster. Please let me know if I'm not in the right place to be posting this.
I created a trigger on a table called 'Customer'. Here are the details of my trigger and the table that my trigger is updating/inserting records into:
CREATE TABLE [dbo].[METACOLUMN_CONFIGURATION](
[CUSTID] [numeric](18, 0) NOT NULL,
[METACOLUMNID] [numeric](18, 0) NOT NULL,
[LABEL] [varchar](35) NOT NULL,
CONSTRAINT [PK_METACOLUMN_CONFIGURATION] PRIMARY KEY CLUSTERED
(
[CUSTID] ASC,
[METACOLUMNID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
ALTER TRIGGER [dbo].[TR_NAME_UDF_LABELS] ON [dbo].[CUSTOMER]
AFTER
INSERT,
UPDATE
AS
IF Trigger_NestLevel() > 1
RETURN
-- //////////////////////////////////////////
-- ** IF the update is for 'Household'...
--///////////////////////////////////////////
IF
(SELECT BillingType FROM Inserted) IN ('HOUSEHOLD', 'HOUSEHOLD PERMANENT', 'HOUSEHOLD SIT')
BEGIN
-- Insert default values into Config so they can be updated if they don't already exist
IF NOT EXISTS (
SELECT
I.CustID
FROM
MetaColumn_Configuration MC
JOIN inserted i ON MC.CustID = I.CustID
)
BEGIN
SELECT
I.CustID AS CustID,
146 AS MetaColumnID,
'Customer Name' AS Label
INTO MetaColumn_Configuration
FROM
inserted I
SELECT
I.CustID AS CustID,
150 AS MetaColumnID,
'MOVE ID#' AS Label
INTO MetaColumn_Configuration
FROM
inserted I
SELECT
I.CustID AS CustID,
151 AS MetaColumnID,
'LOT#' AS Label
INTO MetaColumn_Configuration
FROM
inserted I
SELECT
I.CustID AS CustID,
152 AS MetaColumnID,
'DATE IN' AS Label
INTO MetaColumn_Configuration
FROM
inserted I
SELECT
I.CustID AS CustID,
153 AS MetaColumnID,
'STORAGE TYPE' AS Label
INTO MetaColumn_Configuration
FROM
inserted I
SELECT
I.CustID AS CustID,
154 AS MetaColumnID,
'EARLY OUT' AS Label
INTO MetaColumn_Configuration
FROM
inserted I
SELECT
I.CustID AS CustID,
309 AS MetaColumnID,
'AUTO' AS Label
INTO MetaColumn_Configuration
FROM
inserted I
SELECT
I.CustID AS CustID,
310 AS MetaColumnID,
'DEL BY INTO STGE' AS Label
INTO MetaColumn_Configuration
FROM
inserted I
SELECT
I.CustID AS CustID,
311 AS MetaColumnID,
'EQUIPMENT #' AS Label
INTO MetaColumn_Configuration
FROM
inserted I
SELECT
I.CustID AS CustID,
312 AS MetaColumnID,
'UDF10' AS Label
INTO MetaColumn_Configuration
FROM
inserted I
SELECT
I.CustID AS CustID,
313 AS MetaColumnID,
'UDF11' AS Label
INTO MetaColumn_Configuration
FROM
inserted I
SELECT
I.CustID AS CustID,
314 AS MetaColumnID,
'UDF12' AS Label
INTO MetaColumn_Configuration
FROM
inserted I
UPDATE C
SET
UDF1PROMPT = 1,
UDF2PROMPT = 1,
UDF3PROMPT = 1,
UDF4PROMPT = 1,
UDF5PROMPT = 1,
UDF6PROMPT = 1,
UDF7PROMPT = 1,
UDF8PROMPT = 1,
UDF9PROMPT = 1
FROM
Customer C
JOIN inserted I ON C.CustID = I.CustID
END
When I hit the trigger within the application, I'm getting the error message of [Microsoft][ODBC SQL Server Driver] Invalid Cursor Position. However, as you can see I'm not using a cursor. :blink:
I have found when I use the below code, which changes the syntax of how I'm inserting records, the trigger runs just fine. I'm sure you're wondering: why not just use that code? Well, it breaks when you try to update mass records, which is breaking another part of our application.
CREATE TRIGGER [dbo].[TR_NAME_UDF_LABELS] ON [dbo].[Customer]
AFTER
INSERT,
UPDATE
AS
-- //////////////////////////////////////////
-- ** IF the update is for Household...
--///////////////////////////////////////////
DECLARE @CustID NUMERIC(18,0)
SET @CustID = (SELECT CustID FROM inserted)
IF
(SELECT BillingType FROM Inserted) IN ('HOUSEHOLD', 'HOUSEHOLD PERMANENT', 'HOUSEHOLD SIT')
BEGIN
-- Insert default values into Config for update if they don't already exist
IF NOT EXISTS
(SELECT CustID FROM MetaColumn_Configuration WHERE CustID = @CustID)
BEGIN
INSERT INTO MetaColumn_Configuration VALUES
(
@CustID,
146,
'CUSTOMER NAME'
)
INSERT INTO MetaColumn_Configuration VALUES
(
@CustID,
150,
'MOVE ID#'
)
INSERT INTO MetaColumn_Configuration VALUES
(
@CustID,
151,
'LOT#'
)
INSERT INTO MetaColumn_Configuration VALUES
(
@CustID,
152,
'DATE IN'
)
INSERT INTO MetaColumn_Configuration VALUES
(
@CustID,
153,
'STORAGE TYPE'
)
INSERT INTO MetaColumn_Configuration VALUES
(
@CustID,
154,
'EARLY OUT'
)
INSERT INTO MetaColumn_Configuration VALUES
(
@CustID,
309,
'AUTO'
)
INSERT INTO MetaColumn_Configuration VALUES
(
@CustID,
310,
'DEL BY INTO STGE'
)
INSERT INTO MetaColumn_Configuration VALUES
(
@CustID,
311,
'EQUIPMENT #'
)
INSERT INTO MetaColumn_Configuration VALUES
(
@CustID,
312,
'UDF10'
)
INSERT INTO MetaColumn_Configuration VALUES
(
@CustID,
313,
'UDF11'
)
INSERT INTO MetaColumn_Configuration VALUES
(
@CustID,
314,
'UDF12'
)
UPDATE Customer
SET UDF1PROMPT = 1,
UDF2PROMPT = 1,
UDF3PROMPT = 1,
UDF4PROMPT = 1,
UDF5PROMPT = 1,
UDF6PROMPT = 1,
UDF7PROMPT = 1,
UDF8PROMPT = 1,
UDF9PROMPT = 1
WHERE
CustID = @CustID
END
Anyone out there ever had anything like this happen? Any advice, guidance or just general thoughts are appreciated. I'm going to be intermittently available until the morning, but I'll try to jump back periodically in case anyone asks for additional information.
Thanks so much for your help!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

I'm not sure if it will help but it makes me wonder. Why are you using SELECT...INTO instead of INSERT INTO? That might be part of the problem.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 2

S-I-M-P-L-I-F-Y 😉

I don't guarantee this will work because I don't have your tables to test it on but it should be pretty close. The key here is (as it says in my signature line below) to stop thinking in rows and start thinking in columns.

ALTER TRIGGER [dbo].[TR_NAME_UDF_LABELS] ON [dbo].[CUSTOMER]

AFTER INSERT, UPDATE

AS

--===== Prevent trigger feedback loop

IF TRIGGER_NESTLEVEL() > 1 RETURN

--===== Add the configuration rows for each CustID that

-- doesn't already have them.

INSERT INTO dbo.MetaColumn_Configuration

(CustID,MetaColumnID,Label)

SELECT i.CustID, cj.MetaColumnID, cj.Label

FROM INSERTED i

CROSS JOIN

(

SELECT 146,'Customer Name' UNION ALL

SELECT 150,'MOVE ID#' UNION ALL

SELECT 151,'LOT#' UNION ALL

SELECT 152,'DATE IN' UNION ALL

SELECT 153,'STORAGE TYPE' UNION ALL

SELECT 154,'EARLY OUT' UNION ALL

SELECT 309,'AUTO' UNION ALL

SELECT 310,'DEL BY INTO STGE' UNION ALL

SELECT 311,'EQUIPMENT #' UNION ALL

SELECT 312,'UDF10' UNION ALL

SELECT 313,'UDF11' UNION ALL

SELECT 314,'UDF12'

) cj (MetaColumnID,Lable)

WHERE NOT EXISTS (SELECT CustID FROM dbo.MetaColumn_Configuration mc WHERE mc.CustID = i.CustID)

AND i.BillingType IN ('HOUSEHOLD', 'HOUSEHOLD PERMANENT', 'HOUSEHOLD SIT')

;

--===== Update the prompts in the customer table to enable prompting after we've configured the customer.

-- These shouldn't actually be in the trigger. They should be defaults in the table.

UPDATE c

SET UDF1PROMPT = 1,

UDF2PROMPT = 1,

UDF3PROMPT = 1,

UDF4PROMPT = 1,

UDF5PROMPT = 1,

UDF6PROMPT = 1,

UDF7PROMPT = 1,

UDF8PROMPT = 1,

UDF9PROMPT = 1

FROM dbo.Customer c

JOIN INSERTED i

ON c.CustID = i.CustID

;

The update for the UDFxPROMPT columns shouldn't be in the trigger. They should be defaults in the table.

Also, get into the habit of almost always (and there are incredibly few and very rare reasons to do otherwise) using the 2 part naming convention and setup whatever you're using to write code with to convert tabs to spaces.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pixeldude SSC Veteran Points: 268 More actions · Answer 3

Thanks so much for the input guys. I'll look at your recommendations today and report back. 😀

pixeldude SSC Veteran Points: 268 More actions · Answer 4

@jeff Moden:

You got me on the right track. I made a newbie mistake and didn't post all of the code behind this trigger; as I only posted the parts that I thought were pertinent in an effort to keep it simple. Although your solution wasn't actually "the fix" to my problem, your code taught me something new (a really cool use of cross joins and overall simplification) and led me down the path to find where my code was throwing an error. Not to mention your approach is much cleaner and more efficient.

Thanks again for the help! 😀

pixeldude SSC Veteran Points: 268 More actions · Answer 5