Need help writing the contents of System.Data.DataSet to a SQL server table

Please help with this simple script. I am trying to get a list of all user databases from a server, then write that list to a central table. So far I am able to query a remote SQL instance, and display the results on the screen. However I am struggling with how to write the values in System.Data.DataSet to a SQL Server table.

The table actually has 5 columns and allows null values. I only need to insert INSTANCE NAME and DATABASE NAME to the table.

Everything works up to line 19, but I am stuck on how to insert the data to a table.

# create the connection to the remote instance
$sqlConn = New-Object System.Data.SqlClient.SqlConnection
$sqlConn.ConnectionString = "Server=my_server;Integrated Security=true;Initial Catalog=my_db"
$sqlConn.Open()

#build cmd sting and run the command
$sqlcmd = New-Object System.Data.SqlClient.SqlCommand
$sqlcmd.Connection = $sqlConn
$query = "SELECT @@SERVERNAME as SQL_INST_NAME, [name] FROM sys.databases WHERE database_id > 4 AND [name] <> 'someOtherDB' "
$sqlcmd.CommandText = $query

#bind results to data adapter
$adp_rs = New-Object System.Data.SqlClient.SqlDataAdapter $sqlcmd
$data = New-Object System.Data.DataSet
$adp_rs.Fill($data) | Out-DataTable

#display data: this shows me what I expect to see
$data.Tables

#connect to other SQL instnace
$localConn = New-Object System.Data.SqlClient.SqlConnection
$localConn.ConnectionString = "Server=MyServer\MyInstance;Integrated Security=true;Initial Catalog=db"
$localConn.Open()

$bc = New-Object ("System.Data.SqlClient.SqlBulkCopy") $localConn
$bc.DestinationTableName = "dbo.table_name"
$bc.WriteToServer($adp_rs)
$localConn.Close()

Open in new window

LVL 1
sqlagent007Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DBAduck - Ben MillerPrincipal ConsultantCommented:
In this case, you are so close.

The line 27 is trying to use a DataSet to insert and you need to use a Table.

So just change line 27 to the code below and you should be good.

$bc.WriteToServer($data.Tables[0])

Open in new window

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sqlagent007Author Commented:
Testing now...THANK YOU!
0
sqlagent007Author Commented:
Thanks this worked great! My only question is what does the [0] do? When I looked at that I thought [0] specified the first column in the Data.Table. Clearly that is not the case because both columns got inserted. What does the [0] signify?

Thanks!
0
Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

sqlagent007Author Commented:
In my mind I was thinking I would need to do something like Data.Table([0],[1]).
0
DBAduck - Ben MillerPrincipal ConsultantCommented:
When you use the DataAdapter.Fill( ) you are getting back a DataSet which contains an array of tables.

So to get the first table which is all that would be there would be to use

$data.Tables[0] -- which is just saying you want the first table and that is all.  Write to sql server does not accept a DataSet as an argument.
1
DBAduck - Ben MillerPrincipal ConsultantCommented:
If you had 2 tables then you would have 2 calls to Write with $data.Tables[0] and $data.Tables[1]
1
sqlagent007Author Commented:
Thank you!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.