Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Invalid Cursor Position - No Cursor in Use... Expand / Collapse
Author
Message
Posted Wednesday, February 26, 2014 4:12 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 4, 2014 11:22 AM
Points: 12, Visits: 74
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.

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!
Post #1545634
Posted Wednesday, February 26, 2014 4:22 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:00 PM
Points: 3,783, Visits: 8,485
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1545638
Posted Wednesday, February 26, 2014 8:22 PM This worked for the OP Answer marked as solution


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:38 PM
Points: 35,371, Visits: 31,912
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1545674
Posted Thursday, February 27, 2014 7:29 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 4, 2014 11:22 AM
Points: 12, Visits: 74
Thanks so much for the input guys. I'll look at your recommendations today and report back.
Post #1545887
Posted Thursday, February 27, 2014 11:03 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 4, 2014 11:22 AM
Points: 12, Visits: 74
@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!
Post #1546021
Posted Thursday, February 27, 2014 11:04 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 4, 2014 11:22 AM
Points: 12, Visits: 74
@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! BigGrin
Post #1546023
Posted Thursday, February 27, 2014 4:01 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:38 PM
Points: 35,371, Visits: 31,912
Thanks for the feedback.

--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1546158
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse