In SQLServerManagementStudio: Black SQLCMD Mode

In SQLServerManagementStudio: Black SQLCMD Mode

Note:: This article specifically targets people who are already familiar with the SQLCMD mode in SQL Server Management Studio. Although not very novel, readers should understand the basic functions in the SQLCMD document. If you have a basic understanding of the doscommand Shell, you will also better understand the following examples.

Procedure Overview:This article is used to process dynamically generated files. Although we create and run files in the same script, the results will be unpredictable as the complexity increases. Therefore, this example is run when accidental loss is allowed.
No, I have tried SQLCMD...

If you are like me, it may take you years to access the SQLCMD mode in SQL Server Management Studio. It has some good functions, but its activation takes the cost of disabling smart sensing. Personally, I always close it and forget it for several months... until now...
In the following positions, I will talk about all the ukjg that caused you to discard the SQLCMD mode. My goal is not to enable button activation in SSMS, but to enhance your capabilities, you will never forget.
At the beginning of the article, we should first solve the variable Setvary variable powerlessness. In the second part, I will soon move out of the basic knowledge of the shell command, and set the background as the third section. In section 3, I will show you how to write 20 lines of code to create a "perosn" table containing 4.5 million unique names.
Oh... I didn't use the smart sensing and SQLCMD mode solution, but it is still worth it ....
Variables cannot be set programmatically

In SQLCMD mode, you can set variables based on text values. Unfortunately, you cannot set SQL Server-based output variables. It is also impossible to concatenate environment variables through other SQLCMD variables.

(This is not supported):SETVAR FileName (SELECT @@Servername + '.txt'):SETVAR NewVar $(OldVar1)$(OldVar2)(This is not supported)

Generate a "variable file" through the program"

The following script generates a temporary folder named "GetServerName. SQL" in the system. The content of the file, a SETVAR command, and then read from the same script.

After "GetServerName" is executed, we will use our new variable $ (file path) to create a test file for the System user (server_principals.

For fun, we will also open these two files for review notepad at the end of the year. Remember, Close notepad, or the SQL statement will always be running.

:OUT $(TEMP)\GetServerName.sqlPRINT ':SETVAR FilePath $(TEMP)\' + @@SERVERNAME + '.txt'GO:OUT stdout:r $(TEMP)\GetServerName.sqlGO:OUT $(FilePath)SELECT name, create_date FROM master.sys.server_principalsGO:OUT stdout!!notepad $(TEMP)\GetServerName.sql!!notepad $(FilePath) 

Note:
Note that the first file (GetServerName. SQL) opened in notepad is used as the file path ). Note that the system parameter $ (TEMP) has resolved the complete path of your temporary directory. You need to know before a variable, because its behavior is like an escape character, denying the next variable $ backslash character.
Is there any conditional (if/then) logic in SQLCMD?

Really! How can we solve this problem? We need to solve this problem with another dynamically generated file.

Suppose we want to import local files in batches, run BCP, or process a group of other SQLCMD functions. If our target file does not exist, we usually cannot exit the script. However, we can use the doscommand to perform similar operations, as shown in the preceding example. To this end, we generate a file containing two scripts (which are met according to the conditions ). First, create a "false/Exit (Exit)" file. When we execute, we replace the file and when we succeed.

-- SQLCMD Error Handling:setvar ErrorCheckFile "ErrorCheckFile.sql" -- Set DOS commands that will display in the output window and run SQLCMD EXIT.:setvar ErrorStatusCommand "(ECHO !!ECHO ValidationFailure. Terminating Script. && ECHO :Exit)" -- The following line creates the file. Use this line for each situation that might require us to stop.!! $(ErrorStatusCommand) > $(TEMP)$(ErrorCheckFile) -- See if a file exists. If so, replace our EXIT command with a blank file.!! If EXIST $(TEMP)\*.* @ECHO. > $(TEMP)$(ErrorCheckFile) -- Now read/execute the result:r $(TEMP)$(ErrorCheckFile)!! ECHO Found files in the Temp folder (this is normal).!! ECHO.!! ECHO Checking Next File. -- Reset the file back to Exit!! $(ErrorStatusCommand) > $(TEMP)$(ErrorCheckFile)-- Try again with a file that shouldn't exist!! If EXIST $(TEMP)\ThisFileShouldNotBeHere.txt @ECHO. > $(TEMP)$(ErrorCheckFile) -- Now read/execute the result:r $(TEMP)$(ErrorCheckFile)!! ECHO You should not see this.PRINT 'You should not see this either'

If the script runs correctly, the output window contains the following content:

Found files in the Temp folder (this is normal).Checking Next File.ValidationFailure. Terminating Script. 

Okay... fancy DOS skills... is that true? No. This is where we started to have fun. We will take the level of it in the next article, but we need to do the basics first. Now, I will leave your way to execute the loop.

SET NOCOUNT ONIF OBJECT_ID('tempdb..#state') IS NULLBEGIN    CREATE TABLE #state(TheStart DATETIME2)    INSERT #state VALUES(DATEADD(S, 1, SYSDATETIME()))ENDGOPRINT 'Beginning Loop1'GODECLARE @TimeRemaining INTSELECT TOP 1 @TimeRemaining = DATEDIFF(MS, SYSDATETIME(), TheStart) FROM #statePRINT CAST(@TimeRemaining AS VARCHAR) + ' milliseconds to go...'GO:out $(TEMP)\loop2.sqlIF SYSDATETIME() <=(SELECT TOP 1 TheStart FROM #state)BEGIN    PRINT 'PRINT ''Hello from loop2'''    PRINT ':r $(TEMP)\loop.sql'ENDELSEBEGIN    PRINT 'PRINT ''Last visit to Loop2!'''    PRINT 'DROP TABLE #state'ENDGO:out STDOUTWAITFOR DELAY '0:00:00.1'GOPRINT 'Running loop-checker...'GO:r $(TEMP)\loop2.sql

About the final debugging instructions
From my experience and the SQLCMD mode, the most common cause of unexpected results is the lack of GO statements or blank spaces between non-inserted lines.

SQLCMD. If your command does not work properly, try to add spaces above it... SQLCMD also love to parse everything, and will not want the SQL engine to return the response. If you need SQLCMD to work with the output from SQL Server, throw it in a GO statement. Do not place the backslash before the variable name. This does not work: $ (TEMP) \ $ (File Name) to add more vertical spaces. Add more GO statements. Original article link
Enable sqlcmd in ssms


My code


My situation