can't alter column to NOT NULL

If I run the following 3 statements (on sql server 2000):
create table foo (x nvarchar(128))
create unique index ix_foo on foo (x)
alter table foo alter column x nvarchar(128) not null
I get this error:
Server: Msg 5074, Level 16, State 8, Line 1
The index 'ix_foo' is dependent on column 'x'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN x failed because one or more objects access this
column.
Looking in the documentation it says "The altered column cannot be...Used in
an index, unless the column is a varchar, nvarchar, or varbinary data type,
the data type is not changed, and the new size is equal to or larger than
the old size."
Since the column is nvarchar, the data type is not changed, and the new size
is equal to the old size, this should be allowed.
Is the documentation just wrong or have I misunderstood something?
AndyAndy Fish wrote:

> Since the column is nvarchar, the data type is not changed, and the
> new size is equal to the old size, this should be allowed.
> Is the documentation just wrong or have I misunderstood something?
You need to drop the index, alter the column and then recreate the
index, that's the only way.
HTH,
Stijn Verrept.|||Disable the foreign key constraints / drop them. Alter the table (make
sure there are no NULL values in there). Activate recreate the foreign
key relationship.
HTH, Jens Suessmeyer.sql

Cannot set a Variable from a select statement that contains a variable? Help Please

I am trying to set a vaiable from a select statement

DECLARE @.VALUE_KEEP NVARCHAR(120),

@.COLUMN_NAME NVARCHAR(120)

SET @.COLUMN_NAME = (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'CONTACTS' AND COLUMN_NAME = 'FIRSTNAME')

SET @.VALUE_KEEP = (SELECT @.COLUMN_NAME FROM CONTACTS WHERE CONTACT_ID = 3)

PRINT @.VALUE_KEEP

PRINT @.COLUMN_NAME

RESULTS

-

FirstName <--@.VALUE_KEEP

FirstName <--@.COLUMN_NAME

SELECT @.COLUMN_NAME FROM CONTACTS returns: FirstName

SELECT FirstName from Contacts returns: Brent

How do I make this select statement work using the @.COLUMN_NAME variable?

Any help greatly appreciated!

Your second SET statement is just applying the value of @.COLUMN_NAME to @.VALUE_KEEP. Based on the hardcoding in the first set statement I'm not sure exactly what you would be achieving with the variable use. If you are just trying to select a column dynamically from a given table you would need to use dynamic sql. Syntax something like...

DECLARE @.myVariable varchar(50), @.sql nvarchar(max) --or 4000 if using SQL 2000

SET @.myVariable = 'FirstName'

SET @.sql = 'SELECT ' + @.myVariable + ' FROM dbo.myTable WHERE myColumn = myColumn'

EXEC sp_executesql @.sql

That said, I would be very wary of using this approach in an application as there are security and maintainability issues with dynamic sql.

|||

I am passing this to coalesce()

my goal is to dynamically loop through the columns using coalesce() that is part of my stored procedure for de-duplicating a database. Since the columns can change I wanted to pull the columns and table dynamically.

I compare the records of the duplicates to update any null fields and want to do something like

coalesce(@.Record_Being_Kept, @.Record_Being_Replaced)

so two things...

I want the values in the coalesce ('Brent', 'Brent) when it is comparing the firstnames "Obviously this is much more applicable with the phone and email info" to Merge the Dupes.

I don't know if there is a way to pass EXEC sp_executesql @.sql to coalesce() ?

Cannot retrieve data in utf-8 from php

Hi.

I have a IIS server with php3 installed. I have SQL server database
and data stored in unicode format (nvarchar fields). In header of my
php I have the meta: <meta HTTP-EQUIV="content-type"
CONTENT="text/html; charset=UTF-8">.

I have the following problem:

- I trie to do a query using ODBC (version 3.525.1022.0) and SQL
Server odbc driver (version 200.85.1022.00). The query doesn't
retrieve information in utf format and accents and extra characters
aren't showed correctly.

- I trie to di the query usin native OLE DB. I use mssql funtions and
the problem is the same.

If I install php4 and I use "new
COM("ADODB.Connection",NULL,CP_UTF8);", then it works correctly.

Anybody knows if its posible to retrieve data from sql-server in utf-8
format using php3?

I see in other postings that odbc driver only accept utf-8 in version
3.7 or higher? Is it true? If is true, where I can download odbc
driver 3.7?

Thank's in advance.
Miki.Miki (miquelpl2@.hotmail.com) writes:
> I see in other postings that odbc driver only accept utf-8 in version
> 3.7 or higher? Is it true? If is true, where I can download odbc
> driver 3.7?

First, as far as I know, the ODBC driver for SQL Server does not handle
UTF-8 at all, since SQL Server does not support UTF-8, but stores
all Unicode data as UCS-2. See http://support.microsoft.com/?id=232580.

ODBC is part of the MDAC, and with any recently new Windows machine,
you have an MDAC version which includes ODBC 3.7 or later. If you have
some old NT4 platform, you may have to look for something news.

Go to http://www.microsoft.com/downloads/...?displaylang=en,
and pick MDAC. You will have to check the versions that they support
your OS.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Cannot resolve the collation conflict between

Hi
Your report's query contains JOIN on varchar/nvarchar columns which have
different collation
Try
select * from table join anothertable on table.col =anothertable.column
COLLATE danish_norwegian_ci_as
"H.Gjerde" <hg@.norspace.no> wrote in message
news:%23zJ%23Lv5PHHA.2468@.TK2MSFTNGP06.phx.gbl...
> Hi
> I have a new DB, in this new DB I have import and copy some data from the
> old DB, among others, a report
> When i try to open the report, I get this message
> - Cannot resolve the collation conflict between
> "SQL_Latin1_General_CP1_CI_AS" and "danish_norwegian_ci_as" in the equal
>
> Any ideas.
> Thanks
>
Hi
I have no idea. I don't know Crystal Report
"H.Gjerde" <hg@.norspace.no> wrote in message
news:eeyQ5F6PHHA.3344@.TK2MSFTNGP02.phx.gbl...
> Thank you
> It help, in my plain sql script
> But I stil have the problem, because I not realy sure where to put it in
> my CrystalReport. The report is predefined.
> ...? :|
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uHN$g25PHHA.3268@.TK2MSFTNGP03.phx.gbl...
>
|||can you create SP and call it in Crystal Report
Regards
Amish shah
http://shahamishm.tripod.com
On Jan 24, 3:31 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:[vbcol=seagreen]
> Hi
> I have no idea. I don't know Crystal Report
> "H.Gjerde" <h...@.norspace.no> wrote in messagenews:eeyQ5F6PHHA.3344@.TK2MSFTNGP02.phx.gbl. ..
>
>
>
>