Delete files over 24 hours old via SSIS

March 27, 2014

The following should be pasted into a for each file enumerator, the file name is passed to the script

Dim fi As System.IO.FileSystemInfo

fi = My.Computer.FileSystem.GetFileInfo(Dts.Variables(“file_name”).Value.ToString)

If (DateDiff(“h”, Now.Date, fi.CreationTime)) >= 24 Then
fi.Delete()
End If

Drop all temp tables

March 27, 2014

Rather than list DROP TABLE1,2,3….n.

 

 

DECLARE @d_sql NVARCHAR(MAX)
SET @d_sql = ”

SELECT @d_sql = @d_sql + ‘DROP TABLE ‘ + QUOTENAME(name) + ‘;

FROM tempdb..sysobjects
WHERE name like ‘#[^#]%’
AND OBJECT_ID(‘tempdb..’+QUOTENAME(name)) IS NOT NULL

IF @d_sql <> ”
BEGIN
PRINT @d_sql
EXEC( @d_sql )
END

UK Heat Map using SSRS

March 20, 2014

download a shape file, there is one available here https://www.sharegeo.ac.uk/handle/10672/51?show=full

extract all contents to a directly, not just the .shp file

create a dataset which contains postcodes and summarized data, simple example:

SELECT ‘WV’ AS post_code, 1 AS count
UNION
SELECT ‘CF’ ,5

Drag a map report item to your new report
select esri shapefile and browse to your downloaded file
Set the map resolution to smallest size (i find that the report will fail if I use best quality)
set the position and zoom of the map
select color analytical map

tick the box next to label, select postcode from the dropdown box

change field to visualize to sum(count)

shape file:shape file

Interrogate AD group membership

March 13, 2014

To find which Groups a user is a member of

exec xp_logininfo‘DOMAIN\jlester’,‘all’
To list all members of a group
exec xp_logininfo‘DOMAIN\GROUP’,‘members’

Mapping Report Subscriptions to SQL JOB Names

February 19, 2014

SELECT ‘EXEC ReportServer.dbo.AddEvent @EventType=”TimedSubscription”,
@EventData=”’
+ CAST(a.SubscriptionID AS VARCHAR(40)) + ”” AS ReportCommand ,
b.name AS JobName ,
a.SubscriptionID ,
e.name ,
e.path ,
d.description ,
laststatus ,
eventtype ,
LastRunTime ,
date_created ,
date_modified
FROM ReportServer.dbo.ReportSchedule a
JOIN msdb.dbo.sysjobs b ON CAST(a.ScheduleID AS VARCHAR(1000)) = b.name
JOIN ReportServer.dbo.ReportSchedule c ON b.name = c.ScheduleID
JOIN ReportServer.dbo.Subscriptions d ON c.SubscriptionID = d.SubscriptionID
JOIN ReportServer.dbo.Catalog e ON CAST(d.report_oid AS VARCHAR (MAX)) = CAST(e.itemid AS VARCHAR(MAX))

check if user is a member of AD Group

January 20, 2014

EXECUTE AS USER=‘domain\user’

GO

SELECTIS_MEMBER(‘domain\group’),SUSER_NAME()

GO

REVERT

View all running jobs

January 7, 2014

SELECT
job.name,
job.job_id,
job.originating_server,
activity.run_requested_date,
DATEDIFF( SECOND, activity.run_requested_date, GETDATE() ) as Elapsed
FROM
msdb.dbo.sysjobs_view job
JOIN
msdb.dbo.sysjobactivity activity
ON
job.job_id = activity.job_id
JOIN
msdb.dbo.syssessions sess
ON
sess.session_id = activity.session_id
JOIN
(
SELECT
MAX( agent_start_date ) AS max_agent_start_date
FROM
msdb.dbo.syssessions
) sess_max
ON
sess.agent_start_date = sess_max.max_agent_start_date
WHERE
run_requested_date IS NOT NULL AND stop_execution_date IS NULL

enable users to download files from server

December 20, 2013

Open up IIS

expand the server node
expand sites
right click default web site
add virtual directory
give it a name and file path

turn on directory browsing
turn on basic authentication

Access to the remote server is denied because no login-mapping exists

December 18, 2013

Access to the remote server is denied because no login-mapping exists

Script the linked server and check the @provstr parameter. It must specify a user ID. Even if the userid and password is specified elsewhere, the user id must also be in @provstr.

Example

Change this: @provstr=N’PROVIDER=SQLOLEDB;SERVER=xxxxxx’

into

This: @provstr=N’PROVIDER=SQLOLEDB;SERVER=xxxxxx;User ID=yyyyyyyy’

no need to specify the password here, that comes in the @rmtpassword parameter which you will have elsewhere in your script

running stored procedures/returning data to excel

December 5, 2013

CREATE STORED PROC WITH PARAMETER
Use test
GO

CREATE PROC dbo.excel

@input varchar(100)
AS

select GETDATE() AS field1

GO
SET UP CONNECTION IN EXCEL

1. open excel
2. data ribbon
3. from other sources
4. from microsoft query
5. new data source
6. name your data source
7. sql server driver
8. connect
9. server name
10. untick ‘use trusted connection’ or leave it and skip next step
11. enter sql server username and password
12. pick any table from the list provided
13. tick save user id and password
14. ok
15. ok
16. select any 1 column for your query
17. next, next, next, finish
18. You should now be on the import data diaglogue box
19. properties
20. definition tab
21. change your command text to exec test.dbo.excel ?
22. ok, ok, enter password,options, select database, ok

 


Follow

Get every new post delivered to your Inbox.