Failed Login for a WorkGroup when attempting to run a query

Hi all,
I have an application which works with SQL Server. When the applicationattempts to load a page it needs to run certain queries. When itattempts to run a particular query it fails but I catch the exceptionand then log it. The following is logged:
System.Data.Ole.Db.OleDbException: Login failed for user 'CWAMB01DWH01\ASPENT'.
Now what I don't understand is that this is the work group that ASPNETis run under, and I am running other queries to the database via SQLServer authentication. Why am I getting a failed login for thisworkgroup? Do I need to create a new Login for the Server in SQLServer, and then create a new user for the database with the sameusername as the Workgroup name? If so, then how does the password workfor the SQL Server as the workgroup (CWAMB01DWH01\ASPENT) obviouslydoesn't have a password.
Thanks, and I hope I have explain my problem clear enough.
Tryst
There are two permissions in SQL Server database permissions in the database and server permissions you create under security in Enterprise Manager. Hope this helps.

Fact table changes

Our data mart is medical based type of subjects.
Recently; the government changed how some "hours" of service could be
provided to certain people. The "hours" value is stored in a Fact table.
So for example; Client XYZ used to have 50 hours; it may have 63 now.
So I see how easy it would have been to make these data changes if it had
been in a Dimension - would have been type 2 SCD - but not sure what best
approach is for changes in a Fact table. I'm almost tempted to have a
status column to identify which Fact record is current; but then I would
have much work to do in SSAS to change how the Cubes are put together.
What is the best practice when you have to make data changes to existing
Fact records in a dm/dw?I'd recommend avoiding changes to historic data in a DW unless they were
genuinely erroneous.
Is there a date dimension relationship in your fact table? If so, what if a
user was to pull up a report from the past where the 50 hours was correct
but your warehouse reported 63? Would this be incorrect? If there are no
dates one option as you suggest would be a flag. Then you could construct a
view selecting the same columns as before but only including current rows.
The only change you'd need to make in SSAS would be in the Data Source
View - to change from the table to the view. The disadvantage of this would
be that end-users couldn't report on the previous 'hours' value. This gets
worse if the value changes again.
A better option ('best practice' perhaps) would be to introduce a start and
end date key (related to your date dimension obviously) over which period
the value was current - perhaps seperate to any existing dates. This way end
users could query for the different hours value based on when it was
relevant. A new value generates a new row with new dates. If necessary you
could still construct a view for the most recent value - but you would still
have all of the data in the database to support more sophisticated analysis.
There's more work here clearly - only you can decide whether its worth it.
HTH
--
Phil
http://www.clarity-integration.com
http://www.phil-austin.blogspot.com
"Joe" <hortoristic@.gmail.dot.com> wrote in message
news:0CA262DF-F121-496C-878E-0CDEC387D159@.microsoft.com...
> Our data mart is medical based type of subjects.
> Recently; the government changed how some "hours" of service could be
> provided to certain people. The "hours" value is stored in a Fact table.
> So for example; Client XYZ used to have 50 hours; it may have 63 now.
> So I see how easy it would have been to make these data changes if it had
> been in a Dimension - would have been type 2 SCD - but not sure what best
> approach is for changes in a Fact table. I'm almost tempted to have a
> status column to identify which Fact record is current; but then I would
> have much work to do in SSAS to change how the Cubes are put together.
> What is the best practice when you have to make data changes to existing
> Fact records in a dm/dw?

Extremly bad performance Stored Procedures

The last few days my sqlserver executes stored procedures badly.
When I run the Query Analyzer to execute a certain stored procedure it takes more than 2 minutes to execute the procedure. When I copy the contents of de SP to Query analyser to run it as an sql statement it find's the results within a second.
This behavior dissapear's after a while and comes back randomly.
I had the problem Friday afternoon then tuesday and now again.
Between these day's my sqlserver works fine.
Can anybody please help me with this problem.Have you tried using the "with recompile" option ? Your query plan is probably based on an outdated data distribution or schema. Running the "with recompile" option will regenerate the query plan. Also, are you parameters to the stored procedure vary enough that the execution plans change ? Do a comparison in query analyzer - using show execution plan.|||I was able to elimante the problem by altering de SP.
In the SP there where more than 4 joins to the same table.
When I made a user defined function and replaced those joins with this function, it all works fine.

But one question remains. How is it possible that the query analyser didn't have problems with the joins but de SP did have?|||Did you try the recompile ? Sometimes, if your table(s) involved in the query change enough - the query plan needs to change as well. When you run it in query analyzer, the query plan is generated dynamically. For the sp, it could still be using the original query plan when you created it. That is why I suggested to run the 2 in query analyzer with the "show execution plan".

Extremely High Lock Timeouts

I have recently been investigating some server issues and
found that at certain times of the day we our
experiencing lock timeouts in 10,000 per second. Our
lock timeout value is set to -1, which to me means that
the locks should never timeout.
We are not experiencing an deadlocks during these times
and all lock timeouts are key locks.
Can anyone clear this up for me.There are lots of internal synchronization protocols which request locks
with a 0 length timeout. These are refered to as "NoWait" locks. That is, if
SQL Server can get the lock now, great, otherwise if it can't get the lock
it'll do something different. Unfortunately, these show up in profiler as
lock timeouts when they're really just part of the normal internal locking
protocols. (SQL Server has no way of knowing at the profiler level).
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Jeremy" <anonymous@.discussions.microsoft.com> wrote in message
news:1ce5d01c4532b$c5ea1cd0$a101280a@.phx
.gbl...
> I have recently been investigating some server issues and
> found that at certain times of the day we our
> experiencing lock timeouts in 10,000 per second. Our
> lock timeout value is set to -1, which to me means that
> the locks should never timeout.
> We are not experiencing an deadlocks during these times
> and all lock timeouts are key locks.
> Can anyone clear this up for me.

Extremely High Lock Timeouts

I have recently been investigating some server issues and
found that at certain times of the day we our
experiencing lock timeouts in 10,000 per second. Our
lock timeout value is set to -1, which to me means that
the locks should never timeout.
We are not experiencing an deadlocks during these times
and all lock timeouts are key locks.
Can anyone clear this up for me.There are lots of internal synchronization protocols which request locks
with a 0 length timeout. These are refered to as "NoWait" locks. That is, if
SQL Server can get the lock now, great, otherwise if it can't get the lock
it'll do something different. Unfortunately, these show up in profiler as
lock timeouts when they're really just part of the normal internal locking
protocols. (SQL Server has no way of knowing at the profiler level).
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Jeremy" <anonymous@.discussions.microsoft.com> wrote in message
news:1ce5d01c4532b$c5ea1cd0$a101280a@.phx.gbl...
> I have recently been investigating some server issues and
> found that at certain times of the day we our
> experiencing lock timeouts in 10,000 per second. Our
> lock timeout value is set to -1, which to me means that
> the locks should never timeout.
> We are not experiencing an deadlocks during these times
> and all lock timeouts are key locks.
> Can anyone clear this up for me.

Extremely High Lock Timeouts

I have recently been investigating some server issues and
found that at certain times of the day we our
experiencing lock timeouts in 10,000 per second. Our
lock timeout value is set to -1, which to me means that
the locks should never timeout.
We are not experiencing an deadlocks during these times
and all lock timeouts are key locks.
Can anyone clear this up for me.
There are lots of internal synchronization protocols which request locks
with a 0 length timeout. These are refered to as "NoWait" locks. That is, if
SQL Server can get the lock now, great, otherwise if it can't get the lock
it'll do something different. Unfortunately, these show up in profiler as
lock timeouts when they're really just part of the normal internal locking
protocols. (SQL Server has no way of knowing at the profiler level).
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Jeremy" <anonymous@.discussions.microsoft.com> wrote in message
news:1ce5d01c4532b$c5ea1cd0$a101280a@.phx.gbl...
> I have recently been investigating some server issues and
> found that at certain times of the day we our
> experiencing lock timeouts in 10,000 per second. Our
> lock timeout value is set to -1, which to me means that
> the locks should never timeout.
> We are not experiencing an deadlocks during these times
> and all lock timeouts are key locks.
> Can anyone clear this up for me.

Extracting from one database to another automatically hourly

For a GPS utility project we are planning on extracting certain attributes from a huge "GPS Raw Data" read only database which we have access to containing GPS data from several years from several devices attached to vehicles.

The data is time stamped. Where the time gap between pieces of data is more than 10 minutes, a new trip is instance is assumed and in our write access "Trip" database we create a new instance for the data clump with a newtrip idalong with the time range of the data.

The process is to be run hourly to update the "Trip" database with new trips and append to overlapping trips.

We've some questions:

a) Is it easy to read from one database and write into another in c# hourly

b) How would one go about running a C# program automatically every hour on the server?

c) Is there a better way to do this than an hourly update? (dynamically perhaps??)

d) When querying the database and comparing the time stamps, how for instance would we go about identifying a 10 minute gap when the time/date is in the format "22/12/2007 11:25:00". I can't get my head around actually writing this - it's probably ridiculously simple

what type of database r u using ?

If its SQL, why not create a storedprocedure and make it to run in a job every hour, it will best for performance .

|||

Perhaps you can look into solutions like Replication where your GPS Raw Data can be the publisher and your target db can be the subscriber. Any transactions on publisher are replicated on to the subscriber. and the subscriber can be used for querying.

>>a) Is it easy to read from one database and write into another in c# hourly

Depends on how much data there is to read/write.

b) How would one go about running a C# program automatically every hour on the server?

>>You can write a stored procedure that has the logic to query for modified/new records and does an INSERT into the target db.

>>c) Is there a better way to do this than an hourly update? (dynamically perhaps??)

Perhaps. Look into the replication technology and see if it suits your requirements.

>>d) When querying the database and comparing the time stamps, how for instance would we go about identifying a 10 minute gap when the time/date is in the format "22/12/2007 11:25:00". I can't get my head around actually writing this - it's probably ridiculously simple

You can use the DATEDIFF function : IF DATEDIFF(mi, <valueinrow>, Getdate()) = 10. But you'd have to have some kind of service running constantly to trigger this data transfer when the 10 minute interval happens.

|||

Thank you for your help Ramzi!
We are using MS SQL 2005 with Microsoft Visual Web Dev 2005.

I must read into stored procedures if you think this is the best way forward as a solution. Is there a particular utility you would advise for creating/managing stored procedures?

I just downloaded MS SQL Management Studio. However, I cannot find the ASPNETDB.MDF database created in Visual Web Developer on the local machine when I look for it.

|||

Thanks ndinakar

Perhaps you can look into solutions like Replication where your GPS Raw Data can be the publisher and your target db can be the subscriber. Any transactions on publisher are replicated on to the subscriber. and the subscriber can be used for querying.

>>a) Is it easy to read from one database and write into another in c# hourly

Depends on how much data there is to read/write.

Once the main "lot" from the past year is indexed, then each hourly update would be rather small with the only perhaps a hundred co-ordinates coming in over an hour. At times there will be no new data.

b) How would one go about running a C# program automatically every hour on the server?

>>You can write a stored procedure that has the logic to query for modified/new records and does an INSERT into the target db.

We will look into the stored procedure function. This looks like it could be very useful.. Would this be used alongside or instead of replication? (bear in mind I've not yet read up on replication)

>>d) When querying the database and comparing the time stamps, how for instance would we go about identifying a 10 minute gap when the time/date is in the format "22/12/2007 11:25:00". I can't get my head around actually writing this - it's probably ridiculously simple

You can use the DATEDIFF function : IF DATEDIFF(mi, <valueinrow>, Getdate()) = 10. But you'd have to have some kind of service running constantly to trigger this data transfer when the 10 minute interval happens.

In the stored procedure that runs hourly could this be used to run through the data from the past hour and identify these if/when these intervals happen or are you saying that it needs to be constantly checking (every few seconds)?

|||

I'm looking for info on stored procedures now.
On the MS website there is mention of a "CREATE TRIGGER" procedure... Is this what you refer to as something we could use with the DATEDIFF comparison?

Is it possible to set a timed execution? Could someone give a small example of this?

All of your help has been much appreciated!

|||

Going by your replies perhaps its best to create a job, have the job call a stored procedure. You can schedule the job to run every 10 minutes and in your stored procedure, query all the new/modified records after the previous run and migrate the data. You might need to store the timestamp of the last run in case the job does not run for any reason or you need to stop the job for a few hours. You can always catch up if you have the timestamp of the previous run.

Check out Books On Line (the documentation that comes with SQL Server) for CREATE PROCEDURE.

For creating/scheduling/managing jobs look for "Jobs" under SQL Server Agent.

|||

I have managed to do some basic copying from one database to another, however, I'm having some problems with the datediff function.

We are selecting the time and vehicle ID from the "GPS raw data" database. We want to compare each entry with the previous one and, where there is a gap of more than 15 mins, return it.

We will group the associated data into another "trip" table, with a primary key of "trip id". So any gap in GPS transmission of more than 15 mins will be considered the end/start of a trip.

This is just vague code.
I want to compare each row really.. I've no idea how to though..(I've put in time & time+1 where time+1 is the next row - Don't know how to do this)

SELECT Time, deviceID,
FROM [gps.db]
WHERE datediff(n, time, time+1) > 15