SQLCMD Mode: give it one more chance

-
- Click on me. Choose me. - asked one forgotten feature when some bored DBA was purposelessly wondering through the Management Studio menu at the end of her long and busy working day.
- Why would I use you? I have heard of no one who does. What are you for? - perplexedly wondered aged and wise DBA. At least that DBA thought she was aged and wise though each day tried to prove to her that she wasn't.
- I know you. You are quite lazy. Why would you do additional clicks to move from window to window? From Tool to tool ? This is irritating, isn't it? I can run windows system commands, sql statements and much more from the same script, from the same query window!
- I have all my tools that I‘m used to, I have Management Studio, Cmd, Powershell. They can do anything for me. I don’t need additional tools.
- I promise you, you will like me. – the thing continued to whine .
- All right, show me. – she gave up. It’s always this way, she thought sadly, - easier to agree than to explain why you don’t want.
- Enable me and then think about anything that you always couldn’t do through the management studio and had to use other tools.
- Ok. Google for me the list of greatest features of SQL SERVER 2012.
- Well... I’m not sure... Think about something else.
- Ok, here is something easy for you. I want to check if file folder exists or if file is there. Though, I can easily do this using xp_cmdshell …
- This is easy for me. – rejoiced the feature.
By the way, having the items of the menu talking to you usually means you should stop working and go home. Or drink coffee. Or both. Well, aged and wise dba wasn’t thinking about the weirdness of the situation at that moment.
- After enabling me, – said unfairly forgotten feature (it was thinking of itself in such manner) – after enabling me you can use all command line commands in the same management studio query window by adding two exclamation marks !! at the beginning of the script line to denote that you want to use cmd command:


-Just keep in mind that when using this feature, you are actually running the commands ON YOUR computer and not on SQL server that query window is connected to. This is main difference from using xp_cmdshell which is executing commands on sql server itself. Bottomline, use UNC path instead of local path.
- Look, there are much more than that. - The SQLCMD feature was getting exited.- You can get IP of your servers, create, rename and drop folders. You can see the contents of any file anywhere and even start different tools from the same query window:

Not so aged and wise DBA was getting interested: - I also want to run different scripts on different servers without changing connection of the query window.
- Sure, sure! Another great feature that CMDmode is providing us with and giving more power to querying. Use “:” to use additional features, like :connect that allows you to change connection:

- Now imagine, you have one script where you have all your changes, like creating staging table on the DWH staging server, adding fact table to DWH itself and updating stored procedures in the server where reporting database is located.
- Now, give me more challenges!
- Script out a list of stored procedures into the text files.
- You can do it easily by using command :out which will write the query results into the specified text file. The output can be the code of the stored procedure or any data. Actually this is the same as changing the query output into the file instead of the grid.
- Now, take all of the scripts and run all of them, one by one, on the different server. 
- Easily
- Come on... I’m sure that you can not...

-Why not? Naturally, I can do it using :r commant which is opening a script and executing it. Look, I can also use :setvar command to define an environment variable in SQLCMD mode. Just note that you have to leave the empty string between :r commands, otherwise it’s not working although I have no idea why.

- Wow.- She was really impressed. - Ok, I’ll go to try all those…
-Wait, wait! I know how to google the SQL SERVER features for you! This example will open chrome explorer with search results for the “SQL server 2012 top features” ( change the path to suit your PC):

“Well, this can be probably useful stuff, maybe this feature is really unfairly forgotten”, thought the DBA while going through the dark empty park›ing lot to her lonely car. “As someone really wise once said: “It is what we think we know that keeps us from learning. Learn, unlearn and relearn”.

Comments

Post a Comment

Popular posts from this blog

Unlocking Microsoft Fabric: A Simple Guide when you only have a personal account.

-
Image
" You cannot create a Microsoft capacity using a personal account. Use your organizational account instead." What a disappointment and frustration! I've been trying to set up Microsoft Fabric for a while now and figure out how to work around this error. I use my personal Gmail for an MVP subscription to learn and experiment with Azure services. When I visit app.fabric.microsoft.com and try to use my private Gmail address, I keep getting a similar message: It's frustrating because I'm eager to dive into Fabric and explore its capabilities. I would get the same error trying to set up Azure Data Catalog, PowerBI embedded, and more. What are the different account types and how do they differ? A work or school account is created through Active Directory or other cloud directory services, such as Microsoft 365. On the other hand, a personal account is one that's manually created for individual use, consisting simply of a username and password. After digging into ...
Read more

ETL to ELT journey: Break free your Transformations and discover Happiness and Data Zen

-
Every data integration pipeline consists of 3 stages: Data Extraction (E), Data Transformation (T) and Data Load (L) During the  Data Extraction stage, the source data is read from its origins: transactional databases, CRM or ERP systems or through data scraping from web pages. During the  Data Transformation stage, the necessary modifications are applied to the source data. This includes data filtering, enrichment or merging with existing or other source datasets, data obfuscation, dataset structure alignment or validation, fields renaming and data structuring, according to the canonical data warehouse model.  During the Data Loading stage, the data is stored in the pipeline destination, which could be a staging area, data lake or data warehouse. There are two principal methods for the data integration process: transferring it from where it originated to the destination, where the data will be used for analysis, ETL and ELT. The difference between ETL and ELT pipelines...
Read more

Coding is a rollercoaster of efficiency and eyebrow-raising discoveries.

-
Image
Data Engineers or Developers - many of us love to be gourmet chefs in the kitchen. When it comes to planning and design, we would rather throw all ingredients in the pot and see how it comes out.  Coding without a plan is like assembling a puzzle in a dark room. The result will most probably be unexpected and off the canvas. Whether you follow Waterfall or Agile development strategy, planning and design phases are non-negotiable and are essential to reduce development cycles and redo work. Once upon a time, one data engineer created an amazing piece-of-cake automation pipeline. This masterpiece had very complex logic, pulling data from multiple sources, and merging and persisting the data in a complex, incremental way. When the pipeline started to run successfully and automation flows worked, the data engineer got very excited and considered this development done. A few days later QA engineer found out that the result dataset was never created in the destination. Why has that ...
Read more