none
Error 1000: AFx Library library exception: Unable to write to Azure Sql Table. Message from Sql: Invalid column name 'False'. RRS feed

  • Question

  • I'm trying to connect the output from a Score Model to Azure SQL and receiving the error in the Title.

    My Scored dataset looks like:

    Churn_ Scored Labels Scored Probabilities



    false false 0.000087
    true true 0.999974
    false false 0.000023

    Parameters in the Export Data module:

    Comma separated list of columns to be saved
    Churn_,Scored Labels,Scored Probabilities
    Data table name
    dbo.ChurnScores
    Comma separated list of datatable columns
    [Churn_],[ScoredLabels],[ScoredProbabilities]

    The table exists and the column names match.  It appears that the task is ignoring the dataset headers.

    Any ideas appreciated.

    Regards,

      K.B.


    • Edited by AzurePFE Wednesday, October 5, 2016 12:00 PM
    Wednesday, October 5, 2016 12:00 PM

Answers

  • Thanks for bringing this to our attention... After further investigation, there seems to be an issue with type conversion related to Boolean fields since SQL does not support Boolean type but AML does. Engineering team is currently working on a fix so expecting your problem to be fixed soon.

    As a workaround you could use the "edit metadata" module and convert your Boolean fields to (let's say) string and your writes to SQL should work once you do that conversion in your experiment.

    • Proposed as answer by GokhanUModerator Wednesday, October 19, 2016 7:37 PM
    • Marked as answer by AzurePFE Wednesday, October 19, 2016 11:32 PM
    Wednesday, October 19, 2016 7:36 PM
    Moderator

All replies

  • What is your experiment URL and what is the exact error string you are observing
    Thursday, October 6, 2016 2:34 AM
    Moderator
  • For the experiment URL, do you mean:

    https://asiasoutheast.studio.azureml.net/Home/ViewWorkspaceCached/d0cffc36171a467d9fb84a5f9d14479e#Workspaces/Experiments/Experiment/d0cffc36171a467d9fb84a5f9d14479e.f-id.380689347a29449ea7388c3fe58ca207/ViewExperiment

    The error string is as per the title:

    Error 1000: AFx Library library exception: Unable to write to Azure Sql Table. Message from Sql: Invalid column name 'False'.

    Invalid column name 'False'.
    Start time: UTC 10/05/2016 12:07:49
    End time: UTC 10/05/2016 12:08:01

    Output from output log....

    Verbose]             Passing through string 'Churn_,Scored Labels,Scored Probabilities'
    [Verbose]             parameterInfos[37] name = destinationTable , type = System.String
    [Verbose]             Passing through string 'dbo.ChurnScores'
    [Verbose]             parameterInfos[38] name = destinationColumns , type = System.String
    [Verbose]             Passing through string '[Churn_],[ScoredLabels],[ScoredProbabilities]'
    [Verbose]             parameterInfos[39] name = sqlAzureWriteRowsN , type = System.Int32
    [Verbose]             Converted string '1' to value of type System.Int32
    [Verbose]             parameterInfos[40] name = treatErrorsAsWarnings , type = System.Boolean
    [Verbose]             Converted string 'False' to value of type System.Boolean
    [Stop]         ParameterArgumentBinder::InitializeParameterValues. Duration = 00:00:00.8096564
    [Verbose]         Begin invoking method Run ...
    [Stop]     DllModuleMethod::Execute. Duration = 00:00:06.6471008
    [Critical]     Error: Error 1000: AFx Library library exception: Unable to write to Azure Sql Table. Message from Sql: Invalid column name 'False'.
    Invalid column name 'False'.
    [Critical]     {"InputParameters":{"DataTable":[{"Rows":1400,"Columns":3,"estimatedSize":13836288,"ColumnTypes":{"System.Boolean":2,"System.Double":1},"IsComplete":true,"Statistics":{"0":[2,0],"1":[2,0],"2":[0.11895259928405104,0.00032540778920520097,5.7548982113075908E-07,1.0,0.31093628878012164,1393.0,0.0]}}],"Generic":{"destination":"SqlAzure","databaseServerName":"XXXXXXXXX.database.windows.net,1433","databaseName":"Churn","serverUserAccount":"churnuser","trustServerCertificate":true,"originColumns":"Churn_,Scored Labels,Scored Probabilities","destinationTable":"dbo.ChurnScores","destinationColumns":"[Churn_],[ScoredLabels],[ScoredProbabilities]","sqlAzureWriteRowsN":1,"treatErrorsAsWarnings":false},"Unknown":["Key: serverUserPassword, ValueType : Microsoft.Analytics.Modules.SecureString"]},"OutputParameters":[],"ModuleType":"Microsoft.Analytics.Modules.Writer.Dll","ModuleVersion":" Version=6.0.0.0","AdditionalModuleInfo":"Microsoft.Analytics.Modules.Writer.Dll, Version=6.0.0.0, Culture=neutral, PublicKeyToken=69c3241e6f0468ca;Microsoft.Analytics.Modules.Writer.Dll.Writer;Run","Errors":"Microsoft.Analytics.Exceptions.ErrorMapping+ModuleException: Error 1000: AFx Library library exception: Unable to write to Azure Sql Table. Message from Sql: Invalid column name 'False'.\r\nInvalid column name 'False'. ---> Microsoft.Numerics.AFxLibraryExceptionException: Unable to write to Azure Sql Table. Message from Sql: Invalid column name 'False'.\r\nInvalid column name 'False'.\r\n   at Microsoft.Analytics.IO.Local.SQLAzureWriterComponents.RetrySingleRows(String commandStringHeader, String valueFormatString, DataTable table, SqlConnection con, ISQLMetaData metaData, Int32& consecutiveFailedRows)\r\n   at Microsoft.Analytics.IO.Local.SQLAzureWriterComponents.SerializeDense(String commandStringHeader, ISQLMetaData metaData, IEnumerable`1 input)\r\n   at Microsoft.Analytics.IO.Local.SingleNodeSaver.RunSingleNodeOutputFramework[TInput,TState,TChunk](TInput input, TState state, Func`2 decomposer, Action`2 serializer, Action`2 finalizer)\r\n   at Microsoft.Analytics.IO.Local.SingleNodeSaver.<>c__DisplayClass10`8.<CreateSaver>b__f(T1 x1, T2 x2, T3 x3, T4 x4, T5 x5)\r\n   at Microsoft.Analytics.Modules.Writer.Dll.Writer.Write(DataTable dataset, String databaseServerName, String databaseName, String serverUserAccount, SecureString serverUserPassword, Boolean trustServerCertificate, String originColumns, String destinationTable, String destinationColumns, Int32 chunkSize, Boolean treatErrorsAsWarnings) in d:\\_Bld\\9136\\7669\\Sources\\Product\\Source\\Modules\\Writer.Dll\\SQLWriter.cs:line 70\r\n   at Microsoft.Analytics.Modules.Writer.Dll.Writer.RunImpl(DataTable dataset, WriterDataSourceOrSink destination, AuthenticationType auth, String sas, String accountName, SecureString accountKey, String blobPath, WriterBlobFileWriteMode writeMode, FileTypes sasFormat, Boolean sasHasHeader, FileTypes blobFormat, Boolean blobHasHeader, AuthenticationType tableAuthType, String tableSas, String tableAccountName, SecureString tableAccountKey, String tableNames, ColumnSelection partitionKey, ColumnSelection rowKey, ColumnSelection azureTableOriginColumns, String azureTableDestinationColumns, WriteMode azureTableWriteMode, String hiveTableName, String hCatUri, String hadoopUsername, SecureString hadoopPassword, DataLocation dataLocation, String hdfsUri, String azureAccountName, SecureString azureStorageKey, String containerName, String databaseServerName, String databaseName, String serverUserAccount, SecureString serverUserPassword, Boolean trustServerCertificate, String originColumns, String destinationTable, String destinationColumns, Int32 sqlAzureWriteRowsN, Boolean treatErrorsAsWarnings) in d:\\_Bld\\9136\\7669\\Sources\\Product\\Source\\Modules\\Writer.Dll\\Writer.cs:line 233\r\n   at Microsoft.Analytics.Modules.Writer.Dll.Writer.Run(DataTable dataset, WriterDataSourceOrSink destination, AuthenticationType auth, String sas, String accountName, SecureString accountKey, String blobPath, WriterBlobFileWriteMode writeMode, FileTypes sasFormat, Boolean sasHasHeader, FileTypes blobFormat, Boolean blobHasHeader, AuthenticationType tableAuthType, String tableSas, String tableAccountName, SecureString tableAccountKey, String tableNames, ColumnSelection partitionKey, ColumnSelection rowKey, ColumnSelection azureTableOriginColumns, String azureTableDestinationColumns, WriteMode azureTableWriteMode, String hiveTableName, String hCatUri, String hadoopUsername, SecureString hadoopPassword, DataLocation dataLocation, String hdfsUri, String azureAccountName, SecureString azureStorageKey, String containerName, String databaseServerName, String databaseName, String serverUserAccount, SecureString serverUserPassword, Boolean trustServerCertificate, String originColumns, String destinationTable, String destinationColumns, Int32 sqlAzureWriteRowsN, Boolean treatErrorsAsWarnings) in d:\\_Bld\\9136\\7669\\Sources\\Product\\Source\\Modules\\Writer.Dll\\EntryPoint.cs:line 288\r\n   --- End of inner exception stack trace ---","Warnings":[],"Duration":"00:00:06.6398586"}
    Module finished after a runtime of 00:00:08.8642508 with exit code -2
    Module failed due to negative exit code of -2

    Record Ends at UTC 10/05/2016 12:08:01.

    Regards,

    Thursday, October 6, 2016 2:51 AM
  • Could you try removing the square brackets [] around the database column names.

    Hope this helps,

    Roope

    Monday, October 10, 2016 7:52 PM
    Moderator
  • Afraid not.  I put in the square brackets in an attempt to troubleshoot.  Same error with and without.
    Tuesday, October 11, 2016 12:50 AM
  • Thanks for bringing this to our attention... After further investigation, there seems to be an issue with type conversion related to Boolean fields since SQL does not support Boolean type but AML does. Engineering team is currently working on a fix so expecting your problem to be fixed soon.

    As a workaround you could use the "edit metadata" module and convert your Boolean fields to (let's say) string and your writes to SQL should work once you do that conversion in your experiment.

    Wednesday, October 19, 2016 7:36 PM
    Moderator
  • Thanks for bringing this to our attention... After further investigation, there seems to be an issue with type conversion related to Boolean fields since SQL does not support Boolean type but AML does. Engineering team is currently working on a fix so expecting your problem to be fixed soon.

    As a workaround you could use the "edit metadata" module and convert your Boolean fields to (let's say) string and your writes to SQL should work once you do that conversion in your experiment.

    • Proposed as answer by GokhanUModerator Wednesday, October 19, 2016 7:37 PM
    • Marked as answer by AzurePFE Wednesday, October 19, 2016 11:32 PM
    Wednesday, October 19, 2016 7:36 PM
    Moderator
  • Thanks for following this up.  The workaround approach is successful.

    Wednesday, October 19, 2016 11:33 PM