Does No_TRUNCATE Command Work In SQL 7 ?
Feb 23, 2001
Hello,
I am planning to backup my log files to DLT and to disk on a regular basis. I was planning to issue a "backup log xxxxDB to device with NO_truncate" and then issue a "backup log xxxxxDB to DLT_device".
I have tried this but noticed that the size of file produced from the first command was greater than produced by the second command. It seems that the NO_truncate command is actually truncating the Log.
I am using SQL 7 and SP2 and truncate log on checkpoint option is switched off.
Can anyone shed light on this.
Thanks in advance.
Samir
View 1 Replies
ADVERTISEMENT
Aug 15, 2000
Backup Log ..... with no_truncate -- How to get it to work.
Applicable to SQL Server 7.0 Date 8/14/00 andrews_gary_w@solarturbines.com
Introduction to the "Backup Log
.with no_truncate" feature of SQL Server 7.0.
On occasion a database may become corrupt or a database file may be inaccessible. Ideally, in these cases you want to be able to restore and recover the data back to the current point in time.
This requires that the Transaction Log be backed up prior to the restore process beginning.
Microsoft has provided the "Backup Log
with no_truncate" feature for use in cases such as this. However, this feature works only under very specific conditions and thus is the topic of this document.
Background.
When a database is defined, it always has a primary file group. In some cases this may be the only file group defined for the database. The primary filegroup contains, at a minimum, the system tables for the database being defined. The systems tables hold metadata describing the database. Where no other filegroups have been defined, the primary file group will also contain the user defined tables/indexes.
Essential requirement
"Backup Log
with no_truncate" must be able to access the "metadata" located in the primary file group, or it will not work. This also means that this part of the database must not be corrupt or that the file containing this part of the database must not be inaccessible.
Specific conditions required for "Backup Log
.. with no_truncate" to work.
a. The database needs to be defined so that the user tables/indexes are contained in one or more file groups other than the primary filegroup.
b. Logging of changes to the database must be in effect.
c. A full backup must exist
d. Changes are made (logged) to the user database.
e. No non-logged operations must have been performed since the last full backup.
f. The primary filegroup file(s) must be available.
g. The primary filegroup data must not be corrupted. Or at least the portion needed by the backup operation must not be corrupted. I am not 100% sure about this issue.
h. The data in the filegroups other than primary may be corrupted or these files may be completely missing.
Process to restore/recover.
a. When problem detected (data file(s) inaccessible or database is corrupted), if primary filegroup is accessible, then perform the Backup Log
. with no_truncate.
b. Then perform the restore process using the "with replace" option for the database. This will get back any missing file(s) and recover you right up to when the last log backup was taken.
c.Post restore/recover process, your database will not be corrupt or missing any data file(s)
Bottom line
To achieve maximum recoverability, the following is recommended.
a. Define databases so that user data is not put in the primary file group.
i.e. Define not only a primary filegroup, but also one or more "other" filegroups.
b. Place user data in the "other" filegroup(s), NOT in the primary filegroup.
c. Place the primary filegroup on a disk drive separate from the "other" filegroup(s).
d. Place the log on a disk drive by itself.
Under the above scenario, at least three different disk drives would be needed to accommodate a single database.
Some sources recommend you put the primary file group and the transaction log on the same disk drive (mirrored). This would of course reduce the maximum number of disks needed to two. This topic, from my perspective, is another issue to be separately addressed and thus is not covered any further in this document.
Remember, even if the database files are isolated as recommended above:
a. Should the primary filegroup file(s) be inaccessible, then the Backup Log
. with no_truncate option will not work.
b. Should the primary filegroup file(s) be corrupt, then depending on where the corruption is determines whether or not the Backup Log
.. with no_truncate option will work.
View 2 Replies
View Related
Jun 19, 2001
In Sql 7.0 you cannot perform a log backup with NO_TRUNCATE when the primary db file is missing (ref. Q218739). I'm not updated with respect to Sql2000 and was wondering if this remains an issue in that version too? Anybody know?
(the solution being to have a separate DEFAULT file group and store the primary file on the same disk as the log).
View 1 Replies
View Related
Jan 28, 2007
Hello,
I have a sequense of sql commands in order to recursively update a table that has parents/childs
After I create a temporary table, I need to run an sql command that for some reason is not working. No errors, the command is actually excecuted, but I beieve the rowcount is 0 from the beggining
Here is the command:
Dim InsertConnection As Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection("Server=myServer;User ID=myUser;pwd=myPSW;Database=myDatabase")
Dim SqlInsertCommand As Data.SqlClient.SqlCommand = New Data.SqlClient.SqlCommand("while @@rowcount > 0 " _
& "begin INSERT INTO submenu" _
& uid & " (pageid,parentid) SELECT y.pageid , y.parentid FROM submenu" & uid _
& " i INNER JOIN page y ON y.ParentId = i.pageID LEFT OUTER JOIN subMenu" _
& uid & " i1 ON i1.pageId = y.pageId WHERE(i1.pageID Is NULL) " _
& "end", InsertConnection)
InsertConnection.Open()
SqlInsertCommand.ExecuteNonQuery()
InsertConnection.Close()
SqlInsertCommand = Nothing
If I insert any other SQLcommand there it is excecuted normally.
The command I have is excecuted fine using sql server manager.
Is there any way that a command is excecuted in the SQL manager but not in a page...??
Any ideas would be great...
Thank you
View 6 Replies
View Related
Jun 7, 2004
Hi
I want set the recovery model to SIMPLE via an ASP page
my DB is on the web
i used this code in my ASP page
strSQL = "ALTER DATABASE dbname SET RECOVERY SIMPLE"
and when i run it
nothing happen and the Recovery ramain FULL model
==========================
Please tell me is this a correct code
if not please tell me the correct code for ASP
until i change the Recovery Model to SIMPLE
Thanks
View 2 Replies
View Related
Nov 8, 2007
Hi all,
I have a script to bulk copy data from my table into textfile. This bcp script will be executed by xp_cmdshell. When I try to execute the scipt inside query windows it returned an error : Error = [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified. But when I test the bcp script in command prompt it will execute successfully
Is there any misconfiguration ? (I'm using sqlexpress) thanks in advance.
Best regards,
Hery
View 9 Replies
View Related
Feb 16, 2008
Hi,
i defined a sqldatasource in VWD manually (option specify sql statement) because several tables are involved. I also need a Delete statement, so i defined it also manually.
The select and delete statement are :
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:test %>" SelectCommand="SELECT aspnet_Users.UserName as lid, aspnet_Roles.RoleName as categorie, aspnet_Users.beheerder as beheerder, aspnet_Membership.Email as emailadres FROM aspnet_Users INNER JOIN aspnet_Membership ON aspnet_Users.UserId = aspnet_Membership.UserId INNER JOIN aspnet_UsersInRoles ON aspnet_Users.UserId = aspnet_UsersInRoles.UserId inner JOIN aspnet_Roles ON aspnet_UsersInRoles.RoleId = aspnet_Roles.RoleId order by username"
DeleteCommand="delete from aspnet_users where userid=@userid"> <DeleteParameters> <asp:Parameter Name="userid" /> </DeleteParameters> </asp:SqlDataSource>
My problem is that the Select works, but not the Delete.
Any idea why?
Thanks
tartuffe
View 2 Replies
View Related
Sep 13, 2007
Hello,
I have installed VS 2008 Beta 2 which includes SQL CE 3.5, (I have VS 2005 installed too).
I can not get any INSERT command to work!
First i have tried the VS wizard for a new connection and a dataset. but after inserting a row into one of the dataset tables and then updating it to SQL CE with tha data adapter, nothing happens! NO ERROR and NO inserted row in the database file! so the identity of the inserted row in dataset table doesnt get updated.
Then i tried a simple code without any dataset in a clean solution:
Code Snippet
Dim con As New SqlCeConnection("Data Source=|DataDirectory|MyDatabase#1.sdf")
Dim cmd As New SqlCeCommand("INSERT INTO [Table1](Name) VALUES('TestValue')", con)
Try
con.Open()
cmd.ExecuteNonQuery()
Catch ex As SqlCeException
MessageBox.Show(ex.Message)
Finally
con.Close()
End Try
NO ERROR! NO INSERTED ROW! Nothing happen!
I'm using it in a desktop application.
Why?
Regards,
Parham.
View 5 Replies
View Related
Dec 19, 2005
I was wondering if SQL Cache Dependency would be in fact invalidated if:
1. it was created based on a procedure type command.
2. if the select statement retrieves the data from multiple database tables
Any help would be more appreciated. I am stuck with the fact that none of the data bases on sql dependency is invalidated. I spent literally hours to understand what i am doing incorrectly.
Thanks
View 1 Replies
View Related
Feb 2, 2008
hello it seems lack of c# skills is getting the better of me here...iv been trying to get this to work for hours now without success. I have just started working in c# so i am a beginner :)
all i want to do it query the database, check to see if a title exists, if yes then populate textbox saying 'title in use' but if the title doesnt exists then continue executing rest of code to add the title.
You'll notice iv tried to use string variables and sqlDataAdapter but i cant get these to work either...please help
so far i have this that keeps giving me the error that my button click is undefined: protected void bookButton_Click(object sender, EventArgs e){
//string title;try{SqlConnection conn1 = new SqlConnection();
conn1.ConnectionString =
"Data Source=Gemma-PC\SQLEXPRESS;" +"Initial Catalog=SoSym;" +
"Integrated Security=SSPI;";SqlCommand findTitle = new SqlCommand("SELECT title FROM Publication WHERE title='" + titleTextBox.Text + "';", conn1);
conn1.Open();
findTitle.ExecuteNonQuery();
/*
DataSet myDataSetTitle = new DataSet("PublicationTitle");
myDataSetTitle.Clear();
myDataAdapterTitle.Fill(myDataSetTitle);//myDataSet contains results from above SELECT statement
title = myDataSetTitle.Tables[0].Rows[0]["title"].ToString();*/
}catch(Exception ex)
{TitleInvalidMessage.Text = "Title Not Accepted: This title is already in use by another publication" +ex.Message;
}
//title does not exist in table so continue and add to database
//rest of code here
View 5 Replies
View Related
Feb 23, 2007
i am using visual web developer 2005 and SQL 2005 with VB as the code behindi am using INSERT command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString1").ToString() test.InsertCommandType = SqlDataSourceCommandType.Text test.InsertCommand = "INSERT INTO try (roll,name, age, email) VALUES (@roll,@name, @age, @email) " test.InsertParameters.Add("roll", TextBox1.Text) test.InsertParameters.Add("name", TextBox2.Text) test.InsertParameters.Add("age", TextBox3.Text) test.InsertParameters.Add("email", TextBox4.Text) test.Insert() i am using UPDATE command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString() test.UpdateCommandType = SqlDataSourceCommandType.Text test.UpdateCommand = "UPDATE try SET name = '" + myname + "' , age = '" + myage + "' , email = '" + myemail + "' WHERE roll 123 " test.Update()but i have to use the SELECT command like this which is completely different from INSERT and UPDATE commands Dim tblData As New Data.DataTable() Dim conn As New Data.SqlClient.SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True") Dim Command As New Data.SqlClient.SqlCommand("SELECT * FROM try WHERE age = '100' ", conn) Dim da As New Data.SqlClient.SqlDataAdapter(Command) da.Fill(tblData) conn.Close() TextBox4.Text = tblData.Rows(1).Item("name").ToString() TextBox5.Text = tblData.Rows(1).Item("age").ToString() TextBox6.Text = tblData.Rows(1).Item("email").ToString() for INSERT and UPDATE commands defining the command,commandtype and connectionstring is samebut for the SELECT command it is completely different. why ?can i define the command,commandtype and connectionstring for SELECT command similar to INSERT and UPDATE ?if its possible how to do ?please help me
View 2 Replies
View Related
Nov 4, 2006
Hi All,
i am using a OLE DB Source in my dataflow component and want to select rows from the source based on the Name I enter during execution time. I have created two variables,
enterName - String packageLevel (will store the name I enter)
myVar - String packageLevel. (to store the query)
I am assigning this query to the myVar variable, "Select * from db.Users where (UsrName = " + @[User::enterName] + " )"
Now in the OLE Db source, I have selected as Sql Command from Variable, and I am getting the variable, enterName,. I select that and when I click on OK am getting this error.
Error at Data Flow Task [OLE DB Source [1]]: An OLE DB error has occurred. Error code: 0x80040E0C.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object.".
Can Someone guide me whr am going wrong?
myVar variable, i have set the ExecuteAsExpression Property to true too.
Please let me know where am going wrong?
Thanks in advance.
View 12 Replies
View Related
Aug 30, 2004
Do somebody know how long (in chars) script(command) can be solved by SQL Command?
Thanks
View 1 Replies
View Related
Sep 19, 2006
Hi. I am writing a program in C# to migrate data from a Foxpro database to an SQL Server 2005 Express database. The package is being created programmatically. I am creating a separate data flow for each Foxpro table. It seems to be doing it ok but I am getting the following error message at the package validation stage:
Description: An OLE DB Error has occured. Error code: 0x80040E0C.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object".
.........
Description: "component "OLE DB Destination" (22)" failed validation and returned validation status "VS_ISBROKEN".
This is the first time I am writing such code and I there must be something I am not doing correct but can't seem to figure it out. Any help will be highly appreciated. My code is as below:
private bool BuildPackage()
{
// Create the package object
oPackage = new Package();
// Create connections for the Foxpro and SQL Server data
Connections oPkgConns = oPackage.Connections;
// Foxpro Connection
ConnectionManager oFoxConn = oPkgConns.Add("OLEDB");
oFoxConn.ConnectionString = sSourceConnString; // Created elsewhere
oFoxConn.Name = "SourceConnectionOLEDB";
oFoxConn.Description = "OLEDB Connection For Foxpro Database";
// SQL Server Connection
ConnectionManager oSQLConn = oPkgConns.Add("OLEDB");
oSQLConn.ConnectionString = sTargetConnString; // Created elsewhere
oSQLConn.Name = "DestinationConnectionOLEDB";
oSQLConn.Description = "OLEDB Connection For SQL Server Database";
// Add Prepare SQL Task
Executable exSQLTask = oPackage.Executables.Add("STOCK:SQLTask");
TaskHost thSQLTask = exSQLTask as TaskHost;
thSQLTask.Properties["Connection"].SetValue(thSQLTask, "oSQLConn");
thSQLTask.Properties["DelayValidation"].SetValue(thSQLTask, true);
thSQLTask.Properties["ResultSetType"].SetValue(thSQLTask, ResultSetType.ResultSetType_None);
thSQLTask.Properties["SqlStatementSource"].SetValue(thSQLTask, @"C:LPFMigrateLPF_Script.sql");
thSQLTask.Properties["SqlStatementSourceType"].SetValue(thSQLTask, SqlStatementSourceType.FileConnection);
thSQLTask.FailPackageOnFailure = true;
// Add Data Flow Tasks. Create a separate task for each table.
// Get a list of tables from the source folder
arFiles = Directory.GetFileSystemEntries(sLPFDataFolder, "*.DBF");
for (iCount = 0; iCount <= arFiles.GetUpperBound(0); iCount++)
{
// Get the name of the file from the array
sDataFile = Path.GetFileName(arFiles[iCount].ToString());
sDataFile = sDataFile.Substring(0, sDataFile.Length - 4);
oDataFlow = ((TaskHost)oPackage.Executables.Add("DTS.Pipeline.1")).InnerObject as MainPipe;
oDataFlow.AutoGenerateIDForNewObjects = true;
// Create the source component
IDTSComponentMetaData90 oSource = oDataFlow.ComponentMetaDataCollection.New();
oSource.Name = (sDataFile + "Src");
oSource.ComponentClassID = "DTSAdapter.OLEDBSource.1";
// Get the design time instance of the component and initialize the component
CManagedComponentWrapper srcDesignTime = oSource.Instantiate();
srcDesignTime.ProvideComponentProperties();
// Add the connection manager
if (oSource.RuntimeConnectionCollection.Count > 0)
{
oSource.RuntimeConnectionCollection[0].ConnectionManagerID = oFoxConn.ID;
oSource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(oFoxConn);
}
// Set Custom Properties
srcDesignTime.SetComponentProperty("AccessMode", 0);
srcDesignTime.SetComponentProperty("AlwaysUseDefaultCodePage", true);
srcDesignTime.SetComponentProperty("OpenRowset", sDataFile);
// Re-initialize metadata
srcDesignTime.AcquireConnections(null);
srcDesignTime.ReinitializeMetaData();
srcDesignTime.ReleaseConnections();
// Create Destination component
IDTSComponentMetaData90 oDestination = oDataFlow.ComponentMetaDataCollection.New();
oDestination.Name = (sDataFile + "Dest");
oDestination.ComponentClassID = "DTSAdapter.OLEDBDestination.1";
// Get the design time instance of the component and initialize the component
CManagedComponentWrapper destDesignTime = oDestination.Instantiate();
destDesignTime.ProvideComponentProperties();
// Add the connection manager
if (oDestination.RuntimeConnectionCollection.Count > 0)
{
oDestination.RuntimeConnectionCollection[0].ConnectionManagerID = oSQLConn.ID;
oDestination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(oSQLConn);
}
// Set custom properties
destDesignTime.SetComponentProperty("AccessMode", 2);
destDesignTime.SetComponentProperty("AlwaysUseDefaultCodePage", false);
destDesignTime.SetComponentProperty("OpenRowset", "[dbo].[" + sDataFile + "]");
// Create the path to link the source and destination components of the dataflow
IDTSPath90 dfPath = oDataFlow.PathCollection.New();
dfPath.AttachPathAndPropagateNotifications(oSource.OutputCollection[0], oDestination.InputCollection[0]);
// Iterate through the inputs of the component.
foreach (IDTSInput90 input in oDestination.InputCollection)
{
// Get the virtual input column collection
IDTSVirtualInput90 vInput = input.GetVirtualInput();
// Iterate through the column collection
foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
{
// Call the SetUsageType method of the design time instance of the component.
destDesignTime.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READWRITE);
}
//Map external metadata to the inputcolumn
foreach (IDTSInputColumn90 inputColumn in input.InputColumnCollection)
{
IDTSExternalMetadataColumn90 externalColumn = input.ExternalMetadataColumnCollection.New();
externalColumn.Name = inputColumn.Name;
externalColumn.Precision = inputColumn.Precision;
externalColumn.Length = inputColumn.Length;
externalColumn.DataType = inputColumn.DataType;
externalColumn.Scale = inputColumn.Scale;
// Map the external column to the input column.
inputColumn.ExternalMetadataColumnID = externalColumn.ID;
}
}
}
// Add precedence constraints to the package executables
PrecedenceConstraint pcTasks = oPackage.PrecedenceConstraints.Add((Executable)thSQLTask, oPackage.Executables[0]);
pcTasks.Value = DTSExecResult.Success;
for (iCount = 1; iCount <= (oPackage.Executables.Count - 1); iCount++)
{
pcTasks = oPackage.PrecedenceConstraints.Add(oPackage.Executables[iCount - 1], oPackage.Executables[iCount]);
pcTasks.Value = DTSExecResult.Success;
}
// Validate the package
DTSExecResult eResult = oPackage.Validate(oPkgConns, null, null, null);
// Check if the package was successfully executed
if (eResult.Equals(DTSExecResult.Canceled) || eResult.Equals(DTSExecResult.Failure))
{
string sErrorMessage = "";
foreach (DtsError pkgError in oPackage.Errors)
{
sErrorMessage = sErrorMessage + "Description: " + pkgError.Description + "";
sErrorMessage = sErrorMessage + "HelpContext: " + pkgError.HelpContext + "";
sErrorMessage = sErrorMessage + "HelpFile: " + pkgError.HelpFile + "";
sErrorMessage = sErrorMessage + "IDOfInterfaceWithError: " + pkgError.IDOfInterfaceWithError + "";
sErrorMessage = sErrorMessage + "Source: " + pkgError.Source + "";
sErrorMessage = sErrorMessage + "Subcomponent: " + pkgError.SubComponent + "";
sErrorMessage = sErrorMessage + "Timestamp: " + pkgError.TimeStamp + "";
sErrorMessage = sErrorMessage + "ErrorCode: " + pkgError.ErrorCode;
}
MessageBox.Show("The DTS package was not built successfully because of the following error(s):" + sErrorMessage, "Package Builder", MessageBoxButtons.OK, MessageBoxIcon.Information);
return false;
}
// return a successful result
return true;
}
View 2 Replies
View Related
Feb 27, 2007
My error is that 'Name tr is not declared' tr.Rollback() I tried moving the 'Dim tr As SqlTransaction' outside the try but then I get 'Variable tr is used before it si assinged a value'.
What is the correct way? Try conn.Open() Dim tr As SqlTransaction tr = conn.BeginTransaction() cmdInsert1.Transaction = tr cmdInsert1.ExecuteNonQuery() cmdInsert2.Transaction = tr cmdInsert2.ExecuteNonQuery() cmdInsert3.Transaction = tr cmdInsert3.ExecuteNonQuery() tr.Commit() Catch objException As SqlException tr.Rollback() Dim objError As SqlError For Each objError In objException.Errors Response.Write(objError.Message) Next Finally conn.Close() End Try
View 5 Replies
View Related
Apr 19, 2007
I have the below procedure that will not work- I must be losing my mind, this is not that difficult - mental roadblock for me.
Using SQL Server 2000 to create SP being called by ASP.Net with C# code behind
stored procedure only returns if input exactly matches L_Name
PROCEDURE newdawn.LinkNameLIKESearch @L_Name nvarchar(100)AS SELECT [L_Name], [L_ID], [C_ID], [L_Enabled], [L_Rank], [L_URL] FROM tblContractorLinkInfo WHERE L_Name LIKE @L_Name RETURN
I tried: WHERE L_Name LIKE ' % L_Name % ' no luck. What am I missing?
Thank you
View 2 Replies
View Related
May 24, 2007
SP below is not work.It gives null for @kaysay
I used parametric table name. Is this the problem?
My aim to calculate record count of a table
Thanks.
CREATE PROCEDURE Tablodaki_Kayit_Sayisi(@TABLO varchar(30))AS
Declare @kaysay bigintDeclare @SQLString nvarchar(100)Declare @Param nvarchar(100)
Set @SQLString = N'Select @kaysayOUT = count(BELGE) From ' + @TABLOSet @Param = N'@kaysayOUT bigint'
Execute sp_executesql@SQLString,@Param,@kaysayOUT = @kaysay
Select @kaysay
Return
GO
View 3 Replies
View Related
Sep 3, 2007
SELECT H.id, H.CategoryID ,H.Image ,H.StoryId ,H.Publish, H.PublishDate, H.Date ,H.Deleted ,SL.ListTitle,C.CategoryTitle
FROM HomePageImage H
JOIN shortlist SL on H.StoryId = SL.id
(INNER JOIN category C on H.CategoryId = C.CategoryId)
order by date DESC
View 4 Replies
View Related
Apr 12, 2004
Hello,
I am trying to query with my stored procedure. I am getting comma separated list as input parameter (which is VARCHAR) .
The table column, which I have to compare with input parameter value , is in INTEGER datatype.
So when , I compare Like as follows:-
" AND TABLE1.EMPID IN (@INPUTPARAMETERLIST)"
I am getting error. "Syntax error converting the varchar value '34,343' to a column of data type int."
Could anybody help me , to solve this ?
Thanks !
Nicol
View 1 Replies
View Related
Aug 31, 2004
I have a textbox and a checkbox on a form and I'd like to add both values to a db. The textbox value gets inserted fine but I'm having trouble with the checkbox. Any ideas would be greatly appreciated.
Thanks
Form1 is the column in my db.
SqlCommand myCmd = new SqlCommand();
myCmd.Parameters.Add("@ClientCode",SqlDbType.VarChar).Value = TextBox2.Text;
myCmd.CommandText = "UPDATE table SET Form1 = 'Yes' WHERE ClientCode = @ClientCode";
sqlConnection1.Open();
myCmd.Connection = sqlConnection1;
if( CheckBox1.Checked == true)
{
myCmd.ExecuteNonQuery();
}
sqlConnection1.Close();
View 3 Replies
View Related
Aug 29, 2001
Hi,
Beside working right from the server how else someone can perform the SQL admistration job, I guess my question is how do most SQL DBA perform their administration without going to the server directly. Anyone --- can help please??
Thanks in advance.
View 2 Replies
View Related
Mar 15, 2007
Declare @StartDate datetime
Declare @EndDate Datetime
Set @StartDate = dateadd(day, datediff(day, 0, getdate()), 0)
Set @EndDate = getdate()
The job runs at 11:30 pm so I want the start date to be the same but the time to be equal to 00:00:00.0 When I run the getdate does it also return a time stamp?
The Yak Village Idiot
View 1 Replies
View Related
Aug 6, 2007
I had a problem where some users were experiencing timeouts when trying to add a single record to a table with 2.3 million records.
It's not a very wide table; only 10 columns and the biggest column in varchar 500. The rest are guid, datetime, tinyint...
There is also an old VB app that inserts about 3000 records a day into this table during office hours while users occasionally try and insert a record into this table.
Something said to me that the problem could be indexes but I wasn't quite sure because I though indexes only have an impact on select, delete & update. And not particulary on insert. But I checked it out anyway and noticed that the 3 indexes (1 column PK, 1 column Clustered & 1 column non-clustered) weren't padded. So I changed that (Fill Factor 95) and the problem has gone away. But why? I thought the insert would just have appended it to the end of the index before I made this change? Why would that time out?
View 3 Replies
View Related
Mar 25, 2008
I have the following queries. The first returns the 'Unknown' row, the second works the way I would expect. Are my expectations wrong? Can someone describe for me what is going on?
Thanks
Code Snippet
select *
FROM
SynonymComFinancialCategory b
LEFT JOIN TT_FinancialCategory a
ON
a.FinanceGroup = b.FinanceGroup
AND a.FinanceCode = b.FinanceCode
AND a.Finance = b.Finance
AND b.Finance <> 'Unknown'
WHERE
a.FinanceGroup IS NULL
AND a.FinanceCode IS NULL
AND a.Finance IS NULL
Code Snippet
select *
FROM
SynonymComFinancialCategory b
LEFT JOIN TT_FinancialCategory a
ON
a.FinanceGroup = b.FinanceGroup
AND a.FinanceCode = b.FinanceCode
AND a.Finance = b.Finance
WHERE
a.FinanceGroup IS NULL
AND a.FinanceCode IS NULL
AND a.Finance IS NULL
AND b.Finance <> 'Unknown'
View 8 Replies
View Related
Sep 27, 2006
Hi,I have the following table with some sample values, I want to return the first non null value in that order. COALESCE does not seem to work for me, it does not return the 3rd record. I need to include this in my select statement. Any urgent help please.Mobile Business PrivateNULL 345 NULL4646 65464 65765NULL 564654654 564 6546I want the following as my results:Number3454646564654654Select COALESCE(Mobile,Business,Private) as Number from Table returns:3454646654654 (this is a test to see if private returns & it did with is not null but then how do i include in my select statement to show any one of the 3 fields)select mobile,business,private where private is not null returns:657655646546thanks
View 1 Replies
View Related
Sep 29, 2006
Hello,I created a formview in a web page. The data are in a sql server express database.With this form, I can to create a new data, I delete it but I can't to modify the data.The app_data folder is ready to write data; the datakeynames element in formview web control declared. I replace the automated query created by VS 2005 by a strored procedure to see if the problem solved.The problem is the same with an update query or a update stored procedure...Have you an idea, please.Than you for your help.Regards.
View 1 Replies
View Related
Jan 9, 2007
Im working with a detailsview and when I try to edit something and then update, the changes are not saved.
I have 2 tables ("[etpi.admin].Ocorrencias" and "[etpi.admin].SMS") that store the data that Im trying to change. Since Im having problems with the name of tables, Im coding it manually, using SQL server management studio and VWD.
I believe my code can be wrong (Im new to vwd and C# world), so here it is:
UpdateCommand="UPDATE [etpi.admin].Ocorrencias SET [Status_Ocor] = @Status_Ocor, [Percentual] = @Percentual, [IDPriori] = @IDPriori, [Abertura] = @Abertura, [IDTecRes] = @IDTecRes, [Area] = @Area, [CodEquip] = @CodEquip, [Descricao] = @Descricao, [Destinatario] = @Destinatario, [Data_Implanta] = @Data_Implanta WHERE [etpi.admin].Ocorrencias.IDOcorre = @IDOcorre
UPDATE [etpi.admin].SMS SET [idSMS] = @idSMSWHERE [etpi.admin].SMS.IDOcorre = @IDOcorre">
<UpdateParameters><asp:Parameter Name="idSMS" Type="Int32" /><asp:Parameter Name="Status_Ocor" Type="String" /><asp:Parameter Name="Percentual" Type="Int32" /><asp:Parameter Name="IDPriori" Type="Int32" /><asp:Parameter Name="Abertura" Type="DateTime" /><asp:Parameter Name="IDTecRes" Type="Int32" /><asp:Parameter Name="Area" Type="String" /><asp:Parameter Name="CodEquip" Type="Int32" /><asp:Parameter Name="Descricao" Type="String" /><asp:Parameter Name="Destinatario" Type="String" /><asp:Parameter Name="Data_Implanta" Type="DateTime" /><asp:Parameter Name="IDOcorre" Type="Int32" /></UpdateParameters>
Thx.
View 9 Replies
View Related
May 30, 2007
My rollback does not work
In my SP I want any of cmdS,cmdS2,cmdS3,cmdS4 produces error Rollback must be executed for all of cmdS,cmdS2,cmdS3,cmdS4 . I tested for error producing situation but no rollbak occured.
How can I solve this problem. Thanks.
Below is my SP .
..........
..........
..........
begin transaction
..........
..........If Len(ltrim(rtrim(@cmdS)))>0
execute(@cmdS)
If Len(ltrim(rtrim(@cmdS2)))>0
execute(@cmdS2) If Len(ltrim(rtrim(@cmdS3)))>0
execute(@cmdS3)
If Len(ltrim(rtrim(@cmdS4)))>0
execute(@cmdS4)
If Len(ltrim(rtrim(@cmdS)))>0 or Len(ltrim(rtrim(@cmdS2)))>0 or Len(ltrim(rtrim(@cmdS3)))>0 or Len(ltrim(rtrim(@cmdS4)))>0
Beginif @@ERROR <>0
begin
rollback transaction
set @Hata = 'Error !'
end
Else
Beginset @Hata = 'Sucessfully executed :)' End
End
commit transaction
RETURN
View 1 Replies
View Related
Jun 5, 2007
I can test it in query builder but when i preview the page and go to do the search i get nothing
Here is my statement
SELECT Employees.Last_Name, Employees.First_Name, Job_Transaction.Name, Seq_Descript.Seq_DescriptionFROM Call_List INNER JOIN Call_Group ON Call_List.Group_ID = Call_Group.Group_ID AND Call_List.Group_ID = Call_Group.Group_ID INNER JOIN Employees ON Call_List.Clock = Employees.Clock INNER JOIN Job_Transaction ON Call_Group.Group_ID = Job_Transaction.Group_ID INNER JOIN Seq_Descript ON Call_List.Sequence = Seq_Descript.SequenceWHERE (Call_List.Clock = @Clock)
Mike
View 2 Replies
View Related
Jul 2, 2007
Hi all.I have used a SqlDataSource in my page with this delete command:DELETE FROM tblPersonnel WHERE (ID = @original_ID)and the "OldValueParameterFormatSring" property of the datasource is "original_{0}".and i also have a GridView and a button for delete in rows.(it's CommandName is "Delete"). But when i try to delete a record, the record does not get deleted and this button only makes a PostBack on the page! Why doesn't it work?
Thanks in advance.
View 8 Replies
View Related
Sep 10, 2007
hi all, i have created a gridview with the select,delete and insert commands working properly. but the update command does not work. when i edit a column and click the update button, it generates no errors but does not save the changes. it just brings back the original values. i dont know wats missing. can anyone help me?
this is part of my code:UpdateCommand="UPDATE [test101] SET Surname=@Surname, Names=@Names,Registration=@Registration,[Course code]=@Course_code,Grade=@Grade WHERE ID=@ID1 "<UpdateParameters> <asp:Parameter Name="Surname" Type=String /> <asp:Parameter Name="Names" Type=String /> <asp:Parameter Name="Registration" Type=String /> <asp:Parameter Name="Course_code" Type=String /> <asp:Parameter Name="Grade" Type=Int32 /> <asp:Parameter Name="ID1" Type=Int32 /> </UpdateParameters>
View 4 Replies
View Related
Jan 16, 2008
Hi,I am having trouble with a WHERE clause:WHERE (([A] = @A) AND ([B] >= @B) AND (([C] < [D])) OR ([C] = 0 AND [D] = 0)) It's meant to only select data where A = @A, and B is more than or equal to @B, and one of the next two are true: C is less than D or C and D are both 0 Thanks,Jon
View 3 Replies
View Related
