This is one of 20 SqlScripts executed as part of the install, the error happens almost at the end. SQL Scripts before that particular one are executed correctly, so I do not think it is a problem with a connection string. That said, I created a sample, which only has two SQL scripts.
Since I couldn't find a way to attach a file, I'll simply descrive the package:
The structure is following:
/SqlScripts
sql_DataLoad.sql
sql_schema.sql
Manifest.xml
Parameters.xml
Now contents:
sql_schema.sql
------------------------------------
/****** Object: Table [dbo].[ReportingDates] Script Date: 07/21/2009 17:25:58 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ReportingDates]') AND type in (N'U'))
DROP TABLE [dbo].[ReportingDates]
GO
/****** Object: Table [dbo].[ReportingDates] Script Date: 07/21/2009 17:25:58 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ReportingDates]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ReportingDates](
[DateKey] [int] NOT NULL,
[DateFull] [datetime] NULL,
[CharacterDate] [varchar](10) NULL,
[FullYear] [char](4) NULL,
[QuarterNumber] [tinyint] NULL,
[WeekNumber] [tinyint] NULL,
[WeekDayName] [varchar](10) NULL,
[MonthDay] [tinyint] NULL,
[MonthName] [varchar](12) NULL,
[YearDay] [smallint] NULL,
[DateDefinition] [varchar](50) NULL,
[WeekDay] [tinyint] NULL,
[MonthNumber] [tinyint] NULL,
CONSTRAINT [PK__ReportingDates__7B712C3B] PRIMARY KEY CLUSTERED
(
[DateKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
------------------------------------------------
sql_DataLoad.sql
----------------------------------------------
DECLARE @Date DATETIME
SET @Date = '1/1/2000'
WHILE @Date < '1/1/2020'
BEGIN
INSERT INTO ReportingDates
(
DateKey, DateFull, FullYear,
QuarterNumber, WeekNumber, WeekDayName,
MonthDay, MonthName, YearDay,
DateDefinition,
CharacterDate,
WeekDay,
MonthNumber
)
SELECT
CONVERT(VARCHAR(8), @Date, 112), @Date, YEAR(@Date),
DATEPART(qq, @Date), DATEPART(ww, @Date), DATENAME(dw, @Date),
DATEPART(dd, @Date), DATENAME(mm, @Date), DATEPART(dy,@Date),
DATENAME(mm, @Date) + ' ' + CAST(DATEPART(dd, @Date) AS CHAR(2)) + ',
' + CAST(DATEPART(yy, @Date) AS CHAR(4)),
CONVERT(VARCHAR(10), @Date, 101),
DATEPART(dw, @Date),
DATEPART(mm, @Date)
SET @Date = DATEADD(dd, 1, @Date)
END
GO
-------------------------
manifest.xml
-------------------------------
<MSDeploy.iisApp>
<dbfullSql path="SqlScripts\sql_schema.sql" transacted="false" />
<dbfullSql path="SqlScripts\sql_DataLoad.sql" transacted="false" />
</MSDeploy.iisApp>
----------------------------
Parameters.xml
-----------------------------------
<parameters>
<parameter name="Database Server" description="Location of your database server (i.e., localhost, server name or IP address)." defaultValue="(local)" tags="SQL, dbServer"></parameter>
<parameter name="Database Name" description="Name of the database for your application." defaultValue="sdk" tags="SQL, dbName">
<parameterEntry type="TextFile" scope="msdeploy.config" match="DatabaseName" />
</parameter>
<parameter name="Database Username" description="Username to access your application database." defaultValue="myuser" tags="SQL, dbUsername">
<parameterEntry type="TextFile" scope="msdeploy.config" match="DatabaseUserName" />
</parameter>
<parameter name="Database Password" description="Password for the database username." tags="New, Password, SQL, dbUserPassword">
<parameterEntry type="TextFile" scope="msdeploy.config" match="DatabasePassword" />
</parameter>
<parameter name="Database Administrator" description="Administrator username for your database." defaultValue="sa" tags="SQL, dbAdminUsername"></parameter>
<parameter name="Database Administrator Password" description="Password for the database administrator account." tags="Password, SQL, dbAdminUserPassword"></parameter>
<parameter name="Connection String All 1" description="Automatically sets the connection string for the connection request." defaultValue="Connect Timeout=380;Data Source={Database Server};Database={Database Name};uid={Database Username};Pwd={Database Password};" tags="Hidden">
<parameterEntry type="ProviderPath" scope="dbfullsql" match="sql_*"/>
</parameter>
</parameters>
-----------------------
I zip it as a test.zip and run it with a following command:
"c:\Program Files\IIS\Microsoft Web Deploy\msdeploy.exe" -debug -dest:iisApp="Default Web Site/SDKSample" -verb:sync -source:package="test.zip" -setParam:name="Database Server",value="my-sqlserver" -setParam:name="Database Username",value=sa -setParam:name="Database Password",value=mcsqladmin -setParam:name="Database Administrator",value=sa -setParam:name="Database Administrator Password",value=password
After that i get the following error:
C:\*\ReadySamples>test
C:\*\ReadySamples>"c:\Program Files\IIS\Microsoft Web Depl
oy\msdeploy.exe" -debug -dest:iisApp="Default Web Site/SDKSample" -verb:sync -so
urce:package="test.zip" -setParam:name="Database Server",value="my-sqlserver" -se
tParam:name="Database Username",value=sa -setParam:name="Database Password",valu
e=mcsqladmin -setParam:name="Database Administrator",value=sa -setParam:name="Da
tabase Administrator Password",value=mcsqladmin
Action: Adding MSDeploy.iisApp (MSDeploy.iisApp)
Action: Adding child dbFullSql (SqlScripts\sql_schema.sql)
Action: Adding child sqlScript (MSDeploy.iisApp/dbFullSql[@path='data source=my-
sqlserver;initial catalog=sdk;user id=sa;connect timeout=380']/sqlScript)
Action: Adding child dbFullSql (SqlScripts\sql_DataLoad.sql)
Action: Adding child sqlScript (MSDeploy.iisApp/dbFullSql[@path='data source=my-
sqlserver;initial catalog=sdk;user id=sa;connect timeout=380']/sqlScript)
Microsoft.Web.Deployment.DeploymentException: An error occurred during execution
of the database script. The approximate location of the error was between lines
'1' and '30' of the script. The verbose log may have more information about the
error. The command started with :
DECLARE @Date DATETIME
---> System.Data.SqlClient.SqlException: Timeout expired. The timeout period e
lapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolea
n breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObj
ect stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cm
dHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, Tds
ParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName,
Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult res
ult, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.Web.Deployment.DBStatementInfo.Execute(DbConnection connection,
DbTransaction transaction, DeploymentBaseContext baseContext)
--- End of inner exception stack trace ---
at Microsoft.Web.Deployment.DBStatementInfo.Execute(DbConnection connection,
DbTransaction transaction, DeploymentBaseContext baseContext)
at Microsoft.Web.Deployment.DBConnectionWrapper.ExecuteSql(DBStatementInfo sq
lStatement, DeploymentBaseContext baseContext)
at Microsoft.Web.Deployment.ScriptToDBProvider.Add(DeploymentObject source, B
oolean whatIf)
at Microsoft.Web.Deployment.DeploymentObject.AddChild(DeploymentObject source
, Int32 position, DeploymentSyncContext syncContext)
at Microsoft.Web.Deployment.DeploymentSyncContext.HandleAddChild(DeploymentOb
ject destParent, DeploymentObject sourceObject, Int32 position)
at Microsoft.Web.Deployment.DeploymentSyncContext.SyncChildrenOrder(Deploymen
tObject dest, DeploymentObject source)
at Microsoft.Web.Deployment.DeploymentSyncContext.HandleAddChild(DeploymentOb
ject destParent, DeploymentObject sourceObject, Int32 position)
at Microsoft.Web.Deployment.DeploymentSyncContext.SyncChildrenOrder(Deploymen
tObject dest, DeploymentObject source)
at Microsoft.Web.Deployment.DeploymentSyncContext.ProcessSync(DeploymentObjec
t destinationObject, DeploymentObject sourceObject)
at Microsoft.Web.Deployment.DeploymentObject.SyncToInternal(DeploymentObject
destObject, DeploymentSyncOptions syncOptions, PayloadTable payloadTable, Conten
tRootTable contentRootTable)
at Microsoft.Web.Deployment.DeploymentObject.SyncTo(DeploymentProviderOptions
providerOptions, DeploymentBaseOptions baseOptions, DeploymentSyncOptions syncO
ptions)
at MSDeploy.MSDeploy.Execute()
at MSDeploy.MSDeploy.Main(String[] unusedArgs)
Error count: 1
--------------------
Hope this helps diagnosing the issue. Again, it will only produce error if SQL Server is under load, i believe if you encrease the date in the dataload script, you can get the same exception under IDLE SQL Server as well. One thing to keep in mind, is that the issue is not how long the script takes, but rather with an ability for msdeployer to handle long running SQL Scripts since that is something I can't control as a deployment package developer.
Thanks.