Money Data Type: user

Showing posts with label user. Show all posts

Friday, March 30, 2012

More on restoring database in user instance - exclusive use not obtained

Hi all. I have read and implemented the very helpful threads on backing up and restoring with user instance posted with MFriedlander. Thank you. However, during the rs.SqlRestore(srv) command I am getting the following error "Restore failed for Server '\\.\pipe\4A1F91FF-F6FE-45\tsql\query'. "
"Exclusive access could not be obtained because the database is in use."

I have implemented the changedatabase method as described in that thread.

SqlConnection.ChangeDatabase("master")

right before the line

rs.SqlRestore(srv)

I do not use the default instance of sql express, but I do use an instance called 'test' for my app. Should my changedatabase method also refer to my sql instance?

I am running from VS 2005 debug (F5) when it fails and cannot think of anything that would be locking it. Any help would be appreciated. Thank you. Below is the full code of the restore.

Robert

Dim sqlconnection As SqlConnection = New SqlConnection(My.Settings.dbTestConnectionString)

sqlconnection.Open()

MsgBox(sqlconnection.Database.ToString())

Dim ServerConnection As ServerConnection = New ServerConnection(sqlconnection)

Dim srv As Server = New Server(ServerConnection)

'Declare a BackupDeviceItem by supplying the backup device file name in the constructor, and the type of device is a file.

Dim bdi As BackupDeviceItem

bdi = New BackupDeviceItem(txtRestoreFile.Text, DeviceType.File)

''Define a Restore object variable.

Dim rs As New Restore

'Set the NoRecovery property to true, so the transactions are not recovered.

rs.NoRecovery = True

rs.ReplaceDatabase = True

'Add the device that contains the full database backup to the Restore object.

rs.Devices.Add(bdi)

'don't know why the below norecovery is changed to false

rs.NoRecovery = False

'Specify the database name.

rs.Database = sqlconnection.Database.ToString()

sqlconnection.ChangeDatabase("master")

'Restore the full database backup with no recovery.

rs.SqlRestore(srv)

Hi Robert,

I believe the problem is at

Code Snippet

rs.NoRecovery=True

It should be False, but if you read the comments about NoRecovery in BOL you'll see that True leaves the database in the recovering state, which makes it appear that it is in use the next time you try to run your Restore code. I see that you have it set in your code twice, once each way, and I'm not sure of the impact of doing that is. (It seems it would just reset it.) In any case, I'd clean the code so you're only setting NoRecovery once, and you're setting it to False.

I have a clean version of the code I wrote based on the thread you mention available in the blog, find it here. The code is acutally C#, but the SMO part is identical between the two languages once you've defined the objects.

Mike

|||

Hi Mike-

unfortunately changing the norecovery to false did not stop the error. I got it to work by changing the sqlconnection from referencing the

My.Settings.testConnectionString and replaced it with the connection string spelled out. I toggle between the 2 lines of code and the my.settings consistently doesn't work while the fully spelled out connection string works. I don't get it, I got lucky, but it works and I thank you very much for your support. The only time the restore doesn't work is immediately after running the backup code. Restarting the application fixes this and I can find an elegant way to fix this or work around for it, unless you have any ideas. Thanks again. Robert

|||

Hi Robert,

I think there may be a problem with your code...I have successfully implemented SMO.Restore in my app, and I noticed a critical difference between my restore code and yours:

My restore code (partial):

Code Snippet

Dim dbName As String

Dim SqlCon As SqlClient.SqlConnection = New SqlClient.SqlConnection(My.Settings.UserDBConStr)

SqlCon.Open()

dbName = SqlCon.Database.ToString()

SqlCon.ChangeDatabase("master")

Dim SC As New ServerConnection(SqlCon)

Dim srv As New Smo.Server(SC)

'Verify User Instance connection

Console.WriteLine("User Instance Server: " & srv.InstanceName.ToString)

Please notice that I changed the SqlConnection database, SqlCon.ChangeDatabase("master"), before making a connection to the server using SqlCon. I believe that in your code, the SMO.Server is still using the database you are trying to restore because you made the server connection using the restore database.

Here is the rest of my restore code:

Code Snippet

Dim bdi As New Smo.BackupDeviceItem(RestoreDir, Smo.DeviceType.File)

'***Set backup details

Dim resDB As New Smo.Restore

resDB.Devices.Add(bdi)

resDB.NoRecovery = False

resDB.ReplaceDatabase = True

resDB.Database = dbName

'Run SqlBackup to perform the full database backup on the instance of SQL Server.

resDB.SqlRestore(srv)

My code is based on the guidance from Mike Waschal's blog, here

Hope that helps you.

Andre

More on restoring database in user instance - exclusive use not obtained

I have implemented the changedatabase method as described in that thread.

SqlConnection.ChangeDatabase("master")

right before the line

rs.SqlRestore(srv)

I do not use the default instance of sql express, but I do use an instance called 'test' for my app. Should my changedatabase method also refer to my sql instance?

I am running from VS 2005 debug (F5) when it fails and cannot think of anything that would be locking it. Any help would be appreciated. Thank you. Below is the full code of the restore.

Robert

Dim sqlconnection As SqlConnection = New SqlConnection(My.Settings.dbTestConnectionString)

sqlconnection.Open()

MsgBox(sqlconnection.Database.ToString())

Dim ServerConnection As ServerConnection = New ServerConnection(sqlconnection)

Dim srv As Server = New Server(ServerConnection)

'Declare a BackupDeviceItem by supplying the backup device file name in the constructor, and the type of device is a file.

Dim bdi As BackupDeviceItem

bdi = New BackupDeviceItem(txtRestoreFile.Text, DeviceType.File)

''Define a Restore object variable.

Dim rs As New Restore

'Set the NoRecovery property to true, so the transactions are not recovered.

rs.NoRecovery = True

rs.ReplaceDatabase = True

'Add the device that contains the full database backup to the Restore object.

rs.Devices.Add(bdi)

'don't know why the below norecovery is changed to false

rs.NoRecovery = False

'Specify the database name.

rs.Database = sqlconnection.Database.ToString()

sqlconnection.ChangeDatabase("master")

'Restore the full database backup with no recovery.

rs.SqlRestore(srv)

Hi Robert,

I believe the problem is at

Code Snippet

rs.NoRecovery=True

Mike

|||

Hi Mike-

unfortunately changing the norecovery to false did not stop the error. I got it to work by changing the sqlconnection from referencing the

|||

Hi Robert,

I think there may be a problem with your code...I have successfully implemented SMO.Restore in my app, and I noticed a critical difference between my restore code and yours:

My restore code (partial):

Code Snippet

Dim dbName As String

Dim SqlCon As SqlClient.SqlConnection = New SqlClient.SqlConnection(My.Settings.UserDBConStr)

SqlCon.Open()

dbName = SqlCon.Database.ToString()

SqlCon.ChangeDatabase("master")

Dim SC As New ServerConnection(SqlCon)

Dim srv As New Smo.Server(SC)

'Verify User Instance connection

Console.WriteLine("User Instance Server: " & srv.InstanceName.ToString)

Here is the rest of my restore code:

Code Snippet

Dim bdi As New Smo.BackupDeviceItem(RestoreDir, Smo.DeviceType.File)

'***Set backup details

Dim resDB As New Smo.Restore

resDB.Devices.Add(bdi)

resDB.NoRecovery = False

resDB.ReplaceDatabase = True

resDB.Database = dbName

'Run SqlBackup to perform the full database backup on the instance of SQL Server.

resDB.SqlRestore(srv)

My code is based on the guidance from Mike Waschal's blog, here

Hope that helps you.

Andre

Wednesday, March 28, 2012

More logical drives VS more striped drives

A user called ZoomZoom made the posting below on Tech Republic. As it did
not get a reply there I am interested to know what the community's opinion
is.
Some of my Raid 10's are made up of 8 drives. Would it have been better to
have created 2 Raid 10's of 4 drives and then have two data files in the
file group?
I would expect an 8 drive Raid 10 to give a higher sequential data rate but
what about random IO when one has many users?
Regard
Paul Cahill
...
Posted by ZoomZoom
I have seen many articles that say to place log files separate from db
files, and use raid 1+0 for the db... but I haven't found anything to answer
this question for me though (unless it should be so obvious that I'm just
not seeing it).
Is it faster for the database to have the tables split into separate files
and put each file in smaller raid 1+0 configurations or is it faster to use
the same number of drives in a single raid 1+0. For example: 8 drives could
be split into 2 raid 1+0 arrays (4 each) or they could be configured in one
large raid 1+0 array (8 drives). The end result would be striping between 2
sets of 2 drives (in the 4 disk array due to 2 being only mirrors) or
striping between 4 drives (in the 8 disk array).
I guess another way of looking at the question is how much performance does
each additional pair of disks in a raid 1+0 array really add. Can an 8 disk
array handle twice as many IO's as as a 4 disk array? Or does it only add
maybe 30% more IO ability? If the later, would it make sense to add blocks
of 4 disk arrays and split the database tables into multiple files instead?
Would this theory work like putting the logs on separate drives from the
database files?
I realize it's an expensive solution... but with the price of database
per-processor licenses being what they are... it makes sense to try to
squeeze as much performance out of your database server as possible.I have thought about it many times.
When using more disks in one volume, random IO rises, but not by the same
factor as number of disks.
If load of these two files is the same, then two volumes are better, but if
it is not, one volume is better.
Are you sure that load is always uniformly distributed between files? Than
use separate volumes.
In real world the load always fluctuates so it is not possible to make a
common conclusion.
"Paul Cahill" <anon@.anon.com> píse v diskusním príspevku
news:%237m9RqZuHHA.3368@.TK2MSFTNGP02.phx.gbl...
>A user called ZoomZoom made the posting below on Tech Republic. As it did
>not get a reply there I am interested to know what the community's opinion
>is.
> Some of my Raid 10's are made up of 8 drives. Would it have been better to
> have created 2 Raid 10's of 4 drives and then have two data files in the
> file group?
> I would expect an 8 drive Raid 10 to give a higher sequential data rate
> but what about random IO when one has many users?
> Regard
> Paul Cahill
> ...
> Posted by ZoomZoom
>
> I have seen many articles that say to place log files separate from db
> files, and use raid 1+0 for the db... but I haven't found anything to
> answer this question for me though (unless it should be so obvious that
> I'm just not seeing it).
> Is it faster for the database to have the tables split into separate files
> and put each file in smaller raid 1+0 configurations or is it faster to
> use the same number of drives in a single raid 1+0. For example: 8 drives
> could be split into 2 raid 1+0 arrays (4 each) or they could be configured
> in one large raid 1+0 array (8 drives). The end result would be striping
> between 2 sets of 2 drives (in the 4 disk array due to 2 being only
> mirrors) or striping between 4 drives (in the 8 disk array).
> I guess another way of looking at the question is how much performance
> does each additional pair of disks in a raid 1+0 array really add. Can an
> 8 disk array handle twice as many IO's as as a 4 disk array? Or does it
> only add maybe 30% more IO ability? If the later, would it make sense to
> add blocks of 4 disk arrays and split the database tables into multiple
> files instead? Would this theory work like putting the logs on separate
> drives from the database files?
> I realize it's an expensive solution... but with the price of database
> per-processor licenses being what they are... it makes sense to try to
> squeeze as much performance out of your database server as possible.
>|||I pretty much agree with that explanation. If you know exactly how your
files will be accessed and they compete with each other at a fairly
intensive level you might get better performance from splitting them. This
is true of separating tempdb from data files as well. But if you don't know
or the load is not too high you are most likely better off having a larger
array with all the data files on it. Now placing the log files on another
array is always a good idea.
--
Andrew J. Kelly SQL MVP
"Jirí Lejsek" <jlejsek@.na_volnym_v_cesku> wrote in message
news:urgxfhauHHA.1184@.TK2MSFTNGP04.phx.gbl...
>I have thought about it many times.
> When using more disks in one volume, random IO rises, but not by the same
> factor as number of disks.
> If load of these two files is the same, then two volumes are better, but
> if it is not, one volume is better.
> Are you sure that load is always uniformly distributed between files? Than
> use separate volumes.
> In real world the load always fluctuates so it is not possible to make a
> common conclusion.
> "Paul Cahill" <anon@.anon.com> píse v diskusním príspevku
> news:%237m9RqZuHHA.3368@.TK2MSFTNGP02.phx.gbl...
>>A user called ZoomZoom made the posting below on Tech Republic. As it did
>>not get a reply there I am interested to know what the community's opinion
>>is.
>> Some of my Raid 10's are made up of 8 drives. Would it have been better
>> to have created 2 Raid 10's of 4 drives and then have two data files in
>> the file group?
>> I would expect an 8 drive Raid 10 to give a higher sequential data rate
>> but what about random IO when one has many users?
>> Regard
>> Paul Cahill
>> ...
>> Posted by ZoomZoom
>>
>> I have seen many articles that say to place log files separate from db
>> files, and use raid 1+0 for the db... but I haven't found anything to
>> answer this question for me though (unless it should be so obvious that
>> I'm just not seeing it).
>> Is it faster for the database to have the tables split into separate
>> files and put each file in smaller raid 1+0 configurations or is it
>> faster to use the same number of drives in a single raid 1+0. For
>> example: 8 drives could be split into 2 raid 1+0 arrays (4 each) or they
>> could be configured in one large raid 1+0 array (8 drives). The end
>> result would be striping between 2 sets of 2 drives (in the 4 disk array
>> due to 2 being only mirrors) or striping between 4 drives (in the 8 disk
>> array).
>> I guess another way of looking at the question is how much performance
>> does each additional pair of disks in a raid 1+0 array really add. Can an
>> 8 disk array handle twice as many IO's as as a 4 disk array? Or does it
>> only add maybe 30% more IO ability? If the later, would it make sense to
>> add blocks of 4 disk arrays and split the database tables into multiple
>> files instead? Would this theory work like putting the logs on separate
>> drives from the database files?
>> I realize it's an expensive solution... but with the price of database
>> per-processor licenses being what they are... it makes sense to try to
>> squeeze as much performance out of your database server as possible.
>>
>|||On Thu, 28 Jun 2007 17:02:37 -0400, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:
>I pretty much agree with that explanation. If you know exactly how your
>files will be accessed and they compete with each other at a fairly
>intensive level you might get better performance from splitting them. This
>is true of separating tempdb from data files as well. But if you don't know
>or the load is not too high you are most likely better off having a larger
>array with all the data files on it. Now placing the log files on another
>array is always a good idea.
The problem I have with targetting files at particular drives is that
it tunes for one particular load only. Tune it for the daytime OLTP
and it could be sub-optimal during heavy duty batch processing at
night. One big striped set spreads the load pretty evenly across all
spindles, regardless of load, and regardless of how the load changes
over time. It might not be as fast in some special instances, but I
think on average one big set of drives is going to perform as well or
better. Targetting files also means one more thing for the DBA to
keep an eye on and worry about changing later when the load changes or
space grows differently than expected.
Logs, of course, are another story as everyone knows.
Roy Harvey
Beacon Falls, CT|||Right that is why I qualified it with knowing "Exactly" how they are used
which is hard to do.
--
Andrew J. Kelly SQL MVP
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:v798831lqurvqepcvtb9q294c4ha95liq9@.4ax.com...
> On Thu, 28 Jun 2007 17:02:37 -0400, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote:
>>I pretty much agree with that explanation. If you know exactly how your
>>files will be accessed and they compete with each other at a fairly
>>intensive level you might get better performance from splitting them. This
>>is true of separating tempdb from data files as well. But if you don't
>>know
>>or the load is not too high you are most likely better off having a larger
>>array with all the data files on it. Now placing the log files on another
>>array is always a good idea.
> The problem I have with targetting files at particular drives is that
> it tunes for one particular load only. Tune it for the daytime OLTP
> and it could be sub-optimal during heavy duty batch processing at
> night. One big striped set spreads the load pretty evenly across all
> spindles, regardless of load, and regardless of how the load changes
> over time. It might not be as fast in some special instances, but I
> think on average one big set of drives is going to perform as well or
> better. Targetting files also means one more thing for the DBA to
> keep an eye on and worry about changing later when the load changes or
> space grows differently than expected.
> Logs, of course, are another story as everyone knows.
> Roy Harvey
> Beacon Falls, CT

More logical drives VS more striped drives

A user called ZoomZoom made the posting below on Tech Republic. As it did
not get a reply there I am interested to know what the community's opinion
is.
Some of my Raid 10's are made up of 8 drives. Would it have been better to
have created 2 Raid 10's of 4 drives and then have two data files in the
file group?
I would expect an 8 drive Raid 10 to give a higher sequential data rate but
what about random IO when one has many users?
Regard
Paul Cahill
...
Posted by ZoomZoom
I have seen many articles that say to place log files separate from db
files, and use raid 1+0 for the db... but I haven't found anything to answer
this question for me though (unless it should be so obvious that I'm just
not seeing it).
Is it faster for the database to have the tables split into separate files
and put each file in smaller raid 1+0 configurations or is it faster to use
the same number of drives in a single raid 1+0. For example: 8 drives could
be split into 2 raid 1+0 arrays (4 each) or they could be configured in one
large raid 1+0 array (8 drives). The end result would be striping between 2
sets of 2 drives (in the 4 disk array due to 2 being only mirrors) or
striping between 4 drives (in the 8 disk array).
I guess another way of looking at the question is how much performance does
each additional pair of disks in a raid 1+0 array really add. Can an 8 disk
array handle twice as many IO's as as a 4 disk array? Or does it only add
maybe 30% more IO ability? If the later, would it make sense to add blocks
of 4 disk arrays and split the database tables into multiple files instead?
Would this theory work like putting the logs on separate drives from the
database files?
I realize it's an expensive solution... but with the price of database
per-processor licenses being what they are... it makes sense to try to
squeeze as much performance out of your database server as possible.I have thought about it many times.
When using more disks in one volume, random IO rises, but not by the same
factor as number of disks.
If load of these two files is the same, then two volumes are better, but if
it is not, one volume is better.
Are you sure that load is always uniformly distributed between files? Than
use separate volumes.
In real world the load always fluctuates so it is not possible to make a
common conclusion.
"Paul Cahill" <anon@.anon.com> pse v diskusnm prspevku
news:%237m9RqZuHHA.3368@.TK2MSFTNGP02.phx.gbl...
>A user called ZoomZoom made the posting below on Tech Republic. As it did
>not get a reply there I am interested to know what the community's opinion
>is.
> Some of my Raid 10's are made up of 8 drives. Would it have been better to
> have created 2 Raid 10's of 4 drives and then have two data files in the
> file group?
> I would expect an 8 drive Raid 10 to give a higher sequential data rate
> but what about random IO when one has many users?
> Regard
> Paul Cahill
> ...
> Posted by ZoomZoom
>
> I have seen many articles that say to place log files separate from db
> files, and use raid 1+0 for the db... but I haven't found anything to
> answer this question for me though (unless it should be so obvious that
> I'm just not seeing it).
> Is it faster for the database to have the tables split into separate files
> and put each file in smaller raid 1+0 configurations or is it faster to
> use the same number of drives in a single raid 1+0. For example: 8 drives
> could be split into 2 raid 1+0 arrays (4 each) or they could be configured
> in one large raid 1+0 array (8 drives). The end result would be striping
> between 2 sets of 2 drives (in the 4 disk array due to 2 being only
> mirrors) or striping between 4 drives (in the 8 disk array).
> I guess another way of looking at the question is how much performance
> does each additional pair of disks in a raid 1+0 array really add. Can an
> 8 disk array handle twice as many IO's as as a 4 disk array? Or does it
> only add maybe 30% more IO ability? If the later, would it make sense to
> add blocks of 4 disk arrays and split the database tables into multiple
> files instead? Would this theory work like putting the logs on separate
> drives from the database files?
> I realize it's an expensive solution... but with the price of database
> per-processor licenses being what they are... it makes sense to try to
> squeeze as much performance out of your database server as possible.
>|||I pretty much agree with that explanation. If you know exactly how your
files will be accessed and they compete with each other at a fairly
intensive level you might get better performance from splitting them. This
is true of separating tempdb from data files as well. But if you don't know
or the load is not too high you are most likely better off having a larger
array with all the data files on it. Now placing the log files on another
array is always a good idea.
Andrew J. Kelly SQL MVP
"Jir Lejsek" <jlejsek@.na_volnym_v_cesku> wrote in message
news:urgxfhauHHA.1184@.TK2MSFTNGP04.phx.gbl...
>I have thought about it many times.
> When using more disks in one volume, random IO rises, but not by the same
> factor as number of disks.
> If load of these two files is the same, then two volumes are better, but
> if it is not, one volume is better.
> Are you sure that load is always uniformly distributed between files? Than
> use separate volumes.
> In real world the load always fluctuates so it is not possible to make a
> common conclusion.
> "Paul Cahill" <anon@.anon.com> pse v diskusnm prspevku
> news:%237m9RqZuHHA.3368@.TK2MSFTNGP02.phx.gbl...
>|||On Thu, 28 Jun 2007 17:02:37 -0400, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:

>I pretty much agree with that explanation. If you know exactly how your
>files will be accessed and they compete with each other at a fairly
>intensive level you might get better performance from splitting them. This
>is true of separating tempdb from data files as well. But if you don't know
>or the load is not too high you are most likely better off having a larger
>array with all the data files on it. Now placing the log files on another
>array is always a good idea.
The problem I have with targetting files at particular drives is that
it tunes for one particular load only. Tune it for the daytime OLTP
and it could be sub-optimal during heavy duty batch processing at
night. One big striped set spreads the load pretty evenly across all
spindles, regardless of load, and regardless of how the load changes
over time. It might not be as fast in some special instances, but I
think on average one big set of drives is going to perform as well or
better. Targetting files also means one more thing for the DBA to
keep an eye on and worry about changing later when the load changes or
space grows differently than expected.
Logs, of course, are another story as everyone knows.
Roy Harvey
Beacon Falls, CT|||Right that is why I qualified it with knowing "Exactly" how they are used
which is hard to do.
Andrew J. Kelly SQL MVP
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:v798831lqurvqepcvtb9q294c4ha95liq9@.
4ax.com...
> On Thu, 28 Jun 2007 17:02:37 -0400, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote:
>
> The problem I have with targetting files at particular drives is that
> it tunes for one particular load only. Tune it for the daytime OLTP
> and it could be sub-optimal during heavy duty batch processing at
> night. One big striped set spreads the load pretty evenly across all
> spindles, regardless of load, and regardless of how the load changes
> over time. It might not be as fast in some special instances, but I
> think on average one big set of drives is going to perform as well or
> better. Targetting files also means one more thing for the DBA to
> keep an eye on and worry about changing later when the load changes or
> space grows differently than expected.
> Logs, of course, are another story as everyone knows.
> Roy Harvey
> Beacon Falls, CTsql

Monday, March 26, 2012

More bugs in SSRS 2k5 ....

Here is another one thats been bothering me for quite some time and I believe for user with regional and language settings to non-US English.

I have my regional settings set to Canadian English. I am using Calender controls to pick up Start, End dates in my report. When I pick a date from calender it automatically converts it behind the scenes to US English I believe. Therefore when I run the report, I get the result set, say from Oct 8 instead of Aug 10. Further, if I select the date as 13 Aug, the following error gets thrown (as a result that the SSRS is not able to identify 13 being any month of course)

"An error occured during local report processing. The value provided for the report parameter 'StartDate' is not valid for its type".

Is there a solution to this problem? The underlying date type is DateTime, so why should it matter which regional setting I use?

Comments anyone?

EDIT

The SSRS controls comes with Calendar property you need to use that, because if you use the NOW SSRS will run your report with current date time in your box, I have used it. But I think you have mistaken TimeZone for DateTime, you can get DateTime in SQL Server while TimeZone is .NET there are work around solutions now and it is implemented in VS2008. Try the thread below for details.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1860968&SiteID=1

|||

I never said I am using TimeZone. Let me try to explain once more. I have my regional settings in Control Panel >> Regional and Language Options>>Regional Options>>Standards and Formats - set to English(Canada) and Location set to Canada, further in the Advanced Tab its also set to English (Canada).

Now back in report, I have defined two report parameters: StartDate and EndDate. Their data type is set to DataTime. Therefore when I preview the report, the parameters display Calenders next to the boxes automatically. I pick a date, say 14 Aug 2007 from this calender. The date is formatted as 14/08/2007 in the text box (Canadian format dd/mm/yyyy). Then I hit the View Report button, and its then that the error gets thrown because for some reason report server/sql server does not accept this date format. Notice that I have not done any thing fancy with the date time parameter, simply out of the box usage.

So, I did this test. I switched the computer settings to English (United States) and wolla...everythings works fine. However thats not desirable as this would require the users to use US-English is all applications such as Word, Excel etc.

The thread you provided the link for does not address my problem unfortunately, it talks about TimeZone in .NET.

I suspect that users with English (UK, AUS, NZ) are also facing same problem. I would highly appreciate if some one can provide a solution to this problem.

|||

Moved to the reporting services forum...

|||? Dint I post in SSRS in first place?

|||

SQL Server can do what you want you just need the correct table design, 2005 also comes with GETUTDATE function you can use. Run a search in the BOL for GETUTCDATE and CAST and CONVERT, there is a guideline for SQL Server DateTime design and how to get datetime in the format you want in the links below.

http://www.karaszi.com/SQLServer/info_datetime.asp

http://sqljunkies.com/HowTo/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk

|||This is interesting as I have reports that are used by people with a variety of regional settings, probably at least half of them set to dd/mm/yyyy.

Doing it exactly as you describe works correctly for us, UK users set the date in the format that is consistent with their regional settings, US as mm/dd/yyyy, and both return the same results.

I will say that we have seen at odd times that if you pick too fast from the calendar control it doesn't seem to quite register (even though the correct date shows) and I've gotten that error. Don't know if that helps much.

What version number are you at?

|||To add to this discussion, I've noticed that Report Manager and Report Server behave differently when viewing the same report. One suffers from the bug and the other doesn't.

|||

Well, I do not have SQL Server SP2 installed. Not sure if installing that would make a diff and I can't do that without DBA's consent.

D Wall wrote:
This is interesting as I have reports that are used by people with a variety of regional settings, probably at least half of them set to dd/mm/yyyy.

Doing it exactly as you describe works correctly for us, UK users set the date in the format that is consistent with their regional settings, US as mm/dd/yyyy, and both return the same results.

I will say that we have seen at odd times that if you pick too fast from the calendar control it doesn't seem to quite register (even though the correct date shows) and I've gotten that error. Don't know if that helps much.

What version number are you at?

|||

Ok, so may be who ever wrote this Calender control had a grudge against us Canadians. Jus kidding Smile

D Wall wrote:
This is interesting as I have reports that are used by people with a variety of regional settings, probably at least half of them set to dd/mm/yyyy.

Doing it exactly as you describe works correctly for us, UK users set the date in the format that is consistent with their regional settings, US as mm/dd/yyyy, and both return the same results.

I will say that we have seen at odd times that if you pick too fast from the calendar control it doesn't seem to quite register (even though the correct date shows) and I've gotten that error. Don't know if that helps much.

What version number are you at?

sql

More bugs in SSRS 2k5 ....

Here is another one thats been bothering me for quite some time and I believe for user with regional and language settings to non-US English.

"An error occured during local report processing. The value provided for the report parameter 'StartDate' is not valid for its type".

Is there a solution to this problem? The underlying date type is DateTime, so why should it matter which regional setting I use?

Comments anyone?

EDIT

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1860968&SiteID=1

|||

The thread you provided the link for does not address my problem unfortunately, it talks about TimeZone in .NET.

I suspect that users with English (UK, AUS, NZ) are also facing same problem. I would highly appreciate if some one can provide a solution to this problem.

|||

Moved to the reporting services forum...

|||? Dint I post in SSRS in first place?|||

http://www.karaszi.com/SQLServer/info_datetime.asp

http://sqljunkies.com/HowTo/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk

|||This is interesting as I have reports that are used by people with a variety of regional settings, probably at least half of them set to dd/mm/yyyy.

Doing it exactly as you describe works correctly for us, UK users set the date in the format that is consistent with their regional settings, US as mm/dd/yyyy, and both return the same results.

What version number are you at?

|||To add to this discussion, I've noticed that Report Manager and Report Server behave differently when viewing the same report. One suffers from the bug and the other doesn't.|||

Well, I do not have SQL Server SP2 installed. Not sure if installing that would make a diff and I can't do that without DBA's consent.

D Wall wrote:
This is interesting as I have reports that are used by people with a variety of regional settings, probably at least half of them set to dd/mm/yyyy.
Doing it exactly as you describe works correctly for us, UK users set the date in the format that is consistent with their regional settings, US as mm/dd/yyyy, and both return the same results.

I will say that we have seen at odd times that if you pick too fast from the calendar control it doesn't seem to quite register (even though the correct date shows) and I've gotten that error. Don't know if that helps much.

What version number are you at?

|||

Ok, so may be who ever wrote this Calender control had a grudge against us Canadians. Jus kidding Smile

D Wall wrote:
This is interesting as I have reports that are used by people with a variety of regional settings, probably at least half of them set to dd/mm/yyyy.
Doing it exactly as you describe works correctly for us, UK users set the date in the format that is consistent with their regional settings, US as mm/dd/yyyy, and both return the same results.

I will say that we have seen at odd times that if you pick too fast from the calendar control it doesn't seem to quite register (even though the correct date shows) and I've gotten that error. Don't know if that helps much.

What version number are you at?

Friday, March 23, 2012

Monthly rollups, tricky SQL question?

I'm trying to build a report that shows any user's purchases per month, alon
g
with the resulting balance. That is proving tricky, and to add to the
confusion I have to add rows even where there was no purchases/sales, as lon
g
as the remaining balance was not zero.
Doing the monthly rollup is easy enough with a group by on MONTH(trandate).
Adding rows for "empty" months was also fairly easy, I did a outer join on a
table of dates (is there an easier way to do this?).
What's got me stumped is the "everything up to now", and I think I might be
doing it entirely the wrong way. Normally I would make two subqueries, A and
B, which are the month-by-month rollups. I then have a WHERE that joins them
B.trandate <= A.trandate. But since the data has been grouped by month, the
actual date is no longer available (it's been "grouped out" of the results).
So I'm trying to come up with a way to compare these.
1) MONTH(B.date) < MONTH(A.data) AND YEAR(B.date) < YEAR(A.data) does not
work, because if you have something like 6/2005 it will consider that to fai
l
against the date 7/2004, because 7 > 6. Is there a way to fix this?
2) I tried making up a "fake date" with YEAR(date) + MONTH(date) and
comparing those, but SQL Server uses months with no leading zero, so you end
up with 200412 being smaller than 20043, and if you cast them to numbers the
n
200412 is bigger than 20053. Is there some way to make this work?!
3) maybe I'm doing it all wrong! Is there some way I could get the raw data
without grouping, join on the original dates (I have a dbo.FirstDayOfMonth
that would help here) and then do the grouping? It seems this should work,
but when I try it it seems to be difficult to fold the outer join back in, i
t
needs to refer to data in the A subquery, and if there is a way to do this I
can't figure out the syntax (at least not in an outer join, maybe a WHERE is
the way to go?)
MauryMaury Markowitz wrote:
> I'm trying to build a report that shows any user's purchases per month, al
ong
> with the resulting balance. That is proving tricky, and to add to the
> confusion I have to add rows even where there was no purchases/sales, as l
ong
> as the remaining balance was not zero.
> Doing the monthly rollup is easy enough with a group by on MONTH(trandate)
.
> Adding rows for "empty" months was also fairly easy, I did a outer join on
a
> table of dates (is there an easier way to do this?).
> What's got me stumped is the "everything up to now", and I think I might b
e
> doing it entirely the wrong way. Normally I would make two subqueries, A a
nd
> B, which are the month-by-month rollups. I then have a WHERE that joins th
em
> B.trandate <= A.trandate. But since the data has been grouped by month, th
e
> actual date is no longer available (it's been "grouped out" of the results
).
> So I'm trying to come up with a way to compare these.
> 1) MONTH(B.date) < MONTH(A.data) AND YEAR(B.date) < YEAR(A.data) does not
> work, because if you have something like 6/2005 it will consider that to f
ail
> against the date 7/2004, because 7 > 6. Is there a way to fix this?
> 2) I tried making up a "fake date" with YEAR(date) + MONTH(date) and
> comparing those, but SQL Server uses months with no leading zero, so you e
nd
> up with 200412 being smaller than 20043, and if you cast them to numbers t
hen
> 200412 is bigger than 20053. Is there some way to make this work?!
> 3) maybe I'm doing it all wrong! Is there some way I could get the raw dat
a
> without grouping, join on the original dates (I have a dbo.FirstDayOfMonth
> that would help here) and then do the grouping? It seems this should work,
> but when I try it it seems to be difficult to fold the outer join back in,
it
> needs to refer to data in the A subquery, and if there is a way to do this
I
> can't figure out the syntax (at least not in an outer join, maybe a WHERE
is
> the way to go?)
> Maury
Take a look at CUBE / ROLLUP in Books Online.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On Tue, 5 Sep 2006 12:31:02 -0700, Maury Markowitz wrote:
(snip)
>What's got me stumped is the "everything up to now", and I think I might be
>doing it entirely the wrong way. Normally I would make two subqueries, A an
d
>B, which are the month-by-month rollups. I then have a WHERE that joins the
m
>B.trandate <= A.trandate. But since the data has been grouped by month, the
>actual date is no longer available (it's been "grouped out" of the results)
.
>So I'm trying to come up with a way to compare these.
>1) MONTH(B.date) < MONTH(A.data) AND YEAR(B.date) < YEAR(A.data) does not
>work, because if you have something like 6/2005 it will consider that to fa
il
>against the date 7/2004, because 7 > 6. Is there a way to fix this?
>2) I tried making up a "fake date" with YEAR(date) + MONTH(date) and
>comparing those, but SQL Server uses months with no leading zero, so you en
d
>up with 200412 being smaller than 20043, and if you cast them to numbers th
en
>200412 is bigger than 20053. Is there some way to make this work?!
Hi Maury,
I suppose your current queries do the grouping by month with something
like this:
GROUP BY YEAR(TheDate), MONTH(TheDate)
Right?
If you change it to
GROUP BY DATEDIFF(month, '19000101', TheDate)
you can easily compare dates
WHERE DATEDIFF(month, '19000101', Date1) >
DATEDIFF(month, '19000101', Date2)
or get back to datetime format (at first day of the month):
SELECT DATEADD(month, DATEDIFF(month, '19000101', Date1), '19000101')
Hugo Kornelis, SQL Server MVP|||"Hugo Kornelis" wrote:

> If you change it to
> GROUP BY DATEDIFF(month, '19000101', TheDate)
> you can easily compare dates
> WHERE DATEDIFF(month, '19000101', Date1) >
> DATEDIFF(month, '19000101', Date2)
> or get back to datetime format (at first day of the month):
> SELECT DATEADD(month, DATEDIFF(month, '19000101', Date1), '19000101')
Thanks, I'll try that!
Maurysql

Monthly rollups, tricky SQL question?

I'm trying to build a report that shows any user's purchases per month, along
with the resulting balance. That is proving tricky, and to add to the
confusion I have to add rows even where there was no purchases/sales, as long
as the remaining balance was not zero.
Doing the monthly rollup is easy enough with a group by on MONTH(trandate).
Adding rows for "empty" months was also fairly easy, I did a outer join on a
table of dates (is there an easier way to do this?).
What's got me stumped is the "everything up to now", and I think I might be
doing it entirely the wrong way. Normally I would make two subqueries, A and
B, which are the month-by-month rollups. I then have a WHERE that joins them
B.trandate <= A.trandate. But since the data has been grouped by month, the
actual date is no longer available (it's been "grouped out" of the results).
So I'm trying to come up with a way to compare these.
1) MONTH(B.date) < MONTH(A.data) AND YEAR(B.date) < YEAR(A.data) does not
work, because if you have something like 6/2005 it will consider that to fail
against the date 7/2004, because 7 > 6. Is there a way to fix this?
2) I tried making up a "fake date" with YEAR(date) + MONTH(date) and
comparing those, but SQL Server uses months with no leading zero, so you end
up with 200412 being smaller than 20043, and if you cast them to numbers then
200412 is bigger than 20053. Is there some way to make this work?!
3) maybe I'm doing it all wrong! Is there some way I could get the raw data
without grouping, join on the original dates (I have a dbo.FirstDayOfMonth
that would help here) and then do the grouping? It seems this should work,
but when I try it it seems to be difficult to fold the outer join back in, it
needs to refer to data in the A subquery, and if there is a way to do this I
can't figure out the syntax (at least not in an outer join, maybe a WHERE is
the way to go?)
MauryMaury Markowitz wrote:
> I'm trying to build a report that shows any user's purchases per month, along
> with the resulting balance. That is proving tricky, and to add to the
> confusion I have to add rows even where there was no purchases/sales, as long
> as the remaining balance was not zero.
> Doing the monthly rollup is easy enough with a group by on MONTH(trandate).
> Adding rows for "empty" months was also fairly easy, I did a outer join on a
> table of dates (is there an easier way to do this?).
> What's got me stumped is the "everything up to now", and I think I might be
> doing it entirely the wrong way. Normally I would make two subqueries, A and
> B, which are the month-by-month rollups. I then have a WHERE that joins them
> B.trandate <= A.trandate. But since the data has been grouped by month, the
> actual date is no longer available (it's been "grouped out" of the results).
> So I'm trying to come up with a way to compare these.
> 1) MONTH(B.date) < MONTH(A.data) AND YEAR(B.date) < YEAR(A.data) does not
> work, because if you have something like 6/2005 it will consider that to fail
> against the date 7/2004, because 7 > 6. Is there a way to fix this?
> 2) I tried making up a "fake date" with YEAR(date) + MONTH(date) and
> comparing those, but SQL Server uses months with no leading zero, so you end
> up with 200412 being smaller than 20043, and if you cast them to numbers then
> 200412 is bigger than 20053. Is there some way to make this work?!
> 3) maybe I'm doing it all wrong! Is there some way I could get the raw data
> without grouping, join on the original dates (I have a dbo.FirstDayOfMonth
> that would help here) and then do the grouping? It seems this should work,
> but when I try it it seems to be difficult to fold the outer join back in, it
> needs to refer to data in the A subquery, and if there is a way to do this I
> can't figure out the syntax (at least not in an outer join, maybe a WHERE is
> the way to go?)
> Maury
Take a look at CUBE / ROLLUP in Books Online.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On Tue, 5 Sep 2006 12:31:02 -0700, Maury Markowitz wrote:
(snip)
>What's got me stumped is the "everything up to now", and I think I might be
>doing it entirely the wrong way. Normally I would make two subqueries, A and
>B, which are the month-by-month rollups. I then have a WHERE that joins them
>B.trandate <= A.trandate. But since the data has been grouped by month, the
>actual date is no longer available (it's been "grouped out" of the results).
>So I'm trying to come up with a way to compare these.
>1) MONTH(B.date) < MONTH(A.data) AND YEAR(B.date) < YEAR(A.data) does not
>work, because if you have something like 6/2005 it will consider that to fail
>against the date 7/2004, because 7 > 6. Is there a way to fix this?
>2) I tried making up a "fake date" with YEAR(date) + MONTH(date) and
>comparing those, but SQL Server uses months with no leading zero, so you end
>up with 200412 being smaller than 20043, and if you cast them to numbers then
>200412 is bigger than 20053. Is there some way to make this work?!
Hi Maury,
I suppose your current queries do the grouping by month with something
like this:
GROUP BY YEAR(TheDate), MONTH(TheDate)
Right?
If you change it to
GROUP BY DATEDIFF(month, '19000101', TheDate)
you can easily compare dates
WHERE DATEDIFF(month, '19000101', Date1) >
DATEDIFF(month, '19000101', Date2)
or get back to datetime format (at first day of the month):
SELECT DATEADD(month, DATEDIFF(month, '19000101', Date1), '19000101')
--
Hugo Kornelis, SQL Server MVP|||"Hugo Kornelis" wrote:
> If you change it to
> GROUP BY DATEDIFF(month, '19000101', TheDate)
> you can easily compare dates
> WHERE DATEDIFF(month, '19000101', Date1) >
> DATEDIFF(month, '19000101', Date2)
> or get back to datetime format (at first day of the month):
> SELECT DATEADD(month, DATEDIFF(month, '19000101', Date1), '19000101')
Thanks, I'll try that!
Maury

Monsterlog file - short term solution?

Les R
Sounds like you have your recovery plan set to FULL. If you
truly do not need the contents of the log file then you can
truncate it
backup log <your db> with truncate_only
go
This will delete the inactive portion of the log
You can then issue a DBCC SHRINKFILE command
to reclaim disk space the log may have requested from
the operating system in the course of it's growth:
use <your db>
go
dbcc shrinkfile(<log name>, <size>)
go
Make sure you allocate enough initial space to the log
doesn't have to keep autogrowing (assuming it's set
to autogrow).
If you're using the FULL recovery model, also set it
to SIMPLE.
Before you do anything, make sure that you in fact
do not need the log info. And read up on the
backup log and dbcc shrinkfile before you run
them so you understand them.
"Les Russell" <LesRussell@.discussions.microsoft.com> wrote in message
news:453CEC6E-4E5F-4FDB-BE4E-20AE8CB91C16@.microsoft.com...
> Hi,
> Ive inherited admin of a database of which I was a user, so I'm new to
this.
> The log file has grown to an enormous size. This has happened in the past
> (only a couple of times in a year) and the db admin has fixed it. From
> reading other posts I think I will set the recovery to SIMPLE (it is a
data
> warehouse). (However whatever scheme was in put in place by the admin
> normally worked OK, even with recovery set to FULL, so I am not sure what
has
> changed.)
> So, I still have the problem right now of the huge log file and a database
> that will not respond. Can I fix the problem without backing up the log
file
> (to be honest the log file is of no use)? There is not enough space on
disk
> media to backup the log file. Is there a way of "fooling" SQL Server into
> thinking it has been backed up?
> Any help appreciated. And yes I will read up on BOL

> Les R

Monsterlog file - short term solution?

Les RSounds like you have your recovery plan set to FULL. If you
truly do not need the contents of the log file then you can
truncate it
backup log <your db> with truncate_only
go
This will delete the inactive portion of the log
You can then issue a DBCC SHRINKFILE command
to reclaim disk space the log may have requested from
the operating system in the course of it's growth:
use <your db>
go
dbcc shrinkfile(<log name>, <size> )
go
Make sure you allocate enough initial space to the log
doesn't have to keep autogrowing (assuming it's set
to autogrow).
If you're using the FULL recovery model, also set it
to SIMPLE.
Before you do anything, make sure that you in fact
do not need the log info. And read up on the
backup log and dbcc shrinkfile before you run
them so you understand them.
"Les Russell" <LesRussell@.discussions.microsoft.com> wrote in message
news:453CEC6E-4E5F-4FDB-BE4E-20AE8CB91C16@.microsoft.com...
> Hi,
> Ive inherited admin of a database of which I was a user, so I'm new to
this.
> The log file has grown to an enormous size. This has happened in the past
> (only a couple of times in a year) and the db admin has fixed it. From
> reading other posts I think I will set the recovery to SIMPLE (it is a
data
> warehouse). (However whatever scheme was in put in place by the admin
> normally worked OK, even with recovery set to FULL, so I am not sure what
has
> changed.)
> So, I still have the problem right now of the huge log file and a database
> that will not respond. Can I fix the problem without backing up the log
file
> (to be honest the log file is of no use)? There is not enough space on
disk
> media to backup the log file. Is there a way of "fooling" SQL Server into
> thinking it has been backed up?
> Any help appreciated. And yes I will read up on BOL

> Les Rsql

Monsterlog file - short term solution?

Hi,
Ive inherited admin of a database of which I was a user, so I'm new to this.
The log file has grown to an enormous size. This has happened in the past
(only a couple of times in a year) and the db admin has fixed it. From
reading other posts I think I will set the recovery to SIMPLE (it is a data
warehouse). (However whatever scheme was in put in place by the admin
normally worked OK, even with recovery set to FULL, so I am not sure what has
changed.)
So, I still have the problem right now of the huge log file and a database
that will not respond. Can I fix the problem without backing up the log file
(to be honest the log file is of no use)? There is not enough space on disk
media to backup the log file. Is there a way of "fooling" SQL Server into
thinking it has been backed up?
Any help appreciated. And yes I will read up on BOL :)
Les RSounds like you have your recovery plan set to FULL. If you
truly do not need the contents of the log file then you can
truncate it
backup log <your db> with truncate_only
go
This will delete the inactive portion of the log
You can then issue a DBCC SHRINKFILE command
to reclaim disk space the log may have requested from
the operating system in the course of it's growth:
use <your db>
go
dbcc shrinkfile(<log name>, <size>)
go
Make sure you allocate enough initial space to the log
doesn't have to keep autogrowing (assuming it's set
to autogrow).
If you're using the FULL recovery model, also set it
to SIMPLE.
Before you do anything, make sure that you in fact
do not need the log info. And read up on the
backup log and dbcc shrinkfile before you run
them so you understand them.
"Les Russell" <LesRussell@.discussions.microsoft.com> wrote in message
news:453CEC6E-4E5F-4FDB-BE4E-20AE8CB91C16@.microsoft.com...
> Hi,
> Ive inherited admin of a database of which I was a user, so I'm new to
this.
> The log file has grown to an enormous size. This has happened in the past
> (only a couple of times in a year) and the db admin has fixed it. From
> reading other posts I think I will set the recovery to SIMPLE (it is a
data
> warehouse). (However whatever scheme was in put in place by the admin
> normally worked OK, even with recovery set to FULL, so I am not sure what
has
> changed.)
> So, I still have the problem right now of the huge log file and a database
> that will not respond. Can I fix the problem without backing up the log
file
> (to be honest the log file is of no use)? There is not enough space on
disk
> media to backup the log file. Is there a way of "fooling" SQL Server into
> thinking it has been backed up?
> Any help appreciated. And yes I will read up on BOL :)
> Les R

Friday, March 9, 2012

Monitor user changes

I need to monitor sql user additions and changes and put login name and
change date into a user created table.
Is ther anyway I can do this from a trigger or sp.
Regards
Don GroverYou can use a trigger on the tables you want to audit. Just
look at the values in the deleted and inserted tables used
by the triggers for before and after images of the rows. You
can capture the date with GetDate() and the user with
suser_sname. And then just insert that data into your audit
table.
You can find an example of this in the following article:
http://www.aspfaq.com/show.asp?id=2448
-Sue
On Thu, 10 Feb 2005 07:53:50 +1100, "Don Grover"
<dgrover@.assoft.com.au> wrote:

>I need to monitor sql user additions and changes and put login name and
>change date into a user created table.
>Is ther anyway I can do this from a trigger or sp.
>Regards
>Don Grover
>|||Sorry Sue
I actually mean't users logins, not user data
ie: I actually want to monitor if any more users have been add, deleted or
edited on/to any database.
I have this msql server that I need to monitor remotely by script/email when
any user/login changes are made
Regards
Don
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:krkl01l9jsi45m3ji0bn25ufpbr358j6sb@.
4ax.com...
> You can use a trigger on the tables you want to audit. Just
> look at the values in the deleted and inserted tables used
> by the triggers for before and after images of the rows. You
> can capture the date with GetDate() and the user with
> suser_sname. And then just insert that data into your audit
> table.
> You can find an example of this in the following article:
> http://www.aspfaq.com/show.asp?id=2448
> -Sue
> On Thu, 10 Feb 2005 07:53:50 +1100, "Don Grover"
> <dgrover@.assoft.com.au> wrote:
>
>|||Don
SQL Server does not have a track of this.
Look at SQL Server Profiler to do it for you.
"Don Grover" <dgrover@.assoft.com.au> wrote in message
news:%23KamiQzDFHA.2608@.TK2MSFTNGP10.phx.gbl...
> Sorry Sue
> I actually mean't users logins, not user data
> ie: I actually want to monitor if any more users have been add, deleted or
> edited on/to any database.
> I have this msql server that I need to monitor remotely by script/email
when
> any user/login changes are made
> Regards
> Don
>
>
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:krkl01l9jsi45m3ji0bn25ufpbr358j6sb@.
4ax.com...
>|||Look at the Security Audit Events classes in profiler. You
can capture these changes using Profiler (or a server side
trace) using the Security Audit event category.
-Sue
On Thu, 10 Feb 2005 16:48:21 +1100, "Don Grover"
<dgrover@.assoft.com.au> wrote:

>Sorry Sue
>I actually mean't users logins, not user data
>ie: I actually want to monitor if any more users have been add, deleted or
>edited on/to any database.
>I have this msql server that I need to monitor remotely by script/email whe
n
>any user/login changes are made
>Regards
>Don
>
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:krkl01l9jsi45m3ji0bn25ufpbr358j6sb@.
4ax.com...
>

monitor user activity and bandwidth

Hi,
I am running perfomance monitor and I noticed that my network bandwidth is
off the chart.
How can I write a SQL query to find out who and which database is using all
the bandwidth on the SQL Server?
Thanks in advance.You may want to start here;
821914 HOW TO: Troubleshoot Application Performance with SQL Server 2000
http://support.microsoft.com/?id=821914
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

monitor user activity and bandwidth

Hi,
I am running perfomance monitor and I noticed that my network bandwidth is
off the chart.
How can I write a SQL query to find out who and which database is using all
the bandwidth on the SQL Server?
Thanks in advance.This is sql2000 correct?
You could run a sql profiler trace with maybe Audit Login and logout
settings chosen, along with maybe the RPC events. But again, the profiler
will be running and consuming disk space so you'll have to keep recycling
it or run it only during the time you need it.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Monday, February 20, 2012

Money in SQL and ASP.NET

Hi,
I'm having some trouble with my asp.net page and my sql database. What I'm trying to do is allow the user to upload an number to the database, the number is a money amount like 2.00 (￡2.00) or 20.00(￡20.00). I've tried using money and smallmoney datatypes but the numbers usually end up looking like this in the database...
I enter 2.00 and in the database it looks like 2.00000, and even if I enter the information directly into the database I get the same results. I'm not going to be using big numbers with lots of decimal places like this 1000,000,0000. Can anyone help me? All I want is to know what to set the value to on my aspx page and what setting to set the field to in my database, I'd just like two pound to appear as 2.00. any help would be great.

I'm using Microsoft Visual Web Developer 2005 Express Edition and Microsoft SQL Server 2005 if that helps.

Thanks.

Hi,

The money or smallmoney datatypes are the right ones to use in your case. By design, they have four decimal at the end and you can find relevent infromation related to these two from Books Online. As to your question, you use either of these types in your database to hold your data and when it is time to show your data on your asp.net page, you can format them into what you need. For example, {0,c2} will give you two decimals as you want. In GridView:

<asp:BoundField HeaderText="Price/Unit" DataField="UnitPrice" DataFormatString="{0:c2}"HtmlEncode="false"> </asp:BoundField>

<asp:TemplateFieldHeaderText="UnitPrice"><ItemTemplate><asp:LabelID="Label2"runat="server"Text='<%# Eval("UnitPrice","{0:c2}") %>'></asp:Label></ItemTemplate></asp:TemplateField>|||

Hi,

You don't say what type of controls you're trying to display this in. However, one simple way (that I think you can apply to any text box or label control) is this:

TextBox1.Text = (512.23).ToString("c")

If you need to set the page to a different currency than your own, one way is to set the UICulture of the page directive:

<%@. Page Language="VB" AutoEventWireup="false" UICulture="en-GB" CodeFile="Default4.aspx.vb" Inherits="Default4" %>

You can also set this in the web.config file (under globalization)

How the numbers are stored in SQL aren't relevant to how they'll be displayed - as you've discovered!

Hope this helps.

Paul

MOM Alert when login failed for user 'sa'

I am trying to setup a MOM Alert when the SA account fails to login to SQL,
How can this be setup so SQL reports this into the windows event viewer and
not its own log?
Many thanks for any help,
AlastairIt does get logged in the application event log whenever login failure
happens for any account. (Standard install)
However, you can create a rule in MOM to capture error id:18456 with error
desc like "Login failed for user 'sa'".
--
Thank you,
Saleem Hakani
HTTP://WWW.SQLCOMMUNITY.COM (SQL Server Community)
SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
Articles, SQL Clinic and a lot of SQL fun.
"alastairn" wrote:
> I am trying to setup a MOM Alert when the SA account fails to login to SQL,
> How can this be setup so SQL reports this into the windows event viewer and
> not its own log?
> Many thanks for any help,
> Alastair
>|||Thanks for the fast response and help :)
"Saleem Hakani" wrote:
> It does get logged in the application event log whenever login failure
> happens for any account. (Standard install)
> However, you can create a rule in MOM to capture error id:18456 with error
> desc like "Login failed for user 'sa'".
> --
> Thank you,
> Saleem Hakani
> HTTP://WWW.SQLCOMMUNITY.COM (SQL Server Community)
> SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
> Articles, SQL Clinic and a lot of SQL fun.
>
> "alastairn" wrote:
> > I am trying to setup a MOM Alert when the SA account fails to login to SQL,
> > How can this be setup so SQL reports this into the windows event viewer and
> > not its own log?
> >
> > Many thanks for any help,
> > Alastair
> >

Friday, March 30, 2012

More on that login problem - found the logs -

More on that login problem - found the logs -

More on restoring database in user instance - exclusive use not obtained

More on restoring database in user instance - exclusive use not obtained

Wednesday, March 28, 2012

More logical drives VS more striped drives

More logical drives VS more striped drives

Monday, March 26, 2012

more date problems...

More bugs in SSRS 2k5 ....

More bugs in SSRS 2k5 ....

Friday, March 23, 2012

Monthly rollups, tricky SQL question?

Monthly rollups, tricky SQL question?

Monsterlog file - short term solution?

Monsterlog file - short term solution?

Monsterlog file - short term solution?

Friday, March 9, 2012

Monitor user changes

monitor user activity and bandwidth

monitor user activity and bandwidth

Monday, February 20, 2012

Money in SQL and ASP.NET

MOM Alert when login failed for user 'sa'

Money Data Type

Blog Archive

About Me