Search an MS-SQL database for a specific named column

Need to find a column in a database? The following SQL will return the tables, and columns, that contain a wildcard value

SELECT c.name AS ColumnName,
       t.name AS TableName
FROM sys.columns c
JOIN sys.tables t
 ON c.object_id = t.object_id
WHERE c.name LIKE '%column_name%'
ORDER BY TableName,
         ColumnName;
Advertisements

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

 

Embedding a file in an assembly

Why? I have some unit tests that need to run off of some pre-defined static XML data and while the tests run fine so long as the file and assembly are in the same location as soon as the assembly is moved elsewhere they start to fail, “file not found” exceptions.
So if the file is embedded in the assembly I don’t need to worry about where it is located anymore.

The assumption, in the following, is that the file is XML that has been serialised from an existing List and is being reloaded back into the same, and that the file is in the current assembly. “_states” is a global variable.

So here are the basic steps for this:

In your Solution Explorer view right-click on the file, in this case “state.xml”, and view it’s Properties, make sure that the “Build Action” is set to “Embedded Resource”, optionally set the “Copy to Output Directory” to “Do not copy”.

Once this has done then the file can be loaded and copied into the required object using:

GetFileFromAssembly

An important thing to remember is that the “Namespace” prefix to the file is the “Default namespace” as entered in the Application properties tab and not the namespace used in the referencing file.

You can reference the file, if it’s in a sub-folder by adding the folder just after the namespace, e.g.

GetFileFromAssemblyNamespace

 

 

Debugging multiple websites from one solution? Breakpoints not being hit?

If you see this tooltip when examining your break points “The break point will not currently be hit. No symbols have been loaded for this document” then you’ve found the same issue that has been driving me mad for a few hours, it must mean you’re new to debugging web sites as well!

Simple solution (sic) is to right-click on your solution file and select “Properties” and then under Common Properties->Startup Project select the “Multiple startup projects” and set whatever action you require against the project(s) you want to debug.

Simples! Applies to Visual Studio 2010 onwards, and not just VS2013 as I at first thought.

Cross Origin Resource Sharing or using ajax sensibly

By default ajax requests that go across domains are blocked by most modern browsers, read Chrome, Firefox and Internet Explorer 8+.

To enable this functionality you need to add new headers to your sever specify the domains that can access your data or just let anyone in.

Full details can be found here: http://enable-cors.org/index.html

If you cannot be bothered to read the article then you just need to add the following headers.

  1. Access-Control-Allow-Headers – Try X-Requested-With, Content-Type, Accept
  2. Access-Control-Allow-MethodsGET, POST, PUT, DELETE, OPTIONS
  3. Access-Control-Allow-Origin*
  4. AllowGET, POST, PUT, HEAD, DELETE, TRACE, COPY, LOCK, MKCOL, MOVE, PROPFIND, PROPPATCH, UNLOCK, REPORT, MKACTIVITY, CHECKOUT, MERGE, M-SEARCH, NOTIFY, SUBSCRIBE, UNSUBSCRIBE, PATCH, SEARCH

Obviously the above is over kill and can be cut down to meet your specific requirements.
For example (2) could be reduced but you may find you have to keep the OPTIONS value in as this can be issued by the browser before a GET or POST to determine what the server supports (a preflight request).
The value of “*” for (3) allows any site to access your data using XmlHttpRequest, this could be reduced to a list of space separated domains if required.
The final list (4) is again over the top and could be reduced significantly.

If you happen to be using OAuth 2.0 then and sending the access_token via the header, rather than the querystring, then you need to add Authorize to the Access-Control-Allow-Headers, as the token should be sent using the Bearer field.
In fact if you use any custom headers then you’ll need to add them all as CORS strips them all out.