Attach db failure cause log not primary

Hi Everybody,

I need URGENT help with this. Because of space problem my collegue create a second log file on another drive. I wanted to clean the thing, made a detach, renamed the ldf into old, but now it won't attach anymore, I get the following messages:

EXEC sp_attach_db @.dbname='TARIFINFO_GEN',
@.filename1 = 'F:\Data\TARIFINFO_GEN_Data.mdf'

Server: Msg 5171 F:\Data\TARIFINFO_GEN_log.ldf is not a primary database file
Server: Msg 1813 Device activation error the physical file name 'F:\Data\TARIFINFO_GEN_log.ldf' may be incorrect

Do someone know what to do or where to look for a solution. Many thanks in advance, Isabelle.Try sp_attach_single_file_db 'TARIFINFO_GEN',
'F:\Data\TARIFINFO_GEN_Data.mdf'

Also, in many instances to clean logs you should be able to run something like:

backup log TARIFINFO_GEN with truncate_only

and then a dbcc shrinkfile.

Instead of detaching and re-attaching.

Eric

Asymmetric data syncronization: possible?

I have two sql server instances on two different machines with the same db;
on the first machine some tables need to be copied on the second machine,
and on the second machine some other tables need to be copied on the first;
all the tables are related with the others in some way.
It is possible to make that replication?
Thanks
Mik
I'm wondering if the same data can be changed on each node? If so, then it
sounds like you need either merge or updating transactional subscribers
(queued or immediate). I'd control this from one server designated as the
publisher and synchronize this way. If this is not the case, then both nodes
could be set up as transactional publishers.
Rgds,
Paul Ibison

Assistance developing Query

Good Day;

I would appreciate assistance developing a query that I haven't been
able to develop without using a second table. I wish to count the
number of records that are still open on the first of each month.
Each record has an open date and a close date or the close date is
null i.e., the record is not yet closed. I've previously beaten this
by building a table, simply a list of the dates for the first of each
month for the next ten years or so, and then selecting values based
upon a date selected from that table. However I'd be happier if I
could do it without the second table. I'd be prepared to accept the
Min(Date) for each month as being the first of the month.

I've included some DDL statements to build and populate the table if
that helps. Since the selection is rather small and all the open
dates are very close together I think the result will be simply a
decreasing count from the month the first record is opened till today.

A pseudo code select statement might look like

Select Min(DateOpened) As DateOfInterest, Count(*) as [Qty Still Open]
FROM DetailT
Where DateReceived > DateOfInterest or DateReceived is Null and
DateOpened < DateOfInterest
Group by Min(DateOpened)
Order by Min(DateOpened)

I hope I've explained it sufficiently well.

CREATE TABLE [dbo].[DetailT] (
[Autonum] [int] IDENTITY (1, 1) NOT NULL ,
[QDNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateOpened] [smalldatetime] NOT NULL ,
[DateReceived] [smalldatetime] NULL ,

)
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('C15788', '06/04/2005 9:35', 07/04/2005)
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('B16091', '06/04/2005 9:36', '07/04/2005')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('B15001', '06/04/2005 9:51', '08/04/2005')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('M18696', '06/04/2005 9:56', '06/04/2005')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('C14969', '06/04/2005 10:05', '10/04/2005')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('O10091', '06/04/2005 10:08', '12/04/2005')
Insert into DetailT (QDNumber, DateOpened)
VALUES('D01197', '06/04/2005 10:13')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('H15001', '06/04/2005 10:15', '08/04/2005')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('J15090', '06/04/2005 10:24', '08/04/2005')
Insert into DetailT (QDNumber, DateOpened)
VALUES('J01202', '06/04/2005 10:31')
Insert into DetailT (QDNumber, DateOpened)
VALUES('G01193', '06/04/2005 10:32')
Insert into DetailT (QDNumber, DateOpened)
VALUES('K01164', '06/04/2005 10:35')
Insert into DetailT (QDNumber, DateOpened)
VALUES('K01162', '06/04/2005 10:48')
Insert into DetailT (QDNumber, DateOpened)
VALUES('F01124', '06/04/2005 10:59')
Insert into DetailT (QDNumber, DateOpened)
VALUES('H01147', '06/04/2005 11:01')
Insert into DetailT (QDNumber, DateOpened)
VALUES('S15068', '06/04/2005 11:10')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('E12322', '06/04/2005 11:32', '07/04/2005')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('A12205', '06/04/2005 11:37', '06/04/2005')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('D12259', '06/04/2005 11:40', '07/04/2005')
Insert into DetailT (QDNumber, DateOpened)
VALUES('C03394', '06/04/2005 11:51')

If you made it this far thank you for your patience. Any help would be
appreciated.

Thank you.

BillBill wrote:

> Good Day;
> I would appreciate assistance developing a query that I haven't been
> able to develop without using a second table. I wish to count the
> number of records that are still open on the first of each month.
> Each record has an open date and a close date or the close date is
> null i.e., the record is not yet closed. I've previously beaten this
> by building a table, simply a list of the dates for the first of each
> month for the next ten years or so, and then selecting values based
> upon a date selected from that table. However I'd be happier if I
> could do it without the second table. I'd be prepared to accept the
> Min(Date) for each month as being the first of the month.
> I've included some DDL statements to build and populate the table if
> that helps. Since the selection is rather small and all the open
> dates are very close together I think the result will be simply a
> decreasing count from the month the first record is opened till today.
> A pseudo code select statement might look like
> Select Min(DateOpened) As DateOfInterest, Count(*) as [Qty Still Open]
> FROM DetailT
> Where DateReceived > DateOfInterest or DateReceived is Null and
> DateOpened < DateOfInterest
> Group by Min(DateOpened)
> Order by Min(DateOpened)
> I hope I've explained it sufficiently well.
> CREATE TABLE [dbo].[DetailT] (
> [Autonum] [int] IDENTITY (1, 1) NOT NULL ,
> [QDNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [DateOpened] [smalldatetime] NOT NULL ,
> [DateReceived] [smalldatetime] NULL ,
> )
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('C15788', '06/04/2005 9:35', 07/04/2005)
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('B16091', '06/04/2005 9:36', '07/04/2005')
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('B15001', '06/04/2005 9:51', '08/04/2005')
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('M18696', '06/04/2005 9:56', '06/04/2005')
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('C14969', '06/04/2005 10:05', '10/04/2005')
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('O10091', '06/04/2005 10:08', '12/04/2005')
> Insert into DetailT (QDNumber, DateOpened)
> VALUES('D01197', '06/04/2005 10:13')
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('H15001', '06/04/2005 10:15', '08/04/2005')
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('J15090', '06/04/2005 10:24', '08/04/2005')
> Insert into DetailT (QDNumber, DateOpened)
> VALUES('J01202', '06/04/2005 10:31')
> Insert into DetailT (QDNumber, DateOpened)
> VALUES('G01193', '06/04/2005 10:32')
> Insert into DetailT (QDNumber, DateOpened)
> VALUES('K01164', '06/04/2005 10:35')
> Insert into DetailT (QDNumber, DateOpened)
> VALUES('K01162', '06/04/2005 10:48')
> Insert into DetailT (QDNumber, DateOpened)
> VALUES('F01124', '06/04/2005 10:59')
> Insert into DetailT (QDNumber, DateOpened)
> VALUES('H01147', '06/04/2005 11:01')
> Insert into DetailT (QDNumber, DateOpened)
> VALUES('S15068', '06/04/2005 11:10')
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('E12322', '06/04/2005 11:32', '07/04/2005')
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('A12205', '06/04/2005 11:37', '06/04/2005')
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('D12259', '06/04/2005 11:40', '07/04/2005')
> Insert into DetailT (QDNumber, DateOpened)
> VALUES('C03394', '06/04/2005 11:51')
> If you made it this far thank you for your patience. Any help would be
> appreciated.
> Thank you.
> Bill

Where the SQL statement you would use to load the table?
Put parentheses around it.
Go from there.
There may be far more elegant solutions but your the one getting
the paycheck. ;-)
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace 'x' with 'u' to respond)|||On 18 Apr 2005 16:36:38 -0700, Bill wrote:

>I would appreciate assistance developing a query that I haven't been
>able to develop without using a second table.
(snip)
>I've previously beaten this
>by building a table, simply a list of the dates for the first of each
>month for the next ten years or so, and then selecting values based
>upon a date selected from that table. However I'd be happier if I
>could do it without the second table.

Hi Bill,

Why do you want to do it wothout a second table? Having a permanent
auxiliary calendar table in your database is actually quite useful and I
think that no database should ever be without one.
Here's a link to an article that shows how to create a general
all-purpose calendar table, how to fill it with data and several
examples of how to use it: http://www.aspfaq.com/show.asp?id=2519.

And here's how I'd write your query, using the table described above:

SELECT c.dt,
COUNT(*) as "Qty Still Open"
FROM Calendar AS c
INNER JOIN DetailT
ON ( DateReceived > c.dt OR DateReceived IS NULL )
AND DateOpened < c.dt
WHERE c.D = 1
AND c.dt BETWEEN (SELECT MIN(DateOpened)
FROM DetailT)
AND DATEADD(month, 1, (SELECT MAX(DateReceived)
FROM DetailT))
GROUP BY c.dt
ORDER BY c.dt

This one cuts off the listing at the lowest number. If you increase the
number (1) in the DATEADD expression, you can see that the number of
open cases remains constant after the last month listed.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||DA Morgan <damorgan@.x.washington.edu> wrote in message news:<1113891730.819997@.yasure>...
> Bill wrote:
> > Good Day;
> > I would appreciate assistance developing a query that I haven't been
> > able to develop without using a second table.

> > Thank you.
> > Bill
> Where the SQL statement you would use to load the table?
> Put parentheses around it.
> Go from there.
> There may be far more elegant solutions but your the one getting
> the paycheck. ;-)

Dan;

I'm sorry but I don't understand what you're trying to tell me. If
your first question is "Where is the SQL statement you would use to
load the table?" I don't have one, the data base is interactively
updated through an ASP based HTML form over the web. I did go to the
work of providing insert statements to assist anyone who might try to
help.

I don't understand what putting parentheses around it would do?

I hope there is a more elegant solution but unfortuantely I haven't
figured it out and was simply asking for assistance. If you don't
wish to help thats ok with me. Yes, I'm getting paid for the work I
do, but developing this solution is not what I was trained or educated
to do an so I'm learning as I go. I appologize if I've bothered you
by asking for help.

I will continue to explore the solution.

Cheers;

Bill|||Hugo Kornelis <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message news:<8oqa61p4rgmhtcfh7rgsc8dup9pj1a9d1t@.4ax.com>...
> On 18 Apr 2005 16:36:38 -0700, Bill wrote:
> >I would appreciate assistance developing a query that I haven't been
> >able to develop without using a second table.
> (snip)
> >I've previously beaten this
> >by building a table, simply a list of the dates for the first of each
> >month for the next ten years or so, and then selecting values based
> >upon a date selected from that table. However I'd be happier if I
> >could do it without the second table.
> Hi Bill,
> Why do you want to do it wothout a second table? Having a permanent
> auxiliary calendar table in your database is actually quite useful and I
> think that no database should ever be without one.
...
> This one cuts off the listing at the lowest number. If you increase the
> number (1) in the DATEADD expression, you can see that the number of
> open cases remains constant after the last month listed.
> Best, Hugo

Hugo;

Thank you for your assistance. I've reviewed the article you pointed
me to and have spent my spare time today building a number table and a
date table. I was reluctant to duplicate my earlier date table, which
only contained the dates for the first of the month into the SQL
Server environment since I felt that it was both cheating and
confusing. However the article showed that this can be a very useful
table and since it's been published I don't feel too bad about
emulating someone's work who know more about application development
than I do.

I do have one question, based mainly on my lack of formal training in
SQL Server and my experience this morning building the calendar table.
I was copying the code from the article and pasting it into SQL Query
Analyzer and running it, as I'm not certain where or how this code
should be executed. Most things ran very quickly after I modified
them properly to meet my environment. However the adding of row's to
the Calendar table (4,096) took several hours (2-3) I was really
surprised by this and wondered if I was doing something wrong but
since it finished successfully and subsequent code samples executed
quickly I moved on. My question is am I using the right part of the
SQL Server environment for this sort of work?

Thank you once again for your time and your assistance. Now that I've
overcome my reluctance to using the calendar table I'm comfortable
enough to go on and using you sample query to get what I was looking
for.

Thank you.

Cheers;

Bill|||On 20 Apr 2005 16:06:36 -0700, Bill wrote:

(snip)
>the article showed that this can be a very useful
>table and since it's been published I don't feel too bad about
>emulating someone's work who know more about application development
>than I do.

Hi Bill,

You certainly should not feel bad about it - sharing the code is exactly
the reason why Aaron has published it on his site.

Copying work from others may be a sin in artistic creative work, but in
software development, it's a sin NOT to copy and adapt proven solutions.

(snip)
>Most things ran very quickly after I modified
>them properly to meet my environment. However the adding of row's to
>the Calendar table (4,096) took several hours (2-3) I was really
>surprised by this

And so am I. Okay, the server does have a bit of work to do when
populating the table, but under normal circumstances, I would not expect
it to run for so long! A couple of minutes, maybe. Not hours.

I'm not sure if you still care to investigate this (since you now have
the table, and it's a one-time job after all), but if you do, then could
you please post the exact code you used to create and popultae the
tables?

>My question is am I using the right part of the
>SQL Server environment for this sort of work?

Yes, Query Analyzer is exactly the tool to use for these jobs.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

ASP-Application

Hi,
i have my application running with two servers ( iis - sql
server ).
What is securest way to access from the first workstation
(iis) on the second ( sql server ) ?
With an DSN or a Connection String ?
I am running a ASP-application !
Greetings,
Daniel RakojevicYou could run your application on the IIS server under the context of an acc
ount (domain or local to the SQL server), grant this account access to the d
atabase, and then set the DSN on the IIS box to use trusted_connection=YES.
if you need to secure the data in transit, you could also enable SSL encrypt
ion on the SQL Server:
http://support.microsoft.com/defaul...kb;en-us;316898
--Roberto
-- Daniel Rakojevic wrote: --
Hi,
i have my application running with two servers ( iis - sql
server ).
What is securest way to access from the first workstation
(iis) on the second ( sql server ) ?
With an DSN or a Connection String ?
I am running a ASP-application !
Greetings,
Daniel Rakojevic

ASP.net SQL Problem

Hello,

I have as exemple these values into my database:
ruiter paard punten
1 1 2
1 1 10
2 1 4
1 1 10

i want the second highest value of "punten" grouped by ruiter and paard

this is what i have got so far:
SELECT u.ruiter,u.paard,(SELECT max(TOP 2 punten) FROM uitslag where ruiter = u.ruiter and paard = u.paard) AS points1 FROM uitslag u group by u.ruiter,u.paard"

when i put this code into my asp.net page i get errors

Someone any ideas how i can fix this??
When i can get it to work for the second highest value. I then can work further for the third, fourth,... highest value.

tnx alot
PhilippeUse Query Analyser, that SQL just isn't SQL. It will give you better hints as to why it's wrong