Melick's Blog

SqlConnection SC = new SqlConnection( "Place your connection string here" ); string sqltxt = "" ; string ipath = "C:\Backup.Bak" ; sqltxt = @"BACKUP DATABASE SampleDB TO DISK = ' " + ipath + "' WITH INIT " ;     SqlCommand sqlCm = new SqlCommand(sqltxt, SC); SC.Open(); sqlCm.ExecuteNonQuery(); SC.Close();

There are many scenarios that you need to list down all your existing Reporting subscription in the SharePoint. Sometimes , you might find your reporting server subscription is not showing the in the manage subscription menu. There is a reason for it. Currently subscriptions  are saved based on URL (Alternative access URL) and User. So if your farm has different AAMs, even though you are a same user , it will not show under your subscriptions. So how you can get it. There is a no direct function to get this unless you are using SSRS API to query the data. but you can find these entries by simply querying SSRS SharePoint database.   SELECT   *    FROM [ReportingService_ServiceApplication_DB].[dbo].[Subscriptions] with(nolock) You can find below details [SubscriptionID] ,[OwnerID] ,[Report_OID] ,[Locale] ,[InactiveFlags] ,[ExtensionSettings] ,[ModifiedByID] ,[ModifiedDate] ,[Description] ,[LastStatus] ,[EventType] ,[MatchData] ,[LastRunTime] ,[P...

How to copy you production SharePoint  database to development environment. I had my production environment as SharePoint 2013 SQL Server 2012 and I want to move the instance to another environment , which is freshly installed and different environment and different KBs Share Point 2013 SQL Server 2016 How to migrate SharePoint 2013 , SQL Server 2012 Content Database to SharePoint 2013 SQL Server 2016. Method 1. This is a general method, which is used for upgrading your content database. 1 . Get the back up of the current Content Database 2. Copy SQL backup to new environment 3. Restore the SQL Back Up in to the new environment 4. Go to the Security Login section and grant permission to content database for existing SharePoint Users.   5. You need to select the User mapping and DBO for the content database. 6 Then, execute below PS in SharePoint PowerShell. Make sure you executed as Run as Administrator 7 Firstly, You need to Update the content database. 8. then, mount the usi...

This is a bit of a DBA task. I have come across with a situation which I want to check Cross Database Reference for SharePoint Service Application databases like ReportingService_ServiceApplication_DB You can use following SQL scripts to find database cross references and references with in the Database. Database Cross References SELECT  OBJECT_NAME (referencing_id) AS referencing_object, referenced_database_name,  referenced_schema_name, referenced_entity_name FROM sys.sql_expression_dependencies WHERE  referenced_database_name IS NOT NULLAND is_ambiguous = 0;  Object References with in the Database SELECT  OBJECT_NAME (referencing_id) AS referencing_object, referenced_database_name,  referenced_schema_name, referenced_entity_name FROM sys.sql_expression_dependencies WHERE is_ambiguous = 0; 

Insufficient SQL database permissions for user 'Name: doman\SP_UserProfiles SID: S-1-5-21-2738186777-3937669245-3595685374-1123 ImpersonationLevel: None' in database 'SharePoint_Config' on SQL Server instance 'ADXWSQL.adxweb.ae'. Additional error information from SQL Server is included below. The EXECUTE permission was denied on the object 'proc_putObjectTVP', database 'SharePoint_Config', schema 'dbo'. This is a caused by not having permission to database objects of using least privileges configuration in farm configuration. Thus you need to give permission exclusively to this Object. First you need to Connect to SharePoint Database using privileged account. Then go the SharePoint Config Database and security section. Then go to Roles and find WSS_Content_Application_Pools under the Database Roles section. Double click (or Right Click -> Properties) the item and select the Securable section in the modal window. And click Searc...

  This error occured to me when im trying to access the Manage Processing Oprions in SharePoint SSRS. “ An error occurred within the report server database.  This may be due to a connection failure, timeout or low disk condition within the database. —> Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerStorageException: An error occurred within the report server database.  This may be due to a connection failure, timeout or low disk condition within the database. —> System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object ‘xp_sqlagent_notify’, database ‘mssqlsystemresource’, schema ‘sys’ .” Cause is configurations isses and provisioning issues of Reporting Server User on Related Databases (Master , SSRS) You can resolve the issue by re run the script on your database server. For that you can downlod the report from the Central Admin by Application Management  ->  Manage service applications Then go to Repo...

  This error occured to me when i triyng to connect to a SQL database from SharePoint App which is use Entity Framework. This is a timeout issue when app is trying to connect to a SQL database and connection was expired before execution. I simply connected it adding a Coonection Timeout to the connection string. <add name="dbtemponeEntities" connectionString="metadata=res://*/DataModels.dbtempone.csdl|res://*/DataModels.dbtempone.ssdl|res://*/DataModels.dbtempone.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=ASQL;initial catalog=dbtempone;persist security info=True; Connection Timeout=120 ;user id=SQLAdmin;password=*****$so;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

SQLState = 08001, NativeError = 53 Error = [Microsoft][ODBC Driver 11 for SQL Server]Named Pipes Provider: Could not open a connection to SQL Server [53]. SQLState = 08001, NativeError = 53 Error = [Microsoft][ODBC Driver 11 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. SQLState = S1T00, NativeError = 0 Error = [Microsoft][ODBC Driver 11 for SQL Server]Login timeout expired This is a error i have got when my program is trying to connect to a SQL Server 2012 using named pipe. I did a little google and found below article and followed it very carefully and still i got the error. https://www.mssqltips.com/sqlservertip/2340/resolving-could-not-open-a-connection-to-sql-server-errors/ Futhur digging my syl server is in different act...

Concatenating Text and INT SELECT 'text' + CAST (ID AS VARCHAR (20))

This error occurred to me when i was trying to add .bism model file in SharePoint Power view configuration in WFE server . The reason would be power view configuration process not installed the Microsoft.AnalysisServices.Xmla.dll in the WFE server, You can check the dll by navigating C:\Windows\assembly in the particular server. If this dll is not available you need navigate ( PCUSOURCE\1033_ENU_LP\x64\Setup ) in your SQL installation and execute spPowerPivot with out PowerPivot options.

I encountered a problem when using the slowly changing dimension (SCD) in large databases. SCD is easy and fast when it comes to less number of records. therefore I used following mechanism to  speed up the process using available SSIS components. First record set is send through Lookup command . in there we are checking that item is already available with the table. If yes we are redirecting to UPDARE Command and If not we are redirecting to Insert Command . But make sure following redirect option (Redirect rows to no match output) is set in the lookup component.

This is not a complete post. I came across a situation that I needed to show [Date] dimension values which is not empty as report parameter. But I need yeas that only have values in measure. there for I used following MDX to get that. with member yKey as [ Date ].[ Year ].currentmember.uniquename member yValue as [ Date ].[ Year ].currentmember.MEMBER_CAPTION select {yKey,yValue} on 0, { filter([ Date ].[ Year ].[ Year ],[Measures].[Total Amount]<> null ) } on 1 from [RetailigenceBI] this code result in getting

This error Occurred to me while I design the query in the Query Builder and generate the report in SSRS ( MDX). Months are showing alphabetical order (April , August,..) , but it should be January , February .. I did some background works and even follows the the thread http://social.msdn.microsoft.com/Forums/en/sqlreportingservices/thread/27765eda-371d-4428-ab13-2e96b2fbb6ca . But ultimately I  found there is a sorting adding by default to that column. I Deleted it .. Bingo .. It worked ..  

  This error happened to me when i try to add xml to the SQL server. this is because of i didn't mentioned the proper character set in the XML. Thus is used following character set definition in the top of the XML.  <?xml version="1.0" encoding="windows-1252" ?>  

  This error might come for different scenarios. In my case this error occurred when i going to connect to the SQL server using  BCS (Business connectivity services in SharePoint). though i provide correct user name and password the error is coming. when i look in to the problem i found that SharePoint SQL database server is running on different port to protect unauthorized access. But then how we going to connect ? It is pretty simple but i wasted whole 3/4 hours to figure out that. normally we are giving port like Servername:port but unfortunately this is not working but if we give ‘comma’ rather than semicolon it is working fine. servername,port  

This problem is occurred me while I'm ruining a SQL in compact device. So i resolved this error by installing   above files. If this will not work try wce500 folder also. it may work.

First create a data base using SQL Server. (You can use Visual Studio to create your database) Type your new database name and press ok to continue. Then it will prompt dialog for creating a new database. Then press ok , Now you created a database in your SQL Server. After that go to Visual Studio Command prompt Program Files—> Visual Studio 2008 command prompt and type aspnet_regsql.exe   or C:\Windows\Microsoft.NET\Framework\v2.oxx and run Aspnet_regsql.exe. This command will launch wizard which can use to generate relevant database structure to selected database. Now we have to configure the web.config < connectionStrings > < add name = " ConnectionString " connectionString = " Data Source=TestDB;Initial Catalog=Login;User ID=sa;Password=sapassword " /> </ connectionStrings >   < system.web > < roleManager enabled = " true " defaultProvider = " SqlRoleProvider " > <...

First of all you need to have a Data Connection. (Read.. How to create a Data Connection to SQL Server using C#.net Wizard ) Then you need to have data set and table adapters configured (Read.. How to Create a Dataset, Data Tables, Table Adapter Using C#.NET Wizard)   Then add a data grid ( dtgrid ) to your windows form.  And use following code. When we not saved our connection we need to give connection settings to the table adapter when ever it wants to connect to the data base. private void btnSelect_Click( object sender, EventArgs e) { // using System.Data.SqlClient; import this namespace SqlConnectionStringBuilder cb = new SqlConnectionStringBuilder (); cb.UserID = "sa" ; cb.Password = "sa123" ; cb.InitialCatalog = "CustomerInfo" ; // Create SQL Connection by giving SQL Connection String SqlConnection sc = new SqlConnection (cb.ConnectionString); CusDataSetTableAdapters. CusMainTableAdapter Ta = ...

To create a DataSet First you need to add a data connection to visual studio (Read…).   Assume we are having a table called CusTransactions and having columns CustomerName , InvoiceNum, TotalAmount , Paid , Balance. In my scenario I want to add a Data table to my data set including CustomerName , InvoiceNum and TotalAmount . therefore  first we want to add a dataset. Thus go to Project –> Add new item –> DataSet. And named it as CusDataSet. When you add the data set visual studio shows DataSet Designer. This is the space you can configure your data adapters  and  data tables. ( To add data adapters you can straightway drag and drop tables from your data connection also.) To add a data adapter right click the dataset  designer window and goto Add—> TableAdapter . Then the coming window select the relevant data connection. (You can also create new data connection here)       After the selecting the connection click next to proce...

For this first  go to server explorer and right click it. then go to Add connection. Then a screen will  come. In that you can enter the server name  and login credentials (username password or windows authentication). Then select the relevant database and use Test Connection to verify the connection. (Windows Authentication takes your windows credentials to log in to SQL Server, If you cant find the Server name open the SQL Server Management Studio and go to server properties.) Read.. (How to Create a DataSet, DataTables, Table Adapter Using C#.NET Wizard)