Any way to run a invisible trace on Security Audit?

Is there anyway I could run an Audit trace on SQl Server which records SQL Server System Admin Login/Logout , failed login and machine names I don't want the trace window to show on screen however would like a file generated for later viewing. Also due to Firewall issues we have, We don't have SQL tools enabled to connect to that server.Yes, you can configure SQL 2000 Auditing (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_2ard.asp), up through the C2 level. Unfortunately there isn't a "cookbook" approach for doing it, you really need to understand what you are doing, and the consequences and benefits of each decision.

-PatP|||I found out . Thanks for reply. C2 auditing however is not needed . I found out that from SQL Profiler , you can script the whole trace as SQL and execute it in ISQLW . This gives back a Trace ID . Next you have to run sp_trace_Setstatus @.traceID, 1 to run it .

Thanks for responding . I appreciate that

Any way to pass command line variables to osql

I want to pass a command line values into an osql run stored procedure.
The commandline values should be are the values to put into the insert stored procedure that writes them to a table.
Is it possible to do this.if ur parameter is positioned 2nd then add this code in your command file.

<varname> is some variable name that you want to use to trap the parameter. lets say empid for instance

SET empid=%2

after this, the place where u wud be calling ur stored procedure use this syntax.

osql -S servername -l 60 -n -E -d dbname
-Q"EXEC sp123 @.Var1 = '%empid%'"

Any way to optimise this query?

Hi guys

Is there any way I can run this query faster? Should I take out the ORDER BY
clause? This is supposed to return 17,000 rows and takes around 30 minutes or
so. Is there no way at all to get this result faster?

select r.AttorneyName, r.sitename, r.applicationid, r.clientsurname, r.
clientinitials, r.clientidno, r.grantedamount, r.bankname,
r.accountnumber, r.status, r.grantdate, r.consultantname, r.propertyaddress,
r.erfdescription, r.commenthistory, br.expectedregdate
from bondtrak..rptdetail r
join ebondprd..bankresponse br
on br.applicationid = r.applicationid
where
r.rundate = '20051010'
and r.primarybankind = 'Y'
and r.status = 'granted'
and r.statusdate between '20020101' and '20050930'
and r.businessunit in ('bond choice', 'ppl')
order by r.sitename, r.consultantname, r.statusdate

Thanks for any help.
Driesen

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200510/1If you don't need the result ordered then definitely drop the ORDER BY.

Why do you want to return 17,000 rows in one hit anyway? Can't you
process the data server side?

--
David Portas
SQL Server MVP
--|||Do you have any usefull indexes on these tables?

"I sense many useless updates in you... Useless updates lead to
defragmentation... Defragmentation leads to downtime...Downtime leads
to suffering..Defragmentation is the path to the darkside.. DBCC
INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with
you" -- http://sqlservercode.blogspot.com/|||Driesen via SQLMonster.com (u11907@.uwe) writes:
> Is there any way I can run this query faster? Should I take out the
> ORDER BY clause? This is supposed to return 17,000 rows and takes around
> 30 minutes or so. Is there no way at all to get this result faster?
> select r.AttorneyName, r.sitename, r.applicationid, r.clientsurname, r.
> clientinitials, r.clientidno, r.grantedamount, r.bankname,
> r.accountnumber, r.status, r.grantdate, r.consultantname,
> r.propertyaddress,
> r.erfdescription, r.commenthistory, br.expectedregdate
> from bondtrak..rptdetail r
> join ebondprd..bankresponse br
> on br.applicationid = r.applicationid
> where
> r.rundate = '20051010'
> and r.primarybankind = 'Y'
> and r.status = 'granted'
> and r.statusdate between '20020101' and '20050930'
> and r.businessunit in ('bond choice', 'ppl')
> order by r.sitename, r.consultantname, r.statusdate

There might be. But without knowledge of the tables, indexes and how
big they are, all you can get is guesses. If you don't need data to be
sorted, you can remove ORDER BY, but it doesn't take 29 minutes to sort
17000 rows, so the effect is moderate of that operation.

A clustered index on rptdetail(rundste, statusdate) or only (rundate)
should be a good start. An index on bankreponse(applicationid) is also
necessary.

You may also have problems with statistics that are out of date.
UPDATE STATISTICS WITH FULLSCAN on both table can address this.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

I dropped the ORDER BY and copied it Excel. This is only a once off query for
the big wigs.

Thanks for the help
Driesen

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200510/1|||Hi Erland

Thanks for the help. I did drop the ORDER BY. The only reason this takes so
long to run is becasue our DataBase sits over 500 miles away. We to get
through a whole lot of banking environment security to access our DB.

Driesen

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200510/1|||> We to get
> through a whole lot of banking environment security to access our DB.

So you thought you'd undermine all that security by copying the data to
an insecure Excel spreadsheet... :-)

Maybe you can use DTS to export the data to Excel. Or use BCP to create
a delimited file that can be opened in Excel.

--
David Portas
SQL Server MVP
--

Any way to determine last optimization Run..

Is there any way on a SQL Server 2000 db to determine when it had the
last optimization run on it?
Thanks.
Check the Agent history?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"newsqlman" <ndittoo@.yahoo.com> wrote in message
news:1182280108.117724.142340@.o11g2000prd.googlegr oups.com...
> Is there any way on a SQL Server 2000 db to determine when it had the
> last optimization run on it?
>
> Thanks.
>

Any way to determine last optimization Run..

Is there any way on a SQL Server 2000 db to determine when it had the
last optimization run on it?
Thanks.Check the Agent history?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"newsqlman" <ndittoo@.yahoo.com> wrote in message
news:1182280108.117724.142340@.o11g2000prd.googlegroups.com...
> Is there any way on a SQL Server 2000 db to determine when it had the
> last optimization run on it?
>
> Thanks.
>

Any way to determine last optimization Run..

Is there any way on a SQL Server 2000 db to determine when it had the
last optimization run on it?
Thanks.Check the Agent history?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"newsqlman" <ndittoo@.yahoo.com> wrote in message
news:1182280108.117724.142340@.o11g2000prd.googlegroups.com...
> Is there any way on a SQL Server 2000 db to determine when it had the
> last optimization run on it?
>
> Thanks.
>

Any way around this error

I get the error (in red) below when I run this stored procedure (I'm not running in my app but in Query Analyzer) -- Please help me fix this

CREATE PROCEDURE dbo.sp_Employee_GetEmployeeLNameFNameEmpID
(
@.facilityID nvarchar(2),
@.companyID nvarchar(2),
@.deptID nvarchar(20),
@.Period int
)
AS
SELECT e.LastName + ',' + e.FirstName + ' - ' + e.EmployeeID AS ListBoxText, e.EmployeeID, e.LastName + ',' + e.FirstName AS FullName FROM Employee e
WHERE e.DeptID = @.deptID AND e.FacilityID = @.facilityID AND e.CompanyID = @.companyID AND e.EmployeeID <> (SELECT ev.EmployeeID FROM EmployeeEval ev
WHERE PeriodID= @.Period)
GO

Server: Msg 512, Level 16, State 1, Procedure sp_Employee_GetEmployeeLNameFNameEmpID, Line 9
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

WHICH employee do you want from the ev table?
It looks to me like you want a NOT IN instead:
SELECT
e.LastName + ','+ e.FirstName + ' - ' + e.EmployeeID AS ListBoxText,
e.EmployeeID,
e.LastName + ',' + e.FirstName AS FullName
FROM Employee e
WHEREe.DeptID = @.deptID
AND e.FacilityID = @.facilityID
AND e.CompanyID =@.companyID
AND e.EmployeeID NOT IN
(SELECT ev.EmployeeID
FROMEmployeeEval ev
WHERE PeriodID= @.Period)
|||

Sweet worked perfectly -- my book showed the IN part but I didn't know about the NOT IN.
Thanks a million.

any user access audit programs available?

Hello all, does anyone know of a SS2005RS user audit program that an administrator can run on a RS server to show which userids have access to folders? I have in mind a pgm that would show:

folder users

Home user01, user02, user03

folderA user01,user02, user05

folderB user02, user06

Is there a pgm available as a download, or does someone have a home-grown pgm whose source they would let out?

Has anyone else faced this need?

Thanks in advance

I don't know of such a program but writing your own shouldn't be that difficult. You can call ReportingService2005.GetPolicies method to obtain the secuity polices per folder.|||Mr. Lachev, thanks so much, I'll do that. P.S. I like your website.

Any standalone version of SQL2005 Express?

Hello All,
Just want to know is there any stand alone version of the SQL 2005 Express?
As I would need to run a database on a laptop, but the policy is set to not
allowing any installations... Anyway I can unzip/unpack it and run it off
just like that? Thank you!
THanks & Best Regards,
aNewbie
Many Microsoft applications use a local installation of the Native Client
which you'll find is similar to SQL Express. For example, the Small
Business Contact Manager, the ISA firewall on Small Business Server,
Sharepoint. As far as free goes, it is free to use the SQL Express on your
own machine, but there are limits on connections to the database, limits on
the amount of RAM and number of processors accessible so it is not a fully
functions SERVER but as a server, it runs on your local machine in a way
similar to a server. You can create databases, attach databases, detach
databases, query databases and use much of the functionality that is
available on a full version (Standard or Enterprise) but there will be
limitaions. Don't quote me on this but I believe that the free version of
SQL Express comes with a GUI as well so you won't need to run queries through
DOS. As to why it won't install on your system, there may be other software
there that needs to be uninstalled first, or it is also possible that the
setup of your server is such that it is being denied permission to system
resources.
My suggestion would be to uninstall what you have (Add Remove programs),
redownload (http://msdn2.microsoft.com/en-us/express/aa718378.aspx), and then
reinstall. When you install, accept the defaults on the installation - it
would help if you read the documentation from the link as well.
Regards,
Jamie
"aNewbie" wrote:

> Hello All,
> Just want to know is there any stand alone version of the SQL 2005 Express?
> As I would need to run a database on a laptop, but the policy is set to not
> allowing any installations... Anyway I can unzip/unpack it and run it off
> just like that? Thank you!
>
> THanks & Best Regards,
> aNewbie
|||I guess your laptop belogs to your company or something similar so it's not
allowed installing additional stuff on it?
If so, you can not install any version of SQL Server 2005 on your laptop
because there is no such a version to unzip or something as far as I know.
The installer of SQL Server has to run and install the necessary apps in its
packs included .Net Framework if it's not installed yet.
Ekrem ?nsoy
"aNewbie" <aNewbie@.discussions.microsoft.com> wrote in message
news:B58013A7-E3FA-49CE-BC8F-86010355EED4@.microsoft.com...
> Hello All,
> Just want to know is there any stand alone version of the SQL 2005
> Express?
> As I would need to run a database on a laptop, but the policy is set to
> not
> allowing any installations... Anyway I can unzip/unpack it and run it off
> just like that? Thank you!
>
> THanks & Best Regards,
> aNewbie
|||Thanks thejamie and Ekrem ?nsoy,
And yes, is the company policy that got into way, sorry I didn't state that
clearly earlier...
I know the Setup Wizard will help me to config the nesseary settings and
..Net framework. So that I will not need to do it manual, and I can benefit
the nice GUI too. But sometime when just want to do things quickly on a
laptop, and don't want to go through all the paper work and requests to the
IT dep, a "stand alone" version would be very welcome (and of course, with
the GUI too).
It would be very nice if there is a "stand alone" version... especially when
people just want to do some quick work without installing it. Anyway,...
Thanks again from both of you. Atleast I know there is no standalone SQL
Express... And I would need to move on and find another brand that it has
one...
Thanks & Best Regards,
aNewbie
"Ekrem ?nsoy" wrote:

> I guess your laptop belogs to your company or something similar so it's not
> allowed installing additional stuff on it?
> If so, you can not install any version of SQL Server 2005 on your laptop
> because there is no such a version to unzip or something as far as I know.
> The installer of SQL Server has to run and install the necessary apps in its
> packs included .Net Framework if it's not installed yet.
> --
> Ekrem ?nsoy
>
> "aNewbie" <aNewbie@.discussions.microsoft.com> wrote in message
> news:B58013A7-E3FA-49CE-BC8F-86010355EED4@.microsoft.com...
>

Any standalone version of SQL2005 Express?

Hello All,
Just want to know is there any stand alone version of the SQL 2005 Express?
As I would need to run a database on a laptop, but the policy is set to not
allowing any installations... Anyway I can unzip/unpack it and run it off
just like that? Thank you!
THanks & Best Regards,
aNewbie Many Microsoft applications use a local installation of the Native Client
which you'll find is similar to SQL Express. For example, the Small
Business Contact Manager, the ISA firewall on Small Business Server,
Sharepoint. As far as free goes, it is free to use the SQL Express on your
own machine, but there are limits on connections to the database, limits on
the amount of RAM and number of processors accessible so it is not a fully
functions SERVER but as a server, it runs on your local machine in a way
similar to a server. You can create databases, attach databases, detach
databases, query databases and use much of the functionality that is
available on a full version (Standard or Enterprise) but there will be
limitaions. Don't quote me on this but I believe that the free version of
SQL Express comes with a GUI as well so you won't need to run queries throug
h
DOS. As to why it won't install on your system, there may be other softwar
e
there that needs to be uninstalled first, or it is also possible that the
setup of your server is such that it is being denied permission to system
resources.
My suggestion would be to uninstall what you have (Add Remove programs),
redownload (http://msdn2.microsoft.com/en-us/express/aa718378.aspx), and the
n
reinstall. When you install, accept the defaults on the installation - it
would help if you read the documentation from the link as well.
--
Regards,
Jamie
"aNewbie" wrote:

> Hello All,
> Just want to know is there any stand alone version of the SQL 2005 Express
?
> As I would need to run a database on a laptop, but the policy is set to no
t
> allowing any installations... Anyway I can unzip/unpack it and run it off
> just like that? Thank you!
>
> THanks & Best Regards,
> aNewbie |||I guess your laptop belogs to your company or something similar so it's not
allowed installing additional stuff on it?
If so, you can not install any version of SQL Server 2005 on your laptop
because there is no such a version to unzip or something as far as I know.
The installer of SQL Server has to run and install the necessary apps in its
packs included .Net Framework if it's not installed yet.
Ekrem ?nsoy
"aNewbie" <aNewbie@.discussions.microsoft.com> wrote in message
news:B58013A7-E3FA-49CE-BC8F-86010355EED4@.microsoft.com...
> Hello All,
> Just want to know is there any stand alone version of the SQL 2005
> Express?
> As I would need to run a database on a laptop, but the policy is set to
> not
> allowing any installations... Anyway I can unzip/unpack it and run it off
> just like that? Thank you!
>
> THanks & Best Regards,
> aNewbie |||Thanks thejamie and Ekrem ?nsoy,
And yes, is the company policy that got into way, sorry I didn't state that
clearly earlier...
I know the Setup Wizard will help me to config the nesseary settings and
.Net framework. So that I will not need to do it manual, and I can benefit
the nice GUI too. But sometime when just want to do things quickly on a
laptop, and don't want to go through all the paper work and requests to the
IT dep, a "stand alone" version would be very welcome (and of course, with
the GUI too).
It would be very nice if there is a "stand alone" version... especially when
people just want to do some quick work without installing it. Anyway,...
Thanks again from both of you. Atleast I know there is no standalone SQL
Express... And I would need to move on and find another brand that it has
one...
Thanks & Best Regards,
aNewbie
"Ekrem ?nsoy" wrote:

> I guess your laptop belogs to your company or something similar so it's no
t
> allowed installing additional stuff on it?
> If so, you can not install any version of SQL Server 2005 on your laptop
> because there is no such a version to unzip or something as far as I know.
> The installer of SQL Server has to run and install the necessary apps in i
ts
> packs included .Net Framework if it's not installed yet.
> --
> Ekrem ?nsoy
>
> "aNewbie" <aNewbie@.discussions.microsoft.com> wrote in message
> news:B58013A7-E3FA-49CE-BC8F-86010355EED4@.microsoft.com...
>

Any standalone version of SQL2005 Express?

Hello All,
Just want to know is there any stand alone version of the SQL 2005 Express?
As I would need to run a database on a laptop, but the policy is set to not
allowing any installations... Anyway I can unzip/unpack it and run it off
just like that? Thank you!
THanks & Best Regards,
aNewbie :)Many Microsoft applications use a local installation of the Native Client
which you'll find is similar to SQL Express. For example, the Small
Business Contact Manager, the ISA firewall on Small Business Server,
Sharepoint. As far as free goes, it is free to use the SQL Express on your
own machine, but there are limits on connections to the database, limits on
the amount of RAM and number of processors accessible so it is not a fully
functions SERVER but as a server, it runs on your local machine in a way
similar to a server. You can create databases, attach databases, detach
databases, query databases and use much of the functionality that is
available on a full version (Standard or Enterprise) but there will be
limitaions. Don't quote me on this but I believe that the free version of
SQL Express comes with a GUI as well so you won't need to run queries through
DOS. As to why it won't install on your system, there may be other software
there that needs to be uninstalled first, or it is also possible that the
setup of your server is such that it is being denied permission to system
resources.
My suggestion would be to uninstall what you have (Add Remove programs),
redownload (http://msdn2.microsoft.com/en-us/express/aa718378.aspx), and then
reinstall. When you install, accept the defaults on the installation - it
would help if you read the documentation from the link as well.
--
Regards,
Jamie
"aNewbie" wrote:
> Hello All,
> Just want to know is there any stand alone version of the SQL 2005 Express?
> As I would need to run a database on a laptop, but the policy is set to not
> allowing any installations... Anyway I can unzip/unpack it and run it off
> just like that? Thank you!
>
> THanks & Best Regards,
> aNewbie :)|||I guess your laptop belogs to your company or something similar so it's not
allowed installing additional stuff on it?
If so, you can not install any version of SQL Server 2005 on your laptop
because there is no such a version to unzip or something as far as I know.
The installer of SQL Server has to run and install the necessary apps in its
packs included .Net Framework if it's not installed yet.
--
Ekrem Ã?nsoy
"aNewbie" <aNewbie@.discussions.microsoft.com> wrote in message
news:B58013A7-E3FA-49CE-BC8F-86010355EED4@.microsoft.com...
> Hello All,
> Just want to know is there any stand alone version of the SQL 2005
> Express?
> As I would need to run a database on a laptop, but the policy is set to
> not
> allowing any installations... Anyway I can unzip/unpack it and run it off
> just like that? Thank you!
>
> THanks & Best Regards,
> aNewbie :)|||Thanks thejamie and Ekrem Ã?nsoy,
And yes, is the company policy that got into way, sorry I didn't state that
clearly earlier...
I know the Setup Wizard will help me to config the nesseary settings and
.Net framework. So that I will not need to do it manual, and I can benefit
the nice GUI too. But sometime when just want to do things quickly on a
laptop, and don't want to go through all the paper work and requests to the
IT dep, a "stand alone" version would be very welcome (and of course, with
the GUI too).
It would be very nice if there is a "stand alone" version... especially when
people just want to do some quick work without installing it. Anyway,...
Thanks again from both of you. Atleast I know there is no standalone SQL
Express... And I would need to move on and find another brand that it has
one...
Thanks & Best Regards,
aNewbie
"Ekrem Ã?nsoy" wrote:
> I guess your laptop belogs to your company or something similar so it's not
> allowed installing additional stuff on it?
> If so, you can not install any version of SQL Server 2005 on your laptop
> because there is no such a version to unzip or something as far as I know.
> The installer of SQL Server has to run and install the necessary apps in its
> packs included .Net Framework if it's not installed yet.
> --
> Ekrem Ã?nsoy
>
> "aNewbie" <aNewbie@.discussions.microsoft.com> wrote in message
> news:B58013A7-E3FA-49CE-BC8F-86010355EED4@.microsoft.com...
> > Hello All,
> >
> > Just want to know is there any stand alone version of the SQL 2005
> > Express?
> > As I would need to run a database on a laptop, but the policy is set to
> > not
> > allowing any installations... Anyway I can unzip/unpack it and run it off
> > just like that? Thank you!
> >
> >
> > THanks & Best Regards,
> > aNewbie :)
>

Any setting that would reduce locking during a SQL script?

I am running an upgrade script to update a database from one release to
another. There are a lot of SQL scripts that run during this process.
While this process is running - there should be no other users accessing the
database.
With this in mind - I am hoping to reduce the amount of locking that occurs.
Since no one else will be accessing the system - I don't need all of the
locks at the granular level (I am fine with table locks). Is there some way
that I could make it so that I get table locks instead of row-level locking?
Thanks in advance.You can specify the TABLOCKX hint to acquire an exclusive table lock. For
example:
INSERT INTO MyTable WITH (TABLOCKX)
SELECT * FROM MyOtherTable WITH (TABLOCKX)
Hope this helps.
Dan Guzman
SQL Server MVP
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:%23yBrPIH4DHA.2380@.TK2MSFTNGP10.phx.gbl...

quote:

---

> I am running an upgrade script to update a database from one release to
> another. There are a lot of SQL scripts that run during this process.
> While this process is running - there should be no other users accessing

---

the

quote:

---

> database.
> With this in mind - I am hoping to reduce the amount of locking that

---

occurs.

quote:

---

> Since no one else will be accessing the system - I don't need all of the
> locks at the granular level (I am fine with table locks). Is there some

---

way

quote:

---

> that I could make it so that I get table locks instead of row-level

---

locking?

quote:

---

> Thanks in advance.
>

---

|||One to look at will be setting the database into single user mode.
I'm not sure whether it causes locks to escalate, but admin work such as
you're performing is one of it's indended usage scenarios.
You can set a db into single_user by using the alter database command, eg:
alter database [dbname] set single_user
Regards,
Greg Linwood
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:%23yBrPIH4DHA.2380@.TK2MSFTNGP10.phx.gbl...

quote:

---

> I am running an upgrade script to update a database from one release to
> another. There are a lot of SQL scripts that run during this process.
> While this process is running - there should be no other users accessing

---

the

quote:

---

> database.
> With this in mind - I am hoping to reduce the amount of locking that

---

occurs.

quote:

---

> Since no one else will be accessing the system - I don't need all of the
> locks at the granular level (I am fine with table locks). Is there some

---

way

quote:

---

> that I could make it so that I get table locks instead of row-level

---

locking?

quote:

---

> Thanks in advance.
>

---

Any setting that would reduce locking during a SQL script?

I am running an upgrade script to update a database from one release to
another. There are a lot of SQL scripts that run during this process.
While this process is running - there should be no other users accessing the
database.
With this in mind - I am hoping to reduce the amount of locking that occurs.
Since no one else will be accessing the system - I don't need all of the
locks at the granular level (I am fine with table locks). Is there some way
that I could make it so that I get table locks instead of row-level locking?
Thanks in advance.You can specify the TABLOCKX hint to acquire an exclusive table lock. For
example:
INSERT INTO MyTable WITH (TABLOCKX)
SELECT * FROM MyOtherTable WITH (TABLOCKX)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:%23yBrPIH4DHA.2380@.TK2MSFTNGP10.phx.gbl...
> I am running an upgrade script to update a database from one release to
> another. There are a lot of SQL scripts that run during this process.
> While this process is running - there should be no other users accessing
the
> database.
> With this in mind - I am hoping to reduce the amount of locking that
occurs.
> Since no one else will be accessing the system - I don't need all of the
> locks at the granular level (I am fine with table locks). Is there some
way
> that I could make it so that I get table locks instead of row-level
locking?
> Thanks in advance.
>|||One to look at will be setting the database into single user mode.
I'm not sure whether it causes locks to escalate, but admin work such as
you're performing is one of it's indended usage scenarios.
You can set a db into single_user by using the alter database command, eg:
alter database [dbname] set single_user
Regards,
Greg Linwood
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:%23yBrPIH4DHA.2380@.TK2MSFTNGP10.phx.gbl...
> I am running an upgrade script to update a database from one release to
> another. There are a lot of SQL scripts that run during this process.
> While this process is running - there should be no other users accessing
the
> database.
> With this in mind - I am hoping to reduce the amount of locking that
occurs.
> Since no one else will be accessing the system - I don't need all of the
> locks at the granular level (I am fine with table locks). Is there some
way
> that I could make it so that I get table locks instead of row-level
locking?
> Thanks in advance.
>

any reason why a query takes longer to run on 2005 ?

any reason why a query takes longer to run on 2005 ?
sql 2000 query takes 7 seconds
same query pasted into sql 2005 takes 56 seconds
sql 2005 server is pretty much 10x higher specification
query :-
SELECT Personal.ID, Personal.Surname1, Lead.DateLeadReceived,
Personal.Postcode, Lead.Reference, Lead.LeadLoggedBy, RTB.RTBToClient,
RTB.RTBReturnedFromClient, Live.RTBToCouncil,
ToLive.TransferToLive, Closed.DateClosed, NoValidTel.ID
FROM Personal LEFT OUTER JOIN
Diary ON Personal.ID = Diary.MainID LEFT OUTER JOIN
Lead ON Personal.ID = Lead.ID LEFT OUTER JOIN
Closed ON Personal.ID = Closed.ID LEFT OUTER JOIN
ToLive ON Personal.ID = ToLive.ID LEFT OUTER JOIN
NoValidTel ON Personal.ID = NoValidTel.ID LEFT OUTER
JOIN
RTB ON Personal.ID = RTB.ID LEFT OUTER JOIN
Live ON Personal.ID = Live.ID
GROUP BY Personal.ID, Personal.Surname1, Lead.DateLeadReceived,
Personal.Postcode, Lead.Reference, Lead.LeadLoggedBy, RTB.RTBToClient,
RTB.RTBReturnedFromClient, Live.RTBToCouncil,
ToLive.TransferToLive, Closed.DateClosed, NoValidTel.ID
HAVING (NOT (Personal.ID IN
(SELECT mainid
FROM diary
WHERE valid = 'true'))) AND
(RTB.RTBToClient IS NULL OR
RTB.RTBToClient = CONVERT(DATETIME, '1900-01-01
00:00:00', 102)) AND (RTB.RTBReturnedFromClient IS NULL OR
RTB.RTBReturnedFromClient = CONVERT(DATETIME,
'1900-01-01 00:00:00', 102)) AND (Live.RTBToCouncil IS NULL OR
Live.RTBToCouncil = CONVERT(DATETIME, '1900-01-01
00:00:00', 102)) AND (ToLive.TransferToLive IS NULL OR
ToLive.TransferToLive = CONVERT(DATETIME, '1900-01-01
00:00:00', 102)) AND (Closed.DateClosed IS NULL OR
Closed.DateClosed = CONVERT(DATETIME, '1900-01-01
00:00:00', 102)) AND (Lead.DateLeadReceived > CONVERT(DATETIME, '2005-01-01
00:00:00',
102)) AND (NoValidTel.ID IS NULL)
ORDER BY Lead.DateLeadReceivednm, fixed it, had to tune the databases, it was from a fresh backup restore
"luna" <luna@.themoon.com> wrote in message
news:DQZGf.35862$494.13699@.newsfe2-gui.ntli.net...
> any reason why a query takes longer to run on 2005 ?
> sql 2000 query takes 7 seconds
> same query pasted into sql 2005 takes 56 seconds
> sql 2005 server is pretty much 10x higher specification
> query :-
>
> SELECT Personal.ID, Personal.Surname1, Lead.DateLeadReceived,
> Personal.Postcode, Lead.Reference, Lead.LeadLoggedBy, RTB.RTBToClient,
> RTB.RTBReturnedFromClient, Live.RTBToCouncil,
> ToLive.TransferToLive, Closed.DateClosed, NoValidTel.ID
> FROM Personal LEFT OUTER JOIN
> Diary ON Personal.ID = Diary.MainID LEFT OUTER JOIN
> Lead ON Personal.ID = Lead.ID LEFT OUTER JOIN
> Closed ON Personal.ID = Closed.ID LEFT OUTER JOIN
> ToLive ON Personal.ID = ToLive.ID LEFT OUTER JOIN
> NoValidTel ON Personal.ID = NoValidTel.ID LEFT OUTER
> JOIN
> RTB ON Personal.ID = RTB.ID LEFT OUTER JOIN
> Live ON Personal.ID = Live.ID
> GROUP BY Personal.ID, Personal.Surname1, Lead.DateLeadReceived,
> Personal.Postcode, Lead.Reference, Lead.LeadLoggedBy, RTB.RTBToClient,
> RTB.RTBReturnedFromClient, Live.RTBToCouncil,
> ToLive.TransferToLive, Closed.DateClosed, NoValidTel.ID
> HAVING (NOT (Personal.ID IN
> (SELECT mainid
> FROM diary
> WHERE valid = 'true'))) AND
> (RTB.RTBToClient IS NULL OR
> RTB.RTBToClient = CONVERT(DATETIME, '1900-01-01
> 00:00:00', 102)) AND (RTB.RTBReturnedFromClient IS NULL OR
> RTB.RTBReturnedFromClient = CONVERT(DATETIME,
> '1900-01-01 00:00:00', 102)) AND (Live.RTBToCouncil IS NULL OR
> Live.RTBToCouncil = CONVERT(DATETIME, '1900-01-01
> 00:00:00', 102)) AND (ToLive.TransferToLive IS NULL OR
> ToLive.TransferToLive = CONVERT(DATETIME, '1900-01-01
> 00:00:00', 102)) AND (Closed.DateClosed IS NULL OR
> Closed.DateClosed = CONVERT(DATETIME, '1900-01-01
> 00:00:00', 102)) AND (Lead.DateLeadReceived > CONVERT(DATETIME,
> '2005-01-01 00:00:00',
> 102)) AND (NoValidTel.ID IS NULL)
> ORDER BY Lead.DateLeadReceived
>|||How did you 'tune' the databases?
"luna" <luna@.themoon.com> wrote in message
news:N6_Gf.86038$zt1.2377@.newsfe5-gui.ntli.net...
> nm, fixed it, had to tune the databases, it was from a fresh backup
> restore
>
> "luna" <luna@.themoon.com> wrote in message
> news:DQZGf.35862$494.13699@.newsfe2-gui.ntli.net...
>> any reason why a query takes longer to run on 2005 ?
>> sql 2000 query takes 7 seconds
>> same query pasted into sql 2005 takes 56 seconds
>> sql 2005 server is pretty much 10x higher specification
>> query :-
>>
>> SELECT Personal.ID, Personal.Surname1, Lead.DateLeadReceived,
>> Personal.Postcode, Lead.Reference, Lead.LeadLoggedBy, RTB.RTBToClient,
>> RTB.RTBReturnedFromClient, Live.RTBToCouncil,
>> ToLive.TransferToLive, Closed.DateClosed, NoValidTel.ID
>> FROM Personal LEFT OUTER JOIN
>> Diary ON Personal.ID = Diary.MainID LEFT OUTER JOIN
>> Lead ON Personal.ID = Lead.ID LEFT OUTER JOIN
>> Closed ON Personal.ID = Closed.ID LEFT OUTER JOIN
>> ToLive ON Personal.ID = ToLive.ID LEFT OUTER JOIN
>> NoValidTel ON Personal.ID = NoValidTel.ID LEFT OUTER
>> JOIN
>> RTB ON Personal.ID = RTB.ID LEFT OUTER JOIN
>> Live ON Personal.ID = Live.ID
>> GROUP BY Personal.ID, Personal.Surname1, Lead.DateLeadReceived,
>> Personal.Postcode, Lead.Reference, Lead.LeadLoggedBy, RTB.RTBToClient,
>> RTB.RTBReturnedFromClient, Live.RTBToCouncil,
>> ToLive.TransferToLive, Closed.DateClosed, NoValidTel.ID
>> HAVING (NOT (Personal.ID IN
>> (SELECT mainid
>> FROM diary
>> WHERE valid = 'true'))) AND
>> (RTB.RTBToClient IS NULL OR
>> RTB.RTBToClient = CONVERT(DATETIME, '1900-01-01
>> 00:00:00', 102)) AND (RTB.RTBReturnedFromClient IS NULL OR
>> RTB.RTBReturnedFromClient = CONVERT(DATETIME,
>> '1900-01-01 00:00:00', 102)) AND (Live.RTBToCouncil IS NULL OR
>> Live.RTBToCouncil = CONVERT(DATETIME, '1900-01-01
>> 00:00:00', 102)) AND (ToLive.TransferToLive IS NULL OR
>> ToLive.TransferToLive = CONVERT(DATETIME,
>> '1900-01-01 00:00:00', 102)) AND (Closed.DateClosed IS NULL OR
>> Closed.DateClosed = CONVERT(DATETIME, '1900-01-01
>> 00:00:00', 102)) AND (Lead.DateLeadReceived > CONVERT(DATETIME,
>> '2005-01-01 00:00:00',
>> 102)) AND (NoValidTel.ID IS NULL)
>> ORDER BY Lead.DateLeadReceived
>|||"Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:uC7ektlLGHA.2036@.TK2MSFTNGP14.phx.gbl...
> How did you 'tune' the databases?
>
theres some tuning wizard as part of the install, was 65% improved running
it

any reason why a query takes longer to run on 2005 ?

any reason why a query takes longer to run on 2005 ?
sql 2000 query takes 7 seconds
same query pasted into sql 2005 takes 56 seconds
sql 2005 server is pretty much 10x higher specification
query :-
SELECT Personal.ID, Personal.Surname1, Lead.DateLeadReceived,
Personal.Postcode, Lead.Reference, Lead.LeadLoggedBy, RTB.RTBToClient,
RTB.RTBReturnedFromClient, Live.RTBToCouncil,
ToLive.TransferToLive, Closed.DateClosed, NoValidTel.ID
FROM Personal LEFT OUTER JOIN
Diary ON Personal.ID = Diary.MainID LEFT OUTER JOIN
Lead ON Personal.ID = Lead.ID LEFT OUTER JOIN
Closed ON Personal.ID = Closed.ID LEFT OUTER JOIN
ToLive ON Personal.ID = ToLive.ID LEFT OUTER JOIN
NoValidTel ON Personal.ID = NoValidTel.ID LEFT OUTER
JOIN
RTB ON Personal.ID = RTB.ID LEFT OUTER JOIN
Live ON Personal.ID = Live.ID
GROUP BY Personal.ID, Personal.Surname1, Lead.DateLeadReceived,
Personal.Postcode, Lead.Reference, Lead.LeadLoggedBy, RTB.RTBToClient,
RTB.RTBReturnedFromClient, Live.RTBToCouncil,
ToLive.TransferToLive, Closed.DateClosed, NoValidTel.ID
HAVING (NOT (Personal.ID IN
(SELECT mainid
FROM diary
WHERE valid = 'true'))) AND
(RTB.RTBToClient IS NULL OR
RTB.RTBToClient = CONVERT(DATETIME, '1900-01-01
00:00:00', 102)) AND (RTB.RTBReturnedFromClient IS NULL OR
RTB.RTBReturnedFromClient = CONVERT(DATETIME,
'1900-01-01 00:00:00', 102)) AND (Live.RTBToCouncil IS NULL OR
Live.RTBToCouncil = CONVERT(DATETIME, '1900-01-01
00:00:00', 102)) AND (ToLive.TransferToLive IS NULL OR
ToLive.TransferToLive = CONVERT(DATETIME, '1900-01-01
00:00:00', 102)) AND (Closed.DateClosed IS NULL OR
Closed.DateClosed = CONVERT(DATETIME, '1900-01-01
00:00:00', 102)) AND (Lead.DateLeadReceived > CONVERT(DATETIME, '2005-01-01
00:00:00',
102)) AND (NoValidTel.ID IS NULL)
ORDER BY Lead.DateLeadReceivednm, fixed it, had to tune the databases, it was from a fresh backup restore
"luna" <luna@.themoon.com> wrote in message
news:DQZGf.35862$494.13699@.newsfe2-gui.ntli.net...
> any reason why a query takes longer to run on 2005 ?
> sql 2000 query takes 7 seconds
> same query pasted into sql 2005 takes 56 seconds
> sql 2005 server is pretty much 10x higher specification
> query :-
>
> SELECT Personal.ID, Personal.Surname1, Lead.DateLeadReceived,
> Personal.Postcode, Lead.Reference, Lead.LeadLoggedBy, RTB.RTBToClient,
> RTB.RTBReturnedFromClient, Live.RTBToCouncil,
> ToLive.TransferToLive, Closed.DateClosed, NoValidTel.ID
> FROM Personal LEFT OUTER JOIN
> Diary ON Personal.ID = Diary.MainID LEFT OUTER JOIN
> Lead ON Personal.ID = Lead.ID LEFT OUTER JOIN
> Closed ON Personal.ID = Closed.ID LEFT OUTER JOIN
> ToLive ON Personal.ID = ToLive.ID LEFT OUTER JOIN
> NoValidTel ON Personal.ID = NoValidTel.ID LEFT OUTER
> JOIN
> RTB ON Personal.ID = RTB.ID LEFT OUTER JOIN
> Live ON Personal.ID = Live.ID
> GROUP BY Personal.ID, Personal.Surname1, Lead.DateLeadReceived,
> Personal.Postcode, Lead.Reference, Lead.LeadLoggedBy, RTB.RTBToClient,
> RTB.RTBReturnedFromClient, Live.RTBToCouncil,
> ToLive.TransferToLive, Closed.DateClosed, NoValidTel.ID
> HAVING (NOT (Personal.ID IN
> (SELECT mainid
> FROM diary
> WHERE valid = 'true'))) AND
> (RTB.RTBToClient IS NULL OR
> RTB.RTBToClient = CONVERT(DATETIME, '1900-01-01
> 00:00:00', 102)) AND (RTB.RTBReturnedFromClient IS NULL OR
> RTB.RTBReturnedFromClient = CONVERT(DATETIME,
> '1900-01-01 00:00:00', 102)) AND (Live.RTBToCouncil IS NULL OR
> Live.RTBToCouncil = CONVERT(DATETIME, '1900-01-01
> 00:00:00', 102)) AND (ToLive.TransferToLive IS NULL OR
> ToLive.TransferToLive = CONVERT(DATETIME, '1900-01-01
> 00:00:00', 102)) AND (Closed.DateClosed IS NULL OR
> Closed.DateClosed = CONVERT(DATETIME, '1900-01-01
> 00:00:00', 102)) AND (Lead.DateLeadReceived > CONVERT(DATETIME,
> '2005-01-01 00:00:00',
> 102)) AND (NoValidTel.ID IS NULL)
> ORDER BY Lead.DateLeadReceived
>|||How did you 'tune' the databases?
"luna" <luna@.themoon.com> wrote in message
news:N6_Gf.86038$zt1.2377@.newsfe5-gui.ntli.net...
> nm, fixed it, had to tune the databases, it was from a fresh backup
> restore
>
> "luna" <luna@.themoon.com> wrote in message
> news:DQZGf.35862$494.13699@.newsfe2-gui.ntli.net...
>|||"Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:uC7ektlLGHA.2036@.TK2MSFTNGP14.phx.gbl...
> How did you 'tune' the databases?
>
theres some tuning wizard as part of the install, was 65% improved running
it

any reason why a query takes longer to run on 2005 ?

any reason why a query takes longer to run on 2005 ?
sql 2000 query takes 7 seconds
same query pasted into sql 2005 takes 56 seconds
sql 2005 server is pretty much 10x higher specification
query :-
SELECT Personal.ID, Personal.Surname1, Lead.DateLeadReceived,
Personal.Postcode, Lead.Reference, Lead.LeadLoggedBy, RTB.RTBToClient,
RTB.RTBReturnedFromClient, Live.RTBToCouncil,
ToLive.TransferToLive, Closed.DateClosed, NoValidTel.ID
FROM Personal LEFT OUTER JOIN
Diary ON Personal.ID = Diary.MainID LEFT OUTER JOIN
Lead ON Personal.ID = Lead.ID LEFT OUTER JOIN
Closed ON Personal.ID = Closed.ID LEFT OUTER JOIN
ToLive ON Personal.ID = ToLive.ID LEFT OUTER JOIN
NoValidTel ON Personal.ID = NoValidTel.ID LEFT OUTER
JOIN
RTB ON Personal.ID = RTB.ID LEFT OUTER JOIN
Live ON Personal.ID = Live.ID
GROUP BY Personal.ID, Personal.Surname1, Lead.DateLeadReceived,
Personal.Postcode, Lead.Reference, Lead.LeadLoggedBy, RTB.RTBToClient,
RTB.RTBReturnedFromClient, Live.RTBToCouncil,
ToLive.TransferToLive, Closed.DateClosed, NoValidTel.ID
HAVING (NOT (Personal.ID IN
(SELECT mainid
FROM diary
WHERE valid = 'true'))) AND
(RTB.RTBToClient IS NULL OR
RTB.RTBToClient = CONVERT(DATETIME, '1900-01-01
00:00:00', 102)) AND (RTB.RTBReturnedFromClient IS NULL OR
RTB.RTBReturnedFromClient = CONVERT(DATETIME,
'1900-01-01 00:00:00', 102)) AND (Live.RTBToCouncil IS NULL OR
Live.RTBToCouncil = CONVERT(DATETIME, '1900-01-01
00:00:00', 102)) AND (ToLive.TransferToLive IS NULL OR
ToLive.TransferToLive = CONVERT(DATETIME, '1900-01-01
00:00:00', 102)) AND (Closed.DateClosed IS NULL OR
Closed.DateClosed = CONVERT(DATETIME, '1900-01-01
00:00:00', 102)) AND (Lead.DateLeadReceived > CONVERT(DATETIME, '2005-01-01
00:00:00',
102)) AND (NoValidTel.ID IS NULL)
ORDER BY Lead.DateLeadReceived
nm, fixed it, had to tune the databases, it was from a fresh backup restore
"luna" <luna@.themoon.com> wrote in message
news:DQZGf.35862$494.13699@.newsfe2-gui.ntli.net...
> any reason why a query takes longer to run on 2005 ?
> sql 2000 query takes 7 seconds
> same query pasted into sql 2005 takes 56 seconds
> sql 2005 server is pretty much 10x higher specification
> query :-
>
> SELECT Personal.ID, Personal.Surname1, Lead.DateLeadReceived,
> Personal.Postcode, Lead.Reference, Lead.LeadLoggedBy, RTB.RTBToClient,
> RTB.RTBReturnedFromClient, Live.RTBToCouncil,
> ToLive.TransferToLive, Closed.DateClosed, NoValidTel.ID
> FROM Personal LEFT OUTER JOIN
> Diary ON Personal.ID = Diary.MainID LEFT OUTER JOIN
> Lead ON Personal.ID = Lead.ID LEFT OUTER JOIN
> Closed ON Personal.ID = Closed.ID LEFT OUTER JOIN
> ToLive ON Personal.ID = ToLive.ID LEFT OUTER JOIN
> NoValidTel ON Personal.ID = NoValidTel.ID LEFT OUTER
> JOIN
> RTB ON Personal.ID = RTB.ID LEFT OUTER JOIN
> Live ON Personal.ID = Live.ID
> GROUP BY Personal.ID, Personal.Surname1, Lead.DateLeadReceived,
> Personal.Postcode, Lead.Reference, Lead.LeadLoggedBy, RTB.RTBToClient,
> RTB.RTBReturnedFromClient, Live.RTBToCouncil,
> ToLive.TransferToLive, Closed.DateClosed, NoValidTel.ID
> HAVING (NOT (Personal.ID IN
> (SELECT mainid
> FROM diary
> WHERE valid = 'true'))) AND
> (RTB.RTBToClient IS NULL OR
> RTB.RTBToClient = CONVERT(DATETIME, '1900-01-01
> 00:00:00', 102)) AND (RTB.RTBReturnedFromClient IS NULL OR
> RTB.RTBReturnedFromClient = CONVERT(DATETIME,
> '1900-01-01 00:00:00', 102)) AND (Live.RTBToCouncil IS NULL OR
> Live.RTBToCouncil = CONVERT(DATETIME, '1900-01-01
> 00:00:00', 102)) AND (ToLive.TransferToLive IS NULL OR
> ToLive.TransferToLive = CONVERT(DATETIME, '1900-01-01
> 00:00:00', 102)) AND (Closed.DateClosed IS NULL OR
> Closed.DateClosed = CONVERT(DATETIME, '1900-01-01
> 00:00:00', 102)) AND (Lead.DateLeadReceived > CONVERT(DATETIME,
> '2005-01-01 00:00:00',
> 102)) AND (NoValidTel.ID IS NULL)
> ORDER BY Lead.DateLeadReceived
>
|||How did you 'tune' the databases?
"luna" <luna@.themoon.com> wrote in message
news:N6_Gf.86038$zt1.2377@.newsfe5-gui.ntli.net...
> nm, fixed it, had to tune the databases, it was from a fresh backup
> restore
>
> "luna" <luna@.themoon.com> wrote in message
> news:DQZGf.35862$494.13699@.newsfe2-gui.ntli.net...
>
|||"Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:uC7ektlLGHA.2036@.TK2MSFTNGP14.phx.gbl...
> How did you 'tune' the databases?
>
theres some tuning wizard as part of the install, was 65% improved running
it

Any idea why the update won't run?

I have this store proc that ends at the update command. No matter where I
move it to, the whole thing seems to quit there.
CREATE PROCEDURE sp_LPModUser
@.ModFName NVarChar(100),
@.ModLName NVarChar(100),
@.ModLogon NVarChar(100),
@.ModPassword NVarChar(100),
@.ModLPUserID Int,
@.ModPageAccess Int,
@.ModChangePW Int,
@.Database NVarChar(100)
AS
Declare @.OldLogon nvarchar(100)
set @.OldLogon = (Select Login from LPUsers where lpuserid = @.ModLPUserID)
Declare @.Check integer
Set @.Check = (select Count(*) from master.dbo.sysxlogins where name =
@.ModLogon)
if @.Check = 0
Begin
exec sp_addlogin @.ModLogon, @.ModPassword, @.Database
exec sp_grantdbaccess @.ModLogon
exec sp_addrolemember 'db_datareader', @.ModLogon
exec sp_addrolemember 'db_datawriter', @.ModLogon
End
If @.OldLogon != @.ModLogon
Begin
exec sp_revokedbaccess @.OldLogon
exec sp_droplogin @.OldLogon
End
Begin
exec sp_password NULL, @.ModPassword, @.ModLogon
update lpusers set fname = @.ModFName, lname = @.ModLName, login = @.Modlogon,
[password] = @.ModPassword,
PageAccess = @.ModPageAccess, ChangePW = @.ModChangePW where lpuserid =
@.ModLPUserID
print 'test'
endNevermind, I got it.
"Shawn" <shawn.camner@.ccci.org> wrote in message
news:uXUEsGg6FHA.564@.TK2MSFTNGP10.phx.gbl...
>I have this store proc that ends at the update command. No matter where I
>move it to, the whole thing seems to quit there.
> CREATE PROCEDURE sp_LPModUser
> @.ModFName NVarChar(100),
> @.ModLName NVarChar(100),
> @.ModLogon NVarChar(100),
> @.ModPassword NVarChar(100),
> @.ModLPUserID Int,
> @.ModPageAccess Int,
> @.ModChangePW Int,
> @.Database NVarChar(100)
> AS
> Declare @.OldLogon nvarchar(100)
> set @.OldLogon = (Select Login from LPUsers where lpuserid = @.ModLPUserID)
> Declare @.Check integer
> Set @.Check = (select Count(*) from master.dbo.sysxlogins where name =
> @.ModLogon)
> if @.Check = 0
> Begin
> exec sp_addlogin @.ModLogon, @.ModPassword, @.Database
> exec sp_grantdbaccess @.ModLogon
> exec sp_addrolemember 'db_datareader', @.ModLogon
> exec sp_addrolemember 'db_datawriter', @.ModLogon
> End
> If @.OldLogon != @.ModLogon
> Begin
> exec sp_revokedbaccess @.OldLogon
> exec sp_droplogin @.OldLogon
> End
> Begin
> exec sp_password NULL, @.ModPassword, @.ModLogon
> update lpusers set fname = @.ModFName, lname = @.ModLName, login =
> @.Modlogon, [password] = @.ModPassword,
> PageAccess = @.ModPageAccess, ChangePW = @.ModChangePW where lpuserid =
> @.ModLPUserID
> print 'test'
> end
>

Any idea on this!

How can execute sqlscript stored in a folder.
i.e D:\SQLSCRIPT\cs_posting_exc.PRC
What can I write in query analyzer so that I can run that procedure in that
folder is a specified server and database.Have you looked into using osql.exe with xp_cmdshell? See SQL Server Books
Online for more details & post back if you have questions.
Anith

Any Faster Way To Process This?

Hello All.

I have this script that I need to run every Saturday evening and it takes more than 5 hours to complete. Is there a better way to structure my script? Please advise. Thank you.

update Table_A set financial_yr = t2.Master_financial_year,
financial_period = t2.Master_financial_period
from Master_financial_Table t2
where frst_requested_date >= t2.Start_date and frst_requested_date <= t2.End_date
and financial_year>2000

There are a total of 4.6 million records (and growing weekly) in Table_A and 42 records in Master_financial_table (standard)

Indexes have been created for these tables.Maybe this will be faster
You don't read 4 million times the Master_financial_Table

Declare @.Master_financial_year DateTime
Declare @.Master_financial_period DateTime
Declare @.Start_date DateTime
Declare @.End_date DateTime

Select @.Master_financial_year=Master_financial_year,
@.Master_financial_period=Master_financial_period,
@.Start_date=Start_date,
@.End_date=End_date
From Master_financial_Table

Update Table_A
Set financial_yr=@.Master_financial_year,
financial_period = @.Master_financial_period
Where frst_requested_date between @.Start_date and @.End_date and
financial_year>2000|||I suspect that Table_A will have been given a non-clustered index on frst_requested_date. Given that where frst_requested_date >= t2.Start_date and frst_requested_date <= t2.End_date is going to select a very large number of records, if Table_A has clustered index, you should consider removing it.

In SQL Server 2000, if a table has a clustered index, the leaf nodes of all non-clustered indexes contain the key values of the clustered index corresponding to the index match. So, when a non-clustered index match is found it is followed by a bookmark-lookup on the tables clustered index. On *very* large tables even a highly specific index can return many thousands of records, thereby inducing thousands of clustered index seeks in order to resolve the secondary bookmark lookup. SQL Server should be smart enough to realize a table scan is going to be better, but often it doesnt, and so very large clustered tables exhibit unpleasant performance when a query is conducted through a non-clustered index.

If a table is non-clustered (or heaped), the leaf nodes of all its indexes consist of offset pointers directly into the data blob. This has a significant performance risk in that any index page splitting becomes expensive to fixup on insert or update, but all indexes recover their data by jumping directly to the data row without thousands or millions of bookmark lookups or full blown table scans.

If you can generate a query plan and discover many bookmark lookups are taking place, its worth considering unclustering Table_A.|||If a table is non-clustered (or heaped), the leaf nodes of all its indexes consist of offset pointers directly into the data blob. This has a significant performance risk in that any index page splitting becomes expensive to fixup on insert or update, but all indexes recover their data by jumping directly to the data row without thousands or millions of bookmark lookups or full blown table scans.

I disagree. If the table is a heap the pointers point to the ROWID, which takes into place dbname, table, page, etc. Not sure what you mean by data blob.

and so very large clustered tables exhibit unpleasant performance when a query is conducted through a non-clustered index.

I disagree again. SQL will choose to do a clustered index scan if a table scan is more efficient, a clustered index seek is actually using the index and SQL will usually choose to ignore the NCI and just scan the table (clustered index scan) Also, while you mention pagesplitting, a heap reclaims empty space, this is horrible as it takes time for SQL to find the empty space. Also, choosing the right clustered key will keep your inserts fast and your NCI lookups fast as your key should be small (smaller than the ROWID lookup)

I have found there are very rare occasions when a clustered index should not be on a table.

For the poster - mess with your indexes and play with the "set statistics IO on" command, this will show you how many page reads your query costs you and allow you the ability to tell if removing or adding an index will really benefit you or hurt you.

HTH|||Originally posted by rhigdon
I disagree. If the table is a heap the pointers point to the ROWID, which takes into place dbname, table, page, etc. Not sure what you mean by data blob.



I disagree again. SQL will choose to do a clustered index scan if a table scan is more efficient, a clustered index seek is actually using the index and SQL will usually choose to ignore the NCI and just scan the table (clustered index scan) Also, while you mention pagesplitting, a heap reclaims empty space, this is horrible as it takes time for SQL to find the empty space. Also, choosing the right clustered key will keep your inserts fast and your NCI lookups fast as your key should be small (smaller than the ROWID lookup)

I have found there are very rare occasions when a clustered index should not be on a table.

For the poster - mess with your indexes and play with the "set statistics IO on" command, this will show you how many page reads your query costs you and allow you the ability to tell if removing or adding an index will really benefit you or hurt you.

HTH

You are quite welcome to disagree. However very large data tables behave differently from more modest deployments.

I quote from http://www.sql-server-performance.com/jc_sql_server_quantative_analysis5d.asp

"The key observation for multi-row select queries is that there can be a very wide discrepancy between the point where query optimizer switches the execution plan to a Table Scan and the actually observed cross-over point."

"Other important points include the following. Bookmark Lookups are less expensive for heap organized tables than tables with a clustered index. It is frequently recommended that tables have a clustered index. If clustering only benefits a small fraction of the queries (weighted by the number of rows involved), then it may be better to leave the table a heap."|||"Other important points include the following. Bookmark Lookups are less expensive for heap organized tables than tables with a clustered index. It is frequently recommended that tables have a clustered index. If clustering only benefits a small fraction of the queries (weighted by the number of rows involved), then it may be better to leave the table a heap."

I agree if you will not be having a lot of inserts or deletes. The reclaiming of empty space is non-optimal if either are occuring. The other benefit of more efficient key locks with a clustered index than row locks should be taken into consideration if you will be joining to the table. I would be real interested to hear the posters logical IO when using or not using a clustered index. There are very few tables I work with that are read-only or used solely for querying, the only place I really have that is in a warehouse that I use cubes for anyway rather than SQL to query.

Looks like an interesting article, will have a read.|||Karolyn, limteckboon's query is not going to make four million passes through the table. That's ridiculous. Your solution is functionally equivalent to his, but with extra coding.

limteckboon,
It's unclear whether fields frst_requested_date, and financial_year are in Table_A or Master_financial_Table. It makes a difference in how the query should be written. Please clarify. If they are in Table_A then possibly a JOIN instead of a WHERE clause would improve efficiency:

update Table_A
set financial_yr = t2.Master_financial_year,
financial_period = t2.Master_financial_period
from Table_A
inner join Master_financial_Table t2
on Table_A frst_requested_date between t2.Start_date and t2.End_date
and Table_A.financial_year>2000

In the meantime, you may get better performance if you DROP the indexes on Table_A prior to your update, especially any indexes involving columns financial_yr and financial_period. Add the indexes back in at the end of the process.

blindman|||my query is not equivalent...

there's no JOINs on my query
so the UPDATE query will be excuted faster|||reindexing a 4-million-table must cost a lot...|||It costs less than continuously reshuffling the index pages.|||Hello All.

Thank you very much for all your most valuable advises. Greatly appreciated. Now, I need time to digest them. Will give a try on the 2 suggested codes to see which is more suitable for me but I am very sure both set of codes will definitely give better performance than mine.

Once again Thanks a million to all.

Best regards|||i don't see a difference between Karolyn and blindy's code, except for "extra coding" which actually eliminates a whole table out of the update (that's actually a plus, blindy)

limteckboon:
consider non-conventional data modifications. i guarantee that we can shrink the execution time down to under 60 minutes, maybe even less than 30 if you stop listening to blabbering about indexing and inner joins.

did i get your attention?|||ms_sql_dba posted in another thread

"i've done this type of update on larger number of rows without using update. the trick? use queryout on bcp with values that you want to have, then truncate and bcp data back in."

to his idea
--> use Bulk Insert to put back in the data is faster than Bcp
--> and set the option 'select into/bulkcopy' to True with this command

exec sp_option 'DataBaseName', 'select into/bulkcopy','True'|||simple... *** admiration ***|||If you're going to examine 4 million rows, so a table scan is going to be done (almost certainly), but I doubt that the scanning time is going to be an issue. What will certainly be an issue is the fact that you are updating every single one of them, every single time!

Once you've processed 4 million records this week, how many of them are really going to change by next week? Your query should select, and only update the status of, those records which have changed (or that do not have a known status yet). It seems to me that if you are examining the "first-requested" date, not too many records will ever change status a second time! Take full advantage of that!
If you must make the change to all the records, write a script that processes 1,000 records at a time, then commits. Otherwise, the server is prepared to roll back every one of those changes!
Any change to an indexed column will cause the index to be updated every time. This should be avoided.
Just as an afterthought: can these be calculated fields? I mean, with only twenty-something date ranges, total ...

No matter how efficient a computer or a piece of software may be, the best way to get good performance out of it is to ask it to do absolutely no more than it has to. I think that the root cause of the problem is that you are making the server do far too much work.

Any DOS utility for MDX?

Hi,

Is there any DOS utility can run MDX query ( like sqlcmd and osql for TSQL.)?

We need schedule a MDX query for creating a local cube file.

Appreciate any help on this.

The ASCMD utility, provided as one of the SSAS samples, can be used for this kind of thing. Here's a link on how to use it:

http://msdn2.microsoft.com/en-us/library/ms365187.aspx

Samples can be downloaded at:

http://msdn2.microsoft.com/en-us/library/ms365187.aspx

Please note, you will need to compile the utility per the instructions provided.

Good luck,
Bryan

|||Hi Bryan,

Thank you so much for the answer, I am going to try that.

Appreciated your help.