Anyone have experience with SYSFILES?

According to Microsoft, sysfiles.status:

0x80 = File has been written to since last backup.

Unfortunately, this query is not returning what I expect. That is, if I have inserted/updated/deleted any records, I belive that some underlying file must be written to.

So, I tried:

Code Snippet

select * from sysfiles where status & 0x80 <> 0

I updated several fields in my database, doubled and tripled the size etc. I can't get the above query to return any results.

Does anyone have any experience with this particular flag? I was hoping to check the sysfiles and know if a backup was needed or not.

Thanks mucho.

Interesting. I just tried this with 7.0, 2000, and 2005, and that bit is never set. And 6.5 uses sysdevices rather than sysfiles. I wonder if somewhere in the development cycle they decided not to implement that, and it was never taken out of the documentation drafts. Either that or it's a very long standing, little-known bug. ;-)

There might be some ways to take advantage of the differential changed map to determine if a database has changed since the last backup, but the only way I know to read it isn't well suited for inclusion in a batch job.

Code Snippet

DBCC TRACEON(3604)
DBCC PAGE(databasename, 1, 6, 3)

Granted, a database larger than about 4 GB is probably going to have more than one dcm page, and I'm not entirely sure how to determine where subsequent pages are located.

anyone good with SQL querries?

I have a query that contains a sub query. The problem is that the sub query
returns more than 1 value so it fails with the error (Subquery returned more
than 1 value). Here is a simplified version of how it is setup.
Select * from table1 where column2 = (select col2 from table2 where column 3
=4)
So for the example below it should return rows 1 and 3 from table 1 since a
and c were returned in the subquery.
table 1
col1 col2
1 a
2 b
3 c
table 2
col1 col2 col3
1 a 4
2 b 2
3 c 4
thanks.
Paul G
Software engineer.
On Tue, 30 Jan 2007 16:35:00 -0800, Paul
<Paul@.discussions.microsoft.com> wrote:

>I have a query that contains a sub query. The problem is that the sub query
>returns more than 1 value so it fails with the error (Subquery returned more
>than 1 value). Here is a simplified version of how it is setup.
>Select * from table1 where column2 = (select col2 from table2 where column 3
>=4)
>So for the example below it should return rows 1 and 3 from table 1 since a
>and c were returned in the subquery.
>table 1
>col1 col2
>1 a
>2 b
>3 c
>table 2
>col1 col2 col3
>1 a 4
>2 b 2
>3 c 4
>thanks.
Select * from table1 where column2 in
(select col2 from table2 where column 3 >=4)
J.
|||thanks that is what I was looking for!
Paul G
Software engineer.
"JXStern" wrote:

> On Tue, 30 Jan 2007 16:35:00 -0800, Paul
> <Paul@.discussions.microsoft.com> wrote:
>
> Select * from table1 where column2 in
> (select col2 from table2 where column 3 >=4)
>
> J.
>
>

anyone good with SQL querries?

I have a query that contains a sub query. The problem is that the sub query
returns more than 1 value so it fails with the error (Subquery returned more
than 1 value). Here is a simplified version of how it is setup.
Select * from table1 where column2 = (select col2 from table2 where column 3
=4)
So for the example below it should return rows 1 and 3 from table 1 since a
and c were returned in the subquery.
table 1
col1 col2
1 a
2 b
3 c
table 2
col1 col2 col3
1 a 4
2 b 2
3 c 4
thanks.
--
Paul G
Software engineer.On Tue, 30 Jan 2007 16:35:00 -0800, Paul
<Paul@.discussions.microsoft.com> wrote:
>I have a query that contains a sub query. The problem is that the sub query
>returns more than 1 value so it fails with the error (Subquery returned more
>than 1 value). Here is a simplified version of how it is setup.
>Select * from table1 where column2 = (select col2 from table2 where column 3
>=4)
>So for the example below it should return rows 1 and 3 from table 1 since a
>and c were returned in the subquery.
>table 1
>col1 col2
>1 a
>2 b
>3 c
>table 2
>col1 col2 col3
>1 a 4
>2 b 2
>3 c 4
>thanks.
Select * from table1 where column2 in
(select col2 from table2 where column 3 >=4)
J.|||thanks that is what I was looking for!
--
Paul G
Software engineer.
"JXStern" wrote:
> On Tue, 30 Jan 2007 16:35:00 -0800, Paul
> <Paul@.discussions.microsoft.com> wrote:
> >I have a query that contains a sub query. The problem is that the sub query
> >returns more than 1 value so it fails with the error (Subquery returned more
> >than 1 value). Here is a simplified version of how it is setup.
> >
> >Select * from table1 where column2 = (select col2 from table2 where column 3
> >=4)
> >So for the example below it should return rows 1 and 3 from table 1 since a
> >and c were returned in the subquery.
> >
> >table 1
> >col1 col2
> >1 a
> >2 b
> >3 c
> >
> >table 2
> >col1 col2 col3
> >1 a 4
> >2 b 2
> >3 c 4
> >thanks.
> Select * from table1 where column2 in
> (select col2 from table2 where column 3 >=4)
>
> J.
>
>

anyone good with SQL querries?

I have a query that contains a sub query. The problem is that the sub query
returns more than 1 value so it fails with the error (Subquery returned more
than 1 value). Here is a simplified version of how it is setup.
Select * from table1 where column2 = (select col2 from table2 where column 3
=4)
So for the example below it should return rows 1 and 3 from table 1 since a
and c were returned in the subquery.
table 1
col1 col2
1 a
2 b
3 c
table 2
col1 col2 col3
1 a 4
2 b 2
3 c 4
thanks.
--
Paul G
Software engineer.On Tue, 30 Jan 2007 16:35:00 -0800, Paul
<Paul@.discussions.microsoft.com> wrote:

>I have a query that contains a sub query. The problem is that the sub quer
y
>returns more than 1 value so it fails with the error (Subquery returned mor
e
>than 1 value). Here is a simplified version of how it is setup.
>Select * from table1 where column2 = (select col2 from table2 where column
3
>=4)
>So for the example below it should return rows 1 and 3 from table 1 since a
>and c were returned in the subquery.
>table 1
>col1 col2
>1 a
>2 b
>3 c
>table 2
>col1 col2 col3
>1 a 4
>2 b 2
>3 c 4
>thanks.
Select * from table1 where column2 in
(select col2 from table2 where column 3 >=4)
J.|||thanks that is what I was looking for!
--
Paul G
Software engineer.
"JXStern" wrote:

> On Tue, 30 Jan 2007 16:35:00 -0800, Paul
> <Paul@.discussions.microsoft.com> wrote:
>
> Select * from table1 where column2 in
> (select col2 from table2 where column 3 >=4)
>
> J.
>
>sql

anyone good with Integration services

Hi I have a simple package I am putting together. The package runs a query
on an SQL database (database1) and the results are then copied over to
another SLQ database (database2). I have this set up and it works. I am
trying to add another element that will update a table in database1 as long
as the transfer is successful. I also have a script in the control flow that
writes out a message, success! and this seems to work.
Anyhow not quite sure how what to add to update database1 if the transfer is
successful--
thanks.
Paul G
Software engineer.On Oct 31, 11:44 am, Paul <P...@.discussions.microsoft.com> wrote:
> Hi I have a simple package I am putting together. The package runs a query
> on an SQL database (database1) and the results are then copied over to
> another SLQ database (database2). I have this set up and it works. I am
> trying to add another element that will update a table in database1 as long
> as the transfer is successful. I also have a script in the control flow that
> writes out a message, success! and this seems to work.
> Anyhow not quite sure how what to add to update database1 if the transfer is
> successful--
> thanks.
> Paul G
> Software engineer.
Try Adding a Execute SQL Task as control flow and define your update
Query in the task. change the precedence to success beoe this task.|||Hi thanks that seemed to do the trick. I currently a script task on success
and on failure well that writes out a corresponding message. I was wondering
if you know if there is a way to include the number of records updated from
one of the tasks in the message? It would have to be retreived in the
script? Also are the event handlers for any event in a control flow?
--
Paul G
Software engineer.
"Maninder" wrote:
> On Oct 31, 11:44 am, Paul <P...@.discussions.microsoft.com> wrote:
> > Hi I have a simple package I am putting together. The package runs a query
> > on an SQL database (database1) and the results are then copied over to
> > another SLQ database (database2). I have this set up and it works. I am
> > trying to add another element that will update a table in database1 as long
> > as the transfer is successful. I also have a script in the control flow that
> > writes out a message, success! and this seems to work.
> > Anyhow not quite sure how what to add to update database1 if the transfer is
> > successful--
> > thanks.
> > Paul G
> > Software engineer.
> Try Adding a Execute SQL Task as control flow and define your update
> Query in the task. change the precedence to success beoe this task.
>

Any work around to pass parameters to OPENQUERY

I need to query a linked server which contains a table of million records an
d
need to fetch only the relevant records from the linked server.
Any ideas? Please helpSaji
Have you tried to use WHERE condition? Can you show us what you are trying
to do?
"Saji" <Saji@.discussions.microsoft.com> wrote in message
news:31530C04-03B9-4C24-9169-CBBD26773F3C@.microsoft.com...
>I need to query a linked server which contains a table of million records
>and
> need to fetch only the relevant records from the linked server.
> Any ideas? Please help|||This seems to work..
SELECT * FROM OPENQUERY(PS, '
SELECT
*
FROM
TESTDTA.F0401Z1
JOIN TESTDTA.F0101Z2 ON TESTDTA.F0401Z1.VOAN8 = TESTDTA.F0101Z2.SZAN8
WHERE
VOEDSP != ''C'' AND
VODRIN = ''2''
ORDER BY VOAN8, VOEDBT
')
"Saji" wrote:

> I need to query a linked server which contains a table of million records
and
> need to fetch only the relevant records from the linked server.
> Any ideas? Please help

Any way to use params in a SELECT...IN query?

I wish to execute a query like this:
SELECT name FROM Owner WHERE ownerID IN (@.ownerIDs)
Is there any way to accomplish that? I'm using ADO.NET to execute this..
Thanks,
Pablo
--
Sleep till the end, true soul and sweet!
Nothing comes to thee new or strange.
Sleep full of rest from head to feet;
Lie still, dry dust, secure of change.
-- Alfred Tennyson (1809-1892)
-- To J. S.
Pablo Montilla
www.odyssey.com.uyCheck out http://www.sommarskog.se/dynamic_sql.html#List
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Pablo Montilla" <melkor@.odyssey.com.uy> wrote in message
news:op.typkr1dkcj6shk@.chimera.odyssey.com.uy...
>I wish to execute a query like this:
> SELECT name FROM Owner WHERE ownerID IN (@.ownerIDs)
> Is there any way to accomplish that? I'm using ADO.NET to execute this..
> Thanks,
> Pablo
> --
>
> Sleep till the end, true soul and sweet!
> Nothing comes to thee new or strange.
> Sleep full of rest from head to feet;
> Lie still, dry dust, secure of change.
> -- Alfred Tennyson (1809-1892)
> -- To J. S.
> Pablo Montilla
> www.odyssey.com.uy|||Pablo,
I think Tibor meant:
Arrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
AMB
"Pablo Montilla" wrote:
> I wish to execute a query like this:
> SELECT name FROM Owner WHERE ownerID IN (@.ownerIDs)
> Is there any way to accomplish that? I'm using ADO.NET to execute this..
> Thanks,
> Pablo
> --
>
> Sleep till the end, true soul and sweet!
> Nothing comes to thee new or strange.
> Sleep full of rest from head to feet;
> Lie still, dry dust, secure of change.
> -- Alfred Tennyson (1809-1892)
> -- To J. S.
> Pablo Montilla
> www.odyssey.com.uy
>|||On Sat, 15 Sep 2007 20:22:00 -0300, Alejandro Mesa
<AlejandroMesa@.discussions.microsoft.com> wrote:
> Pablo,
> I think Tibor meant:
> Arrays and Lists in SQL Server
> http://www.sommarskog.se/arrays-in-sql.html
> AMB
>
Many thanks for the links!
Regards,
Pablo
--
Don't vote. The government will get in.
-- Anarchist's Slogan
Pablo Montilla
www.odyssey.com.uy

Any way to query EXECUTE perms on stored procs?

I've recently been tasked with duplicating the permissions from one
account to another. We have a development, system test, and production
SQL Server, and approximately 35 databases in each. We use a fine
level of control on this particular account because it's what the
applications use to log in.
I've granted datareader/datawriter to the new account for all the dbs.
Each db, however, has a ton of stored procedures. Not all of them are
executable by the original account. I found a script that grants
access to all user stored procs, which is ok and will save me a few
days worth of point-and-click - but I'd be a happier DBA if I could
find a way to query the execute permissions on the stored procs to copy
them to the new account.
Any ideas? Below is the script I've used so far on the dev server.
SELECT 'GRANT EXECUTE ON ' + NAME + ' TO MyLogin' -- Replace MyLogin
with the name of your new Login
FROM SYSOBJECTS
WHERE TYPE = 'P'
AND LEFT(NAME,2) <> 'sp' -- system procs
AND LEFT(NAME,2) <> 'dt' -- VSS procs
Thanks,
Bill
A.K.A. PSPDBAThe PERMISSIONS system function can help you identify permission for the
current user:
http://msdn.microsoft.com/library/d...br />
6f78.asp
You could use it in combination with the SETUSER function:
http://msdn.microsoft.com/library/d...br />
6f78.asp
ML
http://milambda.blogspot.com/|||Ok, the sp_helprotect gives me back a list of the execute permissions
for the first user. I don't see how SETUSER is going to help me..|||PSPDBA (williambr@.state.pa.us) writes:
> Ok, the sp_helprotect gives me back a list of the execute permissions
> for the first user. I don't see how SETUSER is going to help me..
I guess the idea is:
SETUSER 'accountunderinvestigation'
go
SELECT name
FROM sysobjects
WHERE xtype = 'P'
AND permissions(id) & 32 = 1
go
SETUSER
That would list all the procedures that the account has permissions to
execute.
I should not that this solution contains three elements that are
deprecated in SQL 2005:
* The SETUSER command (Use EXECUTE AS instead)
* sysobjects (use sys.procedures instead)
* permissions(). (Use fn_my_permissions instead).
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Any way to put a query timeout on the datasource behind a report model?

Folks, we're wanting to put a timeout of 2 minutes on the queries which get generated by an ad-hoc report built in Report Builder. This particular report model has been generated on top of a cube by clicking the Generate Model button after creating an Analysis Services datasource in Report Manager. The connection string for that datasource reads:

Data Source=(local);Initial Catalog=MyCubeDatabase

I expected there to be a connection string property I could set (such as "Timeout=120") but I can't seem to find that in the documentation anywhere... And Timeout=120 doesn't appear to do it. Do you know of one? If not, do you know of another way to put a timeout limit on a Report Builder generated query or a Report Builder report itself?

I tried setting the "Limit report execution to the following number of seconds" at /Reports/Pages/Settings.aspx in Report Manager to 120, but that does not appear to have an effect on Report Builder reports.

See the following bug I reported for more details:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=232694

This is quite an urgent issue for us as we go live in a week. Any help would be appreciated!

Time out is this a joke , this tool can't even get the rigth result's .... Maybe in version 11B pacth 12334 you have the time out feature

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 make this shorter?

I have a T-SQL query that is used to pull up some data for once-a-day export, just out of curiosity more then anything, is there a way to make this shorter?

SELECT DISTINCT
u.userId,
u.lastName,
u.firstName,
u.address1,
u.address2,
u.city,
u.state,
u.zip,
CASE WHEN u.UserClassID_fk BETWEEN 1 AND 3 AND COALESCE(u.RetailerNumber_fk,0)= 0
THEN 'Corporate'
WHEN u.UserClassID_fk BETWEEN 1 AND 3 AND COALESCE(u.RetailerNumber_fk,0)<> 0
THEN 'RETAILER'+ CONVERT(varchar, COALESCE(u.RetailerNumber_fk,0))
WHEN u.UserClassID_fk BETWEEN 4 AND 8 AND COALESCE(p.plantCode,ap.plantNumber_fk,ps.plantNum ber_fk,0) = 0
THEN 'Corporate'
WHEN u.UserClassID_fk BETWEEN 4 AND 8 AND COALESCE(p.plantCode,ap.plantNumber_fk,ps.plantNum ber_fk,0) <> 0
THEN 'PLANT'+ CONVERT(varchar, COALESCE(p.plantCode,ap.plantNumber_fk,ps.plantNum ber_fk,0))
WHEN u.UserClassID_fk BETWEEN 9 AND 14 AND COALESCE(p.regionNumber_fk,rg.regionNumber,0) = 0
THEN 'Corporate'
WHEN u.UserClassID_fk BETWEEN 9 AND 14 AND COALESCE(p.regionNumber_fk,rg.regionNumber,0) <> 0
THEN 'REGION'+ CONVERT(varchar, COALESCE(p.regionNumber_fk,rg.regionNumber,0))
END CPGkey
FROM [...] JOIN [...]

I'm hoping there is something in a way of...
If userClass Between 1 and 3 Then
IF COALESCE(u.RetailerNumber_fk,0)= 0 Then 'Corporate'
ELSE 'RETAILER'+ CONVERT(varchar, COALESCE(u.RetailerNumber_fk,0))
End IF as CPGKey,

Thanks in advace.Not really. This is a little more concise, as it avoids duplicating comparisons:CASE WHEN u.UserClassID_fk BETWEEN 1 AND 3 THEN
CASE WHEN COALESCE(u.RetailerNumber_fk,0)= 0 THEN 'Corporate'
ELSE 'RETAILER'+ CONVERT(varchar, COALESCE(u.RetailerNumber_fk,0)) END
WHEN u.UserClassID_fk BETWEEN 4 AND 8 THEN
CASE WHEN COALESCE(p.plantCode,ap.plantNumber_fk,ps.plantNum ber_fk,0) = 0 THEN 'Corporate'
ELSE 'PLANT'+ CONVERT(varchar, COALESCE(p.plantCode,ap.plantNumber_fk,ps.plantNum ber_fk,0)) END
WHEN u.UserClassID_fk BETWEEN 9 AND 14 THEN
CASE WHEN COALESCE(p.regionNumber_fk,rg.regionNumber,0) = 0 THEN 'Corporate'
ELSE 'REGION'+ CONVERT(varchar, COALESCE(p.regionNumber_fk,rg.regionNumber,0)) END
END CPGkeysql

Any way to find user who did update in SQL SERVER?

Hi there,

Is there any way to find the person or user who did update transaction
manually
in query analyser and not through application in certain time?
As i have check thru log file but cannot find anything.

Any command or software to check it?

Thanks for the help!On Nov 20, 6:45 pm, LaMoRt <cwe...@.gmail.comwrote:

Quote:

Originally Posted by

Hi there,
>
Is there any way to find the person or user who did update transaction
manually
in query analyser and not through application in certain time?
As i have check thru log file but cannot find anything.
>
Any command or software to check it?
>
Thanks for the help!

Try running

sp_who
sp_who2
Profiler|||On Nov 20, 6:45 pm, LaMoRt <cwe...@.gmail.comwrote:

Quote:

Originally Posted by

Hi there,
>
Is there any way to find the person or user who did update transaction
manually
in query analyser and not through application in certain time?
As i have check thru log file but cannot find anything.
>
Any command or software to check it?
>
Thanks for the help!

Duplicate of http://groups.google.com/group/comp...28b3b6e9b5b1558

Any way to easily add this computed column (divide by zero problem)

I have this query that I would rather not turn into a stored proc.
because the client really has no budget. I was hoping there would be a
way to add this statement inline to the following sql with some kind of
IF statement without writing it all out. The problem is either total
cost or conversions will have some zeros in the table.

SUM([total cost]/[cost per conversion])

Any help would be appreciated - Happy New Year

SELECT [Search Term], SUM([total cost]/[cost per conversion]) as calcw,
SUM([impressions]) AS impress, SUM([Total Cost]) AS totalcost,
SUM([Total Clicks]) AS totalclicks, SUM(Conversions) AS totalconv,
SUM([Cost Per Conversion]) AS costconv FROM csv where [start date]
>='01/01/04' and [end date] <='12/31/04' GROUP BY [Search Term] ORDER
BY [Search Term] ASCDo you just want to ignore the row in the sum if either value is zero? If
so:

SUM([total cost]/NULLIF([cost per conversion],0))

--
David Portas
SQL Server MVP
--|||I would rather the row be included as just returned as zero

Thanks!|||Also I tried that statement and it works well except I think the
calculation comes out incorrect - It needs to be something like the
SUM of total cost divived by the SUM of Conversions.

Thanks|||COALESCE( SUM([total cost]) / NULLIF(SUM([cost per conversion]),0) ,0)

--
David Portas
SQL Server MVP
--|||Thank you very much David - That seems to have done it!
Thanks again...

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 T-SQL advice?

Okay, given my newness to SQL, and the complexity of this query, I thought I'd
run this by you for your opinion:

SELECT DISTINCT a.WeekEnding, c.lastdate, c.numlate, b.totaldate

FROM Accomplishment a LEFT OUTER JOIN

(SELECT weekending, COUNT(weekending) AS
totaldate

FROM Accomplishment

WHERE (EmployeeID = 50)

GROUP BY weekending) b ON a.WeekEnding =
b.weekending LEFT OUTER JOIN

(SELECT weekending, MAX(entrydate) AS
lastdate, COUNT(weekending) AS numlate

FROM accomplishment

WHERE employeeid = 50 AND entrydate >
weekending

GROUP BY weekending) c ON a.WeekEnding =
c.weekending

ORDER BY a.WeekEnding

What I'm trying to do is for each pay period find which ones the employee
submitted a timesheet and which they were late (and if they were late, how
many of them). However, the query takes a good 5 seconds, and it seems
removing the "entrydate > weekending" clause speeds things up to almost
instant, however it does ruin the count that I really want. No idea why
that makes such a difference..CK (c_kettenbach@.hotmail.com) writes:
> Okay, given my newness to SQL, and the complexity of this query, I
> thought I'd run this by you for your opinion:
>...
> What I'm trying to do is for each pay period find which ones the employee
> submitted a timesheet and which they were late (and if they were late, how
> many of them). However, the query takes a good 5 seconds, and it seems
> removing the "entrydate > weekending" clause speeds things up to almost
> instant, however it does ruin the count that I really want. No idea why
> that makes such a difference..

Really why it takes longer with that clause I cannot tell, as I don't
know its tables nor its indexes. However, I found a simplification of
the query:

SELECT a.WeekEnding, b.lastdate, b.numlate, b.totaldate
FROM Accomplishment a
LEFT JOIN (SELECT weekending,
COUNT(weekending) AS totaldate,
SUM(CASE WHEN entrydate > weekending
THEN 1
ELSE 0
END) AS numlate,
MAX(CASE WHEN entrydate > weekending
THEN entrydate
END) AS lastdate
FROM Accomplishment
WHERE EmployeeID = 50
GROUP BY weekending) b ON a.WeekEnding = b.weekending
ORDER BY a.WeekEnding

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Mon, 06 Feb 2006 17:11:38 GMT, CK wrote:

>Okay, given my newness to SQL, and the complexity of this query, I thought I'd
>run this by you for your opinion:
>
>SELECT DISTINCT a.WeekEnding, c.lastdate, c.numlate, b.totaldate
>FROM Accomplishment a LEFT OUTER JOIN
> (SELECT weekending, COUNT(weekending) AS
>totaldate
> FROM Accomplishment
> WHERE (EmployeeID = 50)
> GROUP BY weekending) b ON a.WeekEnding =
>b.weekending LEFT OUTER JOIN
> (SELECT weekending, MAX(entrydate) AS
>lastdate, COUNT(weekending) AS numlate
> FROM accomplishment
> WHERE employeeid = 50 AND entrydate >
>weekending
> GROUP BY weekending) c ON a.WeekEnding =
>c.weekending
>ORDER BY a.WeekEnding
>
>What I'm trying to do is for each pay period find which ones the employee
>submitted a timesheet and which they were late (and if they were late, how
>many of them). However, the query takes a good 5 seconds, and it seems
>removing the "entrydate > weekending" clause speeds things up to almost
>instant, however it does ruin the count that I really want. No idea why
>that makes such a difference..

Hi CK,

The query looks more complicated than it needs to be. Based on a whole
lot of assumptions about your data and without any testing (check out
www.aspfaq.com/5006 if you prefer less assumptions and more testing),
I'd suggest changing it to

SELECT WeekEnding,
MAX(CASE WHEN entrydate > weekending THEN entrydate END) AS
LastDate,
COUNT(CASE WHEN entrydate > weekending THEN 'CountMe' END) AS
NumLate,
COUNT(*) AS TotalDate
FROM Accomplishment
WHERE EmployeeId = 30
GROUP BY WeekEnding
ORDER BY WeekEnding

Or, if you want a report for all employees:

SELECT EmployeeID,
WeekEnding,
MAX(CASE WHEN entrydate > weekending THEN entrydate END) AS
LastDate,
COUNT(CASE WHEN entrydate > weekending THEN 'CountMe' END) AS
NumLate,
COUNT(*) AS TotalDate
FROM Accomplishment
GROUP BY EmployeeID,
WeekEnding
ORDER BY EmployeeID,
WeekEnding

--
Hugo Kornelis, SQL Server MVP|||Seeing Hugo's queries, I realize that I did a blunder when I cut
DISTINCT. Assuming that you want all weekendings - also those when
Employee 50 did not enter anything at all, this may be better:

SELECT a.WeekEnding, b.lastdate, b.numlate, b.totaldate
FROM (SELECT DISTINCT WeekEnding FROM Accomplishment) AS a
LEFT JOIN (SELECT weekending,
COUNT(weekending) AS totaldate,
SUM(CASE WHEN entrydate > weekending
THEN 1
ELSE 0
END) AS numlate,
MAX(CASE WHEN entrydate > weekending
THEN entrydate
END) AS lastdate
FROM Accomplishment
WHERE EmployeeID = 50
GROUP BY weekending) b ON a.WeekEnding = b.weekending
ORDER BY a.WeekEnding

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>> What I'm trying to do is for each pay period find which of the employees submitted a timesheet and which they were late (and if they were late, how many of them). <<

Where is the payperiod table in your data model??

>> No idea why that makes such a difference.. <<

Because your schema design is a pile of crap?? Duh!

Wild Idea! for uou Please post DDL, so that people do not have to
guess what the keys, constraints, Declarative Referential Integrity,
data types, etc. in your schema are. Sample data is also a good idea,
along with clear specifications. It is very hard to debug code when
you do not let us see it.

Newbies think that DDL is not as important as DML. They assume they
can "repair" a bad schema in code. You cannot. Ever. The best you can
hope for is a horrible nested sert of joins, like Sommarskog posted.

Do you want ot do it right or just kludge and patch it?

Any Timeout setting in ConnectionString of web.config

Hi:

I have some query that takes quite a long time to process in the sql server and every time the page seems to time out.

I wondor is there any Timeout setting that I can defined in the database ConnectionString in web.config file so that I can extend the "wait" time?

Many thanks!

I believe there is:

"integrated security=SSPI;SERVER=YOUR_SERVER;DATABASE=YOUR_DB_NAME;Connect Timeout=45;";

|||

Jason,

I recently had a time out issue with an app after some servers where moved to a centralized location. In my case, the use of the sql server name caused my app to time out as apparently the newly located web server wasn't able to resolve the sql server name in the web.config file fast enough against the WINS server. The solution was to use our sql server's IP address in web.congif file instead of it's name. Then the app ran just fine.

Time outs can definitely be network related. Need to look at that as well as any app configuring.

|||

Unfortunately not Jason. The timeout in the connection string only controls the timeout of the connection (How long the connection will wait before giving up), normally it's irrelevant as you'll get a (fairly) quick negative response.

That said, in your pages where the default 30 second command timeout is too short, in your SqlDataSource_Selecting/Inserting events, the e parameter will have a reference to the actualy SqlCommand object that is about to be used. Set it's timeout. Off the top of my head, I believe it is like:

e.SelectCommand.Timeout=90

That is assuming that you are having the issue with a SqlDataSource. SqlCommand objects have their own timeout parameter that you can set if that is what you are using and having an issue with.

|||

Actually, e.Command.CommandTimeout=90

Any third-party freebies to administer MSDE?

Are there any third party freebies available to administer MSDE? I'm looking
to replace Interbase with MSDE, but I'll need some sort of Query Analyzer /
Admin interface to replace IBConsole which is free with Interbase. Is
anything like this available?
Thanks for your help,
Joe Geretz
This link may help.
http://www.microsoft.com/sql/msde/partners/default.asp
Jim
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:u91ZuEDGEHA.3856@.TK2MSFTNGP12.phx.gbl...
> Are there any third party freebies available to administer MSDE? I'm
looking
> to replace Interbase with MSDE, but I'll need some sort of Query Analyzer
/
> Admin interface to replace IBConsole which is free with Interbase. Is
> anything like this available?
> Thanks for your help,
> Joe Geretz
>
|||http://www.aspfaq.com/2442
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:u91ZuEDGEHA.3856@.TK2MSFTNGP12.phx.gbl...
> Are there any third party freebies available to administer MSDE? I'm
> looking
> to replace Interbase with MSDE, but I'll need some sort of Query Analyzer
> /
> Admin interface to replace IBConsole which is free with Interbase. Is
> anything like this available?
> Thanks for your help,
> Joe Geretz
>
|||Joseph,

> Are there any third party freebies available to administer MSDE?
There aren't only 3rd party tools, have a look at MS Web Administrator:
http://www.microsoft.com/downloads/d...displaylang=en
The only disadvantage I can see is the poor performance.
I have also tested DbaMgr (http://www.asql.biz/DbaMgr.shtm) and the free
version of myLittleAdmin (http://www.mylittletools.net/scripts/en/mla_sql/).
For having a quick look at your databases, myLittleAdmin is very good.
Bye,
mm

Any suggestions on how to optimize a query written in Dynamic SQL?

I added the subquery and now this thing consistently takes more than five minutes to return 7100+ rows. Any suggestions? Thanks again, you guys are the best.

ddave
---------
SET @.StrQry1 = '(SELECT 1 AS Counter, Q1.SubsidyLevel, VEL.*
FROM dbo.ViewEligibilityPHC VEL
LEFT OUTER JOIN (SELECT *
FROM dbo.MEMB_LISHISTS l
WHERE l.LISThruDate is null
AND l.Deleted = ''0'') AS Q1 ON VEL.MEMBID = Q1.MemberID
WHERE VEL.OPTHRUDT is null
AND VEL.OPT LIKE ''' + @.HPlan + ''' AND (VEL.PCP IS NULL OR VEL.PCP LIKE ''' + @.Prvdr + ''')
AND VEL.HCC LIKE ''' + @.HCC + ''' AND (VEL.CaseMgrID IS NULL OR VEL.CaseMgrID LIKE ''' + @.CaseMngr + ''')
AND VEL.OPFROMDT <= CAST(''' + @.SDate + ''' AS datetime)
AND ISNULL(VEL.OPTHRUDT, CAST(''' + @.TDate + ''' AS datetime)) >= CAST(''' + @.EDate + ''' AS datetime)) 'Be nice and post the results of:

PRINT @.StrQry1

...so we can focus on the SQL and not the dynamic concatenation...|||Sorry it took so long. I had to modify the procedure to print that string and run it again:-). These are the fields. It is confidential medical data so I can't put the actual data here. The procedure is below. Also if there are any obvious errors any tips would be greatly appreciated.

Counter
SubsidyLevel
MEMBID
PATID
SUBSSN
AIDSDATE
AIDCODE
OPFROMDT
OPTHRUDT
OPT
CURRENTCOUNTYID
LASTNM
FIRSTNM
BIRTH
HCP1NUM
SEX
HCC
HCCName
CaseMgrID
CaseMgrName
PCPFROMDT
PCP
PCPName
STREET
STREET2
CITY
STATE
ZIP
PHONE
CURRHIST
INTLZIP
TransferOut
CoPay
CoPayEffDate
PartsABD

-- The entire code is:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

alter PROCEDURE DBO.STANDARD_MEMBERSHIP_QUERY_test_20071015

@.StartDate DateTime, @.EndDate DateTime,
@.HPlan varchar(20), @.HCC varchar(3),
@.Prvdr varchar(20), @.CaseMngr varchar(5),
@.Report smallint

AS

SET NOCOUNT ON

BEGIN

DECLARE @.SDate varchar(10), @.EDate varchar(10),
@.TDate varchar(10)

SET @.SDate = Convert(varchar(10),@.StartDate, 101)
SET @.EDate = Convert(varchar(10), @.EndDate, 101)
SET @.TDate = '06/06/2079'

DECLARE @.SDateP varchar(10), @.EDateP varchar(10)
DECLARE @.MySQL varchar(1600), @.StrQry1 varchar(800), @.StrQry2 varchar(800)

SET @.SDateP = Convert(varchar(10), DateAdd(Month, -1, @.StartDate), 101)
SET @.EDateP = Convert(varchar(10), DateAdd(day, -1, DateAdd(month, DateDiff(month, 0, Cast(@.SDateP AS datetime))+1, 0)), 101)

SET @.StrQry1 = '(SELECT 1 AS Counter, Q1.SubsidyLevel, VEL.*
FROM dbo.ViewEligibilityPHC VEL
LEFT OUTER JOIN (SELECT *
FROM dbo.MEMB_LISHISTS l
WHERE l.LISThruDate is null
AND l.Deleted = ''0'') AS Q1 ON VEL.MEMBID = Q1.MemberID
WHERE VEL.OPTHRUDT is null
AND VEL.OPT LIKE ''' + @.HPlan + ''' AND (VEL.PCP IS NULL OR VEL.PCP LIKE ''' + @.Prvdr + ''')
AND VEL.HCC LIKE ''' + @.HCC + ''' AND (VEL.CaseMgrID IS NULL OR VEL.CaseMgrID LIKE ''' + @.CaseMngr + ''')
AND VEL.OPFROMDT <= CAST(''' + @.SDate + ''' AS datetime)
AND ISNULL(VEL.OPTHRUDT, CAST(''' + @.TDate + ''' AS datetime)) >= CAST(''' + @.EDate + ''' AS datetime)) '

SET @.MySQL = @.StrQry1

END

EXEC (@.MySQL)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO|||also and in the meantime I am studying how to index the view in question. I have rarely worked with views and I have never created an index before.

ddave|||One more time...
Be nice and post the results of:

PRINT @.StrQry1

...so we can focus on the SQL and not the dynamic concatenation...|||to do what blindman is asking, change your EXEC(@.MySQL) to PRINT @.MySQL and post the result.|||You should try to avoid sub queries.

SET @.StrQry1 = '(SELECT 1 AS Counter, Q1.SubsidyLevel, VEL.*
FROM dbo.ViewEligibilityPHC VEL
LEFT JOIN dbo.MEMB_LISHISTS Q1
on l.LISThruDate is null
AND l.Deleted = ''0'' and VEL.MEMBID = Q1.MemberID
WHERE VEL.OPTHRUDT is null
AND VEL.OPT LIKE ''' + @.HPlan + ''' AND (VEL.PCP IS NULL OR VEL.PCP LIKE ''' + @.Prvdr + ''')
AND VEL.HCC LIKE ''' + @.HCC + ''' AND (VEL.CaseMgrID IS NULL OR VEL.CaseMgrID LIKE ''' + @.CaseMngr + ''')
AND VEL.OPFROMDT <= CAST(''' + @.SDate + ''' AS datetime)
AND ISNULL(VEL.OPTHRUDT, CAST(''' + @.TDate + ''' AS datetime)) >= CAST(''' + @.EDate + ''' AS datetime)) '

I also suspect that massive index/table scans causes your problem.

Please post the execution plan for the query.
How to:
print @.StrQry1
copy the SQL to a new query windows.
Execute
set showplan_text on
go
your query here...

Copy and paste the text from the result window to this thread.|||Actually we resolved it by altering the view. We needed to add one field and we just added it to the view instead of going through all this. Thanks anyway.

ddave|||the word dynamic in the term dynamic sql almost makes it sound like a good thing. it deserves a name more akin to it's nature. maybe kludged sql? maybe poor perfroming insecure code?|||Perhaps "Injectable SQL"? Or maybe "Objectional SQL"?

It has its uses, but buy can it be abused.|||Why are you using dynamic sql ?, I usually only use dynamic sql if table names need to be assigned dynamically based on certain conditions. Where do you append the "%" for the likes, as part of the input variable ?|||That's when I use it also to dynamically name tables for example. I got it that way from my supervisor. Sigh, I guess I will just have to blame him. :)

ddave|||And when you have to use dynamic SQL (or injection-sql) do NOT use do it by concatenating the parameters into the sql string. Use sp_executesql with parameters.

Any SQL wizard can help? Reformat the input file and transfer into SQL server

I am trying to transfer 200 txt files into SQL server by using query analyzer.
The command is 'Bulk insert [tableName] from 'path\filename.txt'
However, I need to read and modifiy the txt file.
I am new to SQL server but I believe there must be some one who is a wizard can do what I want easily.

Thank you for the help in advance!

Here is the raw data layout, which is comma delimited.
BDate 1/1/1990 BDate 1/1/1990 BDate 1/1/1990 BDate 1/1/1990
Edate 1/1/2005 Edate 1/1/2005 Edate 1/1/2005 Edate 1/1/2005
Fq D Fq D Fq D Fq D
Date R P M E D Date R P M E D Date R P M E D Date R P M E D
1/1/90 1 2 3 4 5 1/1/90 2 3 4 5 6 1/1/90 3 4 5 6 7 1/1/90 4 5 6 7 8
2 3 4 5 6 1 2 3 4 5 3 4 5 6 7 6 7 8 9 1
1/1/05 ..... 1/1/05 ... 1/1/05 .... 1/1/05 ....

This is the desired output after load into the table, which is tacking each repeating block on top of each other.
Date R P M E D
1/1/90 1 2 3 4 5
2 3 4 5 6
1/1/05 .....
1/1/90 2 3 4 5 6
2 3 4 5 6
1/1/05 .....
1/1/90 3 4 5 6 7
3 4 5 6 7
1/1/05 .....
1/1/90 4 5 6 7 8
6 7 8 9 1
1/1/05 ....."I am trying to transfer 200 txt files into SQL server by using query analyzer."
--DTS might be more appropriate.

"I am new to SQL server but I believe there must be some one who is a wizard can do what I want easily."
--Faith is a powerful thing.

"Here is the raw data layout, which is comma delimited."
--What you posted is not comma delimited.

"This is the desired output after load into the table, which is tacking each repeating block on top of each other."
--You are going to need to load this data into a staging table and normalize it before loading into your production tables. The process will be complex, involving several passes through the data.

If at all possible, try to get your source data in a better format. Practically any other format would be preferable to what you posted.|||Blindman,
Thank you for your reply.
You are right... I forgot to put "," in my sample file layout.
I am using another source provider to request time series in excel. This is the most efficient way I can utilize excel ability (256 columns and over 65,000 rows). That's why the raw data layout looks wired. However, I have to stick to it.

I was thinking to load these files into a table to normalize but I am not sure if I know SQL well enough to say this is the best solution. I think I got the answer from you.

What is staging db. I assume it is one of defualt DB in in enterprise manager, however, I did not see it. Or this is the name you gave?

Thank you again for the help.
Shiparsons|||Not "Staging DB". "Staging TABLE."

A staging table is basically an table that has the same structure as your input data, with additional columns added as needed to keep track of records as they are being processed. I always add an "Imported" column that defaults to getdate(), and an ImportErrors column that I populate as necessary during processing.

Your staging table should have no Primary Keys or constraints (unless you add a surrogate PKey for processing...), so that your import process never fails because the data does not match what is expected.

Once the data is in the staging table you cleans it and make sure it satisfies all the business rules required by your production tables. Then you make as many passes through the staging table as necessary to update the various production tables it feeds, starting with the top-level tables.|||Thank you for the explanation.
What datatype I should use when I create my staging table? I assume this is nonconstraints type since my raw data contains text, datetime, and float.

Thank you,
Qing|||You should try to match the datatype to the type of the data being entered, though some people just make all staging table columns varchar by default. I don't do this, as a rule, but you may have no other choice since your import file is actually a mix of different layouts. String fields are the only column types that will accept any input type.|||Blindman,
Thank you for the help.

I will try.

shiparsons

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