|
Thank you
Best Regards,
M. J. Jaya Chitra
|
|
|
|
|
Hi all,
i want to know How to find exact record in my table.
ex : in a table having 10 records.
i want 5 th record. with out knowing details containg in a table how can i find it. only know table name.
pls help me.
|
|
|
|
|
Here u get the 5th record
SELECT TOP 1 FName
FROM
(
SELECT TOP 5 FName
FROM Names
ORDER BY FName Desc
)
|
|
|
|
|
hi Prasad,
Thanks for u r valuable solution, its working by making some modifications.
this is working properly
SELECT TOP 1 sal
FROM prs where sal in (SELECT TOP 4 sal
FROM prs
ORDER BY sal )order by sal desc
all the best
|
|
|
|
|
Are you trying to get nth Max or Min value
or trying to fetch specific row by number.
Regards
KP
|
|
|
|
|
How can i set the default value to the column while designing the table in SQL Express 2005 GUI
Best Regards,
M. J. Jaya Chitra
|
|
|
|
|
[Created] [datetime] NOT NULL CONSTRAINT [DF_PurchaserTransactionMaster_Created] DEFAULT (getdate())
[Total] [decimal](12, 2) NOT NULL CONSTRAINT [DF_PurchaserTransactionMaster_Total] DEFAULT (0)
Regards,
Sylvester G
sylvester_g_m@yahoo.com
|
|
|
|
|
Dear Sylvester,
I want to know how i can achieve this in GUI i.e which property of the column can i use. is it by default value or binding and while doing this i can simply specify the value alone know, otherwise should i prefix the value with any special characters.
Best Regards,
M. J. Jaya Chitra
|
|
|
|
|
when you create a column you can see a detailed section in the bottom. There you can set the default value.
Regards,
Sylvester G
sylvester_g_m@yahoo.com
|
|
|
|
|
Hello
Is it possible to drop two tables located on two databases in different servers using a single query ?
|
|
|
|
|
why you need to do it in a single query...
you can do it in two seperate quires....
If U Get Errors U Will Learn
If U Don't Get Errors U Have Learnt
|
|
|
|
|
vimal_yet wrote: you can do it in two seperate quires....
Thank you. But I don't want to do this. If I am satisfied with doing this, what is the necessity of posting a question here on this topic .
|
|
|
|
|
try using this
drop table [servername].[instancename].username.<tablename>
example:
drop table [dbserver].collegedetails.dbo.stud
If U Get Errors U Will Learn
If U Don't Get Errors U Have Learnt
|
|
|
|
|
I have the following Stored procedure. There is column named
"tcktreceived" in my database and I want to pass all the rows one by
one to the parameter @starttime. I don't know how to do it.
CREATE PROCEDURE [twcsan].[usp_DateDiff]
-- Add the parameters for the stored procedure here
@starttime DateTime
AS
BEGIN
DECLARE @Diff Varchar(15)
DECLARE @Day INT
DECLARE @Hour INT
DECLARE @Minute INT
DECLARE @Start_Date DateTime
DECLARE @End_Date DateTime
DECLARE @itemReceived DateTime
DECLARE @ID INT
DECLARE @message VARCHAR(50)
DECLARE @table TABLE
(
ItemReceived DateTime,
ID INT,
message text,
Differnce VARCHAR(20)
)
SET NOCOUNT ON;
SET @Start_Date = @starttime
SET @End_Date = GETDATE()
SET @Day = DATEDIFF( day, @Start_Date, @End_Date)
SET @Hour = DATEDIFF(hour , @Start_Date, @End_Date)
SET @Minute = DATEDIFF(minute , @Start_Date, @End_Date)
SET @Minute = @Minute-(@HOUR* 60)
SET @Hour = @Hour-(24* @Day)
SET @Diff = CONVERT(Varchar, @Day) +'d ' + CONVERT(Varchar , @Hour) +
'h ' + CONVERT(Varchar , @Minute) +'m'
INSERT INTO @table(ItemReceived, ID, message, Differnce)
select tck.tcktreceived, tck.ticketid,tckmsg.tcktmessage,@Diff
from tbtickets tck inner join tbticketsmessages tckmsg
on tck.ticketid = tckmsg.ticketid
select * from @table
END
seema
|
|
|
|
|
seemamltn wrote: want to pass all the rows one by
one to the parameter @starttime. I don't know how to do it.
Use CURSOR s for that. However, that is generally very bad practice and should only be done when all other avenues have been exhausted.
SQL Server, and any relational database, works best on sets of data rather than individual items of data. If you can do what you want in one SELECT statement the operation will be much faster and you will use less resources on the SQL Server because you are taking advantage of its capabilities.
It might be best to tell us what the big picture is. Then we might be able to guide you to a set based solution.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
* Reading: Developer Day 5
Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton
My website
|
|
|
|
|
Hi,
Here is my Specs. I created Stored Procedure Which I posted befor.for meeting the requriements
I will be really grateful if you could solve my problem.
I have a table which has a field "tcktReceived" of type datetime. I
have a grid view which has two columns.
In first column i have to show the data from field "tcktReceived" and
in second column I have to show
difference between Currenttime and date from tcktReceived.
e'g
ItemRecieved Difference
6/13/2007 12:38am 1d 21h 45m
6/13/2007 3:54pm 1d 06h 10m
6/15/2007 12:26pm 34m
So the second coulmn displays the difference of current time minus
ItemRecieved. the format shouild be <mintues>m,
or<hours>h, <mintues>m, or <days>d.
How can I do that.
Thanks in advance
seema
|
|
|
|
|
Create a function to calcualte the difference and format it as you need it.
Something like this:
CREATE FUNCTION dbo.TimeDifference(@startTime DATETIME, @endTime DATETIME)
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @Diff int;
DECLARE @Day int;
DECLARE @Hour int;
DECLARE @Minute int;
SELECT @Diff = DATEDIFF(minute, @startTime, @endTime);
SELECT @Day = @Diff / 3600;
SELECT @Hour = (@Diff - (@Day*3600)) / 60;
SELECT @Minute = @Diff % 60
RETURN CAST( @Day AS VARCHAR(5)) + 'd ' +
CAST( @Hour AS VARCHAR(5)) + 'h ' +
CAST( @Minute AS VARCHAR(5)) + 'm';
END
GO
CREATE PROCEDURE dbo.MyProcedure
AS
BEGIN
SELECT
tck.tcktreceived,
tck.ticketid,
tckmsg.tcktmessage,
dbo.TimeDifference(tct.tckreceived, GETDATE())
FROM tbtickets AS tck
INNER JOIN tbticketsmessages AS tckmsg ON tck.ticketid = tckmsg.ticketid
END
GO
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
* Reading: Developer Day 5
Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton
My website
|
|
|
|
|
I have a stored proceure whic is returning a table. Now I need ADO.Net statement So that IO can pass parameters to this stored procedure and get Retuened table from stored procedure.
Could you please send me code how can I reterive a returen value of tyoe table from stroed procedure in ADO.Net
Tnanks.
seema
|
|
|
|
|
You don't return tables from a stored procedure, you SELECT them. It will then return as a result set.
Off the top of my head, something like this will work:
SqlConnection conn = new SqlConnection(myConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "MyStoredProcedureName";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@MyParameter", theValueOfTheParameter);
SqlDataReader reader = cmd.ExecuteReader();
while(reader.Read())
{
}
The stored procedure needs to SELECT the data, as I told you before, in order for it to be collected.
CREATE PROCEDURE dbo.MyStoredProcedureName
@MyParameter INT
BEGIN
SELECT * FROM MyTable WHERE SomeColumn = @MyParameter
END
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
* Reading: Developer Day 5
Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton
My website
|
|
|
|
|
Again thanks a lot but When I execute only this portion it gives me error
Syntax error converting the varchar value '2d 0h 0m' to a column of data
type int.
My stored procedure is the following.
Followed is the SQL Script and you can use this code in your SQL Function to
get the difference and make function with StartDate and EndDate parameters
and return the @Diff
DECLARE @Day INT
DECLARE @Hour INT
DECLARE @Minute INT
DECLARE @Start_Date DateTime
DECLARE @End_Date DateTime
SET @Start_Date = '6/13/2007 12:38 AM'
SET @End_Date = '6/14/2007 11:59 PM'
SET @Day = DATEDIFF( day, @Start_Date, @End_Date)
SET @Hour = DATEDIFF(hour , @Start_Date, @End_Date)
SET @Minute = DATEDIFF(minute , @Start_Date, @End_Date)
SET @Minute = @Minute-(@HOUR* 60)
SET @Hour = @Hour-(24* @Day)
SET @Diff = CONVERT( Varchar, @Day) +'d ' + CONVERT(Varchar , @Hour) + 'h '
+ CONVERT(Varchar , @Minute) +'m'
PRINT
'Difference : ' + @Diff
seema
|
|
|
|
|
Hi Seema
The @Diff variable should be something like a varchar(20). Try the following:
DECLARE @Day INT
DECLARE @Hour INT
DECLARE @Minute INT
DECLARE @Start_Date DateTime
DECLARE @End_Date DateTime
DECLARE @Diff VARCHAR(20)
SET @Start_Date = '12 jun 2007 12:36'
SET @End_Date = '14 jun 2007 12:35'
SET @Day = DATEDIFF(minute, @Start_Date, @End_Date) / (24 * 60)
SET @Hour = (DATEDIFF(minute, @Start_Date, @End_Date) / 60) % 24
SET @Minute = DATEDIFF(minute, @Start_Date, @End_Date) % 60
SET @Diff = RTRIM(
CASE WHEN @Day > 0 THEN CONVERT(Varchar, @Day) + 'd ' ELSE '' END +
CASE WHEN @Hour > 0 THEN CONVERT(Varchar, @Hour) + 'h ' ELSE '' END +
CASE WHEN @Minute> 0 THEN CONVERT(Varchar, @Minute) + 'm ' ELSE '' END)
PRINT 'Difference : ' + @Diff If you use it a lot then perhaps you should set this up as a UDF.
Regards
Andy
|
|
|
|
|
I have the following stroed procedue. But whebnnI execute it it gives me following errors.
Could you please tell me what is wrong
Msg 156, Level 15, State 1, Procedure usp_DateDiff, Line 43
Incorrect syntax near the keyword 'SELECT'.
Msg 156, Level 15, State 1, Procedure usp_DateDiff, Line 47
Incorrect syntax near the keyword 'select'.
Msg 156, Level 15, State 1, Procedure usp_DateDiff, Line 52
Incorrect syntax near the keyword 'select'.
Msg 137, Level 15, State 2, Procedure usp_DateDiff, Line 58
Must declare the variable '@table'.
Msg 137, Level 15, State 2, Procedure usp_DateDiff, Line 59
Must declare the variable '@table'.
alter PROCEDURE [twcsan].[usp_DateDiff]
-- Add the parameters for the stored procedure here
@endDate DateTime
AS
BEGIN
DECLARE @Diff INT
DECLARE @Day INT
DECLARE @Hour INT
DECLARE @Minute INT
DECLARE @Start_Date DateTime
DECLARE @End_Date DateTime
DECLARE @itemReceived DateTime
DECLARE @ID INT
DECLARE @message VARCHAR(50)
DECLARE @table TABLE
(
ItemReceived DateTime,
ID INT,
message VARCHAR(100),
Differnce VARCHAR(20)
)
SET NOCOUNT ON;
SET @Start_Date = DateTime.Now
SET @End_Date = @endDate
SET @Day = DATEDIFF( day, @Start_Date, @End_Date)
SET @Hour = DATEDIFF(hour , @Start_Date, @End_Date)
SET @Minute = DATEDIFF(minute , @Start_Date, @End_Date)
SET @Minute = @Minute-(@HOUR* 60)
SET @Hour = @Hour-(24* @Day)
SET @Diff = CONVERT(Varchar, @Day) +'d ' + CONVERT(Varchar , @Hour) + 'h ' + CONVERT(Varchar , @Minute) +'m'
SET @itemReceived = SELECT tck.tcktreceived
from tbtickets tck inner join tbticketsmessages tckmsg
on tck.ticketid = tckmsg.ticketid
SET @ID = select tck.ticketid
from tbtickets tck inner join tbticketsmessages tckmsg
on tck.ticketid = tckmsg.ticketid
SET @message = select tckmsg.tcktmessage
from tbtickets tck inner join tbticketsmessages tckmsg
on tck.ticketid = tckmsg.ticketid
INSERT INTO @table(ItemReceived, ID,message,Differnce)
Values(@itemReceived, @ID, @message, @Diff)
PRINT @table
return @table
END
seema
|
|
|
|
|
seemamltn wrote: SET @itemReceived = SELECT tck.tcktreceived
from tbtickets tck inner join tbticketsmessages tckmsg
on tck.ticketid = tckmsg.ticketid
Try
SELECT @itemReceived = tck.tcktreceived
FROM tbtickets AS tck
INNER JOIN tbticketsmessages AS tckmsg ON tck.ticketid = tckmsg.ticketid
The same goes for the other two SET s with a SELECT in them.
seemamltn wrote: PRINT @table
return @table
I don't think you can do either of those things.
If you want to return a result set from a stored procedure SELECT it:
SELECT * FROM @table
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
* Reading: Developer Day 5
Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton
My website
|
|
|
|
|
Again thanks a lot but When I execute only this portion it gives me error
Syntax error converting the varchar value '2d 0h 0m' to a column of data
type int.
My stored procedure is the following.
Followed is the SQL Script and you can use this code in your SQL Function to
get the difference and make function with StartDate and EndDate parameters
and return the @Diff
DECLARE @Day INT
DECLARE @Hour INT
DECLARE @Minute INT
DECLARE @Start_Date DateTime
DECLARE @End_Date DateTime
SET @Start_Date = '6/13/2007 12:38 AM'
SET @End_Date = '6/14/2007 11:59 PM'
SET @Day = DATEDIFF( day, @Start_Date, @End_Date)
SET @Hour = DATEDIFF(hour , @Start_Date, @End_Date)
SET @Minute = DATEDIFF(minute , @Start_Date, @End_Date)
SET @Minute = @Minute-(@HOUR* 60)
SET @Hour = @Hour-(24* @Day)
SET @Diff = CONVERT( Varchar, @Day) +'d ' + CONVERT(Varchar , @Hour) + 'h '
+ CONVERT(Varchar , @Minute) +'m'
PRINT
'Difference : ' + @Diff
seema
|
|
|
|
|
You have declared @Diff as an integer. You have supplied it with a string that cannot be interpreted as a number.
Either Diff is incorrectly declared, or you need to find a way to convert the string to an appropriate number.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
* Reading: Developer Day 5
Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton
My website
|
|
|
|
|