Is user logged-on?

Dear Group

I wondered whether there's a function or script that will show me
whether a user is currently logged-on to a MSSQL 2000 database? I'm
using SQL Authentication.

Thanks very much for your help & efforts!
Have a nice day!

MartinYou can use sp_who or sp_who2, or if you need something from code then
a statement like this will work:

if exists (select * from master.dbo.sysprocesses
where loginame = 'MyLogin'
and dbid = db_id('MyDB'))
begin
/* Do something here */
end

Simon|||Hi Simon!

Thanks for the message. Works great!

M

Is this an efficient way to return a comma string

Hi there,
I have created a sp and function that returns amongst other things a
comma seperated string of values via a one to many relationship, the
code works perfectly but i am not sure how to test its performance.. Is
this an efficient way to achieve my solution.. If not any suggestions
how i can improve it.. What are the best ways to check query speed?
MY SP:
CREATE PROCEDURE sp_Jobs_GetJobs
AS
BEGIN
SELECT j.Id, j.Inserted, Title, Reference, dbo.fn_GetJobLocations(j.id)
AS location, salary, summary, logo
FROM Jobs_Jobs j INNER JOIN Client c ON j.ClientID = c.id
ORDER BY j.Inserted DESC
END
GO
---
MY Function:
CREATE FUNCTION fn_GetJobLocations (@.JobID int)
RETURNS varchar(5000) AS
BEGIN
DECLARE @.LocList varchar(5000)
SELECT @.LocList = COALESCE(@.LocList + ', ','') + ll.location_name
FROM Jobs_Locations l inner join List_Locations ll on
ll.LocationID = l.LocationID
WHERE l.JobID = @.JobID
RETURN @.LocList
END
Any help or guidance much appreciated...First of all, what you have in your UDF is a unsupported construct. It
exploits certain physical behaviours that might seem to work in some cases,
but can fail in a variety of situations. Being undocumented, it can change
between versions, service packs or patches.
Doing this in SQL Server invariably requires some level of looping, either
using a cursor, WHILE loop, recursion etc. In SQL 2005, there are some work
arounds using FOR XML method which in some cases can be complex and error
prone.
A good approach is to retrieve the resultset to the client side and generate
the string you need to create.
Also, just noted that you use sp_ prefix to your procedure which is not at
all recommended, since they are reserved for system procedures and can
affect performance adversely.
Anith|||3rd time tonight i've posted this solution, interesting :).
Anyway, something like this (SQL Server 2005) will do the trick and will
perform blisteringly...
select j.Id, j.Inserted, Title, Reference,
(
select location_name + ',' as [text()]
from Jobs_Locations soi
where soi.Job_ID = t.Job_ID
order by location_name
for xml path( '' ), type
)
from Jobs_Jobs as j
It will give one line per job and concatenating each location seperating
them by commas.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
<anthonykallay@.hotmail.com> wrote in message
news:1133804859.768819.33930@.g14g2000cwa.googlegroups.com...
> Hi there,
>
> I have created a sp and function that returns amongst other things a
> comma seperated string of values via a one to many relationship, the
> code works perfectly but i am not sure how to test its performance.. Is
> this an efficient way to achieve my solution.. If not any suggestions
> how i can improve it.. What are the best ways to check query speed?
>
> MY SP:
> CREATE PROCEDURE sp_Jobs_GetJobs
> AS
> BEGIN
> SELECT j.Id, j.Inserted, Title, Reference, dbo.fn_GetJobLocations(j.id)
> AS location, salary, summary, logo
> FROM Jobs_Jobs j INNER JOIN Client c ON j.ClientID = c.id
> ORDER BY j.Inserted DESC
>
> END
> GO
> ---
> MY Function:
> CREATE FUNCTION fn_GetJobLocations (@.JobID int)
>
> RETURNS varchar(5000) AS
> BEGIN
> DECLARE @.LocList varchar(5000)
> SELECT @.LocList = COALESCE(@.LocList + ', ','') + ll.location_name
> FROM Jobs_Locations l inner join List_Locations ll on
> ll.LocationID = l.LocationID
> WHERE l.JobID = @.JobID
> RETURN @.LocList
>
> END
>
> Any help or guidance much appreciated...
>

Is this a bug, CURSOR_STATUS() always return -3 ?

Hi,
With this simple test, CURSOR_STATUS() function always return -3
use Northwind
go
if object_id('dbo.TestCursor') is not null
drop proc dbo.TestCursor
go
create proc dbo.TestCursor
as
declare @.ContactName varchar(50)
declare My_Curs cursor
fast_forward
for
select ContactName from dbo.Customers
open My_Curs
fetch next from My_Curs into @.ContactName
select @.ContactName as ContactName
select
CURSOR_STATUS('variable', 'My_Curs') as CursStatvariable,
CURSOR_STATUS('local', 'My_Curs_Curs') as CursStatlocal,
CURSOR_STATUS('variable', 'My_Curs_Curs') as CursStatvariable
close My_Curs
deallocate My_Curs
select
CURSOR_STATUS('variable', 'My_Curs') as CursStatvariable,
CURSOR_STATUS('local', 'My_Curs_Curs') as CursStatlocal,
CURSOR_STATUS('variable', 'My_Curs_Curs') as CursStatvariable
go
exec dbo.TestCursor
go
I have 02 questions
1. Is this is a bug with CURSOR_STATUS() function ?
2. If the SP fails in the middle and close / deallocate are not executed,
will SQL Server close and dealocate the resources of us or the next time the
SP is execute it will throw 'A cursor with the name ... already exists' ?
According to my tests SQL Server close and dealocate the cursor automaticaly
Thak you for your helpOn Fri, 30 Sep 2005 13:35:15 -0700, S.M wrote:

>Hi,
>With this simple test, CURSOR_STATUS() function always return -3
(snip)
>I have 02 questions
>1. Is this is a bug with CURSOR_STATUS() function ?
Hi S.M.,
No. Try changing your SELECT statement (at both locations) to
select
CURSOR_STATUS('local', 'My_Curs') as CursStatlocal,
CURSOR_STATUS('global', 'My_Curs') as CursStatglobal,
CURSOR_STATUS('variable', 'My_Curs') as CursStatvariable
(That is - change the names, AND include a query for global cursor).
>2. If the SP fails in the middle and close / deallocate are not executed,
>will SQL Server close and dealocate the resources of us or the next time th
e
>SP is execute it will throw 'A cursor with the name ... already exists' ?
>According to my tests SQL Server close and dealocate the cursor automaticaly[/color
]
You didn't test well, then. Include the following extra line after the
forst select but before the close command in the stored proc:
SELECT a FROM b
This won't generate an error when creating the proc, but will generate
an error when executing it. Now execute the proc twice in a row.
Results:
(first execution)
ContactName
----
Maria Anders
CursStatlocal CursStatglobal CursStatvariable
-- -- --
-3 1 -3
Server: Msg 208, Level 16, State 1, Procedure TestCursor, Line 22
Invalid object name 'B'.
(second execution)
Server: Msg 16915, Level 16, State 1, Procedure TestCursor, Line 10
A cursor with the name 'My_Curs' already exists.
Server: Msg 16905, Level 16, State 1, Procedure TestCursor, Line 12
The cursor is already open.
ContactName
----
Ana Trujillo
CursStatlocal CursStatglobal CursStatvariable
-- -- --
-3 1 -3
Server: Msg 208, Level 16, State 1, Procedure TestCursor, Line 22
Invalid object name 'B'.
Of course, when you declare the cursor to be local, it WILL be closed
and deallocated when the SP fails, since it goes out of scope.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

is there?

Is any more function available in KATMAI, to know the OS version of the DB server ?

How about xp_msver? -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi ""Select 'Siva'"@.discussions.microsoft.com" <"=?UTF-8?B?U2VsZWN0ICdTaXZhJw==?="@.discussions.microsoft.com> wrote in message news:322b3b89-0f96-4d93-8c16-7b9a6c6feb1c_WBRev1_@.discussions.microsoft.com... > This post has been edited either by the author or a moderator in the > Microsoft Forums: http://forums.microsoft.com > > > Is any more function available in KATMAI, to know the OS version of the > DB server ? > >|||

Fine,

But In this extended sp, there is no detailed information available like "Windows professional 2003 sp2".

That's wat am asked?

|||

You can use the @.@.VERSION function to get the version, processor architecture, build date, and operating system for the current installation of SQL Server.

is there?

Is any more function available in KATMAI, to know the OS version of the DB server ?

How about xp_msver? -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi ""Select 'Siva'"@.discussions.microsoft.com" <"=?UTF-8?B?U2VsZWN0ICdTaXZhJw==?="@.discussions.microsoft.com> wrote in message news:322b3b89-0f96-4d93-8c16-7b9a6c6feb1c_WBRev1_@.discussions.microsoft.com... > This post has been edited either by the author or a moderator in the > Microsoft Forums: http://forums.microsoft.com > > > Is any more function available in KATMAI, to know the OS version of the > DB server ? > >|||

Fine,

But In this extended sp, there is no detailed information available like "Windows professional 2003 sp2".

That's wat am asked?

|||

You can use the @.@.VERSION function to get the version, processor architecture, build date, and operating system for the current installation of SQL Server.

Is there any way to insert image into DB without coding ?

I need just testing function that retreive image from SQL SERVER 2005 with data type "image" so i need to know another way to insert image into SQL SERVER2005. Because In MICROSOFT ACCESS, I just copy image and then, paste into column.

See my response to your identical question posted in the [SQL Server Database Engine] forum.

Often, the quality of the responses received is related to our ability to ‘bounce’ ideas off of each other. In the future, to make it easier for us to offer you assistance, and to prevent folks from wasting time on already answered questions, please don't post to multiple newsgroups. Choose the one that best fits your question and post there. Only post to another newsgroup if you get no answer in a day or two (or if you accidentally posted to the wrong newsgroup –and you indicate that you've already posted elsewhere).

Is there any way to insert image into DB without coding ?

I need just testing function that retreive image from SQL SERVER 2005 with data type "image" so i need to know another way to insert image into SQL SERVER2005 without coding. Because In MICROSOFT ACCESS, I just copy image and then, paste into column.

Microsoft Access is a client (front-end) application that happens to also work with data storage.

SQL Server is a server (back-end) application that requires a client to move data in and out.

So the short answer is No. However, there are some client applications that do not appear to require 'coding' since it is done 'beneath the covers' for you.

You can connect Access to a SQL Server database, and then use your skills with Access to move data into and out of SQL Server.

|||

It is typically a better practice to store the images elsewhere and store a reference to the image in a database; give a look here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1010368&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=362808&SiteID=1

(In a short while I will delete the other post that duplicates this one.)

As always, Arnie, thank you for your help with all of this.

Is there any performance differences between function type IF and TF?

I could not found more references in BOA about functions that return table.
Does anybody know if there is any performance differences between these two
type of functions?
IF = Inlined table-function
TF = Table function
Thanks,
Lijun
Lijun Zhang (nospam@.nospam.nospam) writes:
> I could not found more references in BOA about functions that return
> table. Does anybody know if there is any performance differences between
> these two type of functions?
> IF = Inlined table-function
> TF = Table function
Yes, there is.
An inline function is in fact not a function at all; it is a macro. The
optimiser pastes the text of the function into the query and optimizes
the result.
A multi-step function returns data to a table variable, and the result
of the function is opaque to the optimizer.
Thus, in the former case the optimizer have more information, and
thus better possibilities to create a better execution plan.
But sometimes it can be too much information, so in fact it leads
to poorer performance. But in the long run, inline functions will
give you better performance.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||Yes, there may be a significant difference, although of course that
depends on exactly what you are doing.
Despite the similar syntax *inline* table-valued functions are
implemented very differently from *multi-statement* table-valued
functions.
A inline table-valued function consists of a single query that works
very like a view. That is, when the function is referenced in another
query the SQL from both the calling query and the function itself is
considered together so as to arrive at an optimal execution plan.
With a multi-statement table-valued function that kind of optimization
isn't possible. In a multi-statement function the function code is
executed more like a stored procedure and then a result returned to the
calling code for further processing.
If you want to encapsulate a single query in a function then use an
inline TVF, or use a view.
If you need to put multiple statements in a function then you'll have
to use a multi-statement TVF.
David Portas
SQL Server MVP

Is there any performance differences between function type IF and TF?

I could not found more references in BOA about functions that return table.
Does anybody know if there is any performance differences between these two
type of functions?
IF = Inlined table-function
TF = Table function
Thanks,
LijunLijun Zhang (nospam@.nospam.nospam) writes:
> I could not found more references in BOA about functions that return
> table. Does anybody know if there is any performance differences between
> these two type of functions?
> IF = Inlined table-function
> TF = Table function
Yes, there is.
An inline function is in fact not a function at all; it is a macro. The
optimiser pastes the text of the function into the query and optimizes
the result.
A multi-step function returns data to a table variable, and the result
of the function is opaque to the optimizer.
Thus, in the former case the optimizer have more information, and
thus better possibilities to create a better execution plan.
But sometimes it can be too much information, so in fact it leads
to poorer performance. But in the long run, inline functions will
give you better performance.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Yes, there may be a significant difference, although of course that
depends on exactly what you are doing.
Despite the similar syntax *inline* table-valued functions are
implemented very differently from *multi-statement* table-valued
functions.
A inline table-valued function consists of a single query that works
very like a view. That is, when the function is referenced in another
query the SQL from both the calling query and the function itself is
considered together so as to arrive at an optimal execution plan.
With a multi-statement table-valued function that kind of optimization
isn't possible. In a multi-statement function the function code is
executed more like a stored procedure and then a result returned to the
calling code for further processing.
If you want to encapsulate a single query in a function then use an
inline TVF, or use a view.
If you need to put multiple statements in a function then you'll have
to use a multi-statement TVF.
David Portas
SQL Server MVP
--

Is there any performance differences between function type IF and TF?

I could not found more references in BOA about functions that return table.
Does anybody know if there is any performance differences between these two
type of functions?
IF = Inlined table-function
TF = Table function
Thanks,
LijunLijun Zhang (nospam@.nospam.nospam) writes:
> I could not found more references in BOA about functions that return
> table. Does anybody know if there is any performance differences between
> these two type of functions?
> IF = Inlined table-function
> TF = Table function
Yes, there is.
An inline function is in fact not a function at all; it is a macro. The
optimiser pastes the text of the function into the query and optimizes
the result.
A multi-step function returns data to a table variable, and the result
of the function is opaque to the optimizer.
Thus, in the former case the optimizer have more information, and
thus better possibilities to create a better execution plan.
But sometimes it can be too much information, so in fact it leads
to poorer performance. But in the long run, inline functions will
give you better performance.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Yes, there may be a significant difference, although of course that
depends on exactly what you are doing.
Despite the similar syntax *inline* table-valued functions are
implemented very differently from *multi-statement* table-valued
functions.
A inline table-valued function consists of a single query that works
very like a view. That is, when the function is referenced in another
query the SQL from both the calling query and the function itself is
considered together so as to arrive at an optimal execution plan.
With a multi-statement table-valued function that kind of optimization
isn't possible. In a multi-statement function the function code is
executed more like a stored procedure and then a result returned to the
calling code for further processing.
If you want to encapsulate a single query in a function then use an
inline TVF, or use a view.
If you need to put multiple statements in a function then you'll have
to use a multi-statement TVF.
--
David Portas
SQL Server MVP
--

is there any IsString() function in vb.net?

hi

i want to check that only string in textbox. the textbox shoould enterd the strings so is there IsString() function in vb.net

plz help me....

Hi,
first of all your subject does say nothing about your problem, so many people won't read it..
secondly try to use the forum search, your problem has been discusses many times..
for example:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=432801&SiteID=1

Is there any function to check the named tag is exist or not?

Is there any function to check the named tag is exist or not?Date: Fri, 06 Jul 2007 13:30:40 +0200
References: <OedTiS4vHHA.3508@.TK2MSFTNGP03.phx.gbl>
Lines: 1
Reply-To: Martin.Honnen@.gmx.de
Organization: Liberty Development
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.0.7) Geck
o/20060910 SeaMonkey/1.0.5
MIME-Version: 1.0
In-Reply-To: <OedTiS4vHHA.3508@.TK2MSFTNGP03.phx.gbl>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: i577ADC0F.versanet.de 87.122.220.15
Xref: leafnode.mcse.ms microsoft.public.sqlserver.xml:2208
ABC wrote:
> Is there any function to check the named tag is exist or not?
Like this, using the method 'exist' of the xml data type:
DECLARE @.x xml;
SET @.x = '<root><foo></foo></root>';
SELECT @.x.exist('//bar') AS barTest, @.x.exist('//foo') AS fooTest;
If that is not what you are looking for then explain in more detail what
you want to achieve.
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/

is there any editable parameters?

i have written a function in report properties, but error returns when i
tried to add 1 to a report parameter, saying that parameter.value is
read-only.
anyways to have editable parameters'
thanks in advance~I have asked almost the same question a few days ago. Apparently no one can
tell us if this can be done.
"Jasonymk" wrote:
> i have written a function in report properties, but error returns when i
> tried to add 1 to a report parameter, saying that parameter.value is
> read-only.
> anyways to have editable parameters'
> thanks in advance~

Is there any date function to get the last date of a month ?

Is there any date function available to get the last date of a month.
If not , How can I get it ?Hi, Sha
Use one of the following functions:
CREATE FUNCTION dbo.LastDayOfTheMonth(@.TheDate datetime)
RETURNS datetime AS
BEGIN
RETURN DATEADD(month, DATEDIFF(month, 0, @.TheDate) + 1, 0) - 1
END
CREATE FUNCTION dbo.LastDayOfTheMonth(@.TheDate datetime)
RETURNS datetime AS
BEGIN
RETURN DATEADD(month, 1, CONVERT(varchar(6), @.TheDate, 112) + '01') - 1
END

Is there an MS SQL Limit function?

MySQL has a convenient syntax for paging data that looks like this:
SELECT * FROM MyTable LIMIT 10, 20
That would select 10 records, starting from record 20, so that it returns records 20 - 30. This is convenient way to page data, without returning anymore rows than than you need.

However, MS SQL doesn't appear to support that syntax. What is the equivalent sql code to select any N rows from an arbitrary starting point, without having to create a stored procedure?

Thanks in advance :)it's a fiasco

SQL Server has the TOP keyword, but it takes only one parameter

see this article -- http://rosca.net/writing/articles/serverside_paging.asp|||How can is start at row 20 when you have not specified an ORDER BY clause?|||How can is start at row 20 when you have not specified an ORDER BY clause?

You can't

Read here

http://weblogs.sqlteam.com/jeffs/category/162.aspx|||http://weblogs.sqlteam.com/jeffs/category/162.aspxjeepers, i took a look at one of the two articles posted there, and boy, that sql is inefficient

brett, did you read the article i posted?|||Which one? I thought the server side paging was pretty good...|||Andrew's code is very,,,need to compare the 2|||i originally read the second one, and it has issues

i just now went back and read the first one, and all it is is a dynamic-ization of the second one

i remain unimpressed

now, did you read the article i posted?|||Yes I did, and it's elegant...but I'd have to test it for performace against some major tables

Is there an IN( ) function?

Is there and IN or and InList function in Reporting services? I'm trying to do some conditional formatting and it would be really handy to be able to write an expression such as =iif(Fields!blah.Value In(X,Y,Z),"Red","White")

Hi,

There is no IN function. I have been using OR functions for this

|||

Hi,

If your using RS 2005 then there is an IN function in Report Builder, just not in BIDS. I've used nested if statements to get around this problem when designing reports.

Cheers.

Dan.

Is there an equivalent Replicate() function in Access

Hi,

I am trying to build a string from a datetime datetype and want to make sure that if a day or month is only one digit long then it is padded with a leading 0.

In SQL I can do it like this;

declare @.datetime datetime

select @.datetime = getdate()

select 'TPR'

+ replicate('0', 2 - len(datepart(dd,@.datetime))) + convert(varchar(2),datepart(dd,@.datetime))

+ replicate('0', 2 - len(datepart(mm,@.datetime))) + convert(varchar(2),datepart(mm,@.datetime))

+ convert(varchar(4),datepart(yyyy,@.datetime))

Do you know how I can do the same thing in Access?

Thanks for your help

It would be SO-O-O-O much easier to do it in this manner:

SELECT right( '00' + cast( datepart( day, @.DateTime ) AS varchar(2) ), 2 )

(Should work in Access AND SQL Server.)

But really, wouldn't something like this be a better solution: (this is SQL)

SELECT ( 'TPR' + SELECT replace( convert( varchar(10), @.DateTime, 101 ), '/', '' ))

For Access, you would have to use the FORMAT function instead of convert. Refer to the help for FORMAT().

|||

Yes Buddy.. You have it..

STRING(<number>, <Char>)

example:

String(5,"M") => MMMMM