|
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.
|
|
|
|
|
Insert into TableName Values('val1','val2'.....)
Update TableName set Col1='val1', Col2='val2'..... where ColName=ValCondition
For better answer,post more detailed questin.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Thanks Blue_Boy;
sorry my english is bad !
I have two tables Table1 and historical_table1. With the same properties and the data are displayed in a gridview in editable mode . when the user modify a line of table1 I want that the first data (before update) will be inserted in historical_table1 automatically.
Thanks .
|
|
|
|
|
Use triggers[^].
While updating data in table Table1 (I sugggest you to rename this table 'Table1' and give it any meaningful name) execute trigger which do insert in historical_table1.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Thanks a lot Blue_Boy;
I must document on triggers because its the first time I heard trigger in database, thank you very much.
if you know a course for beginners on the triggers thank you pass me the link. 
|
|
|
|
|
No problem,I am glad to help you.
Check this link.[^]
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
|
You are welcome.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
