• Status: Solved
  • Priority: Medium
  • Security: Private
  • Views: 38
  • Last Modified:

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

0
sqlagent007
Asked:
sqlagent007
  • 4
  • 3
1 Solution
 
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
 
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
Protect Your Employees from Wi-Fi Threats

As Wi-Fi growth and popularity continues to climb, not everyone understands the risks that come with connecting to public Wi-Fi or even offering Wi-Fi to employees, visitors and guests. Download the resource kit to make sure your safe wherever business takes you!

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improved Protection from Phishing Attacks

WatchGuard DNSWatch reduces malware infections by detecting and blocking malicious DNS requests, improving your ability to protect employees from phishing attacks. Learn more about our newest service included in Total Security Suite today!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now