Hi everyone. I have the following table in MSSQL2005 id | business_key | result 1 | 1 | 0 2 | 1 | 1 3 | 2 | 1 4 | 3 | 1 5 | 4 | 1 6 | 4 | 0 And now i want to group based on the business_key returning the complete entry with the highest id. So my expected result is: business_key | result 1 | 1 2 | 1 3 | 1 4 | 0 I bet that there is ...
Is a select * from myView faster than the query itself to create the view (in order to have the same resultSet): select * from ([query to create same resultSet as myView]) ? It's not totally clear to me if the view uses some sort of caching making it faster compared to a simple query. Any help would be appreciated! ...
I have a Database nearly 1.9Gb Database in size MSDE2000 does not allow DBs that exceed 2.0Gb I need to shrink this DB (and many like it at various client locations) I have found and deleted many 100's of 1000's of records which are considered unneeded. these records account for a large percentage of some of the main (largest) tables i...
Could someone please explain to me how arithmetic operators such as > or < compare strings in SQL Server 2005? ...
When there are a number of people working on a project, all of who could alter the database schema, what's the simplest way to unit test / test / verify it? The main suggestion we've had so far is to write tests for each table to verify column names, constraints, etc. Has anyone else done anything similar / simpler? We're using C# with ...
What advantages does SQLServer CLR offer over T-SQL? Is using .NET syntax easier than T-SQL? I see that you can define user types, but I'm not quite clear on why that's better. For example, you could define an email type and it would have a prefix property and a domain property. You could then search on domain or prefix or both. However...
What should be the data model for a work flow application? Currently we are using an Entity Attribute Value based model in SQL Server 2000 with the user having the ability to create dynamic forms (on asp.net), but as the data grows performance is getting down and hard to generate report and worse if too many users concurrently query the ...
I am trying to keep a record of all of the emails that get sent via xp_smtp mail component, but I don't know hw to store the message. Storing a 1 because it failed doesn't do me much good. DECLARE @rc INT EXEC @rc = master.dbo.xp_smtp_sendmail... --Once the email is sent put the results (pass or fail) into another table: EXEC pr_Inser...
I have an application that uses MS SQL Server for which I'll need to do a bulk insert from a file. The sticking point is that the database and my application will be hosted on separate servers. What is the best way to do a bulk insert across a network? Two ideas I'd come up with so far: From the app server, share a directory that th...
This is more of a syntax question I'm trying to write a store procedure or function that I can embed into a query such as: select * from MyBigProcOrFunction I'm trying to define a tabular function but i do not understand how to do it as I build tmp tables to work out the data before i finally have the return at the endtable. My mark up...
I am supposed to perform ETL where source is a large and badly designed sql 2k database and a a better designed sql 2k5 database. I think SSIS is the way to go. Can anyone suggest a to-do list or a checklist or things to watchout for so that I dont forget anything? How should I approach this so that it does not bite me in the rear later ...
I have a few remote SQL servers that I need to pull a large amount of data from regularly (say 5,000,000 rows per server). This data also needs to be formatted and FTPed to another server. The dial-up portion is fine, I can connect and communicate with the server, but sometimes the connection is slow, maybe only 19Kbps. Once connected I...
Problem: How to most efficiently move data from an array into a SQL Server table. Details: I have created an array with many rows (typically, about 100,000) and many columns (about 40) in memory in a WinForms application. I need to get this array into a corresponding SQL Server table in the fastest way possible. Right now, I am creating...
A query runs fast: DECLARE @SessionGUID uniqueidentifier SET @SessionGUID = 'BCBA333C-B6A1-4155-9833-C495F22EA908' SELECT * FROM Report_Opener WHERE SessionGUID = @SessionGUID ORDER BY CurrencyTypeOrder, Rank subtree cost: 0.502 But putting the same SQL in a stored procedure runs slow, and with a totally different execution plan CR...
Hi I'm an ASP.NET virgin and want to try creating an ASP.Net Application using SQL Server at the back end. I can't locate a single example application or code for doing this. Anyone have any pointers? TIA ...
How do you write number in two decimal place for sql server? ...
I ran a profiler today and found that one of my stored procedures is taking a very high CPU read (1899999) and duration (3233333). How can I fix this problem? ...
Hey all, I'm pulling my hair out on this one. I've checked all my permissions, on both the database server (SQL Server 2000) and the file system to ensure that what I am trying to do should be possible. Here's the situation: I have a Java EE web application running on a Tomcat server on my company's intranet. Long story short, this app...
This is what I am trying to do Declare @Var varchar(50) Declare @Num1 varchar(50) Declare @Num2 varchar(50) Declare @Counter smallint Set @Counter=1 Set @Num1='Hello' Set @Num2='Hi' while (@Counter<2) begin Set @Var=N'@Num'+convert(varchar,@Counter) //Now I want to get the value of '@Num1' that is stored in @Var when @Count...
Dear StackOverflow, I'm trying to do the following in the VS2008 Linq O/R designer, SQL 2005: I have a table called "Entity" with an autoincrementing primary key named "PKey", and another field called "Parent" I've made a View from this table, calling it vwEmployees, with a simple where clause. Add both to O/R designer. in O/R, set th...