Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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

0
votes
1answer
27 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
42 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
28 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
41 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
33 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
29 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
44 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
59 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
32 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
44 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
89 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
102 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
64 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
49 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
1answer
645 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
172 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
83 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
162 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
86 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
147 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
198 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
50 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
111 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
194 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
77 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
52 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
306 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
25 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
253 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
222 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
230 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
364 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
71 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
292 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
729 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
37 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
67 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
176 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
44 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) ...
0
votes
1answer
278 views

How to get list of all tables in SQL Server without specifying a db?

I have a function that allows me to check if a table name is valid, that looks like this: SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @tbl But this has to be executed in the ...
0
votes
1answer
89 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, ...
3
votes
1answer
336 views

Making dynamic queries for linked server in SQL Server

I want to create a dynamic query in SQL Server which will run on linked server. I am trying to do it as follows. USE [MYDB] GO DECLARE @company AS nvarchar(50); DECLARE @id nvarchar(MAX); DECLARE @...
1
vote
1answer
418 views

Comprehensive troubleshooting of DatabaseMail

I am having a problem sending email from one of my servers. I want to use DatabaseMail and/or msdb.dbo.sp_send_dbmail I have other production servers that use all the same databaseMail settings ...
0
votes
1answer
56 views

Create view using tables from differing databases on a server

I need to create a view that pulls data from multiple databases on a server. I've done this by hard coding the database names (db1.schema.table), but I need it to be more dynamic. This view needs to ...