Database Discussion Boards

getting second highest date from a sql table

hkchauhan26-Jul-07 2:16

hkchauhan

26-Jul-07 2:16

how can I get second highest date from a sqltable using a query???

hiiiiii

Re: getting second highest date from a sql table

Krish - KP26-Jul-07 2:21

Krish - KP

26-Jul-07 2:21

earlier some time back (in may/jun) i've given this solution for getting nth max value. try the same must work for you

DECLARE @nth as bigint
SET @nth = 10

SELECT * FROM TblA AS a WHERE @nth = (SELECT COUNT(ColA) FROM
TblB AS b WHERE b.ColA >= a.ColA)

@nth is the variable to hold the 1 for getting 1st max, 2 for 2nd max, .....

Regards
KP

Re: getting second highest date from a sql table

hk7792126-Jul-07 2:34

hk77921

26-Jul-07 2:34

run this query
it will return the second highest date

SELECT max(mdate) FROM <tablename> where mdate not in (SELECT max(mdate) FROM <tablename>) order by 1 desc

Harry

inserting values by checking the existing values

dsaikrishna26-Jul-07 2:12

dsaikrishna

26-Jul-07 2:12

Dear all,
i am a novice programmer to sqlserver i just want a condition for how to insert the values into 3 tables of data at a time by considering that the three tables contains values if one of the table does not contain any values then it wont to be inserted into any of the tables

sai krishna

Re: inserting values by checking the existing values

Eliz.k27-Jul-07 0:20

Eliz.k

27-Jul-07 0:20

Not sure if this is what you want...

IF EXISTS (SELECT TOP 1 1 FROM tblTable1 WHERE col1 = 'abc') AND
EXISTS (SELECT TOp 1 1 FROM tblTable2 WHERE col2= 'def') AND
EXISTS ( SELECT TOP 1 1 FROM tblTable3 WHERE col3= 'xyz' )
--Do Something
ELSE
--Do Nothing

Eliz.K
www.oin1.com

Hiding Report Parameters.

Karuna Kumar26-Jul-07 0:34

Karuna Kumar

26-Jul-07 0:34

Hi,

I am integrating Reporting services with my web application.
Fisrt i designed the report and deployed on the report server.My report is using 4 parameters Location,UserID,LastName,Firtsname.I am able to view the report by passing the parameters.This is all done throw report server.
But i am using my own controls(Textboxes) in my web page to pass this parameters.so i paced four text boxes and a ReportViewer Control.i am able to bind this parameters with the parametrs on report server.
but when i run the page the reportviewe control also showing the parametrs of report server.I dont need this because i already using my TextBoxes.so how to hide the reportserver parameters on Reportviwercontrol.

I hope Ypu guys understood my question. Simply i want hide report server parameters on reportviewer control

Plaese can any body help me?

Thanks
Karuna

Karuna

Stored Procedure Issue

Brendan Vogt26-Jul-07 0:00

Brendan Vogt

26-Jul-07 0:00

Hi,

I have a search form in ASP.NET 1.1. The search has 4 input textboxes, but only one of these are required.

Now I have to do a SELECT in stored procedure. I'm not sure how to construct this stored procedure because not all the input values will have a value.

Please can someone help me with my confusion.

Regards
ma se

Re: Stored Procedure Issue

Mike Dimmick26-Jul-07 0:46

Mike Dimmick

26-Jul-07 0:46

I would strongly recommend constructing a parameterized query in your client code and using that, rather than a stored procedure.

You occasionally find recommendations to use 'OR @paramName IS NULL' to make a parameter optional. This works in the sense that the correct results are returned, but it causes problems for the query optimizer. In SQL Server 2000 SP4, I observed that for a query that looked like

SELECT * FROM table
WHERE 
   ( ColumnA = @columnA OR @columnA IS NULL ) AND
   ( ColumnB = @columnB OR @columnB IS NULL )

that the optimizer actually picked an index on ColumnB when you specified only @columnA, and one on ColumnA when you specified only @columnB! That is, it scanned the wrong index, rather than seeking on the right one.

SQL Server 2005 at least picked the right index and index operation, but you have a new problem: the query plan for a stored procedure is computed the first time it is run, based on the arguments supplied for that run, and the resulting procedure is cached and reused until it ages out of the cache. If, the first time, you supply column A but not B, it picks the index on column A and caches this plan. If you then supply column B but not A, it tries to use the cached plan, eventually realises that the plan isn't workable and reverts to scanning the index and looking up in the base table - just doing a table scan would have been quicker. You can work around this by specifying WITH RECOMPILE on an EXEC statement, which recompiles the procedure just for that run (the previously cached plan is retained), or by specifying WITH RECOMPILE on the CREATE PROCEDURE statement, which means it's recompiled every time.

If you must use a stored procedure, use it as a shell - call sp_executesql with a dynamically-constructed query. The reason I recommend doing it in the client code is that C# and VB's string manipulation support is much better than T-SQL's.

Stability. What an interesting concept. -- Chris Maunder

Re: Stored Procedure Issue

Colin Angus Mackay26-Jul-07 0:52

Colin Angus Mackay

26-Jul-07 0:52

Gosh! That was a much more comprehensive answer than the one I gave.

My only comment would be:

Mike Dimmick wrote:
call sp_executesql with a dynamically-constructed query. The reason I recommend doing it in the client code is that C# and VB's string manipulation support is much better than T-SQL's.

The string manipulation may be better in the client code, but you open up some security concerns, in my view. I prefer the database to be the master of the queries run on it and allowing dynamic code through from the client shoots that idea down. Building the dyanmic code in SQL at least adds the possibility of running additional checks and knowing that the SQL is being built internally from a more trusted source.

Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...

"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."

My website

Re: Stored Procedure Issue

Colin Angus Mackay26-Jul-07 0:48

Colin Angus Mackay

26-Jul-07 0:48

ma se wrote:
I'm not sure how to construct this stored procedure because not all the input values will have a value.

Then they will be null?

SELECT * FROM MyTable
WHERE (someColumn = @param1 OR @param1 IS NULL) AND
      (someOtherColumn = @param2 OR @param2 IS NULL) AND... so on

Re: Stored Procedure Issue

Mike Dimmick26-Jul-07 3:05

Mike Dimmick

26-Jul-07 3:05

As I said in my other answer, that approach can give poor performance because either a) the optimizer gets confused and picks a bad plan or b) the plan it picks for the first set of arguments is poor for the second set of arguments. Marking the procedure to be recompiled every time (WITH RECOMPILE) can help with the second, but you incur the cost of recompiling the plan every time (although I suspect this is largely CPU-bound, barring loading the index/table statistics from wherever SQL Server keeps them).

Any time it has to do a table scan or index scan, it's going to cost you. If the data is not yet in memory, you'll incur a lot of I/O cost reading in all the pages from disk. If it is in memory, you'll get a lot of CPU usage shown in Task Manager and Processor performance counters, but adding more/faster CPUs won't help at all because in fact the processor is just sitting in wait states waiting for the data to come into its caches from main memory. (A faster front-side bus and faster RAM modules would help.)

This assumes that the main operator for each parameter is sargable and can therefore make use of index seeks. If you're using LIKE '%value%', go ahead and do it this way, SQL Server will have to perform a table scan or clustered index scan anyway, assuming no other fixed filter parameters which are sargable.

Stability. What an interesting concept. -- Chris Maunder

Re: Stored Procedure Issue

Brendan Vogt26-Jul-07 3:10

Brendan Vogt

26-Jul-07 3:10

Thanks.

I've just been informed that I need to make use of full text searching, how would I do this now??

Regards
ma se

Re: Stored Procedure Issue

Aaron VanWieren26-Jul-07 2:31

Aaron VanWieren

26-Jul-07 2:31

If you are using sql2005 you could also look at creating your stored procedure as a clr stored procedure, giving you an assembly and stored procedure. The advantage is that you can wrap the c#'s string capabilities into the stored procedure as well as other coding language capabilities. Also, you are able to recieve nulls and can debug the SProc at run time in vs2005.

Should take a look if this is an option.

My 2C worth.

_____________________________________________________________________

Our developers never release
code. Rather, it tends to escape, pillaging the countryside all around.

The Enlightenment Project (paraphrased comment)

Visit Me at GISDevCafe

Re: Stored Procedure Issue

Brendan Vogt26-Jul-07 3:09

Brendan Vogt

26-Jul-07 3:09

What would I need to search for??

Please check my new post under 1 on Colin's postings.

coping data in sql server express

Amjath Rahman25-Jul-07 22:55

Amjath Rahman

25-Jul-07 22:55

Hi All,

Im using Sql server Express 2005, in the client place.
i want to copy the database with all data and put it in my sql server express 2005 in office.
so plz help me how can i do this
how can i copy entire data from sql server express and put it in another sql server express

Thanks for ur valuable help

With Regards
Amjath

Re: coping data in sql server express

Blue_Boy25-Jul-07 23:12

Blue_Boy

25-Jul-07 23:12

copy mdf and ldf file from client place then attach database in your office

I Love SQL

Re: coping data in sql server express

Amjath Rahman25-Jul-07 23:24

Amjath Rahman

25-Jul-07 23:24

thanks for ur quick and valuable reply.
can u plz explain me how to do that.

sorry for disturbing u

With Regards
Amjath

Re: coping data in sql server express

Blue_Boy25-Jul-07 23:37

Blue_Boy

25-Jul-07 23:37

Just stop MSSQL Service then locate where are files copy them and restart again MSSQL Service

In your office open SQL Server Attach Database[^] by findinf file of MDF extension.

I Love SQL

Re: coping data in sql server express

Aaron VanWieren26-Jul-07 2:27

Aaron VanWieren

26-Jul-07 2:27

Actually detaching the database would be the first step. In ServerManager just right click on the database, click on tasks and then detach. I would first get the location from the properties of the ldf and mdf before detaching. Copy those files and move them to where you want, reattach on the database detached from and on your other database. The only other issue you might have is table ownership. Occasionally the mapping gets a little off and has to be justified.

Re: coping data in sql server express

Blue_Boy26-Jul-07 3:55

Blue_Boy

26-Jul-07 3:55

Why did you replied to me?! Sniff | :^)

I Love SQL

How to reset identity(ID) field

Mubashir Javaid25-Jul-07 22:09

Mubashir Javaid

25-Jul-07 22:09

I am using SQL server 2000.
I have added some records in table.
and then deleted.
Now I would like ID field to start count from 1 and onward.
what command will be used.

God Bless you.
Always do good to others

Re: How to reset identity(ID) field

Vasudevan Deepak Kumar25-Jul-07 22:15

Vasudevan Deepak Kumar

25-Jul-07 22:15

DBCC CHECKIDENT

Check out http://www.mssqlcity.com/FAQ/Devel/reset_identity_column.htm[^]

Vasudevan Deepak Kumar
Personal Homepage
Tech Gossips

Re: How to reset identity(ID) field

Muhammad Qasim Pasta25-Jul-07 22:37

Muhammad Qasim Pasta

25-Jul-07 22:37

I agree... but what about the relationships and constraints ...

if I have a primary key of identity column .. then that statement will generate value that already exists in my table.... m I right?

Re: How to reset identity(ID) field

Blue_Boy25-Jul-07 22:16

Blue_Boy

25-Jul-07 22:16

dbcc checkident(tablename,reseed,1)

I Love SQL

asp horizontal data

calvinjb888825-Jul-07 21:31

calvinjb8888

25-Jul-07 21:31

Do any one know how to display data from the data base by extracting it out using the unique id of the database. I wan to display the whole row of information of that unique id.

calvin

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.