Getting a simple function to run in the CLR . Please help :-(

Hi everyone,
If anyone can help me with the following, I would be very greatful.
I want to create a pretty basic function under sql server 2005's runtime.
I've made the assembly and attached it. And when I do something totally simp
le
like return a simple string it works fine. However when I try and use a conn
ection
object I get an exception as follows:
A .NET Framework error occurred during execution of user defined routine
or aggregate 'GetRolesString':
System.Security.HostProtectionException: Attempted to perform an operation
that was forbidden by the CLR host.
The protected resources (only available with full trust) were: All
The demanded resources were: UI
It seems to be suggesting that everything is protected and so I can't do
anything. That would be fine but I havent a clue how to unprotect them.
The things I've tried are:
1. Making the database property Trustworthy equal to true
2. Marking the Permission_Set for the assembly as EXTERNAL (or whatever it
is)
These things havent helped
A huge thank you to anyone who can tell me how to unprotect me crap :-)
Thanks gain
SimonHello Simon,
The attachment didn't work here. Please contact me at ktegels@.develop.com
about this one.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Simon Harvey <nothanks@.hotmail.com> wrote in
news:7c72785b10da8c7bcafc4d67937@.news.microsoft.com:

> Hi everyone,
> If anyone can help me with the following, I would be very greatful.
> I want to create a pretty basic function under sql server 2005's
> runtime. I've made the assembly and attached it. And when I do
> something totally simple like return a simple string it works fine.
> However when I try and use a connection object I get an exception as
> follows:
> A .NET Framework error occurred during execution of user defined
> routine or aggregate 'GetRolesString':
> System.Security.HostProtectionException: Attempted to perform an
> operation that was forbidden by the CLR host.
> The protected resources (only available with full trust) were: All
> The demanded resources were: UI
> It seems to be suggesting that everything is protected and so I can't
> do anything. That would be fine but I havent a clue how to unprotect
> them.
>
Using the connection object shouldn't be a problem. The error you're
getting looks like it has something to do with UI, in other words are
you trying to do a Console.WriteLine or something similar?
If you post your code for the method it'll be easier for us to see
what's wrong.Anyway, below follows some code snippets for the
connection:
public static void GetData() {
SqlConnection conn = new SqlConnection("Context Connection=true");
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "Select some data from somewhere";
SqlContext.Pipe.ExecuteAndSend(cmd);
}
Notice that in the code above it is not entirely necessary to use the
connection at all, as you are calling ExecuteAndSend on the SqlPipe
class.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||Niels my man, you sir are a rutting stallion!
It was that I was trying to output to the console when catching an exception
.
Force of habit really, just so I can see what the exception was a bit easier
.
All fixed now.
Sincerest thanks to you and Kent
Kindest Regards
Simon|||Instead of writing to the console, you could trow an cutom error, and that
would be catched by the calling program.
-Mark
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b12aa8c7bcd0aa78c851@.news.microsoft.com...
> Niels my man, you sir are a rutting stallion!
> It was that I was trying to output to the console when catching an
> exception. Force of habit really, just so I can see what the exception was
> a bit easier.
> All fixed now.
> Sincerest thanks to you and Kent
> Kindest Regards
> Simon
>|||Hi Mark
I know I could do it that way. It's realy just during debugging I sometimes
find it helpful. It's not a huge issue but thanks though
Kindest Regards
Simon

Getting a simple function to run in the CLR . Please help :-(

Hi everyone,
If anyone can help me with the following, I would be very greatful.
I want to create a pretty basic function under sql server 2005's runtime.
I've made the assembly and attached it. And when I do something totally simp
le
like return a simple string it works fine. However when I try and use a conn
ection
object I get an exception as follows:
A .NET Framework error occurred during execution of user defined routine
or aggregate 'GetRolesString':
System.Security.HostProtectionException: Attempted to perform an operation
that was forbidden by the CLR host.
The protected resources (only available with full trust) were: All
The demanded resources were: UI
It seems to be suggesting that everything is protected and so I can't do
anything. That would be fine but I havent a clue how to unprotect them.
The things I've tried are:
1. Making the database property Trustworthy equal to true
2. Marking the Permission_Set for the assembly as EXTERNAL (or whatever it
is)
These things havent helped
A huge thank you to anyone who can tell me how to unprotect me crap :-)
Thanks gain
SimonSimon Harvey <nothanks@.hotmail.com> wrote in
news:7c72785b10da8c7bcafc4d67937@.news.microsoft.com:

> Hi everyone,
> If anyone can help me with the following, I would be very greatful.
> I want to create a pretty basic function under sql server 2005's
> runtime. I've made the assembly and attached it. And when I do
> something totally simple like return a simple string it works fine.
> However when I try and use a connection object I get an exception as
> follows:
> A .NET Framework error occurred during execution of user defined
> routine or aggregate 'GetRolesString':
> System.Security.HostProtectionException: Attempted to perform an
> operation that was forbidden by the CLR host.
> The protected resources (only available with full trust) were: All
> The demanded resources were: UI
> It seems to be suggesting that everything is protected and so I can't
> do anything. That would be fine but I havent a clue how to unprotect
> them.
>
Using the connection object shouldn't be a problem. The error you're
getting looks like it has something to do with UI, in other words are
you trying to do a Console.WriteLine or something similar?
If you post your code for the method it'll be easier for us to see
what's wrong.Anyway, below follows some code snippets for the
connection:
public static void GetData() {
SqlConnection conn = new SqlConnection("Context Connection=true");
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "Select some data from somewhere";
SqlContext.Pipe.ExecuteAndSend(cmd);
}
Notice that in the code above it is not entirely necessary to use the
connection at all, as you are calling ExecuteAndSend on the SqlPipe
class.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||Niels my man, you sir are a rutting stallion!
It was that I was trying to output to the console when catching an exception
.
Force of habit really, just so I can see what the exception was a bit easier
.
All fixed now.
Sincerest thanks to you and Kent
Kindest Regards
Simon|||Instead of writing to the console, you could trow an cutom error, and that
would be catched by the calling program.
-Mark
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b12aa8c7bcd0aa78c851@.news.microsoft.com...
> Niels my man, you sir are a rutting stallion!
> It was that I was trying to output to the console when catching an
> exception. Force of habit really, just so I can see what the exception was
> a bit easier.
> All fixed now.
> Sincerest thanks to you and Kent
> Kindest Regards
> Simon
>|||Hi Mark
I know I could do it that way. It's realy just during debugging I sometimes
find it helpful. It's not a huge issue but thanks though
Kindest Regards
Simon

Getting a simple function to run in the CLR . Please help :-(

Hi everyone,
If anyone can help me with the following, I would be very greatful.
I want to create a pretty basic function under sql server 2005's runtime.
I've made the assembly and attached it. And when I do something totally simple
like return a simple string it works fine. However when I try and use a connection
object I get an exception as follows:
A .NET Framework error occurred during execution of user defined routine
or aggregate 'GetRolesString':
System.Security.HostProtectionException: Attempted to perform an operation
that was forbidden by the CLR host.
The protected resources (only available with full trust) were: All
The demanded resources were: UI
It seems to be suggesting that everything is protected and so I can't do
anything. That would be fine but I havent a clue how to unprotect them.
The things I've tried are:
1. Making the database property Trustworthy equal to true
2. Marking the Permission_Set for the assembly as EXTERNAL (or whatever it
is)
These things havent helped
A huge thank you to anyone who can tell me how to unprotect me crap :-)
Thanks gain
Simon
Simon Harvey <nothanks@.hotmail.com> wrote in
news:7c72785b10da8c7bcafc4d67937@.news.microsoft.co m:

> Hi everyone,
> If anyone can help me with the following, I would be very greatful.
> I want to create a pretty basic function under sql server 2005's
> runtime. I've made the assembly and attached it. And when I do
> something totally simple like return a simple string it works fine.
> However when I try and use a connection object I get an exception as
> follows:
> A .NET Framework error occurred during execution of user defined
> routine or aggregate 'GetRolesString':
> System.Security.HostProtectionException: Attempted to perform an
> operation that was forbidden by the CLR host.
> The protected resources (only available with full trust) were: All
> The demanded resources were: UI
> It seems to be suggesting that everything is protected and so I can't
> do anything. That would be fine but I havent a clue how to unprotect
> them.
>
Using the connection object shouldn't be a problem. The error you're
getting looks like it has something to do with UI, in other words are
you trying to do a Console.WriteLine or something similar?
If you post your code for the method it'll be easier for us to see
what's wrong.Anyway, below follows some code snippets for the
connection:
public static void GetData() {
SqlConnection conn = new SqlConnection("Context Connection=true");
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "Select some data from somewhere";
SqlContext.Pipe.ExecuteAndSend(cmd);
}
Notice that in the code above it is not entirely necessary to use the
connection at all, as you are calling ExecuteAndSend on the SqlPipe
class.
Niels
**************************************************
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
**************************************************
|||Niels my man, you sir are a rutting stallion!
It was that I was trying to output to the console when catching an exception.
Force of habit really, just so I can see what the exception was a bit easier.
All fixed now.
Sincerest thanks to you and Kent
Kindest Regards
Simon
|||Instead of writing to the console, you could trow an cutom error, and that
would be catched by the calling program.
-Mark
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b12aa8c7bcd0aa78c851@.news.microsoft.co m...
> Niels my man, you sir are a rutting stallion!
> It was that I was trying to output to the console when catching an
> exception. Force of habit really, just so I can see what the exception was
> a bit easier.
> All fixed now.
> Sincerest thanks to you and Kent
> Kindest Regards
> Simon
>
|||Hi Mark
I know I could do it that way. It's realy just during debugging I sometimes
find it helpful. It's not a huge issue but thanks though
Kindest Regards
Simon
sql

Getting a simple function to run in the CLR . Please help :-(

Hi everyone,
If anyone can help me with the following, I would be very greatful.
I want to create a pretty basic function under sql server 2005's runtime.
I've made the assembly and attached it. And when I do something totally simple
like return a simple string it works fine. However when I try and use a connection
object I get an exception as follows:
A .NET Framework error occurred during execution of user defined routine
or aggregate 'GetRolesString':
System.Security.HostProtectionException: Attempted to perform an operation
that was forbidden by the CLR host.
The protected resources (only available with full trust) were: All
The demanded resources were: UI
It seems to be suggesting that everything is protected and so I can't do
anything. That would be fine but I havent a clue how to unprotect them.
The things I've tried are:
1. Making the database property Trustworthy equal to true
2. Marking the Permission_Set for the assembly as EXTERNAL (or whatever it
is)
These things havent helped
A huge thank you to anyone who can tell me how to unprotect me crap :-)
Thanks gain
SimonSimon Harvey <nothanks@.hotmail.com> wrote in
news:7c72785b10da8c7bcafc4d67937@.news.microsoft.com:
> Hi everyone,
> If anyone can help me with the following, I would be very greatful.
> I want to create a pretty basic function under sql server 2005's
> runtime. I've made the assembly and attached it. And when I do
> something totally simple like return a simple string it works fine.
> However when I try and use a connection object I get an exception as
> follows:
> A .NET Framework error occurred during execution of user defined
> routine or aggregate 'GetRolesString':
> System.Security.HostProtectionException: Attempted to perform an
> operation that was forbidden by the CLR host.
> The protected resources (only available with full trust) were: All
> The demanded resources were: UI
> It seems to be suggesting that everything is protected and so I can't
> do anything. That would be fine but I havent a clue how to unprotect
> them.
>
Using the connection object shouldn't be a problem. The error you're
getting looks like it has something to do with UI, in other words are
you trying to do a Console.WriteLine or something similar?
If you post your code for the method it'll be easier for us to see
what's wrong.Anyway, below follows some code snippets for the
connection:
public static void GetData() {
SqlConnection conn = new SqlConnection("Context Connection=true");
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "Select some data from somewhere";
SqlContext.Pipe.ExecuteAndSend(cmd);
}
Notice that in the code above it is not entirely necessary to use the
connection at all, as you are calling ExecuteAndSend on the SqlPipe
class.
Niels
--
**************************************************
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
**************************************************

Getting a return value from a Stored Procedure

Hi all,
Is there anyway to get a returned value from a called Stored Procedure from within a piece ofSQL? For example, I have the following code...

DECLARE @.testval AS INT
SET @.testval = EXEC u_checknew_dwi_limits '163'
IF (@.testval = 0)
BEGIN
PRINT '0 Returned'
END
ELSE
BEGIN
PRINT '1 Returned'
END
...whichas you can see calls a SP called 'u_checknew_dwi_limits'. This SP(u_checknew_dwi_limits) actually returns a value (1 or 0), so I want toassign that value to the '@.testval' variable (as you can see in mycode) - but Query Analyser is throwing an error at me. Is this thecorrect way to do this?
Thanks
Tryst

The SP that you are calling should contain an OUTPUT parameter.
So, in your big SP you would get the OUTPUT parameter as follows:
DECLARE @.outParm VARCHAR(50)
EXEC SP_Name , ...(input parameters), ... @.outParam (output parameter)
print @.outParam
Hope that helps ,
Regards

|||Hi, and thanks for the reply. Its seems I got what I needed from using the following line of code...
DECLARE @.testval AS INT
EXEC @.testval = u_checknew_dwi_limits @.varval
Is this a more efficient way of doing thing?
Tryst

Getting a Major error.....

When i try to launch SQL 2005, I'm all of a sudden getting the following error:
"SqlWb.exe - Application Error
The application failed to initialize properly (0xc0000034). Click on OK to
terminate the application."
Any suggestions or recommendations? Tried to search the knowledgebase, but,
couldn't find anything.
Thanks.
JJ
Maybe I am wrong but looks like SqlWb is Management Studio so perhaps SQL
Server is really running. Try connecting from another client like Query
Analyzer to see if the instance is running.
With no additional information perhaps what I would try is rebooting the
computer and, if the problem is still there, reinstalling the client tools.
Regards,
Ben Nevarez
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:658463FC-52F7-493E-9FF7-7DA363D12681@.microsoft.com...
> When i try to launch SQL 2005, I'm all of a sudden getting the following
> error:
> "SqlWb.exe - Application Error
> The application failed to initialize properly (0xc0000034). Click on OK to
> terminate the application."
> Any suggestions or recommendations? Tried to search the knowledgebase,
> but,
> couldn't find anything.
> Thanks.
> --
> JJ

Getting a Major error.....

When i try to launch SQL 2005, I'm all of a sudden getting the following error:
"SqlWb.exe - Application Error
The application failed to initialize properly (0xc0000034). Click on OK to
terminate the application."
Any suggestions or recommendations? Tried to search the knowledgebase, but,
couldn't find anything.
Thanks.
--
JJMaybe I am wrong but looks like SqlWb is Management Studio so perhaps SQL
Server is really running. Try connecting from another client like Query
Analyzer to see if the instance is running.
With no additional information perhaps what I would try is rebooting the
computer and, if the problem is still there, reinstalling the client tools.
Regards,
Ben Nevarez
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:658463FC-52F7-493E-9FF7-7DA363D12681@.microsoft.com...
> When i try to launch SQL 2005, I'm all of a sudden getting the following
> error:
> "SqlWb.exe - Application Error
> The application failed to initialize properly (0xc0000034). Click on OK to
> terminate the application."
> Any suggestions or recommendations? Tried to search the knowledgebase,
> but,
> couldn't find anything.
> Thanks.
> --
> JJ

Getting a Major error.....

When i try to launch SQL 2005, I'm all of a sudden getting the following err
or:
"SqlWb.exe - Application Error
The application failed to initialize properly (0xc0000034). Click on OK to
terminate the application."
Any suggestions or recommendations? Tried to search the knowledgebase, but,
couldn't find anything.
Thanks.
JJMaybe I am wrong but looks like SqlWb is Management Studio so perhaps SQL
Server is really running. Try connecting from another client like Query
Analyzer to see if the instance is running.
With no additional information perhaps what I would try is rebooting the
computer and, if the problem is still there, reinstalling the client tools.
Regards,
Ben Nevarez
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:658463FC-52F7-493E-9FF7-7DA363D12681@.microsoft.com...
> When i try to launch SQL 2005, I'm all of a sudden getting the following
> error:
> "SqlWb.exe - Application Error
> The application failed to initialize properly (0xc0000034). Click on OK to
> terminate the application."
> Any suggestions or recommendations? Tried to search the knowledgebase,
> but,
> couldn't find anything.
> Thanks.
> --
> JJsql

GetProperties returns undeterminable timeout value

I have the following code:
// Get timeout
Property itemTimeout = new Property();
itemTimeout.Name = "ReportTimeout";
Property[] props = new Property[1];
props[1] = itemName;
props[1] = itemTimeout;
Property[] itemProps = _rs.GetProperties(path_, props);
Problem I'm seeing is that this code returns no output array member
for the timeout if either the system default is being used or if there
is no timeout. How can I figure out which one it is then?Whoops - I tried to edit the code to take out some stuff that didn't
pertain to the problem. The code is actually good, looking like this:
Property itemTimeout = new Property();
itemTimeout.Name = "ReportTimeout";
Property[] props = new Property[1];
props[0] = itemTimeout;
Property[] itemProps = _rs.GetProperties(path_, props);
bspann <bspann@.discussions.microsoft.com> wrote in message news:<29B6FD22-E731-4486-9A9A-300BFD6D426E@.microsoft.com>...
> Derek,
> There is a problem with your code. In .NET all arrays are 0 based. So an
> array with one element should be arrayname[0]. So your code should look like
> this:
> Property[] props = new Property[1];
> props[0] = itemTimeout;
> Property[] itemProps = _rs.GetProperties(_path, props);
>
> "Derek Knudsen" wrote:
> > I have the following code:
> >
> > // Get timeout
> > Property itemTimeout = new Property();
> > itemTimeout.Name = "ReportTimeout";
> >
> > Property[] props = new Property[1];
> > props[1] = itemName;
> > props[1] = itemTimeout;
> >
> > Property[] itemProps = _rs.GetProperties(path_, props);
> >
> > Problem I'm seeing is that this code returns no output array member
> > for the timeout if either the system default is being used or if there
> > is no timeout. How can I figure out which one it is then?
> >|||More on this:
The GetProperty works fine, with -1 being returned if there is no timeout
and no value being returned if it uses the default setting. Problem was with
setting it to the default setting, which I had thought you could do by
setting the value to "0" (I can't find where I saw this). This throws an
"invalid value" exception. Anyway, solution was to not specify a value and
then it gets reset to "use default setting". Thanks.
"Derek Knudsen" wrote:
> Whoops - I tried to edit the code to take out some stuff that didn't
> pertain to the problem. The code is actually good, looking like this:
> Property itemTimeout = new Property();
> itemTimeout.Name = "ReportTimeout";
> Property[] props = new Property[1];
> props[0] = itemTimeout;
> Property[] itemProps = _rs.GetProperties(path_, props);
> bspann <bspann@.discussions.microsoft.com> wrote in message news:<29B6FD22-E731-4486-9A9A-300BFD6D426E@.microsoft.com>...
> > Derek,
> >
> > There is a problem with your code. In .NET all arrays are 0 based. So an
> > array with one element should be arrayname[0]. So your code should look like
> > this:
> >
> > Property[] props = new Property[1];
> > props[0] = itemTimeout;
> >
> > Property[] itemProps = _rs.GetProperties(_path, props);
> >
> >
> > "Derek Knudsen" wrote:
> >
> > > I have the following code:
> > >
> > > // Get timeout
> > > Property itemTimeout = new Property();
> > > itemTimeout.Name = "ReportTimeout";
> > >
> > > Property[] props = new Property[1];
> > > props[1] = itemName;
> > > props[1] = itemTimeout;
> > >
> > > Property[] itemProps = _rs.GetProperties(path_, props);
> > >
> > > Problem I'm seeing is that this code returns no output array member
> > > for the timeout if either the system default is being used or if there
> > > is no timeout. How can I figure out which one it is then?
> > >
>

GetDate() parameter in Function.... Not working...

I have a function that has the following two parameters. When I try to
compile I get an error on the GetDate().
If I remove the () from GetDate, it compiles but doesn't return the correct
results.
Example.
ALTER FUNCTION dbo.SiteMaxFileByExt
(
@.Days int = -1,
@.Date datetime = GetDate()
)
RETURNS TABLE
AS
RETURN SELECT TOP 100 PERCENT
tmSystem.dbo.vw_SiteExtensionList.SiteID, dbo.a_SiphonDetail.FileExt,
MAX(dbo.a_SiphonDetail.FileDate) AS MaxDate,
MAX(dbo.a_SiphonDetail.FileName) AS FileName
from tmSystem.dbo.vw_SiteExtensionList
where tmSystem.dbo.vw_SiteExtensionList.FileDate < @.date
Thanks,
Roghttp://www.aspfaq.com/2439
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Roger" <davisro@.netins.net> wrote in message
news:uM4z3r8KFHA.3788@.tk2msftngp13.phx.gbl...
> I have a function that has the following two parameters. When I try to
> compile I get an error on the GetDate().
> If I remove the () from GetDate, it compiles but doesn't return the
correct
> results.
> Example.
> ALTER FUNCTION dbo.SiteMaxFileByExt
> (
> @.Days int = -1,
> @.Date datetime = GetDate()
> )
> RETURNS TABLE
> AS
> RETURN SELECT TOP 100 PERCENT
> tmSystem.dbo.vw_SiteExtensionList.SiteID, dbo.a_SiphonDetail.FileExt,
> MAX(dbo.a_SiphonDetail.FileDate) AS MaxDate,
> MAX(dbo.a_SiphonDetail.FileName) AS FileName
> from tmSystem.dbo.vw_SiteExtensionList
> where tmSystem.dbo.vw_SiteExtensionList.FileDate < @.date
>
> Thanks,
> Rog
>|||Look up "user-defined functions, creating" in BOL.
It explains everything there.
In a nutshell, you cannot use GetDate() inside a UDF.
"Roger" <davisro@.netins.net> wrote in message
news:uM4z3r8KFHA.3788@.tk2msftngp13.phx.gbl...
>I have a function that has the following two parameters. When I try to
> compile I get an error on the GetDate().
> If I remove the () from GetDate, it compiles but doesn't return the
> correct
> results.
> Example.
> ALTER FUNCTION dbo.SiteMaxFileByExt
> (
> @.Days int = -1,
> @.Date datetime = GetDate()
> )
> RETURNS TABLE
> AS
> RETURN SELECT TOP 100 PERCENT
> tmSystem.dbo.vw_SiteExtensionList.SiteID, dbo.a_SiphonDetail.FileExt,
> MAX(dbo.a_SiphonDetail.FileDate) AS MaxDate,
> MAX(dbo.a_SiphonDetail.FileName) AS FileName
> from tmSystem.dbo.vw_SiteExtensionList
> where tmSystem.dbo.vw_SiteExtensionList.FileDate < @.date
>
> Thanks,
> Rog
>

getdate() in user defined function

Hi,

It is possible to use getdate() in userdefined function. If so, how to do the same ?

The following code throws error :

create function function1
return varchar
DECLARE @.currYYMM VARCHAR(20)
SET @.currYYMM = convert(char(4),getdate(),12)
// Here it says the error 'getdate' can't be used inside functions
............
................If I recall correctly, a scalar user defined function must return a deterministic value (ie, if you pass in the same parameters, you will get the same results). A non-deterministic function, would take a randomizer (such as GetDate()) and return a different result everytime you called it (even when calling it with the same parameters). I don't believe that this is allowed.

Regards,

hmscott

Hi,

It is possible to use getdate() in userdefined function. If so, how to do the same ?

The following code throws error :

create function function1
return varchar
DECLARE @.currYYMM VARCHAR(20)
SET @.currYYMM = convert(char(4),getdate(),12)
// Here it says the error 'getdate' can't be used inside functions
............
................|||Create view v_getdate as
Select ThisDate = getdate()

Then reference v_getdate.ThisDate in your function.

getdate() format problem

Hello,
I am just trying to do a simple time stamp. When I do the following SQL
command
SELECT getdate() my book and Microsoft say I should get the format like thi
s:
3/22/2002 6:08:08 AM
Instead when I run this I get the following format:
2006-04-07 18:37:19.823
How do I get it to return the first format?
Thanks,
MichaelGETDATE() returns a datetime datatype, which has no particular display
format, as it is binary in nature. Unless you explicitly convert the
datetime to a character string, SQL Server has nothing to do with how
the date and time are displayed to you. That is a function of the
front end application, such as Query Analyzer (SQL Server 2000) or
Management Studio (SQL Server 2005).
To see what alternatives you have if you convert datetime to a string
explicitly, see the style parameter of the CONVERT function in Books
on Line.
Roy Harvey
Beacon Falls, CT
On Fri, 7 Apr 2006 16:50:02 -0700, Michael
<Michael@.discussions.microsoft.com> wrote:

>Hello,
>I am just trying to do a simple time stamp. When I do the following SQL
>command
>SELECT getdate() my book and Microsoft say I should get the format like th
is:
> 3/22/2002 6:08:08 AM
>Instead when I run this I get the following format:
> 2006-04-07 18:37:19.823
>How do I get it to return the first format?
>Thanks,
>Michael|||Try this:
declare @.date datetime
set @.date = getdate()
select convert(varchar(20), @.date, 101)+ ' ' +convert(varchar(20), @.date, 10
8)
"Roy Harvey" wrote:

> GETDATE() returns a datetime datatype, which has no particular display
> format, as it is binary in nature. Unless you explicitly convert the
> datetime to a character string, SQL Server has nothing to do with how
> the date and time are displayed to you. That is a function of the
> front end application, such as Query Analyzer (SQL Server 2000) or
> Management Studio (SQL Server 2005).
> To see what alternatives you have if you convert datetime to a string
> explicitly, see the style parameter of the CONVERT function in Books
> on Line.
> Roy Harvey
> Beacon Falls, CT
> On Fri, 7 Apr 2006 16:50:02 -0700, Michael
> <Michael@.discussions.microsoft.com> wrote:
>
>|||http://www.aspfaq.com/2464
http://www.aspfaq.com/2460
"Michael" <Michael@.discussions.microsoft.com> wrote in message
news:8669696A-88B3-4F33-88E5-42997C68B387@.microsoft.com...
> Hello,
> I am just trying to do a simple time stamp. When I do the following SQL
> command
> SELECT getdate() my book and Microsoft say I should get the format like
> this:
> 3/22/2002 6:08:08 AM
> Instead when I run this I get the following format:
> 2006-04-07 18:37:19.823
> How do I get it to return the first format?
> Thanks,
> Michael

GetDate() Does not Return Milliseconds ?

Ladies / Gentlemen
If you run the following select statement you will find as I did
that GetDate() does no obtain Milliseconds - Why Not and can I use something
that does ?
Mark Moss
SELECT CONVERT(varchar(20), GETDATE(), 113) AS Expr2,
CONVERT(varchar(20), GETDATE(), 109) AS Expr1Hi Mark
The getdate() function ALWAYS returns milliseconds. It's only when you
convert it to character that the milliseconds might be discarded.
In this situation, you have not provided enough space to hold the
milliseconds. Try varchar(25) instead of varchar(20)
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"msnews.microsoft.com" <markmoss@.adelphia.net> wrote in message
news:%234V0y1ucGHA.4428@.TK2MSFTNGP03.phx.gbl...
> Ladies / Gentlemen
> If you run the following select statement you will find as I did
> that GetDate() does no obtain Milliseconds - Why Not and can I use
> something that does ?
>
> Mark Moss
>
> SELECT CONVERT(varchar(20), GETDATE(), 113) AS Expr2,
> CONVERT(varchar(20), GETDATE(), 109) AS Expr1
>|||Mark,
GETDATE() returns a datetime, which includes a millisec part. If you do
"SELECT DATEPART(ms,GETDATE())" you'll see this. Your problem is that
you're converting to a 20 char string and the millisec bit it getting
truncated. It's a string truncation issue, not a datetime issue. Try
using a varchar(30) or something bigger.
Also, you ought to use CURRENT_TIMESTAMP rather than GETDATE() as
CURRENT_TIMESTAMP is the ANSI equivalent (GETDATE() is Microsoft
proprietary) and in the majority of cases you should leave presentation
of the data up to the presentation layer (ie. the client) rather than
the DB engine.
*mike hodgson*
http://sqlnerd.blogspot.com
msnews.microsoft.com wrote:

>Ladies / Gentlemen
> If you run the following select statement you will find as I did
>that GetDate() does no obtain Milliseconds - Why Not and can I use somethin
g
>that does ?
>
>Mark Moss
>
>SELECT CONVERT(varchar(20), GETDATE(), 113) AS Expr2,
>CONVERT(varchar(20), GETDATE(), 109) AS Expr1
>
>|||The best way to get the format you want is through an user interface. VB
converts the SQL GETDATE() with milliseconds very well.
You can use
SELECT CONVERT(varchar(20), GETDATE(), 113) + '.' + CONVERT(VARCHAR(3),
DATEPART(MS,GETDATE())) AS Expr2,
CONVERT(varchar(20), GETDATE() , 109)+ '.' + CONVERT(VARCHAR(3),
DATEPART(MS,GETDATE())) AS Expr1
and your milliseconds will show
Thanks Kllyj64
"msnews.microsoft.com" wrote:

> Ladies / Gentlemen
> If you run the following select statement you will find as I did
> that GetDate() does no obtain Milliseconds - Why Not and can I use somethi
ng
> that does ?
>
> Mark Moss
>
> SELECT CONVERT(varchar(20), GETDATE(), 113) AS Expr2,
> CONVERT(varchar(20), GETDATE(), 109) AS Expr1
>
>|||lol..or you could just change the size of your VARCHAR()....
--
Thanks Kllyj64
"kllyj64" wrote:
> The best way to get the format you want is through an user interface. VB
> converts the SQL GETDATE() with milliseconds very well.
> You can use
> SELECT CONVERT(varchar(20), GETDATE(), 113) + '.' + CONVERT(VARCHAR(3)
,
> DATEPART(MS,GETDATE())) AS Expr2,
> CONVERT(varchar(20), GETDATE() , 109)+ '.' + CONVERT(VARCHAR(3),
> DATEPART(MS,GETDATE())) AS Expr1
> and your milliseconds will show
>
> --
> Thanks Kllyj64
>
> "msnews.microsoft.com" wrote:
>|||Just increase VARCHAR to 40. 20 is too short and thus the reason why
msec is left off.
Mark
On Mon, 8 May 2006 16:19:10 -0600, "msnews.microsoft.com"
<markmoss@.adelphia.net> wrote:

>Ladies / Gentlemen
> If you run the following select statement you will find as I did
>that GetDate() does no obtain Milliseconds - Why Not and can I use somethin
g
>that does ?
>
>Mark Moss
>
>SELECT CONVERT(varchar(20), GETDATE(), 113) AS Expr2,
>CONVERT(varchar(20), GETDATE(), 109) AS Expr1
>

GETDATE

I have a database that contains, amongst others, the following fields
DocNo, DocDate, DocAmt
I need to extract the data in the these fields, but only where the date (in
yyyy-mm-dd format) is equal to the current date. In other words, I need a
schedule of documents produced on the day of running the query.
I am battling with the WHERE statement. Can anyone help please?Try this:
WHERE CONVERT(DATETIME, CONVERT(CHAR, DocDate, 105), 103) =
CONVERT(DATETIME, CONVERT(CHAR, GETDATE, 105), 103)
This statement sets the time to 00:00:00 on both the sides.
Regards,
Peri
"Chris Lane" <chris.lane@.lantic.net> wrote in message
news:dl3uul$jfc$2@.ctb-nnrp2.saix.net...
> I have a database that contains, amongst others, the following fields
> DocNo, DocDate, DocAmt
> I need to extract the data in the these fields, but only where the date
(in
> yyyy-mm-dd format) is equal to the current date. In other words, I need a
> schedule of documents produced on the day of running the query.
> I am battling with the WHERE statement. Can anyone help please?
>
>|||Thanks Peri
"Peri" <Peri@.newsgroups.nospam> wrote in message
news:OaGOLy05FHA.3296@.TK2MSFTNGP09.phx.gbl...
> Try this:
> WHERE CONVERT(DATETIME, CONVERT(CHAR, DocDate, 105), 103) =
> CONVERT(DATETIME, CONVERT(CHAR, GETDATE, 105), 103)
> This statement sets the time to 00:00:00 on both the sides.
> Regards,
> Peri
> "Chris Lane" <chris.lane@.lantic.net> wrote in message
> news:dl3uul$jfc$2@.ctb-nnrp2.saix.net...
> (in
>|||"Chris Lane" <chris.lane@.lantic.net> wrote in message
news:dl401b$lkt$1@.ctb-nnrp2.saix.net...
> Thanks Peri
> "Peri" <Peri@.newsgroups.nospam> wrote in message
> news:OaGOLy05FHA.3296@.TK2MSFTNGP09.phx.gbl...
>
Avoid putting the CONVERT on the column. Instead, an expression like the
following is more likely to make better use of any index on docdate.
...
WHERE docdate >= CONVERT(CHAR(8),CURRENT_TIMESTAMP,112)
AND docdate < CONVERT(CHAR(8),DATEADD(DAY,1,CURRENT_TI
MESTAMP),112) ;
David Portas
SQL Server MVP
--|||Thanks David
Most helpful
regards
Chris
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:kpednTVQCsA5X-jeRVnyhA@.giganews.com...
> "Chris Lane" <chris.lane@.lantic.net> wrote in message
> news:dl401b$lkt$1@.ctb-nnrp2.saix.net...
> Avoid putting the CONVERT on the column. Instead, an expression like the
> following is more likely to make better use of any index on docdate.
> ...
> WHERE docdate >= CONVERT(CHAR(8),CURRENT_TIMESTAMP,112)
> AND docdate < CONVERT(CHAR(8),DATEADD(DAY,1,CURRENT_TI
MESTAMP),112) ;
> --
> David Portas
> SQL Server MVP
> --
>

Get values from database query

I have written the following lines

myConnection =New MySqlConnection("server=" + dbServer +"; user id=" + dbUserID +"; password=" + dbPassword +"; database=" + dbName +"; pooling=false;")strSQL ="SELECT * FROM user where type=1;"

user table has name, tel, addr, id, type fields

I would like to know how to use a string array to store the name in the result of strSQL?

Thank you

Hi thtang

You can use fllowing code:

SqlCommand cmd = new SqlCommand(myConnection ,strSQL );

SqlDataReader dr = cmd.ExecuteReader();

while(dr.Read())

{

stringList.Add(dr.GetString(0));

}

Get Value to Return to VB.Net

I have the following stored procedure:
ALTER Procedure spInsert
@.UserName char(50),
@.Password char(15),
@.EmailAddress char(60),
@.TCoName char(50),
@.TCoAddress char(50),
etc.
@.UserID int OUTPUT,
@.TCoID int OUTPUT
AS
INSERT INTO tblLogin
VALUES
(
@.UserName,
@.Password,
@.EmailAddress
)
Declare @.Ident int
Select @.UserID = @.@.IDENTITY
Select @.Ident = @.UserID
INSERT INTO tblTCompany
VALUES
(
@.Ident,
@.TCoName,
@.TCoAddress,
etc.....
)
Declare @.Ident2 int
Select @.TCoID = @.@.IDENTITY
Select @.Ident2 = @.TCoID
I need to grab the @.Ident2 value into VB.Net (for a Web App). How do I get
the two applications to "talk" to each other?
Any suggestions will be greatly appreciated!
Sandy> I need to grab the @.Ident2 value into VB.Net (for a Web App).
You have a couple of options. One method is to return the value as a result
set:
SELECT @.Ident2
Another technique is to return the value as an OUTPUT parameter:
ALTER Procedure spInsert
@.UserName char(50),
@.Password char(15),
@.EmailAddress char(60),
etc.,
@.Ident2 OUT
AS
...
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Sandy" <Sandy@.discussions.microsoft.com> wrote in message
news:F826D886-345C-48DF-BE65-3725BA80E920@.microsoft.com...
>I have the following stored procedure:
> ALTER Procedure spInsert
> @.UserName char(50),
> @.Password char(15),
> @.EmailAddress char(60),
> @.TCoName char(50),
> @.TCoAddress char(50),
> etc.
> @.UserID int OUTPUT,
> @.TCoID int OUTPUT
> AS
> INSERT INTO tblLogin
> VALUES
> (
> @.UserName,
> @.Password,
> @.EmailAddress
> )
> Declare @.Ident int
> Select @.UserID = @.@.IDENTITY
> Select @.Ident = @.UserID
> INSERT INTO tblTCompany
> VALUES
> (
> @.Ident,
> @.TCoName,
> @.TCoAddress,
> etc.....
> )
> Declare @.Ident2 int
> Select @.TCoID = @.@.IDENTITY
> Select @.Ident2 = @.TCoID
> I need to grab the @.Ident2 value into VB.Net (for a Web App). How do I
> get
> the two applications to "talk" to each other?
> Any suggestions will be greatly appreciated!
> Sandy|||Hi Dan -
Thanks for your reply. How do you refer to the value in VB.Net, though?
What's the VB code you would write?
Sandy
"Dan Guzman" wrote:

> You have a couple of options. One method is to return the value as a resu
lt
> set:
> SELECT @.Ident2
> Another technique is to return the value as an OUTPUT parameter:
> ALTER Procedure spInsert
> @.UserName char(50),
> @.Password char(15),
> @.EmailAddress char(60),
> etc.,
> @.Ident2 OUT
> AS
> ...
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Sandy" <Sandy@.discussions.microsoft.com> wrote in message
> news:F826D886-345C-48DF-BE65-3725BA80E920@.microsoft.com...
>
>|||Sandy,
Here's my table definition
CREATE TABLE dbo.Report (
ReportID int IDENTITY (1, 1) NOT NULL ,
Descr varchar (50) NOT NULL ,
ReportName varchar (50) NOT NULL
)
And the associated SP. Notice the @.ReportID as OUTPUT
and the SET @.ReportID as the last statement. That
returns the identity value in the OUTPUT parameter.
---
CREATE PROCEDURE dbo.usp_Report_Ins
@.Descr varchar(50),
@.ReportName varchar(50),
@.ReportID int OUTPUT
AS
INSERT INTO dbo.Report (
Descr,
ReportName
)
VALUES (
@.Descr,
@.ReportName
)
SET @.ReportID = SCOPE_IDENTITY()
This is a snippet of the Insert code. I use the the
Microsoft Data Access Application Block to do the
SQL stuff (SqlHelper statements) to update the DB.
Use whatever code works there. The last statement
retrieves the value of the identity field.
---
Dim params() As SqlParameter = New SqlParameter(2) {}
params(0) = New SqlParameter("@.Descr", Reports.Descr)
params(1) = New SqlParameter("@.ReportName", Reports.ReportName)
params(2) = New SqlParameter("@.ReportID", Reports.ReportID)
params(2).Direction = ParameterDirection.Output
SqlHelperParameterCache.CacheParameterSet(ConnectionSettings.cnString,
_
"usp_Report_Ins", params)
Dim result As Integer =
SqlHelper.ExecuteNonQuery(ConnectionSettings.cnString, _
CommandType.StoredProcedure, "usp_Report_Ins",
params)
dim PrimaryKey as Integer = CInt(params(2).Value)
On Thu, 10 Feb 2005 07:09:02 -0800, "Sandy"
<Sandy@.discussions.microsoft.com> wrote:
>Hi Dan -
>Thanks for your reply. How do you refer to the value in VB.Net, though?
>What's the VB code you would write?
>Sandy
>
>"Dan Guzman" wrote:
>|||larzeb's example shows how you can get a parameter output value. To
retrieve a value returned as a single-row single-column result, you can use
a number of methods, such as SqlCommand.ExecuteScalar,
SqlCommandExecuteReader or SqlDataAdapter.Fill.
Hope this helps.
Dan Guzman
SQL Server MVP
"Sandy" <Sandy@.discussions.microsoft.com> wrote in message
news:EBDC9678-1001-4809-A0C5-A524115190B0@.microsoft.com...
> Hi Dan -
> Thanks for your reply. How do you refer to the value in VB.Net, though?
> What's the VB code you would write?
> Sandy
>
> "Dan Guzman" wrote:
>

Get value of MAX(ID) into a variable

Hi All,

Hope someone can help a newbie!

I have the following code:

/* Get MAXID from tblHotels_Web to form the HotelID */SqlCommand cmdGetMaxID =new SqlCommand("Select MAX(HotelID) from tblHotels_Web");int intMaxID;

How do I get the value of HotelID into my intMaxID variable and populate txtID.Text?

Regards,

Brett

see thia example:

static public int AddProductCategory(string newName, string connString){ Int32 newProdID = 0; string sql = "Select MAX(HotelID) from tblHotels_Web"; using (SqlConnection conn = new SqlConnection(connString)) { SqlCommand cmd = new SqlCommand(sql, conn); try { conn.Open(); newProdID = (Int32)cmd.ExecuteScalar(); } catch (Exception ex) { Console.WriteLine(ex.Message); } } return (int)newProdID;}

Get value from datasource in codebehind

Lets say I have a Sqldatasource that uses the following SelectCommand="SELECT category,name FROM table". How do I get the value on category from my datasource in code behind if I know that my selectcommand always will return one row? Can I write something like datasource.items["category"].Value?

Thanks for your help!

You can retrieve the value from your datasource from either dataview or datareader. Here is a sample for your reference:

You can access your SqlDataSouce from code behind through a dataview or datareader by calling select() of the SqlDatasource. If theDataSourceMode property of the SqlDatasource is set to DataSet and you get the dataview(this is default), or a DataReader if it is set to DataReader.

'Programmatically access the SqlDataSource - get back a DataView
Dim dview As DataView = CType(yourSqlDataSource.Select(DataSourceSelectArguments.Empty), DataView)

Dim str1 as string = String.Empty

For Each drow As DataRow In dview.Table.Rows

str1 &= drow("yourcol1").ToString() & "<br />"

NEXT

Or through a datareader ( don't forget to set DataSourceMode property to DataReader)

Dim myreader as SqlDataReader=CType(rndProductsDataSource.Select(DataSourceSelectArguments.Empty), SqlDataReader)

Dim str1 as string=String.Empty
if myreader.Read()
str1= reader(0) ' or your first column name

else

end if

myreader.Close()

get the rank of returned rows (was "SQL Question")

Given the following results:
col0 col1 col2
THY 2,265,850 31
VIE 1,474,994 20
RID 1,221,800 17
ACC 1,124,335 15
FEI 445,184 6
DIR 433,783 6
ROM 324,365 4

What is the best way in a query to get the rank of the returned rows by either col1 or col2. In other words who's the number 1,2,3 etc...

total count col0 = 7
total col1 = 7,290,310
total col2 (would eqaul 100%)= 99%

Looking for a mathmatical solution to this any help would be appreciated.Are you looking for row numbers or running totals? Either (or both) can be done. I assume you are order by COL1 Descending?|||Actaully either but it must be based on the totals. I'm playing with a sub-query at the moment trying to use INTENDTITY(INT,1,1) as myRanK field. Only problem is I don't have control of the resulting inner query. I guess I could use a temp table to query against but I was trying to do this in as few trips as possible.|||What does the desired outpout look like? It looks like you are after

select count(*), sum(col2), sum(col3)
from yourtable|||the desired output would be something like the following:

col0 col1 col2 col3(aka Rank)
THY 2,265,850 31 1
VIE 1,474,994 20 2
RID 1,221,800 17 3
ACC 1,124,335 15 4
FEI 445,184 6 5
DIR 433,783 6 6
ROM 324,365 4 7

So I'd have a rank based on the sum of either col1 or col2 against the totals for the group.

Right now I'm trying something like the following but having trouble controlling my returned records from the inner query:

SELECT IDENTITY (INT, 1, 1) AS rank,q.*
FROM (SELECT col0,col1,col2 FROM mytable) q
ORDER BY q.col1

not working as I'd expect. :confused:|||This is a general solution that numbers the rows of your dataset in descending order of Col0:

Select YourDataSet.col0,
YourDataSet.col1,
YourDataSet.col2,
count(SecondInstance.col0) as Rank
from YourDataSet
inner join YourDataSet SecondInstance on YourDataSet.col1 <= SecondInstance.col1
group by YourDataSet.col0,
YourDataSet.col1,
YourDataSet.col2|||SELECT q.*,IDENTITY (INT, 1, 1) AS rank
INTO db.dbo.TEST
FROM (SELECT col0,col1,SUM(CASE WHEN Date >= '01/01/2004' AND Date <= '12/31/2004' THEN someValue ELSE 0 END)
AS col2
FROM tab1 INNER JOIN
tab2 ON tab1.ID = tab2.ID
WHERE (Date >= '01/01/2004') AND (Date <= '12/31/2004')
GROUP BY col0,col1) q
ORDER BY q.col1 DESC

Better example of what I'm working with...|||hey toejam, did you try blindman's suggestion with the theta join?

FYI you guys should read IDENTITY() Function Isn't Reliable for Imposing Order on a Result Set (http://www.winnetmag.com/SQLServer/Article/ArticleID/43553/43553.html)|||r937,

I read blindmans post but I wasn't sure if it would get me the results I'm after but I'll try it.

the link you provided is very on point so I'm going to take a moment to read through it.

Thx!

:D|||see http://forums.devshed.com/t218290/s.html|||It'll get you the results you are after. It is a pretty standard solution to your class of problem.|||r937 -

DevShed (http://forums.devshed.com/showthread.php?p=944761#post944761)

Thx very much... I did it the old fashion way. I did a little VB code to get my answer but I'd prefer to do it in a query to let the db do the work. Awesome job man!

Thx a bunch

get sorted data

Hi,
I have a table containing the following data:
ID TitelD fk_Next_Knoten_ID fk_Previous_Knoten_ID
-- -- -- --
131 Kupfer, Erde 138 0
132 Flachkabel 0 141
138 Dr=E4hte, Seil 139 131
139 Niederspannu 140 138
140 Sicherheitsk 141 139
141 Litzen 132 140
How can I select the data in the correct order:
131 Kupfer, Erde 138 0
138 Dr=E4hte, Seil 139 131
139 Niederspannu 140 138
140 Sicherheitsk 141 139
141 Litzen 132 140
132 Flachkabel 0 141
Does anybody has an idea? Maybe with the help of a cursor?
Thanks,
MarcHave you tried ORDER BY clause?
<glompf@.gmail.com> wrote in message
news:1128951956.866049.261510@.o13g2000cwo.googlegroups.com...
Hi,
I have a table containing the following data:
ID TitelD fk_Next_Knoten_ID fk_Previous_Knoten_ID
-- -- -- --
131 Kupfer, Erde 138 0
132 Flachkabel 0 141
138 Drhte, Seil 139 131
139 Niederspannu 140 138
140 Sicherheitsk 141 139
141 Litzen 132 140
How can I select the data in the correct order:
131 Kupfer, Erde 138 0
138 Drhte, Seil 139 131
139 Niederspannu 140 138
140 Sicherheitsk 141 139
141 Litzen 132 140
132 Flachkabel 0 141
Does anybody has an idea? Maybe with the help of a cursor?
Thanks,
Marc|||What would you use as the order by parameter?
Marc|||Try,
...
order by fk_Previous_Knoten_ID asc
AMB
"glompf@.gmail.com" wrote:

> Hi,
> I have a table containing the following data:
> ID TitelD fk_Next_Knoten_ID fk_Previous_Knoten_ID
> -- -- -- --
> 131 Kupfer, Erde 138 0
> 132 Flachkabel 0 141
> 138 Dr?hte, Seil 139 131
> 139 Niederspannu 140 138
> 140 Sicherheitsk 141 139
> 141 Litzen 132 140
> How can I select the data in the correct order:
> 131 Kupfer, Erde 138 0
> 138 Dr?hte, Seil 139 131
> 139 Niederspannu 140 138
> 140 Sicherheitsk 141 139
> 141 Litzen 132 140
> 132 Flachkabel 0 141
> Does anybody has an idea? Maybe with the help of a cursor?
> Thanks,
> Marc
>|||If you are able to change this design then I suggest you do. Having
both next and previous IDs in the same table looks redundant. If you
can't fix the design then you may have to use a cursor.
If there is some fixed maximum depth to the hierarchy then you may be
able to achieve the same with self-joins. To answer this fully I'd like
to know if there are any branches or loops in the hierarchy. Are
Previous and Next unique? Please post DDL so that we don't have to
guess.
David Portas
SQL Server MVP
--|||create table #test
(
col1 int not null primary key,
col2 char(1),
col3 int not null
)
insert into #test values (1,'a',0)
insert into #test values (2,'b',100)
insert into #test values (3,'c',80)
insert into #test values (4,'d',90)
insert into #test values (5,'e',92)
insert into #test values (6,'f',99)
select * from #test order by col3 asc
<glompf@.gmail.com> wrote in message
news:1128953061.383573.43240@.o13g2000cwo.googlegroups.com...
> What would you use as the order by parameter?
> Marc
>|||Hi David,
Between the same TLID (=3D additional hierarchical level) the next and
prev are unique
The 'real' data looks like this:
create table #tl
(
ID int,
TLID int,
TitelD varchar(50),
fk_Next_Knoten_ID int,
fk_Previous_Knoten_ID int)
)
insert into #tl values (131,21,'Kupfer, Erder ',138,0 )
insert into #tl values (132,21,'Flachkabel ',147,141)
insert into #tl values (133,21,'Anschluss-/Verl=E4nger',134,146)
insert into #tl values (134,21,'Koaxialkabel ',136,133)
insert into #tl values (135,21,'Datenkabel ',145,137)
insert into #tl values (136,21,'Kabel T+T ',137,134)
insert into #tl values (137,21,'Dr=E4hte T+T ',135,136)
insert into #tl values (138,21,'Dr=E4hte, Seile ',139,131)
insert into #tl values (139,21,'Niederspannungskabel',140,138)
insert into #tl values (140,21,'Sicherheitskabel ',141,139)
insert into #tl values (141,21,'Litzen ',132,140)
insert into #tl values (142,21,'Steuerkabel ',143,147)
insert into #tl values (143,21,'Apparatekabel ',148,142)
insert into #tl values (144,21,'Kabel, konfektionier',146,149)
insert into #tl values (145,21,'Heizb=E4nder/-Kabel/-M',0 ,135)
insert into #tl values (146,21,'Schwachstromleiter ',133,144)
insert into #tl values (147,21,'Hochspannungskabel, ',142,132)
insert into #tl values (148,21,'Silikonkabel <Gd>, w',149,143)
insert into #tl values (149,21,'Gummischn=FCre, flexib',144,148)
insert into #tl values (150,22,'Kabelbriden ',151,0 )
insert into #tl values (151,22,'Kabelbinder, Spiralb',152,150)
insert into #tl values (152,22,'Leitungs-Schnellverl',153,151)
insert into #tl values (153,22,'Kabelendt=FCllen, Knic',159,152)
insert into #tl values (154,22,'Kabelarmaturen ',160,159)
insert into #tl values (155,22,'Kabelverschraubungen',156,157)
insert into #tl values (156,22,'EMP-Material ',158,155)
insert into #tl values (157,22,'Kabeldurchf=FChrungen ',155,160)
insert into #tl values (158,22,'Freileitungsmaterial',0 ,156)
insert into #tl values (159,22,'Schrumpfschl=E4uche ',154,153)
insert into #tl values (160,22,'Abdichtelemente ',157,154)
insert into #tl values (161,23,'Installationsrohre ',162,0 )
insert into #tl values (162,23,'Schl=E4uche, Cu-Wellro',171,161)
insert into #tl values (163,23,'Briden, Klemmbriden,',173,171)
insert into #tl values (164,23,'Schalungsmuffen ',168,167)
insert into #tl values (165,23,'Schlauchzubeh=F6r ',166,173)
insert into #tl values (166,23,'Reduktionen, Erweite',167,165)
insert into #tl values (167,23,'Dichtungszapfen ',164,166)
insert into #tl values (168,23,'Schalungsschoner ',172,164)
insert into #tl values (169,23,'Kabelkan=E4le ',170,172)
insert into #tl values (170,23,'Gummibodenleisten ',0 ,169)
insert into #tl values (171,23,'Kabelschutzrohre und',163,162)
insert into #tl values (172,23,'Rohrst=FCtzen ',169,168)
insert into #tl values (173,23,'Rohrzubeh=F6r ',165,163)
insert into #tl values (174,24,'AP-Abzweigdosen ',175,0 )
insert into #tl values (175,24,'Flachkabel-Abzweigdo',189,174)
insert into #tl values (176,24,'Anschlussdosen ',177,189)
insert into #tl values (177,24,'Bodendosen ',178,176)
insert into #tl values (178,24,'Anschlusss=E4ulen/Bohr',179,177)
insert into #tl values (179,24,'Leiterschienen ',180,178)
insert into #tl values (180,24,'UP-Abzweigdosen ',181,179)
insert into #tl values (181,24,'UP-Schalungskasten ',182,180)
insert into #tl values (182,24,'Verbindungs-/Abzweig',184,181)
insert into #tl values (183,24,'Klemmen ',188,187)
insert into #tl values (184,24,'Kabelschuhe/Konusver',185,182)
insert into #tl values (185,24,'Aderendh=FClsen ',186,184)
insert into #tl values (186,24,'Sammelschienenmateri',187,185)
insert into #tl values (187,24,'Erdungsmaterial ',183,186)
insert into #tl values (188,24,'Reihenklemmen und Zu',0 ,183)
insert into #tl values (189,24,'Rundkabel-Abzweigdos',176,175)
insert into #tl values (190,25,'Schrauben ',199,0 )
insert into #tl values (191,25,'D=FCbel, Befestigungsm',192,199)
insert into #tl values (192,25,'Abschlussrosetten ',193,191)
insert into #tl values (193,25,'Profile/Profilschien',194,192)
insert into #tl values (194,25,'Norm-Bauteile ',195,193)
insert into #tl values (195,25,'Isolier-/Dichtungsma',196,194)
insert into #tl values (196,25,'Isolierplatten ',197,195)
insert into #tl values (197,25,'Bezeichnungsmaterial',198,196)
insert into #tl values (198,25,'Warnungstafeln/Warnu',0 ,197)
insert into #tl values (199,25,'L=F6tmaterial/Klebstof',191,190)
insert into #tl values (200,26,'AP-Apparate trocken ',201,0 )
insert into #tl values (201,26,'AP-Apparate feucht ',202,200)
insert into #tl values (202,26,'AP-Apparate nass ',203,201)
insert into #tl values (203,26,'AP-Apparate explosio',204,202)
insert into #tl values (204,26,'Fusschalter/-taster,',206,203)
insert into #tl values (205,26,'Schnur-Zwischenschal',0 ,206)
insert into #tl values (206,26,'Endtaster/Endschalte',205,204)
insert into #tl values (207,27,'Einlassk=E4sten ',208,0 )
insert into #tl values (208,27,'Abdeckungen/Blindabd',209,207)
insert into #tl values (209,27,'Befestigungsplatten ',210,208)
insert into #tl values (210,27,'AP-Rahmen ',211,209)
insert into #tl values (211,27,'UP-Apparate trocken ',212,210)
insert into #tl values (212,27,'UP-Apparate feucht ',213,211)
insert into #tl values (213,27,'UP-Apparate nass ',0 ,212)
insert into #tl values (214,28,'EB-Apparate ',215,0 )
insert into #tl values (215,28,'EB-Apparate feucht ',217,214)
insert into #tl values (216,28,'EB-Befehls- und Meld',0 ,218)
insert into #tl values (217,28,'EB-Apparate nass ',218,215)
insert into #tl values (218,28,'EB-Lasttrennschalter',216,217)
insert into #tl values (219,29,'Leistungs-/Motorschu',238,0 )
insert into #tl values (220,29,'Relais ',221,237)
insert into #tl values (221,29,'Sch=FCtze/Hilfssch=FCtze',222,220)
insert into #tl values (222,29,'Softstart f=FCr Drehst',244,221)
insert into #tl values (223,29,'Zeitschalter ',224,246)
insert into #tl values (224,29,'Verz=F6gerungs-/Ventil',239,223)
insert into #tl values (225,29,'Vorwahlz=E4hler ',241,239)
insert into #tl values (226,29,'Schaltuhren/Steckdos',227,240)
insert into #tl values (227,29,'D=E4mmerungsschalter ',228,226)
insert into #tl values (228,29,'Pr=E4senzmelder/Bewegu',229,227)
insert into #tl values (229,29,'Lichtschranken/Konta',230,228)
insert into #tl values (230,29,'Thermostaten/Hygrost',231,229)
insert into #tl values (231,29,'Druck-/Niveauregler ',232,230)
insert into #tl values (232,29,'=DCberwachungsger=E4te/B',243,231)
insert into #tl values (233,29,'Netzfreischalter ',0 ,234)
insert into #tl values (234,29,'Kondensatoren ',233,236)
insert into #tl values (235,29,'Schrittschalter ',237,238)
insert into #tl values (236,29,'Lastabwurfrelais ',234,245)
insert into #tl values (237,29,'Treppenhausautomaten',220,235)
insert into #tl values (238,29,'Leistungsschalter ',235,219)
insert into #tl values (239,29,'Betriebsstundenz=E4hle',225,224
)
insert into #tl values (240,29,'Messinstrumente ',226,241)
insert into #tl values (241,29,'Energieverbrauchsz=E4h',240,225
)
insert into #tl values (242,29,'Steuerungsmodule ',245,243)
insert into #tl values (243,29,'Storensteuerung ',242,232)
insert into #tl values (244,29,'Frequenzumrichter ',246,222)
insert into #tl values (245,29,'Kontrollrelais ',236,242)
insert into #tl values (246,29,'Antriebe ',223,244)
insert into #tl values (247,30,'IR-Steuertechnik ',248,0 )
insert into #tl values (248,30,'Geb=E4udesystemtechnik',0 ,247)
insert into #tl values (249,31,'AP-Steckdosen, trock',250,0 )
insert into #tl values (250,31,'AP-Fehlerstromschutz',251,249)
insert into #tl values (251,31,'AP-Steckdosen, feuch',252,250)
insert into #tl values (252,31,'AP-Steckdosen, nass ',253,251)
insert into #tl values (253,31,'AP-Fehlerstromschutz',0 ,252)
insert into #tl values (254,32,'UP-Steckdosen, trock',258,0 )
insert into #tl values (255,32,'UP-Fehlerstromschutz',0 ,257)
insert into #tl values (256,32,'UP-Steckdosen, feuch',257,258)
insert into #tl values (257,32,'UP-Steckdosen, nass ',255,256)
insert into #tl values (258,32,'UP-Fehlerstromschutz',256,254)
insert into #tl values (259,33,'EB-Steckdosen, trock',261,0 )
insert into #tl values (260,33,'EB-Steckdosen, feuch',0 ,261)
insert into #tl values (261,33,'EB-Fehlerstromschutz',260,259)
insert into #tl values (262,34,'Mehrfach-Stecker ',263,265)
insert into #tl values (263,34,'Fehlerstromschutzste',264,262)
insert into #tl values (264,34,'Apparatestecker ',0 ,263)
insert into #tl values (265,34,'Stecker ',262,0 )
insert into #tl values (266,35,'Industriesteckkontak',267,0 )
insert into #tl values (267,35,'Industrie-Steckkonta',268,266)
insert into #tl values (268,35,'Industrie-Steckkonta',0 ,267)
insert into #tl values (269,36,'Sicherungsmaterial (',270,0 )
insert into #tl values (270,36,'Leitungsschutzschalt',271,269)
insert into #tl values (271,36,'Sicherungselemente ',272,270)
insert into #tl values (272,36,'Kleinverteiler ',273,271)
insert into #tl values (273,36,'Aufbautableau/Z=E4hler',274,272)
insert into #tl values (274,36,'Hausanschlusskasten/',277,273)
insert into #tl values (275,36,'Apparategeh=E4use/Vert',276,277)
insert into #tl values (276,36,'Stromverteiler ',278,275)
insert into #tl values (277,36,'Z=E4hlerkasten/Zivilsc',275,274)
insert into #tl values (278,36,'NH-Sicherungsmateria',0 ,276)
insert into #tl values (279,37,'Kochplatten ',280,0 )
insert into #tl values (280,37,'Heizger=E4te ',282,279)
insert into #tl values (281,37,'Ventilatoren ',0 ,282)
insert into #tl values (282,37,'Heisswasserger=E4te ',281,280)
insert into #tl values (283,38,'Fluoreszenzarmaturen',284,0 )
insert into #tl values (284,38,'Vorschaltger=E4te/Star',285,283)
insert into #tl values (285,38,'Beleuchtungssysteme,',286,284)
insert into #tl values (286,38,'Gl=FChlampenarmaturen,',289,285
)
insert into #tl values (287,38,'Fassungen ',288,289)
insert into #tl values (288,38,'Lichtketten/Lichtsch',0 ,287)
insert into #tl values (289,38,'Notbeleuchtungen ',287,286)
insert into #tl values (290,39,'Gl=FChlampen/Halogengl',292,291)
insert into #tl values (291,39,'Fluoreszenzlampen ',290,0 )
insert into #tl values (292,39,'Entladungslampen ',293,290)
insert into #tl values (293,39,'LED-Lampen ',0 ,292)
insert into #tl values (294,40,'Stab-/Taschen-/Handl',295,0 )
insert into #tl values (295,40,'Elemente/Batterien/A',296,294)
insert into #tl values (296,40,'Schwachstromgl=FChlamp',297,295
)
insert into #tl values (297,40,'Transformatoren, Spe',298,296)
insert into #tl values (298,40,'L=E4utwerke/Summer/Hup',307,297)
insert into #tl values (299,40,'Besuchsanzeiger ',300,306)
insert into #tl values (300,40,'Sonnerietaster/Schal',301,299)
insert into #tl values (301,40,'Tasterplatten/Haust=FC',302,300)
insert into #tl values (302,40,'Steckdosen ',309,301)
insert into #tl values (303,40,'Buchsen/Klemmen/Kupp',304,309)
insert into #tl values (304,40,'T=FCr=F6ffner/T=FCr=F6ffnera',305,303)
insert into #tl values (305,40,'Solar-Material Syste',0 ,304)
insert into #tl values (306,40,'Drehspiegelleuchten,',299,308)
insert into #tl values (307,40,'Alarmsysteme ',308,298)
insert into #tl values (308,40,'=DCberwachungssysteme ',306,307)
insert into #tl values (309,40,'R=FCckwegtaugliche Kab',303,302)
insert into #tl values (310,41,'Verbindungsmuffen/Sp',311,0 )
insert into #tl values (311,41,'Montagezubeh=F6r ',312,310)
insert into #tl values (312,41,'Durchgangsdosen/Klem',313,311)
insert into #tl values (313,41,'Amtsverteilerkasten ',314,312)
insert into #tl values (314,41,'Hauptverteiler/Verte',315,313)
insert into #tl values (315,41,'Verbindungskasten ',316,314)
insert into #tl values (316,41,'Anschluss- und Durch',317,315)
insert into #tl values (317,41,'Steck- und Anschluss',322,316)
insert into #tl values (318,41,'Grobsicherungen, Tel',341,332)
insert into #tl values (319,41,'Stromsparschalter ',320,328)
insert into #tl values (320,41,'Teilnehmervermittlun',321,319)
insert into #tl values (321,41,'Telekommunikationsge',344,320)
insert into #tl values (322,41,'Anschlussmaterial RJ',330,317)
insert into #tl values (323,41,'Anschlussmaterial <L',337,338)
insert into #tl values (324,41,'Anschlussmaterial <T',325,337)
insert into #tl values (325,41,'Anschlussdosen <Thor',326,324)
insert into #tl values (326,41,'Anschlussmaterial <D',327,325)
insert into #tl values (327,41,'Verteilkasten ',340,326)
insert into #tl values (328,41,'USV-Anlagen ',319,340)
insert into #tl values (329,41,'Anschlussmaterial IS',343,342)
insert into #tl values (330,41,'Anschlussmaterial <I',332,322)
insert into #tl values (331,41,'Anschlussmaterial <P',346,351)
insert into #tl values (332,41,'Stecker <T+T 83>, <F',318,330)
insert into #tl values (333,41,'Anschlussmaterial Gl',334,352)
insert into #tl values (334,41,'Aktiv-Komponenten ',350,333)
insert into #tl values (335,41,'Anschlussmaterial <B',338,350)
insert into #tl values (336,41,'Anschlussysteme UGV ',348,347)
insert into #tl values (337,41,'Anschlussmaterial <F',324,323)
insert into #tl values (338,41,'Anschlussmaterial <A',323,335)
insert into #tl values (339,41,'Anschlussmaterial IS',351,343)
insert into #tl values (340,41,'Schutzger=E4te <Dehn>,',328,327)
insert into #tl values (341,41,'Anschlussmaterial IS',342,318)
insert into #tl values (342,41,'Anschlussmaterial IS',329,341)
insert into #tl values (343,41,'Anschlussmaterial IS',339,329)
insert into #tl values (344,41,'Messger=E4te ',0 ,321)
insert into #tl values (345,41,'Anschlussmaterial UG',353,349)
insert into #tl values (346,41,'Anschlussmaterial UG',347,331)
insert into #tl values (347,41,'Anschlussmaterial UG',336,346)
insert into #tl values (348,41,'Anschlussmaterial UG',349,336)
insert into #tl values (349,41,'Anschlussmaterial UG',345,348)
insert into #tl values (350,41,'Durchschaltdosen ',335,334)
insert into #tl values (351,41,'Anschlussmaterial AD',331,339)
insert into #tl values (352,41,'Anschlussmaterial <H',333,353)
insert into #tl values (353,41,'Anschlussmaterial UG',352,345)
insert into #tl values (354,42,'Werkzeuge f=FCr Telefo',361,362)
insert into #tl values (355,42,'Werkzeugtaschen, Wer',356,361)
insert into #tl values (356,42,'Planschutztaschen/Sc',357,355)
insert into #tl values (357,42,'Lager- und Transport',358,356)
insert into #tl values (358,42,'Schutzvorrichtungen ',359,357)
insert into #tl values (359,42,'Kabelbox, Kabelrolle',360,358)
insert into #tl values (360,42,'Messinstrumente ',0 ,359)
insert into #tl values (361,42,'Pr=FCfger=E4te ',355,354)
insert into #tl values (362,42,'Werkzeuge ',354,0 )
Regards,
Marc|||Fistly, design issues. Are you sure this is the real data and table
structure? All your columns are nullable, there is no primary key and
despite what you say there are no UNIQUE constraints either. If this is
accurate then you should redesign.
Also, "fk_Next_Knoten_ID" is a poor name for a column that is a foreign
key, but it's an even WORSE name for a column that is NOT a foreign
key, and yours appear not to be - no referential integrity is enforced
for the non-existent "0" row.
I'm guessing this is a more accurate representation:
CREATE TABLE tl
(
id INTEGER NOT NULL PRIMARY KEY,
tlid INTEGER NOT NULL,
titeld VARCHAR(50) NOT NULL,
next_knoten_id INTEGER NOT NULL,
/* NOT A FOREIGN KEY !!! */
previous_knoten_id INTEGER NOT NULL,
/* NOT A FOREIGN KEY!!! - also redundant */
UNIQUE (tlid, next_knoten_id),
UNIQUE (tlid, previous_knoten_id)
)
As there are no branches or loops, why not drop the redundant prev/next
pair of columns and use a sequence column instead? This is apparently
nothing more than an ordered list. What is the reason to create the
self-referencing columns?
As an ordered list, you would just need:
..
ORDER BY sequence_no
To order your present design as if it were a hierarchy you'll really
have to use a cursor. See Books Online "Expanding Hierarchies" for an
example.
David Portas
SQL Server MVP
--