Visual Studio Build Action Copy to Output Directory Not Working

net_logo

February 9, 2019 | Posted in .Net

So here’s an interesting one. I have been working on other systems and it has been a while since I’ve worked on the particular system i’m on now. I add a new library, and needed to have an xsl file copy itself to the main application bin folder – there is a dependency on it to render some XML which then displays a simple report style invoice in a standard .Net web browser control.

Anyway, even though I had the file set to:

Build Action: Content

Copy to Output Directory: Copy always

I couldn’t understand why the file wasn’t being copied to the main bin folder where it needed to be. There was a very simple explanation and something I had completely overlooked, so perhaps this may help someone else in the same situation. You just need to make sure the project that hosts the file you want in the main bin folder is reference from the main application library, that’s it.

Leave a comment in the comments section if this helped you.

Read More →

System.Data.DataRelation: These columns don’t currently have unique values.

net_logo

December 10, 2018 | Posted in .Net

So, this is a very short article about creating a relationship between two data tables in a dataset.

In my case, I was returning a dataset, with two data tables and I needed to add a relationship between the tables for the purposes of displaying that data in a nested fashion in a grid control (rough example – not complete code):

DataSet dataSource;

………..

DataRelation newRelation = new DataRelation(
“RelationshipName”,
headers.Columns["ParentID"],
lines.Columns["ChildID"]);

dataSource.Relations.Add(newRelation);

At the point of adding the DataRelation to the dataset, an error would bubble up as per the title of this blog post, “These columns don’t currently have unique values.”. Having gone through the data returned from the stored procedure I did in fact find ParentID in there twice which caused this error, however I found that I could overcome the error by passing in false on the next DataRelation param called bool createConstraints which may be something you want to do:

DataRelation newRelation = new DataRelation(
“Line”,
headers.Columns["RecId"],
lines.Columns["RecID"],
false);

In my case I didn’t end up doing this as I wanted to see the error and find the data issues (it might be an good idea to add a unique constraint in your database if this does not follow the required database design and duplicates should not exist). A join in the stored procedure was my problem, returning the same record twice.

“Notice that when the DataRelation is created for the Customers and Orders tables, no value is specified for the createConstraints flag (the default is true). This assumes that all the rows in the Orders table have a CustomerID value that exists in the parent Customers table. If a CustomerID exists in the Orders table that does not exist in the Customers table, a ForeignKeyConstraint causes an exception to be thrown.
When the child column might contain values that the parent column does not contain, set the createConstraints flag to false when adding the DataRelation. In the example, the createConstraints flag is set to false for the DataRelation between the Orders table and the OrderDetails table. This enables the application to return all the records from the OrderDetails table and only a subset of records from the Orders table without generating a run-time exception.”

Source: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/dataset-datatable-dataview/navigating-datarelations

 

Read More →

Calling Stored Procedures Via Entity Framework

net_logo

August 30, 2018 | Posted in .Net

If you want to call stored procedures via EF, there is a way, which allows you to keep the entities aspect, and in particular, allows for the transposing of table rows to your own custom DTO’s, which will end up being your entites. Or, if you want a simple conversion from a list of entities to something like a dataset that is also possible. In simplistic terms you need to:

1) Create a new model (edmx) from your database, and when it asks which database schema you want to inject i.e. tables, stored procedures etc, choose none (it will ask you if you are sure, say yes)

2) Copy the new connection string in to your main application configuration file otherwise an exception will get raised when opening the connection

3) Create a DAL and to call your stored procedure

Here is an example:

public class SystemWarningsDTO
{
public string WarningType { get; set; }

public string Supplier { get; set; }

public int Count { get; set; }

public string SuggestedFix { get; set; }
}

public sealed class Adhoc : IDisposable
{
#region class declarations

private bool _disposed = false;

private AdhocQueriesEntities _objectContext = null;

#endregion

#region “constructors”

public Adhoc()
{
this._objectContext = new AdhocQueriesEntities();
}

#endregion

#region “destructor”

~Adhoc()
{
this.Dispose(false);
}

#region IDisposable

public void Dispose()
{
this.Dispose(true);
GC.SuppressFinalize(this);
}

#endregion

private void Dispose(bool disposing)
{
if (!this._disposed)
{
if (disposing)
{
//// TODO: dispose managed resources
}

//// TODO: Call the appropriate methods to clean up unmanaged resources here

this._disposed = true;
}
}

#endregion

#region “properties”
#endregion

#region “methods”
#endregion

#region “functions”

private static DataTable ConvertToDatatable<T>(IList<T> data)
{
PropertyDescriptorCollection props = TypeDescriptor.GetProperties(typeof(T));
DataTable table = new DataTable();
for (int i = 0; i < props.Count; i++)
{
PropertyDescriptor prop = props[i];
if (prop.PropertyType.IsGenericType && prop.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
table.Columns.Add(prop.Name, prop.PropertyType.GetGenericArguments()[0]);
else
table.Columns.Add(prop.Name, prop.PropertyType);
}

object[] values = new object[props.Count];
foreach (T item in data)
{
for (int i = 0; i < values.Length; i++)
{
values[i] = props[i].GetValue(item);
}
table.Rows.Add(values);
}
return table;
}

public DataSet DoSomething()
{
DataSet dataSource = new DataSet();

try
{
var cmd = this._objectContext.Database.Connection.CreateCommand();
cmd.CommandText = “dbo.usp_MySproc”;
cmd.CommandType = CommandType.StoredProcedure;

if (this._objectContext.Database.Connection.State != ConnectionState.Open)
this._objectContext.Database.Connection.Open();

// get first result set of invoice headers
DbDataReader reader = cmd.ExecuteReader();

IList<SystemWarningsDTO> systemWarnings =
((IObjectContextAdapter)this._objectContext).ObjectContext.Translate<SystemWarningsDTO>(reader).ToList();

DataTable warnings = ConvertToDatatable(systemWarnings);

dataSource.Tables.Add(warnings);
}
catch (Exception)
{
throw;
}

return dataSource;
}

#endregion

#region “base overrides”

#region “properties”
#endregion
#region “methods”
#endregion
#region “functions”
#endregion

#endregion

#region “events”
#endregion
}

 

That accounts for the get side of things, but what if you want to update via a stored procedure? Well that is also a straight forward operation, below is an example function that would achieve this:

public bool DoSomething(string invoiceLineIds,
string creditOrderNumber,
string creditNotes,
string resolvedBy,
DateTime resolvedOn)
{
bool returnVal = false;

try
{
var cmd = this._objectContext.Database.Connection.CreateCommand();
cmd.CommandText = “dbo.usp_MySproc”;
cmd.CommandType = CommandType.StoredProcedure;

DbParameter invoiceLineIdsSqlParam = cmd.CreateParameter();
invoiceLineIdsSqlParam.ParameterName = “@invoiceLineIds”;
invoiceLineIdsSqlParam.Value = invoiceLineIds;
cmd.Parameters.Add(invoiceLineIdsSqlParam);

DbParameter creditOrderNumberSqlParam = cmd.CreateParameter();
creditOrderNumberSqlParam.ParameterName = “@creditOrderNumber”;
creditOrderNumberSqlParam.Value = creditOrderNumber;
cmd.Parameters.Add(creditOrderNumberSqlParam);

DbParameter creditNotesSqlParam = cmd.CreateParameter();
creditNotesSqlParam.ParameterName = “@creditNotes”;
creditNotesSqlParam.Value = creditNotes;
cmd.Parameters.Add(creditNotesSqlParam);

DbParameter resolvedBySqlParam = cmd.CreateParameter();
resolvedBySqlParam.ParameterName = “@resolvedBy”;
resolvedBySqlParam.Value = resolvedBy;
cmd.Parameters.Add(resolvedBySqlParam);

DbParameter resolvedOnSqlParam = cmd.CreateParameter();
resolvedOnSqlParam.ParameterName = “@resolvedOn”;
resolvedOnSqlParam.Value = resolvedOn;
cmd.Parameters.Add(resolvedOnSqlParam);

DbParameter returnSqlParam = cmd.CreateParameter();
returnSqlParam.ParameterName = “@result_value”;
returnSqlParam.DbType = DbType.Int32;
returnSqlParam.Direction = ParameterDirection.Output;
cmd.Parameters.Add(returnSqlParam);

if (this._objectContext.Database.Connection.State != ConnectionState.Open)
this._objectContext.Database.Connection.Open();

// get first result set of invoice headers
cmd.ExecuteNonQuery();

int result = (int)cmd.Parameters["@result_value"].Value;

if (result > 0)
returnVal = true;
}
catch (Exception)
{
throw;
}

return returnVal;
}

 

And finally, what if there are multiple rowsets being returned from our stored procedure, they have a parent/child relationship and you want to display them in some type of grid control? Well that is also straightforward enough, here is an example of how to achieve that by trasposing, in this example, invoice headers and invoice lines:

public DataSet DoSomething(string supplierCode,
string poNumber,
string invoiceNumber,
DateTime? invoicesFrom,
DateTime? invoicesTo,
string fileName,
bool includeDiscrepancyResolved,
bool includeNoDiscrepancy)
{
DataSet dataSource = new DataSet();

try
{
var cmd = this._objectContext.Database.Connection.CreateCommand();
cmd.CommandText = “dbo.usp_MySproc”;
cmd.CommandType = CommandType.StoredProcedure;

DbParameter supplierCodeSqlParam = cmd.CreateParameter();
supplierCodeSqlParam.ParameterName = “@supplierCode”;
supplierCodeSqlParam.Value = string.IsNullOrWhiteSpace(supplierCode) ? (object)DBNull.Value : supplierCode;
supplierCodeSqlParam.IsNullable = true;
cmd.Parameters.Add(supplierCodeSqlParam);

DbParameter poNumberSqlParam = cmd.CreateParameter();
poNumberSqlParam.ParameterName = “@poNumber”;
poNumberSqlParam.Value = string.IsNullOrWhiteSpace(poNumber) ? (object)DBNull.Value : poNumber;
poNumberSqlParam.IsNullable = true;
cmd.Parameters.Add(poNumberSqlParam);

DbParameter invoiceNumberSqlParam = cmd.CreateParameter();
invoiceNumberSqlParam.ParameterName = “@invoiceNumber”;
invoiceNumberSqlParam.Value = string.IsNullOrWhiteSpace(invoiceNumber) ? (object)DBNull.Value : invoiceNumber;
invoiceNumberSqlParam.IsNullable = true;
cmd.Parameters.Add(invoiceNumberSqlParam);

DbParameter invoiceDateFromSqlParam = cmd.CreateParameter();
invoiceDateFromSqlParam.ParameterName = “@invoiceDateFrom”;
invoiceDateFromSqlParam.Value = invoicesFrom ?? (object)DBNull.Value;
invoiceDateFromSqlParam.IsNullable = true;
cmd.Parameters.Add(invoiceDateFromSqlParam);

DbParameter invoiceDateToSqlParam = cmd.CreateParameter();
invoiceDateToSqlParam.ParameterName = “@invoiceDateTo”;
invoiceDateToSqlParam.Value = invoicesTo ?? (object)DBNull.Value;
invoiceDateToSqlParam.IsNullable = true;
cmd.Parameters.Add(invoiceDateToSqlParam);

DbParameter fileNameSqlParam = cmd.CreateParameter();
fileNameSqlParam.ParameterName = “@fileName”;
fileNameSqlParam.Value = string.IsNullOrWhiteSpace(fileName) ? (object)DBNull.Value : fileName;
fileNameSqlParam.IsNullable = true;
cmd.Parameters.Add(fileNameSqlParam);

DbParameter includeDiscrepancyResolvedSqlParam = cmd.CreateParameter();
includeDiscrepancyResolvedSqlParam.ParameterName = “@includeDiscrepancyResolved”;
includeDiscrepancyResolvedSqlParam.Value = includeDiscrepancyResolved;
includeDiscrepancyResolvedSqlParam.IsNullable = true;
cmd.Parameters.Add(includeDiscrepancyResolvedSqlParam);

DbParameter includeNoDiscrepancySqlParam = cmd.CreateParameter();
includeNoDiscrepancySqlParam.ParameterName = “@includeNoDiscrepancy”;
includeNoDiscrepancySqlParam.Value = includeNoDiscrepancy;
includeNoDiscrepancySqlParam.IsNullable = true;
cmd.Parameters.Add(includeNoDiscrepancySqlParam);

if (this._objectContext.Database.Connection.State != ConnectionState.Open)
this._objectContext.Database.Connection.Open();

// get first result set of invoice headers
DbDataReader reader = cmd.ExecuteReader();

IList<InvoiceDiscrepancyHeaderDTO> invoiceHeaders =
((IObjectContextAdapter)this._objectContext).ObjectContext.Translate<InvoiceDiscrepancyHeaderDTO>(reader).ToList();

// move to invoice lines
reader.NextResult();

IList<InvoiceDiscrepancyLineDTO> invoiceLines =
((IObjectContextAdapter)this._objectContext).ObjectContext.Translate<InvoiceDiscrepancyLineDTO>(reader).ToList();

DataTable headers = ConvertToDatatable(invoiceHeaders);
DataTable lines = ConvertToDatatable(invoiceLines);

dataSource.Tables.Add(headers);
dataSource.Tables.Add(lines);

DataRelation newRelation = new DataRelation(“PK_RecID”, headers.Columns["RecID"], lines.Columns["RecID"]);
dataSource.Relations.Add(newRelation);
}
catch (Exception)
{
throw;
}

return dataSource;
}

 

Read More →

How To Quickly Test A WCF Service

net_logo

January 15, 2018 | Posted in .Net

A nice way to be able to test a WCF service without the need to spend time creating your own test harness if you want something quick and dirty is to use the Microsoft “WcfTestClient.exe” utility which can be found in a path similar to C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\WcfTestClient.exe. All you have to do is copy your service url and paste it to the utility where you can double click on whichever method you’d like to invoke, drop down he message type and complete the requested info and there you have it, a quick a easy way to test a WCF service.

On the left you sill see the available methods, to the right of that you will see the request and below the request you will see the response from the service.

Read More →

SQL Server Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.

sql server 2014

October 28, 2016 | Posted in SQL Server

For me, I received this error having got up to 1110 case when statements i.e.

case

when Something = ’1′ then ’2′

else null end as Test

So I assume this is the upper limit allowed by SQL Server.

Read More →

Incorrect Syntax Near ‘BEGIN’. Expecting EXTERNAL.

sql server 2014

October 3, 2016 | Posted in SQL Server

Example of where this might happen:

IF NOT EXISTS (SELECT NULL FROM sys.objects [objects] JOIN sys.schemas [schemas] ON [objects].schema_id = [schemas].schema_id WHERE [schemas].[Name] = ‘dbo’  AND [objects].[Name] like ‘%usp_MySproc%’)
BEGIN

CREATE PROCEDURE [dbo].[usp_MySproc]
(
@SiteID INT,
@JobEntryID INT,
@LastQueried datetime,
@ResourceId INT
)
AS
BEGIN

SELECT 1;
END
END
GO

 

Here of how to fix it:

IF NOT EXISTS (SELECT NULL FROM sys.objects [objects] JOIN sys.schemas [schemas] ON [objects].schema_id = [schemas].schema_id WHERE [schemas].[Name] = ‘dbo’  AND [objects].[Name] like ‘%usp_MySproc%’)
BEGIN
EXEC(‘CREATE PROCEDURE [dbo].[usp_MySproc]
(
@SiteID INT,
@JobEntryID INT,
@LastQueried datetime,
@ResourceId INT
)
AS
BEGIN

SELECT 1;
END’)
END
GO

Read More →

How to tell which SQL Server version you are using in SSMS

September 2, 2016 | Posted in SQL Server

DECLARE @ver nvarchar(128)
SET @ver = CAST(serverproperty(‘ProductVersion’) AS nvarchar)
SET @ver = SUBSTRING(@ver, 1, CHARINDEX(‘.’, @ver) – 1)

IF ( @ver = ’7′ )
SELECT ‘SQL Server 7′
ELSE IF ( @ver = ’8′ )
SELECT ‘SQL Server 2000′
ELSE IF ( @ver = ’9′ )
SELECT ‘SQL Server 2005′
ELSE IF ( @ver = ’10′ )
SELECT ‘SQL Server 2008/2008 R2′
ELSE IF ( @ver = ’11′ )
SELECT ‘SQL Server 2012′
ELSE IF ( @ver = ’12′ )
SELECT ‘SQL Server 2014′
ELSE
SELECT ‘Unsupported SQL Server Version’

Read More →

Visual Studio Schema Compare SCMP Include Exclude Slow

Visual Studio 2013

February 8, 2016 | Posted in Visual Studio 2013

If like me you’ve found that the new schema compare files are annoyingly slow, if you decide that you want to exclude all items for a given action, you can do so at the folder level, not at the item level. In other words, right click say the Deleted folder and select Exclude to quickly exclude all items. Then all you need to do is include the items you need to produce the script for.

Read More →

The value of EntityCommand.CommandText is not valid for a StoredProcedure command. The EntityCommand.CommandText value must be of the form ‘ContainerName.FunctionImportName’.

net_logo

January 7, 2016 | Posted in .Net

If you are seeing this exception when trying to call a stored procedure through Entityframework version 5 or greater, there is a known issue with the model tt files.

Open the tt file for your model and search for public string ExecuteFunction(EdmFunction edmFunction, string modelNamespace, bool includeMergeOption) and change edmFunction.Name to edmFunction.FullName.

This should fix the problem for you.

Read More →

Windows Sysinternals Disk Usage Batch File

command_prompt

October 23, 2015 | Posted in Commant Prompt

Batch file single:

@echo off
CD “C:\”

For /f “tokens=2-4 delims=/ ” %%a in (‘date /t’) do (set mydate=%%c-%%a-%%b)
For /f “tokens=1-3 delims=/ ” %%a in (‘date /t’) do (set mydate=%%c-%%b-%%a)
For /f “tokens=1-2 delims=/:” %%a in (‘time /t’) do (set mytime=%%a%%b)
echo Started scan at %mydate%_%mytime%

c:\du.exe -c “\\servername\directory”

For /f “tokens=2-4 delims=/ ” %%a in (‘date /t’) do (set mydate=%%c-%%a-%%b)
For /f “tokens=1-3 delims=/ ” %%a in (‘date /t’) do (set mydate=%%c-%%b-%%a)
For /f “tokens=1-2 delims=/:” %%a in (‘time /t’) do (set mytime=%%a%%b)
echo Finished scan at %mydate%_%mytime%

pause

 

Batch file multi:

@echo off
CD “C:\”

For /f “tokens=2-4 delims=/ ” %%a in (‘date /t’) do (set mydate=%%c-%%a-%%b)
For /f “tokens=1-3 delims=/ ” %%a in (‘date /t’) do (set mydate=%%c-%%b-%%a)
For /f “tokens=1-2 delims=/:” %%a in (‘time /t’) do (set mytime=%%a%%b)
echo Started scan at %mydate%_%mytime%

for %%i in (
“\\servername\directory”
“\\servername\directory”
) do C:\du.exe -c %%i

For /f “tokens=2-4 delims=/ ” %%a in (‘date /t’) do (set mydate=%%c-%%a-%%b)
For /f “tokens=1-3 delims=/ ” %%a in (‘date /t’) do (set mydate=%%c-%%b-%%a)
For /f “tokens=1-2 delims=/:” %%a in (‘time /t’) do (set mytime=%%a%%b)
echo Finished scan at %mydate%_%mytime%

pause

 

For more information on du.exe see https://technet.microsoft.com/en-gb/sysinternals/bb896651.aspx

Read More →

Visual Studio 2013 & Merging Changesets Between Code Streams

Visual Studio 2013

August 25, 2015 | Posted in Visual Studio 2013

On the whole merging changesets between code steams goes without any hitches. On occasion you’ll find issues merging, I find in particular designer files, because they are auto generated in the IDE. If you are finding it difficult to merge and you face the same problem, you can merge the changeset across, take the target version (the key bit here) and then manually implement the changes again in the target branch.

It’s by no means ideal, and if you have found a better way of doing this, please share!

For more information on coding merging see https://msdn.microsoft.com/en-us/library/ms181428%28v=vs.120%29.aspx

Read More →

MSXML2.XMLHTTP30Class and MSXML2.DOMDocument30Class Deprecated

net_logo

August 5, 2015 | Posted in .Net

We have a .Net Windows Forms application that uses RoboHelp to serve up help files. However, since moving our Team Foundation Server to TFS 2013 on Windows Server 2012 R2 compilation issues arose with errors suggesting that XMLHTTP30Class couldn’t be found in the MSXML2 namespace. The same story was true for DOMDocument30Class.

To resolve this simply use XMLHTTP60Class and DOMDocument60Class instead, in RoboHelp_CSH.cs which is supplied by Adobe for .Net incorporation.

I could find no documentation on this either on MSDN or on the Adobe website so hopefully this will help someone else in the same situation.

It’s important to point out that we had the latest version of MSXML Core Service on the server and infact on my machine (the error wasn’t present on my Windows 7 x64 machine however it was present on the new Windows Server 2012 R2 / TFS 2013 machine). The version used was MSXML 6 SP3.

Read More →

Using Extension Methods And A Custom Attribute To Extend System.Windows.Form.Control

net_logo

June 18, 2015 | Posted in .Net

I needed a way to mark any control in the system so that I could check if I needed to perform some action on it. Ideally it needed to be on the base class System.Windows.Forms.Control so that this could be done on anything that inherits from it.

I used two separate extension methods to add and check for the custom attribute using the TypeDescriptor class, and then:

To add the attribute to the control with the ignoreAction value:

YourControl.IgnoreAction(True)

To check for the attribute on the control elsewhere in the system:

if (Not YourControl.IgnoreAction) or (!YourControl.IgnoreAction) if using c#

The extension methods:

Public Module Extensions
Private ReadOnly attr As New IgnoreActionAttribute(True)

<System.Runtime.CompilerServices.Extension()>
Public Sub IgnoreAction(ByVal value As System.Windows.Forms.Control, ignore As Boolean)

Try
Dim params() As Attribute = {New IgnoreActionAttribute(ignore)}

System.ComponentModel.TypeDescriptor.AddAttributes(value, params)

Catch ex As Exception
‘Handle Error
End Try

End Sub

<System.Runtime.CompilerServices.Extension()>
Public Function IgnoreAction(ByVal value As System.Windows.Forms.Control) As Boolean

Dim ignore As Boolean = False

Dim attribs As System.ComponentModel.AttributeCollection = Nothing

Try
attribs = System.ComponentModel.TypeDescriptor.GetAttributes(value)

If attribs.Contains(attr) Then
ignore = True
End If

Catch ex As Exception
‘Handle Error
End Try

Return ignore

End Function

End Module

The custom attribute:

Public Class IgnoreActionAttribute
Inherits Attribute

”’ <summary>
”’ The ignore action value.
”’ </summary>
”’ <remarks></remarks>
Private _ignoreAction As Boolean

”’ <summary>
”’ Main constructor for the ignore action attribute.
”’ </summary>
”’ <param name=”ignoreAction”></param>
”’ <remarks></remarks>
Public Sub New(ignoreAction As Boolean)
Me._ignoreAction = ignoreAction
End Sub

”’ <summary>
”’ The ignore action value.
”’ </summary>
”’ <returns>True if the action should be ignored.</returns>
”’ <remarks></remarks>
Public Property IgnoreAction() As Boolean
Get
Return Me._ignoreAction
End Get
Set(value As Boolean)
Me._ignoreAction = value
End Set
End Property

End Class

I’ve seen other implementations which are far more convoluted than this. Here you can quickly and easily add your attribute to a control, or you could extend it to add any custom attribute to any object you like and then use the TypeDescriptor to check for it.

Read More →

How To Script Data From A SQL Server Table

sql server 2014

June 17, 2015 | Posted in SQL Server

I wanted a way to script off the data in a sql table in some automated fashion rather than having to do it manually and it turns out there is a way to do this in SSMS:

- Right-click on the DB, select Generate Scripts
- Click <Next> through the Introduction screen
- Select Specific database objects then your table, then <Next>
- Click <Advanced> then for “Types of Data to script” select “Schema and Data” then <OK>
- Then save to file\clipboard or new window

It will script off even things like identities so you’ll need to change that if it’s not something you require.

Read More →

Visual Studio 2012 Post Build Event – Run Batch – Validate Entity Framework Model ProviderManifestToken

Visual Studio 2013

June 3, 2015 | Posted in Visual Studio 2012, Windows

When entity framework models are refreshed from a SQL database, the ProviderManifestToken will change based on the version of SQL Server.

It was necessary to automate a process whereby every time the DAL built, I needed to verify that a refresh hadn’t inadvertently changed the ProviderManifestToken to an incorrect version.

To do this, I created a batch file (all edmx files must conform to SQL Server version 2005, and if they don’t, notify):

 

 

@echo off
@break off
@title Create folder with batch but only if it doesn't already exist - PROVIDER MANIFEST SEARCH
@color 0a
@cls

setlocal EnableDelayedExpansion

set shouldpause=0

if exist "C:\ProviderManifestTokens\" (
  rmdir "C:\ProviderManifestTokens\" /s /q

)

if not exist "C:\ProviderManifestTokens\" (
  mkdir "C:\ProviderManifestTokens\"  
)

set SOURCE_DIR=%cd%
set DEST_DIR=C:\ProviderManifestTokens
set FILENAMES_TO_COPY=*.edmx

for /R "%SOURCE_DIR%" %%F IN (%FILENAMES_TO_COPY%) do (
    if exist "%%F" (
    	set FILE_DIR=%%~dpF
    	set FILE_INTERMEDIATE_DIR=!FILE_DIR:%SOURCE_DIR%=!
    	xcopy /I /Y "%%F" "%DEST_DIR%"
    )
)

if exist "C:\ProviderManifestTokens\" (

  chdir /d C:\ProviderManifestTokens\

  ren *.edmx *.txt

  for %%F in (.\*) do (
  find "ProviderManifestToken=""2005" %%F > nul
        if errorlevel 1 (		

		echo ----------------------------------------------------------
		echo Warning: File %%F contains incorrect ProviderManifestToken
		echo ----------------------------------------------------------
		set shouldpause=1
    )
  )
)

if not %shouldpause% == 1 (
	echo ----------------------------------------------------------
	echo Success: *.edmx files valid 
	echo ----------------------------------------------------------
)

exit

 

 

 

 

The script essentially scans directories and sub directories for all edmx files, it then copies them to a folder on the root, changes file extensions from *.edmx to *.txt (so that I could perform string lookups) and then notify where the ProviderManifestToken wasn’t what I wanted it to be.

The only other bit is to add the following in to your project Build Events:

cd $(ProjectDir)
call CheckProviderManifestTokens.bat

The above is a Post Build event and is set to run Always.

Check your Output window for the results of the batch file.

Update: I ended up leaving the batch in the DAL where it was, and putting the post build event in to the main application project, as that project is the last to compile, therefore developers will see the results at the end in the output window.

The post build event changed everso slightly:

cd $(ProjectDir)\DalFolder
call CheckProviderManifestTokens.bat

Read More →

How To Create Class Structure With Properties From SQL Table In VB.Net Or C#.Net

sql_server_logo

April 27, 2015 | Posted in SQL Server

I wanted to overcome the repetitive task of creating a new class and associated properties every time a new table is introducted in to the system, so I’ve written a stored procedure to do exactly that:

create procedure [dbo].[CreateClassStructureFromSQLTable]
(
@VB bit,
@TableName varchar(255),
@PluraliseClassName bit
)
as

/*
exec [dbo].[CreateClassStructureFromSQLTable] 1, ‘tblSomeTable’, 1
*/

declare @prop varchar(max)
declare @field varchar(255)
declare @field_lower varchar(255)
declare @type varchar (255)

declare @class_prefix varchar(10)
set @class_prefix = ‘X’

if @VB = 0
begin

PRINT ‘public class ‘ + replace(@TableName,’tbl’,@class_prefix) + case when @PluraliseClassName = 1 then ‘s’ else ” end + ‘ : IDisposable’
PRINT ‘{‘

declare cprops cursor for
select distinct ‘public ‘ + ColumnType + NullableSign + ‘ ‘ + ColumnName AS prop
from
(
select
replace(col.name, ‘ ‘, ‘_’) ColumnName,
column_id ColumnId,
case typ.name
when ‘bigint’ then ‘long’
when ‘binary’ then ‘byte[]‘
when ‘bit’ then ‘bool’
when ‘char’ then ‘string’
when ‘date’ then ‘System.DateTime’
when ‘datetime’ then ‘System.DateTime’
when ‘datetime2′ then ‘System.DateTime’
when ‘datetimeoffset’ then ‘DateTimeOffset’
when ‘decimal’ then ‘decimal’
when ‘float’ then ‘float’
when ‘image’ then ‘byte[]‘
when ‘int’ then ‘int’
when ‘money’ then ‘decimal’
when ‘nchar’ then ‘char’
when ‘ntext’ then ‘string’
when ‘numeric’ then ‘decimal’
when ‘nvarchar’ then ‘string’
when ‘real’ then ‘double’
when ‘smalldatetime’ then ‘System.DateTime’
when ‘smallint’ then ‘short’
when ‘smallmoney’ then ‘decimal’
when ‘text’ then ‘string’
when ‘time’ then ‘TimeSpan’
when ‘timestamp’ then ‘System.DateTime’
when ‘tinyint’ then ‘byte’
when ‘uniqueidentifier’ then ‘System.Guid’
when ‘varbinary’ then ‘byte[]‘
when ‘varchar’ then ‘string’
else ‘UNKNOWN_’ + typ.name
end ColumnType,
case
when col.is_nullable = 1 and typ.name in (‘bigint’, ‘bit’, ‘date’, ‘datetime’, ‘datetime2′, ‘datetimeoffset’, ‘decimal’, ‘float’, ‘int’, ‘money’, ‘numeric’, ‘real’, ‘smalldatetime’, ‘smallint’, ‘smallmoney’, ‘time’, ‘tinyint’, ‘uniqueidentifier’)
then ‘?’
else ”
end NullableSign
from sys.columns col
join sys.types typ on
col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
where object_id = object_id(@TableName)
) t
order by prop

open cprops
FETCH NEXT FROM cprops INTO @prop
WHILE @@FETCH_STATUS = 0
BEGIN

set @field = replace(substring(replace(@prop, ‘public ‘, ”), charindex(‘ ‘,replace(@prop, ‘public ‘, ”)),100),’ ‘,”)
set @field_lower = lower(left(@field,1)) + right(@field, len(@field)-1)
set @type =  left(replace(@prop, ‘public ‘, ”), charindex(‘ ‘, replace(@prop, ‘public ‘, ”)))

–select @field, @field_lower, @type

print ‘    private ‘ + @type + ‘_’ + @field_lower + ‘;’
print ‘    ‘+@prop
print ‘    {‘
print ‘        get’
print ‘        {‘
print ‘            return this._’ + @field_lower + ‘;’
print ‘        }’
print ‘        set’
print ‘        {‘
print ‘            this._’ + @field_lower + ‘ = value;’
print ‘        }’
print ‘    }’
print ”

FETCH NEXT FROM cprops INTO @prop
END
close cprops
DEALLOCATE cprops
PRINT ‘}’

end
else
begin

PRINT ‘Public Class ‘ + replace(@TableName,’tbl’,@class_prefix) + case when @PluraliseClassName = 1 then ‘s’ else ” end
PRINT ‘    Implements IDisposable’
PRINT ”
declare props cursor for
select distinct ‘Public Property ‘ + ColumnName + ‘ as ‘ + ColumnType AS prop
from (
select
replace(col.name, ‘ ‘, ‘_’) ColumnName,  column_id,
case typ.name
when ‘bigint’ then ‘Long’
when ‘binary’ then ‘Byte[]‘
when ‘bit’ then ‘Boolean’
when ‘char’ then ‘String’
when ‘date’ then ‘DateTime’
when ‘datetime’ then ‘DateTime’
when ‘datetime2′ then ‘DateTime’
when ‘datetimeoffset’ then ‘DateTimeOffset’
when ‘decimal’ then ‘Decimal’
when ‘float’ then ‘Float’
when ‘image’ then ‘Byte[]‘
when ‘int’ then ‘Integer’
when ‘money’ then ‘Decimal’
when ‘nchar’ then ‘Char’
when ‘ntext’ then ‘String’
when ‘numeric’ then ‘Decimal’
when ‘nvarchar’ then ‘String’
when ‘real’ then ‘Double’
when ‘smalldatetime’ then ‘DateTime’
when ‘smallint’ then ‘Short’
when ‘smallmoney’ then ‘Decimal’
when ‘text’ then ‘String’
when ‘time’ then ‘TimeSpan’
when ‘timestamp’ then ‘DateTime’
when ‘tinyint’ then ‘Byte’
when ‘uniqueidentifier’ then ‘Guid’
when ‘varbinary’ then ‘Byte[]‘
when ‘varchar’ then ‘String’
end ColumnType
from sys.columns col join sys.types typ on col.system_type_id = typ.system_type_id
where object_id = object_id(@TableName)
) t
order by prop

open props
FETCH NEXT FROM props INTO @prop
WHILE @@FETCH_STATUS = 0
BEGIN

set @field = replace(left(replace(@prop, ‘Public Property ‘, ”), charindex(‘ ‘, replace(@prop, ‘Public Property ‘, ”))),’ ‘,”)
set @field_lower = lower(left(@field,1)) + right(@field, len(@field)-1)
set @type =  replace(replace(@prop, ‘Public Property ‘, ”), @field + ‘ as ‘,”)

print ‘    Private _’ + lower(left(replace(@prop, ‘Public Property ‘, ”),1)) + substring(replace(@prop, ‘Public Property ‘, ”), 2,100)
print ‘    ‘ +@prop
print ‘    Get’
print ‘        Return Me._’ + @field_lower
print ‘    End Get’
print ‘    Set(value As ‘ + @type + ‘)’
print ‘        Me._’ + @field_lower + ‘ = value’
print ‘    End Set’
print ‘    End Property’
print ”
FETCH NEXT FROM props INTO @prop
END
close props
DEALLOCATE props
PRINT ‘End Class’

end

Read More →

Disable Specific Triggers Based On SYS.SQL_MODULES DEFINITION

sql_server_logo

April 7, 2015 | Posted in SQL Server, Uncategorized

declare @temp table
(table_name varchar(100), trigger_name varchar(100))
insert into @temp
select    object_name(parent_id), name
from    sys.triggers
where    name in
(
select object_name(object_id) from sys.sql_modules where definition like ‘%SEARCH%STRING%’
)
and is_disabled = 0

declare @sql varchar(255)

declare db_cursor cursor for
select ‘ALTER TABLE ‘ + table_name + ‘ DISABLE TRIGGER [' + trigger_name + ']‘ from @temp x

open db_cursor
fetch next from db_cursor into @sql

while @@FETCH_STATUS = 0
begin

exec (@sql);

fetch next from db_cursor into @sql
end

close db_cursor
deallocate db_cursor

Read More →

toolstripcombobox.count = 0

net_logo

March 27, 2015 | Posted in .Net

Having used a toolstripcombobox hosted in a Toolstrip, although having bound a list of objects to the combo using a new bindingsource the items wouldn’t show until the form had displayed and validated. To fix this (if some actuib such as validation is to be performed before the form loads) set the toolstripcombobox.combobox.bindingcontext to the forms bindingcontext which will force the binding to happen.

toolstripcombobox.combobox.bindingcontext = this.bindingcontext;

For more information see https://msdn.microsoft.com/en-us/library/system.windows.forms.control.bindingcontext%28v=vs.100%29.aspx

Read More →

Changing Macintosh OSX Key Mapping

apple_logo

February 28, 2015 | Posted in Mac

I’ve been putting up with the home and end buttons not jumping to the start and end of lines for a while now, and thought there must be a way to map those keys to what I want them to do. I managed to find a ncie little app called Karabiner which supports Yosemite, the link is below.

Once you give the app permissions in Accessibility, you can go ahead and remap any key you like. For me, the options were under “For PC Users” and then tick option “Use PC Style Home/End”. Finally, it works as I want it to!

https://pqrs.org/osx/karabiner/

Read More →

SQL Server Table Sizing

sql_server_logo

November 20, 2014 | Posted in SQL Server

SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
(SUM(a.total_pages) * 8) / 1024 AS TotalSpaceMB,
(SUM(a.used_pages) * 8) / 1024 AS UsedSpaceMB,
((SUM(a.total_pages) – SUM(a.used_pages)) * 8) / 1024 AS UnusedSpaceMB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE ‘dt%’
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
5 desc

Read More →

How to get the ip address of SQL Server using SSMS

sql_server_logo

October 2, 2014 | Posted in Uncategorized

select connectionproperty(‘local_net_address’)

Read More →

Installing and Uninstalling Packages using NuGet Package Manager Console

Visual Studio 2013

July 15, 2014 | Posted in Visual Studio 2012

In Visual Studio under Tools, if you have NuGet installed you should see an option “NuGet Package Manager… Console”.

Here you can install and uninstall packages in the console by specifying the  -package, the -version and the -project and it takes care of the installation and references for you:

 

Install-Package EntityFramework -Version 5.0.0 -Project MyProject

Uninstsall-Package EntityFramework -Version 5.0.0 -Project MyProject

Read More →

Can’t DragDrop Items to Custom Windows Explorer or Windows Control in Visual Studio 2012

net_logo

July 15, 2014 | Posted in .Net

Are you running Visual Studio as “Administrator”? This could very well be your problem.

On windows Vista and later, windows explorer runs with medium integrity. If you run Visual Studio as “Administrator” it runs with high integrity which means the OS will disable DragDrop operatins based on a WIM, literally (Windows Integration Mechanism).

For some light reading, head over to the following URL..

http://msdn.microsoft.com/en-us/library/bb625957.aspx

“The Windows integrity mechanism is a core component of the Windows security architecture that restricts the access permissions of applications that are running under the same user account and that are less trustworthy..”

Read More →

Setting Up Windows Server AppFabric 1.1

iis7

June 25, 2014 | Posted in IIS

We have recently developed a RESTful .NET 4 service which was deployed to a server running Windows Server AppFabric. We wanted to take advantage of all the Windows Server AppFabric goodness and its WCF monitoring capabilities.

Deployment could not have been easier: after creating the zip file and release instructions, our admin deployed the service using the MSDeploy add-on to IIS to import the zip file. The only thing to do next was test it. When we called our RESTful service, it worked as expected. But the ‘WCF Call History” counts AppFabric Dashboard were not increasing. All the counts were zero.

After some time getting our heads around it, we fixed the issue. Before I share what the resolution was, which was pretty simple actually, here are some of the steps you might want to take when troubleshooting Windows Server AppFabric. Start by checking that Application monitoring is enabled and set to write the Event Trace for Windows (ETW) events to the Server AppFabric  monitoring database. To do this right click your service, select “Manage WCF and WF Services” and select “Configure…”:

image

Then select the “Monitoring” tab and check that “Write events to database” is checked, that the connection string is configured, and that the “Level” is something other than “Off” and “Errors Only”:

image

After that, check that the connection string selected in the previous step is correct. To do this select your service and double click “Connection Strings” on the “Features View” pane in IIS, then double click the connection string:

image

Note how integrated security is being used. One important thing to notice here is that the identity of the application pool used by the WCF service has nothing to do with the Windows Server AppFabric monitoring. It will use either the identity of the user configured on the AppFabric Event Collection Service – when the connection string is set to integrated security – or the SQL user defined in the connection string when using SQL authentication. In our case as you can see above we were using integrated security, so the user setup in the service should be the one connecting to SQL Server:

image[13]

It all seems normal, the AS_Administrator has write access to the monitoring database, so it’s time to kick it up a notch: as Ron Jacobs has mentioned on Enpoint.TV , .NET 4.0 takes advantage of Event Trace for Windows (ETW) to create traces of WCF services and workflows. These can be seen in the “Application Server-Applications” part of the Windows event log. There is another part of the event log that gets created when Windows Server AppFabric is installed: “Application Server-System Services”. These are the logs used by the two AppFabric windows services. So we enabled the Admin log in this folder to see why the event collector service did not seem to be writing the events to the monitoring database:

image[15]

After a few seconds we started seeing the errors show up on the log, being thrown by the two AppFabric services (notice the title bar, “System Service Event Collector” and “System Service Workflow Management Service”:

image

image

All the errors pointed at the same exception: “Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’”. What this means is that the Windows Server AppFabric services were not sending the service account through but instead were connecting with the anonymous log on, and, of course, this account does not have access to the AppFabric databases. This error is also visible from the SQL Server logs, which you can also use for this kind of troubleshooting. The error messages in the SQL Server log were (I masked the IP address):

“Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: XXX.XX.XXX.XX]”

and

“Error: 18456, Severity: 14, State: 11. “

Usually this error means that the SQL Server Service Principal Name (SPN) was not configured, and NTLM was not being used as an authentication mechanism.

Now that we knew that this was the issue the first thing we tried was what fixed it – we simple restarted the two Windows Server AppFabric services. That simple. Maybe some changes were made to SQL Server after AppFabrc had been configured and the windows services got out of synch from SQL Server. While they could find the server, they could not authenticate against properly. If the restart had not fixed it we would go the path of ensuring the SQL Server service’s SPN was registered correctly.

Once the services were restarted we could then see the number of WCF completed calls start to rise and it all started working properly:

image

I found this post so useful that I wanted to add it to my blog. Original article can be found here written by Thiago Almeida and last revisioned by Maheshkumar S Tiwari on 10 Sep 2013 11:34 PM.

Read More →

Calculating SQL Server Table Sizes

sql_server_logo

June 23, 2014 | Posted in SQL Server

SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) – SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE ‘dt%’
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
5 desc

Read More →

An error occurred while receiving data: ’10054(error not found)

sql_server_logo

June 19, 2014 | Posted in SQL Server

Having problems sending broker messages from the initiator over to the target?

GRANT CONNECT ON ENDPOINT::YourEndPoint TO [public]

Read More →

Locked out of aspnetdb

sql_server_logo

May 7, 2014 | Posted in SQL Server

Found yourself locked out of aspnetdb and need to reset your password?

Declare @UserName NVarChar(30)
Declare @Password NVarChar(30)
Declare @Application NVarChar(255)
Declare @PasswordSalt NVarChar(128)

set @UserName = ‘SomeUser’
set @Password = ‘SomeP@$$word’
set @Application = ‘/ApplicationName’
Set @PasswordSalt = (SELECT 1 PasswordSalt FROM aspnet_Membership WHERE UserID = ’00000000-0000-0000-0000-000000000000′)

Exec dbo.aspnet_Membership_ResetPassword @Application, @UserName, @Password, 10, 10, @PasswordSalt, -5

Read More →

Flooring And Truncating Decimals

net_logo

April 16, 2014 | Posted in .Net

There may be legitimate circumstances where you want to store a specific decimal value and you don’t want SQL Server to round that precision off which would give you incorrect results when multiplying by large numbers.

Example 10.73085 stored in a SQL Server decimal 18,4 will round up and be stored as 10.7309. If like me you wanted to keep the 4 decimal place precision without round up there is a way.

(Math.Floor(value * 10000) / 10000)

Just specify a zero for each decimal place you wish to keep i.e. * 1000 for 3dp, * 100 for 2 dp and so on.

Read More →

How To See Which Ports Are Listening

command_prompt

April 11, 2014 | Posted in Commant Prompt

netstat -an | find /i “listening”

Read More →

Locating User In Active Directory Using System.DirectoryServices.DirectorySearcher

net_logo

April 11, 2014 | Posted in .Net

DirectorySearcher.FindAll() is very slow. Instead search for the specific AD user, much faster:

Dim windowsLogin As String
Dim currentIdentity As System.Security.Principal.WindowsIdentity
Dim slashIndex As Integer = 0

Try
currentIdentity = System.Security.Principal.WindowsIdentity.GetCurrent()
slashIndex = currentIdentity.Name.IndexOf(“\”)
windowsLogin = currentIdentity.Name.Substring(slashIndex + 1)
Catch
Throw
End Try

Dim searcher As New DirectorySearcher(“”)
Dim result As SearchResult = Nothing
With searcher
.Filter = String.Format(“(&(objectCategory=person)(SAMAccountName={0}))”, windowsLogin)
result = .FindOne()
End With

If ((result IsNot Nothing) AndAlso
(result.Properties.Item(“SAMAccountName”) IsNot Nothing) AndAlso
(result.Properties.Item(“SAMAccountName”).Item(0) IsNot Nothing) AndAlso
(CStr(result.Properties.Item(“SAMAccountName”).Item(0)).ToUpper() = windowsLogin.ToUpper())) Then

MessageBox.Show(String.Format(“User {0}”, windowsLogin) + ” found”)

End If

Read More →

Why Can’t I See Queued Service Broker Messages In Sys.Transmission_Queue?

sql_server_logo

April 11, 2014 | Posted in SQL Server

You need to be in the db_owner() role. The only way around this is to create a stored procedure to execute as dbo which will return this information:

CREATE VIEW sys.transmission_queue AS
SELECT conversation_handle = S.handle,
to_service_name = Q.tosvc,
to_broker_instance = Q.tobrkrinst,
from_service_name = Q.fromsvc,
service_contract_name = Q.svccontr,
enqueue_time = Q.enqtime,
message_sequence_number = Q.msgseqnum,
message_type_name = Q.msgtype,
is_conversation_error = sysconv(bit, Q.status & 2),
is_end_of_dialog = sysconv(bit, Q.status & 4),
message_body = Q.msgbody,
transmission_status = GET_TRANSMISSION_STATUS (S.handle),
priority = R.priority
FROM sys.sysxmitqueue Q
INNER JOIN sys.sysdesend S WITH (NOLOCK) on Q.dlgid = S.diagid AND Q.finitiator = S.initiator
INNER JOIN sys.sysdercv R WITH (NOLOCK) ON Q.dlgid = R.diagid AND Q.finitiator = R.initiator
 WHERE is_member(‘db_owner’) = 1 

Read More →

Visual Studio Treat Warnings As Errors

net_logo

April 9, 2014 | Posted in .Net

I spent a considerable amount of time trying to find all of the VB.Net and C# check id’s so that I could treat a select few warnings as errors. The truth of it is, that the Visual Basic compiler is a lot less picky than the C# compiler. As an example, the default behaviour of the C# compiler is to tell you that a function has no return value (as an error), whereas in the land of Visual Basic you need to specify this under VB Project -> Compile -> Warning Configurations (Condition “Unused local variable” = “Error”). This instructs the Visual Basic compiler to treat these warnings as errors and as you would expect, they then show up in your errors list.

There seem to be very few options for Visual Basic projects to achieve this for many of the desired warnings as errors, but the following should get you started:

http://msdn.microsoft.com/en-us/library/3y20cc1z.aspx

I’ve also managed to find the C# equivilent URL which lists the check id’s here:

http://msdn.microsoft.com/en-us/library/dd380629.aspx

NOTE: If you exceed the maximum number of warnings in Visual Studio and you are treating them as errors, the errors will not show if they are out of scope and will effectively be hidden!! Watch out for this.

Read More →

SQL Char Count

sql_server_logo

February 24, 2014 | Posted in SQL Server

A nice and simple way to find the total instances of a specific char in a SQL varchar:

select (len(’1/2/3/4/5/6′) – len(replace(’1/2/3/4/5/6′, ‘/’, ”))) as char_count

Read More →

Setting A Windows Service Dependancy

windows

November 21, 2013 | Posted in Windows

1. Open the registry editor:
Start -> Run -> regedit

2. Navigate to the specific service you are trying to delay:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\

3. If there is a “DependOnService” value, simply modify it and add the service you want to start before the one you are editing in the registry.

If no “DependOnService” value exists create one:
Right click service name -> New -> Multi-string value
Enter the name DependOnService then modify this new record and add the service you want to start before the one you are editing.

To verify you have the right service name to enter open up Services, right click the service in question, and choose properties. Use the Service name and not the Display name. For instance the IIS Admin Service name is “IISADMIN”.

4. The server will require a restart to apply the change. Once restarted you can open Services, right click the service you edited, choose properties, then the Dependencies tab and you will see the change.

Read More →

VS2012 Schema compare buttons disabled

Visual Studio 2013

November 15, 2013 | Posted in Visual Studio 2012

There have been many trials and tribulations during the upgrade process from Visual Studio 2010 to Visual Studio 2012, not least the SQL Schema Compare functionality.

Having done the initial upgrade between the previous to the new version of Visual Studio, all .dbproj files were converted over to the new .sqlproj format. If you have written any SQL CLR projects in Visual Studio 2010 i.e. .csproj format, these too will get converted to .sqlproj files.

The first thing you will realize is that the references from your new .sqlproj file will throw syntax errors saying that your schema objects i.e. sprocs/functions etc can no longer resolve your CLR proj references. To get around this, go to properties on the reference too your CLR proj file, and change property “Model Aware” to True, this will allow you to compile again successfully.

The new way of doing a schema compare is to create a schema compare file. From with the VS IDE, go to SQL -> Schema Compare -> New Schema Compare. It’s important to note that the settings regarding which types to ignore are now held in this .scmp file. Once you’ve been through the settings and configured it to your liking, check this file in to TFS so that you can reuse it, otherwise you’ll end up having to go through these settings time and time again.

If you try and compare your new .sqlproj file to a SQL Server database, you’ll notice that the two “Update” and “Generate Script” buttons are disabled.

VS2012 Schema compare buttons disabled

Through a lot of digging, I eventually found that the reason for this is that under your .sqlproj settings, you need to set a “Default” value on any “SQLCMD Variables” you may have included (this is the name of the option on the left hand side). Having done this, the options should become available (note you may need to restart the solution).

sqlcmd_variables

The behaviour of these SQLCMD Variables seems to be that it copies the values to your .sqlproj.user file, which the schema compare relies on. If you do not set a default value, you won’t be able to do a compare and generate scripts. The value you provide in the “Default” value will be stored in the project file and can be checked in to source control, however the “Local” value is specific to your machine which gets stored in your .sqlproj.user file I mentioned previously (which is not under source control). If you leave the Local value blank, the Default value will be used.

If this helped solve your issue, leave a comment and let me know.

Read More →

Referencing CLR Projects in Visual Studio 2012

Visual Studio 2013

October 9, 2013 | Posted in Visual Studio 2012

When migrating your database projects from Visual Studio 2010 to Visual Studio 2012, you may run in to a problem. If you have a database project referencing a c# class library which is your CLR project, you’ll find the reference no longer resolves. Why?

Simply right-click the CLR reference in your database project, go to properties, and set “Model Aware” to true, and your issue will be resolved.

Read More →

Bing Geocode Service Returns The remote server returned an unexpected response: (417) Expectation Failed.

net_logo

October 4, 2013 | Posted in .Net

I’d been using the geocode SOAP service for a while and all of a sudden I started to run in to the following error:

The remote server returned an unexpected response: (417) Expectation Failed.

To get around the around the issue, I added a simple setting before making the request to the service (in bold):

 private void GeocodeAddress(string address)
 {
     try
     {
         string key = Properties.Settings.Default.BingMapsKey;
         GeocodeRequest geocodeRequest = new GeocodeRequest();

         // Set the credentials using a valid Bing Maps key
         geocodeRequest.Credentials = new GeocodeService.Credentials();
         geocodeRequest.Credentials.ApplicationId = key;

         // Set the full address query
         geocodeRequest.Query = address;

         // Set the options to only return high confidence results 
         ConfidenceFilter[] filters = new ConfidenceFilter[1];
         filters[0] = new ConfidenceFilter();
         filters[0].MinimumConfidence = GeocodeService.Confidence.High;

         // Add the filters to the options
         GeocodeOptions geocodeOptions = new GeocodeOptions();
         geocodeOptions.Filters = filters;
         geocodeRequest.Options = geocodeOptions;

         // Switch off 100 continue expectation
         System.Net.ServicePointManager.Expect100Continue = false;

         // Make the geocode request
         GeocodeServiceClient geocodeService = new GeocodeServiceClient();
         GeocodeResponse geocodeResponse = geocodeService.Geocode(geocodeRequest);

         if (geocodeResponse.Results.Length > 0)
         {
             this._addressLatitude = Convert.ToString(geocodeResponse.Results[0].Locations[0].Latitude);
             this._addressLongitude = Convert.ToString(geocodeResponse.Results[0].Locations[0].Longitude);
         }
     }
     catch
     {
         throw;
     }
 }

Read More →

Scale out SQL Server by using Reliable Messaging

sql_server_logo

October 3, 2013 | Posted in SQL Server

How do large-scale sites and applications remain SQL-based? is a recent article from Michael Rys (Blog|Twitter) that highlights the relational SQL Server based web-scale deployment at MySpace. I have talked before about how MySpace uses Service Broker as a reliable messaging backbone to power the communications between +1000 databases, allowing them to scale-out and partition the user information into individual shards. Here are some more details about this architecture:

This new article uses the MySpace deployment as a case study to counter balance the claim that large web-scale deployments require the use of NoSQL storage because relational database cannot scale. BTW I know the SQL vs. NoSQL discussion is more subtle, but I won’t enter into details here. I think a good read on thattopic is NoSQL vs. RDBMS: Let the flames begin!.

Why is reliable messaging a key tenet of implementing a data-partitioned scale-out application? Consider a typical example of an modern web-scale application: users connect, get authenticated and view their own profile, but they are also interested in the status updates or wall messages from their network of friends or followers. It is easy to see how one partitions the user profile data, but how do you partition the user ‘walls’? User A is in a partition hosted on node 1, while user B is in a partition hosted by node 2, when User A update his status, how does this show up on User B’s wall?

One option is to have the application update the wall of User B when User A changes his status. But this prevents scalability, because now writes have to occur on many nodes and the application has to orchestrate all these writes. Think Lady GaGa updating her status, the application has to update the wall of every follower.

Another option is to have the application read the status from User A when displaying the wall of User B. This also doesn’t scale, because reads now occur on many nodes. All those Lady GaGa followers refreshing their wall page have to read from the one node hosting her status, and the node is soon overwhelmed.

A solution is to replicate the write on User A’s status onto User B’s wall. The application only updates the User A status, and the infrastructure propagates the this update to User B’s wall.

But traditional replication was historically designed for replicating entire data sets of fixed schema over static topologies, and it falls short of replicating web-scale deployments of hundreds and thousands of nodes:

  • it depends too tightly on physical location and it cannot adapt to rapid changes of topology (nodes being added and removed)
  • its based on schema defined filtering which is difficult to map to the complex application specific data routing conditions (this update goes to node 2 because User B follows User A, but that update goes to node 3 because User D follows User C)
  • its very sensitive to schema changes making application upgrade roll outs a big challenge

Messaging is designed with application-to-application communication in mind and has different semantics that are more friendly on large scale-out deployments:

  • Logical routing to isolate application from topology changes
  • Protocol versioning information allows side-by-side deployments making application upgrade roll outs possible
  • Data schema can change more freely as peers are shielded from changes by keeping the communication protocol unchanged

With messaging in place the application updates the status of User A and the drops a message into a local outbound queue to notify all friends of A. The reliable messaging infrastructure dispatches this message to all nodes interested and processing of this message results in an update of User B wall. MySpace uses Service Broker as the reliable messaging infrastructure, and they make up for the lack of publish/subscribe by adding a router-dispatcher unit: all messages are sent to this dispatcher and the dispatcher in turn figures out how many subscribers have to be notified, and sends individual messages to them. See the slides linked at the beginning of my post for more details. As a side note, I see that StackExchange is also embarking on the route of creating a message bus for their pub/sub infrastructure, but they use Redis as a message store, see asyncRedis await BookSleeve.

Robert Scoble had a post MySpace’s death spiral: insiders say it’s due to bets on Los Angeles and Microsofton which it claims that MySpace insiders blame their loss to Facebook to, amongst other things, the complexity of this infrastructure:

Workers inside MySpace tell me that this infrastructure, which they say has “hundreds of hacks to make it scale that no one wants to touch” is hamstringing their ability to really compete.

I do no know if the sources quoted by the article are right or wrong, and I was never personally involved with the MySpace deployment, but since I was so closely involved in building SQL Service Broker and as SSB is one of the critical components used by MySpace infrastructure, I am understandably interested in this discussion. Service Broker has a notoriously steep learning curve, there are no tools for administer, monitor and troubleshoot Service Broker deployments, and there is absolutely no support in the client programming stack (the managed .Net Framework). This is why all solutions that deploy Service Broker that I know of are large enterprise shops that are staffed with architects that understand the set of unique advantages this technology brings, and are willing to venture into uncharted waters despite the prospect of being impossible to hire development and ops talent with Service Broker expertise. But also the feedback I hear from these deployments is almost always positive: once deployed, Service Broker just works. Someone told me that the Service Broker solution they had was the only piece of technology that “did not break in the last 3 years” and that covers an upgrade from SQL Server 2005 to SQL Server 2008. See Is Service Broker in SQL Server 2008 compatible with the one in SQL Server 2005? to see how Service Broker helps address infrastructure upgrade problems. Personally I am not surprised that Service Broker turns out to be a cornerstone of the response SQL Server has to give to the NoSQL challenge, but this vitally unheard of technology (89 questions tagged service-broker on StackOverflow at time of writing this) will be quite a surprise answer for many

Read More →

Working Out Which Service Broker Queues Are Bound To Your Services

sql_server_logo

October 3, 2013 | Posted in SQL Server

select    [service].name, [queue].name
from    sys.services [service]
join    sys.service_queue_usages [useage]
on        [service].service_id = [useage].service_id
join    sys.service_queues [queue]
on        [queue].object_id = [useage].service_queue_id
where    [queue].is_ms_shipped = 0

Read More →

Printing Blocks Of SQL

sql_server_logo

October 3, 2013 | Posted in SQL Server

–@sql contains the sql to be printed in segments
DECLARE @CurrentEnd BIGINT; /* track the length of the next substring */
DECLARE @offset tinyint; /*tracks the amount of offset needed */
set @sql = replace(  replace(@sql, char(13) + char(10), char(10))   , char(13), char(10))

WHILE LEN(@sql) > 1
BEGIN
IF CHARINDEX(CHAR(10), @sql) between 1 AND 4000
BEGIN
SET @CurrentEnd =  CHARINDEX(char(10), @sql) -1
set @offset = 2
END
ELSE
BEGIN
SET @CurrentEnd = 4000
set @offset = 1
END
PRINT SUBSTRING(@sql, 1, @CurrentEnd)
set @sql = SUBSTRING(@sql, @CurrentEnd+@offset, LEN(@sql))
END /*End While loop*/

Read More →

Debugging Remote SQL Server Service Broker Configuration

sql_server_logo

October 3, 2013 | Posted in SQL Server

Run command prompt with the following:

cd C:\Program Files\Microsoft SQL Server\100\Tools\Binn

The syntax to then check two services on different servers is:

ssbdiagnose CONFIGURATION FROM SERVICE /text/initiator
-S InitiatorComputer -U InitiatorLogin -p !wEx23Dvb
-d InitiatorDatabase TO SERVICE /test/target -S TargetComputer
-U TargetLogin -p ER!49jiy -d TargetDatabase ON CONTRACT TestContract

Read More →

Stopping And Starting SQL Server Agent Using Batch Files

sql_server_logo

October 3, 2013 | Posted in SQL Server

StopSQLServerAgent.bat

@echo off
cls

net stop SQLSERVERAGENT

echo Completed at %date%_%time%
echo Holding result for 24 hours.
ping -n 1 -w 86400000 1.2.3.4 > nul
EXIT

 

StartSQLServerAgent.bat

@echo off
cls

net start SQLSERVERAGENT

echo Completed at %date%_%time%
echo Holding result for 24 hours.
ping -n 1 -w 86400000 1.2.3.4 > nul
EXIT

Run both files as administrator.

Read More →

Disaster Recovery: What to do when the SA account password is lost in SQL Server 2005

sql_server_logo

October 3, 2013 | Posted in SQL Server

You may have faced the issue of losing the SQL Server SA password. Perhaps you followed the security best-practice of removing the  builtin\Administrators from the sysadmin server role,  and no one  you can find is in the sysadmin role.   At this point you may think that your only options are to reinstall SQL Server and attach the databases, or to  directly access the master database files, which may potentially damage the data.

SQL Server 2005 provides a better disaster recovery option for this scenario that is non-intrusive for master DB and that will help you preserve any objects and data stored in master DB (such as logins, certificates, Service Master Key, etc.) intact. Members of the Windows Administrators group now have access to SQL Server when SQL Server is in started in single-user mode, also known as “maintenance mode “.

Using the single-user mode, SQL Server 2005 prevents a  Windows Administrator to abuse this privilege to act on behalf of the sysadmin without being noticed. This allows Windows Administrator accounts to perform certain maintenance tasks, such as installing patches.

In order to start SQL Server in single-user mode, you can add the parameter “-m” at the command line. You can also use the SQL Server Configuration Manager tool, which provides proper controls for the file access and other privileges. To use the Configuration Manager tool to recover your system, use the following steps:

1. Open the Configuration Manager tool from the “SQL Server 2005| Configuration” menu

2. Stop the SQL Server Instance you need to recover

3. Navigate to the “Advanced” tab, and in the Properties text box add “;–m” to the end of the list in the “Startup parameters” option

4. Click the “OK” button and restart the SQL Server Instance

NOTE: make sure there is no space between “;” and “-m”, the registry parameter parser is sensitive to such typos. You should see an entry in the SQL Server ERRORLOG file that says “SQL Server started in single-user mode. After the SQL Server Instance starts in single-user mode, the Windows Administrator account is able to connect to SQL Server using the sqlcmd utility using Windows authentication. You can use Transact-SQL commands such as “sp_addsrvrolemember” to add an existing login (or a newly created one) to the sysadmin server role.

Run the following from command prompt:

sqlcmd -E -S LOCALHOST\MSSQLSERVER (hit enter)

1> EXEC sp_addsrvrolemember ‘DOMAIN\YourAccount’, ‘sysadmin’; (hit enter)

2> GO (hit enter)

Once the sysadmin access has been recovered, remove the “;-m” from the startup parameters using the Configuration Manager and restart the SQL Server Instance

Read More →

Scott Hanselman’s 2011 Ultimate Developer and Power Users Tool List for Windows

windows

October 3, 2013 | Posted in Windows

Probably the most comprehensive list of useful utilities out there!

http://www.hanselman.com/blog/ScottHanselmans2011UltimateDeveloperAndPowerUsersToolListForWindows.aspx

Thanks Scott.

Read More →

SQL Server Transaction was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

sql_server_logo

October 3, 2013 | Posted in SQL Server

This is a classic issue whilst using SQL Server, where two processes are trying to lock the same resources at the same time. If you are handling this with some sort of retry mechanism like myself, try the following to slow SQL Server down before attempting the command again.

/* delay retry for a random number of seconds >= 4 and <= 9 */
declare @Random int, @Upper int, @Lower int, @time varchar(50);

set        @Lower = 4
set        @Upper = 9
select    @Random = ROUND(((@Upper – @Lower -1) * RAND() + @Lower), 0)

set @time = ’00:00:0′ + cast(@Random as varchar(50))

WAITFOR DELAY @time

Read More →

SET NOCOUNT ON Improves SQL Server Stored Procedure Performance

sql_server_logo

October 3, 2013 | Posted in SQL Server

Problem
One of the biggest things that DBAs try to do on a daily basis is to ensure that their database systems run as fast as possible.  As more and more users access the databases and the databases continue to grow, performance slow downs are almost inevitable. Based on this, DBAs and developers should do everything they possibly can to keep performance related issues in mind early in the database lifecycle.  This is not always easy to do, because of the unknowns and the changes that occur over time, but there are some simple things that can be done and we will touch upon one of these in this tip.

Solution
Sometimes even the simplest things can make a difference.  One of these simple items that should be part of every stored procedure is SET NOCOUNT ON.  This one line of code, put at the top of a stored procedure turns off the messages that SQL Server sends back to the client after each T-SQL statement is executed.  This is performed for all SELECT, INSERT, UPDATE, and DELETE statements. Having this information is handy when you run a T-SQL statement in a query window, but when stored procedures are run there is no need for this information to be passed back to the client.

By removing this extra overhead from the network it can greatly improve overall performance for your database and application.

If you still need to get the number of rows affected by the T-SQL statement that is executing you can still use the @@ROWCOUNT option.  By issuing a SET NOCOUNT ON this function (@@ROWCOUNT) still works and can still be used in your stored procedures to identify how many rows were affected by the statement.

Microsoft even realized the issue that this creates and has changed the stored procedure templates from SQL Server 2000 to SQL Server 2005.

Here is the old template style available in SQL Server 2000 without the SET NOCOUNT ON.

– =============================================
– Create procedure basic template
– =============================================
– creating the store procedure
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N’<procedure_name, sysname, proc_test>’
AND type = ‘P’)
DROP PROCEDURE <procedure_name, sysname, proc_test>
GO

CREATE PROCEDURE <procedure_name, sysname, proc_test>
<@param1, sysname, @p1> <datatype_for_param1, , int> = <default_value_for_param1, , 0>,
<@param2, sysname, @p2> <datatype_for_param2, , int> = <default_value_for_param2, , 0>
AS
SELECT @p1, @p2
GO

– =============================================
– example to execute the store procedure
– =============================================
EXECUTE <procedure_name, sysname, proc_test> <value_for_param1, , 1>, <value_for_param2, , 2>
GO

Here is the new template style available in SQL Server 2005 with the SET NOCOUNT ON.

– ================================================
– Template generated from Template Explorer using:
– Create Procedure (New Menu).SQL

– Use the Specify Values for Template Parameters
– command (Ctrl-Shift-M) to fill in the parameter
– values below.

– This block of comments will not be included in
– the definition of the procedure.
– ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
– =============================================
– Author: <Author,,Name>
– Create date: <Create Date,,>
– Description: <Description,,>
– =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
– Add the parameters for the stored procedure here
<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
– SET NOCOUNT ON added to prevent extra result sets from
– interfering with SELECT statements.
SET NOCOUNT ON;

– Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

As you can see even simple little things such as this can make an overall improvement for your database environment.  Stay tuned for other simple tricks and techniques to improve performance.

Original source: http://www.mssqltips.com/sqlservertip/1226/set-nocount-on-improves-sql-server-stored-procedure-performance/

Read More →

Creating An Entity Framework Connection String

net_logo

October 3, 2013 | Posted in .Net

Not quite the same as the conventional ADO.Net connection string. Quick function to return the required format by Entity Framework:

Public Shared Function EntityFrameworkConnectionString() As String
Dim efConnectionString As String = New System.Data.EntityClient.EntityConnectionStringBuilder() _
With {.Metadata = “res://*”, _
.Provider = “System.Data.SqlClient”, _
.ProviderConnectionString = New System.Data.SqlClient.SqlConnectionStringBuilder() _
With {.InitialCatalog = “DatabaseName”, _
.DataSource = “ServerName”, _
.IntegratedSecurity = True
}.ConnectionString
}.ConnectionString
Return efConnectionString
End Function

Read More →

Creating A SQL Server Table, The Easy Way

sql_server_logo

October 3, 2013 | Posted in SQL Server

I wanted to create a quick table in my database to store some values so that I could later roll back the values in the existing table once I had finished some testing. Rather than creating a temporary table for the lifespan on my SQL connection, instead I created a table with this simple T-SQL statement:

select * into tblTempTable
from tblExisingTable

Voila.

Read More →

Debugging SQL Server Service Broker Configuration

sql_server_logo

October 3, 2013 | Posted in SQL Server

Having probed SQL Server Service Broker for some time trying to diagnose a fault with it’s configuration, I soon found a tool that would do this for me in the tools folder under the installation directory called ssbdiagnose.exe. Below is an example of how to query the configuration of your service broker setup (it will diagnose and return any faults):

Run command prompt (I ran this from machine as I had SQL Service 2008 installed which this tool is part of):

cd C:\Program Files\Microsoft SQL Server\100\Tools\Binn

C:\Program Files\Microsoft SQL Server\100\Tools\Binn>ssbdiagnose -u username -p password -s “server ip” -d “databasename” CONFIGURATION FROM SERVICE ”
http://fromservice” TO SERVICE “http://toservice” ON CONTRACT “http://contract”

The results may look something like the following:

Microsoft SQL Server 10.50.1600.1
Service Broker Diagnostic Utility
D  29997 127.0.0.1     Database1      Service Broker GUID is identical to tha
t of database Database1 on server 127.0.0.1
D  29975 127.0.0.1     Database1 User dbo does not have SEND per
mission on service http://someservice
D  29964 127.0.0.1     Database2 The master key was not found
3 Errors, 0 Warnings

C:\Program Files\Microsoft SQL Server\100\Tools\Binn>

It’s also worth noting that you can run ssbdiagnose against a SQL Server 2005 instance.

Read More →

Drop SQL CLR Assembly And It’s Dependencies

sql_server_logo

October 3, 2013 | Posted in SQL Server

Having looked for something that would drop an assemblies dependencies prior to dropping the assembly itself, I couldn’t find anything. So I put this together. Enjoy.

/* review schema information */
select  [schema].Name,
[modules].object_id,
[modules].assembly_id,
[modules].assembly_class,
[modules].assembly_method,
[objects].name,
[objects].type,
[objects].type_desc
from    sys.assembly_modules [modules]
join    sys.objects [objects] with (nolock)
on        [modules].object_id = [objects].object_id
join    sys.schemas [schema] with (nolock)
on        [objects].schema_id = [schema].schema_id
where    [modules].assembly_class in (N’NAME’)

begin try

begin transaction;

/* drop clr dependencies */
declare @dynamicSQL nvarchar(max)

declare @schema nvarchar(200)
declare @name nvarchar(200)
declare @type nvarchar(200)

declare db_cursor cursor for
select  [schema].Name,
[objects].name,
[objects].type_desc
from    sys.assembly_modules [modules]
join    sys.objects [objects] with (nolock)
on        [modules].object_id = [objects].object_id
join    sys.schemas [schema] with (nolock)
on        [objects].schema_id = [schema].schema_id
where    [modules].assembly_class in (N’NAME’)

open db_cursor
fetch next from db_cursor into @schema, @name, @type

while @@FETCH_STATUS = 0
begin
set @dynamicSQL = N”;
set @dynamicSQL = case @type
when N’CLR_STORED_PROCEDURE’ then N’drop procedure’ + N’ ‘ + @schema + N’.’ + @name + N’;’
when N’CLR_SCALAR_FUNCTION’ then N’drop function’ + N’ ‘ + @schema + N’.’ + @name + N’;’
when N’CLR_TABLE_VALUED_FUNCTION’ then N’drop function’ + N’ ‘ + @schema + N’.’ + @name + N’;’
else N’Object Not Recognised’
end

–print @dynamicSQL;
exec sp_executesql @dynamicSQL;

fetch next from db_cursor into @schema, @name, @type
end

close db_cursor
deallocate db_cursor

/* drop assembly once free of dependencies */
drop assembly [NAME]

if @@trancount > 0
commit transaction;

end try
begin catch

rollback transaction;
print ‘ERROR: ‘ + error_message()

end catch

Read More →