Parsing web URL's in SQL

Hello everyone,
I was wondering if anyone could help me out. Basically I have several
sharepoint sites for applications that I run. Each applications
sharepoint site needs to display information about the application
that is located in a SQL database. So for example:
www.qwerty.com/application1/home.html
www.qwerty.com/application2/home.html
www.qwerty.com/application3/home.html
I need to find a way that SQL, Sharepoint, some code, would take the
web address, parse, and truncate it to only read "Application1."
Then I could write a SQL statement to get the data from Application1's
database to display on the sharepoint site. However I have several
application pages, and I would like to be able to have some type of
code that would automatically do it for all the applications, instead
of manually coding/quiering for the data for each application
page.....it would just take too long.
if anyone has any ideas, feel free to share. Thank you in advance, for
all your time and help it is greatly appreciated!
--A4orce84
If the part you want is always the next to last section, then this
would appear to provide what is needed. A bit convoluted though!
CREATE TABLE Demo (url varchar(100) not null)
INSERT Demo values ('www.qwerty.com/application1/home.html')
INSERT Demo values ('www.qwerty.com/application2/home.html')
INSERT Demo values ('www.qwerty.com/application3/home.html')
INSERT Demo values ('www.qwerty.com/whatever/banana/987.htm')
SELECT REVERSE(
SUBSTRING(REVERSE(url),
charindex('/', REVERSE(url)) +1,
charindex('/', REVERSE(url), charindex('/',
REVERSE(url)) + 1) -
charindex('/', REVERSE(url)) - 1))
FROM Demo
application1
application2
application3
banana
Roy Harvey
Beacon Falls, CT
On Fri, 08 Jun 2007 09:01:28 -0700, Asif_Ahmad@.dell.com wrote:

>Hello everyone,
>I was wondering if anyone could help me out. Basically I have several
>sharepoint sites for applications that I run. Each applications
>sharepoint site needs to display information about the application
>that is located in a SQL database. So for example:
>www.qwerty.com/application1/home.html
>www.qwerty.com/application2/home.html
>www.qwerty.com/application3/home.html
>I need to find a way that SQL, Sharepoint, some code, would take the
>web address, parse, and truncate it to only read "Application1."
>Then I could write a SQL statement to get the data from Application1's
>database to display on the sharepoint site. However I have several
>application pages, and I would like to be able to have some type of
>code that would automatically do it for all the applications, instead
>of manually coding/quiering for the data for each application
>page.....it would just take too long.
>if anyone has any ideas, feel free to share. Thank you in advance, for
>all your time and help it is greatly appreciated!
>--A4orce84
|||Ron,
Is this if you only know the number of applications you have? I think
there are several hundred I am using particularly. It also appears
from your code that you have to enter the physical address into the
code by your:
INSERT Demo values ('www.qwerty.com/application1/home.html')
INSERT Demo values ('www.qwerty.com/application2/home.html')
INSERT Demo values ('www.qwerty.com/application3/home.html')
INSERT Demo values ('www.qwerty.com/whatever/banana/987.htm')
I wanted a way that it would do it automatically, that way if servers
change and things get moved around, or they add more applications it
would be easy to keep maintaining the SQL. Or I may have completely
mis-interpreted your code, either way thank you for your help. Any
additional information you could provide would be helpful!
|||I assumed that the URLs you needed to parse would already be in a SQL
Server database table. (Why else would you be trying to parse a URL
in SQL Server?) So my CREATE TABLE and INSERT statements were simply
there to create some test data for demonstration purposes. The number
of URLs in your table is irrelevant to the parsing process.
What I intended to demonstrate was a way to pick out the next to last
section of the URL, which was what I thought you were aiming for. I
believe the code provided does that.
Good luck!
Roy Harvey
Beacon Falls, CT
On Sun, 10 Jun 2007 15:04:14 -0700, Asif_Ahmad@.dell.com wrote:

>Ron,
>Is this if you only know the number of applications you have? I think
>there are several hundred I am using particularly. It also appears
>from your code that you have to enter the physical address into the
>code by your:
>
>INSERT Demo values ('www.qwerty.com/application1/home.html')
>INSERT Demo values ('www.qwerty.com/application2/home.html')
>INSERT Demo values ('www.qwerty.com/application3/home.html')
>INSERT Demo values ('www.qwerty.com/whatever/banana/987.htm')
>
>I wanted a way that it would do it automatically, that way if servers
>change and things get moved around, or they add more applications it
>would be easy to keep maintaining the SQL. Or I may have completely
>mis-interpreted your code, either way thank you for your help. Any
>additional information you could provide would be helpful!

Parsing web URL's in SQL

Hello everyone,
I was wondering if anyone could help me out. Basically I have several
sharepoint sites for applications that I run. Each applications
sharepoint site needs to display information about the application
that is located in a SQL database. So for example:
www.qwerty.com/application1/home.html
www.qwerty.com/application2/home.html
www.qwerty.com/application3/home.html
I need to find a way that SQL, Sharepoint, some code, would take the
web address, parse, and truncate it to only read "Application1."
Then I could write a SQL statement to get the data from Application1's
database to display on the sharepoint site. However I have several
application pages, and I would like to be able to have some type of
code that would automatically do it for all the applications, instead
of manually coding/quiering for the data for each application
page.....it would just take too long.
if anyone has any ideas, feel free to share. Thank you in advance, for
all your time and help it is greatly appreciated!
--A4orce84If the part you want is always the next to last section, then this
would appear to provide what is needed. A bit convoluted though!
CREATE TABLE Demo (url varchar(100) not null)
INSERT Demo values ('www.qwerty.com/application1/home.html')
INSERT Demo values ('www.qwerty.com/application2/home.html')
INSERT Demo values ('www.qwerty.com/application3/home.html')
INSERT Demo values ('www.qwerty.com/whatever/banana/987.htm')
SELECT REVERSE(
SUBSTRING(REVERSE(url),
charindex('/', REVERSE(url)) +1,
charindex('/', REVERSE(url), charindex('/',
REVERSE(url)) + 1) -
charindex('/', REVERSE(url)) - 1))
FROM Demo
--
application1
application2
application3
banana
Roy Harvey
Beacon Falls, CT
On Fri, 08 Jun 2007 09:01:28 -0700, Asif_Ahmad@.dell.com wrote:
>Hello everyone,
>I was wondering if anyone could help me out. Basically I have several
>sharepoint sites for applications that I run. Each applications
>sharepoint site needs to display information about the application
>that is located in a SQL database. So for example:
>www.qwerty.com/application1/home.html
>www.qwerty.com/application2/home.html
>www.qwerty.com/application3/home.html
>I need to find a way that SQL, Sharepoint, some code, would take the
>web address, parse, and truncate it to only read "Application1."
>Then I could write a SQL statement to get the data from Application1's
>database to display on the sharepoint site. However I have several
>application pages, and I would like to be able to have some type of
>code that would automatically do it for all the applications, instead
>of manually coding/quiering for the data for each application
>page.....it would just take too long.
>if anyone has any ideas, feel free to share. Thank you in advance, for
>all your time and help it is greatly appreciated!
>--A4orce84|||Ron,
Is this if you only know the number of applications you have? I think
there are several hundred I am using particularly. It also appears
from your code that you have to enter the physical address into the
code by your:
INSERT Demo values ('www.qwerty.com/application1/home.html')
INSERT Demo values ('www.qwerty.com/application2/home.html')
INSERT Demo values ('www.qwerty.com/application3/home.html')
INSERT Demo values ('www.qwerty.com/whatever/banana/987.htm')
I wanted a way that it would do it automatically, that way if servers
change and things get moved around, or they add more applications it
would be easy to keep maintaining the SQL. Or I may have completely
mis-interpreted your code, either way thank you for your help. Any
additional information you could provide would be helpful!|||I assumed that the URLs you needed to parse would already be in a SQL
Server database table. (Why else would you be trying to parse a URL
in SQL Server?) So my CREATE TABLE and INSERT statements were simply
there to create some test data for demonstration purposes. The number
of URLs in your table is irrelevant to the parsing process.
What I intended to demonstrate was a way to pick out the next to last
section of the URL, which was what I thought you were aiming for. I
believe the code provided does that.
Good luck!
Roy Harvey
Beacon Falls, CT
On Sun, 10 Jun 2007 15:04:14 -0700, Asif_Ahmad@.dell.com wrote:
>Ron,
>Is this if you only know the number of applications you have? I think
>there are several hundred I am using particularly. It also appears
>from your code that you have to enter the physical address into the
>code by your:
>
>INSERT Demo values ('www.qwerty.com/application1/home.html')
>INSERT Demo values ('www.qwerty.com/application2/home.html')
>INSERT Demo values ('www.qwerty.com/application3/home.html')
>INSERT Demo values ('www.qwerty.com/whatever/banana/987.htm')
>
>I wanted a way that it would do it automatically, that way if servers
>change and things get moved around, or they add more applications it
>would be easy to keep maintaining the SQL. Or I may have completely
>mis-interpreted your code, either way thank you for your help. Any
>additional information you could provide would be helpful!

Parsing web URL's in SQL

Hello everyone,
I was wondering if anyone could help me out. Basically I have several
sharepoint sites for applications that I run. Each applications
sharepoint site needs to display information about the application
that is located in a SQL database. So for example:
www.qwerty.com/application1/home.html
www.qwerty.com/application2/home.html
www.qwerty.com/application3/home.html
I need to find a way that SQL, Sharepoint, some code, would take the
web address, parse, and truncate it to only read "Application1."
Then I could write a SQL statement to get the data from Application1's
database to display on the sharepoint site. However I have several
application pages, and I would like to be able to have some type of
code that would automatically do it for all the applications, instead
of manually coding/quiering for the data for each application
page.....it would just take too long.
if anyone has any ideas, feel free to share. Thank you in advance, for
all your time and help it is greatly appreciated!
--A4orce84If the part you want is always the next to last section, then this
would appear to provide what is needed. A bit convoluted though!
CREATE TABLE Demo (url varchar(100) not null)
INSERT Demo values ('www.qwerty.com/application1/home.html')
INSERT Demo values ('www.qwerty.com/application2/home.html')
INSERT Demo values ('www.qwerty.com/application3/home.html')
INSERT Demo values ('www.qwerty.com/whatever/banana/987.htm')
SELECT REVERSE(
SUBSTRING(REVERSE(url),
charindex('/', REVERSE(url)) +1,
charindex('/', REVERSE(url), charindex('/',
REVERSE(url)) + 1) -
charindex('/', REVERSE(url)) - 1))
FROM Demo
application1
application2
application3
banana
Roy Harvey
Beacon Falls, CT
On Fri, 08 Jun 2007 09:01:28 -0700, Asif_Ahmad@.dell.com wrote:

>Hello everyone,
>I was wondering if anyone could help me out. Basically I have several
>sharepoint sites for applications that I run. Each applications
>sharepoint site needs to display information about the application
>that is located in a SQL database. So for example:
>www.qwerty.com/application1/home.html
>www.qwerty.com/application2/home.html
>www.qwerty.com/application3/home.html
>I need to find a way that SQL, Sharepoint, some code, would take the
>web address, parse, and truncate it to only read "Application1."
>Then I could write a SQL statement to get the data from Application1's
>database to display on the sharepoint site. However I have several
>application pages, and I would like to be able to have some type of
>code that would automatically do it for all the applications, instead
>of manually coding/quiering for the data for each application
>page.....it would just take too long.
>if anyone has any ideas, feel free to share. Thank you in advance, for
>all your time and help it is greatly appreciated!
>--A4orce84|||Ron,
Is this if you only know the number of applications you have? I think
there are several hundred I am using particularly. It also appears
from your code that you have to enter the physical address into the
code by your:
INSERT Demo values ('www.qwerty.com/application1/home.html')
INSERT Demo values ('www.qwerty.com/application2/home.html')
INSERT Demo values ('www.qwerty.com/application3/home.html')
INSERT Demo values ('www.qwerty.com/whatever/banana/987.htm')
I wanted a way that it would do it automatically, that way if servers
change and things get moved around, or they add more applications it
would be easy to keep maintaining the SQL. Or I may have completely
mis-interpreted your code, either way thank you for your help. Any
additional information you could provide would be helpful!|||I assumed that the URLs you needed to parse would already be in a SQL
Server database table. (Why else would you be trying to parse a URL
in SQL Server?) So my CREATE TABLE and INSERT statements were simply
there to create some test data for demonstration purposes. The number
of URLs in your table is irrelevant to the parsing process.
What I intended to demonstrate was a way to pick out the next to last
section of the URL, which was what I thought you were aiming for. I
believe the code provided does that.
Good luck!
Roy Harvey
Beacon Falls, CT
On Sun, 10 Jun 2007 15:04:14 -0700, Asif_Ahmad@.dell.com wrote:

>Ron,
>Is this if you only know the number of applications you have? I think
>there are several hundred I am using particularly. It also appears
>from your code that you have to enter the physical address into the
>code by your:
>
>INSERT Demo values ('www.qwerty.com/application1/home.html')
>INSERT Demo values ('www.qwerty.com/application2/home.html')
>INSERT Demo values ('www.qwerty.com/application3/home.html')
>INSERT Demo values ('www.qwerty.com/whatever/banana/987.htm')
>
>I wanted a way that it would do it automatically, that way if servers
>change and things get moved around, or they add more applications it
>would be easy to keep maintaining the SQL. Or I may have completely
>mis-interpreted your code, either way thank you for your help. Any
>additional information you could provide would be helpful!

parse incoming mail content and attacthments into tables

I'm fairly new at this, so I'm not sure if this is a simple task or
not. I've only been able to find desktop applications on doing this
and that's really not the way I want to go.
What I'm trying to do is this:
We have a program that sends email in a templated format (always
containing the same info on the same lines) with a few attached files
as well. We want the server to see them come in, parse the info out of
the body of the email and insert into a few different tables. We also
want to be able to save the images into another table.
The first question I have is whether we write something that sits on
the Exchange server or on the SQL server. If on the SQL server, I
guess I'll have to figure out how to issolate one email address to
point to the SQL server.
Any ideas on where to begin?Hi
With SQL mail you can attach to the mailbox and process messages from it.
You may want to look at looping through xp_findnextmsg and calling
xp_readmail or use sp_processmail see Books Online for more information
regarding these procedures. Also check out:
http://msdn.microsoft.com/library/d...erverE-mail.asp
John
"roger@.springloose.net" wrote:

> I'm fairly new at this, so I'm not sure if this is a simple task or
> not. I've only been able to find desktop applications on doing this
> and that's really not the way I want to go.
> What I'm trying to do is this:
> We have a program that sends email in a templated format (always
> containing the same info on the same lines) with a few attached files
> as well. We want the server to see them come in, parse the info out of
> the body of the email and insert into a few different tables. We also
> want to be able to save the images into another table.
> The first question I have is whether we write something that sits on
> the Exchange server or on the SQL server. If on the SQL server, I
> guess I'll have to figure out how to issolate one email address to
> point to the SQL server.
> Any ideas on where to begin?
>

Parameters...Null as an available value?!?!?

I have a hierarchy of organizations that I need to be able to filter by
in the reports...
For example, an Agency can have many Applications associated with it.
I have three stored procedures, one that gets a list of Agencies, the
other that gets a list of Applications based on the Agency that was
selected, and the third takes a bunch of other parameters and gets all
the matching orders (or whatever) associated with the Agency and
Application (Agency and Application are both input params to the third
stored proc).
What I really want is for the user to be able to not select an Agency,
basically setting it to Null, and letting the stored procedure that
does the query for the report ignore that parameter and get results for
all agencies.
If I set the parameter in the report to null, then I can't have a drop
down list with the Agency names if the user doesn't want to set the
Agency to null. But I can't have both!!!
What is the best procedure for doing this? Returning a -1 record in
the list of Agencies and using that to indicate null within the stored
proc? Any other ideas? I can't return a null record in the stored
proc because the report throws an exception. Does this make sense?
Any help would be appreciated!!! Thanks, BrianI had a similiar problem, I solved it by using 'All' as below:
Select * from MainTable
Where (Agency_Name = @.AgencyName OR @.AgencyName = 'All') AND .....
@.AgencyName is a parameter, you can set the parameter's default value
to All( you can make one dataset for this parameter as below:
Select ID, Name from AgencyTable UNION Select 0,'All'. Then In the
Report Parameters dialogue,Choose available value from query,make All
as default value), then the first part of where clause will always
automatically satisfied unless you choose a different value.
This works great for me, Hope this helps.
Good luck.
Henry
Brian wrote:
> I have a hierarchy of organizations that I need to be able to filter
by
> in the reports...
> For example, an Agency can have many Applications associated with it.
> I have three stored procedures, one that gets a list of Agencies, the
> other that gets a list of Applications based on the Agency that was
> selected, and the third takes a bunch of other parameters and gets
all
> the matching orders (or whatever) associated with the Agency and
> Application (Agency and Application are both input params to the
third
> stored proc).
> What I really want is for the user to be able to not select an
Agency,
> basically setting it to Null, and letting the stored procedure that
> does the query for the report ignore that parameter and get results
for
> all agencies.
> If I set the parameter in the report to null, then I can't have a
drop
> down list with the Agency names if the user doesn't want to set the
> Agency to null. But I can't have both!!!
> What is the best procedure for doing this? Returning a -1 record in
> the list of Agencies and using that to indicate null within the
stored
> proc? Any other ideas? I can't return a null record in the stored
> proc because the report throws an exception. Does this make sense?
> Any help would be appreciated!!! Thanks, Brian|||That's exactly what I ended up doing this morning. Works great so far!
Thanks!
Brian
fanh@.tycoelectronics.com wrote:
> I had a similiar problem, I solved it by using 'All' as below:
> Select * from MainTable
> Where (Agency_Name = @.AgencyName OR @.AgencyName = 'All') AND .....
> @.AgencyName is a parameter, you can set the parameter's default value
> to All( you can make one dataset for this parameter as below:
> Select ID, Name from AgencyTable UNION Select 0,'All'. Then In the
> Report Parameters dialogue,Choose available value from query,make All
> as default value), then the first part of where clause will always
> automatically satisfied unless you choose a different value.
> This works great for me, Hope this helps.
> Good luck.
> Henry
>
> Brian wrote:
> > I have a hierarchy of organizations that I need to be able to
filter
> by
> > in the reports...
> >
> > For example, an Agency can have many Applications associated with
it.
> >
> > I have three stored procedures, one that gets a list of Agencies,
the
> > other that gets a list of Applications based on the Agency that was
> > selected, and the third takes a bunch of other parameters and gets
> all
> > the matching orders (or whatever) associated with the Agency and
> > Application (Agency and Application are both input params to the
> third
> > stored proc).
> >
> > What I really want is for the user to be able to not select an
> Agency,
> > basically setting it to Null, and letting the stored procedure that
> > does the query for the report ignore that parameter and get results
> for
> > all agencies.
> >
> > If I set the parameter in the report to null, then I can't have a
> drop
> > down list with the Agency names if the user doesn't want to set the
> > Agency to null. But I can't have both!!!
> >
> > What is the best procedure for doing this? Returning a -1 record
in
> > the list of Agencies and using that to indicate null within the
> stored
> > proc? Any other ideas? I can't return a null record in the stored
> > proc because the report throws an exception. Does this make sense?
> > Any help would be appreciated!!! Thanks, Brian