DB accessing problems after moving the DB

Hi guys
I just moved my system from a workstation to a notebook. So far so good. But that's the beginning of my trouble. I backed up all databases I need for my developing work installed the SQL Server (Dev Edition) on the notebook an restored the databases. Following to that I enabled the Shared Memory, the TCP/IP and the Named Pipes for the Instance. When I now try to run an ASP.Net Website using one of my databases I'm getting this error message:

Cannot open database "DatabaseName" requested by the login. The login failed. Login failed for user 'DOMAIN\user.name'.

Additionalliy the log (you can find it under SSMS->Management->SQL Server Logs) reports following:
Error: 18456, Severity: 14, State: 16 what means that the incoming user does not have permissions to log into the target database.

I checked this out by logging the user into some other database (master) and then tryed using the USE DATABASE command to switch to the target database to get a better error message:

Msg 911, Level 16, State 1, Server ComputerName, Line 1
Could not locate entry in sysdatabases for database "DatabaseName". No entry found with that name. Make sure that the name is entered correctly.

Do you guys have any idea what I can do?

BTW: The notebook has two NICs. I don't know if has something to do with that.

SOunds kind of like your restore blew up somehow. Try stopping the SQL Server on the workstation, then copy the required MDF / LDF database FILES to the notebook and use the ATTACH method from SQL Management Console. You might also want to consider using SQL Server authentication rather than Windows.

|||

Many thanks! That solved my problem.

Day of the month

I am trying to code a proceedure that will run every weekend. This process will run for a number of hours beginning at 0900 Saturday and ending at 2100 Sunday. However, on the 3rd weekend of the month, I need it run for a shorter time and to also skip some tables. What I have now to find the day of the month I need is

Begin
SET @.3rdSaturday = @.1stDayMonth
/*Loops until the date of the first Saturday(DayofWeek #7)
of the current month is found */
WHILE DATEPART(dw,@.3rdSaturday) <> 7

/*Adds 1 day to the first day of the month until it
reaches the date of the first Friday of the month */
SET @.3rdSaturday = DATEADD(d,1,@.3rdSaturday)
/*Adds 14 days to the first Friday of the month.
The end result is the 3rd Fridays date for the current month*/
SET @.3rdSaturday = DATEADD(d,14,@.3rdSaturday)
End

I am trying to find a better way to find the 3rd Saturday (or similar) of the month. I have also investigated DATENAME function but ran into the same problem. Also if I were using SQLDMOFreq_Monthly I could do it but I am trying to write all of this in T-SQL.

Any and all help is appreciated.

Akinja

Akinja-Earl:

Take a look at this article about establishing a calendar table:

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

Also, are you running on SQL Server 2005 or SQL Server 2000?

Dave

|||Thanks, I will.|||

If you prefer not to use a calendar table you can try something like:

set nocount on

declare @.dateOfMonth datetime
set @.dateOfMonth = '10/13/6'

declare @.monthString varchar (2)
set @.monthString = convert (varchar (2), month (@.dateOfMonth))
declare @.yearString varchar (4)
set @.yearString = convert (varchar (4), year (@.dateOfMonth))

/*
select sampleDate,
datepart (dw, sampleDate) as dayOfWeek,
datename (dw, sampleDate) as nameOfDay
from ( select @.monthString + '/' + convert (char(2), 14 + iter) + '/' + @.yearString as sampleDate
from small_iterator (nolock)
where iter <= 7
) a
where datepart (dw, sampleDate) = 7
*/

-- --
-- create an iterator if you don't already have one.
-- --
declare @.iterator table (iter integer not null)
insert into @.iterator values (1)
insert into @.iterator values (2)
insert into @.iterator values (3)
insert into @.iterator values (4)
insert into @.iterator values (5)
insert into @.iterator values (6)
insert into @.iterator values (7)

select sampleDate,
datepart (dw, sampleDate) as dayOfWeek,
datename (dw, sampleDate) as nameOfDay
from ( select @.monthString + '/' + convert (char(2), 14 + iter) + '/' + @.yearString as sampleDate
from @.iterator
) a
where datepart (dw, sampleDate) = 7

-- -
-- S A M P L E O U T P U T :
-- -

-- sampleDate dayOfWeek nameOfDay
-- - --
-- 10/21/2006 7 Saturday

|||

Thanks you for both suggestions. I like the calendar creation method since I can reuse it for other purposes. The iteration method will work with what I am working now since I am trying to get it done quickly.

Akinja

Datetime comparison problem

Hi,
I am having trouble finding any records with a datetime of 2006-06-16
09:04:39:347. This time clearly falls between the beginning and end times.
I'm even converting to a char to see if that works. It seems to work if I
use .346 but sometimes it requires a difference of .003. Can anyone see what
I might be doing wrong? Thanks Ellie
declare @.dPrevDate datetime
declare @.dNextDate datetime
select @.dPrevDate = '2006-06-16 09:04:39.346'
select @.dNextDate = '2006-06-16 09:05:56.110'
Select *
where (convert(char, tblChanges.dateaction, 21) > convert(char,
@.dPrevDate, 21) and
convert(char, tblChanges.dateaction, 21) < convert(char, @.dNextDate,
21))Sorry I made typos, I should have said that it DIDN'T work for 346 but did
work for 345 (when 346 is clearly less than 347) and the code should be:
>
>
"Ellie" <nospam@.nospam.net> wrote in message
news:O5OF6rulGHA.3816@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I am having trouble finding any records with a datetime of 2006-06-16
> 09:04:39:347. This time clearly falls between the beginning and end times.
> I'm even converting to a char to see if that works. It seems to work if I
> use .346 but sometimes it requires a difference of .003. Can anyone see
> what I might be doing wrong? Thanks Ellie
> declare @.dPrevDate datetime
> declare @.dNextDate datetime
> select @.dPrevDate = '2006-06-16 09:04:39.346'
> select @.dNextDate = '2006-06-16 09:05:56.110'
> Select *
> where (convert(char, tblChanges.dateaction, 21) > convert(char,
> @.dPrevDate, 21) and
> convert(char, tblChanges.dateaction, 21) < convert(char,
> @.dNextDate, 21))
>
>|||Due to a design flaw with Intel based chips, compute clock time is accurate
to the nearest three thousandth of a second.
This makes it difficult to coordinate precise times from a different time so
urce to the times created by a Intel based computer. For example, an automat
ed process control system have be recording times that are .003 seconds 'of
f' what the computer thinks.
You have to build in the .003 'fudge' factor.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Ellie" <nospam@.nospam.net> wrote in message news:O5OF6rulGHA.3816@.TK2MSFTNGP02.phx.gbl...[
color=darkred]
> Hi,
>
> I am having trouble finding any records with a datetime of 2006-06-16
> 09:04:39:347. This time clearly falls between the beginning and end times.
> I'm even converting to a char to see if that works. It seems to work if I
> use .346 but sometimes it requires a difference of .003. Can anyone see wh
at
> I might be doing wrong? Thanks Ellie
>
> declare @.dPrevDate datetime
> declare @.dNextDate datetime
>
> select @.dPrevDate = '2006-06-16 09:04:39.346'
> select @.dNextDate = '2006-06-16 09:05:56.110'
> Select *
> where (convert(char, tblChanges.dateaction, 21) > convert(char,
> @.dPrevDate, 21) and
> convert(char, tblChanges.dateaction, 21) < convert(char, @.dNextDate
,
> 21))
>
>
>
>[/color]|||I was thinking that there must have been a problem there but why can't I
convert it to a string and have it more accurate? That isn't working either.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:umCpU6ulGHA.4144@.TK2MSFTNGP05.phx.gbl...
Due to a design flaw with Intel based chips, compute clock time is accurate
to the nearest three thousandth of a second.
This makes it difficult to coordinate precise times from a different time
source to the times created by a Intel based computer. For example, an
automated process control system have be recording times that are .003
seconds 'off' what the computer thinks.
You have to build in the .003 'fudge' factor.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Ellie" <nospam@.nospam.net> wrote in message
news:O5OF6rulGHA.3816@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I am having trouble finding any records with a datetime of 2006-06-16
> 09:04:39:347. This time clearly falls between the beginning and end times.
> I'm even converting to a char to see if that works. It seems to work if I
> use .346 but sometimes it requires a difference of .003. Can anyone see
> what
> I might be doing wrong? Thanks Ellie
> declare @.dPrevDate datetime
> declare @.dNextDate datetime
> select @.dPrevDate = '2006-06-16 09:04:39.346'
> select @.dNextDate = '2006-06-16 09:05:56.110'
> Select *
> where (convert(char, tblChanges.dateaction, 21) > convert(char,
> @.dPrevDate, 21) and
> convert(char, tblChanges.dateaction, 21) < convert(char,
> @.dNextDate,
> 21))
>
>|||I don't have any clue what "isn't working" means. However, I do have a
suggestion for storing accuracies under 3 ms.
When you enter the data to SQL Server, you can store the date +
minutes/seconds in datetime, then store *your* milliseconds in a separate
INT column. Or to save space you could use smalldatetime for date + minutes
and store seconds*1000+milliseconds in a separate INT column.
Now, when you query, you'll have something more complex to work with, and
that will be the harder part, but your data will be there. You can just
query for the times within your minute, and then where the milliseconds are
in the range you're after.
"Ellie" <nospam@.nospam.net> wrote in message
news:uTH03CvlGHA.884@.TK2MSFTNGP05.phx.gbl...
>I was thinking that there must have been a problem there but why can't I
>convert it to a string and have it more accurate? That isn't working
>either.
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:umCpU6ulGHA.4144@.TK2MSFTNGP05.phx.gbl...
> Due to a design flaw with Intel based chips, compute clock time is
> accurate to the nearest three thousandth of a second.
> This makes it difficult to coordinate precise times from a different time
> source to the times created by a Intel based computer. For example, an
> automated process control system have be recording times that are .003
> seconds 'off' what the computer thinks.
> You have to build in the .003 'fudge' factor.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Ellie" <nospam@.nospam.net> wrote in message
> news:O5OF6rulGHA.3816@.TK2MSFTNGP02.phx.gbl...
>|||Arnie Rowland (arnie@.1568.com) writes:
> Due to a design flaw with Intel based chips, compute clock time is > accurate to t
he nearest three thousandth of a second.

> This makes it difficult to coordinate precise times from a different
> time source to the times created by a Intel based computer. For example,
> an automated process control system have be recording times that are
> .003 seconds 'off' what the computer thinks.
Eh? It's perfectly possible in Windows to handle time down to
dissolution of 100 ns, I believe. At least, you can get far below
3.33 ms. For instance, in the SQL 2005 Profiler, you can opt to get
durations in s.
In fact, if you look at
http://manuals.sybase.com:80/online...=2862;lang=sv#X
you can see that Sybase has the same accuracy. And, as may know
Microsoft SQL Server was originally derived from Sybase. And Sybase
has its origin on Unix. So I doubt that whereever the design flaw
was, that it was with Intel chips.
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|||You are seeing a limit of the DATETIME datatype in SQL Server. From
the Books on Line: "...to an accuracy of one three-hundredth of a
second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values
are rounded to increments of .000, .003, or .007 seconds..."
So, to store the time more accurately you must, as Aaron explained,
have to "roll your own".
Roy Harvey
Beacon Falls, CT
On Fri, 23 Jun 2006 14:13:22 -0400, "Ellie" <nospam@.nospam.net> wrote:

>I was thinking that there must have been a problem there but why can't I
>convert it to a string and have it more accurate? That isn't working either
.
>
>"Arnie Rowland" <arnie@.1568.com> wrote in message
>news:umCpU6ulGHA.4144@.TK2MSFTNGP05.phx.gbl...
>Due to a design flaw with Intel based chips, compute clock time is accurate
>to the nearest three thousandth of a second.
>This makes it difficult to coordinate precise times from a different time
>source to the times created by a Intel based computer. For example, an
>automated process control system have be recording times that are .003
>seconds 'off' what the computer thinks.
>You have to build in the .003 'fudge' factor.|||My brain checked out -it is a code artifact that predates Intel chip
design -is more in keeping of what I wanted to say. The latest IntelAMD
chips are capable of almost s precision.
Erlund, Thanks for catching this.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97EC62C58ABBYazorman@.127.0.0.1...
> Arnie Rowland (arnie@.1568.com) writes:
>
> Eh? It's perfectly possible in Windows to handle time down to
> dissolution of 100 ns, I believe. At least, you can get far below
> 3.33 ms. For instance, in the SQL 2005 Profiler, you can opt to get
> durations in s.
> In fact, if you look at
> http://manuals.sybase.com:80/online...=2862;lang=sv#X
> you can see that Sybase has the same accuracy. And, as may know
> Microsoft SQL Server was originally derived from Sybase. And Sybase
> has its origin on Unix. So I doubt that whereever the design flaw
> was, that it was with Intel chips.
>
> --
> 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|||So does this mean that internally they are not rounded (0.00333) but what
I'm seeing in query analyzer when I try to query it, is the rounded .000,
.003 or .007, etc.? I'm not concerned about the accurate storing of the tim
e
but the query afterwards. If they are stored inaccurately, I should still be
able to use that query, unless it is stored internally pre-rounding. I still
don't get why I can't convert it to a string and do a string comparison
though. Hope I am making sense.
Thanks for all of your help. I'll read some of the links and work around
this.
Ellie
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:u21p9299dg97vdtin85imfs525fkefinc7@.
4ax.com...
> You are seeing a limit of the DATETIME datatype in SQL Server. From
> the Books on Line: "...to an accuracy of one three-hundredth of a
> second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values
> are rounded to increments of .000, .003, or .007 seconds..."
> So, to store the time more accurately you must, as Aaron explained,
> have to "roll your own".
> Roy Harvey
> Beacon Falls, CT
> On Fri, 23 Jun 2006 14:13:22 -0400, "Ellie" <nospam@.nospam.net> wrote:
>|||Effectively they are rounded (as demonstrated below) and stored that
way. Comparison of a string against a datetime is performed by
comparing datetime types after converting the string. The string
conversion follows the rules demonstrated below.
Lets look at them as strings, and as strings converted to datetime:
select '2006-06-16 09:04:39.338',
convert(datetime,'2006-06-16 09:04:39.338') UNION
select '2006-06-16 09:04:39.339',
convert(datetime,'2006-06-16 09:04:39.339') UNION
select '2006-06-16 09:04:39.340',
convert(datetime,'2006-06-16 09:04:39.340') UNION
select '2006-06-16 09:04:39.341',
convert(datetime,'2006-06-16 09:04:39.341') UNION
select '2006-06-16 09:04:39.342',
convert(datetime,'2006-06-16 09:04:39.342') UNION
select '2006-06-16 09:04:39.343',
convert(datetime,'2006-06-16 09:04:39.343') UNION
select '2006-06-16 09:04:39.344',
convert(datetime,'2006-06-16 09:04:39.344') UNION
select '2006-06-16 09:04:39.345',
convert(datetime,'2006-06-16 09:04:39.345') UNION
select '2006-06-16 09:04:39.346',
convert(datetime,'2006-06-16 09:04:39.346') UNION
select '2006-06-16 09:04:39.347',
convert(datetime,'2006-06-16 09:04:39.347') UNION
select '2006-06-16 09:04:39.348',
convert(datetime,'2006-06-16 09:04:39.348') UNION
select '2006-06-16 09:04:39.349',
convert(datetime,'2006-06-16 09:04:39.349') UNION
select '2006-06-16 09:04:39.350',
convert(datetime,'2006-06-16 09:04:39.350') UNION
select '2006-06-16 09:04:39.351',
convert(datetime,'2006-06-16 09:04:39.351') UNION
select '2006-06-16 09:04:39.352',
convert(datetime,'2006-06-16 09:04:39.352')
order by 1
2006-06-16 09:04:39.338 2006-06-16 09:04:39.337
2006-06-16 09:04:39.339 2006-06-16 09:04:39.340
2006-06-16 09:04:39.340 2006-06-16 09:04:39.340
2006-06-16 09:04:39.341 2006-06-16 09:04:39.340
2006-06-16 09:04:39.342 2006-06-16 09:04:39.343
2006-06-16 09:04:39.343 2006-06-16 09:04:39.343
2006-06-16 09:04:39.344 2006-06-16 09:04:39.343
2006-06-16 09:04:39.345 2006-06-16 09:04:39.347
2006-06-16 09:04:39.346 2006-06-16 09:04:39.347
2006-06-16 09:04:39.347 2006-06-16 09:04:39.347
2006-06-16 09:04:39.348 2006-06-16 09:04:39.347
2006-06-16 09:04:39.349 2006-06-16 09:04:39.350
2006-06-16 09:04:39.350 2006-06-16 09:04:39.350
2006-06-16 09:04:39.351 2006-06-16 09:04:39.350
2006-06-16 09:04:39.352 2006-06-16 09:04:39.353
I hope that helps.
Roy Harvey
Beacon Falls, CT
On Sat, 24 Jun 2006 07:48:43 -0400, "Ellie" <nospam@.nospam.net> wrote:

>So does this mean that internally they are not rounded (0.00333) but what
>I'm seeing in query analyzer when I try to query it, is the rounded .000,
>.003 or .007, etc.? I'm not concerned about the accurate storing of the tim
e
>but the query afterwards. If they are stored inaccurately, I should still b
e
>able to use that query, unless it is stored internally pre-rounding. I stil
l
>don't get why I can't convert it to a string and do a string comparison
>though. Hope I am making sense.
>Thanks for all of your help. I'll read some of the links and work around
>this.
>Ellie
>"Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:u21p9299dg97vdtin85imfs525fkefinc7@.
4ax.com...
>