MS SQL Server :: Mdac 2.8 And Sql 2005 Cursor Performace Issue

Mdac 2.8 And Sql 2005 Server Side Cursor Performace Issue
Hello

I have a VB6 application using classic ado (MDAC 2.8) for connecting ms sql 2000 server. Application uses a lot of server side cursors. Now I want to switch to ms sql 2005 server but I have noticed very serious performance problem. Sql profiler results of execution of following commands:

declare @p1 int
set @p1=180150131
declare @p3 int
set @p3=1
declare @p4 int
set @p4=16388
declare @p5 int
set @p5=22221
exec sp_cursoropen @p1 output,N' Select ... from ... where .... order by ...',@p3 output,@p4 output,@p5 output
select @p1, @p3, @p4, @p5

on sql server 2000:

CPU:    234
Reads:   82515
Writes:   136
Duration:   296

and on sql server 2005:

CPU:    4703
Reads:   678751
Writes:   1
Duration:   4867

Both databases are identical, the servers runs on the same machine (Pentium 2,8 Ghz, 2 GB RAM) with only one client connected. On forums I've read that Microsoft doesn't recommend using server side cursors on sql 2005 but is there any way to increase performance to some acceptable level?

thanks in advance

szymon strus

View Replies ! View Related

Performace Issue With SQL Native Client
and TCP as a connection method.

When using Named Pipes the issue is no longer there.

On massive batch inserts we sometimes get a long pause at the end of one insert and before begining the next one. Example:

1000 inserts in the same table and then repeat. This will work fine for 3 or 4 iterations, then pause during the 5th iteration for up to 40 seconds and then simply continue.

When this exact same procedure is done using Named Pipes as the connection method this never happens.

While this is happening neither the server or the workstation is doing anything, 0% CPU, 0% network, it just sits there.

All this using the SQL Native Client 2005 and ADO.

Anyone have any ideas?

View Replies ! View Related

MDAC 2.8 And Cursor FETCH NEXT
it seems that starting with MDAC 2.8 the FETCH NEXT statement for cursors requires an INTO clause. otherwise an unspecified error is returned.

so it's not possible to scroll through the records anymore without storing the values of the fields into local variables?

View Replies ! View Related

Moving From 2000 To 2005: Issue Accessing Cursor Returned By Sp
I have a number of triggers that call a stored procedure that returns a cursor. The triggers then use the results of this cursor to do other actions.

My problem is that this works fine in SQL2000 but just won't work in SQL2005. When I try to access the results of the returned cursor, I get an error -2147217900 could not complete cursor operation because the set options have changed since the cursor was declared.

If I port the code contained in the sp into the trigger, it runs fine. But having to port over the sp's code defeats the whole concept of being able to re-use the sp.

Does anybody have any ideas of what could be going on?

I look forward to a quick response.

Dennis

View Replies ! View Related

SQL Server 2005 Issue - The Cursor Type/concurrency Combination Is Not Supported.
Hi

I have recently upgraded from SQL 2000 to SQL 2005 and I'm getting the following problem, can you suggest me if this is a issue with SQL 2005 or suggest me an asnwer for this.

Below is the exception from my log file

The cursor type/concurrency combination is not supported.
com.microsoft.sqlserver.jdbc.SQLServerException: The cursor type/concurrency combination is not supported.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.<init>(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.<init>(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.prepareStatement(Unknown Source)

The following is the piece of code where the problem I'm assuming is happening, how can I correct it.

varStmt1 = varConnection.prepareStatement(varCitationSQL.toString(),ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);

Have tried using both JDC v1.1 and 1.2 but of no use.

View Replies ! View Related

Minimum MDAC Version Requirement (Error Very Strange..because My MDAC Is 2.8 Sp1) ...help Me Please!!!
I trying to install SQLServerExpress on PC with W2000 Pro Sp4 - IE6.0Sp1 - dot.net framework 2.0 and MDAC 2.8 Sp1, but when the setup check the requirements, I received this error:

Minimum MDAC Version Requirement (Error)
Messages
* Minimum MDAC Version Requirement

* The system does not have the required version of Microsoft Data Access Components (MDAC) for this SQL Server release. For details, see Hardware and Software Requirements for Installing SQL Server 2005 in Microsoft SQL Server Books Online.

After... I reinstalled all MDAC versions and the MDAC Component Checker and I verifyed that my version is 2.8.Sp1 and the key HKEY_LOCAL_MACHINESOFTWAREMICROSOFTDATAACCESSFullInstallVer=2.81.1117.6.

Why??? :-(((( help me please!!!!

Thanks a lot!

View Replies ! View Related

MDAC Problem With SQLServer 2005?
I am running SQLServer 2005 on Windows XP sp2.

I can't build a data connection in BIDS (error 80040154), and I checked the internet for possible solutions.

I downloaded Microsoft Component Checker and it informed me that I had a mismatch with MDAC 2.8 SP1 and XP SP2.

Specifically SQLOLEDB.dll (expected 2000.85.1117.0) and MSXML3.dll (expected 8.50.2162.0) and SQLXMLX.rll (expected 2000.85.1117.0) seem to be mismatched.

There are all kinds of instructions on the web for various fixes, and I am not certain what is current.

What do I need to load/uninstall in order to fix this problem?

Thanks very much...Marv

View Replies ! View Related

Issue With Cursor
Hi

I have created a cursor with the following syntax: "DECLARE costs_cursor CURSOR SCROLL DYNAMIC FOR SELECT RegNumber, FirstName, LastName, Assessment, Catering, Travel, Accommodation, Other from dbo.T_Course_Data ORDER BY LastName OPEN costs_cursor" which works.

What I don't understand is why, when I attempt to update a value in the cursor, irrespective of whether I use the 'FOR UPDATE' option or not, I get the error message to the effect that the cursor cannot be updated because it is READ ONLY. Clearly (to my mind anyway) the cursor wasn't created as read only. My update statement is "Update dbo.T_Course_Data set Assessment='222' WHERE CURRENT OF costs_cursor"

The odd thing is I have another cursor in my app using the exact same statements and it doesn't give this error.

Please help if you can.

Neil

View Replies ! View Related

Cursor Issue
Can someone tell me what's wrong with this cursor?

if @senddisputed=1
declare x cursor for select email from dbo.Users where UserType='A' and email is not null
open x
fetch next from x into @email
while @@fetch_status = 0
BEGIN
insert into emails (transno,reqid,efrom,eto,subject,body,notified,emailtype)
(select 0,0,@fromaddress,@email,'Disputed Transaction Notification',
'This Email is to alert you that you currently have '+ ltrim(str(count(distinct th.transno)))+ ' transactions in
resolve that are under the status of declined. Please log into the system and review these records.' + char(13),
null,8
from
transhdr th join cards c on c.cardid = th.cardid join users u on c.mgrid = u.userkey
join transdtl td on th.transno = td.transno
join recon r on th.transno = r.transno
where
u.email is not null
and th.reqid=1
group by
u.email)
fetch next from x into @email
END
close x
deallocate x

Results:
Msg 16916, Level 16, State 1, Procedure email_generate_general_is2, Line 212
A cursor with the name 'x' does not exist.
Msg 16916, Level 16, State 1, Procedure email_generate_general_is2, Line 215
A cursor with the name 'x' does not exist.
Msg 16916, Level 16, State 1, Procedure email_generate_general_is2, Line 234
A cursor with the name 'x' does not exist.
Msg 16916, Level 16, State 1, Procedure email_generate_general_is2, Line 235
A cursor with the name 'x' does not exist.

---
http://www.ssisdude.blogspot.com/

View Replies ! View Related

Cursor Issue - Order Not Correct
I'm using a cursor in SQL Server 2000 to assist me in calculating for each store, the Sales Rank of a zip code. There are about 1500 stores, and 125,000 store/sales/zip code records.

I am finding that this works for about 95% of the stores, but about 5% are getting fouled up, where the store's records are getting split in the sequencing, and so the store ends up with two zips ranked 1, two ranked 2, two ranked 3, etc.

In the DB structure, there is a constraint restricting one record per store (org_id) per zip code (postalcode).

Here's my code. Basically what I'm trying to have the cursor do is go through the table, ordered by org_id (store) asc, org_criteria_value (sales) desc, and rank the zip codes. When a new store is encountered, reset the counter to 1 and start ranking again. Do this until all the records are processed.

/*CREATE Sales_Table table */
CREATE TABLE [dbo].[Sales_Table] (
[count_id] [int] NULL ,
[org_id] [int] NULL ,
[postalcode] [varchar] (20) NULL,
[sales] [numeric](18,6) NULL ,
[sales_rank] [integer] NULL,
[org_criteria_input_date] [datetime] NULL
) ON [PRIMARY]

insert into Sales_Table
select 0 as count_id, omd.org_id, omd.postalcode, omd.org_criteria_value, 0 as cum_rank, org_criteria_input_date
fromorg_model_data omd
join org o on o.org_id = omd.org_id
where o.client_id = @ClientID
and model_Criteria_id = 27
and org_criteria_value <> 0
order by omd.org_id asc, omd.org_criteria_value desc

-- DECLARE CURSOR for Sales_Table

declare SalesRankCursor CURSOR
SCROLL dynamic FOR
select org_id, sales, sales_rank
from Sales_Table
for update of sales_rank

-- CREATE LOOP TO UPDATE SALES RANK in Sales_Table with valid values

OPEN SalesRankCursor

while exists (Select * from Sales_Table where sales_rank = 0)
Begin

FETCH NEXT FROM SalesRankCursor

set @StoreNext = @StoreCurrent
set @SalesRank = (@SalesRank + 1)

update Sales_Table
set@StoreCurrent = org_id
where current of SalesRankCursor

if @StoreCurrent <> @StoreNext
begin
set @SalesRank = 1
end

update Sales_Table
setsales_rank = @SalesRank
where current of SalesRankCursor

End

CLOSE SalesRankCursor

DEALLOCATE SalesRankCursor

Any ideas?

View Replies ! View Related

Pass Linked ServerName To A Cursor Issue...
Need to loop through a Cursor to linked server:
-----------------------------------------------
Declare Cursor_Loop_serverName Cursor for
select cast(name as varchar(30)) name, cast(dbID as varchar(5)) dbID,
cast(crdate as varchar(25)) crdate
from ServerName_A.master.dbo.sysdatabases

***How could I pass @serverName to change the from to
from @RemoteServer.master.dbo.sysdatabases?
I have tried dynamic sql, it did not work after the Declare Cursor for...

thanks for the help
David

View Replies ! View Related

Conducting A Radius Search Via Postcodes - Cursor Issue?
Hi, Iâ€™ve developed the following stored procedure to query a table of services following a user search. The user can specify a radius from a certain location(UK postcode) in which to search, and its with this Im having a few problems.

Hereâ€™s my method:

STEP1 - I first check the table of services and insert all services matching the specified criteria (regardless of location) into a temporary table (#tMatches).

STEP 2 - I get the postcode entered by the user and retrieve its co-ordinates.

STEP 3 â€“ I open a cursor and loop through the records in #tMatches. For each record, I first retrieve their co-ordinates and then use a bit of Pythagoras to work out whether its location falls within the radius specified by the user.

STEP 4 â€“ If the record falls outside the range specified by the user, I deleted from #tMatches.

STEP 5 â€“ I bind the remaining (matching) records from the #tMatches to a dataset.

Now, I think that seems pretty simple, and the easiest way to achieve what Iâ€™m trying to do. How then when querying just 600 records am I finding it times out? I think Iâ€™ve pinned it down to the STEP 3 (cursor), but not being the SQL expert Iâ€™d like to be, Iâ€™ve no further ideas where Iâ€™m going wrong? Any ideas?

The following is an excerpt from the script - don't worry about the fact several parameters may not be declared.

Many thanks

======================================

--STEP 1
CREATE TABLE #tMatches
(
record_uid uniqueidentifier,
service_name varchar(500),
service_address varchar(500),
service_description varchar(500),
GRE integer,
GRN integer
)
BEGIN
INSERT INTO #tMatches
(record_uid,service_name,service_address,service_description,GRE,GRN)
SELECT service_loc_uid,title_return,service_address,service_overview,GRE,GRN FROM v_RecordsLive_short WHERE (title_return LIKE '%' + @lookingfor + '%' OR service_category LIKE '%' + @lookingfor + '%' OR service_overview LIKE '%' + @lookingfor + '%')
END

-STEP 2

DECLARE @UserCoordX integer
DECLARE @UserCoordY integer
--1) USE POSTCODE DATA FOR REGION ONLY
SELECT @UserCoordX = GRE,@UserCoordY = GRN FROM tPCD WHERE tPCD.PCD = @postcode

--STEP3

--open a cursor containing record ids with co-ordinates
DECLARE @record_uid uniqueidentifier
DECLARE @CoordX integer
DECLARE @CoordY integer
DECLARE @XLen integer
DECLARE @YLen integer
DECLARE @range real

DECLARE locs_cursor CURSOR FOR
SELECT record_uid,GRE,GRN FROM #tMatches

OPEN locs_cursor

FETCH NEXT FROM locs_cursor
INTO @record_uid,@CoordX,@CoordY

WHILE @@FETCH_STATUS = 0
BEGIN
--calculate range from entered postcode using pythagorus
SET @XLen = Abs(@UserCoordX - @CoordX)
SET @YLen = Abs(@UserCoordY - @CoordY)
SET @range = SQRT((@XLen * @XLen) + (@YLen * @YLen))

END

--convert range to miles
SET @range = COALESCE(@range,0)
IF (@range > 0)
SET @range = @range/160.9

--IF OUT OF RANGE, DELETE RECORD FROM TABLE STRAIGHTAWAY
IF @range > CAST(@miles as float)
DELETE FROM #tMatches WHERE record_uid = @record_uid

--get next record
FETCH NEXT FROM locs_cursor
INTO @record_uid,@CoordX,@CoordY

CLOSE locs_cursor
DEALLOCATE locs_cursor

--------------------RETURN ALL RESULTS-----------------------------------------------
SELECT record_uid,service_name,service_address,service_description FROM #tMatches

DROP Table #tMatches

View Replies ! View Related

Why The Performace Differs?
All,I have a problem regarding SQL Server 2000 SP3,I have SP that calls other SP and it inserts about 30,000 records as atime,in the development environment (MS Windows 2003 Enterprise, 256 RAM,3.0 GHz Intel Processor) takes about 6 seconds to run this SP.But, with the same Software but, 2.6 GHz Intel and 1 GB Ram, it runsvery slow it takes more than 135 Seconds to run,I have read a lot of articles about expanding the SQL Memory and giveit a higher process privilege but, with no use,I don't know where the problem is, do you have any idea about what isthe problem?Thank you in advance,MAG

View Replies ! View Related

Better Performace With Query
its k i got it :)

View Replies ! View Related

JOIN Performace Vs. Multiple RersultSets
Can anyone give me some guidelines as to when to chose JOINS over returning multiple resultsets in a strored procedure..
For eample, I have two tables, Orders and OrderDetails, which are linked by a primary key field. There can be orders w/o a corresponding record in orderdetails.
1.) I can return all orders and their details using a stored preocedure that has:
   SELECT o.order_id as OrderId, o.customername, od.order_id, od.orderdate FROM orders AS o LEFT OUTER JOIN orderdetails AS od ON (o.order_id=od.order_id)
2.) I can do the same by returning two results sets in a different stored procedure:
   SELECT order_id, customername FROM orders
   SELECT order_id, orderdate FROM orderdetails
I think the client processing time for the second option will be slightly less, because the resultset I need to filter will only be as big as the orederdetails table (it will not include records for orders that have no details). Regardless, I think this would only make for a small performance gain, so if Option 1 is better in Database performace, I would probably go with that.
I assume the method to choose also depends on table size and # of JOINS. Any guidance would be appreciated. Thanks,
Al

View Replies ! View Related

Frustration.. Slow Performace When Editing A DTS
I have tried many things and even the worst case thing which I was trying to avoid i.e. uninstalled MSDE SP3 and Analysis Services and service packs but the problem is still not solved...

Whenever I open a DTS in design view and double click on the link (the line connecting the two) between the source and destination servers the PC goes to sleep and comes back after a long time and then the same problem occurs when I press on the transformation tab...

I know this may sound weird but that really is the case :eek:

Please help :o

View Replies ! View Related

Openquery Potential Performace Blackhole?
I have a openquery query like this
Select * from openquery([db2],'Select * from tableA')

To only return and process records for a given date range I changed it to be something like this

Select * from openquery([db2],'Select * from tableA')
where datefield > '06/06/2001'

While this works fine, my question is that does it copy all the records from the db2 server to the sql server before filtering them. I think it does.
The db2 table will have over 1,000,000 records eventually, and the sql server will use records for a given day/date range only.

I cannot add the where clause to the db2 query.

any ideas ?

tia.

neil.

View Replies ! View Related

Unable To Get The Performace With Partition Table
I created two tables one is based on partition structure and one is non-partition structure.

File Groups= Jan,Feb.....Dec
Partition Functions='20060101','20060201'......'20061201'
I am using RIGHT Range in Partition function.
Then I defined partition scheme on partition function.

I have more than 7,00,000 data in my database.
I checked filegroups and count rows. It works fine.

But When I check the estimation plan time out for query it is same for both partition table and non partition table.

View Replies ! View Related

Key Factors For Efficiency Of SQL Server 2000 (performace Tuning)
Hello All,

I am new to SQL Server 2000. I am eager to learn what factors/parameters are key for obtaining good retrieval performance of SQL Server 2000 (prompt response to user query).

I recall that someone told me that a recordset with asOpenStatic cursor type has higher speed than that of a recordset with other cursor types.

Is this true or false. Are there really some key parameters for perfomance tuning .

Thanks

View Replies ! View Related

Fulltext Large (500.000) Count Query Performace Too Slow
Hi,

I am with the response time for a simple count on a fulltext search that is too slow.

Even using the most simple query on a good server (64 bit Dual Opteron 4GB Ram with high speed 16 raid disk storage)):

select count(*) from content_books where contains(searchData,'"english"')
Takes 4 seconds to count the avg 500.000 resultsI have removed all the joins with real table data so that the query is only inside the fulltext engine..

I would expect this to be down to 4 milli seconds. Isn't it just getting the size of the "english" word result index?

It seems the engine is going through all the results because if a do a more complex search that returns less results the performance is better.

Any clues of how to do this faster? I never read the thousands of records BUT i need to count them...

Thank you very much.

View Replies ! View Related

Sql Server 2005 Cursor
I am not fimilar with cursors at all but need some assistance/direction on how to re-create the following.

The result set will need to give me a list of new customers with sign dates of last year and this current year plus their sales for each year. If the customer has a date signed of 2007 I need to see the "year of sales" for both 2007 and 2008, whether or not they actually had sales.

I'm needing to convert an oracle cursor to Sql Server 2005.  If at all possible I would like to stay away from the cursor and create this some other way. Any help would be greatly appreciated.

Customer      Date Signed   Year of Sale     Amount
1111              7/1/07               2007              $50,000.0
                                             2008                        0.0

Below is the oracle cursor that was used to calculate this information in the past.

DECLARE
                MBLDRN      BAV_BUILDER_NEW.BLDRN%TYPE;
                MYR_SIGNED BAV_BUILDER_NEW.YEAR%TYPE;
                MCOMPANY    BAV_BUILDER_NEW.COMPANY%TYPE;
                MSHORTNAME BAV_BUILDER_NEW.SHORTNAME%TYPE;
                MDSIGNED    BAV_BUILDER_NEW.DSIGNED%TYPE;
                MDCANCELED BAV_BUILDER_NEW.DCANCELED%TYPE;
                MDMTERR     BAV_BUILDER_NEW.DMTERR%TYPE;
                MDMNAME BAV_BUILDER_NEW.DMNAME%TYPE;
                MENGR_TEAM BAV_BUILDER_NEW.ENGR_TEAM%TYPE;
                MSALESREGON BAV_BUILDER_NEW.SALESREGON%TYPE;
                MDIVCODE    BAV_BUILDER_NEW.DIVCODE%TYPE;
                MYRLOOP    NUMBER;
              MLSTYR     NUMBER;
              MSYEAR     DATE;
                MEYEAR     DATE;
CURSOR CA IS SELECT YEAR,BLDRN,COMPANY,SHORTNAME,DSIGNED,DCANCELED,
                      DMTERR,DMNAME,ENGR_TEAM,SALESREGON,DIVCODE
                 FROM BAV_BUILDER_NEW
               ORDER BY YEAR;
BEGIN
SELECT YEAR INTO MLSTYR FROM CURRENT_DATES;
OPEN CA;
LOOP
        FETCH CA INTO MYR_SIGNED,MBLDRN,MCOMPANY,MSHORTNAME,MDSIGNED,MDCANCELED,
                      MDMTERR,MDMNAME,MENGR_TEAM,MSALESREGON,MDIVCODE;
        EXIT WHEN CA%NOTFOUND;
        MYRLOOP := MYR_SIGNED;
        WHILE MYRLOOP <= MLSTYR LOOP
        SELECT MIN(SYEAR) INTO MSYEAR FROM UDBDATES WHERE YEAR=MYRLOOP;
        SELECT MAX(EYEAR) INTO MEYEAR FROM UDBDATES WHERE YEAR=MYRLOOP;
        BEGIN
        INSERT INTO BAV_BUILDER_NEW_DATA (YR_SIGNED,DIVCODE,SALESREGON,
                                                BLDRN,COMPANY,SHORTNAME,DSIGNED,DCANCELED,ENGR_TEAM,
                                                DMTERR,DMNAME,YR_SALES)
                VALUES
                    (MYR_SIGNED,MDIVCODE,MSALESREGON,MBLDRN,MCOMPANY,MSHORTNAME,
                     MDSIGNED,MDCANCELED,MENGR_TEAM,MDMTERR,
                     MDMNAME,MYRLOOP);
        END;
        MYRLOOP := MYRLOOP + 1;
        END LOOP;
COMMIT WORK;
END LOOP;
COMMIT WORK;
CLOSE CA;
END;
/
COMMIT WORK;

View Replies ! View Related

SQL 2005- Cursor Loop
Please advice when i used loop in the below code its only returning last value from the sub table however i wanted to fetch all values from the sub-table----
Please advice..

ALTER Procedure [dbo].[SyncEmpContGrid] @EmpID As Varchar(20)

As

Declare @CountryEOF bit,

@i int,

@CtR Varchar (1000)

begin

Declare @C_list Cursor

Declare ContCr Cursor Local for

(Select custCountryofExperience from Employees_CountryExperience where Employee=@EmpID)

Set @C_List = contCr

Open @C_List

Fetch next from @C_List into @CtR

While (@@Fetch_Status = 0 )

Begin

set @ctR = @ctR + ','

Fetch next from @C_List into @CtR

update EmployeeCustomTabFields

Set custTxt_Ct = @CtR Where (EmployeeCustomTabFields.Employee = @EmpID);

end

Close @C_List

Deallocate @C_List

Deallocate ContCr

end

View Replies ! View Related

Simulating A SQL Cursor Task In I.S. 2005
Hi EveryBody,

I have the following question :

What components of I.S. would I use, for simulating a cursor in SQL like that?

Example of simple cursor :

declare c_CURSOR cursor FOR
SELECT DISTINCT V.CODIGO FROM GB_RELACIONAL.DBO.VISITA V INNER JOIN MEDIDAS_LINEAL_PROMOCION_200604_02 M
ON M.CODIGO_VISITA=V.CODIGO AND M.TIEMPO_ENTRE_vISITAS <> V.TIEMPO_ENTRE_VISITAS

OPEN C_CURSOR
FETCH NEXT FROM C_CURSOR INTO @CODIGO
WHILE @@FETCH_STATUS=0

Do various Jobs; Doing Selects, Inserts etc.. using @codigo

FETCH NEXT FROM C_CURSOR INTO @CODIGO
end
CLOSE C_CURSOR
DEALLOCATE C_CURSOR

Then what I need is passing the @codigo in every loop, to other components in I.S., for doing inserts in other related tables.

Then my questions are:

a- What Component i would use for simulating the looping cursor like the example ?

b- How i Store and pass de @codigo to the other I.S. components for doing the selects, inserts?

c- What I.S. components and how would use, for receiving the @codigo ,and doing the corresponding Select , Inserts.. in the related tables , using that variable ( @codigo ) ?

I need an Idea of the I.S. components, configurations, and the flow for doing something like this...

Thank in advance!!

View Replies ! View Related

Dynamic Cursor Versus Forward Only Cursor Gives Poor Performance
Hello,I have a test database with table A containing 10,000 rows and a tableB containing 100,000 rows. Rows in B are "children" of rows in A -each row in A has 10 related rows in B (ie. B has a foreign key to A).Using ODBC I am executing the following loop 10,000 times, expressedbelow in pseudo-code:"select * from A order by a_pk option (fast 1)""fetch from A result set""select * from B where where fk_to_a = 'xxx' order by b_pk option(fast 1)""fetch from B result set" repeated 10 timesIn the above psueod-code 'xxx' is the primary key of the current Arow. NOTE: it is not a mistake that we are repeatedly doing the Aquery and retrieving only the first row.When the queries use fast-forward-only cursors this takes about 2.5minutes. When the queries use dynamic cursors this takes about 1 hour.Does anyone know why the dynamic cursor is killing performance?Because of the SQL Server ODBC driver it is not possible to havenested/multiple fast-forward-only cursors, hence I need to exploreother alternatives.I can only assume that a different query plan is getting constructedfor the dynamic cursor case versus the fast forward only cursor, but Ihave no way of finding out what that query plan is.All help appreciated.Kevin

View Replies ! View Related

Variable Type For Fetcing The Cursor Record In Tsql Cursor
what is the equivalent of the %rowtype in oracle for the tsql

View Replies ! View Related

Could Not Complete Cursor Operation Because The Set Options Have Changed Since The Cursor Was Declared.
I'm trying to implement a sp_MSforeachsp howvever when I call sp_MSforeach_worker
I get the following error can you please explain this problem to me so I can over come the issue.

Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 31

Could not complete cursor operation because the set options have changed since the cursor was declared.

Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 32

Could not complete cursor operation because the set options have changed since the cursor was declared.

Msg 16917, Level 16, State 1, Procedure sp_MSforeach_worker, Line 153

Cursor is not open.

here is the stored procedure:

Alter PROCEDURE [dbo].[sp_MSforeachsp]

@command1 nvarchar(2000)

, @replacechar nchar(1) = N'?'

, @command2 nvarchar(2000) = null

, @command3 nvarchar(2000) = null

, @whereand nvarchar(2000) = null

, @precommand nvarchar(2000) = null

, @postcommand nvarchar(2000) = null

AS

/* This procedure belongs in the "master" database so it is acessible to all databases */

/* This proc returns one or more rows for each stored procedure */

/* @precommand and @postcommand may be used to force a single result set via a temp table. */

declare @retval int

if (@precommand is not null) EXECUTE(@precommand)

/* Create the select */

EXECUTE(N'declare hCForEachTable cursor global for

SELECT QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_TYPE = ''PROCEDURE''

AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)), ''IsMSShipped'') = 0 '

+ @whereand)

select @retval = @@error

if (@retval = 0)

EXECUTE @retval = [dbo].sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 0

if (@retval = 0 and @postcommand is not null)

EXECUTE(@postcommand)

RETURN @retval

GO

example useage:

EXEC sp_MSforeachsp @command1="PRINT '?' GRANT EXECUTE ON ? TO [superuser]"

GO

View Replies ! View Related

CURSOR_STATUS Unreliable: SQL 2005 -- Is There A Better Way To Find Cursor Status?
What is the best way of reliably closing a cursor inside a BEGIN CATCH block? The simple problem statement is: I want to check if the cursor is open, then close it. I'm trying to use CURSOR_STATUS function and it seems to return a -3 (both when the cursor is open and not open.). Is this a bug or am I missing something?

I have removed all of my code and only provided what is necessary to repro the problem.

====================================================================================================

CREATE PROCEDURE dbo.repro_PROC

(

@condition1 varchar(10),

@condition2 varchar(10)

)

AS

BEGIN

BEGIN TRY

SET NOCOUNT ON;

SET XACT_ABORT ON;

DECLARE @dbName varchar(64), @dbid int;

DECLARE @FileID int;

DECLARE @Physical_Name varchar(512);

IF (@condition1 = 'ERROR')

RAISERROR ('ERROR: This error happens before any cursor is declared or is open', 16, 1);

DECLARE dbNameCursor CURSOR FOR SELECT Name, database_id FROM master.sys.databases ORDER BY Name;

OPEN dbNameCursor;

FETCH NEXT FROM dbNameCursor INTO @dbName, @dbid;

WHILE @@FETCH_STATUS = 0

BEGIN

DECLARE fileCursor CURSOR FOR SELECT File_ID, Physical_Name FROM master.sys.master_files

WHERE database_id = @dbid

ORDER BY File_ID;

OPEN fileCursor;

FETCH NEXT FROM fileCursor INTO @FileID, @Physical_Name;

WHILE @@FETCH_STATUS = 0

BEGIN

-- some condition which causes an exception

IF (@condition2 = 'ERROR')

RAISERROR ('ERROR: This error happens after both cursors are open', 16, 1);

FETCH NEXT FROM fileCursor INTO @FileID, @Physical_Name;

END

CLOSE fileCursor;

DEALLOCATE fileCursor;

FETCH NEXT FROM dbNameCursor INTO @dbName, @dbid;

END

CLOSE dbNameCursor;

DEALLOCATE dbNameCursor;

END TRY

BEGIN CATCH

DECLARE @ErrorMessage NVARCHAR(4000);

DECLARE @ErrorSeverity INT;

DECLARE @ErrorState INT;

DECLARE @fileCursorStatus INT;

DECLARE @dbNameCursorStatus INT;

SELECT

@ErrorMessage = ERROR_MESSAGE(),

@ErrorSeverity = ERROR_SEVERITY(),

@ErrorState = ERROR_STATE();

SET @fileCursorStatus = CURSOR_STATUS('local', 'fileCursor');

SET @dbNameCursorStatus = CURSOR_STATUS('local', 'dbNameCursor');

PRINT 'Cursor_Status (fileCursor) = ' + convert(varchar, @fileCursorStatus);

PRINT 'Cursor_Status (dbNameCursor) = ' + convert(varchar, @dbNameCursorStatus);

IF (@fileCursorStatus >= 0)

BEGIN

PRINT 'Closing fileCursor';

CLOSE fileCursor;

DEALLOCATE fileCursor;

END

IF (@dbNameCursorStatus >= 0)

BEGIN

PRINT 'Closing dbNameCursor';

CLOSE dbNameCursor;

DEALLOCATE dbNameCursor;

END

-- info about the original error

RAISERROR (@ErrorMessage, -- Message text.

@ErrorSeverity, -- Severity.

@ErrorState -- State.

);

END CATCH;

END;

====================================================================================================

EXEC dbo.repro_PROC @condition1='ERROR', @condition2='NO-ERROR'

Output:

Cursor_Status (fileCursor) = -3

Cursor_Status (dbNameCursor) = -3

Msg 50000, Level 16, State 1, Procedure repro_PROC, Line 88

ERROR: This error happens before any cursor is declared or is open

-------------------------------------------------------------------------

EXEC dbo.repro_PROC @condition1='NO-ERROR', @condition2='ERROR'

Output:

Cursor_Status (fileCursor) = -3

Cursor_Status (dbNameCursor) = -3

Msg 50000, Level 16, State 1, Procedure repro_PROC, Line 89

ERROR: This error happens after both cursors are open

-------------------------------------------------------------------------

EXEC dbo.repro_PROC @condition1='NO-ERROR', @condition2='ERROR'

Output:

Cursor_Status (fileCursor) = -3

Cursor_Status (dbNameCursor) = -3

Msg 50000, Level 16, State 1, Procedure repro_PROC, Line 89

A cursor with the name 'dbNameCursor' already exists. <=== THIS IS A PROBLEM

=================================================================================================

If I try to close the cursor when the status = -3, then sometimes it closes successfully, some other times, it errors "Cursor NOT open" etc.

View Replies ! View Related