Note：This article is specifically targeted at people who are already familiar with SQLCMD mode in SQL Server Management Studio. Although not very novel, readers should understand the basic functionality of the SQLCMD documentation. A basic understanding of the DOS command shell will also give you a better understanding of the following examples.
Overview of the steps:This article is used to process dynamic makefile, although we create and run files in the same script, but as the complexity increases the results will be unpredictable. Therefore, the public is allowed to run the example without accidental loss.
No, sqlcmd I've tried it ...
If you are like me, you may have spent years in and out of sqlcmd mode in SQL Server Management Studio. It has some very good features, but it activates with the cost of disabling IntelliSense. Personally, I always end up closing it and forgetting it for months ... Until now ...
In the next few positions, I'll talk about all the UKJG that have made you abandon sqlcmd mode before. My goal is not to keep your buttons active in SSMs, but to enhance your abilities and you will never forget them again.
At the beginning of the article, we first solve the variable
setvary Variable weakness problem。 In the second part, I will soon move out of command shell basics and for the background as the third section, where happiness really begins. In the third section I'll show you how to write 20 lines of code to create an example of a "perosn" table with 4.5 million unique names.
Oh... I did not use IntelliSense with SQLCMD mode solution, but it is still worthwhile ....
You cannot set a variable programmatically
In sqlcmd mode, you can set variables based on text values. Unfortunately, you cannot set an output variable that is based on SQL Server. It is also not possible to concatenate or environment variables through other SQLCMD variables.
(This is not supported): SETVAR FileName (SELECT @ @Servername + '. txt '): SETVAR Newvar $ (OldVar1) $ (OLDVAR2) (This is not supp orted)
generate a "variable file" from the program
The following script will generate a file that is named "Getservername.sql" in the temporary folder on the system. The contents of the file, a setvar command, and then read from the same script.
After executing "getservername", we will use our new variable $ (file path) to create a test file for the system User (server_principals).
Just for the fun of it, we will also open these two documents at the end of the review Notepad. Please remember that
close Notepad, or SQL will always be running.
Note:
- Note When you file the path, Notepad opens the first file (Getservername.sql). Note that the system parameter, $ (TEMP), has resolved the full path of its own temp directory.
- To know a variable before, because it behaves like an escape character, negates the next variable, the backslash character (\).
in sqlcmd, is there a conditional (If/then) logic?
It's true! How do we solve this problem? We're going to fix this one with another dynamically generated file.
Suppose we want to bulk import local files, run bcp, or handle a group of other sqlcmd features. If our target file does not exist, we usually have no way to exit the script. However, we can use DOS commands to perform similar operations as in the previous example. To do this, we generate a file that contains two scripts (which are met according to the criteria). We first create a "false/exit (exit)" file. When we do, we replace the file when we succeed.
--SQLCMD Error handling:setvar errorcheckfile "Errorcheckfile.sql"--Set DOS commands that would display in the output WI Ndow and run SQLCMD exit.:setvar errorstatuscommand "(ECHO!! ECHO validationfailure. Terminating Script. && echo:exit) "-The following line creates the file. Use the this line for each situation, 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 don't see this. PriNT ' You should don't see this either '
If our script is running correctly, the Output window will contain the following:
All right... Fancy DOS tricks ... Isn't that so? It's gone. This is where we start to have fun. We will take it to the level in the next article, but we need to do the groundwork first. Now, I'll 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 (), Thesta RT) from #statePRINT CAST (@TimeRemaining as VARCHAR) + ' milliseconds to go ... ' Go:out $ (TEMP) \loop2.sqlif sysdatetime () &l t;= (SELECT TOP 1 thestart from #state) BEGIN print ' print ' "Hello from Loop2 '" print ': R $ (TEMP) \loop.sql ' Endelse BEGIN 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
For instructions on how to debug the final
From my experience with SQLCMD mode, the most common cause of unexpected results is due to missing go statements or whitespace between lines not inserted.
- Sqlcmd Love's blank. If your command doesn't work, try adding a space below it ...
- Sqlcmd also loves to parse everything, and will not want the response returned by the SQL engine. If you need sqlcmd to work with output from SQL Server, throw in a go statement.
- Remember not to precede the variable name with a backslash. This does not work: $ (TEMP) \ $ (file name)
- Add more vertical space. Add more go statements.
Original link
Open sqlcmd in SSMs
My Code
My situation
In SQL Server Management Studio: Black sqlcmd Mode
