Question Trying to run a Sybase Script (with Begin and End)

[VantiveGuy]

Hi,

I have a VB.Net (vs2005) application that connects to Sybase database and runs queries (and returns to grid, to XML and to Stye Sheet) but I am having a big problem trying to execute SQL that is written as a script, i.e contains a Begin and End statement, and creates and uses temp tables.
The script runs fine when executed in Sybase, but when I execute in VB.Net I find that the recordset is closed.
I am setting up the connection as :
connectstr = "Driver={SYBASE ASE ODBC Driver};Srvr='" & strServer & "';db='" & strDB & "';Uid='" & strUser & "';Pwd='" & strPass & "'"

Dim conn As Connection = New Connection
Dim connMode As Integer = ConnectModeEnum.adModeUnknown
conn.CommandTimeout = 300

and then executing the SQL to return into a recordset with :
Try
rs = conn.Execute(sQry)
Catch ex As Exception
MsgBox(ex.Message)
conn.Close()
Form2.Close()
Exit Sub
End Try

the conn.Execute runs, and I get no exception, but when I try to check for rs.EOF I get an error saying "Operation is not allowed when the object is closed."

I can't understand why it is saying that the object is closed. At the least, I should be able to test for BOF or EOF of the recordset! (It does return data when executed in Sybase)

Other queries work just fine using the same code, so I suspect that VB.Net has a problem with the SQL, which is copied below.
Basically, the query is used to find dependencies and works by searching syscomments (joining two lines to miss anything that bridges two lines).
I have to also say I claim no credit for the code.
It works when called as a stored procedure, but I can't create new stored procs on some environments so I want it to run as a script.
This is the script:

begin
declare @tabname2 varchar(72)
Select @tabname2 = 'MY_TABLE'
declare @tabname varchar(72)
set @tabname = '%' + @tabname2 + '%'
select com.id, com.text + isnull(com2.text,'') text
into #syscom
from syscomments com
left outer join syscomments com2
on com2.id = com.id
and com2.colid = com.colid + 1
select distinct '%' + object_name(com.id) + '%' Name, ob.type Type
into #tbvw
from #syscom com, sysobjects ob
where text like @tabname
and ob.id = com.id
and ob.type = 'V'
union
select @tabname, 'U'
Insert into #tbvw Select o.name , o.type from sysdepends d, sysobjects o where d.depid =
(select id from sysobjects where name = @tabname2)
and d.id = o.id
select distinct object_name(com.id) Name --, ob.type Type
from #syscom com, sysobjects ob, #tbvw
where com.text like #tbvw.Name
and ob.id = com.id
and ob.name <> @tabname2
Union Select swName Name from SW_BASIC_SCRIPT where swText like @tabname
Union Select RTRIM(n.swName)+"/ScreenEvent/"+e.swScreenName from SW_SCREEN_EVENT e, SW_EVENT_ENUM n Where e.swEventEnum = swEnum and swUserProgram like @tabname
Union Select RTRIM(n.swName)+"/"+RTRIM(e.swScreenName) +"/"+f.swColumnPath from SW_FIELD_EVENT e, SW_SCREEN_FIELD f, SW_EVENT_ENUM n
Where e.swScreenFieldId = f.swScreenFieldId and e.swEventEnum = n.swEnum and swUserProgram like @tabname
Union Select "Derived/"+swScreenName from SW_DERIVED where swStatement like @tabname
Order by Name
Drop table #syscom
Drop table #tbvw
end

Can anyone please tell me why VB.NET cannot handle this when executed as SQL?

Many thanks for any advice.

 

[VantiveGuy]

Problem narrowed down

I have been trying to isolate exactly what the problem is, and I have found that it is a problem when variables are used within the script.
For example, this gives the same error.
begin
Declare @Name Varchar(25)
Select @Name = 'MY_TABLE'
Select * from sysobjects where name = @Name
End

How do I call queries like this using VN.Net? Please note that my actual script is a lot longer and uses temp tables (my first post includes full script) so I still need to have the script within a Begin/End block.
Of course, once this part is resolved I might find I have problems with the temp table usage so I will also test for that separately.

Thanks for any help.

 

[VantiveGuy]

Yep, Temp tables also a problem

I just checked to see if I could use sybase temp tables as part of the query block and had the same problem.
This also fails.
Begin
Select * into #prods from MY_TABLE
Select * from #prods
drop table #prods
End

I have checked other code blocks with begin/End statements and they work OK provided they do not use variables or temp tables.

Any ideas? This is killing a potentially great little app.