Clever Geek Handbook

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

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

Like this

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

But SP_ADDUSER is only executed in the current schema for the connection, so if I wanted to create users in different schemas I would need to change it and that's what I'm looking for.

+1


source to share


2 answers


I don't believe it is possible to change the database the connection points to.



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

+1


source


EXEC <DatabaseName>..sp_adduser

can be started from a connection to any database (even master

, say). The connection will not be affected.

For example, the following works on my system:

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


The same works through the client. Is your client connection a change to 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();
        }
    }
}
0


source






More articles:


All Articles