1 0 Tag Archives: Tips & Tricks
post icon

TSQL – How to find missing or deleted table identity autoincrement values

As a database designer, I often create on database tables the primary key as identity autoincrement and sometimes I needed to find missing or deleted IDs thet interrupt the sequence of autoincrement number on the primary key.
To do this you can create a sequence from 1 (or 0 based on setted RESEED) to MAX ID available on the table and than make a LEFT JOIN of this sequence with start table.

The following snippet get the list of IDs of identity autoincrement column that interrupt the sequence of autoincrement values:

DECLARE @START_ID INT,  @MAXID INT

SET @START_ID = 1
SET @MAXID = (SELECT MAX(ID) FROM [TABLENAME_TO_CHECK])

DECLARE @SEQUENCE_TABLE TABLE (ID BIGINT)

--CREATE TABLE WITH ID FROM [@START_ID] TO [MAX TABLE ID]
WHILE @START_ID < @MAXID
	BEGIN
		INSERT INTO @SEQUENCE_TABLE VALUES(@START_ID)

		SET @START_ID = @START_ID + 1
	END

SELECT S.ID AS [MISSING ID] 
FROM @SEQUENCE_TABLE S 
		LEFT JOIN [TABLENAME_TO_CHECK] AS T ON  S.ID = T.ID
WHERE T.ID IS NULL
post icon

TFS find in files version history with Visual Studio 2010 and 2012

Recently I needed to find in all files version history of a TFS 2010 project with Visual Studio 2010.

Searching on Google I found Visual Studio 2010 and 2012 extensions, developed by Mattias Sköld, that allow you to achieve the goal.

Let’s see how to find inside all version of files with Visual Studio 2010 on TFS 2010.

Once installed the TFS Administrators Toolkit for VS 2010 extension we will get the feature by right-clicking on the TFS root Collection from Team Explorer window, as illustrated in Figure 1.

Figure 1

Figure 1

We you ckick Find in Files the followinf popup will open:

Figure 2

Figure 2

As you can see in the Figure 2, the feature allow you to:

1) Select Team Project
2) Filter by extension file type
3) Search or not in the file history
4) Specify search terms (keywords) with case sensitive and regular expressions options

post icon

TSQL – Passing a list to a Stored Procedure

 

For different needs, often I had needs to send a list of values ​​to a Stored Procedure.
To achieve this goal it is possible to use a workaround with the TSQL function nodes(XQuery) on the types of XML data.

We’ll see two examples to better understand the proposed solution.

Example 1: How to send to a Stored Procedure a list of ID.

1 – Build an XML that contains a list of ID, as follows:

 
<MyIDList>
  <ID>1</ID>
  <ID>2</ID>
</MyIDList>

In C # I can build XML as follows:

XmlDocument xmlDoc = new XmlDocument();
XmlElement xElem;
		
//add root node
XmlElement xRoot = xmlDoc.CreateElement("MyIDList");
xmlDoc.AppendChild(xRoot);
		
//Add ID 1
xElem = xmlDoc.CreateElement("ID");
xElem.InnerText = "1";
xRoot.AppendChild(xElem);
//Add ID 2
xElem = xmlDoc.CreateElement("ID");
xElem.InnerText = "2";
xRoot.AppendChild(xElem);

2 – Sent the XML built to the Stored Procedure.

In C# create DbParam as follows:

//@MyIDListXML
DbParameter dbParam = dbCommand.CreateParameter();
dbParam.ParameterName = "@MyIDListXML";
dbParam.DbType = DbType.Xml;
dbParam.Value = xmlDoc.InnerXml; //Note you need to pass the InnerXml Value
dbCommand.Parameters.Add(dbParam);				

3 – Get the @MyIDListXML parameter value and obtain the list of ID in a relational format to do a lot of relational operations (JOIN, WHERE, etc.)

SELECT T.Item.value('.' , 'BIGINT') AS ID
FROM @MyIDListXML.nodes('//MyIDList/ID') AS T(Item)

The result will be a table with two value like in figure:

Example 2: Hot to send a table to a Stored Procedure.

1 – Build an XML which represents a table like the following:

 
<MyList>
	<item attributo1="value11" attributo2="value12" />
	<item attributo1="value21" attributo2="value22" />
</MyList>

In C# I can create the XML as follows

 
XmlDocument xmlDoc = new XmlDocument();
XmlElement xElem;

//add root node
XmlElement xRoot = xmlDoc.CreateElement("MyList");
xmlDoc.AppendChild(xRoot);

//add item 1
xElem = xmlDoc.CreateElement("item");
xElem.SetAttribute("attributo1", "value11");
xElem.SetAttribute("attributo2", "value12");
xRoot.AppendChild(xElem);

//add item 2
xElem = xmlDoc.CreateElement("item");
xElem.SetAttribute("attributo1", "value21");
xElem.SetAttribute("attributo2", "value22");
xRoot.AppendChild(xElem);

2 – Sent to the Stored Procedure the XML built

In C# create DbParameter as follows:

 
//@MyListXML
DbParameter dbParam = dbCommand.CreateParameter();
dbParam.ParameterName = "@MyListXML";
dbParam.DbType = DbType.Xml;
dbParam.Value = xmlDoc.InnerXml; //Note you need to pass the InnerXml Value
dbCommand.Parameters.Add(dbParam);	

3 – Get @MyListXML list values and I can do a lot of relational operations (JOIN, WHERE, etc.)

 
SELECT T.Item.value('@attributo1' , 'varchar(10)') AS Attributo1,
	T.Item.value('@attributo2' , 'varchar(10)') AS Attributo2
FROM @MyListXML.nodes('//MyList/item') AS T(Item)

The result will be like in the following table:

It’s possible to pass any XML structure and work on SQL Engine with the nodes(XQuery) function.

April 26th, 2013 @ 03:43
post icon

Sharepoint 2010 List Event Receiver SPContext.Current is null

Quando si sviluppa un Event Receiver su una generica lista Sharepoint non è possibile ottenere l’istanza di SPWeb dal contesto corrente mediante l’istruzione seguente:

SPWeb web = SPContext.Current.Web;

poichè viene eseguito in un contesto differente, e dunque SPContext.Current = null.

Per ovviare a questo problema è sufficiente far riferimento al parametro SPItemEventProperties ricevuto, e quindi ottenendo un’istanza di SPWeb come di seguito:

using (SPWeb web = properties.OpenWeb())
{
// do stuff
}

June 15th, 2011 @ 02:41
post icon

Microsoft Visio 2007-2010 has stopped working

 

Lavorando su Microsoft Visio 2010 mi è capitato spesso di ricevere il seguente errore: Microsoft Visio 2007-2010 has stopped working..

Ho dovuto cercare su vari forum prima che riuscissi a trovare una soluzione, così ho deciso di scrivere un post a vantaggio di tutti gli utenti della Rete.
Il problema si verificava con Visio 2010, mo ho avuto modo di vedere che si verificava anche con Visio 2007, è che si interrompeva continuamente segnalando il seguente errore:
“Microsoft Visio has stopped working; A problem caused the program to stop working correctly. Windows will close the program and notify you if a solution is available”
Questo problema si verifica a causa del fallimento dell’Add-in “Send to Bluetooth” sia in Microsoft Visio 2007 che in  Microsoft Visio 2010 sul sistema operativo Windows Vista e Windows 7.
Soluzione:
C’è bisogno di disabilitare l’Add-in “Send to Bluetooth” e per fare ciò bisogna avviare Microsoft Visio con permessi di Amministratore.
Una volta fatto ciò seguire i seguenti passaggi:
– Posizionarsi nella cartella di installazione di Visio (presumibilmente C:\Program Files (x86)\Microsoft Office\Office14 per Visio 2010)
– Click con tasto destro Visio.exe, e selezionare “Run as Administrator”.
– Una volta avviato visio come Amministratore andare in Options (o Tools nella versione Visio 2007) > Add-ins.
– In basso al popup di Add-in, selezionare COM Add-ins nel menu a tendina, quindi click su GO.
– Deselezionare il checkbox “Send to Bluetooth”.
– Fare click su OK, e riavviare Visio.
A questo punto non si dovrebbe presentare nuovamente il problema.

 

February 4th, 2011 @ 10:02