Selecting 3+ rows in SQL
how to select 1st, 4th, 7th, 10th... row from a table in SQL if i HAVING RECORDS LIKE BELOW id Name 1 a 2 b 3 c 4 b 5 s 6 h 7 k 8 g i need to select 1 st, 4th, 7th, 10th rows pls help me thanks Janarthanan M ...
sql-server-2005
To Create SQL Report, Should i Include Aggregate Functions in SQL Query or Do it via Reporting Tools?
While learning about creating MS SQL Reporting and deploying reports to SQL Reporting Server, i found that there is no need to worry about creating a query that involves sorting, grouping and so on. All i need to do is fetch the columns and data i need from various tables and then once the data loads in dataset, i can use the GUI tools t...
TSQL Finding Order that occurred in 3 consecutive months
Please help me to generate the following query. Say I have customer table and order table. Customer Table CustID CustName 1 AA 2 BB 3 CC 4 DD Order Table OrderID OrderDate CustID 100 01-JAN-2000 1 101 05-FEB-2000 1 102 10-MAR-2000 1 103 01-NOV-20...
Drop constraints for a certain column
I need to delete all constraints for a certain column. I found this related SO question but the indicated script actually deletes all constraints related to a certain table, not only for certain columns in it. Is there a way of dropping only constraints applied on a given column? Alternatively, my bigger problem is, how to drop a colum...
sql scripting variable default value
I have a script file e.g. test.sql. I want to call this from another script, say caller.sql, in sqlcmd mode using :r test.sql. This works fine, but I want to use a scripting variable in test.sql. When I call test.sql from caller.sql I can set the scripting variable and all is well. However, I want to use a default value for the scrip...
Best paging solution using SQL Server 2005?
What is the most efficient paging solution using SQL Server 2005 against a table with around 5,000-10,000 rows? I've seen several out there but nothing comparing them. ...
SQL: How to create reports in SQL Server 2005/2008 Reporting Services using XML data?
Any suggestions on how to create report/s in SQL Server Reporting Services using XML data (found in existing table/s)? ...
what might be the query to fetch all the records seperated by comma in SQL ?
In SQL, suppose i have a table employee and have a column as Name containing all the names in the format FirstName,LastName. Ex:Nayeem,Khan . I want to fetch all the last names only ie Khan. ...
SQL Server 2005, Calculating upcoming birthdays from date of birth
This one has bugged me for a while now. Recently when revisiting some code I wrote for a customer a few years ago I was wondering if there is a more elegant solution to the problem. The customer stores all of their clients information including date of birth (date time field) They run an extract every Monday that retrieves any customer...
SQL Start date is current day then end date is 1 month ago even if its not from the first to the last of the month
Ok I am trying to write a query that says get the current date and make it the start date. Then I want to go a month back from that current date for the EndDate. Is it possible to do this? Like if it was 9-15-2010 as the start date can I go a month back from that to 8-15-2010 or is this no possible....what would you do for like 9-20-2010...
The body of the email can't be seen when using sp_send_dbmail in sql server 2005
I create a procedure to send automatic emails. The email gets to the address and everything seems working fine but the body can not be seen. I'm using sql 2005 and MS exchange server 2007. The part of the procedure that writes the body is as follow. declare @bodymsg as varchar(1000) set @bodymsg = 'The application ' set @bodymsg = @b...
How to check when autogrowth is done last?
In sql server 2005, the autogrowth is enabled by size. Is there any way to check when autogrowth on data and log file happened last? ...
adding a column description
Does anyone know how to add a description to a SQL Server column by running a script? I know you can add a description when you create the column using SQL Server Management Studio. How can I script this so when my SQL scripts create the column, a description for the column is also added? ...
Is it better to query on the FK or the PK in a join query
Using SQL2k5 and assuming the [ID] columns are the clustered PK and the [FK...] columns have a nonclustered index, of the two queries, which WHERE clause is more efficient? SELECT * FROM [table1] INNER JOIN [table2] ON [table2].[ID] = [table1].[FK_table2] INNER JOIN [table3] ON [table3].[ID] = [table1].[FK_table3] WHERE [table1].[FK...
Stored Procedures programmed to fire in separate serial transactions appear to execute simultaneously
I have a .net application that has to execute a series of steps in a job. Each step lives in its own transaction, and all tasks are executed serially (no explicit async). The last two tasks are a data transform, and a data retrieval of the transformed data. We are getting deadlock errors from these last two steps, because the last step l...
List of User permissions on all databases for a user
Is there any way can we know the list of user permissions on an sql server instance through script or tool ...
connect vb 2008 with sql server 2005 locally
Hi,I'm trying to connect vb 2008 with sql server 2005 locally. I have the database at the same laptop but I always get an error. Here's my code: Dim strconn As String strconn = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Database;Data Source=(local)" Try Dim connection As New SqlClient.SqlConnection(str...
Finding who is whose manager from the give table. I have given the reqired table and resultant table below. Give query for SQL server 2005
------------------ ID Name Mgr ------------------ 1 Ajay 4 2 Vijay 5 3 Nayeem 1 4 Rakesh 3 5 Varun 4 6 Bhupesh 1 ----------------- I want the result as ----------------- Name Mgr ----------------- Ajay Rakesh Vijay Varun Nayeem Ajay Rakesh Nayeem Varun Rakesh Bhupesh Aja...
UPDATE table from another table with case statement
Hi there! I'm currently having a problem. I need to update Table A from Table B based on this condition: If one record in Table A is null (ex. name) then update that record from Table B Here's my error driven script which I thought from my head. This is what I wanted to happen. UPDATE TableA SET NAME = ( CA...
Retrieve file list of an SQL server database which is offline.
Hi, I have some offline databases on a SQL server. I would like to know which files on disc are related to these databases. Is it possible to retrieve the file list of offline databases without taking them online first? cheers, Achim ...