On-Start SQL

 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

Passing Parameters in URL to view a SSRS Report

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:

• http://www.crystalreportsbook.com/SSRSandCR_ParametersAndFormulas.asp
• http://msdn.microsoft.com/en-us/library/ms170246.aspx
• http://www.mssqltips.com/tip.asp?tip=1336
• http://dobrzanski.net/2008/08/11/reporting-services-problem-with-passing-parameters-directly-in-the-url/
• http://technet.microsoft.com/en-us/library/bb522754.aspx

 

 

SQL Server 2000 Keyboard HotKeys

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…

Listen to Apple’s Opinion about Vista…   

Check this out :

Sql 2005

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