I'm using SAP's SQLAnywhere 17 .NET library to connect to a database (in PowerShell), but can't find anything on the internet for the connection string format.

My credentials are as follows:

Host: 10.10.10.10:12345 Server: testserver UID: DBA Password: 123 Database: testdb

This string does not work; gives me "The user 'SQL Anywhere' does not exist."

Host=10.10.10.10:12345;Server=testserver;DatabaseName=testdb;UserID=DBA;Password=123

This string generated by SQL Central does not work; gives me "The user 'SQL Anywhere' does not exist."

UID=DBA;PWD=123;Server=testserver;dbn=testdb;ASTART=No;host=10.10.10.10:12345

This is the stack trace generated by .NET:

 Sap.Data.SQLAnywhere.SAException (0x80004005): User ID 'SQLAnywhere' does not exist
   at Sap.Data.SQLAnywhere.SACommand._ExecuteReader(CommandBehavior commandBehavior, Boolean isExecuteScalar, Boolean isBeginExecuteReader)
   at Sap.Data.SQLAnywhere.SACommand.ExecuteReader()
   at Sap.Data.SQLAnywhere.SADataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
   at CallSite.Target(Closure , CallSite , Object , Object )

asked 12 Jan, 10:30

tyteen4a03's gravatar image

tyteen4a03
613412
accept rate: 100%

edited 17 Jan, 16:22

JBSchueler's gravatar image

JBSchueler
2.2k2939

There were a number of responses and comments posted for this same question on the SQL Anywhere SCN.

SQL Anywhere would report the error -103 Invalid User ID or password if you supplied the incorrect credentials. SQL Anywhere does not report the message "The user '%s' does not exist."

Given that this is seen in both the .NET and Sybase Central connections, there must be a connect event or custom login_procedure that is causing this to occur, i.e., code that was written specific to the database.

If you get a stack trace of the exception thrown for the error in .NET, it may point you in the right direction.

(12 Jan, 10:54) Chris Keating
Replies hidden

I can connect to the database via SQL Central - the problem is only with the .NET connector. I have included the .NET stack trace. How would I check, via SQL Central, of the settings you mentioned?

(12 Jan, 12:35) tyteen4a03

I will need to correct myself. This error is returned from SQL Anywhere as sql code -140. I am not sure why I missed the error when I looked at this yesterday.

(12 Jan, 13:34) Chris Keating
1

Can you expand on the statement that is being executed? It is most likely that this statement or any side effects such as triggers or procedure executing hits a reference to the user SQLAnywhere which is not defined in the database. Please note that the exception has no space and but in prior text there is a space.

(12 Jan, 13:36) Chris Keating

It's selecting a view with a few JOINs to other tables.

(12 Jan, 13:41) tyteen4a03

Have you looked at the statement and the objects it calls to see if “SQLAnywhere” is referenced. This is going to (likely) be a schema issue and not a product issue.

(14 Jan, 20:42) Chris Keating
Replies hidden

Not as far as I can see. I've tried adding the "SQLAnywhere" account to shut it up, and now there's another error: Procedure 'SACommand' Not Found". This is very confusing as a) The Stack Trace doesn't output anything useful; b) the SQL runs fine on SQL Central.

(15 Jan, 05:36) tyteen4a03

Just to clarify:

When you test with SQL Central, do you use the same login credentials and exactly the same query as with .Net?

And does the .NET connection also fail if you issue only a simple query like "select * from sys.dummy"?

(15 Jan, 05:45) Volker Barth

Yes (except for using prepared statements with WHERE)

(15 Jan, 05:46) tyteen4a03

Sap.Data.SQLAnywhere.SAException (0x80004005): Procedure 'SACommand' not found at Sap.Data.SQLAnywhere.SACommand._ExecuteReader(CommandBehavior commandBehavior, Boolean isExecuteScalar, Boolean isBeginExecuteReader) at Sap.Data.SQLAnywhere.SACommand.ExecuteReader() at Sap.Data.SQLAnywhere.SADataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) at CallSite.Target(Closure , CallSite , Object , Object )

NativeError -265, Error Code -2147467259.

(15 Jan, 06:01) tyteen4a03

This seems like the environment misinterpretes component names like "SQLAnywhere" and "SACommand" with database objects...

Can you show us the whole Powershell script?

And how do you supply the SA types to your script, via AddType with what parameters?

(15 Jan, 06:36) Volker Barth
Comment Text Removed

What version and build are you seeing this issue?

Is this a database that you can provide for investigation - depending on the specifics of the query, it may not require data just the schema. If so, providing a code sample showing the 2 issues would also be helpful. I would suggest a technical support case if you have support or alternatively you can send my email first.last@sap.com for review.

I also wonder if getting a request level log for both queries for the working SQL Central and failing .NET cases. Request level logging can be enabled at the command line with:

-zr sql+hostvars+procedures -zo <filespec>

This can also be enabled using SQL as:

call sa_server_option( 'RequestLogging','sql+hostvars+procedures'); call sa_server_option( 'RequestLogFile', '<filespec>');

To turn off logging

call sa_server_option( 'RequestLogging','none'); call sa_server_option( 'RequestLogFile', '');

(15 Jan, 09:54) Chris Keating

16.0.0.2322, running on Linux. It's part of NetBackup OpsCenter. I'll drop a ticket.

(15 Jan, 10:00) tyteen4a03

The SQL queries you gave me seems to have killed the server instead: "Database server not found"

(15 Jan, 10:05) tyteen4a03

Those statements should not result in any issues with the server. Did you set a valid filespec for the <filespec> placeholder. Windows server did not have problems with setting the RRL to <filespec> but perhaps this is an issue on LINUX.

(15 Jan, 10:17) Chris Keating

<scold> When reporting issues, it is not helpful to state some approximation of the error message that you got. You said the following which was far enough away from the truth to be quite misleading.

This string does not work; gives me "The user 'SQL Anywhere' does not exist."

The actual message you got was very likely something like the below which tells you are at the point in your code where you are calling "Fill" and well beyond any database connection issues.

Exception calling "Fill" with "1" argument(s): "User ID 'SQLAnywhere' does not exist" </scold>

(17 Jan, 16:26) JBSchueler

You must have missed the stack trace I posted. Next time please actually read my post before being condescending.

(17 Jan, 16:53) tyteen4a03

Sorry. I thought you had started the post with a statement to the effect that there was something wrong with your connection string and quoted the message that you were seeing.

This string does not work; gives me "The user 'SQL Anywhere' does not exist."

But, as you point out, the stack trace is there and it shows that you are well past the point where you have made a connection to the database so the connection string has nothing to do with this issue.

(17 Jan, 18:36) JBSchueler
More comments hidden
showing 5 of 19 show all flat view

Your original code looked something like this ...

[Sap.Data.SQLAnywhere.SACommand] $saCommand = New-Object Sap.Data.SQLAnywhere.SACommand($command, $conn)
[Sap.Data.SQLAnywhere.SAParameter] $parameter = New-Object Sap.Data.SQLAnywhere.SAParameter("", $variable)
[void] $saCommand.Parameters.Add($parameter)
[System.Data.DataSet] $dataset = New-Object System.Data.DataSet
[Sap.Data.SQLAnywhere.SADataAdapter] $adapter = New-Object Sap.Data.SQLAnywhere.SADataAdapter($saCommand, $conn)

There is no SADataAdapter overload that takes a command and connection object, but there is a best fit for string and connection object. So PowerShell converted your $saCommand object to the string form of the object type (Sap.Data.SQLAnywhere.SACommand) and that string is executed on the server as "CALL Sap.Data.SQLAnywhere.SACommand()". The owner name is SQLAnywhere. That user ID does not exist.

You really want the SADataAdapter(SACommand) overload. The corrected code should read as follows:

[Sap.Data.SQLAnywhere.SADataAdapter] $adapter = New-Object Sap.Data.SQLAnywhere.SADataAdapter($saCommand)

To see the effect that you were getting try this: Write-Output "$saCommand"

permanent link

answered 17 Jan, 16:19

JBSchueler's gravatar image

JBSchueler
2.2k2939
accept rate: 16%

edited 17 Jan, 16:19

A PowerShell debugging tip. Write your exception handler like this.

} catch {
    $e = $_.Exception
    $line = $_.InvocationInfo.ScriptLineNumber
    $name = $_.InvocationInfo.ScriptName
    Write-Error "caught exception: $e at script source $name($line)"
}

This will give you a complete trace-back and the source line number in your script where the error is happening. That should help you resolve issues on your own more quickly.

permanent link

answered 17 Jan, 16:45

JBSchueler's gravatar image

JBSchueler
2.2k2939
accept rate: 16%

edited 17 Jan, 16:55

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×61

question asked: 12 Jan, 10:30

question was seen: 603 times

last updated: 17 Jan, 18:36