|
Hemant Thaker wrote: If its running for 3 days, then for the first day it executes perfectly.
For the second day and remaining all days it gives following error.
Strange. Based on what you share, sounds like some logical error where you are considering the DayNo. Are you? Somehow, somewhere the current day is getting into picture while running the procedure and the error. Did you DEBUG and see? Check if so.
|
|
|
|
|
I suspect you are concatenating the parameter value and passing in a string! You might want to post the code used to build the procedure call.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for your responses.
Error is resolved, that was more logical.
I was creating Command object at class level, and adding parameters to it in remainderMailtimer_Elapsed().
This event will execute on timer interval.
So when it executes first time it find one parameter and runs successfully.
after 3 minutes interval it executes event for the second time and adds same parameter to it.
so now command object will have two parameters and corresponding SP has one. So it was throwing error.
I changed the code and created command object at event level.
Thanks.
By:
Hemant Thaker
|
|
|
|
|
Hi
Our ERP system has a stock master table. It consist of the fields 'warehouse', 'product' and a bunch of other fields relating to the stock item.
A product can exist in various warehouses, so it is the warehouse +product combination that is unique.
How would the primary key (if any) be set on this table?
|
|
|
|
|
A primary key (often) consists out of multiple columns. I'd suggest putting the primary key on BOTH, and to add an autoincrement-field and make that unique. Use the autoincrement-column to make relations to other tables.
ALTER TABLE dbo.MasterTable ADD CONSTRAINT PK_MasterTable
PRIMARY KEY CLUSTERED (WareHouseId, ProductId); (And call the autoincrement "MasterTableId")
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Thanks Eddie, but you have totally lost me?
If I lok at the two lines:
ADD CONSTRAINT PK_MasterTable
Prevents any duplicates in MasterTable column
and
PRIMARY KEY CLUSTERED (WareHouseId, ProductId)
Stores the data physically sorted by WarehouseId then ProductID?
Or are there three Primary keys now?
If you look at the table in MSQuery, the Product is bold, so is this the primary key?
|
|
|
|
|
Richard.Berry100 wrote: Prevents any duplicates in MasterTable column
No, it doesn't; it's the part behind that which says "which type" of constraint. It's a single statement, not two separate statements.
Richard.Berry100 wrote:
PRIMARY KEY CLUSTERED (WareHouseId, ProductId)
Stores the data physically sorted by WarehouseId then ProductID?
Yup.
Richard.Berry100 wrote: Or are there three Primary keys now?
One (compound) primary key, consisting of two columns, and the name of that constraint is "PK_MasterTable".
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Why would you not use WharehouseID-ProductID in a concatenated field as a primary key.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: WharehouseID
I see what you did there. 
|
|
|
|
|
Mycroft Holmes wrote:
Why would you not use WharehouseID-ProductID in a concatenated field as a primary key. |
That's a possibility; then again, it introduces a concatenation-action, and we'd be storing redundant information. It'd also affect performance; having a large varchar-based key (as two bigints as Id's or Guids would be concatenated to a varchar) would be not-nice for your indexes.
Or, in the words of my teacher; it would no longer be an atomic value.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Friends i am going to develop a website which is like you tube.so i want to play an add first then i want to play the video on clicking the video file.so help me in writing the code. i wish to use two databases one for adds and other for videos.so please help me writing code
Sundeep
|
|
|
|
|
Please do not post questions asking people to write your code for you. Members of this site come here to help people who make an effort to learn and do their own work. If you do not know how to begin to create your project then find some online study guides, or buy some books.
|
|
|
|
|
If you are looking to get the code written then try Elance or one of the other code writing sites. CodeProject is for people who want to write their own code and need a little help learning.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi - I have having a join issue.
we have report which was set up a while ago, needs to be modified.
I have to add one more table, using a LEFT OUTER JOIN but using (+)!
I need to left outer join table B to table A, but it is a substring.
I tried which made sense syntax wise -
substr(A.source,1,4)=substr(B.OFFERNO,1,4)(+)
which did not work.
Below syntax did not error out - does this look right?
substr(A.source,1,4)=substr(B.OFFERNO(+),1,4)
|
|
|
|
|
From the Oracle language reference: The (+) operator can be applied only to a column, not to an arbitrary expression. However, an arbitrary expression can contain one or more columns marked with the (+) operator.
So the second syntax looks valid to me. Does it give you the expected results?
"The ones who care enough to do it right care too much to compromise."
Matthew Faithfull
|
|
|
|
|
You are right.
The second one worked for me.
Thank you!
|
|
|
|
|
If this is still confusing, you can create a view with the expression and then do your join on the view.
|
|
|
|
|
I am sure I am overlooking something very simple but I can't see it.
I have a database of locations with latitudes and longitudes. I need to be able to query for locations that fall within a given radius from a lat/lon point
The code I am intending to use is: (Showing fixed centre 38,-118 in this version)
SELECT quakeid, (6371 * acos(cos(radians(38)) * cos(radians(latitude)) * cos(radians(longitude) - radians(-118)) + sin(radians(38)) * sin(radians(latitude)))) AS [dkm]
FROM tblUSGSData
(Derived from Creating a store locator[^])
This works fine and returns just over half a million entries.
My problem is that as soon as I add
HAVING [dkm] <= 50
or
WHERE [dkm] <= 50
I get the message
Msg 207, Level 16, State 1, Line 3
Invalid column name 'dkm'.
I obviously have not got my SQL glasses on today as I can't seem to resolve this. Can any one make (polite) suggestions?
|
|
|
|
|
Well, [dkm] is an Alias, not a columnname.
You would need to have the same expression in the having or where clause as you have in the select clause.
"The ones who care enough to do it right care too much to compromise."
Matthew Faithfull
|
|
|
|
|
Ah yes. My bad
SELECT quakeid, latitude, longitude, (6371 * acos(cos(radians(37)) * cos(radians(latitude)) * cos(radians(longitude) - radians(-122)) + sin(radians(37)) * sin(radians(latitude))))
FROM tblUSGSData
GROUP BY quakeid, latitude, longitude
HAVING (6371 * acos(cos(radians(37)) * cos(radians(latitude)) * cos(radians(longitude) - radians(-122)) + sin(radians(37)) * sin(radians(latitude)))) <= 50
works - at least does not produce an error. Not sure about the working bit but that is another story.
Many thanks.
|
|
|
|
|
Are you sure you need to use Haversines formula?
If you Radiuses are small enough the Cartesian distance formula would do, depending on what coordinate system you're using of course.
Ignore that question, I just remembered what USGS is, and realized it's a different sort of Quake you're working with. 
"The ones who care enough to do it right care too much to compromise."
Matthew Faithfull
modified 13-Feb-13 6:55am.
|
|
|
|
|
By the way, 50 km is about half a degree. Depending on the amount of rows in your table, it might be wise to filter for longitude between -122.5 and -121.5 and latitude between 36.5 and 37.5 first.
Also a simple transformation with 110 km/degree for latitude, and 110 * cos(latitude) for longitude, and then using simple pythagoras might speed up the query.
|
|
|
|
|
Thanks for your input. Yes I am adding some filtering now. I just needed to get the thing working first.
|
|
|
|
|
To add to Jorgen's answer, I've always wondered why SQL Server doesn't allow us to use alias in WHERE and HAVING clauses. The answer to that lies in the logical order in which the query is processed. The WHERE and HAVING clauses are processed before the SELECT clause and the alias do not exist at that stage.
However, technically it should be possible to introduce another stage earlier in the query processing pipeline where a mapping between expressions and their alias is made and WHERE and HAVING clauses can look up to these mappings and substitute the actual expression in place of the alias.
|
|
|
|
|
You are looking for CTE.
"The ones who care enough to do it right care too much to compromise."
Matthew Faithfull
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
