SansSQL

Saturday, November 15, 2014

Import data from SharePoint Online

It's all cloud could and cloud now-a-days. Everyone are moving towards the cloud for making life easier.
In this post I will be explaining on how to extract/Import data from SharePoint Lists which is hosted on SharePoint Online of Office 365 using SSIS.
There is no direct way to connect to the SharePoint Online using SSIS.
For connecting to SharePoint Online using SSIS, we need the below components installed before stating.
  1. Microsoft OData Source for Microsoft SQL Server 2012
    Download from http://www.microsoft.com/en-in/download/details.aspx?id=42280
  2. SharePoint Server 2013 Client Components SDK
    Download from http://www.microsoft.com/en-in/download/details.aspx?id=35585 
Microsoft OData Source for Microsoft SQL Server 2012 is the SSIS component which we use as source connection to the Sharepoint Online Lists and SharePoint Server 2013 Client Components SDK allows us to connect to Sharepoint Online.

Once you download and Install both the components, 
  1. Open the Visual Studio and create a new Integration Services Package
  2. Drag and Drop "Data Flow Task"
  3. In the "Data Flow Task" Drag and Drop "OData Source"
  4. Right-Click on "OData Source" and Click Edit
  5. Enter the "Connection Manager Name" and "Service Document Location" For Service Document Location, append "/_vti_bin/listdata.svc" to the SharePoint site
    For Office 365, we need to use "User this user name and password"
  6. Click "All" and change the "Microsoft Online Services Authentication" to True
  7. Click "Test Connection"
  8. Click "OK"
  9. Then Choose "Collection" in "User Collection or resource path"
  10. In the "Collection" choose the list you want to import
  11. When you click on "Preview..." you will be able to see the data from the list
  12. Now add the destination connection and run the package to import data from the SharePoint Online list
Related Posts Widget For Blogger with ThumbnailsBlogger Templates

Tuesday, October 14, 2014

Error while connecting to SQL Server Instance on a Windows Azure Machine from local SQL Server Management Studio

When you try to connect to an SQL Server Instance hosted on Windows Azure, it works perfectly fine from within the server or through Azure management portal.
However, when you try to connect to the same instance from an local SQL Server Management studio, you get the below error which says "The connection string is not valid. The parameter is incorrect"

This is because, the name of the server specified will be wrong. For connecting to the instance hosted on azure, the server name we specify is slightly different from what we specify regularly.

Make sure the connection details you specify is as below.

Server type:  Database Engine
Server name: tcp:<servername>.cloudapp.net,<DB Port Number> OR tcp:<servername>.database.windows.net
Authentication:  SQL Server Authentication
Login: SQL User Name
Password: Password

Thursday, October 9, 2014

Review on SysTools Master SQL Server Database Recovery Tool - A Guest Post by Andrew Jackson

Many might be aware of SysTools Software Company, which is a brand name in data recovery industry. Since its start in the year 2007 it has developed multiple software applications and served people all over the world with its highly sophisticated tools by online marketing. One of its key and highly talked about utility is SQL Recovery that is said to recover corrupt MDF and NDF files of MS SQL Server database (DB) and then export it into the database of server. It has been designed and redesigned quite a few times to provide users with new characteristics with its each new release.
Each edition of the SQL Server database recovery tool comes equipped with more enhanced and advanced features as compared to the previous one. The current version of the recovery tool that is being offered is 6.0. It is said to possess quite a number of amenities so as to facilitate people in recovering database and the compatible scripts too. However, the characteristics of the application need to be discussed in detail so as get a complete and overall understanding. The product review in fact is a step forward towards the same purpose. So, here goes a description of the key facilities and are mentioned in the section below:

Key Characteristics
This section of the review contains a detailed explanation of the multiple features of the SQL Server database recovery tool:   
  1. SQL Server DB File Recovery - The database of Structured Query Language server stores Tables, Rules, Stored Procedure, Functions, Views, Triggers and its associated Primary Key, Unique Key and many others  components. Each and every part is said to be recoverable on corruption be it regardless of the fact whether it is minor or major corruption.
  2. Primary & Secondary File Recovery – The primary files are the ones with .mdf as the extension while the secondary with .ndf file extension and both of them form the crucial part of server database in which data items are stored. Even when they get damaged the utility recovers it successfully without any difficulty.
  3. Recovery of Permanently Deleted Data - Sometimes data items of the server database gets removed permanently, knowingly or unknowingly, from the files. With Advance scan option both .mdf as well as .ndf file gets deeply scanned and with this even the permanently deleted data gets retrieved.
  4. Ability to Fix Major SQL Errors Also – When the level of corruption that affects the files of SQL server database is very high, then the damage caused to the primary and secondary files are also severe in nature and major errors prompt up. Even in such cases of rigorous damage, the tool fixes the errors while easily recovering both the file and its data. 
  5. Supports SQL Server 2012 and All below Versions – The tool is devised to recover corrupted files of almost all editions of the SQL server like 2012, 2008, 2008 R2, 2005 and also 2000 database files. However, the latest release is 2014 but it is not supported by the recovery application.
  6. Supported on All Releases of Windows Operating System – The most new version of MS Windows operating system is 8.1 and many users possess it. Therefore, the utility should be and is supported on all editions like Windows 8, 7, Vista, etc. including the latest one. 
Other Significant Properties of the Recovery Tool  
  • Multiple secondary files (with .ndf extension) can be scanned and recovered at a time. This helps in reducing time for recovery.  
  • Can export and save recovered file into both SQL Server database and also as SQL Server Compatible Scripts which is indeed a good feature. 
  • Offers choice of selection to export and save file with either only Schema or both schema and data as well. This is a highly advantageous as it gives user the power to follow his/ her needs.
  • Quick scan is provided to recover files from minor corruption issues. This is important because this form of scan takes less time to be executed as compared to Advance Scan mode of recovery.
  • Has the power to detect the version of SQL server automatically. This is indeed a great feature as it recovers damaged file even when the user is unaware of the edition of SQL server.
  • Minimum system requirements in terms of hard disk space, RAM and processor to install the software.
  • Secured by two powerful anti-malware technologies that are Norton and McAfee that protects the machine on which the tool is run.
Overall Performance Review 
As per the discussion on the characteristics of the SQL Recovery version 6.0 that functions as SQL Server database recovery tool, it can be concluded that the application is an appropriate selection to recover any kind of corruption issue in either the primary or its associated secondary files. With its extremely high tech design, the functionalities that it renders are matchless as compared to the ones developed by other companies. But the point where it lacks is that it does not support the most new release of SQL Server by Microsoft i.e. edition 2014. This drawback will restrict some users in making use of the utility but sidelining this point the software proves to be an apt solution.

Andrew is a SQL Server DBA at SysTools and he is based out of New York City.
He has nearly 7 years of rich experience on SQL Server Database administration skills.

Tuesday, September 2, 2014

T-SQL to find Job Name of Subscribed Reports

Here is an handy T-SQL script which gives information about your subscribed reports in SSRS.

USE ReportServer
GO
SELECT 
   SJ.name AS JobName
  ,Cat.Name AS ReportName
  ,Sub.Description AS SubscriptionDescription
  ,Cat.Path AS ReportPath
  ,Sub.DeliveryExtension AS SubscriptionDeliveryType
  ,Sub.LastStatus
  ,Sub.EventType
  ,SJ.date_created AS SubscriptionCreatedDate
  ,SJ.date_modified AS SubscriptionModifiedDate
FROM msdb.dbo.sysjobs SJ
INNER JOIN ReportSchedule RS ON SJ.name=CAST(RS.ScheduleID AS VARCHAR(max))
INNER JOIN Subscriptions Sub ON Sub.SubscriptionID=RS.SubscriptionID
INNER JOIN Catalog Cat ON Cat.ItemID=Sub.Report_OID

Thursday, August 28, 2014

T-SQL to get running sessions and its progress details

Here is a handy T-SQL to get running sessions and its progress details.

SELECT 
  ExecSessions.session_id AS SessionID
 ,ExecRequests.blocking_session_id AS BlockingSessionID
 ,db_name(ExecRequests.database_id) AS DatabaseName
 ,ExecSessions.login_name AS LoginName
 ,ExecSessions.memory_usage AS MemoryUsage
 ,CONVERT(dec(6,3), (ExecRequests.wait_time/60000.00)) AS [WaitTime (mins)]
 ,ExecRequests.[status] AS [Status]
 ,ExecRequests.percent_complete AS PercentComplete
 ,[Text]
FROM sys.dm_exec_sessions AS ExecSessions INNER JOIN sys.dm_exec_requests AS ExecRequests 
ON ExecSessions.session_id = ExecRequests.session_id
CROSS APPLY sys.dm_exec_sql_text (sql_handle) 
WHERE ExecSessions.status = 'running' AND ExecSessions.session_id <> @@SPID
ORDER BY ExecSessions.session_id