SQLServerCentral / Administration / Notification Services

transactional replication

shreekanth.kavali — Sat, 16 Feb 2013 01:43:35 GMT

After configure of transactional replication ,i want to add one more table ?will it be possible ,how will do that?

errors in sql server installation

shreekanth.kavali — Sat, 16 Feb 2013 01:03:39 GMT

1)Can anybody pls answer ,what are the errors usually come while installing sql server?2)If installation ,fails how to find the exact reason?

replication

shreekanth.kavali — Sat, 16 Feb 2013 01:40:41 GMT

In transactional replication ,will write the delete command in publisher that command do not replicate on the subscriber why?

redgateMonitor issue since changing password

davidandrews13 — Thu, 17 Jan 2013 08:49:24 GMT

i've got an issue which is linked to a previous thread about me changing some passwords for accounts that use SQL Server.i have an account, lets call it 'Domain1\myaccount'i changed the password for that account 4 days ago.i was then looking through our use of sqlMonitor and discovered we weren't using it, so i detached the database, 2 days ago.now, when i attempt to log on to 'Sql Monitor 2 Website', it obviously tells me that the longin failed for user 'Domain1\myaccount' (as i've changed the password).searching the redgate site, it tells me to delete a record within the database, but since its detached, the database node has disappeared from SSMS.is there any other way for me to change the password for sqlMonitor 2, as i believe it is that which is creating an Error Log every 30 seconds.

changed some Passwords, database mail seems to have stopped

davidandrews13 — Tue, 15 Jan 2013 03:12:08 GMT

i've seen there's a few threads about database mail, here, but none seem to apply to me.I've changed a couple of system Passwords and have gone through the process of ensuring that all our processes work.all but the database mail are, but now i'm stuck.the passwords that have changed are for SA (not sure if database mail is using this account)and a windows account (its more than likely using this one).from SSMS, i right clicked the Database Mail node from the Management folder and chose to send a Test EMail but nothing has come through.I've also viewed the Database Mail Log and there are lots of errors stating:[code="other"]the mail could not be sent to the recipients because of the mail server failure... The server response was: Timeout[/code]what i've done so far:after changing the passwords, i logged onto the server and restarted the Sql Server Agent.I've right clicked Database Mail from SSMS and selected Configure Database Mail.I've then selected to 'Manage Database Mail accounts' and also 'Manage profile security' but neither sections explicitly mention a password that i wish it to connect with.

Database Mail Notification

sairam6819 — Mon, 16 Apr 2012 18:20:14 GMT

Hello , Can you suggest me how to Receive a mail notification, not as soon as job completes but at a particular time after the job completion. In the job setup window, I just have an option in notifications as ' when job succeeds'. I want to get notified in the morning after job succeeds (job runs at night). May be I was thinking that I can use jobhistory tables (job_status column) and write a script such that if status is success, notify operator at particular time. Any suggestions would be appreciated.

Database Mail - test is OK, but jobs do not notify

SQLmannn — Tue, 07 Mar 2006 12:27:00 GMT

I'm not sure I'm posting to the correct thread, but here it is anyway.

We have fired up Database Mail on our 2005 SQL Server and all seems to work on the test side. You can Right Click Database Mail and say Send Test Email and as long as you put in the entire email address i.e. me@yourserver.com the email comes back just fine.

The issue is that when I create a simple job that has one step which is to select from a table that doesn't exist, I cannot get a notification to be sent via email on the failure of the job.

Here is the message I get from the job history:

The last step to run was step 1 (exec syntax error). NOTE: Failed to notify 'MyID' via email.

Here is what is listed in the EventLog:

2006-03-07 12:08:48.543 The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2006-03-07T12:08:48). Exception Message: Cannot send mails to mail server. (The specified string is not in the form required for an e-mail addre

I'm not sure where to go.

Is it something to do with a service account?

We have Database Mail selected in the Alert System under SQL Server Agent. Although the TEST button is dimmed out when Database Mail is selected. I haven't changed it to SQLmail, but if you selec SQLmail from that dropdown the TEST button comes to life.

We want to use Database Mail, so anyone have the answer?

Thanks,

Query Analysis

deepika_goyal — Thu, 11 Oct 2012 05:17:12 GMT

Dear All,Need some Suggestion.I have two tables one is Master and other one is slave.MY master table has some set of Data and slave tables contains data set from many slaves.Here is the sample data Master No. Type Version Date1 Sales V1.0 01/10/20122 Consumption V1.10 29/10/20123 Daily Needs V1.01 13/08/20114 Overall V2.01 01/09/2012 Slave Code No. Type Version DateS001 1 Sales V1.0 01/10/2012S001 2 Consumption V1.10 29/10/2012S001 3 Daily Needs V1.01 13/08/2011S001 4 Overall V2.01 01/09/2012S001 5 Electricity V5.01 18/10/2012S002 1 Sales V1.0 01/10/2012S002 2 Consumption V1.10 15/10/2012S002 3 Daily Needs V1.1000 13/08/2011S003 1 Sales V1.0 01/10/2012S003 2 Consumption V1.10 29/10/2012S003 4 Overall V2.01 01/09/2012S003 3 Daily Needs V1.01 13/08/2011S003 4 Overall V2.01 01/09/2012S003 5 Electricity V5.01 18/10/2012Now, i have to compare Master with slave and then Slave with Master.Here are conditions.1. IF my master and slave matches then report says MATCH2. IF my master and slave doesnt matches then report says MISMATCH3. IF my Master contains more data than slave then report says Missing at Slave4. IF my slave contains more data than Master then report says Missing data at Master.i know how to get the data, but i want quickest way as my slave table contains millions of record, i want my process to be very quick.Summarization of test cases.Final Report Master Slave StatusCode No. Type Version Date Version Date S001 1 ALL MatchS002 2 Daily Needs V1.01 13/08/2011 V1.10013 /08/2011 MismatchS002 3 Daily Needs V1.01 13/08/2011 V1.1000 13/08/2011 MismatchS002 4 Overall V2.01 01/09/2012 V5.01 18/10/2012 Missing in SlaveS003 5 Electricity V5.01 18/10/2012 Missing in Master PLease let me know if anybody have quick way to do it.thanksRegards.Deepika

Sql server agent jobs

sree-226516 — Wed, 03 Aug 2005 15:18:00 GMT

Hi,

Could anybody please tell me how to run sql server agent jobs using control-M?

I am new to Control-M so I will appreciate if somebody throw some loght on Control-M.

Thanks,

sree

Multiple sqlservr.exe on Task Manager

israelthieme — Fri, 17 Aug 2012 08:11:42 GMT

I have only one instance of sql, MSSQLSERVER but on Task Manager, there are 2 sqlservr.exe running. Anyone have any idea why?Below is a snapshot of the Task Manager.Thanks[img]http://israelthieme.com/Capture.PNG[/img]

Unable to copy data to remote machine through BCP!!

arnabb — Wed, 08 Aug 2012 04:38:19 GMT

hi folks,i am trying to copy data to remote server through BCP.I am getting the error while executing the query "Error = [Microsoft][SQL Server Native Client 10.0]Unable to open BCP host data-file"xp_cmdshell 'bcp " select * from DBNAME.._MyTempTable" queryout "\\Machinename\M$\MyData\Outbound\Inbound_08082012.csv" -c -t"," -r"" -S SqlServer -T 'when i am running the query without [b]\\Machinename[/b] its working fine that means its not working with the remote machine. [b]The command is running when i am executing it from cmd prompt but xp_cmdshell is giving error.[/b]I have checked the login under which sql server is running and the machine credentials both are same.user is having the write permission to the folder too.. any help will be highly appreciated.ThanksArnab

Secondry File creation in sql server 2000

anand.mind — Wed, 25 Apr 2012 03:23:34 GMT

I have a sql server 2000 database and it is too large. I decided to create a secondary file, so if new data insertion happens it should go to the secondary file. I have created secondry file with the intial size as 1MB and in the same PRIMARY filegroup. But when I am inserting new data, log file size is increasing only and not the secondry file size. I also limited the maxsize of primary file and ran checkpoint manually.Can anyone help me to test it out that new data will go to secondary file?

Linked server between sql server 2008 64 bit to post gre sql 32 bit

Codezilla-292868 — Wed, 21 Mar 2012 09:03:12 GMT

We have a Post Gre SQL running on a 32 bit machine. I need to create a linked server from a SQL server 2008 R2 instance on a 64 bit windows 2008 machine to a post Gre SQL on 32 bit machine. The post gre sql is on 8.1 version.I created system DSN using the odbc driver downloaded from this linkhttp://code.google.com/p/visionmap/wiki/psqlODBCThe connection works fine on the DSN.The linked server gets created. But when I test the linked server I get the following error:Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server " TEST".OLE DB provider "MSDASQL" for linked server " TEST" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". (Microsoft SQL Server, Error: 7303)I am not sure if the driver I am using the problem or is there some other issue with my script.Here is the script I used to creating the linked server:EXEC master.dbo.sp_addlinkedserver@server = N'TEST',@srvproduct=N'PostgreSQL35W',@provider=N'MSDASQL',@provstr=N'Driver=PostgreSQL 64-bit ODBC Drivers;uid=lichen;Server=rtserver;Port=5432;database=uname;pwd=pwd' /* For security reasons the linked server remote logins password is changed with ######## */EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'TEST',@useself=N'True',@locallogin=NULL,@rmtuser=uname,@rmtpassword=pwdIf there is anything you can help me with that would be great.Thanks in Advance.

Error while opening SQL Server 2000 On local machine

rameshpatibandla — Mon, 19 Mar 2012 02:22:42 GMT

HI all,Am not able to connect the sql server 2000, But am able to connect the sql server2005. Please find the ERROR message below. please help me.OS:win XPSQL: Sql server2000,SQL server 2005.SQL Query Analyzer---------------------------Unable to connect to server XXXXXX:Server: Msg 17, Level 16, State 1[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.

Regarding store procedure

pallavi.unde — Sat, 11 Feb 2012 04:52:13 GMT

Hello,I have written a stroreprocedure in that i have to execute the 4 conditions, for that i used if loop in it..can you please guid me how could i write it in other way...can i use switch case in it??It is as below;ALTER PROCEDURE [dbo].[proc_GetCollegeData1]( @CourseId numeric(18,0), @DegreeId numeric(18,0), @StreamId numeric(18,0) = null, @CityId numeric(18,0) = null, @mode char=null )AS --select * from CollegeMaster -- to get college data when stream id is given and city id is specified if(@mode='A') begin SELECT ROW_NUMBER() OVER(ORDER BY main.CollegeName) as SrNo ,main.[CollegeId],[CollegeLogo],[CollegeName], main.PaidClientPage ,[CourseTypeId],[DegreeId],[StreamgroupId] ,[CollegeDescription],[TypeofCollege],[Grade] ,main.[TrustId],[TrustName],main.[UniversityId],[UniversityName] ,main.[ApprovedId],[ApprovedName],main.[AccredationId],[AccredationName] ,[Status],[YearofEst],[CollegeAccType] ,[AddressStreet],[AddressPin] ,main.[CityId],[CityName],main.[StateId],[StateName] ,main.[CountryId],[CountryName] INTO #tempCollegeData1 FROM CollegeMaster main , College_DegreeStreamTransition degTrans ,College_UniversityMaster univ, College_AccredationMaster acc ,College_ApprovedMaster app , College_TrustMaster trust, College_CityMaster city ,College_CountryMaster coun ,College_StateMaster stat WHERE main.CollegeId = degTrans.CollegeId and main.TrustId=trust.TrustId and main.ApprovedId= app.ApprovedId and main.AccredationId= acc.AccredationId and univ.UniversityId = main.UniversityId and main.CountryId = coun.CountryId and main.CityId = city.CityId and main.StateId= stat.StateId and degTrans.CourseTypeId = @CourseId and DegreeId = @DegreeId and StreamgroupId = @StreamId and main.CityId = @CityId and main.CollegeAccType = 'paid' Group by main.[CollegeId],[CollegeLogo],[CollegeName], main.PaidClientPage ,[CourseTypeId],[DegreeId],[StreamgroupId] ,[CollegeDescription],[TypeofCollege],[Grade] ,main.[TrustId],[TrustName],main.[UniversityId],[UniversityName] ,main.[ApprovedId],[ApprovedName],main.[AccredationId],[AccredationName] ,[Status],[YearofEst],[CollegeAccType] ,[AddressStreet],[AddressPin] ,main.[CityId],[CityName],main.[StateId],[StateName] ,main.[CountryId],[CountryName] ORDER BY main.CollegeName else if(@mode='B') begin -- to get college data when stream id is given and city id is all (not specified) SELECT ROW_NUMBER() OVER(ORDER BY main.CollegeName) as SrNo ,main.[CollegeId],[CollegeLogo],[CollegeName], main.PaidClientPage ,[CourseTypeId],[DegreeId],[StreamgroupId] ,[CollegeDescription],[TypeofCollege],[Grade] ,main.[TrustId],[TrustName],main.[UniversityId],[UniversityName] ,main.[ApprovedId],[ApprovedName],main.[AccredationId],[AccredationName] ,[Status],[YearofEst],[CollegeAccType] ,[AddressStreet],[AddressPin] ,main.[CityId],[CityName],main.[StateId],[StateName] ,main.[CountryId],[CountryName] INTO #tempCollegeData3 FROM CollegeMaster main , College_DegreeStreamTransition degTrans ,College_UniversityMaster univ, College_AccredationMaster acc ,College_ApprovedMaster app , College_TrustMaster trust, College_CityMaster city ,College_CountryMaster coun ,College_StateMaster stat WHERE main.CollegeId = degTrans.CollegeId and main.TrustId=trust.TrustId and main.ApprovedId= app.ApprovedId and main.AccredationId= acc.AccredationId and univ.UniversityId = main.UniversityId and main.CountryId = coun.CountryId and main.CityId = city.CityId and main.StateId= stat.StateId and degTrans.CourseTypeId = @CourseId and DegreeId = @DegreeId and StreamgroupId = @StreamId and main.CollegeAccType = 'paid'--Added by Pallavi on 27 june 2011 Group by main.[CollegeId],[CollegeLogo],[CollegeName], main.PaidClientPage ,[CourseTypeId],[DegreeId],[StreamgroupId] ,[CollegeDescription],[TypeofCollege],[Grade] ,main.[TrustId],[TrustName],main.[UniversityId],[UniversityName] ,main.[ApprovedId],[ApprovedName],main.[AccredationId],[AccredationName] ,[Status],[YearofEst],[CollegeAccType] ,[AddressStreet],[AddressPin] ,main.[CityId],[CityName],main.[StateId],[StateName] ,main.[CountryId],[CountryName] ORDER BY main.CollegeNameelse if(@mode='C') begin -- to get college data when stream id is all and city id is specified SELECT distinct main.[CollegeId],[CollegeLogo],[CollegeName] ,course.[CourseTypeId],degree.[DegreeId], main.PaidClientPage ,[CollegeDescription],[TypeofCollege],[Grade] ,main.[TrustId],[TrustName],main.[UniversityId],[UniversityName] ,main.[ApprovedId],[ApprovedName],main.[AccredationId],[AccredationName] ,[Status],[YearofEst],[CollegeAccType] ,[AddressStreet],[AddressPin] ,main.[CityId],[CityName],main.[StateId],[StateName] ,main.[CountryId],[CountryName] INTO #tempCollegeData5 FROM CollegeMaster main , College_DegreeStreamTransition degTrans ,College_UniversityMaster univ, College_AccredationMaster acc ,College_ApprovedMaster app , College_TrustMaster trust, College_CityMaster city ,College_CountryMaster coun ,College_StateMaster stat, college_coursetypemaster course, college_degreemaster degree WHERE main.CollegeId = degTrans.CollegeId and main.TrustId=trust.TrustId and main.ApprovedId= app.ApprovedId and main.AccredationId= acc.AccredationId and univ.UniversityId = main.UniversityId and main.CountryId = coun.CountryId and main.CityId = city.CityId and course.courseTypeId=degTrans.courseTypeId and degree.degreeid=degTrans.degreeid and main.StateId= stat.StateId and course.CourseTypeId = @CourseId and degree.DegreeId = @DegreeId and main.CityId = @CityId and main.CollegeAccType = 'paid' ORDER BY main.CollegeNameelse if(@mode='D') begin -- to get college data when stream id is all and city id is all SELECT distinct main.[CollegeId],[CollegeLogo],[CollegeName] ,course.[CourseTypeId],degree.[DegreeId], main.PaidClientPage ,[CollegeDescription],[TypeofCollege],[Grade] ,main.[TrustId],[TrustName],main.[UniversityId],[UniversityName] ,main.[ApprovedId],[ApprovedName],main.[AccredationId],[AccredationName] ,[Status],[YearofEst],[CollegeAccType] ,[AddressStreet],[AddressPin] ,main.[CityId],[CityName],main.[StateId],[StateName] ,main.[CountryId],[CountryName] into #tempCollegeData7 FROM CollegeMaster main , College_DegreeStreamTransition degTrans ,College_UniversityMaster univ, College_AccredationMaster acc ,College_ApprovedMaster app , College_TrustMaster trust, College_CityMaster city ,College_CountryMaster coun ,College_StateMaster stat,college_coursetypemaster course, college_degreemaster degree WHERE main.CollegeId = degTrans.CollegeId and main.TrustId=trust.TrustId and main.ApprovedId= app.ApprovedId and main.AccredationId= acc.AccredationId and univ.UniversityId = main.UniversityId and main.CountryId = coun.CountryId and main.CityId = city.CityId and course.courseTypeId=degTrans.courseTypeId and degree.degreeid=degTrans.degreeid and main.StateId= stat.StateId and course.CourseTypeId = @CourseId and degree.DegreeId = @DegreeId and main.CollegeAccType = 'paid' ORDER BY main.CollegeName SELECT distinct main.[CollegeId],[CollegeLogo],[CollegeName] ,course.[CourseTypeId],degree.[DegreeId], main.PaidClientPage ,[CollegeDescription],[TypeofCollege],[Grade] ,main.[TrustId],[TrustName],main.[UniversityId],[UniversityName] ,main.[ApprovedId],[ApprovedName],main.[AccredationId],[AccredationName] ,[Status],[YearofEst],[CollegeAccType] ,[AddressStreet],[AddressPin] ,main.[CityId],[CityName],main.[StateId],[StateName] ,main.[CountryId],[CountryName] into #tempCollegeData8 FROM CollegeMaster main , College_DegreeStreamTransition degTrans ,College_UniversityMaster univ, College_AccredationMaster acc ,College_ApprovedMaster app , College_TrustMaster trust, College_CityMaster city ,College_CountryMaster coun ,College_StateMaster stat, college_coursetypemaster course, college_degreemaster degree WHERE main.CollegeId = degTrans.CollegeId and main.TrustId=trust.TrustId and main.ApprovedId= app.ApprovedId and main.AccredationId= acc.AccredationId and univ.UniversityId = main.UniversityId and main.CountryId = coun.CountryId and main.CityId = city.CityId and course.courseTypeId=degTrans.courseTypeId and degree.degreeid=degTrans.degreeid and main.StateId= stat.StateId and course.CourseTypeId = @CourseId and degree.DegreeId = @DegreeId and main.CollegeAccType = 'Unpaid' ORDER BY main.CollegeName end I wanted to write it in other way.....

Time-out occurred while waiting for buffer latch type 2 for page

zayousafi — Mon, 13 Apr 2009 02:31:29 GMT

Hi, I am facing a strange exception from SqlServer. All the DML queries work fine and provides result normally. But when i try to get the schema of my DB either through Microsoft SQlserver Management Studio or by executing Information_Schema.Tables Query, I am getting following exceptionFailed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476------------------------------ADDITIONAL INFORMATION:An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)------------------------------Time-out occurred while waiting for buffer latch type 2 for page (1:68), database ID 18. (Microsoft SQL Server, Error: 845)For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=845&LinkId=20476all other DBs on the same server works fine. Any Idea what is happening and how can I fix this problem

Service Broker

kfewer — Thu, 26 Jan 2012 13:41:30 GMT

Is Service Broker activated when Notification Services is installed? I have multiple servers where I set up mail notification and the only time it ever asked for Service Broker to be activated was on a server that didn't have Notification Services. The relation seems logical to me but I can't find it in writing anywhere. thx.

Duplicating Database onto DR site

sudha_sql — Tue, 10 Jan 2012 10:47:33 GMT

Hello Everybody,I am not sure immediately-updating replication is my solution .....hence the subjectWe have a high profile application that has a front-end application server with SQL2008R2 database on a 3-node windows 2008 cluster. Although the database cluster / actual database is up/available and the application server is available - we want the high-profile board members to be directed to DR site in case of network issues - and they cant reach the application/database.I have initiated replication with the transaction logs sent across every 10 minutes, and manual redirection from application server to DR site - but the requirement is more like 'No manual intervention for failover or failback' and if necessary duplicate application server at DR site. I guess the issue is more like the database being located in primary and DR site being completely in sync at all times and syncing back even after a 1-2 hour downtime on one side. If there are 3rd party tools out there tat you have used that information will also be helpful. Thanks in advance.sda33

Remote access Error to SQL 2005/Cannot generate SSPI context.

Gabicus — Mon, 24 Mar 2008 16:42:21 GMT

I have just created a new SQL 2005 Server that I am not able to get access to remotely with SQL Server Management Studio.What I have is a domain account with Admin Privileges on the domain. On the SQL Server I have the services set to log on with a domain account. This Domain account is starting the following services:SQL ServerSQL Server AgentSQL Server Analysis ServiceSQL Server BrowserSQL Server Full Text searchSQL Server Integration ServicesSQL Server Reporting ServiceIf I try and access the DB with SSMS and use SQL Server Authentication I am able to access the DB. But when I try and use Windows Authentication I get this error.An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 28 - Server doesn't support requested protocol) (Microsoft SQL Server, Error: -1)But when I go in to configure manager and enable Named Pipe it I can use both types for login. The problem is that when I do that I can no longer use the Maintenance Plan because I get this Error:------------------------------ADDITIONAL INFORMATION:An OLE DB error 0x80004005 (Cannot generate SSPI context) occurred while enumerating packages. A SQL statement was issued and failed.------------------------------An OLE DB error 0x80004005 (Cannot generate SSPI context) occurred while enumerating packages. A SQL statement was issued and failed.I have tried:regsvr32 msxml6.dllregsvr32 msxml3.dllto register the DLL but I am still getting the same error.I am not sure what to do so that I will not need to use Named Piped, or even if that will fix the Cannot generate SSPI context issue. Any suggestions what to look at would be great.thanks,Gabicus

scripts to move indexes to diffrnt file groups

janardhan4891 — Fri, 23 Dec 2011 04:49:47 GMT

need some assistance regarding scripts...The requirement as follows------------------------- Move all non-clustered indexes to INDEX file group Move all user (U) type tables and clustered indexes to DATA file groupFor the above,we have 4 schemas and 150+ tables in the database,can we move all indexex/tables at a time using scripts

[364] the messenger service has not been started

Tim Ermlich — Mon, 20 Dec 2004 13:38:00 GMT

- NetSend notifications will not be sent

I have this message in the error log for SQL Server Agent, which is running. I have tried bouncing the service and the server with no luck. Running on Windows Server 2003. I cannot detrmine why the service will not start and cannot figure out how to start it.

There are multiple accounts with name MSSQLSvc

Javier Espinosa — Mon, 18 Apr 2005 17:05:00 GMT

Im getting this error in the Event Viewer and the is something slower. What should i do?There are multiple accounts with name MSSQLSvc/Server.domain.com:1433 of type 10

Thanks to anybody can help me.

SQL Cluster: Failover not happening

sivaprasad — Mon, 23 Aug 2004 02:12:00 GMT

Brief on the current environment:

SQL Server 2000 Enterprise Edition

Windows 2000 Enterprise Edition

Service Pack 3a and KB 859 - Prerequisite applied.

Active/ Passive cluster

Problem:

Automatic failover is not happening.

Errors found on SQL Error Log:

Waiting for type 0x4, current count 0xa, current owning EC 0x43D37588.

Time out occurred while waiting for buffer latch type 4,bp 0x2d9c780, page 1:41), stat 0xb, object ID 2:2:0, EC 0x54DF3570 : 0, waittime 300. Not continuing to wait.

Errors on NT Event Viewer:

Event Id: 17052

[sqsrvres] checkODBCConnectError: sqlstate = 08001; native error = b; message = [Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check your network documentation.

[sqsrvres] ODBC sqldriverconnect failed

[sqsrvres] checkODBCConnectError: sqlstate = 01000; native error = 274c; message = [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (PreLoginHandshake()).

[sqsrvres] ODBC sqldriverconnect failed

[sqsrvres] checkODBCConnectError: sqlstate = 08001; native error = b; message = [Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check your network documentation.

[sqsrvres] CheckQueryProcessorAlive: sqlexecdirect failed

Any help on cause of the problem and resolution will be apprieciated.

Query Optimization

adlakha.22 — Wed, 12 Oct 2011 12:24:47 GMT

Hi,I am working with sql server 2000 and 2005,could any one suggest me how tooptimize a select query which is also using aggregate function on some of its columns.I have tried the following alternatives,1. I have used index on the table which is using the column order same as in where clause.but in this case RID/KEY lookup is taking primary key index and consuming most of the cost of the QUERY EXECTION. Execution Plan Of this case is Shown in Image-12. To avoid RID/KEY lookup,I have used INCLUDE Index,So that all the non primary keys are also included in index.In this case Sort Operator is consuming too much time and again i am at the same place,no improvement in reducing Execution time of Query. Execution Plan for this case is shown in image-2 below where sort opertator is taking 70% of CPU Cost.Please help me out of this issue.

Software Review "Spotlight on SQL Server Enterprise" by quest

kingram-15027 — Mon, 28 Aug 2006 08:49:00 GMT

anyone had any experiance with this software. i am evaluating software for my company and was looking for some real world input

How to Write Trigger in Sql Server 2005

Abhay Jadhav — Wed, 14 Mar 2007 02:52:00 GMT

Hello all,

I want to know that, if i want to write trigger in sql server 2005 from where should i do it. or if i want to view trigger which has been already there for table.

plz help me out.

thx in advance

Abhay

Centralise Connection For WInform application

j.sagar53 — Mon, 01 Aug 2011 23:45:13 GMT

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 ...i get this error while establishing a connection from client pc.plz suggest me the solutions.

Sql Server 2000 installatin issue

pratapgajjala — Tue, 19 Jul 2011 01:06:35 GMT

Hi Friends i have installed the sql server 2000 standard edition . After installation, in DTS designer, under Tasks, the task components are not coming. what can i do ... Pls let meThanks

Database Mail Not starting

Craig Simpson — Thu, 06 Jul 2006 10:39:00 GMT

I've installed SQL Server 2005 on 3 different servers and have finally installed it on my production server. I went to configure Database Mail and get the expected dialog "Database mail depends on Service Broker...Do you want to enable Service Broker on msdb"? I select yes and then nothing happens. It just hangs. I've let it run for over an hour. I've tried the Surface Area Configuration tool and in the logs I see the following, "Setting database option ENABLE_BROKER to ON for database msdb."Still everytime I go to setup a profile I get the same, "do you want to enable Service Broker" message and then hang. I've done this on all my other servers without problem.Thanks.

Timeout occurred while waiting for latch: class 'ACCESS_METHODS_SCAN_RANGE_GENERATOR'

Corey — Sun, 26 Jun 2011 11:36:53 GMT

Edit:Sorry guys. Wrong forum. I will delete.

ADAPTIVE COMPRESSION ON LITESPEED V6.5

Arunraja — Wed, 15 Jun 2011 01:49:06 GMT

Hello,I came across a different kind of backup on SQL server Litespeed V6.5 called ADAPTIVE compression and in the previous versions Litespeed is having SmartDiff backup.Could anyone explain me the difference between both the backup types? Below is the explanation provided in the Quest. But i want to know the difference between the adaptive compression and smartdiff."The new Adaptive Compression feature provides you with more flexibility over the existing LiteSpeed compression levels. Adaptive Compression maintains optimal backup performance by continually monitoring the backup and dynamically changing the compression level based on available server resources. Backups can be optimized for speed or size, while ensuring they do not interfere with normal server activity. "Thanks in advacne for your response!RegardsArunraja

Canno see SQL Server Agent properties despite sysadmin permission

paulnewman — Wed, 14 May 2008 09:00:47 GMT

I'm connecting to a SQL server 2000 (SP4) instance via a domain account that has sysadmin privileges but when I look at the properties for the SQL Server Agent the SQL Service Startup account is greyed out. Also the SQL Server Agent doesn't show as either stopped or started in Enterprise Manager. No problems if I logged on to the server directly.

SQLDUMP GENERATED

prasad.kambli — Fri, 04 Feb 2011 03:23:50 GMT

===================================================================== BugCheck Dump ===================================================================== This file is generated by Microsoft SQL Server version 9.00.4230.00 upon detection of fatal unexpected error. Please return this file, the query or program that produced the bugcheck, the database and the error log, and any other pertinent information with a Service Request. Computer type is AT/AT COMPATIBLE. Current time is 15:06:29 02/03/11. 16 Unknown CPU 9., 2267 Mhz processor (s). Windows NT 5.2 Build 3790 CSD Service Pack 2. Memory MemoryLoad = 96% Total Physical = 32757 MB Available Physical = 1221 MB Total Page File = 99020 MB Available Page File = 67746 MB Total Virtual = 8388607 MB Available Virtual = 8354973 MB ***Stack Dump being sent to F:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLDump0191.txt SqlDumpExceptionHandler: Process 17 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process. * ******************************************************************************* * * BEGIN STACK DUMP: * 02/03/11 15:06:29 spid 17 * * * Exception Address = 0000000001C5C664 Module(sqlservr+0000000000C5C664) * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION * Access Violation occurred writing address 0000000000000020 * * * MODULE BASE END SIZE * sqlservr 0000000001000000 0000000003661FFF 02662000 * ntdll 0000000077EC0000 0000000077FF8FFF 00139000 * kernel32 0000000077D40000 0000000077EB2FFF 00173000 * MSVCR80 0000000078130000 00000000781F8FFF 000c9000 * msvcrt 000007FF7FC00000 000007FF7FC85FFF 00086000 * MSVCP80 000000007C420000 000000007C528FFF 00109000 * ADVAPI32 000007FF7FEE0000 000007FF7FFE4FFF 00105000 * RPCRT4 000007FF7FD30000 000007FF7FECFFFF 001a0000 * Secur32 000007FF7E9C0000 000007FF7E9E1FFF 00022000 * sqlos 00000000344D0000 00000000344D6FFF 00007000 * USERENV 000007FF7C680000 000007FF7C789FFF 0010a000 * USER32 0000000077C20000 0000000077D2BFFF 0010c000 * GDI32 000007FF7FC90000 000007FF7FD2AFFF 0009b000 * WINMM 000007FF7E240000 000007FF7E28FFFF 00050000 * opends60 00000000333E0000 00000000333E7FFF 00008000 * NETAPI32 000007FF77370000 000007FF77408FFF 00099000 * psapi 000007FF7E380000 000007FF7E38FFFF 00010000 * instapi 0000000048060000 000000004806CFFF 0000d000 * CLUSAPI 000007FF7B3A0000 000007FF7B3C3FFF 00024000 * ole32 000007FF57140000 000007FF573C4FFF 00285000 * OLEAUT32 000007FF7EBC0000 000007FF7ECD5FFF 00116000 * RESUTILS 000007FF7B310000 000007FF7B32BFFF 0001c000 * sqlevn70 000000004F610000 000000004F7B8FFF 001a9000 * rsaenh 0000000068000000 000000006804BFFF 0004c000 * AUTHZ 000007FF7E490000 000007FF7E4BDFFF 0002e000 * MSCOREE 000006427EE60000 000006427EED3FFF 00074000 * msv1_0 000007FF7E500000 000007FF7E543FFF 00044000 * WS2_32 000007FF77310000 000007FF7733FFFF 00030000 * WS2HELP 000007FF77140000 000007FF7714BFFF 0000c000 * iphlpapi 000007FF57040000 000007FF57070FFF 00031000 * kerberos 000007FF77410000 000007FF774C3FFF 000b4000 * cryptdll 000007FF7DC90000 000007FF7DC9EFFF 0000f000 * MSASN1 000007FF7D340000 000007FF7D369FFF 0002a000 * schannel 000007FF7DD50000 000007FF7DD90FFF 00041000 * CRYPT32 000007FF7D370000 000007FF7D4CEFFF 0015f000 * COMRES 000007FF7EAF0000 000007FF7EBB5FFF 000c6000 * XOLEHLP 000007FF5C5B0000 000007FF5C5B6FFF 00007000 * MSDTCPRX 000007FF67140000 000007FF67213FFF 000d4000 * msvcp60 000000001B180000 000000001B269FFF 000ea000 * MTXCLU 000007FF7B540000 000007FF7B569FFF 0002a000 * VERSION 000007FF7FBF0000 000007FF7FBFAFFF 0000b000 * WSOCK32 000007FF770F0000 000007FF770F9FFF 0000a000 * mswsock 000007FF771B0000 000007FF7722CFFF 0007d000 * DNSAPI 000007FF7E8F0000 000007FF7E93DFFF 0004e000 * winrnr 000007FF7E9F0000 000007FF7E9FAFFF 0000b000 * WLDAP32 000007FF7E950000 000007FF7E9B5FFF 00066000 * rasadhlp 000007FF7EA00000 000007FF7EA06FFF 00007000 * hnetcfg 000007FF6D200000 000007FF6D292FFF 00093000 * wshtcpip 000007FF77170000 000007FF7717AFFF 0000b000 * security 000007FF77530000 000007FF77534FFF 00005000 * msfte 0000000049980000 0000000049D30FFF 003b1000 * dbghelp 000000001DE40000 000000001DF9CFFF 0015d000 * WINTRUST 000007FF7E3E0000 000007FF7E42DFFF 0004e000 * imagehlp 000007FF7E470000 000007FF7E482FFF 00013000 * dssenh 0000000068100000 000000006813EFFF 0003f000 * NTMARTA 000007FF7E4C0000 000007FF7E4FBFFF 0003c000 * SAMLIB 000007FF77150000 000007FF77165FFF 00016000 * ntdsapi 000007FF7DCB0000 000007FF7DCD3FFF 00024000 * xpsp2res 00000000207E0000 0000000020AA6FFF 002c7000 * CLBCatQ 000007FF7EA10000 000007FF7EAEDFFF 000de000 * sqlncli 00000000337A0000 0000000033A63FFF 002c4000 * COMCTL32 000007FF76200000 000007FF762EEFFF 000ef000 * comdlg32 000007FF7D540000 000007FF7D5B6FFF 00077000 * SHLWAPI 000007FF7EF60000 000007FF7EFFAFFF 0009b000 * SHELL32 000007FF7F190000 000007FF7FB99FFF 00a0a000 * comctl32 000007FF7F000000 000007FF7F186FFF 00187000 * SQLNCLIR 0000000035000000 0000000035032FFF 00033000 * msftepxy 00000000039E0000 0000000003A00FFF 00021000 * xpsqlbot 000000004A7C0000 000000004A7C7FFF 00008000 * xpstar90 0000000053C30000 0000000053CB7FFF 00088000 * SQLSCM90 0000000053AD0000 0000000053ADBFFF 0000c000 * ODBC32 000007FF63F00000 000007FF63F66FFF 00067000 * BatchParser90 00000000520C0000 00000000520ECFFF 0002d000 * ATL80 000000007C630000 000000007C64EFFF 0001f000 * odbcint 000007FF63DC0000 000007FF63DD7FFF 00018000 * xpstar90 0000000029F70000 0000000029F95FFF 00026000 * xplog70 0000000034730000 000000003473FFFF 00010000 * xplog70 000000002A000000 000000002A002FFF 00003000 * odsole70 0000000033380000 0000000033397FFF 00018000 * wbemprox 000007FF7B070000 000007FF7B07CFFF 0000d000 * wbemcomn 000007FF7B7E0000 000007FF7B864FFF 00085000 * SXS 000007FF7CC60000 000007FF7CE46FFF 001e7000 * wmiutils 000007FF7B400000 000007FF7B42EFFF 0002f000 * wbemsvc 000007FF7B050000 000007FF7B061FFF 00012000 * fastprox 000007FF7C030000 000007FF7C10BFFF 000dc000 * scrrun 000007FF79170000 000007FF791B4FFF 00045000 * MFC42 000007FF796F0000 000007FF79859FFF 0016a000 * xpweb90 0000000053E30000 0000000053E4AFFF 0001b000 * WININET 000007FF7D200000 000007FF7D32AFFF 0012b000 * xpweb90 0000000003690000 0000000003695FFF 00006000 * mlang 000007FF7A4C0000 000007FF7A56EFFF 000af000 * mpr 000007FF772D0000 000007FF772F0FFF 00021000 * msxmlsql 0000000078800000 0000000078967FFF 00168000 * msxml3 000007FF56D40000 000007FF56F57FFF 00218000 * * P1Home: FFFFFADD40389D90: * P2Home: 0000000027D5DB90: FFFFFADD40389D90 0000000027D5DB90 0000000000000000 000000065D29AB40 0000000000000002 FFFFFADD40389D90 * P3Home: 0000000000000000: * P4Home: 000000065D29AB40: 000000000163F950 000000043A6635E0 000000043A663590 00000003644C4800 000000065D29AB90 000000065D29ABB0 * P5Home: 0000000000000002: * P6Home: FFFFFADD40389D90: * ContextFlags: 000000000010001F: * MxCsr: 0000000000001FB0: * SegCs: 0000000000000033: * SegDs: 000000000000002B: * SegEs: 000000000000002B: * SegFs: 0000000000000053: * SegGs: 000000000000002B: * SegSs: 000000000000002B: * EFlags: 0000000000010206: 0045004400000065 0047004F004C0046 003D005200490044 0044005C003A0043 006D00750063006F 00730074006E0065 * Rax: 0000000000000000: * Rcx: 00000000802D22B0: 00000000802D2080 0000128500262AA3 0000129700001295 0000128800001293 00000000802D2350 000000FF00000100 * Rdx: 0000000000000000: * Rbx: 0000000000000000: * Rsp: 0000000027D5E130: 0000000100004CE8 0000000000040EC6 00000000802D22B0 00000000802D22B0 0000000000000000 000000000212EC87 * Rbp: 000000065D29AB40: 000000000163F950 000000043A6635E0 000000043A663590 00000003644C4800 000000065D29AB90 000000065D29ABB0 * Rsi: 00000000802D22B0: 00000000802D2080 0000128500262AA3 0000129700001295 0000128800001293 00000000802D2350 000000FF00000100 * Rdi: 00000000802D22B0: 00000000802D2080 0000128500262AA3 0000129700001295 0000128800001293 00000000802D2350 000000FF00000100 * R8: 0000000027D5E188: 00000000802D3700 4887506900000002 0000000100004CE8 000000065D292AD8 0000000000000000 0000000027D5E230 * R9: 0000000000000000: * R10: 0000000000000000: * R11: 00000000000000D2: * R12: 0000000027D5E4A0: 0000000000102B00 0000000000000002 2121212121210000 00000003644C5290 0000000000100000 0000000048875069 * R13: 0000000001000000: 0000000300905A4D 0000FFFF00000004 00000000000000B8 0000000000000040 0000000000000000 0000000000000000 * R14: 0000000001635650: 10498B4C38EC8348 247C894820418B48 498B450C418B4558 148B4E207A8D4818 A0988B4C028B49C0 FD9E058D48000000 * R15: 0000000001635180: 10498B4C38EC8348 247C894820418B48 498B450C418B4558 C0148B4EFA8B4818 00A0988B4C028B49 00026F058D480000 * Rip: 0000000001C5C664: D28B44FF20418341 C98B493775C18B4C 74C085FFEF6347E8 08518B49018B492B 4902894808508948 49000000000841C7 * ******************************************************************************* * ------------------------------------------------------------------------------- * Short Stack Dump 0000000001C5C664 Module(sqlservr+0000000000C5C664) 000000000212EC87 Module(sqlservr+000000000112EC87) 000000000213E88A Module(sqlservr+000000000113E88A) 0000000002145106 Module(sqlservr+0000000001145106) 00000000021D1888 Module(sqlservr+00000000011D1888) 0000000002316B32 Module(sqlservr+0000000001316B32) 00000000023174C3 Module(sqlservr+00000000013174C3) 000000000163C723 Module(sqlservr+000000000063C723) 000000000163FFD4 Module(sqlservr+000000000063FFD4) 000000000162B791 Module(sqlservr+000000000062B791) 000000000161E5FC Module(sqlservr+000000000061E5FC) 0000000001663899 Module(sqlservr+0000000000663899) 0000000001669E8D Module(sqlservr+0000000000669E8D) 0000000001AFFAD6 Module(sqlservr+0000000000AFFAD6) 0000000001742ACA Module(sqlservr+0000000000742ACA) 000000000171C7D5 Module(sqlservr+000000000071C7D5) 00000000016DA8A5 Module(sqlservr+00000000006DA8A5) 00000000016B0BCE Module(sqlservr+00000000006B0BCE) 00000000016FC574 Module(sqlservr+00000000006FC574) 000000000167139E Module(sqlservr+000000000067139E) 0000000001670B89 Module(sqlservr+0000000000670B89) 0000000001676CA4 Module(sqlservr+0000000000676CA4) 00000000017AFCB7 Module(sqlservr+00000000007AFCB7) 00000000014C5A79 Module(sqlservr+00000000004C5A79) 00000000017B02C0 Module(sqlservr+00000000007B02C0) 00000000017F9B88 Module(sqlservr+00000000007F9B88) 00000000781337D7 Module(MSVCR80+00000000000037D7) 0000000078133894 Module(MSVCR80+0000000000003894) 0000000077D6B6DA Module(kernel32+000000000002B6DA) PSS @0x00000003B1A3EB60-----------------------CSession @0x00000003B1A3E2B0----------------------------m_spid = 17 m_cRef = 5 m_rgcRefType[0] = 1 m_rgcRefType[1] = 0 m_rgcRefType[2] = 2 m_rgcRefType[3] = 1 m_rgcRefType[4] = 0 m_rgcRefType[5] = 1 m_pmo = 0x00000003B1A3E080 m_pstackBhfPool = 0x0000000000000000 m_dwLoginFlags = 0x0000 m_fBackground = -1 m_fClientRequestConnReset = 0 m_fUserProc = 0 m_fConnReset = 0 m_fIsConnReset = 0 m_fInLogin = 0 m_fAuditLoginSent = 0 m_fAuditLoginFailedSent = 0 m_fReplRelease = 0 m_fKill = 0 m_ulLoginStamp = 38625 m_eclClient = 5 m_protType = 0 m_hHttpToken = FFFFFFFFFFFFFFFF m_pV7LoginRec---------------------0000000000000000: 00000000 00000000 00000000 00000000 00000000 †.................... 0000000000000014: 00000000 00000000 00000000 00000000 00000000 †.................... 0000000000000028: 00000000 00000000 00000000 00000000 00000000 †.................... 000000000000003C: 00000000 00000000 00000000 00000000 00000000 †.................... 0000000000000050: 00000000 00000000 00000000 0000†††††††††††††††.............. CBatch @0x00000003B1A3F1E0--------------------------m_pSess = 0x00000003B1A3E2B0 m_pConn = 0x00000003B1A3EA50 m_cRef = 2 m_rgcRefType[0] = 1 m_rgcRefType[1] = 1 m_rgcRefType[2] = 0 m_rgcRefType[3] = 0 m_rgcRefType[4] = 0 m_pTask = 0x0000000003C45048 EXCEPT (null) @0x0000000027D5B058---------------------------------exc_number = 0 exc_severity = 0 exc_func = 0x0000000001B13010 Task @0x0000000003C45048------------------------CPU Ticks used (ms) = 53 Task State = 2 WAITINFO_INTERNAL: WaitResource = 0x0000000000000000 WAITINFO_INTERNAL: WaitType = 0x0 WAITINFO_INTERNAL: WaitSpinlock = 0x0000000000000000 SchedulerId = 0xe ThreadId = 0x1078 m_state = 0 m_eAbortSev = 0 EC @0x00000003B1A3EB70----------------------spid = 17 ecid = 0 ec_stat = 0x0 ec_stat2 = 0x0 ec_atomic = 0x0 __fSubProc = 1 ec_dbccContext = 0x0000000000000000 __pSETLS = 0x00000003B1A3EAC0 __pSEParams = 0x0000000027D5F620 __pDbLocks = 0x0000000000000000 SEInternalTLS @0x00000003B1A3EAC0--------------------------------- m_flags = 0 m_TLSstatus = 3 m_owningTask = 0x0000000003C45048 m_activeHeapDatasetList = 0x00000003B1A3EAC0 m_activeIndexDatasetList = 0x00000003B1A3EAD0 SEParams @0x0000000027D5F620---------------------------- m_lockTimeout = -1 m_isoLevel = 4096 m_logDontReplicate = 1 m_neverReplicate = 1 m_XactWorkspace = 0x00000006060C3090 m_pSessionLocks = 0x0000000027D5F720 m_pDbLocks = 0x0000000027D5FA20 m_execStats = 0x00000000804783F0 m_pAllocFileLimit = 0x0000000000000000

FTP the back up from one server to antoher server

bvsc — Fri, 28 Jan 2011 09:51:13 GMT

Hi I am requesting some one explain the FTP process, I want to ftp the backup from one server to another. I found the solution and was trying to apply, Some times it is working some times it is giving me an error saying Connection close by remote host.I have been getting the same message very frequently. I dont know the reason as i dont have much knowledge on networking and harware. I would really appreciate the response. Thanks

What isthe max length of an SQL Server instance name?

SQL ORACLE — Wed, 16 May 2007 09:34:00 GMT

Could anyone tell me what the maximum length of an SQL Server instance name is?

How about an SQL Server cluster?

Are there any difference in SQL Server 2000 and SQL Server 2005?

Where can we get the information?

Any input will be greatly appreciated.

MSSQL Error: 18210

Helen-84411 — Tue, 17 May 2005 01:52:00 GMT

Hi,

Getting this error in production

MSSQL Error: 18210. spid217 BackupMedium::ReportIoError: write failure on backup device

any help appreciated

SELECT STATMAN in profiler ????

peddi_praveen — Thu, 16 Oct 2003 15:03:00 GMT

hi guys, while i run a Stored Proc and profiling this , i had encountered these kinda statements.SELECT statman([ListingAddressPostalCode],[ListingAddressStreetNumber],[ListingAddressStreetName],[ListingAddressUnitNumber],[DataSourceID],[AMListingID],@PSTATMAN) FROM (SELECT TOP 100 PERCENT [ListingAddressPostalCode],[ListingAddressStreetNumber],[ListingAddressStreetName],[ListingAddressUnitNumber],[DataSourceID],[AMListingID] FROM [dbo].[LISTING] WITH(READUNCOMMITTED,SAMPLE 1.000000e+002 PERCENT) ORDER BY [ListingAddressPostalCode],[ListingAddressStreetNumber],[ListingAddressStreetName],[ListingAddressUnitNumber],[DataSourceID],[AMListingID]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER_QUEUE, MAXDOP 1)any help would be appreciated.Praveen Kumar Peddi

FTP the back up from one server to antoher server

bvsc — Thu, 17 Feb 2011 08:31:22 GMT

Hi I have been trying to do FTP through SQL Script.The script does connect the destination server and drop the file in appropriate folder.It is working fine with small size of file, But I have the file size 20gb It is not working and giving the following error: Connection closed by remote host. Netout :Connection reset by peerMy intention is to schedule a job,which transfer the backup file from Production environment to Dev and there I have to write anotherjob to restore the database when ever the new file came or overwrite occurs on the old file.Can some one please help me on that. Thanks in advance.

Database Mail Error

ravic0607 — Fri, 08 Aug 2008 00:44:51 GMT

WHEN I SEND MAIL THROUGH DATABASE MAIL THEN THREE ERRORS I GOT IN [msdb].[dbo].[sysmail_log] BUT MAIL IS RECEIVED BY THE USER.ERROR-1---------Invalid XML message format received on the ExternalMailQueue. conversation_handle: 1751E177-8C64-DD11-991D-001A644FD5EE. message_type_name: {//www.microsoft.com/databasemail/messages}SendMailStatus.message body: <responses:SendMail xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://schemas.microsoft.com/databasemail/responses ResponseTypes.xsd" xmlns:responses="http://schemas.microsoft.com/databasemail/responses"> .====================================================================================================================================================================ERROR-2---------1) Exception Information =================== Exception Type: System.Data.SqlClient.SqlException Errors: System.Data.SqlClient.SqlErrorCollection Class: 16 LineNumber: 1 Number: 6602 Procedure: sp_xml_preparedocument Server: ETEC-SQL State: 2 Source: .Net SqlClient Data Provider ErrorCode: -2146232060 Message: The error description is 'Switch from current encoding to specified encoding not supported.'. The XML parse error 0xc00ce56f occurred on line number 1, near the XML text " ". Data: System.Collections.ListDictionaryInternal TargetSite: Void OnError(System.Data.SqlClient.SqlException, Boolean) HelpLink: NULL StackTrace Information =================== at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) 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.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.DataAccessAdapter.PostResponse(Response r, Guid convHandle) at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.ThreadCallBack.PostResponseIfNeeded(MailInfo mi, Response r) at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.ThreadCallBack.MailOperation(Object o)======================================================================================================================================================================ERROR-3---------Invalid XML message format received on the ExternalMailQueue. conversation_handle: 1451E177-8C64-DD11-991D-001A644FD5EE. message_type_name: {//www.microsoft.com/databasemail/messages}SendMailStatus. message body: ?????????•??????????????????????????????????????????????•????????????????????????????????????????????=????????????????????????????????????†???????††?????????•??????????†?????????•?????????????•???????????????†????????????????????†????????????•???????????? .

Primary Key Vs Clustered Index: Performance Tuning

iamanks — Tue, 21 Dec 2010 00:31:53 GMT

I've a performance related issue. I've one big table A which is been used in one query having join with another big table B. Table A has no Primary key but a clustered index defined on four columns (composite) and two more nonclustered indexes. Table B also has no primary key but a clustered index with five columns (again composite) and three non clustered indexes. All of the indexes are on more than one column.While running the query, it taking too much time and execution plan shows 23% of sort (execution plan shows its in one of the case statements with "in (select ")) on one column of table A which is part of clustered index. How should I resolve it ? Secondly, what is the significance of PK and clustered index separately ? Table A comprises of 35% of index seek and Table B shows 18% of index seek.The query comprises of joins between these two tables and inserting in another table. Lots of columns in select statement and also few case statements with "exists (select" in them.