Tag: stored-procedures

Replace procedural approach with a set based approach

I have a stored procedure that is called from an SSIS package several times with a different value as an argument. The stored procedure contains an open query to an SSRS cube, and returns a list of people. The resultset is stored in a temporary table. CREATE TABLE #tmp (Person varchar(50), Cat1 bit, Cat2 bit, […]

Calling a store procedure

I am beginning a JTA UserTransaction, and calling a stored procedure in that transaction using another connection object. At run time I am getting the following exception: This stored procedure must run outside a transaction! Number of currently open transactions: 1. This error will not cause a rollback of any open transactions. Is there a […]

Query to Stored Procedure in SSRS

I am getting data from a table and I am writing off to the report through a stored procedure. In the reporting part it has to show the results by users defined date. E.g: Today – should show today’s results only Yesterday – should show Yesterday’s results only How should I declare the values in […]

SQL Server stored procedure to insert called from Python doesn't always store data, but still increments identity coutner

I’ve hit a strange inconsistency problem with SQL Server inserts using a stored procedure. I’m calling a stored procedure from Python via pyodbc by running a loop to call it multiple times for inserting multiple rows in a table. It seems to work normally most of the time, but after a while it will just […]

Stored Procedure Running Slow in ColdFusion 9

I have a stored procedure that started to run really slow since yesterday and I can’t find out why. I am running the SP directly in SQL Management Studio and it takes +/- 6 seconds to return the results but when I am running the same thing from a script it takes 40+ seconds to […]

How to from query escaping comma and other special character in SQL Server stored procedure

I have a function which returns the comma separate values, but when I use COALESCE(@Jiraids + ‘, ‘, ”) I get an error. ALTER FUNCTION [dbo].[getCommonJiraIds]( @selct varchar(100), @whereClause varchar(100), @id varchar(100), @TEST_RESULT_INFO_ID varchar(20) ) RETURNS varchar(max) AS BEGIN DECLARE @Jiraids VARCHAR(8000) DECLARE @sql varchar(max) SET @whereClause = ‘dbo.SNSTestResults.JIRA_ID <> ” AND dbo.SNSTestResults.’+@whereClause+ ”+ QUOTENAME(@id,””) […]

How to start an sp_procoption Stored Procedure without restarting SQL Server

Is there a way to start a stored procedure that has been configured for automatic startup via sp_procoption without having to restart SQL Server? Occasionally I’ll need to make an adjustment and have to KILL the running session to do so, but I cannot seem to locate a way to turn it back on again […]

Microsoft Enterprise Library cannot write log through ODBC

In my application MEL is set up to use database listener based on GenericDatabase (ODBC) like this: GenericDatabase db = new GenericDatabase(_connectionString, OdbcFactory.Instance); TraceListener traceListener = new FormattedDatabaseTraceListener(db, _writeLogStoredProcName, _addCategoryStoredProcName, _formatter); ODBC is set up to connect to SQL Server driver. “WriteLog” stored procedure looks as follows: ALTER PROCEDURE [dbo].[WriteLog] ( @EventId int, @Priority int, […]

Query to Stored Procedure

I have been trying to learn using the Pubs database. I have been trying to figure out how to create a stored procedure to create a profitability column. The current code I have is this. SELECT DISTINCT pub_id, ((price * ytd_sales) – advance / 100 * royalty) as [Profit] FROM titles WHERE price IS NOT […]

How to debug a remote stored procedure in SQL Server 2008 Management Studio?

I want to debug a remote stored procedure with breakpoints like in Visual Studio. I know it can be achieved through Visual Studio by adding the existing server connection through “step into the procedure” option. But still using this I can not debug the remote database stored procedure, so how can I achieve this? Can […]

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