Yep, I know this will have been asked before (because I've seen it), but I can't for the life of me find a thread!
So I've inherited this database and I need to search the whole darn thing for a single word, returning something lovely, like the table or column the thing resides in.
Basically it's a code in one of 100+ code tables, all with inconsistant, unclear naming - YAY!
FYI, I'm using SQL Server 200 and the word I'm looking for is "Unpaid".
I have written a query to search for a string in an expression by the number of it's appearance. Script is like this:
DECLARE @Expression VARCHAR(8000) = 'abcd_e_fgh', @SearchString VARCHAR(10)= '_', @OccuranceNumber SMALLINT = 1 DECLARE @SearchIndex INT = 0, @SearchIndexPrevious INT = 0, @Sno INT = 0 WHILE @Sno < @OccuranceNumber BEGIN
[Code] .....
Here i'm trying to search "_" in expression "abcd_e_fgh" where it is appearing for first time. it gives me 5 correctly. Now when i change the @OccurenceNumber to 2 or 3, it gives correct values 7 and -1 respectively. However now when i change it to 4, it gives me 5. So when it's trying to check for fifth appearance of "_", it's not actually giving 0 or -1 but repeating the value 5.
I am a newbie to SSIS and looking for answer to a simple search/replace style question.
I am building a derived column and looking for some expression syntax that can search for several strings in one column and set a value in a new column.
A bit like a case statement:
CASE ProductLine WHEN 'R1' THEN 'Road' WHEN 'M1' THEN 'Mountain' WHEN 'T1' THEN 'Touring' WHEN 'S1' THEN 'Other sale items' ELSE 'Not for sale' END,
The twist is that 'R1','M1','T1','S1' could feature anywhere in the string that is ProductLine.
I am looking for some syntax to help me with a traditional search/replace style requirement. I am wanting to examine the contents of one column and populate another.
similar to this SQL case statement
CASE ProductLine WHEN 'R1' THEN 'Road' WHEN 'M1' THEN 'Mountain' WHEN 'T1' THEN 'Touring' WHEN 'S2' THEN 'Other sale items' ELSE 'Not for sale' END,
the twist is that R1, M1 etc. can appear anywhere in the ProductLine column.
Can anyone tell me how to search an SQL database for a given key word in a textbox? I basically have a database that has a qualifications column and this column needs to be searched for the data given in the textbox. Which is the best method to search for the data? Is it a simple SQL query or an XML based search engine type? Can anyone give any suggestions regarding this? If XML is efficient then how do I use it to query my database, as I'm pretty new in XML based searching.Thanks
hi there, i am doing a school project and i need to have this search engine that will search the data that i have stored inside the database and display the results out can anyone help? thanks
Error 3 Error loading MLS_AZ_PHX.dtsx: The result of the expression ""C:\sql_working_directory\MLS\AZ\Phoenix\Docs\Armls_Schema Updated 020107.xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. c:documents and settingsviewmastermy documentsvisual studio 2005projectsm l sMLS_AZ_PHX.dtsx 1 1
Directly using C:sql_working_directoryMLSAZPhoenixDocsArmls_Schema Updated 020107.xls as connectionString works
However - I'm trying to deploy the package - and trying to use expression: @[User::DIR_WORKING] + "\Docs\Armls_Schema Updated 020107.xls" which causes the same error to occur
(Same error with other Excel source also: Error 5 Error loading MLS_AZ_PHX.dtsx: The result of the expression "@[User::DIR_WORKING] + "\Docs\Armls_SchoolCodesJuly06.xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. c:documents and settingsviewmastermy documentsvisual studio 2005projectsm l sMLS_AZ_PHX.dtsx 1 1 )
I have created 1 report with 2 datasets. This report is attached to the 1st dataset.For example,1st one is "Smallappliances", 2nd is "Largeappliances".
I created a tablix and, the 1st column extracts Total sales per Sales person between 2 dates from 1st dataset (Small appliances). I used running values expression and it works fine.
Now, I would like to add another column that extracts Total sales per sales person between 2 dates from 2nd dataset (Large appliances). I am aware that I need to use Lookup expression and it is giving me the single sales value rather than the total sales values. So, I wanted to use RunningValue expression within lookup table to get total sales for large appliances.
This is the lookup expression that I added for the 2nd column.
I get this error when I preview the report.An error occurred during local report processing.The definition of the report is invalid.An unexpected error occurred in report processing.
I'm trying to set the DestinationDatabaseFiles property programmatically through an expression. I keep getting the error:
SSIS package "Package1.dtsx" starting.
SSIS breakpoint 'Break when the container receives the OnPreExecute event' at executable 'Transfer Database Task' has been hit
Error: 0xC002929C at Transfer Database Task, Transfer Database Task: The transfer file information "","","" is missing the filename.
Error: 0xC002929D at Transfer Database Task, Transfer Database Task: The transfer file information "","","" is missing the folder part.
Error: 0xC0024107 at Transfer Database Task: There were errors during task validation.
Warning: 0x80019002 at Package1: The Execution method succeeded, but the number of errors raised (4) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "Package1.dtsx" finished: Failure.
I'm setting the property to a variable, copyDestFiles, which I've tried numerous ways to set, the latest being in a script.
When I view the variable at runtime it looks like this: "Prod2.mdf","D:\Program Files\Microsoft SQL Server\Data","";"Prod_log2.ldf" ,"D:\Program Files\Microsoft SQL Server\Data",""
How does one set this property using an expression?
I need to translate a user€™s regional setting into one of our own language codes before I send it through as a filter to the model query. If our language codes were the same, the filter would look like this in the report filter - Language Code = GetUserCulture() Which translates to this in the database query (for us english) - table.language_code = 'EN-us' And of course I need it to look like this - table.language_code = 'ENG'
I would like the logic to be globally available to all report writers (ie not forcing each report writer to have an iif or case stataement). I was thinking custom assemblies or maybe a database function, but at this level of the filter, I cannot seem to figure out how to embed a database function call to apply to the filter criteria like this Language Code = dbo.ConvertFcnIWrote(GetUserCulture()) Or how I would access the custom assembly in the filter expression.
Do you have a recommended implementation for this situation?
Hi I would like to know whether any tools are there to search in a database. Ex. i am using sql server2005 and in my db, more than 1000 tables r there. i want to search for a perticular column. This search should be on tables, sps, functions, triggers.....etc.
If anybody aware of any tool for this or any code in dotnet to develop such tool, pls let me know.
I design table which has column "keywords" for keep keywords seperated by comma (,). If user pass one keyword, how can I know that the keyword user select is exactly match with any keyword in field. I mean I just want SQL return only rows that user's keyword is exactly match with at least one keyword in "keywords" column.
--Author:=> Kapil Choudhary Jaipur (India) --Motive:- Search A Text value In A DataBase With The Table Name.
create procedure [search_value] @fstr nvarchar(128) with encryption as
set nocount on declare @rc int --Counter Variable For The Cursor 1. declare @rcc int --Counter Variable For The Cursor 2. declare @tc nvarchar(128)--TABLE_CATALOG. declare @ts nvarchar(128)--TABLE_SCHEMA. declare @tn nvarchar(128)--TABLE_NAME. declare @tt nvarchar(128)--TABLE_TYPE. declare @Ctn nvarchar(128)--Column Table Name. declare @Cts nvarchar(128)--Column Table Schema. declare @Ccn nvarchar(128)--Column Column Name. declare @Cdt nvarchar(128)--Column Data Type. declare @currow1 int--Total Row Count For The Cursor 1. declare @currow2 int--Total Row Count For The Cursor 2. declare @str nvarchar(128) --Dynamically Created Query String. declare @strresult nvarchar(128)--Value Stored From Temp Table. declare @findwhat nvarchar(128) -- String To Be Searched. set @rc=1 create table #temp (colval nvarchar(128)) create table #myresult(Table_Name nvarchar(128),Column_Name nvarchar(128),Column_Value nvarchar(128)) declare mycur1 cursor static for select TABLE_CATALOG,TABLE_SCHEMA ,TABLE_NAME,TABLE_TYPE from INFORMATION_SCHEMA.TABLES where TABLE_TYPE='BASE TABLE' open mycur1 set @currow1=@@cursor_rows --print 'table rows = '+str(@currow1) while @rc<=@currow1 begin fetch next from mycur1 into @tc,@ts,@tn,@tt declare mycolcur cursor static for select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,DATA_TYPE from information_schema.columns where TABLE_CATALOG=@tc and TABLE_SCHEMA=@ts and TABLE_NAME=@tn and data_type in('char','varchar','nchar','nvarchar') open mycolcur set @findwhat='wa'--Your Search Value Here set @rcc=1 set @currow2=@@cursor_rows --print 'table col = '+str(@currow2) --print 'Table name'+' '+'owener'+' '+'Column Name' --print '-----------------------------------------------' while @rcc<=@currow2 begin fetch next from mycolcur into @Cts,@Ctn,@Ccn,@Cdt -- print @ctn+' '+@cts+' '+@ccn set @rcc=@rcc+1 set @str='select '+quotename(@ccn) + ' from ' +quotename(@cts)+'.'+ quotename(@ctn) + ' where ' + quotename(@ccn) +' like '+char(39)+@findwhat+char(39) set @str='insert into #temp(colval) '+@str --print @str exec (@str) if exists(select * from #temp) begin select @strresult= colval from #temp --print 'colval =========>>>>> ' + @strresult delete from #temp insert into #myresult(Table_Name,Column_Name,Column_Value) values(@ctn,@ccn,@strresult) end end close mycolcur deallocate mycolcur
set @rc=@rc+1 end --print @@cursor_rows select * from #myresult close mycur1 deallocate mycur1 drop table #temp drop table #myresult set nocount off Go
well i am using vb.net and would like to know how to search a specific table in the databasefor egif table1 exists in database then drop table1end if ...something like that
Hi guys, Now I upload the PDF file and store it in the database as binary. But now I need to search the text in the PDF file. Is there any way I can search the content in the database ? Anyone has any idea about it ?Thanks!Regards,Sam
I'm trying to make this piece of code work, I get all of it except how I get the data from the database, what I wish to do is. On page load I wish to take the value of a querystring in the page URL called ProductID and search a database to see if it exists in a specifical table and column, if it exists I wish to make a button not visible. the code I am using is:
#############
protected void Page_Load(object sender, EventArgs e) { // Get the querystring value String inQueryString = Request.QueryString["ProductID"];
// Get the data from the database, using the above value DataTable data = ??
The datasource I want to connect to is SqlDataSource1, and the table I want to search is “Reviews�. I want to search in the column “ProductID� and search for the ProductID from the URL query string above.
// Did we find a product in the database? if (data != null && data.Rows.count > 0) { // Code to display the product's information // We found a product, so we need to hide a button Button5.Visible = false; }
}
##########################
Please can somebody fill in the missing bit for me so i know how it should be, i've searched the quickstart tutorial on the menu above and I still don't understand it, as it doesn't show anything like what I want to do, and I have looked at numerous websites about the matter. If you don't have time that is okay, but If somebody does have the time to show me what I need, I would be very greatfull.
Hello I have a Database which contains like 1000 Tables. I am not the designer of that DB.So I need to in which table and which column that string exists. IS there a DBWIDE String search possible? Thanks and Regards
I have a huge problem that ive been sitting with for awhile.
I have a web page with 4 dropdownlist boxes on it
Gender: Race: Field: Location:
Now a user can search a SQL db from selecting values from these 4 dropdownlist boxes which are equal to values in the db obviously.
when they have done that and pressed submit a Gridview is populated with the people corresponding details and the user is able to view each row seperately.
Now the problem i am having is that when i havent used all 4 selections for eg
Gender: null (no value selected) Race: Black Field: Accounting Location: Los Angeles
then no information is returned from the db in the gridview. im using a sqldatasource to populate the gridview and here is the query string that i am using :
Code:
sql SELECT [title], [gender], [initials], [name], [surname], [birthdate], [postaladdress], [suburb], [city], [zipcode], [criminalrecord], [drivers], [maritalstatus], [dependants], [citizenship], [province], [contactref], [hometel], [cell], [jobtitle], [relocate], [emmigrate], [email], [worktel], [enddate], [startdate], [FIELD], [education], [company], [positionheld], [jobdescription], [contactperson], [contacttel], [startdate2], [contactperson3], [jobdescription3], [positionheld3], [company3], [enddate3], [startdate3], [contacttel3], [other] FROM [cvinformation] WHERE (([race] = CASE WHEN @race IS NOT NULL THEN @race ELSE [race] END) AND ([province] = CASE WHEN @province IS NOT NULL THEN @province ELSE [province] END) AND ([education] = CASE WHEN @education IS NOT NULL THEN @education ELSE [education] END))
what i want to do is whether the user doesnt choose any selection and leaves the choice null that ALL the information in the table of the db be shown and even if they only choose 2 values and leave the others null then it still brings back the information from the criteria chosen..
Hi folks, Whts up........??? M back, after a long gap. I have come across with a major issue. And u know wht th issue is.........??? It is about th DATABASE SEARCH TOOL. I have a database of around 30 tables. Now I wud like to have aa search engine on my .asp page. There will be a text box on th page and one submit button. After typing some text in th text area n submitting th page, my package sud check tht perticular text in all th COLUMNS of all th TABLES, n whrevr it gets a match (exactly same, or by speech recognition), it sud through th links on th next page.
Nw i wud like u guys to take this problem, at th earliest n come up with a up to th mark solution.
Hi, I have a sql server database with 250 tables in it, i wanted to find one particular value from one of the tables in the database. How to find that appreciate your help.. Thanks
Hi I was wondering if anyone can help me out here. I want to search a word in the database which has more than 80 tables is it possible to search all the tables at once for that word.
Hi,I would like to use keywords inside a record to search for - like asearchengine.How can I design the table? Right now I would store data in Memo-Column in aformat like ";apple;banana;lemon;". I would use a INSTR(ring)-function - butI wonder if the performance is still OK then - even when I index the field.Are there maybe even better ways?Thanx in advance.Greg
Hi, i want user to type a string in textbox, and i will search, if textbox.text exists in my SQL database table or not. Below is the code i'm using :---------------
string txtname = (txtEmpName.Text).Trim(); sQuery = "select * from tbUser where UserName='_username'"; sQuery = sQuery.Replace("_username", txtname); SqlConnection objConnection = new SqlConnection(source); objDataAdapter = new SqlDataAdapter(sQuery,objConnection); // DataTable dtTable = new DataTable(); if (objConnection.State == ConnectionState.Closed) objConnection.Open(); objDataSet = new DataSet(); objDataAdapter.Fill(objDataSet); // objDataAdapter.Fill(dtTable);
or i want to search if "txtname" exists in "tbUser" table.
Does anyone know how i go about writing a search query using paramaters, ive been using this as my template: Select Distinct Recipedata.Name, Ingredients.Ingredient, Directions.Directions from Recipedata,Ingredients,Directions Where Ingredients.Ingredient Like '%Chicken%' and
RecipeData.RecipeID = Ingredients.RecipeID and Ingredients.RecipeID = Directions.RecipeID;
Problem is now that i want to use paramters instead of hard coded values as this is for a user. How do i go about this? This database has a VB2k5 front end for the desktop and PDA(and no the target PDA is not web enabled so web servcies arent available to me).
I want to search for a keyword in the database. The database has approximately 30 tables and the amount of data in tables is very large. Most of the tables hold 25000 rows. The search procedure for searching a keyword that i want to use is as below. When i executed the stored proc it took 20 minutes. What i want to know is full-text search a better option than this or is there any other way out.
I'm trying to find some attributes within a database I'm working with. I'm working with SQL Server 2000, and I'm trying to find table which have a certain attribute in them. for example, If I want to see tables from my database which have an attribute called 'home_address', what would the query be? Or is there an inbuilt function in SQL Server 2000 which allows me to search this?
Hi,I would like to have a full-text search from a sql server database in my aspx page. I have tried to do it by creating a Dataset with a DataTable where there is a query like: SELECT title, yearFROM ProjectsWHERE title like N'%@Search%' Problem is with the like operator - the visual studio does not reconize the @Search as a parameter, as it reconnizes it in the query below (which works fine by the way): SELECT Projects.*, theme AS Expr1FROM ProjectsWHERE (theme = @theme) Can anybody help me how to deal with this problem, which is the best way to have a full text search? Cheers, ArtoV
