Lowering priority: building

Showing posts with label building. Show all posts

Monday, March 26, 2012

maintenance plan

Im building a new sql server and would like to use my existing maintenance
plans. Where are the files stored and how do I use them on the new server?
Thanks,
HowardHi,
Maintenece plans will be stored in MSDB database.So if you restore the MSDB
database; then you should be able to
get all Maintenance plans and jobs.
If the SQL Server name is different from old server then you may need to
change the Originating server name in Sysjobs table.
Thanks
Hari
SQL Server MVP
"Howard" <howdy0909@.yahoo.com> wrote in message
news:uv9r$rHxGHA.3904@.TK2MSFTNGP02.phx.gbl...
> Im building a new sql server and would like to use my existing maintenance
> plans. Where are the files stored and how do I use them on the new server?
> Thanks,
> Howard
> shou

Friday, March 23, 2012

maintenance plan

Monday, March 19, 2012

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.

Saturday, February 25, 2012

Madness?!? This is SQL!

I'm charged with building a web service that accepts data from
multiple locations and stores it to be queried by users. The data we
receive will vary from business unit to business unit and we intend to
add more units as the project progresses. Each unit's system we add
will return data in a different format, and I really don't want to try
and replicate all of their databases.
Someone tell me whether the way I have this planned is a good way of
doing it, or if it's pure madness.
Lets say I get data from 2 different business units as follows:
Unit 1 Data:
<employees>
<employee id="1">
<name>Doe, Jane</name>
<phone>999-999-9999</phone>
</employee>
<employee>
<name>Doe, John</name>
<phone>888-888-8888</phone>
</employee>
</employees>
Unit 2 Data:
<systems>
<system id="123" name="wrk001">
<ip>1.1.1.1</ip>
<os>WindowsXP</os>
<location>D105</location>
</system>
<system id="234" name="wrk002">
<ip>1.1.1.2</ip>
<os>WindowsXP</os>
<location>D106</location>
</system>
</systems>
When a user needs to look up data from unit 1, they'll need to be able
to supply the employee's name and or id, while users searching unit 2
will need to know an ip address and or location name.
First, lets say I have a System Table:
TBL_System
---
| System_ID | System_Name
+--+--
| 1 | Business Unit 1
+--+--
| 2 | Business Unit 2
+--+--
Then, I create a table that explains what keys users will be searching
for. These keys will be based off of the XML document structure.
---
| System_ID | System_Key | Key_Name
+--+--+--
| 1 | 1 | id
+--+--+--
| 1 | 2 | name
+--+--+--
| 2 | 1 | location
+--+--+--
| 2 | 2 | ip
+--+--+--
Then, lets say I create a Table called TBL_DataStore to hold this
info.
TBL_DataStore
---
| DS_ID | DS_Data
+--+--
| 1 | <employee id="z1">
| | <name>Doe, Jane</name>
| | <phone>999-999-9999</phone>
| | </employee>
+--+--
| 2 | <employee id="z2">
| | <name>Doe, John</name>
| | <phone>888-888-8888</phone>
| | </employee>
+--+--
| 3 | <system id="123" name="wrk001">
| | <ip>1.1.1.1</ip>
| | <os>WindowsXP</os>
| | <location>D105</location>
| | </system>
+--+--
| 4 | <system id="234" name="wrk002">
| | <ip>1.1.1.2</ip>
| | <os>WindowsXP</os>
| | <location>D106</location>
| | </system>
+--+--
Finally, I create a Lookup table that holds key values that the user
will be searching for
TBL_Lookup
---
| System_ID | System_Key | DS_ID | Key_Value
+--+--+--+--
| 1 | 1 | 1 | z1
+--+--+--+--
| 1 | 2 | 1 | Doe, Jane
+--+--+--+--
| 1 | 1 | 2 | z2
+--+--+--+--
| 1 | 2 | 2 | Doe, John
+--+--+--+--
| 2 | 1 | 3 | D105
+--+--+--+--
| 2 | 2 | 3 | 1.1.1.1
+--+--+--+--
| 2 | 1 | 4 | D106
+--+--+--+--
| 2 | 2 | 4 | 1.1.1.2
+--+--+--+--
Now, based on this structure, I can load data from any business unit
without having to change the data structure to add more businesses.
Further, In order to query the data, I don't have to know the specific
xPath of a piece of information.
Is this a good way to accomplish what I'm trying to achieve, or should
I figure out some way to use OpenXML() queries based on XML supplied
by the different business units. My concern about OpenXML() is it's
speed and flexability.Hello Kris,
What volumes are you dealing with?
My view is the effort in creating a table far out ways the effort to support
a system like the one you are suggesting.
One simple view is to use full text, which allows full text to filter down
in a rough manner and then filter further using normal search predicates.
i.e. system and/or an xpath query
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> I'm charged with building a web service that accepts data from
> multiple locations and stores it to be queried by users. The data we
> receive will vary from business unit to business unit and we intend to
> add more units as the project progresses. Each unit's system we add
> will return data in a different format, and I really don't want to try
> and replicate all of their databases.
> Someone tell me whether the way I have this planned is a good way of
> doing it, or if it's pure madness.
> Lets say I get data from 2 different business units as follows:
> Unit 1 Data:
> <employees>
> <employee id="1">
> <name>Doe, Jane</name>
> <phone>999-999-9999</phone>
> </employee>
> <employee>
> <name>Doe, John</name>
> <phone>888-888-8888</phone>
> </employee>
> </employees>
> Unit 2 Data:
> <systems>
> <system id="123" name="wrk001">
> <ip>1.1.1.1</ip>
> <os>WindowsXP</os>
> <location>D105</location>
> </system>
> <system id="234" name="wrk002">
> <ip>1.1.1.2</ip>
> <os>WindowsXP</os>
> <location>D106</location>
> </system>
> </systems>
> When a user needs to look up data from unit 1, they'll need to be able
> to supply the employee's name and or id, while users searching unit 2
> will need to know an ip address and or location name.
> First, lets say I have a System Table:
> TBL_System
> ---
> | System_ID | System_Name
> +--+--
> | 1 | Business Unit 1
> +--+--
> | 2 | Business Unit 2
> +--+--
> Then, I create a table that explains what keys users will be searching
> for. These keys will be based off of the XML document structure.
> ---
> | System_ID | System_Key | Key_Name
> +--+--+--
> | 1 | 1 | id
> +--+--+--
> | 1 | 2 | name
> +--+--+--
> | 2 | 1 | location
> +--+--+--
> | 2 | 2 | ip
> +--+--+--
> Then, lets say I create a Table called TBL_DataStore to hold this
> info.
> TBL_DataStore
> ---
> | DS_ID | DS_Data
> +--+--
> | 1 | <employee id="z1">
> | | <name>Doe, Jane</name>
> | | <phone>999-999-9999</phone>
> | | </employee>
> +--+--
> | 2 | <employee id="z2">
> | | <name>Doe, John</name>
> | | <phone>888-888-8888</phone>
> | | </employee>
> +--+--
> | 3 | <system id="123" name="wrk001">
> | | <ip>1.1.1.1</ip>
> | | <os>WindowsXP</os>
> | | <location>D105</location>
> | | </system>
> +--+--
> | 4 | <system id="234" name="wrk002">
> | | <ip>1.1.1.2</ip>
> | | <os>WindowsXP</os>
> | | <location>D106</location>
> | | </system>
> +--+--
> Finally, I create a Lookup table that holds key values that the user
> will be searching for
> TBL_Lookup
> ---
> | System_ID | System_Key | DS_ID | Key_Value
> +--+--+--+--
> | 1 | 1 | 1 | z1
> +--+--+--+--
> | 1 | 2 | 1 | Doe, Jane
> +--+--+--+--
> | 1 | 1 | 2 | z2
> +--+--+--+--
> | 1 | 2 | 2 | Doe, John
> +--+--+--+--
> | 2 | 1 | 3 | D105
> +--+--+--+--
> | 2 | 2 | 3 | 1.1.1.1
> +--+--+--+--
> | 2 | 1 | 4 | D106
> +--+--+--+--
> | 2 | 2 | 4 | 1.1.1.2
> +--+--+--+--
> Now, based on this structure, I can load data from any business unit
> without having to change the data structure to add more businesses.
> Further, In order to query the data, I don't have to know the specific
> xPath of a piece of information.
> Is this a good way to accomplish what I'm trying to achieve, or should
> I figure out some way to use OpenXML() queries based on XML supplied
> by the different business units. My concern about OpenXML() is it's
> speed and flexability.
>

Madness?!? This is SQL!

I'm charged with building a web service that accepts data from
multiple locations and stores it to be queried by users. The data we
receive will vary from business unit to business unit and we intend to
add more units as the project progresses. Each unit's system we add
will return data in a different format, and I really don't want to try
and replicate all of their databases.
Someone tell me whether the way I have this planned is a good way of
doing it, or if it's pure madness.
Lets say I get data from 2 different business units as follows:
Unit 1 Data:
<employees>
<employee id="1">
<name>Doe, Jane</name>
<phone>999-999-9999</phone>
</employee>
<employee>
<name>Doe, John</name>
<phone>888-888-8888</phone>
</employee>
</employees>
Unit 2 Data:
<systems>
<system id="123" name="wrk001">
<ip>1.1.1.1</ip>
<os>WindowsXP</os>
<location>D105</location>
</system>
<system id="234" name="wrk002">
<ip>1.1.1.2</ip>
<os>WindowsXP</os>
<location>D106</location>
</system>
</systems>
When a user needs to look up data from unit 1, they'll need to be able
to supply the employee's name and or id, while users searching unit 2
will need to know an ip address and or location name.
First, lets say I have a System Table:
TBL_System
| System_ID | System_Name
+--+--
| 1 | Business Unit 1
+--+--
| 2 | Business Unit 2
+--+--
Then, I create a table that explains what keys users will be searching
for. These keys will be based off of the XML document structure.
| System_ID | System_Key | Key_Name
+--+--+--
| 1 | 1 | id
+--+--+--
| 1 | 2 | name
+--+--+--
| 2 | 1 | location
+--+--+--
| 2 | 2 | ip
+--+--+--
Then, lets say I create a Table called TBL_DataStore to hold this
info.
TBL_DataStore
| DS_ID | DS_Data
+--+--
| 1 | <employee id="z1">
| | <name>Doe, Jane</name>
| |<phone>999-999-9999</phone>
|| </employee>
+--+--
| 2 | <employee id="z2">
| | <name>Doe, John</name>
| | <phone>888-888-8888</phone>
| | </employee>
+--+--
| 3 | <system id="123" name="wrk001">
| | <ip>1.1.1.1</ip>
| | <os>WindowsXP</os>
| | <location>D105</location>
| | </system>
+--+--
| 4 | <system id="234" name="wrk002">
| | <ip>1.1.1.2</ip>
| | <os>WindowsXP</os>
| | <location>D106</location>
| | </system>
+--+--
Finally, I create a Lookup table that holds key values that the user
will be searching for
TBL_Lookup
| System_ID | System_Key | DS_ID | Key_Value
+--+--+--+--
| 1 | 1 | 1 | z1
+--+--+--+--
| 1 | 2 | 1 | Doe, Jane
+--+--+--+--
| 1 | 1 | 2 | z2
+--+--+--+--
| 1 | 2 | 2 | Doe, John
+--+--+--+--
| 2 | 1 | 3 | D105
+--+--+--+--
| 2 | 2 | 3 | 1.1.1.1
+--+--+--+--
| 2 | 1 | 4 | D106
+--+--+--+--
| 2 | 2 | 4 | 1.1.1.2
+--+--+--+--
Now, based on this structure, I can load data from any business unit
without having to change the data structure to add more businesses.
Further, In order to query the data, I don't have to know the specific
xPath of a piece of information.
Is this a good way to accomplish what I'm trying to achieve, or should
I figure out some way to use OpenXML() queries based on XML supplied
by the different business units. My concern about OpenXML() is it's
speed and flexability.
Hello Kris,
What volumes are you dealing with?
My view is the effort in creating a table far out ways the effort to support
a system like the one you are suggesting.
One simple view is to use full text, which allows full text to filter down
in a rough manner and then filter further using normal search predicates.
i.e. system and/or an xpath query
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> I'm charged with building a web service that accepts data from
> multiple locations and stores it to be queried by users. The data we
> receive will vary from business unit to business unit and we intend to
> add more units as the project progresses. Each unit's system we add
> will return data in a different format, and I really don't want to try
> and replicate all of their databases.
> Someone tell me whether the way I have this planned is a good way of
> doing it, or if it's pure madness.
> Lets say I get data from 2 different business units as follows:
> Unit 1 Data:
> <employees>
> <employee id="1">
> <name>Doe, Jane</name>
> <phone>999-999-9999</phone>
> </employee>
> <employee>
> <name>Doe, John</name>
> <phone>888-888-8888</phone>
> </employee>
> </employees>
> Unit 2 Data:
> <systems>
> <system id="123" name="wrk001">
> <ip>1.1.1.1</ip>
> <os>WindowsXP</os>
> <location>D105</location>
> </system>
> <system id="234" name="wrk002">
> <ip>1.1.1.2</ip>
> <os>WindowsXP</os>
> <location>D106</location>
> </system>
> </systems>
> When a user needs to look up data from unit 1, they'll need to be able
> to supply the employee's name and or id, while users searching unit 2
> will need to know an ip address and or location name.
> First, lets say I have a System Table:
> TBL_System
> | System_ID | System_Name
> +--+--
> | 1 | Business Unit 1
> +--+--
> | 2 | Business Unit 2
> +--+--
> Then, I create a table that explains what keys users will be searching
> for. These keys will be based off of the XML document structure.
> | System_ID | System_Key | Key_Name
> +--+--+--
> | 1 | 1 | id
> +--+--+--
> | 1 | 2 | name
> +--+--+--
> | 2 | 1 | location
> +--+--+--
> | 2 | 2 | ip
> +--+--+--
> Then, lets say I create a Table called TBL_DataStore to hold this
> info.
> TBL_DataStore
> | DS_ID | DS_Data
> +--+--
> | 1 | <employee id="z1">
> | | <name>Doe, Jane</name>
> | |<phone>999-999-9999</phone>
> || </employee>
> +--+--
> | 2 | <employee id="z2">
> | | <name>Doe, John</name>
> | | <phone>888-888-8888</phone>
> | | </employee>
> +--+--
> | 3 | <system id="123" name="wrk001">
> | | <ip>1.1.1.1</ip>
> | | <os>WindowsXP</os>
> | | <location>D105</location>
> | | </system>
> +--+--
> | 4 | <system id="234" name="wrk002">
> | | <ip>1.1.1.2</ip>
> | | <os>WindowsXP</os>
> | | <location>D106</location>
> | | </system>
> +--+--
> Finally, I create a Lookup table that holds key values that the user
> will be searching for
> TBL_Lookup
> | System_ID | System_Key | DS_ID | Key_Value
> +--+--+--+--
> | 1 | 1 | 1 | z1
> +--+--+--+--
> | 1 | 2 | 1 | Doe, Jane
> +--+--+--+--
> | 1 | 1 | 2 | z2
> +--+--+--+--
> | 1 | 2 | 2 | Doe, John
> +--+--+--+--
> | 2 | 1 | 3 | D105
> +--+--+--+--
> | 2 | 2 | 3 | 1.1.1.1
> +--+--+--+--
> | 2 | 1 | 4 | D106
> +--+--+--+--
> | 2 | 2 | 4 | 1.1.1.2
> +--+--+--+--
> Now, based on this structure, I can load data from any business unit
> without having to change the data structure to add more businesses.
> Further, In order to query the data, I don't have to know the specific
> xPath of a piece of information.
> Is this a good way to accomplish what I'm trying to achieve, or should
> I figure out some way to use OpenXML() queries based on XML supplied
> by the different business units. My concern about OpenXML() is it's
> speed and flexability.
>

Monday, March 26, 2012

maintenance plan

Friday, March 23, 2012

maintenance plan

Monday, March 19, 2012

Maintaining SQL data on a remote host.

Saturday, February 25, 2012

Madness?!? This is SQL!

Madness?!? This is SQL!

Lowering priority

Blog Archive

About Me