I need to use the result of that temp table in my view.
Why?
Creating views on temporary tables is not allowed.
create table #foo(id int)
go
create view vfoo
as
select * from #foo
fails with
Msg 4508, Level 16, State 1, Procedure vfoo, Line 4 (Batch Start Line 9)
Views or functions are not allowed on temporary tables. Table names that begin with '#' denote temporary tables.
I have a stored procedure that runs quite frequently and is one of the top 10 CPU queries.
The procedure runs fairly frequently (every second). The procedure consists of two separate select statements. I have the parameter values that the procedure was compiled with at the beginning and when I run it with these values, for one of the tables, I get an Index Analysis with key search on the clustered index (both select statements involve the same table in which the index scan is done).
But when I get the code of the stored procedure in a SELECT statement and replace the values that are used in the stored procedure during compilation time, I get an Index Search and less write – the query is much faster when it is outside the stored procedure ?
@ I am having SQL Server 2008 R2 SP3
I'm trying to create a function that gathers the contents of select and executes, but I don't know how to call the method that executes the command without defining a model.
public async Task> ExecuteRelatorio((FromServices) GestaoContext context, (FromBody) PW_RelatorioCompleto model, (FromQuery) HeadersParameters parameters){
if ( !Settings.AlteraConnGestao( parameters.ConnectionString)) return BadRequest(new { titulo = "Erro!", mensagem = "Conexão invalida!"});
//SQL QUE DEVE SER EXECUTADO
string SQL = model.SQL;
//filtros
context.Database.ExecuteSqlRaw( @"DROP VIEW IF EXISTS ViewTemp" );
context.Database.ExecuteSqlRaw( @"CREATE VIEW ViewTemp AS " + SQL );
//MONTA AS CONDICOES DE FILTRO
var result = await context.GE_View.FromSqlRaw(SQL).ToListAsync();
return Ok(result);
}
There, in context, I am obliged to inform the model that it will return and how the sql changes its structure all the time it doesn't work.
Is there a way I can run without informing the model?
I tried context.Database.ExecuteSqlRaw but it only has a numerical value. I need something to restore sight.
I am using EF 3.1.2.
If I were to run the following INSERT INTO … EXEC AT on the linked server, the query runs fine when I make the jdbc (dbo) .testsp call
EXEC ('INSERT INTO (dbo).(Products) ((ProductName), (ProductDescription)) VALUES (?,?)', @ProductName, @PPOUnit) AT (linkedserver_DEV)
Now when I try to rename environment linked server using dynamic sql to accomplish this.
CREATE PROCEDURE (dbo).(testsp) @ProductName nvarchar(250), @ProductDescription nvarchar(250)
AS
DECLARE @query NVARCHAR(MAX)
DECLARE @database NVARCHAR(200)
SELECT @database = DB_NAME() -- Use this to append the name of the linked server when DEV linkedserver_DEV, TEST linkedserver_TEST
SET @query = 'EXEC (''INSERT INTO (dbo).(Products) ((ProductName), (ProductDescription)) VALUES (?,?)'',''' + @ProductName + ''',''' + @ProductDescription + ''') AT (linkedserver_' + @database +')'
EXEC sp_executesql @query
But if I use this call to dynamic sql stored procedure (dbo) .testsp I run into errors when there are special characters (& # 39 ;, /, ") in the values
The Stack Exchange network consists of 175 question and answer communities, including Stack Overflow, the largest and most trusted online community for developers to learn, share their insights, and develop their careers.
Visit Stack Exchange
I have a SQL server with numeric types for few columns.
When getting a table structure I have the following results:
Query:
SELECT *
FROM DB.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'TABLE'
AND DATA_TYPE = 'numeric'
The result is as follows (truncated):
CHARACTER_MAXIMUM_LENGTH NUMERIC_PRECISION NUMERIC_SCALE
NULL 2 0
It will be the value CHARACTER_MAXIMUM_LENGTH forever be NULL for a numeric? Does scale and precision not affect the character length of the value?
I am trying to send some backups of TDE-enabled databases from one server to another and I have a problem. Specifically, I receive the following error:
RESTORE FILELISTONLY FROM DISK = N''
Msg 33111, Level 16, State 3, Line 10
Cannot find server certificate with thumbprint '0x'.
Msg 3013, Level 16, State 1, Line 10
RESTORE FILELIST is terminating abnormally
So the first and most obvious thing to check: is there the server certificate? The answer is yes. This is what I ran on the origin server:
BACKUP CERTIFICATE TDE
TO FILE = 'C:TempTDE'
WITH PRIVATE KEY
(
FILE = 'C:TempPK'
, ENCRYPTION BY PASSWORD = 'xxxx'
);
And on the destination server:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'xxxx';
CREATE CERTIFICATE TDE
FROM FILE = 'C:TempTDE'
WITH PRIVATE KEY
(
FILE = 'C:TempPK',
DECRYPTION BY PASSWORD = 'xxxx'
);
If I run SELECT * FROM sys.certificates on both servers, I get identical result (except one additional column in schema: source is SQL2012, destination is SQL2016) and the fingerprint matches exactly what I see in the error.
So what am I missing? I've done it many times before with no problems, and countless blog posts seem to confirm my process. Has anyone ever had a similar problem?
I'm trying to search two different "places" and "events" post_type, but one parameter is about dates and places that don't have this goal.
Also, the event has 4 different dates: a start date and an end date AND a start date and a final post that are here to determine when the event can be displayed on the site or in the form results search.
How can I make this query clearer to search this type of two publications at the same time?
Here is the code that blocked the query because it is taking too long to search:
(PD. Sry for this bad English)
array_push($meta_query_array, array(
'relation' => 'OR',
array(
array(
'key' => 'start_date',
'compare' => 'NOT EXISTS',
'value' => ''
),
array(
'key' => 'end_date',
'compare' => 'NOT EXISTS',
'value' => ''
),
array(
'key' => 'start_date_publish',
'compare' => 'NOT EXISTS',
'value' => ''
),
array(
'key' => 'end_date_publish',
'compare' => 'NOT EXISTS',
'value' => ''
)
),
array(
'relation' => 'AND',
array(
'key' => 'start_date',
'value' => $current_date,
'type' => 'DATE',
'compare' => '<='
),
array(
'key' => 'end_date',
'value' => $current_date,
'type' => 'DATE',
'compare' => '>='
),
array(
'key' => 'start_date_publish',
'value' => $current_date,
'type' => 'DATE',
'compare' => '<='
),
array(
'key' => 'end_date_publish',
'value' => $current_date,
'type' => 'DATE',
'compare' => '>='
),
)
));
When I put ')); As input I will get this:
index album,albumdelta,faq,faqdelta,forum,forumdelta,picture,picturecomment,picturecommentdelta,picturedelta,post1,post2,post3,post4,postdelta,socialgroup,socialgroupdelta,socialgroupmessage,socialgroupmessagedelta,thread1,thread2,thread3,thread4,threaddelta,visitormessage,visitormessagedelta: syntax error, unexpected ')' near ''));'
when i look for this on my website: thisismorethan3'));WAITFOR DELAY '00:01';/*
The search term you specified ('00:01'/*) is under the minimum word length (3) and therefore will not be found. Please make this term longer.
If this term contains a wildcard, make it more specific.
while with thisismorethan'));/* I understand this:
The search term you specified (thisismorethan'));/*) is under the minimum word length (3) and therefore will not be found. Please make this term longer.
If this term contains a wildcard, please make this term more specific.
The following words are either very common, too long, or too short and were not included in your search:
thisismorethan'));/*
I can also inject an XSS through the SQLi error (with '));)
Good day.
I have the following problem:
I want to do an Update in a SQL table (InventarioLab) clicking on the button Drink when a row is selected in DataGridView. The update I want to be done in the column Status.
At the moment I don't have any code.
This is the interface:
I hope you can help me