Maintaining SQL data on a remote host.

Hi,
What is the preferred way to maintain SQL tables on a remote host?
I am a newbie to building ASP.NET websites on a remote host.
A stumbling point has been the maintenance of SQL tables on the remote host.
I understand about doing complete backup and restores,
but I am seeking a quicker way to maintain individual files.
I would like to click and edit but instead am going through the following 30+ clicks.
Is there a easier way?
Thanks.

For example, what I do now to build a new data table for a hosted website.
1) Design table
1a) Name
1b) Fields & Types

2) SQL Server Management Studio Express (assuming existing database)
2a) Select Database & Tables
2b) Add new table
2c) Add fields, Key must be INT for ACCESS
2d) Save as (Name_Table)

3) MS Access (requires ODBC to be setup first through the Windows control panel)
3a) Tables / New / Link / ODBC /Machine_Data_Source
3b) Pick table
3c) Edit data, as needed

4) To transfer data, first:
Select the database in the VWD solution explorer,
then right-click and select the new "Publish to Provider"
4a) Database Publishing Wizard
4b) Choose table to script a backup from
4c) Build script & Copy

5) Start Ipswitch FTP ( this step can be rplaced by 6e below)
5a) locate folder & sql script file and choose destination directory
5b) Transfer file

6) Login to remote host host (1and1)
6a) MS SQL Administration
6b) Admin (MyLittleTools Admin)
6c) Tools
6d) Quey Analyser
6e) Paste script (from step 4)
6f) Submit (Run)
6g) Verify table built

FYI: Script to build and populate the new table "Name_Table"
Built by step 4c above, pasted into remote Hosts Query Analyzer by step 6e above.

/****** Object: Table [dbo].[Name_Table] Script Date: 10/28/2007 18:03:58 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Name_Table]') AND type in (N'U'))
DROP TABLE [dbo].[Name_Table]
GO
/****** Object: Table [dbo].[Name_Table] Script Date: 10/28/2007 18:03:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Name_Table]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Name_Table](
[ID] [int] NOT NULL,
[Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Address] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[City] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[State] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Zip] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Acsz] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Phone] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Fax] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Name_Table_1] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS =

ON)
)
END
GO
INSERT [dbo].[Name_Table] ([ID], [Name], [Address], [City], [State], [Zip], [Acsz], [Phone], [Fax]) VALUES (1, NULL, NULL,

NULL, NULL, NULL, NULL, NULL, NULL)
INSERT [dbo].[Name_Table] ([ID], [Name], [Address], [City], [State], [Zip], [Acsz], [Phone], [Fax]) VALUES (2, NULL, NULL,

NULL, NULL, NULL, NULL, NULL, NULL)
INSERT [dbo].[Name_Table] ([ID], [Name], [Address], [City], [State], [Zip], [Acsz], [Phone], [Fax]) VALUES (3, N'Third

name', NULL, NULL, NULL, NULL, NULL, NULL, NULL)

You should take a good look at SSIS (Sql Server Integration Services).

It's quite nice and made for moving data from one database to another.

Another option is to create a database link from one server to another and use t-sql to move the data. Would recommend using SSIS if possible.

Maintain a consistent DB connection through Application lifecycle

Hey everyone,

I'm new to .NET and I've recently inheirited a rather large and busy asp.net website. I was asked to add a testimonials section on each page that will randomly pull a testimonial out of the db. This is fine, however, I'm getting random errors about the DB connection either being closed or connecting. Here is the code for the testimonials class:

1public SqlDataReader GetTestimonials(ref SqlDataReader reader,int iCatID,string sLanguageType)
2 {
3 SqlCommand cmd =new SqlCommand("sp_DVX_Testimonials_Fetch", Connection);
4 cmd.CommandType = CommandType.StoredProcedure;
56 cmd.Parameters.Add(new SqlParameter("@.Cat_ID", iCatID));
7 cmd.Parameters.Add(new SqlParameter("@.LanguageType", sLanguageType));
89 reader = cmd.ExecuteReader();
1011return reader;
12 }

I know this isn't the best way to do this (especially for each page[this site averages about 1000 hits a day]), so I was wondering was--is there a way to maintain a single DB connection that's set up in the Application_Start that will maintain the connection so I don't have to worry about this error. If not, does anyone and any ideas as to what would help?

Thanks in advance!

Well, I don't think you need to do this, as ADO.Net connections are pooled. Usually the best practice would be: open connection -> retrive data -> close it immediately. The underlying managed data provider will help you to decide if "a connection" "really" closes physically..

msdn:

ASqlConnection object represents a unique session to a SQL Server data source

And where dose 'Connection' variable come from in your code? A member of the Page? As SqlConnection is not thread safe, there might be situations when one thread opens it and is ready to read, while another thread just closed it, so error occurs~

|||

The Connection variable is initialized in the constructor of whatever class is using it. Currently, every class initializes its own DB connection. Would this have be bad? :-P

Thanks for the info!

|||

Yes, this would have been bad.

It would be better to have your data access layer maintain the connections. Also, I would tell the datareader to close the connection automatically when the datareader closes, and create/open your connection object from within your DAL.

Although 1000 hits per day is an extremely low traffic site, you really should get in the habit of opening your connection when it's needed, do whatever you need to, then close it as quickly as possible.

|||

Would you happen to know of any resources that show how to maintain connections within the DAL? I'm coming to .Net from a php background and I'm still getting the hang of the structure of everything.

Thanks!

main page blank after new install ?

Hi,
I installed reporting services on 2003 (IIS6, front page ext, asp.net, no
vb.net). When connecting to http://server im directed to
http://server/Reports/Pages/Folder.aspx . The main form header shows HOME /
MY SUBSCRIPTIONS / HELP but the main page is blank.
Anyone know why and how to sort ?
Thanks
Scottim just wondering if its because i didnt install VB.net 03 so the report
designer is not available.|||on the hosting server i enter http://localhost and get the Report Manager
with all the options.
From a client i get a blank home page. I assume because nothing is setup yet
which is fine.