|
I'm with JSchell here, it really is a requirements decision rather than a general rule. Different databases serving different types of business needs require different delete strategies.
I often use a disabled flag on the record but never make it a general rule. About the only general rules we have are an audit trigger (spit I hate triggers) and Modified/ModifiedBy on every table.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I would create a View with a WHERE IsDeleted = false condition and use the view in my select methods. And I would also make sure that the IsDeleted column is indexed.
|
|
|
|
|
hi all
suppose i have a table named "PlaceHolder" with this fields
ID int
TableName nvarchar
FieldName nvarchar
these fields will indicate specified table.field in my databse
so i want to retrieve them and use it`s content as a tableName.fieldName in my select transaction
SELECT [SQL1].[NewFieldName]------> REPLACED WITH '[TableName].[FieldName]' RETREIEVED BY INNER SELECT FROM
(SELECT '['+[TableName]+']' + TableName + '.['+[FieldName]+']' NewFieldName
FROM [PlaceHolder]
WHERE ....
) AS SQL1
WHERE ...
it will show me the names of some [tables].[field] but i want it`s values in these fields
|
|
|
|
|
Construct a dynamic query in a string and use EXECUTESQL to execute the query.
|
|
|
|
|
I prefer to use String.Format to construct something like that; all those quotes, plusses, and brackets get in the way of understanding what's going on.
Otherwise it should work fine.
|
|
|
|
|
Hi try this
Input:
Declare @tblPlaceholder table(ID int,TableName Nvarchar(20),FieldName Nvarchar(20))
insert into @tblPlaceholder
select 1,'spt_values','name' union all select 1,'spt_values','number' union all
select 2,'spt_monitor','lastrun' union all select 2,'spt_monitor','cpu_busy'
--select * from @tblPlaceholder
Query:
Declare @t table(ID int,Query VARCHAR(2000))
Declare @QueryList VARCHAR(2000)
Declare @i int
set @i = 1
-- Step 1: Build the query and insert the same into a table variable
Insert into @t
Select ID, Query = ' Select ' + FieldNames + ' from ' + TableName
From
(
Select ID,TableName, FieldNames = Stuff((select ',' + CAST(TableName as Nvarchar(20)) + '.' + CAST(FieldName as Nvarchar(20))
from @tblPlaceholder t2 where t2.ID = t1.ID
FOR XML PATH('')),1,1,'')
From @tblPlaceholder t1
Group By t1.ID,t1.TableName)X
-- Step 2 : loop thru the ID and execute the queries
While (@i <= 2) -- since there are two id's.. this can even be configurable as max(id)
Begin
SELECT @QueryList = (Select Query from @t where ID = @i)
exec(@QueryList)
set @i += 1
End
/* Final output
Output of Select spt_values.name,spt_values.number from spt_values
name number
(rpt) -32768
YES OR NO -1
SYSREMOTELOGINS TYPES -1
SYSREMOTELOGINS TYPES (UPDATE) -1
Output of Select spt_monitor.lastrun,spt_monitor.cpu_busy from spt_monitor
lastrun cpu_busy
2008-07-0916:46:13.877 10
*/
I have given the demonstration by using two system tables (spt_values and spt_monitor) found in the Master database.
The comments about the steps are mentioned in the code itself. Hope this will help you
Let me know in case of any concern.
Thanks
Niladri Biswas
|
|
|
|
|
hi
thanx to ur answer
it took a time fore me to understand what just going on
i will try it and answer back
|
|
|
|
|
Hi,
When I run the following select getdate(), I'm getting the value below:
2011-07-04 13:54:22.427
I want to get only the value 2011-07-04...
Can anybody advise?
Note: I don't want to convert it to varchar.
Cheers
|
|
|
|
|
|
If you are using SQL Server 2008 then use the date datatype
select cast(getdate() as date)
returns 2011-07-04
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
|
|
|
|
|
|
Without using the convert function to manipulate the date then your best answer is from thatraja
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
|
|
|
|
|
SELECT DATEADD(dd,0,DATEDIFF(dd,0,GETDATE())) does the trick.
|
|
|
|
|
A simple code as below:
<script language="C#" runat="server">
void Page_Load(object sender, EventArgs e)
{
try
{
SqlConnection objConnection = new SqlConnection("Data Source=MySQLServer;Initial Catalog=gqs;Integrated Security=True;");
objConnection.Open();
}
catch (SqlException objException)
{
Response.Write(objException.Message);
}
Response.Write("<br><Br>The page is executing as " +Thread.CurrentPrincipal.Identity.Name);
}
</script>
gives the following output:
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
The page is executing as MyDepartmentDomain\deepak
When the thread is running as me how does SSPI pickup Anonymous Logon?
Vasudevan Deepak Kumar
Personal Homepage Tech Gossips
The woods are lovely, dark and deep,
But I have promises to keep,
And miles to go before I sleep,
And miles to go before I sleep!
|
|
|
|
|
The issue is because the thing that's actually connecting to the database is the IIS worker process, which is running with the anonymous login here.
|
|
|
|
|
Dear Pete,
The Virtual Directory has 'Anonymous Authentication' disabled. It is a Windows Server 2008 R2. The virtual directory has only Windows Authentication enabled.
Vasudevan Deepak Kumar
Personal Homepage Tech Gossips
The woods are lovely, dark and deep,
But I have promises to keep,
And miles to go before I sleep,
And miles to go before I sleep!
|
|
|
|
|
As Pete said, this is the identity of the AppDomain. Its got nothing to do with the authentication mode of the website or virtual directory.
|
|
|
|
|
There is a reason you rarely see question about integrated security in a database forum, almost no one uses it. The normal design is for the application to have a SQL userid/password that is authorised to access the parts of the database required. I have never seen anything but a test/toy application use integrated security.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Dear Mycroft,
This is a kind of legacy product that the folks were re-engineering. I have suggested them to use a variant of Basic Auth over SSL. A quick proto seems to be working out too.
I admit your stand that 'Integrated Security' is a horrendous flop-show staged by SQL Server.
Vasudevan Deepak Kumar
Personal Homepage Tech Gossips
The woods are lovely, dark and deep,
But I have promises to keep,
And miles to go before I sleep,
And miles to go before I sleep!
|
|
|
|
|
Mycroft Holmes wrote: <layer>almost no one uses it. <layer>The normal design is for the application to have a SQL userid/password that is authorised to access the parts of the database required
You think SQL authentication is preferable to Windows Authentication in a production environment? Or did I misunderstand?
|
|
|
|
|
Nope you did not misunderstand, in over 20 years of database development I have only twice seen windows authentication used and they were both small organisations.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Ok, I have an even smaller sample size than you, but in 15 years of SQL/web development i have only once seen SQL authenttication used.
The wisdom passed to be by my elders was that windows authentication could be kept significantly more secure, primarily by centralising policies across (potentially) multiple instance of sql server.
Almost everywhere ive ever worked has specifically not installed sql with mixed mode security, making the use of sql authentication actually impossible.
Edit: this article seems to backup my claims above: http://databases.about.com/od/sqlserver/a/authentication.htm[^]
"Microsoft’s best practice recommendation is that you use Windows authentication mode whenever possible. The main benefit is that the use of this mode allows you to centralize account administration for your entire enterprise in a single place: Active Directory. This dramatically reduces the chances of error or oversight.
For example, consider the scenario where a trusted database administrator leaves your organization on unfriendly terms. If you use Windows authentication mode, revoking that user’s access takes place automatically when you disable or remove the DBA’s Active Directory account. If you use mixed authentication mode, you not only need to disable the DBA’s Windows account, but you also need to comb through the local user listings on each database server to ensure that no local accounts exist where the DBA may know the password. That’s a lot of work! "
Edit2: The general wisdom seems to be that if you support multiple platforms connecting to SQL, then SQL Authentication is your only option. If all clients are on a windows domain, use windows auth. (source: http://blogs.msdn.com/b/jjameson/archive/2007/03/23/sql-server-authentication-modes.aspx[^]).
Its certainly got nothing to do with company size or programmer experience!
|
|
|
|
|
Your arguments are perfectly valid right up until they meet the inertia of outsourced support where it can take 2 weeks to get a new user group creaded in AD. You need 15 pages of forms and 2 interviews to justify the group.
I need to respond to user requirements in hour (or quicker) not in days/weeks. And yeah I seen a multipage exit document signoff.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
So its basically your very specific situation which precludes you using the prefered method of security. This is totally different from what you originally said:
almost no one uses it
and
test/toy application use integrated security
|
|
|
|
|
Nope I tend to work for large organisations that have a huge amount of inertia. While integrated may be the preferred method I have never seen it implemented in a large organisatrion.
J4amieC wrote: very specific situation
I just finished arguing with IT so my example may have been a little narrow
Never underestimate the power of human stupidity
RAH
|
|
|
|