Ms Sql Server Technology Discussions

[chelsea]

[quote name='mtkr' timestamp='1356652392' post='1303023719']
INT32
[/quote]

hmm scope package level isthe work avthadi kada
example

http://www.bidn.com/blogs/kylewalker/ssis/421/setting-package-variables-using-execute-sql-tasks

[deals2buy]

[quote name='buffaloboy' timestamp='1356646979' post='1303023290']
hello all...

mee real time experiences share chesukondi pls...i mean real time lo as a dba what we need to check on daily basis and how the routine work will be...prathi roju meeru chese tasks endi as a dba..pls cheppandi...dba ga job chestunna nenu...gatha 6 months nundi..pls help...
[/quote]
[quote name='buffaloboy' timestamp='1356647463' post='1303023338']

printing mistake..
dba ga job try chestunna
[/quote]

no idea mama...I haven't worked as a DBA ...ATB for ur job
[quote name='mtkr' timestamp='1356649792' post='1303023557']
mastaaruus... evaraina external sources(files like flat, excel, xml) files nundi oracle ki data load chesara through SSIS..

nen trying to load xml file into oracle db thr SSIS...


matter entante oka execute sql task and daani kinda oka DFT ....n nxt vere konni unnai...

execute sql task fail avutundhiiii....
n danni disable chesthe DFT lo anta baane nadustundhii..


aa execute sql task lo oka query undhii n andi oka condition satisfy chesukoni nxt step ki potundhiii....

Result set:: SINGLE ROW

Sql Stmt:::
select count(*) as found from tbl1 where column = ?

Parameter Mapping::: variable1

result set::: found--- varfound

ee execute sql task ki DFT ki madhyalo exprssion varfound > 0


so adi >0 avuthene nxt step ki pothadi.....

but adi fail avutundhii...
n error iz:::

An error occurred while assigning a value to variable "The type of the value being assigned to
variable differs from the current variable type. Variables may not change type during
execution. Variable types are strict, except for variables of type Object.
[/quote]

enduku SSIS ey use chestunnav? PL SQL lo bulk insert laantidi try cheyyochu ga

[deals2buy]

[b] Index Create Memory Setting for SQL Server[/b]


http://www.mssqltips.com/sqlservertip/2815/index-create-memory-setting-for-sql-server/


bagundi

[deals2buy]

[b] [url="http://www.sqlservercentral.com/blogs/sqlandme/2012/12/19/sql-server-skipping-function-execution-when-parameters-are-null/"]SQL Server – Skipping Function Execution when parameters are NULL[/url][/b]


You can avoid executing a function when NULL values are passed as parameters. To achieve this you need to create function with [b][url="http://msdn.microsoft.com/en-us/library/ms186755%28v=sql.90%29.aspx"]RETURNS NULL ON NULL INPUT[/url][/b], this option introduced in SQL Server 2005.

[b]You can use this while creating a function as below:[/b]

[CODE]
USE [SqlAndMe]
GO
CREATE FUNCTION dbo.searchString
(
@string NVARCHAR(MAX),
@keyword NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
DECLARE @retValue NVARCHAR(MAX)
SET @retValue = 'Input Valid…'
RETURN @retValue
END
GO
[/CODE]


Now, when this function is executed and any of the parameters are NULL, it will simple return NULL without executing the function body.
You can test this functionality using below statement:

[CODE]
USE [SqlAndMe]
GO
SELECT dbo.searchString(N'SqlAndMe', N'Test'),
SELECT dbo.searchString(N'SqlAndMe', N''),
SELECT dbo.searchString(N'SqlAndMe', NULL),
SELECT dbo.searchString(NULL, N'Test')
GO
[/CODE]


[b][u]Result Set:[/u][/b]
Input Valid…

(1 row(s) affected)


Input Valid…

(1 row(s) affected)


NULL

(1 row(s) affected)


NULL

(1 row(s) affected)

As you can see from the last two results a NULL is returned.

[vijay040]

Nice...

[deals2buy]

[b] Fix: Error: The conversion returned status value 2 and status text “The value could not be converted because of a potential loss of data.”. (SQL Server Import and Export Wizard)[/b]


[b] Scenario:[/b]

The user was trying to import data from Excel to tables in SQL Server Database. Every time he attempted to import the data he faced following error. He tried using SSIS package as well using the Import Export Wizard (which creates an SSIS package under the hood as well) but he he kept on facing following error. He could not figure out the reason behind the error. I have modified the error to make it readable.
[b] Error:[/b]

[img]http://www.pinaldave.com/bimg/ssiserror.jpg[/img]

- Executing (Error)
Messages
Error 0xc02020c5: Data Flow Task 1: Data conversion failed while converting column “Col1″ (18) to column “Col1″ (51). The conversion returned status value 2 and status text “The value could not be converted because of a potential loss of data.”.
(SQL Server Import and Export Wizard)

Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. [Error Detail]. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)

Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component “Data Conversion 0 – 0″ (39) failed with error code 0xC0209029 while processing input “Data Conversion Input” (40). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)
[b] Solution:[/b]

Well, there can be many reasons for this error to show up but the major reason is that data type mismatch. I have observed that following two are the major reason for the error.

1) Null’s are not properly handled either in the destination database or during SSIS package creation. It is quite possible that the source contains a null database but the destination is not accepting the NULL data leading to build generate above error.

2) This is the most common issue I have seen in the industry where data types between source and destination does not match. For example, source column has varchar data and destination column have an int data type. This can easily generate above error. There are certain kind of datatypes which will automatically convert to another data type and will not generate the error but there are for incompatible datatypes which will generate above error.

The best practices is to make sure that you match your data types and their properties of the source and destination. If the datatypes of the source and destination are same, there is very less chance of occurrence of the above error.

[deals2buy]

[b] Copy SQL Server Analysis Services Dimensions[/b]

http://www.mssqltips.com/sqlservertip/2829/copy-sql-server-analysis-services-dimensions/

[deals2buy]

Using SQL Server 2012, create this simple stored procedure.
USE AdventureWorks2012

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[TestProc3]
AS
SELECT * FROM [Sales].[vSalesPerson]
Executing the above SP returns 17 rows of data. Without further work it does NOT tell me the meta data of the columns returned. I have a requirement that requires knowledge of the column meta data. I then execute the following T-SQL
/* SELECT A */

SELECT * FROM sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID('TestProc3'), 0)

/* SELECT B */

SELECT * FROM sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID('TestProc3'), 1)
The question is: Which SELECT statement (SELECT A or SELECT returns NON NULL values for the columns: source_database, source_Schema, source_Table,source_Column.

[deals2buy]

[b] Wrap SQL Code in SSMS[/b]


SQL Server Management Studio has a setting which will wrap the code displayed on the screen. Even if you resize the screen the code wraps.

You can access the settings from SSMS >> Tools >> Options >> Text Editor >> Transact-SQL. It will show the following screen.
[img]http://www.pinaldave.com/bimg/wrapsql.jpg[/img]

http://www.youtube.com/watch?feature=player_embedded&v=90sSLEsh42M

[deals2buy]

[b] SQL Server Agent Setup and Overview[/b]

SQL Server Agent is SQL Server's job scheduling and alerting service and, when used properly, it can greatly simplify the workload of a Database Administrator (DBA).
At the heart of SQL Server Agent is the facility to run [b]batch jobs[/b]. A 'batch' is simply a "set of actions", often defined using a Transact-SQL script. These can then be run as a [b]job[/b], on a specific [b]schedule[/b], which you might choose to be at times when few users are accessing the system; in other words, they are scheduled as batch jobs, using SQL Server Agent. These batch jobs can be anything from trivial T-SQL tasks to complex, multi-system jobs run across several SQL Server instances and multiple computers. SQL Server Agent also allows you to notify users, via email for example, when a particular batch job completes, or when an error happens (the Notification engine is covered in Level 3).
It is very likely that you, as a DBA, will immediately be able to make use of SQL Server Agent to schedule jobs on a new SQL Server deployment. For example, your first job might be to backup the system databases, with a mail alert to tell you whether the backup job completed or failed. Subsequent jobs will probably include setting up and scheduling backups of your user databases, index maintenance operations (rebuilds or reorganizations), and so on. If you are managing a fairly simple SQL Server system, then you will enable maintenance plan job scheduling, for example, using the SSMS database maintenance plan wizard.
This article is the first level in a Stairway exploring the features of SQL Server Agent, and discussing how to take full advantage of SQL Server Agent. It will introduce the majority of the components and capabilities of SQL Server Agent, show to the get the services running, and demonstrate how to create and run a simple batch job from SSMS. Subsequent levels will then drill into each major component in much greater depth, including how to use SQL Server Agent using SMO, PowerShell, and Transact-SQL.
This series will focus on SQL Server 2005, SQL Server 2008, and SQL Server 2008 R2; however, the majority of the information in this series also applies to SQL Server 2000.
[b] Getting SQL Server Agent Running[/b]

SQL Server Agent is not enabled by default during the initial setup of SQL Server, so the first simple step to using SQL Server Agent is to get the SQL Server Agent service running.
[b] Setting the Agent to Auto-start with SQL Server[/b]

To set SQL Server Agent to auto-start, open up the SQL Server Configuration Manager utility, found in the "Microsoft SQL Server 2008" | "Configuration Tools" folder. You will need to be a local windows administrator (or be best friends with one) to run this utility.
Once started, the utility should automatically be connected to your local machine. Click on the "SQL Server Services" option in the tree. In the right panel you should see a list of the installed SQL Server services. One of these should be "SQL Server Agent ([b][i]instancename[/i][/b])" where [b][i]instancename[/i][/b] is either the name of the instance used when you installed SQL Server or "MSSQLSERVER" if you're configuring a default instance. If the [b]Start Mode[/b] is already set to automatic, great, it was correctly configured during setup. If not, double click SQL Server Agent, click on the "Service" tab in the properties dialog that appears, and click on the drop-down next to [b]Start Mode[/b]. Select [b]Automatic[/b], then click [b]Apply[/b] (see figure 1.1 for what this should look like). Now SQL Server Agent will automatically start when the server is restarted, and SQL Server is running. If for some reason SQL Server Agent is not running, start the service now.
[img]http://www.sqlservercentral.com/images/8241.jpg[/img]
Figure 1.1 – SQL Server Configuration Manager for SQL Server Agent
[b] Service Account Selection[/b]

You need to ensure that you have an appropriate service account configured based upon what you plan on doing with SQL Server Agent. If you were following along, you are on the Service tab of the SQL Server Agent properties. Click over to the [b]Log On[/b] tab. For basic operations of SQL Server Agent, you can use a built-in account, such as [b]Network Service[/b] (the service account will be set to whatever was selected during the initial installation of SQL Server). Figure 2 shows the selection dialog. Simply select Network Service from the list under "Built-In Account". If you intend to have SQL Server Agent connect to remote machines (to copy files, for example, or to administer multiple systems) then you will want to switch to using a domain user account (the "This account" option covered by the drop-down in Figure 1.2); probably one specifically created for this purpose. This is something you would need to request from your Domain Administrator. You will need an ordinary user account from the Domain Administrator's perspective. In either event, select the correct service account, and once you've made any changes, you will need to select the Restart button to have the new changes take effect. Once you've completed that, click the OK button to finish configuring the SQL Server Agent service. You can read more about service account selection at [url="http://msdn.microsoft.com/en-us/library/cc281953.aspx"]http://msdn.microsoft.com/en-us/library/cc281953.aspx[/url].

[img]http://www.sqlservercentral.com/images/8242.jpg[/img]
Figure 1.2 – Service Account selection for SQL Server Agent
[b] Creating your first Job[/b]

Now that SQL Server Agent is running, you can create your first job. By way of demonstration, we'll set up a job to perform one of the critical DBA tasks, which is to back up your system databases so that, rather than having to remember to manually back up these databases, you can create an automated job that will do it for you.
[b] Jobs[/b]

A [b]job[/b] is the core container for a single logical task, such as backing up a database. That task will contain one or more [b]job steps[/b]. The job may include notifications (for example, email the DBA if a job fails), schedules (when you want the job to run), and may even run on multiple systems. For our first backup job, however, we will keep it simple.
To create the job, open SSMS and connect to your instance of SQL Server. Expand the SQL Server Agent node, then right-click on Jobs. Select the option to create a new job ("New Job…") as shown in Figure 1.3.
[img]http://www.sqlservercentral.com/images/8243.jpg[/img]
Figure 1.3 – Launch the New Job Dialog
A job has a number of components, as you can see in Figure 1.4.
[img]http://www.sqlservercentral.com/images/8244.jpg[/img]
Figure 1.4 – The New Job Dialog
We will name the job "Back Up Master Database". This will be the name used to refer to the job in the SSMS GUI or from PowerShell. Job Categories can be a useful way of organizing your jobs, but it's not important for your first job to set a category. You'll learn how to specify categories in a later level. In the Description box, write something that will help you remember why you created this job and what it's supposed to do. Remember that someone else may well have to administer this system later and, since they didn't create this job, they won't know what it's supposed to accomplish and whether or not it's critical.
Having done all this, you're basically done creating the job 'shell', which is just the 'shell container' for all the components you see as tabs on the New Job Dialog. The most important parts of the job are the components that are on the other tabs.
[b] Job Steps[/b]

A job within SQL Server Agent is made up of at least one job step. When most people think of a SQL Server job performing some work, what they really mean is a job step. A job step is defined by the type of action you wish to perform, and includes the ability to run the following job subsystems:[list]
[*] ActiveX
[*] Operating System (CMDExec)
[*] PowerShell
[*] A variety of replication tasks
[*] SQL Server Analysis Services (SSAS) Command (i.e. XML/A)
[*] SQL Server Analysis Services (SSAS) Query (MDX)
[*] SQL Server Integration Services (SSIS) Package (DTS Package in SQL Server 2000)
[*] Transact-SQL Script (T-SQL)
[/list]
For most SQL Server DBAs, the majority of your jobs will be using the last type of job step, T-SQL. A job can be run as the job owner, or using another security context, depending upon the permissions of the job owner and configuration of proxies. Job Steps and Subsystems will be covered in more detail in the next level.
As you can see in the SSMS GUI, the tab to control Job Steps is simply called "Steps". Click on that now, and we will create a job step. Click the "New" button on the bottom of the dialog, and the "New Job Step" dialog launches (see Figure 1.5).
[img]http://www.sqlservercentral.com/images/8245.jpg[/img]
Figure 1.5 – New Job Step
Give the Job Step a useful name – in our case, the job name and the step name are probably identical – "Backup Master Database". The job step type will be T-SQL, as we will use a simple BACKUP command for our database backup. You can ignore "Run as" for now, as by default we will run the job step as the owner of the job (i.e. you). The database is set correctly by default for what we are doing, which is to work in master.
For the command itself, you can open a file , or copy and paste in some valid T-SQL you've written in a query window. To keep it simple, we're doing a simple backup command. You will need to change the file location to one that works on your computer if you didn't install SQL Server on your C drive. The samples I'm showing come from a simple Virtual Machine install of SQL Server, all on a single hard drive. You shouldn't see a production SQL Server all on a single hard drive like this!
BACKUP DATABASE master
TO DISK='C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\master.bak'
WITH INIT
When complete it should look like Figure 1.6. Click OK to create the job step.
[img]http://www.sqlservercentral.com/images/8246.jpg[/img]
Figure 1.6 – the completed Job Step
[b] Job Schedules[/b]

Job Schedules control when a job will actually be started. There are four types of job schedules:[list]
[*] Start automatically when SQL Server Agent starts
[*] Start whenever the CPUs become idle
[*] Recurring
[*] One Time
[/list]
The majority of the job schedules will be of the Recurring type (for example, run my backup once a week on Sunday at 7pm). Schedules are separate objects within SQL Server Agent, and a schedule may be connected to multiple jobs.
For our job, we want the database to be backed up weekly. So, click on the Schedules tab, and select New to create a new job schedule, as in Figure 1.7. Name the job schedule something practical (because they can be shared between jobs). I'll call this schedule "Weekly System backups". Luckily for us, the defaults on this page will work great! We'll have it run once a week, on Sunday, at Midnight. Click OK when you've named the schedule, then click OK again to finish the job creation.
[img]http://www.sqlservercentral.com/images/8247.jpg[/img]
Figure 1.7 – New Job Schedule
[b] Run the Job you just created[/b]

Now that we have created our backup job, it's a good idea to run the job to verify we did everything correctly. Even though we scheduled this job to run weekly, we can always run a job manually on demand. In SSMS, under the Jobs folder, you should now see your job. Right-click on the Job, and select "Start Job at Step…", as in figure 1.8. The job will run, and now your master database is backed up.
[img]http://www.sqlservercentral.com/images/8248.jpg[/img]
Figure 1.8 – Start Job in SSMS

[deals2buy]

[b] Querying SQL Server Agent Job History Data[/b]


[b] Problem[/b]

Often times we are tasked with having to programmatically come with a list of durations per [url="http://www.mssqltips.com/sql-server-tip-category/27/sql-server-agent/"]SQL Server Agent Job[/url] to trend the run times and order the results by date. Unfortunately Microsoft has not made that very easy in the way the data is stored in the system tables in the [url="http://www.mssqltips.com/sqlservertip/1420/sql-server-system-databases/"]MSDB database[/url]. In this tip I will explain how to use the system tables to get the data into the correct format for dates and job durations.
[b] Solution[/b]

SQL Server stores job information in the [url="http://www.mssqltips.com/sqlservertip/1420/sql-server-system-databases/"]MSDB[/url] system tables. The table that stores historical job executions is [url="http://msdn.microsoft.com/en-us/library/ms174997.aspx"]msdb.dbo.sysjobhistory[/url] and the date/time information as well as the duration of the job execution is stored a little different than what you will see in most system tables. Let's dive into the tables to learn how to perform the calculations.
[b] MSDB.dbo.sysjobhistory Table - run_date and run_time Columns[/b]

In the msdb.dbo.sysjobhistory table, the date (run_date column) and time (run_time column) are stored as two different columns and the columns are an INT data type as seen in the screenshot below.
[img]http://www.mssqltips.com/tipimages2/2850_1_sysjobhistory.jpg[/img]
If you were to query the sysjobhistory (joined to sysjobs of course) this is what the raw data would look like. Note run_date appears to be in YYYYMMDD format but the time is a little trickier. It is in format HHMMSS and not always 6 numbers long as you can see in this example the times are 3:30 AM represented by 33000 in the run_time column.
[img]http://www.mssqltips.com/tipimages2/2850_2_QueryOriginalFieldsDateTime.jpg[/img]

The result set in the picture above was generated by running the following T-SQL script:

select
j.name as 'JobName',
run_date,
run_time
From msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory h
ON j.job_id = h.job_id
where j.enabled = 1 --Only Enabled Jobs
order by JobName, run_date, run_time desc

This can be very confusing when trying to analyze these results and more importantly trying to order results by date and time, which as a common practice for DBA's. There are many ways to get the date/time into a true DATETIME format and I have seen some very creative scripts over the years. Starting in SQL Server 2005 there is an system scalar function located in MSDB that will convert this for you. I say it is undocumented as I could not find a BOL article for it. The function name is [b]MSDB.dbo.agent_datetime(run_date, run_time)[/b] and will return a nicely formatted DATETIME column that is much easier to use programmatically. Below is the same query as the first one above with just an additional column that is a call to this function.

select
j.name as 'JobName',
run_date,
run_time,
msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime'
From msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory h
ON j.job_id = h.job_id
where j.enabled = 1 --Only Enabled Jobs
order by JobName, RunDateTime desc

Below are the results that this query produces and the nicely formatted DATETIME column at the end. This makes sorting easy as well as date range queries against your SQL Agent Jobs.
[img]http://www.mssqltips.com/tipimages2/2850_3_QueryNewFieldsDateTime.jpg[/img]
[b] MSDB.dbo.sysjobhistory Table and run_duration Column[/b]

In the msdb.dbo.sysjobhistory table, the duration (run_duration column) is also stored as an INT and can be very confusing when querying. It is a duration stored in HHMMSS format similar to run_time except this is a duration. As an example 2300 would be 23 minutes. 15467 would be 1 hour, 54 minutes, and 67 seconds. Lets add the run_duration column to our query and see what the results look like.

select
j.name as 'JobName',
run_date,
run_time,
msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',
run_duration
From msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory h
ON j.job_id = h.job_id
where j.enabled = 1 --Only Enabled Jobs
order by JobName, RunDateTime desc

As you can see there are different length returns returned in the run_duration column. 2 digits would be just seconds, 3 digits would be single digit minutes and seconds. This can be very confusing when trying to produce a report on job duration over a time period.
[img]http://www.mssqltips.com/tipimages2/2850_4_QueryOriginalFieldsDuration.jpg[/img]

Now we can add another column that will take this run_duration and convert it into something meaningful. In this example I chose minutes as I don't really have too much concern for jobs that run in seconds. Here is the an updated version of T-SQL script with this additional column added that will round to the nearest minute for the run_duration column.

select
j.name as 'JobName',
run_date,
run_time,
msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',
run_duration,
((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60)
as 'RunDurationMinutes'
From msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory h
ON j.job_id = h.job_id
where j.enabled = 1 --Only Enabled Jobs
order by JobName, RunDateTime desc

You can see that the columns that have an original run_duration that are 2 digits are either 0 or 1 depending if they are over or under 30 seconds. Also you can see the original run_duration values that are 3 digits are rounded to the nearest minute.
[img]http://www.mssqltips.com/tipimages2/2850_5_QueryNewFieldsDuration.jpg[/img]
[b] T-SQL Script for SQL Server Agent Job History[/b]

Up to this point I have shown how to handle the date/time columns to get into a DATETIME formatted single column as well as the job durations into minutes. This makes the analysis and auditing of your SQL Server Agent Jobs much easier. Below is the final script with the original unformatted columns removed. This will give you a list of jobs ordered by name and run date with their associated duration.

select
j.name as 'JobName',
msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',
((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60)
as 'RunDurationMinutes'
From msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory h
ON j.job_id = h.job_id
where j.enabled = 1 --Only Enabled Jobs
--and j.name = 'TestJob' --Uncomment to search for a single job
/*
and msdb.dbo.agent_datetime(run_date, run_time)
BETWEEN '12/08/2012' and '12/10/2012' --Uncomment for date range queries
*/
order by JobName, RunDateTime desc

[b] T-SQL Script for SQL Server Agent Job Step History[/b]

I modified the script above and joined the msdb.dbo.sysjobsteps table in order to get the duration by job step. This information is helpful for long ETL jobs that have many steps in order to determine which step is taking the longest to run within the job.

select
j.name as 'JobName',
s.step_id as 'Step',
s.step_name as 'StepName',
msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',
((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60)
as 'RunDurationMinutes'
From msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobsteps s
ON j.job_id = s.job_id
INNER JOIN msdb.dbo.sysjobhistory h
ON s.job_id = h.job_id
AND s.step_id = h.step_id
AND h.step_id <> 0
where j.enabled = 1 --Only Enabled Jobs
--and j.name = 'TestJob' --Uncomment to search for a single job
/*
and msdb.dbo.agent_datetime(run_date, run_time)
BETWEEN '12/08/2012' and '12/10/2012' --Uncomment for date range queries
*/
order by JobName, RunDateTime desc

[deals2buy]

Which code below will return the date of New Year's Eve if run every day in January?

-- select 1
SELECT dateadd(day,-(datepart(day,dateadd(mm,1,GETDATE()))),dateadd(mm,-1,GETDATE())
-- select 2
SELECT dateadd(day,-(datepart(day,dateadd(mm,1,GETDATE())),GETDATE())
-- select 3
SELECT dateadd(day,-(datepart(day,dateadd(mm,1,GETDATE()))),dateadd(mm,1,GETDATE())

[deals2buy]

What will be returned by the following statements?
select ((CONVERT(NUMERIC(5,0),123) / CONVERT(NUMERIC(5,0),100)) * 100) AS [Percentage]
select ROUND(((CONVERT(Float,123) / CONVERT(Float,100)) * 100),1) AS [Percentag]

[lolliman]

[img]http://www.andhrafriends.com/uploads/profile/photo-thumb-13458.jpg?_r=1356476459[/img]

[deals2buy]

[b] Script to Find SQL Server on Network[/b]

I manage lots of SQL Servers. Many times I forget how many server I have and what are their names. New servers are added frequently and old servers are replaced with powerful servers. I run following script to check if server is properly set up and announcing itself. This script requires execute permissions on XP_CMDShell.

[CODE]
CREATE TABLE #servers(sname VARCHAR(255))
INSERT #servers (sname)
EXEC master..xp_CMDShell 'ISQL -L'
DELETE
FROM #servers
WHERE sname='Servers:'
OR sname IS NULL
SELECT LTRIM(sname)
FROM #servers
DROP TABLE #servers
[/CODE]