|
I'm not a DBA, but Yes, a weekly full backup with differential daily backup is a good start. Also look into log backups maybe every hour or so. In order to do partial database backups, I think the db has to be structured into filegroups; you then can choose not to backup certain filegroups.
Also, some of the commercial backup software out there have suggestions for SQL backup.
|
|
|
|
|
Hi
I'm trying to write some data to an SQL table. I'm using MSDE.
The SQL table has mostly real (4byte) columns.
according to CLS Single.NaN is a leagle type.
For some reason I can't insert NaN values to the table records.
I'm using DataSets for the transfer and working with C#, Visual2003.
Yes, I DO need to save and restore both numbers and NaN values.
And yes, I know there are tons of work-arounds to simulate this but I really need to do this simple task.
Anyone ?
Gilad.
|
|
|
|
|
I have two tables that I am trying to read using OleDB (tableA and tableB) the primary keys for tableA are: col1, col2, col3. tableB has col1, col2, col3, col4, and col5 where col1, col2, and col3 are foreign keys. Because of large number I instances I cannot read all the instances at once. The reason is that I get cache Exception error and Db2 admin does not want to increase the size of db2 cache. I used two nested loops in order to create and execute following sql statements
String SQLSt = "SELECT tableA.col2, tableA.col3, tableA.col4, tableB.col2, tableB.col3, tableB.col4, tableB.col5, TableB.Col6
From tableA inner join tableB
On (tableA.col1 = tableB.col1 and tableA.col2 = tableB.col2 and tableA.col3=tableB.col3 AND tableA.col4 = tableB.col4) where tableA.col1 = 55 order by tableA.col2, tableA.col3, tableA.col4, tableb.col5, TableB.col6 FETCH FIRST " + 2000 + " ROW ONLY";
I use the above statement outside of the loop to read the first 2000 record.
I used following statement in a outer loop to read the following records in chunk of 2000.
SQlSt = "SELECT tableA.col2, tableA.col3, tableA.col4, tableB.col2, tableB.col3, tableB.col4, tableB.col5, TableB.col6
From tableA inner Join tableB
On (tableA.col1 = tableB.col1 and tableA.col2 = tableb.col2 and tableA.col3=tableB.col3 AND tableA.col4 = tableB.col4) where tableA.col1 = 55 and tableA.col2 >= previous_value and tableA.col3 >= previous_value and tableA.col4 >= previous_value and tableb.col5 = previous_value And TableB.col6 >= Previous_value
order by tableA.col2, tableA.col3, tableA.col4, tableB.col5, TableB.col6 FETCH FIRST " + 2000 + " ROW ONLY”;
The inner loop would read one record at a time and store the information in a memory cache.
The number of records that is read is much less than actual records for instances where col1 = 55. That is there are 45000 records and I read only 29000. If I do FECH FIRST 100 row only
Then I get about 20000 records.
1. Is there a way that I can specify in the sql statement to read record I to record I+2000?
I would appreciate any solution to this problem.
Thanks
The Ole command is:
OleDbCommand cmdIndx = new OleDbCommand();
cmdIndx.Connection = new OleDbConnection( ConfigurationSettings.AppSettings["DBConn"]);
OleDbDataReader rdrIndx;
cmdIndx.CommandText = strSQL;
cmdIndx.Connection.Open();
rdrIndx = cmdIndx.ExecuteReader();
rdrIndx.Read()
|
|
|
|
|
Hi there,
I've got the following two tables:
<br />
CREATE TABLE [dbo].[Countries] (<br />
[ID] [int] IDENTITY (1, 1) NOT NULL <br />
) ON [PRIMARY]<br />
GO<br />
ALTER TABLE [dbo].[Countries] WITH NOCHECK ADD <br />
CONSTRAINT [PK_Countries] PRIMARY KEY CLUSTERED <br />
(<br />
[ID]<br />
) ON [PRIMARY] <br />
and...
<br />
CREATE TABLE [dbo].[Cities] (<br />
[ID] [int] IDENTITY (1, 1) NOT NULL ,<br />
[CountryID] [int] NOT NULL <br />
) ON [PRIMARY]<br />
GO<br />
<br />
ALTER TABLE [dbo].[Cities] WITH NOCHECK ADD <br />
CONSTRAINT [PK_Cities] PRIMARY KEY CLUSTERED <br />
(<br />
[ID]<br />
) ON [PRIMARY] <br />
Now I'd like to establish a one to many relationship between them. I guess I know how to do that in the enterprise manager. My questions are as follows:
If I have a relationship between those tables:
- how will I go upon deleting a country record? do I necessarily have to do that within a transaction?
- shall I use the "Force relationship for INSERT and UPDATE(s)?" (roughly translated from the enterprise manager)
- shall I use the CASCADE options?
- and, If I use those options, how will they affect inserting, updating and deleting.
I know that my questions might be a bit vague. I've been trying to google for a primer on this but couldn't come up with something really helpful. I'd appreciated anyone shedding some light here. Thanks in advance!
/matthias
I love deadlines. I like the whooshing sound they make as they fly by.
[Douglas Adams]
|
|
|
|
|
matthias s. wrote: how will I go upon deleting a country record? do I necessarily have to do that within a transaction?
I can't answer this one  .
matthias s. wrote: shall I use the "Force relationship for INSERT and UPDATE(s)?" (roughly translated from the enterprise manager)
Well, you need a country to exist or not? if you do need the country to exist then...
matthias s. wrote: shall I use the CASCADE options?
Most certainly, countries won't be disappearing anytime soon (I hope  ) but in terms of programming, how important is it that you delete 50 cities if you try to delete a country? I think it's probably more important that you keep those cities. Imagine 400 records related to those cities being deleted as well .
matthias s. wrote: and, If I use those options, how will they affect inserting, updating and deleting.
Well, using enforcement of relationship will ensure inserts and updates have valid values for countryID. Deleting a country can have a serious impact on the infromation, as I stated above, is it ok for you to delete 50 cities because you delete a country? is that a good idea? you need to ask that yourself.
I think it's good practice to define relationships (the most important being enforce relationship for inserts and updates) because this makes programming easier. Cascade Delete... be very careful with this one, think if it's appropiate or not to remove information with this. Cascade Update doesn't affect much, specially if you use identity fields or GUID keys, you won't be needing this.
I just hope I don't make it more confusing  .
daniero
-- modified at 19:19 Tuesday 8th November, 2005
|
|
|
|
|
hello,
i am very new to VC++.Net. i am getting lots of bugs while working with
MsAccess Database. Here is my code fragment. i used the Connection
object from toolbox. so the connection is ok. i am able to retrieve the
data. But i failed to modify (inserting, updating and deleting).
DataSet *ds;
OleDb::OleDbDataAdapter* adapter=new OleDb::OleDbDataAdapter("select
prt_text,cut from spool",oleDbConnection1);
oleDbConnection1->Open();
adapter->Fill(ds,"spool");
DataRow *dr=ds->Tables->Item[0]->Rows->get_Item(1);
ds->Tables->Item[0]->Rows->Remove(dr);
ds->AcceptChanges();
adapter->Update(ds,"spool");
oleDbConnection1->Close();
how do i delete a record, update a record and add a record. plz help
me.
Thank u in advance.
regards,
basheer.
|
|
|
|
|
Hello everyone, i have a very strange problem when i using the full-text searching engin provided by SQL Server to search the web site.
If the keyword is directly after the tag , then there will be no matched result return. But if i change the to , then the correct result will be returned by the server. I spent whole day to solve this problem, but still get nothing.
Why this happend?
Thanks a lot!!
|
|
|
|
|
Hallo
I have 3 Tables
<br />
1st table with idfrist - descriptionfirst<br />
2nd table with idsecond - descriptionfirst<br />
3rd table with id - idfirst - idsecond - value<br />
Now i'd like to built a transformed tabel:
colheader with all items from idfirst
rowheader with all item from idsecond
values as sum from 3rd tables value (cols are( tofirst - tosecond - value)
The transformation works fine for all values entered.
But if one col from the colheader isn't used in the 3rd table,
there is no output column for this field defined in the first table ?!?
here si what is already have done with 2 tables
<br />
TRANSFORM Sum(third.value) three<br />
SELECT second.tofirst<br />
FROM third RIGHT JOIN second ON third.idsecond = second.tosecond<br />
WHERE (((third.tofirst) Like "*"))<br />
GROUP BY third.tofirst<br />
PIVOT second.idsecond;<br />
thx for helping
Tim
|
|
|
|
|
Got to this link and register free for
training your self for VS2005
https://www.microsoftelearning.com/visualstudio2005/
or
http://www.financialexpress.com/latest_full_story.php?content_id=107645
Microsoft dont give anything free except training
Vikas Amin
Embin Technology
Bombay
vikas.amin@embin.com
|
|
|
|
|
Must you spam every bloody forum multiple times?
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
-- modified at 21:38 Saturday 5th November, 2005
|
|
|
|
|
|
i am trying to get the data about database from a database(msql/access) using schemas(oledb)but the result is i am unable to get all information like column datatypes big/long/var-Binary and if a column is of type long then weather it is a autotype or simple long etc
in simple i wana get all information of the database. Suggest me a way
Know it share it that is correct knowledge (...kittu)
-- modified at 3:26 Saturday 5th November, 2005
|
|
|
|
|
Hi, take a look at ADO.NET 2.0. In .NET 2.0 it is much easier to get schemas.
'A programmer is just a tool which converts caffeine into code'
|
|
|
|
|
I am Using VS C# 2005 But unable to get all meta dadata info
/************************************************
try
{
ref_con.Open();
DataTable dt1 = ref_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
_dataset.Tables.Add(dt1);
DataTable dt2 = ref_con.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, new object[] { });
_dataset.Tables.Add(dt2);
DataTable dt3 = ref_con.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, new object[] { });
_dataset.Tables.Add(dt3);
DataTable dt4 = ref_con.GetOleDbSchemaTable(OleDbSchemaGuid.Check_Constraints, new object[] { });
_dataset.Tables.Add(dt4);
DataTable dt5 = ref_con.GetOleDbSchemaTable(OleDbSchemaGuid.Provider_Types, new object[] { });
_dataset.Tables.Add(dt5);
DataTable dt6 = ref_con.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { });
_dataset.Tables.Add(dt6);
DataTable dt7 = ref_con.GetOleDbSchemaTable(OleDbSchemaGuid.Constraint_Column_Usage, new object[] { });
_dataset.Tables.Add(dt7);
DataTable dt8 = ref_con.GetOleDbSchemaTable(OleDbSchemaGuid.Indexes, new object[] { });
_dataset.Tables.Add(dt8);
_dbtables.GetTableList(dt1, dt2,_dataset);
_dbcolumns.GetColumnList(dt1, dt5, dt6, _dataset);
results = true;
}
catch (Exception excp)
{
System.Windows.Forms.MessageBox.Show(excp.Message,"Failed To Get MetaData",
System.Windows.Forms.MessageBoxButtons.OK,System.Windows.Forms.MessageBoxIcon.Error);
}
finally
{
if (!ref_con.State.Equals(ConnectionState.Closed))
ref_con.Close();
}
but still unable to get all info
try this using a table containing columns of AutoNumber,long
and see the datatable value-----Provider_Types.
there same number value for some(binary) data types
************************************************/
Know it share it that is correct knowledge (...kittu)
-- modified at 21:34 Saturday 5th November, 2005
|
|
|
|
|
I am using MSDE on my computer. I have application that uses SQL. The application and MSDE are on the same computer. My connecting string follows:
this.cnKennel.ConnectionString = "Data Source=(local)\\NetSDK;packet size=4096;integrated security=SSPI" + ";persist security info=False;initial catalog=kennel";
Because I will be installing this application on many computers I would like not to specify the computer name/server name. Can someone tell me how I can do this.
|
|
|
|
|
I guess I don't clearly understand the question.
If the MSDE will always be on the same computer as the application, then your current connection string will always work (provided MSDE installed with the instance name NetSDK).If MSDE is installed without an instancename, then just DataSource = (local) will always work on the same box.
If the MSDE will be on only one of many computers, then you have no choice but to specify the name of the server (or its IP address if using TCPIP network library) and the instance name (if installed as an instance).
If there will be multiple clients, you likely should read the server name part of the connection string from a configuration file for your app, so it can be changed without recompling.
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
|
|
|
|
|
Thank you much, that was it!!!
|
|
|
|
|
Hello
I want to get data from MSSql sample database: pubs
I registered it in ODBC as sqlMY
(i use dialogWindow, without database support)
I do:
CDatabase *dat;
CRecordset *rec;
dat=new CDatabase;
dat->Open( "sqlMY", FALSE, FALSE, "ODBC;", TRUE );
new CRecordset(dat);
rec->Open(AFX_DB_USE_DEFAULT_TYPE, "select * from authors", CRecordset::none
);
And it shows error messege in rec->Open(...) , error is about SQLFetchScroll/SQLExtendedFetch
1. What i do wrong?
2. How can i access to my row data in CRecordSet (shouldi do CMyRecordSet() derived from CRecordSet and add there my data column as variables)?
thanks
Best Greetings
Michal
|
|
|
|
|
ok, i found, but....
<quote>virtual BOOL Open( UINT nOpenType = AFX_DB_USE_DEFAULT_TYPE, LPCTSTR lpszSQL = NULL, DWORD dwOptions = none );
(...)
lpszSQL
A string pointer containing one of the following:
A NULL pointer.
The name of a table.
An SQL SELECT statement (optionally with an SQL WHERE or ORDER BY clause).
A CALL statement specifying the name of a predefined query (stored procedure). Be careful that you do not insert whitespace between the curly brace and the CALL keyword.
even if i do
rec->Open(AFX_DB_USE_DEFAULT_TYPE, "authors",CRecordset::none );
it still have error
df
|
|
|
|
|
I have this provedure that it long so i have pasted the first and last part of the procedure.
Alter PROCEDURE outputexport
@Key varchar(20) = null
,@export int = 1
AS
BEGIN
CREATE TABLE test
/*Create the table for data*/
.
.
.
SELECT * FROM test
if @export = 1
begin
exec filetransferto 1, '"G:/export"', 'output', 'txt', 1, 0, @TableName='test'
exec filetransferto 1, '"G:/export"', 'output', 'txt', 1, 0, @TableName='test'
end
else select * from test
drop table #QRMDump
END
GO
I am trying to put this in such as that i can run from different server but the file will go to the same location that shown in('"G:/export"')
I am trying to declare @cmd that will do this
declare @cmd varchar(128)
set @cmd = (select @@ServerName) + '"G:/export"'
and calling the
exec filetransferto 1, @cmd, 'output', 'txt', 1, 0, @TableName='test'
but it give invalid file path error.
hope someone can give me answer!!
thanks
|
|
|
|
|
1. @@Servername is always going to give you the name of the server on which the SQL Server resides, regardless of which network client is used to execute the sp.
2. "G:/Export" always refers to a drive that is local to the SQL server (either a physical drive letter or a mapped drive). Prifixing it with the servername acomplishes nothing, since as the error messge states, that is an invalid way to specify a path.
3. You might be able to specify a UNC name: //SomeComputerName/somesharename/export,
but you would need to
a. share the destination drive (Say as Exports)
b. provide the sharing source comuter name either as a parametr value, or 'hard coded' into the SP. For example, if the destination computer is named "FileServer1",
and it shares the exports directory as Export_Share, then you would replace "G:\export" with "//FileServer1/Export_Share"
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
|
|
|
|
|
I have this provedure that it long so i have pasted the first and last part of the procedure.
Alter PROCEDURE outputexport
@Key varchar(20) = null
,@export int = 1
AS
BEGIN
CREATE TABLE test
/*Create the table for data*/
.
.
.
SELECT * FROM test
if @export = 1
begin
exec filetransferto 1, '"G:/export"', 'output', 'txt', 1, 0, @TableName='test'
exec filetransferto 1, '"G:/export"', 'output', 'txt', 1, 0, @TableName='test'
end
else select * from test
drop table #QRMDump
END
GO
I am trying to put this in such as that i can run from different server but the file will go to the same location that shown in('"G:/export"')
I am trying to declare @cmd that will do this
declare @cmd varchar(128)
set @cmd = (select @@ServerName) + '"G:/export"'
and calling the
exec filetransferto 1, @cmd, 'output', 'txt', 1, 0, @TableName='test'
but it give invalid file path error.
hope someone can give me answer!!
thanks
|
|
|
|
|
Hai,
I am having a table(Suppose Table1) with some data. After wards I have made some changes in the table so that the primary key changed and added some more columns(some references also). How I can write the script to modify the table with this change with out dropping the table. Because I need the data saved in the table. How I can write the upgradable script for that.
Please show me the right way. Thanks very much.
Thank You,
Rahul.
"Learn from Mistakes for suceess otherwise get buried with the Mistakes"
|
|
|
|
|
You could use ALTER TABLE[^]
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
May be it's late now... but in Enterprise Manager, when you enter in 'design table', after you make the changes, there's a button in the tool bar that says Save Change Scripts, that will save a script that'll do exactly what you are asking for  . So you can run this script in another server for example. The script alters the table, it doesn't drop and remake it.
daniero
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
