Posts Categorized: SQL Server

Creating ODBC system DNS with command line

Posted by & filed under Command Line, SQL Server.

%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"

Example of an Indexed View in SQL Server

Posted by & filed under 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

Posted by & filed under 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 […]

Misc. Dates – SQL Server

Posted by & filed under 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()) […]

Creating Aggregate Tables

Posted by & filed under Kettle, SQL Server.

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 […]

How to Find Running Queries – SQL Server

Posted by & filed under 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

Posted by & filed under 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 […]

Lock information in SQL Server

Posted by & filed under 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, […]