query database table: contains

Showing posts with label contains. Show all posts

Monday, March 26, 2012

How to remove a recursive relationship

Hi,
I am building a db that contains folders with all info about a folder.
For example :
folder_id PK
folder_name
folder_size etc.
In my case, each folder may have 0 or more childern. A child itself
is unlikely going to have any children in itself. This is a recursive
relationship I'd like to remove in my DB design.
How can I represent this relatonship?
Thank you, DinoPlease elaborate on the issue, if you could. I'm not sure I understand; a
child folder which is the only child of another folder is unlikely to have
any children of its own?
How are you modelling the hierarchy itself? It doesn't HAVE to be recursive
(although that is one option). There are also hierarchical modelling
techniques like enumerated path and nested sets (search Google and you'll
get plenty of info on each) that are not recursive.
"Dino Buljubasic" <dino@.noplacelikehome.com> wrote in message
news:p2sj50p0pcihhqo9i4f0g0kv1thl98see7@.
4ax.com...
> Hi,
> I am building a db that contains folders with all info about a folder.
> For example :
> folder_id PK
> folder_name
> folder_size etc.
> In my case, each folder may have 0 or more childern. A child itself
> is unlikely going to have any children in itself. This is a recursive
> relationship I'd like to remove in my DB design.
> How can I represent this relatonship?
> Thank you, Dino|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.server:335813
I am gonna do it just like:
folder_id PK
parent_folder_id FK
folder_name
this way if the folder has a parent I will know it. I think this
solves the problem.
Thanks anyways,
Dino
On Thu, 18 Mar 2004 14:10:38 -0500, "Adam Machanic"
<amachanic@.air-worldwide.nospamallowed.com> wrote:

>Please elaborate on the issue, if you could. I'm not sure I understand; a
>child folder which is the only child of another folder is unlikely to have
>any children of its own?
>How are you modelling the hierarchy itself? It doesn't HAVE to be recursiv
e
>(although that is one option). There are also hierarchical modelling
>techniques like enumerated path and nested sets (search Google and you'll
>get plenty of info on each) that are not recursive.
>
>"Dino Buljubasic" <dino@.noplacelikehome.com> wrote in message
> news:p2sj50p0pcihhqo9i4f0g0kv1thl98see7@.
4ax.com...
>

Monday, March 12, 2012

How to reader header and footer in EBCDIC file?

I have data arriving in fixed-width EBCDIC format. Each file contains one or more groups of records. Before and after each group there is a header/footer, which is not in the same layout as the records that it describes. Header, record and footer each have a different layout to the other but are consistent within themselves.

Thankfully the one thing header, footer and record layout have in common is their length, so at the moment, using the appropriate code page in the Flat File Connection Manager, I'm able to read all the columns as strings. The headers and footers just come through, albeit a bit weird looking, and I can filter them out with a conditional splt.

However, the header contains information that needs to be appended to each record in the group. Does anyone have any suggestions about how to achieve this? I'm trying to avoid developing a custom data source for this task but, if there's no other way, has anyone done it and do they have any tips?

You should search this forum for discussions on topics similar to this.

You can read the file twice, using two data flows. The first time, to extract just the header and to store its values in a table, perhaps. The second time, to extract just the detail records and to join to the table from the first run to pull in the information you need.

Or, you can use a script task to read through the file. Or a script component inside the data flow. You may have to read in the entire line as one big string though.|||I would read the file twice as does not involved custome code. I knmow John(Jwelch) has some sample code on his blog so that may be another resource apart from searching the forum

How to read XML data from SQL data field?

Using RS2000

I have a field in a SQL DB which contains data stored as an XML string. Is there an easy way to parse this string into data without moving to SS2005?

I have builts a parser in SQL, but I would like to know if RS had a way to directly read this. If not, is there a method in RS2005 which handles this type of data?

The only way to really do this in RS2000 is to create your own custom data processing extension that encapsulates the code you have written to parse the Xml. There are quite a few examples online.

In RS2005, you can use the Xml Data Processing Extension, which is designed for retieving data from xml data sources. It retrieves the data from an xml document which is retireved from a web service, web page, or can use a document embedded in the Xml Query. This extension is very powerful, but can also be somewhat complex for advanced queries.

For more information: http://msdn2.microsoft.com/en-us/library/ms159741.aspx|||

you can write a function in report Code section that will take the xml string, load it into System.Xml.XmlDocument and parse it.

<?xml version="1.0" encoding="utf-8"?>

<ConnectString>server=.</ConnectString>

</ConnectionProperties>

<rd:DataSourceID>6cfe005e-c5e0-4297-aa6b-f2ca0673ec05</rd:DataSourceID>

</DataSource>

</DataSources>

<rd:DrawGrid>true</rd:DrawGrid>

<rd:SnapToGrid>true</rd:SnapToGrid>

<Body>

<rd:DefaultName>my_xml_1</rd:DefaultName>

<Style>

</Style>

<Value>=Code.ParseXml(Fields!my_xml.Value)</Value>

</Textbox>

</ReportItems>

<DataSetName>DataSet1</DataSetName>

</List>

</ReportItems>

</Body>

<rd:ReportID>c9ad02b9-b7a2-4780-b954-3ebaa11e0afb</rd:ReportID>

<Query>

<rd:UseGenericDesigner>true</rd:UseGenericDesigner>

<CommandText>select '<root>Hello world!</root>' my_xml

union select '<root>Goodbye!</root>'</CommandText>

<DataSourceName>DataSource1</DataSourceName>

</Query>

<rd:TypeName>System.String</rd:TypeName>

</Field>

</Fields>

</DataSet>

</DataSets>

<CodeModule>System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</CodeModule>

</CodeModules>

<Code>public shared Function ParseXml(xml as string) as string

Dim doc as System.Xml.XmlDocument = new System.Xml.XmlDocument()

doc.LoadXml(xml)

ParseXml = doc.SelectSingleNode("root").InnerText

end function</Code>

</Report>

Wednesday, March 7, 2012

How to read a .PST file from a SSIS package?

Hi everyone,

I'd like to read a .PST file (which contains thousands of mails) regardless of Exchange client.

Is it possible?

Thanks for your input

SSIS doesn't have an out-of-the-box component to do just that. If there's an OLE DB, .NET or ODBC provider for that data source, you can try that (I'm not sure there is one, but it's amazing to see the wealth of connectors out there for surprising data sources, you might want to check that).

Perhaps, another way could be exporting .PST file into some format SSIS can consume (i.e. flat file or xml) and get the data into SSIS that way.

And of course, you can always write your own component and plug it in SSIS.

|||Thanks for that.

Sunday, February 19, 2012

how to query for a column value that contains dashes

Hi,

I have a large table with a 'datetime' column that has date and time
values in it. The data is in this format:

2007-10-02 09:54:00.000

The table is called 'profile' and the column 'msgdate'

I want to return only rows that match a specific date. So far I have
the following query working:

select * from profile where msgdate like '%2007%'

This returns all rows that start with '2007'. However I cannot seem to
ge the syntax that will allow me to return a specific date, e.g.
2007-10-02

I have researched this, trying all sorts of queries with escape
characters/sequences because of the dash character, but I cannot get
it to return anything. Most of my queries have ran without error, its
just that no data is returned.

JamesWhat exactly is the data type of the column msgdate? You mention it
is a 'datetime' column. If it is the datatype of datetime then forget
about strings, it is stored internally as a couple of numbers. With a
non-zero time the standard way to test for a specific date is:

WHERE msgdate >= '20071002' AND msgdate < '20071003'

Note that the second date is the next day. This approach allows use
of an index is one is available and otherwise makes sense.

Roy Harvey
Beacon Falls, CT

On Fri, 12 Oct 2007 10:01:32 -0700, jdrake@.living-dead.net wrote:

Quote:

Originally Posted by

>Hi,
>
>I have a large table with a 'datetime' column that has date and time
>values in it. The data is in this format:
>
>2007-10-02 09:54:00.000
>
>The table is called 'profile' and the column 'msgdate'
>
>I want to return only rows that match a specific date. So far I have
>the following query working:
>
>select * from profile where msgdate like '%2007%'
>
>This returns all rows that start with '2007'. However I cannot seem to
>ge the syntax that will allow me to return a specific date, e.g.
>2007-10-02
>
>I have researched this, trying all sorts of queries with escape
>characters/sequences because of the dash character, but I cannot get
>it to return anything. Most of my queries have ran without error, its
>just that no data is returned.
>
>
>James

|||Something like this (untested, but you see the point):

select <col list>
from profile
where msgdate >= @.date and msgdate < dateadd(dd,1,@.date)

date would be a parameter...

<jdrake@.living-dead.netwrote in message
news:1192208492.248548.49030@.e34g2000pro.googlegro ups.com...

Quote:

Originally Posted by

Hi,
>
I have a large table with a 'datetime' column that has date and time
values in it. The data is in this format:
>
2007-10-02 09:54:00.000
>
The table is called 'profile' and the column 'msgdate'
>
I want to return only rows that match a specific date. So far I have
the following query working:
>
select * from profile where msgdate like '%2007%'
>
This returns all rows that start with '2007'. However I cannot seem to
ge the syntax that will allow me to return a specific date, e.g.
2007-10-02
>
I have researched this, trying all sorts of queries with escape
characters/sequences because of the dash character, but I cannot get
it to return anything. Most of my queries have ran without error, its
just that no data is returned.
>
>
James
>

How to Query a nested Hierarchy in TSQL

All:
I have a table called Users and there is a column called ParentID that
either contains 0 or another valid userID. This is what I want to do: When I
have a userID, I want to retrieve all UserIDs until ParentID for a searched
user is 0.
Script to simulate the environment:
create table users
(userID Int,
parentID Int)
insert into users
select 1, 0
union all
select 2, 1
union all
select 3, 2
union all
select 4, 3
union all
select 5, 4
union all
select 6, 4
union all
select 7, 5
union all
select 8, 6
union all
select 9, 7
union all
select 10, 8
Situation: I will be supplied with a specific user ID and I need to retun
all userIDs in comma delimited format until ParentID becomes 0 in the
hierarchy.
Example:
UserID 1 should return only one row as the ParentID is 0 for userID 1
UserID 8 should return following userIDs
6,4, 3, 2, 1
I hope everything is clear.This article may give you some idea.
http://www.windowsitpro.com/Article...5715/15715.html
Regards
Mark wrote:
> All:
> I have a table called Users and there is a column called ParentID that
> either contains 0 or another valid userID. This is what I want to do: When
I
> have a userID, I want to retrieve all UserIDs until ParentID for a searche
d
> user is 0.
> Script to simulate the environment:
> create table users
> (userID Int,
> parentID Int)
> insert into users
> select 1, 0
> union all
> select 2, 1
> union all
> select 3, 2
> union all
> select 4, 3
> union all
> select 5, 4
> union all
> select 6, 4
> union all
> select 7, 5
> union all
> select 8, 6
> union all
> select 9, 7
> union all
> select 10, 8
> Situation: I will be supplied with a specific user ID and I need to retun
> all userIDs in comma delimited format until ParentID becomes 0 in the
> hierarchy.
> Example:
> UserID 1 should return only one row as the ParentID is 0 for userID 1
> UserID 8 should return following userIDs
> 6,4, 3, 2, 1
> I hope everything is clear.|||This article may give you some idea.
http://www.windowsitpro.com/Article...5715/15715.html
Regards
Mark wrote:
> All:
> I have a table called Users and there is a column called ParentID that
> either contains 0 or another valid userID. This is what I want to do: When
I
> have a userID, I want to retrieve all UserIDs until ParentID for a searche
d
> user is 0.
> Script to simulate the environment:
> create table users
> (userID Int,
> parentID Int)
> insert into users
> select 1, 0
> union all
> select 2, 1
> union all
> select 3, 2
> union all
> select 4, 3
> union all
> select 5, 4
> union all
> select 6, 4
> union all
> select 7, 5
> union all
> select 8, 6
> union all
> select 9, 7
> union all
> select 10, 8
> Situation: I will be supplied with a specific user ID and I need to retun
> all userIDs in comma delimited format until ParentID becomes 0 in the
> hierarchy.
> Example:
> UserID 1 should return only one row as the ParentID is 0 for userID 1
> UserID 8 should return following userIDs
> 6,4, 3, 2, 1
> I hope everything is clear.|||Mark,
Try this function:
create function dbo.fn_getpath(@.uid as int) returns varchar(8000)
as
begin
declare @.path as varchar(8000);
set @.path = cast(@.uid as varchar(10));
while @.uid <> 0
begin
set @.uid = (select parentid from users where userid = @.uid);
set @.path = @.path + ',' + cast(@.uid as varchar(10));
end
return @.path;
end
go
select dbo.fn_getpath(5)
BG, SQL Server MVP
www.SolidQualityLearning.com
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:1C6A7C66-191E-41A7-AD3C-33E84232CCF6@.microsoft.com...
> All:
> I have a table called Users and there is a column called ParentID that
> either contains 0 or another valid userID. This is what I want to do: When
> I
> have a userID, I want to retrieve all UserIDs until ParentID for a
> searched
> user is 0.
> Script to simulate the environment:
> create table users
> (userID Int,
> parentID Int)
> insert into users
> select 1, 0
> union all
> select 2, 1
> union all
> select 3, 2
> union all
> select 4, 3
> union all
> select 5, 4
> union all
> select 6, 4
> union all
> select 7, 5
> union all
> select 8, 6
> union all
> select 9, 7
> union all
> select 10, 8
> Situation: I will be supplied with a specific user ID and I need to retun
> all userIDs in comma delimited format until ParentID becomes 0 in the
> hierarchy.
> Example:
> UserID 1 should return only one row as the ParentID is 0 for userID 1
> UserID 8 should return following userIDs
> 6,4, 3, 2, 1
> I hope everything is clear.
>|||Look at this example:
http://milambda.blogspot.com/2005/0...or-monkeys.html
ML
http://milambda.blogspot.com/|||"Muhammad Akhter" <mnadeemakhter@.gmail.com> wrote in message
news:1134511951.052763.116790@.g44g2000cwa.googlegroups.com...
> This article may give you some idea.
> http://www.windowsitpro.com/Article...5715/15715.html
Lets just count the *years* that this kind of approach is the only one
available:(:)|||Get a copy of TREES & HIERARCHIES IN SQL for several approaches to this
problem.
Why do you wish to destroy First Normal Form (1NF)? It is the
foundation of RDBMS, after all.
Why are you formatting data in the back end? The basic principle of a
tiered architecture is that display is done in the front end and never
in the back end. This a more basic programming principle than just SQL
and RDBMS.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1134875755.271066.18350@.g44g2000cwa.googlegroups.com...
> Get a copy of TREES & HIERARCHIES IN SQL for several approaches to this
> problem.
Good luck with the book...although some may wonder why
it still is appropriate in the 21st century:)

Monday, March 26, 2012

How to remove a recursive relationship

Monday, March 12, 2012

How to reader header and footer in EBCDIC file?

How to read XML data from SQL data field?

Wednesday, March 7, 2012

How to read a .PST file from a SSIS package?

Sunday, February 19, 2012

how to query for a column value that contains dashes

How to Query a nested Hierarchy in TSQL

query database table

Blog Archive

About Me