The Stack Overflow podcast is back! Listen to an interview with our new CEO.

New answers tagged

0

I used the function that @Scott Hodgin suggested and created the table with that information. Thanks man! Note: if your Stored Procedure is using a Temporary table this won't work as specified in the page: The result could not be determined because the batch contains a temporary table and is not supported by sp_describe_first_result_set.


0

What Edition of SQL Server are you using? Judging from what I see out there, it seems that This is an ADO / OLEDB problem not a SQL-Server one. Even though you are exceeding the transaction capacity of SQL Server, it is actually the application not closing the connection the root problem. One suggestion I saw is to run SQL profiler, and look into how many ...


2

You can do it in a single statement. SET NOCOUNT ON; SET XACT_ABORT ON; BEGIN TRY BEGIN TRANSACTION; DELETE FROM Errn WHERE erid IN ( select distinct m.ID from Member m left join (select *, row_number() over (PARTITION BY rid ...


1

Since LoginA is not member of sysadmin server role, the login must need IMPERSONATE permissions to use EXECUTE AS i.e. USE dbB; CREATE USER LoginA for login LoginA; -- This will create user under "public" database role GRANT IMPERSONATE ON USER::LoginB to LoginA; -- This is required to use "EXECUTE AS" Once LoginA got IMPERSONATE access, you can create ...


0

Get rid of @num_rows and @i; you can loop without counting. There are lots of examples floating around. You must construct the query something like SET @stmt := CONCAT('SELECT "', col_name, '", ' MIN(', col_name, '), ', ' MAX(', col_name, ')', ' FROM ', tbl_name'); The intent is to ...


1

if @quantity is a "variable" .. I hear "input parameter" to the stored procedure ... then add it to the definition .. SET NOCOUNT ON CREATE PROCEDURE [dbo].[GetFruitName] @quantity int AS BEGIN INSERT INTO table1(fname,fprice,quantity) SELECT f.name,f.price, @quantity FROM Table2 as f END


-1

My example sqlcmd -i C:\CFDIxml\SAT3.3\UUID_QAD\sqljob.sql -o W:\einvoicemx\folios.csv if errorlevel 1 and explain in Spanish in https://www.programacionparatodos.com/2019/09/ejecutar-script-sql-desde-cmd.html


1

Focusing on Table-Valued Parameters (TVPs) is a bit misleading since the question isn't really concerned with them. The example code, in fact, isn't even using the TVP as a TVP, and would be the same question even if presented as asking about table variables. Meaning, the following adaptation of the OP's example code is really the same question since ...


0

I would come at this from the opposite direction. select c.table_name, c.column_name, sp.name from INFORMATION_SCHEMA.columns c inner join sys.procedures sp on object_definition(sp.object_id) like '%' + c.TABLE_NAME + '%' and object_definition(sp.object_id) like '%' + c.column_name + '%'


0

Here is my full code as requested (still working on reproducible example for missing dependencies): CREATE PROCEDURE [dbo].[usp_v9_SprocDocInfo_FullDependency_SingleSproc] @SprocName NVARCHAR(150) = '' AS BEGIN DECLARE @ObjName NVARCHAR(128) = NULL DECLARE @rowCount INT = 0 DECLARE @HasNulls BIT = 0 DECLARE @DepExists BIT = 0 --temp table to hold output -...


0

You need to pass @IsKit as well, between AllowAttr and Body.


0

tl;dr They are forbidden in order to prevent you from inadvertently causing a commit that you may not want to happen (eg by calling the stored procedure in the middle of a transaction). Copied from the (now deprecated) MySQL mailing list: the following is Shawn Green's answer to the post I made there, and then his reply to another poster in the same ...


1

Two main ways, neither involving a trigger (generally it's better to avoid triggers if you can) - 1) Use an OUTPUT clause with the first INSERT statement to pick out the generated identity value: DECLARE @NewOrder TABLE (order_id INT); INSERT INTO Orders (customer_id, track_num, pckg_num) OUTPUT inserted.order_id INTO @NewOrder VALUES (@...


-1

"A trigger is a special type of stored procedure that automatically runs when an event occurs in the database server." docs This is why you do noot need to 'fire a trigger'. Its an event that happens because of something else, i.e. inserting a record in table A.


0

I think that if you just change this line it should work for you. SET @order_id = SCOPE_IDENTITY(); Your method of grabbing the max id and adding 1 is not safe for concurrent access, scope_identity() is the correct way of getting the ID of the just inserted row.


0

Returning the resultset does not have to be the last thing that happens in a stored procedure. If you don't want to do things with the resultset in your procedure you don't have to store it in a temp table/table variable. create or alter procedure dbo.GetVersionServer as begin -- Execute your dynamic SQL here returning a rowset declare @S nvarchar(max) ...


0

If you are returning singular results (e.g you want to store returned results in an @parameter int, rather than an @parameter table) then sp_executesql can use OUTPUT parameters see https://support.microsoft.com/en-gb/help/262499/how-to-specify-output-parameters-when-you-use-the-sp-executesql-stored for worked examples of how to do this


1

The C# code you are using to invoke the stored procedure is passing in @order_id as the first parameter. That parameter is not referenced in the actual stored procedure. Try removing that parameter from the C# code.


6

You could use a table variable to hold the results of the dynamic SQL call until you are ready to exit the stored procedure. Just before returning from the stored procedure, select the data from the table variable. DROP PROCEDURE IF EXISTS dbo.MyProc; GO CREATE PROCEDURE dbo.MyProc AS BEGIN --Declare Table variable to hold results of query DECLARE @...


1

Wow, that's a relevant problem, because you can't identify an error in a procedure, an this passes hidden next to some valid result! I think your question should be more popular. After many tries and tests I found a way to solve it. You'd expect to loop through the procedure result set, until no more results are available (the PHP Manual way). However, I ...


2

You're currently looking for a column_name that has different values, so you'll never pass the test. You have to search in different rows to make it work. IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='my_Table' and column_name= 'ID') AND EXISTS (SELECT 1 FROM ...


0

This question is a few years old, but I just want to throw in another possibility for anyone like me who found it later. I ran this command: EXEC SP_CONFIGURE 'Agent XPs' And got the error described: Msg 2812, Level 16, State 62, Line 1 Could not find stored procedure 'SP_CONFIGURE'. But then I remembered that this server is set up to be case-sensitive. ...


1

I'm not sure why you need a recursive CTE. If you just need to split the data into two tables based on a date range you can do the following: CREATE PROCEDURE FillTables ( @dateFrom DATETIME, @dateTo DATETIME ) AS BEGIN SET NOCOUNT ON; BEGIN TRANSACTION; BEGIN TRY -- GET ONLY THE DATA WE NEED AND CACHE IT SELECT doc_num, doc_date, ...


1

Change the assignment operator to := or use select into create table t (id uuid); insert into t values ('672124b6-9894-11e5-be38-001d42e813fe'::uuid), ('672124b6-9894-11e5-be38-001d42e813fe'::uuid), ('672124b6-9894-11e5-be38-001d42e813fe'::uuid), ('672124b6-9894-11e5-be38-001d42e813fe'::uuid); create or replace function test_array2() returns uuid[] as $$ ...


2

If your goal is to execute the Test stored procedure for all stock rows, I'd recommend using a CURSOR to read each row from the stock table and pass the data into the Test stored procedure. Here is an example. CREATE PROCEDURE USAGE AS BEGIN --Declare variables DECLARE @SiteId INT; DECLARE @StockId INT; DECLARE @ProductId INT; --...


2

In general, non-transactional things (particularly those with human-visible side effects) should not be part of transactions. Sending emails, calling web services, etc. cannot be rolled back so you don't want them to be called directly from a trigger. For all your trigger knows, the transaction that did the insert might still be rolled back and your alert ...


0

I worked around the requirement to be on the same transaction by writing a bash script which executed my SQL scripts using PSQL. It seems to work ok. https://stackoverflow.com/a/58022481/494635


Top 50 recent answers are included