Notas .NET

Tuesday, November 06, 2012

SQL :: Truncar Logs en SQL 2005 y 2008

Aquí les dejo unos códigos para truncar logs en SQL 2005 y SQL 2008.

SQL 2008
BACKUP LOG DatabaseName TO DISK = N'D:\Backup\DatabaseName_log.bak'
GO
DBCC SHRINKFILE('MyDatabase_Log', 1)
GO
• DatabaseName_log.bak es el nombre que tendrá el archivo BAK.
• MyDatabase_Log es el nombre lógico del archivo de Logs. Para saber cómo se llama hay que dar clic derecho en la base de datos, seleccionar propiedades y en la pestaña Files buscar Logical_Name del archivo Logs.

SQL 2005
Ejecutar como cualquier Query. No hay necesidad de tener seleccionada la base de datos. Sólo reemplaza MyDataBase por el nombre de tu BD.
dump tran MyDataBase with NO_LOG DBCC
SHRINKDATABASE (MyDataBase, NOTRUNCATE)
DBCC SHRINKDATABASE (MyDataBase, TRUNCATEONLY)

Thursday, March 05, 2009

Exportando tablas de SQL a Excel y viceversa

1. De una tabla SQL a un Excel ya existente:
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\testing.xls;', 'SELECT * FROM [SheetName$]') select * from SQLServerTable


2. Desde Excel creando nueva tabla en SQL:
select * into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\testing.xls;HDR=YES', 'SELECT * FROM [Sheet1$]')


3. Desde Excel a una tabla de SQL ya existente:
Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\testing.xls;HDR=YES', 'SELECT * FROM [SheetName$]')


4. Creando la tabla en un Excel a partir de una tabla de SQL:
EXEC sp_makewebtask
@outputfile = 'd:\testing.xls',
@query = 'Select * from Database_name..SQLServerTable',
@colheaders =1,
@FixedFont=0,@lastupdated=0,@resultstitle='Testing details'


(Now you can find the file with data in tabular format)

5. Creando el Excel con columnas, a partir de una tabla SQL.
Primero se crea el siguiente Stored Procedure:

create procedure proc_generate_excel_with_columns
(
@db_name varchar(100),
@table_name varchar(100),
@file_name varchar(100)
)
as

--Generate column names as a recordset
declare @columns varchar(8000), @sql varchar(8000), @data_file varchar(100)
select
@columns=coalesce(@columns+',','')+column_name+' as '+column_name
from
information_schema.columns
where
table_name=@table_name
select @columns=''''''+replace(replace(@columns,' as ',''''' as '),',',',''''')

--Create a dummy file to have actual data
select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.xls'

--Generate column names in the passed EXCEL file
set @sql='exec master..xp_cmdshell ''bcp " select * from (select '+@columns+') as t" queryout "'+@file_name+'" -c'''
exec(@sql)

--Generate data in the dummy file
set @sql='exec master..xp_cmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c'''
exec(@sql)

--Copy dummy file to passed EXCEL file
set @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> '+@file_name+''''
exec(@sql)

--Delete dummy file
set @sql= 'exec master..xp_cmdshell ''del '+@data_file+''''
exec(@sql)


Para ejecutar el SP:
EXEC proc_generate_excel_with_columns 'your dbname', 'your table name','your file path'


6. En algunas ocasiones nos dirá que algunos permisos no están configurados. Uno de ellos es "Web Assistant Procedures" y para activarlo debemos ejecutar los siguientes comandos:
sp_configure
sp_configure 'show advanced options', 1
reconfigure
sp_configure 'Web Assistant Procedures', 1
reconfigure

Thursday, August 02, 2007

Ejecutar sentencias SQL de modo práctico

Para ello utilizaremos una función llamada RunSQLBD que puede llamarse de dos formas: una es simplemente enviándole como parámetro una sentencia SQL y la otra es enviándole la sentencia más los parámetros SQL que deseemos utilizar.

En ambos casos las sentencias deben ser de tipo INSERT, UPDATE o DELETE. Los parámetros SQL evitan que alguien realice el famoso SQL Injection, modificando nuestras sentencias a su antojo y realizando estragos en nuestra base de datos.

Recordemos que .NET es un lenguaje que permite la abstracción, así que veamos primero cómo utilizar la función RunSQLBD para mostrar lo sencillo que es. Supongamos que tenemos una aplicación que manipula una libreta de direcciones y el usuario ha proporcionado los datos necesarios y es momento de guardar un nuevo registro. La siguiente función recibe la información (que previamente hemos validado) y realiza la inserción.


Sub GuardarLibretaDireccion(ByVal StrNombre As String, ByVal StrCorreo As String, ByVal StrNotas As String, ByVal BlnActivo As Boolean)
Dim StrSQL As String = ""
StrSQL &= " INSERT INTO LibretaDirecciones(Nombre, Correo, Notas, Activo)"
StrSQL &= " VALUES(@Titulo, @Correo, @Notas, @Activo)"
Try
Dim Parametros(3) As OleDbParameter
Parametros(0) = New OleDbParameter("@Nombre", OleDbType.VarChar, 255)
Parametros(1) = New OleDbParameter("@Correo", OleDbType.VarChar, 100)
Parametros(2) = New OleDbParameter("@Notas", OleDbType.LongVarChar, 8000)
Parametros(3) = New OleDbParameter("@Activo", OleDbType.Boolean)
Parametros(0).Value = StrNombre
Parametros(1).Value = StrCorreo
Parametros(2).Value = StrNotas
Parametros(3).Value = BlnActivo
RunSQLBD(StrSQL, Parametros)
Catch ex As Exception
'AQUÍ REALIZAMOS ALGUNA ACCIÓN SI ALGO FALLA (QUIZÁ QUIERAS RECIBIR POR CORREO EL DETALLE DEL ERROR.
End Try
End Sub


Ahora veamos el mismo ejemplo pero sin utilizar parámetros SQL:


Sub GuardarLibretaDireccion(ByVal StrNombre As String, ByVal StrCorreo As String, ByVal StrNotas As String, ByVal BlnActivo As Boolean)
Dim StrSQL As String = ""
StrSQL &= " INSERT INTO LibretaDirecciones(Nombre, Correo, Notas, Activo)"
StrSQL &= " VALUES('" & StrNombre & "', '" & StrCorreo & "', '" & StrNotas & "', '" & BlnActivo & "')"
Try
RunSQLBD(StrSQL, Parametros)
Catch ex As Exception
'AQUÍ REALIZAMOS ALGUNA ACCIÓN SI ALGO FALLA (QUIZÁ QUIERAS RECIBIR POR CORREO EL DETALLE DEL ERROR.
End Try
End Sub


Fácil, ¿no?

Ahora sí! Aquí están las dos funciones que lo hacen todo. Hemos colocado Overloads para que se entienda que ambas tendrán el mismo nombre. Si al llamar a RunSQLBD le colocamos un solo parámetro se ejecutará la primera, si por otro lado colocamos 2 parámetros entonces simplemente se ejecutará la segunda.


Public Overloads Sub RunSQLBD(ByVal StrSQL As String)
Dim dbConnection As New OleDbConnection(System.Web.Configuration.WebConfigurationManager.AppSettings(GetBDKey()))
Dim dbCommand As New OleDbCommand(StrSQL, dbConnection)
Try
dbConnection.Open()
dbCommand.ExecuteNonQuery()
Catch ex As Exception
Throw New Exception(ex.Message, ex.InnerException)
'THROW NOS PERMITIRÁ CACHAR EL ERROR EN CUALQUIER LUGAR DONDE SE MANDE LLAMAR ESTA RUTINA
Finally
dbConnection.Close()
End Try
End Sub

Public Overloads Sub RunSQLBD(ByVal StrSQL As String, ByVal SqlParametros As OleDbParameter())
Dim dbConnection As New OleDbConnection(System.Web.Configuration.WebConfigurationManager.AppSettings(GetBDKey()))
Dim dbCommand As New OleDbCommand(StrSQL, dbConnection)
For Each objpar As OleDbParameter In SqlParametros
dbCommand.Parameters.Add(objpar)
Next

Try
dbConnection.Open()
dbCommand.ExecuteNonQuery()
dbConnection.Close()
Catch ex As Exception
Throw New Exception(ex.Message, ex.InnerException)
'THROW NOS PERMITIRÁ CACHAR EL ERROR EN CUALQUIER LUGAR DONDE SE MANDE LLAMAR ESTA RUTINA
Finally
dbConnection.Close()
End Try
End Sub


Es bastante código para estas dos funciones. Pero de esta forma se ejecutarán sentencias de un modo seguro y no tendremos que escribir todo este código cada vez que queramos realizar una acción en la base de datos.

Ah! Eso de "MiBasedeDatos" significa que hemos creado en el archivo web.config una etiqueta de tipo:

<appSettings>
<add key="MiBasedeDatos" value="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\...\Northwind.mdb"/>
</appSettings>


Este ejemplo se basa en Access; si estás utilizando SQL tendrás que cambiar los objetos OleDb a SQL, por ejemplo OleDbParameter por SqlParameter. Y claro, el "value" que aparece en el código perteneciente al web.config cambiará por una cadena de conexión que sea correcta para tu base de datos SQL.

Manejando llaves primarias en un DataSet

Supongamos que requerimos una tabla que tenga dos columnas: ID y Nombre. La primera columna (ID) deberá ser llave primaria y además será de tipo "UniqueIdentifier" de modo que nos permita almacenar valores alfanuméricos y que no se repitan.

Dim dsProcesadas As New DataSet("MiDataSet")
Dim newTable1 As New DataTable("MiTabla1")
dsProcesadas.Tables.Add(newTable1)

newTable1.Columns.Add("ID", Type.GetType("System.Guid"), "")
newTable1.Columns.Add("Nombre", Type.GetType("System.String"), "")

Dim key(0) As DataColumn
key(0) = newTable1.Columns("ID")
newTable1.PrimaryKey = key


¿Cómo añado registros?

Es muy fácil, podemos crear una rutina muy aparte que lo haga y que reciba como parámetros los valores de las columnas. No nos confundamos, veamos el ejemplo:

Sub CreaRegistro(ByVal GuidID As Guid,ByVal StrNombre As String, ByRef ds As DataSet)
   Dim row As DataRow
   row = ds.Tables("MiTabla1").NewRow
   ds.Tables("MiTabla1").Rows.Add(row)
   row("ID") = GuidID
   row("Nombre") = StrNombre
End Sub


Juntemos todo el código en la siguiente función:

<script runat="server">

Sub Page_Load()
   Dim ds As new DataSet
   ds = GetDataSetPrincipal()
   '# A PARTIR DE AQUI PODEMOS HACER Y DESHACER
   '# EN EL DATASET
End Sub

Function GetDataSetPrincipal() As DataSet
   Dim ds As New DataSet("MiDataSet")
   Dim newTable1 As New DataTable("MiTabla1")
   ds.Tables.Add(newTable1)

   newTable1.Columns.Add("ID", Type.GetType("System.Guid"), "")
   newTable1.Columns.Add("Nombre", Type.GetType("System.String"), "")

   Dim key(0) As DataColumn
   key(0) = newTable1.Columns("ID")
   newTable1.PrimaryKey = key

   Dim GuidJuan As Guid = New Guid("9CD8769C-7DEB-40A0-828E-DBB03556B451")
   Dim GuidPedro As Guid = New Guid("9CD8769C-7DEB-40A0-828E-DBB03556B452")
   Dim GuidAna As Guid = New Guid("9CD8769C-7DEB-40A0-828E-DBB03556B453")
   CreaRegistro(GuidJuan, "Juan" , ds)
   CreaRegistro(GuidPedro, "Pedro" , ds)
   CreaRegistro(GuidAna, "Ana" , ds)

   Return ds
End Function

Sub CreaRegistro(ByVal GuidID As Guid,ByVal StrNombre As String, ByRef ds As DataSet)
   Dim row As DataRow
   row = ds.Tables("MiTabla1").NewRow
   ds.Tables("MiTabla1").Rows.Add(row)
   row("ID") = GuidID
   row("Nombre") = StrNombre
End Sub

</script>

Thursday, June 21, 2007

Confirmación javascript para botones NET

Teniendo el botón:
<asp:Button id="btnEliminar" onClick="EliminaElemento" runat="server" />

procedemos a aplicar la función javascript que mostrará la ventana de confirmación:
btnEliminar.Attributes("onClick") = "return confirm('¿Esta seguro de querer eliminar este elemento?');"

también se puede realizar así:
btnEliminar.Attributes.Add("onClick", "return confirm('¿Esta seguro de querer eliminar este elemento?');")

Observar que el botón tiene su evento OnClick propio de servidor (manda llamar una función que, en nuestro caso, se llama EliminaElemento) y aparte estamos añadiendo otro evento onClick para que se ejecute en modo cliente (javascript). Hay que tener en cuenta que javascript en sensible al uso de mayúsculas por lo que "onclick" no es lo mismo que "onClick".

Labels:

Thursday, August 10, 2006

Un query para paginar a nuestro antojo.

Veamos este primer ejemplo que tomará los primeros 10 registros encontrados en la tabla Clientes:

SELECT TOP 10 * FROM Clientes
WHERE IdCliente NOT IN
(
SELECT TOP 0 IdCliente FROM Clientes
ORDER BY IdCliente
)
ORDER BY IdCliente


Ahora tomando los siguientes 10 registros:

SELECT TOP 10 * FROM Clientes
WHERE IdCliente NOT IN
(
SELECT TOP 10 IdCliente FROM Clientes
ORDER BY IdCliente
)
ORDER BY IdCliente


Ahora tomando el tercer grupo (TOP 20) de 10 registros (TOP 10):

SELECT TOP 10 * FROM Clientes
WHERE IdCliente NOT IN
(
SELECT TOP 20 IdCliente FROM Clientes
ORDER BY IdCliente
)
ORDER BY IdCliente


Obsérvese la aplicación de filtros al ejemplo anterior.

SELECT TOP 10 * FROM Clientes
WHERE IdCliente NOT IN
(
SELECT TOP 20 IdCliente FROM Clientes
WHERE Vigencia > GetDate()
ORDER BY IdCliente
)
AND WHERE Vigencia > GetDate()
ORDER BY IdCliente


¿Cómo vincular dos BD's en una sentencia SQL?


En términos sencillos, la idea aquí es juntar en una sola sentencia dos tablas que pertenezcan a distintas bases de datos SQL.

Algunas veces requerimos accesar a dos bases de datos para obtener cierta información. Por ejemplo, supongamos que en una BD tenemos los datos personales de todos los clientes y queremos "pegarle" a esta información otro dato existente en otra BD. Este dato podría ser, por ejemplo, cuánto ha consumido de nuestros productos. De ese modo tenemos algo así:


SELECT * FROM BdPrincipal..Clientes
INNER JOIN BdDetalles..Consumos
ON (BdPrincipal..Clientes.IdCliente = BdDetalles..Consumos.IdCliente)


Ambas bases deberán estar funcionando bajo el mismo lenguaje, de otro modo nuestra sentencia será algo como:


SELECT * FROM BdPrincipal..Clientes
INNER JOIN BdDetalles..Consumos
ON (
BdPrincipal..Clientes.IdCliente COLLATE SQL_Latin1_General_CP1253_CS_AS
=
BdDetalles..Consumos.IdCliente COLLATE SQL_Latin1_General_CP1253_CS_AS
)


El idioma lo podemos verificar en la configuración de cada base (SQL Manager)

Friday, July 28, 2006

Algunas funciones VB.NET útiles para el manejo de BD

Esta función recibe como parámetro una sentencia SQL de tipo INSERT, UPDATE o DELETE y la ejecuta:

Sub RunSQLBD(ByVal StrSQL As String)
Dim dbConnection As New SqlConnection(ConfigurationManager.AppSettings("MyDataBase"))
Dim dbCommand As New SqlCommand(StrSQL, dbConnection)
Try
dbConnection.Open()
dbCommand.ExecuteNonQuery()
Finally
dbConnection.Close()
End Try
End Sub


Recordemos que:

ConfigurationManager.AppSettings("MyDataBase")

Hace referencia a la siguiente línea creada en el web.config:

<appSettings>
<add key="MyDataBase" value="server=SERVER_NAME;database=DATABASE_NAME;UID=sa;PWD=xxxxx;/>
</appSettings>


2. GetDataSetBD

La siguiente función regresa un DataSet a partir de una sentencia SQL de tipo SELECT

Function GetDataSetBD(ByVal StrSQL As String) As DataSet
 Dim dbConnection As New SqlConnection(ConfigurationManager.AppSettings("MyDataBase"))
  Dim objAdapter As New SqlDataAdapter(StrSQL, dbConnection)
  Dim ds As New DataSet()
  Try
    objAdapter.Fill(ds)
  Catch ex As Exception
    Throw New Exception(ex.Message, ex.InnerException)
  Finally
  objAdapter.Dispose()
  If dbConnection.State = ConnectionState.Open Then dbConnection.Close()
  dbConnection.Dispose()
  End Try
  Return ds
End Function


La instrucción throw nos permitirá arrojar el error en caso de que lo haya. De ese modo cuando llamemos esta función podremos hacer algo como lo siguiente.


  Try
    Dim ds As New DataSet
    ds = GetDataSetBD("SELECT * FROM Usuarios")
    'AQUÍ PROCESAMOS EL DATASET COMO QUERAMOS
  Catch ex As Exception
    'AQUÍ OBTENEMOS EL ERROR GENERADO Y REALIZAMOS ALGUNA ACCIÓN PERTINENTE
  End Try