Tag: stored-procedures

Rebuild Index Task Breaks Compliation of Stored Procedure

I have a maintenance plan that runs on my SQL Server 2008 server every morning before business hours. It was put in place a few years ago to help with some performance issues. The problem that I am seeing is that after that rebuild index finishes, there is a stored procedure in one of the […]

generate test data to perform integration tests of stored procedures

I have a legacy application asp.net web form application that accesses a sql server 2005 database without any proper data access layer. In other words, it is not very amenable to automatic integration tests using NUnit. I would like to add some stored procedures to the database and test them via automatic integration tests (ideally […]

how can I count all row and count group by on one tables

i write a SQL Code like this SELECT count(tn.[ProductName]) as ProductCount,tn.[CategoryID],tp.estbProducerID,tp.NewProducer from [tpdcn] tp,[tpdtn] tn left join [tpdcn] on tn.parentid = tpdcn.libDocumentID where tp.libdocumentID = @id group by tn.[CategoryID],tp.estbProducerID it’s show Product Count CategoryID estbProducerID 2 1 810600017 9 2 810600017 2 3 810600017 2 4 810600017 1 5 810600017 but I need more one […]

sp_replmonitorhelppublisher – “An INSERT EXEC statement cannot be nested.”

Issue: I’m getting this error on Microsoft SQL Server 2008: Msg 8164, Level 16, State 1, Procedure sp_MSload_tmp_replication_status, Line 80 An INSERT EXEC statement cannot be nested. Background: I’m trying to programmatically monitor replication status in Microsoft SQL Server. I’m using INSERT EXEC statement on sp_replmonitorhelppublisher to get the status, questions that will follow are […]

Cakephp mssql Store Procedure with parameters (Invalid cursor state)

I use CakePhp for a little application. In this application I store some store procedures (I use mssql). Now I want to execute theses store procedures and want to use the result set. If I call a store procedure without any parameters i get want I want without any problems. But when i call another […]

Executing Analysis Services OpenQuery from T-SQL Stored Procedure

Good evening, I have a T-SQL stored procedure which executes a PREDICTION JOIN query from an Analysis Services server via an OPENQUERY. When I execute the stored procedure manually it completes successfully and updates the relevant tables in my database. However, I have the stored procedure to run as a scheduled job each morning. When […]

Error running stored procedure with MSSQL in PHP

I’m having trouble getting a stored procedure on sql server to work when executed from PHP. The procedure expects an XML string and parses the XML. If I run the code below without trying to parse the XML in the procedure, it works fine, but when I try to parse the XML, I get an […]

using loops in stored procedures in sql server

i have a stored procedure with 5 parameters. @1,@2,@3,@4,@5 Five Columns @a,@b,@c,@d,@e One of them is of string types(@a) which contains “N” number of numeric values separated by coma(,). ex: @a= ‘1,2,3,4,5,6’ @b=’a’ @c=’f’ @d=’g’ @e=’h’ My requirement is to insert “N” number of rows . The value of rest four parameter’s will remain same […]

Execute stored procedure in OUTER APPLY block

Why I can not use a stored procedure in OUTER APPLY block? I need to get int value from the stored procedure dbo.GetTeacherId and use this in WHERE clause. Here my code: USE [StudentsDb] DECLARE @teacherIdOut int; SELECT StudentLastName, StudentFirstName, StudentMiddleName, LessonName, Score, TLastName, TFirstName, TMiddleName FROM Scores JOIN Students ON Scores.StudentId=Students.StudentId JOIN Lessons ON […]

SQL Server stored procedure performance testing

I have a task to test stored procedures performance in SQL Server. My goal is to report the average time and standard deviation of the stored procedure’s execution time to the stakeholders. Realistic data input is a must here 🙂 My question: as I was trying to realistically stage the test I created a simple […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.