Command Line Help Needed

rodriguezjarodriguezja Posts: 6
edited August 24, 2010 12:10AM in SQL Packager Previous Versions
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.

Comments

  • 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?
    Chris
  • 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...
  • 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.
  • I'm also having this problem. Is there any fix or workaround?
  • 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
    Chris
  • 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?
  • 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,
    Chris
  • 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 (OK).
    SQL Compare running with option: IgnoreUsers (OK).
    SQL Compare running with option: IgnoreFillFactor (OK).
    SQL Compare running with option: IgnoreWhiteSpace (OK).
    ...
    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                   [dbo].[AMXA_CONFIG]                            <>  <>  
    Table                   [dbo].[AMXS_SETTLE]                            <>  <>  
    Table                   [dbo].[AUTHENTICATION]                         <>  <>  
    Table                   [dbo].[AX_CONFIG]                              <>  <>  
    Table                   [dbo].[BPS_CONFIG]                             <>  <>  
    ...
    -------------------------------------------------------------------------------
    
    Details - comparing dbserver.DBNAME with 127.0.0.1.DBNAME
    ===============================================================================
    Table [dbo].[AMXA_CONFIG]
    == CREATE TABLE [dbo].[AMXA_CONFIG]
    ...
    -------------------------------------------------------------------------------
    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 (OK).
    SQL Data Compare running with Mapping Option: IncludeTimestamps (OK).
    SQL Data Compare running with Mapping Option: IncludeIdentities (OK).
    SQL Data Compare running with SQL Option: DisableKeys (OK).
    SQL Data Compare running with SQL Option: DontOutputComments (OK).
    SQL Data Compare running with Mapping Option: MissingFrom2AsInclude (OK).
    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'. [CLIENT: XXX]
    

    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.
  • 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 (OK).
    SQL Data Compare running with Mapping Option: IncludeTimestamps (OK).
    SQL Data Compare running with Mapping Option: IncludeIdentities (OK).
    SQL Data Compare running with SQL Option: DisableKeys (OK).
    SQL Data Compare running with SQL Option: DontOutputComments (OK).
    SQL Data Compare running with Mapping Option: MissingFrom2AsInclude (OK).
    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.
Sign In or Register to comment.