On-Start SQL

August 17, 2009

 On-Start SQL Server 2005

  • Auto starting a user stored procedure at SQL Server startup: sp_procoption: Sets stored procedure for autoexecution.
  • A stored procedure that is set to autoexecution runs every time an instance of SQL Server is started

sp_procoption ‘sp_QActivityLockedObjects’,’startup’,’true’

sp_procoption ‘ usp_OnSQLStart’,’startup’,’true’

  • To disable the SP from the SQL Server Startup

 sp_procoption ‘sp_QActivityLockedObjects’,’startup’,’false’

  •  To Check the SPs that run when SQL Server Starts, run the following query

 select name from master.dbo.sysobjects where type = ‘P’ and objectproperty(id, ‘ExecIsStartup’) = 1

Advertisements

I was having issues with passing parameters to SSRS and here is what I learnt.

I hope this helps some one else and saves some time.

Important thing to note here is SSRS has two Report Application Pools

and two report virtual sites (Check Under Default website in Server you will find Reports and ReportServer sites)

 

 -Reports is the portal (Report Manager).
-ReportServer is the asp.net application that is Reporting Services.

 

Reports

When we use Reports the report comes up in SSRS page but no parameters are passed in.

 

e.g. http://Server/Reports/Pages/Report.aspx?ItemPath=%2fReports%2fPrint+Full+USER+Report

 

Reports Server
Next, when you do this with the Report Server, the report opens up. Note: Parameter is case sensitive to the way it is in the report

 

e.g. http://Server/ReportServer/?%2fReports%2fPrint+Full+ USR+Report
&rs:Command=Render&UsrID=12345&rc:Parameters=false&rc:Toolbar=false

 Example 1
Report : ErrorStatistics  
Report Parameters: DateFrom, DateTo, SubmissionID, InsCode
 
Case 1 : No Parameters passed : User needs to enter
http://Server/ReportServer/?%2fReports%2fErrorStatistics
Note: In Server we have ErrorStatistics Report in the Reports folder which is denoted by 
%2fReports%2f
 
Case 2 : With parameters Passed via URL aspx direct all
http://Server/ReportServer/?%2fReports%2fErrorStatistics&rs:Command=Render&
DateFrom=08/20/2008&DateTo=09/20/2008&SubmissionID=&InsurerCode=
 
Case 3 : With parameters Hidden
http://Server/ReportServer/?%2fReports%2fErrorStatistics &rs:Command=Render&DateFrom=08/20/2008
&DateTo=09/20/2008&SubmissionID=&InsurerCode=&rc:Parameters=false
 
Case 4 : With parameters Hidden and No tool bar
http://Server/ReportServer/?%2fReports%2fErrorStatistics&rs:Command=Render&DateFrom=08/20/2008
&DateTo=09/20/2008&SubmissionID=&InsurerCode=
&rc:Parameters=false&rc:Toolbar=false
 
Case 5 : Direct Open Report in PDF
http://Server/ReportServer/?%2fReports%2fErrorStatistics&rs:Command=Render&DateFrom=08/20/2008&
DateTo=09/20/2008&SubmissionID=&InsurerCode=
&rc:Parameters=false&rc:Toolbar=false&rs:Format=PDF 
 
Case 6 : Direct Open Report in Excel
http://Server/ReportServer/?%2fReports%2fErrorStatistics&rs:Command=Render&DateFrom=
08/20/2008&DateTo=09/20/2008&SubmissionID=
&InsurerCode=&rc:Parameters=false&rc:Toolbar=false&rs:Format=Excel
 
Example 2
Report : Print Full USER Details
Report Parameters: UserID
http://Server/ReportServer/?%2fReports%2fPrint+Full+USR+Report&rs:Command=Render
&UsrID=12345&rc:Parameters=false&rc:Toolbar=false
 
 
References:
 
 

SQL Server 2000 – Query Analyzer Keyboard Short cuts 

CTRL+SHIFT+C   — Comment out code.
CTRL+SHIFT+R   — Remove comments (UNCOMMENT)
CTRL-SHIFT-F2   — Clear all bookmarks.
CTRL+F2                 — Insert or remove a bookmark (toggle).
F2                     — Move to next bookmark.
SHIFT+F2               — Move to previous bookmark.
ALT+BREAK   — Cancel a query.
CTRL+O                  — Connect.
CTRL+F4                — Disconnect.
CTRL+F4                 — Disconnect and close child window.
ALT+F1                 — Database object information.
CTRL+SHIFT+DEL   — Clear the active Editor pane.
CTRL+C or Ctrl+Insert  — Copy
CTRL+X or Shift+Del  — Cut
SHIFT+TAB   — Decrease indent.
CTRL+DEL   — Delete through the end of a line in the Editor pane.
CTRL+F    — Find.
CTRL+G    — Go to a line number.
TAB    — Increase indent.
CTRL+SHIFT+L   — Make selection lowercase.
CTRL+SHIFT+U   — Make selection uppercase.
CTRL+V or Shift+Insert  — Paste.
F3    — Repeat last search or find next.
CTRL+H    — Replace.
CTRL+A    — Select all.
CTRL+Z    — Undo.
F5 or Ctrl + E   — Execute a query.
F1    — Help for Query Analyzer.
SHIFT+F1   — Help for the selected Transact-SQL statement.
F6    — Switch between query and result panes.
Shift+F6   — Switch panes.
CTRL+W    — Window Selector.
CTRL+N    — New Query window.
F8    — Object Browser (show/hide).
F4    — Object Search.
CTRL+F5    — Parse the query and check syntax.
CTRL+P    — Print
CTRL+D    — Display results in grid format.
CTRL+T    — Display results in text format.
CTRL+B    — Move the splitter.
CTRL+SHIFT+F   — Save results to file.
CTRL+R    — Show Results pane (toggle).
CTRL+S    — Save
CTRL+SHIFT+INSERT  — Insert a template.
CTRL+SHIFT+M   — Replace template parameters.
CTRL+L    — Display estimated execution plan.
CTRL+K    — Display execution plan (toggle ON/OFF).
CTRL+I    — Index Tuning Wizard.
CTRL+SHIFT+S   — Show client statistics
CTRL+SHIFT+T   — Show server trace.
CTRL+U    — Use database
ALT + x    — Executes the code
Alt + O    — Switches to the Object browser pane after pressing F8 to show it.

Vista…

February 10, 2007

Listen to Apple’s Opinion about Vista…   

Check this out :

Sql 2005

February 9, 2007

I have put together a list of  few admin tips for  Sql Server 2005  New Admins/User. You can download it from here : Sql Server 2005