Force ConnectionString

Sub testConnection()

    Dim strConName As String
    
    strConName = "MATLKSPRPSQD003 TestSecurityModel Model"

    'ActiveWorkbook.Connections("MATLKSPRPSQD003 TestSecurityModel Model").Name = "Benson_test"
    ActiveWorkbook.Connections("Benson_test").Name = strConName
    
    With ActiveWorkbook.Connections(strConName)
        .OLEDBConnection.Connection = "OLEDB;Provider=MSOLAP.5;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=TestSecurityModel;Data Source=MATLKSPRPSQD003;MDX Compatibility=1;Roles=HRSSC_T2_CRT_BENSON;Safety Options=2;MDX Missing Member Mode=Error"

    End With

End Sub


Sub testConnectionString()
    
    With ActiveWorkbook.Connections("MATLKSPRPSQD003 TestSecurityModel Model")
        .OLEDBConnection.Connection = "OLEDB;Provider=MSOLAP.5;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=TestSecurityModel;Data Source=MATLKSPRPSQD003;MDX Compatibility=1;Roles=HRSSC_T2_CRT_BENSON;Safety Options=2;MDX Missing Member Mode=Error"
    End With

End Sub

T-SQL Trigger example

create table Derived_Values
  (
    BusinessUnit nvarchar(100) not null
    ,Questions nvarchar(100) not null
    ,Answer nvarchar(100)
    )

go

ALTER TABLE Derived_Values ADD CONSTRAINT PK_Derived_Values
PRIMARY KEY CLUSTERED (BusinessUnit, Questions);

create table Derived_Values_Test
  (
    BusinessUnit nvarchar(150) not null
    ,Questions nvarchar(100) not null
    ,Answer nvarchar(100)
    )

go

ALTER TABLE Derived_Values_Test ADD CONSTRAINT PK_Derived_Values_Test
PRIMARY KEY CLUSTERED (BusinessUnit, Questions);

CREATE TRIGGER trgAfterInsert ON  [Derived_Values]
FOR INSERT
AS  
begin
    insert
        [Derived_Values_Test]
        (BusinessUnit,Questions,Answer)
    SELECT 
        i.BusinessUnit, i.Questions, i.Answer
    FROM 
        inserted i
end

CREATE TRIGGER dbo.Table1_Updated
ON dbo.Table1
FOR INSERT, UPDATE /* Fire this trigger when a row is INSERTed or UPDATEd */
AS BEGIN
  UPDATE dbo.Table1 SET dbo.Table1.LastUpdated = GETDATE()
  FROM INSERTED
  WHERE inserted.id=Table1.id
END

Write Differences Between NVARCHAR and VARCHAR

Write Differences Between NVARCHAR and VARCHAR

use SandBox

go

CREATE TABLE dbo.t(c NVARCHAR(32));

INSERT dbo.t(c) SELECT 'រៀន';

INSERT dbo.t(c) SELECT 'នរៀ';

INSERT dbo.t(c) SELECT N'រៀន';

SELECT c FROM dbo.t;

SELECT c FROM dbo.t WHERE c = 'រៀន';

SELECT c FROM dbo.t WHERE c = N'រៀន';

Current DateTime as a string

DECLARE @CurrentDateTime as varchar(50)

SELECT @CurrentDateTime = CAST(Year(GetDate()) as nvarchar(4)) 
+ (CASE 
WHEN Month(GetDate()) < 10 THEN '0' + CAST(Month(GetDate()) as nvarchar(2))
ELSE CAST(Month(GetDate()) as nvarchar(2))
END)
+ (CASE 
WHEN Day(GetDate()) < 10 THEN '0' + CAST(Day(GetDate()) as nvarchar(2))
ELSE CAST(Day(GetDate()) as nvarchar(2))
END)
+ '_'
+ (CASE 
WHEN DatePart(hour, GetDate()) < 10 THEN '0' + CAST(DatePart(hour, GetDate()) as nvarchar(2)) ELSE CAST(DatePart(hour, GetDate()) as nvarchar(2))
END)
+ (CASE 
WHEN DatePart(minute, GetDate()) < 10 THEN '0' + CAST(DatePart(minute, GetDate()) as nvarchar(2))
ELSE CAST(DatePart(minute, GetDate()) as nvarchar(2))
END)
+ (CASE 
WHEN DatePart(second, GetDate()) < 10 THEN '0' + CAST(DatePart(second, GetDate()) as nvarchar(2))
ELSE CAST(DatePart(second, GetDate()) as nvarchar(2))
END)

SELECT @CurrentDateTime

T-SQL to find IP Address

SELECT CONNECTIONPROPERTY('net_transport') AS net_transport, CONNECTIONPROPERTY('protocol_type') AS protocol_type, CONNECTIONPROPERTY('auth_scheme') AS auth_scheme, CONNECTIONPROPERTY('local_net_address') AS local_net_address, CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port, CONNECTIONPROPERTY('client_net_address') AS client_net_address

PostgreSQL CamelCase problem

Given an OO language in which the usual naming convention for object properties is camelCased, and an example object like this:

{
    id: 667,
    firstName: "Vladimir",
    lastName: "Horowitz",
    canPlayPiano: true
}

How should I model this structure in a PostgreSQL table?

There are three main options:

1. unquoted camelCase column names
2. quoted camelCase column names
3. unquoted (lowercase) names with underscores

They each have their drawbacks:

1. Unquoted identifiers automatically fold to lowercase. This means that you can create a table with a canPlayPiano column, but the mixed case never reaches the database. When you inspect the table, the column will always show up as canplaypiano - in psql, pgadmin, explain results, error messages, everything.
2. Quoted identifiers keep their case, but once you create them like that, you will always have to quote them. IOW, if you create a table with a "canPlayPiano" column, a SELECT canPlayPiano ... will fail. This adds a lot of unnecessary noise to all SQL statements.
3. Lowercase names with underscores are unambiguous, but they don't map well to the names that the application language is using. You will have to remember to use different names for storage (can_play_piano) and for code (canPlayPiano). It also prevents certain types of code automation, where properties and DB columns need to be named the same.

So I'm caught between a rock and a hard place (and a large stone; there are three options). Whatever I do, some part is going to feel awkward. For the last 10 years or so, I've been using option 3, but I keep hoping there would be a better solution.

T-SQL to JSON

you need to use a varchar(max) else SSMS will limit it to 2033 chars

---------

declare @FSLI_JSON as varchar(max) = (

    SELECT [col1]
, [col2]
, [col3]...
    FROM [dbo].[myTable]
    FOR JSON auto
)

SELECT @myJSON

db Backup and Restore....to make life easier

--############################################################

--Step 1. :: backup the target database

--Step 2. :: Kill connections from the source database

use mastergo

DECLARE @kill varchar(8000) = '';SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';'FROM master..sysprocesses WHERE dbid = db_id('Reference Data Warehouse')

EXEC(@kill);

--Step 3. :: Single User Mode

ALTER Database [Reference Data Warehouse] SET Single_UserGO

--Step 4. :: Disconnect the session!

--Step 5. :: Multi-User Mode

ALTER Database [Reference Data Warehouse] SET Multi_User
GO

--##############################################################

EXEC sp_change_users_login 'UPDATE_ONE','MDMDQuser','MDMDQuser'


--First, make sure that this is the problem. This will lists the orphaned users:

EXEC sp_change_users_login 'Report'

--If you already have a login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user'

--If you want to create a new login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

Check Transaction Log available space

Check Transaction Log available space

DBCC SQLPERF(logspace)

SQLAgentOperator cannot edit Jobs for other owners

Yes, you read that right.  Only the sysadmin can do this.

my_permissions

USE [sandbox]
GO

select * from sys.fn_my_permissions(NULL, 'database')

select * from sys.fn_my_permissions(NULL, 'server')

Quick View Table Structure and Contents

Viewing table Structure & Contents

USE [db_name]

go

declare @tableName as nvarchar(50) = 'test'

declare @SQLstmt as nvarchar(100) = 'SELECT top 10* FROM [dbo].[' + @tableName + ']'

SELECT obj.type_desc,  OBJECT_NAME(col.object_id) as 'table_name', col.column_id, col.name as 'column_name'

, TYPE_NAME(col.user_type_id) as 'DataType_name'

--, col.system_type_id, col.user_type_id

, col.max_length, col.[precision], col.scale, col.collation_name, col.is_nullable, col.is_ansi_padded, col.is_rowguidcol, col.is_identity

FROM sys.objects obj inner join sys.columns col on obj.object_Id=col.object_Id

WHERE obj.Name=@tableName

--AND col.name LIKE '%org%'

ORDER BY col.column_id

EXEC sp_executesql @SQLstmt

Month and Day Leading Zeros? 09 not 9

declare @REL_START_DATE as datetime = GetDate()

SELECT cast(year(@REL_START_DATE) as varchar(4)) + '-' + RIGHT('0' + cast(month(@rel_start_date) as varchar(2)),2) + '-' + right('0' + cast(day(@rel_start_date) as varchar(2)),2) + ' 00:00:00.000' as 'REL_START_DATE'

Always On

If secondary runs out of space, make sure the Always On connection is removed and re-added so that no data is lost between the two.

Check for Index on table

IF EXISTS (SELECT *  FROM sys.indexes  WHERE name='Index_Name' 
    AND object_id = OBJECT_ID('[SchmaName].[TableName]'))
  begin
    DROP INDEX [Index_Name] ON [SchmaName].[TableName];
  end

Perform Mass Table Drops

You can build up a string using the catalog views, e.g.:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += '
DROP TABLE ' 
    + QUOTENAME(s.name)
    + '.' + QUOTENAME(t.name) + ';'
    FROM sys.tables AS t
    INNER JOIN sys.schemas AS s
    ON t.[schema_id] = s.[schema_id] 
    WHERE t.name LIKE 'LG_001%';

PRINT @sql;
-- EXEC sp_executesql @sql;

To just get the list of tables, use:

SELECT s.name, t.name 
  FROM sys.tables AS t 
  INNER JOIN sys.schemas AS s 
  ON t.[schema_id] = s.[schema_id] 
  WHERE t.name LIKE 'LG_001%';

The transaction log for database is full due to 'LOG_BACKUP'

There are only 2 ways the Transaction Log will truncate itself to reuse the internal space (Virtual log files) , 1 is through checkpoint process, on the simple recovery model when the log gets 70% full. The other, is after every Log Backup under Full recovery model. Maybe something is going on with these log backups. Perhaps they are completing in SQL Agent, but not doing anything at all? Try checking the backup history table in MSDB to make sure the log backups are happening and their physical file exist
SELECT TOP (10) a.database_name[DB],a.server_name[SQL INST],b.physical_device_name,a.backup_finish_date [BKP END DATE],CASE a.type
    WHEN 'D' THEN 'FULL'
    WHEN 'L' THEN 'LOG'
    END AS 'Backup Type'
FROM dbo.backupset a
JOIN dbo.backupmediafamily b
ON a.media_set_id = b.media_set_id
WHERE database_name IN ('Your Database Here') AND type = 'L'
ORDER BY backup_finish_date DESC
Thanks

EXCEL :: Excel FIND() function

IF Cell A1 = "cfuccillo003***********00300285569" THEN Cell B2 = LEFT(A1, FIND("*",A1,1)-1)

ORACLE SQL BETWEEN two dates

SELECT * FROM ps_job

WHERE emplid = '00000000000'

  and action_dt between to_date('10/01/2014','mm-dd-yyyy') and to_date('06/30/2015','mm-dd-yyyy')

Display Column Properties

 SELECT b.name as 'schema_name', a.name, c.name as 'Column_Name', c.column_id, a.object_id, a.schema_id, a.parent_object_id, a.type, a.type_desc, a.create_date, a.modify_date
 FROM sys.columns c INNER JOIN (sys.objects a INNER JOIN sys.schemas b ON a.schema_id = b.schema_id) ON c.object_ID = a.object_id
 WHERE a.object_id IN (select object_ID from sys.columns where name like 'snapshot%')
  and b.name = 'dbo'
  and a.name = 'Flatfile_employee'
 ORDER BY a.[type], b.name, a.name