I haven't been able to find the Dateserial function in SQL that corresponds to the one in Access.
thx
jmI don't think you can use a straigt forward command as
DateSerial(Year,Month,Day) in MS SQL.
You just have to join your colums and use the "convert" command in MS SQL.
MS SQL stores datetime formats in default by 'YYYYMMDD'.
so it would maybe be like this
convert ( datetime, @.columnYear+@.columnMonth+@.columnDay, 1).
Is there a better way? Anyone?|||Should work, assuming that @.columnYear+@.columnMonth+@.columnDay can be evaluated as string in the format YYYYMMDD. So make sure you have a leading '0' for month and days < 10. If you are not sure about that, you can enfore a leading zero by
RIGHT('0000'+@.columnYear, 4) + RIGHT('00'+@.columnMonth, 2) + RIGHT('00'+@.columnDay, 2)|||Does anyone test the code they post?
Patrick Chua SQL SERVER stores dates in either 2 4-byte integers or 2 2-byte integers not in YYYYMMDD format.
jmayo two suggestion:
-- =============================================
-- Create scalar function (FN)
-- =============================================
IF object_ID('dbo.DateSerial') is not null
DROP FUNCTION DateSerial
GO
CREATE FUNCTION DateSerial(
@.Year varchar(4)
, @.Month varchar(2)
, @.Day varchar(2))
RETURNS datetime
AS
BEGIN
declare @.Date datetime
select @.Date = convert(datetime, @.Year + '/' + @.Month + '/' + @.Day)
return @.Date
END
GO
-- =============================================
-- Example to execute function
-- =============================================
SELECT dbo.DateSerial('2003','8','15')
go
OR
declare @.columnYear varchar(4), @.columnMonth varchar(2), @.columnDay varchar(2)
select @.columnYear = '2003'
, @.columnMonth = '8'
, @.columnDay = '15'
select convert(datetime, @.columnYear + '/' + @.columnMonth + '/' + @.columnDay)|||Originally posted by Paul Young
Does anyone test the code they post?
Patrick Chua SQL SERVER stores dates in either 2 4-byte integers or 2 2-byte integers not in YYYYMMDD format.
Hi Paul, did you test my code? It works as fine as yours. But maybe do you have other regional settings. Actually, the style of the date format is as follows:
In the table, the two columns on the left represent the style values for datetime or smalldatetime conversion to character data. Add 100 to a style value to get a four-place year that includes the century (yyyy).
- 0 or 100 (*) Default mon dd yyyy hh:miAM (or PM)
1 101 USA mm/dd/yy
2 102 ANSI yy.mm.dd
3 103 British/French dd/mm/yy
4 104 German dd.mm.yy
5 105 Italian dd-mm-yy
6 106 - dd mon yy
7 107 - mon dd, yy
8 108 - hh:mm:ss
- 9 or 109 (*) Default + milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM)
10 110 USA mm-dd-yy
11 111 JAPAN yy/mm/dd
12 112 ISO yymmdd
- 13 or 113 (*) Europe default + milliseconds dd mon yyyy hh:mm:ss:mmm(24h)
14 114 - hh:mi:ss:mmm(24h)
- 20 or 120 (*) ODBC canonical yyyy-mm-dd hh:mi:ss(24h)
- 21 or 121 (*) ODBC canonical (with milliseconds) yyyy-mm-dd hh:mi:ss.mmm(24h)
:rolleyes:|||DoktorBlue re-read the first two words in your reply... "Should work" I took that to mean Patrick's suggestion should work which it doesn't.
The style argument to CONVERT is used when converting datetime/smalldateime to character data or converting numeric data to character data NOT the other way around.
I do agree with you regarding leading zeros.|||Originally posted by Paul Young
DoktorBlue re-read the first two words in your reply... "Should work" I took that to mean Patrick's suggestion should work which it doesn't.
The style argument to CONVERT is used when converting datetime/smalldateime to character data or converting numeric data to character data NOT the other way around.
I do agree with you regarding leading zeros.
I hate to say this, but Paul you are wrong at all points:
1) It works at my PC, maybe your regional settings prevent you from getting the result, but here is the tested code:
SELECT convert(DateTime, '20030812'), convert(DateTime, '2003/08/12')
2) The style argument is both input (string --> datetime) as output (datetime --> char)! see this code demonstration:
declare @.D as datetime
set @.d= convert(datetime, '12.8.2003',104)
select @.d, convert(varchar(255), @.d, 7)
go
3) Leading zeros are only needed for an implicit format. See my example style 104, which does accept the 8 without a leading 0. So, this style would be a candidate for a more stable DateSerial procedure.
Cheers|||Yeah, Paul, cheer up, it all works, starting with Patrick's statement:
declare @.columnYear char(4), @.columnMonth char(2), @.columnDay char(2)
select @.columnYear = '1992', @.columnMonth = '01', @.columnDay = '15'
select convert ( datetime, @.columnYear+@.columnMonth+@.columnDay)
select convert(datetime, RIGHT('0000'+@.columnYear, 4) + RIGHT('00'+@.columnMonth, 2) + RIGHT('00'+@.columnDay, 2))
But the original question before the p***ing contest started was about DateSerial equivalent. I think Paul started good, except for taking VARCHAR's as parameters. How about changing them to INT's, Paul?|||jmayo, If I can be of help with your problem please post back OR contact me off line.|||Originally posted by Paul Young
jmayo, If I can be of help with your problem please post back OR contact me off line.
Hey Paul,
Knowledge is Power. Get Some!
Ain't you man enough to accept your shotcomings. We are all learning, right?|||Datetime is NOT stored in 'YYYYMMDD' format.
RIGHT('0000'+@.columnYear, 4) + RIGHT('00'+@.columnMonth, 2) + RIGHT('00'+@.columnDay, 2) fails for 2-digit year values unless you are living in the first century.
select @.Date = convert(datetime, @.Year + '/' + @.Month + '/' + @.Day) is the simplest, most direct approach.
Get off Paul's case.
blindman|||blindman: are you on a mission? What's its name?|||I want to thank all of you for your replies. I was unable to reply until now because of the power outage. I live in Michigan.
I've been using SQL for about 1 week so, I'm trying to learn on the fly.
I actually was using the Convert function just having trouble using it in the VIEW I'm creating. I'm trying to convert a Julian date. WHich I've been able to do over the years in other software packages, AS400QUERY, Access, Excel, etc... Thanks Again..
jm|||jmayo maybe you could post the code for your view and indicate the problem or error you are getting.
If you wish to convert a Julian date to a Gregorian date you can use:
declare @.JD int, @.i int, @.J int, @.k int
, @.l real, @.n int
set @.JD = 2440588
set @.L = @.JD + 68569
set @.N = 4 * @.L /146097
set @.L = @.L - (146097 * @.N + 3) / 4
set @.I = 4000 * (@.L+ 1) / 1461001
set @.L = @.L - 1461 * @.I / 4 + 31
set @.J = 80 * @.L / 2447
set @.K = @.L - 2447 * @.J / 80
set @.L = @.J / 11
set @.J = @.J + 2 - 12 * @.L
set @.I = 100 * (@.N - 49) + @.I + @.L
select @.j,@.k,@.i
select cast(@.j as varchar) + '/' + cast(@.k as varchar) + '/' + cast(@.i as varchar)
select convert(datetime,cast(@.j as varchar) + '/' + cast(@.k as varchar) + '/' + cast(@.i as varchar) )
courtisy of: http://aa.usno.navy.mil/faq/docs/JD_Formula.html|||This link has some sql code for converting to and from julian date format. You may have to change the constant for your system.
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=341&lngWId=5
blindman|||Once you get the year extrapilated you only need to add the day-1 of the year to most date functions to get the proper translation.
I should be all set now.
Thanks again|||Example QUASI-JULIAN DATE - 103001
CONVERT (datetime, LEFT (CONVERT (decimal, 1900 + dbo.DAILYSALE2.QSDGL / 1000), 4)) + CONVERT (decimal, RIGHT (dbo.DAILYSALE2.QSDGL, 3)) - 1
This seems to work as a formula within a view. QSDGL being the field that stores the QJDATE.Actually Decimal should be Integer in the convert parameter.
Anyway. Sorry to start all this contraversy.
thx
Showing posts with label function. Show all posts
Showing posts with label function. Show all posts
Tuesday, March 27, 2012
Sunday, March 25, 2012
access data from another sqlserver d.b
I have two databases on the same server and would like to access data from
both. In oracle there is a 'dblink' function that allows you to do this. Wha
t
is the procedure in sqlserver ?
DonIf its the same server and the same instance you can use the epoint
notation, naming the object sas followed:
Databasename.Owner.ObjectsName
If you want to connect to a remote server you can also expand this to:
Servername.Databasename.Owner.ObjectsName
but therefore you have to configure a linked server.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"don" <don@.discussions.microsoft.com> schrieb im Newsbeitrag
news:D2D6750B-DF74-4B41-B578-864258273034@.microsoft.com...
>I have two databases on the same server and would like to access data
>from
> both. In oracle there is a 'dblink' function that allows you to do this.
> What
> is the procedure in sqlserver ?
> Don|||Assuming two databases in the same SQL Server instance: just qualify with th
e database name:
SELECT ...
FROM loctbl INNER JOIN otherdb.dbo.othertbl
...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"don" <don@.discussions.microsoft.com> wrote in message
news:D2D6750B-DF74-4B41-B578-864258273034@.microsoft.com...
>I have two databases on the same server and would like to access data fro
m
> both. In oracle there is a 'dblink' function that allows you to do this. W
hat
> is the procedure in sqlserver ?
> Don
both. In oracle there is a 'dblink' function that allows you to do this. Wha
t
is the procedure in sqlserver ?
DonIf its the same server and the same instance you can use the epoint
notation, naming the object sas followed:
Databasename.Owner.ObjectsName
If you want to connect to a remote server you can also expand this to:
Servername.Databasename.Owner.ObjectsName
but therefore you have to configure a linked server.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"don" <don@.discussions.microsoft.com> schrieb im Newsbeitrag
news:D2D6750B-DF74-4B41-B578-864258273034@.microsoft.com...
>I have two databases on the same server and would like to access data
>from
> both. In oracle there is a 'dblink' function that allows you to do this.
> What
> is the procedure in sqlserver ?
> Don|||Assuming two databases in the same SQL Server instance: just qualify with th
e database name:
SELECT ...
FROM loctbl INNER JOIN otherdb.dbo.othertbl
...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"don" <don@.discussions.microsoft.com> wrote in message
news:D2D6750B-DF74-4B41-B578-864258273034@.microsoft.com...
>I have two databases on the same server and would like to access data fro
m
> both. In oracle there is a 'dblink' function that allows you to do this. W
hat
> is the procedure in sqlserver ?
> Don
Thursday, March 22, 2012
Access and MSDE
how to i can connect the access database and the msde server?
i try whit the "odbc databases" function of access 2000 but not work
sql state 01000
errore di sqlserver
10060 impossibile effettuare la connessione
sql state 08001
errore di sqlserver 17
sorry for my english... ;(
IgnazioC
hi Ignazio,
IgnazioC wrote:
> how to i can connect the access database and the msde server?
> i try whit the "odbc databases" function of access 2000 but not work
> sql state 01000
> errore di sqlserver
> 10060 impossibile effettuare la connessione
> sql state 08001
> errore di sqlserver 17
10060 is a timeout problem. Make sure that you don't have firewall issues
between the client and server. Even personal firewalls on the Server will
block this traffic by default.
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||before it worked,but to the unexpected one it did not work more.
I have changed the name of the pc and all now it works.
why happens this? my name of the pc are "locked" on msde sever? as I
can unlock it?
|||hi Ignazio,
IgnazioC wrote:
> before it worked,but to the unexpected one it did not work more.
> I have changed the name of the pc and all now it works.
> why happens this? my name of the pc are "locked" on msde sever? as I
> can unlock it?
as regard MSDE by it self, there's no problem at all... this was a problem
with SQL Server 7.0/MSDE 1.0 but it is no longer the case...
you can have now problems regarding ie: the registration on Enterprise
Manager, and, re-registering servers on Enterprise Manager UI will solve EM
problems..
or, of course, net problems due to not resolved computer names for firewall
as well...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
sql
i try whit the "odbc databases" function of access 2000 but not work
sql state 01000
errore di sqlserver
10060 impossibile effettuare la connessione
sql state 08001
errore di sqlserver 17
sorry for my english... ;(
IgnazioC
hi Ignazio,
IgnazioC wrote:
> how to i can connect the access database and the msde server?
> i try whit the "odbc databases" function of access 2000 but not work
> sql state 01000
> errore di sqlserver
> 10060 impossibile effettuare la connessione
> sql state 08001
> errore di sqlserver 17
10060 is a timeout problem. Make sure that you don't have firewall issues
between the client and server. Even personal firewalls on the Server will
block this traffic by default.
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||before it worked,but to the unexpected one it did not work more.
I have changed the name of the pc and all now it works.
why happens this? my name of the pc are "locked" on msde sever? as I
can unlock it?
|||hi Ignazio,
IgnazioC wrote:
> before it worked,but to the unexpected one it did not work more.
> I have changed the name of the pc and all now it works.
> why happens this? my name of the pc are "locked" on msde sever? as I
> can unlock it?
as regard MSDE by it self, there's no problem at all... this was a problem
with SQL Server 7.0/MSDE 1.0 but it is no longer the case...
you can have now problems regarding ie: the registration on Enterprise
Manager, and, re-registering servers on Enterprise Manager UI will solve EM
problems..
or, of course, net problems due to not resolved computer names for firewall
as well...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
sql
Tuesday, March 20, 2012
Access 2003 iif function
Can someone tell me how i can create an iif function to do the following?
I need to look a field that has an amount in it. if it is 90K or less make i
t
1 if its 180 to 90001 make it 2 etc...
So for every 90K in that amount add 1..
I tried to do a VB code to create a ceiling funciton but Access keeps giving
me an error saying that my function is undefinded? Had a friend run my code
and it worked for him. I did update to the most current service pack and it
still errors out...
Can anyone tell me how I can make either the function work or how to do an
iif function to make it work?
Thank you so much for any help you can give me> Can someone tell me how i can create an iif function to do the following?
> I need to look a field that has an amount in it. if it is 90K or less make
> it
> 1 if its 180 to 90001 make it 2 etc...
SELECT val = CASE WHEN column < 90000 THEN 1
WHEN column < 180000 THEN 2
WHEN column < 270000 THEN 3
ELSE 4 END
FROM table
This is covered here (along with some other things that will help an Access
person moving to SQL Server):
http://www.aspfaq.com/2214|||Please repost this to a Access NG.
HTH
Jerry
"magikgb via webservertalk.com" <u15188@.uwe> wrote in message
news:566081edf8196@.uwe...
> Can someone tell me how i can create an iif function to do the following?
> I need to look a field that has an amount in it. if it is 90K or less make
> it
> 1 if its 180 to 90001 make it 2 etc...
> So for every 90K in that amount add 1..
> I tried to do a VB code to create a ceiling funciton but Access keeps
> giving
> me an error saying that my function is undefinded? Had a friend run my
> code
> and it worked for him. I did update to the most current service pack and
> it
> still errors out...
> Can anyone tell me how I can make either the function work or how to do an
> iif function to make it work?
> Thank you so much for any help you can give me|||ummmm... divide by 90k and add 1? Assuming integers, then:
select 89000 / 90000 + 1 -- = 1
select 90001 / 90000 + 1 -- = 2
select 810001 / 90000 + 1 -- = 10
Payson
magikgb via webservertalk.com wrote:
> Can someone tell me how i can create an iif function to do the following?
> I need to look a field that has an amount in it. if it is 90K or less make
it
> 1 if its 180 to 90001 make it 2 etc...
> So for every 90K in that amount add 1..
> I tried to do a VB code to create a ceiling funciton but Access keeps givi
ng
> me an error saying that my function is undefinded? Had a friend run my cod
e
> and it worked for him. I did update to the most current service pack and i
t
> still errors out...
> Can anyone tell me how I can make either the function work or how to do an
> iif function to make it work?
> Thank you so much for any help you can give me|||Thank you Aaron... I am new to VB is this VB code or just straight SQL?
Aaron Bertrand [SQL Server MVP] wrote:
>SELECT val = CASE WHEN column < 90000 THEN 1
> WHEN column < 180000 THEN 2
> WHEN column < 270000 THEN 3
> ELSE 4 END
>FROM table
>This is covered here (along with some other things that will help an Access
>person moving to SQL Server):
>http://www.aspfaq.com/2214|||This is T-SQL. You are posting to a SQL Server newsgroup. If you are
trying to write VB code or Access SQL, please post to a more appropriate
newsgroup. Then people won't waste their time providing you with useless
solutions...
"magikgb via webservertalk.com" <u15188@.uwe> wrote in message
news:5660b960e4dd5@.uwe...
> Thank you Aaron... I am new to VB is this VB code or just straight SQL?
> Aaron Bertrand [SQL Server MVP] wrote:|||Aaron.. Sorry new to this too...
Aaron Bertrand [SQL Server MVP] wrote:
>This is T-SQL. You are posting to a SQL Server newsgroup. If you are
>trying to write VB code or Access SQL, please post to a more appropriate
>newsgroup. Then people won't waste their time providing you with useless
>solutions...
>
>[quoted text clipped - 16 lines]
I need to look a field that has an amount in it. if it is 90K or less make i
t
1 if its 180 to 90001 make it 2 etc...
So for every 90K in that amount add 1..
I tried to do a VB code to create a ceiling funciton but Access keeps giving
me an error saying that my function is undefinded? Had a friend run my code
and it worked for him. I did update to the most current service pack and it
still errors out...
Can anyone tell me how I can make either the function work or how to do an
iif function to make it work?
Thank you so much for any help you can give me> Can someone tell me how i can create an iif function to do the following?
> I need to look a field that has an amount in it. if it is 90K or less make
> it
> 1 if its 180 to 90001 make it 2 etc...
SELECT val = CASE WHEN column < 90000 THEN 1
WHEN column < 180000 THEN 2
WHEN column < 270000 THEN 3
ELSE 4 END
FROM table
This is covered here (along with some other things that will help an Access
person moving to SQL Server):
http://www.aspfaq.com/2214|||Please repost this to a Access NG.
HTH
Jerry
"magikgb via webservertalk.com" <u15188@.uwe> wrote in message
news:566081edf8196@.uwe...
> Can someone tell me how i can create an iif function to do the following?
> I need to look a field that has an amount in it. if it is 90K or less make
> it
> 1 if its 180 to 90001 make it 2 etc...
> So for every 90K in that amount add 1..
> I tried to do a VB code to create a ceiling funciton but Access keeps
> giving
> me an error saying that my function is undefinded? Had a friend run my
> code
> and it worked for him. I did update to the most current service pack and
> it
> still errors out...
> Can anyone tell me how I can make either the function work or how to do an
> iif function to make it work?
> Thank you so much for any help you can give me|||ummmm... divide by 90k and add 1? Assuming integers, then:
select 89000 / 90000 + 1 -- = 1
select 90001 / 90000 + 1 -- = 2
select 810001 / 90000 + 1 -- = 10
Payson
magikgb via webservertalk.com wrote:
> Can someone tell me how i can create an iif function to do the following?
> I need to look a field that has an amount in it. if it is 90K or less make
it
> 1 if its 180 to 90001 make it 2 etc...
> So for every 90K in that amount add 1..
> I tried to do a VB code to create a ceiling funciton but Access keeps givi
ng
> me an error saying that my function is undefinded? Had a friend run my cod
e
> and it worked for him. I did update to the most current service pack and i
t
> still errors out...
> Can anyone tell me how I can make either the function work or how to do an
> iif function to make it work?
> Thank you so much for any help you can give me|||Thank you Aaron... I am new to VB is this VB code or just straight SQL?
Aaron Bertrand [SQL Server MVP] wrote:
>SELECT val = CASE WHEN column < 90000 THEN 1
> WHEN column < 180000 THEN 2
> WHEN column < 270000 THEN 3
> ELSE 4 END
>FROM table
>This is covered here (along with some other things that will help an Access
>person moving to SQL Server):
>http://www.aspfaq.com/2214|||This is T-SQL. You are posting to a SQL Server newsgroup. If you are
trying to write VB code or Access SQL, please post to a more appropriate
newsgroup. Then people won't waste their time providing you with useless
solutions...
"magikgb via webservertalk.com" <u15188@.uwe> wrote in message
news:5660b960e4dd5@.uwe...
> Thank you Aaron... I am new to VB is this VB code or just straight SQL?
> Aaron Bertrand [SQL Server MVP] wrote:|||Aaron.. Sorry new to this too...
Aaron Bertrand [SQL Server MVP] wrote:
>This is T-SQL. You are posting to a SQL Server newsgroup. If you are
>trying to write VB code or Access SQL, please post to a more appropriate
>newsgroup. Then people won't waste their time providing you with useless
>solutions...
>
>[quoted text clipped - 16 lines]
Access 2002...
Is there a function equivelent to Oracle's DECODE in Access 2002?
Thanks!
WarrenDid you try:
IIF(<expression>, <response>, <else response>)
;)|||haha that I did... but it stated the function did not exist??
Thanks!
WarrenDid you try:
IIF(<expression>, <response>, <else response>)
;)|||haha that I did... but it stated the function did not exist??
Thursday, March 8, 2012
ABS function issue
Hi Onibuzz,
One month ago i have posted one topic on
microsoft.public.sqlserver.programming group and u have posted a reply
to it.
Your reply was a great help to me but i am stuck with another small
issue in the same query. I would greatly appreciate if you help me
in finding the solution.
My old query and ur reply is there in this same mail. I want little
modification in the query.
My new query is if the date in the table 'Expense_addl values' is
suppose '2006-05-20' (LockDt)
and i have exchangerate available for dates '2006-05-21' and
'2006-05-19' then i want the exchangerate for the previous date
i.e. '2006-05-19' i.e. if the newer or older gap is same then i want
for the older date.
i am currently using ABS function as suggested by u :-
abs(datediff(dd,ER1.ExchangeRateDate,LockDt)) asc)
but this is giving exchangerate for the new date i.e. '2006-05-21'
Could you plese help me with this ?
Thanks in advance
Regards,
Rajeev Rajput
From: Omnibuzz - view profile
Date: Thurs, Jun 22 2006 4:01 pm
Email: Omnibuzz <Omnib...@.discussions.microsoft.com>
Groups: microsoft.public.sqlserver.programming
Not yet ratedRating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse | Find messages by this author
Hi Rajeev,
I don't know what you do that convert and nullif and all.
But I have substituted a query that will give you the exchange rate.
this update works. But no where clause for your Expense_addl.
I didn't try to figure out from your query :)
UPDATE Expense_Addl
SET HostAmt = ROUND(CONVERT(MONEY,(ISNULL(((CONVERT(FLOAT,
Expense_Addl.HostAmt))
* 100) / NULLIF(ISNULL((select top 1 ExchangeRate from ExchangeRates
ER1
where
ER1.CurrencyCd = Expense_Addl.HostCurrencyCD
order by abs(datediff(dd,ER1.ExchangeRateDate,LockDt))
asc),0),0),0))), 4)
Hope this helps.
--
-Omnibuzz (The SQL GC)
My old query - >
I have two tables Expense_Addl and ExchangeRates
These are the scripts
CREATE TABLE Expense_Addl (
FileId int,
HostAmt money,
HostCurrencyCd char(4),
LockDt smalldatetime
)
INSERT INTO Expense_addl values (1001, 500, 'USD', '2006-06-15')
INSERT INTO Expense_addl values (1001, 500, 'USD', '2006-06-19')
INSERT INTO Expense_addl values (1001, 500, 'USD', '2002-01-01')
In the above table HostCurrencycd can be any currenycode like 'USD',
'INR', YEN'
CREATE TABLE ExchangeRates
(CurrencyCd CHAR(4), ExchangeRate MONEY, ExchangeRateDate
SMALLDATETIME)
INSERT INTO ExchangeRates values ('USD', 0.45, '2006-06-21')
INSERT INTO ExchangeRates values ('USD', 0.40, '2006-06-14')
Now i have to update the HostAmt in Expense_Addl table taking
exchangerate for the hostcurrencycd from table ExchangeRates
for the nearest date like this
For e.g. HOstAmt in first row should take exchangerate as 0.40 because
lockdt 15th june is closet to 14th june
Similarly HOstAmt in second row should take exchangerate as 0.45
because lockdt 19th june is closet to 21st june
Similarly HOstAmt in third row should take exchangerate as 0.40 because
lockdt 01st jan is closet to 14th june
I am trying like below but not getting the correct results.
UPDATE EA
SET HostAmt = ROUND(CONVERT(MONEY,(ISNULL(((CONVERT(FLOAT, EA.HostAmt))
* 100) / NULLIF(ISNULL(ER1.ExchangeRate,0),0),0))), 4)
FROM Expense_Addl EA(NOLOCK)
INNER JOIN ExchangeRates ER1(NOLOCK) ON EA.HostCurrencyCd = ER1.CurrencyCd
INNER JOIN
(SELECT CurrencyCd, MAX(ExchangeRateDate) As ExchangeRateDate
FROM ExchangeRates ER ,Expense_Addl EA(NOLOCK) Where
ER.ExchangeRateDate <= EA.lockdt
GROUP BY ER.CurrencyCd
) T1
ON T1.ExchangeRateDate = ER1.ExchangeRateDate AND T1.CurrencyCd = ER1.CurrencyCdIf you desire previous dates only, then the use of ABS should be removed,
and the possible dates limited to negative values only.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Rajeev" <rajeev.rajput@.gmail.com> wrote in message
news:1155128270.682995.67550@.n13g2000cwa.googlegroups.com...
> Hi Onibuzz,
> One month ago i have posted one topic on
> microsoft.public.sqlserver.programming group and u have posted a reply
> to it.
> Your reply was a great help to me but i am stuck with another small
> issue in the same query. I would greatly appreciate if you help me
> in finding the solution.
> My old query and ur reply is there in this same mail. I want little
> modification in the query.
> My new query is if the date in the table 'Expense_addl values' is
> suppose '2006-05-20' (LockDt)
> and i have exchangerate available for dates '2006-05-21' and
> '2006-05-19' then i want the exchangerate for the previous date
> i.e. '2006-05-19' i.e. if the newer or older gap is same then i want
> for the older date.
> i am currently using ABS function as suggested by u :-
> abs(datediff(dd,ER1.ExchangeRateDate,LockDt)) asc)
> but this is giving exchangerate for the new date i.e. '2006-05-21'
> Could you plese help me with this ?
> Thanks in advance
> Regards,
> Rajeev Rajput
>
> From: Omnibuzz - view profile
> Date: Thurs, Jun 22 2006 4:01 pm
> Email: Omnibuzz <Omnib...@.discussions.microsoft.com>
> Groups: microsoft.public.sqlserver.programming
> Not yet ratedRating:
> show options
> Reply | Reply to Author | Forward | Print | Individual Message | Show
> original | Report Abuse | Find messages by this author
>
> Hi Rajeev,
> I don't know what you do that convert and nullif and all.
> But I have substituted a query that will give you the exchange rate.
> this update works. But no where clause for your Expense_addl.
> I didn't try to figure out from your query :)
> UPDATE Expense_Addl
> SET HostAmt = ROUND(CONVERT(MONEY,(ISNULL(((CONVERT(FLOAT,
> Expense_Addl.HostAmt))
> * 100) / NULLIF(ISNULL((select top 1 ExchangeRate from ExchangeRates
> ER1
> where
> ER1.CurrencyCd = Expense_Addl.HostCurrencyCD
> order by abs(datediff(dd,ER1.ExchangeRateDate,LockDt))
> asc),0),0),0))), 4)
>
> Hope this helps.
> --
> -Omnibuzz (The SQL GC)
>
> My old query - >
> I have two tables Expense_Addl and ExchangeRates
> These are the scripts
>
> CREATE TABLE Expense_Addl (
> FileId int,
> HostAmt money,
> HostCurrencyCd char(4),
> LockDt smalldatetime
> )
>
> INSERT INTO Expense_addl values (1001, 500, 'USD', '2006-06-15')
> INSERT INTO Expense_addl values (1001, 500, 'USD', '2006-06-19')
> INSERT INTO Expense_addl values (1001, 500, 'USD', '2002-01-01')
>
> In the above table HostCurrencycd can be any currenycode like 'USD',
> 'INR', YEN'
>
> CREATE TABLE ExchangeRates
> (CurrencyCd CHAR(4), ExchangeRate MONEY, ExchangeRateDate
> SMALLDATETIME)
>
> INSERT INTO ExchangeRates values ('USD', 0.45, '2006-06-21')
> INSERT INTO ExchangeRates values ('USD', 0.40, '2006-06-14')
>
> Now i have to update the HostAmt in Expense_Addl table taking
> exchangerate for the hostcurrencycd from table ExchangeRates
> for the nearest date like this
>
> For e.g. HOstAmt in first row should take exchangerate as 0.40 because
> lockdt 15th june is closet to 14th june
> Similarly HOstAmt in second row should take exchangerate as 0.45
> because lockdt 19th june is closet to 21st june
> Similarly HOstAmt in third row should take exchangerate as 0.40 because
> lockdt 01st jan is closet to 14th june
>
> I am trying like below but not getting the correct results.
>
> UPDATE EA
> SET HostAmt = ROUND(CONVERT(MONEY,(ISNULL(((CONVERT(FLOAT, EA.HostAmt))
> * 100) / NULLIF(ISNULL(ER1.ExchangeRate,0),0),0))), 4)
> FROM Expense_Addl EA(NOLOCK)
> INNER JOIN ExchangeRates ER1(NOLOCK) ON EA.HostCurrencyCd => ER1.CurrencyCd
> INNER JOIN
> (SELECT CurrencyCd, MAX(ExchangeRateDate) As ExchangeRateDate
> FROM ExchangeRates ER ,Expense_Addl EA(NOLOCK) Where
> ER.ExchangeRateDate <= EA.lockdt
> GROUP BY ER.CurrencyCd
> ) T1
> ON T1.ExchangeRateDate = ER1.ExchangeRateDate AND T1.CurrencyCd => ER1.CurrencyCd
>
One month ago i have posted one topic on
microsoft.public.sqlserver.programming group and u have posted a reply
to it.
Your reply was a great help to me but i am stuck with another small
issue in the same query. I would greatly appreciate if you help me
in finding the solution.
My old query and ur reply is there in this same mail. I want little
modification in the query.
My new query is if the date in the table 'Expense_addl values' is
suppose '2006-05-20' (LockDt)
and i have exchangerate available for dates '2006-05-21' and
'2006-05-19' then i want the exchangerate for the previous date
i.e. '2006-05-19' i.e. if the newer or older gap is same then i want
for the older date.
i am currently using ABS function as suggested by u :-
abs(datediff(dd,ER1.ExchangeRateDate,LockDt)) asc)
but this is giving exchangerate for the new date i.e. '2006-05-21'
Could you plese help me with this ?
Thanks in advance
Regards,
Rajeev Rajput
From: Omnibuzz - view profile
Date: Thurs, Jun 22 2006 4:01 pm
Email: Omnibuzz <Omnib...@.discussions.microsoft.com>
Groups: microsoft.public.sqlserver.programming
Not yet ratedRating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse | Find messages by this author
Hi Rajeev,
I don't know what you do that convert and nullif and all.
But I have substituted a query that will give you the exchange rate.
this update works. But no where clause for your Expense_addl.
I didn't try to figure out from your query :)
UPDATE Expense_Addl
SET HostAmt = ROUND(CONVERT(MONEY,(ISNULL(((CONVERT(FLOAT,
Expense_Addl.HostAmt))
* 100) / NULLIF(ISNULL((select top 1 ExchangeRate from ExchangeRates
ER1
where
ER1.CurrencyCd = Expense_Addl.HostCurrencyCD
order by abs(datediff(dd,ER1.ExchangeRateDate,LockDt))
asc),0),0),0))), 4)
Hope this helps.
--
-Omnibuzz (The SQL GC)
My old query - >
I have two tables Expense_Addl and ExchangeRates
These are the scripts
CREATE TABLE Expense_Addl (
FileId int,
HostAmt money,
HostCurrencyCd char(4),
LockDt smalldatetime
)
INSERT INTO Expense_addl values (1001, 500, 'USD', '2006-06-15')
INSERT INTO Expense_addl values (1001, 500, 'USD', '2006-06-19')
INSERT INTO Expense_addl values (1001, 500, 'USD', '2002-01-01')
In the above table HostCurrencycd can be any currenycode like 'USD',
'INR', YEN'
CREATE TABLE ExchangeRates
(CurrencyCd CHAR(4), ExchangeRate MONEY, ExchangeRateDate
SMALLDATETIME)
INSERT INTO ExchangeRates values ('USD', 0.45, '2006-06-21')
INSERT INTO ExchangeRates values ('USD', 0.40, '2006-06-14')
Now i have to update the HostAmt in Expense_Addl table taking
exchangerate for the hostcurrencycd from table ExchangeRates
for the nearest date like this
For e.g. HOstAmt in first row should take exchangerate as 0.40 because
lockdt 15th june is closet to 14th june
Similarly HOstAmt in second row should take exchangerate as 0.45
because lockdt 19th june is closet to 21st june
Similarly HOstAmt in third row should take exchangerate as 0.40 because
lockdt 01st jan is closet to 14th june
I am trying like below but not getting the correct results.
UPDATE EA
SET HostAmt = ROUND(CONVERT(MONEY,(ISNULL(((CONVERT(FLOAT, EA.HostAmt))
* 100) / NULLIF(ISNULL(ER1.ExchangeRate,0),0),0))), 4)
FROM Expense_Addl EA(NOLOCK)
INNER JOIN ExchangeRates ER1(NOLOCK) ON EA.HostCurrencyCd = ER1.CurrencyCd
INNER JOIN
(SELECT CurrencyCd, MAX(ExchangeRateDate) As ExchangeRateDate
FROM ExchangeRates ER ,Expense_Addl EA(NOLOCK) Where
ER.ExchangeRateDate <= EA.lockdt
GROUP BY ER.CurrencyCd
) T1
ON T1.ExchangeRateDate = ER1.ExchangeRateDate AND T1.CurrencyCd = ER1.CurrencyCdIf you desire previous dates only, then the use of ABS should be removed,
and the possible dates limited to negative values only.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Rajeev" <rajeev.rajput@.gmail.com> wrote in message
news:1155128270.682995.67550@.n13g2000cwa.googlegroups.com...
> Hi Onibuzz,
> One month ago i have posted one topic on
> microsoft.public.sqlserver.programming group and u have posted a reply
> to it.
> Your reply was a great help to me but i am stuck with another small
> issue in the same query. I would greatly appreciate if you help me
> in finding the solution.
> My old query and ur reply is there in this same mail. I want little
> modification in the query.
> My new query is if the date in the table 'Expense_addl values' is
> suppose '2006-05-20' (LockDt)
> and i have exchangerate available for dates '2006-05-21' and
> '2006-05-19' then i want the exchangerate for the previous date
> i.e. '2006-05-19' i.e. if the newer or older gap is same then i want
> for the older date.
> i am currently using ABS function as suggested by u :-
> abs(datediff(dd,ER1.ExchangeRateDate,LockDt)) asc)
> but this is giving exchangerate for the new date i.e. '2006-05-21'
> Could you plese help me with this ?
> Thanks in advance
> Regards,
> Rajeev Rajput
>
> From: Omnibuzz - view profile
> Date: Thurs, Jun 22 2006 4:01 pm
> Email: Omnibuzz <Omnib...@.discussions.microsoft.com>
> Groups: microsoft.public.sqlserver.programming
> Not yet ratedRating:
> show options
> Reply | Reply to Author | Forward | Print | Individual Message | Show
> original | Report Abuse | Find messages by this author
>
> Hi Rajeev,
> I don't know what you do that convert and nullif and all.
> But I have substituted a query that will give you the exchange rate.
> this update works. But no where clause for your Expense_addl.
> I didn't try to figure out from your query :)
> UPDATE Expense_Addl
> SET HostAmt = ROUND(CONVERT(MONEY,(ISNULL(((CONVERT(FLOAT,
> Expense_Addl.HostAmt))
> * 100) / NULLIF(ISNULL((select top 1 ExchangeRate from ExchangeRates
> ER1
> where
> ER1.CurrencyCd = Expense_Addl.HostCurrencyCD
> order by abs(datediff(dd,ER1.ExchangeRateDate,LockDt))
> asc),0),0),0))), 4)
>
> Hope this helps.
> --
> -Omnibuzz (The SQL GC)
>
> My old query - >
> I have two tables Expense_Addl and ExchangeRates
> These are the scripts
>
> CREATE TABLE Expense_Addl (
> FileId int,
> HostAmt money,
> HostCurrencyCd char(4),
> LockDt smalldatetime
> )
>
> INSERT INTO Expense_addl values (1001, 500, 'USD', '2006-06-15')
> INSERT INTO Expense_addl values (1001, 500, 'USD', '2006-06-19')
> INSERT INTO Expense_addl values (1001, 500, 'USD', '2002-01-01')
>
> In the above table HostCurrencycd can be any currenycode like 'USD',
> 'INR', YEN'
>
> CREATE TABLE ExchangeRates
> (CurrencyCd CHAR(4), ExchangeRate MONEY, ExchangeRateDate
> SMALLDATETIME)
>
> INSERT INTO ExchangeRates values ('USD', 0.45, '2006-06-21')
> INSERT INTO ExchangeRates values ('USD', 0.40, '2006-06-14')
>
> Now i have to update the HostAmt in Expense_Addl table taking
> exchangerate for the hostcurrencycd from table ExchangeRates
> for the nearest date like this
>
> For e.g. HOstAmt in first row should take exchangerate as 0.40 because
> lockdt 15th june is closet to 14th june
> Similarly HOstAmt in second row should take exchangerate as 0.45
> because lockdt 19th june is closet to 21st june
> Similarly HOstAmt in third row should take exchangerate as 0.40 because
> lockdt 01st jan is closet to 14th june
>
> I am trying like below but not getting the correct results.
>
> UPDATE EA
> SET HostAmt = ROUND(CONVERT(MONEY,(ISNULL(((CONVERT(FLOAT, EA.HostAmt))
> * 100) / NULLIF(ISNULL(ER1.ExchangeRate,0),0),0))), 4)
> FROM Expense_Addl EA(NOLOCK)
> INNER JOIN ExchangeRates ER1(NOLOCK) ON EA.HostCurrencyCd => ER1.CurrencyCd
> INNER JOIN
> (SELECT CurrencyCd, MAX(ExchangeRateDate) As ExchangeRateDate
> FROM ExchangeRates ER ,Expense_Addl EA(NOLOCK) Where
> ER.ExchangeRateDate <= EA.lockdt
> GROUP BY ER.CurrencyCd
> ) T1
> ON T1.ExchangeRateDate = ER1.ExchangeRateDate AND T1.CurrencyCd => ER1.CurrencyCd
>
ABS function issue
Hi Onibuzz,
One month ago i have posted one topic on
microsoft.public.sqlserver.programming group and u have posted a reply
to it.
Your reply was a great help to me but i am stuck with another small
issue in the same query. I would greatly appreciate if you help me
in finding the solution.
My old query and ur reply is there in this same mail. I want little
modification in the query.
My new query is if the date in the table 'Expense_addl values' is
suppose '2006-05-20' (LockDt)
and i have exchangerate available for dates '2006-05-21' and
'2006-05-19' then i want the exchangerate for the previous date
i.e. '2006-05-19' i.e. if the newer or older gap is same then i want
for the older date.
i am currently using ABS function as suggested by u :-
abs(datediff(dd,ER1.ExchangeRateDate,LockDt)) asc)
but this is giving exchangerate for the new date i.e. '2006-05-21'
Could you plese help me with this ?
Thanks in advance
Regards,
Rajeev Rajput
From: Omnibuzz - view profile
Date: Thurs, Jun 22 2006 4:01 pm
Email: Omnibuzz <Omnib...@.discussions.microsoft.com>
Groups: microsoft.public.sqlserver.programming
Not yet ratedRating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse | Find messages by this author
Hi Rajeev,
I don't know what you do that convert and nullif and all.
But I have substituted a query that will give you the exchange rate.
this update works. But no where clause for your Expense_addl.
I didn't try to figure out from your query
UPDATE Expense_Addl
SET HostAmt = ROUND(CONVERT(MONEY,(ISNULL(((CONVERT(FL
OAT,
Expense_Addl.HostAmt))
* 100) / NULLIF(ISNULL((select top 1 ExchangeRate from ExchangeRates
ER1
where
ER1.CurrencyCd = Expense_Addl.HostCurrencyCD
order by abs(datediff(dd,ER1.ExchangeRateDate,LockDt))
asc),0),0),0))), 4)
Hope this helps.
--
-Omnibuzz (The SQL GC)
My old query - >
I have two tables Expense_Addl and ExchangeRates
These are the scripts
CREATE TABLE Expense_Addl (
FileId int,
HostAmt money,
HostCurrencyCd char(4),
LockDt smalldatetime
)
INSERT INTO Expense_addl values (1001, 500, 'USD', '2006-06-15')
INSERT INTO Expense_addl values (1001, 500, 'USD', '2006-06-19')
INSERT INTO Expense_addl values (1001, 500, 'USD', '2002-01-01')
In the above table HostCurrencycd can be any currenycode like 'USD',
'INR', YEN'
CREATE TABLE ExchangeRates
(CurrencyCd CHAR(4), ExchangeRate MONEY, ExchangeRateDate
SMALLDATETIME)
INSERT INTO ExchangeRates values ('USD', 0.45, '2006-06-21')
INSERT INTO ExchangeRates values ('USD', 0.40, '2006-06-14')
Now i have to update the HostAmt in Expense_Addl table taking
exchangerate for the hostcurrencycd from table ExchangeRates
for the nearest date like this
For e.g. HOstAmt in first row should take exchangerate as 0.40 because
lockdt 15th june is closet to 14th june
Similarly HOstAmt in second row should take exchangerate as 0.45
because lockdt 19th june is closet to 21st june
Similarly HOstAmt in third row should take exchangerate as 0.40 because
lockdt 01st jan is closet to 14th june
I am trying like below but not getting the correct results.
UPDATE EA
SET HostAmt = ROUND(CONVERT(MONEY,(ISNULL(((CONVERT(FL
OAT, EA.HostAmt))
* 100) / NULLIF(ISNULL(ER1.ExchangeRate,0),0),0))), 4)
FROM Expense_Addl EA(NOLOCK)
INNER JOIN ExchangeRates ER1(NOLOCK) ON EA.HostCurrencyCd =
ER1.CurrencyCd
INNER JOIN
(SELECT CurrencyCd, MAX(ExchangeRateDate) As ExchangeRateDate
FROM ExchangeRates ER ,Expense_Addl EA(NOLOCK) Where
ER.ExchangeRateDate <= EA.lockdt
GROUP BY ER.CurrencyCd
) T1
ON T1.ExchangeRateDate = ER1.ExchangeRateDate AND T1.CurrencyCd =
ER1.CurrencyCdIf you desire previous dates only, then the use of ABS should be removed,
and the possible dates limited to negative values only.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Rajeev" <rajeev.rajput@.gmail.com> wrote in message
news:1155128270.682995.67550@.n13g2000cwa.googlegroups.com...
> Hi Onibuzz,
> One month ago i have posted one topic on
> microsoft.public.sqlserver.programming group and u have posted a reply
> to it.
> Your reply was a great help to me but i am stuck with another small
> issue in the same query. I would greatly appreciate if you help me
> in finding the solution.
> My old query and ur reply is there in this same mail. I want little
> modification in the query.
> My new query is if the date in the table 'Expense_addl values' is
> suppose '2006-05-20' (LockDt)
> and i have exchangerate available for dates '2006-05-21' and
> '2006-05-19' then i want the exchangerate for the previous date
> i.e. '2006-05-19' i.e. if the newer or older gap is same then i want
> for the older date.
> i am currently using ABS function as suggested by u :-
> abs(datediff(dd,ER1.ExchangeRateDate,LockDt)) asc)
> but this is giving exchangerate for the new date i.e. '2006-05-21'
> Could you plese help me with this ?
> Thanks in advance
> Regards,
> Rajeev Rajput
>
> From: Omnibuzz - view profile
> Date: Thurs, Jun 22 2006 4:01 pm
> Email: Omnibuzz <Omnib...@.discussions.microsoft.com>
> Groups: microsoft.public.sqlserver.programming
> Not yet ratedRating:
> show options
> Reply | Reply to Author | Forward | Print | Individual Message | Show
> original | Report Abuse | Find messages by this author
>
> Hi Rajeev,
> I don't know what you do that convert and nullif and all.
> But I have substituted a query that will give you the exchange rate.
> this update works. But no where clause for your Expense_addl.
> I didn't try to figure out from your query
> UPDATE Expense_Addl
> SET HostAmt = ROUND(CONVERT(MONEY,(ISNULL(((CONVERT(FL
OAT,
> Expense_Addl.HostAmt))
> * 100) / NULLIF(ISNULL((select top 1 ExchangeRate from ExchangeRates
> ER1
> where
> ER1.CurrencyCd = Expense_Addl.HostCurrencyCD
> order by abs(datediff(dd,ER1.ExchangeRateDate,LockDt))
> asc),0),0),0))), 4)
>
> Hope this helps.
> --
> -Omnibuzz (The SQL GC)
>
> My old query - >
> I have two tables Expense_Addl and ExchangeRates
> These are the scripts
>
> CREATE TABLE Expense_Addl (
> FileId int,
> HostAmt money,
> HostCurrencyCd char(4),
> LockDt smalldatetime
> )
>
> INSERT INTO Expense_addl values (1001, 500, 'USD', '2006-06-15')
> INSERT INTO Expense_addl values (1001, 500, 'USD', '2006-06-19')
> INSERT INTO Expense_addl values (1001, 500, 'USD', '2002-01-01')
>
> In the above table HostCurrencycd can be any currenycode like 'USD',
> 'INR', YEN'
>
> CREATE TABLE ExchangeRates
> (CurrencyCd CHAR(4), ExchangeRate MONEY, ExchangeRateDate
> SMALLDATETIME)
>
> INSERT INTO ExchangeRates values ('USD', 0.45, '2006-06-21')
> INSERT INTO ExchangeRates values ('USD', 0.40, '2006-06-14')
>
> Now i have to update the HostAmt in Expense_Addl table taking
> exchangerate for the hostcurrencycd from table ExchangeRates
> for the nearest date like this
>
> For e.g. HOstAmt in first row should take exchangerate as 0.40 because
> lockdt 15th june is closet to 14th june
> Similarly HOstAmt in second row should take exchangerate as 0.45
> because lockdt 19th june is closet to 21st june
> Similarly HOstAmt in third row should take exchangerate as 0.40 because
> lockdt 01st jan is closet to 14th june
>
> I am trying like below but not getting the correct results.
>
> UPDATE EA
> SET HostAmt = ROUND(CONVERT(MONEY,(ISNULL(((CONVERT(FL
OAT, EA.HostAmt))
> * 100) / NULLIF(ISNULL(ER1.ExchangeRate,0),0),0))), 4)
> FROM Expense_Addl EA(NOLOCK)
> INNER JOIN ExchangeRates ER1(NOLOCK) ON EA.HostCurrencyCd =
> ER1.CurrencyCd
> INNER JOIN
> (SELECT CurrencyCd, MAX(ExchangeRateDate) As ExchangeRateDate
> FROM ExchangeRates ER ,Expense_Addl EA(NOLOCK) Where
> ER.ExchangeRateDate <= EA.lockdt
> GROUP BY ER.CurrencyCd
> ) T1
> ON T1.ExchangeRateDate = ER1.ExchangeRateDate AND T1.CurrencyCd =
> ER1.CurrencyCd
>
One month ago i have posted one topic on
microsoft.public.sqlserver.programming group and u have posted a reply
to it.
Your reply was a great help to me but i am stuck with another small
issue in the same query. I would greatly appreciate if you help me
in finding the solution.
My old query and ur reply is there in this same mail. I want little
modification in the query.
My new query is if the date in the table 'Expense_addl values' is
suppose '2006-05-20' (LockDt)
and i have exchangerate available for dates '2006-05-21' and
'2006-05-19' then i want the exchangerate for the previous date
i.e. '2006-05-19' i.e. if the newer or older gap is same then i want
for the older date.
i am currently using ABS function as suggested by u :-
abs(datediff(dd,ER1.ExchangeRateDate,LockDt)) asc)
but this is giving exchangerate for the new date i.e. '2006-05-21'
Could you plese help me with this ?
Thanks in advance
Regards,
Rajeev Rajput
From: Omnibuzz - view profile
Date: Thurs, Jun 22 2006 4:01 pm
Email: Omnibuzz <Omnib...@.discussions.microsoft.com>
Groups: microsoft.public.sqlserver.programming
Not yet ratedRating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse | Find messages by this author
Hi Rajeev,
I don't know what you do that convert and nullif and all.
But I have substituted a query that will give you the exchange rate.
this update works. But no where clause for your Expense_addl.
I didn't try to figure out from your query
UPDATE Expense_Addl
SET HostAmt = ROUND(CONVERT(MONEY,(ISNULL(((CONVERT(FL
OAT,
Expense_Addl.HostAmt))
* 100) / NULLIF(ISNULL((select top 1 ExchangeRate from ExchangeRates
ER1
where
ER1.CurrencyCd = Expense_Addl.HostCurrencyCD
order by abs(datediff(dd,ER1.ExchangeRateDate,LockDt))
asc),0),0),0))), 4)
Hope this helps.
--
-Omnibuzz (The SQL GC)
My old query - >
I have two tables Expense_Addl and ExchangeRates
These are the scripts
CREATE TABLE Expense_Addl (
FileId int,
HostAmt money,
HostCurrencyCd char(4),
LockDt smalldatetime
)
INSERT INTO Expense_addl values (1001, 500, 'USD', '2006-06-15')
INSERT INTO Expense_addl values (1001, 500, 'USD', '2006-06-19')
INSERT INTO Expense_addl values (1001, 500, 'USD', '2002-01-01')
In the above table HostCurrencycd can be any currenycode like 'USD',
'INR', YEN'
CREATE TABLE ExchangeRates
(CurrencyCd CHAR(4), ExchangeRate MONEY, ExchangeRateDate
SMALLDATETIME)
INSERT INTO ExchangeRates values ('USD', 0.45, '2006-06-21')
INSERT INTO ExchangeRates values ('USD', 0.40, '2006-06-14')
Now i have to update the HostAmt in Expense_Addl table taking
exchangerate for the hostcurrencycd from table ExchangeRates
for the nearest date like this
For e.g. HOstAmt in first row should take exchangerate as 0.40 because
lockdt 15th june is closet to 14th june
Similarly HOstAmt in second row should take exchangerate as 0.45
because lockdt 19th june is closet to 21st june
Similarly HOstAmt in third row should take exchangerate as 0.40 because
lockdt 01st jan is closet to 14th june
I am trying like below but not getting the correct results.
UPDATE EA
SET HostAmt = ROUND(CONVERT(MONEY,(ISNULL(((CONVERT(FL
OAT, EA.HostAmt))
* 100) / NULLIF(ISNULL(ER1.ExchangeRate,0),0),0))), 4)
FROM Expense_Addl EA(NOLOCK)
INNER JOIN ExchangeRates ER1(NOLOCK) ON EA.HostCurrencyCd =
ER1.CurrencyCd
INNER JOIN
(SELECT CurrencyCd, MAX(ExchangeRateDate) As ExchangeRateDate
FROM ExchangeRates ER ,Expense_Addl EA(NOLOCK) Where
ER.ExchangeRateDate <= EA.lockdt
GROUP BY ER.CurrencyCd
) T1
ON T1.ExchangeRateDate = ER1.ExchangeRateDate AND T1.CurrencyCd =
ER1.CurrencyCdIf you desire previous dates only, then the use of ABS should be removed,
and the possible dates limited to negative values only.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Rajeev" <rajeev.rajput@.gmail.com> wrote in message
news:1155128270.682995.67550@.n13g2000cwa.googlegroups.com...
> Hi Onibuzz,
> One month ago i have posted one topic on
> microsoft.public.sqlserver.programming group and u have posted a reply
> to it.
> Your reply was a great help to me but i am stuck with another small
> issue in the same query. I would greatly appreciate if you help me
> in finding the solution.
> My old query and ur reply is there in this same mail. I want little
> modification in the query.
> My new query is if the date in the table 'Expense_addl values' is
> suppose '2006-05-20' (LockDt)
> and i have exchangerate available for dates '2006-05-21' and
> '2006-05-19' then i want the exchangerate for the previous date
> i.e. '2006-05-19' i.e. if the newer or older gap is same then i want
> for the older date.
> i am currently using ABS function as suggested by u :-
> abs(datediff(dd,ER1.ExchangeRateDate,LockDt)) asc)
> but this is giving exchangerate for the new date i.e. '2006-05-21'
> Could you plese help me with this ?
> Thanks in advance
> Regards,
> Rajeev Rajput
>
> From: Omnibuzz - view profile
> Date: Thurs, Jun 22 2006 4:01 pm
> Email: Omnibuzz <Omnib...@.discussions.microsoft.com>
> Groups: microsoft.public.sqlserver.programming
> Not yet ratedRating:
> show options
> Reply | Reply to Author | Forward | Print | Individual Message | Show
> original | Report Abuse | Find messages by this author
>
> Hi Rajeev,
> I don't know what you do that convert and nullif and all.
> But I have substituted a query that will give you the exchange rate.
> this update works. But no where clause for your Expense_addl.
> I didn't try to figure out from your query
> UPDATE Expense_Addl
> SET HostAmt = ROUND(CONVERT(MONEY,(ISNULL(((CONVERT(FL
OAT,
> Expense_Addl.HostAmt))
> * 100) / NULLIF(ISNULL((select top 1 ExchangeRate from ExchangeRates
> ER1
> where
> ER1.CurrencyCd = Expense_Addl.HostCurrencyCD
> order by abs(datediff(dd,ER1.ExchangeRateDate,LockDt))
> asc),0),0),0))), 4)
>
> Hope this helps.
> --
> -Omnibuzz (The SQL GC)
>
> My old query - >
> I have two tables Expense_Addl and ExchangeRates
> These are the scripts
>
> CREATE TABLE Expense_Addl (
> FileId int,
> HostAmt money,
> HostCurrencyCd char(4),
> LockDt smalldatetime
> )
>
> INSERT INTO Expense_addl values (1001, 500, 'USD', '2006-06-15')
> INSERT INTO Expense_addl values (1001, 500, 'USD', '2006-06-19')
> INSERT INTO Expense_addl values (1001, 500, 'USD', '2002-01-01')
>
> In the above table HostCurrencycd can be any currenycode like 'USD',
> 'INR', YEN'
>
> CREATE TABLE ExchangeRates
> (CurrencyCd CHAR(4), ExchangeRate MONEY, ExchangeRateDate
> SMALLDATETIME)
>
> INSERT INTO ExchangeRates values ('USD', 0.45, '2006-06-21')
> INSERT INTO ExchangeRates values ('USD', 0.40, '2006-06-14')
>
> Now i have to update the HostAmt in Expense_Addl table taking
> exchangerate for the hostcurrencycd from table ExchangeRates
> for the nearest date like this
>
> For e.g. HOstAmt in first row should take exchangerate as 0.40 because
> lockdt 15th june is closet to 14th june
> Similarly HOstAmt in second row should take exchangerate as 0.45
> because lockdt 19th june is closet to 21st june
> Similarly HOstAmt in third row should take exchangerate as 0.40 because
> lockdt 01st jan is closet to 14th june
>
> I am trying like below but not getting the correct results.
>
> UPDATE EA
> SET HostAmt = ROUND(CONVERT(MONEY,(ISNULL(((CONVERT(FL
OAT, EA.HostAmt))
> * 100) / NULLIF(ISNULL(ER1.ExchangeRate,0),0),0))), 4)
> FROM Expense_Addl EA(NOLOCK)
> INNER JOIN ExchangeRates ER1(NOLOCK) ON EA.HostCurrencyCd =
> ER1.CurrencyCd
> INNER JOIN
> (SELECT CurrencyCd, MAX(ExchangeRateDate) As ExchangeRateDate
> FROM ExchangeRates ER ,Expense_Addl EA(NOLOCK) Where
> ER.ExchangeRateDate <= EA.lockdt
> GROUP BY ER.CurrencyCd
> ) T1
> ON T1.ExchangeRateDate = ER1.ExchangeRateDate AND T1.CurrencyCd =
> ER1.CurrencyCd
>
about XML View Function.
I Know that we can show the view that it is made by Singleton XML Schema.
But I want to show a view what it is made by NO Singleton XML Schema.
Please let me know that if you have any knowledges.
Thanks for you.
just try it like this
Content of XML-Field:
<MyParameters>
<Parameter>
<ParaNr></ParaNr>
<ParaText></ParaText>
</Parameter>
<Parameter>
<ParaNr></ParaNr>
<ParaText></ParaText>
</Parameter>
<Parameter>
<ParaNr></ParaNr>
<ParaText></ParaText>
</Parameter>
</MyParameters>
Statement for the Query (My_ID is just another nonXML column of the table):
SELECT MY_ID
ref.value('./ParaNr', 'nvarchar(2)') AS [PARA_NR],
ref.value('./ParaText', 'nvarchar(200)') AS [PARA_TEXT]
FROM MY_SCEMA.MY_TABLECROSS apply MY_XMLCOLUMN.nodes('/MyParameters/Parameter') AS T (ref)
I Hope this helps
best regards
Raimund
about XML View Function.
I Know that we can show the view that it is made by Singleton XML Schema.
But I want to show a view what it is made by NO Singleton XML Schema.
Please let me know that if you have any knowledges.
Thanks for you.
just try it like this
Content of XML-Field:
<MyParameters>
<Parameter>
<ParaNr></ParaNr>
<ParaText></ParaText>
</Parameter>
<Parameter>
<ParaNr></ParaNr>
<ParaText></ParaText>
</Parameter>
<Parameter>
<ParaNr></ParaNr>
<ParaText></ParaText>
</Parameter>
</MyParameters>
Statement for the Query (My_ID is just another nonXML column of the table):
SELECT MY_ID
ref.value('./ParaNr', 'nvarchar(2)') AS [PARA_NR],
ref.value('./ParaText', 'nvarchar(200)') AS [PARA_TEXT]
FROM MY_SCEMA.MY_TABLECROSS apply MY_XMLCOLUMN.nodes('/MyParameters/Parameter') AS T (ref)
I Hope this helps
best regards
Raimund
Saturday, February 25, 2012
About sys.fn_indexinfo in SQL2005 MOC 2733A
Hi guys,
i reading the MOC 2733A for SQL2005 and found the "sys.fn_indexinfo"
function in a module 5 but when i run a sys.fn_indexinfo how indicated a
error showed
Seems that not exist ... it's possible ? It was replaced ?
If so how i can view a index defrag status ?
Thanks in advance.You might have an version of the course that was based on a beta of the product. My guess is that
the function you want is sys.dm_db_index_physical_stats.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<io.com@.newsgroup.nospam> wrote in message news:O6mSA$DzGHA.3908@.TK2MSFTNGP05.phx.gbl...
> Hi guys,
> i reading the MOC 2733A for SQL2005 and found the "sys.fn_indexinfo" function in a module 5 but
> when i run a sys.fn_indexinfo how indicated a error showed
> Seems that not exist ... it's possible ? It was replaced ?
> If so how i can view a index defrag status ?
> Thanks in advance.
>|||Yes it's right.
Found!
bye.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uGPTSCEzGHA.4972@.TK2MSFTNGP03.phx.gbl...
> You might have an version of the course that was based on a beta of the
> product. My guess is that the function you want is
> sys.dm_db_index_physical_stats.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <io.com@.newsgroup.nospam> wrote in message
> news:O6mSA$DzGHA.3908@.TK2MSFTNGP05.phx.gbl...
>> Hi guys,
>> i reading the MOC 2733A for SQL2005 and found the "sys.fn_indexinfo"
>> function in a module 5 but when i run a sys.fn_indexinfo how indicated a
>> error showed
>> Seems that not exist ... it's possible ? It was replaced ?
>> If so how i can view a index defrag status ?
>> Thanks in advance.
>>
>|||Hi IO,
So far as I know, number of information in MOC 2733 is changed since it is
based on SQL 2005 beta. If you have any other questions, feel free to post
in our newsgroup. We are glad to provide assistance.
Have a good day!
Best regards,
Vincent Xu
Microsoft Online Partner Support
======================================================Get Secure! - www.microsoft.com/security
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties,and confers no rights.
======================================================>>From: <io.com@.newsgroup.nospam>
>>References: <O6mSA$DzGHA.3908@.TK2MSFTNGP05.phx.gbl>
<uGPTSCEzGHA.4972@.TK2MSFTNGP03.phx.gbl>
>>Subject: Re: About sys.fn_indexinfo in SQL2005 MOC 2733A
>>Date: Wed, 30 Aug 2006 16:50:21 +0200
>>Lines: 36
>>X-Priority: 3
>>X-MSMail-Priority: Normal
>>X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
>>X-RFC2646: Format=Flowed; Response
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962
>>Message-ID: <#09RgOEzGHA.1536@.TK2MSFTNGP02.phx.gbl>
>>Newsgroups: microsoft.public.sqlserver.server
>>NNTP-Posting-Host: 62.241.4.149
>>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP02.phx.gbl
>>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:443733
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>Yes it's right.
>>Found!
>>bye.
>>"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
>>message news:uGPTSCEzGHA.4972@.TK2MSFTNGP03.phx.gbl...
>> You might have an version of the course that was based on a beta of the
>> product. My guess is that the function you want is
>> sys.dm_db_index_physical_stats.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> <io.com@.newsgroup.nospam> wrote in message
>> news:O6mSA$DzGHA.3908@.TK2MSFTNGP05.phx.gbl...
>> Hi guys,
>> i reading the MOC 2733A for SQL2005 and found the "sys.fn_indexinfo"
>> function in a module 5 but when i run a sys.fn_indexinfo how indicated
a
>> error showed
>> Seems that not exist ... it's possible ? It was replaced ?
>> If so how i can view a index defrag status ?
>> Thanks in advance.
>>
>>
>>
i reading the MOC 2733A for SQL2005 and found the "sys.fn_indexinfo"
function in a module 5 but when i run a sys.fn_indexinfo how indicated a
error showed
Seems that not exist ... it's possible ? It was replaced ?
If so how i can view a index defrag status ?
Thanks in advance.You might have an version of the course that was based on a beta of the product. My guess is that
the function you want is sys.dm_db_index_physical_stats.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<io.com@.newsgroup.nospam> wrote in message news:O6mSA$DzGHA.3908@.TK2MSFTNGP05.phx.gbl...
> Hi guys,
> i reading the MOC 2733A for SQL2005 and found the "sys.fn_indexinfo" function in a module 5 but
> when i run a sys.fn_indexinfo how indicated a error showed
> Seems that not exist ... it's possible ? It was replaced ?
> If so how i can view a index defrag status ?
> Thanks in advance.
>|||Yes it's right.
Found!
bye.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uGPTSCEzGHA.4972@.TK2MSFTNGP03.phx.gbl...
> You might have an version of the course that was based on a beta of the
> product. My guess is that the function you want is
> sys.dm_db_index_physical_stats.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <io.com@.newsgroup.nospam> wrote in message
> news:O6mSA$DzGHA.3908@.TK2MSFTNGP05.phx.gbl...
>> Hi guys,
>> i reading the MOC 2733A for SQL2005 and found the "sys.fn_indexinfo"
>> function in a module 5 but when i run a sys.fn_indexinfo how indicated a
>> error showed
>> Seems that not exist ... it's possible ? It was replaced ?
>> If so how i can view a index defrag status ?
>> Thanks in advance.
>>
>|||Hi IO,
So far as I know, number of information in MOC 2733 is changed since it is
based on SQL 2005 beta. If you have any other questions, feel free to post
in our newsgroup. We are glad to provide assistance.
Have a good day!
Best regards,
Vincent Xu
Microsoft Online Partner Support
======================================================Get Secure! - www.microsoft.com/security
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties,and confers no rights.
======================================================>>From: <io.com@.newsgroup.nospam>
>>References: <O6mSA$DzGHA.3908@.TK2MSFTNGP05.phx.gbl>
<uGPTSCEzGHA.4972@.TK2MSFTNGP03.phx.gbl>
>>Subject: Re: About sys.fn_indexinfo in SQL2005 MOC 2733A
>>Date: Wed, 30 Aug 2006 16:50:21 +0200
>>Lines: 36
>>X-Priority: 3
>>X-MSMail-Priority: Normal
>>X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
>>X-RFC2646: Format=Flowed; Response
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962
>>Message-ID: <#09RgOEzGHA.1536@.TK2MSFTNGP02.phx.gbl>
>>Newsgroups: microsoft.public.sqlserver.server
>>NNTP-Posting-Host: 62.241.4.149
>>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP02.phx.gbl
>>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:443733
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>Yes it's right.
>>Found!
>>bye.
>>"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
>>message news:uGPTSCEzGHA.4972@.TK2MSFTNGP03.phx.gbl...
>> You might have an version of the course that was based on a beta of the
>> product. My guess is that the function you want is
>> sys.dm_db_index_physical_stats.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> <io.com@.newsgroup.nospam> wrote in message
>> news:O6mSA$DzGHA.3908@.TK2MSFTNGP05.phx.gbl...
>> Hi guys,
>> i reading the MOC 2733A for SQL2005 and found the "sys.fn_indexinfo"
>> function in a module 5 but when i run a sys.fn_indexinfo how indicated
a
>> error showed
>> Seems that not exist ... it's possible ? It was replaced ?
>> If so how i can view a index defrag status ?
>> Thanks in advance.
>>
>>
>>
About sys.fn_indexinfo in SQL2005 MOC 2733A
Hi guys,
i reading the MOC 2733A for SQL2005 and found the "sys.fn_indexinfo"
function in a module 5 but when i run a sys.fn_indexinfo how indicated a
error showed
Seems that not exist ... it's possible ? It was replaced ?
If so how i can view a index defrag status ?
Thanks in advance.You might have an version of the course that was based on a beta of the prod
uct. My guess is that
the function you want is sys.dm_db_index_physical_stats.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<io.com@.newsgroup.nospam> wrote in message news:O6mSA$DzGHA.3908@.TK2MSFTNGP05.phx.gbl...[vbc
ol=seagreen]
> Hi guys,
> i reading the MOC 2733A for SQL2005 and found the "sys.fn_indexinfo" funct
ion in a module 5 but
> when i run a sys.fn_indexinfo how indicated a error showed
> Seems that not exist ... it's possible ? It was replaced ?
> If so how i can view a index defrag status ?
> Thanks in advance.
>[/vbcol]|||Yes it's right.
Found!
bye.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uGPTSCEzGHA.4972@.TK2MSFTNGP03.phx.gbl...
> You might have an version of the course that was based on a beta of the
> product. My guess is that the function you want is
> sys.dm_db_index_physical_stats.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <io.com@.newsgroup.nospam> wrote in message
> news:O6mSA$DzGHA.3908@.TK2MSFTNGP05.phx.gbl...
>|||Hi IO,
So far as I know, number of information in MOC 2733 is changed since it is
based on SQL 2005 beta. If you have any other questions, feel free to post
in our newsgroup. We are glad to provide assistance.
Have a good day!
Best regards,
Vincent Xu
Microsoft Online Partner Support
========================================
==============
Get Secure! - www.microsoft.com/security
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties,and confers no rights.
========================================
==============
--[vbcol=seagreen]
<uGPTSCEzGHA.4972@.TK2MSFTNGP03.phx.gbl>[vbcol=seagreen]
in[vbcol=seagreen]
a[vbcol=seagreen]
i reading the MOC 2733A for SQL2005 and found the "sys.fn_indexinfo"
function in a module 5 but when i run a sys.fn_indexinfo how indicated a
error showed
Seems that not exist ... it's possible ? It was replaced ?
If so how i can view a index defrag status ?
Thanks in advance.You might have an version of the course that was based on a beta of the prod
uct. My guess is that
the function you want is sys.dm_db_index_physical_stats.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<io.com@.newsgroup.nospam> wrote in message news:O6mSA$DzGHA.3908@.TK2MSFTNGP05.phx.gbl...[vbc
ol=seagreen]
> Hi guys,
> i reading the MOC 2733A for SQL2005 and found the "sys.fn_indexinfo" funct
ion in a module 5 but
> when i run a sys.fn_indexinfo how indicated a error showed
> Seems that not exist ... it's possible ? It was replaced ?
> If so how i can view a index defrag status ?
> Thanks in advance.
>[/vbcol]|||Yes it's right.
Found!
bye.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uGPTSCEzGHA.4972@.TK2MSFTNGP03.phx.gbl...
> You might have an version of the course that was based on a beta of the
> product. My guess is that the function you want is
> sys.dm_db_index_physical_stats.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <io.com@.newsgroup.nospam> wrote in message
> news:O6mSA$DzGHA.3908@.TK2MSFTNGP05.phx.gbl...
>|||Hi IO,
So far as I know, number of information in MOC 2733 is changed since it is
based on SQL 2005 beta. If you have any other questions, feel free to post
in our newsgroup. We are glad to provide assistance.
Have a good day!
Best regards,
Vincent Xu
Microsoft Online Partner Support
========================================
==============
Get Secure! - www.microsoft.com/security
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties,and confers no rights.
========================================
==============
--[vbcol=seagreen]
<uGPTSCEzGHA.4972@.TK2MSFTNGP03.phx.gbl>[vbcol=seagreen]
in[vbcol=seagreen]
a[vbcol=seagreen]
Friday, February 24, 2012
about SQL mail
I want to try SQL mail function in my machine but I can't get it works.
information about my machine:
OS: windows 2000 as a workgroup
sql 2000
office 2000 (also tried office xp)
the mail was sent to my outlook
when I try parameter TYPE with IPC, I got it work but only once. later I
can't get it work. I don't know why.
I don't know how to set up TYPE.
THANKS,
TOMThank you Tony,
I downloaded that file but I don't know how to use it. Could you give me
more information about it?
thanks,
Tom Li
"Tony Rogerson" <tonyrogerson@.sqlserver.eu.com> wrote in message
news:eKZeQTKYDHA.2020@.TK2MSFTNGP10.phx.gbl...
> If you just want to send mails from SQL Server then forget about using
MAPI
> etc... and download the xp_smtp_sendmail extended stored procedure from
> sqldev.net, so long as you have an SMTP server then it will work and its
> really good!
> --
> Tony Rogerson
> SQL Server MVP
> http://www.sqlserverfaq.com?mbr=21
> (Create your own groups, Forum, FAQ's and a ton more)
>|||Hi
Usage is described with example on the download page.
http://www.sqldev.net/xp/xpsmtp.htm
What is your particular problem?
John
"Tom Li" <mrpiiii@.sina.com> wrote in message
news:Oqn30rMYDHA.652@.TK2MSFTNGP10.phx.gbl...
> Thank you Tony,
> I downloaded that file but I don't know how to use it. Could you give me
> more information about it?
> thanks,
> Tom Li
> "Tony Rogerson" <tonyrogerson@.sqlserver.eu.com> wrote in message
> news:eKZeQTKYDHA.2020@.TK2MSFTNGP10.phx.gbl...
> > If you just want to send mails from SQL Server then forget about using
> MAPI
> > etc... and download the xp_smtp_sendmail extended stored procedure from
> > sqldev.net, so long as you have an SMTP server then it will work and its
> > really good!
> >
> > --
> > Tony Rogerson
> > SQL Server MVP
> > http://www.sqlserverfaq.com?mbr=21
> > (Create your own groups, Forum, FAQ's and a ton more)
> >
> >
>|||Hi Tom,
Its best to read through the instructions supplied on the site - there are a
lot of examples - just follow them through.
Tony.
--
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)|||Sorry Sir,
I saw there are two procedure names in the code, xp_smtp_sendmail and
sp_smtp_sendmail. I have no experience of creating stored procedure. so that
may be a stupid question.
And we may be in different location with different time zone. Are you in the
States. I am in China.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:uT7pfHNYDHA.2152@.TK2MSFTNGP09.phx.gbl...
> > I found out that the procedure has different name in the file. Please
see
> > attached file.
> I'm still not clear what the problem is. Also, can you please fix your
> system clock.
>|||Thanks John,
I don't know how to use it after I downloaded xp_smtp_sendmail procedure.
Tom
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3f38e335$0$18495$ed9e5944@.reading.news.pipex.net...
> Hi
> Usage is described with example on the download page.
> http://www.sqldev.net/xp/xpsmtp.htm
> What is your particular problem?
> John
>
> "Tom Li" <mrpiiii@.sina.com> wrote in message
> news:Oqn30rMYDHA.652@.TK2MSFTNGP10.phx.gbl...
> > Thank you Tony,
> >
> > I downloaded that file but I don't know how to use it. Could you give me
> > more information about it?
> >
> > thanks,
> >
> > Tom Li
> >
> > "Tony Rogerson" <tonyrogerson@.sqlserver.eu.com> wrote in message
> > news:eKZeQTKYDHA.2020@.TK2MSFTNGP10.phx.gbl...
> > > If you just want to send mails from SQL Server then forget about using
> > MAPI
> > > etc... and download the xp_smtp_sendmail extended stored procedure
from
> > > sqldev.net, so long as you have an SMTP server then it will work and
its
> > > really good!
> > >
> > > --
> > > Tony Rogerson
> > > SQL Server MVP
> > > http://www.sqlserverfaq.com?mbr=21
> > > (Create your own groups, Forum, FAQ's and a ton more)
> > >
> > >
> >
> >
>|||Hi
On http://www.sqldev.net/xp/xpsmtp.htm there is section called installation
which details how to install the extended stored procedure. The other
examples of how to call the extended stored procedure can be cut and pasted
into query analyser... but at a guess you have MSDE, in which case paste an
example into a text editor and save the file to run from osql. If you do
have MSDE then you may want to look at
http://www.aspfaq.com/show.asp?id=2442
John
"Tom Li" <mrpiiii@.sina.com> wrote in message
news:eyojRMWYDHA.2592@.TK2MSFTNGP09.phx.gbl...
> Thanks John,
> I don't know how to use it after I downloaded xp_smtp_sendmail procedure.
> Tom
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:3f38e335$0$18495$ed9e5944@.reading.news.pipex.net...
> > Hi
> >
> > Usage is described with example on the download page.
> >
> > http://www.sqldev.net/xp/xpsmtp.htm
> >
> > What is your particular problem?
> >
> > John
> >
> >
> > "Tom Li" <mrpiiii@.sina.com> wrote in message
> > news:Oqn30rMYDHA.652@.TK2MSFTNGP10.phx.gbl...
> > > Thank you Tony,
> > >
> > > I downloaded that file but I don't know how to use it. Could you give
me
> > > more information about it?
> > >
> > > thanks,
> > >
> > > Tom Li
> > >
> > > "Tony Rogerson" <tonyrogerson@.sqlserver.eu.com> wrote in message
> > > news:eKZeQTKYDHA.2020@.TK2MSFTNGP10.phx.gbl...
> > > > If you just want to send mails from SQL Server then forget about
using
> > > MAPI
> > > > etc... and download the xp_smtp_sendmail extended stored procedure
> from
> > > > sqldev.net, so long as you have an SMTP server then it will work and
> its
> > > > really good!
> > > >
> > > > --
> > > > Tony Rogerson
> > > > SQL Server MVP
> > > > http://www.sqlserverfaq.com?mbr=21
> > > > (Create your own groups, Forum, FAQ's and a ton more)
> > > >
> > > >
> > >
> > >
> >
> >
>|||Hi, John
It is a really great help. I have installed the procedure. But I can't send
mail. I guess something wrong with the SMTP server. My SMTP server is
"263commail.com" which I used in my outlook express. I try the PING command
with this server name and get the following message:
====================================================C:\>ping 263commail.com
Pinging 263commail.com [211.150.100.11] with 32 bytes of data:
Request timed out.
Request timed out.
Request timed out.
Request timed out.
Ping statistics for 211.150.100.11:
Packets: Sent = 4, Received = 0, Lost = 4 (100% loss),
Approximate round trip times in milli-seconds:
Minimum = 0ms, Maximum = 0ms, Average = 0ms
======================================================I was told that some server stopped any message from a PING command. Also my
outgoing server requires authentication. Is that a problem?
thanks,
Tom
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3f39e4de$0$15036$ed9e5944@.reading.news.pipex.net...
> Hi
> On http://www.sqldev.net/xp/xpsmtp.htm there is section called
installation
> which details how to install the extended stored procedure. The other
> examples of how to call the extended stored procedure can be cut and
pasted
> into query analyser... but at a guess you have MSDE, in which case paste
an
> example into a text editor and save the file to run from osql. If you do
> have MSDE then you may want to look at
> http://www.aspfaq.com/show.asp?id=2442
> John
>
> "Tom Li" <mrpiiii@.sina.com> wrote in message
> news:eyojRMWYDHA.2592@.TK2MSFTNGP09.phx.gbl...
> > Thanks John,
> >
> > I don't know how to use it after I downloaded xp_smtp_sendmail
procedure.
> >
> > Tom
> >
> > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > news:3f38e335$0$18495$ed9e5944@.reading.news.pipex.net...
> > > Hi
> > >
> > > Usage is described with example on the download page.
> > >
> > > http://www.sqldev.net/xp/xpsmtp.htm
> > >
> > > What is your particular problem?
> > >
> > > John
> > >
> > >
> > > "Tom Li" <mrpiiii@.sina.com> wrote in message
> > > news:Oqn30rMYDHA.652@.TK2MSFTNGP10.phx.gbl...
> > > > Thank you Tony,
> > > >
> > > > I downloaded that file but I don't know how to use it. Could you
give
> me
> > > > more information about it?
> > > >
> > > > thanks,
> > > >
> > > > Tom Li
> > > >
> > > > "Tony Rogerson" <tonyrogerson@.sqlserver.eu.com> wrote in message
> > > > news:eKZeQTKYDHA.2020@.TK2MSFTNGP10.phx.gbl...
> > > > > If you just want to send mails from SQL Server then forget about
> using
> > > > MAPI
> > > > > etc... and download the xp_smtp_sendmail extended stored procedure
> > from
> > > > > sqldev.net, so long as you have an SMTP server then it will work
and
> > its
> > > > > really good!
> > > > >
> > > > > --
> > > > > Tony Rogerson
> > > > > SQL Server MVP
> > > > > http://www.sqlserverfaq.com?mbr=21
> > > > > (Create your own groups, Forum, FAQ's and a ton more)
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Hi
I can't say I am a SMTP expert so others might want to chip in!
I am not sure how you would handle authentication as the procedure doesn't
have these parameters. One way may be to have a local server that relays the
mails to the remote server. This would have to be set up so that others
could not hijack your SMTP server! Windows 2000/IIS comes with a SMTP
server.
Some Routers/Firewalls will not reply to PINGS to avoid DOS attacks. You may
want to try telnet, but there is also a @.Ping parameter to xp_smtp. Check
that you should be using the default port, if not may need to specify a
different port using the @.port parameter.
John
"Tom Li" <mrpiiii@.sina.com> wrote in message
news:e9x9n%23XYDHA.1056@.TK2MSFTNGP10.phx.gbl...
> Hi, John
> It is a really great help. I have installed the procedure. But I can't
send
> mail. I guess something wrong with the SMTP server. My SMTP server is
> "263commail.com" which I used in my outlook express. I try the PING
command
> with this server name and get the following message:
> ====================================================> C:\>ping 263commail.com
> Pinging 263commail.com [211.150.100.11] with 32 bytes of data:
> Request timed out.
> Request timed out.
> Request timed out.
> Request timed out.
> Ping statistics for 211.150.100.11:
> Packets: Sent = 4, Received = 0, Lost = 4 (100% loss),
> Approximate round trip times in milli-seconds:
> Minimum = 0ms, Maximum = 0ms, Average = 0ms
> ======================================================> I was told that some server stopped any message from a PING command. Also
my
> outgoing server requires authentication. Is that a problem?
> thanks,
> Tom
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:3f39e4de$0$15036$ed9e5944@.reading.news.pipex.net...
> > Hi
> >
> > On http://www.sqldev.net/xp/xpsmtp.htm there is section called
> installation
> > which details how to install the extended stored procedure. The other
> > examples of how to call the extended stored procedure can be cut and
> pasted
> > into query analyser... but at a guess you have MSDE, in which case
paste
> an
> > example into a text editor and save the file to run from osql. If you do
> > have MSDE then you may want to look at
> > http://www.aspfaq.com/show.asp?id=2442
> >
> > John
> >
> >
> > "Tom Li" <mrpiiii@.sina.com> wrote in message
> > news:eyojRMWYDHA.2592@.TK2MSFTNGP09.phx.gbl...
> > > Thanks John,
> > >
> > > I don't know how to use it after I downloaded xp_smtp_sendmail
> procedure.
> > >
> > > Tom
> > >
> > > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > > news:3f38e335$0$18495$ed9e5944@.reading.news.pipex.net...
> > > > Hi
> > > >
> > > > Usage is described with example on the download page.
> > > >
> > > > http://www.sqldev.net/xp/xpsmtp.htm
> > > >
> > > > What is your particular problem?
> > > >
> > > > John
> > > >
> > > >
> > > > "Tom Li" <mrpiiii@.sina.com> wrote in message
> > > > news:Oqn30rMYDHA.652@.TK2MSFTNGP10.phx.gbl...
> > > > > Thank you Tony,
> > > > >
> > > > > I downloaded that file but I don't know how to use it. Could you
> give
> > me
> > > > > more information about it?
> > > > >
> > > > > thanks,
> > > > >
> > > > > Tom Li
> > > > >
> > > > > "Tony Rogerson" <tonyrogerson@.sqlserver.eu.com> wrote in message
> > > > > news:eKZeQTKYDHA.2020@.TK2MSFTNGP10.phx.gbl...
> > > > > > If you just want to send mails from SQL Server then forget about
> > using
> > > > > MAPI
> > > > > > etc... and download the xp_smtp_sendmail extended stored
procedure
> > > from
> > > > > > sqldev.net, so long as you have an SMTP server then it will work
> and
> > > its
> > > > > > really good!
> > > > > >
> > > > > > --
> > > > > > Tony Rogerson
> > > > > > SQL Server MVP
> > > > > > http://www.sqlserverfaq.com?mbr=21
> > > > > > (Create your own groups, Forum, FAQ's and a ton more)
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||> I saw there are two procedure names in the code, xp_smtp_sendmail and
> sp_smtp_sendmail.
xp_smtp_sendmail is the extended stored procedure (a DLL file) that you
download from www.sqldev.net and register in SQL Server according to the
instructions on that site.
sp_smtp_sendmail is a wrapper for the call to this stored procedure... you
can simplify the number and/or order of parameters. I have a wrapper for
xp_smtp_sendmail called smtpmail which has only a few of the parameters
(because I don't use many of the functions that come with the xp).
> And we may be in different location with different time zone. Are you in
the
> States. I am in China.
The news server adjusts your posting time (according to your time zone) to
the reader's time zone settings. This is why *most* threads appear to occur
in chronological order; however, if my reply shows up 7 1/2 hours before
your initial post (what does *your* newsreader tell you are the times of my
previous reply, and your message that I replied to?), then either your clock
is fast, or your time zone is incorrect. For more information, see:
http://www.aspfaq.com/5005
http://www.aspfaq.com/2257|||Thanks for your explanation about the procedure name.
You are really a MVP. My time zone was not correct, which was: (GMT)
Casablanca, Monrovia. Now I correct it.
thanks,
Tom
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:uAtAh5ZYDHA.2960@.tk2msftngp13.phx.gbl...
> > I saw there are two procedure names in the code, xp_smtp_sendmail and
> > sp_smtp_sendmail.
> xp_smtp_sendmail is the extended stored procedure (a DLL file) that you
> download from www.sqldev.net and register in SQL Server according to the
> instructions on that site.
> sp_smtp_sendmail is a wrapper for the call to this stored procedure... you
> can simplify the number and/or order of parameters. I have a wrapper for
> xp_smtp_sendmail called smtpmail which has only a few of the parameters
> (because I don't use many of the functions that come with the xp).
> > And we may be in different location with different time zone. Are you in
> the
> > States. I am in China.
> The news server adjusts your posting time (according to your time zone) to
> the reader's time zone settings. This is why *most* threads appear to
occur
> in chronological order; however, if my reply shows up 7 1/2 hours before
> your initial post (what does *your* newsreader tell you are the times of
my
> previous reply, and your message that I replied to?), then either your
clock
> is fast, or your time zone is incorrect. For more information, see:
> http://www.aspfaq.com/5005
> http://www.aspfaq.com/2257
>
>|||Hi,
Your thoughts is correct. I am thinking about to setup a local SMTP server.
Thanks
Tom
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3f3a19ed$0$15041$ed9e5944@.reading.news.pipex.net...
> Hi
> I can't say I am a SMTP expert so others might want to chip in!
> I am not sure how you would handle authentication as the procedure doesn't
> have these parameters. One way may be to have a local server that relays
the
> mails to the remote server. This would have to be set up so that others
> could not hijack your SMTP server! Windows 2000/IIS comes with a SMTP
> server.
> Some Routers/Firewalls will not reply to PINGS to avoid DOS attacks. You
may
> want to try telnet, but there is also a @.Ping parameter to xp_smtp. Check
> that you should be using the default port, if not may need to specify a
> different port using the @.port parameter.
> John
>
> "Tom Li" <mrpiiii@.sina.com> wrote in message
> news:e9x9n%23XYDHA.1056@.TK2MSFTNGP10.phx.gbl...
> > Hi, John
> >
> > It is a really great help. I have installed the procedure. But I can't
> send
> > mail. I guess something wrong with the SMTP server. My SMTP server is
> > "263commail.com" which I used in my outlook express. I try the PING
> command
> > with this server name and get the following message:
> > ====================================================> > C:\>ping 263commail.com
> >
> > Pinging 263commail.com [211.150.100.11] with 32 bytes of data:
> >
> > Request timed out.
> > Request timed out.
> > Request timed out.
> > Request timed out.
> >
> > Ping statistics for 211.150.100.11:
> > Packets: Sent = 4, Received = 0, Lost = 4 (100% loss),
> > Approximate round trip times in milli-seconds:
> > Minimum = 0ms, Maximum = 0ms, Average = 0ms
> > ======================================================> > I was told that some server stopped any message from a PING command.
Also
> my
> > outgoing server requires authentication. Is that a problem?
> >
> > thanks,
> >
> > Tom
> >
> >
> >
> > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > news:3f39e4de$0$15036$ed9e5944@.reading.news.pipex.net...
> > > Hi
> > >
> > > On http://www.sqldev.net/xp/xpsmtp.htm there is section called
> > installation
> > > which details how to install the extended stored procedure. The other
> > > examples of how to call the extended stored procedure can be cut and
> > pasted
> > > into query analyser... but at a guess you have MSDE, in which case
> paste
> > an
> > > example into a text editor and save the file to run from osql. If you
do
> > > have MSDE then you may want to look at
> > > http://www.aspfaq.com/show.asp?id=2442
> > >
> > > John
> > >
> > >
> > > "Tom Li" <mrpiiii@.sina.com> wrote in message
> > > news:eyojRMWYDHA.2592@.TK2MSFTNGP09.phx.gbl...
> > > > Thanks John,
> > > >
> > > > I don't know how to use it after I downloaded xp_smtp_sendmail
> > procedure.
> > > >
> > > > Tom
> > > >
> > > > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > > > news:3f38e335$0$18495$ed9e5944@.reading.news.pipex.net...
> > > > > Hi
> > > > >
> > > > > Usage is described with example on the download page.
> > > > >
> > > > > http://www.sqldev.net/xp/xpsmtp.htm
> > > > >
> > > > > What is your particular problem?
> > > > >
> > > > > John
> > > > >
> > > > >
> > > > > "Tom Li" <mrpiiii@.sina.com> wrote in message
> > > > > news:Oqn30rMYDHA.652@.TK2MSFTNGP10.phx.gbl...
> > > > > > Thank you Tony,
> > > > > >
> > > > > > I downloaded that file but I don't know how to use it. Could you
> > give
> > > me
> > > > > > more information about it?
> > > > > >
> > > > > > thanks,
> > > > > >
> > > > > > Tom Li
> > > > > >
> > > > > > "Tony Rogerson" <tonyrogerson@.sqlserver.eu.com> wrote in message
> > > > > > news:eKZeQTKYDHA.2020@.TK2MSFTNGP10.phx.gbl...
> > > > > > > If you just want to send mails from SQL Server then forget
about
> > > using
> > > > > > MAPI
> > > > > > > etc... and download the xp_smtp_sendmail extended stored
> procedure
> > > > from
> > > > > > > sqldev.net, so long as you have an SMTP server then it will
work
> > and
> > > > its
> > > > > > > really good!
> > > > > > >
> > > > > > > --
> > > > > > > Tony Rogerson
> > > > > > > SQL Server MVP
> > > > > > > http://www.sqlserverfaq.com?mbr=21
> > > > > > > (Create your own groups, Forum, FAQ's and a ton more)
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||> But I recommend setting up an SMTP server (e.g. within your
> firewall) that doesn't require outgoing authentication.
I think that is a good way to me. But my current condition is not good
enough to do it so that I have to do it later. Anyway I appreciate your
great help and I do learn a lot from you.
Thank you very much
Tom
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:%23dKH$5ZYDHA.1480@.tk2msftngp13.phx.gbl...
> > Also my outgoing server requires authentication. Is that a problem?
> Yes, that is a problem. You will need another SMTP server, or you will
need
> to use a COM object which supports outgoing authentication (e.g.
> www.aspemail.com). You can instantiate such a COM object from within a
> stored procedure using the sp_OA* stored procedure calls. But I recommend
> setting up an SMTP server (e.g. within your firewall) that doesn't require
> outgoing authentication.
>
information about my machine:
OS: windows 2000 as a workgroup
sql 2000
office 2000 (also tried office xp)
the mail was sent to my outlook
when I try parameter TYPE with IPC, I got it work but only once. later I
can't get it work. I don't know why.
I don't know how to set up TYPE.
THANKS,
TOMThank you Tony,
I downloaded that file but I don't know how to use it. Could you give me
more information about it?
thanks,
Tom Li
"Tony Rogerson" <tonyrogerson@.sqlserver.eu.com> wrote in message
news:eKZeQTKYDHA.2020@.TK2MSFTNGP10.phx.gbl...
> If you just want to send mails from SQL Server then forget about using
MAPI
> etc... and download the xp_smtp_sendmail extended stored procedure from
> sqldev.net, so long as you have an SMTP server then it will work and its
> really good!
> --
> Tony Rogerson
> SQL Server MVP
> http://www.sqlserverfaq.com?mbr=21
> (Create your own groups, Forum, FAQ's and a ton more)
>|||Hi
Usage is described with example on the download page.
http://www.sqldev.net/xp/xpsmtp.htm
What is your particular problem?
John
"Tom Li" <mrpiiii@.sina.com> wrote in message
news:Oqn30rMYDHA.652@.TK2MSFTNGP10.phx.gbl...
> Thank you Tony,
> I downloaded that file but I don't know how to use it. Could you give me
> more information about it?
> thanks,
> Tom Li
> "Tony Rogerson" <tonyrogerson@.sqlserver.eu.com> wrote in message
> news:eKZeQTKYDHA.2020@.TK2MSFTNGP10.phx.gbl...
> > If you just want to send mails from SQL Server then forget about using
> MAPI
> > etc... and download the xp_smtp_sendmail extended stored procedure from
> > sqldev.net, so long as you have an SMTP server then it will work and its
> > really good!
> >
> > --
> > Tony Rogerson
> > SQL Server MVP
> > http://www.sqlserverfaq.com?mbr=21
> > (Create your own groups, Forum, FAQ's and a ton more)
> >
> >
>|||Hi Tom,
Its best to read through the instructions supplied on the site - there are a
lot of examples - just follow them through.
Tony.
--
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)|||Sorry Sir,
I saw there are two procedure names in the code, xp_smtp_sendmail and
sp_smtp_sendmail. I have no experience of creating stored procedure. so that
may be a stupid question.
And we may be in different location with different time zone. Are you in the
States. I am in China.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:uT7pfHNYDHA.2152@.TK2MSFTNGP09.phx.gbl...
> > I found out that the procedure has different name in the file. Please
see
> > attached file.
> I'm still not clear what the problem is. Also, can you please fix your
> system clock.
>|||Thanks John,
I don't know how to use it after I downloaded xp_smtp_sendmail procedure.
Tom
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3f38e335$0$18495$ed9e5944@.reading.news.pipex.net...
> Hi
> Usage is described with example on the download page.
> http://www.sqldev.net/xp/xpsmtp.htm
> What is your particular problem?
> John
>
> "Tom Li" <mrpiiii@.sina.com> wrote in message
> news:Oqn30rMYDHA.652@.TK2MSFTNGP10.phx.gbl...
> > Thank you Tony,
> >
> > I downloaded that file but I don't know how to use it. Could you give me
> > more information about it?
> >
> > thanks,
> >
> > Tom Li
> >
> > "Tony Rogerson" <tonyrogerson@.sqlserver.eu.com> wrote in message
> > news:eKZeQTKYDHA.2020@.TK2MSFTNGP10.phx.gbl...
> > > If you just want to send mails from SQL Server then forget about using
> > MAPI
> > > etc... and download the xp_smtp_sendmail extended stored procedure
from
> > > sqldev.net, so long as you have an SMTP server then it will work and
its
> > > really good!
> > >
> > > --
> > > Tony Rogerson
> > > SQL Server MVP
> > > http://www.sqlserverfaq.com?mbr=21
> > > (Create your own groups, Forum, FAQ's and a ton more)
> > >
> > >
> >
> >
>|||Hi
On http://www.sqldev.net/xp/xpsmtp.htm there is section called installation
which details how to install the extended stored procedure. The other
examples of how to call the extended stored procedure can be cut and pasted
into query analyser... but at a guess you have MSDE, in which case paste an
example into a text editor and save the file to run from osql. If you do
have MSDE then you may want to look at
http://www.aspfaq.com/show.asp?id=2442
John
"Tom Li" <mrpiiii@.sina.com> wrote in message
news:eyojRMWYDHA.2592@.TK2MSFTNGP09.phx.gbl...
> Thanks John,
> I don't know how to use it after I downloaded xp_smtp_sendmail procedure.
> Tom
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:3f38e335$0$18495$ed9e5944@.reading.news.pipex.net...
> > Hi
> >
> > Usage is described with example on the download page.
> >
> > http://www.sqldev.net/xp/xpsmtp.htm
> >
> > What is your particular problem?
> >
> > John
> >
> >
> > "Tom Li" <mrpiiii@.sina.com> wrote in message
> > news:Oqn30rMYDHA.652@.TK2MSFTNGP10.phx.gbl...
> > > Thank you Tony,
> > >
> > > I downloaded that file but I don't know how to use it. Could you give
me
> > > more information about it?
> > >
> > > thanks,
> > >
> > > Tom Li
> > >
> > > "Tony Rogerson" <tonyrogerson@.sqlserver.eu.com> wrote in message
> > > news:eKZeQTKYDHA.2020@.TK2MSFTNGP10.phx.gbl...
> > > > If you just want to send mails from SQL Server then forget about
using
> > > MAPI
> > > > etc... and download the xp_smtp_sendmail extended stored procedure
> from
> > > > sqldev.net, so long as you have an SMTP server then it will work and
> its
> > > > really good!
> > > >
> > > > --
> > > > Tony Rogerson
> > > > SQL Server MVP
> > > > http://www.sqlserverfaq.com?mbr=21
> > > > (Create your own groups, Forum, FAQ's and a ton more)
> > > >
> > > >
> > >
> > >
> >
> >
>|||Hi, John
It is a really great help. I have installed the procedure. But I can't send
mail. I guess something wrong with the SMTP server. My SMTP server is
"263commail.com" which I used in my outlook express. I try the PING command
with this server name and get the following message:
====================================================C:\>ping 263commail.com
Pinging 263commail.com [211.150.100.11] with 32 bytes of data:
Request timed out.
Request timed out.
Request timed out.
Request timed out.
Ping statistics for 211.150.100.11:
Packets: Sent = 4, Received = 0, Lost = 4 (100% loss),
Approximate round trip times in milli-seconds:
Minimum = 0ms, Maximum = 0ms, Average = 0ms
======================================================I was told that some server stopped any message from a PING command. Also my
outgoing server requires authentication. Is that a problem?
thanks,
Tom
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3f39e4de$0$15036$ed9e5944@.reading.news.pipex.net...
> Hi
> On http://www.sqldev.net/xp/xpsmtp.htm there is section called
installation
> which details how to install the extended stored procedure. The other
> examples of how to call the extended stored procedure can be cut and
pasted
> into query analyser... but at a guess you have MSDE, in which case paste
an
> example into a text editor and save the file to run from osql. If you do
> have MSDE then you may want to look at
> http://www.aspfaq.com/show.asp?id=2442
> John
>
> "Tom Li" <mrpiiii@.sina.com> wrote in message
> news:eyojRMWYDHA.2592@.TK2MSFTNGP09.phx.gbl...
> > Thanks John,
> >
> > I don't know how to use it after I downloaded xp_smtp_sendmail
procedure.
> >
> > Tom
> >
> > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > news:3f38e335$0$18495$ed9e5944@.reading.news.pipex.net...
> > > Hi
> > >
> > > Usage is described with example on the download page.
> > >
> > > http://www.sqldev.net/xp/xpsmtp.htm
> > >
> > > What is your particular problem?
> > >
> > > John
> > >
> > >
> > > "Tom Li" <mrpiiii@.sina.com> wrote in message
> > > news:Oqn30rMYDHA.652@.TK2MSFTNGP10.phx.gbl...
> > > > Thank you Tony,
> > > >
> > > > I downloaded that file but I don't know how to use it. Could you
give
> me
> > > > more information about it?
> > > >
> > > > thanks,
> > > >
> > > > Tom Li
> > > >
> > > > "Tony Rogerson" <tonyrogerson@.sqlserver.eu.com> wrote in message
> > > > news:eKZeQTKYDHA.2020@.TK2MSFTNGP10.phx.gbl...
> > > > > If you just want to send mails from SQL Server then forget about
> using
> > > > MAPI
> > > > > etc... and download the xp_smtp_sendmail extended stored procedure
> > from
> > > > > sqldev.net, so long as you have an SMTP server then it will work
and
> > its
> > > > > really good!
> > > > >
> > > > > --
> > > > > Tony Rogerson
> > > > > SQL Server MVP
> > > > > http://www.sqlserverfaq.com?mbr=21
> > > > > (Create your own groups, Forum, FAQ's and a ton more)
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Hi
I can't say I am a SMTP expert so others might want to chip in!
I am not sure how you would handle authentication as the procedure doesn't
have these parameters. One way may be to have a local server that relays the
mails to the remote server. This would have to be set up so that others
could not hijack your SMTP server! Windows 2000/IIS comes with a SMTP
server.
Some Routers/Firewalls will not reply to PINGS to avoid DOS attacks. You may
want to try telnet, but there is also a @.Ping parameter to xp_smtp. Check
that you should be using the default port, if not may need to specify a
different port using the @.port parameter.
John
"Tom Li" <mrpiiii@.sina.com> wrote in message
news:e9x9n%23XYDHA.1056@.TK2MSFTNGP10.phx.gbl...
> Hi, John
> It is a really great help. I have installed the procedure. But I can't
send
> mail. I guess something wrong with the SMTP server. My SMTP server is
> "263commail.com" which I used in my outlook express. I try the PING
command
> with this server name and get the following message:
> ====================================================> C:\>ping 263commail.com
> Pinging 263commail.com [211.150.100.11] with 32 bytes of data:
> Request timed out.
> Request timed out.
> Request timed out.
> Request timed out.
> Ping statistics for 211.150.100.11:
> Packets: Sent = 4, Received = 0, Lost = 4 (100% loss),
> Approximate round trip times in milli-seconds:
> Minimum = 0ms, Maximum = 0ms, Average = 0ms
> ======================================================> I was told that some server stopped any message from a PING command. Also
my
> outgoing server requires authentication. Is that a problem?
> thanks,
> Tom
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:3f39e4de$0$15036$ed9e5944@.reading.news.pipex.net...
> > Hi
> >
> > On http://www.sqldev.net/xp/xpsmtp.htm there is section called
> installation
> > which details how to install the extended stored procedure. The other
> > examples of how to call the extended stored procedure can be cut and
> pasted
> > into query analyser... but at a guess you have MSDE, in which case
paste
> an
> > example into a text editor and save the file to run from osql. If you do
> > have MSDE then you may want to look at
> > http://www.aspfaq.com/show.asp?id=2442
> >
> > John
> >
> >
> > "Tom Li" <mrpiiii@.sina.com> wrote in message
> > news:eyojRMWYDHA.2592@.TK2MSFTNGP09.phx.gbl...
> > > Thanks John,
> > >
> > > I don't know how to use it after I downloaded xp_smtp_sendmail
> procedure.
> > >
> > > Tom
> > >
> > > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > > news:3f38e335$0$18495$ed9e5944@.reading.news.pipex.net...
> > > > Hi
> > > >
> > > > Usage is described with example on the download page.
> > > >
> > > > http://www.sqldev.net/xp/xpsmtp.htm
> > > >
> > > > What is your particular problem?
> > > >
> > > > John
> > > >
> > > >
> > > > "Tom Li" <mrpiiii@.sina.com> wrote in message
> > > > news:Oqn30rMYDHA.652@.TK2MSFTNGP10.phx.gbl...
> > > > > Thank you Tony,
> > > > >
> > > > > I downloaded that file but I don't know how to use it. Could you
> give
> > me
> > > > > more information about it?
> > > > >
> > > > > thanks,
> > > > >
> > > > > Tom Li
> > > > >
> > > > > "Tony Rogerson" <tonyrogerson@.sqlserver.eu.com> wrote in message
> > > > > news:eKZeQTKYDHA.2020@.TK2MSFTNGP10.phx.gbl...
> > > > > > If you just want to send mails from SQL Server then forget about
> > using
> > > > > MAPI
> > > > > > etc... and download the xp_smtp_sendmail extended stored
procedure
> > > from
> > > > > > sqldev.net, so long as you have an SMTP server then it will work
> and
> > > its
> > > > > > really good!
> > > > > >
> > > > > > --
> > > > > > Tony Rogerson
> > > > > > SQL Server MVP
> > > > > > http://www.sqlserverfaq.com?mbr=21
> > > > > > (Create your own groups, Forum, FAQ's and a ton more)
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||> I saw there are two procedure names in the code, xp_smtp_sendmail and
> sp_smtp_sendmail.
xp_smtp_sendmail is the extended stored procedure (a DLL file) that you
download from www.sqldev.net and register in SQL Server according to the
instructions on that site.
sp_smtp_sendmail is a wrapper for the call to this stored procedure... you
can simplify the number and/or order of parameters. I have a wrapper for
xp_smtp_sendmail called smtpmail which has only a few of the parameters
(because I don't use many of the functions that come with the xp).
> And we may be in different location with different time zone. Are you in
the
> States. I am in China.
The news server adjusts your posting time (according to your time zone) to
the reader's time zone settings. This is why *most* threads appear to occur
in chronological order; however, if my reply shows up 7 1/2 hours before
your initial post (what does *your* newsreader tell you are the times of my
previous reply, and your message that I replied to?), then either your clock
is fast, or your time zone is incorrect. For more information, see:
http://www.aspfaq.com/5005
http://www.aspfaq.com/2257|||Thanks for your explanation about the procedure name.
You are really a MVP. My time zone was not correct, which was: (GMT)
Casablanca, Monrovia. Now I correct it.
thanks,
Tom
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:uAtAh5ZYDHA.2960@.tk2msftngp13.phx.gbl...
> > I saw there are two procedure names in the code, xp_smtp_sendmail and
> > sp_smtp_sendmail.
> xp_smtp_sendmail is the extended stored procedure (a DLL file) that you
> download from www.sqldev.net and register in SQL Server according to the
> instructions on that site.
> sp_smtp_sendmail is a wrapper for the call to this stored procedure... you
> can simplify the number and/or order of parameters. I have a wrapper for
> xp_smtp_sendmail called smtpmail which has only a few of the parameters
> (because I don't use many of the functions that come with the xp).
> > And we may be in different location with different time zone. Are you in
> the
> > States. I am in China.
> The news server adjusts your posting time (according to your time zone) to
> the reader's time zone settings. This is why *most* threads appear to
occur
> in chronological order; however, if my reply shows up 7 1/2 hours before
> your initial post (what does *your* newsreader tell you are the times of
my
> previous reply, and your message that I replied to?), then either your
clock
> is fast, or your time zone is incorrect. For more information, see:
> http://www.aspfaq.com/5005
> http://www.aspfaq.com/2257
>
>|||Hi,
Your thoughts is correct. I am thinking about to setup a local SMTP server.
Thanks
Tom
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3f3a19ed$0$15041$ed9e5944@.reading.news.pipex.net...
> Hi
> I can't say I am a SMTP expert so others might want to chip in!
> I am not sure how you would handle authentication as the procedure doesn't
> have these parameters. One way may be to have a local server that relays
the
> mails to the remote server. This would have to be set up so that others
> could not hijack your SMTP server! Windows 2000/IIS comes with a SMTP
> server.
> Some Routers/Firewalls will not reply to PINGS to avoid DOS attacks. You
may
> want to try telnet, but there is also a @.Ping parameter to xp_smtp. Check
> that you should be using the default port, if not may need to specify a
> different port using the @.port parameter.
> John
>
> "Tom Li" <mrpiiii@.sina.com> wrote in message
> news:e9x9n%23XYDHA.1056@.TK2MSFTNGP10.phx.gbl...
> > Hi, John
> >
> > It is a really great help. I have installed the procedure. But I can't
> send
> > mail. I guess something wrong with the SMTP server. My SMTP server is
> > "263commail.com" which I used in my outlook express. I try the PING
> command
> > with this server name and get the following message:
> > ====================================================> > C:\>ping 263commail.com
> >
> > Pinging 263commail.com [211.150.100.11] with 32 bytes of data:
> >
> > Request timed out.
> > Request timed out.
> > Request timed out.
> > Request timed out.
> >
> > Ping statistics for 211.150.100.11:
> > Packets: Sent = 4, Received = 0, Lost = 4 (100% loss),
> > Approximate round trip times in milli-seconds:
> > Minimum = 0ms, Maximum = 0ms, Average = 0ms
> > ======================================================> > I was told that some server stopped any message from a PING command.
Also
> my
> > outgoing server requires authentication. Is that a problem?
> >
> > thanks,
> >
> > Tom
> >
> >
> >
> > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > news:3f39e4de$0$15036$ed9e5944@.reading.news.pipex.net...
> > > Hi
> > >
> > > On http://www.sqldev.net/xp/xpsmtp.htm there is section called
> > installation
> > > which details how to install the extended stored procedure. The other
> > > examples of how to call the extended stored procedure can be cut and
> > pasted
> > > into query analyser... but at a guess you have MSDE, in which case
> paste
> > an
> > > example into a text editor and save the file to run from osql. If you
do
> > > have MSDE then you may want to look at
> > > http://www.aspfaq.com/show.asp?id=2442
> > >
> > > John
> > >
> > >
> > > "Tom Li" <mrpiiii@.sina.com> wrote in message
> > > news:eyojRMWYDHA.2592@.TK2MSFTNGP09.phx.gbl...
> > > > Thanks John,
> > > >
> > > > I don't know how to use it after I downloaded xp_smtp_sendmail
> > procedure.
> > > >
> > > > Tom
> > > >
> > > > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > > > news:3f38e335$0$18495$ed9e5944@.reading.news.pipex.net...
> > > > > Hi
> > > > >
> > > > > Usage is described with example on the download page.
> > > > >
> > > > > http://www.sqldev.net/xp/xpsmtp.htm
> > > > >
> > > > > What is your particular problem?
> > > > >
> > > > > John
> > > > >
> > > > >
> > > > > "Tom Li" <mrpiiii@.sina.com> wrote in message
> > > > > news:Oqn30rMYDHA.652@.TK2MSFTNGP10.phx.gbl...
> > > > > > Thank you Tony,
> > > > > >
> > > > > > I downloaded that file but I don't know how to use it. Could you
> > give
> > > me
> > > > > > more information about it?
> > > > > >
> > > > > > thanks,
> > > > > >
> > > > > > Tom Li
> > > > > >
> > > > > > "Tony Rogerson" <tonyrogerson@.sqlserver.eu.com> wrote in message
> > > > > > news:eKZeQTKYDHA.2020@.TK2MSFTNGP10.phx.gbl...
> > > > > > > If you just want to send mails from SQL Server then forget
about
> > > using
> > > > > > MAPI
> > > > > > > etc... and download the xp_smtp_sendmail extended stored
> procedure
> > > > from
> > > > > > > sqldev.net, so long as you have an SMTP server then it will
work
> > and
> > > > its
> > > > > > > really good!
> > > > > > >
> > > > > > > --
> > > > > > > Tony Rogerson
> > > > > > > SQL Server MVP
> > > > > > > http://www.sqlserverfaq.com?mbr=21
> > > > > > > (Create your own groups, Forum, FAQ's and a ton more)
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||> But I recommend setting up an SMTP server (e.g. within your
> firewall) that doesn't require outgoing authentication.
I think that is a good way to me. But my current condition is not good
enough to do it so that I have to do it later. Anyway I appreciate your
great help and I do learn a lot from you.
Thank you very much
Tom
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:%23dKH$5ZYDHA.1480@.tk2msftngp13.phx.gbl...
> > Also my outgoing server requires authentication. Is that a problem?
> Yes, that is a problem. You will need another SMTP server, or you will
need
> to use a COM object which supports outgoing authentication (e.g.
> www.aspemail.com). You can instantiate such a COM object from within a
> stored procedure using the sp_OA* stored procedure calls. But I recommend
> setting up an SMTP server (e.g. within your firewall) that doesn't require
> outgoing authentication.
>
Thursday, February 16, 2012
about option Can Grow
Who know when feild some feild have rows more one.Have function to check this?Why do you want to check this?|||I use feild to make table I use bottom border of each feild but have some problem that problem is if some feild can grow more one row the table will not complete feild will overlap Thank you very much for your reply
about OdbcParameter
ERROR [42000] [Microsoft][SQL Native Client][SQL Server]Procedure or Function 'abc' expects parameter'@.xyz', which was not supplied.
i have supplied the parameter in the storeprocedure and the odbccommand
anyone why this error still happening?
thanks
How did you supply the parameter? Could you post related code?About memory amount that SQL Server uses
Is there any function or something like that that returs the amount of MS
SQL Server 2000 memory usage?
Thanks.Take a look into dbcc perfmon command.
Thanks
Hari
SQL Server MVP
"Grcan YCEL" <gurcan@.workmail.com> wrote in message
news:eOwBUw2xGHA.3500@.TK2MSFTNGP02.phx.gbl...
> Is there any function or something like that that returs the amount of MS
> SQL Server 2000 memory usage?
> Thanks.
>|||DBCC MEMORYSTATUS returns the buffer memory allocations (in units of 8kb
pages). This reports the main memory allocations used for data caching &
execution plan caching but there are other memory allocations which you can
collect from the Perfmon's Process(sqlservr) performance counter.. If you
want to translate the buffer counts to Mb, simply multiply them by 8192.0 &
divide by 1048576.0
Regards,
Greg Linwood
SQL Server MVP
"Grcan YCEL" <gurcan@.workmail.com> wrote in message
news:eOwBUw2xGHA.3500@.TK2MSFTNGP02.phx.gbl...
> Is there any function or something like that that returs the amount of MS
> SQL Server 2000 memory usage?
> Thanks.
>
SQL Server 2000 memory usage?
Thanks.Take a look into dbcc perfmon command.
Thanks
Hari
SQL Server MVP
"Grcan YCEL" <gurcan@.workmail.com> wrote in message
news:eOwBUw2xGHA.3500@.TK2MSFTNGP02.phx.gbl...
> Is there any function or something like that that returs the amount of MS
> SQL Server 2000 memory usage?
> Thanks.
>|||DBCC MEMORYSTATUS returns the buffer memory allocations (in units of 8kb
pages). This reports the main memory allocations used for data caching &
execution plan caching but there are other memory allocations which you can
collect from the Perfmon's Process(sqlservr) performance counter.. If you
want to translate the buffer counts to Mb, simply multiply them by 8192.0 &
divide by 1048576.0
Regards,
Greg Linwood
SQL Server MVP
"Grcan YCEL" <gurcan@.workmail.com> wrote in message
news:eOwBUw2xGHA.3500@.TK2MSFTNGP02.phx.gbl...
> Is there any function or something like that that returs the amount of MS
> SQL Server 2000 memory usage?
> Thanks.
>
About memory amount that SQL Server uses
Is there any function or something like that that returs the amount of MS
SQL Server 2000 memory usage?
Thanks.Take a look into dbcc perfmon command.
Thanks
Hari
SQL Server MVP
"Gürcan YÜCEL" <gurcan@.workmail.com> wrote in message
news:eOwBUw2xGHA.3500@.TK2MSFTNGP02.phx.gbl...
> Is there any function or something like that that returs the amount of MS
> SQL Server 2000 memory usage?
> Thanks.
>|||DBCC MEMORYSTATUS returns the buffer memory allocations (in units of 8kb
pages). This reports the main memory allocations used for data caching &
execution plan caching but there are other memory allocations which you can
collect from the Perfmon's Process(sqlservr) performance counter.. If you
want to translate the buffer counts to Mb, simply multiply them by 8192.0 &
divide by 1048576.0
Regards,
Greg Linwood
SQL Server MVP
"Gürcan YÜCEL" <gurcan@.workmail.com> wrote in message
news:eOwBUw2xGHA.3500@.TK2MSFTNGP02.phx.gbl...
> Is there any function or something like that that returs the amount of MS
> SQL Server 2000 memory usage?
> Thanks.
>
SQL Server 2000 memory usage?
Thanks.Take a look into dbcc perfmon command.
Thanks
Hari
SQL Server MVP
"Gürcan YÜCEL" <gurcan@.workmail.com> wrote in message
news:eOwBUw2xGHA.3500@.TK2MSFTNGP02.phx.gbl...
> Is there any function or something like that that returs the amount of MS
> SQL Server 2000 memory usage?
> Thanks.
>|||DBCC MEMORYSTATUS returns the buffer memory allocations (in units of 8kb
pages). This reports the main memory allocations used for data caching &
execution plan caching but there are other memory allocations which you can
collect from the Perfmon's Process(sqlservr) performance counter.. If you
want to translate the buffer counts to Mb, simply multiply them by 8192.0 &
divide by 1048576.0
Regards,
Greg Linwood
SQL Server MVP
"Gürcan YÜCEL" <gurcan@.workmail.com> wrote in message
news:eOwBUw2xGHA.3500@.TK2MSFTNGP02.phx.gbl...
> Is there any function or something like that that returs the amount of MS
> SQL Server 2000 memory usage?
> Thanks.
>
About Members and Ordinal function
When I use MDX sample application,
I write a code like this:
But it failed.
I write a code like this:
SELECT
{[Time].[All Time].[2005]} ON COLUMNS,
{Filter([Equipment].Members, (InStr(1, [Equipment].CurrentMember.Name, "xxxx") =1))} ON ROWS
FROM francis.
Here Equipment is a huge dimension table,actually I want to do it like
But it failed.
One more is I can't use ordinal function in it too, but I can do it in AS and test in pivottables.
Can someone tell me the reasons?
This is probably the same issue as I mentioned on the other thread - you probably need to use the .Children function instead of the .Members function. The .Children function returns all the members 'underneath' a member in a hierarchy, the .Members function returns all the members on a dimension or level. So:
[Equipment].[All types].[mechanism].Children
should work for you.
I'm not sure what you mean about your problem with the Ordinal function - can you provide more details?
Chris|||Hi, Chris, give you more details as example. I assume[Equipment].[All types].[mechanism].[machine001] have [abc],[bcd],[efg],[hij] these children like [Equipment].[All types].[mechanism].[machine001].[abc], and in them they all have a children call [ABSmain_xxxx] like [Equipment].[All types].[mechanism].[machine001].[abc].ABSmain_abc.
Now I want to average all the ABSmain_xxx value, so I will do a filter to the set I reach now.
If I use [Equipment].[All types].[mechanism].[machine001].Children, it will only filter the [abc],[bcd]...etc, it won't go deep to their children.
I suppose to do it like [Equipment].[All types].[mechanism].[machine001].[abc].level.members,I think it will work.
|||No, your last example won't do what you want, I think - it will return all the members on the same level as[Equipment].[All types].[mechanism].[machine001].[abc]. Does[Equipment].[All types].[mechanism].[machine001].[abc].CHILDREN do what you want? Otherwise, you might want to check out the DESCENDANTS function.
You might want to read some of the articles in the 'MDX Essentials' series here:
http://www.databasejournal.com/article.php/1459531/
Especially the ones on 'family' functions such as this one:
http://www.databasejournal.com/features/mssql/article.php/2168911
HTH,
Chris|||Hi, Chris, I have checked the descendants function and it is the one I want, it works fine expect for a little longer query time than I thought.
About Members and Ordinal function
When I use MDX sample application,
I write a code like this:
But it failed.
I write a code like this:
SELECT
{[Time].[All Time].[2005]} ON COLUMNS,
{Filter([Equipment].Members, (InStr(1, [Equipment].CurrentMember.Name, "xxxx") =1))} ON ROWS
FROM francis.
Here Equipment is a huge dimension table,actually I want to do it like
But it failed.
One more is I can't use ordinal function in it too, but I can do it in AS and test in pivottables.
Can someone tell me the reasons?
This is probably the same issue as I mentioned on the other thread - you probably need to use the .Children function instead of the .Members function. The .Children function returns all the members 'underneath' a member in a hierarchy, the .Members function returns all the members on a dimension or level. So:
[Equipment].[All types].[mechanism].Children
should work for you.
I'm not sure what you mean about your problem with the Ordinal function - can you provide more details?
Chris|||Hi, Chris, give you more details as example. I assume [Equipment].[All types].[mechanism].[machine001] have [abc],[bcd],[efg],[hij] these children like [Equipment].[All types].[mechanism].[machine001].[abc], and in them they all have a children call [ABSmain_xxxx] like [Equipment].[All types].[mechanism].[machine001].[abc].ABSmain_abc.
Now I want to average all the ABSmain_xxx value, so I will do a filter to the set I reach now.
If I use [Equipment].[All types].[mechanism].[machine001].Children, it will only filter the [abc],[bcd]...etc, it won't go deep to their children.
I suppose to do it like [Equipment].[All types].[mechanism].[machine001].[abc].level.members,I think it will work.
|||No, your last example won't do what you want, I think - it will return all the members on the same level as [Equipment].[All types].[mechanism].[machine001].[abc]. Does [Equipment].[All types].[mechanism].[machine001].[abc].CHILDREN do what you want? Otherwise, you might want to check out the DESCENDANTS function.
You might want to read some of the articles in the 'MDX Essentials' series here:
http://www.databasejournal.com/article.php/1459531/
Especially the ones on 'family' functions such as this one:
http://www.databasejournal.com/features/mssql/article.php/2168911
HTH,
Chris|||Hi, Chris, I have checked the descendants function and it is the one I want, it works fine expect for a little longer query time than I thought.
Monday, February 13, 2012
About Group BY Function in sql 2005
Hello
My Name Is Ramesh,
I Have Problem in Using the Group by function. My Requriment is ,i have table1 with name tTitlerates and table 2 with name tQuotation in tQuotation table i have field name called titleId , titlerate and Number of Copies. so i want add all the copies that falling into corresponding titleId and total amount for that total copies . but the Resulting table must contains TitleId, TotalNumberofcopies for that title, titleRate and Total rate Fields .
i have Used below query to get the Result , but by this query i can only display only 2 Fields,
SELECT qi.TitleID , SUM(qI.Copies)as NoofCopies FROM tQuotation q, tQuotationItem qi,tTitleRate tr
WHERE qi.QuotationNo = q.QuotationNo
AND qi.TitleID = tr.TitleId group by qi.TitleID
I need Query for my requriment
Please Repaly me as soon as possiable
Regards
Ramesh
Ramesh:
I started to work on this, but I am afraid that I am still not able to visualize it. Can you provide (1) a short sample input set to the query and (2) the expected output from this input?
|||Hello Kent WaldropI got the result for my last query
I have New query Now .
I have written One Stored procedure , just go through it once
ALTER PROCEDURE bestprice
AS
BEGIN
CREATE TABLE #tmp(QuotationNo INT,CombTitleID INT , TitleId INT, PublisherId INT
)
CREATE TABLE #tmp1(CombTitleID INT ,TitleId INT)
--Local Variables
DECLARE @.Title_id INT
DECLARE @.Publisher_id INT
DECLARE @.message varchar(80)
DECLARE @.Publisher_Name VARCHAR(20)
DECLARE @.Status INT
DECLARE @.Top INT
SET @.Top = 1
DECLARE @.Title_Id1 INT
DECLARE @.Loopcounter1 INT
SET @.Loopcounter1 = 0
DECLARE @.Loopcounter2 INT
SET @.Loopcounter2 = 0
DECLARE @.Counter INT
DECLARE @.QuotationNo INT
DECLARE @.CombTitleID INT
DECLARE @.CombTitleID1 INT
-- Declare Cursor for quotation
INSERT INTO #tmp SELECT q.QuotationNo,null, qi.TitleId, tm.PublisherId
FROM tQuotation q, tQuotationItem qi ,tTitleMaster tm
WHERE q.Status = 1
AND q.QuotationNo = qi.QuotationNo
AND qi.TitleId = tm.TitleId
WHILE(@.Loopcounter1 < (SELECT Count(*) From tTitleComb))
BEGIN
INSERT INTO #Tmp1 SELECT CombTitleID , TitleId FROM tTitleCombList
where CombTitleID = (SELECT CombTitleID FROM tTitleComb a where @.Top = (select Count (*) from tTitleComb b where a.CombTitleID >= b.CombTitleID ))
PRINT 'Top = ' + convert(varchar(20), @.Top)
SET @.Counter = 0
SET @.Loopcounter2 = 0
--statrt Of Inner while Loop
-- Declare Cursor for CombtitleId
DECLARE Inner_Cursor CURSOR FOR
SELECT TitleId ,CombTitleID From #Tmp1
OPEN Inner_Cursor
-- Fetch values from cursor into local variables
FETCH NEXT FROM Inner_Cursor
INTO @.Title_Id1 ,@.CombTitleID
-- Begin Loop
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Counter = 0
DECLARE best_Quotation CURSOR FOR
SELECT QuotationNo,CombTitleID,TitleId ,PublisherId FROM #tmp
OPEN best_Quotation
FETCH NEXT FROM best_Quotation
INTO @.QuotationNo,@.CombTitleID1 ,@.Title_Id, @.Publisher_id
begin transaction Dummy1
--Inner loop to loop through the Title Id in #tmp table for each comb Title Id in #tmp1
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Counter = 0
IF( @.Title_Id1 = @.Title_Id )
BEGIN
SET @.Counter = 1
SET @.Loopcounter2 = @.Loopcounter2 + 1
END
IF(@.Counter = 1)
BEGIN
UPDATE #tmp SET CombTitleID = @.CombTitleID WHERE QuotationNo = @.QuotationNo AND TitleId = @.Title_Id
print 'CombTitleID = ' + convert(varchar(20),@.CombTitleID) + 'QuotationNo = ' + convert(varchar(20),@.QuotationNo) + 'TitleId = ' + convert(varchar(20),@.Title_Id)
BREAK
END
else
BEGIN
FETCH NEXT FROM best_Quotation
INTO @.QuotationNo,@.CombTitleID1,@.Title_Id, @.Publisher_id
END
END
CLOSE best_Quotation
DEALLOCATE best_Quotation
--End OF Inner Loop
FETCH NEXT FROM Inner_Cursor
INTO @.Title_Id1 ,@.CombTitleID
IF( @.Loopcounter2 = (SELECT Count(*) FROM #Tmp1))
BEGIN
COMMIT
Print 'LoopCounter = ' + convert(varchar(20), @.Loopcounter2)
Print 'EXISTS IN COMBTITLE'
END
else
ROLLBACK
--End OF External Loop
END
CLOSE Inner_Cursor
DEALLOCATE Inner_Cursor
--End Of Inner While Loop
set @.Top = @.Top + 1
set @.Loopcounter1 = @.Loopcounter1 + 1
TRUNCATE TABLE #Tmp1
END
DROP TABLE #Tmp1
SELECT * FROM #tmp
END
What i am trying to do is i have table name called tQuotationItem contatining the fields called
QutationNo(pk,fk) ,titleId(pk,fk) (QuotationNo is composite primary key For tQuotationItem and it is primary key for tQuotationtable which is having the fields like QuotationNo (Primary key) and Description(varchar(20) .titleId is composite primary key for tQuotationItem and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
I also hve table called tTitlecombList
CombTitleId(pk,fk) ,titleId(pk,fk) (CombTitleId is composite primary key For tTitlecombList and it is primary key for tTitlecomb table which is having the fields like CombTitleId (Primary key) and Description(varchar(20) .titleId is composite primary key for tTitlecombList and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
lNow my actual query comes
lNow my actual query comes
tQuotation
QutationNo(pk) Desc(Varchar)
1 By ITI College
2 By Gti College
tQuotationItem
QutationNo(pk,fk) titleId(pk,fk)
1 1
1 2
1 7
2 2
2 8
2 9
Ttitlecomb
combtitleId(pk) Desc(varcahr)
1 ITi Books package
2 gti bookPackage
TtitlecombList
combtitleId(pk,Fk) TiTleId(pk,fk)
1 1
1 2
1 8
2 2
2 7
2 9
By using all These table I want My result table Like
QuotationNo CombTitleId TitleId
1 1 1
1 1 2
2 1 8
2 2 2
1 2 7
2 2 9
please replay me as soon as possiable
Regards
Ramesh
|||Maybe something like this:
Code Snippet
declare @.tQuotation table
( QuotationNo integer,
[Desc] varchar(15)
)
insert into @.tQuotation
select 1, 'By ITI College' union all
select 2, 'By Gti College'
--select * from @.tQuotation
declare @.tQuotationItem table
( QuotationNo integer,
TitleId integer
)
insert into @.tQuotationItem
select 1, 1 union all
select 1, 2 union all
select 1, 7 union all
select 2, 2 union all
select 2, 8 union all
select 2, 9 union all
select * from @.tQuotationItem
declare @.titleCombList table
( combTitleId integer,
TitleId integer
)
insert into @.titleCombList
select 1, 1 union all
select 1, 2 union all
select 1, 8 union all
select 2, 2 union all
select 2, 7 union all
select 2, 9
--select * from @.titleCombList
select min(b.quotationNo) as min_quotationNo,
a.CombTitleId,
a.TitleId
from @.titleCombList a
join @.tQuotationItem b
on a.titleId = b.titleID
group by a.combTitleId,
a.titleId
order by a.combTitleId, a.TitleId
/*
min_quotationNo CombTitleId TitleId
-- --
1 1 1
1 1 2
2 1 8
1 2 2
1 2 7
2 2 9
*/
Hello
Thank You for Your Valueable Information.Your Query is working Perfectly fine it is almost matching my requrement.
Their is one problem , if all the combination exists means then only it has to update my resulting table with Combtitle id.
What i am trying to do is i have table name called tQuotationItem contatining the fields called
QutationNo(pk,fk) ,titleId(pk,fk) (QuotationNo is composite primary key For tQuotationItem and it is primary key for tQuotationtable which is having the fields like QuotationNo (Primary key) and Description(varchar(20) .titleId is composite primary key for tQuotationItem and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
I also hve table called tTitlecombList
CombTitleId(pk,fk) ,titleId(pk,fk) (CombTitleId is composite primary key For tTitlecombList and it is primary key for tTitlecomb table which is having the fields like CombTitleId (Primary key) and Description(varchar(20) .titleId is composite primary key for tTitlecombList and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
lNow my actual query comes
lNow my actual query comes
tQuotation
QutationNo(pk) Desc(Varchar)
1 By ITI College
2 By Gti College
3 By gmit college
4 by rec College
tQuotationItem
QutationNo(pk,fk) titleId(pk,fk)
1 2
1 3
1 7
1 8
2 2
2 47
2 55
3 2
3 3
Ttitlecomb
combtitleId(pk) Desc(varcahr)
1 ITi Books package
3 gti bookPackage
4 Var1 package
6 Var2 package
7 Var3package
8 Var4 package
TtitlecombList
combtitleId(pk,Fk) TiTleId(pk,fk)
1 2
1 3
3 2
3 8
4 6
4 10
7 2
7 47
7 55
8 2
8 5
By using all These table I want My result table Like
QuotationNo CombTitleId TitleId
3 1 2
3 1 3
3 3 2
1 3 8
3 6 2
1 6 7
3 7 2
2 7 47
2 7 55
3 8 2
In This table th last row contains combid = 8 and titleid = 2 it doesnt has to come because
combid = 8 and titleid = 2
combid = 8 and titleid = 5
is a package. i cnt able to consider it as package since the 5 titleid is not exist in the quotation table.
my requriment is like this, if hole particular package of combination is exist in quotation then only it has to update in to resulting table .if any one comination of particular combid doent match means it doent insert that TItleid in to my resulting table
|||Hello
Thank You for Your Valueable Information.Your Query is working Perfectly fine it is almost matching my requrement.
Their is one problem , if all the combination exists means then only it has to update my resulting table with Combtitle id.
What i am trying to do is i have table name called tQuotationItem contatining the fields called
QutationNo(pk,fk) ,titleId(pk,fk) (QuotationNo is composite primary key For tQuotationItem and it is primary key for tQuotationtable which is having the fields like QuotationNo (Primary key) and Description(varchar(20) .titleId is composite primary key for tQuotationItem and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
I also hve table called tTitlecombList
CombTitleId(pk,fk) ,titleId(pk,fk) (CombTitleId is composite primary key For tTitlecombList and it is primary key for tTitlecomb table which is having the fields like CombTitleId (Primary key) and Description(varchar(20) .titleId is composite primary key for tTitlecombList and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
lNow my actual query comes
lNow my actual query comes
tQuotation
QutationNo(pk) Desc(Varchar)
1 By ITI College
2 By Gti College
3 By gmit college
4 by rec College
tQuotationItem
QutationNo(pk,fk) titleId(pk,fk)
1 2
1 3
1 7
1 8
2 2
2 47
2 55
3 2
3 3
Ttitlecomb
combtitleId(pk) Desc(varcahr)
1 ITi Books package
3 gti bookPackage
4 Var1 package
6 Var2 package
7 Var3package
8 Var4 package
TtitlecombList
combtitleId(pk,Fk) TiTleId(pk,fk)
1 2
1 3
3 2
3 8
4 6
4 10
7 2
7 47
7 55
8 2
8 5
Your query is resulting below table
QuotationNo CombTitleId TitleId
3 1 2
3 1 3
3 3 2
1 3 8
3 6 2
1 6 7
3 7 2
2 7 47
2 7 55
3 8 2
But In This table th last row contains combid = 8 and titleid = 2 it doesnt has to come because
combid = 8 and titleid = 2
combid = 8 and titleid = 5
is a package. i cnt able to consider it as package since the 5 titleid is not exist in the quotation table.
my requriment is like this, if hole particular package of combination is exist in quotation then only it has to update in to resulting table .if any one comination of particular combid doent match means it doent insert that TItleid in to my resulting table
i want my resulting table below table
QuotationNo CombTitleId TitleId
3 1 2
3 1 3
3 3 2
1 3 8
3 6 2
1 6 7
3 7 2
2 7 47
2 7 55
3 Null 2
Because the if one comination in the quotation table is not exists means we cant update that particular combid in to resulting tableAbout Group BY Function in sql 2005
Hello
My Name Is Ramesh,
I Have Problem in Using the Group by function. My Requriment is ,i have table1 with name tTitlerates and table 2 with name tQuotation in tQuotation table i have field name called titleId , titlerate and Number of Copies. so i want add all the copies that falling into corresponding titleId and total amount for that total copies . but the Resulting table must contains TitleId, TotalNumberofcopies for that title, titleRate and Total rate Fields .
i have Used below query to get the Result , but by this query i can only display only 2 Fields,
SELECT qi.TitleID , SUM(qI.Copies)as NoofCopies FROM tQuotation q, tQuotationItem qi,tTitleRate tr
WHERE qi.QuotationNo = q.QuotationNo
AND qi.TitleID = tr.TitleId group by qi.TitleID
I need Query for my requriment
Please Repaly me as soon as possiable
Regards
Ramesh
Ramesh:
I started to work on this, but I am afraid that I am still not able to visualize it. Can you provide (1) a short sample input set to the query and (2) the expected output from this input?
|||Hello Kent WaldropI got the result for my last query
I have New query Now .
I have written One Stored procedure , just go through it once
ALTER PROCEDURE bestprice
AS
BEGIN
CREATE TABLE #tmp(QuotationNo INT,CombTitleID INT , TitleId INT, PublisherId INT
)
CREATE TABLE #tmp1(CombTitleID INT ,TitleId INT)
--Local Variables
DECLARE @.Title_id INT
DECLARE @.Publisher_id INT
DECLARE @.message varchar(80)
DECLARE @.Publisher_Name VARCHAR(20)
DECLARE @.Status INT
DECLARE @.Top INT
SET @.Top = 1
DECLARE @.Title_Id1 INT
DECLARE @.Loopcounter1 INT
SET @.Loopcounter1 = 0
DECLARE @.Loopcounter2 INT
SET @.Loopcounter2 = 0
DECLARE @.Counter INT
DECLARE @.QuotationNo INT
DECLARE @.CombTitleID INT
DECLARE @.CombTitleID1 INT
-- Declare Cursor for quotation
INSERT INTO #tmp SELECT q.QuotationNo,null, qi.TitleId, tm.PublisherId
FROM tQuotation q, tQuotationItem qi ,tTitleMaster tm
WHERE q.Status = 1
AND q.QuotationNo = qi.QuotationNo
AND qi.TitleId = tm.TitleId
WHILE(@.Loopcounter1 < (SELECT Count(*) From tTitleComb))
BEGIN
INSERT INTO #Tmp1 SELECT CombTitleID , TitleId FROM tTitleCombList
where CombTitleID = (SELECT CombTitleID FROM tTitleComb a where @.Top = (select Count (*) from tTitleComb b where a.CombTitleID >= b.CombTitleID ))
PRINT 'Top = ' + convert(varchar(20), @.Top)
SET @.Counter = 0
SET @.Loopcounter2 = 0
--statrt Of Inner while Loop
-- Declare Cursor for CombtitleId
DECLARE Inner_Cursor CURSOR FOR
SELECT TitleId ,CombTitleID From #Tmp1
OPEN Inner_Cursor
-- Fetch values from cursor into local variables
FETCH NEXT FROM Inner_Cursor
INTO @.Title_Id1 ,@.CombTitleID
-- Begin Loop
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Counter = 0
DECLARE best_Quotation CURSOR FOR
SELECT QuotationNo,CombTitleID,TitleId ,PublisherId FROM #tmp
OPEN best_Quotation
FETCH NEXT FROM best_Quotation
INTO @.QuotationNo,@.CombTitleID1 ,@.Title_Id, @.Publisher_id
begin transaction Dummy1
--Inner loop to loop through the Title Id in #tmp table for each comb Title Id in #tmp1
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Counter = 0
IF( @.Title_Id1 = @.Title_Id )
BEGIN
SET @.Counter = 1
SET @.Loopcounter2 = @.Loopcounter2 + 1
END
IF(@.Counter = 1)
BEGIN
UPDATE #tmp SET CombTitleID = @.CombTitleID WHERE QuotationNo = @.QuotationNo AND TitleId = @.Title_Id
print 'CombTitleID = ' + convert(varchar(20),@.CombTitleID) + 'QuotationNo = ' + convert(varchar(20),@.QuotationNo) + 'TitleId = ' + convert(varchar(20),@.Title_Id)
BREAK
END
else
BEGIN
FETCH NEXT FROM best_Quotation
INTO @.QuotationNo,@.CombTitleID1,@.Title_Id, @.Publisher_id
END
END
CLOSE best_Quotation
DEALLOCATE best_Quotation
--End OF Inner Loop
FETCH NEXT FROM Inner_Cursor
INTO @.Title_Id1 ,@.CombTitleID
IF( @.Loopcounter2 = (SELECT Count(*) FROM #Tmp1))
BEGIN
COMMIT
Print 'LoopCounter = ' + convert(varchar(20), @.Loopcounter2)
Print 'EXISTS IN COMBTITLE'
END
else
ROLLBACK
--End OF External Loop
END
CLOSE Inner_Cursor
DEALLOCATE Inner_Cursor
--End Of Inner While Loop
set @.Top = @.Top + 1
set @.Loopcounter1 = @.Loopcounter1 + 1
TRUNCATE TABLE #Tmp1
END
DROP TABLE #Tmp1
SELECT * FROM #tmp
END
What i am trying to do is i have table name called tQuotationItem contatining the fields called
QutationNo(pk,fk) ,titleId(pk,fk) (QuotationNo is composite primary key For tQuotationItem and it is primary key for tQuotationtable which is having the fields like QuotationNo (Primary key) and Description(varchar(20) .titleId is composite primary key for tQuotationItem and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
I also hve table called tTitlecombList
CombTitleId(pk,fk) ,titleId(pk,fk) (CombTitleId is composite primary key For tTitlecombList and it is primary key for tTitlecomb table which is having the fields like CombTitleId (Primary key) and Description(varchar(20) .titleId is composite primary key for tTitlecombList and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
lNow my actual query comes
lNow my actual query comes
tQuotation
QutationNo(pk) Desc(Varchar)
1 By ITI College
2 By Gti College
tQuotationItem
QutationNo(pk,fk) titleId(pk,fk)
1 1
1 2
1 7
2 2
2 8
2 9
Ttitlecomb
combtitleId(pk) Desc(varcahr)
1 ITi Books package
2 gti bookPackage
TtitlecombList
combtitleId(pk,Fk) TiTleId(pk,fk)
1 1
1 2
1 8
2 2
2 7
2 9
By using all These table I want My result table Like
QuotationNo CombTitleId TitleId
1 1 1
1 1 2
2 1 8
2 2 2
1 2 7
2 2 9
please replay me as soon as possiable
Regards
Ramesh
|||Maybe something like this:
Code Snippet
declare @.tQuotation table
( QuotationNo integer,
[Desc] varchar(15)
)
insert into @.tQuotation
select 1, 'By ITI College' union all
select 2, 'By Gti College'
--select * from @.tQuotation
declare @.tQuotationItem table
( QuotationNo integer,
TitleId integer
)
insert into @.tQuotationItem
select 1, 1 union all
select 1, 2 union all
select 1, 7 union all
select 2, 2 union all
select 2, 8 union all
select 2, 9 union all
select * from @.tQuotationItem
declare @.titleCombList table
( combTitleId integer,
TitleId integer
)
insert into @.titleCombList
select 1, 1 union all
select 1, 2 union all
select 1, 8 union all
select 2, 2 union all
select 2, 7 union all
select 2, 9
--select * from @.titleCombList
select min(b.quotationNo) as min_quotationNo,
a.CombTitleId,
a.TitleId
from @.titleCombList a
join @.tQuotationItem b
on a.titleId = b.titleID
group by a.combTitleId,
a.titleId
order by a.combTitleId, a.TitleId
/*
min_quotationNo CombTitleId TitleId
-- --
1 1 1
1 1 2
2 1 8
1 2 2
1 2 7
2 2 9
*/
Hello
Thank You for Your Valueable Information.Your Query is working Perfectly fine it is almost matching my requrement.
Their is one problem , if all the combination exists means then only it has to update my resulting table with Combtitle id.
What i am trying to do is i have table name called tQuotationItem contatining the fields called
QutationNo(pk,fk) ,titleId(pk,fk) (QuotationNo is composite primary key For tQuotationItem and it is primary key for tQuotationtable which is having the fields like QuotationNo (Primary key) and Description(varchar(20) .titleId is composite primary key for tQuotationItem and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
I also hve table called tTitlecombList
CombTitleId(pk,fk) ,titleId(pk,fk) (CombTitleId is composite primary key For tTitlecombList and it is primary key for tTitlecomb table which is having the fields like CombTitleId (Primary key) and Description(varchar(20) .titleId is composite primary key for tTitlecombList and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
lNow my actual query comes
lNow my actual query comes
tQuotation
QutationNo(pk) Desc(Varchar)
1 By ITI College
2 By Gti College
3 By gmit college
4 by rec College
tQuotationItem
QutationNo(pk,fk) titleId(pk,fk)
1 2
1 3
1 7
1 8
2 2
2 47
2 55
3 2
3 3
Ttitlecomb
combtitleId(pk) Desc(varcahr)
1 ITi Books package
3 gti bookPackage
4 Var1 package
6 Var2 package
7 Var3package
8 Var4 package
TtitlecombList
combtitleId(pk,Fk) TiTleId(pk,fk)
1 2
1 3
3 2
3 8
4 6
4 10
7 2
7 47
7 55
8 2
8 5
By using all These table I want My result table Like
QuotationNo CombTitleId TitleId
3 1 2
3 1 3
3 3 2
1 3 8
3 6 2
1 6 7
3 7 2
2 7 47
2 7 55
3 8 2
In This table th last row contains combid = 8 and titleid = 2 it doesnt has to come because
combid = 8 and titleid = 2
combid = 8 and titleid = 5
is a package. i cnt able to consider it as package since the 5 titleid is not exist in the quotation table.
my requriment is like this, if hole particular package of combination is exist in quotation then only it has to update in to resulting table .if any one comination of particular combid doent match means it doent insert that TItleid in to my resulting table
|||Hello
Thank You for Your Valueable Information.Your Query is working Perfectly fine it is almost matching my requrement.
Their is one problem , if all the combination exists means then only it has to update my resulting table with Combtitle id.
What i am trying to do is i have table name called tQuotationItem contatining the fields called
QutationNo(pk,fk) ,titleId(pk,fk) (QuotationNo is composite primary key For tQuotationItem and it is primary key for tQuotationtable which is having the fields like QuotationNo (Primary key) and Description(varchar(20) .titleId is composite primary key for tQuotationItem and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
I also hve table called tTitlecombList
CombTitleId(pk,fk) ,titleId(pk,fk) (CombTitleId is composite primary key For tTitlecombList and it is primary key for tTitlecomb table which is having the fields like CombTitleId (Primary key) and Description(varchar(20) .titleId is composite primary key for tTitlecombList and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
lNow my actual query comes
lNow my actual query comes
tQuotation
QutationNo(pk) Desc(Varchar)
1 By ITI College
2 By Gti College
3 By gmit college
4 by rec College
tQuotationItem
QutationNo(pk,fk) titleId(pk,fk)
1 2
1 3
1 7
1 8
2 2
2 47
2 55
3 2
3 3
Ttitlecomb
combtitleId(pk) Desc(varcahr)
1 ITi Books package
3 gti bookPackage
4 Var1 package
6 Var2 package
7 Var3package
8 Var4 package
TtitlecombList
combtitleId(pk,Fk) TiTleId(pk,fk)
1 2
1 3
3 2
3 8
4 6
4 10
7 2
7 47
7 55
8 2
8 5
Your query is resulting below table
QuotationNo CombTitleId TitleId
3 1 2
3 1 3
3 3 2
1 3 8
3 6 2
1 6 7
3 7 2
2 7 47
2 7 55
3 8 2
But In This table th last row contains combid = 8 and titleid = 2 it doesnt has to come because
combid = 8 and titleid = 2
combid = 8 and titleid = 5
is a package. i cnt able to consider it as package since the 5 titleid is not exist in the quotation table.
my requriment is like this, if hole particular package of combination is exist in quotation then only it has to update in to resulting table .if any one comination of particular combid doent match means it doent insert that TItleid in to my resulting table
i want my resulting table below table
QuotationNo CombTitleId TitleId
3 1 2
3 1 3
3 3 2
1 3 8
3 6 2
1 6 7
3 7 2
2 7 47
2 7 55
3 Null 2
Because the if one comination in the quotation table is not exists means we cant update that particular combid in to resulting table
Subscribe to:
Comments (Atom)