SQL Server 2012 :: List Catalog Reports With No Execution History Returns 0 Rows
Feb 13, 2015
comparing UNIQUEIDENTIFIER columns..This query returns several rows where the [ReportId] and [LastRunDate] columns are both NULL:
SELECT [c].[Name],[c].[ItemID],[xl].[ReportID]
, MAX([TimeStart]) [LastRunDate]
FROM [dbo].[Catalog] [c]
LEFT JOIN [dbo].[ExecutionLogStorage] [xl] on [c].[ItemID] = [xl].[ReportID]
WHERE [c].[Type] NOT IN (1,5) -- Not a folder or a data source!
group by [c].[Name],[c].[ItemID],[xl].[ReportID]
order by 4
However, trying to just list catalog reports with no execution history returns 0 rows, but I'm expecting it to return a row for every NULL [ReportId] from the above query:
SELECT *
FROM [dbo].[Catalog]
WHERE [Type] NOT IN (1,5) -- Not a folder or a data source!
AND [ItemID] NOT IN (SELECT [ReportID] FROM [dbo].[ExecutionLogStorage])
I even tried casting [ItemId] and [ReportId] columns in the 2nd query to VARCHAR(255), and still got no rows, but the following queries return 0 rows and 1 row (respectively).
select * from [dbo].[ExecutionLogStorage] where [ReportID] = '0BB2209C-7736-46C8-AD02-4614EBA4F0F1'
select * from [dbo].[Catalog] where [ItemID] = '0BB2209C-7736-46C8-AD02-4614EBA4F0F1'
View 4 Replies
ADVERTISEMENT
Oct 23, 2014
"SSIS 2012 Catalog doesn't have option to give read access to SSIS Catalog to view package run reports" ... Any luck allowing power developers / operators access to READ the SQL 2012 SSIS Execution Reports without granting them SSIS_Admin or Sysadmin?
According to this link posted back in 2011 (w/ Microsoft's feedback in Nov 2011: "We’re closing this issue as “Won’t Fix.” At this point the bug does not meet our bar for resolving prior to SQL Server 2012 RTM. As we approach the SQL Server 2012 release the bar for making code changes gets progressively higher." URL....Regarding Permissions to SSIS Catalog, here are the findings. We can give access in three ways:
1. READ Access – We can provide a user db_datareader access. With this the user can see the objects within the SSIS catalog database, but cannot see the reports.
2. SSIS_ADMIN – Add the user to this database role in SSISDB. With this the user can view the reports. But it also provides them privileges to modify catalog information which is not expected. We can add it using below script EXEC sp_addrolemember 'ssis_admin' , 'REDMONDPAIntelAnalyst'
3. SYSADMIN - Add the user to this server role. This will make the user an admin on the SQL server. This is not intended. Is there any method available which will have provision to give read only access to see SSIS Catalog package execution reports and not having modify Catalog access.
View 1 Replies
View Related
Nov 29, 2013
I am trying to do a bulk import of data from XML into sQL.
My query returns no errors but no data gets imported.
Here is my XML
?xml version="1.0" encoding="utf-8"?>
<status>
<connection_status>successful</connection_status>
<operation_status>successful</operation_status>
<CustomerDeposits>
[code]....
View 2 Replies
View Related
May 31, 2014
There is a checkbox when you create an SSIS catalog which says "Enable automatic execution of Integration Services stored procedure at SQL Server startup". It's a good practice to have this option on. BUT! How do you verify whether this option is ON or OFF after you have created a catalog and how do you enable it if it wasn't enabled at the time of creating the catalog?
View 1 Replies
View Related
Jun 3, 2014
I want to save every query executed from a given software, let's say Multi Script for example, and save in a table query text, execution time and rows count among other possible useful information. Right now I've created a sp and a job that runs every 1 milliseconds but I can't figure out how to get execution time and rows count. Another problem with this is that if the query takes too long I end up with several rows in my table.
View 5 Replies
View Related
May 22, 2013
How do I grant a regular user or group the same rights to view the SSIS execution reports as say the sysadmin role?
I need to figure out how to give users the visibility of those reports without actually granting sysadmin.
View 3 Replies
View Related
Oct 10, 2006
How to get the list of catalog views sys.* (sys.objects, sys.columns ....) in sql server 2005
select * from sys.objects where objectproperty(object_id, 'IsSystemTable') = 1
did not return the list as I've expected
thanks
View 4 Replies
View Related
Sep 9, 2015
I would like to view the previous executed query error message in SSMS. Is there any way to view the query execution error messages history in SSMS?
View 2 Replies
View Related
May 7, 2014
I have a table of users including: UserName, Password (comuted col), FirstName, LastName, Address and other details....
I have to keep 10 Recent passwords , so I created another table "ut_Password " (Table2)
This table contains the following columns : Username, Password , and Password_Date.
I searched a lot but could not find something similar in my opinion need SP for it.
- 10 row Max for Password History in table 2
- when user change password it's need to be uniqe and it should not appear last 10 passwords
- Each user can have a maximum of 10 lines containing history password table
- Most old password deleted and replaced with a new password will enter the correct date (FIFO method first in first out).
View 9 Replies
View Related
Aug 14, 2014
I have a customer history table with the follow structure and data:
CustomerID Tier StartDate RecordStatus
123 A 01/01/2013
View 3 Replies
View Related
Aug 14, 2014
I have a customer history table with the follow structure and data:
CustomerID Tier StartDate RecordStatus
123 A 01/01/2013 1
123 A 03/01/2013 0
123 B 03/01/2013 1
123 B 06/01/2013 0
123 A 08/01/2014 1
456 C 02/01/2014 1
CREATE TABLE TEMPHISTORY(
CUSTOMERID VARCHAR(11),
TIER VARCHAR(10),
STARTDATE DATE,
RECORDSTATUS TINYINT)
[Code] .....
The RECORDSTATUS value of 1 means the record is active. A corresponding record of the same CustomerID, Tier. in startdate chronology, with a value of 0 indicates that the previous record with the status of 1 has now terminated and the startdate of the record with recordstatus of 0 is the start date of the termination of the previous record, or better stated, the end date of the previous record.
What I need to do is re-record the above data the startdate of each terminated record become an enddate for the previous record, minus 1 day, as follows:
CUSTOMERID TIER STARTDATE ENDDATE
123 A 01/01/2013 02/28/2013
123 B 03/01/2013 05/31/2013
123 A 08/01/2014 NULL
456 C 02/01/2014 NULL
View 1 Replies
View Related
Jul 6, 2015
I want to give developers access to view SQL Job history, but not let them inhert permission to create local jobs like they get with the fixed server roles.
how can this be achieved? Or can it only be achieved by giving them access to the tables in msdb and querying them?
View 1 Replies
View Related
May 2, 2007
Without systemAdmin role, xp_AvailableMedia can still run (after granting execute permission) but just return 0 rows.
It seems we have to give the user SystemAdmin role, in order to get the list of devices in SQL 2005, while in previous versions of SQL Server, the user granted the execute permission for xp_AvailableMedia will be able to get the list of devices.
Is this be design? Is there any way the a NON-SA user can retrieve device list? If yes, what extra permissions do we have grant?
Thanks in advance for your help.
Frank
View 3 Replies
View Related
Jul 23, 2007
I have a job that executes an ssis package. The SSIS package executes successfully in business intelligence Studio however when I make it into a job I get the error message in the topic title. The log file isnt any more helpful which simply says "The package execution failed. The step failed."
I have no idea how to diagnose this problem!
View 7 Replies
View Related
Jan 7, 2015
Is it possible to deploy a SSIS package without building an Integration Services Catalog on the server?
View 4 Replies
View Related
Apr 1, 2014
So I have two steps in my job that check conditions and call RAISERROR to gracefully complete the job if those conditions are met (meaning I only want the final step to run in a specific situation). Works great, except for some reason my messages are not making it into the log. It is appending the SQLSTATE, and a default error instead of anything that I'm putting into RAISERROR.
View 2 Replies
View Related
Apr 2, 2015
Is there anyway we could retrieve the job history for all the jobs in all the sql server using Central Management Server?
View 0 Replies
View Related
Oct 22, 2015
Here's what my table looks like;
UniqID | Code |
1 | ABC
1 | 123
2 | ABC
3 | ABC
4 | ABC
4 | ABC
I only want to UniqIds that only have the CODE of ABC... and if it contains ANYTHING other than ABC then It doesnt return that UniqID... Now keep in mind there's multiple different codes.. I'm just looking for a bit of code that drops any ID's that don't have my criteria.
View 9 Replies
View Related
May 26, 2015
Until now I've been looking for a possible solution to the execution of a sp from SSIS, but anything seems to work. I´ve got a sp: CREATE PROCEDURE [DBO].[SPIDENTIFIERS] @IDENT NVARCHAR(MAX) OUTPUT
What I need is to save the result in a variable that I've created in SSIS. This is the configuration that I used to try to do it.In the parameter set section I have also used the Direction as Output or ReturnValue but I received a error message. Just to try I put a Script Task to chek the value, but as you can see this is empty.With the Direction Ouput or ReturnValue I've got this:
[Execute SQL Task] Error: Executing the query "EXECUTE spIdentifiers ? OUTPUT;" failed with the following error:
"El valor no está dentro del intervalo esperado.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
View 6 Replies
View Related
May 2, 2008
I have a report that runs monthly and they want to see the previous 2 months of data. This data is stored no place other than the snapshot of the same report for the previous 2 months. Is there any way I can display the last 2 snapshots/history withing my report? Thanks.
View 1 Replies
View Related
May 8, 2007
Hi,
i've used sql 2000 to create reports. Now i have almost 100 reports with lot of history (snapshots). Now i've developed the same reports in 2005 (basically upgraded the reports). If i upload the new reports, the 2000 history will be lost. Is there anyway that i can upgrade to 2005 and save the history ??
Thanks
View 4 Replies
View Related
Jun 26, 2007
I would like to know if there is a way to maintain the history of changes to the reports that have been published to the report server?
I know that the report definitions get saved onto ReportServer database. But let's say a user makes a change to the published report and then saves it back to the server. And that the latest change was incorrect and I have to revert back to the previous version of the published report. Is there a way to do that? Does the report server maintain a history of previous versions.
There is a history for each report and I think that corresponds to the history of report executions (output data). But I am talking about the history of actual report definition.
Thanks for you help.
View 2 Replies
View Related
Jul 1, 2015
We have more that 500 crystal reports and we would like to find out list of stored procedure used by crystal reports. Can we find out ?
View 4 Replies
View Related
Mar 11, 2008
Hi,
What catalog table has the number of rows of a table ?
View 11 Replies
View Related
Apr 9, 2008
I know I can do a JOIN(parameter, "some seperator") and it will build me a list/string of all the values in the multiselect parameter.
However, I want to do the same thing with all the occurances of a field in my result set (each row being an occurance).
For example say I have a form that is being printed which will pull in all the medications a patient is currently listed as having perscriptions for. I want to return all those values (say 8) and display them on a single line (or wrap onto additional lines as needed).
Something like:
List of current perscriptions: Allegra, Allegra-D, Clariton, Nasalcort, Sudafed, Zantac
How can I accomplish this?
I was playing with the list box, but that only lets me repeat on a new line, I couldn't find any way to get it to repeate side by side (repeat left to right instead of top to bottom). I played with the orientation options, but that really just lets me adjust how multiple columns are displayed as best I can tell.
Could a custom function of some sort be written to take all the values and spit them out one by one into a comma seperated string?
View 21 Replies
View Related
Jun 15, 2015
So at the moment, I don't have a function by the name CONCATENATE. What I like to do is to list all those different values that go with a single CASE_ID to appear as a a comma separate list. You might have a better way of doing without even writing a function
So the output would look like :
CASE_ID VARIABLE
=====================
1 [ABC],[HDR],[GHHHHH]
2 [ABCSS],[CCHDR],[XXGHHVVVHHH],[KKKJU],[KLK]
SELECT
preop.Case_ID,
dbo.Concatenate( '[' + CAST(preop.value_text AS VARCHAR) + ']' ) as variable
FROM
dbo.TBL_Preop preop
WHERE
preop.Deleted_CD = 0
GROUP BY
preop.Case_ID
View 8 Replies
View Related
Oct 3, 2014
I am running a package in the catalog. The package contains a c# script that uses FTP webrequest. It runs fine in SSDT. Trying to execute from the catalog the ftp list directory is timing out.
This seems to be a firewall issue. We have tried adding an exception for dtexec, but that did not . I am thinking another program runs the package, maybe ISServer.exe?
Any program that would need the firewall exception to run the package in the catalog?
View 6 Replies
View Related
Feb 15, 2015
I have a Job that creates .CSV files at predetermined intervals (the delimiter is a tabstop) and every 5 minutes I have to retrieve These files one at a time and Import them into the relevant database.
My solution was to create a list of the files and insert this list into a temporary table where a Cursor would extract each filename one at a time and insert that Name into a BCP Statement. The BCP Statement is constructed using dynamic SQL.
Here the Cursor:
create table #filelist(filename nvarchar(100))
insert into #filelist exec xp_cmdshell 'dir C:BCP_Test /A-D /B'
declare @filename nvarchar(100)
declare @path nvarchar(100)
declare @bcp_open nvarchar(100)
[Code] ....
The code seems to work but although there are around 50000 lines in the .csv file, None of the lines are inserted.
View 6 Replies
View Related
Jan 16, 2014
Curious if I have the code below as an example and I execute this code does sql execute from top to bottom? And does the Update run and complete before the delete occurs? Or does SQL execute the update and delete in parallel?
UPDATE tbl1 SET col1 = 1
GO
DELETE FROM tbl1 where col2 = 2
View 2 Replies
View Related
Nov 27, 2014
I have 2 tables,
1. Need
2. NeedCategory
Need table has clusted index on needid column and NeedCategory have composite clustered index on needid and categoryid.
Now take a look on following query and execution plan for the query.
SELECT N.NeedId,N.NeedName,N.ProviderName
FROM dbo.Need N
JOIN dbo.NeedCategory NC
ON nc.NeedId = n.NeedId
WHERE IsActive=1
AND CategoryId= 2
ORDER BY NeedName
* Clustered index scan on need table is happens for Isactive= 1.
* Clustered index scan on needcategory table is happens for CategoryId=2
My question is,
1. Why scan happens before the join occurs? if it happens after join then the filter would be lighter. Even if optimizer chooses the scan to execute first.
2. Is there any chance to rearrange the execution plan manually?
View 6 Replies
View Related
Sep 10, 2014
I am in a dilemma if I should reorganize or rebuild a full text catalog.
My application owner does not want a rebuild as he says that it takes week for the rebuild to occur on these full text indexes.
Will this code just re-organize without turning off the full text indexes : Alter fulltext catalog catalog_name Reorganize
View 4 Replies
View Related
Aug 22, 2014
Set up a trace with the events RPC:Completed, SQL:BatchCompleted, SQL:BatchStarting, and SQL:StmtCompleted.
When I issue the statement: SELECT * FROM XyzView there is nothing captured in Profiler. If I script out the view and then execute the select statement that defines the view, it does show up in Profiler.
I've tried adding a lot of the other events, i.e. SP:StmtCompleted and the various other StmtStarting events and the trace still does not capture anything.
Am I capturing the wrong events or is this known behavior? My goal is to see what the overhead is for using a view versus persisting the results of the view as a table and referencing that instead. The view in question is against static data, joins 9 tables, and is referenced a lot.
I can use the stats generated when I execute the select that defines the view but I still find this to be curious behavior so I assume I'm doing something wrong.
View 9 Replies
View Related
Nov 11, 2014
I'm new to using SQL Server. I've been asked to optimize a series of scripts that queries over 4 millions records. I've managed to add indexes and remove a cursor, which increased performance. Now when I run the execution plan, the only query that cost is a DELETE statement from the main table. It shows a SORT which cost 71%. The table has 2 columns and a unique index. Here is the current index:
ALTER TABLE [dbo].[Qry] ADD CONSTRAINT [Qry_PK] PRIMARY KEY NONCLUSTERED
(
[QryNum] ASC,
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Question: Will the SORT affect the overall performance? If so, is there anything I should change within the index that would speed up my query?
View 5 Replies
View Related