I/O Read/Write Performance Problem

We have an ETL job occasionally encounters performance issue. Today, for
example, it creates several locks in SQL Server, and its I/O Read Bytes (from
Task Manager) are > 3,746,000,000,000 and I/O Write Bytes are >
2,268,000,000,000 and still increasing. The ETL has run for over 4 hours (as
opposed to 40 minutes usually). If the similar scenario happens to you, what
would you do immediately?
I am thinking of implemeting table hints when reviewing the queries in it,
what else can we further improve for long term solutions?Hi,
I would suggest that you place a Sql Trace and a Performance monitor on the
job to determine why it is taking so long. There could be a multitude of
reasons that the job is taking so long from an improperly joined table to a
disk failure in the RAID array. I have posted a couple of links to help you
set up the perfmon and the profiler to aid in your diagnosis.
Hope this helps.
--
http://support.microsoft.com/default.aspx?scid=kb;EN-US;224453
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q224587
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q243589

i'm sorry man no was my intention

i′m sorry my bad english

i have a dts in sql server 2005

for example in sql server 2000 i can do that :

over DTS right-clik choose option "Scheduled pachage" and this create a scheduled what i mean is that this produces a "JOB"

what i mean is that i need to know how can to create a scheduled to DTS′s in sql 2005

Please anyone of you provide some solution for this.

Okay, I completely misunderstood what you wanted before. But, I think I can help you now.

Once you have the SSIS package (DTS packages mean something else - like packages from SQL SERVER 2000 DTS) you can use SQL Server Agent to shedule the package to be run at a certain time.

Use the SQL Server Agent to schedule your package.

|||

sorry man:

what i do in sql server 2005 is:

right-click over SQL Server Agent , choose the option "New" and choose option "Schedule" but isn't what i want, may be i am not specfic

when i do right-click over dts in sql server 2000 and choose the option schedule package this produces an scheduled and i can see it in the part of the jobs

Now how i can do that in sql server 2005? i need to see of my DTSs the jobs that i created and you said me that i can do by SQL Server Agent in SQL server 2005 and this isn′t do what i want.

Please anyone of you provide some solution for this.
Thanks :)

|||

limon26,

I merged this post into your earlier thread. If you want to reply to another reply you should use the reply button and not the new thread button. This will keep the discussion in one thread and others that can help will be able to follow the discussion more easy.

Thanks.

|||

i apologies for that

so what can i do about my DTSs in sql server 2005 ?

|||

I am not that experienced with SSIS for 2005, hopefully swells or somebody else will be able to help you further...

|||

In SQL Server 2000, you could create a schedule by right-clicking on a package in Enteprise Manager and selecting "Schedule Package." SQL Server 2000 created the SQL Server Agent job. You could find the jobs in Enterprise Manager under the Management folder, in the jobs tree.

In SQL Server 2005 there are some changes ...

1. SQL Server Agent is not automatically enabled. Make sure the service has been started.

2. You do not automatically create scheduled jobs for SSIS. You should right click on the SQL Server Agent icon in the SQL Server Object Explorer in the Management Studio and select to create a new Agent Job. When you do that, you can add a new job step to execute an SSIS package. You can also add a new schedule.

The end result is similar to 2000 - jobs are listed under the SQL Server Agent node; jobs have steps - one step executes a package; and jobs have schedules.

An important difference is that SSIS now has an Agent subsytem for more better integrated execution.

hth

Donald

|||

After create your ssis package. Here is step you have to do to make a job run....

My ssis is running every 10 min.....

Here is what I do to make it work.......

Create the proxy account, And SQL Server Agent running under your NT Acount and then the job Steps running under the "SQL Agent Service Account" and your nt account as the owner of the job. It will works perfect.

I don't know if you know how to create proxy account. Here is how to create:

Use master

CREATE CREDENTIAL [MyCredential] WITH IDENTITY = 'yourdomain\myWindowAccount', secret = 'WindowLoginPassword'

Use msdb

Sp_add_proxy @.proxy_name='MyProxy', @.credential_name='MyCredential'

Sp_grant_login_to_proxy @.login_name=' devlogin', @.proxy_name='MyProxy'

Sp_grant_proxy_to_subsystem @.proxy_name='MyProxy', @.subsystem_name='SSIS'

Use master

CREATE CREDENTIAL [MyCredential] WITH IDENTITY = 'yourdomain\myWindowAccount', secret = 'WindowLoginPassword'

Use msdb

Sp_add_proxy @.proxy_name='MyProxy', @.credential_name='MyCredential'

Sp_grant_login_to_proxy @.login_name=' devlogin', @.proxy_name='MyProxy'

Sp_grant_proxy_to_subsystem @.proxy_name='MyProxy', @.subsystem_name='SSIS'

kenny

|||Similar problems, but (so far) the credentials/proxy doesn't seem to resolve it.
We have two people who have developed some SSIS packages, myself and another developer. The other developer's SSIS package runs fine from BIDS, DTexec and DTexecUI, but not from SQL Server agent. My SSIS package runs fine from BIDS, DTexec, DTexecUI and local SQLServer Agent. I haven't had the opportunity to have it deplyed into another environment to check if I have problems from the server.
What was interesting to me, in attempting to test the credentials, was that the working SQL Server Agent job, it I edit it to use the proxy, fails. If I edit the job back to using the SQL Agent Service Account (on the RUN AS tab on the task), then it works successfully.
Now this may be because I've set up the credential incorrectly, since it was my first time (be gentle :-)). I'm going to try and modify the script above and see if that works any better. The credential I set up used a local user I created that was part of the powerusers group. The package does file operations locally (based on the current configuration file). The 'ProtectLevel' was set to 'EncryptSenstitiveWithPassword', and this specific test job is using SQL Server as the package source.
Nay help? The KB http://support.microsoft.com/kb/918760 states that this is ' This behavior is by design.' - but surely it should also be easy to use?
|||using the scipr (after removing the duplicate, and adding exec's), I generated a proxy and a credential. The user being used is the same one I create earlier (which is a member of the power user group on my laptop). I get the following error:
-
Date 25/07/2006 14:21:59
Log Job History (Test Excel Orchestration)
Step ID 1
Server MyLaptop\SQL2005_SP1_TEST
Job Name Test Excel Orchestration
Step Name Invoke Excel Orchestration
Duration 00:00:01
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: MyLaptop\SSISAgentJobCredUser. The package could not be loaded. The step failed.
-
this proves the credential at least worked - it did try to run with the correct user, but still no joy.
.... even less joy now, since I now get the following, trying to use this in the way that worked before:

Date 25/07/2006 14:27:45
Log Job History (Test Excel Orchestration)
Step ID 1
Server MyLaptop\RGALBRAITH\SQL2005_SP1_TEST
Job Name Test Excel Orchestration
Step Name Invoke Excel Orchestration
Duration 00:00:00
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account. The step failed.

|||

Hey,

Today I got the same error as you, to fixed I didn't create the credentials, I changed the owner for the Scheduled Jobs instead, and it's works great!

|||

I fixed this by adding the owner of the package to the owned schema's in the agent roles in msdb.

take a look at this link:

http://www.microsoft.com/technet/prodtechnol/sql/2005/newsqlagent.mspx

I wonder if these ADO constants really work at all

For example why doesn't this give an error?
In a ASP:
<%@.language=3D"VBScript"%>
<%
'...
sSQL =3D "SELECT CustomerID, CompanyName, " & _=20
"ContactName, Country FROM Customers"
Set rs =3D Server.CreateObject("ADODB.Recordset")
rs.CursorType =3D adOpenForwardOnly
rs.Open sSQl, Application("DBconn")
rs.MoveNext
%>
<!-- HTML stuff -->
<%
rs.MoveFirst
'...
%>
<!-- HTML and ASP stuff -->
SQL 2000 SP3 ADO 2.7 SP(not sure) Windows 2000 SP3
--=20
George Hester
__________________________________
What error were you expecting?
http://www.aspfaq.com/
(Reverse address to reply.)
"George Hester" <hesterloli@.hotmail.com> wrote in message
news:%23uESkwAXEHA.2908@.TK2MSFTNGP10.phx.gbl...
For example why doesn't this give an error?
|||> What error were you expecting?
About using forward only cursor backward.
I have seen an error in similar situation, but can't verify this now
|||Right. What I provided should have resulted in a error but did not. =
The adForwardOnly constant is NOT working. rs.MoveNext followed by =
rs.MoveFirst should have resulted in a error at least that is my =
understanding of a Recordset opened with that cursor type =
adForwardOnly..
--=20
George Hester
__________________________________
"Bojidar Alexandrov" <bojo_do_no_spam@.kodar.net> wrote in message =
news:OhiQ$IGXEHA.128@.TK2MSFTNGP10.phx.gbl...
>=20
> About using forward only cursor backward.
>=20
> I have seen an error in similar situation, but can't verify this now
>=20
>
|||I'm not going to presume to know the reason(s) why, but MoveFirst() allows
you to just start over. The problem you will have with certain cursor types
is that you will not be able to move backwards, e.g. MovePrevious(). Also,
try a MoveLast() then a MoveFirst().
http://www.aspfaq.com/
(Reverse address to reply.)
"George Hester" <hesterloli@.hotmail.com> wrote in message
news:ud6NnQKXEHA.1652@.TK2MSFTNGP09.phx.gbl...
Right. What I provided should have resulted in a error but did not. The
adForwardOnly constant is NOT working. rs.MoveNext followed by rs.MoveFirst
should have resulted in a error at least that is my understanding of a
Recordset opened with that cursor type adForwardOnly..
|||On Sun, 27 Jun 2004 02:01:06 -0400, "George Hester"
<hesterloli@.hotmail.com> wrote:
>For example why doesn't this give an error?
My experience with ADO is that if you give a set of parameters that is
supposed to work, it will work, and if you give a set of parameters
that is an any way faulty, results are unpredictable in advance and
error messages unlikely.
I suspect this is pretty much as far as Microsoft's design and
development of ADO reaches.
J.
|||On Sun, 27 Jun 2004 02:01:06 -0400, "George Hester"
<hesterloli@.hotmail.com> wrote:
>For example why doesn't this give an error?
My experience with ADO is that if you give a set of parameters that is
supposed to work, it will work, and if you give a set of parameters
that is an any way faulty, results are unpredictable in advance and
error messages unlikely.
I suspect this is pretty much as far as Microsoft's design and
development of ADO reaches.
J.
|||Aaron I am not sure I follow what you were saying. Are you saying that =
what I presented is not supposed to give an error? If that is the case =
could you please clarify if setting the cursor as adOpenForwardOnly and =
then moving the Recordset rs.MoveNext and later rs.MoveFirst is not =
contradicting the definition of adOpenForwardOnly? Because I seem to =
misunderstand the definition of adOpenForwardOnly then. Thanks.
--=20
George Hester
__________________________________
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message =
news:OmdlQpRXEHA.3168@.TK2MSFTNGP10.phx.gbl...
> I'm not going to presume to know the reason(s) why, but MoveFirst() =
allows
> you to just start over. The problem you will have with certain cursor =
types
> is that you will not be able to move backwards, e.g. MovePrevious(). =
Also,
> try a MoveLast() then a MoveFirst().
>=20
> --=20
> http://www.aspfaq.com/
> (Reverse address to reply.)
>=20
>=20
>=20
>=20
> "George Hester" <hesterloli@.hotmail.com> wrote in message
> news:ud6NnQKXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> Right. What I provided should have resulted in a error but did not. =
The
> adForwardOnly constant is NOT working. rs.MoveNext followed by =
rs.MoveFirst
> should have resulted in a error at least that is my understanding of a
> Recordset opened with that cursor type adForwardOnly..
>=20
>
|||Hi Aaron. I changed rs.MoveFirst to rs.MovePrevious and got the error =
as expected. "Operation is not allowed in this context." Well that's =
good. I guess rs.MoveFirst after one rs.MoveNext is different then =
rs.MovePrevious after one rs.MoveNext. Because the later errors but the =
former does not. Mmmm...
--=20
George Hester
__________________________________
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message =
news:OmdlQpRXEHA.3168@.TK2MSFTNGP10.phx.gbl...
> I'm not going to presume to know the reason(s) why, but MoveFirst() =
allows
> you to just start over. The problem you will have with certain cursor =
types
> is that you will not be able to move backwards, e.g. MovePrevious(). =
Also,
> try a MoveLast() then a MoveFirst().
>=20
> --=20
> http://www.aspfaq.com/
> (Reverse address to reply.)
>=20
>=20
>=20
>=20
> "George Hester" <hesterloli@.hotmail.com> wrote in message
> news:ud6NnQKXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> Right. What I provided should have resulted in a error but did not. =
The
> adForwardOnly constant is NOT working. rs.MoveNext followed by =
rs.MoveFirst
> should have resulted in a error at least that is my understanding of a
> Recordset opened with that cursor type adForwardOnly..
>=20
>
|||Hi Aaron. I changed rs.MoveFirst to rs.MovePrevious and got the error =
as expected. "Operation is not allowed in this context." Well that's =
good. I guess rs.MoveFirst after one rs.MoveNext is different then =
rs.MovePrevious after one rs.MoveNext. Because the later errors but the =
former does not. Mmmm...
--=20
George Hester
__________________________________
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message =
news:OmdlQpRXEHA.3168@.TK2MSFTNGP10.phx.gbl...
> I'm not going to presume to know the reason(s) why, but MoveFirst() =
allows
> you to just start over. The problem you will have with certain cursor =
types
> is that you will not be able to move backwards, e.g. MovePrevious(). =
Also,
> try a MoveLast() then a MoveFirst().
>=20
> --=20
> http://www.aspfaq.com/
> (Reverse address to reply.)
>=20
>=20
>=20
>=20
> "George Hester" <hesterloli@.hotmail.com> wrote in message
> news:ud6NnQKXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> Right. What I provided should have resulted in a error but did not. =
The
> adForwardOnly constant is NOT working. rs.MoveNext followed by =
rs.MoveFirst
> should have resulted in a error at least that is my understanding of a
> Recordset opened with that cursor type adForwardOnly..
>=20
>
sql

I wonder if these ADO constants really work at all

For example why doesn't this give an error?
In a ASP:
<%@.language=3D"VBScript"%>
<%
'...
sSQL =3D "SELECT CustomerID, CompanyName, " & _ "ContactName, Country FROM Customers"
Set rs =3D Server.CreateObject("ADODB.Recordset")
rs.CursorType =3D adOpenForwardOnly
rs.Open sSQl, Application("DBconn")
rs.MoveNext
%>
<!-- HTML stuff -->
<%
rs.MoveFirst
'...
%>
<!-- HTML and ASP stuff -->
SQL 2000 SP3 ADO 2.7 SP(not sure) Windows 2000 SP3
-- George Hester
__________________________________What error were you expecting?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"George Hester" <hesterloli@.hotmail.com> wrote in message
news:%23uESkwAXEHA.2908@.TK2MSFTNGP10.phx.gbl...
For example why doesn't this give an error?|||> What error were you expecting?
About using forward only cursor backward.
I have seen an error in similar situation, but can't verify this now|||Right. What I provided should have resulted in a error but did not. =The adForwardOnly constant is NOT working. rs.MoveNext followed by =rs.MoveFirst should have resulted in a error at least that is my =understanding of a Recordset opened with that cursor type =adForwardOnly..
-- George Hester
__________________________________
"Bojidar Alexandrov" <bojo_do_no_spam@.kodar.net> wrote in message =news:OhiQ$IGXEHA.128@.TK2MSFTNGP10.phx.gbl...
> > What error were you expecting?
> > About using forward only cursor backward.
> > I have seen an error in similar situation, but can't verify this now
> >|||I'm not going to presume to know the reason(s) why, but MoveFirst() allows
you to just start over. The problem you will have with certain cursor types
is that you will not be able to move backwards, e.g. MovePrevious(). Also,
try a MoveLast() then a MoveFirst().
--
http://www.aspfaq.com/
(Reverse address to reply.)
"George Hester" <hesterloli@.hotmail.com> wrote in message
news:ud6NnQKXEHA.1652@.TK2MSFTNGP09.phx.gbl...
Right. What I provided should have resulted in a error but did not. The
adForwardOnly constant is NOT working. rs.MoveNext followed by rs.MoveFirst
should have resulted in a error at least that is my understanding of a
Recordset opened with that cursor type adForwardOnly..|||On Sun, 27 Jun 2004 02:01:06 -0400, "George Hester"
<hesterloli@.hotmail.com> wrote:
>For example why doesn't this give an error?
My experience with ADO is that if you give a set of parameters that is
supposed to work, it will work, and if you give a set of parameters
that is an any way faulty, results are unpredictable in advance and
error messages unlikely.
I suspect this is pretty much as far as Microsoft's design and
development of ADO reaches.
J.|||Could you please explain in what I provided where the "faulty set of =parameters are?" I understand what you are saying but if the parameters =are not faultitly set then how does what you say apply to the issue as I =have presented it? If what I have done is "faulty" please tell me where =and I won't do it again.
-- George Hester
__________________________________
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message =news:5io0e0t6ne26406u5712i0s7525dpthdbc@.4ax.com...
> On Sun, 27 Jun 2004 02:01:06 -0400, "George Hester"
> <hesterloli@.hotmail.com> wrote:
> >For example why doesn't this give an error?
> > My experience with ADO is that if you give a set of parameters that is
> supposed to work, it will work, and if you give a set of parameters
> that is an any way faulty, results are unpredictable in advance and
> error messages unlikely.
> > I suspect this is pretty much as far as Microsoft's design and
> development of ADO reaches.
> > J.
> >|||Aaron I am not sure I follow what you were saying. Are you saying that =what I presented is not supposed to give an error? If that is the case =could you please clarify if setting the cursor as adOpenForwardOnly and =then moving the Recordset rs.MoveNext and later rs.MoveFirst is not =contradicting the definition of adOpenForwardOnly? Because I seem to =misunderstand the definition of adOpenForwardOnly then. Thanks.
-- George Hester
__________________________________
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message =news:OmdlQpRXEHA.3168@.TK2MSFTNGP10.phx.gbl...
> I'm not going to presume to know the reason(s) why, but MoveFirst() =allows
> you to just start over. The problem you will have with certain cursor =types
> is that you will not be able to move backwards, e.g. MovePrevious(). =Also,
> try a MoveLast() then a MoveFirst().
> > -- > http://www.aspfaq.com/
> (Reverse address to reply.)
> > > > > "George Hester" <hesterloli@.hotmail.com> wrote in message
> news:ud6NnQKXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> Right. What I provided should have resulted in a error but did not. =The
> adForwardOnly constant is NOT working. rs.MoveNext followed by =rs.MoveFirst
> should have resulted in a error at least that is my understanding of a
> Recordset opened with that cursor type adForwardOnly..
> >|||Hi Aaron. I changed rs.MoveFirst to rs.MovePrevious and got the error =as expected. "Operation is not allowed in this context." Well that's =good. I guess rs.MoveFirst after one rs.MoveNext is different then =rs.MovePrevious after one rs.MoveNext. Because the later errors but the =former does not. Mmmm...
-- George Hester
__________________________________
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message =news:OmdlQpRXEHA.3168@.TK2MSFTNGP10.phx.gbl...
> I'm not going to presume to know the reason(s) why, but MoveFirst() =allows
> you to just start over. The problem you will have with certain cursor =types
> is that you will not be able to move backwards, e.g. MovePrevious(). =Also,
> try a MoveLast() then a MoveFirst().
> > -- > http://www.aspfaq.com/
> (Reverse address to reply.)
> > > > > "George Hester" <hesterloli@.hotmail.com> wrote in message
> news:ud6NnQKXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> Right. What I provided should have resulted in a error but did not. =The
> adForwardOnly constant is NOT working. rs.MoveNext followed by =rs.MoveFirst
> should have resulted in a error at least that is my understanding of a
> Recordset opened with that cursor type adForwardOnly..
> >|||> what I presented is not supposed to give an error? If that is the case
> could you please clarify if setting the cursor as adOpenForwardOnly and
> then moving the Recordset rs.MoveNext and later rs.MoveFirst is not
> contradicting the definition of adOpenForwardOnly?
If I drive on a one way street, and then do it again, I haven't broken any
law. The constant is not called adOpenForwardOnlyAndOnlyDoItOnce. ;-)
--
http://www.aspfaq.com/
(Reverse address to reply.)|||I agree that makes sense. Consider this using Cursor Type =adOpenForwardOnly
<%
'--
rs.MoveNext
'--
rs.MoveFirst
'--
%>
Gives no error as we have seen. So in the same vein let's try this:
<%
'--
rs.MoveLast
'--
rs.MoveFirst
'--
%>
This DOES give an error namely:
Microsoft OLE DB Provider for SQL Server error '80040e24' Rowset does not support fetching backward.
So you got a pretty funky One-Way street there. If I go to the end of =the road I can't go back. But if I only go a little bit down the road I =can start over if I choose. I'm not trying to beat a dead horse just =trying to understand visualize what is going on here. It seems to me if =the recordset had only two records then both would error. That would be =consistent. I guess that's the next test.
-- George Hester
__________________________________
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message =news:u#FUS8cXEHA.2844@.TK2MSFTNGP11.phx.gbl...
> > what I presented is not supposed to give an error? If that is the =case > > could you please clarify if setting the cursor as adOpenForwardOnly =and > > then moving the Recordset rs.MoveNext and later rs.MoveFirst is not > > contradicting the definition of adOpenForwardOnly?
> > If I drive on a one way street, and then do it again, I haven't broken =any > law. The constant is not called adOpenForwardOnlyAndOnlyDoItOnce. =;-)
> > -- > http://www.aspfaq.com/
> (Reverse address to reply.) > >|||On Tue, 29 Jun 2004 01:32:36 -0400, "George Hester"
<hesterloli@.hotmail.com> wrote:
>Could you please explain in what I provided where the
>"faulty set of parameters are?" I understand what you
>are saying but if the parameters are not faultitly set
>then how does what you say apply to the issue as I have
>presented it? If what I have done is "faulty" please
>tell me where and I won't do it again.
I can't recall whether a forwardonly cursor is supposed to allow a
.MoveFirst(), but probably not, and that is where YOU expected it to
give you an error. Under the circumstances, this is what I meant by
"faulty parameters". I'm not shocked that there was no error message.
But, you didn't tell us what the program *does* when it hits that
point - does the .MoveFirst() execute correctly and let you rerun the
data? If so, you've discovered an undocumented feature, maybe, but
then there were no faulty parameters and no error message should have
been expected.
J.|||It seems to me like some cache :)
What happen if do
<%
'--
rs.MoveNext
'--
rs.MoveNext
'--
rs.MoveFirst
'--
%>
I again cannot test here...
Bojidar Alexandrov
"George Hester" <hesterloli@.hotmail.com> wrote in message
news:uFeceAgXEHA.3640@.TK2MSFTNGP11.phx.gbl...
I agree that makes sense. Consider this using Cursor Type adOpenForwardOnly
<%
'--
rs.MoveNext
'--
rs.MoveFirst
'--
%>
Gives no error as we have seen. So in the same vein let's try this:
<%
'--
rs.MoveLast
'--
rs.MoveFirst
'--
%>
This DOES give an error namely:
Microsoft OLE DB Provider for SQL Server error '80040e24'
Rowset does not support fetching backward.
So you got a pretty funky One-Way street there. If I go to the end of the
road I can't go back. But if I only go a little bit down the road I can
start over if I choose. I'm not trying to beat a dead horse just trying to
understand visualize what is going on here. It seems to me if the recordset
had only two records then both would error. That would be consistent. I
guess that's the next test.
--
George Hester
__________________________________
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u#FUS8cXEHA.2844@.TK2MSFTNGP11.phx.gbl...
> > what I presented is not supposed to give an error? If that is the case
> > could you please clarify if setting the cursor as adOpenForwardOnly and
> > then moving the Recordset rs.MoveNext and later rs.MoveFirst is not
> > contradicting the definition of adOpenForwardOnly?
> If I drive on a one way street, and then do it again, I haven't broken any
> law. The constant is not called adOpenForwardOnlyAndOnlyDoItOnce. ;-)
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>|||Now that is a good question. See I really expected the error to occur =at .MoveFirst. It didn't. But to really see if it did what was =expected I need to make sure that after .MoveNext and then .MoveFirst =the entire recordset is returned.
I haven't really investigated it that much. I am at the moment doing =something a little different. But I should be able to answer both your =and the above interogatories by this weekend.
-- George Hester
__________________________________
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message =news:odu3e0hbedpv3sbab4h4edthi5ierbnoco@.4ax.com...
> On Tue, 29 Jun 2004 01:32:36 -0400, "George Hester"
> <hesterloli@.hotmail.com> wrote:
> > >Could you please explain in what I provided where the > >"faulty set of parameters are?" I understand what you > >are saying but if the parameters are not faultitly set > >then how does what you say apply to the issue as I have > >presented it? If what I have done is "faulty" please > >tell me where and I won't do it again.
> > I can't recall whether a forwardonly cursor is supposed to allow a
> .MoveFirst(), but probably not, and that is where YOU expected it to
> give you an error. Under the circumstances, this is what I meant by
> "faulty parameters". I'm not shocked that there was no error message.
> But, you didn't tell us what the program *does* when it hits that
> point - does the .MoveFirst() execute correctly and let you rerun the
> data? If so, you've discovered an undocumented feature, maybe, but
> then there were no faulty parameters and no error message should have
> been expected.
> > J.
> >|||Yeah OK. The recordset returns returns 268 records without the =.MoveNext first followed by the .MoveFirst before writing the recordset =to the client (asp).
With the .MoveNext followed by the .MoveFirst the recordset returns 268 =records. If I do .MoveLast followed by .MoveFirst then yes there is an =error as I described earlier. Next on the agenda is two .MoveNext's =followed by the .MoveFirst. The answer to that will be under Bojidar's =post.
Remember the Reordset is opened with rs.CursorType=3DadOpenForwardOnly:
<%
Option Explicit
Dim cn, cmd, rs, sSQL, pBeginDate, pEndDate, lRecs, i
sSQL =3D "EXEC [Employee Sales by Country] '19980101','19981231'"
Set cn =3D Server.CreateObject("ADODB.Connection")
i =3D 1
cn.Open(Application("DBconn"))
Set rs =3D Server.CreateObject("ADODB.Recordset")
'
rs.CursorType =3D adOpenForwardOnly
Set rs =3D cn.Execute(sSQL, lRecs, adCmdText)
If rs.EOF Then
Response.Write("No records found")
Else
rs.MoveNext
%>
<!-- Writing the recordset here using ASP and HTML -->
<%
'rs.MoveFirst
'.......
%>
End If
Response.End
'...
%>
The last thing to check is if the Recordset only contains two =records...I just have to find the right parameter for that in the SQL.
-- George Hester
__________________________________
"George Hester" <hesterloli@.hotmail.com> wrote in message =news:#py06C8XEHA.3120@.TK2MSFTNGP12.phx.gbl...
Now that is a good question. See I really expected the error to occur =at .MoveFirst. It didn't. But to really see if it did what was =expected I need to make sure that after .MoveNext and then .MoveFirst =the entire recordset is returned.
I haven't really investigated it that much. I am at the moment doing =something a little different. But I should be able to answer both your =and the above interogatories by this weekend.
-- George Hester
__________________________________
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message =news:odu3e0hbedpv3sbab4h4edthi5ierbnoco@.4ax.com...
> On Tue, 29 Jun 2004 01:32:36 -0400, "George Hester"
> <hesterloli@.hotmail.com> wrote:
> > >Could you please explain in what I provided where the > >"faulty set of parameters are?" I understand what you > >are saying but if the parameters are not faultitly set > >then how does what you say apply to the issue as I have > >presented it? If what I have done is "faulty" please > >tell me where and I won't do it again.
> > I can't recall whether a forwardonly cursor is supposed to allow a
> .MoveFirst(), but probably not, and that is where YOU expected it to
> give you an error. Under the circumstances, this is what I meant by
> "faulty parameters". I'm not shocked that there was no error message.
> But, you didn't tell us what the program *does* when it hits that
> point - does the .MoveFirst() execute correctly and let you rerun the
> data? If so, you've discovered an undocumented feature, maybe, but
> then there were no faulty parameters and no error message should have
> been expected.
> > J.
> >|||Nope no error with and without .rs.MoveNext followed by .rs.MoveFirst =and with rs.MoveNext rs.MoveNext followed by rs.MoveFirst. No error and =the same number of records are returned as with none of these =shanaigans.
Last test is the 2 record recordset...
-- George Hester
__________________________________
"Bojidar Alexandrov" <bojo_do_no_spam@.kodar.net> wrote in message =news:OLsNX7qXEHA.1356@.TK2MSFTNGP09.phx.gbl...
> It seems to me like some cache :)
> > What happen if do
> <%
> '--
> rs.MoveNext
> '--
> rs.MoveNext
> '--
> rs.MoveFirst
> '--
> %>
> > > I again cannot test here...
> > > Bojidar Alexandrov
> "George Hester" <hesterloli@.hotmail.com> wrote in message
> news:uFeceAgXEHA.3640@.TK2MSFTNGP11.phx.gbl...
> I agree that makes sense. Consider this using Cursor Type =adOpenForwardOnly
> > <%
> '--
> rs.MoveNext
> '--
> rs.MoveFirst
> '--
> %>
> > Gives no error as we have seen. So in the same vein let's try this:
> > <%
> '--
> rs.MoveLast
> '--
> rs.MoveFirst
> '--
> %>
> > This DOES give an error namely:
> > Microsoft OLE DB Provider for SQL Server error '80040e24'
> Rowset does not support fetching backward.
> > So you got a pretty funky One-Way street there. If I go to the end of =the
> road I can't go back. But if I only go a little bit down the road I =can
> start over if I choose. I'm not trying to beat a dead horse just =trying to
> understand visualize what is going on here. It seems to me if the =recordset
> had only two records then both would error. That would be consistent. = I
> guess that's the next test.
> > -- > George Hester
> __________________________________
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:u#FUS8cXEHA.2844@.TK2MSFTNGP11.phx.gbl...
> > > what I presented is not supposed to give an error? If that is the =case
> > > could you please clarify if setting the cursor as =adOpenForwardOnly and
> > > then moving the Recordset rs.MoveNext and later rs.MoveFirst is =not
> > > contradicting the definition of adOpenForwardOnly?
> >
> > If I drive on a one way street, and then do it again, I haven't =broken any
> > law. The constant is not called adOpenForwardOnlyAndOnlyDoItOnce. =;-)
> >
> > -- > > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
> >|||Well I don't get it. I set the:
sSQL =3D "EXEC [Employee Sales by Country] '19980505', '19980505'"
This returns two records. The rs.MoveNext sems to do nothing. Only the =.rs.MoveLast followed by rs.MoveFirst gives the error. So I tried this:
rs.MoveNext then wrote this recodset (no rs.MoveFirst). Only one record =was returned as expected. But putting in rs.MoveFirst after this =rs.MoveNext two are returned. I'm not happy with this.
-- George Hester
__________________________________
"Bojidar Alexandrov" <bojo_do_no_spam@.kodar.net> wrote in message =news:OLsNX7qXEHA.1356@.TK2MSFTNGP09.phx.gbl...
> It seems to me like some cache :)
> > What happen if do
> <%
> '--
> rs.MoveNext
> '--
> rs.MoveNext
> '--
> rs.MoveFirst
> '--
> %>
> > > I again cannot test here...
> > > Bojidar Alexandrov
> "George Hester" <hesterloli@.hotmail.com> wrote in message
> news:uFeceAgXEHA.3640@.TK2MSFTNGP11.phx.gbl...
> I agree that makes sense. Consider this using Cursor Type =adOpenForwardOnly
> > <%
> '--
> rs.MoveNext
> '--
> rs.MoveFirst
> '--
> %>
> > Gives no error as we have seen. So in the same vein let's try this:
> > <%
> '--
> rs.MoveLast
> '--
> rs.MoveFirst
> '--
> %>
> > This DOES give an error namely:
> > Microsoft OLE DB Provider for SQL Server error '80040e24'
> Rowset does not support fetching backward.
> > So you got a pretty funky One-Way street there. If I go to the end of =the
> road I can't go back. But if I only go a little bit down the road I =can
> start over if I choose. I'm not trying to beat a dead horse just =trying to
> understand visualize what is going on here. It seems to me if the =recordset
> had only two records then both would error. That would be consistent. = I
> guess that's the next test.
> > -- > George Hester
> __________________________________
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:u#FUS8cXEHA.2844@.TK2MSFTNGP11.phx.gbl...
> > > what I presented is not supposed to give an error? If that is the =case
> > > could you please clarify if setting the cursor as =adOpenForwardOnly and
> > > then moving the Recordset rs.MoveNext and later rs.MoveFirst is =not
> > > contradicting the definition of adOpenForwardOnly?
> >
> > If I drive on a one way street, and then do it again, I haven't =broken any
> > law. The constant is not called adOpenForwardOnlyAndOnlyDoItOnce. =;-)
> >
> > -- > > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
> >

I wonder if these ADO constants really work at all

For example why doesn't this give an error?
In a ASP:
<%@.language=3D"VBScript"%>
<%
'...
sSQL =3D "SELECT CustomerID, CompanyName, " & _=20
"ContactName, Country FROM Customers"
Set rs =3D Server.CreateObject("ADODB.Recordset")
rs.CursorType =3D adOpenForwardOnly
rs.Open sSQl, Application("DBconn")
rs.MoveNext
%>
<!-- HTML stuff -->
<%
rs.MoveFirst
'...
%>
<!-- HTML and ASP stuff -->
SQL 2000 SP3 ADO 2.7 SP(not sure) Windows 2000 SP3
--=20
George Hester
__________________________________What error were you expecting?
http://www.aspfaq.com/
(Reverse address to reply.)
"George Hester" <hesterloli@.hotmail.com> wrote in message
news:%23uESkwAXEHA.2908@.TK2MSFTNGP10.phx.gbl...
For example why doesn't this give an error?|||> What error were you expecting?
About using forward only cursor backward.
I have seen an error in similar situation, but can't verify this now|||Right. What I provided should have resulted in a error but did not. =
The adForwardOnly constant is NOT working. rs.MoveNext followed by =
rs.MoveFirst should have resulted in a error at least that is my =
understanding of a Recordset opened with that cursor type =
adForwardOnly..
--=20
George Hester
__________________________________
"Bojidar Alexandrov" <bojo_do_no_spam@.kodar.net> wrote in message =
news:OhiQ$IGXEHA.128@.TK2MSFTNGP10.phx.gbl...
>=20
> About using forward only cursor backward.
>=20
> I have seen an error in similar situation, but can't verify this now
>=20
>|||I'm not going to presume to know the reason(s) why, but MoveFirst() allows
you to just start over. The problem you will have with certain cursor types
is that you will not be able to move backwards, e.g. MovePrevious(). Also,
try a MoveLast() then a MoveFirst().
http://www.aspfaq.com/
(Reverse address to reply.)
"George Hester" <hesterloli@.hotmail.com> wrote in message
news:ud6NnQKXEHA.1652@.TK2MSFTNGP09.phx.gbl...
Right. What I provided should have resulted in a error but did not. The
adForwardOnly constant is NOT working. rs.MoveNext followed by rs.MoveFirst
should have resulted in a error at least that is my understanding of a
Recordset opened with that cursor type adForwardOnly..|||On Sun, 27 Jun 2004 02:01:06 -0400, "George Hester"
<hesterloli@.hotmail.com> wrote:
>For example why doesn't this give an error?
My experience with ADO is that if you give a set of parameters that is
supposed to work, it will work, and if you give a set of parameters
that is an any way faulty, results are unpredictable in advance and
error messages unlikely.
I suspect this is pretty much as far as Microsoft's design and
development of ADO reaches.
J.|||Could you please explain in what I provided where the "faulty set of =
parameters are?" I understand what you are saying but if the parameters =
are not faultitly set then how does what you say apply to the issue as I =
have presented it? If what I have done is "faulty" please tell me where =
and I won't do it again.
--=20
George Hester
__________________________________
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message =
news:5io0e0t6ne26406u5712i0s7525dpthdbc@.
4ax.com...
> On Sun, 27 Jun 2004 02:01:06 -0400, "George Hester"
> <hesterloli@.hotmail.com> wrote:
>=20
> My experience with ADO is that if you give a set of parameters that is
> supposed to work, it will work, and if you give a set of parameters
> that is an any way faulty, results are unpredictable in advance and
> error messages unlikely.
>=20
> I suspect this is pretty much as far as Microsoft's design and
> development of ADO reaches.
>=20
> J.
>=20
>|||Aaron I am not sure I follow what you were saying. Are you saying that =
what I presented is not supposed to give an error? If that is the case =
could you please clarify if setting the cursor as adOpenForwardOnly and =
then moving the Recordset rs.MoveNext and later rs.MoveFirst is not =
contradicting the definition of adOpenForwardOnly? Because I seem to =
misunderstand the definition of adOpenForwardOnly then. Thanks.
--=20
George Hester
__________________________________
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message =
news:OmdlQpRXEHA.3168@.TK2MSFTNGP10.phx.gbl...
> I'm not going to presume to know the reason(s) why, but MoveFirst() =
allows
> you to just start over. The problem you will have with certain cursor =
types
> is that you will not be able to move backwards, e.g. MovePrevious(). =
Also,
> try a MoveLast() then a MoveFirst().
>=20
> --=20
> http://www.aspfaq.com/
> (Reverse address to reply.)
>=20
>=20
>=20
>=20
> "George Hester" <hesterloli@.hotmail.com> wrote in message
> news:ud6NnQKXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> Right. What I provided should have resulted in a error but did not. =
The
> adForwardOnly constant is NOT working. rs.MoveNext followed by =
rs.MoveFirst
> should have resulted in a error at least that is my understanding of a
> Recordset opened with that cursor type adForwardOnly..
>=20
>|||Hi Aaron. I changed rs.MoveFirst to rs.MovePrevious and got the error =
as expected. "Operation is not allowed in this context." Well that's =
good. I guess rs.MoveFirst after one rs.MoveNext is different then =
rs.MovePrevious after one rs.MoveNext. Because the later errors but the =
former does not. Mmmm...
--=20
George Hester
__________________________________
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message =
news:OmdlQpRXEHA.3168@.TK2MSFTNGP10.phx.gbl...
> I'm not going to presume to know the reason(s) why, but MoveFirst() =
allows
> you to just start over. The problem you will have with certain cursor =
types
> is that you will not be able to move backwards, e.g. MovePrevious(). =
Also,
> try a MoveLast() then a MoveFirst().
>=20
> --=20
> http://www.aspfaq.com/
> (Reverse address to reply.)
>=20
>=20
>=20
>=20
> "George Hester" <hesterloli@.hotmail.com> wrote in message
> news:ud6NnQKXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> Right. What I provided should have resulted in a error but did not. =
The
> adForwardOnly constant is NOT working. rs.MoveNext followed by =
rs.MoveFirst
> should have resulted in a error at least that is my understanding of a
> Recordset opened with that cursor type adForwardOnly..
>=20
>|||> what I presented is not supposed to give an error? If that is the case
> could you please clarify if setting the cursor as adOpenForwardOnly and
> then moving the Recordset rs.MoveNext and later rs.MoveFirst is not
> contradicting the definition of adOpenForwardOnly?
If I drive on a one way street, and then do it again, I haven't broken any
law. The constant is not called adOpenForwardOnlyAndOnlyDoItOnce. ;-)
http://www.aspfaq.com/
(Reverse address to reply.)

I wasnt to learn all the possible ways of accessing Data in ASP .net 2.0

Hello.

I would like to learn all the possible ways that we can access and modify data in ASP .net 2.0 programmatically.

for example one way would be like this:

StringBuilder sql = new StringBuilder();
sql.Append(" SELECT *"); //count the total number of records
sql.Append(" FROM dbo.tblJobTitle ");

//get the connection string from web services
string strConnection = new sqlconnection.SQLConnection().GetSQLConnectString(sqlconnection.SQLDSN.SCIC);

//Use the Microsoft.practices SqlDatabase object to execute our sql.
SqlDatabase SqlHelper = new SqlDatabase(strConnection);
SqlDataReader reader = (SqlDataReader)SqlHelper.ExecuteReader(CommandType.Text, sql.ToString());

the i can loop through reader and get the data OR I can create a DataSet like this:

SqlDatabase db = new SqlDatabase(strConnection);
DbCommand dbCommand = db.GetSqlStringCommand(sql.ToString());
DataSet dst = db.ExecuteDataSet(dbCommand);

I want to know if there is a comprehensive book explainaning all these possible ways to access and modify data using C# under ASP .net 2.0

I really really apreciate this.

Thank you very much.

I think you suit almost all books about ADO.NET because ADO is a part of .NET framework platform as ASP.NET. Also you can read almost all book about C# 2.0 because 100% chances that inside is chapters about ADO.|||

Common, who can read all the books in the world ?

Is there any more practical way or learning them ? Like reading a specific book or a website or MSDN or ....

Honestly I am eager to learn them and I want to learn it in a good way, start from easy stuff to more difficult and pro ways of doing it.

Thanks

|||

Hmm, if what you want is a more practical way to learn ado.net, i would suggest you : 1. Install MSDN 2.Open your browser and google "ado.net c# source code" .You will find pretty a lot of results.

Read those codes line by line and if you find anything you cannot understand, look it up in msdn. That's the way how i learn ado.net.

Hope it helps

I want write a Trigger that make a 3nd table that contain all the record

Hi
I want to write Trigger tha do this:
I have 2 table

main table & sub main table that have data like this
for example I have Bank (code 001) in main table and visa(code 0001) & mastercard(code 0002) in submain table.

or I have BMW(code 101) in main table and X5(code 0001) & X3(code 0001) in sub main table.

I want write a Trigger that make a 3nd table that contain all the record of that two table like this:

NAME CODE
Bank 001
visa card 0010001
master card 0010002

BMW 101
X5 1010001
X3 1010002
..........................................

Trigger MUST make code for any record ( main code * 1000 + submain code ) that is unic. and record name.

1) Set a foreign key in the "submain" table to reference the unique 3-digit codes in the main table.

2) Write an INSERT tigger on the "submain" table that inserts a JOIN of main+submain into the 3rd table.

Something like what you see below. You could also use a computed column as explained in the other post.

Thanks

set nocount on
go

use tempdb
go

create table main(
the_name char(6) not null primary key
, the_main_code char(3) not null constraint main_unique unique
)
go

insert into main values ('Banks', '001')
insert into main values ('Goods', '101')
go

create table submain(
the_name varchar(20) not null primary key
, the_submain_code char(4) not null
, the_main_code char(3) foreign key references main(the_main_code)
)
go

create table combined (
the_main_name char(6)
, the_submain_name varchar(20)
, the_combined_code char(7)
)
go

create trigger make_combined on submain
for insert
as
insert into combined
select main.the_name, inserted.the_name, inserted.the_main_code + inserted.the_submain_code
from main join inserted
on main.the_main_code = inserted.the_main_code
go

insert into submain (the_name, the_submain_code, the_main_code) values ('visa card', '0001', '001')
insert into submain (the_name, the_submain_code, the_main_code) values ('master card', '0002', '001')
insert into submain (the_name, the_submain_code, the_main_code) values ('BMW X5', '0001', '101')
insert into submain (the_name, the_submain_code, the_main_code) values ('BMW X3', '0002', '101')
go

select * from combined order by the_combined_code
go

drop table submain
go
drop table main
go
drop table combined
go

I want to parse @ArrayOfDays into @d1 through @d5

In my stored procedure, I want to parse @.ArrayOfDays into @.d1 through
@.d5.

@.ArrayOfDays is a varchar input parameter containing,
for example, "1.7.21.25.60." - five elements.

Most active vars:
@.i - loop counter
@.char - current char in string
@.tempVal - contains the current element as it is being built
@.tempValExecString - contains SELECT stmt for EXEC()

I'm using EXEC() to execute a dynamically built SELECT.

The error I get when calling from vb.net is:
Must declare the variable '@.tempVal'.

Two manual traces indicate the logic is ok.

I suspect my assignment statement for @.tempValExecString.

Any help would be appreciated. - BobC

------------------
DECLARE
@.d1 varchar(3),
@.d2 varchar(3),
@.d3 varchar(3),
@.d4 varchar(3),
@.d5 varchar(3),
@.i int,
@.char char(1),
@.tempVal varchar(3),
@.tempValExecString varchar(30)

SELECT @.tempVal = ''
SELECT @.i = 1

WHILE @.i < LEN(@.ArrayOfDays)
BEGIN
SELECT @.char = SUBSTRING(@.ArrayOfDays, @.i, 1)
WHILE @.char <'.'
BEGIN
SELECT @.tempVal = @.tempVal + @.char
SELECT @.char = SUBSTRING(@.ArrayOfDays, @.i+1, 1)
IF @.char = '.'
BEGIN
/* the following should produce "SELECT @.d1 = 1" when it reads the
first period(.) */
SELECT @.tempValExecString = 'SELECT @.d' + LTRIM(RTRIM(STR(@.i))) + '
= @.tempVal'
EXEC(@.tempValExecString)
SELECT @.tempVal = ''
SELECT @.i = @.i + 1
END
SELECT @.i = @.i + 1
END
END
------------------bobc (bcanavan@.fmbnewhomes.com) writes:

Quote:

Originally Posted by

In my stored procedure, I want to parse @.ArrayOfDays into @.d1 through
@.d5.
>
@.ArrayOfDays is a varchar input parameter containing,
for example, "1.7.21.25.60." - five elements.
>
Most active vars:
@.i - loop counter
@.char - current char in string
@.tempVal - contains the current element as it is being built
@.tempValExecString - contains SELECT stmt for EXEC()
>
I'm using EXEC() to execute a dynamically built SELECT.

Wait a minute. You are in a relational database now, not in a C++ program.

I didn't ask why you returned a delimited string in the procedure in
your first post, but if you intend on unpack the string in the
calling procedure, you are on the wrong track altogther. Pass the
data in a table, and perform your operations on the whole set.

Quote:

Originally Posted by

The error I get when calling from vb.net is:
Must declare the variable '@.tempVal'.
>
Two manual traces indicate the logic is ok.

No, it's not. A batch of dynamic SQL is a scope of its own, and you
cannot access variables in outer scope. If you want to assign
variables @.d1 to @.d5, that's five SELECT statements.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>In my stored procedure, I want to parse @.ArrayOfDays into @.d1 through @.d5 .. execute a dynamically built SELECT. <<

You are doing almost everything wrong. SQL does not work this way.
Just pass a simple five parameter list. Then clean up your data in the
procedure body. Try this for a skeleton

CREATE PROCEDURE Foobar
(@.d1 INTEGER, @.d2 INTEGER, @.d3 INTEGER, @.d4 INTEGER, @.d5 INTEGER)
AS
SELECT ..
FROM Floob
WHERE Floob.x IN (@.d1, @.d2, @.d3, @.d4, @.d5)
AND ..;

YOU can use COALESCE in the IN() list to handle NULLs or whatever.|||On Sep 28, 8:52 pm, --CELKO-- <jcelko...@.earthlink.netwrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

In my stored procedure, I want to parse @.ArrayOfDays into @.d1 through @.d5 .. execute a dynamically built SELECT. <<

>
You are doing almost everything wrong. SQL does not work this way.
Just pass a simple five parameter list. Then clean up your data in the
procedure body. Try this for a skeleton
>
CREATE PROCEDURE Foobar
(@.d1 INTEGER, @.d2 INTEGER, @.d3 INTEGER, @.d4 INTEGER, @.d5 INTEGER)
AS
SELECT ..
FROM Floob
WHERE Floob.x IN (@.d1, @.d2, @.d3, @.d4, @.d5)
AND ..;
>
YOU can use COALESCE in the IN() list to handle NULLs or whatever.

Thanks, all. Both posts (yesterday and today) have been part of my
experiments to find the most efficient method of querying a ton of
data a ton of different ways to populate a "dashboard" page on
our .net intranet. You've both discovered that I am more an
applications programmer than a sql programmer, so I know you'll
forgive me. Meanwhile, your input has been very helpful. Thanks for
your time. -BobC

ps: es, the output string "array" was intended to be parsed by my
vb.net app. I just got a little curious about how arrays could be
implemented in t-sql, and possibly save some calls to the db server by
my app, or at least reduce the number of batches. I'm sure it's all
been done before, but I had to try and fail for myself. A learning
experience if nothing else. Thanks again.|||CREATE PROCEDURE Foobar

Quote:

Originally Posted by

(@.d1 INTEGER, @.d2 INTEGER, @.d3 INTEGER, @.d4 INTEGER, @.d5 INTEGER)
AS
SELECT ..
FROM Floob
WHERE Floob.x IN (@.d1, @.d2, @.d3, @.d4, @.d5)
AND ..;
>
YOU can use COALESCE in the IN() list to handle NULLs or whatever.
>

Amazing, you just don't learn do you.

What is the risk and resource assessment of adding value number 6?

Resource assessment...

1) Change the stored procedure to accept an extra parameter
2) Change the query IN to accept an extra parameter
3) Change all the applications that call the stored procedure to accept
the extra parameter - that can be '1' to 'n' in a real environment where
applications share common logic (stored procedures).

Risk assessment...

1) Database changes - requires application to be taken offline while the
release to add the extra parameter is done
2) Application changes - each application binary needs to be updated to
use the new parameter; for fat clients that would be quite an involved task
for a couple of thousand clients even with SMS.
3) Testing - did you capture all applications using the procedure, each
application requires a test plan and testing.

Now, if you had used CSV instead - you'd pass a single parameter to the
stored procedure containing 1 to 'n' values then you wouldn't have any of
the above, it would just work; there would be no requirement to take the
application offline, there would be no risk that some clients didn't get
updated properly so weren't using the correct version of the executable
etc...

I really do wish you'd start listening to people who actually do this type
of thing day in day out and have done so for 20 + years, sitting writing
books for 30+ years and teaching people is no replacement for solid
industrial experience.

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]

"--CELKO--" <jcelko212@.earthlink.netwrote in message
news:1191027166.216455.160600@.50g2000hsm.googlegro ups.com...

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

>>In my stored procedure, I want to parse @.ArrayOfDays into @.d1 through
>>@.d5 .. execute a dynamically built SELECT. <<

>
You are doing almost everything wrong. SQL does not work this way.
Just pass a simple five parameter list. Then clean up your data in the
procedure body. Try this for a skeleton
>
CREATE PROCEDURE Foobar
(@.d1 INTEGER, @.d2 INTEGER, @.d3 INTEGER, @.d4 INTEGER, @.d5 INTEGER)
AS
SELECT ..
FROM Floob
WHERE Floob.x IN (@.d1, @.d2, @.d3, @.d4, @.d5)
AND ..;
>
YOU can use COALESCE in the IN() list to handle NULLs or whatever.
>

|||>I just got a little curious about how arrays could be implemented in T-SQL, .. <<

The concept of arrays, linked lists, etc. do not exist in SQL -- just
tables. Ever work with LISP? No arrays, and it uses recursion
instead of loops. And LISP only has lists.

SQL is very much a foreign language to the procedural programmer. For
example, in Japanese, there are no articles or plurals, the pronoun
system is totally different (no direct equivalent to first person
singular, etc), the verb tenses are totally different and sentences
have a topic, but not a subject, etc.). But millions of people still
use Japanese.

You can fake a matrix with this skeleton:

CREATE TABLE Array
(i INTEGER NOT NULL CHECK (i BETWEEN 1 and 10),
j INTEGER NOT NULL CHECK (i BETWEEN 1 and 10),
vali INTEGER NOT NULL);

For INTEGER ARRAY A[1:10, 1:10] in a procedural language, but then you
have to write your or own library functions, loop constructs, etc.
And performance will stink.|||bobc (bcanavan@.fmbnewhomes.com) writes:

Quote:

Originally Posted by

ps: es, the output string "array" was intended to be parsed by my
vb.net app.

Also in that case it would be better to return a result set and receive
that in a datatable in VB .Net. The code for composing the list is
dependent on the number of elements being fixed. Add one more value,
and you have a maintenance job to do. With result set + datatable you have
not.

Quote:

Originally Posted by

I just got a little curious about how arrays could be
implemented in t-sql, and possibly save some calls to the db server by
my app, or at least reduce the number of batches. I'm sure it's all
been done before, but I had to try and fail for myself.

You may be interested in this link for a rainy day:
http://www.sommarskog.se/arrays-in-sql.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||And yes, Tony... I'm after engineering, not quick and dirty code. Not

Quote:

Originally Posted by

sure who your criticism is aimed at, but I have been referring to an

110% @. celko and his proposed solution - the guy is an idiot with little
real industrial experience based on his solutions...

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]|||bobc (bcanavan@.fmbnewhomes.com) writes:

Quote:

Originally Posted by

Here is an example of what I'm trying to do: Populate a grid(below)
on a .net dashboard web page with counts of various types of
activity(y axis) that would be distributed into bins(x axis) . The
datasource would be a single table or simple view(no aggregates). The
bins would represent increments in specific criteria, which would NOT
necessarily involve sequential values such as days of the month. (The
actual page will contain several grids, each having a different number
of bins. I would like to use the same code for all grids, if possible
-- sending the datasource, number of bins, and bin criteria as input
parameters.)
>...
I could call 20 different stored procedures, each having n SELECT
statements, but we all know a whole list of reasons why that's a bad
idea.

Why would you have different procedures for different activities?

I will have to admit that I don't get a very good understanding of
what you are trying to achieve. But a standard recommendation is that
you post:

1) CREATE TABLE statement for your table(s).
2) INSERT statements with sample data.
3) The desired result given the sample.

Of course, this assumes that the data model is set, and neither that is
clear to me. Then again, if you post what you have now, we may get a
better grip of where you're heading.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||

Quote:

Originally Posted by

Quote:

Originally Posted by

I could call 20 different stored procedures, each having n SELECT
statements, but we all know a whole list of reasons why that's a bad
idea.

>
Why would you have different procedures for different activities?

Thanks for your patience, Erland. In the statment above, I was
stepping through my thought process for you -- moving from the simple
method of executing many slightly different SELECTs, to a black box
solution that can handle all bins for all activities. The black box
is what I'm aiming for.

Quote:

Originally Posted by

Of course, this assumes that the data model is set, and neither that is
clear to me. Then again, if you post what you have now, we may get a
better grip of where you're heading.

The data model is set. I will rewrite my post today, providing the
information you've asked for.

Thanks again very much.

BobC|||I hope this makes the problem more understandable. Thanks to anyone
who takes an interest.

I want to display a series of grids on a .net web page.

The grids would make up a dashboard that provides a snapshot of sales
and marketing activities, customer status information, forecasts and
objectives, etc.

Activities are grouped into grids by customer status (lead, active
customer, under contract, closed buyer) because the activities and
milestones (represented by the bins) vary for each status.

Activites(1-n) in a single grid are not all pulled from the same table
or view, and some of the criteria would differ. So I don't believe a
GROUP BY clause on a single dataset would fit the problem.

In the example below, activity1 represents sales leads that are
currently 1, 7, 20, 25 and 60 days old, respectively. These values
will come from the Prospects table.

Activity2 might represent forecasts for current sales leads at the
same intervals. These values would come from a sales objectives
table.

1 7 20 25 60
activity1 0 0 0 0 0
activity2 0 0 0 0 0
activity3 0 0 0 0 0
activity4 0 0 0 0 0
...
activityn 0 0 0 0 0

Another grid might have fewer or more bins.

My goal is to minimize:
# open connections to the database
# calls to stored procedures
length of code in stored procedures or udf's
maintenance as activities or bins are added or dropped

My "procedural language programmer" solution would be to open one
connection to the db and grab all the information at once, retrieving
all the activity rows in individual activity parameters. I could then
parse each parameter value into the appropriate bins with vb behind
the page.

Another approach, which Erland suggested, is to return a dataset.

I have listed (below) simplified versions of the Prospect table
description and the proc GetLeadsByStatusAge.

I use bin variables @.d1-@.d5 rather than literals 1, 7, 20, 25, 60
because I'm hoping there is a way to programmatically vary the number
and values of bin boundaries, through input parameters or some other
means, and make this thing elegant. In other words, I'd like to
create a black box that can process any activity I ask it to,
regardless of where the data comes from, how many bins apply, or what
the bin boundaries are.

GetLeadsByStatusAge returns an array string, but could just as easily
insert a row into a temporary table.

Thanks to Erland for suggesting the use of CASE. I've used it before
in a similar way, but sometimes forget its power.

Thanks to everyone who takes a look at it.

BobC

/ * Simplified definition of the Prospects table: */

CREATE TABLE Prospects (
CommunityCode varchar (3),
LastName1 varchar (30),
FirstName1 varchar (15),
InitialContactDate datetime,
ProspectStatus varchar (1),
StatusChangeDate datetime
)
GO

/* Simplified procedure intended to return one row of the grid on
my .net page. */

CREATE PROCEDURE GetLeadsByStatusAge
@.CommCode char(3),
@.Result varchar(255) OUTPUT

AS

SELECT @.Result =

(SELECT
ltrim(str(SUM(CASE
DATEDIFF(day, statuschangedate, CURRENT_TIMESTAMP) BETWEEN @.d1 AND
@.d2-1
WHEN TRUE THEN 1 ELSE 0 END))) + '.' +

ltrim(str(SUM(CASE
DATEDIFF(day, statuschangedate, CURRENT_TIMESTAMP) BETWEEN @.d2 AND
@.d3-1
WHEN TRUE THEN 1 ELSE 0 END))) + '.' +

ltrim(str(SUM(CASE
DATEDIFF(day, statuschangedate, CURRENT_TIMESTAMP) BETWEEN @.d3 AND
@.d4-1
WHEN TRUE THEN 1 ELSE 0 END))) + '.' +

ltrim(str(SUM(CASE
DATEDIFF(day, statuschangedate, CURRENT_TIMESTAMP) BETWEEN @.d4 AND
@.d5-1
WHEN TRUE THEN 1 ELSE 0 END))) + '.' +

ltrim(str(SUM(CASE
DATEDIFF(day, statuschangedate, CURRENT_TIMESTAMP) >= @.d5
WHEN TRUE THEN 1 ELSE 0 END))) + '.'

FROM prospects
WHERE CommunityCode = @.CommCode
AND prospectstatus='L'
)

GO|||CORRECTION: syntax of the case statement was wrong in last post.
Shoud be...

CREATE PROCEDURE GetLeadsByStatusAge
@.CommCode char(3),
@.Result varchar(255) OUTPUT

AS

SELECT @.Result =

(SELECT
ltrim(str(SUM(CASE WHEN
DATEDIFF(day, statuschangedate, CURRENT_TIMESTAMP) BETWEEN @.d1 AND
@.d2-1
THEN 1 ELSE 0 END))) + '.' +

ltrim(str(SUM(CASE WHEN
DATEDIFF(day, statuschangedate, CURRENT_TIMESTAMP) BETWEEN @.d2 AND
@.d3-1
THEN 1 ELSE 0 END))) + '.' +

ltrim(str(SUM(CASE WHEN
DATEDIFF(day, statuschangedate, CURRENT_TIMESTAMP) BETWEEN @.d3 AND
@.d4-1
THEN 1 ELSE 0 END))) + '.' +

ltrim(str(SUM(CASE WHEN
DATEDIFF(day, statuschangedate, CURRENT_TIMESTAMP) BETWEEN @.d4 AND
@.d5-1
THEN 1 ELSE 0 END))) + '.' +

ltrim(str(SUM(CASE WHEN
DATEDIFF(day, statuschangedate, CURRENT_TIMESTAMP) >= @.d5
THEN 1 ELSE 0 END))) + '.'

FROM prospects
WHERE CommunityCode = @.CommCode
AND prospectstatus='L'
)

GO|||Not giving up yet! I wrote a new proc that would return one row, and
I think looks very flexible. I can vary the number of bins and their
values, the data source, the WHERE clause of the SELECT it builds, and
even accomodate differences in column names from one data source to
another(a date field in this case). It could be called by a wrapper
to build a set of rows. The wrapper would execute a set of EXEC()'s
building a dataset, and then return the dataset to my vb.net page.

Open one db connection one time, get all the data back in one package,
eliminate verbose code.

I could build all the input parameters in vb and send them to the
wrapper, and vary the number of calls the wrapper makes to the
subproc. That way, I would only have to change the code in my page
when the number or value of bins change. Wouldn't have to edit the
stored procs.

Does it look like a reasonable solution to you? Is it reasonably
efficient?

Bob

------------------------------

CREATE PROCEDURE [dbo].[commdash_activity_by_age]
@.ArrayOfBins varchar(255), -- an input array string of variable
size(number of elements)
@.ArraySize int, -- number of elements
@.DataSourceName varchar(50), -- the data source name (table, view,
other stored proc)
@.DateFieldName varchar(25), -- the date field name differs in some
source tables
@.WhereClause varchar(255) -- the predetermined WHERE clause for the
SELECT

AS

DECLARE
@.WorkingArray varchar(255), -- copy of the input array string,
truncated from the left as bin elements are read
@.WorkingArrayLength int, -- actual length of the string
@.FirstDelimiter int, -- position of first bin delimiter in
WorkingArray
@.col int, -- index of current column in the SELECT clause
@.LowerBin varchar(3),
@.UpperBin varchar(3),
@.SelectClause varchar(4000), -- the select clause of the final query
to be executed
@.query varchar(4000) -- the final query to be executed

SET @.WorkingArray = @.ArrayOfBins
SET @.WorkingArrayLength = LEN(@.WorkingArray)
SET @.col = 1
SET @.LowerBin = ''
SET @.UpperBin = ''
SET @.SelectClause = 'SELECT '

-- get the first bin value from the input array string
SET @.FirstDelimiter = CHARINDEX('.', @.WorkingArray)
SET @.LowerBin = SUBSTRING(@.WorkingArray, 1, @.FirstDelimiter - 1)
-- remove the leading bin value and its trailing delimiter from the
front of @.ArrayOfBins
SET @.WorkingArray = SUBSTRING(@.WorkingArray, @.FirstDelimiter + 1,
@.WorkingArrayLength - @.FirstDelimiter)

-- loop through the appending of column statements @.ArraySize times
(number of elements)
WHILE @.col < @.ArraySize
BEGIN

-- get the upper bin value from the input array string
SET @.FirstDelimiter = CHARINDEX('.', @.WorkingArray)
SET @.UpperBin = SUBSTRING(@.WorkingArray, 1, @.FirstDelimiter - 1)
-- remove the leading bin value and its trailing delimiter from the
front of @.ArrayOfBins
SET @.WorkingArray = SUBSTRING(@.WorkingArray, @.FirstDelimiter + 1,
@.WorkingArrayLength - @.FirstDelimiter)

-- append the next column
SET @.SelectClause = @.SelectClause + 'ltrim(str(SUM(CASE WHEN
DATEDIFF(day, ' + @.DateFieldName + ', CURRENT_TIMESTAMP) BETWEEN ' +
@.LowerBin + ' AND ' + STR(CONVERT(integer, @.UpperBin)-1) +
'THEN 1 ELSE 0 END))), '

-- shift @.LowerBin up to @.UpperBin in preparation for next column
SET @.LowerBin = @.UpperBin
-- advance to the next column
SET @.col = @.col + 1

IF @.col = @.ArraySize -- append the column for the last bin, then
exit the loop.
BEGIN
SET @.SelectClause = @.SelectClause + 'ltrim(str(SUM(CASE WHEN
DATEDIFF(day, ' + @.DateFieldName + ', CURRENT_TIMESTAMP) ' +
@.LowerBin +
'THEN 1 ELSE 0 END))) '
END

END

-- append the FROM and WHERE clauses
SET @.query = @.SelectClause + 'FROM ' + @.DataSourcename + ' WHERE ' +
@.WhereClause

-- execute the query
EXEC(@.query)
GO|||bobc (bcanavan@.fmbnewhomes.com) writes:

Quote:

Originally Posted by

That has been the fundamental question all along. "How complex and/or
flexible can a single stored procedure be, and still be efficient?"
>
What I'm learning is that they can be very complex, but not as
flexible as c, vb, etc.

Depends on what you mean with flexible, but with regards to column and
tables, yes, SQL puts you into a straight-jacket. But there is stuff
which is a lot of work to do in a traditional language which is a breeze
in SQL.

Quote:

Originally Posted by

I could build all the input parameters in vb and send them to the
wrapper, and vary the number of calls the wrapper makes to the
subproc. That way, I would only have to change the code in my page
when the number or value of bins change. Wouldn't have to edit the
stored procs.
>
Does it look like a reasonable solution to you? Is it reasonably
efficient?

Except that this far you have not really gained of the strength of a
stored procedure. You could just as well have built that SQL string
in client code. And then use a real array, with need for any list.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

I want to create link with 2 servers

I want to create link with 2 servers is that possible to do that. for example is like this,

-

SELECT Products.ProductID,

Products.ProductName,

Products.CategoryID,

Categories.CategoryName

FROM [SERVER1].[Northwind].[dbo].[Products]

INNER JOIN

[SERVER2].[Northwind].[dbo].[Categories]

ON [Products].[CategoryID] = [Categories].[CategoryID]

--

Products table has SERVER1 and Categories table has SERVER2

Yes, this is possible but not very performant, because you have to retrieve all the data on the local server / query engine for doing your query stuff like joining, this can be vry expensive.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||Thank you for your post. Any better ways 2 do that I want create view using 3 tables in 2 servers.|||

You can Create a Linked Server and Use it like the way u have written the Query.

-Sivaraman Latchapathi

|||But that SQL query doesn’t work. Please can u post sample code?|||OK, why not using one server as therefore there would be one server with local data then. From that server you could pull out your joined data. The query would work if you prior would have setup the linked server. Either do that inthe graphical use interface, or use the procedure sp_addlinkedserver. For more information and examples, have a look in the BOL.

HTH, Jens Suessmeyer-

http://www.sqlserver2005.de

I need some design help (time series analysis)

Hi,
Here is a fictious example that models my real life problem that I'm trying
to solve.
Lets say we have a bunch of baseball hitters and every day we give them an
hour to hit as many 'out of the park' home runs as they can hit. Now we want
to track home many they hit each day and watch whether their totals are
going up or down over time.
My fictious table design (as I see it) would be (with data)
PlayerId TotalHits Date
1 8 1/1/6
2 6 1/1/6
3 10 1/1/6
4 4 1/1/6
1 9 1/2/6
2 8 1/2/6
3 15 1/2/6
4 1 1/2/6
...
This may be poor design, please tell me if it is.
Anyway, from this design I could do a 'select playerid, sum(totalhits) from
xxx group by playerId' to see who has the most hits and look at each of the
top X hitters and see if they're in a incremental pattern or decreasing
pattern or just steady but high hits count type pattern.
Instead of that manual method I would like to figure a way to have the
system automatically tell me whose hit count is increasing over time and
whose are decreasing. and be able to break it down by week. month
quarter...
I'm guessing this might be analysis services in sql server, I've never used
them so I don't know. I run sql server 2000 btw.
If you have articles or references please point me in the right direction.
thanksHi,
the design looks ok, simple and straight forward, if you don=B4t want to
store more information here, you should be fine with that. Sure AS
could help you in some cases, but this should be just a simple query
here. Lets try to find a suggestion which will fit your needs. What do
you want to see at the end of the day, a list with playids only ? The
sums of their hits ? The difference between nother time period ? Which
time period do you want to investigate ? Try to give some sample
resultset that we can suggest something as an query for you.
HTH, Jens Suessmeyer
--
http://www.sqlserver2005.de
--|||"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1151300420.928006.36970@.m73g2000cwd.googlegroups.com...
Hi,
the design looks ok, simple and straight forward, if you don´t want to
store more information here, you should be fine with that. Sure AS
could help you in some cases, but this should be just a simple query
here. Lets try to find a suggestion which will fit your needs. What do
you want to see at the end of the day, a list with playids only ? The
sums of their hits ? The difference between nother time period ? Which
time period do you want to investigate ? Try to give some sample
resultset that we can suggest something as an query for you.
HTH, Jens Suessmeyer
--
http://www.sqlserver2005.de
--
Ultimately I would like to see which players hit counts are on the rise and
which are on the decline. And yes I would like to be able to adjust the time
frames to analyze. So to start out I would like to see the query which would
show me the player id's whose hit counts are on the rise over the entire
time period stored in the database.
I can sort of see how the query could group by week (or month) but I don't
see how it can show me counts which are on the rise.
Thanks for your help.

I need help with date parameter issue

I have a report which I can search by a startdate and enddate. I want to have the parameter fields automatically enter the / / for the date example 01/01/2007 I just want to type 01012007 and have it enter 01/01/2007. How can this be accomplished. can some on show me a piece of code that can do this. I have heard of mask but do not now how to apply this to a parameter.

This should help:http://blogs.msdn.com/bradsy/archive/2006/04/04/568295.aspx

-Damien

|||

Thanks, I already now how to get the date format nte way I want it . I am just stuggling with how to have the parameter box have the // in it for the date I have to typr the // in with the date and I am trying to figure out how to have it fill this in when I type 01012007 and further help would be great

I need help please

Hello,
I started yesterday working with reporting services to get done some complex reports but I've encountered a lot troubles.
For example, I created one report and set both with and height for the body, then added one rectangle and inside of it, I dragged a Chart. Till then everything looked fine, but when I viewed the preview, my report is four page lenth and the charts appears on page 4 !!!
I've read in the newsgroups something about checking the vale of the Report.PageWith against the Body.Width, but WHERE CAN I FIND THE REPORT.PAGEWITDH ? is it withing the VS IDE '
Also, how can I add a "horizontal total" in a matrix '
I have the following layout for a matrix:
-- State (columns )
Month (rows) ... Units sold
My dataset returns these three fields
and I want something like ...
-- State (columns ) , Total Units
Month (rows) ... Units sold ...... , total units sold by month
My task is to create several reporting and each one will have several charts, lists and matrix.
Thanks
CesarSelect "Report" in the property browser. There would be PageSize property in
Layout category
Quoting from Books Online (
ms-help://MS.RSBOL80.1033/RSCREATE/htm/rcr_creating_structure_objects_v1_7vi
0.htm )
To add a subtotal to a matrix, add a subtotal to an individual group within
the matrix. Groups do not have subtotals by default. To add a subtotal to a
group, right-click the group column or row header and then click Subtotal.
This will open a new header for the subtotal. Reporting Services will
calculate the subtotal based on the aggregate in the data cell for the
group.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Cesar" <Cesar@.discussions.microsoft.com> wrote in message
news:DFB067AA-2DB6-4CEF-AC54-0C349FDE0E81@.microsoft.com...
> Hello,
> I started yesterday working with reporting services to get done some
complex reports but I've encountered a lot troubles.
> For example, I created one report and set both with and height for the
body, then added one rectangle and inside of it, I dragged a Chart. Till
then everything looked fine, but when I viewed the preview, my report is
four page lenth and the charts appears on page 4 !!!
> I've read in the newsgroups something about checking the vale of the
Report.PageWith against the Body.Width, but WHERE CAN I FIND THE
REPORT.PAGEWITDH ? is it withing the VS IDE '
> Also, how can I add a "horizontal total" in a matrix '
> I have the following layout for a matrix:
> -- State (columns )
> Month (rows) ... Units sold
> My dataset returns these three fields
> and I want something like ...
> -- State (columns ) , Total Units
> Month (rows) ... Units sold ...... , total units sold by month
> My task is to create several reporting and each one will have several
charts, lists and matrix.
> Thanks
> Cesar