Database

CREATE TRIGGER GrantExecuteTrigger on DATABASE
   FOR CREATE_PROCEDURE
AS
  declare @user varchar(64)
  set @user = 'whateverUserWeWant'

  declare @data xml
  set @data = EVENTDATA()

  declare @sql varchar(max)
  set @sql = 'GRANT EXECUTE ON '
           + @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)')
           + ' TO ' + @user

  execute(@sql)

GO

Mike Ellison wrote:

I wonder if that is limited to the Database edition of Team Studio

Mike Ellison wrote:

I will just have to remember to adjust permissions on any stored procs that I would work with outside the VS context.

Mike Ellison wrote:

It was very helpful.

<br />
SELECT mxContactID, mxzCopies<br />
FROM <br />
(<br />
SELECT [mxContactID], [mxzCopies]<br />
FROM mxContact.dbo.Associations_Groups_to_Contacts <br />
WHERE mxGroupID='35EA110C-FE44-4A85-8D9A-AE973E2C5F85' <br />
OR mxGroupID='3CF66F58-F8E4-44CD-84A0-CB62F85E129B'<br />
)<br />
AS COL<br />
GROUP BY mxContactID, mxzCopies HAVING (COUNT(mxContactID) > 1)<br />

Rob Philpott wrote:

Does this mean I have to use some iterative approach to do this?

declare @counter int
set @counter = 1
while (@counter <= 3)
begin
   update #temp 
   set myXml.modify('replace value of (/document/value[sql:variable("@counter")]/text())[1] with "somevalue"')  
   set @counter = @counter + 1
end

urbane.tiger wrote:

However SQLite looks good, I like the idea of a using library rather than a separate process

urbane.tiger wrote:

Wonder if anyone's used in a Mono/C# app

Mika Wendelius wrote:

If you don't need a "byrocratic" installation, SQLite is most propably a good choice

CREATE PROC AllocateInstructor
 @numStudents INT,
 @InstructorId INT
AS
INSERT INTO Allocation (InstructorId, StudentId)
SELECT TOP @numStudents
   @instructorId,
   studentId
FROM Students
WHERE studentId NOT IN(SELECT studentId FROM Allocation)