How To Get HOST_NAME Insted Of User_name()
Jun 7, 2001
All developers use same login to access database (I know is it is not the best solution)
We need track when user insert value or edit value in specific table
It is done by triger and work fine with USER_ID() or user_name()
But all users (developers) use same login and USER_ID() and user_name()always the same.
Please help me how can get HOST_NAME of user who is trying to modify table and place it into triger insted of user_name()
Thanks a lot
View 2 Replies
ADVERTISEMENT
Apr 25, 2007
Hi,
In SQL Server 2005 Express BOL,
it says in 'Arguments' description part like this.
user_name
Specifies the name by which the user is identified inside this database. user_name is a sysname.
It can be up to 128 characters long.
What 'user_name is a sysname' imply?
View 2 Replies
View Related
Jul 20, 2005
Hi,I have a User-defined function "Concatenate_NoteTexts" which I use in aquery (SQL Server 2000). On my local development machine it is called likethis:SELECTdbo.Concatenate_NoteTexts(Introducers.IntroducerID ) as NoteTextsFROM tblIntroducersI want to run the same code on a shared remote server where I am user "JON"instead of "dbo". I don't want to hard-code the User Name into the SQL, butwhen I tried to put the user name into a variable as here:DECLARE @USER_NAME VarChar(30)SET @USER_NAME = USER_NAME()SELECT@USER_NAME.Concatenate_NoteTexts(Introducers.Intro ducerID) as NoteTextsFROM tblIntroducersI get the following error:Server: Msg 170, Level 15, State 1, Line 4Line 4: Incorrect syntax near '.'Any advice?TIA,JONPS First posted earlier today to AspMessageBoard - no answers yet.http://www.aspmessageboard.com/foru...=626289&F=21&P=1
View 5 Replies
View Related
Jan 10, 2007
i m writing a stored procudrue to update my data that is onther
table.and i pass the parameter in my vb code,when i pass the data that
is insert only first record of data but second record insert the eroor
will come is data reader is colsed. now insted of data reade i have to
use data set how can i use that and update my data is ontehr
table.?below i written my vb.net2005 code. Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("Project1connectionString").ToString()) ' con.Open() ' Dim ggrnid As String ' Dim acceptqty As String ' Dim itemid As String ' Dim grnid As TextBox = CType(GRNDetailsView.FindControl("fldgrnid"), TextBox) ' ggrnid = grnid.Text ' Dim sWhere As String = grnid.Text ' If (Not String.IsNullOrEmpty(sWhere)) Then ' For Each s As String In sWhere ' ' 'Dim iRowIndex As Integer = Convert.ToInt32(s) ' Dim sqldtr As SqlDataReader ' sqlcmd = New SqlCommand ' sqlcmd.Connection = con ' sqlcmd.CommandType = CommandType.Text
' sqlcmd.CommandText = "select acceptqty,itemid from
grndetail where grnid='" & Trim(ggrnid) & "'" ' datacommand = CommandType.StoredProcedure ' 'datacommand("aaceptqtygrn", con) ' Dim cmd As New SqlCommand("aaceptqtygrn", con) ' sqldtr = sqlcmd.ExecuteReader() ' 'dataset = datacommand. ' 'sqldtr = sqlcmd.ExecuteScalar ' If sqldtr.HasRows = True Then ' While sqldtr.Read() ' acceptqty = sqldtr.Item("acceptqty") ' itemid = sqldtr.Item("itemid") ' cmd.CommandType = CommandType.StoredProcedure ' cmd.Parameters.AddWithValue("@acceptqty", acceptqty) ' cmd.Parameters.AddWithValue("@itemid", itemid) ' sqldtr.Close() ' cmd.ExecuteNonQuery() ' End While ' 'sqldtr.Close() ' 'cmd.ExecuteNonQuery() ' 'Next sqldtr.HasRows ' End If ' Next s ' sqldtr.Close() ' con.Close() ' End If ' End If Catch ex As Exception MsgBox(ex.Message) End Try
View 2 Replies
View Related
Mar 11, 2008
Hi All !!
I have a Server on Development with SQL 2000 + Report Server 2000 and SQL 2005 Standard with Report Server 2005. All is working properly.
Now, my boss realized that it is important to use SQL 2005 Enterprise Edition instead of SQL 2005 Standard Edition. This is the first time I do this and I don't know where to start or which are the items that I should consider when installing SQL 2005 Enterprise Edition.
Should I uninstall SQL 2005 Standard?
After installing Enterprise, Report Server will work as usual?
Are there any items to have in mind?
Many Thanks!!
NetGrey.
View 1 Replies
View Related
Nov 1, 2005
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
View 4 Replies
View Related
Apr 13, 2006
Hello,
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:
View 1 Replies
View Related
Feb 10, 2008
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server"><title>Untitled Page</title> </head>
<body>
<form id="form1" runat="server">
<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>
<div>
<br />
<asp:Label ID="lblUserInfo" runat="server" Text="Label"></asp:Label><asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:JaiConnectionString %>" DeleteCommand="JaiDeleteUser" DeleteCommandType="StoredProcedure" SelectCommand="SELECT users.user_name, users.user_pass, user_roles.role_name, users.VendorId FROM users INNER JOIN user_roles ON users.user_name = user_roles.user_name"
UpdateCommand="JaiUpdateUser" UpdateCommandType="StoredProcedure">
<DeleteParameters>
<asp:Parameter Name="user_name" Type="String" />
<asp:Parameter Name="user_pass" Type="String" />
<asp:Parameter Name="role_name" Type="String" />
<asp:Parameter Name="VendorId" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="user_name" Type="String" />
<asp:Parameter Name="user_pass" Type="String" />
<asp:Parameter Name="role_name" Type="String" />
<asp:Parameter Name="VendorId" Type="Int32" />
</UpdateParameters>
</asp:SqlDataSource><asp:GridView ID="GridView1" runat="server" AllowSorting="True"
AutoGenerateColumns="False" DataSourceID="SqlDataSource1">
<Columns>
<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" /><asp:BoundField DataField="user_name" HeaderText="user_name"
SortExpression="user_name" /><asp:BoundField DataField="user_pass" HeaderText="user_pass"
SortExpression="user_pass" /><asp:BoundField DataField="role_name" HeaderText="role_name"
SortExpression="role_name" /><asp:BoundField DataField="VendorId" HeaderText="VendorId"
SortExpression="VendorId" />
</Columns>
</asp:GridView>
<br />
<br />
</div></form> </body>
</html>
<<<<<<<<<<<<<Stored Procedure>>>>>>>>>>>>>>>>
CREATE PROCEDURE [dbo].[JaiDeleteUser] @user_name varchar (25), @user_pass varchar (25), @role_name Varchar (15), @VendorId intAS
beginDelete from user_roles where user_name =@user_nameDELETE FROM [users] WHERE user_name =@user_nameendGO
I am getting the error
Procedure 'JaiDeleteUser' expects parameter '@user_name', which was not supplied
whenever I try to delete a record. While Updating works with no problem. Please help.
View 8 Replies
View Related
Jul 20, 2005
I did this thing with host_name(). I'm hoping someone can tell me ifI'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 dumpthe ones being viewed into a temp table named TaskTEMP.I am using a custom function within a View to grab a bunch of rows inone table and slap them into a single column on the records returnedby the view.Here's where I'm concerned-When I insert the records into the TempTable I insert the valueHOST_NAME() into every row of a column named myMachine.So the row looks like:TaskID aName myMachine1234 Jim Smith Dell1011235 Fran Jones Dell1011235 Mary Cat Dell101When I run the view, I get:TaskID myNames1234 Jim Smith1235 Fran Jones, Mary CatWhat I'm concerned about is when user 2 is looking at the same recordat the same time and inserts the following into TaskTEMP:1234 Jim Smith CompaqXYZ1235 Fran Jones CompaqXYZ1235 Mary Cat CompaqXYZI use the HOST_NAME()filter in the example below to deal with this sothat both user one and user two get what they want instead of:TaskID myNames1234 Jim Smith, Jim Smith1235 Fran Jones, Mary Cat, Fran Jones, Mary CatIt works great in testing. The obvious problem is if two users havethe same machine name, but in this installation business rules preventthat.I hope someone can either validate this approach or improve or trashit before it gets too late with this project.thanks.lqThe view and function look looks like this:SELECT TaskID, dbo.fn_myNameGroup(TaskID) AS myNamesFROM dbo.TaskTEMPGROUP BY TaskIDIDThe function looks like this:CREATE function dbo.fn_myNameGroup(@TaskID as int) returnsnvarchar(500)asbegindeclare @ret_value nvarchar(500)SET @ret_value=''Select @ret_value=@ret_value + '; ' + aName FROM TaskTEMP whereTaskID=@TaskID AND myMachine=HOST_NAME()RETURN RIGHT(@ret_value,LEN(@ret_value)-2)end
View 3 Replies
View Related
Feb 5, 2007
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.
aero1
View 6 Replies
View Related
Aug 25, 2007
Please help I keep getting this message when I log on to my data base. Not sure what state 1 relates too
TITLE: Connect to Server
------------------------------
Cannot connect to <server_name>
------------------------------
ADDITIONAL INFORMATION:
Login failed for user <user_name>. (Microsoft SQL Server, Error: 18456)
------------------------------
BUTTONS:
OK
------------------------------
Server Name: <server~name>Error Number: 18456
Severity: 14
State: 1
Line Number: 65536
View 13 Replies
View Related
Oct 30, 2005
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())
View 20 Replies
View Related
Nov 2, 2006
BOL says the following (headed with important):
"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?
Thank you for your help!
regards, Stefan
View 8 Replies
View Related
