locked
ssma console for access issue connecting to target sql server RRS feed

  • Question

  • Hi!  I am trying to import data to SQL server 2008 from MS Access using SSMA for access v4.2

     

    Here is my sample script file

     

     

    <?xml version="1.0" encoding="utf-8" ?>
    <ssma-script-file xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="..\Schemas\A2SSConsoleScriptSchema.xsd">



    <config>
         <output-providers>
           <output-window suppress-messages="false"
                         destination="stdout"/>
        </output-providers>
    </config>
       

     




    <servers>

    <sql-server name="Target"> 
        <windows-authentication>
          <database value ="TestDB"/>
          <server value ="myserver"/>
          <encrypt value="false"/>
          <trust-server-certificate value="false"/>
        </windows-authentication>
    </sql-server>

     </servers>




    <script-commands>
          







        <create-new-project project-folder="C:\"  project-name="AccessMigrationTest"  overwrite-if-exists="true" />


        <load-access-database database-file="C:\MYACCESSDATA.MDB"/>           

             <connect-target-database server="Target" />       
             
         
           <map-schema source-schema="MYACCESSDATA" sql-server-schema="TestDB.dbo" />


            <convert-schema>
                 <metabase-object object-name="MYACCESSDATA.Tables"
                              object-type="category" />
                </convert-schema>


             <synchronize-target object-name="TestDB.dbo.Tables"
                                object-type="category" />

       

             <migrate-data object-name="MYACCESSDATA.Tables"
                      object-type="category"
                      write-summary-report-to="C:\AccessMigrationTestSummatyReport.xml"
                      report-errors="true"
                      verbose="true" />


       
           
          




           <save-project />
           <close-project />
     </script-commands>






     </ssma-script-file>

     

     

     

    Here is how i am executing the above sctipt-

    ssmaForAccessConsole32.exe -S "C:\MyScript.xml"

     

    And I am getting this sql connection error- Object reference not set to an instance of an object.

     

     

     

    Command Line Processor: Info] [4852/1] [2011-02-14 17:26:14]: ## Processing command #3
    [Command Line Processor: Info] [4852/1] [2011-02-14 17:26:14]: ## Command code: connect-target-database
    [Command Line Processor: Info] [4852/1] [2011-02-14 17:26:14]: CommandStarted was called with command code: connect-target-database
    [Command Line Processor: Info] [4852/1] [2011-02-14 17:26:14]: Establishing connection to sql-server server 'Target'
    [Command Line Processor: Info] [4852/1] [2011-02-14 17:26:14]: ReportFatalException was called
    [Command Line Processor: Info] [4852/1] [2011-02-14 17:26:14]: Exception: Connection to sql-server server failed
     site: Void ProcessCommandImpl(Microsoft.SSMA.Framework.Generic.XTree.XNode)
     source: Microsoft.SSMA.GUI.Generic.CommandProcessor
       at Microsoft.SSMA.GUI.Generic.CommandProcessor.CommandProcessors.CollectorAbstractProcessor.ProcessCommandImpl(XNode command)
       at Microsoft.SSMA.GUI.Generic.CommandProcessor.CommandProcessors.CommandAbstractProcessor.ProcessCommand(XNode command)
       at Microsoft.SSMA.GUI.Generic.CommandProcessor.AbstractCommandProcessor.ProcessCommand(XNode command)
       at Microsoft.SSMA.GUI.Generic.CommandProcessor.AbstractCommandProcessor.ProcessCommandSequence(XNode commandList)
       at Microsoft.SSMA.GUI.Generic.CommandProcessor.AbstractCommandProcessor.ProcessCommandFile(String scriptFile, String serversFile, List`1 variableFiles, Int32& exitCode)
    [Command Line Processor: Info] [4852/1] [2011-02-14 17:26:14]: Inner Exception #1: Object reference not set to an instance of an object.
     site: Void Connect(System.String, System.Security.SecureString)
     source: Microsoft.SSMA.Framework.Access.SqlServer
       at Microsoft.SSMA.Framework.Access.SqlServer.Workspace.SqlServerWorkspaceItem.Connect(String connectionParameters, SecureString password)
       at Microsoft.SSMA.GUI.Generic.CommandProcessor.CommandProcessors.ConnectAbstractProcessor.CollectMetabase(ServerDescriptor server, String connectionXML, SecureString password)
       at Microsoft.SSMA.GUI.Generic.CommandProcessor.CommandProcessors.CollectorAbstractProcessor.ProcessCommandImpl(XNode command)
    [Command Line Processor: Error] [4852/1] [2011-02-14 17:26:14]: ## ERROR: Connection to sql-server server failed
    [Command Line Processor: Error] [4852/1] [2011-02-14 17:26:14]: Exception: Connection to sql-server server failed
     site: Void ProcessCommandImpl(Microsoft.SSMA.Framework.Generic.XTree.XNode)
     source: Microsoft.SSMA.GUI.Generic.CommandProcessor
       at Microsoft.SSMA.GUI.Generic.CommandProcessor.CommandProcessors.CollectorAbstractProcessor.ProcessCommandImpl(XNode command)
       at Microsoft.SSMA.GUI.Generic.CommandProcessor.CommandProcessors.CommandAbstractProcessor.ProcessCommand(XNode command)
       at Microsoft.SSMA.GUI.Generic.CommandProcessor.AbstractCommandProcessor.ProcessCommand(XNode command)
       at Microsoft.SSMA.GUI.Generic.CommandProcessor.AbstractCommandProcessor.ProcessCommandSequence(XNode commandList)
       at Microsoft.SSMA.GUI.Generic.CommandProcessor.AbstractCommandProcessor.ProcessCommandFile(String scriptFile, String serversFile, List`1 variableFiles, Int32& exitCode)
    [Command Line Processor: Error] [4852/1] [2011-02-14 17:26:14]: Inner Exception #1: Object reference not set to an instance of an object.
     site: Void Connect(System.String, System.Security.SecureString)
     source: Microsoft.SSMA.Framework.Access.SqlServer
       at Microsoft.SSMA.Framework.Access.SqlServer.Workspace.SqlServerWorkspaceItem.Connect(String connectionParameters, SecureString password)
       at Microsoft.SSMA.GUI.Generic.CommandProcessor.CommandProcessors.ConnectAbstractProcessor.CollectMetabase(ServerDescriptor server, String connectionXML, SecureString password)
       at Microsoft.SSMA.GUI.Generic.CommandProcessor.CommandProcessors.CollectorAbstractProcessor.ProcessCommandImpl(XNode command)

     

    can anyone please help me resolving this issue. Not sure whats wrong with the connection string?

     

    Thanks

    Tuesday, February 15, 2011 3:47 AM

Answers

  • Few things to look at ..

    • Does your Windows Login have access to SQL Server?
    • If it does is your system running on Windows 2008/SQL Server 2008?  If so your Windows Login might not have been given any permission explicitly.  Might want to give your self access.
    • SQL Server in question is up and running without issues; check out the ERRORLOG file to see what is going on.
    • If everything is fine look into the error log file and see what is the state of the ERROR 18456.

    Cheers!


    Mohit K. Gupta --- MCTS, MCITP, MCC2011 http://sqllearnings.blogspot.com/
    Tuesday, February 15, 2011 12:17 PM