Archives for category: SQL

— these commands execute against a single database…
— …not a whole server\instance
USE MyDatabase
GO

–create a role that will have one permission (execute) granted
CREATE ROLE db_exec
GO

–grant execute permissions to our newly created role
GRANT EXECUTE TO db_exec
GO

–add a single user (MySqlUser) to the role
EXEC sp_addrolemember ‘db_exec’, ‘MySqlUser’
GO

Advertisements

This code does not filter by folder path, so if you have more than one report on the server with the same report name, one report will be chosen arbitrarily and only one subscription will be executed.

declare
@report_name as varchar(100) = ‘My Report Name’
,@my_job_name as uniqueidentifier

–get job (subscription) GUID
SELECT top 1 @my_job_name = Schedule.ScheduleID
FROM ReportServer.dbo.Subscriptions
INNER JOIN ReportServer.dbo.ReportSchedule
ON ReportSchedule.SubscriptionID = Subscriptions.SubscriptionID
INNER JOIN ReportServer.dbo.Schedule
ON ReportSchedule.ScheduleID = Schedule.ScheduleID
INNER JOIN ReportServer.dbo.[Catalog]
ON ReportSchedule.ReportID = [Catalog].ItemID
AND Subscriptions.Report_OID = [Catalog].ItemID
WHERE
[Catalog].Name = @report_name

–email subscription report
EXEC msdb.dbo.sp_start_job @job_name = @my_job_name

“Key” columns values are used for sorting the index and they exist at the node level and the leaf level of the balanced tree structure. “Included” columns are not used for sorting the index and they only exist at the leaf level.

--set the table name below to search for a single table
--leave blank to return indexes for all tables
declare @table_name as varchar(255) = ''

select 
	schema_name(t.schema_id) + '.' + t.name as [table],
    i.name as [index],
    c.name as [column],
	ic.is_included_column
from 
    sys.indexes as i
	join sys.index_columns as ic 
		on ic.object_id = i.object_id 
		and ic.index_id = i.index_id
	join sys.columns as c 
		on c.object_id = ic.object_id 
		and c.column_id = ic.column_id
	join sys.tables as t 
		on t.object_id = c.object_id
where
	--if @table_name is null or empty then do not filter
	coalesce(ltrim(@table_name), '') = '' 
	or lower(t.name) = lower(@table_name)
order by 
	i.index_id,
	ic.index_column_id

In this example, I create a SQL user defined aggregate function that returns a Comma Separated Value (CSV) string from a table column.

In SQL, user defined aggregate functions use CLR, so you cannot create them with SQL scripts alone, you must use C# in Visual Studio.

  1. Create a new project in Visual Studio. I used the name “MyNamespace”.
  2. Create a new class within the project. I used the name “CSV.cs”. Here is the code for the class:
    • using System.Data.SqlTypes;
      using Microsoft.SqlServer.Server;
      using System.Collections.ObjectModel;
      namespace MyNamespace{
      [System.Serializable]
      [Microsoft.SqlServer.Server.SqlUserDefinedAggregate(
      Microsoft.SqlServer.Server.Format.UserDefined,
      IsInvariantToDuplicates = false,
      IsInvariantToNulls = false,
      IsInvariantToOrder = true,
      IsNullIfEmpty = true,
      MaxByteSize = -1,
      Name = “CSV”
      )]
      public struct CSV : IBinarySerialize
      {
      private Collection<string> list;
      private string delimiter;
      public void Init()
      {
      list = new Collection<string>();
      delimiter = string.Empty;
      }
      //this seems to handle the SQL function parameters
      public void Accumulate(string value, string delimiter)
      {
      if (value != null)
      {
      if (this.delimiter != delimiter) { this.delimiter = delimiter; }
      list.Add(value.ToString().Replace(this.delimiter, “”)); //remove delimeter character from value
      }
      }
      //not sure when this gets used
      public void Merge(CSV group)
      {
      if (null != group.list && group.list.Count > 0)
      {
      foreach (string entry in group.list)
      {
      list.Add(entry);
      }
      }
      }
      //this seems to return the result
      public SqlString Terminate()
      {
      return new SqlString(string.Join<string>(“,”, list));
      }
      #region IBinarySerialize Members
      //not sure when this gets used, but it matches the function below
      public void Write(System.IO.BinaryWriter writer)
      {
      writer.Write(this.delimiter);
      foreach (string entry in list)
      {
      writer.Write(entry);
      }
      }
      //not sure when this gets used, but it matches the function above
      public void Read(System.IO.BinaryReader reader)
      {
      this.delimiter = reader.ReadString();
      list = new Collection<string>();
      while(reader.BaseStream.Position != reader.BaseStream.Length) //not end of file
      {
      this.list.Add(reader.ReadString());
      }
      }
      #endregion
      }
      }
  3. Build -> Build Solution
  4. Next we will deploy the assembly (.dll file) generated in the previous step. It should be somewhere within the “bin” folder of you solution. Open the SQL command prompt (Start -> All Programs -> Microsoft SQL Server 2012 -> Visual Studio Tools -> Developer Command Prompt for VS2012) and enter the following command in order to deploy the assembly to Global Assembly Cache (GAC). (be sure to replace the .dll path with your own file path):
    • gacutil.exe /if c:\projects\Web\MyNamespace\MyNamespace\bin\release\MyNamespace.dll
  5. Open SQL Management Studio. Register the assembly in SQL by running the following script:
    • EXEC sp_configure ‘clr enabled’, 1
      GO
      RECONFIGURE
      GOCREATE ASSEMBLY MyNamespace
      AUTHORIZATION dbo
      FROM ‘C:\projects\Web\MyNamespace\MyNamespace\bin\release\MyNamespace.dll’
      WITH PERMISSION_SET = SAFE
      GO
  6. Create the user defined aggregate function:
    • CREATE AGGREGATE dbo.CSV (
      @value nvarchar(MAX),
      @delimiter nvarchar(50)
      )
      RETURNS nvarchar(MAX)
      EXTERNAL NAME MyNamespace.[MyNamespace.CSV]
      –EXTERNAL NAME SQLAssemblyName.[C#NameSpace”.C#ClassName].C#MethodName
  7. Your user defined aggregate function should be ready for use now: run the following script: (the function works correctly with GROUP BY, but does provide any ORDER BY capability within the CSV)
    • SELECT dbo.CSV(MyColumn, ‘,’)
      FROM MyTable
  8. If you need to remove the SQL assembly reference and the user defined aggregate function, run the following script:
    • DROP AGGREGATE dbo.CSV
      DROP ASSEMBLY MyNamespace

This is the syntax that I use for insert/update:

MERGE Table2 AS TARGET
USING (
    SELECT Column1, Column2, Column3
    FROM Table1
) AS SOURCE ([Table1].Column1, [Table1].Column2, [Table1].Column3)
ON TARGET.Column1 = SOURCE.Column1 AND TARGET.Column2 = SOURCE.Column2
WHEN MATCHED THEN
    UPDATE
    SET [Table2].Column1 = SOURCE.Column1,
        [Table2].Column2 = SOURCE.Column2,
        [Table2].Column3 = SOURCE.Column3
WHEN NOT MATCHED THEN
    INSERT
    (
        [Table2].Column1,
        [Table2].Column2,
        [Table2].Column3
    )
    VALUES
    (
        SOURCE.Column1,
        SOURCE.Column2,
        SOURCE.Column3
    );