How To Iterate Through Table Variable Without Using Cursor

Iterate A Variable
Hi Guys,

I need to design this SSIS migration package to migrate data.

In a Execute SQL Task, I need to get a full result set and assign  it to an variable, such as v_collection;

The SQL statement can be as simple as : select primary_key from a_table;

After that I have a ForEach Loop container, that consumes the variable, and assign each iteration to another variable, such as v_iter, the type of v_iter is DT_I4, because the primary key is a long integer

The problem is: in Oracle, the primary key is NUMERIC(10,0) and in SQL it is int.

I can not assign a NUMERIC(10,0) to an variable of DT_I4, but if I change the variable definition to DT_NUMERIC, then it would not work for SQL.

Anyone knows how to fix this?

I was thinking to add a Script Task between the Execute SQL Task and the Foreach Loop, and somehow access the collection variable, v_collection, and manually convert the value to a DT_IT, then repopulate another collection variable, v_collection_I4 with Integers, and force the Foreach Loop to use v_collection_I4 collection variable.

Will this work? If yes, how? :)

 

Thanks a lot!

Wenbiao

View Replies !

---

Possible To Fetch Next From Cursor Into Table Variable?
 

Hello,
 
I have searched the net for an answer but could not find one. When I declare a table variable
and then try to insert fetched row into the table variable like:
 



Code Snippet
declare @table table (col1 nvarchar(50), col2 nvarchar(50))
declare curs for
select * from sometable
open curs
fetch next from curs into @table
 
 


 
 
it does not work. any help would be great.
 
thnx

View Replies !

---

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

View Replies !

---

T-SQL Cursor Variable..
Hello All,Is there a T-SQL equivalent of the PL/SQL cursor rowtype datatype ?Thanks in advance

View Replies !

---

Cursor/variable Help
I'm not sure about this one so if someone could help I'd appreciate this.

As shown below I've declared a variable name1 to be used in a while statement substituting for an object name in a select statement (2000 SP3a)
and throwing the shown error. Are variables allowed to be used to substitute for object names or is there another problem? Thanks.

View Replies !

---

How To Use Variable In A Cursor
if i have the cursor

cursor:

cursor for
select name
from sysobjects
where type='P' AND category='0'

and wants a variable before sysobjects which equals a table name, so itll be:

cursor for
select name
from @variable..sysobjects
where type='P' AND category='0'

how do i include a variable within a cursor statement

View Replies !

---

Bind Variable In CURSOR
SQL Server 2000 SP4 with AWE hotfix. Windows 2003 SP1.I have a stored procedure which is not working the way I think itshould be.I have a CURSOR which has a variable in the WHERE clause:DECLARE get_tabs CURSOR local fast_forward FORSELECT distinct tablename, id, shcontig1dt, shcontig2dtFROM db_indWHERE dbname = @dbnameORDER BY tablenameIt won't return anything, even when I verify that @dbname has a valueand if I run the query in Query Analyzer with the value, it returnsrows:SELECT distinct tablename, id, shcontig1dt, shcontig2dtFROM db_indWHERE dbname = 'Archive'ORDER BY tablenameDB_Rpt_Fragmentation11575791622006-03-29 09:52:11.7772006-03-2909:52:11.823DtsAdtStdArchive_DataSourceType5175768822006-03-2909:52:11.8702006-03-29 09:52:11.887DtsADTstdArchiveNotUsed3575763122006-03-29 09:52:11.8872006-03-2909:52:12.103I've taken out most of the guts for simplicity, but here's what I'vegot:--CREATE TABLE dbo.db_ind--(--db_ind_tkintIDENTITY,-- id int NULL,-- tablename sysname NOT NULL,-- indid int NULL,-- indexname sysname NOT NULL,-- shcontig1dt datetime NULL,-- defragdt datetime NULL,-- shcontig2dt datetime NULL,-- reindexdt datetime NULL--)ALTER PROCEDURE IDR(@hours int)AS--SET NOCOUNT ON--SET ANSI_WARNINGS OFFDECLARE @tabname varchar(100),@indname varchar(100),@dbname varchar(50),@vsql varchar(1000),@v_hours varchar(4),@shcontig1dtdatetime,@shcontig2dtdatetime,@defragdtdatetime,@reindexdtdatetime,@idint,@indidint,@rundbcursorint,@runtabcursorint,@runindcursorintDECLARE get_dbs CURSOR local fast_forward FORSELECT dbnameFROM db_jobsWHERE idrdate < getdate() - 4or idrdate is nullORDER BY dbnameDECLARE get_tabs CURSOR local fast_forward FORSELECT distinct tablename, id, shcontig1dt, shcontig2dtFROM db_indWHERE dbname = @dbnameORDER BY tablenameDECLARE get_inds CURSOR local fast_forward FORSELECT indid, indexname, defragdt, reindexdtFROM db_indWHERE dbname = @dbnameAND tablename = @tabnameORDER BY indexnameOPEN get_dbsFETCH NEXT FROM get_dbsINTO @dbnameIF @@FETCH_STATUS = 0SELECT @rundbcursor = 1ELSESELECT @rundbcursor = 0SELECT @v_hours = CONVERT(varchar,@hours)--================================================== ================================================== =====--================================================== ================================================== =====--================================================== ================================================== =====WHILE @rundbcursor = 1BEGIN -- db whilePRINT '============================='PRINT @dbnamePRINT '============================='--================================================== ================================================== =====--================================================== ================================================== =====OPEN get_tabsFETCH NEXT FROM get_tabsINTO @tabname, @id, @shcontig1dt, @shcontig2dtIF @@FETCH_STATUS = 0BEGINPRINT 'table: ' + @tabnameSELECT @runtabcursor = 1endELSEBEGINPRINT 'not getting any tables! '-- <<<<< THIS IS WHERE IT HITSSELECT @runtabcursor = 0endWHILE @runtabcursor = 1BEGINPRINT @dbnamePRINT @tabname--================================================== ================================================== =====OPEN get_indsFETCH NEXT FROM get_indsINTO @indid, @indname, @defragdt, @reindexdtIF @@FETCH_STATUS = 0SELECT @runindcursor = 1ELSESELECT @runindcursor = 0WHILE @runindcursor = 1BEGINPRINT 'Index:' + @dbname + '.' + @tabname + '.' + @indnameFETCH NEXT FROM get_indsINTO @indid, @indname, @defragdt, @reindexdtIF @@FETCH_STATUS = 0SELECT @runindcursor = 1ELSESELECT @runindcursor = 0END-- 1st loop through indexesCLOSE get_inds--================================================== ================================================== =====--==========PRINT 'db.tab: ' + @dbname + '.' + @tabname--==========--================================================== ================================================== =====OPEN get_indsFETCH NEXT FROM get_indsINTO @indid, @indname, @defragdt, @reindexdtIF @@FETCH_STATUS = 0SELECT @runindcursor = 1ELSESELECT @runindcursor = 0WHILE @runindcursor = 1BEGINPRINT 'dbname: ' + @dbnamePRINT 'tabname: ' + @tabnamePRINT 'indname: ' + @indnameFETCH NEXT FROM get_indsINTO @indid, @indname, @defragdt, @reindexdtIF @@FETCH_STATUS = 0SELECT @runindcursor = 1ELSESELECT @runindcursor = 0END -- 2nd loop through indexesCLOSE get_inds--================================================== ================================================== =====FETCH NEXT FROM get_tabsINTO @tabname, @id, @shcontig1dt, @shcontig2dtIF @@FETCH_STATUS = 0SELECT @runtabcursor = 1ELSESELECT @runtabcursor = 0END-- loop through tablesCLOSE get_tabs--================================================== ================================================== =====--================================================== ================================================== =====PRINT 'Index Maintenence complete. Job report in[DB_Rpt_Fragmentation]'PRINT ''FETCH NEXT FROM get_dbsINTO @dbnameIF @@FETCH_STATUS = 0SELECT @rundbcursor = 1ELSESELECT @rundbcursor = 0END -- loop through databasesCLOSE get_dbsdeallocate get_dbsdeallocate get_tabsdeallocate get_inds--================================================== ================================================== =====--================================================== ================================================== =====--================================================== ================================================== =====GOAnd this is what I'm getting:=============================Archive=============================(0 row(s) affected)not getting any tables!Index Maintenence complete. Job report in [DB_Rpt_Fragmentation]......etc.Am I missing something obvious?Thank you for any help you can provide!!

View Replies !

---

Set Value For Variable In A Declared Cursor
Hi,

I have a problem on setting the value for the variable in a declared cursor. Below is my example, I have declared the cursor c1 once at the top in a stored procedure and open it many times in a loop by setting the variable @str_var to different values. It seems the variable cannot be set after the cursor declared. Please advise how can I solve this issue.

------------------------------------------------------------------------
DECLARE @str_var VARCHAR(10)
DECLARE @field_val VARCHAR(10)

DECLARE c1 CURSOR LOCAL FOR
SELECT field1 FROM tableA WHERE field1 = @str_var


WHILE (Sometime TRUE)
BEGIN

....

SET @str_var = 'set to some values, eg. ABC123, XYZ123'

OPEN c1

FETCH c1 INTO @field_val

WHILE (@@fetch_status != -1)
BEGIN

PRINT @field_val
...

FETCH c1 INTO @field_val
END

CLOSE c1

END

DEALLOCATE c1

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

Thanks a lots,
Vincent

View Replies !

---

Using Cursor Variable As A Tablename
I am currently using a cursor to scroll through sysobjects to extract table names and then extracting relevant column names from syscolumns.

I then need to run the following script:

declare Detail_Cursor cursor for
select @colname, max(len(@colname)) from @table

The message I receive is "must declare variable @table".

Immediately prior to this script I printed out the result of @table which works fine so the variable obviously is declared and populated.

Can anyone let me know what I'm doing wrong or how I can get the same result.

Thanks

View Replies !

---

Cursor Variable Problem
My code below is supposed to pull the destination and then work out 2 calculations per month on each destination. The results returns the string '@destination' instead of the value from the table and also the month value is returned completely wrong. My main problem though is the getting the correct destination to return. If anybody has any pointers I would be very grateful. This is driving me mad!!
Thanks a lot in advance

Here some sample code:


DECLARE @Destination char(50)
DECLARE AVGCallsToDest INSENSITIVE scroll CURSOR

FOR
SELECT destination from table1

OPEN AVGCallsToDest
FETCH First FROM AVGCallsToDest
INTO @Destination

WHILE (@@FETCH_STATUS = 0 )
BEGIN

exec("Insert into NewTable(Card, [Month], NumberOfCalls, AverageLength, Destination)
select 'Phone Card' as 'Card', DATEPART(mm,adetdate) as 'Month',count(*) as 'NumberOfCalls',
avg(bdur)/60 as 'AverageLength', "" +@Destination+ "" as 'Destination'
from table1 (nolock)
whereDATEPART(mm,adetdate) = 5 and
DATEPART(yyyy,adetdate) = DATEPART(yyyy,getdate()-1)
group by DATEPART(mm,adetdate),bdur
order by Month")


FETCHNext FROM AVGCallsToDest
INTO@Destination
End
CLOSE AVGCallsToDest
DEALLOCATE AVGCallsToDest

View Replies !

---

Variable As Column In Cursor Select
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 Replies !

---

Variable As Field Name In CURSOR FOR UPDATE
I'm trying something like:

UPDATE tbl SET @varFieldName = @varValue

The procedure runs, and when I PRINT @varFieldName, it looks fine, but the table isn't getting updated, and no errors, wierd.

I have the CURSOR open for update, but I didn't list the field names, that shouldn't be a problem, as all fields should be updateable then.

To get the field name, I :

SET @varFieldName = 'SomeChars' + LTRIM(STR(asmallint)) + 'SomeMoreChars'

Thanks,
Carl

View Replies !

---

Adding Variable To SELECT For CURSOR
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 Replies !

---

Cursor Declared With Variable In Where Clause
When I execute next query on sqlserver 6.5 nested in stored procedure I can see that 'open testCursor' selected rows using new value of @var. When I execute query on sqlserver 7.0 I can see that 'open testCursor' selected rows using value of @var before 'declare ... cursor'. Is there any way to force sqlserver 7.0 to proccess cursor like it did it before.

select @var = oldValue

declare testCursor cursor
for select someColumns
from someTable
where someColumn = @var

select @var = newValue

open testCursor

fetch next from testCursor into @someColumns

Thank's in advance.

Mirko.

View Replies !

---

How To Read The Rows In A Cursor Variable
Hi,

I have a dynamic query that returns its values in a cursor variable.
How do I read each row from this cursor in a loop ?
Eg.:
use AdventureWorks
go
DECLARE @sqlnvarchar(4000),
@paramsnvarchar(4000),
@tables_cursorcursor,
@db_namenvarchar(50),
@table_namenvarchar(4000),
@schema_namenvarchar(50);

set @db_name = 'AdventureWorks';
set @schema_name = 'Production';
set @table_name = 'BillOfMaterials, Product';
set @sql =
' select a.name table_name ' +
' from ' + @db_name + '.sys.tables a join ' + @db_name + '.sys.schemas b ' +
' on (a.schema_id = b.schema_id) ' +
' where b.name= @schema_name1 ' +
' and @table_name1 is null ' +
' order by 1; '
SELECT @params = N' @table_name1 nvarchar(3000) ,' +
N' @schema_name1 nvarchar(100) ,' +
N' @cursor cursor output'

EXEC sp_executesql @sql, @params, @table_name,@schema_name , @tables_cursor OUTPUT

View Replies !

---

Storing The Result Into A Variable W/o Cursor
Hello All,

A select statement returns one column with more than one rows.Is there a way to store it in sql Variable without using a Cursor.

Thank you in advance

View Replies !

---

Variable Assignment In Cursor Declaration
Hi,

here is the code segment below;
...
DECLARE find_dates CURSOR FOR
SELECT @SQL = 'select DISTINC(Dates) from ['+@name+'].dbo.['+@t_name+'] order by [Dates] ASC'
EXEC (@SQL)

but it gives error, variable assignment is not allowed in a cursor declaration. I need to use dynamic SQL , the only way to access all the dbs and their tables inside. Please help.

thanks

View Replies !

---

Join Cursor With Table Outside Of Cursor
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?

View Replies !

---

How To Include Variable In CURSOR SQL Filter Clause?
After trying every way I could come up with I can't get a filter clauseto work with a passed variable ...I have a cursor that pulls a filter string from a table (works OK),then I want to use that filter in a second cursor, but can't get thesyntax ...@bakfilter is equal to "MISV2_db_%.BAK" before I try to open and fetchfrom the second cursor. Here is the cursor declaration:DECLARE curFiles CURSOR FORSELECT FileName, FileDateFROM DataFileWHERE (((Active)=1) AND ((FileName) LIKE '@bak_filter'))ORDER BY FileDate DESCWhat do I need to do to get it to use the string contained in@bak_filter?Thanks in advance, Jim

View Replies !

---

Output Of Select Stament Into Variable Within Cursor
Within a cursor that I am building I would like to execute a select statement built from a varchar variable as such:

SELECT @BuildDBPageUsed = 'SELECT sum(reserved) FROM ' + @DatabaseName + '.dbo.sysindexes WHERE segment <> 2'

Next I execute the statement, which is now in the variable @BuildDBPageUsed. Such as:

EXEC (@BuildDBPageUsed)

The resulting output I need to set to another variable @DBPageUsed which is integer. I have been unsuccessful in finding the correct set of commands to do this. How should I build the command to input the results of the EXEC (@BuildDBPageUsed) into the integer variable @DBPageUsed?

*Thanks* for any help in this matter.
Brad

View Replies !

---

In Cursor: Help To Perform Proper Comparison Using Variable
Hi All,

I failed to find record when using variable in cursor in WHERE clause:

ID is uniqueidentifier field in the table
DECLARE @EncounterID uniqueidentifier
........
WHERE ID = @EncounterID -> this does not work, though @EncounterID is set properly and can see its value in debugger

WHERE ID = 'E3AE2C5B-06F2-4A3C-A3A4-7D6CC43DE012' -> this works fine and record found

Tried to CAST(@EncounterID as char(40)) but still no luck.

I would greatly appreciate any advise hot to make it working.

Thank you very much in advance

Roman

View Replies !

---

Question About Assigning Cursor Variable Data Types...
Hi,
I'm just curious about something...when assigning a datatype to a cursor variable in ORACLE, you can use the keyword TYPE to automatically grap the datatype from the associated column (or use ROWTYPE to create a cursor variable to represent an entire row in a table). It's so much easier and if the datatype of one of the cursor columns is altered, the associated cursor variable assumes the new datatype.

Is there a way to do this in SQL Server 7.0 ?

many thanks :)

View Replies !

---

Combing In A Cursor, A Select Statement With The WHERE Clause Stored In A Variable
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 Replies !

---

Dynamic Query, Local Cursor Variable And Global Cursors
Hi all.



I am stuck in a bit of a conundrum for quite a while now, and I hope someone here will help me figure this one out.



So, first things first: let me explain what I need to do. I am
designing a web application that will allow users to consult info
available in a SQL2000 database. The user will enter the search
criterea, and hopefully the web page will show matching results.



The problem is the results shown aren't available per se in the DB, I
need to process the data a bit. I decided to do so on the SQL Server
side, though the use of cursors. So, when a user defines his search
criteria, I run a stored procedure that begins by building a dynamic
sql query and creating a cursor for it. I used a global cursor in order
to do so. It looked something like this:



SET @sqlQuery = ... (build the dinamic sql query)

SET @cursorQuery = 'DECLARE myCursor CURSOR GLOBAL FAST_FORWARD FOR ' + @sqlQuery

EXEC @cursorQuery

OPEN myCursor

FETCH NEXT FROM myCursor INTO ...

CLOSE myCursor

DEALLOCATE myCursor



This works fine, if there's only one instance of the
stored procedure running at a time. Should another user connect to the
site and run a search while someone's at it, it'll fail due to the
atempt to create a cursor with the same name.



My first thought was to make the cursor name unique, which led me to:

...

SET @cursorName = 'myCursor' + @uniqueUserID

SET @cursorQuery = 'DECLARE '+ @cursorName + 'CURSOR FAST_FORWARD FOR ' + @sqlQuery

EXEC @cursorQuery

...



The problem with this is that I can't do a FETCH NEXT FROM @cursorName since
@cursorName is a char variable holding the cursor name, and not a
cursor variable. So to enforce this unique name method the only option
I have is to keep creating dynamic sql queries and exucting them. And
this makes the sp a bitch to develop and maintain, and I'm guessing it
doesn't make it very performant.



So I moved on to my second idea: local cursor variables. The problem with
this is that if I create a local cursor variable by executing a dynamic
query, I can't extract it from the EXEC (or sp_executesql) context, as
it offers no output variable.


I guess my concrete questions are:


Is it possible to execute a dynamic sql query and extract a (cursor) variable from it?Is it possible to populate a local cursor variable with a global cursor, by providing the global cursor's name?Can I create a local cursor variable for a dynamic sql query? How?



Anybody sees another way arround this?Thanks in advance,

Carlos

View Replies !

---

Iterate Through A View?
Hello everybody,

obviously, I'm a newbie, so please forgive me for using the wrong terms accidently.

I have created a view that looks like this:

IDDiscCut
----------------
11125
1534
2167
3854
4514
4112
...

ID is a bigint, Disc & Cut ar varchars

As you can see, the ID is not the primary key.
Still, what I need is only the record of the first occurance of each ID,
like this:

IDDiscCut
----------------
11125
2167
3854
4514
...

Is there a way that I can iterate through the view and compare one record to
the previous or next?
(I didn't find anything in the Microsoft Library yet...)

Thanks!

Nina

View Replies !

---

Iterate Through Dates
Hello,

although I don't consider myself a newbie in SQL, I find working with the date functions rather confusing.

I have this (very) simpel statement:
Select count(*) from Invoices inv Where Date = '12/16/2003'

Which gives me the amount of invoices on this (exact) date. Is it possible to iterate between dates? I give a start- and enddate and it fetches the data for all the date in between
ex.
startdate 01/11/2003
stopdate 15/12/2003

Result
----------------------------
Date #invoices
01/11/2003 9
02/11/2003 5
03/11/2003 11
...
14/12/2003 23
15/12/2003 21

All help appreciated.

View Replies !

---

Iterate Through A Resultset In C# Without Cursors
 

Hi,
 
Here is a part of my proc :
 



Code Snippet
CREATE PROCEDURE proc
@param1 INT
@param2 INT
AS
BEGIN

-- how to affect a variable to this resultset in order to iterate and get fields ?
SELECT field FROM table
END
GO
 
 



 
In this proc, I would like to iterate through the resultset without using cursors, but I have no idea of the way to achieve it (I'm a very beginner in T/SQL)

And, in the case I would like to read this resultset through C# code, how can I return this resulset in my C# code in order to read it ?
 
Very sorry for these beginner questions
 
Thanks in advance

View Replies !

---

How To Iterate Thru Tables? Jamie Thomson...?
How does one setup a SSIS package to iterate through all (or selected) tables in a database. The reason is to update an internal database nightly using an approach similar to how Jamie Thomson stated in the below article.

http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

View Replies !

---

Iterate Through All Excel Files And All Their Sheets
 hello,

 

  i need to transfer (migrate ) the data from xl sheet to sqlserver but actually the thing is if the source excel file has different sheets, in each sheet i have the data

 

and i need to move the entire data( all the data that is present in all sheets of the excel file) to a single table into sql server

 

like wise i have many xl files ( which have many sheets ) .

 

for eg:

 

excel file 1:  

                          -> sheet 1

                          -> sheet 2

                          -> sheet 3

excel file 2:

                          -> sheet 1

                          -> sheet 2

                          -> sheet 3

excel file 3:

                          -> sheet 1

                          -> sheet 2

                          -> sheet 3

 

now i need to get the data from all of the files and i need to insert into a single table ( sql server) in ssis package

so plz help me by giving the solution asap.

 

thanks

 

B L Rao

 

View Replies !

---

Iterate Result Set Inside Stored Procedure
Hello, I have a situation that I query a table and return multiple rows (email addresses). I want to iterate through the rows and concatenate all email addresses into one string (will be passing this to another stored procedure to send mail).  How can I process result rows inside a stored procedure? This is what I have so far: CREATE PROCEDURE [dbo].[lm_emailComment_OnInsert] @serviceDetailID int,@comment varchar(500),@commentDate DateTime,@commentAuthor varchar(100)ASBEGINDECLARE @serviceID intDECLARE @p_recipients varchar(8000)DECLARE @p_message varchar(8000)DECLARE @p_subject varchar(100)/* Grab the Service_id from underlying Service_Detail_id*/SELECT @serviceID = Service_id FROM lm_Service_Detail WHERE Service_Detail_id = @serviceDetailID/* Get email addresses of Service Responsible Parties */
SELECT DISTINCT dbo.lm_Responsible_Party.EmailFROM dbo.lm_Service_Detail INNER JOIN
dbo.lm_Service_Filing_Type ON dbo.lm_Service_Detail.Service_id = dbo.lm_Service_Filing_Type.Service_id INNER JOIN
dbo.lm_Responsible_Party_Filing_Type ON dbo.lm_Service_Filing_Type.Filing_Type_id = dbo.lm_Responsible_Party_Filing_Type.Filing_Type_id INNER JOIN
dbo.lm_Responsible_Party ON dbo.lm_Responsible_Party_Filing_Type.Party_id = dbo.lm_Responsible_Party.Party_idWHERE (dbo.lm_Service_Detail.Service_Detail_id = @serviceDetailID)/* Build message */
SET @p_subject = "KLM - Service ID: " + CAST(@serviceID AS varchar(4))SET @p_recipients = "" /*need string of addresses*/

SET @p_message = @p_message + "Service Detail ID: " + CAST(@serviceDetailID AS varchar(4)) + char(13)SET @p_message = @p_message + "Comment Date: " + CAST(@commentDate As varchar(25)) + char(13)SET @p_message = @p_message + "Comment Author: " + @commentAuthor + char(13)SET @p_message = @p_message + "Comment: " + @comment + char(13)PRINT "subject: " + @p_subject + char(13)PRINT "recip: " + @p_recipients + char(13)PRINT "msg: " + @p_message + char(13)/*Send the email*/
Execute master..xp_sendmail @recipients = @p_recipients, @message = @p_message, @subject = @p_subject END
GO  

View Replies !

---

Foreach Loop Container Does Not Iterate Automatically
Hi,
 
Another classic example of something silly I must be doing
 
I have a "Foreach Loop Container" which uses a "Foreach ADO Enumerator".
The object source is a recordset variable.
 
Inside the "Foreach Loop Container", a lot of things are happening - some Execute SQL Tasks, Send Mail Tasks, Script Tasks and also a couple of Sequence Containers.
 
Now, I am getting 2 records in the recordset, but, the "Foreach Loop Container" executes just once.
 
Any Ideas?
 
 
Thanks in advance.
Regards,
B@ns

View Replies !

---

Iterate Thru A Dataset To Generate Multiple Charts
 

Hi, has anyone been able to implement something like this?
 
I have two data sets
1) List of all stores
2) Table containing the following fields: store, sales as of end of quarter, quarter end date
 
I am creating a graph that would track the trend of sales quarter per quarter for each store and would need to have an individual graph for each.
 
Is there a way to dynamically do this rather than creating an individual recordset for each store?
 
Thanks
 
 

View Replies !

---

Parameters!Blah.Label(0) - How To Iterate Through Array
I have a multi select param that I display on the title of the report using something like so:
Parameters!Division.Label(0)

 
The problem is when a user selects more than one value it does not get displayed on the title.  Addding Parameters!Division.Label(0) + Parameters!Division.Label(1) + Parameters!Division.Label(2) doesn't returns an error if user only selects 1 or 2 values because Parameters!Division.Label(2) is empty. 
 
Is there a way to iterate through or do error checking on the array so that I can dynamically populate the title with the values selected by the user.
 
Any help would me much appreciated.
 
 

View Replies !

---

What Is The Difference Between: A Table Create Using Table Variable And Using # Temporary Table In Stored Procedure
 which is more efficient...which takes less memory...how is the memory allocation done for both the types.

View Replies !

---

Putting Data Into Table B From Table A Without Using A Cursor
on SQL Server 2005, no SP.

Currently using a cursor in a stored procedure to retrieve data from one table and to put it into another table.  like so:

declare @HTR money
declare @Uniqueid varchar(15)

declare cur_HTR cursor for
select uniqueid, sum(hours_to_resolve) as thtr from com_trail_helpdesk_module  group by uniqueid

open cur_htr
fetch next from cur_HTR into @Uniqueid, @HTR

while @@fetch_status = 0 begin
    update com_hpd_helpdesk_history
    set total_hours_to_resolve = @HTR
    where case_id_ = @Uniqueid
    and dateasat = @dateasat
    fetch next from cur_HTR into @Uniqueid, @HTR
end

close  cur_htr
deallocate cur_htr
...

This is taking about 45 minutes each time to do 21k records.  Is there a faster, better way to do this?

View Replies !

---

Variable In CURSOR Sql Statement (was &"Please Help Me&")
Hi All,

What i am trying to do is concatenate variable "@Where" with CURSOR sql statement,inside a procedure . But, it doesn't seem to get the value for
the @Where. (I tried with DEBUGGING option of Query Analyzer also).

=============================================
SET @Where = ''
if IsNull(@Input1,NULL) <> NULL
Set @Where = @Where + " AND studentid='" + @input1 +"'"

if isnull(@Input2,NULL) <> NULL
Set @Where = @Where + " AND teacherid =' " + @Input2 +"'"

DECLARE curs1 CURSOR SCROLL
FOR SELECT
firstname
FROM
school
WHERE
school ='ABC' + @where
=============================================
Please check my SQL Above and Could somebody tell me how can I attach the VARIABLE with CURSOR sql statement ?

Thanks !

View Replies !

---

Table Name And Cursor
I am using MS-SQL Server 2K....

I have a stored procedure that contains a cursor. Right now, the table name for the cursor is "hard-coded". I would like to be able to "soft-code" the table name, because the table name may vary (although the fields would always be the same).

Is this even possible?

The only workaround I can think of is to use an EXEC on a real-time built SQL statement into a temp table and then cursor through the temp table.

Is there a better way to do this?

Thanks...

View Replies !

---

Cursor Or Temp Table
I just want to know which one is more efficient cursor or temp table for looping through a record set?

I am basically looking for better performance and server utilization.

View Replies !

---

SQL 2000 - Table .vs. Cursor
can some on tell me which is better to use. the new TABLE data type in 2000 or cursors ????

thanks

View Replies !

---

Cursor Or Temp Table
Hi Chaps,

I want to write a stored procedure in which I will update the all calendar week values (stored in a table). And to achieve this I will have to loop through all the records present in calendar table.

I am just wondering that should I use Cursor or TEMP table (caz of performance issues)?

As it will be part of data warehouse so all the processing will be carried out at SERVER ( means no client)

Can any one tell me that which would be the best solution ( Cursor or Temp table) in my case and WHY?

Note: I am using SQL Server 2005 standard edition

waiting for quick reply.

 

regards,

Anas

View Replies !

---

How Do U Update A Row In A Table Using Cursor ?
 How do you do this by using this cursor mechanisum ? and which type of cursor is best to do that ?

View Replies !

---

Cursor On A Temp Table
Hi all,

I have a task of rewriting all the old procs which were build on cursors, the management does want me to remove the cursor completely since that would mean rewriting everthing.. they want to use a temp table and run a cursor on the temp table. Since there are many cursors in each proc. Any suggestions on to go about it. I am against the use of cursor any everywhere in it , even if it calls for rewriting the whole db again.
I did some tests on running the old proc and the semi new proc , which runs a cursor from the temp table for the cursor query. And it even bad than the cursor only. what does this mean? Is it really a very bad idea to run cursor on a temp table?

Necessity is the mother of all inventions!

View Replies !

---

Is A Temp Table Or A Table Variable Used In UDF's Returning A Table?
In a table-valued UDF, does the UDF use a table variable or a temp table to form the resultset returned?
 

View Replies !

---

Can't Reference Logical Table Within Cursor
Does anyone know if MS SQL can't process a "select * from deleted" from within a cursor that's embedded in a trigger? I'm getting an error when I run this...

DECLARE check_contact_fields CURSOR
FOR SELECT field_id, column_name FROM contacts_fields
OPEN check_contact_fields
FETCH NEXT FROM check_contact_fields INTO @field_id, @column_name
WHILE (@@FETCH_STATUS = 0)
BEGIN
set @SQL = 'select ' + @column_name + ' into ##DeletedData from deleted'
exec sp_executesql @SQL
set @SQL = 'select ' + @column_name + ' into ##InsertedData from inserted'
exec sp_executesql @SQL
if (select * from ##DeletedData) <> (select * from ##InsertedData)
select * from ##InsertedData
FETCH NEXT FROM check_contact_fields INTO @field_id, @column_name
END
CLOSE check_contact_fields
DEALLOCATE check_contact_fields

drop table ##DeletedData
drop table ##InsertedData


Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'deleted'.


TIA

View Replies !

---

Declaring A Cursor On A Temporary Table
How do I declare a cursor on a table like #TempPerson
when thhs table is only created when I do :

Select Name, Age Into #TempPerson From Person

View Replies !

---

Inserting Cursor Information Into A Table
i'm currently writing an application whereby the user must be able to view the records within a table in chunks of 20, 50 or whatever. thus i need to be able to select the first 20 for example then once a 'next' button is pushed, the next 20 must be displayed.

i was thinking of using a cursor to get all the records, moving absolutely to where the first row that should be displayed (fetch absolute 20 from cursor), then using the fetch next from cursor into a temporary table.

is this possible and how would i got about doing it? (ie. code examples)

thanx

View Replies !

---

Cursor From Diff Table -challenge
I have 17 tables with names as Breaktable1, Breaktable2,.... till
Breaktable17


i make a query like this

set nocount on

Declare @sEventtable varchar(20)
Declare @sstartzoneid varchar(20)
Declare @ssttopzoneid varchar(20)



Select @sstartzoneid ='1'
Select @ssttopzoneid = '17'



select @sstartzoneid = convert(smallint,@sstartzoneid)

if @sstartzoneid<> 0
DECLARE Tablecursor FOR <-------> ( error here )
select status, sum(cost)
FROM "breaktable " + @sstartzoneid
where breakdate=DATEDIFF(day,'08/12/1960','03/29/2003')
group by status


CLOSE tableCursor
DEALLOCATE tableCursor


can somebody help ????

View Replies !

---

Will A Cursor Fetch Lock The Table
Open a cursor , Fetch the record ,
during this kind of operation , will the specific table be locked and fail
to be updated or select by another session ?

View Replies !

---

Cursor To Store Value In Temp Table
Following sp gives wrong result whats wrong with following cursor?

ALTER PROCEDURE [dbo].[spPMPT_GetProjectBenefitDetailsForAssess]

@ProjectBenefitID INT

AS

SET NOCOUNT ON


DECLARE @ErrorMsgID INT
DECLARE@ErrorMsg VARCHAR(200)

DECLARE @TEMP_BENEFIT
TABLE (ActualQuantity INT,
ExpectedQuantity INT,
ActualQulity VARCHAR(2000),
ExpectedQulity VARCHAR(2000) )


DECLARE @AssessBenefitID INT
DECLARE @ActualQuantity INT
DECLARE @ExpectedQuantity INT
DECLARE @ActualQuality VARCHAR(2000)
DECLARE @ExpectedQuality VARCHAR(2000)
DECLARE @AssessFlag CHAR
DECLARE CUR_BENEFIT CURSOR FOR
SELECT AssessBenefitID,ProjectBenefitID,AssessFlag FROM PMPT_AssessBenefit WHERE ProjectBenefitID=@ProjectBenefitID

OPEN CUR_BENEFIT


FETCH NEXT FROM CUR_BENEFIT INTO @AssessBenefitID,@ProjectBenefitID,@AssessFlag




WHILE @@FETCH_STATUS = 0
BEGIN



SELECT @ActualQuantity=Quantity FROM dbo.PMPT_AssessBenefit WHERE AssessFlag='A' AND AssessBenefitID=@AssessBenefitID AND ProjectBenefitID=@ProjectBenefitID
SELECT @ExpectedQuantity=Quantity FROM dbo.PMPT_AssessBenefit WHERE AssessFlag='E' AND AssessBenefitID=@AssessBenefitID AND ProjectBenefitID=@ProjectBenefitID
SELECT @ActualQuality=Quality FROM dbo.PMPT_AssessBenefit WHERE AssessFlag='A' AND AssessBenefitID=@AssessBenefitID AND ProjectBenefitID=@ProjectBenefitID
SELECT @ExpectedQuality=Quality FROM dbo.PMPT_AssessBenefit WHERE AssessFlag='E' AND AssessBenefitID=@AssessBenefitID AND ProjectBenefitID=@ProjectBenefitID



INSERT INTO @TEMP_BENEFIT (ActualQuantity ,
ExpectedQuantity ,
ActualQulity ,
ExpectedQulity )VALUES(@ActualQuantity,@ExpectedQuantity,@ActualQuality,@ExpectedQuality)


FETCH NEXT FROM CUR_BENEFIT INTO @AssessBenefitID,@ProjectBenefitID,@AssessFlag
END

CLOSE CUR_BENEFIT
DEALLOCATE CUR_BENEFIT


SELECT * FROM @TEMP_BENEFIT

View Replies !

---

The Cursor Does Not Include The Table ... Error?
We use a lot of virtual machines. I have a base VM with SQL 2005 installed. I rename this VM (i have multiple copies running) and run an old application (VB code, iterates through recordsets, etc.).

I get: Microsoft][ODBC SQL Server Driver][SQL Server]Could not find server '2K3VM-DG' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

This makes sense, and I can fix it with sp_dropserver / sp_addserver [local]. Good.

The next error is puzzling though:

[Microsoft][ODBC SQL Server Driver][SQL Server]The cursor does not include the table being modified or the table is not updatable through the cursor.

Any suggestions on how to fix this?

View Replies !

---