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 SQL requirement possible?

I need a script that enumerates the the databases in a given instance of sql server. Then it needs to output the account with permissions on that server. Finally it then needs to search stored procedured for reference to specific file and account names loaded from a csv file. The results should be output to a text file or displayed in an exportable window format.

Thanks a lot

1. "enumerates the the databases" -yes, possible.

2. "output the account with permissions" -yes, possible.

3. "search stored procedured for reference to specific file and account names loaded from a csv file" -yes, possible.

Any more detailed help requires more detailed information from you.

|||

What I don t understand is this:

"search stored procedured for reference to specific file and account names loaded from a csv file"?

would u be able to guess what that means and if possible give me an example of it, please?

Thanks a lot.

|||

I don't wish to continue playing 'guessing games' with you.

I have previously attempted to 'guess' what you had in mind when you made the original post. If my response doesn't seem 'close to the mark' for you, I suggest that you refine your question and repost.

|||

R.Tutus wrote:

I need a script that enumerates the the databases in a given instance of sql server. Then it needs to output the account with permissions on that server.

The following query will do this..

Create Table #ServerLoginDetails
(
DataBaseName Varchar(1000)
,LoginName Varchar(1000)
,MemberOf Varchar(1000)
)
Go
Exec sp_MSforeachdb 'Insert Into #ServerLoginDetails
Select ''?'',Users.Name Loginname, Isnull(Groups.Name,''NA'') MemberOf From ?..Sysusers Users
Left Outer Join ?..Sysmembers Members On Members.memberuid = Users.Uid
Left Outer Join ?..Sysusers Groups On Groups.Uid = Members.GroupUid And Groups.IsLogin=0
Where Users.IsLogin=1'

Select * from #ServerLoginDetails
Go
Drop Table #ServerLoginDetails

R.Tutus wrote:

Finally it then needs to search stored procedured for reference to specific file and account names loaded from a csv file. The results should be output to a text file or displayed in an exportable window format.

Need more information buddy..

|||

That s perfect. Tell me what does the question mark "?" refer to in T-SQL. I used the script without it and i think ot still works. why do we hgave to use it?

Also you quoted the last question:

R.Tutus wrote:

Finally it then needs to search stored procedured for reference to specific file and account names loaded from a csv file. The results should be output to a text file or displayed in an exportable window format.

Do u have a clue on what does means or how it can be solved.

Thanks a lot, i ll let u know if i have more questions in undersatanding your script.

|||

? will replace the database name when you execute the sp_MSForEachDB.

sp_MSForEachDB is one of the system SP but none of the online document guide you (Undocumented SPs)..

If you remove the ? from the query, you wont get all the database's users information. This SP will automatically enumerate each database and execute the query..

|||Please don't use this system stored procedure. It is not documented nor supported. So we can/will remove it or modify the behavior without notice in any version of SQL Server or service pack. Instead you should write your own SP that uses dynamic SQL to execute the cmd or use sp_executesql itself.|||

Yes I agree this...

So tried to change the logic with following query...

Create Table #ServerLoginDetails
(
DataBaseName Varchar(1000)
,LoginName Varchar(1000)
,MemberOf Varchar(1000)
)
Go
Declare @.DB Table (Query varchar(8000));
Declare @.Query as Varchar(5000);
Declare @.Count as int;

Select @.Query = 'Insert Into #ServerLoginDetails
Select ''?'',Users.Name Loginname, Isnull(Groups.Name,''NA'') MemberOf From ?..Sysusers Users
Left Outer Join ?..Sysmembers Members On Members.memberuid = Users.Uid
Left Outer Join ?..Sysusers Groups On Groups.Uid = Members.GroupUid And Groups.IsLogin=0
Where Users.IsLogin=1';

Insert Into @.DB
Select Replace(@.Query,'?',Name) From Master..Sysdatabases;

Select @.Count = Count(Query) from @.DB
While @.Count <> 0
Begin
Select @.Query=Query From @.DB;
Exec(@.Query);
Delete From @.DB Where Query=@.Query;
Select @.Count = Count(Query) from @.DB;
End

Select * from #ServerLoginDetails
Go
Drop Table #ServerLoginDetails

|||

can u explain a bit Mani pls:

what s the member table used for and how did u join the 2. ( i see the code ) but i need explanantions on what joined columns represent in order to understand.

also cn u explain to me just generally the last script, how different it s from the first. just generally.

Thanks a lot.

|||

How can I change the script above (that looks up the users of all the databases) so that I get the permissions as well.

Thank you.

Is this SQL requirement possible?

I need a script that enumerates the the databases in a given instance of sql server. Then it needs to output the account with permissions on that server. Finally it then needs to search stored procedured for reference to specific file and account names loaded from a csv file. The results should be output to a text file or displayed in an exportable window format.

Thanks a lot

1. "enumerates the the databases" -yes, possible.

2. "output the account with permissions" -yes, possible.

3. "search stored procedured for reference to specific file and account names loaded from a csv file" -yes, possible.

Any more detailed help requires more detailed information from you.

|||

What I don t understand is this:

"search stored procedured for reference to specific file and account names loaded from a csv file"?

would u be able to guess what that means and if possible give me an example of it, please?

Thanks a lot.

|||

I don't wish to continue playing 'guessing games' with you.

I have previously attempted to 'guess' what you had in mind when you made the original post. If my response doesn't seem 'close to the mark' for you, I suggest that you refine your question and repost.

|||

R.Tutus wrote:

I need a script that enumerates the the databases in a given instance of sql server. Then it needs to output the account with permissions on that server.

The following query will do this..

Create Table #ServerLoginDetails
(
DataBaseName Varchar(1000)
,LoginName Varchar(1000)
,MemberOf Varchar(1000)
)
Go
Exec sp_MSforeachdb 'Insert Into #ServerLoginDetails
Select ''?'',Users.Name Loginname, Isnull(Groups.Name,''NA'') MemberOf From ?..Sysusers Users
Left Outer Join ?..Sysmembers Members On Members.memberuid = Users.Uid
Left Outer Join ?..Sysusers Groups On Groups.Uid = Members.GroupUid And Groups.IsLogin=0
Where Users.IsLogin=1'

Select * from #ServerLoginDetails
Go
Drop Table #ServerLoginDetails

R.Tutus wrote:

Finally it then needs to search stored procedured for reference to specific file and account names loaded from a csv file. The results should be output to a text file or displayed in an exportable window format.

Need more information buddy..

|||

That s perfect. Tell me what does the question mark "?" refer to in T-SQL. I used the script without it and i think ot still works. why do we hgave to use it?

Also you quoted the last question:

R.Tutus wrote:

Finally it then needs to search stored procedured for reference to specific file and account names loaded from a csv file. The results should be output to a text file or displayed in an exportable window format.

Do u have a clue on what does means or how it can be solved.

Thanks a lot, i ll let u know if i have more questions in undersatanding your script.

|||

? will replace the database name when you execute the sp_MSForEachDB.

sp_MSForEachDB is one of the system SP but none of the online document guide you (Undocumented SPs)..

If you remove the ? from the query, you wont get all the database's users information. This SP will automatically enumerate each database and execute the query..

|||Please don't use this system stored procedure. It is not documented nor supported. So we can/will remove it or modify the behavior without notice in any version of SQL Server or service pack. Instead you should write your own SP that uses dynamic SQL to execute the cmd or use sp_executesql itself.|||

Yes I agree this...

So tried to change the logic with following query...

Create Table #ServerLoginDetails
(
DataBaseName Varchar(1000)
,LoginName Varchar(1000)
,MemberOf Varchar(1000)
)
Go
Declare @.DB Table (Query varchar(8000));
Declare @.Query as Varchar(5000);
Declare @.Count as int;

Select @.Query = 'Insert Into #ServerLoginDetails
Select ''?'',Users.Name Loginname, Isnull(Groups.Name,''NA'') MemberOf From ?..Sysusers Users
Left Outer Join ?..Sysmembers Members On Members.memberuid = Users.Uid
Left Outer Join ?..Sysusers Groups On Groups.Uid = Members.GroupUid And Groups.IsLogin=0
Where Users.IsLogin=1';

Insert Into @.DB
Select Replace(@.Query,'?',Name) From Master..Sysdatabases;

Select @.Count = Count(Query) from @.DB
While @.Count <> 0
Begin
Select @.Query=Query From @.DB;
Exec(@.Query);
Delete From @.DB Where Query=@.Query;
Select @.Count = Count(Query) from @.DB;
End

Select * from #ServerLoginDetails
Go
Drop Table #ServerLoginDetails

|||

can u explain a bit Mani pls:

what s the member table used for and how did u join the 2. ( i see the code ) but i need explanantions on what joined columns represent in order to understand.

also cn u explain to me just generally the last script, how different it s from the first. just generally.

Thanks a lot.

|||

How can I change the script above (that looks up the users of all the databases) so that I get the permissions as well.

Thank you.

Is this a bug in SQL 2000/2005?

Running the script below the error number is not generated and the
transaction is rolled back ...
Should be like this, or it should nicely give the error number and let me
decide what to do?
This is very usefull when using EXECUTE() or sp_executesql().
Here is hwo you can reproduce the problem:
CREATE TABLE xTest (IDCol int, xText varchar(100))
INSERT INTO xTest(IDCol,xText1) VALUES (1,'aaa')
SELECT @.@.ERROR --should be 207
DROP TABLE xTestSome errors terminates the batch. See the error handling articles at www.sommarsko
g.se for details.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Florin Lazar" <FlorinLazar@.discussions.microsoft.com> wrote in message
news:A436FCDD-B959-42C6-965F-4D40B47F6434@.microsoft.com...
> Running the script below the error number is not generated and the
> transaction is rolled back ...
> Should be like this, or it should nicely give the error number and let me
> decide what to do?
> This is very usefull when using EXECUTE() or sp_executesql().
> Here is hwo you can reproduce the problem:
> CREATE TABLE xTest (IDCol int, xText varchar(100))
> INSERT INTO xTest(IDCol,xText1) VALUES (1,'aaa')
> SELECT @.@.ERROR --should be 207
> DROP TABLE xTest|||The batch does not compile, and thus it is never executed. So the line
"SELECT @.@.ERROR" will never be executed.
Gert-Jan
Florin Lazar wrote:
> Running the script below the error number is not generated and the
> transaction is rolled back ...
> Should be like this, or it should nicely give the error number and let me
> decide what to do?
> This is very usefull when using EXECUTE() or sp_executesql().
> Here is hwo you can reproduce the problem:
> CREATE TABLE xTest (IDCol int, xText varchar(100))
> INSERT INTO xTest(IDCol,xText1) VALUES (1,'aaa')
> SELECT @.@.ERROR --should be 207
> DROP TABLE xTest

Is this a bug in SQL 2000/2005?

Running the script below the error number is not generated and the
transaction is rolled back ...
Should be like this, or it should nicely give the error number and let me
decide what to do?
This is very usefull when using EXECUTE() or sp_executesql().
Here is hwo you can reproduce the problem:
CREATE TABLE xTest (IDCol int, xText varchar(100))
INSERT INTO xTest(IDCol,xText1) VALUES (1,'aaa')
SELECT @.@.ERROR --should be 207
DROP TABLE xTest
Some errors terminates the batch. See the error handling articles at www.sommarskog.se for details.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Florin Lazar" <FlorinLazar@.discussions.microsoft.com> wrote in message
news:A436FCDD-B959-42C6-965F-4D40B47F6434@.microsoft.com...
> Running the script below the error number is not generated and the
> transaction is rolled back ...
> Should be like this, or it should nicely give the error number and let me
> decide what to do?
> This is very usefull when using EXECUTE() or sp_executesql().
> Here is hwo you can reproduce the problem:
> CREATE TABLE xTest (IDCol int, xText varchar(100))
> INSERT INTO xTest(IDCol,xText1) VALUES (1,'aaa')
> SELECT @.@.ERROR --should be 207
> DROP TABLE xTest
|||The batch does not compile, and thus it is never executed. So the line
"SELECT @.@.ERROR" will never be executed.
Gert-Jan
Florin Lazar wrote:
> Running the script below the error number is not generated and the
> transaction is rolled back ...
> Should be like this, or it should nicely give the error number and let me
> decide what to do?
> This is very usefull when using EXECUTE() or sp_executesql().
> Here is hwo you can reproduce the problem:
> CREATE TABLE xTest (IDCol int, xText varchar(100))
> INSERT INTO xTest(IDCol,xText1) VALUES (1,'aaa')
> SELECT @.@.ERROR --should be 207
> DROP TABLE xTest

Is this a bug in SQL 2000/2005?

Running the script below the error number is not generated and the
transaction is rolled back ...
Should be like this, or it should nicely give the error number and let me
decide what to do?
This is very usefull when using EXECUTE() or sp_executesql().
Here is hwo you can reproduce the problem:
CREATE TABLE xTest (IDCol int, xText varchar(100))
INSERT INTO xTest(IDCol,xText1) VALUES (1,'aaa')
SELECT @.@.ERROR --should be 207
DROP TABLE xTestSome errors terminates the batch. See the error handling articles at www.sommarskog.se for details.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Florin Lazar" <FlorinLazar@.discussions.microsoft.com> wrote in message
news:A436FCDD-B959-42C6-965F-4D40B47F6434@.microsoft.com...
> Running the script below the error number is not generated and the
> transaction is rolled back ...
> Should be like this, or it should nicely give the error number and let me
> decide what to do?
> This is very usefull when using EXECUTE() or sp_executesql().
> Here is hwo you can reproduce the problem:
> CREATE TABLE xTest (IDCol int, xText varchar(100))
> INSERT INTO xTest(IDCol,xText1) VALUES (1,'aaa')
> SELECT @.@.ERROR --should be 207
> DROP TABLE xTest|||The batch does not compile, and thus it is never executed. So the line
"SELECT @.@.ERROR" will never be executed.
Gert-Jan
Florin Lazar wrote:
> Running the script below the error number is not generated and the
> transaction is rolled back ...
> Should be like this, or it should nicely give the error number and let me
> decide what to do?
> This is very usefull when using EXECUTE() or sp_executesql().
> Here is hwo you can reproduce the problem:
> CREATE TABLE xTest (IDCol int, xText varchar(100))
> INSERT INTO xTest(IDCol,xText1) VALUES (1,'aaa')
> SELECT @.@.ERROR --should be 207
> DROP TABLE xTest

Is there is away to pass parameter to sql script

Is there is a way to pass parameters to pass parameters to sql script during command line exeution with ISQL or OsqlPlease be more specific with example.|||thanx in advance
simple example:

i have sql script that contains the folowing :
--************
insert into table xxx values (1,2,@.PARAMETER)
--************

i am running this script with isql
my question is how i can change(replace) the parameter @.PARAMETER each time i am running the commad line with isql|||In Windows you can write a batch file and use the parameter that way. For instance, create a file (named execsql.bat for instance) and put these lines in it:

isql.exe -U user_name -P pw -S server -Q "insert into table xxx values (1,2,%1)"

pause

[You need to plug in a real username, password, and server.]

Then you can run the file from a command prompt and pass in a value such as:

execsql.bat SomeParameterValue

The "SomeParameterValue" portion will get substituted into the query.|||thanx , but this is not solving my problem because

the sql is located in sqlfile with 1000 quiries befor and 1000 quireis after . meaning :

sqlfile.sql contains the folowing :

...
...
..
insert into table xxx values (1,2,%1)
..
..
.

and i am runnig it like this :
isql.exe -U user_name -P pw -S server -i sqlfile.sql

meaning i want to pass the parameters from the batch file to the sql file.|||Ahhh. In the documentation of the isql command (Books Online), it says you can use environment variables as parameters in your query. So you might be able to do something in your batch file like:

SET ParamValue = %1
isql.exe -U user_name -P pw -S server -i sqlfile.sql

And then inside sqlfile.sql your statement would read:
...
insert into table xxx values (1,2,%ParamValue%)
...

Does that help?|||Why don't you create a stored procedure out of this script?|||sold to the man with the stored procedure !!

my suggestion exactly|||well stored procedure is brilliant idea , for sure i will use it , but this idea is not solving all the problems
look at the folowing sql that create database on sqlserver machine it needs three parameters
i can create stored procedure to do this task , but the problem is that i need to create this procedure on the MASTER database(i think) , and i dont have permissions to do it.

CREATE DATABASE @.DB_NAME on primary
(Name = eProvisionDb,
FileName = ' @.path\xxxDb.mdf',
size = 50MB,
MaxSize=UNLIMITED,
FileGrowth=10MB)
Log On
(Name = eProvisionLog,
FileName ='@.path\xxxLog.mdf',
size = 20MB,
MaxSize=UNLIMITED,
FileGrowth=5MB)
any way the stored procedure is solving 90% of my problems|||then get someone with permissions to the master database to run the create script in that context.
you can then run the procedure any time you want to.|||Hmmm, I don't think the script will run. It'll have to be dynamically built.|||sure ,
you run it in dynamic sql

Is there any way of keeping all of a solution in one script project?

We do not have a source code control plug in for Visual Studio 2005 and I am trying to establish an efficient way of controlling versions of a solution I have created in BIDS, which includes SSAS objects plus an SSIS package.

As there are so many individual files for the dimensions, cubes, database, etc., it does not seem feasible to have them all checked in to our source control application and I was thinking of creating an Analysis Services Scripts project via SQL Server Mgt Studio where I could simply script out the SSAS database and the package and use these via the Deployment Wizard to deploy.

However -

a) according to BOL, an Analysis Services Scripts project has Connections, Scripts and Miscellaneous folders - when I create one, it has Queries instead of Scripts and when I script out the database I cannot see the xmla file in the project. Am I getting hold of the wrong end of the stick here?!

b) An SSIS package cannoy, I think, be scripted as such; when you deploy it creates the dtsx file and the SSIS DeploymentManifest file that installs the package, so is the best idea to keep these two files in source control?

Any advice gratefully received! Thank you

Rachel

I am a little bit surprised that your source control system has a limitation on number of files that affects you. In typical project you might have couple of dozen files, let's say up to a hundrend in a big solution - but modern source control systems can handle hundrends of thousands of files. Is there something here that I miss ?|||

In Visual Source Safe, part of Visual Studio, you do not check in and out individual files, only objects like dimensions and cubes.

Are you trying to build your own version control system? Visual Source Safe should be enough for projects with 1-5 developers?

Regards

Thomas Ivarsson

|||

Sorry, I was not making myself clear; we use MKS Source Integrity (a rather old version that does not integrate with VS) and there is no limit to the number of files it can deal with, but when I started checking in all the dim, cube, dsv, database, ds, dwproj, sln files etc it became apparent that it would be difficult to manage in that you would need to know which files would be affected by a change before you made it - I ended up checking everything out every time (a waste of time and effort!).

So I thought one script file for the database plus whatever the relevant files for the SSIS package are might be a better way forward, but would appreciate advice.

|||If only we had VSS! (see my response to Mosha above)|||So...any further advice from anyone?

is there any way ( table ) to find out when last time statistics got updated for

Hello All,
is there any way ( table ) to find out when last time statistics got updated for a table ? ( using script)
thank you
jagdishUSE [YOUR DATABASE]
SELECT 'Index Name' = i.name,
'Statistics Date' = STATS_DATE(i.id, i.indid)
FROM sysobjects o, sysindexes i
WHERE o.name = 'employee' AND o.id = i.id
GO

or
STATS_DATE ( table_id , index_id )

table_id

Is the ID of the table used.

index_id

Is the ID of the index used.

;)|||Thank you very much

Is there any database midware like asp script?

Is there any 'database midware' liked asp script? The following code is 'database midware' class in java, is there any code/script for asp3 (not asp.net) in jscript/vbscript ?
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Connection;
import java.util.List;

public class ASPD {
public ASPD() {
}

private Connection connection;
private Statement stmt;
private ResultSet rs;

public void connect(String connectionString) {
//TODO
}

public void disconnect() {
//TODO
}

public void query(String sql) {
//TODO
}

public int update(String sql) {
//TODO
return 0;
}

public boolean execute() {
//TODO
return false;
}

public void preparedQuery(String sql, List parameters) {
//TODO
}

public int preparedUpdate(String sql, List parameters) {
//TODO
return 0;
}

public boolean preparedExecute(String sql, List parameters) {
//TODO
return false;
}
}//EOF
Any help is thankful.If you are talking about classes that support data access, you might want to look at Active X Data Objects that come bundled in MDAC.|||There are tons of example scripts available, look for example here (http://www.aspwebpro.com/tutorials/asp/dbconnectionopen.asp)

Hope this helps.