I am not a fan of triggers and dynamic sql, however, what I am working with requires both. ``` CREATE TRIGGER [dbo].[GenerateDynamicFormItemViews] ON [dbo].[tblFormItems] AFTER INSERT, UPDATE AS BEGIN SET NOCOUNT ON; DECLARE @DatabaseName NVARCHAR(100) DECLARE @FormItemID INT DECLARE db_cursor CURSOR FOR SELECT SourceID,FormItemID from Inserted OPEN db_cursor FETCH NEXT FROM db_cursor INTO @DatabaseName, @FormItemID WHILE @@FETCH_STATUS = 0 BEGIN BEGIN TRY EXEC spAddEditFormItemView @FormItemID, @DatabaseName WITH RESULT SETS NONE END TRY BEGIN CATCH INSERT INTO AdminErrorLog(SourceID, ErrorNumber, ErrorState, ErrorSeverity, ErrorProcedure, ErrorLine, ErrorMessage, ErrorDateTime) SELECT @DatabaseName, ERROR_NUMBER(), ERROR_STATE(), ERROR_SEVERITY(), ERROR_PROCEDURE(), ERROR_LINE(), ERROR_MESSAGE(), GETDATE() END CATCH FETCH NEXT FROM db_cursor INTO @DatabaseName, @FormItemID END CLOSE db_cursor DEALLOCATE db_cursor END ``` Here is what is happening. - An AWS DMS Replication Task is replicating data in batches of 10,000. The implementation of the replication is a black box but there appears to be nested transactions on the connections. - EXEC spAddEditFormItemView calls a stored procedure that exec's dynamic sql and errors out on one record. - The CATCH block is never executed - The error never makes it to AdminErrorLog. - The AWS Batch errors our and is never committed and the whole task fails. - In extended events the following sql error is caught. This is the error that failed the task. I read about conditions where commands can neither be in a commit or rollback state but truly did not fully understand that concept. message: The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. severity: 16 Can anyone explain why the catch

I am running into a strange problem with dynamic sql that I believe is some sort of formatting issue, and I've been tearing my hair out the last few hours over this. I would really appreciate any assistance I can get. For some background information the purpose of this stored procedure is to clean up data in our centralized (hq) database that is supposed to have a complete copy of all of the records for all of our stores. If it matters, we are running Microsoft Dynamics RMS (2.0.2022) and SQL 2014 Standard (For HQ, the stores use Express). Initially I was going to go with putting the sql statement in a parameter and using the exec command, however while I don't use it a lot I think sp_executesql looks a lot cleaner and it is easier to reuse queries as templates and make changes to them. Right now I just have a few select scripts, but once they are finalized I am going to create an additional stored procedure to insert, delete, and update records. Executing the sql statement works, but for whatever reason I cannot get sp_executesql to work with the parameters in the stored procedure. No matter what I change the parameter to, including using a static value in the stored procedure, it just outputs the parameter name. So regardless of: ``` @LS = @LinkedServer ``` OR ``` @LS = 'StaticLinkedServerName' ``` The error message it returns is: Could not find server '@LS' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers. Non Working Solution ``` CREATE PROCEDURE [dbo].[usp_bn_ShowStoreHQSync] ( @StoreCode NVARCHAR(10) ,@LinkedServer NVARCHAR(50) ,@StoreDB NVARCHAR(40) ) AS BEGIN BEGIN TRANSACTION BEGIN TRY IF @StoreCode IS NULL RAISERROR('@StoreCode Cannot Be NULL',15,1) ELSE IF @StoreCode NOT IN (SELECT StoreCode FROM [HQDB].[dbo].[Store])

The following is a dynamic filtering solution that uses sp_executesql ``` IF OBJECT_ID(N'dbo.GetOrders', N'P') IS NOT NULL DROP PROC dbo.GetOrders; GO CREATE PROC dbo.GetOrders @orderid AS INT = NULL, @custid AS INT = NULL, @empid AS INT = NULL, @orderdate AS DATE = NULL AS DECLARE @sql AS NVARCHAR(1000); SET @sql = N'SELECT orderid, custid, empid, orderdate, filler' + N' /* 27702431-107C-478C-8157-6DFCECC148DD */' + N' FROM dbo.Orders' + N' WHERE 1 = 1' + CASE WHEN @orderid IS NOT NULL THEN N' AND orderid = @oid' ELSE N'' END + CASE WHEN @custid IS NOT NULL THEN N' AND custid = @cid' ELSE N'' END + CASE WHEN @empid IS NOT NULL THEN N' AND empid = @eid' ELSE N'' END + CASE WHEN @orderdate IS NOT NULL THEN N' AND orderdate = @dt' ELSE N'' END; EXEC sp_executesql @stmt = @sql, @params = N'@oid AS INT, @cid AS INT, @eid AS INT, @dt AS DATE', @oid = @orderid, @cid = @custid, @eid = @empid, @dt = @orderdate; GO ``` On p 541 of T-SQL Querying, it says Because the dynamic code uses parameters rather than injecting the constants into the code, it is not exposed to SQL injection attacks. How does the use of parameters in sp_executesql protect against SQL injection? Thank you

Is it possible to expand a field value into a comparison operator? Something like this: ``` create table math ( value1 int, value2 int, operator text ); insert into math values(1,2,'>='); select * from math where value1 operator value2; ``` PS: I know that it is possible to solve this use case by means of `case when`, but want to know if there is an alternative solution.

I am trying to create a function which will dynamically create a unique table name, populate the table with a result set returned from another function, and return the table name to the caller. In outline, it looks something like this: ``` CREATE OR REPLACE FUNCTION return_result_table( _param1 integer[], _param2 text[]) RETURNS text LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ DECLARE _table_name TEXT; _select TEXT; BEGIN _table_name := '_XYZABC_' || replace(current_date::text,'-','_') || '_' || 'ZXCVBN'; -- not the real code _select := '(select * from some_other_function(_param1, _param2))'; execute 'create table some_schema.' || _table_name || ' as ' || _select; return _table_name; END; $BODY$; ``` The code is accepted, and it appears to be forming the command correctly as ``` execute 'create table some_schema._XYZABC_2020_10_07_ZXCVBN as (select * from some_other_function(_param1, _param2))' ``` but when I try to execute it with: ``` SELECT return_result_table(ARRAY[0,1,5,19],ARRAY['AA,'BB','CC']) ``` I get the error ERROR: column "_param1" does not exist LINE 1: ...ect * from return_result_table(_param1... It appears that the code is trying to substitute in the value of a column (of what?) rather than using the parameter. How can I prevent this translation happening, so that I can pass the parameters through into the called function?

Is there a definitive list published showing what can and cannot be parameterized in a call to sp_executesql. For example, predicates can, table names cannot. Number of rows in TOP can: ``` exec sp_executesql @stmt = N'SELECT TOP(@n) a, b FROM (VALUES(1,2)) v(a,b)', @params = N'@n int', @n = 10 ``` MAXDOP cannot: ``` exec sp_executesql @stmt = N'SELECT TOP(10) a, b FROM (VALUES(1,2)) v(a,b) OPTION (MAXDOP @n)', @params = N'@n int', @n = 10 ``` Msg 102, Level 15, State 1, Line 8 Incorrect syntax near '@n'. I suppose there are many more examples of what works and what doesn't. I'm looking for an authoritative list to eliminate trial and error.

If local temp tables are only available to the current session, and sp_ExecuteSql creates a new session to execute the dynamic SQL string passed into it, how can that dynamic SQL query access a temp table created in the session that executes sp_ExecuteSql. In other words, why does this work: ``` SELECT 1 AS TestColumn INTO #TestTempTable DECLARE @DS NVARCHAR(MAX) = 'SELECT * FROM #TestTempTable' EXEC sp_EXECUTESQL @DS ``` Results: My understanding for the reason why I can't do the opposite (create the temp table in Dynamic SQL and then access it outside the dynamic SQL query in the executing session) is because sp_ExecuteSql executes under a new session.

Consider: ``` Declare @stringsvar varchar(1000) Declare @Emp_id int DECLARE @strvar SYSNAME = 'Employee_test' SET @stringsvar = ('select * from' + ' ' + @strvar + ' where emp_id' + ' =' + @Emp_id) Print @stringsvar ``` The above query is giving an error as mentioned below: Msg 245, Level 16, State 1, Line 17 Conversion failed when converting the nvarchar value 'select * from Employee_test where emp_id =' to data type int. What needs to be done in this case?

Let's say that I'm making an IRC bot, and in addition to all the other stuff that should be stored in the DB, I want to log all the messages in each channel in which the bot is present. I already have a `channels` table with columns `id`, `channel_name`, and some other info. For logging each message, what seems the most sense to me is to create a `messages_channelname` table for each channel, and in there store the message along with whatever other information is associated with it. That elicits a problem, though - the name of the table is now dissociated from the channel's `id` (although I could alleviate this by making the table name `messages_channelid`). I feel like the best practice here, rather than what makes the most sense, would be to have a master `messages` table that contains the messages from all channels, with columns `channel_id`, `id` and `message`, where `id` would be the index of the message per channel. But then I'd have (unfounded) concerns about table size and possibly search speed. What's the best way to go about this?

I need to copy a record from a Database `DBa`, Table `Tbl1` to Database `DBx`, Table `Tbl1` and get the `Scope Identity` inside the same `SQL Server 2005` server I have already checked this: How to Dynamically change the database using TSQL but my issue is more complicated. I do want just to execute something in the `DBx` being inside `DBa`. I want to copy a record from one database to one of the other (same) databases in the same server and get the scope_identity back. The Dynamic SQL is a string. What do we do when we want to copy variables of other datatypes as well? Example code: ``` CREATE PROCEDURE dbo.pr_consolidation_copy_group @group_id numeric(10,0),@database_to varchar(100) AS DECLARE @group_name char(100),@arrival datetime,@departure datetime,@contact_id char(82) SELECT @group_name = group_name , @arrival = arrival, @departure = departure, @contact_id = contact_id FROM grp WHERE group_id = @group_id DECLARE @exec nvarchar(max) = QUOTENAME(@database_to) + N'.sys.sp_executesql', @sql nvarchar(max) = N'INSERT grp(group_name, arrival, departure, contact_id) SELECT @group_name, @arrival, @departure, @contact_id;SELECT SCOPE_IDENTITY()'; //How do we handle the variables? EXEC @exec @sql; go ``` Get all variables to string and put CONVERT inside the Dynamic SQL? I hope I can find another solution because my table is around 300 columns :(