Monday, March 24, 2008

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.

Monday, March 24, 2008 11:21:58 PM (Pacific Standard Time, UTC-08:00)  #    Comments [2]  | 
Wednesday, March 26, 2008 1:51:24 AM (Pacific Standard Time, UTC-08:00)
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.

http://www.sqlskills.com/blogs/bobb/2007/09/20/AnExampleOfUsingODBCAndSQLServer2008TablevaluedParameters.aspx

Cheers. Steve Cooney
Steve Cooney
Thursday, March 27, 2008 12:59:52 PM (Pacific Standard Time, UTC-08:00)
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
Name
E-mail
Home page

Comment (Some html is allowed: a@href@title, strike) where the @ means "attribute." For example, you can use <a href="" title=""> or <blockquote cite="Scott">.  

Enter the code shown (prevents robots):

Live Comment Preview

Theme design by Jelle Druyts

Pick a theme: