How can I change the database schema using a Statement.execute() in Java ( and SQL Server)?

I need to execute a SQL Server system stored procedure, programmatically, and since it executes in the current schema, I need to change it on the fly.

Like this

Statement st = connection.createStatement(); st.execute("EXEC SP_ADDUSER ' ', ' '");

But SP_ADDUSER only executes on the current schema set for the connection, so if I wanted to create users in various schemas, I'd need to change it, and that's what I am looking for.

From stackoverflow

• I don't believe it's possible to change which database a connection points to.

  You'll probably need to create a separate DataSource/Connection for each database (schema).

  Cade Roux : There is the possibility of using the USE statement in a batch as well as simply using the fully qualified object name (although sp_adduser doesn't physically exist in the DB, it will be searched for in master and used "virtually").

• EXEC <DatabaseName>..sp_adduser can be run from a connection to any database (even master, say). The connection will not be affected.

  For instance, the following appears to work fine on my system:

  USE master
  EXEC sp_addlogin 'test1'
  EXEC SandBox..sp_adduser 'test1'
  

  The same things works fine through the client. Is your client connection altering your SQL?

  using System;
  using System.Data.SqlClient;
  
  namespace TestUse
  {
      class Program
      {
          static void Main(string[] args)
          {
              SqlConnection cn = new SqlConnection("Server=(local);Database=master;Trusted_Connection=True;");
              cn.Open();
              SqlCommand cmd = new SqlCommand("USE master; EXEC sp_addlogin 'test1'; EXEC SandBox..sp_adduser 'test1'", cn);
              cmd.ExecuteNonQuery();
              cn.Close();
          }
      }
  }
  

  Cade Roux : The batch I gave works in SQL Server 2005, creating a login and a user in the SandBox database. If you are just sending a single line, you only need the last line.

  Cade Roux : Even though in my batch, the current database is master. I used that to specifically show that the current database matters not if you use a fully qualified object name.

  Tom Hawtin - tackline : Note, the standard way of calling a stored procedure in JDBC is with a command like "{call sp_name('spanner')}".

  Cade Roux : @Tom - Does JDBC translate the SQL before it reaches the server? Does it do any rewriting of full-qualified object names or the USE statement?