jueves, 4 de noviembre de 2010

Enviar ficheros por FTP usando Powershell

Con este script de Powershell podemos automatizar el envío por FTP de todos los ficheros que existan en un directorio y cumplan con un determinado filtro (por ejemplo, todos los .txt).

Me he basado en este post para hacerlo: How to upload a file, the PowerShell way

Actualización 17/11/2010: añado $R.proxy = $null para que no de problemas con proxys HTTP :)


#Directorio del que obtenemos los ficheros
$source_folder="c:\temp\"

#Filtro que deben cumplir los ficheros que se envían
$files_filter=$source_folder + "*.txt"

#Archivo donde se guardará el log del proceso
$log_file="c:\FTP.log"

#URL de nuestro servidor FTP
$FTP_url="ftp://myserver.ftp/destino/"
$FTP_user="usuario"
$FTP_password="password"

# Obtenemos todos los ficheros del directorio que cumplan con el filtro
foreach ($filename in Get-Childitem $files_filter)
{
#Path completo del fichero de origen que se enviará por FTP
$Source=$source_folder + $filename.name

#Guardamos una línea en el archivo de logs
$DATE=Get-Date
Add-Content $log_file "[INFO]: $DATE - $Source"

#Path completo del fichero de destino
$Destination=$FTP_url + $filename.name

#Aquí comienza el control de errores, se verá más abajo con el "trap"
&{
#Usamos el metodo estático Create para crear una petición web.
#Pasamos el destino como argumento y lo convertimos
#en un objeto FtpWebRequest.
$R=[System.Net.FtpWebRequest][System.Net.WebRequest]::Create($Destination)

#Especificamos a la petición cómo nos loguearemos
#(usando un objeto NetworkCredential)
$R.Credentials = New-Object System.Net.NetworkCredential($FTP_user, $FTP_password)
$R.proxy = $null
#... y qué tipo de método se usará. Un file upload.
$R.Method = "STOR"

#Aquí se usa el método más simple para obtener los bytes
#de un fichero a un array de bytes.
$FileContens = [System.IO.File]::ReadAllBytes($Source)

#Finalmente, se ponen el array de bytes en la petición web.
$S = $R.GetRequestStream()
$S.Write($FileContens, 0, $FileContens.Length)

#Se cierran el stream y se libera el objeto
$S.Close()
$S.Dispose()
}
trap [SystemException]
{
#Si se produce un error, lo guardamos en el fichero de logs
#y continuamos con el siguiente fichero
$ERROR=$_
$DATE=Get-date
Add-Content $log_file "[ERROR]: $DATE - $ERROR"
continue
}
}



Ahora lo suyo es guardarnos el script en un fichero .ps1 y crear una tarea programada que lo ejecute, por ejemplo, que cada media hora compruebe el directorio y envíe los ficheros.

Y ya que estamos, podemos crear la tarea programada con Powershell:

SCHTASKS /Create /RL HIGHEST /SC MINUTE /MO 30 /TN "Enviar ficheros por FTP" /NP /TR 'powershell.exe –Command "c:\enviar_ficheros_FTP.ps1"'

Bola extra
Tal vez os interese también obtener los ficheros de un FTP: How to download a file, the PowerShell way

Happy codding ;)

viernes, 16 de julio de 2010

El final de un largo viaje: MCPD



6 exámenes después ya soy MCPD Enterprise Application Developer 3.5 :)

martes, 15 de junio de 2010

Novedades Visual Studio 2010 (I): Versiones

Con este post empiezo una serie con las novedades del nuevo Visual Studio 2010 y del .NET Framework 4.0, empezamos con las versiones:


Como ya nos tiene acostumbrados, cada vez que sale un nuevo Visual Studio, también cambian los nombres de las versiones y lo que se puede (o no) hacer con ellas.

En esta página tenéis un gráfico comparativo:

http://www.microsoft.com/spain/visualstudio/products

Pero sirva esto de pequeño resumen:

Professional with MSDN Essentials

Incluye todas las herramientas de desarrollo (windows, web, office and Sharepoint y Could development), el debugger y la de Unit Testing.

No incluye ninguna de las herramientas de arquitectura (ni siquiera el visor de diagramas O_o) ni acceso al Team Foundation Server (TFS).

Tampoco incluye las herramientas de desarrollo de bases de datos, únicamente está disponible la herramienta de Data Access.


Professional with MSDN

Las diferencias con la anterior son: más opciones en la subscripción MSDN y acceso al TFS.


Premium

Añade herramientas de debugging, testing y desarrollo de bases de datos. En el apartado de arquitectura sólo incluye la opción de lectura de diagramas.


Ultimate

El paquete completo, incluye todas las herramientas de:

Debugging and Diagnostics
Testing
Database
Development
Lab Management
Team Foundation Server


Test Professional

La versión más limitada, pensada para miembros del equipo de test, no incluye herramientas de desarrollo ni de acceso a base de datos, únicamente de Testing y TFS.


Si os apetece probarlas, aquí tenéis la página de descargas de versiones Trial:

http://www.microsoft.com/spain/visualstudio/download


PD: curiosamente Visual Studio comparte nomenclatura con Windows 7: Premium, Professional y Ultimate.

martes, 11 de mayo de 2010

Movimiento NoSQL y MongoDB

Os dejo un interesante artículo de Ted Neward sobre MongoDB:

Going NoSQL with MongoDB

Y sobre dicho artículo, una reflexión:

¿Consideráis el movimiento NoSQL como un paso adelante o un paso atrás?

Yo lo veo más como algo que cubre una necesidad. No siempre necesitaremos una base de SGDB como SQL Server, Oracle o MySQL, de la que escasamente vamos a aprovechar un 10 o 20% de sus funcionalidades. Así que siempre está bien tener opciones como ésta, que estará más limitada, pero se puede adaptar mucho mejor a nuestros requerimientos.

¿Qué opináis?

martes, 13 de abril de 2010

Off-topic: nueva aventura

Siempre me ha gustado escribir, pero nunca me había atrevido a hacerlo "en público". Pero en mi último viaje surgió una idea que me ha hecho perderle el miedo a mostrar lo que escribo, y lo he plasmado en un blog:

El puente de Alexander

Es una historia que no está acabada, ni yo sé el final, por lo que se puede considerar algo vivo que irá saliendo poco a poco.

Espero que os guste :)

martes, 2 de marzo de 2010

T-SQL: SET ROWCOUNT vs. SELECT TOP

“Si hay 2 formas de hacer lo mismo, una seguro que da problemas.”
No sé si esta es una de las Leyes de Murphy, pero debería serlo :P

SET ROWCOUNT es uno de esos settings “con trampa” que tiene T-SQL y que si no sabes usarlos adecuadamente te puede provocar muchos quebraderos de cabeza.

Según MSDN: Hace que SQL Server detenga el procesamiento de la consulta una vez que se han devuelto las filas especificadas.

Ah, qué bonito, si sólo quiero que una consulta me devuelva 20 registros pues pongo “SET ROWCOUNT 20;” antes de ejecutarla y listo.
Vamos, lo mismo que si hago un SELECT TOP 20 …

Bueno, sabiendo esto se puede elegir entre utilizar uno u otro método para limitar los resultados. Pero SET ROWCOUNT, al tratarse de un setting afecta a TODAS las consultas que se ejecutan dentro del mismo contexto y aquí es donde viene el problema.

Por ejemplo, supongamos que tenemos 2 tablas relacionadas y una función llamada MiFuncion() que hace lo siguiente:


SELECT idPadre FROM TablaPadre WHERE campoPadre = @parametro


Ahora bien, si hacemos esto:


SET ROWCOUNT 10;
SELECT * FROM TablaHija WHERE idPadre in (select idPadre from MiFuncion(@parametro))


La llamada a MiFuncion (@parametro) devolverá únicamente 10 registros, y a su vez la SELECT inicial también devolverá 10 registros, lo que puede provocar que no obtengamos el resultado deseado :(

¿Y cómo se puede solucionar esto? Pues es relativamente sencillo, cambiamos la llamada a la función por una subconsulta:


SET ROWCOUNT 10;
SELECT * FROM TablaHija WHERE idPadre in (SELECT idPadre FROM TablaPadre WHERE campoPadre = @parametro)


Y así, la subconsulta devuelve los registros que tenga que devolver, sin tener en cuenta el número de SET ROWCOUNT y la SELECT sí que devuelve 10 registros O_o El que lo entienda que me lo explique.

Así que ya sabéis, si vais a usar SET ROWCOUNT, hacerlo con cuidado y aseguraros de desactivarla usando SET ROWCOUNT 0. En cualquier caso, es menos peligroso usar SELECT TOP, ya que solo afecta a la consulta en la que se especifica.

Tampoco es muy aconsejable mezclar SELECT TOP … con SET ROWCOUNT … porque SET ROWCOUNT anula la palabra clave TOP de la instrucción SELECT si el número de filas es inferior.

Happy codding ;)

BOLA EXTRA: a partir de la próxima versión de SQL Server (la que siga a la 2008) la utilización de SET ROWCOUNT no afectará a las instrucciones DELETE, INSERT ni UPDATE.
No me quiero ni imaginar los problemas que puede provocar SET ROWCOUNT con un UPDATE O_o

viernes, 26 de febrero de 2010

Otro test de agudeza visual: ¿qué código es peor?

Opción 1:


Dim intVariable as Integer

Try
intVariable = LlamadaaMetodoQuePuedeDevolverNothing()
Catch ex as Exception
intVariable = 0
End Try



Opción 2:


Dim intVariable as Integer = 0

Try
intVariable = LlamadaaMetodoQuePuedeDevolverNothing()
Catch ex as Exception

End Try





Respuesta: la opción 2, que provoca un precioso "agujero negro de excepciones".

PD: los 2 ejemplos están basados en hechos reales, que no tengo tanta imaginación :P

jueves, 14 de enero de 2010

Search in text fields with T-SQL: beyond LIKE

Versión en castellano

One of the first things you learn when begin with T-SQL is to use the LIKE clause to make comparisons in text fields (char, nchar, varchar, nvarchar or text). But this clause is quite limited and don’t have good performance. So, what can be used instead of LIKE?

A good option is to use CONTAINS(that to be honest, I discovered by accident: P), which allows us to refine more comparisons and offers much better performance.


To give an example of using CONTAINS I will use the PUBS database (Microsoft offers as an example with SQL Server and can be downloaded from here) and we will find records in the "jobs" table filtering by "job_desc" field.

To give you an idea (or if you don't want download example databases) the "jobs" table has the following records:


job_id job_desc min_lvl max_lvl
-------- -------------------------------------------------- ------- -------
1 New Hire - Job not specified 10 10
2 Chief Executive Officer 200 250
3 Business Operations Manager 175 225
4 Chief Financial Officier 175 250
5 Publisher 150 250
6 Managing Editor 140 225
7 Marketing Manager 120 200
8 Public Relations Manager 100 175
9 Acquisitions Manager 75 175
10 Productions Manager 75 165
11 Operations Manager 75 150
12 Editor 25 100
13 Sales Representative 25 100
14 Designer 25 100


Before using CONTAINS we must create a FULLTEXT index in the table, some like this:


CREATE FULLTEXT CATALOG ft AS DEFAULT;
CREATE FULLTEXT INDEX ON Jobs(job_desc)
KEY INDEX PK__jobs__173876EA
GO


PK__jobs__173876EA is the table’s PRIMARY KEY.


Once this is done we can start with examples:

Search exact word

Obtains all records containing the word 'manager' in "job_desc" field.
Note: is not case sensitive.


select * from jobs where CONTAINS( job_desc, 'manager' )

job_id job_desc min_lvl max_lvl
-------- -------------------------------------------------- ------- -------
3 Business Operations Manager 175 225
7 Marketing Manager 120 200
8 Public Relations Manager 100 175
9 Acquisitions Manager 75 175
10 Productions Manager 75 165
11 Operations Manager 75 150


Also you can search for phrases (more than 1 word) and separators are ignored.


select * from jobs where CONTAINS (job_desc, '"hire job"');

job_id job_desc min_lvl max_lvl
-------- -------------------------------------------------- ------- -------
1 New Hire - Job not specified 10 10


Search by approximation with several options

Finds the records containing any word beginning with 'pub' or 'edit'.


select * from jobs where CONTAINS(job_desc, '"pub*" OR "edit*"');

job_id job_desc min_lvl max_lvl
-------- -------------------------------------------------- ------- -------
5 Publisher 150 250
6 Managing Editor 140 225
8 Public Relations Manager 100 175
12 Editor 25 100


CONTAINS supports AND, AND NOT, OR.

Search a word near another one

Finds records that contain a word beginning with 'pub' and that word is near 'manager'.


select * from jobs where CONTAINS (job_desc, '"pub*" NEAR manager')

job_id job_desc min_lvl max_lvl
-------- -------------------------------------------------- ------- -------
8 Public Relations Manager 100 175


CONTAINS can also search for synonyms and derived words, but this requires using a thesaurus.

Happy codding ;)

Extra ball
If you find CONTAINS interested, see also FREETEXT and CONTAINSTABLE.

miércoles, 13 de enero de 2010

3 ways to find SQL Server version with T-SQL

Versión en castellano

In a perfect world all our facilities have the same versions of the applications, database engines and auxiliary libraries, but this world is a utopia, and we sometimes have to find out what version of the database engine is working or what dll version is installed.

In the case of SQL Server I have discovered 3 ways to find the version from T-SQL, with any of the 3 options will get the full version, but in most cases only need the "major release". For example if you want to execute features of the new versions, or ones that are obsolete in the latest version but still work in older versions.

Let’s go:

@@VERSION

Is a system variable, so is useful for any version of SQL Server, but from the 2005 version returns a nvarchar instead of varchar from previous versions.


declare @version char(2)

select @version = substring(@@version, charindex('- ', @@version) + 2, charindex('.',@@version) - charindex('- ', @@version)-2)


SERVERPROPERTY(‘ProductVersion’)

This option is only available after SQL Server 2000 and returns a value of type sqlvariant, so you have to convert it to varchar or nvarchar to get the main version.


declare @productversion varchar(50)

declare @version char(2)

select @productversion = CAST(SERVERPROPERTY('productversion') AS VARCHAR(50))

set @version = SUBSTRING(@productversion,1,CHARINDEX('.',@productversion)-1)



xp_msver ‘ProductVersion’

Perhaps the most complicated, because it returns a result set like this:


Index Name Internal_Value Character_Value
------ -------------------------------- -------------- ----------------------
2 ProductVersion 589824 9.00.1406.00

(1 row(s) affected)


But you can also get the version with something like this:

CREATE TABLE #VersionTable(
[Index] int,
Name varchar(30),
Internal_Value int,
Character_Value varchar(50))
GO

INSERT INTO #VersionTable
EXEC master..xp_msver 'ProductVersion'

DECLARE @Version char(2)

SELECT @Version = (
SELECT SUBSTRING(Character_Value,1,CHARINDEX('.', Character_Value)-1)
FROM #VersionTable)

drop table #VersionTable


With any of the 3 options we get the following in @Version:

‘7’ -> SQL Server 7
‘8’ -> SQL Server 2000 y MSDE
‘9’ -> SQL Server 2005
‘10’ -> SQL Server 2008

Happy coding ;)

martes, 12 de enero de 2010

Getting SQL Servers instances and databases’ names with .NET (VB.NET and C#)

Versión en castellano

This is an example of a form that show SQL Server data sources (some like the “Add Connection” in Visual Studio).

Create a new Windows Forms project, set name “MyConnections”, and create a new form frmConnections. In this form add 2 comboboxes: cmbServers and cmbDataBases (yes, I’m working a lot the names :P).

Add this code to the form:

VB.NET


Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient

Public Class frmConnections
Private servers As SqlDataSourceEnumerator
Private tableServers As DataTable
Private server As String


Public Sub New()
InitializeComponent()
servers = SqlDataSourceEnumerator.Instance
tableServers = New DataTable()
End Sub

End Class


C#

using System;
using System.Windows.Forms;
using System.Data;
using System.Data.Sql;
using System.Collections.Generic;

namespace MyConnections
{
public partial class frmConnections: Form
{
SqlDataSourceEnumerator servers;
System.Data.DataTable tableServers;
String server;

public frmConnections ()
{
InitializeComponent();
servers = SqlDataSourceEnumerator.Instance;
tableServers = new DataTable();
}
}
}

To get a list of available instances of SQL Server use the SqlDataSourceEnumerator.Instance object, namely the getDataSource() method that returns a DataTable with the characteristics of instances: server name, instance name, if it is part of a cluster and its version ("8.00.x" for SQL Server 2000 and "9.00.x" for SQL Server 2005).

Now, the cmbServers click event:

VB.NET

Private Sub cmbServers_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbServers.Click


' Check if datatable is empty
If tableServers.Rows.Count = 0 Then

' Get a datatable with info about SQL Server 2000 and 2005 instances
tableServers = servers.GetDataSources()

' List that will be combobox’s datasource
Dim listServers As List(Of String) = New List(Of String)

' For each element in the datatable add a new element in the list
For Each rowServer As DataRow In tableServers.Rows

' SQL Server instance could have instace name or only server name,
' check this for show the name
If String.IsNullOrEmpty(rowServer ("InstanceName").ToString()) Then
listServers.Add(rowServer ("ServerName").ToString())
Else
listServers.Add(rowServer ("ServerName") & "\\" & rowServer ("InstanceName"))
End If

Next

'Set servers list to combobox’s datasource
Me.cmbServers.DataSource = listServers
End If

End Sub


C#

private void cmbServers_Click(object sender, EventArgs e)
{

// Check if datatable is empty
if (tableServers.Rows.Count == 0)
{
// Get a datatable with info about SQL Server 2000 and 2005 instances
tableServers = servers.GetDataSources();


// List that will be combobox’s datasource
List listServers = new List();


// For each element in the datatable add a new element in the list
foreach (DataRow rowServer in tableServers.Rows)
{

// Server instance could have instace name or only server name,
// check this for show the name
if (String.IsNullOrEmpty(rowServidor["InstanceName"].ToString()))
listServers.Add(rowServer ["ServerName"].ToString());
else
listServers.Add(rowServer["ServerName"] + "\\" + rowServer["InstanceName"]);
}

// Set servers list to combobox’s datasource
this.cmbServers.DataSource = listServers;
}
}



For the combobox with the databases list we must execute a query againts sys.databases view:

VB.NET

Private Sub cmbDataBases_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbDataBases.Click

Dim listDataBases As List(Of String) = New List(Of String)
Dim connectString As String
Dim selectSQL As String

' Check if user was selected a server to connect
If Me.cmbServers.Text = "" Then
MsgBox("Must select a server")
Return
End If

server = Me.cmbServers.Text

'Set connection string with selected server and integrated security
connectString = "Data Source=" & server & " ;Integrated Security=True;Initial Catalog=master"


Using con As New SqlConnection(connectString)

' Open connection
con.Open()

'Get databases names in server in a datareader
selectSQL = "select name from sys.databases;"

Dim com As SqlCommand = New SqlCommand(selectSQL, con)
Dim dr As SqlDataReader = com.ExecuteReader()


While (dr.Read())
listDataBases.Add(dr(0).ToString())
End While

'Set databases list as combobox’s datasource
Me.cmbDataBases.DataSource = listaBasesDatos

End Using

End Sub


C#

private void cmbBasesdeDatos_Click(object sender, EventArgs e)
{
string select;
string connectString;
List listDataBases = new List();

// Check if user was selected a server to connect
if (this.cmbServers.Text == "")
{
MessageBox.Show("Must select a server");
Return;
}

this.server = this.cmbServers.Text;

// Set connection string with selected server and integrated security
connectString = "Data Source=" + this.server + ";Integrated Security=True;Initial Catalog=master";

using (System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(connectString))
{
// Open connection
con.Open();

//Get databases names in server in a datareader
select = "select name from sys.databases;";

System.Data.SqlClient.SqlCommand com =
new System.Data.SqlClient.SqlCommand(select, con);
System.Data.SqlClient.SqlDataReader dr = com.ExecuteReader();

while (dr.Read())
{
listDataBases.Add(dr[0].ToString());
}

// Set databases list as combobox’s datasource
this.cmbDataBases.DataSource = listDataBases;
}
}


And that’s all, we have a form that show SQL Server instances and theirs databases.


Note: if you don’t use integrated security, must change connection string to add user and password attributes.

Happy codding ;)

lunes, 11 de enero de 2010

Converting rows in columns with T-SQL: PIVOT() and things that will brighten the day :)

Versión en castellano

Sometimes things that seem very complex have a easy solution, but this isn’t the first one that we think (yes, sound like Occam’s razor, but isn’t the same).

Well, the case is that I had a logging table in which some external process inserted the results of passing for different states, something like this:



Process State Timestamp

Process1 10 2008/12/21 12:22:05.320
Process2 10 2008/12/21 12:22:05.358
Process1 11 2008/12/21 12:22:06.210
Process1 12 2008/12/21 12:22:06.588
Process2 11 2008/12/21 12:22:10.100
Process2 12 2008/12/21 12:22:10.048
Process3 10 2008/12/21 14:30:05.358
Process3 11 2008/12/21 14:30:20.052


To monitor processes and identify performance problems at certain times: many processes at once, backups were began, the server runs out of memory, ... I get a query grouping by process and hour.


SELECT process, count(*) as requests, datepart(hh,Timestamp) as hour
FROM MiLOG with (nolock)
GROUP BY process, datepart(hh,Timestamp)
ORDER BY 1,3


With this query we retrieve a result like this:



Process Requests Hour

Process1 10 12
Process1 2 13
Process2 5 12
Process2 3 13
Process3 25 14
Process3 4 15


Of course, this output is a bit complicated to treat because even though they are grouped by the process and the time, when you have lots of data becomes too cumbersome to check them, and just bringing the results to a spreadsheet you can see more clearly. Then I wondered, is there not a way to do it from T-SQL convert rows into columns, with a column for each hour and a single row for each process?

As always the first solution to a complicated query is a sub-query (I don't know why but it's true, someone would have to study it :P) but in this case was not a sub-query, but rather 24 sub-queries, all attacking the same table, and all using the function datepart (hh, TimeStamp). Surely you would get the expected result, but at the expense of a very poor performance.

As this situation is very common, I think that should have an easy way to fix it, so in an initial Google search did not surprise me the first results show the same resolution of sub-queries (for the principle that the first solution in a complicated query is a sub-query: P) and I was doing a huge query with a lot of sub-queries, JOINs to the same table. But on the first page of results I found a much simpler solution: the PIVOT clause (that is new SQL Server 2005, so don't try in earlier versions).

So, with a query like that:


WITH Processes(process, requests, hour)
AS(
SELECT process, count(*) as requests, datepart(hh,Timestamp) as hour
FROM MiLOG with (nolock)
GROUP BY process, datepart(hh,Timestamp)
)
SELECT * FROM Processes PIVOT(SUM(requests) FOR Hour IN ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23])) AS Result
ORDER BY process


we have the expected result:



Process 0 1 2 ... 21 22 23

Process1 0 10 23 4 5 21
Process2 12 11 15 22 17 8
Process3 9 7 2 19 9 13


Note: PIVOT() statement works only with agregate functions (like COUNT, SUM, MAX, AVG, …).


Another interesting thing, is:


WITH Processes( process, requests, hour) AS(...)


that Specifies a temporary named result set, known as a common table expression (CTE).


Happy codding ;)


Extra ball: for this case I also thought of using CUBE, that is other summarizing tool provided by T-SQL.