I/O tracking for a Sql Table?

I'm looking for a way to monitor I/O(select, delete, & updates) to a particular SQL Table for a period of time. Any suggestions?SQL Profiler (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_mon_perf_86ib.asp)?

-PatP|||SQL Profiler would be usefull to you !|||Thanks for tip. I'm new to SQL Server 2000, but learning fast! Thanks againsql

I/O Error

Dear Experts,
During the execution of a select statement i faced this error message.
"I/O error 23(Data error (cyclic redundancy check).) detected during read at
offset
0x00000190efa000 in file 'd:\sqldb\MSSQL\data\Data2005_Data.MDF'."
Pls. advise solution
Thanks in advance.
Subhash ChandraSounds like a hard disk issue. Are you using a SAN, or otherwise
vendor-supported hardware? Do you have a good backup?
A
"Subhash" <Subhash@.discussions.microsoft.com> wrote in message
news:FF723DE3-1CD2-4912-AC49-1E4FA0D7B6D4@.microsoft.com...
> Dear Experts,
> During the execution of a select statement i faced this error
> message.
> "I/O error 23(Data error (cyclic redundancy check).) detected during read
> at
> offset
> 0x00000190efa000 in file 'd:\sqldb\MSSQL\data\Data2005_Data.MDF'."
> Pls. advise solution
> Thanks in advance.
> Subhash Chandra

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 was asked for this in interview...?? how to solve this..

Hi groups, I got this question in interview...
I have 3 table,
(1)select * from Mark_Details
(2)select * from Mark
(3)select * from Student
(1)select * from Mark_Details
--
StudId Markid Marks
--
A1 1 75
A1 2 70
A1 3 80
A1 4 85
A1 5 90
A2 1 70
A2 2 75
A2 3 80
A2 4 90
A2 5 80
(2)select * from Mark
--
Markid markname
--
1 Maths
2 Science
3 Social
4 English
5 Tamil
(3)select * from Student
--
StudId studname
--
A1 Selva
A2 Akbar
----
--
Is there any way to get the output given below...I tried subqueries but not
got it so for..is it possbile.?
----
--
Studid Studname Maths science Social English Tamil
----
--
A1 Selva 75 70 80 85 90
A2 Akbar 70 75 80 90 80
----
--
please suggest me...
MahesMahesh Kumar.R wrote:
> Hi groups, I got this question in interview...
> I have 3 table,
> (1)select * from Mark_Details
> (2)select * from Mark
> (3)select * from Student
> (1)select * from Mark_Details
> --
> StudId Markid Marks
> --
> A1 1 75
> A1 2 70
> A1 3 80
> A1 4 85
> A1 5 90
> A2 1 70
> A2 2 75
> A2 3 80
> A2 4 90
> A2 5 80
> (2)select * from Mark
> --
> Markid markname
> --
> 1 Maths
> 2 Science
> 3 Social
> 4 English
> 5 Tamil
> (3)select * from Student
> --
> StudId studname
> --
> A1 Selva
> A2 Akbar
> ----
--
> Is there any way to get the output given below...I tried subqueries but n
ot got it so for..is it possbile.?
> ----
--
> Studid Studname Maths science Social English Tamil
> ----
--
> A1 Selva 75 70 80 85 90
> A2 Akbar 70 75 80 90 80
> ----
--
> please suggest me...
> Mahes
I hope they gave you a better spec than you have here - like DDL
including primary and foreign keys and constraints. Here are two
solutions, obviously with some assumptions about the keys in your
example tables.
In SQL Server 2000:
SELECT S.studid, S.studname,
SUM(CASE WHEN M.markname = 'maths' THEN marks END) AS maths,
SUM(CASE WHEN M.markname = 'science' THEN marks END) AS science,
SUM(CASE WHEN M.markname = 'social' THEN marks END) AS social,
SUM(CASE WHEN M.markname = 'english' THEN marks END) AS english,
SUM(CASE WHEN M.markname = 'tamil' THEN marks END) AS tamil
FROM mark_details AS D
JOIN stud AS S
ON D.studid = S.studid
JOIN mark AS M
ON D.markid = M.markid
GROUP BY S.studid, S.studname ;
In SQL Server 2005:
WITH student_marks (studid, studname, markname, marks)
AS (
SELECT S.studid, S.studname, M.markname, D.marks
FROM mark_details AS D
JOIN stud AS S
ON D.studid = S.studid
JOIN mark AS M
ON D.markid = M.markid
)
SELECT
D.studid,
D.studname,
P.maths,
P.science,
P.social,
P.english,
P.tamil
FROM
student_marks AS D
PIVOT (
SUM(marks)
FOR markname IN ([maths],[science],[social],[english],[t
amil])
) AS P
Both of these are untested.
David Portas
SQL Server MVP
--|||Like David mentioned, these specs are incomplete. I'd like to add that even
with DDL and constraint info, there are a number of techniques that will
work with for the test data provided yet won't produce the desired results
when other complexities are added. For example, should students that take
only some or none of the courses be included?
Asking the right questions can do more to demonstrate your knowledge than
providing a correct answer. At a minimum, it's a good idea to list any
assumptions made and/or provide alternative solutions when the provided
information is incomplete.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mahesh Kumar.R" <maheshkumar@.sninform.com> wrote in message
news:uzZm0MZ9FHA.2640@.tk2msftngp13.phx.gbl...
Hi groups, I got this question in interview...
I have 3 table,
(1)select * from Mark_Details
(2)select * from Mark
(3)select * from Student
(1)select * from Mark_Details
--
StudId Markid Marks
--
A1 1 75
A1 2 70
A1 3 80
A1 4 85
A1 5 90
A2 1 70
A2 2 75
A2 3 80
A2 4 90
A2 5 80
(2)select * from Mark
--
Markid markname
--
1 Maths
2 Science
3 Social
4 English
5 Tamil
(3)select * from Student
--
StudId studname
--
A1 Selva
A2 Akbar
----
--
Is there any way to get the output given below...I tried subqueries but not
got it so for..is it possbile.?
----
--
Studid Studname Maths science Social English Tamil
----
--
A1 Selva 75 70 80 85 90
A2 Akbar 70 75 80 90 80
----
--
please suggest me...
Mahes|||I think a dynamic query can work this things out
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Mahesh Kumar.R" wrote:

> Hi groups, I got this question in interview...
> I have 3 table,
> (1)select * from Mark_Details
> (2)select * from Mark
> (3)select * from Student
> (1)select * from Mark_Details
> --
> StudId Markid Marks
> --
> A1 1 75
> A1 2 70
> A1 3 80
> A1 4 85
> A1 5 90
> A2 1 70
> A2 2 75
> A2 3 80
> A2 4 90
> A2 5 80
> (2)select * from Mark
> --
> Markid markname
> --
> 1 Maths
> 2 Science
> 3 Social
> 4 English
> 5 Tamil
> (3)select * from Student
> --
> StudId studname
> --
> A1 Selva
> A2 Akbar
> ----
--
> Is there any way to get the output given below...I tried subqueries but n
ot got it so for..is it possbile.?
> ----
--
> Studid Studname Maths science Social English Tamil
> ----
--
> A1 Selva 75 70 80 85 90
> A2 Akbar 70 75 80 90 80
> ----
--
> please suggest me...
> Mahes
>|||For SQL Server 2005, this should work. No subqueries or dynamic SQL needed.
:)
-- Prep tables
create table dbo.marks
(
studentID char(2),
subjectID tinyint,
score tinyint
)
go
create table dbo.subjects
(
subjectID tinyint,
subjectName varchar(15)
)
go
create table dbo.students
(
studentID char(2),
studentName varchar(15)
)
go
-- Spin up data
insert into dbo.marks values('A1','1','75')
insert into dbo.marks values('A1','2','70')
insert into dbo.marks values('A1','3','80')
insert into dbo.marks values('A1','4','85')
insert into dbo.marks values('A1','5','90')
insert into dbo.marks values('A2','1','70')
insert into dbo.marks values('A2','2','75')
insert into dbo.marks values('A2','3','80')
insert into dbo.marks values('A2','4','90')
insert into dbo.marks values('A2','5','80')
insert into dbo.subjects values(1,'Maths')
insert into dbo.subjects values(2,'Science')
insert into dbo.subjects values(3,'Social')
insert into dbo.subjects values(4,'English')
insert into dbo.subjects values(5,'Tamil')
insert into dbo.students values('A1','Selva')
insert into dbo.students values('A2','Akbar')
go
-- solution
with m(studID,studName,subject,score) as
(
select s.studentID,s.StudentName,b.subjectName,m.score
from dbo.students s
join dbo.marks m on s.studentID = m.studentID
join dbo.subjects b on m.subjectID = b.subjectID
)
select StudID,StudName,Maths,Science,Social,Eng
lish,Tamil
from m
pivot
(
max(score)
for subject in ([Maths],[Science],[Social],[English],[T
amil])
) p
Thanks!
Kent|||Mahesh Kumar.R wrote:
> Hi groups, I got this question in interview...
> I have 3 table,
> (1)select * from Mark_Details
> (2)select * from Mark
> (3)select * from Student
> (1)select * from Mark_Details
> --
> StudId Markid Marks
> --
> A1 1 75
> A1 2 70
> A1 3 80
> A1 4 85
> A1 5 90
> A2 1 70
> A2 2 75
> A2 3 80
> A2 4 90
> A2 5 80
> (2)select * from Mark
> --
> Markid markname
> --
> 1 Maths
> 2 Science
> 3 Social
> 4 English
> 5 Tamil
> (3)select * from Student
> --
> StudId studname
> --
> A1 Selva
> A2 Akbar
> ----
--
> Is there any way to get the output given below...I tried subqueries but n
ot got it so for..is it possbile.?
> ----
--
> Studid Studname Maths science Social English Tamil
> ----
--
> A1 Selva 75 70 80 85 90
> A2 Akbar 70 75 80 90 80
> ----
--
> please suggest me...
Sheesh! Why don't they just ask "Do you know what pivot query is"?
Regarding vocabulary, it looks like SQL area seriously lacks one. In
the other thread I see people pointlessy competing piling up
subqueries, istead of just saying "Look, that is just interval coalesce
problem. Look it up in the book ..."
Admittedly there is no such a book yet. I'm writing the one!|||Well Thanks for all your inputs :)..I feel complete now for asking MORE
......
In simple, How to convert ('N' rows ) into ('N' Columns )...I mean 'n' is
dynamic.so i'm not going to give this time mark=Maths..etc..
Mahes.~
"Mikito Harakiri" <mikharakiri_nospaum@.yahoo.com> wrote in message
news:1133408783.342538.244800@.g43g2000cwa.googlegroups.com...
> Mahesh Kumar.R wrote:
> ----
--
not got it so for..is it possbile.?
> ----
--
> ----
--
> ----
--
> Sheesh! Why don't they just ask "Do you know what pivot query is"?
> Regarding vocabulary, it looks like SQL area seriously lacks one. In
> the other thread I see people pointlessy competing piling up
> subqueries, istead of just saying "Look, that is just interval coalesce
> problem. Look it up in the book ..."
> Admittedly there is no such a book yet. I'm writing the one!
>|||Mahesh Kumar.R wrote:
> Well Thanks for all your inputs :)..I feel complete now for asking MORE
> ......
> In simple, How to convert ('N' rows ) into ('N' Columns )...I mean 'n' is
> dynamic.so i'm not going to give this time mark=Maths..etc..
> Mahes.~
>
http://www.aspfaq.com/show.asp?id=2462
David Portas
SQL Server MVP
--|||finally I learned a concept called " CROSS TAB REPORTS in SQL "..thanks for
all..
Mahes
http://spaces.msn.com/members/cyberiafreak
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1133442541.843844.176260@.g47g2000cwa.googlegroups.com...
> Mahesh Kumar.R wrote:
is
> http://www.aspfaq.com/show.asp?id=2462
> --
> David Portas
> SQL Server MVP
> --
>sql

i want to use substring function in DTS for transformation

hi all
i want to use substring function in DTS for transformation but its give me
run time error
my SQL statement for Source Table is
Select SUBSTRING(Field_name, 1, 2) from table_name
any help for how to use SUBSTRING in DTS
--
Message posted via http://www.sqlmonster.comamit
start on www.sqldts.com
"amit mota via SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in
message news:028636b3e291457ebd710ac58f1e25f0@.SQLMonster.com...
> hi all
> i want to use substring function in DTS for transformation but its give me
> run time error
> my SQL statement for Source Table is
> Select SUBSTRING(Field_name, 1, 2) from table_name
> any help for how to use SUBSTRING in DTS
> --
> Message posted via http://www.sqlmonster.com|||What type of server are you connecting to in the DTS package?
--
Message posted via http://www.sqlmonster.com

I want to use multiple select queries in a stored procedure

Dear all
I actually want to query multiple tables from a single stored
procedure. Please let me know the possibility of doing this. If some
have any idea regarding this please contact me at
resume2karthik@.gmail.com.
Of course it is possible, but your statement is so general that I do not
know what you want to do. For example:
1. Can you join several tables to produce a single result set? (Yes.)
2. Can you independently query several tables producing several result sets?
(Yes.)
3. Can you independently query several tables to finally producing a single
result set? (Yes.)
4. Et cetera.
Returning information from a stored procedure:
http://technet.microsoft.com/en-us/library/aa214398(SQL.80).aspx
Comments on processing multiple result sets:
http://technet.microsoft.com/en-us/library/ms187602.aspx
The UNION command: http://technet.microsoft.com/en-us/library/ms180026.aspx
Explain a little bit more and you will probably get a more useful answer.
RLF
<resume2karthik@.gmail.com> wrote in message
news:8cc48990-c843-4f34-b03e-b3b1d290701c@.x69g2000hsx.googlegroups.com...
> Dear all
> I actually want to query multiple tables from a single stored
> procedure. Please let me know the possibility of doing this. If some
> have any idea regarding this please contact me at
> resume2karthik@.gmail.com.
|||You can have multiple SELECT statements in your stored procuedre to return
the data and access each result sets using ADOs NextRecordset method.
- Sha Anand
"resume2karthik@.gmail.com" wrote:

> Dear all
> I actually want to query multiple tables from a single stored
> procedure. Please let me know the possibility of doing this. If some
> have any idea regarding this please contact me at
> resume2karthik@.gmail.com.
>

I want to use multiple select queries in a stored procedure

Dear all
I actually want to query multiple tables from a single stored
procedure. Please let me know the possibility of doing this. If some
have any idea regarding this please contact me at
resume2karthik@.gmail.com.Of course it is possible, but your statement is so general that I do not
know what you want to do. For example:
1. Can you join several tables to produce a single result set? (Yes.)
2. Can you independently query several tables producing several result sets?
(Yes.)
3. Can you independently query several tables to finally producing a single
result set? (Yes.)
4. Et cetera.
Returning information from a stored procedure:
http://technet.microsoft.com/en-us/library/aa214398(SQL.80).aspx
Comments on processing multiple result sets:
http://technet.microsoft.com/en-us/library/ms187602.aspx
The UNION command: http://technet.microsoft.com/en-us/library/ms180026.aspx
Explain a little bit more and you will probably get a more useful answer.
RLF
<resume2karthik@.gmail.com> wrote in message
news:8cc48990-c843-4f34-b03e-b3b1d290701c@.x69g2000hsx.googlegroups.com...
> Dear all
> I actually want to query multiple tables from a single stored
> procedure. Please let me know the possibility of doing this. If some
> have any idea regarding this please contact me at
> resume2karthik@.gmail.com.|||You can have multiple SELECT statements in your stored procuedre to return
the data and access each result sets using ADOs NextRecordset method.
- Sha Anand
"resume2karthik@.gmail.com" wrote:
> Dear all
> I actually want to query multiple tables from a single stored
> procedure. Please let me know the possibility of doing this. If some
> have any idea regarding this please contact me at
> resume2karthik@.gmail.com.
>

I want to use multiple select queries in a stored procedure

Dear all
I actually want to query multiple tables from a single stored
procedure. Please let me know the possibility of doing this. If some
have any idea regarding this please contact me at
resume2karthik@.gmail.com.Of course it is possible, but your statement is so general that I do not
know what you want to do. For example:
1. Can you join several tables to produce a single result set? (Yes.)
2. Can you independently query several tables producing several result sets?
(Yes.)
3. Can you independently query several tables to finally producing a single
result set? (Yes.)
4. Et cetera.
Returning information from a stored procedure:
http://technet.microsoft.com/en-us/library/aa214398(SQL.80).aspx
Comments on processing multiple result sets:
http://technet.microsoft.com/en-us/...y/ms187602.aspx
The UNION command: http://technet.microsoft.com/en-us/...y/ms180026.aspx
Explain a little bit more and you will probably get a more useful answer.
RLF
<resume2karthik@.gmail.com> wrote in message
news:8cc48990-c843-4f34-b03e-b3b1d290701c@.x69g2000hsx.googlegroups.com...
> Dear all
> I actually want to query multiple tables from a single stored
> procedure. Please let me know the possibility of doing this. If some
> have any idea regarding this please contact me at
> resume2karthik@.gmail.com.|||You can have multiple SELECT statements in your stored procuedre to return
the data and access each result sets using ADOs NextRecordset method.
- Sha Anand
"resume2karthik@.gmail.com" wrote:

> Dear all
> I actually want to query multiple tables from a single stored
> procedure. Please let me know the possibility of doing this. If some
> have any idea regarding this please contact me at
> resume2karthik@.gmail.com.
>

I want to use in a stored procedures two databases

I have two database, database1 and database2.
Now I want to write a stored procedure in database1, in this Database I need to make a select statement to a table in database2. It is possible? What is the correct code for this problem?

Can someone help me?SELECT * FROM Database2.dbo.Table1

(assuming they are on the same server, that is).

I want to select the SECOND newest record in a table,....is this possible?

Hi!
I want to do a query against a SQL DB and by sorting a datetime field, I want to get the second newest record in the table, not the newest.
Can I do that?
/Johan Ch

You could do it like this:

SELECT TOP 1
*
FROM
(SELECT TOP 2 * FROM myTable Order by myDateTime DESC) AS A
ORDER BY
myDateTime ASC

I want to select all rows between 10 and 20 .......

I want to select all rows between 10 and 20 from a
view

how can I do this?

which database are you using?

Oracle has rownum or something i'm not sure about name which you can use directly.

but you need to justify the criteria for records to be return. does your table/view contain ID field, if yes then think of situation say where my 15th record is deleted?

in general what i would do is create a temporary with required columns and 1 identity column. then i'll insert records in this table.

after inserting i'll simple run query like select fields from temp_table where id in @.num1 and @.num2.

thanks,

satish.

|||

is it ok?

(MS SQL)

SELECT ProductID, ProductName FROM Products WHERE UnitsInStock BETWEEN '0' AND '35'

this example is from MSDN

hope it helps thanks

|||

if you are asking about MS SQL

I think it may be

select *

from a

where rows between '10' and '20'

thanks and hope it helps

I want to prevent a login ID from doing Select

I want to disallow a login id from doing a 'Select' on
Table A. but can't.
What I did is Create a Login ID : User
Next, I access Table A's pemission and turn on red cross
under Select Column for the User row
Then log on to Quey Analyzer and do a select . I'm able
to do it( I expect to see permission denied message)
I couldn't even get it... Can someone advise ?
ThnksA user does not have any permissions on a database object unless they have
been granted permissions. Permissions can be granted directly to a users
but it better to grant permissions only to roles. This way, you can more
easily control security with user role membership.
The red X in Enterprise Manager is an explicit DENY. DENY is meaningful
only if the user was granted permissions (usually via some other role
membership) and you don't want a user/role to have this permission.
Usually, one only GRANTs permissions; DENY is used only in special cases.
The only way a user can access an object after an explicit DENY (as you have
apparently done) is if they are a member of a privileged database or server
role. Check to see if the user is a member of the sysadmin server role or
db_owner database role.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jen" <anonymous@.discussions.microsoft.com> wrote in message
news:dc1001c40b34$4f09be20$a501280a@.phx.gbl...
> I want to disallow a login id from doing a 'Select' on
> Table A. but can't.
> What I did is Create a Login ID : User
> Next, I access Table A's pemission and turn on red cross
> under Select Column for the User row
> Then log on to Quey Analyzer and do a select . I'm able
> to do it( I expect to see permission denied message)
> I couldn't even get it... Can someone advise ?
> Thnks

I want to jump to an entry specified by parameter, doable as bookmark?

I know I can do to just show 1 entry

select * from table where no = @.parameter_no

but is it possible to actually select ALL entries (I have 250 for now) in the list report, but jump to a particular # based on parameter?

Say I have ID 1 ~ 250 (select * from table) in DataSet, but when I enter 220 in parameter, the report still selects all 250 entries but jump to the 220 entry?

I have bookmark set up on each entry

Thanks for any help. I noticed in my search that jump to a report + bookmark isn't supported, does my situation fall under that?

Hello

Did you ever figure out how to do this? If so, would you care to share?

Thanks

sql

I want to jump to an entry specified by parameter, doable as bookmark?

I know I can do to just show 1 entry

select * from table where no = @.parameter_no

but is it possible to actually select ALL entries (I have 250 for now) in the list report, but jump to a particular # based on parameter?

Say I have ID 1 ~ 250 (select * from table) in DataSet, but when I enter 220 in parameter, the report still selects all 250 entries but jump to the 220 entry?

I have bookmark set up on each entry

Thanks for any help. I noticed in my search that jump to a report + bookmark isn't supported, does my situation fall under that?

Hello

Did you ever figure out how to do this? If so, would you care to share?

Thanks

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 WANNA UPDATE IMAGE

UPDATE TDAV
SET FOTO=(SELECT FOTO FROM #temp_FOTO WHERE NOID=TDAV.NOID)
Where FOTO is Image field. This syntax is error. How to Update Image field?If the comparison is based on a column with unique values ( the column noid
in your case ), you can use t-SQL update statement like:
UPDATE tdav
SET foto = t.foto
FROM #temp t
WHERE t.noid = tdav.noid ;
Anith

I thought I posted this one but dont see it. Problem with querying a query

It is my understanding that Views cannot have parameters. Also that
stored procedures can not be queried. My problem is this:

I want to select the rows that match a certain parameter.

From that I want to select the most current 20 rows (there is a date
field).

From that I want to select the lowest 10 rows based on a numeric
field.

Finally I want that to be input to a report and some calculations.

What this basically is the selection for USGA Golf Handicap Index. It
is the most current 20 rounds of golf by a golfer, then the best 10 of
those 20 and then finally the calculation.

Any help would be appreciated.Could be something like this. Here it is in SQL92:

SELECT S.employee_id, S.date_col, S.numeric_col
FROM Something AS S
LEFT JOIN Something AS T
ON S.employee_id = 1
AND T.employee_id = 1
AND (S.date_col < T.date_col
OR (s.date_col = T.date_col
AND S.numeric_col < T.numeric_col))
GROUP BY S.employee_id, S.date_col, S.numeric_col
HAVING COUNT(T.employee_id)<10 ;

Or, if you don't mind using the proprietary TOP modifier in SQL Server:

SELECT TOP 10 WITH TIES
employee_id, date_col, numeric_col
FROM Something
WHERE employee_id = 1
ORDER BY date_col DESC, numeric_col DESC ;

--
David Portas
SQL Server MVP
--|||Do some research on "inline user defined functions". These are essentially
views with parameters.

"Jim R" <JimReid@.comcast.net> wrote in message
news:7eb6df4b.0407151031.3b92ccd2@.posting.google.c om...
> It is my understanding that Views cannot have parameters. Also that
> stored procedures can not be queried. My problem is this:
> I want to select the rows that match a certain parameter.
> From that I want to select the most current 20 rows (there is a date
> field).
> From that I want to select the lowest 10 rows based on a numeric
> field.
> Finally I want that to be input to a report and some calculations.
> What this basically is the selection for USGA Golf Handicap Index. It
> is the most current 20 rounds of golf by a golfer, then the best 10 of
> those 20 and then finally the calculation.
> Any help would be appreciated.|||Thank you. My brother who is a dba in CA with Oracle was trying to
explain this to me but I just couldn't get it. You example allowed me
to do it both ways. I don't mind using the Top 10 with TIES but never
heard of it before.

Again thanks, makes for cleaner than temporary tables.

Jim

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

I think there is a better way.

It seems like im missing something, what i want to do is select the last row
from a table that relates to a specific person. My table and select
statement follow, the select statement works fine, works great as a matter
of fact, but i feel as if i am missing something (well something in addition
to a better understanding of sql but im still working on that).

CREATE TABLE Logins
(
ID IDENTITY(1,1) PRIMARY KEY
Username VARCHAR(100),
LoginTime DATETIME
)

-- Now i want to select the last login time for a specific user, so this is
what i have been doing.
SELECT TOP 1 FROM Logins WHERE Username = 'x' ORDER BY ID DESC

Best,
Muhd.Ok i just realized this might not be the best example because i think you
could probably just compare the date value of logintime and select the one
thats the highest (although im not sure how to do that either). So maybe
this is a better example, where what i want to do is select the most recent
comment made by someone.

CREATE TABLE Comments
(
ID IDENTITY(1,1) PRIMARY KEY,
UserName VARCHAR(100),
Comments VARCHAR(1000)
)

And then as in the previous example i would simply select the top 1 row
sorted desc by id. Of course maybe i touched on the solution, if i date
stamped each entry i could then select the most recent entry (its that most
recent entry thats giving me problems).

"Muhd" <muhd@.binarydemon.com> wrote in message
news:nLDNb.113982$JQ1.55283@.pd7tw1no...
> It seems like im missing something, what i want to do is select the last
row
> from a table that relates to a specific person. My table and select
> statement follow, the select statement works fine, works great as a matter
> of fact, but i feel as if i am missing something (well something in
addition
> to a better understanding of sql but im still working on that).
> CREATE TABLE Logins
> (
> ID IDENTITY(1,1) PRIMARY KEY
> Username VARCHAR(100),
> LoginTime DATETIME
> )
> -- Now i want to select the last login time for a specific user, so this
is
> what i have been doing.
> SELECT TOP 1 FROM Logins WHERE Username = 'x' ORDER BY ID DESC
> Best,
> Muhd.|||"Muhd" <muhd@.binarydemon.com> wrote in message news:nLDNb.113982$JQ1.55283@.pd7tw1no...
> It seems like im missing something, what i want to do is select the last row
> from a table that relates to a specific person. My table and select
> statement follow, the select statement works fine, works great as a matter
> of fact, but i feel as if i am missing something (well something in addition
> to a better understanding of sql but im still working on that).
> CREATE TABLE Logins
> (
> ID IDENTITY(1,1) PRIMARY KEY
> Username VARCHAR(100),
> LoginTime DATETIME
> )
> -- Now i want to select the last login time for a specific user, so this is
> what i have been doing.
> SELECT TOP 1 FROM Logins WHERE Username = 'x' ORDER BY ID DESC
> Best,
> Muhd.

CREATE VIEW LatestLogins (username, login_time)
AS
SELECT Username, LoginTime
FROM Logins AS L1
WHERE NOT EXISTS (SELECT *
FROM Logins AS L2
WHERE L2.Username = L1.Username AND
L2.LoginTime > L1.LoginTime)

SELECT username, login_time
FROM LatestLogins
WHERE username = 'x'

Regards,
jag|||Ok one last comment, by "last row" i actually mean the last entry that
someone made. I know enough to understand there isn't a "last row" in a
relational database. Just thought i would clarify.

"Muhd" <muhd@.binarydemon.com> wrote in message
news:nLDNb.113982$JQ1.55283@.pd7tw1no...
> It seems like im missing something, what i want to do is select the last
row
> from a table that relates to a specific person. My table and select
> statement follow, the select statement works fine, works great as a matter
> of fact, but i feel as if i am missing something (well something in
addition
> to a better understanding of sql but im still working on that).
> CREATE TABLE Logins
> (
> ID IDENTITY(1,1) PRIMARY KEY
> Username VARCHAR(100),
> LoginTime DATETIME
> )
> -- Now i want to select the last login time for a specific user, so this
is
> what i have been doing.
> SELECT TOP 1 FROM Logins WHERE Username = 'x' ORDER BY ID DESC
> Best,
> Muhd.|||> if i date
> stamped each entry i could then select the most recent entry (its that
most
> recent entry thats giving me problems).

Exactly. If you don't put that date/time stamp in your table then you
haven't recorded the information you need for your query. I suggest you
don't rely on the sequence of an identity column since identity isn't a
"real" attribute of your entity and it will cause you problems if you want
re-seed the value or merge it with data from another table.

SELECT username, comments
FROM Comments AS C
WHERE date_created =
(SELECT MAX(date_created)
FROM Comments
WHERE username = C.username)

--
David Portas
----
Please reply only to the newsgroup
--