I have tested this procedure changing the variable @aname for the surname of the author. This works fine. The problem comes when I execute this procedure from web matrix. I use a textbox to obtain the data for the variable @aname. I send the data. What happens is that when I run the web page and look for a book through the author the select statement behaves differently and gives me a different output.
I was doing research oh how to do some searches on dates that allow a bit of a freedom to the person searching. They should be able to specify the parameters. For example how would you make a search for all events that happened on a specific day in any year,any month? example : every 18 Or how would you searh for values that happened in a specific month in any year and any day? eg. May Or how would you search for values that happen in a specific year and any day and month? eg. 2007 Or how would you search for values that happen every 18 May?
I tried few things and can only work out the search for the year bit. The values are saved in the database as DateTime.
First thank you for all your help thus far. Now I'm stuck again. I've been doing a lot of reading on triggers and logging information into tables but I've been trying to capture how many times someone enters an item into the search box.
So every time somebody types Gumballs into the search box I want to capture it and the name of the person who is currently logged in. Is there away to do this? Maybe this is something that I should be checking in ASP.NET forums?
I have a number of functions that require the input of parameters in order to ultimatly create a report under Reporting Services by making use of a Stored Procedure.
All the functions etc work as does the stored procedure, but it only works if I specify data that I know exists e.g.
Hi, I am looking for an easy and efficiant way to add a boolean search function to an already existing search i have using ASP pages and MSSQL 2000 SPROCS.
Currently I have some code such as:
CREATE PROCEDURE sp_name
@var1 int = NULL, @var2 varchar(50) = NULL
AS
SELECT blah, blah, blah FROM sometable INNER JOIN anothertable ON anID = anID WHERE (table1.column2 = COALESCE(@var1,table1.column2) AND table2.column4 = COALESCE(@var2,table2.column4))
and this works like a charm, if i pass a value into @var1 or @var 2 it gives back results where those crietera match, or if I pass no values in it returns all records.... anyways, you get the idea...
Now I would like to add the ability to introduce a 3rd variable, and have it run a boolean search on a varchar(2500) column. This column is a description field for an image so I would like to be able to search the descriptions by passing in something like 'car OR bike AND travel' and return all the records that have the words 'car' or 'bike' and 'travel' somewhere in the description field.
How can i do this? I am using SQL Server Standard Edition so I don't believe I have full text search functions available, (not sure if that would even help).
Thanks all for your support and guidance!
P.S. i would also like to maintain the functinality so that if all the @var# are blank it still returns all records.
I am writing a stored procedure to pull records from a table of personal data related to jobs applied for.
There are 3 tables involved, the jobs, the applicants and the applications.
I need to search on job title, job ref from the jobs table and on forename, surname and applicant ID from the applicants table.
There are some quirks here, if the user enters an applicant ID then we can simply scan the jobs table for that id where it also matches the job title wildcards, so that is quite easy to manage.
My own idea for the more complicated searches was to gather the unique ID's from the jobs table into a var, then do similar with the applicants and then search the applications table where both these ID's matched? I think that wouldn't work so well if using the 'WHERE IN()' clause for the main query?
So what approach would be best here to perform the second part of the SP if the client hasn't passed an ApplicantID?
Obviously the applications table has both JobID and ClientID's to relate back to the applicants table.
So can anyone help here, it seems a fairly complicated statement or set of statements would be required here.
Here is my requirement: The script needs to enumerate the the databases in a givin instance of sql server. Then it needs to output the account with permissions on that server. Finally it then needs to search stored procedured for reference to specific file and account names loaded from a csv file. The results should be output to a text file or displayed in an exportable window format.
What I don t understand: What do they mean by: account with permissions on that server, which account re they talking about
and what do they mean by the SP references a specific file and account name? how can an SP reference a file or account name. i have never seen that? Thanks for help.
For anybody that implemented fuzzy searches in SQL Server or any otherdatabase for that matter. I have implemented edit distance algorithm,specifically q-grams, in SQL Server and wanted to get some opinionsfrom anyone who used a similar algorithm in a DBMS.We are looking to implement fuzzy address search and are employingnumber of different techniques including synonyms for strings like"drive", "dr", "drv", etc... Number expansion, so 6 ave = Six Ave =6th Ave = 6... Now, we also have an implementation of edit distancealgorithm, which would cover misspellings and similar sounding words.One of the challenges is putting various techniques together. Synonymsand number expansion techniques go well together but no edit distance.Also, I am trying to figure out how to leverage edit distanceimplementation for 'contains' searches. So if a string like'Mississippi Municipal Building' would be returned if the Search is for'Misisipi Municipal'.Any thoughts?
Hi All,I have the following scenario. I have a table called Invoice. Thishas around 30 columns of which i have to do a retrieval based onfilter conditions on 10 columns. These filters need to be partialsearches i.e. for e.g the Customer name could be 'Arun', 'Parthiv','Aaron', now i should be able to search the customer as 'ar' and itshould return 'Arun' and 'Parthiv'. My concern is there are 10 columnson which this like '%x%' search has to be done and there willpractically be hudreds of thousands of rows. can anybody suggest me toimprove the performance of such a query. Currently what i am thinkinof isselect Id, Memo, .. FROM Invoice where CustomerName like '%' + @Name +'%' and etc.P.S. am using ASP.Net as the front end.
I have a database containing several tables with many different fields. I need to create an admin section that lets me search on one field or the combination of several. Does anyone have links to pages that offer a general overview for inhouse database search strategy and admin edits. Thank you
I have recently become responsible for a small database for a volunteer soccer league. I am reasonably savvy when it comes to development, but I have not had a lot of experience with administration before.
I need to do what I think must be pretty simple: set up full text indexing so I can use a CONTAINS search on a table. The table contains all of the fields the kids use, and each field has a number of divisions that typically play on that field; we use these 'favored divisions' to make scheduling a little easier. Now, one day when I have time, I will set up a proper, normalized, one-to-many relationship between the favored divisions and the playing fields, but right now it's basically like this:
fieldID (int, primary key, identity seed) fieldName (varchar), e.g. High School Field favored_divisions (varchar) - comma-delimited list of divisions, e.g. G10,B14,G12
I imagine it's probably database sacrilege to have a comma-delimited list like that, but we don't have the resources now to re-write that piece of the web application. My question is, in SQL Server 2005, what do I need to do to be able to do a full-text search on this field with the following query:
SELECT fieldID, fieldName FROM playing_fields WHERE CONTAINS(favored_divisions,'G10')
Right now the query runs and does not return an error, but does not return any results, either. IIRC, full-text indexing is enabled by default in SQL Server 2005, but I am not familiar with the procedure -- something about having to populate a catalog. Do I need to edit or set up a new index on the actual playing_fields table? What has to happen to make this work?
I have a form that loads a dataset. This dataset is composed from SQL statements using alias and unions. Basically it takes uses data from 3 tables. This dataset also has a alias column called ClientName that consists of either people's name or business name.In addition, the form also consist of a search field that allows user to enter the 'ClientName' to be searched (i.e. to search the alias column). So, my question is how can the alias column be searched (user can also enter % in the search field)Function QueryByService(ByVal searchClientNameText As String) As System.Data.DataSet If InStr(Trim(searchClientNameText), "%")>0 Then searchStatement = "WHERE ClientName LIKE '" & searchClientNameText & "'"Else searchStatement = "WHERE ClientName = @searchClientNameText"End If Dim queryString As String = "SELECT RTrim([People].[Given_Name])"& _"+ ' ' + RTrim([People].[Family_Name]) AS ClientName, [Event].[NumEvents],"& _"[Event].[Event_Ref]"& _"FROM [Event] INNER JOIN [People] ON [Event].[APP_Person_ID] = [People].[APP_Person_ID]"& _searchStatement + " "& _"UNION SELECT [Bus].[Organisation_Name],"& _"[Event].[NumEvents], [Event].[Event_Ref]"& _"FROM [Bus] INNER JOIN [Event] ON [Bus].[APP_Organisation_ID] = [Event].[APP_Organisation_ID] "& _searchStatement ..........End Function
i want to be able to do full text searches on a table that i have that already has data in it.. i created a new catalog, then started incremenetal population.. but nothing happened? will it only catalog the new data thats been entered into the table after i start incremental update? so since i have data in teh table already, would i need to do a full population first before i start incremenetal population?
The reason it is poorly designed is the table is used to hold questions and answers, all with a 1:1 relationship. Instead of having ID, ProductType, Question, Answer they have unfortunately adopted the approach of the above i.e:
id 1 thisID 3 parentid nuLL DESCRIPTION: this is a question
id 20 thisID 3_1 parentID 3 DESCRIPTION: this is the answer to the question above
So I am writing a sproc that does this using a temp table. I got this far:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author:Spencer -- ============================================= ALTER PROCEDURE [dbo].[GetFAQs] -- Add the parameters for the stored procedure here @ProductType varchar(255) AS BEGIN SET NOCOUNT ON; -- Insert statements for procedure here CREATE TABLE TEMP ( IDINT, thisIDvarchar(50) null, parentIDvarchar(50) null, Titlevarchar(255) null, DescriptionQvarchar(8000) null, DescriptionAvarchar(8000) null, ProductTypevarchar(255) null, )
SELECT ID, thisID, parentID, Title, DescriptionQ, DescriptionA, ProductType FROM A2Z WHERE ProductType = @ProductType AND parentID IS NULL END GO
This gets all my questions for that product type.
What I need to do is load the questions into my temp table and then run through the a2z table again gaining the answers to the questions (the parentid holds the question ID). The answers then will also get loaded into the temp table.
For those implementing encrypted columns, what is the recommended approach when allowing users to also do partial searches on encrypted data? (ie email or creditcard info where the tables contain millions of rows). I understand one cannot have the encryption without performance impact, but the searches can be 10 to 20 times as long as when the info is stored in normal char(20) columns. Just looking as a way to try and lessen the impact.
I got the following code to add a column in a matrix with a variance:
IIF(IsNothing(Previous(Sum(Fields!Amount.Value))) or Fields!year.Value=First(Fields!year.Value,"Category") or Previous(Sum(Fields!Amount.Value))=0,nothing, ( (Fields!Amount.Value) /Previous(sum(Fields!Amount.Value)) ) )
This code works fine, except that the first row of the matrix shows an #error
This happens with each matrix where I use this expression. A warning emerges:
rsruntimeerrorinexpression the value expression for the textrun Textbox43.Paragraphs[0].TextRuns[0]' contains an error.
Attempted to divide by zero.
The strange thing is that the part
Fields!year.Value=First(Fields!year.Value,"Category") should prevent an error and I expect it to show 'nothing'
An screenshot of the table. (each color is a different category. Each row stands for 2013, 2014, 2015)
As you can see, all other 2013 rows show a blank cell, except the first row.
I have a Matrix table that expands to the right when choosing an amount of months to be shown. Under this matrix I have to Charts. The two charts are situated together, that is no space between them, and to the left of the report.
Now, if I choose a lot of months, say three years the matrix diagram will be huge to the right. The problem I have is that the second diagram, the one on the right, moves to the right depending on how big the report gets, and this is not good at all. The two charts are supposed to be all the way to the left.
Hello. I hope to explain myself well - I want to make a matrix with two rows. Lats say my data is this: I hava a list of months and in every month I have a number of pepole and there age. How can I show this in a matrix? It need to be in a matrix since I need the columns to expand acording to the month but I don't know how to create two diffrent rows in my matrix.
I have a report thats fully functional. I just want to add a filter so that my "Visits" field only displays the Visits per day that are less then 6. When i try to filter out the matrix or the group, it tells me the datatypes are different . Something about int32. Its in a matrix, but i have seen this happen in a table too, so i guessing thats not the problem. I just want to be able to display the information for Sales Reps with less then 6 Visits. Any help, will be greatlly appreciated.
Adding more columns in a matrix report that don€™t belong to the columns drilldown dimensions€¦
That is, for example, having the following report:
Product Family
Product
Country City Number of units sold
Then I would add some ratios, that is, Units Sold/Months (sold per month) and other that is the average for Product Family (Units Sold/Number of Product Family), for putting an example€¦ some columns should be precalculated prior to the report so do not get into it, the real problem I don€™t see how to solve is adding one or two columns for showing these calculated column that doesn€™t depend on the column groups but they do for the rows groups€¦
Any guidance on that?
The only way I am seeing by now is to set it as two different reports, and that is not what my client wants€¦
I'd be really grateful for some help.I have successfully installed the SQL server on my xp home edition machine but everytime I try to connect through the Data Matrix i get the message 'unable to connect to database sever. Log in failed for user sa.' I am entering 'local host' for server 'sa' for user and my password but I cannot connect.I saw Harish Rangathans article about this where he suggested going to the Enterprise manager and then the properties box to fix the problem but my version of the server only has a server manager and no properties box to enter to fix the problem.Is there anyone out there with any ideas about how to fix this problem.
I have a matrix in my report that is based on an MDX query. I copy-paste the matrix and then see the two matrices in the report and all is fine.
I then insert a group into the second matrix. When I run the report the second matrix appears as I would expect, but the cells are blank in the first matrix. Sometime all the data cells are blank, but not the totals.
I tried a similair thing with a table. I added a table based on the same MDX dataset and the cells in the matrix were also then blanked out.
I made sure that the matrices were not sitting on top of each or even in the same space.
The goal here is to show the same dataset but with different groupings. I thought of doing drilldowns, drillthroughs, etc. but this is the way they want to see the data.
Ok, I am having a little trouble figuring out if this is possible...
At first it looked easy (It always does), but as I started messing with matrix groups, it seems that i cant be done in 1 matrix.
Here is the display I want:
Value 1 Value 2 Total
Period 1 Period 2 Var % Period 1 Period 2 Var % Period 1 Period 2 Var %
Row
Row
Row
Row
I was thinking of having 2 groups, and then add a column to the 2nd group to calculate the variance column, and have that total. I cannot seem to get it to do that.
This is the first time I have really pushed the matrix for more than the basics.
Is it possible to Add a cell in Matrix A to a Cell in Matrix B and output that value in Matrix C?
Question # 2:
Since I think the answer to #1 is NO, I'll ask this question.
I have a dataset which has duplicates which I can't seem to get rid of - I've tried the distinct keyword and it's a no go. Is there a way to SUM NON-DUPLICATE values in a matrix. I've seen the COUNTDISTINCT function, but I don't think that's going to help me.
I have a count (bookings.id) as nobookings in my query, which returns a count of all specific classes for a member. In the matrix i have sum(fields!nobookings.value) which adds the bookings by class type (1 tennis, 2 golf, 3 cricket). Now i need the total of all classes by pmember (6 in this case). Can srs do this?
I am working on a sport site, where i have to create a Calender Like thing where i have to show which sport is avaible in which month, we can have any numbers of sports as the club grow it can add 100s of different sport to itself, so i was not able to understand how to make a database for this kind of thing, can anyone please help
Games - Jan Feb Mar Apr May Jun Jul …..Dec Cricket Yes Yes Yes No No No No ..... Hockey No Yes Yes No No Yes Yes ..... Horse R Yes No No No Yes Yes Yes .....
I am trying to sort a matrix by column. I first tried adding an interactive sort to the column heading, but it either gave me a run-time error, or there was not response. I then tried adding sorting to the row groups, and that doesn't seem to work either. I feel I have done every combination of groupings and sort expressions. Has anyone been able to accomplish this? Thanks, Kris
Is matrix reports in SSRS similar to the crystal cross tab reports, if so, I am trying to create my first report using reporting services in matrix report format, can you please send me a good reference I can use to create my first report. Thanks!
Hi All,I have the table as followscolumn1 ---------------------------------column2-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------column3-------3 -1.149,3.571,17.978,4.706,8.28,-5.988,-5.65,7.273,10,-7.975,0.617,-9.497,5.917,-1.744,-7.027,29.371,0,2.878,-2.113,0,-16.471,-10.526,-2.062,-2.513,-0.995,-12.987,0.435,4.072,12.183,-3.902,-4.651,25,-7.027,8.824,-15,-3.846,-4.587,-7.234,-5.242,16.432,0.472,-0.61832692307692316 1.649,2.768,3.474,-0.746,-0.186,1.129,-1.801,-0.46,5.634,1.429,0.995,-1.131,-2.959,1.946,3.266,0.404,2.588,-0.31,0.938,-0.26,-1.333,-1.215,-0.554,-1.194,-2.304,-0.223,1.576,-1.932,2.425,-4.67,0.617,0.333,0.962,-1.59,-0.066,3.575,-1.018,3.357,0.372,3.627,-10.28217307692307723 7.042,1.429,11.111,-13.103,-3.974,-1.948,6.207,2.113,-0.699,0.704,1.429,0,-2.098,2.143,0,1.449,-1.429,-6.667,13.636,-8.966,1.399,2.143,-9.091,-3.75,-5.882,10.39,-5.521,-13.757,2.717,-2.128,2.174,0,-8.911,12.222,-10,3.093,-3,7.527,0,-2.105,2.151,-7,-0.99,0,-0.53159615384615425 0.542,0.63,0.549,-2.148,4.534,0.13,2.436,-10.432,0.599,4.854,4.871,2.519,-3.306,5.362,-4.716,0.837,11.487,-5.869,-2.523,8.187,-6.087,-8.232,1.061,-6.914,-5.7,-5.058,-4.202,13.982,-1.559,4.282,-1.868,5.414,-4.728,-2.737,-2.592,-8.441,-2.091,-5.341,-1.516,4.-0.194057692307692From The above result pane i want to get a covariance matrix fromcolumn2 and column3.Here column3 can be be taken as "Mean"please help .It's an urgent deliverable.Immediate response is highly appreciated.
