does any one has any sql server function that passes some text and returns a string without html tags.
example:
nice day
should return nice day
or if other html tags strip them off.
thanks for your help.
-Fr
I have a column of string which has html tags attached to it. How can I remove them..other than manually going and doing it? Any funtions?
Thanks!!
Tanya
Hi !
i have a function written in c# which removes all html tags from the provide string like
public static string RemoveHTML(string HTML)
{
return Regex.Replace(HTML, "<(.|)*?>", "");
}
how can i apply such functionality to varchar field which removes all the html tags from it in stored procedure
Regards,
DiL
I have a table with a column that has html text. The column with html text is pretty big datatye varchar(max)... I wanted to check if any of you have any function that I can use to Strip out the HTML tags... I saw couple of version online, but it was running too slow..
This is the one I used: [URL] .....
I ran a CONTAINS query for the word "target" in a bunch of index web pages. I came up with lots of matches -- but they were all inside html tags:
<a href="www.foo.com" target = "_blank">lorem ipsum</a>
Is there a good way to exclude tags (and their attributes) from the full-text index?
Thanks!
I had a problem with the ntext datatype. I need to strip the HTML tags out of a ntext datatype column. I have sample query for that, which works fine for STRING, as stuff is the string function, what to do for ntext field.
=======The Process follows like this =========
--**************************************
--
-- Name: A relational technique to strip
-- the HTML tags out of a string
-- Description:A relational technique to
-- strip the HTML tags out of a string. Th
-- is solution demonstrates how to use simp
-- le tables & search functions effectively
-- in SQL Server to solve procedural / ite
-- rative problems.
-- This table contains the tags to be re
-- placed. The % in <head%>
-- will take care of any extra informati
-- on in the tag that you needn't worry
-- about as a whole. In any case, this t
-- able contains all the tags that needs
-- to be search & replaced.
CREATE TABLE #html ( tag varchar(30) )
INSERT #html VALUES ( '<html>' )
INSERT #html VALUES ( '<head%>' )
INSERT #html VALUES ( '<title%>' )
INSERT #html VALUES ( '<link%>' )
INSERT #html VALUES ( '</title>' )
INSERT #html VALUES ( '</head>' )
INSERT #html VALUES ( '<body%>' )
INSERT #html VALUES ( '</html>' )
go
-- A simple table with the HTML strings
CREATE TABLE #t ( id tinyint IDENTITY , string varchar(255) )
INSERT #t VALUES (
'<HTML><HEAD><TITLE>Some Name</TITLE>
<LINK REL="stylesheet" HREF="/style.css" TYPE="text/css" ></HEAD>
<BODY BGCOLOR="FFFFFF" VLINK="#444444">
SOME HTML text after the body</HTML>'
)
INSERT #t VALUES (
'<HTML><HEAD><TITLE>Another Name</TITLE>
<LINK REL="stylesheet" HREF="/style.css"></HEAD>
<BODY BGCOLOR="FFFFFF" VLINK="#444444">Another HTML text after the body</HTML>'
)
go
-- This is the code to strip the tags out.
-- It finds the starting location of eac
-- h tag in the HTML string ,
-- finds the length of the tag with the
-- extra properties if any. This is
-- done by locating the end of the tag n
-- amely '>'. The same is done
-- in a loop till all tags are replaced.
BEGIN TRAN
WHILE exists(select * FROM #t JOIN #html on patindex('%' + tag + '%' , string ) > 0 )
UPDATE #t
SET string = stuff( string , patindex('%' + tag + '%' , string ) ,
charindex( '>' , string , patindex('%' + tag + '%' , string ) )
- patindex('%' + tag + '%' , string ) + 1 , '' )
FROM #t JOIN #html
ON patindex('%' + tag + '%' , string ) > 0
SELECT * FROM #t
rollback
Hi, I was wondering if any SQL Server gurus out there could help me...I
have a table which contains text resources for my application. The text
resources are multi-lingual so I've read that if I add a html language
indicator meta tag e.g.<META NAME="MS.LOCALE" CONTENT="ES">and
store the text in a varbinary column with a supporting Document Type
column containing ".html" of varchar(5) then the full text index
service should be intelligent about the language word breakers it
applies when indexing the text. (I hope this is correct technique for
best multi-lingual support in a single table?)However, when I come to query this data the results always return 0 rows (no errors are encountered). e.g.DECLARE @SearchWord nvarchar(256)SET @SearchWord = 'search' -- Yes, this word is definitely present in my resources.SELECT * FROM Resource WHERE CONTAINS(Document, @SearchWord)I'm a little puzzled as Full Text search is working fine on another table that employs an nvarchar column (just plain text, no html).Does the filter used for full text indexing of html expect certain tags to be present as standard? E.g. <html> and <body> tags? At present the data I have stored might look like this (no html or body wrapping tags):Example record 1 data: <META NAME="MS.LOCALE" CONTENT="EN">Search for keywords:Example record 2 data: <META NAME="MS.LOCALE" CONTENT="EN">Sorry no results were found for your search.etc.Any pointers / suggestions would be greatly appreciated. Cheers,Gavin.UPDATE: I have tried wrapping the text in more usual html tags and re-built the full text index but I still never get any rows returned for my query results. Example of content wrapping tried - <HTML><HEAD><META NAME="MS.LOCALE" CONTENT="EN"></HEAD><BODY>Test text.</BODY></HTML>I've also tried stripping all html tags from the content and set the Document Type column = .txt but I still get no rows returned?!?
Hi, I was wondering if any SQL Server gurus out there could help me...
I have a table which contains text resources for my application. The text resources are multi-lingual so I've read that if I add a html language indicator meta tag e.g.
<META NAME="MS.LOCALE" CONTENT="ES">
and store the text in a varbinary column with a supporting Document Type column containing ".html" of varchar(5) then the full text index service should be intelligent about the language word breakers it applies when indexing the text. (I hope this is correct technique for best multi-lingual support in a single table?)
However, when I come to query this data the results always return 0 rows (no errors are encountered). e.g.
DECLARE @SearchWord nvarchar(256)
SET @SearchWord = 'search' -- Yes, this word is definitely present in my resources.
SELECT * FROM Resource WHERE CONTAINS(Document, @SearchWord)
I'm a little puzzled as Full Text search is working fine on another table that employs an nvarchar column (just plain text, no html).
Does the filter used for full text indexing of html expect certain tags to be present as standard? E.g. <html> and <body> tags? At present the data I have stored might look like this (no html or body wrapping tags):
Example record 1 data: <META NAME="MS.LOCALE" CONTENT="EN">Search for keywords:
Example record 2 data: <META NAME="MS.LOCALE" CONTENT="EN">Sorry no results were found for your search.
etc.
Any pointers / suggestions would be greatly appreciated. Cheers,
Gavin.
UPDATE: I have tried wrapping the text in more usual html tags and re-built the full text index but I still never get any rows returned for my query results. Example of content wrapping tried - <HTML><HEAD><META NAME="MS.LOCALE" CONTENT="EN"></HEAD><BODY>Test text.</BODY></HTML>
I've also tried stripping all html tags from the content and set the Document Type column = .txt but I still get no rows returned?!?
I want to replace div tags with p tags in a column in sql.
<div style: bold> abc </abc>
<div> efgh></div>
required output:
<p>abc</p>
<p>efgh</p>
I am retrieving a field from SQL and displaying that data on a web page.
The data contains a mixture of text and html codes, like this "<b>test</b>".
But rather than displaying the word test in bold, it is displaying the entire sting as text.
How do I get it to treat the HTML as HTML?
Does anyone know how to get rid of rtf tags that are stored in the table? I need to filter out the data and wondering if there is a utility on the SQL Server that can do it.
View 16 Replies View RelatedThis algorithm can be used to strip out HTML tags too.
With reference to http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89973
and http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=90000CREATE FUNCTIONdbo.fnParseRTF
(
@rtf VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE@Stage TABLE
(
Chr CHAR(1),
Pos INT
)
INSERT@Stage
(
Chr,
Pos
)
SELECTSUBSTRING(@rtf, Number, 1),
Number
FROMmaster..spt_values
WHEREType = 'p'
AND SUBSTRING(@rtf, Number, 1) IN ('{', '}')
DECLARE@Pos1 INT,
@Pos2 INT
SELECT@Pos1 = MIN(Pos),
@Pos2 = MAX(Pos)
FROM@Stage
DELETE
FROM@Stage
WHEREPos IN (@Pos1, @Pos2)
WHILE 1 = 1
BEGIN
SELECT TOP 1@Pos1 = s1.Pos,
@Pos2 = s2.Pos
FROM@Stage AS s1
INNER JOIN@Stage AS s2 ON s2.Pos > s1.Pos
WHEREs1.Chr = '{'
AND s2.Chr = '}'
ORDER BYs2.Pos - s1.Pos
IF @@ROWCOUNT = 0
BREAK
DELETE
FROM@Stage
WHEREPos IN (@Pos1, @Pos2)
UPDATE@Stage
SETPos = Pos - @Pos2 + @Pos1 - 1
WHEREPos > @Pos2
SET @rtf = STUFF(@rtf, @Pos1, @Pos2 - @Pos1 + 1, '')
END
SET@Pos1 = PATINDEX('%cf[0123456789][0123456789 ]%', @rtf)
WHILE @Pos1 > 0
SELECT@Pos2 = CHARINDEX(' ', @rtf, @Pos1 + 1),
@rtf = STUFF(@rtf, @Pos1, @Pos2 - @Pos1 + 1, ''),
@Pos1 = PATINDEX('%cf[0123456789][0123456789 ]%', @rtf)
SELECT@rtf = REPLACE(@rtf, 'pard', ''),
@rtf = REPLACE(@rtf, 'par', ''),
@rtf = LEFT(@rtf, LEN(@rtf) - 1)
SELECT@rtf = REPLACE(@rtf, '0 ', ''),
@rtf = REPLACE(@rtf, ' ', '')
SELECT@rtf = STUFF(@rtf, 1, CHARINDEX(' ', @rtf), '')
RETURN@rtf
ENDE 12°55'05.25"
N 56°04'39.16"
How should I go about releasing any objects I've created? I've got a very complex CLR stored proc that creates a dataset, many sqlcommand objects, an adapter, etc, etc. It's used to evaluate high school transcripts for completion of the core subjects. It's a very complex SP that takes a single SSN as a parameter.
Right now it works as expected when I call it from a query window in SQL Mgmt Studio, I can stack 39 calls in a row like:
exec usp_Evaluate_eTranscript '011...'
exec usp_Evaluate_eTranscript '012...'
...
exec usp_Evaluate_eTranscript '039...'
And if I build a little test harness in VB.Net calling it in a populated dataset For ... Next loop, it works just fine for the 39 valid values I have.
But, when I build a cursor in a query window in MgmtStudio I get some strange behavior, that just doesn't really make sense. I'm getting a foreign key violation and I'm not even trying to insert any rows, just updating existing ones.
So, the only possible cause that I haven't eliminated yet is that some objects are still hanging around when the cursor tries to call the SP again.
It's declared:
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub usp_Evaluate_eTranscript(ByVal SSN As String)
And exits with the standard End Sub.
Is there some way I can help the garbage collector along? Kick it into action? Maybe some
Set sqlcommand = Nothing or
sqlcommand.Dispose
dataset.Clear or
dataset.Dispose or somesuch thing?
I'm closing the context connection immediately after each time I use it (I have to so the next object can work).
I have generated a database for my website, I intend on using software that will convert the database into static web pages.
Big problem I have I am not a programmer, but I know a tiny bit about tags etc. for search engines.
The meta tag description is what I want to create using a field in this database.
The software I am about to use has a sql builder is there anyway it could be done be highlighting the relavent field and using sql language.
PLEASE someone Help
This problem has been driving me around the twist.
Can someone give me some advise on how to clean out my log file?
Thanks :confused:
Hey gang, I have 2 100,000 record tables, with a ID & address field. (I've exclude all the other field for now) I need to make sure the ID field in table A is equal to table B and that the address in table A is equal to table B. If the Id's or address do not match create a separate table. Any good ideas
View 2 Replies View RelatedHello
my 2 tables in MS SQL 2000
Report :
Report_id (PK)
name
Product :
Product_id (PK)
Report_id (FK)
name
the Foreign Key and Primary Key have been added later (when the tables were allready full)
product has a few millions of lines and report a few 10.thousand
now I want to clean the 2 tables and remove all the lines which are not conected by PK > FK or FK > PK
i am trying :
DELETE FROM Product WHERE (Product.Report_id NOT IN (SELECT Report.Report_id FROM Report))
DELETE FROM Report WHERE (Report.Report_id NOT IN (SELECT Product.Report_id FROM Product))
but the database crash : time overflow !
how can I do it ?
thank you
Hi there, I have the following set of records: (DDL and data provided)
create table dbo.myNames (
FilePersonId int,
Sex varchar(20),
Title varchar(20),
Forename varchar(50),
Surname varchar(50)
)
insert into dbo.myNames (FilePersonId, Sex, Title, Forename, Surname)
select 477827, 'F', 'MISS', 'NATALIE', 'MR NIEL MC DONOUGH' union all
select 516992, 'M', 'MR', 'MARIO.', 'MISS CARLA THRINCHEIRAS' union all
select 1725198, 'M', 'MR', 'THOMAS', 'MS TAGGERT' union all
select 1907673, 'M', 'MR', 'COLIN', 'MR C PATTERSON' union all
select 2282432, 'M', 'MR', 'JOHN', 'MR GOLDSMITH' union all
select 3172847, 'M', 'MR', 'JASON', 'MR JASON MARCUS RENDALL' union all
select 3173628, 'M', 'MR', 'STUART', 'MR STUART IAN JAMES CALLOW' union all
select 3174343, 'M', 'MR', 'JOHANNES', 'MR JOHANNES ARIE JAN DE JAGER' union all
select 3176784, 'F', 'MISS', 'MONICA', 'MISS MONICA S F ALMEIDA' union all
select 3177717, 'M', 'MR', 'MOHAMMAD', 'MR MOHAMMAD NASIR KHAN SABIR' union all
select 3180017, 'M', 'MR', 'MD', 'MR MD. MAMUN UR RASHID' union all
select 3180585, 'F', 'MISS', 'KATHRYN', 'MISS KATHRYN M J GRANT' union all
select 3182306, 'M', 'MR', 'ABDUL', 'MR ABUL K M H KABIR' union all
select 3182416, 'F', 'MRS', 'CATHERINE', 'MRS CATHERINE ANN LEA DOIG' union all
select 3183759, 'M', 'MR', 'PHILIP', 'MR PHILIP ADAM MOXON' union all
select 3183790, 'F', 'MISS', 'KELLY', 'MISS KELLY NEAVE CHAPPELL' union all
select 3191895, 'F', 'MISS', 'LUCY', 'MISS LUCY ANNE MAY GAULT' union all
select 3192189, 'M', 'MR', 'JOAQUIM', 'MR JOAQUIM FILIPE ABRANTES VAZ' union all
select 3192261, 'F', 'MRS', 'C', 'MRS C D V BALBO-BELL' union all
select 3193077, 'M', 'MR', 'NIDAL', 'MR NIDAL FATHEE ABY SABEI' union all
select 3194554, 'M', 'MR', 'STUART', 'MR STUART ROBERT JAMES DOYLE' union all
select 3201437, 'M', 'MR', 'IAN', 'MR IAN' union all
select 3206600, 'M', 'MR', 'ADRIAN', 'MR ADRIAN JOHN JAMES OWEN' union all
select 3206637, 'M', 'MR', 'J', 'MR J O GARCIA CHAURAN' union all
select 3206695, 'M', 'MR', 'SYED', 'MR SYED SAFDAR SHAH' union all
select 3206744, 'M', 'MR', 'LEE', 'MR LEE ALAN JOEL MAYNARD' union all
select 3206763, 'F', 'MISS', 'TANIA', 'MISS TANIA RACHEL NASH' union all
select 3207104, 'M', 'MR', 'ALEXANDER', 'MR ALEXANDER C D HOPWOOD' union all
select 3207577, 'M', 'MR', 'J', 'MR J H S CAMPBELL' union all
select 3207611, 'F', 'MISS', 'TERRI', 'MISS TERRI LOUISE KATE FLEMING' union all
select 3209050, 'F', 'MISS', 'MELISSA', 'MISS MELISSA NEKITA MAKEMSON' union all
select 3213291, 'M', 'MR', 'MALCOLM', 'MR MALCOLM PETER FORD CROOK' union all
select 3215920, 'M', 'MR', 'IAIN', 'MR IAIN ALASTAIR SMITH DOWNEY' union all
select 3889047, 'M', 'MR', 'PEDORA', 'MR LOFARO' union all
select 3974174, 'M', 'MR', 'MICHAEL', 'MR MICHAEL J KERR' union all
select 3977713, 'M', 'MR', 'XAVIER', 'MR XAVIER DE PONTE DE SOUSA' union all
select 3977753, 'M', 'MR', 'JEAN-DANIEL', 'MR JEAN-DANIEL KENNY FLORIAN' union all
select 6815795, 'U', 'UNKNOWN OR INVALID', 'M', 'MR M WOOD' union all
select 7617439, 'F', 'MRS', 'M', 'MRS MARYMARSHALL' union all
select 7929645, 'M', 'MR', 'RICHARD', 'MR PANTER' union all
select 9092854, 'M', 'MR', 'JARED', 'MR JARED ELVANDO BLAKE' union all
select 9109880, 'M', 'MR', 'MICHAEL', 'MR MICHAEL PAUL LLOYD RAMWELL' union all
select 9165326, 'M', 'MR', 'SCOTT', 'MR SCOTT DE LA FUENTE' union all
select 9321272, 'M', 'MR', 'MPHAMMAD', 'MR MPHAMMAD HAMIDI TABAR' union all
select 9461030, 'M', 'MR', 'KELVIN', 'MR KELVIN POH ANN TAN' union all
select 9508257, 'M', 'MR', 'HARDI', 'MR HARDI M IBRAHIM' union all
select 7899594, 'F', 'MRS', 'SANDRA', 'MRSTHPOMPSON' union all
select 9616820, 'U', 'UNKNOWN OR INVALID', 'HAMMOND', 'MRJOHNHAMMOND' union all
select 9617471, 'M', 'MR', 'PAUL', 'MRPAULWAYMAN' union all
select 9817442, 'M', 'MR', 'ADAM', 'MRADAMMARLEY' union all
select 9853852, 'F', 'MISS', 'P', 'MRBARRYGILMARTIN' union all
select 9895683, 'F', 'MRS', 'REBECCA', 'MRSREBECCAWILSON' union all
select 9924241, 'M', 'MR', 'JOHN', 'MRJOHNWORDEN' union all
select 9941612, 'M', 'MR', 'BRIAN', 'MRBRIANOKEEFFE' union all
select 10214780, 'M', 'MR', 'G', 'MRJAMESWADDLE'
What I am trying to do is cleanse the Surname field. I need to remove the Title/Initials and/or Forename from the Title field. But I am struggling to come up with a neat solution to this problem.
Your help would be much appreciated.
Thanks.
There are two tables A and B where asset tags are present, but in one table in rows and in another in column wise.
for eg
ASSet Tag
SR-062009-00032966
SR-062009-00032962
SR-072009-00020572
SR-072009-00020571
SR-072009-00020585
HH-092009-00038342
Table B
field 1 -->Asset TAG
Record 1-->SR-072009-00020572,SR-072009-00020571,SR-062009-00020685,SR-072009-00001592,SR-072009-00001376,SR-062009-00020683,SR-092009-00001617
field 2 --> Material code
REcord 1-->121
REcord 2-->123
What is the query so that asset tag of A matches with each and every asset tag table of B and output comes as
Output
Asset TAg -------- MAterial Code
SR-062009-00032966
SR-062009-00032962
SR-072009-00020572 ------121
SR-072009-00020571 -------121
SR-072009-00020585
Hi All,
I'm currently using an Execute SQL Task to return XML data from a query into an SSIS string variable. In my FOR XML clause in SQL I'm specifying a certain name for my root tag, called "Accounts". This works great in Management Studio, however, the Execute SQL Task appends a <ROOT> and </ROOT> tag to the start and end of the string, so now it looks like:
<ROOT><Accounts>...all my elements...</Accounts></ROOT>
I'd like to remove the ROOT tags so that the <Accounts> tags are actually the root for this doc. What would be the best way to remove the ROOT tags from the SSIS string variable?
Thanks!
I have been getting some strange results while trying to reduce the size of a transaction log file. The background is the database was fully backed up last night. There has been no activity since the backup. A transaction backup was performed 30 minuted prior to my testing. Here is what I tried:
1. dbcc shrinkdatabase ('mydb',1)
Resulted in no change to the size of the log file (Still 110meg)
2. Right clicked on the database in Enterprise Manager and selected Shrink Database. I selected the log file to shrink and it shrunk to 29meg.
3. Tried dbcc shrinkdatabase ('mydb',1) again and no change.
4. Issued backup log mydb with truncate_only and
dbcc shrinkdatabase ('mydb',1) and the size shrunk to 2 meg.
Am I doing something wrong? I thought that after a full backup and backing up the log file, the out of date log records would be able to be shrunk out.
also, why does the Enterprise Manager shrink work differently from the dbcc command?
Any help would be greatly appreciated.
Thanks,
Ken Nicholson
Hey,
Does anyone know of a neat and easy way to modify this section of hardcode:
SELECT CASE dbo.requestsbyyeartemp.themonth when '1' then 'January' when '2' then 'Febuary' when '3' then 'March'
when '4' then 'April' when '5' then 'May' when '6' then 'June' when '7' then 'July' when '8' then 'August'
when '9' then 'September' when '10' then 'October' when '11' then 'November' when '12' then 'December' end as 'themonth', etc...
Requestsbyyeartemp is a table where the numbers corresponding to the months are stored. This statement is used to make a new table where the months are stored with the proper names and not numbers.
I would sooner not have any hardcode at all if there is a simple way to do it.
Thanks
I have following XQuery:
declare @xmldoc as xml
select @xmldoc = '<Text>This is firstline<Break />This is second line<Break />This is third line</Text>'
select @xmldoc.value('(/Text)[1]','varchar(max)')Result is: "This is firstlineThis is second lineThis is third line"
My problem is, that the <Break /> tags within the text are removed in the conversion to varchar. How to preserve the such tags in the varchar output? Or to get the <Break /> tags "translated" to e.g. CHAR(10)?
The below query will read the data in XML format but any query to read from each column of XML tags easily?
SELECT CAST(record AS XML), record
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_CONNECTIVITY'
What tools has everyone used for cleaning name and address data(including identifying not-immediately-obvious duplicates) inconnection with a CRM project or the Customer dimension of a datawarehouse? What did you like/dislike about the tool you used? Howcustomizable was the tool you used?
View 10 Replies View RelatedAll
I am in trying to clean and standardize the data during the ETL processes using the €œLookup Data Flow Transformation€? in SSIS€¦
I am able to clean data by replacing the values in columns with values from a reference table, using an exact lookup to locate values in a reference table.
What I would like to do is €œif there is NO exact match€? replace it by e.g. zero or some other value which means €œno reference data available€?, how do I do this?
Any help is much appreciated.
Thanks,
Manojkumar
I have been researching on how to cleanup four backup system files located in the MSDB database. I currently have approximately 1 million rows in each of these tables which is making my MSDB database to be 4 gig in size. I cannot find any instructions on how to gracefully clean these tables up or what to do to keep the number of rows down in these tables. The tables are backupfile, backupmediafamily, backupmediaset, and backupset. Thanks.
View 1 Replies View RelatedBack Story...
i am building a new deployment routine for one of our products using NANT (.net version of ANT). I takes all of .sql files out of vault for all non table objects and concatenates them into larger .sql files with IF EXISTS .... DROP ... GO CREATE statements which will then be fired off by osql batch files that will log the errors.
Problem...
When I open the resulting files in notepad I have junk characters (microsoft OEM stuff) in some of my object definitions which you do not see in the QA or if you opnen the file in Vault. I need to get this stuff out of there and I do not want to write a bunch of filesystem code to do it because I need this tested and ready by Friday COB. I know I can solve this by opening the problematic files in ultraedit and saving them one by one but I do have that kind of time.
Any ideas?
It's going to be a long couple of days.
I am running a maintenance cleanup task as shown below but despite the log saying "success" it is not successful as nothing is cleaned up.
Maintenance Cleanup Task (VPS20604)
Maintenance Cleanup on Local server connection
Cleanup Database Backup files
Age: Older than 4 Weeks
Task start: 2015-07-08T16:14:24.
Task end: 2015-07-08T16:14:24.
Success
Command:EXECUTE master.dbo.xp_delete_file 0,N''C:SQLBkp'',N'''',N''2015-06-10T16:14:24''
GO
I've recently inhereted an environment in which we have over 600 storedprocedures. The documentation is either very poor or non-existant andam needing advice on how to determine if a stored procedure has beenused. Does SQL Server have any sort of ticker that indicates when astored procedure was last used?Thanks and any additional information or experience would be greatlyappreciated.
View 2 Replies View Relatedhi everyone,
can any one suggest me how to clean the 'REPLDATA' folder created during snapshot replication. i want to automate the process.
can anyone suggest any undocumented sp like
'sp_MSDelete_publisherdb_trans'
thanks
I have few questions
1.Input column has dates in this format.
90Q1(this is 1990,march)
90Q2(this is 1990 june)
Is there a way to convert 90Q1 to 1990 march?
2.Patient Name column has to be split into Last name,First Name, Middle Name
Can this be done??
3.For one column i have _9 at the end of each ID
How do i remove _9??Is there a way to remove?
Please let me know
Hi all,
I'm trying delete metadata of a sql 2005 sp1 subscriber from a sql 2005 sp1 merge publication, but is not working, the "retention" parameter for the publication is 999 and this is the code I'm using:
declare @num_genhistory_rows int,
@num_contents_rows int,
@num_tombstone_rows int
declare @retcode smallint
--select count(*) from msmerge_contents
-- records before 2,633,848
exec @retcode = sys.sp_mergemetadataretentioncleanup @num_genhistory_rows OUTPUT , @num_contents_rows OUTPUT , @num_tombstone_rows OUTPUT
select retcode =@retcode
select num_genhistory_rows =@num_genhistory_rows
select num_contents_rows=@num_contents_rows
select num_tombstone_rows=@num_tombstone_rows
--select count(*) from msmerge_contents
-- records after 2,633,8
Results :
retcode
0
num_genhistory_rows
0
num_contents_rows
0
num_tombstone_rows
0
Has omebody any idea why this is not working ?
I did check "sp_mergemetadataretentioncleanup " and I note that is using a function to calculate the limit date, but I could not testing because it give me the below error :
declare @curdate datetime, @cutoffdate datetime
select @curdate = getdate()
select @cutoffdate = null
-- find max retention of all pubs the article belongs to.
-- add some safety margin to compensate for different clock speeds
select @cutoffdate = min(sys.fn_subtract_units_from_date(isnull(retention,0), retention_period_unit, @curdate))
from dbo.sysmergepublications where
pubid in (select pubid from dbo.sysmergearticles where nickname = 5088000)
select @cutoffdate
and this is the message error:
Msg 4121, Level 16, State 1, Line 7
Cannot find either column "sys" or the user-defined function or aggregate "sys.fn_subtract_units_from_date", or the name is ambiguous.
I looked this function but I didn't find it.
any help will be appreciated !