For anyone who hasn't seen it yet, SQL Server 2019 CU7 has been pulled with a recommendation to uninstall if you're using database snapshots (including DBCC CHECKDB).
ALTER PROCEDURE [insoles].[new_order] (@data NVARCHAR(max))
AS
BEGIN
DECLARE @inserted TABLE (Id bigint);
INSERT INTO [insoles].[orders] (data)
OUTPUT inserted.Id INTO @inserted
VALUES (@data);
DECLARE @result bigint;
SELECT TOP 1 @result = Id FROM @inserted;
return @result;
END
^^^ Anyone knows why it doesn't return the OUTPUT value on a LocalDB?
From the manual: "Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored". The return statement doesn't generate a rowset output, therefore ExecuteScalar can't read the result
@McNets That's exactly how you read the return result of a procedure
You can make the proc itself return the ID as a rowset. Instead of SELECT TOP 1 @result = Id FROM @inserted; return @result; do just SELECT TOP 1 Id FROM @inserted;. (And consequently you don't need to the @result variable.) But I don't know if this won't break something else in your app (or in any other place that uses the proc).
I mean, if you make that change, then ExecuteScalar will be able to consume the result of exec insoles.new_order @somedata without the need for a subsequent select.
I have written a stored procedure in SQL Server which will return a Bigint value
alter procedure [dbo].adding
@one bigint,
@two bigint,
@startid bigint output
as
begin
set @startid = @one + @two
return @startid
end
But while returning value I am getting an excepti...
Let me see what I ended up doing. I probably should have changed the log table to use a sequence. Because that's what I use for sessions.
Lol, no apparently, I punted down the road and left it as int.
I don't actually have a Session table, but a given set of operations that might be logged can be all related to a Session, and the ID for that is just pulled off a sequence. When I needed to have logs related to other log entries and also hang some detailed audit off of a log entry was when I ran into this, and it was too hard to refactor everything so I left it as int so all the various log procs didn't have to be changed to have output parameters and everything.
Easy. All you need to do is return the lower 32 bits in the first call and then call a second proc which outputs the upper 32 bits. Bickety bam, problem solved. I'll send you an invoice
BEGIN
DECLARE @inserted TABLE (Id bigint);
INSERT INTO [insoles].[orders] (data)
OUTPUT inserted.Id INTO @inserted
VALUES (@data);
SELECT TOP 1 Id FROM @inserted;
return 0;
END
So I have two identical databases, one on SQL Server 2012, one on SQL Server 2016. I have a script that installs my data mart rules pack which is basically a sets of three tables that define some transforms. So Syndromes might be one and then it has a Syndromes_Detail and Syndromes_Tags table.
There are two XML columns in Syndromes and a trigger on INSERT that populates the Detail and Tags so that during actual application of the rules this data is already shredded and the XML is unused. I don't think these are the issue, but just background.
The script in question simply deletes everything from the table and inserts literals - the rules which are made from Excel->XML->SQL Insert during my build process.
The script takes 1-2 minutes to run locally on either 2012 or 2016 instances.
On the development shared 2012 instance it is about the same.
On the development shared 2016 instance it is like 50 minutes!
So there is something up with that 2016 machine (two separate machines). The profiler shows CPU on a single insert of ~600 and Duration of ~600 on the 2016 machine compared to 0 CPU and 1 Duration on 2012 machine.
The databases should be identical, everything is dropped and installed from a single build on every build cycle, it's just there are two installs (soon there will be a third on a SQL 2019 instance) that are then tested, build results compiled, documentation generated.
No other dev groups complain, but their apps are all OLTP. This script is batch deletes and single INSERTs and occasional calls to my Logging PROC, but in proviler, I'm just looking at the single INSERTs and not knowing where to start.
There is a network/server guy, but I don't know what else to point him at.
Equivalent hardware? Other activity on the database? What wait type is it reporting on that insert? Assume it's not a log growth event as it wouldn't happen every time you run ... unless the log is always undersized, I suppose
If you disable the trigger, does the insert take nearly identical amount of time?
That was something I was going to try. It is new for this version so that I didn't forget to add calls to that template every time informatics used a new set of drivers.
The SQL 2016 run is STILL going, I guess I could just drop the trigger while it's running!
I seem to recall there was a "thing" where the first insert into a heap/table would cost more than an insert into a table with at least one record but damned if I can remember the details and version I ran into it with
Looks like only two wait types (it's still running, I just captured some activity): WRITELOG and SOS_SCHEDULER_YIELD, total wait time seems low, doesn't seem like it's waiting for anything.
Only reason they aren't combined is I haven't made the templating system that goes from Excel->XML->SQL smart enough to combine up to 1000 into a single insert.
And it's not behaving any differently between my local 2012 and 2016 instances.
I try to only develop against 2012 because it needs to run on that feature set for a while longer.
@PaulWhite No, it runs during the install script either from Invoke-SqlCommand or sqlcmd, depending on if that's available. For my testing, it's just sqlcmd.
And I'm just doing this rules pack over and over because it's idempotent - the database drop and creation (and tables and procs etc) obviously isn't.
I just kept narrowing it down until I found two places where the same thing ran in vastly different times.
And this is the simplest part, pbut even then, I can probably cut it down to one set of mapping tables.
sqlcmd is running locally on each machine to rule out network and Team City build agent effects.
If I were able to repro this locally, I would try a few things: * Wrap everything in a transaction; OR * Wrap everything in a temporary procedure with SET NOCOUNT ON; at the top.
As I say, it is curious behaviour.
I would also check the setting of @@OPTIONS on both instances
There are SET NOCOUNT at the top of most if not all batches. It definitely seems to point to a problem with that instance, but I don't really know where to point the systems engineer. I can definitely change the script generation to be smarter on the data generation, we are still stabilizing this build. One thing I am hesitant to do is try to load from external files to the SQL Server in the installation script because a requirement is that it can be run from anywhere.
And that means a lot more client shenanigans in PowerShell like SQLBulkCopy. I quite like the naked INSERTs, they are easy to troubleshoot.
The documentation for SET NOCOUNT says:
SET NOCOUNT ON prevents the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. For stored procedures that contain several statements that do not return much actual data, or for procedures that contain Transact-SQL loop...
Well, I'm glad it is caught in a development server somewhere. The ruleset for our first release is small, no one might have even noticed. I didn't notice. This second release is much larger.
It's so large right now that it means the whole build cycle is affected
I have written a stored procedure in SQL Server which will return a Bigint value
alter procedure [dbo].adding
@one bigint,
@two bigint,
@startid bigint output
as
begin
set @startid = @one + @two
return @startid
end
But while returning value I am getting an excepti...
