Questions tagged [dynamic-sql]

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

Filter by
Sorted by
Tagged with
0
votes
1answer
878 views

Dynamic SQL gives "Incorrect Syntax Near '/'

I am looking for some assistance with this code. This code builds the proper command line syntax and executes fine. However, when running via TSQL I get Incorrect Syntax near '/'. */ /*************...
4
votes
2answers
2k views

Safe alternative to exec(sql)

I've found some exec(sql) statements buried in the code. They're there for good reason, because these statements couldn't be written directly, however they are an obvious attack vector. Is there a ...
2
votes
1answer
1k views

Problem with EXEC MyLinkedServer…sp_executesql within transaction

I rather clueless about this so any help is appreciated. We have got a stored procedure that is running within a transaction. The stored procedure is called from a .NET program (via normal ADO.NET). ...
2
votes
1answer
2k views

permissions for yet another “ORA-06512: table or view does not exist” scenario

See the code below. I'm running the code with user x in schema y. The exact same code works if I run it by user x in schema x, but not with user x in schema y. I get the error ORA-06512: table or ...
1
vote
2answers
1k views

Applying a rule dynamically to all columns in different tables

I am trying to apply a rule to remove double quotes from any value in my DB. I declared the table name as an Input parameter (This is required). Now I want the rule to run dynamically against all ...
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 ...
1
vote
1answer
3k views

generating scripts - Incorrect syntax near 'GO'

this is a partial view of my script to generate the drop index for a particular index ,[DropIndexScript] = 'DROP INDEX ' + QUOTENAME(SI.name) + + CHAR(13) + ' ON ' + QUOTENAME(...
1
vote
1answer
696 views

Creating a View using a query created by sp_executesql

I posted a question about creating a dynamic sql statement and got the following code which fulfills this task: DECLARE @TableName nvarchar(400) DECLARE @DynSQL nvarchar(MAX) Set @DynSQL = '' ...
3
votes
1answer
7k views

Create a view that dynamically UNIONs new tables

I have a database named DDOS_WAF_REPORT that has multiple tables with the name format dbo.DDOS_WAF_ACCOUNT_###### (a 5 or 6 digit ID number). All the tables have the exact same structure (both column ...
5
votes
3answers
13k views

what is causing arithmetic overflow in the query below?

when I run the following query on MY_Database select * from sys.sysfiles I get the following results: But when I run a dynamic query that gets the percent of free space I get: Msg 8115, Level ...
2
votes
1answer
546 views

Stuck at Dynamic SQL execution in a procedure

I'm learning PL/SQL and using Oracle 11g as my Database and HR as my schema for practice. There's one scenario where I'm stuck and not sure why it's not working. The requirement is: "Create a ...
3
votes
1answer
4k views

Assignment of a column with dynamic column name

I get the name of a column to set in a (BEFORE UPDATE) trigger, I want to set it to the OLD value and ignore anything coming in. I've tried the following: CREATE OR REPLACE FUNCTION ...
0
votes
1answer
142 views

A short way to execute a dynamic SQL string

I have a table, and a column with a query in it. And I want to update this table, converting the value to the result of it's execution. This is a well known example of such table actually: ...
1
vote
2answers
83 views

Get the Text of the currently running SQL?

This is something I am just interested in knowing if it is possible. Could I write an SQL query that could return the text of itself as a variable? Where I might use this would be on things like ...
1
vote
0answers
501 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 ...
0
votes
2answers
103 views

Search entire database

Recently I was working on the database of an application that had no documentation and the database did not have any foreign keys. I needed to create a script to enforce a 10 year retention policy. It ...
1
vote
3answers
2k views

Finding count of null values in 500 columns of a single table

I have 500 columns in a table in SQL Server. I want to find the count of null values in each column. Is there a way to do it using a cursor and dynamic SQL?
2
votes
1answer
5k views

Wildcard search using parameters in function with dynamic SQL

What is the proper way to implement a wildcard search in PostgreSQL when using a parameter in a function that uses dynamic SQL? As a starting point, here is an example from Erwin Brandstetter ...
3
votes
1answer
2k views

OPENQUERY and server permissions

when I run my stored procedure it is a complete success, even if I run it through exec. Running these: sp_GetServerLogins GO exec ( 'sp_GetServerLogins ') GO I get this: Needless ...
2
votes
1answer
96 views

Query Each Database in A Table [closed]

We have it set so that any database that shows an error code during the server scan is set to write the database name into a table. What I want to do is then have an email generated that will send the ...
1
vote
2answers
85 views

Building a where clause based on user selections

My group and I are developing a multi agent system (MAS), a system that is supposed to conduct statistics on specific fields of the agents' databases using semantic search and ontology (the user ...
0
votes
1answer
342 views

how to send email message with the results of a job?

I have the following update, that creates a table backup in a database called tablebackups with the records that are going to be updated, and then do 2 different updates. USE ...
3
votes
2answers
5k views

Dynamic Creation of Table using Stored Procedure Without a Use of OpenRowSet in SQL Server

Q.--> An alternative of OpenRowSet in SQL Server Description: I have created a procedure which uses Dynamic PIVOT to get a result. I have a table which contains column DateRange with values Jan-2014 ...
2
votes
0answers
272 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 ...
1
vote
1answer
100 views

SQL Stored Procedure with one parameter and several variables that will change over time

I am attempting to build a stored procedure with variables that will change over time. on SP execution (Right clicking SP in programmability under the database and execute) results in Error output: ...
1
vote
2answers
810 views

How can I pass a list of databases to sp_MSforeachdb?

I have the following script that when run gives me a comma delimited list of database names. SET NOCOUNT ON declare @db_list NVARCHAR(MAX) DECLARE @command varchar(1000) ...
4
votes
2answers
1k views

Find changed columns for near-identical rows across tables

I run PostgreSQL 9.3.5 on Windows 7, 64-bit. My data arrives quarterly, in multiple tables (table1, ..., tableN) that are linked, intra-period, by cross-table constraints based on key identifiers. ...
8
votes
2answers
5k views

Generate script to automate renaming of default constraints

Background: Some of our default column constraints were generated without explicit names, so we get fun names that vary from server to server like: DF__User__TimeZoneIn__5C4D869D I would prefer to ...
15
votes
1answer
1k views

User-shared queries: Dynamic SQL vs. SQLCMD

I have to refactor and document a number of foo.sql queries which will be shared by a team of DB tech support (for customer configurations and things like that). There are types of tickets that come ...
1
vote
1answer
2k views

How to run ALTER TABLE on a list of tables sequentially in a function

I have a schema (called import) where data are imported as tables, and a function that processes them all. After that I want to move these tables away in another schema (called data_archive). The ...
6
votes
4answers
4k views

Copy indexes from one table to another

I have a series of ETL jobs in which I create a swap table using CREATE TABLE table1_swap LIKE table1. In order to make the populating of table1_swap faster I do not include the indexes. When I'm ...
7
votes
3answers
5k views

Dynamically convert hstore keys into columns for an unknown set of keys

I have a database that stores a bunch of custom fields using hstore. In order to merge it into another database that doesn't support hstore, I'd like to split the keys into extra columns. Users can ...
1
vote
3answers
176 views

Copy table structure excluding nullability into local temporary table

How can I copy the structure of a table into a temp table (or table variable) excluding the nullability of each column? Mainly I need to copy the column names and data types, but not nullability, keys,...
2
votes
1answer
6k views

How to use values from table column as field names in SELECT query?

Scenario: Field names of Table1 named as testtable id,name,size,width,height Field names of Table2 named as errortable id,desc,field1,field2,operator Values of errortable +----+----------------...
2
votes
1answer
121 views

looking for a cleaner way to select from multiple tables

I'm currently using this code to select from 2 tables using a stored procedure. DECLARE @now as varchar(26) set @now = CONVERT(VARCHAR(26), SYSDATETIME(), 9) --Select 'SMD_' + replace(convert(...
10
votes
1answer
31k views

Stored Procedure to return dynamically created table data

Quick back story, we are working with an outside vendor that has a survey system. The system is not necessarily designed the best in that when you create a new survey and the system creates a new ...
0
votes
2answers
765 views

Create View For All Table Names Listed In Table

I know this data structure is morbidly incorrect, but I am playing with the cards I have been dealt as I can not go back and re-write the structure. I have a table that holds table names, I need to ...
6
votes
1answer
5k views

triggers - using the inserted / deleted tables in Dynamic SQL

Within a trigger, I'm trying to create a unique table name (using the NEWID()) which I can store the data that is found in the inserted and deleted tables. Declare @NewID varchar(50) = Replace(...
3
votes
2answers
6k views

Dropping a group of schemas with similar name patterns

Consider a situation where one need to perform a bunch of essentially identical operations, with the only variable being the name of some object. In my case, I need to drop some schemas, all of the ...
2
votes
2answers
1k views

Union tables by mask in PostgreSQL [closed]

In database I have many tables like with names: "t_2015_09_01_z15" "t_2015_09_02_z15" "t_2015_09_03_z15" "t_2015_09_04_z15" "t_2015_09_05_z15" ... "t_2015_10_01_z15" "t_2015_10_02_z15" "...
-1
votes
1answer
1k views

Job successfully execute Stored Procedure but no results [closed]

I have stored procedure which is using dynamic query and inserts details of severs into table. If I execute stored procedure manually it works fine. but if try to execute the same with JOB, Job ...
2
votes
2answers
687 views

Database design for web mail client

I am implementing a web-mail application.Only registered users can send, receive and view E-mails. When I start to design the database,I came up with following issue. Lets say this application has ...
3
votes
2answers
58k views

Dynamic pivot: sum of sales per month

My table is like this: Sitecode Month Amount -------- ----- ------ XX Jan 1000 XX Jan 3000 XX Apr 3000 XX Apr 1000 What I want is, to show ...
2
votes
1answer
2k views

Can't execute dynamic DDL, argument is NULL [closed]

I'm running PostgreSQL 9.4.4. I'm new to Postgres (used to Oracle & SQL Server) so I might be doing something really silly. I'm trying to dynamically create partitions for a table. I've got the ...
0
votes
1answer
1k views

Dynamic SQL To Send HTML Email

I am creating a cursor to iterate the values stored in my table, then attempting to use dynamic sql to set the select statement for my html body email. However, anytime I try to execute my statement ...
2
votes
2answers
520 views

Dynamic Pivot SQL table [duplicate]

Table dbsize loading with data of every database size every night. dbsize (id, database_name, database_size, insert_date) It looks like this: - 1 dbname1 1000 1/1/1900 - 2 dbname2 ...
0
votes
1answer
11k views

Create a table dynamically in SQL Server

I am trying to create a table dynamically depending upon current date in SQL Server. But I am unable to do so. I am getting error as invalid identifier. These are the details: SQL query: DECLARE @...
3
votes
1answer
156 views

Trigger function using current row and current table name as variables (final part)

Like detailed in my first question I have a Postgres 9.1 database with multiple tables that have the exact same column names, they only vary in their column values: tbl_log_a tbl_log_b tbl_log_c ... ...
0
votes
1answer
1k views

Trigger function using current row and current table name as variables

Like detailed in my first question I have multiple tables with identical layout in a Postgres 9.1 DB. They only vary in their column values: tbl_log_a tbl_log_b tbl_log_c ... 26 tables (from a to z)....
3
votes
3answers
3k views

PostgreSQL trigger function that selects from %current% table

I have multiple tables that have the same column names, they only vary in their column values, like: tbl_log_a tbl_log_b tbl_log_c ... 26 tables from a to z. Each table has a trigger that calls a ...