CODEWITHSUNDEEP
sql-serverjdbcjboss
daniel sp
daniel sp

Reputation: 1000

Specifying schema in SQL server connection

I have a JBoss application that I copied from our production system running in local. This application connects to a SQL server database. I copied also this database in local and can browse it with MS SQL server studio. After a long fight with the users and TCP connections settings in the SQL server. Now I can see how the connection between the JBoss and my copy of the DB is done in local.

However, the problem I am facing is that I get lots of exceptions like

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'table_name'.

Obviously the table_name exists in the SQL database as I can browse it, from SQL studio, using the same user JBoss is using to connect. One interesting thing is that when I browse the tables I see their names are in the form of: 'schemaName.table_name' so my only guess is that the schema is expected for the queries sent from JBoss. This is perhaps a problem with the configuration in the SQL because the JBoss is the same, the jars in the JBoss are also the same and the only thing that may be different is the creation of the users for the SQL database.

Does any one in the forum has any idea about what the problem could be or how can I specify the default schema for a certain user, so it is used in the JDBC connection?

Upvotes: 1

Views: 4846

Answers (1)

daniel sp
daniel sp

Reputation: 1000

To assign a user a default schema: In SQL Server Management Studio go to Security / Logins / YourUser / UserMapping. There you can set the default schema for each database so you don't have to type the schema before the table names.

Upvotes: 1

Related Questions