SQL Server – List all the Constraints by Table or by Column Name

List all Constraints of the Database:

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
OR
SELECT OBJECT_NAME(object_id) AS ConstraintName,
SCHEMA_NAME(schema_id) AS SchemaName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'

List Constraints  on table column:

 Select SysObjects.[Name] As [Contraint Name] ,Tab.[Name] as [Table Name],Col.[Name] As [Column Name]
From SysObjects Inner Join (Select [Name],[ID] From SysObjects Where XType = 'U') As Tab
On Tab.[ID] = Sysobjects.[Parent_Obj]
Inner Join sysconstraints On sysconstraints.Constid = Sysobjects.[ID]
Inner Join SysColumns Col On Col.[ColID] = sysconstraints.[ColID] And Col.[ID] = Tab.[ID]
WHERE Tab.[Name] ='Employee' AND Col.[Name]='DOB'
order by Col.[Name]

The provided anti-forgery token was meant for user “”, but the current user is “user@example.com”

While working with Asp.net MVC, I got HttpAntiForgeryException exception (The provided anti-forgery token was meant for user “”, but the current user is “user@example.com”) at login page. It happens when user login with valid credentials and re-directed to inner page ofr the application. At inner pages he press the browser back button and will show login page again. He entered the valid credentials again that time, this exception will generate and display on the browser.

To fix this exception: Need to add [OutputCache(NoStore=true, Duration = 0, VaryByParam= “None”)] line to your login get method.

[AllowAnonymous]
[HttpGet]
[OutputCache(NoStore=true, Duration = 0, VaryByParam= "None")]
public ActionResult Login(string returnUrl)
{
ViewBag.ReturnUrl = returnUrl;
return View();
}

How to fix apache drill startup error on windows-10

Apache Drill can be downloaded from here. It will works fine on windows 7 but it is giving error when you install it as embedded mode on Windows-10. Error is something like that after running the command:

sqlline.bat -u “jdbc:drill:zk=local”

Error during udf area creation [/C:/Users/<user>/drill/udf/registry] on file system [file:///] (state=,code=0)

After googling I found this is related to ownership and missing folder on users directory. To resolve it we need to manually create the folders and update the ownership of them. Below are the commands which we need to run before start.

mkdir “%userprofile%\drill”
mkdir “%userprofile%\drill\udf”
mkdir “%userprofile%\drill\udf\registry”
mkdir “%userprofile%\drill\udf\tmp”
mkdir “%userprofile%\drill\udf\staging”

takeown /R /F “%userprofile%\drill

What is tuple and when to use

A tuple is a data structure that has a specific number and sequence of elements. It allow us to combine multiple values with similar or different types into single object without creating a custom class. It can also be used where we have to return multiple items from the method instead of “out” keyword.
From MSDN:

Tuples are commonly used in four ways:

  • To represent a single set of data. For example, a tuple can represent a database record, and its components can represent individual fields of the record.
  • To provide easy access to, and manipulation of, a data set.
  • To return multiple values from a method without using out parameters (in C#) or ByRef parameters (in Visual Basic).
  • To pass multiple values to a method through a single parameter. For example, the Thread.Start(Object) method has a single parameter that lets you supply one value to the method that the thread executes at startup time. If you supply a Tuple<T1, T2, T3> object as the method argument, you can supply the thread’s startup routine with three items of data.

how to remove helper warnings at visual studio mvc view page

While working on MVC project we can get warnings in the MVC view/ partial view / layout page.

The name ‘ViewBag’ does not exist in the current context

‘System.Web.WebPages.Html.HtmlHelper’ does not contain a definition for ‘ActionLink’ …

‘System.Web.WebPages.Html.HtmlHelper’ does not contain a definition for ‘AntiForgeryToken’ …

These are due to wrong version in the config file. To fix this we need to update the version of the few things and reopen the project.

Here are few things which I have updated:

<add key="webpages:Version" value="2.0.0.0" />
<add key="webpages:Version" value="3.0.0.0" />
<dependentAssembly>
  <assemblyIdentity name="System.Web.Helpers" publicKeyToken="31bf3856ad364e35" />
  <bindingRedirect oldVersion="1.0.0.0-2.0.0.0" newVersion="2.0.0.0" />
</dependentAssembly>
<dependentAssembly>
  <assemblyIdentity name="System.Web.Mvc" publicKeyToken="31bf3856ad364e35" />
  <bindingRedirect oldVersion="1.0.0.0-4.0.0.0" newVersion="4.0.0.0" />
</dependentAssembly>
<dependentAssembly>
  <assemblyIdentity name="System.Web.WebPages" publicKeyToken="31bf3856ad364e35" />
  <bindingRedirect oldVersion="1.0.0.0-2.0.0.0" newVersion="2.0.0.0" />
</dependentAssembly>
<dependentAssembly>
  <assemblyIdentity name="System.Web.Helpers" publicKeyToken="31bf3856ad364e35" />
  <bindingRedirect oldVersion="1.0.0.0-3.0.0.0" newVersion="3.0.0.0" />
</dependentAssembly>
<dependentAssembly>
  <assemblyIdentity name="System.Web.Mvc" publicKeyToken="31bf3856ad364e35" />
  <bindingRedirect oldVersion="1.0.0.0-5.0.0.0" newVersion="5.0.0.0" />
</dependentAssembly>
<dependentAssembly>
  <assemblyIdentity name="System.Web.WebPages" publicKeyToken="31bf3856ad364e35" />
  <bindingRedirect oldVersion="1.0.0.0-3.0.0.0" newVersion="3.0.0.0" />
</dependentAssembly>
 

 

How to fix CLSID {00024500-0000-0000-C000-000000000046} error when using excel component in background code?

We have  a windows Service application which uses Excel Application Object. It used to work on some machines but not all, the error which I am getting is:

Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80070005.

After some struggling I found a solution and it works, solution is:

  • Create directory C:\Windows\SysWOW64\config\systemprofile\Desktop (for 64 bit Windows) or C:\Windows\System32\config\systemprofile\Desktop (for 32 bit Windows) and give full permission
  • Run program “dcomcnfg” Go to “Console Root/Component Services/Computer/My Computer/DCOM Config/” Look up Microsoft Excel and choose properties.
  • Go to Security and Launch and activation permissions->customize->add network, network service, administrator, interactive, system and everyone and give full access
  • Now go to “Identity” and select “The interactive user”.
  • Now go to “Console Root/Component Services/Computer/My Computer”,  right click Properties of “My Computer”, give full access permission to all users.

 

JSON date to Date object – javascript

While working with web services or API with json return, we will get date values in different format, we can’t use this as is where required.

To convert to date object we need to extract integer value and then pass to Date object, below example shows how to get valid date object.

var json = "/Date(-1827639000000)/"
var mytime = new Date(parseInt(json.replace("/Date(", "").replace(")/",""), 10));
alert(mytime);
alert(mytime.toUTCString());

How to Stop Window Error Sound When Typing ‘Enter’ or ‘Esc’

In the KeyDown event, set e.Handled = true and e.SuppressKeyPress = true

OR

protected override bool ProcessCmdKey(ref Message msg, Keys keyData) {
if (keyData == Keys.Escape || keyData == Keys.Enter) {
return true;
}
return base.ProcessCmdKey(ref msg, keyData);
}