Connecting to SQL Server - PowerShell Tutorial

- [Instructor] All right, so let's look at some examples of how to actually connect to SQL Server. So we've got a few different ways we can connect to SQL Server from Power Shell. We can use the SQLPS commandlets, which are going to be what comes with SQL Server. So they're going to be installed on the box, when you install SQL Server, when you install management tools. We're using Invoke-SQLcmd, so that's going to let us run commands against SQL from inside Power Shell. Or we can declare a native SQL connection object and then actually run that natively. So SQLPS, like I said, that's going to be our native functionality for Power Shell and SQL Server. So this is going to connect to SQL and let you navigate SQL as if it's like a file system from within Power Shell. So it's kind of cool in the way it does things there, 'cause you don't have to use the use command, you can just CD into your database and run whatever commands you need to run. So you can actually integrate it very nicely with the native Power Shell. If you've got something that's got to run against every database you can just iterate through the databases very easily that way, by simply doing a dir on your database folder, and that'll give you a list of all your databases. We can use the Invoke-SQLcmd command to run native T-SQL commands against the SQL Server instance. So this is going to be a great way to run commands against SQL Server from Power Shell, that aren't natively exposed in Power Shell. So maybe you've got to do some, either third party command or some native command like say, run aesterproc. We don't care what the output is, we just need to run it in all our databases. So we can do that through Invoke-SQLcmd within out Power Shell scripts. Now the reason you don't want to care about the output is one of the annoying things of Invoke-SQLcmd is it assumes we include a header row for all the output. So you're not gunna be able to take it to output and then use it, because of that annoying header row that you'd have to skip. So if you need to run commands against SQL, get output, and then do something with that output, then you're gunna want to use this more declarative style of connecting to SQL Server and doing stuff. This is what I'm going to show in the demo 'cause this is the most complex way of doing things unfortunately. This is going to be more of a C Sharp style of writing code, to actually go ahead and output that data that you were going to run back to the app, and then do something with it. So, I've talked about it, let's go ahead and look at it 'cause it's going to make a lot more sense once we actually look at it. So here we've got a basic command that we're going to execute. So first thing is, I need to declare a user name and a password. So in this case I'm using the S-A account and I've set a password. So if you've got a local box that you're running this against, you're gunna want to change that account to something other than S-A and welcome12345, which is my default password here. So for my connection string, I'm just gunna run against my localhost and I'm just running it into the master database. And you can see here, I'm just passing in the SQL user name, SQL password, turning off encryption, we're not trusting the certificate. And we have a default connection time-out of 30 seconds. Once we're gunna go ahead and connect, so that's what that next couple of lines is gunna do is it's gunna create a connection object. It's gunna connect with that connection string we've just specified. We're simply going to run the command to select @@SERVERNAME from SQL Server. We're just gunna look see if we have rows, and if we do, return them, and then go ahead and throw away the connection and end this (mumbles) correctly. So if we run that, we'll just run that whole command, we're gunna get a value of true, because we did get a row back. So we actually see that we got data. We can do other stuff now. So this is a very simple example of what we could run. This, obvioulsy, command right here starting on line 13 ending on line 15, could be as complicated as you want it to be, but the end result is we're gunna get data back, and that's all that's gunna matter.