Questions tagged [exec]
17 questions
19
votes
3 answers
EXEC vs SP_EXECUTESQL Performance
Recently we used a sql code reviewing tool against our database. It is suggesting to use SP_EXECUTESQL instead of EXEC.
I know SP_EXECUTESQL helps us to avoid sql injection. Is there any difference in performance when using EXEC vs SP_EXECUTESQL.
Pரதீப்
- 1,409
- 5
- 18
- 37
8
votes
4 answers
Get @@SERVERNAME from linked server
This seems like a basic question but I can't find any answers out there - I need to be able to get the server name/instance etc. from a linked server. I've tried a couple of things:
select .@@SERVERNAME;
select
dwjv
- 679
- 2
- 9
- 15
5
votes
2 answers
Insert Into table Exec SP with bad performance
I am working on a datawarehouse. One of our staging tables that is refreshed every night has about 10 million rows. We are using a custom built ETL tool that I can't make too many changes to. The tool loads this staging table like this:
truncate…
Ryati
- 153
- 6
2
votes
1 answer
On a linked server I can execute a stored procedure A but Can Not execute stored procedure B
On a linked server I can execute a stored procedure A but Can Not execute stored procedure B
I have a db server DBServer and a linked server LinkedServer. From the DBServer I can execute successfully a stored procedure StoredProcedureGood like…
SqlStar
- 23
- 2
2
votes
1 answer
Check the result of a math expression in mysql
Let's say I have a table, which has four columns (a, b, oper and c) and some primary key column. oper means arithmetic operation (+ - * /) here.
a b oper c
-------------
2 3 + 5
4 2 / 3
6 1 * 9
8 5 - 3
As, we can see in…
vrintle
- 123
- 4
2
votes
1 answer
SQL Server: what additional permissions for EXECUTE for implicit result codes?
Tested on: 2014+SP2+CU7, 2016+SP1+CU5, 2016+SP2
Short description: User with EXECUTE permission still gets
Cannot find the object 'sp_fakeProcedureName', because it does not exist or you do not have permission.
when successfully EXECuting the…
Joe Mroczek
- 23
- 5
1
vote
1 answer
exec limited to 4000 characters?
trying to execute this script gives only the first select as a result
declare @sp nvarchar(max)
select @sp = concat(N'select 1 ', replicate('-', 5000), char(13) + char(10), N'select 2')
exec (@sp)
but when I lower hyphens to 1000, I get also the…
aldo kern
- 11
- 2
1
vote
3 answers
Must declare the scalar variable question
That is my first post here, need helps:
in a stored procedure I had some following code
CREATE PROCEDURE [dbo].[SP_getAvg] (
@projectId INT
,@carrierId INT
,@fetchType VARCHAR(20)
)
AS
BEGIN TRANSACTION GetDataSet * *
DECLARE…
Fred
- 21
- 1
- 2
1
vote
1 answer
Insert openquery results into an existing table
I have a stored procedure that queries a linked DB2 server in this fashion:
SET @sql='SELECT * FROM openquery(DB2,''SELECT column1, column2 FROM table'')'
exec sp_executesql @sql
I am trying to store that result into a new table via another stored…
blacksaibot
- 113
- 1
- 4
1
vote
3 answers
How to extract dynamically data from another database in function with DBName as parameter?
I have one main database (MAIN) and several client DBs (CLIENTDB). I need to start query in MAIN and to get data from CLIENTDB. The client DB can be found also on linked server. My problem is that I need to do this in a function with EXEC, so I can…
Bogdan Bogdanov
- 1,163
- 2
- 18
- 38
0
votes
1 answer
Why can I not capture dynamic SQL called with EXEC using the sqlserver.sql_batch_completed Extended Event?
It is my understanding that dynamic SQL is part of a batch. Yet, when I listed for sqlserver.sql_batch_completed as follows
CREATE EVENT SESSION [CaptureBatch] ON SERVER
ADD EVENT sqlserver.sql_batch_completed(
…
J. Mini
- 1,161
- 8
- 32
0
votes
2 answers
Exec An SQL query in a table in Database 'B' from database 'A'
i have this Table sequence_data in database A . The table has create script for sequences . No I want to create all these sequences in another database B. If it is the same database i can just use a while loop and execute this one by one using the…
Viz Krishna
- 109
- 8
0
votes
2 answers
How to combine multiple EXEC statements into a single SELECT?
I run this every month to extract database logs, and I have to change the dates as well.
DECLARE @start DATETIME
SET @start = CONVERT(DATETIME, '2020-08-01 00:00');
DECLARE @end DATETIME
SET @end = CONVERT(DATETIME, '2020-08-31 23:59');
DECLARE…
Fandango68
- 295
- 2
- 11
0
votes
1 answer
How to use variable in Exec statement
I'm trying to add my target server to the Multi Server SQ LAgent Job (MSX). We have multiple versions of SQL Agent jobs and I'm trying to automate adding only related targets according to SQL Server version. I'm trying to add target server using…
Ali
- 345
- 3
- 17
0
votes
1 answer
How can I stop script execution if insert value is null and set value if not null?
I have tried to use Case and SET NOEXEC ON but get the following errors:
Syntax Error: unexpected 'SET' (set)
Syntax Error: missing 'closing parenthesis'
INSERT INTO tag (table, repr, tag, value)
SELECT 'product' AS table,
@Id…
DBNewbie
- 1
- 3