Month: June 2013

OpenQuery Result Set Data Type

Here’s a quick script to grab the schema definition for a remote query to see the type of schema of the result set.  Handy for when you’re doing OpenQuery to retrieve a resultset into a staging table, but don’t know the specific data types being returned from said remote query.

cls
$sql = "
SELECT * FROM OPENQUERY(LINKEDSERVERNAME, '
	SELECT Column1, Column2 FROM RemoteSource
')
"

$serverConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection
$serverConn.ServerInstance = 'ServerInstance'
$serverConn.DatabaseName = 'master'
$serverConn.LoginSecure = $false;
$serverConn.Login = 'LoginName'
$serverConn.SecurePassword = get-content "c:\SecureCredential.txt" | convertto-securestring 
$outputs = @();



$reader = $serverConn.ExecuteReader($sql);
$dt = $reader.GetSchemaTable()
foreach($row in $dt.Rows){

	$output = New-Object -TypeName PSObject -Property @{
		FieldName = $row.Item("ColumnName")
	    TypeName = $row.Item("DataTypeName")
		Scale = $row.Item("NumericScale")
		Precision = $row.Item("NumericPrecision")
		Nullable = $row.Item("AllowDBNull")
		ProviderType = $row.Item("ProviderSpecificDataType")
	}
	$outputs += $output
}
$outputs | SELECT FieldName, TypeName, Scale, Precision, Nullable, ProviderType | fl