All Questions
Tagged with sql-server-2008-r2 stored-procedures
64 questions
5
votes
2
answers
4k
views
Is it possible to determine what part of a stored procedure is currently executing?
Say I have a stored procedure like this (assume that the table schemas are fine):
CREATE PROCEDURE p_MyProc
AS
INSERT MyTable SELECT Col1 FROM Table1
INSERT MyTable SELECT Col2 FROM Table2
INSERT ...
- 1,217
2
votes
1
answer
2k
views
Add user to Server Role in a transaction
Situation
One webapp responsible for creating other webapps using SQL Server 2008 R2 Stored Procedures. One of the steps in creating another webapp is creating a new database, a login/user, assigning ...
- 153
36
votes
4
answers
6k
views
Still wrong to start the name of a user stored procedure with sp_?
One of my co-workers named a stored procedure in our SQL Server 2008 R2 database sp_something. When I saw this, I immediately thought: "That is WRONG!" and started searching my bookmarks for this ...
user5147
19
votes
2
answers
40k
views
Disabling Checking of Schema On Function/Stored Procedure Creation
I'm trying to automate the process that executes changes to the SQL Server 2008 R2 database. The process I put in place drops and recreates my stored procedures and functions, as well as run scripts ...
- 597
2
votes
1
answer
4k
views
How to schedule a weekly backup of a database only if it has been changed since last backup
I´m new to SQL Server administration.
I´d like to create automatically backup database every Sunday night, but only if there was any change in previous week (if not, backup is unnecessary).
Is there ...
- 185
12
votes
2
answers
4k
views
Documenting a giant web of inter-related stored procedures in an MS SQL database: What tool or format? [duplicate]
I hope this is a question with a shorter answer than "Read a 1000 page book", but then, if that's the real situation, then hit me with it.
I am not a real DBA, I'm a software developer who is ...
- 1,087
5
votes
1
answer
545
views
can sql generate a good plan for this procedure?
I have read that procs that do not always do the same thing per se, will not always have a good plan generated. That is (correct me if I'm wrong), if I have a proc that if the day is even it reads ...
- 153
10
votes
1
answer
33k
views
Stored Procedure to return dynamically created table data
Quick back story, we are working with an outside vendor that has a survey system. The system is not necessarily designed the best in that when you create a new survey and the system creates a new ...
- 9,716
3
votes
2
answers
2k
views
Stored procedure compilation blocking due to many parameters to sp_executesql
I am seeing blocking due to locks on compiling a particular stored procedure (as described in KB 263889). Basically, several processes are waiting with LCK_M_X on the same resource "TAB: 8:1044511100:...
- 139
1
vote
0
answers
778
views
Create stored procedure to select the data?
I have four tables user, shops, offer, merchant in that id is unique and present in all table
shop
------
id shops
1 shop1
1 shop2
1 shop3
user
-----
id name
1 abc
merchant
---------
...
- 21
7
votes
2
answers
4k
views
Why does the order of parameters matter for sp_trace_create?
The first batch of the following script calls stored procedure sp_trace_create with parameters in documentation order; the second batch swaps the positions of parameters @tracefile and @options:
...
- 2,358
6
votes
2
answers
2k
views
Error handling in container procedures
I'm aware of and use the techniques in this question.
In my environment we have a lot of container procedures that call subprocedures, that may call other procedures, ad infinitum.
For me personally,...
- 18k
8
votes
2
answers
10k
views
How to Determine the input and output values of stored procedures?
I have hundreds of SPs and I would like to find out:
input parameters with type
output fields with type (not output parameters)
Of course I could manually go through each one and write it down but ...
15
votes
2
answers
3k
views
Optimization: Moving variable declarations to the top of your procedure
While working on optimizing some stored procedures, I sat down with the DBA and went through some stored procedures with high blocking and/or high read/write activity.
One thing the DBA mentioned was ...
- 263