I have a vb 2005 app that executes a SSIS package in threads. The SSIS package imports a large (20mb) file, does many alternation to it, exports it to the database. The SSIS execution is quite frequent and there for multi-threading is needed.
Here is the issue: If i take out the threading everything works great. The second that i add threading, and try to execute a few instances of the SSIS it starts crashing. The thing is that it errors out in different task and is completely unpredictable.
My question: 1) Can SSIS be used in a multi-threading environment? 2) If yes, How do i do it? 3) Does anyone have any suggestion as to what i should try?
p.s. Timing is definitely an issue for me. I got to get this working, tested, approved by 8/27/2006 --------- here are the 3 errors that i'm getting when i turn multi-threading on 1)Thread "WorkThread0" has exited with error code 0xC0202009.
2)An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Transaction (Process ID 112) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.".
3)The ProcessInput method on component "(i cannot display the name of the task for security reasons" (448) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
We're moving in a 4-socket (Xeon Dual Core CPU's) server. What version and product of SQL Server should I be looking for to fully take advantage of the new server's 4 processors. I'm not sure I understand the licensing per CPU. Is there any advantage in waiting for SQL Server 2008 as it concerns multi-threading and what should I be looking to spend for a SQL server solution that utilizes all four processors. Someone has told me that the SQL Server standard edition comes with 4 cpu licenses. Is this correct?
I have an SSIS package that is running the same set of stored procedures on about 50 remote database servers and importing the data through data flow tasks into a local database.
To do this I have a db table set up with the remote server and db names and using variables, i am looping through each one and getting the data. Everything works wonderfully...however, I need to find a way to be able to run multiple calls at the same time. Is it possible to multithread SSIS so that I can run 1 server retrieval on 1 thread and another retrieval on another thread? Any other suggestions on how to optimally do this?
Hi,I have SQL SERVER 2000 SP4 Enterprise , Windows 2003Enterprise onXeon 4 Processors (now with multi-threading CPU) and I havequestions whichseem weirds to me (used to have the same config withoutMulti-Threading) as following:1. SQL Server see 8 CPUs. Is this because ofmulti-threading CPUs ?2. when use SP_WHO2 ACTIVE ,2.1 Result has user SPID , which blockitself(it is UPDATE and SELECTstatements, most often)2.2 Result has user SPID , show morethan one result rows withthe same processe and command3. when use Properties of SQL Server , it has anerror 15407 in log.many thanks in advanceNiponW
Is SQL Compact Edition safe to use in ASP.NET WebApps?
And second if so, should i use transation isolation level or row locking hints to assure data quality during iniserts?
E.g. i want to assure that there is only one record named user2@domain2 and thus i want to make sure that my code will not fail in a mutli-theaded scenario and insert second one or get a key exception back: see sample
transaction or locking hit{ select from users .... if no user found insert into users }
Greetings everyone, I am attempting to build my first application using Microsofts Sql databases. It is a Windows Mobile application so I am using Sql Server Compact 3.5 with Visual Studio 2008 Beta 2. When I try and insert a new row into one of my tables, the app throws the error message shown in the title of this topic. '((System.Exception)($exception)).Message' threw an exception of type 'System.NotSupportedException'
My table has 4 columns (i have since changed my FavoriteAccount datatype from bit to Integer) http://i85.photobucket.com/albums/k71/Scionwest/table.jpg
Account type will either be "Checking" or "Savings" when a new row is added, the user will select what they want from a combo box.
Next is a snap shot of my startup form. http://i85.photobucket.com/albums/k71/Scionwest/form.jpg
Where it says "Favorite Account: None" in the top panel, I am using a link label. When a user clicks "None" it will go to a account creation wizard, and set the first account as it's primary/favorite. As more accounts are added the user can select which will be his/her primary/favorite. For now I am just creating a sample account when the label is clicked in an attempt to get something working. Below is the code used.
account.FavoriteAccount = 1;//datatype is an integer, I have changed it since I took the screenshot.
financesDataSet.BankAccount.Rows.Add(account); //The next three lines where added while I was trying to get this to work. //I don't know if I really need them or not, I receive the error regardless if these are here or not.
catch (global:ystem.InvalidCastException e) { //Stops at the following line, this error was caused by 'if (this.financesDataSet.BankAccount[num].FavoriteAccount == 1)'
throw new global:ystem.Data.StrongTypingException("The value for column 'FavoriteAccount' in table 'BankAccount' is DBNull.", e);
I have no idea what I am doing wrong, all of the code I used I retreived from Microsofts help documentation included with VS2008. I have tried used my TableAdapter.Insert() method and it still failed when it got to
if (this.financesDataSet.BankAccount[num].FavoriteAccount == 1)
in my refreshDatabase() method it still failed.
When I look, the data has been added into the database, it's just when I try to retreive it now, it bails on me. Am I retreiving the information wrong?
When running the package in VisualStudio it runs properly, but if I let this package run as part of an SQL-Server Agent job, I got the message "The script threw an exception: Exception of type 'System.OutOfMemoryException' was thrown." on my log and the package ends up with an error.
Both times it is exactly the same package on the same server, so I don't know how the debug or even if there is anything I need to debug?
We have quite a few SQL Servers running on servers with hyper threaded enabled processors. We have always left hyper threading on, even on our very busy servers but i've recently begun to question this due to comments i have received.
All our servers run SQL 2000 SP3a as "support" for hyper threading was added in SP3a
So... What do you think of Hyper Threading any why?
Over some time now, I've been developing a fairly hefty stored procedure, that does a lot of computations, and fairly few table lookups.
When I look at the performance on my server (a dual Xeon HT) I can see that it only uses 1 out of 4 possible "cpus" to work on the calculations, while the three others idle out, and was wondering if I can somehow force it to use max available CPU power?
We are experiencing some problems when running the same package on multiple threads of a C# application concurrently. The package instances seem to share a Dts.Variables collection.
I have created a test application and package to reproduce the issue. The package is very simple. It has two variables - Instance (Int32) and Content (string). The test application runs two concurrent threads that run through 25 iterations running the package. Each thread loads the package, sets the instance, and then runs the package. The package then takes the instance, sticks it in the Content variable and then writes it to a file called "FileX" where X is the Instance. Thread 1 sets the instance from 1 to 25, thread 2 sets it from 26 to 50. The test execution results in about 15% of the files containing the incorrect "instance content". That is, I'll see "42" in the file named "File14", or something along those lines. This tells me that the variables are being overwritten.
I ran another test where I created a copy of the package, so the code is identical, but the VersionGUID is different. Running the thread test application always succeeds. There is no file name/content mismatch. I then ran another test where I manually edit the XML of the package to change the VersionGUID before the package is loaded (by calling LoadFromXml). Again this never fails.
Changing the VersionGUID may work for us, but it is a total hack. Has anyone else experienced this or have any other solutions? Any thoughts on manually updating the VersionGUID to get around this problem? I hate doing this, but we are getting desperate.
If I have several stored procedures, all of them making inserts/updates to the same tables, what is the safest way to run them in sequence?
The context is an asp.net application; I have an aspx page where a click on a button (or more) will launch stored procedures in execution. I have encountered the unfortunate situation before, when stored proc #2 started long before stored proc #1 finished and this caused problems.
If I group all stroed procs in one that simply calls all of them in the needed sequence:
exec stproc1 exec stproc2 ..... exec stprocn
Will they execute in a single thread? Is there any guarantee that stproc2 will be executed when stproc1 finishes, and so on?
I've seen a few posts in the MSDN documentation (see links below) stating that the MergeSynchronizationAgent no longer requires the STA threading model in SQL Server 2005 SP1. However, I'm still receiving the following exception message in my synchronization code (where it attempts to access the SynchronizationAgent property):
The MergeSynchronizationAgent class must be instantiated on a Single-Threaded Apartment (STA) thread.
I have Service Pack 1 for SQL Server 2005 installed on both my server as well as my local client (the client is running SQL Server 2005 Express). How can I verify the correct files are there (e.g. are the some specific date/time values for the RMO/COM objects?
Disaster Recovery Options based on the following criteria.
--Currently running SQL 2012 standard edition --We have 18000 databases (same schema across databases)- majority of databases are less than 2gb-- across 64 instances approximately --Recovery needs to happen within 1 hour (Not sure that this is realistic -- We are building a new data center and building dr from the ground up.
What I have looked into is:
1. Transactional Replication: Too Much Data Not viable 2. AlwaysOn Availability Groups (Need enterprise) Again too many databases and would have to upgrade all instances 3. Log Shipping is a viable option and the only one I can come up with that would work right now. Might be a management nightmare but with this many databases probably all options with be a nightmare.
More often than not, I typically don't touch DTC on clusters anymore; however on a project where the vendor states that it's required. So a couple things here.
1) Do you really need DTC per instance or one for all? 2) Should DTC be in its own resource group or within the instance's group? 2a) If in it's own resource group, how do you tie an instance to an outside resource group? tmMappingSet right?
Hello,I am trying to construct a query across 5 tables but primarily 3tables. Plan, Provider, ProviderLocation are the three primary tablesthe other tables are lookup tables for values the other tables.PlanID is the primary in Plan andPlanProviderProviderLocationLookups---------------------------------------------PlanIDProviderIDProviderIDLookupTypePlanNamePlanIDProviderStatusLookupKeyRegionIDLastName...LookupValue....FirstName...Given a PlanID I want all the Providers with a ProviderStatus = 0I can get the query to work just fine if there are records but what Iwant is if there are no records then I at least want one record withthe Plan information. Here is a sample of the Query:SELECT pln.PlanName, pln.PlanID, l3.LookupValue as Region,p.ProviderID, p.SSNEIN, pl.DisplayLocationOnPCP,pl.NoDisplayDate, pl.ProviderStatus, pl.InvalidDate,l1.LookupValue as ReasonMain, l2.LookupValue as ReasonSub,pl.InvalidDataFROM Plans plnINNER JOIN Lookups l3 ON l3.LookupType = 'REGN'AND pln.RegionID = l3.Lookupkeyleft outer JOIN Provider p ON pln.PlanID = p.PlanIDleft outer JOIN ProviderLocation pl ON p.ProviderID = pl.ProviderIDleft outer JOIN Lookups l1 ON l1.LookupType = 'PLRM'AND pl.ReasonMain = l1.LookupKeyleft outer JOIN Lookups l2 ON l2.LookupType = 'PLX1'AND pl.ReasonSub = l2.LookupkeyWHERE pln.PlanID = '123456789' AND pl.ProviderStatus = 0ORDER BY p.PlanID, p.ProviderID, pl.SiteLocationNumI know the problew the ProviderStatus on the Where clause is keepingany records from being returned but I'm not good enough at this toanother select.Can anybody give me some suggestions?ThanksDavid
I am new to Reporting Services and hope that what I am looking to do is within capabilities :-)
I have many identical schema databases residing on a number of data servers. These support individual clients accessing them via a web interface. What I need to be able to do is run reports across all of the databases. So the layout is:
Dataserver A
Database A1
Database A2
Database A3
Dataserver B
Database B1
Database B2
Dataserver C
Database C1
Database C2
Database C3
I would like to run a report that pulls table data from A1, A2, A3, B1, B2, C1, C2, C3
Now the actual number of servers is 7 and the number of databases is close to 1000. All servers are running SQL2005.
Is this something that Reporting Services is able to handle or do I need to look at some other solution?
Greetings,We are trying to set up a set of "Leading Practices" for ourdevelopers, as well as ourselves, and hope some gentle reader canrecommend some documentation in favor of what appears to be the rightposition to take.We do not allow third party applications to run on our SQL Servers. Wewant to include DTS Packages under the definition of third partyapplications, insisting instead that the developers save theirpackages as COM Formatted files into their source code control systemsand run them from their app servers. The devlopers would like to hearthis from someone besides ourselves.While strong recomendations to remove guest access to MSDB altogetherabound, I have been unable to find a straight forward discussion ofthe advantages of structured file storage and app server off load ofDTS packages.Can anyone suggest any articles, white papers, rants, etc attemptingto formulate a solution to the benefits of taking msdb away fromguest, with the advantages of running DTS from an App server orworkstation platform, with the packages protected in source codecontrol?Thank youJohn Pollinsjpollins @ eqt . com
Guys, I need your help on this one. I have a problem here. There is an exception on my conn.Open. It said that "SQL Exception was unhandled by user code. Cannot open database requested in login 'MUSIC STORE'. Login fails.Login failed for user 'IT785P13student'." Does anyone have any idea what this means?
I downloaded a web site from internet and tried to open it in visual web developer express edition but it gave an error and the code and the error was: CODE- Return CType(Me.GetPropertyValue("Theme"),String) ERROR -An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. An answer will be very good for me
Could somebody please tell me what is wrong with this SQL Statement. I'm trying to run it in SQL Server 2000, and keep getting the error: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'GROUP' but as far as I can see, there isn't anything wrong near "GROUP"..." SELECT b.ColorID, b.ColorName, " + " MAX(a.ColorID) AS DesignerProductAvailability_ColorID, " +" MAX(a.Quantity) AS DesignerProductAvailability_Quantity, " + " MAX(a.ProductID) AS DesignerProductAvailability_ProductID " +" FROM DesignerProductAvailability a " + " INNER JOIN ColorList b " +" ON b.ColorID = DesignerProductAvailability_ColorID " + " WHERE DesignerProductAvailability_ProductID = @ProductID AND " +" DesignerProductAvailability_Quantity > 0 " + " ORDER BY b.ColorName ASC " + " GROUP BY b.ColorName "; Any help would be greatly appreciated. Thanks in advance!
I am passing the values to a class which has a method to insert the date into database. How to retrive this exception thrown in this method and disply to user.
We have load-balanced sql server mcs and users will be accessing all the time. We are maintaining user profiles in one mc. Here is the problem. We have a sp which reads profile from one particular mc always. If the mc is down, the sp does not fail gracefully. Instead it causes exception. Is there a way to access sql servers alternatively ie if one server is not responding read data from another sql?
I am using MSSQL Server 8.0 , and the driver jtds.jar to connect the database for SQL operation .
I can do all the sql operation . But after the creation of a trigger for a table , I used the same java program to insert rows into table .
-------The java code I used --------------------- String connectionString = "jdbc:jtds:sqlserver://localhost:1433/master"; try { Class.forName(jdbcDriverClass); Connection con = DriverManager.getConnection(connectionString,userN ame,password); Statement st = con.createStatement(); String query = "insert into tabname values('11','ROBERT')"; st.executeUpdate(query); } catch (Exception e) { e.printStackTrace(); } -------------------------------------------------
and got the exception
//////////////////////////////////////////////////////// java.sql.SQLException: executeUpdate can't return a result set at net.sourceforge.jtds.jdbc.TdsStatement.executeUpda te(Unknown Source) ////////////////////////////////////////////////////////
What will be the problem here ? Is it bcos of any time out ? Please help !!!
I have just recovered data from a hard drive crash. I installed a fresh copy of Windows Server 2003 with sp2. Have SQL Standard Running with sp2, and VS 2005 standard running sp1. I have several webs on this server, was able to get most up and running fine, but one web and database cannot make the connection after recovery. I had to reapply permissions and folder security to all webs, and change provided dbo privaledges to network_services. However I am still getting the following error: Server Error in '/bpe' Application.-------------------------------------------------------------------------------- The EXECUTE permission was denied on the object 'aspnet_CheckSchemaVersion', database 'Fitness1stDb', schema 'dbo'. 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.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object 'aspnet_CheckSchemaVersion', database 'Fitness1stDb', schema 'dbo'. 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: [SqlException (0x80131904): The EXECUTE permission was denied on the object 'aspnet_CheckSchemaVersion', database 'Fitness1stDb', schema 'dbo'.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857354 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734966 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +149 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +886 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +415 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135 System.Web.Util.SecUtility.CheckSchemaVersion(ProviderBase provider, SqlConnection connection, String[] features, String version, Int32& schemaVersionCheck) +367 System.Web.Security.SqlMembershipProvider.CheckSchemaVersion(SqlConnection connection) +85 System.Web.Security.SqlMembershipProvider.GetPasswordWithFormat(String username, Boolean updateLastLoginActivityDate, Int32& status, String& password, Int32& passwordFormat, String& passwordSalt, Int32& failedPasswordAttemptCount, Int32& failedPasswordAnswerAttemptCount, Boolean& isApproved, DateTime& lastLoginDate, DateTime& lastActivityDate) +1121 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved, String& salt, Int32& passwordFormat) +105 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved) +42 System.Web.Security.SqlMembershipProvider.ValidateUser(String username, String password) +83 System.Web.UI.WebControls.Login.OnAuthenticate(AuthenticateEventArgs e) +160 System.Web.UI.WebControls.Login.AttemptLogin() +105 System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +99 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +163 System.Web.UI.WebControls.Button.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) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
--------------------------------------------------------------------------------Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42
it gives error while calling a sql stored procedure as "INPUT STRING WAS NOT IN A CORRECT FORMAT". I am providing the code here.public void get_issid(string cse_email, string tech_email, string subject, string issue_details, string response, string solv_date, out int issid) { // Establish ConnectionSqlConnection oConnection = GetConnection(); // build the commandSqlCommand oCommand = new SqlCommand("get_issid", oConnection); oCommand.CommandType = CommandType.StoredProcedure; // ParametersSqlParameter paracse_email = new SqlParameter("@cse_email", SqlDbType.VarChar, 50); paracse_email.Value =cse_email; oCommand.Parameters.Add(paracse_email); SqlParameter paratech_email = new SqlParameter("@tech_email", SqlDbType.VarChar,50); paratech_email.Value = cse_email; oCommand.Parameters.Add(paratech_email);SqlParameter parasubject = new SqlParameter("@subject", SqlDbType.VarChar, 50); parasubject.Value = subject; oCommand.Parameters.Add(parasubject);SqlParameter paraissue_details = new SqlParameter("@issue_details", SqlDbType.VarChar, 500); paraissue_details.Value = issue_details; oCommand.Parameters.Add(paraissue_details);SqlParameter pararesponse = new SqlParameter("@response", SqlDbType.VarChar, 500); pararesponse.Value = response; oCommand.Parameters.Add(pararesponse);SqlParameter parasolv_date = new SqlParameter("@solv_date", SqlDbType.DateTime); parasolv_date.Value = solv_date; oCommand.Parameters.Add(parasolv_date);SqlParameter paraissid = new SqlParameter("@issid", SqlDbType.Int);paraissid.Direction = ParameterDirection.Output; oCommand.Parameters.Add(paraissid); try { oConnection.Open(); oCommand.ExecuteNonQuery();issid = int.Parse(paraissid.Value.ToString()); }catch (Exception oException) {throw oException; } finally { oConnection.Close(); } }
the stored procedure is:
create proc [dbo].[get_issid](@tech_email varchar(50), @cse_email varchar(50),@subject varchar(50),@issue_details varchar(500),@response varchar(500),@solv_date datetime, @issid int output) as select @issid=tech_response.issue_id from tech_response,issue_details where tech_response.tech_email=@tech_email and tech_response.cse_email=@cse_email and tech_response.subject=@subject and tech_response.issue_details=@issue_details and response=@response and solv_date=@solv_date and tech_response.issue_id=issue_details.issue_id requested to help in this
