SQL SERVER – Find Last Date Time Updated for a Table

 

SELECT OBJECT_NAME(OBJECT_ID) AS TableName,

 last_dateTime_update,*

FROM sys.dm_db_index_usage_stats

WHERE database_id = DB_ID( 'PRODDTA')

AND OBJECT_ID=OBJECT_ID('TBLWDCData')

Passing Parameter in a stored Proc VB.Net

 

you have to add this

    sqlComm.Parameters.AddWithValue("FirstName", txtName.Text)

Private Sub InsertNewRecord()

sqlCon = New SqlConnection(strConn)

Using (sqlCon)

            Dim sqlComm As New SqlCommand()

            sqlComm.Connection = sqlCon

sqlComm.CommandText = "InsertDataIntoTable"

            sqlComm.CommandType = CommandType.StoredProcedure

sqlComm.Parameters.AddWithValue("FirstName", txtName.Text)

            sqlComm.Parameters.AddWithValue("Surname", txtSurname.Text)

            sqlComm.Parameters.AddWithValue("Age", Integer.Parse(txtAge.Text))

            sqlCon.Open()

            sqlComm.ExecuteNonQuery()

        End Using

End Sub

Source: https://www.codeguru.com/visual-basic/using-sql-stored-procedures-with-vb-net

Get Full SQL permission List

 ;WITH 

[explicit] AS (

   SELECT [p].[principal_id], [p].[name], [p].[type_desc], [p].[create_date], [p].[is_disabled],

         [dbp].[permission_name] COLLATE SQL_Latin1_General_CP1_CI_AS [permission],

         CAST('' AS SYSNAME) [grant_through]

   FROM [sys].[server_permissions] [dbp]

   INNER JOIN [sys].[server_principals] [p] ON [dbp].[grantee_principal_id] = [p].[principal_id]

   --WHERE ([dbp].[type] IN ('CL','TO','IM','ADBO') OR [dbp].[type] LIKE 'AL%')

   --  AND [dbp].[state] IN ('G','W')

   UNION ALL

   SELECT [dp].[principal_id], [dp].[name], [dp].[type_desc], [dp].[create_date], [dp].[is_disabled], [p].[permission], [p].[name] [grant_through]

   FROM [sys].[server_principals] [dp]

   INNER JOIN [sys].[server_role_members] [rm] ON [rm].[member_principal_id] = [dp].[principal_id]

   INNER JOIN [explicit] [p] ON [p].[principal_id] = [rm].[role_principal_id]

   ),

[fixed] AS (

   SELECT [dp].[principal_id], [dp].[name], [dp].[type_desc], [dp].[create_date], [dp].[is_disabled], [p].[name] [permission], CAST('' AS SYSNAME) [grant_through]

   FROM [sys].[server_principals] [dp]

   INNER JOIN [sys].[server_role_members] [rm] ON [rm].[member_principal_id] = [dp].[principal_id]

   INNER JOIN [sys].[server_principals] [p] ON [p].[principal_id] = [rm].[role_principal_id]

   --WHERE [p].[name] IN ('sysadmin','securityadmin','bulkadmin')

   UNION ALL

   SELECT [dp].[principal_id], [dp].[name], [dp].[type_desc], [dp].[create_date], [dp].[is_disabled], [p].[permission], [p].[name] [grant_through]

   FROM [sys].[server_principals] [dp]

   INNER JOIN [sys].[server_role_members] [rm] ON [rm].[member_principal_id] = [dp].[principal_id]

   INNER JOIN [fixed] [p] ON [p].[principal_id] = [rm].[role_principal_id]

   )

SELECT DISTINCT [name], [type_desc], [create_date], [is_disabled], [permission], [grant_through]

FROM [explicit]

--WHERE [type_desc] NOT IN ('SERVER_ROLE')

--  AND [name] NOT IN ('sa','SQLDBO','SQLNETIQ')

--  AND [name] NOT LIKE '##%'

--  AND [name] NOT LIKE 'NT SERVICE%'

--  AND [name] NOT LIKE 'NT AUTHORITY%'

--  AND [name] NOT LIKE 'BUILTIN%'

UNION ALL

SELECT DISTINCT [name], [type_desc], [create_date], [is_disabled], [permission], [grant_through]

FROM [fixed]

--WHERE [type_desc] NOT IN ('SERVER_ROLE')

--  AND [name] NOT IN ('sa','SQLDBO','SQLNETIQ')

--  AND [name] NOT LIKE '##%'

--  AND [name] NOT LIKE 'NT SERVICE%'

--  AND [name] NOT LIKE 'NT AUTHORITY%'

--  AND [name] NOT LIKE 'BUILTIN%'

ORDER BY 1

OPTION(MAXRECURSION 10)

CREATE TABLE #Info([database] SYSNAME, [username] SYSNAME, [type_desc] NVARCHAR(60), [create_date] DATETIME, [permission] SYSNAME, [grant_through] SYSNAME)

DECLARE @cmd VARCHAR(MAX)

SET @cmd = ''

SELECT @cmd = @cmd + 'INSERT #Info EXEC(''

USE ['+[name]+']

;WITH 

[explicit] AS (

   SELECT [p].[principal_id], [p].[name], [p].[type_desc], [p].[create_date],

         [dbp].[permission_name] COLLATE SQL_Latin1_General_CP1_CI_AS [permission],

         CAST('''''''' AS SYSNAME) [grant_through]

   FROM [sys].[database_permissions] [dbp]

   INNER JOIN [sys].[database_principals] [p] ON [dbp].[grantee_principal_id] = [p].[principal_id]

   WHERE ([dbp].[type] IN (''''IN'''',''''UP'''',''''DL'''',''''CL'''',''''DABO'''',''''IM'''',''''SL'''',''''TO'''') OR [dbp].[type] LIKE ''''AL%'''' OR [dbp].[type] LIKE ''''CR%'''')

     AND [dbp].[state] IN (''''G'''',''''W'''')

   UNION ALL

   SELECT [dp].[principal_id], [dp].[name], [dp].[type_desc], [dp].[create_date], [p].[permission], [p].[name] [grant_through]

   FROM [sys].[database_principals] [dp]

   INNER JOIN [sys].[database_role_members] [rm] ON [rm].[member_principal_id] = [dp].[principal_id]

   INNER JOIN [explicit] [p] ON [p].[principal_id] = [rm].[role_principal_id]

   ),

[fixed] AS (

   SELECT [dp].[principal_id], [dp].[name], [dp].[type_desc], [dp].[create_date], [p].[name] [permission], CAST('''''''' AS SYSNAME) [grant_through]

   FROM [sys].[database_principals] [dp]

   INNER JOIN [sys].[database_role_members] [rm] ON [rm].[member_principal_id] = [dp].[principal_id]

   INNER JOIN [sys].[database_principals] [p] ON [p].[principal_id] = [rm].[role_principal_id]

   --WHERE [p].[name] IN (''''db_owner'''',''''db_datareader'''',''''db_datawriter'''',''''db_ddladmin'''',''''db_securityadmin'''',''''db_accessadmin'''')

   UNION ALL

   SELECT [dp].[principal_id], [dp].[name], [dp].[type_desc], [dp].[create_date], [p].[permission], [p].[name] [grant_through]

   FROM [sys].[database_principals] [dp]

   INNER JOIN [sys].[database_role_members] [rm] ON [rm].[member_principal_id] = [dp].[principal_id]

   INNER JOIN [fixed] [p] ON [p].[principal_id] = [rm].[role_principal_id]

   )

SELECT DB_NAME(), [name], [type_desc], [create_date], [permission], [grant_through]

FROM [explicit]

--WHERE [type_desc] NOT IN (''''DATABASE_ROLE'''')

UNION ALL

SELECT DB_NAME(), [name], [type_desc], [create_date], [permission], [grant_through]

FROM [fixed]

--WHERE [type_desc] NOT IN (''''DATABASE_ROLE'''')

OPTION(MAXRECURSION 10)

'');'

FROM [sys].[databases]

--WHERE [state_desc] = 'ONLINE'

EXEC (@cmd)

SELECT DISTINCT *

FROM #Info

--WHERE [username] NOT IN ('dbo','guest','SQLDBO')

--  AND [username] NOT LIKE '##%'

--  AND [database] NOT IN ('master','model','msdb','tempdb')

ORDER BY 1, 2

DROP TABLE #Info

source 

https://www.mssqltips.com/sqlservertip/6145/sql-server-permissions-list-for-read-and-write-access-for-all-databases/

How to enter PTO(Time-Off) in Kronos for your Employee

 How to enter PTO in Kronos for your Employee 

Go To Manage Schedule-> Click on Full Schedule->Select employee->right click on day->Enter time-off

-> Click on Request Time of ->Click on Apply-> after that Enter Time off-> Review and submit.

To find if a particulate table column changed in SQL Server

 

SELECT
OBJECT_NAME(a.[object_id]) as [table_Name]
,a.[name] as [column_Name]
,b.modify_date
, b.create_date
FROM [sys].[columns] a
JOIN [sys].[objects] b
ON a.[object_id] = b.[object_id]
where OBJECT_NAME(a.[object_id])='F0911'
ORDER BY b.modify_date DESC

2 Digit Year in SQL Server

FORMAT(DATEADD(year, 1, getdate()), 'yy') 

-- In Select statement

Select FORMAT(DATEADD(year, 0, getdate()), 'yy')  from PRODDTA.F0010 where ccco='00000'

also you can add one year 

FORMAT(DATEADD(year, 1, getdate()), 'yy') 

Error: Must declare a named package eclipse because this compilation unit is associated to the named module

Fix:

Just delete module-info.java at your Project Explorer tab.

https://stackoverflow.com/questions/53033899/must-declare-a-named-package-eclipse-because-this-compilation-unit-is-associated

Convert Celsius in Fahrenheit Java Eclipse

 import java.util.Scanner;

public class h1 {

public static void main(String[] args) {

Scanner in = new Scanner( System.in );

System.out.print( "Enter a temperature in Celsius: ");

double celsius = in.nextDouble();

System.out.printf("The temperature is %,.2f in Fahrenheit.\n",

(9.0/5.0*celsius) + 32 );

}

}

Sum two numbers Java eclipse

 

public class h1 {

public static void main(String[] args) {

int firstNumber, secondNumber;

java.util.Scanner in = new java.util.Scanner( System.in );

firstNumber = in.nextInt ( );

secondNumber = in.nextInt ( );

System.out.println ( firstNumber + " + " + secondNumber +

" = " + (firstNumber+secondNumber) );

}

}

TripleInteger Java class

import java.util.Scanner;

public class TripleInteger {

public static void main(String[] args) {

// TODO Auto-generated method stub

Scanner in = new Scanner( java.lang.System.in );

int n = in.nextInt();

System.out.println( "The number " + n + " tripled is " + n*3 );

}

}

Re-indexing SQL Tables

 DBCC DBREINDEX ('TBL_VERGLDATA_DD', ' ', 70);  

DBCC DBREINDEX ('tblPP_TranData', ' ', 70);  

DBCC DBREINDEX ('tblAct_PP_TranData', ' ', 70);  

DBCC DBREINDEX ('tblPPTYPE', ' ', 70);  

Why is my add-in crashing? Excel VBA

 https://support.microsoft.com/en-us/topic/why-is-my-add-in-crashing-1e02d873-04c8-4ecd-ac63-8f18507a3870

https://support.microsoft.com/en-us/office/excel-not-responding-hangs-freezes-or-stops-working-37e7d3c9-9e84-40bf-a805-4ca6853a1ff4?ui=en-us&rs=en-us&ad=us

https://social.msdn.microsoft.com/Forums/Lync/en-US/bff8e4f8-eb29-4454-8623-c1fffe195de7/why-does-excel-2013-crash-when-vba-creates-connection-to-access-2016-database?forum=accessdev

Eclipse Java error: This selection cannot be launched and there are no recent launches

Eclipse Java error: This selection cannot be launched and there are no recent launches 

How to fix the error:-

https://www.youtube.com/watch?v=m8EKjSSQRs0

final Code :-

Remove NULL from all the columns from SQL Table using Excel formula:-

Remove NULL from all the columns from SQL Table using Excel formula:-

 

1. use sp_help to get all the columns:- 

 sp_help  SSAS_AC_DRILL

2. Paste SQL tables columns in excel

3. after that use following formula in excel to create SQL code 

="update SSAS_AC_DRILL set "&A2&" ='' Where " &A2&  " is null"

you will get following as formula output 

update SSAS_AC_DRILL set LT ='' Where LT is null

4. copy and paste following in SSMS and execute:-

update SSAS_AC_DRILL set LT ='' Where LT is null

Delete Join 2 tables SQL Server

 DELETE M

  FROM MainTable M
  LEFT OUTER JOIN SecTable S     ON M.MainTableId = S.Id

where S.Id IS NOT NULL