In this live SQL Server 2008 (build 10.0.1600) database, there's an Events table, which contains a text column named Details. (Yes, I realize this should actually be a varchar(MAX) column, but whoever set this database up did not do it that way.) This column contains very large logs of exceptions and associated JSON data that I'm tryin...
I'm attempting to perform dynamic sorting of data that I'm putting into grids into our MVC UI. Since MVC is abstracted from everything else via WCF, I've created a couple utility classes and extensions to help with this. The two most important things (slightly simplified) are as follows: public static IQueryable<TModel> ApplySortOpt...
I've got the following trigger; CREATE TRIGGER trFLightAndDestination ON checkin_flight AFTER INSERT,UPDATE AS BEGIN IF NOT EXISTS ( SELECT 1 FROM Flight v INNER JOIN Inserted AS i ON i.flightnumber = v.flightnumber INNER JOIN checkin_destination AS ib ON ib.airport = v.ai...
What is the best way to restore a SQL Server database on our testing server from a snapshot taken in production. Database is running on Windows Server 2003 with SQL Server 2008 We are using Bamboo along with Ant scripts for continuous integration running on Ubuntu. I want to be able to restore the database to a backup taken from produ...
I have a table PeroidicDeduction and the fields are ID(auto-increment),TotalDeduction(e.g.it can be loan),Paid(on which the deduction for each month),RemainingAmount, What I want is when every time I insert or update the table---RemainingAmount will get the value of TotalDeduction-SUM(Paid)....and writ the following trigger...but dosen'...
I've got a table defined as : OrderID bigint NOT NULL, IDA varchar(50) NULL, IDB bigint NULL, [ ... 50 other non relevant columns ...] The natural primary key for this table would be (OrderID,IDA,IDB), but this it not possible because IDA and IDB can be null (they can both be null, but they are never both defined at the same time). Ri...
Microsoft SQL Server 2008 (SP1), getting an unexpected 'Conversion failed' error. Not quite sure how to describe this problem, so below is a simple example. The CTE extracts the numeric portion of certain IDs using a search condition to ensure a numeric portion actually exists. The CTE is then used to find the lowest unused sequence num...
While using SQL Server Management Studio (2008), is it possible to open a table in Edit mode through keyboard shortcut or even some special keyword I type while in "New Query" window? ...
Hi guys, I have a vb.net app , and I am creating and dropping temp tables. I just want to know whether the temp tables created programatically from some client app will get dropped automatically with dispose of application or we should have to drop it explicitly by code. I need to be sure that all tables will get dropped when I close m...
How do you unit test your T-SQL? Which libraries/tools do you use? What percentage of your code is covered by unit tests and how do you measure it? How do you decide which modules to unit test first? Do you think the time and effort which you invested in your unit testing harness has paid off or not? If you do not use unit testing, ca...
Hey chaps, I have following situation: SQL Server 2008 database, in full recovery model I created a full backup, and an hour later a transaction log backup (both to separate files, so I have a .bak file, and a .trn file) Now for testing purposes, I want to restore the database using these two files. I am doing this on the same serve...
In SQL Server Management Studio I created a maintenance plan but it won't work Error is; "Message Executed as user: LITESPELL-19C34\Administrator. Microsoft (R) SQL Server Execute Package Utility Version 10.0.1600.22 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. The SQL Server Execute Package Utility requires ...
Hi All, I am struggling finding an efficient way to find a distance between a Point that intersects a polygon and the border of that polygon. I was able to use the STDistance comparing the point to every point that made up the polygon but that is taking a lot of time. Using SPatial indexed wasn't much helpful because the STDistance is n...
Currently I am designing a database for use in our company. We are using SQL Server 2008. The database will hold data gathered from several customers. The goal of the database is to acquire aggregate benchmark numbers over several customers. Recently, I have become worried with the fact that one table in particular will be getting very ...
Hi folks, In SQL Server 2008, how do I update fields in a table only if their values differ with the values in the update statement ? For example: I have TableA with column FirstName whose value is 'Roger Moore' with an unique id of '007'. Now, i am calling an update statement but it should update the 'FirstName' field only if value...
Hi I am looking to use a database project in visual studio 2010 so that we can version control a database. I have used a database project before but we had a batch file that we had to add to any scripts we wanted to be run. I didn't set this up and I am not sure if this is the best way to go about it. We have just started using VS 201...
I was wondering if there was any recommended max size for MDF and/or LDF Files for an SQL server instance. For example, if I want to create a 400 GBytes Database, is there a rule to help me decide how many mdf files I should create ? or should I just go ahead and create a single gigantic 400Gbytes mdf file? If so is this going to someh...
I've got transactional replication configured from a database called DBProd to another database called DBWarehouse ; everything works fine, and transaction are usually replicated instantaneously to the warehouse .... which is my problem. I'd like to add a slight delay to the replication (something like 10 minutes), so that the replicate...
Is there a quick query I can run that returns all columns of the same name between two tables? I have many pairs of tables that I know are tied together, but I'm not sure which of the 50 or so columns they have in common. ...
Hello, please can someone help me? I'm writing a query for a report to show different results in the factory from the machines. There is speed, efficiency and downtime. The speed and efficiency columns are recorded hourly and the downtime is recorded as and when downtime occurs. This is the code I have to collect the speed and effici...