creating variables in a Stored procedure

I would like to know if I can create new variables using existing variables in a stored procedure.

To be clear, in the SP I use, I pass table name. But I need the results from 2 other tables as well.

The tables are named 1996, 1997, 1998.......2007.
If I pass 2005 to SP, I need results from 2005, 2004 & 2003. How do I assign or get 2 new table names (variables) for 2004 & 2003 ?

Part of code:
ALTER Procedure [dbo].[XX](
@.TblName1 varchar(20),
@.Month varchar(3)
)

when I pass 2005 to SP, I need
@.TblName1 = 2005
@.TblName2 = 2004
@.TblName3 = 2003

How can assign 2004 & 2003 to variables TblName2 & Tblname3 ?

I really appreciate any help.

look up either the SELECT statement or the SET statement in books online. But you seem to have an inconsistency in the way you are using the data. Your code has:

Code Snippet

ALTER Procedure [dbo].[XX](
@.TblName1 varchar(20),
@.Month varchar(3)
)

when I pass 2005 to SP, I need
@.TblName1 = 2005
@.TblName2 = 2004
@.TblName3 = 2003

But you seem to be subtracting 1 and 2 from parameter passed to the proedure. But if that is what you want, you need something like:

Code Snippet

ALTER Procedure [dbo].[XX](
@.TblName1 varchar(20),
@.Month varchar(3)
)

as

declare @.TblName2 varchar(20)

declare @.TblName3 varchar(20)

set @.TblName2 = convert(integer, @.TblName1) - 1

set @.TblName3 = convert(integer, @.TblName1) - 2

But a big problem now is that if you pass a string in for you table name such as 'aTable'

you will get execution errors.

You can avoid this by use of IF statements such as:

Code Snippet

IF isNumeric(@.TblName1) = 1

select @.TblName2 = convert(integer, @.TblName1) - 1,

@.TblName3 = convert(integer, @.TblName2)

Now if that isn't enough, there are times (actually, many times) in which the "IsNumeric" function will not correctly assess whether or not a string is numeric. There is an article that talks about these particular problems here:

http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html

Look for the "IsReallyNumeric" funtion at this site. That should be enough to keep you going for at least a little while.

Kent

|||

Miamik,

This has all the markings of a very unwieldy design. I would not consider having identical tables named as this seems to indicate JUST as a method to partition data.

You would be better served to explore table partitioning in Books Online. Then your code would be so-o-o-o much easier, and you wouldn't have to resort to using dynamic SQL (with all of its' issues and security problems.)

|||I think Arnie is definitely telling it like it is.|||Thanks for all your replies.

Creating table in linked server

I have created a linked server in sql 2005. I want to create a table from an existing database in the newly created linked server.

Any suggestion pls.I think you must use the exact qualified name of table.

Like this :

CREATE TABLE [LINKED-SERVER].[LINKED-DATABASE].[dbo].[AnagCategorie](
[Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[CodCategoria] [dbo].[CodCat] NOT NULL,
[CodDitta] [dbo].[CodDitta] NOT NULL,
[Descr] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
CONSTRAINT [PK_AnagCategorie] PRIMARY KEY CLUSTERED
(
[CodCategoria] ASC,
[CodDitta] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]sql

Creating SSIS Package Event Handlers

I would like to create an event handler that would catch any errors that result from a sys.<table> not existing. The package is designed to run on both SQL Server 2000 and SQL Server 2005 and when I query sys.<tables> there is an error when the query is run on SQL Server 2000. I just need a good starting point...I would like something that when the server isn't 2005 it just skips the server and doesn't fail the package and doesn't get counted towards the max error count. Thanks for any help.
-Kyle

Add an Execute SQL task that queries @.@.VERSION prior to the query that uses the sys.*. Then you can use precedence constraints based on the version value to determine whether the tasks execute.

Code Snippet

SELECT @.@.VERSION

Creating SQL2000 Database from SQL7 Database

I'm trying to create a new database on a SQL2000 server
from an existing one in a SQL7 server. If I dettach/attach
database or restore from an existing backup, I have this
error message:
"Microsoft SQL-DMO (ODBC SQLState:HY000)
Error 3624:
Location: xdes.cpp:1060
Expression: (m_state==XDES_ACTIVE_NOLOG)||IS_ON_SAFE
(XDES_OUTCOME_ONLY, m_status)||(IS_ON_SAFE
(XDES_BEGINXACT_LOGGED, m_status)&&(m_beginLsn!=NullLSN)&&
(m_lastLsn!=NullLSN))
SPID: 53
Process ID: 1064"
What can I do?Suggest running DBCC CHECKDB on the source database to ensure there is no
corruption.
Also check these resources :-
http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b317852
http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b283899
http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b274266
http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b324630
http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b319212
http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b295039
http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b281220
HTH
Ryan Waight, MCDBA, MCSE
"Susana Pimentel" <spimentel@.aeportugal.com> wrote in message
news:0c3201c38f27$8f687530$a401280a@.phx.gbl...
> I'm trying to create a new database on a SQL2000 server
> from an existing one in a SQL7 server. If I dettach/attach
> database or restore from an existing backup, I have this
> error message:
> "Microsoft SQL-DMO (ODBC SQLState:HY000)
> Error 3624:
> Location: xdes.cpp:1060
> Expression: (m_state==XDES_ACTIVE_NOLOG)||IS_ON_SAFE
> (XDES_OUTCOME_ONLY, m_status)||(IS_ON_SAFE
> (XDES_BEGINXACT_LOGGED, m_status)&&(m_beginLsn!=NullLSN)&&
> (m_lastLsn!=NullLSN))
> SPID: 53
> Process ID: 1064"
> What can I do?

creating SQL from ACCESS

what is the best way to create a MS SQL 2000 database from an existing MS ACCESS 2003 database

with all datas in it ?

thank youUpsizing Wizard in Access (Tools/database Utilities)

DTS in Enterprise Manager (right click, All tasks, Import Data as I recall).

Creating Scripts via Query Analyzer or Enterprise Manager

I'm trying to decide what is the best practice in terms of creating scripts
that, for instance, update an existing database. It seems there are a
couple of ways to do this (although I am sure there are more). It is often
suggested that you code your changes, by hand, using Enterprise Manager. As
I am looking at examples how to do this, I understand some of the basics of
doing this. For instance, if I have a tabled called Numbers and there is a
field named Employee (data type int, length 4, non nullable) that I want to
add a unique constraint to, it seems that most examples have the following
simplistic syntax:
ALTER TABLE Numbers
ADD CONSTRAINT EmployeeUnique
UNIQUE(Employee)
If I make the same change via Enterprise Manager and then click the Save
Change Script button, the amount of code it creates is much more:
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Numbers ADD CONSTRAINT
IX_Numbers UNIQUE NONCLUSTERED
(
Employee
) ON [PRIMARY]
GO
COMMIT
So, is all of this extra code necessary? Are the examples I am looking at
too simplistic? Is it OK to use EM to make changes and then generate the
scripts?
Thanks.It si absolutely Ok to that in QA (the way you did).
All these SET statements, generated by EM, are actually happening whenever
you create a new session in QA, BUT behind the scene. You may see this if
you trace tsql exec from QA by Profiler.
With transactions is the same situation: in case of implicit transactions,
new T-SQL statement starts a new transaction. This explains Begin-commit
blocks inside EM-generated code.
All these settings are server defaults and can be changed.
Regards,
Marko Simic
"epigram" wrote:

> I'm trying to decide what is the best practice in terms of creating script
s
> that, for instance, update an existing database. It seems there are a
> couple of ways to do this (although I am sure there are more). It is ofte
n
> suggested that you code your changes, by hand, using Enterprise Manager.
As
> I am looking at examples how to do this, I understand some of the basics o
f
> doing this. For instance, if I have a tabled called Numbers and there is
a
> field named Employee (data type int, length 4, non nullable) that I want t
o
> add a unique constraint to, it seems that most examples have the following
> simplistic syntax:
> ALTER TABLE Numbers
> ADD CONSTRAINT EmployeeUnique
> UNIQUE(Employee)
> If I make the same change via Enterprise Manager and then click the Save
> Change Script button, the amount of code it creates is much more:
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> ALTER TABLE dbo.Numbers ADD CONSTRAINT
> IX_Numbers UNIQUE NONCLUSTERED
> (
> Employee
> ) ON [PRIMARY]
> GO
> COMMIT
>
> So, is all of this extra code necessary? Are the examples I am looking at
> too simplistic? Is it OK to use EM to make changes and then generate the
> scripts?
> Thanks.
>
>|||Do you save all your change scripts so you have a complete audit trail of al
l
changes made to your database? How you make the changes is just one part of
database change management. Check out this article on the subject
http://www.innovartis.co.uk/pdf/ In...Mgt.
pdf
This is an article showing an approach to database change managment that
will always work and gives you a complete audit trail on all database change
s
without slowing down the development of the database. It was the foundation
of the software called DB Ghost (http://www.dbghost.com)
How the database is developed (whether you use Query Analyzer, Enterprise
Manager or other third party tools) holds little relevance to the larger
problems of deploying those changes and having a clear picture of what has
changed, when it was changed, who it was changed by - so any problems can b
e
quickly solved in a repeatable and totally auditable manner. This (I believe
)
is the essence of database change management.
Excuse me for ranting, however I find it very frustrating watching the same
mistakes repeated time and again...
"epigram" wrote:

> I'm trying to decide what is the best practice in terms of creating script
s
> that, for instance, update an existing database. It seems there are a
> couple of ways to do this (although I am sure there are more). It is ofte
n
> suggested that you code your changes, by hand, using Enterprise Manager.
As
> I am looking at examples how to do this, I understand some of the basics o
f
> doing this. For instance, if I have a tabled called Numbers and there is
a
> field named Employee (data type int, length 4, non nullable) that I want t
o
> add a unique constraint to, it seems that most examples have the following
> simplistic syntax:
> ALTER TABLE Numbers
> ADD CONSTRAINT EmployeeUnique
> UNIQUE(Employee)
> If I make the same change via Enterprise Manager and then click the Save
> Change Script button, the amount of code it creates is much more:
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> ALTER TABLE dbo.Numbers ADD CONSTRAINT
> IX_Numbers UNIQUE NONCLUSTERED
> (
> Employee
> ) ON [PRIMARY]
> GO
> COMMIT
>
> So, is all of this extra code necessary? Are the examples I am looking at
> too simplistic? Is it OK to use EM to make changes and then generate the
> scripts?
> Thanks.
>
>

Creating Scripts via Query Analyzer or Enterprise Manager

I'm trying to decide what is the best practice in terms of creating scripts
that, for instance, update an existing database. It seems there are a
couple of ways to do this (although I am sure there are more). It is often
suggested that you code your changes, by hand, using Enterprise Manager. As
I am looking at examples how to do this, I understand some of the basics of
doing this. For instance, if I have a tabled called Numbers and there is a
field named Employee (data type int, length 4, non nullable) that I want to
add a unique constraint to, it seems that most examples have the following
simplistic syntax:
ALTER TABLE Numbers
ADD CONSTRAINT EmployeeUnique
UNIQUE(Employee)
If I make the same change via Enterprise Manager and then click the Save
Change Script button, the amount of code it creates is much more:
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Numbers ADD CONSTRAINT
IX_Numbers UNIQUE NONCLUSTERED
(
Employee
) ON [PRIMARY]
GO
COMMIT
So, is all of this extra code necessary? Are the examples I am looking at
too simplistic? Is it OK to use EM to make changes and then generate the
scripts?
Thanks.
It si absolutely Ok to that in QA (the way you did).
All these SET statements, generated by EM, are actually happening whenever
you create a new session in QA, BUT behind the scene. You may see this if
you trace tsql exec from QA by Profiler.
With transactions is the same situation: in case of implicit transactions,
new T-SQL statement starts a new transaction. This explains Begin-commit
blocks inside EM-generated code.
All these settings are server defaults and can be changed.
Regards,
Marko Simic
"epigram" wrote:

> I'm trying to decide what is the best practice in terms of creating scripts
> that, for instance, update an existing database. It seems there are a
> couple of ways to do this (although I am sure there are more). It is often
> suggested that you code your changes, by hand, using Enterprise Manager. As
> I am looking at examples how to do this, I understand some of the basics of
> doing this. For instance, if I have a tabled called Numbers and there is a
> field named Employee (data type int, length 4, non nullable) that I want to
> add a unique constraint to, it seems that most examples have the following
> simplistic syntax:
> ALTER TABLE Numbers
> ADD CONSTRAINT EmployeeUnique
> UNIQUE(Employee)
> If I make the same change via Enterprise Manager and then click the Save
> Change Script button, the amount of code it creates is much more:
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> ALTER TABLE dbo.Numbers ADD CONSTRAINT
> IX_Numbers UNIQUE NONCLUSTERED
> (
> Employee
> ) ON [PRIMARY]
> GO
> COMMIT
>
> So, is all of this extra code necessary? Are the examples I am looking at
> too simplistic? Is it OK to use EM to make changes and then generate the
> scripts?
> Thanks.
>
>
|||Do you save all your change scripts so you have a complete audit trail of all
changes made to your database? How you make the changes is just one part of
database change management. Check out this article on the subject
http://www.innovartis.co.uk/pdf/Inno...ange_Mgt. pdf
This is an article showing an approach to database change managment that
will always work and gives you a complete audit trail on all database changes
without slowing down the development of the database. It was the foundation
of the software called DB Ghost (http://www.dbghost.com)
How the database is developed (whether you use Query Analyzer, Enterprise
Manager or other third party tools) holds little relevance to the larger
problems of deploying those changes and having a clear picture of what has
changed, when it was changed, who it was changed by - so any problems can be
quickly solved in a repeatable and totally auditable manner. This (I believe)
is the essence of database change management.
Excuse me for ranting, however I find it very frustrating watching the same
mistakes repeated time and again...
"epigram" wrote:

> I'm trying to decide what is the best practice in terms of creating scripts
> that, for instance, update an existing database. It seems there are a
> couple of ways to do this (although I am sure there are more). It is often
> suggested that you code your changes, by hand, using Enterprise Manager. As
> I am looking at examples how to do this, I understand some of the basics of
> doing this. For instance, if I have a tabled called Numbers and there is a
> field named Employee (data type int, length 4, non nullable) that I want to
> add a unique constraint to, it seems that most examples have the following
> simplistic syntax:
> ALTER TABLE Numbers
> ADD CONSTRAINT EmployeeUnique
> UNIQUE(Employee)
> If I make the same change via Enterprise Manager and then click the Save
> Change Script button, the amount of code it creates is much more:
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> ALTER TABLE dbo.Numbers ADD CONSTRAINT
> IX_Numbers UNIQUE NONCLUSTERED
> (
> Employee
> ) ON [PRIMARY]
> GO
> COMMIT
>
> So, is all of this extra code necessary? Are the examples I am looking at
> too simplistic? Is it OK to use EM to make changes and then generate the
> scripts?
> Thanks.
>
>

Creating RDL using information contained in an existing Data Model

This question is in regard to creating RDL programmatically, using information contained in an existing Data Model; basically mimicking what the Report Builder does.

What I need to do is integrate a dummied down version of the Report Builder into an existing web application; where users will have the option to build and save reports using a very simple web interface, 4 step process. This process will only have a very few of the options that the Report Builder has.

So far I have built this process referencing the Microsoft Reporting Services dll's (Microsoft.ReportingServices.Modeling.dll for example), in order to retrieve the entity and attributes information from the Data Model.

** Does anyone know how I could create the RDL, using both the entity and attribute information contained in the Data Model in association with selected fields, filters, etc, that is contained in a database?

I can't find anything about this anywhere on the net; any help would be greatly appreciated!

Thanks,

Dan

There isn't an MS tool to automate this with RS 2005. To meet a similar requirement and speed up the implementation effort, we decided to develop an object wrapper on top of RDL which knows how to serialize itself to RDL using the .NET XmlSerializer. In comparison with the XML DOM alternative, we found this approach to work much better for us. You need to familiriaze yourself with the RDL specification beforehand.|||

Btw, more information about the RDL specification mentioned by Teo is available here: http://www.microsoft.com/sql/technologies/reporting/rdlspec.mspx

-- Robert

|||Well thank you very much for this information, I just wanted to make sure I was not missing out on existing functionality!

Creating New tables by copying existing table

Hi,
Is there any easy way of copying the structure of a table within a single
database and creating a new table with a new name. I have an app that the
structure of several new tables is close enough that it would save time if I
could create one and copy and recreate a new one under a new name. Then I
would only have to make the changes that are different.
thanks,
hughVery Easy
Select * into NewTableName from OldTableName where 1= 0
Keep in mind that this is just the table structure no keys, constraints or
indexes are created
http://sqlservercode.blogspot.com/
"Hugh O" wrote:

> Hi,
> Is there any easy way of copying the structure of a table within a single
> database and creating a new table with a new name. I have an app that the
> structure of several new tables is close enough that it would save time if
I
> could create one and copy and recreate a new one under a new name. Then I
> would only have to make the changes that are different.
> thanks,
> hugh
>
>|||Script the CREATE TABLE statement and then edit it. You can do that in Query
Analyzer.
David Portas
SQL Server MVP
--
"Hugh O" <HughLD@.newsgroup.nospam> wrote in message
news:%23kGLB6DzFHA.2008@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Is there any easy way of copying the structure of a table within a single
> database and creating a new table with a new name. I have an app that the
> structure of several new tables is close enough that it would save time if
> I could create one and copy and recreate a new one under a new name. Then
> I would only have to make the changes that are different.
> thanks,
> hugh
>|||Hi
You should consider putting your table definitions under version control in
which case you could use the scripting options on Enterpeise Manager or
Query Analyser to create the file to be edited.
John
"Hugh O" <HughLD@.newsgroup.nospam> wrote in message
news:%23kGLB6DzFHA.2008@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Is there any easy way of copying the structure of a table within a single
> database and creating a new table with a new name. I have an app that the
> structure of several new tables is close enough that it would save time if
> I could create one and copy and recreate a new one under a new name. Then
> I would only have to make the changes that are different.
> thanks,
> hugh
>|||>> I have an app that the structure of several new tables is close enough th
at it would save time if I could create one and copy and recreate a new one
under a new name. Then I would only have to make the changes that are diffe
rent. <<
Be careful about this. While not a certainity, this is a sign that you
might have split a table on an attributes. For example, someone
recently posted a set of tables where an event had been put into a
temporal table and a separate physical location table and then was
trying to tie them back together to answer queries about the location
and status at a site.|||Thanks SQL, David, John, & Celko
These newsgroups are great. So helpful and shows the diversity and
alternatives so clearly.
hugh
"Hugh O" <HughLD@.newsgroup.nospam> wrote in message
news:%23kGLB6DzFHA.2008@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Is there any easy way of copying the structure of a table within a single
> database and creating a new table with a new name. I have an app that the
> structure of several new tables is close enough that it would save time if
> I could create one and copy and recreate a new one under a new name. Then
> I would only have to make the changes that are different.
> thanks,
> hugh
>

Creating new table from existing table

Hi,
I m new to this forum with a Query
Create table <table name1 > as select * from <table name2>
this works for oracle. do anybody knows its alternative in sqlserver 2000
thanx. :)you can use SELECT INTO

select * into <table name2> from <table name1 >|||Hi

A minor adjustment:
SELECT TOP 0 * INTO <table name2>
FROM <table name1 >

SELECT * INTO <table name2>
FROM <table name1 >
This does the same thing but creates the table and then populates it. Reason? Whilst the table is being created, tempdb gets locked up. If you do it in one stage then the creation lasts as long as the data insertion. Lots of data = long tempdb lock up.

HTH|||Hi

A minor adjustment:
SELECT TOP 0 * INTO <table name2>
FROM <table name1 >

SELECT * INTO <table name2>
FROM <table name1 >
This does the same thing but creates the table and then populates it. Reason? Whilst the table is being created, tempdb gets locked up. If you do it in one stage then the creation lasts as long as the data insertion. Lots of data = long tempdb lock up.

HTH

Again correction,above code will throw error.And tempdb will get locked if u r creating temp table otherwise it will lock some of the system tables in database where u create table.

SELECT TOP 0 * INTO <table name2>
FROM <table name1 >

insert into <table name2>
select *
from <table name1 >|||Again correction,above code will throw error.And tempdb will get locked if u r creating temp table otherwise it will lock some of the system tables in database where u create table.

SELECT TOP 0 * INTO <table name2>
FROM <table name1 >

insert into <table name2>
select *
from <table name1 >
Duh - yeah - thanks Mallier - copy and paste error :o

Creating new File groups on an existing db

Out production database is around 10G and it is all in one file group
(Primary).
I would like to create new file groups and split the database into multiple
file groups for better manageablity.
I have identified the tables that can be moved into new file groups.
What is the best approach to spilt an existing production database into
multiple file groups?
Thanks,
S.KumarSuresh
If your table whose are going to be place on separate filegroup have
clustered indexes then you can easily re-create the clustered index with
specify file group .For details please refer to BOL.
"Suresh Kumar" <skumat@.pcdiDOTcom> wrote in message
news:#A3i3KWbDHA.2548@.TK2MSFTNGP09.phx.gbl...
> Out production database is around 10G and it is all in one file group
> (Primary).
> I would like to create new file groups and split the database into
multiple
> file groups for better manageablity.
> I have identified the tables that can be moved into new file groups.
> What is the best approach to spilt an existing production database into
> multiple file groups?
> Thanks,
> S.Kumar
>|||You should start changing the filegroups for the specified tables, if there
aren´t many tables you could work with EM, edit table, property of the
tables/ indexes.
Jens Süßmeyer.|||Multiple files in a single filegroup allows SQL to do parallel IO on a
single query...
Using multiple filegroups allow you to.
1. Backup/Restore subsets of tables with different recovery needs.
2. Balance IO if you beleive you can do better than striping
3. Limit the space allocation which is used by a subset of tables...
The 1st is the best reason for filegroups...It does not improve
manageability, but causes you to have to watch for disk space utilization on
EACH filegroup...
--
Wayne Snyder MCDBA, SQL Server MVP
Computer Education Services Corp (CESC), Charlotte, NC
(Please respond only to the newsgroups.)
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Suresh Kumar" <skumat@.pcdiDOTcom> wrote in message
news:OxBFFmWbDHA.3360@.tk2msftngp13.phx.gbl...
> My aim is to split the database into multiple files.
> I could have multiple files referring to the same File Group (Primary) or
> multiple files going to multiple File groups.
> Which way is better and what are the pros and cons?
> Thanks,
> S.Kumar
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eki5qUWbDHA.1580@.tk2msftngp13.phx.gbl...
> > Suresh
> > If your table whose are going to be place on separate filegroup have
> > clustered indexes then you can easily re-create the clustered index with
> > specify file group .For details please refer to BOL.
> >
> >
> >
> > "Suresh Kumar" <skumat@.pcdiDOTcom> wrote in message
> > news:#A3i3KWbDHA.2548@.TK2MSFTNGP09.phx.gbl...
> > > Out production database is around 10G and it is all in one file group
> > > (Primary).
> > > I would like to create new file groups and split the database into
> > multiple
> > > file groups for better manageablity.
> > >
> > > I have identified the tables that can be moved into new file groups.
> > > What is the best approach to spilt an existing production database
into
> > > multiple file groups?
> > >
> > > Thanks,
> > > S.Kumar
> > >
> > >
> >
> >
>|||Wayne,
Did you use to work for Unisys ?
I use to know one Wayne Snyder when I worked for them in Charlotte 10 yrs
ago.
If you are the one, please send me a note to my personal email address.
Thanks,
Suresh Kumar
"Wayne Snyder" <wsnyder@.ikon.com> wrote in message
news:OyKlrSXbDHA.2672@.tk2msftngp13.phx.gbl...
> Multiple files in a single filegroup allows SQL to do parallel IO on a
> single query...
> Using multiple filegroups allow you to.
> 1. Backup/Restore subsets of tables with different recovery needs.
> 2. Balance IO if you beleive you can do better than striping
> 3. Limit the space allocation which is used by a subset of tables...
> The 1st is the best reason for filegroups...It does not improve
> manageability, but causes you to have to watch for disk space utilization
on
> EACH filegroup...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Computer Education Services Corp (CESC), Charlotte, NC
> (Please respond only to the newsgroups.)
> PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
> "Suresh Kumar" <skumat@.pcdiDOTcom> wrote in message
> news:OxBFFmWbDHA.3360@.tk2msftngp13.phx.gbl...
> > My aim is to split the database into multiple files.
> > I could have multiple files referring to the same File Group (Primary)
or
> > multiple files going to multiple File groups.
> >
> > Which way is better and what are the pros and cons?
> > Thanks,
> > S.Kumar
> >
> >
> > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > news:eki5qUWbDHA.1580@.tk2msftngp13.phx.gbl...
> > > Suresh
> > > If your table whose are going to be place on separate filegroup have
> > > clustered indexes then you can easily re-create the clustered index
with
> > > specify file group .For details please refer to BOL.
> > >
> > >
> > >
> > > "Suresh Kumar" <skumat@.pcdiDOTcom> wrote in message
> > > news:#A3i3KWbDHA.2548@.TK2MSFTNGP09.phx.gbl...
> > > > Out production database is around 10G and it is all in one file
group
> > > > (Primary).
> > > > I would like to create new file groups and split the database into
> > > multiple
> > > > file groups for better manageablity.
> > > >
> > > > I have identified the tables that can be moved into new file groups.
> > > > What is the best approach to spilt an existing production database
> into
> > > > multiple file groups?
> > > >
> > > > Thanks,
> > > > S.Kumar
> > > >
> > > >
> > >
> > >
> >
> >
>

Creating Nested Tables inside of a Existing SQL Server Express Table

Hello,

Quick question, I hope, I am trying to create a table that has a column that is a nested table in SQL Server 2005 Express Edition. Any ideas how I could go about doing this?

Sincerely,

James Simpson

Straightway Technologies Inc.

The only way to "create a table that has a column that is a nested table", is to use an xml datatype.

Refer to Books Online, Topic: XML [SQL Server]

|||

As Arnie indicates, SQL Server doesn't support TABLE as a data type within a table. There is probably another way to accomlish what you're trying to do without going to XML, but it's hard to say without know what your goal is. In general, I'd suggest that you create a new table for your "nested" information and then use a 1:Many relationship between the two tables to map the nested info to the parent record.

Mike

Creating Named Query in Analysis Services Using AMO

Hello,

Is there an example somewhere that shows how to create a named query in DSV based on an existing table in the DSV using AMO?

Thank you so much,

Sincerely,

Annie

This may help:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=727220&SiteID=17

Also, if you install the SQL Server Samples, the following path will have a bit of a more comprehensive example of creating a DSV. Though it doesn't create any named queries, it may be helpful:

C:\program files\Microsoft SQL Server\90\Samples\Analysis Services\Programmability\AMO\AMOAdventureWorks\CS\AmoAdventureWorks

Creating DB using command line

Hi All,

I was given a task of coming up with the script to recreate an existing database using a command line. I would use this script in case when the server is down and I can't get to Query Analyzer or EM to recreate it. I am not sure where to start. Any ideas are greatly appreciated.

Thanks.If the server is down, how would you be able to create a database? Other than that, look up the OSQL utility.|||I'm assuming they have another server with sql server installed...

And you should have sql server client tools installed on your desktop so you can connect to any server...no need to log on to a particular box

And how do you want o create a "new" database?

I'm assuming from a backup...is the backup copied to another box?

If not, then when the server is down, you are sol|||It is a clustered SQL Server, so dev will stand for prod. Yes, the backup is being copied to a drive on a dev box.|||OK, so create a *.sql file that does the restore and either create a bat file, or type this in the command line

osql /U <login> /P <password>/i <filename>.sql

simple enough|||Yes it is. Thanks a lot.

Creating Database Update Scripts

I created an application based on an existing database, I made a lot of changes to the database, now I need to create scripts in the new database to use to update the old database. For example, I added 15 fields to 1 of the tables. Is there a way to use Tasks -> Generate Scripts to create a script that will check the existance of each column and create it if it does not exist? I tried multiple ways of doing this, but it will only create columns for tables that do not exist in the old database. If a table exists, none of the new columns are added.

I may be going about doing this wrong. The main goal I am tying to accomplish is to get all of the data that is in the old database (It was in use while the new one was being developed so there is a lot of data in the old database that I need to have in the new one) into the new one.

Am I better off creating a blank database, then exporting all of the data from the old database to the new one? Will this create any problems with my Primary Keys (The are all auto-increment Integers)?

The customary method would be to drop the existing table and add a new one with the correct structure, then restore data to it. But if your goal is really just to get the old data into the new database, a DTS job would do this or a simple SQL UPDATE query with the proper WHERE clause should work.

Jeff

|||The most reliable way to move a database is backup the old database and then restore it as the new database with the new name. Hope this helps.|||

The new database has a lot of new fields in it so restoring the backup would not work.

If I export the data, would I be able to import the data into the new database? All of the fields from the old database still exist, and would doing this change the auto increment Integer Primary Keys? and would doing this cause duplicate keys or is the database smart enough to see that they have been used?

|||If you restore the old database you have only the differences to move to the restored database but you can still use DTS to move the old database. In SQL Server IDENTITY is a property and you can drop it and set it with ALTER TABLE. Run a search for IDENTITY all in SQL Server BOL (books online). Hope this helps.