Database

Small sql backup problem... [modified]

28-May-06 11:55

I presume it looks up the various sys* tables (e.g. sysobjects & syscolumns) to find out what is in the database and create a script based on that.

"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog

Stanciu Vlad28-May-06 3:00

Stanciu Vlad

28-May-06 3:00

I have a small c# application that connects to a sql server (msde, 2000 or 2005). At a certain point I scheduleld a database backup to a file like this :

BACKUP DATABASE myDatabase TO DISK = 'c:\test folder'

Nothing wrong if the server is on the computer that runs tha small application, but if the server is on a remote computer the file is saved on that one instead on the one that runs the application.
How can I save the file on the computer that acceses the sql server (na- how can I make the server interact upon backup-restore with an other computer over the network)?

protected internal static readonly ... and I wish the list could continue ...

Colin Angus Mackay28-May-06 4:04

28-May-06 4:04

Vlad Stanciu wrote:
Nothing wrong if the server is on the computer that runs tha small application, but if the server is on a remote computer the file is saved on that one instead on the one that runs the application.

That is correct because the command is running on the remote server so all disk access will be as seen from the SQL Server, not your local machine.

Stanciu Vlad28-May-06 4:11

Stanciu Vlad

28-May-06 4:11

So how can I connect to a remote server and backup a database localy?

protected internal static readonly ... and I wish the list could continue ...

Colin Angus Mackay28-May-06 4:15

28-May-06 4:15

You don't. You backup on the remote machine then copy the file across the network.

Stanciu Vlad29-May-06 1:29

Stanciu Vlad

29-May-06 1:29

Thanks for your answer!

Now I have another question, kind of offtopic... why didn't MS implemented the functionality to backup batabases to the terminals (remote pc's).
Security? I mean if you are able to backup a database you are able to querry it too, so there is noting to protect...

protected internal static readonly ... and I wish the list could continue ...

Colin Angus Mackay29-May-06 4:37

Enterprise Library - Data Access layer

29-May-06 4:37

Vlad Stanciu wrote:
Security? I mean if you are able to backup a database you are able to querry it too

Not necessarily. You can assign a user account the the db_backupoperator role which permits only backing up the database (assuming no other permissions are granted).

Storing the backups on the local machine only means that you have to provide additional security rights in order to get the backup off the machine in order to restore it elsewhere.

devvvy27-May-06 22:36

devvvy

27-May-06 22:36

One of our projects is using Enterprise Library for data access (i.e. Data Access block). I looked at the code and don't see what's the advantage of using Data Access Block...

But one big advantage I is, you don't need to "Close" connection.

Personally, I find NHibernate much more helpful in minimizing data access chores...

Norman Fung

Inserting a New Record in MSAccess

ADY00727-May-06 12:02

ADY007

27-May-06 12:02

Right Now I have this code:

Private Sub add()
Dim firstvalue As String = txtName.Text
Dim secondvalue As String = txtSurname.Text

Dim conn As OleDbConnection = New OleDbConnection(strConnection)
Dim cmd As OleDbCommand = New OleDbCommand
cmd.Connection = conn
cmd.CommandText = "INSERT Into tblMembers(Name, Surname) VALUES (?,?)"
cmd.Parameters.Add("Name", firstvalue)
cmd.Parameters.Add("Surname", secondvalue)
conn.Open()
cmd.ExecuteNonQuery()
conn.Close()
End Sub

But an error is occuring telling me this:

Exception Details: System.Data.OleDb.OleDbException: Operation must use an updateable query.

What do you thing this is?? Help me please!!

Adrian De Battista: .Net Programmer, Java Programmer and Web Designer.

grouped

Amarni27-May-06 1:50

Amarni

27-May-06 1:50

hi i want to make an select statment that contains some data, that is
SELECT Name,ID, Sname,Schools from student

imagine that this table have one student have the same ID and same name but i need the last one how can do that while there are no Primary keys .
can i use grouped by Name alone or i should make a grouped by name,ID,..

Amarni

Re: grouped

Colin Angus Mackay27-May-06 2:22

SQL Server Cursor - Quick Question

27-May-06 2:22

Amarni wrote:
imagine that this table have one student have the same ID and same name but ... there are no Primary keys

So what is ID if not the primary key?

Amarni wrote:
i need the last one

What do you mean by "the last one"? Databases contain sets of data there is no implied order of anything in the database. If you want to make something ordered then it has to be ordered on a column or columns. Which column defines the order?

Amarni wrote:
can i use grouped by Name alone or i should make a grouped by name,ID,..

GROUP BY will allow you to aggregate the other columns based on the column or columns you are GROUPing BY. That means you will no longer be able to access ID, Sname, Schools other than through an aggregation function such as MIN(), MAX(), SUM(), AVG() etc.

need help in ADO VC++

GANsJob26-May-06 18:56

GANsJob

26-May-06 18:56

I use a table named FDetails with 4 text fields and a datetime field named date in MSaccess
when insert values into table ising ado recordset obj I receive some syntax error
recordset->Open("INSERT INTO FDetails(name,path,duration,volume,date) VALUES('hello','location','duration','vol','12/12/2000')",connection.GetInterfacePtr(), ADODB::adOpenForwardOnly,ADODB::adLockReadOnly, ADODB::adCmdText);

any one know inserting date into table

Jawz-X26-May-06 10:12

Jawz-X

26-May-06 10:12

I have a question SQL Server cursor question

I have a table, “clients”. I want to create a cursor to select and iterate through each client in the database. Obviously, that will lock the “clients” table. Fine. But if I involve another table to do a filter (in the where clause, to reduce the result set) does that lock both tables and just the “Client” table?

Thanks in advance,
Frank
www.TheOpenSourceU.com

Jawz-X26-May-06 10:18

Jawz-X

26-May-06 10:18

Also, it is important to note that the table i'd be using is a constantly used table (it touches almost every thing)

Thanks again,
Frank
www.TheOpenSourceU.com

Eric Dahlvang26-May-06 11:12

Eric Dahlvang

26-May-06 11:12

Jawz-X wrote:
I want to create a cursor to select and iterate through each client in the database. Obviously, that will lock the “clients” table.

It will not obviously lock the clients table.

SQL Server Books Online:
Cursor Transaction Isolation Levels
The transaction locking behavior of a specific cursor is determined by combining the locking behaviors of the cursor concurrency setting, any locking hints specified in the cursor SELECT, and transaction isolation level options.

Microsoft® SQL Server™ 2000 supports these cursor transaction isolation levels:

Read Committed
SQL Server acquires a share lock while reading a row into a cursor but frees the lock immediately after reading the row. Because shared lock requests are blocked by an exclusive lock, a cursor is prevented from reading a row that another task has updated but not yet committed. Read committed is the default isolation level setting for both SQL Server and ODBC.

Read Uncommitted
SQL Server requests no locks while reading a row into a cursor and honors no exclusive locks. Cursors can be populated with values that have already been updated but not yet committed. The user is bypassing all of the locking transaction control mechanisms in SQL Server.

Repeatable Read or Serializable
SQL Server requests a shared lock on each row as it is read into the cursor as in READ COMMITTED, but if the cursor is opened within a transaction, the shared locks are held until the end of the transaction instead of being freed after the row is read. This has the same effect as specifying HOLDLOCK on a SELECT statement.

----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters

Jawz-X26-May-06 11:53

Jawz-X

26-May-06 11:53

Thank you for that information. So, the answer to my question is that, in fact, neither table will be locked.

So, I should probably know this, but please enlighten me and save me some research (I don't use cursors very often, I think I did one once). But why then are cursors to be considered a last resort? Is it simply the amount of resources that they use on the DBMS?

Thank you,
Frank
www.TheOpenSourceU.com

P.S. I like your quote.

Colin Angus Mackay26-May-06 21:20

Re: problem with sql server

26-May-06 21:20

Jawz-X wrote:
But why then are cursors to be considered a last resort?

Because they are increadibly slow. I once saw a question on this forum complaining that the code was taking too long (it was using cursors). I suggested a better query to use without cursors and the operation took around one second to complete.

Some things cannot be solved without cursors which is why they exist. But oftentimes the code can be re-written without cursors.

problem with sql server

lucky123456026-May-06 9:12

lucky1234560

26-May-06 9:12

i m not able to execute ado.net application with sql server
database .

i m getting error as login failed.

i want to use sql server with my asp.net application.as i didnt install sql server in my p.c i dont know user name and pwd.

plz help

Colin Angus Mackay26-May-06 11:31