I did this thing with host_name(). I'm hoping someone can tell me if
I'm gonna get into trouble with this scheme before it's too late...
I have maybe 75 users.
They all call on a table of appointments (many thousands) and I dump
the ones being viewed into a temp table named TaskTEMP.
I am using a custom function within a View to grab a bunch of rows in
one table and slap them into a single column on the records returned
by the view.
Here's where I'm concerned-
When I insert the records into the TempTable I insert the value
HOST_NAME() into every row of a column named myMachine.
So the row looks like:
TaskID aName myMachine
1234 Jim Smith Dell101
1235 Fran Jones Dell101
1235 Mary Cat Dell101
When I run the view, I get:
TaskID myNames
1234 Jim Smith
1235 Fran Jones, Mary Cat
What I'm concerned about is when user 2 is looking at the same record
at the same time and inserts the following into TaskTEMP:
1234 Jim Smith CompaqXYZ
1235 Fran Jones CompaqXYZ
1235 Mary Cat CompaqXYZ
I use the HOST_NAME()filter in the example below to deal with this so
that both user one and user two get what they want instead of:
TaskID myNames
1234 Jim Smith, Jim Smith
1235 Fran Jones, Mary Cat, Fran Jones, Mary Cat
It works great in testing. The obvious problem is if two users have
the same machine name, but in this installation business rules prevent
that.
I hope someone can either validate this approach or improve or trash
it before it gets too late with this project.
thanks.
lq
The view and function look looks like this:
SELECT TaskID, dbo.fn_myNameGroup(TaskID) AS myNames
FROM dbo.TaskTEMP
GROUP BY TaskIDID
The function looks like this:
CREATE function dbo.fn_myNameGroup(@TaskID as int) returns
nvarchar(500)
as
begin
declare @ret_value nvarchar(500)
SET @ret_value=''
Select @ret_value=@ret_value + '; ' + aName FROM TaskTEMP where
TaskID=@TaskID AND myMachine=HOST_NAME()
RETURN RIGHT(@ret_value,LEN(@ret_value)-2)
end
