DB Engine :: TOP Changing Cursor Select Performance

Jun 15, 2015

the cursor at the bottom iterates only to print the number of rows.The problem is in the select. This takes 30 seconds to iterate through 1242 records.But if I add a TOP 1000000 or whatever number to the select, the same iteration takes less than a 1 second.I've tested each query without cursor, and both have the same cost and performance. (Not exactly the same plan)Note that I got the same performance improvement declaring the cursor as STATIC.Why the top is affecting the cursor iteration so much?

Declare @query varchar(512)
DECLARE Itera CURSOR --LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
select --TOP 1000000

[code]....

View 2 Replies

Dynamic Cursor Versus Forward Only Cursor Gives Poor Performance

Jul 20, 2005

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 Related

DB Engine :: Changing ANSI PADDING On A Table

Aug 3, 2012

I have a question relating to the ANSI_PADDING setting on some existing tables in a SQL Server 2008 R2 database I am working with. When I generated the tables originally I basically programmatically created them by building CREATE scripts within my code. Since I did not explicitly set ANSI_PADDING to ON all these tables they seem to have been created with ANSI_PADDING as OFF. Some of these tables, which I now need to add columns to, contain varchar(n) and varbinary(n) columns.

When I try to alter the tables through Management Studio, SQL Server gives me a warning: "One or more tables have ANSI_PADDING 'off' and will be recreated with ANSI_PADDING 'on'" - this seems to be generated by the ALTER statement which by default sets ANSI_PADDING to ON. Another iteration of the same warning - "Columns have different ANSI_PADDING settings. New columns will be created with ANSI_PADDING 'on'".

From what I read regarding ANSI_PADDING it seems ON is definitely the way to go. I just need to know if changing the value may result in any of the existing data in the table to be changed or may have any other unintended side effect, as this may cause problems for me.

View 8 Replies View Related

Cursor Will Increase Performance Or Not

Jun 29, 2000

"Cursor provide row-by-row level processing and it will store the result sets in 'TEMPDB' database".

(Because of this) or (By using Cursor in Triggers or Stored Procedures) the performance will increase or performance will come down?. I am thankful if I get a good reason for this?

Srinivasan

View 3 Replies View Related

Cursor Performance Question

Jan 10, 2002

Hey,

I have a stored procedure below that loops through a table and updates another table. I used to do this client side in vb but was hoping for better performance when doing everything server side.

Question;

Am I doing something wrng since the query is that slow.?
Why is cursors so bad ?

Thanks

David

ALTER PROCEDURE usp_setinternational

AS

Declare @acnt_no int

declare @cntry_code varchar(10)

declare @counter int

declare @error int

declare MyC cursor

Local

for

SELECT ACNT_NO, COUNTRY_CODE FROM INTNL_ACNT

open MyC

fetch next from MyC into @acnt_no,@cntry_code

select @counter = 1

while @@fetch_status = 0

begin

update xform2 set

address_type='m'

where acnt_no = @acnt_no

update addr set delivery='m',cntry_cde= @cntry_code

where addr_id in (select addr_id from acnt_addr where acnt_no = @acnt_no)

select @counter = @counter +1

fetch next from MyC into @acnt_no,@cntry_code

end

View 2 Replies View Related

Problem With Cursor Performance

Apr 23, 2008

hi friends i am using a cursors to fetch data row by row , but the main problem is it takes a very big time span to execute can anybody help me out to replace cursor with some other method or trick so that i can make it perform fast... see my query is like this:

--update cdr set destinationid = null

DECLARE @CDRID BIGINT;

DECLARE @CodeDestID BIGINT;

DECLARE @TempCodeDestID BIGINT;

DECLARE @CtryCode VARCHAR(20);

DECLARE @MATCH INT;

DECLARE @dialed_digits NVARCHAR(50);
DECLARE @FormattedNbr VARCHAR(50);
DECLARE @CountryCode VARCHAR(2);
DECLARE CDR_cursor CURSOR FOR SELECT CDRID, dialed_digits FROM mydata.dbo.CDR WHERE DestinationID IS NULL
OPEN CDR_cursor

FETCH NEXT FROM CDR_cursor INTO @CDRID, @dialed_digits

WHILE @@FETCH_STATUS = 0

BEGIN

SET @FormattedNbr = replace(LTRIM(RTRIM(@dialed_digits)), '"', '');

SET @CountryCode = substring(@FormattedNbr, 0, 3);

SET @CodeDestID = null;

SET @MATCH = 0;

DECLARE Dest_cursor CURSOR FOR SELECT DestinationID, Code FROM mydata.dbo.ctryCode WHERE code Like LTRIM(RTRIM(@CountryCode)) +'%' Order by code Desc

OPEN Dest_cursor

FETCH NEXT FROM Dest_cursor INTO @TempCodeDestID, @CtryCode

WHILE @@FETCH_STATUS = 0

BEGIN

IF @MATCH <> 1

BEGIN

SET @MATCH = PATINDEX(@CtryCode + '%', @FormattedNbr);

IF @MATCH = 1

BEGIN

print @TempCodeDestID

SET @CodeDestID = @TempCodeDestID

END
END

FETCH NEXT FROM Dest_cursor INTO @TempCodeDestID, @CtryCode

END

CLOSE Dest_cursor

DEALLOCATE Dest_cursor

IF @MATCH = 1

BEGIN

--Updating the maximum possible match

UPDATE mydata.dbo.CDR SET DestinationID = @CodeDestID WHERE CDRID = @CDRID

SET @CodeDestID = null;

SET @MATCH = 0;

END
FETCH NEXT FROM CDR_cursor INTO @CDRID, @dialed_digits

END

CLOSE CDR_cursor

DEALLOCATE CDR_cursor

SELECT mydata.dbo.CDR.CDRID, mydata.dbo.CDR.start_date_time, mydata.dbo.CDR.ani, mydata.dbo.CDR.dialed_digits, mydata.dbo.CDR.actual_dur, mydata.dbo.CDR.rounded_dur_secs,mydata.dbo.CDR.cost, mydata.dbo.CtryCode.Destination, mydata.dbo.CtryCode.Code,

mydata.dbo.CtryCode.Cost AS Code_Rate

FROM mydata.dbo.CDR LEFT OUTER JOIN

mydata.dbo.CtryCode ON mydata.dbo.CDR.DestinationID = mydata.dbo.CtryCode.DestinationID

please help me its urgent.....

View 3 Replies View Related

DB Engine :: Unable To Select Data From A Table Even After Providing Select Access

Aug 28, 2015

I am unable to the access on table even after providing the SELECT permission on table.

Used Query by me :

Here Test is schema ; Card is table ; User is Satish

To grant select on Table

GRANT SELECT ON TEST.Card TO satish
Even after this it is not working, So provided select on schema also.
used query : GRANT SELECT ON SCHEMA::TEST TO Satish.

View 8 Replies View Related

DB Engine :: Transactions / Sec Performance Counter

Dec 16, 2010

What exactly does the DatabasesTransactions/sec measure? The explain button says: "Number of transactions started for the database." Does this mean the number of transactions initiated during the time period or is it some kind of average of the number in progress.

The reason I am asking is that sometimes I see a spike in Transactions/sec. lasting about a half hour concurrent with high memory and CPU usage. Does this really mean that too many transactions are being started or is it a side effect of the machine being too busy to complete transactions thus leaving them pile up uncompleted.

It seems like it should be counting the number of BEGIN TRANSACTIONs statements (plus implicit ones) actually received during the time period, but I'm not having an easy time diagnosing this problem so I am trying to figure out what to rule out.

View 3 Replies View Related

DB Engine :: Missing Performance Monitor Counter

May 27, 2014

In sql server 2014 , many performance counters are missing .I need " Buffer cache hit ratio " under "sql server :buffer manager " .Is there any way to get it back or removed form sql server2014 ?

View 6 Replies View Related

Performance Tuning Using Index. How To Force The Query Engine To Use It?

Nov 14, 2007

Hello all.
I have the following table

Create Table Item(
I_AssetCode NVarChar(40) Primary Key NOT NULL,
I_Name NVarChar(160),
I_BC nvarchar(20),
I_Company nvarchar(20)
);

Create Index ind_Item_Name on Item(I_Name);
Create Index ind_Item_BC on Item(I_BC);
Create Index ind_Item_Company on Item(I_Company);

It is populated with 50 000 records.
Searching on indexed columns is fast, but I've run into the following problem:
I need to get all distinct companies in the table.
I've tried with these two queries, but they both are very slow!

1. "select I_Company from item group by I_Company " - This one takes 19 seconds

2. "select distinct(I_Company) from item" -This one takes 29 secons

When I ran them through the SQL Management Studio and checked the performance plan, I saw that the second one doesn't use index at all ! So I focused on the first...
The first one used index (it took it 15% of the time), but then it ran the "stream aggregate" which took 85% of the time !
Actully 15% of 19 seconds - about 2 seconds is pretty much enough for me. But it looks that aggregate function is run for nothing!
So is it possible to force the query engine of the SSCE not to run it, since there is actually no aggregate functions in my select clause?
According to SQL CE Books online:
Group By

"Specifies the groups (equivalence classes) that output rows are to be placed in. If aggregate functions are included in the SELECT clause <select list>, the GROUP BY clause calculates a summary value for each group."
It seems the aggregate is run every time, not only when there is an aggregate function.

Is this a bug?

Thanks in advance,
TipoMan

View 4 Replies View Related

DB Engine :: Running Performance Monitor While ETL Process Is In Progress?

Jul 21, 2015

know if running performance counters during ETL process is running will impact performance on the server, I have win 2008 r2 server with sql server 2008 r2. I want to measure performance of server while ETL is in progress.

View 4 Replies View Related

DB Engine :: Performance Tuning Temp DB Slow INSERT From VIEW

May 16, 2015

I am running A View that INSERTS into #Temp Table - On Only Certain Days the INSERT Speed into #tempDB is so slow.

Attached snapshot that shows after one minute so many few records are inserted - and it dosent happen every day somedays its very fast.

View 3 Replies View Related

SQL2005 Enterprise Database Engine Performance Features (versus Standard)

Feb 21, 2007

Can anyone comment on the engine performance difference between SQL2005 Enterprise Edition versus Standard? I'm talking generalized performance of the engine and not admin features (parallel index operations) or scaled-storage (partitioning)

( http://www.microsoft.com/sql/editions/enterprise/comparison.mspx )

The marketing literature makes note of two things:

Enterprise can use more then 4 processors

Enhanced read-ahead and scan (super scan)
(note: I cannot find anything about this 'feature')
One un-noted Feature:

only Enterprise supports 'lock pages in memory'

We are in the process of migrating from SQL2000 to SQL2005 in an OLTP environment. Based on the marketing literature; I would have chosen SQL2005-standard. But based on our limited testing, we are seeing some strange differences.

Query Performance

With MaxDOP=1 and using a large batch query (select top 1500000); SQL2005-Enterprise is twice as fast as SQL2005-Standard.

(Note: this difference persists regardless of lock-pages-in-memory setting)

CPU Utilization

In addition, taskmgr shows that SQL2005-Enterprise uses a single processor at ~90%. While SQL2005-Standard shows a single processor at ~20%.

Lock Behavior

We are also seeing lock-behavior differences. A single DML statement that attempts to modify ~5000 rows will cause Table-locks on SQL2005-Standard but obtain normal row-locks on SQL2005-Enterprise.

These empirical differences make me wonder if the engine codebase is fundamentally different between the two?

Any insight would be appreciated.

View 4 Replies View Related

Multiple Tables Select Performance - SQL 2005 - Should It Take 90 Seconds For A Select?

Dec 4, 2007

I have a problem where my users complain that a select statement takes too long, at 90 seconds, to read 120 records out of a database.
The select statement reads from 9 tables three of which contain 1000000 records, the others contain between 100 and 250000 records.
I have checked that each column in the joins are indexed - they are (but some of them are clustered indexes, not unclustered).
I have run the SQL Profiler trace from the run of the query through the "Database Engine Tuning Advisor". That just suggested two statistics items which I added (no benefit) and two indexes for tables that are not involved at all in the query (I didn't add these).
I also ran the query through the Query window in SSMS with "Include Actual Execution Plan" enabled. This showed that all the execution time was being taken up by searches of the clustered indexes.
I have tried running the select with just three tables involved, and it completes fast. I added a fourth and it took 7 seconds. However there was no WHERE clause for the fourth table, so I got a cartesian product which might have explained the problem.
So my question is: Is it normal for such a type of read query to take 90 seconds to complete?
Is there anything I could do to speed it up.
Any other thoughts?
Thanks

View 7 Replies View Related

Cursor Vs. Select

Jul 23, 2005

buddies,situation: a processing must take place on every row of a table, andoutput results to another table, that can't be done via an insertinto..select query (let's assume that it's not possible for now).There're 2 solutions I have in mind:1) open a cursor and cycle through each row (The table can have up to1M rows)2) create a clustered index (i.e on an identity column) then have aloop like:declare @i int, @rows int,@col1 varchar(20), @col2 varchar(20),... @coln varchar(20),@outval1 varchar(20),... -- output valuesselect @i=1, @rows = max(xid) from tblname -- xid is clustered indexedwhile (@i<=@rows)beginselect @col1 = col1, @col2 = col2,...@coln = colnfrom tblnamewhere xid = i-- do the processing on the variables-- then insert results to another tableset @i = @i+1endI'd like to know your ideas of which one would be more efficient. Anyother solutions are much appreciatedthanks,Tamy

View 4 Replies View Related

Select Output With Cursor

May 8, 2002

In my previous post I asked how to do the bottom question. I got a response to use a cursor, now I made an attempt to use a cursor but I still get the same response. Any help will be greatly appreciated.

--CURRENT OUTPUT--

empID Rank Skills
------- ---- --------------------------------------------------
2924 1 Create Documents
2924 2 Mail Merge
2924 3 Create Header and footer
2924 3 Create Spreadsheet
2924 3 Joining Tables in a Query

--DESIRED OUTPUT--

empID Rank Skills
------ ---- ------------
2924 1 Create Documents
2924 2 Mail Merge
2924 3 Create Header and footer, Joining Tables in a Query, Create Spreadsheet

--Here is the cursor script.--

Declare @skills varchar(255),@skills2 varchar(255),@message varchar(255),@empID varchar(255), @Rank varchar(255)
DECLARE emp_skills CURSOR For
select C.empID, B.Rank,B.Text1 as Skills from tbl_survey_rank B , tbl_survey_valueID C
where PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and C.empID = '2924'and
(B.Rank ='1'or B.Rank ='2' or B.Rank ='3') or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and
B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and
B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and
B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and
B.Rank ='3'and C.empID = '2924'

DECLARE emp_skills2 CURSOR For
select B.Text1 as Skills from tbl_survey_rank B , tbl_survey_valueID C
where PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and C.empID = '2924'and
(B.Rank ='1'or B.Rank ='2' or B.Rank ='3') or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and
B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and
B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and
B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and
B.Rank ='3'and C.empID = '2924'
OPEN emp_skills
OPEN emp_skills2
FETCH NEXT FROM emp_skills into @empID, @Rank, @skills
FETCH NEXT FROM emp_skills2 into @skills2
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @message = @skills2
FETCH NEXT FROM emp_skills2 into @skills2
Print @empID + ' '+ @Rank + ' ' + @message

FETCH NEXT FROM emp_skills into @empID, @Rank, @skills

End
CLOSE emp_skills
DEALLOCATE emp_skills
CLOSE emp_skills2
DEALLOCATE emp_skills2

--Previous Post--

Another question for all you SQL experts, I have a lot of them. I am trying to select from a table wher some conditions need to be met based on an employee ID. What I am doing is when the rank is a 1,2, or 3 I pick up the text description of that rank. Can I make it so that I get the ID only once and all the text descriptions are on the same line. Here is the sql script along with my current output and my desired output.

--SQL SCRIPT__

select C.empID, B.Rank,B.Text1 as Skills from tbl_survey_rank B , tbl_survey_valueID C
where PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and C.empID = '2924'and
(B.Rank ='1'or B.Rank ='2' or B.Rank ='3')

--CURRENT OUTPUT--

empID Rank Skills
------- ---- --------------------------------------------------
2924 1 Create Documents
2924 2 Mail Merge
2924 3 Create Header and footer
2924 3 Create Spreadsheet
2924 3 Joining Tables in a Query

--DESIRED OUTPUT--

empID Rank Skills
------ ---- ------------
2924 1 Create Documents
2924 2 Mail Merge
2924 3 Create Header and footer, Joining Tables in a Query, Create Spreadsheet

View 2 Replies View Related

Select Statement In Cursor

Mar 26, 2000

Hi...

I have a stored procedure that rertrieves data from an sql database
and sends out a mail to each receipient who meets the criteria

I am using SQL mail.

I dynamically generate the where clause for my sql query based on criteria taken
from other stored procedures and store it in a varchar variable
called @sqlquery

When i have the following code to run my cursor

DECLARE overdue3 CURSOR
LOCAL FORWARD_ONLY
FOR SELECT DISTINCT Events.E_Name, Events.E_SDate, Events.E_City,
Events.E_ID FROM Events, IndustryEvents + @sqlquery2
OPEN overdue3

I get an error message at the '+' sign
which says, cannot use empty object or column names, use a single
space if necessary.

What should i do. i have tested the variable @sqlquery and it is
definately not blank. There is no bracket error or anything.

Please help!!!

Thanks much indeed

Ramesh

View 1 Replies View Related

Using Select Statement Instead Of Cursor

May 20, 2003

Hi All, Can anyone please help?

TableA has data as below:

ssn sex dob rel_code
111111111 m 19500403 m
111111111 f 19570908 w
111111111 f 19770804 d
111111111 f 19801203 d
111111111 f 19869712 d
111111111 m 19870907 s
111111111 m 19901211 s

I have to convert the rel_code into a specific manner so the data will look as below in TableB:

ssn sex dob rel_code
111111111 m 19500403 01
111111111 f 19570908 02
111111111 f 19770804 20
111111111 f 19801203 21 111111111 f 19869712 22
111111111 m 19870907 30
111111111 m 19901211 31

Member's rel_code = 01
spouse's rel_code = 02
daughter's rel_code starts from 20 with the oldest and increments by 1.
Son's rel_code starts from 30 and increments by 1 from oldest to the youngest.

I know You can write a Sp with cursor and do this, but I would like to know if you can accomplish the same thing by a select or case or something else instead of a cursor.

Thanks in advance.

Jannat.

View 6 Replies View Related

Dynamic Select For CURSOR

May 14, 2008

Hi all

I am trying to do dynamic Select for Cursor. The dynamic would be like this:
IF CONDITION1 IS TRUE:
SELECT CustomerTenderID, CustomerSiteID, ContractPeriod, SupplierID
FROM dbo.tnd_TenderSiteRateConsumptionView
WHERE CustomerTenderID = @CustomerTenderID
IF CONDITION2 IS TRUE:
SELECT CustomerTenderID, CustomerSiteID, ContractPeriod, SupplierID
FROM dbo.tnd_TenderSiteRateConsumptionView
WHERE CustomerTenderID = @CustomerTenderID AND
CustomerSiteID = @CustomerSiteID

etc etc

Here's the cursor

DECLARE RateList CURSOR FOR
SELECT CustomerTenderID, CustomerSiteID, ContractPeriod, SupplierID
FROM dbo.tnd_TenderSiteRateConsumptionView
WHERE (BASED ON CONDITION)
ORDER BY CustomerTenderID,
CustomerSiteID,
SupplierID,
ContractPeriod

OPEN RateList
FETCH NEXT FROM RateList
INTO@CustomerTenderID, @ReturnedCustomerSiteID, @ReturnedContractPeriod, @ReturnedSupplierID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @rowNum = @rowNum + 1

-- DO SOME FUNKY STUFF

FETCH NEXT
FROM RateList
INTO@CustomerTenderID, @ReturnedCustomerSiteID, @ReturnedContractPeriod, @ReturnedSupplierID

View 4 Replies View Related

CURSOR Select Clause

Oct 11, 2006

I need to dynamically construct the field order of a cursor based on fixed labels from another table, but when I put that resulting query I receive the error:

Server: Msg 16924, Level 16, State 1, Line 78
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.

I have 6 fields defined in the cursor select, and 6 parameters in the fetch. The results of running the @sql portion returns valid data. Should this be possible to define a parameter containing the select clause of the cursor?

select colnum, coldesc, colname into #ae_defs from ae_adefs
select @Sql = (select colname from #ae_defs where coldesc = 'PATIENT NAME') +
', ' +
(select colname from #ae_defs where coldesc = 'PATIENT NUMBER') +
', ' +
(select colname from #ae_defs where coldesc = 'ACCOUNT NUMBER') +
', ' +
(select colname from #ae_defs where coldesc = 'VISIT DATE') +
', ' +
(select colname from #ae_defs where coldesc = 'VISIT TYPE') +
', DocID from ae_dtl1'

DECLARE myCursor CURSOR FOR
Select @SQL

OPEN myCursor
print @@Cursor_rows
FETCH NEXT FROM myCursor into @var1, @var2, @var3, @var4, @var5, @DocID

View 2 Replies View Related

Cursor Select And Variables

Oct 23, 2006

I have problems to place my variable into the select statement.

DECLARE @DB_NAME varchar(64)
DECLARE MR_ReqPro_DB_cursor CURSOR FOR
select name from dbo.sysdatabases where name like '%MR_req%'
OPEN MR_ReqPro_DB_cursor
FETCH NEXT FROM MR_ReqPro_DB_cursor
INTO @DB_NAME

WHILE @@FETCH_STATUS = 0
BEGIN
print @DB_NAME; --works fine

Select NAME, FILEDIRECTORY FROM @DB_NAME.MR_ReqPro.RQDOCUMENTS WHERE (FILEDIRECTORY LIKE '%\%');

FETCH NEXT FROM MR_ReqPro_DB_cursor INTO @DB_NAME
END
CLOSE MR_ReqPro_DB_cursor
DEALLOCATE MR_ReqPro_DB_cursor

GO

How could i use a variable like @DB_Name in my select ?

View 1 Replies View Related

Adding Variable To SELECT For CURSOR

Oct 13, 2003

I'm trying to build a select statement for a CURSOR where part of the SQL statement is built using a variable.
The following fails to parse:

Declare Cursor1 Cursor
For
'select table_name from ' + @database + '.Information_Schema.Tables Where Table_Type = ''Base Table'' order by Table_Name'
Open cursor1

That doesn't work, I've also tried using an Execute() statement, no luck there either. Any ideas or suggestions are greatly appreciated.

View 7 Replies View Related

Variable As Column In Cursor Select

Jun 7, 2004

I can't seem to get a cursor to work when I'm passing in a variable for a column name of the select statement. For example:

declare @col varchar(50)

set @col = 'Temperature'

declare notifycurs cursor scroll for
select @col from Table

Obviously this won't work correctly (since the result will simply be 'Temperature' instead of the actual float value for temperature). I tried to use quotes for the entire statement with an EXEC
(ie. exec('select '+@col+' from Table' )
but that gave me an error.

Is there a way to pass in a variable for a column name for a curor select statement????

View 7 Replies View Related

How To Specify Which Database To Use For A Select Statement Within A Cursor?

Nov 9, 2007

Hi everyone,

I have been trying to perform the following task:

Using the sys.databases & sys.sysindexes views to display all the columns with a clustered index for all tables and all databases in a given server. So the end result will have 3 columns:

Database name
Table name
Column name from that table with a clustered index

I have already created the following script which displays all the databases for a given server:

declare @DBname nvarchar(128)
declare testCursorForDB cursor
for
select name from sys.databases with (nolock)
where name not in ('master','tempdb','model','msdb')
order by name

open testCursorForDB
fetch next from testCursorForDB
into @DBname

while @@fetch_status = 0
begin
print @DBname
fetch next from testCursorForDB
into @DBname
end

close testCursorForDB
deallocate testCursorForDB

I also have created the following query which will display all the table and column names which have a clustered index for a given database:

select object_name(i.id) as TableName,

i.name as IndexName
from sys.sysindexes as i with (nolock)
where i.indid = '1'

However, what I need help/advice on is how do I combine these two together into one working script (either using nested cursors or a better way). In other words, how can I specify which database to use (ie. using the "use database_name") so that my query above will be applied to each database found within the cursor.

Any help is greatly appreciated

Thanks!

View 7 Replies View Related

DB Engine :: SIU Locks During Select

Dec 28, 2007

We have found deadlocks in our application. Deadlocks occure between SELECT and UPDATE. I get deadlock graph using profiler and find that SELECT makes SIU lock. Below you'll find SELECT statement:

select t1.*
    from MyTable t1
    --self join on field1 and field2
        left outer join
            (select field1, field2

[Code] ....

Why SIU lock is set?

View 9 Replies View Related

Changing Output Of Select

Feb 18, 2015

View 1 Replies View Related

SELECT DISTINCT Will Always Result In A Static Cursor

Jul 31, 2007

an example for the pb
1)First i have created a dynamic cursor :

DECLARE authors_cursor CURSOR DYNAMIC
FOR Select DISTINCT LOCATION_EN AS "0Location" from am_location WHERE LOCATION_ID = 7
OPEN authors_cursor
FETCH first FROM authors_cursor

2)The result for this cursor is for expamle 'USA'.

3) If now i do an update on that location with a new value 'USA1'

update am_location set location_en = 'USA1' WHERE LOCATION_ID = 7

4)now if i fetch the cursor , i''ll get the old value (USA) not (USA1).

If i remove DISTINCT from the cursor declaration , the process works fine .

View 10 Replies View Related

SELECT DISTINCT Will Always Result In A Static Cursor

Jul 31, 2007

An example for my pb
1) Created a dynamic cursor :
DECLARE cursor_teste CURSOR DYNAMIC
FOR Select DISTINCT name from table WHERE ID = 1
OPEN cursor_teste
FETCH first FROM cursor_teste
2)The result for this cursor is for example 'teste'.
3) If now i do an update on that name with a new value 'teste1'
than if i fetch the cursor , i''ll get the old value (teste) .

any idea how to make a select distinct result in a dynamic Cursor?

View 7 Replies View Related

How To Put Condition In Select Statement To Write A Cursor

Mar 29, 2008

col1 col2 col3 col4
36930.60 145 N . 00
17618.43 190 N . 00
6259.20 115 N .00
8175.45 19 N .00
18022.54 212 N .00
111.07 212 B .00
13393.05 67 N .00
In above 4 col
if col3 value is B then cursor has to fectch appropriate value from col4.
if col3 value is N then cursor has to fectch appropriate value from col1.
here col2 values are unique.

Can any one reply for this..............

View 3 Replies View Related

Changing The SQLDatasource SELECT For A Gridview

May 19, 2008

I cant seen to change the Select command for a SQL Datasourcetry #1            SqlDataSourceProfilesThatMatch.SelectCommand = strSQLForSearch            SqlDataSourceProfilesThatMatch.SelectParameters("ProfileID").DefaultValue = pProfileID            SqlDataSourceProfilesThatMatch.SelectParameters("LoggedInUsersZipcode").DefaultValue = pUsersZipCode            SqlDataSourceProfilesThatMatch.SelectParameters("ZipDistance").DefaultValue = pDistance
            NewProfilesThatMatchGridView.DataBind()
try #2            SqlDataSourceProfilesToBeMatched.SelectParameters.Clear()            SqlDataSourceProfilesThatMatch.SelectCommand = strSQLForSearch            SqlDataSourceProfilesThatMatch.SelectParameters.Add("ProfileID", pProfileID)            SqlDataSourceProfilesThatMatch.SelectParameters.Add("LoggedInUsersZipcode", pUsersZipCode)            SqlDataSourceProfilesThatMatch.SelectParameters.Add("ZipDistance", pDistance)
            NewProfilesThatMatchGridView.DataBind()
No errors but no rows show in the gridview. If I debug and get the value strSQLForSearch and paste it into a new SQL query window I get results.
Any ideas????
Thanks

View 3 Replies View Related

Changing Data Format On Select

Jul 20, 2005

Hey all,I have a basic table that looks something like this.CREATE TABLE MyTable(ID INT IDENTITY PRIMARY KEY,Company_ID INT NOT NULL,Round VARCHAR(50) NOT NULL,Details VARCHAR(250) NOT NULL)It has a few rows of data that look like this:Identity Company_ID Round Details--------------------------------------------1 5 A Blah, blah.2 5 B Generic data, blah blah.3 5 WERT More generic blah blah.Now what i'm trying to do during my select statement is select all the rowsthat belong to company_id 5 but if any of the rows round value contains thetext "WERT" convert that text into just a "--" for presentation purposes,but still select that row. I can't seem to figure out how i would transformthe text in the select statement? My immediate thought was substring /replace but i would need to combine it with an if else statement which i'veno idea how to make work in a select (sub-query maybe?) statement. Is thispossible? Perhaps i'm stuck iterating through the returned data within theclient application before presenting?Any help, as always, would be greatly appreciated.Muhd

View 5 Replies View Related

Moving Average Using Select Statement Or Cursor Based?

Jul 30, 2007

ID DATE(dd/mm/yy) TYPE QTYIN COST_IN_AMT COST_OUT_AMT(MOVING AVERAGE)
1 01/01/2007 PURCHASE 10 1000
2 01/01/2007 PURCHAES 5 1100
3 01/01/2007 SALES -5 *TobeCalculated
4 02/01/2007 Purchase 20 9000
5 02/01/2007 SALES -10 *TobeCalculated
5 02/01/2007 purchase 50 8000
6 03/01/2007 Sales -10 *TobeCalculate
7 01/01/2007 Purchase 20 12000

I have a table when user add new sales or puchase will be added to this table ITEM_TXNS. The above date is part of the table for a ProductID . (The field is removed here)
In order to calculate the balance amount using moving average, I must calculated the cost_out_amt first on the fly.
When user add new sales I also need to determine the cost/unit for a product id using moving average. The problem is I can not just use sum, because i need to determine cost_out_amt for each sales first which will be calculated on the fly.
The reason i dont store the cost_out_amt (instead calculate on the fly) because User could Edit the previous sales/purchase txn or Insert new sales for a previous date. Example THe record with ID 9. By Adding this txn with ID 9, would cause all the cost_out_amt will be incorrect (Using moving Average) if i store the cost_amout_out on entrying txn and need to be recalculated.
Instead I just want to calculate on the fly and able to determine the cost avr for a specific point of time.
Should I just use Cursor and loop all the record and calculate the cost or maybe I can just use on Select Statement?

View 20 Replies View Related

Combing In A Cursor, A Select Statement With The WHERE Clause Stored In A Variable

Mar 28, 2000

Hi
I am ramesh here from go-events.com
I am using sql mail to send out emails to my mailing list

I have difficulty combining a select statement with a where clause stored in a variable inside a cursor

The users select the mail content and frequency of delivery and i deliver the mail

I use lots of queries and a stored procedure to retrieve thier preferences. In the end i use a cursor to send out mails to each of them.

Because my query is dynamic, the where clause of my select statement is stored in a variable. I have the following code
that does not work

For example

DECLARE overdue3 CURSOR
LOCAL FORWARD_ONLY
FOR SELECT DISTINCT Events.E_Name, Events.E_SDate, Events.E_City, Events.E_ID FROM Events, IndustryEvents + @sqlquery2
OPEN overdue3

I get an error message at the '+' sign
which says, cannot use empty object or column names, use a single
space if necessary

How do I combine the select statement with the where clause?

Help me...I need help urgently

View 1 Replies View Related