Database

Re: How to convert Sql server 2000 database backup file to Sql server 2005 Express Edition .mdf file.

29-Jul-07 8:56

You should be able to attach the SQL 2000 database file to SQL 2005. Have you tried attaching it?

"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

Mike Dimmick30-Jul-07 12:15

Mike Dimmick

30-Jul-07 12:15

Yes, you can restore a SQL Server 2000 database backup to a SQL Server 2005 server. You simply use the normal Restore features - use Management Studio or the RESTORE DATABASE command.

The compatibility level for the database will be 80 (i.e. SQL Server 2000 compatible) by default. If you want to take full advantage of SQL Server 2005 features, you will need to use sp_dbcmptlevel to set the compatibility level to 90.

Stability. What an interesting concept. -- Chris Maunder

Re: How to convert Sql server 2000 database backup file to Sql server 2005 Express Edition .mdf file.

Paramhans Dubey31-Jul-07 2:09

Paramhans Dubey

31-Jul-07 2:09

hi friends,

As I have mentioned earlier I m using SQL Server 2005 Express Edition, So I don't have Management Studio.

I tried following restore command :

"

Restoring SQL Server 2000 Database *.bak backup file to SQL Server 2005 Express Edition *.mdf file<br /> RESTORE filelistonly<br /> FROM disk = 'C:\Documents and Settings\Administrator\Desktop\ImageDB.bak' RESTORE DATABASE image<br /> FROM disk = 'C:\Documents and Settings\Administrator\Desktop\ImageDB.bak' WITH move 'ImageDB' TO 'C:\Documents and Settings\Administrator\My Documents\Visual Studio 2005\Projects\WindowsApplication1\WindowsApplication1\Image.mdf',<br /> move 'Image_log' TO 'C:\Documents and Settings\Administrator\My Documents\Visual Studio 2005\Projects\WindowsApplication1\WindowsApplication1\Image.ldf'
"

But I am unable to find any mdf or ldf file in the directory "C:\Documents and Settings\Administrator\My Documents\Visual Studio 2005\Projects\WindowsApplication1\WindowsApplication1".
I also searched the directory ” C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data” but that was also of no use.

Regards,
Paramhans Dubey.

Compare fields from two different tables - vs.net & reporting services

snoop2i28-Jul-07 17:23

snoop2i

28-Jul-07 17:23

Hi everyone,

I'm designing a report which currently includes two tables: dataset1_datatable2 & dataset1_datatable4.
I need to make a very simple comparison between two fields from these two tables.
My goal is to change the background color of these fields if their values are identical. I know how to write an expression for fields which are on the same table, but I don't know how to compare fields which are on different tables (same dataset!).

If someone could post an example for such expression it will be great!

Thanks in advance!

connect to oracle database using visual c# express edition

cellardoor071628-Jul-07 3:21

cellardoor0716

28-Jul-07 3:21

Hi is it possible to connect to an oracle database using visual c# express edition? In the database explorer I click on the Add connection.. and there are only two choices: Microsoft Access Database File and Microsoft SQL Server Database File.

Can anyone help me with this?

cellardoor

Re: connect to oracle database using visual c# express edition

Colin Angus Mackay28-Jul-07 3:42

Re: connect to oracle database using visual c# express edition

28-Jul-07 3:42

cellardoor0716 wrote:
Hi is it possible to connect to an oracle database using visual c# express edition?

Yes.

cellardoor0716 wrote:
In the database explorer I click on the Add connection.. and there are only two choices: Microsoft Access Database File and Microsoft SQL Server Database File.

Never use this piece of crap. It only serves to reinforce bad habits.

cellardoor0716 wrote:
Can anyone help me with this?

Look at the classes in the System.Data.OracleClient namespace[^] specifically OracleConnection[^] and OracleCommand[^]

Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...

"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."

My website

Paul Conrad28-Jul-07 5:37

SQL 2005 Express, connecting to database

28-Jul-07 5:37

Colin Angus Mackay wrote:
Never use this piece of crap. It only serves to reinforce bad habits.

Well said, Colin Smile | :)

"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

#realJSOP27-Jul-07 23:04

#realJSOP

27-Jul-07 23:04

OS: XP Pro
IDE: VS2005
SQL Express 2005
IIS 5

I'm working with a "classic" asp site that is known to work when the site is on a remote server (that's running IIS6 and SQL Server 2005).

I've installed IIS5 and SQL Express 2005 on my development box because VS2005's internal server won't run classic asp pages (how stupid is that?).

I have IIS5 working, and a database created in SQL Express 2005 (using scripts that created the database on the working server), and I can even see it in Server Explorer pane in VS2005. However, when I try to browse a page that attempts a database connection, I get this error:

Microsoft OLE DB Provider for ODBC drivers (0x80004005)
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL server does not exist or access denied

The SQL server service is running.

I can connect to the desired database in VS2005 (and can see it in the Server Explorer pane in the IDE).

For the server name, I've tried the machine name ("earth2") and 127.0.0.1 - neither one appears to affect the outcome.

What could I be doing wrong?

"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"...the staggering layers of obscenity in your statement make it a work of art on so many levels." - Jason Jystad, 10/26/2001

Colin Angus Mackay28-Jul-07 3:40

28-Jul-07 3:40

You are trying to connect with a machine name or IP address. By default the SQL Server doesn't permit TCP/IP connections. You have to go into the SQL Server Configuration thing (sorry, I don't have it installed on this machine, and I can't remember exactly what it is called) and allow TCP/IP connections.

Alternatively it might work by specifying the SQL Server as "(local)" (no quotes, but with brackets) which will use shared memory (at least in .NET, I don't know about classic ASP)

#realJSOP28-Jul-07 5:51

#realJSOP

28-Jul-07 5:51

TCP/IP is turned on.

I'll try the local thing.

Mike Dimmick28-Jul-07 4:14

Mike Dimmick

28-Jul-07 4:14

SQL Server 2005 Express Edition does not install as the default instance but as an instance named SQLEXPRESS. Use (local)\SQLEXPRESS as the Server name.

Stability. What an interesting concept. -- Chris Maunder

Paul Conrad28-Jul-07 6:32

Will these two queries perform the same?

28-Jul-07 6:32

Have you used the Sql Server Configuration Manager like Colin suggested? On my test box, I've opened up the TCP/IP, Named Pipes, and the others. Not sure if this helps out.

John Simmons / outlaw programmer wrote:
I've tried the machine name ("earth2") and 127.0.0.1

How about the actual IP of the machine? Not sure about it working with the loopback...

"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

Joe Smith IX27-Jul-07 21:47

Joe Smith IX

27-Jul-07 21:47

Hi all,

Let say I have a table with 100+ columns and millions of rows, and I only need to gather all data in column A and B. Will the second query below perform faster, or no difference whatsoever?

1: SELECT * FROM MyTable
2: SELECT A,B From MyTable

Thanks for any pointers.

Colin Angus Mackay27-Jul-07 22:41

27-Jul-07 22:41

The second query will perform faster because it will use less bandwidth to get the results to the client. The first query will return a large amount of data that is not needed.

Let's say you have one million rows and each of your columns are integers (or 4 bytes on average) that means each row is 400 bytes (if you have 100 colums), which requires 400,000,000 bytes (+ some overhead for the protocol - Call it 400Mb) to get that data to the client.

If you get only the columns you want that is 8 bytes per row which is 8,000,000 for all the million rows (+ some overhead for the protocall - Call it 8Mb).

So, even if you are on a super-duper network 8Mb will transfer faster than 400Mb. And if you have lots of clients that is a lot of network bandwidth being saved.

Joe Smith IX2-Aug-07 3:47

Joe Smith IX

2-Aug-07 3:47

I can see that the second query is definitely faster if I run the queries in SQL Management Studio (I am using SQL Express 2005).

Now, the question is: if I run the query from the code (VC++) like the following pseudo-code:

CADORecordset pRs = CADORecordset(&theApp.m_pDb);
if(pRs.Open(sz, CADORecordset::openQuery))
{
  while(!pRs.IsEOF())
  {
    pRs.GetFieldValue("A", szA);
    pRs.GetFieldValue("B", szB);
    ... // do something with szA and szB
 
    pRs.MoveNext();
  }
}

Will the first query still use less bandwith? Isn't the data transferred during the GetFieldValue function, so the two queries perform exactly the same?

Colin Angus Mackay2-Aug-07 7:57

2-Aug-07 7:57

Joe Smith IX wrote:
Will the first query still use less bandwith? Isn't the data transferred during the GetFieldValue function, so the two queries perform exactly the same?

No, they wont. The data will still be transferred (because that's what you asked the database for) your application will just ignore the rest.

Mike Dimmick28-Jul-07 4:27

Mike Dimmick

28-Jul-07 4:27

Colin's answer is correct. I will also add that if you have an index on columns A and B (or containing columns A and B in the index), SQL Server will consider that a covering index for the query and only scan the index, rather than scanning the table itself.

Because the index occupies less space than the table itself, this will require less I/O and return the results faster.

Stability. What an interesting concept. -- Chris Maunder

Colin Angus Mackay28-Jul-07 7:17

28-Jul-07 7:17

Good point - I'd forgotten about that.

blakey40430-Jul-07 0:02

blakey404

30-Jul-07 0:02

thats the main point - if columns a and b are indexed you will notice a huge % difference in performance - the number of I/O's will reduce even more.

Colin Angus Mackay30-Jul-07 6:52

30-Jul-07 6:52

blakey404 wrote:
thats the main point

It's a point. I don't see how it is the main point. The OP asked about performance for a query. One aspect is indexes, another is returning just the information that is actually needed. Both equally valid, especially as there was no filtering (WHERE clause) involved.

blakey40430-Jul-07 21:46

blakey404

30-Jul-07 21:46

i would say its the main point because of how the indexes are stored. if the columns specified to be returned are both in a non-clustered index the data returned can be retrieved directly from that index, therefore not having to do x number of disk reads to return all the columns, as with a select *. try both in query analyzer and see the difference. returning index columns only from a non-clustered index is the fastest query possible, as it reduces logical reads.

Colin Angus Mackay30-Jul-07 21:50

30-Jul-07 21:50

blakey404 wrote:
returning index columns only from a non-clustered index is the fastest query possible, as it reduces logical reads.

I don't disagree with that. I'm just saying there are other ways that can help improve performance. Indexing is not the be all and end all of performance improvement.

blakey40430-Jul-07 22:26

blakey404

30-Jul-07 22:26

oh i completely agree, i was just thinking about in this particular example - where it is very possible the 2 columns required may be indexed.

Blush | :O

)

Paul Conrad29-Jul-07 3:34

29-Jul-07 3:34

Like Colin said about the network bandwidth, it will take less to only pull the fields that you need at the moment, and it is better for design and maintenance of the code to only pull what you need at that moment in the program. Another thing, do you need to pull the millions of rows at once?

"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon