Can't create a secondary index on a bit column

I am trying to create a secondary index on mult. columns
and one of them is a bit (I know you should not but that
is what they want). Using Enterprise Manager this does not
work, I get the following error:
Primary key or index cannot be created on column '<0s>'
because its data type is 'bit'.
If I use Query Analyzer it works fine.
My question is: Is this a bug in Enterprise Manager that
it does not let me or a bug in Query Analyzer that it does
let me?
Thanks.Don't know where is wrong. But you can check with QA's execution plan if
you have adequate data set -- use a situation that the new index is suitable
and run a query to see whether ther index is used. Or you can also sysindex
and sysindexkeys to look at.
Quentin
"Amy" <anonymous@.discussions.microsoft.com> wrote in message
news:030c01c3daf1$218abda0$a601280a@.phx.gbl...

quote:

---

> I am trying to create a secondary index on mult. columns
> and one of them is a bit (I know you should not but that
> is what they want). Using Enterprise Manager this does not
> work, I get the following error:
> Primary key or index cannot be created on column '<0s>'
> because its data type is 'bit'.
> If I use Query Analyzer it works fine.
> My question is: Is this a bug in Enterprise Manager that
> it does not let me or a bug in Query Analyzer that it does
> let me?
> Thanks.

---

|||Amy
There are some (using your word 'bugs') with EM
It does work with QA so be happy.
Note: You are right that it's a bad idea to have index on 'bit' column.
"Amy" <anonymous@.discussions.microsoft.com> wrote in message
news:030c01c3daf1$218abda0$a601280a@.phx.gbl...

quote:

---

> I am trying to create a secondary index on mult. columns
> and one of them is a bit (I know you should not but that
> is what they want). Using Enterprise Manager this does not
> work, I get the following error:
> Primary key or index cannot be created on column '<0s>'
> because its data type is 'bit'.
> If I use Query Analyzer it works fine.
> My question is: Is this a bug in Enterprise Manager that
> it does not let me or a bug in Query Analyzer that it does
> let me?
> Thanks.

---

Can't create a secondary index on a bit column

I am trying to create a secondary index on mult. columns
and one of them is a bit (I know you should not but that
is what they want). Using Enterprise Manager this does not
work, I get the following error:
Primary key or index cannot be created on column '<0s>'
because its data type is 'bit'.
If I use Query Analyzer it works fine.
My question is: Is this a bug in Enterprise Manager that
it does not let me or a bug in Query Analyzer that it does
let me?
Thanks.Don't know where is wrong. But you can check with QA's execution plan if
you have adequate data set -- use a situation that the new index is suitable
and run a query to see whether ther index is used. Or you can also sysindex
and sysindexkeys to look at.
Quentin
"Amy" <anonymous@.discussions.microsoft.com> wrote in message
news:030c01c3daf1$218abda0$a601280a@.phx.gbl...
> I am trying to create a secondary index on mult. columns
> and one of them is a bit (I know you should not but that
> is what they want). Using Enterprise Manager this does not
> work, I get the following error:
> Primary key or index cannot be created on column '<0s>'
> because its data type is 'bit'.
> If I use Query Analyzer it works fine.
> My question is: Is this a bug in Enterprise Manager that
> it does not let me or a bug in Query Analyzer that it does
> let me?
> Thanks.|||Amy
There are some (using your word 'bugs') with EM
It does work with QA so be happy.
Note: You are right that it's a bad idea to have index on 'bit' column.
"Amy" <anonymous@.discussions.microsoft.com> wrote in message
news:030c01c3daf1$218abda0$a601280a@.phx.gbl...
> I am trying to create a secondary index on mult. columns
> and one of them is a bit (I know you should not but that
> is what they want). Using Enterprise Manager this does not
> work, I get the following error:
> Primary key or index cannot be created on column '<0s>'
> because its data type is 'bit'.
> If I use Query Analyzer it works fine.
> My question is: Is this a bug in Enterprise Manager that
> it does not let me or a bug in Query Analyzer that it does
> let me?
> Thanks.

Cant create a foreign key using non_primary key fields

Howdy all !
I'm just a bit on the frustrated side cause I want to create a foreign key but SQL Server won't let me. :(

I have table A with a primary key of main_id
I have table B with a primary key of another_id

Table A has a corresponding field called another_id.

I'm trying to create a foreign key between Table A & Table B on another_id but since it's not the Primary Key in Table A I get the following error:

There are no primary or candidate keys in the referenced table 'A' that match the referencing column list in the foreign key 'fk_classB_classA'.

Am I missing something totally obvious here? Why should I have to create a foreign key on a primary key?

What I find interesting is that I can create the relationship from enterprise manager but when I script it out is when I run into problems.

Here's the script I'm using:

Begin Code

alter table B add
constraint fk_classB_classA foreign key
(classB) references A (classB)
on delete no action
on update no action

End Code

Any help is greatly appreciated.

tamAdd a UNIQUE constraint and then SQL Server will oblige. It won't let you create an FK relationship to a column that could have more than one row with the "target" value, due to the chaos that can cause.

-PatP|||Hey Pat,
Many thanks for the quick response and I'll give that a try, but could you explain to me why it does it from enterprise manager? If I go into diagrams and drag and drop from one table to the other it comes up as a foreign key not a unique constraint.

Is it actually creating a unique constraint behind the curtain and labeling it as a Foreign key?

I'm more curious than anything. To me it just doesn't make much sense.

Again, thanks for the answer.
tam|||I don't think EM is actually creating FK relationship on the columns that you think it does. Right-mouse click on the relationship and select Properties, and see what fields participate in the relationship.|||The two ideas (UNIQUE CONSTRAINT and Foreign Key) are related, but they are decidedly not the same thing.

A unique constraint means that the column(s) that the constraint applies to are unique within the table, only one row can exist with a particular value. As an example, you might have an employee table that has an EmployeeID column which is the Primary Key, and an SSN column that is Unique. There can be only one row with any given value of EmployeeID, and NULL values are never allowed. There can be NULL values for SSN (which is good, since some employees may not have one), but the database engine won't allow two rows to have the same value.

Various tables in your schema would link to your employee table using the EmployeeID as a Foreign Key. You might get a table from the IRS that has employee data organized by SSN, and you could make that SSN a Foreign Key to the employee table too (because it has a Unique constraint). Not every employee may have this detail data, but the detail can apply to only one employee.

You might get data from a shoe manufacturer telling you about their schwell new work shoes. Even if they provide information by shoe size, you can't make the shoe size a foreign key to employee, because in the employee table the shoe size column couldn't have a unique constraint (at least not in most companies anyway!).

-PatP|||UNIQUE constraint on SSN? Then there may be only 1 employee without a valid unique SSN, because UNIQUE constraint will allow only 1 NULL-valued row for that field.|||Well I'll be horny-swoggled! I'd forgotten that they'd changed that. Good catch!

Ok, so much for my sterling example then... It used to hold water, long ago and far away!

-PatP

Can't Connect to SQL db

Going a bit crazy. Have sqlserver all installed and running. I have a
security problem I think- have read many other news posts that SOUND
similar but e.g. My SQL Server configuration manager doesnt seem to
have seccurity options let alone offer the possibility of allowing a
'mix' of SQL/ windows requests.

For logon in SQL Server configuration manager 'built- in account'
local system is selected rather than the 'this account' option which
has stars in the password field and no username options.

This is myconnection string: kris\ is this local machine

myConnection = New SqlConnection("server=kris
\SQLEXPRESS;uid=sa;pwd=;database=pubs")

This is the error I get
System.Data.SqlClient.SqlException was unhandled
Class=14
ErrorCode=-2146232060
LineNumber=65536
Message="Login failed for user 'sa'. The user is not associated with
a trusted SQL Server connection."
Number=18452
Procedure=""
Server="kris\SQLEXPRESS"

Any ideas?DanWeaver (danofweaver@.googlemail.com) writes:

Quote:

Originally Posted by

This is myconnection string: kris\ is this local machine
>
myConnection = New SqlConnection("server=kris
\SQLEXPRESS;uid=sa;pwd=;database=pubs")
>
This is the error I get
System.Data.SqlClient.SqlException was unhandled
Class=14
ErrorCode=-2146232060
LineNumber=65536
Message="Login failed for user 'sa'. The user is not associated with
a trusted SQL Server connection."
Number=18452
Procedure=""
Server="kris\SQLEXPRESS"

Have you enabled you server for SQL authetnication? By default,
SQL Server permits only Window authentication.

Do you have any particular reason you want to use SQL authentication?
Else, change "uid=sa;pwd=" to "Integrated Security=SSPI".

Quote:

Originally Posted by

For logon in SQL Server configuration manager 'built- in account'
local system is selected rather than the 'this account' option which
has stars in the password field and no username options.

Note that his is about how SQL Server logs into Windows. That's a
different thing.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||

Quote:

Originally Posted by

Do you have any particular reason you want to use SQL authentication?
Else, change "uid=sa;pwd=" to "Integrated Security=SSPI".

Thanks, Erland. That helped move me on- in fact the first time I tried
it it seemed to work (though my record was not added- posibly as there
already existed a record with same id) Anyway- I cant experiment with
that cos shorlty after I could no longer connect- seems strange- I
checked the table after first successful run to see if it had updated
and ran the code again- got a message "another process is locking the
db"- great I thought- all is working- and closed the connection in
VS-
now though the connection gets stuck at login and says:

Cannot open database "pubs" requested by the login. The login failed.
Login failed for user 'KRISLAPTOP\Kris'.|||DanWeaver (danofweaver@.googlemail.com) writes:

Quote:

Originally Posted by

Quote:

Originally Posted by

>Do you have any particular reason you want to use SQL authentication?
>Else, change "uid=sa;pwd=" to "Integrated Security=SSPI".

>
Thanks, Erland. That helped move me on- in fact the first time I tried
it it seemed to work (though my record was not added- posibly as there
already existed a record with same id) Anyway- I cant experiment with
that cos shorlty after I could no longer connect- seems strange- I
checked the table after first successful run to see if it had updated
and ran the code again- got a message "another process is locking the
db"- great I thought- all is working- and closed the connection in
VS-
now though the connection gets stuck at login and says:
>
Cannot open database "pubs" requested by the login. The login failed.
Login failed for user 'KRISLAPTOP\Kris'.

Seems like that either your user is not present in the pubs database,
or the database has somehow gone away.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks, Erland. Something fishy was ocurring with the dbs- deleted
them and reconnected and set permissions again- seems to work now.

Cheers, more challenging questions (when I am more informed!) will
probably follow.

Dan

Quote:

Originally Posted by

Seems like that either your user is not present in the pubs database,
or the database has somehow gone away.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Can't connect to SeptCTP x64

I've built and deployed cubes on a x64 server, with x64 SQL2005 SeptCTP.

Works pretty much fine. I can run 32 bit Excel on the server, and browse the cubes no problem.

However, if I fire up 32 bit Excel on my 32 bit client machine, and go through the MultiDimensional data source wizard to connet to the cubes, after I put the server name in, it throws this error:

"The peer prematurely closed the connection"

Then this error:

"An error was encountered in the transport layer"

Like a fool, I went from 32 bit to 64 bit and from JunCTP to SeptCTP at the same time, so I don't know which is to blame.

I'm aware of the classic "re-register msolap90.dll on the client" fix, and I've tried it, and it aint that.

Any ideas?

Some more info on this:

If I open up SeptCTP Management Studio on a 32 bit client machine, connect to the Analysis Server on the 64 bit server, it connects. I can see the database on teh x64 machine, and below that, I can see the Data Source, DSVs, Cubes etc.

BUT

When I right click on cube, and try to browse, it fails with the following error:

Cannot show requested dialog.

ADDITIONAL INFORMATION:

Deserialization failed: The 'EditionID' element in the 'http://schemas.microsoft.com/analysisservices/2003/engine' namespace is unexpected. (Microsoft.AnalysisServices)

Exactly the same error presents whether I try to get the properties of a cub, data source, dsv, whatever.

I do not understand this at all.

|||I got the same error on deploying my project. It works fine on Jun CTP but got the error in Sep CTP and the MSSQL 2005 evaluation copy. Any advise? Thanks in advance!

Can't connect to SeptCTP x64

I've built and deployed cubes on a x64 server, with x64 SQL2005 SeptCTP.

Works pretty much fine. I can run 32 bit Excel on the server, and browse the cubes no problem.

However, if I fire up 32 bit Excel on my 32 bit client machine, and go through the MultiDimensional data source wizard to connet to the cubes, after I put the server name in, it throws this error:

"The peer prematurely closed the connection"

Then this error:

"An error was encountered in the transport layer"

Like a fool, I went from 32 bit to 64 bit and from JunCTP to SeptCTP at the same time, so I don't know which is to blame.

I'm aware of the classic "re-register msolap90.dll on the client" fix, and I've tried it, and it aint that.

Any ideas?

Some more info on this:

If I open up SeptCTP Management Studio on a 32 bit client machine, connect to the Analysis Server on the 64 bit server, it connects. I can see the database on teh x64 machine, and below that, I can see the Data Source, DSVs, Cubes etc.

BUT

When I right click on cube, and try to browse, it fails with the following error:

Cannot show requested dialog.

ADDITIONAL INFORMATION:

Deserialization failed: The 'EditionID' element in the 'http://schemas.microsoft.com/analysisservices/2003/engine' namespace is unexpected. (Microsoft.AnalysisServices)

Exactly the same error presents whether I try to get the properties of a cub, data source, dsv, whatever.

I do not understand this at all.

|||I got the same error on deploying my project. It works fine on Jun CTP but got the error in Sep CTP and the MSSQL 2005 evaluation copy. Any advise? Thanks in advance!