4
votes
1answer
65 views

Splitting a csv column from select query into multiple columns

I have a stored procedure SELECT [ShipWorks].[dbo].[OrderItem].[Code] AS Code, CASE WHEN (LEN(ISNULL(MAX([ShipWorks].[dbo].[OrderItem].[Location]),'')) = 1) THEN ...
2
votes
0answers
57 views

Stored Procedure slow after weekend

When I come to work on monday some stored procedures take several minutes to execute and STATISTICS IO shows a lot of PHYSICAL READS and READ AHEADS going on. However, after running the procedures ...
-2
votes
0answers
68 views

Writing a Condition in a Stored Procedure Using If Exists

I have a stored procedure which is working. I want to check two condition in my stored procedure: If I found the same record in database it should not save the Record, it will return 1. If I found ...
0
votes
0answers
33 views

Can a stored procedure OUTPUT parameter return a table as output?

I would like to know whether we can use a stored procedure's OUTPUT parameter to a return table as result set? I would appreciate any practical example using it OUTPUT parameters in stored ...
0
votes
1answer
253 views

Create and fill temp table

I have a stored procedure that selects some data and returns it. Now I also want to add a temp table, fill it with data and return it with the stored procedure. I define it as follows: create table ...
0
votes
1answer
40 views

AWS RDS MSSQL Extremely Slow compared to EC2 with MSSQL

Background - I built a tool which grabs a lot of data from multiple third party APIs and stores it in a MSSQL DB. Then, I built a visualization to display this data in tabular and chart format. ...
0
votes
1answer
54 views

How to deal with async calls to a stored procedure that updates data

I have asynchronous calls from Entity Framework to a stored procedure. The stored procedure selects data to a temp table and later on, it updates the real table. This works fine for sync calls. The ...
1
vote
1answer
42 views

Table Variable and Database Scope

I am a junior DBA, and I am still confused by database scope. Now I am learning about local/global temp table and table variables. I saw an example as following: declare @T1 as table ( col1 ...
1
vote
4answers
100 views

Execute a Stored Procedure Daily

I am building a Web Application which is attached to a Database which is in a Web Server(which is at unknown remote location). I want to execute a Stored Procedure daily at a specific time. I searched ...
0
votes
3answers
176 views

How to use DAY/WEEK/MONTH/YEAR constant as parameter in stored procedure?

Assume I have a table looks like this: Scores { Id uniqueidentifier, ScoredAt datetime, Value int } Basically I want to create a stored procedure that works similar to this ...
0
votes
0answers
36 views

how to insert into multiple columns from one column in sql [duplicate]

suppose i have a column called [SpendPlanned] and [SpendGME] with data corresponding to its monthid column in such a fashion below say in 'x' table: i have only two columns[SpendPlanned] and ...
0
votes
1answer
49 views

Create SP for inserting data from a table to a table

I have a table that has 1 record where one field may have 2 values separated by commas (in that cases I have to create 2 records with those values separated in 2 row and the other field values are the ...
2
votes
2answers
74 views

how to create backup for stored procedure,trigger and functions of a database in sql server 2008?

I'm having 2 databases in 2 different servers(.246 and .252). I need to create backup for all stored procedures, triggers and function from 252 server and restore it in 246 server.database name in ...
1
vote
2answers
131 views

multiple select query for the same tables with union [closed]

Using below query i want to find out the top number of students from each catagory(SC,ST,OBC,etc) on plus2percentage basis. eg i want top 3 students from SC catagory, top 2 from ST and Top3 from OBC. ...
1
vote
0answers
69 views

exec sp_getapplock - Different behaviour using go or without go

we have a strange behaviour while using sp_getapplock (we tried to use this to solve a problem with a deadlock involving some insert while another session was trying to execute a ALTER SWITCH ...
1
vote
2answers
82 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 ...
4
votes
1answer
125 views

Is there a performance consideration if stored procedures are in a separate database than the source data?

This applies to a SQL Server 2012 instance. We are considering structuring things to where we have a database that contains our source data, and then separate database (on the same instance) that ...
0
votes
1answer
61 views

Error updating zoned field from SQL Server stored procedure linked server to iSeries

I'm trying to update a table on an iSeries (AS400) within a SQL Server stored procedure using linked server, but it is failing when trying to update a column defined as zoned. In my stored ...
1
vote
1answer
200 views

Creating an SSIS package that uses a stored procedure

We're trying to make an SSIS package where it'll launch a stored procedure and capture the contents in a flat file. This will have to run every night, and the new file should overwrite the existing ...
0
votes
1answer
67 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: ...
1
vote
2answers
69 views

Is outputting to both a result set and a temporary table possible?

I have a stored procedure that I need to both output the result set to the user and use a piece of information in the result set to possibly output other result sets. Here is a basic schema of what I ...
1
vote
0answers
64 views

user rights validation inside stored procedure?

You have an application with a Users table, and the Users table has various columns to indicate what rights a User has within the application. Ignoring for the purposes of this question what should ...
1
vote
0answers
87 views

Using #temp to work around Reporting Services blocking issue

Having utterly failed to resolve why a perfectly acceptable but admitidly large stored procedure was suffering the blocking process lck_m_s when called via a report in SQL Reporting Services I have ...
0
votes
1answer
353 views

Execute stored procedure in another server from sql agent job

I am trying execute stored procedure in remote server in sql agent job using sqlserver 2008 r2 select * from openquery([1.1.1.1],'exec [DB_Name].[dbo].[sp_ProcName]') Or EXEC ...
0
votes
1answer
61 views

Is this application creating stored procedures on the fly? [duplicate]

We are running a transaction program that uses MS SQL Server. Lately as our number of users and locations have grown, speed/response seems to be an issue. When looking at *sys.dm_exec_sql_text*, I ...
2
votes
2answers
302 views

How to Log Debug Information About a Stored Procedure

We have a group of fairly complex stored procedures that process a transaction queue on a pretty much round the clock schedule. Currently these scripts use a number of print statements to document ...
1
vote
1answer
267 views

Is there anyway to make this stored procedure thread safe?

I have a stored procedure that deletes X rows. This procedure is called by a Job Scheduler (Not SQL Server Agent) from one of three Azure Instances. The deletion task is run every 30 minutes from each ...
0
votes
2answers
69 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 ...
2
votes
1answer
103 views

Do SPROCs have a “signature”, and how is that defined?

I have a stored procedure which is called by a third-party tool. I believe it to be using ODBC, but it is essentially a black box. The DBMS is Sql Server 2012. When the stored procedure is a simple ...
5
votes
1answer
198 views

How to avoid a race condition with cross db proc calls? and other concerns

I am not even sure this question is necessary but I am curious to know everyone's thoughts. I have two databases on the same server, dbFoo, dbBar. dbFoo has the following table please note this is a ...
0
votes
1answer
43 views

An SQL Server 2008 R2 based program fails when updating stored procedures?

I'm trying to update MIP software and one of the "companies" fails while it is updating a stored procedure. Is there any place in SQL Server 2008 R2 / Windows where I could see an error message ...
2
votes
1answer
27 views

Running members of a job subset at different intervals

So I'm wanting to batch a number of stored procedures into one job and would like a number of the procedures to run on a schedule different than that of the main job without setting up a new job for ...
4
votes
1answer
331 views

Is ASYNC_NETWORK_IO wait type anything to worry about?

In looking at the list of sprocs that take long time to execute, one stands out as causing the most wait. However, most of that wait (81%) is ASYNC_NETWORK_IO and I know why: the sproc transfers ...
0
votes
1answer
97 views

SQL Server Stored Procedure - Does Raiserror affect performance

Does RAISERROR in a SQL Server stored procedure affect performance? Would I be better off just having a try-catch wrapper around the whole procedure and just catch the errors in the database access ...
0
votes
1answer
174 views

Memory is not released after the execution stored procedure followed by a process end

I am running a stored procedure in query builder and closing query builder. However, the memory allocated doesn't get released. The stored procedure uses temp tables and drops them immediately after ...
1
vote
1answer
157 views

How to run stored procedure in another database on the same server using a different login?

I have 2 databases (A and B) on the same server. Database A has user ua defined, and database B has user ub defined. I have a stored procedure in A, running as ua, that needs to run a stored ...
0
votes
3answers
575 views

SQL Server, how to execute a job against all databases on server?

I'm trying to find the best way to execute a query against all databases on the server using SQLServerAgent or maintenance plan. I have a stored procedure that i'd like to run on these databases, but ...
0
votes
1answer
4k views

Setting user permissions for different SQL Server schemas

I need to limit access to a particular user, but they still need to be able to see the data in tables owned by dbo. I'm trying to do the following: dbo schema functions as it would normally, has ...
1
vote
1answer
236 views

Cannot grant execute to stored procedure

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 ...
0
votes
1answer
920 views

Row locking within stored procedure [duplicate]

I have a SQL Server Stored Procedure for getting the next number in sequence stored in a configuration table: declare @val as int SELECT @val = LongValue from tblSystem WHERE [Key] = @Key; SET @val ...
1
vote
2answers
72 views

Fixed database paths in stored procedures/views - is there a smoother way of doing this?

Our developers love to create lots of different stored procedures/views, which extracts data from lots of different databases on different servers. Every time we migrate to a new database server, ...
4
votes
5answers
1k views

How can I prove that the following T-SQL is bad for performance?

I have recently inherited a codebase with a large amount of Stored Procedures. The system they are supporting is encountering numerous performance problems which I am looking in to. A number of the ...
0
votes
0answers
20 views

How can I prove that the following T-SQL is bad for performance? [duplicate]

I have recently inherited a codebase with a large amount of stored procedures. The system they are supporting is encountering numerous performance problems which I am looking in to. A number of the ...
4
votes
1answer
190 views

Plan cache memory: parameterized SQL vs stored procedures

In making a case to disallow parameterized SQL in my company's development environment the lead developer related a story about how the last time they used parameterized SQL the server had major ...
-3
votes
1answer
91 views

Sproc - Adding columns with matching ID column [closed]

I currently have a sproc that is used for reporting on a table similar to the one below: ID Weight QTY OtherData 1 20 12 1234 1 44 4 1234 ...
1
vote
1answer
119 views

Stored procedure to validate xml content against dtd

I have a stored procedure which creates xml content and then is stored to a xml file. Is it possible to validate the generated xml content against a dtd file? Thank you in advance!
8
votes
2answers
920 views

When does sp_executesql refresh the query plan?

You'll have to forgive my naivety as I'm not a DBA but my understanding is that over time the statistics of a database change and a stored procedure must be recompiled to keep the query plan up to ...
1
vote
3answers
568 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?
1
vote
1answer
577 views

How to insert in table from remote stored procedure without creating a distributed transaction?

How can I insert in a local table from a remote stored procedure without creating a distributed transaction? I want to run a stored procedure on a remote server and use the output as the source for an ...
3
votes
6answers
6k views

Don't use a transaction for Stored Procedure

I have a stored procedure that runs a few commands. I don't want these commands to be wrapped in the transaction of the stored procedure. If the 4th command fails, I want the 1st, 2nd and 3rd ones to ...