Anyone know about Parsing an email?

Hello everyone

Heres what it looks like:

I have a large file of over 40k email records. The emails are all mixed up and come in various formats but i noticed that most of them are in this format:

firstname.lastname@.email.com
firstname.middlename.lastname@.email.com

For all those emails with the period (.) in between, the (.) actually separates an individuals first and last name.

My task is this, to separate all the emails that are in this format into first and last name fields. I'm stimped folks and I'll really appreciate any pointers or ideas on how to go about solving this task.

Thanksuse substring and charindexsql

Any work around to pass parameters to OPENQUERY

I need to query a linked server which contains a table of million records an
d
need to fetch only the relevant records from the linked server.
Any ideas? Please helpSaji
Have you tried to use WHERE condition? Can you show us what you are trying
to do?
"Saji" <Saji@.discussions.microsoft.com> wrote in message
news:31530C04-03B9-4C24-9169-CBBD26773F3C@.microsoft.com...
>I need to query a linked server which contains a table of million records
>and
> need to fetch only the relevant records from the linked server.
> Any ideas? Please help|||This seems to work..
SELECT * FROM OPENQUERY(PS, '
SELECT
*
FROM
TESTDTA.F0401Z1
JOIN TESTDTA.F0101Z2 ON TESTDTA.F0401Z1.VOAN8 = TESTDTA.F0101Z2.SZAN8
WHERE
VOEDSP != ''C'' AND
VODRIN = ''2''
ORDER BY VOAN8, VOEDBT
')
"Saji" wrote:

> I need to query a linked server which contains a table of million records
and
> need to fetch only the relevant records from the linked server.
> Any ideas? Please help

Any way to run a invisible trace on Security Audit?

Is there anyway I could run an Audit trace on SQl Server which records SQL Server System Admin Login/Logout , failed login and machine names I don't want the trace window to show on screen however would like a file generated for later viewing. Also due to Firewall issues we have, We don't have SQL tools enabled to connect to that server.Yes, you can configure SQL 2000 Auditing (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_2ard.asp), up through the C2 level. Unfortunately there isn't a "cookbook" approach for doing it, you really need to understand what you are doing, and the consequences and benefits of each decision.

-PatP|||I found out . Thanks for reply. C2 auditing however is not needed . I found out that from SQL Profiler , you can script the whole trace as SQL and execute it in ISQLW . This gives back a Trace ID . Next you have to run sp_trace_Setstatus @.traceID, 1 to run it .

Thanks for responding . I appreciate that

Any tips for using large parameter lists

Our parts table has 5k records. I want to use part number as a parameter for one of my reports. Is there a way to do this and have the report generate in a reasonable amount of time?

Thanks

Create a new dataset on the Data tab with the query as "SELECT part_number from Parts" and name the dataset as, say DataSet2.

Go to Report (menu) -> Report Parameters

and add a new parameter and give the name, type and prompt.

Then under Available Values, select "From Query" radio button

select DataSet2 under Dataset, part_number under Value field

Shyam

|||Thats the problem.
It takes five minutes for the param list to render.|||

Maybe you can reduce the query time on SQL server by adding index (clustered preferably) to part_number column. This is the only solution to reduce the load time.

Shyam

Any significant differences between these two date CAST statements

I need to find records that have a date stamp of yesterday. I have created two where clauses that use CAST and DATETIME. Both seem to return the same results. Is either one better?

CAST(FLOOR(CAST(cc.Date AS float)) AS datetime) = CAST(FLOOR(CAST((getdate()-1) AS float)) AS datetime)

-- OR --

cast (round(cast(cc.Date as float),0,1) as datetime) = cast (round(cast((getdate()-1) as float),0,1) as datetime)

BTW: Oracle handles this easily as trunc(mydate)

Thanks in advance

Oh Using SQLServer 2005

Doug

www.cooltimbers.com

It looks to me like they will both work:

declare @.morningDt datetime set @.morningDt = '3/13/7 0:05'
declare @.afternoonDt datetime set @.afternoonDt = '3/13/7 23:59'

select cast(round(cast(@.morningDt as float),0,1) as datetime),
cast(round(cast(@.afternoonDt as float),0,1) as datetime),
round(cast(@.morningDt as float),0,1) ,
round(cast(@.afternoonDt as float),0,1)

-- -- - -
2007-03-13 00:00:00.000 2007-03-13 00:00:00.000 39152.0 39152.0

I am more used to seeing the FLOOR version.

|||

Thanks Kent for the prompt reply... and for your help in general!

I sure do wish Microsoft implements something similar to Oracles "TRUNC" function that nicely truncates to a day.

any order

Hi,
Is there any sort order if we don't use order by? for example,
select * from tablename
what is sort order? each time, I get the same records with the same order,
any mystery?As far as the I know, the order was the same as you add the data.
You can change the order bu add "order by" statement , I think you can get
whole information from BOL|||Wei
I don't think that order depends on insertion. (unless you have clustered
index on this column)
"Wei Ci Zhou" <weicizhou@.hotmail.com.discuss> wrote in message
news:O9uMAcW6DHA.2380@.TK2MSFTNGP10.phx.gbl...

quote:

---

> As far as the I know, the order was the same as you add the data.
> You can change the order bu add "order by" statement , I think you can get
> whole information from BOL
>

---

|||Hi ,
If you have a clusted index in that table database will be ordered based on
the Index key. So incase if you have a clustered index the
"select * from tablename" will return the result set based on the clustered
index key order.
If you donot have a clusted index the result set will be reurned based on
the way you inserted.
Thanks
Hari
MCDBA
"John" <spam@.spam.com> wrote in message
news:OkuS1PW6DHA.1636@.TK2MSFTNGP12.phx.gbl...

quote:

---

> Hi,
> Is there any sort order if we don't use order by? for example,
> select * from tablename
> what is sort order? each time, I get the same records with the same order,
> any mystery?
>

---

|||records are sorted in ascending order by default.
"John" <spam@.spam.com> wrote in message
news:OkuS1PW6DHA.1636@.TK2MSFTNGP12.phx.gbl...

quote:

---

> Hi,
> Is there any sort order if we don't use order by? for example,
> select * from tablename
> what is sort order? each time, I get the same records with the same order,
> any mystery?
>

---

|||Sorry

quote:

---

> I don't think that order depends on insertion. (unless you have clustered
> index on this column)

---

I meant if you don't have a clustered index on the column it depends on
insertion.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#JxgijW6DHA.3548@.TK2MSFTNGP11.phx.gbl...

quote:

---

> Wei
> I don't think that order depends on insertion. (unless you have clustered
> index on this column)
>
> "Wei Ci Zhou" <weicizhou@.hotmail.com.discuss> wrote in message
> news:O9uMAcW6DHA.2380@.TK2MSFTNGP10.phx.gbl...
get[QUOTE]
>

---

|||The first reply you made me shocked |||> If you have a clusted index in that table database will be ordered based on

quote:

---

> the Index key.

---

Not necessarily. See my other post.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uPL7gkW6DHA.2628@.TK2MSFTNGP10.phx.gbl...

quote:

---

> Hi ,
> If you have a clusted index in that table database will be ordered based o
n
> the Index key. So incase if you have a clustered index the
> "select * from tablename" will return the result set based on the clustere
d
> index key order.
> If you donot have a clusted index the result set will be reurned based on
> the way you inserted.
>
> Thanks
> Hari
> MCDBA
> "John" <spam@.spam.com> wrote in message
> news:OkuS1PW6DHA.1636@.TK2MSFTNGP12.phx.gbl...
>

---

|||> I meant if you don't have a clustered index on the column it depends on

quote:

---

> insertion.

---

Not correct. I'm afraid. See my other post.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:%23qCR2tW6DHA.1936@.TK2MSFTNGP12.phx.gbl...[Q
UOTE]
> Sorry
> I meant if you don't have a clustered index on the column it depends on
> insertion.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:#JxgijW6DHA.3548@.TK2MSFTNGP11.phx.gbl...
> get
>[/QUOTE]|||A table, is in the relation model, an unordered set of rows. Imagine your ro
ws being notes on
paper-slips and you throw them into a bag. The back is shaken from time to t
ime. Now. try to pull
the paper-slips out of this bag "in order". The term "order" doesn't make se
nse in the relational
model.
To be more specific, if you don't have order by, the optimizer is free to re
turn the rows in any
order. It will pick the order it finds most cost-effective.
It doesn't have to be in the same sequence as the rows are inserted (read ab
out IAM page etc in the
physical database chapter in Books Online). That is regardless whether you h
ave a clustered index or
not.
It doesn't have to be in the same sequence as the clustered index. The index
can be heavy
fragmented, so the optimizer can decide to instead of jumping back and forth
on the disk, it will
scan the file in physical order, according to the IAM page.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"John" <spam@.spam.com> wrote in message news:OkuS1PW6DHA.1636@.TK2MSFTNGP12.phx.gbl...

quote:

---

[color
=darkred]
> Hi,
> Is there any sort order if we don't use order by? for example,
> select * from tablename
> what is sort order? each time, I get the same records with the same order,
> any mystery?
>[/color]

---

any order

Hi,
Is there any sort order if we don't use order by? for example,
select * from tablename
what is sort order? each time, I get the same records with the same order,
any mystery?As far as the I know, the order was the same as you add the data.
You can change the order bu add "order by" statement , I think you can get
whole information from BOL|||Wei
I don't think that order depends on insertion. (unless you have clustered
index on this column)
"Wei Ci Zhou" <weicizhou@.hotmail.com.discuss> wrote in message
news:O9uMAcW6DHA.2380@.TK2MSFTNGP10.phx.gbl...
> As far as the I know, the order was the same as you add the data.
> You can change the order bu add "order by" statement , I think you can get
> whole information from BOL
>|||Hi ,
If you have a clusted index in that table database will be ordered based on
the Index key. So incase if you have a clustered index the
"select * from tablename" will return the result set based on the clustered
index key order.
If you donot have a clusted index the result set will be reurned based on
the way you inserted.
Thanks
Hari
MCDBA
"John" <spam@.spam.com> wrote in message
news:OkuS1PW6DHA.1636@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Is there any sort order if we don't use order by? for example,
> select * from tablename
> what is sort order? each time, I get the same records with the same order,
> any mystery?
>|||records are sorted in ascending order by default.
"John" <spam@.spam.com> wrote in message
news:OkuS1PW6DHA.1636@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Is there any sort order if we don't use order by? for example,
> select * from tablename
> what is sort order? each time, I get the same records with the same order,
> any mystery?
>|||Sorry
> I don't think that order depends on insertion. (unless you have clustered
> index on this column)
I meant if you don't have a clustered index on the column it depends on
insertion.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#JxgijW6DHA.3548@.TK2MSFTNGP11.phx.gbl...
> Wei
> I don't think that order depends on insertion. (unless you have clustered
> index on this column)
>
> "Wei Ci Zhou" <weicizhou@.hotmail.com.discuss> wrote in message
> news:O9uMAcW6DHA.2380@.TK2MSFTNGP10.phx.gbl...
> > As far as the I know, the order was the same as you add the data.
> > You can change the order bu add "order by" statement , I think you can
get
> > whole information from BOL
> >
> >
>|||The first reply you made me shocked :)|||Incorrect, I'm afraid. See my other post.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"harsh" <harshalmistry@.hotmail.com> wrote in message
news:%23RaX%23pW6DHA.2568@.TK2MSFTNGP10.phx.gbl...
> records are sorted in ascending order by default.
>
> "John" <spam@.spam.com> wrote in message
> news:OkuS1PW6DHA.1636@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> > Is there any sort order if we don't use order by? for example,
> >
> > select * from tablename
> >
> > what is sort order? each time, I get the same records with the same order,
> > any mystery?
> >
> >
>|||> If you have a clusted index in that table database will be ordered based on
> the Index key.
Not necessarily. See my other post.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uPL7gkW6DHA.2628@.TK2MSFTNGP10.phx.gbl...
> Hi ,
> If you have a clusted index in that table database will be ordered based on
> the Index key. So incase if you have a clustered index the
> "select * from tablename" will return the result set based on the clustered
> index key order.
> If you donot have a clusted index the result set will be reurned based on
> the way you inserted.
>
> Thanks
> Hari
> MCDBA
> "John" <spam@.spam.com> wrote in message
> news:OkuS1PW6DHA.1636@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> > Is there any sort order if we don't use order by? for example,
> >
> > select * from tablename
> >
> > what is sort order? each time, I get the same records with the same order,
> > any mystery?
> >
> >
>|||> I meant if you don't have a clustered index on the column it depends on
> insertion.
Not correct. I'm afraid. See my other post.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:%23qCR2tW6DHA.1936@.TK2MSFTNGP12.phx.gbl...
> Sorry
> > I don't think that order depends on insertion. (unless you have clustered
> > index on this column)
> I meant if you don't have a clustered index on the column it depends on
> insertion.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:#JxgijW6DHA.3548@.TK2MSFTNGP11.phx.gbl...
> > Wei
> > I don't think that order depends on insertion. (unless you have clustered
> > index on this column)
> >
> >
> >
> > "Wei Ci Zhou" <weicizhou@.hotmail.com.discuss> wrote in message
> > news:O9uMAcW6DHA.2380@.TK2MSFTNGP10.phx.gbl...
> > > As far as the I know, the order was the same as you add the data.
> > > You can change the order bu add "order by" statement , I think you can
> get
> > > whole information from BOL
> > >
> > >
> >
> >
>|||A table, is in the relation model, an unordered set of rows. Imagine your rows being notes on
paper-slips and you throw them into a bag. The back is shaken from time to time. Now. try to pull
the paper-slips out of this bag "in order". The term "order" doesn't make sense in the relational
model.
To be more specific, if you don't have order by, the optimizer is free to return the rows in any
order. It will pick the order it finds most cost-effective.
It doesn't have to be in the same sequence as the rows are inserted (read about IAM page etc in the
physical database chapter in Books Online). That is regardless whether you have a clustered index or
not.
It doesn't have to be in the same sequence as the clustered index. The index can be heavy
fragmented, so the optimizer can decide to instead of jumping back and forth on the disk, it will
scan the file in physical order, according to the IAM page.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"John" <spam@.spam.com> wrote in message news:OkuS1PW6DHA.1636@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Is there any sort order if we don't use order by? for example,
> select * from tablename
> what is sort order? each time, I get the same records with the same order,
> any mystery?
>|||Tibor
I have just finished to read the article, you are absolutely right. Sorry
for giniven incorrect information.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OS1xRJX6DHA.2712@.tk2msftngp13.phx.gbl...
> A table, is in the relation model, an unordered set of rows. Imagine your
rows being notes on
> paper-slips and you throw them into a bag. The back is shaken from time to
time. Now. try to pull
> the paper-slips out of this bag "in order". The term "order" doesn't make
sense in the relational
> model.
> To be more specific, if you don't have order by, the optimizer is free to
return the rows in any
> order. It will pick the order it finds most cost-effective.
> It doesn't have to be in the same sequence as the rows are inserted (read
about IAM page etc in the
> physical database chapter in Books Online). That is regardless whether you
have a clustered index or
> not.
> It doesn't have to be in the same sequence as the clustered index. The
index can be heavy
> fragmented, so the optimizer can decide to instead of jumping back and
forth on the disk, it will
> scan the file in physical order, according to the IAM page.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "John" <spam@.spam.com> wrote in message
news:OkuS1PW6DHA.1636@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> > Is there any sort order if we don't use order by? for example,
> >
> > select * from tablename
> >
> > what is sort order? each time, I get the same records with the same
order,
> > any mystery?
> >
> >
>|||Tibor.
Greate Poster, let me learn many thing.
I do not think you are right after you poster, but after read the
information on BOL. Yes you are right.|||>The term "order" doesn't make sense in the relational
> model.
I understand that the order and PHYSICAL placement of the data is not a
concern in the relational model but surely the RM addresses returning
ordered data? Isn't this called sort sets in the RM?
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OS1xRJX6DHA.2712@.tk2msftngp13.phx.gbl...
> A table, is in the relation model, an unordered set of rows. Imagine your
rows being notes on
> paper-slips and you throw them into a bag. The back is shaken from time to
time. Now. try to pull
> the paper-slips out of this bag "in order". The term "order" doesn't make
sense in the relational
> model.
> To be more specific, if you don't have order by, the optimizer is free to
return the rows in any
> order. It will pick the order it finds most cost-effective.
> It doesn't have to be in the same sequence as the rows are inserted (read
about IAM page etc in the
> physical database chapter in Books Online). That is regardless whether you
have a clustered index or
> not.
> It doesn't have to be in the same sequence as the clustered index. The
index can be heavy
> fragmented, so the optimizer can decide to instead of jumping back and
forth on the disk, it will
> scan the file in physical order, according to the IAM page.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "John" <spam@.spam.com> wrote in message
news:OkuS1PW6DHA.1636@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> > Is there any sort order if we don't use order by? for example,
> >
> > select * from tablename
> >
> > what is sort order? each time, I get the same records with the same
order,
> > any mystery?
> >
> >
>|||As far as I know, RM does not have this concept, as this would mean that RM defines other concepts
than relations. I might be wrong, of course and I welcome (as always :-) ) pointers etc...
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
news:eNG0m9X6DHA.2380@.TK2MSFTNGP10.phx.gbl...
> >The term "order" doesn't make sense in the relational
> > model.
> I understand that the order and PHYSICAL placement of the data is not a
> concern in the relational model but surely the RM addresses returning
> ordered data? Isn't this called sort sets in the RM?
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:OS1xRJX6DHA.2712@.tk2msftngp13.phx.gbl...
> > A table, is in the relation model, an unordered set of rows. Imagine your
> rows being notes on
> > paper-slips and you throw them into a bag. The back is shaken from time to
> time. Now. try to pull
> > the paper-slips out of this bag "in order". The term "order" doesn't make
> sense in the relational
> > model.
> >
> > To be more specific, if you don't have order by, the optimizer is free to
> return the rows in any
> > order. It will pick the order it finds most cost-effective.
> >
> > It doesn't have to be in the same sequence as the rows are inserted (read
> about IAM page etc in the
> > physical database chapter in Books Online). That is regardless whether you
> have a clustered index or
> > not.
> >
> > It doesn't have to be in the same sequence as the clustered index. The
> index can be heavy
> > fragmented, so the optimizer can decide to instead of jumping back and
> forth on the disk, it will
> > scan the file in physical order, according to the IAM page.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "John" <spam@.spam.com> wrote in message
> news:OkuS1PW6DHA.1636@.TK2MSFTNGP12.phx.gbl...
> > > Hi,
> > >
> > > Is there any sort order if we don't use order by? for example,
> > >
> > > select * from tablename
> > >
> > > what is sort order? each time, I get the same records with the same
> order,
> > > any mystery?
> > >
> > >
> >
> >
>

Any ideas on how to combine records into one

Here is what I have,

select id, name from rss_user

gives me this

r604738 one
r604738 two
r604738 three
r604739 one
r604739 two
r604739 three
r604739 four

I would like to be able to pipe this into a @.temp table so it looks like this,

r604738 one,two,three
r604739 one,two,three,four

Any ideas, so far I am drawing a blank.Sure, the search feature is your friend! Check out this thread (http://www.dbforums.com/t989683.html) from a couple of days ago.

This is MUCH better if done on the client, but it can also be done on the server too.

-PatP

Any code for automatically inserting queries of first table into the second?

Any code for automatically inserting queries of first table into the second?
Is there a way of inserting the query of records from the first table into the second? Because I want to use the second db by deleting records that might be unnecessary.

I want to copy the searches of one table to another. Let's say I search a keyword I want the search to go through the first table and copy the records where the keyword is to be found to the second table. I find that would be easy to study. Whatever I don't want from that search I can easily delete it. And at the same time I have the original table not tampered.could u just

INSERT INTO Table2 (field1)
SELECT * FROM Table1;

i believe u can just use the INSERT cmd|||Is it before or after (I'd guess it's before) the <%rs.Open SQL%> statement?|||What does this mean?

INSERT INTO results(id, book, book_spoke, recordType, book_title, chap, chapter, chapter_spoke, vers, verse, verse_spoke, text_data) VALUES (, , , '', '', , , , , , , '')
Microsoft VBScript runtime error '800a01b6'

Object doesn't support this property or method: 'execute'

/kjvresp.asp, line 280|||the problem is here: VALUES (, , , '', '', , , , , , , '')

you have to put something for each column

at worst, you could use nulls (assuming the table allows nulls)

VALUES (null, null, null, '', '', null, null, null, null, null, null, '')|||I had put && around each fieldname. But I made some changes. But this is what I got now:

INSERT INTO results SELECT * FROM bible WHERE text_data LIKE '%ezra%' AND text_data LIKE '%%' AND text_data LIKE '%%'
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

/kjvresp.asp, line 272

The insertion was found in the table but the output to the ASP page is somehow blocked.|||text_data LIKE '%%' is true for every row and should be dropped

if you have an ASP problem, you should post in the ASP forum

this forum is for generic (non-proprietary) SQL