I'm currently writing an SQL Query, where a few of the columns returned need to be calculated depending on quite a lot of conditions. I'm currently using nested case statements, but its getting messy. Is there a better (more organised and/or readable) way? (I am using Microsoft SQL Server, 2005) A simplified example: SELECT col...
I have a set of records in my MS SQL table. With Date as the primary key. But the Dates are only for working days and not the continues days. Eg: 1/3/2000 12:00:00 AM 5209.540000000 5384.660000000 5209.540000000 5375.110000000 1/4/2000 12:00:00 AM 5533.980000000 5533.980000000 5376.430000000 5491.010000000 1/5/2000 12:00:00 AM ...
I have a table of vehicles with registration numbers, and want to select a subset of them that are between some user-supplied 'from' and 'to' values. So lets say the table looks like this: id reg_num 1 DD1111 2 DD1112 3 DE2245 4 EE5678 5 EF6547 The SQL I have so far looks like this: select * ...
We have a query that is taking around 5 sec on our production system, but on our mirror system (as identical as possible to production) and dev systems it takes under 1 second. We have checked out the query plans and we can see that they differ. Also from these plans we can see why one is taking longer than the other. The data, schame ...
Hi All, I have this problem which I am trying to debug for a lot of time. The setup is like this: i. The application is a Windows application developed using VS2005, .net 2.0. ii. I use the Cyrstal reports component Crystal Report Viewer and dynamically display various reports in the same form. iii. The db is SQLSERVER Express 2005 ...
I am developing a web application using ASP .NET 2.0, VS 2008 and SQL Server 2005. I would like to Use SSRS 2005 for the various reports I need to build for this web application. I would like to convince the team that we should adopt SSRS as the main reporting platform for most internal and external web applications we have. What are t...
I'm securing the DB by only allowing interaction with the DB through a series of Sprocs; pretty common fare. I've dug up and modified a script which loops through and assigns the user EXECUTE permission for all non-system SProcs. It works a treat except that I'd ideally like to add it to the Master DB so that I can easily use it for any...
Hi all! When I attended a presentation of SQL Server 2008 at Microsoft, they did a quick gallup to see what features we were using. It turned out that in the entire lecture hall, my company was the only one using the Service Broker. This surprised me a lot, as I thought that more people would be using it. My experience with SB is that ...
Hi! I am trying to determine the size in bytes of the contents in a varbinary(max) field in sql server 2005, using sql. As I doubt there is native support for this, could it be done using CLR integration? Any ideas would be greatly appreciated. ...
When executing the following using a smalldatetime constraint on the returned results, I get zero results: Execute sp_proc @DateOfBirth = '01/01/1900' or Execute sp_proc @DateOfBirth = '1900-01-01' But when using the following varchar argument all the sudden i get results which correspond to 1 Jan 2000. Does smalldatetime imp...
Does anyone have experience using trace flags 2389 and 2390? SQL server supports two trace flags (2389 and 2390) which singly or combination can be used to solve the problem of getting a bad query plan on a parameterized query such as a stored procedure which filters on a column with continuously ascending values. Has anyone tried this...
Is it possible to analyze SQL Server 2005 Transaction logs using LogParser 2.2? If not, what is the alternative? Red Gate's tool only analyzes SQL Server 2000 and Lumigent's Log Parser is no where to be found. TIA ramesh ...
Ok! I'm ready to embark on some .NET development for the first time. I've recently installed Microsoft Visual Studio which included Microsoft SQL Sever 2005. What is the prefered method for programming against a development database? I want to write an ASP.NET application that uses a database and I'm not sure how to create one. In...
I have some C# in which I create a reader on a connection (ExecuteReader), then for every row in that reader, perform another command (with ExecuteNonQuery). In this case is it better that I use MultipleActiveResultSets=True on my connection or use multiple connections? ...
SSRS version is SQL 2005 x64 sp2 on Windows 2003 x64 sp2 and IIS 6 Clients run IE 6 on a mixture of Win2k sp4 and WinXP sp3 reports are accessed through the standard SSRS html viewer As the title suggests my issue is that, in SSRS reports which use toggle buttons to control visibility of sections, the graphics for the toggle buttons w...
I want to create a one off backup of a database that I can then restore on my local machine. I do not want to have any impact on the current media set. I see an option "Back up to new media set, and erase all existing backup sets". What I really want is "Bak up to new media set". ...
We receive multiple thousands of flat files per week currently, and I have a system that runs reports on these and exports them to PDF for our people to process and reference. I currently bulk load these into a database, make sure all fields/formatting is valid, export them, and truncate the tables on the next run. What I'm wondering...
Hi, I am trying to perform a simple calculation to some data returned from SQL Server: SELECT val1X, val1Y, val2X, val2Y FROM myTable I want to be able to calculate the following against the returned values and return just a single value - the result of the calculation below (this was originally written in VB6): If IsNull(val1X) O...
I recently imported about 60k records into a table that relates data in one table to data in another table. However, my client has since requested that a sort order be added to all 60k records. My hope is there is a nice clean way to auto generate these sort orders in a SQL Update. The finished data should look something like this: i...
What is the best/most efficient way to store old dates (pre-1753) in SQL Server 2005? I am not concerned with storing times - just dates. SQL Server's datetime data type can only hold dates back to January 1, 1753. The MSDN documentation states that there are date and datetime2 data types, but SQL Server Management Studio does not seem t...