Hi ALL,
I heard and reffered many sites to know abot cursor in sql server.
I could not clear why exactly the cursor is ineffecient to use.
I came to know that cursor needs more resources.BUT can anyone tell me wat type of resources it needs and why is it exactly ineffiecient?
keval300@googlepages.com
I've been reading around that nested views can be quite inefficient because:
a) Using views in general involves some overheads (getting info from system tables etc.)
b) The optimiser doesn't do anything intelligent with them but just mixes all the joins from each of the nested views into one big, nasty join
If the only way to get the results I need involves writing this "big, nasty" join anyway, does it matter that I'm not putting it directly into a single view, but breaking it into components so that I can also access parts of the join for other purposes?
If the queries process lots of data, are the system overheads really that noticeable?
THANX!,
Angelos
In MSDN file I read about static cursor
STATIC
Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in
tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications
It say's that modifications is not allowed in the static cursor. I have a questions regarding that
Static Cursor
declare ll cursor global static
for select name, salary from ag
open ll
fetch from ll
while @@FETCH_STATUS=0
fetch from ll
update ag set salary=200 where 1=1
close ll
deallocate ll
In "AG" table, "SALARY" was 100 for all the entries. When I run the Cursor, it showed the salary value as "100" correctly.After the cursor was closed, I run the query select * from AG.But the result had updated to salary 200 as given in the cursor. file says modifications is not allowed in the static cursor.But I am able to update the data using static cursor.
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 1 Replies View RelatedI'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
part 1
Declare @SQLCMD varchar(5000)
DECLARE @DBNAME VARCHAR (5000)
DECLARE DBCur CURSOR FOR
SELECT U_OB_DB FROM [@OB_TB04_COMPDATA]
OPEN DBCur
FETCH NEXT FROM DBCur INTO @DBNAME
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQLCMD = 'SELECT T0.CARDCODE, T0.U_OB_TID AS TRANSID, T0.DOCNUM AS INV_NO, ' +
+ 'T0.DOCDATE AS INV_DATE, T0.DOCTOTAL AS INV_AMT, T0.U_OB_DONO AS DONO ' +
+ 'FROM ' + @DBNAME + '.dbo.OINV T0 WHERE T0.U_OB_TID IS NOT NULL'
EXEC(@SQLCMD)
PRINT @SQLCMD
FETCH NEXT FROM DBCur INTO @DBNAME
END
CLOSE DBCur
DEALLOCATE DBCur
Part 2
SELECT
T4.U_OB_PCOMP AS PARENTCOMP, T0.CARDCODE, T0.CARDNAME, ISNULL(T0.U_OB_TID,'') AS TRANSID, T0.DOCNUM AS SONO, T0.DOCDATE AS SODATE,
SUM(T1.QUANTITY) AS SOQTY, T0.DOCTOTAL - T0.TOTALEXPNS AS SO_AMT, T3.DOCNUM AS DONO, T3.DOCDATE AS DO_DATE,
SUM(T2.QUANTITY) AS DOQTY, T3.DOCTOTAL - T3.TOTALEXPNS AS DO_AMT
INTO #MAIN
FROM
ORDR T0
JOIN RDR1 T1 ON T0.DOCENTRY = T1.DOCENTRY
LEFT JOIN DLN1 T2 ON T1.DOCENTRY = T2.BASEENTRY AND T1.LINENUM = T2.BASELINE AND T2.BASETYPE = T0.OBJTYPE
LEFT JOIN ODLN T3 ON T2.DOCENTRY = T3.DOCENTRY
LEFT JOIN OCRD T4 ON T0.CARDCODE = T4.CARDCODE
WHERE ISNULL(T0.U_OB_TID,0) <> 0
GROUP BY T4.U_OB_PCOMP, T0.CARDCODE,T0.CARDNAME, T0.U_OB_TID, T0.DOCNUM, T0.DOCDATE, T3.DOCNUM, T3.DOCDATE, T0.DOCTOTAL, T3.DOCTOTAL, T3.TOTALEXPNS, T0.TOTALEXPNS
my question is,
how to join the part 1 n part 2?
is there posibility?
Hi. I have a cursor in my SQL Server 2000 usp that doesn't seem to be working. It returns 0's for all counts within the cursor. I assigned a value to one of the variables after the cursor and the assigned value came back to my asp.net code. This leads me to believe the cursor variables lose their value once the cursor is done. What am I doing wrong?
--------------------------------------
DECLARE @numFPrintNA int
DECLARE @txtPrintSuit varchar(1)
DECLARE curs_Count CURSOR
FOR
SELECT numFPrintNA,
txtPrintSuit
FROM tblRecords
WHERE txtLocationCode = @txtLocationCode
AND (dtmOffDate >= @dtmFrom
AND dtmOffDate <= @dtmTo)
OPEN curs_Count
FETCH curs_Count INTO @numFPrintNA, @txtPrintSuit
IF @@FETCH_STATUS = 0
BEGIN
IF @numFPrintNA = 0 SELECT @numNACount = @numNACount + 1 ELSE SELECT @numAppCount = @numAppCount + 1
IF @txtPrintSuit = 'U' SELECT @numUnkCount = @numUnkCount + 1
IF @txtPrintSuit = 'Y' SELECT @numSuitCount = @numSuitCount + 1
IF @txtPrintSuit = 'N' SELECT @numUnsuitCount = @numUnsuitCount + 1
FETCH curs_Count INTO @numFPrintNA, @txtPrintSuit
END
GO
---------------------------------------
How do I create a "Cursor" in SQL Server 7.0 that compares an imported table against a exsisting table? Ex. Table1 is my existing table(Destination), Table2 is my imported table(source). I would like to update records in the Destination from the Source but I have to be aware of these three scenarios.
1. If the record exist in the Destination and the Source I will do nothing.
2. If the record exist in the Source and not in the Destination then I will add the record to the destination.
3. If the record exist in the Destination and not in the Source then I will write to the transaction Log.
Note: I am only concerned about updating one column in Destination which matches the only column in the source.
Someone Please help??
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...
ERROR:
Server: Msg 154, Level 15, State 3, Line 48
variable assignment is not allowed in a cursor declaration.
thanks for the help
David
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;
Forgive me if this is a stupid question.
How do you use a Server Side block cursor?
Lets say I have a db of 250,000 items and I want to retrieve the data 100 rows at a time.
How would I do this (using a block cursor, not in general)
thanks,
suppose from my code behind i will pass my sql query to sql server store procedure and i want that scriptwill be written in such a way that my store procedure will execute my query and populate cursor and then cursor will be return from my store procedure to code behind.so i want to know is it possible in sql server if so pls give me a sample sql server store procedure code.
View 1 Replies View Relatedadding a if statement within my cursor.
Use master
GO
DECLARE @dbname VARCHAR(50)
DECLARE @statement NVARCHAR(max)
DECLARE db_cursor CURSOR
LOCAL FAST_FORWARD
[code]....
The cursor should only grant access to a particular database. If the user exists within the database it should not execute the script.
I have a SQL statement stored in a SQL varriable (after a lot of conditions)
Code:
declare @sql char(100)
set @sql = 'select ma_kh, ten from _khang'
Now, I want to create a cursor to recalculate some values
I've tried:
Code:
declare cur_T cursor for exec(@sql) open cur_T
but it doesn't work.
Can I have another way to do that???
i have to delete data from a table which is older than 2 weeks, how can i use a cursor to do it.
I will have to place the query in a SQL job and run that weekly once
I have a table called Table1 where I have five fields like Tableid, Processigndate, Amount, remainingCollectonCount and Frequency. All total I have more than 5Lacs records.
Now I need to fill up another table Called FutuecashFlow taking the records from Table1. There will be also five Columns like FutureCashflowid, Table1id, Processigndate, Amount.
Now the condition is that if the remainingCollectonCount =6 and the frequency is 12 then there will be the 6 entries in the futurecasflow table where the prcessign datae wille be addeed by 1 month.
For example Table1
Tableid, Processigndate Amount remainingCollectonCount Frequency
1 2014-02-15 48 8 12
the future cash flow table the prcessing date column will be shown in the following way
Processigndate
2014-03-15
2014-04-15
2014-05-15
2014-06-15
2014-07-15
2014-09-15
2014-10-15
I do not to want to use cursor....
I need to use WHILE to avoid Cursor under certains conditions.
My SELECT statement is:
SELECT ref, ano, numberofyears ,nreint, naoreint,degress,
tabela, tax, taxamaxima,[evactual],
[evaldepact],[ereintact],nrregbt,[taxAmtAno]
FROM deprec
ORDER BY [ref] ASC
numberofyears= 100 /tax for exemple for a good where lifecycle is 4 years ,ex:
Tax = 25% Then 100/25 = 4 years
I see this WHILE script, but i need to run :
1. for each REF + Until years < 4 in this exemple, because i have goods years depend on Percent.
the WHILE script i see is:
DECLARE @table1 TABLE (Id int not null primary key identity(1,1), col1 int )
INSERT into @table1 (col1) SELECT col1 FROM table2
SET @num_rows=@@ROWCOUNT
SET @cnt=0
WHILE @cnt<@num_rows
[Code] .....
My doubt is how to make the LOOP for each REF until Year < 4 (like my example)
I am trying to calculate cumulative GPA which is currently it is being done via a cursor and i was looking to change it to set based if possible.
The cumulative GPA is basically the average marks of all courses in a term and all previous terms. Where I am having an issue is if the course was taken in a previous term and the mark was lower, then the lower mark in not factored in to the GPA. If a course was taken in a previous term and the mark was higher then the marks for the current term are not factored into the GPA.
In the example below, math is taken in all three terms and the current term's mark is included in the GPA but not the previous terms because current terms mark is higher. Two classes (bio,chem) are taken in third term but not included due to marks being higher in second term.
CREATE TABLE [gpa]([term] int,
[course] varchar(10),
[mark] float);
INSERT INTO [gpa]
VALUES (1,'math',3),
(1,'eng',4),
[Code] .....
I like to backup the stored procedures' code used in my databases. So I created this Script:
/*
IF OBJECT_ID('[Monitor].[dbo].[Procedurecode]') IS NOT NULL DROP TABLE [Monitor].[dbo].[Procedurecode];
*/
DECLARE
@db nvarchar(50),
@strSQL nvarchar (100)
IF CURSOR_STATUS('global','cur1')>=-1 BEGIN DEALLOCATE cur1 END
[code]....
Problem is (and I seem not to be the only one with tis) described here:
"If the executed string contains a USE statement that changes the database context, the change to the database context only lasts until sp_executesql or the EXECUTE statement has finished running." There is nothing magical in SQL server that knows you intend a series of dynamic sql commands to be part of a single job running in a single context. You have to build the entire string for the job you want to execute."
[URL]
So I only get the SPs of the current database.
Using SQL native client from VFP 9.0 to SQL Server 2005 64 bit SP1 (happened before SP1 too)..
We have a stored procedure that returns 6 result sets. This SP uses 2 cursors. It is rather lengthy - I'll post the code if needed.
This SP works fine when called from VFP 99 percent of the time. Normally takes 2 to 3 secunds to execute.
Once in a while we will get a return from SQL ..
"OLE IDispatch exception code 0 from Microsoft SQL Native Client: A server cursor cannot be opened on the given statement or statements. Use a default result set or client cursor..."
The OLE error code is 1429. An OLE Exception code 3604 is also returned.
When this happens the SP will return the same error when executed for the same parameters over and over when called from VFP. When called directly from SQL management console it will normally work for the same parameters, although once in a while it will just hang (and not timeout apparently). In that case it will also hang from SQLCMD command line utility as well.
Wait a few hours and the SP will run fine for the same parameters in VFP. This happens even in the middle of the night when there is no possibility that data is being changed.
Here's the really fun part...
Open the SP source for modification (ALTER PROCEDURE) in management console and execute it (no changes at all, just let it recompile). Immediately it will work fine when called with the same parameters called from VFP or anywhere else (even if it was one of the rare instances where it hung in management console). This works EVERY TIME.
Sooo... I edited and executed the SP with the WITH RECOMPILE option assuming that that should do the trick (same as alter procedure/executing from management console right?). NOPE. Same problems. In order to work around the problem when the error occurs, I HAVE TO alter procedure and execute the code from management console.
Help??
Bill Kuhn - MCSE
The Kuhn Group, Inc.
http://www.kuhngroup.com
Dear all,
I have a procedure in Oracle that contains the following cursor:
CURSOR SCHED_TRIPS IS
SELECT TRAVELDATE, STOP_NUM, TRIPID, STOP_TYPE, PROMISED_TIME, ETA, PERFORM_TIME, DEPART_TIME, ETD, DRIVERWAIT, PASSENGERWAIT, TRIPTIME, GROUP_ID
FROM Dbo.SCHEDTRIPS_VIEW
WHERE UNQ_ID = SESSION_ID AND TRUNC(TRAVELDATE) = TRUNC(TDATE)
AND DISPOSITION <> 'V';
BEGIN
FOR S IN SCH_TRIPS LOOP
UPDATE dbo.SCHEDULES T
SET T.DIRTYBIT = 1
WHERE T.TRIPID = S.TRIPID AND T.STOP_TYPE = S.STOP_TYPE AND (T.STOP_NUM <> S.STOP_NUM OR T.ETA <> S.ETA);
UPDATE dbo.SCHEDULES T
SET T.STOP_NUM = S.STOP_NUM, T.PROMISED_TIME = S.PROMISED_TIME, T.ETA = S.ETA, T.ETD = S.ETD, T.LAST_CHANGED = SYSDATE
WHERE T.TRIPID = S.TRIPID AND T.STOP_TYPE = S.STOP_TYPE;
END LOOP;
COMMIT ;
END;
My problem is with the line shown in Red. What will be the T-SQL equivalent for this line.
Anxiously waiting for help!
I am writing a custom query to determine if a legacy table exists or not. From My CMS Server I already have all the instances I have to query and I store the name of the instance in the @Instance variable. I cannot get those stubborn ticks to work right in my query. Below I am using the IF EXISTS statement to search the metadata for the legacy table.
DECLARE @Found tinyint
DECLARE @Instance varchar(100)
set @Instance = 'The Instance'
IF (EXISTS (SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=' + @Instance + ';UID=DBAReader;PWD=DBAReader;','SELECT * FROM [DBA].INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''TheTable''') AS a))
SET @Found = 1
ELSE
SET @Found = 0
PRINT @Found
Hi,
We have an application that uses a large number of cursors, and I can run sys.dm_exec_cursors (0) to view them. However, the 'name' column always appears to be NULL no matter when I run the query.
I see, when tracking some blocked processes, that the blocked processes usually have in inputbuffer that looks something like FETCH API_CURSOR00000000000003435 - which I'm trying to map back to the cursor itself. However, I can't seem to do so based on the name - since it isn't in the DMV.
Can anyone shed any light on this one?
Thanks in advance,
Chris Camadella
chris@vroominc.com
I'm new to cursors, and I'm not sure what's wrong with this code, it run for ever and when I stop it I get cursor open errors
declare Q cursor for
select systudentid from satrans
declare @id int
open Q
fetch next from Q into @id
while @@fetch_status = 0
begin
declare c cursor for
Select
b.ssn,
SaTrans.SyStudentID,
satrans.date,
satrans.type,
SaTrans.SyCampusID,
Amount = Case SaTrans.Type
When 'P' Then SaTrans.Amount * -1
When 'C' Then SaTrans.Amount * -1
Else SaTrans.Amount END
From SaTrans , systudent b where satrans.systudentid = b.systudentid
and satrans.systudentid = @id
declare @arbalance money, @type varchar, @ssn varchar, @amount money, @systudentid int, @transdate datetime, @sycampusid int, @before money
set @arbalance = 0
open c
fetch next from c into @ssn, @systudentid, @transdate, @type, @sycampusid, @amount
while @@fetch_status = 0
begin
set @arbalance = @arbalance + @amount
set @before = @arbalance -@amount
insert c2000_utility1..tempbalhistory1
select @systudentid systudentid, @sycampusid sycampusid, @transdate transdate, @amount amount, @type type, @arbalance Arbalance, @before BeforeBalance
where( convert (int,@amount) <= -50
or @amount * -1 > @before * .02)
and @type = 'P'
fetch next from c into @ssn, @systudentid, @transdate, @type, @sycampusid, @amount
end
close c
deallocate c
fetch next from Q into @id
end
close Q
deallocate Q
select * from c2000_utility1..tempbalhistory1
truncate table c2000_utility1..tempbalhistory1
I have a situation where I need to call a stored procedure once per each row of table (with some of the columns of each row was its parameters). I was wondering how I can do this without having to use cursors.
Here are my simulated procs...
Main Stored Procedure: This will be called once per each row of some table.
-- All this proc does is, prints out the list of parameters that are passed to it.
CREATE PROCEDURE dbo.MyMainStoredProc (
@IDINT,
@NameVARCHAR (200),
@SessionIDINT
)
AS
BEGIN
[Code] ....
Here is a sample call to the out proc...
EXEC dbo.MyOuterStoredProc @SessionID = 123
In my code above for "MyOuterStoredProc", I managed to avoid using cursors and was able to frame a string that contains myltiple EXEC statements. At the end of the proc, I am using sp_executesql to run this string (of multipl sp calls). However, it has a limitation in terms of string length for NVARCHAR. Besides, I am not very sure if this is an efficient way...just managed to hack something to make it work.
I have a temptable with a list of user IDs that I want to drop so I created a script to do a cursor and run through my drop functions. The drops work by themselves and the ver check works with them but when I wrap them in the cursor all i get is an output for each user in the results window in ssms. why it's not setting the variable and instead outputting to results?
DECLARE @ver nvarchar(128);
DECLARE @UserName nvarchar(50);
DECLARE @UserD nvarchar(80);
DECLARE @LoginD nvarchar(80);
-- Initialize the variable.
SET @ver = CAST(serverproperty('ProductVersion') AS nvarchar)
[code]...
I need removing cursor in my table valued function with alternate code.
ALTER FUNCTION [dbo].[eufn_e5_eSM_SE_GetCurrentContentForContainer]
(
@containerSqlId SMALLINT,
@containerIncId INT
)
RETURNS @Results TABLE
[Code] ....
This store procedure will get some executable queries from the select statement, the cursor will fetch each rows to execute the query and insert the queries into table_3 to mark as 'E'. Until 17:00, this store procedure will stop execute the queries and just get the queries from select statement insert into table_3 to mark as 'C'.
I don't know why the outputs in table_3 are quiet different than I think. This store procedure comes out with two exactly same queries and one marked as C and another marked as E.
CREATE PROCEDURE procedure1
AS
DECLARE cursor_1 CURSOR FOR
SELECT
'This is a executable query'
FROM table_1
DECLARE @table_2
[code]....
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
I need to find all uses of special characters in a database. I used the following code to do this:
USE dbName
GO
IF OBJECT_ID('tempdb.dbo.#Results') IS NOT NULL DROP TABLE #Results
GO
[code]...
This will check all tables in the database, but if you want to check specific tables you can uncomment the line in the where clause and specify tables to be checked. The query will return any text fields that have any characters other than letters, numbers or spaces.
This code works fine for me because all the tables in my database have single column primary keys. However I know how much Jeff Moden hates cursors or RBAR queries, so my question is could this have been done by any method other than using a cursor?
I have been trying to write a cursor to fetch required data from table but somehow its running forever and inserting duplicate records.
I have a temp table named getInvoice where I have five important columns
1. invoice number
2.group
3.invoice status
4. Invoice Expiration date
5. Creation date time
and some other columns.One invoice number can belong to one or more group and there can be one or more records for a particular invoice number and group.
An example is below :
InvoiceNumber Group InvoiceStatus InvoiceExpirationDate CreationDateTime
579312 01 3 NULL 2003-03-24 00:00:00
579312 01 2 2015-12-14 00:00:00 2005-12-24 00:00:00
579312 02 2 2003-12-21 00:00:00 2005-10-12 00:00:00
321244 01 2 2015-12-21 00:00:00 2005-10-12 00:00:00
321244 01 3 2010-12-21 00:00:00 2010-12-21 00:00:00
My query condition is complex and that is why Im facing problem retrieving the output.I need a cursor for getting distinct invoice number from the table and for each invoice number I need to get the latest record for each invoice number and suffix combination based on creationdateand time column and if that record has invoice status of 2 and also the invoice expiration date can be either null or greater than today's date, then I need to get that record and put it in a temp table.
The query I wrote is below
declare myData cursor for
select distinct invoiceNumber from #getInvoice
declare @invoiceNumber varchar(30)
open myData
fetch next from myData into @invoiceNumber
while @@FETCH_STATUS = 0
[Code] .....
This query runs forever and doesn't stop.
This store procedure will get some executable queries from the select statement, the cursor will fetch each rows to execute the query and insert the queries into table_3 to mark as 'E'. Until 17:00, this store procedure will stop execute the queries and just get the queries from select statement insert into table_3 to mark as 'C'.
I don't know why the outputs in table_3 are quiet different than I think. This store procedure comes out with two exactly same queries and one marked as C and another marked as E.
CREATE PROCEDURE procedure1
AS
DECLARE cursor_1 CURSOR FOR
SELECT
'This is a executable query'
FROM table_1
DECLARE @table_2
DECLARE @stoptime DATETIME = NULL;
[Code] ....
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.