Data Flow Task Problem: creating

Showing posts with label creating. Show all posts

Tuesday, March 27, 2012

data extension error

Im in the process of creating my first data extension for reporting
services.
Im getting an error while creating a new report via the add report
wizard. My entry shows up in the type dropdown just as expected but
the next screen has a text box that says querystring. When I click
next I get the following error:
"an error occurred while the query design method was being saved.
Object not set to an instance of an object."
How might I know where this occured? Any help is appreciated.And yes I am debugging this and it is failing in the
the createcommand() in the connection class.
I get no clue as to why. Perhaps its my db conn string. Or something
with those config files.|||And yes I am debugging this and it is failing in the
the createcommand() in the connection class.
I get no clue as to why. Perhaps its my db conn string. Or something
with those config files.

Sunday, March 25, 2012

Data entry in a datetime field

Hi,

I am using SQL Server Management Studio Express for creating my database. I also use the GUI tools for data entry instead of using T-SQL. Whenever I try to enter a value in a field that has a smalldatetime data type, it gives me an error message -

"Invalid Value for cell (row 1, column 2).

The changed value in this cell was not recognised as valid.

.Net Framework Datatype: Datetime

Error Message: Index was outside the bounds of the array.

Type a value appropriate for this data type or press ESC to cancel the change."

Now I have tried entering all different combinations in which one can enter a date or a time or both, including in the format I have specified in the windows regional settings, but I always get the same error message.

How do I input data into the field?

Hi,

you are using a format SQL Serve ri snot expecting. Which User language did you configured for the accessing user and which date are you trying to insert ?
HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

I use only English as the default language for all programs, OS included. The date in question is not just one particular date, it is any date or time value, and I've been facing this issue ever since I started using SQL Server Express a year ago. Only I haven't used it much since, and need to seriously develop something now, that I bothered to ask the question here.

Some examples of the values I tried entering yesterday, and none of them worked. (I got the same error message for each value) -

1-1-2007

01-01-2007

01/01/2007

01,01,2007

01:01:2007

Jan 01, 2007 (this is the format I have specified for short date in the Win regional settings - MMM dd, yyyy)

I even tried entering time alongside with all those above figures in the format -

11:35 PM

11:35 AM

23:35

The only time value(s) I didn't try was/were - hh:mm:ss tt - which is the time format specified in my Win regional settings, neither standalone, nor with the date values.

I also tried entering a time value standalone, without the date. Still no go.

(Also, as I have gleaned from previous posts, if all goes well and SQL Server accepts your data, then if you enter just a date value in a datetime field, then the field automatically gets populated with the value of the system time at that moment, and if you enter just a standalone time value, then the date part will be filled by the system date value on the date of the entry. Am I correct so far?)

Now I'm in a real fix because of the inability to enter date/time values.

Could you please help me out?

|||Guys, I need your help. Please give me a solution to my problem. (This post has already been relegated to the second page without any replies).|||People, I need some help here. Any replies addressing my issue will be greatly appreciated. Thank You in advance.|||

I don't understand. I can enter the datetime values in all the above mentioned combinations if I use an Insert or Update statement using T-SQL, but I can't seem to enter data in the datetime/smalldatetime fields at all if I try to use the GUI of Management Studio Express to enter data. (right click the table, select show table, and the grid view pops up). Ditto for using GUI tools of Visual Studio Express. Is this a bug in the Visual Studio (Express) software?

Also, my other concerns are, once I develop a front-end for data entry using VB, will I face the same issues with data entry in the datetime fields using the GUI of the front end? I can't test that right now, because I'm still learning VB, and cannot create the front end just yet.

|||

Alright, I have done a complete reinstall of Win XP, and have also reinstalled SQL Server Express. Prior to this, I had SQLExpress SP1, but now I have directly installed SP2.

Now I continue to face the same issue. SQL Server will not accept any value for any datetime field, unless it is entered as an SQL insert statement. It won't accept the value entered in the exact same format as the insert statement from the GUI mode of Management Studio Express, nor will it take any values from any GUI developed using VBExpress and the fill dataset method. It throws an error everytime. I don't know any other way of databinding and updating/inserting using VB at this time.

I am at my wits' end because of this. I ask again, is this a bug in SQL Server Express? If so how do I report it to MS and get my issue resolved? If not, even then how can I solve this problem?

sql

Data entry in a datetime field

Hi,

"Invalid Value for cell (row 1, column 2). The changed value in this cell was not recognised as valid. .Net Framework Datatype: Datetime Error Message: Index was outside the bounds of the array. Type a value appropriate for this data type or press ESC to cancel the change."

How do I input data into the field?

Hi,

you are using a format SQL Serve ri snot expecting. Which User language did you configured for the accessing user and which date are you trying to insert ?
HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

Some examples of the values I tried entering yesterday, and none of them worked. (I got the same error message for each value) -

1-1-2007

01-01-2007

01/01/2007

01,01,2007

01:01:2007

Jan 01, 2007 (this is the format I have specified for short date in the Win regional settings - MMM dd, yyyy)

I even tried entering time alongside with all those above figures in the format -

11:35 PM

11:35 AM

23:35

The only time value(s) I didn't try was/were - hh:mm:ss tt - which is the time format specified in my Win regional settings, neither standalone, nor with the date values.

I also tried entering a time value standalone, without the date. Still no go.

Now I'm in a real fix because of the inability to enter date/time values.

Could you please help me out?

|||

Alright, I have done a complete reinstall of Win XP, and have also reinstalled SQL Server Express. Prior to this, I had SQLExpress SP1, but now I have directly installed SP2.

I am at my wits' end because of this. I ask again, is this a bug in SQL Server Express? If so how do I report it to MS and get my issue resolved? If not, even then how can I solve this problem?

Thursday, March 22, 2012

Data driven Subscription

is there any way to read the values for userid and password from a database dynamically while creating a data driven subscription?

i have a huge list of reports written out to a shared folder once daily.
These are then moved to appropriate fodlers with a file moving program.
Instead of typing the userid and password manually on the subscriptions creation page, can we read it from a database. I am able to read all other parameters but these two.

any help is appreciated.

thanx in advance.

hi,

maybe this http://www.sqlservercentral.com/columnists/jselburg/datadrivensubscriptions.asp could be a proper workaround?

cheers,
markus

Thursday, March 8, 2012

Data between 2 dates issue

I am very new to using SQL, so please be patient. I am using MS Access for
the front end and SQL for the backend.
I'm creating a view (using SQL Server Enterprise Manager) that I will link
to Access, but the view) keeps timing out. If I run the same view using
WinSQL, it works.
I am trying to lookup data between 2 dates in my main table. The parameter
dates are kept in a separate table having no fields I can join with my main
table. If I manually type the dates in, it works.
Here's my statement:
SELECT Office, SUM(dbo.oss_tran.CHARGE) AS Charge,
SUM(dbo.oss_tran.TAXCHARGE) AS TaxCharge,
SUM(dbo.oss_tran.CHARGE + dbo.oss_tran.TAXCHARGE) AS
Total
FROM dbo.vw_vhb_combine_accounts_distinct INNER JOIN
dbo.oss_tran ON
dbo.vw_vhb_combine_accounts_distinct.sub_acctno = dbo.oss_tran.ACCTNO INNER
JOIN
dbo.oss_user ON dbo.oss_tran.ACCTNO = dbo.oss_user.ACCTNO AND dbo.oss_tran.USERNAME = dbo.oss_user.ID INNER JOIN
dbo.oss_proj ON dbo.oss_tran.PROJECTNO = dbo.oss_proj.ACC AND dbo.oss_tran.ACCTNO = dbo.oss_proj.ACCTNO
WHERE (dbo.oss_tran.[DATE] BETWEEN
(SELECT BeginDate
FROM dbo.oss_vhb_Data_Input) AND
(SELECT EndDate
FROM dbo.oss_vhb_Data_Input)) AND
(dbo.oss_tran.read_period IS NULL)
GROUP BY RIGHT office
I hope that's not too confusing. I really need help with this.
Thanks,
RachelHi Rachel
"RFrechette" wrote:
> I am very new to using SQL, so please be patient. I am using MS Access for
> the front end and SQL for the backend.
> I'm creating a view (using SQL Server Enterprise Manager) that I will link
> to Access, but the view) keeps timing out. If I run the same view using
> WinSQL, it works.
> I am trying to lookup data between 2 dates in my main table. The parameter
> dates are kept in a separate table having no fields I can join with my main
> table. If I manually type the dates in, it works.
I assume you mean there is a single row in the table?
> Here's my statement:
> SELECT Office, SUM(dbo.oss_tran.CHARGE) AS Charge,
> SUM(dbo.oss_tran.TAXCHARGE) AS TaxCharge,
> SUM(dbo.oss_tran.CHARGE + dbo.oss_tran.TAXCHARGE) AS
> Total
> FROM dbo.vw_vhb_combine_accounts_distinct INNER JOIN
> dbo.oss_tran ON
> dbo.vw_vhb_combine_accounts_distinct.sub_acctno = dbo.oss_tran.ACCTNO INNER
> JOIN
> dbo.oss_user ON dbo.oss_tran.ACCTNO => dbo.oss_user.ACCTNO AND dbo.oss_tran.USERNAME = dbo.oss_user.ID INNER JOIN
> dbo.oss_proj ON dbo.oss_tran.PROJECTNO => dbo.oss_proj.ACC AND dbo.oss_tran.ACCTNO = dbo.oss_proj.ACCTNO
> WHERE (dbo.oss_tran.[DATE] BETWEEN
> (SELECT BeginDate
> FROM dbo.oss_vhb_Data_Input) AND
> (SELECT EndDate
> FROM dbo.oss_vhb_Data_Input)) AND
> (dbo.oss_tran.read_period IS NULL)
> GROUP BY RIGHT office
>
Try (untested):
SELECT w.Office,
SUM(t.CHARGE) AS Charge,
SUM(t.TAXCHARGE) AS TaxCharge,
SUM(t.CHARGE + t.TAXCHARGE) AS Total
FROM dbo.vw_vhb_combine_accounts_distinct w
JOIN dbo.oss_tran t ON w.sub_acctno = t.ACCTNO AND r.read_period
IS NULL
JOIN dbo.oss_user u ON t.ACCTNO = u.ACCTNO AND t.USERNAME = u.ID
JOIN dbo.oss_proj p ON t.PROJECTNO = p.ACC AND t.ACCTNO = p.ACCTNO
JOIN dbo.oss_vhb_Data_Input d ON r.[DATE] >= d.BeginDate AND
r.[DATE] <= EndDate
GROUP BY w.office
> I hope that's not too confusing. I really need help with this.
> Thanks,
> Rachel
>
I am not sure why you are not passing your date criteria as parameters to a
stored procedure. The user an proj tables may not be adding anything to this
query.
John|||Hi John,
Thank you so much for helping me.
I took out the "oss_user" table because I think you are right that it is not
being used. Also, I took out the "AND r.read_period IS NULL" because I
realized that should not be there as well. I really need only the 2 last
digits of the field "general1" from table r. I tried using
"r.Right(general1,2) AS Office but it doesn't seem to like that.
This is what I ended up with:
SELECT r.general1 AS Office, SUM(r.CHARGE) AS Charge, SUM(r.TAXCHARGE)
AS TaxCharge, SUM(r.CHARGE + r.TAXCHARGE) AS Total
FROM dbo.vw_vhb_combine_accounts_distinct w INNER JOIN
dbo.oss_tran r ON w.sub_acctno = r.ACCTNO INNER JOIN
dbo.oss_proj p ON r.PROJECTNO = p.ACC AND r.ACCTNO =p.ACCTNO INNER JOIN
dbo.oss_vhb_Data_Input d ON r.[DATE] >= d.BeginDate
AND r.[DATE] <= d.EndDate
GROUP BY r.general1
It still times out on me. And of course, if it did work, it wouldn't be
grouped correctly because I'm using the whole "general1" field instead of the
last 2 digits.
I noticed in the diagram section, that the d table is joined to the w table.
Shouldn't the d table be joined to the r table because the r table hold the
Date field and the d table holds the BeginDate and EndDate fields?
I'm sorry I'm not more knowledgeble on this but I've had no classes and only
have 1 book and I kind of got thrown into this.
Do you have any more suggestions?
Thank you, Rachel
"John Bell" wrote:
> Hi Rachel
> "RFrechette" wrote:
> > I am very new to using SQL, so please be patient. I am using MS Access for
> > the front end and SQL for the backend.
> >
> > I'm creating a view (using SQL Server Enterprise Manager) that I will link
> > to Access, but the view) keeps timing out. If I run the same view using
> > WinSQL, it works.
> >
> > I am trying to lookup data between 2 dates in my main table. The parameter
> > dates are kept in a separate table having no fields I can join with my main
> > table. If I manually type the dates in, it works.
> I assume you mean there is a single row in the table?
> >
> > Here's my statement:
> >
> > SELECT Office, SUM(dbo.oss_tran.CHARGE) AS Charge,
> > SUM(dbo.oss_tran.TAXCHARGE) AS TaxCharge,
> > SUM(dbo.oss_tran.CHARGE + dbo.oss_tran.TAXCHARGE) AS
> > Total
> > FROM dbo.vw_vhb_combine_accounts_distinct INNER JOIN
> > dbo.oss_tran ON
> > dbo.vw_vhb_combine_accounts_distinct.sub_acctno = dbo.oss_tran.ACCTNO INNER
> > JOIN
> > dbo.oss_user ON dbo.oss_tran.ACCTNO => > dbo.oss_user.ACCTNO AND dbo.oss_tran.USERNAME = dbo.oss_user.ID INNER JOIN
> > dbo.oss_proj ON dbo.oss_tran.PROJECTNO => > dbo.oss_proj.ACC AND dbo.oss_tran.ACCTNO = dbo.oss_proj.ACCTNO
> > WHERE (dbo.oss_tran.[DATE] BETWEEN
> > (SELECT BeginDate
> > FROM dbo.oss_vhb_Data_Input) AND
> > (SELECT EndDate
> > FROM dbo.oss_vhb_Data_Input)) AND
> > (dbo.oss_tran.read_period IS NULL)
> > GROUP BY RIGHT office
> >
> Try (untested):
> SELECT w.Office,
> SUM(t.CHARGE) AS Charge,
> SUM(t.TAXCHARGE) AS TaxCharge,
> SUM(t.CHARGE + t.TAXCHARGE) AS Total
> FROM dbo.vw_vhb_combine_accounts_distinct w
> JOIN dbo.oss_tran t ON w.sub_acctno = t.ACCTNO AND r.read_period
> IS NULL
> JOIN dbo.oss_user u ON t.ACCTNO = u.ACCTNO AND t.USERNAME = u.ID
> JOIN dbo.oss_proj p ON t.PROJECTNO = p.ACC AND t.ACCTNO = p.ACCTNO
> JOIN dbo.oss_vhb_Data_Input d ON r.[DATE] >= d.BeginDate AND
> r.[DATE] <= EndDate
> GROUP BY w.office
> > I hope that's not too confusing. I really need help with this.
> >
> > Thanks,
> > Rachel
> >
> I am not sure why you are not passing your date criteria as parameters to a
> stored procedure. The user an proj tables may not be adding anything to this
> query.
> John|||Hi Rachel
"RFrechette" wrote:
> Hi John,
> Thank you so much for helping me.
> I took out the "oss_user" table because I think you are right that it is not
> being used. Also, I took out the "AND r.read_period IS NULL" because I
> realized that should not be there as well. I really need only the 2 last
> digits of the field "general1" from table r. I tried using
> "r.Right(general1,2) AS Office but it doesn't seem to like that.
> This is what I ended up with:
> SELECT r.general1 AS Office, SUM(r.CHARGE) AS Charge, SUM(r.TAXCHARGE)
> AS TaxCharge, SUM(r.CHARGE + r.TAXCHARGE) AS Total
> FROM dbo.vw_vhb_combine_accounts_distinct w INNER JOIN
> dbo.oss_tran r ON w.sub_acctno = r.ACCTNO INNER JOIN
> dbo.oss_proj p ON r.PROJECTNO = p.ACC AND r.ACCTNO => p.ACCTNO INNER JOIN
> dbo.oss_vhb_Data_Input d ON r.[DATE] >= d.BeginDate
> AND r.[DATE] <= d.EndDate
> GROUP BY r.general1
> It still times out on me. And of course, if it did work, it wouldn't be
> grouped correctly because I'm using the whole "general1" field instead of the
> last 2 digits.
> I noticed in the diagram section, that the d table is joined to the w table.
> Shouldn't the d table be joined to the r table because the r table hold the
> Date field and the d table holds the BeginDate and EndDate fields?
> I'm sorry I'm not more knowledgeble on this but I've had no classes and only
> have 1 book and I kind of got thrown into this.
> Do you have any more suggestions?
> Thank you, Rachel
>
> "John Bell" wrote:
> > Hi Rachel
> >
> > "RFrechette" wrote:
> >
> > > I am very new to using SQL, so please be patient. I am using MS Access for
> > > the front end and SQL for the backend.
> > >
> > > I'm creating a view (using SQL Server Enterprise Manager) that I will link
> > > to Access, but the view) keeps timing out. If I run the same view using
> > > WinSQL, it works.
> > >
> > > I am trying to lookup data between 2 dates in my main table. The parameter
> > > dates are kept in a separate table having no fields I can join with my main
> > > table. If I manually type the dates in, it works.
> > I assume you mean there is a single row in the table?
> >
> > >
> > > Here's my statement:
> > >
> > > SELECT Office, SUM(dbo.oss_tran.CHARGE) AS Charge,
> > > SUM(dbo.oss_tran.TAXCHARGE) AS TaxCharge,
> > > SUM(dbo.oss_tran.CHARGE + dbo.oss_tran.TAXCHARGE) AS
> > > Total
> > > FROM dbo.vw_vhb_combine_accounts_distinct INNER JOIN
> > > dbo.oss_tran ON
> > > dbo.vw_vhb_combine_accounts_distinct.sub_acctno = dbo.oss_tran.ACCTNO INNER
> > > JOIN
> > > dbo.oss_user ON dbo.oss_tran.ACCTNO => > > dbo.oss_user.ACCTNO AND dbo.oss_tran.USERNAME = dbo.oss_user.ID INNER JOIN
> > > dbo.oss_proj ON dbo.oss_tran.PROJECTNO => > > dbo.oss_proj.ACC AND dbo.oss_tran.ACCTNO = dbo.oss_proj.ACCTNO
> > > WHERE (dbo.oss_tran.[DATE] BETWEEN
> > > (SELECT BeginDate
> > > FROM dbo.oss_vhb_Data_Input) AND
> > > (SELECT EndDate
> > > FROM dbo.oss_vhb_Data_Input)) AND
> > > (dbo.oss_tran.read_period IS NULL)
> > > GROUP BY RIGHT office
> > >
> > Try (untested):
> >
> > SELECT w.Office,
> > SUM(t.CHARGE) AS Charge,
> > SUM(t.TAXCHARGE) AS TaxCharge,
> > SUM(t.CHARGE + t.TAXCHARGE) AS Total
> > FROM dbo.vw_vhb_combine_accounts_distinct w
> > JOIN dbo.oss_tran t ON w.sub_acctno = t.ACCTNO AND r.read_period
> > IS NULL
> > JOIN dbo.oss_user u ON t.ACCTNO = u.ACCTNO AND t.USERNAME = u.ID
> > JOIN dbo.oss_proj p ON t.PROJECTNO = p.ACC AND t.ACCTNO = p.ACCTNO
> > JOIN dbo.oss_vhb_Data_Input d ON r.[DATE] >= d.BeginDate AND
> > r.[DATE] <= EndDate
> > GROUP BY w.office
> >
> > > I hope that's not too confusing. I really need help with this.
> > >
> > > Thanks,
> > > Rachel
> > >
> >
> > I am not sure why you are not passing your date criteria as parameters to a
> > stored procedure. The user an proj tables may not be adding anything to this
> > query.
> >
> > John
The join condidition is
JOIN dbo.oss_vhb_Data_Input d ON r.[DATE] >= d.BeginDate AND r.[DATE] <=EndDate
Therefore I am not sure what your diagram is showing!
Use RIGHT(r.general1,2) in the SELECT part and the GROUP BY clause of the
statement
SELECT RIGHT(r.general1,2) AS Office,
SUM(t.CHARGE) AS Charge,
SUM(t.TAXCHARGE) AS TaxCharge,
SUM(t.CHARGE + t.TAXCHARGE) AS Total
FROM dbo.vw_vhb_combine_accounts_distinct w
JOIN dbo.oss_tran t ON w.sub_acctno = t.ACCTNO
JOIN dbo.oss_proj p ON t.PROJECTNO = p.ACC AND t.ACCTNO = p.ACCTNO
JOIN dbo.oss_vhb_Data_Input d ON r.[DATE] >= d.BeginDate AND
r.[DATE] <= EndDate
GROUP BY RIGHT(r.general1,2)
I would assume that vw_vhb_combine_accounts_distinct is a view, so you may
want to look to see if that is contributing anything that is not necessary
(if office is not in this view why do you need it?)
John|||Hi John,
I want to thank you for helping me.
The query is working perfectly when I use it in WinSQL, but it's still
timing out when I use it in SQL Server Enterprise Manager. So there must not
be anything wrong with the query itself. Maybe there is a way to extend the
time out in Enterprise Manager?
I'm about ready to give up. I guess I just need to find some SQL classes.
(Or classes in using SQL Server Enterprise Manager, if any exist.
I really do appreciate all your help though.
Rachel
"John Bell" wrote:
> Hi Rachel
> "RFrechette" wrote:
> > Hi John,
> > Thank you so much for helping me.
> >
> > I took out the "oss_user" table because I think you are right that it is not
> > being used. Also, I took out the "AND r.read_period IS NULL" because I
> > realized that should not be there as well. I really need only the 2 last
> > digits of the field "general1" from table r. I tried using
> > "r.Right(general1,2) AS Office but it doesn't seem to like that.
> >
> > This is what I ended up with:
> >
> > SELECT r.general1 AS Office, SUM(r.CHARGE) AS Charge, SUM(r.TAXCHARGE)
> > AS TaxCharge, SUM(r.CHARGE + r.TAXCHARGE) AS Total
> > FROM dbo.vw_vhb_combine_accounts_distinct w INNER JOIN
> > dbo.oss_tran r ON w.sub_acctno = r.ACCTNO INNER JOIN
> > dbo.oss_proj p ON r.PROJECTNO = p.ACC AND r.ACCTNO => > p.ACCTNO INNER JOIN
> > dbo.oss_vhb_Data_Input d ON r.[DATE] >= d.BeginDate
> > AND r.[DATE] <= d.EndDate
> > GROUP BY r.general1
> >
> > It still times out on me. And of course, if it did work, it wouldn't be
> > grouped correctly because I'm using the whole "general1" field instead of the
> > last 2 digits.
> >
> > I noticed in the diagram section, that the d table is joined to the w table.
> > Shouldn't the d table be joined to the r table because the r table hold the
> > Date field and the d table holds the BeginDate and EndDate fields?
> >
> > I'm sorry I'm not more knowledgeble on this but I've had no classes and only
> > have 1 book and I kind of got thrown into this.
> >
> > Do you have any more suggestions?
> >
> > Thank you, Rachel
> >
> >
> > "John Bell" wrote:
> >
> > > Hi Rachel
> > >
> > > "RFrechette" wrote:
> > >
> > > > I am very new to using SQL, so please be patient. I am using MS Access for
> > > > the front end and SQL for the backend.
> > > >
> > > > I'm creating a view (using SQL Server Enterprise Manager) that I will link
> > > > to Access, but the view) keeps timing out. If I run the same view using
> > > > WinSQL, it works.
> > > >
> > > > I am trying to lookup data between 2 dates in my main table. The parameter
> > > > dates are kept in a separate table having no fields I can join with my main
> > > > table. If I manually type the dates in, it works.
> > > I assume you mean there is a single row in the table?
> > >
> > > >
> > > > Here's my statement:
> > > >
> > > > SELECT Office, SUM(dbo.oss_tran.CHARGE) AS Charge,
> > > > SUM(dbo.oss_tran.TAXCHARGE) AS TaxCharge,
> > > > SUM(dbo.oss_tran.CHARGE + dbo.oss_tran.TAXCHARGE) AS
> > > > Total
> > > > FROM dbo.vw_vhb_combine_accounts_distinct INNER JOIN
> > > > dbo.oss_tran ON
> > > > dbo.vw_vhb_combine_accounts_distinct.sub_acctno = dbo.oss_tran.ACCTNO INNER
> > > > JOIN
> > > > dbo.oss_user ON dbo.oss_tran.ACCTNO => > > > dbo.oss_user.ACCTNO AND dbo.oss_tran.USERNAME = dbo.oss_user.ID INNER JOIN
> > > > dbo.oss_proj ON dbo.oss_tran.PROJECTNO => > > > dbo.oss_proj.ACC AND dbo.oss_tran.ACCTNO = dbo.oss_proj.ACCTNO
> > > > WHERE (dbo.oss_tran.[DATE] BETWEEN
> > > > (SELECT BeginDate
> > > > FROM dbo.oss_vhb_Data_Input) AND
> > > > (SELECT EndDate
> > > > FROM dbo.oss_vhb_Data_Input)) AND
> > > > (dbo.oss_tran.read_period IS NULL)
> > > > GROUP BY RIGHT office
> > > >
> > > Try (untested):
> > >
> > > SELECT w.Office,
> > > SUM(t.CHARGE) AS Charge,
> > > SUM(t.TAXCHARGE) AS TaxCharge,
> > > SUM(t.CHARGE + t.TAXCHARGE) AS Total
> > > FROM dbo.vw_vhb_combine_accounts_distinct w
> > > JOIN dbo.oss_tran t ON w.sub_acctno = t.ACCTNO AND r.read_period
> > > IS NULL
> > > JOIN dbo.oss_user u ON t.ACCTNO = u.ACCTNO AND t.USERNAME = u.ID
> > > JOIN dbo.oss_proj p ON t.PROJECTNO = p.ACC AND t.ACCTNO = p.ACCTNO
> > > JOIN dbo.oss_vhb_Data_Input d ON r.[DATE] >= d.BeginDate AND
> > > r.[DATE] <= EndDate
> > > GROUP BY w.office
> > >
> > > > I hope that's not too confusing. I really need help with this.
> > > >
> > > > Thanks,
> > > > Rachel
> > > >
> > >
> > > I am not sure why you are not passing your date criteria as parameters to a
> > > stored procedure. The user an proj tables may not be adding anything to this
> > > query.
> > >
> > > John
> The join condidition is
> JOIN dbo.oss_vhb_Data_Input d ON r.[DATE] >= d.BeginDate AND r.[DATE] <=> EndDate
> Therefore I am not sure what your diagram is showing!
> Use RIGHT(r.general1,2) in the SELECT part and the GROUP BY clause of the
> statement
> SELECT RIGHT(r.general1,2) AS Office,
> SUM(t.CHARGE) AS Charge,
> SUM(t.TAXCHARGE) AS TaxCharge,
> SUM(t.CHARGE + t.TAXCHARGE) AS Total
> FROM dbo.vw_vhb_combine_accounts_distinct w
> JOIN dbo.oss_tran t ON w.sub_acctno = t.ACCTNO
> JOIN dbo.oss_proj p ON t.PROJECTNO = p.ACC AND t.ACCTNO = p.ACCTNO
> JOIN dbo.oss_vhb_Data_Input d ON r.[DATE] >= d.BeginDate AND
> r.[DATE] <= EndDate
> GROUP BY RIGHT(r.general1,2)
> I would assume that vw_vhb_combine_accounts_distinct is a view, so you may
> want to look to see if that is contributing anything that is not necessary
> (if office is not in this view why do you need it?)
> John
>|||Hi Rachel
You don't say what time it takes in WinSQL (which I am note sure it is!)
Try using Query Analyser!!
In Enterprise Manager if you click on the server, then choose Tools/Options
there is a query timeout value on the advanced tab, this should be 0 for no
timeout. In Enterprise Manager how are you running this?
John
"RFrechette" wrote:
> Hi John,
> I want to thank you for helping me.
> The query is working perfectly when I use it in WinSQL, but it's still
> timing out when I use it in SQL Server Enterprise Manager. So there must not
> be anything wrong with the query itself. Maybe there is a way to extend the
> time out in Enterprise Manager?
> I'm about ready to give up. I guess I just need to find some SQL classes.
> (Or classes in using SQL Server Enterprise Manager, if any exist.
> I really do appreciate all your help though.
> Rachel
> "John Bell" wrote:
> > Hi Rachel
> >
> > "RFrechette" wrote:
> >
> > > Hi John,
> > > Thank you so much for helping me.
> > >
> > > I took out the "oss_user" table because I think you are right that it is not
> > > being used. Also, I took out the "AND r.read_period IS NULL" because I
> > > realized that should not be there as well. I really need only the 2 last
> > > digits of the field "general1" from table r. I tried using
> > > "r.Right(general1,2) AS Office but it doesn't seem to like that.
> > >
> > > This is what I ended up with:
> > >
> > > SELECT r.general1 AS Office, SUM(r.CHARGE) AS Charge, SUM(r.TAXCHARGE)
> > > AS TaxCharge, SUM(r.CHARGE + r.TAXCHARGE) AS Total
> > > FROM dbo.vw_vhb_combine_accounts_distinct w INNER JOIN
> > > dbo.oss_tran r ON w.sub_acctno = r.ACCTNO INNER JOIN
> > > dbo.oss_proj p ON r.PROJECTNO = p.ACC AND r.ACCTNO => > > p.ACCTNO INNER JOIN
> > > dbo.oss_vhb_Data_Input d ON r.[DATE] >= d.BeginDate
> > > AND r.[DATE] <= d.EndDate
> > > GROUP BY r.general1
> > >
> > > It still times out on me. And of course, if it did work, it wouldn't be
> > > grouped correctly because I'm using the whole "general1" field instead of the
> > > last 2 digits.
> > >
> > > I noticed in the diagram section, that the d table is joined to the w table.
> > > Shouldn't the d table be joined to the r table because the r table hold the
> > > Date field and the d table holds the BeginDate and EndDate fields?
> > >
> > > I'm sorry I'm not more knowledgeble on this but I've had no classes and only
> > > have 1 book and I kind of got thrown into this.
> > >
> > > Do you have any more suggestions?
> > >
> > > Thank you, Rachel
> > >
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi Rachel
> > > >
> > > > "RFrechette" wrote:
> > > >
> > > > > I am very new to using SQL, so please be patient. I am using MS Access for
> > > > > the front end and SQL for the backend.
> > > > >
> > > > > I'm creating a view (using SQL Server Enterprise Manager) that I will link
> > > > > to Access, but the view) keeps timing out. If I run the same view using
> > > > > WinSQL, it works.
> > > > >
> > > > > I am trying to lookup data between 2 dates in my main table. The parameter
> > > > > dates are kept in a separate table having no fields I can join with my main
> > > > > table. If I manually type the dates in, it works.
> > > > I assume you mean there is a single row in the table?
> > > >
> > > > >
> > > > > Here's my statement:
> > > > >
> > > > > SELECT Office, SUM(dbo.oss_tran.CHARGE) AS Charge,
> > > > > SUM(dbo.oss_tran.TAXCHARGE) AS TaxCharge,
> > > > > SUM(dbo.oss_tran.CHARGE + dbo.oss_tran.TAXCHARGE) AS
> > > > > Total
> > > > > FROM dbo.vw_vhb_combine_accounts_distinct INNER JOIN
> > > > > dbo.oss_tran ON
> > > > > dbo.vw_vhb_combine_accounts_distinct.sub_acctno = dbo.oss_tran.ACCTNO INNER
> > > > > JOIN
> > > > > dbo.oss_user ON dbo.oss_tran.ACCTNO => > > > > dbo.oss_user.ACCTNO AND dbo.oss_tran.USERNAME = dbo.oss_user.ID INNER JOIN
> > > > > dbo.oss_proj ON dbo.oss_tran.PROJECTNO => > > > > dbo.oss_proj.ACC AND dbo.oss_tran.ACCTNO = dbo.oss_proj.ACCTNO
> > > > > WHERE (dbo.oss_tran.[DATE] BETWEEN
> > > > > (SELECT BeginDate
> > > > > FROM dbo.oss_vhb_Data_Input) AND
> > > > > (SELECT EndDate
> > > > > FROM dbo.oss_vhb_Data_Input)) AND
> > > > > (dbo.oss_tran.read_period IS NULL)
> > > > > GROUP BY RIGHT office
> > > > >
> > > > Try (untested):
> > > >
> > > > SELECT w.Office,
> > > > SUM(t.CHARGE) AS Charge,
> > > > SUM(t.TAXCHARGE) AS TaxCharge,
> > > > SUM(t.CHARGE + t.TAXCHARGE) AS Total
> > > > FROM dbo.vw_vhb_combine_accounts_distinct w
> > > > JOIN dbo.oss_tran t ON w.sub_acctno = t.ACCTNO AND r.read_period
> > > > IS NULL
> > > > JOIN dbo.oss_user u ON t.ACCTNO = u.ACCTNO AND t.USERNAME = u.ID
> > > > JOIN dbo.oss_proj p ON t.PROJECTNO = p.ACC AND t.ACCTNO = p.ACCTNO
> > > > JOIN dbo.oss_vhb_Data_Input d ON r.[DATE] >= d.BeginDate AND
> > > > r.[DATE] <= EndDate
> > > > GROUP BY w.office
> > > >
> > > > > I hope that's not too confusing. I really need help with this.
> > > > >
> > > > > Thanks,
> > > > > Rachel
> > > > >
> > > >
> > > > I am not sure why you are not passing your date criteria as parameters to a
> > > > stored procedure. The user an proj tables may not be adding anything to this
> > > > query.
> > > >
> > > > John
> > The join condidition is
> >
> > JOIN dbo.oss_vhb_Data_Input d ON r.[DATE] >= d.BeginDate AND r.[DATE] <=> > EndDate
> >
> > Therefore I am not sure what your diagram is showing!
> >
> > Use RIGHT(r.general1,2) in the SELECT part and the GROUP BY clause of the
> > statement
> >
> > SELECT RIGHT(r.general1,2) AS Office,
> > SUM(t.CHARGE) AS Charge,
> > SUM(t.TAXCHARGE) AS TaxCharge,
> > SUM(t.CHARGE + t.TAXCHARGE) AS Total
> > FROM dbo.vw_vhb_combine_accounts_distinct w
> > JOIN dbo.oss_tran t ON w.sub_acctno = t.ACCTNO
> > JOIN dbo.oss_proj p ON t.PROJECTNO = p.ACC AND t.ACCTNO = p.ACCTNO
> > JOIN dbo.oss_vhb_Data_Input d ON r.[DATE] >= d.BeginDate AND
> > r.[DATE] <= EndDate
> > GROUP BY RIGHT(r.general1,2)
> >
> > I would assume that vw_vhb_combine_accounts_distinct is a view, so you may
> > want to look to see if that is contributing anything that is not necessary
> > (if office is not in this view why do you need it?)
> >
> > John
> >|||Hi John,
I checked the query time out value in Enterprise, it's 0. Here's what I'm
getting using the query you gave me:
WinSQL:
Works in about 3 minutes with correct results.
If I hard code the dates instead of having them look them up, it takes less
than 1 minute with correct results.
SQL Server Enterprise:
After less than 1 minute I get the message: [Microsoft][ODBC SQL Server
Driver] Timeout expired.
If I hard code the dates instead of having them look them up, it takes less
than 1 minute with correct results.
Query Analyser:
Works in about 3 minutes with correct results.
If I hard code the dates instead of having them look them up, it takes less
than 1 minute with correct results.
Does that information help at all?
Thank you.
Rachel
"John Bell" wrote:
> Hi Rachel
> You don't say what time it takes in WinSQL (which I am note sure it is!)
> Try using Query Analyser!!
> In Enterprise Manager if you click on the server, then choose Tools/Options
> there is a query timeout value on the advanced tab, this should be 0 for no
> timeout. In Enterprise Manager how are you running this?
> John
> "RFrechette" wrote:
> > Hi John,
> >
> > I want to thank you for helping me.
> >
> > The query is working perfectly when I use it in WinSQL, but it's still
> > timing out when I use it in SQL Server Enterprise Manager. So there must not
> > be anything wrong with the query itself. Maybe there is a way to extend the
> > time out in Enterprise Manager?
> >
> > I'm about ready to give up. I guess I just need to find some SQL classes.
> > (Or classes in using SQL Server Enterprise Manager, if any exist.
> >
> > I really do appreciate all your help though.
> >
> > Rachel
> >
> > "John Bell" wrote:
> >
> > > Hi Rachel
> > >
> > > "RFrechette" wrote:
> > >
> > > > Hi John,
> > > > Thank you so much for helping me.
> > > >
> > > > I took out the "oss_user" table because I think you are right that it is not
> > > > being used. Also, I took out the "AND r.read_period IS NULL" because I
> > > > realized that should not be there as well. I really need only the 2 last
> > > > digits of the field "general1" from table r. I tried using
> > > > "r.Right(general1,2) AS Office but it doesn't seem to like that.
> > > >
> > > > This is what I ended up with:
> > > >
> > > > SELECT r.general1 AS Office, SUM(r.CHARGE) AS Charge, SUM(r.TAXCHARGE)
> > > > AS TaxCharge, SUM(r.CHARGE + r.TAXCHARGE) AS Total
> > > > FROM dbo.vw_vhb_combine_accounts_distinct w INNER JOIN
> > > > dbo.oss_tran r ON w.sub_acctno = r.ACCTNO INNER JOIN
> > > > dbo.oss_proj p ON r.PROJECTNO = p.ACC AND r.ACCTNO => > > > p.ACCTNO INNER JOIN
> > > > dbo.oss_vhb_Data_Input d ON r.[DATE] >= d.BeginDate
> > > > AND r.[DATE] <= d.EndDate
> > > > GROUP BY r.general1
> > > >
> > > > It still times out on me. And of course, if it did work, it wouldn't be
> > > > grouped correctly because I'm using the whole "general1" field instead of the
> > > > last 2 digits.
> > > >
> > > > I noticed in the diagram section, that the d table is joined to the w table.
> > > > Shouldn't the d table be joined to the r table because the r table hold the
> > > > Date field and the d table holds the BeginDate and EndDate fields?
> > > >
> > > > I'm sorry I'm not more knowledgeble on this but I've had no classes and only
> > > > have 1 book and I kind of got thrown into this.
> > > >
> > > > Do you have any more suggestions?
> > > >
> > > > Thank you, Rachel
> > > >
> > > >
> > > > "John Bell" wrote:
> > > >
> > > > > Hi Rachel
> > > > >
> > > > > "RFrechette" wrote:
> > > > >
> > > > > > I am very new to using SQL, so please be patient. I am using MS Access for
> > > > > > the front end and SQL for the backend.
> > > > > >
> > > > > > I'm creating a view (using SQL Server Enterprise Manager) that I will link
> > > > > > to Access, but the view) keeps timing out. If I run the same view using
> > > > > > WinSQL, it works.
> > > > > >
> > > > > > I am trying to lookup data between 2 dates in my main table. The parameter
> > > > > > dates are kept in a separate table having no fields I can join with my main
> > > > > > table. If I manually type the dates in, it works.
> > > > > I assume you mean there is a single row in the table?
> > > > >
> > > > > >
> > > > > > Here's my statement:
> > > > > >
> > > > > > SELECT Office, SUM(dbo.oss_tran.CHARGE) AS Charge,
> > > > > > SUM(dbo.oss_tran.TAXCHARGE) AS TaxCharge,
> > > > > > SUM(dbo.oss_tran.CHARGE + dbo.oss_tran.TAXCHARGE) AS
> > > > > > Total
> > > > > > FROM dbo.vw_vhb_combine_accounts_distinct INNER JOIN
> > > > > > dbo.oss_tran ON
> > > > > > dbo.vw_vhb_combine_accounts_distinct.sub_acctno = dbo.oss_tran.ACCTNO INNER
> > > > > > JOIN
> > > > > > dbo.oss_user ON dbo.oss_tran.ACCTNO => > > > > > dbo.oss_user.ACCTNO AND dbo.oss_tran.USERNAME = dbo.oss_user.ID INNER JOIN
> > > > > > dbo.oss_proj ON dbo.oss_tran.PROJECTNO => > > > > > dbo.oss_proj.ACC AND dbo.oss_tran.ACCTNO = dbo.oss_proj.ACCTNO
> > > > > > WHERE (dbo.oss_tran.[DATE] BETWEEN
> > > > > > (SELECT BeginDate
> > > > > > FROM dbo.oss_vhb_Data_Input) AND
> > > > > > (SELECT EndDate
> > > > > > FROM dbo.oss_vhb_Data_Input)) AND
> > > > > > (dbo.oss_tran.read_period IS NULL)
> > > > > > GROUP BY RIGHT office
> > > > > >
> > > > > Try (untested):
> > > > >
> > > > > SELECT w.Office,
> > > > > SUM(t.CHARGE) AS Charge,
> > > > > SUM(t.TAXCHARGE) AS TaxCharge,
> > > > > SUM(t.CHARGE + t.TAXCHARGE) AS Total
> > > > > FROM dbo.vw_vhb_combine_accounts_distinct w
> > > > > JOIN dbo.oss_tran t ON w.sub_acctno = t.ACCTNO AND r.read_period
> > > > > IS NULL
> > > > > JOIN dbo.oss_user u ON t.ACCTNO = u.ACCTNO AND t.USERNAME = u.ID
> > > > > JOIN dbo.oss_proj p ON t.PROJECTNO = p.ACC AND t.ACCTNO = p.ACCTNO
> > > > > JOIN dbo.oss_vhb_Data_Input d ON r.[DATE] >= d.BeginDate AND
> > > > > r.[DATE] <= EndDate
> > > > > GROUP BY w.office
> > > > >
> > > > > > I hope that's not too confusing. I really need help with this.
> > > > > >
> > > > > > Thanks,
> > > > > > Rachel
> > > > > >
> > > > >
> > > > > I am not sure why you are not passing your date criteria as parameters to a
> > > > > stored procedure. The user an proj tables may not be adding anything to this
> > > > > query.
> > > > >
> > > > > John
> > > The join condidition is
> > >
> > > JOIN dbo.oss_vhb_Data_Input d ON r.[DATE] >= d.BeginDate AND r.[DATE] <=> > > EndDate
> > >
> > > Therefore I am not sure what your diagram is showing!
> > >
> > > Use RIGHT(r.general1,2) in the SELECT part and the GROUP BY clause of the
> > > statement
> > >
> > > SELECT RIGHT(r.general1,2) AS Office,
> > > SUM(t.CHARGE) AS Charge,
> > > SUM(t.TAXCHARGE) AS TaxCharge,
> > > SUM(t.CHARGE + t.TAXCHARGE) AS Total
> > > FROM dbo.vw_vhb_combine_accounts_distinct w
> > > JOIN dbo.oss_tran t ON w.sub_acctno = t.ACCTNO
> > > JOIN dbo.oss_proj p ON t.PROJECTNO = p.ACC AND t.ACCTNO = p.ACCTNO
> > > JOIN dbo.oss_vhb_Data_Input d ON r.[DATE] >= d.BeginDate AND
> > > r.[DATE] <= EndDate
> > > GROUP BY RIGHT(r.general1,2)
> > >
> > > I would assume that vw_vhb_combine_accounts_distinct is a view, so you may
> > > want to look to see if that is contributing anything that is not necessary
> > > (if office is not in this view why do you need it?)
> > >
> > > John
> > >|||Hi Rachel
"RFrechette" wrote:
> Hi John,
> I checked the query time out value in Enterprise, it's 0. Here's what I'm
> getting using the query you gave me:
> WinSQL:
> Works in about 3 minutes with correct results.
> If I hard code the dates instead of having them look them up, it takes less
> than 1 minute with correct results.
> SQL Server Enterprise:
> After less than 1 minute I get the message: [Microsoft][ODBC SQL Server
> Driver] Timeout expired.
> If I hard code the dates instead of having them look them up, it takes less
> than 1 minute with correct results.
> Query Analyser:
> Works in about 3 minutes with correct results.
> If I hard code the dates instead of having them look them up, it takes less
> than 1 minute with correct results.
> Does that information help at all?
> Thank you.
> Rachel
> "John Bell" wrote:
> > Hi Rachel
> >
> > You don't say what time it takes in WinSQL (which I am note sure it is!)
> > Try using Query Analyser!!
> >
> > In Enterprise Manager if you click on the server, then choose Tools/Options
> > there is a query timeout value on the advanced tab, this should be 0 for no
> > timeout. In Enterprise Manager how are you running this?
> >
> > John
> >
> > "RFrechette" wrote:
> >
> > > Hi John,
> > >
> > > I want to thank you for helping me.
> > >
> > > The query is working perfectly when I use it in WinSQL, but it's still
> > > timing out when I use it in SQL Server Enterprise Manager. So there must not
> > > be anything wrong with the query itself. Maybe there is a way to extend the
> > > time out in Enterprise Manager?
> > >
> > > I'm about ready to give up. I guess I just need to find some SQL classes.
> > > (Or classes in using SQL Server Enterprise Manager, if any exist.
> > >
> > > I really do appreciate all your help though.
> > >
> > > Rachel
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi Rachel
> > > >
> > > > "RFrechette" wrote:
> > > >
> > > > > Hi John,
> > > > > Thank you so much for helping me.
> > > > >
> > > > > I took out the "oss_user" table because I think you are right that it is not
> > > > > being used. Also, I took out the "AND r.read_period IS NULL" because I
> > > > > realized that should not be there as well. I really need only the 2 last
> > > > > digits of the field "general1" from table r. I tried using
> > > > > "r.Right(general1,2) AS Office but it doesn't seem to like that.
> > > > >
> > > > > This is what I ended up with:
> > > > >
> > > > > SELECT r.general1 AS Office, SUM(r.CHARGE) AS Charge, SUM(r.TAXCHARGE)
> > > > > AS TaxCharge, SUM(r.CHARGE + r.TAXCHARGE) AS Total
> > > > > FROM dbo.vw_vhb_combine_accounts_distinct w INNER JOIN
> > > > > dbo.oss_tran r ON w.sub_acctno = r.ACCTNO INNER JOIN
> > > > > dbo.oss_proj p ON r.PROJECTNO = p.ACC AND r.ACCTNO => > > > > p.ACCTNO INNER JOIN
> > > > > dbo.oss_vhb_Data_Input d ON r.[DATE] >= d.BeginDate
> > > > > AND r.[DATE] <= d.EndDate
> > > > > GROUP BY r.general1
> > > > >
> > > > > It still times out on me. And of course, if it did work, it wouldn't be
> > > > > grouped correctly because I'm using the whole "general1" field instead of the
> > > > > last 2 digits.
> > > > >
> > > > > I noticed in the diagram section, that the d table is joined to the w table.
> > > > > Shouldn't the d table be joined to the r table because the r table hold the
> > > > > Date field and the d table holds the BeginDate and EndDate fields?
> > > > >
> > > > > I'm sorry I'm not more knowledgeble on this but I've had no classes and only
> > > > > have 1 book and I kind of got thrown into this.
> > > > >
> > > > > Do you have any more suggestions?
> > > > >
> > > > > Thank you, Rachel
> > > > >
> > > > >
> > > > > "John Bell" wrote:
> > > > >
> > > > > > Hi Rachel
> > > > > >
> > > > > > "RFrechette" wrote:
> > > > > >
> > > > > > > I am very new to using SQL, so please be patient. I am using MS Access for
> > > > > > > the front end and SQL for the backend.
> > > > > > >
> > > > > > > I'm creating a view (using SQL Server Enterprise Manager) that I will link
> > > > > > > to Access, but the view) keeps timing out. If I run the same view using
> > > > > > > WinSQL, it works.
> > > > > > >
> > > > > > > I am trying to lookup data between 2 dates in my main table. The parameter
> > > > > > > dates are kept in a separate table having no fields I can join with my main
> > > > > > > table. If I manually type the dates in, it works.
> > > > > > I assume you mean there is a single row in the table?
> > > > > >
> > > > > > >
> > > > > > > Here's my statement:
> > > > > > >
> > > > > > > SELECT Office, SUM(dbo.oss_tran.CHARGE) AS Charge,
> > > > > > > SUM(dbo.oss_tran.TAXCHARGE) AS TaxCharge,
> > > > > > > SUM(dbo.oss_tran.CHARGE + dbo.oss_tran.TAXCHARGE) AS
> > > > > > > Total
> > > > > > > FROM dbo.vw_vhb_combine_accounts_distinct INNER JOIN
> > > > > > > dbo.oss_tran ON
> > > > > > > dbo.vw_vhb_combine_accounts_distinct.sub_acctno = dbo.oss_tran.ACCTNO INNER
> > > > > > > JOIN
> > > > > > > dbo.oss_user ON dbo.oss_tran.ACCTNO => > > > > > > dbo.oss_user.ACCTNO AND dbo.oss_tran.USERNAME = dbo.oss_user.ID INNER JOIN
> > > > > > > dbo.oss_proj ON dbo.oss_tran.PROJECTNO => > > > > > > dbo.oss_proj.ACC AND dbo.oss_tran.ACCTNO = dbo.oss_proj.ACCTNO
> > > > > > > WHERE (dbo.oss_tran.[DATE] BETWEEN
> > > > > > > (SELECT BeginDate
> > > > > > > FROM dbo.oss_vhb_Data_Input) AND
> > > > > > > (SELECT EndDate
> > > > > > > FROM dbo.oss_vhb_Data_Input)) AND
> > > > > > > (dbo.oss_tran.read_period IS NULL)
> > > > > > > GROUP BY RIGHT office
> > > > > > >
> > > > > > Try (untested):
> > > > > >
> > > > > > SELECT w.Office,
> > > > > > SUM(t.CHARGE) AS Charge,
> > > > > > SUM(t.TAXCHARGE) AS TaxCharge,
> > > > > > SUM(t.CHARGE + t.TAXCHARGE) AS Total
> > > > > > FROM dbo.vw_vhb_combine_accounts_distinct w
> > > > > > JOIN dbo.oss_tran t ON w.sub_acctno = t.ACCTNO AND r.read_period
> > > > > > IS NULL
> > > > > > JOIN dbo.oss_user u ON t.ACCTNO = u.ACCTNO AND t.USERNAME = u.ID
> > > > > > JOIN dbo.oss_proj p ON t.PROJECTNO = p.ACC AND t.ACCTNO = p.ACCTNO
> > > > > > JOIN dbo.oss_vhb_Data_Input d ON r.[DATE] >= d.BeginDate AND
> > > > > > r.[DATE] <= EndDate
> > > > > > GROUP BY w.office
> > > > > >
> > > > > > > I hope that's not too confusing. I really need help with this.
> > > > > > >
> > > > > > > Thanks,
> > > > > > > Rachel
> > > > > > >
> > > > > >
> > > > > > I am not sure why you are not passing your date criteria as parameters to a
> > > > > > stored procedure. The user an proj tables may not be adding anything to this
> > > > > > query.
> > > > > >
> > > > > > John
> > > > The join condidition is
> > > >
> > > > JOIN dbo.oss_vhb_Data_Input d ON r.[DATE] >= d.BeginDate AND r.[DATE] <=> > > > EndDate
> > > >
> > > > Therefore I am not sure what your diagram is showing!
> > > >
> > > > Use RIGHT(r.general1,2) in the SELECT part and the GROUP BY clause of the
> > > > statement
> > > >
> > > > SELECT RIGHT(r.general1,2) AS Office,
> > > > SUM(t.CHARGE) AS Charge,
> > > > SUM(t.TAXCHARGE) AS TaxCharge,
> > > > SUM(t.CHARGE + t.TAXCHARGE) AS Total
> > > > FROM dbo.vw_vhb_combine_accounts_distinct w
> > > > JOIN dbo.oss_tran t ON w.sub_acctno = t.ACCTNO
> > > > JOIN dbo.oss_proj p ON t.PROJECTNO = p.ACC AND t.ACCTNO = p.ACCTNO
> > > > JOIN dbo.oss_vhb_Data_Input d ON r.[DATE] >= d.BeginDate AND
> > > > r.[DATE] <= EndDate
> > > > GROUP BY RIGHT(r.general1,2)
> > > >
> > > > I would assume that vw_vhb_combine_accounts_distinct is a view, so you may
> > > > want to look to see if that is contributing anything that is not necessary
> > > > (if office is not in this view why do you need it?)
> > > >
> > > > John
> > > >
I assume that the date table is necessary because of the way your
applications works?
In query analyser look at the query plan (to this on by checking under the
show execution paln on the query menu), you will hopefully be able to see
what is inefficient when you run this query. If sounds like there are missing
indexes, so using the index tuning wizard may give you some clue as to what
is missing.
John|||Hi John,
Yes, the Date table is necessary. I hope I haven't included too much data
for you here...
I looked at the query plan, unfortunately it didn't make much sense to me.
When I go the "Manage Indexes" for oss_tran it shows:
Index: pkey, Clustered: No, Columns: Acctno, Date, OrderNo
Index: pkey2, Clustered: No, Columns: Date
Index: pkey3, Clustered: No, Columns: OrderNo
Index: pkey4, Clustered: No, Columns: Acctno, ProjectNo
I used the Index Tuning Wizard. On the Index Recommendations page it showed:
Clustered: Yes, Index: oss_tran1, Table: dbo.oss_tran, Column: AcctNo
Clustered: No, Index: pkey4, Table: dbo.oss_tran, Column: AcctNo, ProjectNo
Clustered: No, Index: pkey, Table: dbo.oss_tran, Column: AcctNo, Date, OrderNo
Clustered: No, Index: pkey2, Table: dbo.oss_tran, Column: AcctNo, ProjectNo
Clustered: No, Index: pkey3, Table: dbo.oss_tran, Column: OrderNo
(It showed that if I implemented the recommended changes, I should see a 79%
change in performance.) Unfortunately, I can not do this without permission,
which will be hard to get.
Under the Analysis on the Index Recommendations page, it showed for the
Index Usage Report (Recommended Configuration):
Table: dbo.oss_tran, Index: [oss_tran1], % usage: 100, Size: 19011696
Table: dbo.oss_tran, Index: [pkey4], % usage: 0, Size: 1,390,096
Table: dbo.oss_tran, Index: [pkey1], % usage: 0, Size: 1,553,640
Table: dbo.oss_tran, Index: [pkey2], % usage: 0, Size: 695,048
Table: dbo.oss_tran, Index: [pkey3], % usage: 0, Size: 858,592
Do you think that the problem Iâ'm having getting the query to run in SQL
Enterprise would be solved if the recommendations were implemented?
Also, when I look at the properties of the oss_tran table it shows the Full
Text Indexes as inactive and none of the columns are checked off for Full
Text Indexes.
Could this also be the problem?
I truly appreciate your help on this. I feel like an idiot.
Thank you, Rachel
"John Bell" wrote:
> Hi Rachel
> "RFrechette" wrote:
> > Hi John,
> > I checked the query time out value in Enterprise, it's 0. Here's what I'm
> > getting using the query you gave me:
> >
> > WinSQL:
> > Works in about 3 minutes with correct results.
> > If I hard code the dates instead of having them look them up, it takes less
> > than 1 minute with correct results.
> >
> > SQL Server Enterprise:
> > After less than 1 minute I get the message: [Microsoft][ODBC SQL Server
> > Driver] Timeout expired.
> > If I hard code the dates instead of having them look them up, it takes less
> > than 1 minute with correct results.
> >
> > Query Analyser:
> > Works in about 3 minutes with correct results.
> > If I hard code the dates instead of having them look them up, it takes less
> > than 1 minute with correct results.
> >
> > Does that information help at all?
> >
> > Thank you.
> >
> > Rachel
> >
> > "John Bell" wrote:
> >
> > > Hi Rachel
> > >
> > > You don't say what time it takes in WinSQL (which I am note sure it is!)
> > > Try using Query Analyser!!
> > >
> > > In Enterprise Manager if you click on the server, then choose Tools/Options
> > > there is a query timeout value on the advanced tab, this should be 0 for no
> > > timeout. In Enterprise Manager how are you running this?
> > >
> > > John
> > >
> > > "RFrechette" wrote:
> > >
> > > > Hi John,
> > > >
> > > > I want to thank you for helping me.
> > > >
> > > > The query is working perfectly when I use it in WinSQL, but it's still
> > > > timing out when I use it in SQL Server Enterprise Manager. So there must not
> > > > be anything wrong with the query itself. Maybe there is a way to extend the
> > > > time out in Enterprise Manager?
> > > >
> > > > I'm about ready to give up. I guess I just need to find some SQL classes.
> > > > (Or classes in using SQL Server Enterprise Manager, if any exist.
> > > >
> > > > I really do appreciate all your help though.
> > > >
> > > > Rachel
> > > >
> > > > "John Bell" wrote:
> > > >
> > > > > Hi Rachel
> > > > >
> > > > > "RFrechette" wrote:
> > > > >
> > > > > > Hi John,
> > > > > > Thank you so much for helping me.
> > > > > >
> > > > > > I took out the "oss_user" table because I think you are right that it is not
> > > > > > being used. Also, I took out the "AND r.read_period IS NULL" because I
> > > > > > realized that should not be there as well. I really need only the 2 last
> > > > > > digits of the field "general1" from table r. I tried using
> > > > > > "r.Right(general1,2) AS Office but it doesn't seem to like that.
> > > > > >
> > > > > > This is what I ended up with:
> > > > > >
> > > > > > SELECT r.general1 AS Office, SUM(r.CHARGE) AS Charge, SUM(r.TAXCHARGE)
> > > > > > AS TaxCharge, SUM(r.CHARGE + r.TAXCHARGE) AS Total
> > > > > > FROM dbo.vw_vhb_combine_accounts_distinct w INNER JOIN
> > > > > > dbo.oss_tran r ON w.sub_acctno = r.ACCTNO INNER JOIN
> > > > > > dbo.oss_proj p ON r.PROJECTNO = p.ACC AND r.ACCTNO => > > > > > p.ACCTNO INNER JOIN
> > > > > > dbo.oss_vhb_Data_Input d ON r.[DATE] >= d.BeginDate
> > > > > > AND r.[DATE] <= d.EndDate
> > > > > > GROUP BY r.general1
> > > > > >
> > > > > > It still times out on me. And of course, if it did work, it wouldn't be
> > > > > > grouped correctly because I'm using the whole "general1" field instead of the
> > > > > > last 2 digits.
> > > > > >
> > > > > > I noticed in the diagram section, that the d table is joined to the w table.
> > > > > > Shouldn't the d table be joined to the r table because the r table hold the
> > > > > > Date field and the d table holds the BeginDate and EndDate fields?
> > > > > >
> > > > > > I'm sorry I'm not more knowledgeble on this but I've had no classes and only
> > > > > > have 1 book and I kind of got thrown into this.
> > > > > >
> > > > > > Do you have any more suggestions?
> > > > > >
> > > > > > Thank you, Rachel
> > > > > >
> > > > > >
> > > > > > "John Bell" wrote:
> > > > > >
> > > > > > > Hi Rachel
> > > > > > >
> > > > > > > "RFrechette" wrote:
> > > > > > >
> > > > > > > > I am very new to using SQL, so please be patient. I am using MS Access for
> > > > > > > > the front end and SQL for the backend.
> > > > > > > >
> > > > > > > > I'm creating a view (using SQL Server Enterprise Manager) that I will link
> > > > > > > > to Access, but the view) keeps timing out. If I run the same view using
> > > > > > > > WinSQL, it works.
> > > > > > > >
> > > > > > > > I am trying to lookup data between 2 dates in my main table. The parameter
> > > > > > > > dates are kept in a separate table having no fields I can join with my main
> > > > > > > > table. If I manually type the dates in, it works.
> > > > > > > I assume you mean there is a single row in the table?
> > > > > > >
> > > > > > > >
> > > > > > > > Here's my statement:
> > > > > > > >
> > > > > > > > SELECT Office, SUM(dbo.oss_tran.CHARGE) AS Charge,
> > > > > > > > SUM(dbo.oss_tran.TAXCHARGE) AS TaxCharge,
> > > > > > > > SUM(dbo.oss_tran.CHARGE + dbo.oss_tran.TAXCHARGE) AS
> > > > > > > > Total
> > > > > > > > FROM dbo.vw_vhb_combine_accounts_distinct INNER JOIN
> > > > > > > > dbo.oss_tran ON
> > > > > > > > dbo.vw_vhb_combine_accounts_distinct.sub_acctno = dbo.oss_tran.ACCTNO INNER
> > > > > > > > JOIN
> > > > > > > > dbo.oss_user ON dbo.oss_tran.ACCTNO => > > > > > > > dbo.oss_user.ACCTNO AND dbo.oss_tran.USERNAME = dbo.oss_user.ID INNER JOIN
> > > > > > > > dbo.oss_proj ON dbo.oss_tran.PROJECTNO => > > > > > > > dbo.oss_proj.ACC AND dbo.oss_tran.ACCTNO = dbo.oss_proj.ACCTNO
> > > > > > > > WHERE (dbo.oss_tran.[DATE] BETWEEN
> > > > > > > > (SELECT BeginDate
> > > > > > > > FROM dbo.oss_vhb_Data_Input) AND
> > > > > > > > (SELECT EndDate
> > > > > > > > FROM dbo.oss_vhb_Data_Input)) AND
> > > > > > > > (dbo.oss_tran.read_period IS NULL)
> > > > > > > > GROUP BY RIGHT office
> > > > > > > >
> > > > > > > Try (untested):
> > > > > > >
> > > > > > > SELECT w.Office,
> > > > > > > SUM(t.CHARGE) AS Charge,
> > > > > > > SUM(t.TAXCHARGE) AS TaxCharge,
> > > > > > > SUM(t.CHARGE + t.TAXCHARGE) AS Total
> > > > > > > FROM dbo.vw_vhb_combine_accounts_distinct w
> > > > > > > JOIN dbo.oss_tran t ON w.sub_acctno = t.ACCTNO AND r.read_period
> > > > > > > IS NULL
> > > > > > > JOIN dbo.oss_user u ON t.ACCTNO = u.ACCTNO AND t.USERNAME = u.ID
> > > > > > > JOIN dbo.oss_proj p ON t.PROJECTNO = p.ACC AND t.ACCTNO = p.ACCTNO
> > > > > > > JOIN dbo.oss_vhb_Data_Input d ON r.[DATE] >= d.BeginDate AND
> > > > > > > r.[DATE] <= EndDate
> > > > > > > GROUP BY w.office
> > > > > > >
> > > > > > > > I hope that's not too confusing. I really need help with this.
> > > > > > > >
> > > > > > > > Thanks,
> > > > > > > > Rachel
> > > > > > > >
> > > > > > >
> > > > > > > I am not sure why you are not passing your date criteria as parameters to a
> > > > > > > stored procedure. The user an proj tables may not be adding anything to this
> > > > > > > query.
> > > > > > >
> > > > > > > John
> > > > > The join condidition is
> > > > >
> > > > > JOIN dbo.oss_vhb_Data_Input d ON r.[DATE] >= d.BeginDate AND r.[DATE] <=> > > > > EndDate
> > > > >
> > > > > Therefore I am not sure what your diagram is showing!
> > > > >
> > > > > Use RIGHT(r.general1,2) in the SELECT part and the GROUP BY clause of the
> > > > > statement
> > > > >
> > > > > SELECT RIGHT(r.general1,2) AS Office,
> > > > > SUM(t.CHARGE) AS Charge,
> > > > > SUM(t.TAXCHARGE) AS TaxCharge,
> > > > > SUM(t.CHARGE + t.TAXCHARGE) AS Total
> > > > > FROM dbo.vw_vhb_combine_accounts_distinct w
> > > > > JOIN dbo.oss_tran t ON w.sub_acctno = t.ACCTNO
> > > > > JOIN dbo.oss_proj p ON t.PROJECTNO = p.ACC AND t.ACCTNO = p.ACCTNO
> > > > > JOIN dbo.oss_vhb_Data_Input d ON r.[DATE] >= d.BeginDate AND
> > > > > r.[DATE] <= EndDate
> > > > > GROUP BY RIGHT(r.general1,2)
> > > > >
> > > > > I would assume that vw_vhb_combine_accounts_distinct is a view, so you may
> > > > > want to look to see if that is contributing anything that is not necessary
> > > > > (if office is not in this view why do you need it?)
> > > > >
> > > > > John
> > > > >
> I assume that the date table is necessary because of the way your
> applications works?
> In query analyser look at the query plan (to this on by checking under the
> show execution paln on the query menu), you will hopefully be able to see
> what is inefficient when you run this query. If sounds like there are missing
> indexes, so using the index tuning wizard may give you some clue as to what
> is missing.
> John|||Hi Rachel
"RFrechette" wrote:
> Hi John,
> Yes, the Date table is necessary. I hope I haven't included too much data
> for you here...
> I looked at the query plan, unfortunately it didn't make much sense to me.
> When I go the "Manage Indexes" for oss_tran it shows:
> Index: pkey, Clustered: No, Columns: Acctno, Date, OrderNo
> Index: pkey2, Clustered: No, Columns: Date
> Index: pkey3, Clustered: No, Columns: OrderNo
> Index: pkey4, Clustered: No, Columns: Acctno, ProjectNo
> I used the Index Tuning Wizard. On the Index Recommendations page it showed:
> Clustered: Yes, Index: oss_tran1, Table: dbo.oss_tran, Column: AcctNo
> Clustered: No, Index: pkey4, Table: dbo.oss_tran, Column: AcctNo, ProjectNo
> Clustered: No, Index: pkey, Table: dbo.oss_tran, Column: AcctNo, Date, OrderNo
> Clustered: No, Index: pkey2, Table: dbo.oss_tran, Column: AcctNo, ProjectNo
> Clustered: No, Index: pkey3, Table: dbo.oss_tran, Column: OrderNo
> (It showed that if I implemented the recommended changes, I should see a 79%
> change in performance.) Unfortunately, I can not do this without permission,
> which will be hard to get.
> Under the Analysis on the Index Recommendations page, it showed for the
> Index Usage Report (Recommended Configuration):
> Table: dbo.oss_tran, Index: [oss_tran1], % usage: 100, Size: 19011696
> Table: dbo.oss_tran, Index: [pkey4], % usage: 0, Size: 1,390,096
> Table: dbo.oss_tran, Index: [pkey1], % usage: 0, Size: 1,553,640
> Table: dbo.oss_tran, Index: [pkey2], % usage: 0, Size: 695,048
> Table: dbo.oss_tran, Index: [pkey3], % usage: 0, Size: 858,592
> Do you think that the problem Iâ'm having getting the query to run in SQL
> Enterprise would be solved if the recommendations were implemented?
> Also, when I look at the properties of the oss_tran table it shows the Full
> Text Indexes as inactive and none of the columns are checked off for Full
> Text Indexes.
> Could this also be the problem?
> I truly appreciate your help on this. I feel like an idiot.
> Thank you, Rachel
>
> "John Bell" wrote:
> > Hi Rachel
> >
> > "RFrechette" wrote:
> >
> > > Hi John,
> > > I checked the query time out value in Enterprise, it's 0. Here's what I'm
> > > getting using the query you gave me:
> > >
> > > WinSQL:
> > > Works in about 3 minutes with correct results.
> > > If I hard code the dates instead of having them look them up, it takes less
> > > than 1 minute with correct results.
> > >
> > > SQL Server Enterprise:
> > > After less than 1 minute I get the message: [Microsoft][ODBC SQL Server
> > > Driver] Timeout expired.
> > > If I hard code the dates instead of having them look them up, it takes less
> > > than 1 minute with correct results.
> > >
> > > Query Analyser:
> > > Works in about 3 minutes with correct results.
> > > If I hard code the dates instead of having them look them up, it takes less
> > > than 1 minute with correct results.
> > >
> > > Does that information help at all?
> > >
> > > Thank you.
> > >
> > > Rachel
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi Rachel
> > > >
> > > > You don't say what time it takes in WinSQL (which I am note sure it is!)
> > > > Try using Query Analyser!!
> > > >
> > > > In Enterprise Manager if you click on the server, then choose Tools/Options
> > > > there is a query timeout value on the advanced tab, this should be 0 for no
> > > > timeout. In Enterprise Manager how are you running this?
> > > >
> > > > John
> > > >
> > > > "RFrechette" wrote:
> > > >
> > > > > Hi John,
> > > > >
> > > > > I want to thank you for helping me.
> > > > >
> > > > > The query is working perfectly when I use it in WinSQL, but it's still
> > > > > timing out when I use it in SQL Server Enterprise Manager. So there must not
> > > > > be anything wrong with the query itself. Maybe there is a way to extend the
> > > > > time out in Enterprise Manager?
> > > > >
> > > > > I'm about ready to give up. I guess I just need to find some SQL classes.
> > > > > (Or classes in using SQL Server Enterprise Manager, if any exist.
> > > > >
> > > > > I really do appreciate all your help though.
> > > > >
> > > > > Rachel
> > > > >
> > > > > "John Bell" wrote:
> > > > >
> > > > > > Hi Rachel
> > > > > >
> > > > > > "RFrechette" wrote:
> > > > > >
> > > > > > > Hi John,
> > > > > > > Thank you so much for helping me.
> > > > > > >
> > > > > > > I took out the "oss_user" table because I think you are right that it is not
> > > > > > > being used. Also, I took out the "AND r.read_period IS NULL" because I
> > > > > > > realized that should not be there as well. I really need only the 2 last
> > > > > > > digits of the field "general1" from table r. I tried using
> > > > > > > "r.Right(general1,2) AS Office but it doesn't seem to like that.
> > > > > > >
> > > > > > > This is what I ended up with:
> > > > > > >
> > > > > > > SELECT r.general1 AS Office, SUM(r.CHARGE) AS Charge, SUM(r.TAXCHARGE)
> > > > > > > AS TaxCharge, SUM(r.CHARGE + r.TAXCHARGE) AS Total
> > > > > > > FROM dbo.vw_vhb_combine_accounts_distinct w INNER JOIN
> > > > > > > dbo.oss_tran r ON w.sub_acctno = r.ACCTNO INNER JOIN
> > > > > > > dbo.oss_proj p ON r.PROJECTNO = p.ACC AND r.ACCTNO => > > > > > > p.ACCTNO INNER JOIN
> > > > > > > dbo.oss_vhb_Data_Input d ON r.[DATE] >= d.BeginDate
> > > > > > > AND r.[DATE] <= d.EndDate
> > > > > > > GROUP BY r.general1
> > > > > > >
> > > > > > > It still times out on me. And of course, if it did work, it wouldn't be
> > > > > > > grouped correctly because I'm using the whole "general1" field instead of the
> > > > > > > last 2 digits.
> > > > > > >
> > > > > > > I noticed in the diagram section, that the d table is joined to the w table.
> > > > > > > Shouldn't the d table be joined to the r table because the r table hold the
> > > > > > > Date field and the d table holds the BeginDate and EndDate fields?
> > > > > > >
> > > > > > > I'm sorry I'm not more knowledgeble on this but I've had no classes and only
> > > > > > > have 1 book and I kind of got thrown into this.
> > > > > > >
> > > > > > > Do you have any more suggestions?
> > > > > > >
> > > > > > > Thank you, Rachel
> > > > > > >
> > > > > > >
> > > > > > > "John Bell" wrote:
> > > > > > >
> > > > > > > > Hi Rachel
> > > > > > > >
> > > > > > > > "RFrechette" wrote:
> > > > > > > >
> > > > > > > > > I am very new to using SQL, so please be patient. I am using MS Access for
> > > > > > > > > the front end and SQL for the backend.
> > > > > > > > >
> > > > > > > > > I'm creating a view (using SQL Server Enterprise Manager) that I will link
> > > > > > > > > to Access, but the view) keeps timing out. If I run the same view using
> > > > > > > > > WinSQL, it works.
> > > > > > > > >
> > > > > > > > > I am trying to lookup data between 2 dates in my main table. The parameter
> > > > > > > > > dates are kept in a separate table having no fields I can join with my main
> > > > > > > > > table. If I manually type the dates in, it works.
> > > > > > > > I assume you mean there is a single row in the table?
> > > > > > > >
> > > > > > > > >
> > > > > > > > > Here's my statement:
> > > > > > > > >
> > > > > > > > > SELECT Office, SUM(dbo.oss_tran.CHARGE) AS Charge,
> > > > > > > > > SUM(dbo.oss_tran.TAXCHARGE) AS TaxCharge,
> > > > > > > > > SUM(dbo.oss_tran.CHARGE + dbo.oss_tran.TAXCHARGE) AS
> > > > > > > > > Total
> > > > > > > > > FROM dbo.vw_vhb_combine_accounts_distinct INNER JOIN
> > > > > > > > > dbo.oss_tran ON
> > > > > > > > > dbo.vw_vhb_combine_accounts_distinct.sub_acctno = dbo.oss_tran.ACCTNO INNER
> > > > > > > > > JOIN
> > > > > > > > > dbo.oss_user ON dbo.oss_tran.ACCTNO => > > > > > > > > dbo.oss_user.ACCTNO AND dbo.oss_tran.USERNAME = dbo.oss_user.ID INNER JOIN
> > > > > > > > > dbo.oss_proj ON dbo.oss_tran.PROJECTNO => > > > > > > > > dbo.oss_proj.ACC AND dbo.oss_tran.ACCTNO = dbo.oss_proj.ACCTNO
> > > > > > > > > WHERE (dbo.oss_tran.[DATE] BETWEEN
> > > > > > > > > (SELECT BeginDate
> > > > > > > > > FROM dbo.oss_vhb_Data_Input) AND
> > > > > > > > > (SELECT EndDate
> > > > > > > > > FROM dbo.oss_vhb_Data_Input)) AND
> > > > > > > > > (dbo.oss_tran.read_period IS NULL)
> > > > > > > > > GROUP BY RIGHT office
> > > > > > > > >
> > > > > > > > Try (untested):
> > > > > > > >
> > > > > > > > SELECT w.Office,
> > > > > > > > SUM(t.CHARGE) AS Charge,
> > > > > > > > SUM(t.TAXCHARGE) AS TaxCharge,
> > > > > > > > SUM(t.CHARGE + t.TAXCHARGE) AS Total
> > > > > > > > FROM dbo.vw_vhb_combine_accounts_distinct w
> > > > > > > > JOIN dbo.oss_tran t ON w.sub_acctno = t.ACCTNO AND r.read_period
> > > > > > > > IS NULL
> > > > > > > > JOIN dbo.oss_user u ON t.ACCTNO = u.ACCTNO AND t.USERNAME = u.ID
> > > > > > > > JOIN dbo.oss_proj p ON t.PROJECTNO = p.ACC AND t.ACCTNO = p.ACCTNO
> > > > > > > > JOIN dbo.oss_vhb_Data_Input d ON r.[DATE] >= d.BeginDate AND
> > > > > > > > r.[DATE] <= EndDate
> > > > > > > > GROUP BY w.office
> > > > > > > >
> > > > > > > > > I hope that's not too confusing. I really need help with this.
> > > > > > > > >
> > > > > > > > > Thanks,
> > > > > > > > > Rachel
> > > > > > > > >
> > > > > > > >
> > > > > > > > I am not sure why you are not passing your date criteria as parameters to a
> > > > > > > > stored procedure. The user an proj tables may not be adding anything to this
> > > > > > > > query.
> > > > > > > >
> > > > > > > > John
> > > > > > The join condidition is
> > > > > >
> > > > > > JOIN dbo.oss_vhb_Data_Input d ON r.[DATE] >= d.BeginDate AND r.[DATE] <=> > > > > > EndDate
> > > > > >
> > > > > > Therefore I am not sure what your diagram is showing!
> > > > > >
> > > > > > Use RIGHT(r.general1,2) in the SELECT part and the GROUP BY clause of the
> > > > > > statement
> > > > > >
> > > > > > SELECT RIGHT(r.general1,2) AS Office,
> > > > > > SUM(t.CHARGE) AS Charge,
> > > > > > SUM(t.TAXCHARGE) AS TaxCharge,
> > > > > > SUM(t.CHARGE + t.TAXCHARGE) AS Total
> > > > > > FROM dbo.vw_vhb_combine_accounts_distinct w
> > > > > > JOIN dbo.oss_tran t ON w.sub_acctno = t.ACCTNO
> > > > > > JOIN dbo.oss_proj p ON t.PROJECTNO = p.ACC AND t.ACCTNO = p.ACCTNO
> > > > > > JOIN dbo.oss_vhb_Data_Input d ON r.[DATE] >= d.BeginDate AND
> > > > > > r.[DATE] <= EndDate
> > > > > > GROUP BY RIGHT(r.general1,2)
> > > > > >
> > > > > > I would assume that vw_vhb_combine_accounts_distinct is a view, so you may
> > > > > > want to look to see if that is contributing anything that is not necessary
> > > > > > (if office is not in this view why do you need it?)
> > > > > >
> > > > > > John
> > > > > >
> > I assume that the date table is necessary because of the way your
> > applications works?
> >
> > In query analyser look at the query plan (to this on by checking under the
> > show execution paln on the query menu), you will hopefully be able to see
> > what is inefficient when you run this query. If sounds like there are missing
> > indexes, so using the index tuning wizard may give you some clue as to what
> > is missing.
> >
> > John
The index tuning wizard is effectively saying that the indexes on the single
columns are not useful for this query, and that covering indexes would be
alot better. It does not mean that they are not useful to other queries that
are executed on your systems and a more thorough indexing excercise would
take a larger sample of queries and produces the best indexing strategy for
the overall system.
You may want to try using SET SHOWPLAN_ALL or SET SHOWPLAN_TEXT to get
information about the expected query plan i.e.
SET SHOWPLAN_ALL ON before your query and SET SHOWPLAN_ALL OFF after it. You
may then be able to post the plans.
Can you use a stored procedure to encapsulate this query?
e.g.
CREATE PROCEDURE spr_execmyquery
AS
BEGIN
DECLARE @.datestart datetime, @.dateend datetime
SELECT @.datestart = BeginDate,
@.dateend = EndDate
FROM dbo.oss_vhb_Data_Input
SELECT RIGHT(w.general1,2) AS Office,
SUM(t.CHARGE) AS Charge,
SUM(t.TAXCHARGE) AS TaxCharge,
SUM(t.CHARGE + t.TAXCHARGE) AS Total
FROM dbo.vw_vhb_combine_accounts_distinct w
JOIN dbo.oss_tran t ON w.sub_acctno = t.ACCTNO AND t.[DATE] >=@.datestart AND t.[DATE] <= @.dateend
JOIN dbo.oss_proj p ON t.PROJECTNO = p.ACC AND t.ACCTNO = p.ACCTNO
GROUP BY RIGHT(w.general1,2)
END
I am a bit confudled regarding which tables currently being used and what
their aliases are!!
John|||Hi John,
I couldn't get the SET SHOWPLAN_ALL to work.
I did create a stored procedure. But I'm afraid I don't know where to go
from there.
I think it's pretty obvious that I'm way in over my head. I'm not by any
means a programmer. And I need to take some classes.
I can't express enough how thankful I am for your help and the time you put
into this. You did teach me quite a few things.
My boss knew I didn't know SQL when he hired me, they'll just have to accept
the fact that I need some training.
Thank you so much.
Rachel
"John Bell" wrote:
> Hi Rachel
> "RFrechette" wrote:
> > Hi John,
> > Yes, the Date table is necessary. I hope I haven't included too much data
> > for you here...
> >
> > I looked at the query plan, unfortunately it didn't make much sense to me.
> >
> > When I go the "Manage Indexes" for oss_tran it shows:
> >
> > Index: pkey, Clustered: No, Columns: Acctno, Date, OrderNo
> > Index: pkey2, Clustered: No, Columns: Date
> > Index: pkey3, Clustered: No, Columns: OrderNo
> > Index: pkey4, Clustered: No, Columns: Acctno, ProjectNo
> >
> > I used the Index Tuning Wizard. On the Index Recommendations page it showed:
> >
> > Clustered: Yes, Index: oss_tran1, Table: dbo.oss_tran, Column: AcctNo
> > Clustered: No, Index: pkey4, Table: dbo.oss_tran, Column: AcctNo, ProjectNo
> > Clustered: No, Index: pkey, Table: dbo.oss_tran, Column: AcctNo, Date, OrderNo
> > Clustered: No, Index: pkey2, Table: dbo.oss_tran, Column: AcctNo, ProjectNo
> > Clustered: No, Index: pkey3, Table: dbo.oss_tran, Column: OrderNo
> >
> > (It showed that if I implemented the recommended changes, I should see a 79%
> > change in performance.) Unfortunately, I can not do this without permission,
> > which will be hard to get.
> >
> > Under the Analysis on the Index Recommendations page, it showed for the
> > Index Usage Report (Recommended Configuration):
> >
> > Table: dbo.oss_tran, Index: [oss_tran1], % usage: 100, Size: 19011696
> > Table: dbo.oss_tran, Index: [pkey4], % usage: 0, Size: 1,390,096
> > Table: dbo.oss_tran, Index: [pkey1], % usage: 0, Size: 1,553,640
> > Table: dbo.oss_tran, Index: [pkey2], % usage: 0, Size: 695,048
> > Table: dbo.oss_tran, Index: [pkey3], % usage: 0, Size: 858,592
> >
> > Do you think that the problem Iâ'm having getting the query to run in SQL
> > Enterprise would be solved if the recommendations were implemented?
> >
> > Also, when I look at the properties of the oss_tran table it shows the Full
> > Text Indexes as inactive and none of the columns are checked off for Full
> > Text Indexes.
> >
> > Could this also be the problem?
> >
> > I truly appreciate your help on this. I feel like an idiot.
> >
> > Thank you, Rachel
> >
> >
> > "John Bell" wrote:
> >
> > > Hi Rachel
> > >
> > > "RFrechette" wrote:
> > >
> > > > Hi John,
> > > > I checked the query time out value in Enterprise, it's 0. Here's what I'm
> > > > getting using the query you gave me:
> > > >
> > > > WinSQL:
> > > > Works in about 3 minutes with correct results.
> > > > If I hard code the dates instead of having them look them up, it takes less
> > > > than 1 minute with correct results.
> > > >
> > > > SQL Server Enterprise:
> > > > After less than 1 minute I get the message: [Microsoft][ODBC SQL Server
> > > > Driver] Timeout expired.
> > > > If I hard code the dates instead of having them look them up, it takes less
> > > > than 1 minute with correct results.
> > > >
> > > > Query Analyser:
> > > > Works in about 3 minutes with correct results.
> > > > If I hard code the dates instead of having them look them up, it takes less
> > > > than 1 minute with correct results.
> > > >
> > > > Does that information help at all?
> > > >
> > > > Thank you.
> > > >
> > > > Rachel
> > > >
> > > > "John Bell" wrote:
> > > >
> > > > > Hi Rachel
> > > > >
> > > > > You don't say what time it takes in WinSQL (which I am note sure it is!)
> > > > > Try using Query Analyser!!
> > > > >
> > > > > In Enterprise Manager if you click on the server, then choose Tools/Options
> > > > > there is a query timeout value on the advanced tab, this should be 0 for no
> > > > > timeout. In Enterprise Manager how are you running this?
> > > > >
> > > > > John
> > > > >
> > > > > "RFrechette" wrote:
> > > > >
> > > > > > Hi John,
> > > > > >
> > > > > > I want to thank you for helping me.
> > > > > >
> > > > > > The query is working perfectly when I use it in WinSQL, but it's still
> > > > > > timing out when I use it in SQL Server Enterprise Manager. So there must not
> > > > > > be anything wrong with the query itself. Maybe there is a way to extend the
> > > > > > time out in Enterprise Manager?
> > > > > >
> > > > > > I'm about ready to give up. I guess I just need to find some SQL classes.
> > > > > > (Or classes in using SQL Server Enterprise Manager, if any exist.
> > > > > >
> > > > > > I really do appreciate all your help though.
> > > > > >
> > > > > > Rachel
> > > > > >
> > > > > > "John Bell" wrote:
> > > > > >
> > > > > > > Hi Rachel
> > > > > > >
> > > > > > > "RFrechette" wrote:
> > > > > > >
> > > > > > > > Hi John,
> > > > > > > > Thank you so much for helping me.
> > > > > > > >
> > > > > > > > I took out the "oss_user" table because I think you are right that it is not
> > > > > > > > being used. Also, I took out the "AND r.read_period IS NULL" because I
> > > > > > > > realized that should not be there as well. I really need only the 2 last
> > > > > > > > digits of the field "general1" from table r. I tried using
> > > > > > > > "r.Right(general1,2) AS Office but it doesn't seem to like that.
> > > > > > > >
> > > > > > > > This is what I ended up with:
> > > > > > > >
> > > > > > > > SELECT r.general1 AS Office, SUM(r.CHARGE) AS Charge, SUM(r.TAXCHARGE)
> > > > > > > > AS TaxCharge, SUM(r.CHARGE + r.TAXCHARGE) AS Total
> > > > > > > > FROM dbo.vw_vhb_combine_accounts_distinct w INNER JOIN
> > > > > > > > dbo.oss_tran r ON w.sub_acctno = r.ACCTNO INNER JOIN
> > > > > > > > dbo.oss_proj p ON r.PROJECTNO = p.ACC AND r.ACCTNO => > > > > > > > p.ACCTNO INNER JOIN
> > > > > > > > dbo.oss_vhb_Data_Input d ON r.[DATE] >= d.BeginDate
> > > > > > > > AND r.[DATE] <= d.EndDate
> > > > > > > > GROUP BY r.general1
> > > > > > > >
> > > > > > > > It still times out on me. And of course, if it did work, it wouldn't be
> > > > > > > > grouped correctly because I'm using the whole "general1" field instead of the
> > > > > > > > last 2 digits.
> > > > > > > >
> > > > > > > > I noticed in the diagram section, that the d table is joined to the w table.
> > > > > > > > Shouldn't the d table be joined to the r table because the r table hold the
> > > > > > > > Date field and the d table holds the BeginDate and EndDate fields?
> > > > > > > >
> > > > > > > > I'm sorry I'm not more knowledgeble on this but I've had no classes and only
> > > > > > > > have 1 book and I kind of got thrown into this.
> > > > > > > >
> > > > > > > > Do you have any more suggestions?
> > > > > > > >
> > > > > > > > Thank you, Rachel
> > > > > > > >
> > > > > > > >
> > > > > > > > "John Bell" wrote:
> > > > > > > >
> > > > > > > > > Hi Rachel
> > > > > > > > >
> > > > > > > > > "RFrechette" wrote:
> > > > > > > > >
> > > > > > > > > > I am very new to using SQL, so please be patient. I am using MS Access for
> > > > > > > > > > the front end and SQL for the backend.
> > > > > > > > > >
> > > > > > > > > > I'm creating a view (using SQL Server Enterprise Manager) that I will link
> > > > > > > > > > to Access, but the view) keeps timing out. If I run the same view using
> > > > > > > > > > WinSQL, it works.
> > > > > > > > > >
> > > > > > > > > > I am trying to lookup data between 2 dates in my main table. The parameter
> > > > > > > > > > dates are kept in a separate table having no fields I can join with my main
> > > > > > > > > > table. If I manually type the dates in, it works.
> > > > > > > > > I assume you mean there is a single row in the table?
> > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > Here's my statement:
> > > > > > > > > >
> > > > > > > > > > SELECT Office, SUM(dbo.oss_tran.CHARGE) AS Charge,
> > > > > > > > > > SUM(dbo.oss_tran.TAXCHARGE) AS TaxCharge,
> > > > > > > > > > SUM(dbo.oss_tran.CHARGE + dbo.oss_tran.TAXCHARGE) AS
> > > > > > > > > > Total
> > > > > > > > > > FROM dbo.vw_vhb_combine_accounts_distinct INNER JOIN
> > > > > > > > > > dbo.oss_tran ON
> > > > > > > > > > dbo.vw_vhb_combine_accounts_distinct.sub_acctno = dbo.oss_tran.ACCTNO INNER
> > > > > > > > > > JOIN
> > > > > > > > > > dbo.oss_user ON dbo.oss_tran.ACCTNO => > > > > > > > > > dbo.oss_user.ACCTNO AND dbo.oss_tran.USERNAME = dbo.oss_user.ID INNER JOIN
> > > > > > > > > > dbo.oss_proj ON dbo.oss_tran.PROJECTNO => > > > > > > > > > dbo.oss_proj.ACC AND dbo.oss_tran.ACCTNO = dbo.oss_proj.ACCTNO
> > > > > > > > > > WHERE (dbo.oss_tran.[DATE] BETWEEN
> > > > > > > > > > (SELECT BeginDate
> > > > > > > > > > FROM dbo.oss_vhb_Data_Input) AND
> > > > > > > > > > (SELECT EndDate
> > > > > > > > > > FROM dbo.oss_vhb_Data_Input)) AND
> > > > > > > > > > (dbo.oss_tran.read_period IS NULL)
> > > > > > > > > > GROUP BY RIGHT office
> > > > > > > > > >
> > > > > > > > > Try (untested):
> > > > > > > > >
> > > > > > > > > SELECT w.Office,
> > > > > > > > > SUM(t.CHARGE) AS Charge,
> > > > > > > > > SUM(t.TAXCHARGE) AS TaxCharge,
> > > > > > > > > SUM(t.CHARGE + t.TAXCHARGE) AS Total
> > > > > > > > > FROM dbo.vw_vhb_combine_accounts_distinct w
> > > > > > > > > JOIN dbo.oss_tran t ON w.sub_acctno = t.ACCTNO AND r.read_period
> > > > > > > > > IS NULL
> > > > > > > > > JOIN dbo.oss_user u ON t.ACCTNO = u.ACCTNO AND t.USERNAME = u.ID
> > > > > > > > > JOIN dbo.oss_proj p ON t.PROJECTNO = p.ACC AND t.ACCTNO = p.ACCTNO
> > > > > > > > > JOIN dbo.oss_vhb_Data_Input d ON r.[DATE] >= d.BeginDate AND
> > > > > > > > > r.[DATE] <= EndDate
> > > > > > > > > GROUP BY w.office
> > > > > > > > >
> > > > > > > > > > I hope that's not too confusing. I really need help with this.
> > > > > > > > > >
> > > > > > > > > > Thanks,
> > > > > > > > > > Rachel
> > > > > > > > > >
> > > > > > > > >
> > > > > > > > > I am not sure why you are not passing your date criteria as parameters to a
> > > > > > > > > stored procedure. The user an proj tables may not be adding anything to this
> > > > > > > > > query.
> > > > > > > > >
> > > > > > > > > John
> > > > > > > The join condidition is
> > > > > > >
> > > > > > > JOIN dbo.oss_vhb_Data_Input d ON r.[DATE] >= d.BeginDate AND r.[DATE] <=> > > > > > > EndDate
> > > > > > >
> > > > > > > Therefore I am not sure what your diagram is showing!
> > > > > > >
> > > > > > > Use RIGHT(r.general1,2) in the SELECT part and the GROUP BY clause of the
> > > > > > > statement
> > > > > > >
> > > > > > > SELECT RIGHT(r.general1,2) AS Office,
> > > > > > > SUM(t.CHARGE) AS Charge,
> > > > > > > SUM(t.TAXCHARGE) AS TaxCharge,
> > > > > > > SUM(t.CHARGE + t.TAXCHARGE) AS Total
> > > > > > > FROM dbo.vw_vhb_combine_accounts_distinct w
> > > > > > > JOIN dbo.oss_tran t ON w.sub_acctno = t.ACCTNO
> > > > > > > JOIN dbo.oss_proj p ON t.PROJECTNO = p.ACC AND t.ACCTNO = p.ACCTNO
> > > > > > > JOIN dbo.oss_vhb_Data_Input d ON r.[DATE] >= d.BeginDate AND
> > > > > > > r.[DATE] <= EndDate
> > > > > > > GROUP BY RIGHT(r.general1,2)
> > > > > > >
> > > > > > > I would assume that vw_vhb_combine_accounts_distinct is a view, so you may
> > > > > > > want to look to see if that is contributing anything that is not necessary
> > > > > > > (if office is not in this view why do you need it?)
> > > > > > >
> > > > > > > John
> > > > > > >
> > > I assume that the date table is necessary because of the way your
> > > applications works?
> > >
> > > In query analyser look at the query plan (to this on by checking under the
> > > show execution paln on the query menu), you will hopefully be able to see
> > > what is inefficient when you run this query. If sounds like there are missing
> > > indexes, so using the index tuning wizard may give you some clue as to what
> > > is missing.
> > >
> > > John
> The index tuning wizard is effectively saying that the indexes on the single
> columns are not useful for this query, and that covering indexes would be
> alot better. It does not mean that they are not useful to other queries that
> are executed on your systems and a more thorough indexing excercise would
> take a larger sample of queries and produces the best indexing strategy for
> the overall system.
> You may want to try using SET SHOWPLAN_ALL or SET SHOWPLAN_TEXT to get
> information about the expected query plan i.e.
> SET SHOWPLAN_ALL ON before your query and SET SHOWPLAN_ALL OFF after it. You
> may then be able to post the plans.
> Can you use a stored procedure to encapsulate this query?
> e.g.
> CREATE PROCEDURE spr_execmyquery
> AS
> BEGIN
> DECLARE @.datestart datetime, @.dateend datetime
> SELECT @.datestart = BeginDate,
> @.dateend = EndDate
> FROM dbo.oss_vhb_Data_Input
> SELECT RIGHT(w.general1,2) AS Office,
> SUM(t.CHARGE) AS Charge,
> SUM(t.TAXCHARGE) AS TaxCharge,
> SUM(t.CHARGE + t.TAXCHARGE) AS Total
> FROM dbo.vw_vhb_combine_accounts_distinct w
> JOIN dbo.oss_tran t ON w.sub_acctno = t.ACCTNO AND t.[DATE] >=> @.datestart AND t.[DATE] <= @.dateend
> JOIN dbo.oss_proj p ON t.PROJECTNO = p.ACC AND t.ACCTNO = p.ACCTNO
> GROUP BY RIGHT(w.general1,2)
> END|||Hi Rachel
"RFrechette" wrote:
> Hi John,
> I couldn't get the SET SHOWPLAN_ALL to work.
> I did create a stored procedure. But I'm afraid I don't know where to go
> from there.
> Thank you so much.
> Rachel
How would you have used the query?
Try substituting the query for the statement
EXEC spr_execmyquery
John

Tuesday, March 27, 2012

data extension error

Sunday, March 25, 2012

Data entry in a datetime field

Data entry in a datetime field

Thursday, March 22, 2012

Data driven Subscription

Thursday, March 8, 2012

Data between 2 dates issue

Data between 2 dates issue

Data between 2 dates issue

Data Flow Task Problem

Blog Archive

About Me