Constructing a query at runtime with string concatenation operations and executing the query from that string.
0
votes
1answer
39 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 ...
2
votes
2answers
258 views
Pass data from a table row into a create login statement using dynamicSQL [closed]
I have a lab exercise that involves creating a script that uses dynamic SQL and a cursor to loop through each row of a table and then create a loginID and more.
We've done NO work concerning cursors/...
1
vote
2answers
65 views
Syntax error in SQL Server while making Dynamic SQL
Scenario:
I have a table with 100M+ rows of data so I have created an index with something as:
CREATE NONCLUSTERED INDEX FIBaseVoterWithDeletedAt
ON dbo.base_voter (name_voter,...
0
votes
0answers
49 views
Error in writing Dynamic stored procedure
I'm trying to write a Dynamic query to pass file name and table name to the procedure dynamically. what is wrong with this query?
ALTER Procedure [dbo].[OpenExcel]
@FileName nvarchar(2000),
@...
2
votes
1answer
42 views
Dynamically define a RETURN table (column type, name) for subsequent loop
I would like to execute ST_Intersection(clipper_geom, clipped_geom) on number of rows coming from a table.
https://postgis.net/docs/ST_Intersection.html
https://postgis.net/docs/ST_Intersects.html
...
0
votes
1answer
28 views
How I develop Union Report Views for Multiple Single Tenant Databases?
We have a database for each customer, over 20+ databases. All database schemas are the same. Now we want to create a Consolidated Reporting View for All Databases, for total database analysis. How ...
-2
votes
1answer
49 views
What is the Optimized Way to Dynamic SQL Select all Columns from Table?
I just generated this script.
It will select all columns from a table dynamically.
Is there an alternative or more clean, efficient way of conducting this?
Additionally, I want to place line breaks ...
1
vote
2answers
36 views
How to get max field value length for each TEXT field in all tables in database
I am trying to get information about the data in my database. I have several columns in several tables that or type 'TEXT'. I want to perform a query that will create a result that looks like:
|...
0
votes
2answers
44 views
how to run once a bunch of generated scripts with exception handling?
Before I restore a database, I need to save the contents of a table called dbo.ref_systemSettings if it exists.
So I created a script that calls a procedure that will save the contents of this table ...
1
vote
1answer
38 views
Create DDL Trigger Dynamically
I'm trying to create a DDL Trigger dynamically so I can create a job that checks each database and creates the trigger if it's not already in place. To do this, I'm using sp_foreachdb. The code works ...
1
vote
0answers
79 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 ...
0
votes
0answers
63 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 ...
2
votes
0answers
67 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
2answers
75 views
Can you use dynamic sql in a select statement?
I have the following code:
select
[schema].[name] [schema],
[table].[name] [table],
[column].[name] [column],
[type].[name] [type],
[column].max_length [...
0
votes
0answers
54 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
2answers
65 views
Dynamically choosing column for WHERE clause
I've got a table that holds columns named "Day1" through to "Day31".
Is there a way I can dynamically choose the correct column to put in a WHERE clause?
I can get the current day, that's no ...
0
votes
1answer
111 views
Inserting 3 lakh (300'000) records into a table [closed]
I have a table named transaction into which I want to insert 3 lakh (300'000) records. I need it to be created as dynamic query instead of writing thousands of insert statements.
For example :
...
3
votes
1answer
159 views
Execution time high for Query in sp_executesql
I have a query generated from a .net application and parameters are linq parameters. The procedure I followed to tune the query is as below.
I run the application and capture the query using profiler....
0
votes
1answer
79 views
How to get newly inserted row ids when inserting with execute?
I have a function which returns newly inserted row count:
BEGIN
EXECUTE (
SELECT format('INSERT INTO %1$s(%2$s) SELECT %2$s FROM %1$s WHERE budget_id = $1',
_tbl, ...
-1
votes
2answers
50 views
Dynamic SQL isn't updating table
So I need to basically get the count of every table in a database daily and insert those counts into a separate database for analysis and reporting purposes. The table the counts need to be inserted ...
0
votes
2answers
1k views
How to create a table dynamically based on json data?
I have a json that I get as a file, I have loaded that in the Postgres database.
Here is the json data:
{
"tablename": "test",
"columns": [
{
"name": "field1",
"datatype": "BigInt"...
4
votes
3answers
175 views
SELECT rows based on indefinite number of filters
How do I create a function which takes indefinite parameters? And then finds all game_ids in a table where each parameter matches a different row (but same game_id)?
Example
Table:
create table ...
0
votes
0answers
22 views
linked server issue while synchronising logins between servers using T-SQL
I have been working on a solution to synchronise logins (using T-SQL) between 2 servers, or between AlwaysOn nodes, inspired by sqlsoldier.
It requires a linked Server.
this is a partial view of the ...
1
vote
1answer
203 views
Transpose Rows to column
Can anyone Please help me to transpose below result
+------+------------+-------+
| SVR | dates | count |
+------+------------+-------+
| SRV1 | 03/25/2018 | 1 |
| SRV1 | 04/06/2018 | 59 ...
2
votes
1answer
298 views
Stored procedure with case statement for 'AND' based on parameter value
I have a table in my db that stored both successful and failed log-in attempts. I am creating a stored procedure that allow us to delete records which are older than X days old. So far so good, but I ...
1
vote
2answers
173 views
script all triggers in a database
I have a few databases involved in replication as subscribers and they have triggers and are used by a different set of applications.
Every time I need to reinitialise those database I need to script ...
0
votes
1answer
233 views
SQL SERVER dynamic Query with Like Statement for null values
I'm trying to execute a SQL dynamic query with nullable parameters using LIKE. If the parameter has a value, the query is successfully executed. But if parameter is NULL, query breaks returning only "...
2
votes
2answers
216 views
Safely quoting type names to protect against SQL-injection
How would I quote the type name to protect against SQL Injection. For example, take this
SELECT FORMAT('SELECT CAST(%L AS %s);', '5.42', 'int');
^ ...
4
votes
2answers
55 views
Add column for subsequent values for a record
I have a table structured as follows:
CREATE TABLE t (
HHID varchar2(1),
INDIV_ID int,
PRIM varchar2(1)
);
insert into t values ('A', 10, 'Y');
insert into t values ('A',...
0
votes
1answer
63 views
sp_executesql changes non standard characters to?
Am trying to use sp_executesql to run commands but I've come across an issue where in non-english characters (aka Chinese & Japanese) are changed to ??? when ran.
sql server 2008R2 & 2012
...
0
votes
1answer
157 views
Set columns dinamically in UTL_FILE
I've been stuck with this problem, so I'm looking for help now.
I'm trying to get a .cvs file from a query with PL/SQL. I'm using a cursor and UTL_FILE to achieve that. The problem is that I have to ...
0
votes
1answer
232 views
SQL Server 2017 Management Data Warehouse: collection_set_1 not working due to database trigger
The problem
I have got an issue setting up Management Data Warehouse on SQL Server 2017 CU 5. The job "collection_set_1_noncached_collect_and_upload" keeps failing. It is related to the "Disk Usage" ...
3
votes
1answer
83 views
Any way to make stored procedure code dynamically reference other databases, without dynamic sql?
I've a situation where I have two databases:
ABCData
ABCUsers
One of the stored procedures in ABCData needs to perform an insert to a table in ABCUsers. This could just be hard coded:
INSERT ...
3
votes
2answers
54 views
how to find the database context of the last sql statement?
I would like to find out from the code below,
what was the name of the database where the last command was run.
In this case I am looking for my_other_database.
his there any way of finding this?
Is ...
4
votes
2answers
434 views
How to quote qualified table name with format() function?
I have some problems in properly quoting a table name using the format function in the example below.
CREATE OR REPLACE FUNCTION copy_table(_source_tbl regclass, _target_tbl text)
RETURNS bool AS $...
1
vote
1answer
27 views
Build dynamic text search on table columns
With no success, I am trying to build a dynamic search text on a table with two columns (Id & Number, both columns are unique).
The Number column should be grouped and start a new section of ...
2
votes
1answer
78 views
Generating SQL with SQL [closed]
I'm trying to generate SQL statements using SQL to create SQL code that will be highlighted and run by an admin to process the statement. My issue is with the code I have created. It's not efficient ...
2
votes
2answers
361 views
Dynamic SQL Query with Paging, Performance and Optimization
I have an interesting problem in front of me. There is a database with ~1 million user accounts with an anticipated growth of 1-2 million per year. The database is strongly TPT but this particular ...
1
vote
1answer
300 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
244 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
2answers
404 views
SQL Server Extend dynamic SQL query with where part fetched from Stored Procedure
I have a stored procedure that creates a dynamic SQL Query. I use sp_executesql, to have it SQL injection safe.
I have a second stored procedure that generates a where logic.
For example if I run the ...
1
vote
1answer
122 views
Loop INSERT EXEC AT through linked servers
Relating to Inserting EXEC AT linked server into temp table
I am now in the midst of dynamically insert each result of dynamic sql to the temp table for each registered linked server. Below is my ...
1
vote
1answer
2k views
Using INSERT INTO…EXEC AT linked server into temp table fails with Msg 7391
I am using SQL Server 2014. I want to execute EXEC (@remotecmd) AT [server_name]; (@remotecmd is a dynamic sql not a stored procedure) into ##eapb. My code is
insert into ##eapb
EXEC (@remotecmd) AT [...
1
vote
1answer
379 views
How to return rows from multiple tables selected from information_schema
I need to return rows from a set of tables found in information_schema. I can find the table names, and I have a function that compiles and executes, but it returns an empty set, so there must be ...
0
votes
1answer
2k views
metadata could not be determined because stored procedure contains dynamic SQL
I have a stored procedure that contains some dynamic SQL. I am trying to execute the stored procedure using OPENROWSET but am getting the error:
Msg 11514, Level 16, State 1, Procedure ...
0
votes
3answers
914 views
Dynamic conversion of long data to wide with multiple columns
I'm trying to convert long data:
ID|SchoolID|Section|RepScore|SportsScore|PartyScore
1 |20 |1 |23.2 |70.2 |42.3
2 |20 |6 |65.2 |75.8 |52.3
...
0
votes
1answer
38 views
Special letter behaviour problem?
I asked question in very blurry manner because I cannot explain in short sentence what is my problem about.
In general I have a set of procedures which transforms text and XML. Inside the procedures ...
1
vote
2answers
75 views
Execute dynamic query inside procedure
I'm trying to build a procedure as follows:
CREATE PROCEDURE [test].[ZTEST1]
@Messages NVARCHAR(4000)
AS
SET NOCOUNT ON;
BEGIN
Declare @SQL nvarchar(1000);
SET @SQL = 'SELECT parsename(replace(...
0
votes
1answer
226 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 ...
1
vote
1answer
47 views
Function taking a string as table and column name
I'm trying to write a function that will create a new column in a participation table based on the presence of a id in survey_name table:
CREATE OR REPLACE FUNCTION survey_participated(_sn text)
...
