Rendering Directory Structures

Oct 31, 2005

Hi, I have a table of folders that I would like to be able to display
in a depth first manner, similar to what you would see in Windows
Explorer. the table is defined similar to

CREATE TABLE Folders (
folderID int,
parentFolderID int,
name varchar(32)
)

.... I've left some things out of the definition for the simplicity of
example.

My desire is to quickly generate a record set containing the all child
folders of a specific folder along with the how many levels deep each
folder is, in a quick and memory efficient manner. I have done some
research through this group and found a few examples similar to what I
have hypothesized, however I was hoping for some feedback on what I
have decided to use.

Since this database table is already created and I cannot modify the
existing schema, it would not be very feasible to use a nested sets
model (unfortunately -- or if anyone has a suggestion?).

I plan on using a stored procedure that recusively calls itself.
something similar to the following:

/* This is assuming the table #TempFolderTable has
* already been declared globally
*/

sp_FolderDisplayRecurive @folder int, @level int AS
IF @@NESTLEVEL > 31 RETURN;
DECLARE @childFolder INT
DECLARE folders_cursor AS CURSOR LOCAL FOR
SELECT folderID FROM Folders
WHERE parentFolderID = @folderID
OPEN folders_cursor
FETCH NEXT FROM folders_cursorINTO @childFolder
while @@Fetch_status = 0
BEGIN
-- Insert current folder into
-- folder table (under parent @folder)
INSERT INTO #TempFolderTable
(FolderID, DepthLevel, ParentFolderID) VALUES
(@childFolder, @level, @folder)
-- Iterate over all of its children
EXEC sp_FolderDisplayRecursive
@childFolder, @level + 1

FETCH NEXT FROM folders_cursor INTO @childFolder
END
...


Will this incure a large overhead for a deep directory structure? Are
there any other problematic issues? Any suggestions and/or comments
are very welcome. Thanks!

View 3 Replies


ADVERTISEMENT

Either Rendering Tiff Images In SRS Report Viewer Or Converting Tiff To Jpg Or Gif And Then Rendering

Jun 19, 2007

First of all, this is not in reference to using SRS (SQL Reporting Services) to render a report and then use one of the extensions to render the complete report as a pdf, tiff, excel etc. We have an opportunity to render a list of claims and then embed the supporting docs for each of the claims within the report. We don't have an issue referencing jpg and gif images via URLs and then rendering them within the report after the grid information. We do have an issue rendering tiff images within the report.



You can insert an image object into SRS at design time and have it render and you can convert a complete report to a tiff image but I cannot find a way to be able to render a tiff image when running the report. All you get is the red "x".



My question is has anyone encountered the same issue and, if so, what did you do to resolve the issue?



Thank you,



J Z

View 3 Replies View Related

Using Provider For Microsoft Directory Services For Querying Active Directory

Apr 6, 2007

Has anyone used this successfully from an OLEDB source component, or even from the Execute SQL Task? I've seen some examples of using a script component, but nothing that uses it through a connection manager.

View 6 Replies View Related

FileGroup Structures

Apr 14, 1999

I have 2 test database identical in size and table structure, only one resides on filegroups and one resides on a single file.
From everything I have heard filegroups are suppose to improve performance. I stop and start the server before each test to clean out
cache. When I run the filegroup test initially the first run averages 20 seconds slower then the database sitting on the single file. However
on consective runs there is a significant improvement in performance with filegroups verses single files. I have
the indexes sitting on a separate filegroup and 2 additional filegroups to spread the tables across. Does any one know what would be causing
the performance to degrade on the initial run of the test. (The test by the way is a stored procedure that runs a select statement against each table).

Any Info Will help Thanks
Barb

View 3 Replies View Related

Tree Structures In SQL

Jun 11, 2006

Hi,

i'm writing a app in c# and have to store Trees in a Database.

I'm working with Datasets for the exchange between the DB and the App.

The trees have the same options like the windows folders. If u delete a node, all subnodes should be deleted too.

But something a Foreign Key from ParentID references (Id) with the delete-Rule on cascade seems not to be possible, because of multiple cascade Paths or cycles. Do i have to add some xtra constarins:

Not Possible:

create Table tree (
Id varchar Not null,
ParentId varchar Not null,
Constraint pk1 Primary Key (Id),
Constraint fk1 Foreign Key (ParentId) references tree(Id)
On Update Cascade
On delete CAscade
)


Do i have to write triggers, which delete The subnodes too and set the Update-/deleterulr on NO Action

Greetz

View 1 Replies View Related

Tree Structures...

Oct 24, 2006

Does anyone know any good links for SQL tree structures and example queries and stuff... I cant really find anything part from the standard example of emplyee, boss, salary which explains how to create the tree table...(dun dis bit) I did notice a book but I live in a little village so cant go get it till wekend?

I'm desperate, reli need to work out how too do this.....

View 14 Replies View Related

Data Structures

Nov 14, 2006

(Wow; surfing this site has really illuminated what a lowly hack-programmer I am to this field of SQL and relational processing :S )I am creating a temp table, doing an Bill of Material explosion for a single Order Line Item.(note: SQL Server 2000)I used blindman's "accumulator method (click here) (http://www.dbforums.com/showpost.php?p=6239109&postcount=6)" to generate the entire potential BOM tree.So; step 1 works wonderfully! :beer: Cheers blindman!Now; I want to remove unwanted nodes (and all their children) from the temp-table. I have a bunch of functions (0 to 1 for each node) that return a Yes or No. If "No", then flag-to-eliminate immediately that branch and don't revisit anything on it (and therein lies my problem). This will leave me with a temp table of only valid nodes.Since this is recursive, and since it will involve Dynamic SQL (the function name varies), all I can come up with is using a Cursor in a WHILE loop. Even at that; since a CURSOR is point-in-time (ie: values don't change once selected), I'll have to re-check the current temp table values (or create of 2nd temp table of only deleted nodes and repeat a SELECT with NOT EXISTS in it, hmmm).Since blindman's method generates a table ordered by level, the sequence of processing is pre-determined - unless I can re-order it into a more traditional hierarchy (1 entire branch at a time) and number the levels, in which case the cursor could just skip to the next branch of equal or higher level.Note: The thought does occur to me I could have an intermediary function (static name) that in turn does the Dynamic SQL. These functions contain the Business Logic that looks at a myriad of column values and relationships in the database and there's no one-size-fits-all decision tree so Dynamic SQL is necessary.The max cursor size will be maybe 300, and on average 100. Number of levels will normally be 3 or 4, but conceivably could be up to 10. Given the average 100 potential components/sub-assemblies, the final assembly will be about 30. As a periodic background process; it will do 3,000 Order Line Items a day, so I'm figuring 1 second response time per build is adequate (ie: the user's not waiting on it so it doesn't have to be blinding fast) - however why waste?Anyhow; I thought this might be a fun problem for some Data Structure genius who wants to give a lesson in Relational Programming.Thanks for looking.Here's what I have so far:CREATE PROCEDURE dbo.sp_ExplodeTest1 (@recID int = 1) AS/* tbTestH is a table containing an assembly hierarchy. Assemblies with no parent are Builds.Assemblies with no children are Components.It's columns: MyID int, ParentID int, (other descriptive columns)*/declare @t table (TempNodeID int identity(1,1),MyID int)-- Seed the tree with the Build's ID.insert into @t (MyID) values (@recID)/* This populates the temp table with the entire Assembly for the given build. It is Ordered By the level in the assembly. Number of assembly levels is infinite.For example: Level 1 = the Build. It comes firstLevel 2 = all parents are level 1. Is next (no particular seq)Level 3 = all parents are level 2. Is next (no particular seq)etc.*/while @@Rowcount > 0insert into @t (MyID)select tbTestH.MyID from tbTestHinner join @t rList on tbTestH.ParentID = rList.MyIDwhere not exists ( select *from @t CurrentList where CurrentList.MyID = tbTestH.MyID)-- now to display the results so far.select t.*, tbTestH.* from tbTestH inner join @t t on tbTestH.MyID = t.MyID order by t.TempNodeIDGO

View 8 Replies View Related

SQL Data Structures

Aug 9, 2007

What is included under SQL Data structures? Could anyone refer me to some good tutorials on that material?

View 2 Replies View Related

Mining Structures

Aug 23, 2006

I am using the wizard to create a new mining structure and getting the error at the time of selecting the datamining technique like either microsoft decision tree or microsoft timeseries etc.,

"Unable to retrieve a list of supported data mining algorithms. Make sure you are connected to the correct Analysis Services server instance and the Analysis Services server named localhost is running and configured properly. You can continue with a default list of data mining algorithms."

What is that means?

Thanks in advance,

View 3 Replies View Related

Printing Out Table Structures......

Nov 15, 2000

I have a SQL 7 database with about 30 tables in. For documentation purposes I need to print out the structures of each table. I have tried using the diagram tools but no joy.

I have access to Access, Crystal Reports, if these are any use?

Please help!!

Tony Kavanagh

View 3 Replies View Related

Tools For Db Structures Comparision

Jul 10, 2002

Hi,Does any of u folks used any tools for database structures comparision??

I have used DBDIFF(from DKGAS.com) ChangeManager from Embarcadaro and SQL COMPARE from Redgate.

I did have have some problem in each of these tools AND am looking for some good tools which are economical also.

Thx in advance
Ravi

View 2 Replies View Related

Complex Data Structures

Oct 5, 2001

A point for general discussion maybe.

I've just read Jeff's query regarding passing an array to a stored procedure, and it seems to be a question that hits the message board quite frequently. There often seems to be the case where you have some block of data in one location, and want to process it somewhere else, and there just isn't an easy way to do it (or is there?). Passing a cursor is possible, but getting data back out of a cursor is painful.

The ways I have used in the past are:

Using a cursor
Using a global temporary table
Using a "permanent" table, but declared with a unique name (using a timestamp) and passing the tablename as a parameter.

Has anybody any suggestions for a future SQL release which we could suggest to Microsoft?

Suggestions from myself:
-Having a single TSQL command to copy a cursor back into a table, to make cursor parameters less cumbersome
-Being able to pass a pointer to a temporary table as an SP parameter
-Allowing variables to be declared with more complex structures (such as arrays, or classes)

Regards,
ChrisH

View 1 Replies View Related

Table Structures Script

Dec 11, 2006

I will soon be writting a script which will search threw all tables in my database and try to find how they may be linked to one another, and also which tables are called by which stored procedures, any suggestions or tips would be great.

View 1 Replies View Related

How To Compare Database Structures?

Jul 20, 2005

Let us suppose that I have two similar databases and need to create ansql-script upgrating one database structure to another. For example, thesedatabases are from different versions of some software, first is from earlyversion, next is from current, and second one contains several new tables,sevelal new fields in old tables, several new or changed stored procedures,UDFs and so on.How to solve this problem using standard tools?

View 6 Replies View Related

Comparing Table Structures.

Oct 18, 2006

Hi all,

Could anyone let me know if there are any Scripts available for comparing the schema differences between two SQL server databases?

Thanks

DBAnalyst



View 2 Replies View Related

Compare Tables Structures

Apr 1, 2008



Hi All,

I have 2 tables in different database. I need to compare the tables structure and insert the fields if not existing.

Ex:

I have 3 fields in table1.(database name Pubs)

Table1
F1
F2

F3

I have 2 fields in table2.(database name northwind)

Table2
F1
F2


I need to insert the table1.F3 into Table2. Basically i need to compare all the fields with table1, if not exist in table1 i need to insert that field into table2. I am using SQL Server 2000. Please send me the code.

Thanks,
Mears

View 7 Replies View Related

Open Source DB Structures

Oct 27, 2006

I'm wondering if anyone knows of a good site to find open source database structures (such as ERD's, scripts, or something of that nature)?

I think I have a pretty good handle on database design, but it would be nice to have a resource like that so that I can compare the design of my DB to DB's that people (smarter and more experienced than me) have created for similar purposes.

What I'm thinking of is a site like Open Source Web Design (www.oswd.org) for Databases. Anyone know of anything?

View 1 Replies View Related

Changing Database Structures Through Code

Apr 28, 2006

I am having a hard time finding materials on this subject.  I am guessing I am using the wrong keywords to search.  Basically, I want to be able to modify database tables through a web form.  They can add columns and delete columns through the form.  I would just want to default the type of column and the length.  I am sure it has been done, I was just wondering if anyone had some resources they could throw my way.  I would appreciate it.  Thanks.

View 1 Replies View Related

Merging Two Databases With The Same Table Structures

Oct 20, 2000

Hi
I have a problem out here. I have two databases with the same table structure
and I want to merge data from both the tables . Please can anyone let me know.
Iam using Sql Server 7.0.

Thanks
Bye

View 1 Replies View Related

Printing Out Table Structures, Rows

Dec 5, 2000

Hello !! :O)

I have a SQL 7 database with some tables in. For documentation purposes I need to print out the structures of each table and all the rows in every table.

Please help!!

Thanks

View 2 Replies View Related

Copy The Table Structures Of All The Tables

Nov 16, 2006

CREATE TABLE table_name AS SELECT STATEMENT WHERE rownum = 0; this query will copy the table structure of one table without copying the data. I need to copy the table structures of all the tables in a particular database without copying the data.suggest me on this.

View 6 Replies View Related

Sql Server 2005 And Tree Structures

Mar 9, 2007

I am trying to traverse a tree structure like below,

1 Pets
--1.1 Cat
----1.1.1 Persian
----1.1.2 Bengal
--1.2 Dog
----1.2.1 Poodle
2 etc

I would like to be able to search by a keyword, i.e. Poodle, or the reference number, i.e. 1.2.1. I would prefer to do this all through a stored procedure if possible, it seems recursion is the way to go as the number of levels may increase in the future but i'm completely new to this. From what i've seen so far I would need a table structure with a parentID,NodeID,Name field and Primary key.

i.e,

ID...Name......Parent.....NodeID
1....Pets.........0............1
2....Cat..........1............1
3....Dog..........1............1
4....Persian.....2............1
5....Bengal......2............2
6....Poodle......3............1
etc

i've heard that SQL SERVER 2005 provides recursion through CTE, is this the recommended way/only way to achieve this?

Any tips on where to start would be really appreciated.

View 3 Replies View Related

Concurrency Issues With Tree Structures.

Jul 23, 2005

Hi,I'm currently implementing a database with a tree structure in a table. Thenodes in the tree are stored as records with a column called "Parent". Theroot of the tree has a "NULL" parent. The path to each node is stored inthe column "Path" and is of the form "000010000200003" etc. Thelatter enabling me to fetch subtrees using the "LIKE" predicate. I alsohave created the relation "ID" <-> "ID_Parent, effectively the table isrelated to itself. I did this so that an attempt to remove a parent whenthat parent still has children will fail due to referential integrity.Unfortunately, in order to delete subtrees, I have to first set all of thenodes in the subtree to point to the "NULL" parent so that I don't getcaught with integrity errors during deletes.Unlike a typical linear system, any given node record is related to morethan one of the other records. What I mean is it is possible to follow achain from any given node back to the root (ancestors) or collect a seriesof branches and leaves (descendants) and so it is not reasonable to considerany given node in isolation. Changes to any given descendant can triggerchanges which are propagated to it's ancestors. I am not using recursion todo this, rather, I am using an iterative approach to select and update theparent, then the parents parent, etc. according to it's new state (given bythe state of it's immediate descendants), right back up to the root.Now, the problem comes when I consider concurrency with respect to thisscheme. It seems to me, that locking the record I am updating is notsufficient to ensure clients are kept synchronised or the integrity of thetree structure is correct. I think I need to lock all ancestors of the tree(HOLDLOCK) before performing any operation on a given node. Is thisreasonable? Also, consider the "delete" problem given above. I reallyshould HOLDLOCK on the entire subtree of any node I wish to delete as I amgoing to set the entire subtrees parent values to NULL. I don't wantanother client to perform a read on part of the subtree while the nodes are"parentless" pending deletion.Secondly, I am not sure how to handle synchronization of the tree for eachclient. How does each client know when a change has been made to the tree?Consider a client holding a copy of the tree in memory. Another clientdeletes a subtree. The first client attempts to update one of the deletedsubtree nodes and fails because the node no longer exists. At this point,in the eyes of the first client, all of the ancestors and all of thedescendants of the node in question must become suspect. Should thesoftware now attempt to re-build this part of the tree? It seems that anyoperation on any of the nodes in the tree will potentially make a lot ofother nodes suspect and so my application will be doing a lot of updating.I would be interested to hear any insights people have on these issues,particularly those implementing a "file system" structure in their databaseor similar. How do you deal with these concurrency issues when manipulatingtrees?ThanksRobin

View 4 Replies View Related

'Diff' For Tables *structures* Rather Than Data

Jul 20, 2005

If I have two sql server databases that started out with identicaltable/key/index structures, but were not properly kept in sync, isthere any way I can generate a table change script to essentially'diff' the two databases and come up with table change scripts tobring one in line with the other?An answer to this age-old question of mine would make me veryhappy...!Brian

View 2 Replies View Related

How Can I Print Table Structures In SQLExpress

Oct 20, 2006

I need to get a print out of column names, types, and properties for each of the tables. We just started using SQL Express from Access. I cannot find any reference to printing the structures in Microsoft SQL Server Management Studio Express.

Thanks

View 6 Replies View Related

Duplicate Table Structures To A New Database

Aug 7, 2007

Hi all expert,

Question as stated title above, how do I duplicate the table structure to another database whether it copies all columns, primary and foreign keys, default values, descriptions, constraints, indexes

Many thanks for the help.

regards,

View 8 Replies View Related

Copy SQL Data Structures From One Server To Another

Oct 17, 2007

Hello,
I have a sql database server (let call it server A) which has SQL program files installed on C:, and SQL data structures MSSQL (include subdirectories BAckup, DATA, FTDAT, JOBS, LOG, REPLDATA) on F:. A new server (call it server B) was cloned of server A but only at the C: partition. I still need to manually copy the SQL data structure on F: drive from A to B. The purpose of setting up server B is to test the restore of backup of server A and eventually want it to serve as the standby server should server A crashes. My questions are
1. Can MSSQL directory be copied from A to B without stopping the SQL server service on A first?
2. I will use robocopy for copying data over, what switches should I use to retain all database file security, permission, etc..
3. Currently I can not start the SQL service on server B because part of MSSQL directory is missing from the F: drive. so theorically, after I manually copy that folder over, I should be able to start the sql service. Am I right? Is there any steps that I might have overlook here. Any errors I might encounter doing it this way

Once I can get the sql started, I can take care of the restore part.
Thanks for any insights or suggestions you can provide on this.


View 7 Replies View Related

Exporting Table Structures To Another Database

Sep 26, 2006

We have databases with large numbers of tables. We have a separate database for each year. For various reasons, we need to export about 100 of the tables (Structure only, not their data) from last years database into this year's database. What is the best method for doing this? The import/export wizard creates the tables but does not bring in important things like keys.

Regards Shirley A

View 3 Replies View Related

Imports Table Structures In SQL 2000 Into Excel

May 18, 2004

Hi.
Is there anyway to export the table structures : data type,length,NULLABLE,Description into an Excel file using MS SQL Server?

Or I need to do it manually?
Thank you in advanced.
Sincerely

Agustina

View 2 Replies View Related

Importing MSaccess Files And Structures Into SQL6.5 Is BCP The Only Way??

Mar 25, 1999

I was wondering if the only way to import MSaccess files into sql6.5 is to dump the MSaccess file into a text file and then recreate the structure in SQL6.5 and then use the bcp command to copy the data into the new structure.

Is there an easier way?

thanks in advance.

Rich
LoPingKill
loping@inlink.com

View 3 Replies View Related

Urgent Help.. Import Files With Different Structures And Formats..?

May 30, 2007



In SSIS it is very easy to import any kind of file using the Import/Export Wizard but programming in SSIS package to do the same thing is almost impossible. It can even be done by using OPENROWSET in SQL management studio but then there are problems with Excel data types and etc..



I have done my research and so far I couldnt figure it out, I know poeple have done it but I am new to SSIS and I need clearer instructions. This is my situation..



I have created a package that checks an then creates a database and then creates a table based on the Excel/csv/sdf/dbf etc file. (The structure is captured in another database using a front end app). I am stuck at importing the file into the table that I just created. I cannot use the design time tools as the files coming in will be in different formats and structures. I have found different examples of desinging the package programatically but I dont know how to:



1. Convert C# to VB. Net (Actually Im looking for the example of the code in VB.net rather than converting it)

2. Add the code in my design (Most of the code that I have seen looks like they are creating the package from scratch).



I am following this but I keep getting stuck.. (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=749383&SiteID=1)



Any help will be appreciated..

I have used these key words for my research so far: copy data from excel to SQL, vb .net ssis custom column maping, cutsom transform, custom data flow , build column mapping programatically, script component custom source destination transform mapping



Hope answer to this thread will help many people (like me) who are in the learning process..

View 3 Replies View Related

How To Scripting Existing Mining Models And Structures?

Dec 18, 2007

Hi!
If you make an view you can script it in SSMS and get the DDL.
How to do something similar with mining models/structures getting DMX?
I see you easy can get XMLA, but I would prefer the "dmx-ddl"...

Thanks
Bjorn

View 1 Replies View Related

Processing Mining Models &&amp; Structures Via SSIS

Dec 12, 2006

Still new to DM and SSIS...anyand all help is greatly appreciated!

In SSIS they say that you can use the Analysis Services Processing Task to process a mining model/mining structure, however, I do not see where you can give it a relational table to work off of. I know that I can use a data flow to do this but I wanted to go a different route if I could to process my models as I don't really necessarily need the data flow as what I am tring to do is pretty simple.

That brings me to a more general question, what is the best method for training your models using SSIS? I am building a new model everytime the package runs using some variables and the DDL task, running a query on it, and destroying it at the end of the package but I am having logistical problems training it outside of the data flow. I tried using the DM Query task but it requires that you output a result set and I am not sure if I can use it to create and process models.

I would think that they would just give you a DMX task similar to the SQL task but that does not seem to be the case. Also, when I browse the AS objects via the processing task I can only see the mining structures and not the mining models.

Am I just missing something here?


Cheers,

Dan Meyers

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved