SQL2005 Database Mailer Problem
Jul 26, 2006
Hi there,
Got a 2005 SP1 SQL box, with database mailer giving problems. In the sysmail_event_log is has
1) Exception Information =================== Exception Type: System.NullReferenceException Message: Object reference not set to an instance of an object. Data: System.Collections.ListDictionaryInternal TargetSite: Microsoft.SqlServer.Management.SqlIMail.Server.Objects.Account GetAccount(Int32) HelpLink: NULL Source: DatabaseMailEngine StackTrace Information =================== at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.DataAccessAdapter.GetAccount(Int32 accountID) at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.SessionManager.GetAccount(Int32 accountID) at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandFactory.CreateSendMailCommand(DBSession dbSession) at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandFactory.CreateCommand(DBSession dbSession) at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandRunner.Run(DBSession db) at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.ThreadCallBack.MailOperation(Object o)
Error: 14667, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
whenever it is trying to send an email. The only way to get this to start sending mail is to run the databasemail90.exe file, so the profiles security etc seem ok. It's just that after the SQL box gets restarted
that it gives all these problems and then it needs some tinkering before it starts to work again.
So anybody got any ideas or tops on what can be done to help sort this out?
Thanks!
View 7 Replies
ADVERTISEMENT
Dec 11, 2007
Im working on this project, when days is greater than 21, the isSubmitable is 1 else 0, then some kind of cursor or stored procedure will read the value and do something, but if executed twice it will do it again, in other words, how can i make sure that the isSubmitable =1 gets executed only once? do i need another column? like history or something? is a cursor the right choice?
ProjectID Emp Days Status IsSubmitable
22859 3535 85 F 1
22859 5163 73 F 1
22859 5689 6 F 0
22859 5715 13 F 0
thanks in advance
View 2 Replies
View Related
Dec 15, 2005
hi
i try to restore a bak file from another sql2005 server to my sql2005 server, but it show the error message as below :
TITLE: Microsoft SQL Server Management Studio Express
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)
------------------------------
ADDITIONAL INFORMATION:
Cannot open backup device 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupackup.bak'. Operating system error 5(error not found).
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3201)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=3201&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
pls some one can help me ???
thanks
chaus
View 62 Replies
View Related
Oct 12, 2007
Can you open/use a database created in SQL2005 in SQL2005 Express?
Thanks for the help!
Max
View 4 Replies
View Related
Nov 15, 2007
We replicate a SQL2000 database (DataBaseA) to a SQL2000 database (DataBaseB) by using the Restore function and hasn't change its logical name but only the physical data path and file name. It is running fine for a year. We use the same way to migrate the DataBaseB to a new SQL2005 server with the Restore function and the daily operation is running perfect. However, when we do the Backup of DatabaseB in the SQL2005, it just prompt the error message
System.Data.SqlClient.SqlError: The backup of full-text catalog 'DataBaseA' is not permitted because it is not online. Check errorlog file for the reason that full-text catalog became offline and bring it online. Or BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data. (Microsoft.SqlServer.Smo)
Please note we left the DataBaseA in the old SQL2000 server.
Please help on how we can delete the Full-text catalog from DatabaseB so we can do a backup
Many Thanks
View 1 Replies
View Related
Apr 23, 2006
I have a medical records system, SoapWare v4.90, that uses MSDE (SQL2000) databases. Due to the 2gb limitation, I am trying to migrate over to SQL 2005 (Standard or Express) which I have heard works fine. The SoapWare has a datamanager that allows me to log in to the MSDE instance, detach the SoapWare databases from msde (as well as do backups, etc) which I can confirm are detached.
Then I log back into a SQL2005 database instance using the datamanager and try to attach the database. This is what their pictured instructions demonstrate. However, I get the following error:
Database 'sw_charts' cannot be upgraded because it is read-only or has read-only files. Make the database or files writeable, and rerun recovery.
Of course, some of the entries will be read only, since doctors have to sign off the charts and are not allowed to subsequently change them. But I should still be able to switch over to sql 2005?!?!?!?
Or... is there a way to attach the databases to SQLExpress manually?
Help pls?
View 1 Replies
View Related
Apr 22, 2006
I have a medical records system, SoapWare v4.90, that uses MSDE (SQL2000) databases. Due to the 2gb limitation, I am trying to migrate over to SQL 2005 (Standard or Express) which the company says works fine. The SoapWare has a datamanager that allows me to log in to the MSDE instance, detach the SoapWare databases from msde (as well as do backups, etc) which I can confirm are detached.
Then I log back into a SQL2005 database instance using the datamanager and try to attach the database. This is what their pictured instructions demonstrate. However, I get an error:
Database 'sw_charts' cannot be upgraded because it is read-only or has read-only files. Make the database or files writeable, and rerun recovery.
Or... is there a way to attach the databases to SQLExpress manually?
Help pls?
View 3 Replies
View Related
Oct 28, 2006
Situation: at work we have a windows 2003 server with MS sql server2005 express. With my pc at work, I can see my own database I created.At home. I can connect with VPN to the network. With extern desktop Ican see the database and do everything. Now I want to create a loginwith visual web developer 2005 express. But when I try to login withsql server authentication I don't see any database. When I try windowsauthentication, I have connection with the master, model, msdb andtempdb but not my own database. I am very sure at work I could createthe login and the permissions were OK.Thanks
View 3 Replies
View Related
Jan 4, 2006
Ok is this possible??
I tried to import data thinking it should just work - ha ha! Threw lots of errors about table and column mapping instead.
Hodgehegs
View 2 Replies
View Related
Sep 24, 2007
We currently built a new SQL2005 server and have serveral sentitive Payroll database hosted in it. I managed to seperate some roles to various users to prohibit them to direct access the data but can access it via front-end application.
The problem is I setup a backup operator with the following rights
Server Roles -- Public
User Mapping
User Mapped to this login / Database membership for
PayrollDataBase_1 BackupOperatorName
db_backupoperator / db_denydatareader / db_denydatawriter / public
However, I try to login using the 'BackupOperatorName' in my workstation and Backup the PayllrollDataBase_1 to a server shared path says E:SqlDataBacupPayrollDataBase_1.BAK. Afterward, I copy it over the network back to my workstation and RESTORE in back to my local SQL2005 instant using the local SQL2005 SA user. All data CAN BE browse
Anyone please help to post how to restrict the Backup operator can only Backup the database but cannot restore to its local SQL2005 instant
Many Thanks in advance
View 2 Replies
View Related
Jul 13, 2007
Does 2005 have some kind of new feature that audits/monitors changes to a database kind of like an antivirus or something.
Reason for question:
1) inserting records into database, 1000 records takes about 2 minutes.
2) reading those 1000 records takes about 45 seconds
3) updating those 1000 records takes about 15 minutes
4) yes we are using ntwdblib.dll and a 4gl language
i was running a test program to add, read, update, delete 1000 records and that is when i noticed that insert, update, delete took a performance hit whereas reading didnt. i ran my test program on a control server (in house) and then at the clients side(matching OS, MSSQL 2005 SP2). Results from test program: The UPDATE process on client side took about 4x longer, INSERT about 2x longer, DELETE about 1.5x longer, READ was actually faster on the clients system.
so this made me wonder if their was some kind of database monitoring/auditing going on.
View 14 Replies
View Related
May 2, 2008
hi
i have use database mail in sql server2005 to send mail.
i have complete all step to configure databasemail.but test the mail
the error is "database mail stoped.Use sysmail_start_up to start database mail .
View 8 Replies
View Related
Jun 23, 2008
hi
i have shrink log file of database use dbcc command
Message
Executed as user: sa. Cannot shrink log file 2 (abc.ldf) because all logical log files are in use. [SQLSTATE 01000] (Message 9008) DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528). The step succeeded.
View 2 Replies
View Related
Aug 2, 2007
I'm querying a small SQL2005 database and finding that the query can sometimes complete in under a second and then 5 minutes later the same query can take 15 minutes to complete.
The query I'm running is very simple as follows:
select TOP 26 * from vSearchListOpportunityItem WHERE OpIt_OpportunityId=2495 ORDER BY Prod_Name, OpIt_OpportunityItemId
The view it is pulling data from only contains only 1890 lines, which in turn pulls data from 3 tables with 821, 2560, and 1957 lines of data. In other words it's small. I have noticed that if I try and open the smallest of these tables while on a 'go slow' period it also takes around 15 minutes to return the data.
The database was originally on SQL 2000. It is the only database on this powerful quad core server.
The SQL Server CPU usage never goes above 40%, and always has free memory.
No sign of locks.
I can't figure out why such a small database is going so slow with such a simple query. Any ideas?
View 8 Replies
View Related
Mar 28, 2008
I am planning to move the sql server 2000 database to the sql server 2005 database. I have to restore that by using the back up copy. Do I need to follow any special instructions or just like moving from one server to another. Could some body help with this. Thanks!
View 5 Replies
View Related
Jan 18, 2007
Hi. I need to move database schema (tables definiton with relations without data) form SQL Express to SQL 2005. Is there any easy way to do it? I'll be grateful for your help. Pawel
View 4 Replies
View Related
Aug 31, 2005
How to get the list of table names in a SQL2005 database?
View 6 Replies
View Related
Apr 23, 2008
If I restore a SQL 2000 DB into 2005 it upgrades it for me.
When restoring a DB to a new server/instance of SQL, in SQL 2000 I had to carry out the sp_changedbowner procedure after the restore was complete.
Is the equivalent procedure when restoring a SQL 2000 DB to 2005 to go to the schema for the restored database, find the schema with the name of the SQL 2000 db owner and change the schema owner?
OR alternatively run the following query where mark is the db owner in 2000 and lladmin in the new user in 2005 ;
ALTER AUTHORIZATION ON SCHEMA::mark TO lladmin;
GO
View 1 Replies
View Related
Feb 5, 2007
Hi,
I recently migrated our database from Access to SQL Server 2005. All the WinXP users can connect to the new SQL2005 database. But all the Win2K machines fail to log on. To diagnose the problem, I tried to connect from a Wind2K machine using an .udl file. The connection failed because "a provider failed to initialize".
The SQL2005 server is residing on a WinXP machine. Users log on via SQL Server authentication layer using a dedicated login that only has read/write privileges to one specific database.
Does anyone know a solution? Thanks in adance.
Regards,
Vladislav
View 3 Replies
View Related
Sep 7, 2007
Heya all,
Sorry if this has been posted elsewhere, etc., please point me in the right direction if it has 'cos I couldn't find it!
Right, we have a mirrored database with full safety and a witness for automatic fail over, all works fine, very impressed with it. Now I need to backup the database involved and this is where I could do with some help and answers and/or tips.
As the mirror database is off-line/recovering it seems you can't back that one up, but I'd like to have something that tries to back it up for if/when it fails over and becomes the primary. The solution I've used for now is to write a small .Net application that uses the client side fail over connection string (Data Source=Server1;Failover Partner=Server2) so that it connects to whatever system is currently the primary, and then issues the relevant 'BACKUP xxx' statements to backup the database.
This applications is launched from a windows scheduled task job on the hour (or near to it), every hour. At 06:00 it does a full backup, at 12:00, 18:00 and 00:00 it does a differential backup, and all other times it does a transaction log backup.
This all seems to work fine so far, and generates all the relevant backup files to a share on another server.
So, my main question is; does this look like a good plan? Am I missing some really simple wizard or button that would backup the relevant database from whatever server is up?
Secondly, are the backups from each server interchangeable as they're in a mirrored configuration? That is, for example, the backup application (on the hour) connects to Server1 does a full back up, followed later by a log backup. Server1 then dies, so the next time the backup application runs it connects to Server2 and because of the current time does a log backup from Server2. If we had to restore from backups, could we use the full backup and log from Server1, followed by the log from Server2? Otherwise I'll guess I'll need to modify the logic for the backup application to detect it's failed over, and maybe do a full backup on Server2, or something like that.
Anyway, thanks for any help/advice/tips,
Gareth/OhGod
View 5 Replies
View Related
Dec 27, 2005
I just upgraded my SQL 2000 server to SQL2005. I forked out all that money, and now it takes 4~5 seconds for a webpage to load. You can see for yourself. It's pathetic. When I ran SQL2000, i was getting instant results on any webpage. I can't find any tool to optimize the tables or databases. And when I used caused SQL Server to use 100% cpu and 500+MB of ram. I can't have this.Can anyone give me some tips as to why SQL 2005 is so slow?
View 3 Replies
View Related
Jan 4, 2007
Hi
We have Sql2005 x64 bit standard edition server installed in windows 2003 64 bit editio server,
currently due to buisness requirements we need to have sql2005 x64 bit enterprise edition, please let me know how do i do the upgrade or change.
is it possible to retain all our custom settings in the standard edition after changing to enterprise edition.
This has to be done for our production and very critical, please help
Thanks
Samuel I
View 4 Replies
View Related
Sep 5, 2006
HiI'm new this .I' using SQL 2005 VWD05.Could some body tell me how to upload to my host server database to to run a membership user account.I dont know what are the procedure to do on my pc in order to transfer the file on the database.Thanks
View 25 Replies
View Related
Oct 10, 2006
Dear all I create this html file on the fly in my asp.net application abd what i would like to do is to store it inside my sql2005 database. What would be the best way?The html file itself is not really big. Probably not more then 600 - 800 characters most. I was thinking the text type fields of the database and then when retreiving it dump it inside in a file and save the file with html extention. Are there any better sugestions? Thank you for your time
View 3 Replies
View Related
Jun 25, 2007
Can anyone tell me why my SQL2000 database has grown aprox 15 % and my Log file 20,000 % when I attach it to SQL2005 .I've Thousands of Databases to Upgrade, but with the log file increasing to more than the size of the Database Its going to be a struggle !
It also takes a fair ammount of time to attach,
I suspect there is some reindexing going on , as when I try to reattach to SQL 2000 I get index errors ?
Is the re anything I can do in advance to reduce the database growth ?
I know I can truncate the log afterward but the peak diskspace consumed during my Migration may be an issue !
Thanks for any help
View 2 Replies
View Related
Oct 6, 2006
Following update runs 20 hours till TEMPDB grows up to 400GB and runs out of space with error message:
Msg 1105, Level 17, State 2, Line 8
Could not allocate space for object 'dbo.Large Object Storage System object: 440701391536128' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
T_PERF_LOC has 30,000,000 rows and T_MASTER_LOC has 2,000,000 rows
There is and index on V_KEY in both tables. CHANNEL_KEY is of Integer datatype and not indexed.
F_MAP function performs simple lookup on very small table (10 rows)
UPDATE A
SET CHANNEL_KEY = OTHERDB.DBO.F_MAP(b.ID)
FROM T_PERF A
JOIN dbo.T_MASTER b
on a.V_KEY = b.V_KEY
Any ideas why?
Thanks
View 4 Replies
View Related
May 31, 2007
Hi All,
I'm just upgrading my business database to SQL 2005 and hit a problem when executing a stored procedure in the msdb database. Error message reading "EXECUTE permission denied on object 'sp_delete_job'".
Obviously this message tells me that I do not have permissions to execute stored procedures in the MSDB database.
In SQL 2000 I never had to mess around with permissions on this database as the security was already pre-configured.
I'm still familiarising myself with SQL 2005 so rather than making a finger in the air guess I'd like to understand how SQL 2000 was configured and the best solution for resolving this in SQL2005.
I'm really after some advice/suggestions!
View 5 Replies
View Related
Dec 5, 2007
Hi
I have to migrate a SQL2000 database to SQL2005, I tried to use CopyDatabase Wizard (CDW) but invariably stopped all processes (may be for permissions, but I really I don€™t know) so I decided to detach de database, copy files to new server and attach it to SQL2005, it apparently works well, except for the fact that Schemas where created with the name of the users of SQL2000.
Is that correct?
Could it affect the performance or any other thing?
Is correct to do what I did?
Could be another problem in the recently attached database?
(Compatibility, data loss, corrupted structures, collation or something like that)
The server I am using is in a domain, it has windows server 2003 R2 SP2
@@Vesion returns
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)
Feb 9 2007 22:47:07
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
And in the source server with windows 2000 SP4
@@Vesion returns
Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
Thank you in advance
Any comment will be greatly appreciated
JAPonce
View 4 Replies
View Related
Sep 19, 2006
While doing maintenance on a development server we detached a database and later reattached it to the same server.
For some unknown reason web services execute but return no results. There are no errors reported in event viewer. I connected to the database in Visual Studio 2005 and attempted to execute a simple select statement but it returns no row.
It appears to execute the stored procedure but does not return any data. I have cut and pasted the select statement into a View which does execute.
Lastly I just created a new store procedure to test the database and it also does not return any rows. This is all very strange.
Could use a few suggestions at this point.
Larry
View 2 Replies
View Related
Apr 2, 2008
Hi,
I have a problem to connect to SQL2005 Express Database with VC++ as regular user, not window system administrator.(If i login as window system admin, everything's working fine.)
A. I tried to use SQL2005 Express Server authentication, the error msg is:
Code= 80004005 €œunspecified error€? Cannot open database ***, login failed.
1. I am using Server authentication with username and password.
2. Gave all the permission of Program FilesMicrosoft SQL ServerMSSQL.1 and Program FilesMicrosoft SQL ServerMSSQL.2 to this regular window user.
3. Connection String is:
Provider=SQLNCLI;Initial Catalog=***; User ID=***;Password=***;Data Source=***;Persist Security Info=False;Integrated Security=SSPI;
B. I tried to use Window authentication by adding User Instance, but the error is:
Code= 80004005 €œunspecified error€? MS Native Client
Description = Invalid connection string attribute.
Connection String is:
Provider=SQLNCLI;Initial Catalog=***; Data Source=***;Persist Security Info=False;User Instance=True;Integrated Security=SSPI;AttachDBFileName=directory***.mdf;
Any idea? Thanks.
View 3 Replies
View Related
Nov 7, 2007
Hi,
I'm trying to migrate a MSDE based application into a SQL2005 Express SP2 DB Engine on Windows Vista Business but I'm facing several errors that are blocking me.
Since the installation of SQL2005 Expr. must be done in a silent mode (the end user shouldn't set anything), I'm installing it using this syntax:
SQLEXPRSP2_ITA.EXE -q /norebootchk /qb reboot=ReallySuppress addlocal=all instancename=CSERV DISABLENETWORKPROTOCOLS=0 SQLBROWSERAUTOSTART=1 SQLACCOUNT="NT AUTHORITYSYSTEM" SQLBROWSERACCOUNT="NT AUTHORITYSYSTEM" AGTACCOUNT="NT AUTHORITYSYSTEM" ASACCOUNT="NT AUTHORITYSYSTEM" RSACCOUNT="NT AUTHORITYSYSTEM"
The installation proceeds without problems but when my program tries to create the database, a message error appears:
CREATE DATABASE denied on database 'master' (I translated directly from italian).
What I would like is to install SQL 2005 with "Windows Authentication" access instead of "SQL Authentication".
If I install SQL2005 manually by setting a named instance (CSERV), Windows Authentication mode, activating the option "set the local account as SQL administrative account" (translated from italian and suggested with Vista) I get the following error (read from the LOG):
CREATE FILE encountered operating system error 5 (access denied) while attempting to open or create the physical file 'C:Program FilesMicrosoft SQL ServerCSERVDataCGIDBDat.mdf'
CSERVData is the folder that the application creates and where the "mdf" and "ldf" files are stored.
So in the first case it seems that SQL2005 don't recognize the local account as administrator even if the user has administrative privileges (at least for Vista), in the second case is Vista that is blocking the action.
What is the solution??
Thank you and regards.
Roberto G.
View 4 Replies
View Related
Apr 11, 2007
I backup the database into tape0 using management studio and can see the contents in the backup media.
However when I tried to restore, the error "timeout expired" appears and sometimes together with the error "restore headeronly is terminated abnormally".
Please advice and thank you in advance!
Below is the error message:
===================================
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Program Location:
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlCommand)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteWithResults(String query)
at Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSqlWithResults(Server server, String cmd)
at Microsoft.SqlServer.Management.Smo.Restore.ReadBackupHeader(Server srv)
at Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseGeneral.buttonSelectDevice_Click(Object sender, EventArgs e)
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
===================================
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (.Net SqlClient Data Provider)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-2&LinkId=20476
------------------------------
Server Name: NTSRV1
Error Number: -2
Severity: 11
State: 0
------------------------------
Program Location:
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32 bytesExpected)
at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()
at System.Data.SqlClient.TdsParserStateObject.ReadByte()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlCommand)
View 3 Replies
View Related
Feb 21, 2007
Can anyone comment on the engine performance difference between SQL2005 Enterprise Edition versus Standard? I'm talking generalized performance of the engine and not admin features (parallel index operations) or scaled-storage (partitioning)
( http://www.microsoft.com/sql/editions/enterprise/comparison.mspx )
The marketing literature makes note of two things:
Enterprise can use more then 4 processors
Enhanced read-ahead and scan (super scan)
(note: I cannot find anything about this 'feature')
One un-noted Feature:
only Enterprise supports 'lock pages in memory'
We are in the process of migrating from SQL2000 to SQL2005 in an OLTP environment. Based on the marketing literature; I would have chosen SQL2005-standard. But based on our limited testing, we are seeing some strange differences.
Query Performance
With MaxDOP=1 and using a large batch query (select top 1500000); SQL2005-Enterprise is twice as fast as SQL2005-Standard.
(Note: this difference persists regardless of lock-pages-in-memory setting)
CPU Utilization
In addition, taskmgr shows that SQL2005-Enterprise uses a single processor at ~90%. While SQL2005-Standard shows a single processor at ~20%.
Lock Behavior
We are also seeing lock-behavior differences. A single DML statement that attempts to modify ~5000 rows will cause Table-locks on SQL2005-Standard but obtain normal row-locks on SQL2005-Enterprise.
These empirical differences make me wonder if the engine codebase is fundamentally different between the two?
Any insight would be appreciated.
View 4 Replies
View Related
