All Questions
Tagged with sql-server-2008-r2 stored-procedures
64 questions
5
votes
4
answers
7k
views
How to get the name of the database a stored procedure is executed in within that stored procedure while it's executing?
Weird question?...maybe, but I have a need. :)
I have a stored procedure that I want to use universally in any database.
The stored procedure generates some dynamic SQL and then executes that SQL in ...
- 39.5k
1
vote
3
answers
234
views
Is it a parameter sniffing problem?
I have a stored procedure which is called by Console Application (C#), the sp do nothing much just checking the existing of the record and update it:
ALTER PROCEDURE [dbo].[sp_Update_StaffTable]
...
- 13
1
vote
1
answer
7k
views
Need to filter SQL database data by time interval (1 sec, 1Min, 10 Min,1Hour, 2Hour) [closed]
I want filter SQL database data by time interval (1 sec, 1Min, 10 Min,1Hour, 2Hour)
I want to fetch data from "ProDataTable" by in between Start Date and End Date with Interval of (1 Sec, 1Min, 10 Min,...
0
votes
3
answers
1k
views
Filling Empty Values when Calculating Standard Deviation
I am currently trying to calculate the standard deviation of the amount of products we have sold at our company over the past 12 months.
I have a table that contains every date over the past year and ...
- 123
1
vote
2
answers
70
views
Queries gaining by adding a new index
How would i be able to find out which queries would gain by adding a new index.
Ex: Lets assume i add a new NCI on a table , how would i be able to find out which queries would benefit from adding ...
user117729
4
votes
2
answers
1k
views
SQL Server Replication - Can destination db have stored procedures that source db lacks?
I'm a developer with a very basic question, but I can't see anything conclusive on MSDN. I'm developing a web application that uses a SQL server 2008 R2 back end. According to this MSDN page, SQL ...
- 143
3
votes
3
answers
1k
views
tSQL Stored Procedure with CTE; CASE statement in WHERE slowing run
I have a user-defined Stored Procedure
When calling without passing explicit values, I want to just pass all Locations (nvarchar(50)), which is the primary key'd field of a table: Monitor_Locations (...
- 968
4
votes
6
answers
42k
views
Passing comma separated values to parameters in stored procedure
I have a requirement to retrieve data from a table based on the comma separated values sent in the parameters for stored procedure. As of now I have made the code work for one single value not sure ...
- 1,033
1
vote
1
answer
723
views
Use stored procedure to get minimum value from column and decrement it and other values
I'm trying to understand how the following algorithm could be completed in SQL 2008 R2.
I have a table that contains production data. The table uses a "rolling" scheduling method, meaning that orders ...
- 123
4
votes
2
answers
7k
views
Query to find plan details for a specific stored procedure, searching by name
I'm trying to write a query to find information about query stats and the query plan for a specific stored procedure, but I can't manage to find the right DMV or query to look for a specific stored ...
- 983
2
votes
1
answer
1k
views
Wrong Last Execution Time sys.dm_exec_procedure_stats
I am running the following query it shows a Stored Proc that I know ran on Mon 6/6/2016 8:45 AM but the below query shows that the last execution time was 2016-05-19 12:09:23.197. Is there something I ...
- 241
0
votes
1
answer
438
views
Organizing Stored Procs and Views
I have a database with 112 stored procs, 7 views and 88 tables.
The problem is over time I have created tables and stored procs as I needed without taking the time to think about what I might be ...
- 241
4
votes
1
answer
421
views
Read/Write Deadlock in SQL Server
We are facing deadlocks when a stored procedure is concurrently executed. We are using Microsoft SQL Server 2008 R2, concurrent execution is done with java executing multiple request of the stored ...
- 41
0
votes
1
answer
181
views
Create a temp table that consolidates start and end dates plus generates a uniqueid for each consolidated record
I have a table called projecthistory,
it is actually a view so I could take advantage of the Rank function to get a unique record number.
From this view I need to create a temp table that has a ...
- 11
1
vote
2
answers
2k
views
Need Script to find SP Invoked in SQL
I'm unable to track a stored procedure that is executing every morning 7 AM. Is there is any script to check that which job invokes that particular SP?
4
votes
2
answers
3k
views
Stored procedure returns -4 after catching exception
In a stored procedure, I am finding that if it successfully inserts a record, it returns 0. If it fails on a primary key violation (which is caught and ignored), it returns -4. Where is the -4 coming ...
- 143
4
votes
2
answers
3k
views
Timeout expired SqlException for complex Procedure
I use SQL Server 2008 R2 and ASP.NET 4.5.
Sometimes, I get this error when I executed a complex procedure:
System.Data.SqlClient.SqlException
Timeout expired. The timeout period elapsed prior to ...
- 155
6
votes
1
answer
8k
views
sys.dm_exec_procedure_stats missing history?
My boss would like me to find which (of approx 400+) stored procedures are not being used by our website any more. I'm found and reworked a script to give me the details of most recent executions, ...
- 161
5
votes
1
answer
539
views
sys.dm_exec_procedure_stats DMV contents contradict BOL
I am doing some analysis of long running stored procedures and have come across something of an anomaly, according to books online sys.dm_exec_procedure_stats should only display one row per cached ...
- 678
0
votes
1
answer
111
views
How to send an email with embedded values
I need to do a stored procedure that sends an email when a value reaches 100 with this wording in the body: "100 daily file downloaded from the user:USERID" where USERID is the user that reached 100.
...
4
votes
1
answer
3k
views
Is it possible to validate more than one parameter simultaneously into a Stored Procedure in sql?
On Microsoft SQL Server 2008 I realized a stored procedure which contains more than 20 parameters, in this SP I update or insert data into table which does not accept null values.
I need to evaluate ...
10
votes
3
answers
11k
views
Who created a procedure? (SQL Server 2008 R2 )
Is there a way to list who created certain procure in SQL Server 2008?
In this SO link there are some answers but from 6yrs ago.
I know it's a best pratice to write your name and date, and then ...
- 7,484
1
vote
1
answer
2k
views
Why RECOMPILE doesn't fix parameter sniffing, but local variables do?
A developer was asking me about why his procedure wasn't running, but the query was ok.
When I run the query, it was fast.But running the procedure, it was executing forever.
What I did was to add ...
- 7,484
2
votes
1
answer
2k
views
stored procedure not showing a result
If I run the query outside the stored procedure, I get results, but I only get the column names as the result after executing the query - even though return value is 0. Any help would be appreciated.
...
- 23
9
votes
1
answer
21k
views
How to output more than 4000 characters in sqlcmd
I've made a stored procedure restoredatabase that returns code to restore a database. The output script looks like this:
restore database [DB] from disk='F:\FULL1.bak' with norecovery
restore ...
- 405
7
votes
2
answers
651
views
Unexpected Implicit conversion in a procedure
I have a procedure like this (simplified):
CREATE PROCEDURE test @userName VARCHAR(64)
SELECT *
FROM member M
INNER JOIN order O
ON M.MemberId=O.MemberId
WHERE M.Username = @userName
There is a ...
- 1,550
2
votes
2
answers
8k
views
Insert/Update Stored Procedure
My database model is quite simple. I have the following tables: Monitor, Computer, and Monitor_Computer_Map as my mapping table for a many-to-many relationship between the other two tables.
I have a ...
- 145
1
vote
1
answer
108
views
Executed a Stored Procedure in the Production Server and the following warning message is shown [closed]
I executed a stored procedure on the production server; and though it shows that query has been successfully completed it doesn't allow me to close SQL Server Management Studio and the below warning ...
- 1,505
1
vote
2
answers
2k
views
Reviewing Stored Procedure template for handling transaction?
I've prepare a template for SQL Server stored procedures which will have Insert, delete, Update transaction.
I have a stored procedure to log error in catch block in a table.
How can I improve my ...
- 229
2
votes
4
answers
4k
views
Saving a variable globally in SQL server it self and use it when required
We are using ENCRYPTBYPASSPHRASE to encrypt some columns of some database. To decrypt the values every time we need to pass passphrase with DECRYPTBYPASSPHRASE.
So I planned to create a stored ...
- 3,131
6
votes
3
answers
71k
views
Run multiple stored procedures in sequence
I have a stored procedure written in T-SQL (SQL Server 2008 R2). It is a master procedure that essentially call multiple other subprocedures in sequence. The call and error handling is identical for ...
user48112
0
votes
2
answers
71
views
Triggers not reflecting on procedure
I have a trigger
create trigger flightinput2 on reservation
after insert
as
begin
update reservation
set Departure_Airport='LDN',
Arrival_Airport='KUL'
where Flight_Code='EM0088'
end
...
- 9
0
votes
3
answers
2k
views
List all tables that a specific login has permissions [duplicate]
This might be repetitive, but I haven't found a solution that serves my purpose 100%. What I'd like to achieve is to have a script (or SP) that'll list all tables that a user has permissions on. Any ...
- 3
3
votes
2
answers
2k
views
SQL Server Login to access tables of three different database from single database
I have created a login in SQL Server 2008 R2 and given access to run stored procedures in that database only. But this stored procedure is actually accessing data from other two databases to which ...
- 135
10
votes
1
answer
223
views
Giving server-role to login breaks stored procedures
I have a login called user1. user1 can execute stored procedure [Test].[SP1]
EXEC [Test].[TestSortation]
EXEC [TestSortation]
Both of the above commands work.
Now if I give the user1 the server ...
- 285
0
votes
1
answer
650
views
Table Valued Parameter not pulling the correct data for my sp?
http://sqlfiddle.com/#!3/b2643/5/1
http://sqlfiddle.com/#!3/b2643/5/0
This link above has the sample database tables I created on SQLfiddle.
I have 3 main tables in my stored procedure:
...
- 655
0
votes
3
answers
3k
views
SQL Log Messages - FILESTREAM and xp_cmdshell
I was perusing the SQL logs today and found the following:
FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.
Configuration option '...
- 1,385
3
votes
1
answer
643
views
Can comments in stored procedures cause problems
We're running a webserver that calls stored procedures to compute complex queries. Using sp_who2, we've discovered a peculiar issue where an abnormal number of processes would be stuck on SELECT state ...
- 275
1
vote
2
answers
99
views
Known vulnerabilities for when a guest executes a stored procedure on SQL Server
I have a production database with crucial business information. Meanwhile, my company is involved in a project with some other company and we have to exchange some information on a regular basis. How ...
- 51
3
votes
1
answer
4k
views
Searching all Stored Procedures for an update of a specific column
I'm looking at an audit table created by a trigger, and we were smart enough to save the SUSER_NAME(); we have a column called LastUpdateRef, but not all old code has been updated to use it (it's a ...
- 577
1
vote
1
answer
625
views
Raise error from Stored PROC in SQL Server PDW
I need to do some validation inside a stored procedure before I continue processing but SQL Server PDW (SQL Server 2008 R2) does not support RAISEERROR inside stored procedures.
Is there any other ...
- 337
9
votes
1
answer
15k
views
Rights to execute stored procedure, but not read underlying tables directly
I have a stored procedure that contains lots of different columns from many different tables; I want to give 20 users access to the results of the stored procedure via Excel.
Is it possible to make ...
- 219
1
vote
1
answer
518
views
Cannot grant execute to stored procedure [closed]
When I try to GRANT EXECUTE ON SP_NAME TO [DOMAIN\USER_NAME] on my database, I'm getting the error message below:
Msg 8115, Level 16, State 1, Procedure grant_safety, Line 5
Arithmetic overflow error ...
- 399
4
votes
1
answer
14k
views
Stored Procedure which accept xml file as input
<?xml version="1.0"?>
<catalog>
<book id="bk101">
<author>Gambardella, Matthew</author>
<title>XML Developer's Guide</title>
<genre>...
nilesh888
2
votes
1
answer
3k
views
Counting the number of calls of a stored procedure
I have a CLR sp in SQL Server 2008 R2, and I want to count how often it is called, in order to create a statistic on data quality. (The sp allows manual correction of data).
How can I let a counter ...
- 152
1
vote
3
answers
6k
views
Convert all ntext columns to nvarchar(max)
I'm trying to create a stored procedure to convert all ntext columns in my database to nvarchar(max).
This is the code
ALTER PROCEDURE [dbo].[usp_SL_ConvertNtextToNvarchar]
AS
BEGIN
SET NOCOUNT ON;
...
- 443
2
votes
3
answers
6k
views
How can I drop a stored procedure whose name is in a variable?
Given a variable that contains a stored procedure name:
DECLARE @stored_procedure_name sysname;
SET @stored_procedure_name = 'some_stored_procedure_name';
How can I drop the stored procedure?
- 227
-2
votes
1
answer
563
views
Errors keeping stored procedures through restore from backup [closed]
We are running SQL Server 2008 R2 on two identical servers. One, called LIVE, is our production environment. The other, called QA, is our dev enivornment. In order to check backup integrity and run ...
- 1,560
5
votes
2
answers
2k
views
Stored Procedure as Create Table proxy?
With SQL Server it was my understanding, I thought, that you could elevate a users permissions with specific constraints.
For example, you need a user to create a specific table object in a specific ...
- 153
5
votes
1
answer
11k
views
Error in restore database
I use this query in SQL Server 2008 R2 to restore DB1 database:
RESTORE DATABASE [DB1]
FROM DISK = N'D:\new.bak'
WITH RESTRICTED_USER, FILE = 1, NOUNLOAD, REPLACE, STATS = 10;
The following error ...
Ahmad Kazemi