Command Line Help Needed

rodriguezja · April 27, 2010 5:20PM

We run the following command line to package an UPGRADE to our database:

sqlpackager /username1:user1/password1:password /server1:sq-sqlsrv00.ci.snoqualmie.wa.us /database1:CouncilAgenda /username2:user2 /password2:password /server2:.\sqlexpress /database2:councilagenda /location:"C:\Packages" /name:CouncilAgenda /makeexe

database 1: CouncilAgenda has the most current version of our database
database 2: CouncilAgenda has the database that we need to update

The above command line runs with no problem, the issue is that it seems to actually package the complete database as opposed to just packaging the changes. We want the command line to just build an executable that contains just the changes.

When run the SQLPackager UI and choose the Upgrade, we are able to create executable with no problems. Any comments would be helpful. Thanks.

Chris Auckland · April 29, 2010 8:23AM

That's strange, the command you're using seems to work ok for me creating an upgrade package.

Could you post the console output when you try?

rodriguezja · April 30, 2010 1:12PM

Registering databases
Creating mappings
Comparing
Replaying user actions
Creating SQL

Summary Information
===============================================================================
DB1 = sq-sqlsrv00.ci.snoqualmie.wa.us.CouncilAgenda
DB2 = .\sqlexpress.councilagenda

Object type Name DB1 DB2

Table [dbo].[ABSource] == ==
Table [dbo].[AgendaBillActions] == ==
Table [dbo].[AgendaBills] == ==
Table [dbo].[AgendaBillsAgendaPacket] == ==
Table [dbo].[AgendaBillsExhibits] == ==
Table [dbo].[AgendaBillsSource] == ==
Table [dbo].[AgendaBillsVotingRecord] == ==
Table [dbo].[AgendaPacketCurrent] == ==
Table [dbo].[AgendaPacketPublished] == ==
Table [dbo].[AlphaID] == ==
Table [dbo].[Announcements] == ==
Table [dbo].[AnnouncementsAttachments] == ==
Table [dbo].[Appointments] == ==
Table [dbo].[AppointmentsAttachments] == ==
Table [dbo].[aspnet_Applications] == ==
Table [dbo].[aspnet_Membership] == ==
Table [dbo].[aspnet_Paths] == ==
Table [dbo].[aspnet_PersonalizationAllUsers] == ==
Table [dbo].[aspnet_PersonalizationPerUser] == ==
Table [dbo].[aspnet_Profile] == ==
Table [dbo].[aspnet_Roles] == ==
Table [dbo].[aspnet_SchemaVersions] == ==
Table [dbo].[aspnet_Users] == ==
Table [dbo].[aspnet_UsersInRoles] == ==
Table [dbo].[aspnet_WebEvent_Events] == ==
Table [dbo].[attendance] == ==
Table [dbo].[attendanceother] == ==
Table [dbo].[Bulletins] == ==
Table [dbo].[citystaff] == ==
Table [dbo].[Configuration] == ==
Table [dbo].[CouncilCommittees] == ==
Table [dbo].[CouncilMembers] == ==
Table [dbo].[CouncilMinutes] == ==
Table [dbo].[Departments] == ==
Table [dbo].[Discussions] == ==
Table [dbo].[FileCategories] == ==
Table [dbo].[FiscalImpact] == ==
Table [dbo].[mayorsnotes] == ==
Table [dbo].[pendingbusiness] == ==
Table [dbo].[precouncil] == ==
Table [dbo].[Presentations] == ==
Table [dbo].[PresentationsAttachments] == ==
Table [dbo].[Proclamations] == ==
Table [dbo].[ProclamationsAttachments] == ==
Table [dbo].[PublicHearings] == ==
Table [dbo].[PublicHearingsAttachments] == ==
Table [dbo].[SEQ_CHAN] == ==
Table [dbo].[SEQ_NO] == ==
Table [dbo].[staffpositions] == ==
Table [dbo].[StaffReports] == ==
Table [dbo].[temp_ab] == ==
Table [dbo].[VoteResponses] == ==
Table [dbo].[Warrants] == ==
Table [dbo].[YesNo] == ==
User Network Service == ==
User jrodrigu == ==
Role public->[dbo] == ==
Role db_owner->[dbo] == ==
Role db_accessadmin->[dbo] == ==
Role db_securityadmin->[dbo] == ==
Role db_ddladmin->[dbo] == ==
Role db_backupoperator->[dbo] == ==
Role db_datareader->[dbo] == ==
Role db_datawriter->[dbo] == ==
Role db_denydatareader->[dbo] == ==
Role db_denydatawriter->[dbo] == ==
Role aspnet_Membership_FullAccess->[dbo] == ==
Role aspnet_Membership_BasicAccess->[dbo] == ==
Role aspnet_Membership_ReportingAccess->[dbo] == ==
Role aspnet_Profile_FullAccess->[dbo] == ==
Role aspnet_Profile_BasicAccess->[dbo] == ==
Role aspnet_Profile_ReportingAccess->[dbo] == ==
Role aspnet_Roles_FullAccess->[dbo] == ==
Role aspnet_Roles_BasicAccess->[dbo] == ==
Role aspnet_Roles_ReportingAccess->[dbo] == ==
Role aspnet_Personalization_FullAccess->[dbo] == ==
Role aspnet_Personalization_BasicAccess->[dbo] == ==
Role aspnet_Personalization_ReportingAccess->[dbo] == ==
Role aspnet_WebEvent_FullAccess->[dbo] == ==
View [dbo].[vw_aspnet_Applications] == ==
View [dbo].[vw_aspnet_MembershipUsers] == ==
View [dbo].[vw_aspnet_Profiles] == ==
View [dbo].[vw_aspnet_Roles] == ==
View [dbo].[vw_aspnet_Users] == ==
View [dbo].[vw_aspnet_UsersInRoles] == ==
View [dbo].[vw_aspnet_WebPartState_Paths] == ==
View [dbo].[vw_aspnet_WebPartState_Shared] == ==
View [dbo].[vw_aspnet_WebPartState_User] == ==
StoredProcedure [dbo].[aspnet_AnyDataInTables] == ==
StoredProcedure [dbo].[aspnet_Applications_CreateApplication] == ==
StoredProcedure [dbo].[aspnet_CheckSchemaVersion] == ==
StoredProcedure [dbo].[aspnet...angePasswordQuestionAndAnswer] == ==
StoredProcedure [dbo].[aspnet_Membership_CreateUser] == ==
StoredProcedure [dbo].[aspnet_Membership_FindUsersByEmail] == ==
StoredProcedure [dbo].[aspnet_Membership_FindUsersByName] == ==
StoredProcedure [dbo].[aspnet_Membership_GetAllUsers] == ==
StoredProcedure [dbo].[aspnet...ership_GetNumberOfUsersOnline] == ==
StoredProcedure [dbo].[aspnet_Membership_GetPassword] == ==
StoredProcedure [dbo].[aspnet...bership_GetPasswordWithFormat] == ==
StoredProcedure [dbo].[aspnet_Membership_GetUserByEmail] == ==
StoredProcedure [dbo].[aspnet_Membership_GetUserByName] == ==
StoredProcedure [dbo].[aspnet_Membership_GetUserByUserId] == ==
StoredProcedure [dbo].[aspnet_Membership_ResetPassword] == ==
StoredProcedure [dbo].[aspnet_Membership_SetPassword] == ==
StoredProcedure [dbo].[aspnet_Membership_UnlockUser] == ==
StoredProcedure [dbo].[aspnet_Membership_UpdateUser] == ==
StoredProcedure [dbo].[aspnet_Membership_UpdateUserInfo] == ==
StoredProcedure [dbo].[aspnet_Paths_CreatePath] == ==
StoredProcedure [dbo].[aspnet...sonalization_GetApplicationId] == ==
StoredProcedure [dbo].[aspnet...Administration_DeleteAllState] == ==
StoredProcedure [dbo].[aspnet...ationAdministration_FindState] == ==
StoredProcedure [dbo].[aspnet...dministration_GetCountOfState] == ==
StoredProcedure [dbo].[aspnet...ministration_ResetSharedState] == ==
StoredProcedure [dbo].[aspnet...Administration_ResetUserState] == ==
StoredProcedure [dbo].[aspnet...ationAllUsers_GetPageSettings] == ==
StoredProcedure [dbo].[aspnet...ionAllUsers_ResetPageSettings] == ==
StoredProcedure [dbo].[aspnet...ationAllUsers_SetPageSettings] == ==
StoredProcedure [dbo].[aspnet...zationPerUser_GetPageSettings] == ==
StoredProcedure [dbo].[aspnet...tionPerUser_ResetPageSettings] == ==
StoredProcedure [dbo].[aspnet...zationPerUser_SetPageSettings] == ==
StoredProcedure [dbo].[aspnet_Profile_DeleteInactiveProfiles] == ==
StoredProcedure [dbo].[aspnet_Profile_DeleteProfiles] == ==
StoredProcedure [dbo].[aspnet...e_GetNumberOfInactiveProfiles] == ==
StoredProcedure [dbo].[aspnet_Profile_GetProfiles] == ==
StoredProcedure [dbo].[aspnet_Profile_GetProperties] == ==
StoredProcedure [dbo].[aspnet_Profile_SetProperties] == ==
StoredProcedure [dbo].[aspnet_RegisterSchemaVersion] == ==
StoredProcedure [dbo].[aspnet_Roles_CreateRole] == ==
StoredProcedure [dbo].[aspnet_Roles_DeleteRole] == ==
StoredProcedure [dbo].[aspnet_Roles_GetAllRoles] == ==
StoredProcedure [dbo].[aspnet_Roles_RoleExists] == ==
StoredProcedure [dbo].[aspnet_Setup_RemoveAllRoleMembers] == ==
StoredProcedure [dbo].[aspnet_Setup_RestorePermissions] == ==
StoredProcedure [dbo].[aspnet_UnRegisterSchemaVersion] == ==
StoredProcedure [dbo].[aspnet_Users_CreateUser] == ==
StoredProcedure [dbo].[aspnet_Users_DeleteUser] == ==
StoredProcedure [dbo].[aspnet_UsersInRoles_AddUsersToRoles] == ==
StoredProcedure [dbo].[aspnet_UsersInRoles_FindUsersInRole] == ==
StoredProcedure [dbo].[aspnet_UsersInRoles_GetRolesForUser] == ==
StoredProcedure [dbo].[aspnet_UsersInRoles_GetUsersInRoles] == ==
StoredProcedure [dbo].[aspnet_UsersInRoles_IsUserInRole] == ==
StoredProcedure [dbo].[aspnet...sInRoles_RemoveUsersFromRoles] == ==
StoredProcedure [dbo].[aspnet_WebEvent_LogEvent] == ==
StoredProcedure [dbo].[FindReplace] == ==
StoredProcedure [dbo].[freetext_rank_proc] == ==
StoredProcedure [dbo].[sp_ins...nnouncementsAndExecutivenotes] == ==
StoredProcedure [dbo].[sp_insert_attendance] == ==
StoredProcedure [dbo].[sp_insert_calltoordernotes] == ==
StoredProcedure [dbo].[sp_insert_citizencommentsnotes] == ==
StoredProcedure [dbo].[sp_insert_councilmtgdate] == ==
StoredProcedure [dbo].[sp_insert_MayorsReportnotes] == ==
StoredProcedure [dbo].[sp_insert_precouncilnotes] == ==
StoredProcedure [dbo].[sp_insert_publichearingsnotes] == ==
StoredProcedure [dbo].[sp_insert_staffreportsnotes] == ==
StoredProcedure [dbo].[sp_insert_streets] == ==
StoredProcedure [dbo].[sprocFileInfoSelectList] == ==
StoredProcedure [dbo].[sprocFilesInsertSingleItem] == ==
StoredProcedure [dbo].[sprocFilesSelectSingleItem] == ==

Registering databases
Mapping
Comparing databases
Generating SQL scripts
Finished

Summary Information
===============================================================================
DB1 = sq-sqlsrv00.ci.snoqualmie.wa.us.CouncilAgenda
DB2 = .\sqlexpress.councilagenda

Object type Name Records DB1 DB2

Table [dbo].[ABSource] 572 >>
Table [dbo].[AgendaBillActions] 10 >>
Table [dbo].[AgendaBills] 3169 >>
Table [dbo].[AgendaBillsAgendaPacket] 70 >>
Table [dbo].[AgendaBillsExhibits] 1653 >>
Table [dbo].[AgendaBillsSource] 46 >>
Table [dbo].[AgendaPacketCurrent] 1 >>
Table [dbo].[AgendaPacketPublished] 1 >>
Table [dbo].[AlphaID] 3 >>
Table [dbo].[Announcements] 29 >>
Table [dbo].[Appointments] 41 >>
Table [dbo].[aspnet_Applications] 1 >>
Table [dbo].[aspnet_Membership] 26 >>
Table [dbo].[aspnet_Roles] 5 >>
Table [dbo].[aspnet_SchemaVersions] 6 >>
Table [dbo].[aspnet_Users] 26 >>
Table [dbo].[aspnet_UsersInRoles] 38 >>
Table [dbo].[attendance] 24 >>
Table [dbo].[Bulletins] 8 >>
Table [dbo].[citystaff] 16 >>
Table [dbo].[CouncilCommittees] 6 >>
Table [dbo].[CouncilMembers] 9 >>
Table [dbo].[Departments] 9 >>
Table [dbo].[Discussions] 19 >>
Table [dbo].[FileCategories] 7 >>
Table [dbo].[FiscalImpact] 39 >>
Table [dbo].[mayorsnotes] 48 >>
Table [dbo].[precouncil] 6 >>
Table [dbo].[Presentations] 29 >>
Table [dbo].[Proclamations] 6 >>
Table [dbo].[PublicHearings] 2 >>
Table [dbo].[SEQ_CHAN] 2 >>
Table [dbo].[SEQ_NO] 3 >>
Table [dbo].[staffpositions] 16 >>
Table [dbo].[temp_ab] 2520 >>
Table [dbo].[VoteResponses] 6 >>
Table [dbo].[Warrants] 65 >>
Table [dbo].[YesNo] 3 >>

Creating package...

rodriguezja · April 30, 2010 1:57PM

So I've been troubleshooting this now and tried it where the two SQL Servers are the same (SQL 2005). That scenario worked.

Our production scenario is this:

Production Server with Current database: SQL Server 2000
Client with database to be updated: SQL Server Express 2005

the scenario above from SQL 2000 to SQL 2005 does not work via command line. Are there specific switches that we might need to be invoking? I even tried the saved project file that we have via the GUI and that one failed as well via command line even though it works via the GUI.

ryepup · August 5, 2010 3:34PM

I'm also having this problem. Is there any fix or workaround?

Chris Auckland · August 10, 2010 3:16PM

Thanks for your post.

Can you try the latest patch version? this issue should hopefully be fixed:

http://www.red-gate.com/messageboard/vi ... php?t=9883

ryepup · August 16, 2010 10:08AM

came here to bitch, but now I see the patch version has been updated. Will try the latest version tonight.

Is there any mailing list I can get on to be notified when the sql packager patch gets updated?

Chris Auckland · August 16, 2010 10:25AM

came here to bitch, but now I see the patch version has been updated. Will try the latest version tonight.

Is there any mailing list I can get on to be notified when the sql packager patch gets updated?

I tried to update you a number of times, but the emails to you always bounce.

If we log a support ticket for you, we will always let you know as soon as a patch is available and/or keep you updated on the status of the ticket, but that assumes we are able to contact you.

If you would like me to send you the error we get when we try to email, can you give me an alternate email address?

Thanks,

ryepup · August 16, 2010 7:22PM

Getting closer. I think it works fine during schema comparison, then gives me a permission error when starting data comparison.

I've replaced the source DB server, DBName, and sql login I'm using to protect the innocent. Here's some abridged log output:

SQL Packager Command Line V6.3.1.51
==============================================================================
Copyright c Red Gate Software Ltd 1999-2008

Serial Number:XXXXXXXXXXXX

SQL Compare running with option: IgnorePermissions &#40;OK&#41;.
SQL Compare running with option: IgnoreUsers &#40;OK&#41;.
SQL Compare running with option: IgnoreFillFactor &#40;OK&#41;.
SQL Compare running with option: IgnoreWhiteSpace &#40;OK&#41;.
...
Registering data sources
Creating mappings
Comparing
Applying Command Line Items
Creating SQL

Summary Information
===============================================================================
DB1 = dbserver.DBNAME
DB2 = 127.0.0.1.DBNAME

Object type             Name                                           DB1 DB2 
-------------------------------------------------------------------------------
Table                   &#91;dbo&#93;.&#91;AMXA_CONFIG&#93;                            &lt;&gt;  &lt;&gt;  
Table                   &#91;dbo&#93;.&#91;AMXS_SETTLE&#93;                            &lt;&gt;  &lt;&gt;  
Table                   &#91;dbo&#93;.&#91;AUTHENTICATION&#93;                         &lt;&gt;  &lt;&gt;  
Table                   &#91;dbo&#93;.&#91;AX_CONFIG&#93;                              &lt;&gt;  &lt;&gt;  
Table                   &#91;dbo&#93;.&#91;BPS_CONFIG&#93;                             &lt;&gt;  &lt;&gt;  
...
-------------------------------------------------------------------------------

Details - comparing dbserver.DBNAME with 127.0.0.1.DBNAME
===============================================================================
Table &#91;dbo&#93;.&#91;AMXA_CONFIG&#93;
== CREATE TABLE &#91;dbo&#93;.&#91;AMXA_CONFIG&#93;
...
-------------------------------------------------------------------------------
Used mapping option 'IgnoreSpaces'
Used mapping option 'IncludeTimestamps'
Used mapping option 'IncludeIdentities'
Used Sql Option 'DisableKeys'
Used Sql Option 'DontOutputComments'
SQL Data Compare running with Mapping Option: IgnoreSpaces &#40;OK&#41;.
SQL Data Compare running with Mapping Option: IncludeTimestamps &#40;OK&#41;.
SQL Data Compare running with Mapping Option: IncludeIdentities &#40;OK&#41;.
SQL Data Compare running with SQL Option: DisableKeys &#40;OK&#41;.
SQL Data Compare running with SQL Option: DontOutputComments &#40;OK&#41;.
SQL Data Compare running with Mapping Option: MissingFrom2AsInclude &#40;OK&#41;.
Comparing database dbserver.DBNAME with database 127.0.0.1.DBNAME...
Registering databases
Error: Comparison of 'dbserver.DBNAME' and '127.0.0.1.DBNAME' failed:Login failed for user 'SQL_LOGIN'.

I am using an ssh tunnel to my destination server, so it appears to be running at 127.0.0.1:1433 (long ugly story, that's the best way I could come up with to talk to the destination server). All the microsoft tools can connect just fine, and it looks like the schema portion of the process can connect OK, too. Running from the SQLPackager GUI works with no problems.

I did some detective work:

My destination SQL server logs only show one successful login, so the failed login message must on the source server. I tried creating a different username/password on my destination server (using a different username2, password2), and got the same error message from SQLPackager. I think that confirms the process is failing when trying to talk to the source server again (dbserver in my log output). I inverted my comparison (used my destination server as username1,server1, etc), and now I do see entries in my SQL Server logs like:

Login failed for user 'SQL_LOGIN'. &#91;CLIENT: XXX&#93;

So, looks like the password isn't making it into the data compare section.

Running using command flags (/u1, u2 etc) instead of an XML argsfile produces the same result.

I think the handoff to the SQL Data Compare subsystem is missing the password information, at least for password1.

I don't mean to be a pain with this stuff, thank you very much for your help so far. In my case it would be very nice to have the db upgrade start as soon as the compare is finished, which is why I'm wanting the command line so bad. The alternative is me spending a weekend remoting into a flaky server every so often to see if it's done yet, and I'm really hoping to avoid that.

ryepup · August 24, 2010 12:10AM

Looks like a regression in 6.3.1.57

When making an upgrade script using the command line, the schema compare looks fine, then we get a nullref once we're into the data section. From my log:

...
SQL Data Compare running with Mapping Option: IgnoreSpaces &#40;OK&#41;.
SQL Data Compare running with Mapping Option: IncludeTimestamps &#40;OK&#41;.
SQL Data Compare running with Mapping Option: IncludeIdentities &#40;OK&#41;.
SQL Data Compare running with SQL Option: DisableKeys &#40;OK&#41;.
SQL Data Compare running with SQL Option: DontOutputComments &#40;OK&#41;.
SQL Data Compare running with Mapping Option: MissingFrom2AsInclude &#40;OK&#41;.
Comparing database SRC.DB with database DST.DB...
Registering databases
Mapping
Comparing databases
Generating SQL scripts
Error: Object reference not set to an instance of an object.

I get similar errors when make an install script via the command line, and also via the GUI.

Command Line Help Needed

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk