El TecnoBaúl de Kiquenet

Kiquenet boring stories

MS Search Service, Indexing Service and SQL Server: search engine for ASP.NET website

Posted by kiquenet en 21 junio 2016

Quick and Dirty method to Combine MS Search Service, Indexing Service and SQL Server to provide a unified search engine for your ASP.NET website

WebSite for a company and search engine is required.

They had the contents in .aspx pages, not a problem, but they also had forums whose contents were collated in a database table, in a column to be precise. They wanted me to display the results from these two sources through a common search engine. Since I had little time to write a search engine of my own, I put the power of MS Search Service, Indexing Service and SQL Server together to do the task for me. There is a lot of scope for enhancement but here is how you can implement a very basic yet powerful search engine of your own.

STEP I: Create a Web Catalog in Indexing Service

By default, the indexing service has two catalogs, one for the file system (System) and one for the default web site (Web).
If Web catalog is not present, you can easily create one.

1. Open Control Panel –> Administrative Tools –> Computer Management
2. Scroll down to Computer Management–> Services and Applications –> Indexing Service in the MMC
3. Right click Indexing Service and choose New –> Catalog
4. In the name field give Web and choose C:\Inetpub as the location
5. Right click the newly created catalog, choose Properties. Click on the Tracking tab of the properties window. Select "Default Web Site" as the WWW server.
6. Restart Indexing Service
7. Go to Computer Management –> Services and Applications –> Services and configure Indexing Service as Automatic if it is Manual or Disabled.

STEP II: Optimize ASPX and ASCX files for full-text search

By default, the *.aspx and *.ascx file types are treated as text files which are not optimized for searching by the Indexing Service. To optimize searching for these two file types copy the following into a new .reg file and run it in your computer. The customary warning: Editing registry incorrectly may prohibit your computer to run properly. Edit the registry at your own risk. I may not be held responsible for the damage you do to your computer by incorrectly following the steps below.


You must have Index files with Unknown Extensions enabled. To enable this, right click on Indexing Service, choose Properties and click on Generation tab on the window. Check Index files with Unknown Extensions checkbox. Restart the computer, stop Indexing Service, delete all the contents of the catalog.wci folder (not the folder itself) corresponding to your catalog (in this case C:\Inetpub\catalog.wci), start the Indexing Service and allow it to rebuild the catalog.

STEP III: Using Full-Text Searches directly in ASP.NET Applications

This is not actually a step but a side step where you can take a pause for a moment and test whether your newly created catalog is returning some results. If you don’t have a database to worry about, then this might be your last step unless you want to link the Indexing Service with SQL Server.

Indexing service exposes itself via the OLEDB provider MSIDXS. You can take the full advantage of the server in your ASP.NET application via ADO.NET. If  you have a TextBox (TextBox1), a Button (Button1) and a DataGrid (DataGrid1) on your web form and the Web catalog in place, this might as well be the content of your button click handler:

using System.Data;
using System.Data.OleDb;
private void Button1_Click(object sender, EventArgs e)
      string strCatalog = "Web";
      string strQuery = "Select Filename, Rank, VPath from SCOPE() where FREETEXT(‘" + TextBox1.Text + "’)";
      string connString = "Provider=MSIDXS.1;Integrated Security .=”;Data Source=’" + strCatalog + "’";
      OleDbConnection Connection = new OleDbConnection(connString);
      OleDbDataAdapter da = new OleDbDataAdapter(strQuery, Connection);
      DataSet ds = new DataSet();
      DataView source= new DataView(ds.Tables[0]);
      DataGrid1.DataSource = source;

STEP IV: Link Indexing Service with SQL Server

The next step is to link the Indexing Service with your SQL Server. Open Query Analyzer or your favourite SQL script editor. Run the following script.

EXEC sp_addlinkedserver FTIndexWeb, ‘Index Server’, ‘MSIDXS’, ‘Web’

where FTIndexWeb is the chosen linked server name, and Web is the catalog name you created in STEP I.

STEP V: Querying Indexing Service via SQL Server

Let’s modify the previous query and run it in SQL server. Run the following query in Query Analyzer.

SELECT Q.FileName, Q.Rank, Q.VPath
FROM OpenQuery(
‘Select Filename, Rank, VPath
from SCOPE()
where FREETEXT(”Calcutta”)
              ) AS Q

Replace FTIndexWeb with whatever linked server name you chose in step IV and Calcutta with your search keyword(s).

STEP VI: Enabling a table/column in SQL Server for full-text searches

Open Enterprise Manager.

Browse to Console Root–>; Microsoft SQL Servers –> Databases –> Tables. Check two things before you proceed.

1. The table where you want full-text searching enabled, must have some unique constraint. If a primary key or a unique constraint is not present, create an "ID" column and apply a unique constraint.
2. Microsoft Search Service (mssearch.exe) must have been enabled and running in your computer. If not, browse to Computer Management –> Services and Applications–> Services in Computer Management MMC and configure Microsoft Search Service as Automatic and start the service.

On the Enterprise Manager MMC, right click on your table and choose Full-Text Index Table –> Define Full-Text Indexing on a table. If the option is grayed out, check #2 above.

Click Next on the popped up wizard .
Choose the unique index and click Next.
Choose the columns where you want indexing enabled. Click Next.
Give the catalog a name and specify a physical location to store the catalog. Click Next.

If you want the control over how and when the catalog is filled (full or incremental) click on New Catalog Schedule.
After configuring it, come back to Full-Text Indexing Wizard and click Next. Click Finish.

The wizard takes a minute or two to setup the catalog.

STEP VII: Querying Full-Text Catalog in SQL Server

Let’s test the newly created catalog in SQL Server. Run the following query.

FROM forums_topics AS FT_TBL,
CONTAINSTABLE ( forums_topics
                ,   message
                , ‘"Calcutta"’ )

Forums_Topics is the table name and Message is the column name on which full-text catalog is built. Replace Calcutta with your search keyword(s).

STEP VIII: Combining the results

The steps to combine the results would be to

1. Create a temporary table
2. Insert the results of the first query
3. Insert the results of the second query
4. Query the temp table
5. Drop the temp table

We need a stored procedure for this and here it is:

CREATE PROCEDURE sp_Accounts_SearchSite
@FreeText varchar (255)
CREATE TABLE #tempresults(
FileNames varchar (255),
Rank int,
VPath varchar(255))
DECLARE @sql nvarchar(1000)
SET @sql = N’INSERT INTO #tempresults(FileNames, Rank, VPath) ‘ + CHAR(13) +
N’SELECT Q.FileName As FileNames, Q.Rank As Rank, Q.VPath As VPath ‘ + CHAR(13) +
N’FROM OpenQuery(FTIndexWeb, ”Select Filename, Rank, VPath from SCOPE() where FREETEXT(””’ + @FreeText + ””’)” ) AS Q’
EXECUTE sp_executesql @sql
SET @SQL = N’INSERT INTO #tempresults(FileNames, Rank, VPath) ‘ + CHAR(13) +
N’SELECT FT_TBL.subject As FileNames, KEY_TBL.RANK As Rank, FT_TBL.topicid As VPath ‘ + CHAR(13) +
N’FROM forums_topics AS FT_TBL, ‘ + CHAR(13) +
N’CONTAINSTABLE ( forums_topics ‘ + CHAR(13) +
N’, message’ + CHAR(13) +
N’, ”"’ + @FreeText + ‘"” ) ‘ + CHAR(13) +
N’AS KEY_TBL’ + CHAR(13) +
N’WHERE FT_TBL.topicid = KEY_TBL.[KEY] ‘
EXECUTE sp_executesql @sql
SELECT FileNames, Rank, VPath from #tempresults ORDER BY Rank DESC
DROP TABLE #tempresults

STEP IX: Modify your .NET Application

The rest is a piece of cake. Your Button click handler should now look like this:

using System.Data;
using System.Data.SqlClient; // Bye Bye OleDb
private void Button1_Click(object sender, EventArgs e)
      string connString = @"server=****;database=****;uid=****;pwd=****;";
      string storedProcName = "sp_Accounts_SearchSite";
      SqlConnection Connection = new SqlConnection(connString);
      SqlCommand command = new SqlCommand( storedProcName, Connection );
      command.CommandType = CommandType.StoredProcedure;
      command.Parameters.Add("@FreeText", TextBox1.Text);
      SqlDataAdapter sqlDA = new SqlDataAdapter();
      sqlDA.SelectCommand = command;
      DataSet dataSet = new DataSet();
      sqlDA.Fill( dataSet, "mySearchResults" );
      DataView source = new DataView(dataSet.Tables[0]);
      DataGrid1.DataSource = source;

The grid will show results from your file system as well as from your database tables. With everything indexed, the result is lightening fast for hundreds of results if not millions.

Many of you might think that there remains a lot to be told. But didn’t I say it was quick and dirty? No pun intended.

To learn more about how to compose your own queries for full-text searches, visit the MSDN website at http://msdn.microsoft.com.

With little logic of your own, you can have a nice search engine which would query different sources differently based on your own requirements.

For example, you can redefine the scope (Deep Copy Traversal, Swallow Copy Traversal ring a bell?) and can do regular expression searches. You are the one to set your own limit.

By Ram Dash.

Reference: http://www.gisremotesensing.com/2011/05/unified-search-engine.html

Posted in .NET, ASP.NET, SQLServer | Etiquetado: , , , | Leave a Comment »

Indexing Server and Sql Server

Posted by kiquenet en 21 junio 2016


EXEC sp_addlinkedserver Web,’Index Server’,’MSIDXS’,’CarenetWeb’

where CarenetWeb is a catalog on the local server.

You can’t build a linked server to a remote IS catalog, but you can query it like this

Select * from openquery(LinkedServerName,’Select DocTitle, Vpath, Characterization from RemoteServerName.RemoteCatalogName..Scope()
where FileName like ”%.txt”’)

Using Query analyzer connected to the Sql server I executed a sp_addlinked server:

EXEC sp_addlinkedserver FileSystem, ‘Index Server’, ‘MSIDXS’, ‘CatalogName’

In the Sql Management console, under Security –> Linked Servers –>Properties, I set the Security tab option to "Be made using this security context:" and assigned a domain account that is a member of the local Administrators group on the remote server where the queried catalog resides.

Then constructed a query like this:

SELECT DocTitle, vpath, Filename, Size, Contents, characterization, PATH, URL, A_Href, rank, write
FROM RemoteServer.CatalogName..SCOPE(”" + this.Scope + "”)
Where FREETEXT(Contents,”" SearchPhrase"”)

Which I was able to then execute while connected with a sql account that was a db_owner.

The Sql Server Agent runs under a domain account, which is a member of the remote server’s Administrators group.

1) the IS server is running locally.
2) the catalog is indexing directories, expand the directories folder and verify there are directories there
3) the directories indexed are both real and virtual (the virtual directories have blue globes on them),
4) there are documents in the physical directories.

Also can you try this code agains your remote catalog – make sure you change this entry to reflect your actual remote server name and catalog

@objIxsso, ‘catalog’, ‘query://remoteservername.remotecatalogname’

create procedure sp_indexServer @input char(255)
declare @HResult int
declare @objIxsso int
declare @RecordSet int
declare @recordCount int
declare @Count int
declare @fields int
declare @path varchar(100)
declare @vpath varchar(100)
declare @doctitle varchar(100)
declare @size varchar(100)
declare @filename varchar(100)
declare @write varchar(100)
declare @rank varchar(100)
declare @hitcount varchar(100)

EXEC @HResult = sp_OACreate ‘Ixsso.query’, @objIxsso Out
EXEC @HResult = sp_OASetProperty @objIxsso, ‘Query’, @input
EXEC @HResult = sp_OASetProperty
@objIxsso, ‘catalog’, ‘query://remoteservername.remotecatalogname’
EXEC @HResult = sp_OASetProperty @objIxsso, ‘Columns’, _
‘path, vpath, doctitle,size,filename,write,rank,hitcount’
EXEC @HResult = sp_OASetProperty
@objIxsso, ‘MaxRecords’, ‘200’
EXEC @HResult = sp_OASetProperty @objIxsso, ‘SortBy’, ‘rank
IF @HResult <> 0
EXEC sp_OAGetErrorInfo @objIxsso
exec @HResult =sp_OAMethod @objIxsso,_
‘CreateRecordSet("nonsequential")’,@recordSet OUTPUT
IF @HResult <> 0
EXEC sp_OAGetErrorInfo @objIxsso
EXEC @HResult = sp_OAGetProperty
@recordSet, ‘RecordCount’,_
@RecordCount output
IF @HResult <> 0
EXEC sp_OAGetErrorInfo @recordset
if @recordcount =0
print ‘No matches found’
print convert (varchar(5),@recordcount) +’ matches found’
SET @Count = 1
WHILE ( @Count <= @RecordCount )
EXEC @HResult = sp_OAGetProperty @RecordSet, ‘fields’, _
@fields out,0
EXEC @HResult = sp_OAGetProperty @fields, ‘Value’, _
@path output
EXEC @HResult = sp_OAGetProperty @RecordSet, ‘fields’, _
@fields out,1
EXEC @HResult = sp_OAGetProperty @fields, ‘Value’, _
@vpath output
EXEC @HResult = sp_OAGetProperty @RecordSet, ‘fields’, _
@fields out,2
EXEC @HResult = sp_OAGetProperty @fields, ‘Value’, _
@doctitle output
EXEC @HResult = sp_OAGetProperty @RecordSet, ‘fields’, _
@fields out,3
EXEC @HResult = sp_OAGetProperty @fields, ‘Value’, _
@size output
EXEC @HResult = sp_OAGetProperty @RecordSet, ‘fields’, _
@fields out,4
EXEC @HResult = sp_OAGetProperty @fields, ‘Value’, _
@filename output
EXEC @HResult = sp_OAGetProperty @RecordSet, ‘fields’,
@fields out,5
EXEC @HResult = sp_OAGetProperty @fields, ‘Value’, @write
EXEC @HResult = sp_OAGetProperty @RecordSet, ‘fields’,
@fields out,6
EXEC @HResult = sp_OAGetProperty @fields, ‘Value’, @rank
EXEC @HResult = sp_OAGetProperty @RecordSet, ‘fields’,
@fields out,7
EXEC @HResult = sp_OAGetProperty @fields, ‘Value’,
@hitcount output
print @path +’, ‘ +@vpath+’, ‘+@doctitle+’, ‘+@size +’, ‘+_
@filename +’, ‘+@write+’, ‘+@rank+’, ‘+@hitcount
EXEC @HResult =sp_OAMethod @RecordSet, ‘MoveNext’
select @count=@Count+1
print @count
EXEC @HResult = sp_OADestroy @fields
IF @HResult <> 0
EXEC sp_OAGetErrorInfo @fields
EXEC @HResult = sp_OADestroy @recordset
IF @HResult <> 0
EXEC sp_OAGetErrorInfo @recordset
EXEC @HResult = sp_OADestroy @objIxsso
IF @HResult <> 0
EXEC sp_OAGetErrorInfo @objIxsso

Posted in .NET, SQLServer | Etiquetado: , , | Leave a Comment »

Indexing Server notes

Posted by kiquenet en 20 junio 2016

Instalar y configurar los servicios de Index Server

De forma predeterminada, los servicios de Index Server no están instalados en un equipo basado en Windows Server 2008

Herramientas administrativas -> Administrador de servidores.
Servicios de Index Server en Servicios de archivo de Windows Server 2003

MMC Servicios de Index Server

Crear un Catálogo:
Nombre: extranetProductos
Ruta  : C:\CatalogosIndexServer\MyApp

Agregar carpetas para incluirlas en el "ámbito del catálogo"
"El ámbito es el conjunto de carpetas que se incluyen y excluyen del catálogo; el ámbito define el contenido que se incluye y excluye del índice"

    Path: \\serv\MyAppCMS\Dev\Products
        Account: DOMAIN\USER_NAS

    Incluir en el índice:
            Sí para incluir la carpeta en el catálogo
            No para excluir la carpeta del catálogo



Como complemento a Index Server, habría que instalar el complemento Adobe PDF iFilter 9 for 64-bit platforms, para la correcta indexación de archivos PDF:

Y después habría que “reescanear” el directorio (All Tasks -> Rescan (Full))

Página ASP.NET de búsqueda personalizada para buscar el catálogo:


A tener en cuenta para IIS 7 sites:
    Install IIS 6 Management Compatibility
    HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\ContentIndex\Catalogs\[Catalog Name]
    Set ContentIndexed metabase property. cscript adsutil.vbs set w3svc/[web site instance]/root/[virtual director]/ContentIndexed 1


Sobre catálogos (catalog.wci) en Index Server


      REG DELETE \\HKLM\SYSTEM\CurrentControlSet\Control\ContentIndex\Catalogs /v <Catalog Name> 

     cisvc.exe = Content Index service or Indexing service
     cidaemon.exe = Indexing Service filter daemon

      Forcing Rescan with Indexing Service on Win2k
      I’ve tried selecting a Full Rescan, Incremental Rescan, and restarting the service.

net stop cisvc
cd "System Volume Information"\catalog.wci\
del *.*
cd \
net start cisvc

Stop the service, then right click the Indexing Service icon in MMC (the one in the tree on the left). Then go All Tasks, Tune Performance. There should be something like "this computer is mostly gonna be used for Index Service". That lets you raise the priority such that it’ll interrupt you. Its default setting, it stops indexing if the computer is doing any work.


Set objISAdm = CreateObject("Microsoft.ISAdm")
Set objCatAdm = objISAdm.GetCatalogByName("MyCatalog")
Set objScopeAdm = objCatAdm.GetScopeByPath("c:\FolderToReScan")

In the last line, TRUE means do a full scan. Change it to FALSE to do an incremental scan.


Página de Referencia en MSDN Microsoft Indexing Service

Note: Indexing Service is no longer supported as of Windows XP and is unavailable for use as of Windows 8. Instead, use Windows Search for client side search and Microsoft Search Server Express for server side search.

Microsoft Index Server Guide


Getting Started

ODBC data sources on Windows Server 2008 x64

Windows Server 2008 x64 has both 32 bit ODBC data sources and 64 bit ODBC data sources. They are managed separately.

Setting up a 32 bit ODBC connection in Windows Server 2008 x64. Start, Run, C:\Windows\SysWOW64\odbcad32.exe

To setup the 64 bit ODBC connection go to: Control Panel, Administrative Tools, Data Sources (ODBC).

Microsoft OLE DB Provider for Microsoft Indexing Service

“Microsoft Indexing Service exposes itself to the developer as an OLE DB provider. Its name is MSIDXS. You can use ADO.NET for querying your Indexing Service”

Connection String: "Provider=MSIDXS;Data Source=myCatalog;Locale Identifier=nnnn;"


Know all the OLE DB Providers registered on your system



Guid for CLSID OLEDB is C8B522D0-5CF3-11ce-ADE5-00AA0044773D

internal static Guid IID_IUnknown = new Guid("00000000-0000-0000-c000-000000000046");

internal static Guid CLSID_DataLinks = new Guid("2206CDB2-19C1-11d1-89E0-00C04FD7A829");

internal static Guid CLSID_OLEDB_ENUMERATOR = new Guid("C8B522D0-5CF3-11ce-ADE5-00AA0044773D");

internal static Guid CLSID_MSDASQL_ENUMERATOR = new Guid("C8B522CD-5CF3-11ce-ADE5-00AA0044773D");

Guid guidOleDbEnum = new Guid("{C8B522D0-5CF3-11ce-ADE5-00AA0044773D}");

Type type = Type.GetTypeFromCLSID(guidOleDbEnum);

using (OleDbDataReader rdr = OleDbEnumerator.GetEnumerator(type))


while (rdr.Read())

Console.WriteLine("{0} – {1}", rdr["SOURCES_NAME"], rdr["SOURCES_DESCRIPTION"]);



Indexing Service Administration Type Library (c:\Windows\system32\ciodm.dll) <- used for admin functions to add/remove catalogs and scopes

ixsso Control Library (c:\Windows\system32\ixxso.dll) <- used to run queries against the catalogs

Microsoft ActiveX Data Objects Recordset 2.8 Library (c:\Program Files\Common Files\System\ado\msdaor15.dll) <- used to read query records

There are two methods for querying the Indexing Service:

1) IXSSO.dll contains the Indexing Service Query Automation Objects – Query and Utility

This is also refered to as the Query Helper API of the Indexing Service, and runs as a user process.  The Query object can be used to create and execute a query, which causes the Indexing Service OLE DB provider to returnan ADO Recordset object.  This then needs to be converted to an .Net compatible object such as the OleDB DataTable.

2) MSIDXS.dll contains the OLE DB Provider for Indexing Service object.  This object can be used to create a oledb connection object and dataadapter object.  All settings for the search are done in the connection string and in query string using special SQL extensions for Indexing Service.

If using the IXSSO.dll in VB.Net the declarations look like…

Dim IndexQueryHelper As Object = CreateObject("ixsso.Query")

Dim IndexUtilities As Object = CreateObject("ixsso.Util")

Dim IndexAdminHelper As Object = CreateObject("Microsoft.ISAdm") ‘ciodm.dll

Also keep in mind that when using the ixsso.dll the results are returned as an ADO recordset, which is not directly compatible with .Net with utilizes the OLE DB assembly.  To convert the result try…

Dim ODA As OleDbDataAdapter = New OleDbDataAdapter

Dim SearchResults As DataTable = New DataTable("FilesList")

ODA.Fill(SearchResults, IndexQueryHelper.CreateRecordSet("sequential"))

Troubleshooting: https://support.microsoft.com/en-us/kb/954819


Permissions for MSIDXS

Group or user names list: SYSTEM, ASPNET

[HKEY_CLASSES_ROOT\CLSID\{F9AE8980-7E52-11d0-8964-00C04FD611D7}\OLE DB Provider]

@="Microsoft OLE DB Provider for Indexing Service"



Proveedor Microsoft OLE DB para servicios de Index Server



Microsoft OLE DB Provider for Microsoft Index Server and Microsoft Site Server Search

version 5.0.1781.3


SQL Server OLE DLL del proveedor (Sqloledb.dll) como ejemplo

1.En el símbolo del sistema, cambie a la carpeta C:\Program Files\Common Files\System\Ole DB.

2.En el símbolo del sistema, escriba el comando siguiente: regsvr32 sqloledb.dll

Posted in ASP.NET, Windows | Etiquetado: , , , , | Leave a Comment »

Performance DataAccess Oracle and ODP.NET

Posted by kiquenet en 18 junio 2016

FetchSize (OracleDataReader)

A number of blogs and sites mention increasing FetchSize of OracleDataReader to improve performance when fetching big volumes of data (e.g. thousands of rows). There are some documented experiments with exact numbers on this like:


Table had a little more than 155K rows and was size of 31 MB. Yet, it took more than 5 minutes to complete for data adapter to fill data table.

The cause of the problem was the number of round trips client need to accomplish to get all the rows from database. If you can reduce the number of round trips, you can increase the fetch size so that in each turn command object will fetch more rows from database. Here is how it’s impletemented:

using (OracleConnection conn = new OracleConnection())
     OracleCommand comm = new OracleCommand();
     comm.Connection = conn;
     comm.FetchSize = comm.FetchSize * 8;
     comm.CommandText = "select * from some_table";

          OracleDataAdapter adap = new OracleDataAdapter(comm);
          System.Data.DataTable dt = new System.Data.DataTable();

Notice the line with blue font, fetch size of command object increased by 8 times its default which is 128 KB at ODP.Net OracleDataReader also has the FetchSize property. By increasing fetch size, you increase the cache size in memory to fetch rows.

What we gained is up to 96% performance improvement. Here are some timings with different fetch sizes:

Fetch Size             Timing (MI:SS.FF3)
Default (128 KB)       05:20.290
Default x 8 (1 MB)     00:52.941
Default x 32 (4 MB)    00:26.008
Default x 64 (8 MB)    00:12.409

Reference: http://metekarar.blogspot.com.es/2013/04/performance-improvement-for-odpnet.html

Strangely, unless the connection pooling is explicitly disabled (e.g. in the connection string), the increase/decrease of FetchSize stops having any effect. When the pooling is disabled though, it’s clear that the FetchSize can improve the performance (the more records, the bigger the effect).

It turns out that this unexpected behavior is limited to the following conditions: 1) The SELECT statement is exactly the same 2) The pooling is ON 3) The self-tuning is ON

Only in those conditions the first time the FetchSize is set, it gets somehow cached by ODP.NET and attempts to change it don’t work.

References Mark Williams

string with_pool =
  "User Id=hr;
   Data Source=oramag;

Connection Pooling Overview

Making specific connection pooling parameter recommendations is difficult, because workloads can vary greatly. One general recommendation is to have a sufficiently high minimum pool size. I have often seen connection pools drained to the minimum during a lull in activity. When activity picks up rapidly, the connection pool has to create connections quickly rather than use connections in the pool. In most cases, the application server reduces the number of connections and remains relatively idle. Thus, there is no reason to have a low minimum pool size.

Controlling Fetch Size

Retrieving data from the database tier to the middle tier (or client tier) is one of the most expensive operations with respect to performance. If the end user consumes a lot of data, you will want your application to minimize the number of round-trip data fetches.

By default, ODP.NET will read 64KB of data from a result set at a time. You change this value by setting the FetchSize attribute for an OracleDataReader object. However, rather than arbitrarily setting the fetch size, ODP.NET provides the ability to control the number of rows that are fetched from a result set per round trip. The two properties you use to do this are RowSize and FetchSize.

RowSize is a property of the OracleCommand object, and the value for this property is assigned by ODP.NET when the statement associated with the command object is executed. You explicitly set the value of the FetchSize property. For example, if you want to retrieve 100 rows at a time from the result set per round trip, you set the FetchSize property:

dr.FetchSize = cmd.RowSize * 100;

if you are tempted to fetch only a single row at a time to "save resources and increase performance" (I have sometimes heard this advice), you will actually be dramatically reducing, rather than increasing, runtime performance. In addition, for this set of data, there is not much performance increase when fetching more than 1,000 rows at a time. Of course, this is also a narrow data set (with only two small columns). Apply this technique to a result set representative of your environment to determine optimal values for setting your fetch size.

Of course, there is a cost if the fetch size is arbitrarily large. More client-side memory and processor cycles will be needed to store and manage a larger amount of data. The goal is to find a high-performing balance between the number of round trips and the amount of data retrieved per trip.

Statement Caching

You enable the statement caching feature by setting the Statement Cache Size connection string parameter. The default value of this parameter is 0, meaning that statement caching is disabled by default. You set this parameter to the number of statements you plan to keep cached during your application’s lifetime. For example, if you plan to cache 16 statements, you will set this value to 16. ODP.NET will then cache the 16 most recently used statements. The 17th most recently used statement is aged out of the cache by the last 16 unique statements.

With statement caching enabled, ODP.NET will automatically cache any statement you execute. If you have enabled statement caching and you do not want to place a statement in the cache, set the AddToStatementCache property on OracleCommand to false:

string constr =
  "User Id=hr;
   Data Source=oramag;
   Statement Cache Size=1";


create or replace procedure get_sales (p_refcur out sys_refcursor) is


  — open the cursor using the passed in ref cursor
— sys_refcursor is a built in type

  open p_refcur for   select  *   from    sales;


 // connection string — be sure to adjust for your environment
public const string constr = "User Id=sh; Password=sh; Data Source=otndemo; Pooling=false; Enlist=false";

      // enable extended sql tracing
      // this can be used to verify the number of rows fetched
      // uncomment to create trace file in user_dump_dest directory
      // EnableExtendedTrace(con);

static void EnableExtendedTrace(OracleConnection con)
    // create, setup, and execute command to enable extended sql trace
    OracleCommand cmd = con.CreateCommand();

    cmd.CommandText = "alter session set events ‘10046 trace name context forever, level 4’";

    // clean up

      // do the fetch test
      // passing 0 for "rows" will use default fetch size of 64k
      FetchTest(con, 100);
      FetchTest(con, 1000);
      FetchTest(con, 10000);
      FetchTest(con, 0);

static void FetchTest(OracleConnection con, int rows)


      // used to track execution duration
      DateTime timeStart;
      DateTime timeEnd;
      double totalSeconds;

      // create and setup command to call stored procedure
      OracleCommand cmd = con.CreateCommand();
      cmd.CommandText = "get_sales";
      cmd.CommandType = CommandType.StoredProcedure;

      // create the ref cursor parameter and add to command parameter collection
      OracleParameter prm = new OracleParameter("p_refcur", OracleDbType.RefCursor, ParameterDirection.Output);

      // get the data reader
      OracleDataReader dr = cmd.ExecuteReader();

      // There are 3 steps in getting the m_rowSize property value…
      // Step 1 – get the data reader type
      Type type = dr.GetType();

      // Step 2 – get the "m_rowSize" field info
      FieldInfo fi = type.GetField("m_rowSize", BindingFlags.Instance | BindingFlags.NonPublic);

      // Step 3 – get the value of m_rowSize for the dr object
      int rowSize = (int)fi.GetValue(dr);

      // if rows is greater than 0 use to set fetch size,
      // otherwise use default size (64k)

      if (rows > 0)
        // set the fetch size on the dr object using the row size
        dr.FetchSize = rowSize * rows;
      // capture test start time
      timeStart = DateTime.Now;

      // simply loop forcing the entire result set to be fetched
      while (dr.Read())      {      }

      // capture test end time
      timeEnd = DateTime.Now;

      // calculate total seconds for this test
      totalSeconds = timeEnd.Subtract(timeStart).TotalSeconds;

      // display time used for test
      Console.WriteLine("Number of rows = {0,5}: {1,8:###.0000} total seconds, fetch size = {2,9:###,###,###} bytes.", rows, totalSeconds, dr.FetchSize);

      // clean up



From Alex Keh (Oracle)
This video describes ODP.NET best practices for performance:

The slides are here:

The code samples for many of the performance patterns are available in the following tutorials:

Oracle SQL High Performance tuning
Oracle Performance Survival Guide
Troubleshooting Oracle Performance


Bind variables

Array Select

Array insert



Connection Pool (CP)

    Ensure you have enough connections in CP – better to have many than too few.
    OS-authenticated CP available with ODP.NET 11g
    Keep a steady state of CP – never destroy or create large number of connections
    Close/Dispose connections explicitly – don’t rely on garbage collector
    You can monitor CP performance counters using ODP.NET and higher

Bind Variables

    Always use Bind Variables. Using Bind Variables will prevent reparsing of frequently executed statements. Literal value changes in commands force a reparse. Reparsing is fairly expensive CPU operation and requires shared pool locks.

Statement Caching

    Using statement caching retains parsed statement in shared pool.
    Cursor stays open on client side and Metadata remains on client
    Best used/works with Bind Variables
    Caching works with and caches the last 10  executed statements.
    Developer can choose which statement to cache.

Statement Cache Size=0 (no caching)

Statement Cache Size=1 (caching)

With ODP.NET cache size dynamically changes at runtime and provides automatic optimization/self-tuning. Self-tuning is enabled by default and no code changes are required.


Data Retrieval

You can control how much data is retrieved from the database per round-trip. Too much data can cause excessive client-side memory used and too little may cause additional round-trips. You can use OracleCommand.RowSize and OracleDataReader.FetchSize to control the result. FetchSize can be set as multiple of RowSize and RowSize can be dynamicall populated after statement execution.

FetchSize = RowSize X 1
FetchSize = RowSize X 100

Mass Data Movement with Arrays

    PL/SQL associative arrays can be used to pass large amounts of data between .NET and DB of the same data type.
    If you are executing the same statement multiple times, you could use a parameter array binding.

Statement Batching

    You can execute multiple commands in one DB round-trip using OracleDataAdapter.UpdateBatchSize.
    You can use anonymous PL/SQL for disparate or similar statements

ODP.NET DataTypes

    Try and avoid unnecessary datatype conversions between .NET and the DB
    Use OracleParameter.Dbtype

    public void CreateOracleDbParameter()
        OracleParameter parameter = new OracleParameter();
        parameter.ParameterName = "pDName";
        parameter.DbType = DbType.String;
        parameter.Value = "ENGINEERING";
        parameter.SourceColumn = "DName";

Use REF Cursors

With REF Cursors, you can retrieve data as needed and control data retrieved via FetchSize. You can fill a DataSet with just a portion of the REF cursor result. You can pass REF cursors back as input stored procedure parameters. Use OracleRefCursor class.

Oracle Performance Tuning in Visual Studio

    You can tune ad-hoc SQLs in query window

    Tune bad SQL using Oracle Performance Analyzer – requires:

        SYSDBA privilege

        database license for Oracle Diagnostic Pack

        database license for Oracle Tuning Pack


These are built into Oracle Database 10g and are invaluable for diagnosing Performance issues.

AWR – Automatic Workload Repository

    Evolution of statspack

    builtin repository

    captures performance stats at regular intervals

ADDM – Automatic Database Diagnostic Monitor

    Analyses AWR stats and generates recommendations

AWR and ADDM nodes are now available in Visual Studio.




using(OracleConnection conn = new OracleConnection(@"DataSource=source;User Id=username;Password=password;"))
        OracleCommand cmd = new OracleCommand();
        cmd.Connection = conn;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "StoredProcedureName";
        cmd.Parameters.Add("REF_CURSOR_SAMPLE",OracleType.RefCursor).Direction =  ParameterDirection.Output;
        OracleDataReader reader = cmd.ExecuteReader();
        FieldInfo fieldInfo = reader.GetType().GetField("m_rowSize", BindingFlags.Instance | BindingFlags.NonPublic);
        int cmdRowSize = (int)fieldInfo.GetValue(reader);
        reader.FetchSize = cmdRowSize * noOfRowsExpected;
             // do your logic here

Troubleshooting Oracle Performance

ODP.NET. The default fetch size of ODP.NET (65,536) is defined in bytes, not rows. You can change this value through the FetchSize property provided by the  OracleCommand and OracleDataReader classes. The following code snippet is an example of how to set the value for fetching 100 rows.

Notice how the RowSize property of the OracleCommand class is used to compute the amount of memory needed to store the 100 rows. The C# program in the RowPrefetching.cs file provides a complete example:

sql = "SELECT id, pad FROM t";
command = new OracleCommand(sql, connection);
reader = command.ExecuteReader();
reader.FetchSize = command.RowSize * 100;
while (reader.Read())
    id = reader.GetDecimal(0);
    pad = reader.GetString(1);
    // process data

As of ODP.NET version,  you can also change the default fetch size through the following registry entry (<Assembly_Version> is the full version number of Oracle.DataAccess.dll)




  pkg_emp.get_emps (l_emp_cur);
  l_cur_no := DBMS_SQL.TO_CURSOR_NUMBER (l_emp_cur);
  DBMS_SQL.DESCRIBE_COLUMNS(l_cur_no, l_col_cnt, l_rec_tab);

  FOR i IN 1..l_col_cnt
     DBMS_OUTPUT.PUT_LINE(‘Column ‘ || l_rec_tab(i).col_name || ‘ max length of ‘ || TO_CHAR(l_rec_tab(i).col_max_len));



      <add name="DllPath" value="N:\ORACLE\ORA11odacR5"/>
      <add name="FetchSize"             value="65536"/>
      <add name="PromotableTransaction" value="promotable"/>
      <add name="StatementCacheSize"    value="0"/>
      <add name="TraceFileName"         value="%TEMP%\odpnet.trc"/>
      <add name="TraceLevel"            value="0"/>
      <add name="TraceOption"           value="0"/>
  <!– Here we’re telling .NET framework two things:
       – which version of Oracle.DataAccess.dll we want to be used
       – and from where exactly it should load the assembly.
       Any version of Oracle.DataAccess.dll between and
       will be replaced by
       Note that publicKeyToken is "hash" dedicated to Oracle Corp. but might
       change in the future. We checked the token against GAC.
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
        <publisherPolicy apply="no" />
        <assemblyIdentity name="Oracle.DataAccess"
                          culture="neutral" />
        <bindingRedirect oldVersion=""

:: RunOraDUAL.bat
:: Oracle11g [] Instant Client with ODAC
:: Batch routine to launch OraDUAL.exe from newtork drive.
:: ———————————————————
title Oracle11g Instant Client – ODAC
start OraDUAL.exe
:: End

Posted in .NET, DataAccess, Oracle, Performance | Etiquetado: , , , | Leave a Comment »

Inserciones con autonuméricos y batch

Posted by kiquenet en 20 abril 2016

Tabla temporal



Id int,

Nombre varchar(50)


Inserción Batch con OUTPUT (inserted) INTO

INSERT INTO Personas(Nombre)

OUTPUT inserted.Id, inserted.Nombre

INTO @Temp


INSERT INTO Mascotas(Nombre, PersonaId)

SELECT 'Mascota de ' + Nombre, Id

FROM @Temp;


Posted in SQLServer | Etiquetado: , , | Leave a Comment »

TroubleShooting Oracle–PLSQL: Locks

Posted by kiquenet en 30 noviembre 2015

Issue: Can’t compile a stored procedure when it’s locked

ETL process (dtexeui.exe, DtsDebugHost.exe) call many times to a Package, like it is being used, restricting me from compiling it (the package). 

Monitor de Sesiones de PLSQL Developer

Consultas útiles:

select * from DBA_DDL_LOCKS where name like ‘%PNMKT_CLIENTES%’

select    * from    v$session x where machine like ‘%DS0366%’

Question:  In our development environment we need to re-compile packages as we change our code. Once in a while a session will hold onto a package, like it is being used, restricting me from compiling it.   Is there a way to find out what SID is holding on to the package in order for me to kill it?

Answer:  You can query v$session and join into v$sql to see session and SQL information.  This will show the SID for the session that is holding a PL/SQL package:

   v$session x, v$sqltext y
   x.sql_address = y.address
and  machine like ‘%DS0366%’
–   y.sql_text like ‘%PNMKT_CLIENTES%’;

Esta query te dice los objetos que están bloqueados, de tablas y filas:

decode(L.TYPE,’TM’,’TABLE’,’TX’,’Record(s)’) TYPE_LOCK,
decode(L.REQUEST,0,’NO’,’YES’) WAIT,
concat(‘ ‘,s.PROGRAM) PROGRAM,
FROM v$lock l,dba_objects o,v$session s
AND l.TYPE in (‘TM’,’TX’)

Esta otra te dice si existe algún paquete pillado (no bloqueado):

select /*+ CHOOSE */ a.sid, a.serial#, A.INST_ID,a.username, a.username "DB User",
a.osuser, a.status, a.terminal, a.type ptype, b.owner, b.object, b.type, a.USERNAME "DB User"
from gv$session a, gv$access b
where a.sid=b.sid and a.inst_id = b.inst_id
and b.type<>’NON-EXISTENT’
and (b.owner is not null) and (b.owner<>’SYSTEM’) and (b.owner<>’SYS’)

Si el STATUS te aparece como ACTIVE, ese paquete si lo intentas compilar te dará TIMEOUT y no podrás.


— view all currently locked objects:
SELECT username U_NAME, owner OBJ_OWNER,
object_name, object_type, s.osuser,
  0, ‘Not Blocking’,
  1, ‘Blocking’,
  2, ‘Global’) STATUS,
    0, ‘None’,
    1, ‘Null’,
    2, ‘Row-S (SS)’,
    3, ‘Row-X (SX)’,
    4, ‘Share’,
    5, ‘S/Row-X (SSX)’,
    6, ‘Exclusive’, TO_CHAR(lmode)
FROM gv$locked_object v, dba_objects d,
gv$lock l, gv$session s
WHERE v.object_id = d.object_id
AND (v.object_id = l.id1)
AND v.session_id = s.sid
ORDER BY username, session_id;
— list current locks
SELECT session_id,lock_type,
FROM dba_lock l
WHERE lock_type
NOT IN (‘Media Recovery’, ‘Redo Thread’);

— list objects that have been
— locked for 60 seconds or more:

SELECT SUBSTR(TO_CHAR(w.session_id),1,5) WSID, p1.spid WPID,
SUBSTR(s1.username,1,12) "WAITING User",
SUBSTR(s1.osuser,1,8) "OS User",
SUBSTR(s1.program,1,20) "WAITING Program",
s1.client_info "WAITING Client",
SUBSTR(TO_CHAR(h.session_id),1,5) HSID, p2.spid HPID,
SUBSTR(s2.username,1,12) "HOLDING User",
SUBSTR(s2.osuser,1,8) "OS User",
SUBSTR(s2.program,1,20) "HOLDING Program",
s2.client_info "HOLDING Client",
o.object_name "HOLDING Object"
FROM gv$process p1, gv$process p2, gv$session s1,
gv$session s2, dba_locks w, dba_locks h, dba_objects o
WHERE w.last_convert > 60
AND h.mode_held != ‘None’
AND h.mode_held != ‘Null’
AND w.mode_requested != ‘None’
AND s1.row_wait_obj# = o.object_id
AND w.lock_type(+) = h.lock_type
AND w.lock_id1(+) = h.lock_id1
AND w.lock_id2 (+) = h.lock_id2
AND w.session_id = s1.sid (+)
AND h.session_id = s2.sid (+)
AND s1.paddr = p1.addr (+)
AND s2.paddr = p2.addr (+)
ORDER BY w.last_convert DESC;

References :


Oracle Tuning Power Scripts

http://it-ebooks.info/search/?type=title&q=Oracle Tuning

Posted in Oracle | Etiquetado: , | Leave a Comment »

Firefox: Load Page Faster

Posted by kiquenet en 21 octubre 2015

Update Firefox. Many optimization tweaks from the past are now included by default in Firefox. To take advantage of them, update to the latest version. This happens automatically when you check your version number.

Back up your preferences file. These settings can cause bugs and slowdown for some users. You can usually revert them without a problem, but back up the file just in case so you can restore your old preferences:

  • Open a new tab and enter about:support in the address bar.
  • Look for "Profile Folder" and click Show Folder next to it (Show in Finder on a Mac).
  • Go one level up from the folder that opens. You should see a folder with a string of letters and number, ending in ".default."
  • Right-click this folder and choose "Copy," then right-click in a backup location and select "Paste."

Try one setting at a time. These settings are intended for advanced users, and could cause problems with your add-ons. It’s best to change one setting at a time so you can test the effect.

  • You can find tools online to test the speed of your browser precisely.

Adjust connections per server. Your browser limits the number of simultaneous connections to a single server. Upping this limit will make a noticeable change to pages with many images or videos, if your bandwidth can handle it. Increasing this too much is considered bad etiquette, and may get you banned from a server, but you have a little wiggle room:

  • Search for network.http.max-persistent-connections-per-server and double-click the Value. Increase this to a maximum of 10.[1] Some users prefer 8 to be on the safe side.
  • Search for network.http.max-connections. Set this value to 256, if it isn’t already.[2]

Disable animations. Firefox displays small animations when opening or closing tabs. This is usually not an issue, but you can avoid some hang-ups if you tend to open or close many tabs at once:[3]

  • Set browser.tab.animate to False.
  • Set browser.panorama.animate_zoom to False.

Consider disabling prefetching. Prefetching loads pages before you visit them, guessing which links you’ll click on. When working correctly, this should only use idle browser time, and will actually increase load speed. If you have unusually slow load speeds, a buggy prefetch could be the issue. Make the following changes to disable both forms of prefetch, then reverse them if there’s no speed up:[4]

  • Change network.dns.disablePrefetch to True.
  • Change network.prefetch-next to False.
  • Change the value of network.http.speculative-parallel-limit to 0.

Toggle hardware acceleration and WebGL. These functions use your graphics card to speed up certain functions, especially loading videos. However, this can cause slow load times or blurry text, especially with older operating systems or graphics cards. Try watching videos with these settings on and off to see which works best for you:[5]

  • Change webgl.disabled to True or False.
  • Visit about:preferences#advanced in a new tab. Check or uncheck "Use hardware acceleration."
  • Unlike most setting changes, you may need to restart Firefox for these to take effect.[6]

Install an ad blocker. On many web pages, advertisements take up a large fraction of the load time. Install Adblock Plus or another ad-blocking add-on to prevent these ads from loading.

  • Many web hosts rely on ads for most of their revenue. Consider disabling ad block on sites you’d like to support.

Start Firefox in safe mode. Click the menu icon (three horizontal bars), then the help icon (?), then Restart with add-ons disabled. If Firefox runs much faster in Safe Mode, a faulty add-on is slowing you down.

Disable add-ons. Enter about:addons in the address bar, or click the menu icon (three horizontal bars) and select Add-ons. Disable one add-on at a time, and browse for a while to see if it speeds you up. Visit the same page to remove the add-on permanently or re-enable it, depending on your test.

Switch to the default theme. If you have a custom theme, it could be slowing down your browser. Visit the Appearance tab on the add-ons page, and switch to the default theme.

Minimize memory usage. If you’ve just closed a bunch of tabs, Firefox might lag for a while until it deletes the contents of those tabs from its memory. Get rid of them right away by visiting about:memory and clicking Minimize memory usage.[7]

Adjust cache. The cache is another feature that speeds up browsing in general, but can slow you down if it gets too full for your hard drive. To adjust the cache size, visit about:preferences#advanced, click the Network tab, and check "Override cache management." Increase the cache if you have a fast drive with plenty of space, and reduce it to about 250MB if you have a slow or mostly full hard drive.

  • Clearing your cache every couple months is a good idea, or whenever your browser gets unusually slow. You should also clear the cache before reducing its size.

Reset Firefox. If you are experiencing major slowdowns on a broadband connection, you might have to reset Firefox to remove a buggy add-on or setting change. This will delete all your add-ons, themes, and download history, and return your settings to default. Visit about:support and click Refresh Firefox, or see this article for more detailed instructions.

Understand pipelining. Pipelining allows Firefox to open more than one connection to a server. This will only help if you have a good broadband connection. Even then, this only gives a small speed boost, and can even cause slight slowdowns or errors. The difference in results are probably dependent on the way the web page is structured, so give it a try and see if it helps for your most visited websites.[8][9]

Visit about:config. Open a new Firefox tab and enter about:config in the address bar.

Enable pipelining. Search for network.http.pipelining using the bar at the top of the page. This entry should be set to "Status: default" and "Value: false." Double-click that line to change it to "Status: user set" and "Value: true."

Adjust other settings (optional). There are quite a few related settings you can change as well. Adjusting most of these is not recommended unless you know what you are doing. Here are a couple you could try:

  • network.http.pipelining.maxrequests was capped at 8 for many years, but now the limit (and the default) is 32. Raising this number could cause errors. Lowering it will reduce speed but free up your bandwidth a little.
  • Enabling network.http.pipelining.aggressive doubles down on the tactic: when it works, you’ll be even faster, and when it doesn’t, you’ll be much slower.[10]
  • If you route all your internet use through a proxy, you’ll need to enable network.http.proxy.pipelining. (You’ll need to do a new search to find this.)

Switch to secure-only pipelining if you encounter problems. If pipelining is slowing you down, or causing errors in your web pages, return it to the default "false" position. You can still implement pipelining for secure websites by enabling network.http.pipelining.ssl. Most pipeline-related errors come from proxy servers, which are not an issue with secure connections.[11]

  • Despite the problems with "unsecure" connections, pipelining does not open you up to any security risks.


Posted in Productividad | Etiquetado: , | Leave a Comment »

Experiencies NLB, WSDL, SSL, HTTPS

Posted by kiquenet en 14 abril 2015

El sitio web está en 2 servidores de preproducción: preiis01 y preiis02. No está configurado SSL.

A la url http://preiis01/ServicioPyS/Service.svc?wsdl puedo acceder e invocar al servicio y funciona bien (todo por http)

La url de https://pruebaslba.xxxx.net/ServicioPyS/Service.svc?wsdl se refiere al Balanceador (F5 creo que es), que permite acceso desde fuera (acceso público), y ahí parece está configurado el SSL. Los detalles no los conozco, es de ámbito de sistemas.

El problema es relativo al WSDL


<wsdl:service name="ActualService">

<wsdl:port name="ActualServiceEndPoint"binding="i0:ActualServiceEndPoint">

<soap:addresslocation="http://preiis01/ServicioPyS/Service.svc/Service.svc" />



  Con la url del WSDL del balanceador el problema que la parte de soap:address location no la genera bien (la pone como http)


<wsdl:service name="ActualService">

<wsdl:port name="ActualServiceEndPoint"binding="i0:ActualServiceEndPoint">

<soap:addresslocation="http://pruebaslba.xxxx.net/ServicioPyS/Service.svc/Service.svc" />



   Por eso la solución rápida que encontré fue modificar el WSDL (singlefile) manualmente  (la parte de soap:addresslocation y poner httpS)  y ponerlo en https://pruebaslba.xxxx.net/ServicioPyS/WSDL/ServiceWsdlModificado.Single.wsdl


<wsdl:service name="ActualService">

<wsdl:port name="ActualServiceEndPoint"binding="tns:ActualServiceEndPoint">




Seguramente hay una forma de hacerlo sin tener que hacer esa modificación manual del WSDL, desconozco esos temas si el sitio web en el IIS no es SSL, y sin embargo en la llamada desde internet a través de Load Balancer se hace a través de SSL.

hay temas de “SSL pass-through” y load balancer. Yo lo desconozco



Error inicial  hasta que se descubrió que sitio IIS no tiene SSL habilitado.


Could not find a base address that matches scheme https for the endpoint with binding WSHttpBinding. Registered base address schemes are [http].


Could not find a base address that matches scheme https for the endpoint with binding BasicHttpBinding. Registered base address schemes are [http].

Googleando, he visto distintas configuraciones e ido probando alternativas para serviceMetadata, bindings, baseAddresses, ..

<serviceMetadata httpGetEnabled="false o true" httpsGetEnabled="true" />



        <binding name="SecureTransportOnly">

          <security mode="Transport">

            <transport clientCredentialType="None" proxyCredentialType="None" realm="" />



        <binding name="NoSecure">

          <security mode="None"><transport clientCredentialType="None"/></security>




        <binding name="SecureTransportOnly">

          <security mode="Transport">

              <transport clientCredentialType="None" proxyCredentialType="None" realm="" />












<endpoint address="mex" binding="mexHttpBinding o mexHttpsBinding" contract="IMetadataExchange"/>

Hasta probé 



            <add  baseAddress="https://pruebaslba.xxxx.net o https://pruebaslba.xxxx.net/ServicioPyS"/>




            <dns value="localhost" />



HOW-TO Configure an IIS-hosted WCF service with SSL

WCF Service namespaces



public const string NAMESPACE = "https://ServicioPyS.xxxx.es";

[ServiceContract(Name = "IActualService", Namespace = Constants.NAMESPACE)]

public interface IActualService

[System.Runtime.Serialization.DataContract(Namespace = Constants.NAMESPACE)]

public class Cabecera

[System.Runtime.Serialization.DataContract(Namespace = Constants.NAMESPACE)]

public enum Respuesta

bindingNamespace and WSDL

Info relativa a configuración SSL, HTTPS y bindings WCF


WCF Security:authentication based on Username/Password – Part II


Otras referencias WCF Security






Could not find a base address that matches scheme https for the endpoint with binding WebHttpBinding. Registered base address schemes are [http]



Posted in .NET | Etiquetado: , , | Leave a Comment »

WCF Quotas and IIS Limits

Posted by kiquenet en 14 abril 2015

Exceptions about Quotas and limits using WCF for uploading files or another method:

System.ServiceModel.EndpointNotFoundException: There was no endpoint listening at http://localhost:8080/oms/FileService.svc that could accept the message. This is often caused by an incorrect address or SOAP action. See InnerException, if present, for more details. —> System.Net.WebException: The remote server returned an error: (404) Not Found.

System.InsufficientMemoryException: Failed to allocate a managed memory buffer of 279620368 bytes. The amount of available memory may be low. —> System.OutOfMemoryException: Exception of type ‘System.OutOfMemoryException’ was thrown.

The maximum message size quota for incoming messages (65536) has been exceeded. To increase the quota, use the MaxReceivedMessageSize property on the appropriate binding element.

Review “quotas” of Bindings

I can view System.ServiceModel.Diagnostics.MessageNotLoggedQuotaExceeded in the svclog file.

No se registró el mensaje porque el tamaño supera la cuota configurada
Message not logged because its size exceeds configured quota

Request Limits <requestLimits>
Specifies the maximum length of content in a request, in bytes.
maxAllowedContentLength has type uint, its maximum value is 4,294,967,295 bytes = 3,99 gb

        <requestLimits maxAllowedContentLength="209715200" />

209715200 are 200 MB


In addition to maxAllowedContentLength, the maxRequestLength on the server side in your WCF confgiration needs to be increased as well.

  <!–Increase ‘maxRequestLength’ to needed value: 100mb (value is in kilobytes)–>
  <httpRuntime maxRequestLength="102400"/>

the default web site doesn’t override it using:

"%WINDIR%\System32\inetsrv\appcmd.exe" list config "Default web site" -section:requestFiltering

Note about SubStatusCode:

WebExceptionStatus – ProtocolError = 7
HTTP Substatus
404.13 Content Length Too Large
404.14 URL Too Long
404.15 Query String Too Long
404.10 Request Header Too Long

WebException hasn’t SubStatusCode, only HttpException

Useful Troubleshooting Failed Requests – Tracing in IIS

using (ServerManager serverManager = new ServerManager())
   Configuration config = serverManager.GetWebConfiguration("Default Web Site");
   ConfigurationSection requestFilteringSection = config.GetSection("system.webServer/security/requestFiltering");
   ConfigurationElement requestLimitsElement = requestFilteringSection.GetChildElement("requestLimits");
   ConfigurationElementCollection headerLimitsCollection = requestLimitsElement.GetCollection("headerLimits");

   ConfigurationElement addElement = headerLimitsCollection.CreateElement("add");
   addElement["header"] = @"Content-type";
   addElement["sizeLimit"] = 100;


EndpointNotFoundException with InnerExcception (WebException) hasn’t SubStatusCode, only HttpException:

catch (System.ServiceModel.EndpointNotFoundException ex)
               var iex = ex.InnerException;
               if (iex is System.Net.WebException)
                   var msg = "no http status code available";
                   var wex = (iex as System.Net.WebException);
                   if (wex.Status == System.Net.WebExceptionStatus.ProtocolError)
                       var response = wex.Response as System.Net.HttpWebResponse;
                       if (response != null)
                           msg = "HTTP Status Code: " + (int)response.StatusCode;

Bit Calculator








Posted in .NET | Etiquetado: , , , , | Leave a Comment »

Modify machine.config (config files) using Powershell

Posted by kiquenet en 22 enero 2015


$config = [System.Configuration.ConfigurationManager]::OpenMachineConfiguration() 

"{0,-25}     {1,25}"  -f "   Name", "   Allow Definition"
"{0,-25}     {1,25}"  -f "   —-", "   —————-"
$i = 0 

foreach ($section in $config.Sections) 

     "{0,-25}     {1,25}" -f $section.SectionInformation.Name,$section.SectionInformation.AllowExeDefinition 

"Total number of sections: {0}" -f $i

get-content and DocumentElement

$xml = [xml](get-content $machineConfig)
$xml.Save($machineConfig + "_$currentDate")
$root = $xml.get_DocumentElement()
$system_web = $root."system.web"
if ($system_web.machineKey -eq $nul) {
$machineKey = $xml.CreateElement("machineKey")
$a = $system_web.AppendChild($machineKey)
$a = $xml.Save($machineConfig)


if (!$origXml.configuration.’system.serviceModel’.bindings)
    $tempXmlDoc = new-object System.Xml.XmlDocument
    $newNode = $origXml.ImportNode($tempXmlDoc.DocumentElement, $true)



Posted in PowerShell, Scripts | Etiquetado: , , | Leave a Comment »