:::: MENU ::::

Posts Categorized / dbDigger

  • Sep 26 / 2013
  • 0
dbDigger, Linked Servers and remote data

Concerns to keep in mind and analyze before going for linked servers

Linked Servers are used in SQL Server to get connect and retrieve the data from outside your local server instance. We may link to other SQL Server as well as non SQL Server DBMS. It is a mature and proven technology. Rather than discussing the configuration method i will discuss some concerns that should be analyzed and take care while implementing the Linked Server.

  • Joining the data between local and linked server databases will result in poor performance. And performace may go worst if link is on WAN.
  • Execution of procedure on remote server will leak memory on local server for each call from it. It is due to return status of procedure even if it is not used by the caller. This leak is very minimal about 40 Bytes per call but may accumulate over the time.
  • SQL variant data types also cause minor memory leaks for local server. Large character strings of this type may accumulate considerable memory leaks over the time.
  • Performance – Optimizing is always a challenge and distributed queries are even more difficult. An extra complication with linked servers is that to get statistics from the remote server, OLE DB runs DBCC SHOW_STATISTICS. But this command requires ‘sysadmin’ permission or membership in ‘db_owner’ or ‘db_ddladmin’, which is not expected from a regular user to have, no matter he accesses the linked server with his own credentials, or through a proxy account defined.
  • There are a number of SQL Server features that are not supported with linked servers
    • XML data type
    • Table-valued parameters
    • CLR User-defined data types
    • Cannot call table-valued functions with four-part notation
    • MAX parameters cannot be OUTPUT
    • BULK INSERT into linked server is not supported. More details here
  • Error messages with linked servers and distributed transactions are often very obscure. Often it includes an OLE DB error, which is difficult to understand. Also, the useful part of the error message is often only raised with level 10, so you cannot catch it. A prime example is when a remote call times out.
  • Distributed transactions – The MSDTC has following problems:
    • Cannot use SAVE TRANSACTION in a distributed transaction
    • Some errors when a distributed transaction fails are not catchable
    • Once you have rolled back locally, there is very little you can do, for instance you cannot call a stored procedure
  •  Security and permissions for linked servers will require extra effort and skill.
  • Sep 24 / 2013
  • 0
dbDigger, Reporting Services SSRS, T-SQL Scripts

Get scheduled job name for specific SSRS report subscription

I was required to rerun some failed SSRS subscriptions. As we know that SSRS subscriptions automatically create scheduled jobs in SQL Server agent. The problem is that job names are not readable and it is some UID. So first of all i got the list of failed subscriptions from execution logs and then i was required to get job name for each failed subscription so that it may be executed manually. For this purpose i found following piece of code and it worked fine for me. In following example i got the job name for a report named MyReport.

SELECT b.NAME AS JobName,e.NAME  ,e.path  ,d.description  ,a.SubscriptionID  
   ,laststatus  ,eventtype  ,LastRunTime  ,date_created  ,date_modified  
 FROM ReportServer.dbo.ReportSchedule a  
 JOIN msdb.dbo.sysjobs b ON a.ScheduleID = b.NAME  
 JOIN ReportServer.dbo.ReportSchedule c ON b.NAME = c.ScheduleID  
 JOIN ReportServer.dbo.Subscriptions d ON c.SubscriptionID = d.SubscriptionID  
 JOIN ReportServer.dbo.CATALOG e ON d.report_oid = e.itemid  
 WHERE e.NAME = 'MyReport'  
 and b.description = 'This job is owned by a report server process. Modifying this job could result in database incompatibilities. Use Report Manager or Management Studio to update this job.'   

Above piece of code may be found in full context here.

  • Sep 23 / 2013
  • 0
dbDigger, Linked Servers and remote data, System Administration, Windows Server

Copy paste not working during remote desktop sessions

I use Remote Desktop utility to get connect to the online servers if required. While using the remote desktop it enables us to share the clipboard among both the OS to copy the data. A couple of days back i noticed that i am no more able to copy data from or to my laptop from the server connected through remote desktop. It was problematic and first thing that came to mind was that i should verify the local resources settings in my remote desktop connection panel. It had following settings and nothing was wrong here

Data copy not working on remote desktop session

So now i had to google for any further clue and I found the reason in a minute by googling. I performed following steps to solve the issue.

  • Login to the remote server on Remote Desktop
  • Open task manager (by right clicking on taskbar)
  • Go to Processes tab
  • Search the process RDPClip.exe
  • End the process RDPClip.exe
  • Go to Applications tab in task manager
  • Click on New Task and start RDPClick.exe by entering in box

The issue is solved here. We may also implement the same  solution through following cmd commands for the same tasks
Taskkill.exe /im rdpclip.exe
Rdpclip.exe

  • Sep 11 / 2013
  • 0
dbDigger, Reporting Services SSRS

SSRS 2008 R2 configuration files

Various SSRS settings can be seen and modified in the provided configuration files. We should backup these files and log any changes made to these. Following is the list of major SSRS 2008 R2 log files along with their path on disk

  • RSReportServer.config: It stores the configuration settings for Report Manager, the Report Server Web service features as well as background processing. Click here to get its details on BOL.
  • RSSrvPolicy.config: It stores the security policies for code access of the server extensions. Click here to read about using Reporting Services security policy files.
  • RSMgrPolicy.config: It stores the security policies for code access of report manager. Click here to read about using Reporting Services security policy files.
  • Web.config for the Report Server Web service: Includes only the ASP.Net related settings.
  • Web.config for Report Manager: Includes only the ASP.Net related settings.
  • ReportingServicesService.exe.config: It stores the configuration settings related to trace levels and logging options for the Report Server service.Click here to get its details on BOL.
  • RSReportDesigner.config: It stores configuration settings for Report Designer. Click here to get its details on BOL.
  • RSPreviewPolicy.config: It stores the code access security policies for the server extensions used during report preview. Click here to read about using Reporting Services security policy files.
  • Registry setting: It stores configuration state and other settings used to uninstall Reporting Services. If you are troubleshooting an installation or configuration problem, you can view these settings to get information about how the report server is configured. However these settings should not be modified directly.
  • Aug 29 / 2013
  • 0
Data Modeling and Database Design, dbDigger, Security and Permissions, T-SQL Interview Questions, T-SQL Tips and Tricks

Drawbacks of using Dynamic SQL

I am performing some knowledge discovery that what may be the bad effects of using dynamic SQL in SQL Server environment. So while going through some good articles on it i have figured out following major issues/side effects of dynamic SQL

  • Query plans of Dynamic SQL are not cached so not reused
  • Dynamic SQL can put you vulnerable for SQL injection attack
  • Messy quotation marks and  spacing complicate the query writing
  • Network traffic is increased as compared to a USP executed
  • Generating the dependencies through various methods becomes unreliable as objects used in dynamic SQL can not be traced by system views
  • Ownership chaining is skipped hence permissions are compromised

These major reasons are enough to think twice before using the dynamic SQL. So use it wisely and also look for options to avoid it.

  • Aug 28 / 2013
  • 0
dbDigger, SET Options, SQL Server Error messages

Error message when creating assembly in a SQL Server database

I was required to transfer SQL Server assembly along with the other objects from one SQL Server instance to another. I used Transfer SQL Server Objects which is a handy control of SSIS. During the test process i get following error as a result of trying to create assembly in target database.

[Transfer SQL Server Objects Task] Error: Execution failed with the following error: “ERROR : errorCode=-1073548784 description=Executing the query “CREATE ASSEMBLY [%]
AUTHORIZATION [dbo]
FRO…” failed with the following error: “CREATE ASSEMBLY for assembly ‘%’ failed because assembly ‘%’ is not authorized for PERMISSION_SET = EXTERNAL_ACCESS.  The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.helpFile= helpContext=0 idofInterfaceWithError={C81DFC5A-3B22-4DA3-BD3B-10BF861A7F9C}”.

This error is produced because before creating the assembly we have to mark the database as trustworthy. By default trustworthy mode is disabled for SQL Server databases. Trustworthy mode tells the server that this database contains controlled creation of objects and there is no chance that some one will create malicious objects in it or some one will manipulate it by attach/detach process. So if you are satisfied with above assumptions that you are going to give to your server then use following command in subject database and set it as a trustworthy database

ALTER DATABASE [DBNameHere] SET TRUSTWORTHY ON
GO

After this step we are now able to create and refer assemblies in SQL Server database and error exists no more.

Pages:12345678...84
Consult us to explore the Databases. Contact us