All Questions
Tagged with sql-server stored-procedures
756
questions
-1
votes
1
answer
33
views
SQL Server Management Studio "Key cannot be null" error when giving a specific user permissions on Stored Procedures
I'm attempting to give a specific user execute permissions on a Stored Procedure. When I attempt to do so, I get an error:
Key cannot be null. Parameter name: key (System)"
When I attempt to ...
- 1
1
vote
1
answer
73
views
Trouble with slow stored procedure performance on 35M rows with CTE/Temp table
We recently went through a new feature release to our app that added about 25 new columns to a large table (~35 million rows), and now we're having some major query performance issues. I assume that ...
- 127
-2
votes
1
answer
84
views
Data Is Null. The Method or Property cannot be call on null values. Exception
In my application ultiple users request the API and in the API I am calling the stored Procedure via Entity Framework but some times getting exception Data Is Null. The Method or Property cannot be ...
- 23
1
vote
2
answers
116
views
Prevent users from sending an email attachment in sql server
In SQL Server, I want to restrict all users (except a few specific users) to send an email with attachment.
For example, someone import data into excel file and send it via mail using the ...
0
votes
1
answer
44
views
sql bigger parameter then specyfied - no error - just taking substring? why?
I have some sql procedure (MSSQL 2019) if relevant
Create PROCEDURE [dbo].[test] @tmp varchar(32) AS
print @tmp
why when i do
EXEC @return_value = [dbo].[test]
@tmp = N'...
- 103
1
vote
1
answer
281
views
Execution timeout exception in ASP.NET core
My problem is a quite hard to define. We have an SQL Server (version 15.0.2104.1) stored procedure that fills out our web site home page with data. When executed from SSMS it takes abount 0.5 second ...
- 25
0
votes
0
answers
179
views
Insert data into Always Encrypted column using stored procedure in MSSQL
I am facing an issue while trying to insert data into SQL table which is having encrypted columns in it.
Following is my approach.
I have created a table with three columns: [ID], [Name], [Password].
[...
- 11
3
votes
3
answers
746
views
Given that CREATE PROCEDURE must be the first statement of its batch, what is the use of Temporary Stored Procedures?
I was recently made aware of temporary stored procedures and became shocked that I've never seen them used before in my career. I was very disappointed when I remembered that CREATE PROCEDURE must be ...
- 545
1
vote
2
answers
245
views
Track CPU time used by a Stored Procedure
I need to track CPU time (not duration!) used by a stored procedure.
Procedure has many SQL statements inside it.
I've tried module_end event in Extended Events, but it does not have cpu_time field (...
- 5,827
1
vote
1
answer
498
views
Is there any benefit to WITH RECOMPILE or OPTION (RECOMPILE) in a stored procedure that uses temp tables at every stage?
Suppose that I have a stored procedure. It does not call any other stored procedures and is not called by any others, nor is it called concurrently. Every step in this stored procedure either creates ...
- 545
0
votes
1
answer
118
views
How to rename columns in an SQL-server database
My employer has the habit of giving customer specific tables a name, starting with the name of the customer. This makes it difficult to call the sp_rename stored procedure for renaming column names, ...
- 581
4
votes
1
answer
589
views
Compilations/sec high when load testing a simple procedure
I am trying to load test a simple insert stored procedure:
CREATE TABLE _test(ID BIGINT)
GO
CREATE OR ALTER PROCEDURE dbo.test_sp
AS
BEGIN
SET NOCOUNT ON;
BEGIN
INSERT INTO _test
...
- 1,533
1
vote
1
answer
165
views
SQL Server stored proc locking, serialization
Stored proc in SQL Server INSERT INTO table1 value sets A , B
Value set A has as one of its parameters: (SELECT TOP 1 cashbalance - 1 FROM table1 WHERE userid = __ ORDER BY createdUTC, ID)
Value set B ...
- 21
0
votes
1
answer
84
views
MS SQL Dynamic Pivot from Stored Procedure result
I have 3 tables as Student, Batches and StudentAttendanceRegistry. I managed to join these tables and get a desired result as following,
StudentID Name Year Month ...
1
vote
1
answer
540
views
Is it possible to add a query hint to an entire stored procedure in SQL Server 2022?
SQL Server 2022 has a new feature called Parameter Sensitive Plan Optimization. I don't want to turn it on for my entire database, but I have four stored procedures that I believe would benefit from ...
- 291