3

I am trying to create a database on the fly for my unit testing script.

Using SQL Server LocalDB 11.00.3000 .NET 4.0

I can create/drop the database and add SPs, Views, Tables etc... all fine using straight out SQL script.

The problem occurs when I try to create a user defined TYPE.

CREATE TYPE test as TABLE( [Action_Item_Id] INT,[Shift_Id] NVARCHAR (34))

.... executes perfectly in the SQL console. But in the code ....

SqlConnection conn = new SqlConnection(connString);
conn.Open();
var cmd = new SqlCommand("CREATE DATABASE UnitTestingDatabase",conn);
conn.ChangeDatabase("master");
cmd.ExecuteNonQuery();
conn.ChangeDatabase("UnitTestingDatabase");
cmd.CommandText = "CREATE TYPE test as TABLE( [Action_Item_Id] INT,[Shift_Id] NVARCHAR (34))";
cmd.ExecuteNonQuery();

I get an exception:

{"Incorrect syntax near ''."}
[System.Data.SqlClient.SqlException]: {"Incorrect syntax near ''."}
_className: null
_data: {System.Collections.ListDictionaryInternal}
_dynamicMethods: null
_exceptionMethod: {Void OnError(System.Data.SqlClient.SqlException, Boolean, System.Action`1[System.Action])}
_exceptionMethodString: null
_helpURL: null
_HResult: -2146232060
_innerException: null
_ipForWatsonBuckets: 247330783
_message: "Incorrect syntax near ''."
_remoteStackIndex: 0
_remoteStackTraceString: null
_safeSerializationManager: {System.Runtime.Serialization.SafeSerializationManager}
_source: null
_stackTrace: {sbyte[384]}
_stackTraceString: null
_watsonBuckets: null
_xcode: -532462766
_xptrs: 0
Data: {System.Collections.ListDictionaryInternal}
HelpLink: null
HResult: -2146232060
InnerException: null
IPForWatsonBuckets: 247330783
IsTransient: false
Message: "Incorrect syntax near ''."
RemoteStackTrace: null
Source: ".Net SqlClient Data Provider"
TargetSite: {Void OnError(System.Data.SqlClient.SqlException, Boolean, System.Action`1[System.Action])}
WatsonBuckets: null

It took me a bit to realize it wasn't a . it had an issue with but the nothingness between the '' which seems to be nonsense.

Chris ten Den
  • 549
  • 1
  • 5
  • 19
  • Hmm, I've never tried doing this before, however, it may be better to use a Cursor object in SQL rather than creating a TYPE. Also, creating dynamic databases could potentially cause a lot of issues. It may be best to use a stored procedure with variables defined by you that returns a cursor (sort of a temp table) then reading/parsing the cursor. – Ryan C Aug 07 '15 at 17:04
  • @Ryan Carlisle - The main code we are doing the unittesting on uses a datatable which is passed into a SP in order to process it in a large batch. So the cursor probably isn't ideal. Since the dynamic database is only for unit-testing on a localdb, i'm not overly concerned about that. I may end up having to create a copy of the database, but it means others have to set it up on their localdb too. (Or put it on a server...maybe) – Chris ten Den Aug 07 '15 at 17:11
  • I can't reproduce your error. I tried with .NET 4.5. And tried with both SQL Server 2008 R2, and SQL Server 2014. Using your code exactly, everything works perfectly. – sstan Aug 07 '15 at 17:26
  • I'm using .NET 4.0, SQL Server LocalDB v. 11.00.3000 - Perhaps that is the issue, maybe unsupported. Good clue. Will update my question with this information. – Chris ten Den Aug 07 '15 at 17:27
  • 2
    possible duplicate of [Incorrect syntax near ''](http://stackoverflow.com/questions/19730441/incorrect-syntax-near) – sstan Aug 07 '15 at 17:28
  • Aaack! You are right, there was a ByteOrderMark as the first character. (Zero With Breaking Space). Sneaky! Thank-you oodles! – Chris ten Den Aug 07 '15 at 17:46

0 Answers0