I have a large table with 1 million+ records. Unfortunately, the person who created the table decided to put dates in a varchar(50) field. I need to do a simple date comparison - datediff(dd, convert(datetime, lastUpdate, 100), getDate()) < 31 But it fails on the convert(): Conversion failed when converting datetime from character s...
I have a SQL script that inserts data (via INSERT statements currently numbering in the thousands) One of the columns contains a unique identifier (though not an IDENTITY type, just a plain ol' int) that's actually unique across a few different tables. I'd like to add a scalar function to my script that gets the next available ID (i.e....
We have a couple of mirrored SQL Server databases. My first problem - the key problem - is to get a notification when the db fails over. I don't need to know because, erm, its mirrored and so it (almost) all carries on working automagically but it would useful to be advised and I'm currently getting failovers when I don't think I should...
I am about to migrate a bunch of seriously over-complex Reports (MS SQL report server over some horrible stored procedures) to something more BI based. I was wondering if anybody had any pointers, gotchas, or general advice on the best way to do this. Thanks. ...
I have a development vm which is running sql server as well as some other apps for my stack, and I found that the other apps are performing awfully. After doing some digging, SQL Server was hogging the memory. After a quick web search I discovered that by default, it will consume as much memory as it can in order to cache data and give...
I have a sproc that puts 750K records into a temp table through a query as one of its first actions. If I create indexes on the temp table before filling it, the item takes about twice as long to run compared to when I index after filling the table. (The index is an integer in a single column, the table being indexed is just two column...
I have a sql server database that I am querying and I only want to get the information when a specific row is null. I used a where statement such as: WHERE database.foobar = NULL and it does not return anything. However, I know that there is at least one result because I created an instance in the database where 'foobar' is equal to nu...
I've always thought the answer to this was "no", but let's give CrackOverflow a shot... Is it possible to get a breakdown of CPU utilization by database? I'm ideally looking for a task manager type interface for SQL server, but instead of looking at the CPU utilization of each PID (like taskmgr) or each SPID (like spwho2k5), I want to v...
I need to specifically catch SQL server timeout exceptions so that they can be handled differently. I know I could catch the SqlException and then check if the message string Contains "Timeout" but was wondering if there is a better way to do it? try { //some code } catch (SqlException ex) { if (ex.Message.Contains("Timeout"))...
I have about 150 000 rows of data written to a database everyday. These row represent outgoing articles for example. Now I need to show a graph using SSRS that show the average number of articles per day over time. I also need to have a information about the actual number of articles from yesterday. The idea is to have a aggregated view...
Trying to make a MySQL-based application support MS SQL, I ran into the following issue: I keep MySQL's auto_increment as unsigned integer fields (of various sizes) in order to make use of the full range, as I know there will never be negative values. MS SQL does not support the unsigned attribute on all integer types, so I have to choo...
I have a History Table in SQL Server that basically tracks an item through a process. The item has some fixed fields that don't change throughout the process, but has a few other fields including status and Id which increment as the steps of the process increase. Basically I want to retrieve the last step for each item given a Batch Ref...
I am writing a query in which I have to get the data for only the last year What is the best way to do this SELECT ... From ... WHERE date > '8/27/2007 12:00:00 AM' ????? ...
Are there any excellent sources (book, podcast, tribal folklore, etc) fo rgetting a grasp on using OLAP for data analysis in SQL Server (or just in generic terms that can be applied to SQL Server)? ...
I have one field that I need to sum lets say named items However that field can be part of group a or b In the end I need to have all of the items summed for group a and group b when I say grouped I mean there is a LEFT OUTER JOIN to another table the previous table has a type for the items and the one being joined has a group assigned ...
I didn't see any similar questions asked on this topic, and I had to research this for something I'm working on right now. Thought I would post the answer for it in case anyone else had the same question. ...
It seems like the generation of SQL scripts from the SQL Server Management Studio is terribly slow. I think that the old Enterprise Manager could run laps around the newer script generation tool. I've seen a few posts here and there with other folks complaining about the speed, but I haven't seen much offered in the way of alternatives. ...
I am reading in CSV file and translating it to a SQl Table. The kicker is that one of the columns in the table is a type ID that needs to be set to a constant (in this case 2). I am not sure how to do this. ...
Hi all; Using SqlServer, and trying to update rows from within the same table. I want to use a table alias for readability. This is the way I am doing it at the moment: UPDATE ra SET ra.ItemValue = rb.ItemValue FROM dbo.Rates ra, dbo.Rates rb WHERE ra.ResourceID = rb.ResourceID AND ra.PriceSched = 't8' AND r...
the title says it all... ...