I've recently inhereted an environment in which we have over 600 stored
procedures. The documentation is either very poor or non-existant and
am needing advice on how to determine if a stored procedure has been
used. Does SQL Server have any sort of ticker that indicates when a
stored procedure was last used?
Thanks and any additional information or experience would be greatly
appreciated.
Hi all,
I have this stored procedure which works fine but I am unsure if I should be doing anything different to improve the overall efficiency of it or any other things that may need changing to shorten the code etc.
It takes in some parameters from an ASP page and puts them through some conditional statements.
Here it is:
CREATE PROCEDURE [imagineDB].[sp_treatment]
-- parameters
@id int = 0,
@ikeyword int = 0,
@keyword nvarchar(100) = NULL
AS
if @id <> 0 OR @ikeyword = 1
if @id <> 0 and @ikeyword = 0
begin
SET NOCOUNT ON;
SELECT id, pageTitle, description FROM ihcProcedures where id = @id order by pageTitle ASC
end
if @id = 0 and @ikeyword = 1
begin
SET NOCOUNT ON;
SELECT id, pageTitle, description FROM ihcProcedures where description like '%' + @keyword + '%' order by pageTitle ASC
end
if @id <> 0 and @ikeyword = 1
begin
SET NOCOUNT ON;
SELECT id, pageTitle, description FROM ihcProcedures where id = @id and description like '%' + @keyword + '%' order by pageTitle ASC
end
else
if @id = 0 and @ikeyword = 0
begin
SET NOCOUNT ON;
SELECT id, pageTitle, description FROM ihcProcedures order by pageTitle ASC
end
else
if @id = 0 and @ikeyword = 1
begin
SET NOCOUNT ON;
SELECT id, pageTitle, description FROM ihcProcedures where description like '%' + @keyword + '%' order by pageTitle ASC
end
thanks in advance
I want to build a spaceship with ligthspeed capabilities and I don't even know what a wrench is.
Not sure what the best forum on this site for this. Looked around but couldn't find.
When I google this... I just get a million threads about how to call stored procedures in c#.
But what I want to do in this case is call a C#.net app from a stored procedure. It will need to be backwards compatible for 2000 unfortunately. I could compile the C# app as a win-32 app and kick it off from the Stored Procedure with command line parameters. But is there a better way? What is best practice?
I have a view setup on db2 that I am accessing from our sql server. I'm trying to figure out how I could setup a view or stored procedure on the sql server to access the db2 view so the developers don't have to use the openquery statement, but still could query it like they had access to a table. In other words, I want them to be able to specify whatever columns they need to from the sql server stored procedure or view without sacrificing performance on the db2 side of things. I don't want to select every record from the db2 view or stored procedure and then filter based on that criteria.
I think I need to use a stored procedure to accomplish what I am trying to do. I would also like to prevent any type of sql injection attacks. Therefore, I didn't want to just create a long varchar variable and store the entire where clause in the varchar variable. In addition, I want the developers to be able to specify only the columns needed to be returned.
select * from openquery ([linkedServer], 'select * from catalog.schema.table where column1=''A''')
select * from openquery ([linkedServer], 'select column4 from catalog.schema.table where column1=''A'' or column2=''A'' or column3=''A''')
Any help would be greatly appreciated.
I would like to have a stored procedure executed once a week via a DTS package. The data I would like inserted into Table_2, which is the table where the DTS is being executed on, comes from a weekly dump from Oracle into a Table_1 via another DTS package.
I would like to only import data since the last import so I was thinking of my logic to be like this:
INSERT INTO Table_2
(Field1, Field2, ... , FieldN)
VALUES (SELECT Field1, Field2, ... , FieldN FROM Table_1 WHERE ThisDate > MAX(Table_2.ThatDate))
Does this make sense? Or do you all suggest a different mannger of accomplishing this?
How do you find stale stored procedures ?
In a scenario where a developer created a slight modification of a stored procedure because he was afraid of breaking something else and took the easy way out, and a few more later down the line, multiple versions of a stored proc. doing slightly different things are just laying around.
"Last used" would be useful piece of information to determine the most recent date a stored procedure was called, either by the application itself or by another stored procedure itself called by the application.
Any stored proc not used for more than say 6 months would then be identified as a candidate for clean-up.
So - short or creating - after the fact - a trigger to update the usage date upon each call - which means a lot of work and no result for the next six months, how can one go about this ?
And could this be done in SS2K8 ?
Hi,
I have 3 tables: authors, companies, and countries.
I have a stored procedure defined as:
PROC addAuthor(AuthorName, CompanyID, CountryID)
INSERT INTO authors (author_name, comp_id, country_id) VALUES( authorName, CompanyID, CountryID)
END PROC
....So the ID of the company and the country are the parameters. This makes it easy since I can just do a direct INSERT statement into the authors table.
Would it be better practice if the parameters asked for the name (not the ID's) of the company and country instead? This way we do not have to memorize the ID's for everything. So for example
PROC addAuthor(AuthorName, CompanyName, CountryName)
SELECT COUNT(*) FROM companies INTO v_numRows WHERE companies.company_name = CompanyName
IF v_numRows = 0
-- error
END IF
SELECT COUNT(*) FROM countries INTO v_numRows WHERE countries.country_name = CountryName
IF v_numRows = 0
-- error
END IF
END PROC
yeah, I might have the SQL syntax wrong up there (not entirely sure how it works for variables) but the gist of it is that I will need to include validation code within the stored procedure.
What do you guys think of this? Which method do you prefer? Is there a significant performance decrease with using the second method?
Thanks for any insights
All -
I am using SQL Server 2005 and I have an endpoint that exposes some stored procedures as web-methods in the endpoint.
One particular stored procedure I have exposed takes a long time to execute: about 10 - 15 minutes. While, it is OK, that this stored procedure takes this long, it is not desirable for the HTTP Request that executed this proc to not wait for that long.
What I want to be able to do is to call the stored procedure and have the call return immidetaly but the stored proc continues what its doing. I will call another stored proc at a later time to retrive the result of the first stored proc. The first proc will store its results in a temp table. I am thinking of using SQL Server Service Broker to achieve this.
Is there a better a way to achieve this? And how does SQL Server process the Service Broker requests, i.e., I dont want the query to be executed when the server is busy. Are there any hints that I need to give to Service Broker to be able to do this?
Thanks.
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created')
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert).
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
Hi all,
I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):
(1) /////--spTopSixAnalytes.sql--///
USE ssmsExpressDB
GO
CREATE Procedure [dbo].[spTopSixAnalytes]
AS
SET ROWCOUNT 6
SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName
FROM LabTests
ORDER BY LabTests.Result DESC
GO
(2) /////--spTopSixAnalytesEXEC.sql--//////////////
USE ssmsExpressDB
GO
EXEC spTopSixAnalytes
GO
I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class
///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance,
Scott Chang
More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
I am new to work on Sql server,
I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.
Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.
I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?
CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete]
@QQ_YYYY char(7),
@YYYYQQ char(8)
AS
begin
SET NOCOUNT ON;
select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],
[Code] ....
I have a requirement to execute an Oracle procedure from within an SQL Server procedure and vice versa.
How do I do that? Articles, code samples, etc???
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 a sub that passes values from my form to my stored procedure. The stored procedure passes back an @@IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page. Here's where I'm stuck: Public Sub InsertOrder() Conn.Open() cmd = New SqlCommand("Add_NewOrder", Conn) cmd.CommandType = CommandType.StoredProcedure ' pass customer info to stored proc cmd.Parameters.Add("@FirstName", txtFName.Text) cmd.Parameters.Add("@LastName", txtLName.Text) cmd.Parameters.Add("@AddressLine1", txtStreet.Text) cmd.Parameters.Add("@CityID", dropdown_city.SelectedValue) cmd.Parameters.Add("@Zip", intZip.Text) cmd.Parameters.Add("@EmailPrefix", txtEmailPre.Text) cmd.Parameters.Add("@EmailSuffix", txtEmailSuf.Text) cmd.Parameters.Add("@PhoneAreaCode", txtPhoneArea.Text) cmd.Parameters.Add("@PhonePrefix", txtPhonePre.Text) cmd.Parameters.Add("@PhoneSuffix", txtPhoneSuf.Text) ' pass order info to stored proc cmd.Parameters.Add("@NumberOfPeopleID", dropdown_people.SelectedValue) cmd.Parameters.Add("@BeanOptionID", dropdown_beans.SelectedValue) cmd.Parameters.Add("@TortillaOptionID", dropdown_tortilla.SelectedValue) 'Session.Add("FirstName", txtFName.Text) cmd.ExecuteNonQuery() cmd = New SqlCommand("Add_EntreeItems", Conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@CateringOrderID", get identity from previous stored proc) <------------------------- Dim li As ListItem Dim p As SqlParameter = cmd.Parameters.Add("@EntreeID", Data.SqlDbType.VarChar) For Each li In chbxl_entrees.Items If li.Selected Then p.Value = li.Value cmd.ExecuteNonQuery() End If Next Conn.Close()I want to somehow grab the @CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder) and pass that to my second stored procedure (Add_EntreeItems)
View 9 Replies View RelatedI have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure
at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT
I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT
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.
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 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 a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense.
How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how?
Thanks in advance
Has anyone encountered cases in which a proc executed by DTS has the following behavior:
1) underperforms the same proc when executed in DTS as opposed to SQL Server Managemet Studio
2) underperforms an ad-hoc version of the same query (UPDATE) executed in SQL Server Managemet Studio
What could explain this?
Obviously,
All three scenarios are executed against the same database and hit the exact same tables and indices.
Query plans show that one step, a Clustered Index Seek, consumes most of the resources (57%) and for that the estimated rows = 1 and actual rows is 10 of 1000's time higher. (~ 23000).
The DTS execution effectively never finishes even after many hours (10+)
The Stored procedure execution will finish in 6 minutes (executed after the update ad-hoc query)
The Update ad-hoc query will finish in 2 minutes
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
hi 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 !