I don't want to use LIKE because the table contains more than 2 million rows. It makes the query to run very slow.

nice question

try this.

DECLARE @xml XML

SELECT @xml = BulkColumn

FROM OPENROWSET

(

BULK 'D:\Test1.dat', SINGLE_BLOB

) X

SELECT

C.value('.', 'nvarchar(50)') as [Message],

C.value('@time', 'datetime') as [Time],

C.value('@type', 'nvarchar(50)') as [Type],

C.value('@code', 'nvarchar(50)') as [Code]

FROM @xml.nodes('/Msg') X(C)

Use Sql Server Profiler and look what statement exactly passed to server to run.

If it wouldn't help, write here your stored procedure and the statement from Sql Server Profiler.

1)

Drop table

Generate the script of structure and date of the other table with Sql Publishing Wizard

Run generated script

2)

Drop table

Generate the script of the table

USE bcp tool to load data (table...

DECLARE @xml XML

SELECT @xml = BulkColumn

FROM OPENROWSET

(

BULK 'D:\Test1.dat', SINGLE_BLOB

...

a2zwd (3/26/2009)

Thanks for the reply.

I have created the full text index on the table.

When I issued a query like

select * from tablename where contains(*,'Lon')

Its not retrieving anything. When I...

If you do not want to return anything if ImageData is empty you can add simple checking "is not null"

SqlCommand command = new SqlCommand(String.Format("select ImageData from ImagesStore where ImageId={0} and...

dbcc checkident(Table_NAME, reseed, 0)

Gail,

thanks a lot.

Artur

PS. nice photo... sitting on the elephant 🙂

Gail,

Thanks for your help.

the IsTemplate = 0 have about 99.3% of rows.

IsActive = 1 have about 85% of rows.

You see, the search query is made dynamically, I posted here only...

d.IsActive always = 1

d.IsTemplate always = 0

d.DocumentTypeId always existing parameter.

I cannot suppose what parameters will be used more frequently. This project is not in production yet. But I have created...

TABLE SCHEME:

CREATE TABLE [dbo].[Documents](

[DocumentId] [numeric](18, 0) IDENTITY(1,1) NOT NULL,

[DocumentTypeId] [numeric](18, 0) NOT NULL,

[SectionId] [numeric](18, 0) NOT NULL,

[TemplateId] [numeric](18, 0) NULL,

[Title] [varchar](200) NULL,

[Description] [varchar](200) NULL,

[ContentSource] [varchar](200) NULL,

[ContentSourcePublicationDate] [datetime] NULL,

[ContentSubject] [varchar](200) NULL,

[ContentPublisher]...

Thanks for reply.

I have some varchar columns and Full text indexing is set for these columns and I use FREETEXT and CONTAINS for searching in these columns. The others are...

I'm getting "Transaction (Process ID 76) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction." too.