For Each Container Example
http://beyondrelational.com/modules/2/blogs/106/posts/11122/loop-through-each-record-in-a-text-file-recordset-destination.aspx
Mapping SP variable in OLEDB Source-->
Friday, March 16, 2012
Tuesday, March 6, 2012
Useful Special Characters For Sql Server Dynamic Queries
Char(44) -- comma
Char(39) -- Single Quote
char(9) -- Tab
char(10) -- Line feed
char(13) -- Enter\Carriage return
Char(32) -- Space
Go to SSMS.. Type :
[code language="sql"]
Select Char(44)
[/code]
and see the magic :)
Char(39) -- Single Quote
char(9) -- Tab
char(10) -- Line feed
char(13) -- Enter\Carriage return
Char(32) -- Space
Go to SSMS.. Type :
[code language="sql"]
Select Char(44)
[/code]
and see the magic :)
Wednesday, February 22, 2012
Sql Server Formatter - Free and Useful
http://architectshack.com/PoorMansTSqlFormatter.ashx#Download_8 --> Download SSMS Plugin
http://poorsql.com/ --> Free Online Formatter
http://poorsql.com/ --> Free Online Formatter
Friday, February 17, 2012
Thursday, February 9, 2012
Trim Leading and Trailing quotes before inserting to database - Sql Server
Importing from a excel to SSIS is a tedious task. Some time we have to convert from excel to tab seprated text file and then import.
While converting from excel to tab separated file "quotes" get appended to the text.
Following is a Sql function to remove leading and trailing quotes from text and then insert to db.
[code language="sql"]</pre>
CREATE FUNCTION dbo.TrimQuotes(@str NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @Start INT, @End Int, @Length INT
SET @Str = REPLACE(RTRIM(LTRIM(@Str)),'""', '"')
SELECT @Start = CHARINDEX('"', @Str), @End = CHARINDEX('"', REVERSE(@Str)),
@Length = LEN(@Str)
IF @Start =1
SET @Start = @Start + 1 -- new string must start from second character
ElSE
SET @Start = 1 --as there is not starting quote. we will not trim it
IF @End != 1
SET @End = 0 -- as there is not ending quote. we will not trim it
SELECT @Str = SUBSTRING(@Str,@Start, @Length - @Start - @End + 1)
RETURN @str
END
GO
SELECT dbo.TrimQuotes ( '"abc"') , dbo.TrimQuotes ( ' abc" ') , dbo.TrimQuotes ( '"a"b"c') , dbo.TrimQuotes ( 'a"b"c')
, dbo.TrimQuotes ( '"abc'), dbo.TrimQuotes ( '"a""bc'), dbo.TrimQuotes ( '"""b""c""')
<pre>
[/code]
While converting from excel to tab separated file "quotes" get appended to the text.
Following is a Sql function to remove leading and trailing quotes from text and then insert to db.
[code language="sql"]</pre>
CREATE FUNCTION dbo.TrimQuotes(@str NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @Start INT, @End Int, @Length INT
SET @Str = REPLACE(RTRIM(LTRIM(@Str)),'""', '"')
SELECT @Start = CHARINDEX('"', @Str), @End = CHARINDEX('"', REVERSE(@Str)),
@Length = LEN(@Str)
IF @Start =1
SET @Start = @Start + 1 -- new string must start from second character
ElSE
SET @Start = 1 --as there is not starting quote. we will not trim it
IF @End != 1
SET @End = 0 -- as there is not ending quote. we will not trim it
SELECT @Str = SUBSTRING(@Str,@Start, @Length - @Start - @End + 1)
RETURN @str
END
GO
SELECT dbo.TrimQuotes ( '"abc"') , dbo.TrimQuotes ( ' abc" ') , dbo.TrimQuotes ( '"a"b"c') , dbo.TrimQuotes ( 'a"b"c')
, dbo.TrimQuotes ( '"abc'), dbo.TrimQuotes ( '"a""bc'), dbo.TrimQuotes ( '"""b""c""')
<pre>
[/code]
Job to list all the Sql Server Agent Jobs
[code language="sql"]
with JobStatus as
(
select
job_id AS JobId,
row_number() over(partition by job_id order by run_date desc, run_time desc) asExecutionRank,
case run_status when 0 then 'Failed' when 1 then 'Succeeded' when 2 then 'Retry' when3 then 'Canceled' end as LastRunStatus
from
msdb.dbo.sysjobhistory
)
select
j.job_id as JobId,
j.name as JobName,
j.[description] as JobDescription,
j.[enabled] as IsJobEnabled,
js.LastRunStatus,
ja.run_requested_date as LastRunDate,
ja.next_scheduled_run_date as NextRunDate,
datediff(second, ja.start_execution_date, ja.stop_execution_date) as JobDuration
from
msdb.dbo.sysjobs j
join msdb.dbo.sysjobactivity ja on j.job_id = ja.job_id
left join JobStatus js on j.job_id = js.JobId
and js.ExecutionRank = 1
order by
JobName,
NextRunDate
[/code]
with JobStatus as
(
select
job_id AS JobId,
row_number() over(partition by job_id order by run_date desc, run_time desc) asExecutionRank,
case run_status when 0 then 'Failed' when 1 then 'Succeeded' when 2 then 'Retry' when3 then 'Canceled' end as LastRunStatus
from
msdb.dbo.sysjobhistory
)
select
j.job_id as JobId,
j.name as JobName,
j.[description] as JobDescription,
j.[enabled] as IsJobEnabled,
js.LastRunStatus,
ja.run_requested_date as LastRunDate,
ja.next_scheduled_run_date as NextRunDate,
datediff(second, ja.start_execution_date, ja.stop_execution_date) as JobDuration
from
msdb.dbo.sysjobs j
join msdb.dbo.sysjobactivity ja on j.job_id = ja.job_id
left join JobStatus js on j.job_id = js.JobId
and js.ExecutionRank = 1
order by
JobName,
NextRunDate
[/code]
Subscribe to:
Posts (Atom)