Spring ‘09 SQL Server vConference
is scheduled for April 22, 23, 24. Four tracks – SQL Server/.NET/SharePoint/Business Intelligence
check it out at http://www.vconferenceonline.com/shows/spring09/sql/
you can use VIP code VCTAF503033-0 to get £10 discount! (inaddition to any earlybird discounts)
Table Row counts Sql Server 2005
You can get the same in SSMS, by selecting ‘Reports’ option in the Database context sensitive menu
SELECT distinct Table_Name = object_name(object_id), T.TABLE_SCHEMA, Total_Rows = st.row_count FROM sys.dm_db_partition_stats st INNER JOIN information_schema.tables T on object_name(object_id) = T.TABLE_NAME where object_name(object_id) not like 'sys%' and object_name(object_id) not like 'queue%' ORDER BY object_name(object_id)
sqlcmd & dtexec params and syntax
SQLCMD
sqlcmd -S MySERVER -E -d master -i APPLY_DB_SECURITY.sql -o LOG\APPLY_DB_SECURITY.log
DTEXEC
dtexec /FILE “\\MYSERVER\BI Environments\myfolder\ETL\SSIS\MasterETL\LoadControl.dtsx”
/CONNECTION SSISControl;”\”Data Source=MYSERVER;Initial Catalog=mydb;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;\”" /MAXCONCURRENT ” -1 ” /CHECKPOINTING OFF
/SET “\Package.Variables[User::RootPath].Properties[Value]“;”\\MYSERVER\BI Environments\mpadigela_dev1\ETL”
/SET “\Package.Variables[User::ServerName].Properties[Value]“;”MYSERVERNAME”
/SET “\Package.Variables[User::EnviromentName].Properties[Value]“;”myEnvironment_”
/REPORTING EWCDI
Manipulating, Programming EXCEL in SSIS(2005) using ScriptTask -II
Following on from my previous post, this is how you’d automate Excel using SSIS. The following sample code inside a ScriptTask extracts a given worksheet from each workwook into another consolidated workbook. The ScriptTask is placed inside a ForEachLoop container that loops through given folder structure for .xls files.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.Office.Interop.Excel
Imports System.IO
Public Class ScriptMain
Public Sub Main()
'FolderPath,FullFilePath,CombinedFilePath
Dim FolderPath As String = Dts.Variables("FolderPath").Value.ToString
Dim FullFilePath As String = Dts.Variables("FullFilePath").Value.ToString
Dim CombinedFilePath As String = Dts.Variables("CombinedFilePath").Value.ToString
Dim fi As FileInfo
Dim FileCreateddate As String
If FullFilePath <> CombinedFilePath Then 'exclude combined file that we are building
Dim app As Microsoft.Office.Interop.Excel.Application
Dim SourceWb As Workbook
Dim CombinedWb As Workbook
Dim ws As Worksheet
Dim FileName As String
Dim i As Integer = 1
fi = New FileInfo(FullFilePath)
FileCreateddate = CStr(Format(fi.CreationTime, "yyyymmdd"))
FileName = Replace(fi.Name.Substring(0, fi.Name.Length - 4), " ", "") ' exclude .xls
If FileCreateddate >= CStr(Format(Today, "yyyymmdd")) Then ' file satisfies required criteria
app = New Microsoft.Office.Interop.Excel.Application
app.DisplayAlerts = False 'suppress excel warnings/alers
SourceWb = app.Workbooks.Open(FullFilePath, False, True)
CombinedWb = app.Workbooks.Open(CombinedFilePath, False)
For Each ws In SourceWb.Worksheets
If ws.Name.ToString = "Rates for SB" Then
ws.Name = FileName
ws.Copy(After:=CombinedWb.Sheets(i))
Exit For
End If
Next
SourceWb.Close(False) ' close source wb without saving
CombinedWb.Save()
CombinedWb.Close()
SourceWb = Nothing
CombinedWb = Nothing
End If
'cleanup
app.Quit()
If Not app Is Nothing Then
Runtime.InteropServices.Marshal.ReleaseComObject(app)
GC.Collect(0) 'because it's not a .net library, call GC to cleanup
End If
End If
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
Speedup SSMS and Visaul Studio
SSMS
“C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe” /nosplash /S MYSERVER /E /D MYDB
so, no splash screen, connects to MYSERVER server, MYDB database using Integrated Security.
VS
“C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe” /nosplash
Manipulating, Programming EXCEL in SSIS(2005) using ScriptTask – I
This can be achieved using Office Primary Interop Assemblies (PIAs).
Office XP PIAs can be downloaded from
STEPS:
- Instal Microsoft.Office.Interop.Excel.dll to GAC (C:\WINDOWS\assembly)
- Copy Microsoft.Office.Interop.Excel.dll to C:\WINDOWS\Microsoft.NET\Framework\v2.0.xxxxx (so SSIS ScriptTask can list it under References dialog)
- Register excel assembly — regedit /s Microsoft.Office.Interop.Excel.dll.reg
now we can add a reference to this library from ssis ScriptTask and start coding using excel objects!!
T-SQL String Concatenation using FOR XML PATH(”)
Sometimes we need to convert (concatenate) string column values for a given group into a
comma(or any character) separated list of values.
eg:
I have this:
I want this:
here is one way of doing this in T-SQL using FOR XML PATH(”) (only works with SQL Server 2005 and above)
select NoteID, STUFF(
(SELECT ',' + a.Comments AS [text()]
from Notes a
where a.NoteID = b.NoteID
Order by a.Comments
for xml PATH('')),1,1,'' ) AS Comments_Concatenated
from Notes b
group by NoteID
ORDER BY NoteID
or convert all rows into csv..
SELECT 'MP' NAME
into #tt1
UNION
SELECT 'BP' NAME
UNION
SELECT 'HP' NAME
select name from #tt1
declare @strTemp as varchar(100)
Select @strTemp = STUFF(
(SELECT ',' + a.Name AS [text()]
from #tt1 a
for xml PATH('')),1,1,'' )
print @strTemp
STUFF function removes the leading comma
Date to/from ID Conversion – TSQL
/*Convert DateTime to ID (yyyymmdd)*/
select replace(convert(char(10),getdate(),120),’-',”)
/*Convert DateID (yyyymmdd) to Date*/
SELECT CONVERT(DATETIME, CONVERT(CHAR(8), 20080922))
Help for Hierarchies – Dimensional Modelling
here is a good article by Ralph Kimball on managing ragged/variable-depth hierarchies in a data warehouse
http://www.dbmsmag.com/9809d05.html
And here is the design tip that follows on from the above article