Call procedure on another database with user defined table type

Question

I have a connection to a database with right to another. I want to call a procedure on the other database which has a user table data type parameter. But the user table data type isn't found whatever I try.

• I tried using database name in front of [dbo].[myType] but it's not a valid syntax.
• I tried creating the same type in the current database
• I tried creating the same type in the model database
• I tried appending "USE MyOtherDatabase" at the top of my SqlCommand.Text

Everything failed (I'm really abashed the "USE ..." approach failed).

How can I do that?

Sample of code:

// While connected to MyOtherDatabase
CREATE TYPE dbo.typeClubMembersVersion AS TABLE (
    ID INT
    , UNIQUE(ID)

    , [version] INT
)
GO

CREATE PROCEDURE dbo.spCheckCMembersMods (
    @pVersions AS dbo.typeClubMembersVersion READONLY
    , @pWhoID AS BIGINT
)
AS
BEGIN
    [...]
END

        SqlCommand com = new SqlConnection(functions.ConnectionString).CreateCommand();
        com.CommandText = @"
// While connected to CurrentDatabase
USE MyOtherDatabase

DECLARE @tbl AS dbo.typeClubMembersVersion

BEGIN TRANSACTION
    UPDATE dbo.tClubMembers
    SET
        Title = @Title
    OUTPUT inserted.ID, deleted.[version] INTO @tbl (ID, [version])
    WHERE IdMember = @IdMember

    EXEC dbo.spCheckCMembersMods @tbl, @whoID
COMMIT
";
        com.Parameters.Add("@Title", SqlDbType.NVarChar, 20).Value = this.Title;
        com.Parameters.Add("@IdMember", SqlDbType.BigInt).Value = this.Id;
        com.Parameters.Add("@whoID", SqlDbType.BigInt).Value = (object)whoID ?? DBNull.Value;

        com.Connection.Open();
        try
        {
            com.ExecuteNonQuery();
        }
        catch (Exception exe)
        {
            throw exe;
        }
        finally
        {
            com.Connection.Close();
        }

Answer 1

First, what you are calling "Schemas" are actually "Databases" in SQL Server. The "dbo." in your object names is a "Schema" in SQL Server. The "USE.." command only works on Databases.

Secondly, you cannot reference or use Types from another database, it has to be defined in the same database(s) that it is used in. Types can be in other SQL Server schemas, but not in other Databases, which is what you are actually trying to do here.

---

OK, as you noted, your Type is defined in [myOtherDatbase] so why doesn't it work? Probably because the USE.. and SQL command strings do not work the way you might think. Whenever you pass a string like this to SQL Server and try to execute it:

com.CommandText = @"
// While connected to CurrentDatabase
USE MyOtherDatabase

DECLARE @tbl AS dbo.typeClubMembersVersion

BEGIN TRANSACTION
    UPDATE dbo.tClubMembers
    SET
        Title = @Title
    OUTPUT inserted.ID, deleted.[version] INTO @tbl (ID, [version])
    WHERE IdMember = @IdMember

    EXEC dbo.spCheckCMembersMods @tbl, @whoID
COMMIT
";

SQL Server will first compile the entire string and then try to execute it. This means that all of the commands are compiled first before any of them are executed. And that means that your DECLARE @tbl and UPDATE.. commands are compiled before the USE command is executed. So when they are compiled you are still in the previous database where the Type has not been defined. This is what leads to your syntax errors (which are coming from the compiler, not from their execution).

There are three possible solutions:

1. Define the Type in currentDatabase also (I am pretty sure that this works, but not 100%).

2. Reconnect with a connection string that specifies "Initial Catalog=MyOtherDatabase".

3. Re-execute everything after your USE command with Dynamic SQL.

Of these I would recommend #2.

---

Silly me, I just realized that there is another option:

• First execute just the USE command by itself,
• then, execute the rest of the SQL commands on the same connection.

Of course this will leave you in [MyOtherDatabase], so you may want to end this by executing another USE command back to your original database.

Answer 2

It's been such a very long time since I had to use SqlConnection.ChangeDatabase I fergot about it. Until now I've always been able to use "fully named objects" to make my databases interract with each other.
Since I'm currently stuck I'll use it but I hope somebody tells me a way that don't force me to let go the current database connection.

    SqlCommand com = new SqlConnection(functions.ConnectionString).CreateCommand();
    com.CommandText = @"
DECLARE @tbl AS dbo.typeClubMembersVersion

BEGIN TRANSACTION
    UPDATE dbo.tClubMembers
    SET
        Title = @Title
    OUTPUT inserted.ID, deleted.[version] INTO @tbl (ID, [version])
    WHERE IdMember = @IdMember

    EXEC dbo.spCheckCMembersMods @tbl, @whoID
COMMIT
";
    com.Parameters.Add("@Title", SqlDbType.NVarChar, 20).Value = this.Title;
    com.Parameters.Add("@IdMember", SqlDbType.BigInt).Value = this.Id;
    com.Parameters.Add("@whoID", SqlDbType.BigInt).Value = (object)whoID ?? DBNull.Value;

    com.Connection.Open();
    try
    {
        com.Connection.ChangeDatabase("MyOtherDatabase");

        com.ExecuteNonQuery();
    }
    catch (Exception exe)
    {
        throw exe;
    }
    finally
    {
        com.Connection.Close();
    }