Ideal Event Handling Technique
Apr 13, 2007
Hi All,
We have this SSIS package that serves a ETL role, but sometimes when the package crashes we wanted our support personnel to be aware of it, so we did implement an Event handler Package, executable is the package and Event Handler on taskFailed. The idea is if any of the Tasks fail, then trigger the event handler.
Now recently I found out while debugging the package, one of the DFT's failed, and the control went to the Event handler. But thenrest of the Task's are executing and the package finished successfully.
How do I ensure that if at all some Tasks fail, the control should invoke the task at the event handler and end the package run.
One more query, what is the ideal and best way of Event handling to be followed in case of ETL jobs ?
Thanks in Advance.
View 10 Replies
ADVERTISEMENT
Jul 12, 2004
This is DataSet retriving code from IBUYSPY Events Module.
public DataSet GetEvents(int moduleId) {
// Create Instance of Connection and Command Object
SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);
SqlDataAdapter myCommand = new SqlDataAdapter("GetEvents", myConnection);
// Mark the Command as a SPROC
myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
// Add Parameters to SPROC
SqlParameter parameterModuleId = new SqlParameter("@ModuleId", SqlDbType.Int, 4);
parameterModuleId.Value = moduleId;
myCommand.SelectCommand.Parameters.Add(parameterModuleId);
// Create and Fill the DataSet
DataSet myDataSet = new DataSet();
myCommand.Fill(myDataSet);
// Return the DataSet
return myDataSet;
}
My Question is
1) Is this ideal way to retrive data?
2) Will it return connection back to pool?
View 4 Replies
View Related
Mar 12, 2004
What is the ideal hardware configuration for SQL Server 2000? We have about 2 dozen databases - ranging in size from 500 MB up to 90 GB. All databases have very heavy traffic from multiple users on the same app. There are about 8 reads for every write, averaging about 8 million writes per 24-hour period.
Currently we have these databases split across 2 servers with 4 2.4 Ghz hyperthreaded processors and have 8 GB of RAM. The disks are 10,000 RPM SCSI, and are all in one very large physical disk array split into 2 logical drives per server. Logs and data for multiple databases are all sharing the same array.
Performance is becoming a pressing issue. Our Network administrator is 100% that the hardware configuration is not an issue, and I am 100% convinced it is at least a major part of the issue. Does anyone here feel that it may be the issue?
View 2 Replies
View Related
Nov 12, 2006
Hi
What should be the ideal number of joins that should be used in a query? In some books it says anything beyond 7 joins should be re-considered. Is this correct. We have application where the number of joins are more than 25 and in some cases it is beyond 50. I am looking for some guidelines as it will help to design the table in the best possible way. I am not looking for reporting type of queries; instead they are all the business logic.
thanks
Sanjay
View 3 Replies
View Related
May 12, 2008
I am currently deploying a desktop application that uses SQL Express as a local data source. I load data from other sources into the local database and then use that data for my application. Currently I'm deploying the MDF file into a Data folder within my application folder. I'm finding that most of the users that will be using this application has only power user rights, not admin rights.
My question is this. Is using a data folder within my application folder the ideal place to store this data or is there other more benefitial places within the users computer that would help reduce rights and permissions issues? When users upgrade to Vista will this cause any problems that another location might help me avoid?
Appreaciate any opinions.
View 6 Replies
View Related
Jun 2, 2015
Recently we migrated our environment to 2012.
We are planning to implement Xevents in all the servers in place of Trace files and everything is working fine.
Is it possible to configure Extended event to trigger a mail whenever any event (example dead lock) occurs.
I have gone through so many websites but i never find.
View 13 Replies
View Related
Oct 25, 2011
My SQL Server 2005 SP4 on Windows 2008 R2 is flooded with the below errors:-
Date 10/25/2011 10:55:46 AM
Log SQL Server (Current - 10/25/2011 10:55:00 AM)
Source spid
Message
Event Tracing for Windows failed to send an event. Send failures with the same error code may not be reported in the future. Error ID: 0, Event class ID: 54, Cause: (null).
Is there a way I can trace it how it is coming? When I check input buffer for these ids, it looks like it is tracing everything. All the general application DMLs are coming in these spids.
View 2 Replies
View Related
Apr 8, 2008
I have been testing with the WMI Event Watcher Task, so that I can identify a change to a file.
The WQL is thus:
SELECT * FROM __InstanceModificationEvent within 30
WHERE targetinstance isa 'CIM_DataFile'
AND targetinstance.name = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\AdventureWorks.bak'
This polls every 30 secs and in the SSIS Event (ActionAtEvent in the WMI Task is set to fire the SSIS Event) I have a simple script task that runs a message box).
My understanding is that the event polls every 30 s and if there is a change on the AdventureWorks.bak file then the event is triggered and the script task will run producing the message.
However, when I run the package the message is occurring every 30s, meaning the event is continually firing even though there has been NO change to the AdventureWorks.bak file.
Am I correct in my understanding of how this should work and if so why is the event firing when it should not ?
View 2 Replies
View Related
May 31, 2007
Server 2003 SE SP1 5.2.3790 Sql Server 2000, SP 4, 8.00.2187 (latest hotfix rollup)
We fixed one issue, but it brought up another. the fix we applied stopped the ServicesActive access failure, but now we have a failure on MSSEARCH. The users this is affecting do NOT have admin rights on the machine, they are SQL developers.
We were having
Event Type: Failure Audit
Event Source: Security
Event Category: Object AccessEvent ID: 560
Date: 5/23/2007
Time: 6:27:15 AM
User: domainuser
Computer: MACHINENAME
Description:
Object Open:
Object Server: SC Manager
Object Type: SC_MANAGER OBJECT
Object Name: ServicesActive
Handle ID: -
Operation ID: {0,1623975729}
Process ID: 840
Image File Name: C:WINDOWSsystem32services.exe
Primary User Name: MACHINE$
Primary Domain: Domain
Primary Logon ID: (0x0,0x3E7)
Client User Name: User
Client Domain: Domain
Client Logon ID: (0x0,0x6097C608)
Accesses: READ_CONTROL
Connect to service controller
Enumerate services
Query service database lock state
Privileges: -
Restricted Sid Count: 0
Access Mask: 0x20015
Applied the following fix
http://support.microsoft.com/kb/907460/
Now we are getting
Event Type: Failure Audit
Event Source: Security
Event Category: Object Access
Event ID: 560
Date: 5/23/2007
Time: 10:51:23 AM
User: domainuser
Computer: MACHINE
Description:
Object Open:
Object Server: SC Manager
Object Type: SERVICE OBJECT
Object Name: MSSEARCH
Handle ID: -
Operation ID: {0,1627659603}
Process ID: 840
Image File Name: C:WINDOWSsystem32services.exe
Primary User Name: MACHINE$
Primary Domain: domain
Primary Logon ID: (0x0,0x3E7)
Client User Name: user
Client Domain: domain
Client Logon ID: (0x0,0x60D37C1A)
Accesses: READ_CONTROL
Query service configuration information
Query status of service
Enumerate dependencies of service
Query information from service
Privileges: - Restricted Sid Count: 0 Access Mask: 0x2008D
View 4 Replies
View Related
Nov 2, 2007
Hi all,
Can we get the event properties by using a query?
Are there any extended stored procuder to get the above?
Scenario:
>Desktop>Right Click on My Computer
>Go to Manage and click
>Expand System Tools
>Expand Event Viewer
>Application
click on one event.We can get the log info which is the manual procudure.
But now i want to get the event properties through the Query analyzer...
Any help would be great?
Thanks,
View 4 Replies
View Related
Jan 19, 2008
Hello Members and Contributors and All the Peoples, Assalamualikum;
I am a Univesity Student and Currently i am creating my own website on ASP.
I have little bit of problem in Personal Message or Private Message Section.
What i want is that my Message Box Field of PM Should render HTML tags.. like here , in this website, Message Box renders HTML tags.. also here it has option to see the HTML view...
ALSO in my SQL Database the maximum length of my PM is 4,000... obviously that field will not store if it increases the limit. So how i increase my PM Field Length. Because i have put the option of replying the message... and old message will be shown as quoted message so all the text is very much important and what technique should use to STOP <a href> tags
View 1 Replies
View Related
Jul 20, 2005
I'm a newbie when it comes to the SQL Server side of things. I've been doingAccess on and off for years.I've got a form that is the main point of entry for all data.As a matter of technique, should I use one stored procedure to validate data(testing for uniqueness mostly) then another one to actually write the datato the tables?Does it make a difference one way or another to the performance?Do I gain any flexibility in my app dong it that way (Access Data Project)?Your help is appreciated.--Jake
View 2 Replies
View Related
Nov 23, 2007
Hi, we currently use binding to bind data from an SQL Query to a DataGrid, the problem is that we offer the choice to the user to choose the field that he want to see in the grid.
We currently include ALL optional field in the query even if they are not used by the grid, that make a realy big query with MANY inner and left outer join.
Whats the best techinique to dynamicaly construct my query with the field that the user choose.
All suggestion or webpage link will be appreciated.
Thanks a lot.
View 1 Replies
View Related
Aug 8, 2007
Questoin
I am using Sql Server 2000.
I have a table named Cities which has more than 2600000 records.
I have to display the records for a specific city page wise.
I don't want to compromise with performance.
Can anyone has the idea?
Waiting for your fruitful response.
Happy Day And Night For All
Muhammad Zeeshanuddin Khan
View 1 Replies
View Related
Aug 28, 2004
I need to write a query in an SP that returns either yes or no. Is it bad technique to use the return value of the SP for this? Should I use a scalar or output parameter instead? If so which?
Thanks,
Steve
View 7 Replies
View Related
Jul 23, 2005
Sorry for my bad englishWhat is the optimization technique used by SqlServer dbms?Thanks
View 1 Replies
View Related
Jul 20, 2005
Hi AllI'm creating some SPs and I've got a query which is inserting datainto a table with a a unique constraint:CREATE TABLE [fil_Films] ([fil_ID] [int] IDENTITY (1, 1) NOT NULL ,[fil_Film] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,CONSTRAINT [PK_Tfil_Film] PRIMARY KEY NONCLUSTERED([fil_ID]) ON [PRIMARY] ,CONSTRAINT [IX_fil_Films] UNIQUE NONCLUSTERED([fil_Film]) ON [PRIMARY]) ON [PRIMARY]GOWhen I insert data, should I check in the SP to see if there is anexisting record or simply catch the error if it already exists? Whichis the better technique? My current SP looks like:CREATE PROCEDURE spAddFilm (@Type varchar(50)) ASDECLARE @Count intSET NOCOUNT ONSELECT @Count = Count(fil_ID) FROM fil_Films WHERE fil_Films.fil_Film= @TypeIF @COUNT IS NULLBEGININSERT INTO dbo.Tfil_Film (Tfil_Type) VALUES (@Type)RETURN 1 -- OKENDELSEBEGINRETURN 2 -- ExistsENDGOThanksSam
View 2 Replies
View Related
Jan 15, 2008
Been handed a system which inserts millions of records per day into a single table with a composite 'wide' index (they tell me it's the only way to achieve uniqueness) and they run large financial reports from this 300,000,000 row table and of course we are I/O is an understatment.
I wish to split the data feed (inserts) workload away from the reporting tables however I need method to transfer the feed data into the report tables and control the volume of traffic. first choice is replication but is this sufficiently robust enough for a commodeties system? If not any ideas?
View 2 Replies
View Related
Jul 20, 2005
Looking for some insight from the professionals about how they handlerow inserts. Specifically single row inserts through a storedprocedure versus bulk inserts.One argument are people who say all inserts (and updates and deletionsI guess) should go through stored procedures. The reasoning is thatthe developers that code the client side have no reason to understandHOW the data is stored, just that it is. Another problem is an insertthat deals with multiple tables. It would be very easy for thedeveloper to forget a step. That last point also applies to businesslogic. In my case, adding a security to our SecurityMaster can touch 1to 4 tables depending on the type of security. Also, certain fieldsare required while others are set to null for depending on the type.Because a stored procedure cannot be passed datasets but only scalarvalues, when you need to deal with multiple (i.e. bulk) rows you arestuck using cursors. This post is NOT about the pros and cons ofcursors. There are plenty of those on the boards (some of themprobably started by me and showing my understanding (or morecorrectly, lack of) of the way to do things). Stored procedures alsogive you the ability to abort and/or log inserts that cannot happenbecause of contraints and/or business rule failures.Another approach is to write code (not accessible from outside thedatabase) that handles bulk inserts. You would need to write in rulesto "extract" or "exclude" rows that do not match constraints orbusiness rules otherwise ALL the inserts would fail because of one badrow. I guess you could put the "potential" rows into a temp table.Apply your rules to the temp table and delete / move rows that wouldfail. Any rows left can that be bulk inserted. (You could also use therows that were moved to another temp table for logging why theyfailed.)So that leaves use with two possible ways to get data into the system.A single row based approach for client apps and a bulk based forinternal use. But that leaves use with another problem. You now havebusiness logic in TWO separate areas. You have to remember to modifycode or fix bugs in multiple locations.For those that are still reading my post, my question is...How do you handle this? What is the approach you take?
View 4 Replies
View Related
Jul 20, 2005
I've been asked to turn our single-user database system into a multi-usersystem. At present, each user has a copy of the MSDE on their desktopmachine and uses our program to access it. In future, we would like tocentralise our MSDE instance and allow multiple users to access it. Inorder to facilitate this, we are going to only allow one user write accessto the system at a time (I know, its a kludge, but the system was neverdesigned for multiple users in the first place).I have a single, simple question this being the case: can I update a single"read-only" bit field in a table of the database in order to flag to otherusers that the system is in read-only mode in a way that avoids concurrencyissues? ie. does an "UPDATE" query lock and unlock? ( I suspect the answeris yes! ). If anyone else has experience of these things, I would alsoappreciate some tips on how best to proceed.ThanksRobin
View 7 Replies
View Related
Apr 14, 2007
hello.
How to do Search in the method which is done in MSDN library 2005 (local application)?
i.e enabling a search filter and then (most incredibly) as you type the criteria, the
list gets updated depending on the characters entered.
for eg. you type: loo
and obtain the following display:
Look in dialog box
Look tab
lookaheads
l
l
l
l
l
l
l
l
l
l
l
l
l
How to use this fast searching technique in vb.net 2005 and the items to be searched in
sql server 2005 express??
View 1 Replies
View Related
Apr 11, 2008
Hi folks, I have implemented this technique to simplify SCD loads and also to maintain consistent units of work during update/insert of a single row. Wanted to get your feedback on this technique: performance, transaction issues, etc.
I send all rows to an OLE DB Command that performs both update and insert for a single row in a single command:
Code Snippet
UPDATE PROPERTY SET ORD_TERM_DT = ? WHERE ACCOUNT_NBR = ? AND ORD_TERM_DT = '9999-12-31 23:59:59';
INSERT INTO PROPERTY (
ACCOUNT_NBR
, APPRAISAL_COMPANY_CD
, .....
, ORD_TERM_DT
) VALUES (?, ...,?);
This way I can guarantee that if the termination (update) of an old row (say, row 10) succeeds, but insert of the new row 10 fails, that it will roll back. Otherwise, row 10 will get terminated without being replaced with a current record...
Performance: load of 7,734 changed records into a table of 6.8M existing records was roughly 8 seconds. The data flow task container TransactionOption = Required.
View 4 Replies
View Related
Apr 8, 2008
I'm looking to remove hundereds of legacy procs, triggers, functions etc in a DB. Is there a DMV, sys.<something> command or technique that will tell me the last night an object was accessed or executed?
View 2 Replies
View Related
May 22, 2008
I have Came to know that Pinning concept is removed in sqlserver 2005 .
So is there any alternate for that .Any links regardint that which provides information.
View 5 Replies
View Related
Nov 14, 2006
Colleagues:
I'm working through the DM tutorials and I can't select a data mining technique when creating a new mining structure in BI Studio - BI Studio locks up totally. Have installed SQL Svr 20005 SP1 and components from feature pack. Analysis Server service is running, have set the properties in the DM project properties to the running instance of Analysis Services. Have read the various posts on the forum and have double checked my configuration - all seems as it should be.
All suggestions appreciated - very much looking forward to working with the technology.
Michael (michael.dataSense@sympatico.ca)
View 3 Replies
View Related
Jul 12, 2007
Hello geniuses
First of all I would like to announce that this is my first time I post here.. However, I'm pretty sure that I'm in the best place to ask what I want. To cut the story short, I'm querying SQL database on a remote machine and having the result saved (mapped) to another table on another database on the same remote machine. The thing is the destination table was empty before the query was run the first time. I have been searching for some smart way so that when I modify the source tables that my query is based on, it doesn't affect except the modified rows. In other words, it should be like if the row is already there, do nothing. otherwise, it updates the existig record. else, it's a new record and it's inserted. I think what i need will include some coding for sure, yes? I don't know if i'm clear about the requirement or not though! but I know that you are experts and can direct me. Waiting for your valuable replies.
Sherif Magdi
View 11 Replies
View Related
Nov 25, 2007
Hi,Env: MS SQL Server 2000DB Info (sorry no DDL nor sample data):tblA has 147249 rows -- clustered index on pk (one key ofdatatype(int)) andhas two clumns, both are being used in joins;intersecTbl4AB has 207016 rows -- clustered index on two fks andthis intersection table has six colums but only the two fks are beingused for joins;tblB has 117597 rows -- clustered index on pk (one key ofdatatype(bigint)) andhas four columns but only its key are being used for joinsA complex query involving the above the three tables includes innerand outer joins, aggregate, sorting, predicate, math function, derivedtable etc.On the first run, the query takes about 4200ms to finish;after some research on index optimization, I provided some index hint,then the query runs atabout 3000ms. (That was yesterday).Just now I realized that MS SQL Server 2000 is quite "intelligent", Ithink it saves search termsinto cache because the second time search of the a same term is muchmuch faster. Now, a couple of questions:a) if I construct a long long list of "common" terms andprogrammatically let the sql server to cache them would it speed upthe overal query performance in my case? (Or it may depend on thequality of the "common" terms?) and if your answer is yes (supposedlyyou've been there, do you have to know where I could find such a"common" term list, for everyday life or the general public?)b) what other techniques out there to speed up the above describedquery? Bring it down to 1000ms would be most desirable.Thanks.
View 1 Replies
View Related
May 20, 2007
there is any service or technique to reflect database changes to form?
am mean if there is two people update same data and one of them
does the update i need (search) on service in sql server or .net that(or triger) that can automatically reflect changes to the form control that display data automatically when data changes before the another persone make second aupdate, so he can see the update made by the first person pefore he make another update.
Thanks So Much for who try to answer.
View 3 Replies
View Related
Oct 22, 2007
We recently upgraded to SQL 2005 from SQL 2000. We have most of our issues ironed out however about every 1 minute there is a message in the Application Event log and the SQL log that states:
EVENT ID 18456 Login Failed for the users DOMAIN/ACCOUNT [CLIENT: <local machine>]
This is a state 16 message which I thought meant that the account does not have access to the default database. The account is actually the account that the SQL services run under.
Any ideas? We can't seem to figure this one out. We actually upgraded to 2005 from 2000 and had an error appear after every reboot that prevented the SQL Agent from running(This application has failed to start because GAPI32.dll was not found. Re-installing the application may fix this problem.) We did a full uninstall of SQL and reinstalled fresh and restored the databases from .bak files and that is when the EVENT ID 18546 started occuring every minute.
We don't have any SQL heavy hitters here so please be detailed with any possible solutions. That you very much for any help you can provide!
David
View 5 Replies
View Related
Nov 27, 2007
I had a problem with the ntext datatype. I need to strip the HTML tags out of a ntext datatype column. I have sample query for that, which works fine for STRING, as stuff is the string function, what to do for ntext field.
=======The Process follows like this =========
--**************************************
--
-- Name: A relational technique to strip
-- the HTML tags out of a string
-- Description:A relational technique to
-- strip the HTML tags out of a string. Th
-- is solution demonstrates how to use simp
-- le tables & search functions effectively
-- in SQL Server to solve procedural / ite
-- rative problems.
-- This table contains the tags to be re
-- placed. The % in <head%>
-- will take care of any extra informati
-- on in the tag that you needn't worry
-- about as a whole. In any case, this t
-- able contains all the tags that needs
-- to be search & replaced.
CREATE TABLE #html ( tag varchar(30) )
INSERT #html VALUES ( '<html>' )
INSERT #html VALUES ( '<head%>' )
INSERT #html VALUES ( '<title%>' )
INSERT #html VALUES ( '<link%>' )
INSERT #html VALUES ( '</title>' )
INSERT #html VALUES ( '</head>' )
INSERT #html VALUES ( '<body%>' )
INSERT #html VALUES ( '</html>' )
go
-- A simple table with the HTML strings
CREATE TABLE #t ( id tinyint IDENTITY , string varchar(255) )
INSERT #t VALUES (
'<HTML><HEAD><TITLE>Some Name</TITLE>
<LINK REL="stylesheet" HREF="/style.css" TYPE="text/css" ></HEAD>
<BODY BGCOLOR="FFFFFF" VLINK="#444444">
SOME HTML text after the body</HTML>'
)
INSERT #t VALUES (
'<HTML><HEAD><TITLE>Another Name</TITLE>
<LINK REL="stylesheet" HREF="/style.css"></HEAD>
<BODY BGCOLOR="FFFFFF" VLINK="#444444">Another HTML text after the body</HTML>'
)
go
-- This is the code to strip the tags out.
-- It finds the starting location of eac
-- h tag in the HTML string ,
-- finds the length of the tag with the
-- extra properties if any. This is
-- done by locating the end of the tag n
-- amely '>'. The same is done
-- in a loop till all tags are replaced.
BEGIN TRAN
WHILE exists(select * FROM #t JOIN #html on patindex('%' + tag + '%' , string ) > 0 )
UPDATE #t
SET string = stuff( string , patindex('%' + tag + '%' , string ) ,
charindex( '>' , string , patindex('%' + tag + '%' , string ) )
- patindex('%' + tag + '%' , string ) + 1 , '' )
FROM #t JOIN #html
ON patindex('%' + tag + '%' , string ) > 0
SELECT * FROM #t
rollback
View 1 Replies
View Related
Mar 9, 2007
Hi I have a production table in SQL Server 2005 that has approx 500,000 records---every 6 hours this table needs to be truncated and filled
The basic SSIS package uses a script compentant and imports the data into a staging table which has the same structure as the production table. I have a final Execute SQL Task that Truncates the production table and does a Insert into production-select * from stage table.
Takes around 30 seconds to run this last Execute SQL Task--problem is there is a risk that our webservice will query this table during the Execute SQL Task and return incorrect results.
Q1: In this last Execute SQL Task if I used a BEGIN TRANSACTION and COMMIT TRANSACTION; will this be any quicker ?
Q2: In this last Execute SQL Task- would it be better to use a RENAME TABLE technique in TSQL--any code examples ??
Q3:Is there any way in TSQL I can compare EVERY FIELD in two tables ie Stage and Production which have identical data structures and figure out a way to update only the records that changed? Is SQL Server Replication the best way to do this
thanks kindly
Dave
View 9 Replies
View Related
Apr 21, 2006
1) We are writing a custome Source component for Oracle with OCI calls, Could some one please let me know how to Enable Error Handling for the Same,
2) Is it possible to write Custome Error Handeling Component for SSIS? if yes could you please help me on how to write it.
Thanks in advance.
View 1 Replies
View Related
Feb 25, 2006
Hi,
I am executing script like this. How to check for the errors if "master..xp_cmdshell @bcpCommand" fails. Is there any way to verify that BCP is completed successfully
DECLARE @FileName varchar(50),
@bcpCommand varchar(2000)
SET @FileName = 'E:TestBCPOut.txt'
SET @bcpCommand = 'bcp "SELECT * FROM pubs1..authors ORDER BY au_lname" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -c -U -P'
EXEC master..xp_cmdshell @bcpCommand
Thanks in Advance,
View 10 Replies
View Related
