|
To solve the problem all I did was put a using(connection = new SQLConnection ...) statment around the code using the sql connection so the unmanaged resources were garbage collected.
Thanks,
Steve Holdorf
|
|
|
|
|
Here is my problem: I have a job posting board that displays job postings for a certain about of time. Each posting in the database has a start date and an end date. Both are datetime fields that contain the time of 00:00:00.0000. Now, when i go to display these jobs on the site I use the following query to get them:
SELECT TOP (10) Jobs.jobID, Job_Display.displayID, Jobs.title, Entity.entityName, Jobs.location
FROM Entity INNER JOIN
Jobs ON Entity.entID = Jobs.entID INNER JOIN
Job_Display ON Jobs.jobID = Job_Display.jobID
WHERE datediff(dd, getdate(), job_display.endDate) > -1 AND (Job_Display.display = 1)
order by job_display.displayID desc
Here is there the problem is: Say a job ended today (11/30/2011) it won't show up on the site, but what is intended is that it will be on the site until 12/1/2011. What do I need to change in the where clause to fix this issue?
|
|
|
|
|
Just a suggestion. The call to getDate is also going to include the current time. Have the time component truncated or set to '00:00:00.0000' as well. 
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
I would suppose....
where (getdate() < job_display.endDate)
|
|
|
|
|
How about WHERE GETDATE() BETWEEN startDate AND endDate? BETWEEN is inclusive.
Also, (if SQL Server) maybe look nito the DATE datatype.
|
|
|
|
|
Convert GetDate() to a date to get rid of the time component (assumes SQL Server 2008).
SELECT CONVERT(DATE,GETDATE())
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have one main database (for my user). now when i change the database(SP,View,Tables,....) from my side I wanna make possible the i create some scripts and when i run the scipt all the changes make for the user database without changing the data of user.
//if needed to delete SP,Create View , Delete Table,Create Column ,.... every thing that changed.
thanks!
|
|
|
|
|
Thsi may help: Database Publishing Wizard in VS 2010 Professional[^].
It's usually best to generate 2 scripts: one for the objects and one for the data, especially if you have large amounts of data (in which case you probably should look at other methods of doing this).
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
We use Red-Gate SQL Compare but it is paid fr and not particularly cheap.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hi thanks for reply.
But i wanna do that my self without and additional software.
just in C# and SQL.
|
|
|
|
|
I am trying to use a join to pull all 50 states from a table and data from another table if there is anything there. Here is the code
SELECT S.szState, SUM(O.CAP_OFFLIN) AS fOffline
FROM tblStates AS S LEFT OUTER JOIN tblOutages AS O ON S.szState = O.UNIT_STATE
WHERE NOT(O.TA_START > '11/23/2011') AND NOT(O.TA_END < '11/23/2011')
GROUP BY S.szState
I cannot for the life of me get the 50 states to list. I have changed the join order (using tblOutages then tblStates with RIGHT JOIN) but cannot get the join to work.
Anything stupid I am doing?
tblStates is szState nvarchar(2)
tblOutages is CAP_OFFLINE FLOAT, UNIT_STATE nvarchar(4)
Thx
Mark Jackson
UPDATE - THE WHERE clause is causing the issue. Removing it causes all 50 states to show. I tried to put it in a HAVING clause but I can't do that unless I include it in the result set.
modified 29-Nov-11 13:54pm.
|
|
|
|
|
when you use GROUP BY you need to use and aggregate_function as well.
|
|
|
|
|
It looks like there is a sum operation, so whould that not be the aggregate function? 
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
Not sure, but you may also have to include in your where clause something that allows TA_START and TA_END to be NULL.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
Changing the WHERE clause to be part of the JOIN clause produced the desired results
SELECT S.szState, SUM(O.CAP_OFFLIN) AS fOffline
FROM tblStates AS S LEFT JOIN tblOutages AS O ON S.szState = O.UNIT_STATE
AND NOT(O.TA_START > '11/23/2011')
AND NOT(O.TA_END < '11/23/2011')
GROUP BY S.szState
Thx
Mark Jackson
|
|
|
|
|
The WHERE clause was causing the LEFT OUTER JOIN to act as an INNER JOIN, hence you lost all of the records where there wasn't a match.
Well done on finding the right way to do it and for showing others what you did!
|
|
|
|
|
Hi,
I am getting error while configuring "Web Service Identity" in SQL Reporting Service.
ReportServicesConfigUI.WMIProvider.WMIProviderException: The account name is not valid. Specify an account in the form domain\alias.
at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.SetWebServiceIdentity(String applicationPool)
Please advise
Cheers
Berba
|
|
|
|
|
Please change account name which you are using for webserviceidentity I believe you have given a wrong account name for webservice. Please check it once.... 
|
|
|
|
|
Hi guyz,
I am a .net developer having experience of one and half year. I want to go for some sQL certifications which may be helpful in boosting my professional career. Can anybody help me that which course of SQL I should go for in this regard.
Thanks in advance
|
|
|
|
|
You posted on wrong forum!
I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
|
Hi,
I have a field called stock_value and I want to update the value with either 10 or 20 so t should list the records in random way then update the first as 10, second as 20, thrid as 10, fourth as 20 etc until end of records..
How can I do this?
Thanks,
Jassim
|
|
|
|
|
You don't say what database you're using, so I'll assume it's SQL-Server.
Random ordering you can get by making a variation of this query:
SELECT * FROM table ORDER BY NEWID()
Then you can use the CASE and MOD operators to update every second row.
|
|
|
|
|
Hi everyone, I'm writing a small client application that will access data from Sql Server Database. I would like to know how the SQL that can be used to reset a user's password if the old password is lost or forgotten.
The Sql Server stored procedure sp_password requires the old password but what should be done if the old password is lost in order to reset the user's password. I'm using SQL Server 2005. Thanks in advance.
|
|
|
|
|
sp_ prefix on your procedure implies that you are using a system procedure, just where are you storing your users authentication?
If you are creating a sql server user and assigning authentication/authorisation via sql server object you are using the most difficult model possible. I think most apps have a "functional" ID they use to connect to the database and then internalise the authorisation of their users, much simpler!
You may need to drop and recreate the user.
Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
