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'
I have a CLR assembly loaded (UNSAFE) in SQL Server 2005. Assembly is in written C#. It calls functions from another assembly (also successfully created in SQL Server) written in Managed C++ and compiled with /clrure. The later one calls functions from native DLL. When I try to call one of the functions from my C# assembly I get this:
Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "MyClrFunction":
System.TypeInitializationException: The type initializer for '<Module>' threw an exception. ---> System.IO.FileLoadException: Could not load file or assembly 'msvcm80, Version=8.0.50727.1433, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. Failed to grant permission to execute. (Exception from HRESULT: 0x80131418) ---> System.Security.Policy.PolicyException: Execution permission cannot be acquired.
at <CrtImplementationDetails>.ThrowNestedModuleLoadException(Exception , Exception )
at <CrtImplementationDetails>.LanguageSupport.Cleanup(LanguageSupport* , Exception innerException)
at <CrtImplementationDetails>.LanguageSupport.Initialize(LanguageSupport* )
at .cctor()
System.TypeInitializationException:
at Functions.MyClrFunction(SqlString key)
. Calls into C# assembly work perfectly. Calls into C++ assembly produce the above message no matter what I don inside (even if I just return immediately). Any hints what's wrong? Or at least how I can track this down?
Hi, This is regarding the SQL 2005 Report Manager (http://localhost/Reports) page. I have posted the reports, the reports run fine, buttons like new folder, new data source, etc... are visible (lets call this machine as A). The problem is when my machine is accessed from a nearby computer on the same network as http://machineA/Reports, the Report manager Home page appears with limited or no buttons. The reports are also not visible in the browser of machine B. How do I make the reports visible to users (grant permission) for them to run and view those reports?
In a nutshell, how the posted reports be accessed by users?
"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.
I created some reports and save rdl files in a shared folder. Now some of managers want to view those reports using visual studio or BIDS(not from report manager). But they should not have access to modify the report and they can only open the rdl file and preview it. so i'm trying to set up security level to this folder so that they can only view the rports. So I gave read, read attributes and read data permission to that folder. But still not only I can open the file but can change it too. Does any one know how to set this security level so that when the rdl file is opened using VS, that perticular group can only preview reports?
I got an error while deploying my report: The permissions granted to user machineNameuserName are insufficient for performing this operation. After reading the posts in the forum, I realized that my RS setup may be incorrect. On http://localhost/reports, Contents and Properties tabs are missing... Please let me know how I can correct the problems. I am using SQL Server 2005, and I have admin rights on my local machine
Protected Sub ReportViewer1_Drillthrough(ByVal sender As Object, ByVal e As Microsoft.Reporting.WebForms.DrillthroughEventArgs) Handles ReportViewer1.Drillthrough
While (Me.ReportViewer1.ServerReport.IsDrillthroughReport)
When I drill-through the report and hit the (browser) back button and try to drill-trough again I get the following error:
The path of the item "(null)" is not valid. The path must be less than 260 characters long and must start with slash. Other restrictions apply. (rsInvalidItemPath)
When I hit back again and try to export the report to eg. PDF, I get this error: Execution 'mly2yj555oen0o45oowe1e55' cannot be found
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Exception: Execution 'mly2yj555oen0o45oowe1e55' cannot be found
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
Scenarion: 1.- SSIS Package execute tasks on 2000 SQL Server Database 2.- Execution takes places using Business Intelligence Studio Question: 1.- How can I tracked that SQl 2000 tasks took place using a SSIS Package?
I have a VB.NET scheduled job as a task scheduler ( windows 2012) that calls a stored procedure that bulk inserts. I have added the user in the server role "bulkadmin" yet I get the "You do not have permission to use the bulk load statement error"
System.Data.SqlClient.SqlException (0x80131904): You do not have permission to use the bulk load statement. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream,
How to grant DROP table permission within a database to an SQL login. I could see in Databae properties-> permission tab, there is an option to grant CREATE TABLE, EXECUTE permission etc, but not DROP table permission. How to grant it?
Have run to a select permission error when attempting insert data to a table. received the following error
Msg 229, Level 14, State 5, Line 11 The SELECT permission was denied on the object 'tableName', database 'DBname', schema 'Schema'.
Few things to note - There are no triggers depending on the table - Permissions are granted at a roll level which is rolled down to the login - The test environments have the same level of permission which works fine.
Just encountered something that I wasn't expected, in that a user who has an explicit deny on a column in a table was able to select it when referenced through a view in a schema they have the SELECT permission on. This seems to me to go against the principle that DENY overrides everything when it comes to permissions? Is this how it's meant to work?
Code is below:-
--create test user CREATE USER TestDenyOnViewUser WITHOUT LOGIN GO
--create test schema (authorization dbo - same owner as dbo schema so ownership chaining will apply) CREATE SCHEMA TestDenyOnView AUTHORIZATION dbo
I have a sql server 2012 server and I need to prevent the users from creating new schemas by mistake. Is there any way to revoke that permission alone but still letting the user to create their own objects in dbo (yes I know that shouldn't be in dbo but that is another issue).
I want to find out more information about the execution plan. I saw Parallelism (Gather Streams) in the excution plan. In what situation we can see this icon?If in case if we need to avoid how can we avoid this?
We have got a query for fine tuning and it is using lot of CTE ,how can i check the execution plan of that?
CREATE VIEW Mercy AS with ADR as ( SELECT urpx.RoleID , urx.UserID FROM [DBA].dbo.URPX WITH ( NOLOCK ) INNER JOIN [DBA].dbo.URX WITH ( NOLOCK ) ON urpx.RoleID = urx.RoleID WHERE PermissionID = '1' ), SDR as (
-- Collect the roles that a configured with Sales Team Create permission
-- This will include Sales Director , Suite Admin,
SELECT urpx.RoleID FROM [DBA].dbo.URPX WITH ( NOLOCK ) INNER JOIN [DBA].dbo.URX WITH ( NOLOCK ) ON urpx.RoleID = urx.RoleID LEFT OUTER JOIN ADR ON ADR.UserID = urx.UserID WHERE ADR.RoleID IS NULL AND PermissionID='2' )
I have an execution plan that is huge, the pdf it generates if I print it is over 1000 pages. Is there a way to change the graphical plan into a table, so I can sort the %, and find the items that are taking the longest?
I need to implement one logic similar to rule engine. Below is the example.how to execute all queries in second table order?? what is the best way to implements this
declare @tblRules AS Table (RuleNo INT, RuleDesc NVARCHAR(500), RuleQuery NVARCHAR(MAX), QueryExecutionInterval NVARCHAR(50)) declare @tblRuleResults AS Table (RuleResultID INT, RuleNo INT, ExecuteTime DateTime, NextExecutionTime DateTime, Result NVARCHAR(10)) INSERT INTO @tblRules VALUES ('1','Fail - 2 times within 1 Hour','XXX','Every 15 Minutes') INSERT INTO @tblRules VALUES ('2','Fail- 2 times within 2 Hour','YYY','Every 30 Minutes')
I have a query which contains 12 left outer join. I remove some of the joins that don't have parameters. The result is coming same but usually when we remove joining it should take less exec time but for me it is taking more time. What could be the reason?
I setup SQL Server 2012 on Windows Server 2012 with the service accounts in the local Administrator group, but now that I'd like to remove the accounts from this group I'm finding they don't have the appropriate access to the network storage. notes on setting the per-service SID's for SQL (SQL Engine, Analysis Services, Reporting Services, and Agent Service) so they can read the Data, Log, and TempDB mount points?
Whenever you connect to a database server that is running SQL 2012 integration services, then you right click on SSISDB, choose "Reports, then Standard Reports, then Executions" there is a display of each execution and options to drill down.
There is a particular execution that when I click to see "All Messages" I get a blank screen in SSMS with the following error: "Error: and error occurred during local report processing. --> An error has occurred during report processing. --> Exception of type 'System.OutOfMemoryException' was thrown."
Before I go add more memory to this SQL box, I need to understand how an out of the box, canned SSRS report such as this, which is built into Integration Services could produce this issue?
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?
know if there is any way out to run execution plan for parameterized queries?
As application is sending queries which are mostly parameterized in nature and values being used are very robust in nature, So i can not even make a guess.
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?
I have a SSIS package developed by a different user which does a lot of DML. This package sits on the server.
The package needs to be executed on a regular basis.
I have given RO access for a regular user on production DB, he is executing the package from his client desktop.
I was expecting this execution fail, since the package is doing lot of INSERTS , where the user has ONLY RO access.
I understand from the above experience that there is an “execution context? for SSIS execution. Can someone tell me how can I define the execution context for SSIS?
------------------------ I think, therefore I am - Rene Descartes
I have been using SSIS now for quite sometime and over teh past month when i open for edit or execute a package SSIS just seems to go away and wait for about 10 minutes before opening or starting the execution.
I have defragmented my drive, i have monitored cpu usage and it just looks like it is not doing anything at all.
I am running windows xp connecting to windows 2003 servers with sql server 2005.
Has anyone else experienced this and can anyone help?
when using lookup i am geting the following warning.our OLEDB connection is Oracle.how to resolve this will this have any performance impact.
[Lookup [14342]] Warning: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.
I have multiple data source text files with different names , file extensions and format , i need to bring the data into SQL tables for temporary holding of data. Once i bring the data into table i need to identify some fields using substring and then after cleansing at table level command. Once thats done i need to convert those tables back into text file with comma delimited join those tables and bring a different output.
Problem i am facing is if i have one data flow task then the first source file bring the data into table which is destination , but how to convert the data back using those tables back in to text file.
basically when i create one source file which is text file becomes source of data flow task and when convert those files into tables becomes destination. but when i need to bring the data back into a different format , the tables which were destination needs to become source.
Do i need to create a multiple data flow task or is their any other way i could work out.
Which is the best way to execute SSIS packages? I have no problem to use dtexec command but I want it to run every night. Is this going to be done through the SQL Server (SQL Server Agent under Jobs)? Is the type going to be Operating System (CmdExec) or Transact Sql? How exactly is the command written there?