Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Permission error does not come to the client

5 posts in Connectivity - ODBC Last posting was on 2012-01-30 18:31:35.0Z
George Brink Posted on 2011-12-23 20:40:28.0Z
From: George Brink <siberianowl@yahoo.com>
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:8.0) Gecko/20111105 Thunderbird/8.0
MIME-Version: 1.0
Newsgroups: sybase.public.connectivity.odbc
Subject: Permission error does not come to the client
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ef4e73c$1@forums-1-dub>
Date: 23 Dec 2011 12:40:28 -0800
X-Trace: forums-1-dub 1324672828 10.22.241.152 (23 Dec 2011 12:40:28 -0800)
X-Original-Trace: 23 Dec 2011 12:40:28 -0800, vip152.sybase.com
Lines: 43
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.connectivity.odbc:4713
Article PK: 117497

My environment:
Server: Adaptive Server Enterprise/15.0.3/EBF 17162 ESD#3/P/RS6000/AIX
5.3/ase1503/2726/64-bit/FBO/Thu Feb 4 21:48:22 2010
ODBC driver: Adaptive Server Enterprise 15.05.00.1022
Server accessed from VBS script through ADO.


I have a multy-statement query like this:
----- sqcript.sql
select *
into #tmp
from table1

update #tmp ...
delete from #tmp ...
-----

This query is executed as one SQL statement from VBS/ADO:

======
set fso = CreateObject("Scripting.FileSystemObject")
set fo = fso.OpenTextFile("script.sql", 1)
SQL_Statement = fo.ReadAll
fo.Close

set conn = CreateObject("ADODB.Connection")
conn.Open "DSN=MyDSN;UID=UserA;PWD=***"
set rs = conn.Execute(SQL_Statement)
======
MyDSN points to ASE 15.0.3 server through ODBC driver v15.05.00.1022

The problem is:
When I run this script, it failed on conn.Execute() with the error "no
such table #tmp". After a long investigation I found out that the real
problem was that the user UserA has no SELECT permissions on the table
table1.
My question is: why executing this batch as one statement fails on the
second statement of the batch? The real error about insufficient
permissions I receive only if I reduce the batch and leave the statement
select * into #tmp from table1
as the last statement of the batch.

Any thoughts?


NJS Posted on 2011-12-31 02:21:04.0Z
From: NJS <NJS@optonline.net>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:7.0.1) Gecko/20110929 Thunderbird/7.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.connectivity.odbc
Subject: Re: Permission error does not come to the client
References: <4ef4e73c$1@forums-1-dub>
In-Reply-To: <4ef4e73c$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4efe7190$1@forums-1-dub>
Date: 30 Dec 2011 18:21:04 -0800
X-Trace: forums-1-dub 1325298064 10.22.241.152 (30 Dec 2011 18:21:04 -0800)
X-Original-Trace: 30 Dec 2011 18:21:04 -0800, vip152.sybase.com
Lines: 50
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.connectivity.odbc:4714
Article PK: 117500

This is a known problem with certain statements in a batch, where all
the SQL stmts are parsed individually and hence the error as #temp is
yet to be created. The only option is to run in separate batches / or
use execute(@sql) (dynamic SQL exec).

On 12/23/2011 3:40 PM, George Brink wrote:
> My environment:
> Server: Adaptive Server Enterprise/15.0.3/EBF 17162 ESD#3/P/RS6000/AIX
> 5.3/ase1503/2726/64-bit/FBO/Thu Feb 4 21:48:22 2010
> ODBC driver: Adaptive Server Enterprise 15.05.00.1022
> Server accessed from VBS script through ADO.
>
>
> I have a multy-statement query like this:
> ----- sqcript.sql
> select *
> into #tmp
> from table1
>
> update #tmp ...
> delete from #tmp ...
> -----
>
> This query is executed as one SQL statement from VBS/ADO:
>
> ======
> set fso = CreateObject("Scripting.FileSystemObject")
> set fo = fso.OpenTextFile("script.sql", 1)
> SQL_Statement = fo.ReadAll
> fo.Close
>
> set conn = CreateObject("ADODB.Connection")
> conn.Open "DSN=MyDSN;UID=UserA;PWD=***"
> set rs = conn.Execute(SQL_Statement)
> ======
> MyDSN points to ASE 15.0.3 server through ODBC driver v15.05.00.1022
>
> The problem is:
> When I run this script, it failed on conn.Execute() with the error "no
> such table #tmp". After a long investigation I found out that the real
> problem was that the user UserA has no SELECT permissions on the table
> table1.
> My question is: why executing this batch as one statement fails on the
> second statement of the batch? The real error about insufficient
> permissions I receive only if I reduce the batch and leave the statement
> select * into #tmp from table1
> as the last statement of the batch.
>
> Any thoughts?


Paul Vero [Sybase] Posted on 2012-01-05 00:18:14.0Z
From: "Paul Vero [Sybase]" <pvero@nospam.com>
Newsgroups: sybase.public.connectivity.odbc
References: <4ef4e73c$1@forums-1-dub>
Subject: Re: Permission error does not come to the client
Lines: 56
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.6109
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4f04ec46@forums-1-dub>
Date: 4 Jan 2012 16:18:14 -0800
X-Trace: forums-1-dub 1325722694 10.22.241.152 (4 Jan 2012 16:18:14 -0800)
X-Original-Trace: 4 Jan 2012 16:18:14 -0800, vip152.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.connectivity.odbc:4716
Article PK: 117501

Hi George,

What's the TDS look like with the batch? Are there several messages?
I'm not sure how vbs handles this - you may need to loop through the errors
collection to get everything out.

Thank you,
-Paul

"George Brink" <siberianowl@yahoo.com> wrote in message
news:4ef4e73c$1@forums-1-dub...
> My environment:
> Server: Adaptive Server Enterprise/15.0.3/EBF 17162 ESD#3/P/RS6000/AIX
> 5.3/ase1503/2726/64-bit/FBO/Thu Feb 4 21:48:22 2010
> ODBC driver: Adaptive Server Enterprise 15.05.00.1022
> Server accessed from VBS script through ADO.
>
>
> I have a multy-statement query like this:
> ----- sqcript.sql
> select *
> into #tmp
> from table1
>
> update #tmp ...
> delete from #tmp ...
> -----
>
> This query is executed as one SQL statement from VBS/ADO:
>
> ======
> set fso = CreateObject("Scripting.FileSystemObject")
> set fo = fso.OpenTextFile("script.sql", 1)
> SQL_Statement = fo.ReadAll
> fo.Close
>
> set conn = CreateObject("ADODB.Connection")
> conn.Open "DSN=MyDSN;UID=UserA;PWD=***"
> set rs = conn.Execute(SQL_Statement)
> ======
> MyDSN points to ASE 15.0.3 server through ODBC driver v15.05.00.1022
>
> The problem is:
> When I run this script, it failed on conn.Execute() with the error "no
> such table #tmp". After a long investigation I found out that the real
> problem was that the user UserA has no SELECT permissions on the table
> table1.
> My question is: why executing this batch as one statement fails on the
> second statement of the batch? The real error about insufficient
> permissions I receive only if I reduce the batch and leave the statement
> select * into #tmp from table1
> as the last statement of the batch.
>
> Any thoughts?


George Brink Posted on 2012-01-09 18:42:41.0Z
From: George Brink <siberianowl@yahoo.com>
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:8.0) Gecko/20111105 Thunderbird/8.0
MIME-Version: 1.0
Newsgroups: sybase.public.connectivity.odbc
Subject: Re: Permission error does not come to the client
References: <4ef4e73c$1@forums-1-dub> <4f04ec46@forums-1-dub>
In-Reply-To: <4f04ec46@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4f0b3521$1@forums-1-dub>
Date: 9 Jan 2012 10:42:41 -0800
X-Trace: forums-1-dub 1326134561 10.22.241.152 (9 Jan 2012 10:42:41 -0800)
X-Original-Trace: 9 Jan 2012 10:42:41 -0800, vip152.sybase.com
Lines: 133
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.connectivity.odbc:4717
Article PK: 117502

TDS? Sorry, I do not understand the question.

And "No". There is only one message - about non existent #tmp table.
Here is the reduced VBS script, which shows the problem:
------------------
Connection_String = "DSN=MyDSN;UID=****;PWD=****"
SQL_Statement = _
"select password into #tmp from master..syslogins " & _
"select * from #tmp"

set conn = CreateObject("ADODB.Connection")
conn.Open Connection_String
set rs = conn.Execute(SQL_Statement)

do while not rs.eof
wscript.echo rs(0)
rs.MoveNext
loop

rs.Close
conn.Close
------------------
just save it as test.vbs, correct the connection string and run under a
user without sa privileges (usually no one except sa has access to
syslogins.password field)
When I run it from a console, I receive an error:
============================
C:\Projects>cscript /nologo test.vbs
C:\Projects\test.vbs(8, 1) Microsoft OLE DB Provider for ODBC Drivers:
[Sybase][ODBC Driver][Adaptive Server Enterprise]#tmp not found. Specify
owner.objectname or use sp_help to check whether the object exists
(sp_help may produce lots of output).
============================


Removing the second command from the statement:
SQL_Statement = "select password into #tmp from master..syslogins"
produces another error:
============================
C:\Projects>cscript /nologo test.vbs
C:\Projects\test.vbs(6, 1) Microsoft OLE DB Provider for ODBC Drivers:
[Sybase][ODBC Driver][Adaptive Server Enterprise]SELECT permission
denied on column password of object syslogins, database master, owner dbo
============================


As you can see, I do not have any manual error checking inside my vbs
script - the error is reported by the ADO/WSH itself.
If I do manual checking:
-------
on error resume next
set rs = conn.Execute(SQL_Statement)
for each e in conn.Errors
wscript.echo e
next
on error goto 0
if conn.Errors.Count > 0 then wscript.quit(1)
-------
I see two errors:
============================
C:\Projects>cscript /nologo test.vbs
[Sybase][ODBC Driver][Adaptive Server Enterprise]#tmp not found. Specify
owner.objectname or use sp_help to check whether the object exists
(sp_help may produce lots of output).

[Sybase][ODBC Driver][Adaptive Server Enterprise]SELECT permission
denied on column password of object syslogins, database master, owner dbo
============================

Apparently, the default WSH's error handler shows the first error in the
list and aborts the script. So now my question is: why are errors in the
list in the reversed order?

On 1/4/2012 7:18 PM, Paul Vero [Sybase] wrote:
> Hi George,
>
> What's the TDS look like with the batch? Are there several messages?
> I'm not sure how vbs handles this - you may need to loop through the errors
> collection to get everything out.
>
> Thank you,
> -Paul
>
> "George Brink"<siberianowl@yahoo.com> wrote in message
> news:4ef4e73c$1@forums-1-dub...
>> My environment:
>> Server: Adaptive Server Enterprise/15.0.3/EBF 17162 ESD#3/P/RS6000/AIX
>> 5.3/ase1503/2726/64-bit/FBO/Thu Feb 4 21:48:22 2010
>> ODBC driver: Adaptive Server Enterprise 15.05.00.1022
>> Server accessed from VBS script through ADO.
>>
>>
>> I have a multy-statement query like this:
>> ----- sqcript.sql
>> select *
>> into #tmp
>> from table1
>>
>> update #tmp ...
>> delete from #tmp ...
>> -----
>>
>> This query is executed as one SQL statement from VBS/ADO:
>>
>> ======
>> set fso = CreateObject("Scripting.FileSystemObject")
>> set fo = fso.OpenTextFile("script.sql", 1)
>> SQL_Statement = fo.ReadAll
>> fo.Close
>>
>> set conn = CreateObject("ADODB.Connection")
>> conn.Open "DSN=MyDSN;UID=UserA;PWD=***"
>> set rs = conn.Execute(SQL_Statement)
>> ======
>> MyDSN points to ASE 15.0.3 server through ODBC driver v15.05.00.1022
>>
>> The problem is:
>> When I run this script, it failed on conn.Execute() with the error "no
>> such table #tmp". After a long investigation I found out that the real
>> problem was that the user UserA has no SELECT permissions on the table
>> table1.
>> My question is: why executing this batch as one statement fails on the
>> second statement of the batch? The real error about insufficient
>> permissions I receive only if I reduce the batch and leave the statement
>> select * into #tmp from table1
>> as the last statement of the batch.
>>
>> Any thoughts?
>
>


Paul Vero [Sybase] Posted on 2012-01-30 18:31:35.0Z
From: "Paul Vero [Sybase]" <pvero@nospam.com>
Newsgroups: sybase.public.connectivity.odbc
References: <4ef4e73c$1@forums-1-dub> <4f04ec46@forums-1-dub> <4f0b3521$1@forums-1-dub>
Subject: Re: Permission error does not come to the client
Lines: 341
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.6157
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4f26e207@forums-1-dub>
Date: 30 Jan 2012 10:31:35 -0800
X-Trace: forums-1-dub 1327948295 10.22.241.152 (30 Jan 2012 10:31:35 -0800)
X-Original-Trace: 30 Jan 2012 10:31:35 -0800, vip152.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.connectivity.odbc:4718
Article PK: 117503

Hi George,

I got this ADO code to work - you should be able to modify the script.

First thing I find is with ODBC the CursorLocation adUseServer doesn't get
the messages and throws this sort of error:

Error #-2147217887
ODBC driver does not support the requested properties.
(Source: Microsoft OLE DB Provider for ODBC Drivers)
(SQL State: )
(NativeError: 0)


When I set teh CursorLocation to adUseClient I get the messages but I have
to loop through the Errors collection since there are 2 errors (TDS is
tabular data stream - how the client and ase communicate. I use this to
confirm the information going back and forth. We provide a utility in the
SDK , RIBO, to gather this sort of information)

I get the 2 errors - the #tmp not found and then the SELECT permission
denied [the driver pops these off the TDS and gets the last message first]

Error #-2147217911
[Sybase][ODBC Driver][Adaptive Server Enterprise]#tmp not found. Specify
owner.objectname or use sp_help to check whether the object exists (sp_help
may produce lots of output).

(Source: Microsoft OLE DB Provider for ODBC Drivers)
(SQL State: 42000)
(NativeError: 208)

Error #-2147217911
[Sybase][ODBC Driver][Adaptive Server Enterprise]SELECT permission denied
on column password of object syslogins, database master, owner dbo

(Source: Microsoft OLE DB Provider for ODBC Drivers)
(SQL State: ZZZZZ)
(NativeError: 10332)

With the native OLEDB Provider, and using CursorLocation = adUseServer (this
is default setting by the way)

Error #0
[ZZZZZ]
[Message Class: 14]
[Message State: 1]
[Transaction State: 1]
[Server Name: pvxp1570]
[Native Code: 10332]
[ASEOLEDB]SELECT permission denied on column password of object syslogins,
database master, owner dbo

(Source: ASEOLEDB)
(SQL State: )
(NativeError: 10332)

Error #0
[42000]
[Message Class: 16]
[Message State: 1]
[Transaction State: 1]
[Server Name: pvxp1570]
[Native Code: 208]
[ASEOLEDB]#tmp not found. Specify owner.objectname or use sp_help to check
whether the object exists (sp_help may produce lots of output).

(Source: ASEOLEDB)
(SQL State: )
(NativeError: 208)

The messages are received in order.

With MSSQL Server only the SELECT permission error is sent back by MSSQL
Server.

This is the code I used - you should be able to incoprorate the errors
collection and CursorLocation stuff into your script.


VB/ADO Code:

'SybSamps for ADO
'
Option Explicit
Private Sub cmdExit_Click()
On Error Resume Next
Unload Me
Set Form1 = Nothing
End Sub
Private Sub cmdTest_Click()

Dim sql As String ' sql statement
Dim connStr As String ' connection string
Dim lstr As String ' String that contains record data
Dim fieldStr As String ' field info string
Dim firstRow As Boolean ' true if on first Row - to be used to make field
string
Dim tmpStr As String


Dim sybConn As ADODB.Connection 'ADO Connection object
Dim sybRst As ADODB.Recordset 'ADO Recordset object
Dim sybFld As ADODB.Field 'ADO Field object
Dim sybFld2 As ADODB.Field 'ADO Field Object to collect column info
Dim errLoop As ADODB.Error 'ADO Error object
Dim strError As String

On Error GoTo ErrTrap

Set sybConn = New ADODB.Connection
Set sybRst = New ADODB.Recordset

'Connection object information
'sybConn.CursorLocation = adUseClient
'sybConn.CursorLocation = adUseServer
sybConn.ConnectionTimeout = 10 'login timeout
'sybConn.CommandTimeout = 10 'query timeout

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Sybase ASE ODBC Driver Connection string - uses DSN
'sybConn.Open "DSN=ase1570;UID=pvero;PWD=pvero1;", userNameTextBox.Text,
passWordTextBox.Text

' MSSQL Server
sybConn.Open "DSN=pvms2008r2;UID=testuser;PWD=Sybase2008;",
userNameTextBox.Text, passWordTextBox.Text

'ASE Provider
'sybConn.Open "Provider=ASEOLEDB;Data Source=ase1570;UID=pvero;PWD=pvero1;",
userNameTextBox.Text, passWordTextBox.Text


' SQL Statement to send to ASE
sql = "select password into #tmp from master..syslogins select * from #tmp"
'sybRst.ActiveConnection = sybConn

sybRst.Open sql, sybConn, adOpenStatic, adLockOptimistic, adCmdText
'sybRst.Open sql, sybConn, adOpenStatic, adLockReadOnly, adCmdText

'sybRst.Open sql, sybConn, adOpenDynamic, adLockOptimistic, adCmdText
'sybRst.Open sql, sybConn, adOpenForwardOnly, adLockOptimistic, adCmdText


' This loops over the sybRst object and will display the data values in each
row
Do While Not sybRst.EOF
lstr = ""
For Each sybFld In sybRst.Fields
lstr = lstr & sybFld.Value & ","
Next
lstr = Left(lstr, Len(lstr) - 1)
List1.AddItem lstr
sybRst.MoveNext
Loop
List1.AddItem "=============================================="


MsgBox "RecordCount is " & sybRst.RecordCount
'Debug.Print "RecordCount is " & sybRst.RecordCount

'Housecleaning - close all ADO objects used
'field objects are destroyed when Recordset object is destroyed
sybRst.Close
sybConn.Close
Set sybRst = Nothing
Set sybConn = Nothing

Exit Sub

'Basic error handling when error is trapped
ErrTrap:
If Err.Number = 5 Then
lstr = lstr & Err.Description & ","
Resume Next
End If
'We get description of error, the ADO number and the Source
'MsgBox "There has been an error" & vbCrLf & " " & Err.Description & " " &
Err.Number & " " & Err.Source
' Another method to collect error information
' Enumerate Errors collection and display
' properties of each Error object.
For Each errLoop In sybConn.Errors
' Message Box to pop out the error
MsgBox "There has been an error" & vbCrLf & " " & errLoop.Description
& " " & errLoop.Number & " " & errLoop.Source

strError = "Error #" & errLoop.Number & vbCr & _
" " & errLoop.Description & vbCr & _
" (Source: " & errLoop.Source & ")" & vbCr & _
" (SQL State: " & errLoop.SQLState & ")" & vbCr & _
" (NativeError: " & errLoop.NativeError & ")" & vbCr


Debug.Print strError
Next


End Sub

"George Brink" <siberianowl@yahoo.com> wrote in message
news:4f0b3521$1@forums-1-dub...
> TDS? Sorry, I do not understand the question.
>
> And "No". There is only one message - about non existent #tmp table.
> Here is the reduced VBS script, which shows the problem:
> ------------------
> Connection_String = "DSN=MyDSN;UID=****;PWD=****"
> SQL_Statement = _
> "select password into #tmp from master..syslogins " & _
> "select * from #tmp"
>
> set conn = CreateObject("ADODB.Connection")
> conn.Open Connection_String
> set rs = conn.Execute(SQL_Statement)
>
> do while not rs.eof
> wscript.echo rs(0)
> rs.MoveNext
> loop
>
> rs.Close
> conn.Close
> ------------------
> just save it as test.vbs, correct the connection string and run under a
> user without sa privileges (usually no one except sa has access to
> syslogins.password field)
> When I run it from a console, I receive an error:
> ============================
> C:\Projects>cscript /nologo test.vbs
> C:\Projects\test.vbs(8, 1) Microsoft OLE DB Provider for ODBC Drivers:
> [Sybase][ODBC Driver][Adaptive Server Enterprise]#tmp not found. Specify
> owner.objectname or use sp_help to check whether the object exists
> (sp_help may produce lots of output).
> ============================
>
>
> Removing the second command from the statement:
> SQL_Statement = "select password into #tmp from master..syslogins"
> produces another error:
> ============================
> C:\Projects>cscript /nologo test.vbs
> C:\Projects\test.vbs(6, 1) Microsoft OLE DB Provider for ODBC Drivers:
> [Sybase][ODBC Driver][Adaptive Server Enterprise]SELECT permission denied
> on column password of object syslogins, database master, owner dbo
> ============================
>
>
> As you can see, I do not have any manual error checking inside my vbs
> script - the error is reported by the ADO/WSH itself.
> If I do manual checking:
> -------
> on error resume next
> set rs = conn.Execute(SQL_Statement)
> for each e in conn.Errors
> wscript.echo e
> next
> on error goto 0
> if conn.Errors.Count > 0 then wscript.quit(1)
> -------
> I see two errors:
> ============================
> C:\Projects>cscript /nologo test.vbs
> [Sybase][ODBC Driver][Adaptive Server Enterprise]#tmp not found. Specify
> owner.objectname or use sp_help to check whether the object exists
> (sp_help may produce lots of output).
>
> [Sybase][ODBC Driver][Adaptive Server Enterprise]SELECT permission denied
> on column password of object syslogins, database master, owner dbo
> ============================
>
> Apparently, the default WSH's error handler shows the first error in the
> list and aborts the script. So now my question is: why are errors in the
> list in the reversed order?
>
>
>
> On 1/4/2012 7:18 PM, Paul Vero [Sybase] wrote:
>> Hi George,
>>
>> What's the TDS look like with the batch? Are there several messages?
>> I'm not sure how vbs handles this - you may need to loop through the
>> errors
>> collection to get everything out.
>>
>> Thank you,
>> -Paul
>>
>> "George Brink"<siberianowl@yahoo.com> wrote in message
>> news:4ef4e73c$1@forums-1-dub...
>>> My environment:
>>> Server: Adaptive Server Enterprise/15.0.3/EBF 17162 ESD#3/P/RS6000/AIX
>>> 5.3/ase1503/2726/64-bit/FBO/Thu Feb 4 21:48:22 2010
>>> ODBC driver: Adaptive Server Enterprise 15.05.00.1022
>>> Server accessed from VBS script through ADO.
>>>
>>>
>>> I have a multy-statement query like this:
>>> ----- sqcript.sql
>>> select *
>>> into #tmp
>>> from table1
>>>
>>> update #tmp ...
>>> delete from #tmp ...
>>> -----
>>>
>>> This query is executed as one SQL statement from VBS/ADO:
>>>
>>> ======
>>> set fso = CreateObject("Scripting.FileSystemObject")
>>> set fo = fso.OpenTextFile("script.sql", 1)
>>> SQL_Statement = fo.ReadAll
>>> fo.Close
>>>
>>> set conn = CreateObject("ADODB.Connection")
>>> conn.Open "DSN=MyDSN;UID=UserA;PWD=***"
>>> set rs = conn.Execute(SQL_Statement)
>>> ======
>>> MyDSN points to ASE 15.0.3 server through ODBC driver v15.05.00.1022
>>>
>>> The problem is:
>>> When I run this script, it failed on conn.Execute() with the error "no
>>> such table #tmp". After a long investigation I found out that the real
>>> problem was that the user UserA has no SELECT permissions on the table
>>> table1.
>>> My question is: why executing this batch as one statement fails on the
>>> second statement of the batch? The real error about insufficient
>>> permissions I receive only if I reduce the batch and leave the statement
>>> select * into #tmp from table1
>>> as the last statement of the batch.
>>>
>>> Any thoughts?
>>
>>
>