TODO:
Have you ever wanted to connect to a SQL Azure Database from .Net and get a Access Denied error? The issue is you need to create a user to use in your connection strings.
SOLUTION:
--run on master DB
CREATE LOGIN myuser WITH password='mypassword';
--run on Application DB
CREATE USER myuser FROM LOGIN myuser;
exec sp_addrolemember db_datareader, myuser
go
exec sp_addrolemember db_datawriter, myuser
NOTES:
You cannot use the 'use' keyword, so you need to open a new query window for each database. Once complete, use this user / pass in your code and you can connect to the DB.
TODO:
Have you ever wanted to keep the last X records for a Customer, Employee, Order, etc?
SOLUTION:
DELETE c FROM (
SELECT *, rn=row_number() OVER (PARTITION BY CustomerId ORDER BY Id desc)
FROM CustomerHistory
) c
WHERE rn > 3
NOTES:
The SQL above will keep the last 3 records in the table CustomerHistory. The Grouping is by CustomerId, and Id is an Identity column, which ensures we keep the last 3 records (order by desc). If you want to test it, just execute the statement as a select (Change DELETE c to SELECT CustomerId, Id, rn)
TODO:
Have you ever wanted to call a stored procedure in C# that has output parameters?
SOLUTIONS:
System.Data.Objects.ObjectParameter param1 = new ObjectParameter("paraminsp1", typeof(bool));
System.Data.Objects.ObjectParameter param2 = new ObjectParameter("paraminsp2", 0.000); //default so EF works
using (MyEntities MyDatabase = new MyEntities())
{
MyDatabase.sp_name(x, 2, param1, param2);
}
NOTES:
There are no notes on this topic
TODO:
You have table A, and Table B. Table A has a foreign key to table B, and table B has a foreign key to table A. You need to delete data from table A, but cannot because of the foreign key on Table B.
SOLUTION:
ALTER TABLE employee NOCHECK CONSTRAINT ALL
delete employee where id=100
ALTER TABLE employee WITH CHECK CHECK CONSTRAINT ALL
NOTES:
This will allow you to delete data when there arecircular foreign keys.
TODO:
Have you ever wanted to have a table of US states and not have to type them in. If so, run the SQL below. It assumes you have a table named 'State', with fields of StateId (identity), Abbreviation, and Name.
SOLUTION:
insert into state(abbreviation,name) values('AL','Alabama');
insert into state(abbreviation,name) values('AK','Alaska');
insert into state(abbreviation,name) values('AS','American Somoa');
insert into state(abbreviation,name) values('AZ','Arizona');
insert into state(abbreviation,name) values('AR','Arkansas');
insert into state(abbreviation,name) values('AE','Armed Forces Africa, Canada, Middle East, Europe');
insert into state(abbreviation,name) values('AA','Armed Forces America (except Canada)');
insert into state(abbreviation,name) values('AP','Armed Forces Pacific');
insert into state(abbreviation,name) values('CA','California');
insert into state(abbreviation,name) values('CO','Colorado');
insert into state(abbreviation,name) values('CT','Connecticut');
insert into state(abbreviation,name) values('DE','Delaware');
insert into state(abbreviation,name) values('DC','District of Columbia');
insert into state(abbreviation,name) values('FM','Federated States of Micronesia');
insert into state(abbreviation,name) values('FL','Florida');
insert into state(abbreviation,name) values('GA','Georgia');
insert into state(abbreviation,name) values('GU','Guam');
insert into state(abbreviation,name) values('HI','Hawaii');
insert into state(abbreviation,name) values('ID','Idaho');
insert into state(abbreviation,name) values('IL','Illinois');
insert into state(abbreviation,name) values('IN','Indiana');
insert into state(abbreviation,name) values('IA','Iowa');
insert into state(abbreviation,name) values('KS','Kansas');
insert into state(abbreviation,name) values('KY','Kentucky');
insert into state(abbreviation,name) values('LA','Louisiana');
insert into state(abbreviation,name) values('ME','Maine');
insert into state(abbreviation,name) values('MH','Marshall Islands');
insert into state(abbreviation,name) values('MD','Maryland');
insert into state(abbreviation,name) values('MA','Massachusetts');
insert into state(abbreviation,name) values('MI','Michigan');
insert into state(abbreviation,name) values('MN','Minnesota');
insert into state(abbreviation,name) values('MS','Mississippi');
insert into state(abbreviation,name) values('MO','Missouri');
insert into state(abbreviation,name) values('MT','Montana');
insert into state(abbreviation,name) values('NE','Nebraska');
insert into state(abbreviation,name) values('NV','Nevada');
insert into state(abbreviation,name) values('NH','New Hampshire');
insert into state(abbreviation,name) values('NJ','New Jersey');
insert into state(abbreviation,name) values('NM','New Mexico');
insert into state(abbreviation,name) values('NY','New York');
insert into state(abbreviation,name) values('NC','North Carolina');
insert into state(abbreviation,name) values('ND','North Dakota');
insert into state(abbreviation,name) values('MP','Northern Mariana Islands');
insert into state(abbreviation,name) values('OH','Ohio');
insert into state(abbreviation,name) values('OK','Oklahoma');
insert into state(abbreviation,name) values('OR','Oregon');
insert into state(abbreviation,name) values('PM','Palau');
insert into state(abbreviation,name) values('PA','Pennsylvania');
insert into state(abbreviation,name) values('PR','Puerto Rico');
insert into state(abbreviation,name) values('RI','Rhode Island');
insert into state(abbreviation,name) values('SC','South Carolina');
insert into state(abbreviation,name) values('SD','South Dakota');
insert into state(abbreviation,name) values('TN','Tennessee');
insert into state(abbreviation,name) values('TX','Texas');
insert into state(abbreviation,name) values('VI','U.S. Virgin Islands');
insert into state(abbreviation,name) values('UT','Utah');
insert into state(abbreviation,name) values('VT','Vermont');
insert into state(abbreviation,name) values('VA','Virginia');
insert into state(abbreviation,name) values('WA','Washington');
insert into state(abbreviation,name) values('WV','West Virginia');
insert into state(abbreviation,name) values('WI','Wisconsin');
insert into state(abbreviation,name) values('WY','Wyoming');
NOTES:
There are no notes on this topic.