|
I don't recall there beeing any functionality in SQL server to backup a single table without planning for it first. It has to be planned for by creating filegroups and files.
- A filegroup is a collection of one or more files.
- A file is a file in the filesystem.
- On SQL Server you can control wich filegroup(s) a table is stored in.
- SQL Server allows you to control what file(s) or filegroup(s) to backup.
The prosess goes something like this:
1. Create a database filegroup containing one file in your database.
ALTER DATABASE MyDatabase<br />
ADD FILE TO FILEGROUP MyOtherFilegroup<br />
(<br />
NAME = MyOtherFile,<br />
FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\mydb_otherfile.ndf',<br />
SIZE = 5MB,<br />
MAXSIZE = 100MB,<br />
FILEGROWTH = 5MB<br />
)
2. Create (or move) the table on that database file.
CREATE TABLE Tablename(<your table definition here>) ON MyOtherFilegroup
or (to move a table from one filegroup to another) have a quick look in SQL Server Books Online. Select the index and lookup "filegroups, switching filegroup for table".
3. Take a backup of that specific file or filgroup.
The backup command will look something like either
BACKUP DATABASE MyDatabase FILE = 'MyOtherFile' TO 'MyBackupDevice'
or
BACKUP DATABASE MyDatabase FILEGROUP = 'MyOtherFilegroup' TO 'MyBackupDevice'
|
|
|
|
|
You could also use the BCP (Bulk Copy Program) utility along with an script to create or alter the table. (Automatically made for you with Visual Studio .NET).
John
|
|
|
|
|
Hello all,
I have what I hope is a quick question. In my Access 2000 database I have a Table called Candidates. In this table there is a Field called LName. How can I get the data in this field without opening the table?
This what I am trying to do. In the Main Form of my database, you can eneter a Last Name and hit the Command button and it will open the Candidate Table to the first occurence of the person with that last name. I'd like to write an autofill ability in the Main Form. This way when I type A into the space where you type in the last name, it will automatically fill the text to Aardvark for instance. And then if I hit another letter, like b and the string in there is "ab" it would return the first last name with Ab, for instance Aba.
Now, I know the logic behind how to do what I want, but I need the syntax.
How can I make a string equal the text in the last name field?
Would it be:
Dim lastName as String
lastName = [Candidate].[LName] ??
Any help you can offer me would be great.
And one more quick question.
Is there a way to access the characters in a string?
Can I type lastName[1] and get the first letter of the string?
Again thank you very much.
Nick
|
|
|
|
|
Open the table, read the last names into a listbox, then close the table. Not certain, but after you've done that I believe you can use the properties of a listbox to set it to autofill. If not, you can probably find example code for creating an autofill listbox.
>>>-----> MikeO
|
|
|
|
|
There are 4 fields in my database table. First fields is an integer, second is a string, third is a date field, and the fourth is a currency field.
I am using a combo box to select the field to be search, a text box for the value of the field to be searched, and a command button once clicked will invoke the filter.
Items in the combo includes the 4 fields names and another item "<everything>" which will disable the textbox for the filter value but once the command button is clicked, the recordset will be refreshed and set filter to nothing.
How to implement it. Please show example statements to filter using each of the above 4 fields. Also please include how to set the filter to nothing.
Thanks in advance
|
|
|
|
|
Hi. I'm trying to use ExecuteReader method in my code I'm getting an error. Could you please tell me what am I doing wrong? Here's the code:
SqlConnection tmpconn = new SqlConnection(this.connString);
tmpconn.Open();
string tmpsql = "select id from tbldeneme where username='a'";
SqlCommand tmpcmd = new SqlCommand(tmpsql,tmpconn);
Response.Write(tmpcmd.ExecuteScalar().ToString()); //<-- error line
Here's the error:
System.NullReferenceException: Object reference not set to an instance of an object.
|
|
|
|
|
kensai wrote:
Response.Write(tmpcmd.ExecuteScalar().ToString()); //<-- error line
You are getting a NULL returned from the database.
To be "safe" about using ExecuteScalar, assign it to a variable first, then put it out as string. Assuming your id field is integer...
//will return 0 if null record
int i = Convert.ToInt32(tmpcmd.ExecuteScalar());
if (i > 0)
Response.Write(i.ToString());
else
Response.Write("Record not found");
|
|
|
|
|
I need a perl script that I can run as a cron job.. The script needs to connect to an ftp server and grab a csv file and create a MySQL table from that file. I then need to know if its any different for a .txt file.
Any help is appreciated
|
|
|
|
|
This will be a long post but any insight will be greatly appreciated.
Process Description:
I have to write a C# method which will take as its parameter a System.Data.DataTable and then write this DataTable to a new dBASE IV table on disk.
Current Solution: (not a good one)
Here is my current solution in C# which is very very very slow (It takes 5+ minutes to output a 10MB .DBF on a dual 2.4 Zeon machine with 2GB of RAM running a RAID 5).
public void WriteTable(DataTable table)
{
string columnString="";
foreach(DataColumn column in table.Columns)
{
string type = "";
switch(column.DataType.ToString())
{
case "System.String":
type = "varchar(" + MaxLength(column) + ")";
break;
case "System.Int32":
type = "int";
break;
case "System.Double":
type = "double";
break;
default:
throw new ArgumentException("Data type not found.", column.DataType.ToString());
}
columnString += column.ColumnName + " " + type + ", ";
}
columnString = columnString.Substring(0, columnString.Length-2);
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + directory + ";Extended Properties=dBase IV";
sqlString = "CREATE TABLE " + name + " (" + columnString + ")";
connection = new OleDbConnection(connectionString);
command = new OleDbCommand(sqlString, connection);
command.Connection.Open();
command.ExecuteNonQuery();
command.Connection.Close();
command.Connection.Open();
int columnCount = table.Columns.Count;
foreach(DataRow row in table.Rows)
{
string valueString="";
for(int i=0; i < columnCount; i++)
{
switch(row[i].GetType().ToString())
{
case "System.String":
valueString += "'" + ((string)row[i]).Replace("'", "''") + "', ";
break;
case "System.Int32":
valueString += System.Convert.ToString((int)row[i]) + ", ";
break;
case "System.Double":
valueString += System.Convert.ToString((double)row[i]) + ", ";
break;
case "System.DBNull":
valueString += "'', ";
break;
default:
throw new ArgumentException("Data type not found.", row[i].GetType().ToString());
}
}
valueString = valueString.Substring(0, valueString.Length-2);
sqlString = "INSERT INTO " + name + " VALUES (" + valueString + ")";
command.CommandText = sqlString;
command.ExecuteNonQuery();
}
command.Connection.Close();
}
Questions:
- Any suggestions on a much better way to do this?
- Do you think it would be possible/better to try and see if I could use an OleDbDataSet/OleDbDataAdapter solution?
- Can anyone give me some insight as to why this solution is incredibly slow other than the fact that there is a very large amount of records to write out?
Any comments, suggestions, samples, etc. will be greatly appreciated.
Thanks
Mark Sanders
|
|
|
|
|
Mark Sanders wrote:
Do you think it would be possible/better to try and see if I could use an OleDbDataSet/OleDbDataAdapter solution?
No, probably this will be slower.
Mark Sanders wrote:
Can anyone give me some insight as to why this solution is incredibly slow other than the fact that there is a very large amount of records to write out?
Use parametrized queries: this will give you a huge performance boost. See the OleDbParameter class for examples.
Notice that using parametrized queries you won't need anymore to replace "dangerous" chars like the quotes.
This probably will eliminate the need for the switch on the type and all the string conversions, too.
For another tip, do not make massive string concatenations using the String class. This is slow, and will kill the performance of the garbage collector. Actually, this can lead to hard-to-find performance problems. Use the System.Text.StringBuilder instead.
My latest article: GBVB - Converting VB.NET code to C#
|
|
|
|
|
I have been trying to find some examples of setting up an INSERT into a .dbf using OleDbParameter class. I have been unsuccessful. Can you point me to some examples? I understand how to use parameters with SQL Server stored procedures but I seem to be missing something when it comes to using parameters for and Ole connection to a .dbf.
Here is what I have so far but it is not working.
string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\;Extended Properties=dBase IV";
string sqlString = "INSERT INTO TestTabl (Column) VALUES (?)";
OleDbConnection connection = new OleDbConnection(connectionString);
OleDbCommand command = new OleDbCommand(sqlString, connection);
command.Parameters.Add("@Column", OleDbType.VarChar);
command.Parameters["@Column"].Value = "It Worked";
command.Connection.Open();
command.ExecuteNonQuery();
command.Connection.Close();
Thanks
Mark Sanders
|
|
|
|
|
I adjusted my code to use parameterized queries but have not gained a significant enough performance boost. After some performance tests I have found that my OleDbCommand.ExecuteNonQuery() takes an average of 0.004 seconds to execute (with the max occurance being 0.015 seconds). With my current mid-range test I do this for 145,160 records for a total of 9.85 minutes! After some more research it appears the general concensus is that ADO.NET is unable to do this type of operation any faster.
So, my new question is...
I have no choice in the requirements. I must take a DataTable and write it to disk as a dBASE IV table. Does anyone have any ideas on how to do this in the fastest possible way?
Mark Sanders
|
|
|
|
|
When I work with DBF files I always use microsoft's ODBC data provider classes, ODBC Adapters etc. So far it works fast and effective.
You can dowload it somewhere from the MSDN
Good luck.
"I only Play for Sport"
Lara H. Croft
modified 17-Oct-21 21:01pm.
|
|
|
|
|
Working with a table containing 2 million records I want to get the next SeqNum for each group and display an order field.
Example:
Table:
ID Field1 Field2 DateEntered SeqNum
1 tom a 12/12/00 1
2 tom b 1/1/01 2
3 jack d 2/2/01 1
4 tom d 1/2/02 null
5 me a 1/2/02 1
6 jack a 2/2/02 null
7 me d 3/3/03 null
8 me a 3/3/03 null
Desired Output:
ID Field1 Field2 DateEntered SeqNum Order
1 tom a 12/12/00 1 1
2 tom b 1/1/01 2 2
3 jack d 2/2/01 1 3
4 tom d 1/2/02 3 4
5 me a 1/2/02 1 5
6 jack a 2/2/02 2 6
7 me d 3/3/03 2 7
8 me a 3/3/03 3 8
Could someone point me in the right direction on how to replace the null values with the appropriate next SeqNum and output the Order fields.
Because of the size of the table using a cursor is out of the question. Anyone know of a good way this can be done.
Thanks,
Jason W.
|
|
|
|
|
Depends on the database you are using. If it is SQL Server or Sybase, consider creating an identity column for automatic numbering.
|
|
|
|
|
Jason Weibel wrote:
Could someone point me in the right direction on how to replace the null values with the appropriate next SeqNum and output the Order fields.
Because of the size of the table using a cursor is out of the question. Anyone know of a good way this can be done.
Jason, long time no see, not sure if you got this fixed or not but you should consider using a CASE statement in the stored procedure where you can replace a null value with an identity value for each record.
-Nick Parker
|
|
|
|
|
Man I thought you fell off the face of the planet. What’s up? I found a work around for the problem, it isn’t pretty but it works.
Jason W.
|
|
|
|
|
Jason Weibel wrote:
Man I thought you fell off the face of the planet. What’s up? I found a work around for the problem, it isn’t pretty but it works.
Did you get the email I sent you a few days ago?
-Nick Parker
|
|
|
|
|
Hi all,
I am constructing a oledbconnection using the connection string which obtained through data link properties dialog.
After getting a connection string we can easily create oledbconnection irrespective of the database. My problem is I should know the oledbconnection is with an oracle database.
How can I know? The only way I know is checking the database provider in the connection string that it is from oracle. But what is the problem with this, this is always possible only if we know all the oracle oledb providers. That also will fail, if they change the version in future. Without using the provider in the connection string, is there any other way to determine whether the connection is with an oracle database?
Thanks.
|
|
|
|
|
Hi All,
I have the following scenario:
in a Bound windows form, I have a master-detail relationship, the first part of the form is the Master(represented by text and combo boxes) and retrives it's Data from a single SQL data table, The Details Section is represented by a DataGrid, and retrives its Data from Multi Tables Sql Query.
I wand to Add/ Insert/ Update and Delete from this Related sections at once, but still facing some problems affecting the Details Section, the modifications are done on the Master only.
The relation between them is build in the run time.
plz, if you have any Idea how to solve such a problem, that will be appreciated.
Thanx All.
I.M.A
|
|
|
|
|
HI,
I created tables in MS Access.. and am quite familiar in accessing the tables from the ASP page for my site. I then saw somethin like relationships.. and started playing around it. I got the followinf stuff....
Employee (table)
- EmpId
- DeptId
- Name
Department (table)
- DeptId
- DeptHead
- Description
The Dept head at the second table is linked with the EmpId in the first table by means of the relationships....
NOW, will it be possible for me to access the first table data, by querying the second table. (In access screen I was able to see the first table data in the second table by clicking + on the field which joins them)
Please help
I was born intelligent Education ruined me!.
|
|
|
|
|
Hi,
> The Dept head at the second table is linked with the EmpId in the first table by means of the relationships....
From your table definitions, I think DeptId is a foreign key that links the Employee to a Department.
> NOW, will it be possible for me to access the first table data, by querying the second table.
It depends of what you want to do. What do you mean by "access the first table data" ? Do you want to know the employees for a specific departement ?
Just a DB design tip :
- don't use prefix on your field names : Employee.Id or Department.Id are ok, Employee.EmpId or Department.DeptId/DeptHead is a redondance
JM. Molina
Web: http://goa.ifrance.com
|
|
|
|
|
how to use ado.net access paradox database(tables)?
thanks.
|
|
|
|
|
i have a table in my Sql Server database which contains data in a hierarchical manner is as below
table-1
child_id parent_id
1 2
2 3
3 4
4 5
5 6
11 5
17 4
Now i want to retrive all the children,grand children,grand-grand children of parent_id=5.How i can achive it through SQL query .Please help me its very very urgent
Thank u
|
|
|
|
|
In Standard-SQL you got to program every level
i.e.
SELECT child_id, parent_id
FROM table
WHERE parent_id=5
SELECT child_id, parent_id
FROM table
WHERE parent_id IN (SELECT child_id FROM table WHERE parent_id=5)
etc.
you can find examples when looking for 'partlists'.
however, if you use Oracle, you can use the CONNECT BY clause:
SELECT child_id, parent_id
FROM table
CONNECT BY parent_id = child_id
|
|
|
|