Microsoft Query?

i am using the following criteria in the [DateTime] field;

>=#3/9/2007# And <=#3/12/2007 11:59:59 PM#

i am using microsft query and excel to create pivot tables, i created an odbc file to connect to my local server

[DateTime] [TagName] [Value]
2007-03-09 00:00:00.000 PS002_Pump1.Daily_Runtime 1.1833188533782959
2007-03-09 00:00:00.000 PS002_Pump2.Daily_Runtime 1.4499822854995728
2007-03-09 04:59:59.947 PS002_Pump1.Daily_Runtime 0.16666463017463684
2007-03-09 04:59:59.947 PS002_Pump2.Daily_Runtime 0.13333170115947723
2007-03-09 09:59:59.893 PS002_Pump1.Daily_Runtime 0.51666033267974854
2007-03-09 09:59:59.893 PS002_Pump2.Daily_Runtime 0.48332741856575012
2007-03-09 14:59:59.843 PS002_Pump1.Daily_Runtime 0.86665606498718262
2007-03-09 14:59:59.843 PS002_Pump2.Daily_Runtime 0.86665606498718262
2007-03-09 19:59:59.790 PS002_Pump1.Daily_Runtime 1.083320140838623
2007-03-09 19:59:59.790 PS002_Pump2.Daily_Runtime 1.1333194971084595
2007-03-10 05:59:59.683 PS002_Pump1.Daily_Runtime 0.18333110213279724
2007-03-10 05:59:59.683 PS002_Pump2.Daily_Runtime 0.18333110213279724
2007-03-10 10:59:59.630 PS002_Pump1.Daily_Runtime 0.49999389052391052
2007-03-10 10:59:59.630 PS002_Pump2.Daily_Runtime 0.56665974855422974
2007-03-10 15:59:59.577 PS002_Pump1.Daily_Runtime 0.83332318067550659
2007-03-10 15:59:59.577 PS002_Pump2.Daily_Runtime 0.93332195281982422
2007-03-10 20:59:59.527 PS002_Pump1.Daily_Runtime 1.1166530847549438
2007-03-10 20:59:59.527 PS002_Pump2.Daily_Runtime 1.2833176851272583
2007-03-11 07:59:59.420 PS002_Pump1.Daily_Runtime 0.14999817311763763
2007-03-11 07:59:59.420 PS002_Pump2.Daily_Runtime 0.24999694526195526
2007-03-11 12:59:59.367 PS002_Pump1.Daily_Runtime 0.56665974855422974
2007-03-11 12:59:59.367 PS002_Pump2.Daily_Runtime 0.69999146461486816
2007-03-11 17:59:59.317 PS002_Pump1.Daily_Runtime 0.81665670871734619
2007-03-11 17:59:59.317 PS002_Pump2.Daily_Runtime 0.98332130908966064
2007-03-11 22:59:59.263 PS002_Pump1.Daily_Runtime 1.1833188533782959
2007-03-11 22:59:59.263 PS002_Pump2.Daily_Runtime 1.3666499853134155
2007-03-12 03:59:59.210 PS002_Pump1.Daily_Runtime 0.11666524410247803
2007-03-12 03:59:59.210 PS002_Pump2.Daily_Runtime 0.08333231508731842
2007-03-12 08:59:59.157 PS002_Pump1.Daily_Runtime 0.23333048820495605
2007-03-12 08:59:59.157 PS002_Pump2.Daily_Runtime 0.28332987427711487
2007-03-12 13:59:59.107 PS002_Pump1.Daily_Runtime 0.66665852069854736
2007-03-12 13:59:59.107 PS002_Pump2.Daily_Runtime 0.76665729284286499
2007-03-12 18:59:59.053 PS002_Pump1.Daily_Runtime 0.89998900890350342
2007-03-12 18:59:59.053 PS002_Pump2.Daily_Runtime 1.0166542530059814
2007-03-12 23:59:59.000 PS002_Pump1.Daily_Runtime 1.1999853849411011
2007-03-12 23:59:59.000 PS002_Pump2.Daily_Runtime 1.3833163976669312

what i would like to do is not hard code the date for the data i need, is there a way to code the dates so that microsoft query
would pull those date from sql...ie;

[DateTime]

>= CONVERT(VARCHAR(20), GETDATE()-5, 100) 12:00:00 AM and <= CONVERT(VARCHAR(20), GETDATE(), 100) 11:59:59 PM

any ideas?

Can't you just use:

WHERE [DateTime] BETWEEN GETDATE() - 5 AND GETDATE()

Chris

|||no , because you only get the data for the current date and time. I need to code this so that it will pick up all data between 12:00:00 am and 11:5:59 pm for each date.|||

Just to clarify - if you ran the query now (15-Mar-2007 20:23:32.000) then you would expect to return data from 10-Mar-2007 00:00:00.000 to 15-Mar-2007 23:59:59.999 inclusive.

Is this correct?

If so, then this could help:

WHERE [DateTime] >= CAST(CONVERT(VARCHAR(8), GETDATE() - 5, 112) AS DATETIME)
AND [DateTime] < CAST(CONVERT(VARCHAR(8), GETDATE() + 1, 112) AS DATETIME)

Chris

|||Thank You Chris, that worked like a charm...Have a great weekend.

microsoft jdbc driver for SQL Server 7.0

Hi,
I need a Microsft jdbc driver to connect Microsoft SQL
Server 7.0 . I can find a driver Microsoft 2000 jdbc
driver through which I can access Microsoft SQL Server
2000 but not 7.0.
I do not like to go for a third party driver..
Thank you in advance,
Hanson.
Third party solution is what you will likely have to get. You can pay for one
or just get a free one that works really well:
http://jtds.sourceforge.net/
Good luck.
Yuval
"Hanson" wrote:

> Hi,
> I need a Microsft jdbc driver to connect Microsoft SQL
> Server 7.0 . I can find a driver Microsoft 2000 jdbc
> driver through which I can access Microsoft SQL Server
> 2000 but not 7.0.
> I do not like to go for a third party driver..
> Thank you in advance,
> Hanson.
>
|||"Hanson" <rarockia@.chn.cognizant.com> wrote in message
news:4d7001c4804e$40294f30$a601280a@.phx.gbl...
> Hi,
> I need a Microsft jdbc driver to connect Microsoft SQL
> Server 7.0 . I can find a driver Microsoft 2000 jdbc
> driver through which I can access Microsoft SQL Server
> 2000 but not 7.0.
> I do not like to go for a third party driver..
> Thank you in advance,
> Hanson
The Microsoft JDBC driver was designed specifically for SQL Server 2000. As
was mentioned before, you will have to consider an alternative solution if
you wish to connect to SQL Server 7.0.
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security..

MIcrosft SQLServer Best Practices document on securing SQLServer

I'm chasing after a documetn that was available on one of the Microsoft
websites that was titled somethign like "MS SQL Server Best Practices"
and detailed a nyumber of best practices about securing the server.

Included in this was revoking public access to the system table
objects.

Can someone post the URL where I can pick this up, or drop me a note on
contacting them for a copy of the document?"byrocat" <bdealhoy@.sympatico.ca> wrote in message
news:1122653744.611069.56230@.o13g2000cwo.googlegro ups.com...
> I'm chasing after a documetn that was available on one of the Microsoft
> websites that was titled somethign like "MS SQL Server Best Practices"
> and detailed a nyumber of best practices about securing the server.
> Included in this was revoking public access to the system table
> objects.
> Can someone post the URL where I can pick this up, or drop me a note on
> contacting them for a copy of the document?

You can find the Microsoft security docs, including a best practices white
paper, here:

http://www.microsoft.com/sql/techin...ty/default.mspx

I don't know of any good reason to revoke public permissions on system
tables - it might actually break something if users can't retrieve metadata
for some operations. Books Online states that a REVOKE applied to the public
role applies to all database users, which is probably not desirable in many
cases.

This issue often seems to be raised by IT auditors, probably because it has
somehow became part of an industry-standard audit checklist, but the MS best
practices document says only "do not grant additional permissions to this
role", implying that the existing permissions are fine:

http://www.microsoft.com/technet/pr...n/sp3sec02.mspx

Simon|||byrocat (bdealhoy@.sympatico.ca) writes:
> I'm chasing after a documetn that was available on one of the Microsoft
> websites that was titled somethign like "MS SQL Server Best Practices"
> and detailed a nyumber of best practices about securing the server.
> Included in this was revoking public access to the system table
> objects.

I would not do this. At least not without extensive testing first.

The fact that all metadata is open to anyone is not entirely
satisfyable, but the opposite is not good either.

In SQL 2005 things are different. Here you can only see metadata
for objects that you have access to. Unfortunately, this important
distinction is not possible to make in SQL 2000.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Microsft Fix for wrong SP

Hi!
Tried to cure a problem described here:
http://support.microsoft.com/default.aspx?scid=kb;en-us;328551&Product=sql
The article says that
quote:
----
Hotfix 8.00.0702
A supported fix is now available from Microsoft, but it is only
intended to correct the problem described in this article. Only apply
it to systems that are experiencing this specific problem. This fix
may receive additional testing to further ensure product quality.
Therefore, if you are not severely affected by this problem, Microsoft
recommends that you wait for the next Microsoft SQL Server 2000
service pack (sp4) that contains this fix.
----
So I should be able to apply this fix for MS SQL2000 SP3. I could not
do it: It says that I have wrong service pack or version... Why?
Thanks."Roust_m" <roustam@.hotbox.ru> wrote in message
news:a388fd78.0311120254.3a5323ad@.posting.google.com...
> Tried to cure a problem described here:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;328551&Product=sql
> The article says that
> quote:
> ----
--
> Hotfix 8.00.0702
> A supported fix is now available from Microsoft, but it is only
> intended to correct the problem described in this article. Only apply
> it to systems that are experiencing this specific problem. This fix
> may receive additional testing to further ensure product quality.
> Therefore, if you are not severely affected by this problem, Microsoft
> recommends that you wait for the next Microsoft SQL Server 2000
> service pack (sp4) that contains this fix.
> ----
--
> So I should be able to apply this fix for MS SQL2000 SP3. I could not
> do it: It says that I have wrong service pack or version... Why?
It's possible you need to apply the latest service pack which is SQL Server
2000 SP3a , then apply the hotfix.
Steve|||This hotfix is a pre-SP3 hotfix. Build number 702 is prior to SP3 build of
760. This hotfix cannot be applied to SQL Server 2000 SP3. You will need
hotfix build 765 to get the fix for SP3.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||- So, what SP matches this service pack, 1st or 2nd?
(I could not install it on MS SQL2000 w/o SP too)
rboyd@.onlinemicrosoft.com (Rand Boyd [MS]) wrote in message news:<uOuMCDYqDHA.1548@.cpmsftngxa06.phx.gbl>...
> This hotfix is a pre-SP3 hotfix. Build number 702 is prior to SP3 build of
> 760. This hotfix cannot be applied to SQL Server 2000 SP3. You will need
> hotfix build 765 to get the fix for SP3.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.|||"Roust_m" <roustam@.hotbox.ru> wrote in message
news:a388fd78.0311140139.49eb3fc0@.posting.google.com...
> - So, what SP matches this service pack, 1st or 2nd?
> (I could not install it on MS SQL2000 w/o SP too)
If you mean which service patch would match that hotfix -- the hotfix should
work on a post SP2 SQL installation.
For version information
http://support.microsoft.com/default.aspx?scid=kb;en-us;321185&Product=sql
I'd recommend getting the post sp3 hotfix and installing SQL Server SP3a.
Steve
> rboyd@.onlinemicrosoft.com (Rand Boyd [MS]) wrote in message
news:<uOuMCDYqDHA.1548@.cpmsftngxa06.phx.gbl>...
> > This hotfix is a pre-SP3 hotfix. Build number 702 is prior to SP3 build
of
> > 760. This hotfix cannot be applied to SQL Server 2000 SP3. You will need
> > hotfix build 765 to get the fix for SP3.
> >
> > Rand
> > This posting is provided "as is" with no warranties and confers no
rights.|||You need to contact Microsoft and get the post-SP3 hotfix.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Microsft Development Environment - Missing Toolbox Items

Hi,
I had some problems with the MDE application used to create reporting services reports, and rectified the problem by running the program with the command switch /resetskippkgs. Since doing this, my toolbox has lost all the controls used to design a reporting services report (e.g. Matrix, Table, Image etc).
Anyone know how I can get these back? I've tried doing a repair on the installation of the application, but no joy.
Thanks.
SimonRight-click in the toolbox, choose Add/Remove Items, and click the Reset
button.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:F7117049-4270-49A4-A9D0-08CDAB6D70A9@.microsoft.com...
> Hi,
> I had some problems with the MDE application used to create reporting
services reports, and rectified the problem by running the program with the
command switch /resetskippkgs. Since doing this, my toolbox has lost all
the controls used to design a reporting services report (e.g. Matrix, Table,
Image etc).
> Anyone know how I can get these back? I've tried doing a repair on the
installation of the application, but no joy.
> Thanks.
> Simon