I'm trying to programmatically add an identity column to a table Employees. Not sure what I'm doing wrong with my syntax. ALTER TABLE Employees ADD COLUMN EmployeeID int NOT NULL IDENTITY (1, 1) ALTER TABLE Employees ADD CONSTRAINT PK_Employees PRIMARY KEY CLUSTERED ( EmployeeID ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP...
Hi, Can some kind soul please lend me the Linq To SQL query for the following T-SQL Query SELECT e.EmployeeName, MIN(a.SignInTime), MAX(a.SignOutTime) FROM Employee e LEFT OUTER JOIN Attendance a ON e.Id = a.EmployeeId AND CAST (CONVERT (varchar, a.SignInTime, 106) AS DATETIME) = '28 APR 2009' GROUP BY e.EmployeeName The database sch...
I am working off this example. http://www.drury.net.nz/2005/04/15/specifying-a-sort-parameter-for-a-tsql-stored-procedure/ CREATE PROCEDURE getEmployees ( @ColumnName varchar(100) ) AS SELECT EmployeeID, FirstName, LastName, SSN, Salary FROM Employees ORDER BY CASE WHEN @ColumnName=’...
I have a web application that uses a fairly large table (millions of rows, about 30 columns). Let's call that TableA. Among the 30 columns, this table has a primary key named "id", and another column named "campaignID". As part of the application, users are able to upload new sets of data pertaining to new "campaigns". These data sets...
Creating XML out of data in Database by calling proc with bcp as SET @SQL= 'bcp "exec dbo.proc" queryout '+ @FileName +' -w -r -t -Sdd\SQL2005 -T ' (proc produced below) Everything is fine => creates XML as desired. Now task is to Add Declaration to this XML (<?xml version="1.0" ?>) How can this be achieved either in below proc or...
Is it possible to execute a stored procedure in a view? I.e. CREATE VIEW [dbo].[v_ReportInvoiceClientsThisMonth] AS EXEC [dbo].[GetInvoiceClients] @startDate = '2009-03-01', @endDate = '2009-04-01' (doesn't work) The reason I need this is that I need a way to access the SP in Excel (in an idiot safe way, i.e. without VBA). ...
How to delete N oldest entries. I'm limited Sybase. I need to write a stored procedure which would accept a number X and then leave only X newest entries in the table. For example: Say ID is auto incremented. The smaller it is, the older this entry is. ID Text ========= 1 ASD 2 DSA 3 HJK 4 OIU I need a procedure which would...
I've inherited a legacy system with lots (160+) of stored procedures. I'm looking for a way to create a diagram that would show which of the procedures are called by other procedures show which part of the C# code uses which stored procedures group the procedures by the parts of the system in which they are used Is there a tool that ...
Why do Scalar-valued functions seem to cause queries to run cumulatively slower the more times in succession that they are used? I have this table that was built with data purchased from a 3rd party. I've trimmed out some stuff to make this post shorter... but just so you get the idea of how things are setup. CREATE TABLE [dbo].[GIS_L...
What are the advantages, if any, of explicitly doing a HASH JOIN over a regular JOIN (wherein SQL Server will decide the best JOIN strategy)? Eg: select pd.* from profiledata pd inner hash join profiledatavalue val on val.profiledataid=pd.id In the simplistic sample code above, I'm specifying the JOIN strategy, whereas if I leave off ...
I have a table of name value pairs where I am storing tags: TAGID | NAME | VALUE I have a table of 'Things' this tags apply to THINGID | TAGID I need a query to generate a resultSet were the columns/fields are all possible TAG-NAMES (NAME field in the TAG table)for a given THINGID and the values are the correspondent tag values. T...
ihave at table with columns sales(int),month (int) . i want to retrieve sum of sales corresponding to every month .i need ouput in form of 12 columns corresponding to each month.in which there will be single record containing sales for for each column(month) ...
I was looking at the source of sys.sp_dbcmptlevel in SQL Server 2005. In the source, there is this line I do not understand how it works. EXEC %%DatabaseEx(Name = @dbname).SetCompatibility(Level = @input_cmptlevel) It doesn't appear that DatabaseEx is a stored procedure. -- does not return any result select * from sys.procedures whe...
What's the equivalent of an MS-Access crosstab query in TSQL? And Is there a better way? I have a data organised like this: Fish ID Name ---- --------- 1 Jack 2 Trout 3 Bass 4 Cat FishProperty ID FishID Property Value ---- ------ -------- ----- 1 1 Length 10 2 1 Girth 6 3 1 Weight 4 4 ...
Does anyone have an example of a stored procedure which makes a connection to a remote server? I have been searching the web and have so far discovered that it might can be done using sp_addlinkedserver and sp_addlinkedsrvlogin but I haven't found a good example and I don't understand the documentation that well. UPDATE: None of the t...
I have a piece of dynamic SQL I need to execute, I then need to store the result into a variable. I know I can use sp_executesql but can't find clear examples around about how to do this. ...
Normally we use the TSQL TOP to get first number of rows of data like this: SELECT TOP 10 * FROM myTable; I am not sure if there is any way to get the next 10 and next 10, ... till to the end, just something paging. I cannot figure out. May be CTE statements? ...
I need to get a set of distinct records for a table along with the max date across all the duplciates. ex: Select distinct a,b,c, Max(OrderDate) as maxDate From ABC Group By a,b,c The issue is I get a record back for each different date. Ex: aaa, bbb, ccc, Jan 1 2009 aaa, bbb, ccc, Jan 28 2009 How can I limit this so I end up w...
Question: How do I store a selected field value into a variable from a query and use it in an update statement? Here is my procedure: I'm writing a sql server 2005 tsql stored procedure which does the following: 1. gets list of invoices id's from invoice table and stores to Cursor 2. Fetch invoice id from cursor -> tmp_key variable...
I have an existing program deployed where some customer databases have a field set to not null, while others are nullable. I need to run a patch to correct the database so that the column is nullable but do not need to run it against all databases, just ones where it is incorrect. Is there a simple method that can be used in SQL Server...