Cursore con “where current of”

set nocount on

-- CREO TABELLA TEMPORANEA ----------------------------------------------------
drop table #p

create table #p
(
 id int
)

insert #p values (10)
insert #p values (20)
insert #p values (30)
insert #p values (40)
insert #p values (50)
insert #p values (60)
insert #p values (70)
insert #p values (80)


select * from #p


-- DICHIARO IL CURSORE --------------------------------------------------------
declare @i int

declare #c cursor local for select id from #p

open #c

fetch next from #c into @i

while @@fetch_status = 0
 begin
  set @i = @i + 100
 
  update #p
  set id = @i
  where current of #c
 
  fetch next from #c into @i
 end


close #c
deallocate #c


select * from #p

Elimina file più vecchi di n giorni con ricorsione - BosoCleanDir.vbs / Delete old files

NOTE:
- impostare i parametri
- la riga che elimina i file è remmata

HACK:
- ottenere i parametri da commandline.

BosoCleanDir.vbs

' -- 2015-10-31 - aggiunto qualche echo()

Option Explicit

'------------------------------------------------------------------------------

' -- PARAMETRI
const elencoCartelle = "c:\test\1,c:\test\bla bla 2"
const maxGiorni = 30

'------------------------------------------------------------------------------

sub eliminaFile(wPath, oggi)
 
 if trim(wpath) = "" then exit sub
 
 with wscript
  .echo
  .echo "**** Processing directory: " & wpath
 end with

 dim wFileSystem, wFolder, wSubFolders, f, file
 set wFileSystem = createobject("Scripting.FileSystemObject")
 set wFolder = wFileSystem.getfolder(wPath)
 
 ' -- ELIMINA TUTTI I FILE PIU' VECCHI DI [maxGiorni] GIORNI
 for each file in wFolder.Files
  if DateDiff("d", file.DateCreated, oggi) > maxGiorni then
   wscript.echo "Deleting " & file.name
'   file.delete(true)
  end if
 next
 
 ' -- ELIMINA I FILES NELLE SOTTOCARTELLE
 dim subFolder
 for each subFolder in wFolder.SubFolders
  eliminaFile subFolder, oggi
 next

 set file = nothing
 set wFileSystem = nothing
 set wFolder = nothing
 Set wSubFolders = nothing
 
end sub

'------------------------------------------------------------------------------

sub Main()
 
 ' -- LOOP SULL'ELENCO DELLE CARTELLE
 dim arrFolder
 arrFolder = Split(elencoCartelle, ",")

 dim oggi
 oggi = now
 
 dim riga
 riga =  string(80, "*")
 
 with wscript
  .echo
  .echo riga
  .echo "* DELETING FILES OLDER THAN " & maxGiorni & " DAYS"
  .echo riga

  dim cartella
  for each cartella in arrFolder
   eliminaFile cartella, oggi
  next

  .echo
  .echo "** DONE"
 end with

end sub

'------------------------------------------------------------------------------

Main

---

Versione alternativa, senza output

Questa versione pulisce i log di IIS:

sLogFolder = "c:\inetpub\logs\LogFiles"
iMaxAge = 30   'in days

Set objFSO = CreateObject("Scripting.FileSystemObject")
set colFolder = objFSO.GetFolder(sLogFolder)

For Each colSubfolder in colFolder.SubFolders
        Set objFolder = objFSO.GetFolder(colSubfolder.Path)
        Set colFiles = objFolder.Files

        For Each objFile in colFiles
                iFileAge = now-objFile.DateCreated
                if iFileAge > (iMaxAge+1)  then
                        objFSO.deletefile objFile, True
                end if
        Next
Next

Via.

Chiudere la connessione dal DataReader

When you create a DataReader, you call .ExecuteDataReader. This method accepts a parameter that can be CommandBehavior.CloseConnection. This parameter tells the DataReader that when it is closed, the underlying connection should be closed as well. This is an example function that shows how you can return a DataReader and ensure that it is closed by the calling method:

// C# version

public static IDataReader SelectByRoyalty(int Percentage)
{ 
 SqlDataReader dr=null;
 SqlConnection cn=new SqlConnection
                  ("Server=Aron1;Database=pubs;Trusted_Connection=True;");
 cn.Open();
 try
 {
  SqlCommand cmd=new SqlCommand("byRoyalty",cn);
  cmd.CommandType=CommandType.StoredProcedure;
  cmd.Parameters.Add("@Percentage",Percentage);
   
  dr=cmd.ExecuteReader(CommandBehavior.CloseConnection);
 }
 catch ( Exception Ex )
 {
  if ( dr!=null )
  {
   dr.Close();
   cn.Close();
  }
  throw Ex;
 }
 return (IDataReader)dr;
}

' VB version

Public Shared Function SelectByRoyalty(ByVal Percentage As Integer) As IDataReader
    Dim dr As SqlDataReader = Nothing
    Dim cn As New SqlConnection("Server=Aron1;Database=pubs;Trusted_Connection=True;")
    cn.Open()
    Try
        Dim cmd As New SqlCommand("byRoyalty", cn)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add("@Percentage", Percentage)

        dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
    Catch Ex As Exception
        If dr <> Nothing Then
            dr.Close()
            cn.Close()
        End If
        Throw Ex
    End Try
    Return DirectCast(dr, IDataReader)
End Function

Then, you could call the method as follows:

IDataReader dr=Coatings.SelectCoating(CoatingID);
try
{
 // Use the DataReader..
}
Finally
{
 dr.Close();
}

Failure to use a pattern like this will lead to a great deal of difficulty with connections that are not closed in a timely fashion. I believe that Microsoft initially pushed DataReaders as the preferred way to do database access, at least for ASP.NET applications. As time went on, I think the company discovered that many developers misused DataReaders, not properly closing the connection. Recent Microsoft presentations have often emphasized DataSets even for ASP.NET applications.

Cerca opzioni database su un server

-- This script works well in SQL 2000, didn't test in 2005+) 

DECLARE @DataBase sysname 

DECLARE	database_cursor CURSOR FOR 
SELECT	quotename(name) as Base 
FROM 	master..sysdatabases 
WHERE	DATABASEPROPERTYEX(name, 'Status') = 'ONLINE' AND 
		DATABASEPROPERTYEX(name, 'IsAutoShrink') = 1 

OPEN database_cursor 

FETCH NEXT 
FROM database_cursor 
INTO @DataBase 

WHILE @@FETCH_STATUS = 0 
	BEGIN 
		PRINT @DataBase
--		EXEC ('ALTER DATABASE ' + @DataBase + ' SET AUTO_SHRINK OFF') 

		FETCH NEXT 
		FROM database_cursor 
		INTO @DataBase 
	END 

CLOSE database_cursor 
DEALLOCATE database_cursor 
GO 

Apri UI stampa attendendo esecuzione comando

Dim WshShell, oExec
dim wCmd


' COMANDO DA ESEGUIRE
wCmd = "rundll32 printui.dll,PrintUIEntry /o /n\\nome_macchina\nome_stampante"


' CREA L'OGGETTO SHELL (PER LANCIARE IL COMANDO)
set WshShell = CreateObject("WScript.Shell")


' ESEGUE IL COMANDO
set oExec = WshShell.Exec(wcmd)


' ATTENDE IL TERMINE DELL'ESECUZIONE DEL COMANDO
Do While oExec.Status = 0
	WScript.Sleep 100
Loop


' ESECUZIONE TERMINATA!
MsgBox oExec.Status


' SVUOTA OGGETTI (PER "RISPARMIARE" MEMORIA)
set WshShell = nothing
set oExec = nothing

Conversione di codice html in testo piano

Piccolo e utile frammento di codice che, utilizzando le amate/odiate espressioni regolari, ripulisce dai tag html il testo passato.

 

Imports System.Text
Imports System.Text.RegularExpressions
		
Function Html2Text(ByVal html As String) As String
 
   Dim repattern As String = “\<[^\>]*\>“
   Dim rexp As New Regex(repattern, RegexOptions.IgnoreCase)

    html = rexp.Replace(html, String.Empty)

   Return html
End Function

sp_who filtrata

set nocount on

drop table #boso

create table #boso
(
	spid nvarchar(max), 
	ecid nvarchar(max), 
	status nvarchar(max), 
	loginame nvarchar(max), 
	hostname nvarchar(max), 
	blk nvarchar(max), 
	dbname nvarchar(max), 
	cmd nvarchar(max), 
	request_id nvarchar(max)
)

insert #boso
exec sp_who


select * 
from #boso
where dbname = 'ITPORTAL_FALCONE'
order by status

Accesso ai dati asincrono – Async data access

Namespace DotNetSide.Articles.AsyncDataAccess
    Imports System
    Imports System.Collections.Generic
    Imports System.ComponentModel
    Imports System.Data
    Imports System.Data.SqlClient
    Imports System.Drawing
    Imports System.Text
    Imports System.Windows.Forms
    
    
    Public Class Form1
        Inherits Form
        
        Public Sub New()
            MyBase.New
            InitializeComponent
        End Sub
        
        Private Sub btnStart_Click(ByVal sender As Object, ByVal e As EventArgs)
            Dim myConnection As SqlConnection = New SqlConnection
            myConnection.ConnectionString = "Data Source=.\\SQLEXPRESS;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=ProgrammareADONet;async=true"
            myConnection.Open
            Dim cmd As SqlCommand = myConnection.CreateCommand
            cmd.CommandText = "WaitFor Delay '00:00:15' Select @@Version"
            cmd.BeginExecuteReader(New AsyncCallback(ProcessResult), cmd)
        End Sub
        
        Public Sub ProcessResult(ByVal asyncRes As IAsyncResult)
            Dim cmd As SqlCommand = CType(asyncRes.AsyncState,SqlCommand)
            cmd.Connection
            cmd
            Dim DBVersion As String = string.Empty
            Dim myReader As SqlDataReader = cmd.EndExecuteReader(asyncRes)
            If myReader.Read Then
                DBVersion = myReader(0).ToString
                lbltextVersion.BeginInvoke(New LabelHnd(Update), DBVersion)
            End If
        End Sub
        
        Public Sub Update(ByVal text As String)
            lbltextVersion.Text = text
        End Sub
        
        Public Delegate Sub LabelHnd(ByVal value As String)
    End Class
End Namespace

Apri Access da .NET

' Apre una form in un db di AccessForm, passando la WhereCondition
Private Sub ApriAccess()

Try
        Console.WriteLine("premi un tasto per aprire access...")
        Console.ReadKey()

        Dim a As New Access.Application

        a.OpenCurrentDatabase("d:\pippo.mdb")
        a.DoCmd.OpenForm("Form1", , , " Nome='C' ")
        a.Visible = True

        Console.WriteLine("premi un tasto per chiudere access...")
    	Console.ReadKey()
    	
        a.CloseCurrentDatabase()
    Catch ex As Exception

    End Try

    a = Nothing

End Sub

Cerca campi orfani fra due tabelle

create procedure sys_CampiOrfani_sp
	@tab1 as nvarchar(100), 
	@tab2 as nvarchar(100)
AS
BEGIN
	set nocount on

	select	c.*, s.name as typeName
	into	#aaa
	from	sys.columns c inner join sys.types s on 
			c.system_type_id = s.system_type_id
	where	c.object_id = object_id(@tab1)


	select	c.*, s.name as typeName
	into	#bbb
	from	sys.columns c inner join sys.types s on 
			c.system_type_id = s.system_type_id
	where	c.object_id = object_id(@tab2)


	select	@tab1 AS 'Table name', a.name AS 'Nome campo', a.typeName, 
			a.max_length, a.precision, a.scale
	from	#aaa a LEFT join #bbb b on 
			a.name = b.name
	where	b.name is null
	order by
			a.name


	select	@tab2 AS 'Table name', b.name AS 'Nome campo', b.typeName, 
			b.max_length, b.precision, b.scale
	from	#aaa a RIGHT join #bbb b on 
			a.name = b.name
	where	a.name is null
	order by
			b.name

END

go

exec sys_CampiOrfani_sp 
'MPS_InterventiStorico',
'RichiesteManutenzione_tb'

if object_id('sys_CampiOrfani_sp') is not null 
	drop procedure sys_CampiOrfani_sp

SQL CSV to Table

ALTER Function [dbo].[Csv2Table_fn] (@CSVList nvarchar(1000))
 RETURNS @Table table (id nvarchar(50))
AS
BEGIN
 DECLARE @sep AS nvarchar(1); SET @sep = ','
 
 IF RIGHT(@CSVList, 1) <> @sep SET @CSVList = @CSVList + @sep

 DECLARE @Pos smallint; SET @Pos = 1
 DECLARE @OldPos smallint; SET @OldPos = 1
 
 WHILE @Pos < LEN(@CSVList)
  BEGIN
   SET @Pos = CHARINDEX(@sep, @CSVList, @OldPos)
   
   INSERT @Table
   SELECT LTRIM(RTRIM(SUBSTRING(@CSVList, @OldPos, @Pos - @OldPos)))
   
   SET @OldPos = @Pos + 1
  END

 RETURN
END
GO

-- ESEMPIO:
DECLARE @S AS NVARCHAR(100)
SET @S = 'ROSSO, VERDE,BLU,   BOSO'
SELECT * FROM CSVToTable_fn(@S)

VERSIONE CON SEPARATORE PARAMETRICO:

CREATE FUNCTION [dbo].[Csv2Table_fn]
(
   @CSVList nvarchar(MAX)
 , @sep nvarchar(1) = ','
)
 RETURNS @Table table (id nvarchar(50))
AS
BEGIN 
 IF RIGHT(@CSVList, 1) <> @sep SET @CSVList = @CSVList + @sep

 DECLARE @Pos smallint; SET @Pos = 1
 DECLARE @OldPos smallint; SET @OldPos = 1
 
 WHILE @Pos < LEN(@CSVList)
  BEGIN
   SET @Pos = CHARINDEX(@sep, @CSVList, @OldPos)
   
   INSERT @Table
   SELECT LTRIM(RTRIM(SUBSTRING(@CSVList, @OldPos, @Pos - @OldPos)))
   
   SET @OldPos = @Pos + 1
  END

 RETURN
END
GO

UTILIZZATO PER SPLITTARE UNA STRINGA:

DECLARE @split table (Id nvarchar(50), Posizione int)
 
INSERT @split (Id, Posizione)
SELECT Id, ROW_NUMBER() OVER (ORDER BY GETDATE()) - 1
FROM GVSYS..Csv2Table_fn('38023454.XZQ.QWE.00029466.0768.txt', '.')


SELECT *
FROM @split s



-- RISULTATO:

Id     Posizione
---------------------
38023454            0
XZQ                 1
QWE                 2
00029466            3
0768                4
txt                 5

Trovato qui.

COPY/XCOPY from network: restartable mode

I recently had to copy a very large file (>4GB) from a remote Windows share to my Windows 7 machine, over a very finicky connection (frequently-failing VPN over a slow Internet connection). A GUI copy didn't work because the connection dropped frequently. Then a colleague pointed me to the "copy /z" command:

/Z Copies networked files in restartable mode.

And this simple, built-in tool turned out to be the thing I needed. It was, every time, able to restart the download properly, and after a few times, it downloaded the file correctly.
A couple of tips:

• If you interrupt the command with Ctrl-C, it will delete the portion copied so far and you'll have to restart the copy from the beginning. If you must interrupt a copy, simply terminate the network connection (it will die with an error, but leave the copied-so-far file) or close the window where the command is running.
• When you restart it and it asks if you want to overwrite, you must answer "yes". You can also use the /Y flag.

Trovato qui.

Rinumera record con una sola update

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
 DROP TABLE #tmp

SELECT IdArgomento, IdPagina, OrdineVis 
INTO #tmp 
FROM PagineArgomenti_tb 
WHERE IdArgomento = 171 
ORDER BY OrdineVis 


DECLARE @Newpos smallint
SELECT @Newpos = 0


UPDATE #tmp 
SET  @Newpos = OrdineVis = @Newpos + 10 


UPDATE p
SET  p.OrdineVis = t.OrdineVis 
FROM PagineArgomenti_tb p 
  INNER JOIN #tmp t 
   ON p.IdArgomento = t.IdArgomento 
   AND p.IdPagina = t.IdPagina

Oppure, con la funzione ROW_NUMBER()

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
 DROP TABLE #tmp

SELECT IdArgomento, IdPagina, ROW_NUMBER() OVER (ORDER BY OrdineVis) AS OrdineVis 
INTO #tmp 
FROM PagineArgomenti_tb 
WHERE IdArgomento = 171 


UPDATE p
SET  p.OrdineVis = t.OrdineVis 
FROM PagineArgomenti_tb p 
  INNER JOIN #tmp t 
   ON p.IdArgomento = t.IdArgomento 
   AND p.IdPagina = t.IdPagina

L'ORDER BY è dentro la OVER().
Se voglio un progressivo e basta, si può fare ROW_NUMBER() OVER (ORDER BY GETDATE())