Query

I got this access database thats been changed to SQL. The person who made the database created a make-table which is suppose to be a temporary table which sends data to a html webpage. I dont understand why they are making all these temporary tables, but their reason for this is becasue they dont want the updates which would come from the Originating table. Does that make sense. Now I would think this would be where the "criteria" would come in, ya know with some wild cards possibly. Trying to understand someone elses logic...Then I was thinking about a possible Holding TableThere are a bunch of possibilities, but my guess would be that they were building the data "on the fly" using Access Queries, so they didn't have a complete picture until they finished. If that were the case, it would make sense to create a sort of a "staging area" where you could build what you wanted to present one step at a time, then copy it out for the final presentation.

-PatP|||Hi Pat how are ya :)

Ok from what I am seeing here it looks like they have vb coding that creates this temporary table which constantly overwrites itself...and this information goes onto a webpage..The tech says that ADP files dont support Html exports. My thought was why not create an xml webpage through the enterprise manager in sql??

Query

Hi all,
I created a query in Access that I want to convert to SQL. The query is a
little complicated and I tried to work out the differences between the two
engines in terms of the functions I used. Still I'm getting error messages
regarding the ROUND function. Please Help!!
Create proc st_proc_FASP6M AS
(
SELECT SHR_Case_Name AS CaseName, RTrim(SHR_last_name)+",
"+RTrim(SHR_first_name) AS ChildName, CRS_CID,
((Round((6/1/2005-[CRS_cid])/182.625)+1)*182.625+ [CRS_cid],0) AS
Prelim_Plan_Date, DatePart("d",[CRS_cid]) AS ciddate,
DatePart("d",[prelim_plan_date]) AS pdate, [ciddate]-[pdate] AS difference,
Abs([difference]) AS [absolute],
IIf([absolute]< =3,[prelim_plan_date]+[difference],[prel
im_plan_date]-0) AS
Plan_Date, DateAdd("d",-25,[plan_Date]) AS ACS_Date,
DateAdd("d",-30,[plan_date]) AS Supervisor_DueDate INTO tbl_FaspFinal
FROM tbl_PreventionCases
GROUP BY RTrim([tbl_PreventionCases]![SHR_last_na
me])+",
" +RTrim([tbl_PreventionCases]![SHR_first_
name]), tbl_PreventionCases.CRS_CID
HAVING
(((((Round((6/1/2005-[tbl_PreventionCases]![CRS_cid])/182.625)+1)*182.625+[t
bl_PreventionCases]![CRS_cid]),0) Between 9/30/2005 And 11/30/2005));)
TSI found at least two things you need to change:
IIF.
No such animal in TSQL or ANSI SQL. Use CASE instead.

> Abs([difference]) AS [absolute],
Here you reference a column alias given by the prior column in the SELECT. I
.e., there is no column
named difference in the table, at least I guess so). No can do (as per ANSI
SQL), everything
"happens at once" in SQL.
If you post something we can execute, we might be able to help you further.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"TS" <TS@.discussions.microsoft.com> wrote in message
news:56D23A37-C8CB-4C12-8630-0A22E96864DD@.microsoft.com...
> Hi all,
> I created a query in Access that I want to convert to SQL. The query is a
> little complicated and I tried to work out the differences between the two
> engines in terms of the functions I used. Still I'm getting error messages
> regarding the ROUND function. Please Help!!
> Create proc st_proc_FASP6M AS
> (
> SELECT SHR_Case_Name AS CaseName, RTrim(SHR_last_name)+",
> "+RTrim(SHR_first_name) AS ChildName, CRS_CID,
> ((Round((6/1/2005-[CRS_cid])/182.625)+1)*182.625+ [CRS_cid],0) AS
> Prelim_Plan_Date, DatePart("d",[CRS_cid]) AS ciddate,
> DatePart("d",[prelim_plan_date]) AS pdate, [ciddate]-[pdate] AS difference,
> Abs([difference]) AS [absolute],
> IIf([absolute]< =3,[prelim_plan_date]+[difference],[prel
im_plan_date]-0) AS
> Plan_Date, DateAdd("d",-25,[plan_Date]) AS ACS_Date,
> DateAdd("d",-30,[plan_date]) AS Supervisor_DueDate INTO tbl_FaspFinal
> FROM tbl_PreventionCases
> GROUP BY RTrim([tbl_PreventionCases]![SHR_last_na
me])+",
> " +RTrim([tbl_PreventionCases]![SHR_first_
name]), tbl_PreventionCases.CRS_C
ID
> HAVING
> (((((Round((6/1/2005-[tbl_PreventionCases]![CRS_cid])/182.625)+1)*182.625+
[tbl_PreventionCases]![CRS_cid]),0)
> Between 9/30/2005 And 11/30/2005));)
> --
> TS|||how can I attach something you can excute? I don't see an attach option
--
TS
"Tibor Karaszi" wrote:

> I found at least two things you need to change:
> IIF.
> No such animal in TSQL or ANSI SQL. Use CASE instead.
>
> Here you reference a column alias given by the prior column in the SELECT.
I.e., there is no column
> named difference in the table, at least I guess so). No can do (as per ANS
I SQL), everything
> "happens at once" in SQL.
> If you post something we can execute, we might be able to help you further
.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "TS" <TS@.discussions.microsoft.com> wrote in message
> news:56D23A37-C8CB-4C12-8630-0A22E96864DD@.microsoft.com...
>|||Not attach. Post the CREATE TABLE and INSERT statements we need to re-create
your scenario.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"TS" <TS@.discussions.microsoft.com> wrote in message
news:7A6145D0-7F41-466C-94BA-AC0D056A55F6@.microsoft.com...
> how can I attach something you can excute? I don't see an attach option
> --
> TS
>
> "Tibor Karaszi" wrote:
>|||Here is the create table statement and you have the statement for the query
I
want to run. Thanks a million
CREATE TABLE [dbo].[tbl_PreventionCases] (
[SHR_LAST_NAME] [nvarchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SHR_FIRST_NAME] [nvarchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CRS_CASE_NUMBER] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CRS_CID] [smalldatetime] NULL ,
[SHR_CASE_NAME] [nvarchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SHR_CIN] [nvarchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SHR_CHILD_DOB] [smalldatetime] NULL ,
[CRS_CASE_CITY] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CRS_CASE_STATE] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CRS_CASE_STREET] [nvarchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CRS_CASE_ZIP] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CHILD_ID] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
--
TS
"Tibor Karaszi" wrote:

> Not attach. Post the CREATE TABLE and INSERT statements we need to re-crea
te your scenario.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "TS" <TS@.discussions.microsoft.com> wrote in message
> news:7A6145D0-7F41-466C-94BA-AC0D056A55F6@.microsoft.com...
>|||I found several issues with the query. The basic problem is that Access does
n't follow ANSI SQL very
well, So you need to re-work some part and learn to recognize those parts to
port from Access to SQL
Server. I've did a little bit in your query, but as I didn't know the busine
ss requirements etc, I
can't do it all. Here's the current version (possibly some parenthesis misma
tch):
SELECT
SHR_Case_Name AS CaseName,
RTRIM(SHR_last_name)+", "+RTRIM(SHR_first_name) AS ChildName,
CRS_CID,
ROUND((6/1/2005-[CRS_cid])/182.625)+1) * 182.625 + [CRS_cid],) AS Prelim_Pla
n_Date,
DATEPART("d",[CRS_cid]) AS ciddate,
DATEPART("d",[prelim_plan_date]) AS pdate, [ciddate]-[pdate] AS difference,
ABS([difference]) AS [absolute],
CASE WHEN ABS([difference])<=3 THEN [prelim_plan_date]+[difference] ELSE [prelim_plan_date]-0 END AS
Plan_Date,
DATEADD("d",-25,[plan_Date]) AS ACS_Date,
DATEADD("d",-30,[plan_date]) AS Supervisor_DueDate INTO tbl_FaspFinal
FROM tbl_PreventionCases
GROUP BY RTRIM([tbl_PreventionCases]![SHR_last_na
me])+",
" +RTrim([tbl_PreventionCases]![SHR_first_
name]), tbl_PreventionCases.CRS_CID
HAVING
ROUND( ((((6/1/2005-[tbl_PreventionCases]![CRS_cid])
/182.625)+1)*182.625+[tbl_PreventionCases]![CRS_cid]),0) Between 9/30/2005 A
nd 11/30/2005)
Some comments:
Learn to format the query in a more readable manner. It will help you and ot
hers in the future.
Is 6/1/2005 a date?If so, it should be expressed ad '20050601'. See
http://www.karaszi.com/SQLServer/info_datetime.asp
What is [tbl_PreventionCases]![SHR_last_name]? There's no exclamation mark i
n the SQL language. Is
it a text box in a form? Is it a column in a table? If a column in a table,
then use
tblname.colname. If some GUI element, then you have to pass this in as a par
ameter to SQL Server.
SQL Server cannot access what is on the screen of a client application.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"TS" <TS@.discussions.microsoft.com> wrote in message
news:81B06B42-2C52-4DCA-B4DC-811112FDCE3A@.microsoft.com...
> Here is the create table statement and you have the statement for the quer
y I
> want to run. Thanks a million
> CREATE TABLE [dbo].[tbl_PreventionCases] (
> [SHR_LAST_NAME] [nvarchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SHR_FIRST_NAME] [nvarchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CRS_CASE_NUMBER] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CRS_CID] [smalldatetime] NULL ,
> [SHR_CASE_NAME] [nvarchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SHR_CIN] [nvarchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SHR_CHILD_DOB] [smalldatetime] NULL ,
> [CRS_CASE_CITY] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CRS_CASE_STATE] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CRS_CASE_STREET] [nvarchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CRS_CASE_ZIP] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CHILD_ID] [int] IDENTITY (1, 1) NOT NULL
> ) ON [PRIMARY]
> --
> TS
>
> "Tibor Karaszi" wrote:
>|||Thanks. There is a problem with the round function, it returns an error
message saying the round function requires 2 to 3 arguments.
--
TS
"Tibor Karaszi" wrote:

> I found several issues with the query. The basic problem is that Access do
esn't follow ANSI SQL very
> well, So you need to re-work some part and learn to recognize those parts
to port from Access to SQL
> Server. I've did a little bit in your query, but as I didn't know the busi
ness requirements etc, I
> can't do it all. Here's the current version (possibly some parenthesis mis
match):
> SELECT
> SHR_Case_Name AS CaseName,
> RTRIM(SHR_last_name)+", "+RTRIM(SHR_first_name) AS ChildName,
> CRS_CID,
> ROUND((6/1/2005-[CRS_cid])/182.625)+1) * 182.625 + [CRS_cid],) AS Prelim_P
lan_Date,
> DATEPART("d",[CRS_cid]) AS ciddate,
> DATEPART("d",[prelim_plan_date]) AS pdate, [ciddate]-[pdate] AS difference,
> ABS([difference]) AS [absolute],
> CASE WHEN ABS([difference])<=3 THEN [prelim_plan_date]+[difference] ELSE [prelim_plan_date]-0 END
AS
> Plan_Date,
> DATEADD("d",-25,[plan_Date]) AS ACS_Date,
> DATEADD("d",-30,[plan_date]) AS Supervisor_DueDate INTO tbl_FaspFinal
> FROM tbl_PreventionCases
> GROUP BY RTRIM([tbl_PreventionCases]![SHR_last_na
me])+",
> " +RTrim([tbl_PreventionCases]![SHR_first_
name]), tbl_PreventionCases.CRS_C
ID
> HAVING
> ROUND( ((((6/1/2005-[tbl_PreventionCases]![CRS_cid])
> /182.625)+1)*182.625+[tbl_PreventionCases]![CRS_cid]),0) Between 9/30/2005
And 11/30/2005)
> Some comments:
> Learn to format the query in a more readable manner. It will help you and
others in the future.
> Is 6/1/2005 a date?If so, it should be expressed ad '20050601'. See
> http://www.karaszi.com/SQLServer/info_datetime.asp
> What is [tbl_PreventionCases]![SHR_last_name]? There's no exclamation mark
in the SQL language. Is
> it a text box in a form? Is it a column in a table? If a column in a table
, then use
> tblname.colname. If some GUI element, then you have to pass this in as a p
arameter to SQL Server.
> SQL Server cannot access what is on the screen of a client application.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "TS" <TS@.discussions.microsoft.com> wrote in message
> news:81B06B42-2C52-4DCA-B4DC-811112FDCE3A@.microsoft.com...
>sql

querry question

I have created a small querry and for some reason my container (datalist) is not reconizing the company name This is also the first time that i have created a querry with two select statements in the sproc... Does it make a difference if there is two select statements in a querry?? When populating a datalist? Not sure if i need to seperate the two statements some kind of way.. Is this ok to use with datalist? Erik.. ALTER PROCEDURE Get_Information_For_Datalist_Global_ @.IDProduct AS INT, @.IDCompany AS INT AS SELECT Product.ProductName, Product.ProductID,Product.ProductImage, Product.ProductDescription, SprocsTable.* FROM Product INNER JOIN SprocsTable ON Product.ProductID = SprocsTable.ProductID WHERE Product.ProductID = @.IDProduct SELECT Company.CompanyID, Company.CompanyName FROM COMPANY WHERE COMPANY.COMPANYID = @.IDCompany

You cannot bind two queries to the same repeater. Using a DataReader bind the first result, and then call .NextResult and then bind the dataReader to a second repeater.

Queries run slower in 2000 than 7

I created a new SQL Server 2000 SP2 on a 4 processor machine wiht 2 gig of r
am. I moved the database from a SQL Server 7 SP2 box with only 2 processors
and 1 gig of RAM. I inherited a monster report that runs in 30 seconds or
less on the sql 7 box, but
takes a minute and a half on the new box. This report uses a tremendous amo
unt of temp tables and dynamic sql (as I said I inherited it). By using sp_
executesql instead of EXEC for the dynamic sql, i was able to get the report
to run in 55 seconds. How
ever, I am confused as to why it would run so much slower on a much larder b
ox with 2000, especially considering i was the only one on the 2000 box, and
the SQL 7 box has 400 users on it.
Any ideas?Did you remember to update statistics on all tables, preferably with the
FULLSCAN option?
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Tammy Moisan" <anonymous@.discussions.microsoft.com> wrote in message
news:6AA5B19E-7050-4FF7-914D-B0772F6E6ADC@.microsoft.com...
I created a new SQL Server 2000 SP2 on a 4 processor machine wiht 2 gig of
ram. I moved the database from a SQL Server 7 SP2 box with only 2
processors and 1 gig of RAM. I inherited a monster report that runs in 30
seconds or less on the sql 7 box, but takes a minute and a half on the new
box. This report uses a tremendous amount of temp tables and dynamic sql
(as I said I inherited it). By using sp_executesql instead of EXEC for the
dynamic sql, i was able to get the report to run in 55 seconds. However, I
am confused as to why it would run so much slower on a much larder box with
2000, especially considering i was the only one on the 2000 box, and the SQL
7 box has 400 users on it.
Any ideas?|||I just ran sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'. it took a
lmost 12 minutes to run and actually rhe query came back at 57 seconds inste
ad of 55. It is the same exact database file as sql 7. i am so confused.
i am supposed to be releasi
ng this server to productiona t the end of the week, but my queries are runn
ing slower. the whole justification for this purchase was to make things fa
ster and now i have no explanation why things are slower. any toehr ideas a
re greatly appreciated.|||Could you please list your hardware as well as where you placed your data
files on each server?
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Tammy Moisan" <anonymous@.discussions.microsoft.com> wrote in message
news:04C40E3B-AFD1-4862-8957-CA29F358B3FE@.microsoft.com...
I just ran sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'. it took
almost 12 minutes to run and actually rhe query came back at 57 seconds
instead of 55. It is the same exact database file as sql 7. i am so
confused. i am supposed to be releasing this server to productiona t the
end of the week, but my queries are running slower. the whole justification
for this purchase was to make things faster and now i have no explanation
why things are slower. any toehr ideas are greatly appreciated.|||Can you list your hardware and DB configuration? Especially the disk config.
Eric Li
SQL DBA
MCDBA
Tammy Moisan wrote:

> I just ran sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'. it took almost
12 minutes to run and actually rhe query came back at 57 seconds instead of 55. It
is the same exact database file as sql 7. i am so confused. i am supposed to be re
lea
sing this server to productiona t the end of the week, but my queries are running slower. t
he whole justification for this purchase was to make things faster and now i have no explana
tion why things are slower. any toehr ideas are greatly appreciated.
>|||Here’s the specifications for this server.
Compaq DL580 G2
4x 2800 MHz processors
2GB Ram – SQL is configured to dynamically use all of this except the last
128MB which is saved for the OS.
4GB Page file on C: drive
C: - 34 GB Local Mirror (OS and SQL binn files only)
E: - 34 GB Local Mirror (SQL Log Files Only)
F: - 200 GB SAN RAID 5 (SQL Data Files Only)
I: - 200 GB SAN RAID 5 (SQL Data Files or Application data staging area)
J: - 100 GB SAN RAID 5 (SQL Data Files or Application data staging area)|||Outside of using RAID0+1, instead of RAID 5, I'd expect this to be OK. What
did you have for hardware for your SQL 7 box? Also, for your particular
query, have you tried running off parallelism:
SELECT
*
FROM
MyTable
OPTION (MAXDOP 1)
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Tammy Moisan" <anonymous@.discussions.microsoft.com> wrote in message
news:BA36F91D-17C8-47A3-9E7F-C8DCBCC5F620@.microsoft.com...
Here’s the specifications for this server.
Compaq DL580 G2
4x 2800 MHz processors
2GB Ram – SQL is configured to dynamically use all of this except the last
128MB which is saved for the OS.
4GB Page file on C: drive
C: - 34 GB Local Mirror (OS and SQL binn files only)
E: - 34 GB Local Mirror (SQL Log Files Only)
F: - 200 GB SAN RAID 5 (SQL Data Files Only)
I: - 200 GB SAN RAID 5 (SQL Data Files or Application data staging area)
J: - 100 GB SAN RAID 5 (SQL Data Files or Application data staging area)|||What is your SQL 7 box config.?
Eric Li
SQL DBA
MCDBA
Tammy Moisan wrote:
> Here’s the specifications for this server.
> Compaq DL580 G2
> 4x 2800 MHz processors
> 2GB Ram – SQL is configured to dynamically use all of this except the la
st 128MB which is saved for the OS.
> 4GB Page file on C: drive
> C: - 34 GB Local Mirror (OS and SQL binn files only)
> E: - 34 GB Local Mirror (SQL Log Files Only)
> F: - 200 GB SAN RAID 5 (SQL Data Files Only)
> I: - 200 GB SAN RAID 5 (SQL Data Files or Application data staging area)
> J: - 100 GB SAN RAID 5 (SQL Data Files or Application data staging area)|||I do nto have so much info about this box, except that it is much smaller
2 1.2 ghz Processors
1 GIG Ram
Data files on d:\ with 500MB free
Log FIles on e:swap file on f:\ 250MB
I do not see why this matters, as it is a smaller box|||I just wanted to confirm that fact. Have you tried turning off parallelism
on the disaffected query?
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Tammy Moisan" <anonymous@.discussions.microsoft.com> wrote in message
news:A4598E2A-D769-41FB-8FDC-E3FDD1459B2C@.microsoft.com...
I do nto have so much info about this box, except that it is much smaller
2 1.2 ghz Processors
1 GIG Ram
Data files on d:\ with 500MB free
Log FIles on e:swap file on f:\ 250MB
I do not see why this matters, as it is a smaller box

Queries run slower in 2000 than 7

I created a new SQL Server 2000 SP2 on a 4 processor machine wiht 2 gig of ram. I moved the database from a SQL Server 7 SP2 box with only 2 processors and 1 gig of RAM. I inherited a monster report that runs in 30 seconds or less on the sql 7 box, but
takes a minute and a half on the new box. This report uses a tremendous amount of temp tables and dynamic sql (as I said I inherited it). By using sp_executesql instead of EXEC for the dynamic sql, i was able to get the report to run in 55 seconds. How
ever, I am confused as to why it would run so much slower on a much larder box with 2000, especially considering i was the only one on the 2000 box, and the SQL 7 box has 400 users on it.
Any ideas?
Did you remember to update statistics on all tables, preferably with the
FULLSCAN option?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Tammy Moisan" <anonymous@.discussions.microsoft.com> wrote in message
news:6AA5B19E-7050-4FF7-914D-B0772F6E6ADC@.microsoft.com...
I created a new SQL Server 2000 SP2 on a 4 processor machine wiht 2 gig of
ram. I moved the database from a SQL Server 7 SP2 box with only 2
processors and 1 gig of RAM. I inherited a monster report that runs in 30
seconds or less on the sql 7 box, but takes a minute and a half on the new
box. This report uses a tremendous amount of temp tables and dynamic sql
(as I said I inherited it). By using sp_executesql instead of EXEC for the
dynamic sql, i was able to get the report to run in 55 seconds. However, I
am confused as to why it would run so much slower on a much larder box with
2000, especially considering i was the only one on the 2000 box, and the SQL
7 box has 400 users on it.
Any ideas?
|||I just ran sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'. it took almost 12 minutes to run and actually rhe query came back at 57 seconds instead of 55. It is the same exact database file as sql 7. i am so confused. i am supposed to be releasi
ng this server to productiona t the end of the week, but my queries are running slower. the whole justification for this purchase was to make things faster and now i have no explanation why things are slower. any toehr ideas are greatly appreciated.
|||Could you please list your hardware as well as where you placed your data
files on each server?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Tammy Moisan" <anonymous@.discussions.microsoft.com> wrote in message
news:04C40E3B-AFD1-4862-8957-CA29F358B3FE@.microsoft.com...
I just ran sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'. it took
almost 12 minutes to run and actually rhe query came back at 57 seconds
instead of 55. It is the same exact database file as sql 7. i am so
confused. i am supposed to be releasing this server to productiona t the
end of the week, but my queries are running slower. the whole justification
for this purchase was to make things faster and now i have no explanation
why things are slower. any toehr ideas are greatly appreciated.
|||Can you list your hardware and DB configuration? Especially the disk config.
Eric Li
SQL DBA
MCDBA
Tammy Moisan wrote:

> I just ran sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'. it took almost 12 minutes to run and actually rhe query came back at 57 seconds instead of 55. It is the same exact database file as sql 7. i am so confused. i am supposed to be relea
sing this server to productiona t the end of the week, but my queries are running slower. the whole justification for this purchase was to make things faster and now i have no explanation why things are slower. any toehr ideas are greatly appreciated.
>
|||Here’s the specifications for this server.
Compaq DL580 G2
4x 2800 MHz processors
2GB Ram – SQL is configured to dynamically use all of this except the last 128MB which is saved for the OS.
4GB Page file on C: drive
C: - 34 GB Local Mirror (OS and SQL binn files only)
E: - 34 GB Local Mirror (SQL Log Files Only)
F: - 200 GB SAN RAID 5 (SQL Data Files Only)
I: - 200 GB SAN RAID 5 (SQL Data Files or Application data staging area)
J: - 100 GB SAN RAID 5 (SQL Data Files or Application data staging area)
|||Outside of using RAID0+1, instead of RAID 5, I'd expect this to be OK. What
did you have for hardware for your SQL 7 box? Also, for your particular
query, have you tried running off parallelism:
SELECT
*
FROM
MyTable
OPTION (MAXDOP 1)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Tammy Moisan" <anonymous@.discussions.microsoft.com> wrote in message
news:BA36F91D-17C8-47A3-9E7F-C8DCBCC5F620@.microsoft.com...
Here’s the specifications for this server.
Compaq DL580 G2
4x 2800 MHz processors
2GB Ram – SQL is configured to dynamically use all of this except the last
128MB which is saved for the OS.
4GB Page file on C: drive
C: - 34 GB Local Mirror (OS and SQL binn files only)
E: - 34 GB Local Mirror (SQL Log Files Only)
F: - 200 GB SAN RAID 5 (SQL Data Files Only)
I: - 200 GB SAN RAID 5 (SQL Data Files or Application data staging area)
J: - 100 GB SAN RAID 5 (SQL Data Files or Application data staging area)
|||What is your SQL 7 box config.?
Eric Li
SQL DBA
MCDBA
Tammy Moisan wrote:
> Here’s the specifications for this server.
> Compaq DL580 G2
> 4x 2800 MHz processors
> 2GB Ram – SQL is configured to dynamically use all of this except the last 128MB which is saved for the OS.
> 4GB Page file on C: drive
> C: - 34 GB Local Mirror (OS and SQL binn files only)
> E: - 34 GB Local Mirror (SQL Log Files Only)
> F: - 200 GB SAN RAID 5 (SQL Data Files Only)
> I: - 200 GB SAN RAID 5 (SQL Data Files or Application data staging area)
> J: - 100 GB SAN RAID 5 (SQL Data Files or Application data staging area)
|||I do nto have so much info about this box, except that it is much smaller
2 1.2 ghz Processors
1 GIG Ram
Data files on d:\ with 500MB free
Log FIles on e:swap file on f:\ 250MB
I do not see why this matters, as it is a smaller box
|||I just wanted to confirm that fact. Have you tried turning off parallelism
on the disaffected query?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Tammy Moisan" <anonymous@.discussions.microsoft.com> wrote in message
news:A4598E2A-D769-41FB-8FDC-E3FDD1459B2C@.microsoft.com...
I do nto have so much info about this box, except that it is much smaller
2 1.2 ghz Processors
1 GIG Ram
Data files on d:\ with 500MB free
Log FIles on e:swap file on f:\ 250MB
I do not see why this matters, as it is a smaller box

Queries returning Multiple instances of the same record

I am running SQL 2005 and have created a simple database for interfacing with Visual Web Dev 2005 and Visual Studio 2005. I noticed that my applications where returning multiple instances of the same records in the queries. I went back to the SQL server and created a query, and sure enough, I'm getting each record returned 3 times. Where do I start to resolve this issue?DId you have the records already duplicated in the database tables ? Or did you just made a mistake in your join, defining the wrong joined keys. THe best thing would be to provide some information like DDL and some sample data.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Jens,

My DB and query are much simpler than what you are imagining:

The DB Structure is:

<MemberID, Int,> - Primary Key Autoincrement

<FirstName, nvarchar(30),>

<LastName, nvarchar(30),>

<Salutation, nvarchar(20),>

<MemberType, nvarchar(20),>

<IsNeighbor, tinyint,>

<Title, nvarchar(30),>

<Address, nvarchar(60),>

<Address2, nvarchar(60),>

<City, nvarchar(30),>

<State, nvarchar(2),>

<Zip, nvarchar(9),>

<Phone, nvarchar(10),>

<Email, nvarchar(50),>

<DateJoined, datetime,>

<ExpirationDate, datetime,>

<SubMemberTo, int,>

<Fax, nvarchar(10),>

<Cellphone, nvarchar(10),>

The SELECT query is:

SELECT [MemberID]

,[FirstName]

,[LastName]

,[Salutation]

,[MemberType]

,[IsNeighbor]

,[Title]

,[Address]

,[Address2]

,[City]

,[State]

,[Zip]

,[Phone]

,[Email]

,[DateJoined]

,[ExpirationDate]

,[SubMemberTo]

,[Fax]

,[Cellphone]

FROM [FriendsSQL].[dbo].[Members]

WHERE FirstName = 'ANNE' and LastName = 'REIS'

Without the WHERE clause the query returns the entire DB without duplication, however, when the WHERE clause is included the output is:

13 ANNE REIS Anne Reis & Owen Boy Full Member 0 Environmental Coordinator

XXXX E KENILWORTH PL NULL MILWAUKEE WI 53202 4147370000

XXXXX@.PLANET-SAVE.COM 2005-11-01 00:00:00.000 NULL NULL NULL NULL

13 ANNE REIS Anne Reis & Owen Boy Full Member 0 Environmental Coordinator

XXXX E KENILWORTH PL NULL MILWAUKEE WI 53202 4147370000

XXXXX@.PLANET-SAVE.COM 2005-11-01 00:00:00.000 NULL NULL NULL NULL

13 ANNE REIS Anne Reis & Owen Boy Full Member 0 Environmental Coordinator

XXXX E KENILWORTH PL NULL MILWAUKEE WI 53202 4147370000

XXXXX@.PLANET-SAVE.COM 2005-11-01 00:00:00.000 NULL NULL NULL NULL

Notice that the single record is returned 3 times.

|||DOH! You were right. The records were duplicated. Apparently using the SET Insert Unique ON and not having the Primary Key set allowed the duplications. I've cleaned up the mess and I'll try not to shoot off any more toes. Sorry for the bother. I should have caught that one.

Queries are slow when accessed from remote machine

Hi,

I have succesfully created a Stored Procedure which runs under 2 seconds locally.

However when i run the same proc from another machine in the LAN, the response times vary from 5 sec to over 40 Secs and even occassionally times out.

My server is SQL 2005 Dev Edition (32 Bit) running on a Dual Core Box with 2GB memory.

Any Ideas why this would be happening?

Does the query return a lot of data? If so, it is very likely that network latency and bandwidth are the bottleneck, since the results have to be sent over the network back to the client.

How many rows are you returning? You can turn on Client Statistics in SSMS, and see how much data (in bytes) is being returned to the client (assuming you are calling the SP from SSMS on one machine, talking to a remote server).

|||

Thanks for the reply.

It is returning about 400k. But what is interesting is that, even that delay is not consistant ( from 5 sec to over 40sec, when i have run over 100 tests) The other machine is on the same lan with 100Mbps network card. I couldnt also see any significant rise in network utilization in both the machines

|||Did you ever resolve this? It really sounds like a network issue.|||Yes. It turns out that the SSRS was in a web farm scenario. I was checking only one server. duh!!!|||

Hi, me too have the same problem:

I made a migration from SQL2000 to SQL2005. Before migration, both IIS and SQL were on the same server and performance was good.

We decided to split application server (IIS) from db server (SQL). Now the wait time to display page is 5/10 more high.

We made migration of db (backup/restore, detach/attach), rebuilded indexes and compiled store procedure.

Both server are in the same LAN, switched to 1Gb

Some help?

Regards

vito

Queries are slow when accessed from remote machine

Hi,

I have succesfully created a Stored Procedure which runs under 2 seconds locally.

However when i run the same proc from another machine in the LAN, the response times vary from 5 sec to over 40 Secs and even occassionally times out.

My server is SQL 2005 Dev Edition (32 Bit) running on a Dual Core Box with 2GB memory.

Any Ideas why this would be happening?

Does the query return a lot of data? If so, it is very likely that network latency and bandwidth are the bottleneck, since the results have to be sent over the network back to the client.

How many rows are you returning? You can turn on Client Statistics in SSMS, and see how much data (in bytes) is being returned to the client (assuming you are calling the SP from SSMS on one machine, talking to a remote server).

|||

Thanks for the reply.

It is returning about 400k. But what is interesting is that, even that delay is not consistant ( from 5 sec to over 40sec, when i have run over 100 tests) The other machine is on the same lan with 100Mbps network card. I couldnt also see any significant rise in network utilization in both the machines

|||Did you ever resolve this? It really sounds like a network issue.|||Yes. It turns out that the SSRS was in a web farm scenario. I was checking only one server. duh!!!|||

Hi, me too have the same problem:

I made a migration from SQL2000 to SQL2005. Before migration, both IIS and SQL were on the same server and performance was good.

We decided to split application server (IIS) from db server (SQL). Now the wait time to display page is 5/10 more high.

We made migration of db (backup/restore, detach/attach), rebuilded indexes and compiled store procedure.

Both server are in the same LAN, switched to 1Gb

Some help?

Regards

vito

Qry with "OR" keyword assistance...

Is there a better more reliable or eaiser way to get what I'm looking for?
I want anything that was created after Jan. 1, 2003 that was declined, the
declined letter codes are as indicated, and created after Jan 1, 2003
Or, I want anything that was rated and created after Jan 1, 2003
Or, I want anything that was paid and created after Jan 1, 2005 and
(redundently) created after Jan 1, 2003
Or that was recently created in the most recent campaign occuring Sept 1, 20
05
and (redundently) created after Jan 1, 2003
where (c2.ltrcode in('4','13,'16')
or (c2.rated > '0')
or (isdate(c2.upremrecd = 1 and c1.createdt > '20050101')
or c1.createdt > '20050901')
and c1.createdt > '20030101'
TIA
JeffP...if you find this running slowly, you can use UNIONs to pull them all
together, e.g.
select <colum list>
from <tables>
where c1.createdt > '20030101'
and c2.ltrcode in ('4','13','16')
union
select <colum list>
from <tables>
where c1.createdt > '20030101'
and c2.rated > '0'
union
<etc>
JDP@.Work wrote:
> Is there a better more reliable or eaiser way to get what I'm looking for?
> I want anything that was created after Jan. 1, 2003 that was declined, the
> declined letter codes are as indicated, and created after Jan 1, 2003
> Or, I want anything that was rated and created after Jan 1, 2003
> Or, I want anything that was paid and created after Jan 1, 2005 and
> (redundently) created after Jan 1, 2003
> Or that was recently created in the most recent campaign occuring Sept 1,
2005
> and (redundently) created after Jan 1, 2003
> where (c2.ltrcode in('4','13,'16')
> or (c2.rated > '0')
> or (isdate(c2.upremrecd = 1 and c1.createdt > '20050101')
> or c1.createdt > '20050901')
> and c1.createdt > '20030101'
> TIA
> JeffP...
>

QI for IEnumVARIANT failed on the unmanaged server

I have an SSIS project created with Beta 2 of VS2005. When I try to open the SSIS Package in the Designer with the final version of VS2005 I get the error message:

Microsoft Visual Studio is unable to load this document
QI for IEnumVARIANT failed on the unmanaged server

Before that happens there is a warning Message: "Document contains one or more extremely long lines of text. These lines will cause the editor to respond slowly when you open the file. Do you still want to open the file?"

When click on Yes to this question the above Error occurs. In the Beta I got the same warning message, but the package was still able to load in the designer.

Is there a way to recover or migrate my package so that I can use it in the final VS2005?

Torsten

I get a very similar problem. I'm just starting on my learning curve for SSIS and am trying to create a new package using the SQL Server Import Export Wizard. The wizard fails and reports the exact same error you mention.|||

Possible problem is InstallShield. Here is their solution: http://support.installshield.com/kb/view.asp?articleid=q106194

|||Bien!
Esa es la solución.

Recomendación: No usar InstallShield, un fallo como este es imperdonable

Prueba y aprende NSIS|||

RoMoVi wrote:

Bien!
Esa es la solución.

Recomendación: No usar InstallShield, un fallo como este es imperdonable

Prueba y aprende NSIS

These are English based forums. Please avoid the use of Spanish.

I don't believe NSIS is a solution to the InstallShield problem as a simple upgrade to InstallShield should suffice.

QI for IEnumVARIANT failed on the unmanaged server

I have an SSIS project created with Beta 2 of VS2005. When I try to open the SSIS Package in the Designer with the final version of VS2005 I get the error message:

Microsoft Visual Studio is unable to load this document
QI for IEnumVARIANT failed on the unmanaged server

Before that happens there is a warning Message: "Document contains one or more extremely long lines of text. These lines will cause the editor to respond slowly when you open the file. Do you still want to open the file?"

When click on Yes to this question the above Error occurs. In the Beta I got the same warning message, but the package was still able to load in the designer.

Is there a way to recover or migrate my package so that I can use it in the final VS2005?

Torsten

I get a very similar problem. I'm just starting on my learning curve for SSIS and am trying to create a new package using the SQL Server Import Export Wizard. The wizard fails and reports the exact same error you mention.|||

Possible problem is InstallShield. Here is their solution: http://support.installshield.com/kb/view.asp?articleid=q106194

|||Bien!
Esa es la solución.

Recomendación: No usar InstallShield, un fallo como este es imperdonable

Prueba y aprende NSIS|||

RoMoVi wrote:

Bien!
Esa es la solución.

Recomendación: No usar InstallShield, un fallo como este es imperdonable

Prueba y aprende NSIS

These are English based forums. Please avoid the use of Spanish.

I don't believe NSIS is a solution to the InstallShield problem as a simple upgrade to InstallShield should suffice.

QI for IEnumVARIANT failed on the unmanaged server

I have an SSIS project created with Beta 2 of VS2005. When I try to open the SSIS Package in the Designer with the final version of VS2005 I get the error message:

Microsoft Visual Studio is unable to load this document
QI for IEnumVARIANT failed on the unmanaged server

Before that happens there is a warning Message: "Document contains one or more extremely long lines of text. These lines will cause the editor to respond slowly when you open the file. Do you still want to open the file?"

When click on Yes to this question the above Error occurs. In the Beta I got the same warning message, but the package was still able to load in the designer.

Is there a way to recover or migrate my package so that I can use it in the final VS2005?

Torsten

I get a very similar problem. I'm just starting on my learning curve for SSIS and am trying to create a new package using the SQL Server Import Export Wizard. The wizard fails and reports the exact same error you mention.|||

Possible problem is InstallShield. Here is their solution: http://support.installshield.com/kb/view.asp?articleid=q106194

|||Bien!
Esa es la solución.

Recomendación: No usar InstallShield, un fallo como este es imperdonable

Prueba y aprende NSIS|||

RoMoVi wrote:

Bien!
Esa es la solución.

Recomendación: No usar InstallShield, un fallo como este es imperdonable

Prueba y aprende NSIS

These are English based forums. Please avoid the use of Spanish.

I don't believe NSIS is a solution to the InstallShield problem as a simple upgrade to InstallShield should suffice.

QI for IEnumVARIANT failed on the unmanaged server

I have an SSIS project created with Beta 2 of VS2005. When I try to open the SSIS Package in the Designer with the final version of VS2005 I get the error message:

Microsoft Visual Studio is unable to load this document
QI for IEnumVARIANT failed on the unmanaged server

Before that happens there is a warning Message: "Document contains one or more extremely long lines of text. These lines will cause the editor to respond slowly when you open the file. Do you still want to open the file?"

When click on Yes to this question the above Error occurs. In the Beta I got the same warning message, but the package was still able to load in the designer.

Is there a way to recover or migrate my package so that I can use it in the final VS2005?

Torsten

I get a very similar problem. I'm just starting on my learning curve for SSIS and am trying to create a new package using the SQL Server Import Export Wizard. The wizard fails and reports the exact same error you mention.|||

Possible problem is InstallShield. Here is their solution: http://support.installshield.com/kb/view.asp?articleid=q106194

|||Bien!
Esa es la solución.

Recomendación: No usar InstallShield, un fallo como este es imperdonable

Prueba y aprende NSIS|||

RoMoVi wrote:

Bien!
Esa es la solución.

Recomendación: No usar InstallShield, un fallo como este es imperdonable

Prueba y aprende NSIS

These are English based forums. Please avoid the use of Spanish.

I don't believe NSIS is a solution to the InstallShield problem as a simple upgrade to InstallShield should suffice.

Qery Analyser - Table Access

Sql Server 2000, Win 2000
Problem accessing tables in SQL Query Analyser:-
? why can't I access the database connected as Jim ?
Created Database [SolutionsNet] - Owner Jim
Created Table [Jim.Customers] Owner Jim
Data base access Jim 'permit'
Table - Companies - permissions Jim > Select . . . [the lot]
Jim Permissions - Table Companies > Select . . . [the lot]
Jim - permit in database role > public, db_owners . . [the lot]
BUT
SQL Query Analyser
Connect as Jim
use SolutionsNet
go
select * from jim.companies
go
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet',
owner 'Jim'.
[same if table is just 'companies' withot the prefix]
HOWEVER
Connect as sa
use SolutionsNet
go
select * from jim.companies
go
Fine:- displays the empty table!
? why can't I access the data connected as Jim ?
Jim Bunton
283 968
Jim Bunton wrote:
> Sql Server 2000, Win 2000
> Problem accessing tables in SQL Query Analyser:-
> ? why can't I access the database connected as Jim ?
> Created Database [SolutionsNet] - Owner Jim
> Created Table [Jim.Customers] Owner Jim
> Data base access Jim 'permit'
> Table - Companies - permissions Jim > Select . . . [the lot]
> Jim Permissions - Table Companies > Select . . . [the lot]
> Jim - permit in database role > public, db_owners . . [the lot]
> BUT
> SQL Query Analyser
> Connect as Jim
> use SolutionsNet
> go
> select * from jim.companies
> go
> Server: Msg 229, Level 14, State 5, Line 1
> SELECT permission denied on object 'Companies', database
> 'SolutionsNet', owner 'Jim'.
> [same if table is just 'companies' withot the prefix]
> HOWEVER
> Connect as sa
> use SolutionsNet
> go
> select * from jim.companies
> go
> Fine:- displays the empty table!
> ? why can't I access the data connected as Jim ?
>
> Jim Bunton
> 283 968
Because you created a table called [Jim.Customers]. What you want is a
table called [Jim].[Customers].
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Hi,
Table name is created as [Jim.Customers].
So you have to use :-
Select * from jim.[Jim.Customers]
Thanks
Hari
SQL Server MVP
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message
news:4pw1f.77602$iW5.66654@.fe3.news.blueyonder.co. uk...
> Sql Server 2000, Win 2000
> Problem accessing tables in SQL Query Analyser:-
> ? why can't I access the database connected as Jim ?
> Created Database [SolutionsNet] - Owner Jim
> Created Table [Jim.Customers] Owner Jim
> Data base access Jim 'permit'
> Table - Companies - permissions Jim > Select . . . [the lot]
> Jim Permissions - Table Companies > Select . . . [the lot]
> Jim - permit in database role > public, db_owners . . [the lot]
> BUT
> SQL Query Analyser
> Connect as Jim
> use SolutionsNet
> go
> select * from jim.companies
> go
> Server: Msg 229, Level 14, State 5, Line 1
> SELECT permission denied on object 'Companies', database 'SolutionsNet',
> owner 'Jim'.
> [same if table is just 'companies' withot the prefix]
> HOWEVER
> Connect as sa
> use SolutionsNet
> go
> select * from jim.companies
> go
> Fine:- displays the empty table!
> ? why can't I access the data connected as Jim ?
>
> Jim Bunton
> 283 968
>

Q: sum of defined field

Hello,
I created a column and create a field: myField that does some calculation. I
was trying to do Sum(ReportItems!myField.Vlaue) in the group line, this does
not sum details. How can I sum it?
Thanks,
Jim.Not sure what you mean by "does not sum details" but you will have to
duplicate at the group level any calculations you perform at the detail
level. For example, if at the detail level you have
"=ReportItems!myField.Vlaue*2" then at the group level, you would need
"=Sum(ReportItems!myField.Vlaue)*2"
"JIM.H." wrote:
> Hello,
> I created a column and create a field: myField that does some calculation. I
> was trying to do Sum(ReportItems!myField.Vlaue) in the group line, this does
> not sum details. How can I sum it?
> Thanks,
> Jim.
>|||I have this in one of the columns at the second group level. I named the
field DepTotal
=ReportItems!Dep1.Value + ReportItems!Dep2.Value ReportItems!Dep3.Value I do
not have problem at this level, I get my number.
I added this in the first group level in the same column.
=Sum(ReportItems!DepTotal.Value)
It does not compile and it says aggregate function can be used only on
reports items contained in page headers and footers. This column has value in
only second group level, there is no detail data, might it be the problem?
"CGW" wrote:
> Not sure what you mean by "does not sum details" but you will have to
> duplicate at the group level any calculations you perform at the detail
> level. For example, if at the detail level you have
> "=ReportItems!myField.Vlaue*2" then at the group level, you would need
> "=Sum(ReportItems!myField.Vlaue)*2"
> "JIM.H." wrote:
> > Hello,
> > I created a column and create a field: myField that does some calculation. I
> > was trying to do Sum(ReportItems!myField.Vlaue) in the group line, this does
> > not sum details. How can I sum it?
> > Thanks,
> > Jim.
> >|||When you say you named the field Dep Total, do you mean you named the textbox
that?
I believe you'll get the figure you're wanting for the second group level if
you use
=Sum(ReportItems!Dep1.Value + ReportItems!Dep2.Value +
ReportItems!Dep3.Value), or, if the groups are different from what I
understand them to be...
=Sum(ReportItems!Dep1.Value) + sum(ReportItems!Dep2.Value) +
Sum(ReportItems!Dep3.Value),
"JIM.H." wrote:
> I have this in one of the columns at the second group level. I named the
> field DepTotal
> =ReportItems!Dep1.Value + ReportItems!Dep2.Value ReportItems!Dep3.Value I do
> not have problem at this level, I get my number.
> I added this in the first group level in the same column.
> =Sum(ReportItems!DepTotal.Value)
> It does not compile and it says aggregate function can be used only on
> reports items contained in page headers and footers. This column has value in
> only second group level, there is no detail data, might it be the problem?
>
> "CGW" wrote:
> > Not sure what you mean by "does not sum details" but you will have to
> > duplicate at the group level any calculations you perform at the detail
> > level. For example, if at the detail level you have
> > "=ReportItems!myField.Vlaue*2" then at the group level, you would need
> > "=Sum(ReportItems!myField.Vlaue)*2"
> >
> > "JIM.H." wrote:
> >
> > > Hello,
> > > I created a column and create a field: myField that does some calculation. I
> > > was trying to do Sum(ReportItems!myField.Vlaue) in the group line, this does
> > > not sum details. How can I sum it?
> > > Thanks,
> > > Jim.
> > >|||yes, when I go to properties of textbox I see DepTotal and thsi is group
level 2. No in group level 1, I need to sum all group level 2 DepTotal
values. I put Sum in group level 2 like =Sum(ReportItems!Dep1.Value +
ReportItems!Dep2.Value + ReportItems!Dep3.Value) and put this into grpup
level 1 too. I still get the same message for Dep1,2,3.
"CGW" wrote:
> When you say you named the field Dep Total, do you mean you named the textbox
> that?
> I believe you'll get the figure you're wanting for the second group level if
> you use
> =Sum(ReportItems!Dep1.Value + ReportItems!Dep2.Value +
> ReportItems!Dep3.Value), or, if the groups are different from what I
> understand them to be...
> =Sum(ReportItems!Dep1.Value) + sum(ReportItems!Dep2.Value) +
> Sum(ReportItems!Dep3.Value),
>
> "JIM.H." wrote:
> > I have this in one of the columns at the second group level. I named the
> > field DepTotal
> > =ReportItems!Dep1.Value + ReportItems!Dep2.Value ReportItems!Dep3.Value I do
> > not have problem at this level, I get my number.
> >
> > I added this in the first group level in the same column.
> > =Sum(ReportItems!DepTotal.Value)
> >
> > It does not compile and it says aggregate function can be used only on
> > reports items contained in page headers and footers. This column has value in
> > only second group level, there is no detail data, might it be the problem?
> >
> >
> >
> > "CGW" wrote:
> >
> > > Not sure what you mean by "does not sum details" but you will have to
> > > duplicate at the group level any calculations you perform at the detail
> > > level. For example, if at the detail level you have
> > > "=ReportItems!myField.Vlaue*2" then at the group level, you would need
> > > "=Sum(ReportItems!myField.Vlaue)*2"
> > >
> > > "JIM.H." wrote:
> > >
> > > > Hello,
> > > > I created a column and create a field: myField that does some calculation. I
> > > > was trying to do Sum(ReportItems!myField.Vlaue) in the group line, this does
> > > > not sum details. How can I sum it?
> > > > Thanks,
> > > > Jim.
> > > >|||The same sum(whatever) in each group should yield sums appropriate for that
group. If you get the same total in each group, then the groups are
identical. You might check your group definitions in the properities of the
table to make sure you're grouping distinctly.
"JIM.H." wrote:
> yes, when I go to properties of textbox I see DepTotal and thsi is group
> level 2. No in group level 1, I need to sum all group level 2 DepTotal
> values. I put Sum in group level 2 like =Sum(ReportItems!Dep1.Value +
> ReportItems!Dep2.Value + ReportItems!Dep3.Value) and put this into grpup
> level 1 too. I still get the same message for Dep1,2,3.
>
> "CGW" wrote:
> > When you say you named the field Dep Total, do you mean you named the textbox
> > that?
> >
> > I believe you'll get the figure you're wanting for the second group level if
> > you use
> >
> > =Sum(ReportItems!Dep1.Value + ReportItems!Dep2.Value +
> > ReportItems!Dep3.Value), or, if the groups are different from what I
> > understand them to be...
> > =Sum(ReportItems!Dep1.Value) + sum(ReportItems!Dep2.Value) +
> > Sum(ReportItems!Dep3.Value),
> >
> >
> > "JIM.H." wrote:
> >
> > > I have this in one of the columns at the second group level. I named the
> > > field DepTotal
> > > =ReportItems!Dep1.Value + ReportItems!Dep2.Value ReportItems!Dep3.Value I do
> > > not have problem at this level, I get my number.
> > >
> > > I added this in the first group level in the same column.
> > > =Sum(ReportItems!DepTotal.Value)
> > >
> > > It does not compile and it says aggregate function can be used only on
> > > reports items contained in page headers and footers. This column has value in
> > > only second group level, there is no detail data, might it be the problem?
> > >
> > >
> > >
> > > "CGW" wrote:
> > >
> > > > Not sure what you mean by "does not sum details" but you will have to
> > > > duplicate at the group level any calculations you perform at the detail
> > > > level. For example, if at the detail level you have
> > > > "=ReportItems!myField.Vlaue*2" then at the group level, you would need
> > > > "=Sum(ReportItems!myField.Vlaue)*2"
> > > >
> > > > "JIM.H." wrote:
> > > >
> > > > > Hello,
> > > > > I created a column and create a field: myField that does some calculation. I
> > > > > was trying to do Sum(ReportItems!myField.Vlaue) in the group line, this does
> > > > > not sum details. How can I sum it?
> > > > > Thanks,
> > > > > Jim.
> > > > >

Q: Report Parameter dropdown

Hello,
I need to create a MyPar parameter dropdown list, that is why I created a
new dataset that gets data through a stored procedure. I also add @.MyPar into
the query string of MyMainDataSet . I defined this parameter in Parameter
screen for both available and default values.
When I run the report, I get the following error.
Query execution failed for data set MyMainDataSet.
Must declare the variable @.MyPar
What is problem?You may have to update the data set directly.. Ensure you are using the
correct case for everything...
right click the ellipsis on the data tab after selecting the data set, go to
the parameters tab, and manually add the parameter...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:058B7DC5-6B26-48CC-8F88-6B6E41BAFC59@.microsoft.com...
> Hello,
> I need to create a MyPar parameter dropdown list, that is why I created a
> new dataset that gets data through a stored procedure. I also add @.MyPar
> into
> the query string of MyMainDataSet . I defined this parameter in Parameter
> screen for both available and default values.
> When I run the report, I get the following error.
> Query execution failed for data set MyMainDataSet.
> Must declare the variable @.MyPar
> What is problem?
>