sql-server

What is the difference between type and xtype in system views in SQL Server?

There are several columns in many of the system views that have the 'x' prefix, does anyone know what this is suppose to signify? ...

Rollback complete database one hour

We had some problems this morning and need to rollback our database for about one hour. Is this possible and how is it done? It is a Microsoft SQL 2005 database. BR Larre ...

Calculating different tariff-periods for a call in SQL Server

For a call-rating system, I'm trying to split a telephone call duration into sub-durations for different tariff-periods. The calls are stored in a SQL Server database and have a starttime and total duration. Rates are different for night (0000 - 0800), peak (0800 - 1900) and offpeak (1900-235959) periods. For example: A call starts at ...

SQL Server 2005 64bit query blocking

Hi We are experiencing some difficulties with SQL Server performance and wanted some help. Our environment is: - Windows 2003 Enterprise x64 Edition R2 Intel E5450 Quad Core 3ghz Processor 16GB RAM SQL Server 2005 64bit Enterprise Edition (9.00.3282.00) Database compatibility is 8 (but tested on 9 as well) Hyperthreading is switched ...

A work around Group By Clause Limitation

I'm working on a social network web application, and i got a situation where i need to resend reminder emails to users who haven't activated their emails. The problem is when i investigated the DB i found that many emails are duplicated (there was no validation on the email uniqueness apparently. So what i need to do know is to retrieve ...

mssql_query accent

I hava a problem with this mssql_query in PHP : $query = 'SELECT Ville FROM tblLstManufacturiers where province = "Québec"'; The result is empty because I have an accent in Québec How can I do this? ...

Any good spatial database tutorials out there?

I was looking to explore the spatial capabilities of PostGIS or SQL Server 2008. Maybe Oracle as well. I was wondering if there are any good tutorials out there exploring the spatial capabilities of these database in particular or of this type of capability in general. Does anyone know of any? Book recommendations would work as well. ...

Database Properties "Mirroring" Page

I am having a problem with one database on my SQL Server 2005 production server. A number of databases are already set up for mirroring, however when I right click and go to properties in SSMS, on one particular database there is no "Mirroring" property page available. I have done the normal tasks, such as setting Full Recovery model, ...

Finding the T-SQL to return these values

I'm trying to write a stored procedure that will return two calculated values for each record according to the rules below, but I haven't figured out how to structure the SQL to make it happen. I'm using SQL Server 2008. First, the relevant tables, and the fields that matter to the problem. ProductionRuns RunID (key, and RunID is giv...

SQL: Select TOP N Marks Per User (In a List of Users)

I am having difficulty writing a Stored Procedure that will query a list of students with their associative marks. Retrieving a List of Students - trivial Retrieving the top five marks per student - trivial...SELECT TOP (5) * WHERE StudentID = X Combining these two, I am a bit confused. I would like the Stored Procedure to return two ...

Setting up a Reporting Server to liberate resource from a webserver.

Hi everyone, Yay, first post on SO! (Good work Jeff et al.) We're trying to solve a bottleneck in one of our web-applications that was introduced when we started allowing users to generate reports on-demand. Our infrastructure is as follows: 1 server acting as a Webserver/DBServer (ColdFusion 7 and MSSQL 2005) It's serving a web-appl...

Deploying a WCF Service

I will be deploying my first WCF service in the next few days on a Test server (MS Server 2003 O/S). This particular service will do nothing more than run some queries against a SQL Server 2005 (most likely on the same box) and return the values to the caller. My question is, unlike the "classic" web services, with WCF I now have cho...

SQL Problem: Using CONTAINS() doesn't work, but LIKE works fine

I have a Products table in a SQL Server database and I am having to troubleshoot a legacy stored procedure that uses Full-Text indexing. For our purposes here, lets suppose that the Products table has two fields ID, Keywords. And the Keywords field is populated with the following: ROLAND SA-300 This Roland SA-300 is in MINT conditi...

Full-text Indexing for a view with multiple databases

Can MS SQL support full-text indexing for a view that connects (joins or unions) multiple databases? ...

Why is it so difficult to do a loop in T-SQL

OK, I know it can be done, I do it quite often, but why so difficult to do a loop in T-SQL? I can think of a ton of reasons I'd want to parse thru a query result set and do something that simply can't be done without a loop, yet the code to setup and execute my loop is > 20 lines. I'm sure others have a similar opinions so why are we st...

How does SET NOEXEC ON work?

I have the following code: -- start of code set noexec off declare @requiredVersion int declare @currentVersion int set @requiredVersion = 5 set @currentVersion = 4 if (@currentVersion < @requiredVersion) begin print 'Please update your DB to version 5 before running this script.' set noexec on end go -- print 'Dummy' insert...

Optimizing a Query to Build Averages

Hello All, I recently built a query in SQL that I can use to look at our payment log and find out an average number of payments made per hour over a period of time. I'm sure there are third party reporting applications that are much more suited for doing the type of calculation I'm trying to do, but just for fun, I'm kind of curious t...

How could I generate a contextual text extract from text returned from a SQL Server Full-Text Index?

I'm experimenting with an idea using SQL Server's Full Text Indexing. It seems perfect for the task, but what my client wants is a very Google-like results summary, where the results display an extract of the text around their search term. If I search for "house"... My House Is a Very, Very, Very Fine House ...thanks for coming t...

Delphi 2007 Pro - Best way to connect to SQL Server

I usually use Delphi-targeted databases for most of my work (NexusDB typically, lately), but still have bad memories of how painfully slow connecting (and posting) to MS Access was via ADO. I have a new project that may need to target MS SQL Server. For D2007 Pro, what is the best way to connect to MS SQL Server? (Third party componen...

How to get the data source information from a SSRS report, using .NET

I am currently making an ASP.Net and C# page, which is a front end for some reports. I also want to run some queries, from the same data source as the reports (each report uses just 1 data source). Is it possible to extract the data source connection information from a report, using either ReportingService2005 or ReportExecutionServi...