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 ...
