MSSQL stored procedures problem

Post here for help with installing or upgrading mojoPortal pre-compiled release packages. When posting in this forum, please provide all relevant details. You may also want to review the installation or upgrading documentation.

If you have questions about using the source code or working with mojoPortal in Visual Studio, please post in the Developer forum.

Post here for help with installation of mojoPortal pre-compiled release packages

When posting in this forum, please try to provide as many relevant details as possible. Particularly the following:

What operating system were you running when the bug appeared?
What database platform is your site using?
What version of mojoPortal are you running?
What version of .NET do you use?
What steps are necessary to reproduce the issue? Compare expected results vs actual results.

You may also want to review the installation or upgrading documentation.

If you have questions about using the source code or working with mojoPortal in Visual Studio, please post in the Developer forum.

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

8/26/2013 11:05:07 AM

Barbara

Total Posts 36

View Posts

MSSQL stored procedures problem

Hi,

I have moved my web hosting to another ISP and I wondered if I could create the database as new and then load the tables from my local database into it. I see that the installation says the only way is to take a complete backup and restore it on the new server. I can't see any way of copying the stored procedures to the new database.

Many Thanks

8/26/2013 11:27:48 AM

Joe Audette

Total Posts 18439

View Posts

Re: MSSQL stored procedures problem

When you create a database backup using mssql management tools the backup will include all database objects including the stored procedures.

Please read the article Moving an Installation of mojoPortal to a different server, and if you don't know how to correctly backup and restore a database then do some googling for "how to backup and restore an ms sql database" and read up on it and/or watch some youtube videos about it.

It is never going to work by importing the tables or copying objects one by one so don't waste time trying to make that work.

8/27/2013 3:00:00 PM

Barbara

Total Posts 36

View Posts

Re: MSSQL stored procedures problem

Is this the error I would get if I uploaded the stored procedures separately:

2013-08-27 20:46:27,483 ERROR 87.114.237.57 - en-GB - /Setup/Default.aspx - mojoPortal.Data.DBPortal - dbPortal.RunScript failed System.Data.SqlClient.SqlException (0x80131904): Invalid object name 'dbo.mp_HtmlContent_Insert'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at mojoPortal.Data.SqlHelper.ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, String commandText, Int32 commandTimeout, SqlParameter[] commandParameters) at mojoPortal.Data.DBPortal.DatabaseHelperRunScript(String script, String overrideConnectionInfo) ClientConnectionId:636e886a-166b-46b8-a7f8-1f594e900694 2013-08-27 20:46:27,483 INFO 87.114.237.57 - en-GB - /Setup/Default.aspx - mojoPortal.Data.DBPortal - last script statement was ALTER PROCEDURE [dbo].[mp_HtmlContent_Insert] /* Author: Joe Audette Created: 2004-12-23 Last Modified: 2013-04-18 */ @ItemGuid uniqueidentifier, @ModuleGuid uniqueidentifier, @ModuleID int, @Title nvarchar(255), @Excerpt nvarchar(max), @Body nvarchar(max), @MoreLink nvarchar(255), @SortOrder int, @BeginDate datetime, @EndDate datetime, @CreatedDate datetime, @UserID int, @UserGuid uniqueidentifier, @ExcludeFromRecentContent bit AS INSERT INTO [dbo].[mp_HtmlContent] ( ItemGuid, ModuleGuid, [ModuleID], [Title], [Excerpt], [Body], [MoreLink], [SortOrder], [BeginDate], [EndDate], [CreatedDate], [UserID], [UserGuid], LastModUserGuid, LastModUtc, ExcludeFromRecentContent ) VALUES ( @ItemGuid, @ModuleGuid, @ModuleID, @Title, @Excerpt, @Body, @MoreLink, @SortOrder, @BeginDate, @EndDate, @CreatedDate, @UserID, @UserGuid, @UserGuid, @CreatedDate, @ExcludeFromRecentContent ) SELECT @@IDENTITY
nised.

The procedure dbo.mp_HtmlContentInsert is in the database, but does not appear to be recognised.

By the way, I can access the database locally via the SQL Server Management Studio, but cannot access the local backup.

Regards

8/28/2013 11:40:00 AM

Joe Davis

Total Posts 2239

View Posts

Re: MSSQL stored procedures problem

Hi,

That error could occur if you haven't created a backup of your database and restored it on your new host.

You will need to get your old hosting company to help you get to the database backup if you can't access it. You will then need to get your new hosting company to help you restore the backup if you can't access a tool to restore it.

HTH,
Joe D.

8/28/2013 1:22:46 PM

Joe Audette

Total Posts 18439

View Posts

Re: MSSQL stored procedures problem

Note that the dbo portion of the object name is the owner of the object and dbo stands for database owner. If the user in the connection string is not a dbo on the database then it will not "see" those objects that are owned by dbo. Typically you would add the sql user as a dbo of the database.

The main reason why only a real database backup and restore will work is not anything about the stored procedures. The biggest problem is that when you import tables from one database to another they lose their primary keys, indexes and default column values which leads to all kind of errors. You will never be able to solve all those problems so the only right way to do it is using a real database backup and restore.

The only other way to migrate a database is you have to use scripting to script off all the database objects and the data and then you can run the script on the new database to create the objects correctly and restore the data. There are commercial tools such as Red Gate sql tools that can script off the database and there is a free open source database scripting tool here.

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