Read & Write Parquet file using Databrick and Spark

Save Excel Without dropping leading zeros

 https://youtu.be/O_Fy308BlEg

#Excel #SQL-Server

PowerBI run SP in DirectQuery

When I try to run a Stored Procedure in PowerBI getting this error 

Incorrect syntax near the keyword 'EXEC'. Incorrect syntax near ')'.

You may need to configure SQL Server , but after all setup , you have to do following

Create the SP, and use in OPENROWSET as following , test this is in SSMS

SELECT * FROM OPENROWSET('SQLNCLI','server=DEVSERVER;Persist Security Info=True;UID=User;PWD=Password',

'exec budget.[dbo].[SP_TB_Test_Delthis_new]')

If this is working fine create a view  as following:-

Create view [dbo].[VTest_dataTB_Delthis] as

 SELECT * FROM OPENROWSET('SQLNCLI','server=DEVSERVER;Persist Security Info=True;UID=User;PWD=Password',

'exec budget.[dbo].[SP_TB_Test_Delthis_new]')

 

use this in Power BI as following :

Ad Hoc Distributed Queries turned off as part of the security configuration

 Msg 15281, Level 16, State 1, Procedure PBIView_encrypted_Test1, Line 6 [Batch Start Line 0]

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', search for 'Ad Hoc Distributed Queries' in SQL Server Books Online.

Run Following-

EXEC sp_configure 'show advanced options', 1

RECONFIGURE

GO

EXEC sp_configure 'ad hoc distributed queries', 1

RECONFIGURE

GO

REBUILD indexes and update Stat SQL Server

Sometime when you load data, we are not able to get fast access using application. In order to fix this do following:- 

 ALTER INDEX ALL ON TBL_PlanBUDGET REBUILD WITH (ONLINE = ON, SORT_IN_TEMPDB = ON, FILLFACTOR = 80)

Update STATISTICS TBL_PlanBUDGET

GRANT Permission SQL Server

 GRANT EXECUTE, SELECT, UPDATE, INSERT, DELETE ON SCHEMA::ABC_EXT TO [MMC\JDEDEV]

Convert From date and to date in Julian , JD Edwards in VBA

 

    Dim FrmDateImp

    Dim ToDateImp

    Dim tmpToDateImp

 

   

    FrmDateImp = Right(STR(PERIOD_BEG), 2) & "/01/" & Mid(str(PERIOD_BEG), 2, 4)

    tmpToDateImp = Right(STR(PERIOD_END), 2) & "/01/" & Mid(str(PERIOD_END), 2, 4)

    ToDateImp = DateSerial(YEAR(tmpToDateImp), Month(tmpToDateImp) + 1, 0)

 

   

    Dim FrmDate_DGJ As Long

    Dim ToDate_DGJ As Long

  FrmDate_DGJ = ((DatePart("YYYY", FrmDateImp) - 1900) * 1000) + DatePart("y", FrmDateImp)

ToDate_DGJ = ((DatePart("YYYY", ToDateImp) - 1900) * 1000) + DatePart("y", ToDateImp)

QryDateSummaryStr = "GLDGJ BETWEEN '" & FrmDate_DGJ & "' and '" & ToDate_DGJ & "'"

Change Date Format Excel Formula

 Change date format excel formula, this is good for developer too, who wants to create SQL statement from excel cell.

=TEXT(B3,"mm/dd/yyyy")

=TEXT(B3,"mm-dd-yyyy")

Dynamic SQL table columns for dates in Aging report, SQL Server

 

How to create Dynamic SQL table columns for dates in Aging report JD Edwards, SQL Server 

DECLARE @SQL_STR VARCHAR(8000)

DECLARE @DueDate VARCHAR(80)

DECLARE @DueDate14 VARCHAR(80)

DECLARE @DueDate28 VARCHAR(80)

DECLARE @DueDateAfter28 VARCHAR(80)

SET @DueDate =  '['+'Due as of ' +CONVERT(VARCHAR(10), GETDATE()+1, 101)+'],'

SET @DueDate14 =  '['+'Due as of ' +CONVERT(VARCHAR(10), GETDATE()+14, 101)+'],'

SET @DueDate28 =  '['+'Due as of ' +CONVERT(VARCHAR(10), GETDATE()+28, 101)+'],'

SET @DueDateAfter28 =  '['+'Due After ' +CONVERT(VARCHAR(10), GETDATE()+28, 101)+'],'

SET @SQL_STR = 

'SELECT RPAN8 AS Supplier_Number, sup.ABALPH AS Supplier_name, 

RPPST AS Pay_Stat, RPPYIN AS Pay_Inst, RPAN8 AS Payee_Number, PR.ABALPH AS Payee_Name,

RPDCT AS Doc_Type, RPDOC AS Doc_Number, RPSFX AS ITM, RPKCO AS CO, RPVINV AS Invoice_Number,

[dbo].[JDT_JTG](RPDIVJ) AS Invoice_Date,

[dbo].[JDT_JTG](RPDDJ) AS Due_Date,

CASE WHEN [dbo].[JDT_JTG](RPDDJ) BETWEEN CONVERT(VARCHAR(10), GETDATE(), 101) and  CONVERT(VARCHAR(10), GETDATE()+1, 101)

     THEN RPAG/100

ELSE 0 END '+ @DueDate + '

CASE WHEN [dbo].[JDT_JTG](RPDDJ) BETWEEN CONVERT(VARCHAR(10), GETDATE()+2, 101) and  CONVERT(VARCHAR(10), GETDATE()+14, 101)

     THEN RPAG/100

ELSE 0 END '+ @DueDate14 + '

CASE WHEN [dbo].[JDT_JTG](RPDDJ) BETWEEN CONVERT(VARCHAR(10), GETDATE()+15, 101) and  CONVERT(VARCHAR(10), GETDATE()+28, 101)

     THEN RPAG/100

ELSE 0 END '+ @DueDate28 + '

CASE WHEN [dbo].[JDT_JTG](RPDDJ) > CONVERT(VARCHAR(10), GETDATE()+28, 101) 

     THEN RPAG/100

ELSE 0 END AS '+ @DueDateAfter28 + '

CASE WHEN [dbo].[JDT_JTG](RPDDJ) >= CONVERT(VARCHAR(10), GETDATE(), 101) 

     THEN RPAG/100

ELSE 0 END AS Total_Due

FROM MFDB.PRODDTA.F0411

LEFT OUTER JOIN MFDB.PRODDTA.F0101 Sup ON Sup.ABAN8 = RPAN8

LEFT OUTER JOIN MFDB.PRODDTA.F0101 PR ON PR.ABAN8 = RPAN8

WHERE RPFY = 22 

--AND rpan8 = 642564 

AND RPPST = ''A'';

'

--PRINT (@SQL_STR)

EXEC (@SQL_STR)

Convert Calendar Date to Julian Date Excel VBA (JD Edwards style Date)

Convert Calendar Date to Julian Date Excel VBA (JD Edwards style Date)

   Dim myDate As Date

    myDate = "07/23/2022"

    Debug.Print (((DatePart("YYYY", myDate)) - 1900) * 1000) + DatePart("y", myDate)

find a table column in stored procedure in SQL Server

 SELECT  distinct OBJECT_NAME (id)

FROM syscomments

WHERE text LIKE '%GLMCU%'

GO

DROP INDEX SQL Server

 DROP INDEX SQL Server if exists

DROP INDEX IF EXISTS IDX_tbl_XXXData_RR ON tbl_XXXData_RR;

How to load Sql queries in SQL Server table

 Sometimes we need to load SQL code in a sql table and run that code in any application. you can load long SQL code using following:-

1. make your query in one line.

2.Open an excel and add a column name in excel, paste that query in a excel cell.

3. Load this excel in SQL server using sql import wizard.

4.  Create an insert statement to load in your main table with all the fields.

INSERT INTO table_sql(sqlname,sqldesc,date,user,sqlcode)

Select sqlname,sqldesc,date,user, Column01 sqlcode from YourUpoadedTable

Schema LEVEL SECURITY ,SQL Server

SET NOCOUNT ON DECLARE @sql VARCHAR(MAX) DECLARE @strHTML VARCHAR(MAX) DECLARE @i INT DECLARE @rc INT DECLARE @dbname VARCHAR(400) -----------------Print header of the report-------------------- SELECT @strHTML = '
' PRINT @strHTML -----------------Login information------------------------------------------------------------- SELECT ROW_NUMBER () OVER (ORDER BY name) AS RowNumber, name, dbname,language, CONVERT(CHAR(10),CASE denylogin WHEN 1 THEN 'X' ELSE '--' END) AS IsDenied, CONVERT(CHAR(10),CASE isntname WHEN 1 THEN 'X' ELSE '--' END) AS IsWinAuTHENtication, CONVERT(CHAR(10),CASE isntgroup WHEN 1 THEN 'X' ELSE '--' END) AS IsWinGroup, createdate,UPDATEdate, CONVERT(VARCHAR(2000), CASE sysadmin WHEN 1 THEN 'sysadmin,' ELSE '' END + CASE securityadmin WHEN 1 THEN 'securityadmin,' ELSE '' END + CASE serveradmin WHEN 1 THEN 'serveradmin,' ELSE '' END + CASE setupadmin WHEN 1 THEN 'setupadmin,' ELSE '' END + CASE processadmin WHEN 1 THEN 'processadmin,' ELSE '' END + CASE diskadmin WHEN 1 THEN 'diskadmin,' ELSE '' END + CASE dbcreator WHEN 1 THEN 'dbcreator,' ELSE '' END + CASE bulkadmin WHEN 1 THEN 'bulkadmin' ELSE '' END ) AS ServerRoles INTO #syslogins FROM master..syslogins WITH (nolock) ORDER BY name SET @rc = @@rowcount SELECT @strHTML = '
Server ' + @@servername + '
' PRINT @strHTML PRINT '

' -- Query the data only if there are rows: IF @rc = 0 BEGIN PRINT '' PRINT '' END ELSE BEGIN UPDATE #syslogins SET ServerRoles = SUBSTRING(ServerRoles,1,LEN(ServerRoles)-1) WHERE SUBSTRING(ServerRoles,LEN(ServerRoles),1) = ',' UPDATE #syslogins SET ServerRoles = '--' WHERE LTRIM(RTRIM(ServerRoles)) = '' PRINT '

Logins information

There are no logins on this server

' PRINT '' PRINT '' SET @i = 1 WHILE @i <= @rc BEGIN SELECT @strHTML = '' + '' + '' + '' + '' + '' + '' + '' + '' + '' FROM #syslogins WHERE RowNumber = @i PRINT @strHTML SET @i = @i + 1 END PRINT '

Logins information
Login Name	Default DB	Language	Denied acess?	Windows Auth?	Window group?	Date created	Date UPDATEd	Server roles
' + CONVERT(VARCHAR(50),name) + '	' + CONVERT(VARCHAR(50),CASE ISNULL(dbname,'--') WHEN '' THEN '--' ELSE ISNULL(dbname,'--') END) + '	' + CONVERT(VARCHAR(50),ISNULL(language,'--')) + '	' + CONVERT(VARCHAR(10),ISNULL(IsDenied,'--')) + '	' + CONVERT(VARCHAR(10),ISNULL(IsWinAuTHENtication,'--')) + '	' + CONVERT(VARCHAR(10),ISNULL(IsWinGroup,'--')) + '	' + CONVERT(VARCHAR(30),ISNULL(createdate,'--')) + '	' + CONVERT(VARCHAR(30),ISNULL(UPDATEdate,'--')) + '	' + CONVERT(VARCHAR(100),ISNULL(ServerRoles,'--')) + '

Back To Top ^

' PRINT'

' END DROP TABLE #syslogins ---------------Fetch data per database------------------------------------------------- CREATE TABLE #LoginMap (LoginName VARCHAR(200), UserName VARCHAR(200) NULL) CREATE TABLE #RoleUser (RoLEName VARCHAR(200), UserName VARCHAR(200) NULL) CREATE TABLE #ObjectPerms (RowNumber INT IDENTITY, UserName VARCHAR(50), PerType VARCHAR(10),PermName VARCHAR(30), SchemaName VARCHAR(50), ObjectName VARCHAR(100), ObjectType VARCHAR(20), ColName VARCHAR(50), IsGrantOption VARCHAR(10)) CREATE TABLE #DatabasePerms (RowNumber INT IDENTITY,UserName VARCHAR(50),PermType VARCHAR(20),PermName VARCHAR(50),IsGrantOption VARCHAR(5)) DECLARE dbs CURSOR FOR SELECT name FROM master..sysdatabases ORDER BY name OPEN dbs FETCH NEXT FROM dbs INTO @dbname WHILE @@FETCH_STATUS = 0 BEGIN TRUNCATE TABLE #LoginMap TRUNCATE TABLE #RoleUser TRUNCATE TABLE #ObjectPerms TRUNCATE TABLE #DatabasePerms SELECT @strHTML = '
Database ' + @dbname + '
' PRINT @strHTML -----------------Mapping of logins to users------------------ EXEC(' INSERT INTO #LoginMap SELECT login.loginname,users.name FROM ['+ @dbname+'].dbo.sysusers users INNER JOIN [master].[dbo].[syslogins] login ON users.[sid] = login.[sid] WHERE users.uid < 16382 and users.name not in (''public'',''dbo'',''guest'') ') SET @strHTML = '' PRINT '

' --Query the data only if there are rows IF NOT EXISTS (SELECT 1 FROM #LoginMap) BEGIN PRINT '' PRINT '' END ELSE BEGIN PRINT '' PRINT '' SELECT @strHTML = @strHTML + '' + CHAR(10) FROM #LoginMap ORDER BY LoginName PRINT @strHTML END PRINT '

Mapping of logins to users
There are no mappings in this database
Mapping of logins to users
Login Name	User Name
' + CONVERT(VARCHAR(50),LoginName) + '	' + CONVERT(VARCHAR(50),ISNULL(UserName,'')) + '

Back To Top ^

' ----------------SQL roles per user------------------ EXEC ('INSERT INTO #RoleUser SELECT b.name AS Role_name, a.name AS User_name ' + 'FROM ['+ @dbname+']..sysusers a ' + 'INNER JOIN ['+ @dbname+ ']..sysmembers c on a.uid = c.memberuid ' + 'INNER JOIN ['+ @dbname+ ']..sysusers b ON c.groupuid = b.uid ' + 'WHERE a.name <> ''dbo''' ) SET @strHTML = '' PRINT '

' -- Query the data only if there are rows: IF NOT EXISTS(SELECT 1 FROM #RoleUser) BEGIN PRINT '' PRINT '' END ELSE BEGIN PRINT '' PRINT '' SELECT @strHTML = @strHTML + '' + CHAR(10) FROM #RoleUser ORDER BY RoLEName PRINT @strHTML END PRINT '

Roles per user
There are no users mapped to roles in this database
Roles per user
Role Name	User Name
' + CONVERT(VARCHAR(50),RoLEName) + '	' + CONVERT(VARCHAR(50),ISNULL(UserName,'')) + '

Back To Top ^

' ----------------Database level Permissions------------------------- EXEC ('INSERT INTO #DatabasePerms (UserName,PermType,PermName,IsGrantOption) SELECT usr.name, CASE WHEN perm.state <> ''W'' THEN perm.state_desc ELSE ''GRANT'' END, perm.permission_name, CASE WHEN perm.state != ''W'' THEN ''--'' ELSE ''X'' END AS IsGrantOption FROM ['+@dbname+'].sys.database_permissions AS perm INNER JOIN ['+@dbname+'].sys.database_principals AS usr ON perm.grantee_principal_id = usr.principal_id WHERE perm.major_id = 0 ORDER BY usr.name, perm.permission_name ASC, perm.state_desc ASC' ) SET @rc = @@rowcount PRINT '

' -- Query the data only if there are rows: IF NOT EXISTS(SELECT 1 FROM #DatabasePerms) BEGIN PRINT '' PRINT '' END ELSE BEGIN PRINT '' PRINT '' SET @i = 1 WHILE @i <= @rc BEGIN SELECT @strHTML = '' + '' + '' + ''+ '' FROM #DatabasePerms WHERE Rownumber = @i PRINT @strHTML SET @i = @i + 1 END END PRINT '

Database level permissions
There are no specific permissions on the database level
Database level permissions
User Name	Permission type	Permission Name	Grant option?
' + CONVERT(VARCHAR(50),UserName) + '	' + CONVERT(VARCHAR(50),ISNULL(PermType,'--')) + '	' + CONVERT(VARCHAR(50),ISNULL(PermName,'--')) + '	' + CONVERT(VARCHAR(5),ISNULL(IsGrantOption,'--')) + '

Back To Top ^

' ----------------Database object Permissions------------------------- EXEC ('INSERT INTO #ObjectPerms (UserName,PerType,PermName,SchemaName,ObjectName,ObjectType,ColName,IsGrantOption) SELECT usr.name AS UserName, CASE WHEN perm.state <> ''W'' THEN perm.state_desc ELSE ''GRANT'' END AS PerType, perm.permission_name,USER_NAME(obj.schema_id) AS SchemaName, obj.name AS ObjectName, CASE obj.Type WHEN ''U'' THEN ''Table'' WHEN ''V'' THEN ''View'' WHEN ''P'' THEN ''Stored Proc'' WHEN ''FN'' THEN ''Function'' ELSE obj.Type END AS ObjectType, CASE WHEN cl.column_id IS NULL THEN ''--'' ELSE cl.name END AS ColName, CASE WHEN perm.state = ''W'' THEN ''X'' ELSE ''--'' END AS IsGrantOption FROM ['+@dbname+'].sys.database_permissions AS perm INNER JOIN ['+@dbname+'].sys.objects AS obj ON perm.major_id = obj.[object_id] INNER JOIN ['+@dbname+'].sys.database_principals AS usr ON perm.grantee_principal_id = usr.principal_id LEFT JOIN ['+@dbname+'].sys.columns AS cl ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id WHERE obj.Type <> ''S'' ORDER BY usr.name, perm.state_desc ASC, perm.permission_name ASC' ) SET @rc = @@rowcount PRINT '

' -- Query the data only if there are rows: IF NOT EXISTS(SELECT 1 FROM #ObjectPerms) BEGIN PRINT '' PRINT '' END ELSE BEGIN PRINT '' PRINT '' SET @i = 1 WHILE @i <= @rc BEGIN SELECT @strHTML = '' + '' + '' + '' + '' + '' + '' + '' FROM #ObjectPerms WHERE Rownumber = @i PRINT @strHTML SET @i = @i + 1 END END PRINT '

Object permissions
There are no specific permissions to objects in this database
Object permissions
User Name	Permission type	Permission Name	Schema Name	Object Name	Object type type	Column Name	Grant option?
' + CONVERT(VARCHAR(50),UserName) + '	' + CONVERT(VARCHAR(50),ISNULL(PerType,'--')) + '	' + CONVERT(VARCHAR(50),ISNULL(PermName,'--')) + '	' + CONVERT(VARCHAR(50),ISNULL(SchemaName,'--')) + '	' + CONVERT(VARCHAR(50),ISNULL(ObjectName,'--')) + '	' + CONVERT(VARCHAR(30),ISNULL(ObjectType,'--')) + '	' + CONVERT(VARCHAR(50),ISNULL(ColName,'--')) + '	' + CONVERT(VARCHAR(5),ISNULL(IsGrantOption,'--')) + '

Back To Top ^

' FETCH NEXT FROM dbs INTO @dbname END ---------------Close cursor and drop all temporary objects------------- CLOSE dbs DEALLOCATE dbs DROP TABLE #LoginMap DROP TABLE #RoleUser DROP TABLE #ObjectPerms DROP TABLE #DatabasePerms PRINT '' GO https://www.mssqltips.com/sqlservertip/1818/script-to-auto-generate-a-security-report-for-your-sql-server-instance/

Crystal Report Check box

Crystal Report does not have built in support to display a check box for Boolean operators or to display a radio button, but it can be achieved as explained below: The simplest way to accomplish this task is to 1) Edit the display string of the value in Format Editor as shown below. https://blogs.sap.com/2012/05/18/how-to-display-a-check-box-and-a-radio-button-in-crystal-report-2011/

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