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:
>

refresh data best way?

Hi There

I have a production server and a training server.On a monthly basis the training server needs to have data refreshed from the production server.This consists of approximately 1000 tables, with a few hundered thousand rows from many of the tables.
I was wondering what would be the best way to accomplish this in integration services.Obviously i do not want to create a task for each of the 1000 tables, and the data needs to be copied as fast as possible (it is a substatial amount of data).

I was thinking of loading a table with all the table names and using a loop to dynamically go throught the tables, but then i dont want to use a sql task to do a "select * from linked server into" as this would be very inefficient.
There are no real transformations needed, just copying many objects from one server to another.

There are other ways but i cant help thinking it is not the best way.
Any ideas on the best practice for this?

ThanxWhat about the transfer objects task? It does exactly what it says on the tin and also exactly what you described above.

-Jamie|||Hi Jamie

Thanx for the assiatance.
Yes i did investigate the copy objects tasks. Sorry i should have mentioned that my concerns are that i do not want to copy all rows from these tables, is there a dynamic sql command one can use for object data extraction in this task ?
Also this list of over 100 tables may change, which would require me to change the package every time, is there a way to dynamically drive this task from perhaps a table holding the objects that need to be copied? Perhaps this is possible in the copy objects task ? But i doubt it.

Thanx again for your help.|||Hi Jamie

I have realized dynamically driving thorugh the table list will not work with a for each loop container using a data flow task , as the source can use dynamic sql but the ole db destination cannot be dynamic therefore i could change the destination with the source dynamically.

I tlooks like the only way is to loop through using a sql task with a linked server.

Any ideas ?

Thanx|||

The other thing that woul worry me with only a partial data transfer would be integrity. If you transfer only 100 rows per table for example, what ensures that any foreign key relationships will not be violated, when you do not get matching data?

I don't think a totally generic and automated solution is feasible, the type of data, static vs transactional will influence how they are treated. If the tables themselves are dynamic, with tables being added and removed, you will need to know what each table is, and therefore how to treat them.

|||Hi Darren Thanx for the assistance.

There are no foreign keys, so that should not be an issue.
I agree i do not think it is feasible for SSIS in this situation, i have opted to go for Snapshot replication, easier to add and articles etc, just thought there may be a cool way to do it it in SSIS.

But thanx for the feedback!

Cheers|||Did you try using the SMO enumerator with the transfer objects task? This seems ideally suited, though I admit I don't fully understand what you're trying to do with your data.sql

Refresh cube after data changed?

After I created a cube in BI Studio, I added more data to the fact table, modified dimension table, and refreshed DSV. I got Refresh Data Source View message ‘No changes have been found’, but I didn’t see the data that I just added in from cube browser.

(Database connecting and database structure didn’t change)

My questions:

1. Is refresh DSV only for database structure change?
2. Which step I was missing for refreshing cube data?

Thanks in advance.

Yes, refreshing is to pull database structure changes into the DSV. To get the new data, "Process" the cube.

You'll find that MSAS provides a rich set of capabilities and abilities to update the data. For now, always do a "Full Process", until you get more comfortable with the technology. Depending on the size of your cube, plan on spending a little time learning about incremental processing and partitions. For anything other than small cubes, expect to define / manage partitions are part of the definitions. The impact on performance is dramatic.

|||Thank you very much Dave.

It is very helpful information.

Refresh cube after data changed?

After I created a cube in BI Studio, I added more data to

the fact table, modified dimension table, and refreshed DSV. I got Refresh Data

Source View message ‘No changes have been found’, but I didn’t see the data that

I just added in from cube browser.

(Database connecting and database structure didn’t change)

My questions:

1. Is refresh

   DSV only for database structure change?

2. Which

   step I was missing for refreshing cube data?

Thanks in advance.

Yes, refreshing is to pull database structure changes into the DSV. To get the new data, "Process" the cube.

You'll find that MSAS provides a rich set of capabilities and abilities to update the data. For now, always do a "Full Process", until you get more comfortable with the technology. Depending on the size of your cube, plan on spending a little time learning about incremental processing and partitions. For anything other than small cubes, expect to define / manage partitions are part of the definitions. The impact on performance is dramatic.

|||Thank you very much Dave.

It is very helpful information.

Refresh cube after data changed?

After I created a cube in BI Studio, I added more data to the fact table, modified dimension table, and refreshed DSV. I got Refresh Data Source View message ‘No changes have been found’, but I didn’t see the data that I just added in from cube browser.

(Database connecting and database structure didn’t change)

My questions:

1. Is refresh DSV only for database structure change?
2. Which step I was missing for refreshing cube data?

Thanks in advance.

Yes, refreshing is to pull database structure changes into the DSV. To get the new data, "Process" the cube.

You'll find that MSAS provides a rich set of capabilities and abilities to update the data. For now, always do a "Full Process", until you get more comfortable with the technology. Depending on the size of your cube, plan on spending a little time learning about incremental processing and partitions. For anything other than small cubes, expect to define / manage partitions are part of the definitions. The impact on performance is dramatic.

|||Thank you very much Dave.

It is very helpful information.