HELP! ORDER BY in views no longer works in SQL2K5

Is there some setting somewhere that would break this? I have some views
with order by clauses in them that are now totally ignored. I did not write
these...I would never put an order by clause in the view but I also do not
have access to the csharp code that is calling this so I cannot move the
clause into the appropriate place.
This worked fine under SQL2000 -- is there some setting I'm missing?That was an undocumented behavior of SQL Server 2000, and
unfortunately was discovered and was put into use by many. The
problem with undocumented behaviors is that they can change without
warning, with a new release or even a service pack. This one stopped
working with SQL Server 2005.
If you want data returned in a specific order, the ONLY way to be sure
to get it is with an ORDER BY on the (outer) query.
Roy Harvey
Beacon Falls, CT
On Mon, 17 Jul 2006 16:21:32 -0700, "Tim Greenwood" <tim_greenwood AT
yahoo DOT com> wrote:
>Is there some setting somewhere that would break this? I have some views
>with order by clauses in them that are now totally ignored. I did not write
>these...I would never put an order by clause in the view but I also do not
>have access to the csharp code that is calling this so I cannot move the
>clause into the appropriate place.
>This worked fine under SQL2000 -- is there some setting I'm missing?
>|||What you are seeing the the appropriate behavior. A VIEW 'should' not order
data UNLESS it is a necessary part of obtaining the required results, e.g.,
SELECT TOP 5%.
In the past, a VIEW would sometimes, but contrary to 'what should have been'
select the TOP 100% and then ORDER BY as a way to 'kludge' a sorted
resultset. TOP 100 percent and ORDER BY is ignored in SQL 2005.
In SQL 2005, it is the responsibility of the query that accesses the VIEW to
sort the data after the receiving the view resultset.
If the VIEW definitions are in the database (as they should be), you can
easily correct the view definintion. However, as you note, the application
code will have to be corrected in order for the queries to produce properly
sorted results.
This is another illustration of how having all data access operate through
Stored Procedures facilitates 'robustness'. If Stored Procedures were used,
it would be very easy for you to correct the problem.
You have few options.
Contact whoever wrote the code and request/require that it be corrected
since it was not written 'properly' and used unsupported features.
OR,
make a horrendous 'kludge'.
(1) Rename the views, and
(2) Create new stored procedures using the existing VIEW names, and in
those stored procedures, write the proper queries requesting data from the
renamed views and sorting it appropriately. (3) Test, Test, Test, Test, and
then Test.
Hopefully, someone will come up with other suggestions that will make this
outrageous kludge so difficult to stomach that you won't seriously consider
it.
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:%23jUN%23efqGHA.2180@.TK2MSFTNGP05.phx.gbl...
> Is there some setting somewhere that would break this? I have some views
> with order by clauses in them that are now totally ignored. I did not
> write these...I would never put an order by clause in the view but I also
> do not have access to the csharp code that is calling this so I cannot
> move the clause into the appropriate place.
> This worked fine under SQL2000 -- is there some setting I'm missing?
>|||Before everyone fires off on me, the 'horrendous 'kludge' idea was meant to
be tongue in cheek.
The joke sould have never gotten past the 'Test' stage.
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:el52YxfqGHA.2452@.TK2MSFTNGP03.phx.gbl...
> What you are seeing the the appropriate behavior. A VIEW 'should' not
> order data UNLESS it is a necessary part of obtaining the required
> results, e.g., SELECT TOP 5%.
> In the past, a VIEW would sometimes, but contrary to 'what should have
> been' select the TOP 100% and then ORDER BY as a way to 'kludge' a sorted
> resultset. TOP 100 percent and ORDER BY is ignored in SQL 2005.
> In SQL 2005, it is the responsibility of the query that accesses the VIEW
> to sort the data after the receiving the view resultset.
> If the VIEW definitions are in the database (as they should be), you can
> easily correct the view definintion. However, as you note, the application
> code will have to be corrected in order for the queries to produce
> properly sorted results.
> This is another illustration of how having all data access operate through
> Stored Procedures facilitates 'robustness'. If Stored Procedures were
> used, it would be very easy for you to correct the problem.
> You have few options.
> Contact whoever wrote the code and request/require that it be corrected
> since it was not written 'properly' and used unsupported features.
> OR,
> make a horrendous 'kludge'.
> (1) Rename the views, and
> (2) Create new stored procedures using the existing VIEW names, and in
> those stored procedures, write the proper queries requesting data from the
> renamed views and sorting it appropriately. (3) Test, Test, Test, Test,
> and then Test.
> Hopefully, someone will come up with other suggestions that will make this
> outrageous kludge so difficult to stomach that you won't seriously
> consider it.
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:%23jUN%23efqGHA.2180@.TK2MSFTNGP05.phx.gbl...
>> Is there some setting somewhere that would break this? I have some views
>> with order by clauses in them that are now totally ignored. I did not
>> write these...I would never put an order by clause in the view but I
>> also do not have access to the csharp code that is calling this so I
>> cannot move the clause into the appropriate place.
>> This worked fine under SQL2000 -- is there some setting I'm missing?
>|||Tim Greenwood wrote:
> Is there some setting somewhere that would break this? I have some views
> with order by clauses in them that are now totally ignored. I did not write
> these...I would never put an order by clause in the view but I also do not
> have access to the csharp code that is calling this so I cannot move the
> clause into the appropriate place.
> This worked fine under SQL2000 -- is there some setting I'm missing?
There is no change in SQL Server 2005. The ordering of rows is
undefined in the case of queries that don't specify ORDER BY. ORDER BY
in the view doesn't affect this.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Help! I cant write this SQL Server query ... can you?

Take the following table

ID Shipment ETA Updated
01 123 3/1/04 2/12/04
02 123 3/2/04 2/13/04
03 123 3/1/04 2/14/04
04 154 3/2/04 2/12/04
05 456 3/1/04 2/17/04
06 456 3/1/04 2/16/04
07 456 3/1/04 2/15/04

I need a query that will return the 2 most recently updated rows for
each shipment. So the results would look like the following:

ID Shipment ETA Updated
02 123 3/2/04 2/13/04
03 123 3/1/04 2/14/04
04 154 3/2/04 2/12/04
06 456 3/1/04 2/16/04
05 456 3/1/04 2/17/04

Thanks,

TeknariLets say the name of your table is Shipment_Status. Here goes

select Shipment,Id from shipment_status a where
id=(select max(id) from shipment_status b where shipment=a.shipment )
or
id=(select max(id) from shipment_status c where shipment=a.shipment
and id <>(select max(id) from shipment_status d where
shipment=c.shipment) )

Try it
Prashant

junk2@.bgtl.com (Teknari) wrote in message news:<b1e69426.0402160742.25896bac@.posting.google.com>...
> Take the following table
> ID Shipment ETA Updated
> 01 123 3/1/04 2/12/04
> 02 123 3/2/04 2/13/04
> 03 123 3/1/04 2/14/04
> 04 154 3/2/04 2/12/04
> 05 456 3/1/04 2/17/04
> 06 456 3/1/04 2/16/04
> 07 456 3/1/04 2/15/04
>
> I need a query that will return the 2 most recently updated rows for
> each shipment. So the results would look like the following:
> ID Shipment ETA Updated
> 02 123 3/2/04 2/13/04
> 03 123 3/1/04 2/14/04
> 04 154 3/2/04 2/12/04
> 06 456 3/1/04 2/16/04
> 05 456 3/1/04 2/17/04
> Thanks,
> Teknari|||You can write this in a couple of ways:

--#1 (using TOP clause)
SELECT *
FROM tbl
WHERE Updated IN ( SELECT TOP 2 t1.Updated
FROM tbl t1
WHERE t1.Shipment = tbl.Shipment
ORDER BY t1.Updated DESC );

--#2 (using an aggregate)
SELECT *
FROM tbl
WHERE( SELECT COUNT(*)
FROM tbl t1
WHERE t1.Shipment = tbl.Shipment
AND t1.Updated >= tbl.Updated) <= 2 ;

--
- Anith
( Please reply to newsgroups only )|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. You might want to use ISO-8601 temporal formats,
just in case you have to exchange data with someone, either human or
machine.

I also hope that you have not written your own audit logs in SQL and
that "updated" refers to the ETA and not the PHYSICAL row. There are
tools for that kind of function, which is totally outside the scope of
an application database. Likewise, I will assume that you know better
than to use any kind of auto-increment number for a key, so I assume
that the real DDL looks like this:

CREATE TABLE ShipmentHistory
(shipment_nbr INTEGER NOT NULL,
eta DATETIME NOT NULL,
revised_eta DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
PRIMARY KEY (shipment_nbr, eta, revised_eta));

>> I need a query that will return the two most recently updated rows
for each shipment. <<

SELECT H1.shipment_nbr, H1.eta, MAX(H1.revised_eta),
(SELECT MAX(H2.revised_eta)
FROM ShipmentHistory AS H2
WHERE H1.shipment_nbr = H2.shipment_nbr
AND H2.revised_eta < H1.revised_eta)
FROM ShipmentHistory AS H1
GROUP BY H1.shipment_nbr, H1.eta

This will give you a NULL if the ETA changed only once.|||Celko wrote...

<snip>
Likewise, I will assume that you know better
> than to use any kind of auto-increment number for a key
<snip
Why not use an auto increment number as a key?|||You could try this

SELECT s.*
FROM shipment_status s
INNER JOIN (SELECT shipment, MAX(Updated) max_updated
FROM shipment_status
GROUP BY shipment

UNION ALL

SELECT s1.shipment, MAX(s1.Updated)
FROM shipment_status s1
INNER JOIN (SELECT shipment, MAX(Updated) max_updated
FROM shipment_status
GROUP BY shipment) s2 ON s1.shipment = s2.shipment AND
s1.Updated < s2.max_updated
GROUP BY s1.shipment) as m_all
ON s.shipment = m_all.shipment AND s.Updated = m_all.max_updated
ORDER BY s.shipment, s.ID

"Teknari" <junk2@.bgtl.com> wrote in message
news:b1e69426.0402160742.25896bac@.posting.google.c om...
> Take the following table
> ID Shipment ETA Updated
> 01 123 3/1/04 2/12/04
> 02 123 3/2/04 2/13/04
> 03 123 3/1/04 2/14/04
> 04 154 3/2/04 2/12/04
> 05 456 3/1/04 2/17/04
> 06 456 3/1/04 2/16/04
> 07 456 3/1/04 2/15/04
>
> I need a query that will return the 2 most recently updated rows for
> each shipment. So the results would look like the following:
> ID Shipment ETA Updated
> 02 123 3/2/04 2/13/04
> 03 123 3/1/04 2/14/04
> 04 154 3/2/04 2/12/04
> 06 456 3/1/04 2/16/04
> 05 456 3/1/04 2/17/04
> Thanks,
> Teknari|||>> Why not use an auto increment number as a key? <<

I have longer rants, but the Cliff Notes version is:

1) It is PHYSICAL and not LOGICAL

2) Non-relational

3) Proprietary

4) Meaningless in the data model

5) Unverifiable in the reality of the data model

6) Dangerously redundant, assuming that the table is actually a properly
designed table.

Newbies throw this on a table to fake a key because they don't know what
a key is, they are too lazy to research their industry for standards and
it looks like a sequential file's record number. They only know files
and still think in those terms.

As an aside, if this is an audit log, then you might want to look at
third party tools which are built for audits:

http://www.sswug.org/searchresults...ofind2=lumigent

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

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

You solution seemed the most elegant (at least as far as length of
query) so I went with it.

I do not have any experience using the table t1 in your query below.
It appears to me that it is a temporary table that is identical to the
table tbl which it is placed next to in the FROM statement, but I
really don't understand this.

Can somebody explain to me how it is used below or point me to a good
tutorial on the web?

Thanks,
Teknari

"Anith Sen" <anith@.bizdatasolutions.com> wrote in message news:<g7aYb.7095$hm4.6316@.newsread3.news.atl.earthlink.n et>...
> You can write this in a couple of ways:
> --#1 (using TOP clause)
> SELECT *
> FROM tbl
> WHERE Updated IN ( SELECT TOP 2 t1.Updated
> FROM tbl t1
> WHERE t1.Shipment = tbl.Shipment
> ORDER BY t1.Updated DESC );
> --#2 (using an aggregate)
> SELECT *
> FROM tbl
> WHERE( SELECT COUNT(*)
> FROM tbl t1
> WHERE t1.Shipment = tbl.Shipment
> AND t1.Updated >= tbl.Updated) <= 2 ;|||>> I do not have any experience using the table t1 in your query below.
It appears to me that it is a temporary table that is identical to the
table tbl which it is placed next to in the FROM statement, but I really
don't understand this.<<

This is a correlation name, or alias. It lets youuse the same table in
sevreal places. This is one of the reasons that SQL used to mean
"Structured Query Language"; it is that fundamental to the language. It
acts as if the engine has made a copy of the base table under the new
name that exists for the duration o the statement.

>> ... point me to a good tutorial on the web? <<

Go to the FirstSQL website or use Google to find one.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

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

Help! First timer with SQL

I am trying to write my first app using SQL Server. I am not able to even open a connection. I get the following error message:

Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

I am not sure first of all why the user is listed as null. I hard coded a user name in for test purposes. Second, how DO I establish a trusted connection with a SQL Server. The SQL Server is located on an internal domain controller.

Also, is it necessary to have anything special installed on my remote machine? As I said, I am VERY new to Microsoft SQL ServerCan you post your conn string?|||Sure... here is the connection string. It was generated by default by the system.

data source=ERICSERVER;initial catalog=Northwind;integrated security=SSPI;persist security info=False;workstation id=ERIC;packet size=4096

Any ideas?|||ejcrammer I 2 am also in your shoes. I am new to SQL Server and ASP.NET. I also am trying to connect to a database and am receving the same error message you are. I have looked at your connection string but their does not seem to be one specific way to write the connection string because I have seen about 7 different versions all of which are not good and no one really explains them in detail. some ask for password and username some dont, I dont know. Anyway since were both in the same shoes I was wondering if you figure out this answere to this question you could post and let me know and I as well will post back if I figure out the answere to this problem we seem to be haveing.
Thanks

Help! a database can not be backed up!

Hi,
I have a 80 GB database. The backup can not be completed.
The error message were:
1. BackupMedium::ReportIoError: write failure on backup
device 'd:\gisdata\MSSQL\BACKUP\DistribuGIS_backup.BAK'.
Operating system error 33(The process cannot access the
file because another process has locked a portion of the
file.).
2. Internal I/O request 0x06614E20: Op: Write, pBuffer:
0x0A0D0000, Size: 983040, Position: 43656813056, UMS:
Internal: 0x103, InternalHigh: 0x0, Offset: 0x2A261A00,
OffsetHigh: 0xA, m_buf: 0x0A0D0000, m_len: 983040,
m_actualBytes: 0, m_errcode: 33, BackupFile:
d:\gisdata\MSSQL\BACKUP\DistribuGIS_backup.BAK
3. BACKUP failed to complete the command BACKUP DATABASE
[DistribuGIS] TO DISK = N'd:\gisdata\MSSQL\BACKUP\DistribuGIS_backup.BAK' WITH
INIT , NOUNLOAD , NAME = N'DistribuGIS_backup',
NOSKIP , STATS = 10, NOFORMAT
I checked the disk were working fine.
I checked the database by using dbcc checkdb with
repair_rebuild option. It reported me no error found.
Please help if anything could be wrong/what should I do.
Thank you in advance.try backing up the database to a different machine that
has enough space for the database. This way you can verify
if there is something wrong with the local disk array.
>--Original Message--
>Hi,
>I have a 80 GB database. The backup can not be completed.
>The error message were:
>1. BackupMedium::ReportIoError: write failure on backup
>device 'd:\gisdata\MSSQL\BACKUP\DistribuGIS_backup.BAK'.
>Operating system error 33(The process cannot access the
>file because another process has locked a portion of the
>file.).
>2. Internal I/O request 0x06614E20: Op: Write, pBuffer:
>0x0A0D0000, Size: 983040, Position: 43656813056, UMS:
>Internal: 0x103, InternalHigh: 0x0, Offset: 0x2A261A00,
>OffsetHigh: 0xA, m_buf: 0x0A0D0000, m_len: 983040,
>m_actualBytes: 0, m_errcode: 33, BackupFile:
>d:\gisdata\MSSQL\BACKUP\DistribuGIS_backup.BAK
>3. BACKUP failed to complete the command BACKUP DATABASE
>[DistribuGIS] TO DISK =>N'd:\gisdata\MSSQL\BACKUP\DistribuGIS_backup.BAK' WITH
>INIT , NOUNLOAD , NAME = N'DistribuGIS_backup',
>NOSKIP , STATS = 10, NOFORMAT
>I checked the disk were working fine.
>I checked the database by using dbcc checkdb with
>repair_rebuild option. It reported me no error found.
>
>Please help if anything could be wrong/what should I do.
>Thank you in advance.
>
>.
>|||Unfortunately, since this database is big I can not try
backing up the database to a different machine.
But I have other databases in the same machine and they
all could be backed up successfully. Since this, can I say
the local disk array is ok?
Do you think this is because that the database files are
damaged?
Thank you.
>--Original Message--
>try backing up the database to a different machine that
>has enough space for the database. This way you can
verify
>if there is something wrong with the local disk array.
>
>>--Original Message--
>>Hi,
>>I have a 80 GB database. The backup can not be
completed.
>>The error message were:
>>1. BackupMedium::ReportIoError: write failure on backup
>>device 'd:\gisdata\MSSQL\BACKUP\DistribuGIS_backup.BAK'.
>>Operating system error 33(The process cannot access the
>>file because another process has locked a portion of the
>>file.).
>>2. Internal I/O request 0x06614E20: Op: Write, pBuffer:
>>0x0A0D0000, Size: 983040, Position: 43656813056, UMS:
>>Internal: 0x103, InternalHigh: 0x0, Offset: 0x2A261A00,
>>OffsetHigh: 0xA, m_buf: 0x0A0D0000, m_len: 983040,
>>m_actualBytes: 0, m_errcode: 33, BackupFile:
>>d:\gisdata\MSSQL\BACKUP\DistribuGIS_backup.BAK
>>3. BACKUP failed to complete the command BACKUP DATABASE
>>[DistribuGIS] TO DISK =>>N'd:\gisdata\MSSQL\BACKUP\DistribuGIS_backup.BAK' WITH
>>INIT , NOUNLOAD , NAME = N'DistribuGIS_backup',
>>NOSKIP , STATS = 10, NOFORMAT
>>I checked the disk were working fine.
>>I checked the database by using dbcc checkdb with
>>repair_rebuild option. It reported me no error found.
>>
>>Please help if anything could be wrong/what should I do.
>>Thank you in advance.
>>
>>.
>.
>|||It's the .bak backup file that's the issue, not your
database. Something else is accessing the file - are you
also running NT backup at the same time? That puts a lock on
the file. If you are always hitting the error, try just
backing up to another file name.
Filemon from www.sysinternals.com should tell you what
process has a lock on the file.
-Sue
On Wed, 1 Oct 2003 09:51:23 -0700, "Juan"
<juan_wu@.hotmail.com> wrote:
>Hi,
>I have a 80 GB database. The backup can not be completed.
>The error message were:
>1. BackupMedium::ReportIoError: write failure on backup
>device 'd:\gisdata\MSSQL\BACKUP\DistribuGIS_backup.BAK'.
>Operating system error 33(The process cannot access the
>file because another process has locked a portion of the
>file.).
>2. Internal I/O request 0x06614E20: Op: Write, pBuffer:
>0x0A0D0000, Size: 983040, Position: 43656813056, UMS:
>Internal: 0x103, InternalHigh: 0x0, Offset: 0x2A261A00,
>OffsetHigh: 0xA, m_buf: 0x0A0D0000, m_len: 983040,
>m_actualBytes: 0, m_errcode: 33, BackupFile:
>d:\gisdata\MSSQL\BACKUP\DistribuGIS_backup.BAK
>3. BACKUP failed to complete the command BACKUP DATABASE
>[DistribuGIS] TO DISK =>N'd:\gisdata\MSSQL\BACKUP\DistribuGIS_backup.BAK' WITH
>INIT , NOUNLOAD , NAME = N'DistribuGIS_backup',
>NOSKIP , STATS = 10, NOFORMAT
>I checked the disk were working fine.
>I checked the database by using dbcc checkdb with
>repair_rebuild option. It reported me no error found.
>
>Please help if anything could be wrong/what should I do.
>Thank you in advance.
>|||To ensure the database is in a good state run DBCC CHECKDB against it
--
HTH
Ryan Waight, MCDBA, MCSE
"Juan" <juan_wu@.hotmail.com> wrote in message
news:192401c3884a$93780660$a001280a@.phx.gbl...
> Unfortunately, since this database is big I can not try
> backing up the database to a different machine.
> But I have other databases in the same machine and they
> all could be backed up successfully. Since this, can I say
> the local disk array is ok?
> Do you think this is because that the database files are
> damaged?
> Thank you.
> >--Original Message--
> >try backing up the database to a different machine that
> >has enough space for the database. This way you can
> verify
> >if there is something wrong with the local disk array.
> >
> >
> >>--Original Message--
> >>Hi,
> >>
> >>I have a 80 GB database. The backup can not be
> completed.
> >>The error message were:
> >>1. BackupMedium::ReportIoError: write failure on backup
> >>device 'd:\gisdata\MSSQL\BACKUP\DistribuGIS_backup.BAK'.
> >>Operating system error 33(The process cannot access the
> >>file because another process has locked a portion of the
> >>file.).
> >>
> >>2. Internal I/O request 0x06614E20: Op: Write, pBuffer:
> >>0x0A0D0000, Size: 983040, Position: 43656813056, UMS:
> >>Internal: 0x103, InternalHigh: 0x0, Offset: 0x2A261A00,
> >>OffsetHigh: 0xA, m_buf: 0x0A0D0000, m_len: 983040,
> >>m_actualBytes: 0, m_errcode: 33, BackupFile:
> >>d:\gisdata\MSSQL\BACKUP\DistribuGIS_backup.BAK
> >>
> >>3. BACKUP failed to complete the command BACKUP DATABASE
> >>[DistribuGIS] TO DISK => >>N'd:\gisdata\MSSQL\BACKUP\DistribuGIS_backup.BAK' WITH
> >>INIT , NOUNLOAD , NAME = N'DistribuGIS_backup',
> >>NOSKIP , STATS = 10, NOFORMAT
> >>
> >>I checked the disk were working fine.
> >>I checked the database by using dbcc checkdb with
> >>repair_rebuild option. It reported me no error found.
> >>
> >>
> >>Please help if anything could be wrong/what should I do.
> >>
> >>Thank you in advance.
> >>
> >>
> >>.
> >>
> >.
> >|||Hi,
It got backed up successfully to a different folder. But
in the meantime, I used to back it up as compressed file
since the shortage of my disk and this time I backed it up
as normal file. Do you think the compression also possibly
cause problem?
I will try the Filemon once I got a chance.
Thank you so much for your help.
--Juan
>--Original Message--
>It's the .bak backup file that's the issue, not your
>database. Something else is accessing the file - are you
>also running NT backup at the same time? That puts a lock
on
>the file. If you are always hitting the error, try just
>backing up to another file name.
>Filemon from www.sysinternals.com should tell you what
>process has a lock on the file.
>-Sue
>On Wed, 1 Oct 2003 09:51:23 -0700, "Juan"
><juan_wu@.hotmail.com> wrote:
>>Hi,
>>I have a 80 GB database. The backup can not be
completed.
>>The error message were:
>>1. BackupMedium::ReportIoError: write failure on backup
>>device 'd:\gisdata\MSSQL\BACKUP\DistribuGIS_backup.BAK'.
>>Operating system error 33(The process cannot access the
>>file because another process has locked a portion of the
>>file.).
>>2. Internal I/O request 0x06614E20: Op: Write, pBuffer:
>>0x0A0D0000, Size: 983040, Position: 43656813056, UMS:
>>Internal: 0x103, InternalHigh: 0x0, Offset: 0x2A261A00,
>>OffsetHigh: 0xA, m_buf: 0x0A0D0000, m_len: 983040,
>>m_actualBytes: 0, m_errcode: 33, BackupFile:
>>d:\gisdata\MSSQL\BACKUP\DistribuGIS_backup.BAK
>>3. BACKUP failed to complete the command BACKUP DATABASE
>>[DistribuGIS] TO DISK =>>N'd:\gisdata\MSSQL\BACKUP\DistribuGIS_backup.BAK' WITH
>>INIT , NOUNLOAD , NAME = N'DistribuGIS_backup',
>>NOSKIP , STATS = 10, NOFORMAT
>>I checked the disk were working fine.
>>I checked the database by using dbcc checkdb with
>>repair_rebuild option. It reported me no error found.
>>
>>Please help if anything could be wrong/what should I do.
>>Thank you in advance.
>.
>|||I'm not sure Juan - I've never messed with compression for
database backups. Filemon should show you what's locking the
file though and if it's a system process, you'll see that.
It's a good tool to have and pretty easy to use.
-Sue
On Thu, 2 Oct 2003 14:01:20 -0700, "Juan"
<juan_wu@.hotmail.com> wrote:
>Hi,
>It got backed up successfully to a different folder. But
>in the meantime, I used to back it up as compressed file
>since the shortage of my disk and this time I backed it up
>as normal file. Do you think the compression also possibly
>cause problem?
>I will try the Filemon once I got a chance.
>Thank you so much for your help.
>--Juan
>>--Original Message--
>>It's the .bak backup file that's the issue, not your
>>database. Something else is accessing the file - are you
>>also running NT backup at the same time? That puts a lock
>on
>>the file. If you are always hitting the error, try just
>>backing up to another file name.
>>Filemon from www.sysinternals.com should tell you what
>>process has a lock on the file.
>>-Sue
>>On Wed, 1 Oct 2003 09:51:23 -0700, "Juan"
>><juan_wu@.hotmail.com> wrote:
>>Hi,
>>I have a 80 GB database. The backup can not be
>completed.
>>The error message were:
>>1. BackupMedium::ReportIoError: write failure on backup
>>device 'd:\gisdata\MSSQL\BACKUP\DistribuGIS_backup.BAK'.
>>Operating system error 33(The process cannot access the
>>file because another process has locked a portion of the
>>file.).
>>2. Internal I/O request 0x06614E20: Op: Write, pBuffer:
>>0x0A0D0000, Size: 983040, Position: 43656813056, UMS:
>>Internal: 0x103, InternalHigh: 0x0, Offset: 0x2A261A00,
>>OffsetHigh: 0xA, m_buf: 0x0A0D0000, m_len: 983040,
>>m_actualBytes: 0, m_errcode: 33, BackupFile:
>>d:\gisdata\MSSQL\BACKUP\DistribuGIS_backup.BAK
>>3. BACKUP failed to complete the command BACKUP DATABASE
>>[DistribuGIS] TO DISK =>>N'd:\gisdata\MSSQL\BACKUP\DistribuGIS_backup.BAK' WITH
>>INIT , NOUNLOAD , NAME = N'DistribuGIS_backup',
>>NOSKIP , STATS = 10, NOFORMAT
>>I checked the disk were working fine.
>>I checked the database by using dbcc checkdb with
>>repair_rebuild option. It reported me no error found.
>>
>>Please help if anything could be wrong/what should I do.
>>Thank you in advance.
>>
>>.

Help! - Query On Time Field In SQL Server - How?

Hi,

I have an orders database and I need to be able to write a query
that groups sales by:

1. Date
2. Time of day (both am and pm)

I currently have a date/time field named "Submitted" that stores
just the date.

However, how do I store the time?

I know I can store the date/time in the "Submitted" field I
created. However, because of the time being stored in that field, I
can't execute an easy query such as:

select * from database where submitted = '2/10/05'

(reason being, the time portion makes each "Submitted" field
record unique, so just the date won't be recognized)

I realize there probably is a much better way to do this in the
SQL query language that I am not aware of.

Two questions:

1. Based on what I'm trying to do -- query based on date and group
by number of sales for each hour of the day (am and pm) -- what is the
best approach to store the time?

2. What does the query look like that querys by date and groups
orders by time of day (am and pm)?

Thanks for your help.

JThe examples below should help. Notice the Y-M-D formats I have used
for date literals. These formats are safe under any regional connection
settings whereas other formats used in code (such as in the example you
gave) may fail depending on the settings of DATEFORMAT and LANGUAGE.

CREATE TABLE YourTable (dt DATETIME PRIMARY KEY)

INSERT INTO YourTable VALUES ('2004-02-10T09:00:00.000')
INSERT INTO YourTable VALUES ('2004-02-10T10:00:00.000')

/* Retrieve a particular date */
SELECT *
FROM YourTable
WHERE dt >= '20050210'
AND dt < '20050211'

/* Group by date and hour */
SELECT MIN(dt), COUNT(*)
FROM YourTable
GROUP BY DATEDIFF(HH,'20000101',dt)

--
David Portas
SQL Server MVP
--

Help!

Okay I'm totally new to SQL, I need to write a program that has 2 tables, can any one help me get started?

Quote:

Originally Posted by ghostrider

Okay I'm totally new to SQL, I need to write a program that has 2 tables, can any one help me get started?

Can you give us any more specifics?

What kind of program are you writing, what are the specs? What platform/programming language are you using?|||I'm using MS SQL 2005 Express. I'm trying to write a INSERT statement pulling data from my online school database and I'm not sure how to do it.|||

Quote:

Originally Posted by ghostrider

Okay I'm totally new to SQL, I need to write a program that has 2 tables, can any one help me get started?

Hi,
can you tell me clearly your requirements.really you want help for creating tables|||

Quote:

Originally Posted by ghostrider

I'm using MS SQL 2005 Express. I'm trying to write a INSERT statement pulling data from my online school database and I'm not sure how to do it.

If I understand correctly, you are trying to insert data from one table into another.

If these tables exist on different platforms you will need to write an application that does this.

I will assume both tables are in the same database.

INSERT INTO Table1(Column1,Column2)
SELECT Column1,Column2
FROM Table2|||Thanks this is what I was looking for. Thanks again!!!!

Help writing a report query

OK, I have to write a SQL query for someone, for a report. There's financial values involved. There's three tables, essentially. Call them account names, account types, and transactions. I need to return the value of each transaction with the transactions, the sum of the transactions for the account, and the sum of all account transactions across all account types for the account name. I'm not sure how best to do this, I'm thinking of creating a temporary table at the bottom level ( where the actual data is ) and joining against it to do a SUM on the higher levels. To do that, I seem to need to do group by, which then means I need to specify all my non grouped column names, is that right ?

Can you post a bit more info, particularly a small sample set of data to work with and what you want the results to be? I am having a bit of trouble envisioning what you want.

|||

OK, it works something like this:

table ShareInfo

ShareInfoId int

CurrentPrice int

table SharePurchase

SharePurchaseId int

PortfolioId int

ShareInfoId int

SharesPurchased int

PricePerShare int

table SharePortfolio

PortfolioId int

and some other columns for names, etc. So, on the bottom level, I want to list all share purchases within a portfolio, and how their value has changed in each instance to toay. This data will be collapsible on the report, and the row that shows always will show the share name, and the total value of shares purchased, and amount lost/gained. This data is also collapsible, and on the top level, I show the Portfolio name, and the total value/total amount lost or gained on the portfolio to date.

|||

Hi you can use the ROLLUP operator,

Example,
Create table #Shares
(
PortfolioId int,
ShareId int,
NoOfShares int,
TimePurchased varchar(10),
SharesPerPrice int
)

Insert Into #Shares values(1,1,10,'10:00 AM',30);
Insert Into #Shares values(1,2,100,'10:00 AM',5);
Insert Into #Shares values(1,2,50,'11:00 AM',6);
Insert Into #Shares values(1,3,112,'10:00 AM',5);
Insert Into #Shares values(1,4,112,'10:00 AM',5);

Select
PortfolioId
,ShareId
,TimePurchased
,Sum(NoOfShares)
,Sum(SharesPerPrice)
,Sum(NoOfShares * SharesPerPrice)
from
#Shares
Group By PortfolioId, ShareId, TimePurchased With Rollup

OUTPUT:

PortfolioId ShareId TimePurchased NoOfShares SharesPerPrice Totalvalue
-- -- - -- -- --
1 1 10:00 AM 10 30 300
1 1 NULL 10 30 300
1 2 10:00 AM 100 5 500
1 2 11:00 AM 50 6 300
1 2 NULL 150 11 800
1 3 10:00 AM 112 5 560
1 3 NULL 112 5 560
1 4 10:00 AM 112 5 560
1 4 NULL 112 5 560
1 NULL NULL 384 51 2220
NULL NULL NULL 384 51 2220

If TimePurchased is NOT NULL then it is Down Level Data
If TimePurchased is NULL and ShareId is not null then it is One level Collopsed from the Down Level
If TimePurchased is NULL and ShareId is NULL and PortfolioId is not NULL then it is at Portfolio Level

|||

OK - that looks good, but how would I then populate my report from this ? I expected I'd return three tables, one for each level.

Thanks for helping...

|||

Here you can change the query as follow as,

Select
PortfolioId
,ShareId
,TimePurchased
,SUM(NoOfShares) NoOfShares
,SUM(SharesPerPrice) SharesPerPrice
,Sum(NoOfShares * SharesPerPrice) TotalValue
INTO #RESULT
from
Shares
Group By PortfolioId, ShareId, TimePurchased wITH ROLLUP
Select PortfolioId,ShareId,TimePurchased,NoOfShares,SharesPerPrice,TotalValue from #RESULT where TimePurchased is not null

Select PortfolioId,ShareId,NoOfShares,SharesPerPrice,TotalValue from #RESULT where TimePurchased is Null And ShareId is NOT NULL

Select PortfolioId,NoOfShares,SharesPerPrice,TotalValue from #RESULT where TimePurchased is Null And ShareId is NULL And PortfolioId is Not Null

Help writing a query.

Hello,

I am trying to write a query and getting a little confused.

My problem:
I send enquiries to partners from 12:00am to 11:59pm each day. I have ten
partners, and each partner has a different daily limit. In my partner table
(Table1), I have a columnm for the Daily Enquiry Limit called "DailyCap". I
also have a second table (Table2) which counts how many enquiries a partner
has had and assigns a date stamp.So, my query needs to:

Select PARTNER
where Table2.PartnerCount is less than Table1.DailyCap
AND where Table2.TimeStamp between 12:00am TODAY and 11:59pm TODAY.

This should hopefully then select any partner who hasent yet reached the
daily cap between midnight start and 24 hours later.

Any odeas how to write this query using proper SQL? I have tried but
failed.

Regards,

Gary.I'll assume your tables look like this:

CREATE TABLE Partners (partner_no INTEGER PRIMARY KEY, partner_name
VARCHAR(20) NOT NULL UNIQUE, dailycap INTEGER NOT NULL CHECK (dailycap>=0))

CREATE TABLE PartnerEnquiries (partner_no INTEGER REFERENCES Partners
(partner_no), enquiry_dt DATETIME, PRIMARY KEY (partner_no, enquiry_dt))

Here's the query:

SELECT P.partner_name, MAX(enquiry_dt)
FROM Partners AS P
LEFT JOIN PartnerEnquiries AS E
ON P.partner_no = E.partner_no
AND E.enquiry_dt >=CONVERT(CHAR(8),CURRENT_TIMESTAMP,112)
AND E.enquiry_dt <DATEADD(DAY,1,CONVERT(CHAR(8),CURRENT_TIMESTAMP,11 2))
GROUP BY P.partner_no, P.partner_name, P.dailycap
HAVING COUNT(E.partner_no) < P.dailycap

--
David Portas
SQL Server MVP
--|||Hi David,

Thanks for the time you have taken to reply. I am fairly new to SQL, could
you or perhaps another NG user comment on the code you have provided and let
me know what all the elements are and how they work? It looks quite
complex!!!

Thanks,

Gary.

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:uqedne4yoJAjPLnd4p2dnA@.giganews.com...
> I'll assume your tables look like this:
> CREATE TABLE Partners (partner_no INTEGER PRIMARY KEY, partner_name
> VARCHAR(20) NOT NULL UNIQUE, dailycap INTEGER NOT NULL CHECK
(dailycap>=0))
> CREATE TABLE PartnerEnquiries (partner_no INTEGER REFERENCES Partners
> (partner_no), enquiry_dt DATETIME, PRIMARY KEY (partner_no, enquiry_dt))
> Here's the query:
> SELECT P.partner_name, MAX(enquiry_dt)
> FROM Partners AS P
> LEFT JOIN PartnerEnquiries AS E
> ON P.partner_no = E.partner_no
> AND E.enquiry_dt >=CONVERT(CHAR(8),CURRENT_TIMESTAMP,112)
> AND E.enquiry_dt
<DATEADD(DAY,1,CONVERT(CHAR(8),CURRENT_TIMESTAMP,11 2))
> GROUP BY P.partner_no, P.partner_name, P.dailycap
> HAVING COUNT(E.partner_no) < P.dailycap
> --
> David Portas
> SQL Server MVP
> --|||Here it is again with comments. You can refer to Books Online for the
meaning of particular keywords.

SELECT P.partner_name, MAX(enquiry_dt) /* latest datetime */
FROM Partners AS P
/* Left join because maybe not every partner has an enquiry: */
LEFT JOIN PartnerEnquiries AS E
ON P.partner_no = E.partner_no
/* Include rows only where Enquiry_dt is today: */
AND E.enquiry_dt >=CONVERT(CHAR(8),CURRENT_TIMESTAMP,112)
AND E.enquiry_dt <DATEADD(DAY,1,CONVERT(CHAR(8),CURRENT_TIMESTAMP,11 2))
GROUP BY P.partner_no, P.partner_name, P.dailycap
/* where the row count from the enquires table is < dailycap: */
HAVING COUNT(E.partner_no) < P.dailycap

I have assumed that you have a row in PartnerEnquiries for each enquiry and
that you want to count those rows and compare to dailycap. That part wasn't
entirely clear to me from your original post.

--
David Portas
SQL Server MVP
--

Help with UDF

Hi,
I've not created one of these before so if anyone can help me get this up
and running that would be great. I want to write a function to obtain the
exchange rate from a table based on three parameters - exhange rate, country
& year.
The complication I have is I want to pass in the value 1 for Euro and 2 for
Dollar so in the function I need an "if" statement to run the appropiate
select statement. So in the function it would be something like this...
if @.iExchangeRate = 1
BEGIN
select Euro from ExchangeRates where CountryID = 35 and YearID = 5
END
ELSE
BEGIN
select Dollar from ExchangeRates where CountryID = 35 and YearID = 5
END
I had a go at creating a UDF based on the above and I get warnings around
the IF statement when creating the function. Is it possible to have
conditions in the UDF.
thanks,
jpYou can have IF in a multi-statement table values function but as you say yo
u got en error, I assume
you are trying an in-line table valued function. You should try to make your
functions in-line as
they can potentially perform much better. But why not use only one SELECT st
atement and use CASE
inside that to do what you want?
CREATE TABLE ExchangeRates (Euro money NULL, Dollar money NULL)
INSERT INTO ExchangeRates VALUES(10, 12)
INSERT INTO ExchangeRates VALUES(NULL, 14)
INSERT INTO ExchangeRates VALUES(15, NULL)
INSERT INTO ExchangeRates VALUES(NULL, NULL)
CREATE FUNCTION x (@.iExchangeRate tinyint)
RETURNS table
AS
RETURN
(
SELECT
CASE
WHEN @.iExchangeRate = 1
THEN Euro
ELSE Dollar
END AS ExchangeRate
FROM ExchangeRates
)
SELECT * FROM x(1)
SELECT * FROM x(2)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jp" <jpfrancoisuk@.yahoo.co.uk> wrote in message news:Cs_Gf.3414$gB4.2385@.newsfe4-gui.ntli.
net...
> Hi,
> I've not created one of these before so if anyone can help me get this up
and running that would
> be great. I want to write a function to obtain the exchange rate from a ta
ble based on three
> parameters - exhange rate, country & year.
> The complication I have is I want to pass in the value 1 for Euro and 2 fo
r Dollar so in the
> function I need an "if" statement to run the appropiate select statement.
So in the function it
> would be something like this...
> if @.iExchangeRate = 1
> BEGIN
> select Euro from ExchangeRates where CountryID = 35 and YearID = 5
> END
> ELSE
> BEGIN
> select Dollar from ExchangeRates where CountryID = 35 and YearID = 5
> END
> I had a go at creating a UDF based on the above and I get warnings around
the IF statement when
> creating the function. Is it possible to have conditions in the UDF.
> thanks,
> jp
>|||Thanks - got it working with your help.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23btVNfjLGHA.3960@.TK2MSFTNGP09.phx.gbl...
> You can have IF in a multi-statement table values function but as you say
> you got en error, I assume you are trying an in-line table valued
> function. You should try to make your functions in-line as they can
> potentially perform much better. But why not use only one SELECT statement
> and use CASE inside that to do what you want?
> CREATE TABLE ExchangeRates (Euro money NULL, Dollar money NULL)
> INSERT INTO ExchangeRates VALUES(10, 12)
> INSERT INTO ExchangeRates VALUES(NULL, 14)
> INSERT INTO ExchangeRates VALUES(15, NULL)
> INSERT INTO ExchangeRates VALUES(NULL, NULL)
> CREATE FUNCTION x (@.iExchangeRate tinyint)
> RETURNS table
> AS
> RETURN
> (
> SELECT
> CASE
> WHEN @.iExchangeRate = 1
> THEN Euro
> ELSE Dollar
> END AS ExchangeRate
> FROM ExchangeRates
> )
> SELECT * FROM x(1)
> SELECT * FROM x(2)
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "jp" <jpfrancoisuk@.yahoo.co.uk> wrote in message
> news:Cs_Gf.3414$gB4.2385@.newsfe4-gui.ntli.net...
>