I am trying to create a report from a cloud based EHR so I cannot share real data and some of these tables are fairly massive. I will try to minimize and share the bare minimum and expand if someone needs more information to help. This should be fairly easy and I'm just having a brain fart I think. I need to combine multiple answers into a single row as separate columns. Here is my query as it is and it does return all the answers but every answer is generating a separate row. There will only ever be one answer for each question per visit id. There are a few catches to working with this system. At it's heart it's SQLServer, however queries are restricted to starting with 'select' making temp tables a bit more difficult. There can be no spaces, no blank lines nothing before your select. This is their version of security I guess. All reports are written through a web interface no direct access to the db in any way. Current Output: **

clientvisit_id  |  client_id  |  members_present  |  patient_category  

**

141001          |  2001       |                   |
141001          |  2001       |                   |  	 
141001          |  2001       |  Patient          |  	 
141001          |  2001       |                   |  Adult  	 

Desired output: **

clientvisit_id  |  client_id  |  members_present  |  patient_category  

**

141001          |  2001       |  Patient          |  Adult 	 

Select cv.clientvisit_id, cv.client_id, mp.answer as members_present, pc.answer as patient_category From ClientVisit cv Inner Join SavedVisitAnswer sva On sva.clientvisit_id = cv.clientvisit_id Inner Join Question q On sva.question_id = q.question_id Inner Join Category cat On q.category_id = cat.category_id Inner Join FormVersion fv On cat.form_ver_id = fv.form_ver_id Inner Join Forms On fv.form_id = Forms.form_id Inner Join (Select a1.answer_id, a1.answer From Answer a1 Where a1.question_id = '532096' ) as pc on sva.answer_id = pc.answer_id Inner Join (Select a2.answer, a2.answer_id From Answer a2 Where a2.question_id = '532093' ) as mp on sva.answer_id = mp.answer_id Where Forms.form_id = '246'

Say I am trying to understand some unkown part of a database. I usually rely a *lot* on foreign keys to understand the relations between the data, but the tables in question don't have any for some reason. And what's even worse, the data are not consistent! There are child records lacking their parent. Can I create the foreign key on such table, but without actually enforcing it (at least temporarily)? As a comment of sorts. --- We can use following AI generated demo as an example.

CREATE TABLE Invoice (
    InvoiceID INT PRIMARY KEY,
    CustomerName NVARCHAR(100) NOT NULL,
    InvoiceDate DATE NOT NULL,
    TotalAmount DECIMAL(18, 2) NOT NULL
);

CREATE TABLE InvoiceRow (
    RowID INT PRIMARY KEY IDENTITY(1,1),
    InvoiceID INT NOT NULL,
    ProductName NVARCHAR(100) NOT NULL,
    Quantity INT NOT NULL,
    UnitPrice DECIMAL(18, 2) NOT NULL
);

INSERT INTO Invoice (InvoiceID, CustomerName, InvoiceDate, TotalAmount) VALUES
(1, 'John Doe', '2025-07-20', 150.00);

INSERT INTO InvoiceRow (InvoiceID, ProductName, Quantity, UnitPrice) VALUES
(1, 'Product A', 2, 50.00),
(1, 'Product B', 1, 50.00),
(2, 'Product C', 3, 40.00);

This command fails, because the data are not consistent.

ALTER TABLE InvoiceRow
ADD CONSTRAINT FK_InvoiceRow_Invoice
FOREIGN KEY (InvoiceID) 
REFERENCES Invoice(InvoiceID);

[Fiddle](https://dbfiddle.uk/m6Nb0ohj) .

I am using Microsoft SQL Server 2016. For my products table; I need to define a ClusteredIndex with 2 fields of Product Type and Product Code. ProductType(tinyint), ProductCode(varchar(32)). I know that ProductId is the right option for this, but it affects my software development speed. But in terms of speed, I want my Product table to run fast. Estimately there will be 1,000,000 records in the table. What I want to ask is if using varchar(16) instead of varchar(32) in ProductCode, how much will it affect performance in queries? Is there a software that I can test as ProductCode varchar(32) and varchar(16) in 1,000,000 lines? Can I do this on sql Management studio? 16 characters is enough right now. However, the user may want the product code to be given by the system itself in this case I want to automatically set a product code with newid. Product Id(int) is not the right solution for me. Because many transactions (orders, warehouses, sales) that have their own product codes are executed through this code. Edit: I tested with SqQueryStress based on the comment. I am sharing the results. I created 2 tables as products and sales. productsID,salesID; - productsID.id clustered index - salesID.productId non clustered index products16,sales16 ; - products16.productCode clustered index - sales16.productCode non clustered index products36,sales36; - products36.productCode clustered index - sales36.productCode non clustered index (https://www.db-fiddle.com/f/3qJM9uupQoXAtLgL7u8YaE/0) I entered 100,000 products, random results productsId 06:03 [![enter image description here]] products16 05:46 [![enter image description here]] products36 05:42 I entered a sales record from 10,000 random products tables. salesID 01:42 sales16 02:34 sales36 02:01 I listed the sales reports according to the data I entered. (1000 times) Does that mean reporting 1000 * 10,000 (sales item) 10,000,000 rows? salesId sales report (join) 01:07 sales16 sales report (join) 02:05 sales36 sales report (join) 02:55 I used SqlQueryStress for the first time, I don't know much about interpretation. **When I pull 10,000,000 sales data. Reporting sales36 and sales16 as well 02:55 (175 seconds) 02:05 (125 seconds) Is there a 40% performance difference?**

**Question**: Why example 1 does not work while example 2 works: **SQL Table to check for non-numeric values in varchar column**: CREATE TABLE #t(Mycol varchar(15)) INSERT #t VALUES ('123.86'),('4a5.67'),('45.68'),('45r8.78') Query to check the NON-NUMERIC values in the table: SELECT Mycol from #t WHERE ISNUMERIC(Mycol) = 0 **Output**: | Mycol | |---------------------| | 4a5.67 | | 45r8.78 | **GOAL**: Achieve the same by using dynamic SQL **Example 1**: Did not work, why? How can we improve this code without declaring variables outside EXECUTE sp_executesql statement; or is it even possible? EXECUTE sp_executesql N'SELECT @colName as Mycol from #t WHERE ISNUMERIC(@colName) = 0', N'@colName varchar(15)', @colName = N'Mycol'; **Output**: | Mycol | |---------------------| | Mycol | | Mycol | | Mycol | | Mycol | **Example 2**: Works - but required more variable declarations. DECLARE @Qry nvarchar(150), @colName varchar(15) = 'Mycol' SET @Qry = N'SELECT ' + @colName + ' FROM #t WHERE ISNUMERIC(' + @colName + ') = 0' EXECUTE sp_executesql @Qry, N'@colName varchar(15)', @colName = N'Mycol'; Output: | Mycol | |---------------------| | 4a5.67 | | 45r8.78 |

I have been given a job of taking two tables (trimmed down create scripts below) and merging their data into a third table.

` 
CREATE TABLE dbo.Ztest_nominal
(
  BMCode       varchar(20)     NOT NULL,
  nomcode      varchar(500)    NOT NULL,
  Description  nvarchar(4000)  NULL,
  LanguageID   int             NOT NULL,
  CONSTRAINT PK_Ztest_nominal PRIMARY KEY CLUSTERED (BMCode ASC)
)

CREATE TABLE dbo.Ztest_Participation
(
  ParticipationID       int IDENTITY(1,1)  NOT NULL,
  BMCode                varchar(20)        NOT NULL,
  LatestVersionNo       varchar(5)         NOT NULL,
  LastVersionSubmitted  varchar(5)         NOT NULL,
  CONSTRAINT PK_Ztest_Participation PRIMARY KEY CLUSTERED (BMCode ASC)
)

` The third tables columns to take the contents of above (basically the above two tables):

`
[ParticipationID] [int] IDENTITY(1,1) NOT NULL,
[BMCode] [varchar](20) NOT NULL,
[LatestVersionNo] [varchar](5) NOT NULL,
[LastVersionSubmitted] [varchar](5) NOT NULL,
[nomcode] [varchar](500) NOT NULL,
[Description] [nvarchar](4000) NULL,
[LanguageID] [int] NOT NULL

` Ztest_nominal has about 63,000,000 rows and Ztest_Participation about 62,000 The sql I was going to use (with an insert into) is (I did have the inner select outside before I gave up and posted here!):

`
SELECT 
  p.ParticipationId,
  p.LatestVersionNo,
  n.*
FROM
  Ztest_nominal n
INNER JOIN
  (
    SELECT 
      ParticipationId,
      LatestVersionNo,
      BMCode
    FROM 
      Ztest_participation
    WHERE 
      LatestVersionNo = LastVersionSubmitted
  ) p 
    ON p.BMCode = n.BMCode

` However i feel its going need some optimisation so its not slow, also I've already had out of memory errors when running it. Any help would be appreciated

I encountered an issue where I received a CSV file that supposed to be delimited by semicolons (;) but was delimited by commas (,). Bulk insert called by sqlcmd did not fail but did not insert either. I know that calling xp_cmdshell is not best practice but please don't comment on this. After investigation I found that it only fails (as expected) when FIRSTROW = 1, but I need a header inside the file. Table:

CREATE TABLE [dbo].[test_table](
	[id] [int] NULL,
	[title] [varchar](10) NULL,
	[val] [int] NULL
)

Format file:

12.0
3
1       SQLCHAR              0       4       ";"    1     "id"             ""
2       SQLCHAR              0       10      ";"    2     "title"          SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR              0       4       "\r\n"     3     "val"            ""

Data file:

1,first,0
2,second,2

Bulk insert:

DECLARE @cmd VARCHAR(4000);
SET @cmd = 'sqlcmd -b -S  -Q "set nocount on; set dateformat dmy; bulk insert [test_db].[dbo].[test_insert] from ''C:\temp\test_table.csv'' with ( DATAFILETYPE = ''char'', TABLOCK, MAXERRORS = 1000, FIELDTERMINATOR = '';'', ROWTERMINATOR = ''\r\n'', BATCHSIZE = 100000, FORMATFILE = ''C:\temp\test_table.txt'', FIRSTROW = 2 );"';
EXEC xp_cmdshell @cmd;

I have a linked server setup on my source production server. I also have a secondary server which is the linked server. What I was looking to do is to execute a stroed proc and log connections to the linked server. However I seem to be having issues. > The object name 'MetricDB.Baseline.dbo.WhoIsActive' contains more than > the maximum number of prefixes. The maximum is 2. Metric DB is the destination server which is configured on the source server as a linked server. I been using Adam's sp which was described by Brent here: https://www.brentozar.com/responder/log-sp_whoisactive-to-a-table/ Due to space issues on my production DB I wanted to log it all to another server. Has anyone done this previously? or know if I'm missing something? Thanks

I am using SQL Server 2022 Developer Trying get all AccessLog that classified to type 1.

SELECT [t].[Time], [u].[UserName], [t].[Type], [t].[Message]
FROM [AccessLog] AS [t]
         LEFT JOIN [AppUser] AS [u] ON [t].[UserId] = [u].[Id]
WHERE EXISTS (SELECT 1
              FROM [LogCatalog] AS [c]
              WHERE [c].[Type] = 1
                AND [c].[Name] = [t].[Type])
ORDER BY [t].[Time] DESC

For 1M record, it will need ~90s to execute on my computer. Most cost is on sort operate. I already have index on AccessLog.Time DESC, but the plan will sort again still after join. https://www.brentozar.com/pastetheplan/?id=HyXzc9UUp I have Index on AccessLog: 1. PK [Id] 2. IX [Time] DESC 3. IX [Time] DESC, [Type] ASC 4. IX [Type] ASC, [Time] DESC 5. IX [Type] ASC 6. IX [UserId] ASC 7. IX [Time] DESC, [UserId] ASC, [Type] ASC The query filter by [Type] and order by [Time], why the plan can not use the [Time],[Type] index but need to sort again?

The last line of the following code inserts records from #S to #D if value is a valid date; otherwise it returns NULL (since I am not using ELSE clause): CREATE TABLE #S(COL VARCHAR(15)) INSERT #S VALUES('20250630'),('ABC'),('20150821') CREATE TABLE #E(ERCOL VARCHAR(15)) CREATE TABLE #D(DTCOL DATE) INSERT #D(DTCOL) SELECT (CASE WHEN ISDATE(COL) = 1 THEN (CAST(COL AS DATE)) END) FROM #S **Question**: How can we include ELSE block in the last line of above code and use that block to insert the error value (ABC) into #E table while still using the same SELECT statement as above. So, we are just modifying the last line of the above code: INSERT #D(DTCOL) SELECT (CASE WHEN ISDATE(COL) = 1 THEN (CAST(COL AS DATE)) ELSE 'NEED TO INSERT "ABC" VALUE TO #E TABLE' END) FROM #S

I am maintaining and publishing a DBA tools database using a Visual Studio 2017 Database Project. Now I am trying to add a SQL Server agent job using a post deployment script. The job is scripted out by SSMS and is parsing/executing just fine there. It is created with the option "Script (not in build)" and called by the post-deployment script (":r /path/file.sql"). Several other scripts are called like this and the build is working like that. VS is parsing the script in the editor as well until I try to build. The build fails with: > 72006: Fatal scripting error: Incorrect syntax was encountered while parsing '$(Escape_SQUOTE('. The lines it fails on is: @command=N'sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d MSSYS -Q "EXECUTE dbo.IndexOptimize @Databases = ''USER_DATABASES'', @FragmentationLow = NULL, @FragmentationMedium = NULL, @FragmentationHigh = NULL, @UpdateStatistics = ''ALL'', @OnlyModifiedStatistics = ''Y'', @MaxDOP = 8, @LogToTable = ''Y''" -b', @output_file_name=N'$(ESCAPE_SQUOTE(SQLLOGDIR))\StatisticUpdate_$(ESCAPE_SQUOTE(JOBID))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt', So it tries to parse something as code that is actually supposed to be just a Unicode sting. I tried to put both strings into a variable ending up with the same error in the SET statements. The full code for your reference: USE [msdb] GO BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance' IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback END DECLARE @jobId BINARY(16) EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'MSSYS StatisticUpdate - USER_DATABASES', @enabled=1, @notify_level_eventlog=2, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N'Source: https://ola.hallengren.com ', @category_name=N'Database Maintenance', @owner_login_name=N'sa', @job_id = @jobId OUTPUT IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'StatisticUpdate - USER_DATABASES', @step_id=1, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'CmdExec', @command=N'sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d MSSYS -Q "EXECUTE dbo.IndexOptimize @Databases = ''USER_DATABASES'', @FragmentationLow = NULL, @FragmentationMedium = NULL, @FragmentationHigh = NULL, @UpdateStatistics = ''ALL'', @OnlyModifiedStatistics = ''Y'', @MaxDOP = 8, @LogToTable = ''Y''" -b', @output_file_name=N'$(ESCAPE_SQUOTE(SQLLOGDIR))\StatisticUpdate_$(ESCAPE_SQUOTE(JOBID))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt', @flags=0 IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1 IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'MSSYS StatisticUpdate - USER_DATABASES', @enabled=0, @freq_type=8, @freq_interval=62, @freq_subday_type=1, @freq_subday_interval=0, @freq_relative_interval=0, @freq_recurrence_factor=1, @active_start_date=20171010, @active_end_date=99991231, @active_start_time=190000, @active_end_time=235959 IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)' IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback COMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave: GO

I need to create a script that gives the report for the following information: - Backup Job Name - Databases that have been backed up in the last 24 hours - Size of each DB backup - Start time of backup - End time of backup - Duration of backup - Location of the backup files I need to still include Log backup information, backup size and the location of the backup files stored. Could someone point me in the right direction of how to better my query? Thanks in advance. What I have tried:

@dbname sysname
SET @dbname = NULL --set this to be whatever dbname you want
SELECT 
  bup.database_name AS [Database],
  bup.server_name AS [Server],
  bup.backup_start_date AS [Backup Started],
  bup.backup_finish_date AS [Backup Finished]
  ,CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))/3600 AS varchar) + ' hours, ' 
  + CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))/60 AS varchar)+ ' minutes, '
  + CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))%60 AS varchar)+ ' seconds'
  AS [Total Time]
FROM msdb.dbo.backupset bup
WHERE bup.backup_set_id IN
  (SELECT MAX(backup_set_id) 
   FROM msdb.dbo.backupset
   WHERE database_name = ISNULL(@dbname, database_name) --if no dbname, then return all
   AND type = 'D' --only interested in the time of last full backup
   GROUP BY database_name) 
/* COMMENT THE NEXT LINE IF YOU WANT ALL BACKUP HISTORY */
AND bup.database_name IN (SELECT name FROM master.dbo.sysdatabases)
ORDER BY bup.database_name

BACKGROUND ---------- I am using Microsoft SQL Server Management Studio v18.9.1 on Windows 10. I've created a database to store test results of our company's widget (not yet being produced). I am working on a stored procedure to populate that database with random test result records. The purpose of this is to have a data set to use for query development while waiting for real widgets to start being produced and tested. In what follows, our test suite is composed of multiple subtest types. For this post, I've created only one subtest type. The example below runs the test suite one time (with a **[test_id]** of 0). Within a test, a given subtest may be run multiple times if the tester deems it necessary for any reason. In the example below, the one subtest I show is run five times for illustrative purposes. The subtest I show has two parameters, the first of which can take on two discrete values, and the second of which can take on three discrete values. All subtests must be run over all parameter combinations. So, a subtest result consists of 2 * 3 = 6 partial results (one for each parameter combination). When a parameter combination is tested, either anomalies are not observed (pass) or one or more anomalies are observed (fail). If any of the six parameter combinations produces one or more anomalies, the subtest as a whole fails. A subtest result record contains the following columns: - **[test_id]** - **[subtest_run_number]** - **[parameter_1]** - **[parameter_2]** - **[anomaly_id]** - **[anomaly_magnitude]** - **[subtest_type]** If testing with a given parameter combination produces no anomalies, this is noted by creating a single subtest record containing the two parameter values, an **[anomaly_id]** of 0, and an **[anomaly_magnitude]** of NULL. If testing with a given parameter combination produces N anomalies, this is noted by creating N subtest records. Each subtest record contains the parameter values that produced the anomaly. Each subtest record also contains a 1-based anomaly ID for one of the anomalies seen as well as the "magnitude" of that anomaly. In producing this data set of random test results, I am trying to use set-based operations rather than iteration. As can be seen below, I did have to use iteration when 1) looping over the number of times the subtest would be run and when 2) looping over the (randomly-chosen) number of anomalies that were to be created for a given parameter combination. Where I was able to use set-based operations was when creating test result data for every parameter combination. This was a natural place to use a cross join since each parameter has a table defining its valid values. However, there's a problem... THE PROBLEM ----------- Within a subtest run, every parameter combination has the same number of anomalies associated with it. What I want is for the anomaly count associated with each parameter combination to be able to vary independently. AN UNDESIRABLE SOLUTION ----------------------- The only way I can think of accomplishing this is to forego set-based operations and iterate as follows: DECLARE @parameter_1 as INT = 1; DECLARE @parameter_2 as INT = 1; DECLARE @anomaly_count AS INT; EXEC [dbo].[rand_int] @a = 0, @b = 2, @random_int = @anomaly_count OUTPUT; WHILE @parameter_1 0 AND [anomaly_magnitude] IS NOT NULL) OR ([anomaly_id] = 0 AND [anomaly_magnitude] IS NULL) ) ); GO -- Stored Procedure CREATE PROC [dbo].[rand_int] @a AS INT, @b AS INT, @random_int AS INT OUTPUT AS SET NOCOUNT ON; SET @random_int = FLOOR(RAND() * (@b - @a + 1)) + @a; GO -- Stored Procedure CREATE PROC [dbo].[generate_random_subtest_type_1_data] @test_id AS VARCHAR(20) AS SET NOCOUNT ON; DECLARE @subtest_type_1_count AS INT = 5; DECLARE @subtest_type_1_loop AS INT = 0; WHILE @subtest_type_1_loop 0 SET @subtest_result = 'fail'; ELSE SET @subtest_result = 'pass'; INSERT INTO [dbo].[test_runs] ([test_id], [subtest_run_number], [subtest_type], [subtest_result]) VALUES (@test_id, @subtest_type_1_loop, 'subtest_type_1', @subtest_result); IF @anomaly_count = 0 BEGIN INSERT INTO [dbo].[subtest_type_1_data] ([test_id], [subtest_run_number], [parameter_1], [parameter_2], [anomaly_id], [anomaly_magnitude]) SELECT @test_id AS [test_id], @subtest_type_1_loop AS [subtest_run_number], [parameter_1_value] AS [parameter_1_value], [parameter_2_value] AS [parameter_2_value], 0 AS [anomaly_id], NULL AS [anomaly_magnitude] FROM [dbo].[parameter_1_values] CROSS JOIN [dbo].[parameter_2_values]; END; ELSE BEGIN DECLARE @anomaly_loop AS INT; SET @anomaly_loop = 1; WHILE @anomaly_loop <= @anomaly_count BEGIN -- Instead of RAND(), use ABS(CHECKSUM(NewId()) / 2147483647.0). -- This is because RAND() gets invoked only once in the INSERT statement below. -- -- By using an expression based on NewID(), every row will be assigned its -- own random number. INSERT INTO [dbo].[subtest_type_1_data] ([test_id], [subtest_run_number], [parameter_1], [parameter_2], [anomaly_id], [anomaly_magnitude]) SELECT @test_id AS [test_id], @subtest_type_1_loop AS [subtest_run_number], [parameter_1_value] AS [parameter_1_value], [parameter_2_value] AS [parameter_2_value], @anomaly_loop AS [anomaly_id], ABS(CHECKSUM(NewId()) / 2147483647.0) AS [anomaly_magnitude] FROM [dbo].[parameter_1_values] CROSS JOIN [dbo].[parameter_2_values]; SET @anomaly_loop = @anomaly_loop + 1; END; END; SET @subtest_type_1_loop = @subtest_type_1_loop + 1; END; GO **SCRIPT 2** -- Script 2 -- Delete old data, generate a new data set of random test results, and display it DELETE FROM [dbo].[subtest_type_1_data]; DELETE FROM [dbo].[test_runs]; GO EXEC [dbo].[generate_random_subtest_type_1_data] @test_id = '0'; GO SELECT * FROM [dbo].[test_runs]; SELECT * FROM [dbo].[subtest_type_1_data]; GO EXAMPLE OUTPUT (current, incorrect) ----------------------------------- In the example output below: - All parameter combinations within subtest run 0 have the **same number** (0) of anomalies - All parameter combinations within subtest run 1 have the **same number** (0) of anomalies - All parameter combinations within subtest run 2 have the **same number** (1) of anomaly - All parameter combinations within subtest run 3 have the **same number** (0) of anomalies - All parameter combinations within subtest run 4 have the **same number** (2) of anomalies I would like each parameter combination's anomaly count to be able to vary independently of any other parameter combination's anomaly count.

test_id subtest_run_number  parameter_1 parameter_2 anomaly_id  anomaly_magnitude   subtest_type
    ------- ------------------  ----------- ----------- ----------  -----------------   ------------
    0       0                   1           1           0           NULL                subtest_type_1
    0       0                   1           2           0           NULL                subtest_type_1
    0       0                   1           3           0           NULL                subtest_type_1
    0       0                   2           1           0           NULL                subtest_type_1
    0       0                   2           2           0           NULL                subtest_type_1
    0       0                   2           3           0           NULL                subtest_type_1
    0       1                   1           1           0           NULL                subtest_type_1
    0       1                   1           2           0           NULL                subtest_type_1
    0       1                   1           3           0           NULL                subtest_type_1
    0       1                   2           1           0           NULL                subtest_type_1
    0       1                   2           2           0           NULL                subtest_type_1
    0       1                   2           3           0           NULL                subtest_type_1
    0       2                   1           1           1           0.8730268           subtest_type_1
    0       2                   1           2           1           0.5566615           subtest_type_1
    0       2                   1           3           1           0.4599889           subtest_type_1
    0       2                   2           1           1           0.9322677           subtest_type_1
    0       2                   2           2           1           0.3515796           subtest_type_1
    0       2                   2           3           1           0.872755            subtest_type_1
    0       3                   1           1           0           NULL                subtest_type_1
    0       3                   1           2           0           NULL                subtest_type_1
    0       3                   1           3           0           NULL                subtest_type_1
    0       3                   2           1           0           NULL                subtest_type_1
    0       3                   2           2           0           NULL                subtest_type_1
    0       3                   2           3           0           NULL                subtest_type_1
    0       4                   1           1           1           0.6965834           subtest_type_1
    0       4                   1           1           2           0.4588626           subtest_type_1
    0       4                   1           2           1           0.1284888           subtest_type_1
    0       4                   1           2           2           0.4331938           subtest_type_1
    0       4                   1           3           1           0.3083588           subtest_type_1
    0       4                   1           3           2           0.8907238           subtest_type_1
    0       4                   2           1           1           0.4016767           subtest_type_1
    0       4                   2           1           2           0.8041269           subtest_type_1
    0       4                   2           2           1           0.5932015           subtest_type_1
    0       4                   2           2           2           0.5389434           subtest_type_1
    0       4                   2           3           1           0.7058043           subtest_type_1
    0       4                   2           3           2           0.749176            subtest_type_1

EXAMPLE OUTPUT (desired) ------------------------ Below, I show an example of what desired output might look like. (Here, I show only rows associated with **[subtest_run_number]** = 0. Of course, what's being demonstrated here holds for all **[subtest_run_number]** values.) In the example output below: - The parameter combination (1, 1) has one anomaly (**[anomaly_id]** value is only 1) - The parameter combination (1, 2) has zero anomalies (**[anomaly_id]** value is only 0) - The parameter combination (1, 3) has two anomalies (**[anomaly_id]** values are 1, 2) - The parameter combination (2, 1) has one anomaly (**[anomaly_id]** value is only 1) - The parameter combination (2, 2) has zero anomalies (**[anomaly_id]** value is only 0) - The parameter combination (2, 3) has three anomalies (**[anomaly_id]** values are 1, 2, 3) The salient point is that the number of anomalies per parameter combination can vary within a given value of **[subtest_run_number]**. In the incorrect example output shown earlier, the number of anomalies per parameter combination could not vary within a given value of **[subtest_run_number]**.

test_id subtest_run_number  parameter_1 parameter_2 anomaly_id  anomaly_magnitude   subtest_type
    ------- ------------------  ----------- ----------- ----------  -----------------   ------------
    0       0                   1           1           1           0.5095024           subtest_type_1
    0       0                   1           2           0           NULL                subtest_type_1
    0       0                   1           3           1           0.5062660           subtest_type_1
    0       0                   1           3           2           0.3940517           subtest_type_1
    0       0                   2           1           1           0.6216237           subtest_type_1
    0       0                   2           2           0           NULL                subtest_type_1
    0       0                   2           3           1           0.5802680           subtest_type_1
    0       0                   2           3           2           0.5673455           subtest_type_1
    0       0                   2           3           3           0.5517588           subtest_type_1

MY QUESTION ----------- Is there a way I can continue to use set-based operations but have each parameter combination have its own, independent anomaly count? Or, must I resort to iteration?

Does the SQL Server (2008 or 2012, specifically) CASE statement evaluate all the WHEN conditions or does it exit once it finds a WHEN clause that evaluates to true? If it does go through the entire set of conditions, does that mean that the last condition evaluating to true overwrites what the first condition that evaluated to true did? For example: SELECT CASE WHEN 1+1 = 2 THEN'YES' WHEN 1+1 = 3 THEN 'NO' WHEN 1+1 = 2 THEN 'NO' END The results is "YES" even though the last when condition should make it evaluate to "NO". It seems that it exits once it finds the first TRUE condition. Can someone please confirm if this is the *case*.

Does anyone know how to check from T-SQL code in a stored procedure if a SQL user have read access to a WIN folder that is mapped to the SQL server? Thx Erik

Because I never know if my procedure might be called from other procedures that have an opened transaction, should I always check for existing transactions before opening a new one? It seems like a bit of boilerplate code that I need to repeat everywhere and it will make for worse readability. I'm trying to future proof against this error: > Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0. Example of boilerplate code Exception handling and nested transactions

I have a bunch of SQL Servers registered within my SSMS, lots of different SQL versions, editions and DB compatibilities. I want to check out if **any** of those servers have **any** of the [sp_Blitz](https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit) or [sp_WhoIsActive](http://whoisactive.com) stored procedures installed, and which versions they are. I know I can **right-click** on my **Registered Servers group** and open **one** query window that connects to **all** the servers in the group, but is there a reliable way to check which SProcs and versions I have?

Oracle version: =============== CREATE OR replace PROCEDURE Item_reco_recordset (p_depositid IN NUMBER, p_b1 IN NUMBER, p_itemset OUT SYS_REFCURSOR) AS BEGIN OPEN p_itemset FOR SELECT * FROM pcwitem WHERE deposittransid = p_depositid AND ( ( Bitand(recostatecode, 1) > 0 AND Bitand(recostatecode, 5) != 0 ) OR ( Bitand(recostatecode, 2) > 0 AND Bitand(recostatecode, 6) != 0 ) OR ( p_b1 = 1 AND Bitand(recostatecode, 3) > 0 AND Bitand(recostatecode, 7) != 0 ) OR ( Bitand(recostatecode, 4) > 0 AND Bitand(recostatecode, 8) != 0 ) OR ( Bitand(recostatecode, 9) > 0 AND Bitand(recostatecode, 10) != 0 ) OR ( Bitand(recostatecode, 11) > 0 AND Bitand(recostatecode, 15) != 0 ) OR ( Bitand(recostatecode, 12) > 0 ) OR ( Bitand(recostatecode, 16) > 0 AND Bitand(recostatecode, 14) != 0 ) OR ( Bitand(recostatecode, 18) > 0 ) OR ( Bitand(recostatecode, 19) > 0 AND Bitand(recostatecode, 20) != 0 ) OR ( Bitand(recostatecode, 21) > 0 ) OR ( Bitand(recostatecode, 22) > 0 ) OR ( Bitand(recostatecode, 23) > 0 AND Bitand(recostatecode, 24) != 0 ) OR ( Bitand(recostatecode, 25) > 0 AND Bitand(recostatecode, 29) != 0 ) OR ( Bitand(recostatecode, 26) > 0 ) OR ( Bitand(recostatecode, 28) > 0 ) OR ( Bitand(recostatecode, 31) > 0 ) OR ( Bitand(recostatecode, 32) > 0 AND Bitand(recostatecode, 61) != 0 ) OR ( Bitand(recostatecode, 39) > 0 ) OR ( Bitand(recostatecode, 40) > 0 AND Bitand(recostatecode, 64) != 0 ) OR ( Bitand(recostatecode, 48) > 0 ) OR ( Bitand(recostatecode, 51) > 0 ) OR ( Bitand(recostatecode, 53) > 0 AND Bitand(recostatecode, 54) != 0 ) OR ( Bitand(recostatecode, 57) > 0 AND Bitand(recostatecode, 58) != 0 ) ); END item_reco_recordset; SQL Server Version (throws error -- bitand not a recognized built-in function): ---------- IF OBJECT_ID('Item_reco_recordset', 'P') IS NOT NULL DROP PROCEDURE Item_reco_recordset; GO CREATE PROCEDURE Item_reco_recordset (@p_depositid FLOAT, @p_b1 FLOAT ) AS BEGIN SET NOCOUNT ON; SELECT * FROM pcwitem WHERE deposittransid = @p_depositid AND ( ( Bitand(recostatecode, 1) > 0 AND Bitand(recostatecode, 5) != 0 ) OR ( Bitand(recostatecode, 2) > 0 AND Bitand(recostatecode, 6) != 0 ) OR ( @p_b1 = 1 AND Bitand(recostatecode, 3) > 0 AND Bitand(recostatecode, 7) != 0 ) OR ( Bitand(recostatecode, 4) > 0 AND Bitand(recostatecode, 8) != 0 ) OR ( Bitand(recostatecode, 9) > 0 AND Bitand(recostatecode, 10) != 0 ) OR ( Bitand(recostatecode, 11) > 0 AND Bitand(recostatecode, 15) != 0 ) OR ( Bitand(recostatecode, 12) > 0 ) OR ( Bitand(recostatecode, 16) > 0 AND Bitand(recostatecode, 14) != 0 ) OR ( Bitand(recostatecode, 18) > 0 ) OR ( Bitand(recostatecode, 19) > 0 AND Bitand(recostatecode, 20) != 0 ) OR ( Bitand(recostatecode, 21) > 0 ) OR ( Bitand(recostatecode, 22) > 0 ) OR ( Bitand(recostatecode, 23) > 0 AND Bitand(recostatecode, 24) != 0 ) OR ( Bitand(recostatecode, 25) > 0 AND Bitand(recostatecode, 29) != 0 ) OR ( Bitand(recostatecode, 26) > 0 ) OR ( Bitand(recostatecode, 28) > 0 ) OR ( Bitand(recostatecode, 31) > 0 ) OR ( Bitand(recostatecode, 32) > 0 AND Bitand(recostatecode, 61) != 0 ) OR ( Bitand(recostatecode, 39) > 0 ) OR ( Bitand(recostatecode, 40) > 0 AND Bitand(recostatecode, 64) != 0 ) OR ( Bitand(recostatecode, 48) > 0 ) OR ( Bitand(recostatecode, 51) > 0 ) OR ( Bitand(recostatecode, 53) > 0 AND Bitand(recostatecode, 54) != 0 ) OR ( Bitand(recostatecode, 57) > 0 AND Bitand(recostatecode, 58) != 0 ) ); END; item_reco_recordset;

I am collecting errors data using the error_reported event and saving the data on XEL files. The problem is that there are hundreds of thousands of errors over there and querying them can take a lot of time. One option is to prevent errors from being logged twice if they happened on the same object in the same timestamp. I really don't think the second option is possible. Second option is to index the file in a way that it will be quick to filter errors by the error text and the object that caused them. So far I thought of creating a separate process that will run in the background and writes filtered values from the files to an indexed table, but its a bad solution. That process would need maintenance and could be expensive on IO resources. So far I didn't find a way to solve it on the extended event level. Does anyone have a better idea?

I have around 30 tables from which I need to generate a specific XML format, which is consistent through the tables. With my current solution I have a stored procedure for each 30 tables where I do: select '' + '' + '' + '' + '' + '' + '' + from dbo.table where the parantheses values are hardcoded for each table. So for example in one table (id column) is CUSTOMER_ID. While in a different table (id column) is STORE_ID. and (fieldname) is for example CUSTOMER_NAME. So they are all hardcoded column names in the brackets and primary key in . So my stored procedure with all 30 tables is around 1000 lines long, as every column and primary key is hardcoded for each table. I'm certain there is a dynamic way to achieve this functionality where columns/rows are somehow iterated over, but I can't figure it out.

Currently I have hundreds of sp that are in multiple servers, and that coexist with other sp that are in other servers with linked server. The origin of this is that when I do a restore of databases in development I need that these now point to their corresponding test version. Then I already created a script that searches in all the sp and saves in a table the sp with the modifications that I require. Now I can't manage to execute the sp to make the alterations of the SP.

-- Cursor variables
DECLARE @currentId INT;
DECLARE @currentSchema NVARCHAR(128);
DECLARE @currentName NVARCHAR(128);
DECLARE @currentScript NVARCHAR(MAX);
DECLARE @errorMessage NVARCHAR(MAX);
DECLARE @cleanScript NVARCHAR(MAX);

-- Start cursor
DECLARE procedure_cursor CURSOR FOR
SELECT Id, SchemaName, ProcedureName, AlterScript FROM AlterProcedures
ORDER BY Id;

OPEN procedure_cursor;
FETCH NEXT FROM procedure_cursor INTO @currentId, @currentSchema, @currentName, @currentScript;

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        EXEC(@currentScript);
        PRINT 'Updated ' + @currentSchema + '.' + @currentName + ' successfully.';
    END TRY
    BEGIN CATCH
        SET @errorMessage = 'Error in update:  ' + @currentSchema + '.' + @currentName + ': ' + ERROR_MESSAGE();
        PRINT @errorMessage;
        -- continue with the next even if there is wrong
    END CATCH
    
    FETCH NEXT FROM procedure_cursor INTO @currentId, @currentSchema, @currentName, @currentScript;
END

CLOSE procedure_cursor;
DEALLOCATE procedure_cursor;

--TRUNCATE TABLE AlterProcedures;
PRINT 'Process completed'

Result:

Error al actualizar dbo.SIIF_MotorMantenimientoFechaEjercida_sp: 'CREATE/ALTER PROCEDURE' debe ser la primera instrucción en un lote de consultas.

--> create/alter procedure' must be the first statement in a query batch