_hypmv error

I just tried to update a column type from varchar(32) to varchar(64) in a
table,but got some dependency error like:
The object '_hypmv_0' is dependent on column 'EMAILREFERENCE'.
The object '_hypmv_0_9625' is dependent on column 'EMAILREFERENCE'.
The object '_hypmv_0_7898' is dependent on column 'EMAILREFERENCE'.
The object '_hypmv_0_6539' is dependent on column 'EMAILREFERENCE'.
But,in the dependency listing for the table I cant view any of the above,but
I have
got admin access.What are these dependecies for?any pointer will be greatly
appreciated.
A wild guess is that someone has been running Index Tuning izard which has created "Hypothetical
Materialized Views" (called Indexed Views), and these from some reason haven't been dropped. I'd
check if these are views and if your applications aren't using these, consider dropping them, using
DROP VIEW command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"c_ani" <cani@.discussions.microsoft.com> wrote in message
news:53B89EB6-C82A-49B2-96C2-84A98CB02FC0@.microsoft.com...
>I just tried to update a column type from varchar(32) to varchar(64) in a
> table,but got some dependency error like:
> The object '_hypmv_0' is dependent on column 'EMAILREFERENCE'.
> The object '_hypmv_0_9625' is dependent on column 'EMAILREFERENCE'.
> The object '_hypmv_0_7898' is dependent on column 'EMAILREFERENCE'.
> The object '_hypmv_0_6539' is dependent on column 'EMAILREFERENCE'.
> But,in the dependency listing for the table I cant view any of the above,but
> I have
> got admin access.What are these dependecies for?any pointer will be greatly
> appreciated.
sql

_hypmv error

I just tried to update a column type from varchar(32) to varchar(64) in a
table,but got some dependency error like:
The object '_hypmv_0' is dependent on column 'EMAILREFERENCE'.
The object '_hypmv_0_9625' is dependent on column 'EMAILREFERENCE'.
The object '_hypmv_0_7898' is dependent on column 'EMAILREFERENCE'.
The object '_hypmv_0_6539' is dependent on column 'EMAILREFERENCE'.
But,in the dependency listing for the table I cant view any of the above,but
I have
got admin access.What are these dependecies for?any pointer will be greatly
appreciated.A wild guess is that someone has been running Index Tuning izard which has created "Hypothetical
Materialized Views" (called Indexed Views), and these from some reason haven't been dropped. I'd
check if these are views and if your applications aren't using these, consider dropping them, using
DROP VIEW command.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"c_ani" <cani@.discussions.microsoft.com> wrote in message
news:53B89EB6-C82A-49B2-96C2-84A98CB02FC0@.microsoft.com...
>I just tried to update a column type from varchar(32) to varchar(64) in a
> table,but got some dependency error like:
> The object '_hypmv_0' is dependent on column 'EMAILREFERENCE'.
> The object '_hypmv_0_9625' is dependent on column 'EMAILREFERENCE'.
> The object '_hypmv_0_7898' is dependent on column 'EMAILREFERENCE'.
> The object '_hypmv_0_6539' is dependent on column 'EMAILREFERENCE'.
> But,in the dependency listing for the table I cant view any of the above,but
> I have
> got admin access.What are these dependecies for?any pointer will be greatly
> appreciated.

[varchar] (100) and empty space

Hello

I am creating the following table

CREATE TABLE [dbo].[myT] (
[name] [varchar] (100) NULL
)

if I do

INSERT INTO myT (name) VALUES ('A')

i get in the table a column with

A ...

the 100 char place is full even if there is a data with only 1 char

how is it possible to avoid it ?
I want 100 char maximum but not full with nothing

thank you for helpingWhat do you get when you run this?:

select len([Name]), '[' + [Name] + ']' from myT

Name is a reserved word, and so it is not a good label for a column, but I don't think this would cause the problem you are seeing.|||the 100 char place is full even if there is a data with only 1 charHow do you know?

Could this be a display issue of whatever client program you use to display the data?
Only CHAR columns are padded to the full length not VARCHAR columns|||I know it when I fill a formula with datas I am getting 99 empty spaces

blindman it is an exemple i have no column named [name]

if i run select datalength(name) from myT
i am getting 200 2 times (100)|||Strange I just ran all the scripts above & everything looks good to me

I get 1 from select datalength(name) from myT

anselme

take a deep breath reboot and start running these scripts again

If this does'nt work

Reply here and

stop using the word Char as in the 100 char place is full even if there is a data with only 1 char

explain what query editor you are using

explain what database you are using

run the script exactly as blindman suggests select len([Name]), '[' + [Name] + ']' from myT and tell us exactly the output

GW|||blindman it is an exemple i have no column named [name]You have some sort of typo, and if you expect any more help on this you need to post the actual code so we don't waste more of our time.|||I know it when I fill a formula with datas I am getting 99 empty spacesSQL Server does not have "formulas" to be "filled" (whatever that should mean).
What exactly are you doing?|||i agree with Gwilliy everything looks cool for me too....
varchar will only occupy the required number of space...
however if still problems persist you can always use LTRIM and RTRIM to get rid of the remaining whitespaces

so your query will be something like
select LTRIM(RTRIM([name])) from myT

thats the most we can get you....

However the fact that the 100 varchar place is full still mystifies me|||However the fact that the 100 varchar place is full still mystifies me
I suspect this is a front end issue.
What is this "formula" thing he is filling in?

[help] ALTER TABLE: error message 4929

Hi,

I've the following error message:

-----
ALTER TABLE [dbo].[AnagraficoAutomezzi]
ALTER COLUMN [CodiceXTrascodifica] varchar(10)
Go

Server: messaggio 4929, livello 16, stato 1, riga 1
Cannot alter table 'AnagraficoAutomezzi' because it is being published for
replication.
-----

This is only part of a script with much more alter table…

How can I lunch the script without this problem?

Thanks for your help!
Giacomo

--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/Well, you cant alter schema involved in replication all that easily. For
this, you can use sp_addmergecolumn (not certain if the name is correct but
its close). If you post other code samples we can try to see what can be
done

MC

"Giacomo" <no_spam@.grazie.itwrote in message
news:op.tgst4zpwt6znx9@.tpprog002.ccvtech.com...

Quote:

Originally Posted by

>
Hi,
>
I've the following error message:
>
-----
ALTER TABLE [dbo].[AnagraficoAutomezzi]
ALTER COLUMN [CodiceXTrascodifica] varchar(10)
Go
>
Server: messaggio 4929, livello 16, stato 1, riga 1
Cannot alter table 'AnagraficoAutomezzi' because it is being published for
replication.
-----
>
This is only part of a script with much more alter table.
>
How can I lunch the script without this problem?
>
Thanks for your help!
Giacomo
>
>
--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/

|||I've no more code sample to post, it's just a script with some ALTER TABLE
to change columns name or type.
We use this script to update client's DB. But one of them use replication
and we are always in trouble to update this DB.|||One of the downsides to replication is that is complicates subsequent schema
changes. As ML mentioned, you need to run the appropriate sp_repl* procs
instead of ALTER TABLE. The details vary depending on the type of
replication and the changes you are making.

If your client established replication outside the scope of your normal
support agreement, I suggest you have then remove replication so that your
normal script can be run and then reestablish replication afterwards. It's
unreasonable to expect your upgrade script to handle a replicated schema
unless you have detailed knowledge of the replication topology.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Giacomo" <no_spam@.grazie.itwrote in message
news:op.tgt5pk06t6znx9@.tpprog002.ccvtech.com...

Quote:

Originally Posted by

I've no more code sample to post, it's just a script with some ALTER TABLE
to change columns name or type.
We use this script to update client's DB. But one of them use replication
and we are always in trouble to update this DB.

|||Just to add something to my previous response. You could try using EM to
implement some of the changes, however I really wouldnt recommend this.
Changing schema for database involved in replication isnt something you
should do lightly.
Thats why I asked if you could post additional code that doesnt work for you
so we could suggest something.

MC

PS. Dan, its MC (Marko Culo) ;).

"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.netwrote in message
news:i4sUg.1248$NE6.754@.newssvr11.news.prodigy.com ...

Quote:

Originally Posted by

One of the downsides to replication is that is complicates subsequent

schema

Quote:

Originally Posted by

changes. As ML mentioned, you need to run the appropriate sp_repl* procs
instead of ALTER TABLE. The details vary depending on the type of
replication and the changes you are making.
>
If your client established replication outside the scope of your normal
support agreement, I suggest you have then remove replication so that your
normal script can be run and then reestablish replication afterwards.

It's

Quote:

Originally Posted by

unreasonable to expect your upgrade script to handle a replicated schema
unless you have detailed knowledge of the replication topology.
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
>
"Giacomo" <no_spam@.grazie.itwrote in message
news:op.tgt5pk06t6znx9@.tpprog002.ccvtech.com...

Quote:

Originally Posted by

I've no more code sample to post, it's just a script with some ALTER

TABLE

Quote:

Originally Posted by

Quote:

Originally Posted by

to change columns name or type.
We use this script to update client's DB. But one of them use

replication

Quote:

Originally Posted by

Quote:

Originally Posted by

and we are always in trouble to update this DB.

>
>

|||PS. Dan, its MC (Marko Culo) ;).

Oops. Sorry, Marko.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"MC" <marko_culoNOSPAM@.yahoo.comwrote in message
news:eg0r1t$bqq$1@.ss408.t-com.hr...

Quote:

Originally Posted by

Just to add something to my previous response. You could try using EM to
implement some of the changes, however I really wouldnt recommend this.
Changing schema for database involved in replication isnt something you
should do lightly.
Thats why I asked if you could post additional code that doesnt work for
you
so we could suggest something.
>
>
MC
>
PS. Dan, its MC (Marko Culo) ;).
>
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.netwrote in message
news:i4sUg.1248$NE6.754@.newssvr11.news.prodigy.com ...

Quote:

Originally Posted by

>One of the downsides to replication is that is complicates subsequent

schema

Quote:

Originally Posted by

>changes. As ML mentioned, you need to run the appropriate sp_repl* procs
>instead of ALTER TABLE. The details vary depending on the type of
>replication and the changes you are making.
>>
>If your client established replication outside the scope of your normal
>support agreement, I suggest you have then remove replication so that
>your
>normal script can be run and then reestablish replication afterwards.

It's

Quote:

Originally Posted by

>unreasonable to expect your upgrade script to handle a replicated schema
>unless you have detailed knowledge of the replication topology.
>>
>--
>Hope this helps.
>>
>Dan Guzman
>SQL Server MVP
>>
>"Giacomo" <no_spam@.grazie.itwrote in message
>news:op.tgt5pk06t6znx9@.tpprog002.ccvtech.com...

Quote:

Originally Posted by

I've no more code sample to post, it's just a script with some ALTER

TABLE

Quote:

Originally Posted by

Quote:

Originally Posted by

to change columns name or type.
We use this script to update client's DB. But one of them use

replication

Quote:

Originally Posted by

Quote:

Originally Posted by

and we are always in trouble to update this DB.

>>
>>

>
>

@variable in SELECT ... WHERE ... IN clause

Is there a way to create a query that can be like:

DECLARE @.group_id_list varchar(100)

SET @.group_id_list='100,101,150'

SELECT * FROM abc WHERE abc_id IN (@.group_id_list)

I get the error "Syntax error converting the varchar value '100,101,150' to a column of data type int.

Do I need to resort to a dynamic SQL statement?

You do.|||Not if you don't want to. In many cases I prefer to use a udf that I've created that takes a comma-delimited varchar and returns a table. Then you can either join on the table to limit your results, or you can use IN (SELECT id FROM Split(@.param,DEFAULT) alias1) in your where clause. The second param into my Split UDF is what the separator is (default is comma).|||

Thank you. I actually ended up doing something similar (never thought of using a UDF):

DECLARE @.groups TABLE (group_id int)

I ran a while loop inserting the values into the @.groups table, then used:

SELECT * FROM abc WHERE abc_id IN (SELECT group_id FROM @.groups)

I don't know if it's efficient, but my dynamic SQL statement was going to exceed 14k in length!

@NUM_MEMBERS = EXEC (USE +@X + ; SELECT COUNT(Member_Name) FROM Logins) -HOW?

DECLARE @.X VARCHAR(10)
DECLARE @.NUM_MEMBERS SMALLINT
SELECT @.X = 'other_db'

@.NUM_MEMBERS = EXEC ('USE ' +@.X + '; SELECT COUNT(Member_Name) FROM Logins')

I get an error when I try to store the result in @.NUM_MEMBERS. :/ I've been looking all over for the correct syntax. Can anyone help?

ERROR:
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near '@.NUM_MEMBERS'.

thx n advits probably better just to use sp_executesql, eh?|||I think you have to to get results back in variables from a dynamic SQL string.

@@sqlstatus error

Is @.@.sqlstatus not a global variable. Why am I getting this error.

Code Snippet

declare @.obj_type varchar(50);

declare @.obj_name varchar(50);

declare @.schema varchar(100);

select @.schema = 'new';

declare schema_object cursor for

select obj.type, obj.name

from sys.objects obj join sys.schemas s on (s.schema_id = obj.schema_id)

where s.name = @.schema

open schema_object

fetch schema_object into @.obj_type, @.obj_name;

while (@.@.sqlstatus = 0)

begin

fetch schema_object into @.obj_type, @.obj_name;

end

close schema_object

Msg 137, Level 15, State 2, Line 14

Must declare the scalar variable "@.@.sqlstatus".

I think u must use @.@.Fetch_Status instead of @.@.sqlstatus but I'm not sure about that

just give a try