Disable Or Block TRUNCATE Command
May 2, 2008
Hi guys, am I know is there any way for disabling/blocking the truncate command so that won't accidentally truncate all the tables within the db in a production server ?
ADVERTISEMENT
'Truncate' Command Doesn't Truncate Enough
Dec 30, 2004With a database size of almost 2 GB, I run the 'truncate table eventlog command' which completes successfully, but the database size only decreases by about 10 MB so stays too large - indeed the number of rows in the eventlog table is minimal, but the otehr tables in this database don't show such an amount of tables large enough to cause the size issue either. What could be the reason and how can I reduce it (possibly truncating another table but then which one, how could I determine which is too large and needs truncating?).
View 3 Replies View RelatedDoes A Background Process Block Sp_dboption Command
Jan 25, 2008Does an sp_dboption 'dbname','single user',true command execute succesfully when a background process is working on a database.
View 3 Replies View RelatedUse Truncate Command On A Table
Jun 3, 2008hi
Can We Use Truncate Command on a table which is Refernced by Foreign Key?
Database Mirroring And Truncate Table Command
Jan 3, 2008
I was working on my Lab Environment and i had a DB which was mirrored in synchronous mode without witness server. I truncated one of the tables in this DB and did a failover to the mirror server. The table got truncated in the mirror too. Though we know that truncate is a non logged operation how does this mechanism work? Can someone please expain me??? Thanks in advance. Your replies will be very much helpful to me.
SQL Server Admin 2014 :: How To Disable Reboot Pending Through Command
Mar 16, 2015Any command through that can I delete/disable the DWORD value "Pendingfilerename operations" so that while I start the SQL Install work, it does not fail due to this and can write this as one of the precheck options.
View 0 Replies View RelatedT-SQL (SS2K8) :: Why Block Scope Variables Exist Outside Of Block
Dec 3, 2014I have the following stored procedure to test scope of variables
alter proc updatePrereq
@pcntr int,
@pmax int
as
begin
[Code] ....
In the above script @i is declare in the if block only when the @pcntr value is 1. Assume the above stored procedure is called 5 times from this script
declare @z int
set @z = 1
declare @max int
set @max = 5
while @z <= @max
begin
exec dbo.updatePrereq @z, @max
set @z = @z + 1
end
go
As i said earlier `@i` variable exists only when `@pcntr` is `1`. Therefore when i call the stored procedure for the second time and so forth the control cannot enter the if block therefore @i variable wouldn't even exist. But the script prints the value in `@i` in each iteration, How comes this is possible should it throw an error saying `@i` variable does not exist when `@pcntr` values is greater than `1`?
To Truncate Or Not To Truncate: That Is The Question..
Apr 17, 2008Hi all...
Please forgive the elementary nature of my question, but could someone please explain the differences between these two database backup types:
1. Log backup
2. Log backup no truncate
From what I understand and have read, the "no truncate" backup method keeps the entire transaction log indefinitely. Using the truncation method, the transaction log is either 1) compressed or 2) cleaned up so that any completed transactions are removed from the log. Which one of these is true?
And, for the big question: is it better to run a backup of the transaction log with truncation or not? Our current backup scheme is similar to the following:
Full backup every 24 hours
transaction log backup every hour with no truncation
Should we insert a truncation backup somewhere in here? What is the danger of removing (or compressing) parts of the transaction log? Will this affect the restore process?
Thanks in advance!
Defining Command,commandtype And Connectionstring For SELECT Command Is Not Similar To INSERT And UPDATE
Feb 23, 2007i 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 RelatedPl/Sql Block?
Apr 8, 2008I've written a couple blocks but I have no idea when it comes to this one.
Create a PL/SQL block to retrieve the last name and department ID if each employee from the EMPLOYEES table
for those employees whose EMPLOYEE_ID is less than 114. From the values retreived from the employees table, populate two PL/SQL tables,
one to store the records of the employee last names and the other to store the records of their department IDs.
Using a loop, retreive the employee name information and salary infromation from the PL/SQL tables and doisplay it in the window,
using DBMS_OUTPUT.PUT_LINE. Display these details for the first 15 employees in the PL/SQL tables.
Any help helps
AES (128 Bit Block) : Can I Use It?
May 18, 2006If I've got a 64-bit OS such as Windows XP Professional x64 Edition running and I have the SQL Server 2005 Express Edition, is AES permittable or does TRIPLEDES still need to be used?
View 1 Replies View RelatedUsing A Variable In SSIS - Error - Command Text Was Not Set For The Command Object..
Nov 4, 2006Hi 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.
Database Block
Apr 10, 2008Hello,
I have been experiencing a lot of blocking activity in my database. My trace results show that the following extended stored procedures sp_prepexec and sp_cursorfetch are executed by both the user causing the block and the user tha is blocked. Any ideas as to how to rectify this situation will be appreciated.
Regards,
Albert
Try.. Catch Block
Dec 11, 2006HI,
i'm trying to execute some sql using the Try.. Catch blocks.
Following code does not execute in Catch Block
Begin
begin try
insert into dbo.Test values (1,'aaa')
-- here we are inserting int value in identity field...
END TRY
Begin catch
PRINT 'TEST'
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;
END Catch
End
GO
Whereas the following block works fine and the Catch block executes.
Begin
begin try
Select 1/0
--This causes an error.
END TRY
Begin catch
PRINT 'TEST'
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;
END Catch
End
GO
Any idea why is it so?
How To Check Block
Mar 5, 2008Can anyone guide me to write check block in SQL Server?
I Mean if blocking occurs, just inform people.
Table Block
Mar 18, 2008Hi there! I need such thing: I have a data in Table A. When I get this data, I also increment this data by one, what I want is block Table A in order to other people will not get access to data.. I think about transactions, but are transactions reaaly blocks a table's content?
P.S. For example in MySQL there are Lock table command for blocking table for reading/writing. I need such thing/
Thanks
Process Block Itself
Jul 3, 2007Hi to everybody,
i have this problem.
sometimes a process in my SQL Server 2005 (upd 2) is blocked from itself. If I find in monitor it wait for a lock_M_Sch_M on a temporary table as you can see from
select * from sys.dm_tran_locks where request_session_id=51 and request_status <> 'GRANT'
resorce type : OBJECT
resource id : 218899226
request mode : Sch-M
request status WAIT
request_session id : 51
request life time : 33554432
or from
SELECT * FROM sys.dm_os_waiting_tasks WHERE SESSION_ID=51
waiting task address : 0x0000000000C2F198
session id : 51
wait type : LCK_M_SCH_M
resource address : 0x0000000201C71300
blocking task address : 0x0000000000C2F198
blocking session id = 51
resource description objectlock lockPartition=0 objid=218899226 subresource=FULL dbid=2 id=lock80d04900 mode=Sch-S associatedObjectId=218899226
I resolve this problem only with restart of sql server . that clear tempdb and eliminate this process
I think is not a problem about latch present from sql server 2000 sp4 (I read some document abount this problem)
So I haven't idea how to resolve this problem and how to kill this kind of process without to restart sql server
thanks in advance
Luca
How Do We Use Block Cursor
Oct 25, 2007
hi there,
to retrieve more then one record with a cursor we can use block cursor, so we have to set the cursor attribute SQL_ATTR_ROWSET_SIZE to some number...i am not getting any code example on this.
do anyone know this???
Try Catch Block
Apr 12, 2006Hi:
one of our study group members noticed a strange behavior and has the following question. Any thoughts are appreciated.
I am unable to understand as to why the CATCH block is not executed when an INSERT is made
On table T3 which is dropped after the first transaction.
The severity of Insert into t3 values (3) is Msg 208, Level 16, State 1, Line 2
Invalid object name 't3'.
BOL says TRY€¦CATCH constructs do not trap the following conditions:
Warnings or informational messages with a severity of 10 or lower.
Errors with severity of 20 or higher that terminate the SQL Server Database Engine task processing for the session. If an error occurs with severity of 20 or higher and the database connection is not disrupted, TRY€¦CATCH will handle the error.
Here is the script.
use tempdb
go
create table t1 (a int)
create table t2 (b int)
create table t3 (c int)
Begin tran
Insert into t1 values (1)
Insert into t2 values (2)
Insert into t3 values (3)
IF @@error <> 0
Rollback tran
else
commit tran
-------------------------------------------------------------
Select * from t1
Select * from t2
Select * from t3
-------------------------------------------------------------
Drop table t3
-------------------------------------------------------------
Set xact_abort on
Begin try
Begin tran insertNow
Insert into t1 values (1)
Insert into t2 values (2)
save tran insertNow
Insert into t3 values (3)
commit tran insertNow
End try
Begin Catch
IF (XACT_STATE()) = -1
BEGIN
PRINT 'The transaction is in an uncommittable state.' +
' Rolling back transaction.'
ROLLBACK TRANSACTION insertNow
END;
-- Test if the transaction is active and valid.
IF (XACT_STATE()) = 1
BEGIN
PRINT 'The transaction is committable.' +
' Committing transaction.'
COMMIT TRANSACTION insertNow
END
End Catch
Do Somebody Know How Long (in Chars) Script(command) Can Be Solved By SQL Command?
Aug 30, 2004Do somebody know how long (in chars) script(command) can be solved by SQL Command?
Thanks
What Command Is Used To Get Back The Privileges Offered By The GRANT Command?
Mar 10, 2007reply.
View 1 Replies View RelatedBLock Records Using UPDLOCK
Oct 2, 2006Hi all,
here my question :
I have 2 applications.
connection 1.
one does select max(grp) from orv and one does select max(grp) from orh. orh is the historical file from orv. We did this to know which is the greather grp between these 2 files.
After having did this, we add 1 at grp field.
we insert into orv the record max(grp) + 1
connection 2.
an other application could insert at the same time record in this table orv with same parameters.
my problem is the following. I need to block record in orv table either on the select ( connection 1) or Insert ( connection 2) to avoid having select max(grp) + 1 on orv at connection 1 and Insert a record into orv at connection 2.
I believe I need to use HOLDLOCK, UPDLOCK.
but I have not the habitude to use them.
Can I do this ?
connection 1
select max(grp) from orv WITH HOLDLOCK
connection 2
what should I use to avoid lock when I need to insert into orv. ?
thanks for your quick answer
End A Blocking Process (Was Block)
Feb 11, 2008Ho do I clear a kill process blocking others to run
View 3 Replies View RelatedDo A Lot Of Linked Tables Cause Block?
May 18, 2004Hello, everyone:
There are a lot of Access and Excel tables linked to my SQL Server (SQL2K SP3 on W2K). The end users update those likned tables. I am wondering if there is the block problem. If yes, how to prevent that? Thanks.
ZYT
Writer Block Reader, Reader Block Writer...
Sep 27, 2005hi,
i've performed a test on 2 machines based on the topic above, but it doesn't seem like blocking each other... This is the context...
I've created a table call TEST_DEL i.e.
quote:CREATE TABLE test_del
(v_id INT,
desc CHAR(3)
)
In machine 1, i'm login as USER 1 and try to insert a set of records into the table
quote:
Machine 1
~~~~
BEGIN
DECLARE @li_num int,
@li_start int
SET @li_num = 100000
SET @li_start = 1
WHILE @li_start < @li_num
BEGIN
INSERT INTO test_del VALUES (@li_start, 'zzz', 'xxx')
IF (@li_start > @li_num)
BREAK
ELSE
SET @li_start = @li_start + 1
CONTINUE
END
END
In machine 2, i login in as USER 2 to retrieve the records at the same time...
quote:
SELECT * FROM test_del
but, the system still allow me to retrieve the records at the same time... May i know when will "Writer block reader, reader block writer" occur and in what situation
Thanks in advance
Transactions & TRY CATCH Block.
Oct 10, 2006Can anyone give a template for a stored procedure which involves a transaction and has a TRY CATCH block too...
Thanks in advance.
Create Procedure In An IF Block?
Jun 14, 2006I am writing some code generation stuff and I am trying to get a scriptlike this to work:IF (something)BEGINCREATE PROCEDURE WhateverASSELECT 1 as oneENDBut it complains about this, so I am guessing that I can't put thecreate prodcedure in an IF block.Does anyone know of a work around for this?
View 6 Replies View RelatedSQL Stmt To Exit From A Job In The ELSE Block
Mar 5, 2008Hi experts,
I have a job that has several steps.
One of the steps is of type T-SQL. I have an IF...ELSE block in it. If the if statement is true...perform an action.
Else exit from the job reporting failure.
I would like to know what sql stmts should i use to exit from the job (in the ELSE block)
I tried to use EXIT in the else stmt but it is not exiting from the job step.
Thanks in advance
IF Statement With BEGIN END Block
May 4, 2006I'm working on a stored procedure which includes an IF statement at the end of the procedure which appends my sql call with two lines. But I'm getting an error that probably comes from the fact that I have a nested BEGIN END block in my overall procedure. Can anyone tell me if my assumption is correct and help polish of the syntax?
The error I'm getting is:
Msg 156, Level 15, State 1, Procedure payments_sp, Line 55
Incorrect syntax near the keyword 'AND'.
and the sql code looks like this:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[payments_sp]
-- Add the parameters for the stored procedure here
@payment_type varchar(15),
@mydate smalldatetime
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
DECLARE @myBool1 tinyint, @myBool12 tinyint
SET @myBool1 = 1
IF (@payment_type = 'CODE1' OR @payment_type = 'CODE4')
SET @myBool1 = 1
ELSE
-- the payment type must be 'CODE1' or 'CODE2'
-- set the value to false
SET @myBool1 = 0
IF (@payment_type = 'CODE3' OR @payment_type = 'CODE4')
SET @myBool12 = 1
ELSE
SET @myBool12 = 0
SELECT
SUM(Mydatabase.dbo.[PAYMENTS].[AMT_RECD])
FROM Mydatabase.dbo.[PAYMENTS]
INNER JOIN Mydatabase.dbo.[ACCT]
ON Mydatabase.dbo.[PAYMENTS].[SOME_UID] = Mydatabase.dbo.[ACCT].[SOME_UID]
WHERE Mydatabase.dbo.[ACCT].[CLIENT] = 'MY CLIENT'
AND Mydatabase.dbo.[PAYMENTS].[DATE_RECD] = @mydate
AND Mydatabase.dbo.[PAYMENTS].[BOOL] = @myBool1
AND Mydatabase.dbo.[PAYMENTS].[SOURCE] != 'val1'
AND Mydatabase.dbo.[PAYMENTS].[SOURCE] != 'val2'
AND Mydatabase.dbo.[PAYMENTS].[SOURCE] != 'val3'
IF (@payment_type = 'CODE3' OR @payment_type = 'CODE4')
BEGIN
AND Mydatabase.dbo.[PAYMENTS].[SOURCE] != 'val4'
AND Mydatabase.dbo.[PAYMENTS].[SOURCE] != 'val5'
END
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
T-SQL Mental Logic Block
May 21, 2006SELECT src_terrier.Areacode, src_terrier.siteref, src_terrier.estatename, src_terrier.Securitised, src_terrier.unitref, src_terrier.unittype, src_terrier.unittype_count, src_terrier.tenantname, src_terrier.tenantstatus, src_terrier.tenantstatus_count, src_terrier.unitstatus, src_terrier.unitstatus_count, src_terrier.floortotal, src_terrier.floortotocc, src_terrier.initialvacarea, src_terrier.initialvacnet, src_terrier.TotalRent, src_terrier.NetRent, src_terrier.FinalRtLsincSC, src_terrier.ErvTot, src_terrier.tenancyterm, src_terrier.landact, src_terrier.datadate, src_div_mgr.div_mgr, src_portfolio_mgr.portfolio_mgr, src_centre_list.propcat
FROM src_terrier INNER JOIN src_centre_list ON src_terrier.siteref = src_centre_list.Site_Ref AND src_terrier.Areacode = src_centre_list.Division INNER JOIN src_div_mgr ON src_centre_list.Division = src_div_mgr.division INNER JOIN src_portfolio_mgr ON src_centre_list.Portfolio_no = src_portfolio_mgr.portfolio_no
WHERE (src_terrier.datadate = @dt_src_date) AND (@chr_div is null or src_terrier.Areacode = @chr_div) AND (@vch_portfolio_no is null or src_centre_list.Portfolio_no = @vch_portfolio_no) AND (@vch_prop_cat is null or src_centre_list.propcat = @vch_prop_cat)
How can I modify this function in two ways
1. I want the value of src_terrier.siteref to be matched to a different table src_tbl_budget.siteref. I want all rows in src_terrier.siteref and only the one row from the new table where the siteref is matched.
2. Also part of the new query, not only do I want it to match the siteref, I also want it to only match the mm/yyyy of the parameter entered (src_terrier.datadate = @dt_src_date) in the WHERE statement.
Is that any simpler?
Regards
Toni Chaffin
aka Toni
Internet Explorer Is Block
Dec 17, 2007
Help! On my desktop some security warning came up and my friend clicked "ok" when it said "block". Apparently it was warning her that internet explorer was trying to contact the internet. So...now internet explorer cannot display anything and it is the only browser I have on my computer. I am so frustrated I can't figure out what on earth I can change to fix this. Please...someone help me figure out why I can't contact the internet? My internet connection is fine, and the troubleshooting feature in explorer keeps telling me so.
Thanks!
Command Text Was Not Set For The Command Object Error
Sep 19, 2006Hi. 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;
}
TRY..CATCH Block Not Cetching RAISERROR()
Sep 30, 2007 I guess this is a common problem because I ran into a lot of threads concerning the matter. Unfortunately, none of them helped my situation.I am throw a RAISERROR() in my sql and my vb.net try catch block is not detecting an error. SELECT '3'
RAISERROR('testerror',10,5) Dim con As New SqlConnection Dim _sqlcommand As New SqlCommand con = New SqlConnection(Spiritopedia.Web.Globals.Settings.General.ConnectionString) _sqlcommand.CommandType = Data.CommandType.StoredProcedure _sqlcommand.CommandText = "TestFunction"
_sqlcommand.Connection = con
'The value to be returned
Dim value As New Object
'Execute the command making sure the connection gets closed in the end
Try
'Open the connection of the command
_sqlcommand.Connection.Open()
'Execute the command and get the number of affected rows 'value = _sqlcommand.ExecuteScalar().ToString()
value = _sqlcommand.ExecuteScalar()
Catch ex As SqlException Throw ex Finally
'Close the connection
_sqlcommand.Connection.Close()
End Try