SQLDIY/execute_sql.ps1 at master · SQLServerIO/SQLDIY · GitHub

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
#---------------------------------------------------------------
# This script will execute a sql command and returns a formatted
# table of the result.  The first parm can either be a query string
# or a path to a file that contains the query.
#
#---------------------------------------------------------------
param(
	[string]$sqlFile,# = $(Throw "SQL command (or the full path/Filename to a .sql file) is required!"),
	[string]$server,   #Optional parm to specify the server.  Otherwise local is used.
	[string]$database, #Optional parm to specify the database to use in the connection
	[string]$userName, #Optional parm to specify the UserName/Password for the connection
	[string]$password, #Optional parm to specify the UserName/Password for the connection
    [string]$rserver,  #Optional parm to specifiy the repository server. Otherwise local is used.
    [string]$rdatabase #Optional parm to specify the repository database to use in the connection. Otherwise Management is used.
	)

#---------------------------------------------------------------
# Determine if a sql command is given or the path to a file.
#
$table = "";
$sqlFile = "C:\projects\source\opensource\SQLDIY\Management.GatherDatabaseSpaceDetailps.sql"
$server = "WARMACHINE";
$database = "master";
$rdatabase = "SQLDIY";
#$userName, #Optional parm to specify the UserName/Password for the connection
#$password  #Optional parm to specify the UserName/Password for the connection

$command = $sqlFile.Trim();
if (Test-Path $command -pathType leaf)
{
	#---------------------------------------------------------------
	# Since a file path was given, pull the content as the sql command.
	#
	$command = Get-Content $command;
}

#---------------------------------------------------------------
# Create a connection object, if this cannot be create we must fail
#
$conn = New-Object System.Data.SqlClient.SqlConnection;
$rconn = New-Object System.Data.SqlClient.SqlConnection;
if (!$conn)
{
	Throw "SqlConnection could not be created!";
	return;
}

#---------------------------------------------------------------
# Default the server to local if one was not provided
#
if (!$server)
{
	$server = "(local)";
}
$connString = "Server = $($server);";

#---------------------------------------------------------------
# Include the database in our connection string if one is given.
#
if ($database)
{
	$connString = $connString + " Database = $($database);";
}

#---------------------------------------------------------------
# Base security on the existence of a username/password
#
if ($userName -and $password)
{
	$connString = $connString + " User Id = $($userName); Password = $($password)";
}
else
{
	$connString = $connString + " Integrated Security = True";
}

#---------------------------------------------------------------
# Default the repository server to local if one was not provided
#
if (!$rserver)
{
	$rserver = "(local)";
}
$rconnString = "Server = $($rserver);";

#---------------------------------------------------------------
# Include the repository  database in our connection string if one is given.
#
if ($rdatabase)
{
	$rconnString = $rconnString + " Database = $($rdatabase);";
}
#---------------------------------------------------------------
# repository connection string trusted auth only!
$rconnString = $rconnString + " Integrated Security = True";

#---------------------------------------------------------------
# Now that we have built our connection string, attempt the connection.
#
$conn.ConnectionString = $connString;
$rconn.ConnectionString = $rconnString;
$conn.Open();
if ($conn.State -eq 1)
{
	$cmd = New-Object System.Data.SqlClient.SqlCommand $command, $conn;
	if ($cmd)
	{
		$data = New-Object System.Data.SqlClient.SqlDataAdapter;
		if ($data)
		{
			$ds = New-Object System.Data.DataSet;
			if ($ds)
			{
				$data.SelectCommand = $cmd;
				$data.Fill($ds) | Out-Null;

				foreach ($table in $ds.Tables)
				{
                    $bulkCopy = New-Object Data.SqlClient.SqlBulkCopy($rconn.ConnectionString, [System.Data.SqlClient.SqlBulkCopyOptions]::KeepIdentity)
                    $bulkCopy.DestinationTableName = $rdatabase+".dbo.DBFile"
                    $bulkCopy.WriteToServer($table)
					#$table | Format-Table;
				}
				$ds.Dispose();
			}
			else
			{
				Write-Host "Failed creating the data set object!";
			}
			$data.Dispose();
		}
		else
		{
			Write-Host "Failed creating the data adapter object!";
		}
		$cmd.Dispose();
	}
	else
	{
		Write-Host "Failed creating the command object!";
	}
	$conn.Close();
}
else
{
	Write-Host "Connection could not be opened!";
}
$conn.Dispose();