Monday, June 29, 2015
To get around that problem you need to generate the script of Publication Stored Procedure.
And execute (alter) them on Subscription Databases.
First You need to update the output limit of you SQL Server Management Studio.
As follow :
Secondly , Change the output to Text as follow :
Then Execute the stored procedure on your published databases and copy the output and execute it on your subscriber database.
EXEC sp_scriptpublicationcustomprocs 'PublicationName'
There is also a utility by Code Project : here
Monday, October 28, 2013
Here is a simple TSQL code that gives you the Schedule Name & ID which invoked he current job execution.
DECLARE @Schedule_Name_ID nVARCHAR(1000)
DECLARE @Job_ID nVARCHAR(100)
DECLARE @State INT
DECLARE @Job_Name nVARCHAR(1000)
SET @Job_Name = 'Your Job Name Goes Here'
SELECT @Job_ID = job_id
WHERE name = @Job_Name
IF(@Job_ID IS NOT NULL)
CREATE TABLE #job_current_state
INSERT INTO #job_current_state
EXEC master.dbo.xp_sqlagent_enum_jobs 1, garbage
SELECT @Schedule_Name_ID = Request_Source_ID ,@State = State
where job_id = @Job_ID
SELECT ISNULL(@Schedule_Name_ID,'NA') Schedule_Name_ID
,CASE @State WHEN 1 THEN 'Running' ELSE 'Stopped' END State
DROP TABLE #job_current_state
SELECT 'Job does not exist' output
Thursday, July 05, 2012
Monday, June 11, 2012
Wednesday, November 23, 2011
Calculate difference of Time only between two dates while discarding difference of dates in SQL Server/ extract time and calculate difference between two dates in SQL Server
DECLARE @DateTimeOne DATETIME
DECLARE @DateTimeTwo DATETIME
SET @DateTimeOne = '1900-01-01 10:41:09.250'
SET @DateTimeTwo = GETDATE()
--hour = hh
--minute = mi, n
--second = ss, s
--millisecond = ms
SELECT DATEDIFF(mi,SUBSTRING(CONVERT(VARCHAR,@DateTimeOne, 121), 12, 12)
,SUBSTRING(CONVERT(VARCHAR,@DateTimeTwo, 121), 12, 12) ) MinuteDifferenceinDate
Friday, June 24, 2011
here is a UDF you can use to validate the email address
It will validate mentioned below list
No Spaces are allowed
email cant start with '@'
email cant end at '.'
@must be in email and only once
domain name should be at the end and must be at least two characters
email cant habe patterns like '..' or '.@'
CREATE FUNCTION [dbo].[validateEmailAddress]
RETURNS TINYINT AS
DECLARE @Result TINYINT
SELECT @Result =
CHARINDEX(' ',LTRIM(RTRIM(@EmailAddress))) = 0
AND LEFT(LTRIM(@EmailAddress),1) <> '@'
AND RIGHT(RTRIM(@EmailAddress),1) <> '.'
AND CHARINDEX('.',@EmailAddress,CHARINDEX('@',@EmailAddress)) - CHARINDEX('@',@EmailAddress) > 1
AND LEN(LTRIM(RTRIM(@EmailAddress))) - LEN(REPLACE(LTRIM(RTRIM(@EmailAddress)),'@','')) = 1
AND CHARINDEX('.',REVERSE(LTRIM(RTRIM(@EmailAddress)))) >= 3
AND (CHARINDEX('.@',@EmailAddress) = 0 AND CHARINDEX('..',@EmailAddress) = 0)
THEN 1 ELSE 0 END
Tuesday, January 11, 2011
- One Persisted Computed Column (BIGINT) which will store DATETIME in integer format by removing special symbols from DATETIME
- This computed column will be added in the covering index
- Query will use this column instead of DATETIME column
Wednesday, November 03, 2010
A simple and powerful tool for migrating data from SQL Server to MySQL.
It can import/export your data directly to database server and you can also generate Create and Insert scripts.
A useful tool by MySQL team.
You can download the tool by signing up on mentioned below site and download this tool