I have a hopefully easy question that I cannot find the answer to anywhere.
I am attempting to automate the creation of an anonymous pull subscription.
I have created the subscription on one machine, and then generated a script
using Enterprise Manager to create the subscription.
When I run the subscription script, the subscription is created correctly.
However, when I run the job created to do the merge, it always fails because
the process running on the subscriber could not connect to the distributor
because of a login failure.
In the generated script, I have seen that there is an option for
SubscriberEncryptedPassword. I have generated the script in Unicode mode,
and then looking at the script in WordPad shows some non-ASCII characters as
the contents of that password. I'm assuming that the problem has to do with
this password.
I am using SQL Server authentication. Does anyone know what the problem is,
or where I should look to find the answer? I haven't been successful in any
of my searches.
Thanks for any help,
Ryan
do not use this parameter. SQL will encrypt the password you specify in the
@.distributor_password and @.publisher_password (if you specify this)
parameters.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Ryan McFall" <mcfall@.hope.edu> wrote in message
news:%23bCSNfdhEHA.644@.tk2msftngp13.phx.gbl...
> I have a hopefully easy question that I cannot find the answer to
anywhere.
> I am attempting to automate the creation of an anonymous pull
subscription.
> I have created the subscription on one machine, and then generated a
script
> using Enterprise Manager to create the subscription.
> When I run the subscription script, the subscription is created correctly.
> However, when I run the job created to do the merge, it always fails
because
> the process running on the subscriber could not connect to the distributor
> because of a login failure.
> In the generated script, I have seen that there is an option for
> SubscriberEncryptedPassword. I have generated the script in Unicode mode,
> and then looking at the script in WordPad shows some non-ASCII characters
as
> the contents of that password. I'm assuming that the problem has to do
with
> this password.
> I am using SQL Server authentication. Does anyone know what the problem
is,
> or where I should look to find the answer? I haven't been successful in
any
> of my searches.
> Thanks for any help,
> Ryan
>
|||Thanks for the tip. I'm afraid it didn't work, however.
First I edited out the @.subscriberEncryptedPassword parameter completely.
The generated script had specified @.distributor_password=N'' (and the same
for the publisher). I first tried leaving those as they were and ran the
script without the @.subscriberEncryptedPassword parameter. When that didn't
work, I entered in the password for the SQL server account specified by the
@.distributor_login parameter.
In the first case, where the passwords weren't set, I got an error that the
agent was unable to connect to the subscriber. When I entered the
appropriate password for the distributor and publisher and ran the script,
the merge agent fails with "unable to connect to distributor, invalid
password for user annotations (the SQL server account used on the publisher,
distributor and subscriber).
Maybe I'm misunderstanding what you're suggesting?
Ryan
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:egjqd5ghEHA.4064@.TK2MSFTNGP12.phx.gbl...
> do not use this parameter. SQL will encrypt the password you specify in
the[vbcol=seagreen]
> @.distributor_password and @.publisher_password (if you specify this)
> parameters.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Ryan McFall" <mcfall@.hope.edu> wrote in message
> news:%23bCSNfdhEHA.644@.tk2msftngp13.phx.gbl...
> anywhere.
> subscription.
> script
correctly.[vbcol=seagreen]
> because
distributor[vbcol=seagreen]
mode,[vbcol=seagreen]
characters
> as
> with
> is,
> any
>
Showing posts with label enterprise. Show all posts
Showing posts with label enterprise. Show all posts
Tuesday, March 20, 2012
Monday, March 19, 2012
Creating SQLServer Jobs in Enterprise Manager
I would like to create a job in Enterprise manager that runs sp_who2
and write the output to a file. The filename would need to be appended
with a timestamp that makes the file unique. I can then write another
job that will cleanup the directory by deleting files that are older
than a specified date. This is a very simple process in unix platform.
I am hoping that it can be done in Windows but I don't know how?
and write the output to a file. The filename would need to be appended
with a timestamp that makes the file unique. I can then write another
job that will cleanup the directory by deleting files that are older
than a specified date. This is a very simple process in unix platform.
I am hoping that it can be done in Windows but I don't know how?
Any help will be applicicated..You can send the output of a job step directly to a file (see the
Advanced tab), but there's no easy way to set the file name
dynamically. You might be able to use sp_update_jobstep to set it at
runtime, but then you would still have to do the cleanup part, which
would mean using xp_cmdshell.
Personally, I would use a script in a langauge like VBScript, Perl or
Python to do this:
1. Work out the correct filename
2. Call osql.exe to get the output into the file (see the -Q and -o
options)
3. Clean up any old files
You can then call the script from a job step - it's much easier to
manipulate files outside the database.
Simon
Sunday, March 11, 2012
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.
>
>
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.
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.
>
>
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.
>
>
Thursday, March 8, 2012
Creating relationships in asp.net enterprise manager
Hello
I am using a web hosting company for my web site, unfortunately after developing the site with server express the company told me they do not support this. As the database only holds three tables and only contained test data, this was not too much of a problem and I thought I would rebuild the databse on the development tool (ASP.net enterprise manager). I have managed to create the tables and populate them with the required data. My only problem is the user interface of the enterprise manager is not the friendliest and I am not too sure how to create the relationships between the tables. The interface only seems to have facilities for creating tables, views, stored procedures, users and roles.
Any tips on how I can create relationships between the tables.
Graeme
In SQL Enterprise Manager right-click on a table, and select Design. A design window will open from the table. What you want is on the SQL Enterprise main window (parent window) - the 3 icons on the right: Manage Constraints, Manage Indexes, and Manage Relationships. clicking on anyone of them opens the same dialog, which lets you manage any of the three.
Good luck.
|||Hi Alex
I think the interface the webhosting company allows me to use is not the actual program as it does not allow right clicking; all the images and tables etc seem to be hyperlinks. I am trying to get around this by hardcoding the tables in SQL and then running the queries.
|||Hi,
From your description, it seems that you can't use some functions of enterprise manager in the website based enterprise manager they provided, right?
Right, actually, the website based enterprise manager is a web application which runs the underlying SQL command for your specific operations. (such as create,drop tables, insert records and etc..) And the function you can use is totally based on your hosters, since it's not a real Enterprise Manager of SQLServer. So as you mentioned, if the interface only seems to have facilities for creating tables, views, stored procedures, users and roles, but not for creating relations, you have to handle it in another way.
Based on my understanding, lot's of hosters are support remote connections. So you may connect to the database instance on your hoster's server by IP address and userid, passwords. And then export the data on your local instance to the remote instance, or you can just create your tables on remote instance directly and handle the relation by the real Enterprise Manger tool.
Thanks.
Wednesday, March 7, 2012
Creating New Views on SQL 2000
Recently we upgraded our server from SQL 7.0 to SQL 2000. I created a
new view in Enterprise Manager which pulls data from a linked server's
database. When I write the view...
new view in Enterprise Manager which pulls data from a linked server's
database. When I write the view...
CREATE VIEW dbo.VIEW1
AS
SELECT *
FROM LinkedServerName.DatabaseName.dbo.TableName
and I click on the run button it changes to:
CREATE VIEW dbo.VIEW1
AS
SELECT *
From LinkedServerName.DatabaseName.dbo.TableName TableName_1
I can still see the correct results, but the users can't- Once I
remove the TableName_1 they can see the results.
When I tried creating a view and did not use a linked server,
everything worked fine. Anyone have any answers?RJMAL (rjmal@.connecticare.com) writes:
> Recently we upgraded our server from SQL 7.0 to SQL 2000. I created a
> new view in Enterprise Manager which pulls data from a linked server's
> database. When I write the view...
> CREATE VIEW dbo.VIEW1
> AS
> SELECT *
> FROM LinkedServerName.DatabaseName.dbo.TableName
> and I click on the run button it changes to:
> CREATE VIEW dbo.VIEW1
> AS
> SELECT *
> From LinkedServerName.DatabaseName.dbo.TableName TableName_1
> I can still see the correct results, but the users can't- Once I
> remove the TableName_1 they can see the results.
> When I tried creating a view and did not use a linked server,
> everything worked fine. Anyone have any answers?
I was not able to reproduces this, because I was not even able to create
a view that accesses a linked server through Enterprise Manager. I came
as far that I saw the added alias, though.
Really why Enterprise Manager adds the alias, I don't know, but neither
do I understand why the alias would affect the common users. You say
they don't see the correct results - but what do they see? An error
message? No rows at all? Too few rows? Garbled data? From what sort of
application, to the users access the view?
Anyway, try using Query Analyzer to create the view instead. In my
opinion, Enterprise Manager plays too much behind your back to be
trustworthy.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Saturday, February 25, 2012
creating new tables on diff file group using EM
Hi,
How do i acutally create a new table that i can specify to a different
file group using Enterprise Manager ?
this is how i done in TSQL :
create table test
( myname char(20)
)
on MYOWNFILEGROUP
appreciate any advice
tks & rdgs
In Enterprise Manager, Right Click on "Tables" node and select "New Table"
from the context menu. Click on "Table and Index Properties" button (Second
button from left to right) and use "Table Filegroup" dropdown list to select
"MYOWNFILEGROUP".
Cristian Lefter, SQL Server MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:D90E63AB-F3AC-426F-875D-E8CB71B6189C@.microsoft.com...
> Hi,
> How do i acutally create a new table that i can specify to a different
> file group using Enterprise Manager ?
> this is how i done in TSQL :
> create table test
> ( myname char(20)
> )
> on MYOWNFILEGROUP
> appreciate any advice
> tks & rdgs
|||tks Cristian , i got it
"Cristian Lefter" wrote:
> In Enterprise Manager, Right Click on "Tables" node and select "New Table"
> from the context menu. Click on "Table and Index Properties" button (Second
> button from left to right) and use "Table Filegroup" dropdown list to select
> "MYOWNFILEGROUP".
> Cristian Lefter, SQL Server MVP
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:D90E63AB-F3AC-426F-875D-E8CB71B6189C@.microsoft.com...
>
>
How do i acutally create a new table that i can specify to a different
file group using Enterprise Manager ?
this is how i done in TSQL :
create table test
( myname char(20)
)
on MYOWNFILEGROUP
appreciate any advice
tks & rdgs
In Enterprise Manager, Right Click on "Tables" node and select "New Table"
from the context menu. Click on "Table and Index Properties" button (Second
button from left to right) and use "Table Filegroup" dropdown list to select
"MYOWNFILEGROUP".
Cristian Lefter, SQL Server MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:D90E63AB-F3AC-426F-875D-E8CB71B6189C@.microsoft.com...
> Hi,
> How do i acutally create a new table that i can specify to a different
> file group using Enterprise Manager ?
> this is how i done in TSQL :
> create table test
> ( myname char(20)
> )
> on MYOWNFILEGROUP
> appreciate any advice
> tks & rdgs
|||tks Cristian , i got it
"Cristian Lefter" wrote:
> In Enterprise Manager, Right Click on "Tables" node and select "New Table"
> from the context menu. Click on "Table and Index Properties" button (Second
> button from left to right) and use "Table Filegroup" dropdown list to select
> "MYOWNFILEGROUP".
> Cristian Lefter, SQL Server MVP
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:D90E63AB-F3AC-426F-875D-E8CB71B6189C@.microsoft.com...
>
>
creating new tables on diff file group using EM
Hi,
How do i acutally create a new table that i can specify to a different
file group using Enterprise Manager ?
this is how i done in TSQL :
create table test
( myname char(20)
)
on MYOWNFILEGROUP
appreciate any advice
tks & rdgsIn Enterprise Manager, Right Click on "Tables" node and select "New Table"
from the context menu. Click on "Table and Index Properties" button (Second
button from left to right) and use "Table Filegroup" dropdown list to select
"MYOWNFILEGROUP".
Cristian Lefter, SQL Server MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:D90E63AB-F3AC-426F-875D-E8CB71B6189C@.microsoft.com...
> Hi,
> How do i acutally create a new table that i can specify to a different
> file group using Enterprise Manager ?
> this is how i done in TSQL :
> create table test
> ( myname char(20)
> )
> on MYOWNFILEGROUP
> appreciate any advice
> tks & rdgs|||tks Cristian , i got it
"Cristian Lefter" wrote:
> In Enterprise Manager, Right Click on "Tables" node and select "New Table"
> from the context menu. Click on "Table and Index Properties" button (Second
> button from left to right) and use "Table Filegroup" dropdown list to select
> "MYOWNFILEGROUP".
> Cristian Lefter, SQL Server MVP
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:D90E63AB-F3AC-426F-875D-E8CB71B6189C@.microsoft.com...
> > Hi,
> >
> > How do i acutally create a new table that i can specify to a different
> > file group using Enterprise Manager ?
> >
> > this is how i done in TSQL :
> >
> > create table test
> > ( myname char(20)
> > )
> > on MYOWNFILEGROUP
> >
> > appreciate any advice
> >
> > tks & rdgs
>
>
How do i acutally create a new table that i can specify to a different
file group using Enterprise Manager ?
this is how i done in TSQL :
create table test
( myname char(20)
)
on MYOWNFILEGROUP
appreciate any advice
tks & rdgsIn Enterprise Manager, Right Click on "Tables" node and select "New Table"
from the context menu. Click on "Table and Index Properties" button (Second
button from left to right) and use "Table Filegroup" dropdown list to select
"MYOWNFILEGROUP".
Cristian Lefter, SQL Server MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:D90E63AB-F3AC-426F-875D-E8CB71B6189C@.microsoft.com...
> Hi,
> How do i acutally create a new table that i can specify to a different
> file group using Enterprise Manager ?
> this is how i done in TSQL :
> create table test
> ( myname char(20)
> )
> on MYOWNFILEGROUP
> appreciate any advice
> tks & rdgs|||tks Cristian , i got it
"Cristian Lefter" wrote:
> In Enterprise Manager, Right Click on "Tables" node and select "New Table"
> from the context menu. Click on "Table and Index Properties" button (Second
> button from left to right) and use "Table Filegroup" dropdown list to select
> "MYOWNFILEGROUP".
> Cristian Lefter, SQL Server MVP
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:D90E63AB-F3AC-426F-875D-E8CB71B6189C@.microsoft.com...
> > Hi,
> >
> > How do i acutally create a new table that i can specify to a different
> > file group using Enterprise Manager ?
> >
> > this is how i done in TSQL :
> >
> > create table test
> > ( myname char(20)
> > )
> > on MYOWNFILEGROUP
> >
> > appreciate any advice
> >
> > tks & rdgs
>
>
creating new tables on diff file group using EM
Hi,
How do i acutally create a new table that i can specify to a different
file group using Enterprise Manager ?
this is how i done in TSQL :
create table test
( myname char(20)
)
on MYOWNFILEGROUP
appreciate any advice
tks & rdgsIn Enterprise Manager, Right Click on "Tables" node and select "New Table"
from the context menu. Click on "Table and Index Properties" button (Second
button from left to right) and use "Table Filegroup" dropdown list to select
"MYOWNFILEGROUP".
Cristian Lefter, SQL Server MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:D90E63AB-F3AC-426F-875D-E8CB71B6189C@.microsoft.com...
> Hi,
> How do i acutally create a new table that i can specify to a different
> file group using Enterprise Manager ?
> this is how i done in TSQL :
> create table test
> ( myname char(20)
> )
> on MYOWNFILEGROUP
> appreciate any advice
> tks & rdgs|||tks Cristian , i got it
"Cristian Lefter" wrote:
> In Enterprise Manager, Right Click on "Tables" node and select "New Table"
> from the context menu. Click on "Table and Index Properties" button (Secon
d
> button from left to right) and use "Table Filegroup" dropdown list to sele
ct
> "MYOWNFILEGROUP".
> Cristian Lefter, SQL Server MVP
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:D90E63AB-F3AC-426F-875D-E8CB71B6189C@.microsoft.com...
>
>
How do i acutally create a new table that i can specify to a different
file group using Enterprise Manager ?
this is how i done in TSQL :
create table test
( myname char(20)
)
on MYOWNFILEGROUP
appreciate any advice
tks & rdgsIn Enterprise Manager, Right Click on "Tables" node and select "New Table"
from the context menu. Click on "Table and Index Properties" button (Second
button from left to right) and use "Table Filegroup" dropdown list to select
"MYOWNFILEGROUP".
Cristian Lefter, SQL Server MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:D90E63AB-F3AC-426F-875D-E8CB71B6189C@.microsoft.com...
> Hi,
> How do i acutally create a new table that i can specify to a different
> file group using Enterprise Manager ?
> this is how i done in TSQL :
> create table test
> ( myname char(20)
> )
> on MYOWNFILEGROUP
> appreciate any advice
> tks & rdgs|||tks Cristian , i got it
"Cristian Lefter" wrote:
> In Enterprise Manager, Right Click on "Tables" node and select "New Table"
> from the context menu. Click on "Table and Index Properties" button (Secon
d
> button from left to right) and use "Table Filegroup" dropdown list to sele
ct
> "MYOWNFILEGROUP".
> Cristian Lefter, SQL Server MVP
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:D90E63AB-F3AC-426F-875D-E8CB71B6189C@.microsoft.com...
>
>
Friday, February 24, 2012
Creating Linked Server
Hello there
On my enterprise manager there are many Servers who are registered
I would like to transfare data between these two servers
When i'm conneted to one server on the query analyser and i'm trying to see
table from other server i get an error: Could not find server 'ROY-XP' in
sysservers. Execute sp_addlinkedserver to add the server to sysservers.
What i need to do in order to connect between these two servers?Roy
You can use EM to create linked server. Just provide a login and password
that can access the database.
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:eaj6jZa9FHA.3660@.TK2MSFTNGP09.phx.gbl...
> Hello there
> On my enterprise manager there are many Servers who are registered
> I would like to transfare data between these two servers
> When i'm conneted to one server on the query analyser and i'm trying to
> see table from other server i get an error: Could not find server 'ROY-XP'
> in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
> What i need to do in order to connect between these two servers?
>
>|||Hi,
sp_addlinkedserver -- map server with this
and
sp_addlinkedsrvlogin -- map user account with this
:-)
Regards
--
Andy Davis
Activecrypt Team
---
SQL Server Encryption Software
http://www.activecrypt.com
"Roy Goldhammer" wrote:
> Hello there
> On my enterprise manager there are many Servers who are registered
> I would like to transfare data between these two servers
> When i'm conneted to one server on the query analyser and i'm trying to se
e
> table from other server i get an error: Could not find server 'ROY-XP' in
> sysservers. Execute sp_addlinkedserver to add the server to sysservers.
> What i need to do in order to connect between these two servers?
>
>
On my enterprise manager there are many Servers who are registered
I would like to transfare data between these two servers
When i'm conneted to one server on the query analyser and i'm trying to see
table from other server i get an error: Could not find server 'ROY-XP' in
sysservers. Execute sp_addlinkedserver to add the server to sysservers.
What i need to do in order to connect between these two servers?Roy
You can use EM to create linked server. Just provide a login and password
that can access the database.
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:eaj6jZa9FHA.3660@.TK2MSFTNGP09.phx.gbl...
> Hello there
> On my enterprise manager there are many Servers who are registered
> I would like to transfare data between these two servers
> When i'm conneted to one server on the query analyser and i'm trying to
> see table from other server i get an error: Could not find server 'ROY-XP'
> in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
> What i need to do in order to connect between these two servers?
>
>|||Hi,
sp_addlinkedserver -- map server with this
and
sp_addlinkedsrvlogin -- map user account with this
:-)
Regards
--
Andy Davis
Activecrypt Team
---
SQL Server Encryption Software
http://www.activecrypt.com
"Roy Goldhammer" wrote:
> Hello there
> On my enterprise manager there are many Servers who are registered
> I would like to transfare data between these two servers
> When i'm conneted to one server on the query analyser and i'm trying to se
e
> table from other server i get an error: Could not find server 'ROY-XP' in
> sysservers. Execute sp_addlinkedserver to add the server to sysservers.
> What i need to do in order to connect between these two servers?
>
>
Sunday, February 19, 2012
Creating indexes with ONLINE = ON - failing
Hi list
Did a search on all SQL related newsgroups, but didn't find anything.
So, here's my problem:
We are running SQL 2005 Enterprise edition.
SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00 (Inte
l X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)".
We want to rebuild our indexes on 2 tables with the ONLINE option set to ON.
They are currently set to OFF. We tried everything that came across our min
ds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename> REBUILD WI
TH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
Apparently, the statement executes with no errors, but index is not created
if ONLINE set to ON. When scritping the indexes or right-clicking and then P
roperties, they all appear with ONLINE = OFF.
We also tried to isolate the problem, by creating a fresh new database, a fr
esh new table and then play around with indexes. Same outcome.
Also tested this on a developer edition, with same outcome.
Table doesn't have any LOB columns (text, ntext, image, varchar(max), nvarch
ar(max)).
Any ideas, welcome.
Thanks in advance for any thoughts.
Regards,
AlinCan you post the actual DDL (including indexes) and the ALTER INDEX statemen
t you used?
--
Andrew J. Kelly SQL MVP
"Alin Selicean" <alin.nomail..selicean@.nospam.gmail.com> wrote in message ne
ws:%23f7250C2HHA.5160@.TK2MSFTNGP05.phx.gbl...
Hi list
Did a search on all SQL related newsgroups, but didn't find anything.
So, here's my problem:
We are running SQL 2005 Enterprise edition.
SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00 (Inte
l X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)".
We want to rebuild our indexes on 2 tables with the ONLINE option set to ON.
They are currently set to OFF. We tried everything that came across our min
ds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename> REBUILD WI
TH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
Apparently, the statement executes with no errors, but index is not created
if ONLINE set to ON. When scritping the indexes or right-clicking and then P
roperties, they all appear with ONLINE = OFF.
We also tried to isolate the problem, by creating a fresh new database, a fr
esh new table and then play around with indexes. Same outcome.
Also tested this on a developer edition, with same outcome.
Table doesn't have any LOB columns (text, ntext, image, varchar(max), nvarch
ar(max)).
Any ideas, welcome.
Thanks in advance for any thoughts.
Regards,
Alin|||Hi Andrew
Thank you for your time.
We tried the following:
1. Manually creating the index and checking the Allow online processing of D
ML statements...
2. The following DDL statements:
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo]
.[table1]') AND name = N'index1')DROP INDEX [index1] ON [dbo].
[table1]
CREATE NONCLUSTERED INDEX [index1] ON [dbo].[table1]
([field1] ASC,[field2] ASC,[field3] ASC,[field4] ASC,[fi
eld5] ASC,[field6] ASC)
WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP
_KEY = OFF, ONLINE = ON) ON [PRIMARY]
3. We tried the above statement with only ONLINE = ON option, to avoid possi
ble conflicts with backward compatibility keywords (as stated in BOL).
4. We also tried ALTER INDEX ALL ON dbo.table1 REBUILD WITH (ONLINE = ON);
Database compatibility level is 90.
All of the above with the same result.
Thanks.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:etQdu%
23C2HHA.5884@.TK2MSFTNGP02.phx.gbl...
Can you post the actual DDL (including indexes) and the ALTER INDEX statemen
t you used?
--
Andrew J. Kelly SQL MVP
"Alin Selicean" <alin.nomail..selicean@.nospam.gmail.com> wrote in message ne
ws:%23f7250C2HHA.5160@.TK2MSFTNGP05.phx.gbl...
Hi list
Did a search on all SQL related newsgroups, but didn't find anything.
So, here's my problem:
We are running SQL 2005 Enterprise edition.
SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00 (Inte
l X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)".
We want to rebuild our indexes on 2 tables with the ONLINE option set to ON.
They are currently set to OFF. We tried everything that came across our min
ds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename> REBUILD WI
TH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
Apparently, the statement executes with no errors, but index is not created
if ONLINE set to ON. When scritping the indexes or right-clicking and then P
roperties, they all appear with ONLINE = OFF.
We also tried to isolate the problem, by creating a fresh new database, a fr
esh new table and then play around with indexes. Same outcome.
Also tested this on a developer edition, with same outcome.
Table doesn't have any LOB columns (text, ntext, image, varchar(max), nvarch
ar(max)).
Any ideas, welcome.
Thanks in advance for any thoughts.
Regards,
Alin|||We also have a clustered index on that table, we also tried to recreate that
index with ONLINE = ON, BOL stating that if a clustered index is created wi
th ONLINE = ON, all non-clustered indexes will be rebuilt with ONLINE = ON.
Same outcome.
Thx again.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:etQdu%
23C2HHA.5884@.TK2MSFTNGP02.phx.gbl...
Can you post the actual DDL (including indexes) and the ALTER INDEX statemen
t you used?
--
Andrew J. Kelly SQL MVP
"Alin Selicean" <alin.nomail..selicean@.nospam.gmail.com> wrote in message ne
ws:%23f7250C2HHA.5160@.TK2MSFTNGP05.phx.gbl...
Hi list
Did a search on all SQL related newsgroups, but didn't find anything.
So, here's my problem:
We are running SQL 2005 Enterprise edition.
SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00 (Inte
l X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)".
We want to rebuild our indexes on 2 tables with the ONLINE option set to ON.
They are currently set to OFF. We tried everything that came across our min
ds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename> REBUILD WI
TH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
Apparently, the statement executes with no errors, but index is not created
if ONLINE set to ON. When scritping the indexes or right-clicking and then P
roperties, they all appear with ONLINE = OFF.
We also tried to isolate the problem, by creating a fresh new database, a fr
esh new table and then play around with indexes. Same outcome.
Also tested this on a developer edition, with same outcome.
Table doesn't have any LOB columns (text, ntext, image, varchar(max), nvarch
ar(max)).
Any ideas, welcome.
Thanks in advance for any thoughts.
Regards,
Alin|||I think there is a general bug somewhere... this is not the only option that
gets forgotten (it also happens with SORT_IN_TEMPB). I noticed this a while
ago, but at the time did not catch the online option as well. You've
spurred me to finally enter a bug report after retrieving my notes from back
then:
http://connect.microsoft.com/SQLSer...=29128
8
Aaron Bertrand
SQL Server MVP
"Alin Selicean" <alin.nomail..selicean@.nospam.gmail.com> wrote in message
news:%23f7250C2HHA.5160@.TK2MSFTNGP05.phx.gbl...
Hi list
Did a search on all SQL related newsgroups, but didn't find anything.
So, here's my problem:
We are running SQL 2005 Enterprise edition.
SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00
(Intel X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack
1)".
We want to rebuild our indexes on 2 tables with the ONLINE option set to ON.
They are currently set to OFF. We tried everything that came across our
minds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename> REBUILD
WITH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
Apparently, the statement executes with no errors, but index is not created
if ONLINE set to ON. When scritping the indexes or right-clicking and then
Properties, they all appear with ONLINE = OFF.
We also tried to isolate the problem, by creating a fresh new database, a
fresh new table and then play around with indexes. Same outcome.
Also tested this on a developer edition, with same outcome.
Table doesn't have any LOB columns (text, ntext, image, varchar(max),
nvarchar(max)).
Any ideas, welcome.
Thanks in advance for any thoughts.
Regards,
Alin|||Thank you, Aaron, for your feedback. Reviewing your post on connect, I am
not sure if index is created with online = on, as it doesn't came up this
way when querying sys.indexes (with all the joins towards sys.index_columns
and sys.columns) or at least I didn't saw that option listed anywhere.
So my guess is that somehow that that option (along with many more, as you
also noticed) is not saved at all. Furthermore, I cannot be 100% sure that
the index is created as stated in my statement. So I think that there is no
workaround available. One can only assume that the index is created as one
expected, but there is no way to get a confirmation.
I will also test on Katmai tomorrow and post my findings on connect.
Thanks for the heads up related to Katmai.
Regards,
Alin
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:%23z9%23CgD2HHA.3760@.TK2MSFTNGP03.phx.gbl...
>I think there is a general bug somewhere... this is not the only option
>that gets forgotten (it also happens with SORT_IN_TEMPB). I noticed this a
>while ago, but at the time did not catch the online option as well. You've
>spurred me to finally enter a bug report after retrieving my notes from
>back then:
> http://connect.microsoft.com/SQLSer...=291
288
> --
> Aaron Bertrand
> SQL Server MVP
>
>
>
> "Alin Selicean" <alin.nomail..selicean@.nospam.gmail.com> wrote in message
> news:%23f7250C2HHA.5160@.TK2MSFTNGP05.phx.gbl...
> Hi list
> Did a search on all SQL related newsgroups, but didn't find anything.
> So, here's my problem:
> We are running SQL 2005 Enterprise edition.
> SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00
> (Intel X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack
> 1)".
> We want to rebuild our indexes on 2 tables with the ONLINE option set to
> ON. They are currently set to OFF. We tried everything that came across
> our minds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename>
> REBUILD WITH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
> Apparently, the statement executes with no errors, but index is not
> created if ONLINE set to ON. When scritping the indexes or right-clicking
> and then Properties, they all appear with ONLINE = OFF.
> We also tried to isolate the problem, by creating a fresh new database, a
> fresh new table and then play around with indexes. Same outcome.
> Also tested this on a developer edition, with same outcome.
> Table doesn't have any LOB columns (text, ntext, image, varchar(max),
> nvarchar(max)).
> Any ideas, welcome.
> Thanks in advance for any thoughts.
> Regards,
> Alin
>|||I guess having those options during the CREATE INDEX statement only affect
the initial creation of the index, and do not represent any long-term
setting that "sticks."
My guess is that it will come back as "by design." E.g. you can't specify
up front that every time you change the index, those settings will be true.
If you want to rebuild the index and rebuild it online, you will need to
specify that in the ALTER INDEX command.
Then again, if I want to script the exact CREATE INDEX statement that I ran
yesterday, I can't see what harm it would have to maintain the properties
with the index as it was created, even if you will still need to specify
those options explicitly when using ALTER. Right now it doesn't seem
possible using the tools to re-generate the exact same CREATE INDEX script
that I ran yesterday...
Aaron Bertrand
SQL Server MVP
"Alin Selicean" <alin.nomail.selicean@.nomail.gmail.com> wrote in message
news:eZjKdUE2HHA.4680@.TK2MSFTNGP03.phx.gbl...
> Thank you, Aaron, for your feedback. Reviewing your post on connect, I am
> not sure if index is created with online = on, as it doesn't came up this
> way when querying sys.indexes (with all the joins towards
> sys.index_columns and sys.columns) or at least I didn't saw that option
> listed anywhere.
> So my guess is that somehow that that option (along with many more, as you
> also noticed) is not saved at all. Furthermore, I cannot be 100% sure that
> the index is created as stated in my statement. So I think that there is
> no workaround available. One can only assume that the index is created as
> one expected, but there is no way to get a confirmation.
> I will also test on Katmai tomorrow and post my findings on connect.
> Thanks for the heads up related to Katmai.
> Regards,
> Alin|||I agree, but I still wondering why this option is not reflected in the index
properties, when right-click the index, then on properties.
My feeling is that this is a bug in the UI. Also, you cannot retrieve this
piece of information from any of the DMV's that shows any index options. Or
maybe it's just me
, I don't know. I will further test it tomorrow, but as
of today, these are my conclusions (including your opinions on this).
I will come back with more news tomorrow.
Again, thanks for your efforts.
Regards,
Alin Selicean
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:eELWJeE2HHA.5772@.TK2MSFTNGP02.phx.gbl...
>I guess having those options during the CREATE INDEX statement only affect
>the initial creation of the index, and do not represent any long-term
>setting that "sticks."
> My guess is that it will come back as "by design." E.g. you can't specify
> up front that every time you change the index, those settings will be
> true. If you want to rebuild the index and rebuild it online, you will
> need to specify that in the ALTER INDEX command.
> Then again, if I want to script the exact CREATE INDEX statement that I
> ran yesterday, I can't see what harm it would have to maintain the
> properties with the index as it was created, even if you will still need
> to specify those options explicitly when using ALTER. Right now it
> doesn't seem possible using the tools to re-generate the exact same CREATE
> INDEX script that I ran yesterday...
> --
> Aaron Bertrand
> SQL Server MVP
>
>
> "Alin Selicean" <alin.nomail.selicean@.nomail.gmail.com> wrote in message
> news:eZjKdUE2HHA.4680@.TK2MSFTNGP03.phx.gbl...
>|||>I agree, but I still wondering why this option is not reflected in the
>index properties, when right-click the index, then on properties.
Because I don't think it's a "permanent" property, it is merely describing
how it should initially be created. Once the index is created, they're
going to argue that whether it was created online or not last week is not a
permanent property of the index. It's kind of like driving a car off the
lot with premium fuel, and then later expecting the car to know it should
only accept premium fuel.
I agree somewhat with your sentiments that this information should be
preserved somehow, but it is more for the purposes of re-generation of
identical scripts, rather than any property I expect the index to maintain.
A|||> permanent property of the index. It's kind of like driving a car off the
> lot with premium fuel, and then later expecting the car to know it should
> only accept premium fuel.
A better analogy would be expecting the car to somehow know that it at one
time used premium fuel.
A
Did a search on all SQL related newsgroups, but didn't find anything.
So, here's my problem:
We are running SQL 2005 Enterprise edition.
SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00 (Inte
l X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)".
We want to rebuild our indexes on 2 tables with the ONLINE option set to ON.
They are currently set to OFF. We tried everything that came across our min
ds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename> REBUILD WI
TH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
Apparently, the statement executes with no errors, but index is not created
if ONLINE set to ON. When scritping the indexes or right-clicking and then P
roperties, they all appear with ONLINE = OFF.
We also tried to isolate the problem, by creating a fresh new database, a fr
esh new table and then play around with indexes. Same outcome.
Also tested this on a developer edition, with same outcome.
Table doesn't have any LOB columns (text, ntext, image, varchar(max), nvarch
ar(max)).
Any ideas, welcome.
Thanks in advance for any thoughts.
Regards,
AlinCan you post the actual DDL (including indexes) and the ALTER INDEX statemen
t you used?
--
Andrew J. Kelly SQL MVP
"Alin Selicean" <alin.nomail..selicean@.nospam.gmail.com> wrote in message ne
ws:%23f7250C2HHA.5160@.TK2MSFTNGP05.phx.gbl...
Hi list
Did a search on all SQL related newsgroups, but didn't find anything.
So, here's my problem:
We are running SQL 2005 Enterprise edition.
SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00 (Inte
l X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)".
We want to rebuild our indexes on 2 tables with the ONLINE option set to ON.
They are currently set to OFF. We tried everything that came across our min
ds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename> REBUILD WI
TH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
Apparently, the statement executes with no errors, but index is not created
if ONLINE set to ON. When scritping the indexes or right-clicking and then P
roperties, they all appear with ONLINE = OFF.
We also tried to isolate the problem, by creating a fresh new database, a fr
esh new table and then play around with indexes. Same outcome.
Also tested this on a developer edition, with same outcome.
Table doesn't have any LOB columns (text, ntext, image, varchar(max), nvarch
ar(max)).
Any ideas, welcome.
Thanks in advance for any thoughts.
Regards,
Alin|||Hi Andrew
Thank you for your time.
We tried the following:
1. Manually creating the index and checking the Allow online processing of D
ML statements...
2. The following DDL statements:
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo]
.[table1]') AND name = N'index1')DROP INDEX [index1] ON [dbo].
[table1]
CREATE NONCLUSTERED INDEX [index1] ON [dbo].[table1]
([field1] ASC,[field2] ASC,[field3] ASC,[field4] ASC,[fi
eld5] ASC,[field6] ASC)
WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP
_KEY = OFF, ONLINE = ON) ON [PRIMARY]
3. We tried the above statement with only ONLINE = ON option, to avoid possi
ble conflicts with backward compatibility keywords (as stated in BOL).
4. We also tried ALTER INDEX ALL ON dbo.table1 REBUILD WITH (ONLINE = ON);
Database compatibility level is 90.
All of the above with the same result.
Thanks.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:etQdu%
23C2HHA.5884@.TK2MSFTNGP02.phx.gbl...
Can you post the actual DDL (including indexes) and the ALTER INDEX statemen
t you used?
--
Andrew J. Kelly SQL MVP
"Alin Selicean" <alin.nomail..selicean@.nospam.gmail.com> wrote in message ne
ws:%23f7250C2HHA.5160@.TK2MSFTNGP05.phx.gbl...
Hi list
Did a search on all SQL related newsgroups, but didn't find anything.
So, here's my problem:
We are running SQL 2005 Enterprise edition.
SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00 (Inte
l X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)".
We want to rebuild our indexes on 2 tables with the ONLINE option set to ON.
They are currently set to OFF. We tried everything that came across our min
ds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename> REBUILD WI
TH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
Apparently, the statement executes with no errors, but index is not created
if ONLINE set to ON. When scritping the indexes or right-clicking and then P
roperties, they all appear with ONLINE = OFF.
We also tried to isolate the problem, by creating a fresh new database, a fr
esh new table and then play around with indexes. Same outcome.
Also tested this on a developer edition, with same outcome.
Table doesn't have any LOB columns (text, ntext, image, varchar(max), nvarch
ar(max)).
Any ideas, welcome.
Thanks in advance for any thoughts.
Regards,
Alin|||We also have a clustered index on that table, we also tried to recreate that
index with ONLINE = ON, BOL stating that if a clustered index is created wi
th ONLINE = ON, all non-clustered indexes will be rebuilt with ONLINE = ON.
Same outcome.
Thx again.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:etQdu%
23C2HHA.5884@.TK2MSFTNGP02.phx.gbl...
Can you post the actual DDL (including indexes) and the ALTER INDEX statemen
t you used?
--
Andrew J. Kelly SQL MVP
"Alin Selicean" <alin.nomail..selicean@.nospam.gmail.com> wrote in message ne
ws:%23f7250C2HHA.5160@.TK2MSFTNGP05.phx.gbl...
Hi list
Did a search on all SQL related newsgroups, but didn't find anything.
So, here's my problem:
We are running SQL 2005 Enterprise edition.
SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00 (Inte
l X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)".
We want to rebuild our indexes on 2 tables with the ONLINE option set to ON.
They are currently set to OFF. We tried everything that came across our min
ds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename> REBUILD WI
TH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
Apparently, the statement executes with no errors, but index is not created
if ONLINE set to ON. When scritping the indexes or right-clicking and then P
roperties, they all appear with ONLINE = OFF.
We also tried to isolate the problem, by creating a fresh new database, a fr
esh new table and then play around with indexes. Same outcome.
Also tested this on a developer edition, with same outcome.
Table doesn't have any LOB columns (text, ntext, image, varchar(max), nvarch
ar(max)).
Any ideas, welcome.
Thanks in advance for any thoughts.
Regards,
Alin|||I think there is a general bug somewhere... this is not the only option that
gets forgotten (it also happens with SORT_IN_TEMPB). I noticed this a while
ago, but at the time did not catch the online option as well. You've
spurred me to finally enter a bug report after retrieving my notes from back
then:
http://connect.microsoft.com/SQLSer...=29128
8
Aaron Bertrand
SQL Server MVP
"Alin Selicean" <alin.nomail..selicean@.nospam.gmail.com> wrote in message
news:%23f7250C2HHA.5160@.TK2MSFTNGP05.phx.gbl...
Hi list
Did a search on all SQL related newsgroups, but didn't find anything.
So, here's my problem:
We are running SQL 2005 Enterprise edition.
SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00
(Intel X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack
1)".
We want to rebuild our indexes on 2 tables with the ONLINE option set to ON.
They are currently set to OFF. We tried everything that came across our
minds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename> REBUILD
WITH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
Apparently, the statement executes with no errors, but index is not created
if ONLINE set to ON. When scritping the indexes or right-clicking and then
Properties, they all appear with ONLINE = OFF.
We also tried to isolate the problem, by creating a fresh new database, a
fresh new table and then play around with indexes. Same outcome.
Also tested this on a developer edition, with same outcome.
Table doesn't have any LOB columns (text, ntext, image, varchar(max),
nvarchar(max)).
Any ideas, welcome.
Thanks in advance for any thoughts.
Regards,
Alin|||Thank you, Aaron, for your feedback. Reviewing your post on connect, I am
not sure if index is created with online = on, as it doesn't came up this
way when querying sys.indexes (with all the joins towards sys.index_columns
and sys.columns) or at least I didn't saw that option listed anywhere.
So my guess is that somehow that that option (along with many more, as you
also noticed) is not saved at all. Furthermore, I cannot be 100% sure that
the index is created as stated in my statement. So I think that there is no
workaround available. One can only assume that the index is created as one
expected, but there is no way to get a confirmation.
I will also test on Katmai tomorrow and post my findings on connect.
Thanks for the heads up related to Katmai.
Regards,
Alin
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:%23z9%23CgD2HHA.3760@.TK2MSFTNGP03.phx.gbl...
>I think there is a general bug somewhere... this is not the only option
>that gets forgotten (it also happens with SORT_IN_TEMPB). I noticed this a
>while ago, but at the time did not catch the online option as well. You've
>spurred me to finally enter a bug report after retrieving my notes from
>back then:
> http://connect.microsoft.com/SQLSer...=291
288
> --
> Aaron Bertrand
> SQL Server MVP
>
>
>
> "Alin Selicean" <alin.nomail..selicean@.nospam.gmail.com> wrote in message
> news:%23f7250C2HHA.5160@.TK2MSFTNGP05.phx.gbl...
> Hi list
> Did a search on all SQL related newsgroups, but didn't find anything.
> So, here's my problem:
> We are running SQL 2005 Enterprise edition.
> SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00
> (Intel X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack
> 1)".
> We want to rebuild our indexes on 2 tables with the ONLINE option set to
> ON. They are currently set to OFF. We tried everything that came across
> our minds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename>
> REBUILD WITH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
> Apparently, the statement executes with no errors, but index is not
> created if ONLINE set to ON. When scritping the indexes or right-clicking
> and then Properties, they all appear with ONLINE = OFF.
> We also tried to isolate the problem, by creating a fresh new database, a
> fresh new table and then play around with indexes. Same outcome.
> Also tested this on a developer edition, with same outcome.
> Table doesn't have any LOB columns (text, ntext, image, varchar(max),
> nvarchar(max)).
> Any ideas, welcome.
> Thanks in advance for any thoughts.
> Regards,
> Alin
>|||I guess having those options during the CREATE INDEX statement only affect
the initial creation of the index, and do not represent any long-term
setting that "sticks."
My guess is that it will come back as "by design." E.g. you can't specify
up front that every time you change the index, those settings will be true.
If you want to rebuild the index and rebuild it online, you will need to
specify that in the ALTER INDEX command.
Then again, if I want to script the exact CREATE INDEX statement that I ran
yesterday, I can't see what harm it would have to maintain the properties
with the index as it was created, even if you will still need to specify
those options explicitly when using ALTER. Right now it doesn't seem
possible using the tools to re-generate the exact same CREATE INDEX script
that I ran yesterday...
Aaron Bertrand
SQL Server MVP
"Alin Selicean" <alin.nomail.selicean@.nomail.gmail.com> wrote in message
news:eZjKdUE2HHA.4680@.TK2MSFTNGP03.phx.gbl...
> Thank you, Aaron, for your feedback. Reviewing your post on connect, I am
> not sure if index is created with online = on, as it doesn't came up this
> way when querying sys.indexes (with all the joins towards
> sys.index_columns and sys.columns) or at least I didn't saw that option
> listed anywhere.
> So my guess is that somehow that that option (along with many more, as you
> also noticed) is not saved at all. Furthermore, I cannot be 100% sure that
> the index is created as stated in my statement. So I think that there is
> no workaround available. One can only assume that the index is created as
> one expected, but there is no way to get a confirmation.
> I will also test on Katmai tomorrow and post my findings on connect.
> Thanks for the heads up related to Katmai.
> Regards,
> Alin|||I agree, but I still wondering why this option is not reflected in the index
properties, when right-click the index, then on properties.
My feeling is that this is a bug in the UI. Also, you cannot retrieve this
piece of information from any of the DMV's that shows any index options. Or
maybe it's just me
, I don't know. I will further test it tomorrow, but asof today, these are my conclusions (including your opinions on this).
I will come back with more news tomorrow.
Again, thanks for your efforts.
Regards,
Alin Selicean
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:eELWJeE2HHA.5772@.TK2MSFTNGP02.phx.gbl...
>I guess having those options during the CREATE INDEX statement only affect
>the initial creation of the index, and do not represent any long-term
>setting that "sticks."
> My guess is that it will come back as "by design." E.g. you can't specify
> up front that every time you change the index, those settings will be
> true. If you want to rebuild the index and rebuild it online, you will
> need to specify that in the ALTER INDEX command.
> Then again, if I want to script the exact CREATE INDEX statement that I
> ran yesterday, I can't see what harm it would have to maintain the
> properties with the index as it was created, even if you will still need
> to specify those options explicitly when using ALTER. Right now it
> doesn't seem possible using the tools to re-generate the exact same CREATE
> INDEX script that I ran yesterday...
> --
> Aaron Bertrand
> SQL Server MVP
>
>
> "Alin Selicean" <alin.nomail.selicean@.nomail.gmail.com> wrote in message
> news:eZjKdUE2HHA.4680@.TK2MSFTNGP03.phx.gbl...
>|||>I agree, but I still wondering why this option is not reflected in the
>index properties, when right-click the index, then on properties.
Because I don't think it's a "permanent" property, it is merely describing
how it should initially be created. Once the index is created, they're
going to argue that whether it was created online or not last week is not a
permanent property of the index. It's kind of like driving a car off the
lot with premium fuel, and then later expecting the car to know it should
only accept premium fuel.
I agree somewhat with your sentiments that this information should be
preserved somehow, but it is more for the purposes of re-generation of
identical scripts, rather than any property I expect the index to maintain.
A|||> permanent property of the index. It's kind of like driving a car off the
> lot with premium fuel, and then later expecting the car to know it should
> only accept premium fuel.
A better analogy would be expecting the car to somehow know that it at one
time used premium fuel.
A
Creating indexes with ONLINE = ON - failing
Hi list
Did a search on all SQL related newsgroups, but didn't find anything.
So, here's my problem:
We are running SQL 2005 Enterprise edition.
SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)".
We want to rebuild our indexes on 2 tables with the ONLINE option set to ON. They are currently set to OFF. We tried everything that came across our minds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename> REBUILD WITH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
Apparently, the statement executes with no errors, but index is not created if ONLINE set to ON. When scritping the indexes or right-clicking and then Properties, they all appear with ONLINE = OFF.
We also tried to isolate the problem, by creating a fresh new database, a fresh new table and then play around with indexes. Same outcome.
Also tested this on a developer edition, with same outcome.
Table doesn't have any LOB columns (text, ntext, image, varchar(max), nvarchar(max)).
Any ideas, welcome.
Thanks in advance for any thoughts.
Regards,
Alin
Can you post the actual DDL (including indexes) and the ALTER INDEX statement you used?
Andrew J. Kelly SQL MVP
"Alin Selicean" <alin.nomail..selicean@.nospam.gmail.com> wrote in message news:%23f7250C2HHA.5160@.TK2MSFTNGP05.phx.gbl...
Hi list
Did a search on all SQL related newsgroups, but didn't find anything.
So, here's my problem:
We are running SQL 2005 Enterprise edition.
SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)".
We want to rebuild our indexes on 2 tables with the ONLINE option set to ON. They are currently set to OFF. We tried everything that came across our minds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename> REBUILD WITH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
Apparently, the statement executes with no errors, but index is not created if ONLINE set to ON. When scritping the indexes or right-clicking and then Properties, they all appear with ONLINE = OFF.
We also tried to isolate the problem, by creating a fresh new database, a fresh new table and then play around with indexes. Same outcome.
Also tested this on a developer edition, with same outcome.
Table doesn't have any LOB columns (text, ntext, image, varchar(max), nvarchar(max)).
Any ideas, welcome.
Thanks in advance for any thoughts.
Regards,
Alin
|||Hi Andrew
Thank you for your time.
We tried the following:
1. Manually creating the index and checking the Allow online processing of DML statements...
2. The following DDL statements:
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[table1]') AND name = N'index1')DROP INDEX [index1] ON [dbo].[table1]
CREATE NONCLUSTERED INDEX [index1] ON [dbo].[table1]
([field1] ASC,[field2] ASC,[field3] ASC,[field4] ASC,[field5] ASC,[field6] ASC)
WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = ON) ON [PRIMARY]
3. We tried the above statement with only ONLINE = ON option, to avoid possible conflicts with backward compatibility keywords (as stated in BOL).
4. We also tried ALTER INDEX ALL ON dbo.table1 REBUILD WITH (ONLINE = ON);
Database compatibility level is 90.
All of the above with the same result.
Thanks.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:etQdu%23C2HHA.5884@.TK2MSFTNGP02.phx.gbl...
Can you post the actual DDL (including indexes) and the ALTER INDEX statement you used?
Andrew J. Kelly SQL MVP
"Alin Selicean" <alin.nomail..selicean@.nospam.gmail.com> wrote in message news:%23f7250C2HHA.5160@.TK2MSFTNGP05.phx.gbl...
Hi list
Did a search on all SQL related newsgroups, but didn't find anything.
So, here's my problem:
We are running SQL 2005 Enterprise edition.
SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)".
We want to rebuild our indexes on 2 tables with the ONLINE option set to ON. They are currently set to OFF. We tried everything that came across our minds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename> REBUILD WITH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
Apparently, the statement executes with no errors, but index is not created if ONLINE set to ON. When scritping the indexes or right-clicking and then Properties, they all appear with ONLINE = OFF.
We also tried to isolate the problem, by creating a fresh new database, a fresh new table and then play around with indexes. Same outcome.
Also tested this on a developer edition, with same outcome.
Table doesn't have any LOB columns (text, ntext, image, varchar(max), nvarchar(max)).
Any ideas, welcome.
Thanks in advance for any thoughts.
Regards,
Alin
|||We also have a clustered index on that table, we also tried to recreate that index with ONLINE = ON, BOL stating that if a clustered index is created with ONLINE = ON, all non-clustered indexes will be rebuilt with ONLINE = ON.
Same outcome.
Thx again.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:etQdu%23C2HHA.5884@.TK2MSFTNGP02.phx.gbl...
Can you post the actual DDL (including indexes) and the ALTER INDEX statement you used?
Andrew J. Kelly SQL MVP
"Alin Selicean" <alin.nomail..selicean@.nospam.gmail.com> wrote in message news:%23f7250C2HHA.5160@.TK2MSFTNGP05.phx.gbl...
Hi list
Did a search on all SQL related newsgroups, but didn't find anything.
So, here's my problem:
We are running SQL 2005 Enterprise edition.
SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)".
We want to rebuild our indexes on 2 tables with the ONLINE option set to ON. They are currently set to OFF. We tried everything that came across our minds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename> REBUILD WITH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
Apparently, the statement executes with no errors, but index is not created if ONLINE set to ON. When scritping the indexes or right-clicking and then Properties, they all appear with ONLINE = OFF.
We also tried to isolate the problem, by creating a fresh new database, a fresh new table and then play around with indexes. Same outcome.
Also tested this on a developer edition, with same outcome.
Table doesn't have any LOB columns (text, ntext, image, varchar(max), nvarchar(max)).
Any ideas, welcome.
Thanks in advance for any thoughts.
Regards,
Alin
|||I think there is a general bug somewhere... this is not the only option that
gets forgotten (it also happens with SORT_IN_TEMPB). I noticed this a while
ago, but at the time did not catch the online option as well. You've
spurred me to finally enter a bug report after retrieving my notes from back
then:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=291288
Aaron Bertrand
SQL Server MVP
"Alin Selicean" <alin.nomail..selicean@.nospam.gmail.com> wrote in message
news:%23f7250C2HHA.5160@.TK2MSFTNGP05.phx.gbl...
Hi list
Did a search on all SQL related newsgroups, but didn't find anything.
So, here's my problem:
We are running SQL 2005 Enterprise edition.
SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00
(Intel X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack
1)".
We want to rebuild our indexes on 2 tables with the ONLINE option set to ON.
They are currently set to OFF. We tried everything that came across our
minds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename> REBUILD
WITH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
Apparently, the statement executes with no errors, but index is not created
if ONLINE set to ON. When scritping the indexes or right-clicking and then
Properties, they all appear with ONLINE = OFF.
We also tried to isolate the problem, by creating a fresh new database, a
fresh new table and then play around with indexes. Same outcome.
Also tested this on a developer edition, with same outcome.
Table doesn't have any LOB columns (text, ntext, image, varchar(max),
nvarchar(max)).
Any ideas, welcome.
Thanks in advance for any thoughts.
Regards,
Alin
|||Thank you, Aaron, for your feedback. Reviewing your post on connect, I am
not sure if index is created with online = on, as it doesn't came up this
way when querying sys.indexes (with all the joins towards sys.index_columns
and sys.columns) or at least I didn't saw that option listed anywhere.
So my guess is that somehow that that option (along with many more, as you
also noticed) is not saved at all. Furthermore, I cannot be 100% sure that
the index is created as stated in my statement. So I think that there is no
workaround available. One can only assume that the index is created as one
expected, but there is no way to get a confirmation.
I will also test on Katmai tomorrow and post my findings on connect.
Thanks for the heads up related to Katmai.
Regards,
Alin
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23z9%23CgD2HHA.3760@.TK2MSFTNGP03.phx.gbl...
>I think there is a general bug somewhere... this is not the only option
>that gets forgotten (it also happens with SORT_IN_TEMPB). I noticed this a
>while ago, but at the time did not catch the online option as well. You've
>spurred me to finally enter a bug report after retrieving my notes from
>back then:
> http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=291288
> --
> Aaron Bertrand
> SQL Server MVP
>
>
>
> "Alin Selicean" <alin.nomail..selicean@.nospam.gmail.com> wrote in message
> news:%23f7250C2HHA.5160@.TK2MSFTNGP05.phx.gbl...
> Hi list
> Did a search on all SQL related newsgroups, but didn't find anything.
> So, here's my problem:
> We are running SQL 2005 Enterprise edition.
> SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00
> (Intel X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack
> 1)".
> We want to rebuild our indexes on 2 tables with the ONLINE option set to
> ON. They are currently set to OFF. We tried everything that came across
> our minds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename>
> REBUILD WITH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
> Apparently, the statement executes with no errors, but index is not
> created if ONLINE set to ON. When scritping the indexes or right-clicking
> and then Properties, they all appear with ONLINE = OFF.
> We also tried to isolate the problem, by creating a fresh new database, a
> fresh new table and then play around with indexes. Same outcome.
> Also tested this on a developer edition, with same outcome.
> Table doesn't have any LOB columns (text, ntext, image, varchar(max),
> nvarchar(max)).
> Any ideas, welcome.
> Thanks in advance for any thoughts.
> Regards,
> Alin
>
|||I guess having those options during the CREATE INDEX statement only affect
the initial creation of the index, and do not represent any long-term
setting that "sticks."
My guess is that it will come back as "by design." E.g. you can't specify
up front that every time you change the index, those settings will be true.
If you want to rebuild the index and rebuild it online, you will need to
specify that in the ALTER INDEX command.
Then again, if I want to script the exact CREATE INDEX statement that I ran
yesterday, I can't see what harm it would have to maintain the properties
with the index as it was created, even if you will still need to specify
those options explicitly when using ALTER. Right now it doesn't seem
possible using the tools to re-generate the exact same CREATE INDEX script
that I ran yesterday...
Aaron Bertrand
SQL Server MVP
"Alin Selicean" <alin.nomail.selicean@.nomail.gmail.com> wrote in message
news:eZjKdUE2HHA.4680@.TK2MSFTNGP03.phx.gbl...
> Thank you, Aaron, for your feedback. Reviewing your post on connect, I am
> not sure if index is created with online = on, as it doesn't came up this
> way when querying sys.indexes (with all the joins towards
> sys.index_columns and sys.columns) or at least I didn't saw that option
> listed anywhere.
> So my guess is that somehow that that option (along with many more, as you
> also noticed) is not saved at all. Furthermore, I cannot be 100% sure that
> the index is created as stated in my statement. So I think that there is
> no workaround available. One can only assume that the index is created as
> one expected, but there is no way to get a confirmation.
> I will also test on Katmai tomorrow and post my findings on connect.
> Thanks for the heads up related to Katmai.
> Regards,
> Alin
|||I agree, but I still wondering why this option is not reflected in the index
properties, when right-click the index, then on properties.
My feeling is that this is a bug in the UI. Also, you cannot retrieve this
piece of information from any of the DMV's that shows any index options. Or
maybe it's just me, I don't know. I will further test it tomorrow, but as
of today, these are my conclusions (including your opinions on this).
I will come back with more news tomorrow.
Again, thanks for your efforts.
Regards,
Alin Selicean
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eELWJeE2HHA.5772@.TK2MSFTNGP02.phx.gbl...
>I guess having those options during the CREATE INDEX statement only affect
>the initial creation of the index, and do not represent any long-term
>setting that "sticks."
> My guess is that it will come back as "by design." E.g. you can't specify
> up front that every time you change the index, those settings will be
> true. If you want to rebuild the index and rebuild it online, you will
> need to specify that in the ALTER INDEX command.
> Then again, if I want to script the exact CREATE INDEX statement that I
> ran yesterday, I can't see what harm it would have to maintain the
> properties with the index as it was created, even if you will still need
> to specify those options explicitly when using ALTER. Right now it
> doesn't seem possible using the tools to re-generate the exact same CREATE
> INDEX script that I ran yesterday...
> --
> Aaron Bertrand
> SQL Server MVP
>
>
> "Alin Selicean" <alin.nomail.selicean@.nomail.gmail.com> wrote in message
> news:eZjKdUE2HHA.4680@.TK2MSFTNGP03.phx.gbl...
>
|||>I agree, but I still wondering why this option is not reflected in the
>index properties, when right-click the index, then on properties.
Because I don't think it's a "permanent" property, it is merely describing
how it should initially be created. Once the index is created, they're
going to argue that whether it was created online or not last week is not a
permanent property of the index. It's kind of like driving a car off the
lot with premium fuel, and then later expecting the car to know it should
only accept premium fuel.
I agree somewhat with your sentiments that this information should be
preserved somehow, but it is more for the purposes of re-generation of
identical scripts, rather than any property I expect the index to maintain.
A
|||> permanent property of the index. It's kind of like driving a car off the
> lot with premium fuel, and then later expecting the car to know it should
> only accept premium fuel.
A better analogy would be expecting the car to somehow know that it at one
time used premium fuel.
A
Did a search on all SQL related newsgroups, but didn't find anything.
So, here's my problem:
We are running SQL 2005 Enterprise edition.
SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)".
We want to rebuild our indexes on 2 tables with the ONLINE option set to ON. They are currently set to OFF. We tried everything that came across our minds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename> REBUILD WITH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
Apparently, the statement executes with no errors, but index is not created if ONLINE set to ON. When scritping the indexes or right-clicking and then Properties, they all appear with ONLINE = OFF.
We also tried to isolate the problem, by creating a fresh new database, a fresh new table and then play around with indexes. Same outcome.
Also tested this on a developer edition, with same outcome.
Table doesn't have any LOB columns (text, ntext, image, varchar(max), nvarchar(max)).
Any ideas, welcome.
Thanks in advance for any thoughts.
Regards,
Alin
Can you post the actual DDL (including indexes) and the ALTER INDEX statement you used?
Andrew J. Kelly SQL MVP
"Alin Selicean" <alin.nomail..selicean@.nospam.gmail.com> wrote in message news:%23f7250C2HHA.5160@.TK2MSFTNGP05.phx.gbl...
Hi list
Did a search on all SQL related newsgroups, but didn't find anything.
So, here's my problem:
We are running SQL 2005 Enterprise edition.
SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)".
We want to rebuild our indexes on 2 tables with the ONLINE option set to ON. They are currently set to OFF. We tried everything that came across our minds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename> REBUILD WITH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
Apparently, the statement executes with no errors, but index is not created if ONLINE set to ON. When scritping the indexes or right-clicking and then Properties, they all appear with ONLINE = OFF.
We also tried to isolate the problem, by creating a fresh new database, a fresh new table and then play around with indexes. Same outcome.
Also tested this on a developer edition, with same outcome.
Table doesn't have any LOB columns (text, ntext, image, varchar(max), nvarchar(max)).
Any ideas, welcome.
Thanks in advance for any thoughts.
Regards,
Alin
|||Hi Andrew
Thank you for your time.
We tried the following:
1. Manually creating the index and checking the Allow online processing of DML statements...
2. The following DDL statements:
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[table1]') AND name = N'index1')DROP INDEX [index1] ON [dbo].[table1]
CREATE NONCLUSTERED INDEX [index1] ON [dbo].[table1]
([field1] ASC,[field2] ASC,[field3] ASC,[field4] ASC,[field5] ASC,[field6] ASC)
WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = ON) ON [PRIMARY]
3. We tried the above statement with only ONLINE = ON option, to avoid possible conflicts with backward compatibility keywords (as stated in BOL).
4. We also tried ALTER INDEX ALL ON dbo.table1 REBUILD WITH (ONLINE = ON);
Database compatibility level is 90.
All of the above with the same result.
Thanks.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:etQdu%23C2HHA.5884@.TK2MSFTNGP02.phx.gbl...
Can you post the actual DDL (including indexes) and the ALTER INDEX statement you used?
Andrew J. Kelly SQL MVP
"Alin Selicean" <alin.nomail..selicean@.nospam.gmail.com> wrote in message news:%23f7250C2HHA.5160@.TK2MSFTNGP05.phx.gbl...
Hi list
Did a search on all SQL related newsgroups, but didn't find anything.
So, here's my problem:
We are running SQL 2005 Enterprise edition.
SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)".
We want to rebuild our indexes on 2 tables with the ONLINE option set to ON. They are currently set to OFF. We tried everything that came across our minds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename> REBUILD WITH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
Apparently, the statement executes with no errors, but index is not created if ONLINE set to ON. When scritping the indexes or right-clicking and then Properties, they all appear with ONLINE = OFF.
We also tried to isolate the problem, by creating a fresh new database, a fresh new table and then play around with indexes. Same outcome.
Also tested this on a developer edition, with same outcome.
Table doesn't have any LOB columns (text, ntext, image, varchar(max), nvarchar(max)).
Any ideas, welcome.
Thanks in advance for any thoughts.
Regards,
Alin
|||We also have a clustered index on that table, we also tried to recreate that index with ONLINE = ON, BOL stating that if a clustered index is created with ONLINE = ON, all non-clustered indexes will be rebuilt with ONLINE = ON.
Same outcome.
Thx again.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:etQdu%23C2HHA.5884@.TK2MSFTNGP02.phx.gbl...
Can you post the actual DDL (including indexes) and the ALTER INDEX statement you used?
Andrew J. Kelly SQL MVP
"Alin Selicean" <alin.nomail..selicean@.nospam.gmail.com> wrote in message news:%23f7250C2HHA.5160@.TK2MSFTNGP05.phx.gbl...
Hi list
Did a search on all SQL related newsgroups, but didn't find anything.
So, here's my problem:
We are running SQL 2005 Enterprise edition.
SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)".
We want to rebuild our indexes on 2 tables with the ONLINE option set to ON. They are currently set to OFF. We tried everything that came across our minds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename> REBUILD WITH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
Apparently, the statement executes with no errors, but index is not created if ONLINE set to ON. When scritping the indexes or right-clicking and then Properties, they all appear with ONLINE = OFF.
We also tried to isolate the problem, by creating a fresh new database, a fresh new table and then play around with indexes. Same outcome.
Also tested this on a developer edition, with same outcome.
Table doesn't have any LOB columns (text, ntext, image, varchar(max), nvarchar(max)).
Any ideas, welcome.
Thanks in advance for any thoughts.
Regards,
Alin
|||I think there is a general bug somewhere... this is not the only option that
gets forgotten (it also happens with SORT_IN_TEMPB). I noticed this a while
ago, but at the time did not catch the online option as well. You've
spurred me to finally enter a bug report after retrieving my notes from back
then:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=291288
Aaron Bertrand
SQL Server MVP
"Alin Selicean" <alin.nomail..selicean@.nospam.gmail.com> wrote in message
news:%23f7250C2HHA.5160@.TK2MSFTNGP05.phx.gbl...
Hi list
Did a search on all SQL related newsgroups, but didn't find anything.
So, here's my problem:
We are running SQL 2005 Enterprise edition.
SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00
(Intel X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack
1)".
We want to rebuild our indexes on 2 tables with the ONLINE option set to ON.
They are currently set to OFF. We tried everything that came across our
minds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename> REBUILD
WITH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
Apparently, the statement executes with no errors, but index is not created
if ONLINE set to ON. When scritping the indexes or right-clicking and then
Properties, they all appear with ONLINE = OFF.
We also tried to isolate the problem, by creating a fresh new database, a
fresh new table and then play around with indexes. Same outcome.
Also tested this on a developer edition, with same outcome.
Table doesn't have any LOB columns (text, ntext, image, varchar(max),
nvarchar(max)).
Any ideas, welcome.
Thanks in advance for any thoughts.
Regards,
Alin
|||Thank you, Aaron, for your feedback. Reviewing your post on connect, I am
not sure if index is created with online = on, as it doesn't came up this
way when querying sys.indexes (with all the joins towards sys.index_columns
and sys.columns) or at least I didn't saw that option listed anywhere.
So my guess is that somehow that that option (along with many more, as you
also noticed) is not saved at all. Furthermore, I cannot be 100% sure that
the index is created as stated in my statement. So I think that there is no
workaround available. One can only assume that the index is created as one
expected, but there is no way to get a confirmation.
I will also test on Katmai tomorrow and post my findings on connect.
Thanks for the heads up related to Katmai.
Regards,
Alin
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23z9%23CgD2HHA.3760@.TK2MSFTNGP03.phx.gbl...
>I think there is a general bug somewhere... this is not the only option
>that gets forgotten (it also happens with SORT_IN_TEMPB). I noticed this a
>while ago, but at the time did not catch the online option as well. You've
>spurred me to finally enter a bug report after retrieving my notes from
>back then:
> http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=291288
> --
> Aaron Bertrand
> SQL Server MVP
>
>
>
> "Alin Selicean" <alin.nomail..selicean@.nospam.gmail.com> wrote in message
> news:%23f7250C2HHA.5160@.TK2MSFTNGP05.phx.gbl...
> Hi list
> Did a search on all SQL related newsgroups, but didn't find anything.
> So, here's my problem:
> We are running SQL 2005 Enterprise edition.
> SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00
> (Intel X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack
> 1)".
> We want to rebuild our indexes on 2 tables with the ONLINE option set to
> ON. They are currently set to OFF. We tried everything that came across
> our minds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename>
> REBUILD WITH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
> Apparently, the statement executes with no errors, but index is not
> created if ONLINE set to ON. When scritping the indexes or right-clicking
> and then Properties, they all appear with ONLINE = OFF.
> We also tried to isolate the problem, by creating a fresh new database, a
> fresh new table and then play around with indexes. Same outcome.
> Also tested this on a developer edition, with same outcome.
> Table doesn't have any LOB columns (text, ntext, image, varchar(max),
> nvarchar(max)).
> Any ideas, welcome.
> Thanks in advance for any thoughts.
> Regards,
> Alin
>
|||I guess having those options during the CREATE INDEX statement only affect
the initial creation of the index, and do not represent any long-term
setting that "sticks."
My guess is that it will come back as "by design." E.g. you can't specify
up front that every time you change the index, those settings will be true.
If you want to rebuild the index and rebuild it online, you will need to
specify that in the ALTER INDEX command.
Then again, if I want to script the exact CREATE INDEX statement that I ran
yesterday, I can't see what harm it would have to maintain the properties
with the index as it was created, even if you will still need to specify
those options explicitly when using ALTER. Right now it doesn't seem
possible using the tools to re-generate the exact same CREATE INDEX script
that I ran yesterday...
Aaron Bertrand
SQL Server MVP
"Alin Selicean" <alin.nomail.selicean@.nomail.gmail.com> wrote in message
news:eZjKdUE2HHA.4680@.TK2MSFTNGP03.phx.gbl...
> Thank you, Aaron, for your feedback. Reviewing your post on connect, I am
> not sure if index is created with online = on, as it doesn't came up this
> way when querying sys.indexes (with all the joins towards
> sys.index_columns and sys.columns) or at least I didn't saw that option
> listed anywhere.
> So my guess is that somehow that that option (along with many more, as you
> also noticed) is not saved at all. Furthermore, I cannot be 100% sure that
> the index is created as stated in my statement. So I think that there is
> no workaround available. One can only assume that the index is created as
> one expected, but there is no way to get a confirmation.
> I will also test on Katmai tomorrow and post my findings on connect.
> Thanks for the heads up related to Katmai.
> Regards,
> Alin
|||I agree, but I still wondering why this option is not reflected in the index
properties, when right-click the index, then on properties.
My feeling is that this is a bug in the UI. Also, you cannot retrieve this
piece of information from any of the DMV's that shows any index options. Or
maybe it's just me
, I don't know. I will further test it tomorrow, but asof today, these are my conclusions (including your opinions on this).
I will come back with more news tomorrow.
Again, thanks for your efforts.
Regards,
Alin Selicean
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eELWJeE2HHA.5772@.TK2MSFTNGP02.phx.gbl...
>I guess having those options during the CREATE INDEX statement only affect
>the initial creation of the index, and do not represent any long-term
>setting that "sticks."
> My guess is that it will come back as "by design." E.g. you can't specify
> up front that every time you change the index, those settings will be
> true. If you want to rebuild the index and rebuild it online, you will
> need to specify that in the ALTER INDEX command.
> Then again, if I want to script the exact CREATE INDEX statement that I
> ran yesterday, I can't see what harm it would have to maintain the
> properties with the index as it was created, even if you will still need
> to specify those options explicitly when using ALTER. Right now it
> doesn't seem possible using the tools to re-generate the exact same CREATE
> INDEX script that I ran yesterday...
> --
> Aaron Bertrand
> SQL Server MVP
>
>
> "Alin Selicean" <alin.nomail.selicean@.nomail.gmail.com> wrote in message
> news:eZjKdUE2HHA.4680@.TK2MSFTNGP03.phx.gbl...
>
|||>I agree, but I still wondering why this option is not reflected in the
>index properties, when right-click the index, then on properties.
Because I don't think it's a "permanent" property, it is merely describing
how it should initially be created. Once the index is created, they're
going to argue that whether it was created online or not last week is not a
permanent property of the index. It's kind of like driving a car off the
lot with premium fuel, and then later expecting the car to know it should
only accept premium fuel.
I agree somewhat with your sentiments that this information should be
preserved somehow, but it is more for the purposes of re-generation of
identical scripts, rather than any property I expect the index to maintain.
A
|||> permanent property of the index. It's kind of like driving a car off the
> lot with premium fuel, and then later expecting the car to know it should
> only accept premium fuel.
A better analogy would be expecting the car to somehow know that it at one
time used premium fuel.
A
Creating Fireign Keys....How?
Hi!!
How I can create foreign keys using Enterprise Manager without writing any
SQL commands'
Thanks in advance!
TimurHi,
Inside the table creation option from Enterprise manager, click the manage
relationship icon.
Thanks
Hari
MCDBA
"" <tim_@.pochtamt.ru> wrote in message
news:Oa3RUvY8DHA.2560@.TK2MSFTNGP09.phx.gbl...
> Hi!!
> How I can create foreign keys using Enterprise Manager without writing any
> SQL commands'
> Thanks in advance!
> Timur
>
How I can create foreign keys using Enterprise Manager without writing any
SQL commands'
Thanks in advance!
TimurHi,
Inside the table creation option from Enterprise manager, click the manage
relationship icon.
Thanks
Hari
MCDBA
"" <tim_@.pochtamt.ru> wrote in message
news:Oa3RUvY8DHA.2560@.TK2MSFTNGP09.phx.gbl...
> Hi!!
> How I can create foreign keys using Enterprise Manager without writing any
> SQL commands'
> Thanks in advance!
> Timur
>
Creating Fireign Keys....How?
Hi!!
How I can create foreign keys using Enterprise Manager without writing any
SQL commands'
Thanks in advance!
TimurHi,
Inside the table creation option from Enterprise manager, click the manage
relationship icon.
Thanks
Hari
MCDBA
"ôÉÍÕÒ" <tim_@.pochtamt.ru> wrote in message
news:Oa3RUvY8DHA.2560@.TK2MSFTNGP09.phx.gbl...
> Hi!!
> How I can create foreign keys using Enterprise Manager without writing any
> SQL commands'
> Thanks in advance!
> Timur
>
How I can create foreign keys using Enterprise Manager without writing any
SQL commands'
Thanks in advance!
TimurHi,
Inside the table creation option from Enterprise manager, click the manage
relationship icon.
Thanks
Hari
MCDBA
"ôÉÍÕÒ" <tim_@.pochtamt.ru> wrote in message
news:Oa3RUvY8DHA.2560@.TK2MSFTNGP09.phx.gbl...
> Hi!!
> How I can create foreign keys using Enterprise Manager without writing any
> SQL commands'
> Thanks in advance!
> Timur
>
Subscribe to:
Posts (Atom)