Containstable Queries Not Making Sense.

Jan 2, 2008

I have a few questions related to using CONTAINSTABLE in a query that I hope someone can help with.

I am working on a project to add document search capabilities to my companies product using fulltext indexing. Part of this requirement is an ability to breakdown the component parts of of the search query and provide information on *why* documentX ranked higher than documentY.
This is a bit convoluted, but taking this (very simple) example - the user wishes to search for 2 skills - "HTML" and/or "XML".
The generated query looks a little like :-

Select DOC.DOC_ID, RANK1.RANK, RANK2.RANK, RANK3.RANK
from DOCS DOC
inner join CONTAINSTABLE(docs, doc, 'HTML AND XML') as RANK1 on RANK1.DOC_ID=DOC.DOC_ID
inner join CONTAINSTABLE(docs, doc, 'HTML') as RANK2 on RANK2.DOC_ID=DOC.DOC_ID
inner join CONTAINSTABLE(docs, doc, 'XML') as RANK3 on RANK3.DOC_ID=DOC.DOC_ID

This returns the "overall" rank, and a rank for the 2 component parts, so I can say this doc ranked XXX overall because it scored "rank1" for HTML and scored "rank2" for XML etc....

My question on this part is about the values for the "overall rank". If the query contained an OR it always seems to return the highest of the "rankX" values, and if it doesnt, it returns the lowest.
e.g. for the example
for java and word and excel and access - the overall ranking is 2 , java=36, word=2, excel=16 and access=36
for java and word or excel and access - the overall ranking is 16 , java=36, word=2, excel=16 and access=36
for (java and word) or (excel and access) - the overall ranking is 16 , java=36, word=2, excel=16 and access=36

So in the first example, regardless of what the other values are, the rank returned is always 2 (the score for "word"). My resultset has 100ish rows, all with a rank of < 5 for word, but all with ranks of 18-100 for the other 3 values - yet the "overall" rank always matched the "word" rank.....??
This doesnt feel right to me somehow, I would expect a different value as if the document ranked really highly for one value but low for the other, it doesnt feel right the value is clamped to the lowest? Or am I just understanding it wrong?
If I use "freetexttable" the overall rank is a little more meaningful - but unfortunately I also need to use weighting, which brings me to my next question . . .


This question is about rankings returned from the ISABOUT function.
In the following example,
select * from documents as DOC
inner join containstable(docs,doc,'project') as doc0 on DOC.DOC_ID=doc0."key"
inner join containstable(docs,doc,'ISABOUT (project weight (1.0))') as doc1 on DOC.DOC_ID=doc1."key"
inner join containstable(docs,doc,'ISABOUT (project weight (0.5))') as doc2 on DOC.DOC_ID=doc2."key"
inner join containstable(docs,doc,'ISABOUT (project weight (0.1))') as doc3 on DOC.DOC_ID=doc3."key"
inner join containstable(docs,doc,'ISABOUT (project weight (0.0))') as doc4 on DOC.DOC_ID=doc4."key"
order by doc0.rank desc

The values I get from the doc1/2/3/4.RANK columns dont seem right.
In this example,

doc0.rank = 133
doc1.rank = 150
doc2.rank = 330

doc3.rank = 924

doc4.rank = 0

These values dont make any sense to me, as the rank seems to go UP when the documentation on ISABOUT says it goes down (I think it says somewhere the calculated rank is multiplied by the weight?).
Once again, is there something I missed or am I understanding it wrong?

Thanks in advance for any help into understanding the whys of this...

View 1 Replies


ADVERTISEMENT

Run Times Of SSIS Package Not Making Sense....HELP!

Feb 27, 2007



We have an SSIS package that was created to migrate data in from a few production databases. The steps for the package are as follows...

backup databases on server 1 (prod database server) restore database to SSIS server (server 2) .
truncate worker tables in SSIS server's (server 2) Main DB database.
copy data from restored db tables to working db tables ( database to database)
Start Multiple threads (15 ) and run steps from here in parrallel
Combination of Data flow tasks and SQL scripts and Stored procedures used to flatten data out and combine data for reporting purposes.

The average run time is 8 hours.

the issue we are seeing is this, the package will fluctuate in run times from 4 hours to over 11 with no change in the data or the underlying SSIS package. We have looked for any changes or things that would effect this but have not found anything that changed...

Also, certain steps are running shorter while others double in time. there doesnt seem to be any rhyme or reason to this behaviour. The server is x64 12GB of RAM 2 dual core 3.2Ghz.

Please let me know if you need any more information or specifics...

the only thing I have seen so far that looks out of place is Tempdb has one of its files that is 20+GB.

Thanks,

Chris

View 4 Replies View Related

Help I M New To SQL And Weak At Making Queries

Apr 30, 2007

plzzzzzzzzzzzzzzzzzzzzz !!!!!!!

help i m new to SQL and weak at making queries


can u please suggest me some books or site from
where i can pratice making queries


u can mail me at
rastogi.akash@gmail.com

View 1 Replies View Related

Making Multiple Queries

Feb 26, 2008

Hi,

I have some questions about making muliple T-SQL queries againt an SQL-server. I retrieve all rows from an SQL-server table called ActivityGroup and add the result to a Radio Button List. Suppose, I also wish to add the total sum of every "ActivityGroup" value to the Radio Button List. I guess this ought to be done using a SELECT COUNT statement and then retrieved using the Executescalar method?

The question is do I need to copy the code for the SELECT ('*) statement and make the neccessary changes for the SELECT COUNT statement and Executescalar method or is there a more simple way? Is it possible to use two command statements in the same code block?

The total sum is going to be calculated using the values from both an SQL-server and an Access database. The OleDb sample below is what I refer to as a code block. I appreciate any help!



string connectionString = ConfigurationManager.ConnectionStrings["ServetteConnectionString"].ConnectionString;

string sQuery = "SELECT * FROM Aktivitetsgrupper where aktivitetsid = " + Request["AktivitetsId"];



OleDbConnection oOleDbConnection = new OleDbConnection(connectionString);

oOleDbConnection.Open();

OleDbCommand command = new OleDbCommand(sQuery, oOleDbConnection);

OleDbDataReader reader = command.ExecuteReader();

int test = 12;

while (reader.Read())

{



rblAktivitetsgrupper.Items.Add(new ListItem(String.Format("{0}, platser", reader["aktivitetsGruppNamn"]), reader["aktivitetsGruppID"].ToString()));



}

View 4 Replies View Related

Making A View That Shows The Results Of Several Different Queries.

Dec 21, 2005

Hello,I am trying to create a view that shows the followingField1: Sum of Amounts from Table AField2: Count of Amounts from Table AField3: Sum of of Amounts from Table BField4: Count of Amounts from Table B......Field3: Sum of of Amounts from Table HField4: Count of Amounts from Table H......Things are a bit more complex but this is the gist.I am using SQL 2000.I know how to do this pretty easily using a stored procedure. But howcan I do it in a view? A SQL server won't meet my needs in thissituation.I tried OpenQuery ('myserver', 'exec myprocedure') but get the messagethat my server is not configured for data access. I tried the systemstored procedure to set data access to true but nothing seemed tohappen.I also tried Select * from (Select Statement1, select statement2)but got syntax error at the comma between statement1 and statement2.Trying to use select Statement1 as ABC to does not seem to work either.Is there a way to do what I want without making 15 views and then afinal view that shows them all together? I know I could probably dosomething by creating a ton of functions, but it really seems thisshould not be that hard...I am definitely open to any easy suggestions!Thanks,Ryan

View 3 Replies View Related

SQL Server 2014 :: Making Dashboard In Excel From Queries?

Jul 20, 2015

Currently i have set of queries which i run for data extraction and result pasted in Excel table so that my pivot table and Chart gets populated along with the summary in Excel file.

I would like to automate this thing and want to know if i can achieve excel output for dashboard on a click of button.

View 2 Replies View Related

Does This Make Any Sense?

Mar 18, 2008

I have inherited a SQL 2005 server with a few small databases on it. There's a maintenance plan here that doesn't seem to make a lot of sense to me. Can anyone comment:

Every Sunday at 4:00 AM


1. Reorganize index on All user database Tables and Views - compact large objects.
2. Rebuild index on local server connection, All user databases, Tables and view, Original amount of free space.
3. Shrink database. All user databases. Limit 100MB.

I'm confused a little about item 3. Won't a shrink be kind of useless after all of the work that goes on in steps 1 and 2. When I ran this manually, the transaction logs jumped significantly.

Thanks in advance for your help.

Dean

View 5 Replies View Related

Help: UNION Vs. CONTAINSTABLE

Jul 8, 2007

I have n Tables: T_1, T_2, ... T_n that all have the same exact fields/columns.
Ultimately, I want to search through ALL n tables and return a single table of relevant results from all tables, arranged in order by rank.
I'm not sure if this is possible. So far, I have:
 1 SELECT RANK, field_1, field_2, ..., field_m FROM
2 (
3 SELECT RANK, field_1, field_2, ..., field_m FROM T_1,
4 CONTAINSTABLE(T_1, field_i,@searchText) searchTable
5 WHERE KEY = T_1.field_i
6 UNION
7 SELECT RANK, field_1, field_2, ..., field_m FROM T_2,
8 CONTAINSTABLE(T_2, field_i,@searchText) searchTable
9 WHERE KEY = T_2.field_i
10 UNION
11 .
12 .
13 .
14 UNION
15 SELECT RANK, field_1, field_2, ... field_m FROM T_n,
16 CONTAINSTABLE(T_n, field_i,@searchText) searchTable
17 WHERE KEY = T_n.field_i
18 )
19 ORDER BY RANK DESC

 I haven't tried it yet, but it seems wrong. How do we actually do this?

View 3 Replies View Related

LINQ To SQL Using CONTAINS Or CONTAINSTABLE Possible?

May 12, 2008

I would like to use LINQ to generate a sql statement that does not use LIKE, but rather uses CONTAINS.  Is this possible?  If not, my second question is whether or not I can parameterize a SqlCommand that uses CONTAINS.  For example the following statement works just fine when I pass in the parameter via SqlCommand.Parameters.AddWithValue()SELECT * FROM [event] WHERE CONTAINS(comments, @searchTerm1)However, the following results in a variable not defined error.SELECT * FROM [event] WHERE (comments LIKE @searchTerm1)Any ideas?  Thanks for your help. 

View 1 Replies View Related

Search: CONTAINSTABLE

Jun 19, 2007

I'm trying to write a query to associate to an alternate key. Cansomeone provide the right syntax/keyword I need to accomplish this?Here's the line that keeps giving me the error (Operand type clash:uniqueidentifier is incompatible with int):JOIN CONTAINSTABLE(VocabularyFrench, *, 'FORMSOF(INFLECTIONAL,"remorques")') SRCH ON P.PRODUCT_ID = SRCH.[KEY]Thanks,Jeff

View 1 Replies View Related

Last 100 Recs!!! Does This Make Sense

Feb 20, 2002

l've written a cursor to koop through a table and then insert the last 100 records into a table.Reason why l want the last 100 records is to monitor and log the last 100 trans avery hr or so.

-- Declare the variables to store the values returned by FETCH.
SET ROWCOUNT 100
DECLARE
@customer_No char(15),
@loan_No char(12),
@date_Issued datetime ,
@maturity_Date datetime ,
@status int

DECLARE loan_cursor CURSOR FOR
SELECT customer_No,
loan_No,
date_Issued,
maturity_Date,
status

FROM loan

OPEN loan_cursor

-- Perform the first fetch and store the values in variables.

FETCH NEXT FROM loan_cursor
INTO @customer_No,
@loan_No,
@date_Issued ,
@maturity_Date,
@status,

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.

WHILE @@FETCH_STATUS = 0
BEGIN

-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM loan_cursor
INTO @customer_No ,
@loan_No ,
@date_Issued ,
@maturity_Date ,
@status
END

CLOSE loan_cursor

DEALLOCATE loan_cursor;

insert into Loan1
(customer_No,
loan_No,
date_Issued ,
maturity_Date ,
status
)

select @customer_No,
@loan_No,
@date_Issued,
@maturity_Date,
@status
FROM loan
ORDER BY date_Issued desc;

View 3 Replies View Related

Help.... This Doesn't Make Sense...

Jun 23, 2006

I need any one's advice/imput on this...PLEASE!My computer will now begin the process of taking all the MS Access (NativeJet Engines - x30 total departmetns) and put the tbles/BE on SQL Server 2005and the Ms Access FE on MS Sharepoint.This is the kicker, say 20 out of the 30 (ball park) was created by oneperson and that is their whole job function was to create/maintain a QAtracking system and more.The person who created the 20 out 30 only knows intermediate ms access andsome vba, but NOT sql or net conversions (visual studio - all the differentlanguages), so the IT director asks me (I develop in MS Access andintermediate in VBA and can create web sites using publisher, front page andHTML) he asks me and this other person if we want to take on the challenge ofhelping him and the other IT guy in the conversion process of all of thesedb's.What does this do the developers who developed and still maintain thesecurrent 30 ms access db's, well you guessed it, it now takes all that hardwork that those developers did and still do (they still add more forms,updates) and it NOW takes the databases owners away from them and grant itnow to the person (s) who will maintain SQL Server 2005 ( I hope will be aDBA)???Is this true, once all the databases are converted, the owners will no longerbe able to go behind the scenes in tables, queries,etc.... It will now be inthe hands of a DBA?You know the funny thing is the IT Director wasn't even sure if he was goingto hire a DBA, who in the heck will maintain all of those db's on the server?There is only one other guy and he certainly does not have the training orskills or TIME.MY POINT QUESTION IS:when these conversion take place like this at a company, most of the time thems access dbs that have now be put into sql will now take the ownership awayfrom the owner (they cannot develop no more, unless they are sql friendly/dba)and put all of that into one persons hand (DBA) to maintain and development??????--Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forum...eneral/200606/1

View 4 Replies View Related

Does It Make Sense To Try To Configure This?

Apr 14, 2008

Thanks to several guys here, I now understand how SQL Server configuration option works... Pretty nifty stuff.

Now, I'm trying to see if I can configure the Server property of the Connection Manager that holds the information for where my configuration table is. I thought about this and tried it, but it doesn't work. Then it occurred to me, this may not make sense to try to do because it is like the question, "what came first? the egg or the chicken?"

Am I making sense?

View 5 Replies View Related

Combining Containstable And Freetexttable

Jun 9, 2007

i use two containstable (one of them with 'formsof') and one freetexttable attributes in a select command and i want to combine them with the logical 'or'.can i do this in the same command? what is the syntax?my code :select table1.field1,a.rank,b.rank,c.rankfrom table1containstable(table1,field,'"word"') as aor containstable(table1,field,'formsof(inflectional(word)') as b)or freetexttable(table1,field,'word') as cwhere table1.id=a.[key] and table1.id=b.[key] and table1.id=c.[key]the above syntax is wrong. i tried ',' instead of 'or' but the results were not right. thanks  

View 1 Replies View Related

Help With CONTAINSTABLE Fulltext Query

Mar 6, 2008

 Hi,I'm doing a search function for recipe database and have the query:1 SELECT K.RANK, tRecipe.sHeadline, tRecipe.sIngredients, tRecipe.sImagePath
2 FROM tRecipe
3 INNER JOIN
4 FaktaRecipe ON tRecipe.iRecipeID = FaktaRecipe.iRecipe
5 INNER JOIN
6 CONTAINSTABLE(tRecipe, *, 'ISABOUT (chick* WEIGHT(0.2))') AS K
7 ON tRecipe.iRecipeID = K.[KEY]
8 WHERE (FaktaRecipe.iRecipeFakta = 5)
9 ORDER BY RANK DESC I want to return records like 'chicken pie' etc, hence using the wildcard in chick* BUT the wildcard doesn't work! It works fine if I use the whole word 'chicken' but of course a user won't always do that... I am using SQL server 2000. Any ideas? - I'm tearing my hair out!  Thanks,Paul   

View 3 Replies View Related

Is There A Way To Have List Of NOT Words With Containstable?

Nov 6, 2007

This works when @searchString is used in containstable (provided searchString has value)...

set @searchStringNoneOfWords = 'not(Airplane)'
SET @searchString = @searchString + ' AND ' + @searchStringNoneOfWords

This does NOT work when @searchString is used in containstable...

set @searchStringNoneOfWords = 'not(Airplane)'
SET @searchString = @searchStringNoneOfWords

I understand it is because the syntax is AND NOT, but what if I have a list of words that I do not want included? How do I start out with a NOT using containstable? It is kind of like Google's advanced search except that if you enter a word in the "without words" section with the other fields blank it would return everything under the sun except for things found with those words.

View 1 Replies View Related

Issue With CONTAINSTABLE Statement

Jan 18, 2007

I am using the following query to search all columns in the 'dashboard'table for the value 'Meets':SELECT * from dashboard AS FT_TBL INNER JOIN CONTAINSTABLE(dashboard,*,'meets') AS KEY_TBL ON FT_TBL.employee = KEY_TBL.[KEY]I have multiple records that contain the word 'Meets', but none areshowing up as a result of this query. Any ideas?Also, anytime I use a space in my search condition (Meets Expectationsinstead of Meets) I am getting an error:Syntax error occurred near 'Expectations'. Expected ''''' in searchcondition 'Meets Expectations'.Any ideas?Thanks in advance.

View 1 Replies View Related

Containstable Variable Usage

Aug 24, 2006

I have a stored procedure that uses containstable and want to make it a little dynamic so I was going to add a parameter that consist of the column names that needed to be search. But when I add a variable I get an error saying incorrect syntax....

Can you not use a variable as a column list?? I have a variable for search criteria and it works fine...

Here is my syntax

containstable([tablename],@columnlist,@srch)

I have been looking online and can't seem to find anything that says I can or cannot use a variable.

View 1 Replies View Related

Difference Between FREETEXTTABLE And CONTAINSTABLE

Apr 8, 2008

Can somebody help me in identifying the difference between FREETEXTABLE and CONTAINSTABLE statements?


I have recently started experimenting with the free text search and I feel that FREETEXTTABLE can look for the search string into multiple columns by breaking it into tokens.

For example

select ft_tbl.saon, ft_tbl.paon, ft_tbl.street, ft_tbl.postcode
,key_tbl.rank
from temp as ft_tbl
INNER JOIN freetextTABLE(temp, (saon, paon, street), '80 ridge avenue', 15) as key_tbl
ON FT_tbl.ID = key_tbl.[key]

Now in my case €œ80€? is in PAON column and €œRidge Avenue€? is in STREET column. Among the resulting rows it also displays the required row. If I try to achieve this with CONTAINSTABLE using

select ft_tbl.saon, ft_tbl.paon, ft_tbl.street, ft_tbl.postcode
,key_tbl.rank
from temp as ft_tbl
INNER JOIN containsTABLE(temp, (saon, paon, street, postcode), '80 ridge avenue') as key_tbl
ON FT_tbl.ID = key_tbl.[key]

It gives me error
Syntax error near 'ridge' in the full-text search condition '80 ridge avenue'.

If I make search string as €˜€?80 ridge avenue€?€™ instead of €˜ridge avenue€™ then it doesn€™t give me error but also displays no results because 80 ridge avenue does not appear as whole in any single column. If I search by only specifying €œridge avenue€? in search string then I get a full list where street is ridge avenue.

My question is Does FREETEXTTABLE can search in all listed column by breaking the search string into tokens and CONTAINSTABLE only in one as whole?


View 3 Replies View Related

SQL Server SP And SSMSE Intelli Sense

Apr 5, 2008

How can I know my sql server 2005 express service pack? The same goes for SSMSE. I can't find these information in the about box.

What about intelli sense for queries in SSMSE? Is it there yet or should I wait for sql server 2008 for dev. (http://channel9.msdn.com/Showpost.aspx?postid=387069)? I haven't seen the video but does anyone know any details? When it will be available, is it part of the sql server 2008 CTP, will it be available in the Express edition?


Thanks in advance,
Shehab.

View 1 Replies View Related

Error. Doesnt Make Sense. HELP!

Jun 8, 2007

I am new to SSIS. i am trying to port database from SQL SERVER 2000 to 2005. i am using "Transfer SQL Server Objects" for this. i am just trying to move one object for testing wether it works or not. and it is not working. i am getting this error.



[Transfer SQL Server Objects Task] Error: Execution failed with the following error: "ERROR : errorCode=-1071636471 description=An OLE DB error has occurred. Error code: 0x80040E37. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E37 Description: "Invalid object name 'dbo.consta_AE'.". helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}".







Both DBs are on seprate machines. of you need any more info please let me know. that would be great help.



Thanks

View 8 Replies View Related

CONTAINSTABLE: Query Multiple Columns With AND

Oct 9, 2003

Is there a simple and direct way to perform a fulltext query in a table with multiple columns, and to use AND over multiple columns? I've noticed that AND only works within one column.

Example: Take a column 'lastname' with 'jones' and 'smith' in it. Another column 'firstname' with 'alan' where lastname=smith. Search for 'alan and smith', and no results are returned because they are in different columns. If you would have a lastname 'alan smith', that would be found.

View 2 Replies View Related

Containstable Not Returning Accurate Results

Aug 27, 2007



Does anyone lese get the same results that I do?

tblTest has an id field and a testName field....table has 2 records:
1 kevin test 1
2 kevin test 3


SELECT *

FROM tblTest t

INNER JOIN CONTAINSTABLE(tblTest,*,'"kevin test 1"') as A ON t.id = A.[KEY]

Result is both records returned, but since the search is in quotes as an exact phrase, should not just the first record be returned?

View 3 Replies View Related

Search Full Text CONTAINSTABLE

Feb 25, 2008



Hi To all...
This is my question....

I use full text search with the CONTAINSTABLE Sintax for read the rank......
in this way:

Select * from CONTAINSTABLE(MyTable,*,'myWord')

and this work perfectly.....

Now I want to know, the name of the field where the full text found my string......

is possible? Is there a way to know this?

Tnx for reply

Franz.

View 1 Replies View Related

Is There A Way To Have List Of NOT Words With Containstable (especially Begining With NOT)?

Nov 6, 2007


This works when @searchString is used in containstable (provided searchString has value)...

set @searchStringNoneOfWords = 'not(Airplane)'
SET @searchString = @searchString + ' AND ' + @searchStringNoneOfWords

This does NOT work when @searchString is used in containstable...

set @searchStringNoneOfWords = 'not(Airplane)'
SET @searchString = @searchStringNoneOfWords

I understand it is because the syntax is AND NOT, but what if I have a list of words that I do not want included? How do I start out with a NOT using containstable? It is kind of like Google's advanced search except that if you enter a word in the "without words" section with the other fields blank it would return everything under the sun except for things found with those words.

View 2 Replies View Related

Developing DW Project On SQL 2008 - Does It Make Sense?

Feb 5, 2008

Dear all,

We started to develop a datawarehouse solution for client back in December 2008 based on SQL 2008.
We are convinced that we can use some of the new feature included in the new version which is the reason we we chossed to go down this path.

Due to the delay of the next version we have some question that I would like to hear you opinion on.

The estimatet "go live" date is during spring (march/april)

Is it wrong to contiue the development on the 2008 version?

We would need to run Release candidate at the customer site until the product is released. Is there any major changes coming up that are already known?

So far in the development we have had no big problems with releasecandidate.

Thank you for you feedback.

View 4 Replies View Related

Size Of Database Ndoesn't Make Sense

Sep 21, 2007



I have a weird problem.

We are importing a very small subset of a big desktop database into a CE database on a mobile device for an occasionally connected application. The idea is that the mobile device can use this CE database as a fall back database in case we are not connected.

The database is a very simple list of barcodes.

Basically a single field as primary key

EAN13 bigint

When we import 200K rows (yes we have quite a lot of them). The database is 7MB!!!! A bit big I would say since 8 bytes times 200.000 is only 1.5 MB. Where does the extra space come from?

Yes I did compact the database after the import

View 1 Replies View Related

Irritating ADODB Error... Makes No Sense To Me

Aug 22, 2007

Hi Guys,

I've been programming with SQL 7 for about a year and my company has finally decided to go SQL 2k5.

I've come accross a really irritating error when writing to the DB via ADO in ASP pages. I have a column in a table that is auto-incremental.

In SQL server 7 you just make an ADODB.Command object and enter the SQL query 'insert into table (columns) values ('val...') now for SQL 7 I can completely leave out the auto-incremental column (called 'ErrorNo') and simply specify the other columns and values in my insert query.
e.g. where my table is called master_error:

ErrorNo int identity (1, 1) not null
,ReportedBy char(10)
,ExpectedFixDate datetime


With ErrorNo being an auto-incremental identity, my query would be

INSERT INTO master_error (ReportedBy, ExpectedFixDate) VALUES ('Ben','01 Sep 2007')

this works perfectly with ADODB.Command when writing to SQL Server 7 from IIS 5.0

however when I execute the exact same command on the exact same table using ADODB.Command writing to SQL Server 2005 from IIS 6.0 I get an 'error 500 internal server error'

IIS log states:
|166|80040e14|[Microsoft][ODBC_SQL_Server_Driver][SQL_Server]Cannot_insert_the_value_NULL_into_column_'ErrorNo'__table_'ReACToR_V1.0.dbo.master_error';_column_does_not_allow_nulls._INSERT_fails. 80 - 192.168.78.159 Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.1;+.NET+CLR+1.1.4322;+.NET+CLR+2.0.50727;+.NET+CLR+3.0.04506.30) 500 0 0

I thought perhaps SQL 2005 might have different syntax so I typed the query directly into SQL Server 2005's version of query analyser and guess what... it worked fine.

I can't tell where the error lies. I find it hard to beleive that the error is in the code of my ASP page as it works perfectly against a sql 7 db.

Any ideas?

Many Thanks,
Ben Ward

View 8 Replies View Related

Overflow Error That Doesn't Make Sense....

May 3, 2006

I'm troubleshooting a stored procedure that suddenly decided to stop working. I narrowed down the problem to the last part of the stored procedure where it selects data from a temp table and inserts it into a physical table in the SQL2000 database.

I keep receiving the following error:

Server: Msg 8115, Level 16, State 8, Line 140
Arithmetic overflow error converting numeric to data type numeric.

The data values all appear to be correct with none of them seeming to be out of precision, but I keep getting the error. I've tried casting all the values and it didn't work. It executes w/o error when I comment out that particular insert. I just don't get it.

Any help would be appreciated. Thanks.

Code below:
-------------------------------------------------------------

declare @dtAsOfdate DATETIME
set @dtAsOfDate = '2006-04-16';

DECLARE @RC INTEGER
-------------------------
-- 1) Eligible Investments:
-------------------------

-- Input: @SPVId - SPV we are running process for
-- @Yes - value of enum CCPEnum::eYesNoYes (get by lookup).

-- Output: Recordset (temp table) of Collaterals that are eligible for MV Test (#MVTriggerInvestments).

DECLARE @Yes INTEGER
EXEC @RC = [dbo].CPLookupVal 'YesNo', 'Yes', @Yes OUTPUT
IF (@RC<>0)BEGIN
RAISERROR ('SP_OCCalculationMVTriggerTest: Failed to find Yes enum', 16, 1) WITH SETERROR
END
drop table #MVTriggerInvestments
BEGIN

SELECT dbal.SPVId,
dbal.CusipId,
dbal.GroupId,
@dtAsOfDate AS AsOfDate,
dbal.NormalOCRate,
dbal.SteppedUpOCRate,
dbal.AllocMarketValue AS MarketValue,
dbal.NbrDays,
dbal.PriceChangeRatio

INTO #MVTriggerInvestments

FROM DailyCollateralBalance dbal

JOIN CollateralGroupIncludeInOC gin
ON dbal.SPVId = 2
AND gin.SPVId = 2
AND dbal.AsOfDate = '2006-04-16'
AND @dtAsOfDate BETWEEN gin.EffectiveFrom AND gin.EffectiveTo
AND dbal.GroupId = gin.GroupId
AND gin.IncludeInOC = @Yes

END
select * from #MVTriggerInvestments
print 'end #1'
--select * from #MVTriggerInvestments --looks ok

--------------------------------------------------------------
-- 2) Calculate Weighted Average Price change ratio Market Value (by Group):
-- PCRMV - Price Change Ratio Market Value
--------------------------------------------------------------

-- Input : Recordset of collaterals (having New/Old prices, MarketValue defined)
-- Output: Recordset Aggregated by Group (#GroupOCRate)
drop table #MVTriggerGroup
BEGIN

SELECT A.SPVId,
A.AsOfDate,
A.GroupId,
A.NormalOCRate,
A.SteppedUpOCRate,
A.MarketValue,

cast([dbo].fn_divide_or_number (B.PriceChangeRatioMarketValue, B.MarketValueForPeriod, 0.00) as numeric(12,9)) as PriceChangeRatio,

CAST (0 AS NUMERIC(12,9)) AS OCRate,
CAST ('' AS VARCHAR(6)) AS OCRateType,
CAST (0 AS NUMERIC(18,2)) AS DiscMarketValue,
CAST (0 AS NUMERIC(18,2)) AS InterestAccrued

INTO #MVTriggerGroup

FROM
(
SELECT SPVId,
AsOfDate,
GroupId,
NormalOCRate,
SteppedUpOCRate,
cast(SUM(MarketValue) as numeric(18,2)) AS MarketValue

FROM #MVTriggerInvestments
GROUP BY SPVId, AsOfDate, GroupId, NormalOCRate, SteppedUpOCRate
) A --works up to here

JOIN
(SELECT SPVId,
cast(SUM(AllocMarketValue) as numeric(18,2)) AS MarketValueForPeriod ,
cast(SUM(AllocMarketValue * PriceChangeRatio) as numeric(18,2)) as PriceChangeRatioMarketValue,
GroupId

FROM T_DailyCollateralBalance
WHERE SPVId = 2
AND AsOfDate between '2006-03-17' and '2006-04-15'
AND IsBusinessDay = 1
GROUP BY SPVId, GroupId
) B

ON A.SPVId = B.SPVId
AND A.GroupId = B.GroupId

END
print 'end #2'
---------------------------------------------
-- Calculate OCRate to apply for each group.
---------------------------------------------
BEGIN
UPDATE #MVTriggerGroup
SET OCRate = (CASE WHEN ((PriceChangeRatio < 0) AND ABS(PriceChangeRatio) > (0.55 * NormalOCRate)) THEN SteppedUpOCRate
ELSE NormalOCRate
END),
OCRateType = (CASE WHEN ((PriceChangeRatio < 0) AND ABS(PriceChangeRatio) > (0.55 * NormalOCRate)) THEN 'stepup'
ELSE 'normal'
END)
END
print 'end #3'
-------------------------------------
-- Calculate discounted Market Value
-------------------------------------
UPDATE #MVTriggerGroup
SET DiscMarketValue = MarketValue / (1.0 + OCRate * 0.01)
print 'end #4'
---------------------------------
-- Insert data from temp tables
---------------------------------
-- 1)
select * from #MVTriggerInvestments

print 'begin tran'
BEGIN TRAN
DELETE T_MVTriggerInvestments
WHERE SPVId = 2 AND AsOfDate = '2006-04-16'
print 'DELETE T_MVTriggerInvestments'
--error is here!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
INSERT T_MVTriggerInvestments
(
SPVId ,
CusipId ,
GroupId ,
AsOfDate ,
NormalOCRate ,
SteppedUpOCRate ,
MarketValue ,
NbrDays ,
OldPrice ,
NewPrice ,
PriceChangeRatio
)
SELECT SPVId ,
CusipId ,
GroupId ,
AsOfDate ,
NormalOCRate ,
SteppedUpOCRate ,
MarketValue ,
NbrDays ,
0.00 ,
0.00 ,
PriceChangeRatio

FROM #MVTriggerInvestments
print 'end mvtriggerinv select'
COMMIT TRAN
--end error!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
-- 2)
print 'begin tran 2'
BEGIN TRAN
DELETE T_OCTestGroup
WHERE SPVId = 2 AND AsOfDate = '2006-04-16'

INSERT T_OCTestGroup
(
SPVId ,
AsOfDate ,
GroupId ,
NormalOCRate ,
SteppedUpOCRate ,
MarketValue ,
PriceChangeRatio,
OCRate ,
OCRateType ,
DiscMarketValue ,
InterestAccrued ,
SectionA ,
CPFace ,
IntExpense ,
Fees ,
SectionB ,
Receivables ,
IntReceivables ,
CashBalance ,
Investments ,
SectionC ,
ExcessCollateral,
MaxCPAllowed
)
SELECT
SPVId ,
AsOfDate ,
GroupId ,
NormalOCRate ,
SteppedUpOCRate ,
MarketValue ,
PriceChangeRatio,
OCRate ,
OCRateType ,
DiscMarketValue ,
InterestAccrued ,
0 ,
0 ,
0 ,
0 ,
0 ,
0 ,
0 ,
0 ,
0 ,
0 ,
0 ,
0

FROM #MVTriggerGroup
print 'end tran 2'
COMMIT TRAN

View 4 Replies View Related

ContainsTable Function Searching Only One Column Per Record

Oct 25, 2007

Hello all,

I am using the ContainsTable function to search a database from my (c#) app. This works relatively well and all fields of the table are indexed and searched. That is, any column, but per record only one column.
What I mean is this: when searching for "chris 2007", I want to retrieve all items where author contains chris and year contains 2007. Currently, a search for chris brings up all items where author (or any other field) contains chris, a search for 2007 works as well, but chris 2007 fails as there is no -one- field where chris and 2007 are located.
Can anybody help me achieve this? My code is:




Code Block
SELECT FT_TBL.ID, FT_TBL.Type, FT_TBL.Author, IsNull(FT_TBL.Author, FT_TBL.Editor + ' (Ed.)') AS CorrectedAuthor, FT_TBL.Editor, FT_TBL.Title, FT_TBL.Abstract, FT_TBL.Comments, FT_TBL.Year, FT_TBL.City, FT_TBL.Publisher, FT_TBL.ISBN, FT_TBL.Pages, FT_TBL.Journal, FT_TBL.Issue, FT_TBL.Hyperlink, FT_TBL.Tags, KEY_TBL.RANK
FROM Sources AS FT_TBL
INNER JOIN CONTAINSTABLE(Sources, *, @searchQuery) AS KEY_TBL ON FT_TBL.ID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC;



What am I doing wrong?

Thanks in advance,

Chris

View 1 Replies View Related

Why Does A Timeout Make Sense? (Reading From DTS Buffer Timed Out)

Aug 28, 2007

I've looked at the other threads on this topic, and don't see an answer to the following question:

Why should an error destination time out waiting for error rows?

I'm using SQL Server Destinations both for my staging tables and for my "Error Staging" tables. Yet it seems that these are timing out if the package runs a long time without any error rows. This leads to two questiosn:



Why should an error destination time out waiting for error rows?

I can solve this by setting the timeouts to some very large number. But, is there a better way to do this?
Right now, if the package takes five minutes, I need to set the timeouts to longer than five minutes. That does not sound like a good idea.

View 7 Replies View Related

Full Text Search And CONTAINSTABLE - Can I Weight By Column?

Aug 8, 2006

I'm trying to use CONTAINSTABLE to do a product search on a number of columns. I want matches on some columns to be weighted higher than for other columns.

It seems like there should be a way to do this with CONTAINSTABLE - I know I can search by multiple strings and weight those matches variably, but what I want is to vary the weight by which column a single term matched. What I have now is a series of UNION queries, which are quite slow:
===================
SELECT m.TitleCode, m.ShortName, m.ShortDescription, a.Rank, 50 as theWeight
FROM CONTAINSTABLE (Product, ShortName, @theSearchTerm) as a, Product m(NoLock)
WHERE a.[KEY] = m.TitleCode

UNION ALL

SELECT m.TitleCode, m.ShortName, m.ShortDescription, a.Rank, 40 as theWeight
FROM FREETEXTTABLE (Product, ShortName, @theSearchTerm) as a, Product m(NoLock)
WHERE a.[KEY] = m.TitleCode

UNION ALL

SELECT m.TitleCode, m.ShortName, m.ShortDescription, a.Rank, 30 as theWeight
FROM FREETEXTTABLE (Product, MatchKeywords, @theSearchTerm) as a, Product m(NoLock)
WHERE a.[KEY] = m.TitleCode

UNION ALL

SELECT m.TitleCode, m.ShortName, m.ShortDescription, a.Rank, 20 as theWeight
FROM FREETEXTTABLE (Product, ShortDescription, @theSearchTerm) as a, Product m(NoLock)
WHERE a.[KEY] = m.TitleCode

ORDER BY 'theWeight' DESC, m.ShortName

===================

What is the better way?

View 1 Replies View Related

FULLTEXT CONTAINSTABLE ISABOUT WEIGHT ?? SYNTAX Ranking Search

Mar 25, 2008



Sorry, is this possible?


select * from COLLABORATOR x inner join

containstable

(CollaborAtor,*,

' ISABOUT (

"hiv and Gender" weight (.8)

or

"hiv or Gender" weight (.2)

)

'

)

as KEY_TBL

on x.ColId = KEY_TBL.[KEY]



==

basically I want all rows with either string, but I want those with both to rank first.

Also, how can I deal with singular vs plurals of words..

Thanks.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved