Data Flow Task Problem: access

Showing posts with label access. Show all posts

Sunday, March 25, 2012

Data entry table not editable in Access ADP - what do I need to do

Hello,
I created a simple table for data entry where I can manually edit/add data
to the table in Enterprise Manager. But when I open the same table in an
Access it is locked. I cannot edit/add data to the table. Does anyone know
what I need to do to make the table editable in the Access ADP?
Thanks,
RichFigured it out. You need to add a key field to the table to make it manuall
y
editable outside of Enterprise Manager.
"Rich" wrote:

> Hello,
> I created a simple table for data entry where I can manually edit/add data
> to the table in Enterprise Manager. But when I open the same table in an
> Access it is locked. I cannot edit/add data to the table. Does anyone kn
ow
> what I need to do to make the table editable in the Access ADP?
> Thanks,
> Rich

Data encryption and keys

Hi,
I would like to encrypt data in my database. I want encrypted column value to be viewable only for certain group of users. Users that has access to my database doesn't meant they can access to my encrypted data.

Currently, I am using the following "approach" as my key management.

create master key encryption by password= 'MasterKeyPass'

CREATE ASYMMETRIC KEY MyAsymmKey AUTHORIZATION MyUser
WITH ALGORITHM = RSA_1024
ENCRYPTION BY PASSWORD ='MyAsymmPass'

CREATE SYMMETRIC KEY MySymmKey WITH ALGORITHM = DES
ENCRYPTION BY ASYMMETRIC KEY MyAsymmKey

My data will be encrypted using Symmetric key MySymmKey.

User who want to access my data must have MasterKey and MyAsymmKey password.
Is it OK? Any better way?

Thank you

As long as the user you are trying to protect against is not a dbo or sysadmin, you can also use permissions (i.e. "GRANT CONTROL ON ASYMMETRIC KEY :: MyAsymmKey TO user1") to restrict access rather than through passwords. The advantage is the user then doesn't have to depend on memorizing a password and you don't have to pass any password values in which is safer from a security standpoint.

Sung

|||Fyi, Books online links up a section about BACKUP and RESTORING encryption keys http://msdn2.microsoft.com/en-US/library/ms157275.aspx link.

data encryption

Hello there
I have database with some details that i don't no one can read it. including
programmers who have access directly to sql server.
How can i do that ?Roy, shalom
I assume , you are using SQL Server 2000 , right?
Take a look at this article
http://vyaskn.tripod.com/ row_level...as
es.htm
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:u3IMJblMHHA.2456@.TK2MSFTNGP06.phx.gbl...
> Hello there
> I have database with some details that i don't no one can read it.
> including programmers who have access directly to sql server.
> How can i do that ?
>|||what's the datatypes of that columns? if it looks like the binary datea,
then try to convert it as varchar(8000) column
SELECt cast(urVarbinaryColumn as varchar(8000) )
from urTable
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:u3IMJblMHHA.2456@.TK2MSFTNGP06.phx.gbl...
> Hello there
> I have database with some details that i don't no one can read it.
> including programmers who have access directly to sql server.
> How can i do that ?
>|||"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:u3IMJblMHHA.2456@.TK2MSFTNGP06.phx.gbl...
> Hello there
> I have database with some details that i don't no one can read it.
> including programmers who have access directly to sql server.
> How can i do that ?
In addition, if SQL 2005, look here:
http://www.sqlservercentral.com/col...oolkitpart1.aspsql

Wednesday, March 21, 2012

Data Dictionary

In SQL 2000 is there a way to generate a data dictionary of an existing data
base? If so how do you go about doing it? In Access it has a documenter that
will create one for you. ThanksSQL doesn't really have a tool like that, but there are a couple of things
you might do.
1. Right click a database in SQL Enterprise manager -> all tasks -> generate
Sql script...
2. Go to metadata in Enterprise Manager (under Data transformation services)
and import metdata...
Neither is really what you are looking for I suspect
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Alan" <anonymous@.discussions.microsoft.com> wrote in message
news:C5866E7F-E6A7-4791-8F28-2C82E3343984@.microsoft.com...

quote:

> In SQL 2000 is there a way to generate a data dictionary of an existing

database? If so how do you go about doing it? In Access it has a documenter
that will create one for you. Thankssql

Data Dictionary

In SQL 2000 is there a way to generate a data dictionary of an existing database? If so how do you go about doing it? In Access it has a documenter that will create one for you. ThanksSQL doesn't really have a tool like that, but there are a couple of things
you might do.
1. Right click a database in SQL Enterprise manager -> all tasks -> generate
Sql script...
2. Go to metadata in Enterprise Manager (under Data transformation services)
and import metdata...
Neither is really what you are looking for I suspect
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Alan" <anonymous@.discussions.microsoft.com> wrote in message
news:C5866E7F-E6A7-4791-8F28-2C82E3343984@.microsoft.com...
> In SQL 2000 is there a way to generate a data dictionary of an existing
database? If so how do you go about doing it? In Access it has a documenter
that will create one for you. Thankssql

Monday, March 19, 2012

Data Delivery

I've created a number of .html, pivot tables using Access' Data Page
tools. These work great and by pointing my browser to these files,
users throughout my firm can access these pivot tables. I had assumed
that I could simply publish these pivot tables on the web to outside
users who are configured as active directory users. The .htm pivot
table is linked to a query in Access that is built from a SQL database.
We've separately built a system where users can crawl through our file
server from outside of our network. I placed the .htm file in a folder
that the user has access to and the application shows the file.
However, when the user clicks on the file, s/he gets these messages:
Data Access Pages has detected that your IE security settings will not
allow you to access data from a site considered to be insecure.
[
In order to access the data contained within the Data Access Page, you
need to:
1. Start IE
2. Choose Internet Options from the Tools menu
3. Click on the "Security" Tab
4. Click on the "Trusted Sites" icon
5. Click on the "Sites..." button
6. Uncheck the "Require server verification (https) for all sites in
the zone" checkbox
]
Our technology consultant indicated that there's no way for the data to
"bind" to the browser and it's impossible to expose these .htm, pivot
tables to users outside of our network. Does anyone know a way to
accomplish what I'm trying to do? I feel like it should be possible
since Microsoft created that product to build .htm pages?
Ryan
Any suggestions would be very helpfulRyan.Chowdhury@.gmail.com wrote:
> I've created a number of .html, pivot tables using Access' Data Page
> tools. These work great and by pointing my browser to these files,
> users throughout my firm can access these pivot tables. I had assumed
> that I could simply publish these pivot tables on the web to outside
> users who are configured as active directory users. The .htm pivot
> table is linked to a query in Access that is built from a SQL database.
>
> We've separately built a system where users can crawl through our file
> server from outside of our network. I placed the .htm file in a folder
> that the user has access to and the application shows the file.
> However, when the user clicks on the file, s/he gets these messages:
> Data Access Pages has detected that your IE security settings will not
> allow you to access data from a site considered to be insecure.
> [
> In order to access the data contained within the Data Access Page, you
> need to:
> 1. Start IE
> 2. Choose Internet Options from the Tools menu
> 3. Click on the "Security" Tab
> 4. Click on the "Trusted Sites" icon
> 5. Click on the "Sites..." button
> 6. Uncheck the "Require server verification (https) for all sites in
> the zone" checkbox
> ]
> Our technology consultant indicated that there's no way for the data to
> "bind" to the browser and it's impossible to expose these .htm, pivot
> tables to users outside of our network. Does anyone know a way to
> accomplish what I'm trying to do? I feel like it should be possible
> since Microsoft created that product to build .htm pages?
> Ryan
> Any suggestions would be very helpful
>
First off, you might have better luck asking in an Access group instead
of SQL Server. Second, let me say, YIKES!!! You allow outside access
to your file servers?
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Data Delivery

Data converted when loaded into SQL 2k5 table

Hi All

Data in access is converted when loaded into SQL 2k5

00 >> 0

01 >> 1

03 >> 3

I am using SSIS import wizard to load data from MS Access ’03 into a SQL 2k5 database.Some data of the data is converted, or the leading zero is deleted when loaded into sql table.

The data type on the source field is byte with a 00 format. The data type in 2k5 is tinyint.

I need some help with getting the data to load into 2k5 exactly as it appears in access.

Thanks for you help.

Nats

Hi Nats

One quick question - how is the data going to be used once it has been imported? To store data in the format that you specified then you'll have to decare the column as a text-based datatype, such as VARCHAR, which is not necessarily the best option for numerical data.

If you're going to perform calculations on the data then it might be better to store the data as TINYINT then manipulate the formatting when you want to return / display the data.

e.g.

DECLARE @.int INT

SET @.int = 1

SELECT '00' + RIGHT(CAST(@.int AS VARCHAR(1)), 2)

...will return a text string of '01' even though @.int is of type integer.

Chris

|||

I don't think it will be used in any calculation.

Thanks,

Data converted when loaded into SQL 2k5 table

Hi All

Data in access is converted when loaded into SQL 2k5

00 >> 0

01 >> 1

03 >> 3

I am using SSIS import wizard to load data from MS Access ’03 into a SQL 2k5 database.Some data of the data is converted, or the leading zero is deleted when loaded into sql table.

The data type on the source field is byte with a 00 format. The data type in 2k5 is tinyint.

I need some help with getting the data to load into 2k5 exactly as it appears in access.

Thanks for you help.

Nats

Hi Nats

If you're going to perform calculations on the data then it might be better to store the data as TINYINT then manipulate the formatting when you want to return / display the data.

e.g.

DECLARE @.int INT

SET @.int = 1

SELECT '00' + RIGHT(CAST(@.int AS VARCHAR(1)), 2)

...will return a text string of '01' even though @.int is of type integer.

Chris

|||

I don't think it will be used in any calculation.

Thanks,

Sunday, March 11, 2012

Data connection problem between Assess front end and SQL server behind firewall

I have an Access 2000 front end (on win 98) for a SQL 2000 database (on win 2000 server) behind a firewall. A port (1433) was opened for connecting the Access front end to the SQL database. The odbc system DSN connection showed data source "TESTS COMPLETED SUCCESSFULLY!". However, the Access front end still does not work. The same Access program works well within the firewall. Could anyone tell me what could possible be wrong?What error does Access return?

Data connection error - Please help

Thanks in advance to anyone that can help me with this.

We have software in a production environment that uses a DSN-Less connection to access an Access Database. The software was written in VB6 and uses MDAC 2.8

99% of customers have no issues with the software connecting the the database however once in while we encounter a customer that get's the following error:

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Except for the DB path the rest of the connection string is hardcoded in the app so there is no difference in the name of the driver used on 99% of the working installations and the 1% that fail. The debug log returned by the customer shows that the following connection string is being used:

Driver={Microsoft Access Driver (*.mdb)};Uid=xxxx;Pwd=xxx;Dbq=C:\Windows\SomeSubDir\ourAccessDB;

We have verfied the following:

1. Our Access DB must be placed under a subdirectory of the main Windows directory. There is special reason for this but it would take too long to explain. However, I have confirmed that the database can be accessed and the user has full permissions on these folders. The can create new files in the same directory, etc.

2. Customer is running Windows XP SP2. Has MDAC 2.8 installed as well as the MS Jet SP8

3. Customer confirms that the entry "Microsoft Access Drive (*.mdb)" shows up in the driver tab of the ODBC panel.

4. I have discovered that I can only reproduce that error on our development systems if I change the connection string to use a driver that doesn't exist (e.g. Driver={Mosoft Access Driver (*.mdb)}

5. Latest users states that this is a "clean" system that is fairly new.

Any ideas would be greatly appreciated.

Take a look at this support article

http://support.microsoft.com/default.aspx/kb/271908

Hope this helps

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

Sunday, March 25, 2012

Data entry table not editable in Access ADP - what do I need to do

Data encryption and keys

data encryption

Wednesday, March 21, 2012

Data Dictionary

Data Dictionary

Monday, March 19, 2012

Data Delivery

Data Delivery

Data converted when loaded into SQL 2k5 table

Data converted when loaded into SQL 2k5 table

Sunday, March 11, 2012

Data connection problem between Assess front end and SQL server behind firewall

Data connection error - Please help

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