We have Oracle-based enterprise resource planning (ERP) application that facilitates order entry, invoicing, dispatch, stock management, and purchasing. However, it took as much as 30 seconds to perform queries from the remote offices, which were linked via Frame Relay.
Any experiences or ideas shared will be greatly appreciated.
Best,
Steve
My application which ran perfectly well yesterday suddenly stopped working in this morning with following error message. I didn't change anything, at least I believe. Interesting thing is that I don't use any Oracle connection but connects to MS SQL Server 2005 Express version locally. Error messages are different according to the connection string though I don't think it makes difference:
ConnectionString = <add key="Databasehandtool1" value="Data Source=.SQLEXPRESS;Database=handtool;SSPI=true" />ErrorMessage from VS2008 ="Unable to get records. Object reference not set to an instance of an object."
ConnectionString = <add key="Databasehandtool1" value="Data Source=.SQLEXPRESS;Database=handtool;User Id=handtool_DB;Password=mypassword" />ErrorMessage from VS2008 = "Unable to get records. Unable to connect to database. ORA-12154: TNS:could not resolve the connect identifier specified." By the way, the application was generated from IronSpeed 5.1 Enterprise. My development environment is : VS2008, SQL2005 EXPRESS, Windows XP SP2, ASP.NET 2.0
We have actually an Oracle server in the network though. My gut feeling is that something might have changed in my network server during the night but how come it can possibly affect my application running on my local PC. I checked the connection of local SQL Express Server from SQL Server Management Studio and it connected well with UserID=handtoo_DB and showed all tables and stored procedures required for the application. So there is no problem in SQL Server database side.
I would welcome and appreciate any input.
fudata
Hi,
I have developed .NET application on 32-bit system with Oracle 10g and works very well. When I have deployed the application on the Client server I am getting the following error
"Attempt to load Oracle Cleint libraries threw BandImageFormatException. This problem will occur when running in 64 bit mode with the 32 bit Oracle client components installed"
I did have installed Oracle x64 bit client on the system still getting the same error. I have tried to uninstall the oracle instances and also installed ODACHome for .NET as well (64-bit). No luck so far.
I tried to use VS 2005 to connect to the Oracle instance and I still go the same error.
I tried to used the System.Data.OracleClient namespace with following code snippet
OracleConnection con = new OracleConnection();
con.ConnectionString = "Data Source=testdb; User ID=scott; Password=tiger";
con.Open();
con.Close();
Same error.
Is there any way that you guys know of or if anyone has come across this situation please advise me on how to over come this issue.
Thank you very much
I need advice of a powerfull laptop can bear the future extensions ofOracle editions and implementing installations of all Oracle productson it ?
View 3 Replies View RelatedBasically, I have a web-based application that queries aggregates from several big tables that grow rather quickly. I feel I am fairly accomplished at tuning individual queries and have optimized these particular stored procedures as much as I can and often see great results for a period of time. However, after time passes, it seems the stored procedures begins performing really badly. What's frustrating is that I have traced the server, located a stored procedure that consistently executes with an abnormally high duration, but when I run the exact same text directly against the sql server it returns instantly.
If I recompile the stored procedure using my direct connection by including the WITH RECOMPILE clause to the EXEC statement, the problem remains for the web-app. However, if I add the WITH RECOMPILE to the actual stored procedure, it is instantly resolved. I can then remove the WITH RECOMPILE from the stored procedure text and things will remain speedy for a period of time before they again slip back.
I'm curious as to what I can do from a SQL standpoint to correct for this. For now, the app team is setting a counter for each execution and then issuing a WITH RECOMPILE when the counter limit is exceeded.
I apologize if this question has most been asked before and I have attempted a review of the forums but was unable to find any material. Any suggestions, advice, links or general assistance would be most appreciated.
Dear Experts,
I'm a DBA, Working for a Product based company.
We are implementing our product for a certain client of huge OLTP.
our reports team is facing problem (error: all the reports are timed out).though the queries are written properly, Each query is taking some minutes of time.
I've given the command DBCC DROPCLEANBUFFERS.
the time immediately dropped to 10 sec.
now my question is :
please suggest me the DBCC commands or any DBA related commands to improve the performance of the application for my reports team.
Thanks in advance.
Vinod
Hello all,I've been recruited to assist in diagnosing and fixing a performance problemon an application we have running on SQL Server 7.The application itself is third party software, so we can't get at thesource code. It's a Client Management system, where consultants all overthecountry track their client meetings, results, action plans, etc. , and hasapparently been problematic for a long time now. I came into thisinvestigationin mid-stream, but here's the situation as I understand it:We have users reporting it's slow, with no discernable pattern with respectto what part of the application they're using or now particular time of day.I am told that it doesn't appear to be a bandwith or computer resourceproblem. They apparently added two app servers a year or so ago, whichtemporarilyimproved the performance. We're using a nominal percentage of CPU andmemory.There are three large tables (approx 8 million rows) that are queried often,as users click to see their calendar of appointments or review past meetingswith a client, etc. The activity on these tables is over 90% reads(SELECTS) with about 10% INSERTS/UPDATES. We have attempted to run the IndexAnalyzer Wizard twicebut so far it just seems to hang (it could be that the workload file is toobig?) . So, what we're doing now is isolating the SELECT statements thattake a long time to run and manually comparing them to the indexes thatexist on these large tables. Since we can't alter the SQL source code,we're trying to alter the indexes to improve performance.What I would like to know is, is there a good way to get benchmarkmeasurements so we can explicitly measure any performance changes? Also, doyou thinkwe're going about this the right way, or is there some other avenue we couldbe looking at to improve performance?I recognize that performance questions are tricky to post/answer in anewsgroup, because usually you need more information than is provided. Theproblem isthat this is a high profile investigation (they're hauling us into meetingsevery two days to report our progress) and I need to be able to convincinglystate that we have either improved performance by X% , or that it is theapplication itself that's the problem and we're stuck with it.Any thoughts would be deeply appreciated.Thanks and best regards,Steve
View 5 Replies View RelatedI have a 32-bit application which sometimes runs against 64-bit servers. When it does, I can't read the performance counters published by the FTS service because they're 64-bit only. I get event #1022 from PerfLib in the event log:
Windows cannot open the 64-bit extensible counter DLL msftesqlFD in a 32-bit environment. Contact the file vendor to obtain a 32-bit version. Alternatively if you are running a 64-bit native environment, you can open the 64-bit extensible counter DLL by using the 64-bit version of Performance Monitor. To use this tool, open the Windows folder, open the System32 folder, and then start Perfmon.exe.
Does Microsoft make available 32-bit counter libraries for 32-bit clients of the 64-bit FTS? How do I obtain those bits?
.B ekiM
hi to all,i have looked in the forum whit no success and hoping that this is the right place to ask : i am looking for a tool - application to manage mssql server , is anybody new a good system.thanks ori
View 1 Replies View RelatedI am working in ASP.NET 2.0 and using sql server 2000 as backend . In my application i need to insert/update to oracle database table lying on different server. Please let me know how can i maintain two different connecttions to different databases lying on different servers.....
View 1 Replies View RelatedI am working in ASP.NET 2.0 and using sql server 2000 as backend . In my
application i need to insert/update to oracle database table lying on
different server. Please let me know how can i maintain two different
connecttions to different databases lying on different servers.....
Hi,
I am developing a application which is dependent on Sql database (mdf).
I want to know how should i need to deploye or Package the database along with my
application , would i need to instal the sql server exp on to the client system, or there
is any other way to do it , I know that an Access file can be accessed through the
application even the client system dont hav Office, Is there any such type of
mechanism , through which we can do the same with sql database
Hi,
I got some performance problem when I try to transfer records from SQL Server 7 to Oracle 7 database using DTS. I am using a Ole DB connection on both side with the driver provide by Microsoft and it's really slow
Any tips or suggestion to increase the speed will be appreciate
Thank
Martin
Attention: To all SSIS gods like Donald Farmer, Jamie Thompson, Michael Entin
I am so very disappointed with the performance of SSIS on Oracle. When I am doing a simple lookup, it is caching the entire lookup table that is killing my performance. What is worst? When I try to change from Full to Partial or None caching mode, the component throws an error (x) sign.
I am also so frustrated with the way I have warnings all over the place. The warnings are saying code page value for the column cannot be determined. But the code page value is defaulted to 1252. I looked around everywhere to find what the damn code page value of my Oracle database is without any luck.
The damn package that I created takes 10 minutes to process 10,000 records ONLY which is slower than a legacy Cognos Decision Stream!!! Unacceptable!!! My lookup tables on an average have 200,000 records and I DO NOT WANT TO CACHE THEM all for 10,000 records. Something seems messed up!!!
PLEASE HELP!!!!!!!!!!!!!!!!!!!!!!!
Hello Everyone,
Does SQL Server 2005 has a counterpart to Oracle 10G's Real Application Clusters (RAC)?
http://www.oracle.com/technology/products/database/clustering/index.html
Regards,
Joseph
Hi everyone I am analyzing the performance on my SQL Server and need some advice on why I am seeing the results that I am.
One of my problems is that the SQL Server takes an extremely long time to execute the following command 'exec sp_spaceused' on my tempdb. This command usually displays how much free space is remaining on the selected server.
My other problem is that one of the applications that use SQL Server as a back end is acting extremely slugish. Navigating through screens, performing functions and refreshing grids take some time to execute however when I do a trace in SQL Profiler the queries seem to be executing instantly. My first thought was that the queries executed by the application needed to be optimized, but when I do a trace on the queries spawned by the application they seem fine. Does this mean that I have a network bottleneck problem or possibly a PC problem? The PC that we use to do our testing is a P4 3.0 with 512MB of ram running on Windows 2000. The SQL Server is running on Windows 2003 Server with a Raid 5 configuration 4 zeon 3.0 processors with 4 GB of ram. I feel that our desktop PC are sufficient to run any application but have received suggestions that I should upgrade my client PC's to at least 1GB of ram or move to a Citrix server, configure my SQL Server for Raid 10, try file splitting, use gigabit connections from the SQL Server to the desktops etc.
Given the facts above can someone give me advise on the most logical way to resolve my performance issue. Is my problem on the application end of things or do I need to change my network architecture and or hardware?
I deployed my application to my newly installed xp pro laptop
The prerequisites are installed
Now that I want to testdrive my database application I get this error:
An attempt to attach an auto-named database file c:/program files........................MyDB.mdf failed. A database with the same name exists, or specified file can not be opened, or it is located on UNC share.
This is what I read in my app.config file;
<connectionStrings>
<add name="MasterDetail.My.MySettings.MyDBConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|MyDB.mdf;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
What could I be doing wrong? If there is need for additional information you have to tell me
Thanks, Richard
Richard
Hello, as a ISV we develop a software product called Ultimo (www.ultimo.net).
I do have a license question: is it allowed for our customers to run our webbased application on IIS and use sql server express as a database.
The employees from the customer will use the application (intranet). For a small number of users sql express may do the job.
Untill now we always advice sqlserver with processor license.
With kind regards,
Erik.
Anyone have any suggestion on increasing the performance going over a linked server? IS this connection persistent? Or does it establish a connection with each reference? Any other Providers for the connection other than Microsofts OLE DB Provider for Oracle? I imagine that building index's on the referenced oracle tables would help?
Any help, input or suggestions would help.
David
How to launch Oracle SQL Developer through "Run" window in windows as we can launch SQL Server Management studio through "Run" command line using command line "sqlwb -S localhost -d MyDB -U sa -P
View 4 Replies View RelatedGeekz,
My problem is that , I had setup blog on server say, blog.xyz.com [I am using blogengine.net, which is in ASP.Net]. It is using sql server2005 as a backend for storing its blog's posts. After settingup blog, i tried to access its database through my windows application. But while development I was getting error that
"An error has occured while establishing a onnection to the server. When connecting to SQL server 2005, this failure may be caused by the fact that under the defaukt settings SQL server doesnot allow remote connections.(Provider Named Pipes, error :40 - Could not open a connection to SQL Server)"
I am using [String ConnectionString = "Data Source=sql-v123.mesa11.1Server.net; Initial Catalog=abc; User ID=xyz; Password=xyz123";]
Then I decided to created a webservice that will access the data from databse blog and store it somewhere in datatable and using windows application i will retrieve those data for end user. So I want to to know that whether i can access the datatable of webservice holding the blog data from windows application or not. if answer is Yes, then please provide me with source or some great links. Or if There is any other option so that I can access my database directly from windows application, then it would be better. Looking for your answers.
Thanks and regards
I am currently querying two databases - One in SQL Server and one Oracle, to find records which are in one but not the other (essentially a reconcilliation) this is working fine in MS Access, using 2 passthrough queries to return the results of the 2 databases, then another query to find the data in one but not the other.
I decided to try the linked server approach as I thought this would give me enhanced performance - but strangely enough when I query the oracle database from MS Access the query takes about 22 seconds - whereas when I query the linked server from SQL Server the same query takes about 55 seconds.
The only difernce I can see is the passthrough query in MS Access is connecting using ODBC, an the linked server is connecting using Microsoft OLE DB Provider for Oracle.
Any ideas would be appreciated
Hi,
I need to create a derived column for each row in a SQL dataset.
This derived column needs to be created by passing across two values from the SQL dataset and querying an Oracle table based on those parameters. If the Oracle query returns a record(s) then the derived column should be set to 1 otherwise leave it as default (0).
One of these parameters needs to check a date range so I can't use a Lookup Transformation...any ideas how I can accomplish this ?
Thanks
Hi all,
I am looking for some study meaterial focussed on performance based transact SQL development. I am a fairly well seasoned (3+ years self taught), and I am getting into situations where different query constructs yield the same results, but the performance (execution time and disk I/O) varies.
I have found that sometimes nested select statements execute faster than joins, but usually the opposite is true, for example, and I would like to learn why.
Info would be appreciated.
Hi all,
I have been reading some topics on "slow Oracle connections", but I have not found an answer for my question yet.
The problem is that I am extracting data from a Oracle database to Raw files (to import into our DWH later on).
For one table this takes more that 1 hour! I find this to be very slow, but maybe I am expecting too much speed?
Can anyone tell me how long it should take to download 10 million rows from Oracle to Raw file (no transformations)?
Some extra information:
- The table has 10 million rows and a little over 200 columns
- The datatypes in the Oracle database are varchar, number and date
- I tried to tune the buffer size in the data flow and found that I could fit about 1900 rows in a DefaultBuffersize of 100 MB
- I use the "Native OLE DBMicrosoft OLE OB Provider for Oracle" (Provider=MSDAORA.1)
Can I do anything to get a better performance or is 1 hour for a table like this the best performance we can get?
(Changing the Oracle database is unfortunately not an option...)
Thanks and regards,
Ellen
In MS Visual Studio, when creating a new Report Model Project, after defining a datasource to an Oracle database (and successfully testing it), and a simple datasource view (1 table), when I click "Run" in Report Model Wizard, I receive the following error:
"ORA-02179: valid options: ISOLATION LEVEL { SERIALIZABLE | READ COMMITTED }"
It does not appear that one can create Report Models from an Oracle database (since the SQL being used to query the Oracle database cannot be edited and contains syntax errors?). Is this the case?
Thank you.
I am working with a vendor application called Cisco Unified Attendant Console - it operates on a Windows server with a SQL express database. The CUPs function of the application needs to reference a "contact" field with only the user portion of the contact's email address - generally, the contact's User ID will match the user portion of their email address, however, for this customer it does not (they use the employee number as the User ID and firstname.lastname as user portion of the email address.
Writing a script to accomplish the following:
The dbo.Contact_Properties table of the ATTCFG database has the following fields that we can work with:
- First_Name
- Last_Name
- Email
- User_Field_2
- Contact_Unique_Ref (appears to be the field that ties all other contact tables together ?)
Is it possible to create a script that could run daily to either, combine the First_Name and Last_Name fields (with a period between) and populate the User_Field_2 field for each user, or populate the User_Field_2 field with everything before the @ symbol in the Email field for each user?
Also, by default the servers that this application is installed on does not have SQL Server Management Studio installed - is it possible to accomplish with PowerShell script triggered from the Windows Scheduler?
I was told in one of my systems classes that the real performance bottleneck in accessing information from the database was the opening of a connection from the application to the database.
To combat that problem I was advised to use a Singleton Factory pattern and to have that Factory instaniate a connection and open it, then pass references to that connection for all of the objects that it created. All of those objects passed the connection reference to the objects they created and so on. Basically that meant that I only ever had one connection open at any one time for my entire application. And I was able to implement this solution at my previous job where I was developing in Oracle. I primarially used OracleCommands and OracleDataReaders to get the informaton into and out of the database. I thought this was a very nice solution. Having this many DataReaders accessing a single connection was not a problem because OracleConnections don't get locked from having more than one DataReader open at once.
At my current job, however, I use SQL Server. I am concerned that the single connection will not work in my new enviroment as the SQLDataReaders lock up the connection while they are using it. If the information that I recieved about opening connections being the real bottleneck, then I am hesitant to have a connection instanciated and opened for each method, but I am concerned that a whole lot of errors will be generated if I use the single connection method. Also, how do DataAdapters effect my decision of which approach to use.
Any advice would be most helpful. If you have any questions that would help answer just ask. Thanks.
We have a table with a couple of computed columns. The value of the computed column represents a foreign key reference into another table. We're seeing a major performance problem doing a query joining between the two tables with one of the columns, but not the other. In other words, this kind of query is very fast:
select * from TheTable A, FKeyTable B
where A.ComputedColumn1 = B.KeyColumn
but this one sends the CPU usage of SQL Server to 99% for a very long time:
select * from TheTable A, FKeyTable B
where A.ComputedColumn2 = B.KeyColumn
The main difference we can see that the computed column that causes problems is based on a UDF, and the other one isn't (but again, both are computed). When I look at the execution plan, the slow query shows a Nested Loop (Inner Join) with a "No Join Predicate" warning, with the estimated # of rows being 70 million (which correponds to the product of 1016 rows in TheTable and 69K rows in FKeyTable). The fast query doesn't have that warning, and shows 1016 rows (the # of rows in TheTable).
Does anyone know why the usage of a UDF would induce this horribly inefficient join behavior? Anything we can do to fix it?
This is SQL Server 2005 SP2, btw.
Coldfusion Web appls from Oracle to SQL Server 2005 - How to use Application Roles in Coldfusion.
Is there anyone who has used application roles in Coldfusion Applications? How would you set this up?
I know how to set up application roles. If you establish your application role and you move from one page to another, how would you run cfquery since you loose your initial user connection in place of the application role connection. Are there alternatives to using application roles in Coldfusion?
Sample code would be helpful.
TF
Hello Everyone,
Does SQL Server 2005 has a counterpart to Oracle 10G's Real Application Clusters (RAC)?
http://www.oracle.com/technology/products/database/clustering/index.html
Regards,
Joseph
How to connect to SQL server database from Oracle 9ias Application Server (9.0.3) & Test & Verify the Connection. Is it posible.
View 1 Replies View Relatedhave SQL Server 2005 std edition SP1 installed on Windows 2003 Std edition .Configured Transactional (single Publisher and no clustered environment.)
Replication past two months working fine, Now
1.Distrib.exe application err is coming.
Due to which my job is failing (Distributor to Subscriber).
Iam attaching thw file.
Thanks
Sandeep