Problem installing multiple tables with install system

This forum is only for questions or discussions about working with the mojoPortal source code in Visual Studio, obtaining the source code from the repository, developing custom features, etc. If your question is not along these lines this is not the right forum. Please try to post your question in the appropriate forum.

Please do not post questions about design, CSS, or skinning here. Use the Help With Skins Forum for those questions.

This forum is for discussing mojoPortal development

This forum is only for questions or discussions about working with the mojoPortal source code in Visual Studio, obtaining the source code from the repository, developing custom features, etc. If your question is not along these lines this is not the right forum. Please try to post your question in the appropriate forum.

You can monitor commits to the repository from this page. We also recommend developers to subscribe to email notifications in the developer forum as occasionally important things are announced.

Before posting questions here you might want to review the developer documentation.

Do not post questions about design, CSS, or skinning here. Use the Help With Skins Forum for those questions.
This thread is closed to new posts. You must sign in to post in the forums.
3/21/2011 12:51:05 PM
koconnor55
Gravatar
Total Posts 22
View Posts

Problem installing multiple tables with install system

Hi,

Im building a custom feature that requires multiple tables. When I try to install the tables using the install system I get the following error: CREATE DATABASE statement not allowed within multi-statement transaction.

Is it possible to script an entire db, including stored procs and relationships and install it using the install system??

Full error text:

Running script Booking - 0.0.0.1 - 00:00:00.9220527
System.Data.SqlClient.SqlException (0x80131904): CREATE DATABASE statement not allowed within multi-statement transaction. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at mojoPortal.Data.SqlHelper.ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, String commandText, SqlParameter[] commandParameters) in C:\inetpub\wwwroot\mojo_booking\mojoPortal.Data.MSSQL\SqlHelper.cs:line 105 at mojoPortal.Data.DBPortal.DatabaseHelperRunScript(String script, String overrideConnectionInfo) in C:\inetpub\wwwroot\mojo_booking\mojoPortal.Data.MSSQL\dbPortal.cs:line 711 at mojoPortal.Data.DBPortal.DatabaseHelperRunScript(FileInfo scriptFile, String overrideConnectionInfo) in C:\inetpub\wwwroot\mojo_booking\mojoPortal.Data.MSSQL\dbPortal.cs:line 644 at mojoPortal.Business.DatabaseHelper.RunScript(Guid applicationId, FileInfo scriptFile, String overrideConnectionInfo) in C:\inetpub\wwwroot\mojo_booking\mojoPortal.Business\DatabaseHelper.cs:line 327 - 00:00:01.1740671

Thanks for your help in advance

Keith

3/21/2011 3:52:41 PM
Rick Hubka
Gravatar
Total Posts 245
View Posts
mojoPortal Community Expert

Re: Problem installing multiple tables with install system

Hi Keith

Yes.  I have built several custom features in the last 2 years, all with multiple tables.  The current feature I am working on has 14 tables, 50 stored procedures, 10 functions and it has foreign keys and indexes and I insert dozens of rows of data all using the mojoPortal install system.  I install them in the mojoPortal database which I am connected to during the installation.

I have never created a seperate database through the install system though.

Your error "multi-statement transaction" makes me think you are doing too much without issuing GO statement.

Make sure you have a GO after the Create statement.  Then issue the USE your database and GO

If this does not work your may have to do it from Master???

USE master
GO
CREATE DATABASE yourdatabase
GO

USE yourdatabase
GO

Create Table
................
GO

Also make sure the login user has CREATE privileges and/or Master database privileges.

Then you are going to have to have a new connection string for your custom feature to use, etc...


Why not just use the default installation mojoPortal database?  It will save you tons of work.  I use a scratchpad database to create my feature just like in Joe's tutorial then I script it and use the install system.

Hope this helps.  Have Fun!

Rick

3/21/2011 4:45:16 PM
koconnor55
Gravatar
Total Posts 22
View Posts

Re: Problem installing multiple tables with install system

Thanks a mil Rick but I dont think I explained myself very well in the 1st post. I am currently using the steps you suggested i.e. installing the default mojoportal db and then creating a seperate db, similar to scratchpad joe uses in the tutorial. I then script the db and add the file to the SchemaInstallScript folder as shown in the tutorial. However I receive an error when I go to the setup page and the script to add my custom tables is run.

For your custom features do you usually install the entire db using 1 script or do you add tables individually. The error dose suggest there are 2 many create statements running concurrently but i've checked the script and there is a GO after each CREATE. Have you ever experienced similar probs installing multiple tables with 1 file??

I can break the file down but it would make things alot easier going forward if i can script the entire db and install as 1

3/21/2011 5:43:34 PM
koconnor55
Gravatar
Total Posts 22
View Posts

Re: Problem installing multiple tables with install system

After looking at the error a little longer i figured the only piece of the script which could possible cause that error was a stored proc which accessed multiple tables.  I removed this and the script ran without error. Hopefully I can add the sproc's now using an upgrade script without getting the same error.

Thanks again for your help.

3/21/2011 8:45:51 PM
Rick Hubka
Gravatar
Total Posts 245
View Posts
mojoPortal Community Expert

Re: Problem installing multiple tables with install system

Hi Keith

So glad you figured it out.

I use multiple scripts.

In SchemaInstallScripts/mssql I have a file  0.0.0.1.config with all my tables.

Then in SchemaUpgradeScripts/mssql I have:
0.0.0.2.config for my procs
0.0.0.3.config for my function and triggers
0.0.0.3.config for my Row Insert Statements

I do this to enable quickly finding errors and to keep things organized.

Like you, originally, I used to use one file and then one day after lots of database changes I had an error that took forever to find .  Breaking things up into multiple files is how I found the error.

BTW, if you have to re-run your scripts, you will have to delete references to your feature in tables:
mp_SchemaVersion and mp_SchemaScriptHistory

Take Care and have fun building your feature.

Rick

 

3/22/2011 3:07:07 PM
koconnor55
Gravatar
Total Posts 22
View Posts

Re: Problem installing multiple tables with install system

Cheers for the advice Rick. Have started using the same system and I can see the advantages of keeping stored proc, functions etc separate. At least this way if a script fails I know what area its in. Thanks again. Keith

You must sign in to post in the forums. This thread is closed to new posts.