return current date

Hi,

Can I write a code in SQL that return the current date? If so, how?

Thanks!

WillYou can use the GETDATE() function:

SELECT GETDATE() AS CurrentDateTime

And you can use the CONVERT function to format that datetime value.

Terri|||Convert(varchar(10),GetDate(),101)

returns the date as:

01/01/2004

Retrieving IP address of current client's connection

MS SQL 2000:

I can retrieve "host" name of the current connection from "sysprocesses" system table(select host from sysprocesses). But is it possible to get the IP address of the client connection instead of host?

I could not find any stored procedures or extended procedures that would let me retrieve such information.

thank you in advance.

Hi,

there is no such call AFAIK. But you can use the follwing procedure to evaluate your IPAdress:

CREATE PROCEDURE getIPAdress

(

@.Hostname VARCHAR(255)

)

AS

SET NOCOUNT ON

CREATE TABLE #Results

(

Results VARCHAR(4000)

)

DECLARE @.Commandstring VARCHAR(300)

SET @.Commandstring = 'ping ' + @.Hostname

INSERT INTO #Results

EXEC master..xp_cmdshell @.Commandstring

Select DISTINCT SUBSTRING(Results,12,CHARINDEX(':',Results)-12) AS HostIpAdress from #Results

Where Results LIKE 'Reply From%'

DROP TABLE #Results

GO

In SQL Server 2005, you can sure use a more sophisticated method of .NET.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||Thank you for the response.
I had tried the method and it works.
But I do not think I should be trying to retrieve IP address for now just because of the fact that I have to run "xp_cmdshell".
|||

IN 2005 you can try:

select * from sys.dm_exec_connections

This will give you the IP Address of any TCP/IP connections.

Louis

|||

Thank you for also giving the solution for SQL Server 2005.

I am downloading and about to give SQL Server Express 2005 a try just for that functionality.

Great opportunity to move toward SQL Server 2005 if it works out :)

*** EDIT ***

I have installed SQL Server 2005 and tried out the query and it worked as I wanted. :)

Retrieve Values from SQL Server into Winforms

Hi all,

I want to insert information like SQL Server Version, current sql server user etc into a form, How will I achieve this? I have followed this question at http://www.vbforums.com/showthread.php?t=357605 ,but I havent had an answer to my question yet. Please help, I am stuck and can't go on with my application until I have figured this out.

Thanks alot in advance

Rudi Groenewald

You can use various SQL Server Functions for this (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_7oqb.asp).

Here's an example:

select serverproperty('ProductVersion') as ProductVersion
serverproperty('Edition') as Edition;

On my computer this returns:

Product Version Edition
-
9.00.1116 Express Edition

Hope this helps,
Josh Lindenmuth

retrieve either day of week or day name for current date

How can I code an mdx statement to return either the number for the current day of the week (ie. 1 - 7), or the name of the current day (Monday, Tuesday, etc.)? day(now()) gets me the actual calendar day

Thank you,

PB

Hopefully, this helps you get where you want to go:

Code Snippet

with member [Measures].[x] as

datepart("w",VBAMDX!Now())

select [x] on 0

from [Adventure Works]

;

B.

Retrieve DB path from a SP

Is there a method that would work from both SQL 2000 and SQL 2005 to retrieve the path
(Ex: D:\Appl\DB\widget.mdb) of the database the current SP is running from?
I have a need to process a XML file from a SP and I would like the SP to retrieve the
location of the XML file (Ex: D:\Appl\XML\Config.xml) automatically knowing that the XML
and the DB are both under the same root folder (D:\Appl\).
Thanks.
I think you can do it with extended stored procs... this article might
help you...
http://www.devarticles.com/c/a/SQL-S...ol-Examples/2/
|||KBuser wrote:
> I think you can do it with extended stored procs... this article might
> help you...
> http://www.devarticles.com/c/a/SQL-S...ol-Examples/2/
>
You can also try sp_helpdb 'YourDatabaseName'.
Regards
Steen
|||Or, you can try sp_helpfile.

> You can also try sp_helpdb 'YourDatabaseName'.
> Regards
> Steen
>

Retrieve DB path from a SP

Is there a method that would work from both SQL 2000 and SQL 2005 to retriev
e the path
(Ex: D:\Appl\DB\widget.mdb) of the database the current SP is running from?
I have a need to process a XML file from a SP and I would like the SP to ret
rieve the
location of the XML file (Ex: D:\Appl\XML\Config.xml) automatically knowing
that the XML
and the DB are both under the same root folder (D:\Appl\).
Thanks.I think you can do it with extended stored procs... this article might
help you...
http://www.devarticles.com/c/a/SQL-...ool-Examples/2/|||KBuser wrote:
> I think you can do it with extended stored procs... this article might
> help you...
> http://www.devarticles.com/c/a/SQL-...ool-Examples/2/
>
You can also try sp_helpdb 'YourDatabaseName'.
Regards
Steen|||Or, you can try sp_helpfile.

> You can also try sp_helpdb 'YourDatabaseName'.
> Regards
> Steen
>

Retrieve DB path from a SP

Is there a method that would work from both SQL 2000 and SQL 2005 to retrieve the path
(Ex: D:\Appl\DB\widget.mdb) of the database the current SP is running from?
I have a need to process a XML file from a SP and I would like the SP to retrieve the
location of the XML file (Ex: D:\Appl\XML\Config.xml) automatically knowing that the XML
and the DB are both under the same root folder (D:\Appl\).
Thanks.I think you can do it with extended stored procs... this article might
help you...
http://www.devarticles.com/c/a/SQL-Server/Extended-Stored-Procedures-Intro-And-10-Cool-Examples/2/|||KBuser wrote:
> I think you can do it with extended stored procs... this article might
> help you...
> http://www.devarticles.com/c/a/SQL-Server/Extended-Stored-Procedures-Intro-And-10-Cool-Examples/2/
>
You can also try sp_helpdb 'YourDatabaseName'.
Regards
Steen|||Or, you can try sp_helpfile.
> >
> You can also try sp_helpdb 'YourDatabaseName'.
> Regards
> Steen
>

Retrieve data from another table in subquery

Hi there, here is my current query:

sqlTemp = "SELECT id,code,description FROM tbl_content " &_
"WHERE EXISTS " &_
"(SELECT * FROM tbl_published_content " &_
"WHERE tbl_content.id = tbl_published_content.id ORDER BY tbl_published_content.nCorrectOrder)"

I don't get results sorted by tbl_published_content.nCorrectOrder, and have also no iea how to retrieve that values.

Please can any one tell me how can I have access to tbl_published_content.nCorrectOrder and get it sorted by this field?

So I can use objRS.("tbl_published_content.nCorrectOrder") or some alias?

thanks a million!Try this instead
select
[id]
,code
,description
from tbl_content tc join
tbl_published_content tpc
on tc.[id] = tpc.[id]
order by tpc.ncorrectorder

now the correct way to do this is to create a stored procedure
then execute the procedure from your client
your clients will thank you.|||Great! thanks :)

I forgot to mention it was for a JetSQL database, so here is the final query if anyone is interested

sqlTemp = "SELECT tc.id,tc.code,tc.description,tpc.nCorrectOrder FROM tbl_content tc INNER JOIN " &_
"tbl_published_content tpc on tc.id = tpc.id " &_
"ORDER BY tpc.nCorrectOrder"

Best regards, Eth.