Windows 2012 Hosting - MVC 6 and SQL 2014 BLOG

Tutorial and Articles about Windows Hosting, SQL Hosting, MVC Hosting, and Silverlight Hosting

ASP.NET Hosting - ASPHostPortal.com :: How to Prevent ASP.NET Website From Multiple Database Request

clock October 26, 2015 20:44 by author Dan

It is not good to execute multiple db request for loading single page.  Review your database code to see if you have request paths that go to the database more than once. Each of those round-trips decreases the number of requests per second your application can serve. By returning multiple resultsets in a single database request, you can cut the total time spent communicating with the database.

In order to improve performance you should execute single stored proc and bring multiple resultset in to single db request.  In this article i will explain you how to avoid multiple database request and how to bring multiple resultset into single db request.

Consider a scenario of loading a Product Page, which displays

  • Product Information and
  • Product Review Information

In order to bring 2 database request in single db request, your sql server stored proc should be declared as below.

SQL Server Stored Proc

CREATE PROCEDURE GetProductDetails
 @ProductId bigint,
AS
SET NOCOUNT ON

--Product Information
Select ProductId,
 ProductName,
 ProductImage,
 Description,
 Price
From Product
Where ProductId = @ProductId

--Product Review Information
Select  ReviewerName,
 ReviewDesc,
 ReviewDate
From ProductReview
Where ProductId = @ProductId

Asp.net, C# Code to bring multiple db request into single db request

Code Inside Data Access Class Library (DAL)

public DataSet GetProductDetails()
{
SqlCommand cmdToExecute = new SqlCommand();
cmdToExecute.CommandText = "GetProductDetails";
cmdToExecute.CommandType = CommandType.StoredProcedure;
DataSet dsResultSet = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(cmdToExecute);

try
{
    var conString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnStr"];
    string strConnString = conString.ConnectionString;
    SqlConnection conn = new SqlConnection(strConnString);

    cmdToExecute.Connection = conn;

    cmdToExecute.Parameters.Add(new SqlParameter("@ ProductId", SqlDbType.BigInt, 8, ParameterDirection.Input, false, 19, 0, "", DataRowVersion.Proposed, _productId));

    //Open Connection
    conn.Open();

    // Assign proper name to multiple table
    adapter.TableMappings.Add("Table", "ProductInfo");
    adapter.TableMappings.Add("Table1", "ProductReviewInfo");
    adapter.Fill(dsResultSet);

    return dsResultSet;             
}
catch (Exception ex)
{
    // some error occured.
    throw new Exception("DB Request error.", ex);
}
finally
{
    conn.Close();
    cmdToExecute.Dispose();
    adapter.Dispose();
}
}

Code Inside Asp.net .aspx.cs page

protected void Page_Load(object sender, EventArgs e)
{
   if (Request.QueryString[ProductId] != null)
   {
      long ProductId = Convert.ToInt64(Request.QueryString[ProductId].ToString()); 
  
      DataSet dsData = new DataSet();

      //Assuming you have Product class in DAL
      ProductInfo objProduct = new ProductInfo();
      objProduct.ProductId = ProductId;
      dsData = objProduct.GetProductDetails();

      DataTable dtProductInfo = dsData.Tables["ProductInfo"];
      DataTable dtProductReviews = dsData.Tables["ProductReviewInfo"];

      //Now you have data table containing information
      //Make necessary assignment to controls
      .....
      .....
      .....
      .....
      ..... 

    }
}


Finish, Happy coding.

Best Windows Shared Hosting Recommendation

ASPHostPortal.com provides our customers with Plesk Panel, one of the most popular and stable control panels for Windows hosting, as free. You could also see the latest .NET framework, a crazy amount of functionality as well as Large disk space, bandwidth, MSSQL databases and more. All those give people the convenience to build up a powerful site in Our Shared Hosting. We offers Windows hosting starts from $5/month only. We also guarantees 30 days money back and guarantee 99.9% uptime. If you need a reliable affordable Windows Shared Hosting, we should be your best choice.



SQL Server 2014 Hosting - ASPHostPortal :: Identify ErrorLog with xp_ReadErrorLog

clock May 7, 2015 07:14 by author Dan

To read error logs in SQL Server using T-SQL you can use extended stored procedure xp_ReadErrorLog to read SQL Server and SQL Server Agent error logs. xp_ReadErrorLog has seven parameters that can be used to filter error logs.

Syntax for xp_ReadErrorLog:

EXEC xp_ReadErrorLog   <LogNumber>, <LogType>,

<SearchTerm1>, <SearchTerm2>,

<StartDate>, <EndDate>, <SortOrder>

The parameter values can be as follows:

You can use the stored procedure as:

EXEC xp_ReadErrorLog

– Reads current SQL Server error log

Below are some more examples of xp_ReadErrorLog:

EXEC xp_ReadErrorLog 1
– Reads SQL Server error log from ERRORLOG.1 file

EXEC xp_ReadErrorLog 0, 1
– Reads current SQL Server error log

EXEC xp_ReadErrorLog 0, 2
– Reads current SQL Server Agent error log

EXEC xp_ReadErrorLog 0, 1, 'Failed'
– Reads current SQL Server error log with text 'Failed'

EXEC xp_ReadErrorLog 0, 1, 'Failed', 'Login'
– Reads current SQL Server error log with text ‘Failed’ AND 'Login'

EXEC xp_ReadErrorLog 0, 1, 'Failed', 'Login', '20121101', NULL
– Reads current SQL Server error log with text ‘Failed’ AND ‘Login’ from 01-Nov-2012

EXEC xp_ReadErrorLog 0, 1, 'Failed', 'Login', '20121101', '20121130'
– Reads current SQL Server error log with text ‘Failed’ AND ‘Login’ between 01-Nov-2012 and 30-Nov-2012

EXEC xp_ReadErrorLog 0, 1, NULL, NULL, '20121101', '20121130'
– Reads current SQL Server error between 01-Nov-2012 and 30-Nov-2012

EXEC xp_ReadErrorLog 0, 1, NULL, NULL, '20121101', '20121130', 'DESC'
– Reads current SQL Server error log between 01-Nov-2012 and 30-Nov-2012 and sorts in descending order

Hope This Helps!

Best SQL Server 2014 Hosting Recommendation

ASPHostPortal.com provides our customers with Plesk Panel, one of the most popular and stable control panels for Windows hosting, as free. You could also see the latest .NET framework, a crazy amount of functionality as well as Large disk space, bandwidth, MSSQL databases and more. All those give people the convenience to build up a powerful site in SQL Server 2014 . We offers Windows hosting starts from $5/month only. We also guarantees 30 days money back and guarantee 99.9% uptime. If you need a reliable affordable SQL SERVER 2014 Hosting, we should be your best choice.



SQL SERVER 2014 Hosting - ASPHostPortal :: How to use Output Parameters in SQL Server and ASP.NET

clock May 4, 2015 05:57 by author Mark

The out parameters in SQL Server, when used in Stored Procedures, allow developers to pass a value in the database to the front-end controls like label. They are most commonly used in web application development.
Let us discuss how to create and use them in ASP.Net with a practical example. First we will design our database.

  • Create a database in SQL Server.
  • Let us create a table with three columns, say username, password and confirmation password.

Create table logintable(username varchar(max),password varchar(max),confirmpassword varchar(max))

  • Let us create our Stored Procedure.

Here we have created a Stored Procedure named usplogintable with username, password and confirmpassword as input parameters. The next variable that I have created is the @error variable of varchar type. You can see the keyword "out" near the varchar. Yes, your guess is correct, the keyword "out" stands for the output parameter in SQL Server.

We will execute this Stored Procedure as a batch so we have begins and ends. Then, "set nocount on" avoids returning the number of rows affected.
The if condition checks whether the username exists in a database and if the answer for it is yes, the @error variable is set with the username already taken or it inserts the values into the table and sets the @error variable as the username inserted.

Executing the Stored Procedure also requres a different style. First you need to declare a variable, you need to specify the output parameter on execution and you need to write a select query at the end to make it execute. Here is my sample for the preceding sp.

I have inserted the table with the values markus, mark and confirm password as mark.

  • I can guess what you are thinking. “How can I use it in my server-side code?”. Yeah, I am an ASP.Net developer and I have the solution for this. Here are the ways.

Additionally, you must also open your Visual Studio or press Ctrl+r and type devenv.
Create an ASP.Net web application with the framework being above 2.0. First create a form in ASP.Net with three labels and three textboxes with names as username.password and confirmpassword as shown in the screen below.

Okay. Let me take you through a tour of the server-side code on it. I will use ADO.Net here for the database connectivity. I will add my logic on my button click. Add using statements for the namespaces System.data and System.Data.SqlClient since these are not the default namespaces in .Net.

Add the following code by double-clicking the submit button.

  • Here I have created the connection string in my fashion and you can use your own style in your application as usual.
  • Thats it. We are done. Press Ctrl+F5.
  • If you provide the inserted usename.
  • And if you provide a new username, yuppy, it is inserted.

Best SQL Server 2014 Hosting Recommendation

ASPHostPortal.com provides our customers with Plesk Panel, one of the most popular and stable control panels for Windows hosting, as free. You could also see the latest .NET framework, a crazy amount of functionality as well as Large disk space, bandwidth, MSSQL databases and more. All those give people the convenience to build up a powerful site in SQL Server 2014 . We offers Windows hosting starts from $5/month only. We also guarantees 30 days money back and guarantee 99.9% uptime. If you need a reliable affordable SQL SERVER 2014 Hosting, we should be your best choice.

 



Windows 2008 Hosting with Free ASP.NET :: 9 Steps to Solve Login Failed Error : 18456 in Microsoft SQL Server 2008

clock April 23, 2015 07:31 by author Dan

In this posting, I'll coach you on how you can figure out what user features administrative legal rights within the server along with walk you Step-By-Step how you can configure SQL Server permitting some other end users to sign in as Administrators to SQL Server. This simple information “Login Failed for User (Microsoft SQL Server, Error: 18456)” indicates anyone came into unacceptable recommendations as soon as logging into SQL Server. Inside the beneath display screen chance, We are logging into 'microsoft' SQL Server Managing Facilities which has a user that will not have administrative permissions for connecting to the server.

You must to decide what user is equipped with privileges to SQL server. Normally this can be a user which you logged with having once you put in SQL Server or the user that is the default owner on computer. To obtain a set of user on the unit (Assuming Microsoft windows Server 2012 or above).

Step-By-Step

Launch Server Manager – Start – Click Server Manager



In the upper right corner, click Tools, then Computer Management



Expand Local Users and Groups (Under Computer Management-System Tools) then click Users

Notice under Description… There is a Built-in account for administering… This is very likely an account that has access to SQL server.  Try logging onto windows with that account that is Built-in account for administering then we can grant rights to the user you want to use to login to SQL Server.



Now that you are logged into Windows with an account that has access to connect to SQL Server, Let’s go in and grant rights to the user that you want to be able to use to access SQL Server.

Step-By-Step

Launch SQL Server Management Studio again and you should be able to Connect

Expand your ServerName, then Expand Security, then Logins.  Click Logins

Right-Click Logins Select New Login



Click the Search Button

Type in the Windows User Name you would like to add as an SQL Administrator then click Check Names  (or you can click advanced and select from a list)



Click Server Roles in the left pane; then turn on the sysadmin checkbox, then click OK



You can then confirm you have sa rights by double-clicking the user name in the left Object Explorer and clicking on Securables



Now you can log out of Windows, login as the user you just granted rights to and all should work fine.

Best Windows Server 2008 Hosting Recommendation

ASPHostPortal.com provides our customers with Plesk Panel, one of the most popular and stable control panels for Windows hosting, as free. You could also see the latest .NET framework, a crazy amount of functionality as well as Large disk space, bandwidth, MSSQL databases and more. All those give people the convenience to build up a powerful site in Windows server 2008. We offers Windows hosting starts from $5/month only. We also guarantees 30 days money back and guarantee 99.9% uptime. If you need a reliable affordable Windows server 2008 Hosting, we should be your best choice.



SQL SERVER 2014 Hosting - ASPHostPortal :: Automatic SQL Server Backup Utility using Sqlserver Agent

clock March 23, 2015 08:22 by author Mark

Automatic SQL Server Backup Utility using Sqlserver Agent

It is a sample C# (Visual Studio) application for Automatic Sql server Backup Utility using sqlserveragent. I have used SQL-DMO dll. This article will show you how to create a automatic backup in Sql server.
This code should work on any PC use VB.NET and installed SQL Server(any edition or Client Components for SQL Server.
SQLDMO (Which installed always bt MS SQL Serveror MS SQL Server Client Tools
To do:

  • First enter your SQL Server username and password on corresponding Text Box.
  • Set backup Start date & Backup Time
  • After Finishing this then please check manually it will working or not
  • Manual working procedure:
    • Run Sql sever enterprise Manager
    • Select management Option
    • Open Sql server agent
    • Open Jobs window
    • Check whether job item exist or not
    • Right click on newly created job item then, we will get one
    • Popup menu, then select start job
    • After finish the job then check folder "D:\backup" bkp file created or not

Add reference to SQL-DMO dll

You can do this by right clicking the project in Solution Explorer, then selecting 'Add Reference', COM components and the latest version of "Microsoft SQLDMO Object Library".

Available Server

public void dIsplayServerList(ComboBox cboListName)
{
    try
    {
        SQLDMO.Application oSQLServerDMOApp = new SQLDMO.Application();
        Info.informationLayer info = new Info.informationLayer();           
        SQLDMO.NameList oNameList;
        oNameList = oSQLServerDMOApp.ListAvailableSQLServers();
        for (int intIndex = 0; intIndex <= oNameList.Count - 1; intIndex++)
        {
            if (oNameList.Item(intIndex as object) != null)
            {
                cboListName.Items.Add(oNameList.Item(intIndex).ToString());
            }
        }
        if (cboListName.Items.Count > 0) cboListName.SelectedIndex = 0;
        else cboListName.Text = "(Local)";
        }
    catch
    {
}
}

Available databases

public void dIsplayDatabases(ComboBox cboDatabase,Info.informationLayer info)
{
    try
    {
        SQLDMO._SQLServer SQLServer = new SQLDMO.SQLServerClass();
        cboDatabase.Items.Clear();
        SQLServer.Connect(info.strServerName,info.strLoginName,info.strPwd);
        foreach (SQLDMO.Database db in SQLServer.Databases)
        {
            if (db.Name != null)
                cboDatabase.Items.Add(db.Name);
        }
        cboDatabase.Sorted = true;
        if (cboDatabase.Items.Count == 0)cboDatabase.Text = "<No databases found>";
    }
    catch (Exception err)
    {
       info.ErrorMessageDataLayer = err.Message;
    }
}
Create Job on Server Agent:
public void CreateJob_Sql(Info.informationLayer info)
{
    try
    {
        SQLDMO._SQLServer SQLServer = new SQLDMO.SQLServerClass();
        SQLDMO.Job SQLJob = new SQLDMO.Job();
        SQLDMO.JobSchedule SQLSchedule = new SQLDMO.JobSchedule();
        SQLServer.Connect(info.strServerName, info.strLoginName, info.strPwd);
        switch (SQLServer.JobServer.Status)
        {
            case SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Stopped:
            SQLServer.JobServer.Start();
            SQLServer.JobServer.AutoStart = true;
            break;
        }
        SQLJob.Name = info.strDatabaseName;
        SQLJob.Description = "Check and Backup" + info.strDatabaseName;
        SQLServer.JobServer.Jobs.Add(SQLJob);
        SQLJob.Category = "Database Maintenance";
        SQLDMO.JobStep aJobStep = new SQLDMO.JobStep();
        aJobStep.Name = "Step 2: Backup the Database";
        aJobStep.StepID = 1;
        aJobStep.DatabaseName = info.strDatabaseName;
        aJobStep.SubSystem = "TSQL";
        //------>>> If BackUp Folder is Not Found then create BackUp Folder                
        string   DirectoryName = "D:\\BackUp";
        if (Directory.Exists(DirectoryName)==false)
        {
            System.IO.Directory.CreateDirectory(DirectoryName);
        }
        //------>>>
        string sExt;
        sExt="EXEC master.dbo.xp_sqlmaint '-S " + info.strServerName + " -U " + info.strLoginName + " -P " + info.strPwd + "  -D " + info.strDatabaseName + " -CkDB -CkAl -CkCat -BkUpMedia DISK -BkUpDB D:\\Backup  -BkExt BAK -DelBkUps 2weeks -BkUpOnlyIfClean -Rpt D:\\Backup\\BackDB_Checks.txt'";
        aJobStep.Command = sExt;
        aJobStep.OnSuccessAction = SQLDMO_JOBSTEPACTION_TYPE.SQLDMOJobStepAction_QuitWithSuccess;
        aJobStep.OnFailAction = SQLDMO_JOBSTEPACTION_TYPE.SQLDMOJobStepAction_QuitWithFailure;
        SQLJob.JobSteps.Add(aJobStep);
        SQLJob.ApplyToTargetServer(info.strServerName);
        aJobStep.DoAlter();
        SQLJob.Refresh();
        aJobStep.Refresh();
    }
    catch (Exception Err)
    {
        info.ErrorMessageDataLayer = Err.Message;
    }
}

Create Job shedule on  server Agent:

public void CreateShedule_Sql(Info.informationLayer info)
{
    try
    {
        //it will take bkp every week 2 day
        SQLDMO.Job SQLJob = new SQLDMO.Job();
        SQLDMO._SQLServer SQLServer = new SQLDMO.SQLServerClass();
        SQLDMO.JobSchedule SQLSchedule = new SQLDMO.JobSchedule();
        SQLServer.Connect(info.strServerName, info.strLoginName, info.strPwd);
        SQLJob = SQLServer.JobServer.Jobs.Item(info.strDatabaseName);
        // create a new JobSchedule object
        SQLSchedule.Name = "Weekly Backup";
        SQLSchedule.Schedule.FrequencyType = SQLDMO.SQLDMO_FREQUENCY_TYPE.SQLDMOFreq_Weekly;
        SQLSchedule.Schedule.FrequencyInterval = 2;
        SQLSchedule.Schedule.FrequencyRecurrenceFactor = 2;
        // // start on Mar22, 2015 - at 12.55
        SQLSchedule.Schedule.ActiveStartDate = info.intStartDate;
        SQLSchedule.Schedule.ActiveStartTimeOfDay = info.intStartTime;
        ////  this schedule has no end time or end date
        SQLSchedule.Schedule.ActiveEndDate = 99991231;
        SQLSchedule.Schedule.ActiveEndTimeOfDay = 235959;
        ////  add the schedule to the Job
        SQLJob.BeginAlter();
        SQLJob.JobSchedules.Add(SQLSchedule);
        SQLJob.DoAlter();
        //SQLJob.JobSchedules.Refresh();
        info.ErrorMessageDataLayer = "New Sql Job [Databasename= " + info.strDatabaseName + " ]Sucessfully Created.  ";
    }
    catch (Exception err)
    {
        info.ErrorMessageDataLayer = err.Message;
    }
}

Syntax (SQL Server)

xp_sqlmaint 'switch_string'
[
    [-S server_name[\instance_name]]
    [-U login_ID [-P password]]
    {
        [ -D database_name | -PlanName name | -PlanID guid ]
        [-Rpt text_file]
        [-To operator_name]
        [-HtmlRpt html_file [-DelHtmlRpt <time_period>] ]
        [-RmUnusedSpace threshold_percent free_percent]
        [-CkDB | -CkDBNoIdx]
        [-CkAl | -CkAlNoIdx]
        [-CkCat]
        [-UpdOptiStats sample_percent]
        [-RebldIdx free_space]
        [-WriteHistory]
        [
            {-BkUpDB [backup_path] | -BkUpLog [backup_path] }
            {-BkUpMedia
                {DISK [    [-DelBkUps <time_period>]
                            [-CrBkSubDir ] [ -UseDefDir ]
                         ]
                | TAPE
                }
            }
            [-BkUpOnlyIfClean]
            [-VrfyBackup]
        ]
    }
]
time_period
number[minutes | hours | days | weeks | months]

Syntax (SQL Server)

sqlmaint
[-?] |
[
    [-S server]
    [-U login_ID [-P password]]
    {
        [ -D database_name | -PlanName name | -PlanID guid ]
        [-Rpt text_file [-DelTxtRpt <time_period>] ]
        [-To operator_name]
        [-HtmlRpt html_file [-DelHtmlRpt <time_period>] ]
        [-RmUnusedSpace threshold_percent free_percent]
        [-CkDB | -CkDBNoIdx]
        [-CkAl | -CkAlNoIdx]
        [-CkTxtAl]
        [-CkCat]
        [-UpdSts]
        [-UpdOptiStats sample_percent]
        [-RebldIdx free_space]
        [-WriteHistory]
        [
            {-BkUpDB [backup_path] | -BkUpLog [backup_path] }
            {-BkUpMedia
                {DISK [    [-DelBkUps <time_period>]
                            [-CrBkSubDir ] [ -UseDefDir ]
                         ]
                | TAPE
                }
            }
            [-BkUpOnlyIfClean]
            [-VrfyBackup]
        ]
    }
]

Best SQL 2014 Hosting Recommendation

ASPHostPortal.com

ASPHostPortal.com provides our customers with Plesk Panel, one of the most popular and stable control panels for Windows hosting, as free. You could also see the latest .NET framework, a crazy amount of functionality as well as Large disk space, bandwidth, MSSQL databases and more. All those give people the convenience to build up a powerful site in Windows server. We offers SQL 2014 hosting starts from $5/month only. We also guarantees 30 days money back and guarantee 99.9% uptime. If you need a reliable affordable SQL 2014 Hosting, we should be your best choice.



SQL Server 2014 Hosting with ASPHostPortal.com :: How to Restart an Interrupted SQL Server Database Restore

clock August 23, 2014 09:53 by author Kenny

SQL Server is Microsoft's relational database management system (RDBMS). It is a full-featured databse primarily designed to compete against competitors Oracle Database (DB) and MySQL.

Like all major RBDMS, SQL Server supports ANSI SQL, the standard SQL language. However, SQL Server also contains T-SQL, its own SQL implemention. SQL Server Management Studio (SSMS) (previously known as Enterprise Manager) is SQL Server’s main interface tool, and it supports 32-bit and 64-bit environments.

In this article, we will tell you about how to restart an interrupted SQL Server Database Restore.
Have you ever restored a large database on a Failover Cluster Production Server and while the restore was in progress, due to network failure, the restore failed? Once the SQL Server came up on the other node all the databases came up, except for the database which you were restoring prior to the failover. In this tip we will take a look at the command RESTORE DATABASE...WITH RESTART to see how this command can be helpful during such scenarios.

Here is the solution

The RESTORE DATABASE...WITH RESTART command is a very useful command which is available in SQL Server 2005 and higher versions. A Database Administrator can use this command to finish restoring an interrupted database restore operation.

In the below snippet you can see that ProductDB is in a (Restoring...) state once the SQL Server came online after the unexpected failure.

During such scenarios one can execute the RESTORE DATABASE...WITH RESTART command to successfully complete the database restore operation.

Below are two commands.  The first gets a list of the backups on the file and the second does the actual restore with the restart option.

-- get backup information from backup file
RESTORE FILELISTONLY
FROM DISK ='C:\DBBackups\ProductDB.bak'
GO

-- restore the database
RESTORE DATABASE ProductDB
FROM DISK ='C:\DBBackups\ProductDB.bak'
WITH RESTART
GO

Below you can see that after running the RESTORE DATABASE...WITH RESTART command the database was successfully restored allowing user connectivity.



SQL Server Hosting with ASPHostPortal.com :: How to Backup and Restore Your Database with PowerShell Commands

clock July 10, 2014 09:20 by author Ben

PowerShell is Microsoft’s new command-line shell and scripting language that promises to simplify automation and integration across different Microsoft applications and components. Database professionals can leverage PowerShell by utilizing its numerous built-in cmdlets, or using any of the readily available .NET classes, to automate database tasks, simplify integration, or just discover new ways to accomplish the job at hand.

Windows PowerShell commands can be a valuable addition to your SQL Server management tools. PowerShell is going to replace SQL Server Management Studio (SSMS) anytime soon, it can be used for a wide range of scripted management tasks. PowerShell can run T-SQL commands and also work with objects outside of the SQL Server database. You can use the SQL Server PowerShell Provider to navigate and manage SQL Server database objects, and PowerShell scripts can be run by SQL Agent.

Here is a working Windows PowerShell script to perform a FULL database backup against the Northwind database, storing the backup file in your file system.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "LOCALHOST\SQL2005_1"

#Create a Backup object instance with the Microsoft.SqlServer.Management.Smo.Backup namespace
$dbBackup = new-object ("Microsoft.SqlServer.Management.Smo.Backup")

#Set the Database property to Northwind
$dbBackup.Database = "Northwind"

#Add the backup file to the Devices collection and specify File as the backup type
$dbBackup.Devices.AddDevice("D:\PSScripts\backups\NWind_FULL.bak", "File")

#Specify the Action property to generate a FULL backup
$dbBackup.Action="Database"

#Call the SqlBackup method to generate the backup
$dbBackup.SqlBackup($s)


Since you won't be performing backups of just a single database, it would be better if we loop the entire script in a For-Each cmdlet iterating thru the Databases collection of the Server object.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$s = new-object ("Microsoft.SqlServer.Management.Smo.Server") $instance

$bkdir = "D:\PSScripts\backups" #We define the folder path as a variable
$dbs = $s.Databases
foreach ($db in $dbs)
{
     if($db.Name -ne "tempdb") #We don't want to backup the tempdb database
     {
     $dbname = $db.Name
     $dt = get-date -format yyyyMMddHHmm #We use this to create a file name based on the timestamp
     $dbBackup = new-object ("Microsoft.SqlServer.Management.Smo.Backup")
     $dbBackup.Action = "Database"
     $dbBackup.Database = $dbname
     $dbBackup.Devices.AddDevice($bkdir + "\" + $dbname + "_db_" + $dt + ".bak", "File")
     $dbBackup.SqlBackup($s)
     }
}


There are a lot of different reasons why we need to restore databases, so there are a lot more options with restores than there are with backups. The easiest way to demonstrate a restore is to simply restore a database from a full backup, setting the option to overwrite the existing database.

Restore-SqlDatabase -ServerInstance TESTSQL -Database Northwind`
-BackupFile "E:\Backup\Northwind_db_20130420153024.bak" -ReplaceDatabase


Cheap SQL Hosting with ASPHostPortal.com
Providing the best security, compliance, performance, and managed service separates ASPHostPortal.com from other hosting companies. MS SQL server supports our commitment to providing service options the businesses that choose ASPHostPortal.com demand. Use the Promo Code "DBSQL" (without quotes) and receive double SQL Server Space!



Cheap Windows Hosting Based in USA:: How to Fix SQL Injection Vulnerabilities in ASP.NET

clock June 5, 2014 07:07 by author Ben

Simply stated, SQL injection vulnerabilities are caused by software applications that accept data from an untrusted source (internet users), fail to properly validate and sanitize the data, and subsequently use that data to dynamically construct an SQL query to the database backing that application. For example, imagine a simple application that takes inputs of a username and password. It may ultimately process this input in an SQL statement of the form

string query = "SELECT * FROM users WHERE username = "'" + username + "' AND password = '" + password + "'";

Since this query is constructed by concatenating an input string directly from the user, the query behaves correctly only if password does not contain a single-quote character.

Impact of SQL Injection vulnerabilities

  • Reading, Updating and Deleting arbitrary data from the database
  • Executing commands on the underlying operating system
  • Reading, Updating and Deleting arbitrary tables from the database

There are many way finding SQL Injection Vulnerabilities manually. But, in this article, I will show you how to find SQL Injection Vulnerabilities automatically. It’s no different than finding it manually. The process mainly involves three tasks :

  • Identifying Data Entry.
  • Inject Data to Database.
  • And last, detect anomalies from it’s response

you will see that you can do it automatically to a certain process. Identifying data entry (1st step) is something that can be automated. You can do it by just crawl the website and finding GET and POST request. As well ass Data Injection (2nd step), can also be done in an automatic fashion. The main problem is the 3rd step ( Detect Anomalies Response of Remote Server ). Although this part is easy for human to detect. it sometimes very difficult for a bot or software to detect it and fully understand output of the remote server. For example, when the web application returns the SQL error from database or when the web application returns HTTP 500 code error.

How to Fix SQL Injection Vulnerabilities in ASP.NET

c# code:

string queryText = "SELECT * FROM Students WHERE [email protected]";
SqlCommand cmd = new SqlCommand(queryText, conn);
cmd.Parameters.Add("@City",City);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
return ds;


ASP.NET code:

/*C# code*/
string commandText = "SELECT * FROM Customers WHERE [email protected]";
SqlCommand cmd = new SqlCommand(commandText, conn);
cmd.Parameters.Add("@CountryName",countryName);


Stored Procedure:

var connect = ConfigurationManager.ConnectionStrings["NorthWind"].ToString();
var query = "GetProductByID";
using (var conn = new SqlConnection(connect))
{
  using (var cmd = new SqlCommand(query, conn))
  {
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@ProductID", SqlDbType.Int).Value = Convert.ToInt32(Request["ProductID"]);
    conn.Open();
    //Process results
  }
}


Fixing the SQL Injection Vulnerabilities would not be enough to protect your web application. You need to protect it using Runtime Protection.

Cheap SQL Hosting with ASPHostPortal.com
Providing the best security, compliance, performance, and managed service separates ASPHostPortal.com from other hosting companies. MS SQL server supports our commitment to providing service options the businesses that choose ASPHostPortal.com demand. Use the Promo Code "DBSQL" (without quotes) and receive double SQL Server Space!



mojoPortal Hosting - How to Recover Password Login on Your mojoPortal Site

clock April 29, 2014 07:54 by author Ben

mojoPortal is another open source CMS option based upon the .NET framework. It has a very active developer group and is consistently being updated. While it is free to download and use, there are a number of commercial add-ons that are used to help fund the project. When it comes to developing your own applications, many people prefer mojoPortal because it can act as a starter kit for advanced .NET sites or portals.

mojoPortal is also considered to be very strong as a standalone CMS. It is easy to learn and very simple to use. It includes a variety of different tools such as blogs, photo galleries, chat, newsletters, pools, forums, and much more. It also has a very strong community which makes troubleshooting extremely simple.

Here is the tutorial how to recover password login on your mojoPortal site :

However, in order to use this feature you require login e-mail which you used either at the time of installation or while updating the site later on.

Sometimes, you will forget password but your login e-mail id will be the default provided by the installer – [email protected]

In such a scenario, you need to dig deep into the database to find out the password as mentioned below

  1. Open SQL Server 2008 / 2012
  2. You will view a dialog named – Connect to Server
  3. Provide the database name, database username and password which you provided at the time of mojoportal installation process.
  4. If you installed MojoPortal through Web App Gallery from within WebsitePanel then you need to search for “user.config” file from the file manager and fetch the required credentials.
  5. If the provided credentials are correct then you will be logged into SQL Server. Locate your database from the navigation panel located on the left hand side.
  6. Expand the database name, then Tables and locate the table name – dbo.mp_Users
  7. Right click on the above mentioned table and select the option – Select Top 1000 Rows
  8. SQL Server will display the list of entries under each column. By default, it will only show details of Admin user. However, you will view more entries if your site is bit old and users have registered on the site.
  9. In order to view the password of admin user, you need to scroll horizontally until you see the column name – Pwd.


It is to be noted that MojoPortal displays passwords as such without any kind of encryption. Hence, you should use very strong password for the database, FTP and administrator user account.



Reporting Services Hosting with ASPHostPortal :: Integrating Reporting Services Into a Web Application

clock April 4, 2014 13:12 by author Kenny

With Visual Studio you can be easier to integrate SQL Reporting Services into ASP.NET web applications. Now, i will explain about how to integrate Report Services into a web application using Visual Studio.

First, you must create a new project in Visual Studio, in the ASP.NET code, you will need to delete (or change) this line:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

Next replace any existing code between the <form> tags with the below code:
<table style="vertical-align: bottom; border-width: 0px; margin-top: 0px;
   margin-bottom: 0px; width: 100%; height: 100%; padding: 0px,0px,0px,0px;"
   cellspacing="0" cellpadding="0">
    <tr>
        <td>
            HEADER
        </td>
    </tr>
    <tr>
        <td>
            <div style="border-top: black 1px solid;
                background-color: #ece9d8; border-bottom-width: 1px;
                border-bottom-color: #d4d0c8; padding-bottom: 10px;">
                Custom Controls
            </div>
       </td>
    </tr>
    <tr>
        <td style="height: 100%;">
            <%-- This is where the ReportViewer control will go. --%>
        </td>
    </tr>
</table>


Switch to the “Design” view since it is now time to add the ReportViewer control.
In the “Data” section within the Toolbox, drag the ReportViewer control onto the last row of your table, which should be taking up most of the page. Next you want to set the control’s properties so that it points to the right server and the right report. Under properties, go to the “Server Report” section. Right above the section you should see a property called “ProcessingMode”. In most cases you are going to want use an instance of a Report Server. So for the purposes of this tutorial, set this property to “Remote”, even if the Report Server instance is on the same computer as your web app. Next, you want to set the “ServerReport” settings.

First, type in address of the Report server in the “ReportSeverUrl” field.
The syntax is:
http:// NameOfServer / reportserver
i.e. http://ServerOne/reportserver

Next type in the location of the report in the “ReportPath” field.
i.e. /MyReports/TheReport

The location is easy to know since it is the same path structure that you see in the Report Manager. Also, always be sure the put a forward slash first before the actual path.

Unless you want the ReportViewer to be an absolute size, go ahead and set the height and width settings under the “Layout” section to be 100%. The ReportViewer will by default show controls to set the parameters for the report. These often look a little ugly, and I would recommend most developers to create their own parameter controls. This can be done in the control bar row of the template provided above. To get rid of the ReportViewer’s parameter controls set the “ShowParameterPrompts” in the properties underneath “Appearance” to false. In, the next section I’ll show how you can set the report parameters with your own code.

Those should be all the settings you need to make for the ReportViewer to display correctly, unless your version of Visual Studio has other default settings. Double check to make sure the ASP.NET looks similar to this code:
<rsweb:ReportViewer ID="ReportViewer1" runat="server" Height="100%"
    ProcessingMode="Remote" ShowParameterPrompts="False" Width="100%">
    <ServerReport ReportPath="/MyReports/TheReport" />
</rsweb:ReportViewer>

Now, the report is ready to be processed. You can go ahead and preview your website and the report should generate just fine.



About ASPHostPortal.com

We’re a company that works differently to most. Value is what we output and help our customers achieve, not how much money we put in the bank. It’s not because we are altruistic. It’s based on an even simpler principle. "Do good things, and good things will come to you".

Success for us is something that is continually experienced, not something that is reached. For us it is all about the experience – more than the journey. Life is a continual experience. We see the Internet as being an incredible amplifier to the experience of life for all of us. It can help humanity come together to explode in knowledge exploration and discussion. It is continual enlightenment of new ideas, experiences, and passions


Author Link


Corporate Address (Location)

ASPHostPortal
170 W 56th Street, Suite 121
New York, NY 10019
United States

Sign in