New answers tagged t-sql
0
A common way of doing this is to compare the total order (regarding date) vs the relative order within each value, let's call the difference grp. If the grp changes, it means that date from another partition of value interfered with the current group. So by picking then min date for each value, grp we can achieve what you want. I'll leave days_at_this_val as ...
0
The meaning of records within the file is inherently depending on the sequence of the records. In a relational database, however, no sequence of rows in a table is guaranteed. Therefore as soon as you load the file into a table you have lost a vital piece of information.
Further, rows within a table are required to have the same columns. Since each record ...
0
You can use the UNPIVOT function to achieve this result set without having to join to the Field table at all.
This query shows how this works (only showing 3 columns for brevity):
SELECT Import_id,
p.Field,
p.Value
FROM
(
SELECT Import_id,
CAST(Test1 AS VARCHAR) AS Test1,
CAST(Test2 AS VARCHAR) AS Test2,
CAST(Test3 AS VARCHAR) AS ...
0
Assuming the ordering of your input data is such that the beginning of the batch (Column1=1) always has the least RowNumber within that batch, you can do something like this to assign pseudo batch numbers using the largest RowNumber among the preceding header records:
select t.*,
max(case column1 when 1 then RowNumber end)
over(order by rownumber ...
1
Check:
UPDATE @tt
SET @tt.TypeId1 = t.TypeId1, @tt.TypeId2 = t.TypeId2
FROM @tt
JOIN ( SELECT Id,
MAX(CASE WHEN TypeId=1 THEN number END) TypeId1,
MAX(CASE WHEN TypeId=2 THEN number END) TypeId2
FROM @dd
GROUP BY Id ) t
ON @tt.Id = t.Id
1
Focusing on Table-Valued Parameters (TVPs) is a bit misleading since the question isn't really concerned with them. The example code, in fact, isn't even using the TVP as a TVP, and would be the same question even if presented as asking about table variables. Meaning, the following adaptation of the OP's example code is really the same question since ...
1
Just found the answer:
Create SQL login with minimal privileges
Execute function from source database under that user (WITH EXECUTE AS)
Module sign object
GRANT AUTHENTICATE to user created from certificate (this is the one I missed)
4
Having your schema would be helpful in helping with your specific query. Without the full schema, here are some pointers for how you might write a query to get your results.
Assuming you have a single dbo.SurveyResults table, where there is some ID (survey_id) that is used to identify all the results from a given user's responses, you could use logic that ...
1
You can nest your CASE statements, as follows:
CASE WHEN <condition 1> THEN
CASE WHEN <sub-condition 1> THEN <output 1> ELSE <output 2> END
WHEN <condition 2> THEN
CASE WHEN <sub-condition 2> THEN <output 3> ELSE <output 4> END
ELSE <output 5> END AS CaseTest
You start ...
0
I would come at this from the opposite direction.
select c.table_name, c.column_name, sp.name
from INFORMATION_SCHEMA.columns c
inner join sys.procedures sp on object_definition(sp.object_id) like '%' + c.TABLE_NAME + '%'
and object_definition(sp.object_id) like '%' + c.column_name + '%'
0
Here is my full code as requested (still working on reproducible example for missing dependencies):
CREATE PROCEDURE [dbo].[usp_v9_SprocDocInfo_FullDependency_SingleSproc]
@SprocName NVARCHAR(150) = ''
AS
BEGIN
DECLARE @ObjName NVARCHAR(128) = NULL
DECLARE @rowCount INT = 0
DECLARE @HasNulls BIT = 0
DECLARE @DepExists BIT = 0
--temp table to hold output
-...
0
I found some years ago a query that can transform any query into HTML:
-- Description: Turns a query into a formatted HTML table. Useful for emails.
-- Any ORDER BY clause needs to be passed in the separate ORDER BY parameter.
-- =============================================
CREATE PROC [dbo].[spQueryToHtmlTable]
(
@query nvarchar(MAX), --A query to ...
0
If exec sp_validatelogins does not return a resultset, the #invalidlogins will be empty.
As a result, setting @EmailBody to values in the #invalidlogins table will also set @EmailBody to NULL. The same is true for @TableHTML
SET @TABLEHTML = @TABLEHTML + @EmailBody + '</table></div></body></html>'
This might be why you are ...
0
I've add LastOutCome to the script with this.
select
S.name AS JobName,
SS.name AS ScheduleName,
CASE(SS.freq_type)
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Daily'
WHEN 8 THEN (case when (SS.freq_recurrence_factor > 1) then 'Every ' + convert(varchar(3),SS.freq_recurrence_factor) + '...
6
This seems to give you what you want.
--demo setup
DROP TABLE IF EXISTS DocumentTable;
go
CREATE TABLE DocumentTable
(DOC_ID int, RENDITION_ID int, RENDITION_TYPE varchar(11))
;
INSERT INTO DocumentTable
(DOC_ID, RENDITION_ID, RENDITION_TYPE)
VALUES
(001, 001, 'Interactive'),
(001, 002, 'Web PDF'),
(002, 003, 'Interactive'),
(003, ...
1
This appears to give you what you want. I added logic where the ENTITLEMENTS are being generated to eliminate rows that are null or blank and wrapped that section in an isnull.
--demo setup
drop table if exists staff_user_data
go
CREATE TABLE staff_user_data (
EMPLOYEEID INTEGER,
USERNAME VARCHAR(7),
ISPRIMARY VARCHAR(1),
ENTITLEMENTS VARCHAR(31)
);...
0
Although I think scsimon answer is da best, you can get the same result by adding:
AND td.Beer_name IS NULL
LEFT JOIN selects all rows from __SampleRequired table that match WHERE condition, but add nulls for those __Sample rows that do not match ON condition.
2
Using NOT EXISTS
Select DISTINCT
bm.Location
,bm.[Beer (Upcoming Menu)]
FROM
___SampleRequired bm
WHERE NOT EXISTS
(SELECT 1
FROM ___Sample td
WHERE LTRIM(RTRIM(bm.[Beer (Upcoming Menu)])) = LTRIM(RTRIM(td.Beer_name)))
AND bm.Type LIKE '%Mandate%'
AND bm.Location = 'StapleChase'
Also, Max Vernon has a nice answer comparing LEFT ...
1
You have a performance spool that feeds your nested loops operator (non-apply)
...with very low estimates.
Now, for each value in the top(outer) input, the table spool will produce a rebind. No rewinds are possible for performance spools on non apply nested loop joins.
...The lazy table spool is populated once during the first loop
iteration. The ...
0
Returning the resultset does not have to be the last thing that happens in a stored procedure. If you don't want to do things with the resultset in your procedure you don't have to store it in a temp table/table variable.
create or alter procedure dbo.GetVersionServer as
begin
-- Execute your dynamic SQL here returning a rowset
declare @S nvarchar(max) ...
0
If you are returning singular results (e.g you want to store returned results in an @parameter int, rather than an @parameter table) then sp_executesql can use OUTPUT parameters
see
https://support.microsoft.com/en-gb/help/262499/how-to-specify-output-parameters-when-you-use-the-sp-executesql-stored
for worked examples of how to do this
0
Few Basic ideas.
A login is a security principal, or an entity that can be authenticated by a secure system. Users need a login to connect to SQL Server. You can create a login based on a Windows principal (such as a domain user or a Windows domain group) or you can create a login that is not based on a Windows principal (such as an SQL Server login).
A ...
0
You are missing create user before you alter it.
Login - gets you entry to the server
Database User - gets you entry to a particular database
so in your script, add create user from login ...
USE [xxxx-xxxx]
create user [xxxx] from login [xxxx] <--- this is needed !
ALTER USER [xxxx] WITH DEFAULT_SCHEMA=[db_datareader]
ALTER ROLE [db_datareader] ADD ...
2
A quicker / safer method may be to update the command on step 1 to run if it's not the primary, or not in a AG, or whatever.
update msdb.dbo.sysjobsteps
set command = 'print ''removed primary check code'''
where command = 'your old code'
and step_id = 1
and step_name like '%check prima%'
3
You could try dynamically generating TSQL to delete the specific step. Here is an example:
SELECT
'exec msdb.dbo.sp_delete_jobstep @job_id=''' + convert(varchar(50), a.job_id) + '''' + ', @step_id = ' + convert(varchar(30), b.step_id) as CmdToExec ,
a.job_id
,name
,enabled
,b.command
,b.step_name
,b.step_id
FROM msdb.dbo.sysjobs a
INNER JOIN msdb.dbo....
6
You could use a table variable to hold the results of the dynamic SQL call until you are ready to exit the stored procedure. Just before returning from the stored procedure, select the data from the table variable.
DROP PROCEDURE IF EXISTS dbo.MyProc;
GO
CREATE PROCEDURE dbo.MyProc
AS
BEGIN
--Declare Table variable to hold results of query
DECLARE @...
0
I use MS Access DoCMD to export CSV's from SQL Server. It is able to do it with a single command. The access database is only used to enable the access command. It performs well, and as an added benefit (not sure why) it creates a schema.ini for the table exported.
Adam
CMD="SELECT * INTO [Text;HDR=Yes;DATABASE=C:\].[Results.txt]
FROM [ODBC;DSN=SQL2017;...
4
I agree with Erik, but answering your question the error was in the quotation marks in the STUFF and here AND ISNULL (DUPE1.include_column_list, '') = ISNULL (DUPE2.include_column_list, '')
You did not have the quotation marks correctly, remember that when you intermix texts all the '' must be like '' ''
DECLARE @db_name AS nvarchar(max)
DECLARE c_db_names ...
4
Rather than hack away at a script you don't understand well enough to troubleshoot, why not use something tested and understandable, like sp_BlitzIndex?
If you run EXEC sp_BlitzIndex @Mode = 4, @GetAllDatabases = 1; it will go through and diagnose all sorts of index issues beyond what you're looking for in that Script You Found On The Internet™
1
you can create a recursive CTE that will result in a table returning all dates between the give dates, and then JOIN this with your result:
WITH abc as
(
SELECT CAST('2019-04-20' as date) as i
UNION ALL
SELECT DATEADD(d,1,i)
FROM abc WHERE i < CAST('2019-04-25' as date)
)
SELECT
ISNULL(AVG(Value),0) AS VALUE,
CAST(Timestamp AS DATE) AS TimeStamp
...
2
You can also decide to use In-Memory OLTP tables. They of course still use transactions, but there is no blocking involved.
Instead of blocking all operations will be successful, but during the commit phase engine will check for transaction conflicts and one of the commits may fail. Microsoft uses the term "Optimistic locking".
If the scaling issue is caused ...
Top 50 recent answers are included
