Generate Entity Framework update scripts from migrations

This is how you generate Entity Framework update scripts from migrations.

Note: this is a very simplified post that doesn’t generate a very complicated database script.

So you already have an initial database migration in your project. If you don’t go Google how to get started.

I’ll start by generating an SQL script for my initial migration.

Here is part of my initial migration in C#:












I will now generate the script for this but running this command in the Package Manager Console:

Update-Database -Script -SourceMigration: $InitialDatabase -TargetMigration: Initial






Make sure you select the correct Default Project in the dropdown shown in the above picture.

Here is the SQL script:



























Now I will update my model with a new property:




I then ran the following to create my new C# migration:

Add-Migration AddedAProperty -StartUpProjectName User.DbResourceAccess







Which created this new C# file:










Next I will run this:

Update-Database -Script -SourceMigration: $InitialDatabase -TargetMigration: AddedAProperty

Which created the following script:








You could then apply this to a production database for example.
I’m not sure you would want to insert into a __MigrationHistory table on production though.

thanks
Russ

Add SendGrid email to SQL database mail

How to configure database mail with SendGrid and use it for SSIS agent jobs.

Note: This is more for me as a reminder for the future.

USE master;  
EXECUTE sp_configure ‘show advanced options’,1;  
RECONFIGURE WITH OVERRIDE;  
sp_configure ‘Database Mail XPs’,1;  
RECONFIGURE;  

USE msdb;  
EXECUTE msdb.dbo.sysmail_add_profile_sp  
@profile_name = ‘EmailAdmin’,
@description = ‘Profile for sending Automated DBA Notifications’;

EXECUTE msdb.dbo.sysmail_add_account_sp  
@account_name = ‘SendGridSQLAlerts’,
@description = ‘Account for Automated DBA Notifications’,
@email_address = ‘‘,
@display_name = ‘SendGrid SQL Alerts’,
@mailserver_name = ‘smtp.sendgrid.net’,
@username = ‘‘,
@password = ‘‘,
@port = 25

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp  
@profile_name = ‘EmailAdmin’,
@account_name = ‘SendGridSQLAlerts’,
@sequence_number = 1

USE [msdb];  
EXEC msdb.dbo.sp_set_sqlagent_properties @databasemail_profile=N’EmailAdmin’;  

EXEC msdb.dbo.sp_add_operator @name=N’EmailOperator’,  
    @enabled=1, 
    @pager_days=0, 
    @email_address=N’

thanks
Russ

Reconnect an SQL login to a user

Sometimes a user in a database gets disconnected from a server login and you can re-map the login to the database as the user is already there.

This usually happens after you restore a database to a server.

So the following gived you a report of all the disconnected users:

sp_change_users_login report

And this remaps a login(tramdba(server login)) to a user(tramdba(user))

sp_change_users_login update_one,,

thanks

RuSs

Finding Duplicates with SQL

Info was found here:
http://www.petefreitag.com/item/169.cfm

Here's a handy query for finding duplicates in a table. Suppose you

want to find all email addresses in a table that exist more than once:

SELECT email,
COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )

You could also use this technique to find rows that occur exactly once:

SELECT email
FROM users
GROUP BY email
HAVING ( COUNT(email) = 1 )

RuSs

Create SQL 2005 pivot table

CREATE TABLE #FinalTable
(
[Name] varchar(10),
[Month] varchar(10),
amount float
)

Insert into #finaltable values (‘Ray’,’Jan’,1)
Insert into #finaltable values (‘Ray’,’Feb’,2)
Insert into #finaltable values (‘Ray’,’Mar’,3)

Insert into #finaltable values (‘Eimaer’,’Jan’,5)
Insert into #finaltable values (‘Eimaer’,’Feb’,6)
Insert into #finaltable values (‘Eimaer’,’Mar’,7)

select
[Name],
isnull(sum([Jan]),0) as ‘Jan’,
isnull(sum([Feb]),0) as ‘Feb’,
isnull(sum([Mar]),0) as ‘MAr’
from #FinalTable
AS Data PIVOT(
SUM( amount)
FOR [Month] IN
([Jan],[Feb],[Mar])

) AS PVT

group by [Name]

drop table #FinalTable