Cannot Use Alias In Delete Command
Dec 27, 2000
Why cannot I use alias in delete command when I join two table.
My query is as follows.
delete from ikw_indexed_form_approved where exists(select * from ikw_router
where ikw_indexed_form_approved.ikw_subscription_id=ikw_ subscription_id
and ikw_indexed_form_approved.ikw_indexed_name='centra al corp'
and ikw_indexed_form_approved.content_code='html'
and ikw_indexed_form_approved.ikw_subscription_id=1)
if I use like this it gives me an error
delete from ikw_indexed_form_approved A where exists(select * from ikw_router
where A.ikw_subscription_id=ikw_subscription_id
and A.ikw_indexed_name='centraal corp'
and A.content_code='html'
and A.ikw_subscription_id=1)
Your help will be appreciated.
Thanks in advance
View 3 Replies
ADVERTISEMENT
Nov 24, 2015
We will be moving 2 different databases (SS2005 & SS2008) to a new SS2014 SQL Server. Currently our codes looks something like Server1DBInstance1... & Server2DBInstance2... Is it possible to move the objects from these 2 instances to Server3DBInstance3 and then use an alias to reference the objects? Or does Server3 need to have DBInstance1 & DBIstance2? Basically, is the alias just for the database or for the instance too? Can I create an alias "Server1DBInstance1' on Server3DBInstance3 and assign objects to that alias?
View 16 Replies
View Related
Mar 16, 2008
What's the best way to debug the SQL commands with parameters? My Formview performs the INSERT and EDIT commands fine -- and I can see the results in the tables. However, my Delete command crashes -- see the error below.
I assume that the problem is a formatting issue with an input parameter -- i see there are equality tests for the parameters before the delete is allowed --
How Can I look at these parameters to inspect them? All the types on the paranmeters look fine to me!!
---Jim
Server Error in '/GVOps4' Application.
Input string was not in a correct format.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.FormatException: Input string was not in a correct format.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[FormatException: Input string was not in a correct format.]
System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal) +2755599
System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info) +112
System.String.System.IConvertible.ToInt32(IFormatProvider provider) +43
System.Convert.ChangeType(Object value, TypeCode typeCode, IFormatProvider provider) +293
System.Web.UI.WebControls.Parameter.GetValue(Object value, String defaultValue, TypeCode type, Boolean convertEmptyStringToNull, Boolean ignoreNullableTypeChanges) +264
System.Web.UI.WebControls.SqlDataSourceView.AddParameters(DbCommand command, ParameterCollection reference, IDictionary parameters, IDictionary exclusionList, String oldValuesParameterFormatString) +557
System.Web.UI.WebControls.SqlDataSourceView.ExecuteDelete(IDictionary keys, IDictionary oldValues) +615
System.Web.UI.DataSourceView.Delete(IDictionary keys, IDictionary oldValues, DataSourceViewOperationCallback callback) +75
System.Web.UI.WebControls.FormView.HandleDelete(String commandArg) +832
System.Web.UI.WebControls.FormView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +535
System.Web.UI.WebControls.FormView.OnBubbleEvent(Object source, EventArgs e) +95
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
System.Web.UI.WebControls.FormViewRow.OnBubbleEvent(Object source, EventArgs e) +109
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +115
System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +132
System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +177
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1746
<asp:SqlDataSource ID="PilotReportDetail" runat="server"
ConflictDetection="CompareAllValues" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
DeleteCommand="DELETE FROM [FlightReport] WHERE [FlightReportID] = @original_FlightReportID AND [DateTimeEntered] = @original_DateTimeEntered AND [DateTimeFlown] = @original_DateTimeFlown AND [PilotID] = @original_PilotID AND [AircraftID] = @original_AircraftID AND [CameraPodID] = @original_CameraPodID AND [OrderID] = @original_OrderID AND [FlightHours] = @original_FlightHours AND [FlightSegments] = @original_FlightSegments AND [ReflyReason] = @original_ReflyReason" InsertCommand="INSERT INTO [FlightReport] ([DateTimeEntered], [DateTimeFlown], [PilotID], [AircraftID], [CameraPodID], [OrderID], [FlightHours], [FlightSegments], [ReflyReason]) VALUES (@DateTimeEntered, @DateTimeFlown, @PilotID, @AircraftID, @CameraPodID, @OrderID, @FlightHours, @FlightSegments, @ReflyReason)"
OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [FlightReport] WHERE ([FlightReportID] = @FlightReportID)"
UpdateCommand="UPDATE [FlightReport] SET [DateTimeEntered] = @DateTimeEntered, [DateTimeFlown] = @DateTimeFlown, [PilotID] = @PilotID, [AircraftID] = @AircraftID, [CameraPodID] = @CameraPodID, [OrderID] = @OrderID, [FlightHours] = @FlightHours, [FlightSegments] = @FlightSegments, [ReflyReason] = @ReflyReason WHERE [FlightReportID] = @original_FlightReportID AND [DateTimeEntered] = @original_DateTimeEntered AND [DateTimeFlown] = @original_DateTimeFlown AND [PilotID] = @original_PilotID AND [AircraftID] = @original_AircraftID AND [CameraPodID] = @original_CameraPodID AND [OrderID] = @original_OrderID AND [FlightHours] = @original_FlightHours AND [FlightSegments] = @original_FlightSegments AND [ReflyReason] = @original_ReflyReason">
<SelectParameters><asp:ControlParameter ControlID="GridView1" Name="FlightReportID"
PropertyName="SelectedValue" Type="Int32" />
</SelectParameters>
<DeleteParameters>
<asp:Parameter Name="original_FlightReportID" Type="Int32" />
<asp:Parameter Name="original_DateTimeEntered" Type="DateTime" />
<asp:Parameter Name="original_DateTimeFlown" Type="DateTime" />
<asp:Parameter Name="original_PilotID" Type="Int32" />
<asp:Parameter Name="original_AircraftID" Type="Int32" />
<asp:Parameter Name="original_CameraPodID" Type="Int32" />
<asp:Parameter Name="original_OrderID" Type="Int32" />
<asp:Parameter Name="original_FlightHours" Type="Double" />
<asp:Parameter Name="original_FlightSegments" Type="Int32" />
<asp:Parameter Name="original_ReflyReason" Type="Int32" />
</DeleteParameters>
View 2 Replies
View Related
Nov 10, 1999
Hi
I am using SQL 6.5. I have a master table which is referenced by more than 35 tables. When I try to
delete a record from the table, i am getting this message
Msg 431, Level 16, State 0
Unable to bind foreign key constraint. Too many tables involved in query.
SQL 6.5 maximum 31 foreign key references. I was wondering is there any way to delete those
records. (i don't want to drop the foreign constraints)
SQL 7.0 supports maximum 63 foreign key references.
Thanks in advance
Moorthy.
View 1 Replies
View Related
Aug 12, 2006
i need to dyanamically generate my SQL commands so to do that i am generating sqldatasource commands programmatically rather declaratively. SELECT commands seems to work fine but DELETE isnt doing anything, here is my code:
SqlDataSource sdsConsultant = new SqlDataSource();
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
loadDataSet(); //it just loads dataset used by formview
initializeSDS();
}
}
protected void initializeSDS()
{
string strConnection = ConfigurationManager.ConnectionStrings["myDB"].ToString();
sdsConsultant.ConnectionString = strConnection;
//SELECT
sdsConsultant.SelectCommand = "SELECT * FROM Consultants WHERE (id=@id)";
QueryStringParameter id = new QueryStringParameter("id", "id");
sdsConsultant.SelectParameters.Add(id);
//DELETE
sdsConsultant.DeleteCommand = "DELETE * FROM Consultants WHERE (id=@id2)";
QueryStringParameter id2 = new QueryStringParameter("id2", "id");
sdsConsultant.DeleteParameters.Add(id2);
//UPDATE
Page.Controls.Add(sdsConsultant);
FormView1.DataSource = sdsConsultant;
FormView1.DataBind();
}
my formview control looks like:
<asp:FormView DefaultMode="Edit" ID="FormView1" runat="server" DataKeyNames="id"
OnItemDeleted="FormView1_ItemDeleted"
OnItemDeleting="FormView1_ItemDeleting">
the DELETE doesnt execute and the OnItemDeleted event doesnt do anything either. am i doing something wrong here? plz help
View 2 Replies
View Related
Apr 23, 2008
I am presently using 'tablediff' utility of SQLServer 2005 to compare tables located on two different servers. My real intension is to backup data from Serv1 to Serv2, except that I wish to update the tables on server2 with only newly inserted rows and updated rows and NOT deleted rows.
In short, I want the final .sql file generated by the tablediff utility to contain only 'Insert' and 'Update' statements and NOT 'Delete' statements.
I hope someone can help me in this regard.
Thank you,
Little_Birdie.
View 6 Replies
View Related
Dec 13, 2005
Hi,
I tried to use the RETURN_VALUE of a stored procedure in updata / delete commands with a sql-datasource and a stored procedure.
The thing works fine with the insert command.But it fails with "too many arguments..." in delete / update.
I also tried to handle the updating event like this:
protected void sqldsReportSelect_Updating(object sender, SqlDataSourceCommandEventArgs e) { SqlParameter sqP = new SqlParameter("RETURN_VALUE", SqlDbType.Int); sqP.Direction = ParameterDirection.ReturnValue; e.Command.Parameters.Add(sqP);}
It brings the same error.
Do I make something wrong or is this a bug?
Regards
Manfred
View 2 Replies
View Related
May 7, 2008
Hi to all:
I'm trying to make a sp who generates an export string for everey sql command, for example: Insert, update and delete. In the case of Insert I just have the sp, but in "update" and "delete" commands... how can I know the conditions?..
UDTATE DemoTable SET DemoField=77 WHERE (Condition1 AND Condition2)
....may I use a comparative with 'updated' table?, please help me with your ideas
Thanks a lot.
View 4 Replies
View Related
Dec 11, 2007
We have SS2K5 source and DB2 Target. I downloaded and installed MS OLE DB provider for DB2. It is configured correctly. The data insert works fine. But I am having problem with update/delete on DB2 when passing string parameters.
The OLEDB command works fine when I hard code the values:
delete from TableName where Col1='abc' and Col2='xyz'
But when I use parameters the package executes successfully, but the data is not delete on DB2.
Any one having similar experience or solution for this is really appreciated.
Thanks,
View 7 Replies
View Related
Mar 8, 2007
HiI'm using a simple SqlDataSource to connect to a stored proc to delete a number of rows from different tables.In my SQL im using: DECLARE @table1Count int
DELETE FROM Table1 WHERE id = @new_id
SET @table1Count=@@rowcount
SELECT @table1Count I'm then using an input box and linking it to the delete control parameter. Then on a button click event i'm running SqlDataSource1.Delete() which all works fine. But how do i get the @table1Count back into my aspx page? Thanks
View 3 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
Apr 25, 2008
i've read the transact-sql command,
i known that the select command use to retrieve many fields from many tables with one command
select * from table1,table2
yes,
but i ' ve not seen the way to add,delete or update those fields from those tables with one command...
Is it possible? why?
I don't have any idea , can u help me
I want to know the sql commands , if it's possible
thanks for reply,
mochi
View 3 Replies
View Related
Sep 19, 2015
Recently I constructed a new backup process that I want to institute globally across all my SQL Server 2005 instances, Is there a way I can automate a file deletion process from within SQL Server?
View 2 Replies
View Related
Feb 24, 2015
I have the need to delete old backup files via TSQL job. Found this solution online:
PushD "
emoteservershareDIFF" &&(
forfiles -m *DIFF*.sqb -d -1 -c "cmd /c del /q @path"
) & PopD
It works remotely if I run it via command prompt. But when I add this to a TSQL job on my remote SQL instance, it runs without deleting anything. What I'm missing?
View 6 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
Sep 11, 2015
I have some simple files but they are failing because the delete history task is failing as it is looking for files in a non existent directory.
It is looking for files in C:Program FilesMicrosoft SQL ServerMSSQL10_50.INSTANCEMSSQLLog whereas it should be looking in C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLLog
how I can get this corrected so I can get the Maintenance Plans to run correctly.
I have tried deleting and recreating the Plan but to no avail
View 0 Replies
View Related
May 19, 2012
I am using Master Data Service for couple of months now. I can load, update, merge and soft delete data in MDS. Occasionally we even have to hard delete data from MDS. If we keep on soft deleting records in a MDS table eventually there will be huge number of soft deleted records. Is there an easy way to hard delete all the soft deleted records from all MDS tables in a specific Model.
View 18 Replies
View Related
Oct 23, 2004
Hello:
Need some serious help with this one...
Background:
Am working on completing an ORM that can not only handles CRUD actions -- but that can also updates the structure of a table transparently when the class defs change. Reason for this is that I can't get the SQL scripts that would work for updating a software on SqlServer to be portable to other DBMS systems. Doing it by code, rather than SQL batch has a chance of making cross-platform, updateable, software...
Anyway, because it needs to be cross-DBMS capable, the constraints are that the system used must work for the lowest common denominator....ie, a 'recipe' of steps that will work on all DBMS's.
The Problem:
There might be simpler ways to do this with SqlServer (all ears :-) - just in case I can't make it cross platform right now) but, with simplistic DBMS's (SqlLite, etc) there is no way to ALTER table once formed: one has to COPY the Table to a new TMP name, adding a Column in the process, then delete the original, then rename the TMP to the original name.
This appears possible in SqlServer too --...as long as there are no CASCADE operations.
Truncate table doesn't seem to be the solution, nor drop, as they all seem to trigger a Cascade delete in the Foreign Table.
So -- please correct me if I am wrong here -- it appears that the operations would be
along the lines of:
a) Remove the Foreign Key references
b) Copy the table structure, and make a new temp table, adding the column
c) Copy the data over
d) Add the FK relations, that used to be in the first table, to the new table
e) Delete the original
f) Done?
The questions are:
a) How does one alter a table to REMOVE the Foreign Key References part, if it has no 'name'.
b) Anyone know of a good clean way to get, and save these constraints to reapply them to the new table. Hopefully with some cross platform ADO.NET solution? GetSchema etc appears to me to be very dbms dependant?
c) ANY and all tips on things I might run into later that I have not mentioned, are also greatly appreciated.
Thanks!
Sky
View 1 Replies
View Related
Nov 13, 2006
I am having great difficulty with cascading deletes, delete triggers and referential integrity.
The database is in First Normal Form.
I have some tables that are child tables with two foreign keyes to two different parent tables, for example:
Table A
/
Table B Table C
/
Table D
So if I try to turn on cascading deletes for A/B, A/C, B/D and C/D relationships, I get an error that I cannot have cascading delete because it would create multiple cascade paths. I do understand why this is happening. If I delete a row in Table A, I want it to delete child rows in Table B and table C, and then child rows in table D as well. But if I delete a row in Table C, I want it to delete child rows in Table D, and if I delete a row in Table B, I want it to also delete child rows in Table D.
SQL sees this as cyclical, because if I delete a row in table A, both table B and table C would try to delete their child rows in table D.
Ok, so I thought, no biggie, I'll just use delete triggers. So I created delete triggers that will delete child rows in table B and table C when deleting a row in table A. Then I created triggers in both Table B and Table C that would delete child rows in Table D.
When I try to delete a row in table A, B or C, I get the error "Delete Statement Conflicted with COLUMN REFERENCE". This does not make sense to me, can anyone explain? I have a trigger in place that should be deleting the child rows before it attempts to delete the parent row...isn't that the whole point of delete triggers?????
This is an example of my delete trigger:
CREATE TRIGGER [DeleteA] ON A
FOR DELETE
AS
Delete from B where MeetingID = ID;
Delete from C where MeetingID = ID;
And then Table B and C both have delete triggers to delete child rows in table D. But it never gets to that point, none of the triggers execute because the above error happens first.
So if I then go into the relationships, and deselect the option for "Enforce relationship for INSERTs and UPDATEs" these triggers all work just fine. Only problem is that now I have no referential integrity and I can simply create unrestrained child rows that do not reference actual foreign keys in the parent table.
So the question is, how do I maintain referential integrity and also have the database delete child rows, keeping in mind that the cascading deletes will not work because of the multiple cascade paths (which are certainly required).
Hope this makes sense...
Thanks,
Josh
View 6 Replies
View Related
Nov 17, 2006
I'm trying to clean up a database design and I'm in a situation to where two tables need a FK but since it didn't exist before there are orphaned records.
Tables are:
Brokers and it's PK is BID
The 2nd table is Broker_Rates which also has a BID table.
I'm trying to figure out a t-sql statement that will parse through all the recrods in the Broker_Rates table and delete the record if there isn't a match for the BID record in the brokers table.
I know this isn't correct syntax but should hopefully clear up what I'm asking
DELETE FROM Broker_Rates
WHERE (Broker_Rates.BID <> Broker.BID)
Thanks
View 6 Replies
View Related
Mar 30, 2000
We have an alias for dbo in various code areas, development/test/production. It was easy to tell the DBO alias
on release 6.5 but now on 7.0, the only way I can figure out if the userid is aliased to dbo is to sign on as the userid
and query 'select user_name( )' and if it returns dbo, I have the answer. Is there an easier way to do this by looking in a
system catalog or is there something else I am missing? Thanks in advance. Karen Suenram
View 7 Replies
View Related
Jul 1, 1999
In 6.5 you could alias someone as dbo, thus preventing an ownership chain break. In 7.0 there are roles, one of which is db_owner. I gave a user db_owner role, and they created a table. But no one else can see it? And he is listed as the owner in EM. How do I more effectivly mimic the dbo alias available in 6.5?
View 1 Replies
View Related
Jun 25, 2007
Hi All,
I am trying to filter my result with HAVING clause. vchEditorName is an alias which I get in the result as a column (it gives me correct values). I want to select only those records which have the value in this field like 'akash'
sql Code:
Original
- sql Code
SELECT a.intArticleId, a.intEditorId, a.enumEditorType, a.vchArticleTitle, a.enumStatus, am.vchFirstName, u.vchScreenName,
Case a.enumEditorType
WHEN 'A' THEN am.vchFirstName
WHEN 'M' THEN u.vchScreenName
END as vchEditorName
FROM tblArticles a
LEFT OUTER JOIN tblLogin u ON a.intEditorId = u.intUserID
LEFT OUTER JOIN tblAdminMaster am ON a.intEditorId = am.intAdminID
WHERE a.enumStatus='A'
GROUP BY a.intArticleId, a.intEditorId, a.enumEditorType, a.vchArticleTitle, a.enumStatus, am.vchFirstName, u.vchScreenName
HAVING vchEditorName LIKE '%akash%'
SELECT a.intArticleId, a.intEditorId, a.enumEditorType, a.vchArticleTitle, a.enumStatus, am.vchFirstName, u.vchScreenName, CASE a.enumEditorType WHEN 'A' THEN am.vchFirstName WHEN 'M' THEN u.vchScreenName END AS vchEditorNameFROM tblArticles a LEFT OUTER JOIN tblLogin u ON a.intEditorId = u.intUserID LEFT OUTER JOIN tblAdminMaster am ON a.intEditorId = am.intAdminID WHERE a.enumStatus='A' GROUP BY a.intArticleId, a.intEditorId, a.enumEditorType, a.vchArticleTitle, a.enumStatus, am.vchFirstName, u.vchScreenNameHAVING vchEditorName LIKE '%akash%'
View 5 Replies
View Related
Apr 21, 2008
I want an alias instead of display the column name for the below query..
select STE = case when STE ='AT'then'01'
when STE ='AT1'then'02'
......
.......
.......
........
else STE
end
from tableinfo
Output for the query
STE
---
01
02
Desired output:
STEDN
-----
01
02
Thanks for the help in advance !!!
View 4 Replies
View Related
Jun 2, 2008
Hi All,
I've just installed an active/active sql cluster. Both are named instance hence if i need to connect to it, I need to use NODE1SQLINST1 and NODE2SQLINST2. Is there a way to just use NODE1 or any other single name to connect to the sql instance? I was looking at Alias but I can't get it working. Anyone has any ideas or suggestion for me? Reason why i want to do this is in an odbc connection, instead of using NODE1SQLINST1, I can use NODE1.
Thanks
Regards
Ken
View 6 Replies
View Related
Aug 15, 2007
pls someone help me about adding alias column or column alias
here's my code
select
(select count(*)
from dto_client_dtl
where dto_client_dtl.testStage is null or (dto_client_dtl.testStage = 'INI' and dto_client_dtl.testResult = 'POS')) as 'NoOfPendingTransaction',
(select count(*)
from dto_client_dtl
where dto_client_dtl.testStage in ('CHM','CHA') and dto_client_dtl.testResult = 'POS') as 'NoOfConfirmedPositive',
(select count(*)
from dto_client_dtl
where dto_client_dtl.testStage in ('CHM','CHA') and dto_client_dtl.testResult = 'NEG') as 'NoOfConfirmedNegative',
(select count(*)
from dto_client_dtl
where dto_client_dtl.testStage is not null and dto_client_dtl.testResult = 'POS') as 'TotalNoOfScreeningPositive',
(select count(*)
from dto_client_dtl
where dto_client_dtl.testStage = 'INI' and dto_client_dtl.testResult = 'NEG') as 'TotalNoOfScreeningNegative',
(select count(*)
from dto_client_dtl
where dto_client_dtl.tranStat in ('CCn','TCN')) as 'TotalNoOfCancelledTxns',
('NoOfPendingTransaction'+'TotalNoOfScreeningPositive'+'TotalNoOfScreeningNegative') as 'SubTotal'
from dto_client_dtl
when i run this code the output is
Truncated incorrect DOUBLE value: 'NoOfPendingTransaction'
Truncated incorrect DOUBLE value: 'TotalNoOfScreeningPositive'
Truncated incorrect DOUBLE value: 'TotalNoOfScreeningNegative'
View 2 Replies
View Related
Oct 5, 2006
hi. i face a problem . i cant reference an alias and have to copy and paste code again. how can i ? see "Computed Total", i had to copy its code again.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[Select_Bill_]
@payment_or_bill nvarchar(2),
@spcode nvarchar(25)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @fieldname varchar(10) DECLARE @stmt varchar(4000)
DECLARE Fields CURSOR FOR
SELECT Amounttype FROM BILL_Amounttypes
SET @stmt = 'select billid,Date_,Suppliercode,billtype,typecode,payment_or_bill,roe,currency'
OPEN Fields
FETCH Next FROM Fields INTO @fieldname
WHILE @@Fetch_Status = 0
BEGIN
SET @stmt = @stmt + ', (select billid_detailed from
bill_Detailed where billid = bill1.billid and
amounttype = ''' + @fieldname + ''''
SET @stmt = @stmt + ') As ' + @fieldname
FETCH Next
FROM Fields
INTO @fieldname END
CLOSE Fields
DEALLOCATE Fields
SET @stmt = @stmt + ',(Select sum(amountfc * case when amountfc < 0 then -1 else 1 end)
from bill_detailed where billid = bill1.billid ) as "Computed Total",Total'
SET @stmt = @stmt + ',(Select case when
(Select sum(amountfc * case when amountfc < 0 then -1 else 1 end ) from bill_detailed where billid = bill1.billid ) =
Total then ''Yes'' else ''No'' end ) as Processed'
SET @stmt = @stmt + ' From bill_ as bill1'
exec(@stmt)
select (@stmt)
END
View 5 Replies
View Related
Dec 11, 2006
Hi all,
I was able to get my mirroring setup to work only when I use Alias instead of IP address. Any idea why it is so?
Thanks,
Avi
View 3 Replies
View Related
May 18, 2006
How to get more columns within same alias?
(
select DateOpen AS Date,TestObjectID from RprRepair where TestObjectID = @AssetID
union all
select DateSent ,TestObjectID from RprRepair where TestObjectID = @AssetID
union all
select DateRepairFinished,TestObjectID from RprRepair where TestObjectID = @AssetID
) AS Der
This works fine alone, but when i put it into union i get an error that no more than one value can be in subqueries.
View 3 Replies
View Related
