USE active_directory
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
PRINT 'Date: ' + CONVERT(VARCHAR(24),GETDATE(),13) + ' Server Name: ' + @@servername + ' Service Name: ' + @@servicename
GO
IF EXISTS
(SELECT *
FROM sys.objects
WHERE name = 'implementation.ADusers')
DROP FUNCTION implementation.ADusers
PRINT 'DROP FUNCTION implementation.ADusers'
GO
PRINT 'CREATE FUNCTION implementation.ADusers'
GO
CREATE FUNCTION implementation.ADusers()
/************************************************************************************************************************************
AD users
Usage: EXEC sp_executesql @strSQL
Change Log
# Date Who Change
*************************************************************************************************************************************/
RETURNS nvarchar(max)
BEGIN
DECLARE @strSQL1 AS varchar(600)
, @strSQL2 AS varchar(max)
, @strSQL3 AS varchar(100)
, @strSQL AS nvarchar(max)
SET @strSQL1 = 'SELECT samaccountname,distinguishedname,mail,mobile,telephonenumber,extensionattribute14,displayname,name,givenName,sn,useraccountcontrol,objectsid,employeeID,employeeNumber,company,title,streetAddress,postalCode,l,st,co,physicalDeliveryOfficeName,manager
,CAST(CASE WHEN accountexpires = 0 or accountexpires = 0x7FFFFFFFFFFFFFFF then null
ELSE dateadd(day, (accountexpires / (1e7 * 60 * 60 * 24)), cast(''16000101'' as datetime2))
END AS datetime
) FROM (
'
SELECT @strSQL2 = COALESCE(@strSQL2, ' ') + alpha_union.strSQL
FROM (
SELECT char(decimal#) alpha
,'SELECT samaccountname,distinguishedname,mail,mobile,telephonenumber,extensionattribute14,displayname,NAME,givenName,sn,useraccountcontrol,objectsid,employeeID,employeeNumber,company,title,streetAddress,postalCode,l,st,co,physicalDeliveryOfficeName,manager,accountExpires = CAST(accountExpires AS bigint)
FROM OPENQUERY(active_directory,''SELECT samaccountname,distinguishedname,mail,mobile,telephonenumber,extensionattribute14,displayname,name,givenName,sn,useraccountcontrol,objectsid,employeeID,employeeNumber,company,title,streetAddress,postalCode,l,st,co,physicalDeliveryOfficeName,manager,accountExpires
FROM ''''LDAP://OU=Organisation,DC=corporate,DC=com,DC=au''''
WHERE objectCategory = ''''Person'''' and objectClass = ''''USER'''' and samaccountname = '
+ '''''' + CHAR(decimal#) + '*' + ''''''''
+ IIF(CHAR(decimal#) <> 'z','
) UNION ALL
','') strSQL
FROM (select row_number() over (order by (select 1)) as decimal#
FROM sys.all_columns) cte_tally
WHERE decimal# > 96 and decimal# < 123) alpha_union
SET @strSQL3 = (SELECT ')) t
WHERE t.distinguishedName LIKE ''%OU=Users%''')
SET @strSQL = @strSQL1 + @strSQL2 + @strSQL3
RETURN @strSQL
END
;WITH
XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition'
,'http://schemas.microsoft.com/sqlserver/reporting/reportdesigner'
AS rd)
,DEF AS
(SELECT RPT.ItemID
,RPT.Name
,RPT.ReportPath
,R.RptNode.value('@Name[1]', 'nvarchar(425)') AS DataSetName
,CommandText = REPLACE(REPLACE(LTRIM((R.RptNode.value('(./Query/CommandText)[1]', 'nvarchar(4000)'))),'>', '>'),'<', '<')
FROM (SELECT RPT.ItemID
,RPT.Name
,RPT.Path AS ReportPath
,RPT.name AS ReportName
,CONVERT(xml, CONVERT(varbinary(max), RPT.content)) AS contentXML
FROM ReportServer.dbo.[Catalog] AS RPT
WHERE RPT.Type in (2, 5, 7, 8)
) AS RPT
CROSS APPLY RPT.contentXML.nodes('/Report/DataSets/DataSet') AS R(RptNode))
SELECT DEF.Name ReportName
,DEF.ReportPath
,DEF.DataSetName
,DEF.CommandText
,o.OBJECT_NAME
,o.OBJECT_DEFINITION
FROM DEF
,(select o.name as OBJECT_NAME
, m.definition as OBJECT_DEFINITION
from sys.sql_modules m
inner join sys.objects o on o.object_id = m.object_id
WHERE (o.type in ('P','V','TF','IF','FN')
AND (m.definition Like '%fld_current%'
OR m.definition Like '%originalcost%')
)
)
WHERE DEF.CommandText like '%' + o.object_name + '%'
USE MyDataBase
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET FMTONLY OFF
GO
SET NOCOUNT ON
GO
PRINT 'Date: ' + CONVERT(VARCHAR(24),GETDATE(),13) + ' Server Name: ' + @@servername + ' Service Name: ' + @@servicename;
GO
IF EXISTS
(SELECT *
FROM sys.objects
WHERE name = 'Last_Period_Revenue_MA')
DROP PROC dbo.Last_Period_Revenue_MA;
PRINT 'DROP PROC dbo.Last_Period_Revenue_MA';
GO
PRINT 'CREATE PROC dbo.Last_Period_Revenue_MA';
GO
CREATE PROC dbo.Last_Period_Revenue_MA
/************************************************************************************************************************************
Description:
Change Log
# Date Who Change
*************************************************************************************************************************************/
@CustomerCode NVARCHAR(255)
,@Period INT = -12
AS
BEGIN
BEGIN TRY
DECLARE @StrMDX AS NVARCHAR(MAX)
,@BOMDate AS SMALLDATETIME
,@DateFr AS SMALLDATETIME
,@DateTo AS SMALLDATETIME
,@StrSQL AS NVARCHAR(MAX)
,@Amount FLOAT;
SET @BOMDate = DATEADD(d,-DAY(GETDATE())+1, GETDATE());
SET @DateFr = DATEADD(m, @Period, @BOMDate);
SET @DateTo = DATEADD (d, -1, @BOMDate); CREATE TABLE #MSCRM_Roll_DataSet1 (Amount FLOAT NULL);
CREATE TABLE #MSCRM_Roll_DataSet2 ( CustomerType NVARCHAR(255) NULL
,CustomerName VARCHAR(255) NULL
,CustomerSite VARCHAR(255) NULL
,FinancialYearDaily NVARCHAR(255) NULL
,FinancialPeriodDaily NVARCHAR(255) NULL
,CalendarDateDaily NVARCHAR(255) NULL
,ExclAmount FLOAT NULL);
CREATE TABLE #MSCRM_Roll_DataSet3 ( MthName NVARCHAR(20) NULL
,YrMth NVARCHAR(10) NULL
,ExclAmount FLOAT NULL);
SET @StrSQL = '([Calendar].[' +
right('0'+CAST(DAY(@DateFr) AS nvarchar),2)
+ '/'+ right('0'+CAST(MONTH(@DateFr) AS nvarchar),2)
+ '/'+ CAST(YEAR(@DateFr) AS nvarchar) + '] : [Calendar].['
+ right('0'+CAST(DAY(@DateTo) AS nvarchar),2)
+ '/'+ right('0'+CAST(MONTH(@DateTo) AS nvarchar),2)
+ '/'+ CAST(YEAR(@DateTo) AS nvarchar) + '])';
SET @StrMDX = 'INSERT INTO #MSCRM_Roll_DataSet1 SELECT * FROM OPENQUERY ([PROD_AS], ' + CHAR(39) +
'SELECT { [Measures].[Exclusive Amount] } ON COLUMNS
FROM ( SELECT ( { [Customer].[Customers].[CUSTOMER NAME].[' + @CustomerCode +'] } ) ON COLUMNS
FROM ( SELECT ( { ' + @StrSQL + ' } ) ON COLUMNS FROM [Sales])) ' + CHAR(39) + ')';
EXEC sp_executesql @StrMDX;
SET @Amount = (SELECT Amount FROM #MSCRM_Roll_DataSet1)
SET @StrMDX = 'INSERT INTO #MSCRM_Roll_DataSet2 SELECT * FROM OPENQUERY ([PROD_AS], ' + CHAR(39) +
'SELECT NON EMPTY { [Measures].[Exclusive Amount] } ON COLUMNS
,NON EMPTY { ([Customer].[Customers].[Ref Customer Site].ALLMEMBERS
* [Calendar].[Calendar Date Daily].ALLMEMBERS ) } ON ROWS
FROM ( SELECT ( { [Customer].[Customers].[CUSTOMER NAME].&[' + @CustomerCode +'] } ) ON COLUMNS
FROM ( SELECT ( { ' + @StrSQL + ' } ) ON COLUMNS FROM [Sales])) ' + CHAR(39) + ')';
EXEC sp_executesql @StrMDX;
INSERT INTO #MSCRM_Roll_DataSet3
SELECT MAX(DATENAME(m, CAST(SUBSTRING(CalendarDateDaily,7,4)
+ '-' + SUBSTRING(CalendarDateDaily,4,2)
+ '-' + SUBSTRING(CalendarDateDaily,1,2) AS SMALLDATETIME)))
, MAX(SUBSTRING(CalendarDateDaily,7,4)
+ '-' + SUBSTRING(CalendarDateDaily,4,2))
, SUM (ExclAmount) from #MSCRM_Roll_DataSet2
GROUP BY SUBSTRING(CalendarDateDaily,7,4)
+ '-' + SUBSTRING(CalendarDateDaily,4,2);
WHILE @DateFr < @DateTo
BEGIN
INSERT INTO #MSCRM_Roll_DataSet3
SELECT DATENAME(m,@DateFr)
, DATENAME(year,@DateFr)
+ '-' + right('0'+CAST(DATEPART(MONTH,@DateFr)as NVARCHAR(2)),2) , 0;
SET @DateFr = DATEADD(m,1,@DateFr);
END;
SELECT MthName
,MthName + ' ' + SUBSTRING(YrMth,3 , 2) MthYr
,YrMth
,ExclAmount
FROM #MSCRM_Roll_DataSet3;
DROP TABLE #MSCRM_Roll_DataSet1;
DROP TABLE #MSCRM_Roll_DataSet2;
DROP TABLE #MSCRM_Roll_DataSet3;
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH
END
GO
/***************************************************************************************
Call Sample:
EXEC dbo.Last_Period_Revenue_MA
'BAUL4021 - Baulderstone Queensland Pty Ltd'
,-24
****************************************************************************************/ PRINT 'Date: ' + CONVERT(VARCHAR(24),GETDATE(),13) + ' Server Name: ' + @@servername + ' Service Name: ' + @@servicename
GO IF EXISTS
(SELECT *
FROM sys.objects
WHERE name = 'Extract_Report_Info')
DROP FUNCTION dbo.Extract_Report_Info
PRINT 'DROP FUNCTION dbo.Extract_Report_Info'
GO
PRINT 'CREATE FUNCTION dbo.Extract_Report_Info'
GO
CREATE FUNCTION dbo.Extract_Report_Info(
/*****************************************************************************
Purpose: Extracts the contents of all Reports, Data Sources, Report Parts and Shared Datasets (Types 2,5,7, & 8) from the database
Change Log
# Date Who Change
******************************************************************************/
@rptName NVARCHAR(255)
,@rptPath NVARCHAR(255))
RETURNS @tempTBL TABLE (
ItemID NVARCHAR(255)
,Name NVARCHAR(255)
,[Path] NVARCHAR(MAX)
,[Type] NVARCHAR(255)
,TypeDescription NVARCHAR(MAX)
,Content varbinary(MAX)
,ContentVarchar NVARCHAR(MAX)
,ContentXML xml
)
AS
BEGIN
--The first CTE gets the content AS a varbinary(MAX)
--as well AS the other important columns for all reports,
--data sources and shared datasets.
WITH ItemContentBinaries AS
(
SELECT
ItemID
,Name
,[Path]
,[Type]
,CASE Type
WHEN 2 THEN 'Report'
WHEN 5 THEN 'Data Source'
WHEN 7 THEN 'Report Part'
WHEN 8 THEN 'Shared Dataset'
ELSE 'Other'
END AS TypeDescription
,CONVERT(varbinary(MAX),Content) AS Content
--FROM OPENROWSET('SQLNCLI', 'Server=COA-DARC-SQL05\DEV_INTEGRATION;Trusted_Connection=yes;',
-- 'SELECT * FROM ReportServer.dbo.Catalog')
FROM ReportServer.dbo.Catalog
WHERE Type IN (2,5,7,8)
),
--The second CTE strips off the BOM if it exists...
ItemContentNoBOM AS
(
SELECT
ItemID
,Name
,[Path]
,[Type]
,TypeDescription
,CASE
WHEN LEFT(Content,3) = 0xEFBBBF
THEN CONVERT(varbinary(MAX),SUBSTRING(Content,4,LEN(Content)))
ELSE
Content
END AS Content
FROM ItemContentBinaries
)
--The outer query gets the content in its varbinary, varchar and xml representations...
INSERT INTO @tempTBL
SELECT
ItemID
,Name
,[Path]
,[Type]
,TypeDescription
,Content --varbinary
,CONVERT(varCHAR(MAX),Content) AS ContentVarchar --varchar
,CONVERT(xml,Content) AS ContentXML --xml
FROM ItemContentNoBOM
WHERE (ISNULL(@rptName,'') = ''
OR
name LIKE '%' + @rptName + '%')
AND
(ISNULL(@rptPath,'') = ''
OR
[Path] LIKE '%' + @rptPath + '%')
RETURN
END
GO
/*******************************************************************
Test Sample:
SELECT * FROM dbo.Extract_Report_Info('{Report Name},'{Report Path}')
SELECT * FROM dbo.Extract_Report_Info('{Report Name},',NULL)
SELECT * FROM dbo.Extract_Report_Info(NULL,Null)
********************************************************************/
----------------------------------------------------------------------------------------------------------
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOPRINT 'Date: ' + CONVERT(VARCHAR(24),GETDATE(),13) + ' Server Name: ' + @@servername + ' Service Name: ' + @@servicename
GOIF EXISTS
(SELECT *
FROM sys.objects
WHERE name = 'Extract_Dataset_Commands')
DROP FUNCTION dbo.Extract_Dataset_Commands
PRINT 'DROP FUNCTION dbo.Extract_Dataset_Commands'
GOPRINT 'CREATE FUNCTION dbo.Extract_Dataset_Commands'
GOCREATE FUNCTION dbo.Extract_Dataset_Commands(
/****************************************************************************
Purpose : extracts the actual commands used for the each dataSET in each Report or Shared Dataset.Change Log
# Date Who Change
*****************************************************************************/
@rptName NVARCHAR(255)
,@rptPath NVARCHAR(255)
,@cmdText NVARCHAR(255))
RETURNS @tempTBL TABLE ( ItemID NVARCHAR(255)
,Name NVARCHAR(255)
,[Path] NVARCHAR(MAX)
,[Type] NVARCHAR(255)
,TypeDescription NVARCHAR(MAX)
,ContentXML xml
,CommandType NVARCHAR(MAX)
,CommandText NVARCHAR(MAX)
)
AS
BEGIN
--The first CTE gets the content AS a varbinary(MAX)
--as well AS the other important columns for all reports,
--data sources and shared datasets.
WITH ItemContentBinaries AS
(
SELECT
ItemID
,Name
,[Path]
,[Type]
,CASE Type
WHEN 2 THEN 'Report'
WHEN 5 THEN 'Data Source'
WHEN 7 THEN 'Report Part'
WHEN 8 THEN 'Shared Dataset'
ELSE 'Other'
END AS TypeDescription
,CONVERT(varbinary(MAX),Content) AS Content
--FROM OPENROWSET('SQLNCLI', 'Server=COA-DARC-SQL05\DEV_INTEGRATION;Trusted_Connection=yes;',
-- 'SELECT * FROM ReportServer.dbo.Catalog')
FROM ReportServer.dbo.Catalog
WHERE Type IN (2,5,7,8)
),
--The second CTE strips off the BOM if it exists...
ItemContentNoBOM AS
(
SELECT
ItemID
,Name
,[Path]
,[Type]
,TypeDescription
,CASE
WHEN LEFT(Content,3) = 0xEFBBBF
THEN CONVERT(varbinary(MAX),SUBSTRING(Content,4,LEN(Content)))
ELSE
Content
END AS Content
FROM ItemContentBinaries
)
--The old outer query is now a CTE to get the content in its xml form only...
,ItemContentXML AS
(
SELECT ItemID
,Name
,[Path]
,[Type]
,TypeDescription
,CONVERT(xml,Content) AS ContentXML
FROM ItemContentNoBOM
)
--now use the XML data type to extract the queries, and their command types and text....
INSERT INTO @tempTBL
SELECT
ItemID
,Name
,[Path]
,[Type]
,TypeDescription
,ContentXML
,ISNULL(Query.value('(./*:CommandType/text())[1]','NVARCHAR(1024)'),'Query') AS CommandType
,Query.value('(./*:CommandText/text())[1]','NVARCHAR(MAX)') AS CommandText
FROM ItemContentXML
--Get all the Query elements (The "*:" ignores any xml namespaces)
CROSS APPLY ItemContentXML.ContentXML.nodes('//*:Query') Queries(Query)
WHERE (ISNULL(@rptName,'') = ''
OR
name LIKE '%' + @rptName + '%')
AND
(ISNULL(@rptPath,'') = ''
OR
[Path] LIKE '%' + @rptPath + '%')
AND
(ISNULL(@cmdText,'') = ''
OR
Query.value('(./*:CommandText/text())[1]','NVARCHAR(MAX)') LIKE '%' + @cmdText + '%')
RETURN
END
GO
/*****************************************************************************
Test Sample:
SELECT * FROM dbo.Extract_Dataset_Commands(‘{Report Name}','{Report Path}',NULL)
SELECT * FROM dbo.Extract_Dataset_Commands('{Report Name}',NULL, 'select')
SELECT * FROM dbo.Extract_Dataset_Commands(NULL,'{Report Path}’','SELECT')
*****************************************************************************/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOPRINT 'Date: ' + CONVERT(VARCHAR(24),GETDATE(),13) + ' Server Name: ' + @@servername + ' Service Name: ' + @@servicename
GOIF EXISTS( SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS
WHERE VIEW_DEFINITION LIKE '%dbo.Shared_Data_Sources%')
BEGIN
DROP VIEW dbo.Shared_Data_Sources
PRINT 'DROP VIEW dbo.Shared_Data_Sources'
END
GOPRINT 'CREATE VIEW dbo.Shared_Data_Sources'
GO/************************************************************************************************************************************
Reports Audit - Extract Shared Data SourcesChange Log
# Date Who Change
*************************************************************************************************************************************/
CREATE VIEW dbo.Shared_Data_Sources
AS
WITH XMLNAMESPACES
-- XML namespace def must be the first in with clause.
(
DEFAULT
'http://schemas.microsoft.com/sqlserver/reporting/2006/03/reportdatasource'
,
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'
AS rd
)
,SDS
AS (
SELECT SDS.NAME AS SharedDsName
,SDS.[Path]
,CONVERT(XML, CONVERT(VARBINARY(max), content)) AS DEF
FROM dbo.[Catalog] AS SDS
WHERE SDS.Type = 5
) -- 5 = Shared Datasource
SELECT CON.[Path]
,CON.SharedDsName
,CON.ConnString
FROM (
SELECT SDS.[Path]
,SDS.SharedDsName
,DSN.value('ConnectString[1]', 'varchar(150)') AS
ConnString
FROM SDS
CROSS APPLY SDS.DEF.nodes('/DataSourceDefinition') AS R(DSN
)
) AS CON
GOSET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOPRINT 'Date: ' + CONVERT(VARCHAR(24),GETDATE(),13) + ' Server Name: ' + @@servername + ' Service Name: ' + @@servicename
GOIF EXISTS( SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS
WHERE VIEW_DEFINITION LIKE '%dbo.Report_Data_Sources%')
BEGIN
DROP VIEW dbo.Report_Data_Sources
PRINT 'DROP VIEW dbo.Report_Data_Sources'
END
GOPRINT 'CREATE VIEW dbo.Report_Data_Sources'
GO/************************************************************************************************************************************
Reports Audit - Extract Report Data SourcesChange Log
# Date Who Change
*************************************************************************************************************************************/
CREATE VIEW dbo.Report_Data_Sources
AS
SELECT C.ItemID
,C.Name
,C.Path
,C.Type
,CASE C.Type WHEN 2 THEN 'Report'
WHEN 5 THEN 'Data Source'
WHEN 7 THEN 'Report Part'
WHEN 8 THEN 'Shared Dataset'
ELSE 'Other' END AS TypeDescription
, CONVERT(varbinary(MAX), C.[Content]) AS Content
, D.Name DataSourceName
FROM dbo.Catalog C LEFT JOIN
dbo.DataSource D
ON C.ItemID = D.ItemID
WHERE (C.Type IN (2, 5, 7, 8))
GO
It allows:· Extract from shared data source the connection string for each report
SELECT R.ItemID
,R.Name
,R.Path
,R.Type
,R.TypeDescription
,R.[Content]
,R.DataSourceName
FROM dbo.Report_Data_Sources R LEFT OUTER JOIN
dbo.Shared_Data_Sources S
ON R.DataSourceName = S.SharedDsName
WHERE R.Name = '{Report Name}'· Identify reports which have no shared data sources attachedSELECT R.ItemID
,R.Name
,R.Path
,R.Type
,R.TypeDescription
,R.[Content]
,R.DataSourceName
FROM dbo.Report_Data_Sources R LEFT OUTER JOIN
dbo.Shared_Data_Sources S
ON R.DataSourceName = S.SharedDsName
WHERE DataSourceName IS NULL
The above udf’s can be executed from the following PowerShell Script: #Query a SQL database
$SQLServer = "DB\INSTANCE";
$SQLDBName = "ReportServer";
$rptName = "Report Name";
$rptPath = "Report Path";
$SqlQuery = "SELECT * FROM [dbo].[Extract_Report_Info]('" + $rptName + "'," + "'" + $rptPath + "');";
$smtp = "smtp address";
$From = "noreply email address";
$To = "destination email address";
$connectionName = "Extract_RS_Info";
$commandTimeout = 15; clear;
$SqlQuery; Function Extract_RS_Info { Param ( $server = $SQLServer
,$database = $SQLDBName
,$smtp
,$From
,$To
,$rptName
,$rptPath
,$SqlQuery
,$connectionName
,$commandTimeout)
Try{
$conn =new-object ('System.Data.SqlClient.SqlConnection')
$connString = "Server=$server;Integrated Security=SSPI;Database=$database;Application Name=$connectionName"
$conn.ConnectionString = $connString
Write-Debug ("Function: Query-SQL: $server $database")
if (test-path variable:\conn) {
$conn.close()
} else {
$conn =new-object ('System.Data.SqlClient.SqlConnection')
}
$conn.Open()
$sqlCmd =New-Object System.Data.SqlClient.SqlCommand
$sqlCmd.CommandTimeout = $CommandTimeout
$sqlCmd.CommandText = $SqlQuery
$sqlCmd.Connection = $conn
$data = $sqlCmd.ExecuteReader()
while ($data.read() -eq $true) {
$max = $data.FieldCount -1
$obj =New-Object Object
For ($i = 0; $i -le $max; $i++) {
$name = $data.GetName($i)
if ($name.length -eq 0) {
$name = "field$i"
}
$obj |Add-Member Noteproperty $name -value $data.GetValue($i) -Force
}
$obj
}
$conn.close()
$conn = $null
} Catch {
$ErrorMessage = $_.Exception.Message;
$FailedItem = $_.Exception.ItemName;
$body = "We failed to read file $FailedItem. The error message was $ErrorMessage";
Send-MailMessage -From $From -To $To -Subject "Extract_RS_Info.ps1 failed!" -SmtpServer $smtp -Body $body;
Break
}
} Extract_RS_Info $SQLServer $SQLDBName $smtp $From $To $rptName $rptPath $SqlQuery $connectionName $commandTimeout; $str = "*" * 80;
write-host $str;
$SqlQuery = "SELECT * FROM [dbo].[Extract_Dataset_Commands]('" + $rptName + "','" + $rptPath + "','" + $cmdText + "');";
$SqlQuery; Extract_RS_Info $SQLServer $SQLDBName $smtp $From $To $rptName $rptPath $SqlQuery $connectionName $commandTimeout;
$Path = "E:\Pictures\Scanned\"
$files = Get-ChildItem $Path*.bmp
Add-Type -AssemblyName system.drawing
$imageFormat = "System.Drawing.Imaging.ImageFormat" -as [type]
ForEach ($file in $files) {
$outputfile = $file.fullName -replace ".bmp", ".jpg"
write-host $outputfile
$image = [drawing.image]::FromFile($file.fullName)
$image.Save($outputfile, $imageFormat::jpeg)
}
exec dbms_output.enable(1000000);
SET SERVEROUTPUT ON
CREATE OR REPLACE
TYPE table_rec FORCE AS OBJECT (
person_code number(10)
,visa_type varchar2(40)
,prm number(10)
);
/
CREATE OR REPLACE
TYPE my_table FORCE AS table of table_rec;
/
CREATE OR REPLACE FUNCTION get_table_rows(
p_person_code IN NUMBER
) RETURN my_table PIPELINED AS
v_my_table my_table;
BEGIN
SELECT table_rec(TBL.person_code
,TBL.visa_type
,TBL.p_person_code
)
BULK COLLECT INTO v_my_table
FROM
(SELECT person_code
,visa_type
,p_person_code
FROM visas
where person_code = p_person_code
) TBL;
FOR i IN v_my_table.FIRST .. v_my_table.LAST LOOP
PIPE ROW(v_my_table(i));
END LOOP;
RETURN;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE || ' ' || SQLERRM);
END get_table_rows;
/
The script below runs this function:
DECLARE
CURSOR C IS select person_code
,registration_no
,forename
,surname
from table(get_table_rows(182));
l_rec C%ROWTYPE;
l_start date;
l_finish date;
BEGIN
l_start := sysdate;
OPEN C;
LOOP
FETCH C INTO l_rec;
DBMS_OUTPUT.PUT_LINE(l_rec.person_code);
EXIT WHEN C%NOTFOUND;
END LOOP;
CLOSE C;
l_finish := sysdate;
dbms_output.put_line('Time difference = ' || (l_finish - l_start) * (24 * 60 * 60));
END;