Hi All,
i have some views in my database, and these views are having some columns,i want to know particular column name to be there in particular view.
For example ,just like functionality of sp_search_code 'Keyword'.
like this i want to search in views .
or else please let me know sp_search_code 'Keyword'. was used for views also.
Thanks and Regards,
G.JaganMohanrao.
I use SQL Server 2005 Developer. I failed to search the records by using SQLCommand Paramater. Please find the code below.
However, when I hardcode like LIKE '%sudoku%' , it works. Could aynone help?
Thanks,
Andy.private bool searchProducts(string keywords)
{
.......
command.CommandText = "SELECT Product.ProductID, Product.Name, Product.Image, ProductPrice.Price, ISNULL(SpecialProduct.PromoDiscount,0) as PromoDiscount FROM Product INNER JOIN ProductPrice ON Product.ProductID = ProductPrice.ProductID LEFT OUTER JOIN SpecialProduct ON Product.ProductID = SpecialProduct.ProductID WHERE Product.Name LIKE '%@keywords%' ";
command.Parameters.Add("@keywords", SqlDbType.VarChar, 100);
command.Parameters["@keywords"].Value = keywords;
.............
}
I'm trying to create a query that searches an entire database for keywords inside of the columns for each table within the database. For instance my tables have 2 columns one named ID and the other Permission, I'd like it be able to return all the lines that are associated with that keyword. So if I search "Schedule" it returns all the lines containing that word in it within that database.
View 6 Replies View RelatedI recently migrated a database from server A to server B. The backup jobs I am trying to build on server B are failing because of the following error:
--------------
[Microsoft SQL-DMO] Error 21776: [SQL-DMO]The name 'WinDat' was not found in the Databases collection. If the name is a qualified name, use [] to separate various parts of the name, and try again.
-------------
How do I add this database to the databases collection so it will be recognized?
Thanks in advance for your time and help.
why doesn't this code always work?
<code>
Dim conn as serverconnection=New serverconnection(".SQLEXPRESS")
Dim srv as new server(conn)
Dim db as database
for each db in srv.databases
cmbDBfilenames.items.add(db.Name.tostring)
Next
</code)
If the databases are located in the applicatio path , it doesn't pick them up
hi,
this is sanjeev,
i have SSIS package, using my c# program i want to add one execute package task to this package's sequence container.
it is creating the new package with out any probelm. but when i opened the package and try to move the newly created exeute package task it is giving the following error.
the element cannot be found in a collection. this error happens when you try to retrieve an element from a collection on a container during the execution of the package
this is my code
{
Package pkg = new Package();
string str = (string)entry.Key;
pkg.Name = str;
alEntity = (ArrayList)entry.Value;
ConnectionManager conMgr;
Executable chPackage;
TaskHost executePackageTask;
Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
//string PackagePath = @"c:Genesis.dtsx";
//p = app.LoadPackage(PackagePath, null);
p = new Package();
p.LoadFromXML(parentPackageBody, null);
p.Name = str;
//Sequence seqContainer;
IDTSSequence seqContainer;
//seqContainer = (Sequence)p.Executables["Extract Genesis Data"];
seqContainer = ((Sequence)p.Executables[0]);
string packageLocation = @"Geneva Packages";
conMgr = p.Connections["SQLChildPackagesConnectionString"];
foreach (string val in alEntity)
{
if (seqContainer.Executables.Contains("Load_" + val) == false)
{
chPackage = seqContainer.Executables.Add("STOCK:ExecutePackageTask");
executePackageTask = (TaskHost)chPackage;
executePackageTask.Name = "Load_" + val;
executePackageTask.Description = "Execute Package Task";
executePackageTask.Properties["Connection"].SetValue(executePackageTask, conMgr.Name);
executePackageTask.Properties["PackageName"].SetValue(executePackageTask, packageLocation + ddlApplication.SelectedItem.Text + @"" + executePackageTask.Name);
}
}
app.SaveToXml(Server.MapPath("../SynchronizeScript/Packages/" + ddlApplication.SelectedItem.Text + @"") + str + ".dtsx", p, null);
}
please let me know what is the wrong in my code.
thanks in advance.
regards
sanjeev bolllina
sanjay.bollina@gmail.com
I Enabled Data Collection on one of the server and planned to make it as Centralised Management Data Warehouse I configured data collection on it and can view reports. Next, I went to other server and configured "Set up data collection" to use my first instance as the centralised Database. But the issue is I can only see reports of first server. Am I missing something here.
I did exactly as explained in this video [URL] .....
Hi All, Please help me how to proceed from this situation, my requirement is to present (load) data in excel file and need to access (update/delete) it, the data which is in remote server; I want to connect database using odbc connection through ASP.NET. Please suggest me how to continue.
Thanks to all in Advance.
kolags@hotmail.com
I have created a table but i forget in which database. I know the table name but i dont know the database name..
I have to filter my table from all database ... how can i do and find....
I have a comments field in database table which holds all html data, like bold chars and underline and with fonts etc.
Is it possible to show that data in the report or do i have to extract the data only by excluding all html / font tags etc.
Please help thank you very much.
Hi,
This is the error that I am getting after an unsuccessful
database attachment.
Error 21776: [SQL-DMO] The name 'XXXX' was not found in the Database
collection. If the name is a qualified name, use [] to separate various parts
of the name, and try again.
Any ideas how can I delete that database?
Thanks
I have a Microsoft Cluster running on Server 2003 Entrprise. SQL 2000 8.00.2039 (SP4). 5gb physical memory installed.
With the databases online we run a test failover from the Cluster administrator. It takes about 30-40 seconds and completes without generating any Server Event log errors nor SQL log errors. Everything looks good from an administrative stand point.
However, when we test with running a series of queries to the databases, then failover, we notice that it can take up to 3 or 4 minutes before some of the databases will respond. Connections are not refused, they just sit there.
How can we troubleshoot this or does anyone have a similiar experience with this scenario?
I have an excel sheet containing one column (ID_NO) with 400K rows. I have a database from where I have to fetch some other columns from a Netezza database. Initially I tried hardcoding all the 400K rows in the query that I wrote using filter WHERE ID IN ('1212','2334'). But after pasting all the 400K rows the query is running indefinitely.
I have imported all the ID in a SQL table (MY_LIST table). I used a DFT, and selected ODBC source, and selected my netezza server. Then in the 'Data access mode' I selected the SQL command from the dropdown.I pasted the same query that I wrote in Netezza. Is there any way to pull only for those records that I have pulled in my SQL table (MY_LIST) ?
i currently have a function and a storedpro in my sql database they are:CREATE PROCEDURE SearchCatalog (@PageNumber tinyint,@ProductsOnPage tinyint,@HowManyResults smallint OUTPUT,@AllWords bit,@Word1 varchar(15) = NULL,@Word2 varchar(15) = NULL,@Word3 varchar(15) = NULL,@Word4 varchar(15) = NULL,@Word5 varchar(15) = NULL)AS
/* Create the temporary table that will contain the search results */CREATE TABLE #SearchedProducts(RowNumber SMALLINT NOT NULL IDENTITY(1,1), ProductID INT, Name VARCHAR(50), Description VARCHAR(1000), Price MONEY, ImagePath VARCHAR(50), Rank INT, ImageALT VARCHAR(100), Artist VARCHAR(50))
/* Populate #SearchedProducts for an any-words search */IF @AllWords = 0 INSERT INTO #SearchedProducts (ProductID, Name, Description, Price, ImagePath, ImageALT, Artist, Rank) SELECT Product.ProductID, Product.Name, Product.Description, Product.Price, Product.ImagePath, Product.ImageALT, Artist.ArtistName, 3*dbo.WordCount(@Word1, Name)+dbo.WordCount(@Word1, Description)+ 3*dbo.WordCount(@Word2, Name)+dbo.WordCount(@Word2, Description)+ 3*dbo.WordCount(@Word3, Name)+dbo.WordCount(@Word3, Description)+ 3*dbo.WordCount(@Word4, Name)+dbo.WordCount(@Word4, Description)+ 3*dbo.WordCount(@Word5, Name)+dbo.WordCount(@Word5, Description) AS TotalRank FROM Product INNER JOIN (Artist INNER JOIN AlbumSingleDetails ON Artist.ArtistID = AlbumSingleDetails.ArtistID) ON Product.ProductID = AlbumSingleDetails.ProductID ORDER BY TotalRank DESC
/* Populate #SearchedProducts for an all-words search */IF @AllWords = 1 INSERT INTO #SearchedProducts (ProductID, Name, Description, Price, ImagePath, ImageALT, Artist, Rank) SELECT Product.ProductID, Product.Name, Product.Description, Product.Price, Product.ImagePath, Product.ImageALT, Artist.ArtistName, (3*dbo.WordCount(@Word1, Name)+dbo.WordCount(@Word1, Description)) * CASE WHEN @Word2 IS NULL THEN 1 ELSE 3*dbo.WordCount(@Word2, Name)+dbo.WordCount(@Word2, Description) END * CASE WHEN @Word3 IS NULL THEN 1 ELSE 3*dbo.WordCount(@Word3, Name)+dbo.WordCount(@Word3, Description) END * CASE WHEN @Word4 IS NULL THEN 1 ELSE 3*dbo.WordCount(@Word4, Name)+dbo.WordCount(@Word4, Description) END * CASE WHEN @Word5 IS NULL THEN 1 ELSE 3*dbo.WordCount(@Word5, Name)+dbo.WordCount(@Word5, Description) END AS TotalRank FROM Product INNER JOIN (Artist INNER JOIN AlbumSingleDetails ON Artist.ArtistID = AlbumSingleDetails.ArtistID) ON Product.ProductID = AlbumSingleDetails.ProductID ORDER BY TotalRank DESC
/* Save the number of searched products in an output variable */SELECT @HowManyResults=COUNT(*) FROM #SearchedProducts WHERE Rank>0
/* Send back the requested products */SELECT ProductID, Name, Description, Price, ImagePath, ImageALT, Artist, RankFROM #SearchedProductsWHERE Rank > 0 AND RowNumber BETWEEN (@PageNumber-1) * @ProductsOnPage + 1 AND @PageNumber * @ProductsOnPageORDER BY Rank DESCand:CREATE FUNCTION dbo.WordCount(@Word VARCHAR(20),@Phrase VARCHAR(1000))RETURNS SMALLINTASBEGIN
/* If @Word or @Phrase is NULL the function returns 0 */IF @Word IS NULL OR @Phrase IS NULL RETURN 0
/* Calculate and store the SOUNDEX value of the word */DECLARE @SoundexWord CHAR(4)SELECT @SoundexWord = SOUNDEX(@Word)
/* Eliminate bogus characters from phrase */SELECT @Phrase = REPLACE(@Phrase, ',', ' ')SELECT @Phrase = REPLACE(@Phrase, '.', ' ')SELECT @Phrase = REPLACE(@Phrase, '!', ' ')SELECT @Phrase = REPLACE(@Phrase, '?', ' ')SELECT @Phrase = REPLACE(@Phrase, ';', ' ')SELECT @Phrase = REPLACE(@Phrase, '-', ' ')
/* Necesdbory because LEN doesn't calculate trailing spaces */SELECT @Phrase = RTRIM(@Phrase)
/* Check every word in the phrase */DECLARE @NextSpacePos SMALLINTDECLARE @ExtractedWord VARCHAR(20)DECLARE @Matches SMALLINT
SELECT @Matches = 0
WHILE LEN(@Phrase)>0 BEGIN SELECT @NextSpacePos = CHARINDEX(' ', @Phrase) IF @NextSpacePos = 0 BEGIN SELECT @ExtractedWord = @Phrase SELECT @Phrase='' END ELSE BEGIN SELECT @ExtractedWord = LEFT(@Phrase, @NextSpacePos-1) SELECT @Phrase = RIGHT(@Phrase, LEN(@Phrase)-@NextSpacePos) END
IF @SoundexWord = SOUNDEX(@ExtractedWord) SELECT @Matches = @Matches + 1 END
/* Return the number of occurences of @Word in @Phrase */RETURN @MatchesENDmy database has many table but product is linkinked to albumsingledetails with productid in the albumsingledetails table, the the albumsingledetails table has the artistid in it which links to the artist table. I have tried searching for an artist but it does not find them!! can anyone see where i have gone wrong?
Hi,
This is the first database I have ever created, so please bear with me.
I've created a simple database with 1 column and about 80,000 rows. In each row is a word (basically a dictionary without definitions).
I have written a query which works, and is, as follows (you'll notice that i'm not the most original of people)
SELECT
word
FROM dbo.words
WHERE word= 'hello'
This finds the word hello.
In excel I have a row with 25 letters and then a column with every single combination of letters from 3 to 10 lettered words. (It makes sense to me!)
This comes back with a lot of possibilities (thousands), but is great in the sense that when I change any of the 25 letters the entire column automatically updates.
What I am trying to do is then take all of these possibilities and compare them against the dictionary.
I have written a line in excel which automatically creates a cell a bit like this, for the first couple of thousand possibilities:
WHERE word= 'abc' or word= 'fgm' or word= 'klm' or word= 'pqr' or word= 'uvw' or word= 'bcd' or word= 'ghi' or word= 'lmn' or word= 'qrs' or word= 'vwx'
I then whack this into the query from above and off it goes. The only problem is that the search takes ages, and because of limitations in excel I can't put more than a thousand or so words in the cell.
I am certain there is a faster way of searching through all the possibilities, any help would be much appreciated.
Thanks in advance
Im trying to do a database query based on user input from a text field. I pulled apart the string that was
entered into the search form and stored it in textArray. The problem I
am having is when I include commmon words such as the, near, view and so
on into the search field I end up with zero results. when I only search
for less common words such as rocky and argentina for example the search
works fine. at first I thought that maybe it was generating too many
results and was screwing up but when i search for rocky it works and
when i search for near rocky it doesnt.
I realize the code might not be the most efficient but here it is...
@searches_pages, @searches = paginate(:searches, :per_page => 10,
:conditions => getSearch(textArray,params[:country]))
def getSearch(textArray,selectedCountry )
result = []
string = ""
if selectedCountry != "Optional Field"
string << "country = (?) and "
end
textArray.each do |x|
if textArray[textArray.length - 1] == x
string << "match(country,caption, keywords, notes) against
(?) "
else
string << "match(country, caption, keywords, notes) against
(?) and "
end
end
result << string
if selectedCountry != "Optional Field"
result << selectedCountry
end
textArray.each do |x|
result << x
end
result
end
Im not sure if i supplied enough information but I am trying to finish
this project soon so any type of responses would help. Also, if there
is an easier way to do an sql search based off of what is entered into
the search field please let me know. The reason I did this is because I
wasn't sure how many words the user would be entering into the field.
And without knowing this I could not hard code the conditions => so I
wrote a helper method.
OK I have a search page and the query that is being send from the search box is
"SELECT * FROM [problems] WHERE ((problemBody LIKE '%' + @search_id + '%')OR @search_id IS NULL)"
Now say I have in the column for problemBody "Search the database" If i Type in the search field search the, or the database, or data, ot search, or even just s it will bring back records, But if I do not use exact keywords such as "search database" it will not bring back anything. How do I make it search all the keywords used?? like a normal search engine.
Thanks
I am finishing up my senior project application but I wanted to include a search function that would search all the tables of the database and look for matching text that is input by the user. I am not really looking for code or anything just some pointers in the right direction. I was thinking that I would have to create a view that is populated by a sub-query. My problem is how do I output the results of a search of every relevent table in the database when they all have very different column names and data types? Im guessing column aliases are involved somehow but I am not really sure where I should start. Any suggestions would be appreciated.
hi there,i have a textbox in my page and a button,when the button is clicked the application will search for the text written in the textbox in my database which is sql server, it works fine in my system but when i upload my website in the web it doesn't work correctly i mean that it dosent find all the matches, why this happens? is it possible that this problem occur because of the different fonts which is used in sql server and the font is used in my application?
thanks for help
Whats the easiest way to search for a keyword in all the tables present in the database.
Or searching in 5-6 tables.
Thanks,
AzamSharp
I've been given read access to a database and I also am looking at a GUI which draws data from the database. I am trying to map the results I see from the GUI to find the columns that the data is in... So.. big database, takes a long time to search the entire thing so I try to narrow it down by the following:
Code:
select * from information_schema.columns where table_schema = 'db19' and table_catalog = 'masterdb' and
table_name in(select table_name from information_schema.tables where table_type = 'base table' and table_catalog = 'masterdb' and table_schema = 'db19' and data_type in ('text' and 'varchar'))
This essentially gives me a list of tables and columns whos data type is either text or varchar. Once I have this list... I then run the following on each:
Code:
select top 1 [col_name] from [table_name] where [col_name] like ' (here is the value I want to search) '
So this runs through each table, looking to see if a value exists and if so, prints the result.
I am then left with a much much smaller list that I can manually look through and find the one I am looking to specifically find.
Is it possible to do this running only one query... where the output gives me all the columns in with a specific data type that contain a value I enter Anything else to make this more efficient
I am aware that there are data mining programs that could probably do this however I only have read access on the database which often causes a problem. The application I am using is "Aqua Data Studio" ....
Is there any way to do a complete database search in SQL server? For instance, if I have a criteria "DBFORUMS", I would like to scan through all user tables in my database to get all records with the word "DBFORUMS" stored, just like want we are doing in "Quick Search" in dbforums site.
Any ideas?
Thanks in advance.
Hi friends
Suppose i have a table of 100 cols and 10000 rows i want to search a particular field called 'Newyork' . I dont no what the col is ?
Can anyone tell me how can i search that
Vicky
Hi, this is my first post on these forums, so please excuse me if this topic has already been covered.
I'm currently working in a power station for student vacation work placement. I need to export data from a database that gets it's data from machinery and inputs out in the plant. The machines that provide this input put it into a database, and I need to find the relevant data to export.
My problem is that, in some cases, the sample data that i'm given may be under different field names, in a completely unrelated table. I was looking for a way to search the entire database (250+ tables) for a certain string, so I can find where it is in the database, and run queries on the table it originates from. For example:
My sample data shows me that I have an object with the ID Y03A3DEA_TH1. I know this ID will occur somewhere else in the database, but i'm just not sure where.
If anyone knows of any way that I can search the entire database for specific data, either using tools in MS SQL 2000, or 3rd party apps, i would greatly appreciate their help.
Thanks a lot,
Jack Smith
Dear Sir,
I am building a website some what like B2B portal using asp.net and access database. I want to provide a search facility to the user through which they can search products in our database.
Can you provide me a strong SQL Query for that. Or is there any other way of doing that.
Please help me.
Thanking You
Suraj
Maybe a totally newbie question - I need to find some text in SQL database, I have no idea in which table it may be. Can I do it through SQL Management Studio 2005 or do I need some other utility? What would you suggest then?
Thanks.
Hi,
I am building a website in ASP.net C# for a university project, and would like to search a table (Member) for a field (UserName) using a session variable Session["sUserName"]. If that field is null, then I would like to insert that session variable into the field to start to create a new user. However, I am getting errors saying that I am using invalid expression terms. My code is;
//Create the Command, passing in the SQL statement and the ConnectionString queryString = "SELECT UserName FROM Member WHERE (UserName = @myUsername); ";
SqlCommand cmd = new SqlCommand(queryString, sqlConn);cmd.Parameters.Add(new SqlParameter("@myUsername", Convert.ToString(Session["sUserName"])));
//If UserName is null, display confirmation, else display errorif (UserName == null) ;
{UserNameCheckLabel.Text = "Username okay";
String queryString = "INSERT INTO Member (UserName) VALUES(@myUsername); ";SqlCommand cmd = new SqlCommand(queryString, sqlConn); cmd.Parameters.Add(new SqlParameter("@myUsername", Convert.ToString(Session["sUserName"])));
}else;
{UserNameCheckLabel.Text = "That username is in use";
}
I have a feeling I should be checking the database for the UserName, but I'm not sure whether to put this in the SELECT statement part or as a method... I would be most grateful for any advice!
Many thanks,
Chima
hihere is a problem:i have a databes with many, many tablesthe problem is that i dont know where 'abcd' string is (but it is for surein one of that table)is there any SELECT that could help me with finding this string in database?--greets
View 1 Replies View RelatedHi!
I found a problem while retrieving/searching/filtering Myanmar Text with a Select Statement, In a Myanmar Text table there in a column called HeadWords.
It's Sample Data
HWID,HeadWords
1,|က|
2,|ကာ|
3,|ကိ|
4,|ကီ|
5,|ကဲ|
I want to make the following search:
SELECT * FROM TableName WHERE HeadWords LIKE '%|က|%' this should give me all entries that have a "|က|" *ANY* place in the HeadWords column. Right?
However, it gives me unproper results. you may see last two records have 2 characters between pipe(|).
HWID,HeadWords
1,|က|
3,|ကိ|
5,|ကဲ|
Since the wildcard character % means no or all characters it should work. And I've tried pipe, comma, forward slash and back slash.
The problem only seems to occur when the wildcard character is used for the any part of character. Let me know alternative way to search that matters. I've tried in MSAccess. There are same problem like MSSQL.
It's any problem in searching support for National Characters (UTF8). I've tried in OpenOffice Database with those data.
It's work fine.
If you not see Myanmar characters, please download fonts from http://www.fontmm.com/font_downloads.htm
Does anybody have an explanation to this, please let me know.
Thanks in advance!
Ngwe Tun
Database design is well covered in undergrad CS. But does anyone know ofa textbook that deals with the design of database servers (and probablytouches on databases themselves), and perhaps deals with interpreting SQLinquiries?The motivation for this is that I wish to write a database server for aproprietaryoperating system (it resembles POSIX, but is not completely compliant)for an embedded processor system that exchanges queries from clients alsoemploying embedded processors. Stored data is all in one place.I'm a little familiar with the MySQL source code, but I don'twish to replicate something that complex. The sort of investmentI'm talking about should involve no more than three man, er, person-monthsby an experienced C++ programmer with plenty of experience in systemsand database programming for both Microsoft and Linux platforms.
View 1 Replies View RelatedFellow database developers,I would like to draw on your experience with views. I have a databasethat includes many views. Sometimes, views contains other views, andthose views in turn may contain views. In fact, I have some views inmy database that are a product of nested views of up to 6 levels deep!The reason we did this was.1. Object-oriented in nature. Makes it easy to work with them.2. Changing an underlying view (adding new fields, removing etc),automatically the higher up views inherit this new information. Thismake maintenance very easy.3. These nested views are only ever used for the reporting side of ourapplication, not for the day-to-day database use by the application.We use Crystal Reports and Crystal is smart enough (can't believe Ijust said that about Crystal) to only pull back the fields that arebeing accessed by the report. In other words, Crystal will issue aSelect field1, field2, field3 from ReportingView Where .... eventhough "ReportingView" contains a long list of fields.Problems I can see.1. Parent views generally use "Select * From childview". This meansthat we have to execute a "sp_refreshview" command against all viewswhenever child views are altered.2. Parent views return a lot of information that isn't necessarilyused.3. Makes it harder to track down exactly where the information iscoming from. You have to drill right through to the child view to seethe raw table joins etc.Does anyone have any comments on this database design? I would love tohear your opinions and tales from the trenches.Best regards,Rod.
View 15 Replies View RelatedWhere can I find the list of new keywords for sql 7.0? Namely the keyword to allow duplicates when creating a table?
Thanks!
does anyone know whether the words 'comments' and 'narrative' have become reserved keywords post-SQL2000 ?
View 2 Replies View Related