SQL SERVER CANNOT LOGIN

  SELECT sp.[name],sp.type_desc

FROM sys.server_principals sp

INNER JOIN sys.server_permissions PERM ON sp.principal_id = PERM.grantee_principal_id

WHERE PERM.state_desc = 'DENY'

SQL SERVER list Masked columns details

 select  princ.name

,       princ.type_desc

,       perm.permission_name

,       perm.class_desc

,       object_name(perm.major_id)

,       m.name as "COLUMN NAME"

,       schem.name as "SCHEMA"

from    sys.database_principals princ

left join

        sys.database_permissions perm

on      perm.grantee_principal_id = princ.principal_id

left join sys.masked_columns m

on   perm.major_id=m.object_id

left join sys.schemas schem

on perm.major_id=schem.schema_id

where perm.permission_name='UNMASK';

How to Restore a Delta Table in Databricks

Scenario.

We will make some updates and inserts on a table. After that, we restore it to its original form.

SELECT * FROM airline_passenger_csv

WHERE Age > 80

%sql

UPDATE airline_passenger_csv

SET Class="Business"

WHERE Age > 80

%sql

INSERT INTO airline_passenger_csv

VALUES(1234567,'Female','Loyal Customer',88,"Personal Travel","Eco",300,0,0, "satisfied")

%sql

DELETE FROM airline_passenger_csv

WHERE _c0=7547

So far we have updated the Class to business class. Added a user and deleted id 7547

to restore we will check the version from the DESC HISTORY airline_passenger_csv.

Below are a few examples on how to flashback queries DELTA TABLES

%sql

SELECT* FROM airline_passenger_csv

TIMESTAMP AS OF '2024-04-12T01:06:51.000+00:00'

WHERE AGE > 80

%sql

SELECT * FROM airline_passenger_csv@v2

WHERE Age > 80

Now let's say we need to restore to v2 of the table

we type

%sql

RESTORE airline_passenger_csv TO VERSION AS OF 2

How to Network Trace

 Network Trace

Disconnect the dedicated pool.

- Start CMD (Run as Admin)

- Start the process: 

netsh trace start capture=yes packettruncatebytes=512 tracefile=%temp%\%computername%_nettrace.etl maxsize=2048 filemode=circular overwrite=yes report=no

- Reproduce the issue.(Try connecting to dedicated pool through SSMS again)

- Stop the process: netsh trace stop

Oracle Restore Single table to another name

 Note:  It does not work in moving it on another schema

impdp dav4635/Wolver1n323$    tables=PPM.EP_PPM_PROJECT_ALLOCATIONS remap_table=PPM.EP_PPM_PROJECT_ALLOCATIONS:MYTABLE1 dumpfile=16restore.dmp logfile=dx.log

Troubleshoot SQL Server Email

 EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'CVTMSsQLT02 Mailer'

   ,@recipients = 'dexter.velasco@aei.com'

   ,@subject = 'Email from SQL Server'

   ,@body = 'This is my First Email sent from SQL Server :)'

   ,@importance ='HIGH'

GO

SELECT * FROM msdb.dbo.sysmail_unsentitems

SELECT * FROM msdb.dbo.sysmail_sentitems

SELECT * FROM msdb.dbo.sysmail_faileditems

EXEC msdb.dbo.sysmail_help_profile_sp;

select

 err.[description] ,

 fail.*

FROM [msdb].[dbo].[sysmail_event_log] err

 inner join [msdb].dbo.sysmail_faileditems fail

 On err.mailitem_id = fail.mailitem_id;

sysmail_help_profile_sp

ListTablesWithColumnLike

 SELECT      COLUMN_NAME AS 'ColumnName'

            ,TABLE_NAME AS  'TableName'

FROM        INFORMATION_SCHEMA.COLUMNS

WHERE       COLUMN_NAME LIKE '%whatever%'

ORDER BY    TableName

            ,ColumnName;