%WINDIR%\System32\odbcconf.exe CONFIGSYSDSN "SQL Server" "DSN=macoprod|Description=macoprod|SERVER=10.2.151.18|Trusted_Connection=No|Database=macoprod" %WINDIR%\SysWOW64\odbcconf.exe CONFIGSYSDSN "SQL Server" "DSN=macoprod|Description=macoprod|SERVER=10.2.151.18|Trusted_Connection=No|Database=macoprod"
Category Archives: SQL Server
Example of an Indexed View in SQL Server
CREATE VIEW C_FINANCEENTRYPV WITH SCHEMABINDING AS SELECT ACCOUNTNUMBER ,ENTRYDATE ,Sum(IsNull(FINANCEENTRY.DEBITBASE,0)) DEBITBASE ,Sum(IsNull(FINANCEENTRY.CREDITBASE,0)) CREDITBASE ,Count_Big(*) CNT FROM dbo.FINANCEENTRY FINANCEENTRY GROUP BY ACCOUNTNUMBER, ENTRYDATE GO
CREATE UNIQUE CLUSTERED INDEX C_FINANCEENTRYPV_01 ON C_FINANCEENTRYPV ( ACCOUNTNUMBER, ENTRYDATE ) GO
SELECT * FROM C_FINANCEENTRYPV WITH (NOEXPAND)
How to Allow Remote TCP/IP Connections for SQL Server
- SQL Server Configuration Manager > SQL Server Network Configuration > Protocols for SQLEXPRESS > TCP/IP
TCP/IP Enabled
IPAll > TCP Dynamic Ports is blank
IPAll > TCP Port = 1433 - SQL Server Management Studio > Server Properties > Security
Server authentication = SQL Server and Windows Authentication mode - Services > SQL Server Browser
Enable and start - Windows Firewall with Advanced Security > Inbound Rules > New Rule… > Port
Allow the connection for the port 1433
Misc. Dates – SQL Server
SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0) AS FIRST_DAY_PREVIOUS_YEAR, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) AS FIRST_DAY_CURRENT_YEAR, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0) AS FIRST_DAY_NEXT_YEAR, DATEADD(YEAR, DATEDIFF(YEAR, -1, GETDATE()) - 1, -1) AS LAST_DAY_PREVIOUS_YEAR, DATEADD(YEAR, DATEDIFF(YEAR, -1, GETDATE()), -1) AS LAST_DAY_CURRENT_YEAR, DATEADD(YEAR, DATEDIFF(YEAR, -1, GETDATE()) + 1, -1) AS LAST_DAY_NEXT_YEAR, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0) AS FIRST_DAY_PREVIOUS_MONTH, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS FIRST_DAY_CURRENT_MONTH, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0) AS FIRST_DAY_NEXT_MONTH, DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) - 1, -1) AS LAST_DAY_PREVIOUS_MONTH, DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()), -1) AS LAST_DAY_CURRENT_MONTH, DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) + 1, -1) AS LAST_DAY_NEXT_MONTH, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 1, 0) AS YESTERDAY, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) AS TODAY, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) + 1, 0) AS TOMORROW, DATEADD(WEEK, DATEDIFF(WEEK,0,GETDATE() - 7), 0) AS PREVIOUS_MONDAY, DATEADD(WEEK, DATEDIFF(WEEK,0,GETDATE()), 0) AS CURRENT_MONDAY, DATEADD(WEEK, DATEDIFF(WEEK,0,GETDATE() + 7), 0) AS NEXT_MONDAY, DATEADD(WEEK, DATEDIFF(WEEK,0,GETDATE()), 0) - 1 AS PREVIOUS_SUNDAY, DATEADD(WEEK, DATEDIFF(WEEK,0,GETDATE() + 7), 0) - 1 AS CURRENT_SUNDAY, DATEADD(WEEK, DATEDIFF(WEEK,0,GETDATE() + 14), 0) - 1 AS NEXT_SUNDAY, DATEADD(YEAR, 1, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) AS THE_DAY_PLUS_1YEAR, DATEADD(YEAR, -1, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) AS THE_DAY_MINUS_1YEAR, DATEADD(MONTH, 1, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) AS THE_DAY_1_MONTH_AGO, DATEADD(MONTH, 2, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) AS THE_DAY_2_MONTH_AGO, DATEADD(MONTH, 3, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) AS THE_DAY_3_MONTH_AGO, DATEADD(MONTH, 4, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) AS THE_DAY_4_MONTH_AGO, DATEADD(MONTH, 5, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) AS THE_DAY_5_MONTH_AGO, DATEADD(MONTH, 6, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) AS THE_DAY_6_MONTH_AGO, DATEADD(MONTH, -1, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) AS THE_DAY_1_MONTHS_AHEAD, DATEADD(MONTH, -2, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) AS THE_DAY_2_MONTHS_AHEAD, DATEADD(MONTH, -3, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) AS THE_DAY_3_MONTHS_AHEAD, DATEADD(MONTH, -4, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) AS THE_DAY_4_MONTHS_AHEAD, DATEADD(MONTH, -5, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) AS THE_DAY_5_MONTHS_AHEAD, DATEADD(MONTH, -6, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) AS THE_DAY_6_MONTHS_AHEAD, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 2, 0) AS FIRST_DAY_2_MONTHS_AGO, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 3, 0) AS FIRST_DAY_3_MONTHS_AGO, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 4, 0) AS FIRST_DAY_4_MONTHS_AGO, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 5, 0) AS FIRST_DAY_5_MONTHS_AGO, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 6, 0) AS FIRST_DAY_6_MONTHS_AGO, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 2, 0) AS FIRST_DAY_2_MONTHS_AHEAD, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 3, 0) AS FIRST_DAY_3_MONTHS_AHEAD, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 4, 0) AS FIRST_DAY_4_MONTHS_AHEAD, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 5, 0) AS FIRST_DAY_5_MONTHS_AHEAD, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 6, 0) AS FIRST_DAY_6_MONTHS_AHEAD, DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) - 2, -1) AS LAST_DAY_2_MONTHS_AGO, DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) - 3, -1) AS LAST_DAY_3_MONTHS_AGO, DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) - 4, -1) AS LAST_DAY_4_MONTHS_AGO, DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) - 5, -1) AS LAST_DAY_5_MONTHS_AGO, DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) - 6, -1) AS LAST_DAY_6_MONTHS_AGO, DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) + 2, -1) AS LAST_DAY_2_MONTHS_AHEAD, DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) + 3, -1) AS LAST_DAY_3_MONTHS_AHEAD, DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) + 4, -1) AS LAST_DAY_4_MONTHS_AHEAD, DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) + 5, -1) AS LAST_DAY_5_MONTHS_AHEAD, DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) + 6, -1) AS LAST_DAY_6_MONTHS_AHEAD
How to Kill Active Connections to SQL Server database
ALTER DATABASE databasename SET SINGLE_USER WITH ROLLBACK IMMEDIATE ALTER DATABASE databasename SET MULTI_USER
Creating Aggregate Tables
This post demonstrates an example how to create aggregation tables in Kettle.
Let’s assume the original transformation loading delta is the following:
It reads data from source database, makes some transformations and loads data into data warehouse.
The source table JobEntry has two grouping fields project_id and employee_id and one additive measure registered_hrs. The task is to update aggregate tables (by project_id, and by employee_id) without complete rebuilding them.
Before updating the fact table in data warehouse, we need to retain the current value from the fact table (step Get previous fact value). After the fact table is updated, we are updating the aggregate tables. We calculate difference between the new and old value (step Calculate change), summarize the change of value to necessary granularity (steps Sort rows, Aggregate), and add the change to the value in the aggregate table (steps Get old agg.value, Calculate new value, Insert/update agg table). The transformation may look like this:
Demo Data (SQL Server)
The source database table:
create table JobEntry ( project_id int, employee_id int, registered_hrs numeric(22,2) )
The script changing the table (updating existing or inserting new rows):
declare @project_id int = RAND() * 3, @employee_id int = RAND() * 3, @registered_hrs numeric(22,2) = RAND() * 10 declare @cnt int = ( select COUNT(*) from JobEntry where employee_id = @employee_id and project_id = @project_id ) if @cnt = 0 begin insert JobEntry values ( @project_id, @employee_id, @registered_hrs ) end else begin update JobEntry set registered_hrs = @registered_hrs where employee_id = @employee_id and project_id = @project_id end select * from JobEntry
The transformation
The data warehouse tables:
create table f_jobentry( project_id int, employee_id int, registered_hrs decimal(22,2) ); create table f_jobentry_employee ( employee_id int, registered_hrs decimal(22,2) ); create table f_jobentry_project ( project_id int, registered_hrs decimal(22,2) );
Test
- Create the necessary tables.
- Run the script generating data.
- Run the transformation updating the fact table and aggregate tables.
- Check the output tables.
- Run the script.
- Run the transformation.
- Check the output tables.
Summary
This approach seems too complex. Maybe the complete rebuilding the aggregate tables is not bad…
How to Find Running Queries – SQL Server
SELECT CN.session_id AS SPID, ST.text AS SqlStatementText FROM sys.dm_exec_connections AS CN CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST ORDER BY CN.session_id
If a BusinessObjects document is refreshing for a long time, you might be interested in what query is currently running (to optimize it). The above code can help to figure out this.
Hierarchical (Recursive) Queries in SQL Server
This code sample demonstrates recursive queries in SQL Server.
The query returns paths of Kettle transformations.
with Parent as ( select ID_DIRECTORY , ID_DIRECTORY_PARENT , cast(DIRECTORY_NAME as varchar(255)) as DIRECTORY_PATH from R_DIRECTORY where ID_DIRECTORY_PARENT = 0 union all select TH.ID_DIRECTORY , TH.ID_DIRECTORY_PARENT , cast(Parent.DIRECTORY_PATH + '/' + DIRECTORY_NAME as varchar(255)) as DIRECTORY_PATH from R_DIRECTORY TH inner join Parent on Parent.ID_DIRECTORY = TH.ID_DIRECTORY_PARENT ) select coalesce(dir.DIRECTORY_PATH, '/') DIRECTORY_PATH , trans.NAME as TRANSFORMATION_NAME from Parent dir right join R_TRANSFORMATION trans on dir.ID_DIRECTORY = trans.ID_DIRECTORY
Lock information in SQL Server
The script gets miscellaneous information about locks including SQL.
SELECT L.request_session_id AS SPID, DB_NAME(L.resource_database_id) AS DatabaseName, L.request_mode AS LockType, L.request_status AS RequestStatus, L.resource_type AS ResourceType, L.resource_description AS ResourceDescription, L.resource_associated_entity_id AS ResourceAssociatedEntryId, -- Object O.name AS ObjectName, O.object_id AS ObjectId, -- SQL ST.text AS SqlStatementText, CN.auth_scheme AS AuthenticationMethod, -- Session ES.login_name AS LoginName, ES.host_name AS HostName, -- Transaction TST.is_user_transaction AS IsUserTransaction, AT.name AS TransactionName, AT.transaction_begin_time AS TransactionBeginTime FROM sys.dm_tran_locks L -- Object LEFT JOIN sys.objects O ON O.object_id = ( CASE WHEN resource_type IN ('PAGE', 'KEY', 'RID', 'HOBT') THEN ( SELECT P.object_id FROM sys.partitions P WHERE P.hobt_id = L.resource_associated_entity_id ) WHEN resource_type = 'OBJECT' THEN L.resource_associated_entity_id WHEN resource_type = 'ALLOCATION_UNIT' THEN ( SELECT P.object_id FROM sys.partitions P INNER JOIN sys.allocation_units AU ON AU.container_id = ( CASE WHEN AU.type IN (1,3) THEN P.hobt_id WHEN AU.type = 2 THEN P.partition_id END ) WHERE AU.allocation_unit_id = L.resource_associated_entity_id ) END ) -- SQL LEFT JOIN sys.dm_exec_connections CN ON CN.session_id = L.request_session_id CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST -- Session LEFT JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id -- Transaction LEFT JOIN sys.dm_tran_session_transactions TST ON TST.session_id = L.request_session_id LEFT JOIN sys.dm_tran_active_transactions AT ON AT.transaction_id = TST.transaction_id ORDER BY L.request_session_id