none
Failed to import target model DBName. Detailed message Unable to reconnect to database. RRS feed

  • Question

  • When attempting to publish my database project I continually get the following error message.

    Failed to import target model DBName. Detailed message Unable to reconnect to database.

    However, I am able to publish the same project to the same target database over the same network but from a different computer.


    Thursday, May 10, 2012 12:39 PM

Answers

  • Most likely the problem is that the QueryTimeout used by default is 60 seconds, if you have a large database or some degree of blocking or locking it is easy to go over this, which explains the second try works, because data is in the cache as result of the first attempt.

    You can try setting the QueryTimeout, you have to do this in the Registry, we do not allow you to pass this on the command line (I asked for this improvement to be added).

    reg.exe add HKCU\Software\Microsoft\VisualStudio\10.0\
    SQLDB\Database /v QueryTimeoutSeconds /t REG_DWORD /d 0 /f

    Let me know if this help,


    -GertD @ www.sqlproj.com


    Saturday, June 16, 2012 6:59 PM
    Moderator

All replies

  • Hey David,

    Thanks for reporting the issue. Let's see if we can get to the bottom of this. Have you tried publishing the .dacpac file that is a result of building the project via SqlPackage.exe, our command-line deployment tool? The .dacpac file can be found in %Project directory%/bin/%Debug or Release%/<ProjectName>.dacpac after you build the project.

    If it works through the command-line tool, we can isolate the issue to the project system. Let me know if you are able to give this a try!

    Thanks,

    Adam


    Adam Mahood - Program Manager Data-Tier Application Framework (DACFX)/SQL Server Data Tools

    Tuesday, May 22, 2012 11:50 PM
  • I am seeing a similar issue in our environment and it is random.  In just about every case if we rerun the command a second time it succeeds.  So far any tracing i have run on the server side (both xevents / profiler) has not truned up anything. 

    I am skeptical that there are really connectivity issues because we run lots of databases on this server and the only report of connection failures I am hearing about are from the deployment tools.

    Are there any switches that allow better logging from sqlpackage?  Or good ways to troubleshoot this?

    Here is an example of our command line.

    [227.85]: From . running C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe /Action:Publish /SourceFile:"C:\some.dacpac" /TargetDatabaseName:"databasename" /TargetServerName:"myserver" /p:IgnoreFilegroupPlacement=True /p:IgnoreFileAndLogFilePath=True /p:IgnorePartitionSchemes=True /p:UnmodifiableObjectWarnings=False /p:BlockOnPossibleDataLoss=False /p:CommentOutSetVarDeclarations=False /Variables:DefaultDataFileGrowth="20%" /Variables:DefaultDataFileSize="10" /Variables:DefaultLogFileGrowth="20%" /Variables:DefaultLogFileSize="10" /Variables:DomainName="domain" /Variables:master="master"

    [228.85]: Publishing to database 'somedatabase' on server 'myserver'.
    [323.07]: *** Could not deploy package.
    [323.07]: Failed to import target model somedatabase. Detailed message Unable to reconnect to database.
    [323.08]: Unable to reconnect to database.
    [323.08]: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

    Friday, June 15, 2012 12:37 AM
  • Most likely the problem is that the QueryTimeout used by default is 60 seconds, if you have a large database or some degree of blocking or locking it is easy to go over this, which explains the second try works, because data is in the cache as result of the first attempt.

    You can try setting the QueryTimeout, you have to do this in the Registry, we do not allow you to pass this on the command line (I asked for this improvement to be added).

    reg.exe add HKCU\Software\Microsoft\VisualStudio\10.0\
    SQLDB\Database /v QueryTimeoutSeconds /t REG_DWORD /d 0 /f

    Let me know if this help,


    -GertD @ www.sqlproj.com


    Saturday, June 16, 2012 6:59 PM
    Moderator
  • Thanks Gert.  I will give that a try.  It would be nice to have this exposed as a command line option to sqlpackage.exe.
    Monday, June 18, 2012 10:59 PM
  • Hi guys, I know this is an old thread but I can't see that this option has been exposed to sqlpackage as of yet

    is this the only timeout registry entry used by ssdt's sqlpackage publish option? or are LockTimeoutSeconds and LongRunningQueryTimeoutSeconds also used in some cases?

    Monday, October 6, 2014 10:37 AM
  • Hi Brett,

    We've been doing some work on timeouts and we anticipate an update to that functionality will be included in an upcoming release. As for the other two registry keys you identified, those are indeed used in some scenarios.

    Regards,

    - Steven.

    Tuesday, October 7, 2014 4:47 PM
    Moderator
  • Thanks Steven, I've literally just had a problem with timeouts having had to go back to sqlpackage publish method as I can't get error handling to work correctly running the script through sqlcmd.exe (see my other post on that http://social.msdn.microsoft.com/Forums/sqlserver/en-US/edfb0722-545d-442c-a5ef-d9c8952c290d/how-can-i-execute-the-script-created-by-sqlpackage-so-the-behaviour-is-the-same-as-publish?forum=ssdt)

    I have set all 3 timeout registry keys to 0 in HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\10.0\SQLDB\Database but I still get:

    Error SQL72014: .Net SqlClient Data Provider: Msg 1222, Level 16, State 56, Line 1 Lock request time out period exceeded.

    Is there another setting for this somehow or am I out of luck?

    Tuesday, October 7, 2014 5:25 PM
  • The LockTimeoutSeconds setting is read from the registry, multiplied by 1000 to convert to milliseconds, and then used in a SET LOCK_TIMEOUT command. See: http://msdn.microsoft.com/en-us/library/ms189470.aspx

    Our default LockTimeoutSeconds is 5. Setting LockTimeoutSeconds to 0 means to fail when any lock is encountered, so that's almost certainly not what you want. A value of -1 won't work either, unfortunately, because our code doesn't allow that, so perhaps try using a larger value for LockTimeoutSeconds. (60, maybe?)

    Thursday, October 9, 2014 6:21 PM
    Moderator
  • Thanks Steven, suspecting this may be the case I already had set this value to 1800 but thank you for the confirmation.

    I assume the one remaining timeout not yet discussed (LongRunningQueryTimeoutSeconds) is ok at 0 as I believe this is it's default.

    So hopefully now I have a publish operation that will not fail due to other activity on the server/db

    Friday, October 10, 2014 10:21 AM
  • LongRunningQueryTimeoutSeconds does indeed have a default value of 0, and that does indeed mean that there will be no timeout (for the queries that use that setting).
    Friday, October 10, 2014 8:18 PM
    Moderator
  • We had this issue and our sql server log for the database it was failing on had filled up and had run the drive out of space, thus the messages:

    [323.07]: Failed to import target model somedatabase. Detailed message Unable to reconnect to database.

    [323.08]: Unable to reconnect to database.


    SQL db had actually corrupted itself and had to be restored from previous nights backup.  Then the drive had to be cleared.  Then a rebuild.   Our build takes an hour and it took me a few builds to figure this out.   In case some other unsuspecting accidental TFS admin runs into this.


    Tuesday, July 19, 2016 9:31 PM
  • I am still having this problem with Visual Studio 2017 targeting a SQL Server 2016 database.  I can see that the problem is this: Visual Studio is executing the following query:

    SELECT *
    FROM   (   SELECT SCHEMA_NAME([o].[schema_id]) AS [SchemaName] ,
                      [si].[object_id] AS [ColumnSourceId] ,
                      [o].[name] AS [ColumnSourceName] ,
                      [o].[type] AS [ColumnSourceType] ,
                      [ic].[column_id] AS [ColumnId] ,
                      [c].[name] AS [ColumnName] ,
                      [si].[index_id] AS [IndexId] ,
                      [si].[name] AS [IndexName] ,
                      [ds].[type] AS [DataspaceType] ,
                      [ds].[data_space_id] AS [DataspaceId] ,
                      [ds].[name] AS [DataspaceName] ,
                      [si].[fill_factor] AS [FillFactor] ,
                      [si].[is_padded] AS [IsPadded] ,
                      [si].[is_disabled] AS [IsDisabled] ,
                      [si].[allow_page_locks] AS [DoAllowPageLocks] ,
                      [si].[allow_row_locks] AS [DoAllowRowLocks] ,
                      [sit].[cells_per_object] AS [CellsPerObject] ,
                      [sit].[bounding_box_xmin] AS [XMin] ,
                      [sit].[bounding_box_xmax] AS [XMax] ,
                      [sit].[bounding_box_ymin] AS [YMin] ,
                      [sit].[bounding_box_ymax] AS [YMax] ,
                      [sit].[level_1_grid] AS [Level1Grid] ,
                      [sit].[level_2_grid] AS [Level2Grid] ,
                      [sit].[level_3_grid] AS [Level3Grid] ,
                      [sit].[level_4_grid] AS [Level4Grid] ,
                      [sit].[tessellation_scheme] AS [TessellationScheme] ,
                      [s].[no_recompute] AS [NoRecomputeStatistics] ,
                      [p].[data_compression] AS [DataCompressionId] ,
                      CONVERT(
                          BIT ,
                          CASE WHEN [ti].[data_space_id] = [ds].[data_space_id] THEN
                                   1
                               ELSE 0
                          END) AS [EqualsParentDataSpace]
               FROM   [sys].[spatial_indexes] AS [si] WITH ( NOLOCK )
                      INNER JOIN [sys].[objects] AS [o] WITH ( NOLOCK ) ON [si].[object_id] = [o].[object_id]
                      INNER JOIN [sys].[spatial_index_tessellations] [sit] WITH ( NOLOCK ) ON [si].[object_id] = [sit].[object_id]
                                                                                              AND [si].[index_id] = [sit].[index_id]
                      INNER JOIN [sys].[data_spaces] AS [ds] WITH ( NOLOCK ) ON [ds].[data_space_id] = [si].[data_space_id]
                      INNER JOIN [sys].[index_columns] AS [ic] WITH ( NOLOCK ) ON [si].[object_id] = [ic].[object_id]
                                                                                  AND [si].[index_id] = [ic].[index_id]
                      INNER JOIN [sys].[columns] AS [c] WITH ( NOLOCK ) ON [si].[object_id] = [c].[object_id]
                                                                           AND [ic].[column_id] = [c].[column_id]
                      INNER JOIN [sys].[objects] AS [o2] WITH ( NOLOCK ) ON [o2].[parent_object_id] = [si].[object_id]
                      INNER JOIN [sys].[stats] AS [s] WITH ( NOLOCK ) ON [o2].[object_id] = [s].[object_id]
                                                                         AND [s].[name] = [si].[name]
                      INNER JOIN [sys].[partitions] AS [p] WITH ( NOLOCK ) ON [p].[object_id] = [o2].[object_id]
                                                                              AND [p].[partition_number] = 1
                      LEFT JOIN [sys].[indexes] AS [ti] WITH ( NOLOCK ) ON [o].[object_id] = [ti].[object_id]
                      LEFT JOIN [sys].[tables] AS [t] WITH ( NOLOCK ) ON [t].[object_id] = [si].[object_id]
               WHERE  [si].[is_hypothetical] = 0
                      AND [ti].[index_id] < 2
                      AND OBJECTPROPERTY([o].[object_id], N'IsSystemTable') = 0
                      AND (   [t].[is_filetable] = 0
                              OR [t].[is_filetable] IS NULL )
                      AND (   [o].[is_ms_shipped] = 0
                              AND NOT EXISTS (   SELECT *
                                                 FROM   [sys].[extended_properties]
                                                 WHERE  [major_id] = [o].[object_id]
                                                        AND [minor_id] = 0
                                                        AND [class] = 1
                                                        AND [name] = N'microsoft_database_tools_support' ))) AS [_results];

    And it is getting a SQL Plan that includes the following two full scans of sysobjvalues, reading 25M rows each.  Can this be fixed/resolved?


     
    Tuesday, March 6, 2018 9:54 PM
  • I should note that I tried all the measure suggested in this thread - command-line execution, registry and timeout settings. My guess is that the failure is caused by the very large number of db objects recorded in sysobjvalues.  I'm going to try using a plan guide with a plan captured from a successful deployment of a different db project against a smaller database.
    Wednesday, March 7, 2018 2:35 PM
  • Just for kicks, try enabling Legacy Cardinality Estimation on the database that you're deploying prior to deploying it and see if that helps.

    ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;

    **Make sure to turn it off afterwards.

    ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF;

    • Edited by delish Wednesday, March 7, 2018 2:45 PM
    • Proposed as answer by Ted Feldman Wednesday, March 7, 2018 3:57 PM
    Wednesday, March 7, 2018 2:38 PM
  • That seemed to have worked!!!!   Thank you so much, this was causing tremendous grief.
    Wednesday, March 7, 2018 3:57 PM
  • You're welcome. This has been an issue for us for a long time now. I have not been able to find a bug fix or "real" solution thus far. We have implemented CI/CD and as part of that automation we enable Legacy CE prior to the deploy task and disable after the deploy task. This way we don't have to have any manual intervention.
    Wednesday, March 7, 2018 4:07 PM
  • I've applied this fix in the past to make this work as well. However, recently, my publishes have started to fail again. So the the old time out behavior is occurring even though I have the additional statements in there.

    we're using SQL 2017.

    Any ideas on how to resolve this?

    thanks,

    Marcos.


    marcos paz

    Thursday, September 12, 2019 3:13 PM
  • Issue has just started again for me too
    Monday, September 16, 2019 12:44 AM