This repository was archived by the owner on Jun 29, 2019. It is now read-only.
-
Notifications
You must be signed in to change notification settings - Fork 356
Expand file tree
/
Copy pathRunSQL_SQL_Walkthrough.ps1
More file actions
272 lines (251 loc) · 10.4 KB
/
RunSQL_SQL_Walkthrough.ps1
File metadata and controls
272 lines (251 loc) · 10.4 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
<#--------------------------------------------------------------------------
.SYNOPSIS
Script for running T-SQL files in MS SQL Server
Hang Zhang
Built on a post by Andy Mishechkin at https://gallery.technet.microsoft.com/scriptcenter/The-PowerShell-script-for-2a2456c4
This script has been tested on PowerShell V3, and not tested on older versions.
.DESCRIPTION
.\RunSQL_SQL_Walkthrough.ps1
Users will be prompted to input parameters for:
- name of Microsoft SQL Server with R Service instance
- database name that you want to create and use in this walkthrough
- path and name of the .csv file on the local machine to be loaded to the database
- user name which has the previliges of creating database, tables, stored procedures, and uploading data to tables
- password of users
Examples.
Execute on remote SQL Server Express with
.\RunSQL_SQL_Walkthrough.ps1
---------------------------------------------------------------------------#>
function DownloadAndInstall($DownloadPath, $ArgsForInstall, $DownloadFileType = "exe")
{
$LocalPath = [IO.Path]::GetTempFileName() + "." + $DownloadFileType
$web_client.DownloadFile($DownloadPath, $LocalPath)
Start-Process -FilePath $LocalPath -ArgumentList $ArgsForInstall -Wait
}
function InstallSQLUtilities(){
# Install SQL Server Command Line Utilities
$b = Get-WmiObject -Class Win32_Product | sort-object Name | select Name | where { $_.Name -match “Microsoft SQL Server" -and $_.Name -match "Command Line Utilities" }
if($b -eq $null)
{
Write-Output "SQL Server Command Line Utilities not installed. Download and install SQL Server Command Line Utilities" -ForegroundColor "Yellow"
$os = Get-WMIObject win32_operatingsystem
$os_bit = $os.OSArchitecture
if($os_bit -eq '64-bit')
{
$download_url1 = "http://go.microsoft.com/fwlink/?LinkID=188401&clcid=0x409"
$download_url2 = "http://go.microsoft.com/fwlink/?LinkID=188430&clcid=0x409"
}
else
{
$download_url1 = "http://go.microsoft.com/fwlink/?LinkID=188400&clcid=0x409"
$download_url2 = "http://go.microsoft.com/fwlink/?LinkID=188429&clcid=0x409"
}
Write-host "Installing SQL Server Native Client..." -ForegroundColor "Yellow"
DownloadAndInstall $download_url1 "/quiet IACCEPTSQLNCLILICENSETERMS=YES" "msi"
Write-host "Installing SQL Command Line Utilities..." -ForegroundColor "Yellow"
DownloadAndInstall $download_url2 "/quiet" "msi"
}
}
function SearchBCP(){
$bcp_list = Get-ChildItem -Path "C:\Program Files*\" -Filter bcp.exe -Recurse -ErrorAction SilentlyContinue -Force | where {$_.FullName -like '*\bcp.exe'}
if ($bcp_list -ne $null){
$bcp_path = @('')*$bcp_list.count
for ($i=0; $i -lt $bcp_list.count; $i++){
$bcp_path[$i] = $bcp_list[$i].DirectoryName
}
}
return $bcp_path
}
function ExecuteSQLFile($sqlfile,$go_or_not)
{
if($go_or_not -eq 1)
{
$SQLCommandText = @(Get-Content -Path $sqlfile)
foreach($SQLString in $SQLCommandText)
{
if($SQLString -ne "go")
{
#Preparation of SQL packet
if($SQLString -match "SET @path_to_data")
{
$SQLPacket += "SET @path_to_data = '" + $csvfilepath + "'`n"
}
Elseif($SQLString.ToLower() -match "set @db_name")
{
$SQLPacket += "set @db_name = '" + $dbname + "'`n"
}
Elseif($SQLString -match "SET @db_name")
{
$SQLPacket += "SET @db_name = " + $dbname + "`n"
}
Elseif($SQLString.ToLower() -match "use \[taxinyc_sample")
{
$SQLPacket += "USE [" + $dbname +"]`n"
}
Else
{
$SQLPacket += $SQLString + "`n"
}
}
else
{
Write-Host "---------------------------------------------"
Write-Host "Executed SQL packet:"
Write-Host $SQLPacket
$IsSQLErr = $false
#Execution of SQL packet
try
{
$SQLCommand = New-Object System.Data.SqlClient.SqlCommand($SQLPacket, $SQLConnection)
$SQLCommand.CommandTimeout = 0
$SQLCommand.ExecuteScalar()
}
catch
{
$IsSQLErr = $true
Write-Host $Error[0] -ForegroundColor Red
$SQLPacket | Out-File -FilePath ($PWD.Path + "\SQLErrors.txt") -Append
$Error[0] | Out-File -FilePath ($PWD.Path + "\SQLErrors.txt") -Append
"----------" | Out-File -FilePath ($PWD.Path + "\SQLErrors.txt") -Append
}
if(-not $IsSQLErr)
{
Write-Host "Execution succesful"
}
else
{
Write-Host "Execution failed" -ForegroundColor Red
}
$SQLPacket = ""
}
}
}
else
{
#Reading the T-SQL file as a whole packet
$SQLCommandText = @([IO.File]::ReadAllText($sqlfile))
#Execution of SQL packet
try
{
$SQLCommand = New-Object System.Data.SqlClient.SqlCommand($SQLCommandText, $SQLConnection)
$SQLCommand.CommandTimeout = 0
$SQLCommand.ExecuteScalar()
}
catch
{
Write-Host $Error[0] -ForegroundColor Red
}
}
#Disconnection from MS SQL Server
Write-Host "-----------------------------------------"
Write-Host $sqlfile "execution done"
}
###################### End of Functions / Start of Script ######################
$server = Read-Host -Prompt 'Input the database server name (the full address)'
$dbname = Read-Host -Prompt 'Input the name of the database you want to create'
$u = Read-Host -Prompt 'Input the user name which has the previlige to create the database'
$p0 = Read-Host -Prompt 'Input the password of user name which has the previlige to create the database' -AsSecureString
$p1 = [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($p0)
$p = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto($p1)
$csvfilepath = Read-Host -Prompt 'Input the path to the csv file you want to upload to the database'
# Check whether BCP is intalled on the computer. If no, install it.
$web_client = new-object System.Net.WebClient
try
{
$bcp_path = SearchBCP
if ($bcp_path -eq $null){
Write-Host "bcp.exe is not found in C:\Program Files*. Now, start installing SQL Utilities..." -ForegroundColor "Yellow"
InstallSQLUtilities
$bcp_path = SearchBCP
}
Write-Host "Adding path to bcp.exe to the system path..." -Foregroundcolor "Yellow"
$env_path = $env:Path
for ($i=0; $i -lt $bcp_path.count; $i++){
if ($bcp_path.count -eq 1){
$bcp_path_i = $bcp_path
} else {
$bcp_path_i = $bcp_path[$i]
}
if ($env_path -notlike ‘*’+$bcp_path_i+'*'){
Write-Host $bcp_path_i 'not in system path, add it...'
[Environment]::SetEnvironmentVariable("Path", "$bcp_path_i;$env_path", "Machine")
$env:Path = [System.Environment]::GetEnvironmentVariable("Path","Machine")
$env_path = $env:Path
}
}
}
catch
{
Write-Host "Installing SQL Utilities failed. "
}
#Connect to MS SQL Server
try
{
$SQLConnection = New-Object System.Data.SqlClient.SqlConnection
#The MS SQL Server user and password is specified
if($u -and $p)
{
$SQLConnection.ConnectionString = "Server=" + $server + ";Database=master;User ID= " + $u + ";Password=" + $p + ";"
}
#The MS SQL Server user and password is not specified - using the Windows user credentials
else
{
$SQLConnection.ConnectionString = "Server=" + $server + ";Database=master;Integrated Security=True"
}
$SQLConnection.Open()
}
#Error of connection
catch
{
Write-Host $Error[0] -ForegroundColor Red
exit 1
}
# Create database and tables, and upload data to database from local machine using BCP
Write-Host "Start creating database and table on your SQL Server, and uploading data to the table. It may take a while..."
$start_time = Get-Date
try
{
ExecuteSQLFile $PWD"\create-db-tb-upload-data.sql" 1
}
catch
{
Write-Host "Creating database and tables failed. Probably the database or tables already exist." -ForegroundColor "Red"
}
$db_tb = $dbname + ".dbo.nyctaxi_sample"
Write-host "start loading the data to SQL Server table..." -Foregroundcolor "Yellow"
try
{
#The MS SQL Server user and password is specified
if($u -and $p)
{
bcp $db_tb in $csvfilepath -t ',' -S $server -f taxiimportfmt.xml -F 2 -C "RAW" -b 200000 -U $u -P $p
}
#The MS SQL Server user and password is not specified - using the Windows user credentials
else
{
bcp $db_tb in $csvfilepath -t ',' -S $server -f taxiimportfmt.xml -F 2 -C "RAW" -b 200000 -T
}
}
catch
{
Write-Host "BCP uploading data to table failed. Please check whether bcp.exe is installed and the path is added to system path." -ForegroundColor "Red"
}
$end_time = Get-Date
$time_span = $end_time - $start_time
$total_seconds = [math]::Round($time_span.TotalSeconds,2)
Write-Host "This step (creating database and tables, and uploading data to table) takes $total_seconds seconds." -Foregroundcolor "Yellow"
Write-Host "Start running the .sql files to register all functions and stored procedures used in this walkthrough..."
$start_time = Get-Date
ExecuteSQLFile $PWD"\fnCalculateDistance.sql" 1
ExecuteSQLFile $PWD"\fnEngineerFeatures.sql" 1
ExecuteSQLFile $PWD"\TrainTipPredictionModel.sql" 1
ExecuteSQLFile $PWD"\PlotHistogram.sql" 1
ExecuteSQLFile $PWD"\PlotInOutputFiles.sql" 1
ExecuteSQLFile $PWD"\PredictTip.sql" 1
ExecuteSQLFile $PWD"\PredictTipSingleMode.sql" 1
Write-Host "Completed registering all functions and stored procedures used in this walkthrough."
$end_time = Get-Date
$time_span = $end_time - $start_time
$total_seconds = [math]::Round($time_span.TotalSeconds,2)
Write-Host "This step (registering all functions and stored procedures) takes $total_seconds seconds."
$SQLConnection.Close()