The code below is used to calculate the miles between two cities. In this case, it's for the distance from Yarmouth, ME to Yarmouth, ME - obviously zero - meaning that results for cities within X miles of Yarmouth should include Yarmouth itself. The problem is that the latitude and longitude for Yarmouth seem to be causing some kind of ...
Table A has columns question varchar(200) objective varchar(200) Table B has columns + a fulltext index solution varchar(max) I am trying to output a flat merge of these matching a.objective with the TOP 5 solutions from Table B for the keywords in A.objective. where freetext(text,A.objective) e.g. "question one", "objective one",...
1. Name---ID--- SEQ 2. *---------101--1 3. *---------101--2 4. *---------101--3 5. *---------999--1 6. *---------999--2 Iqnore the -'s What im importing is Name, ID, and other fields. What im trying to assign is SEQ. I'm not sure how to reset a count when I get to the 2nd group. Im using MS SQL Server 2005 ...
I am a bit of an sql noob so please forgive. I can't seem to find a usage example of LTRIM anywhere. I have a NVARCHAR column in my table in which a number of entries have leading whitespace - I'm presuming if I run this it should do the trick: SELECT LTRIM( ColumnName) From TableName; Will this give the desired result? ...
I have a query that gives me all customer's and their last three order dates. EX: CustomerId DateOrdered 167 2006-09-16 01:25:38.060 167 2006-09-21 13:11:53.530 171 2006-08-31 15:19:22.543 171 2006-09-01 13:30:54.013 171 2006-09-01 13:34:36.483 178 2006-09-04 11:36:19.983 186 2006-09-05 12:50:27.153 186 2006-09-05 12:51:08.513 I want...
I am populating a table that acts as a cache. (This is needed because the data comes through a linked server and joins through the link are too expensive) I've included pseudo code below that hopefully demonstrates what I'm trying to do. I really don't know if there's a way to lock a table down like this, or if I need to use transaction...
I'm using SQL Server 2005 and I want to export all records from my Employee table to a .sql file. But when I run the export data option, it only generated the script of the structure of the Employee table, it does not contain the records on it. What i want is to generate a script that will contain an INSERT statement containing the val...
I'm using classic asp and I'm passing in a varchar (ado data type) into an xml data column in a proc. The xml looks as follows: <DocumentCriteria> <Document documentGUID="{B49654E7-9AF2-4B89-AF8F-5146F7CD4911}" graderFYC="5907"> <criterion cnumber="1" rank="3"/> <criterion cnumber="3" rank="3"/> </Document> </Docume...
I have a classic ASP application for student grading with a paper (with an ID and a userID) that requires a grade and scores from several different criteria (criteria 1, score 1, criteria 2, score 2) I wonder if I should put this in XML and push it into SQL Server as XML or convert the name/value pairs to a delimited string and just par...
This error showed when I tried to select on an empty table in MS SQL Server 2005: "either BOF or EOF is True, or the current record has been deleted". I have used TADOConnection and TADODataSet in Delphi 5 to connect and retrieve data from the database. Conn := TADOConnection.Create(nil); DataSet := TADODataSet.Create(nil); Conn....
Hello, When I write report for SQL Reporting Services I put SQL-script inside my report (rdl). When I run this script in SQL Management Studio it takes about 1 second, but report are generated very long time (Sometimes report throws exception about timeout). Should I write stored procedure and use it in report? Will it be faster? And...
I am developing a custom assembly in SSAS. I wanted to make the connection string configurable or i wanted to read the connection string from cubes data source ...
Hi Guys, Please can any ove suggest me how i can remove time part from sql dates. I have already read 100s of articles and most of them remove the time but leaves 00:00:00 behine which i don't want also i know that i can remove these ZERO's by doing a convert to varchar but unfortunately i cannot change the type of the date column it ha...
Hi all I am trying to eak out a bit extra performance from some sql queries which have a where clause on a field which is a non-unique non-clustered index, it is also a foreign key in table A. That foreign key is the primary key on table B and is the clustered index. What I'm wondering is, will I get a performance gain if I add a join ...
In an app, where SQL server databases with schemas and locale unknown at compile-time (determined at runtime) can be plugged in, I need to figure out how to update a decimal field - ie. what decimal separator to use. So against one server, if I need to update a decimal field, I would send 100.125. On another server that would be interpr...
I am executing Dynamic SQL, sqlQuery = " SELECT "; sqlQuery += _Allowed + " , "; sqlQuery += " + cast( "; sqlQuery += " _ID as nvarchar ) "; sqlQuery += " FROM "; sqlQuery += " TBL_SUCCESS "; when i execute it suppose to return common separated values like 2,4,5 in single column instead it return values in separate column my MyDa...
this stored procedure does not return salary with decimal format 00.00 ALTER PROCEDURE taxable_varsalary @emp_code bigint, @co_id bigint AS declare @t_varsalary decimal(8,2) set @t_varsalary = (select sum(tran_value) from emp_ded_ben_trans where emp_code=@emp_code and co_id=@co_id and period_flg=2 and tax_flg=0) RETURN @t_var...
I have at my test database datatable Tags. ID int autoincrement Name nvarchar(255) I want to transfer this table to my production database. How to do it if I want to have the same ids foreach element The problem is that my start index at testdatable is 15, and some ids don't exist because I removed theirs. So: Situation at Test da...
Hi, I am trying to install SQL Server on my machine that has windows XP. During installation system encounters an error while installing MSXML 6 Service Pack 2 (KB954459) and SQL server installation fails. I researched on web and people have recommended using utility at http://support.microsoft.com/kb/290301 to uninstall MSXML and th...
Dear Colleagues, I want to alter a view as follows: ALTER VIEW [dbo].[ViewOne] as SELECT columnOne, -- not null columnTwo, --not null (convert(decimal(2,0), columnOne)) as columnThree -- I want this not to be NULL FROM DBOne.TableOne Since columnOne is "not null" I want to force columnThree to be "not null" also. Is...