Category: SqlServer

Import large Excel file into SqlServer

I have to import the list of NGO from Romania to SqlServer.  Before seeing my solution , please do it yourself: the file could be found at http://www.just.ro/registrul-national-ong/ and the name is Asociatii

I have tried with SSMS – no luck ( text will be truncated, even when I put nvarchar(max))

The I tried with flat file from CSV  – same

Finally, I have solved with a little bit of VBA : first I have tried to write the insert into file and execute with sqlcmd. The file was too large, so sqlcmd does not report any errors( and not insert anything)

So the solution was to generate a batch file that have this

sqlcmd  -S . -E -d InfoRo -Q “INSERT INTO [dbo].[Asociatii$]  ([Denumire]  ,[Numar inreg Reg National],[pozitie inchisa (da/nu)],[Starea actuala],[Judet],[Localitate],[Adresa],[Asociati/Fondatori],[Scop],[Consiliu director],[Apartenenta federatie],[HG utilitate publica],[Data HG utilitate publica],[F14])values( ////)”

generated by this VBA script

Option Explicit

Sub x()
Dim startInsert As String

startInsert = "sqlcmd  -S . -E -d InfoRo -Q ""INSERT INTO [dbo].[Asociatii$]"
         startInsert = startInsert & "  ([Denumire]"
         startInsert = startInsert & "  ,[Numar inreg Reg National]"
           startInsert = startInsert & ",[pozitie inchisa (da/nu)]"
           startInsert = startInsert & ",[Starea actuala]"
           startInsert = startInsert & ",[Judet]"
           startInsert = startInsert & ",[Localitate]"
           startInsert = startInsert & ",[Adresa]"
           startInsert = startInsert & ",[Asociati/Fondatori]"
           startInsert = startInsert & ",[Scop]"
           startInsert = startInsert & ",[Consiliu director]"
           startInsert = startInsert & ",[Apartenenta federatie]"
           startInsert = startInsert & ",[HG utilitate publica]"
           startInsert = startInsert & ",[Data HG utilitate publica]"
           startInsert = startInsert & ",[F14])values("
           
           
           Dim i As Long, j As Integer, text As String
           Dim insert As String
           For i = 2 To 86813
            insert = insert & startInsert
            For j = Asc("A") To Asc("M")
                text = Sheet1.Range(Chr(j) & i).text
                text = Replace(text, vbCr, " ")
                text = Replace(text, vbLf, " ")
                text = Trim(text)
                 text = Replace(text, "'", "''")
                text = Replace(text, "  ", " ")
                
                insert = insert & "'" & text & "',"
            Next j
            insert = insert & "null)" & """"
            insert = insert & vbCrLf
            If (i Mod 150 = 0) Then
                Open "C:\Users\Surface1\Desktop\2Percent\a.bat" For Append As #1
                Print #1, insert
                Close #1
                insert = ""
            End If
           Next i
           If (Len(insert) > 0) Then
                Open "C:\Users\Surface1\Desktop\2Percent\a.bat" For Append As #1
                Print #1, insert
                Close #1
                insert = ""
            End If
          
           
          
           
     
End Sub


Maintaining history of data

 

What is this about?

I tend to quote Fowler: “Usually when we see properties on a class, they represent questions we can ask of an object now. However there are times when we don’t want to just ask questions about a property of an object now, we also want to ask these questions about some point in the past when things may have changed. “

For example , we want to track every change that occurs at an Employee ( changing name, salary) and see the previous versions( what was the salary 2 years ago ?)

So what are the software solutions to do an audit log ? First, we should have a history table for each table that we want to maintain an audit log. E.g. Employee => EmployeeHstory

Solution 1 : Coding / Event Sourcing

That means code should maintain history himself – i.e. adding to the history table every modifications.

For Entity Framework this can be done easy, by overiding SaveChanges.

Demo at https://youtu.be/YZ9GDjFApns 

Solution 2 : Table Triggers

This can be done from database . There are triggers for insert , update, delete – and those triggers insert the data in the history table

Demo at https://youtu.be/9xsvCJ-1f7Q

Solution 3, Temporal Tables, Microsot  Sql Server 2016 :

Documentation at https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables 

Limitations at https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-table-considerations-and-limitations 

Demo at https://youtu.be/1Q3DmXJI5h8

Monitor SqlServer Stored Proc execution time

I have had the task to made something to monitor what stored procedure, in Sql Server,in most used. The problem that I have had is that the host_name is easily changed by putting, in the connection string WSID (see http://msdn.microsoft.com/en-us/library/ms178598.aspx )
The code is for sql server 2005 and I have not realized without the help of http://sqlserver.ro/forums/thread/8332.aspx
So there are the three easy steps to have the monitor proceudre in place:
1. Define an audit table :

CREATE TABLE [Audit](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ProcName] [varchar](128)
	[IdentifierAccess] [varchar](max)
	[DateAccess] [datetime] NULL DEFAULT (getdate())
) ON [PRIMARY]

2. Stored proc to insert data :

alter proc dbo.proc_Log(@procname varchar(100))

as
begin
declare @userid varchar(100)
BEGIN TRY
select @userid=client_net_address from sys.[dm_exec_connections] with(nolock)
where session_id = @@SPID
End TRY
BEGIN CATCH
--GRANT SELECT ON sys.dm_exec_connections TO the user or
--grant view server state to the user ! http://msdn.microsoft.com/en-us/library/ms186717.aspx
set @userid = SUSER_NAME()
End CATCH

INSERT INTO [Audit]
(
[ProcName]
,[IdentifierAccess]
)
VALUES
(@procname,
@userid
)
end

3. In each stored procedure put this at beginning

DECLARE @ProcName nvarchar(128)

    SET @ProcName = OBJECT_NAME(@@PROCID)

    exec proc_Log  @ProcName

And this at the end of stored proc :

    exec proc_Log  @ProcName

4. See what the procedure calling are and the time (basic)

select a1.*,'--',a2.* , datediff(ss,a2.DateAccess,a1.DateAccess) 
from [Audit] a1  inner join [Audit] a2 on
a2.ID =
( select max(ID) from [Audit] a2 where a1.ProcName= a2.ProcName and a1.IdentifierAcces=a2.IdentifierAcces and a1.ID > a2.ID )
order by 1

From there is a simple way to make a group by to have the sum * count or other relevant data ( which proceudres are most used in what times of the day, and so on)

Work from home :
1. From the previous select(put it into a Common table Expression ), see what procedure use most
a) number of uses
b) number of time
2. What is missing from Audit table definition ? (Hint :easy clustered index definition)

Sql Server Management Studio free awesome addons

The two addons that I can not live without in Sql Server and that, more , are free :

  1. SSMS Tools Pack – now 1.7.5.1 , http://www.ssmstoolspack.com/Download
  2. SQL Search – now1.0 http://www.red-gate.com/products/SQL_Search/

 

The SSMS Tools Pack maintains a history of your commands . More, it saves on the C:\SSMSTools\SQLQueryActionLog

image

 

The Sql Search finds a text in the objects in the database – very useful if you decide to change a column name and find all stored procedures that have this reference

image