Derived Tables and joining to them

I am struggling with some syntax. I have created a couple of derived tables
and now want to LEFT OUTER JOIN to them. Can someone help me?
Thanks in advance.
Here's the SQL...
SELECT DERIVE1A.column_1,
DERIVE1A.column_2,
DERIVE1A.column_3,
DERIVE1A.column_4,
DERIVE1A.column_5,
DERIVE2A.column_1,
DERIVE2A.column_2,
DERIVE2A.column_3,
DERIVE2A.column_4,
DERIVE2A.column_5
FROM #tbl_Export tbl_Export,
(SELECT column_1,
column_2,
column_3,
column_4,
column_5
FROM #tbl_Export tbl_export_1
WHERE column_x = 'f') DERIVE1,
(SELECT column_1,
column_2,
column_3,
column_4,
column_5
FROM #tbl_Export tbl_export_1
WHERE column_x = 's') DERIVE2,
LEFT OUTER JOIN DERIVE1 DERIVE1A
ON tbl_Export.key_column = DERIVE1A.key_column
LEFT OUTER JOIN DERIVE2 DERIVE2A
ON tbl_Export.key_column = DERIVE2A.key_column
wnfisbaStart by checking the syntax and examples from SQL Server Books Online.
Based on the sample code you posted, you could re-write it along the lines
of:
SELECT *
FROM tbl t1
LEFT OUTER JOIN
( SELECT col1, col2, ...... ) derived_1
ON t1.key = derived_1.col1
LEFT OUTER JOIN
( SELECT col1, col2, ...... ) derived_2
ON t1.key = derived_2.col2 ;
Anith

Derived Tables

OK...I know how to write a query to return for example :

All the people that ordered X and Y

but how do I write one for:

All the people that ordered X but not Y?

Thanks,
TreyO yea...this is how i did the first part

SELECT DISTINCT c.Company
FROM Customers as c
JOIN
 (SELECT CustomerID
 FROM Orders o
 JOIN [Order Details] od
 ON o.OrderID = od.OrderID
 JOIN products p
 ON od.ProductID = p.ProductID
 WHERE p.ProductName = 'X') as temp1
 ON c.CustomerID = temp1.CustomerID
JOIN
 (SELECT CustomerID
 FROM Orders o
 JOIN [Order Details] od
 ON o.OrderID = od.OrderID
 JOIN products p
 ON od.ProductID = p.ProductID
 WHERE p.ProductName = 'Y') as temp2
 ON c.CustomerID = temp2.CustomerID

derive listing of values NOT in table

I have 3 tables..
- unique listing of all employees...
- unique listing of all dates where transactions took place
- detail listing of all transactions
The detail listing of all transactions has a reference to the employee to
whom the transaction belongs as well as the date of the transaction.
I need to come up with a list of employees who did NOT have a transaction
for each date.
My initial thought was to create joins where I would get a listing of each
date along with all employees... then those employees who did not have a
transaction on a given date would showup as a null... for example:
Employees:
pete
david
joe
Dates:
10/5
10/6
10/7
Transactions
10/5 pete shoes
10/6 pete belt
10/6 david shoes
10/7 pete hat
10/7 david pants
I want to join those 3 tables so that I get this outcome:
Date Employee Transaction
10/5 pete shoes
10/5 david <null>
10/5 joe <null>
10/6 pete belt
10/6 david shoes
10/6 joe <null>
10/7 pete hat
10/7 david pants
10/7 joe <null>
I've done this type of thing before... but for some reason it's not working
for me. The outcome is not displaying the <null> rows.. only those rows
which contain a transaction.
Thanks.First Cross join the Employee table and the Dates table to get the full rang
e
of possible values. Then outer join to the Transactions table to get
yourtransactions.
SELECT d.Dates, e.EmpName, t.Trans
FROM Dates d
CROSS JOIN Emp e
LEFT JOIN Trans t ON d.Dates = t.Dates AND e.EmpName = t.EmpName
(Obviously I made up column names and table names :P )
HTH,
John Scragg
"David Sampson" wrote:

> I have 3 tables..
> - unique listing of all employees...
> - unique listing of all dates where transactions took place
> - detail listing of all transactions
> The detail listing of all transactions has a reference to the employee to
> whom the transaction belongs as well as the date of the transaction.
>
> I need to come up with a list of employees who did NOT have a transaction
> for each date.
>
> My initial thought was to create joins where I would get a listing of each
> date along with all employees... then those employees who did not have a
> transaction on a given date would showup as a null... for example:
>
> Employees:
> pete
> david
> joe
> Dates:
> 10/5
> 10/6
> 10/7
> Transactions
> 10/5 pete shoes
> 10/6 pete belt
> 10/6 david shoes
> 10/7 pete hat
> 10/7 david pants
>
> I want to join those 3 tables so that I get this outcome:
> Date Employee Transaction
> 10/5 pete shoes
> 10/5 david <null>
> 10/5 joe <null>
> 10/6 pete belt
> 10/6 david shoes
> 10/6 joe <null>
> 10/7 pete hat
> 10/7 david pants
> 10/7 joe <null>
> I've done this type of thing before... but for some reason it's not workin
g
> for me. The outcome is not displaying the <null> rows.. only those rows
> which contain a transaction.
> Thanks.
>
>|||Please post the offending query. I bet there's something in it tha diminishe
s
the effects of outer joins - that is if you use them at all. Can't tell unti
l
we see the query. :)
ML|||I've never heard of a CROSS join... I'll use that and see what it does for
me.
thanks
"John Scragg" <JohnScragg@.discussions.microsoft.com> wrote in message
news:AFDD7347-E6D1-4C85-99FA-470F0772B468@.microsoft.com...
> First Cross join the Employee table and the Dates table to get the full
> range
> of possible values. Then outer join to the Transactions table to get
> yourtransactions.
> SELECT d.Dates, e.EmpName, t.Trans
> FROM Dates d
> CROSS JOIN Emp e
> LEFT JOIN Trans t ON d.Dates = t.Dates AND e.EmpName = t.EmpName
> (Obviously I made up column names and table names :P )
> HTH,
> John Scragg
> "David Sampson" wrote:
>|||That is EXACTLY what I needed!!!!!
Thanks alot!!!!
David
"John Scragg" <JohnScragg@.discussions.microsoft.com> wrote in message
news:AFDD7347-E6D1-4C85-99FA-470F0772B468@.microsoft.com...
> First Cross join the Employee table and the Dates table to get the full
> range
> of possible values. Then outer join to the Transactions table to get
> yourtransactions.
> SELECT d.Dates, e.EmpName, t.Trans
> FROM Dates d
> CROSS JOIN Emp e
> LEFT JOIN Trans t ON d.Dates = t.Dates AND e.EmpName = t.EmpName
> (Obviously I made up column names and table names :P )
> HTH,
> John Scragg
> "David Sampson" wrote:
>|||I'm not making it up :P
A CROSS join is the cartesian product of two tables. So if you have 3 rows
in each table. A CROSS join will give you 9 rows. Every possible
combination.
It is the same as saying
SELECT d.Dates, e.EmpName
FROM Dates d, Employees e
Notice that there is no where clause. Same as there is no "ON" clause in my
example posted earlier. I just prefer the clarity of describing my join
types over the method show above.
Best of luck,
John
"David Sampson" wrote:

> I've never heard of a CROSS join... I'll use that and see what it does for
> me.
> thanks
>
> "John Scragg" <JohnScragg@.discussions.microsoft.com> wrote in message
> news:AFDD7347-E6D1-4C85-99FA-470F0772B468@.microsoft.com...
>
>sql

Deploying SQL Database

Hi All,

Can anyone tell me,is it possible to create an exe or msi for sql stored procedures,tables and triggers?I want to deploy the database objects(stored proc,tables,views and functions) as an exe file..just like publishing and deploying the asp.net application.Is it possible for sql server database objects.Pls,let me know.

Thank U[:)]Usually database with all objects gets backed up and then restored on another Server. It is the best and safest way to do it.

You also can extract scripts of all objects and then run them on another Server using exe or any means which can connect to that Server.

Good Luck.

Deploying a database witch uses the membership tables

I have a website that uses an SQL 2005 Express database, with the added aspnet_users, aspnet_roles tables etc' inside it.
(That is not an .mdf file, but in the databse itself)

Are there any guidelines for moving this kind of database to the deployment server, which also has SQL Express ?

Is it just a case of detach + attach ?

Hi,

Detach + Attach will ship the whole database (tables, sp, users....)

Regards

deploying a database

Hi, do you know how to deploy a database (along with all the tables and data
in them etc.) from a development machine to the target machine? Can I just
copy the dabase file? (Assume the SQLServer application is already
installed on the target machine)
Thanks!Backup/restore,
Detach/Attach.
Either should work just fine
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Ido" <Ido@.discussions.microsoft.com> wrote in message
news:29EA6106-1D43-4B19-89F5-B2A438DA6B8F@.microsoft.com...
> Hi, do you know how to deploy a database (along with all the tables and
> data
> in them etc.) from a development machine to the target machine? Can I just
> copy the dabase file? (Assume the SQLServer application is already
> installed on the target machine)
> Thanks!|||http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.databasejournal.com/feat...cle.php/3379901 Moving
system DB's
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Ido" <Ido@.discussions.microsoft.com> wrote in message
news:29EA6106-1D43-4B19-89F5-B2A438DA6B8F@.microsoft.com...
> Hi, do you know how to deploy a database (along with all the tables and
> data
> in them etc.) from a development machine to the target machine? Can I just
> copy the dabase file? (Assume the SQLServer application is already
> installed on the target machine)
> Thanks!

deploying a database

Hi, do you know how to deploy a database (along with all the tables and data
in them etc.) from a development machine to the target machine? Can I just
copy the dabase file? (Assume the SQLServer application is already
installed on the target machine)
Thanks!
Backup/restore,
Detach/Attach.
Either should work just fine
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Ido" <Ido@.discussions.microsoft.com> wrote in message
news:29EA6106-1D43-4B19-89F5-B2A438DA6B8F@.microsoft.com...
> Hi, do you know how to deploy a database (along with all the tables and
> data
> in them etc.) from a development machine to the target machine? Can I just
> copy the dabase file? (Assume the SQLServer application is already
> installed on the target machine)
> Thanks!
|||http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.databasejournal.com/featu...le.php/3379901 Moving
system DB's
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Ido" <Ido@.discussions.microsoft.com> wrote in message
news:29EA6106-1D43-4B19-89F5-B2A438DA6B8F@.microsoft.com...
> Hi, do you know how to deploy a database (along with all the tables and
> data
> in them etc.) from a development machine to the target machine? Can I just
> copy the dabase file? (Assume the SQLServer application is already
> installed on the target machine)
> Thanks!

Deploy on the hoster

Dears,

I have devoloped an application ASP.NET 2.0.

Before I have builded the aspnetdb throught the command then I built some tables and stored procedures on this db. (My db is sql express 2005 and my hoster db is sql 2005 workgroup)

My hoster doesn't allow connection Management studio express, doesn't allow attach or restore functionalities.

Than I have built my script db (contains Tables, Views and Stored Procedures), I have substituted the dbo with my user account in the script because my hoster doesn't allow the dbo access.

I have also transfered my site files (.aspx, img, etc.) on the server.

When I try to access the db I receive this error (for example when I push on the button create user):

The SSE Provider did not find the database file specified in the connection string. At the configured trust level (below High trust level), the SSE provider can not automatically create the database file.

Please could you help me?

Thank you.

I Forgot one thing!!

For allow the access throught an user different from dbo I have modified the "ASP.NET Provider Toolkit SQL Samples".

Thank you

dependent tables

How can I find out all dependent tables to a table?
I don't see this information in sysdepends, sysdepends
provides information about dependent procedure, trigger,
views.
Thank you,
Linda
To show all tables with foreign keys to pubs..titles:
use pubs
select object_name(fkeyid)
from sysforeignkeys
where rkeyid=object_id('titles')
"Linda" <anonymous@.discussions.microsoft.com> wrote in message
news:1d9e801c45486$4ec39d50$a301280a@.phx.gbl...
> How can I find out all dependent tables to a table?
> I don't see this information in sysdepends, sysdepends
> provides information about dependent procedure, trigger,
> views.
> Thank you,
> Linda
|||Thank you Adams, this is what I was looking for.
-Linda
>--Original Message--
>To show all tables with foreign keys to pubs..titles:
>use pubs
>select object_name(fkeyid)
>from sysforeignkeys
>where rkeyid=object_id('titles')
>
>"Linda" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:1d9e801c45486$4ec39d50$a301280a@.phx.gbl...
trigger,
>
>.
>

dependent tables

How can I find out all dependent tables to a table?
I don't see this information in sysdepends, sysdepends
provides information about dependent procedure, trigger,
views.
Thank you,
LindaTo show all tables with foreign keys to pubs..titles:
use pubs
select object_name(fkeyid)
from sysforeignkeys
where rkeyid=object_id('titles')
"Linda" <anonymous@.discussions.microsoft.com> wrote in message
news:1d9e801c45486$4ec39d50$a301280a@.phx.gbl...
> How can I find out all dependent tables to a table?
> I don't see this information in sysdepends, sysdepends
> provides information about dependent procedure, trigger,
> views.
> Thank you,
> Linda|||Thank you Adams, this is what I was looking for.
-Linda
>--Original Message--
>To show all tables with foreign keys to pubs..titles:
>use pubs
>select object_name(fkeyid)
>from sysforeignkeys
>where rkeyid=object_id('titles')
>
>"Linda" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1d9e801c45486$4ec39d50$a301280a@.phx.gbl...
>> How can I find out all dependent tables to a table?
>> I don't see this information in sysdepends, sysdepends
>> provides information about dependent procedure,
trigger,
>> views.
>> Thank you,
>> Linda
>
>.
>

dependent tables

How can I find out all dependent tables to a table?
I don't see this information in sysdepends, sysdepends
provides information about dependent procedure, trigger,
views.
Thank you,
LindaTo show all tables with foreign keys to pubs..titles:
use pubs
select object_name(fkeyid)
from sysforeignkeys
where rkeyid=object_id('titles')
"Linda" <anonymous@.discussions.microsoft.com> wrote in message
news:1d9e801c45486$4ec39d50$a301280a@.phx
.gbl...
> How can I find out all dependent tables to a table?
> I don't see this information in sysdepends, sysdepends
> provides information about dependent procedure, trigger,
> views.
> Thank you,
> Linda|||Thank you Adams, this is what I was looking for.
-Linda
>--Original Message--
>To show all tables with foreign keys to pubs..titles:
>use pubs
>select object_name(fkeyid)
>from sysforeignkeys
>where rkeyid=object_id('titles')
>
>"Linda" <anonymous@.discussions.microsoft.com> wrote in
message
> news:1d9e801c45486$4ec39d50$a301280a@.phx
.gbl...
trigger,[vbcol=seagreen]
>
>.
>

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

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

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

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?
ThanksDependencies 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.[/quote]. 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...
>

Deny view on system tables and views

Hello,
Through a GUI, my users can see all the system table or views.
I want to hide these tables and views so the users cannot see them in the
list.
Is it a good idea to:
use master;
deny select on 'systemTable1' to Public
deny select on 'systemTable2' to Public
deny select on 'systemTable3' to Public
...etc...
deny select on 'systemTablen' to Public
or it can have bad consequences?
ThxTo the best of my knowledge, this isn't supported. Move to 2005, where there is explicit support for
this, and by default you can only see objects you have access to (except for databases, but that can
be changed).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Chris Leroquais" <c.le_roq@.caramail.com> wrote in message
news:44884b87$0$851$ba4acef3@.news.orange.fr...
> Hello,
> Through a GUI, my users can see all the system table or views.
> I want to hide these tables and views so the users cannot see them in the list.
> Is it a good idea to:
> use master;
> deny select on 'systemTable1' to Public
> deny select on 'systemTable2' to Public
> deny select on 'systemTable3' to Public
> ...etc...
> deny select on 'systemTablen' to Public
> or it can have bad consequences?
> Thx
>