|
hello forum
i want to give an option of search on frontend (asp.net) that will find the given keyword in selected columns of each table present in database
how can i do that perfectly and without effecting the performance.
How to create a stored procedure to apply a search on every table in a database
can anybody help me.
regards
rahul
|
|
|
|
|
Assuming SQL Server 2005/2008 there is a built in command called sp_MSForEachTable
exec sp_MSForEachTable @command1 = "select col1 from ? where x = y"
Note the use of double quotes around the command. When the query runs it replaces the question mark with the table name. I have a feeling you made need single quotes around the question mark, but I don't have sql server on this pc to check.
Hopefully this will get you in the right direction
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thanx Friend
I m working on that....
regards
Rahul
|
|
|
|
|
You're welcome, I hope you get the result you need
Bob
Ashfield Consultants Ltd
|
|
|
|
|
hello Bob
I did some job on that but the output is not according to my desire.
Let me explain
the Demo tables are
txnNews
NewsId Title News
txnWallpapers
WallpaperID Name Path
txnHoroscope
SunSignId Horoscope
Now i want to do search on the News and title columns of txnNews on Name column of txnWallpapers and Horoscope column of txnHoroscope
the code that i used is
declare @search varchar(128)
declare @mycommand varchar(128)
declare @mywhereand varchar(128)
declare @returnvalue varchar(156)
declare @colname varchar (50)
set @search = '%India%'
set @mycommand = 'select * from ? where news like '''+ @search+''''
exec @returnvalue= sp_MSforeachtable
@command1 = @mycommand
print @returnvalue
that gives an error for the other two tables of invalid column
what should i do
regards
rahul
|
|
|
|
|
Think, if that is the error you are getting what do you THINK may be causing the problem. The columns are different in each table, you expect SQL to be telepathic. Also you don't need to search EVERY table in the database which this will do. looking for the same column in EVERY table.
Your requirement is to search different columns in 3 tables, write 3 select statements and deal with the result sets.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello all,
We're about to start using reporting services. It looks like it will do most things we want, however, we are having some trouble figuring out how to do things in a way that doesn't create more work for us long term. We would like to set things up so that our ASP.NET applications that call reporting services can specify which connection the reports actually run against. In other words, in dev, we want to make sure it is using the dev connection, while in live, we obviously want the real data. The suggestions I've seen so far are as follows:
1) Use multiple instances of reporting services, and use shared connections with different connection strings on each, then alter the URL you are calling to retrieve reports.
2) Have a dynamic connection string which uses data passed in as a parameter to determine the host and database to use for building the report.
Item #2 is pretty much out, since it would involve manually going back to a static connection string any time we want to edit a report. I can see potential for that breaking all over the place. So, is #1 the only way to do this or is there something easier and more obvious that we are missing? We currently have automated deployments for our other code, and we'd like to do this in a way that will eventually allow the same approach.
Any thoughts?
Will
|
|
|
|
|
I think SSRS does this automatically - as long as you configure the Data Source through the web front end and make sure it's named the same in each environment it should be okay.
I've got projects that I deploy between different environments and don't have to make any changes to the data source. It might be worth pointing out that when deploying to the remote environment I upload the RDL files manually rather than deploying from the VS Solution (as VS isn't installed on the UAT and Production machines) though you can probably get the same end result by changing your project properties so that the datasource isn't overwritten on deployment.
At any rate, I don't remember having to do anything particularly clever when I installed it last (though that was over a year ago...!)
It definitely isn't definatley
|
|
|
|
|
We use #2, the dynamic connection string and pass in the server as a parameter. Works fine for us, the developer is so used to the process of changing servers when modifying a report that it is now automatic. A total PITA for the rest of us who rarely have to get into the reports. B/C as report can run against any of 4-5 servers this work perfectly.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have a table with three columns: EmployeeID, Target, Date
Every employee is assigned a target (amount in dollars) to try to hit on every date. How do I get the latest target each employee has been assigned ordered by the employeeID. I tried the following but it is not accurate:
SELECT EmployeeID, MAX(Date), Target
FROM Target
GROUP BY EmployeeID, Target
ORDER BY EmployeeID
This query does not only return the latest target date.
|
|
|
|
|
CodingYoshi wrote: How do I get the latest target each employee
You can either add HAVING clause after GROUP BY or eliminate previous dates like:
SELECT EmployeeID, Date, Target
FROM Target
WHERE Date = (SELECT MAX(DATE) FROM Target)
ORDER BY EmployeeID
The previous example is assuming that the date is the same for all. If it's not, use correlated subquery like
...
WHERE Date = (SELECT MAX(DATE) FROM Target t2 WHERE t2.EmployeeID = target.EmployeeID)
...
|
|
|
|
|
I need some assistance in accommodating an apostrophe in an SQL statement using VBA in MS Access. Please help.
<br />
strSQLHyperion = "SELECT [COUNTRY], [TYPE], [BUSINESS_UNIT], " & _<br />
"[ALT_GROUPING], [PERSONNEL_AREA], [L_R_G], [REGION], [JOB_FUNCTION], " & _<br />
"[PRIMARY_KEY] FROM [TBLHYPERION] " & _<br />
"WHERE [COUNTRY]='" & UCase(rstInputFile![COUNTRY]) & "' " & _<br />
"AND [TYPE]='" & UCase(rstInputFile![Type]) & "' " & _<br />
"AND [BUSINESS_UNIT]='" & UCase(rstInputFile![BUSINESS_UNIT]) & "' " & _<br />
"AND [ALT_GROUPING]='" & UCase(rstInputFile![ALT_GROUPING]) & "' " & _<br />
"AND [PERSONNEL_AREA]='" & UCase(rstInputFile![PERSONNEL_AREA]) & "' " & _<br />
"AND [L_R_G]='" & UCase(rstInputFile![L_R_G]) & "' " & _<br />
"AND [REGION]='" & UCase(rstInputFile![REGION]) & "' " & _<br />
"AND [JOB_FUNCTION]='" & UCase(rstInputFile![JOB_FUNCTION]) & "'"<br />
What do I need to do to prevent it from hanging up on the apostrophe? The apostrophe needs to be accepted in:
<br />
"AND [JOB_FUNCTION]='" & UCase(rstInputFile![JOB_FUNCTION]) & "'" <br />
modified on Wednesday, December 3, 2008 11:31 AM
|
|
|
|
|
You need to replace the apostrophy with 2 apostrophies. Best way is probably through a function (although I'm not sure of the exact syntax for VBA) - something like :
Public Function MakeSqlSafe(strData) as string
Return strData.Replace("'", "''")
end function
and then pass each one of your inputs through the function, e.g.
'" & MakeSqlSafe(UCase(rstInputFile![REGION])) & "'
Hope this helps.
|
|
|
|
|
Thank you, liqz for your assistance.
The item that I have encountered in the implementation in the temporary copy of the application is: "Return without GoSub".
The code is as follows in VBA:
<br />
Public Function MakeSqlSafe(strData) As String<br />
strNewStrData = Replace(strData, "'", "''")<br />
Return<br />
End Function<br />
<br />
strSQLHyperion = "SELECT [COUNTRY], [TYPE], [BUSINESS_UNIT], " & _<br />
"[ALT_GROUPING], [PERSONNEL_AREA], [L_R_G], [REGION], [JOB_FUNCTION], " & _<br />
"[PRIMARY_KEY] FROM [TBLHYPERION] " & _<br />
"WHERE [COUNTRY]='" & UCase(rstInputFile![COUNTRY]) & "' " & _<br />
"AND [TYPE]='" & UCase(rstInputFile![Type]) & "' " & _<br />
"AND [BUSINESS_UNIT]='" & UCase(rstInputFile![BUSINESS_UNIT]) & "' " & _<br />
"AND IsNull([ALT_GROUPING]) " & _<br />
"AND [PERSONNEL_AREA]='" & UCase(rstInputFile![PERSONNEL_AREA]) & "' " & _<br />
"AND [L_R_G]='" & UCase(rstInputFile![L_R_G]) & "' " & _<br />
"AND [REGION]='" & UCase(rstInputFile![REGION]) & "' " & _<br />
"AND [JOB_FUNCTION]='" & MakeSqlSafe(UCase(rstInputFile![JOB_FUNCTION])) & "'"<br />
Any assistance in resolving this item would be greatly appreciated!!!
|
|
|
|
|
Ah, try changing the function to :
Function MakeSqlsafe(strData)
MakeSqlSafe = Replace(strData, "'", "''")
end Function
|
|
|
|
|
It worked!!! Woo Hoo!!!
Thank you liqz, for your assistance in resolving this item!!!
|
|
|
|
|
Although replacing single apostrophe with double apostrophes will correct your problem, I think you should use parameters instead. Concatenating literal strings leaves you open to sql injections. Also using parameters gives a performance advantage.
|
|
|
|
|
Hi, below is my database schema and some of my sample data
patient(patientID,name,email, address)
visitentry(visitentryID,medicalnotes, patientID)
symptom(symptomID,name,description)
visitentrysymptom(symptomentryID, patientID, symptomID,date)
Patient Table
1 || myname || email@email.com || blk 123 tampines
2 || myname2 || another@email.com || blk 543 pasir ris
Visit Entry Table
1 || high fever 40degree || 1
Symptom Table
1 || fever || feeling warm in body
2 || bleeding || red substance
3 || flu || sneezing with mucas
Visit Entry Symptom Table
1 || 1 || 2 || 02/12/2008
2 || 1 || 3 || 02/12/2008
Okay so what i wan to do now is to Count the number of fever, bleeding and flu base on the patient address (using the LIKE to extract TAMPINES) and the current month
I came up with this statement
SELECT count(visitentrysymptom.symptomID)
FROM symptom inner join visitentrysymptom
on symptom.SymptomID = visitentrysymptom.symptomID
where symptom.name in ('fever')
But my statement does not check the patient address(TAMPINES) and the current month
|
|
|
|
|
SELECT count(visitentrysymptom.symptomID)
FROM symptom
inner join visitentrysymptom on symptom.SymptomID = visitentrysymptom.symptomID
where symptom.name in ('fever')
and visitentrysymptom.patientID in (select patientID from patient where address like '%TAMPINES%')
Keep It Simple Stupid! (KISS)
|
|
|
|
|
THANKS ALOT!!!
I have modified ur query to check the date as well
ALTER PROCEDURE CountCases
(
@location varchar(100),
@symptomname varchar(100)
)
AS
SELECT count(visitentrysymptom.symptomID) "number"
FROM symptom inner join visitentrysymptom on symptom.SymptomID = visitentrysymptom.symptomID
where symptom.name in (select name from symptom where name= @symptomname)
and visitentrysymptom.date in (select date from visitentrysymptom where date= '03/12/2008 22:39:09')
and visitentrysymptom.patientID in (select patientID from patient where homeaddress like '%'+@location+'%')
How do i make the date to check only the month(current month)
modified on Wednesday, December 3, 2008 10:50 AM
|
|
|
|
|
Very simple, like this:
ALTER PROCEDURE CountCases
(
@location varchar(100),
@symptomname varchar(100)
)
AS
SELECT count(visitentrysymptom.symptomID) "number"
FROM symptom inner join visitentrysymptom on symptom.SymptomID = visitentrysymptom.symptomID
where symptom.name in (select name from symptom where name= @symptomname)
and visitentrysymptom.date in (select date from visitentrysymptom where month(date) = 3 and year(date) = 2008)
and visitentrysymptom.patientID in (select patientID from patient where homeaddress like '%'+@location+'%')
Keep It Simple Stupid! (KISS)
|
|
|
|
|
Hi. How can I store PDB files in my MS SQL Database? I want to have a compilation of those files for quick retrieval. There's only an image and xml file in the datatype list. What about other files?
|
|
|
|
|
Take a look at the Binary and VarBinary datatypes.
|
|
|
|
|
|
Hi!
I work with Sqlserver2005 and I have a table which I update, I need to insert the data before update in a table History.
is a way to do that.
Thank you in advance.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
