|
|
Hi Colin,
here's what i came up with:
select distinct a.officer as Officer, d.close_date as [Date], count(d.open_date)as [Open], count(d.close_date) as Closed, count(can_date)as Cancelled
from a10 a inner join escrow d on a.escrow = d.escrow inner join e120 j on j.escrow = d.escrow where a.officer is not null group by a.officer, d.close_date
Nino
|
|
|
|
|
|
I am looking for some direction about how to keep a table in a SQL server db (2000 or above) of fixed size.
Requirements:
Keep n records, Keep t date/time range of records, keep priority records etc...
a) Should I delete old records on insert, ie. stored proc?
b) Should I delete old records with a Job/External process?
c) Should I just use the the old "DELETE FROM tablename" command?
Since I will be filling the table to it's limit often, and deleting the old contents should I clean up the transaction logs as well? When? How?
I am just looking for some general direction.
.............................
There's nothing like the sound of incoming rifle and mortar rounds to cure the blues. No matter how down you are, you take an active and immediate interest in life.
Fiat justitia, et ruat cælum
|
|
|
|
|
Use stored procedures to only return the set size.
Depending on tps requirements you can then write a trigger or a job to run and clear the table removing old elements. I would use the job if you can get a way with it. The trigger, however, will always keep the table the right size.
A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."
-- Stephen Crane
|
|
|
|
|
You can use the following script to keep the table 10000 rows.
using Indentity
<br />
DELETE from BigTable<br />
Where ID NOT in ( SELECT Top(10000) from BigTable ORDER BY ID DESC )<br />
Please test first with a test table.
Look where you want to go not where you don't want to crash.
Bikers Bible
|
|
|
|
|
Are you sure he should first test on a test test table before testing on a test table. One can never be to careful :)
A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."
-- Stephen Crane
|
|
|
|
|
Thanks all I will give the suggestions a try.
.............................
There's nothing like the sound of incoming rifle and mortar rounds to cure the blues. No matter how down you are, you take an active and immediate interest in life.
Fiat justitia, et ruat cælum
|
|
|
|
|
Quite Quite Sure !
Look where you want to go not where you don't want to crash.
Bikers Bible
-- modified at 9:54 Tuesday 18th July, 2006
|
|
|
|
|
Please call me skippy
Look where you want to go not where you don't want to crash.
Bikers Bible
|
|
|
|
|
Frank Kerrigan wrote: Please test first with a test table.
Test? I think you mean test in Production! (is it Friday yet?) 
I'd love to help, but unfortunatley I have prior commitments monitoring the length of my grass. :Andrew Bleakley:
|
|
|
|
|
Not sure if this is a widely-know issue, but the XML structure for a DataSet written by calling WriteXml() method does not write elements for NULL values. Now when calling ReadXml() an integer column for example will have a value of 0 instead of NULL.
Is anyone aware of this issue?
And is there a simple solution?
thanks
"Nothing ever changes by staying the same." - David Brent (BBC's The Office) ~ ScrollingGrid: A cross-browser freeze-header control for the ASP.NET DataGrid
|
|
|
|
|
I don't think MS supports null. Their XSD.exe application likes to use xSpecified where x is the name of the attribute. Which is SO much better in my opinion.
A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."
-- Stephen Crane
|
|
|
|
|
.NET has a DbNull class for NULL values in datareaders and datasets... I just don't get why they're dropped in the serialisation.
"Nothing ever changes by staying the same." - David Brent (BBC's The Office) ~ ScrollingGrid: A cross-browser freeze-header control for the ASP.NET DataGrid
|
|
|
|
|
That doesn't mean they work for MS XML. I use dbnull all the time against oracle and SQL server, however.
Do you get a generated Specified attribute or element?
A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."
-- Stephen Crane
|
|
|
|
|
Ennis Ray Lynch, Jr. wrote: Specified attribute or element?
Not sure what you mean by this... but I don't think so. The schema has this:
<xs:element name="TxnID" type="xs:long" minOccurs="0" />
Now normally for each row you would get the values- e.g.: <TxnID>2</TxnID>
But if it's NULL you don't get this element. And then when reading it into a DataSet it will automatically have the value: 0 - which is not what the original dataset had.
The solution seems to be to read the schema, set a default value, and then read the data. But is there a simpler solution? Is this a bug, or intentional behaviour by microsoft experts? .NET 2.0 seems to have the same behaviour.
"Nothing ever changes by staying the same." - David Brent (BBC's The Office) ~ ScrollingGrid: A cross-browser freeze-header control for the ASP.NET DataGrid
|
|
|
|
|
Using XSD.exe MS will give the following results which leads me to believe they do not like null.
<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified" attributeFormDefault="unqualified">
<xs:element name="sample">
<xs:complexType>
<xs:attribute name="maxQuantity" type="xs:int" use="optional" />
</xs:complexType>
</xs:element>
</xs:schema>
will generate
//------------------------------------------------------------------------------
// <autogenerated>
// This code was generated by a tool.
// Runtime Version: 1.1.4322.2032
//
// Changes to this file may cause incorrect behavior and will be lost if
// the code is regenerated.
// </autogenerated>
//------------------------------------------------------------------------------
//
// This source code was auto-generated by xsd, Version=1.1.4322.2032.
//
using System.Xml.Serialization;
/// <remarks/>
[System.Xml.Serialization.XmlRootAttribute(Namespace="", IsNullable=false)]
public class sample {
/// <remarks/>
[System.Xml.Serialization.XmlAttributeAttribute()]
public int maxQuantity;
/// <remarks/>
[System.Xml.Serialization.XmlIgnoreAttribute()]
public bool maxQuantitySpecified;
}
A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."
-- Stephen Crane
|
|
|
|
|
Interesting, thanks. They must equate NULL with 'not specified'.
"Nothing ever changes by staying the same." - David Brent (BBC's The Office) ~ ScrollingGrid: A cross-browser freeze-header control for the ASP.NET DataGrid
|
|
|
|
|
really my sample probably doesn't directly apply to your situation but it does illustrate where MS seems to be going with nulls in general. I don't believe there XML validator even supports nillable.
A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."
-- Stephen Crane
|
|
|
|
|
I have two identical tables in which the columns and data types are of same type.
But one table is having some data whereas the other table is empty. Both are existing tables. I want to transfer/copy all the data from table1 to table2 by using a query.
How can I do this with a query
Thanks,
Sandeep S. Sekhon
|
|
|
|
|
|
Hi,
How can I add additional data to SQL Server 2k login.
I have a database with logins/users for each person that can login I need to add extra information and create a relation (based user login or index) to other tables in my database.
Sample:
"document" has an author, author is a user that can login to SQL server.
How can I add relationship from "document" table to SQL server login?
Thanks for any suggestions how to do that.
If this is not a good idea to create a relationship between SQL server login and other tables, is there other solution for similar problem?
regards
dobrzan
|
|
|
|
|
|
What I need is to extend the basic login/account.
There is a number of accounts/logins that have access to database.
Each user (which is a login/account) can do some things depending on other data collected in database.
Example:
There is a "customer" table in database. Each "customer" can order "product". There is relationship between customer and product - lets say it's done by "order" table.
Now when customer connect to database (using his SQL Server login) I want to have a view that shows what "product" "customer" ordered. So there must be a some kind of way to link SQL Server login with entries from "customer" table.
It's quiet simple thing, when I log to codeproject I can track my posts. I can do that without SQL Server, but I want to create secure app - based on SQL Server Login authentication.
|
|
|
|
|
Since all this will be done through an application* you let the business logic in the application (or in stored procedures) filter the data returned to the user of the application.
dobrzan wrote: but I want to create secure app - based on SQL Server Login authentication.
Current advice is not to use SQL Server Authentication unless you are connecting it to systems that are not windows based. You should be using trusted connections with Windows Authentication. What you are suggesting is less likely to secure your system because your users will have some direct access to your SQL Server (even if they don't know it). This access can be used by an attacker to compromise your system. All an attacker needs is an account to your application and you automatically give them an account to the datbase.
If you have a web application, it will probably try to connect to SQL Server using the ASPNET account if you let it use a trusted connection. Let it do that. Create your own user tables to handle the users of your application. Do not use the sysusers table in SQL Server, it is not designed for that purpose.
you are not going to give your customers direct logins to your SQL Server as that would be nuts - Imagine if Code Project added a login in its SQL Server for each of its 3million+ members - it also screws with connection pooling making it very inefficient
Scottish Developers events:
* .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy
* Developer Day Scotland: are you interested in speaking or attending?
My: Website | Blog
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
