How To Read The Rows In A Cursor Variable

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

View Replies !

---

How To Create A Trigger Such That It Can Delete The Rows Whenever Any Other Application Such As Biztalk Had Read The Rows ?
I had created a trigger which sees that whether a database is updated if it is its copy the values of the updated row into another control table now I want to read the content of control_table into BIzTalk and after reading I want to delete it.Can any one suggest the suitable ay to do this?

View Replies !

---

Cursor Is READ ONLY.----HELP!!!
I have script for retrivel of duplicates. It ran successfully 1st time....but now it gives "error 16929 cursor is READ ONLY". Can anyone tell me why and how it can be rectified?

View Replies !

---

CURSOR READ ONLY
select * from new_iba_3

DECLARE new_cur2 CURSOR FOR select * from new_iba_3 order by rid, attr, val for update of rid;
open new_cur2

DECLARE @tracepoint NUMERIC,
@ibaidNUMERIC,
@ridNUMERIC,
@attrNUMERIC,
@valNVARCHAR(1024),
@src_ibaNUMERIC,
@src_linkNUMERIC,
@rowidNUMERIC,
@new_rec_ibaidNUMERIC,
@new_rec_ridNUMERIC,
@new_rec_attrNUMERIC,
@new_rec_valNVARCHAR(1024),
@new_rec_orig_ridNUMERIC



FETCH NEXT FROM new_cur2 INTO @new_rec_ibaid,@new_rec_rid,@new_rec_attr,@new_rec_val,@new_rec_orig_rid
delete from new_iba_3 where current of new_cur2; --HERE



close new_cur2;
deallocate new_cur2;

select * from new_iba_3


WHEN I TRY TO DELETE FROM new_iba_3 IT GIVES CURSOR IS READ ONLY
BUT UPDATE WORKS ON THIS TABLE I HAVE NOT DECLARED THIS CURSOUR AS READ ONLY THEN WHY IT IS GIVING THIS ERROR

View Replies !

---

Can I Read Some Rows From The Middle Of Rows In DataReader?
helo..
I have 100,000 rows in the database and I want to read results for eg: from 5000 to 5050 by DataReader.
I wrote this code to do this but its too slow:

 

Dim SlctStr As String = "select * from topicstbl where partID like '" & PagePartID & "'"

Dim ReadCom As New SqlClient.SqlCommand

ReadCom.CommandText = SlctStr

ReadCom.Connection = MainLib.MyConnection

Dim MyReader As SqlClient.SqlDataReader = ReadCom.ExecuteReader()

 

Dim StartTNum As Long = 5000

 

For IR As Long = 0 To StartTNum - 1

MyReader.Read()

Next

 

Do While MyReader.Read

StartTNum += 1

If StartTNum > 5500 Then Exit Do

 

 '''''''''''''''''''


Loop

MyReader.Close()

 

is there another way to do the same thing better off than this code?

View Replies !

---

Cursor Is READ ONLY.----HELP!!!--error 16929.
CREATE PROCEDURE RemoveDuplicates
AS

Declare c_Work Cursor For
SELECT storeid, stocknumber, invnumber FROM inventorytest
ORDER BY storeid, stocknumber, invnumber

-- declare variables for the columns
-- data types made up

Declare @storeid char(20)
Declare @stocknumber char(10)
Declare @invnumber char(12)

-- declare a duplicate set of variables now for working with

Declare @storeid_new char(20)
Declare @stocknumber_new char(10)
Declare @invnumber_new char(12)

-- declare a counter variable for Transaction Commits

Declare @Count int

Open c_Work
Fetch Next from c_Work INTO @storeid, @stocknumber, @invnumber

BEGIN TRANSACTION
While @@FETCH_STATUS = 0
BEGIN
If @storeid = @storeid_new
BEGIN
If @stocknumber = @stocknumber_new
BEGIN
If @invnumber = @invnumber_new
BEGIN
DELETE FROM inventorytest Where Current Of c_Work
SET @Count = @Count + 1
IF @Count = 1000
BEGIN
Set @Count = 0
COMMIT TRANSACTION
BEGIN TRANSACTION
END
End
ELSE
BEGIN
SET @invnumber_new = @invnumber
END
END
ELSE
BEGIN
SET @invnumber_new = @invnumber
SET @stocknumber_new = @stocknumber
END
END
ELSE
BEGIN
SET @invnumber_new = @invnumber
SET @stocknumber_new = @stocknumber
SET @storeid_new = @storeid
END
Fetch Next from c_Work INTO @storeid, @stocknumber, @invnumber
END

Close c_Work
Deallocate c_Work

COMMIT TRANSACTION

----When i execute this Sp i get "error 16929 cursor is READ ONLY" . It had worked a week ago and now it doesn't work. Can anyone help me out and its urgent. Thanks in advance.

View Replies !

---

Read The Csv In A Cursor Instead Of Bulk Update
Hello,
I am trying to read in from a csv file which works like this:


DECLARE @doesExist INT
DECLARE @fileName VARCHAR(200)
SET @fileName = 'c:file.csv'

SET NOCOUNT ON

EXEC xp_fileexist "' + @fileName + '", @doesExist OUTPUT
SET NOCOUNT OFF

IF @doesExist = 1

BEGIN
BULK INSERT OrdersBulk
FROM "' + @fileName + '"
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
END
ELSE
print('Error cant find file')



What I want to do is check another table before each line inserts, if the data already exists I want to do an UPDATE.
I think i can do what i need with a cursor but I think the bulk update just pushes all the data up and will not allow me to put in the cursor.
So is there a way i can read the csv in a cursor instead of using the bulk insert so i can examine each row?

View Replies !

---

Does Cursor Convert Table To Read/write?
Hello,

Any help here much appreciated.

I am using sql server 2000 to perform address cleansing. there is a point in my scripting when a table i pass values to becomes read/write.

i suspect this is when i run a cursor through the table.

Is anyone able to confirm for me whether running a cursor changes a table's properties?

Many thanks.

Tim

Ps as the table seems to be read/write it is harder to tell if NULLs are in the table and this is messing with joins I have further down the track.

View Replies !

---

BUG? Optimistic Concurrency Check Don't Work When BLOB Field Read After Row Modification Outside Of The Cursor.
Hi, here is the problem.

 

SYSTEMS INVOLVED:


Sql Server 2000, VB 6, ADODB

SYMPTOM:

Recordset opend with settings:

tb.LockType = adLockOptimistic
tb.CursorLocation = adUseServer
tb.CursorType = adOpenKeyset

When I open recordset using above settings (just 1 record), then change that record in other application(eg. using Management Studio), then get back to vb and just read any blob field (text, ntext)  and modify any field in the same record then at the very end execute tb.Update operation is succesful which is wrong, because we should get an error: "Optimistic concurrency check failed. The row was modified outside of this cursor." If you pass over the "read any blob field" step it works as it should (operation fails with above error).

 

CODE TO REPRODUCE:

 

Sub Test()

    Dim SqlConn As New ADODB.Connection
    Dim tb As New ADODB.Recordset
    tb.LockType = adLockOptimistic
    tb.CursorLocation = adUseServer
    tb.CursorType = adOpenKeyset
   
On Error GoTo Error
    SqlConn.ConnectionString = "Provider=SQLOLEDB.1;User Id=sa;Password=<password>;Initial Catalog=Northwind;Data Source=<server>"
    SqlConn.Open
    tb.Open "SELECT * FROM Categories WHERE CategoryId = 1", SqlConn
       
    tb.Fields("CategoryName") = "A"
   
    MsgBox "Now modify and commit changes to this record in separate application, when done click OK."
   
    res = MsgBox("Read blob field (Yes, concurency check doesn't work), No (does work)", vbYesNo)
    If res = vbYes Then
        tmp = tb.Fields("Description")
    End If
       
    tb.Update
   
    MsgBox "Update successful!"
    SqlConn.Close
    Exit Sub
Error:
    MsgBox Err.Description + vbCrLf + Err.Source, 16

End Sub

 

 

I'm aware that blob fileld is handled in a special  way, it is not stored in record's data page it belongs to (unles we specify in row option).

If anyone had this problem or found confirmation that it is the bug, please let me know or maybe it's not the bug and there is some justification of such behaviore ....

Thank you for any reply !

 

Tomek

 

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 !

---

How To Read A Filename Into A Variable
I'm trying to read the filename from a file that I'm downloading by FTP (FTP Component) into a string variable so I can later use it for another part of the process, how can I do this?

View Replies !

---

How Tro Read Excel Through Variable
Hi Team,
I have created on SSIS package which export data from excel to SQL server 2005.It's working fine.Now I am trying to make it dynamic using variable. It means I wil just change the path or name of the excel file
How to access excel file using variables ?
Need help any link,suggestion .....

Yogesh V. Desai. | SQLDBA|

View Replies !

---

Max Rows In Cursor
hi, is there a max number of rows created in a cursor..... if so, what is the max alowable rows in a cursor?

thanks

Ali

View Replies !

---

How To Iterate Through Table Variable Without Using Cursor
Hi,
I need a small help. In my stored procedure, i create a table variable and fill it with records based on my query. The ID field within the table is not continous and can have any value in increasing order .e.g. The ID sequence may be like 20, 33, 34, 59, 78, 79... I want to iterate through each record within the table but without using a Cursor. I want to use a loop for this purpose. There are many articles pointing out how to iterate through records in a table variable but that requires the IDs to be continous which is not possible in my case. Can anyone help me solve this problem...
Any help is appreciated...

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 !

---

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 !

---

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 !

---

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 !

---

Can I Read What Is In A SSIS Variable From Within A C# Program That Executed It?
Can I read what is in a SSIS variable from within a C# program that executed it?

View Replies !

---

Is It Possible To Read Flat File Data From A Variable?
Hi,

I'm relatively new to SSIS and I've been tinkering with the XML and Flat File sources.

I noticed that in the XML source it is possible to tell SSIS to read the XML data from a variable. I didn't see a similar option for the Flat File source.

Does anyone know if it is possible to read flat file data from a variable when using the Flat File source?

Thanks,

-dhideal

View Replies !

---

Read Write Same Variable In Script Task
I want to write a variable say testVar in PostExecute of my script. I also want to read the same variable at the start of my script block.

How to specify this variable in the script transformation editor

ReadOnlyVariables = ????
WriteOnlyVariables = ???

If i just define

WriteOnlyVariables = testVar

will it work . I mean i cannot use testVar in both read & write


Any suggestions...


Thanks

View Replies !

---

Rows Deletion Affected By Cursor
Hello,

I am using a cursor to navigate on data...of a table....
inside the while @@fetch_status = 0 command
I want to delete some rows from the table(temporary table)
in order to not be processed...
The problem is that I want this deletion to affect the rows the cursor has.

I declared a dynamic cursor but it does not work.

Does anyone know how I can do this??

Thanks :)

View Replies !

---

Help With Cursor To Insert 100 Rows At A Time
Hi all,

Can one of you help me with using a cursor that would insert only 100 rows at a time from source table 1 to target table 2. I am not able to loop beyond the first 100 rows.

Here is what I have till now:


CREATE procedure Insert100RowsAtaTime
AS
SET NOCOUNT ON

declare @Col1 int
declare @Col2 char(9)
DECLARE @RETURNVALUE int
DECLARE @ERRORMESSAGETXT varchar(510)
DECLARE @ERRORNUM int
DECLARE @LOCALROWCOUNT int

declare Insert_Cur cursor local fast_forward
FOR
SELECT top 100 Col1,Col2 from Table1
WHERE Col1 not in ( SELECT Col1 /* Col1 is PK. This statement is used to prevent the same rows from being inserted in Table 2*/
from Table2)

set @RETURNVALUE = 0
set @ERRORNUM = 0

BEGIN

open Insert_Cur
fetch NEXT from Insert_Cur into @Col1, @Col2
while (@@FETCH_STATUS = 0)
insert into Table2 (Col1,Col2) select @Col1,@Col2

SELECT @ERRORNUM = @@ERROR, @LOCALROWCOUNT = @@ROWCOUNT
IF @ERRORNUM = 0
BEGIN
IF @LOCALROWCOUNT >= 1
BEGIN
SELECT @RETURNVALUE = 0
END
ELSE
BEGIN
SELECT @RETURNVALUE = 1
RAISERROR ('INSERT FAILS',16, 1)
END
END
ELSE
BEGIN
SELECT @ERRORMESSAGETXT = description FROM [master].[dbo].[sysmessages]
WHERE error = @@ERROR
RAISERROR (@ERRORMESSAGETXT, 16, 1)
SELECT @RETURNVALUE = 1
END

fetch NEXT from Insert_Cur into @Col1, @Col2
end

close Insert_Cur
deallocate Insert_Cur

RETURN @RETURNVALUE
END

View Replies !

---

How To Read A Set Of Rows Into Session Variables? C#
Hello ASP.NET C# and SQL gurus
 I want to read the results of a set of rows into session variables -- how is it possible?
 Let me try explain.  I have a query which returns multiple rows, e.g. the following query
SELECT PROFILE_ID, PROFILE_NAME FROM USER_PROFILES returns 5 rows i.e 5 sets of profile_ids and profile_names.
 Now, I want to capture these and store them in session variables thus.
Session["PROFILEID_1"] =
Session["PROFILEID_2"] =
Session["PROFILEID_3"] =
Session["PROFILEID_4"] =
Session["PROFILEID_5"] =
Session["PROFILENAME_1"] =
Session["PROFILENAME_2"] =
Session["PROFILENAME_3"] =
Session["PROFILENAME_4"] =
Session["PROFILENAME_5"] =
 
Thanks in advance!
Fouwaaz

View Replies !

---

Join Only Returns The Read Rows :|
Hi all,

I am trying to build a association table (t2) to store a list of users
have viewed an item in my records table (t1). My goal is to send the
UserID parameter to the query and return to the user a read / not read
marker from the query so I can handle the read ones differently in my
.net code. The problem is that I cannot work out how to return anything
but the read data to the client. So far my stored proc looks like this

DECLARE @UserID AS Int -- FOR TESTING
SET @UserID = 219 -- FOR TESTING

SELECT t1.strTitle, t1.MemoID, Count(t2.UserID) AS ReadCount,t2.UserID

FROM t1
LEFT OUTER JOIN
t2 ON t1.MemoID = t2.MemoID

WHERE t2.UserID = @UserID

GROUP BY t1.MemoID, t1.strTitle,t2.UserID

It works fine but only returns those records from t1 that are read. I
need to return the records with null values also! I may have built the
assoc table wrong and would really appreciate some pointers on what I
am doing wrong. (assoc table has rID, MemoID and UserID columns)

Please help!

Many thanks

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 !

---

Read Single Piece Of Data From SqlDataSource To A Variable?
First I want to say thanks to the folks how have helped me over the past week or two.
I'm missing a crucial piece of insight. How do I read a single piece of data from a SqlDataSource which only hold one row of data into a declared variable?
string zUserName = SqlDataSource.(get the value in the row that is labeled UserName)
It really doesn't matter to me whether it is an SqlDataSource or some other data container that hold the data I've selected. My goal is to be able to read some data from some predefined datasource into a declared variable.
If I am approaching this problem in the wrong way any advice or article links you could give me would be grealy appreciate.
Thanks, Bill

View Replies !

---

Use Cursor To Update Rows In Batches Of A Given Size
I'd like to use a cursor to update a large number of rows in a table, updating a batch of 1000 records at a time. I tried updating in one transaction but log fills up. I'd like to update a batch, clear the log, then update another batch. Repeat till done. Don't really want to increase the log size for this one time shot. Can anyone give me an example to follow.
Thanks

View Replies !

---

Retrieving Multiple Rows For A Cursor Item
Hi, can someone plz give me an idea of how to proceed with this...
I've got a server side cursor that retrieves a list of customers from a sql server table which match a specific criteria (those who have had orders in the yr 2000)
For each customer, I need to retrieve a list of 5 top items purchased along with dollars spent on each item by that customer.
I've tried to do this in a loop that uses a counter but probably my syntax was off and I'm not sure that this is better than a correlated join? Can someone show me a temlate of the appropriate syntax to use for this operation
I appreciate suggestions, thanks again.

Irene

View Replies !

---

Read CSV File - Save Columns Into Rows
 I want to import CSV file and convert columns into rows depending on Customer count(2nd record in each row of CSV file) and save to SQL table

--CSV file format
State, Customer_Count, Name_1, Total_1,Name_2, Total_2,Name_3, Total_3..can go upto 600

GA,2,'John Doe',14.00,'Roger Smith',15.00
FL,3,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00
SC,5,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00,'James Brown',17.00,'Rick Davis',18.00

Data in SQL table from csv file should look like this

State,Name,Total
GA,John Doe,14.00
GA,Roger Smith,15.00
FL,John Doe,14.00,
FL,Roger Smith,15.00
FL,Sally Cox,16.00

I have multiple CSV files with millions of records. How can i achieve this using Integration Services or Bulk Data Import.

 

 

View Replies !

---

SSIS Doesn't Read All Input Rows
Hi *,

I'm trying to import a flat file with ~3500 rows into a SQL-DB. SSIS extracts only around half the rows. It leaves out every 2nd row. Anyone had this problem before?

Thanks!

View Replies !

---

How To Read Block Of Rows From Database Tables
have created a Database Application in Java and display all the records in tabular format of one Table. This table have Millions of Rows, If I run Select * from Table, then my Machine not responding, so Now I wants to add paging of 1000 rows at one time.

Is there are any option/query to read block of rows at one time and then query again for next page ?

i.e In MYSQL have LIMIT clause with Select Statement

Please let me know..

Database : SQL Server 2000/2005,

Thanks in Advance
Laxmilal

View Replies !

---

Read CSV File - Save Columns Into Rows
I want to import CSV file and convert columns into rows depending on Customer count(2nd record in each row of CSV file) and save to SQL table

--CSV file format
State, Customer_Count, Name_1, Total_1,Name_2, Total_2,Name_3, Total_3..can go upto 350

GA,2,'John Doe',14.00,'Roger Smith',15.00
FL,3,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00
SC,5,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00,'James Brown',17.00,'Rick Davis',18.00

Data in SQL table from csv file should look like this

State,Name,Total
GA,John Doe,14.00
GA,Roger Smith,15.00
FL,John Doe,14.00,
FL,Roger Smith,15.00
FL,Sally Cox,16.00

I have multiple CSV files with millions of records. How can i achieve this using Integration Services or Bulk Data Import.

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 !

---

Error While Trying To Assign A Value To A Read Write Variable In SSIS Package Script Component
Hi,

       I am trying to develop a SSIS package which will read the records from the flat file and insert them into a destination table. I have some validations written in script component. I have declared two Read Write variables with package level scope. when i try to assign a value to the variable in the script component and run the package, the package throws me an error "The collection of variables locked for read and write access is not available outside of PostExecute".

 

What should be done to over come the problem please help me on this regard 

 

Thanks

Madhavan.M

View Replies !

---

Read Rows AND An Output Parameter From Codebehind Returned By Stored Proc?
I have a stored procedure that returns a resultset AND an output parameter, pseudocode:myspGetPoll@pollID int,@totalvoters int outputselect questionID,question from [myPoll] where pollID=@pollID  @totalvoters=(select count(usercode) from [myPoll] where pollID=@pollID)1. In my code behind I'd like to read both the rows (questionID and question) as well as total results (totalvoters) How could I do so?2. what would be the signature of my function so that I can retreive BOTH a resultset AND a single value?e.g.: private function getPollResults(byval pollID as integer, byref totalvoters as integer) as datasetwhile reader.read    dataset.addrow <read from result>end whiletotalvoters=<read from result>end functionThanks!

View Replies !

---

SqlServer Changes Cursor To &"read Only&"
I am trying to write a cursor to update certain rows in a particularorder as follows: (I need the cursor version, not SQL, as the updatelogic depends on the order of rows and some other conditions. Iremoved the Order-By clause from the statement to simplify it; itgives the same error message with or without it.)DECLARE prod_cursor CURSORFORWARD_ONLYKEYSETFOR SELECT 1 FROM all_products WHERE p_qty = 0 FOR UPDATEThis gives the following error message: "FOR UPDATE cannot bespecified on a READ ONLY cursor."I have tried a few different combinations of cursor types (like SCROLLinstead of FORWARD_ONLY) but they all give this error, although thestatement seems identical to what I have seen in the books and inbooks online.Any ideas on how to convert this into an updating cursor?

View Replies !

---