Transact SQL – (Global) script variables

Problem:

When running a SQL script using the command sqlcmd tool if you switch context to another database, by using the “use database” command, then any variables defined before this are lost. A similar thing happens if you use the “go” command to group, or batch, commands in the script.

Solution 1:

Create a temporary table, e.g

CREATE TABLE #GlobalVariables (variableOne varchar(30), variableTwo int)
INSERT INTO #GlobalVariables (variableOne, variableTwo) VALUES ('ABCdef', 222222)

Solution 2:

Use script variables

:setvar variableOne "ABCdef"
:setvar variableTwo 222222

To access these in your SQL code you would do the following:

UPDATE #GlobalVariables SET variableOne = '$(variableOne)', variableTwo = $(variableTwo)

The current identity (IIS APPPOOl\xxxxxxx) does not have write access to…

If you encounter the following error then this may be the fix you’re looking for.

---

Server Error in ‘/’ Application.

---

The current identity (IIS APPPOOL\xxxxx) does not have write access to ‘C:\Windows\Microsoft.NET\Framework\v4.0.30319\Temporary ASP.NET Files’.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Web.HttpException: The current identity (IIS APPPOOL\xxxxx) does not have write access to ‘C:\Windows\Microsoft.NET\Framework\v4.0.30319\Temporary ASP.NET Files’.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[HttpException (0x80004005): The current identity (IIS APPPOOL\xxxxx) does not have write access to ‘C:\Windows\Microsoft.NET\Framework\v4.0.30319\Temporary ASP.NET Files’.] System.Web.HttpRuntime.SetUpCodegenDirectory(CompilationSection compilationSection) +10003412 System.Web.HttpRuntime.HostingInit(HostingEnvironmentFlags hostingFlags, PolicyLevel policyLevel, Exception appDomainCreationException) +204 [HttpException (0x80004005): The current identity (IIS APPPOOL\xxxxx) does not have write access to ‘C:\Windows\Microsoft.NET\Framework\v4.0.30319\Temporary ASP.NET Files’.] System.Web.HttpRuntime.FirstRequestInit(HttpContext context) +9947380 System.Web.HttpRuntime.EnsureFirstRequestInit(HttpContext context) +101 System.Web.HttpRuntime.ProcessRequestInternal(HttpWorkerRequest wr) +456

 

---

Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.6.1055.0

---

Simply run this code, from the Developer Command prompt: aspnet_regiis -i

Update 3-Jul-17:
On Windows Server 2012 you may need to run this command:

dism /online /enable-feature /featurename:IIS-ASPNET45 /all

 

Repairing Windows

Just some random jottings on tools to repair Windows, release 10 and maybe 7 and 8 as well.

DISM (Deployment Image Servicing and Management)

Some of the options for this tool are not available depending on Windows version (Enterprise, Professional or Home) and also release.

Sample commands

• DISM.exe /Online /cleanup-image /scanhealth
• DISM.exe /Online /Cleanup-image /Restorehealth

SFC (System File Checker)

Tries to repair any file corruption in Windows files.

• sfc /scannow
• sfc /verifyonly

Enabling xp_cmdshell in Microsoft SQL Server

By default the sproc is disabled, something to do with a security concern.

To enable the procedure run the following in a query window:
EXEC sp_configure 'xp_cmdshell', 1
Reconfigure


If that doesn’t work, and it didn’t for me – I’m running SQL Server 2008 R2. Then run the following commands:
EXEC sp_configure 'show advanced options', 1;
Reconfigure;
GO
EXEC sp_configure 'xp_cmdshell',1
Reconfigure
GO


You can then run something like:
EXEC xp_cmdshell 'iisreset'
GO