Hi,
what I am trying to discern is if there is any way of logging SP
activity on a SQL server 2k DB. Ideally I would want to log SP name,
parameters, user and time.
I found sp_monitor in MSDN but that just gives overall statistics.
Not specific enough to aid debugging.
Thanks in advance,
Finlay Macrae
Hello,I'm currently working on debugging a very large DTS package that wascreated by someone else for the purpose of importing data into mycompany's database. The data is mainly user/contact-related data forour customer base.We ran into problems when one import, of about 40,000 rows, tookupwards of six hours to complete. Many of the stored procedures usedby this package were written using XML. I've re-written many of themusing native SQL to see if that improves the performance, but I'mgetting some errors that I haven't been able to diagnose.Instead of asking about my specific errors, I'd like to know moregenerally what ways are there to debug DTS packages and storedprocedures? I'm aware of, and experienced with SQL Profiler but it'snot giving me the info I need. I need the ability to see exactly whatvalues are being passed to every call to a stored procedure fromwithin the DTS package or another stored procedure.I've used it very successfully to debug .asp, .aspx, .vb and the like,but right now I'm running it while running this huge stored procedurethat is called by the DTS package and does the lion's share of thework, including multiple updates and inserts into about 10 tables.The problem is, I see the calls to the "sub-procedures" from the mainone, but I can't see the values of any of the input or outputparameters. Instead ofInsert_Contact 'John', 'Q', 'Smith', '333-333-3333'......etc.I seeInsert_Contact @FirstName, @Initial, @LastName, @PhoneNumber......etc.My trace includes Stored Procedure events:RPC: CompletedRPC: StartingSP: StartingSP: StmtCompletedSP: StmtStartingand TSQL:Exec Prepared SQLPrepare SQLSQL: BatchCompletedSQL: StmtStartingI figured with these I would've covered the bases but I don't see anyof the parameters, which is critical for my debugging, as some of themare not being properly set.Any ideas or help would be greatly appreciated!TIA,Mike
View 4 Replies View RelatedHello All,
I want to audit the users who are using a particular store procedure.
Your solution for this problem is highly appreciated.
Thanks,
Naveen.
I have around 100 packages (all [packages run at same time) each package calls a stored Procedure once the Stored proc Execution is completed the package will write the log information into a log table, here how can i capture the CPU and Memory usage for execution of each stored proc.
View 2 Replies View RelatedI am maintaining an application where most of the business rules are inTriggers, Stored Procedures and User Defined Functions. When a bugarises, it can get very tedious to debug. Today for example, I wantedto modify a function that was being called by a trigger. The problemis that I don't want to change the function, for fear that it is beingcalled by one of the other SP's or triggers in the database (there arehundreds of them)Essentially, I need a tool that allows me to view where functions andsp's are being referenced from. At the very least, I'd like to performa "full text search" in the database objects, so that let's say I havea function named "fn_doSomething", I can search the schema for thisstring and get all the places where it appears.As you can see, I'm in the dark here. I've never worked on a systemwhere all business rules are at the database level. If you know of atool that does what I describe above, or anything else that wouldfacilitate my life, please let me know!Thanks for your help,Marc
View 5 Replies View RelatedI have a very simple piece of code (see below) which when executed sometimes takes around 7 minutes and sometimes around 3.5 hours. the difference is that during the 3.5 hours there is a lot of querying of the table being updated. But I don't know how to find out if this is the case. How can I find out whether my process is waiting (for locks or for any other reason) - is there a trace or debug facility within the tandard Microsoft Toolset which I can use.
Regards
Colin
Problem code below
===============
print 'Updating stm_brnline - Start time is ' + convert(char(25),getdate(),113)
--
update m
set m.branchpgrade = s.branchgrade
from stm_brnline m, tmp_brngrades s
where m.traddiv = s.traddiv and
m.contcode = s.contcode and
m.merchsect = s.merchsect and
m.branch = s.branchcode
--
print 'Updating stm_brnline - End time is ' + convert(char(25),getdate(),113)
Hello .
How to count how many time and who executed stored procedure using Sql agent or system tables
Hi,
i've a store procedure has a strange behavior, As soon as created has a good performance , but after some times (indeterminated) it takes more time to be execute.... (up to 70s!!!)
The thing that i've not understood was if i take the query inside to the store i execute it separtely I get result immediately... :eek:
Dropping and re-creating procedure,it become newly fast... I've just scheduled a maintenance plan with index optimization and integrity check, but this seems doesn't work ...
Any idea?
Thanks .
My server (SQL 2005 SP2) typically runs with a procedure cache usage of about 92% or higher... lately it seems like at some point in time during the day it just drops to anywhere between 50% and 65%... with this comes horrible server performance and many snowball effects. If I clear the procedure cache it will go up only about 10% for a minute or two. The only way I can get it to recover completely seems to be restarting the SQL service. Then it will be fine till the next incident. The database is a read only (not set to read only but no updates other than replication). and the same SPs are run over and over and over throughout the day. also did notice that the compiles of the SPs goes up drastically at this point also. not sure if this is part of the cause or part of the effect.
CPU is normal. response from anything (even sp_who) is slow.
i do not understand the way procedure cache works completely so I thought I would ask for some direction.
Any ideas where to look or where to start???
Any thing I can do to catch this when it happens would be great.
thanks a head of time.
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 there,
Could any one suggest a query which yields the list of tables and columns used in a stored procedures. I know sp_depends has similar functionality, but would like to know the T-SQL code for that..
Madhu
I would like to know if there is a penalty for Varchar variables in stored procedures if I declare them Varchar(8000) instead of Varchar(1000).
I have a lot of variables and sometimes the content will be more them 1000 characters.
Is memory only allocated for the the actual contents or for the complete declared length?
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 have a client program that writes to sql server database 10 records per second . i want to compute the CPU usage and the memory usage for the whole program or CPU usage,memory usage for the insert statement in the program .
Can anybody help me with this?
Hello, When I am seeing SQL Server 2005 Management studio Server Dashboard> I am seeing my(USERS) databases and msdb database usage is very small % of in CPU Usage(%), Logical IO Performed (%) Usage pie chart.
90% of Total cpu usage is showing for Adhoc Queries. what excatly this means in Dashboard? if application uses more than it would have shown in Database level or not?
sicerely this dashboard is good, if any one is watching daily, please advice their experiences here.
Thanks in advance. Hail SQL Server!
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???
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
I would greatly appreciate any help with this problem, as I've been digging thru every resource I can find looking for a solution with no luck.
I'm going to be monitoring a database for all SQL statements containing INSERT, DELETE, or UPDATE. I'm grabbing the user name, time, and the entire text of the query. I can already do this programmatically, no problem. The problem lies in this. When I set up a trace on SQL Server 2000 using the system stored procedures sp_trace_create, sp_trace_setfilter, etc, and set the trace to save to a trace file, I find that I must first stop the trace then close the trace before I can use fn_trace_gettable to get the information that I want. However, this is undesirable, because this database may be accessed worldwide, and stopping the trace to read the data could cause the trace to miss some users making changes. Does anyone know how that I could get my trace data into a table so that I can just run queries on that table to get my data? It's very important that I not stop the trace to do this. Thanks for your help!
JR Rickerson
Software Engineer
Infinite Software Solutions, Inc.
What are different kind of traces we can take regarding the performace and how traces can be used for improving performance ?
spatle
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
Hi,
I need to trace deadlock, one of article was mentioning “QL Server Profiler's Create
Trace Wizard to run the "Identify The Cause of a Deadlock" for SQL Server 7.0, is there any way I can do this in Sql Server 2000?
How would I go about tracing UDF performance in profiler? I'd like to
specifically know the impact of the UDF without having to dig into the
execution plan of the statement containing it. Is this possible?
I am fairly new to SQL Server. I am writing a tool in stored procedureto identify locks in a table. I have already written the basic frameworkof the SP. It will reside in master database and take two inputs. Databasename and table name. From that it will show all locks at that instanton that table of that database. If table name is omitted, then it will showlocks on all tables.I am using syslockinfo, spt_values tables and joining with SP_WHO procedureto get the table name, user name and the session id.Now what I need is to find out which SQL is causing the lock and since whenlock is being held on the table. Which tables in master database holds therequired information.TIA.Ravi
View 2 Replies View RelatedHiI want to trace all the selects/deletes/modifys whatever on a databasein an application that are performed in a seperate application.I need to look into this, any ideas?- Can triggers do this kind of thing- Can you somehow access the profiler via OLE or similiar to do this?- Anything else?TaF
View 5 Replies View RelatedHi,
I need to trace deadlock, one of article was mentioning €œQL Server Profiler's Create
Trace Wizard to run the "Identify The Cause of a Deadlock" for SQL Server 7.0, is there any way I can do this in Sql Server 2000?
Hi,
I am using sp_executesql to get some data but it is not working. Is there a way to actually see the actual statement where subsituted variables are replaced with the actual values.
SET @SQLString = N'SELECT Top(1) @Passenger_OUT = Name, @Ticket_OUT = TicketNumber , @TAN_OUT= [TAN] '
+ N'FROM dbo.Table1 WHERE 1 = 1 '
+ Case @MatchAmount When 1 Then N' and Amount = @BillingAmount ' Else N'' End
+ Case @MatchTicket When 1 Then N' and LTrim(TicketNumber) = STUFF(STUFF(@TicketNumber,Len(@TicketNumber)-@RemoveRight+1,@RemoveRight,''''),1,@RemoveLeft,'''') ' Else N'' End
+ Case @DaysDiff When 0 Then N'' Else N' and DATEDIFF(d,@BillingDate , InvoiceDate) <= @DaysDiff ' End
+ Case @MatchName When 1 Then N' and Left(Name,@CharsToMatch) = Left(@PassengerName, @CharsToMatch) ' Else N'' End ;
SET @ParmDefinition = N'@BillingAmount decimal, @TicketNumber varchar(15), @RemoveRight tinyint, @RemoveLeft tinyint, @BillingDate datetime, @DaysDiff tinyint, @CharsToMatch tinyint, @PassengerName varchar(35), @Passenger_OUT varchar(35) OUTPUT, @Ticket_OUT varchar(15) OUTPUT, @TAN_OUT varchar(25) OUTPUT';
EXECUTE sp_executesql @SQLString, @ParmDefinition,@BillingAmount=@BillingAmount, @TicketNumber=@TicketNumber, @RemoveRight=@RemoveRight, @RemoveLeft=@RemoveLeft, @BillingDate=@BillingDate, @DaysDiff=@DaysDiff , @CharsToMatch=@CharsToMatch, @PassengerName=@PassengerName, @Passenger_OUT=@Passenger_new OUTPUT, @Ticket_OUT=@Ticket_new OUTPUT, @TAN_OUT=@TAN_new OUTPUT;
Print @SQLString will gives the statement with variables and I need to see the actual data plugged in during runtime.
If I take the ouput of @SQLString and run it myself by adding variables it works fine
Thanks
Shafiq
There is a bug in one of the service packs where Profiler (7.0) only traces one server (regardless of the server you tell it to trace). Can anyone tell me how to fix this or point me to a KB article? I thought this was fixed in SQL 7 SP 3, however I'm experiencing this problem with SP3 installed.
View 1 Replies View RelatedSQL 7 profiler has an event in the Misc. category of Failed Login. It does not, or at least I cannot get it to, produce any output when a failed login occurs. Any hints?
I tried this because every week or so I get this in the error log:
Login failed for user 'Admin'.
It occurs several hundred times within a minute or so. It obviously has to be an automated process as you couldn't click a button or press a key 13 times a second.
The login does not exist as a SQL login so I can't tell which database it is trying to get at. Any suggestions gratefully received.
Simon
Hi There,
I hope someone can assist me in tracing the cause of a problem I am experiencing. I have a Web Server with ASP's querying the SQL Server 6.50.416 database. There is only one user db on this machine and yet I am running out of User Connections (current setting 2000) and memory (128MB RAM). Also, NT repeatedly experiences Stack Dumps. I have used the PRINTDMP utility to try and trace the cause of the error. The "Input Buffer" section of the Stack Dump (symptom dump) contains the following:
SELECT FK_SUB_PRODUCT_GROUPING, FK_SUB_PRODUCT_NAME FROM RESOLUTION_PRODUCT_SUB_PRODUCT WHERE FK_PRODUCT_CODE=1072 order by FK_SUB_PRODUCT_GROUPING
Is this SQL Statement the cause of the Stack Dump? Does anyone have any other ideas on what may be causing my problem. Any help would be greatly appreciated.