Cursor variable not declared issue

Ok, I missed the boat somewhere.
I prepared the query below to go though all the columns and tables in
the database and return the count of distinct values for each column and
label them with the table and column names
When I run the query, I receive the following error from the part
labeled #1:
Server: Msg 137, Level 15, State 2, Line 44
Must declare the variable '@.tbl_name'.
What I dont understand is why this error is occurring. I defined the
variable and populated it. I commented out part #1 and tried PRINT
@.tbl_name @.col_name which returned appropriate values.
I have a workaround of commenting out the part labeled #1 and instead
insert the following which produced a list of queries that I copied and
pasted into a new QA window and executed.
PRINT 'select count(distinct ' + @.col_name + ') ' + '"' + @.tbl_name +
'.' + @.col_name + '"' + ' from ' + @.tbl_name
I dont understand why I cannot substitute the variables in a query as I
wish to.
I am also considering building a command string and using exec
sp_executesql.
I welcome comments and suggestions on this matter.
-- -- --
DECLARE @.tbl_name varchar(255), @.col_name varchar(255)
DECLARE CURS_sys_tables_and_cols CURSOR FOR
select sysobjects.name, syscolumns.name from syscolumns, sysobjects
where sysobjects.id = syscolumns.id
and (sysobjects.xtype='U' or sysobjects.xtype='S')
and sysobjects.name NOT like 'SYS%'
and sysobjects.name NOT IN ( LIST OF TABLES I DONT WANT)
order by sysobjects.name
OPEN CURS_sys_tables_and_cols
FETCH NEXT FROM CURS_sys_tables_and_cols
INTO @.tbl_name, @.col_name
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- #1
PRINT @.tbl_name + '.' + @.col_name
Select count(distinct @.col_name) from @.tbl_name
PRINT '--'
FETCH NEXT FROM CURS_sys_tables_and_cols
INTO @.tbl_name, @.col_name
END
CLOSE CURS_sys_tables_and_cols
DEALLOCATE CURS_sys_tables_and_cols
*** Sent via Developersdex http://www.examnotes.net ***SJM,
I think you'll need to use Dynamic SQL to use a variable for the table name
in your query i.e., EXEC or sp_executesql.
Check it out in the SQL BOL and at Erland's article:
http://www.sommarskog.se/dynamic_sql.html
HTH
Jerry
"SJM" <nospam@.devdex.com> wrote in message
news:eIAwXiRxFHA.624@.TK2MSFTNGP11.phx.gbl...
> Ok, I missed the boat somewhere.
> I prepared the query below to go though all the columns and tables in
> the database and return the count of distinct values for each column and
> label them with the table and column names
> When I run the query, I receive the following error from the part
> labeled #1:
> Server: Msg 137, Level 15, State 2, Line 44
> Must declare the variable '@.tbl_name'.
> What I don't understand is why this error is occurring. I defined the
> variable and populated it. I commented out part #1 and tried PRINT
> @.tbl_name @.col_name which returned appropriate values.
> I have a workaround of commenting out the part labeled #1 and instead
> insert the following which produced a list of queries that I copied and
> pasted into a new QA window and executed.
> PRINT 'select count(distinct ' + @.col_name + ') ' + '"' + @.tbl_name +
> '.' + @.col_name + '"' + ' from ' + @.tbl_name
> I don't understand why I cannot substitute the variables in a query as I
> wish to.
> I am also considering building a command string and using exec
> sp_executesql.
> I welcome comments and suggestions on this matter.
> -- -- --
> DECLARE @.tbl_name varchar(255), @.col_name varchar(255)
> DECLARE CURS_sys_tables_and_cols CURSOR FOR
> select sysobjects.name, syscolumns.name from syscolumns, sysobjects
> where sysobjects.id = syscolumns.id
> and (sysobjects.xtype='U' or sysobjects.xtype='S')
> and sysobjects.name NOT like 'SYS%'
> and sysobjects.name NOT IN ( LIST OF TABLES I DON'T WANT)
> order by sysobjects.name
> OPEN CURS_sys_tables_and_cols
> FETCH NEXT FROM CURS_sys_tables_and_cols
> INTO @.tbl_name, @.col_name
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
>
> -- #1
> PRINT @.tbl_name + '.' + @.col_name
> Select count(distinct @.col_name) from @.tbl_name
> PRINT '--'
>
> FETCH NEXT FROM CURS_sys_tables_and_cols
> INTO @.tbl_name, @.col_name
> END
> CLOSE CURS_sys_tables_and_cols
> DEALLOCATE CURS_sys_tables_and_cols
>
> *** Sent via Developersdex http://www.examnotes.net ***|||
Indeed, I thought I might need to build strings and use sp_executesql.
Thanks for the pointer to the article, I missed it in my google
searches.
*** Sent via Developersdex http://www.examnotes.net ***

cursor usage

hi guys

i have a table that contains a tremendous amount of row. i have written a stored procedure that takes a summary of that information and updates it's master table as well as another table. the problem is it takes very long to do. is what i am doing correct or is there a better way. here is the source

CREATE PROCEDURE update_cvrbatches
AS

declare @.code varchar(25),@.type varchar(3),@.batchno varchar(10),@.batchqty Float,@.issued float,@.returned float,@.transfered float,@.itemcount float,@.totestcost float,@.totactcost float,@.reserved float,@.warehouse varchar(3)

Update cvrwarehouse set BoughtQty =0, IssuedQty = 0, ReservedQty =0 ,Returned = 0 ,Transfered = 0

DECLARE getbatches CURSOR
for
select Code,Type,BatchNo,sum(BoughtQty) as BoughtQty,sum(IssuedQty) as IssuedQty,sum(ReservedQty) as ReservedQty,sum(ReturnedQty) as ReturnedQty,sum(TransferQty) as TransferQty,count(Barcode) as ItemCount,
sum(EstCost) as EstCost,sum(ActCost) as ActCost,Warehouse
from cvrbatches
Group by Code,Type,Colour,Quality,CustomField,BatchNo,Warehouse
OPEN getbatches

FETCH NEXT FROM getbatches into @.code,@.type ,@.batchno ,@.batchqty,@.issued ,@.reserved,@.returned ,@.transfered ,@.itemcount ,@.totestcost ,@.totactcost ,@.warehouse
WHILE @.@.FETCH_STATUS = 0
BEGIN
--doen iets hier

update cvrbatchctrl set BatchQty = @.batchqty, Issued = @.issued, Reserved = @.reserved ,Returned = @.returned ,Transfered = @.transfered, ItemCount = @.itemcount,TotalEstCost = @.totestcost,TotalActCost = @.totactcost
where Code = @.code and Type = @.type and Warehouse = @.warehouse and BatchNo = @.batchno
update cvrwarehouse set BoughtQty =BoughtQty + @.batchqty, IssuedQty = IssuedQty + @.issued, ReservedQty = ReservedQty + @.reserved ,Returned = Returned + @.returned ,Transfered = Transfered + @.transfered
where Code = @.code and Type = @.type and Warehouse = @.warehouse

FETCH NEXT FROM getbatches into @.code,@.type ,@.batchno ,@.batchqty,@.issued ,@.reserved,@.returned ,@.transfered ,@.itemcount ,@.totestcost ,@.totactcost,@.warehouse

END
CLOSE getbatches
DEALLOCATE getbatches

is there a better way?

Hi,

better use setbased solutions rather than cursors, one example would be (*untested*) the below one: (put in a more human readble format)

update cvrbatchctrl
set BatchQty = SUbQuery.BoughtQty,
Issued = SUbQuery.IssuedQty,
Reserved = SUbQuery.ReservedQty,
Returned = SUbQuery.ReservedQty,
Transfered = SUbQuery.TransferQty,
ItemCount = SUbQuery.ItemCount,
TotalEstCost = SUbQuery.EstCost,
TotalActCost = SUbQuery.ActCost
FROM cvrbatchctrl cvr
INNER JOIN
(
select Code,
Type,
BatchNo,
Warehouse,
sum(BoughtQty) as BoughtQty,
sum(IssuedQty) as IssuedQty,
sum(ReservedQty) as ReservedQty,
sum(ReturnedQty) as ReturnedQty,
sum(TransferQty) as TransferQty,
count(Barcode) as ItemCount,
sum(EstCost) as EstCost,
sum(ActCost) as ActCost
from cvrbatches
Group by Code,Type,Colour,Quality,CustomField,BatchNo,Warehouse
) SUbQuery
ON
cvr.Code = SUbQuery.Code and
cvr.Type = SUbQuery.Type and
cvr.Warehouse = SUbQuery.Warehouse and
cvr.BatchNo = SUbQuery.batchno

--Second one, taking the value from the first update (make surethat your condition is complete in the below script)

update cvrwarehouse
set BoughtQty = cvrhouse.BoughtQty + @.batchqty,
IssuedQty = cvrhouse.IssuedQty + @.issued,
ReservedQty = cvrhouse.ReservedQty + @.reserved ,
Returned = cvrhouse.Returned + @.returned ,
Transfered = cvrhouse.Transfered + @.transfered
FROM cvrwarehouse cvrhouse
INNER JOIN cvrbatchctrl cvrbatch
ON
cvrhouse.Code = cvrbatchctrl.Code AND
cvrhouse.Type = cvrbatchctrl.Type
cvrhouse.Warehouse = cvrbatchctrl.Warehouse

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thank you.

haven't tried it yet but it sure makes for an interesting solution.

let you know as soon as i do.

again thanks

|||

i just tested it

works really well and the speed is a lot better

thanks again

Cursor Update problem

I am using a cursor to take information from a temp table and either insert
or update another table. I am using a while loop to fetch all the vaules
from the cursor and then I close and deallocate the cursor. Everything runs
fine the first time but when I run the procedure again it updates the first
record with the information from the last record process from the time
before. If it helps I am running it as a job just like it will run when
completed. Ideas on why I am getting data from the previous run?Can you poste the code instead just a brief description of the problem?
Please provide DDL and sample data.
http://www.aspfaq.com/etiquette.asp?id=5006
AMB
"Shannon Thompson" wrote:

> I am using a cursor to take information from a temp table and either inser
t
> or update another table. I am using a while loop to fetch all the vaules
> from the cursor and then I close and deallocate the cursor. Everything ru
ns
> fine the first time but when I run the procedure again it updates the firs
t
> record with the information from the last record process from the time
> before. If it helps I am running it as a job just like it will run when
> completed. Ideas on why I am getting data from the previous run?|||Here is my code but I cannot give you the code for stored procedures call
from this code here because they are Encrypted and part of the software
program I am integrating with.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[xxx]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[xxx]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE procedure xxxx
AS
/*
** Declare & initialize Local Variables
***
*/
DECLARE @.iReturnCode int,
@.iRet int,
@.vchFirstName nvarchar(255) ,
@.vchLastName nvarchar(255) ,
@.vchAdSource nvarchar(255) ,
@.vchOnyxCode nvarchar(255) ,
@.vchAddress1 nvarchar(255) ,
@.vchCity nvarchar(255) ,
@.chStateCode nvarchar(50) ,
@.vchPostCode nvarchar(40) ,
@.chCountryCode nvarchar(50) ,
@.vchPhoneType nvarchar(50) ,
@.vchPhoneNumber nvarchar(40) ,
@.vchBestTime nvarchar(255),
@.vchEmail nvarchar(255),
@.iHt_Feet nvarchar(50),
@.iHt_Inches nvarchar(50),
@.iWeight nvarchar(50),
@.dtDOB nvarchar(50),
@.vchInsurance nvarchar(255),
@.vchOtherIns nvarchar(255),
@.vchInsuranceType nvarchar(255),
@.vchSem nvarchar(255),
@.vchSemSrc nvarchar(255),
@.dtTimeStamp nvarchar(255),
@.iIndividualId int ,
@.iIncidentId int,
@.onyx_cursor cursor,
@.chInsUpd nchar(1),
@.iPhoneTypeId int,
@.getDate datetime,
@.dtPreviousUpdateDate datetime,
@.iHeight int,
@.dtUpdate datetime,
@.bmi float
set @.iReturnCode = 0
set @.iRet = 0
set @.getDate = getDate()
set @.bmi = 0
set @.onyx_cursor = cursor
--local Scroll Keyset Optimistic
FOR Select
vchFirstName,
vchLastName,
vchAdSource,
vchOnyxCode,
vchAddress1,
vchCity,
chStateCode,
vchPostCode,
chCountryCode,
vchPhoneType,
vchPhoneNumber,
vchBestTime,
vchEmail,
iHt_Feet,
iHt_Inches,
iWeight,
dtDOB,
vchInsurance,
vchOtherIns,
vchInsuranceType,
vchSem,
vchSemSrc,
dtTimeStamp
from CallCenter_Temp
OPEN @.onyx_cursor
FETCH NEXT from @.onyx_cursor into
@.vchFirstName,
@.vchLastName,
@.vchAdSource,
@.vchOnyxCode,
@.vchAddress1,
@.vchCity,
@.chStateCode,
@.vchPostCode,
@.chCountryCode,
@.vchPhoneType,
@.vchPhoneNumber,
@.vchBestTime,
@.vchEmail,
@.iHt_Feet,
@.iHt_Inches,
@.iWeight,
@.dtDOB,
@.vchInsurance,
@.vchOtherIns,
@.vchInsuranceType,
@.vchSem,
@.vchSemSrc,
@.dtTimeStamp
-- loop while there are still records in table
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.iHeight= convert(int,convert(float,ROUND(@.iHt_Inc
hes,0)) +
(convert(float,ROUND(@.iHt_Feet,0))*12))
IF @.iHeight <> 0 AND @.iWeight <> 0
BEGIN
declare @.meters float,
@.totalinches float,
@.kilos float,
@.metersq float
set @.totalinches = convert(float,@.iHeight)
set @.meters = @.totalinches/39.36
set @.kilos = convert(float,@.iWeight)/2.2
set @.metersq = @.meters * @.meters
set @.bmi = Round(@.kilos/@.metersq,0)
END
set @.vchFirstName = UPPER(@.vchFirstName)
set @.vchLastName = UPPER(@.vchLastName)
set @.vchAddress1 = UPPER(@.vchAddress1)
set @.vchCity = UPPER(@.vchCity)
set @.chStateCode = UPPER(@.chStateCode)
set @.chCountryCode = UPPER(@.chCountryCode)
-- determine phone type
SELECT @.iPhoneTypeId =
CASE LOWER(RTRIM(@.vchPhoneType))
WHEN 'home' THEN 119
WHEN 'cell' THEN 103
WHEN 'work' THEN 102
ELSE 119
END
-- if no Onyx Id is listed then search for the individual
IF @.iIndividualId is null
BEGIN
-- check to see if Person is in Onyx
exec @.iRet = wbocpscOnyxTalley
@.vchFirstName,
@.vchLastName,
@.vchAddress1,
@.vchCity,
@.chStateCode,
@.vchPostCode,
NULL
if (@.iRet <> 0)
begin
--update
set @.chInsUpd = 'U'
set @.iIndividualId = @.iRet
end
else
begin
--insert
set @.chInsUpd = 'I'
end
end
-- iIndividualId is given so this is an update
ELSE
BEGIN
set @.chInsUpd = 'U'
END
print @.chInsUpd + ' ' + @.vchLastName
if @.chInsUpd = 'I'
begin
exec @.iReturnCode = wbospsiIndividual
1,
@.iIndividualId,
'ENG',
'PatientLC',
null,
@.vchFirstName,
null,
@.vchLastName,
null,
@.vchAddress1,
null,
null,
@.vchCity,
@.chStateCode,
@.chCountryCode,
@.vchPostCode,
@.vchPhoneNumber,
@.vchEmail,
'',
null,
null,
null,
0,
'',
'',
'',
null,
null,
@.iPhoneTypeId,
119,
null,
null,
1,
1,
0,
null,
null,
@.iHeight,
@.iWeight,
@.bmi,
null,
null,
null,
@.dtDOB,
null,
'CCLeads',
@.getDate,
0,
1,
1
if @.iReturnCode <> 0
begin
print 'insert failed for ' + @.vchFirstName + ' ' + @.vchLastName
print @.iReturnCode
end
else
begin
print 'inserted ' + @.vchFirstName + ' ' + @.vchLastName
print @.iReturnCode
end
end
ELSE
begin
select @.dtPreviousUpdateDate = dtUpdateDate FROM Individual WHERE
iIndividualId = @.iIndividualId
exec @.iReturnCode = ospsgCheckRecordLock
@.dtUpdate OUTPUT,
@.dtPreviousUpdateDate,
1
-- if error returned then must change dtUpdateDate to current
if (@.iReturnCode <> 0)
begin
UPDATE Individual SET dtUpdateDate = @.getDate,chUpdateBy='sa' WHERE
iIndividualId = @.iIndividualId
exec @.iReturnCode = wbospsuIndividual
1,
@.iIndividualId,
'ENG',
'PatientLC',
null,
@.vchFirstName,
null,
@.vchLastName,
null,
@.vchAddress1,
null,
null,
@.vchCity,
@.chStateCode,
@.chCountryCode,
@.vchPostCode,
@.vchPhoneNumber,
@.vchEmail,
'',
null,
null,
null,
0,
'',
'',
'',
null,
null,
@.iPhoneTypeId,
null,
null,
null,
1,
1,
0,
null,
null,
null,
@.iHeight,
@.iWeight,
@.bmi,
null,
null,
@.dtDOB,
null,
'CCLeads',
@.getDate,
0,
1
if @.iReturnCode <> 0
begin
print 'update failed for ' + @.vchFirstName + ' ' + @.vchLastName
print @.iReturnCode
end
else
begin
print 'updated ' + @.vchFirstName + ' ' + @.vchLastName
print @.iReturnCode
end
end
end
--fetch next record
FETCH NEXT from @.onyx_cursor into
@.vchFirstName,
@.vchLastName,
@.vchAdSource,
@.vchOnyxCode,
@.vchAddress1,
@.vchCity,
@.chStateCode,
@.vchPostCode,
@.chCountryCode,
@.vchPhoneType,
@.vchPhoneNumber,
@.vchBestTime,
@.vchEmail,
@.iHt_Feet,
@.iHt_Inches,
@.iWeight,
@.dtDOB,
@.vchInsurance,
@.vchOtherIns,
@.vchInsuranceType,
@.vchSem,
@.vchSemSrc,
@.dtTimeStamp
END
CLOSE @.onyx_cursor
DEALLOCATE @.onyx_cursor
return @.iReturnCode
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
"Alejandro Mesa" wrote:
> Can you poste the code instead just a brief description of the problem?
> Please provide DDL and sample data.
> http://www.aspfaq.com/etiquette.asp?id=5006
>
> AMB
> "Shannon Thompson" wrote:
>|||Shannon,
The cursor is based on a permanent table. How are you feeding this table
before calling the sp?
AMB
"Shannon Thompson" wrote:

> Here is my code but I cannot give you the code for stored procedures call
> from this code here because they are Encrypted and part of the software
> program I am integrating with.
> if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[xxx]')
> and OBJECTPROPERTY(id, N'IsProcedure') = 1)
> drop procedure [dbo].[xxx]
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS OFF
> GO
>
> CREATE procedure xxxx
> AS
> /*
> ** Declare & initialize Local Variables
> ***
> */
>
> DECLARE @.iReturnCode int,
> @.iRet int,
> @.vchFirstName nvarchar(255) ,
> @.vchLastName nvarchar(255) ,
> @.vchAdSource nvarchar(255) ,
> @.vchOnyxCode nvarchar(255) ,
> @.vchAddress1 nvarchar(255) ,
> @.vchCity nvarchar(255) ,
> @.chStateCode nvarchar(50) ,
> @.vchPostCode nvarchar(40) ,
> @.chCountryCode nvarchar(50) ,
> @.vchPhoneType nvarchar(50) ,
> @.vchPhoneNumber nvarchar(40) ,
> @.vchBestTime nvarchar(255),
> @.vchEmail nvarchar(255),
> @.iHt_Feet nvarchar(50),
> @.iHt_Inches nvarchar(50),
> @.iWeight nvarchar(50),
> @.dtDOB nvarchar(50),
> @.vchInsurance nvarchar(255),
> @.vchOtherIns nvarchar(255),
> @.vchInsuranceType nvarchar(255),
> @.vchSem nvarchar(255),
> @.vchSemSrc nvarchar(255),
> @.dtTimeStamp nvarchar(255),
> @.iIndividualId int ,
> @.iIncidentId int,
> @.onyx_cursor cursor,
> @.chInsUpd nchar(1),
> @.iPhoneTypeId int,
> @.getDate datetime,
> @.dtPreviousUpdateDate datetime,
> @.iHeight int,
> @.dtUpdate datetime,
> @.bmi float
> set @.iReturnCode = 0
> set @.iRet = 0
> set @.getDate = getDate()
> set @.bmi = 0
> set @.onyx_cursor = cursor
> --local Scroll Keyset Optimistic
> FOR Select
> vchFirstName,
> vchLastName,
> vchAdSource,
> vchOnyxCode,
> vchAddress1,
> vchCity,
> chStateCode,
> vchPostCode,
> chCountryCode,
> vchPhoneType,
> vchPhoneNumber,
> vchBestTime,
> vchEmail,
> iHt_Feet,
> iHt_Inches,
> iWeight,
> dtDOB,
> vchInsurance,
> vchOtherIns,
> vchInsuranceType,
> vchSem,
> vchSemSrc,
> dtTimeStamp
> from CallCenter_Temp
> OPEN @.onyx_cursor
> FETCH NEXT from @.onyx_cursor into
> @.vchFirstName,
> @.vchLastName,
> @.vchAdSource,
> @.vchOnyxCode,
> @.vchAddress1,
> @.vchCity,
> @.chStateCode,
> @.vchPostCode,
> @.chCountryCode,
> @.vchPhoneType,
> @.vchPhoneNumber,
> @.vchBestTime,
> @.vchEmail,
> @.iHt_Feet,
> @.iHt_Inches,
> @.iWeight,
> @.dtDOB,
> @.vchInsurance,
> @.vchOtherIns,
> @.vchInsuranceType,
> @.vchSem,
> @.vchSemSrc,
> @.dtTimeStamp
> -- loop while there are still records in table
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> set @.iHeight= convert(int,convert(float,ROUND(@.iHt_Inc
hes,0)) +
> (convert(float,ROUND(@.iHt_Feet,0))*12))
> IF @.iHeight <> 0 AND @.iWeight <> 0
> BEGIN
> declare @.meters float,
> @.totalinches float,
> @.kilos float,
> @.metersq float
> set @.totalinches = convert(float,@.iHeight)
> set @.meters = @.totalinches/39.36
> set @.kilos = convert(float,@.iWeight)/2.2
> set @.metersq = @.meters * @.meters
> set @.bmi = Round(@.kilos/@.metersq,0)
> END
> set @.vchFirstName = UPPER(@.vchFirstName)
> set @.vchLastName = UPPER(@.vchLastName)
> set @.vchAddress1 = UPPER(@.vchAddress1)
> set @.vchCity = UPPER(@.vchCity)
> set @.chStateCode = UPPER(@.chStateCode)
> set @.chCountryCode = UPPER(@.chCountryCode)
> -- determine phone type
> SELECT @.iPhoneTypeId =
> CASE LOWER(RTRIM(@.vchPhoneType))
> WHEN 'home' THEN 119
> WHEN 'cell' THEN 103
> WHEN 'work' THEN 102
> ELSE 119
> END
> -- if no Onyx Id is listed then search for the individual
> IF @.iIndividualId is null
> BEGIN
> -- check to see if Person is in Onyx
> exec @.iRet = wbocpscOnyxTalley
> @.vchFirstName,
> @.vchLastName,
> @.vchAddress1,
> @.vchCity,
> @.chStateCode,
> @.vchPostCode,
> NULL
> if (@.iRet <> 0)
> begin
> --update
> set @.chInsUpd = 'U'
> set @.iIndividualId = @.iRet
> end
> else
> begin
> --insert
> set @.chInsUpd = 'I'
> end
> end
> -- iIndividualId is given so this is an update
> ELSE
> BEGIN
> set @.chInsUpd = 'U'
> END
> print @.chInsUpd + ' ' + @.vchLastName
> if @.chInsUpd = 'I'
> begin
> exec @.iReturnCode = wbospsiIndividual
> 1,
> @.iIndividualId,
> 'ENG',
> 'PatientLC',
> null,
> @.vchFirstName,
> null,
> @.vchLastName,
> null,
> @.vchAddress1,
> null,
> null,
> @.vchCity,
> @.chStateCode,
> @.chCountryCode,
> @.vchPostCode,
> @.vchPhoneNumber,
> @.vchEmail,
> '',
> null,
> null,
> null,
> 0,
> '',
> '',
> '',
> null,
> null,
> @.iPhoneTypeId,
> 119,
> null,
> null,
> 1,
> 1,
> 0,
> null,
> null,
> @.iHeight,
> @.iWeight,
> @.bmi,
> null,
> null,
> null,
> @.dtDOB,
> null,
> 'CCLeads',
> @.getDate,
> 0,
> 1,
> 1
> if @.iReturnCode <> 0
> begin
> print 'insert failed for ' + @.vchFirstName + ' ' + @.vchLastName
> print @.iReturnCode
> end
> else
> begin
> print 'inserted ' + @.vchFirstName + ' ' + @.vchLastName
> print @.iReturnCode
> end
> end
> ELSE
> begin
> select @.dtPreviousUpdateDate = dtUpdateDate FROM Individual WHERE
> iIndividualId = @.iIndividualId
> exec @.iReturnCode = ospsgCheckRecordLock
> @.dtUpdate OUTPUT,
> @.dtPreviousUpdateDate,
> 1
> -- if error returned then must change dtUpdateDate to current
> if (@.iReturnCode <> 0)
> begin
> UPDATE Individual SET dtUpdateDate = @.getDate,chUpdateBy='sa' WHERE
> iIndividualId = @.iIndividualId
> exec @.iReturnCode = wbospsuIndividual
> 1,
> @.iIndividualId,
> 'ENG',
> 'PatientLC',
> null,
> @.vchFirstName,
> null,
> @.vchLastName,
> null,
> @.vchAddress1,
> null,
> null,
> @.vchCity,
> @.chStateCode,
> @.chCountryCode,
> @.vchPostCode,
> @.vchPhoneNumber,
> @.vchEmail,
> '',
> null,
> null,
> null,
> 0,
> '',
> '',
> '',
> null,
> null,
> @.iPhoneTypeId,
> null,
> null,
> null,|||This table (CallCenter_temp) is populated by another stored procedure that
gets a file list from a directory, puts that into a true temp table (gets
created and deleted within procedure) and batch inserts each text file:
BEGIN
/*
** Declare & initialize Local Variables
*/
DECLARE
@.iReturnCode int,
@.MyFile varchar(200),
@.SQL varchar(2000),
@.Path varchar(400),
@.onyx_cursor cursor,
@.vchFileName varchar(255),
@.vchXPCMD nvarchar(255)
select
@.iReturnCode = 0
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.tables WHERE table_name =
'CCFiles')
BEGIN
DROP TABLE CCFiles
END
CREATE TABLE [dbo].[CCFiles] (
[vchFileName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
SET @.Path = '\\wms1\c$\CallCenterWebSite\data'
EXECUTE cpListFiles @.Path,'CCFiles','%.txt',NULL,0
IF @.iReturnCode = 0
BEGIN
SET @.onyx_cursor = cursor
FOR SELECT
vchFileName
FROM CCFiles
OPEN @.onyx_cursor
FETCH NEXT from @.onyx_cursor into @.vchFileName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.SQL = 'BULK INSERT [Onyx]..CallCenter_temp FROM "' + @.Path +
@.vchFileName + '"' +
' WITH(BATCHSIZE = 250 ,DATAFILETYPE = "char" ,FIELDTERMINATOR = "|"
,ROWTERMINATOR = "\n",MAXERRORS = 50 ,TABLOCK)'
--SELECT @.SQL
EXECUTE (@.SQL)
IF @.iReturnCode = 0
BEGIN
set @.vchFileName = @.Path + @.vchFileName
set @.vchXPCMD = '@.Del ' + RTrim(@.vchFileName)
--execute master..xp_cmdshell @.vchXPCMD
END
FETCH NEXT from @.onyx_cursor into @.vchFileName
END
END
DROP TABLE CCFiles
return @.iReturnCode
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
"Alejandro Mesa" wrote:
> Shannon,
> The cursor is based on a permanent table. How are you feeding this table
> before calling the sp?
>
> AMB
> "Shannon Thompson" wrote:
>|||> I cannot give you the code for stored procedures call
> from this code here because they are Encrypted and part of the software
> program I am integrating with.
Personally I'd want to decrypt those procs to see if it's feasible to
rewrite your code without a cursor.
http://www.planetsourcecode.com/vb/...6J00S003GU.html
David Portas
SQL Server MVP
--|||Shannon,
In the previous post you are not closing and deallocating the cursor and
this cursor.
AMB
"Shannon Thompson" wrote:
> This table (CallCenter_temp) is populated by another stored procedure that
> gets a file list from a directory, puts that into a true temp table (gets
> created and deleted within procedure) and batch inserts each text file:
> BEGIN
> /*
> ** Declare & initialize Local Variables
> */
> DECLARE
> @.iReturnCode int,
> @.MyFile varchar(200),
> @.SQL varchar(2000),
> @.Path varchar(400),
> @.onyx_cursor cursor,
> @.vchFileName varchar(255),
> @.vchXPCMD nvarchar(255)
> select
> @.iReturnCode = 0
> IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.tables WHERE table_name =
> 'CCFiles')
> BEGIN
> DROP TABLE CCFiles
> END
> CREATE TABLE [dbo].[CCFiles] (
> [vchFileName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> SET @.Path = '\\wms1\c$\CallCenterWebSite\data'
> EXECUTE cpListFiles @.Path,'CCFiles','%.txt',NULL,0
> IF @.iReturnCode = 0
> BEGIN
> SET @.onyx_cursor = cursor
> FOR SELECT
> vchFileName
> FROM CCFiles
> OPEN @.onyx_cursor
> FETCH NEXT from @.onyx_cursor into @.vchFileName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.SQL = 'BULK INSERT [Onyx]..CallCenter_temp FROM "' + @.Path +
> @.vchFileName + '"' +
> ' WITH(BATCHSIZE = 250 ,DATAFILETYPE = "char" ,FIELDTERMINATOR = "|"
> ,ROWTERMINATOR = "\n",MAXERRORS = 50 ,TABLOCK)'
> --SELECT @.SQL
> EXECUTE (@.SQL)
> IF @.iReturnCode = 0
> BEGIN
> set @.vchFileName = @.Path + @.vchFileName
> set @.vchXPCMD = '@.Del ' + RTrim(@.vchFileName)
> --execute master..xp_cmdshell @.vchXPCMD
> END
> FETCH NEXT from @.onyx_cursor into @.vchFileName
> END
> END
> DROP TABLE CCFiles
> return @.iReturnCode
> END
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
>
> "Alejandro Mesa" wrote:
>|||The reason I use a cursor is that having limited SQL Stored Procedure
experience (mainly VBScript SQL experience) I know of no other way to go
record by record to call these stored procedures. I have de-crypted these
procedures but by using these stored procedures instead of recreating them I
can safety insert data into the database without breaking any middle tier
rules of the software and corrupt any of the data.
The store procedures are made to put one person at a time into the database
(when a users clicks save on the web page) not bulk so this is why I am usin
g
the cursor.
"David Portas" wrote:

> Personally I'd want to decrypt those procs to see if it's feasible to
> rewrite your code without a cursor.
> http://www.planetsourcecode.com/vb/...6J00S003GU.html
> --
> David Portas
> SQL Server MVP
> --
>|||Why did you destroy Standard SQL behavior? Why are there more NULLs in
one table than should be in an entire Fortune 500 accounting package?
Do you really have a lot of data elements that are in Chinese and 255
characters long? Why do you have data type prefixes on variable names,
which is a violation of both good programming and ISO-11179? Why do you
have numeric data elements in strings? What kind of total garbage are
trying to get with things like "weight VARCHAR(50)", "@.phonetype
VARCHAR(50)", etc. And you don't seem to be aware of floating
point rounding errors (does your machine have a floating point
processor, or do you want to slow things down with a software floating
point package?)
You keep height in inches or cm then convert it for display. You do
not do this in the database. The syntax for CAST is CAST (<exp> AS
<datatype> ) -- do not use the proprietary CONVERT().
You are NOT writing SQL at all, but some kind of 3GL, using SQL for it.
But even worse, you did absolutely no design or research on the data.
Other products have a MERGE or UPSERT statement to do this. The usual
pattern in older products is:
BEGIN
-- insert the new rows
INSERT INTO Foobar
SELECT *
FROM WorkingData AS W
WHERE W.keycol
NOT IN (SELECT keycol FROM Foobar);
-- update the rows are already there
UPDATE Foobar
SET <column>
= (SELECT col FROM WorkingData AS W
WHERE W.keycol = Foobar.keycol)
WHERE keycol IN (SELECT keycol FROM WorkingData);
END;|||Since you have no idea about the situation or the database schema your
posting is just wasted useless space. Please do not bother my thread again
unless you want to know more about the database and can actually just give m
e
a reason to my error. I did not ask for a comments on the coding though
constructive criticism, not code bashing, is appreciated - again I stated I
am a Computer Programmer using SQL code in ASP pages mostly not much done
with SQL Server, I would have done this in VBScript (which I have already
done before) but store procedures are more efficient and reliable.
Oh and the comment about doing design and research on the data, you have no
idea how much design and research in this database I have done. With a
database that all the stored procedures are encrypted (which you cannot
decrypt unless you want to break the software agreement which I play by the
rules maybe you do not), no manuals or references on the database (because i
t
is part of a software program and while they want you to add functionalilty
to the product for your own uses they are not forth coming with how to do
things) and myself being no where near a DBA I think I have a pretty good
understanding of this database which you clearly do not because you did not
ask!
Thanks but no Thanks for your post...this is why I originally did not post
the code because people like you want to just code bash instead of helping!
"--CELKO--" wrote:

> Why did you destroy Standard SQL behavior? Why are there more NULLs in
> one table than should be in an entire Fortune 500 accounting package?
> Do you really have a lot of data elements that are in Chinese and 255
> characters long? Why do you have data type prefixes on variable names,
> which is a violation of both good programming and ISO-11179? Why do you
> have numeric data elements in strings? What kind of total garbage are
> trying to get with things like "weight VARCHAR(50)", "@.phonetype
> VARCHAR(50)", etc. And you don't seem to be aware of floating
> point rounding errors (does your machine have a floating point
> processor, or do you want to slow things down with a software floating
> point package?)
> You keep height in inches or cm then convert it for display. You do
> not do this in the database. The syntax for CAST is CAST (<exp> AS
> <datatype> ) -- do not use the proprietary CONVERT().
> You are NOT writing SQL at all, but some kind of 3GL, using SQL for it.
> But even worse, you did absolutely no design or research on the data.
> Other products have a MERGE or UPSERT statement to do this. The usual
> pattern in older products is:
> BEGIN
> -- insert the new rows
> INSERT INTO Foobar
> SELECT *
> FROM WorkingData AS W
> WHERE W.keycol
> NOT IN (SELECT keycol FROM Foobar);
> -- update the rows are already there
> UPDATE Foobar
> SET <column>
> = (SELECT col FROM WorkingData AS W
> WHERE W.keycol = Foobar.keycol)
> WHERE keycol IN (SELECT keycol FROM WorkingData);
> END;
>

Cursor update performance issue

I am using the following code which works fine except that when there are alot of rows being used (> 500) this performs really slow and I get a timeout error. Any ideas on how to make this faster since I need to update multiple rows, multiple times with multiple values?

<code>
DECLARE Item_Cursor CURSOR LOCAL FAST_FORWARD FOR
Select FileObjectId,CopyFileObjectId From FileObject
Where CopyFileObjectId IS NOT NULL
AND CreateTime = @.copyTime

set @.LastError = @.@.error
if(@.LastError <> 0) goto ERR_HANDLE

OPEN Item_Cursor
FETCH NEXT FROM Item_Cursor INTO @.NewId,@.OldId

WHILE @.@.FETCH_STATUS = 0
BEGIN
--update idhierarchies
Update FileObject Set IdHierarchy=Replace(IdHierarchy,'.'+Cast(@.OldId as varchar)+'.','.'+Cast(@.NewId as varchar)+'.')
Where CopyFileObjectId IS NOT NULL
AND CreateTime = @.copyTime

set @.LastError = @.@.error
if(@.LastError <> 0) goto ERR_HANDLE

--update parent ids
Update FileObject Set ParentId=@.NewId
Where ParentId=@.OldId
AND CopyFileObjectId IS NOT NULL
AND CreateTime = @.copyTime

set @.LastError = @.@.error
if(@.LastError <> 0) goto ERR_HANDLE

FETCH NEXT FROM Item_Cursor INTO @.NewId,@.OldId
END
CLOSE Item_Cursor
DEALLOCATE Item_Cursor

</code>I've read that when you declare a cursor the result of the select-statement actually gets written to the temp-database and that's whats causing you such delays. But from what I can read from your procedure here it should be possible to do those updates without the use of a cursor...?|||If you have any suggestions, let me know... I can't figure out how to do it in one or two update statements, that's definitely how I would prefer to do it and try to shoot for for every op. I just couldn't figur eout how to do that here.

Originally posted by Frettmaestro
I've read that when you declare a cursor the result of the select-statement actually gets written to the temp-database and that's whats causing you such delays. But from what I can read from your procedure here it should be possible to do those updates without the use of a cursor...?sql

cursor type error

this is an error which i happen to encounter..can you guys
help me out here..
cursor type should be :rdopenForwardonly
lock type should be :rdConcurReadonly
Rowsetsize should be : 1
how do i solve this error.i tried the isql/w script but
its not working either..any chance you guys know..
thanks
Please post this to the SQL Server Programming newsgroup for assistance
from other SQL developers.
Chris Skorlinski
Microsoft SQL Server Support
Please reply directly to the thread with any updates.
This posting is provided "as is" with no warranties and confers no rights.

Cursor type changed?

Hi,
I just posted this on sqlserver.connect since I'm not sure where it belongs.
So here goes.
We've recently began migrating to SQL 2005 from SQL 7 and have had a few
issues.
Right now we have an issue when trying to logon to the server through our
application.
'sa' login works from our application but when we try to logon as a user we
get:
"[ODBC SQL Server Driver]Cursor type changed"
I have tried logging on through Query Analyzer and that works fine for all
users so it should not be a permission issue.
I've tried to search the web high and low without really finding a solution
or cause for this error. I've seen a few a reports of people having the
same- or similar problems but no solutions.
I'd be greatful for any tips you guys and girls might have.
Thanks in advance and have a great wend.
Regards,
Tony HolopainenThis message is informational, not an error. Perhaps the application code
is treating the message as an error simply because it's unexpected.
Are you using ADO or calling ODBC directly? Do the get this message during
login or when you run a query? I wouldn't expect this to be security
related unless different results are returned depending on the user logging
in.
Hope this helps.
Dan Guzman
SQL Server MVP
"TonyH" <tony@.nospam.com> wrote in message
news:e8OWomAnGHA.4604@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I just posted this on sqlserver.connect since I'm not sure where it
> belongs. So here goes.
> We've recently began migrating to SQL 2005 from SQL 7 and have had a few
> issues.
> Right now we have an issue when trying to logon to the server through our
> application.
> 'sa' login works from our application but when we try to logon as a user
> we get:
> "[ODBC SQL Server Driver]Cursor type changed"
> I have tried logging on through Query Analyzer and that works fine for all
> users so it should not be a permission issue.
> I've tried to search the web high and low without really finding a
> solution or cause for this error. I've seen a few a reports of people
> having the same- or similar problems but no solutions.
> I'd be greatful for any tips you guys and girls might have.
> Thanks in advance and have a great wend.
> Regards,
> Tony Holopainen
>

cursor type

Hi there,
Can anyone tell me what's happening here?
I have the following code snippet that opens a recordset using some stored
procedure that returns some rows.
Dim rst as new ADODB.recordset
Dim Count as long
'rst.CursorType = adOpenStatic
rst.Open "Execute my_storedprocedure" & ItemID, _
m_con, adOpenStatic, adLockReadOnly
Count =rst.Recordcount
I check that rst.EOF = false. Yet rst.Recordcount returns -1. I also found
out that after opening the recordset, rst.CursorType = 0 (adOpenForwardOnly)
again. I tried setting rst.CursorType = adOpenStatic before specifically
before opening the recordset but it didn't help. It will still be reset to
adOpenForwardOnly after it's open. I think that's why RecordCount
returns -1.
Many thanks.
SusanPerhaps this will help:
http://www.sqlteam.com/item.asp?ItemID=11842
"Susan" <xxx> wrote in message news:u7JCuPbMGHA.140@.TK2MSFTNGP12.phx.gbl...
> Hi there,
> Can anyone tell me what's happening here?
> I have the following code snippet that opens a recordset using some stored
> procedure that returns some rows.
>
> Dim rst as new ADODB.recordset
> Dim Count as long
> 'rst.CursorType = adOpenStatic
> rst.Open "Execute my_storedprocedure" & ItemID, _
> m_con, adOpenStatic, adLockReadOnly
> Count =rst.Recordcount
> I check that rst.EOF = false. Yet rst.Recordcount returns -1. I also found
> out that after opening the recordset, rst.CursorType = 0
> (adOpenForwardOnly) again. I tried setting rst.CursorType = adOpenStatic
> before specifically before opening the recordset but it didn't help. It
> will still be reset to adOpenForwardOnly after it's open. I think that's
> why RecordCount returns -1.
> Many thanks.
> Susan
>|||I found out that this only happens when I use "EXEC my_storedprocedure" to
open a recordset. If I use embedded sql to open a recordset, e.g.
rst.Open "SELECT * FROM Products", m_con, adOpenStatic, adLockReadOnly
then it will returns the RecordCount fine.
But how do I work around that? I still like to use stored procedure though.
Thanks,
Susan
"Susan" <xxx> wrote in message news:u7JCuPbMGHA.140@.TK2MSFTNGP12.phx.gbl...
> Hi there,
> Can anyone tell me what's happening here?
> I have the following code snippet that opens a recordset using some stored
> procedure that returns some rows.
>
> Dim rst as new ADODB.recordset
> Dim Count as long
> 'rst.CursorType = adOpenStatic
> rst.Open "Execute my_storedprocedure" & ItemID, _
> m_con, adOpenStatic, adLockReadOnly
> Count =rst.Recordcount
> I check that rst.EOF = false. Yet rst.Recordcount returns -1. I also found
> out that after opening the recordset, rst.CursorType = 0
> (adOpenForwardOnly) again. I tried setting rst.CursorType = adOpenStatic
> before specifically before opening the recordset but it didn't help. It
> will still be reset to adOpenForwardOnly after it's open. I think that's
> why RecordCount returns -1.
> Many thanks.
> Susan
>|||Could you clarify that you are using SET NOCOUNT ON within you stored
procedure?
Do you have any PRINT statetments withing your stored procedure?
Jack Vamvas
________________________________________
__________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
New article by Jack Vamvas - Improper Use of indexes on MS SQL: Server
2000 - www.ciquery.com/articles/useofindexes.asp
"Susan" <xxx> wrote in message
news:uy0B%232bMGHA.2580@.TK2MSFTNGP14.phx.gbl...
> I found out that this only happens when I use "EXEC my_storedprocedure" to
> open a recordset. If I use embedded sql to open a recordset, e.g.
> rst.Open "SELECT * FROM Products", m_con, adOpenStatic, adLockReadOnly
> then it will returns the RecordCount fine.
> But how do I work around that? I still like to use stored procedure
though.
> Thanks,
> Susan
> "Susan" <xxx> wrote in message
news:u7JCuPbMGHA.140@.TK2MSFTNGP12.phx.gbl...
stored
found
adOpenStatic
>|||I suspected that too but no, I don't have SET NOCOUNT ON or Print statement.
Actually I found out sortly that if I set the connection's cursor location
to adUseClinet
m_con.CursorLocation = adUseClient
then it will return the RecordCount just fine. Does this mean that if I use
a stored procedure to open a recordset, then I need to specifically set
adUseClient to get a recordset other than a firehose forward only recordset?
But adUseServer is fine if I use embedded sql statement to open a recordset?
Susan
"Jack Vamvas" <DELETE_BEFORE_REPLY_jack@.ciquery.com> wrote in message
news:dsv9cr$p82$1@.nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com...
> Could you clarify that you are using SET NOCOUNT ON within you stored
> procedure?
> Do you have any PRINT statetments withing your stored procedure?
>
> --
> Jack Vamvas
> ________________________________________
__________________________
> Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
> New article by Jack Vamvas - Improper Use of indexes on MS SQL: Server
> 2000 - www.ciquery.com/articles/useofindexes.asp
> "Susan" <xxx> wrote in message
> news:uy0B%232bMGHA.2580@.TK2MSFTNGP14.phx.gbl...
> though.
> news:u7JCuPbMGHA.140@.TK2MSFTNGP12.phx.gbl...
> stored
> found
> adOpenStatic
>|||I thought that all ADO recordsets returned from a stored procedure are
client side.
"Susan" <xxx> wrote in message news:OXjHa8lMGHA.3708@.TK2MSFTNGP09.phx.gbl...
>I suspected that too but no, I don't have SET NOCOUNT ON or Print
>statement.
> Actually I found out sortly that if I set the connection's cursor location
> to adUseClinet
> m_con.CursorLocation = adUseClient
> then it will return the RecordCount just fine. Does this mean that if I
> use a stored procedure to open a recordset, then I need to specifically
> set adUseClient to get a recordset other than a firehose forward only
> recordset? But adUseServer is fine if I use embedded sql statement to open
> a recordset?
> Susan
>
> "Jack Vamvas" <DELETE_BEFORE_REPLY_jack@.ciquery.com> wrote in message
> news:dsv9cr$p82$1@.nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com...
>|||No, you can open a server-side cursor on a stored procedure. What's
difficult is opening a server-side scrollable cursor that supports
RecordCount on a stored procedure:
http://groups.google.com/group/micr.../>
aef2?hl=en&
To the OP, I hope you take to heart the advice in that thread to use a less
expensive way to count your records.
Bob Barrows
JT wrote:
> I thought that all ADO recordsets returned from a stored procedure are
> client side.
> "Susan" <xxx> wrote in message
> news:OXjHa8lMGHA.3708@.TK2MSFTNGP09.phx.gbl...
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.