Create table eror

I am able to connect to a database set up on SQL windows 2003 under
IIS. However I get an error message when I try to create a table. I am
running from php.
I am sure I am doing something stupid but I can't see it or could it
be a permissions issue?
Error -
Connection to Database members Successful.
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax
near
the keyword 'IF'.
Code -
//create a table in that dbase
$sql ="CREATE TABLE IF NOT EXISTS $_SESSION[table_name]
(
firstname VARCHAR(20),
lastname VARCHAR(20),
username VARCHAR(20),
password VARCHAR(50),
group1 VARCHAR(20),
group2 VARCHAR(20),
group3 VARCHAR(20),
pchange VARCHAR(1),
email VARCHAR(100),
redirect VARCHAR(100),
verified VARCHAR(1),
last_login DATE
)";
$result = odbc_exec($connection,$sql) or die(odbc_errormsg());
Any ideas or places to look gratefully received.
JohnThere is no CREATE TABLE IF NOT EXISTS statement in T-SQL.
You would need to use something along the lines of:
IF NOT EXISTS (select * from dbo.sysobjects
WHERE id = object_id('dbo.TableName')
AND OBJECTPROPERTY(id, 'IsUserTable') = 1)
CREATE TABLE dbo.TableName
(ColumnName varchar(50) not null,
etc.....)
-Sue
On Thu, 10 Aug 2006 19:45:59 +0100, John
<yjdyhugo@.d8hujsrfu> wrote:

>I am able to connect to a database set up on SQL windows 2003 under
>IIS. However I get an error message when I try to create a table. I am
>running from php.
>I am sure I am doing something stupid but I can't see it or could it
>be a permissions issue?
>Error -
>Connection to Database members Successful.
>[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax
near
>the keyword 'IF'.
>Code -
>//create a table in that dbase
>$sql ="CREATE TABLE IF NOT EXISTS $_SESSION[table_name]
>(
> firstname VARCHAR(20),
> lastname VARCHAR(20),
> username VARCHAR(20),
> password VARCHAR(50),
> group1 VARCHAR(20),
> group2 VARCHAR(20),
> group3 VARCHAR(20),
> pchange VARCHAR(1),
> email VARCHAR(100),
> redirect VARCHAR(100),
> verified VARCHAR(1),
> last_login DATE
> )";
>$result = odbc_exec($connection,$sql) or die(odbc_errormsg());
>Any ideas or places to look gratefully received.
>John|||On Thu, 10 Aug 2006 13:09:11 -0600, Sue Hoegemeier
<Sue_H@.nomail.please> wrote:

>There is no CREATE TABLE IF NOT EXISTS statement in T-SQL.
>You would need to use something along the lines of:
>IF NOT EXISTS (select * from dbo.sysobjects
> WHERE id = object_id('dbo.TableName')
> AND OBJECTPROPERTY(id, 'IsUserTable') = 1)
> CREATE TABLE dbo.TableName
> (ColumnName varchar(50) not null,
> etc.....)
>-Sue
Thanks Sue
Would I be using T-SQL?
How can I find out?
I have never heard of that.
John
[vbcol=seagreen]
>On Thu, 10 Aug 2006 19:45:59 +0100, John
><yjdyhugo@.d8hujsrfu> wrote:
>|||Yes that's T-SQL. T-SQL (Transact SQL) is SQL Server's
implementation or "flavor" of SQL.
-Sue
On Thu, 10 Aug 2006 21:04:35 +0100, John
<yjdyhugo@.d8hujsrfu> wrote:

>On Thu, 10 Aug 2006 13:09:11 -0600, Sue Hoegemeier
><Sue_H@.nomail.please> wrote:
>
>Thanks Sue
>Would I be using T-SQL?
>How can I find out?
>I have never heard of that.|||On Thu, 10 Aug 2006 19:17:41 -0600, Sue Hoegemeier
<Sue_H@.nomail.please> wrote:

>Yes that's T-SQL. T-SQL (Transact SQL) is SQL Server's
>implementation or "flavor" of SQL. Thanks again Sue
Having had a quick look round TSQL seems to be an extension of SQL.
Are you saying that the code I used would not work on a server/SQL
implementation.
John|||On Fri, 11 Aug 2006 09:31:28 +0100, John <yjdyhugo@.d8hujsrfu> wrote:

>On Thu, 10 Aug 2006 19:17:41 -0600, Sue Hoegemeier
><Sue_H@.nomail.please> wrote:
>
>Having had a quick look round TSQL seems to be an extension of SQL.
>Are you saying that the code I used would not work on a server/SQL
>implementation.
Further info
SQL seems to be objecting to
IF NOT EXISTS
&
last_login DATE
If I remove these then it runs through OK
Any ideas whu this is the case much appreciated.
John|||On Fri, 11 Aug 2006 13:07:27 +0100, John <yjdyhugo@.d8hujsrfu> wrote:

>On Fri, 11 Aug 2006 09:31:28 +0100, John <yjdyhugo@.d8hujsrfu> wrote:
>
>Further info
>SQL seems to be objecting to
>IF NOT EXISTS
>&
>last_login DATE
>If I remove these then it runs through OK
>Any ideas whu this is the case much appreciated.
>John
Sue already gave you the answer. In SQL Server's implementation of
SQL, "CREATE TABLE IF NOT EXISTS" is not legal syntax. Neither is
"DATE" a valid data type.
T-SQL (Transact SQL) defines what is legal syntax for SQL Server.|||On Fri, 11 Aug 2006 07:01:18 -0700, Jack Jackson
<jacknospam@.pebbleridge.com> wrote:

>On Fri, 11 Aug 2006 13:07:27 +0100, John <yjdyhugo@.d8hujsrfu> wrote:
>
>Sue already gave you the answer. In SQL Server's implementation of
>SQL, "CREATE TABLE IF NOT EXISTS" is not legal syntax. Neither is
>"DATE" a valid data type.
>T-SQL (Transact SQL) defines what is legal syntax for SQL Server.
Sorry you are quite correct.
I thought T-SQL was an extension to SQL but it appears some things
have been taken out as well.
Is that the case?
Thanks for your help Sue and Jack, I think I am beginning to get the
picture now.
John|||It is an extension but it is also the "flavor" used by SQL
Server and impacts how some of the SQL and related standards
are implemented. So it's not just a matter of "additional
functionality" type of thing. Not all database vendors
implement all of the same ANSI SQL.And not everything
implemented by a database vendor is a SQL standard. You will
find plenty of variation in syntax, functionality among the
different database platforms.
-Sue
On Sat, 12 Aug 2006 10:26:30 +0100, John
<yjdyhugo@.d8hujsrfu> wrote:

>Sorry you are quite correct.
>I thought T-SQL was an extension to SQL but it appears some things
>have been taken out as well.
>Is that the case?
>Thanks for your help Sue and Jack, I think I am beginning to get the
>picture now.
>John|||On Sun, 13 Aug 2006 15:26:03 -0600, Sue Hoegemeier
<Sue_H@.nomail.please> wrote:

>It is an extension but it is also the "flavor" used by SQL
>Server and impacts how some of the SQL and related standards
>are implemented. So it's not just a matter of "additional
>functionality" type of thing. Not all database vendors
>implement all of the same ANSI SQL.And not everything
>implemented by a database vendor is a SQL standard. You will
>find plenty of variation in syntax, functionality among the
>different database platforms.
Looks like I am going to have to wade through the mysql and spot the
differences as I go.
Thanks again for your help on this, I appreciate it.
John

Create Subscription For Rpt Service with Windows integrated security !!!

hi All,
I m nicky here.
I have problem on Create Subscription for my report if my Data Source is use Windows Integrated Security, with this error message:

"Subscriptions cannot be created because the credentials used to run the report are not stored, or if a linked report , the linked is no longer valid"

Please Help ....

Thank you
From
nicky

For subscriptions to work the data source must either store credentials nor not require them (none option in the UI).

-Lukasz

|||Does this open the report so that anyone can see it?|||Yep, it sure does. A big pain. You could have multiple copies of the one report and change the data source of each report. Then secure the report via user groups. A pain, but if you only had a few users it might be ok. If you have hundreds however......|||

Not sure I understand. When you say multiple copies of a report, you lose me.

I have a report that has stored credentials so that the subscription will work. Unfortunately this also means that anyone can log on to the server and view the report. How do stop everyone from viewing the report?

|||

Hey,

Sorry about the delay in responding, Xmas and project work got in the way!

I mean that you publish the one copy of a report, several times to different folders. You don't use a shared data source, but instead use a data source specificially for each report. Now you edit the connection string of each report based data source to match each specific user. Finially, if it is possible, you have each of these reports in a unique folder that have security defined on it per user.

As I said, not pretty, but a possible solution!

Mark

|||Btw, is this report against an Analysis Service DB?|||

I got my issue fixed. The problem was that anonymous connections were set within IIS and this meant that RS security was rendered useless. Now that this issue is corrected, everything that I have read works correctly. (If not for the rock in the hoof of the lead horse...)

Thanks for all your help!

Create Subscription For Rpt Service with Windows integrated security !!!

hi All,
I m nicky here.
I have problem on Create Subscription for my report if my Data Source is use Windows Integrated Security, with this error message:

"Subscriptions cannot be created because the credentials used to run the report are not stored, or if a linked report , the linked is no longer valid"

Please Help ....

Thank you
From
nicky

For subscriptions to work the data source must either store credentials nor not require them (none option in the UI).

-Lukasz

|||Does this open the report so that anyone can see it?|||Yep, it sure does. A big pain. You could have multiple copies of the one report and change the data source of each report. Then secure the report via user groups. A pain, but if you only had a few users it might be ok. If you have hundreds however......|||

Not sure I understand. When you say multiple copies of a report, you lose me.

I have a report that has stored credentials so that the subscription will work. Unfortunately this also means that anyone can log on to the server and view the report. How do stop everyone from viewing the report?

|||

Hey,

Sorry about the delay in responding, Xmas and project work got in the way!

I mean that you publish the one copy of a report, several times to different folders. You don't use a shared data source, but instead use a data source specificially for each report. Now you edit the connection string of each report based data source to match each specific user. Finially, if it is possible, you have each of these reports in a unique folder that have security defined on it per user.

As I said, not pretty, but a possible solution!

Mark

|||Btw, is this report against an Analysis Service DB?|||

I got my issue fixed. The problem was that anonymous connections were set within IIS and this meant that RS security was rendered useless. Now that this issue is corrected, everything that I have read works correctly. (If not for the rock in the hoof of the lead horse...)

Thanks for all your help!

Create Subscription For Rpt Service with Windows integrated security !!!

hi All,
I m nicky here.
I have problem on Create Subscription for my report if my Data Source is use Windows Integrated Security, with this error message:

"Subscriptions cannot be created because the credentials used to run the report are not stored, or if a linked report , the linked is no longer valid"

Please Help ....

Thank you
From
nicky

For subscriptions to work the data source must either store credentials nor not require them (none option in the UI).

-Lukasz

|||Does this open the report so that anyone can see it?|||Yep, it sure does. A big pain. You could have multiple copies of the one report and change the data source of each report. Then secure the report via user groups. A pain, but if you only had a few users it might be ok. If you have hundreds however......|||

Not sure I understand. When you say multiple copies of a report, you lose me.

I have a report that has stored credentials so that the subscription will work. Unfortunately this also means that anyone can log on to the server and view the report. How do stop everyone from viewing the report?

|||

Hey,

Sorry about the delay in responding, Xmas and project work got in the way!

I mean that you publish the one copy of a report, several times to different folders. You don't use a shared data source, but instead use a data source specificially for each report. Now you edit the connection string of each report based data source to match each specific user. Finially, if it is possible, you have each of these reports in a unique folder that have security defined on it per user.

As I said, not pretty, but a possible solution!

Mark

|||Btw, is this report against an Analysis Service DB?|||

I got my issue fixed. The problem was that anonymous connections were set within IIS and this meant that RS security was rendered useless. Now that this issue is corrected, everything that I have read works correctly. (If not for the rock in the hoof of the lead horse...)

Thanks for all your help!

sql

Create Subscription For Rpt Service with Windows integrated security !!!

hi All,
I m nicky here.
I have problem on Create Subscription for my report if my Data Source is use Windows Integrated Security, with this error message:

"Subscriptions cannot be created because the credentials used to run the report are not stored, or if a linked report , the linked is no longer valid"

Please Help ....

Thank you
From
nicky

For subscriptions to work the data source must either store credentials nor not require them (none option in the UI).

-Lukasz

|||Does this open the report so that anyone can see it?|||Yep, it sure does. A big pain. You could have multiple copies of the one report and change the data source of each report. Then secure the report via user groups. A pain, but if you only had a few users it might be ok. If you have hundreds however......|||

Not sure I understand. When you say multiple copies of a report, you lose me.

I have a report that has stored credentials so that the subscription will work. Unfortunately this also means that anyone can log on to the server and view the report. How do stop everyone from viewing the report?

|||

Hey,

Sorry about the delay in responding, Xmas and project work got in the way!

I mean that you publish the one copy of a report, several times to different folders. You don't use a shared data source, but instead use a data source specificially for each report. Now you edit the connection string of each report based data source to match each specific user. Finially, if it is possible, you have each of these reports in a unique folder that have security defined on it per user.

As I said, not pretty, but a possible solution!

Mark

|||Btw, is this report against an Analysis Service DB?|||

I got my issue fixed. The problem was that anonymous connections were set within IIS and this meant that RS security was rendered useless. Now that this issue is corrected, everything that I have read works correctly. (If not for the rock in the hoof of the lead horse...)

Thanks for all your help!

Create reporting services project in VS 2003

I've installed instance of SQL Server 2005 with reporting services on
my Windows 2003
computer .I've had Visual studio 2003 .After installation i can't
create new reporting
service project in my VS 2003 IDE,but i have now VS 2005 with only
reporting services
project types evalible.I need to work on reporting services in VS
2003.Please tell me how
can i do this.
ThanksHi,
I dont think it is possible in VS 2003. If you have installed Sql server
2005 and their tools you can go through "SQL Server Business Intelligent
studio" you can do report creation.
Regards
Amarnath
"gbletel@.gmail.com" wrote:
> I've installed instance of SQL Server 2005 with reporting services on
> my Windows 2003
> computer .I've had Visual studio 2003 .After installation i can't
> create new reporting
> service project in my VS 2003 IDE,but i have now VS 2005 with only
> reporting services
> project types evalible.I need to work on reporting services in VS
> 2003.Please tell me how
> can i do this.
> Thanks
>

Create Reporting Services on Windows Form

How can I create a report on windows form?
I can create in asp.net with report viewer but cannot do it on windows form!
Any reference that can tell me how to do that?
Thanks
JasonAdd a AxSHDocVw.AxWebBrowser to the form and call the report as follows
"Jason" <Jason@.discussions.microsoft.com> wrote in message
news:A68C2B5F-65DE-4CE4-8DB6-8CA7F194A8D2@.microsoft.com...
> How can I create a report on windows form?
> I can create in asp.net with report viewer but cannot do it on windows
form!
> Any reference that can tell me how to do that?
> Thanks
> Jason|||Add an AxSHDocVw.AxWebBrowser to you form and then call it using:
Object o = null;
axWebBrowser1.Navigate("path to report", ref o, ref o, ref o, ref o);
This is probably the easiest way.
"Jason" <Jason@.discussions.microsoft.com> wrote in message
news:A68C2B5F-65DE-4CE4-8DB6-8CA7F194A8D2@.microsoft.com...
> How can I create a report on windows form?
> I can create in asp.net with report viewer but cannot do it on windows
form!
> Any reference that can tell me how to do that?
> Thanks
> Jason|||You need to use the Web Service to render reports.
Refer to the following link :
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/RSPROG/htm/rsp_prog_intro_7vqa.asp
>--Original Message--
>How can I create a report on windows form?
>I can create in asp.net with report viewer but cannot do
it on windows form!
>Any reference that can tell me how to do that?
>Thanks
>Jason
>.
>|||Jason,
The easiest way to to this is to shell out to the browser. For example, if
your app is .NET WinForm app, you can do:
Process.Start ("IExplore", <report url>)
If you need to embed the report inside the form, you can use the Microsoft
WebBrowser ActiveX control. You may have a look at the RSExplorer sample
which comes with RS to find out how this could be implemented.
The above two options generate reports via URL addressability.
Alternatively, you can render the report by SOAP by calling the Render SOAP
API. In general, I would recommend you evaluate the URL addressability
option first since it is the fastest, richest and simplest way to render
reports.
--
Hope this helps.
----
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"Jason" <Jason@.discussions.microsoft.com> wrote in message
news:A68C2B5F-65DE-4CE4-8DB6-8CA7F194A8D2@.microsoft.com...
> How can I create a report on windows form?
> I can create in asp.net with report viewer but cannot do it on windows
form!
> Any reference that can tell me how to do that?
> Thanks
> Jason

Create only unique indexes

Hello.
SQL Server 2000, Windows 2000 Server, latest SPs
I have been told by a knowledgeable friend that SQL Server works best if
you ONLY use unique indexes. To create an index on a non-indexed field,
he recommends creating a composite index on the indexed field together
with the table's primary key, eg:
CREATE UNIQUE INDEX idx1 ON t.field1, t.id
Could anyone please explain:
i) if this is indeed best practice
ii) if so, why this works, and
iii) if so, why SQL Server does not do this by default?
Thanks in advance for your help,
Mike
Mike,
The more selective a column's values are the more likely the optimizer is to
use the index on the column. That being said a column with only unique
values will have the highest selectivity and will be a prime candidate for
an index. However in the real world data is very often required to be
searched that is not unique. Even then if the column selectivity is high
enough, the optimizer is still likely to use the index if it exists.
By default a PRIMARY KEY is a clustered index. When you create a
nonclustered index the pointer to the data in the index is the clustered
key. So there is no need to add the index column and the PK column in the
index.
HTH
Jerry
"Mike Chamberlain" <none@.hotmail.com> wrote in message
news:ehuM8850FHA.3524@.tk2msftngp13.phx.gbl...
> Hello.
> SQL Server 2000, Windows 2000 Server, latest SPs
> I have been told by a knowledgeable friend that SQL Server works best if
> you ONLY use unique indexes. To create an index on a non-indexed field,
> he recommends creating a composite index on the indexed field together
> with the table's primary key, eg:
> CREATE UNIQUE INDEX idx1 ON t.field1, t.id
> Could anyone please explain:
> i) if this is indeed best practice
> ii) if so, why this works, and
> iii) if so, why SQL Server does not do this by default?
> Thanks in advance for your help,
> Mike
|||Mike Chamberlain wrote:
> CREATE UNIQUE INDEX idx1 ON t.field1, t.id
> Could anyone please explain:
> i) if this is indeed best practice?
No. Here's few tidbits. Others may add more:
1- Indexes should be driven by DRI constraints (PK, FK, alternate keys)
and by performance needs. If you're dealing with a PK or alternate key,
it's going to be unique as a matter of design. If it's a FK, it's most
likely a non-unique index. If a column or set of columns should be
unique because the business says they are, then a unique index should be
used. If the business says they're not unique, then they're not.
2- A table with a clustered index uses the clustered index key as the
pointer in all non-clustered indexes. Using your co-workers recommended
scenario in this case you would have a unique, clustered index on ID,
and a unique, non-clustered index on COL1 + ID. Underneath, the index
will really have ID + COL1 + ID, which just adds overhead.
3- A clustered index key is always stored internally as a unique value.
If you create a non-unique, clustered index, each column value that has
repeating values has a unique identifier tacked onto the value of each
to make it unique internally. Adding on an UNIQUE INT value as
suggested, could decrease index size if there are many repeating values.
In this case, it could help a little in size, but I don't think it's
worth it.
4- On a table with many non-unique indexes, you would have the ID column
added onto each, adding an additional 4 bytes per index key. Using a
clustered index on the ID would give you the same effect without the
additional overhead.
5- SQL Server can use non-unique indexes as well as unique ones. If your
index statistics are kept up to date, then the query optimizer should
select the proper index.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Just a minor point, if you don't mind, David (and some additional points for Mike):

> 2- A table with a clustered index uses the clustered index key as the pointer in all non-clustered
> indexes. Using your co-workers recommended scenario in this case you would have a unique,
> clustered index on ID, and a unique, non-clustered index on COL1 + ID. Underneath, the index will
> really have ID + COL1 + ID, which just adds overhead.
SQL Server will not store the clustering key twice if you name the clustering key in the nc index.
In other words, say you have a unique clustered index on the "ID" column and want to create a nc
index on the "Data" column, then both below will do the exact same thing:
(data, id)
(data)
For both above, the index will be on (data, id)
Below is a different thing, though:
(id, data)
But above would not be what is desired, as the reason to create the nc index is probably for SQL
Server to be able to SEEK through the index on the "data" column alone. Not having that column as
the first column in the index makes it useless for SEEK over the column.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23D$QNO60FHA.3720@.TK2MSFTNGP14.phx.gbl...
> Mike Chamberlain wrote:
> No. Here's few tidbits. Others may add more:
> 1- Indexes should be driven by DRI constraints (PK, FK, alternate keys) and by performance needs.
> If you're dealing with a PK or alternate key, it's going to be unique as a matter of design. If
> it's a FK, it's most likely a non-unique index. If a column or set of columns should be unique
> because the business says they are, then a unique index should be used. If the business says
> they're not unique, then they're not.
> 2- A table with a clustered index uses the clustered index key as the pointer in all non-clustered
> indexes. Using your co-workers recommended scenario in this case you would have a unique,
> clustered index on ID, and a unique, non-clustered index on COL1 + ID. Underneath, the index will
> really have ID + COL1 + ID, which just adds overhead.
> 3- A clustered index key is always stored internally as a unique value. If you create a
> non-unique, clustered index, each column value that has repeating values has a unique identifier
> tacked onto the value of each to make it unique internally. Adding on an UNIQUE INT value as
> suggested, could decrease index size if there are many repeating values. In this case, it could
> help a little in size, but I don't think it's worth it.
> 4- On a table with many non-unique indexes, you would have the ID column added onto each, adding
> an additional 4 bytes per index key. Using a clustered index on the ID would give you the same
> effect without the additional overhead.
> 5- SQL Server can use non-unique indexes as well as unique ones. If your index statistics are kept
> up to date, then the query optimizer should select the proper index.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
|||Tibor Karaszi wrote:
> Just a minor point, if you don't mind, David (and some additional
> points for Mike):
> SQL Server will not store the clustering key twice if you name the
> clustering key in the nc index. In other words, say you have a unique
> clustered index on the "ID" column and want to create a nc index on
> the "Data" column, then both below will do the exact same thing:
> (data, id)
> (data)
>
Tibor,
I just did a quick test. I created a clustered index on COL1 + COL2. I
then created two other indexes: A unique, non-clustered on COL3 + COL1 +
COL2 and a non-clustered on COL3.
In sysindexes, the keycnt column shows as 5 for the first unique,
non-clustered index and 3 for the non-unique index. The keys column is
larger for the 5 keycnt column. However, the reserved pages are similar,
supporting your theory.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||David,
DBCC PAGE to the rescue :-). I created a table same as your description and looked at the actual
index pages:
A clustered index on COL1 + COL2. A unique, non-clustered on COL3 + COL1 + COL2 and a non-clustered
on COL3. My test show that a row entry for the index page for the two non-clustered indexes is
identical:
use tempdb
GO
drop table t
GO
create table t(c1 varchar(1), c2 varchar(1), c3 varchar(1), c4 varchar(1), c5 varchar(1))
insert into t values ('a', 'b', 'c', 'd', 'e')
create clustered index x1 on t(c1, c2)
create unique nonclustered index x2 on t(c3, c1, c2)
create nonclustered index x3 on t(c3)
SELECT keycnt, * FROM sysindexes where id = object_id('t')
DBCC IND(tempdb, t, -1)
--Take address for indid 2 and 3, page type 2 and use below
DBCC TRACEON(3604)
DBCC PAGE(tempdb, 1, 15, 1)
DBCC PAGE(tempdb, 1, 31, 1)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23HdkwXB1FHA.1256@.TK2MSFTNGP09.phx.gbl...
> Tibor Karaszi wrote:
> Tibor,
> I just did a quick test. I created a clustered index on COL1 + COL2. I then created two other
> indexes: A unique, non-clustered on COL3 + COL1 + COL2 and a non-clustered on COL3.
> In sysindexes, the keycnt column shows as 5 for the first unique, non-clustered index and 3 for
> the non-unique index. The keys column is larger for the 5 keycnt column. However, the reserved
> pages are similar, supporting your theory.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com

Create only unique indexes

Hello.
SQL Server 2000, Windows 2000 Server, latest SPs
I have been told by a knowledgeable friend that SQL Server works best if
you ONLY use unique indexes. To create an index on a non-indexed field,
he recommends creating a composite index on the indexed field together
with the table's primary key, eg:
CREATE UNIQUE INDEX idx1 ON t.field1, t.id
Could anyone please explain:
i) if this is indeed best practice
ii) if so, why this works, and
iii) if so, why SQL Server does not do this by default?
Thanks in advance for your help,
MikeMike,
The more selective a column's values are the more likely the optimizer is to
use the index on the column. That being said a column with only unique
values will have the highest selectivity and will be a prime candidate for
an index. However in the real world data is very often required to be
searched that is not unique. Even then if the column selectivity is high
enough, the optimizer is still likely to use the index if it exists.
By default a PRIMARY KEY is a clustered index. When you create a
nonclustered index the pointer to the data in the index is the clustered
key. So there is no need to add the index column and the PK column in the
index.
HTH
Jerry
"Mike Chamberlain" <none@.hotmail.com> wrote in message
news:ehuM8850FHA.3524@.tk2msftngp13.phx.gbl...
> Hello.
> SQL Server 2000, Windows 2000 Server, latest SPs
> I have been told by a knowledgeable friend that SQL Server works best if
> you ONLY use unique indexes. To create an index on a non-indexed field,
> he recommends creating a composite index on the indexed field together
> with the table's primary key, eg:
> CREATE UNIQUE INDEX idx1 ON t.field1, t.id
> Could anyone please explain:
> i) if this is indeed best practice
> ii) if so, why this works, and
> iii) if so, why SQL Server does not do this by default?
> Thanks in advance for your help,
> Mike|||Mike Chamberlain wrote:
> CREATE UNIQUE INDEX idx1 ON t.field1, t.id
> Could anyone please explain:
> i) if this is indeed best practice?
No. Here's few tidbits. Others may add more:
1- Indexes should be driven by DRI constraints (PK, FK, alternate keys)
and by performance needs. If you're dealing with a PK or alternate key,
it's going to be unique as a matter of design. If it's a FK, it's most
likely a non-unique index. If a column or set of columns should be
unique because the business says they are, then a unique index should be
used. If the business says they're not unique, then they're not.
2- A table with a clustered index uses the clustered index key as the
pointer in all non-clustered indexes. Using your co-workers recommended
scenario in this case you would have a unique, clustered index on ID,
and a unique, non-clustered index on COL1 + ID. Underneath, the index
will really have ID + COL1 + ID, which just adds overhead.
3- A clustered index key is always stored internally as a unique value.
If you create a non-unique, clustered index, each column value that has
repeating values has a unique identifier tacked onto the value of each
to make it unique internally. Adding on an UNIQUE INT value as
suggested, could decrease index size if there are many repeating values.
In this case, it could help a little in size, but I don't think it's
worth it.
4- On a table with many non-unique indexes, you would have the ID column
added onto each, adding an additional 4 bytes per index key. Using a
clustered index on the ID would give you the same effect without the
additional overhead.
5- SQL Server can use non-unique indexes as well as unique ones. If your
index statistics are kept up to date, then the query optimizer should
select the proper index.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Just a minor point, if you don't mind, David (and some additional points for
Mike):

> 2- A table with a clustered index uses the clustered index key as the poin
ter in all non-clustered
> indexes. Using your co-workers recommended scenario in this case you would
have a unique,
> clustered index on ID, and a unique, non-clustered index on COL1 + ID. Und
erneath, the index will
> really have ID + COL1 + ID, which just adds overhead.
SQL Server will not store the clustering key twice if you name the clusterin
g key in the nc index.
In other words, say you have a unique clustered index on the "ID" column and
want to create a nc
index on the "Data" column, then both below will do the exact same thing:
(data, id)
(data)
For both above, the index will be on (data, id)
Below is a different thing, though:
(id, data)
But above would not be what is desired, as the reason to create the nc index
is probably for SQL
Server to be able to SEEK through the index on the "data" column alone. Not
having that column as
the first column in the index makes it useless for SEEK over the column.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23D$QNO60FHA.3720@.TK2MSFTNGP14.phx.gbl...
> Mike Chamberlain wrote:
> No. Here's few tidbits. Others may add more:
> 1- Indexes should be driven by DRI constraints (PK, FK, alternate keys) an
d by performance needs.
> If you're dealing with a PK or alternate key, it's going to be unique as a
matter of design. If
> it's a FK, it's most likely a non-unique index. If a column or set of colu
mns should be unique
> because the business says they are, then a unique index should be used. If
the business says
> they're not unique, then they're not.
> 2- A table with a clustered index uses the clustered index key as the poin
ter in all non-clustered
> indexes. Using your co-workers recommended scenario in this case you would
have a unique,
> clustered index on ID, and a unique, non-clustered index on COL1 + ID. Und
erneath, the index will
> really have ID + COL1 + ID, which just adds overhead.
> 3- A clustered index key is always stored internally as a unique value. If
you create a
> non-unique, clustered index, each column value that has repeating values h
as a unique identifier
> tacked onto the value of each to make it unique internally. Adding on an U
NIQUE INT value as
> suggested, could decrease index size if there are many repeating values. I
n this case, it could
> help a little in size, but I don't think it's worth it.
> 4- On a table with many non-unique indexes, you would have the ID column a
dded onto each, adding
> an additional 4 bytes per index key. Using a clustered index on the ID wou
ld give you the same
> effect without the additional overhead.
> 5- SQL Server can use non-unique indexes as well as unique ones. If your i
ndex statistics are kept
> up to date, then the query optimizer should select the proper index.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||Tibor Karaszi wrote:
> Just a minor point, if you don't mind, David (and some additional
> points for Mike):
> SQL Server will not store the clustering key twice if you name the
> clustering key in the nc index. In other words, say you have a unique
> clustered index on the "ID" column and want to create a nc index on
> the "Data" column, then both below will do the exact same thing:
> (data, id)
> (data)
>
Tibor,
I just did a quick test. I created a clustered index on COL1 + COL2. I
then created two other indexes: A unique, non-clustered on COL3 + COL1 +
COL2 and a non-clustered on COL3.
In sysindexes, the keycnt column shows as 5 for the first unique,
non-clustered index and 3 for the non-unique index. The keys column is
larger for the 5 keycnt column. However, the reserved pages are similar,
supporting your theory.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||David,
DBCC PAGE to the rescue :-). I created a table same as your description and
looked at the actual
index pages:
A clustered index on COL1 + COL2. A unique, non-clustered on COL3 + COL1 + C
OL2 and a non-clustered
on COL3. My test show that a row entry for the index page for the two non-cl
ustered indexes is
identical:
use tempdb
GO
drop table t
GO
create table t(c1 varchar(1), c2 varchar(1), c3 varchar(1), c4 varchar(1), c
5 varchar(1))
insert into t values ('a', 'b', 'c', 'd', 'e')
create clustered index x1 on t(c1, c2)
create unique nonclustered index x2 on t(c3, c1, c2)
create nonclustered index x3 on t(c3)
SELECT keycnt, * FROM sysindexes where id = object_id('t')
DBCC IND(tempdb, t, -1)
--Take address for indid 2 and 3, page type 2 and use below
DBCC TRACEON(3604)
DBCC PAGE(tempdb, 1, 15, 1)
DBCC PAGE(tempdb, 1, 31, 1)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23HdkwXB1FHA.1256@.TK2MSFTNGP09.phx.gbl...
> Tibor Karaszi wrote:
> Tibor,
> I just did a quick test. I created a clustered index on COL1 + COL2. I the
n created two other
> indexes: A unique, non-clustered on COL3 + COL1 + COL2 and a non-clustered
on COL3.
> In sysindexes, the keycnt column shows as 5 for the first unique, non-clus
tered index and 3 for
> the non-unique index. The keys column is larger for the 5 keycnt column. H
owever, the reserved
> pages are similar, supporting your theory.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com

Create only unique indexes

Hello.
SQL Server 2000, Windows 2000 Server, latest SPs
I have been told by a knowledgeable friend that SQL Server works best if
you ONLY use unique indexes. To create an index on a non-indexed field,
he recommends creating a composite index on the indexed field together
with the table's primary key, eg:
CREATE UNIQUE INDEX idx1 ON t.field1, t.id
Could anyone please explain:
i) if this is indeed best practice
ii) if so, why this works, and
iii) if so, why SQL Server does not do this by default?
Thanks in advance for your help,
MikeMike,
The more selective a column's values are the more likely the optimizer is to
use the index on the column. That being said a column with only unique
values will have the highest selectivity and will be a prime candidate for
an index. However in the real world data is very often required to be
searched that is not unique. Even then if the column selectivity is high
enough, the optimizer is still likely to use the index if it exists.
By default a PRIMARY KEY is a clustered index. When you create a
nonclustered index the pointer to the data in the index is the clustered
key. So there is no need to add the index column and the PK column in the
index.
HTH
Jerry
"Mike Chamberlain" <none@.hotmail.com> wrote in message
news:ehuM8850FHA.3524@.tk2msftngp13.phx.gbl...
> Hello.
> SQL Server 2000, Windows 2000 Server, latest SPs
> I have been told by a knowledgeable friend that SQL Server works best if
> you ONLY use unique indexes. To create an index on a non-indexed field,
> he recommends creating a composite index on the indexed field together
> with the table's primary key, eg:
> CREATE UNIQUE INDEX idx1 ON t.field1, t.id
> Could anyone please explain:
> i) if this is indeed best practice
> ii) if so, why this works, and
> iii) if so, why SQL Server does not do this by default?
> Thanks in advance for your help,
> Mike|||Mike Chamberlain wrote:
> CREATE UNIQUE INDEX idx1 ON t.field1, t.id
> Could anyone please explain:
> i) if this is indeed best practice?
No. Here's few tidbits. Others may add more:
1- Indexes should be driven by DRI constraints (PK, FK, alternate keys)
and by performance needs. If you're dealing with a PK or alternate key,
it's going to be unique as a matter of design. If it's a FK, it's most
likely a non-unique index. If a column or set of columns should be
unique because the business says they are, then a unique index should be
used. If the business says they're not unique, then they're not.
2- A table with a clustered index uses the clustered index key as the
pointer in all non-clustered indexes. Using your co-workers recommended
scenario in this case you would have a unique, clustered index on ID,
and a unique, non-clustered index on COL1 + ID. Underneath, the index
will really have ID + COL1 + ID, which just adds overhead.
3- A clustered index key is always stored internally as a unique value.
If you create a non-unique, clustered index, each column value that has
repeating values has a unique identifier tacked onto the value of each
to make it unique internally. Adding on an UNIQUE INT value as
suggested, could decrease index size if there are many repeating values.
In this case, it could help a little in size, but I don't think it's
worth it.
4- On a table with many non-unique indexes, you would have the ID column
added onto each, adding an additional 4 bytes per index key. Using a
clustered index on the ID would give you the same effect without the
additional overhead.
5- SQL Server can use non-unique indexes as well as unique ones. If your
index statistics are kept up to date, then the query optimizer should
select the proper index.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Just a minor point, if you don't mind, David (and some additional points for Mike):
> 2- A table with a clustered index uses the clustered index key as the pointer in all non-clustered
> indexes. Using your co-workers recommended scenario in this case you would have a unique,
> clustered index on ID, and a unique, non-clustered index on COL1 + ID. Underneath, the index will
> really have ID + COL1 + ID, which just adds overhead.
SQL Server will not store the clustering key twice if you name the clustering key in the nc index.
In other words, say you have a unique clustered index on the "ID" column and want to create a nc
index on the "Data" column, then both below will do the exact same thing:
(data, id)
(data)
For both above, the index will be on (data, id)
Below is a different thing, though:
(id, data)
But above would not be what is desired, as the reason to create the nc index is probably for SQL
Server to be able to SEEK through the index on the "data" column alone. Not having that column as
the first column in the index makes it useless for SEEK over the column.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23D$QNO60FHA.3720@.TK2MSFTNGP14.phx.gbl...
> Mike Chamberlain wrote:
>> CREATE UNIQUE INDEX idx1 ON t.field1, t.id
>> Could anyone please explain:
>> i) if this is indeed best practice?
> No. Here's few tidbits. Others may add more:
> 1- Indexes should be driven by DRI constraints (PK, FK, alternate keys) and by performance needs.
> If you're dealing with a PK or alternate key, it's going to be unique as a matter of design. If
> it's a FK, it's most likely a non-unique index. If a column or set of columns should be unique
> because the business says they are, then a unique index should be used. If the business says
> they're not unique, then they're not.
> 2- A table with a clustered index uses the clustered index key as the pointer in all non-clustered
> indexes. Using your co-workers recommended scenario in this case you would have a unique,
> clustered index on ID, and a unique, non-clustered index on COL1 + ID. Underneath, the index will
> really have ID + COL1 + ID, which just adds overhead.
> 3- A clustered index key is always stored internally as a unique value. If you create a
> non-unique, clustered index, each column value that has repeating values has a unique identifier
> tacked onto the value of each to make it unique internally. Adding on an UNIQUE INT value as
> suggested, could decrease index size if there are many repeating values. In this case, it could
> help a little in size, but I don't think it's worth it.
> 4- On a table with many non-unique indexes, you would have the ID column added onto each, adding
> an additional 4 bytes per index key. Using a clustered index on the ID would give you the same
> effect without the additional overhead.
> 5- SQL Server can use non-unique indexes as well as unique ones. If your index statistics are kept
> up to date, then the query optimizer should select the proper index.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||Tibor Karaszi wrote:
> Just a minor point, if you don't mind, David (and some additional
> points for Mike):
>> 2- A table with a clustered index uses the clustered index key as
>> the pointer in all non-clustered indexes. Using your co-workers
>> recommended scenario in this case you would have a unique, clustered
>> index on ID, and a unique, non-clustered index on COL1 + ID.
>> Underneath, the index will really have ID + COL1 + ID, which just
>> adds overhead.
> SQL Server will not store the clustering key twice if you name the
> clustering key in the nc index. In other words, say you have a unique
> clustered index on the "ID" column and want to create a nc index on
> the "Data" column, then both below will do the exact same thing:
> (data, id)
> (data)
>
Tibor,
I just did a quick test. I created a clustered index on COL1 + COL2. I
then created two other indexes: A unique, non-clustered on COL3 + COL1 +
COL2 and a non-clustered on COL3.
In sysindexes, the keycnt column shows as 5 for the first unique,
non-clustered index and 3 for the non-unique index. The keys column is
larger for the 5 keycnt column. However, the reserved pages are similar,
supporting your theory.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||David,
DBCC PAGE to the rescue :-). I created a table same as your description and looked at the actual
index pages:
A clustered index on COL1 + COL2. A unique, non-clustered on COL3 + COL1 + COL2 and a non-clustered
on COL3. My test show that a row entry for the index page for the two non-clustered indexes is
identical:
use tempdb
GO
drop table t
GO
create table t(c1 varchar(1), c2 varchar(1), c3 varchar(1), c4 varchar(1), c5 varchar(1))
insert into t values ('a', 'b', 'c', 'd', 'e')
create clustered index x1 on t(c1, c2)
create unique nonclustered index x2 on t(c3, c1, c2)
create nonclustered index x3 on t(c3)
SELECT keycnt, * FROM sysindexes where id = object_id('t')
DBCC IND(tempdb, t, -1)
--Take address for indid 2 and 3, page type 2 and use below
DBCC TRACEON(3604)
DBCC PAGE(tempdb, 1, 15, 1)
DBCC PAGE(tempdb, 1, 31, 1)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23HdkwXB1FHA.1256@.TK2MSFTNGP09.phx.gbl...
> Tibor Karaszi wrote:
>> Just a minor point, if you don't mind, David (and some additional
>> points for Mike):
>> 2- A table with a clustered index uses the clustered index key as
>> the pointer in all non-clustered indexes. Using your co-workers
>> recommended scenario in this case you would have a unique, clustered
>> index on ID, and a unique, non-clustered index on COL1 + ID.
>> Underneath, the index will really have ID + COL1 + ID, which just
>> adds overhead.
>> SQL Server will not store the clustering key twice if you name the
>> clustering key in the nc index. In other words, say you have a unique
>> clustered index on the "ID" column and want to create a nc index on
>> the "Data" column, then both below will do the exact same thing:
>> (data, id)
>> (data)
> Tibor,
> I just did a quick test. I created a clustered index on COL1 + COL2. I then created two other
> indexes: A unique, non-clustered on COL3 + COL1 + COL2 and a non-clustered on COL3.
> In sysindexes, the keycnt column shows as 5 for the first unique, non-clustered index and 3 for
> the non-unique index. The keys column is larger for the 5 keycnt column. However, the reserved
> pages are similar, supporting your theory.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com

create maintenance plan failed

I am running Windows Small Business server 2003 R2 and I've upgraded the
Sharepoint from MSDE to SQL 2005. I'm trying to create a Database
maintenance plan to backup the Sharepoint database but I'm getting an error
message. I've pasted the messaged below:Create maintenance plan failed.
TITLE: Maintenance Plan Wizard Progress
Create maintenance plan failed.
ADDITIONAL INFORMATION:
Create failed for JobStep 'Subplan'.
(Microsoft.SqlServer.MaintenancePlanTasks)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo. ExceptionTemplates.FailedOperationExceptionText&Ev tID=Create+JobStep&LinkId=20476[/url]
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
The specified '@.subsystem' is invalid (valid values are returned by
sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .1399&EvtSrc=MSSQLServer&EvtID=14234&LinkId=20476[ /url]
BUTTONS:
OK
===================================
Create failed for JobStep 'Subplan'.
(Microsoft.SqlServer.MaintenancePlanTasks)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo. ExceptionTemplates.FailedOperationExceptionText&Ev tID=Create+JobStep&LinkId=20476[/url]
Program Location:
at
Microsoft.SqlServer.Management.DatabaseMaintenance .MaintenancePlanSubPlan.AddAgentJob(ServerConnecti on localConnObj, String proxyName)
at
Microsoft.SqlServer.Management.DatabaseMaintenance .MaintenancePlanSubPlan..ctor(String
subplanName, String proxyAccount, Package package, ServerConnection
localConnObj)
at
Microsoft.SqlServer.Management.DatabaseMaintenance .MaintenancePlan.AddSubPlan(String subplanName, String proxyAccount)
at
Microsoft.SqlServer.Management.MaintenancePlanWiza rd.MaintenancePlanWizardForm.PerformActions()
===================================
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
Program Location:
at
Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String
sqlCommand, ExecutionTypes executionType)
at
Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
at
Microsoft.SqlServer.Management.Smo.ExecutionManage r.ExecuteNonQuery(StringCollection queries)
at
Microsoft.SqlServer.Management.Smo.SqlSmoObject.Ex ecuteNonQuery(StringCollection queries, Boolean includeDbContext)
at
Microsoft.SqlServer.Management.Smo.SqlSmoObject.Cr eateImplFinish(StringCollection createQuery, ScriptingOptions so)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.Cr eateImpl()
===================================
The specified '@.subsystem' is invalid (valid values are returned by
sp_enum_sqlagent_subsystems). (.Net SqlClient Data Provider)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .1399&EvtSrc=MSSQLServer&EvtID=14234&LinkId=20476[ /url]
Server Name: SERVER1\SHAREPOINT
Error Number: 14234
Severity: 16
State: 1
Procedure: sp_verify_subsystem
Line Number: 28
Program Location:
at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception,
Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject
stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQuer yTds(String
methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNo nQuery(DbAsyncResult
result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at
Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String
sqlCommand, ExecutionTypes executionType)
no, I didn't. I did some more searches and found some fixes for it.
Actually what fixed it for me was SQL 2005 SP1.
"Tibor Karaszi" wrote:

> Did you install Integration Services?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Gilbert" <Gilbert@.discussions.microsoft.com> wrote in message
> news:07FDD247-34D0-4F03-8BFB-E669E8E51829@.microsoft.com...
>
|||Hi Tibor
I have an issue on a newly buildt SQL2005 cluster. I didn't install the SSIS
at first but then added the service. However I still have the same problems
and I wonder if I need to reinstall the Management Tools in order to get it
to work?
I have a case with support and we verified that both nodes are running the
SSIS service but the creation/saving of Maintenance plans fails. Any
experience with this?
Rune
"Tibor Karaszi" wrote:

> Interesting. A Maint Plan in 2005 is an SSIS package, so I would expect a requirement for using 2005
> Maint Plans is to have SSIS installed. Perhaps they did some special handling of Maint Plans SSIS
> packages...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Gilbert" <Gilbert@.discussions.microsoft.com> wrote in message
> news:BDC49606-300E-4BEA-8CE1-083026C6C00F@.microsoft.com...
>

Create Directory in Instance Sql Server of another machine

My Sql Server is in Windows Server 2003 machine the name: Machine1

I want create a new directory in machine1 over in machine2 (my machine)

The code is:

Dim srv As New Server("SqlInstance")

Dim sFolder As String = srv.Settings.DefaultFile & "\NewDirectory"

IO.Directory.CreateDirectory(sFolder)

This not work because the sFolder report = D:\SqlData\NewDirectory and the Io.Directory try create in my local machine (I not have a drive D:\ in machine local).

How can I create a subdirectory in srv.settings.defaulfile Sql Server?

thanks,

Marsenne

Hi,

do you want to create a folder on a remote machine ?

you can either use a share to create this file (an administrative share perhpas, if you have the appropiate permissions:

System.IO.Directory.CreateDirectory(@.\\m2-jenss\hierrein\Test);

Or you can use WMI to create a folder on the remote machine:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wmisdk/wmi/win32_createfolderaction.asp

HTH, JEns Suessmeyer.

http://www.sqlserver2005.de

Create database permision denied in database ' master' (MS SQL SERVER, ERROR 262

Cn not do anything with my sql server, everything i trt to do i get this message, user does not have permision, etc, ,

I am running windows Vista Business, SQL SERVER 2005

so what going on here

SQL Server SP1 is not supported on Vista... U must have SQL Server SP2... but sp2 is in CTP version... SP2 is to be released soon...so wait for a while...

http://www.microsoft.com/sql/howtobuy/windowsvistasupport.mspx

Madhu

|||True and try to test it on a other Windows edition and see the user privileges are not an issue.|||Just tryed with SP2. Got the same error. This is fun. Can any Microsoft MVP enlighten us please?|||

Have a look at this article:

http://msdn2.microsoft.com/en-us/library/bb326612.aspx

Thanks
Laurentiu

Create Database on a Virtual Drive (Created with subst) Failed

When I try to add a (SQL Server 2005 Express Edition) database to my project (I'v tried a windows application and an ASP.NET application) in Visual Studio 2005 Professional Edition; It fails with an error like this :
"create database failed. some file names listed could not be created (...)"
but when I open the project from the real path It works.
Is this a bug? Is there any solution? (In many situations there is a need for working with virtual drives. There must be some work around ...)

Thanks

SQL Server does not recognize OS level 'mapped' drives.

You must use a 'actual' drive, or a SAN/NAS lun(drive).