sql-server

Integrating Spring + Hibernate + Sql server with unicode support

How could I integrate Spring with Hibernate using sql server 2005 and have Unicode support. I tried many different ways but I just couldn't get it to work. Column in the table is nvarchar, character set in Spring is UTF-8. I can read Unicode text (which I added myself using the sql server management tool) just fine but writing doesn't w...

Caching Function Results in SQL Server 2000

I want to memoize function results for performance, i.e. lazily populate a cache indexed on the function arguments. The first time I call a function, the cache won't have anything for the input arguments, so it will calculate it and store it before returning it. Subsequent calls just use the cache. However, it seems that SQL Server 20...

What is the best way to check whether a trigger exists in SQL Server?

I'm looking for the most portable method to check for existance of a trigger in MS SQL Server. It needs to work on at least SQL Server 2000, 2005 and preferably 2008. The information does not appear to be in INFORMATION_SCHEMA, but if it is in there somewhere, I would prefer to use it from there. I do know of this method: if exists ( ...

Replacements for Full Text Search

I am not a big fan of Full Text search with MSSQL(not sure if there is another) does anyone know of any other options to this? ...

Performance problems with SQL Server Management Studio

I'm running Sql Server Management Studio 2008 on a decent machine. Even if it is the only thing open with no other connections to the database, anything that has to do with the Database Diagram or simple schema changes in a designer take up to 10 minutes to complete and SQL Management Studio is unresponsive during that time. The same S...

Powershell Sqlcmd popup in c#

I have a PSHost object executing a Powershell script myscript.ps1 and that .ps1 script is executing sqlcmd.exe to get what it needs done. Is there a way to keep it from popping up empty sqlcmd dos prompts (as it seems to do)? ...

SQLServer (2000 & 2005) query for security/role info

I need a query to display information about the users, roles and privileges within an SQLServer 2000 and SQLServer 2005 database? ...

EJB3 case-sensitive annotations

We are connecting to a SQL Server to persist our EJB3 objects. These objects are annotated up with @Column. If the column name in the database starts with a capital letter (E.g. OrderName) will the ejb annotation have case sensitivity issues if the element is defined like such: @Column String orderName Thanks ...

SQL Server: Determining Running Traces

Is there an easy way to determine what traces have been set up by sp_trace_create on SQL Server 2000? ...

Lowercase constraint - Sql Server

I'm not sure if this should be a constraint or not, but I want the "UserName" column of a table to ignore the value that is set when an insert or update is executed and instead, store the value of "DisplayUserName" column converted to lowercase. And if "DisplayUserName" is changed, "UserName" should be updated as well to "DisplayUserName...

Design Question - Put hundreds of Yes/No switches in columns, rows, or other?

We are porting an old application that used a hierarchical database to a relational web app, and are trying to figure out the best way to port configuration switches (Y/N values). Our old system had 256 distinct switches (per client) that were each stored as a bit in one of 8 32-bit data fields. Each client would typically have ~100 ...

SQL: How to SELECT tablename.*

Hi, I tried doing this but it failed. SELECT table2.ID, table1.* FROM table2 LEFT JOIN table1 ON table1.ID = table2.table1ID How do you select all columns from a table? EDIT: There is no error in the above query. I don't know what caused the error but the code is now working. ...

MSSQL Server - get a whole part of a decimal value in the computed column

Here's my simplified table (SQL Server 2005): table1: col1 int, col2 int, col3 cast(col1/col2 as int) [computed column] for some reason the above doesn't work. i just want to save a WHOLE part of col1/col2, how do i do that? example: col1 = 5, col2 = 3 ... col3 should be 1 ...

SQL Query - Search across multiple fields

Hi everyone, I'm trying to implement a search, where you can enter more than one searchterm to form an AND-Condition. Also it should search in different fields of the database. So for instance: You when you enter "Bill Seattle", you should get a record where NAME matches Bill and CITY matches Seattle. You shoudn't get any rows where ju...

Constraint for only one record marked as default.

How could I set a constraint on a table so that only one of the records has its isDefault bit field set to 1? The constraint is not table scope, but one default per set of rows, specified by a FormID. ...

Count and grouped by in t-sql

I have a table that looks something like this CREATE TABLE MyTable( [RecordID] [bigint] IDENTITY(1,1) NOT NULL, [PortName] [nvarchar](50) NULL, [ReceivedEvent] [datetime] NULL, [SentEvent] [datetime] NULL, ); The data could then be RecordID | PortName | ReceivedEvent | SentEvent 1 | Port1 | 2009-...

Is there a simple tool for adding users to a SQL Server database?

I'm looking for a simple tool, preferably one that I can redistribute with my application, that non-technical end-users can use to add users (and logins if necessary) to a particular SQL Server (Express) database. Obviously SSME will do the job, but I don't want to require users to install something so heavy-weight. I have a good idea...

Set Identity_insert on - Merge Replication - SQL Server 2005

I have merge replication set up between two databases and am using identity ranges on both. I want to add a specific row to a merged table (setting the identity value to something outside of the identity range) on the publisher. When I try this, I get the following error. The insert failed. It conflicted with an identity range check c...

Please explain the query plan sql server chooses

In this blog post, I need clarification why SQL server would choose a particular type of scan: Let’s assume for simplicities sake that col1 is unique and is ever increasing in value, col2 has 1000 distinct values and there are 10,000,000 rows in the table, and that the clustered index consists of col1, and a nonclustered ...

Select and EXEC in a single statement - SQL server

I have a temp table in a SP. I insert some values into it. I will need to then EXEC a second SP by sending in the values from the temp table. I would rather avoid having to use local variables. DECLARE @tmp TABLE ( Name VARCHAR(200). Code INT ) INSERT INTO @tmp SELECT 'TEST', 100 EXEC MyProc @Name = --Here I send the values fro...