I am trying to use the copy database wizard in Enterprise
manager to copy a database from one MSDE installation to
another. It always returns the error:
"Your SQL Server Service is running under the local
system account. You need to to change your SQL Server
Service account to have the rights to copy files over the
Network."
When I try to change the service to run under a different
account I get another error, "The handle is invalid."
I got to thinking that perhaps MSDE doesn't allow you to
run under anything but local system. Can anyone
confirm? Or does anyone know what's wrong here?
hi bill,
"bill" <anonymous@.discussions.microsoft.com> ha scritto nel messaggio
news:2d14001c46a06$8a1cd570$a401280a@.phx.gbl...
> When I try to change the service to run under a different
> account I get another error, "The handle is invalid."
> I got to thinking that perhaps MSDE doesn't allow you to
> run under anything but local system. Can anyone
> confirm? Or does anyone know what's wrong here?
MSDE SQL Server service and SQLSERVERAgent service can run on diferent
account than LocalSystem with no problem [of mines =;-) ]
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||a little addition to "invalid" message...
please have a look at http://tinyurl.com/696hq if can help
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
Showing posts with label returns. Show all posts
Showing posts with label returns. Show all posts
Thursday, March 22, 2012
Sunday, February 19, 2012
converting to date only
I have a function on a MSSQL 2000 db like the following:
create function GetDateOnly (@.pInputDate datetime)
returns datetime
as
begin
return cast(convert(varchar(10), @.pInputDate, 111) as datetime)
end
which returns the date with the time all zeros ( '2006-05-09 00:00:00' ). I tried to implement this same function on an MSSQL 7 server and I get errors.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'function'.
How can I code something similar in version 7? I'm assuming it's a version difference that is causing my problems.
Thanks,
Randy7.0 did not support user-defined functions.
You could write a stored proc with an OUTPUT parameter to do this, but you could not include it in a SELECT statement as you can with a function.
You will probably need to code the logic each time you need it. But I suggest you use this formula instead, which is more efficient than the CONVERT method you are using:
create function GetDateOnly (@.pInputDate datetime)
returns datetime
as
begin
return dateadd(day, datediff(day, 0, getdate()), 0)
end|||thanks alot for the advise. I thought about stored procedure, but then like you mentioned I can't use it in a SELECT which was my whole goal. Back to the drawing board :-)
Randy
create function GetDateOnly (@.pInputDate datetime)
returns datetime
as
begin
return cast(convert(varchar(10), @.pInputDate, 111) as datetime)
end
which returns the date with the time all zeros ( '2006-05-09 00:00:00' ). I tried to implement this same function on an MSSQL 7 server and I get errors.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'function'.
How can I code something similar in version 7? I'm assuming it's a version difference that is causing my problems.
Thanks,
Randy7.0 did not support user-defined functions.
You could write a stored proc with an OUTPUT parameter to do this, but you could not include it in a SELECT statement as you can with a function.
You will probably need to code the logic each time you need it. But I suggest you use this formula instead, which is more efficient than the CONVERT method you are using:
create function GetDateOnly (@.pInputDate datetime)
returns datetime
as
begin
return dateadd(day, datediff(day, 0, getdate()), 0)
end|||thanks alot for the advise. I thought about stored procedure, but then like you mentioned I can't use it in a SELECT which was my whole goal. Back to the drawing board :-)
Randy
Sunday, February 12, 2012
Converting sp outcome
Hi all,
i have an stored procedure that returns a value, i want to convert that value into smallmoney, is this possible and i yes how?
here is my code:
CREATE proc CP_avgloss_total
@.mID varchar(10),
@.startdate datetime,
@.enddate datetime
as
select
case
((sum(playtime))/ 3600)
when 0
then 0
else ((sum(vtp)-(sum(moneyout)))/100) / ((sum(playtime))/ 3600)
end avgloss
from dbo.total
where
machineID = @.mID
and convert(varchar,njdate,121)
between convert(varchar,@.startdate,121)
and convert(varchar,@.enddate,121)
GO
The value that needs to convert is avgloss
Hope someone can help me with this.
Cheers Wimselect cast(P1.avgloss as smallmoney) as avgloss from
(select
case
((sum(playtime))/ 3600)
when 0
then 0
else ((sum(vtp)-(sum(moneyout)))/100) / ((sum(playtime))/ 3600)
end avgloss
from dbo.total
where
machineID = @.mID
and convert(varchar,njdate,121)
between convert(varchar,@.startdate,121)
and convert(varchar,@.enddate,121)) P1|||Originally posted by marp
select cast(P1.avgloss as smallmoney) as avgloss from
(select
case
((sum(playtime))/ 3600)
when 0
then 0
else ((sum(vtp)-(sum(moneyout)))/100) / ((sum(playtime))/ 3600)
end avgloss
from dbo.total
where
machineID = @.mID
and convert(varchar,njdate,121)
between convert(varchar,@.startdate,121)
and convert(varchar,@.enddate,121)) P1
It worked thanx but i still get as outcome 4 digit behind the break
Do you know how to get 2 digits?
Thanx|||As far as I know, the smallmoney data type uses by default 4 decimal digits. If different number of decimal places are needed, "Books Online" recommends using the Decimal data type.|||Originally posted by marp
As far as I know, the smallmoney data type uses by default 4 decimal digits. If different number of decimal places are needed, "Books Online" recommends using the Decimal data type.
Thanx Man this is everything i need.
Your help was fantastic!
Kind regards Wim
i have an stored procedure that returns a value, i want to convert that value into smallmoney, is this possible and i yes how?
here is my code:
CREATE proc CP_avgloss_total
@.mID varchar(10),
@.startdate datetime,
@.enddate datetime
as
select
case
((sum(playtime))/ 3600)
when 0
then 0
else ((sum(vtp)-(sum(moneyout)))/100) / ((sum(playtime))/ 3600)
end avgloss
from dbo.total
where
machineID = @.mID
and convert(varchar,njdate,121)
between convert(varchar,@.startdate,121)
and convert(varchar,@.enddate,121)
GO
The value that needs to convert is avgloss
Hope someone can help me with this.
Cheers Wimselect cast(P1.avgloss as smallmoney) as avgloss from
(select
case
((sum(playtime))/ 3600)
when 0
then 0
else ((sum(vtp)-(sum(moneyout)))/100) / ((sum(playtime))/ 3600)
end avgloss
from dbo.total
where
machineID = @.mID
and convert(varchar,njdate,121)
between convert(varchar,@.startdate,121)
and convert(varchar,@.enddate,121)) P1|||Originally posted by marp
select cast(P1.avgloss as smallmoney) as avgloss from
(select
case
((sum(playtime))/ 3600)
when 0
then 0
else ((sum(vtp)-(sum(moneyout)))/100) / ((sum(playtime))/ 3600)
end avgloss
from dbo.total
where
machineID = @.mID
and convert(varchar,njdate,121)
between convert(varchar,@.startdate,121)
and convert(varchar,@.enddate,121)) P1
It worked thanx but i still get as outcome 4 digit behind the break
Do you know how to get 2 digits?
Thanx|||As far as I know, the smallmoney data type uses by default 4 decimal digits. If different number of decimal places are needed, "Books Online" recommends using the Decimal data type.|||Originally posted by marp
As far as I know, the smallmoney data type uses by default 4 decimal digits. If different number of decimal places are needed, "Books Online" recommends using the Decimal data type.
Thanx Man this is everything i need.
Your help was fantastic!
Kind regards Wim
Converting Select query to Update
Hi,
I need to update some data based on the results of a select query. The
following select statement returns the values:
SELECT DISTINCT A.itemid, B.DateSigned, B.RefID, C.EnteredBy,
C.Action, B.Status, C.dateEntered
FROM A INNER JOIN
B ON A.itemid = B.RecordNumber INNER JOIN
C ON A.itemid = C.recordid LEFT OUTER JOIN
D ON A.itemid = D.DemoRecordID
WHERE (D.itemid IS NULL)
and B.status = 'app'
and C.action like '%signed%'
ORDER BY A.itemid DESC
What I want to do is update DataSigned as follows.
UPDATE B SET B.DateSigned=C.dateEntered
WHERE '?
I am not sure how to set up the WHERE clause to update the correct records
with the correct values. Suggestions?
Thanks,
JerryYou can try the following:
UPDATE B
SET B.DateSigned = C.dateEntered
FROM B
inner join ( SELECT DISTINCT A.itemid
, B.DateSigned
, B.RefID
, C.EnteredBy
, C.Action
, B.Status
, C.dateEntered
FROM A
INNER JOIN B
ON A.itemid = B.RecordNumber
INNER JOIN C
ON A.itemid = C.recordid
LEFT OUTER JOIN D
ON A.itemid = D.DemoRecordID
WHERE (D.itemid IS NULL)
and B.status = 'app'
and C.action like '%signed%'
) C
on C.ItemID = B.RecordNumber
You may want to check the join clause to make sure you match the records
exactly. In any case, the idea is to use derived tables, which is the one
that is created on-the-fly using the SELECT statement, and referenced just
like a regular table or view
Let me know if it helps
"JerryK" wrote:
> Hi,
> I need to update some data based on the results of a select query. The
> following select statement returns the values:
> SELECT DISTINCT A.itemid, B.DateSigned, B.RefID, C.EnteredBy,
> C.Action, B.Status, C.dateEntered
> FROM A INNER JOIN
> B ON A.itemid = B.RecordNumber INNER JOIN
> C ON A.itemid = C.recordid LEFT OUTER JOIN
> D ON A.itemid = D.DemoRecordID
> WHERE (D.itemid IS NULL)
> and B.status = 'app'
> and C.action like '%signed%'
> ORDER BY A.itemid DESC
>
> What I want to do is update DataSigned as follows.
> UPDATE B SET B.DateSigned=C.dateEntered
> WHERE '?
> I am not sure how to set up the WHERE clause to update the correct records
> with the correct values. Suggestions?
> Thanks,
> Jerry
>
>|||something like this (completely untested):
UPDATE B
SET DateSigned=(
SELECT C.dateEntered
FROM A INNER JOIN
C ON A.itemid = C.recordid LEFT OUTER JOIN
D ON A.itemid = D.DemoRecordID
WHERE D.itemid IS NULL
and C.action like '%signed%'
and A.itemid = B.RecordNumber )
WHERE status = 'app'
dean
"JerryK" <jerryk@.nospam.com> wrote in message
news:%23S1clJfIGHA.2668@.tk2msftngp13.phx.gbl...
> Hi,
> I need to update some data based on the results of a select query. The
> following select statement returns the values:
> SELECT DISTINCT A.itemid, B.DateSigned, B.RefID, C.EnteredBy,
> C.Action, B.Status, C.dateEntered
> FROM A INNER JOIN
> B ON A.itemid = B.RecordNumber INNER JOIN
> C ON A.itemid = C.recordid LEFT OUTER JOIN
> D ON A.itemid = D.DemoRecordID
> WHERE (D.itemid IS NULL)
> and B.status = 'app'
> and C.action like '%signed%'
> ORDER BY A.itemid DESC
>
> What I want to do is update DataSigned as follows.
> UPDATE B SET B.DateSigned=C.dateEntered
> WHERE '?
> I am not sure how to set up the WHERE clause to update the correct records
> with the correct values. Suggestions?
> Thanks,
> Jerry
>|||On Wed, 25 Jan 2006 13:00:37 -0800, JerryK wrote:
(snip)
Hi Jerry,
I just answered this question in microsoft.public.sqlserver.newusers.
In the future, please post your questions to one group only. And if you
really feel that a question should be in two groups, crosspost it (i.e.
send one copy to both groups at the same time) instead of sending
independent copies to the groups. With crossposting, all replies will
(normally) show up in both groups as well. That saves others the time
and energy to find an answer if the question already was answered
elsewhere!
Hugo Kornelis, SQL Server MVP
I need to update some data based on the results of a select query. The
following select statement returns the values:
SELECT DISTINCT A.itemid, B.DateSigned, B.RefID, C.EnteredBy,
C.Action, B.Status, C.dateEntered
FROM A INNER JOIN
B ON A.itemid = B.RecordNumber INNER JOIN
C ON A.itemid = C.recordid LEFT OUTER JOIN
D ON A.itemid = D.DemoRecordID
WHERE (D.itemid IS NULL)
and B.status = 'app'
and C.action like '%signed%'
ORDER BY A.itemid DESC
What I want to do is update DataSigned as follows.
UPDATE B SET B.DateSigned=C.dateEntered
WHERE '?
I am not sure how to set up the WHERE clause to update the correct records
with the correct values. Suggestions?
Thanks,
JerryYou can try the following:
UPDATE B
SET B.DateSigned = C.dateEntered
FROM B
inner join ( SELECT DISTINCT A.itemid
, B.DateSigned
, B.RefID
, C.EnteredBy
, C.Action
, B.Status
, C.dateEntered
FROM A
INNER JOIN B
ON A.itemid = B.RecordNumber
INNER JOIN C
ON A.itemid = C.recordid
LEFT OUTER JOIN D
ON A.itemid = D.DemoRecordID
WHERE (D.itemid IS NULL)
and B.status = 'app'
and C.action like '%signed%'
) C
on C.ItemID = B.RecordNumber
You may want to check the join clause to make sure you match the records
exactly. In any case, the idea is to use derived tables, which is the one
that is created on-the-fly using the SELECT statement, and referenced just
like a regular table or view
Let me know if it helps
"JerryK" wrote:
> Hi,
> I need to update some data based on the results of a select query. The
> following select statement returns the values:
> SELECT DISTINCT A.itemid, B.DateSigned, B.RefID, C.EnteredBy,
> C.Action, B.Status, C.dateEntered
> FROM A INNER JOIN
> B ON A.itemid = B.RecordNumber INNER JOIN
> C ON A.itemid = C.recordid LEFT OUTER JOIN
> D ON A.itemid = D.DemoRecordID
> WHERE (D.itemid IS NULL)
> and B.status = 'app'
> and C.action like '%signed%'
> ORDER BY A.itemid DESC
>
> What I want to do is update DataSigned as follows.
> UPDATE B SET B.DateSigned=C.dateEntered
> WHERE '?
> I am not sure how to set up the WHERE clause to update the correct records
> with the correct values. Suggestions?
> Thanks,
> Jerry
>
>|||something like this (completely untested):
UPDATE B
SET DateSigned=(
SELECT C.dateEntered
FROM A INNER JOIN
C ON A.itemid = C.recordid LEFT OUTER JOIN
D ON A.itemid = D.DemoRecordID
WHERE D.itemid IS NULL
and C.action like '%signed%'
and A.itemid = B.RecordNumber )
WHERE status = 'app'
dean
"JerryK" <jerryk@.nospam.com> wrote in message
news:%23S1clJfIGHA.2668@.tk2msftngp13.phx.gbl...
> Hi,
> I need to update some data based on the results of a select query. The
> following select statement returns the values:
> SELECT DISTINCT A.itemid, B.DateSigned, B.RefID, C.EnteredBy,
> C.Action, B.Status, C.dateEntered
> FROM A INNER JOIN
> B ON A.itemid = B.RecordNumber INNER JOIN
> C ON A.itemid = C.recordid LEFT OUTER JOIN
> D ON A.itemid = D.DemoRecordID
> WHERE (D.itemid IS NULL)
> and B.status = 'app'
> and C.action like '%signed%'
> ORDER BY A.itemid DESC
>
> What I want to do is update DataSigned as follows.
> UPDATE B SET B.DateSigned=C.dateEntered
> WHERE '?
> I am not sure how to set up the WHERE clause to update the correct records
> with the correct values. Suggestions?
> Thanks,
> Jerry
>|||On Wed, 25 Jan 2006 13:00:37 -0800, JerryK wrote:
(snip)
Hi Jerry,
I just answered this question in microsoft.public.sqlserver.newusers.
In the future, please post your questions to one group only. And if you
really feel that a question should be in two groups, crosspost it (i.e.
send one copy to both groups at the same time) instead of sending
independent copies to the groups. With crossposting, all replies will
(normally) show up in both groups as well. That saves others the time
and energy to find an answer if the question already was answered
elsewhere!
Hugo Kornelis, SQL Server MVP
Converting seconds to HHMMSS
My code calculates a duration of a start and end dates. It then converts the
duration into "
HHMMSS" format. My sample returns 0:0:3 which means 3 seconds.
Can someone help me modify my code so that the result would be 0:0:03 and
add the extra "padding 0" when the hours, minutes, or seconds are 1 digit?
CODE **************
declare @.dtStartDate datetime, @.dtEndDate datetime, @.duration as int
set @.dtStartDate = '20060314 01:39:14'
set @.dtEndDate = '20060314 01:39:17'
set @.duration = datediff(s,@.dtStartDate,@.dtEndDate)
select rtrim(@.duration/3600) + ':' + rtrim(@.duration % 3600/60) + ':' +
rtrim(@.duration
% 60)does this work for you?
declare @.dtStartDate datetime, @.dtEndDate datetime, @.duration as int
set @.dtStartDate = '20060314 01:39:14'
set @.dtEndDate = '20060314 01:39:17'
with 2 zeros
select convert(varchar,convert(datetime,
dateadd(s,datediff(s,@.dtStartDate,@.dtEnd
Date),'19000101' )),108)
one zero
select replace(convert(varchar,convert(datetime
,
dateadd(s,datediff(s,@.dtStartDate,@.dtEnd
Date),'19000101'
)),108),'00','0')
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||that'll do it. thanks.
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1145902342.424509.319660@.y43g2000cwc.googlegroups.com...
> does this work for you?
> declare @.dtStartDate datetime, @.dtEndDate datetime, @.duration as int
> set @.dtStartDate = '20060314 01:39:14'
> set @.dtEndDate = '20060314 01:39:17'
> with 2 zeros
> select convert(varchar,convert(datetime,
> dateadd(s,datediff(s,@.dtStartDate,@.dtEnd
Date),'19000101' )),108)
> one zero
> select replace(convert(varchar,convert(datetime
,
> dateadd(s,datediff(s,@.dtStartDate,@.dtEnd
Date),'19000101'
> )),108),'00','0')
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>|||Scott,
Try using the function "right".
select right('00' + rtrim(@.duration/3600), 2) + ':' + right('00' +
rtrim(@.duration % 3600 / 60), 2) + ':' + right('00' + rtrim(@.duration % 60),
2)
Be careful with the solution posted by SQL, It does not seem to work for
duration greater than 24 hours.
AMB
"Scott" wrote:
> that'll do it. thanks.
>
> "SQL" <denis.gobo@.gmail.com> wrote in message
> news:1145902342.424509.319660@.y43g2000cwc.googlegroups.com...
>
>|||> Be careful with the solution posted by SQL, It does not seem to work for
> duration greater than 24 hours.
Neither this one when the duration is greater than or equal to 100 hours. we
need to get more than two digits.
> select right('00' + rtrim(@.duration/3600), 2) <--
AMB
"Alejandro Mesa" wrote:
> Scott,
> Try using the function "right".
> select right('00' + rtrim(@.duration/3600), 2) + ':' + right('00' +
> rtrim(@.duration % 3600 / 60), 2) + ':' + right('00' + rtrim(@.duration % 60
),
> 2)
> Be careful with the solution posted by SQL, It does not seem to work for
> duration greater than 24 hours.
>
> AMB
> "Scott" wrote:
>
duration into "
HHMMSS" format. My sample returns 0:0:3 which means 3 seconds.
Can someone help me modify my code so that the result would be 0:0:03 and
add the extra "padding 0" when the hours, minutes, or seconds are 1 digit?
CODE **************
declare @.dtStartDate datetime, @.dtEndDate datetime, @.duration as int
set @.dtStartDate = '20060314 01:39:14'
set @.dtEndDate = '20060314 01:39:17'
set @.duration = datediff(s,@.dtStartDate,@.dtEndDate)
select rtrim(@.duration/3600) + ':' + rtrim(@.duration % 3600/60) + ':' +
rtrim(@.duration
% 60)does this work for you?
declare @.dtStartDate datetime, @.dtEndDate datetime, @.duration as int
set @.dtStartDate = '20060314 01:39:14'
set @.dtEndDate = '20060314 01:39:17'
with 2 zeros
select convert(varchar,convert(datetime,
dateadd(s,datediff(s,@.dtStartDate,@.dtEnd
Date),'19000101' )),108)
one zero
select replace(convert(varchar,convert(datetime
,
dateadd(s,datediff(s,@.dtStartDate,@.dtEnd
Date),'19000101'
)),108),'00','0')
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||that'll do it. thanks.
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1145902342.424509.319660@.y43g2000cwc.googlegroups.com...
> does this work for you?
> declare @.dtStartDate datetime, @.dtEndDate datetime, @.duration as int
> set @.dtStartDate = '20060314 01:39:14'
> set @.dtEndDate = '20060314 01:39:17'
> with 2 zeros
> select convert(varchar,convert(datetime,
> dateadd(s,datediff(s,@.dtStartDate,@.dtEnd
Date),'19000101' )),108)
> one zero
> select replace(convert(varchar,convert(datetime
,
> dateadd(s,datediff(s,@.dtStartDate,@.dtEnd
Date),'19000101'
> )),108),'00','0')
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>|||Scott,
Try using the function "right".
select right('00' + rtrim(@.duration/3600), 2) + ':' + right('00' +
rtrim(@.duration % 3600 / 60), 2) + ':' + right('00' + rtrim(@.duration % 60),
2)
Be careful with the solution posted by SQL, It does not seem to work for
duration greater than 24 hours.
AMB
"Scott" wrote:
> that'll do it. thanks.
>
> "SQL" <denis.gobo@.gmail.com> wrote in message
> news:1145902342.424509.319660@.y43g2000cwc.googlegroups.com...
>
>|||> Be careful with the solution posted by SQL, It does not seem to work for
> duration greater than 24 hours.
Neither this one when the duration is greater than or equal to 100 hours. we
need to get more than two digits.
> select right('00' + rtrim(@.duration/3600), 2) <--
AMB
"Alejandro Mesa" wrote:
> Scott,
> Try using the function "right".
> select right('00' + rtrim(@.duration/3600), 2) + ':' + right('00' +
> rtrim(@.duration % 3600 / 60), 2) + ':' + right('00' + rtrim(@.duration % 60
),
> 2)
> Be careful with the solution posted by SQL, It does not seem to work for
> duration greater than 24 hours.
>
> AMB
> "Scott" wrote:
>
Converting Rows into Columns MS SQL 2K
I have a SP that returns the information I want but it returns it in 2
separate queries.
separate queries.
Example:
Query 1
Name, Number, Class
Row 1- Mike Phillips, 154AA, AA
and
Query 2
Time, Manual
Row 1 -12:45:22,0
Row 2 -13:04:56,0
What I want it to look like is:
Name, Number, Class, Time 1, Manual 1, Time 2, Manual 2
Row 1- Mike Phillips, 154AA, AA, 12:45:22, 0, 13:04:56, 0
Here is the query I'm using:
DECLARE Class cursor
FOR
--here we get a list of distinct classes to pass to the Class cursor
select Distinct(class_ID) from kt_member_lap
where Race_ID = 83
order by Class_ID;
OPEN Class;
DECLARE @.RaceID int
DECLARE@.RacerCount int
DECLARE @.ClassID char(50)
DECLARE @.classcount
DECLARE @.Racer char(50)
DECLARE @.i int
SET @.RaceID = 83
--this is where we loop through the classes
FETCH NEXT FROM Class INTO @.ClassID
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
IF (@.@.FETCH_STATUS <> -2)
DECLARE Lap cursor
FOR
Select DISTINCT(Member_ID) from KT_MEMBER_LAP
Where class_ID = @.classID and race_id = @.RaceID
OPEN Lap;
--this is to begin counting from the first lap
SET @.i = 1;
FETCH NEXT FROM Lap INTO @.Racer
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
IF (@.@.FETCH_STATUS <> -2)
SELECT KT_MEMBER.MEMBER_FNAME + ' ' +
KT_MEMBER.MEMBER_LNAME As MemberName,
CONVERT(nvarchar(3),
KT_MEMBER_CLASS.MEMBER_CLASS_BIKE_NUM) + KT_CLASS.CLASS_LETTER As
BikeNumber,
KT_CLASS.CLASS_DESC
FROM KT_CLASS INNER JOIN
KT_MEMBER_CLASS ON KT_CLASS.CLASS_ID =
KT_MEMBER_CLASS.CLASS_ID INNER JOIN
KT_MEMBER ON KT_MEMBER_CLASS.MEMBER_ID =
KT_MEMBER.MEMBER_ID
WHERE KT_MEMBER.MEMBER_ID = @.Racer and KT_CLASS.CLASS_ID =
@.ClassID
--SELECT @.Racer, @.ClassID
Select MEMBER_LAP_TIME_REAL, member_lap_manual from KT_MEMBER_LAP
Where Member_ID = @.Racer and class_ID = @.classID and race_id =
@.RaceID
ORDER BY MEMBER_LAP_TIME_REAL
--here I count up for the next lap
SET @.i = @.i + 1;
FETCH NEXT FROM Lap INTO @.Racer
END
CLOSE Lap;
DEALLOCATE Lap;
FETCH NEXT FROM Class INTO @.ClassID
END
CLOSE Class;
DEALLOCATE Class;
Any help would be appreciated.[posted and mailed, please reply in news]
dare197 (daniel.white@.perceptivetech.com) writes:
> I have a SP that returns the information I want but it returns it in 2
> separate queries.
> Example:
> Query 1
> Name, Number, Class
> Row 1- Mike Phillips, 154AA, AA
> and
> Query 2
> Time, Manual
> Row 1 -12:45:22,0
> Row 2 -13:04:56,0
> What I want it to look like is:
> Name, Number, Class, Time 1, Manual 1, Time 2, Manual 2
> Row 1- Mike Phillips, 154AA, AA, 12:45:22, 0, 13:04:56, 0
Could there be any number of Time, Manual rows or is there never more
than two? I will assume that you always have two. Then you can try
this query:
SELECT m.MEMBER_FNAME + ' ' + m.MEMBER_LNAME As MemberName,
CONVERT(nvarchar(3), mc.MEMBER_CLASS_BIKE_NUM) +
c.CLASS_LETTER As BikeNumber,
c.CLASS_DESC,
ml1.MEMBER_LAP_TIME_REAL AS "Time 1",
ml1.member_lap_manual AS "Manual 1",
ml2.MEMBER_LAP_TIME_REAL AS "Time 2",
ml2.member_lap_manual AS "Manual 2",
FROM KT_CLASS c
JOIN KT_MEMBER_CLASS mc ON c.CLASS_ID = mc.CLASS_ID
JOIN KT_MEMBER m ON mc.MEMBER_ID = m.MEMBER_ID
JOIN KT_MEMBER_LAP ml1 ON ml1.MEMBER_ID = m.MEMBER_ID
AND mll.CLASS_ID = mc.CLASS_ID
JOIN KT_MEMBER_LAP ml2 ON ml2.MEMBER_ID = m.MEMBER_ID
AND ml2.CLASS_ID = mc.CLASS_ID
AND ml2.member_lap_manual > ml1.member_lap_manual
WHERE m.MEMBER_ID = @.Racer
AND c.CLASS_ID = @.ClassID
ORDER BY c.CLASS_ID, m.MEMBER_ID
Here I have collapsed everything into one query, without any cursor, as
I could see no need for a cursor. Cursors can be a magnitude slower than
set-based statements, so there all reasons to avoid them.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Converting Output Dates
My input data is in the date format: DD/MM/YY. I have 2 parameters Start date and end date. The query runs fine and returns the data between start and end date.
The problem is that it converts the dates into MM/DD/YY. What it does is it converts 01/12/05 (1st December 2005) to display 12/01/2005 (12th January 2005).
Also when i export the data its format shows 12 as the date instead of month.
The server is in AUstralia and running on British format (DD/MM/YY).
Any help will be appreciated.
Thanks,
Nit
What's the client culture when you export the report? And what is the server OS culture? Does the RDL have a specified language setting?
We use the client culture when formatting the parameter value, and server OS culture when formatting the textbox value in the report (if there's no language specified in the RDL).
- Fang
Subscribe to:
Posts (Atom)