|
rownum is a pseudocolumn so it's created automatically and defining it in the inner select would change the logic of the statement.
The need to optimize rises from a bad design
|
|
|
|
|
|
Didn't quite understand your point? There's a good example in the article:
select *
from
(select *
from t
order by id)
where rownum <= 10
which returns top 10 rows. Rownum is placed on the outer query and the inner query selects only columns from the table t. Rownum is used on the ordered result set to ensure that really top 10 rows based on id column are returned.
I understood that the original query in this question was doing top N fetch.
The need to optimize rises from a bad design
|
|
|
|
|
I know that's why I said good catch... when I saw the rownum I was thinking of it as
(
SELECT col1, col2,ROW_NUMBER() OVER (PARTITION BY col1_id ORDER BY col1) AS rownum
FROM employee
)
where rownum < 200
My bad..
hth
Al
|
|
|
|
|
Oh, I see.
I clearly misunderstood your reply to my post. Sorry for that.
The need to optimize rises from a bad design
|
|
|
|
|
If you're running this in SQL*Plus or a program based on that, I would guess that the problem is in & character. This is normally reserved for prompting values so your statement may evaluate to
where Trans_date between '' and ''
Also you 'should' provide an alias for sub select in FROM.
If you're using tools mentioned above, they should show you the line where the problem is.
The need to optimize rises from a bad design
|
|
|
|
|
Please can someone help me!!! I use SQL Server 2005 and I need to import the data in a sample SQL Db into my own SQL database.... I dont know how to do this please help. my boss is on my neck
|
|
|
|
|
Check this google result.[^]
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.
|
|
|
|
|
|
Two books that I've found useful. They are bit old (from the time before electricity  ), but excellent ones:
- An introduction to database systems / C.J. Date
- Fundamentals of database systems / Elmasri & Navathe
The need to optimize rises from a bad design
|
|
|
|
|
I'll second both of those suggested books
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
"Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
|
|
|
|
|
Thanx Mika
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
No problem
The need to optimize rises from a bad design
|
|
|
|
|
If you are using Sql Server then you should look at Pro Sql Server 200x Database Design and Optimization by Louis Davidson, et al. (Apress).
|
|
|
|
|
download absolute beginners lesson in microsoft visual studio site.
You have there video about c# and sql server.
BOB TUBOR lessons.
Later on you will have something to question, not now 
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
nelsonpaixao wrote: BOB TUBOR lessons.
Sure
Thanx mate!
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
Hi
i need to pass table name and to a function dynamically anyone can help
Mohammad Al Hoss
Development To Me Is A Pleasure more than a Job
|
|
|
|
|
Well, you can pass the table name as a variable, but as far as I know you cannot execute dynamic sql in a function so it will be of no use to you.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I knew this would come up sooner or later.
SQL Server Central had an article (sorry no link) that used XML to pass the table and data to a function.
Stored proc expamle
Declare @X xml<br />
Set @X = (SELECT RegionID, sum(Amount) Amount <br />
FROM FactTran<br />
where PeriodID = @PeriodID<br />
and WFGroupID = @WFGroupID<br />
and TranTypeID = @TranTypeID<br />
and ISNULL(BUID,7) = 7<br />
Group By <br />
RegionID<br />
For XML Raw ('RegionID'), ROOT('Regions'))
Function example
LEFT OUTER JOIN (SELECT <br />
x.item.value('@RegionID[1]', 'VARCHAR(100)') AS RegionID,<br />
x.item.value('@Amount[1]', 'Decimal(30,2)') AS Amount<br />
FROM @x.nodes('//Regions/RegionID') AS x(item)) R on R.RegionID = A.RegionID<br />
These are my usage and may give you some idea if you cannot find the article.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I am trying to install the SQL Server 2008 Express on Windows XP SP3 machine but getting the following messages:
Event Type: Error
Event Source: MSSQL$SQLSERVER
Event Category: Server
Event ID: 17190
Date: 10/6/2008
Time: 6:36:38 PM
User: N/A
Computer: JASSIMRAHMA
Description:
FallBack certificate initialization failed with error code: 1.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 26 43 00 00 10 00 00 00 &C......
0008: 16 00 00 00 4a 00 41 00 ....J.A.
0010: 53 00 53 00 49 00 4d 00 S.S.I.M.
0018: 52 00 41 00 48 00 4d 00 R.A.H.M.
0020: 41 00 5c 00 53 00 51 00 A.\.S.Q.
0028: 4c 00 53 00 45 00 52 00 L.S.E.R.
0030: 56 00 45 00 52 00 00 00 V.E.R...
0038: 00 00 00 00 ....
Event Type: Error
Event Source: MSSQL$SQLSERVER
Event Category: Server
Event ID: 17182
Date: 10/6/2008
Time: 6:36:38 PM
User: N/A
Computer: JASSIMRAHMA
Description:
TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. Cannot find object or property.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 1e 43 00 00 10 00 00 00 .C......
0008: 16 00 00 00 4a 00 41 00 ....J.A.
0010: 53 00 53 00 49 00 4d 00 S.S.I.M.
0018: 52 00 41 00 48 00 4d 00 R.A.H.M.
0020: 41 00 5c 00 53 00 51 00 A.\.S.Q.
0028: 4c 00 53 00 45 00 52 00 L.S.E.R.
0030: 56 00 45 00 52 00 00 00 V.E.R...
0038: 00 00 00 00 ....
Event Type: Error
Event Source: MSSQL$SQLSERVER
Event Category: Server
Event ID: 17182
Date: 10/6/2008
Time: 6:36:38 PM
User: N/A
Computer: JASSIMRAHMA
Description:
TDSSNIClient initialization failed with error 0x80092004, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. Cannot find object or property.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 1e 43 00 00 10 00 00 00 .C......
0008: 16 00 00 00 4a 00 41 00 ....J.A.
0010: 53 00 53 00 49 00 4d 00 S.S.I.M.
0018: 52 00 41 00 48 00 4d 00 R.A.H.M.
0020: 41 00 5c 00 53 00 51 00 A.\.S.Q.
0028: 4c 00 53 00 45 00 52 00 L.S.E.R.
0030: 56 00 45 00 52 00 00 00 V.E.R...
0038: 00 00 00 00 ....
Event Type: Error
Event Source: MSSQL$SQLSERVER
Event Category: Server
Event ID: 17826
Date: 10/6/2008
Time: 6:36:38 PM
User: N/A
Computer: JASSIMRAHMA
Description:
Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: a2 45 00 00 12 00 00 00 ¢E......
0008: 16 00 00 00 4a 00 41 00 ....J.A.
0010: 53 00 53 00 49 00 4d 00 S.S.I.M.
0018: 52 00 41 00 48 00 4d 00 R.A.H.M.
0020: 41 00 5c 00 53 00 51 00 A.\.S.Q.
0028: 4c 00 53 00 45 00 52 00 L.S.E.R.
0030: 56 00 45 00 52 00 00 00 V.E.R...
0038: 00 00 00 00 ....
Event Type: Error
Event Source: MSSQL$SQLSERVER
Event Category: Server
Event ID: 17120
Date: 10/6/2008
Time: 6:36:38 PM
User: N/A
Computer: JASSIMRAHMA
Description:
SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: e0 42 00 00 10 00 00 00 àB......
0008: 16 00 00 00 4a 00 41 00 ....J.A.
0010: 53 00 53 00 49 00 4d 00 S.S.I.M.
0018: 52 00 41 00 48 00 4d 00 R.A.H.M.
0020: 41 00 5c 00 53 00 51 00 A.\.S.Q.
0028: 4c 00 53 00 45 00 52 00 L.S.E.R.
0030: 56 00 45 00 52 00 00 00 V.E.R...
0038: 00 00 00 00 ....
Thanks,
Jassim
|
|
|
|
|
Have you installed all your service packs for your OS ?
|
|
|
|
|
|
just tried to start the SQL Server (SQLEXPRESS) service and got the following messages in the evet log:
Event Type: Error
Event Source: MSSQL$SQLEXPRESS
Event Category: Server
Event ID: 17207
Date: 09/10/2008
Time: 11:35:27 AM
User: N/A
Computer: JASSIMRAHMA
Description:
FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'e:\sql10_main_t\sql\mkmastr\databases\objfre\i386\modellog.ldf'. Diagnose and correct the operating system error, and retry the operation.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 37 43 00 00 10 00 00 00 7C......
0008: 17 00 00 00 4a 00 41 00 ....J.A.
0010: 53 00 53 00 49 00 4d 00 S.S.I.M.
0018: 52 00 41 00 48 00 4d 00 R.A.H.M.
0020: 41 00 5c 00 53 00 51 00 A.\.S.Q.
0028: 4c 00 45 00 58 00 50 00 L.E.X.P.
0030: 52 00 45 00 53 00 53 00 R.E.S.S.
0038: 00 00 00 00 00 00 ......
Event Type: Error
Event Source: MSSQL$SQLEXPRESS
Event Category: Server
Event ID: 17120
Date: 09/10/2008
Time: 11:35:26 AM
User: N/A
Computer: JASSIMRAHMA
Description:
SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: e0 42 00 00 10 00 00 00 àB......
0008: 17 00 00 00 4a 00 41 00 ....J.A.
0010: 53 00 53 00 49 00 4d 00 S.S.I.M.
0018: 52 00 41 00 48 00 4d 00 R.A.H.M.
0020: 41 00 5c 00 53 00 51 00 A.\.S.Q.
0028: 4c 00 45 00 58 00 50 00 L.E.X.P.
0030: 52 00 45 00 53 00 53 00 R.E.S.S.
0038: 00 00 00 00 00 00 ......
Event Type: Error
Event Source: MSSQL$SQLEXPRESS
Event Category: Server
Event ID: 17826
Date: 09/10/2008
Time: 11:35:26 AM
User: N/A
Computer: JASSIMRAHMA
Description:
Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: a2 45 00 00 12 00 00 00 ¢E......
0008: 17 00 00 00 4a 00 41 00 ....J.A.
0010: 53 00 53 00 49 00 4d 00 S.S.I.M.
0018: 52 00 41 00 48 00 4d 00 R.A.H.M.
0020: 41 00 5c 00 53 00 51 00 A.\.S.Q.
0028: 4c 00 45 00 58 00 50 00 L.E.X.P.
0030: 52 00 45 00 53 00 53 00 R.E.S.S.
0038: 00 00 00 00 00 00 ......
Event Type: Error
Event Source: MSSQL$SQLEXPRESS
Event Category: Server
Event ID: 17182
Date: 09/10/2008
Time: 11:35:26 AM
User: N/A
Computer: JASSIMRAHMA
Description:
TDSSNIClient initialization failed with error 0x80092004, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. Cannot find object or property.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 1e 43 00 00 10 00 00 00 .C......
0008: 17 00 00 00 4a 00 41 00 ....J.A.
0010: 53 00 53 00 49 00 4d 00 S.S.I.M.
0018: 52 00 41 00 48 00 4d 00 R.A.H.M.
0020: 41 00 5c 00 53 00 51 00 A.\.S.Q.
0028: 4c 00 45 00 58 00 50 00 L.E.X.P.
0030: 52 00 45 00 53 00 53 00 R.E.S.S.
0038: 00 00 00 00 00 00 ......
Event Type: Error
Event Source: MSSQL$SQLEXPRESS
Event Category: Server
Event ID: 17182
Date: 09/10/2008
Time: 11:35:26 AM
User: N/A
Computer: JASSIMRAHMA
Description:
TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. Cannot find object or property.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 1e 43 00 00 10 00 00 00 .C......
0008: 17 00 00 00 4a 00 41 00 ....J.A.
0010: 53 00 53 00 49 00 4d 00 S.S.I.M.
0018: 52 00 41 00 48 00 4d 00 R.A.H.M.
0020: 41 00 5c 00 53 00 51 00 A.\.S.Q.
0028: 4c 00 45 00 58 00 50 00 L.E.X.P.
0030: 52 00 45 00 53 00 53 00 R.E.S.S.
0038: 00 00 00 00 00 00 ......
Event Type: Error
Event Source: MSSQL$SQLEXPRESS
Event Category: Server
Event ID: 17204
Date: 09/10/2008
Time: 11:35:26 AM
User: N/A
Computer: JASSIMRAHMA
Description:
FCB::Open failed: Could not open file e:\sql10_main_t\sql\mkmastr\databases\objfre\i386\model.mdf for file number 1. OS error: 21(The device is not ready.).
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 34 43 00 00 10 00 00 00 4C......
0008: 17 00 00 00 4a 00 41 00 ....J.A.
0010: 53 00 53 00 49 00 4d 00 S.S.I.M.
0018: 52 00 41 00 48 00 4d 00 R.A.H.M.
0020: 41 00 5c 00 53 00 51 00 A.\.S.Q.
0028: 4c 00 45 00 58 00 50 00 L.E.X.P.
0030: 52 00 45 00 53 00 53 00 R.E.S.S.
0038: 00 00 00 00 00 00 ......
Event Type: Error
Event Source: MSSQL$SQLEXPRESS
Event Category: Server
Event ID: 17190
Date: 09/10/2008
Time: 11:35:26 AM
User: N/A
Computer: JASSIMRAHMA
Description:
FallBack certificate initialization failed with error code: 1.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 26 43 00 00 10 00 00 00 &amp;C......
0008: 17 00 00 00 4a 00 41 00 ....J.A.
0010: 53 00 53 00 49 00 4d 00 S.S.I.M.
0018: 52 00 41 00 48 00 4d 00 R.A.H.M.
0020: 41 00 5c 00 53 00 51 00 A.\.S.Q.
0028: 4c 00 45 00 58 00 50 00 L.E.X.P.
0030: 52 00 45 00 53 00 53 00 R.E.S.S.
0038: 00 00 00 00 00 00 ......
Event Type: Error
Event Source: Service Control Manager
Event Category: None
Event ID: 7024
Date: 09/10/2008
Time: 11:35:27 AM
User: N/A
Computer: JASSIMRAHMA
Description:
The SQL Server (SQLEXPRESS) service terminated with service-specific error 1814 (0x716).
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
|
|
|
|
|
what is @identity in sql server 2005............is a table or variable what functionality it has...plz explain.
|
|
|
|
|
@identity is not part of SQL Server, so either its a variable you have declared or you meant @@identity which contains the last value inserted into an identity column.
Bob
Ashfield Consultants Ltd
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
