# Miscellaneous > Ask an Expert >  No cursor was declared

## dougb

I am at present constructing a little program in Visual Basic 6 to extract some data from an Microsoft SQL server running our main database within the college and have run into a little problem that has me stumped.
I have been using an MS access database that represents the final target database (Table names, Field names etc are identical with test data) and have developed an SQL select statement that works fine from within VB code on this access database  :Smilie: . But when I redirect the same SQL statement to the SQL server I get the following error. :Frown: 

Run-time error 40002:
37000:[Microsoft][ODBC SQL server driver][SQL server]The cursor was not declared.

SQL Statement is as follows:

SELECT ST.STUD_Student_ID , ST.STUD_Surname_S02 , ST.STUD_Forname_1_S03, se.STEN_Student_ID, se.STEN_Prog_code FROM STUDstudent AS ST, STENenrols AS se, st INNER JOIN se ON st.stud_student_id=se.sten_student_id WHERE se.sten_expctd_end_date__q17 > #" & Date & "# and se.sten_actual_end_date__q18 IS null

Database Structure
Table STENenrols
	STEN_Student_ID	Key field
STEN_College_Year
	STEN_Prog_Code
	STEN_Type_Of_Record
	STEN_Actual_End_Date__Q18
	STEN_Expctd_End_Date__Q17
	STEN_Completion_Stat_Q19

Table STUDstudent
	STUD_Student_ID	key field
	STUD_College_Year
	STUD_Surname_s02
	STUD_Forename_1_s03
	STUD_Primary_Programme

Is there some thing I have overlooked with regard to differences between ACCESS and MS SQL Server?
 :Confused:

----------


## skhanal

Your join statement needs to be changed, try this


SELECT ST.STUD_Student_ID , ST.STUD_Surname_S02 , ST.STUD_Forname_1_S03, se.STEN_Student_ID, se.STEN_Prog_code FROM STUDstudent AS ST
INNER JOIN STENenrols AS se
ON st.stud_student_id=se.sten_student_id WHERE se.sten_expctd_end_date__q17 >  getdate() and se.sten_actual_end_date__q18 IS null


If you are trying to get current date, use getdate() function instead of Date.

----------


## dougb

I have just tried your suggested code and got the same error again. The date part when tried on the access database fails when reverted to my original bit of code for date the select statement works fine on access but not the SQL server :Confused:  
Just to rule out the date I have now entered a fixed date yet again this works fine with the access database but not the SQL server.

----------


## skhanal

Can you run the SELECT statement in Query Analyzer.

I don't see anything wrong with the SQL Statement.

Are you using ODBC to make a connection? Your version of ODBC may not support ANSI join syntax, try this one if you have to use ODBC



SELECT ST.STUD_Student_ID , ST.STUD_Surname_S02 , ST.STUD_Forname_1_S03, se.STEN_Student_ID, se.STEN_Prog_code FROM STUDstudent AS ST, STENenrols AS se
WHERE 
st.stud_student_id=se.sten_student_id and se.sten_expctd_end_date__q17 > getdate() and se.sten_actual_end_date__q18 IS null

----------


## dougb

To answer your question yes I am using ODBC to talk to the SQL database server.
Did a bit more research yesterday and found some information on Microsoft site (Knowledge base article 154825) that suggested using the following code to force the ODBC to use local cursors not server cursors and all appears to be working fine for me now. :Big Grin:  

Thank you for your guidance.  :Cool: 

The following line was added in to my VB code
rdoEnvironments(0).CursorDriver = rdUseOdbc

----------


## DriverToBiker

rdoEnvironments(0).CursorDriver = rdUseOdbc

    Set Env1 = rdoEngine.rdoEnvironments(0)
    Set CN = Env1.OpenConnection(odbcBud, rdDriverNoPrompt, False, sCon)
   CN.QueryTimeout = 0

     SQL1 = "SELECT * " & _
                "FROM BorramePresupuesto " & _
                "WHERE (len(CENTRAB)>16 or len(DEPTO)>16 or len(MONEDA)>1 or len(CVE_EGRESO)>1 or " & _
                      "len(TIPOREC)>2 or len(RENGLON)>3 or len(TIPO_PPTO)>1 or len(CVE_GASTO)>1 or " & _
                      "len(PROYECTO)>10 or len(OBRA)>11 or len(SUBDIR)>4 or len(PIN_PEF)>24 or len(CONOR)> 6) "


    Set Rset1 = cn.OpenResultset(SQL1, rdOpenKeyset, rdConcurReadOnly, rdExecDirect)

    If Rset1.RowCount > 0 Then





> To answer your question yes I am using ODBC to talk to the SQL database server.
> Did a bit more research yesterday and found some information on Microsoft site (Knowledge base article 154825) that suggested using the following code to force the ODBC to use local cursors not server cursors and all appears to be working fine for me now. 
> 
> Thank you for your guidance. 
> 
> The following line was added in to my VB code
> rdoEnvironments(0).CursorDriver = rdUseOdbc

----------

