Note: This is a community wiki entry and is mainly to document a problem together with its solution. I was hardly able to find information on the net to solve this. Hope it helps someone! I have a SQL-Server 2005 DB with the data tables being on the dbo schema. For a new component on the project, I created a new data access layer (us...
The Locks performance object on SQL Server provides three counters that I'm interested in. X = Average Wait Time (ms) Y = Lock Wait Time (ms) Z = Lock Waits/sec From their descriptions I'd have imagined that X = Y / Z. However, while this is true for SQL2005, it does not seem to be true for SQL2000. I have some code that forces a dea...
Is it a good idea to store audio files (mp3, wav) in SQL Server as BLOBs? One of the merits appears to be saving Hard disk space. Does this have any impact on scalability or performance? ...
My problem is that for some reason there is a 15min delay between replication alerts (at the distribution agent), even when the underlying cause is the same transaction as what caused the previous alert. It's frustrating when I think that I've fixed the problem and then have to wait ages to see if it has any more to throw at me. Does an...
We have table with several full text indexed fields. Say, it is firstName, surName and lastName. I'm looking for "John AND Smith" and obviously these two words most likely will be written in different fields. Query returns nothing. If I search for "John OR Smith" it is working. The question is: can I somehow tell SQL Server that all fie...
I have the below SQL Trigger on a SQL 2005 box that is supposed to help me replicate certain Person Info to another database to be used for Reporting and various other things as a reference. I have 1 DB named Connect which is where the current app manipulates the Person Data on tblPerson. I have another DB, on same physical box, named ...
I need to execute a custom code function when the report first loads, and I need it to only run once. Where do I put it? ...
Is it possible to turn it on in a table trigger? I tried creating a trigger with SET IDENTITY_INSERT tableName ON But when I open the trigger definition, I see that the statement is not there.... This is my query to alter my trigger to add the IDENTITY_INSERT, when I open the definition, the IDENTITY_INSERT is removed ... SET ANSI_...
In the query below is it better for performance to move the case statement to a UDF? If so why? SELECT id, name, case when v1 = 'Y' then 'something' when v2 = 'K' then 'something else' when v3 is null then 'dont know' else 'default' end from table ...
I'm interested in the side effects and potential problems of the following pattern: CREATE PROCEDURE [Name] AS BEGIN BEGIN TRANSACTION BEGIN TRY [...Perform work, call nested procedures...] END TRY BEGIN CATCH ROLLBACK TRANSACTION RAISERROR [rethrow caught error using @ErrorNumber, @ErrorMessage, ...
I have a table with two columns of GMT time in seconds, and offset in minutes like this: SELECT TOP 1 StartTime, OffSet FROM MyTable; 1247242537 -420 -- as example What SQL function I can use to convert it to a datetime in yyyy-MM-dd hh:mm:ss as TimeStamp? The SQL I am using is SQL Server 2005. ...
Please look at following image, I have explained my requirements in the image. I can't use here WHERE UsageTypeid IN(1,2,3,4) because this will behave as an OR condition and fetch all records. I just want those records, of first table, which are attached with all 4 ShipmentToID . All others which are attached with 3 or less Shipmen...
I am trying to compare two tables, SQL Server, to verify some data. I want to return all the rows from both tables where data is either in one or the other. In essence, I want to show all the discrepancies. I need to check three pieces of data in doing so, FirstName, LastName and Product. I'm fairly new to SQL and it seems like a lot of...
I have just installed Sql Server Compact Edition. To my surprise, we can'nt use stored procedure in sql server CE. Do I have any alternative of Stored Procedure in Sql Server CE. I am strongly obseesed with stored procedure, I can'nt think of an application without stored procedures. Please help, Thanks in advance. Edit: Can I use Ma...
Lets say we have a following table with two columns and following rows of data in SQLServer-2005: Tiger 50 Wolf 4 Tiger 53 Lion 55 Elephant 54 Rhino 52 Lion 5 Can we have a sql query that result as following: Tiger,Wolf,Lion,Elephant,Rhino as a single string varchar output?Is it possible ? using T-SQL not possibl...
Hi there, delete activities where unt_uid is null would be the fastest way but nobody can access the database / table until this statement has finished so this is a no-go. I defined a cursor to get this task done during working time but anyway the impact to productivity is to big. So how to delete these record so that the normal use...
I want to create view that combine data from two tables, sample data in each table is like below. SELECT Command for TableA SELECT [ID], [Date], [SUM] FROM TableA Result ID | Date | SUM 1 | 1/1/2010 | 2 1 | 1/2/2010 | 4 3 | 1/3/2010 | 6 SELECT Command for TableB SELECT [ID], [Date], [...
I have created an INSTEAD OF INSERT trigger on a view in my database. I want to know which columns are included in the column list of the INSERT statement on the view. If you read the MSDN documentation for triggers the UPDATE() and COLUMNS_UPDATED() functions should satisfy this requirement. However, during my testing I found that re...
I really hate this application, it always generates tables & procedures I didn't select. Is there a better software to extract specific SP's and Table schemas? Note: for Microsoft SQL Server 2005 ...
Hi guys, I want to convert a DATETIME field (DateOfBirth)... When I execute the following query it says invalid syntax near convert SELECT e.Emp_Id,e.Emp_Name,e.Address,e.Department, (convert(varchar, e.Date_Of_Birth, 103) as Date_Of_Birth) from Employee as e But when I execute the below query it gives result but my dateofbirth colu...