MS User wrote:
> SQL Server 2000
> We have a db maintenance job which backup all the db's in a Production
> Server to a remote server.
> Production Server - Windows 2000 server
> Remote Box - Windows 2003 Standard Edition
> We recently applied SP1 to Windows 2003 box (Remote Box), after that Db
> maintenance job with error message 'Operating system error 64 (The specifi
ed
> network is no longer available..)
> Certain days it will backup all databases but with this error message.
> Any thoughts/suggestions/ideas
> Thanks
> Mike
>
I'm not sure this is a SQL problem, sounds more like a network or OS
problem on the remote side. You're losing the connection to the remote
box during the file copy (the backup) that SQL is doing. Search Google
for "The specified network is no longer available", lots of hits.SQL Server 2000
We have a db maintenance job which backup all the db's in a Production
Server to a remote server.
Production Server - Windows 2000 server
Remote Box - Windows 2003 Standard Edition
We recently applied SP1 to Windows 2003 box (Remote Box), after that Db
maintenance job with error message 'Operating system error 64 (The specified
network is no longer available..)
Certain days it will backup all databases but with this error message.
Any thoughts/suggestions/ideas
Thanks
Mike|||MS User wrote:
> SQL Server 2000
> We have a db maintenance job which backup all the db's in a Production
> Server to a remote server.
> Production Server - Windows 2000 server
> Remote Box - Windows 2003 Standard Edition
> We recently applied SP1 to Windows 2003 box (Remote Box), after that Db
> maintenance job with error message 'Operating system error 64 (The specifi
ed
> network is no longer available..)
> Certain days it will backup all databases but with this error message.
> Any thoughts/suggestions/ideas
> Thanks
> Mike
>
I'm not sure this is a SQL problem, sounds more like a network or OS
problem on the remote side. You're losing the connection to the remote
box during the file copy (the backup) that SQL is doing. Search Google
for "The specified network is no longer available", lots of hits.
Showing posts with label user. Show all posts
Showing posts with label user. Show all posts
Monday, March 19, 2012
Database automatically resetting to Single User Mode frequently
The problem was with the SQL server that authenticates
users' access to the Intranet. For some reason, the SQL
server put itself in single-use mode, which prevents users
from accessing the database- hence denying logon. This
happens once every few months- for reasons we cannot
explain. This time, it also looked like the SQL server had
depleted resources. I rebooted the server and everything
came back immediately.
Frequency - Once in 10 days or a week.
Any Idea why this is happening?
Thanks.Perhaps the main plan? Remove the option to "fix minor problems", this is the cause. And if this is
your problem, make sure you are current on service pack.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Scott London" <anonymous@.discussions.microsoft.com> wrote in message
news:0afc01c3996f$7d736540$a401280a@.phx.gbl...
> The problem was with the SQL server that authenticates
> users' access to the Intranet. For some reason, the SQL
> server put itself in single-use mode, which prevents users
> from accessing the database- hence denying logon. This
> happens once every few months- for reasons we cannot
> explain. This time, it also looked like the SQL server had
> depleted resources. I rebooted the server and everything
> came back immediately.
> Frequency - Once in 10 days or a week.
>
> Any Idea why this is happening?
> Thanks.|||Scott,
I would bet you have a job or process that sets the db to
single user mode temporarily in order to perform some
function. If that job fails then the db may be left in
single user mode until the server is restarted. Check
the sql error logs and the windows application log around
the times this happens and see if you can find any clues.
Sincerely,
Invotion Engineering Team
Advanced Microsoft Hosting Solutions
http://www.Invotion.com
>--Original Message--
>The problem was with the SQL server that authenticates
>users' access to the Intranet. For some reason, the SQL
>server put itself in single-use mode, which prevents
users
>from accessing the database- hence denying logon. This
>happens once every few months- for reasons we cannot
>explain. This time, it also looked like the SQL server
had
>depleted resources. I rebooted the server and everything
>came back immediately.
>Frequency - Once in 10 days or a week.
>
>Any Idea why this is happening?
>Thanks.
>.
>|||Tibor,
a recent SP eliminates that problem?
Quentin
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:eg6riEXmDHA.360@.TK2MSFTNGP12.phx.gbl...
> Perhaps the main plan? Remove the option to "fix minor problems", this is
the cause. And if this is
> your problem, make sure you are current on service pack.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Scott London" <anonymous@.discussions.microsoft.com> wrote in message
> news:0afc01c3996f$7d736540$a401280a@.phx.gbl...
> > The problem was with the SQL server that authenticates
> > users' access to the Intranet. For some reason, the SQL
> > server put itself in single-use mode, which prevents users
> > from accessing the database- hence denying logon. This
> > happens once every few months- for reasons we cannot
> > explain. This time, it also looked like the SQL server had
> > depleted resources. I rebooted the server and everything
> > came back immediately.
> >
> > Frequency - Once in 10 days or a week.
> >
> >
> > Any Idea why this is happening?
> >
> > Thanks.
>|||Yep. I think the problem occurred with SQL7, and fixed in sp3. Not 100% sure though. Note that
removing the fix option (which is a bad option in the first place) eliminates this problem.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Quentin Ran" <ab@.who.com> wrote in message news:uBVlkbXmDHA.1408@.TK2MSFTNGP11.phx.gbl...
> Tibor,
> a recent SP eliminates that problem?
> Quentin
>
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:eg6riEXmDHA.360@.TK2MSFTNGP12.phx.gbl...
> > Perhaps the main plan? Remove the option to "fix minor problems", this is
> the cause. And if this is
> > your problem, make sure you are current on service pack.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Scott London" <anonymous@.discussions.microsoft.com> wrote in message
> > news:0afc01c3996f$7d736540$a401280a@.phx.gbl...
> > > The problem was with the SQL server that authenticates
> > > users' access to the Intranet. For some reason, the SQL
> > > server put itself in single-use mode, which prevents users
> > > from accessing the database- hence denying logon. This
> > > happens once every few months- for reasons we cannot
> > > explain. This time, it also looked like the SQL server had
> > > depleted resources. I rebooted the server and everything
> > > came back immediately.
> > >
> > > Frequency - Once in 10 days or a week.
> > >
> > >
> > > Any Idea why this is happening?
> > >
> > > Thanks.
> >
> >
>
users' access to the Intranet. For some reason, the SQL
server put itself in single-use mode, which prevents users
from accessing the database- hence denying logon. This
happens once every few months- for reasons we cannot
explain. This time, it also looked like the SQL server had
depleted resources. I rebooted the server and everything
came back immediately.
Frequency - Once in 10 days or a week.
Any Idea why this is happening?
Thanks.Perhaps the main plan? Remove the option to "fix minor problems", this is the cause. And if this is
your problem, make sure you are current on service pack.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Scott London" <anonymous@.discussions.microsoft.com> wrote in message
news:0afc01c3996f$7d736540$a401280a@.phx.gbl...
> The problem was with the SQL server that authenticates
> users' access to the Intranet. For some reason, the SQL
> server put itself in single-use mode, which prevents users
> from accessing the database- hence denying logon. This
> happens once every few months- for reasons we cannot
> explain. This time, it also looked like the SQL server had
> depleted resources. I rebooted the server and everything
> came back immediately.
> Frequency - Once in 10 days or a week.
>
> Any Idea why this is happening?
> Thanks.|||Scott,
I would bet you have a job or process that sets the db to
single user mode temporarily in order to perform some
function. If that job fails then the db may be left in
single user mode until the server is restarted. Check
the sql error logs and the windows application log around
the times this happens and see if you can find any clues.
Sincerely,
Invotion Engineering Team
Advanced Microsoft Hosting Solutions
http://www.Invotion.com
>--Original Message--
>The problem was with the SQL server that authenticates
>users' access to the Intranet. For some reason, the SQL
>server put itself in single-use mode, which prevents
users
>from accessing the database- hence denying logon. This
>happens once every few months- for reasons we cannot
>explain. This time, it also looked like the SQL server
had
>depleted resources. I rebooted the server and everything
>came back immediately.
>Frequency - Once in 10 days or a week.
>
>Any Idea why this is happening?
>Thanks.
>.
>|||Tibor,
a recent SP eliminates that problem?
Quentin
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:eg6riEXmDHA.360@.TK2MSFTNGP12.phx.gbl...
> Perhaps the main plan? Remove the option to "fix minor problems", this is
the cause. And if this is
> your problem, make sure you are current on service pack.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Scott London" <anonymous@.discussions.microsoft.com> wrote in message
> news:0afc01c3996f$7d736540$a401280a@.phx.gbl...
> > The problem was with the SQL server that authenticates
> > users' access to the Intranet. For some reason, the SQL
> > server put itself in single-use mode, which prevents users
> > from accessing the database- hence denying logon. This
> > happens once every few months- for reasons we cannot
> > explain. This time, it also looked like the SQL server had
> > depleted resources. I rebooted the server and everything
> > came back immediately.
> >
> > Frequency - Once in 10 days or a week.
> >
> >
> > Any Idea why this is happening?
> >
> > Thanks.
>|||Yep. I think the problem occurred with SQL7, and fixed in sp3. Not 100% sure though. Note that
removing the fix option (which is a bad option in the first place) eliminates this problem.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Quentin Ran" <ab@.who.com> wrote in message news:uBVlkbXmDHA.1408@.TK2MSFTNGP11.phx.gbl...
> Tibor,
> a recent SP eliminates that problem?
> Quentin
>
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:eg6riEXmDHA.360@.TK2MSFTNGP12.phx.gbl...
> > Perhaps the main plan? Remove the option to "fix minor problems", this is
> the cause. And if this is
> > your problem, make sure you are current on service pack.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Scott London" <anonymous@.discussions.microsoft.com> wrote in message
> > news:0afc01c3996f$7d736540$a401280a@.phx.gbl...
> > > The problem was with the SQL server that authenticates
> > > users' access to the Intranet. For some reason, the SQL
> > > server put itself in single-use mode, which prevents users
> > > from accessing the database- hence denying logon. This
> > > happens once every few months- for reasons we cannot
> > > explain. This time, it also looked like the SQL server had
> > > depleted resources. I rebooted the server and everything
> > > came back immediately.
> > >
> > > Frequency - Once in 10 days or a week.
> > >
> > >
> > > Any Idea why this is happening?
> > >
> > > Thanks.
> >
> >
>
Thursday, March 8, 2012
Database Access Issues
I'm running a simple login application locally with SQLExpress. If I run it straight out of VS2005 it will allow the user to login successfully, however if I run it through IIS it gives me the following error.
Server Error in '/BasicReview' Application.
An attempt to attach an auto-named database for file C:\Documents and Settings\jmfoster\My Documents\Visual Studio 2005\Projects\BasicReview\BasicReview\App_Data\aspnetdb.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details:System.Data.SqlClient.SqlException: An attempt to attach an auto-named database for file C:\Documents and Settings\jmfoster\My Documents\Visual Studio 2005\Projects\BasicReview\BasicReview\App_Data\aspnetdb.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.Stack Trace:
[SqlException (0x80131904): An attempt to attach an auto-named database for file C:\Documents and Settings\jmfoster\My Documents\Visual Studio 2005\Projects\BasicReview\BasicReview\App_Data\aspnetdb.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +739123 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1956 System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33 System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +170 System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +349 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +181 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 System.Web.DataAccess.SqlConnectionHolder.Open(HttpContext context, Boolean revertImpersonate) +84 System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +197 System.Web.Security.SqlMembershipProvider.GetPasswordWithFormat(String username, Boolean updateLastLoginActivityDate, Int32& status, String& password, Int32& passwordFormat, String& passwordSalt, Int32& failedPasswordAttemptCount, Int32& failedPasswordAnswerAttemptCount, Boolean& isApproved, DateTime& lastLoginDate, DateTime& lastActivityDate) +1121 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved, String& salt, Int32& passwordFormat) +105 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved) +42 System.Web.Security.SqlMembershipProvider.ValidateUser(String username, String password) +83 System.Web.UI.WebControls.Login.OnAuthenticate(AuthenticateEventArgs e) +160 System.Web.UI.WebControls.Login.AttemptLogin() +105 System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +99 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +163 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
Version Information: Microsoft .NET Framework Version:2.0.50727.832; ASP.NET Version:2.0.50727.832
User instances of SQL Server Express only allow 1 open connection to the database at a time. You'll have to close the connection to the database in VS 2005 before IIS will be able to connect. Open the Data Connections panel, right-click on the database, and click "Disconnect..."
|||I've closed the connection, closed VS2005 and it still gives me the same error. I'm thinking windows security or sql security is the issue, but I'm not sure.
|||Out of curiousity, do you have a machine instance (through SQL Express Manager) of a database by the same name?
|||Is this website in a hosted site, where someone else could have the same SQL Server name?
|||I installed SQL Express Manager however I was unable to attach the database. For some reason when I try to attach databases the folder view to find it won't allow me to go very deep into the folder tree. However it does show up if in SQL Express Manager if I have VS2005 running and the database attached in there.
I have no other databases on this computer. I just installed everything a couple days ago.
|||I have a feeling that when you attempted to attach it to the SQL Express manager it may have mucked something up. Try changing the name of the mdb file and update your connection string.
|||I renamed it but receive the same error.
Let me explain what I'm doing. I'm making a very simple login app. I literally have done very little so far. In VS2005 I went to the 'Project Menu' and clicked 'ASP.NET Configuration' which allowed me to make roles and users. This step created the default database ASPNETDB.MDB in the App_Data folder in the directory of my project. My project is in the default directory for new projects (My Documents\Visual Studio 2005\Projects\BasicReview\BasicReview\App_Data).
I am able to connect to this database within VS2005, and also with SQL Express Manager (only if I have VS2005 attached to the database).
The login is successful within VS2005 ASP.NET Development runtime. However I need to use IIS. I have IIS 6 installed locally and a virtual directory set within the Defaul Website. I can see the Default.aspx page, but once I try to login it fails and displays the above error.
|||Would someone be willing to list the steps they take to get a login page working on their local machine with IIS and SQL Express? This bump in my road is killing me, I can't understand why something that everyone must do at some point in time would be so difficult.
|||Since you need to open your site using IIS as opposed to VS2005 web server you will need to attach your database (mdf file) in SQL Express Managment studio. You can do this by opening SQL Exp. Managment, and right clicking on your database and choosing attach.
Then you need to remove your connection string in your web.config and add a new one like
<add name="connection string name" connectionString="Data Source=yourservername;Initial Catalog=databasename;Integrated Security=True"
providerName="System.Data.SqlClient" />
Couple of things to keep in mind about this. By default SQLExpress uses named pipes for client connections. Which will break through IIS connections. You will also need to open up your SQL2005 Configuration Manager and configure TCP/IP for client connections. Also this will break the DB so you can't use it inside of Visual Studio anymore, you'll have to use SQL Express Management Studio to manage your DB.
database access & asp.net
If a user has permissions to add and delete rows from a table i.e. adding
and removing items from an order what is to stop a malicious user changing
the product code in the form and then adding to or removing items to/from
another user's order ?
How do we ensure that the rows the user is editing are rows the user has
permission to edit ?
ThanksTwo words Application Architecture. Seriously you need to implement your
own security model if you want to provide for row level and field level
security.
Secondly if you are developing a web app the user should not have rights to
your database. The connection should be handled by an "Application User"
that in turn is managed by a Connection Pool.
Dan
"Murphy" <murphy@.murphy.com> wrote in message
news:%23wb%23$V%23oDHA.684@.TK2MSFTNGP09.phx.gbl...
> If a user has permissions to add and delete rows from a table i.e. adding
> and removing items from an order what is to stop a malicious user changing
> the product code in the form and then adding to or removing items to/from
> another user's order ?
> How do we ensure that the rows the user is editing are rows the user has
> permission to edit ?
> Thanks
>|||Are there any security models that have been tried and proven, don't want to
reinvent the wheel ?
Thanks
"solex" <solex@.nowhere.com> wrote in message
news:%23oTqDa%23oDHA.708@.TK2MSFTNGP10.phx.gbl...
> Two words Application Architecture. Seriously you need to implement your
> own security model if you want to provide for row level and field level
> security.
> Secondly if you are developing a web app the user should not have rights
to
> your database. The connection should be handled by an "Application User"
> that in turn is managed by a Connection Pool.
> Dan
> "Murphy" <murphy@.murphy.com> wrote in message
> news:%23wb%23$V%23oDHA.684@.TK2MSFTNGP09.phx.gbl...
> > If a user has permissions to add and delete rows from a table i.e.
adding
> > and removing items from an order what is to stop a malicious user
changing
> > the product code in the form and then adding to or removing items
to/from
> > another user's order ?
> >
> > How do we ensure that the rows the user is editing are rows the user has
> > permission to edit ?
> >
> > Thanks
> >
> >
>|||There are many security models but none that I have seen that will implement
row level security in your database. It has been my experience that if you
want granular security you will need to implement it on your own.
Dan
"Murphy" <murphy@.murphy.com> wrote in message
news:eosI6c%23oDHA.2652@.TK2MSFTNGP09.phx.gbl...
> Are there any security models that have been tried and proven, don't want
to
> reinvent the wheel ?
> Thanks
> "solex" <solex@.nowhere.com> wrote in message
> news:%23oTqDa%23oDHA.708@.TK2MSFTNGP10.phx.gbl...
> > Two words Application Architecture. Seriously you need to implement
your
> > own security model if you want to provide for row level and field level
> > security.
> >
> > Secondly if you are developing a web app the user should not have rights
> to
> > your database. The connection should be handled by an "Application
User"
> > that in turn is managed by a Connection Pool.
> >
> > Dan
> >
> > "Murphy" <murphy@.murphy.com> wrote in message
> > news:%23wb%23$V%23oDHA.684@.TK2MSFTNGP09.phx.gbl...
> > > If a user has permissions to add and delete rows from a table i.e.
> adding
> > > and removing items from an order what is to stop a malicious user
> changing
> > > the product code in the form and then adding to or removing items
> to/from
> > > another user's order ?
> > >
> > > How do we ensure that the rows the user is editing are rows the user
has
> > > permission to edit ?
> > >
> > > Thanks
> > >
> > >
> >
> >
>
and removing items from an order what is to stop a malicious user changing
the product code in the form and then adding to or removing items to/from
another user's order ?
How do we ensure that the rows the user is editing are rows the user has
permission to edit ?
ThanksTwo words Application Architecture. Seriously you need to implement your
own security model if you want to provide for row level and field level
security.
Secondly if you are developing a web app the user should not have rights to
your database. The connection should be handled by an "Application User"
that in turn is managed by a Connection Pool.
Dan
"Murphy" <murphy@.murphy.com> wrote in message
news:%23wb%23$V%23oDHA.684@.TK2MSFTNGP09.phx.gbl...
> If a user has permissions to add and delete rows from a table i.e. adding
> and removing items from an order what is to stop a malicious user changing
> the product code in the form and then adding to or removing items to/from
> another user's order ?
> How do we ensure that the rows the user is editing are rows the user has
> permission to edit ?
> Thanks
>|||Are there any security models that have been tried and proven, don't want to
reinvent the wheel ?
Thanks
"solex" <solex@.nowhere.com> wrote in message
news:%23oTqDa%23oDHA.708@.TK2MSFTNGP10.phx.gbl...
> Two words Application Architecture. Seriously you need to implement your
> own security model if you want to provide for row level and field level
> security.
> Secondly if you are developing a web app the user should not have rights
to
> your database. The connection should be handled by an "Application User"
> that in turn is managed by a Connection Pool.
> Dan
> "Murphy" <murphy@.murphy.com> wrote in message
> news:%23wb%23$V%23oDHA.684@.TK2MSFTNGP09.phx.gbl...
> > If a user has permissions to add and delete rows from a table i.e.
adding
> > and removing items from an order what is to stop a malicious user
changing
> > the product code in the form and then adding to or removing items
to/from
> > another user's order ?
> >
> > How do we ensure that the rows the user is editing are rows the user has
> > permission to edit ?
> >
> > Thanks
> >
> >
>|||There are many security models but none that I have seen that will implement
row level security in your database. It has been my experience that if you
want granular security you will need to implement it on your own.
Dan
"Murphy" <murphy@.murphy.com> wrote in message
news:eosI6c%23oDHA.2652@.TK2MSFTNGP09.phx.gbl...
> Are there any security models that have been tried and proven, don't want
to
> reinvent the wheel ?
> Thanks
> "solex" <solex@.nowhere.com> wrote in message
> news:%23oTqDa%23oDHA.708@.TK2MSFTNGP10.phx.gbl...
> > Two words Application Architecture. Seriously you need to implement
your
> > own security model if you want to provide for row level and field level
> > security.
> >
> > Secondly if you are developing a web app the user should not have rights
> to
> > your database. The connection should be handled by an "Application
User"
> > that in turn is managed by a Connection Pool.
> >
> > Dan
> >
> > "Murphy" <murphy@.murphy.com> wrote in message
> > news:%23wb%23$V%23oDHA.684@.TK2MSFTNGP09.phx.gbl...
> > > If a user has permissions to add and delete rows from a table i.e.
> adding
> > > and removing items from an order what is to stop a malicious user
> changing
> > > the product code in the form and then adding to or removing items
> to/from
> > > another user's order ?
> > >
> > > How do we ensure that the rows the user is editing are rows the user
has
> > > permission to edit ?
> > >
> > > Thanks
> > >
> > >
> >
> >
>
Wednesday, March 7, 2012
DataAdapter.Update Method Question.
Hi,
I am trying to use DataAdapter.Update to save a file stream into SQl Express.
I have a dialog box that lets user select the file:
openFileDialog1.ShowDialog();
I want to put
openFileDialog1.OpenFile();
Into
this.documentTableAdapter.Update(this.docControllerAlphaDBDataSet.Document.DocumentColumn);
I am thinking that it might just be some syntax issue, but I looked online, and didn't find much answers.
Thanks,
Ke
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=622943&SiteID=1
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Friday, February 24, 2012
Data Update Problem
Dear All,
I am facing a problem from last one month. I have around 100 of client user of my database. I am using VB application for the client users. When they submit the information most of the time the information submits properly. But twice or thrice a day no one can submit the information properly. It remains for more then 15 mins and it will sort out automatically.
What might be the problem. Please suggestDatabase locking?
Without more information, we can't really help you.
When this happens again, check to see if any blocking is occuring.|||I am trying to trace from the Profiler. All the Insert statement to that particular table is taking a lot of time. At that point of time the cpu usases, memory everything is ok.|||did you sp_who2 or sp_lock?
I am facing a problem from last one month. I have around 100 of client user of my database. I am using VB application for the client users. When they submit the information most of the time the information submits properly. But twice or thrice a day no one can submit the information properly. It remains for more then 15 mins and it will sort out automatically.
What might be the problem. Please suggestDatabase locking?
Without more information, we can't really help you.
When this happens again, check to see if any blocking is occuring.|||I am trying to trace from the Profiler. All the Insert statement to that particular table is taking a lot of time. At that point of time the cpu usases, memory everything is ok.|||did you sp_who2 or sp_lock?
Data Types
Hi...what would be the right data type to use for a field that will hold a
long description that will be keyed in by the user?
Currently, I have it set to use varchar with a size of 3000, but it still
cuts off.
Thanks> Currently, I have it set to use varchar with a size of 3000, but it still
> cuts off.
What does that mean? You are trying to store more than 3,000 characters, or
your stored procedure truncates the entry, or Query Analyzer doesn't show
the full entry, or something else ... ?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Hi,
SQL 2000 allows varchar with a max size of 8000. You can use ALTER TABLE
statement to increase the width from 3000 to 8000.
If your problem is just display then try selecting the data with out grid
(text format) option in query analyzer.
Thanks
Hari
MCDBA
"SQL" <nospam@.asdfadsf.com> wrote in message
news:uN9gJH06DHA.2656@.TK2MSFTNGP11.phx.gbl...
> Hi...what would be the right data type to use for a field that will hold a
> long description that will be keyed in by the user?
> Currently, I have it set to use varchar with a size of 3000, but it still
> cuts off.
> Thanks
>|||Assuming this isn't a display issue and you consider this, be careful
,since inserts will then fail when the total length of a row exceeds
8060 bytes. Better to cut the information off than to create a
situation where inserts can cause an error condition. You could also
look into using the text data type. It's not as easy to manipulate
beyond inserts and retrieval, but can hold up to 2 Gig of information,
since it only stores a 16-byte text pointer in the row. If you can,
limit the amount of information inserted at the front end.
If it is a display issue, you will not only need to change the output to
text from grid, but you will also have to go into the Query Analyzer
Tools|Options|[Results] and set the "Maximum Characters per Column:"
value to match or exceed your varchar column length. The default is
256, I believe.
SK
Hari wrote:
>Hi,
>SQL 2000 allows varchar with a max size of 8000. You can use ALTER TABLE
>statement to increase the width from 3000 to 8000.
>
>If your problem is just display then try selecting the data with out grid
>(text format) option in query analyzer.
>Thanks
>Hari
>MCDBA
>"SQL" <nospam@.asdfadsf.com> wrote in message
>news:uN9gJH06DHA.2656@.TK2MSFTNGP11.phx.gbl...
>
>>Hi...what would be the right data type to use for a field that will hold a
>>long description that will be keyed in by the user?
>>Currently, I have it set to use varchar with a size of 3000, but it still
>>cuts off.
>>Thanks
>>
>>
>
>
long description that will be keyed in by the user?
Currently, I have it set to use varchar with a size of 3000, but it still
cuts off.
Thanks> Currently, I have it set to use varchar with a size of 3000, but it still
> cuts off.
What does that mean? You are trying to store more than 3,000 characters, or
your stored procedure truncates the entry, or Query Analyzer doesn't show
the full entry, or something else ... ?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Hi,
SQL 2000 allows varchar with a max size of 8000. You can use ALTER TABLE
statement to increase the width from 3000 to 8000.
If your problem is just display then try selecting the data with out grid
(text format) option in query analyzer.
Thanks
Hari
MCDBA
"SQL" <nospam@.asdfadsf.com> wrote in message
news:uN9gJH06DHA.2656@.TK2MSFTNGP11.phx.gbl...
> Hi...what would be the right data type to use for a field that will hold a
> long description that will be keyed in by the user?
> Currently, I have it set to use varchar with a size of 3000, but it still
> cuts off.
> Thanks
>|||Assuming this isn't a display issue and you consider this, be careful
,since inserts will then fail when the total length of a row exceeds
8060 bytes. Better to cut the information off than to create a
situation where inserts can cause an error condition. You could also
look into using the text data type. It's not as easy to manipulate
beyond inserts and retrieval, but can hold up to 2 Gig of information,
since it only stores a 16-byte text pointer in the row. If you can,
limit the amount of information inserted at the front end.
If it is a display issue, you will not only need to change the output to
text from grid, but you will also have to go into the Query Analyzer
Tools|Options|[Results] and set the "Maximum Characters per Column:"
value to match or exceed your varchar column length. The default is
256, I believe.
SK
Hari wrote:
>Hi,
>SQL 2000 allows varchar with a max size of 8000. You can use ALTER TABLE
>statement to increase the width from 3000 to 8000.
>
>If your problem is just display then try selecting the data with out grid
>(text format) option in query analyzer.
>Thanks
>Hari
>MCDBA
>"SQL" <nospam@.asdfadsf.com> wrote in message
>news:uN9gJH06DHA.2656@.TK2MSFTNGP11.phx.gbl...
>
>>Hi...what would be the right data type to use for a field that will hold a
>>long description that will be keyed in by the user?
>>Currently, I have it set to use varchar with a size of 3000, but it still
>>cuts off.
>>Thanks
>>
>>
>
>
Data types
I need to export the name of certain user tables along with the names of their columns and which type the column contains ie. varchar / integer and so on. I can figure out the table and column names - but how do i retrieve information about the data-types in each column ?
My query so far:
--------
select obj.Name as Tbl,Col.Name as Col
from sysobjects obj, syscolumns col
where obj.xtype='U' and obj.Name like 'netop%' and obj.id=col.id
--------
Thx. in advanceSELECT A.TABLE_NAME, B.COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.TABLES AS A
INNER JOIN INFORMATION_SCHEMA.COLUMNS AS B
ON A.TABLE_NAME = B.TABLE_NAME
WHERE A.TABLE_NAME = 'yourTable'
Very useful views and I strongly recommend that your read more about them on BOL.|||WOW - that was fast - thanks a lot|||Beware of objects with the same name and different owners! Include a join on TABLE_SCHEMA to be safe:
SELECT A.*, A.TABLE_NAME, B.COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.TABLES AS A
INNER JOIN INFORMATION_SCHEMA.COLUMNS AS B
ON A.TABLE_NAME = B.TABLE_NAME
AND A.TABLE_SCHEMA = B.TABLE_SCHEMA
blindman|||Good point and well spotted.
My query so far:
--------
select obj.Name as Tbl,Col.Name as Col
from sysobjects obj, syscolumns col
where obj.xtype='U' and obj.Name like 'netop%' and obj.id=col.id
--------
Thx. in advanceSELECT A.TABLE_NAME, B.COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.TABLES AS A
INNER JOIN INFORMATION_SCHEMA.COLUMNS AS B
ON A.TABLE_NAME = B.TABLE_NAME
WHERE A.TABLE_NAME = 'yourTable'
Very useful views and I strongly recommend that your read more about them on BOL.|||WOW - that was fast - thanks a lot|||Beware of objects with the same name and different owners! Include a join on TABLE_SCHEMA to be safe:
SELECT A.*, A.TABLE_NAME, B.COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.TABLES AS A
INNER JOIN INFORMATION_SCHEMA.COLUMNS AS B
ON A.TABLE_NAME = B.TABLE_NAME
AND A.TABLE_SCHEMA = B.TABLE_SCHEMA
blindman|||Good point and well spotted.
Sunday, February 19, 2012
Data Type question
Hi
I often have the dilemma of whether to use Text type or Varchar type.
Normally the situation is that the user will input 50 characters however in
some cases he will input 2000 characters should I make the field Text or
Varchar(2000)
Technically the question is whether a varchar field is assigned the space
automatically or only on request also how significant is the overhead of
using the Text type
Thank you in advance,
Shmuel Shulman
SBS Technologies LTD
"S Shulman" <smshulman@.hotmail.com> wrote in message
news:%23vJQVG0nFHA.3380@.TK2MSFTNGP12.phx.gbl...
> Hi
> I often have the dilemma of whether to use Text type or Varchar type.
> Normally the situation is that the user will input 50 characters however
> in some cases he will input 2000 characters should I make the field Text
> or Varchar(2000)
> Technically the question is whether a varchar field is assigned the space
> automatically or only on request also how significant is the overhead of
> using the Text type
>
The "var" in varchar is because the storage is variable. It only takes up
as much space as you use (plus a small fixed overhead).
Using the text type causes a 16-byte locator to be stored in the row instead
of the actual value. The actual value is stored on another page. So the
overhead of using Text is mainly the extra read required to get to the
actual value. For 50-2000 characters, use Varchar.
David
|||Thanks you for your response,
Shmuel
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:e7rPgN0nFHA.3540@.TK2MSFTNGP10.phx.gbl...
> "S Shulman" <smshulman@.hotmail.com> wrote in message
> news:%23vJQVG0nFHA.3380@.TK2MSFTNGP12.phx.gbl...
> The "var" in varchar is because the storage is variable. It only takes up
> as much space as you use (plus a small fixed overhead).
> Using the text type causes a 16-byte locator to be stored in the row
> instead of the actual value. The actual value is stored on another page.
> So the overhead of using Text is mainly the extra read required to get to
> the actual value. For 50-2000 characters, use Varchar.
> David
>
I often have the dilemma of whether to use Text type or Varchar type.
Normally the situation is that the user will input 50 characters however in
some cases he will input 2000 characters should I make the field Text or
Varchar(2000)
Technically the question is whether a varchar field is assigned the space
automatically or only on request also how significant is the overhead of
using the Text type
Thank you in advance,
Shmuel Shulman
SBS Technologies LTD
"S Shulman" <smshulman@.hotmail.com> wrote in message
news:%23vJQVG0nFHA.3380@.TK2MSFTNGP12.phx.gbl...
> Hi
> I often have the dilemma of whether to use Text type or Varchar type.
> Normally the situation is that the user will input 50 characters however
> in some cases he will input 2000 characters should I make the field Text
> or Varchar(2000)
> Technically the question is whether a varchar field is assigned the space
> automatically or only on request also how significant is the overhead of
> using the Text type
>
The "var" in varchar is because the storage is variable. It only takes up
as much space as you use (plus a small fixed overhead).
Using the text type causes a 16-byte locator to be stored in the row instead
of the actual value. The actual value is stored on another page. So the
overhead of using Text is mainly the extra read required to get to the
actual value. For 50-2000 characters, use Varchar.
David
|||Thanks you for your response,
Shmuel
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:e7rPgN0nFHA.3540@.TK2MSFTNGP10.phx.gbl...
> "S Shulman" <smshulman@.hotmail.com> wrote in message
> news:%23vJQVG0nFHA.3380@.TK2MSFTNGP12.phx.gbl...
> The "var" in varchar is because the storage is variable. It only takes up
> as much space as you use (plus a small fixed overhead).
> Using the text type causes a 16-byte locator to be stored in the row
> instead of the actual value. The actual value is stored on another page.
> So the overhead of using Text is mainly the extra read required to get to
> the actual value. For 50-2000 characters, use Varchar.
> David
>
Data Type question
Hi
I often have the dilemma of whether to use Text type or Varchar type.
Normally the situation is that the user will input 50 characters however in
some cases he will input 2000 characters should I make the field Text or
Varchar(2000)
Technically the question is whether a varchar field is assigned the space
automatically or only on request also how significant is the overhead of
using the Text type
Thank you in advance,
Shmuel Shulman
SBS Technologies LTD"S Shulman" <smshulman@.hotmail.com> wrote in message
news:%23vJQVG0nFHA.3380@.TK2MSFTNGP12.phx.gbl...
> Hi
> I often have the dilemma of whether to use Text type or Varchar type.
> Normally the situation is that the user will input 50 characters however
> in some cases he will input 2000 characters should I make the field Text
> or Varchar(2000)
> Technically the question is whether a varchar field is assigned the space
> automatically or only on request also how significant is the overhead of
> using the Text type
>
The "var" in varchar is because the storage is variable. It only takes up
as much space as you use (plus a small fixed overhead).
Using the text type causes a 16-byte locator to be stored in the row instead
of the actual value. The actual value is stored on another page. So the
overhead of using Text is mainly the extra read required to get to the
actual value. For 50-2000 characters, use Varchar.
David|||Thanks you for your response,
Shmuel
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:e7rPgN0nFHA.3540@.TK2MSFTNGP10.phx.gbl...
> "S Shulman" <smshulman@.hotmail.com> wrote in message
> news:%23vJQVG0nFHA.3380@.TK2MSFTNGP12.phx.gbl...
> The "var" in varchar is because the storage is variable. It only takes up
> as much space as you use (plus a small fixed overhead).
> Using the text type causes a 16-byte locator to be stored in the row
> instead of the actual value. The actual value is stored on another page.
> So the overhead of using Text is mainly the extra read required to get to
> the actual value. For 50-2000 characters, use Varchar.
> David
>
I often have the dilemma of whether to use Text type or Varchar type.
Normally the situation is that the user will input 50 characters however in
some cases he will input 2000 characters should I make the field Text or
Varchar(2000)
Technically the question is whether a varchar field is assigned the space
automatically or only on request also how significant is the overhead of
using the Text type
Thank you in advance,
Shmuel Shulman
SBS Technologies LTD"S Shulman" <smshulman@.hotmail.com> wrote in message
news:%23vJQVG0nFHA.3380@.TK2MSFTNGP12.phx.gbl...
> Hi
> I often have the dilemma of whether to use Text type or Varchar type.
> Normally the situation is that the user will input 50 characters however
> in some cases he will input 2000 characters should I make the field Text
> or Varchar(2000)
> Technically the question is whether a varchar field is assigned the space
> automatically or only on request also how significant is the overhead of
> using the Text type
>
The "var" in varchar is because the storage is variable. It only takes up
as much space as you use (plus a small fixed overhead).
Using the text type causes a 16-byte locator to be stored in the row instead
of the actual value. The actual value is stored on another page. So the
overhead of using Text is mainly the extra read required to get to the
actual value. For 50-2000 characters, use Varchar.
David|||Thanks you for your response,
Shmuel
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:e7rPgN0nFHA.3540@.TK2MSFTNGP10.phx.gbl...
> "S Shulman" <smshulman@.hotmail.com> wrote in message
> news:%23vJQVG0nFHA.3380@.TK2MSFTNGP12.phx.gbl...
> The "var" in varchar is because the storage is variable. It only takes up
> as much space as you use (plus a small fixed overhead).
> Using the text type causes a 16-byte locator to be stored in the row
> instead of the actual value. The actual value is stored on another page.
> So the overhead of using Text is mainly the extra read required to get to
> the actual value. For 50-2000 characters, use Varchar.
> David
>
Data Type question
Hi
I often have the dilemma of whether to use Text type or Varchar type.
Normally the situation is that the user will input 50 characters however in
some cases he will input 2000 characters should I make the field Text or
Varchar(2000)
Technically the question is whether a varchar field is assigned the space
automatically or only on request also how significant is the overhead of
using the Text type
Thank you in advance,
Shmuel Shulman
SBS Technologies LTD"S Shulman" <smshulman@.hotmail.com> wrote in message
news:%23vJQVG0nFHA.3380@.TK2MSFTNGP12.phx.gbl...
> Hi
> I often have the dilemma of whether to use Text type or Varchar type.
> Normally the situation is that the user will input 50 characters however
> in some cases he will input 2000 characters should I make the field Text
> or Varchar(2000)
> Technically the question is whether a varchar field is assigned the space
> automatically or only on request also how significant is the overhead of
> using the Text type
>
The "var" in varchar is because the storage is variable. It only takes up
as much space as you use (plus a small fixed overhead).
Using the text type causes a 16-byte locator to be stored in the row instead
of the actual value. The actual value is stored on another page. So the
overhead of using Text is mainly the extra read required to get to the
actual value. For 50-2000 characters, use Varchar.
David|||Thanks you for your response,
Shmuel
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:e7rPgN0nFHA.3540@.TK2MSFTNGP10.phx.gbl...
> "S Shulman" <smshulman@.hotmail.com> wrote in message
> news:%23vJQVG0nFHA.3380@.TK2MSFTNGP12.phx.gbl...
>> Hi
>> I often have the dilemma of whether to use Text type or Varchar type.
>> Normally the situation is that the user will input 50 characters however
>> in some cases he will input 2000 characters should I make the field Text
>> or Varchar(2000)
>> Technically the question is whether a varchar field is assigned the space
>> automatically or only on request also how significant is the overhead of
>> using the Text type
> The "var" in varchar is because the storage is variable. It only takes up
> as much space as you use (plus a small fixed overhead).
> Using the text type causes a 16-byte locator to be stored in the row
> instead of the actual value. The actual value is stored on another page.
> So the overhead of using Text is mainly the extra read required to get to
> the actual value. For 50-2000 characters, use Varchar.
> David
>
I often have the dilemma of whether to use Text type or Varchar type.
Normally the situation is that the user will input 50 characters however in
some cases he will input 2000 characters should I make the field Text or
Varchar(2000)
Technically the question is whether a varchar field is assigned the space
automatically or only on request also how significant is the overhead of
using the Text type
Thank you in advance,
Shmuel Shulman
SBS Technologies LTD"S Shulman" <smshulman@.hotmail.com> wrote in message
news:%23vJQVG0nFHA.3380@.TK2MSFTNGP12.phx.gbl...
> Hi
> I often have the dilemma of whether to use Text type or Varchar type.
> Normally the situation is that the user will input 50 characters however
> in some cases he will input 2000 characters should I make the field Text
> or Varchar(2000)
> Technically the question is whether a varchar field is assigned the space
> automatically or only on request also how significant is the overhead of
> using the Text type
>
The "var" in varchar is because the storage is variable. It only takes up
as much space as you use (plus a small fixed overhead).
Using the text type causes a 16-byte locator to be stored in the row instead
of the actual value. The actual value is stored on another page. So the
overhead of using Text is mainly the extra read required to get to the
actual value. For 50-2000 characters, use Varchar.
David|||Thanks you for your response,
Shmuel
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:e7rPgN0nFHA.3540@.TK2MSFTNGP10.phx.gbl...
> "S Shulman" <smshulman@.hotmail.com> wrote in message
> news:%23vJQVG0nFHA.3380@.TK2MSFTNGP12.phx.gbl...
>> Hi
>> I often have the dilemma of whether to use Text type or Varchar type.
>> Normally the situation is that the user will input 50 characters however
>> in some cases he will input 2000 characters should I make the field Text
>> or Varchar(2000)
>> Technically the question is whether a varchar field is assigned the space
>> automatically or only on request also how significant is the overhead of
>> using the Text type
> The "var" in varchar is because the storage is variable. It only takes up
> as much space as you use (plus a small fixed overhead).
> Using the text type causes a 16-byte locator to be stored in the row
> instead of the actual value. The actual value is stored on another page.
> So the overhead of using Text is mainly the extra read required to get to
> the actual value. For 50-2000 characters, use Varchar.
> David
>
Data type in audit record
Hi,
Do you actually have anything (or any reasonable prospect of having
anything in future) for which NVARCHAR(4000) wouldn't be good enough?
>
>
>
>
>
Do you actually have anything (or any reasonable prospect of having
anything in future) for which NVARCHAR(4000) wouldn't be good enough?
>
Whatever you do, I strongly recommend keeping tabs on how quickly it
grows, showing that trend information to the client, and (1) narrow it
down to the tables that really need an audit trail and/or (2) come up
with a sane archive-and-purge schedule.
Yeah, unfortunately we do have several tables with a column of type
text. These generally don't hold anything close to 4000 chars but
there's nothing actually preventing them from doing so. But...if
there's no tidier option I think I may just truncate to 4000 and be
done with it. We're not auditing to fulfil legal obligations or
anything nasty like that so I don't think it will be a problem.
That is not going to be fun in SQL 2000. In SQL 2005 you could build a
generic audit solution on the xml data type.
Look at third party tools that follow the law and get a basic dat
modeling book.
I want my application to audit any data changes (update, insert,
delete) made by the users. Rather than have an audit table mirroring
each user table, I'd prefer to have a generic structure which can log
anything. This is what I've come up with:
TABLE: audit_record
*audit_record_id (uniqueidentifier, auto-assign, PK) - unique
idenfiier of the audit record
table_name (varchar) - name of the table where the action (insert/
update/delete) was made
pk_value (varchar) - primary key of the changed record. If the PK
itself has changed, this will store the old value.
user_id (varchar) - user who changed the record
date (datetime) - date/time at which the change was made
action (int) - 0, 1 or 2 (insert, update, delete)
TABLE: audit_column
*audit_record_id (uniqueidentifier, composite PK) - FK to
cdb_audit_record table
*column_name (varchar, composite PK) - name of the column with changed
data
new_value (text?) - value after the change
So every column which changes has its new value logged individually in
the audit_column table. However, I'm not sure what data type the
new_value column should have. The obvious answer (to me) is text, as
that can handle any necessary data type with the appropriate
conversion (we don't store any binary data). However, this table is
going to grow to millions of records and I'm not sure what the
performance implications of a text column will be, particularly given
that the actual data stored in it will almost always be tiny.
Any thoughts/recommendations/criticism would be greatly appreciated.
Thanks
AlexSorry for replying to myself - I forgot to state that I'm using SQL
Server 2000 standard edition.|||WombatDeath@.gmail.com wrote:
Quote:
Originally Posted by
I want my application to audit any data changes (update, insert,
delete) made by the users. Rather than have an audit table mirroring
each user table, I'd prefer to have a generic structure which can log
anything. This is what I've come up with:
>
TABLE: audit_record
*audit_record_id (uniqueidentifier, auto-assign, PK) - unique
idenfiier of the audit record
table_name (varchar) - name of the table where the action (insert/
update/delete) was made
pk_value (varchar) - primary key of the changed record. If the PK
itself has changed, this will store the old value.
user_id (varchar) - user who changed the record
date (datetime) - date/time at which the change was made
action (int) - 0, 1 or 2 (insert, update, delete)
>
TABLE: audit_column
*audit_record_id (uniqueidentifier, composite PK) - FK to
cdb_audit_record table
*column_name (varchar, composite PK) - name of the column with changed
data
new_value (text?) - value after the change
>
So every column which changes has its new value logged individually in
the audit_column table. However, I'm not sure what data type the
new_value column should have. The obvious answer (to me) is text, as
that can handle any necessary data type with the appropriate
conversion (we don't store any binary data). However, this table is
going to grow to millions of records and I'm not sure what the
performance implications of a text column will be, particularly given
that the actual data stored in it will almost always be tiny.
>
Any thoughts/recommendations/criticism would be greatly appreciated.
Do you actually have anything (or any reasonable prospect of having
anything in future) for which NVARCHAR(4000) wouldn't be good enough?
Whatever you do, I strongly recommend keeping tabs on how quickly it
grows, showing that trend information to the client, and (1) narrow it
down to the tables that really need an audit trail and/or (2) come up
with a sane archive-and-purge schedule.|||On Mar 30, 3:42 pm, Ed Murphy <emurph...@.socal.rr.comwrote:
Quote:
Originally Posted by
WombatDe...@.gmail.com wrote:
Quote:
Originally Posted by
I want my application to audit any data changes (update, insert,
delete) made by the users. Rather than have an audit table mirroring
each user table, I'd prefer to have a generic structure which can log
anything. This is what I've come up with:
>
Quote:
Originally Posted by
TABLE: audit_record
*audit_record_id (uniqueidentifier, auto-assign, PK) - unique
idenfiier of the audit record
table_name (varchar) - name of the table where the action (insert/
update/delete) was made
pk_value (varchar) - primary key of the changed record. If the PK
itself has changed, this will store the old value.
user_id (varchar) - user who changed the record
date (datetime) - date/time at which the change was made
action (int) - 0, 1 or 2 (insert, update, delete)
>
Quote:
Originally Posted by
TABLE: audit_column
*audit_record_id (uniqueidentifier, composite PK) - FK to
cdb_audit_record table
*column_name (varchar, composite PK) - name of the column with changed
data
new_value (text?) - value after the change
>
Quote:
Originally Posted by
So every column which changes has its new value logged individually in
the audit_column table. However, I'm not sure what data type the
new_value column should have. The obvious answer (to me) is text, as
that can handle any necessary data type with the appropriate
conversion (we don't store any binary data). However, this table is
going to grow to millions of records and I'm not sure what the
performance implications of a text column will be, particularly given
that the actual data stored in it will almost always be tiny.
>
Quote:
Originally Posted by
Any thoughts/recommendations/criticism would be greatly appreciated.
>
Do you actually have anything (or any reasonable prospect of having
anything in future) for which NVARCHAR(4000) wouldn't be good enough?
>
Whatever you do, I strongly recommend keeping tabs on how quickly it
grows, showing that trend information to the client, and (1) narrow it
down to the tables that really need an audit trail and/or (2) come up
with a sane archive-and-purge schedule.
Yeah, unfortunately we do have several tables with a column of type
text. These generally don't hold anything close to 4000 chars but
there's nothing actually preventing them from doing so. But...if
there's no tidier option I think I may just truncate to 4000 and be
done with it. We're not auditing to fulfil legal obligations or
anything nasty like that so I don't think it will be a problem.
Your point about maintenance is well taken. I've specified that the
application's auditing must be configurable on an entity-by-entity
basis, and every so often we'll archive away any old data for fast-
changing entities.
Thanks very much for your input!|||(WombatDeath@.gmail.com) writes:
Quote:
Originally Posted by
I want my application to audit any data changes (update, insert,
delete) made by the users. Rather than have an audit table mirroring
each user table, I'd prefer to have a generic structure which can log
anything. This is what I've come up with:
>
TABLE: audit_record
*audit_record_id (uniqueidentifier, auto-assign, PK) - unique
idenfiier of the audit record
table_name (varchar) - name of the table where the action (insert/
update/delete) was made
pk_value (varchar) - primary key of the changed record. If the PK
itself has changed, this will store the old value.
user_id (varchar) - user who changed the record
date (datetime) - date/time at which the change was made
action (int) - 0, 1 or 2 (insert, update, delete)
>
TABLE: audit_column
*audit_record_id (uniqueidentifier, composite PK) - FK to
cdb_audit_record table
*column_name (varchar, composite PK) - name of the column with changed
data
new_value (text?) - value after the change
>
So every column which changes has its new value logged individually in
the audit_column table. However, I'm not sure what data type the
new_value column should have. The obvious answer (to me) is text, as
that can handle any necessary data type with the appropriate
conversion (we don't store any binary data). However, this table is
going to grow to millions of records and I'm not sure what the
performance implications of a text column will be, particularly given
that the actual data stored in it will almost always be tiny.
That is not going to be fun in SQL 2000. In SQL 2005 you could build a
generic audit solution on the xml data type.
I would recommend that you research the market for audit products. I
know for instance that ApexSQL has a something they call SQLAudit
if memory serves.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>I want my application to audit any data changes (update, insert, delete) made by the users. Rather than have an audit table mirroring each user table, I'd prefer to have a generic structure which can log anything. <<
Any chance you might post DDL instead of your personal pseudo-code?
And I hope you know that auto-numbering is not a relational key.
Finally, Google "EAV design flaw" for tens of thousands of words on
why this approach stinks. There is no such magical shape shifting
table in RDBMS. Data Versus metadata, etc.? Freshman database
course, 3rd week of the quarter?
While you might like this kludge your accountants and auditors will
not. NEVER keep audit trails on the same database or even the same
hardware as the database.
Quote:
Originally Posted by
Quote:
Originally Posted by
>Any thoughts/recommendations/criticism would be greatly appreciated. <<
Look at third party tools that follow the law and get a basic dat
modeling book.
Friday, February 17, 2012
Data Type conversion precedence problem
I heard that if you upgrade user database from SQL 7.0 to SQL 2000(latest SPs),
there is some data type conversion precedence problem and the same query
will produce different results on SQL 7.0 and SQL 2000.
I am sorry, I don't have any specific examples. If someone had this problem,
please advice me with some examples.
Thanks.
Strictly speaking I think your question is not about data type precedence,
which has stayed the same from SQL 7.0 to SQL 2000, but about the rules for
implicit conversion. Those rules have changed between SQL 7.0 and 2000. In
SQL 2000 implicit conversion is always determined by data type precedence.
In SQL 7.0 there was an exception to that rule, namely if you compare a
column with a non-column value (literal, variable, function or expression).
In that case the datatype of the column would always take precedence over
the datatype that it was compared with.
You can check the behaviour by running the example below on both versions.
On SQL 7 you should get one row returned, because the literal 11 is
converted to a VARCHAR, but on SQL 2000 the column is converted to an int,
and no row is returned:
CREATE TABLE #t (a VARCHAR(11))
INSERT INTO #t(a) VALUES (1)
INSERT INTO #t(a) VALUES (2)
SELECT a FROM #t WHERE a > 11
Jacco Schalkwijk
SQL Server MVP
"Kumar" <Kumar@.discussions.microsoft.com> wrote in message
news:A3C8EA3E-827C-4897-A23D-A11291415BD5@.microsoft.com...
>I heard that if you upgrade user database from SQL 7.0 to SQL 2000(latest
>SPs),
> there is some data type conversion precedence problem and the same query
> will produce different results on SQL 7.0 and SQL 2000.
> I am sorry, I don't have any specific examples. If someone had this
> problem,
> please advice me with some examples.
> Thanks.
there is some data type conversion precedence problem and the same query
will produce different results on SQL 7.0 and SQL 2000.
I am sorry, I don't have any specific examples. If someone had this problem,
please advice me with some examples.
Thanks.
Strictly speaking I think your question is not about data type precedence,
which has stayed the same from SQL 7.0 to SQL 2000, but about the rules for
implicit conversion. Those rules have changed between SQL 7.0 and 2000. In
SQL 2000 implicit conversion is always determined by data type precedence.
In SQL 7.0 there was an exception to that rule, namely if you compare a
column with a non-column value (literal, variable, function or expression).
In that case the datatype of the column would always take precedence over
the datatype that it was compared with.
You can check the behaviour by running the example below on both versions.
On SQL 7 you should get one row returned, because the literal 11 is
converted to a VARCHAR, but on SQL 2000 the column is converted to an int,
and no row is returned:
CREATE TABLE #t (a VARCHAR(11))
INSERT INTO #t(a) VALUES (1)
INSERT INTO #t(a) VALUES (2)
SELECT a FROM #t WHERE a > 11
Jacco Schalkwijk
SQL Server MVP
"Kumar" <Kumar@.discussions.microsoft.com> wrote in message
news:A3C8EA3E-827C-4897-A23D-A11291415BD5@.microsoft.com...
>I heard that if you upgrade user database from SQL 7.0 to SQL 2000(latest
>SPs),
> there is some data type conversion precedence problem and the same query
> will produce different results on SQL 7.0 and SQL 2000.
> I am sorry, I don't have any specific examples. If someone had this
> problem,
> please advice me with some examples.
> Thanks.
Tuesday, February 14, 2012
Data transformation services
Hi
I was told that using DTS will allow me to schedule stored procedures to keep an sql database up to date. For example if a user registers but does not activate the registration, his details will be removed by a stored procedure which is scheduled to run every 24 hours. I use to use the global.asax file to fire a update by using a file containing a the date of the last update and then by adding 24 hours to it, it would execute a SP to delete unwanted data.
I have tried to install DTS with no success. I am running the following
Visual web studio express
SQL 2005 Express. (From SQLExpr_exe) and I have told it to install all the extra components
Installed SQLEXPR_Toolkit.exe with all its options
Installed SQLServer2005_DTS.MSI
When I go into the sql server using MS SQL Server Management Studio Express. I cannot see the Data transformation services node. I have also just installed server reports which I had no problems installing.
Can somebody please help me.
DTS is a SQL2000 component; SQL2005 hasa totally rewritten equivalent called SSIS. An SSIS (SQL Server Integration Sercvices) job amongst other things will run stored procedures for you. However it is SQL Agent that provides the scheduling capability.
|||Hi
Thanks for the reply. I need to know where to download the ssis installation application. The other thing is my service provider that I use uses SQL2000. Im developing in SQL Express 2005. How will I deploy the scheduled jobs to there server if im using the newer version.
Regards
Subscribe to:
Posts (Atom)