Feb 11, 2011

Script To know Jobs Scheduled

-- to know what jobs were schedule for the next 24 hours
CREATE PROC dbo.GetJobsScheduledInTheNextNHours (@HowManyHoursAhead INT = 24)
AS
WITH OurJobs AS (
    SELECT  job.job_id,  job.[name]
      , CASE job.[description] WHEN 'No description available.' THEN NULL ELSE job.description END AS Description
      , job.date_modified
      , CASE sched.next_run_date
            WHEN 0 THEN 'Never'
            ELSE
              CONVERT(varchar(10), CONVERT(smalldatetime, CAST(sched.next_run_date as varchar), 120), 120)+' '+
              RIGHT('0'+CAST((sched.next_run_time/10000) AS VARCHAR), 2)+':'+
              RIGHT('0'+CAST((sched.next_run_time-((sched.next_run_time/10000)*10000))/100 AS VARCHAR), 2)+':'+
              RIGHT('0'+CAST((sched.next_run_time-((sched.next_run_time/10000)*10000)-((sched.next_run_time-((sched.next_run_time/10000)*10000))/100*100)) AS VARCHAR), 2)
      END AS NextRunDateTime
      , (
        SELECT CASE last_run_date
            WHEN 0 THEN 'Never'
            ELSE
              CONVERT(varchar(10), CONVERT(smalldatetime, CAST(last_run_date as varchar), 120), 120)+' '+
              RIGHT('0'+CAST((last_run_time/10000) AS VARCHAR), 2)+':'+
              RIGHT('0'+CAST((last_run_time-((last_run_time/10000)*10000))/100 AS VARCHAR), 2)+':'+
              RIGHT('0'+CAST((last_run_time-((last_run_time/10000)*10000)-((last_run_time-((last_run_time/10000)*10000))/100*100)) AS VARCHAR), 2)
          END AS LastRunDateTime
        FROM msdb.dbo.sysjobsteps
        WHERE job_id = job.job_id AND step_id = (
          SELECT MAX(step_id)
          FROM msdb.dbo.sysjobsteps
          WHERE job_id = job.job_id
        )
      ) as LastSuccessfulExecution
    FROM msdb.dbo.sysjobs job JOIN msdb.dbo.sysjobschedules sched
        ON sched.job_id = job.job_id
    WHERE job.enabled = 1 -- remove this if you wish to return all jobs
        AND sched.next_run_date > 0
)
SELECT * FROM OurJobs
WHERE DATEDIFF(hh, GETDATE(), NextRunDateTime) <= @HowManyHoursAhead
GO
EXEC GetJobsScheduledInTheNextNHours 24

Feb 9, 2011

Import SQL Server Error Log into Table

CREATE PROC sp_import_errorlog
(
 @log_name sysname,
 @log_number int = 0,
 @overwrite bit = 0
)
AS
/*************************************************************************************************
Purpose: To import the SQL Server error log into a table, so that it can be queried
Written by: Anand Mahendra

Tested on:  SQL Server 2000
Limitation:  With error messages spanning more than one line only the first line is included in the table
Email:   anandbox@sify.com
Example 1:  To import the current error log to table myerrorlog
  EXEC sp_import_errorlog 'myerrorlog'
Example 2:  To import the current error log to table myerrorlog, and overwrite the table
  'myerrorlog' if it already exists
  EXEC sp_import_errorlog 'myerrorlog', @overwrite = 1
Example 3:  To import the previous error log to table myerrorlog
  EXEC sp_import_errorlog 'myerrorlog', 1
Example 4:  To import the second previous error log to table myerrorlog
  EXEC sp_import_errorlog 'myerrorlog', 2
*************************************************************************************************/
BEGIN
 SET NOCOUNT ON

Test SQL Connectivity


on error resume next
'Author: MAK
'Contact: mak_999@yahoo.com
'Date March 30, 2005
'Check Connectivity
'Usage: testconnectivity.vbs Servername dbname Loginname Password
Set objArgs = WScript.Arguments
Server= objArgs(0)
database= objArgs(1)
uid=objArgs(2)
pwd=objArgs(3)

Set AdCn = CreateObject("ADODB.Connection")
Set AdRec = CreateObject("ADODB.Recordset")

connstr="Provider=SQLOLEDB.1;Data Source=" & server & ";Initial Catalog=" & database & ";user id = '" & uid & "';password='" & pwd & "'"
'msgbox connstr
'AdCn.Open = "Provider=SQLOLEDB.1;Data Source=sssss;Initial Catalog=weeklyreboot;user id = 'rebootuser';password='reboot123' "
AdCn.Open = connstr
SQL="Select @@version as name"
AdRec.Open SQL, AdCn,1,1
xxx=Adrec("name")
if err.number <> 0 or left(xxx,5)<>"Micro" then
msgbox "Can't connect"
else
msgbox "connected"
end if

Creating Excel using T-SQL

PRINT 'Begin CreateXLS script at '+RTRIM(CONVERT(varchar(24),GETDATE(),121))+' '
PRINT ''
GO
SET NOCOUNT ON
DECLARE @Conn int -- ADO Connection object to create XLS
      , @hr int -- OLE return value
      , @src varchar(255) -- OLE Error Source
      , @desc varchar(255) -- OLE Error Description
      , @Path varchar(255) -- Drive or UNC path for XLS
      , @Connect varchar(255) -- OLE DB Connection string for Jet 4 Excel ISAM
      , @WKS_Created bit -- Whether the XLS Worksheet exists
      , @WKS_Name varchar(128) -- Name of the XLS Worksheet (table)
      , @ServerName nvarchar(128) -- Linked Server name for XLS
      , @DDL varchar(8000) -- Jet4 DDL for the XLS WKS table creation
      , @SQL varchar(8000) -- INSERT INTO XLS T-SQL
      , @Recs int -- Number of records added to XLS
      , @Log bit -- Whether to log process detail

Joins

USE AdventureWorks
GO

CREATE TABLE table1
(ID INT, Value VARCHAR(10))
INSERT INTO Table1 (ID, Value)
SELECT 1,'First'
UNION ALL
SELECT 2,'Second'
UNION ALL
SELECT 3,'Third'
UNION ALL
SELECT 4,'Fourth'
UNION ALL
SELECT 5,'Fifth'
GO

Uploading XL Sheet data into SQL Server 2005 using C#

protected void btn_upload1_Click(object sender, EventArgs e)
    {
        try
        {
            string FilePath = System.IO.Path.GetFullPath(fileload1.PostedFile.FileName);
            string xConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + FilePath + ";" + "Extended Properties=Excel 8.0;";
            using (OleDbConnection connection = new OleDbConnection(xConnStr))
            {
                OleDbCommand command = new OleDbCommand("Select * FROM [Sheet1$]", connection);
                connection.Open();
                using (DbDataReader dr = command.ExecuteReader())
                {
                    string conStr = ConfigurationManager.AppSettings["conStr"];
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conStr))
                    {
                        bulkCopy.DestinationTableName = "Your_Table_Name";
                        bulkCopy.WriteToServer(dr);
                        lbl_Hardwaremsg.Visible = true;
                        lbl_Hardwaremsg.Text = "Hardware Details uploaded successfully";
                    }
                }
            }
      }

Books For SQL Server 2005

1.The Microsoft data warehouse toolkit: with SQL Server 2005
  Joy Mundy, Warren Thornthwaite, Ralph Kimball

2.Building a Data Warehouse: With Examples in SQL Server
  Vincent Rainardi

3.Sql Server 2005 Black Book
  Patrick Dalton, Paul Whitehead & Kogent Solutions

The Effect of NOLOCK on Performance

When this article was first published, it produced some lively debate. It was believed by some that the article misled readers into thinking that NOLOCK should be a tool for performance tuning, which it is not. There is a divided opinion on the appropriate use of NOLOCK, and therefore I am adding this foreword to my article in an effort to clarify matters.
NOLOCK is a query optimizer hint. It has advantages, and disadvantages, and it potentially breaks one of the fundamental rules of databases – data integrity through the use of a locking mechanism. In a nutshell, NOLOCK does not take locks on the data it reads. This provides benefits for concurrency and performance, but data integrity suffers.
I can't speak for Microsoft or the MVP's, but if Microsoft offers an ISOLATION LEVEL which does the same thing as NOLOCK, then there must be an appropriate place for the use of NOLOCK. We've discussed the advantages of NOLOCK, so let's take a look at some of the disadvantages.
Firstly, when reading with NOLOCK you are reading uncommitted data. This means the data has not been committed to the database, and could be rolled back (undone) after you have read it. So, you may find your application is processing invalid data. This is not so much of a problem with Name and Address data, but is particularity problematic with Accounts, Finance and Sales data. This is where your data integrity would suffer.
Secondly, as noted by one of the SQLServerCentral.com forum posters, Itzik Ben-Gan demonstrated to the UK SQL Server User Group that NOLOCK can return duplicate rows when reading data. To quote the forum posting: "Tony Rogerson's blog has code which demonstrates this. http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/16/1345.aspx ".
This article was intended as a clinical approach to the effect of NOLOCK on performance, without advocating you use NOLOCK in an attempt to speed up your queries. If you do decide to use it that way, I would like you to know the risks.

Count Records of Table

SELECT
    [TableName] = so.name,
    [RowCount] = MAX(si.rows)
FROM
    sysobjects so,
    sysindexes si
WHERE
    so.xtype = 'U'
    AND
    si.id = OBJECT_ID(so.name)
GROUP BY
    so.name
order by TableName

SQL Date Formats

SQL Date Formats

Date Format
Standard
SQL Statement
Sample Output
Mon DD YYYY 1
HH:MIAM (or PM)
Default
SELECT CONVERT(VARCHAR(20), GETDATE(), 100)
Jan 1 2005 1:29PM 1
MM/DD/YY
USA
SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY]
11/23/98
MM/DD/YYYY
USA
SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]
11/23/1998
YY.MM.DD
ANSI
SELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD]
72.01.01
YYYY.MM.DD
ANSI
SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD]
1972.01.01
DD/MM/YY
British/French
SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY]
19/02/72
DD/MM/YYYY
British/French
SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY]
19/02/1972
DD.MM.YY
German
SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY]
25.12.05
DD.MM.YYYY
German
SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY]
25.12.2005
DD-MM-YY
Italian
SELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS [DD-MM-YY]
24-01-98
DD-MM-YYYY
Italian
SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY]
24-01-1998
DD Mon YY 1
-
SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY]
04 Jul 06 1
DD Mon YYYY 1
-
SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY]
04 Jul 2006 1
Mon DD, YY 1
-
SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY]
Jan 24, 98 1
Mon DD, YYYY 1
-
SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY]
Jan 24, 1998 1
HH:MM:SS
-
SELECT CONVERT(VARCHAR(8), GETDATE(), 108)
03:24:53
Mon DD YYYY HH:MI:SS:MMMAM (or PM) 1
Default +
milliseconds
SELECT CONVERT(VARCHAR(26), GETDATE(), 109)
Apr 28 2006 12:32:29:253PM 1
MM-DD-YY
USA
SELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY]
01-01-06
MM-DD-YYYY
USA
SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY]
01-01-2006
YY/MM/DD
-
SELECT CONVERT(VARCHAR(8), GETDATE(), 11) AS [YY/MM/DD]
98/11/23
YYYY/MM/DD
-
SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD]
1998/11/23
YYMMDD
ISO
SELECT CONVERT(VARCHAR(6), GETDATE(), 12) AS [YYMMDD]
980124
YYYYMMDD
ISO
SELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD]
19980124
DD Mon YYYY HH:MM:SS:MMM(24h) 1
Europe default + milliseconds
SELECT CONVERT(VARCHAR(24), GETDATE(), 113)
28 Apr 2006 00:34:55:190 1
HH:MI:SS:MMM(24H)
-
SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS:MMM(24H)]
11:34:23:013
YYYY-MM-DD HH:MI:SS(24h)
ODBC Canonical
SELECT CONVERT(VARCHAR(19), GETDATE(), 120)
1972-01-01 13:42:24
YYYY-MM-DD HH:MI:SS.MMM(24h)
ODBC Canonical
(with milliseconds)
SELECT CONVERT(VARCHAR(23), GETDATE(), 121)
1972-02-19 06:35:24.489
YYYY-MM-DDTHH:MM:SS:MMM
ISO8601
SELECT CONVERT(VARCHAR(23), GETDATE(), 126)
1998-11-23T11:25:43:250
DD Mon YYYY HH:MI:SS:MMMAM 1
Kuwaiti
SELECT CONVERT(VARCHAR(26), GETDATE(), 130)
28 Apr 2006 12:39:32:429AM 1
DD/MM/YYYY HH:MI:SS:MMMAM
Kuwaiti
SELECT CONVERT(VARCHAR(25), GETDATE(), 131)
28/04/2006 12:39:32:429AM