Char and Varchar: table

Showing posts with label table. Show all posts

Tuesday, March 27, 2012

Check Roles assign to user

Hello,
Can you tell me how can i verify to what roles a user is assigned? Is there
any system table or stored procedure to check this information?
Best regards
sp_helplogins,sp_helpusers
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:A1DF2C34-92DF-4525-871C-FBA160B0F705@.microsoft.com...
> Hello,
> Can you tell me how can i verify to what roles a user is assigned? Is
there
> any system table or stored procedure to check this information?
> Best regards
>

Check Roles assign to user

Hello,
Can you tell me how can i verify to what roles a user is assigned? Is there
any system table or stored procedure to check this information?
Best regardssp_helplogins,sp_helpusers
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:A1DF2C34-92DF-4525-871C-FBA160B0F705@.microsoft.com...
> Hello,
> Can you tell me how can i verify to what roles a user is assigned? Is
there
> any system table or stored procedure to check this information?
> Best regards
>

Check Roles assign to user

Hello,
Can you tell me how can i verify to what roles a user is assigned? Is there
any system table or stored procedure to check this information?
Best regardssp_helplogins,sp_helpusers
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:A1DF2C34-92DF-4525-871C-FBA160B0F705@.microsoft.com...
> Hello,
> Can you tell me how can i verify to what roles a user is assigned? Is
there
> any system table or stored procedure to check this information?
> Best regards
>sql

Check Recovery Model

Hello,
How can i get information about the recovery model of one
database without using the Enterprise Manager?
What table should i query to get this information?
Best RegardsYou can get the recovery model for a database with:
SELECT DATABASEPROPERTYEX('<database name>','Recovery')
for example:
SELECT DATABASEPROPERTYEX('Northwind', 'Recovery')
Jacco Schalkwijk
SQL Server MVP
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:449b01c42b91$3600c950$a501280a@.phx.gbl...
> Hello,
> How can i get information about the recovery model of one
> database without using the Enterprise Manager?
> What table should i query to get this information?
> Best Regards|||To add to Jacco's response, the recovery model is also available via
sp_helpdb.
Hope this helps.
Dan Guzman
SQL Server MVP
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:449b01c42b91$3600c950$a501280a@.phx.gbl...
> Hello,
> How can i get information about the recovery model of one
> database without using the Enterprise Manager?
> What table should i query to get this information?
> Best Regardssql

Check Recovery Model

Hello,
How can i get information about the recovery model of one
database without using the Enterprise Manager?
What table should i query to get this information?
Best Regards
You can get the recovery model for a database with:
SELECT DATABASEPROPERTYEX('<database name>','Recovery')
for example:
SELECT DATABASEPROPERTYEX('Northwind', 'Recovery')
Jacco Schalkwijk
SQL Server MVP
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:449b01c42b91$3600c950$a501280a@.phx.gbl...
> Hello,
> How can i get information about the recovery model of one
> database without using the Enterprise Manager?
> What table should i query to get this information?
> Best Regards
|||To add to Jacco's response, the recovery model is also available via
sp_helpdb.
Hope this helps.
Dan Guzman
SQL Server MVP
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:449b01c42b91$3600c950$a501280a@.phx.gbl...
> Hello,
> How can i get information about the recovery model of one
> database without using the Enterprise Manager?
> What table should i query to get this information?
> Best Regards

Check Recovery Model

Hello,
How can i get information about the recovery model of one
database without using the Enterprise Manager?
What table should i query to get this information?
Best RegardsYou can get the recovery model for a database with:
SELECT DATABASEPROPERTYEX('<database name>','Recovery')
for example:
SELECT DATABASEPROPERTYEX('Northwind', 'Recovery')
--
Jacco Schalkwijk
SQL Server MVP
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:449b01c42b91$3600c950$a501280a@.phx.gbl...
> Hello,
> How can i get information about the recovery model of one
> database without using the Enterprise Manager?
> What table should i query to get this information?
> Best Regards|||To add to Jacco's response, the recovery model is also available via
sp_helpdb.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:449b01c42b91$3600c950$a501280a@.phx.gbl...
> Hello,
> How can i get information about the recovery model of one
> database without using the Enterprise Manager?
> What table should i query to get this information?
> Best Regards

check previous row in the table

Hi...

Is there any way to check previous row in SQL Query?

I have a table with these column :
Name1
Name2
Audit_Time (datetime)
Changes

I want to delete record from database in which the Audit_time is <'01/05/2004'.
However before deletion, I want to check, if the Changes value is 'OLD' And the previous value is 'NEW', I will check the Audit_time of the NEW instead of OLD.


Table :
Row Name1 Name2 Audit_Time(mm/dd/yyyy) Changes
1 ABCD EFGH '01/01/2004' ADD
2 ABCD EFGHIJ '01/04/2004' NEW
3 ABCD EFGH '01/04/2004' OLD
4 Klarinda Rahmat '02/08/2004' NEW
5 Klarinda Rahmat '01/04/2004' OLD

In this case, I want to delete row 1,2,3 Where the audit_time are < '01/05/2004'.
Row 5 the audit_time also < '01/05/2004', however the changes='OLD' and the previous value changes='NEW', so I will check the Audit_Time of row 4 which is not < '01/05/2004'.
So I can't delete row5.

Is there any way to check previous row or the row before a specific row in SQL.
Any suggestion is welcomed.
Thank you in advanced.You can use a Cursor in a Stored Procedure. It would be very complex though...|||Check out the EXISTS keyword (assuming SQL server)...sql

Sunday, March 25, 2012

Check on the tables about last modification!

Hi,
Is there any possibilty to have a check on the tables about the last
modification(ie.,who has changed the data in the table using a query or hard
core change in the table value)?
If not, what are the other possibilities (any third party tool).
Kindly help.
Thanks,
ShyamHi Shyam,
Unfortunately that is not possible. But there are methods by which you can
track it...
(a) Having a trigger on the table. Remember its a costly operation
(b) Having the trace on
(c) If the modification is allowed only from the application, then probably
you can have a datetime field (getdate()) in the table for insert/update
statements.
HTH
--
Thanks
Yogish|||you can use a log explorer (http://www.kccltd.com/tools/log.asp) to see what
has happened after the event, which may be too late or you could introduce
database change management so you know what is changing in the database
before it actually changes (http://www.dbghost.com)
"Shyam" wrote:

> Hi,
> Is there any possibilty to have a check on the tables about the last
> modification(ie.,who has changed the data in the table using a query or ha
rd
> core change in the table value)?
> If not, what are the other possibilities (any third party tool).
> Kindly help.
> Thanks,
> Shyamsql

Check on the tables about last modification!

Hi,
Is there any possibilty to have a check on the tables about the last
modification(ie.,who has changed the data in the table using a query or hard
core change in the table value)?
If not, what are the other possibilities (any third party tool).
Kindly help.
Thanks,
Shyam
Hi Shyam,
Unfortunately that is not possible. But there are methods by which you can
track it...
(a) Having a trigger on the table. Remember its a costly operation
(b) Having the trace on
(c) If the modification is allowed only from the application, then probably
you can have a datetime field (getdate()) in the table for insert/update
statements.
HTH
Thanks
Yogish
|||you can use a log explorer (http://www.kccltd.com/tools/log.asp) to see what
has happened after the event, which may be too late or you could introduce
database change management so you know what is changing in the database
before it actually changes (http://www.dbghost.com)
"Shyam" wrote:

> Hi,
> Is there any possibilty to have a check on the tables about the last
> modification(ie.,who has changed the data in the table using a query or hard
> core change in the table value)?
> If not, what are the other possibilities (any third party tool).
> Kindly help.
> Thanks,
> Shyam

Check on the tables about last modification!

Hi,
Is there any possibilty to have a check on the tables about the last
modification(ie.,who has changed the data in the table using a query or hard
core change in the table value)?
If not, what are the other possibilities (any third party tool).
Kindly help.
Thanks,
ShyamHi Shyam,
Unfortunately that is not possible. But there are methods by which you can
track it...
(a) Having a trigger on the table. Remember its a costly operation
(b) Having the trace on
(c) If the modification is allowed only from the application, then probably
you can have a datetime field (getdate()) in the table for insert/update
statements.
HTH
--
Thanks
Yogish|||you can use a log explorer (http://www.kccltd.com/tools/log.asp) to see what
has happened after the event, which may be too late or you could introduce
database change management so you know what is changing in the database
before it actually changes (http://www.dbghost.com)
"Shyam" wrote:
> Hi,
> Is there any possibilty to have a check on the tables about the last
> modification(ie.,who has changed the data in the table using a query or hard
> core change in the table value)?
> If not, what are the other possibilities (any third party tool).
> Kindly help.
> Thanks,
> Shyam

Check null value of long data type

Hi, I have a record set that is bound to a table in MS SQL Server. One
filed in the table is bound to a "long" type member variable in the
RecordSet. What will happen to the "long" variable when the field is
NULL in the table?
Thanks!
-YiYi (huskerchen@.hotmail.com) writes:
> Hi, I have a record set that is bound to a table in MS SQL Server. One
> filed in the table is bound to a "long" type member variable in the
> RecordSet. What will happen to the "long" variable when the field is
> NULL in the table?

I will have to admit that I am out on a limb, but I would expect
IsNull to be true for this field.

The simplest is probably just to make an experiment.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

check my SP [its not working fine]

Hello everyone, im using SQL Server 2000, here is my table:

Table1: Buyers

BuyerID int
ParentID int
MerchantCode varchar(20) (each buyer has unique merchant code we say that its login is Merchant Code)
PinCode (this is used as a password for the buyers login)
ApprovalStatusCode int (FK, data is for authenticationending Approval, Approved, Cancelled)
IsCliEnabled smallint
Clis varchar(100) (this is the CSV: 12345, 2346,....)[as each buyer has more that 1 CLi values so this field is in CSV form data)

//Now buyers also has SubAccounts (Table2) that was made for his workers or some one else (Not necessary every buyer has SubAccounts)

Table2: Buyers SubAccounts

BuyerID
AccountNumber smallint
PinCode varchar(64)
CreateDate datetime

now here is my SP,
CREATE PROCEDURE IvrAuthenticateBuyer

@.MerchantCode varchar(20),
@.PinCode varchar(64),
@.CLI varchar(15)

AS
-- For testing i give values at here
declare @.MerchantCode varchar(20)
set @.merchantCode='000000010'
declare @.PinCode varchar(64)
set @.PinCode='1234656'
declare @.CLI varchar(15)
set @.CLI='12345'
-

declare @.BuyerID int
declare @.ApprovalStatusCode smallint
declare @.IsCliEnabled smallint
declare @.Clis varchar(1000)

-- buyerID get by checking only 8 digits of merchant code (omit last one)
-- if last digit of merchant code are > "0" then
-- get them in a variable
-- check if the account is approved
-- check if CLI is enabled, if yes, check if @.cli is in the list

SELECT @.BuyerID = BuyerID, @.ApprovalStatusCode = ApprovalStatusCode,
@.IsCliEnabled = IsCLIEnabled, @.Clis = coalesce(@.Clis+',','')+CLIs
FROM Buyers
WHERE MerchantCode = @.MerchantCode

--select @.BuyerID
--select @.IsCliEnabled
--select @.ApprovalStatusCode
--select @.Clis
-- chk all conditions

if @.ApprovalStatusCode <> 2
raiserror('Account is not Approved',16,1)

select @.BuyerID

return

if (@.IsCliEnabled=1) --check whether true
begin --Main Begin
--charindex will return value greater than 0 if CLI is found in list
-- if charindex('34534',@.Clis)>0
if ','+@.Clis+',' like '%,'+@.CLI +',%'
begin
print 'CLI found in CSV list'
end
else
begin
raiserror('CLI NOT found in CSV list',16,1)
end
end --Main End
else
begin
raiserror('CLI NOT ENABLED',16,1)
end

-- Get Last Digit of Merchant Code and stored them in a variable

declare @.SubAccountNo varchar(2)
select @.SubAccountNo = Substring(@.MerchantCode,8,1)

-- Check It is SubAccount or the Buyer's Main Account

if convert(int, @.SubAccountNo) > 0
select 1
from BuyerSubAccounts
where PinCode=@.PinCode
else
select 1
from Buyers
where PinCode=@.PinCode

that is used for Buyer Authenticate, but i dont know whats wrong with this as its not working fine now,
as if i give wrong password then it also retuns the error of if i give wrong CLi same its runs without giving any error. Kindly
check it whats wrong with this as i m in much trouble still can't find out the actual problem in my SP. So i requested to all of u
plz help me and make my SP correct.

Thanx in Advance.

I believe I have found your problem. See code snippet below.

Code Snippet

CREATE PROCEDURE IvrAuthenticateBuyer

@.MerchantCode varchar(20),
@.PinCode varchar(64),
@.CLI varchar(15)

declare @.BuyerID int
declare @.ApprovalStatusCode smallint
declare @.IsCliEnabled smallint
declare @.Clis varchar(1000)

SELECT @.BuyerID = BuyerID, @.ApprovalStatusCode = ApprovalStatusCode,
@.IsCliEnabled = IsCLIEnabled, @.Clis = coalesce(@.Clis+',','')+CLIs
FROM Buyers
WHERE MerchantCode = @.MerchantCode

--select @.BuyerID
--select @.IsCliEnabled
--select @.ApprovalStatusCode
--select @.Clis
-- chk all conditions

if @.ApprovalStatusCode <> 2

BEGIN
raiserror('Account is not Approved',16,1)

select @.BuyerID

return

END

-- Get Last Digit of Merchant Code and stored them in a variable

declare @.SubAccountNo varchar(2)
select @.SubAccountNo = Substring(@.MerchantCode,8,1)

-- Check It is SubAccount or the Buyer's Main Account

if convert(int, @.SubAccountNo) > 0
select 1
from BuyerSubAccounts
where PinCode=@.PinCode
else
select 1
from Buyers
where PinCode=@.PinCode

An IF statement will only apply to the statement immediately following it, unless that statement is BEGIN, then it will execute until the next END is found.

Check multiple records before inserting records

i have a table which is having size and pieces information

Size, pieces, sizeID, sizecombID

S, 1, 1, 1

M, 3, 2, 1

L, 3, 3, 1

XL, 1, 4, 1

I have two questions :-

1. If user enters the same information i.e. same sizes and same pieces then my stored procedure should not add these four records, instead it should return existing value of SizecombID(which stands for size combination ID)

2. If a totally new information is entered then that should be added in the table and sizecombID should be updated with highest value + 1. So for example if there is only one record in table as shown above then once a new combination is entered, my stored procedure should return 2..

can anybody help on this...

One possibility:

Code Snippet

SELECT SizeCombID

FROM MyTable

WHERE ( Col1 = NewValue1

AND Col2 = NewValue2

AND Col3 = NewValue3

)

IF ( @.@.ROWCOUNT = 0 )

INSERT INTO MyTable

( Col1,

Col2,

Col3

)

VALUES

( NewValue1,

NewValue2,

NewValue3

)

|||

Actually the code snippet is only checking 1 record which is not the case.

1. I will be sending all the entered records to my stored procedure, what is the parameter to be used for sending multiple records

2. All records entered by user are to be checked. In exmaple which was provided, user has entered four sizes so it will be four records. All four records are to be checked.

3. Can you also tell how i can increment the sizecombid automatically to generate next value. Before Insert statement, stored procedure should get the last sizecombID value, add 1 in it and then add that value also to SizecombID.

Pls advs..

|||

1.Passing Multiple Records:

Use XML string, you can get back those records in same structure you want.
There is no limitation since you can use either text or ntext as parameter datatype.

(Ex)

Code Snippet

Declare @.Param as Varchar(8000);
Select @.param = '<Root>
<Row Size="S" pieces="1" sizeID="1" sizecombID="1"/>
<Row Size="M" pieces="3" sizeID="2" sizecombID="1"/>
<Row Size="L" pieces="3" sizeID="3" sizecombID="1"/>
<Row Size="XL" pieces="1" sizeID="4" sizecombID="1"/>
</Root>'

Declare @.iDoc as Int;
EXEC sp_xml_preparedocument @.iDoc OUTPUT, @.param;

SELECT *
FROM OPENXML (@.idoc, '/Root/Row',1)
WITH (Size varchar(10),
pieces Int,
sizeID Int,
sizecombID Int)

EXEC sp_xml_removedocument @.iDoc;

2. You can store the xml result in Table variable and you can validate those with your main tables.

(ex)

Code Snippet

Declare @.Input table
(
Size varchar(10),
pieces Int,
sizeID Int,
sizecombID Int
);
insert Into @.Input
SELECT *
FROM OPENXML (@.idoc, '/Root/Row',1)
WITH (Size varchar(10),
pieces Int,
sizeID Int,
sizecombID Int)

3. This can be achived Max(id) + 1 but you have to take care the concurrent users issue.

|||

appreicate your reply on this, i have few more questions :-

1. Is xml the only way to acheive this, can't I pass a set of rows directly to stored procedure without using xml.

2. once all the records are available inside stored proc, do i have to loop through all records using cursor or temp table or i can get the results directly through a sql statement

3. I also thought of Max(id) but bcos of concurrency issues, i was hesitant. Can you suggest any other way ? i just want the value unique not necessarily it should be in sequence (i don't want to use guid)

4. Do you think that i should change my table structure to make it as a xml field for sizes instead of multiple rows, just wanted to know some suggestion on that..

5. how do u get these code snippet blocks in your post, these are very nice.

|||

1. No SQL Server wont support parameter as array value - it should be csv or xml

2. You can get the results directly from the SQL, most of the cursor used batches we can rewrite using SQL queries.

3. Then you can have a lookup table to fetch the unique id (SizeCombo Master table with Identity value) insert the new sizecombo on master table then use the @.@.IDENTITY / SCOPE_IDENTITY on your other detailed table.

4. Don't store your data as XML it will be very difficult to maintaine & manupulations , store the data as rows

5. Its there in the forum text editor itself (next to the Smily Icon with green color {})

|||

Hopefully last question..

Regarding answer to point 2, can u pls provide me the sql query which can search and return sizecombID from a table...

Check log

Hi all,
where / which table could i check the access information from the MSSQL
(i.e. which PC sent query request to my SQL server with the domain login ID,
IP, time...etc..
Thanks a lot
Rena.Rena,
This information is not logged in any table by default. You should read
about SQL Profiler and SQL traces to see how you might capture this
information. After that you may have more specific questions.
Russell Fields
"Rena" <Rena@.mail.hongkong.com> wrote in message
news:Ob1DDbEZEHA.808@.tk2msftngp13.phx.gbl...
> Hi all,
> where / which table could i check the access information from the
MSSQL
> (i.e. which PC sent query request to my SQL server with the domain login
ID,
> IP, time...etc..
> Thanks a lot
> Rena.
>|||Hi,
Have a look into the below procedures as well
sp_who
sp_who2
and the master..sysprocesses system table.
Note:
All the above contains the information of currently connected users.
Thanks
Hari
MCDBA
"Rena" <Rena@.mail.hongkong.com> wrote in message
news:Ob1DDbEZEHA.808@.tk2msftngp13.phx.gbl...
> Hi all,
> where / which table could i check the access information from the
MSSQL
> (i.e. which PC sent query request to my SQL server with the domain login
ID,
> IP, time...etc..
> Thanks a lot
> Rena.
>|||Thx Russell and Hari,
as I need to keep track whether my SQL Server in my Office is access by
hostile PC / outsider, so what should be done, or if there are any turn
around stuff to be done on SQL?
Profiler might keep a hug lock even open for one night, and i do not know
which event class is the most effiecient one in this case as I am not
familiar :p
Rena.
Thx.
"Russell Fields" <RussellFields@.NoMailPlease.Com> ¦b¶l¥ó
news:ew1jNCGZEHA.3168@.TK2MSFTNGP10.phx.gbl ¤¤¼¶¼g...
> Rena,
> This information is not logged in any table by default. You should read
> about SQL Profiler and SQL traces to see how you might capture this
> information. After that you may have more specific questions.
> Russell Fields
> "Rena" <Rena@.mail.hongkong.com> wrote in message
> news:Ob1DDbEZEHA.808@.tk2msftngp13.phx.gbl...
> > Hi all,
> > where / which table could i check the access information from the
> MSSQL
> > (i.e. which PC sent query request to my SQL server with the domain login
> ID,
> > IP, time...etc..
> >
> > Thanks a lot
> > Rena.
> >
> >
>|||Rena,
Profiler will not keep a huge lock, but it does require some resources. You
might try tracing the Audit Login / Logout events and, if you are interested
in what SQL is running, the SQL:BatchCompleted.
A lighter weight look might serve you as well using sp_who2 to find out who
is active at whatever interval you run it.
sp_who2 active -- only lists the active connections
You can get the results of either approach sent to your file system or to an
internal table. (For sp_who2 use osql to run a looping script in order to
send the results to a file. For Profiler, use the trace properties.)
Tracing to a table adds to the overhead and is generally recommended
against, so it depends out what your system constraints are.
Russell Fields
"Rena" <Rena@.mail.hongkong.com> wrote in message
news:uTVn7XRZEHA.3144@.TK2MSFTNGP12.phx.gbl...
> Thx Russell and Hari,
> as I need to keep track whether my SQL Server in my Office is access
by
> hostile PC / outsider, so what should be done, or if there are any turn
> around stuff to be done on SQL?
> Profiler might keep a hug lock even open for one night, and i do not know
> which event class is the most effiecient one in this case as I am not
> familiar :p
> Rena.
> Thx.
>
> "Russell Fields" <RussellFields@.NoMailPlease.Com> ¦b¶l¥ó
> news:ew1jNCGZEHA.3168@.TK2MSFTNGP10.phx.gbl ¤¤¼¶¼g...
> > Rena,
> >
> > This information is not logged in any table by default. You should read
> > about SQL Profiler and SQL traces to see how you might capture this
> > information. After that you may have more specific questions.
> >
> > Russell Fields
> > "Rena" <Rena@.mail.hongkong.com> wrote in message
> > news:Ob1DDbEZEHA.808@.tk2msftngp13.phx.gbl...
> > > Hi all,
> > > where / which table could i check the access information from the
> > MSSQL
> > > (i.e. which PC sent query request to my SQL server with the domain
login
> > ID,
> > > IP, time...etc..
> > >
> > > Thanks a lot
> > > Rena.
> > >
> > >
> >
> >
>|||Thx. alot :)
"Russell Fields" <RussellFields@.NoMailPlease.Com> ¦b¶l¥ó
news:uoVJSUfZEHA.4092@.TK2MSFTNGP11.phx.gbl ¤¤¼¶¼g...
> Rena,
> Profiler will not keep a huge lock, but it does require some resources.
You
> might try tracing the Audit Login / Logout events and, if you are
interested
> in what SQL is running, the SQL:BatchCompleted.
> A lighter weight look might serve you as well using sp_who2 to find out
who
> is active at whatever interval you run it.
> sp_who2 active -- only lists the active connections
> You can get the results of either approach sent to your file system or to
an
> internal table. (For sp_who2 use osql to run a looping script in order to
> send the results to a file. For Profiler, use the trace properties.)
> Tracing to a table adds to the overhead and is generally recommended
> against, so it depends out what your system constraints are.
> Russell Fields
> "Rena" <Rena@.mail.hongkong.com> wrote in message
> news:uTVn7XRZEHA.3144@.TK2MSFTNGP12.phx.gbl...
> > Thx Russell and Hari,
> > as I need to keep track whether my SQL Server in my Office is access
> by
> > hostile PC / outsider, so what should be done, or if there are any turn
> > around stuff to be done on SQL?
> >
> > Profiler might keep a hug lock even open for one night, and i do not
know
> > which event class is the most effiecient one in this case as I am not
> > familiar :p
> >
> > Rena.
> > Thx.
> >
> >
> > "Russell Fields" <RussellFields@.NoMailPlease.Com> ¦b¶l¥ó
> > news:ew1jNCGZEHA.3168@.TK2MSFTNGP10.phx.gbl ¤¤¼¶¼g...
> > > Rena,
> > >
> > > This information is not logged in any table by default. You should
read
> > > about SQL Profiler and SQL traces to see how you might capture this
> > > information. After that you may have more specific questions.
> > >
> > > Russell Fields
> > > "Rena" <Rena@.mail.hongkong.com> wrote in message
> > > news:Ob1DDbEZEHA.808@.tk2msftngp13.phx.gbl...
> > > > Hi all,
> > > > where / which table could i check the access information from
the
> > > MSSQL
> > > > (i.e. which PC sent query request to my SQL server with the domain
> login
> > > ID,
> > > > IP, time...etc..
> > > >
> > > > Thanks a lot
> > > > Rena.
> > > >
> > > >
> > >
> > >
> >
> >
>

Check log

"Russell Fields" <RussellFields@.NoMailPlease.Com> bl
news:uoVJSUfZEHA.4092@.TK2MSFTNGP11.phx.gbl g...
> Rena,
> Profiler will not keep a huge lock, but it does require some resources.
You
> might try tracing the Audit Login / Logout events and, if you are
interested
> in what SQL is running, the SQL:BatchCompleted.
> A lighter weight look might serve you as well using sp_who2 to find out
who
> is active at whatever interval you run it.
> sp_who2 active -- only lists the active connections
> You can get the results of either approach sent to your file system or to
an
> internal table. (For sp_who2 use osql to run a looping script in order to
> send the results to a file. For Profiler, use the trace properties.)
> Tracing to a table adds to the overhead and is generally recommended
> against, so it depends out what your system constraints are.
> Russell Fields
> "Rena" <Rena@.mail.hongkong.com> wrote in message
> news:uTVn7XRZEHA.3144@.TK2MSFTNGP12.phx.gbl...
> by
know[vbcol=seagreen]
read[vbcol=seagreen]
the[vbcol=seagreen]
> login
>

Check log

"Russell Fields" <RussellFields@.NoMailPlease.Com> bl
news:uoVJSUfZEHA.4092@.TK2MSFTNGP11.phx.gbl g...
> Rena,
> Profiler will not keep a huge lock, but it does require some resources.
You
> might try tracing the Audit Login / Logout events and, if you are
interested
> in what SQL is running, the SQL:BatchCompleted.
> A lighter weight look might serve you as well using sp_who2 to find out
who
> is active at whatever interval you run it.
> sp_who2 active -- only lists the active connections
> You can get the results of either approach sent to your file system or to
an[vbcol=seagreen]
> internal table. (For sp_who2 use osql to run a looping script in order to
> send the results to a file. For Profiler, use the trace properties.)
> Tracing to a table adds to the overhead and is generally recommended
> against, so it depends out what your system constraints are.
> Russell Fields
> "Rena" <Rena@.mail.hongkong.com> wrote in message
> news:uTVn7XRZEHA.3144@.TK2MSFTNGP12.phx.gbl...
> by
know[vbcol=seagreen]
read[vbcol=seagreen]
the
> login
>

Thursday, March 22, 2012

Check if there is trigger

Hi,

How can I check if there is trigger for one table, and if yes disable?

if ( there is trigger in cm_template = yes )

ALTERTABLE cm_template DISABLETRIGGER TRG_cm_template

else

do stuff...

Since I try disable one trigger in a table which there is not trigger I think I'll receive one error. am I right?

cheers,

If you are using 2005, this is really easy:

select object_name(parent_id) as table_name, name as trigger_name, is_disabled
from sys.triggers

For 2000, you can use:

SELECT OBJECT_NAME (parent_obj), name,
OBJECTPROPERTY (id, 'ExecIsTriggerDisabled') as is_disabled
FROM sysobjects
WHERE type = 'TR'

Credit: By Srinivas Sampath MCSE, MVP (SQL Server) in his article: http://sqljunkies.com/Article/F5783CE9-BA74-4657-BF9D-1139BCC3096B.scuk

sql

Check if temporary table exists

Hello.

How can I check if a temporary table exists in the current context?

With normal tables I'd do a

EXISTS ( SELECT name FROM sysobjects

WHERE name='myTableName' AND type='U')

However, I can't do that with a temporary table. I'd have to go look at the sysobjects table in the tempdb database.

The problem is that for temporary tables, a suffix is added to the name to make it unique for each scope. I can't change the WHERE clause to name LIKE 'myTableName%' because this would return true if a temporary table with the same name exists in a different scope.

Any ideas?

Carlos

You can try the following

IF object_id('tempdb..#MyTempTable') IS NOT NULL
BEGIN
DROP TABLE #MyTempTable
END

CREATE TABLE #MyTempTable
(
ID int IDENTITY(1,1),
SomeValue varchar(100)
)
GO

|||IF OBJECT_ID('tempdb..#MyTempTable', 'U') IS NOT NULL
Print 'Yes'
Else
Print 'No'sql

Check if Table is empty

Hi,
I would like to do a check in a SP if a table is empty.
How do i do that?
Thanksif exists(select * from <your table>)
select 'not empty'
else
select 'empty'|||Originally posted by Paul Young
if exists(select * from <your table>)
select 'not empty'
else
select 'empty'

Great..
Thaks

Check if record has dependencies

I have a database where one table 'Project' has a one to many relationship
with several other tables. Other than doing multiple Select queries, is
there a simple quick way of testing to see if there are any dependencies in
the tables connect with the constraints
Cheerstry this:
sp_help <table>
--
current location: alicante (es)
"Newbie" wrote:

> I have a database where one table 'Project' has a one to many relationship
> with several other tables. Other than doing multiple Select queries, is
> there a simple quick way of testing to see if there are any dependencies i
n
> the tables connect with the constraints
> Cheers
>
>|||What ?,
were you replying to someone else by mistake, this makes absolutely no sense
to me whatsoever !
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:2BF857E7-DDE1-4AFF-AA13-965C3573162B@.microsoft.com...
> try this:
> sp_help <table>
> --
> current location: alicante (es)
>
> "Newbie" wrote:
>|||Basically you want to do a outer left join with any related tables. Let's
assume that the Project table has related tables Tasks and Notes. The
following will count the number of projects that have at least one task or
note:
select
count(distinct Project.ProjectID) as CountAssignedProjects
from Project
left join Tasks on Tasks.ProjectID = Project.ProjectID
left join Notes on Notes.ProjectID = Project.ProjectID
where
Tasks.ProjectID is not null or
Notes.ProjectID is not null
"Newbie" <me@.me.com> wrote in message
news:%23yfFEoBSGHA.4792@.TK2MSFTNGP14.phx.gbl...
>I have a database where one table 'Project' has a one to many relationship
>with several other tables. Other than doing multiple Select queries, is
>there a simple quick way of testing to see if there are any dependencies in
>the tables connect with the constraints
> Cheers
>|||I'm sorry I was wrong. I though that you are looking for the current
references for a table and using sp_help such request is returned...
--
current location: alicante (es)
"Newbie" wrote:

> What ?,
> were you replying to someone else by mistake, this makes absolutely no sen
se
> to me whatsoever !
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:2BF857E7-DDE1-4AFF-AA13-965C3573162B@.microsoft.com...
>
>

Tuesday, March 27, 2012

Check Roles assign to user

Check Roles assign to user

Check Roles assign to user

Check Recovery Model

Check Recovery Model

Check Recovery Model

check previous row in the table

Sunday, March 25, 2012

Check on the tables about last modification!

Check on the tables about last modification!

Check on the tables about last modification!

Check null value of long data type

check my SP [its not working fine]

Check multiple records before inserting records

Check log

Check log

Check log

Thursday, March 22, 2012

Check if there is trigger

Check if temporary table exists

Check if Table is empty

Check if record has dependencies

Char and Varchar

Blog Archive

About Me