Questions tagged [dynamic-sql]

Constructing a query at runtime with string concatenation operations and executing the query from that string.

33 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
4
votes
2answers
1k views

Update query in a trigger

I have a configuration table: Table: refdatecol id int(11) startdatecol varchar(100) enddatecol varchar(100) datecolTable varchar(100) This table holds the user configured column names ...
2
votes
0answers
148 views

Plan Guide for the LINQ-SQL Query

I have a query which is slow and it takes 15-20 sec when i run from the application.So i captured that query using SQL profiler and executed on SSMS with OPTION(RECOMPILE) and the query runs faster in ...
2
votes
1answer
154 views

Inserting to a table when everything is parameterized

I need to grab data stored on a remote AS/400 file system to which I connect using a linked server connection, so I can send OPENQUERY select statements and receive the data on SQL Server. I don't ...
2
votes
0answers
260 views

security concerns over enabling data access for the current server

Included in the code of the creation of some stored procedures, I have the enabling of data access as you can see below. BEGIN TRY PRINT @@SERVERNAME PRINT DB_NAME() PRINT 'enable the ...
2
votes
0answers
382 views

plan_generation_num keeps incrementing, why?

I have a query that is executed using sp_executesql code is as follows DECLARE @rsk NVARCHAR(1) = '1', @ppCityID NVARCHAR(3); SET @ppCityID = CAST(@cityID AS NVARCHAR(3)); exec sp_executesql N'...
2
votes
1answer
347 views

Schema for multiple identical queue tables

I am migrating a message processing application that was built on MSMQ to SQL Server 2012 (preferably Standard Edition). Messages are received and distributed via TCP/IP endpoints and the typical ...
1
vote
1answer
260 views

How to create and fill multiple session temp tables with dynamic SQL?

I have a column that holds JSON data. The table can be filtered for sets of similar JSON data that will have mostly identical JSON keys in the JSON column. I can dynamically collect the set of JSON ...
1
vote
1answer
175 views

How to send parameters to BCP inside dynamic sql using sp_executesql

We are trying to run a BCP-query dynamically to put some data to files. However, when using the following code, it does not seem that sp_executesql understands that @RId inside the BCP query is a ...
1
vote
0answers
307 views

How to run a dynamic select query with column names from another query?

I'm new to Postgres, so I'm sorry if this is overly simple. But I've searched for ages, and can't find a solution for my dynamic-sql problem. I have one table that contains the list of columns in a ...
1
vote
0answers
344 views

SQL Buffer Pool and Plan cache

Does SQL Buffer Pool size = Max server memory.? I have been trying to find out the reason why plan cache in my SQL server 2016 gets cleared every few hours. I have 128 GB Physical Memory. Earlier i ...
1
vote
1answer
628 views

How to specify with a parameter a field to extract from a RECORD variable

Here's a table: CREATE TABLE t_heights (id INT, height REAL); INSERT INTO t_heights VALUES (1,53.63), (2,45.19), (3,47.06); Reading through the table rows with a FOR...LOOP and a RECORD type ...
1
vote
0answers
385 views

MySQL dynamic number of columns

I'm looking for a way to dynamically build up the number of columns in a query. Imagine a school assessment database. I desire a way to get, for each question the percentage of students who got a ...
1
vote
0answers
69 views

listing the permissions for a database object - and take into consideration it can be a synonym

I have a script that gives me all the permissions assigned to a database object: ------------------------------------------------- -- permissions to database object -- marcelo miorelli v. 20170401 ---...
1
vote
0answers
426 views

BULK INSERT ERRORFILE Not Created

We have stored procedure on SQL Server v12.0.5203.0. Within that procedure we have a dynamic SQL statement that performs BULK INSERT. We are trying to capture misformatted rows and continue processing ...
1
vote
0answers
45 views

Dynamic content update problem

I have three tables: forms which contains basic information about a form form_parts which contains information about a certain input field and has a FK to forms (each form parts must have one ...
1
vote
0answers
56 views

Are there any pitfalls to be aware of when using dynamic type-coercion in SQL Server?

I want to insert into a database with a query that looks something like the following: INSERT INTO <table name> (<set of column names>) VALUES (<set of corresponding values>); As I ...
1
vote
0answers
35 views

Dynamically determine the starting time and ending time for a. . . Breakfast of Champions!

Dynamically determine the starting time and ending time for a between statement based on the results of a previous query's table records when ordered appropriately for TOP and BOTTOM records ...
1
vote
1answer
96 views

How do I generate scripts to drop indexes, constraints, and keys for a database to change columns from int to bigint?

I've worked myself into a corner, and I'm not sure how to handle this. I have a large set of databases with generally the same structure. Each database has a list of ID columns that are currently ...
1
vote
0answers
479 views

Select where table and column name are from another table

I know I need dynamic sql, but haven't gotten this to work: I want to loop through a table and for each row, return the value of the field that is located by two columns for that row. Master ...
1
vote
0answers
1k views

Partly rotated table with pivot/dynamic SQL without aggregation

Using MS SQL 2008, I have created a table that I want to display in horizontal direction instead of vertical direction. Because I do not now how many query results I will get returned I think I have ...
0
votes
0answers
13 views

PostgreSQL multi-pivot with keys as column names rather than categories?

I am trying to use crosstab() to generate a tricky pivot which is not really a true pivot. I am looking to get the key names as column names rather than the category. E.g. create table pivot (id ...
0
votes
1answer
29 views

How to select maximum date_created value from all tables matching certain pattern?

Given a MySQL DB with a hundreds of tables with the ones of interest being named ap_form_* where * = a random value. How do I loop through that list of tables to display the MAX(date_created) for each ...
0
votes
1answer
65 views

postgres dyanmic and unlimited where conditions

How would one properly write a FUNCTION in postgres to return a TABLE based on a query where the query WHERE clause can have both an unknown number of filter conditions, and the conditions themselves ...
0
votes
0answers
193 views

SSIS: Using Event Viewer to Trace Errors of A Package

Overview A package was developed due to a planned database server migration, and other teams with a dependency on legacy cross-database queries. There is a policy of not having Linked Servers, so an ...
0
votes
1answer
382 views

How to Alter and add a suffix “DO_NOT_DROP” to several tables using PL/SQL LOOP

Say I have table A(see below) which has details of all the tables that has to be renamed. Also, what if the table does not exist and how to handle exception and find out how many of the tables were ...
0
votes
1answer
188 views

Comparing multiple tables on two databases using dynamic PL/SQL

The issue I'm having is I have a block of dynamic SQL that I can't get the results from. Here's the SQL: CREATE OR REPLACE PROCEDURE GGCHECKER IS cursor c1 is SELECT TABLE_NAME, ...
0
votes
2answers
2k views

Fill in a table name using a variable from another query

I have a general query I need to run for many tables SELECT sum(honoraria_amount) FROM table_1 The table IDs I have a list, from another query. For example, I want to run this for: table_1, table_39,...
0
votes
0answers
1k views

How to create Dynamic table in stored procedure?

We want call a Stored procedure which returns 4 tables in my Stored procedure.To create dynamic table in my sp I am using OPENROWSET. While executing my new sp i am getting Column name or number of ...
0
votes
1answer
178 views

Get the deleted value using while looping through the columns

I wrote a trigger to do auditing. To capture all the changes, I loop through the list of columns. The problem I'm having (see code below), when I try to set the OldValue using the @fieldname, I get ...
0
votes
0answers
371 views

Reviewing ways to generate monthly tables using PL/SQL automatically

I am attempting to automate a set of tables that my colleagues produce monthly, using PL/SQL. The set of tables contain data about different metrics (e.g. Customer revenue, customer demographic ...
0
votes
0answers
950 views

Run a specific SQL Server job step from a stored procedure

I have a job with 5 steps that are scheduled to run after each other. I want to have a stored procedure where a user can execute the stored procedure and give the step number as a parameter. Only ...
0
votes
0answers
371 views

Execute SQL at different server (with dynamic server name)

I'm using a settings table to configure on which servers a specific kind of data is stored, and I would like to do this as dynamically as possible without having to add the server as a linked server ...
0
votes
1answer
23 views

Executing an oracle variable string as I do in SQL Server

In SQL Server I can do something like: DECLARE @VAR VARCHAR(100); SELECT @VAR = SELECT * FROM SYS.DATABASES EXECUTE(@VAR) How can I execute a string that I've created inside a variable in Oracle? ...