tsql

I Need to Discard Days of Month and Make Duplicate Months as Distinct

SELECT e.firstName,t.setDate, w.wageAmount FROM TimeSheet t INNER JOIN Employee e ON e.employeeID = t.employeeID INNER JOIN Salary s ON s.salaryID = t.salaryID INNER JOIN Wage w ON w.wageID = s.wageID INNER JOIN EmpHiringInfo ehf ON ehf.EmpHiringInfoID = s....

Update rows in a related table based on condition in parent table.

I have a table TABLE1 (PARENT TABLE) with columns StaffID (PK) Name CategoryID (FK) I also have another related table TABLE2 (RELATED TABLE) with columns LeaveID (PK) StaffId (FK) StartDate What i want to do is write a T-SQL query to update StartDate column of all rows in TABLE2 whose CategoryID in TABLE1 = '3' TABLE2 is rela...

"Warn on null primary keys" table design option in SSMS

I SSMS (2008R2) menu Tools --> Options --> Designers --> Table and Database Designers --> "Warn on null primary keys" I played checking and unchecking this checkbox but could not detect the differences. What is it for? My related questions, probably: NULL permitted in Primary Key - why and in which DBMS? Why to use “not null pr...

how to debug a long script in management studio?

I have a very long T-sql script, it is a single batch, something like DECLARE variables select into temptable... cursor... insert... update.. as I run the script I have (1 row(s) affected) (5 row(s) affected) [...] ERROR (2 row(s) affected) etc... I would like to run step by step the script to identify which is the command...

Create dll from SQL Server procedure

Hi! I have procedure on SQL Server! Now,is it possible to create dll from that procedure and if it is, how i can do that? Thanks! ...

Stored procedure with FAST_FORWARD cursor loop starts fast, ends slow

I have a stored procedure that uses a FAST_FORWARD cursor to chronologically loop over a set of ~300k records and assigns them to declaration sets based on the state of a lot of running variables and flags, partly implemented as table variables. I have given a lot of thought on how to do this set-based and I just can't do it. So I am stu...

SQL Server 2005 Get First and Last date for any Month in any Year.

I have a stored procedure that has to accept a month as int (1-12) and a year as int. Given those two values, I have to determine the date range of that month. So I need a datetime variable to represent the first day of that month, and another datetime variable to represent the last day of that month. Is there a fairly easy way to get th...

Help with sql query

Hello, I'm doing an sql query where I want to return a list that will contain the users / groups who will have access to an application. This is the scheme, we will see if a user have permissions, if not, we going to see the group. If both not, we see if there are any permissions on the application, if not, then will add the user to the ...

Recursive SQL query to speed up non-indexed query

This question is largely driven by curiosity, as I do have a working query (it just takes a little longer than I would like). I have a table with 4 million rows. The only index on this table is an auto-increment BigInt ID. The query is looking for distinct values in one of the columns, but only going back 1 day. Unfortunately, the Re...

where like over varchar(500)

I have a query which slows down immensely when i add an addition where part which essentially is just a like lookup on a varchar(500) field where... and (xxxxx.yyyy like '% blahblah %') I've been racking my head but pretty much the query slows down terribly when I add this in. I'm wondering if anyone has suggestions in terms of chan...

Delete multiple duplicate rows in table

I'm sure this has been asked before, but I was having a hard time finding it. I have multiple groups of duplicates in one table (3 records for one, 2 for another, etc) - multiple rows where more than 1 exists. Below is what I came up with to delete them, but I have to run the script for however many duplicates there are: set rowcount ...

SQL Query - Ensure a row exists for each value in ()

Currently struggling with finding a way to validate 2 tables (efficiently lots of rows for Table A) I have two tables Table A ID A B C Table matched ID Number A 1 A 2 A 9 B 1 B 9 C 2 I am trying to write a SQL Server query that basically checks to make sure for every value in Table A there exists a row for a v...

What is the difference between using a cross join and putting a comma between the two tables?

What is the difference between select * from A, B and select * from A cross join B ? They seem to return the same results. Is the second version preferred over the first? Is the first version completely syntactically wrong? ...

Sql Server 2000 openrowset issue.

I’m attempting to query and Excel spread sheet on a network share, using the openrowset function with no success. I get the following error: SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;DATABASE=\\server\Xdrive:\Spreadsheet.xls', 'Select * from [Sheet1$]') Server: Msg 7399, Level 16, State 1, Line 1 O...

SQL to detect similar records in the same database table

I have a requirement to loop through records in a database table and group items that have similar content. I want to match on a single column in the database and if there are similar records I want to extract the ID of each row and save it to another table e.g. if I had 10 similar rows they would be linked to one "header" record in anot...

i get an error when creating a variable and specifing the collation in sql server 2005

when i tried this: DECLARE @var nvarchar(500) collate Arabic_BIN i got that: Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'collate'. that is the full code, it works, i do not know how but the person who give it to me have used it successfully CREATE FUNCTION fn_RemoveTashkeel (@InputString nvarchar(2300) ) ...

How To Return All Instances Within ## Mile Radius of a List of Zipcodes

SQL Server 2008 has added some cool new Spacial types and given SQL Developers a lot more powerful ways of manipulating spacial data but it still eludes me how to efficently return, for example, only the locations that are within ## mile radius of a long list of zips (20 to maybe 15 thousand distinct zipcodes). Is there a simple way to ...

Insert into from temp table - creating too many rows

I am bulk importing CSV file into a temporary table and then inserting the rows into the target table if they do not already exists. When I start the process, my table has the following values VECH01 AAA 111 VECH01 BBB 222 VECH01 CCC 333 VECH02 AAA 111 VECH02 BBB 222 VECH02 CCC 333 When I import a CSV file with these values: VECH01...

Dynamic TSQL in a secure context

I have a database where the primary user logging into the database has no permissions to any of the tables directly. THe user needs to execute stored procedures which have been explicitly granted to it. This covers any straightforward CRUD operation that is needed. But now, I have a need to execute SQL Dynamically but I want to maintain ...

Trouble turning a Simple MsSQL select into a monitor

I have a SQL Statement that works as I want. select COUNT(*), MIN(emailed_to)from email.email_archive group by emailed_to order by COUNT(*) desc The output looks like this. 13 [email protected]; 8 [email protected]; 4 [email protected]; ... ... ... 1 deadlockFF17uyt...