Need Advice On System Performance
Feb 8, 2007
Hi,
I am starting a new job next week. Part of what I am required to do is
a "system diagnostic" on a SQL Server 2000 box to determine what areas
can use improvement - this would include configuration settings,
backup/recovery, sql tuning, etc... and anything else I may not have
mentioned here!
What I need is a thorough and systematic approach to doing this. Can
anyone please give me advice, or point to a FAQ or other links that
discuss this. I am running out of time.
THANKS MUCH
View 2 Replies
ADVERTISEMENT
Jun 3, 2007
I have a VB.Net 2005/SQLCE desktop application that imports XML data into an SQLCE database. The needs of the application require that upon import, certain values in the incoming data are used to look up and store additional values in the import datatable. The completed import data is then stored in a 'production' table that constitutes the application's reason for being - the data is processed, assessments are made on it, and reports are generated.
Anyway, this valid value process is performed three times for different types of data.
The way I currently have it running is that the three valid value lookup processes are built into loops
through the incoming data.
<Pseudocode>
Read incomingData into datatable
For each row in incomingData
for each row in refTable1
if incomingData.field1 = reftable1.Field1 then
incomingData.Field2 = refTable1.Field2
incomingData.Field3 = refTable1.Field3
else
send user to valid value mapping dialog
collect dialog mappings
add mapped data to incomingData
end if
next
Next
For each row in incomingData
for each row in refTable2
if incomingData.field4 = reftable2.Field4 then
incomingData.Field5 = refTable2.Field5
incomingData.Field6 = refTable2.Field6
incomingData.Field5 = refTable7.Field5 JOIN ON refTable2("ID") = refTable7("ID")
incomingData.Field6 = refTable7.Field6 JOIN ON refTable2("ID") = refTable7("ID")
else
send user to valid value mapping dialog
collect dialog mappings
add mapped data to incomingData
end if
end if
next
Next
For each row in incomingData
for each row in refTable3
if incomingData.field7 = reftable3.Field7 then
incomingData.Field8 = refTable3.Field8
else
send user to valid value mapping dialog
collect dialog mappings
add mapped data to incomingData
end if
end if
next
Next
</Pseudocode>
One of these lookup processes, let's say the one involving refTable2, takes a long time; so long that I have it executing on a separate thread. For example. in a 512 kb incoming XML file, there may be around 700 records. Also, refTable2 contains around 800 possible matches. So, we are checking against 800 possible values, 700 times. That's a minimum of 56,000 operations, not counting the additional lookups to refTable7. For a 1.5 MB XML file, it may take ~40 seconds to do the following:
check each incoming record against refTable3
look up any matching values and store in the import datatable
create a list of unmapped incoming values
create a list of valid values from refTable2
present the user with the valid value mapping dialog
All that being said, a colleague recently suggested that before executing the CPU-intensive refTable2 lookup, that I first get a distinct list of Field7 from incomingData before performing the lookup (see pseudocode above) for that set of values.
QUESTIONS:
1) Would this really help performance? I don't see how, since I must still iterate through incomingData and add looked-up values to each record before storing it in the production datatable.
2) Is there a better way to achieve desired results rather than the brute-force matching routine described above?
This deadline has been ridiculous as usual, so I've done the first thing that came to mind with this import/lookup routine. However, I figured I'd check to see if I was overlooking anything fundamental that would speed things up a bit.
thanks
View 4 Replies
View Related
Jul 20, 2005
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 Related
Mar 22, 1999
hi, I am working on SQL server 6.5 using a desk top. Now I am about to buy a note book and do some development work. As far as I know, win 95,98 does not support SQL server 6.5
But SQL sever 7.0 does support win 95,98 Am I correct?
I am confuzed, what notebook operating system shall I buy which will allow me to work on sql development on the road.
another issues is what if I choose to use win 98 for sql server 7.0 what are the things that I will be missing . In other word, what are the limitation that I may have when acquiring win 98 as an operating system for sql server 7.0..
thanks for your advice
regards
Ali
View 5 Replies
View Related
May 25, 2006
I am a DBA working for a large business in the North East of England.
One of our main Database servers (SQL Server 2000 SP4) is configured as follows:
Storage - 1. Logical Drive configured as RAID5 with about 800MB of storage, 40% free space. Used for main database files. 8 Physical drives.
- 2. Logical Drive configured as RAID1 with about 140MB of storage, 20% free space. Used for sql log files.
Both 1 and 2 are on the same controller (SCSI).
- 3. System Drive (C:)
Storage configuration was done by an external consultancy and disk performance (when benchmarked) was excellent.
Memory - 8GB of memory 6.5GB allocated to SQL Server
Operating System: Windows server 2003 Enterprise Edition
SQL Server: 2000 SP4
We would appreciate your advice regarding the following problem that we have on busy times:
Typical number of users/connections to SQL Server is between 200 to 250 (sysprocesses)
Typical number of active processes can vary between 5 to 30 (all sorts of applications)
The problem we have is that on some busy days a combination of processes grinding the server to a halt.
When we check the activity (normally using sp_who2) we can see that there are no blocked processes, cpu and DiskIO progressing as normal
(some values maybe large but not beyond expectation).
However, when "the problem" occurs, everything (queries, updates, etc) is extremely slow for 5-20 minutes until the bottleneck is freed.
We would appreciate any advice (tempdb?, disk bottleneck?, etc) and recommendation for any useful tools.
Thank you,
View 3 Replies
View Related
Jan 30, 2008
Greetings!
I would like to create a database for keeping track of payroll data for employees where the supervisors (job coaches) on our workshop floor can use a Pocket PC device to record the hourly employee data on the fly. Then at the end of the day, the supervisor can place the device in a cradle of some sort and synch the newly entered data into the main database.
I'm guessing that SQL Server Compact edition would be perfect for this type of task? Is that correct? Can someone give me recommendations on how to go about setting this up? What should I use as the main database? SQL Server? Access? Any advice is appreciated!
View 1 Replies
View Related
May 17, 2007
Hi,
I am developing an editorial system(MS-SQL). There are a public part(frontend for reader) and an editorial part (backend for editor, admin,...).
The public part of system will be used approximately 1000 acesses/s.
A possible solution:
I solve whether I have to use two databases:
1.DB all data
2.DB with data for frontend
acesses...............trigers
--------->2.DB <----------1.DB
or more of databases or else?
The purpose is, a reader should have good response that the reader don't wait.
Could you give me any advice ?
Thanks
View 3 Replies
View Related
Nov 8, 2001
I am developing a new application and am trying to find the best way to deliver XML documents to the app. Here's the scenario: each set of xml docs will have a unique identifier. The app needs to retrieve a known xml doc by using this identifier. So the dilema I am having is as follows: I could create a folder for each identifier and load the xml docs relevant to each id into the folder. For the app to retrieve the document, it would simply construct a file path and the windows B-tree search would find it and return it very quickly. Alternatively, I could create a table with a primary key that stores the unique identifier, cluster it, and store the xml in a blob field.
Does anyone have any experience, references, or thoughts about which would be the best way to do this? I am an avid database developer and the app will be using sql 2000 anyway, however it seems in this situation the database might not be the best option in terms of speed, performance, and resource useage. Thoughts?
Thanks,
Mike
View 5 Replies
View Related
Dec 24, 2002
Hello,
I just wanted how we can find the system performance without using tool like performance monitor or profiler.
I just need the query like equalent to peformance monitor for see the systmem status of CPU ,IO ,Memory and etc..
Thanks,
Ravi
View 2 Replies
View Related
Jul 20, 2005
I 'inherited' a group of SQL Server server class machines. They aretrue server technology but the disk sub-systems are lacking. There isone hot-swap backplane that all the drives share (with one SCSIchannel) thusly even though there are three logical drives (composedfrom 6 to 8 hard drives), they all go through one channel. This iscreating a performance issue that is noticable and can be seen invarious performance counters that Microsoft recommended one shouldmonitor in terms of disk I/O. For a cheaper 'fix', I can add aseperate two drive bay (with its own SCSI channel) with mirroreddrives. I would then mostly likely place the transaction log files onthis new channel. Or I could place the indices filegroup files onthis new channel for DBs with mainly searching going on (not muchupdating). If I went this route I would be leaning towards thetransaction log move since the second method would require me movingDBs around quite a bit. Any input on this solution (besides spendingmore money)?What I would prefer to do is get a better server class machine or addan external drive bay solution (not a SAN). I would try to get threeor four SCSI channels in the new hardware to split the differentfile/filegroups out (i.e. transaction logs files, data filegroup,indices filegroup, etc.). My only concern here is: would this moreexpensive solution be worth the money? As far as replacing servers, Ihave only two kinds of experience...replacing somewhat underpoweredservers with slightly less underpowered servers and replacing overkillservers with even more overkill servers. In both cases, the disksub-systems were fairly equivalent from the old system to the new one.Will going the three/four channel route really get data moving along?We have one server in particular that hosts a database (one of many onit) for a web application that gets decent traffic (it is a privatelogin based system for internal use and external use by our clients'agents). Periodically throughout the day, there are 2-5 minute burstswhere performance slows to a crawl. I want to spend more timeprofiling queries and such before recommending we spend more money,but the folks I am working for want quick results and there is quite abit of stored procedure logic to profile and investigate. I know thedisk sub-system is definately in need of an overhaul, but I would liketo get an idea of peformance gains from adding either one additionalchannel over the existing single channel as well as going thethree/four channel route over the existing single channel setup.Any information would be greatly appreciated.Regards,Tony
View 2 Replies
View Related
May 14, 2007
Summary: Started replication April 1 of 4M xact / day publishing system to subscribing system.
Performance was good. Latency was ~ 5-7 seconds.
May 10 we noticed that the DB was behind (latency was 12 hours).
All performance counters seem good with the exception of the disk.
. Performance spikes are 8 minutes apart and last from 30 - 60 seconds.
. During this period, Disk % Busy (1 - Disk % Idle) is 100%
The publisher DB publishes about 50-52 xacts/sec.
Rate of distribution (distribution DB to Subscriber DB) is ~ 47 xacts / second, so latency is increasing (currently at 33 hours). Previously my Subscriber system's "capacity" was 150 xacts / sec.
I know this because several weeks ago, the network went down, we were 24 hours behind.
When the network came back up the replication subscriber system was able to catchup at around 150 xacts / sec, or 3X the production system rate.
What has changed between then and now? Not much. We did install Tivoli Service Manager (IBM's backup system) a couple of weeks ago. It seems to run fine on a nightly basis, but I don't see any periodic heavy Disk I/O from that. Just to be sure, I've had them shut the TSM services down just to be sure.
We've also eliminated all extraneous processes other than those I need for performance monitoring (there was a RTVscan, virus scan process).
I've eliminated Autogrowth's as an issue as I've bumped the growth so that they are very infrequent (several days at this point. When we resolve the problem, I'll dial this down to something more reasonable.
My disk configuration is not ideal I realize (single Raid-5 disk with 3 partitions), however, this has not changed in the 6 weeks.
Thanks for any help on this!
Jack Griffith
Configuration:
Subscribing System:
SQL Server: 2000, SP4 - 8.0.2039
CPU - 2.8GHZ Xeon, Quad Dual-core
Memory - 3.5GB RAM
Disk: 3 partitions on a single RAID-5 disk with 1118 GB of space:
C: 39GB System and Programs
D: 97GB Log space
E: 982 GB Data space
Replication configuration:
- nosynch, continuous Transactional Replication
- Distribution db is on Subscription system
- distribution - Publication of approx. 50 transactions / second
Subscriber DB configuration:
DB size: 64458 MB
Logging: Simple (at this point)
distribution
DB size: 3111 MB
Logging: Simple (at this point)
View 1 Replies
View Related
Jan 16, 2007
Hi,
I am trying to write a table-valued function in SQL Server 2005 (SP1) to return all active directory groups a user belongs too, using managed code (VB.NET).
Testing the code with a simple winform I get the list of groups in about 0.4 seconds. However the table-valued function takes upwards of 17 seconds to run! Is this normal for managed code in SQL Server?
Imports SystemImports System.TextImports System.DataImports System.Data.SqlClientImports System.Data.SqlTypesImports System.CollectionsImports System.DirectoryServicesImports Microsoft.SqlServer.ServerPartial Public Class UserDefinedFunctions#Region "Constants" ''' <summary> ''' The connection string for Active Directory. ''' </summary> 'Private Const LDAP_CONNECTION_STRING As String = "LDAP://<My LDAP connection string> ''' <summary> ''' The LDAP search filter need to find a user in Active Directory. ''' </summary> 'Private Const LDAP_SEARCH_FILTER_USER As String = "(&(objectclass=user)(objectcategory=person)(sAMAccountName={0}))"#End Region ''' <summary> ''' Gets all active directory groups for the user. ''' </summary> ''' <returns>All dataset permissions for the user.</returns> <Microsoft.SqlServer.Server.SqlFunction(DataAccess:=DataAccessKind.None, FillRowMethodName:="udfUserActiveDirectoryGroupsFill", TableDefinition:="GroupID NVARCHAR(100)")> _ Public Shared Function udfUserActiveDirectoryGroups(ByVal userName As String) As IEnumerable ' Setup the active directory search. Dim searcher As New DirectorySearcher(LDAP_CONNECTION_STRING) searcher.Filter = String.Format(LDAP_SEARCH_FILTER_USER, userName) searcher.SearchScope = SearchScope.Subtree searcher.PropertiesToLoad.Add("distinguishedname") ' Run the active directory search. Dim result As SearchResult = searcher.FindOne() Dim userEntry As DirectoryEntry = result.GetDirectoryEntry() Dim userGroups As New ArrayList GetActiveDirectoryGroupsForEntry(userEntry, userGroups) Return userGroups End Function Public Shared Sub udfUserActiveDirectoryGroupsFill(ByVal source As Object, ByRef GroupID As SqlChars) GroupID = New SqlChars(CType(source, String)) End Sub ''' <summary> ''' Recursively gets the active directory groups for the directory entry. ''' </summary> ''' <param name="entry">The active directory entry.</param> ''' <param name="groups">The list of groups.</param> Private Shared Sub GetActiveDirectoryGroupsForEntry(ByVal entry As DirectoryEntry, ByVal groups As ArrayList) For i As Integer = 0 To entry.Properties("memberOf").Count - 1 Dim memberEntry As New DirectoryEntry("LDAP://" + entry.Properties("memberOf")(i).ToString()) groups.Add(memberEntry.Properties("sAMAccountName")(0).ToString()) GetActiveDirectoryGroupsForEntry(memberEntry, groups) Next End SubEnd Class
View 9 Replies
View Related
Aug 21, 2006
I have created a windows library control that accesses a local sql database
I tried the following strings for connecting
Dim connectionString As String = "Data Source=localhostSQLEXPRESS;Initial Catalog=TimeSheet;Trusted_Connection = true"
Dim connectionString As String = "Data Source=localhostSQLEXPRESS;Initial Catalog=TimeSheet;Integrated Security=SSPI"
I am not running the webpage in a virtual directory but in
C:Inetpubwwwrootusercontrol
and I have a simple index.html that tries to read from an sql db but throws
the error
System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.PermissionSet.Demand()
at System.Data.Common.DbConnectionOptions.DemandPermission()
at System.Data.SqlClient.SqlConnection.PermissionDemand()
at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection,
etc etc
The action that failed was:
Demand
The type of the first permission that failed was:
System.Data.SqlClient.SqlClientPermission
The Zone of the assembly that failed was:
Trusted
I looked into the .net config utility but it says unrestricted and I tried adding it to the trusted internet zones in ie options security
I think that a windows form connecting to a sql database running in a webpage should be simple
to configure what am I missing?
View 28 Replies
View Related
Mar 21, 2007
Reporting services is configured to use a custom security extension in the following Environment.
Windows xp
IIS 5.0
when i go to http://servername/reports , the UIlogon.aspx page loads fine and i enter username and password. but i get logon failed.
when i go to http://servername/reportserver , the logon.aspx does not load and i get the following error message :
"Request for the permission of type 'System.Web.AspNetHostingPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed."
any idea ? .
chi
View 6 Replies
View Related
Apr 3, 2007
Ok guys, I am trying to install Sql server 2005 on Vista but I am still stuck with this warning message in the System Configuration Check during Sql server 2K5 installation :
SQL Server Edition Operating System Compatibility (Warning)
Messages
* SQL Server Edition Operating System Compatibility
* Some components of this edition of SQL Server are not supported on this operating system. For details, see 'Hardware and Software Requirements for Installing SQL Server 2005' in Microsoft SQL Server Books Online.
Now, I know I need to install SP2 but how the hell I am going to install SP2 if Sql server 2005 doesn't install any of the components including Sql server Database services, Analysus services, Reporting integration services( only the workstation component is available for installation)?
Any work around for this issue?
P.S.: I didn't install VS.NET 2005 yet, can this solve the problem?
Thanks.
View 8 Replies
View Related
Jan 29, 2007
Hello!
Hopefully someone can help me.
I have scripts to refresh database as SQL daily jobs. (O.S is Win2K3 and SQL server 2000 and SP4) It was worked and I got the following message this morning from SQL error log.
Internal I/O request 0x5FDA3C50: Op: Read, pBuffer: 0x0D860000, Size: 65536, Position: 25534864896, RetryCount: 10, UMS: Internal: 0x483099C8, InternalHigh: 0x0, Offset: 0xF1FF1E00, OffsetHigh: 0x5, m_buf: 0x0D860000, m_len: 65536, m_actualBytes: 0, m_errcode: 1450, BackupFile: \XAPROD12MASTERXAPRODXAPROD_db_200701290000.BAK
BackupMedium::ReportIoError: read failure on backup device '\XAPROD12MASTERXAPRODXAPROD_db_200701290000.BAK'. Operating system error 1450(Insufficient system resources exist to complete the requested service.).
View 1 Replies
View Related
Jul 20, 2005
Hi All,I use Bulk insert to put data to myTable.When the SQL server is in local machin, it works well. But when I putthe data in a sql server situated not locally, then I get a errormessage like this:Could not bulk insert because file 'C:Data2003txtfilesabif_20031130.txt' could not be opened. Operating systemerror code 3(The system cannot find the path specified.).BULK INSERT myTableFROM 'C:Data2003 txtfilesabif_20031130.txt'with (-- codepage = ' + char(39) + 'ACP' + char(39) + ',fieldterminator = ';',rowterminator = '',keepnulls,maxerrors=0)Someone can explan me what the error shows upThanks in advance- Loi -
View 3 Replies
View Related
Jul 5, 2007
Hello,
I am getting the following error from my SqlClr proc. I am using a third party API, which is making call to some webservice.
System.InvalidOperationException: There is an error in XML document (11, 2). ---> System.ArgumentException: Item has already been added. Key in dictionary: 'urn:iControl:Common.ULong64' Key being added: 'urn:iControl:Common.ULong64'
System.ArgumentException:
at System.Collections.Hashtable.Insert(Object key, Object nvalue, Boolean add)
at System.Collections.Hashtable.Add(Object key, Object value)
at System.Xml.Serialization.XmlSerializationReader.AddReadCallback(String name, String ns, Type type, XmlSerializationReadCallback read)
at Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationReader1.InitCallbacks()
at System.Xml.Serialization.XmlSerializationReader.ReadReferencingElement(String name, String ns, Boolean elementCanBeType, String& fixupReference)
at System.Xml.Serialization.XmlSerializationReader.ReadReferencingElement(String name, String ns, String& fixupReference)
at Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationReader1.Read5926_get_failover_stateResponse()
at Microsoft.Xml.Serialization.GeneratedAssembly.ArrayOfObjectSerializer8221.Deserialize(XmlSerializationReader reader)
at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle, Xml
...
System.InvalidOperationException:
at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle, XmlDeserializationEvents events)
at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle)
at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at iControl.SystemFailover.get_failover_state()
at F5CacheManager.GetActiveLBIPaddress()
Found a similar problem in a different thread, but couldn't find any solution. I was wondering if there is an open case for this issue?
https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=398142&SiteID=1
View 4 Replies
View Related
Jul 20, 2005
Hi,I have a few things on my databases which seem to be neither true systemobjects or user objects - notably a table called 'dtproperties' (createdby Enterprise manager as I understand, relating to relationship graphingor something) and some stored procs begining with "dt_" (some kind ofsource control stuff, possible visual studio related). These show up whenI use"exec sp_help 'databaseName'"but not in Ent. Mgr. or in Query Analyzer's object browser, and also notin a third party tool I use called AdeptSQL. I am wondering how thosetools know to differentiate between these types of quasi-system objects,and my real user data. (This is for the purpose of a customized schemagenerator I am writing). I'd prefer to determine this info with systemstored procs (ie sp_help, sp_helptex, sp_...etc) but will dip into thesystem tables if needed.Thanks,Dave
View 1 Replies
View Related
Sep 12, 2004
1. Use mssql server agent service to take the schedule
2. Use a .NET windows service with timers to call SqlClientConnection
above, which way would be faster and get a better performance?
View 2 Replies
View Related
Jun 23, 2006
Hello Everyone,I have a very complex performance issue with our production database.Here's the scenario. We have a production webserver server and adevelopment web server. Both are running SQL Server 2000.I encounted various performance issues with the production server with aparticular query. It would take approximately 22 seconds to return 100rows, thats about 0.22 seconds per row. Note: I ran the query in singleuser mode. So I tested the query on the Development server by taking abackup (.dmp) of the database and moving it onto the dev server. I ranthe same query and found that it ran in less than a second.I took a look at the query execution plan and I found that they we'rethe exact same in both cases.Then I took a look at the various index's, and again I found nodifferences in the table indices.If both databases are identical, I'm assumeing that the issue is relatedto some external hardware issue like: disk space, memory etc. Or couldit be OS software related issues, like service packs, SQL Serverconfiguations etc.Here's what I've done to rule out some obvious hardware issues on theprod server:1. Moved all extraneous files to a secondary harddrive to free up spaceon the primary harddrive. There is 55gb's of free space on the disk.2. Applied SQL Server SP4 service packs3. Defragmented the primary harddrive4. Applied all Windows Server 2003 updatesHere is the prod servers system specs:2x Intel Xeon 2.67GHZTotal Physical Memory 2GB, Available Physical Memory 815MBWindows Server 2003 SE /w SP1Here is the dev serers system specs:2x Intel Xeon 2.80GHz2GB DDR2-SDRAMWindows Server 2003 SE /w SP1I'm not sure what else to do, the query performance is an order ofmagnitude difference and I can't explain it. To me its is a hardware oroperating system related issue.Any Ideas would help me greatly!Thanks,Brian T*** Sent via Developersdex http://www.developersdex.com ***
View 2 Replies
View Related
Oct 19, 2006
I am trying to put the data from a field in my database into a row in a table using the SQLDataSource.Select statement. I am using the following code: FileBase.SelectCommand = "SELECT Username FROM Files WHERE Filename = '" & myFileInfo.FullName & "'" myDataRow("Username") = CType(FileBase.Select(New DataSourceSelectArguments()), String)But when I run the code, I get the following error:Server Error in '/YorZap' Application. Unable to cast object of type 'System.Data.DataView' to type 'System.String'. 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.InvalidCastException: Unable to cast object of type 'System.Data.DataView' to type 'System.String'.Source Error: Line 54: FileBase.SelectCommand = "SELECT Username FROM Files WHERE Filename = '" & myFileInfo.FullName & "'"
Line 55: 'myDataRow("Username") = CType(FileBase.Select(New DataSourceSelectArguments).GetEnumerator.Current, String)
Line 56: myDataRow("Username") = CType(FileBase.Select(New DataSourceSelectArguments()), String)
Line 57:
Line 58: filesTable.Rows.Add(myDataRow)Source File: D:YorZapdir_list_sort.aspx Line: 56 Stack Trace: [InvalidCastException: Unable to cast object of type 'System.Data.DataView' to type 'System.String'.]
ASP.dir_list_sort_aspx.BindFileDataToGrid(String strSortField) in D:YorZapdir_list_sort.aspx:56
ASP.dir_list_sort_aspx.Page_Load(Object sender, EventArgs e) in D:YorZapdir_list_sort.aspx:7
System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +13
System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +45
System.Web.UI.Control.OnLoad(EventArgs e) +80
System.Web.UI.Control.LoadRecursive() +49
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3743
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210 Please help me!
View 3 Replies
View Related
Nov 25, 2007
System.Data.SqlClient.SqlError: Cannot open backup device '\.Tape0'. Operating system error 5(error not found). (Microsoft.SqlServer.express.Smo)
i have only one sql instance and tape is istalled successfully.
please help me to find solution for this error.
Thanks,
View 2 Replies
View Related
May 17, 2006
Dear all,
I am stuck with a SSIS package and I can€™t work out. Let me know what steps are the correct in order to solve this.
At first I have just a Flat File Source and then Script Component, nothing else.
Error:
[Script Component [516]] Error: System.InvalidCastException: Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.SqlClient.SqlConnection'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface. at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.AcquireConnections(Object transaction) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)
Script Code (from Script Component):
' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components
Imports System
Imports System.Data.SqlClient
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Dim nDTS As IDTSConnectionManager90
Dim sqlConnecta As SqlConnection
Dim sqlComm As SqlCommand
Dim sqlParam As SqlParameter
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim valorColumna As String
Dim valorColumna10 As Double
valorColumna = Row.Column9.Substring(1, 1)
If valorColumna = "N" Then
valorColumna10 = -1 * CDbl(Row.Column10 / 100)
Else
valorColumna10 = CDbl(Row.Column10 / 100)
End If
Me.Output0Buffer.PORCRETEN = CDbl(Row.Column11 / 100)
Me.Output0Buffer.IMPRETEN = CDbl(Row.Column12 / 100)
Me.Output0Buffer.EJERCICIO = CInt(Row.Column2)
Me.Output0Buffer.CODPROV = CInt(Row.Column7)
Me.Output0Buffer.MODALIDAD = CInt(Row.Column8)
Me.Output0Buffer.NIFPERC = CStr(Row.Column3)
Me.Output0Buffer.NIFREP = CStr(Row.Column4)
Me.Output0Buffer.NOMBRE = CStr(Row.Column6)
Me.Output0Buffer.EJERDEV = CDbl(Row.Column13)
With sqlComm
.Parameters("@Ejercicio").Value = CInt(Row.Column2)
.Parameters("@NIFPerc").Value = CStr(Row.Column3)
.Parameters("@NIFReP").Value = CStr(Row.Column4)
.Parameters("@Nombre").Value = CStr(Row.Column6)
.Parameters("@CodProv").Value = CInt(Row.Column7)
.Parameters("@Modalidad").Value = CInt(Row.Column8)
.Parameters("@ImpBase").Value = valorColumna10
.Parameters("@PorcReten").Value = CDbl(Row.Column11 / 100)
.Parameters("@ImpReten").Value = CDbl(Row.Column12 / 100)
.Parameters("@EjerDev").Value = CDbl(Row.Column13)
.ExecuteNonQuery()
End With
End Sub
Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
Dim nDTS As IDTSConnectionManager90 = Me.Connections.TablaMODELO80
sqlConnecta = CType(nDTS.AcquireConnection(Nothing), SqlConnection)
End Sub
Public Overrides Sub PreExecute()
sqlComm = New SqlCommand("INSERT INTO hac_modelo180(Ejercicio,NIFPerc,NIFReP,Nombre,CodProv,Modalidad,ImpBase,PorcReten,ImpReten,EjerDev) " & _
"VALUES(@Ejercicio,@NIFPerc,@NIFReP,@Nombre,@CodProv,@Modalidad,@ImpBase,@PorcReten,@ImpReten,@EjerDev)", sqlConnecta)
sqlParam = New SqlParameter("@Ejercicio", Data.SqlDbType.SmallInt)
sqlComm.parameters.add(sqlParam)
sqlParam = New SqlParameter("@NIFPerc", Data.SqlDbType.Char)
sqlComm.parameters.add(sqlParam)
sqlParam = New SqlParameter("@NIFReP", Data.SqlDbType.Char)
sqlComm.parameters.add(sqlParam)
sqlParam = New SqlParameter("@Nombre", Data.SqlDbType.VarChar)
sqlComm.parameters.add(sqlParam)
sqlParam = New SqlParameter("@CodProv", Data.SqlDbType.TinyInt)
sqlComm.parameters.add(sqlParam)
sqlParam = New SqlParameter("@Modalidad", Data.SqlDbType.SmallInt)
sqlComm.parameters.add(sqlParam)
sqlParam = New SqlParameter("@ImpBase", Data.SqlDbType.Decimal)
sqlComm.parameters.add(sqlParam)
sqlParam = New SqlParameter("@PorcReten", Data.SqlDbType.Decimal)
sqlComm.parameters.add(sqlParam)
sqlParam = New SqlParameter("@ImpReten", Data.SqlDbType.Decimal)
sqlComm.parameters.add(sqlParam)
sqlParam = New SqlParameter("@EjerDev", Data.SqlDbType.Decimal)
sqlComm.Parameters.Add(sqlParam)
End Sub
Public Sub New()
End Sub
Public Overrides Sub ReleaseConnections()
nDts.ReleaseConnection(sqlConnecta)
End Sub
Protected Overrides Sub Finalize()
MyBase.Finalize()
End Sub
End Class
Thanks a lot for your help
View 13 Replies
View Related
Apr 23, 2007
I get the following message in the vs2005 querybuilder when i do a preview:
***********************************
SQL Execution Error.
Executed SQL statement: SELECT Schoolindex, Variant, VVSchool, [index], indincl, VVRuimtes, School FROM School WHERE (Schoolindex = @PARAM1)
Error Source: SQL Server Compact Edition ADO.NET Data Provider
Error Message: @PARAM1 : Unable to cast object of type 'System.Data.SqlTypes.SqlInt32 to type System.IConvertable'.
****************************************
The same querypreview works fine without the parameter:
SELECT Schoolindex, Variant, VVSchool, [index], indincl, VVRuimtes, School FROM School WHERE (Schoolindex = 186)
Can anybody tell me why this is?
And tell me a way to get the tableadapter working?
Anne-Jan Tuinstra
View 3 Replies
View Related
Mar 12, 2008
I created a .net console application within which I connect to a sql server (not local) as follows,
string conn_str = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=QuantEquitySql;Data Source=server name";
SqlConnection conn = new SqlConnection(conn_str);
conn.Open();
.
Everything works fine when I run it from my computer. When I try to run it from a network share I get the following error,
Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
I am using Visual Studio 2005 and .Net framwork v2.0.50727.
Does anybody know a fix to this problem?
Thanks
View 3 Replies
View Related
Jun 20, 2007
I have a report that uses some embedded custom code. The embedded custom code is a function that execute some sql query on a sql server database.Everything works fine in Visual studio. The report gets deployed on the server successfully, however when running the report from report manager i get the following error message :
The Hidden expression for the table €˜table1€™ contains an error: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
Here is the code :
************************************************************************
Public function get_field() as string
Dim myConnection As System.Data.SqlClient.SqlConnection
Dim myCommand As System.Data.SqlClient.SqlCommand
Dim data_reader As System.Data.SqlClient.SqlDataReader
Dim field(100) as string
Dim i as integer
Dim j as integer
Dim sql_field as string
Dim nbr_field as integer
Dim rtn_string as string
i = 0
sql_field ="Select field from mytable"
myConnection = New System.Data.SqlClient.SqlConnection("Datasource=xxx.xxx.xxx.xxmydatabase;Initial Catalog=mydatabase;User Id=user1;Password=password1;")
myConnection.Open()
myCommand = New System.Data.SqlClient.SqlCommand(sql_field, myConnection)
data_reader = myCommand.ExecuteReader()
While data_reader.Read()
if data_reader.HasRows then
field(i)= data_reader(0).ToString()
end if
nbr_field = nbr_field + 1
i= i+1
End While
data_reader.Close()
myConnection.Close()
for j = 0 to nbr_field -1
rtn_string = rtn_string + field(j) + ","
Next j
rtn_string = left(rtn_string,rtn_string.length-1)
return rtn_string
'return sql_cmd
'return yes_no
'return lkupfield
end function
******************************************************************
Why do i get the error message ?, is this related to Code Access Security issues with .net framework. if yes
how do i set the Security so the report server or report manager allows embedded custom code to be executed. Any advice ?
Chi
View 7 Replies
View Related
Feb 10, 2007
Hi,
Im Designed a Webpage .It consosts of a Button, whenever i click on
Button report must generated.When the report is in my system report
generated successfully.when I try to access a report from other
system.I cudnot able to access report.Can any1 hlp me inthis aspect
.any procedure in order to access the report of other system.wht shud
be url for Reportserverurl and reportpath.In short
When i click on btn i shud get report of other system.what previleges shud be given for other system in other to access
With regards,
mahender
View 3 Replies
View Related
Jan 18, 2008
Hi,
I've written this code multiple times now. But for the first time i get an error at the line underlined. My procedure runs perfectly when i execute it through Sql Query analyzer.
plzz help.. Its urgent and am unable to find the reason for this error "A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll"
Thanks !SqlConnection conn = new SqlConnection(DbConnectionString);
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "dbo.rqryTradesPRR";
cmd.Parameters.Add("@COBDate",SqlDbType.DateTime).Value = "2002-10-31 00:00:00.000" ;
SqlDataReader reader = cmd.ExecuteReader();
while(reader.Read())
{
// have written something here
}
Thanks in advance !
View 2 Replies
View Related
Jun 19, 2008
I'm getting this error on a vb.net page the needs to execute two separate stored procedures. The first one, is the main insert, and returns the identity value for the ClientID. The second stored procedure inserts data, but needs to insert the ClientID returned in the first stored procedure. What am I doing wrong with including the identity value "ClientID" in the second stored procedure?
Unable to cast object of type 'System.String' to type 'System.Web.UI.WebControls.Parameter'. 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.InvalidCastException: Unable to cast object of type 'System.String' to type 'System.Web.UI.WebControls.Parameter'.Source Error:
Line 14: If li.Selected Then
Line 15: InsertClientCompanyType.InsertParameters("CompanyTypeID").DefaultValue = li.Value
Line 16: InsertClientCompanyType.InsertParameters("ClientID") = ViewState("ClientID")
Line 17:
Line 18:
Source File: C:InetpubwwwrootIntranetExternalAppsNewEmploymentClientNewClient.aspx.vb Line: 16
Here is my code behind... What am I doing wrong with grabbing the ClientID from the first stored procedure insert?
Protected Sub InsertNewClient_Inserted(ByVal sender As Object, ByVal e As SqlDataSourceStatusEventArgs)ClientID.Text = e.Command.Parameters("@ClientID").Value.ToString()ViewState("ClientID") = e.Command.Parameters("@ClientID").Value.ToString()End SubProtected Sub Submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Submit.ClickInsertNewClient.Insert()For Each li As ListItem In CompanyTypeID.Items
If li.Selected ThenInsertClientCompanyType.InsertParameters("CompanyTypeID").DefaultValue = li.ValueInsertClientCompanyType.InsertParameters("ClientID") = ViewState("ClientID")InsertClientCompanyType.Insert()End IfNextEnd Sub
View 2 Replies
View Related
Aug 13, 2007
Hi,
I have developed a custom server control for .NET Framework 2.0. The server control has a property named BinaryData of type byte[]. I marked this property to be data bindable. Now, I have varbinary(Max) type of field in my SQL Database and I have used SQLDataSource and bound this varbinary(Max) field with the property BinaryData (byte[]) of my control. It is working fine as long as the data value is not NULL. Now, In my control, I have handled the NULL value so that no Exception is thrown. Still, when I bind this property using the SQLDataSource, I get Error "Unable to cast object of type 'System.DBNull' to type 'System.Byte[]'." I am not sure if I can do anything to stop this erro within my control. If it is not possible from the control, then what is the workaround that I can do in my ASPX page in order to stop this error ?
Thanks a lot in advance.
View 10 Replies
View Related
Dec 31, 2007
Hi, I got this field (dateSubmitted) having a data type of DateTime but I receive this error "Unable to cast object of type 'System.DateTime' to type 'System.String'." All value for dateSubmitted field are 12/27/2007 12:00:00 AM. cheers,imperialx
View 3 Replies
View Related
Sep 21, 2007
Hello
Which is better and faster?? and WHY????Writing Select Statement with joins in Stored procedure, or creating view and calling it from stored procedure (select * from view)..
View 4 Replies
View Related
