SQL Server | Stephen Shakespeare

SSRS Subscriptions – Email – Windows File Share

So easy does not need much detail here.
My publish on a schedule to either a file share or by email.
To use Email settings need to set up in SSRS configuration of server.

PROBLEM
Subscriptions cannot be created because the credentials used to run the report are not stored, or if a linked report, the link is no longer valid.

SOLUTION
The report credentails were Windows Authentication. For my scenario it was feasible to save low read-only credentials with the report.

Filed under SQL SSRS, SQL SSRS Configuration

SQL Server Report Manager takes a long time to start up

04-Dec-1111 Leave a comment

See post below
May need to go to Programs > turn windows features on or off for IIS Manager

http://www.developmentnow.com/g/115_2005_11_0_0_641257/Report-Manager-Start-Up.htm

I believe the setting you are looking for is the “Shutdown worker processes
after being idle for…” on the Performance tab of the application pool that
the Reports virtual directory is assigned to.
See old post below for details:
[quoted text, click to view]

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

If you are running Windows 2003 server for your IIS reportserver, then this
is a simple issue – I’ll explain what happens:
The report service engine, once it is idle for more than the default 20
minutes, the worker process is shutdown.
This is controlled by IIS.
Open up the Internet Information Services (IIS) Manager
Expand the server node then the application pools.
On my IIS machine, I created an application pool dedicated to the
reportserver & reportmanager virtual webs.
But anyways, for the application pool that the reportserver is pointing to
if you left everything to their defaults will be the DefaultAppPool.
Right click the default app pool and select properties.
There are two things that are checked by default – On the recycling tab
there is a checkbox for recycling worker processes – it is currently set to
1740 minutes (29 hours). Leave it.
The other one is on the performance tab – which is the one you are
interested in changing….
See the “Idle Timeout” section and increase the number of minutes to be 8
hours a typical working day – 8*60 = 480 minutes.
Next, to be sure the “morning person” that runs the first report doesn’t get
the delay, set up a schedule for either a dummy or adhoc report to fire off
like at 6am so that the report component worker processes get loaded.

Filed under SQL SSRS, SQL SSRS Configuration, Troubleshooting

SQL Server Operators – SQL Agent Alert System – Notify Operators

24-Nov-1111 Leave a comment

After adding SQL Operators you need to restart SQL Agent

ERROR in SQL Agent Jobs
… NOTE: Failed to notify ‘<operator name>’ via email.

[476] Database Mail is not enabled for agent notifications. Cannot send e-mail to
[475] Database Mail is not enabled for agent notifications.

May have log in SSMS > SQL Server Agent > Error Logs

SOLVED
Credit to:
http://sqlblog.jfperry.com/jpsql/post/2009/04/07/NOTE-Failed-to-notify-7boperator7d-via-email.aspx

This was set up on one of my work servers. For a different company this needed to be set up.

On SQL 2005+ you have to tell SQL Server Agent / Properties / Alert System, which profile that you’d like to enable. Here’s what it does.
e.g. and don’t forget to restart SQL Agent

To set up SQL Server Agent Mail to use Database Mail

In Object Explorer, expand a server.
Right-click SQL Server Agent, and then click Properties.
Click Alert System.
Select Enable Mail Profile.
In the Mail system list, choose Database Mail.
In the Mail profile list, select a mail profile for Database Mail.

Or less likely Transact SQL

USE [msdb] GO EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', N'MyDatabaseProfile' GO

Filed under Errors, SQL Configuration, SQL Server Tagged with SQL Agent, SQL Database Mail

SQL Server Allow Remote Connections – Including Blocked by McAffee

16-Nov-1111 Leave a comment

A lot of websites have the basics of this. Applied steps and still did not work. Steps were

Allow remote connections
Start SQL Browser
Ensure that Protocol for TCP/IP was enabled
For new PC only adjustment to Firewall was addition of inbound port rule. Had to include Private network.

SOLVED However removed McAffee from the server (laptop) and remote connection worked immediately. Think this was the deciding ultimate solving step. Other Links http://blogs.msdn.com/b/walzenbach/archive/2010/04/14/how-to-enable-remote-connections-in-sql-server-2008.aspx http://support.microsoft.com/kb/914277 http://www.dummies.com/how-to/content/how-to-open-a-port-in-the-windows-7-firewall.html http://www.sevenforums.com/system-security/58817-remote-access-sql-server-express-2008-windows-7-a.html

For remote connection over the internet also need to use port forwarding on the router.

Filed under SQL Configuration, SQL Server Tagged with Solved

Transact SQL for disable and enable Trigger

11-Nov-1111 Leave a comment

DISABLE TRIGGER Product.updateProducts ON Product.Products;

UPDATE Product.Products…

ENABLE TRIGGER updateProducts ON Product.Products;

Filed under Transact SQL Tagged with SQL Triggers

Policy Management in SQL Server

30-Oct-1111 Leave a comment

After reading help this is quite easy

Filed under SQL Server

SQL Agent job calling stored procedure msdb.dbo.sp_send_dbmail @query does not return any records

29-Sep-1111 Leave a comment

ERROR:
SQL Agent job calling stored procedure msdb.dbo.sp_send_dbmail @query does not return any records

SOLUTION
Add the Database Name into the @query

BACKGROUND
This may be because of the default database of the account which executes the SQL Agent job.

EXAMPLE
If @query does not include the database name (three-part naming), it is possible that although the EXISTS may fire, the @query does not.
To solve add the Database Name into the @query.

BEGIN TRY

IF EXISTS(SELECT * FROM Production.vAuditSOCDBTable)
  BEGIN
   DECLARE @MyBody nvarchar(100)
   SET @MyBody = ‘<message>’ + char(13) + char(10)

   EXEC msdb.dbo.sp_send_dbmail
    @profile_name = ‘<>’,
    @recipients = ”,
    @blind_copy_recipients = ”,
    @query = ‘SELECT * FROM <databasename>..’,
    –or possibly @execute_query_database = ‘databasename’,
    @subject = ‘subject’,
    –@attach_query_result_as_file = 1 ,
    @body = @MyBody;

   PRINT ‘message’
  END
ELSE
  PRINT ‘nothing in audit’

END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() ErrorNumber, ERROR_MESSAGE() [Message]
PRINT ‘FAILED’
END CATCH

Filed under Errors, SQL Server Tagged with Solved, SQL Agent, SQL Database Mail

SQL Server Table Fragmentation

22-Sep-1111 Leave a comment

USE AdventureWorks;
GO
DBCC SHOWCONTIG (‘HumanResources.Employee’)
GO

DBCC SHOWCONTIG scanning ‘Employee’ table…
Table: ‘Employee’ (869578136); index ID: 1, database ID: 6
TABLE level scan performed.
– Pages Scanned…………………………..: 7
– Extents Scanned…………………………: 3
– Extent Switches…………………………: 2
– Avg. Pages per Extent……………………: 2.3
– Scan Density [Best Count:Actual Count]…….: 33.33% [1:3]
– Logical Scan Fragmentation ………………: 14.29%
– Extent Scan Fragmentation ……………….: 33.33%
– Avg. Bytes Free per Page…………………: 172.6
– Avg. Page Density (full)…………………: 97.87%

sys.dm_db_index_physical_stats DMV example:

USE AdventureWorks
GO
SELECT object_id, index_id, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats(DB_ID(‘AdventureWorks’), OBJECT_ID(‘HumanResources.Employee’), NULL, NULL, NULL);

http://msdn.microsoft.com/en-us/library/ms189858.aspx

As a guideline from above

avg_fragmentation_in_percent value	Corrective statement
> 5% and < = 30%	ALTER INDEX REORGANIZE
> 30%	ALTER INDEX REBUILD WITH (ONLINE = ON)*

Filed under SQL Server

SSMS 2008 R2 is losing Intellisense after installing Visual Studio 2010 SP1

09-Sep-1111 Leave a comment

http://connect.microsoft.com/SQLServer/feedback/details/650569/ssms-2008-r2-is-losing-intellisense-after-installing-visual-studio-2010-sp1

Apply cumulative update to SQL Server 2008 R2

http://support.microsoft.com/kb/981356

Filed under SQL Server, Troubleshooting, Visual Studio

SQL Server PowerPivot

09-Sep-1111 Leave a comment

Installation seemed easy enough – If Excel 2010 32-bit edition as you should, then need 32-bit x86 version of PowerPivot

Import

If a table has a timestamp (binary) data then exclude that otherwise you get an error

Group by date

So far this has been disabled for me

How do I … Import more tables using an Existing Connection

At first glance “Home” tab, section “Get External Data” is the only place to get new tables from. However it is not possible to use an existing connection from here, so I landed up with several connections 1,2,3 to the same SQL Server. To add a new import to an existing connection:

Design tab > Existing Connections > Open or Double click the connection you want to get a new import from

How do I … Refresh after a Schema Change

Design Tab > Table Properties
Use Switch to “Table Preview” or “Query Editor” to validate

Insert Pivot Chart

This puts the Data for Sheet 1 Chart 1 on a different tab.
To move it to the same tab as the chart use “PivotTable Tools” > Move PivotTable

Trouble Shoot every sum as same number – no apparent grouping

I had WO -> Reject Card on WO -> Reject Detail on WO
I found this created no proper grouping, so sum was same number for every group. Changed to:
WO – > Reject Card
and
WO -> Reject Detail
Problem solved

Sort by Date workaround

Sorting by date does not work, to workaround this create a Computed/Calculated column and use this:
= Year([WorkOrder!CompletionDate) * 100 + Month([WorkOrder]![CompletionDate]
YearMonth = Date(Year([WorkOrder!CompletionDate), Month([WorkOrder]![CompletionDate], 1)
YearMonthSort = Format(YearMonth, “yyyy-mm”)

Error 1

“Could not add the field “<field>” to the PivotTable. Please ensure the field exists and is calculated, and try refreshing the PivotTable.”

Solution 1

Close the file and then open it again

Possible improvements for Microsoft

If an import fails you should be able to go back to the previous step, rather than having to start again
On Diagram View good to have zoom, but why is minimum 100 %
The table import wizard should be resizable
Make sort by date work

Filed under SQL PowerPivot

← Older posts

Newer posts →

Stephen Shakespeare