I am looking for an expression for a group in a matric. I am trying to figure out how to group by the a certain amount of letters in a string. For example if I have the followong fields I am grouping...
I want to group it up as...
And then be able to summarize the results in the matrix.
Hi, I test the following sql statement, finding that an error ocurs:
Msg 7630, Level 15, State 2, Line 3 Syntax error near '"' in the full-text search condition '"dsg SDRGDG " OR "sdfsdfsdfsdafdsafdsfds'.
DECLARE @searchTerm NVARCHAR(40) SET @searchTerm = '"dsg SDRGDG " OR "sdfsdfsdfsdafdsafdsfdsafdsafdsafsafdfdsafdf"'; SELECT [JobTitle], [JobDes], [OpenDate], j.[URLRef], c.[CompanyName], c.[URLRef], c.[URLSource] FROM JobWanted AS j INNER JOIN Company AS c ON c.CompanyID = j.CompanyID WHERE CONTAINS((JobTitle, JobDes), @searchTerm)
It seems too lengthy string will cause an error for full-text engine. I find the sdfsdfsdfsdafdsafdsfdsafdsafdsafsafdfdsafdf is truncated as shown in error message.
How to avoid this issue? Could I configre this limination?
Is it possible using Sql2000 to compare the first four characters of a parameter? WHERE (tblLinkInfo.CG_ID = @BCG_ID) AND ( 'first 4 characters' of Location_Zip.Zip = 'first 4 characters' of @ZipCodeIn)
Sorry, I realize that this is probably a pretty simple question for you, but I am in a quick time bind and would really appreciate it if someone could help me with this update that I'm trying to run.
Let's say you have a table (call it testtable) that contains the column "text".
Now in column text you want to replace all instantiations of the phrase "in the US" with "to the United States", how would you structure your update query to perform this change (Please note that this phrase could be embedded in the middle of a larger sentence like 'Send a package in the US'. You'd want to change this instance to 'Send a package to the United States')
Daily, I have to map thoursands of items into its correct category. What I have to do is Filter item by the ClSID and SCLISD, then look into the Label for where the item should be. For example from the attachment,
CLSID = 1 and SCLSID = 23 - if in the label has the keyword "notebook", its NEW_CAT should be 14 and its NEW_CATNAME is LAPTOPS;
CLSID = 1 and SCLSID = 23 - if in the Label contains Tablet PC, its NEW_CAT name should be 2474 and its NEW_CATNAME is PC LAPTOPS.
CLSID = 3 and SCLSID = 54 - if in the Label contains Watch, its NEW_CAT name should be A6052 and its New_CATNAME is SMART WATCH.
Basically, the work flow is looking into the CLSID, then SCLSID,then, Label and then assign a correct NEW_CAT and NEW_CATNAME of the item.
I have written a code but I can't get it look into specific CLSID and SCLSID.
For example, i have when upper(Label) like upper ('%Tablet%PC%') then '2474 PC Laptops'
Select AVG(AVG_Back), AVG(AVG_Yield) FROM tblUser WHERE Date Between '3/1/2008' AND '3/31/2008'
I want to limit the AVG_Back field to exclude all values of 0. So only average AVG_Back if the value > 0. What is the best way to accomplish this? I can't just put it in the where clause or the AVG_Yield will be excluded too.
I have a column that is VARCHAR(30) this column is supposed to contain values that "look" like a date in the form mm/dd/yyyy - however because it is a free-text character field often times data is entered other then a date -- "text" -
How do I return only the data that is in the format of mm/dd/yyyy
I moved a database from one server to another, in this database are references to a UNC path on the server. now I need to find a way to modify the server name that was in each row to reflect the new server name and path, ex:
\server1path1data to now say \server2path2data
how do I selectivly modify just some of the data? or do I have to basically re-write the entire column for each row?
I have a table called MasterSkillList which is a list of skills and attributes, eg: [Appraise, INT], [Bluff, CHA] etc I have a table called Classes, which is a list of all classes available (and some details which are irrelevant), eg: [Fighter], [Assassin] etc. I also have a table called ClassSkills which holds a list of classes and their applicable skills, eg: [Assassin, Bluff], [Assassin, Open Lock], [Fighter, Appraise], [Fighter, Bluff] etc. What I have is a gridview which shows all my classes from the class table. i want to be able to select a class on that gridview and create a checkbox list of all available skills that are NOT allready associated with that class. Eg: assassin has bluff and open lock, so those two skills shouldn't appear on my checkbox list. So i want to show all the skill from the master skills list, excluding all the skills the selected class allready has. Alternatively, It would be better if there was a way to display all the skills in existance on my checkboxlist and the ones that class allready has to be checked. Any suggestions? Here's the query I have: SELECT MasterSkillsList.SkillFROM ClassSkills INNER JOIN MasterSkillsList ON ClassSkills.Skill = MasterSkillsList.SkillWHERE (MasterSkillsList.Skill <> ClassSkill.Skill) Edit: I just added the following sql query, but when i run it i get no results even though it should show everything except 2 skills. Have I written it wrong? SELECT SkillFROM MasterSkillsListWHERE (NOT EXISTS (SELECT Skill FROM ClassSkills WHERE (ClassName = @ClassName)))
After converting from SSIS 2008 to SSIS 2012, I am facing major performance slowdown while loading fact data.When we used 2008 - one file used to take around 2 hours average and now after converting to 2012 - it took 17 hours to load one file. This is the current scenario: We load data into Staging and Select everything from Staging (28 million rows) and use a lookups for each dimension. I believe it is taking very long time due to one Dimension table which has (89 million rows).
With the lookup, we currently are using partial cache because full cache caused system out of memory.Lookup Transformation Editor - on this lookup - how to increase the size on partial Cache size 64-bit? I am being stuck at 4096 MB and can not increase it. In 2008, I had 200,000 MB partial cache size.
Now the issue starts when I add a parameter to my report for user to choose which outlets (LOCATIONs) he wants in the equation. I know how to make a column disappear based on parameter value but how to take it out of equation? At the moment when user selects only LOCATION2 and not LOCATION3 then data is not filtered correctly:
I am trying to insert a row into a table of Microsoft SQL Server 2000.
There are various columns.
[SNO] [numeric](3, 0) NOT NULL , [DATT] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [DATTA] [char] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [CODECS] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
The [DATTA] column is causing a problem. Even if I am trying to put only 1700 character string into [DATTA], the java code throws the following exception:-
StaleConnecti A CONM7007I: Mapping the following SQLException, with ErrorCode 0 and SQLState 08S01, to a StaleConnectionException: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Connection reset
at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
Why is it throwing an exception even though the sum-total of this row doesn't exceed 8000 characters?
Hello, I am tring to add a string my database. Info is added, but it is the name of the string, not the data contained within. What am I doing wrong? The text "Company" and "currentUserID" is showing up in my database, but I need the info contained within the string. All help is appreciated!
Imports System.Data Imports System.Data.Common Imports System.Data.SqlClientPartial Class _DefaultInherits System.Web.UI.Page
Protected Sub CreateUserWizard1_CreatedUser(ByVal sender As Object, ByVal e As System.EventArgs) Handles CreateUserWizard1.CreatedUser 'Database ConnectionDim con As New SqlConnection("Data Source = .SQLExpress;integrated security=true;attachdbfilename=|DataDirectory|ASPNETDB.mdf;user instance=true") 'First Command DataDim Company As String = ((CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Company"), TextBox)).Text) Dim insertSQL1 As StringDim currentUserID As String = ((CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("UserName"), TextBox)).Text) insertSQL1 = "INSERT INTO Company (CompanyName, UserID) VALUES ('Company', 'currentUserID')"Dim cmd1 As New SqlCommand(insertSQL1, con) '2nd Command Data Dim selectSQL As String selectSQL = "SELECT companyKey FROM Company WHERE UserID = 'currentUserID'"Dim cmd2 As New SqlCommand(selectSQL, con) Dim reader As SqlDataReader '3rd Command Data Dim insertSQL2 As String insertSQL2 = "INSERT INTO Company_Membership (CompanyKey, UserID) VALUES ('CompanyKey', 'currentUserID')"Dim cmd3 As New SqlCommand(insertSQL2, con) 'First CommandDim added As Integer = 0 Try con.Open() added = cmd1.ExecuteNonQuery() lblResults.Text = added.ToString() & " records inserted."Catch err As Exception lblResults.Text = "Error inserting record." lblResults.Text &= err.Message Finally con.Close() End Try '2nd Command Try con.Open() reader = cmd2.ExecuteReader()Do While reader.Read() Dim CompanyKey = reader("CompanyKey").ToString() Loop reader.Close()Catch err As Exception lbl1Results.Text = "Error selecting record." lbl1Results.Text &= err.Message Finally con.Close() End Try '3rd Command Try con.Open() added = cmd3.ExecuteNonQuery() lbl2Results.Text = added.ToString() & " records inserted."Catch err As Exception lbl2Results.Text = "Error inserting record." lbl2Results.Text &= err.Message Finally con.Close()End Try
I run statement down,result is "go". why goes,gone,going is not in the result? SELECT ID, firstname, lastnameFROM [contain-1]WHERE CONTAINS(firstname, ' FORMSOF (INFLECTIONAL, go) ') information table [contain-1] ID firstname 1 go 2 goes 3 gone 4 going
I run query down.result is null. Why is result null? SELECT ID, firstnameFROM [contain-1]WHERE CONTAINS(firstname, 'yaser NEAR pooya') table information: ID firstname 1 ali 2 peter 3 yaser 4 yaserapooya 5 mehdi
Hi,i want to search record in a table where a varchar column is equal toa word or this word with the letter 's' or this word with the letter'e'.Today, i've this query :select * From Table Where Column = 'Word' or Column like 'Word[es]'I want to remove the 'or' of this query to have only one condition.Does someone know a solution to my problem.Excuse my poor english language.I hope you understand my problem and thks for solution.
Hi everybody, I have a problem using the CONTAINS predicate. I use the contains predicate in a web page that students utilize to look for dissertations stored in a database (MS 2000) of the library. The search is one big textbox where students can perform any type of reaserch. For instance, they can insert the author, the title or few keywords of a dissertation. After they have clicked on the button Submit, the words that they have inserted are filtered and combined (with AND) in one string: Example: miami, education --> 'Miami AND Education'
An example of the query that I need to perfrom is the Query Number 1:
where contains(SearchColumn,'miami AND education') order by docnumber
SearchColumn is a field (varchar) that is the concatenation of various fields such as Location, Title, Author, etc... that are contained in the same table (See below for the description of the Table DocTable1).
I'm currently testing the search and I have figured out that the query described above does not work. For example, the query should produce the same resultsof the following one, but it does not:
select docnumber FROM [Log].[dbo].[DocTable1] where contains(Location,'miami') AND contains(Keywords,'education') order by docnumber
I need to implement the first query, but i can not understand why it does not produce the same results of the second one. Any Ideas?
I am having problems with the following SQL statement. It works how I want it to work if I use = @SearchQuery but when I try to switch to LIKE I run into problems. I tried it the way it is shown below and I also tried LIKE '%'@SearchQuery'%' with no luck. Does this have something to do with the pattern to match being a parameter? How would I go about doing this correctly. "SELECT server_info.*, network_adapter.* FROM server_info LEFT JOIN network_adapter " + "ON server_info.server_id = network_adapter.server_id WHERE " + DropDownList1.SelectedValue.ToString() + " LIKE '%@SearchQuery%'" Thanks
HiI am having a debate with one of the Postgres developers (Tom Lane)according to him ms-sql does not implement predicatelocking(SERIALIZABLE).Where predicate locking is defined as (from the postgres help):"126.96.36.199. Serializable Isolation versus True Serializability"It states: "To guarantee true mathematical serializability, it isnecessary for a database system to enforce predicate locking, whichmeans that a transaction cannot insert or modify a row that would havematched the WHERE condition of a query in another concurrenttransaction"Now I put it to him that that is exactly what SERIALIZABLE does isMS-SQL.But his response was "Only for WHERE conditions that can be expressedas a simple range constraint."Now is he correct i.e SERIALIZABLE works "Only for WHERE conditionsthat can be expressed as a simple range constraint." ?Regards,Daniel RothMCSD.NET