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 Light Saving Time Change

Please fix your system clock.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23w4WIE8SHHA.4784@.TK2MSFTNGP03.phx.gbl...
> Please fix your system clock.
That's why he's so interested in Daylight Savings Time. He's trying to get
back from the future.

Day Light Saving Time Change

Please fix your system clock."Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23w4WIE8SHHA.4784@.TK2MSFTNGP03.phx.gbl...
> Please fix your system clock.
That's why he's so interested in Daylight Savings Time. He's trying to get
back from the future.

Day Light Saving Time Change

Please fix your system clock."Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:%23w4WIE8SHHA.4784@.TK2MSFTNGP03.phx.gbl...
> Please fix your system clock.
That's why he's so interested in Daylight Savings Time. He's trying to get
back from the future.

DateTime Query

Hi, am trying to build a scheduling system within my SQL Server application. Can someone point me in a good direction please?

OK, A user can select that they want something to happen Weekly, and on each Tuesday of every week. They of course can select any day from Monday through to Sunday. I would like to know how to take this data, and through a stored procedure update a table to set the "next execution date".

I have sorted the Daily timetable for each time, and the Monthly on a certain date seems easy enough, but I cant get the Weekly on a certain Day sorted. Any advice would be great!

Maybe you could post some code of your table and query...?

Though I'm not sure why you have multiple tables; monthly, weekly, daily.

You should just need one

NextExecutionMgr( DueDate datetime, FreqIntvl varchar(2), FreqAmt int, RecordKey varchar(200) )

index on DueDate, most likely a second index on RecordKey

The first item on your DueDate index is the next one to be processed.

When its time comes and once it is processesed you just adjust the date:

Code Snippet

case FreqIntvl when 'dy' then DueDate = DateAdd(dy, FreqAmt, DueDate)

when 'wk' then DueDate = DateAdd(wk, FreqAmt, DueDate)

etc.

end

(doesn't it suck that dateadd doesn't accept a variable for parameter one?)

|||

Why re-invent the wheel?

I would recommend exploring the SQL Agent Service, since it has full features calendaring and scheduling already built-in.

And if you are using SQL 2005 Express, which doens't include SQL Agent, you could explore a combination of using the Windows Scheduler service and SQLCmd.exe.

|||

Arnie, quite true.

I guess it just depends on what it is he's trying to schedule.

Agent is perfect for scheduled system level events and tasks.

But if he's trying to kick off application events with 1,000's of users, that a different thing.

Lotsa cats...

|||

And the skin just regrows...

I suspect that the solution will evolve into a combination of efforts -your outline about how to manage a 'queue' table, and some form of a scheduled process to 'POP' the queue.

There just isn't enough information to point the OP in the 'best' direction. SQL Agent, Notification Service, Service Broker Queues, some 'homegrown' hybrid, ...

datepart

I am trying to use datepart to determine what row in a table a users
hiredate is closest to current system date
example
JOE was hired in Mar 01 2000
I need to get his payrate based off months experience
<12 months
<24 months
<60 months
<120 months
<200 months
Thanks
mike
something like below.
select hiredate, monthsrow from payee, payrategroup where
hiredate,
getdate(),
ltrim(datediff(month, experience_date, getdate()) / 12) + '.'
+
ltrim(datediff(month, experience_date, getdate()) % 12) as months <=
monthsrowHi
CREATE TABLE #Test
(
empl INT NOT NULL PRIMARY KEY,
hiredate DATETIME NOT NULL
)
INSERT INTO #Test VALUES (1,'20060101')
INSERT INTO #Test VALUES (2,'20060101')
INSERT INTO #Test VALUES (3,'20060409')
INSERT INTO #Test VALUES (4,'20060110')
INSERT INTO #Test VALUES (5,'20060112')
INSERT INTO #Test VALUES (6,'20060120')
INSERT INTO #Test VALUES (7,'20060108')
INSERT INTO #Test VALUES (8,'20060103')
DECLARE @.dt DATETIME
SET @.dt ='20060115' --desired date
SELECT TOP 1 WITH TIES *
FROM #Test WHERE hiredate>'20050101' AND hiredate < DATEADD(day,1,@.dt)
ORDER BY hiredate DESC
<ciojr@.yahoo.com> wrote in message
news:1144550863.151230.197030@.t31g2000cwb.googlegroups.com...
>I am trying to use datepart to determine what row in a table a users
> hiredate is closest to current system date
> example
> JOE was hired in Mar 01 2000
> I need to get his payrate based off months experience
> <12 months
> <24 months
> <60 months
> <120 months
> <200 months
> Thanks
> mike
> something like below.
> select hiredate, monthsrow from payee, payrategroup where
> hiredate,
> getdate(),
> ltrim(datediff(month, experience_date, getdate()) / 12) + '.'
> +
> ltrim(datediff(month, experience_date, getdate()) % 12) as months <=
> monthsrow
>|||Hi Mike,
Can you give the ddls and the expected output. The question seems to be
a bit confusing.|||not what I am looking for.

Date/Time formatting

Hi all, I'm hoping this is an easy solution . . .
How can I format the system date: getdate() as mm/dd/yyyy hh:mi am?
Am I missing something? I am fairly new to sql server . . .
I would use datepart, but I cannot find a way to display the time like
I have shown.
Your help is appreciated.
Thank you and have a great night,
Ryan<ryan.mclean@.gmail.com> wrote in message
news:1130715275.732555.138960@.g43g2000cwa.googlegroups.com...
> Hi all, I'm hoping this is an easy solution . . .
> How can I format the system date: getdate() as mm/dd/yyyy hh:mi am?
> Am I missing something? I am fairly new to sql server . . .
> I would use datepart, but I cannot find a way to display the time like
> I have shown.
> Your help is appreciated.
> Thank you and have a great night,
> Ryan
>
The way your dates and times are displayed is controlled by your client
application, not by SQL Server. Format the value client-side.
If you must control the format from the server then you'll have to return a
string instead of a DATETIME. Take a look at the CONVERT function in Books
Online. CONVERT allows you to convert DATETIME to a string in any one of
various formats.
David Portas
SQL Server MVP
--|||<ryan.mclean@.gmail.com> wrote in message
news:1130715275.732555.138960@.g43g2000cwa.googlegroups.com...
> Hi all, I'm hoping this is an easy solution . . .
> How can I format the system date: getdate() as mm/dd/yyyy hh:mi am?
> Am I missing something? I am fairly new to sql server . . .
> I would use datepart, but I cannot find a way to display the time like
> I have shown.
> Your help is appreciated.
> Thank you and have a great night,
> Ryan
>
The way your dates and times are displayed is controlled by your client
application, not by SQL Server. Format the value client-side.
If you must control the format from the server then you'll have to return a
string instead of a DATETIME. Take a look at the CONVERT function in Books
Online. CONVERT allows you to convert DATETIME to a string in any one of
various formats.
David Portas
SQL Server MVP
--|||Hi David,
First, thanks for posting a reply . . .
I am displaying the data in an asp.net datagrid, so it is actually more
efficient if I format the data on the database server in my sql
statement.
I have looked at the convert function, and I don't see where I can
format the data how I have specified. In ocacle, I would use to_date
and it would be done.
Thanks for trying, but I need a little more guidance then suggesting a
function.
Ryan|||I'm surprised that you think it's more efficient to do formatting in
the database than in ADO. Usually formatting is done client side
precisely to avoid putting that overhead on the database. ADO provides
date formatting through the ToString method for that purpose.
Aside from efficiency, doing it at the client has the advantage of
being user-configurable. Apparently you want to force all your users to
conform to your preference on how they should view dates. Also, if you
return a formatted string in the form you suggested then the user won't
be able to sort on it.
To do it in the database would look something like this:
SELECT CONVERT(CHAR(10),CURRENT_TIMESTAMP,101)+
'
'+RIGHT(CONVERT(VARCHAR,CURRENT_TIMESTAM
P,0),8)
David Portas
SQL Server MVP
--|||>> I have looked at the convert function, and I don't see where I can format
SELECT CONVERT( VARCHAR, GETDATE(), 101 ) +
RIGHT( CONVERT( VARCHAR, GETDATE(), 100 ), 8 )
Anith|||Hi again,
David, that is a good point and it would probably be more applicable if
it were a distributed app (it's just an admin tool that we will use).
I just don't see the advantage of pulling back a data set, then looping
through every record and messing with each item individually when I can
have the database engine do all the work, then I just have to display
it.
Anyway, always a learning process. I really appreciate your posts.
Also, a thank you to Anith.
Thanks and have a great day,
Ryan