Tag Info

New answers tagged

0

You cannot use an aggregate like CountRows() in a calculated field in your dataset. But you can use CountRows() as an expression in a textbox (alone or within a table) scoped to your dataset. Now that you have your dataset created, you can put a textbox on the report and populate it with the expression =CountRows("DataSet1"), and it will provide the correct ...


8

There is a simple solution to this: Drop all of the _dta_... statistics and stop blindly applying DTA recommendations. More information The particular problem was that there were multiple sets of statistics for the column in question. The extra dta statistics were created by sampling the data (the default behaviour for statistics not associated with an ...


0

I've now got this in production and it's working beautifully. We found the underlying issue before this went out, but it may be very useful for future issues. To any future readers, SET CONTEXT_INFO can indeed be used for correlating SQL requests to the code and/or requests that run them, and I've seen no noticeable performance hit. For anyone curious ...


6

No sane DBA will ever allow such a procedure. This is a SQL injection privilege escalation vector. I can pass in the tablename 'x; exec sp_myfoo;' and voila. There are basic issue: table names are NVARCHAR, not VARCHAR table names are length 128, not 60 sysname is a handy type to represent object names, is an alias for NVARCHAR(128) tables are qualified ...


2

No, it is executing TRUNCATE TABLE in the context of the current owner of the module. So, as long as the owner of stored procedure has the ALTER TABLE permission, it will work. Anyone who has CREATE PROCEDURE permission in the database and ALTER permission on the schema in which the procedure is being created, can create the procedure. Any member who has ...


2

The question is for 2008R2 but here's a way to do it in 2012+ versions, using LAG() and window functions (OVER with ROWS ...): WITH cte AS ( SELECT *, blockstart = CASE WHEN Value >= 80 AND COALESCE(LAG(Value) OVER (PARTITION BY Source ORDER BY PollTime), 0) ...


5

Here's a solution that uses ranking functions only: SELECT source, polltime, value, block = CASE -- ranks the islands WHEN Value >= 80 THEN DENSE_RANK() OVER (PARTITION BY source, CASE bb WHEN 0 THEN 0 ELSE 1 END ORDER BY bb ) ELSE 0 END FROM ( SELECT *, bb = -- creates the "islands" ...


1

Here's what you want: SELECT t.source, t.polltime, t.value, COALESCE(b.idx, 0) AS Block FROM #temptable t LEFT JOIN ( SELECT t1.polltime, DENSE_RANK() OVER (ORDER BY COALESCE(MIN(DATEADD(ms,-1,t2.polltime)), MAX(DATEADD(ms,1,t2.polltime)))) AS idx FROM #temptable t1 LEFT JOIN #temptable t2 ...


4

Here is a recursive CTE solution using a technique that Paul White blogged about in Performance Tuning the Whole Query Plan. declare @T table ( Eventdate date index IX_Eventdate clustered, Val int ); insert into @T(Eventdate, Val) values ('2012-03-23', 3965), ('2012-03-26', 3979), ('2012-03-27', 3974), ('2012-03-28', 3965), ('2012-03-29', 3967), ...


0

Thanks Dog OnAPorch, your link was veryhelpful. i for-gone checking if the trigger exists on this one off rare occasion but i managed to get a working version from the link you provided. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER qf.Update_ServiceWorkingHours_Cache ON qf.customer_working_Hours AFTER INSERT,UPDATE AS BEGIN ...


3

I am assuming you are trying to implement some basic replication using a trigger. As long as your process remains fairly simple, this should work OK. However, I would advise you to read up on (i.e. google) why executing stored procedures from triggers is not a good idea, and also about SQL's built in transactional replication feature. Here is a practical ...


0

Here's a link that's very similar: http://stackoverflow.com/questions/3768278/call-stored-procedure-within-create-trigger-in-sql-server Since the scope is an action resulting from a data change within a table, you'll want to write the trigger on the table, not the database. When you say "i want the script to check if the trigger exist and if it doesn't ...


2

If you want this query gangsta fast create an indexed view that groups on [OpenTimeUtc], [SubscriberId]. That view will serve the date filter perfectly and it will pre-calculate the distinct as well. The query will result in a plan that does an index range scan on the view. Not possible to do any better than that.


5

Why this happens has already been answered by @PaulWhite in the SO question: How does this CASE expression reach the ELSE clause? To solve it, you should calculate the ABS(CHECKSUM(NEWID()))%10 +1 outside/before the INSERT statement so it is calculated once. Something like: DECLARE @i INT = 1 ; DECLARE @rand INT ; WHILE @i <= 10 BEGIN SET @rand ...


4

There are so many things outside of SQL Server's metadata that may depend on the size of a column, it's not even funny. Here are a few things you'll need to check: Any variable or parameter declarations in stored procedures, dynamic SQL or ad hoc SQL that may pass values to that column, filter on it, search from it, or get assigned from it. Any references ...


3

Your query includes a lot of tables and columns that aren't listed in your sample data. From this query you can join and pull in other things: ;WITH lastMessage AS ( SELECT Id, IdMain, IdReply, DatePosted, rn = ROW_NUMBER() OVER (PARTITION BY COALESCE(IdMain, Id) ORDER BY DatePosted DESC, Id) FROM dbo.Message ) SELECT t.Id, t.IdMessage, ...


-1

There is a isnull function in TSQL, writing it something like this might shorten up your query since you are basically checking to see if the column is not null so you can subtract the two columns and see if it is greater than or equal to 180 case when isnull(table.column1,0) - isnull(table.column2,0) >= 180 then 'Y' else 'N' end


0

Here is one way to rewrite the query that should give the same results without nested CASE statements. I'm checking for NULLs first, to potentially short-circuit additional testing. I considered using a single WHEN, with an OR between each expression, but OR isn't deterministic so this may perform better. If those columns are mostly null (vs mostly filled), ...


1

What I can see here is that you're constantly checking if Table.COLUMN1 is not null and Table.COLUMN2 is not null, and then calculating the difference between them, and if it's greater or equal to 180, then you return 'Y' and you compare this whole thing to 'Y' every time. This same pattern repeats 9 times. 1) Can you have that as a computed column in the ...


3

Group by MachineName and get the rows where there are less then 3 distinct values in GrpName. To figure out what groups are missing you can use the table value constructor to include the group names you want to check and use for xml to build a string with the missing group names. declare @T table ( MachineName varchar(10), GrpName varchar(10) ); ...


0

written from the head, maybe this will help ya -- extract group Names from table with groups as ( select grpName from table group by grpName ) select maschineName, string_agg(grpName,',') as missingOnes -- here you need something mssql specific, haven't found any from table t where not exists ( select 1 from groups where groups.grpName = t.grpName ) ...


0

You should not use T-SQL to do filesystem tasks like move files, copy files, etc. Instead use PowerShell. SQL Server has support of PowerShell and you can schedule it using SQL Agent job as well. You should look into Move-Item or Copy-Item cmdlets of PowerShell. There are plenty of scripts that will help you.


0

'(There's) no such thing as a stupid question' is a popular phrase that has had a long history. It suggests that the quest for knowledge includes failure, and that just because one person may know less than others they should not be afraid to ask rather than pretend they already know. :) I am using below code to transfer full backups to another location ...


1

You can use the available views in msdb to find the information regarding backups : dbo.backupset: provides information concerning the most-granular details of the backup process dbo.backupmediafamily: provides metadata for the physical backup files as they relate to backup sets. dbo.backupfile: this system view provides the most-granular information for ...


0

There is a way to do that, but the columns you are searching on must be all the same data type. The "trick" involves using a sort of UNPIVOT with CROSS APPLY VALUES. Here's an example: -- Go to a safe place to work on USE tempdb; GO -- Drop sample data if exists IF OBJECT_ID('Demo') IS NOT NULL DROP TABLE Demo; -- Demo table CREATE TABLE Demo ( ...


3

In your current setup, each of those queries is a separate transaction. Individually they are reading only committed data. But nothing is binding them together. If you want to guarantee the same underlying data for that view across both statements, you need to wrap them in a transaction, and add a Table Hint to the first query specifying to lock the table: ...



Top 50 recent answers are included