SQL Server Change Ownership

Bill Graziano  wrote a very useful script here. 

SELECT 'EXEC(''sp_changeobjectowner @objname = '''''+
  ltrim(u.name) + '.' + ltrim(s.name) + ''''''
  + ', @newowner = dbo'')'
FROM  sysobjects s,
      sysusers u
WHERE s.uid = u.uid
AND   u.name <> 'dbo'
AND   xtype in ('V', 'P', 'U')
AND   u.name not like 'INFORMATION%'
order by s.name
This query finds every view, stored procedure and user table in the database not owned by the dbo and converts ownership to the dbo.

SQL Server 2000 vs SQL Server 2005 queries

This gives an example of SQL queries between SQL Server 2000  and SQL Server 2005

http://www.mssqltips.com/tip.asp?tip=1015

Bumped into this while looking for  IGNORE_DUP_KEY

 

Also realized you can’t put this as part of a table creation unlike SQL Server 2005.

Back

Accidentally locked the site…Just realized.

Sorry for the inconvenience.

8 Ways to DataBind your DropDownList

http://www.aspfree.com/c/a/ASP.NET/ASPNET-Dropdown-List-Control-Eight-Ways-to-Bind-Data/1/

Here is a useful link. There are so many ways to databind and this page summarizes it all.

Tech.Ed 2008 Sydney

http://www.microsoft.com.au/teched/

Random links

This was a stuck draft written sometime ago. I couldn’t remember what was the appropriate title for all the links I found.

Basic Javascript Alert, Confirm, Prompt boxes

http://www.javascriptkit.com/javatutors/alert2.shtml

Search Date Time with ICriterion

http://www.jaltiere.com/?p=52

SQL Server 2005 Triggers

Getting a list of triggers

SELECT * FROM sys.triggers
select * from sysobjects where xtype =’TR’

DDL Triggers in SQL Server 2005 by Dinesh Asanka

Disabling Triggers in SQL Server 2005

What I was really looking for was how to edit an existing trigger in a database. In SQL 2000, you right click on a table on the Object Explorer details > All Tasks > Manage Triggers

In SQL 2005, you CANNOT right click on a table in the Object Explorer Details. You have to use the left side bar

And a short tutorial answering my questions. (Thanks Imran!)

This feature is also available in SQL Server 2005.

If you login into SSMS, expand Servername- expand database – expand table- under tables 4th subobject you will see trigger – expand triggers ( to see all triggers in that table) – right click on any one of the trigger and click modify and you will see a new query window with SQL code for that trigger, If you want to make changes to this trigger, make necessary changes in the sql code and then hot execute button at the top. ( same as to execute any query.) and you are done.

You also mentioned something about, Programmability under Database ( in object explorer, SSMS), let me explain you in brief what exactly is this.

Objects in a database can be : Tables, views, stored procedures and functions ….

So when we are talking about triggers, these are specific for tables, because triggers (DML, Data Manipulation Language Triggers) can be created on tables for/instead of insert ,update and delete. That is why you will see triggers under tables but not under Programmability.

Now the second question, When you expand programmability, you will see database triggers , what are these triggers ???

This is a brand new concept in SQL Server 2005, these are called DDL Triggers, Data Definition Language ( these are not available in SQL Server 2000).

These triggers, as the name defines are used for all DDL operations like create, alter, drop… You cant imagine how many types of these triggers are available in SQL Server 2005. DML like I said are only 3 types, Insert update and delete, but if you read books online, you will see, there are many many types for DDL Triggers. This is very good feature in SQL Server 2005. Since these triggers are not for a specific table, they cannot be placed under tables( in SSMS, object Explorer) , so I assume designer found it very handy to keep these triggers under programmability, and names these triggers as Database triggers, since these triggers are at database level but not for a specific table.

Here is a very quick example for both of these triggers,

– Create two tables
create table example1 ( eid1 int )
create table example2 ( eid2 int )

– create insert trigger on table : example1
create trigger Tr_ex1
on example1
for insert
as
begin
insert into example2 select * from inserted
end

– insert into table: example1
insert into example1 values ( 1)

– check table: example2 if this value is inserted
select * from example2

By this time you should be able to see this trigger ( Tr_ex1) under table name example1. ( in SSMS , object explorer). Right click on trigger and click modify, you will see the same SQL code as above, if you want to modify, you can modify and click execute and you are done.

– create another table to store information about, who dropped tables, when and from which database and at what time.
create table example3 ( UserName varchar (max), databasename varchar(max), Date_time datetime)

– create DDL trigger
CREATE TRIGGER Tr_ex2
ON DATABASE
FOR DROP_TABLE
AS
insert into example3 select suser_name() , db_name(), getdate()

By this time you should be able to see a database trigger, under programmability by name Tr_ex2. ( in SSMS , object explorer). Right click on trigger and click modify, you will see the same SQL code as above, if you want to modify, you can modify and click execute and you are done.

– Drop table: example2
drop table example2

– check table:example3 if it shows information about who dropped table from which database and at what time.

select * from example3

Result:

IMRAN\imran databasename 2008-08-23 23:10:09.617

Read more about DDL Triggers : http://blog.sqlauthority.com/2007/07/24/sql-server-2005-server-and-database-level-ddl-triggers-examples-and-explanation/

Hope this helps,
Imran.

Anyone else with useful articles on SQL 2005 triggers please feel free to paste it in the comments section or email me the link so I can add it on here!

Finding list of constraints in SQL 2000

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

Got it from here

http://www.mssqltips.com/tip.asp?tip=1242

when the regular one that I used doesn’t work in SQL 2000!

You learn something new every now and then, sometimes, unexpectedly

MCTS VS2008

Visual Studio 2008 MCTS Exams Available Now
As of today, these two Visual Studio 2008 MCTS exams are available in English for registration worldwide:

 

 · Exam 70-561 TS: Microsoft .NET Framework 3.5, ADO.NET Application Development (NEW!)

 · Exam 70-562 TS: Microsoft .NET Framework 3.5, ASP.NET Application Development (NEW!)

These exams, along with the three Microsoft Certified Technology Specialist (MCTS) exams that released in April (Exam 70-502, 70-503, 70-504), make it possible for you to verify your deep technical skills in key Visual Studio 2008 technologies. Passing any of these five exams earns credit toward a corresponding MCTS certification. You must also pass Exam 70-536 TS: Microsoft .NET Framework, Application Development Foundation <http://go.microsoft.com/?linkid=9328048> in order to complete the requirements for the certification.

SQL Server 2008 exams are scheduled to start coming out in September (subject to change based on actual exam release date). Please look forward to another notification and savings voucher at that time.

A string of links – LINQ & VB.NET

Visual Basic LINQ Hands On Labs for Visual Studio 2008
http://www.microsoft.com/downloads/details.aspx?FamilyID=e7fa5e3a-f8b2-4f77-bbcd-b5b978402dd1&displaylang=en

LINQ (How Do I in Visual Basic)
http://msdn.microsoft.com/en-us/library/bb384508.aspx

Using LINQ to SQL (Part 1) – Scott Guthrie
http://weblogs.asp.net/scottgu/archive/2007/05/19/using-linq-to-sql-part-1.aspx

LINQ to XML – Eric White
http://blogs.msdn.com/ericwhite/archive/tags/LINQ/default.aspx

Learn The Basics Of LINQ
http://www.singingeels.com/articles/learn_the_basics_of_linq.aspx

LINQ Introduction Part 1 Of 3 – Sacha Barber
http://www.codeproject.com/KB/vista/LINQ_1.aspx

DLINQ Part 2 of 3
http://www.codeproject.com/KB/vista/LINQ2.aspx

XLINQ Part 3 of 3
http://www.codeproject.com/KB/vista/LINQ_3.aspx

Create many-to-many relationships in LINQ for SQL – Galin Iliev
http://www.codeproject.com/KB/database/LINQ_many2many.aspx

A custom implementation of the .NET Standard Query Operators from LINQ
http://www.codeplex.com/LINQSQO

B#.NET BLOG – LINQ tags
http://community.bartdesmet.net/blogs/bart/archive/tags/LINQ/default.aspx

The Visual Basic Team – LINQ Cookbook
http://blogs.msdn.com/vbteam/archive/tags/LINQ+Cookbook/default.aspx