retrieving an instance name

In an DTS ActiveX I need to open an ADODB connection whose Data Source shoud
be the SQLServer instance where the DTS is located.
How can I retrive the instance name?
Thnx
Kyky
Hi
Not that I am aware of. DTS Packages can be run outside the scope of the SQL
Server, or be compiled into VB6 projects.
Regards
Mike
"Kyky" wrote:

> In an DTS ActiveX I need to open an ADODB connection whose Data Source shoud
> be the SQLServer instance where the DTS is located.
> How can I retrive the instance name?
> Thnx
> Kyky
>
>
|||You can add a T-SQL to the DTS package and select @.@.servername to get the
instance.. .map the return value to a global variable and use it anywhere...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Kyky" <kyky_az@.yahoo.it> wrote in message
news:e%230BNtZGFHA.3928@.TK2MSFTNGP09.phx.gbl...
> In an DTS ActiveX I need to open an ADODB connection whose Data Source
> shoud
> be the SQLServer instance where the DTS is located.
> How can I retrive the instance name?
> Thnx
> Kyky
>

retrieving an instance name

In an DTS ActiveX I need to open an ADODB connection whose Data Source shoud
be the SQLServer instance where the DTS is located.
How can I retrive the instance name?
Thnx
KykyHi
Not that I am aware of. DTS Packages can be run outside the scope of the SQL
Server, or be compiled into VB6 projects.
Regards
Mike
"Kyky" wrote:

> In an DTS ActiveX I need to open an ADODB connection whose Data Source sho
ud
> be the SQLServer instance where the DTS is located.
> How can I retrive the instance name?
> Thnx
> Kyky
>
>|||You can add a T-SQL to the DTS package and select @.@.servername to get the
instance.. .map the return value to a global variable and use it anywhere...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Kyky" <kyky_az@.yahoo.it> wrote in message
news:e%230BNtZGFHA.3928@.TK2MSFTNGP09.phx.gbl...
> In an DTS ActiveX I need to open an ADODB connection whose Data Source
> shoud
> be the SQLServer instance where the DTS is located.
> How can I retrive the instance name?
> Thnx
> Kyky
>

retrieving an instance name

In an DTS ActiveX I need to open an ADODB connection whose Data Source shoud
be the SQLServer instance where the DTS is located.
How can I retrive the instance name?
Thnx
KykyHi
Not that I am aware of. DTS Packages can be run outside the scope of the SQL
Server, or be compiled into VB6 projects.
Regards
Mike
"Kyky" wrote:
> In an DTS ActiveX I need to open an ADODB connection whose Data Source shoud
> be the SQLServer instance where the DTS is located.
> How can I retrive the instance name?
> Thnx
> Kyky
>
>|||You can add a T-SQL to the DTS package and select @.@.servername to get the
instance.. .map the return value to a global variable and use it anywhere...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Kyky" <kyky_az@.yahoo.it> wrote in message
news:e%230BNtZGFHA.3928@.TK2MSFTNGP09.phx.gbl...
> In an DTS ActiveX I need to open an ADODB connection whose Data Source
> shoud
> be the SQLServer instance where the DTS is located.
> How can I retrive the instance name?
> Thnx
> Kyky
>

Retrieving a list of Event Classes

Hi,
I have been trying in vain for the last few hours to retrieve a list of EventClasses for a particular Notification Service instance. Its a web application that I'm building I want to be able to populate a drop down list with all the notifications a user can subscribe to. Any advice on the subject would be greatly appreciated.
RegardsIn relation to the above its actually a list of subscription classes that I wish to retrieve from the notification services instance and then the user will be able to select which one they wish to subscribe to. Sorry about the mix up.
Regards|||Hi,

Stored procedure named "NSGetSubscriptionClasses" on NS application database will get you that information.

Shamir

Retrieve the default data folder path

Is it possible to find out the default path SQL Server creates databases in
depending on what instance you are connected to. E.g. My default instance
default data path is C:\Program Files\Microsoft SQL Server\MSSQL\Data. My
named instance path I also have is C:\Program Files\Microsoft SQL
Server\MSSQL$Testing\Data and my SQL Express data path is C:\Program
Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data.
Can I find out the default data path for the current instance I am connected
to using a query?
ThanksYou can probably use the Model database's SysDatabases.FileName to get the
path.
"Chris" wrote:

> Is it possible to find out the default path SQL Server creates databases i
n
> depending on what instance you are connected to. E.g. My default instance
> default data path is C:\Program Files\Microsoft SQL Server\MSSQL\Data. My
> named instance path I also have is C:\Program Files\Microsoft SQL
> Server\MSSQL$Testing\Data and my SQL Express data path is C:\Program
> Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data.
> Can I find out the default data path for the current instance I am connect
ed
> to using a query?
> Thanks
>
>|||Thanks Mike,
If I do the following it seems to return the path.
SELECT REPLACE(filename, 'model.mdf', '') AS DataPath FROM SysDatabases
WHERE name = 'model'
Chris
"Mike K" <MikeK@.discussions.microsoft.com> wrote in message
news:E3722206-05B2-4026-9E59-9F863C75B73E@.microsoft.com...
> You can probably use the Model database's SysDatabases.FileName to get the
> path.
> "Chris" wrote:
>|||You might want to research what database you are currently connected to.
Just checking a different database might not get you really what you want.
set nocount on
create table #output
(
spid int,
status varchar(100),
login varchar(100),
hostname varchar(100),
blkby varchar(10),
dbname varchar(30),
command varchar(50),
cputime int,
diskio int,
lastbatch varchar(20),
programname varchar(200),
spid2 int
)
insert into #output exec sp_who2
set nocount off
select
ltrim(rtrim(dbname))
from
#output
where
spid = @.@.spid
drop table #output

Retrieve first record

I have a table of payments with these columns:

PaymentDate,UID,Item,Amount,Status,TransactionID

I'd like to retrieve the first instance of each purchase by a user (UID) where Status is "Completed".

For example

1/1/2003,10,Socks,7,Pending,1001
1/1/2003,10,Socks,7,Completed,1002
1/2/2003,11,Shirt,20,Pending,1003
1/2/2003,11,Shirt,20,Completed,1004
1/3/2003,11,Pants,30,Completed,1005

should return:

1/1/2003,10,Socks,7,Completed,1002
1/2/2003,11,Shirt,20,Completed,1004select columns
from table t
where rowid = (select min(rowid) from table where t.key = key);|||Originally posted by r123456
select columns
from table t
where rowid = (select min(rowid) from table where t.key = key);
Thanks for the reply but forgive me for being a newbie. I tried

SELECT ID,PaymentDate,UID,AID,ItemNumber,Username,Payment Status,TxnID FROM transactions where UID = (select min(UID) from table where transactions.UID = UID)

and I get

[Microsoft][ODBC Microsoft Access Driver] Syntax error. in query expression 'UID = (select min(UID) from table where transactions.UID = UID)'.|||select PaymentDate
, UID
, Item
, Amount
, Status
, TransactionID
from transactions FOO
where Status = 'Completed'
and PaymentDate
= ( select min(PaymentDate)
from transactions
where Status = 'Completed'
and UID = FOO.UID ) rudy
http://r937.com|||Originally posted by r937
select PaymentDate
, UID
, Item
, Amount
, Status
, TransactionID
from transactions FOO
where Status = 'Completed'
and PaymentDate
= ( select min(PaymentDate)
from transactions
where Status = 'Completed'
and UID = FOO.UID ) rudy
http://r937.com

It's a winner! Thank you!

I thought this could be done with some form of TOP and/or GROUP BY but I spent all morning trying different incarnations with no luck. Thanks again for your help.

Retrieve Domain Name

Is there a way to pull back the domain name that a server is in from within
SQL?
ie @.@.servername returns the server/instance, something similar for the
domain is what I am looking for.
to capture the start time, end time, application name, client host name, NT
user name and the domain name.
DECLARE @.TraceID int, @.DB_ID int
EXEC CreateTrace
'C:\My SQL Traces\ProceduresCalledInMSDB',
@.OutputTraceID = @.TraceID OUT
EXEC AddEvent
@.TraceID,
'SP:Completed',
'TextData, StartTime, EndTime, ApplicationName, ClientHostName, NTUserName,
NTDomainName, DatabaseID'
SET @.DB_ID = DB_ID('msdb')
EXEC AddFilter
@.TraceID,
'DatabaseID',
@.DB_ID
EXEC StartTrace 1
GO
"Thom" wrote:

> Is there a way to pull back the domain name that a server is in from within
> SQL?
> ie @.@.servername returns the server/instance, something similar for the
> domain is what I am looking for.
|||Sorry all, this is not within a trace but just at a sql analyzer window or
within a stored proc?
"Thom" wrote:

> Is there a way to pull back the domain name that a server is in from within
> SQL?
> ie @.@.servername returns the server/instance, something similar for the
> domain is what I am looking for.
|||I don't think there is any direct statement in SQL to get the domain name.
I think these statement will give the domain information for a login or
associated to the server:
xp_loginconfig
xp_enumgroups
xp_logininfo
"Thom" wrote:

> Is there a way to pull back the domain name that a server is in from within
> SQL?
> ie @.@.servername returns the server/instance, something similar for the
> domain is what I am looking for.

Retrieve Domain Name

Is there a way to pull back the domain name that a server is in from within
SQL?
ie @.@.servername returns the server/instance, something similar for the
domain is what I am looking for.to capture the start time, end time, application name, client host name, NT
user name and the domain name.
DECLARE @.TraceID int, @.DB_ID int
EXEC CreateTrace
'C:\My SQL Traces\ProceduresCalledInMSDB',
@.OutputTraceID = @.TraceID OUT
EXEC AddEvent
@.TraceID,
'SP:Completed',
'TextData, StartTime, EndTime, ApplicationName, ClientHostName, NTUserName,
NTDomainName, DatabaseID'
SET @.DB_ID = DB_ID('msdb')
EXEC AddFilter
@.TraceID,
'DatabaseID',
@.DB_ID
EXEC StartTrace 1
GO
"Thom" wrote:
> Is there a way to pull back the domain name that a server is in from within
> SQL?
> ie @.@.servername returns the server/instance, something similar for the
> domain is what I am looking for.|||Sorry all, this is not within a trace but just at a sql analyzer window or
within a stored proc?
"Thom" wrote:
> Is there a way to pull back the domain name that a server is in from within
> SQL?
> ie @.@.servername returns the server/instance, something similar for the
> domain is what I am looking for.|||I don't think there is any direct statement in SQL to get the domain name.
I think these statement will give the domain information for a login or
associated to the server:
xp_loginconfig
xp_enumgroups
xp_logininfo
"Thom" wrote:
> Is there a way to pull back the domain name that a server is in from within
> SQL?
> ie @.@.servername returns the server/instance, something similar for the
> domain is what I am looking for.

Retrieve Domain Name

Is there a way to pull back the domain name that a server is in from within
SQL?
ie @.@.servername returns the server/instance, something similar for the
domain is what I am looking for.to capture the start time, end time, application name, client host name, NT
user name and the domain name.
DECLARE @.TraceID int, @.DB_ID int
EXEC CreateTrace
'C:\My SQL Traces\ProceduresCalledInMSDB',
@.OutputTraceID = @.TraceID OUT
EXEC AddEvent
@.TraceID,
'SP:Completed',
'TextData, StartTime, EndTime, ApplicationName, ClientHostName, NTUserName,
NTDomainName, DatabaseID'
SET @.DB_ID = DB_ID('msdb')
EXEC AddFilter
@.TraceID,
'DatabaseID',
@.DB_ID
EXEC StartTrace 1
GO
"Thom" wrote:

> Is there a way to pull back the domain name that a server is in from withi
n
> SQL?
> ie @.@.servername returns the server/instance, something similar for the
> domain is what I am looking for.|||Sorry all, this is not within a trace but just at a sql analyzer window or
within a stored proc?
"Thom" wrote:

> Is there a way to pull back the domain name that a server is in from withi
n
> SQL?
> ie @.@.servername returns the server/instance, something similar for the
> domain is what I am looking for.|||I don't think there is any direct statement in SQL to get the domain name.
I think these statement will give the domain information for a login or
associated to the server:
xp_loginconfig
xp_enumgroups
xp_logininfo
"Thom" wrote:

> Is there a way to pull back the domain name that a server is in from withi
n
> SQL?
> ie @.@.servername returns the server/instance, something similar for the
> domain is what I am looking for.