Knowledgebase » Knowledgebase » Database » MSSQL

How to use Webbase Enterprise Manager to manage the MSSQL database?

Mark — Tue, 19 May 2009 14:11:16 GMT

You can read this article if you have difficulties in inputting the database information in the Webbase Enterprise Manager.

To open the Webbase Enterprise Manager, you can browse the link, https://sqladmin.mysite4now.com/, with your web browser, and you will see the following page.
(You can also find the link in Hosting Control Panel -> Database -> MSSQL manager)

To create connection to your database,
1) Click the connection link.

2) Click the "DSN-less" link.

3) type in the database information and click on the Connect button.

4) Great~! You have connected to your database.

Function in Webbase Enterprise Manager

All the function in Webbase Enterprise Manager will be show as an icon. Here is the list of the icon and the function that it represent.

    - Create NEW item
   - Edit item
    - Manage item
    - Rename item
    - Delete item
    - Show Data
    - Show constraints
     - Export data as XML
     - Export data as CSV

To run query,
1) Click on tool icon.

2) Click the "Query Analyser" link. The Query Analyser will be show on the right windows.

3) You can type in your query and click on the submit button to execute.

How to connect to my MSSQL database?

Mark — Mon, 18 May 2009 14:38:24 GMT

You can read this article if you have difficulties in inputting the database information in the software below.

You have few ways to connect to your MSSQL database.
1) Our web-based Enterprise Manager
2) SQL Management Studio
3) Other Similar remote administration software

1) Our web-based Enterprise Manager
Please browse our enterprise manager in this URL: https://sqladmin.mysite4now.com/
The login screen looks like this:

2) SQL Management Studio
You can download the SQL Management Studio here.
The login screen looks like this:

3) Other similar remote administration software
If you use other similar remote administration software, please contact the support of their software team.

How to find my database's server name, IP and login?

Mark — Mon, 18 May 2009 14:36:12 GMT

What information should I input in the Web-based Enterprise Manager and SQL Server Management Studio?
To find these information, please login to your control panel and click "database". You will see a list of your databases. Here is an example:

You can follow the table below to input the information in enterprise manager and SQL Management Studio.

Control Panel	Enterprise Manager	SQL Management Studio
DB Name	Database Name	(not require)
DB IP	SQL Server IP	Server Name
DB ID	User name	Login
(pw does not show in control panel)	Password	Password

Using SQL Server for ASP.Net session state

Mark — Wed, 06 May 2009 16:41:08 GMT

Setup/use SQL Server for ASP.Net session state

This tutorial will go through steps to setup/install the session state service for ASP .Net application with custom database.
1.) Create the MSSQL Database for session state
2.) Tool to install the session state template
3.) Installation the session state service on custom database
4.) Update the web.config file
5.) Create the job to delete expired sessions

1.) Create the MSSQL Database for session state

Create MSSQL database for the session state service.
control panel -> databases -> MSSQL Admin -> Create New DB

This tutorial will go through with follow MSSQL DB info:
Database Server Address: sql999.mysite4now.com
Database name: statedemoDB_62760
Database User ID: statedemoUser_62760
Database Password: statepassword
*You should create/use your database to setup the session state service

2.) Tool to install the session state template
You can install the session state service to custom database with the installation template query:
InstallSqlStateTemplate.sql

By default, InstallSqlStateTemplate.sql located in following folders:
C:\Windows\Microsoft.NET\Framework\version_ID\

For example:
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\InstallSqlStateTemplate.sql

Copy this sql file to another location, and update the template file:
- find and replace all reserved string DatabaseNamePlaceHolder within the template
- replace this string to the database name which install the session state service, for this tutorial - statedemoDB_62760

3.) Installation the session state service on custom database
Connect the database with Microsoft SQL Server Management Studio

Currently, this database is a blank database:

Execute the InstallSqlStateTemplate.sql via the Management Studio

1 - Click "New Query"
2 - Copy and paste the contents of updated InstallSqlStateTemplate.sql as sql query
3 - "Execute" the query
4 - The result of the query execution

Query execution result:

-------------------------------------------------
Starting execution of InstallSqlStateTemplate.SQL
-------------------------------------------------

--------------------------------------------------
Note:
This file is included for backward compatibility
only. You should use aspnet_regsql.exe to install
and uninstall SQL session state.

Run 'aspnet_regsql.exe -?' for details.
--------------------------------------------------
If the job does not exist, an error from msdb.dbo.sp_delete_job is expected.
Msg 229, Level 14, State 5, Procedure sp_delete_job, Line 1
The EXECUTE permission was denied on the object 'sp_delete_job', database 'msdb', schema 'dbo'.
If the category already exists, an error from msdb.dbo.sp_add_category is expected.
Msg 229, Level 14, State 5, Procedure sp_add_category, Line 1
The EXECUTE permission was denied on the object 'sp_add_category', database 'msdb', schema 'dbo'.
Msg 229, Level 14, State 5, Procedure sp_add_job, Line 1
The EXECUTE permission was denied on the object 'sp_add_job', database 'msdb', schema 'dbo'.

--------------------------------------------------
Completed execution of InstallSqlStateTemplate.SQL
--------------------------------------------------

Normally, the core tables/stored procedures have installed on the database after execution
Above error message due to insufficient user privilege to add the scheduled job.
Which is a scheduled job to delete expired session state record of the session state database.

Execution of InstallSqlStateTemplate.sql installed session state related tables/stored procedures:

4.) Update the web.config file

Update the web.config to use the custom session state server/database:
Update/insert sessionState code:

<sessionState
        mode="SQLServer"
        allowCustomSqlDatabase="true"
        sqlConnectionString="data source=sql999.mysite4now.com;initial catalog=statedemoDB_62760;user id=statedemoUser_62760;password=statepassword"
        cookieless="false"
        timeout="20"
/>

** Use mode = SQLServer
** To use custom DB instead of default database "ASPSTATE" , need to enable option allowCustomSqlDatabase="true"
** sessionState : S is uppercase, case sensitive
** sqlConnectionString : C , S are uppercase, case sensitive

Session state record update within the table:

5.) Create the scheduled job to delete expired sessions record in the database

Refer to steps 3.)
To setup the scheduled job for the session state database,
you need to purchase the scheduled Task service via control panel:
control panel -> Site Admin -> Schedule Task

After that submit a ticket to our support team and they will give further assistance to setup the schedule job.

How to Display Reporting Service Reports without Authentication?

Administrator — Mon, 06 Apr 2009 11:03:58 GMT

The simplest way is to run an asp page that will fetch the report and display it.

You can do it with the following example code, just replace the URL with your own reporting url, login and password:

set objhttp = server.createobject("microsoft.xmlhttp")
url = "http://sqlreport01.mysite4now.com/ReportServer/webho_demo/Report1&rs:Command=Render&rs:Format=HTML4.0&rc:Toolbar=True&rc:JavaScript=False&rc:LinkTarget=_top&rc:Area=Report"

objhttp.open "get", url, false, "login", "password"

objhttp.send
response.write objhttp.responsetext

How do I backup my database?

Mark — Mon, 30 Mar 2009 10:11:55 GMT

MSSQL:

1) Login to your hosting control panel. Click on the menu "Database". On the left menu, click on MSSQL.

2) You'll now see a list of your mssql db. Click on the "Backup" link next to the database that you want to backup.

3) Follow the backup steps to setup FTP Account and to Start the backup of your db.

4) FTP to the account that you just setup for your database backup and retrieve the backup.

Please note that we do backup your db on a daily basis as well.

MySQL:

1) Login to your hosting control panel. Click on the menu "Database". On the left menu, click on MySQL.

2) You\'ll now see a list of your mysql db. Click on the "Backup" link next to the database that you want to backup.

3) Follow the backup steps to setup FTP Account and to Start the backup of your db.

4) FTP to the account that you just setup for your database backup and retrieve the backup.

Please note that we do backup your db on a daily basis as well.

To backup your files, please refer to this article.

How can I display sql reporting service reports without login?

Mark — Wed, 24 Dec 2008 11:49:25 GMT

How to display sql reporting service reports without login

The simplest way is to write an asp page with following code:

set objhttp = server.createobject("microsoft.xmlhttp")

url= "http://sqlreport339.mysite4now.com/ReportServer?/chris/sales orderdetail/&rs:Command=Render&rs:Format=HTML4.0&rc:Toolbar=True&rc:JavaScript=False&rc:LinkTarget=_top&rc:Area=Report"

objhttp.open "get", url, false, "login", "password"

objhttp.send
response.write objhttp.responsetext

You must know the report server url,your report path, and report server parameters. You can use theparameter shown in the sample. For detail of the parameters you cansearch msdn.

If the report you designed requiresinteraction between report and browswer or there's embeded item likeimages in your report, this does not work well and user will getpassword prompt.

How do I enable Full Text Indexing for MSSQL?

Mark — Wed, 24 Dec 2008 11:37:09 GMT

You need to execute the following SQL command :

This will enable full-text indexing for the current database:
exec sp_fulltext_database 'enable'

This creates a catalog:
exec sp_fulltext_catalog 'catalogname', 'create'

This enables indexing of a table:
exec sp_fulltext_table 'tablename', 'create',
'catalogname', 'indexname'

This adds a column to an index:
exec sp_fulltext_column 'tablename', 'columnname', 'add'

This activates fulltext on a table:
exec sp_fulltext_table 'tablename', 'activate'

These two enable automatic filling of the full-text index when changes occur to a table:

exec sp_fulltext_table 'tablename',
'start_change_tracking'

exec sp_fulltext_table 'tablename',
'start_background_updateindex'

What are my SQL Reporting Service URLs ?

Mark — Wed, 24 Dec 2008 11:35:50 GMT

You need to purchase and setup SQL reporting service first. You can order it via login Hosting Control Panel -> Database -> Reporting Service.

Report manager:
http://sqlreport01.mysite4now.com/Reports/Pages/Folder.aspx?ItemPath=/<account_id>

Report Server:
http://sqlreport01.mysite4now.com/Reportserver

If you are deploying, you should have the following information in your project properties:
TargetDataSourceFolder: /<account_id>
TargetReportFolder: /<account_id>
TargetServerURL : http://sqlreport01.mysite4now.com/reportserver

Please substitute <account_id> with your hosting account ID.

How to Display Reporting Service Reports with asp.net reportviewer control?

Mark — Wed, 24 Dec 2008 11:34:28 GMT

To use ReportViewer control remote modeto display reports deployed to SQL reporting server, you will need tosign up with SQL Reporting service from your hosting control panel, andhave your reports deployed to the report server.
You may also want to know "How to use SQL Reporting Service web service to generate report in specific file format"

Summary to write your report viewer:

1. Using Visual Studio drag the ReportViewer control to the form
2. create a class that implements the Microsoft.Reporting.WebForms.IReportServerCredentials class.
3. override the NetworkCredentials property of the new class, you can put your login information here
4. initiate an object from the new class and assign to your ReportViewer.ServerReport.ReportServerCredentials property.

Example:

http://webho_demo.mysite4now.com/reportviewer/default.aspx

Source code:

http://webho_demo.mysite4now.com/reportviewer/reportviewer.zip

MSDN reference:

http://msdn2.microsoft.com/en-us/library/microsoft.reporting.webforms.ireportservercredentials(VS.80).aspx

Known issue:

1. If you see asp.net session exception, try to set the reportviewer control Async property to False.

I can't login FTP to retrieve my db backup file.

Mark — Wed, 24 Dec 2008 11:31:28 GMT

Please make sure you have setup your FTPlogin first.

To do so, login Hosting Control Panel -> Database -> MSSQL admin or MySQL admin -> Backup DB

You'll see that option at your DB backup/restore section (Step 1) of yourcontrol panel.

How to migrate SQL Express to SQL2005

Mark — Wed, 24 Dec 2008 11:29:12 GMT

Since March2008, we no longer support SQLExpress engine. If you are not sure ifyour website is using SQLExpress and need to perform migrate action,please check the instructions below.

Please note,if you are NOT using ASP.NET 2.0 or above, and if your ASP.NET websitedoes not have dynamic data, such as product information, membershipinformation, you are most likely not using SQLExpress.

To confirm whether you\'re using sql express database or sql server database, you can follow the following step.

Please checkthe web.config file under your domain/application root folder. Look forkeyword "sqlexpress" within this text file, if you are usingSqlExpress, you should find something similar to:

<addname="ConnectionString" connectionString="DataSource=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\datafilename.MDF;IntegratedSecurity=True;User Instance=True;Initial Catalog=dbname"providerName="System.Data.SqlClient"/>
(you may find thiskeyword, but it's commented out by , in such case thisconnection string does not take effect and you are not affected)

If you donot find the above keyword or a connectionstring specifying SqlExpress,you are not affected and you can ignore this notification.

To attach the SQL express database,

step1: You will need to go to control panel -> database -> mssql admin -> attach new db

step2: Create an FTP account in the SQL server with the FTP login of [accountid_db],

step3: Afterthat, you have to download your SQL express database data file fromyour web server App_data folder via your FTP account in your web serverwith login name of [accountid] Or If you have the SQL express databasein your local computer, you can go directly to step 4

step4: Upload the SQL express database data file to the SQL server using the FTP account you just created in the SQL server

step5: Please attach the database in the SQL server by inputing the database name, database login and database password.

step6: Finally, you have to reconfigure your connection string in your web.config of the file you set your connection string:
change from something like
<addname="ConnectionString" connectionString="DataSource=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\datafilename.MDF;IntegratedSecurity=True;User Instance=True;Initial Catalog=dbname"providerName="System.Data.SqlClient"/>

<addname="ConnectionString"connectionString="Server=SQLServerAddress;InitialCatalog=DBName;uid=DBLogin;pwd=DBPassword;"providerName="System.Data.SqlClient" />

where SQLServerAddress is the new SQL database server address designated when you attache the database.

How do I use Enterprise Manager to connect to my MsSQL DB?

Mark — Wed, 24 Dec 2008 11:27:06 GMT

* Enterprise Manager only work with SQL2000 only. For SQL2005 or above, please check this article.

Follow these instructions:

1. Open your enterprise manager, expand the list on the left hand side panel to reach local.

2. Right-click '(local) (Windows NT)' and select 'New SQL Server Registration'.

3. Enter the server IP hosting your database.

4. Authentication Mode: 'Select The SQL Server login information that was assigned to me...'

5. Connection Options: 'Login automatically...' and enter your ID and password

6.Select a connection group or create a new one. This is to keep thingsorganized; choosing one over the other will not affect your connection.

7. Click on 'Finish' and you are done.

How to get MSSQL DTS working?

Mark — Wed, 24 Dec 2008 11:24:52 GMT

Notice this article is for SQL2000 and Enterprise manager only.

Use Import Export Tool (DTS) - select "Copy objects and data between SQL server".

Onthe next screen, check only: one in the "Create Destination Objects..."- one in the "Copy Data -Replace all Data..." - and one in "Copy allobjects...".

Inthe box "Use Default Options", uncheck it and click the "Options"button. In the following window, clear all of the Security check boxes.

Click OK, Next, Next and Finish.

This way all tables, views and stored procedures are transferred into the SQL Server Database.

Where's the "Edit" icon for me to edit data from Web Enterprise Manager?

Mark — Wed, 24 Dec 2008 11:22:08 GMT

When I open to see my data in a tablewith Web Enterprise Manager (a.k.a. MSSQL Manager), I do not see the"Edit" icon next to the data. Why did the edit icon go?

Answer: In order for the "Edit" icon toshow up when you view data of any table using Web Enterprise Manager,the table needs to have an index. This "index" can be a primary key,or any other type of index. Without index, the edit icon will not showup when you use web EM to view the data of a table.

How to display non-English text with ASP and MSSQL server?

Mark — Wed, 24 Dec 2008 11:13:13 GMT

As an example, one of the technique for you to display Chinese Big5 character:

Make every column type that will store Chinese Big5 text to be Unicode support column (such as nvarchar or nchar).

In your query, put an "N" sign in front of every Chinese Big5 text string.

And in asp page under the <meta> tag, set charset="big5".

Then your asp page should be able to display Chinese Big5 text string.

Note: This is just one of the techniques to display non-English language. There are other methods that you can try as well.

How to use SQL Reporting Service web service to generate report in specific file format?

Mark — Wed, 24 Dec 2008 09:54:45 GMT

To use SQL Reporting Service web serviceto generate report in specific file format, you will need to sign upwith SQL Reporting service from your hosting control panel, and haveyour reports deployed to the report server.
The supported file formats are XML, CSV, IMAGE, PDF, HTML, MHTML, EXCEL.
You may also want to know "How to Display Reporting Service Reports with asp.net 2.0 reportviewer control"

Summary to write your report viewer:

1. from vs.net add web reference to http://sqlreport01.mysite4now.com/reportserver/ReportExecution2005.asmx
2. create an object from the ReportExecutionService class
3. initiate parameters as sample code shows
4. call the LoadReport method and follow by Render method
5. output byte stream returned from Render method to browser.

Example:

http://webho_demo.mysite4now.com/reportviewer2/default.aspx

Source code:

http://webho_demo.mysite4now.com/reportviewer2/reportviewer2.zip

MSDN reference:

http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsexecutionservice2005.reportexecutionservice.render.aspx

Known issue:

1. Try to key your data set small, or it will take a long time to load the report

Need to run aspnet_regsql.exe for my WEB database

Mark — Sun, 30 Nov 2008 12:16:06 GMT

You can run this yourself at home.
Here is the requirement:
1. Your home computer must have .net 2.0 installed
2. Your port 1433 is opened and able to connect target SQL server
3. You can provide correct SQL authentication login/password

Then, gotoC:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\, double clickaspnet_regsql.exe. Follow the wizard and complete the setup.
Thisprogram is to create some tables and stored procedures to allow themembership control feature (for ASP.net 2.0) work. For more detail ofmembership control, consult an .net programmer

Details of aspnet_regsql.exe in MSDN:

http://msdn.microsoft.com/en-us/library/ms229862(VS.80).aspx

How to resolve collation conflict in ASPNETForum

Mark — Sun, 30 Nov 2008 11:50:42 GMT

When ASPNETForum returns the error as similar as follow:

Cannot resolve the collation conflictbetween "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" inthe equal to operation.

You could mostly resolve it by trying to:

1) change database collation to SQL_Latin1_General_CP1_CI_AS via the control panel
2) connect the database, find stored procedure "Statistics_ResetTopPosters"
3) find and change the code from:

CREATE Table #Top500Users (
Rank int IDENTITY (1, 1) NOT NULL,
UserName nvarchar(50)
)

to:

CREATE Table #Top500Users (
Rank int IDENTITY (1, 1) NOT NULL,
UserName nvarchar(50) collate database_default
)

reference link:

http://forums.asp.net/535822/ShowPost.aspx