SQL ODBC Probs

[Guest_imported]

I'm having trouble with a DTS VBScript. When I execute a query to SELECT records from an ODBC Data Source (Remedy), I get an empty recordset. The same query if run through Access on a linked table works fine and returns all the records. Any ideas?

Thanks

Ar

 

[rajeevnandanmishra]

Dear,

ODBC and Access parses the T-SQL in different way. More information about your problem required. If possible, write your SQL statements as it is, as you are passing to ODBC driver.

 

[Guest_imported]

I've tried lots of different SQL commands but none return anything. Here's the vb code ...


With cnRemedy
.Provider="MSDASQL"
.Properties("Data Source&quot.Value="Remedy ODBC Data Source"
.Open
End With

strSQL="SELECT * FROM ESH4:User"

rsRemedyCases.Open strSQL, cnRemedy


Can you see anything wrong with this?

Thanks,

Ar.

 

[calidrake]

Have you tried:

strSQL="SELECT * FROM ESH4.User"
(I'm assuming that ESH4 is the table owner)

The other syntax is good for acces but like rajee said, you're not going through access to get to the server. You are going directly there, and the syntax for access and TSQL are a little different.

 

[DougP]

Not sure what the ":User" is either???

Here is waht I use all the time with great success

' ---------- DSN less for SQL Server ------------------
Dim Conn As ADODB.Connection
Dim Rs1 As ADODB.Recordset

Set Conn = New ADODB.Connection
Set Rs1 = New ADODB.Recordset

Conn.Open "driver=SQL Server;server=yourserver;uid=sa;pwd=;database=yourdatabase;"
Dim SQLCode As String
SQLCode = "SELECT * FROM ESH4"

Rs1.Open SQLCode, Conn, adOpenStatic, adLockOptimistic

'Do some stuff with the recordset

Set Rs1 = Nothing
Set Conn = Nothing
DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive