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
Ok, to start, i have read SO many posts on this site, and othe sites, and i cannot get this to work.
What i want to do is: 1. Login 2. show an image from a SQL database, based on that login.
I have the image path stored in the database, and i can easily get it to display on the screen, but it doesnt show the correct image for the login, it shows them all. (all images based on that column).
I know i have to put a WHERE at the end of my SELECT statement, but everything i have tried doesnt work.
When i do the query builder, and i put in the value, it shows exactly what i want, based on the login's username.
My company is moving from developing with Sql Server 2000 to developing in 2005, but we don't necessarily want to require that our customers upgrade with us.
Is it possible for us to develop in 2005 without compatibility problems with 2000? By develop, I refer to the many stored procedures we would write, databases we would create/open/backup/restore, and scripts we would run.
Generally, are we going to have backwards compatiblity issues if our customers continue to use 2000?
Hi,I have a question regarding host_name() and IP addresses of clients. I'mrunning on a shared server - so access to xp_cmdshell is barred which is thestandard response to questions about getting the IP address of a client fromsql server. My issue is this:For security reasons every user of our database system logs into our customsecurity system all under the *same* sql-server user name (who only hasaccess to a discrete set of stored procedures). This can't be changed as weare limited to 3 database users. I store the host_name that the user log'sin from when he logs in - and then check the host_name of any further callsto sp's under this login context. I have however just discovered thathost_name() is set in the connection string - so the client can pass prettymuch whatever he wants to - so all an imposter would have to do is *fake*the client name of an existing user. Is there anyway of detecting the *real*client's host? Is there any way of forcing a client to be limited to justone client machine? Can I get hold of the IP address in a reliable way?ThanksNick
I am wanting to limit the amount of rows that are merged between the server and the wm device. Host_name() doesn't seem to be supported in sql mobile. I created a column called host and set the default value as host_name(). I get the table to replicate however when I try to insert into the table on the mobile side I get errors. Is there another option to filter like this or if I hard code a deviceID how can I add this to the filter rows options? Thanks.
John
Table script:
CREATE TABLE [dbo].[tstHost](
[peoplid] [numeric](18, 0) NOT NULL,
[image] [image] NULL,
[host] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_tstHost_host] DEFAULT (host_name()),
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [MSmerge_df_rowguid_F9DD287E09FF4064813E154D2F0ACBC6] DEFAULT (newsequentialid()),
CONSTRAINT [PK_tstHost] PRIMARY KEY CLUSTERED
(
[peoplid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Query on sql mobile side:
insert into tsthost (peopleid) values (2)
Error: FAILED: insert into tsthost (peopleid) values (2) Error: 0x80040e14 DB_E_ERRORSINCOMMAND Native Error: (25503) Description: The column name is not valid. [,,,Node name (if any),Column name,] Interface defining error: IID_ICommand Param. 0: 0 Param. 1: 0 Param. 2: 0 Param. 3: Param. 4: peopleid Param. 5:
I am using dynamic filtering on host_name() for SQL 2005 SP1 merge publication. It had been working fine, but something has caused the configuration to stop working.
Setting up the publication and a test subscription via the GUI works fine. However, if I script out the publication and subscription and attempt to run the script (with suitable password), then I get the error
a value for the parameter @host_name was specified while publication does not use host_name() for dynamic filtering?
I did some digging and was curious to find some differences in the information returned by sp_helpmergepublication and the content of dbo.sysmergepublications.
The sp_helpmergepublication output showed expected content after both GUI and script setup with a value of HOST_NAME() for validate_subscriber_info.
The content of dbo.sysmergepublications was as expected after setup via the GUI with a value of HOST_NAME() for dynamic_filters_function_list.
However, I noticed that if I performed setup using scripts the dynamic_filters_function_list value was null.
Further note that the subset_filter_clause value was correct for the filtered article with both GUI and script setup (<column name> = HOST_NAME())
After some experimentation, I found that if I executed sp_changemergearticle to set the subset_filter_clause again (after all the normal publication setup scripts had run) then the dynamic_filters_function_list value in dbo.sysmergepublications became correct and everything started working again.
This looks like a bug to me and although the workaround above seems to work it would be good to know if this is a known issue
If this is not a known issue, then I will attempt to put together a simpler repro than I currently have available.
I have an Access 2000 MDB file with a SQL 7 back end. I have a main tablewith 50,000 records; and I have a selections table with 50,000 records foreach machine that uses the database (about 25-50). This allows each user tohave their own set of selections.The selections table has three fields: ID (int), Sel (bit), MachName(varchar). ID and MachName comprise the primary key.I have a view that combines the main table and the entries for theselections table for the current machine (SQL below). The view works finewhen opened in EM and QA. And if I create a pass-through query from myAccess MDB file, the results are displayed fine.However, if I link the view to the Access MDB file, I get "#Deleted" inevery field of every record (which seems to indicate that the records werethere and then they were gone). However, if I hard-code the machine nameinto the same view instead of using HOST_NAME and then relink the view tothe MDB file, the linked view opens fine. Only when I use HOST_NAME as aparameter in the view is there a problem with it.Anyone have any idea what's going on here, or have heard of any issues withHOST_NAME and ODBC linked objects? SQL for the view is below.Thanks!NeilSELECT INVTRY.*, InvtrySelections.Sel, InvtrySelections.MachNameFROM dbo.INVTRY INNER JOINdbo.InvtrySelections ONdbo.INVTRY.ID = dbo.InvtrySelections.IDWHERE (dbo.InvtrySelections.MachName = HOST_NAME())
"The HOST_NAME() function returns an nchar value, so you must use CONVERT if the column in the filter clause is of a numeric data type, as it is in the example above. For performance reasons, we recommended that you not apply functions to column names in parameterized row filter clauses, such as CONVERT(nchar,EmployeeID) = HOST_NAME(). Instead, we recommend using the approach shown in the example: EmployeeID = CONVERT(int,HOST_NAME()). This clause can be used for the @subset_filterclause parameter of sp_addmergearticle (Transact-SQL), but it typically cannot be used in the New Publication Wizard (the wizard executes the filter clause to validate it, which fails because the computer name cannot be converted to an int). If you use the New Publication Wizard, it is recommended to specify CONVERT(nchar,EmployeeID) = HOST_NAME() in the wizard and then use sp_changemergearticle (Transact-SQL) to change the clause to EmployeeID = CONVERT(int,HOST_NAME()) before creating a snapshot for the publication."
We have setup a publication with this scenario and by accident a nonconvertable HOST_NAME slipped in when someone tried to create a subscription in the database with the wrong parameters.
The consequence was not only that he failed to create the misconfigured subscription but all our several hundred subscriptions failed to synchronize from this point on.
It's weird that one single subscription causes all other subscriptions to fail (BUG??) but how to resolve this miserable situation?
First thing I tried was to delete the faulty partition via the Publication Wizard but all I got was an nasty error which was also related to the SQL Server trying to convert the partition string to integer.....
The next thing I tryed was setting PreComputed Partitions to false and re-initialize all subscriptions. Also this did not help. All the failed subscriptions continued to fail synching.
As a thing of last resort I changed the article filter to convert the partition key to nvarchar and then compare it to the HOST_NAME() value. Thank god this worked and the existing subscriptions started to synch successfully again.
So my question is: How do you correctly recover in this situation? As it really a wanted feature that one faulty partition causes all subscriptions to fail? Shouldn't it be possible to delete the unwanted partition?