Friday, June 21, 2013

[MS SQL Server] SQL browser

[MS SQL Server] SQL browser

SQL browser

Posted: 21 Jun 2013 04:14 AM PDT

If I have two instances running on a server and the instances are both named instances.Do [b]I have to[/b] do some special configuration of SQL browser, or leave as default should be OK?

Database Mail failing with attachment

Posted: 20 Jun 2013 09:00 AM PDT

We are using Database Mail on SQL Server 2008 R2 to send results of queries as jobs using SQL Server Agent. We are using an MS exchange mail server (mail.mycompany.com) & the default port 25. All emails that have the query results in the body of the email work fine. However, those that use @attach_query_result_as_file = 1 or those that have nothing in the body of the email fail with this error message:The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2013-06-20T13:54:39). Exception Message: Cannot send mails to mail server. (Mailbox unavailable. The server response was: 5.7.1 Requested action not taken: message refused). )The event viewer & error logs on the mail server provide no data, which leads us to believe that the emails are not making it to the mail server. All firewalls and virus protection software have been checked and are not configured to stop emails with attachments from being sent.The following code from the sproc in question is working with no issues whatsoever with the exact set up described above except it is hosted on SQL Server 2005 (please note that the value for @RecipientList is passed as a parameter to the sproc & that we've added values for @body & @body_format to ensure there is something in the body of the email): DECLARE @SQL nvarchar(MAX) DECLARE @File varchar(1000) DECLARE @SubjectLine varchar(200) SET @File = 'Dupes.txt' SET @SubjectLine = 'Active devices with duplicate MfgSerialNumbers' SET @SQL = 'SET NOCOUNT ON; DECLARE @Dupes TABLE ( MfgSerialNumber varchar(100) ) INSERT INTO @Dupes SELECT MfgSerialNumber FROM Devices WHERE Active = 1 AND MfgSerialNumber <> ''NO SERIAL NUMBER FOUND'' GROUP BY MfgSerialNumber HAVING ( COUNT(MfgSerialNumber) > 1 ) ORDER BY MfgSerialNumber; DECLARE @Data TABLE ( Customer varchar(100) ,Campus varchar(60) ,Bldg varchar(75) ,Floor varchar(15) ,CostCenter varchar(50) ,Dept varchar(75) ,Area varchar(100) ,Location varchar(100) ,AuxBarcode varchar(10) ,MfgSerialNumber varchar(50) ,DeviceID int ,Active varchar(10) ,Mfg varchar(30) ,Model varchar(60) ) INSERT INTO @Data SELECT TOP (100) PERCENT dbo.Customers.Name AS Customers, dbo.Campuses.Name AS Campus, dbo.Buildings.Name AS Building, dbo.Floors.Floor, dbo.Departments.DepartmentNumber, dbo.Departments.DepartmentName, CASE WHEN (CHARINDEX(''\'', dbo.Locations.LocationDescription)) = 0 THEN '''' ELSE LTRIM(RTRIM(LEFT(dbo.Locations.LocationDescription, (CHARINDEX(''\'', dbo.Locations.LocationDescription) - 1)))) END AS Area, CASE WHEN (CHARINDEX(''\'', dbo.Locations.LocationDescription)) = 0 THEN dbo.Locations.LocationDescription ELSE LTRIM(RIGHT(dbo.Locations.LocationDescription, (LEN(dbo.Locations.LocationDescription) - (CHARINDEX(''\'', dbo.Locations.LocationDescription))))) END AS Location, dbo.Devices.AuxBarcode, dbo.Devices.MfgSerialNumber, dbo.Devices.DeviceID, CASE WHEN Devices.Active = 0 THEN ''FALSE'' ELSE ''TRUE'' END AS Active, dbo.DeviceMfgs.MfgName, dbo.DeviceModels.ModelName FROM dbo.Customers INNER JOIN dbo.Campuses ON dbo.Customers.CustomerID = dbo.Campuses.CustomerID INNER JOIN dbo.CampusBusinessUnits ON dbo.Campuses.CampusID = dbo.CampusBusinessUnits.CampusID INNER JOIN dbo.BusinessUnits ON dbo.CampusBusinessUnits.BusinessUnitID = dbo.BusinessUnits.BusinessUnitID INNER JOIN dbo.Buildings ON dbo.Campuses.CampusID = dbo.Buildings.CampusID INNER JOIN dbo.Floors ON dbo.Buildings.BuildingID = dbo.Floors.BuildingID INNER JOIN dbo.Departments ON dbo.Campuses.CampusID = dbo.Departments.CampusID AND dbo.CampusBusinessUnits.CampusBusinessUnitID = dbo.Departments.CampusBusinessUnitID INNER JOIN dbo.SubDepartments ON dbo.Departments.DepartmentID = dbo.SubDepartments.DepartmentID INNER JOIN dbo.Devices ON dbo.SubDepartments.SubDepartmentID = dbo.Devices.SubDepartmentID INNER JOIN dbo.Locations ON dbo.Devices.LocationID = dbo.Locations.LocationID AND dbo.Floors.FloorID = dbo.Locations.FloorID INNER JOIN dbo.DeviceMfgs ON dbo.Devices.DeviceMfgID = dbo.DeviceMfgs.DeviceMfgID INNER JOIN dbo.DeviceModels ON dbo.Devices.DeviceModelID = dbo.DeviceModels.DeviceModelID INNER JOIN dbo.DeviceTypes ON dbo.DeviceModels.DeviceTypeID = dbo.DeviceTypes.DeviceTypeID INNER JOIN dbo.PrintTechnology ON dbo.DeviceModels.PrintTechnologyID = dbo.PrintTechnology.PrintTechnologyID LEFT OUTER JOIN dbo.NetworkInterfaces ON dbo.Devices.DeviceID = dbo.NetworkInterfaces.DeviceID WHERE (dbo.Customers.Active = 1) AND (dbo.Customers.CustomerID <> 81) AND (dbo.Campuses.Active = 1) AND (dbo.Devices.Active = 1) AND (dbo.Devices.MfgSerialNumber IN (SELECT MfgSerialNumber From @Dupes)) ORDER BY dbo.Customers.Name, dbo.Devices.MfgSerialNumber; select * From @Data;' EXEC msdb.dbo.sp_send_dbmail @recipients = @RecipientList, @query_result_separator = ' ' , @subject = @SubjectLine, @body = 'end of message', @body_format = 'text', @profile_name ='appropriate profile name that works with other emails', @query = @SQL, @attach_query_result_as_file = 1, @query_attachment_filename = @File, @query_result_header = 0, @query_result_no_padding = 1, @execute_query_database='appropriate db name'I've gone through a good number of the posts regarding Database Mail on this site but did not come across anything like this problem. I apologize if a solution has been posted previously.Thank you in advance.

SQL Linked Server to MySQL Problem

Posted: 21 Jun 2013 01:19 AM PDT

I successfully downloaded the MySQL drivers and installed the. There are 2 ... ANSI and Unicode. Both are successful when I do the test connection to MySQL and I can select the MySQL database that I want to use. Is the ANSI driver the correct one ?I am having trouble getting the linked server set up. I have found various instructions on line, but I must be doing something wrong, because I cannot establish a connection to MySQLAny good "MySQL Linked Server for Dummies" recommended articles ??EDIT: Running SQL 2005 64 bit, so I should have posted in SQL 2005 forum.

Tuning Parallelism

Posted: 20 Jun 2013 06:34 PM PDT

Hi AllI've been trying to tune Cost Threshold for Parallelism on a test systemUsing the query below, taken from [url]http://www.sqlskills.com/blogs/jonathan/tuning-cost-threshold-for-parallelism-from-the-plan-cache/[/url]. I have isolated a query that has a subtree cost of 93. I changed my Cost Threshold for Parallelism to 95 but the query still uses parallelism.Am I missing something here?Query:[code="sql"]SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') SELECT query_plan AS CompleteQueryPlan, n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS StatementText, n.value('(@StatementOptmLevel)[1]', 'VARCHAR(25)') AS StatementOptimizationLevel, n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') AS StatementSubTreeCost, n.query('.') AS ParallelSubTreeXML, ecp.usecounts, ecp.size_in_bytes FROM sys.dm_exec_cached_plans AS ecp ROSS APPLY sys.dm_exec_query_plan(plan_handle) AS eqp CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS qn(n) WHERE n.query('.').exist('//RelOp[@PhysicalOp="Parallelism"]') = 1 [/code]Thanks

Can't access database deferred transactions

Posted: 20 Jun 2013 08:57 AM PDT

Hi, we have a big problem.Today we change a table structure on db.Meanwhile sql was working the disk where our db is fail.After fix that we restart the server and our database start recover.this are the logs.[code="plain"] 2013-06-20 18:08:26.94 spid16s Recovery of database 'sistema' (7) is 96% complete (approximately 67 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.2013-06-20 18:08:31.70 spid16s Recovery of database 'sistema' (7) is 97% complete (approximately 62 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.2013-06-20 18:08:51.70 spid16s Recovery of database 'sistema' (7) is 97% complete (approximately 42 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.2013-06-20 18:08:53.61 spid16s Recovery of database 'sistema' (7) is 98% complete (approximately 40 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.2013-06-20 18:09:11.89 spid16s Recovery of database 'sistema' (7) is 99% complete (approximately 20 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.[/code]after db recover we cant access to the table or config (if we try to access the config management studio says the are blockings)if I run a query to sys.databases I see that the database is ONLINE.if I try to run DBCC checkdb, said the is deferred transactions.SQL server VersionMicrosoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64) Sep 21 2011 22:45:45 Copyright (c) 1988-2008 Microsoft Corporation [b]Enterprise [/b]Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) I dont know what i have to do now, I dont know if when server start try to rollback the deferred trans.Thanks for any help.greetings from argentina

Linked Server Help

Posted: 20 Jun 2013 09:55 AM PDT

Hi I am unable to query the database from my server remotely. Could you please refer the below pic and tell me the necessary actions. Here I am using the linked server to query the database from my local server(ABC12345) to other server (25.24.147.25), please let me know if you need additional information.On the other server(25.24.147.25) i do have only read permissions.Msg 18456, Level 14, State 1, Line 1Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.ThanksAswin
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog