All Questions

Filter by
Sorted by
Tagged with
0
votes
1answer
156 views

Fetch data from SQL Server table of the ERP DB

I have pretty old ERP system running on SQL Server 2005 (about 20GB database. Largest able has 2 million rows). Unfortunately, the ERP can't run on SQL Server 2008 or newer. I have a huge amount of ...
-2
votes
3answers
238 views

How to calculate sum of employees late mins and Insert into another table using stored procedure

Employees daily late minutes stored in one table(Attndate) and I want to calculate sum of Individual employees late mins then insert into another table(LateMuster) using sql server stored procedure ...
0
votes
1answer
254 views

SQL 2005 Unused procedures

I'm trying to get a list of unused procedures across the whole SQL server. I've accomplished getting a list of ALL procedures and now I'm using the following script to identify which procedures have ...
1
vote
1answer
250 views

sys.dm_exec_query_stats - what causes total_worker_time to be reset? How to work around that?

I am monitoring stored procedure executions, mainly the execution times and the CPU usage, using sys.dm_exec_query_stats in the script below. SET NOCOUNT ON; declare @lastex datetime select @lastex =...
13
votes
5answers
12k views

In SQL Server, is there a way to determine the values of the parameters passed to an executing stored procedure

One way to determine the executing stored procedure is to use "dynamic management" methods, like so: SELECT sqlText.Text, req.* FROM sys.dm_exec_requests req OUTER APPLY sys....
2
votes
1answer
774 views

stored procedure can select and update tables in other databases - minimal permissions granted

I recently had to make the stored procedure spPartyOrderAllocation_AllocateItems to select and update tables in other databases, while keeping the pemissions levels to the minimum. This was ...
1
vote
1answer
7k views

all tables used by a stored procedure and all sps that use a particular table - recursive cte

I am working on a script that gives me a list of tables that use a specific stored procedure. It also works on all tables used by a stored procedure. on the example below, all stored procedures that ...
1
vote
0answers
371 views

How to create a stored procedure to update database after table edit

In a C# website application with a database, I set a gridView table so that I could edit the values in the table by row, but it appears that I need a stored procedure to do that. Below is the stored ...
3
votes
1answer
531 views

Tracking down unused Views and Stored Procedures

I am working on a SQL Server 2005 database that has two Microsoft Access ADP frontends. This database needs a lot of love and attention which I am attempting to give it. There are lots of stored ...
3
votes
2answers
1k views

Getting Machine name from where stored procedure was last executed

Is there any way to find out machine IP or machine name from where a particular stored procedure is executed in SQL Server 2005? We have a client server architecture with .NET as a front end and SQL ...
1
vote
2answers
456 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 ...
16
votes
1answer
21k views

Is ASYNC_NETWORK_IO wait type anything to worry about?

In looking at the list of stored procedures 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 stored ...
7
votes
1answer
8k 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 ...
4
votes
4answers
13k views

How can I search the full text of a stored procedure for a value?

I use the following script to search the text of all stored procedures when I want to find specific values. SELECT ROUTINE_NAME, ROUTINE_TYPE FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION ...
41
votes
5answers
44k views

Can I launch a stored procedure and immediately return without waiting for it to finish?

We have a stored procedure that users can run manually to get some updated numbers for a report that's used constantly throughout the day. I have a second stored procedure that should be run after ...
1
vote
1answer
2k views

How to split the comma separated list in the stored procedure

I am working on small module in which we pass the column name as comma-separated string to a stored procedure. In the stored procedure, we want to separate the column names out.
5
votes
1answer
9k views

Who changed/modified stored procedure in SSMS 2005 or 2008? [duplicate]

Possible Duplicate: Determining how a schema change occurred? I have a stored procedure that was modified late last night and just spent the majority of the morning tracing issues back to this ...
6
votes
2answers
154 views

Will a SP which writes records fail if accessed by a db_datareader role?

I have a stored procedure which is accessed remotely by an app which is running on some kind of Java/WebSphere type platform. It takes one argument, returns some data and that's that. Recently there ...
4
votes
3answers
1k views

What should I consider when deciding between passing a comma-delimited string to a stored procedure instead of calling it individually per record?

I have to run the same stored procedure for a lot of records (hundreds or thousands, depending on user selection). The procedure is fairly basic: insert a record in two separate tables with some ...
1
vote
1answer
168 views

transactional Replication of millions of updates

I have standard transactional replication setup from 2005 to 4 2008 subscribers. One of the tables that is replicated is updated several times from an agent job resulting in 6 million updates ...
6
votes
1answer
1k views

SQL Server database : moving from 2008 to 2005 - encrypted procedure issue

I could normally use generate scripts / DTS to move data and tables etc. However one of the databases has a large amount of encrypted procedures that I can't script to move. Neither do I have older ...
6
votes
2answers
1k views

Is there a way to setup a user to run SPs, but not access the underlying data tables?

We have two database instances on two separate server boxes that are linked. The website user needs to be able to run stored procedures on DatabaseA which query DatabaseB tables, however it should not ...
1
vote
1answer
397 views

What can cause an UPDATE statement to execute endless? [closed]

I have a stored-procedure which is executed at 7 o'clock in the morning that updates the database because new records were imported at night. Today it started but never endet. So i've tried to run ...
14
votes
2answers
35k views

Why does a query run slower in a Stored Procedure than in the Query window?

I have a complex query which runs in 2 seconds in the query window, but about 5 minutes as a Stored Procedure. Why is it taking so much longer to run as a stored procedure? Here's what my query looks ...
2
votes
1answer
880 views

high 'total_worker_time' for stored proc using OPENQUERY in SQL Server 2005

I have a stored procedure in SQL Server 2005 (SP2) which contains a single query like the following (simplified for clarity) SELECT * FROM OPENQUERY(MYODBC, 'SELECT * FROM MyTable WHERE Option = ''Y''...
1
vote
3answers
5k views

BACKUP failed to complete - with spaces, for no reason

Every day my database backup fails for some databases and does fine for others. I'm calling the backup through a Stored Procedure that runs scheduled in a job. Tonight I'm going to run the procedure ...
3
votes
1answer
627 views

Possible to add KEEPFIXED PLAN option to reduce the recompile in SQL Server 2005 in Production Env?

I'm not sure if it is good to enable the KEEPFIXED PLAN to reduce the impact of recompilation in the Production Env? I'd know if we may gain performance after that or on the other hand it will be ...
4
votes
3answers
8k views

SQL Server: How to call stored procedure without schema name?

SQL Server 2005std/2008web edition db=user=schema=owner='John' I'm moving website from one server to another. This piece of code works well on old server with SQL Server 2005. Dim sqlCmdVehicle ...
3
votes
2answers
1k views

SQL 2005- stored procedure performance troubleshooting

I am investigating performance issues for a stored procedure on SQL 2005 server. This is how I intend to do it: Analyze the stored procedure for any bad TSQL practices (like count * etc) Run it ...
3
votes
2answers
2k views

update stored proc ony fields that are passed to the proc and leaving others alone

i have a stored proc that does a simple update (see below). i want to update only fields passed and not the ones that are null. that is to say if i dont get a parm value for somethign i dont want ...
7
votes
2answers
740 views

Most efficient way to return multiple aggregates in a single stored proc?

What is the best or most efficient way to get multiple aggregate result values? Basically I have an email app and wanted to get all the numbers of messages for each type of folder (inbox, sent, ...
11
votes
3answers
3k views

What is the best method to add error handling in SQL 2005 stored procs?

What’s a good way to make stored procs robust enough that they can scale very well and also contain error handling? Additionally, whats the best way to handle multiple error scenarios in a stored ...
3
votes
1answer
298 views

What's the best paradigm or design pattern for exception handling in a mission-critical DB driven web application?

I want to design a bullet-proof, fault tolerant, DB driven web application and was wondering on how to architect it. the system will have a asp.net UI, web services middle tier and SQL2005 back end. ...
9
votes
2answers
3k views

How to migrate SQL Server Stored Procedures using temporary tables or table variables to Oracle?

C# developer encouraged by management to write SQL Server stored procedures often produce procedures like this create table #t1 (...); insert into #t1 Select ... from table_a where ...; insert into #...
3
votes
4answers
537 views

Help improving SQL Server 2005 query execution time

One of my stored procedure has long execution time (average around 4 to 7 minutes). I want to make it run faster. I am looking at the execution plan and two things are contributing most of the cost. ...