refreshview removes user defined functions from sysdepends

When I create a procedure that references a user defined procedure it
appears in sysdepends, but disapears after being refreshed. Is this
expected and is there an alternative to sp_refreshview that correctly
refreshes sysdepends
--0--0--
print 'drop depenencies'
go
drop view depends_test
go
print 'create view'
go
create view depends_test as
select
dbo.ufJsmTranslate('test') As test_column
from
(select 1 one) test
go
print 'check depenencies'
go
sp_depends depends_test
go
print 'refresh'
go
sp_refreshview depends_test
go
print 'check depenencies'
go
sp_depends depends_test
--0--0--
drop depenencies
create view
check depenencies
In the current database, the specified object references the following:
name type updated selected column
-- -- -- -- --
dbo.ufJsmTranslate scalar function no no
refresh
check depenencies
Object does not reference any object, and no objects reference it.
--0--0--May be this is a bug, It seems that sp_refreshview does not update reference
s
to udfs. Here I have a script with the same problem. I also tried to create
the udf and view with the SCHEMABINDING property, everything was ok, except
that sp_refresh is giving me an error when I try to refresh the view.
-- same problem
use northwind
go
create table t(colA int)
go
create function dbo.ufn_funct1 (
@.i int
)
returns int
as
begin
return (@.i)
end
go
create view myview
as
select dbo.ufn_funct1(colA) as colA from t
go
exec sp_depends myview
go
exec sp_refreshview myview
go
exec sp_depends myview
go
drop view myview
go
drop function dbo.ufn_funct1
go
drop table t
go
-- Tried to fix it using schemabinfing
-- sp_refreshview is giving me an error
use northwind
go
create table t(colA int)
go
create function dbo.ufn_funct1 (
@.i int
)
returns int
with schemabinding
as
begin
return (@.i)
end
go
create view dbo.myview
with schemabinding
as
select dbo.ufn_funct1(colA) as colA from dbo.t
go
exec sp_depends 'dbo.myview'
go
exec sp_refreshview 'dbo.myview'
go
-- Server: Msg 208, Level 16, State 8, Procedure sp_refreshview, Line 1
-- Invalid object name 'dbo.myview'.
exec sp_depends 'dbo.myview'
go
drop view dbo.myview
go
drop function dbo.ufn_funct1
go
drop table t
go
AMB
"bilbo.baggins@.freesurf.ch" wrote:

> When I create a procedure that references a user defined procedure it
> appears in sysdepends, but disapears after being refreshed. Is this
> expected and is there an alternative to sp_refreshview that correctly
> refreshes sysdepends
> --0--0--
> print 'drop depenencies'
> go
> drop view depends_test
> go
> print 'create view'
> go
> create view depends_test as
> select
> dbo.ufJsmTranslate('test') As test_column
> from
> (select 1 one) test
> go
> print 'check depenencies'
> go
> sp_depends depends_test
> go
> print 'refresh'
> go
> sp_refreshview depends_test
> go
> print 'check depenencies'
> go
> sp_depends depends_test
> --0--0--
> drop depenencies
> create view
> check depenencies
> In the current database, the specified object references the following:
> name type updated selected column
> -- -- -- -- --
> dbo.ufJsmTranslate scalar function no no
> refresh
> check depenencies
> Object does not reference any object, and no objects reference it.
> --0--0--
>|||Correction,

> May be this is a bug, It seems that sp_refreshview does not update referen
ces
> to udfs. Here I have a script with the same problem. I also tried to creat
e
> the udf and view with the SCHEMABINDING property, everything was ok, excep
t
> that sp_refresh is giving me an error when I try to refresh the view.
..., except that sp_refreshview ...

> -- Server: Msg 208, Level 16, State 8, Procedure sp_refreshview, Line 1
> -- Invalid object name 'dbo.myview'.
> exec sp_depends 'dbo.myview'
> go
-- Server: Msg 208, Level 16, State 8, Procedure sp_refreshview, Line 1
-- Invalid object name 'dbo.myview'.
exec sp_refreshview 'dbo.myview'
go
AMB
"Alejandro Mesa" wrote:
> May be this is a bug, It seems that sp_refreshview does not update referen
ces
> to udfs. Here I have a script with the same problem. I also tried to creat
e
> the udf and view with the SCHEMABINDING property, everything was ok, excep
t
> that sp_refresh is giving me an error when I try to refresh the view.
> -- same problem
> use northwind
> go
> create table t(colA int)
> go
> create function dbo.ufn_funct1 (
> @.i int
> )
> returns int
> as
> begin
> return (@.i)
> end
> go
> create view myview
> as
> select dbo.ufn_funct1(colA) as colA from t
> go
> exec sp_depends myview
> go
> exec sp_refreshview myview
> go
> exec sp_depends myview
> go
> drop view myview
> go
> drop function dbo.ufn_funct1
> go
> drop table t
> go
> -- Tried to fix it using schemabinfing
> -- sp_refreshview is giving me an error
> use northwind
> go
> create table t(colA int)
> go
> create function dbo.ufn_funct1 (
> @.i int
> )
> returns int
> with schemabinding
> as
> begin
> return (@.i)
> end
> go
> create view dbo.myview
> with schemabinding
> as
> select dbo.ufn_funct1(colA) as colA from dbo.t
> go
> exec sp_depends 'dbo.myview'
> go
> exec sp_refreshview 'dbo.myview'
> go
> -- Server: Msg 208, Level 16, State 8, Procedure sp_refreshview, Line 1
> -- Invalid object name 'dbo.myview'.
> exec sp_depends 'dbo.myview'
> go
> drop view dbo.myview
> go
> drop function dbo.ufn_funct1
> go
> drop table t
> go
>
> AMB
>
> "bilbo.baggins@.freesurf.ch" wrote:
>

Refreshing a User Defined Type

Hi,
Using a User Define Type (a class written in C# for use in SQL 2005).
I have added my assembly and created a new Type and tested inserting and
selecting.
I now need to add a new field to my class.
How can I update the assembly and the type without disrupting the data
that is currently stored under the UDT.
Many Thanks in Advance
Stuart
*** Sent via Developersdex http://www.examnotes.net ***Stuart Ferguson <stuart_ferguson1@.btinternet.com> wrote in
news:exAtpR7OGHA.3856@.TK2MSFTNGP12.phx.gbl:

> Using a User Define Type (a class written in C# for use in SQL 2005).
> I have added my assembly and created a new Type and tested inserting
> and selecting.
> I now need to add a new field to my class.
> How can I update the assembly and the type without disrupting the data
> that is currently stored under the UDT.
>
Unless you have created the UDT with Format.UserDefined you can't. If
you have created it with UserDefined you need to, in your Read method,
cater for the fact that the new field may not be available. Then you run
ALTER ASSEMBLY and you use the WITH UNCHECKED DATA option.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||"Niels Berglund" <nielsb@.develop.com> wrote in message
news:Xns9777AABB8FC2Bnielsbdevelopcom@.20
7.46.248.16...
> Unless you have created the UDT with Format.UserDefined you can't. If
> you have created it with UserDefined you need to, in your Read method,
> cater for the fact that the new field may not be available. Then you run
> ALTER ASSEMBLY and you use the WITH UNCHECKED DATA option.
Doing this is not a good idea for a number of reasons. It will only work if
your UDT is IsFixedLength = false. Any DBCC CheckTable command on your UDT
will now fail. If your UDT is IsByteOrdered=True, you will end up with 2
udts that have the same value but with different byte representation,
potentially returning wrong results in queries that filter on your UDT.
Additional reasons this is a bad idea are left unspecified.
A better approach is to make a new column and copy your UDT data into it in
either binary or string format. Drop the type and create it again from your
new assembly and reparse the old udt data into the new UDT column. This may
seem like a lot more work, but it's the only way to do it right.
Steven|||"Steven Hemingray [MSFT]" <stevehem@.online.microsoft.com> wrote in
news:e$Y15v9OGHA.3164@.TK2MSFTNGP11.phx.gbl:

> Doing this is not a good idea for a number of reasons. It will only
> work if your UDT is IsFixedLength = false. Any DBCC CheckTable
> command on your UDT will now fail.
Well, I never said it was a good idea :-)

> If your UDT is IsByteOrdered=True,
> you will end up with 2 udts that have the same value but with
> different byte representation, potentially returning wrong results in
> queries that filter on your UDT.
Hmm, can you elaborate on that; if my read method caters for missing a
field, how can two udt's with the same value have different byte
representations?
[SNIP]
> A better approach is to make a new column and copy your UDT data into
> it in either binary or string format. Drop the type and create it
> again from your new assembly and reparse the old udt data into the new
> UDT column. This may seem like a lot more work, but it's the only way
> to do it right.
OK, so when you say re=parse the data, I assume you mean that the user
has to - before inserting the old data into the new column - either
update the old data so it contains some bogus value for the missing
field, or have the write method handle it?
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||Thanks for the replies guys.
However how would i handle the case where a field was removed from the
UDT, would i then have to copy all the fields individually when copying
the class to another column ?
Stuart
*** Sent via Developersdex http://www.examnotes.net ***|||Stuart Ferguson <stuart_ferguson1@.btinternet.com> wrote in
news:u1v2IoGPGHA.740@.TK2MSFTNGP12.phx.gbl:

> However how would i handle the case where a field was removed from the
> UDT, would i then have to copy all the fields individually when
> copying the class to another column ?
>
Well, as I wrote in my original reply, the easiest way is that you
handle it in your read and write methods. However, if you copy the data
to a separate table, then - in this scenario - you should copy it as the
string representation. Then you would need to update that data and take
away the removed field. Finally you insert the data back into the table
with the updated type.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||"Niels Berglund" <nielsb@.develop.com> wrote in message
news:Xns97786A60D5CDAnielsbdevelopcom@.20
7.46.248.16...
> "Steven Hemingray [MSFT]" <stevehem@.online.microsoft.com> wrote in
> news:e$Y15v9OGHA.3164@.TK2MSFTNGP11.phx.gbl:
> Hmm, can you elaborate on that; if my read method caters for missing a
> field, how can two udt's with the same value have different byte
> representations?
What if you run Alter Assembly and your read method doesn't get called
again? When queries refer to your IsByteOrdered udt, the udt isn't
instantiated so your read method isn't called.
--Assume Point is Format.UserDefined, IsByteOrdered=True,
IsFixedLength=False, and MaxByteSize leaves enough room for the alter
assembly expansion
CREATE TABLE DataPoints(c1 Point)
go
insert into DataPoints values(convert(Point, '(2,2)'))
go
--now alter the assembly that contains Point to add a new field to the type
ALTER ASSEMBLY Point_Assembly
go
--This won't return any rows because the bytes of the new udt Point for
(2,2) are different than the bytes for the old udt Point (2,2)
select * from DataPoints where c1 = convert(Point, '(2,2)')
In order for this to work, you'd need to write a stored-proc to go through
and 'refresh' your udt every place it is persisted. If you're going to do
this, you might as well do it the safe way and drop the type to ensure that
you catch all cases and don't hit any surprises.

> OK, so when you say re=parse the data, I assume you mean that the user
> has to - before inserting the old data into the new column - either
> update the old data so it contains some bogus value for the missing
> field, or have the write method handle it?
I think you mean the Read method, but that's correct. If the String
representation of the type doesn't change, then the user could go through
the Parse method instead without requiring additional work.
Obviously, changing the serialization of your UDT when it's already in use
is something to be avoided if at all possible.
Steven|||"Steven Hemingray [MSFT]" <stevehem@.online.microsoft.com> wrote in
news:OEBRH7IPGHA.2440@.TK2MSFTNGP11.phx.gbl:

> What if you run Alter Assembly and your read method doesn't get called
> again? When queries refer to your IsByteOrdered udt, the udt isn't
> instantiated so your read method isn't called.
> --Assume Point is Format.UserDefined, IsByteOrdered=True,
> IsFixedLength=False, and MaxByteSize leaves enough room for the alter
> assembly expansion
> CREATE TABLE DataPoints(c1 Point)
> go
> insert into DataPoints values(convert(Point, '(2,2)'))
> go
> --now alter the assembly that contains Point to add a new field to the
> type ALTER ASSEMBLY Point_Assembly
> go
> --This won't return any rows because the bytes of the new udt Point
> for (2,2) are different than the bytes for the old udt Point (2,2)
> select * from DataPoints where c1 = convert(Point, '(2,2)')
>
Hmm, I see your point (pun intended).
Quick question, if this is now the case, is there any reason for ALTER
ASSEMBLY ... WITH UNCHECKED DATA? I.e., when changing a type we should
always move the data to some other table and then re-populate.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||"Niels Berglund" <nielsb@.develop.com> wrote in message
news:Xns977952A6CDE45nielsbdevelopcom@.20
7.46.248.16...
> Quick question, if this is now the case, is there any reason for ALTER
> ASSEMBLY ... WITH UNCHECKED DATA? I.e., when changing a type we should
> always move the data to some other table and then re-populate.
Yep - Alter Assembly's primary use case is for fixing bugs in your code.
With UDTs, you may have a bug in one of the methods that you want to fix or
you may need to add a new method. With Format.Native UDTs, you can do this
without requiring WITH UNCHECKED DATA even when your type is persisted
because the server can verify that the serialization did not change.
(UNCHECKED DATA might still be required in cases where a UDT method is
persisted somewhere). With Format.UserDefined UDTs, you could change the
serialization and the server would never know it so WITH UNCHECKED DATA is
required even if you are not modifying the serialization.
Steven

referenced relationship error

I try to add dimension with referenced relationship. I defined everything like explaination in Books online, but when I try to save cube, I always get message:

"Cube 'Cube' cannot be saved because of the following errors:

Errors in the metadata manager. The 'Key' intermediate granularity attribute of the'IntDim'

measures group dimension does not have an attribute hierarchy enabled."

I enabled this attribute hierarchy for Key... but....

Anybody who can help?

When I tried these basic steps using the Adventure Works sample it worked for me. If you remove the dimension relationship and try to re-create it after saving the dimension with the key enabled, does the problem still occur? If so, could you send me the project files in a Zip?|||

How my problems started...

First of all, when I migrate OLAP DB, in one cube I had relation (referenced) by two fields in tables. I don't know is it supported in AS 2005. I think no.

This is my main question.

After that I try to do several different thinks, explaned in Books on-line (attributes, new named calculation...), but without good results... Unfortunatly, I spend two days to solve this problem, end on the end I changed this relationship between tables (replaced referenced with regular relationship) on the AS 2000 and made migrated again.

At the moment I have not project because I made new migration, but I will try to re-create all story and send to you..

I send to me proper your e-mail adress

|||

Here's my email. Just replace the "-" with an "@.".

Matt.Carroll-Microsoft.com

|||

Matt Carroll wrote:

Here's my email. Just replace the "-" with an "@.".

Matt.Carroll-Microsoft.com

Or you can send it directly to me. "Sasha.Juric" with same domain name as Matt's.

|||When I try to do it from the beginning, everything is ok. But when I migrate old Datebase in AS, I have problem. when i have time I re-craete it. But stay the question, if I use two fields in referenced relationship, does AS 2005 support it

referenced relationship error

I try to add dimension with referenced relationship. I defined everything like explaination in Books online, but when I try to save cube, I always get message:

"Cube 'Cube' cannot be saved because of the following errors:

Errors in the metadata manager. The 'Key' intermediate granularity attribute of the'IntDim'

measures group dimension does not have an attribute hierarchy enabled."

I enabled this attribute hierarchy for Key... but....

Anybody who can help?

When I tried these basic steps using the Adventure Works sample it worked for me. If you remove the dimension relationship and try to re-create it after saving the dimension with the key enabled, does the problem still occur? If so, could you send me the project files in a Zip?|||

How my problems started...

First of all, when I migrate OLAP DB, in one cube I had relation (referenced) by two fields in tables. I don't know is it supported in AS 2005. I think no.

This is my main question.

After that I try to do several different thinks, explaned in Books on-line (attributes, new named calculation...), but without good results... Unfortunatly, I spend two days to solve this problem, end on the end I changed this relationship between tables (replaced referenced with regular relationship) on the AS 2000 and made migrated again.

At the moment I have not project because I made new migration, but I will try to re-create all story and send to you..

I send to me proper your e-mail adress

|||

Here's my email. Just replace the "-" with an "@.".

Matt.Carroll-Microsoft.com

|||

Matt Carroll wrote:

Here's my email. Just replace the "-" with an "@.".

Matt.Carroll-Microsoft.com

Or you can send it directly to me. "Sasha.Juric" with same domain name as Matt's.

|||When I try to do it from the beginning, everything is ok. But when I migrate old Datebase in AS, I have problem. when i have time I re-craete it. But stay the question, if I use two fields in referenced relationship, does AS 2005 support it

Reference Linked Server From Linked Server

Hello all, hoping someone can help.
usual info: windows 2003,sql server 2005 32bit and 64bit
ServerA has a linked server defined that points to ServerB.
ServerC has a linked server defined that points to ServerA.
Is it possible to have a query execute on ServerC that will reference
ServerB through the linked ServerA?
example:
execute on ServerC: Select * from ServerA.ServerB.database.dbo.table
Note:
I understand that I can use openquery:
Select * from openquery(ServerA,'Select * from
ServerB.database.dbo.table')
But because I need to use openquery in the query executed on ServerB
it will get ugly.
Example:
Select * from openquery(ServerA,'Select * From openquery(ServerB,
''Select * from database.dbo.table''')
You may be wondering why I don't just create linked server on ServerC
that references ServerB directly. Let me explain:
ServerC is 64bit SQL Server2005
ServerA is 32bit SQL Server2005
ServerB is 32bit Intersytems Cache Database (ODBC Driver ONLY!)
ServerA uses the oledb provider for ODBC datasources to connect to
ServerB. Intersystems does not have an oledb driver. And Microsoft
does not support the oledb provider for odbc datasourceS on 64 bit sql
server.
Any and all ideas are welcome!
Jay
I don't believe this is possible using T-SQL without OPENQUERY or similar.
Would creating views on ServerA that refer to ServerB help? I can't say I
particularly like this approach, but it may be an option.
Stored procedures would be a more fitting approach to take if your querying
is not ad-hoc.
"Jay" <JasonPirtle@.gmail.com> wrote in message
news:8325615c-a36b-4722-9974-a9b58d4756c6@.i12g2000prf.googlegroups.com...
> Hello all, hoping someone can help.
> usual info: windows 2003,sql server 2005 32bit and 64bit
> ServerA has a linked server defined that points to ServerB.
> ServerC has a linked server defined that points to ServerA.
> Is it possible to have a query execute on ServerC that will reference
> ServerB through the linked ServerA?
> example:
> execute on ServerC: Select * from ServerA.ServerB.database.dbo.table
> Note:
> I understand that I can use openquery:
> Select * from openquery(ServerA,'Select * from
> ServerB.database.dbo.table')
> But because I need to use openquery in the query executed on ServerB
> it will get ugly.
> Example:
> Select * from openquery(ServerA,'Select * From openquery(ServerB,
> ''Select * from database.dbo.table''')
> You may be wondering why I don't just create linked server on ServerC
> that references ServerB directly. Let me explain:
> ServerC is 64bit SQL Server2005
> ServerA is 32bit SQL Server2005
> ServerB is 32bit Intersytems Cache Database (ODBC Driver ONLY!)
> ServerA uses the oledb provider for ODBC datasources to connect to
> ServerB. Intersystems does not have an oledb driver. And Microsoft
> does not support the oledb provider for odbc datasourceS on 64 bit sql
> server.
> Any and all ideas are welcome!
> Jay
>

Reference Linked Server From Linked Server

Hello all, hoping someone can help.
usual info: windows 2003,sql server 2005 32bit and 64bit
ServerA has a linked server defined that points to ServerB.
ServerC has a linked server defined that points to ServerA.
Is it possible to have a query execute on ServerC that will reference
ServerB through the linked ServerA?
example:
execute on ServerC: Select * from ServerA.ServerB.database.dbo.table
Note:
I understand that I can use openquery:
Select * from openquery(ServerA,'Select * from
ServerB.database.dbo.table')
But because I need to use openquery in the query executed on ServerB
it will get ugly.
Example:
Select * from openquery(ServerA,'Select * From openquery(ServerB,
''Select * from database.dbo.table''')
You may be wondering why I don't just create linked server on ServerC
that references ServerB directly. Let me explain:
ServerC is 64bit SQL Server2005
ServerA is 32bit SQL Server2005
ServerB is 32bit Intersytems Cache Database (ODBC Driver ONLY!)
ServerA uses the oledb provider for ODBC datasources to connect to
ServerB. Intersystems does not have an oledb driver. And Microsoft
does not support the oledb provider for odbc datasourceS on 64 bit sql
server.
Any and all ideas are welcome!
JayI don't believe this is possible using T-SQL without OPENQUERY or similar.
Would creating views on ServerA that refer to ServerB help? I can't say I
particularly like this approach, but it may be an option.
Stored procedures would be a more fitting approach to take if your querying
is not ad-hoc.
"Jay" <JasonPirtle@.gmail.com> wrote in message
news:8325615c-a36b-4722-9974-a9b58d4756c6@.i12g2000prf.googlegroups.com...
> Hello all, hoping someone can help.
> usual info: windows 2003,sql server 2005 32bit and 64bit
> ServerA has a linked server defined that points to ServerB.
> ServerC has a linked server defined that points to ServerA.
> Is it possible to have a query execute on ServerC that will reference
> ServerB through the linked ServerA?
> example:
> execute on ServerC: Select * from ServerA.ServerB.database.dbo.table
> Note:
> I understand that I can use openquery:
> Select * from openquery(ServerA,'Select * from
> ServerB.database.dbo.table')
> But because I need to use openquery in the query executed on ServerB
> it will get ugly.
> Example:
> Select * from openquery(ServerA,'Select * From openquery(ServerB,
> ''Select * from database.dbo.table''')
> You may be wondering why I don't just create linked server on ServerC
> that references ServerB directly. Let me explain:
> ServerC is 64bit SQL Server2005
> ServerA is 32bit SQL Server2005
> ServerB is 32bit Intersytems Cache Database (ODBC Driver ONLY!)
> ServerA uses the oledb provider for ODBC datasources to connect to
> ServerB. Intersystems does not have an oledb driver. And Microsoft
> does not support the oledb provider for odbc datasourceS on 64 bit sql
> server.
> Any and all ideas are welcome!
> Jay
>