return db names on server

Hello,

Does anyone know how to return the name of all the databases except the system databases on a server for SQL Server 2005?

Thank you

select * from sys.databases where database_id not in (1, 2, 3, 4)

|||Thank you!

Retrieving user defined Role name

Is there a System stored procedure that gives me the Role in which a user is in. For example I execute this procedure, give the user as parameter an that gives me back the Role the user is in. It has to be said that this is a user defined role, I got three of them, HR, Employee, Approver.

Greetings,
GodofredoIs it not clear? I just want to retrieve a user defined Role

Greets,
geoff|||I'd use sp_helpuser (http://msdn.microsoft.com/library/en-us/tsqlref/ts_sp_help_45o2.asp).

-PatP

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. :)

Retrieving Data

Hi

I've got a module that contains the following function.
Imports System.Data.SqlClient

Module SQL_Sprocs
Function ListUsers()

Dim conn As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("DBConn"))

Dim cmd As SqlCommand = New SqlCommand("list_users", conn)
cmd.CommandType = CommandType.StoredProcedure
conn.Open()

Dim dr As SqlDataReader

dr = cmd.ExecuteReader

ListUsers = dr

dr.Close()
conn.Close()

End Function
End Module

I then want to call this function from my webform. So I'm using the following

Dim dr As SqlClient.SqlDataReader

dr = Timetracker.SQLProcs.ListUsers()

Do While dr.Read
Label1.Text &= dr("first_name") & " " & dr("last_name") & ", "
Loop

dr.Close()

But it's not working. I want to have a module that contains all my sprocs and be able to call them from the individual webpages.

What am I doing wrong?

LbobIt's also private.

You're returning a closed data reader.

dr = cmd.ExecuteReader

ListUsers = dr

dr.Close()
conn.Close()

-------

and you should never break apart the datalayer, and require a reader object to be return back to the primary worker process.

ghetto code.|||Can someone tell me then what I should do with stored procedures that get called from multiple places? In our current app we have a sql_procs.asp which contains all of them, we then use include on the required page and return the recordset.

Any ideas??|||You should never return a data reader to a presentation component, it keeps the connection open until you close, as you've just discovered. If you're looking for a simple solution I'd used a DataSet instead.

Retrieving and storing file timestamp

I have a package that processes a flat file, which is generated by a separate system each weekday around 3:30AM. I would like my package to store the timestamp of the input file and compare it to the timestamp of the next day's file. If they are the same, then the package can exit without reprocessing a particular file again.

So, my first question is whether a package variable can be updated so that its value will persist from one run to the next?

Second, what is the syntax to retrieve the file's timestamp, either directly from the file connection manager or within a script task?

Thanks,

Phil

Philsky wrote:

I have a package that processes a flat file, which is generated by a separate system each weekday around 3:30AM. I would like my package to store the timestamp of the input file and compare it to the timestamp of the next day's file. If they are the same, then the package can exit without reprocessing a particular file again.

So, my first question is whether a package variable can be updated so that its value will persist from one run to the next?

No! That'd be nice wouldn't it. I've requested similar functionality in the past although not formally via the Product Feedback Center. Perhaps you could submit it?

Philsky wrote:

Second, what is the syntax to retrieve the file's timestamp, either directly from the file connection manager or within a script task?

Thanks,

Phil

There's some code here that shows how you can access this information: http://blogs.conchango.com/jamiethomson/archive/2005/10/10/2253.aspx

Check lines 7 & 8 of the first code block.

-Jamie

Retrieve Primary key coloumn by querying system table of Sql server 2000

Hi,
How can i retrieve primary key columns including foreign key ( part of
primary key) by querying system tables of sql server 2000. I want to
know that how many columns are primary key column in table.
Pls help.
Thanks in advance
ShailSee if this helps:
--In the WHERE clause change the table name and table owner to your table
name and owner.
SELECT TC.TABLE_SCHEMA,
TC.TABLE_NAME,
TC.CONSTRAINT_TYPE,
TC.CONSTRAINT_NAME,
KCU.COLUMN_NAME,
KCU.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU
ON TC.TABLE_SCHEMA = KCU.TABLE_SCHEMA
AND TC.TABLE_NAME = KCU.TABLE_NAME
AND TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
WHERE TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND TC.TABLE_SCHEMA = 'dbo'
AND TC.TABLE_NAME = 'txsh0hor'
ORDER BY KCU.ORDINAL_POSITION
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<shailbpl@.gmail.com> wrote in message
news:1150279652.629125.32380@.u72g2000cwu.googlegroups.com...
Hi,
How can i retrieve primary key columns including foreign key ( part of
primary key) by querying system tables of sql server 2000. I want to
know that how many columns are primary key column in table.
Pls help.
Thanks in advance
Shail|||Here's an example of getting the columns of a primary key constraint:
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'author
s'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<shailbpl@.gmail.com> wrote in message news:1150279652.629125.32380@.u72g2000cwu.googlegroups
.com...
> Hi,
> How can i retrieve primary key columns including foreign key ( part of
> primary key) by querying system tables of sql server 2000. I want to
> know that how many columns are primary key column in table.
> Pls help.
> Thanks in advance
> Shail
>|||sp_help 'tableName'
OR
select * from information_schema.table_constraints
where constraInt_type IN ('PRIMARY KEY','FOREIGN KEY')
Madhivanan
shailbpl@.gmail.com wrote:
> Hi,
> How can i retrieve primary key columns including foreign key ( part of
> primary key) by querying system tables of sql server 2000. I want to
> know that how many columns are primary key column in table.
> Pls help.
> Thanks in advance
> Shail|||See whether this helps
SELECT a.pktable,A.fktable,a.fkcol,a.[name]
FROM
(SELECT a.*,b.[name]
FROM
(SELECT object_name(fkeyid) as FkTable ,
object_name(rkeyid) as PkTable,
b.name as fkCol ,rkey,rkeyid
FROM sysforeignkeys a
INNER JOIN syscolumns b
ON a.fkey=b.colid and a.fkeyid=b.id ) a
LEFT OUTER JOIN syscolumns b
ON a.rkey=b.colid where a.rkeyid=b.id ) a
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
<shailbpl@.gmail.com> wrote in message
news:1150279652.629125.32380@.u72g2000cwu.googlegroups.com...
> Hi,
> How can i retrieve primary key columns including foreign key ( part of
> primary key) by querying system tables of sql server 2000. I want to
> know that how many columns are primary key column in table.
> Pls help.
> Thanks in advance
> Shail
>