我需要提供一个powershell脚本,它运行一系列步骤来安装自定义解决方案.其中一个步骤是创建SQL Server数据库并执行.sql文件以生成架构.
问题是我需要一种在任何机器上执行它的方法,无论是否在本地安装SQL服务器(它通常在另一台网络机器上).此外,我需要避免使用第三方模块,因为我无法强制客户端安装它们.
有办法做到这一点吗?
更新:运行脚本的服务器将安装.NET 4.5,因为将安装SharePoint 2013.
像这样的东西:
#Variables $sqlServer = "." $sqlDBName = "master" $sqlQuery = "CREATE DATABASE test" # Create the connection string $sqlConnectionString ="Server = $sqlServer; Database = $sqlDBName; Integrated Security = True" $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = $sqlConnectionString #Create the SQL Command object $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = $SqlQuery $SqlCmd.Connection = $SqlConnection #Open SQL connection $SqlCmd.Connection.Open() #Execute the Query $ReturnValue = $SqlCmd.ExecuteNonQuery()
- 编辑
从技术上讲,GO
它不是T-SQL命令.它是Microsoft SQL工具(Management Studio,isql,osql)认可的批处理结束标记.这就是为什么当你直接执行语句时,它无法识别."真正的"解决方案是消除GO语句,或将语句拆分为单独的批处理进程(物理或string.split("GO"))
或者使用SQL Management Object的备用,理论上可以处理"Go"语句(SqlCommand()ExecuteNonQuery()截断命令文本):
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null $SMOServer = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $SMOServer.ConnectionContext.ConnectionString = $sqlConnectionString $SMOServer.ConnectionContext.ExecuteNonQuery($sqlQuery)
- 编辑2
或者,如果您不能使用SQL管理对象,并且您有"GO"语句,那么快速而又脏的是您可以拆分字符串并使用如下代码:
#Variables $sqlServer = "." $sqlDBName = "master" $sqlQuery = "CREATE DATABASE test; GO; CREATE DATABASE test2; GO;" # Create the connection string $sqlConnectionString ="Server = $sqlServer; Database = $sqlDBName; Integrated Security = True" $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = $sqlConnectionString $sqlQuery -split "GO;" | ForEach-Object{ if($_ -ne "") { #Create the SQL Command object $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = $_ $SqlCmd.Connection = $SqlConnection #Open SQL connection $SqlCmd.Connection.Open() #Execute the Query $ReturnValue = $SqlCmd.ExecuteNonQuery() #Close the connection $SqlCmd.Connection.Close() } }
您可以使用PowerShell中的普通.NET类SqlConnection和SqlCommand.为此,您不需要安装SQL Server,也不需要安装任何特定的PowerShell模块.只需使用New-Object cmdlet创建对象即可.
更新:
下面是一个示例,说明如何在不使用SQL特定cmdlet的情况下调用执行SQL代码(无添加错误处理):
$connectionString = "" #Set your connection string here $connection = New-Object -TypeName System.Data.SqlClient.SqlConnection($connectionString) $query = "" #Set your sql query here $command = New-Object -TypeName System.Data.SqlClient.SqlCommand($query, $connection) $connection.Open() $command.ExecuteNonQuery() #Other methods are available if you want to get the return value of your query. $connection.Close()