Hi,
I got a problem while testing the BEGIN TRAN.. COMMIT TRAN. I have created
3 tables and 3 sps for inserting values into the table.
Now I ran a query to call the 3 sps inside a Begi.. commit tranaction which
is given below (of course there is WAITFOR DELAY statement in between the
first to sp calls), but before executing completely i disaqbled the network.
When i reconnect it and checked the values in the tables, i found the
entries. i wonder how this happened before committing the operation. Can
anyone help me. The code is given below
regards Lara
CREATE TABLE [TableOne] (
[a] [int] IDENTITY (1, 1) NOT NULL ,
[atext] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [TableThree] (
[c] [int] IDENTITY (1, 1) NOT NULL ,
[ctext] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [TableTwo] (
[b] [int] IDENTITY (1, 1) NOT NULL ,
[btext] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE PROCEDURE InsertA
@.x varchar(32)
as
insert into TableOne select @.x
GO
CREATE PROCEDURE InsertB
@.x varchar(32)
as
BEGIN
--WAITFOR DELAY '000:00:59'
INSERT INTO TableTwo select @.x
END
GO
CREATE PROCEDURE InsertC
@.x varchar(32)
AS
INSERT INTO TableThree select @.x
GO
BEGIN TRAN
EXEC insertA 'A'
WAITFOR DELAY '000:00:59'
EXEC insertB 'B'
EXEC insertC 'C'
COMMIT TRANI assume you found data in all three tables? In this case, the entire batch
was sent to SQL Server where it executed to completion event though the
client was unplugged from the network. It's just that SQL Server couldn't
notify the client after the transaction completed successfully.
Hope this helps.
Dan Guzman
SQL Server MVP
"Lara" <aneeshattingal@.hotpop.com> wrote in message
news:OEZYoCmQFHA.2384@.tk2msftngp13.phx.gbl...
> Hi,
> I got a problem while testing the BEGIN TRAN.. COMMIT TRAN. I have
> created
> 3 tables and 3 sps for inserting values into the table.
> Now I ran a query to call the 3 sps inside a Begi.. commit tranaction
> which
> is given below (of course there is WAITFOR DELAY statement in between the
> first to sp calls), but before executing completely i disaqbled the
> network.
> When i reconnect it and checked the values in the tables, i found the
> entries. i wonder how this happened before committing the operation. Can
> anyone help me. The code is given below
> regards Lara
>
> CREATE TABLE [TableOne] (
> [a] [int] IDENTITY (1, 1) NOT NULL ,
> [atext] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [TableThree] (
> [c] [int] IDENTITY (1, 1) NOT NULL ,
> [ctext] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [TableTwo] (
> [b] [int] IDENTITY (1, 1) NOT NULL ,
> [btext] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> CREATE PROCEDURE InsertA
> @.x varchar(32)
> as
> insert into TableOne select @.x
> GO
> CREATE PROCEDURE InsertB
> @.x varchar(32)
> as
> BEGIN
> --WAITFOR DELAY '000:00:59'
> INSERT INTO TableTwo select @.x
> END
> GO
> CREATE PROCEDURE InsertC
> @.x varchar(32)
> AS
> INSERT INTO TableThree select @.x
> GO
> BEGIN TRAN
> EXEC insertA 'A'
> WAITFOR DELAY '000:00:59'
> EXEC insertB 'B'
> EXEC insertC 'C'
> COMMIT TRAN
>
Showing posts with label query. Show all posts
Showing posts with label query. Show all posts
Friday, March 23, 2012
Pb table scan
I have a query like this :
select * from table
where champs1=val1 and champs2 =val2
I have an index on (champs1,champs2).
The table has 698694 rows.
The table scan cost 17770 i/os.
If the number of rows selected is greater than 4700, the optimizer choice
the table scan. If i force the index, i have 4762 i/os ( is less than
17770 )
I don't know why the optimizer choice the table scan ( is there a traceon,
command like the dbcc traceon(302) in sybase, that help you to understand
the choice of the optimizer ? ).
Thanks and sorry for my poor english.Have you tried clustering your index?
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>|||Execute this from Query Analyzer and look at the query plan. It will show
you the details... Then force the index you think is best and execute again,
compare the two.
You might try to update statistics on the table,
or drop /recreate the indices on the table and try again.
Generally however if a query returns more than 3-10% of the rows in the
table, the optimizer will choose a table scan.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>|||One possible reason is outdated statistics. Did you try UPDATE STATISTICS?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message news:1084801423.162182@.atn
04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>|||there is a primary key but not on this rows
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> a crit dans le
message de news:OIe9eACPEHA.3020@.tk2msftngp13.phx.gbl...
> Have you tried clustering your index?
> "ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
> news:1084801423.162182@.atn04...
choice[vbcol=seagreen]
traceon,[vbcol=seagreen]
understand[vbcol=seagreen]
>
select * from table
where champs1=val1 and champs2 =val2
I have an index on (champs1,champs2).
The table has 698694 rows.
The table scan cost 17770 i/os.
If the number of rows selected is greater than 4700, the optimizer choice
the table scan. If i force the index, i have 4762 i/os ( is less than
17770 )
I don't know why the optimizer choice the table scan ( is there a traceon,
command like the dbcc traceon(302) in sybase, that help you to understand
the choice of the optimizer ? ).
Thanks and sorry for my poor english.Have you tried clustering your index?
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>|||Execute this from Query Analyzer and look at the query plan. It will show
you the details... Then force the index you think is best and execute again,
compare the two.
You might try to update statistics on the table,
or drop /recreate the indices on the table and try again.
Generally however if a query returns more than 3-10% of the rows in the
table, the optimizer will choose a table scan.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>|||One possible reason is outdated statistics. Did you try UPDATE STATISTICS?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message news:1084801423.162182@.atn
04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>|||there is a primary key but not on this rows
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> a crit dans le
message de news:OIe9eACPEHA.3020@.tk2msftngp13.phx.gbl...
> Have you tried clustering your index?
> "ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
> news:1084801423.162182@.atn04...
choice[vbcol=seagreen]
traceon,[vbcol=seagreen]
understand[vbcol=seagreen]
>
Pb table scan
I have a query like this :
select * from table
where champs1=val1 and champs2 =val2
I have an index on (champs1,champs2).
The table has 698694 rows.
The table scan cost 17770 i/os.
If the number of rows selected is greater than 4700, the optimizer choice
the table scan. If i force the index, i have 4762 i/os ( is less than
17770 )
I don't know why the optimizer choice the table scan ( is there a traceon,
command like the dbcc traceon(302) in sybase, that help you to understand
the choice of the optimizer ? ).
Thanks and sorry for my poor english.Have you tried clustering your index?
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>|||Execute this from Query Analyzer and look at the query plan. It will show
you the details... Then force the index you think is best and execute again,
compare the two.
You might try to update statistics on the table,
or drop /recreate the indices on the table and try again.
Generally however if a query returns more than 3-10% of the rows in the
table, the optimizer will choose a table scan.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>|||One possible reason is outdated statistics. Did you try UPDATE STATISTICS?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>|||there is a primary key but not on this rows
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> a écrit dans le
message de news:OIe9eACPEHA.3020@.tk2msftngp13.phx.gbl...
> Have you tried clustering your index?
> "ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
> news:1084801423.162182@.atn04...
> > I have a query like this :
> >
> > select * from table
> > where champs1=val1 and champs2 =val2
> > I have an index on (champs1,champs2).
> > The table has 698694 rows.
> > The table scan cost 17770 i/os.
> >
> > If the number of rows selected is greater than 4700, the optimizer
choice
> > the table scan. If i force the index, i have 4762 i/os ( is less than
> > 17770 )
> >
> > I don't know why the optimizer choice the table scan ( is there a
traceon,
> > command like the dbcc traceon(302) in sybase, that help you to
understand
> > the choice of the optimizer ? ).
> >
> > Thanks and sorry for my poor english.
> >
> >
> >
> >
> >
> >
>
select * from table
where champs1=val1 and champs2 =val2
I have an index on (champs1,champs2).
The table has 698694 rows.
The table scan cost 17770 i/os.
If the number of rows selected is greater than 4700, the optimizer choice
the table scan. If i force the index, i have 4762 i/os ( is less than
17770 )
I don't know why the optimizer choice the table scan ( is there a traceon,
command like the dbcc traceon(302) in sybase, that help you to understand
the choice of the optimizer ? ).
Thanks and sorry for my poor english.Have you tried clustering your index?
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>|||Execute this from Query Analyzer and look at the query plan. It will show
you the details... Then force the index you think is best and execute again,
compare the two.
You might try to update statistics on the table,
or drop /recreate the indices on the table and try again.
Generally however if a query returns more than 3-10% of the rows in the
table, the optimizer will choose a table scan.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>|||One possible reason is outdated statistics. Did you try UPDATE STATISTICS?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>|||there is a primary key but not on this rows
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> a écrit dans le
message de news:OIe9eACPEHA.3020@.tk2msftngp13.phx.gbl...
> Have you tried clustering your index?
> "ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
> news:1084801423.162182@.atn04...
> > I have a query like this :
> >
> > select * from table
> > where champs1=val1 and champs2 =val2
> > I have an index on (champs1,champs2).
> > The table has 698694 rows.
> > The table scan cost 17770 i/os.
> >
> > If the number of rows selected is greater than 4700, the optimizer
choice
> > the table scan. If i force the index, i have 4762 i/os ( is less than
> > 17770 )
> >
> > I don't know why the optimizer choice the table scan ( is there a
traceon,
> > command like the dbcc traceon(302) in sybase, that help you to
understand
> > the choice of the optimizer ? ).
> >
> > Thanks and sorry for my poor english.
> >
> >
> >
> >
> >
> >
>
Pb table scan
I have a query like this :
select * from table
where champs1=val1 and champs2 =val2
I have an index on (champs1,champs2).
The table has 698694 rows.
The table scan cost 17770 i/os.
If the number of rows selected is greater than 4700, the optimizer choice
the table scan. If i force the index, i have 4762 i/os ( is less than
17770 )
I don't know why the optimizer choice the table scan ( is there a traceon,
command like the dbcc traceon(302) in sybase, that help you to understand
the choice of the optimizer ? ).
Thanks and sorry for my poor english.
Have you tried clustering your index?
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>
|||Execute this from Query Analyzer and look at the query plan. It will show
you the details... Then force the index you think is best and execute again,
compare the two.
You might try to update statistics on the table,
or drop /recreate the indices on the table and try again.
Generally however if a query returns more than 3-10% of the rows in the
table, the optimizer will choose a table scan.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>
|||One possible reason is outdated statistics. Did you try UPDATE STATISTICS?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>
|||there is a primary key but not on this rows
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> a crit dans le
message de news:OIe9eACPEHA.3020@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Have you tried clustering your index?
> "ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
> news:1084801423.162182@.atn04...
choice[vbcol=seagreen]
traceon,[vbcol=seagreen]
understand
>
sql
select * from table
where champs1=val1 and champs2 =val2
I have an index on (champs1,champs2).
The table has 698694 rows.
The table scan cost 17770 i/os.
If the number of rows selected is greater than 4700, the optimizer choice
the table scan. If i force the index, i have 4762 i/os ( is less than
17770 )
I don't know why the optimizer choice the table scan ( is there a traceon,
command like the dbcc traceon(302) in sybase, that help you to understand
the choice of the optimizer ? ).
Thanks and sorry for my poor english.
Have you tried clustering your index?
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>
|||Execute this from Query Analyzer and look at the query plan. It will show
you the details... Then force the index you think is best and execute again,
compare the two.
You might try to update statistics on the table,
or drop /recreate the indices on the table and try again.
Generally however if a query returns more than 3-10% of the rows in the
table, the optimizer will choose a table scan.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>
|||One possible reason is outdated statistics. Did you try UPDATE STATISTICS?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>
|||there is a primary key but not on this rows
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> a crit dans le
message de news:OIe9eACPEHA.3020@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Have you tried clustering your index?
> "ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
> news:1084801423.162182@.atn04...
choice[vbcol=seagreen]
traceon,[vbcol=seagreen]
understand
>
sql
Tuesday, March 20, 2012
Patient query... Pls help
Hi Guys,
I have got a table of patients where each patient has got a unique patient number.There are different services which a patient can undertake such as resthome, dementia etc.
Here is the sample data
Service patientNo startDate EndDate
resthome 12 01/04/2003 03/05/2003
resthome 13 12/9/2004 13/10/2006
Dementia 44 12/08/2002 13/01/2004
dementia 12 05/05/2003 06/12/2006
................................. ............ ................................
Each patient has got a start date when he started undertaking that service and end date when the service finished .
Now I need to count total patients for each catagory but here is the main issue....
A patient can move from one servcice to another but cant be in two services at the same time and I need to count the most recent service which the patient is currently enrolled in..
How do I do that?
Pls help me guys as you have always done:wow:
Thanks
This will get you a list of the most recent service by startdate that the patient was enrolled in
Code Snippet
select ps.*
from patientservice ps
inner join
(select patientno, max(startdate) as startdate
from patientservices
group by patientno) z
on z.patientno = ps.patientno
and z.startdate = ps.startdate
|||Hi Cam
Thanks for your help
I ran this query but I dont know why is it giving me an error saying:
Invalid column name: startdate
Thanks
Path of the backup file
Hi All,
Can anyone tell me if there is any system table/view/SP available where I
can query to view the path of a database backup file when the last database
backup is taken?
For example I can find the backup information(like backup begin start date,
backup finish date, etc) of a database using msdb.dbo.backupset. I am
looking for something similar query where I can see the location, either to
tape, device, or any local directory, of the backup.
Thanks a lot,
VM
Take a look at the backupmediafamily table in msdb. The
columns Physical_device_name and device_type tell you what
device and path was used for the backup.
-Sue
On Mon, 16 Jan 2006 11:38:02 -0800, "VM"
<VM@.discussions.microsoft.com> wrote:
>Hi All,
>Can anyone tell me if there is any system table/view/SP available where I
>can query to view the path of a database backup file when the last database
>backup is taken?
>For example I can find the backup information(like backup begin start date,
>backup finish date, etc) of a database using msdb.dbo.backupset. I am
>looking for something similar query where I can see the location, either to
>tape, device, or any local directory, of the backup.
>Thanks a lot,
>VM
|||There is a child table of backupset called backupfile that contains the
information you need. Look up System tables in BOL. Choose All Databases
as the subtopic. Scroll down to the section called "Tables in the MSDB
database" for more details.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"VM" <VM@.discussions.microsoft.com> wrote in message
news:A0E40887-16A5-4128-8AEE-DE5955C79860@.microsoft.com...
> Hi All,
> Can anyone tell me if there is any system table/view/SP available where I
> can query to view the path of a database backup file when the last
> database
> backup is taken?
> For example I can find the backup information(like backup begin start
> date,
> backup finish date, etc) of a database using msdb.dbo.backupset. I am
> looking for something similar query where I can see the location, either
> to
> tape, device, or any local directory, of the backup.
> Thanks a lot,
> VM
|||Thanks Sue! It worked.
VM
"Sue Hoegemeier" wrote:
> Take a look at the backupmediafamily table in msdb. The
> columns Physical_device_name and device_type tell you what
> device and path was used for the backup.
> -Sue
> On Mon, 16 Jan 2006 11:38:02 -0800, "VM"
> <VM@.discussions.microsoft.com> wrote:
>
>
|||Thanks for Information Geoff. I was looking for backupmediafamily table.
VM
"Geoff N. Hiten" wrote:
> There is a child table of backupset called backupfile that contains the
> information you need. Look up System tables in BOL. Choose All Databases
> as the subtopic. Scroll down to the section called "Tables in the MSDB
> database" for more details.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "VM" <VM@.discussions.microsoft.com> wrote in message
> news:A0E40887-16A5-4128-8AEE-DE5955C79860@.microsoft.com...
>
>
|||Sue was right with backupmediafamily. I should have looked at my backup
management code instead of writig from memory. Either way, the information
in BOL is very useful.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"VM" <VM@.discussions.microsoft.com> wrote in message
news:5E526962-6D6A-4827-8EAE-FBA04E9EF8CD@.microsoft.com...[vbcol=seagreen]
> Thanks for Information Geoff. I was looking for backupmediafamily table.
> VM
> "Geoff N. Hiten" wrote:
Can anyone tell me if there is any system table/view/SP available where I
can query to view the path of a database backup file when the last database
backup is taken?
For example I can find the backup information(like backup begin start date,
backup finish date, etc) of a database using msdb.dbo.backupset. I am
looking for something similar query where I can see the location, either to
tape, device, or any local directory, of the backup.
Thanks a lot,
VM
Take a look at the backupmediafamily table in msdb. The
columns Physical_device_name and device_type tell you what
device and path was used for the backup.
-Sue
On Mon, 16 Jan 2006 11:38:02 -0800, "VM"
<VM@.discussions.microsoft.com> wrote:
>Hi All,
>Can anyone tell me if there is any system table/view/SP available where I
>can query to view the path of a database backup file when the last database
>backup is taken?
>For example I can find the backup information(like backup begin start date,
>backup finish date, etc) of a database using msdb.dbo.backupset. I am
>looking for something similar query where I can see the location, either to
>tape, device, or any local directory, of the backup.
>Thanks a lot,
>VM
|||There is a child table of backupset called backupfile that contains the
information you need. Look up System tables in BOL. Choose All Databases
as the subtopic. Scroll down to the section called "Tables in the MSDB
database" for more details.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"VM" <VM@.discussions.microsoft.com> wrote in message
news:A0E40887-16A5-4128-8AEE-DE5955C79860@.microsoft.com...
> Hi All,
> Can anyone tell me if there is any system table/view/SP available where I
> can query to view the path of a database backup file when the last
> database
> backup is taken?
> For example I can find the backup information(like backup begin start
> date,
> backup finish date, etc) of a database using msdb.dbo.backupset. I am
> looking for something similar query where I can see the location, either
> to
> tape, device, or any local directory, of the backup.
> Thanks a lot,
> VM
|||Thanks Sue! It worked.
VM
"Sue Hoegemeier" wrote:
> Take a look at the backupmediafamily table in msdb. The
> columns Physical_device_name and device_type tell you what
> device and path was used for the backup.
> -Sue
> On Mon, 16 Jan 2006 11:38:02 -0800, "VM"
> <VM@.discussions.microsoft.com> wrote:
>
>
|||Thanks for Information Geoff. I was looking for backupmediafamily table.
VM
"Geoff N. Hiten" wrote:
> There is a child table of backupset called backupfile that contains the
> information you need. Look up System tables in BOL. Choose All Databases
> as the subtopic. Scroll down to the section called "Tables in the MSDB
> database" for more details.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "VM" <VM@.discussions.microsoft.com> wrote in message
> news:A0E40887-16A5-4128-8AEE-DE5955C79860@.microsoft.com...
>
>
|||Sue was right with backupmediafamily. I should have looked at my backup
management code instead of writig from memory. Either way, the information
in BOL is very useful.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"VM" <VM@.discussions.microsoft.com> wrote in message
news:5E526962-6D6A-4827-8EAE-FBA04E9EF8CD@.microsoft.com...[vbcol=seagreen]
> Thanks for Information Geoff. I was looking for backupmediafamily table.
> VM
> "Geoff N. Hiten" wrote:
Path of the backup file
Hi All,
Can anyone tell me if there is any system table/view/SP available where I
can query to view the path of a database backup file when the last database
backup is taken?
For example I can find the backup information(like backup begin start date,
backup finish date, etc) of a database using msdb.dbo.backupset. I am
looking for something similar query where I can see the location, either to
tape, device, or any local directory, of the backup.
Thanks a lot,
VMTake a look at the backupmediafamily table in msdb. The
columns Physical_device_name and device_type tell you what
device and path was used for the backup.
-Sue
On Mon, 16 Jan 2006 11:38:02 -0800, "VM"
<VM@.discussions.microsoft.com> wrote:
>Hi All,
>Can anyone tell me if there is any system table/view/SP available where I
>can query to view the path of a database backup file when the last database
>backup is taken?
>For example I can find the backup information(like backup begin start date,
>backup finish date, etc) of a database using msdb.dbo.backupset. I am
>looking for something similar query where I can see the location, either to
>tape, device, or any local directory, of the backup.
>Thanks a lot,
>VM|||There is a child table of backupset called backupfile that contains the
information you need. Look up System tables in BOL. Choose All Databases
as the subtopic. Scroll down to the section called "Tables in the MSDB
database" for more details.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"VM" <VM@.discussions.microsoft.com> wrote in message
news:A0E40887-16A5-4128-8AEE-DE5955C79860@.microsoft.com...
> Hi All,
> Can anyone tell me if there is any system table/view/SP available where I
> can query to view the path of a database backup file when the last
> database
> backup is taken?
> For example I can find the backup information(like backup begin start
> date,
> backup finish date, etc) of a database using msdb.dbo.backupset. I am
> looking for something similar query where I can see the location, either
> to
> tape, device, or any local directory, of the backup.
> Thanks a lot,
> VM|||Thanks Sue! It worked.
VM
"Sue Hoegemeier" wrote:
> Take a look at the backupmediafamily table in msdb. The
> columns Physical_device_name and device_type tell you what
> device and path was used for the backup.
> -Sue
> On Mon, 16 Jan 2006 11:38:02 -0800, "VM"
> <VM@.discussions.microsoft.com> wrote:
>
>|||Thanks for Information Geoff. I was looking for backupmediafamily table.
VM
"Geoff N. Hiten" wrote:
> There is a child table of backupset called backupfile that contains the
> information you need. Look up System tables in BOL. Choose All Databases
> as the subtopic. Scroll down to the section called "Tables in the MSDB
> database" for more details.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "VM" <VM@.discussions.microsoft.com> wrote in message
> news:A0E40887-16A5-4128-8AEE-DE5955C79860@.microsoft.com...
>
>|||Sue was right with backupmediafamily. I should have looked at my backup
management code instead of writig from memory. Either way, the information
in BOL is very useful.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"VM" <VM@.discussions.microsoft.com> wrote in message
news:5E526962-6D6A-4827-8EAE-FBA04E9EF8CD@.microsoft.com...[vbcol=seagreen]
> Thanks for Information Geoff. I was looking for backupmediafamily table.
> VM
> "Geoff N. Hiten" wrote:
>
Can anyone tell me if there is any system table/view/SP available where I
can query to view the path of a database backup file when the last database
backup is taken?
For example I can find the backup information(like backup begin start date,
backup finish date, etc) of a database using msdb.dbo.backupset. I am
looking for something similar query where I can see the location, either to
tape, device, or any local directory, of the backup.
Thanks a lot,
VMTake a look at the backupmediafamily table in msdb. The
columns Physical_device_name and device_type tell you what
device and path was used for the backup.
-Sue
On Mon, 16 Jan 2006 11:38:02 -0800, "VM"
<VM@.discussions.microsoft.com> wrote:
>Hi All,
>Can anyone tell me if there is any system table/view/SP available where I
>can query to view the path of a database backup file when the last database
>backup is taken?
>For example I can find the backup information(like backup begin start date,
>backup finish date, etc) of a database using msdb.dbo.backupset. I am
>looking for something similar query where I can see the location, either to
>tape, device, or any local directory, of the backup.
>Thanks a lot,
>VM|||There is a child table of backupset called backupfile that contains the
information you need. Look up System tables in BOL. Choose All Databases
as the subtopic. Scroll down to the section called "Tables in the MSDB
database" for more details.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"VM" <VM@.discussions.microsoft.com> wrote in message
news:A0E40887-16A5-4128-8AEE-DE5955C79860@.microsoft.com...
> Hi All,
> Can anyone tell me if there is any system table/view/SP available where I
> can query to view the path of a database backup file when the last
> database
> backup is taken?
> For example I can find the backup information(like backup begin start
> date,
> backup finish date, etc) of a database using msdb.dbo.backupset. I am
> looking for something similar query where I can see the location, either
> to
> tape, device, or any local directory, of the backup.
> Thanks a lot,
> VM|||Thanks Sue! It worked.
VM
"Sue Hoegemeier" wrote:
> Take a look at the backupmediafamily table in msdb. The
> columns Physical_device_name and device_type tell you what
> device and path was used for the backup.
> -Sue
> On Mon, 16 Jan 2006 11:38:02 -0800, "VM"
> <VM@.discussions.microsoft.com> wrote:
>
>|||Thanks for Information Geoff. I was looking for backupmediafamily table.
VM
"Geoff N. Hiten" wrote:
> There is a child table of backupset called backupfile that contains the
> information you need. Look up System tables in BOL. Choose All Databases
> as the subtopic. Scroll down to the section called "Tables in the MSDB
> database" for more details.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "VM" <VM@.discussions.microsoft.com> wrote in message
> news:A0E40887-16A5-4128-8AEE-DE5955C79860@.microsoft.com...
>
>|||Sue was right with backupmediafamily. I should have looked at my backup
management code instead of writig from memory. Either way, the information
in BOL is very useful.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"VM" <VM@.discussions.microsoft.com> wrote in message
news:5E526962-6D6A-4827-8EAE-FBA04E9EF8CD@.microsoft.com...[vbcol=seagreen]
> Thanks for Information Geoff. I was looking for backupmediafamily table.
> VM
> "Geoff N. Hiten" wrote:
>
Path of the backup file
Hi All,
Can anyone tell me if there is any system table/view/SP available where I
can query to view the path of a database backup file when the last database
backup is taken?
For example I can find the backup information(like backup begin start date,
backup finish date, etc) of a database using msdb.dbo.backupset. I am
looking for something similar query where I can see the location, either to
tape, device, or any local directory, of the backup.
Thanks a lot,
VMTake a look at the backupmediafamily table in msdb. The
columns Physical_device_name and device_type tell you what
device and path was used for the backup.
-Sue
On Mon, 16 Jan 2006 11:38:02 -0800, "VM"
<VM@.discussions.microsoft.com> wrote:
>Hi All,
>Can anyone tell me if there is any system table/view/SP available where I
>can query to view the path of a database backup file when the last database
>backup is taken?
>For example I can find the backup information(like backup begin start date,
>backup finish date, etc) of a database using msdb.dbo.backupset. I am
>looking for something similar query where I can see the location, either to
>tape, device, or any local directory, of the backup.
>Thanks a lot,
>VM|||There is a child table of backupset called backupfile that contains the
information you need. Look up System tables in BOL. Choose All Databases
as the subtopic. Scroll down to the section called "Tables in the MSDB
database" for more details.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"VM" <VM@.discussions.microsoft.com> wrote in message
news:A0E40887-16A5-4128-8AEE-DE5955C79860@.microsoft.com...
> Hi All,
> Can anyone tell me if there is any system table/view/SP available where I
> can query to view the path of a database backup file when the last
> database
> backup is taken?
> For example I can find the backup information(like backup begin start
> date,
> backup finish date, etc) of a database using msdb.dbo.backupset. I am
> looking for something similar query where I can see the location, either
> to
> tape, device, or any local directory, of the backup.
> Thanks a lot,
> VM|||Thanks Sue! It worked.
VM
"Sue Hoegemeier" wrote:
> Take a look at the backupmediafamily table in msdb. The
> columns Physical_device_name and device_type tell you what
> device and path was used for the backup.
> -Sue
> On Mon, 16 Jan 2006 11:38:02 -0800, "VM"
> <VM@.discussions.microsoft.com> wrote:
> >Hi All,
> >
> >Can anyone tell me if there is any system table/view/SP available where I
> >can query to view the path of a database backup file when the last database
> >backup is taken?
> >
> >For example I can find the backup information(like backup begin start date,
> >backup finish date, etc) of a database using msdb.dbo.backupset. I am
> >looking for something similar query where I can see the location, either to
> >tape, device, or any local directory, of the backup.
> >
> >Thanks a lot,
> >VM
>|||Thanks for Information Geoff. I was looking for backupmediafamily table.
VM
"Geoff N. Hiten" wrote:
> There is a child table of backupset called backupfile that contains the
> information you need. Look up System tables in BOL. Choose All Databases
> as the subtopic. Scroll down to the section called "Tables in the MSDB
> database" for more details.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "VM" <VM@.discussions.microsoft.com> wrote in message
> news:A0E40887-16A5-4128-8AEE-DE5955C79860@.microsoft.com...
> > Hi All,
> >
> > Can anyone tell me if there is any system table/view/SP available where I
> > can query to view the path of a database backup file when the last
> > database
> > backup is taken?
> >
> > For example I can find the backup information(like backup begin start
> > date,
> > backup finish date, etc) of a database using msdb.dbo.backupset. I am
> > looking for something similar query where I can see the location, either
> > to
> > tape, device, or any local directory, of the backup.
> >
> > Thanks a lot,
> > VM
>
>|||Sue was right with backupmediafamily. I should have looked at my backup
management code instead of writig from memory. Either way, the information
in BOL is very useful.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"VM" <VM@.discussions.microsoft.com> wrote in message
news:5E526962-6D6A-4827-8EAE-FBA04E9EF8CD@.microsoft.com...
> Thanks for Information Geoff. I was looking for backupmediafamily table.
> VM
> "Geoff N. Hiten" wrote:
>> There is a child table of backupset called backupfile that contains the
>> information you need. Look up System tables in BOL. Choose All
>> Databases
>> as the subtopic. Scroll down to the section called "Tables in the MSDB
>> database" for more details.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>>
>>
>> "VM" <VM@.discussions.microsoft.com> wrote in message
>> news:A0E40887-16A5-4128-8AEE-DE5955C79860@.microsoft.com...
>> > Hi All,
>> >
>> > Can anyone tell me if there is any system table/view/SP available where
>> > I
>> > can query to view the path of a database backup file when the last
>> > database
>> > backup is taken?
>> >
>> > For example I can find the backup information(like backup begin start
>> > date,
>> > backup finish date, etc) of a database using msdb.dbo.backupset. I am
>> > looking for something similar query where I can see the location,
>> > either
>> > to
>> > tape, device, or any local directory, of the backup.
>> >
>> > Thanks a lot,
>> > VM
>>
Can anyone tell me if there is any system table/view/SP available where I
can query to view the path of a database backup file when the last database
backup is taken?
For example I can find the backup information(like backup begin start date,
backup finish date, etc) of a database using msdb.dbo.backupset. I am
looking for something similar query where I can see the location, either to
tape, device, or any local directory, of the backup.
Thanks a lot,
VMTake a look at the backupmediafamily table in msdb. The
columns Physical_device_name and device_type tell you what
device and path was used for the backup.
-Sue
On Mon, 16 Jan 2006 11:38:02 -0800, "VM"
<VM@.discussions.microsoft.com> wrote:
>Hi All,
>Can anyone tell me if there is any system table/view/SP available where I
>can query to view the path of a database backup file when the last database
>backup is taken?
>For example I can find the backup information(like backup begin start date,
>backup finish date, etc) of a database using msdb.dbo.backupset. I am
>looking for something similar query where I can see the location, either to
>tape, device, or any local directory, of the backup.
>Thanks a lot,
>VM|||There is a child table of backupset called backupfile that contains the
information you need. Look up System tables in BOL. Choose All Databases
as the subtopic. Scroll down to the section called "Tables in the MSDB
database" for more details.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"VM" <VM@.discussions.microsoft.com> wrote in message
news:A0E40887-16A5-4128-8AEE-DE5955C79860@.microsoft.com...
> Hi All,
> Can anyone tell me if there is any system table/view/SP available where I
> can query to view the path of a database backup file when the last
> database
> backup is taken?
> For example I can find the backup information(like backup begin start
> date,
> backup finish date, etc) of a database using msdb.dbo.backupset. I am
> looking for something similar query where I can see the location, either
> to
> tape, device, or any local directory, of the backup.
> Thanks a lot,
> VM|||Thanks Sue! It worked.
VM
"Sue Hoegemeier" wrote:
> Take a look at the backupmediafamily table in msdb. The
> columns Physical_device_name and device_type tell you what
> device and path was used for the backup.
> -Sue
> On Mon, 16 Jan 2006 11:38:02 -0800, "VM"
> <VM@.discussions.microsoft.com> wrote:
> >Hi All,
> >
> >Can anyone tell me if there is any system table/view/SP available where I
> >can query to view the path of a database backup file when the last database
> >backup is taken?
> >
> >For example I can find the backup information(like backup begin start date,
> >backup finish date, etc) of a database using msdb.dbo.backupset. I am
> >looking for something similar query where I can see the location, either to
> >tape, device, or any local directory, of the backup.
> >
> >Thanks a lot,
> >VM
>|||Thanks for Information Geoff. I was looking for backupmediafamily table.
VM
"Geoff N. Hiten" wrote:
> There is a child table of backupset called backupfile that contains the
> information you need. Look up System tables in BOL. Choose All Databases
> as the subtopic. Scroll down to the section called "Tables in the MSDB
> database" for more details.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "VM" <VM@.discussions.microsoft.com> wrote in message
> news:A0E40887-16A5-4128-8AEE-DE5955C79860@.microsoft.com...
> > Hi All,
> >
> > Can anyone tell me if there is any system table/view/SP available where I
> > can query to view the path of a database backup file when the last
> > database
> > backup is taken?
> >
> > For example I can find the backup information(like backup begin start
> > date,
> > backup finish date, etc) of a database using msdb.dbo.backupset. I am
> > looking for something similar query where I can see the location, either
> > to
> > tape, device, or any local directory, of the backup.
> >
> > Thanks a lot,
> > VM
>
>|||Sue was right with backupmediafamily. I should have looked at my backup
management code instead of writig from memory. Either way, the information
in BOL is very useful.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"VM" <VM@.discussions.microsoft.com> wrote in message
news:5E526962-6D6A-4827-8EAE-FBA04E9EF8CD@.microsoft.com...
> Thanks for Information Geoff. I was looking for backupmediafamily table.
> VM
> "Geoff N. Hiten" wrote:
>> There is a child table of backupset called backupfile that contains the
>> information you need. Look up System tables in BOL. Choose All
>> Databases
>> as the subtopic. Scroll down to the section called "Tables in the MSDB
>> database" for more details.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>>
>>
>> "VM" <VM@.discussions.microsoft.com> wrote in message
>> news:A0E40887-16A5-4128-8AEE-DE5955C79860@.microsoft.com...
>> > Hi All,
>> >
>> > Can anyone tell me if there is any system table/view/SP available where
>> > I
>> > can query to view the path of a database backup file when the last
>> > database
>> > backup is taken?
>> >
>> > For example I can find the backup information(like backup begin start
>> > date,
>> > backup finish date, etc) of a database using msdb.dbo.backupset. I am
>> > looking for something similar query where I can see the location,
>> > either
>> > to
>> > tape, device, or any local directory, of the backup.
>> >
>> > Thanks a lot,
>> > VM
>>
Friday, March 9, 2012
Password Protect Crystal Report
Hi, everyone -
This may be a rather simple query, but I'm going to put it out there:
We're using Crystal Report Version 8.5 at work, and I have created several reports that I would like to password protect because of personnel issues. Can someone help me out in terms of how I can password protect a document in Crystal? Thanks so much............. :confused:As far as I know it's not possible.
This may be a rather simple query, but I'm going to put it out there:
We're using Crystal Report Version 8.5 at work, and I have created several reports that I would like to password protect because of personnel issues. Can someone help me out in terms of how I can password protect a document in Crystal? Thanks so much............. :confused:As far as I know it's not possible.
Saturday, February 25, 2012
Password Audit
Hello All,
I am in the process of auditing a SQL server and was checking for NULL
passwords. I ran the following query select name, password from
master..sysxlogins where password is NULL.
It returned the expected windows accounts with NULL passwords, but it also
returned two unknown accounts with NULL for the SID, Name, and Password, and
a status of 192.
Has anyone ever seen this before, and if so, what they might be?
Thanks
DanHi,
Have you tried with C2 Audit ? Password Audit from www.sqlsecurity.com ?
:-)
Regards
--
Andy Davis
Activecrypt Team
---
SQL Server Encryption Software
http://www.activecrypt.com
I am in the process of auditing a SQL server and was checking for NULL
passwords. I ran the following query select name, password from
master..sysxlogins where password is NULL.
It returned the expected windows accounts with NULL passwords, but it also
returned two unknown accounts with NULL for the SID, Name, and Password, and
a status of 192.
Has anyone ever seen this before, and if so, what they might be?
Thanks
DanHi,
Have you tried with C2 Audit ? Password Audit from www.sqlsecurity.com ?
:-)
Regards
--
Andy Davis
Activecrypt Team
---
SQL Server Encryption Software
http://www.activecrypt.com
password
Hi,
Is there any way to show one column(password) values as ***.
One of my table has password field, when you query the table it will give actual value, i wanted to show them as **** or with junk charecters.
FYI..column data type is varchar(50).
Thanks in advance.You could use Pwdencrypt and pwdcompare:
Pwdencrypt and pwdcompare are internal, undocumented functions that SQL Server uses to manage passwords. Pwdencrypt uses a one-way hash that takes a clear string and returns an encrypted version of that string. Pwdcompare compares an unencrypted string to its encrypted representation to see whether they match.
Script below shows how to save and compare passwords:
create table users(
id int identity,
username nvarchar(128) not null unique,
userpassword nvarchar(128) not null
)
insert users(username,userpassword)
select 'tom',pwdencrypt('tom2')
insert users(username,userpassword)
select 'brett',pwdencrypt('brett2')
select Id from users
where pwdcompare('tom2',userpassword)=1
and username='tom'
Id
----
1
(1 row(s) affected)
select Id from users
where pwdcompare('brett3',userpassword)=1
and username='brett'
Id
----
(0 row(s) affected)|||Thanks for your reply.
And is there any way to decrypt the password, if you loose your actual password?
Thanks|||Thanks for your reply.
And is there any way to decrypt the password, if you loose your actual password?
Thanks
Nope ;) just set up new one|||Yeah, the point is to encrypt the password before it is stored in your table. And if it could be decrypted, it wouldn't be very secure, right?
Actually, SQL Server's encryption method has been cracked, and decryption algorythms are available on the web. If you want more security, I have a one-way encryption function you are welcome to use.|||Any leads would be greatly appreciated!!
Thanks|||Here is my pasword encryption algorythm.
The other disadvantage of using SQL Server's PWD_ENCRYPT function is that Microsoft can and has changed the algorythm in subsequent releases of SQL Server, rendering all existing passwords useless.|||Just curious...what sql server security model are you using?
I'm always leary of Application level security that store passwords in the database.|||Mixed mode.
thanks
Is there any way to show one column(password) values as ***.
One of my table has password field, when you query the table it will give actual value, i wanted to show them as **** or with junk charecters.
FYI..column data type is varchar(50).
Thanks in advance.You could use Pwdencrypt and pwdcompare:
Pwdencrypt and pwdcompare are internal, undocumented functions that SQL Server uses to manage passwords. Pwdencrypt uses a one-way hash that takes a clear string and returns an encrypted version of that string. Pwdcompare compares an unencrypted string to its encrypted representation to see whether they match.
Script below shows how to save and compare passwords:
create table users(
id int identity,
username nvarchar(128) not null unique,
userpassword nvarchar(128) not null
)
insert users(username,userpassword)
select 'tom',pwdencrypt('tom2')
insert users(username,userpassword)
select 'brett',pwdencrypt('brett2')
select Id from users
where pwdcompare('tom2',userpassword)=1
and username='tom'
Id
----
1
(1 row(s) affected)
select Id from users
where pwdcompare('brett3',userpassword)=1
and username='brett'
Id
----
(0 row(s) affected)|||Thanks for your reply.
And is there any way to decrypt the password, if you loose your actual password?
Thanks|||Thanks for your reply.
And is there any way to decrypt the password, if you loose your actual password?
Thanks
Nope ;) just set up new one|||Yeah, the point is to encrypt the password before it is stored in your table. And if it could be decrypted, it wouldn't be very secure, right?
Actually, SQL Server's encryption method has been cracked, and decryption algorythms are available on the web. If you want more security, I have a one-way encryption function you are welcome to use.|||Any leads would be greatly appreciated!!
Thanks|||Here is my pasword encryption algorythm.
The other disadvantage of using SQL Server's PWD_ENCRYPT function is that Microsoft can and has changed the algorythm in subsequent releases of SQL Server, rendering all existing passwords useless.|||Just curious...what sql server security model are you using?
I'm always leary of Application level security that store passwords in the database.|||Mixed mode.
thanks
PASSWORD
I forgot my "sa" password for SQL2000. Is there some place to look to get
it back? (Enterprise Manager or Query Analizer) please help?This is a multi-part message in MIME format.
--=_NextPart_000_0167_01C3A9CF.CEBC57C0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
Unless you removed the BUILTIN\Administrators group from the sysadmin role,
you can connect as a local Windows administrator on the server and use
Windows authentication to log into SQL Server. At that time, you can reset
the sa password.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"matta25" <matta125work@.hotmail.com> wrote in message
news:#1wbjkfqDHA.2820@.TK2MSFTNGP10.phx.gbl...
I forgot my "sa" password for SQL2000. Is there some place to look to get
it back? (Enterprise Manager or Query Analizer) please help?
--=_NextPart_000_0167_01C3A9CF.CEBC57C0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Unless you removed the =BUILTIN\Administrators group from the sysadmin role, you can connect as a local Windows =administrator on the server and use Windows authentication to log into SQL =Server. At that time, you can reset the sa password.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"matta25"= wrote in message news:#1wbjkfqDHA.2820=@.TK2MSFTNGP10.phx.gbl...I forgot my "sa" password for SQL2000. Is there some place to look =to getit back? (Enterprise Manager or Query Analizer) please help?
--=_NextPart_000_0167_01C3A9CF.CEBC57C0--
it back? (Enterprise Manager or Query Analizer) please help?This is a multi-part message in MIME format.
--=_NextPart_000_0167_01C3A9CF.CEBC57C0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
Unless you removed the BUILTIN\Administrators group from the sysadmin role,
you can connect as a local Windows administrator on the server and use
Windows authentication to log into SQL Server. At that time, you can reset
the sa password.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"matta25" <matta125work@.hotmail.com> wrote in message
news:#1wbjkfqDHA.2820@.TK2MSFTNGP10.phx.gbl...
I forgot my "sa" password for SQL2000. Is there some place to look to get
it back? (Enterprise Manager or Query Analizer) please help?
--=_NextPart_000_0167_01C3A9CF.CEBC57C0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Unless you removed the =BUILTIN\Administrators group from the sysadmin role, you can connect as a local Windows =administrator on the server and use Windows authentication to log into SQL =Server. At that time, you can reset the sa password.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"matta25"
--=_NextPart_000_0167_01C3A9CF.CEBC57C0--
Monday, February 20, 2012
Passing table to MSSQL stored procedure
Hi all,
Any suggestions on the best way to pass a set of values to a stored
procedure that will use them in a query? Specifically, I want to call a
stored procedure from .NET that takes a set of phone numbers and returns a
resultset of records containing these numbers from a table. Is it possible
to pass a dataset/XML or something that would be interpreted as a table? Or
do I need to pass a string and convert it to a temporary table in the stored
procedure? Or...?
TIA,
JohnArrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
AMB
"John Spiegel" wrote:
> Hi all,
> Any suggestions on the best way to pass a set of values to a stored
> procedure that will use them in a query? Specifically, I want to call a
> stored procedure from .NET that takes a set of phone numbers and returns a
> resultset of records containing these numbers from a table. Is it possibl
e
> to pass a dataset/XML or something that would be interpreted as a table?
Or
> do I need to pass a string and convert it to a temporary table in the stor
ed
> procedure? Or...?
> TIA,
> John
>
>|||Thanks, Alejandro. Exactly the kind of stuff I was wondering.
- John
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:EFAFA1B0-C1A5-49F7-8764-6FAEF26CF493@.microsoft.com...
> Arrays and Lists in SQL Server
> http://www.sommarskog.se/arrays-in-sql.html
>
> AMB
>
> "John Spiegel" wrote:
>
Any suggestions on the best way to pass a set of values to a stored
procedure that will use them in a query? Specifically, I want to call a
stored procedure from .NET that takes a set of phone numbers and returns a
resultset of records containing these numbers from a table. Is it possible
to pass a dataset/XML or something that would be interpreted as a table? Or
do I need to pass a string and convert it to a temporary table in the stored
procedure? Or...?
TIA,
JohnArrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
AMB
"John Spiegel" wrote:
> Hi all,
> Any suggestions on the best way to pass a set of values to a stored
> procedure that will use them in a query? Specifically, I want to call a
> stored procedure from .NET that takes a set of phone numbers and returns a
> resultset of records containing these numbers from a table. Is it possibl
e
> to pass a dataset/XML or something that would be interpreted as a table?
Or
> do I need to pass a string and convert it to a temporary table in the stor
ed
> procedure? Or...?
> TIA,
> John
>
>|||Thanks, Alejandro. Exactly the kind of stuff I was wondering.
- John
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:EFAFA1B0-C1A5-49F7-8764-6FAEF26CF493@.microsoft.com...
> Arrays and Lists in SQL Server
> http://www.sommarskog.se/arrays-in-sql.html
>
> AMB
>
> "John Spiegel" wrote:
>
Passing SQL Clauses as Parameters to a Stored Procedure
Hi,
I have a complex SQL query against multiple tables that ideally would be best used as a stored procedure.
I know of and have read about creating named parameters and setting the parameters' values in ASP just before issuing an execute command via the Command object. What I'd like to do is assign the text of the filter and sort clauses to the parameters. Not the values, but the entire string, such as
"WHERE lastname LIKE 'A%' "
"ORDER BY lastname "
I need to assign the entire clause because the query may or may not use a particular clause.
I have experimented. However SQL treats the parameter as a literal string rather than part of the SQL query itself. Is there another technique that I may use to accomplish my goal?
Thanks in advance for your opinion, suggestion, criticism, etc.
--Tom.Dynamic SQL|||and look up SQL Injection
DECLARE @.SQL varchar(8000)
SELECT @.SQL = 'SELECT * FROM INFORMATION_SCHEMA'
EXEC(@.SQL)
You should use dynamic as a last reort...
What prob you trying to solve?
I have a complex SQL query against multiple tables that ideally would be best used as a stored procedure.
I know of and have read about creating named parameters and setting the parameters' values in ASP just before issuing an execute command via the Command object. What I'd like to do is assign the text of the filter and sort clauses to the parameters. Not the values, but the entire string, such as
"WHERE lastname LIKE 'A%' "
"ORDER BY lastname "
I need to assign the entire clause because the query may or may not use a particular clause.
I have experimented. However SQL treats the parameter as a literal string rather than part of the SQL query itself. Is there another technique that I may use to accomplish my goal?
Thanks in advance for your opinion, suggestion, criticism, etc.
--Tom.Dynamic SQL|||and look up SQL Injection
DECLARE @.SQL varchar(8000)
SELECT @.SQL = 'SELECT * FROM INFORMATION_SCHEMA'
EXEC(@.SQL)
You should use dynamic as a last reort...
What prob you trying to solve?
Subscribe to:
Posts (Atom)