2
votes
3answers
277 views

How to pull rows only from the weekends?

My table data is like this: ID Name salary date 1 a 100 1/08/2014 2 b 200 2/08/2014 . . . . 26 z 2600 26/8/2014 Now I want to return only rows from Saturday or ...
1
vote
1answer
39 views

Group by alternative [on hold]

I have a table , that has a generic set of actions for EVERY user. However if there is a user that needs to have a unique action, that replaces an existing action, then in my query, I want to ...
1
vote
1answer
47 views

set date value based on other row values

Sorry for the Header, I can't find the right way to describe what I want, possibly the reason why I can't get a solution by googling :) ... Here my problem: I've got a table with the following ...
1
vote
2answers
780 views

Adding Data to Multiple Tables Using Single Query [closed]

I want to add data to multiple tables using a single INSERT INTO query, can I do this? Please guide me and provide me syntax if this is possible.
-1
votes
1answer
40 views

combining two CTE in a single query

Here i have two CTE and i need to select values from these two CTE as a single query. I can able to select values from a single CTE at a time but i dont know like how to merge these two CTE and select ...
0
votes
1answer
66 views

getting error incorrect syntax near @certificate

I have created a certificate and now I want to remove certificate signature and drop the certificate. When I supply the certificate name directly it is working but when I'm making use of local ...
0
votes
1answer
89 views

How to restore Network backup to Network Path in SQL Server 2005?

I am trying to restore network backup file to network path.. is there any way to do it? I am using SQL Server 2005 and code.... RESTORE DATABASE DB_NAme FROM DISK = ...
0
votes
3answers
109 views

How to improve this query performance by indexing properly?

I have following select query, and I need to improve the performance of this query. Can anyone suggest me how to convert the clustered index scan to index seek? DECLARE @now DateTime; DECLARE ...
2
votes
1answer
140 views

How to improve this queries performance by indexing properly?

Hi here i have a query and i need to improve the performance of this query.Can anyone suggest me like how to convert the clustered index scan to index seek. DECLARE @now DateTime; DECLARE ...
4
votes
2answers
206 views

Update Statement takes forever to complete [closed]

I have table around 1250000 records, and 80 columns Table Structure CREATE TABLE [dbo].[Table] ( [Column1] [int] IDENTITY(1, 1) NOT FOR REPLICATION NOT NULL, [Column2] [int] NULL, [Column3] [int] ...
3
votes
1answer
202 views

SQL Script to Drop old AD logins, Inactive SQL Logins and all related Users

Related Question: SQL Server script to delete accounts no longer in Active Directory Whilst the above question points out the rather handy sp_validatelogins procedure, i'd like to extend this ...
2
votes
1answer
94 views

Avoiding Deadlocks

We currently have a trigger-based system in place that does the following: When records in certain tables are modified, a trigger fires that makes a field NULL in a varying number of records in ...
1
vote
0answers
149 views

SQL command in stored procedure continue on error

To perform Checkdb on all database of server i am using a stored procedure which loops over a view which contains list of databases in server and the stored procedure performs checkdb on all databases ...
1
vote
2answers
323 views

Metric to standard unit conversion in SQL Server

I am giving simplified version of my two table which are as follow. tblRecords ID unit val --- --- --- 1 KV 3 1 BAR 4 2 KV 7 2 KG 5 3 C 37 tblUnit unit ...
1
vote
2answers
100 views

SP or query to dump “password policy” checkbox for all logins

sp_helplogins doesn't show a column for this. I need a listing of all logins, with the status of their "Enforce password policy" checkbox. This is for an auditor, and the standard practice is for ...
0
votes
1answer
190 views

T-SQL Gurus, I need help optimising these queries for performance

I would like to have suggestions on improving the performance of some queries. These will be run on SQL Server 2008 R2 against a single table called dbo.bids In a production environment the table ...
4
votes
1answer
249 views

Tips on improving/refactoring slow query?

Let's play lotto! Ok. I have a numbers table with 80 rows (numbers 1-80): create table dbo.numbers (knum tinyint primary key identity); I have a table with the drawid, and each number drawn. ...
1
vote
1answer
143 views

Is there a way to extract a report of or script all of the primary keys in a database?

I have just discovered that deployed copies of one of our databases have no primary keys. This is probably the fault of the SSMS Import/Export wizard, which has done the same to me. Our master copy of ...
7
votes
2answers
710 views

How to find parent rows that have indentical sets of child rows?

Suppose I have structure like this: Recipes Table: RecipeID Name Description RecipeIngredients Table: RecipeID IngredientID Quantity UOM What are some good ways for ...
2
votes
1answer
52 views

Strategies for summary data in parent record based on detail data?

I have parent-child relationship as shown below. I need to find the first order date for each customer. Obviously, writing a query to do this is fairly straight-forward but it does require reading all ...
0
votes
0answers
275 views

How to improve the performance for SQL Server Count(distinct()) and Sum() functions? [closed]

The below one is my query. It's taking 12 seconds for the execution process. The count(distinct()) and SUM() functions are taking long time for execution. I tried it after create the non-cluster index ...
4
votes
3answers
1k views

Cannot rebuild index, but there's no reason why not?

I've created a process whereby I am able to only rebuild indexes that need rebuilding(the process takes an hour and a half if I rebuild them all), and while it works beautifully, it gets stuck on one ...
1
vote
1answer
2k views

How can we get table name from inside trigger?

I need to pass the table name from trigger to procedure. ALTER TRIGGER [dbo].[Trg_ProjectCreation] ON [dbo].[Projects] AFTER INSERT AS BEGIN SET NOCOUNT ON; -- procedure call ...
0
votes
3answers
1k views

Easier way to handle so many isnull() situation

Is there any good way to avoid writing so many times isnull() function inside sproc ? I have stored procedure that use almost 30 times isnull() function, I think that I am miss a concept , but until I ...
1
vote
3answers
153 views

How to handle update or insert situations

From time to time my stored procedures looks like create procedure handle_data @fk int ,@value varchar(10) as begin if exists (select * from my_table ...
4
votes
2answers
319 views

Update strings for all cmdexec Jobs

Does anyone know if it's possible, (and furthermore advisable) to update all cmdexec strings in jobs on a server? We'd like to change our logging location, and in principle I'd think this could be ...
0
votes
2answers
661 views

Need a way to query a table, and JOIN it with the TOP 1 related record from an other table

As a follow up to this question, I'm wondering if there is a better and/or more efficient way to gather the data in question. As stated, this query does return me 95% of the data I need -- SELECT ...
2
votes
2answers
771 views

Why do I need to use a sub query to filter down a grouped select?

If I do this -- SELECT dv.Name ,MAX(hb.[DateEntered]) as DE FROM [Devices] as dv INNER JOIN [Heartbeats] as hb ON hb.DeviceID = dv.ID WHERE DE < '2013-03-04' GROUP BY dv.Name ...
11
votes
1answer
2k views

What exactly does “No Join Predicate” mean in SQL Server?

MSDN "Missing Join Predicate Event Class" says it "indicates that a query is being executed that has no join predicate". But unfortunately it does not seem to be as easy as that. For example, very ...
0
votes
1answer
2k views

how to return table on sql function

I am very new to sql create function i wanted to check data column value at table the purpose is to return full table where data is null , <0 , >1 the problem is i am trying make data is ...
2
votes
1answer
600 views

return percentage of records based on value in column

I'm working on a sql query that needs to get a percentage of the records based on the value of a particular column. The percentage is given by the user. A stripped down version of the query is below ...
0
votes
0answers
338 views

RAND in T-sql function

Can any one tell why t-sql not allowing using RAND function in side a function SELECT CAST (RAND() * 10000000 as varchar(20)) the error is Invalid use of a side-effecting operator 'rand' ...
0
votes
1answer
199 views

Having trouble with a T-SQL scalar variable during a create table [closed]

I am still a beginner, but I am having trouble with a T-SQL scalar variable during a create table. Can anyone tell me what is wrong with this script? -- T-SQL MSSQL SET NOCOUNT ON DECLARE @tt = ...
2
votes
3answers
3k views

Inserting rows into other table whilst preserving IDENTITY

I'm performing ETL logic on a SQL Server table. I'm going to be synchronizing data from one table to another. For all of the records that need to be added to the target table based on the source ...
2
votes
2answers
2k views

How to map another server through SQL Server Management Studio

I am trying to map another server by giving the command EXEC xp_cmdshell 'NET USE H:\568.256.8.358\backup_147 1234abc /USER:cranew /PERSISTENT:yes' I got an error with this network path not ...
1
vote
1answer
302 views

Query tuning help needed (Hash Match and Table Scans)

I need help optimizing the following query (returning ~8k rows): SELECT A.sys_id, 'AppSvrRels' = CAST(SUBSTRING((SELECT (', ' + T.name) FROM ( ...
5
votes
1answer
515 views

Query performance while using sqlcmd [duplicate]

Possible Duplicate: Running queries with SQLCMD vs. Running queries with SSMS I have a number of scripts that query the data from different tables and consequently update them. I have ...
1
vote
1answer
761 views

Trying to Build TreeView Query in T-SQL

I have parent records in two tables, Table1 and Table2. Some records in Table3 are children of Table1, and some are children of Table2. I would like to build a query that I can use to populate a ...
6
votes
1answer
452 views

CATCH block is triggered when it should not

On my neverending quest for shooting myself in the foot with save transaction I seem to have found even more ways to fulfill the quest purpose. The save transaction clause itself this time is out of ...
5
votes
3answers
1k views

Total of orders open on a given date for each date in a date range

This question is similar to Running total with count?, but please allow me to explain some further twists on the issue. I'm using SQL Server 2008, so the cursor option described by Aaron Bertrand ...
5
votes
4answers
25k views

Writing select result to a csv file

We need to write the SELECT query results to a csv file. How can it be done using T-SQL in SQL Server 2008 r2? I know that it can be done in SSIS, but for some reasons, we don't have this option. I ...
6
votes
3answers
2k views

RETURN statement before DEALLOCATE CURSOR

Question about use of Cursors in combination with RETURN in a SQL Server 2008 Stored Procedure Consider this example: CREATE PROCEDURE [dbo].[test] @ReturnEarly BIT = 0 AS BEGIN SET NOCOUNT ...
1
vote
1answer
248 views

SQL Server 2008 R2 - Weekly / Monthly Statistics

I'm new working with SQL Server 2008 R2 Express, and I´d like to gather some statistics every week / month for users who are connecting to this server. For instance, how many times they were ...
3
votes
1answer
140 views

Selecting 'edge' records in a sequence of data

I have a table of items which run sequentially by date with both a start and end for each entry: ID | Start | End ---------------------------------------- 1 | 2012-08-20 00:00 | ...
8
votes
2answers
184 views

Non-Clustered Indexes - keys and nonkeys

I just want to make sure I'm on the right track with these concepts, so any feedback would be greatly appreciated. Here's my theory from the query I've just optimised, through a process of trial and ...
4
votes
4answers
842 views

Speeding up COUNT(*) - WHERE clause slowing query

The following query takes 497 ms to run, if I remove the AND portion onwards it only takes 320 ms. Is there any way to speed this up? The only indexes I have on 'messages' table is a PK on ...
2
votes
3answers
7k views

Questions about handling NULLs and empty strings in nvarchar and numeric fields

I understand that questions similar to these pop up often around here. I have searched before posting these but I didn't find any QA threads that completely answer my questions. In a table, I ...
3
votes
1answer
2k views

sp_execute expects parameter '@handle' of type 'int'

I'm trying to check in a stored procedure if a table exists in my destination database. If it does not then I will create the table useing the information_schema tables from the source database. ...
2
votes
2answers
997 views

Is there any way to speed up a query with 3x UNION on the same large table?

Assuming that the following query is legitimate and executes successfully, is there any way to speed it up? SELECT foo from T1 --T1 is a huge (>5e6 rows) table with no indexes UNION SELECT bar ...
10
votes
2answers
1k views

Why does SELECT 1/2 return 0?

As the title says. I even tried SELECT CONVERT(NUMERIC, 1/2) which also returned 0. I am using SQL Server 2008.