MS Flow tips

Admin page: https://admin.flow.microsoft.com

 

Compare date with today in SQL Query:

select [UserId],[FullName],[MissingDays] from raw.tidsoptimists where CONVERT(varchar, RecordedDate, 23) = CONVERT(varchar, getdate(), 23)

Convert to JSON: Remember to use “Use sample payload to generate schema” to get Schema

Get current date (use expression):

utcNow(‘yyyy-MM-dd’)
Get JSON result as table (add a new step and use result from parse JSON step, then you will be able to see the result as table (including all rows)):
@{body(‘JSON_for_Tids_Optimists’)?[‘ResultSets’]?[‘Table1’]}
Find DateIff from today with another date in SQL Query:
SELECT DATEDIFF (day, CONVERT(date, ‘@{items(‘Apply_to_each_2’)?[‘Year’]}-@{items(‘Apply_to_each_2’)?[‘Month’]}-01′), GetDate()) AS TodayDiff
Get TodayDiff above:
1. Parse to JSON
2. Take value from JSON like this:
first(body(‘JSON_DateDiff’)?[‘ResultSets’]?[‘Table1’])?[‘TodayDiff’]
3. You can also compare it with another number, such as:
greater(first(body(‘JSON_DateDiff’)?[‘ResultSets’]?[‘Table1’])?[‘TodayDiff’], 35)
.

 

 

What is the DATABASE_URL in Postgres

Hi!

When getting started on Heroku with Node.js here, You will have a new database in  Postgres.

Type:

        heroku config

and here is what you have:

DATABASE_URL: postgres://tpkhetigbqignwgqid:2d866e51149688dbfd15eafa2f3aae18f81697418e7a73e72625a1ac21c008a4@ec2-54-225-227-125.compute-1.amazonaws.com:5432/dftroisg8ps8r9

So, what is the database url mean? Hmm, it is a combination of username,password, host and database name

here is the syntax:

var conString = "postgres://UserName:Password@Host:5432/Database";

It means:

UserName = tpkhetigbqignwgqid
Password = 2d866e51149688dbfd15eafa2f3aae18f81697418e7a73e72625a1ac21c008a4
Host = ec2-54-225-227-125.compute-1.amazonaws.com
Database = dftroisg8ps8r9

And here is how you connect it to your pgAdmin:

connecttopostgres

connecttopostgresconnectionstring

Congratulation, you have successful to connect to the first Postgres database ever.

Drop all tables in a SQL Server database (Azure Friendly!)

Following Ed Spencer’s Blog:

First, remove constrain fist:

1:  while(exists(select 1 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE='FOREIGN KEY'))  
2:  begin  
3:   declare @sql nvarchar(2000)  
4:   SELECT TOP 1 @sql=('ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME  
5:   + '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']')  
6:   FROM information_schema.table_constraints  
7:   WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'  
8:   exec (@sql)  
9:   PRINT @sql  
10:  end  

Then, drop all tables:

1:  while(exists(select 1 from INFORMATION_SCHEMA.TABLES where TABLE_NAME != '__MigrationHistory'))  
2:  begin  
3:   declare @sql nvarchar(2000)  
4:   SELECT TOP 1 @sql=('DROP TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME  
5:   + ']')  
6:   FROM INFORMATION_SCHEMA.TABLES  
7:   WHERE TABLE_NAME != '__MigrationHistory'  
8:  exec (@sql)  
9:   PRINT @sql  
10:  end  

Note: Copy the results of these command to use in next time.

Delete all data in database (when you have FKs)

Niikola have a very nice post about this in his blog. I only want to tell more how to use it (first solution) with example images

First, change query result to text:
SelectQueryMode

copy his code:

 SET NOCOUNT ON  
 GO  
 SELECT 'USE [' + db_name() +']';  
 Print 'GO'  
 SELECT 'ALTER TABLE ' +  
     '[' + s.name + '].[' + t.name + ']' +  
     ' DROP CONSTRAINT [' + f.name +']' +   
        CHAR(13) + 'GO'  
  FROM sys.foreign_keys f  
  INNER JOIN sys.TABLES t ON f.parent_object_id=t.object_id  
  INNER JOIN sys.schemas s ON t.schema_id=s.schema_id  
  WHERE t.is_ms_shipped=0;   
 SELECT 'TRUNCATE TABLE ' + '[' + s.name + '].[' + t.name + ']' +   
        CHAR(13) + 'GO'  
  FROM sys.TABLES t  
  INNER JOIN sys.schemas s ON t.schema_id=s.schema_id  
  WHERE t.is_ms_shipped=0;   
 SELECT 'ALTER TABLE ' +  
     '[' + s.name + '].[' + t.name + ']' +  
     ' ADD CONSTRAINT [' + f.name + ']' +  
     ' FOREIGN KEY (' +      
     Stuff( (SELECT ', ['+col_name(fk.parent_object_id, fk.parent_column_id) +']'  
          FROM sys.foreign_key_columns fk  
         WHERE constraint_object_id = f.object_id  
         ORDER BY constraint_column_id  
          FOR XML Path('')  
       ), 1,2,'') + ')' +  
     ' REFERENCES [' +  
     object_schema_name(f.referenced_object_id)+'].['+object_name(f.referenced_object_id) + '] (' +  
     Stuff((SELECT ', ['+col_name(fc.referenced_object_id, fc.referenced_column_id)+']'  
         FROM sys.foreign_key_columns fc  
         WHERE constraint_object_id = f.object_id  
         ORDER BY constraint_column_id  
          FOR XML Path('')),  
        1,2,'') +  
     ')' +  
     ' ON DELETE ' + REPLACE(f.delete_referential_action_desc, '_', ' ') +  
     ' ON UPDATE ' + REPLACE(f.update_referential_action_desc , '_', ' ') collate database_default +   
        CHAR(13) + 'GO'  
  FROM sys.foreign_keys f  
  INNER JOIN sys.TABLES t ON f.parent_object_id=t.object_id  
  INNER JOIN sys.schemas s ON t.schema_id=s.schema_id  
  WHERE t.is_ms_shipped=0;  

and run it in Text mode of SQL Server Management Studio
Copy_Result

then, copy text results and open new query to execute it
Run_scripts

Now, the database is empty!