I have big problem now, can somebody help me? I have a program used ASP+SQL2000 ,the connection string is : strConnection = "Provider=SQLOLEDB;Server=BOUNwindows,1005;User ID=training;Password=training22;Database=ourtraining;" Everything works fine, I can insert data, delete or modify. But since we changed sql2000 server to sql 2005 express, then I can read data but can't insert data(write data into sql 2005) I checked all permission are correct on sql 2005, program are correct, everything looks good, but only can't write data into sql2005
Hello All Just wondered if someone could help me with a bit of T-SQL, i have a application in ASP.NET/VB that allows the user to update a message board by clicking a button "update" this in turn triggers my Stored Procedure for inserting this data into a table, which works great. It inserts the data into its respective fields and also takes The Title, Line 1, Line 2 and so on and creates a XML file (Using FOR XML) which is stored in the same Table under a column call XML_Data. Which again works great. My problem now is how do i output this XML_Data to an actual XML file that is on my local machine, i.e. It be created in say C:Inetpubwwwrootxmlfiles("xml file name inserted here from another column that holds xml file name").xml Any help on this would be greatfully apreciated Thanks In Advance Neil
We currently run sql 2005 server and also sql express in our dev environments. We use sql express as an offline store (smart client). We have a similar/exact schema on the sql 2005 server and also the express.
We use the auto attach feature to connect to the express version of the database. Both the developer machines and the one that is running the sql 2005 server have exactly the same hardware configuration. The only difference may be that the server box is not running the VS.Net environment. The disk space etc is pretty much the same. Actually we run another database server(DB2) on the 2005 server machine.
We have observed that sql express is much slower and queries execute much slower aswell. For example, this may not be a totally scientific way of checking but a long running query on the server took only 2 minutes while on express it took longer than 9 minutes. The schema and data etc are the same.
Is there something we need to look into as far as read write speed/performance goes ?
Hello, I have worked with SQL Server 2000 but now we have a requirement where I need to write values from an asp.net form into an XML type in SQL Server 2005. I have never used XML as a type in SQL Server 2005. How do we write xml into xml type. For example the structure of XML is something like: <application> <applicationID = "value"></applicationID> <customerName="value></customerName> </application> I have to write this kind of XML into the XML type and later retrieve these XML values and populate the form again. Kindly suggest. Thanks a lot.
Hello, I have worked with SQL Server 2000 but now we have a requirement where I need to write values from an asp.net form into an XML type in SQL Server 2005.I have never used XML as a type in SQL Server 2005. How do we write xml into xml type. For example the structure of XML is something like:<application><applicationID = "value"></applicationID><customerName="value></customerName></application>I have to write this kind of XML into the XML type and later retrieve these XML values and populate the form again.Kindly suggest. Thanks a lot.
Hello All, I want to write Batch Program which will insert Data into Table every day based on some conditions. can anyone please let me know how to write Batch Process in SQL server 2005?? Thanks.
Hi,I'm accessing a pair of databases with ASP/ADO,and using stored procedures on the first access.The first access works OK - everything gets writtento where it's supposed to be.On attempting to write to a pair of database tablesin the second database (second access attempt),the server crashes, as does the app, and I get the following error:--------------------------------------------------------------------Server Error in '/Webfolder01' Application.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: A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - The pipe has been ended.)--------------------------------------------------------------------After this, I have to go into the services panel and restart SQL 2005.This doesn't happen if I use small test data sets in the first access,and I can comment out the second access attempt, and the first accessexecutes just fine with both the large and small data sets,so I'm thinking that this has something to do with synchronisation:Perhaps the second attempt is being made before the server is ready.Is there something that I need to do to make certain that SQL 2005is ready to receive data?I'm using SqlBulkCopy with both accesses, but I don't see how thatcould be a problem.--------------------------------------------------------------------[SqlException (0x80131904): A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - The pipe has been ended.)] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857370 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734982 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error) +556 System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj) +164 System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32 bytesExpected) +34 System.Data.SqlClient.TdsParserStateObject.ReadBuffer() +30 System.Data.SqlClient.TdsParserStateObject.ReadByte() +17 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +59 System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal() +1327 System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer (Int32 columnCount) +916 System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowState rowState) +176 System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table) +6 Database_control.DB_ctrl_class.load_datatable_to_DB_table(DB_ref_class src_table) in i:Virtual WebfoldersDBctrl.cs:978--------------------------------------------------------------------THANK YOU VERY MUCH!!!
I have the linked server connection created and works perfectly well. I mean I am able to see the tables while I am on my database.
how do I write a SQL statement to reference the linked server ?
I tried the following:
Select top 100 * from casmpogdbspr1.MPOG_Collations.dbo.AsaClass_Cleaned
Then I get the error message....
Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI10" for linked server "casmpogdbspr1" does not contain the table ""MPOG_Collations"."dbo"."AsaClass_Cleaned"". The table either does not exist or the current user does not have permissions on that table.
Hi there, i have sometimes trouble to write data to a table in a sql server expr. database.
i have a vb.net application that writes every 2 minutes data to a table. sometimes (no regulary phenomen) the error appears after some hours, sometimes after some days. then i cannot write data in the table. it seems that the database sleeps or is offline.
before i write the data i check if the connection is open and i can write data. if i cannot write the data i try several times and often after the third try i can write the data.
i have checked everything (no power save's on or other power off modes)
i have also test it with SqlClient.SqlConnection.ClearAllPools()
and many other things to find the bug, but no success at the moment.
it seems that the database or the table is away ?? or sleeps for some seconds ?!? i have no idea.
I need to export data from a table of SQL server 2000 database, into an Excel 2000 sheet. I tried following query from the sql query analyzer
Code SnippetSELECT * INTO [Excel 4.0;Database=F:ew.xls].[sheet1] FROM tab1
It gave me following error
Code SnippetServer: Msg 2760, Level 16, State 1, Line 1 Specified owner name 'Excel 4.0;Database=F:ew.xls' either does not exist or you do not have permission to use it.
The F:ew.xls file is present and it has no additional security applied, hence I think it should be writable.
Please tell me where am I doing wrong.
I don't know if this is the correct news group or not. Let me know if I have to post to some other news group.
Does anyone know how to how to performance case-insensitive search onXML data type in SQLServer 2005? Or I have to convert all the xml datato lower case before I store it?Thanks in advance.John
I have been trying to write a cursor to fetch required data from table but somehow its running forever and inserting duplicate records.
I have a temp table named getInvoice where I have five important columns
1. invoice number 2.group 3.invoice status 4. Invoice Expiration date 5. Creation date time
and some other columns.One invoice number can belong to one or more group and there can be one or more records for a particular invoice number and group.
An example is below :
InvoiceNumber Group InvoiceStatus InvoiceExpirationDate CreationDateTime
My query condition is complex and that is why Im facing problem retrieving the output.I need a cursor for getting distinct invoice number from the table and for each invoice number I need to get the latest record for each invoice number and suffix combination based on creationdateand time column and if that record has invoice status of 2 and also the invoice expiration date can be either null or greater than today's date, then I need to get that record and put it in a temp table.
The query I wrote is below
declare myData cursor for select distinct invoiceNumber from #getInvoice declare @invoiceNumber varchar(30) open myData fetch next from myData into @invoiceNumber while @@FETCH_STATUS = 0
Hello!I have a very simple structured table:id | datawhere "data" is a varchar(100) This table would contain a lot rows (~ 500.000.000) and I want to select all "id" where data=@data. Is it realistic that the SQL Server could serve this request on a normal webserver within 1 or 2 seconds? Thanks!
I have recently upgraded from SQL Server 2000 to SQL Server 2005, and now all my queries run infinitely more slowly.
Here is the scenario - I run an extract of a MS SQL Server database at a client site, then recreate the database on our in-house server - but without indexes etc. Then I run various queries in order to created data files that will be used for importing into a global system. When I was running Server 2000, most of the queries ran in less than 10 seconds each, but under Server 2005 they take 3 minutes or more! Does anybody know of any parameters that I need to adjust to fix this problem?
Hi,I have a Microsoft SQL Server 2005 Enterprise installed on Windows Server 2003, and developing web application for 500 clients. So I am interested will I have any performance issues if I put in 'Articles' table, data for all 500 clients and then filter it on client ID, or should I make 500 'Articles' tables for every client one with different name and then change sqldatasource for gridview depending on which client is working on it. I will have, beside 'Articles' table, another 10 tables, which means 5500 tables total, if I use second approach, on first I will have only 11 tables. So I am asking is it better to have more tables with less data, or less tables with more data. And what are pros and cons for both approach. Thanks a lot!
Ive got sql server 2005 WG edition running and have an access adp application which connects to it. However since upgrading to sql server 2005 from 2000 the adp project runs a lot slower. However when I install express on a machine and connect the adp project to it which sits on the same machine it runs just fine. We have also rebuild all the indexes for the database but that doesnt fix the problem. Could someone please help...
I have one query which is calulating running total and taking just 6 mins to run on production SQL Server 2000 server but it is taking more than 45 mins to run on QA on SQL Server 2005 server. The index and data is same on both server, What other things we can check beside the index? Thanks
Does using varchar in SQL Server 2005 significantly affect performance on updates?
Why or why not?
I have seen many SQL Server databases with many varchar columns - in other databases other than SQL Server it is advised not to use varchar because it significantly impacts performance.
I am trying to weigh when to waste space to help performance.
I am having a table with 40 columns and it contains 4 million records. I got the results for one year in 40 secs. After tuning, it is retuning in 24 secs( what i have done is i created index on order by fields).
Can you please suggest me in which way I can increase the performance.
I'm not sure I chose the right forum, so any comments on that are also welcome
We recently changed from SQLserver2000 to SQLserver 2005 in the beginnen all went fine. But now we are struggling with a severe performance problem... suddenly SQLserver2005 reaches its max and is not longer able to work properly -> Extremely slow
I'm wondering if there are other people / companies / ... sharing this same issue?
A query was taking 20 seconds and consuming 70% CPU takes only 1 second after setting Maximum Memory property to 2048 MB - why?
Server: OS Microsoft(R) Windows(R) Server 2003, Enterprise Edition Version5.2.3790 Service Pack 1 Build 3790 8 GB memory Two Dual-core AMD Opteron 285 2.6GHz Processors Server is not configured for AWE Fiber channel connection to EMC Clarion - two LUNs - one for MDF, one for LDF
SQL 2005 SQL 2005 32 bit Standard Edition - SP1 (version 9.0.2047) Three instances installed on server - only one instance in use Binaries and system databases on local mirrored disk Database file (MDF) on one EMC LUN - dedicated physical drives Log file (LDF) on one EMC LUN - dedicated physical drives
Query in question:
SELECT TOP 10 Address.Address1, Address.Address2, Address.City, Address.County, Address.State, Address.ZIPCode, Address.Country, Client.Name, Quote.Deleted, Client.PrimaryContact, Client.DBA, Client.Type, Quote.Status, Quote.LOB, Client.ClientID, Quote.QuoteID, Quote.PolicyNumber, Quote.EffectiveDate, Quote.ExpirationDate, Quote.Description, Quote.Description2, Quote.DateModified, Quote.DateAccessed, Quote.CurrentPremium, Quote.TransactionDate, Quote.CreationDate, Quote.Producer FROM ((Client INNER JOIN Address ON Client.ClientID = Address.ClientID) INNER JOIN Quote ON Client.ClientID = Quote.ClientID) WHERE (Quote.Deleted = 0) AND ((Address.AddressType)='Mailing') ORDER BY Client.Name
With default maximum memory setting (2,147,483,647 MB) - query runs in 20 seconds and consumes over 70 % of the CPU.
After changing maximum memory setting to 2048 MB, query runs in less than 1 second.
Question is: What is the best practice for setting the minimum and maximum memory settings for SQL 2005? What can be monitored to identify the cause of these type of issues - using profiler, PerfMon, other tool?
I am looking for an useful sql server 2005 performance tuning book. i have been searching for a real nice book as i m going to start my job from next month in a financial domain with one of the requirement as sql server 2005 performance tuning.so i m looking forward a book which can help me doing well at my workplace. Any suggestions and links appreciated in advance .
Does anyone know of any documentation on the performance of partitionmerge/split? Does the merge or split of a partition cause any lockingon the partitioned table? If you were merging or splitting a largevolume of data rebalancing your partitioned table would youpotentially lock users out?
My Performance Counters for SQL Server 2005 are corrupted. How do I repair them ?
Any help would be appreciated. Thanks.
Salyx
Specs Windows 2003 Standard, AMD x64. SQL Server 2005; x64; 9.00.3042.00; SP2 Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2) This is a new install, so no "upgrade from SQL 2000". This is a production server, so "reboot" is hopefully not part of the suggested repair.
Symptom Open Performance Monitor. Open Add Counters. Open Dropdown "Performance Object". Instead of the SQL Server Performance Counter names, a list of 4-digit numbers appears. Other Performance Counters, eg, Processor, work as normal.
Attempted repair 1 - Recovery of system performance counters Open Command Prompt CD WindowsSystem32 lodctr /R This failed to restore the full set of performance counters for an unknown reason.
Attempted repair 2 - Recovery from a backup file from a second host I used the performance counter backup file from a second host which has an identical windows install. This properly restored the system performance counters, but failed to restore the SQL Server ones. This seems odd, because both system have - as much as I can tell - the same applications installed.
Open Command Prompt CD WindowsSystem32 REM Load backup file from second host lodctr /R:c:PerfStringBackup.INI
Attempted repair 3 - Recover SQL Server - specific counters Open Command Prompt CD WindowsSystem32 REM Load backup file from second host lodctr /R:c:PerfStringBackup.INI REM Clear and re-load MSSQLServer counters... unlodctr MSSQLServer lodctr "/R:C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinnsqlctr.ini"
Executing this pretty much wiped most performance counters. Only a small sub-set is now available.
More Info SQL Server 2005 and later SP2 were installed under the administrator account. MSSQLServer service runs under its own Windows Account (permission issues ??) I get Event Log entries regarding x86 vs x64 Performance Counter Libraries. These, however, do not refer to ASP, not SQL Server. I have 2 (virtually) identical hosts (same install sequence of apps). The Performance Counters on the second host work fine. Exctrlst.exe lists MSSQLSERVER service, but I don't know how to diagnose the details.
We have our SQL databases clustered using MSCS on X64 servers and are planning to apply SP2. During initial tests, we find that around 20-25% queries perform slow after applying SP2 compared to SP1. Just wanted to know if anyone else has found the same behavior and if there are any known patterns / issues with respect to performance for Sp2
I have a SQL Server 2005 database where covering indexes had to be used to improve performance for the heavy amounts of retrievals; however, the inserts into the tables are now very slow of course. Is there any way to improve the performance of the inserts without taking away the indexes.
Would changing locking or partitioning the index help the inserts?
Other databases use a concept of "freespace" to set up in the beginning - making pre-existing space for inserts - is there anything like this in SQL Server 2005?
We have updated to SQL Server 2005, let€™s say, in a hurry without thinking or testing. Databases were attached to the new instance of SQL Server 2005. It looked great when I tested it alone but then a new day come and as all users logged into the system we had got a big problem. The response times are very long and users receive time out errors all the time.
A little background:
The instance of SQL Server 2005 is installed on the same server as 2000 was installed on. 2000 has been uninstalled. It is a Xenon 3.2 GHz with 2GB RAM and SCSI raid. Data and logs are on different spins.
Application is an old ASP code and some parts are not optimized at all. But it worked fine on SQL Server 2000.
What could be the problem?
I really don€™t want to downgrade to SQL Server 2000.
Wondering if anyone has any experience with SQL Server Express Edition (SSEXP). We're looking at a mobile sales force type model, so a local database on a laptop with no real time network connection. So the users would collect data locally, then connect up to the network every few days to replicate the data to a central server. So questions.. Has anyone tried anything similar? How stable/mature is SSEXP? Any other thoughts, alternatives or gotchas anyone can think of?
I installed the SQL Server 2005 SP2 update 2 rollup on my 64-bitserver and the performance has tanked!I installed rollup 3 on some of them, but that did not seem to help.I thought it was just a linked server performance issue, but myoptimization started running today on one of the "update 2" instancesand so far it's been running about 10 hours longer than it normallydoes.The rollup 3 fixed our stack dumping issues, but we NEED to have thisperformance thing fixed!I saw that MS has come out with update 4 last week - doesn't sayanything about fixing this, though.Has anyone else experienced this?I'm not necessarily expecting anyone to have a fix for this, justwantto know I'm looking in the right place before I call MS.