Deployment

Hi all,
Can someone please tell me the (.ocx) or other dependencies to include in my deployment package for crystal reports 10. I am using the "crystal active X report viewer library 10.0" in VB 6.0.
Thanks
LarryFound a useful resource on Crystal Deployment.

http://support.businessobjects.com/communityCS/TechnicalPapers/rdc10_deployment.pdf

Larry

deploying cube w/o database dependencies

Hi,

I have been asked to find a way to deploy a SQL 2005 cube with no dependencies on the relational database. Does any one have any suggestions of how this might be achieved?

Thanks

ahur

Not sure what you mean by "deploy".

Do you have a cube already you have built? Are you planning to create a new cube in Analysis Services and start writing data directly into it instead of processing cube based on the relational database?

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

You can process the cube on one machine and then use either backup/restore or the Database Synchronization Wizard to move the processed database to another machine. If you are wanting to deploy to customers or to offline machines, then you may want to consider creating local cubes and copying these over.

Is this what you were looking for?

|||

Hi,

I have the same case, I need to deploy the cubes structure and data to another machine ( from development to production) , is there any way to achieve that?

Thanks

|||From what you describe, my posting earlier in this thread should provide some solutions. Please clarify your scenario if none of these solutions meet your needs.

deploying cube w/o database dependencies

Hi,

I have been asked to find a way to deploy a SQL 2005 cube with no dependencies on the relational database. Does any one have any suggestions of how this might be achieved?

Thanks

ahur

Not sure what you mean by "deploy".

Do you have a cube already you have built? Are you planning to create a new cube in Analysis Services and start writing data directly into it instead of processing cube based on the relational database?

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

You can process the cube on one machine and then use either backup/restore or the Database Synchronization Wizard to move the processed database to another machine. If you are wanting to deploy to customers or to offline machines, then you may want to consider creating local cubes and copying these over.

Is this what you were looking for?

Dependency information and URN

I want to trunctate some tables but I need to find the right order to truncate them in because of dependencies. I have the following code:

List<Table> truncateTableList = new List<Table>();

DependencyWalker walker = new DependencyWalker(server);

for (int i = 0; i < truncateList.Length; i++)

{

truncateTableList.Add(server.Databases[_database].Tables[truncateList]);

}

DependencyTree tree = walker.DiscoverDependencies(truncateTableList.ToArray(), true);

DependencyCollection collection = walker.WalkDependencies(tree);

server.ConnectionContext.SqlExecutionModes = SqlExecutionModes.CaptureSql;

Now that I have the DependencyCollection what do I do? Looking at the nodes I see that the tree seems to be built with the parents on the top (the tables that other tables depend on) and followed by the children. I want to Truncate in reverse order from this list. Now is all I need is the names of the tables. How do I get that from the Urn?

Thank you.

Kevin

I know this is not exactly what you are trying to do, but I have a sample that walks the table dependency list so it can script the creates in the correct order, it generates a Q in the right order, I think you could take the code and tweak it for truncates. Its a lot more verbose than yours but the sample does a lot more than just this;

Code Snippet

m_UniqueTableList = new Hashtable(); //Hashtable used to check for dupes
m_TableQ = new Queue<Table>(); //Ordered list of tables
m_ServerConn = new Server(); //Server Connection

SqlSmoObject[] UnOrderedTableArr; //Array that holds the list of table objects, it could have dupes in it

...

...

//Take the unordered list and make it ordered and unique, then use the ordered list to generate the indexes, pk, fks in the right order
Scripter scripter = new Scripter(m_ServerConn);
BuildOrderedTableList(scripter.DiscoverDependencies(UnOrderedTableArr, DependencyType.Parents));

--

private void BuildOrderedTableList(DependencyTree tree)
{
DependencyTreeNode rootNode;
Table TargetTable;

// Get the first child in tree
rootNode = tree.FirstChild;

// Iterate children
while (rootNode != null)
{
// Add treeview node
if (rootNode.Urn.Type == "Table")
{

// Add child nodes to tree (this will recurse)
AddChildren(rootNode);

TargetTable = (Table)m_ServerConn.GetSmoObject(rootNode.Urn);
if (AddAndValidateTableAdd(TargetTable.Name))
{
m_TableQ.Enqueue(TargetTable);
}
}
// Skip to next child node from root
rootNode = rootNode.NextSibling;
}
}

private void AddChildren(DependencyTreeNode dependencyTreeNode)
{
DependencyTreeNode child;
Table TargetTable;

// Get first child of this node
child = dependencyTreeNode.FirstChild;

while (child != null)
{
if (child.Urn.Type == "Table")
{
// Recursively add the other nodes
AddChildren(child);

TargetTable = (Table)m_ServerConn.GetSmoObject(child.Urn);

if (AddAndValidateTableAdd(TargetTable.Name))
{
m_TableQ.Enqueue(TargetTable);
}
}
// Skip to next child node at this level
child = child.NextSibling;
}
}

//Add a table to the list, uniquely
//Use the hash table to make sure we only get one reference to each table
private bool AddAndValidateTableAdd(String TableName)
{
bool result = false;
if (!m_UniqueTableList.ContainsKey(TableName))
{
m_UniqueTableList.Add(TableName, "");
result = true;
}
return result;
}

|||

Euan Garden wrote:

I know this is not exactly what you are trying to do, but I have a sample that walks the table dependency list so it can script the creates in the correct order, it generates a Q in the right order, I think you could take the code and tweak it for truncates. Its a lot more verbose than yours but the sample does a lot more than just this;

Code Snippet

m_UniqueTableList = new Hashtable(); //Hashtable used to check for dupes
m_TableQ = new Queue<Table>(); //Ordered list of tables
m_ServerConn = new Server(); //Server Connection

SqlSmoObject[] UnOrderedTableArr; //Array that holds the list of table objects, it could have dupes in it

...

...

//Take the unordered list and make it ordered and unique, then use the ordered list to generate the indexes, pk, fks in the right order
Scripter scripter = new Scripter(m_ServerConn);
BuildOrderedTableList(scripter.DiscoverDependencies(UnOrderedTableArr, DependencyType.Parents));

--

private void BuildOrderedTableList(DependencyTree tree)
{
DependencyTreeNode rootNode;
Table TargetTable;

// Get the first child in tree
rootNode = tree.FirstChild;

// Iterate children
while (rootNode != null)
{
// Add treeview node
if (rootNode.Urn.Type == "Table")
{

// Add child nodes to tree (this will recurse)
AddChildren(rootNode);

TargetTable = (Table)m_ServerConn.GetSmoObject(rootNode.Urn);
if (AddAndValidateTableAdd(TargetTable.Name))
{
m_TableQ.Enqueue(TargetTable);
}
}
// Skip to next child node from root
rootNode = rootNode.NextSibling;
}
}

private void AddChildren(DependencyTreeNode dependencyTreeNode)
{
DependencyTreeNode child;
Table TargetTable;

// Get first child of this node
child = dependencyTreeNode.FirstChild;

while (child != null)
{
if (child.Urn.Type == "Table")
{
// Recursively add the other nodes
AddChildren(child);

TargetTable = (Table)m_ServerConn.GetSmoObject(child.Urn);

if (AddAndValidateTableAdd(TargetTable.Name))
{
m_TableQ.Enqueue(TargetTable);
}
}
// Skip to next child node at this level
child = child.NextSibling;
}
}

//Add a table to the list, uniquely
//Use the hash table to make sure we only get one reference to each table
private bool AddAndValidateTableAdd(String TableName)
{
bool result = false;
if (!m_UniqueTableList.ContainsKey(TableName))
{
m_UniqueTableList.Add(TableName, "");
result = true;
}
return result;
}

Thank you very much. One question your code starts out with an unordered table list. Where is that list obtained from?

|||I think I understand your code. I think is what I want is a little more complicated. I want to input a table and get a list of objects that depend on it. Is that possible with SMO?|||Try just passing one table object as part of the array and see what happens|||

I just get that one table returned.

My "solution" is to go through every table in the database and see if that table has a FK defined that references the given table. There is a method on each ForeignKey object call "ReferencedTable". If I compare that to the table in question I get a list of tables that have a FK reference to the table in question. For as common as this seems to me I would think a method call would solve the problem but this solution seems to work.

Thank you.

Kevin

|||If you are going through every table anyway just add them to the array and the code will work (apart from the order will be a create order)

Dependency information and URN

I want to trunctate some tables but I need to find the right order to truncate them in because of dependencies. I have the following code:

List<Table> truncateTableList = new List<Table>();

DependencyWalker walker = new DependencyWalker(server);

for (int i = 0; i < truncateList.Length; i++)

{

truncateTableList.Add(server.Databases[_database].Tables[truncateList]);

}

DependencyTree tree = walker.DiscoverDependencies(truncateTableList.ToArray(), true);

DependencyCollection collection = walker.WalkDependencies(tree);

server.ConnectionContext.SqlExecutionModes = SqlExecutionModes.CaptureSql;

Now that I have the DependencyCollection what do I do? Looking at the nodes I see that the tree seems to be built with the parents on the top (the tables that other tables depend on) and followed by the children. I want to Truncate in reverse order from this list. Now is all I need is the names of the tables. How do I get that from the Urn?

Thank you.

Kevin

I know this is not exactly what you are trying to do, but I have a sample that walks the table dependency list so it can script the creates in the correct order, it generates a Q in the right order, I think you could take the code and tweak it for truncates. Its a lot more verbose than yours but the sample does a lot more than just this;

Code Snippet

m_UniqueTableList = new Hashtable(); //Hashtable used to check for dupes
m_TableQ = new Queue<Table>(); //Ordered list of tables
m_ServerConn = new Server(); //Server Connection

SqlSmoObject[] UnOrderedTableArr; //Array that holds the list of table objects, it could have dupes in it

...

...

//Take the unordered list and make it ordered and unique, then use the ordered list to generate the indexes, pk, fks in the right order
Scripter scripter = new Scripter(m_ServerConn);
BuildOrderedTableList(scripter.DiscoverDependencies(UnOrderedTableArr, DependencyType.Parents));

--

private void BuildOrderedTableList(DependencyTree tree)
{
DependencyTreeNode rootNode;
Table TargetTable;

// Get the first child in tree
rootNode = tree.FirstChild;

// Iterate children
while (rootNode != null)
{
// Add treeview node
if (rootNode.Urn.Type == "Table")
{

// Add child nodes to tree (this will recurse)
AddChildren(rootNode);

TargetTable = (Table)m_ServerConn.GetSmoObject(rootNode.Urn);
if (AddAndValidateTableAdd(TargetTable.Name))
{
m_TableQ.Enqueue(TargetTable);
}
}
// Skip to next child node from root
rootNode = rootNode.NextSibling;
}
}

private void AddChildren(DependencyTreeNode dependencyTreeNode)
{
DependencyTreeNode child;
Table TargetTable;

// Get first child of this node
child = dependencyTreeNode.FirstChild;

while (child != null)
{
if (child.Urn.Type == "Table")
{
// Recursively add the other nodes
AddChildren(child);

TargetTable = (Table)m_ServerConn.GetSmoObject(child.Urn);

if (AddAndValidateTableAdd(TargetTable.Name))
{
m_TableQ.Enqueue(TargetTable);
}
}
// Skip to next child node at this level
child = child.NextSibling;
}
}

//Add a table to the list, uniquely
//Use the hash table to make sure we only get one reference to each table
private bool AddAndValidateTableAdd(String TableName)
{
bool result = false;
if (!m_UniqueTableList.ContainsKey(TableName))
{
m_UniqueTableList.Add(TableName, "");
result = true;
}
return result;
}

|||

Euan Garden wrote:

I know this is not exactly what you are trying to do, but I have a sample that walks the table dependency list so it can script the creates in the correct order, it generates a Q in the right order, I think you could take the code and tweak it for truncates. Its a lot more verbose than yours but the sample does a lot more than just this;

Code Snippet

m_UniqueTableList = new Hashtable(); //Hashtable used to check for dupes
m_TableQ = new Queue<Table>(); //Ordered list of tables
m_ServerConn = new Server(); //Server Connection

SqlSmoObject[] UnOrderedTableArr; //Array that holds the list of table objects, it could have dupes in it

...

...

//Take the unordered list and make it ordered and unique, then use the ordered list to generate the indexes, pk, fks in the right order
Scripter scripter = new Scripter(m_ServerConn);
BuildOrderedTableList(scripter.DiscoverDependencies(UnOrderedTableArr, DependencyType.Parents));

--

private void BuildOrderedTableList(DependencyTree tree)
{
DependencyTreeNode rootNode;
Table TargetTable;

// Get the first child in tree
rootNode = tree.FirstChild;

// Iterate children
while (rootNode != null)
{
// Add treeview node
if (rootNode.Urn.Type == "Table")
{

// Add child nodes to tree (this will recurse)
AddChildren(rootNode);

TargetTable = (Table)m_ServerConn.GetSmoObject(rootNode.Urn);
if (AddAndValidateTableAdd(TargetTable.Name))
{
m_TableQ.Enqueue(TargetTable);
}
}
// Skip to next child node from root
rootNode = rootNode.NextSibling;
}
}

private void AddChildren(DependencyTreeNode dependencyTreeNode)
{
DependencyTreeNode child;
Table TargetTable;

// Get first child of this node
child = dependencyTreeNode.FirstChild;

while (child != null)
{
if (child.Urn.Type == "Table")
{
// Recursively add the other nodes
AddChildren(child);

TargetTable = (Table)m_ServerConn.GetSmoObject(child.Urn);

if (AddAndValidateTableAdd(TargetTable.Name))
{
m_TableQ.Enqueue(TargetTable);
}
}
// Skip to next child node at this level
child = child.NextSibling;
}
}

//Add a table to the list, uniquely
//Use the hash table to make sure we only get one reference to each table
private bool AddAndValidateTableAdd(String TableName)
{
bool result = false;
if (!m_UniqueTableList.ContainsKey(TableName))
{
m_UniqueTableList.Add(TableName, "");
result = true;
}
return result;
}

Thank you very much. One question your code starts out with an unordered table list. Where is that list obtained from?

|||I think I understand your code. I think is what I want is a little more complicated. I want to input a table and get a list of objects that depend on it. Is that possible with SMO?|||Try just passing one table object as part of the array and see what happens|||

I just get that one table returned.

My "solution" is to go through every table in the database and see if that table has a FK defined that references the given table. There is a method on each ForeignKey object call "ReferencedTable". If I compare that to the table in question I get a list of tables that have a FK reference to the table in question. For as common as this seems to me I would think a method call would solve the problem but this solution seems to work.

Thank you.

Kevin

|||If you are going through every table anyway just add them to the array and the code will work (apart from the order will be a create order)

Dependencies.

I am trying to ascertain dependencies of a certain stored proc, but it
seems that the dependencies are out of date. How do I force the
dependencies to refresh? (SQL 2000)
Regards
Short of dropping and recreating all the objects you don't. Never trust the
dependencies to be up to date.
Andrew J. Kelly SQL MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:ePB8NVUIGHA.3936@.TK2MSFTNGP12.phx.gbl...
>I am trying to ascertain dependencies of a certain stored proc, but it
>seems that the dependencies are out of date. How do I force the
>dependencies to refresh? (SQL 2000)
> Regards

Dependencies.

I am trying to ascertain dependencies of a certain stored proc, but it
seems that the dependencies are out of date. How do I force the
dependencies to refresh? (SQL 2000)
RegardsShort of dropping and recreating all the objects you don't. Never trust the
dependencies to be up to date.
Andrew J. Kelly SQL MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:ePB8NVUIGHA.3936@.TK2MSFTNGP12.phx.gbl...
>I am trying to ascertain dependencies of a certain stored proc, but it
>seems that the dependencies are out of date. How do I force the
>dependencies to refresh? (SQL 2000)
> Regards

Dependencies.

I am trying to ascertain dependencies of a certain stored proc, but it
seems that the dependencies are out of date. How do I force the
dependencies to refresh? (SQL 2000)
RegardsShort of dropping and recreating all the objects you don't. Never trust the
dependencies to be up to date.
--
Andrew J. Kelly SQL MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:ePB8NVUIGHA.3936@.TK2MSFTNGP12.phx.gbl...
>I am trying to ascertain dependencies of a certain stored proc, but it
>seems that the dependencies are out of date. How do I force the
>dependencies to refresh? (SQL 2000)
> Regards

Dependencies within a transaction

If i were to have a dynamic SQL transaction, where i want to insert a
record, and use the ID generated for this record, in a subsequent query in
the same transaction , how do i do it ?
Regards,
Chak.How are you generating the id? Is that an IDENITITY? or GUID? See @.@.IDENTITY
and SCOPE_IDENTITY() in SQL Server Books Online. Also see:
http://vyaskn.tripod.com/retrieve_g...ke_identity.htm
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Chakra" <chakra@.smnetserv.com> wrote in message
news:OnfQHsgJFHA.2628@.tk2msftngp13.phx.gbl...
> If i were to have a dynamic SQL transaction, where i want to insert a
> record, and use the ID generated for this record, in a subsequent query in
> the same transaction , how do i do it ?
> Regards,
> Chak.
>

Dependencies of a table

Hello,

I have a table in my SQL2000 server and there's a field in this table being created by an object - with a GREEN PLUS sign (look like an APPEND query in MsAccess). How can I locate and edit/modify that (append query-like) object within my SQL 2000 - Enterpise Manager? Here's some more details:

Table: Customer

Field1: CustomerID

Field2: CustomerRegion_ID (contains the ID above & RegionID below)

Table: Region

Field: Region_ID

--

When I checked on the dependencies of the table "Customer" I saw 1 dependency and this dependency has 2 table depend on it back-to-back (Customer & Region).

I want to modify the "Field2" of the "Customer" table and I looked everywhere on my VB/stored-procedure codes but couldn't find what triggle the creation of the field. The only thing that I suspect that would be the append object with "GREEN PLUS" sign. Any help would be appreciated.

Peter.

There is no ddl trigger in sql2k. So, it would be hard to track down the point in time of the creation of the column (unless you have profiler turned on).

The best you can do is to check syscomments for the object/column name that might be used in other porcesses.

|||

it could be a trigger.

expand em to the table you need.

right click the table and then click "manage trigger"

If the dropdownlist contains something other than new

then thats the trigger thats affecting your logic

Dependencies of a table

Hello,

I have a table in my SQL2000 server and there's a field in this table being created by an object - with a GREEN PLUS sign (look like an APPEND query in MsAccess). How can I locate and edit/modify that (append query-like) object within my SQL 2000 - Enterpise Manager? Here's some more details:

Table: Customer

Field1: CustomerID

Field2: CustomerRegion_ID (contains the ID above & RegionID below)

Table: Region

Field: Region_ID

--

When I checked on the dependencies of the table "Customer" I saw 1 dependency and this dependency has 2 table depend on it back-to-back (Customer & Region).

I want to modify the "Field2" of the "Customer" table and I looked everywhere on my VB/stored-procedure codes but couldn't find what triggle the creation of the field. The only thing that I suspect that would be the append object with "GREEN PLUS" sign. Any help would be appreciated.

Peter.

Hello all,

Never mind the question I asked. I found the answer. FYI, just in case you want to know: It's under "manage trigger" within the Enterprise Manager.

Peter.

Dependencies not correct with temporary tables --> replication is failing

Hello all,

here is a stored procedure I have:

CREATE PROCEDURE spU_GUI_AppliqueConditionFinancementPourGuichet
(
@.GuichetId int,
@.Validateur nvarchar(40)
)
AS
CREATE TABLE #tReservations (ReservationId int)

IF (dbo.GetSiGuichetEnRegle(@.GuichetId) = 0)
INSERT #tReservations
EXECUTE spU_GUI_AppliquePerteFinancement @.GuichetID, @.Validateur
ELSE
INSERT #tReservations
EXECUTE spU_GUI_AppliquePerteAgrement @.GuichetID, @.Validateur

SELECT GR.Id,
dbo.FormateNoms(GR.Name) AS Names
FROM #tReservations
LEFT JOIN AnotherTable GR ON GR.Id = AnotherTable.id
DROP TABLE #tReservations
GO

The creation is ok but when I look to the dependencies, I see that it depends on GetSiGuichetEnRegle only.

For me, it shall also depend on

AnotherTable

spU_GUI_AppliquePerteFinancement

spU_GUI_AppliquePerteAgrement

FormateNoms

Apparently the dependencies are not calculated correctly because I'm using a temporary table.

My problem is that I have updated this stored procedures (and the two other that I call) to add a new parameter. As a consequence, when I do a replication, this is failing saying that I have an extra parameter. I imagine that because my dependencies are not correct, the replication is not occuring in the correct order and so it's still using the old definition of the stored procedure.

Do you have any idea on how I can force the dependencies to be calculated correctly ?

Thanks

Did the error happen when you are applying a snapshot or when the distribution agent is applying a DDL change for the stored procedure update? If this happened while the snapshot was being delivered to the subscriber, I have the following questions for you:

1) Are you using 'drop' as the pre-creation command for the stored procedure articles? If so, you should theoretically not be seeing the error that you saw when the snapshot was delivered to the subscriber since the referenced procedures would either have been drop (or recreated with the new definition).

2) Are the referenced stored procedures (spU_GUI_AppliquePerteFinancement and spU_GUI_AppliquePerteAgrement) included in the publication as well? (It never hurts to ask the obvious.)

3) It would be great if you can post history messages of the distribution agent when the snapshot was applied, I just want to see the relative order of how the objects that you mentioned are applied. I will also be interested to see the history messages from the snapshot agent.

There is an undocumented -EnableStoredProcedureDependenciesReevaluation 1 switch in the snapshot agent which forces the snapshot agent to compute dependency ordering for stored procedure articles in a more accurate manner but I have a feeling that the real problem is something else.

-Raymond

|||

Hello Raymond,

Indeed there seems to be problems with the dependencies between stored procedures and tables, but the main issue here seems to be to be related to the latest problem we spoke together

After isolating the problem, I've found that the stored procedure that could not be replicated contains a "INSERT INTO" statement.

I will try a work-around by playing with post-replication scripts, while waiting for SP2 ;-)

Anyway, I will investigate the solutions you give for the dependencies

Pierre-Emmanuel

Dependencies are lost when alter an object

Hello,
I have an old problem but now it became ugly...
I have an object (for example a function) that is used in some views. If I am looking on dependencies, it shows me the 8 views whcih depends on it. But if I edit the function in Query Analyser and run the script (no modification on the script), it deletes
all the dependencies for the 8 views on it... The problem is worst because when I generate the initial snapshot for a merge replication, it will affect the generation order of the scripts and I get error when trying to create the views (on subscriber), s
aying that the function doesn't exist in database...
Any ideea how can I preserve the dependencies?
Thanks in advance
Catalin,
I've also wondered what is the cause of this. There was mention a while back
that it is caused by using enterprise manager to do certain changes to
objects rather than using QA. Also, it has been mentioned that renaming
objects can corrupt dependencies.
I haven't found a definitive article explaining this, but as a workaround,
if you have this problem, you might want to run sp_refreshview on your views
prior to replication.
HTH,
Paul Ibison
|||I don't work with EM, only with QA.
For example: I run this script, to create 2 functions:
CREATE FUNCTION dbo.Child () RETURNS TABLE RETURN (SELECT 1 AS Col)
GO
CREATE FUNCTION dbo._Parent () RETURNS TABLE RETURN (SELECT * FROM dbo.Child ())
GO
As you can see _Parent depens on Child.
At this moment everything is fine on dependencies (each one shows the other)
But if I execute the next script:
ALTER FUNCTION dbo.Child () RETURNS TABLE RETURN (SELECT 1 AS Col)
I loose all the referencies. And I need to generate the initial snapshot for a replication. And because no dependencies found, it will create the function in the order of names (and "_Parent" is before "Child") and when it tries to create the database (on
subscriber) I get the error because it tries to create _Parent before Child...
|||Hi Catalin,
If you don't have very many complex dependencies among functions, you may be
able to restore the dependency information by altering _Parent after you
alter Child.
HTH
-Raymond
"Catalin" <anonymous@.discussions.microsoft.com> wrote in message
news:0D8E0108-CACC-4491-A1B2-DFCD4FE641C6@.microsoft.com...
> I don't work with EM, only with QA.
> For example: I run this script, to create 2 functions:
> CREATE FUNCTION dbo.Child () RETURNS TABLE RETURN (SELECT 1 AS Col)
> GO
> CREATE FUNCTION dbo._Parent () RETURNS TABLE RETURN (SELECT * FROM
dbo.Child ())
> GO
> As you can see _Parent depens on Child.
> At this moment everything is fine on dependencies (each one shows the
other)
> But if I execute the next script:
> ALTER FUNCTION dbo.Child () RETURNS TABLE RETURN (SELECT 1 AS Col)
> I loose all the referencies. And I need to generate the initial snapshot
for a replication. And because no dependencies found, it will create the
function in the order of names (and "_Parent" is before "Child") and when it
tries to create the database (on subscriber) I get the error because it
tries to create _Parent before Child...
|||Yes, but... unfortunately I have a database with more that 1000 objects and I need to replicate it... In this case I need to "reconstruct" the dependencies tree. The simplest way that I think is to develop a small VB application that will try to re-establ
ish the order (trying each script to execute in a blank database and to determine the missing object)...

dependencies among views and a table in different databases

Hi everybody.

I need to find all the views that depend on a table in a different database, in order to refresh them once the table is altered.

In the BOL I found the following script that is very useful, but I can't use it if the view and the table are in different database.

Where can I find the dependencies in this case?

Thank you very much.

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/9ce1d07c-ee66-4a83-8c73-cd2cc104dd08.htm

Creating a script that updates all views that have dependencies on a changed object.

USE AdventureWorks; GO SELECT DISTINCT 'EXEC sp_refreshview ''' + name + '''' FROM sys.objects so INNER JOIN sys.sql_dependencies sd ON so.object_id = sd.object_id WHERE type = 'V' AND sd.referenced_major_id = object_id('Person.Contact')

Interesting question.
I have had a similar problem just a few weeks ago, but I have found no way to resolve it...
So it would be interesting to know if it's possible to do that, and how.

Thanks to all

dependencies among views and a table in different databases

Hi everybody.

I need to find all the views that depend on a table in a different database, in order to refresh them once the table is altered.

In the BOL I found the following script that is very useful, but I can't use it if the view and the table are in different database.

Where can I find the dependencies in this case?

Thank you very much.

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/9ce1d07c-ee66-4a83-8c73-cd2cc104dd08.htm

Creating a script that updates all views that have dependencies on a changed object.

USE AdventureWorks; GO SELECT DISTINCT 'EXEC sp_refreshview ''' + name + '''' FROM sys.objects so INNER JOIN sys.sql_dependencies sd ON so.object_id = sd.object_id WHERE type = 'V' AND sd.referenced_major_id = object_id('Person.Contact')

Interesting question.
I have had a similar problem just a few weeks ago, but I have found no way to resolve it...
So it would be interesting to know if it's possible to do that, and how.

Thanks to all

Dependencies

In Enterprise Manager (EM), how does it arrive at
the dependencies for a table?
The reason I am asking is, before I add additional
columns to a table, under "objects dependent on
<tablename>", it shows tables, procedures, triggers, etc.
After I add columns to this table, it shows only tables
but nothing else.
What could be the reason? What I may be doing wrong?
How can I relink everything?
Thank you,
Kelly
To learn what EM does, run a profiler trace. You will probably find that EM calls a number of stored
procedures. So, you can read the source code for those stored procedures to determine whether you should
consider this a bug and possibly open a case with MS Support.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kelly" <anonymous@.discussions.microsoft.com> wrote in message news:1cb6901c4532d$bcccab20$a301280a@.phx.gbl...
> In Enterprise Manager (EM), how does it arrive at
> the dependencies for a table?
> The reason I am asking is, before I add additional
> columns to a table, under "objects dependent on
> <tablename>", it shows tables, procedures, triggers, etc.
> After I add columns to this table, it shows only tables
> but nothing else.
> What could be the reason? What I may be doing wrong?
> How can I relink everything?
> Thank you,
> Kelly
|||Depending on the nature of the table changes EM may create a new table, copy the data, and drop the original table. This process causes the dependency records to be lost. The dependency records can be restored by dropping and creating all the dependent
objects in the proper sequence, or by...
Two of the SQL Server Tools applications have an option to restore dependency records automatically. WT2 and WT3 implement that feature in the same way, but then they diverge into different functionality. You can learn more at this URL...
http://www.sqlservertools.us
"Kelly" wrote:

> In Enterprise Manager (EM), how does it arrive at
> the dependencies for a table?
> The reason I am asking is, before I add additional
> columns to a table, under "objects dependent on
> <tablename>", it shows tables, procedures, triggers, etc.
> After I add columns to this table, it shows only tables
> but nothing else.
> What could be the reason? What I may be doing wrong?
> How can I relink everything?
> Thank you,
> Kelly
>
|||Tibor,
Thanks for your input.
-Kelly

>--Original Message--
>To learn what EM does, run a profiler trace. You will
probably find that EM calls a number of stored
>procedures. So, you can read the source code for those
stored procedures to determine whether you should
>consider this a bug and possibly open a case with MS
Support.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Kelly" <anonymous@.discussions.microsoft.com> wrote in
message news:1cb6901c4532d$bcccab20$a301280a@.phx.gbl...[vbcol=seagreen]
etc.
>
>.
>
|||Thanks for the information.
-Kelly
>--Original Message--
>Depending on the nature of the table changes EM may
create a new table, copy the data, and drop the original
table. This process causes the dependency records to be
lost. The dependency records can be restored by dropping
and creating all the dependent objects in the proper
sequence, or by...
>Two of the SQL Server Tools applications have an option
to restore dependency records automatically. WT2 and WT3
implement that feature in the same way, but then they
diverge into different functionality. You can learn more
at this URL...[vbcol=seagreen]
>http://www.sqlservertools.us
>"Kelly" wrote:
etc.
>.
>

Dependencies

how and when are dependencies created. I know when tables are linked in a
view then a dependency is created what about stored proceedures?
Thanks
Dependencies are created when objects are created altered. These include
the list of all objects in the current database that the object directly
references.
Note that dependency information can be inaccurate when objects are not
created in proper dependency order. For example, you can create a proc
before the referenced table is created. No record of this dependency is
created in this case. Similarly, if you drop and recreate the table, the
dependency info is deleted but not recreated.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:613A82D1-7040-4FE2-808E-4109340EEB26@.microsoft.com...
> how and when are dependencies created. I know when tables are linked in a
> view then a dependency is created what about stored proceedures?
> Thanks
|||The sp_rename will give you all sorts of errors about breaking dependencies
as well.
Sincerely,
Anthony Thomas

"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uNzDMJb2EHA.2568@.TK2MSFTNGP10.phx.gbl...
Dependencies are created when objects are created altered. These include
the list of all objects in the current database that the object directly
references.
Note that dependency information can be inaccurate when objects are not
created in proper dependency order. For example, you can create a proc
before the referenced table is created. No record of this dependency is
created in this case. Similarly, if you drop and recreate the table, the
dependency info is deleted but not recreated.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:613A82D1-7040-4FE2-808E-4109340EEB26@.microsoft.com...
> how and when are dependencies created. I know when tables are linked in a
> view then a dependency is created what about stored proceedures?
> Thanks

Dependencies

Can I drop a table while leaving a view in tact?
I will recreate the tables with the same name after that...
The correct sequence will be to drop the view first and then drop referenced
tables.

quote:

---

Any view or stored procedure that references the dropped
table must be explicitly dropped by using the DROP VIEW or DROP PROCEDURE
statement.

---

. In practice I managed to drop referenced tables without
dropping the view first though I won't recommend it.
Cristian Lefter, SQL Server MVP
"AshVsAOD" <.> wrote in message
news:O4tsMD0OFHA.1176@.TK2MSFTNGP12.phx.gbl...
> Can I drop a table while leaving a view in tact?
> I will recreate the tables with the same name after that...
>

Dependencies

In Enterprise Manager (EM), how does it arrive at
the dependencies for a table?
The reason I am asking is, before I add additional
columns to a table, under "objects dependent on
<tablename>", it shows tables, procedures, triggers, etc.
After I add columns to this table, it shows only tables
but nothing else.
What could be the reason? What I may be doing wrong?
How can I relink everything?
Thank you,
KellyTo learn what EM does, run a profiler trace. You will probably find that EM
calls a number of stored
procedures. So, you can read the source code for those stored procedures to
determine whether you should
consider this a bug and possibly open a case with MS Support.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kelly" <anonymous@.discussions.microsoft.com> wrote in message news:1cb6901c4532d$bcccab20$a
301280a@.phx.gbl...
> In Enterprise Manager (EM), how does it arrive at
> the dependencies for a table?
> The reason I am asking is, before I add additional
> columns to a table, under "objects dependent on
> <tablename>", it shows tables, procedures, triggers, etc.
> After I add columns to this table, it shows only tables
> but nothing else.
> What could be the reason? What I may be doing wrong?
> How can I relink everything?
> Thank you,
> Kelly|||Depending on the nature of the table changes EM may create a new table, copy
the data, and drop the original table. This process causes the dependency
records to be lost. The dependency records can be restored by dropping and
creating all the dependent
objects in the proper sequence, or by...
Two of the SQL Server Tools applications have an option to restore dependenc
y records automatically. WT2 and WT3 implement that feature in the same way
, but then they diverge into different functionality. You can learn more at
this URL...
http://www.sqlservertools.us
"Kelly" wrote:

> In Enterprise Manager (EM), how does it arrive at
> the dependencies for a table?
> The reason I am asking is, before I add additional
> columns to a table, under "objects dependent on
> <tablename>", it shows tables, procedures, triggers, etc.
> After I add columns to this table, it shows only tables
> but nothing else.
> What could be the reason? What I may be doing wrong?
> How can I relink everything?
> Thank you,
> Kelly
>|||Tibor,
Thanks for your input.
-Kelly

>--Original Message--
>To learn what EM does, run a profiler trace. You will
probably find that EM calls a number of stored
>procedures. So, you can read the source code for those
stored procedures to determine whether you should
>consider this a bug and possibly open a case with MS
Support.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Kelly" <anonymous@.discussions.microsoft.com> wrote in
message news:1cb6901c4532d$bcccab20$a301280a@.phx
.gbl...
etc.[vbcol=seagreen]
>
>.
>|||Thanks for the information.
-Kelly
>--Original Message--
>Depending on the nature of the table changes EM may
create a new table, copy the data, and drop the original
table. This process causes the dependency records to be
lost. The dependency records can be restored by dropping
and creating all the dependent objects in the proper
sequence, or by...
>Two of the SQL Server Tools applications have an option
to restore dependency records automatically. WT2 and WT3
implement that feature in the same way, but then they
diverge into different functionality. You can learn more
at this URL...
>http://www.sqlservertools.us
>"Kelly" wrote:
>
etc.[vbcol=seagreen]
>.
>

Dependencies

Can I drop a table while leaving a view in tact?
I will recreate the tables with the same name after that...The correct sequence will be to drop the view first and then drop referenced
tables. [quote]Any view or stored procedure that references the dropped
table must be explicitly dropped by using the DROP VIEW or DROP PROCEDURE
statement.[/quote]. In practice I managed to drop referenced tables with
out
dropping the view first though I won't recommend it.
Cristian Lefter, SQL Server MVP
"AshVsAOD" <.> wrote in message
news:O4tsMD0OFHA.1176@.TK2MSFTNGP12.phx.gbl...
> Can I drop a table while leaving a view in tact?
> I will recreate the tables with the same name after that...
>