Why I am getting this cursor error? I don't think there should be an error.
-
Here is my sp, the error comes out at the first FETCH NEXT FROM c INTO
Error:
[highlight=#ffff11]Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.[/highlight]
ALTER Proc [dbo].[spPopulateAORFromEPM]
AS
DECLARE @Code varchar(50)
DECLARE @AppCode varchar(50)
DECLARE @AppName varchar(255)
DECLARE @Description varchar(3000)
DECLARE @TechOwner_LastName varchar(50)
DECLARE @TechOwner_FirstName varchar(50)
DECLARE @TechOwnerTransit varchar(5)
DECLARE @TechOwner_Department varchar(100)
DECLARE @Tech_Vendor varchar(50)
DECLARE @ClientOwner_LastName varchar(20)
DECLARE @ClientOwner_FirstName varchar(20)
DECLARE @ClientOwner_Transit varchar(5)
DECLARE @ClientOwner_Department varchar(50)
DECLARE @ClientSponsor varchar(50)
DECLARE @APM_ClientCode varchar(5)
DECLARE @APM_UsageArea varchar(20)
DECLARE @APM_ValueName varchar(50)
DECLARE @APM_DispositionName varchar(20)
DECLARE @BITA_GroupName varchar(100)
DECLARE @Sox bit
DECLARE @Integrity int
DECLARE @Confidentiality int
DECLARE @Availability int
DECLARE @Continuity int
DECLARE @DBR int
DECLARE @Overall_RskRating varchar(20)
;with cte as (select distinct appcode from TOCGBITASQL1PW.epm.dbo.applicationprofileview Where AppCode is not NULL)
insert into infosysnew (Code, SysID)
select AppCode, row_number() over (order by AppCode) as SysID from cte;
--select * from infosysnew
--truncate table infosysnew
DECLARE c CURSOR FOR
SELECT Distinct
AppCode,
AppName,
[Description],
TechOwner_LastName,
TechOwner_FirstName,
TechOwner_Transit,
TechOwner_Department
Tech_Vendor,
ClientOwner_LastName,
ClientOwner_FirstName,
ClientOwner_Transit,
ClientOwner_Department,
ClientSponsor,
APM_ClientCode,
APM_UsageArea,
APM_ValueName,
APM_DispositionName,
BITA_GroupName,
Overall_RskRating
FROM TOCGBITASQL1PW.epm.dbo.applicationprofileview
Where AppCode is not NULL
OPEN c
FETCH NEXT FROM c INTO
@AppCode,
@AppName,
@Description,
@TechOwner_LastName,
@TechOwner_FirstName,
@TechOwnerTransit,
@TechOwner_Department,
@Tech_Vendor,
@ClientOwner_LastName,
@ClientOwner_FirstName,
@ClientOwner_Transit,
@ClientOwner_Department,
@ClientSponsor,
@APM_ClientCode,
@APM_UsageArea,
@APM_ValueName,
@APM_DispositionName,
@BITA_GroupName,
@Overall_RskRating
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
UPDATE infosysnew
SET
[Name] = @AppName,
[Description] = @Description,
TechOwner_LastName = @TechOwner_LastName,
TechOwner_FirstName = @TechOwner_FirstName,
TechOwner_Transit = @TechOwnerTransit,
TechOwner_Department = @TechOwner_Department,
Tech_Vendor = @Tech_Vendor,
ClientOwner_LastName = @ClientOwner_LastName,
ClientOwner_FirstName = @ClientOwner_FirstName,
ClientOwner_Transit = @ClientOwner_Transit,
ClientOwner_Department = @ClientOwner_Department,
ClientSponsor = @ClientSponsor,
APM_ClientCode = @APM_ClientCode,
APM_UsageArea = @APM_UsageArea,
APM_ValueName = @APM_ValueName,
APM_DispositionName = @APM_DispositionName,
BITA_GroupName = @BITA_GroupName,
Overall_RskRating = @Overall_RskRating
WHERE Code = @AppCode
IF EXISTS(select * from infosys where code = @Code)
Begin
SELECT @Sox = Sox,
@Integrity = Integrity,
@Confidentiality = Confidentiality,
@Availability = Availability,
@Continuity = Continuity,
@DBR = DBR
From infosys Where Code = @Code
update infosysnew
set sox = @Sox, Integrity = @Integrity, Confidentiality = @Confidentiality, Availability = @Availability, Continuity = @Continuity, DBR= @DBR
where code = @code
End
End Try
BEGIN CATCH
--Print @AppCode
END CATCH
FETCH NEXT FROM c INTO
@AppCode,
@AppName,
@Description,
@TechOwner_LastName,
@TechOwner_FirstName,
@TechOwnerTransit,
@TechOwner_Department,
@Tech_Vendor,
@ClientOwner_LastName,
@ClientOwner_FirstName,
@ClientOwner_Transit,
@ClientOwner_Department,
@ClientSponsor,
@APM_ClientCode,
@APM_UsageArea,
@APM_ValueName,
@APM_DispositionName,
@BITA_GroupName,
@Overall_RskRating
END
CLOSE c
DEALLOCATE c
-
Could you post the DDL for the tables? Looking at it I can't see a problem.
-
I am blind now. I converted the cursor into table, and still getting similar error saying columns number does not match.:hehe:
-
Lynn Pettis (3/14/2012)
Could you post the DDL for the tables? Looking at it I can't see a problem.CREATE TABLE [dbo].[infosysnew](
[ID] [int] IDENTITY(1,1) NOT NULL,
[SysID] [int] NOT NULL CONSTRAINT [DF_infosysnew_SysID] DEFAULT ((0)),
[OrgUnitID] [varchar](20) NOT NULL CONSTRAINT [DF_infosysnew_OrgUnitID] DEFAULT (''),
[Code] [varchar](50) NOT NULL CONSTRAINT [DF_infosysnew_Code] DEFAULT (''),
[Name] [varchar](255) NOT NULL CONSTRAINT [DF_infosysnew_Name] DEFAULT (''),
[Description] [varchar](3000) NOT NULL CONSTRAINT [DF_infosysnew_Description] DEFAULT (''),
[SID] [int] NOT NULL CONSTRAINT [DF_infosysnew_SID] DEFAULT ((0)),
[SIDNew] [int] NOT NULL CONSTRAINT [DF_infosysnew_SIDNew] DEFAULT ((0)),
[SOrgUnitID] [varchar](20) NOT NULL CONSTRAINT [DF_infosysnew_SOrgUnitID] DEFAULT (''),
[TechOwner_LastName] [varchar](50) NOT NULL CONSTRAINT [DF_infosysnew_TechOwner_LastName] DEFAULT (''),
[TechOwner_FirstName] [varchar](50) NOT NULL CONSTRAINT [DF_infosysnew_TechOwner_FirstName] DEFAULT (''),
[TechOwner_Transit] [varchar](5) NOT NULL CONSTRAINT [DF_infosysnew_TechOwner_Transit] DEFAULT (''),
[TechOwner_Department] [varchar](100) NOT NULL CONSTRAINT [DF_infosysnew_TechOwner_Department] DEFAULT (''),
[Tech_Vendor] [varchar](50) NOT NULL CONSTRAINT [DF_infosysnew_Tech_Vendor] DEFAULT (''),
[ClientOwner_LastName] [varchar](20) NOT NULL CONSTRAINT [DF_infosysnew_ClientOwner_LastName] DEFAULT (''),
[ClientOwner_FirstName] [varchar](20) NOT NULL CONSTRAINT [DF_infosysnew_ClientOwner_FirstName] DEFAULT (''),
[ClientOwner_Transit] [varchar](5) NOT NULL CONSTRAINT [DF_infosysnew_ClientOwner_Transit] DEFAULT (''),
[ClientOwner_Department] [varchar](50) NOT NULL CONSTRAINT [DF_infosysnew_ClientOwner_Department] DEFAULT (''),
[ClientSponsor] [varchar](50) NOT NULL CONSTRAINT [DF_infosysnew_ClientSponsor] DEFAULT (''),
[APM_ClientCode] [varchar](5) NOT NULL CONSTRAINT [DF_infosysnew_APM_ClientCode] DEFAULT (''),
[APM_UsageArea] [varchar](20) NOT NULL CONSTRAINT [DF_infosysnew_APM_UsageArea] DEFAULT (''),
[APM_ValueName] [varchar](50) NOT NULL CONSTRAINT [DF_infosysnew_APM_ValueName] DEFAULT (''),
[APM_DispositionName] [varchar](20) NOT NULL CONSTRAINT [DF_infosysnew_APM_DispositionName] DEFAULT (''),
[BITA_GroupName] [varchar](100) NOT NULL CONSTRAINT [DF_infosysnew_BITA_GroupName] DEFAULT (''),
[UID] [int] NOT NULL CONSTRAINT [DF_infosysnew_UID] DEFAULT ((0)),
[CreatedOn] [datetime] NOT NULL CONSTRAINT [DF_infosysnew_CreatedOn] DEFAULT (getdate()),
[CreatedBy] [varchar](50) NOT NULL CONSTRAINT [DF_infosysnew_CreatedBy] DEFAULT (suser_sname()),
[LastUpdatedOn] [datetime] NOT NULL CONSTRAINT [DF_infosysnew_LastUpdatedOn] DEFAULT (getdate()),
[LastUpdatedBy] [varchar](50) NOT NULL CONSTRAINT [DF_infosysnew_LastUpdatedBy] DEFAULT (suser_sname()),
[Status] [varchar](50) NOT NULL CONSTRAINT [DF_infosysnew_Status] DEFAULT ('Active'),
[Sox] [bit] NOT NULL CONSTRAINT [DF_infosysnew_Sox] DEFAULT ((0)),
[Integrity] [int] NOT NULL CONSTRAINT [DF_infosysnew_Integrity] DEFAULT ((0)),
[Confidentiality] [int] NOT NULL CONSTRAINT [DF_infosysnew_Confidentiality] DEFAULT ((0)),
[Availability] [int] NOT NULL CONSTRAINT [DF_infosysnew_Availability] DEFAULT ((0)),
[Continuity] [int] NOT NULL CONSTRAINT [DF_infosysnew_Continuity] DEFAULT ((0)),
[DBR] [int] NOT NULL CONSTRAINT [DF_infosysnew_DBR] DEFAULT ((0)),
[ApplicationValue] [varchar](20) NOT NULL CONSTRAINT [DF_infosysnew_ApplicationValue] DEFAULT (''),
[Overall_RskRating] [varchar](20) NOT NULL CONSTRAINT [DF_infosysnew_Overall_RskRating] DEFAULT ('')
) ON [PRIMARY]
-
You need a comma after TechOwner_Department
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
It throws error before it reaches the table, although the table has more columns than the cursor, I don't think that should be a problem.
-
Has to be both of us. I count 19 columns in the defintion of the cursor and 19 variables.
One question, why are you using a cursor?
-
halifaxdal (3/14/2012)
Lynn Pettis (3/14/2012)
Could you post the DDL for the tables? Looking at it I can't see a problem.CREATE TABLE [dbo].[infosysnew](
[ID] [int] IDENTITY(1,1) NOT NULL,
[SysID] [int] NOT NULL CONSTRAINT [DF_infosysnew_SysID] DEFAULT ((0)),
[OrgUnitID] [varchar](20) NOT NULL CONSTRAINT [DF_infosysnew_OrgUnitID] DEFAULT (''),
[Code] [varchar](50) NOT NULL CONSTRAINT [DF_infosysnew_Code] DEFAULT (''),
[Name] [varchar](255) NOT NULL CONSTRAINT [DF_infosysnew_Name] DEFAULT (''),
[Description] [varchar](3000) NOT NULL CONSTRAINT [DF_infosysnew_Description] DEFAULT (''),
[SID] [int] NOT NULL CONSTRAINT [DF_infosysnew_SID] DEFAULT ((0)),
[SIDNew] [int] NOT NULL CONSTRAINT [DF_infosysnew_SIDNew] DEFAULT ((0)),
[SOrgUnitID] [varchar](20) NOT NULL CONSTRAINT [DF_infosysnew_SOrgUnitID] DEFAULT (''),
[TechOwner_LastName] [varchar](50) NOT NULL CONSTRAINT [DF_infosysnew_TechOwner_LastName] DEFAULT (''),
[TechOwner_FirstName] [varchar](50) NOT NULL CONSTRAINT [DF_infosysnew_TechOwner_FirstName] DEFAULT (''),
[TechOwner_Transit] [varchar](5) NOT NULL CONSTRAINT [DF_infosysnew_TechOwner_Transit] DEFAULT (''),
[TechOwner_Department] [varchar](100) NOT NULL CONSTRAINT [DF_infosysnew_TechOwner_Department] DEFAULT (''),
[Tech_Vendor] [varchar](50) NOT NULL CONSTRAINT [DF_infosysnew_Tech_Vendor] DEFAULT (''),
[ClientOwner_LastName] [varchar](20) NOT NULL CONSTRAINT [DF_infosysnew_ClientOwner_LastName] DEFAULT (''),
[ClientOwner_FirstName] [varchar](20) NOT NULL CONSTRAINT [DF_infosysnew_ClientOwner_FirstName] DEFAULT (''),
[ClientOwner_Transit] [varchar](5) NOT NULL CONSTRAINT [DF_infosysnew_ClientOwner_Transit] DEFAULT (''),
[ClientOwner_Department] [varchar](50) NOT NULL CONSTRAINT [DF_infosysnew_ClientOwner_Department] DEFAULT (''),
[ClientSponsor] [varchar](50) NOT NULL CONSTRAINT [DF_infosysnew_ClientSponsor] DEFAULT (''),
[APM_ClientCode] [varchar](5) NOT NULL CONSTRAINT [DF_infosysnew_APM_ClientCode] DEFAULT (''),
[APM_UsageArea] [varchar](20) NOT NULL CONSTRAINT [DF_infosysnew_APM_UsageArea] DEFAULT (''),
[APM_ValueName] [varchar](50) NOT NULL CONSTRAINT [DF_infosysnew_APM_ValueName] DEFAULT (''),
[APM_DispositionName] [varchar](20) NOT NULL CONSTRAINT [DF_infosysnew_APM_DispositionName] DEFAULT (''),
[BITA_GroupName] [varchar](100) NOT NULL CONSTRAINT [DF_infosysnew_BITA_GroupName] DEFAULT (''),
[UID] [int] NOT NULL CONSTRAINT [DF_infosysnew_UID] DEFAULT ((0)),
[CreatedOn] [datetime] NOT NULL CONSTRAINT [DF_infosysnew_CreatedOn] DEFAULT (getdate()),
[CreatedBy] [varchar](50) NOT NULL CONSTRAINT [DF_infosysnew_CreatedBy] DEFAULT (suser_sname()),
[LastUpdatedOn] [datetime] NOT NULL CONSTRAINT [DF_infosysnew_LastUpdatedOn] DEFAULT (getdate()),
[LastUpdatedBy] [varchar](50) NOT NULL CONSTRAINT [DF_infosysnew_LastUpdatedBy] DEFAULT (suser_sname()),
[Status] [varchar](50) NOT NULL CONSTRAINT [DF_infosysnew_Status] DEFAULT ('Active'),
[Sox] [bit] NOT NULL CONSTRAINT [DF_infosysnew_Sox] DEFAULT ((0)),
[Integrity] [int] NOT NULL CONSTRAINT [DF_infosysnew_Integrity] DEFAULT ((0)),
[Confidentiality] [int] NOT NULL CONSTRAINT [DF_infosysnew_Confidentiality] DEFAULT ((0)),
[Availability] [int] NOT NULL CONSTRAINT [DF_infosysnew_Availability] DEFAULT ((0)),
[Continuity] [int] NOT NULL CONSTRAINT [DF_infosysnew_Continuity] DEFAULT ((0)),
[DBR] [int] NOT NULL CONSTRAINT [DF_infosysnew_DBR] DEFAULT ((0)),
[ApplicationValue] [varchar](20) NOT NULL CONSTRAINT [DF_infosysnew_ApplicationValue] DEFAULT (''),
[Overall_RskRating] [varchar](20) NOT NULL CONSTRAINT [DF_infosysnew_Overall_RskRating] DEFAULT ('')
) ON [PRIMARY]
Thank you!!!! How silly I was!!! I even converted the cursor into a table and still didn't see the problem. :w00t::w00t::w00t:
I guess I need a break now.
-
Lynn Pettis (3/14/2012)
Has to be both of us. I count 19 columns in the defintion of the cursor and 19 variables.One question, why are you using a cursor?
I don't usually use cursor, this sp was created based on an existing sp, I didn't find a table when I created it.
Is there a way to quickly search some freetext in all sp/fn within the database? I often need to search for a sp but just couldn't find it, I have 300+ sp in my database.
Thanks.
-
Hey, I missed it too and I was looking for a missing comma. :blush:
-
the error is here: one column is missing a comma, so the field is used ans an alias instead:
TechOwner_Transit,
TechOwner_Department
Tech_Vendor,
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
halifaxdal (3/14/2012)
Lynn Pettis (3/14/2012)
Has to be both of us. I count 19 columns in the defintion of the cursor and 19 variables.One question, why are you using a cursor?
I don't usually use cursor, this sp was created based on an existing sp, I didn't find a table when I created it.
Is there a way to quickly search some freetext in all sp/fn within the database? I often need to search for a sp but just couldn't find it, I have 300+ sp in my database.
Thanks.
You can query the sys.objects table for most object names.
If you're looking for references in procedures or functions, you can script those objects using the scripting feature. right click the database > tasks > generate scripts. Choose the object types to script and do a simple find for the keywords you're looking for.
Good catch on the missing comma. I too was counting fields over and over and missed the comma.
-
Is there a way to quickly search some freetext in all sp/fn within the database? I often need to search for a sp but just couldn't find it, I have 300+ sp in my database.
Check out the excellent (and free) SQL Search product from Redgate.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Phil Parkin (3/14/2012)
Is there a way to quickly search some freetext in all sp/fn within the database? I often need to search for a sp but just couldn't find it, I have 300+ sp in my database.
Check out the excellent (and free) SQL Search product from Redgate.
Excellento! Thank you for sharing this here.
-
Is there a way to quickly search some freetext in all sp/fn within the database? I often need to search for a sp but just couldn't find it, I have 300+ sp in my database.Thanks.
Yes, the following query provides a quick way to find a list of objects that contain any specific reference. So just substitute a table, column, etc. for yoursearchstring.
SELECT object_name(id) Name, text
FROM syscomments
WHERE text LIKE '%yoursearchstring%'
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply