Anyone who has used jderef.com is certainly mourning its demise as JDE, like most ERP systems, is a bear to understand without a really big instruction book. Luckily you can chat with your JDE people and, given the correct libraries, modify the following query to get the same information:
SELECT RTRIM(TDOBNM) AS TABLE_NAME, RTRIM(d.SIMD) AS Table_Description, RTRIM(d.SIPFX) AS Table_Prefix, RTRIM(TDOBND) AS Data_Dictionary_Object_Name, RTRIM(TDSQLC) AS SQL_Column_Name, RTRIM(b.FRDSCR) AS Column_Description, RTRIM(e.DRDL01) AS Field_Type, RTRIM(c.FRDTAS) AS Field_Length, RTRIM(c.FRDTAD) AS Field_Decimals, --RTRIM(c.FROWDI) as DD_Item, CASE WHEN RTRIM(c.FROWER) = 'UDC' THEN 'UDC: RTRIM(DRSY)=''' || RTRIM(c.FROER1) || ''' AND RTRIM(DRRT)=''' || RTRIM(c.FROER2) || '''' ELSE 'Generic Edit' END AS Edit_Type, --RTRIM(c.FROWER) as Edit_Rule, --RTRIM(c.FROER1) as Edit_Rule_Parm1, --RTRIM(c.FROER2) as Edit_Rule_Parm2, RTRIM(TDPSEQ) AS Program_Sequence_Number FROM COPD910.F98711 a LEFT OUTER JOIN DD910.F9202 b ON a.TDOBND = b.FRDTAI AND FRLNGP = ' ' AND FRSYR = ' ' LEFT OUTER JOIN DD910.F9210 c ON a.TDOBND = c.FRDTAI LEFT OUTER JOIN OL910.F9860 d ON a.TDOBNM = d.SIOBNM AND d.SIFUNO = 'TBLE' LEFT OUTER JOIN PRODCTL.F0005 e ON LTRIM(RTRIM(c.FROWTP)) = LTRIM(RTRIM(e.DRKY)) AND e.DRSY = 'H98' AND e.DRRT = 'DT' WHERE RTRIM(TDOBNM) LIKE ? ORDER BY TDOBNM, TDPSEQ
Sample RDL can be found here: http://timlaqua.com/jderef.rdl. You will need to change the data source to work in your environment as well as update the following libraries to your environment:
- COPD910.F98711 - F98711 varies per environment, so make sure you get the right library
- DD910.F9202 - all the F92 files are in the same library
- PRODCTL.F0005 - whatever library your UDC table is in
- OL910.F9860 - the Object Librarian library
Everybody hates these things. If you're in the Activity Monitor or browsing via the SSMS tree view, these GUID jobs that represent SSRS subscriptions are really just none of our concern. Sure, I can admit that I've seen people manually fire these to re-send a given subscription, but you can just do that using the AddEvent proc in a query window. Personally - I don't want to see these... usually...
Connect to the database instance you want to filter the agent jobs out on
Browse to Databases > System Databases > msdb > Programmability > Stored Procedures > System Stored Procedures
Right-click on dbo.sp_help_category and select Modify...
At the top, change the definition of @where_clause to NVARCHAR(MAX)
DECLARE @where_clause NVARCHAR(max)
At the bottom, add in a few lines to append the @where_clause variable with a predicate that filters out the Report Server category when it's you from your workstation (so you can still see that category from another machine if you need to).
94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112
SELECT @cmd = @cmd + N'FROM msdb.dbo.syscategories ' SET @where_clause += N' AND CASE WHEN name = ''Report Server'' AND ( SELECT RTRIM(nt_username) + ''|'' + RTRIM(hostname) FROM sys.sysprocesses where spid = @@spid) = ''tlaqua|TIMSLAPTOP'' THEN 0 ELSE 1 END = 1 ' -- Execute the query EXECUTE (@cmd + @where_clause + N'ORDER BY category_type, name') RETURN(@@error) -- 0 means success END
So, what on earth are we doing here? First, replace my nt_username with yours and replace my hostname with yours. From my less-than-exhaustive trial and error testing, it seems that when either the SSMS Jobs node is expanded or the Job Activity Monitor fetches jobs, two DB calls are made - one to fetch categories and another to fetch jobs. I tried filtering out the jobs portion originally and that yielded some errors. So I'm assuming it's trying to marry the categories and the jobs internally, and it expects there to be jobs for each category the first query returned. By not returning the Report Server category at all, the resulting merged list of jobs doesn't contain any jobs belonging to that category (logically an INNER JOIN).
Sure, this is a dirty hack, but I don't mind.
Here is the predicate for hiding those jobs from ALL SSMS clients:
96 97 98 99 100 101 102 103 104 105 106
SET @where_clause += N' AND CASE WHEN name = ''Report Server'' AND ( SELECT program_name FROM sys.sysprocesses where spid = @@spid) = ''Microsoft SQL Server Management Studio'' THEN 0 ELSE 1 END = 1 '
This is a pretty common request - send the report if there's an issue, don't send it if there's not an issue. This is a simple pattern for doing that using Data Driven Subscriptions. Here, we're not using the data driven subscription to dynamically populate recipient and parameter information, we're just using it to suppress the sending of the report if the report has no value.
To do this, we only need one thing - a query that returns one row if the report should be sent and zero rows if the report should not be sent. Here is an example - Let's say I want to send a report out if a JDE Address Book record with an AT1 of 'DC' has been updated after a certain julian date.
First, write a query to return rows meeting that criteria:
Quick script to locate and execute SSRS timed subscriptions (useful for testing).
1. Execute the top (uncommented) part and identify which subscription you want to schedule.
2. Paste the SubscriptionId from that row in to the @id variable in the lower section
3. Run the entire lower (commented) section
DECLARE @ReportName VARCHAR(255) = 'Report Name' select a.Path ,b.SubscriptionID ,b.Description ,CAST(b.Parameters AS XML) AS [Parameters] ,b.LastRunTime ,b.LastStatus ,CAST(b.ExtensionSettings AS XML) AS [ExtensionSettings] from ReportServer.dbo.[Catalog] a INNER JOIN ReportServer.dbo.[Subscriptions] b ON b.Report_OID = a.ItemID where a.Name = @ReportName /* DECLARE @id varchar(260) = '' ,@Type VARCHAR(32) = 'TimedSubscription' EXEC [ReportServer].dbo.AddEvent @EventType=@Type, @EventData=@id */
I recently worked with Dave DuVarney while he was configuring one of our Reporting Services instances for Kerberos authentication. While working through it, he suggested we take a look at network traffic to see what's actually happening. Turns out, that DuVarney fellow has some clever ideas now and then 😉
We used Network Monitor 3.4 for our tests and really weren't 100% on what we were looking for. Examining traffic over the KerberosV5 protocol, you'll always seem some chatter from the krbtgt as it does whatever it does, but other than that, we just kind of captured and sifted. Here are a few examples of what we saw:
Reporting Services Source Control is an application to create copies of all the reports stored in your Report Manager and commit them to source control (SVN is the only supported source control in the 1.0 release). The need for this arises for two reasons:
- First, we empower our users to create reports using Report Builder and those reports usually only exist the ReportServer database.
- Second, Report Builder has matured so well that it is no longer very beneficial to create a Report Server project using Business Intelligence Development Studio (BIDS).
So now we have intellectual property generated by both end users and developers sitting in Report Manager with no version control whatsoever. Reporting Services Source Control addresses this by scripting out reports, subscriptions, and other properties and committing them to source control.
While we love to empower our users as much as possible, we still need to pay attention to them and what they're up to. The first place I usually see users running amok as Microsoft BI adoption grows in organization is the Report Subscriptions in SSRS. They go nuts with them. I was at one company where 50% of the report subscriptions went to users outside of the company - that means our customers were depending on these subscriptions! Which brings up an interesting licensing debacle... but that's another story. Needless to say, when people set up a subscription, they expect it to work. If it doesn't, we REALLY need to let someone know.
Here, we have a procedure to monitor for Reporting Services subscription failures. Specifically, email subscription failures. When a failed subscription is detected, an email is sent to both the subscription owner and a digest of failures is sent to the specified admin group.
Reporting Services (SSRS/MSRS) 2008 Error: Set used with the complement operator must have all members from the same level
When you use the Not In operator in a SSRS 2008 MDX query filter to exclude a named set, it uses a the complement operator in the constructed MDX. This is fine as long as "all members [are] from the same level." Since you got this error, they are not 😉 You can get around this by using the Except() MDX function instead of letting SSRS use the Complement operator
In the ReportServerService log, you'll see something like this:
Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException: Query (..., ...) Set used with the complement operator must have all members from the same level.
Hierarchy: Calendar Date
Operator: Not In
Filter Expression: [Today]
Hierarchy: Calendar Date
Filter Expression: Except([Time].[Calendar Date].[Calendar Date].MEMBERS, [Today])
During impact analysis for any changes to existing database tables, cube dimensions, cube measures, etc, it's nice to know which reports are going to horribly break before your end-users let you know about it 😉 All of the rdl content for reports uploaded to SSRS is stored in the ReportingServices database in the Content column of the Catalog table (in binary, of course) so here's what I came up with to get the list of soon to be broken reports:
SELECT [Path], ContentText FROM ( SELECT [Path], CAST(CAST([content]AS varbinary(8000)) AS VARCHAR(8000)) AS [ContentText] FROM [catalog] cat WITH(nolock) WHERE [TYPE]=2 ) a WHERE ContentText LIKE '%ColumnName%' OR ContentText LIKE '%columnname%' OR ContentText LIKE '%MeasureName%' OR ContentText LIKE '%AttributeName%' OR ContentText LIKE '%etc...%'