Recovery Model code in SQL 2000

In SQL 2005, sys.databases has a column named recovery_model that stores a code for the type of recovery model used by the database. Where is the recovery_model column in the SQL 2000 master database?

Thanks,

Craig

Code Snippet

select name, Databasepropertyex(name, 'Recovery') recovery from master..sysdatabases

sql

Records NOT in OTHER table - SELECT statement

I am trying to compare 2 tables with similar data. One table has a field named [X Sent] and the other table has a field named [X Setup]. I was trying to extract all of the fields in Table 1 with field [X Sent] that are not yet in Table 2, i.e. field [X Setup]. I was trying some LEFT JOINS and other stuff but I can't seem to get the syntax correct. How can I get a query of only the fields that are not in Table 2, but are in Table 1?

Here are two of my attempts:

SELECT [X Sent], [X Date]
FROM [Table 1]
WHERE ([X Sent] NOT IN
(SELECT [Table 1].[X Sent]
FROM [Table 2] INNER JOIN
[Table 1] ON [Table 2].[X Setup]= [Table 1].[X Sent]))

I don't believe 'NOT IN' is proper syntax!

And my other attempt:

SELECT [Table 1].[X Sent]
FROM [Table 1] LEFT OUTER JOIN
[Table 2] ON [Table 2].[X Setup]= [Table 1].[X Sent]

Any help would greatly be appreciated.An outer join, checking for NULL in [Table 2] will do it.

SELECT [X Sent],[X Date] FROM
[Table 1] t1 LEFT OUTER JOIN [Table 2] t2 ON t1.[X Sent]=t2.[X Setup]
WHERE t2.[X Setup] IS NULL

|||Thanks once again Douglas Reilly!!!!

Greatly appreciative...|||After analyzing the data further, I have noticed that I can't only compare one field, but rather two from each table. I have been trying to work with the following statement, but I can't seem to get it to work. To start off with, I would like to capture all the fields where Field1 and Field2 from Table1 equals Field1 and Field2 of Table2.

SELECT [Field1], [Field2]
FROM Table1 t1 INNER JOIN
Table2 t2 ON t1.Field1 = t2.Field1 AND t1.Field2 = t2.Field2

Once I figure this part out, I will go ahead and search for all of these tuples not in the other table. Your assistance will greatly be appreciated.|||You should pick one of your candidate keys to be the primary key of Table1. Then you can do this:

select * from Table1
where Table1PrimaryKey not in
(select Table1PrimaryKey from Table1
inner join Table2 on Table1.Field1=Table2.Field1 and Table1.Field2=Table2.Field2)

If for some reason you really need the fields, you can use this:

select Table1.Field1, Table1.Field2
from Table1
inner join Table2 on Table1.Field1=Table2.Field1 and Table1.Field2=Table2.Field2

Records drilled through multiple times due to Partitions

Hi,

I have created a cube for a telephony database. In this database there are two fields that point to the same table named tblPhonebook. These fields are FromPhonebookID and ToPhonebookID, which respectively contain the Person in the phonebook who placed the call and the Person in the phonebook who received the call. (the latter could both be an internal person and an external person).

I created one dimension. The Phonebook dimension which points to the table tblPhonebook.

When I select a person in this dimension I want to see all the calls this person placed and also all the calls this person received. So I created two "views" in SQL Server.

view1: SELECT ToID as UseID, * FROM tblData

view2: SELECT FromID as UseID, * FROM tblData

With the Cube I created two partitions. The first partition takes view1 as data table, the second partition takes view2 as data table. My Phonebook dimension is related to the UseID in these views.

So far so good. When I select a person in the Phonebook dimension, I get a neat result of his ingoing and outgoing phonecalls. However, when I select "All Phonebook", so not a specific user in the phonebook list, and instead select a date for instance, there occurs a problem. Because no Phonebook entry is specified there are always two entries per phonecall. One from view1 and one from view2. So my calls are counted double.

For counting the calls this is also not a problem, because I can perform a Distinct Count on the tblData.ID. I also measure the Average of the Conversation Time. This is also not a problem, because every value is counted double, not one excluded, so the average stays the same (isn't it? conversations lasting 1, 8 and 12 seconds average on 7 seconds. If those are counted twice then it would be 1,1,8,8,12,12 which also average on 7).

But when I select a drillthrough (with "All Phonebook" selected) the problem arrises. All entries are showed double in my drillthrough. First all the From Data ordered by date, then all the To Data ordered by date.

Ofcourse I don't want that. I want them only to be listed once. How do I filter this drillthrough that when no phonebook entry is selected, they still only appear once in my list?

(Sorry for the long introduction to a one line question ;))

Greets,

Edward

For one, Analysis Server in this case delivers exactly to the promise : in the result of the drillrhough it returns you all the data you selected to perform the drillthrough into.

Now about your situation. To solve your problem, you might decide to model your cube a bit different. You can create another dimension with only a single non-aggregatable attribute and 2 values "From" and "To". Then your user will choose to drill through into which part of the data.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hmmm... so no filter possible? that was what I was afraid for ;)

Thanks for looking into my problem....

Greets,

Edward