Database

18-Oct-12 11:50

Hi
I'd like to copy a couple of tables with all field information (primary keys, constraints etc) from SQL server 2008 at work to a pc at home running same SQL server.

I dont have any sort of link to the work server from home, so I need to somehow export the tables and save them on a flashdisk, then import them to the other database at home.

How can I do this?

Tks

Richard

R. Giskard Reventlov18-Oct-12 12:22

R. Giskard Reventlov

18-Oct-12 12:22

Easiest way is to right-click the table and then select 'Script Table As', 'Create To...' and then select whichever is the easiest way for you to accomplish your task.

You can then run that script on another database.

You can also use the Database Export Wizard in VS to accomplish a similar task and it will, optionally, get the table data as well. This is found on the Server Explorer tab. Right click on a db connection and select 'Publish to provider...' and follow the prompts.

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum

me, me, me

Richard.Berry10018-Oct-12 12:40

18-Oct-12 12:40

Thank Marc

Tried the 'Script Table' .. 'Create To' and this looks good..

I would like the data as well though, when you refer to VS do you mean Visual Studio?

I looked at Database Explorer in VS, and tried to add a connection, using SQL Authentication, browsed to (mmmm what I think is the path to the database) and get error You dont have permission...

Am I on the right track here?

How can I tell from SQL management Studio what the path is to the actual database ( I battled with the SQL server installation, and I think I have more than one instance installed)

R. Giskard Reventlov18-Oct-12 13:14

R. Giskard Reventlov

18-Oct-12 13:14

It's 'Mark' spelt the correct way: with a 'K'.

Yes, Visual Studio or you can download a stand-alon version from here[^].

Try using Windows Authentication.

To get the path to a database right-click on the database and select 'Properties', then select 'Files' in the dialog.

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum

me, me, me

Richard.Berry10018-Oct-12 14:10

Create small db, then use backup

18-Oct-12 14:10

Sorry Mark - Dunno where I got the 'c' from.

Using Visual Studio, tried Windows, and SQL authentication???

If I open SQL management studio, I connect using sa with no password, but same does not happen with VS????

What is confusing is that if I click Properties on the top level item in the SQL Server Management studio, the path shows as below.

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL

Yet I have found two two .mdf database files both of which show in management studio
a) C:\1\SQL2008\simmstock.mdf
b) C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\vektron.mdf

I know I battled with clearing the sa password, so not sure if I am browsing to the correct databse file.

How can I check the actual database path specific to the vektron database from management studio, since if you right click the database, there is no path shown (You only see a path if you click properties on very top of the tree (Server) and not the individual databases under the databases folder?

David Mujica19-Oct-12 6:21

David Mujica

19-Oct-12 6:21

How about this ...

On your work server,
1) create a DB
2) Issue some select into commands to copy the tables from <maindb> to <smalldb>
3) Do a full backup of small DB and copy that BAK file to a Flashdisk
4) Restore the DB from the flashdisk to your home PC.

You will get both the schema and the data in one neat package and you can easily make this a very repeatable process to bring "fresh" copies home.

Just a thought.
Rose | [Rose]

Re: Create small db, then use backup

fjdiewornncalwe19-Oct-12 6:43

fjdiewornncalwe

19-Oct-12 6:43

That gets my 5.

I wasn't, now I am, then I won't be anymore.

Re: Create small db, then use backup

Richard.Berry10025-Oct-12 5:55

Re: 10g to 11g migration.

25-Oct-12 5:55

Hello David

Thanks very much for the suggestion

The full DB was not as large as I though it would be so just used a full backup, took that home and restored it - all perfect!

Thanks

Richard

10g to 11g migration.

vanikanc17-Oct-12 7:44

vanikanc

17-Oct-12 7:44

Hi,

We just migrated from Oracle 10g to 11g. And one the report started failing after this.

One of the reports is not working with the error - Oracle Error: ORA-00918: column ambiguously defined in 11g.

It is failing at this line, '"'||mo_oaa.NAMEX||mo_oaa.NAMEX2||'"' "NAME",

Eddy Vluggen17-Oct-12 9:31

Eddy Vluggen

17-Oct-12 9:31

Probably the quotes. Remove those and try your query again.

Bastard Programmer from Hell Suspicious | :suss:

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

How to access window ?

nghia09t317-Oct-12 5:27

nghia09t3

17-Oct-12 5:27

I am coding the project, which access to the Window, exactly is HDD(NTFS, FAT32), help me solve it!!!, thanksssss Big Grin | :-D

Eddy Vluggen17-Oct-12 9:32

Eddy Vluggen

17-Oct-12 9:32

Your question does not make any sense to me. Can you describe the problem a bit more?

Bastard Programmer from Hell Suspicious | :suss:

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

nghia09t317-Oct-12 12:47

nghia09t3

17-Oct-12 12:47

Hi Eddy Vluggen, i think my English is not good to express my purpose, hjhj,
and my project makes a "Program" which can show properties of
hardware, example type(NTFS/FAT32), used space,..

i am coding by C++, can you give to me the documents about this, thanks so much!!!!

Richard Andrew x6417-Oct-12 14:06

Richard Andrew x64

17-Oct-12 14:06

You can use WMI[^] to query system properties.

The difficult we do right away...
...the impossible takes slightly longer.

nghia09t317-Oct-12 14:29

nghia09t3

17-Oct-12 14:29

thanksssss Richard, Have a nice day!!!!!

Database to C++

shiftwik16-Oct-12 16:21

shiftwik

16-Oct-12 16:21

hey, noob here

I'm reading some database queries into a structure in C++
The database class I'm using MFC CDatabase/CRecordset reads all the data as type strings.

So my questions are:

What is the best practice for storing the fields? should I convert them to their appropriate data type as I read them from the data base,(using CODBCFieldInfo) to determine the datatype?

or is their a class that exists that already does converts the query to a structure automatically in C++?

what is the preferred method for handling datatypes for ma database?

Re: Database to C++

Jochen Arndt16-Oct-12 21:21

Jochen Arndt

16-Oct-12 21:21

You may use the CRecordset member function GetFieldValue()[^]. There are different overloaded versions of this function which retrieve the values as strings or variants. Use a function which retrieves a variant to get values as C types that correspond to the type stored in the database.

subquery using 1 table

john john mackey16-Oct-12 7:26

john john mackey

16-Oct-12 7:26

i'm not sure if this is a Circular Reference problem or not.

i have the following SQL query

SQL

SELECT tb1.SomeField,
(SELECT tb2.AccountName
FROM tblAccountInfo tb2
WHERE tb2.CustomerID = tb1.CustomerID) AS strAccountName
FROM tblAccountInfo tb1
WHERE tb1.AccountID = 123456

Since i'm using the same table in the subquery, is this a problem?

If it is a circular problem, how could i correct this?

Thanks,
JJ

fjdiewornncalwe16-Oct-12 8:01

fjdiewornncalwe

16-Oct-12 8:01

Because you are using the same column and table in the subquery, then you simply make the subquery redundant.

SQL

SELECT SomeField, AccountName FROM AccountInfo WHERE AccountID=123456

will get you the accurate result.
The issue in your query is that you may have more than 1 record in the accountInfo table with a given CustomerID so you will throw an error in that the subquery can have multiple results when it can only have one. If you actually want more than one result then you could do something like this, but I'm not entirely sure the inner join will be valid like this. Something you can try, though.

SQL

SELECT t1.SomeField,
       t2.AccountName
  FROM tblAccountInfo t1
       INNER JOIN tblAcocuntInfo t2 ON t2.CustomerID = t1.CustomerID
 WHERE t1.AccountID = 123456

I wasn't, now I am, then I won't be anymore.

john john mackey16-Oct-12 9:50

john john mackey

16-Oct-12 9:50

I think i need the construct i initially posed, but there may be the case where the result may return more than one result - in which case i could handle with MAX() or a simple aggregate.

When i first call tblAccountInfo with AccountID, it gives me 1 record in tblAccountInfo with a CustomerID.

When i call tblAccountInfo using CustomerID, it will return AccountName, different than if I used AccountID, but I need both calls to retrieve different sets of data from same table.

I need first call to tblAccountInfo to get the CustomerID to use in the second call. Sigh | :sigh:

Michael Potter16-Oct-12 10:38

Michael Potter

16-Oct-12 10:38

I have to agree with Marcus. The correlated subquery is redundant. I can't imagine any structure and/or data that would make this subquery useful.

tb2.CustomerID = tb1.CustomerID will always return both columns from the same row. If CustomerID is duplicated within the table, then it will throw an error.

It also seems strange that AccountName is not dependant upon AccountId as thier names imply. Are you sure your structure is designed correctly?

Thou mewling ill-breeding pignut!

AspDotNetDev16-Oct-12 11:25

AspDotNetDev

16-Oct-12 11:25

To answer your question, there will be no "circular problem". If you run this query, you should see that it works fine. What allows you to have the same table multiple times in a single query is aliasing, which you are doing (you are aliasing tblAccountInfo as tb1 and tb2).

Like others have mentioned, your query doesn't make sense as is. If, however, there can be multiple records with the same CustomerID, this would make sense (notice the TOP 1):

SQL

SELECT
    tb1.SomeField,
    (
        SELECT TOP 1
            tb2.AccountName
        FROM tblAccountInfo AS tb2
        WHERE
            tb2.CustomerID = tb1.CustomerID
    ) AS strAccountName
FROM tblAccountInfo AS tb1
WHERE
    tb1.AccountID = 123456

But then, you also mentioned an aggregate function (such as MAX) in a reply above, which would accomplish much the same. If CustomerID is a unique field, then an aggregate or TOP 1 would be unnecessary, but then so would the subquery.

Aadhar Joshi16-Oct-12 20:28

Aadhar Joshi

16-Oct-12 20:28

First thing is u should avoid sub query as a column and make a inner join and take a column from sub table as it improves your performance when u fire your query with large data.

Second is it works fine till sub query produces single record for parameter, if there is more than one row returned by sub query, it throws errors.

Third for same u can use distinct clause in sub query.