Skip to content

June 13, 2013

OpenQuery Result Set Data Type

by Scott Newman

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.

$sql = "
	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
Read more from Powershell, SQL Server

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Note: HTML is allowed. Your email address will never be published.

Subscribe to comments

%d bloggers like this: