Multi table designations in sql string

I'm putting together a resultset in an sql string
(it will be used in asp.net) where I'm drawing from a few different
tables, and I do have a relation with the tblDeal and tblSalesrep ID :
''
strSQLQuery = "SELECT d.salesrep_id,s.fname,s.lname,
s.boardtotal_note,s.BoardTotal_BonusPerc, s.ID,s.Hire_date,s.Term_date,
d.Orderdate,d.salesOff_loc_ID,d.SplitGross,
d.SplitRep_ID,d.Sale_Type_ID,d.DBA_ID,b.Amount_Revenue,b.Amount_Attempted
FROM tblBankTrans as b,tblDeal as d INNER JOIN tblSalesrep as s ON s.ID
= d.salesrep_id WHERE d.Orderdate = '" &
request.querystring("boarddate") & "';"
'''
I am not getting an error, but no response when running this sql string
(i did response.write the "boarddate" variable successfully, so thats
not the problem)
'
.NetSports> strSQLQuery = "SELECT d.salesrep_id,s.fname,s.lname,
> s.boardtotal_note,s.BoardTotal_BonusPerc, s.ID,s.Hire_date,s.Term_date,
> d.Orderdate,d.salesOff_loc_ID,d.SplitGross,
> d.SplitRep_ID,d.Sale_Type_ID,d.DBA_ID,b.Amount_Revenue,b.Amount_Attempted
> FROM tblBankTrans as b,tblDeal as d INNER JOIN tblSalesrep as s ON s.ID
> = d.salesrep_id WHERE d.Orderdate = '" &
> request.querystring("boarddate") & "';"
If I read your post correctly, you are asking SQL Server to figure out your
boarddate as a parameter to your query.
If I am correct in your interpretation, perhaps your SQL query [VSS script]
(which should be a stored procedure) should look like this:
<sql>
IF EXISTS (
SELECT *
FROM SysObjects
WHERE Type = 'P'
AND Name = 'WhateverYouCallIt '
)
DROP PROCEDURE dbo.WhateverYouCallIt
GO
CREATE PROCEDURE dbo.WhateverYouCallIt (
@.boardDate INT
) AS
SELECT d.salesrep_id,s.fname,s.lname, s.boardtotal_note,
s.BoardTotal_BonusPerc, s.ID,s.Hire_date,s.Term_date,
d.Orderdate,d.salesOff_loc_ID,d.SplitGross, d.SplitRep_ID,
d.Sale_Type_ID,d.DBA_ID,b.Amount_Revenue,
b.Amount_Attempted
FROM tblBankTrans b,
tblDeal d
INNER JOIN tblSalesrep s
ON s.ID = d.salesrep_id
WHERE d.Orderdate = @.boardDate
GO
GRANT EXEC ON dbo.WhateverYouCallIt TO PUBLIC
GO
</sql>
Then you call it like this:
<vb.NET>
' Assumes that your connection string is in your Web.config or
App.config
' and that you have an Imports System.Configuration item in your code
class
Dim cnstr As String = ConnectionSettings.AppSetting("connectionString")
Dim cn As New SqlConnection(cnstr)
Dim da As New SqlDataAdapter("dbo.WhateverYouCallIt", cn)
Dim dt As New DataTable()
With da.SelectCommand
.CommandType = CommandType.StoredProcedure
' Assumes that you have a VB variable called boardDate that is the
' same as the stored procedure's (above) @.boardDate parameter:
.Parameters.Add("@.boardDate", boardDate)
End With
da.Fill(dt)
' etc...
</vb.NET>
Peace & happy computing,
Mike Labosh, MCSD
"(bb)|(^b){2}" -- William Shakespeare|||actually, I would probably like to see if the multi-table inner joins
are the correct way to make this sql string work, seeming that I am
also using the tblBankTrans table, but with no joins or relationships.

multi keyword search SP

Is it possible to write a Stored Procedure that takes a string of
search keywords as argument and returns the recordset? At the mo I am
passing the WHERE String as argument.

I got this technique from an Extreme Ultradev tutorial by Rick Curtis
it looked quite ok:
http://www.princeton.edu/~rcurtis/u...tutorial12.html

I have to admit, one of the main reason for passing the WHERE string is
that I do not know how to do the string splitting / parsing and putting
together in a Stored Procedure. I bet T-SQL would be just as powerful
as VBScript if I just knew it well enough.

What I liked about having built them on the web script was the
flexibility allowing to potentially build an advanced search without
having to change the stored procedure - but this is not crucial I could
always write several stored procedures or add parameters to the SP.

Here is what I have achieved in this way:
User can enter one ore more keywords separated by space.
Search algorithm returns results across a number of fields where ALL
search words are contained in any of these.
Search results will always be formatted a certain way and displayed in
a html table no matter how the search procedure / criteria is varied.

Here is the algorithm (that now works in ASP)
1. split search string into separate keywords
2. build where condition based on single keyword, concatenating all
searched fields (" AND f1+' '+f2+{' '+f<n>} LIKE %<keyword>%")
3. concatenate all these where conditions and pass to stored procedure.
4. stored procedure takes care of all other logic (e.g. Joins, which
fields are searched etc.). It uses a string variable @.SQL to build the
complete search string and then does
execute (@.SQL);
to create the recordset.

I bet there is a way to move 1. 2. and 3. into the SP (and I would feel
better if it was) but I don't have the expertise to do this. If anybody
wants to help me this is very welcome.

I can also post my original code to clarify, just want to avoid too
long posts.

Cheers
AxelThese links might give you some ideas (the first is similar to the
article you linked to, but from a pure TSQL perspective):

http://www.sommarskog.se/dyn-search.html
http://www.sommarskog.se/arrays-in-sql.html

TSQL string functions are not as powerful as those in other languages,
so if you have lots of string parsing, it would probably be best to do
it in the ASP code rather than in TSQL.

If your requirements become more complex, you could consider some sort
of reporting tool, rather than writing and maintaining code yourself.

Simon|||Thanks Simon

I had actually already read that article but it takes a different
approach - the SP takes a named list of parameters (similar to an
advanced search form) so it presumes the user knows where to look for
the keyword. My approch concatenates all candidate fields into one
large string and then looks for the search string within that long
string. It loops this process with multilpe kewoards and concatenates
using the AND condition.

If for example we assume for simplicities sake there is on table
tblProduct with the fields Name, Desc, Comment, Category, ID and I have
a record with "Iron Screw", "17 inch amalgated Screw - green", "useful
for outdoor use","consumables","1234-23"
this would be caught by passing "screw iron 17 outdoor" to the search
procedure. The user does not need to know in which fields the keywords
are contained but at the same time he will be able to narrow down the
search results very efficiently by adding more search words.
In my little loop I am comparing
'Iron Screw 17 inch amalgated Screw - green useful for outdoor use
consumables 1234-23' Like '%screw%'
AND
'Iron Screw 17 inch amalgated Screw - green useful for outdoor use
consumables 1234-23' Like '%iron%'
AND
'Iron...' Like '%17%'
AND
'Iron...' Like '%outdoor%'

what I need is like a split() and loop in T-SQL; also I would have to
eliminate invalid characters e.g. single quotes for the search string
to avoid any attempts at hacking.

I was told using dynamic SQL the way I have done here is wrong I only
want to get it right. Maybe there is another approach? MOre opinions
welcome...

thx,
Axel|||On 17 Jun 2005 03:56:57 -0700, Axel wrote:

>My approch concatenates all candidate fields into one
>large string and then looks for the search string within that long
>string.
(snip example)

Hi Axel,

The example clarifies what you want to do.

First, revisit this page: http://www.sommarskog.se/arrays-in-sql.html.
It will explain how you can break the collected search words ("screw
iron 17 outdoor" in your example) into a set of rows.

To find the Products that match at least one of the terms given, use

SELECT DISTINCT p.Product
FROM Products AS p
INNER JOIN #SearchWords AS s-- The search words in a table;
-- See link above for details.
ON p.Name + p.Desc + p.Comment + p.Category + p.ID
LIKE '%' + s.Word + '%'

And if you only want the products that match ALL the terms given:

SELECT p.Product
FROM Products AS p
INNER JOIN #SearchWords AS s-- The search words in a table;
-- See link above for details.
ON p.Name + p.Desc + p.Comment + p.Category + p.ID
LIKE '%' + s.Word + '%'
GROUP BY p.Product
HAVING COUNT(*) = (SELECT COUNT(*) FROM #SearchWords)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Axel (realraven2000@.hotmail.com) writes:
> I had actually already read that article but it takes a different
> approach - the SP takes a named list of parameters (similar to an
> advanced search form) so it presumes the user knows where to look for
> the keyword. My approch concatenates all candidate fields into one
> large string and then looks for the search string within that long
> string. It loops this process with multilpe kewoards and concatenates
> using the AND condition.

If I were you, I would look into full-text search. Not that I know
whether this is the answer to your problem, because I have never
used full-text myself. But I would look into it, before I started
to build a complex engine myself.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Multi Column FTS (2005) Query

Hi
I am trying to get my query syntax to work given a typical query I expect to
passed into my FTS procedure.
The typical search string could be Italy investment. The FTS must search
for Italy filtering by investment.
With an FTS index on CountryName and Item (item is where the word investment
resides)
Some t-sql like this would do the trick:
SELECT CountryName, ItemName
FROM SearchView
WHERE CONTAINS(*, 'italy AND investment')
Given that a single textbox is displayed to allow users to type their search
criteria, how would one seperate such a query so that country names were
searched against the CountryName FTS column and other (non country names)
searched against the FTS Item column?
Should I have several queries to determine if countries exist? If so, then
apply those country names in the AND of the WHERE clause, then how would I
then parse to remove the country names from the intial query string so that
the remaining words can be searched against the FTS Item column?
To add to the delimea, I'm using the FORMSOF(THESAURUS...) functionality, so
a user can query investment Europe (getting countries in the thesaurus file
matching Europe)
Craig
Unfortunately what you would have to do is the following
select *From SearchView where contains(CountryName, 'Italy') and
contains(ItenName,'investment')
This would offer the best performance, you could also do the following
select * from SearchView where CountryName='Italy' and
contains(ItenName,'investment')
unfortunately you will have to parse the search string to see which
countries are present in it, and then build your query accordingly.
Hilary

> To add to the delimea, I'm using the FORMSOF(THESAURUS...)
functionality, so
> a user can query investment Europe (getting countries in the
thesaurus file
> matching Europe)
> Craig
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

MSSQLServerOLAPService connection string name change

Hi all,
We had to rename the hostname of a server where SQL 2005 and Analysis
services were installed. SQL 2005 picked up the change no problem, but when
we try to connect to the Analysis services side of things, it's still
connecting to the old host name. I've been digging around and seen alot of
suggestions that say 'reinstall Analysis services' but I would prefer not to
if there is any other possibility.
If I do need to re-install, does anyone know if re-installing just analysis
services is possible, and how much of the existing data will be overwritten?
Any thoughts appreciated!!!
Edward
ebrooathealthydirectionsdotcomHit "rename sql server" on google. You'll get sites like
http://www.sql-server-performance.c...maintenance.asp which show
how to us sp_dropserver and sp_addserver. I have not had to do this very
often, but it works.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"EB" <ebroo@.healthydirections.com> wrote in message
news:eyuunmiaHHA.4616@.TK2MSFTNGP03.phx.gbl...
> Hi all,
> We had to rename the hostname of a server where SQL 2005 and Analysis
> services were installed. SQL 2005 picked up the change no problem, but
> when we try to connect to the Analysis services side of things, it's still
> connecting to the old host name. I've been digging around and seen alot of
> suggestions that say 'reinstall Analysis services' but I would prefer not
> to if there is any other possibility.
> If I do need to re-install, does anyone know if re-installing just
> analysis services is possible, and how much of the existing data will be
> overwritten?
> Any thoughts appreciated!!!
> Edward
> ebrooathealthydirectionsdotcom
>

MSSQLServerOLAPService connection string name change

Hi all,
We had to rename the hostname of a server where SQL 2005 and Analysis
services were installed. SQL 2005 picked up the change no problem, but when
we try to connect to the Analysis services side of things, it's still
connecting to the old host name. I've been digging around and seen alot of
suggestions that say 'reinstall Analysis services' but I would prefer not to
if there is any other possibility.
If I do need to re-install, does anyone know if re-installing just analysis
services is possible, and how much of the existing data will be overwritten?
Any thoughts appreciated!!!
Edward
ebrooathealthydirectionsdotcom
Hit "rename sql server" on google. You'll get sites like
http://www.sql-server-performance.com/sql_server_maintenance.asp which show
how to us sp_dropserver and sp_addserver. I have not had to do this very
often, but it works.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"EB" <ebroo@.healthydirections.com> wrote in message
news:eyuunmiaHHA.4616@.TK2MSFTNGP03.phx.gbl...
> Hi all,
> We had to rename the hostname of a server where SQL 2005 and Analysis
> services were installed. SQL 2005 picked up the change no problem, but
> when we try to connect to the Analysis services side of things, it's still
> connecting to the old host name. I've been digging around and seen alot of
> suggestions that say 'reinstall Analysis services' but I would prefer not
> to if there is any other possibility.
> If I do need to re-install, does anyone know if re-installing just
> analysis services is possible, and how much of the existing data will be
> overwritten?
> Any thoughts appreciated!!!
> Edward
> ebrooathealthydirectionsdotcom
>
sql