sql server: get the full text of a long request

This may be due to the maximum number of characters that the result to the grid can return, 65535.

enter the description of the image here

You cannot change this to be higher than this number.

The text data type of sys.dm_exec_sql_text is nvarchar(max), there are no problems there.

You can convert the column to XML as an alternative solution

SELECT CAST(t.(text) AS XML)
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t

Or a better method by Evgeniy Gribkov

SELECT t.(text) 
FROM sys.dm_exec_requests AS r 
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t 
FOR XML RAW, ELEMENTS;

Better this way: SELECT t. (Text) FROM sys.dm_exec_requests AS CROSS
APPLY sys.dm_exec_sql_text (r.sql_handle) AS t FOR XML RAW, ELEMENTS;
Since the request text cannot always be converted to XML. In
in particular, you cannot convert the query text when it is called stored remotely
procedures: "XML analysis: line 13, character 129, illegally qualified
character name "

Or save the results in a file

enter the description of the image here

partitioning: SQL Server query analyzes more partitions than expected

Reasoning

When comparing the column of the unpartitioned table with the partitioned table, the sql server may not know DayOfMonth It will remain, even if all are 15.

As a result, I would not know what partitions to return to satisfy this filtering by joining the two tables.

A different example to give more information can be found here.


Tests

I was able to recreate your problem, for more questions, add as much information as possible.
This could be table definition, indexes, partition scheme, …

The DDL and DML is at the bottom.

When executing the query we can obtain the same result:

enter the description of the image here

62 partitions returned.

In search predicates, try to filter what you can, these are the two filters in CalendarId they don't have the + DayOfMonth-1.

enter the description of the image here

This translates into

WHERE ...
    AND (( dc.CalendarId < 20190701)
    OR (dc.CalendarId >= 20270601))
...

When you run the query with these filters, you will see the same number of rows returned when accessing the table in the query plan.

Only after obtaining this data, can you and will join the AccountInfo table in the ID& # 39; s and the DayOfMonth - 1

enter the description of the image here

enter the description of the image here

And to confirm when we change the query:

SELECT
    ai.Name
    ,SUM(dc.Component )
    ,SUM(dc.Value) Value
FROM 
    Warm.DailyCosts dc
    JOIN Warm.AccountInfo ai
        ON dc.AccountInfoId = ai.Id
WHERE
    ai.CorrelationId = '00000000-0000-0000-0000-000000000000'
    AND ((dc.CalendarId >= (20190601  - 1)
        AND dc.CalendarId < 20190701)
    OR (dc.CalendarId >= 20270601
        AND dc.CalendarId < (20270601  - 1)))
GROUP BY            Name

Yes delete the partitions:

a


Solution

I will assume that DayOfMonth just go up to 31 days.

If you know these limits, you can & # 39; encode them & # 39; so that the sql server knows which partitions to look for. After this, you can add additional filtering.

E.G.

WITH CTE
AS
(
SELECT
    ai.Name
    ,dc.Component 
    ,dc.Value 
    ,dc.CalendarId
    ,ai.DayOfMonth
FROM 
    Warm.DailyCosts dc
    JOIN Warm.AccountInfo ai
        ON dc.AccountInfoId = ai.Id
WHERE
    ai.CorrelationId = '00000000-0000-0000-0000-000000000000'
    AND ((dc.CalendarId >= (20190601 )
        AND dc.CalendarId < 20190701)
    OR (dc.CalendarId >= 20270601
        AND dc.CalendarId < (20270701)))

)

SELECT Name,Component,SUM(Value)
FROM CTE 
WHERE ((CalendarId >= (20190601 + DayOfMonth - 1)
        AND CalendarId < 20190701)
    OR (CalendarId >= 20270601
        AND CalendarId < (20270601 + DayOfMonth - 1)))

GROUP BY        
    Name
    ,Component

The sole purpose of the cte is to let the SQL server know that it can also filter dc.CalendarId >= dc.CalendarId >= (20190601 ) OR dc.CalendarId < (20270701)).

Side note: Adding a restriction does not work to enforce this.

This query gives us the result we want, with the correct separation of the partition:

enter the description of the image here

enter the description of the image here

Only access to partitions 19 and 21.


Test data

CREATE SCHEMA WARM
GO

CREATE TABLE Warm.DailyCosts(ID INT IDENTITY(1,1) NOT NULL,
                            Component int, 
                             Value int, 
                             CalendarId INT, 
                             AccountInfoId int
                             )


CREATE TABLE Warm.AccountInfo(Id INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
                              Name VARCHAR(25),
                              CorrelationId uniqueidentifier,
                              DayOfMonth int
                              );
USE (master)
GO
ALTER DATABASE (test) ADD FILEGROUP (Partitionfg)
GO
USE (test)
GO
CREATE PARTITION FUNCTION (PF_CostDateByYearMonth)(int) AS RANGE RIGHT FOR VALUES (
    20180101,
    20180201,
    20180301,
    20180401,
    20180501,
    20180601,
    20180701,
    20180801,
    20180901,
    20181001,
    20181101,
    20181201,
    20190101,
    20190201,
    20190301,
    20190401,
    20190501,
    20190601,
    20190701,
    20270601,
    20270701,
    20270801,
    20270901,
    20271001,
    20271101,
    20271201,
    20280101,
    20280201,
    20280301,
    20280401,
    20280501,
    20280601,
    20280701,
    20280801,
    20280901,
    20281001,
    20281101,
    20281201,
    20290101,
    20290201,
    20290301,
    20290401,
    20290501,
    20290601,
    20290701,
    20290801,
    20290901,
    20291001,
    20291101,
    20291201,
    20300101,
    20300201,
    20300301,
    20300401,
    20300501,
    20300601,
    20300701,
    20300801,
    20300901,
    20301001,
    20301101,
    20301201)


CREATE PARTITION SCHEME (PS_CostDateByYearMonth)  
AS PARTITION (PF_CostDateByYearMonth)  
ALL TO ( (Partitionfg) );  

CREATE UNIQUE CLUSTERED INDEX ptci ON  Warm.DailyCosts(CalendarId,Id) ON (PS_CostDateByYearMonth)(CalendarId) ;
USE (master)
GO
ALTER DATABASE (test) ADD FILE ( NAME = N'TestPartition', FILENAME = N'D:DATATestPartition.ndf' , SIZE = 3072KB , FILEGROWTH = 65536KB ) TO FILEGROUP (Partitionfg)
GO
USE (test)
GO

INSERT INTO  Warm.DailyCosts(Component, 
                             Value, 
                             CalendarId, 
                             AccountInfoId 
                             )
SELECT TOP(1000000) --1M
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
        20180101 +  ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) / 1000 % 1000 ,
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2 
-- 2018
INSERT INTO  Warm.DailyCosts(Component, 
                             Value, 
                             CalendarId, 
                             AccountInfoId 
                             )
SELECT TOP(1000000) --1M
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
        20190101 +  ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) / 1000 % 1000 ,
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2 
-- 2019
INSERT INTO  Warm.DailyCosts(Component, 
                             Value, 
                             CalendarId, 
                             AccountInfoId 
                             )

SELECT TOP(1000000) --1M
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
        20280101 +  ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) / 1000 % 1000 ,
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2 
-- 2028

INSERT INTO Warm.AccountInfo(
                              Name ,
                              CorrelationId,
                              DayOfMonth 
                              )

SELECT TOP(3000000) --3M
        CAST(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS varchar(10)) + 'a',
       '00000000-0000-0000-0000-000000000000',
       15
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2 
CREATE NONCLUSTERED INDEX (IX_Component_Value_DailyCosts) ON Warm.DailyCosts
 (Component,Value) 
ON (PS_CostDateByYearMonth)(CalendarId);
GO

ftp – How do I upload an sql data file to a server?

I have a sql database with 1 table associated with the database.

I have used my FTP tool to transfer the php files from my local host to the server. The database is really a file folder with little content. The table is in the folder.

I have transferred the file folder to the server site, but when I run it the error appears that there is no data in the table.

If anyone can help with this, it would be great.

sql server – multiple Mdf, one is missing

Hi guys, I have a big problem. I know I made such a stupid and unforgivable mistake.
And know, please, help me if you can.
I wanted to make a copy of the database. You should do it by making a backup and then restore it as another database, but the fastest option was to stop the mssql server service, copy files and then attach them as a new database.
The problem is that in a few minutes I created a new mdf file in the primary file group in my database that I wanted to copy. That was an empty mdf file, just a new one. Then I stopped the mssql service and deleted that new empty mdf file from the hard drive manually. I don't know why, I thought: "This was only for tests we no longer need." When I copied files, I started the mssql service and my database is in "pending recovery" mode and I can't find that empty mdf file. Even if I create the same file from another database on the courde mssql server, it knows that this is not the right file. I can't have alter table delete the file due to an error:

Database cannot be opened due to inaccessible files or insufficient memory or disk space

Is there any chance of recovering that database? I have a backup but I will lose data for a day.

please help if you can

Windows application – SQL Master ASA PHP Generator Professional 18.3.0.8 | NulledTeam UnderGround

SQL Master Oracle PHP Generator Professional Edition 18.3.0.8 Multilingual | 28.27 Mb

Oracle PHP Generator is a powerful tool to generate a complete set of PHP for Oracle tables, views and queries selected to continue working with these objects through the web.

Support for upgradeable SQL queries;
Detailed master presentations;
Event-based content management;
Customer side data validation;
Custom paging (screen partitioning);
Data export to PDF, XML, CVS, Excel and Word;
Support for database server user authentication, encrypted and based on tables;
Record level security;
Color schemes;
Multi-language support;
many other more useful things
Pentium PC or higher;
Windows NT4 / 2000 / XP / Vista / Windows 7 / Windows 8 / Windows 10;
512 MB of RAM (1 GB recommended);
25 MB of free hard disk space;
SVGA compatible video adapter;
Internet Explorer 9 or higher (to display the Preview page correctly);
Microsoft .NET Framework 4.0 or higher (to compile styles);
Oracle client software

Multilingual

TO DOWNLOAD
nitroflare

What happens to the registry backup if I reconfigure the SQL Server clock?

What happens to the log backups if I reconfigure the SQL Server clock? In countries with summer time, this occurs at least once a year. Will the point in time be restored with the STOPAT option?

Using fn_dump_dblog, I see this after delaying the clock two hours.

fn_dump_dblog

SQL Server Availability Group Always Active

I have a problem with MS SQL Always On Availability. Suppose the connection between primary and secondary replication is broken. The client inserts data into the primary database (replication cannot be done due to disconnection) that was confirmed. Then, the connection between the client and the primary database is disconnected. The client must now be connected to the secondary database but does not have the latest data inserted by the client. How can this problem be solved?

sql server: what would be the appropriate certification for the protection and privacy of SQL data?

This publication is not related to any specific problem. I could not find the appropriate certification code related to SQL Data Protection, Privacy, SOX (Sections 302,404,408, 409, etc.)

What would be the appropriate certification for the protection and privacy of SQL data?

SQL Server: generates an error when the data size is larger than the maximum response size

The SQL Server error log only captures errors that have significant severity (usually 19-25, with exceptions). See the documentation here:

Imagine if all errors divided by zero, ambiguous column or syntax flooded the error log? That record would quickly become an unmanageable disaster.

What you can do is use an Alert, which can activate notifications and other actions whenever an exception is generated with a certain severity (or, where appropriate, error number):

For example:

EXEC sys.sp_add_alert @name = 'repl size error',
  @message_id = 7139, -- I believe this is the right message
  ...

See this document for sp_add_alert use:

Assignment of shift to work day (Morning / Afternoon / Night) in SQL Server

Help!!!

I find myself making a query through SQL to a database that is fed by a program where staff records a number of defects throughout their work day, the format is a date and time according to the record "yyyy-mm-dd hh : mm: ss.000 ", I have no problem with the query itself, however I would like to be able to determine based on the hours of registration the shift as shown below:

From 22:30:00 to 06:00:00 -> Turn 1 or Night

From 06:00:00 to 14:30:00 -> Turn 2 or Morning

From 2:30 p.m. to 10:30 p.m. -> Turn 3 or Evening

I have tried to use CASE but without success, could someone give me a recommendation to add a column to my query and take the turn?