|
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 ) |
|
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" |
|
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. |
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.
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?
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.
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.
It's selecting a view with a few JOINs to other tables.
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.
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.
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"?
Yes (except for using prepared statements with WHERE)
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.
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?
https://gist.github.com/tyteen4a03/8bfd959475531305b02671f0047558a0
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', '');
16.0.0.2322, running on Linux. It's part of NetBackup OpsCenter. I'll drop a ticket.
The SQL queries you gave me seems to have killed the server instead: "Database server not found"
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.
<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>
You must have missed the stack trace I posted. Next time please actually read my post before being condescending.
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.