Power Automate Desktop & SQL Server Management Studio - Full Tutorial

let's set up SQL server and SQL Server management studio and connect to it from Power automate desktop I'm an assistant a Microsoft MVP let's learn some power automate desktop to install SQL server and SQL Server management Studio we navigate to this URL scroll a little bit down and under developer click download now that will download the executable we will run it that will open up the installation guide here you will choose the basic accept install and we will Fast Forward that's it and now we will install the SQL Server management Studio so here you click install ssms scroll a little bit down here download ssms choose the free download for SQL Server management Studio that will download another executable we will also open and run that here we will click install that's it we can click close here and we can close our browser we will also choose to close here and then we go to our start menu search for SQL Server 2022 configuration manager click that one over here you will see the SQL server and the status running you can choose to click it and either right click and click stop pause or you can use these buttons up here so it's running that's great I can close this one again then I go to the start menu again and I type management studio and this is the SQL Server management Studio I want to open it so click here and here we can connect to our database you'll see the server name and the authentication we're using Windows authentication and this server name is our workstation name in case this is blank we're going to find it go to your start menu type in CMD and click enter now you type who am I click enter and if I move it down here you can see that this here that is the server name so simply just Ctrl C Ctrl V paste it in up here and we'll click connect that's it this is the SQL Server management Studio we have the overview over here and to write a new query I just go up here and click new query that will open up the query window to the right best practice is to write these query in caps lock so I'll click it on my keyboard here and that is because these queries are easily written when they are in caps lock we will start by creating a new database we can work on so here you will write create database and we will have the database name let's call it Blacklist and to end the line I'll have a semicolon then click enter type in go now we can execute this query by clicking execute over here that will create the database if you want to inspect it you can go over here to the plus into databases and you will see our newly created database over here called Blacklist to use this database I go over here to my query window again I'll click enter and then I will say use Blacklist and I'll say semicolon again I'll click enter and I'll say go now I only want to execute the use Blacklist and not to create database because we already created that to do so I just Mark these two here and then I'll click execute you'll see down here that our commands complete successfully if we try to create a database again we would be getting an error now I want to create a table in my database so I go up here and then I click enter to create a table I will say create table and then I'll give my table a name I will call mine companies like that then I will click enter I'll have a parentheses start and here comes my table but let's create the end first so I'll click enter twice so I go down here I'll have a parenthesis end and a semicolon I can also click enter once more and have it go here now go up here inside these parentheses what I will do here is that I will say that then space and I'll disable the caps lock and here I'll say int that means that this vat number will be of the format integer integer are whole numbers that could be 1 0 2 minus five whole numbers that is without decimals then I'll have another space I'll enable the Caps locks again and here I'll say primary key that means that this is the unique identifier for this table here I will have a comma I will click enter I'll also want the company name so so I write C I disable caps lock and I will say company underscore name and I'll have space here I'll say VAR Char and I'll Define that this will be 30 characters I also want this company name to have a value I will not allow null values null means nothing it just doesn't mean an empty value it means nothing so here I'll say not null in caps and I'll have a comma and I'll click enter I'll also have the last header which will be reason and here I'll say VAR chart parentheses 40 that one could be a little bit bigger and I will have another comma that's it let's try to run this query that will create the table in our database so I mark this I'll click execute and if I go over here to Blacklist then I can click this plus I can click Plus and in this tables you can see a dbo companies that is the table that we just created if I click this plus here you can see if I go into columns I click this plus here you can see that we have the vat company name and reason let's also fill some data into this table so I click enter once more to fill data into a table again I'll enable the caps lock I will say insert into and then I'll take the companies here if I start writing C you can see that this intellisense comes up companies is marked that means that I can just put in the tabulator on my keyboard and it will auto fill you should always use this intellisense since this reduces error so we insert into the companies I'll have a space and a parentheses start then I want to define the column headers and here I'll just type in that comma caps lock and I'll type c now you can see we have this intelligence comes up again I can just press tab I'll have another comma here I'll say R reason that's it then I'll click enter now I'll have the Caps locks again I'll say values like this then I'll click enter and I'll click the tabulator now I'll have a parentheses start and we can start by creating the first row that will go into our table so just right after me I'll say 4 5 8 6. five seven six four that will be the first cell of this row so I'll have a comma I'll have a space and this is just for readability the first number here that was our vat number that was an integer we defined it up here the next one that is a VAR Char or a string of variable length to type in the value of this we need to surround it with single quotation marks let's call the first company sub photo like this and have another single quotation mark now we need a reason so here I'll have a comma and again I will surround it with single quotation marks here I'll say didn't pay like this and another single quotation mark let's end the row by a parentheses and have a comma because we are ready for the next line click enter here and we can create the next one I'll create it a little bit faster feel free to pause the video If things goes too fast that's it that's our three rows into our table I'll click another enter and then I'll move back here and I'll have a semicolon click enter and I'll have a go so this one will insert this value into our tables so mark it here click execute and you'll see that it says three rows affected let's fetch the data so I go back here I'll click enter again I'll enable the caps lock so here I just want to select all the data from the table called companies to do so I will say select then I'll have a star asterisk that means that I will select everything I'll click enter then I'll say from where should I take the data from I'll take it from there companies like this that's it and here we don't really require a go let me explain what the code does the go separates these queries into batches that means that if we want to run it all at once it will first run this then it will run this this and this in case we haven't done those then we would get an error because these steps needs to be done sequential but the year after we have this go we can just select the data from companies we don't need further goes that means that if I mark this I'll click execute we now have selected all the data from this table let's say I want to fetch with the condition and go up here I can click enter I'll say where and that will be well that equals and let's give it a number I can say one two one five seven three two one and I will mark this I'll execute query and here you can see that we have our fetch data with our condition let's copy our queries so I will control a here in the query window I'll say Ctrl C and I'll go down to my start menu I type Notepad and open it let me drag it in from my other screen here and I'll Ctrl V so now I have it stored here let's close down the SQL Server management studio and here do I want to save these queries amongst others no I don't really need that the data will be in our database we have now set up a SQL database please open power automate desktop and create a new flow then we will create the SQL Server database with power automate desktop to connect to a SQL database go up to actions and find a open SQL connection and drag it in you can use the Builder by clicking here but it is rather slow so I prefer to creating this connection string myself first we will have our driver so start by writing driver equals and then in curly brackets I'll say SQL Server Like This and curly bracket end then I'll have a semicolon then I'll say server and I'll connect to my local database we set up the SQL server in the previous lesson if you haven't watched that you can do it by clicking the video in the upper right corner so here I'll say server equals then I'll have a parentheses start I'll say local and the parentheses end I'll have another semicolon and here I'll say integrated security like this equals true another semicolon and then I'll have the database name so I'll say database equals and my database is called Blacklist like this this Integrated Security equals two that means that we're using our Windows credentials we have variables produced called SQL connection we can refer to that connection when we want to access this database go click save here you can try to run it so if I go up here and click run it will take a few seconds and if I go over here to SQL connection and flow variables and I double click here we have opened it that's fine I can click close here usually you also want to close down this connection so I go up to actions and then I'll find a close SQL connection and drag it in this will just close our SQL connection that we have just opened which is fine but not much fun here let's also execute a SQL statement so I go up to actions and then I'll find a x cubed SQL statement drag it in between the open and close here we will get the connection by our SQL connection variable which we created up here it's also possible to use a connection string here but if we want to perform multiple queries that is not preferred the SQL statement again we will just use the SQL queries from the last lesson again I will just use this select it so I'll go copy it go back here and I'll paste it in here Ctrl V you also more than welcome to write your own SQL query down here our variables produced is called query resolve that will contain the query result in a data table format I'll click save let's try to run it now so if I click run here again it will take around probably 10 seconds we open it and we executed it that's it if I move over here to query result and double click it you can see that we looked up this vat number we did it directly in the SQL Server management Studio before but here we have it and now we can use power automate desktop to connect to a SQL database so I'll click close here we have some sample data I downloaded it to my desktop you can find this Excel sheet in the video description so if I double click this Excel sheet and here I have my company sheet this is a four column table I'll have the vat number which is unique identifiers that those are bad numbers I have a company name and then I have two blank columns called company check and the reason I want to look up each vat number in this Excel sheet I want to look it up inside my SQL database here I want to know if these ones are blacklisted companies or okay companies in case they are blacklisted I also want the reason here and that is the use case so close this Excel sheet we can also close down this notepad that we created before so I'll do that I want to grab the path of this companies here here I will say shift right click I'll move down and say copy as path back to Power automate desktop in the beginning I want a set variable set variable and I'll drag it in here this will be my Excel path so I go down here to variable and then I'll say Excel path and in the value I'll say Ctrl V I'll remove the quotation marks in the start and in the end power automate desktop don't use those the reason why we are keeping this Excel path in a variable instead of just hard coding in in an Excel action is that this is best practice this path can change and it will certainly not stay on my desktop this Excel sheet if I click save here then if I come back to the flow and have moved my Excel sheet I can easily change the value of this variable let's also launch Excel so I go up here and I'll find a laurance Excel and I'll drag it in here I'll open up the following document document path I just created that up here called Excel path so I click this x and double click the Excel path I'll not make my instance visible if I scroll a little bit down you can see here that the variables produced is called Excel instance I'll refer to that one when I want to use this Excel book called stored in the Excel path here I'll click save I also want to read from this book now we're just launching it here with the launch Excel so I go up to actions then I'll find a read from Excel worksheet and I'll drag it in here we're reading in the Excel instance what do I want to retrieve I click this drop down and I'll say all available values from the worksheet that means that I get all the data inside this Excel sheet you want to click Advanced here because our data contains column names take that one the variables produced you can see here it's called Excel data that will be a data table that we can work with click save here what you also want to do is in the end we want to close this Excel instance so here I'll find a closed Excel I'll drag it down here and for now we will not save it I will click save Let's test run this that we can actually open up the Excel we can read from it and we can close it again we haven't connected the Excel to our SQL queries yet but we will do so very soon that's it if you go over to your flow variables Excel data double click it here now we have our Excel sheet loaded into a data table so we can work with it let's iterate through this data table so that is for each one of these rows we want to use this vat number in our query right now it's quite static it says one two one five seven three two o but I want to use these vat numbers one by one let me click close here so go up to actions find a for each drag it in between the open and execute SQL statement the value to iterate that will be my Excel data so click this x here Excel data this was the data that we loaded into our data table that came from Excel here we stored into a variable called q and item we can rename it this is just the reference name so I can say current company that might be a little bit better because that says what's going on here you all click save so now we're iterating to each value then let's drag in the execute SQL statement by tagging it with the mouse and dragging it into the for each we need to replace this static value with the value from the Excel data which will now be in the current company one by one so double click here instead of this one here I will delete it we will use the qn company because the qn company that is the current row when we do this iteration one by one that will get stored here so first we'll go over to this x variable picker always use this variable picker whenever you can then I'll take you and Company but this will be the entire row I only want the that number so here I'll either need to put in there column index or the column name column name is most stable because people can put in extra Columns of course they can also rename columns but that is less likely to find the column name click save here go over to your Excel data double click and the column name that is vat no with capital letters so I'll click close here I'll go into the execute SQL statement again and I move over here here you will have a hard bracket single quotation marks that space no then a single quotation mark and a parenthesis a hard parentheses end and we will produce a variable called query result let's try to write that one out so I click save here and let's just have a display message I'll drag it in still inside the for each year let me drag it over here I'll go down to message to display I'll click this little X here and I'll double click the query result go to variables produced let's not confuse ourselves so I'll just untick this button pressed since we are not going to use it I'll click save so what I will do here is that I'll run to this Excel data let me open it again I'll look up each vat number in our SQL database and then I'll get a query result back that means that if we can't find these bad numbers in the SQL database their query result will be blank let's go test it so here I'll click run and again it will just open that will take like five seconds and the queries will run very fast so the first one was blank I'll click OK blank okay here we have a blacklisted company subphoto didn't pay blank we have another one called might Sam that is root Behavior you'll see here that we get the entire row that is fine and we have the laby post let me just run quickly to it that's it so our automation works now now we just need to make a little bit Beauty here so we can write the query results back to excel so I will use an if go up here to actions and then file an if here I'll drag it in after the execute SQL statement and here I want to look at the query result so if I go over to this x here and here you can see the query result that is also a data table so if I first let me just double click here so I want to say how many rows are in this data table and that means that if we have zero we know that we have a blank result then it's not a blacklisted company if it's not zero then it's a blacklisted company so what I can do instead here I can actually delete this and go back to this x I can go down to query result and click this Arrow here and then I can take the rows count for example let me double click here if I'll say the operator that will be not equal to not equal to 0 then I know I have a blacklisted company now I can click save let's try to move the display message up here so right now we will only print the blacklisted company and then we have took another step in our solution for this entire project we're opening it you can see that we're running through now we have the first blacklisted company the second and we have the Third that's it so now we're able to find three blacklisted companies in our data set and now instead of using these display messages let's update our data table called Excel data and let's print that data table back to excel so first I will delete this display message we'll need a helping variable and that is because we want to know where to write it back into this table that is not stored in the qn company so we don't know actually for each of these iterations we don't know where we are so we go up to actions and then we'll find a set variable again this is just the helping variable drag it in just before the for each we will call this one here row count and we will give it the value 0 that is because data tables are zero indexed the first row is index 0. I'll click save then I want to increase this variable by one here in the end you can either use a set variable again or an increase variable they do the same I prefer the set variables so I drag this one that one in here in the end so go up to this X we will update the row count in the value click the X again row count we're going to add one to it and that is because for each iteration we want to add one to it so simply just go in here after the T and say plus one and we'll click save so now here in the if that means that we know we have a blacklisted company then we want to update the Excel data if I go over here to Excel data and I want to update the column called company checked I want to have a blacklisted in here and then the reason that will be the reason that we have in our SQL database so I'll click close here we will find another set variable and drag it in here first of all I want to update the Excel data data table so I click this x double click the Excel data but I also want to specify what cell and what column I want to update so I go up here and I can do that with indexes or variables or hard coded values first one that is which cell I want to update and I use the row count for that let me drag it over here and go so I'll have a hard bracket here and I'll use the row count variable so I'll just type in row count we are inside these percentage signs that means that we don't need another pair of percentage signs then I can have a hard bracket end I'll have a hard bracket start and here I want to save it in the company check column so I'll have a single quotation mark and here I'll say company check another single quotation mark and then a hard bracket end the reason here is that the company check that is text another variable so this is a text variable coming from the data table and what value do I want to fill in the company check well we just go with blacklisted that's what we know so here I'll click save then we'll have another similar variable than this because we are lazy I prefer just to mark this Ctrl C Ctrl V I know we have two similar ones go in here now we're not going to update the company check but the reason so let me delete this company check type in reason instead we want to move into the query result for the reason so what I will do here is that I'll delete this blacklisted to see what we're actually going to take out I'll click save here this will give us an arrow that is fine but I want to go into this square root result I know that this is empty but if we have a blacklisted company then we will have one row in this data table that means that we can refer to this query result variable then we can say 0 for the first row and I want to get the reason back remember the query result that came from the execute SQL statement I'll click close here I'll move in here so the value that should be click the X here double click the query result move up here and again we set the zero and here I'll move into the reason of the reason column of the query result so I'll say reason and I'll have a hard bracket end so I'm updating the Excel data the row count that will be the current row that we're in and the reason column I'll do that by taking the value from the query result the first row and the reason column and that one we got from the execute SQL statement B keep in mind that we are in the if so we know that we have a blacklisted company we know that we have something here then I can click save so now that is done I also want to have in OK value if these two if this if is not zero that means that it equals zero and that means that it's not a blacklisted company I also want to have an OK value but first I'll go up to actions find an else and drag it in here and again I'm lazy so I have this in my clipboard I click this end and when I press Ctrl V that will paste in another one of this set variable double click to open it instead of blacklisted I will have the oh okay like this I can click save so now we are running through this with the data I recommend you to go run it just to test it to see that we can update our data table and then if that works we just need to write it back to excel so here running through all 10 rows you can make it faster if you are changing this run delay tool one you can do this this is just the Run delay here in this editor you'll only see it in here when you move your robot to production it will be a run delay of one that is because you might often want to see what's going on when you're building these robots now go up to Excel data and click here here there you go we have now updated our Excel data data table with the data from the SQL database now we just need to save this to our Excel sheet so I could close here go up to actions and then find a right to Excel worksheet drag it down here between the closed SQL connection and close Excel the value to write that will be the Excel data double click here where do I want to write it since this is a data table I can start by writing in the upper left corner and then it will automatically fill in into the correct cells I'll say column 8 and Row 2 and that is because we have headers in row one so here I'll click save one thing that we'll need to do is to go into close Excel double click here say save document and we'll click save here now try to run this again it will take a few seconds if you like this video then you can really help me a lot by giving it a thumbs up that's it here we have closed it let me move out here let us go into companies there you go YouTube wants you to watch this video next in case you want help with your power automate problems then you should join the eye level automation Discord your guide and invitation is right up here

Channel: Anders Jensen

Views: 7,362

Keywords: power automate desktop, sql, sql server, sql server management studio, power automate desktop and sql, power automate desktop connect to sql server, power automate sql, power automate desktop sql connect, anders jensen, power automate desktop tutorial

Id: _rgHDiaJBds

Length: 36min 41sec (2201 seconds)

Published: Mon May 08 2023