SMO fails to execute script with user-defined table type

Question

I am trying to execute a sql script read from a file; however, when I go to execute the script I am met with the exception:

Column, parameter, or variable #1: Cannot find data type dbo.ExistingTableType.
Must declare the table variable "@existingTableType".
Parameter or variable '@existingTableType' has an invalid data type.

I have tried testing the script by copying the text and running it in SQL server management studio and it does run successfully. Additionally, other scripts are able to run but for some reason those with a user-defined table type as a parameter are not. Below is a simple example:

C#

string script = scriptFile.OpenText().ReadToEnd();
SqlConnection connection = new SqlConnection(connectionString);
Server server = new Server(new ServerConnection(connection));
server.ConnectionContext.StatementTimeout = 100;
server.ConnectionContext.ExecuteNonQuery(script);

SQL

CREATE PROCEDURE [dbo].[failedStoredProcedure]
(
    @existingTableType dbo.ExistingTableType READONLY
)
AS

-- Do something here

GO

Other tests done:

Tried another user in connection string.

Edit: Additionally, when scripts run it returns 'Invalid Object Name {x}' when the object does exist.

Does your connection string contain default catalog? Are you sure your C# code is running the script in correct database? — Ondra, Oct 25 '13 at 19:15
Example of connection string used with information replaced: "server=SERVER\INSTANCE;database=database_name;Integrated Security=SSPI;Persist Security Info=False;" Also, it is running on the correct database. — BenM, Oct 25 '13 at 19:18

score 0 · Answer 1 · answered Oct 25 '13 at 19:53

0

I tried your code on my end (SQL 2012 Express) and it worked just fine. Does the sql account has grants to the type?

answered Oct 25 '13 at 19:53

Wagner DosAnjos

6,304
1
15
29

It does as when I log on to SSMS I am logging on as that same user and testing the script, in which case it works. – BenM Oct 25 '13 at 20:30
Please check server.ConnectionContext.DatabaseName and verify that it's pointing to the correct db, also check server.ConnectionContext.TrueLogin and verify it's pointing to the correct user name. – Wagner DosAnjos Oct 25 '13 at 20:57
I checked the server connection. DatabaseName, Login, ConnectionString, TrueLogin, LoginSecure are all correct. Still does not work though. – BenM Oct 29 '13 at 17:24

BenM · Accepted Answer · 2013-10-30T15:14:45.210

It seems that at some point something happens to the SMO object where it no longer applies to the specified database, despite the database name being the intended object.

To correct this I created a new database object and applied the scripts from there:

Server server = new Server();
server.ConnectionContext.ConnectionString = connectionString;
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(connectionString);
Database db = server.Databases[builder.InitialCatalog];

Then later instead run

db.ExecuteNonQuery(script);

SMO fails to execute script with user-defined table type

2 Answers2