Deploying Report Server behind web servers

We'd like to deploy our report servers behind our web tier in the app tier
in order to prevent our clients from being able to use url access directly.
My main concern has to do with a chance that a client may attempt to alter
the content of report parameters or otherwise probe around.
Our web code is able to forward requests to the report server and render
reports correctly, but toolbar functionality seems to be broken. The web
developer tells me that the toolbar functionality is too stateful, so
paging (as an example) won't work.
Is what we're trying to do (request forwarding) unsupported? Are my fears
regarding exposing the report server in our DMZ unwarranted? If so, I must
be missing something.What about building your own interfaace or getting the Report Stream from
Reporting Webservice to Qrite the Stream to the browser ? There you could
lock down your reporting server and connect to the RS with special lockedup
credentials.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"JoeA" <joea@.nospam.net> schrieb im Newsbeitrag
news:Xns963D69BAFC14Emailmailcom@.207.46.248.16...
> We'd like to deploy our report servers behind our web tier in the app tier
> in order to prevent our clients from being able to use url access
> directly.
> My main concern has to do with a chance that a client may attempt to alter
> the content of report parameters or otherwise probe around.
> Our web code is able to forward requests to the report server and render
> reports correctly, but toolbar functionality seems to be broken. The web
> developer tells me that the toolbar functionality is too stateful, so
> paging (as an example) won't work.
> Is what we're trying to do (request forwarding) unsupported? Are my fears
> regarding exposing the report server in our DMZ unwarranted? If so, I
> must
> be missing something.|||Thanks for the reply. We have considered that - however we didn't want to
have to recreate all of the goodies in the toolbar.
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
in news:#1t7uEQRFHA.2136@.TK2MSFTNGP14.phx.gbl:
> What about building your own interfaace or getting the Report Stream
> from Reporting Webservice to Qrite the Stream to the browser ? There
> you could lock down your reporting server and connect to the RS with
> special lockedup credentials.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>

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