Cant fetch record from Cursor

Hi,

I'm relatively inexperienced in sql, and am having trouble interpreting the behavior of a cursor in some code I have inherited. When there is a record in both the Filters and FilterElements tables, the fetch_status is 0. If there is a record in Filters, but no child record in FilterElements, the fetch_status is -1. Since the tables are joined with a RIGHT OUTER JOIN, even when there is no corresponding record in FilterElements, a record is returned (I have verified running the select in a query window). But when used in a cursor, the record is not fetched. The fetch_status is -1. Can anyone tell me why the fetch doesn't work in this case. Thanks

--

DECLARE @.CreatedByUsernchar(100),@.WorkflowIDsvarchar(50);

DECLARE @.MyVariableCURSOR;

SET @.MyVariable=CURSORFOR

SELECTisnull(Filters.WorkflowIDs,''),

isnull(FilterElements.CreatedByUser,'')

FROMFiltersRIGHTOUTERJOIN

FilterElementsON Filters.ItemID= FilterElements.FiltersItemID

WHERE FiltersItemID= @.FilterID;

OPEN @.MyVariable;

FETCH NEXTFROM @.MyVariable

INTO @.WorkflowIDs, @.CreatedByUser;

For Table A and B,

1. if you dont have any corresponding entries for a record in A in B, and still wants to display the record in A, you should be using LEFT OUTER JOIN

2. if you dont have any records in B for a record in A, but have records for other records in A and still wants to display all the records in B, then you should be using RIGHT OUTER JOIN

Thanks

-Mark the post(s) as "Answer" that helped you|||

Everything seems to be correct. I had tried using the steps u followed, I am getting results for that,

DECLARE @.storeID varchar(10), @.storeName varchar(50), @.ordNo varchar(10)
DECLARE @.MyVariable CURSOR
SET @.MyVariable = CURSOR FOR
SELECT isnull(a.stor_id,''), isnull(a.stor_name,''),isnull(b.ord_num,'') from stores a
right outer join sales b on a.stor_id = b.stor_id
Open @.MyVariable
fetch next from @.MyVariable into @.storeID,@.storeName, @.ordNo
while @.@.fetch_status = 0
begin
print @.storeID+''+@.storeName+' '+ @.ordNo
fetch next from @.MyVariable into @.storeID,@.storeName, @.ordNo
end
close @.MyVariable
deallocate @.MyVariable

|||

Thanks. It appears to be working now. I think I got the LEFT and RIGHT outer join confused. That's a newbie for you.

Cant delete single record? HELP!

OK,

This one is driving me nuts. I've issued a very simple statement to delete a single row from a table. It appears that when I execute it in SQL Query Analyzer the CPUTime spikes and holds one of the CPUs on the box pegged at 100%. I've let this thing run for over a day, and it's not deleting the one damn record. Any thoughts? :confused: :confused: Here's the command I'm executing:

DELETE FROM Invoices WHERE InvoiceID = 153345

Running SELECT * FROM Invoices WHERE InvoiceID = 153345 returns only a single record as it should. InvoiceID is the PK in this table. Any and all help is greatly appreciated. I've rebooted the server, but to no avail. Same thing happens after a reboot.

TIAAny triggers on the table?|||Any triggers on the table?
No, no triggers on the table.|||Any triggles on this table?
No triggles either ;)|||what does the execution plan looks like for the delete statement?

Are there foreign key constraints on Invoices table ?
run something similar to the following:

select object_name(constid) as FkeyName,
object_name(fkeyid) as DependentTable from sysforeignkeys where rkeyid=object_id('Invoices')

Is anything locking Invoices table when you try the delete?

simas|||It won't even pull up an estimated execution plan. It just sits there doing nothing. There are several foreign keys, but I'm deleting from those tables prior to this delete, and they all delete without a hitch. It's just this table. I don't see anything locking the Invoices table.|||Have you ran a DBCC CHECKDB to see if there are any integrity issues?|||use <Your_Database_Name>
go
dbcc opentran
go

You have to have an open transaction that references this table. Oh, and do KILL your DELETE before you issue the DBCC. Make sure that any rollback (doubt it) completes before you fire DBCC.|||CHECKDB found 0 allocation errors and 0 consistency errors in database 'Accounting'.

Nice thought though.|||This is trying to pull a rabit out of the hat, but try to run UPDATE STATISTICS on the table. Also, try to drop and recreate the PRIMARY KEY index on that table. Then, see if you can delete the rows.

Also, I don't know why in the world I didn't suggest this earlier. In Query Analyzer before you do anything else, run: SELECT @.@.SPID. Set up a profiler to capture all activity using that SPID as a filter. In particular, look for SQL:Batch Started; SQL:Batch Completed; all errors, all recompiles, all locks and blocks; RPC:Started; RPC:Completed. See if you can see anything in the profiler that would lead to this problem.

Also, when this is running, if you run this in Query Analyzer do you get any results?

SELECT * FROM master..sysprocesses WHERE blocked <> 0|||UPDATE: DELETING THE PRIMARY KEY AND RECREATING IT WORKED. (no comment on how bad I think it is that this should ever have to be done, but it's fixed so I won't complain)

Mad Thanks go out to those that helped me out with this!

Special Thanks to Derrick! :D|||And you're sure you're on the latest service packs, critical updates, etc for Windows and SQL Server? Make sure the machine that has Query Analyzer on it has also been updated.|||SP3a on both, I'll have to check on the critical updates (since my boss thinks the internet is a fad). Thanks again for everything!

Can't create a new record using Access?

Please forgive this basic question but I am just getting started. I am using
an Access project file connected to an SQL table. I used the Enterprise
Manager to create a simple table with only 3 fields. When I open the
database from the Tables section in Access it will not let me add a new
record. I tried looking at every possible option but I am stumped and none
of the books I have address this issue. Any suggestion would be greatly
appreciated.
Thanks,
Steve P.What does "not let me" mean? Do you get an error message? What is it?
"Steve P" <sprovoyeur@.cox.net> wrote in message
news:J3%Ib.17521$WQ3.11463@.lakeread05...

quote:

---

> Please forgive this basic question but I am just getting started. I am

---

using

quote:

---

> an Access project file connected to an SQL table. I used the Enterprise
> Manager to create a simple table with only 3 fields. When I open the
> database from the Tables section in Access it will not let me add a new
> record. I tried looking at every possible option but I am stumped and none
> of the books I have address this issue. Any suggestion would be greatly
> appreciated.
> Thanks,
> Steve P.
>
>

---

Cant convert record to integer

Hi All

I'm getting an error that says that this can't be converted to an integer.

Here is the line that gets the error.dt = ((DataView)(EventDataSource1.Select(dssa))).ToTable()

I have also tried. dt = (DataView)(EventDataSource1.Select(dssa);

I am programming in VB

here is teh rest of my code.

Dim EventDataSource1 As New SqlDataSource()

EventDataSource1.ConnectionString = ConfigurationManager.ConnectionStrings("ASPNETDBConnectionString").ToString

Dim dssa As New DataSourceSelectArguments()

Dim EventID As String = ""

Dim DataView = ""

Dim dt As New Data.DataTable

Dim conn As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ASPNETDBConnectionString").ToString())

Dim cmd As New Data.SqlClient.SqlCommand("SELECT EventID FROM Event WHERE ([StartDate] = @.StartDate)", conn)

EventDataSource1.SelectCommand = ("SELECT EventID FROM Event WHERE ([StartDate] = @.StartDate)")

conn.Open()

dt = ((DataView)(EventDataSource1.Select(dssa))).ToTable()

EventID = dt.Rows(0)(0).ToString()

EventDataSource1.SelectParameters.Add("@.StartDate",StartDate)

EventID = cmd.ExecuteScalar()

Seems like you overdoing it. If all you want is the first row, first column value...try something like this.

Dim EventIDAs String =""Dim connAs New Data.SqlClient.SqlConnection("Connection String")Dim cmdAs New Data.SqlClient.SqlCommand("SELECT EventID FROM Event WHERE ([StartDate] = @.StartDate)", conn)'add parameters cmd.Parameters.Add(New SqlClient.SqlParameter("@.StartDate","01/01/2007"))'open connections cmd.Connection.Open()'get top row and column EventID = cmd.ExecuteScalar()'close connection cmd.Connection.Close()'clean up conn =Nothing cmd =Nothing

|||

That's all? Wow thanks!!

Just out of interest, what would I change if I didn't want the first column?

Thanks

|||here is a sample,

Dim EventIDAs String Dim drAs SqlDataReaderDim connAs New Data.SqlClient.SqlConnection("Connection String")Dim cmdAs New Data.SqlClient.SqlCommand("SELECT EventID FROM Event WHERE ([StartDate] = @.StartDate)", conn)'add parameters cmd.Parameters.Add(New SqlClient.SqlParameter("@.StartDate","01/01/2007"))'open connections cmd.Connection.Open()'get entire dataset dr = cmd.ExecuteReaderWhile dr.Read()If IsDBNull(dr.Item("EventID")) =False Then EventID = (dr.Item("EventID"))End If'do whatever you want witht the dataEnd While'close dr dr.Close()'close connection cmd.Connection.Close()'clean up conn =Nothing cmd =Nothing

just remember to close the reader.|||Thanks again. Everything works great! YEEE HAAA!!!!