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.
