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
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
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?
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:
<<<<<<<<<<<<<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.
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
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.
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
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?
