One of the most important tasks for a Windows Server running Microsoft SQL Server is the ability to allow the user to get job execution information from a stored procedure. The stored procedures are used by a user to run a series of business specific or application specific commands to produce or gather data. To illustrate this in more detail let us imagine that a user wanted to know how many times his computer was switched on. This is a common scenario where one wants to know the default value for the Verify Operating System disk usage percentage in a small windows server and how many times his computer was switched on after a specific amount of time.
If we just consider the values that can be produced from the sql stored procedure ” connnect:;” we can see that there are a number of interesting things that can be done with the default value “;” however, if we want to be able to combine different stored procedures into one single command then we need to be able to use the “;” as a delimiter. In previous versions of Microsoft SQL Server, there was only one way to create a collection and that was by using the “;” as a delimiter. However, in MS SQL Server 3.0, this was changed so that when a “;” was used as a delimiter the last part of the line would be ignored and the result would be a constant or single value instead of a series of values. This change did not affect the functionality of the “;” as a delimiter, but it has greatly affected the way that one is able to get more information out of the stored procedure “;”
So, what does this all mean? Well, if we use the following example, we can see that the “;” can be used to get more information out of a stored procedure ” connnect” which is a very common stored procedure within an enterprise wide network. When we executed the following command, we could see the following output, ” connnect;;” which means that the connection to the Microsoft SQL Server has already been established. It also shows that the default value of the “;” is actually false, which means that the stored procedure cannot be executed, and the error message ” connnect: syntax check error” can be seen.
Now, lets look at how the “;” works with the new parameter “;” in SQL Server 3.0. When we execute the following command, we can see that the default value of the “;” is now true, and the execution of the command failed. The reason for this failure is because when the “;” is used as a delimiter, the last part of the statement is ignored and instead the value of the whole statement is passed onto the next connecting block, which is the server. In previous versions of SQL Server, this would cause the whole statement to become flushed resulting in the server crashing.
So what we can see here is that when we change the default value of the “;” from false to true, the execution of the statements is different. When the “;” is used as a delimiter, the result is the same. Therefore, if we want to get-job is already running, we need to specify the execute permission before executing any command. If the user does not have an execute permission, the SQL Server will give the job reading permission instead of the write permission. In other words, we need to tell the server that we do not want it to accept any data input, and then start the connection. Furthermore, if we change the default value of the “;” from true to false, the connection will be prevented from running if any other user has read access to the server.
Another interesting scenario is when you are trying to get a job in a new city. Before you relocate to the new city, you may send your resume there instead of waiting for an employer to search your resume and send you an offer. In the new city, your resume needs to be submitted along with your application form so that you can get a job. However, while you wait for the employer to respond, there may be some positions that are lying vacant. Therefore, the last thing you should do is to wait for the employer to send your resume but rather search for the available jobs.