invoke-BulkCopy Error

Mar 23, 2011 at 8:34 AM

Team,

I am getting following error for invoke-bulkCopy

Exception calling "WriteToServer" with "1" argument(s): "Login failed for user 'userName'"

I am trying to supply database server info and not an active connection to invoke-bulkCopy (invoke-bulkCopy anyways does not take an connection parameter) 

It generates a connection string by creating a new-connection object using the supplied parameters and uses this to generate a new-object Data.sqlClient.sqlBulkCopy with connectionString as parameter.

$connection=new-connection -server $server -database $Database -User $user -password $password

$bulkCopy = new-object "Data.SqlClient.SqlBulkCopy" $connectionString

This connection is immediately closed in next line.

Now bulkCopy operation has a connection String that is created above doesn't hold a password value. I am supplying Password when I call the function. ConnectionString when pass doesn't hold password, could this be creating an issue? You can check the $connectionString variable.

Tried modifying code at my end to supply new created connection $connection in the function as a paramter when $bulkCopy is created rather than passing $connectionString and closed connection after the bulkCopy is called i.e. after

$bulkCopy.WriteToServer($records)

It Worked.

Is this the right way to do?

Please get back with some examples.

Coordinator
Mar 24, 2011 at 2:48 AM

That bug has been there since november.  It's fixed now in changeset 59056.  I ended up "factoring out" a new-connectionstring function from the new-connection function, and just called it instead of the nonsense I had been doing to get a connection string.

Thanks again for finding a bug.

Let me know if you still need example code.  I suspect that it will work as expected now that the connection string is correct.

Mar 24, 2011 at 11:20 AM

Thanks Mike.

I didn't find any difference in adolib.ps1 change set 59056 and 59024. Have you put the changes mentioned above to invoke-bulkCopy in changeset 59056?

Also, will be good have an example code.

Regards,

Amol

 

Coordinator
Mar 24, 2011 at 12:39 PM
Just double-checked, and the changes are definitely in 59056. There's a new function called "new-connectionstring" at the top (not exported from the module, though), and invoke-bulkcopy has been changed to use it rather than using new-connection as before.

Here's the code I used to test it. I created a new database called "TestBulkCopy" and copied the DatabaseLog table (structure only, no data) from AdventureWorks there:

$server = '.'
$db = 'AdventureWorks'
$sql = "SELECT * from dbo.DatabaseLog"

#test simple query using ad hoc connection and NT authentication
$rows=invoke-query -sql $sql -server $server -database $db
Invoke-Bulkcopy -records $rows -server '.' -database TestBulkCopy -table DatabaseLog -user test_login -password 12345

On Mar 24, 2011 6:20am, amolzagade <notifications@codeplex.com> wrote:
>
> From: amolzagade
> Thanks Mike.
>
> I didn't find any difference in adolib.ps1 change set 59056 and 59024. Have you put the changes mentioned above to invoke-bulkCopy in changeset 59056?
>
> Also, will be good have an example code.
>
> Regards,
>
> Amol
>
>
>
> Read the full discussion online.
> To add a post to this discussion, reply to this email (SQLPSX@discussions.codeplex.com)
> To start a new discussion for this project, email SQLPSX@discussions.codeplex.com
> You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe or change your settings on codePlex.com.
> Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at codeplex.com
>
>
Mar 25, 2011 at 5:28 AM

Thanks Mike.

I got the file on http://sqlpsx.codeplex.com/SourceControl/changeset/view/59056#551047 (This under ChangeSet 59056>branches>Mike) 

It works fine for me. I was able to do a Invoke-BulkCopy.

Earlier, I was looking into http://sqlpsx.codeplex.com/SourceControl/changeset/view/59056#573356 (ChangeSet 59056>trunk>Modules>adolib)

Don't we need to update the latest file on this path too? ChangeSet 59056>trunk>Modules>adolib

Thanks again for you help.

Amol