Hi i have query which i need to join it
like i am doing some thing like
Select [a].[dbo].[tbla].* From [a].[dbo].[tbla] INNER INNER JOIN
[B].[dbo].[tblB ON [a].[dbo].[tbla].[ID] =
[b].[dbo].[tbla].[ID]
and i am running that query in database A but the only problem is it saying
that b.dbo.tblb.ID is invalid colum name i dont understand that i am giving
full address of both databases but cannt do it ..... any help thanksYour query seem to be syntactically wrong.
Anyways, correct it and try to add table alias for the tables and use them
to refer to the columns.
Or post the actual query that gave the error you mentioned.
--
"amjad" wrote:
> Hi i have query which i need to join it
> like i am doing some thing like
> Select [a].[dbo].[tbla].* From [a].[dbo].[tbla] INNER INNER JOIN
> [B].[dbo].[tblB ON [a].[dbo].[tbla].[ID] =
> [b].[dbo].[tbla].[ID]
> and i am running that query in database A but the only problem is it sayin
g
> that b.dbo.tblb.ID is invalid colum name i dont understand that i am givin
g
> full address of both databases but cannt do it ..... any help thanks|||could you post what your actually doing, rather than something like
what you're doing?
I mean what's an INNER INNER JOIN? is it something that's really
seriously inner on the join? and is it a typo or have you missed out a
"]" in your actual code?|||thanks for help i solved thanks
"Will" wrote:
> could you post what your actually doing, rather than something like
> what you're doing?
> I mean what's an INNER INNER JOIN? is it something that's really
> seriously inner on the join? and is it a typo or have you missed out a
> "]" in your actual code?
>
Showing posts with label inner. Show all posts
Showing posts with label inner. Show all posts
Friday, March 23, 2012
Monday, March 19, 2012
How to rewrite this query using LEFT OUTER JOIN?
Hello Everyone!
I'm having problems with the INNER JOIN in the subquery. If it wasn't
for that, I'd be able to LEFT JOIN it and then check for nulls on the
right side, but I'm not sure where I'd stick the INNER JOIN.
SELECT COUNT(*)
FROM FND_DONATION_MASTER F
WHERE F.SHIP_CUSTOMER NOT IN
(SELECT
F.SHIP_CUSTOMER
FROM FND_DONATION_MASTER F INNER JOIN RECEIVABLE_TXN R
ON R.ORDER_NO = F.ORDER_NO
WHERE R.TXN_TYPE < 3
)
Thank you!
*** Sent via Developersdex http://www.examnotes.net ***SELECT COUNT(*)
FROM FND_DONATION_MASTER F
LEFT OUTER JOIN
(SELECT
F.SHIP_CUSTOMER
FROM FND_DONATION_MASTER F INNER JOIN RECEIVABLE_TXN R
ON R.ORDER_NO = F.ORDER_NO
WHERE R.TXN_TYPE < 3
) D
ON F.SHIP_CUSTOMER = D.SHIP_CUSTOMER
WHERE D.SHIP_CUSTOMER IS NULL
HTH,
John Scragg
"mEmENT0m0RI" wrote:
> Hello Everyone!
> I'm having problems with the INNER JOIN in the subquery. If it wasn't
> for that, I'd be able to LEFT JOIN it and then check for nulls on the
> right side, but I'm not sure where I'd stick the INNER JOIN.
>
> SELECT COUNT(*)
> FROM FND_DONATION_MASTER F
> WHERE F.SHIP_CUSTOMER NOT IN
> (SELECT
> F.SHIP_CUSTOMER
> FROM FND_DONATION_MASTER F INNER JOIN RECEIVABLE_TXN R
> ON R.ORDER_NO = F.ORDER_NO
> WHERE R.TXN_TYPE < 3
> )
> Thank you!
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Hi,
the below query can be re-written with LEFT JOIN as below.
SELECT COUNT(*)
FROM FND_DONATION_MASTER F
LEFT JOIN
(SELECT
F.SHIP_CUSTOMER
FROM FND_DONATION_MASTER F INNER JOIN RECEIVABLE_TXN R
ON R.ORDER_NO = F.ORDER_NO
WHERE R.TXN_TYPE < 3
) Temp
ON F.SHIP_CUSTOMER = Temp.SHIP_CUSTOMER
WHERE F.SHIP_CUSTOMER IS NULL
Hope this helps.
Thanks,
Siva
"mEmENT0m0RI" wrote:
> Hello Everyone!
> I'm having problems with the INNER JOIN in the subquery. If it wasn't
> for that, I'd be able to LEFT JOIN it and then check for nulls on the
> right side, but I'm not sure where I'd stick the INNER JOIN.
>
> SELECT COUNT(*)
> FROM FND_DONATION_MASTER F
> WHERE F.SHIP_CUSTOMER NOT IN
> (SELECT
> F.SHIP_CUSTOMER
> FROM FND_DONATION_MASTER F INNER JOIN RECEIVABLE_TXN R
> ON R.ORDER_NO = F.ORDER_NO
> WHERE R.TXN_TYPE < 3
> )
> Thank you!
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Hi
It is better to post DDL and example data along with your expected output
then your intensions will be cleared! See
http://www.aspfaq.com/etiquette.asp?id=5006 on how to do this.
SELECT COUNT(*)
FROM FND_DONATION_MASTER F
LEFT JOIN ( FND_DONATION_MASTER M
JOIN RECEIVABLE_TXN R ON R.ORDER_NO = F.ORDER_NO AND
R.TXN_TYPE < 3
) ON F.SHIP_CUSTOMER = M.SHIP_CUSTOMER
or possibly
SELECT COUNT(*)
FROM FND_DONATION_MASTER F
LEFT JOIN RECEIVABLE_TXN R ON R.ORDER_NO = F.ORDER_NO AND R.TXN_TYPE < 3
WHERE R.ORDER_NO IS NULL
or you maybe:
SELECT COUNT(*)
FROM FND_DONATION_MASTER F
WHERE NOT EXISTS
(SELECT *
FROM RECEIVABLE_TXN R
WHERE R.ORDER_NO = F.ORDER_NO AND R.TXN_TYPE < 3
)
John
"mEmENT0m0RI" wrote:
> Hello Everyone!
> I'm having problems with the INNER JOIN in the subquery. If it wasn't
> for that, I'd be able to LEFT JOIN it and then check for nulls on the
> right side, but I'm not sure where I'd stick the INNER JOIN.
>
> SELECT COUNT(*)
> FROM FND_DONATION_MASTER F
> WHERE F.SHIP_CUSTOMER NOT IN
> (SELECT
> F.SHIP_CUSTOMER
> FROM FND_DONATION_MASTER F INNER JOIN RECEIVABLE_TXN R
> ON R.ORDER_NO = F.ORDER_NO
> WHERE R.TXN_TYPE < 3
> )
> Thank you!
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||John,
Thank you for your reply! I tried your solution. It produces the right
result, however it runs even a little slower that the NOT IN variant.
I wonder, why this isn't working:
SELECT COUNT(*)
FROM FND_DONATION_MASTER F1
LEFT OUTER JOIN FND_DONATION_MASTER F2
ON F1.ship_customer = F2.ship_customer
INNER JOIN RECEIVABLE_TXN R2
ON R2.ORDER_NO = F2.ORDER_NO
AND R2.TXN_TYPE < 3
WHERE F2.ship_customer IS NULL
Is there a way in SQL to change the precedence of how multiple joins
apply?
Thank you,
Igor
*** Sent via Developersdex http://www.examnotes.net ***|||On Wed, 09 Nov 2005 08:57:56 -0800, mEmENT0m0RI wrote:
>John,
>Thank you for your reply! I tried your solution. It produces the right
>result, however it runs even a little slower that the NOT IN variant.
>I wonder, why this isn't working:
>SELECT COUNT(*)
>FROM FND_DONATION_MASTER F1
>LEFT OUTER JOIN FND_DONATION_MASTER F2
> ON F1.ship_customer = F2.ship_customer
>INNER JOIN RECEIVABLE_TXN R2
> ON R2.ORDER_NO = F2.ORDER_NO
> AND R2.TXN_TYPE < 3
>WHERE F2.ship_customer IS NULL
Hi Igor,
The LEFT OUTER JOIN is processed first. If any rows in F1 are not
matched against F2, they are retained in F1, with NULL values for the F2
columns. But then, the INNER JOIN is processed, that joins on
F2.ship_customer. For the retained rows, this is NULL, and no row in F3
will match. As a result, the INNER JOIN fails and the row is removed.
In the end, the INNER JOIN that uses columns from the outer table
transforms your outer join to a normal inner join.
>Is there a way in SQL to change the precedence of how multiple joins
>apply?
Two ways, even. Use parentheses, or re-order the joins
#1: Use parentheses.
SELECT COUNT(*)
FROM FND_DONATION_MASTER F1
LEFT OUTER JOIN ( FND_DONATION_MASTER F2
INNER JOIN RECEIVABLE_TXN R2
ON R2.ORDER_NO = F2.ORDER_NO
AND R2.TXN_TYPE < 3 )
ON F1.ship_customer = F2.ship_customer
WHERE F2.ship_customer IS NULL
Note that the parentheses force me to move the ON clause for the OUTER
JOIN to the end.
Also note that the parentheses might even be removed in this query - the
location of the ON clauses (two JOIN clauses followed by two ON clauses)
is sufficient for SQL Server to figure out how this is meant. Humans are
less able to understand such a mess, though, so my advice is to leave
the parentheses in.
Even with the parentheses, the version above is hardlly readably. A
better alternative is:
#2: Reorder the joins.
SELECT COUNT(*)
FROM FND_DONATION_MASTER F2
INNER JOIN RECEIVABLE_TXN R2
ON R2.ORDER_NO = F2.ORDER_NO
AND R2.TXN_TYPE < 3
RIGHT OUTER JOIN FND_DONATION_MASTER F1
ON F1.ship_customer = F2.ship_customer
WHERE F2.ship_customer IS NULL
Note that the reversal of the F1 and F2 tables forces me to rename the
LEFT OUTER JOIN to a RIGHT OUTER JOIN (since the two tables have traded
placecs).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Guys,
I apologize for not replying to the thread for so long. I've been out of
the office for a couple of days.
I appreciate all your answers.
Hugo, special thanks to you; your answer is exactly what I was looking
for and something I couldn't figure out for a long time.
Thank you!
*** Sent via Developersdex http://www.examnotes.net ***
I'm having problems with the INNER JOIN in the subquery. If it wasn't
for that, I'd be able to LEFT JOIN it and then check for nulls on the
right side, but I'm not sure where I'd stick the INNER JOIN.
SELECT COUNT(*)
FROM FND_DONATION_MASTER F
WHERE F.SHIP_CUSTOMER NOT IN
(SELECT
F.SHIP_CUSTOMER
FROM FND_DONATION_MASTER F INNER JOIN RECEIVABLE_TXN R
ON R.ORDER_NO = F.ORDER_NO
WHERE R.TXN_TYPE < 3
)
Thank you!
*** Sent via Developersdex http://www.examnotes.net ***SELECT COUNT(*)
FROM FND_DONATION_MASTER F
LEFT OUTER JOIN
(SELECT
F.SHIP_CUSTOMER
FROM FND_DONATION_MASTER F INNER JOIN RECEIVABLE_TXN R
ON R.ORDER_NO = F.ORDER_NO
WHERE R.TXN_TYPE < 3
) D
ON F.SHIP_CUSTOMER = D.SHIP_CUSTOMER
WHERE D.SHIP_CUSTOMER IS NULL
HTH,
John Scragg
"mEmENT0m0RI" wrote:
> Hello Everyone!
> I'm having problems with the INNER JOIN in the subquery. If it wasn't
> for that, I'd be able to LEFT JOIN it and then check for nulls on the
> right side, but I'm not sure where I'd stick the INNER JOIN.
>
> SELECT COUNT(*)
> FROM FND_DONATION_MASTER F
> WHERE F.SHIP_CUSTOMER NOT IN
> (SELECT
> F.SHIP_CUSTOMER
> FROM FND_DONATION_MASTER F INNER JOIN RECEIVABLE_TXN R
> ON R.ORDER_NO = F.ORDER_NO
> WHERE R.TXN_TYPE < 3
> )
> Thank you!
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Hi,
the below query can be re-written with LEFT JOIN as below.
SELECT COUNT(*)
FROM FND_DONATION_MASTER F
LEFT JOIN
(SELECT
F.SHIP_CUSTOMER
FROM FND_DONATION_MASTER F INNER JOIN RECEIVABLE_TXN R
ON R.ORDER_NO = F.ORDER_NO
WHERE R.TXN_TYPE < 3
) Temp
ON F.SHIP_CUSTOMER = Temp.SHIP_CUSTOMER
WHERE F.SHIP_CUSTOMER IS NULL
Hope this helps.
Thanks,
Siva
"mEmENT0m0RI" wrote:
> Hello Everyone!
> I'm having problems with the INNER JOIN in the subquery. If it wasn't
> for that, I'd be able to LEFT JOIN it and then check for nulls on the
> right side, but I'm not sure where I'd stick the INNER JOIN.
>
> SELECT COUNT(*)
> FROM FND_DONATION_MASTER F
> WHERE F.SHIP_CUSTOMER NOT IN
> (SELECT
> F.SHIP_CUSTOMER
> FROM FND_DONATION_MASTER F INNER JOIN RECEIVABLE_TXN R
> ON R.ORDER_NO = F.ORDER_NO
> WHERE R.TXN_TYPE < 3
> )
> Thank you!
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Hi
It is better to post DDL and example data along with your expected output
then your intensions will be cleared! See
http://www.aspfaq.com/etiquette.asp?id=5006 on how to do this.
SELECT COUNT(*)
FROM FND_DONATION_MASTER F
LEFT JOIN ( FND_DONATION_MASTER M
JOIN RECEIVABLE_TXN R ON R.ORDER_NO = F.ORDER_NO AND
R.TXN_TYPE < 3
) ON F.SHIP_CUSTOMER = M.SHIP_CUSTOMER
or possibly
SELECT COUNT(*)
FROM FND_DONATION_MASTER F
LEFT JOIN RECEIVABLE_TXN R ON R.ORDER_NO = F.ORDER_NO AND R.TXN_TYPE < 3
WHERE R.ORDER_NO IS NULL
or you maybe:
SELECT COUNT(*)
FROM FND_DONATION_MASTER F
WHERE NOT EXISTS
(SELECT *
FROM RECEIVABLE_TXN R
WHERE R.ORDER_NO = F.ORDER_NO AND R.TXN_TYPE < 3
)
John
"mEmENT0m0RI" wrote:
> Hello Everyone!
> I'm having problems with the INNER JOIN in the subquery. If it wasn't
> for that, I'd be able to LEFT JOIN it and then check for nulls on the
> right side, but I'm not sure where I'd stick the INNER JOIN.
>
> SELECT COUNT(*)
> FROM FND_DONATION_MASTER F
> WHERE F.SHIP_CUSTOMER NOT IN
> (SELECT
> F.SHIP_CUSTOMER
> FROM FND_DONATION_MASTER F INNER JOIN RECEIVABLE_TXN R
> ON R.ORDER_NO = F.ORDER_NO
> WHERE R.TXN_TYPE < 3
> )
> Thank you!
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||John,
Thank you for your reply! I tried your solution. It produces the right
result, however it runs even a little slower that the NOT IN variant.
I wonder, why this isn't working:
SELECT COUNT(*)
FROM FND_DONATION_MASTER F1
LEFT OUTER JOIN FND_DONATION_MASTER F2
ON F1.ship_customer = F2.ship_customer
INNER JOIN RECEIVABLE_TXN R2
ON R2.ORDER_NO = F2.ORDER_NO
AND R2.TXN_TYPE < 3
WHERE F2.ship_customer IS NULL
Is there a way in SQL to change the precedence of how multiple joins
apply?
Thank you,
Igor
*** Sent via Developersdex http://www.examnotes.net ***|||On Wed, 09 Nov 2005 08:57:56 -0800, mEmENT0m0RI wrote:
>John,
>Thank you for your reply! I tried your solution. It produces the right
>result, however it runs even a little slower that the NOT IN variant.
>I wonder, why this isn't working:
>SELECT COUNT(*)
>FROM FND_DONATION_MASTER F1
>LEFT OUTER JOIN FND_DONATION_MASTER F2
> ON F1.ship_customer = F2.ship_customer
>INNER JOIN RECEIVABLE_TXN R2
> ON R2.ORDER_NO = F2.ORDER_NO
> AND R2.TXN_TYPE < 3
>WHERE F2.ship_customer IS NULL
Hi Igor,
The LEFT OUTER JOIN is processed first. If any rows in F1 are not
matched against F2, they are retained in F1, with NULL values for the F2
columns. But then, the INNER JOIN is processed, that joins on
F2.ship_customer. For the retained rows, this is NULL, and no row in F3
will match. As a result, the INNER JOIN fails and the row is removed.
In the end, the INNER JOIN that uses columns from the outer table
transforms your outer join to a normal inner join.
>Is there a way in SQL to change the precedence of how multiple joins
>apply?
Two ways, even. Use parentheses, or re-order the joins
#1: Use parentheses.
SELECT COUNT(*)
FROM FND_DONATION_MASTER F1
LEFT OUTER JOIN ( FND_DONATION_MASTER F2
INNER JOIN RECEIVABLE_TXN R2
ON R2.ORDER_NO = F2.ORDER_NO
AND R2.TXN_TYPE < 3 )
ON F1.ship_customer = F2.ship_customer
WHERE F2.ship_customer IS NULL
Note that the parentheses force me to move the ON clause for the OUTER
JOIN to the end.
Also note that the parentheses might even be removed in this query - the
location of the ON clauses (two JOIN clauses followed by two ON clauses)
is sufficient for SQL Server to figure out how this is meant. Humans are
less able to understand such a mess, though, so my advice is to leave
the parentheses in.
Even with the parentheses, the version above is hardlly readably. A
better alternative is:
#2: Reorder the joins.
SELECT COUNT(*)
FROM FND_DONATION_MASTER F2
INNER JOIN RECEIVABLE_TXN R2
ON R2.ORDER_NO = F2.ORDER_NO
AND R2.TXN_TYPE < 3
RIGHT OUTER JOIN FND_DONATION_MASTER F1
ON F1.ship_customer = F2.ship_customer
WHERE F2.ship_customer IS NULL
Note that the reversal of the F1 and F2 tables forces me to rename the
LEFT OUTER JOIN to a RIGHT OUTER JOIN (since the two tables have traded
placecs).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Guys,
I apologize for not replying to the thread for so long. I've been out of
the office for a couple of days.
I appreciate all your answers.
Hugo, special thanks to you; your answer is exactly what I was looking
for and something I couldn't figure out for a long time.
Thank you!
*** Sent via Developersdex http://www.examnotes.net ***
How to rewrite query
I have this as part of SP
DECLARE @.StockId bigint
SELECT @.StockId=Reservation.StockId
FROM Reservation INNER JOIN
Stock ON Reservation.StockId = Stock.StockId
WHERE (Reservation.OrderItemId = @.OrderItemId) AND (Stock.HolderId =
@.WarehouseId)
UPDATE Reservation
SET Amount=Amount-@.Amount
WHERE (Reservation.OrderItemId = @.OrderItemId) AND(StockId=@.StockId)
I know there it could be done in one statement. How?
Thanks.Shimon Sim wrote:
> I have this as part of SP
> DECLARE @.StockId bigint
> SELECT @.StockId=Reservation.StockId
> FROM Reservation INNER JOIN
> Stock ON Reservation.StockId = Stock.StockId
> WHERE (Reservation.OrderItemId = @.OrderItemId) AND (Stock.HolderId =
> @.WarehouseId)
> UPDATE Reservation
> SET Amount=Amount-@.Amount
> WHERE (Reservation.OrderItemId = @.OrderItemId) AND(StockId=@.StockId)
> I know there it could be done in one statement. How?
> Thanks.
>
This should get you close:
UPDATE Reservation
SET Amount = Amount - @.Amount
FROM Reservation Reservation
INNER JOIN Stock Stock
ON Reservation.StockID = Stock.StockID
WHERE Reservation.OrderItemID = @.OrderItemID
AND Stock.HolderID = @.WarehouseID
I'm assuming @.WarehouseID and @.OrderItemID are parameters passed into
the stored procedure?|||Yes. They are parameters.
Thank you.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:OpFYPvAlGHA.3512@.TK2MSFTNGP03.phx.gbl...
> Shimon Sim wrote:
> This should get you close:
> UPDATE Reservation
> SET Amount = Amount - @.Amount
> FROM Reservation Reservation
> INNER JOIN Stock Stock
> ON Reservation.StockID = Stock.StockID
> WHERE Reservation.OrderItemID = @.OrderItemID
> AND Stock.HolderID = @.WarehouseID
> I'm assuming @.WarehouseID and @.OrderItemID are parameters passed into the
> stored procedure?
DECLARE @.StockId bigint
SELECT @.StockId=Reservation.StockId
FROM Reservation INNER JOIN
Stock ON Reservation.StockId = Stock.StockId
WHERE (Reservation.OrderItemId = @.OrderItemId) AND (Stock.HolderId =
@.WarehouseId)
UPDATE Reservation
SET Amount=Amount-@.Amount
WHERE (Reservation.OrderItemId = @.OrderItemId) AND(StockId=@.StockId)
I know there it could be done in one statement. How?
Thanks.Shimon Sim wrote:
> I have this as part of SP
> DECLARE @.StockId bigint
> SELECT @.StockId=Reservation.StockId
> FROM Reservation INNER JOIN
> Stock ON Reservation.StockId = Stock.StockId
> WHERE (Reservation.OrderItemId = @.OrderItemId) AND (Stock.HolderId =
> @.WarehouseId)
> UPDATE Reservation
> SET Amount=Amount-@.Amount
> WHERE (Reservation.OrderItemId = @.OrderItemId) AND(StockId=@.StockId)
> I know there it could be done in one statement. How?
> Thanks.
>
This should get you close:
UPDATE Reservation
SET Amount = Amount - @.Amount
FROM Reservation Reservation
INNER JOIN Stock Stock
ON Reservation.StockID = Stock.StockID
WHERE Reservation.OrderItemID = @.OrderItemID
AND Stock.HolderID = @.WarehouseID
I'm assuming @.WarehouseID and @.OrderItemID are parameters passed into
the stored procedure?|||Yes. They are parameters.
Thank you.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:OpFYPvAlGHA.3512@.TK2MSFTNGP03.phx.gbl...
> Shimon Sim wrote:
> This should get you close:
> UPDATE Reservation
> SET Amount = Amount - @.Amount
> FROM Reservation Reservation
> INNER JOIN Stock Stock
> ON Reservation.StockID = Stock.StockID
> WHERE Reservation.OrderItemID = @.OrderItemID
> AND Stock.HolderID = @.WarehouseID
> I'm assuming @.WarehouseID and @.OrderItemID are parameters passed into the
> stored procedure?
Wednesday, March 7, 2012
How to retrieve the text of UDF in SQL Server 2005
In the previous versions of SQL Server, retrieving the text of User
Defined Functions was easy:
SELECT text FROM syscomments sc INNER JOIN sysobjects so ON sc.id =
so.id WHERE so.name = 'fn_dblog' ORDER BY sc.colid
For some reason the above statement doesn't work in SQL Server 2005 (it
works for procedures and for views, but not for functions).
How can I retrieve the text of User Defined Functions in SQL Server
2005?
TIA
Dariusz DziewialtowskiHi
No, it works very well for user's UDF as well. This udf is not created by
an user ,moreover if i'm mo mistaken it isnt supported by MS
However you can achive it by issuing
sp_helptext 'fn_dblog'
<dariusz.dziewialtowski@.gmail.com> wrote in message
news:1144554215.276141.57970@.u72g2000cwu.googlegroups.com...
> In the previous versions of SQL Server, retrieving the text of User
> Defined Functions was easy:
> SELECT text FROM syscomments sc INNER JOIN sysobjects so ON sc.id =
> so.id WHERE so.name = 'fn_dblog' ORDER BY sc.colid
> For some reason the above statement doesn't work in SQL Server 2005 (it
> works for procedures and for views, but not for functions).
> How can I retrieve the text of User Defined Functions in SQL Server
> 2005?
> TIA
> Dariusz Dziewialtowski
>|||Hi Uri,
Thank you for your help.
>This udf is not created by an user
Yes, I gave it only as an example.
>However you can achive it by issuing sp_helptext 'fn_dblog'
I didn't think of that - I have to retrieve the text programmatically,
from VB6 code, but if I cannot make the old method work - "SELECT text
FROM syscomments" - than I'll try to execute sp_helptext
programmatically.
Still it puzzles me why "SELECT text FROM syscomments" is failing for
User Defined Functions in SQL Server 2005.
Thanks again for your help.
Dariusz Dziewialtowski.|||(dariusz.dziewialtowski@.gmail.com) writes:
> Still it puzzles me why "SELECT text FROM syscomments" is failing for
> User Defined Functions in SQL Server 2005.
In general it isn't:
CREATE FUNCTION myudf() RETURNS int AS
BEGIN
RETURN (99)
END
go
SELECT text FROM syscomments WHERE id = object_id('myudf')
go
DROP FUNCTION myudf
works for me.
However, in your original post you had fn_dblog, and that function
has moved and no longer lives in master, as have all other system
procedures and system UDFs. They now live in the hidden resource
database.
Also beware that SQL 2005 completely changes how metadata is stored.
The system tables from SQL 2000 are now merely compatibility views
on top of the new catalog views. The catalog views in their turn
refers to the new system tables that are accessible outside system code.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hello Erland,
Thank you for your help - I was not aware about the changes in metadata
in SQL Server 2005. Thanks a lot for explaining them to me.
Dariusz Dziewialtowski.
Defined Functions was easy:
SELECT text FROM syscomments sc INNER JOIN sysobjects so ON sc.id =
so.id WHERE so.name = 'fn_dblog' ORDER BY sc.colid
For some reason the above statement doesn't work in SQL Server 2005 (it
works for procedures and for views, but not for functions).
How can I retrieve the text of User Defined Functions in SQL Server
2005?
TIA
Dariusz DziewialtowskiHi
No, it works very well for user's UDF as well. This udf is not created by
an user ,moreover if i'm mo mistaken it isnt supported by MS
However you can achive it by issuing
sp_helptext 'fn_dblog'
<dariusz.dziewialtowski@.gmail.com> wrote in message
news:1144554215.276141.57970@.u72g2000cwu.googlegroups.com...
> In the previous versions of SQL Server, retrieving the text of User
> Defined Functions was easy:
> SELECT text FROM syscomments sc INNER JOIN sysobjects so ON sc.id =
> so.id WHERE so.name = 'fn_dblog' ORDER BY sc.colid
> For some reason the above statement doesn't work in SQL Server 2005 (it
> works for procedures and for views, but not for functions).
> How can I retrieve the text of User Defined Functions in SQL Server
> 2005?
> TIA
> Dariusz Dziewialtowski
>|||Hi Uri,
Thank you for your help.
>This udf is not created by an user
Yes, I gave it only as an example.
>However you can achive it by issuing sp_helptext 'fn_dblog'
I didn't think of that - I have to retrieve the text programmatically,
from VB6 code, but if I cannot make the old method work - "SELECT text
FROM syscomments" - than I'll try to execute sp_helptext
programmatically.
Still it puzzles me why "SELECT text FROM syscomments" is failing for
User Defined Functions in SQL Server 2005.
Thanks again for your help.
Dariusz Dziewialtowski.|||(dariusz.dziewialtowski@.gmail.com) writes:
> Still it puzzles me why "SELECT text FROM syscomments" is failing for
> User Defined Functions in SQL Server 2005.
In general it isn't:
CREATE FUNCTION myudf() RETURNS int AS
BEGIN
RETURN (99)
END
go
SELECT text FROM syscomments WHERE id = object_id('myudf')
go
DROP FUNCTION myudf
works for me.
However, in your original post you had fn_dblog, and that function
has moved and no longer lives in master, as have all other system
procedures and system UDFs. They now live in the hidden resource
database.
Also beware that SQL 2005 completely changes how metadata is stored.
The system tables from SQL 2000 are now merely compatibility views
on top of the new catalog views. The catalog views in their turn
refers to the new system tables that are accessible outside system code.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hello Erland,
Thank you for your help - I was not aware about the changes in metadata
in SQL Server 2005. Thanks a lot for explaining them to me.
Dariusz Dziewialtowski.
Subscribe to:
Comments (Atom)