Using date of type char in where clause

I have a SQL Server database table with a char column named “DATE” (I know, really bad, but I didn’t create the database) that has dates stored in this format as strings: YYMMDD. I need to return records between these dates, so treat them as actual dates and I’ve tried every combination I know but […]

Sql sever fulltext search does not return all the results

I tried to use full text search for a table called “Business” in Sql sever 2008 here is the statement(the search term is in chinese) select * from Business biz where CONTAINS(biz.*,’家具’) And the I use like statement to do the same select * from Business where Name like ‘%家具%’ The Fulltext searc returns 8 […]

Cross-database view permissions

I’m working with a database (let’s call it DB_data) that contains all of the tables for a series of applications. In an attempt to minimize downtime during upgrades, a facade database (let’s call it DB_facade) has been created which has a view for each of the tables in DB_data. It also contains all of the […]

View temporary table`s data when debugging an MS SQL Function

I’m currently debugging an Ms SQL Function (SQL 2008). In this function, I have a variable declared this way: DECLARE @TempTable TABLE ( Id INT UNIQUE ); Then, I insert some records using an insert into…select statement. When debugging, I would like to see the records in this table. Is there a way to do […]

RowGuide and UNIQUE KEY

i need a GUID at row level in my table (name column RowGuid) datatype is uniqueIdentifier. Do I need also state it as a UNIQUE KEY (alternate key)? Ex: RowGuid uniqueIdentifier UNIQUE KEY Thanks

SQL 2000 – Enterprise Manager & Triggers

We use SQL 2000 Enterprise Edition. We have noticed that the triggers are not executed when we make data changes using the Enterprise Manager. That is, we execute a “Select” query, and we edit the results directly in the grid results table. The trigger is valid since it is executed as expected if we run […]

Stange column name appearing in nHibernate generated SQL

For some reason the following mapping seems to be creating me a very strange column name that I cant find anywhere. There error occures when trying to load the Events collection which is defined as a IList<Event> <?xml version=”1.0″ encoding=”utf-8″ ?> <hibernate-mapping xmlns=”urn:nhibernate-mapping-2.2″ namespace=”JeanieMaster.Domain.Entities” assembly=”JeanieMaster.Domain”> <class name=”Location” table=”Location” select-before-update=”false” optimistic-lock=”none”> <id name=”Id” column=”LocationId” type=”Int32″> <generator […]

Strange execution times in t-sql

I have two stored procedures, the first one calls the second .. If I execute the second one alone it takes over 5 minutes to complete.. But when executed within the first one it takes little over 1 minute.. What is the reason ! Here is the first one ALTER procedure [dbo].[schRefreshPriceListItemGroups] as begin tran […]

SQL query for distinct values

I have a table as below INTERPRO GOTERM1 GOTERM2 CONFIDENCE IPR123 1576 2345 1.000 IPR235 3459 4987 1.000 IPR356 9987 5567 1.000 IPR568 3345 3213 0.88 IPR342 7860 7654 0.88 Now I want any of the record with distinct confidence values only like below IPR123 1576 2345 1.000 IPR342 7860 7654 0.88

How to truncate decimal space in TSQL?

I am trying to get the size of database and wanna display it to the GUI. Thats what i am using select sum(((((CAST(saf.[size] as DECIMAL(18,4)) * 8192) /1024) /1024) /1024)) as ‘Size’ from sys.sysdatabases sdb inner join sys.sysaltfiles saf on sdb.dbid = saf.dbid where sdb.name = ‘testDB’ Result is :- 3.8329467773437500000 But i want it […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.