Tag Info

New answers tagged

8

There are two key differences between EXCEPT and NOT IN. EXCEPT EXCEPT filters the DISTINCT values from the left-hand table that do not appear in the right-hand table. It's essentially the same as doing a NOT EXISTS with a DISTINCT clause. It also expects the two tables (or subset of columns from the tables) to have the same number of columns in the ...


2

If I can understand correctly what you are looking to do, you want to SELECT all records from TableA that only appear once, but don't already appear in TableB. You can do this with a GROUP BY and HAVING to identify those that only appear once in TableA, then do an EXCEPT to filter out those that already appear in TableB. SELECT int, com FROM TableA ...


3

--======================================================= -- delete the duplicate records from table @t -- keeping a single unit of each -- marcelo miorelli 24-nov-2014 --======================================================= --======================================================= --create a table variable and insert records in it -- just for this ...


2

select ROW_NUMBER() OVER (PARTITION BY num, com ORDER BY identity_field) as 'RowNumber', t1.* into ##TableA from TableA t1 --delete t1 select t1.* from TableA t1 join ##TableA t2 on t2.identity_field=t1.identity_field where t2.RowNumber > 1 but first make backup of your table


1

Add role member SQLAgentUserRole in msdb, the user will receive access only to the local (owned) jobs. -- Step 1 CREATE LOGIN [AgentJobUser] WITH PASSWORD=N'1234', DEFAULT_DATABASE=[msdb], DEFAULT_LANGUAGE=[us_english] , CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO --Step 2 USE [msdb] GO CREATE USER [AgentJobUser] FOR LOGIN [AgentJobUser] WITH ...


1

User need access on that job, he can modify and run too. You can give the user - SQLAgentUserRole. From BOL : SQLAgentUserRole is the least privileged of the SQL Server Agent fixed database roles. It has permissions on only operators, local jobs, and job schedules. Members of SQLAgentUserRole have permissions on only local jobs and job schedules ...


1

On the question of the size of things, first of all you said that you shrank a database, so that would reduce the DB size of the space that was discarded during the shrink. John M also provided some clarification. Regarding the numbers in the bottom picture, they are: select (2585832 / 1024.0) /* KB from the Reserved in the bottom picture to MB */ ...


-1

I always use this TSQL command. -- the utc value declare @utc datetime = '20/11/2014 05:14' -- the local time select DATEADD(hh, DATEDIFF(hh, getutcdate(), getdate()), @utc) It is very simple and it does the job.


1

SQL Server 2005, I'm seeing about 100 'jobs' in the SQL Agent>jobs node in SSMS. They appear as hexadecimal strings not with their real names: They are from reporting service subscriptions. Below SQL will give you list of the job names against the actual reports select ScheduleID ,Path ,name ,s.Description from ReportServer.dbo.catalog c ...


1

1) Yes 2) Yes, After creating the new files, you'll need to drop and re-create the clustered indexes of the target tables, moving them on to the new filegroup to move the data: CREATE CLUSTERED INDEX CIX_YourTable ON dbo.YourTable(YourClusteringKeyFields) WITH DROP_EXISTING ON [filegroup_name] 3) If you can get the DBA to back you up on this solution, ...


2

You cannot add the constraints to tblUserRoles because the data within this table already violates the constraints that you are trying to add. a) Double check that all UserID values in tblUserRoles reference UserID values in tblUsers b) Do the same as the above for tblRoles and RoleID. c) As I imagine both of the above will return records that are not in ...


0

Granted DBID and UPC are not guaranteed to be the same for Price1 and Price2 your code should be similar to this: SELECT (SELECT TOP 1 DBID FROM sub WHERE Price1=(SELECT MIN(Price1) FROM sub)) AS DBID1 ,(SELECT TOP 1 UPC FROM sub WHERE Price1=(SELECT MIN(Price1) FROM sub)) AS UPC1 ,(SELECT MIN(Price1) FROM sub) as Price1 ,(SELECT TOP 1 DBID FROM sub WHERE ...


0

Where minimum is Price2 or Price1? If both of them then SELECT DBID,UPC,Price1,Price2 FROM MyTable WHERE Price2 = (SELECT MIN(Price2) FROM MyTable) AND Price1 = (SELECT MIN(price1) FROM MyTable)


1

You can treat the union as a subquery. SELECT TOP 1 DBID, UPC, Price1, Price2 FROM (SELECT DBID, UPC, Price1, Price2 FROM Table1 UNION SELECT DBID, UPC, Price1, Price2 FROM Table2) sub ORDER BY Price1 DESC


0

Use the OUTPUT clause of the INSERT statement to capture the identity values created in data. Then use that to write to relation. create table #data ( id int primary key , value int not null); if not exists (select id from data where value = 1337) insert into data (value) OUTPUT INSERTED.id, INSERTED.value INTO #data ...


3

If I understand correctly, I think this should work... We're checking that TableA.id+100 is in TableC.eid and then counting the number of times TableA.id+100 appears as TableB.char. It's always good to create a SQLFiddle though so that others can easily pick up your schema and code to debug. SELECT a.id, a.name, a.comment, COUNT(b.char) AS count ...


3

A million rows and a narrow table is not really a big table. I would expect most queries to return sub-second, unless you are pulling back the whole table to client, in which case rendering the rows in the SSMS grid is really taking the time. Having said that, this might be a good use for indexed views ( and there aren't many! ). I don't tend to use them ...


0

Why you are creating the column name "id" (if there is no reason for it, I suggest to delete it ). For the Clustered Index issue, I think that his KEY should be only, "[SUM_Amount]". the reason is that your queries are going to do "INDEX SCAN". and for this type of operation CLUSTERED index give us the best performance (Clustered index pages are sorted ...


3

I think maybe the intention of what is being asked for here is getting lost by the distraction of trying to address the literal question of how to get the RAISERROR to work. @wBob asked a great question: "why are you watching it?" The intention here is to have insight into where the process is, how far it has gotten, etc. That doesn't require PRINT or ...


3

Yes, placing a clustered index like you suggest will give you excellent query performance for those two particular types of queries, but it may spell disaster for most other SELECT queries on that table. I would offer an alternative solution: I would suggest adding a basic clustered index on a single, unique column like idUser: ALTER TABLE dbo.Table1 ADD ...


1

Honestly why are you watching it? If the proc really takes that long, schedule it to run overnight using a SQL Agent job so it's done in the morning when you come in. Longer term, consider refactoring the proc to use set-based logic as this will almost certainly be faster. At least when you have existing code you have something to test against, eg ...


4

You can also do: RAISERROR('%d', 0, 0, @cur_id) WITH NOWAIT; However depending on how many of these you have you can still overrun the buffer provided by Management Studio, and still end up in scenarios where the output isn't printing immediately.


9

In SQL Server (and I think this should work in the ancient version you say you're using), the normal method for this is to use RAISERROR. PRINT won't display any output until the process finishes which is not very useful. I use this technique a lot in batch scripts for say updates and deletes where I want to keep the batch size small. In the example below, ...


0

Ram, start your own thread (Ask Question - in top right). You'll get a better response that way. When you do start your own thread - include more detail: Can you log in to the Instance at all? Are you using Mixed Mode authentication? What version of SQL/ORACLE? Etc. Don't post your new question at the end of someone else's thread - that's not cool.


1

If you want to put a query result in a table variable and iterate through the rows that can be easily done. Be careful with your loops of course. DECLARE @Value int, @Label varchar(25) DECLARE @Tmp table (value int, label varchar(25)) INSERT INTO @Tmp (value, label) SELECT 1, 'One' UNION SELECT 2, 'Two' UNION SELECT 3, 'Three' WHILE (SELECT ...


0

I have a Credential running under a generic (AD) domain account. I changed the password last week. That broke the SSIS jobs. Updating the password fixed it.


3

No, SQL Server does not track successful logins by default. And even if you turn that option, it'll only keep it back through the current log + 6 previous (again, by default), and those logs will be bloated and full of noise. You may wish to implement a logon trigger going forward, but going back in time, this information is simply not available.


3

Microsoft SQL Server 2005 - 9.00.5000.00 (X64) Dec 10 2010 10:38:40 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.0 (Build 6002: Service Pack 2) As per output you have SQL Server 2005 SP4 and it does fall under category of extended support. For main question SQL Server 2005 SP2 is not supported at all. ...


0

Hopefully I can help you answer this question if I understand the question correctly. You wish to run the same query against multiple schemas in a database. I assume each schema has identical tables. As suggested below by Shawn you will need to import the module SQLPS in Powershell to run the scripts below. Assume the database with 3 schemas as follows ...


1

This might help you to create simple dynamic query for partitioned tables using COALESCE function of SQL Server Step1: Create function to split tokens CREATE FUNCTION [dbo].[fnSplit] ( @List VARCHAR(8000), @Delimiter CHAR(1) = ',' ) RETURNS @Temp1 TABLE ( ItemId INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, Item VARCHAR(8000) NULL ) AS BEGIN DECLARE @item ...


0

I have this script I generally use before I need to DETACH a database. it tests for mirror too. USE MASTER GO --=========================================================== -- before detaching the database -- see what files it has and where they are located -- checks mirror and replication -- database must not be part of mirror (neither as principal nor ...


4

What you are referring to is deferred name resolution and is further explained here Deferred Name Resolution and Compilation. Deferred name resolution can only be used when you reference nonexistent table objects. All other objects must exist at the time the stored procedure is created. For example, when you reference an existing table in a stored ...


1

I believe that in sql server 2005 you can create a stored procedure that references a table that does not exist. I have just done it, see my example below. --set parseonly on select @@version --Microsoft SQL Server 2005 - 9.00.5000.00 (X64) --Dec 10 2010 10:38:40 --Copyright (c) 1988-2005 Microsoft Corporation --Enterprise Edition (64-bit) on Windows NT ...


2

I think your problem is the fact your WHERE clause has multiple predicates on the same table. This article by Paul White explains the issue. It may be possible to get around this issue by having a computed column that does what your current WHERE clause does. You could then use this column on it's own but this would need a bit of testing to make sure it ...



Top 50 recent answers are included