Database

Re: Query in SQL Server (machines that are now in specific project)

31-Oct-12 2:52

Sounds like a simple query to me. Did I forget something or have I misread the question?

SQL

SELECT TOP 1 MachineName
  FROM MyTable
 WHERE ProjectName = 'Web 8.0'
 ORDER BY [DateTime] DESC

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Elina.Ma31-Oct-12 3:12

Elina.Ma

31-Oct-12 3:12

I want all machines that now be in specific project , may machines "a", 2 days ago in project "1" and now be in project "2" and machines "b" now in project "1", when I wand machines in project "1" ,only "b" should be in result and Should not machine "a" be in result

Re: Query in SQL Server (machines that are now in specific project)

Eddy Vluggen31-Oct-12 3:19

Re: Query in SQL Server (machines that are now in specific project)

31-Oct-12 3:19

Member 8407653 wrote:
I want all machines that now

Change the Sql accordingly. I want coffee.

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Richard Deeming31-Oct-12 3:28

Richard Deeming

31-Oct-12 3:28

Something like this should work:

SQL

WITH cteOrderdAssignments As
(
   SELECT
      MachineName,
      ProjectName,
      ROW_NUMBER() OVER ( PARTITION BY MachineName ORDER BY [DateTime] DESC ) As RowNumber
   FROM
      MyTable
)
SELECT
   MachineName
FROM
   cteOrderedAssignments
WHERE
   RowNumber = 1
And
   ProjectName = 'Web 8.0'
ORDER BY
   MachineName
;

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: Query in SQL Server (machines that are now in specific project)

Herman<T>.Instance2-Nov-12 5:01

Herman<T>.Instance

2-Nov-12 5:01

Row_number() function in combination with common table expression is the best way indeed

In Word you can only store 2 bytes. That is why I use Writer.

Get databases mapped to login

Danzy8331-Oct-12 1:59

Danzy83

31-Oct-12 1:59

Hi everyone! I need to get a list of databases mapped to a login in Sql Server 2008. I have two databases which I have mapped both databases to a login.

What is the required script to return the login mapped databases? I'm able to do this in Management Studio by right-clicking on the login->Properties->User Mapping which shows the mapped databases as checked.

I'm however writing a client application in Windows Forms and I would like to know the script that can return the login mapped databases. Please help.

Re: Get databases mapped to login

Eddy Vluggen31-Oct-12 2:14

Re: Get databases mapped to login

31-Oct-12 2:14

Dan_K wrote:
What is the required script to return the login mapped databases?

No idea, but hang on, and we'll introduce a way to figure it out.

Dan_K wrote:
I'm able to do this in Management Studio by right-clicking on the login->Properties->User Mapping which shows the mapped databases as checked.

The SMS uses Sql-commands, and one can intercept[^] those. Meaning, you can "look" at what command the application is executing to get those results.

Enjoy Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Danzy8331-Oct-12 2:25

Danzy83

31-Oct-12 2:25

Thanks Eddy. But provided links on the specified page were not found.

Re: Get databases mapped to login

Eddy Vluggen31-Oct-12 2:42

31-Oct-12 2:42

Aaaah, the open source profiler has become a commercial product. There's also a profiler included with Sql Server, but it would not be installed for a Sql Express version - that's why I linked an open-source version. You might want to try their trial-version[^].

Alternatively, there's an implementation of a TSql profiler here[^]. Guess I'll be using that one from now on, as I do not like to recommend trial-versions.

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

ADO broken in W7 SP1

hairy_hats30-Oct-12 6:19

hairy_hats

30-Oct-12 6:19

I just hit this problem today when recompiling an ADO-enabled project in VS2005 after installing Windows 7 SP1. After an hour of frantic Googling I found this[^] and this[^] and managed to get it to compile again, with testing on XP machines to follow soon.

As MS is apparently no longer recommending we use ADO, what should I be changing my C++ programs to use to talk to SQL Server?

Re: ADO broken in W7 SP1

Eddy Vluggen31-Oct-12 2:15

31-Oct-12 2:15

viaducting wrote:
As MS is apparently no longer recommending we use ADO

I did not read that in the links you provided. Can you point it out to me? It'd be a small game-changer if all older software would suddenly stop to work Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Re: ADO broken in W7 SP1

hairy_hats31-Oct-12 2:24

hairy_hats

31-Oct-12 2:24

Please note that VB6 and .NET ADO interop are not supported anymore. This forum post does not imply any future support of these out-of-support technologies.

and

we drastically underestimated the number of customers who were recompiling ADO applications on Windows 7 SP1. Even worse, when I say drastically, I really mean DRASTICALLY."

suggest to me that MS aren't expecting people to still be actively developing ADO-based projects.

Re: ADO broken in W7 SP1

Eddy Vluggen31-Oct-12 2:45

First Day using Oracle 11.g

31-Oct-12 2:45

viaducting wrote:
Please note that VB6 and .NET ADO interop are not supported anymore

That's saying something about VB6 and ADO interop. Not a statement on the future of ADO.

viaducting wrote:
suggest to me that MS aren't expecting people to still be actively developing ADO-based projects.

Second statement merely explains how a specific bug surprised the team.

viaducting wrote:
suggest to me that MS aren't expecting people to still be actively developing ADO-based projects.

VB6 is deprecated, and yes, I imagine that they extend that to the combination of ADO and VB6. There is no official statement that ADO is being phased out.

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

jkirkerx29-Oct-12 12:34

29-Oct-12 12:34

I'm confused here on Oracle 11.g Express. This is my first day using it, I read some of the documentation, but some things are not clear.

I was trying to create a database, but it seems like you just create connections, and that XE is the default database.

My question is XE the only database you can work with because it's the express edition?, or am I just completely missing something here.

It's been over 5 years since I have worked with anything other that SQL Server 2005+, so I'm kind of fuzzy here on the Oracle. I was able to setup user accounts, and write my stuff for asp,net to connect using the Oracle DLL.

modified 30-Oct-12 16:30pm.

jkirkerx29-Oct-12 15:32

29-Oct-12 15:32

[SOLVED]

I think I figured it out. I think XE is the sample database.

So I was able to create a new database, and then a tablespace, and made my tables, and was able to connect to it using asp.net, and read the data.

So far so good, until something else comes up.

Jörgen Andersson30-Oct-12 6:21

30-Oct-12 6:21

You're on the right track.
But Oracle has a different archtecture than SQLServer. You are not supposed to create several databases on one computer, that's just a waste of resources.

So a few concepts:

Quote: René Nyffenegger
In Oracle terminology, a user is someone who can connect to a database (if granted enough privileges) and optionally (again, if granted the appropriate privileges) can own objects (such as tables) in the database.

The objects a user owns are collectively called >schema. A schema, on its part, is always bound to exactly one user. Because there is obviously a 1 to 1 relationship between a user and a schema, these two terms are often used interchangeable.

The tablespace is a logical storage area containing of one or more physical datafiles. There can be one or more Schema's stored in one tablespace.

The tablespace is the equivalent to the database in a SQLServer, except that that would be a lie. It's just not the same thing

Now you have all the keywords you need to google all the info you need.

What you should do when you create a new application is to create a tablespace for it, to separate the data for easier handling when exporting for example.
Then create a new user having this new tablespace as the default tablespace. And using this user, create your objects, and you will have a new schema that your application can use.

People say nothing is impossible, but I do nothing every day.

jkirkerx30-Oct-12 10:30

30-Oct-12 10:30

Thank you, for the clear explanation of differences between Oracle and SQL Server.

I just wasn't able to put the relationships together, but now I get it. It seemed so different, and when I searched the terms, I would get fragments of information.

I never did understand the schema, and sort of ignored it in exchange for hey it works, but I can see how I need to learn that now.

That was a well written explanation that I'm sure others will stumble upon in their search.

Jörgen Andersson30-Oct-12 23:00

30-Oct-12 23:00

Thanks!
I just didn't feel that it was very clear myself. But if you know the keywords and concepts you can google it.
If you don't know what to google for, you can be stuck.

Here's[^] a comparison that I find very useful when I need to "translate" functionality between SQLServer and Oracle.

People say nothing is impossible, but I do nothing every day.

jkirkerx31-Oct-12 6:54

31-Oct-12 6:54

I needed that chart. I used a char for boolean Y/N, just going to change it to a number with 0/1.

At the moment, I'm not that thrilled with using it, but on day 2, I got all my stuff working fine. I won't see any thing that I can measure till Friday, when I put the 1000 parts in it, and take it for a test drive.

Guess I made the right choice on using the ODP.Net, instead of the Microsoft equivalent, since the latter is being phased out.

Today is day 3, guess I'm making good progress with it.

I decided to try it because of the complaints I received using SQL Server 2008 from customers that refused to buy my ecommerce software. They said they used a Java SQL Server, and that they wouldn't use that piece of junk, I didn't know what to make of that comment.

So when I mentioned to this customer that I would use Oracle for there project, they got all excited about it.

Just out of curiosity, what do think of the Oracle 11.g Express?

Jörgen Andersson31-Oct-12 11:23

31-Oct-12 11:23

I have never used the express version, but I understand that functionality is almost the same as the standard version except that it uses only one processor and maximum database size of 4GB. There's You will also only find bitmap and cluster indexes on the enterprise edition
So for development and small applications with low performance demands it should be just fine.

Just keep in mind that there is no built in backup in the express version, so if you're having a catastrophic failure you're f***ed. Unless you create your own backup, which isn't overly easy.
But flashback queries works, and that's what I use in 99% of the cases anyway.

People say nothing is impossible, but I do nothing every day.

Jörgen Andersson31-Oct-12 11:40

Re: 3rd Day using Oracle 11.g

31-Oct-12 11:40

jkirkerx wrote:
I needed that chart. I used a char for boolean Y/N, just going to change it to a
number with 0/1.

There's nothing wrong with using Y/N as such, it uses only one byte of storage just like the zero, while the one uses two bytes.
But 0/1 implicitly translates to boolean TRUE/FALSE in dot.net, while Y/N needs a bit of logic to convert to a boolean. Not that I recommend implicit conversions.

People say nothing is impossible, but I do nothing every day.

jkirkerx31-Oct-12 16:37