|
hi all,
I am changing a sql Server 2000 query to sql server 2005.
That query contains =*.I changed it to Outer join in 2005.But the query returns only 1000 rows and in 2000 it returns 120000 rows.
here is the query in 2000
<br />
select p.planno,e.element,I.Invhst from plan_ p,Element e,Invhst I<br />
where p.Planno=e.Planno<br />
and I.Planno=*p.Planno
here is the query in 2005
select p.planno,e.element,I.Invhst from plan_ p<br />
inner join element E on P.Planno=E.Planno<br />
Left outer join Invhst on I.Planno=p.Planno
Please give me ur suggestion and help to solve this problem.
thanks in advance,
Sangeet
|
|
|
|
|
is it supposed to return 120000 rows, you could be duplicating data in sqlserver 2k. crossproducts of tables typically do that in my experience, which is why i dont use them.
|
|
|
|
|
ramyasangeet wrote: here is the query in 2005
select p.planno,e.element,I.Invhst from plan_ p
inner join element E on P.Planno=E.Planno
Left outer join Invhst on I.Planno=p.Planno
Have you tried a RIGHT join?
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi Everyone,
I have one problem that is i dont know how to connect ms access database in VC++ program.please tell me the procedure for connection.
please help me.
Thanks In Advance,
savitri
|
|
|
|
|
Please ask in VC++ forum!
Do rate the reply, if it helps or even if it doesnot, because it helps the members to know, what solved the issue. Thanks.
|
|
|
|
|
Hello Friends,
I've deleted my Table1's records and now i want to retrieve all of deleted records using Rollback.......
Can anybody tell me how can i do it using rollback or any other way.....
|
|
|
|
|
If you did a BEGIN TRAN before the delete then issuing a ROLLBACK TRAN will undo your delete and restore all your records, otherwise they have gone. You could restore from your latest backup - assuming you have one.
Hope this helps.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi there,
I am trying to encrypt my sotred procedures with "WITH ENCRYPTION" clause like this ALTER PROC myProcedure WITH ENCRYPTION. It works well for the stored procedure that has not parameters but for a stored procedure with parameter list it generates the following error.
"Incorrect syntax near '@p_param1'." where @p_param1 is the first parameter. Note that this SP is giving the proper results and runs well.
Thanx in advance
Mujtaba
"If both of us are having one apple each and we exchange it, at the end we both will have one apple each. BUT if both of us are having one idea each and we exchange it, at the end both of us will be having two ideas each."
|
|
|
|
|
Try adding the WITH clause after your parameters list like
create procedure testproc<br />
@param1 int<br />
with encryption <br />
as<br />
begin<br />
[your stmts here]<br />
end<br />
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
Well I wonder why this idea didn't struck me.  that was too simple.
Thanx for help SimulationofSai.
Mujtaba
"If both of us are having one apple each and we exchange it, at the end we both will have one apple each. BUT if both of us are having one idea each and we exchange it, at the end both of us will be having two ideas each."
|
|
|
|
|
What is the proper syntax of an if statement ina stored procedure in MS SQL please?
thanks
|
|
|
|
|
|
|
IF ReadManual = FALSE OR UseGoogle = FALSE
BEGIN
Waste time posting on CodeProject
END
ELSE
BEGIN
No need to post - Yipee!
END
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi everyone,
I am getting a bizzare message when I try running a stored procedure on SQL Server 2000 Query Analyzer. The message is "Formal parameter '@ACTION' was defined as OUTPUT but the actual parameter not declared OUTPUT".
Now, what I don't get is - the '@ACTION' parameter is NOT an output parameter. This is how the parameter is defined in the stored procedure header:
CREATE PROCEDURE [GV_SUPP_VALUATION_EDIT]
@ACTIVE_USER_ID INTEGER = 0,
@VALUATION_ID INTEGER = 0,
.
.
.
@ACTION VARCHAR(50) = 'SAVE',
@ERROR VARCHAR(500) = '' OUTPUT
AS
PRINT 'PROCEDURE [SDE].[GV_SUPP_VALUATION_EDIT]'
As you see from the code above, only the @ERROR parameter is defined as output. Why then am I getting this error? 
Your assistance with this is highly appreciated
|
|
|
|
|
Based on your post, I can't tell what's happening. I copied and pasted your snippet and it runs fine (see below). Maybe you could post the entire header along with the code you're using to call the procedure it might be easier to help figure out what's wrong.
Here's what I tried:
<br />
CREATE PROCEDURE [GV_SUPP_VALUATION_EDIT]<br />
@ACTIVE_USER_ID INTEGER = 0, <br />
@VALUATION_ID INTEGER = 0,<br />
@ACTION VARCHAR(50) = 'SAVE', <br />
@ERROR VARCHAR(500) = '' OUTPUT<br />
AS<br />
PRINT 'PROCEDURE [SDE].[GV_SUPP_VALUATION_EDIT]'<br />
go<br />
<br />
DECLARE @ERROR VARCHAR(500)<br />
EXEC [GV_SUPP_VALUATION_EDIT] 0, <br />
0,<br />
'SAVE', <br />
@ERROR OUTPUT<br />
Mark's blog: developMENTALmadness.blogspot.com
Funniest variable name:
lLongDong - spotted in legacy code, was used to determine how long a beep should be. - Dave Bacher
|
|
|
|
|
Hi,
Thanks for your reply  I just figured out what I'd done wrong! 
|
|
|
|
|
Hey everyone,
This is so embarassing! The message was actually correct, I was passing a non-output parameter as an output parameter. What prevented me from picking this up is the number of parameters I was passing, the 'edit' stored proc has 28 parameters - @ACTION is the 27th. My mistake was that I wasn't passing the 24th parameter, which is optional anyway.
This actually has made me favour the practice of using the syntax @param1=param1, @param2=param2, ... , @paramn=paramn - when calling stored procedures. I mean, if I had done this, then I wouldn't have suffered for 3 hours because of an optional parameter!
By the way, it's a great site you guys have here - keep up the good work!
|
|
|
|
|
I am using a Access database, and I am wanting to delete a record that a user selects, from a datagridview. The sql statement looks like this
command.CommandText = "DELETE FROM BankAccount WHERE ID = "'+dataGridView1.Rows[0].Cells[0].Value+ '";
I keep getting a datatype mismatch error. I am unsure of what type the Autonumber is, whether its an int, double etc. Is there any other way to delete a record, using an autonumber in the WHERE clause, or is it mayb just a simple problem and I am unaware of
|
|
|
|
|
try this
command.CommandText = "DELETE FROM BankAccount WHERE [ID] = "+Convert.ToInt32(dataGridView1.Rows[0].Cells[0].Value.ToString());
but be aware of SQL Injections
I Love T-SQL
|
|
|
|
|
Quinton wrote: I am unsure of what type the Autonumber is
AutoNumbers in Access tables are long integers.
|
|
|
|
|
Hi there,
i have an application developed for XP in VS2003 which uses Microsoft.ApplicationBlocks.Data.dll. Now i wish to migrate the application to vista and for that i first wich to migrate my whole code to VS2005. i just wanted to know whether Microsoft.ApplicationBlocks.Data.dll is compatible with VS2005 or if not then what are the workarounds
Best Regards,
Mustanseer
|
|
|
|
|
Download the .NET 2 version and find out. Rather than waiting for others to stumble on this thread, you could have found out by yourself and actually tried it.
|
|
|
|
|
I have an sql script that has 2 main blocks of Where filter which I'd call as Filter1 and Filter2 as follows:
<br />
Select<br />
DisplayColumns...<br />
From<br />
InterestingTables<br />
Where<br />
(Filter1) --W1<br />
AND --W2<br />
NOT --W3<br />
(Filter2) --W4<br />
Note that Filter1 and Filter2 are composite filters (inner filters nested; it shouldn't matter as long as I have the outer parenthesis as above, right?).
Now, say SetF1ExF2 is the result set of the sql script above.
Additionally, let's say I obtain the result set SetF1 by having W2 to W4 commented out and SetF1AndF2 comes from commenting out W3.
Shouldn't the following hold: SetF1AndF2 = SetF1 - SetF1ExF2
I am having a discrepancy between the values with SetF1AndF2 < SetF1 - SetF1ExF2.
TIA.
I am a SysAdmin, I battle my own daemons.
|
|
|
|
|
Hi,
Do you think you could phrase your question better maybe? I'm not sure I understand what you're asking
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
