Short post this evening…
Just in case anyone else flails around looking for this feature, its right under your nose. When you use the PowerShell SQL Server 2008 provider, you have visibility to a single, local machine (and all its SQL Server instances you can access with integrated security) by default. To get access to multiple machines using the provider, simply reference a SQL provider path that contains that machine name. If the (Windows) principal has access to the other machine's SQL Server instance, it will open a connection using Windows auth.
For example, say that I'm on a machine named zmv20. I have access to machine zmv21's SQL Server instance as well.
>cd SQLSERVER:\SQL
>dir
MachineName
———–
zmv20
>dir zmv21 <—- makes a connection to zmv21
Instance Name (on zmv21)
————-
DEFAULT
>dir <—- now you can "see" zmv21 too
MachineName
———–
zmv20
zmv21
Using the test-path cmdlet also works.
>test-path SQLSERVER:\SQL\zmv22 <—- can I login to this machine too?
But bear in mind that this is subject to a connection timeout lag. The error message indicates that its first using WMI to obtain the machine connection.
Of course, all this is doc'd in SQL Server Books Online. Where I missed it a few times…I was looking for the equivalent of a "connect" command. You don't need one.
2 thoughts on “Accessing multiple servers with the SQL Server 2008 PowerShell provider”
Hi Bob,
Sorry off topic but comments are closed on this topic.
I wonder if you could re-enable the ODBC TVP sample. Its currently ofline/inaccessible. If thats too hard just mailing it would be cool.
https://www.sqlskills.com/blogs/bobb/2007/09/20/AnExampleOfUsingODBCAndSQLServer2008TablevaluedParameters.aspx
Cheers. Steve Cooney
Hi Steve,
I was able to get to the sample by clicking the hyperlink on the web page. But just in case, I reposted the file and the entire entry. It should be accessible now. If this still doesn’t work, email me (bobb-at…) since I could not deduce your email address from the comment you left.
BTW, since I posted this, there have been an entire set of SQL Server 2008 ODBC and OLE DB demos posted up on codeplex. Includes TVPs and some others.
Cheers,
Bob
Comments are closed.