Hi,
Can anyone tell me when to use oleDB, ODBC, SqlConnection and Database
explorer to connect a sql server (from VB.net) where users have windows
security.
ThxHDI
Visit http://www.carlprothman.net/Default.aspx?tabid=81
"HDI" <hdinf@.hotmail.com> wrote in message
news:1168252383.802235.66360@.i15g2000cwa.googlegroups.com...
> Hi,
> Can anyone tell me when to use oleDB, ODBC, SqlConnection and Database
> explorer to connect a sql server (from VB.net) where users have windows
> security.
>
> Thx
>|||Thanx , but this paper explains how to use them not when/ why using
them.
Uri Dimant schreef:
> HDI
> Visit http://www.carlprothman.net/Default.aspx?tabid=81
>
>
> "HDI" <hdinf@.hotmail.com> wrote in message
> news:1168252383.802235.66360@.i15g2000cwa.googlegroups.com...
> > Hi,
> >
> > Can anyone tell me when to use oleDB, ODBC, SqlConnection and Database
> > explorer to connect a sql server (from VB.net) where users have windows
> > security.
> >
> >
> > Thx
> >|||HDI
I thought it was pretty clear if you use VB.NET
"HDI" <hdinf@.hotmail.com> wrote in message
news:1168253291.539504.44540@.38g2000cwa.googlegroups.com...
> Thanx , but this paper explains how to use them not when/ why using
> them.
>
> Uri Dimant schreef:
>> HDI
>> Visit http://www.carlprothman.net/Default.aspx?tabid=81
>>
>>
>> "HDI" <hdinf@.hotmail.com> wrote in message
>> news:1168252383.802235.66360@.i15g2000cwa.googlegroups.com...
>> > Hi,
>> >
>> > Can anyone tell me when to use oleDB, ODBC, SqlConnection and Database
>> > explorer to connect a sql server (from VB.net) where users have windows
>> > security.
>> >
>> >
>> > Thx
>> >
>|||Go for SqlConnection because oleDB and ODBC involve more layers between
the app and SQL Server. See
http://msdn2.microsoft.com/en-us/library/a6cd7c08.aspx for further info.
Showing posts with label users. Show all posts
Showing posts with label users. Show all posts
Thursday, March 29, 2012
Database concurrency
I'm wondering whether the following code would work if users are RAPIDLY registering (assumption) WITH the same username.
public bool UsernamExists(string username){string sql ="SELECT true FROM [users] WEHRE username = @.username;";return Convert.ToBoolean(comm.ExecuteScalar());}public bool Signup(User user){bool usernameExists = UsernameExists(user.Username);if( usernameExists )return false;//update or insert sql for user etc blah blah}If two users try to signup AT THE VERY SAME TIME (DOWN TO THE NANOSECOND), would this technique work? Do I have to wrap it in a transaction, stored procedure??
Thanks.
no that does not guarantee unique usernames.
You should add a unique constraint to your username column in the database to guarantee that duplicates cannot exist.
Then, you can still do your usernameexists check it you want, but you would also want to add an error handler (try/catch) to catch the exception that would occur in the unlikely event that two users simultaneously submitted the same usernames.
|||I know about unique column BUT would the technique I mentioned above work IN TERMS OF CODING? Like 2 users submitting AT THE VERY SAME TIME.|||
Yes the code would run, but...
if you dont have a unique constraint in your db, then you could end up with 2 records with the same username.
|||
Your code will not work because you have error in it
"SELECT true FROM [users]WHERE username = @.username;";
you have no comm object defined ad no parameter added to command. But if you update it you will have no guaranty that you will not have two user with the same name in your database. But you can modify you code to do something like this with transaction (safer) or not:
BEGIN TRAN
IF not exist(SELECT * FROM [users]WHERE username = @.username)
BEGIN
INSERT into [users](username)
Values (@.username)
SELECT 'True' [Result]
END
ELSE
SELECT 'False' [RESULT]
COMMIT TRAN
In this way you will save one round trip to SQL server an everything will be done in one shot and very fast.
Thanks
I know you have to use the comm object, excluded it out for the simplicity.
I thought about wrapping my sql syntax in a transaction with row locking, but I'm not to keen on writing that much sql syntax. I like to keep it minimal.
Is there a way the comm object could "perform" the transaction automatically? Can you show me a simple example?
Hi vze1r2ht
There is no way to make the comm object start a transaction automatically. You will need to start a transaction by explicitly calling SqlConnection.BeginTransaction() and assign the returned SqlTransaction object to a SqlCommand.
You can also perform this in a stored procedure by using BEGIN TRAN.
|||
Kevin Yu - MSFT:
Hi vze1r2ht
There is no way to make the comm object start a transaction automatically. You will need to start a transaction by explicitly calling SqlConnection.BeginTransaction() and assign the returned SqlTransaction object to a SqlCommand.
You can also perform this in a stored procedure by using BEGIN TRAN.
Thank you. Is there any major performance hit when using the SqlConnection.BeginTransaction() technique vs SQL's BEGIN Tran? Because of the massive amounts of transactions I will need to write for my classes, it will alll be inline if it was SQL and NOT stored procedures (hard to maintain).
|||Hi,
As far as I can see, they are the same. Not much differences.
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 log
SQL 2K
We developed a VB.NET application (SQL 2k db) where users can log into the
system and extract reports. We are planning to create a report with user
list who accessed the application in last 30 days.
Can this be achieved by querying directly to the database '
Thanks
JohnHello John,
This will have to be done when your application authenticates. It will need
to create an audit of who logs in and when. This isn't a function of the
database but a function of the way your application is designed.
Aaron Weiker
http://aaronweiker.com/
> SQL 2K
> We developed a VB.NET application (SQL 2k db) where users can log into
> the system and extract reports. We are planning to create a report
> with user list who accessed the application in last 30 days.
> Can this be achieved by querying directly to the database '
> Thanks
> John|||Hi John,
You can get this information, if you would have done one of the following;
(a) Enabled logging in your VB app (the user and datetime)
(b) Logging in the database tables. Again this should have been done with
your application. (the user and datetime)
(c) Enabled Auditing in the database server.
Are you using one super user to connect to SQL Server for all the
application users or are you connecting to SQL Server for every user
accessing your VB application?
In the former case, you will always see one user at the database level.
Thanks
Yogish
We developed a VB.NET application (SQL 2k db) where users can log into the
system and extract reports. We are planning to create a report with user
list who accessed the application in last 30 days.
Can this be achieved by querying directly to the database '
Thanks
JohnHello John,
This will have to be done when your application authenticates. It will need
to create an audit of who logs in and when. This isn't a function of the
database but a function of the way your application is designed.
Aaron Weiker
http://aaronweiker.com/
> SQL 2K
> We developed a VB.NET application (SQL 2k db) where users can log into
> the system and extract reports. We are planning to create a report
> with user list who accessed the application in last 30 days.
> Can this be achieved by querying directly to the database '
> Thanks
> John|||Hi John,
You can get this information, if you would have done one of the following;
(a) Enabled logging in your VB app (the user and datetime)
(b) Logging in the database tables. Again this should have been done with
your application. (the user and datetime)
(c) Enabled Auditing in the database server.
Are you using one super user to connect to SQL Server for all the
application users or are you connecting to SQL Server for every user
accessing your VB application?
In the former case, you will always see one user at the database level.
Thanks
Yogish
Database access log
SQL 2K
We developed a VB.NET application (SQL 2k db) where users can log into the
system and extract reports. We are planning to create a report with user
list who accessed the application in last 30 days.
Can this be achieved by querying directly to the database '
Thanks
JohnHi John,
You can get this information, if you would have done one of the following;
(a) Enabled logging in your VB app (the user and datetime)
(b) Logging in the database tables. Again this should have been done with
your application. (the user and datetime)
(c) Enabled Auditing in the database server.
Are you using one super user to connect to SQL Server for all the
application users or are you connecting to SQL Server for every user
accessing your VB application?
In the former case, you will always see one user at the database level.
Thanks
Yogish
We developed a VB.NET application (SQL 2k db) where users can log into the
system and extract reports. We are planning to create a report with user
list who accessed the application in last 30 days.
Can this be achieved by querying directly to the database '
Thanks
JohnHi John,
You can get this information, if you would have done one of the following;
(a) Enabled logging in your VB app (the user and datetime)
(b) Logging in the database tables. Again this should have been done with
your application. (the user and datetime)
(c) Enabled Auditing in the database server.
Are you using one super user to connect to SQL Server for all the
application users or are you connecting to SQL Server for every user
accessing your VB application?
In the former case, you will always see one user at the database level.
Thanks
Yogish
Database access log
SQL 2K
We developed a VB.NET application (SQL 2k db) where users can log into the
system and extract reports. We are planning to create a report with user
list who accessed the application in last 30 days.
Can this be achieved by querying directly to the database ?
Thanks
John
Hi John,
You can get this information, if you would have done one of the following;
(a) Enabled logging in your VB app (the user and datetime)
(b) Logging in the database tables. Again this should have been done with
your application. (the user and datetime)
(c) Enabled Auditing in the database server.
Are you using one super user to connect to SQL Server for all the
application users or are you connecting to SQL Server for every user
accessing your VB application?
In the former case, you will always see one user at the database level.
Thanks
Yogish
We developed a VB.NET application (SQL 2k db) where users can log into the
system and extract reports. We are planning to create a report with user
list who accessed the application in last 30 days.
Can this be achieved by querying directly to the database ?
Thanks
John
Hi John,
You can get this information, if you would have done one of the following;
(a) Enabled logging in your VB app (the user and datetime)
(b) Logging in the database tables. Again this should have been done with
your application. (the user and datetime)
(c) Enabled Auditing in the database server.
Are you using one super user to connect to SQL Server for all the
application users or are you connecting to SQL Server for every user
accessing your VB application?
In the former case, you will always see one user at the database level.
Thanks
Yogish
Database access log
SQL 2K
We developed a VB.NET application (SQL 2k db) where users can log into the
system and extract reports. We are planning to create a report with user
list who accessed the application in last 30 days.
Can this be achieved by querying directly to the database '
Thanks
JohnHi John,
You can get this information, if you would have done one of the following;
(a) Enabled logging in your VB app (the user and datetime)
(b) Logging in the database tables. Again this should have been done with
your application. (the user and datetime)
(c) Enabled Auditing in the database server.
Are you using one super user to connect to SQL Server for all the
application users or are you connecting to SQL Server for every user
accessing your VB application?
In the former case, you will always see one user at the database level.
Thanks
Yogish
We developed a VB.NET application (SQL 2k db) where users can log into the
system and extract reports. We are planning to create a report with user
list who accessed the application in last 30 days.
Can this be achieved by querying directly to the database '
Thanks
JohnHi John,
You can get this information, if you would have done one of the following;
(a) Enabled logging in your VB app (the user and datetime)
(b) Logging in the database tables. Again this should have been done with
your application. (the user and datetime)
(c) Enabled Auditing in the database server.
Are you using one super user to connect to SQL Server for all the
application users or are you connecting to SQL Server for every user
accessing your VB application?
In the former case, you will always see one user at the database level.
Thanks
Yogish
Database Access
Is it possible to get a list of all users that accessed a particular
Sql Server database? ThanksOnly is you had a trace running that captured the user names or logins.
Andrew J. Kelly SQL MVP
"S Chapman" <s_chapman47@.hotmail.co.uk> wrote in message
news:1140541552.276198.144260@.g43g2000cwa.googlegroups.com...
> Is it possible to get a list of all users that accessed a particular
> Sql Server database? Thanks
>|||sp_who ?
"S Chapman" <s_chapman47@.hotmail.co.uk> wrote in message
news:1140541552.276198.144260@.g43g2000cwa.googlegroups.com...
> Is it possible to get a list of all users that accessed a particular
> Sql Server database? Thanks
>|||No, the current users can be viewed with sp_who or sp_who2 but to see
the users in the past you have to use a log reader to see which user
issued a command against a database.
HTH, Jens Suessmeyer.|||http://msdn.microsoft.com/library/d...>
ity_2ard.asp
http://msdn.microsoft.com/library/d...>
erf_4wl5.asp
"S Chapman" <s_chapman47@.hotmail.co.uk> wrote in message
news:1140541552.276198.144260@.g43g2000cwa.googlegroups.com...
> Is it possible to get a list of all users that accessed a particular
> Sql Server database? Thanks
>|||sp_you
"Ben" <bennett@.newsgroups.nospam> wrote in message
news:41773$43fb55f3$d8445835$6596@.FUSE.NET...
> sp_who ?
> "S Chapman" <s_chapman47@.hotmail.co.uk> wrote in message
> news:1140541552.276198.144260@.g43g2000cwa.googlegroups.com...
>
Sql Server database? ThanksOnly is you had a trace running that captured the user names or logins.
Andrew J. Kelly SQL MVP
"S Chapman" <s_chapman47@.hotmail.co.uk> wrote in message
news:1140541552.276198.144260@.g43g2000cwa.googlegroups.com...
> Is it possible to get a list of all users that accessed a particular
> Sql Server database? Thanks
>|||sp_who ?
"S Chapman" <s_chapman47@.hotmail.co.uk> wrote in message
news:1140541552.276198.144260@.g43g2000cwa.googlegroups.com...
> Is it possible to get a list of all users that accessed a particular
> Sql Server database? Thanks
>|||No, the current users can be viewed with sp_who or sp_who2 but to see
the users in the past you have to use a log reader to see which user
issued a command against a database.
HTH, Jens Suessmeyer.|||http://msdn.microsoft.com/library/d...>
ity_2ard.asp
http://msdn.microsoft.com/library/d...>
erf_4wl5.asp
"S Chapman" <s_chapman47@.hotmail.co.uk> wrote in message
news:1140541552.276198.144260@.g43g2000cwa.googlegroups.com...
> Is it possible to get a list of all users that accessed a particular
> Sql Server database? Thanks
>|||sp_you
"Ben" <bennett@.newsgroups.nospam> wrote in message
news:41773$43fb55f3$d8445835$6596@.FUSE.NET...
> sp_who ?
> "S Chapman" <s_chapman47@.hotmail.co.uk> wrote in message
> news:1140541552.276198.144260@.g43g2000cwa.googlegroups.com...
>
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 help?
Which data type do I need to assign to my field to let users enter characters such as +=-_|\ etc...pretty much every single character from a standard keyboard...
thanksAny of the CHAR() related types, such as CHAR(), VARCHAR(), NCHAR(), NVARCHAR(), or TEXT should do nicely.
-PatP|||I have varchar now (on a web site with sql server as a database) but only save the letter and number for this example ab+ not the plus...
But I do have function, that I will probably have to customize,that is causing it only to save letters and numbers,
Thanks for your help once again
thanksAny of the CHAR() related types, such as CHAR(), VARCHAR(), NCHAR(), NVARCHAR(), or TEXT should do nicely.
-PatP|||I have varchar now (on a web site with sql server as a database) but only save the letter and number for this example ab+ not the plus...
But I do have function, that I will probably have to customize,that is causing it only to save letters and numbers,
Thanks for your help once again
Sunday, February 19, 2012
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.
Data type for storing MS Word documents/large data type?
Hi all,
I am bginning to build a SQL Server database that will be used to store
documents from various users. The document types will mostly be MS Word and
MS PowerPoint. I want to know what datatype to use in the database to store
these kinds of documents.
The data will be fed to SQL Server via an ASP.Net-based website. I would
also like to know if I can just use standard ASP.Net database objects to
transfer these large amounts of data.
Many thanks for any advice or a point in the right direction.
JustinHave you done any research on the pros and cons of storing the documents in
the database, vs. using a file system? It is going to be much more
efficient to store the *paths* to the files in the database. Have a read:
http://www.aspfaq.com/2149
"Justin Little" <JustinLittle@.discussions.microsoft.com> wrote in message
news:E4D7888D-C902-4B9F-AE6A-7A8FB2F6C921@.microsoft.com...
> Hi all,
> I am bginning to build a SQL Server database that will be used to store
> documents from various users. The document types will mostly be MS Word
> and
> MS PowerPoint. I want to know what datatype to use in the database to
> store
> these kinds of documents.
> The data will be fed to SQL Server via an ASP.Net-based website. I would
> also like to know if I can just use standard ASP.Net database objects to
> transfer these large amounts of data.
> Many thanks for any advice or a point in the right direction.
> Justin
>
I am bginning to build a SQL Server database that will be used to store
documents from various users. The document types will mostly be MS Word and
MS PowerPoint. I want to know what datatype to use in the database to store
these kinds of documents.
The data will be fed to SQL Server via an ASP.Net-based website. I would
also like to know if I can just use standard ASP.Net database objects to
transfer these large amounts of data.
Many thanks for any advice or a point in the right direction.
JustinHave you done any research on the pros and cons of storing the documents in
the database, vs. using a file system? It is going to be much more
efficient to store the *paths* to the files in the database. Have a read:
http://www.aspfaq.com/2149
"Justin Little" <JustinLittle@.discussions.microsoft.com> wrote in message
news:E4D7888D-C902-4B9F-AE6A-7A8FB2F6C921@.microsoft.com...
> Hi all,
> I am bginning to build a SQL Server database that will be used to store
> documents from various users. The document types will mostly be MS Word
> and
> MS PowerPoint. I want to know what datatype to use in the database to
> store
> these kinds of documents.
> The data will be fed to SQL Server via an ASP.Net-based website. I would
> also like to know if I can just use standard ASP.Net database objects to
> transfer these large amounts of data.
> Many thanks for any advice or a point in the right direction.
> Justin
>
Subscribe to:
Posts (Atom)