|
Hi guys,
I am having an inlined table valued function to search the users in my site.
The search is optional ie. sometimes user will search with first name, sometimes he searches with last name, and sometimes both.
As of now I am using if-else statements to check whether first name is ''(empty),or last name is ''(empty) or both are ''(empty).
I have two questions?
1. Is there any other way, so that I can avoid these if-else statements and make it a single query?
2. If I am using a function as shown below, I have to specify all the return parameters. Is there any way I can avoid this and return the complete table(I do not want to use stored procedures)?
I'm using a freetext search,because I want to get the search results according to relevence(ranked results).
ALTER FUNCTION [dbo].[Search]
(
@FirstName Varchar(400),@LastName Varchar(400)
)
RETURNS
@SearchResults TABLE
(
-- Add the column definitions for the TABLE variable here
UserName Varchar(400),
EmailAddress Varchar(400)
)
AS
BEGIN
IF (@FirstName<>'' AND @LastName<>'')
begin
insert into @SearchResults
Select Top 100 Percent UserName,EmailAddress
From UsersFirst t
JOIN FreetextTable(UsersFirst, FirstName, @FirstName) K On t.Id = K.[Key]
JOIN UsersSecond U on t.UserId=U.Id
JOIN FreetextTable(UsersSecond, LastName, @LastName) As L On U.Id = L.[Key]
ORDER BY K.[Rank] DESC;
end
ELSE IF (@FirstName<>'')
begin
insert into @SearchResults
Select Top 100 Percent UserName,EmailAddress
From UsersFirst t
JOIN FreetextTable(UsersFirst, FirstName, @FirstName) K On t.Id = K.[Key]
JOIN UsersSecond U on t.UserId=U.Id
ORDER BY K.[Rank] DESC;
end
return
END
Thanks In Advance
jophin
|
|
|
|
|
Dear Friend's,
My Issue scenario is as follow's
i have two servers Name
"Server_A"
"Server_B"
In "Server_A" i have one Database "DB1"
In "Server_B" i have one Database "DB2"
in Database "DB1" of Server "Server_A" i have one table "tbl_ListOfCountry"
now as per my business logic that table must be centralize where my client will update information and i need to use this table data in "Server_B".
For that i have create one procedure which pull all the data of table "tbl_ListOfCountry" from "Server_A".
for that i have created Synonym "tbl_ListOfCountry"
using following syntex
CREATE SYNONYM [dbo].[tbl_ListOfCountry] FOR [Server_A].[DB1].[dbo].[tbl_ListOfCountry]
now for security purpose i have created one user "user1" with "Server Role" as "Public" and "User Mapping" as "db_datareader", "db_datawriter" and "public" in "Server_B"
and Grant As Execute Permission of this user to my procedure "Proc1" which use to pull the record.
now the main issue is started
whenever i execute that procedure "Proc1" using "user1" login instead of "sa" it give me error
Msg 18456, Level 14, State 1, Line 1
Login failed for user 'user1'.
Please, help me to overcome this issue...
Thanks in advanced..
Sasmi
|
|
|
|
|
Let me guess. The password for the 'sa' account on DB1(ServerA) is the same as on DB2(ServerB). 
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
Thanks for the quick reply...
I am also think same but when i am using this Procedure in my application it is giving me same error can you explain me why this happening....
|
|
|
|
|
I suspect that it works when you are logged in to DB2 as 'sa', because it attempts to use those same credentials on DB1, it will work since 'sa' also exists in DB1 with the same password. When you login to DB2 with 'XYZ', the same login will be attempted on DB1 and since 'XYZ' doesn't exist on DB1, the login fails. The quick and dirty solution is to replicate the userid on DB1 and I suspect that will allow the link to work.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
if the shift time is 7pm to 3am..after 12am,it will be considered as next day..and the time after 12am will be calculated as work hour of next day..please help me to write a sql function for this..pls...
|
|
|
|
|
|
Things you may want to consider when designing a system which includes a start time and end time:
1) Store both the date and time. This will make it much easier to determine if a shift crosses a midnight boundary.
2) Use the concept of a "End of Day" time. For example, lots of manufacturing sites use 11pm (23:00) because they consider anyone working after 11pm to be working the next day.
3) Also take into consideration a "Pay Date", typically this is what is considered the day the person works (and gets paid for). For example if your worker starts his shift on Friday at 7pm and works into Saturday morning, he would be paid for working Friday. Not 2 different pay days. (Fri & Sat)
Best of luck.
|
|
|
|
|
Time can be a real nightmare to handle. Other issues which you might want to think about include:
Day-light saving time (esp. around midnight boundaries).
Time zones -- don't look applicable in your case, but I worked on a car hire problem which supported pickup/drop off across time zones.
Of course, I've not actually answered the original question. Some idea of the data structure/data available might be useful.
|
|
|
|
|
Hi All,
I have used (datepart(hour,CR_callstart)) As Hours to group Data by Hours.
What i would like is to group them by Every 30 Minutes. Is there any thing i can use so that
I can get data grouped every hlaf an hour.
At the moment i can get :
Time Revenue
10:00 $100
11:00 $200
With :
Select (datepart(hour,CR_callstart)) As Hours,sum(cost)as Revenue from dbo.Tablename
Group by Hours
But I want to get
Time Revenue
10:00 $75
10:30 $25
11:00 $100
11:30 $100
Please advice.
Thanks
|
|
|
|
|
Test the time component (using datepart) for the number of minutes, set a value 0 or 30 and then group by the value. This probably can all me done in 1 select statement.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks so Much for your reply.Its much appreciated.Sorry i am Having trouble to understand it.How do i modify :
(datepart(hour,CR_callstart)) As Hours
Many thanks.
|
|
|
|
|
This should do it.
SELECT
DATEPART(hour,CR_callstart) as hour,
CASE
WHEN DATEPART(minute,CR_callstart) between 0 AND 29 THEN 0
ELSE 30
END as minute,
sum(cost) as revenue
from tablename
group by DATEPART(hour,CR_callstart),
CASE
WHEN DATEPART(minute,CR_callstart) between 0 AND 29 THEN 0
ELSE 30
END
|
|
|
|
|
That is Fantastic.Is there any way I can put Hour and Minute in One Column.
Like :
Time
10:00
10:30
11:00
11:30
etcc..
Thanks so much for your time.
|
|
|
|
|
It_tech wrote: Is there any way I can put Hour and Minute in One Column.
Sure, but thats presentation logic and should be handled appropriately.
|
|
|
|
|
Thanks For your reply and sorry to bother you.Can i do that from a Sql level?
It would be great if you can give me an example.
Many thanks.
|
|
|
|
|
He wrote the code for you - what do you want, that he should go out and test your app as well.
Jamie stated that the requirement is PRESENTATION logic therefore it does not belong in the database and you then ask him if it can be done in the database.
Do your own work and use your brain instead of relying on the forum for ideas.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks.I have already sorted it out.
Cheers
|
|
|
|
|
Hi Guru's
I have a Linked Server in SQL 2005 to an Access 2003 database.
I have written a stored procedure to insert a record into the Access DB. When I run the Proc on it's own, it perfoms the task, but the moment I call it from a Insert Trigger, do I get an Error Message Saying that the Linked Server does not support the required interface.
Could someone please help me with a solution as to how I am going to insert / update the table in Access from SQL?
Your help will be greatly appreciated.
Kind Regards,
Elizma
|
|
|
|
|
It seems likely that linked servers do not support triggers (spit - I hate triggers) so I would do the following.
Do some reading on linked server/triggers (you mey get a definitive answer here).
Move the insert to Access out of the trigger and into the proc that inserts into your SQL table. You will miss any inserts that do not go through the proc but is will work.
If you have data inserting from another source then track it down and nail it.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi all I am using Microsoft.Jet.OleDb.4.0 provider to obtaint data from Excel files. Here's the connection string and it works properly:
string conn = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=source.xls;Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
The problem is that I want whenever excel file was opened or to be using by another programs, OleDb provider wouldn't access to the excel files. I guess that I have to set some properties on Extened Properties. I have googled alot but it seems nothing about it.
anybody help me! thanks
[N][Q][H]
|
|
|
|
|
Do you get an error if the file is in use?
If not how do you expect the connection to detect the file is in use. If you are then trap the error and tell the user.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Perhaps you are checking for the wrong thing... what you could consider doing is checking if the file is open before attempting to open it yourself...
There's a nice example here[^] that I found with a simple google search - it's about half way down - the one that uses the API calls.
|
|
|
|
|
this's just look like when you use
File.OpenRead(filename)
error will be returned when the file is opening.
In my case, if the file is still opening and user can modify the content while I am trying to obtain data. That's bad isn't it?
[N][Q][H]
|
|
|
|
|
Dear all,
Pls help me solve this.
select distinct voyage_no,vessel_code from D4A_RAW_BLP where vessel_code in (select distinct vessel_code,vessel_desc from D4A_RAW_BLP where vessel_code<>'') order by vessel_code
when i execute this query error occour as below
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Thanks and best regards
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
