Resolving function USER

If I log in to Query Analyzer using Windows Auth and run "PRINT USER", it returns "dbo". Is there any way to have the USER function actually return <Domain>\<login> for myself?Look up suser_name and suser_sname.

Rescursive Sort Sql Server 2005. How to sort by name in a function (see example)

Hello,
I have this example. My question is, how can I sort after "sortcol" and
"empname" ?
I mean, I get with this function all the nodes sorted but behalft the nodes
I want to
sort to "empname". How can I do this?
WITH NodeTree2(empid, empname, mgrid, depth, sortcol)
AS
(
SELECT empid, empname, mgrid, 0, CAST(empid AS VARBINARY(900))
FROM employees
WHERE empid = 1
UNION ALL
SELECT E.empid, E.empname, E.mgrid, M.depth+1, CAST(sortcol + CAST(E.empid
AS BINARY(4)) AS VARBINARY(900))
FROM Employees AS E
JOIN EmpCTE AS M
ON E.mgrid = M.empid
)
SELECT
REPLICATE('| ', depth)
+ '(' + (CAST(empid AS VARCHAR(10))) + ') '
+ empname AS empname
FROM EmpCTE
ORDER BY sortcol
Thanks for helping me!
AndreasAndreas,
Construct the binary sort path out of row numbers based on mgrid
partitioning and empname sorting:
WITH EmpCTE(empid, empname, mgrid, depth, sortcol)
AS
(
SELECT empid, empname, mgrid, 0, CAST(1 AS VARBINARY(MAX))
FROM employees
WHERE empid = 1
UNION ALL
SELECT E.empid, E.empname, E.mgrid, M.depth+1,
sortcol + CAST(
ROW_NUMBER() OVER(PARTITION BY E.mgrid ORDER BY E.empname)
AS BINARY(4))
FROM Employees AS E
JOIN EmpCTE AS M
ON E.mgrid = M.empid
)
SELECT
REPLICATE('| ', depth)
+ '(' + (CAST(empid AS VARCHAR(10))) + ') '
+ empname AS empname
FROM EmpCTE
ORDER BY sortcol;
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"Andreas Klemt" <aklemt68@.hotmail.com> wrote in message
news:%23xtB3ROiGHA.3848@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I have this example. My question is, how can I sort after "sortcol" and
> "empname" ?
> I mean, I get with this function all the nodes sorted but behalft the
> nodes I want to
> sort to "empname". How can I do this?
> WITH NodeTree2(empid, empname, mgrid, depth, sortcol)
> AS
> (
> SELECT empid, empname, mgrid, 0, CAST(empid AS VARBINARY(900))
> FROM employees
> WHERE empid = 1
> UNION ALL
> SELECT E.empid, E.empname, E.mgrid, M.depth+1, CAST(sortcol + CAST(E.empid
> AS BINARY(4)) AS VARBINARY(900))
> FROM Employees AS E
> JOIN EmpCTE AS M
> ON E.mgrid = M.empid
> )
> SELECT
> REPLICATE('| ', depth)
> + '(' + (CAST(empid AS VARCHAR(10))) + ') '
> + empname AS empname
> FROM EmpCTE
> ORDER BY sortcol
> Thanks for helping me!
> Andreas
>

Resaving / Rebuilding User Functions

I have about 10 User Defined Functions that all use another Function that we
can call funct1. These Functions in turn are used by around 50 Stored
Procedures. I recently made a change to funct1 to return an extra field and
it broke many of the stored procs and functions that called it. If I resaved
the functions then they started working again, even though no change was
made. I suppose they had cached the fields they were expecting from funct1
and needed to "rebuild" the list.
Is there some type of dbcc command that would automatically force everything
to be "rebuilt" or "resaved"? I tried DBCC FREEPROCCACHE but that evidently
only empties the execution plans.You can use sp_recompile to force a recompilation of the stored procedure on
next execution.
-oj
"Greg Steele" <Greg Steele@.discussions.microsoft.com> wrote in message
news:DEDFA89D-5592-42F3-A8F8-F40CFEE20919@.microsoft.com...
>I have about 10 User Defined Functions that all use another Function that
>we
> can call funct1. These Functions in turn are used by around 50 Stored
> Procedures. I recently made a change to funct1 to return an extra field
> and
> it broke many of the stored procs and functions that called it. If I
> resaved
> the functions then they started working again, even though no change was
> made. I suppose they had cached the fields they were expecting from funct1
> and needed to "rebuild" the list.
> Is there some type of dbcc command that would automatically force
> everything
> to be "rebuilt" or "resaved"? I tried DBCC FREEPROCCACHE but that
> evidently
> only empties the execution plans.|||Also, make sure that you are not using "SELECT *" in your functions,
because bad things may happen, for example:
USE tempdb
GO
CREATE FUNCTION dbo.FirstFunction()
RETURNS TABLE AS RETURN
SELECT 1 A, 2 B
GO
CREATE FUNCTION dbo.SecondFunction()
RETURNS TABLE AS RETURN
SELECT * FROM dbo.FirstFunction()
GO
CREATE PROCEDURE Procedure1
AS
SELECT A, B FROM dbo.SecondFunction()
GO
EXEC Procedure1
GO
ALTER FUNCTION dbo.FirstFunction()
RETURNS TABLE AS RETURN
SELECT 1 A, 3 C, 2 B
GO
EXEC Procedure1
EXEC sp_recompile 'Procedure1'
EXEC Procedure1
EXEC sp_recompile 'SecondFunction'
EXEC Procedure1
GO
ALTER FUNCTION dbo.SecondFunction()
RETURNS TABLE AS RETURN
SELECT * FROM dbo.FirstFunction()
GO
EXEC Procedure1
GO
DROP FUNCTION FirstFunction, SecondFunction
DROP PROCEDURE Procedure1
As you can see from the above example, if you are using "SELECT *" (in
a view or in-line function) and you add a new column before another
column (or change the order of the columns), this messes-up things
pretty badly: it returns data from the wrong columns; this cannot be
fixed by using sp_recompile, only by altering the UDF that contains
"SELECT *".
Razvan

reregister sqlDmo.dll

When I try to script my database, I get the following error:
schema_name is not recognized function name
I found that I have to reregister sqlDmo.dll
How can I do that?
Thank you,
Simonregsvr32.exe "%ProgramFiles%\Microsoft SQL Server\80\Tools\Binn\sqldmo.dll"
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"simon" <simon.zupan@.stud-moderna.si> wrote in message
news:OQHzo5pCFHA.4004@.tk2msftngp13.phx.gbl...
> When I try to script my database, I get the following error:
> schema_name is not recognized function name
> I found that I have to reregister sqlDmo.dll
> How can I do that?
> Thank you,
> Simon
>

Require help writing a function

Hi there
I am trying to write a function which will return the Maximum value in a
field of a given table and have run into problems.
I want to pass the name of the field and the name of the table to the
function, have it find the Max value and then add 1 to that number and
return the result.
After doing some reading I thought a Simple Scalar Function would be the way
to go but I just cannot make it work.
Could anyone help me please.
Thanks
June
hi June,
"June Macleod" <junework@.hotmail.com> ha scritto nel messaggio
news:esYBukT4EHA.2568@.TK2MSFTNGP10.phx.gbl
> Hi there
> I am trying to write a function which will return the Maximum value
> in a field of a given table and have run into problems.
> I want to pass the name of the field and the name of the table to the
> function, have it find the Max value and then add 1 to that number and
> return the result.
> After doing some reading I thought a Simple Scalar Function would be
> the way to go but I just cannot make it work.
>
what you want to do is known as Dynamic SQL, which provides poor
performances and security holes toubles...
for your informatgion, please have a look at
http://www.sommarskog.se/dynamic_sql.html for a great explanation of it's
uses and related troubles... and something like
CREATE FUNCTION dbo.udfNextValue (
@.col VARCHAR(10) ,
@.tbl VARCHAR (10)
) RETURNS INT
AS BEGIN
DECLARE @.cmd NVARCHAR
DECLARE @.Value INT
SET @.cmd ='SELECT @.Value = ISNULL(MAX(' + @.col + ') , 0) + 1 FROM ' + @.tbl
EXEC sp_executesql @.cmd, N'@.Value INT OUTPUT', @.Value OUTPUT
RETURN (@.Value)
END
is unfortunately not permitted within user defined function...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Reproducable bug, yay!

Hi Ian,
Here is a good article about changes in behavior for this function, between
versions 2000 and 2005.
Change in Behavior of RAND and NEWID in SQL Server 2005
http://www.sqlmag.com/Articles/ArticleID/97032/97032.html?Ad=1
Enjoy it,
AMB
"Ian Boyd" wrote:

> Steps to reproduce:
> --Create test table of one uniqueidentifier column
> CREATE TABLE [dbo].[UniverseCollapse] (
> [SomeID] [uniqueidentifier] NOT NULL
> )
> --Add clustered index
> CREATE CLUSTERED INDEX [IX_UniverseCollapse_1] ON
> [dbo].[UniverseCollapse]([SomeID])
> --Add test row
> INSERT INTO UniverseCollapse (SomeID) VALUES
> ('F9CB8628-571D-463B-9E82-0AC4D0A3C2EB')
> --Run the select over and over. Sometimes returns zero rows, sometimes
> returns one.
> SELECT SomeID
> FROM UniverseCollapse
> WHERE SomeID = 'F9CB8628-571D-463B-9E82-0AC4D0A3C2EB'
> AND '6c03c85d-8151-43ed-858f-7ec5d887689b' <> newID()
>
> Run the select over and over and sometimes you get zero rows, sometimes you
> get one.
> Without the clustered index, it never fails.
> Looking closer at the two WHERE clause conditions:
> 1) SomeID = '...'
> This is always the case, as it is the value that was inserted.
> 2) '6c03...' <> newid()
> This is a trueism, since guid's don't conflict very that often.
> If we run just:
> SELECT SomeID
> FROM UniverseCollapse
> WHERE SomeID = 'F9CB8628-571D-463B-9E82-0AC4D0A3C2EB'
> we always get one row. If we run:
> SELECT SomeID
> FROM UniverseCollapse
> WHERE '6c03c85d-8151-43ed-858f-7ec5d887689b' <> newID()
> we always get one row. Have both criteria together, and we sometimes get
> zero rows.
> Execution Plan:
> |--Filter(
> WHERE
> [UniverseCollapse].[SomeID]<newid()
> OR
> [UniverseCollapse].[SomeID]>newid() ))
> |--Clustered Index Seek(
> OBJECT
> [foo].[dbo].[UniverseCollapse].[IX_UniverseCollapse_1]),
> SEEK[UniverseCollapse].[SomeID]=F9CB8628-571D-463B-9E82-0AC4D0A3C2EB)
> ORDERED FORWARD)
> The "ID < newid OR ID > newid" is very interesting. If "newid()" is
> evaluated twice, once for each half of the WHERE clause, and it happens to
> get a GUID that has swung the other way, it could then eliminate the row
> when it shouldn't.
>
> If i only execute:
> SELECT SomeID
> FROM UniverseCollapse
> WHERE '6c03c85d-8151-43ed-858f-7ec5d887689b' <> newID()
> i get the plan:
> |--Filter(
> WHERE
> STARTUP
> R(6C03C85D-8151-43ED-858F-7EC5D887689B<>newid()) ))
> |--Clustered Index Scan(
> OBJECT[foo].[dbo].[UniverseCollapse].[IX_UniverseCollapse_1]))
> And it's only evaluating newid() once. So the question then becomes, "Why
> does it fail when a clustered index is there?" Looking at the execution
> plan if i remove the clustered index:
> |--Filter(
> WHERESTARTUP EXPR(6C03C85D-8151-43ED-858F-7EC5D887689B<>newid())))
> |--Table Scan(
> OBJECT[foo].[dbo].[UniverseCollapse]),
> WHERE[UniverseCollapse].[SomeID]=F9CB8628-571D-463B-9E82-0AC4D0A3C2EB))
> It doesn't do a "guid > newid or guid < newid", but instead does a "Startup
> Expression". Which means that the problem is in fact that newID is being
> evaluated twice for the "field <> newid()"
>
> Yay! i found a bug! So do i get a cheque or something?
>
>
> Here is a good article about changes in behavior for this function,
> between
> versions 2000 and 2005.
> Change in Behavior of RAND and NEWID in SQL Server 2005
> http://www.sqlmag.com/Articles/ArticleID/97032/97032.html?Ad=1
It should be mentioned that these changes to newid() are not intended for
this case. These changes ensure that Rand() and Newid() are only evaluated
once for each reference. Before if you aliased a column that was a Rand or a
NewID, it would generate a new value for each time the alias is used.
For example in:
SELECT
CASE
WHEN TheValue < 0.5 THEN 'LessThanPoint5'
ELSE 'GreaterThanPoint5'
END AS Test1,
CASE
WHEN TheValue < 0.5 THEN 'LessThanPoint5'
ELSE 'GreaterThanPoint5'
END AS Test2,
CASE
WHEN TheValue < 0.5 THEN 'LessThanPoint5'
ELSE 'GreaterThanPoint5'
END AS Test3
FROM (SELECT rand() as TheValue) ADerivedTable
it happens that for any given execution, Test1 Test2 and Test3 need not be
equal, even through you're selecting rand() only once. Because the rand
column is aliased, it generates a new value.
This is also a bug, but not quite the situation i have, since i am not
referencing the rand or newid column more than once. The optimizer is
internally turning my one reference into two references - and then exposing
the bug.