1

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.

Improve this question

2 Answers 2

Reset to default
1

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).

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

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").
0

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();
        }
    }
}
Improve this answer

6 Comments

It works....but it does not seem to create the user in the correct database, since the SP creates the user in the current database. Since I need to be able to do it in databases other than master, it is not working...."USE master" throws a SQLException for wrong syntax
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.
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.
Well, it happens that I was making a mistake in the query, so that was causing the SQLException... USE <databaseName> is working fine...as is EXEC <databaseName>..sp_adduser. Thanks for the help
Note, the standard way of calling a stored procedure in JDBC is with a command like "{call sp_name('spanner')}".
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.