Any SQL wizard can help? Reformat the input file and transfer into SQL server

I am trying to transfer 200 txt files into SQL server by using query analyzer.
The command is 'Bulk insert [tableName] from 'path\filename.txt'
However, I need to read and modifiy the txt file.
I am new to SQL server but I believe there must be some one who is a wizard can do what I want easily.

Thank you for the help in advance!

Here is the raw data layout, which is comma delimited.
BDate 1/1/1990 BDate 1/1/1990 BDate 1/1/1990 BDate 1/1/1990
Edate 1/1/2005 Edate 1/1/2005 Edate 1/1/2005 Edate 1/1/2005
Fq D Fq D Fq D Fq D
Date R P M E D Date R P M E D Date R P M E D Date R P M E D
1/1/90 1 2 3 4 5 1/1/90 2 3 4 5 6 1/1/90 3 4 5 6 7 1/1/90 4 5 6 7 8
2 3 4 5 6 1 2 3 4 5 3 4 5 6 7 6 7 8 9 1
1/1/05 ..... 1/1/05 ... 1/1/05 .... 1/1/05 ....

This is the desired output after load into the table, which is tacking each repeating block on top of each other.
Date R P M E D
1/1/90 1 2 3 4 5
2 3 4 5 6
1/1/05 .....
1/1/90 2 3 4 5 6
2 3 4 5 6
1/1/05 .....
1/1/90 3 4 5 6 7
3 4 5 6 7
1/1/05 .....
1/1/90 4 5 6 7 8
6 7 8 9 1
1/1/05 ....."I am trying to transfer 200 txt files into SQL server by using query analyzer."
--DTS might be more appropriate.

"I am new to SQL server but I believe there must be some one who is a wizard can do what I want easily."
--Faith is a powerful thing.

"Here is the raw data layout, which is comma delimited."
--What you posted is not comma delimited.

"This is the desired output after load into the table, which is tacking each repeating block on top of each other."
--You are going to need to load this data into a staging table and normalize it before loading into your production tables. The process will be complex, involving several passes through the data.

If at all possible, try to get your source data in a better format. Practically any other format would be preferable to what you posted.|||Blindman,
Thank you for your reply.
You are right... I forgot to put "," in my sample file layout.
I am using another source provider to request time series in excel. This is the most efficient way I can utilize excel ability (256 columns and over 65,000 rows). That's why the raw data layout looks wired. However, I have to stick to it.

I was thinking to load these files into a table to normalize but I am not sure if I know SQL well enough to say this is the best solution. I think I got the answer from you.

What is staging db. I assume it is one of defualt DB in in enterprise manager, however, I did not see it. Or this is the name you gave?

Thank you again for the help.
Shiparsons|||Not "Staging DB". "Staging TABLE."

A staging table is basically an table that has the same structure as your input data, with additional columns added as needed to keep track of records as they are being processed. I always add an "Imported" column that defaults to getdate(), and an ImportErrors column that I populate as necessary during processing.

Your staging table should have no Primary Keys or constraints (unless you add a surrogate PKey for processing...), so that your import process never fails because the data does not match what is expected.

Once the data is in the staging table you cleans it and make sure it satisfies all the business rules required by your production tables. Then you make as many passes through the staging table as necessary to update the various production tables it feeds, starting with the top-level tables.|||Thank you for the explanation.
What datatype I should use when I create my staging table? I assume this is nonconstraints type since my raw data contains text, datetime, and float.

Thank you,
Qing|||You should try to match the datatype to the type of the data being entered, though some people just make all staging table columns varchar by default. I don't do this, as a rule, but you may have no other choice since your import file is actually a mix of different layouts. String fields are the only column types that will accept any input type.|||Blindman,
Thank you for the help.

I will try.

shiparsons

Any SQL Server Guru?

Dear SQL Server Gurus:
Your input and advises are greatly appreciated!
We have a SQL Server 2005 database served for Decision Support and Reporting
purposes and it is about 15 G and growing moderately. The source data is
from DB2, an on line transactional database. Everyday, data (existing or
modified tables with data) was transformed into a SQL server staging database
and got backup on that server. Then the backup was ftp to our produciton
server and the restore process was kick off to restore the database. During
that restore process, our production database got refreshed, however, all
views/functions/store procedures created and edited by the developer will be
gone too. To resolve this problem, everyday our developer before going home,
she would generate all scripts (including views/functions/procedures) thru
Server Management studio and run it thru command line from a job scheduler
right after the restore process was done. We have over 300 views, functions
and store procedures. When the developer generates the scripts, she needs
to manually reset value on the Server Management Studio and it is pretty
risky, for if she did not reset value right (for instance, set dependency to
true), she will not get the correct scripts and the restore process will wipe
out the scripts and the reporting programs would fail. I am looking for a
way to streamline the developer's working procedures.
Is there any way to keep the functions/views/procedure stored on the
database when the tables and data got refreshed? or do you have any other
recommendation?
Thanks,
Jenny
1) Create a separate DB for the functions and procs.
2) Use 3-part naming for the objects they access.
3) Create a separate DB with data only. Refresh just that DB. The
objects referred to in #2 are in this DB.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"Jenny" <Jenny@.discussions.microsoft.com> wrote in message
news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
Dear SQL Server Gurus:
Your input and advises are greatly appreciated!
We have a SQL Server 2005 database served for Decision Support and Reporting
purposes and it is about 15 G and growing moderately. The source data is
from DB2, an on line transactional database. Everyday, data (existing or
modified tables with data) was transformed into a SQL server staging
database
and got backup on that server. Then the backup was ftp to our produciton
server and the restore process was kick off to restore the database. During
that restore process, our production database got refreshed, however, all
views/functions/store procedures created and edited by the developer will be
gone too. To resolve this problem, everyday our developer before going
home,
she would generate all scripts (including views/functions/procedures) thru
Server Management studio and run it thru command line from a job scheduler
right after the restore process was done. We have over 300 views, functions
and store procedures. When the developer generates the scripts, she needs
to manually reset value on the Server Management Studio and it is pretty
risky, for if she did not reset value right (for instance, set dependency to
true), she will not get the correct scripts and the restore process will
wipe
out the scripts and the reporting programs would fail. I am looking for a
way to streamline the developer's working procedures.
Is there any way to keep the functions/views/procedure stored on the
database when the tables and data got refreshed? or do you have any other
recommendation?
Thanks,
Jenny
|||Sounds like best option is to create another database for all of the
reporting stored procedures/functions/views. It will take a little bit of
work to go through all of the objects and change the object name to indicate
the database where the data is located. (Check Books Online for "External
Data and Transact-SQL".)
But the end result is that you 'refresh' process would not 'wipe out' the
objects.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Jenny" <Jenny@.discussions.microsoft.com> wrote in message
news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
> Dear SQL Server Gurus:
> Your input and advises are greatly appreciated!
> We have a SQL Server 2005 database served for Decision Support and
> Reporting
> purposes and it is about 15 G and growing moderately. The source data is
> from DB2, an on line transactional database. Everyday, data (existing or
> modified tables with data) was transformed into a SQL server staging
> database
> and got backup on that server. Then the backup was ftp to our produciton
> server and the restore process was kick off to restore the database.
> During
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will
> be
> gone too. To resolve this problem, everyday our developer before going
> home,
> she would generate all scripts (including views/functions/procedures) thru
> Server Management studio and run it thru command line from a job scheduler
> right after the restore process was done. We have over 300 views,
> functions
> and store procedures. When the developer generates the scripts, she
> needs
> to manually reset value on the Server Management Studio and it is pretty
> risky, for if she did not reset value right (for instance, set dependency
> to
> true), she will not get the correct scripts and the restore process will
> wipe
> out the scripts and the reporting programs would fail. I am looking for a
> way to streamline the developer's working procedures.
> Is there any way to keep the functions/views/procedure stored on the
> database when the tables and data got refreshed? or do you have any other
> recommendation?
>
> Thanks,
>
> Jenny
>
|||> During
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will
> be
> gone too.
Rather that generate scripts, consider keeping the 'master' scripts under
source control (or at least in the file system). You can then run the
scripts as part of a post-restore process. This is much less risky than
using the database as the source and generating scripts. Keeping scripts
under source control is a Best Practice in SQL Server development.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jenny" <Jenny@.discussions.microsoft.com> wrote in message
news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
> Dear SQL Server Gurus:
> Your input and advises are greatly appreciated!
> We have a SQL Server 2005 database served for Decision Support and
> Reporting
> purposes and it is about 15 G and growing moderately. The source data is
> from DB2, an on line transactional database. Everyday, data (existing or
> modified tables with data) was transformed into a SQL server staging
> database
> and got backup on that server. Then the backup was ftp to our produciton
> server and the restore process was kick off to restore the database.
> During
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will
> be
> gone too. To resolve this problem, everyday our developer before going
> home,
> she would generate all scripts (including views/functions/procedures) thru
> Server Management studio and run it thru command line from a job scheduler
> right after the restore process was done. We have over 300 views,
> functions
> and store procedures. When the developer generates the scripts, she
> needs
> to manually reset value on the Server Management Studio and it is pretty
> risky, for if she did not reset value right (for instance, set dependency
> to
> true), she will not get the correct scripts and the restore process will
> wipe
> out the scripts and the reporting programs would fail. I am looking for a
> way to streamline the developer's working procedures.
> Is there any way to keep the functions/views/procedure stored on the
> database when the tables and data got refreshed? or do you have any other
> recommendation?
>
> Thanks,
>
> Jenny
>
|||Hi Tom,
What do you mean in item 2 and 3?
I created a backup db and truncate data, which would fullfilled the first
comments you mentioned. Then what is next? I don't quite understand what
you said on item 2 & 3.
Thanks,
"Tom Moreau" wrote:

> 1) Create a separate DB for the functions and procs.
> 2) Use 3-part naming for the objects they access.
> 3) Create a separate DB with data only. Refresh just that DB. The
> objects referred to in #2 are in this DB.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Jenny" <Jenny@.discussions.microsoft.com> wrote in message
> news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
> Dear SQL Server Gurus:
> Your input and advises are greatly appreciated!
> We have a SQL Server 2005 database served for Decision Support and Reporting
> purposes and it is about 15 G and growing moderately. The source data is
> from DB2, an on line transactional database. Everyday, data (existing or
> modified tables with data) was transformed into a SQL server staging
> database
> and got backup on that server. Then the backup was ftp to our produciton
> server and the restore process was kick off to restore the database. During
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will be
> gone too. To resolve this problem, everyday our developer before going
> home,
> she would generate all scripts (including views/functions/procedures) thru
> Server Management studio and run it thru command line from a job scheduler
> right after the restore process was done. We have over 300 views, functions
> and store procedures. When the developer generates the scripts, she needs
> to manually reset value on the Server Management Studio and it is pretty
> risky, for if she did not reset value right (for instance, set dependency to
> true), she will not get the correct scripts and the restore process will
> wipe
> out the scripts and the reporting programs would fail. I am looking for a
> way to streamline the developer's working procedures.
> Is there any way to keep the functions/views/procedure stored on the
> database when the tables and data got refreshed? or do you have any other
> recommendation?
>
> Thanks,
>
> Jenny
>
|||3-part naming of objects is of the form:
SQL 2000: Database.Owner.ObjectName
SQL 2005: Database.Schema.ObjectName
Thus, you would refer to a table in the MyDB database as:
MyDB.dbo.MyTable
You would have 2 DB's, say, DBFunc and DBData. Place your stored procs and
functions in DBFunc. Place your data in DBData. In the functions/procs,
refer to your tables in the form shown above.
HTH
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"Jenny" <Jenny@.discussions.microsoft.com> wrote in message
news:3F2249E6-86F6-454A-9DCA-1C4F194348D4@.microsoft.com...
Hi Tom,
What do you mean in item 2 and 3?
I created a backup db and truncate data, which would fullfilled the first
comments you mentioned. Then what is next? I don't quite understand what
you said on item 2 & 3.
Thanks,
"Tom Moreau" wrote:

> 1) Create a separate DB for the functions and procs.
> 2) Use 3-part naming for the objects they access.
> 3) Create a separate DB with data only. Refresh just that DB. The
> objects referred to in #2 are in this DB.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Jenny" <Jenny@.discussions.microsoft.com> wrote in message
> news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
> Dear SQL Server Gurus:
> Your input and advises are greatly appreciated!
> We have a SQL Server 2005 database served for Decision Support and
> Reporting
> purposes and it is about 15 G and growing moderately. The source data is
> from DB2, an on line transactional database. Everyday, data (existing or
> modified tables with data) was transformed into a SQL server staging
> database
> and got backup on that server. Then the backup was ftp to our produciton
> server and the restore process was kick off to restore the database.
> During
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will
> be
> gone too. To resolve this problem, everyday our developer before going
> home,
> she would generate all scripts (including views/functions/procedures) thru
> Server Management studio and run it thru command line from a job scheduler
> right after the restore process was done. We have over 300 views,
> functions
> and store procedures. When the developer generates the scripts, she
> needs
> to manually reset value on the Server Management Studio and it is pretty
> risky, for if she did not reset value right (for instance, set dependency
> to
> true), she will not get the correct scripts and the restore process will
> wipe
> out the scripts and the reporting programs would fail. I am looking for a
> way to streamline the developer's working procedures.
> Is there any way to keep the functions/views/procedure stored on the
> database when the tables and data got refreshed? or do you have any other
> recommendation?
>
> Thanks,
>
> Jenny
>

Any SQL Server Guru?

Dear SQL Server Gurus:
Your input and advises are greatly appreciated!
We have a SQL Server 2005 database served for Decision Support and Reporting
purposes and it is about 15 G and growing moderately. The source data is
from DB2, an on line transactional database. Everyday, data (existing or
modified tables with data) was transformed into a SQL server staging database
and got backup on that server. Then the backup was ftp to our produciton
server and the restore process was kick off to restore the database. During
that restore process, our production database got refreshed, however, all
views/functions/store procedures created and edited by the developer will be
gone too. To resolve this problem, everyday our developer before going home,
she would generate all scripts (including views/functions/procedures) thru
Server Management studio and run it thru command line from a job scheduler
right after the restore process was done. We have over 300 views, functions
and store procedures. When the developer generates the scripts, she needs
to manually reset value on the Server Management Studio and it is pretty
risky, for if she did not reset value right (for instance, set dependency to
true), she will not get the correct scripts and the restore process will wipe
out the scripts and the reporting programs would fail. I am looking for a
way to streamline the developer's working procedures.
Is there any way to keep the functions/views/procedure stored on the
database when the tables and data got refreshed? or do you have any other
recommendation?
Thanks,
Jenny1) Create a separate DB for the functions and procs.
2) Use 3-part naming for the objects they access.
3) Create a separate DB with data only. Refresh just that DB. The
objects referred to in #2 are in this DB.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Jenny" <Jenny@.discussions.microsoft.com> wrote in message
news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
Dear SQL Server Gurus:
Your input and advises are greatly appreciated!
We have a SQL Server 2005 database served for Decision Support and Reporting
purposes and it is about 15 G and growing moderately. The source data is
from DB2, an on line transactional database. Everyday, data (existing or
modified tables with data) was transformed into a SQL server staging
database
and got backup on that server. Then the backup was ftp to our produciton
server and the restore process was kick off to restore the database. During
that restore process, our production database got refreshed, however, all
views/functions/store procedures created and edited by the developer will be
gone too. To resolve this problem, everyday our developer before going
home,
she would generate all scripts (including views/functions/procedures) thru
Server Management studio and run it thru command line from a job scheduler
right after the restore process was done. We have over 300 views, functions
and store procedures. When the developer generates the scripts, she needs
to manually reset value on the Server Management Studio and it is pretty
risky, for if she did not reset value right (for instance, set dependency to
true), she will not get the correct scripts and the restore process will
wipe
out the scripts and the reporting programs would fail. I am looking for a
way to streamline the developer's working procedures.
Is there any way to keep the functions/views/procedure stored on the
database when the tables and data got refreshed? or do you have any other
recommendation?
Thanks,
Jenny|||Sounds like best option is to create another database for all of the
reporting stored procedures/functions/views. It will take a little bit of
work to go through all of the objects and change the object name to indicate
the database where the data is located. (Check Books Online for "External
Data and Transact-SQL".)
But the end result is that you 'refresh' process would not 'wipe out' the
objects.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Jenny" <Jenny@.discussions.microsoft.com> wrote in message
news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
> Dear SQL Server Gurus:
> Your input and advises are greatly appreciated!
> We have a SQL Server 2005 database served for Decision Support and
> Reporting
> purposes and it is about 15 G and growing moderately. The source data is
> from DB2, an on line transactional database. Everyday, data (existing or
> modified tables with data) was transformed into a SQL server staging
> database
> and got backup on that server. Then the backup was ftp to our produciton
> server and the restore process was kick off to restore the database.
> During
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will
> be
> gone too. To resolve this problem, everyday our developer before going
> home,
> she would generate all scripts (including views/functions/procedures) thru
> Server Management studio and run it thru command line from a job scheduler
> right after the restore process was done. We have over 300 views,
> functions
> and store procedures. When the developer generates the scripts, she
> needs
> to manually reset value on the Server Management Studio and it is pretty
> risky, for if she did not reset value right (for instance, set dependency
> to
> true), she will not get the correct scripts and the restore process will
> wipe
> out the scripts and the reporting programs would fail. I am looking for a
> way to streamline the developer's working procedures.
> Is there any way to keep the functions/views/procedure stored on the
> database when the tables and data got refreshed? or do you have any other
> recommendation?
>
> Thanks,
>
> Jenny
>|||> During
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will
> be
> gone too.
Rather that generate scripts, consider keeping the 'master' scripts under
source control (or at least in the file system). You can then run the
scripts as part of a post-restore process. This is much less risky than
using the database as the source and generating scripts. Keeping scripts
under source control is a Best Practice in SQL Server development.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jenny" <Jenny@.discussions.microsoft.com> wrote in message
news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
> Dear SQL Server Gurus:
> Your input and advises are greatly appreciated!
> We have a SQL Server 2005 database served for Decision Support and
> Reporting
> purposes and it is about 15 G and growing moderately. The source data is
> from DB2, an on line transactional database. Everyday, data (existing or
> modified tables with data) was transformed into a SQL server staging
> database
> and got backup on that server. Then the backup was ftp to our produciton
> server and the restore process was kick off to restore the database.
> During
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will
> be
> gone too. To resolve this problem, everyday our developer before going
> home,
> she would generate all scripts (including views/functions/procedures) thru
> Server Management studio and run it thru command line from a job scheduler
> right after the restore process was done. We have over 300 views,
> functions
> and store procedures. When the developer generates the scripts, she
> needs
> to manually reset value on the Server Management Studio and it is pretty
> risky, for if she did not reset value right (for instance, set dependency
> to
> true), she will not get the correct scripts and the restore process will
> wipe
> out the scripts and the reporting programs would fail. I am looking for a
> way to streamline the developer's working procedures.
> Is there any way to keep the functions/views/procedure stored on the
> database when the tables and data got refreshed? or do you have any other
> recommendation?
>
> Thanks,
>
> Jenny
>|||3-part naming of objects is of the form:
SQL 2000: Database.Owner.ObjectName
SQL 2005: Database.Schema.ObjectName
Thus, you would refer to a table in the MyDB database as:
MyDB.dbo.MyTable
You would have 2 DB's, say, DBFunc and DBData. Place your stored procs and
functions in DBFunc. Place your data in DBData. In the functions/procs,
refer to your tables in the form shown above.
HTH
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Jenny" <Jenny@.discussions.microsoft.com> wrote in message
news:3F2249E6-86F6-454A-9DCA-1C4F194348D4@.microsoft.com...
Hi Tom,
What do you mean in item 2 and 3?
I created a backup db and truncate data, which would fullfilled the first
comments you mentioned. Then what is next? I don't quite understand what
you said on item 2 & 3.
Thanks,
"Tom Moreau" wrote:
> 1) Create a separate DB for the functions and procs.
> 2) Use 3-part naming for the objects they access.
> 3) Create a separate DB with data only. Refresh just that DB. The
> objects referred to in #2 are in this DB.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Jenny" <Jenny@.discussions.microsoft.com> wrote in message
> news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
> Dear SQL Server Gurus:
> Your input and advises are greatly appreciated!
> We have a SQL Server 2005 database served for Decision Support and
> Reporting
> purposes and it is about 15 G and growing moderately. The source data is
> from DB2, an on line transactional database. Everyday, data (existing or
> modified tables with data) was transformed into a SQL server staging
> database
> and got backup on that server. Then the backup was ftp to our produciton
> server and the restore process was kick off to restore the database.
> During
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will
> be
> gone too. To resolve this problem, everyday our developer before going
> home,
> she would generate all scripts (including views/functions/procedures) thru
> Server Management studio and run it thru command line from a job scheduler
> right after the restore process was done. We have over 300 views,
> functions
> and store procedures. When the developer generates the scripts, she
> needs
> to manually reset value on the Server Management Studio and it is pretty
> risky, for if she did not reset value right (for instance, set dependency
> to
> true), she will not get the correct scripts and the restore process will
> wipe
> out the scripts and the reporting programs would fail. I am looking for a
> way to streamline the developer's working procedures.
> Is there any way to keep the functions/views/procedure stored on the
> database when the tables and data got refreshed? or do you have any other
> recommendation?
>
> Thanks,
>
> Jenny
>

Any SQL Server Guru?

Dear SQL Server Gurus:
Your input and advises are greatly appreciated!
We have a SQL Server 2005 database served for Decision Support and Reporting
purposes and it is about 15 G and growing moderately. The source data is
from DB2, an on line transactional database. Everyday, data (existing or
modified tables with data) was transformed into a SQL server staging databas
e
and got backup on that server. Then the backup was ftp to our produciton
server and the restore process was kick off to restore the database. During
that restore process, our production database got refreshed, however, all
views/functions/store procedures created and edited by the developer will be
gone too. To resolve this problem, everyday our developer before going home
,
she would generate all scripts (including views/functions/procedures) thru
Server Management studio and run it thru command line from a job scheduler
right after the restore process was done. We have over 300 views, functions
and store procedures. When the developer generates the scripts, she needs
to manually reset value on the Server Management Studio and it is pretty
risky, for if she did not reset value right (for instance, set dependency to
true), she will not get the correct scripts and the restore process will wip
e
out the scripts and the reporting programs would fail. I am looking for a
way to streamline the developer's working procedures.
Is there any way to keep the functions/views/procedure stored on the
database when the tables and data got refreshed? or do you have any other
recommendation?
Thanks,
Jenny1) Create a separate DB for the functions and procs.
2) Use 3-part naming for the objects they access.
3) Create a separate DB with data only. Refresh just that DB. The
objects referred to in #2 are in this DB.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Jenny" <Jenny@.discussions.microsoft.com> wrote in message
news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
Dear SQL Server Gurus:
Your input and advises are greatly appreciated!
We have a SQL Server 2005 database served for Decision Support and Reporting
purposes and it is about 15 G and growing moderately. The source data is
from DB2, an on line transactional database. Everyday, data (existing or
modified tables with data) was transformed into a SQL server staging
database
and got backup on that server. Then the backup was ftp to our produciton
server and the restore process was kick off to restore the database. During
that restore process, our production database got refreshed, however, all
views/functions/store procedures created and edited by the developer will be
gone too. To resolve this problem, everyday our developer before going
home,
she would generate all scripts (including views/functions/procedures) thru
Server Management studio and run it thru command line from a job scheduler
right after the restore process was done. We have over 300 views, functions
and store procedures. When the developer generates the scripts, she needs
to manually reset value on the Server Management Studio and it is pretty
risky, for if she did not reset value right (for instance, set dependency to
true), she will not get the correct scripts and the restore process will
wipe
out the scripts and the reporting programs would fail. I am looking for a
way to streamline the developer's working procedures.
Is there any way to keep the functions/views/procedure stored on the
database when the tables and data got refreshed? or do you have any other
recommendation?
Thanks,
Jenny|||Sounds like best option is to create another database for all of the
reporting stored procedures/functions/views. It will take a little bit of
work to go through all of the objects and change the object name to indicate
the database where the data is located. (Check Books Online for "External
Data and Transact-SQL".)
But the end result is that you 'refresh' process would not 'wipe out' the
objects.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Jenny" <Jenny@.discussions.microsoft.com> wrote in message
news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
> Dear SQL Server Gurus:
> Your input and advises are greatly appreciated!
> We have a SQL Server 2005 database served for Decision Support and
> Reporting
> purposes and it is about 15 G and growing moderately. The source data is
> from DB2, an on line transactional database. Everyday, data (existing or
> modified tables with data) was transformed into a SQL server staging
> database
> and got backup on that server. Then the backup was ftp to our produciton
> server and the restore process was kick off to restore the database.
> During
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will
> be
> gone too. To resolve this problem, everyday our developer before going
> home,
> she would generate all scripts (including views/functions/procedures) thru
> Server Management studio and run it thru command line from a job scheduler
> right after the restore process was done. We have over 300 views,
> functions
> and store procedures. When the developer generates the scripts, she
> needs
> to manually reset value on the Server Management Studio and it is pretty
> risky, for if she did not reset value right (for instance, set dependency
> to
> true), she will not get the correct scripts and the restore process will
> wipe
> out the scripts and the reporting programs would fail. I am looking for a
> way to streamline the developer's working procedures.
> Is there any way to keep the functions/views/procedure stored on the
> database when the tables and data got refreshed? or do you have any other
> recommendation?
>
> Thanks,
>
> Jenny
>|||> During
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will
> be
> gone too.
Rather that generate scripts, consider keeping the 'master' scripts under
source control (or at least in the file system). You can then run the
scripts as part of a post-restore process. This is much less risky than
using the database as the source and generating scripts. Keeping scripts
under source control is a Best Practice in SQL Server development.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jenny" <Jenny@.discussions.microsoft.com> wrote in message
news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
> Dear SQL Server Gurus:
> Your input and advises are greatly appreciated!
> We have a SQL Server 2005 database served for Decision Support and
> Reporting
> purposes and it is about 15 G and growing moderately. The source data is
> from DB2, an on line transactional database. Everyday, data (existing or
> modified tables with data) was transformed into a SQL server staging
> database
> and got backup on that server. Then the backup was ftp to our produciton
> server and the restore process was kick off to restore the database.
> During
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will
> be
> gone too. To resolve this problem, everyday our developer before going
> home,
> she would generate all scripts (including views/functions/procedures) thru
> Server Management studio and run it thru command line from a job scheduler
> right after the restore process was done. We have over 300 views,
> functions
> and store procedures. When the developer generates the scripts, she
> needs
> to manually reset value on the Server Management Studio and it is pretty
> risky, for if she did not reset value right (for instance, set dependency
> to
> true), she will not get the correct scripts and the restore process will
> wipe
> out the scripts and the reporting programs would fail. I am looking for a
> way to streamline the developer's working procedures.
> Is there any way to keep the functions/views/procedure stored on the
> database when the tables and data got refreshed? or do you have any other
> recommendation?
>
> Thanks,
>
> Jenny
>|||Hi Tom,
What do you mean in item 2 and 3?
I created a backup db and truncate data, which would fullfilled the first
comments you mentioned. Then what is next? I don't quite understand what
you said on item 2 & 3.
Thanks,
"Tom Moreau" wrote:

> 1) Create a separate DB for the functions and procs.
> 2) Use 3-part naming for the objects they access.
> 3) Create a separate DB with data only. Refresh just that DB. The
> objects referred to in #2 are in this DB.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Jenny" <Jenny@.discussions.microsoft.com> wrote in message
> news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
> Dear SQL Server Gurus:
> Your input and advises are greatly appreciated!
> We have a SQL Server 2005 database served for Decision Support and Reporti
ng
> purposes and it is about 15 G and growing moderately. The source data is
> from DB2, an on line transactional database. Everyday, data (existing or
> modified tables with data) was transformed into a SQL server staging
> database
> and got backup on that server. Then the backup was ftp to our produciton
> server and the restore process was kick off to restore the database. Duri
ng
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will
be
> gone too. To resolve this problem, everyday our developer before going
> home,
> she would generate all scripts (including views/functions/procedures) thru
> Server Management studio and run it thru command line from a job scheduler
> right after the restore process was done. We have over 300 views, functio
ns
> and store procedures. When the developer generates the scripts, she need
s
> to manually reset value on the Server Management Studio and it is pretty
> risky, for if she did not reset value right (for instance, set dependency
to
> true), she will not get the correct scripts and the restore process will
> wipe
> out the scripts and the reporting programs would fail. I am looking for a
> way to streamline the developer's working procedures.
> Is there any way to keep the functions/views/procedure stored on the
> database when the tables and data got refreshed? or do you have any other
> recommendation?
>
> Thanks,
>
> Jenny
>|||3-part naming of objects is of the form:
SQL 2000: Database.Owner.ObjectName
SQL 2005: Database.Schema.ObjectName
Thus, you would refer to a table in the MyDB database as:
MyDB.dbo.MyTable
You would have 2 DB's, say, DBFunc and DBData. Place your stored procs and
functions in DBFunc. Place your data in DBData. In the functions/procs,
refer to your tables in the form shown above.
HTH
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Jenny" <Jenny@.discussions.microsoft.com> wrote in message
news:3F2249E6-86F6-454A-9DCA-1C4F194348D4@.microsoft.com...
Hi Tom,
What do you mean in item 2 and 3?
I created a backup db and truncate data, which would fullfilled the first
comments you mentioned. Then what is next? I don't quite understand what
you said on item 2 & 3.
Thanks,
"Tom Moreau" wrote:

> 1) Create a separate DB for the functions and procs.
> 2) Use 3-part naming for the objects they access.
> 3) Create a separate DB with data only. Refresh just that DB. The
> objects referred to in #2 are in this DB.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Jenny" <Jenny@.discussions.microsoft.com> wrote in message
> news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
> Dear SQL Server Gurus:
> Your input and advises are greatly appreciated!
> We have a SQL Server 2005 database served for Decision Support and
> Reporting
> purposes and it is about 15 G and growing moderately. The source data is
> from DB2, an on line transactional database. Everyday, data (existing or
> modified tables with data) was transformed into a SQL server staging
> database
> and got backup on that server. Then the backup was ftp to our produciton
> server and the restore process was kick off to restore the database.
> During
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will
> be
> gone too. To resolve this problem, everyday our developer before going
> home,
> she would generate all scripts (including views/functions/procedures) thru
> Server Management studio and run it thru command line from a job scheduler
> right after the restore process was done. We have over 300 views,
> functions
> and store procedures. When the developer generates the scripts, she
> needs
> to manually reset value on the Server Management Studio and it is pretty
> risky, for if she did not reset value right (for instance, set dependency
> to
> true), she will not get the correct scripts and the restore process will
> wipe
> out the scripts and the reporting programs would fail. I am looking for a
> way to streamline the developer's working procedures.
> Is there any way to keep the functions/views/procedure stored on the
> database when the tables and data got refreshed? or do you have any other
> recommendation?
>
> Thanks,
>
> Jenny
>

Any Input: Converting from Sybase to SQL

Any Input: Converting from Sybase to SQLMonte Tiernan wrote:

Quote:

Originally Posted by

>

[this space intentionally left blank]