New answers tagged

0

Assuming you missed a column call LastLoginTime in User table or some other Log table, here goes the query. USE ABC; IF NOT EXISTS (SELECT * FROM sys.objects WHERE name = 'Not_loggedinUsers' AND type ='U') CREATE TABLE Not_loggedinUsers ( Email NVARCHAR(100), NoticeCount INT ); SELECT UserEmail Email INTO #oldUsers FROM User GROUP BY UserEmail ...


0

As others have mentioned, a trigger would work fine. You do not need to change the sproc, just put a INSTEAD OF trigger on the table. The trigger will look to see if the RangeFrom value being inserted is between any of the ranges already in the same and then do the same for the RangeTo value. If it passes that logic, it will insert. If it fails, it will ...


2

select count(*) from ( select RangeTo, LEAD(RangeFrom, 1, 0) OVER (ORDER BY RangeTo) AS LeadRangFrom from table ) ss where @RangeFrom > RangeTo and ( @RangeTo < LeadRangFrom or LeadRangFrom is null) If count(*) is one then you can insert Is that the question you are asking?


0

You could use an insert trigger to check the inserted values against the table data, and fail if the range already exists. But since you are using a Stored Proc, you can check in the proc. Something like this: CREATE PROCEDURE blah (@rangeFrom int, @rangeTo int) AS BEGIN IF EXISTS( SELECT 1 FROM Ranges WHERE @rangeFrom BETWEEN RangeFrom AND RangeTo ...


3

Aaron Bertrand is right. The documentation for the OUTPUT clause is not explicit in saying that a table cannot be created with its use. I've run into the same issue. What I ended up doing was using two statements: --This creates the table with the needed columns without inserting any data. SELECT TOP 0 [myColumn1], [myColumn2], ...etc INTO [myNewTable] FROM ...


0

In SQL what's how many CPU's do you have for that server? What's your MAXDOP and Cost threshold for parallelism'? One thing you can do to check to see if your cost threshold is too low is run the same query with OPTION (MAXDOP 1) SELECT cc.ReportID, cc.ReportDate, cc.UnitID, cc.Callsign, cc.SpeciesCode, cc.ComName, ...


2

Their cannot be a comparison drawn as Snapshot is "photo copy" of data file. The difference lies in amount of activity both does and consistency of product arising out of both the operations. Full backup "Is Always" more reliable than snapshot backup Full backup includes all committed and uncommitted transaction when full backup has finished. Read more ...


4

Mostly because a snapshot isn't really a backup. As explained in How database snapshots work The snapshot uses one or more sparse files. Initially, a sparse file is an essentially empty file that contains no user data and has not yet been allocated disk space for user data. As more and more pages are updated in the source database, the size of ...


1

I was a little bit surprised that the Vladimir's cursor solution was so slow, so I also tried to optimize that version. I did confirm that using a cursor was very slow for me as well. However, at the cost of using undocumented functionality in SQL Server by appending to a variable while processing a rowset, I was able to create a simplified version of this ...


1

Discussion will follow the code. declare @Helper table( rn tinyint, dowInt tinyint, dowChar char(3)); insert @Helper values ( 1,1,'Sun'), ( 2,2,'Mon'), ( 3,3,'Tue'), ( 4,4,'Wed'), ( 5,5,'Thu'), ( 6,6,'Fri'), ( 7,7,'Sat'), ( 8,1,'Sun'), ( 9,2,'Mon'), (10,3,'Tue'), ...


3

Try naming that query, and then using the column with PARTITION BY in an OVER clause. WITH MyCTE AS ( SELECT S.Product_Name, SPD.*, Sp.Delivery_Status, CONVERT(decimal(10, 2), (SPD.Quantity * SPD.UnitPrice)) Amount, CONVERT(int, (SPD.Quantity * SPD.UnitPrice) / (SUM(SPD.Quantity * SPD.UnitPrice) OVER ()) * 100) [Cost%], CAST((SPD.Quantity * ...


7

From xp_cmdshell (Transact-SQL) in the product documentation: The Windows process spawned by xp_cmdshell has the same security rights as the SQL Server service account. When it is called by a user that is not a member of the sysadmin fixed server role, xp_cmdshell connects to Windows by using the account name and password stored in the credential ...


0

One problem with hierarchyid is you get vendor lock-in. But I did find a great article by Adam Milazzo about how everything works internally: http://www.adammil.net/blog/view.php?id=100 With this I was able to write a Postgres script to convert my data set over from MSSQL. Also included it in a script I wrote to import the AdventureWorks database into ...


2

The time is not stored as number of milliseconds. It is a numeric representation of the actual time. For instance, 12:06:59 is represented as 120659. 1:00:02 pm is presented as 130102. 1:15:42 am would be 11542. Instead of calculating that all yourself, simply use the dbo.agent_datetime function. Something like: SELECT ...


4

I ended up with an approach that yields the optimal solution in this case and I think will do well in general. The solution is quite lengthy, however, so it would be interesting to see if someone else has a different approach that is more concise. Here is a script that contains the full solution. And here is an outline of the algorithm: Pivot the data ...


5

This one uses a recursive CTE. Its result is identical to the example in the question. It was a nightmare to come up with... The code includes comments to ease through its convoluted logic. SET DATEFIRST 1 -- Make Monday weekday=1 DECLARE @Ranked TABLE (RowID int NOT NULL IDENTITY PRIMARY KEY, -- Incremental uninterrupted sequence in the ...


2

For the sake of completeness, here is a two-pass gaps-and-islands approach that I tried myself before asking this question. As I was testing it on the real data I found few cases when it was producing incorrect results and fixed it. Here is the algorithm: Generate islands of consecutive dates (CTE_ContractDays, CTE_DailyRN, CTE_DailyIslands) and ...


5

Not exactly what you are looking for but could perhaps be of interest to you. The query creates weeks with a comma separated string for the days used in each week. It then finds the islands of consecutive weeks that uses the same pattern in Weekdays. with Weeks as ( select T.*, row_number() over(partition by T.ContractID, T.WeekDays order by ...


-1

You can do Cartesian product using CTE as our CTE would be only one row . create View [dbo].StokMaliyet AS WITH CTEDolar AS (select top 1 cast (Dolar as decimal(8,7) )Dolar from GunlukDoviz), CTEEuro AS (select top 1 cast (Euro as decimal(8,7) ) Euro from GunlukDoviz) select Stok_Kodu, (case **strong text**when dovtip='3' then net_maliyet ...


11

If a non-clustered index is created on a partitioned table, that index will be partition-aligned by default unless you explicitly specify that it should not be (e.g., use ON [PRIMARY] to specify the file group). In such a case, the SSMS script index functionality will not show the partition scheme used, but you can use sp_help on the table to confirm that ...


0

Is the clustered Index on ColDate? The key of the clustered index is included in any NC index.


1

SELECT tbl_A.id FROM tbl_A LEFT JOIN tbl_B ON tbl_A.id = tbl_B.id WHERE tbl_B.id IS NULL This is most likely how I would do something like that. Just a simple join where the column in A does not equal the columns in B. The DBMS should take care of optimizations. As for varchar and char: char is more efficient when using data that is all the same length. ...


4

I couldn't understand the logic behind grouping weeks with gaps, or weeks with weekends (e.g. when there are two consecutive weeks with a weekend, which week does the weekend go to?). The following query produces the desired output except that it only groups consecutive weekdays, and groups weeks Sun-Sat (rather than Mon-Sun). Whilst not exactly what you ...


11

Yes, things haven't changed much in this area, you should be using quotename for any SQL server object names that are used in dynamic SQL (especially if they are supplied externally to your code). As well as SQL injection mitigation this also means your code will work correctly for non standard identifier names. The function is only appropriate for object ...


2

Use CET : CREATE PROCEDURE [dbo].[sp_JobSearch] @EnteredKeyword nvarchar(200) = '', @EnteredLocation nvarchar(200) = '', @PageNumber INT = 1, @PageSize INT = 40 AS BEGIN WITH CTE AS ( SELECT ROW_NUMBER() OVER ( ORDER BY [MasterJobId] ) AS RowNum , MasterJob.Title, MasterJob.CompanyName, ...


0

You can also use the below query to check in real time. Most applications will keep a session alive even when not in use. SELECT es.session_id, es.client_interface_name, es.login_name, es.nt_domain, es.nt_user_name, ec.client_net_address, ec.client_tcp_port, ec.local_net_address, ec.local_tcp_port FROM ...


1

You run a server-side trace or Extended Event session to capture login activity. I also would not have just renamed it in production. If you are referring to the sa account itself, disable it first to verify if anything breaks; and leave that disabled for a day or up to a week. How long you leave it is based on knowledge by the application owner. If they ...


0

Yes. If you're on 2008 you can set up an extended event session to record logins to a file (you can configure it to roll over and set a maximum size, even 64mb may be enough). You could use a ring buffer but it seems like a waste of memory... and if extended events were not possible then an audit trace will work, as will a standard trace (created in profiler ...


0

ok, so instead of using a CMDEXEC job step, I changed it back to a t-SQL script step replaced the batch file name with the full string: EXEC xp_cmdshell 'c:\dircommand.bat' and it works. So I am not sure what the problem is with Operating System job steps but at this point I am passed the hurdle. funny thing is, I have another server setup the other ...


2

Active/Active refers, typically, to the idea of having multiple copies of a database available concurrently. However, in SQL Server, there is no way to accomplish that for read-write databases, other than the somewhat edge case of sharding data across mutliple instances. In SQL Server, Active/Active is normally used to denote a multi-node Windows Server ...


1

but we have no access at all to their domain. Your Windows user won't work - since you don't have access to the domain or the domains are not trusted. What you can do? Ask the vendor to create a SQL Server user with enough rights. Assign appropriate roles, then take that SID and create a login on the secondary server. Remember that log shipping works ...


-3

Use below code. change r.* according to your result ; with a1 as (SELECT a.* FROM tblabc a INNER JOIN tblxyz b ON a.ID = b.ID ) select r.* from a1 r


3

I think the condition ON a.ID = ISNULL(b.ID,a.id) is not the best way to do this. It may lead to inefficient plans and may return unwanted results (according to your specifications). If the tblxyz table has rows with NULL values, you'll still get all the rows from table a (and possibly multiple times). I would write the query without using the ISNULL() ...


2

The way you've done it is correct. So the reasons for slowness will likely come down to: when is it slow (is it when the temp table has a lot of rows, or when it has few rows?), how many rows are usually in each table, what indexes exist on each table, and what data type is ID? You can also look at your real-world execution plan for some answers. I ...


0

You can use the following SQL command to get the last commands that executed on MyDB (as example). SELECT deqs.last_execution_time AS [Time], dest.text AS [Query], dest.* FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest WHERE dest.dbid = DB_ID('MyDB') --note you need to change MyDB to your database name ORDER BY ...


-1

Both indexes are the basic terms in sql. A clustered index requires no separate storage than the table storage. It forces the rows to be stored sorted on the index key. A non-clustered index requires separate storage than the table storage to store the index information.


1

The following query will provide a list of statements you can copy-and-paste into a new query window if you need to perform this task once: SELECT 'ALTER LOGIN ' + QUOTENAME(sp.name) + ' WITH CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF;' , * FROM sys.server_principals sp WHERE sp.type_desc = 'SQL_LOGIN' AND sp.is_disabled = 0 ORDER BY sp.name; ...


6

I would suggest you to create a server side trace or enable SQL Audit to track down activity from users that you dont trust. Remember that DMV data gets reset if the DMV is cleared out, sql server is restarted, etc. The closest you can get is using below query: /****************************************************** Script : Findout Who did what ? Author ...


2

There are multiple things to look at to improve query performance. Understand your system load Volume of data you are expecting to be in the tables mentioned in query Volume of data you are going to fetch from tables mentioned in query Indexes applied on tables mentioned in query Structure of the query I can suggest you improvements related to only ...


2

First of all, if custId is the Customer's Primary Key, then there's no need to specify DISTINCT at all. Second, grouping and then filtering by the grouped key may be very inefficient when the filtering will likely render a small subset: filter first, then group. Third, I assume you don't require casting to varchar when removing the DISTINCT clause. Fourth, ...


2

You may want to look at this SO question I'm looking for a reliable way to verify T-SQL stored procedures. Anybody got one? which is asking essentially the same thing, with several answers. To build upon the script Alaa Awad posted... this should show the schema and database of the referenced and referencing objects. If you're using many temp tables via ...



Top 50 recent answers are included