How Can I Directly Include A Square Root In A SQL Query

Dec 17, 2006

I have an ASP.NET application where I need to filter the records returned from a SQL query using a calculated distance to a point that is set by the user.   To do this directly, I would need to include a square root of an expression in the SQL query.  It seems like I should be able to do this by writing my SQL query something like:

SELECT SQRT(expression ....) AS distance, column2, column3 ... FROM mytable WHERE distance < 50

Unfortunately, SQL queries apparently don't like the SQRT function.  I am using .NET 2.0 with VS2005 and a MS Access backend (that will soon be moved to SQL/Server).

As an alternate, maybe I can query the data into a temporary record set with a extra field for distance and then step my way through the temporary record set and replace the values in the distance field with a calculated distance.  Once the temporary record set is prepared, then I would delete the records that don't pass the distance requirement or otherwise hide them and bind the data to a gridview control.

Thanks,

David

View 10 Replies


ADVERTISEMENT

ERROR MESSAGE: The Database Filename Can Not Contain The Following 3 Characters: [ (open Square Brace), ] (close Square Brace) And ' (single Quote)

Oct 20, 2007

Hi, I am new to ASP.NET and Visual Web Developer 2005 Express, which I run on Windows Vista. I am currently going through some of the tutorial videos on http://www.asp.net/learn/videos/ . I encountered an error in Lesson 04 and I would have ignored it but it keeps haunting me and now I cannot proceed with the lesson 09 all because of the same error. I would really appreciate it if somebody could help me resolve this because it.
General error text
The database filename can not contain the following 3 characters: [ (open square brace), ] (close square brace) and ' (single quote)
Lesson 4 circumstances


Web.Configis modified to contain the following code just before the closing </system.web> tag:<anonymousIdentification enabled="true"/><profile enabled="true">  <properties>    <add name="MyNewProperty" allowAnonymous="true"/>  </properties></profile>

View 2 Replies View Related

I Want To Get The Root Appliation Path For Loading A File Which Is In My Root Folder Of Windows Application

Dec 4, 2007



hi
my application is applocation1 i kept a file in this folder i want to read it from application how can i get the path of that file in my application.

i want to read the file in my root folder excluding the bin path

Please any one know the answer let me know ..

thanks and regards
Ravishanker Maduri

View 1 Replies View Related

Query View Vs Query Tables Directly

May 19, 2008

Greetings,

I recently started working with a database that uses several views, none of which are indexed. I've compared the execution plans of querying against the view versus querying against the tables and as best I can tell from my limited knowledge the two seem to perform equally. It seems to me that having the view is just one more thing I need to keep track of.

I've done some google searches but haven't found anything that really tells me which performs better, querying the view or the tables directly. Generally speaking which is better?

Thanks in advance for your replies.

View 3 Replies View Related

Query Replica Directly Via SSMS?

Apr 3, 2015

I know it is possible to use the secondary replica to pull data (application intent) etc..

But is there some way to query the replica directly via SSMS?

View 2 Replies View Related

Different Result Running SQL Query Directly Or Using SqlCommand

Sep 11, 2006

Hi, when running the following stored procedure: ALTER PROCEDURE [dbo].[GetWerknemersBijLeidinggevende]@LeidinggevendeID int,@Start int = 1,@Limit int = 25,@Sofinummer int = NULL,@Achternaam nvarchar(128) = NULL,@Functie nvarchar(64) = NULL
ASWITH Ordered AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY Achternaam) AS RowNumber,Persoon.*FROM PersoonINNER JOIN DienstverbandON Persoon.ID = Dienstverband.PersoonIDINNER JOIN BedrijfsonderdeelON Bedrijfsonderdeel.ID = Dienstverband.BedrijfsonderdeelIDINNER JOIN LeidinggevendeON Bedrijfsonderdeel.ID = Leidinggevende.BedrijfsonderdeelIDWHERE
Leidinggevende.Begindatum <= getdate()AND (Leidinggevende.Einddatum > getdate()OR Leidinggevende.Einddatum IS NULL
)
AND Leidinggevende.PersoonID = @LeidinggevendeIDAND
(
Sofinummer = @Sofinummer
OR @Sofinummer IS NULL
)
AND
(
Achternaam LIKE @AchternaamOR AchternaamPartner LIKE @AchternaamOR @Achternaam IS NULL
)
)
SELECT *FROM OrderedWHERE RowNumber between @Start and (@Start + @Limit - 1)  When I run this in the database and fille de LeidinggevendeID parameter with a value I get a few rows returned, however when I run the following code: [DataObject(true)] public class PersoonFactory { [DataObjectMethod(DataObjectMethodType.Select, false)] public static IList WerknemersBijLeidinggevende(int ldgID, int start, int max) { IList list = new List(); SqlDataReader rdr = null; SqlConnection connection = DatabaseProvider.Connection; SqlCommand command = new SqlCommand("GetWerknemersBijLeidinggevende", connection); command.Parameters.AddWithValue("LeidinggevendeID", ldgID); command.CommandType = CommandType.StoredProcedure; try
{
connection.Open();
rdr = command.ExecuteReader(CommandBehavior.CloseConnection);
while (rdr.Read()) { Persoon pers = new Persoon(); pers.ID = rdr["ID"] as int?; pers.Achternaam = rdr["Achternaam"] as string; pers.AchternaamPartner = rdr["AchternaamPartner"] as string; pers.Achtertitels = rdr["Achtertitels"] as string; pers.DatumOverlijden = rdr["DatumOverlijden"] as DateTime?; pers.Geboortedatum = rdr["Geboortedatum"] as DateTime?; pers.Geslacht = rdr["Geslacht"] as string; pers.Middentitels = rdr["Middentitels"] as string; pers.Naamgebruik = (int)rdr["Naamgebruik"]; pers.Sofinummer = rdr["Sofinummer"] as string; pers.Voorletters = rdr["Voorletters"] as string; pers.Voortitels = rdr["Voortitels"] as string; pers.Voorvoegsel = rdr["Voorvoegsel"] as string; pers.VoorvoegselPartner = rdr["VoorvoegselPartner"] as string; list.Add(pers); } } catch
{
throw; } finally
{
if (rdr != null) rdr.Close(); else connection.Close(); } return list; }  I get 0 rows all of a sudden. Any idea why? 

View 7 Replies View Related

SQL 2012 :: Save Query Results Directly Using Variables And Date / Time

Sep 1, 2015

I wonder if it is possible to run a stored procedure and save the results directly to a file in a predetermined directory

As an example I have created a (simple) stored procedure:

USE CovasCopy
GO
CREATE PROCEDURE spTryOut
(
@LastName as NVARCHAR(50)
, @FirstName AS NVARCHAR(25)

[Code] ....

What I would like to add is a (or more?) lines that save the results in a file (csv/txt/tab?)

The name I would like the file to have is "LastName, FirstName, Date query ran, time (HHMMSS?) query ran"

The directory: D:SQLServerResults

View 9 Replies View Related

Can I Query A Spreadsheet Directly From Sql Without Ssis, Excel Macros, Intermediate Table?

Nov 22, 2007

is there a way to query an excel spreadsheet directly from sql without using ssis or excel macros?...and without saving the spreadsheet to a table first?

View 5 Replies View Related

SQL Query To Include One Record Before And After

Oct 29, 2007

Hi I have a query that return set of records between 2 given timestamps. What I want to do is to include one record before and one record after to the result. For example I have the following dataRow    Timestamp1          01/01/20072          15/03/20073          17/04/20074          05/05/20075          10/05/20076          11/06/20077          12/07/2007 My query returns row 3 to 5 for example and I want to include row 2 and 6 to the result as well. How can I do that? Is it possible to do it in 1 query?  

View 11 Replies View Related

How To Include A Single Quote In A Sql Query

Nov 15, 2006

Hi

Declare @Customer varchar(255)
Set @Customer = Single quotes + customer name + single quotes

Select Customerid from Customer Where name = @Customer

I have a query written above, but i was not able to add single quotes to the set statement above. Can i know as how to go about it?

Early reply is much appreciated.

Thanks!

View 15 Replies View Related

Extending CDOSYS Mail To Include Query Attachments??

May 18, 2004

Hi,

I was wondering if anyone has extended the standard CDOSYS Mail Stored Procedure (SP) to allow it to send the results of a query as an attachment?

I have set up a SP for CDOSYS Mail as outlined in the following link:
http://support.microsoft.com/default.aspx?id=kb;de;312839&sd=tech

Currently I am using the old SQL Mail (xp_SendMail). But due to the problems with losing the MAPI connection and other limitations, I have been forced to find another solution. Using SQL Mail, I was able to add a query parameter and attach the results of the query to the email. I need to have the same functionality in CDOSYS Mail

Thanks,
Kim

View 3 Replies View Related

T-SQL (SS2K8) :: Include Row Values As Columns In Select Query

Apr 28, 2015

How to include row values as columns in my select query. I have a table that stores comments for different sections in a web application. In the table below, I would like display each comment as a new column. I only want one row for each record_ID.

Existing table layout

table name - tblcomments
Record_ID Comment_Section_ID Comment
1 5 Test 5 comment
1 7 Test 7 comment
2 5 New comment
2 7 Old comment
3 5 Stop
3 7 Go

Desired table layout
table name - #tempComment
Record_ID Comment_Section_5 Comment_Section_7
1 Test 5 comment Test 7 comment
2 New comment old comment
3 Stop Go

Once I figure out how to get the data in the layout above, I will need to join the table with my record table.

table name - tblRecord
Record_ID Record_Type_ID Record_Status
1 23 Closed
2 56 Open
3 67 Open
4 09 Closed
5 43 In progress

I would like to be able to join the tables in the query below for the final output.

Select r.Record_ID, r.Record_Type_ID, r.Record_Status,
c.Comment_Section_5, c.Comment_Section_7
from tblRecord r
left outer join #tempComment c
on r.record_ID = c.record_ID

How I can get the data in the desired #tempComment table layout mentioned above?

View 2 Replies View Related

Chi-square Test

Aug 9, 2006



hi all:



do ms sql 2005 have chi-square test?

sincerly!

View 5 Replies View Related

Square Brackets In SQL Server

Nov 1, 2004

Hi

Do i have use square brackets surrounding the table names when executing a query in SQL Server ? i.e Is

select * from Department

different from

select * from [Department]

I'm using a case-insensitive SQL Server installation. Any help is appreciated!

Thanks in advance,
Sam

View 1 Replies View Related

Get Rid Of Square Character In Sql 2005

Oct 28, 2007

how to eliminate square character before the data.
When i import it from excel into sql server 2005, it have a double square character on my data. help.

View 2 Replies View Related

Square Boxes Getting Inserted In Table

Apr 11, 2007

hi ,
       In my database I have table with column of datatype nvarchar in which I am writing the address of users.Now address field in aspx page is multiline because of which my address contains characters and
. These special characters are getting inserted in database as square boxes. Is their any way to avoid it. Also the constraint is that I don't want to replace or
by space before writing contents to database.
       Please write to me any solutions u are having.
thanks in advance.
Thanks and regards,
Nita Jadhav.       
 

View 1 Replies View Related

Transact SQL :: Replace Not Supporting Square Bracket?

Jul 1, 2015

update t1
     set t1.col1 = replace(t1.col1, t2.col1, t2.col2)
from table1 t1
inner join table2 t2 on t1.col3 = t2.col3

I am not able to replace some record which of t2.col1 contain like "[abc]def"

I have used "[][abc]def" and "/[abc/]def" for same but its not working.

View 4 Replies View Related

NTEXT Special Character SQUARE Appearing Where A Return Should Be. How Do I Fix This?

Sep 17, 2006

I had to import some FileMaker Data into SQL Server 2005 and in the resume field a "SQUARE" special character is appearing everyplace that a return should be.

It has really messed up the formatting. I know how to use the updatetext in a cursor to replace the special character but I don't know what to replace the "SQUARE" special character with so the text is displaying a new line rather then this "SQUARE" character.



Any help would be appreicated.

View 6 Replies View Related

FOR XML Sub Root

Apr 6, 2008

Hi all
I am trying to create xml file from the data, everything works fine cept I can't get the format that I need.

This is my SP:

WITH XMLNAMESPACES (
'urn:http://base.google.com/cns/1.0' as "c"
, 'urn:http://base.google.com/ns/1.0' as "g"
)

SELECT top 2
TITLE "item/title",
description "item/description",
employer "item/g:employer",
job_type "item/g:job_type",
link "item/link",
location "item/g:location",
salary "item/g:salary",
education "item/g:education"


FROM JobExport
FOR XML PATH ('chanel'), ROOT('rss')

I need to get:

<rss version="2.0" xmlns:g="http://base.google.com/ns/1.0" xmlns:c="http://base.google.com/cns/1.0">
- <channel>
- <item>
<title></title>
<description></description>
<g:employer></g:employer>
<g:job_type />
<link></link>
<g:location></g:location>
<g:salary />
<g:education></g:education>
</item>
- <item>
<title></title>
<description></description>
<g:employer></g:employer>
<g:job_type />
<link></link>
<g:location></g:location>
<g:salary />
<g:education>High School</g:education>
</item>
</channel>
</rss>

but I get this:


<rss xmlns:g="urn:http://base.google.com/ns/1.0" xmlns:c="urn:http://base.google.com/cns/1.0">
<chanel>
<item></title>
<description></description>
<g:employer></g:employer>
<g:job_type></g:job_type>
<link></link>
<g:location></g:location>
<g:salary></g:salary>
<g:education></g:education>
</item>
</chanel>
<chanel>
<item>
<title></title>
<description></description>
<g:employer></g:employer>
<g:job_type></g:job_type>
<link></link>
<g:location></g:location>
<g:salary></g:salary>
</item>
</chanel>
</rss>

The problem is that the "chanel" need to be a subroot (not sure what else to call it)

View 4 Replies View Related

Root Node From A FOR XML

Aug 26, 2005

Hello,

How can I get a XML with a root node from a FOR XML ?:
Now I have this:

The SQL:
SELECT * FROM Table_Projects Project
FOR XML AUTO

and the XML output:
<Project>
    <ItemID>1</ItemID>
    .....
</Project>
<Project>

    <ItemID>2</ItemID>

    .....

</Project>
...

and I want this:


<Projects>
    <Project>
        <ItemID>1</ItemID>
        .....
    </Project>
    <Project>
        <ItemID>2</ItemID>
        .....
    </Project>
    ...
</Projects>

thanks and regards,
Edu

View 5 Replies View Related

Different Login && SQL Console Root .....

May 28, 2003

Logged on local Administrator on one of my sqlserver 2000 "ABC" and registered 5 sql groups and linked 50 sql2k servers from MSDE to Standare (all sp3).
Now, I need a different login on this machine "ABC", how could I get back all the SQL Enterprise Manager Console Root Setting and connections like before?

thanks
David

View 11 Replies View Related

XML Source - What About The Root Element?

Apr 7, 2008

Greetings!

Why does XML Source in MS VS 2005 Integration Services Data Flow Sources XML Source not pick up the root element?

The problem is the following example:
<root createddate="2008-01-01">
<elements>
...
</elements
</root>

The XML Source picks up everything after the first element which is the root element but it does not seam to pick up the root element where there is a need for the createddate attribute.

Is there a soultion for this problem or a workaround?

Thanks for help.

View 4 Replies View Related

Retrieve Value Of The Root Attribute In XML

Apr 2, 2008

Hello everyone,

I have an xml file that looks like this:

?xml version="1.0" encoding="UTF-8"?>

<WHOLE Date="*********">
<MAIN .......</MAIN>
</WHOLE>

What i need is to find a way to retrieve the value of the attribute belonging to the root element (value of @Date) in SSIS.
Tried it using Xpath but don't know how i should configure the xml task editor or if maybe there's any other way to perform such a task.

thank you

View 6 Replies View Related

How To Change The Root Directory?

May 23, 2007

Hello,



I was wondering if it were possible to change the Root Directory on an SQL Server 2005 install?



expl: current = C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQL



desired: D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQL

View 7 Replies View Related

Master Database On Root Drive??

Apr 3, 2002

I have a 'need an opinion' question. I've always installed the system databases (master, msdb, model, tempdb) on the root drive (c:) with the sql application. Then I was thinking, the only things that really should be on the root drive is the sql app itself (and any other required apps). So...my question is, do y'all install the master, etc on a separate drive other than the root, ie d: or e:? The reason I ask is that if the tempdb database has alot of activity, and the database resides on the root drive, could it affect sql performance?

Just wondering. I just started working in a 'virgin' sql environment (setting up sql servers from scratch), and want to have sql installed in the best possible way for performance.

Thanks in advance for any info y'all can provide.

View 3 Replies View Related

TempDB Files On Root Of Drive

Sep 15, 2015

Documentation that supports the placement of Tempdb files on the root of a drive, i.e T: instead of T: empdb. I am positive this is not a best practice, but when challenged could not find any documentation that would support that view.

View 7 Replies View Related

Does Anyone Know How To Change The Document Map Root Text?

Jan 17, 2006

hi, guys
Does anyone know how to change the Document Map root text? For example, i have report, the file name is sc.rdl, and then the root is sc. Apparently this is not good. I am thinking is there a way to change it.

View 3 Replies View Related

Root Directory Of SSIS Package

Jan 12, 2008

Hi everyone!
I have a configuration file, I want to place it in somewhere of SSIS package to extract configuration information. But i dont know where is root directory. can you help me?
Thanks you very much

View 3 Replies View Related

Root Element Is Missing Problem

Apr 22, 2008

Configuration: SQL Server 2005 Enterprise edition on Microsoft Cluster. Machines named Node1, Node2 and NodeV (Virtual Node). Best practices require SSRS be deployeed on an IIS6.0 server. Reporting services was installed on the Web1 machine and pointing back to the database on the NodeV platform. Everything appears to run fine in Reporting Services. However, when I use the ReportViewer control from within a webpage, the basic control appears along with the message "root element is missing". Using the same code, I point the ReportViewer control to a different server Web2 where Sql server standard, IIS and RS are installed and the report renders properly. What am I missing in the configuration of the Web1 box as far as IIS or SSRS are concerned?

View 4 Replies View Related

How To Move Server Root Directory To Another Partition?

Apr 18, 2000

I want to move the SQL Server 7.0 root directory from C:MSSQL7 to D:MSSQL7 (same host). I see the startup parameters under server properties could be changed to point to the new location, but Master has the paths to all the databases recorded in sysdatabases table, so at least that would have to change, and sysdevices table points to files on C:. And maybe the NT registry points to some files there too. Anyway, is there some established method to move it? If that isn't practical, at least I would want to move TEMPDB ( perhaps by using the SP_DETACH and SP_ATTACH method ). My main database is already on D: so I do not have to move it. Any informed advice would be appreciated. Thanks.

View 1 Replies View Related

Putting Attribute On Root Node Using FOR XML PATH

May 17, 2006

I would like to know how to put an attribute on the root node of thexml returned from a FOR XML PATH query. One thing I tried is this:selectm.msgid '@msgID',st.namelong 'set/@namelong',st.nameshort 'set/@nameshort',frommsgset minner join settable st on (st.setid = m.setid)where m.msgID = 195for xml path('set'), root('message')but it gives me:<message><set msgID="195"><set namelong="STUFF HERE" nameshort="STUFF" /></set><set msgID="195"><set namelong="MORE STUFF" nameshort="M STUFF" /></set><set msgID="195"><set namelong="TESTING 123" nameshort="TEST" /></set></message>here is what I want:<message msgID="195"><set namelong="STUFF HERE" nameshort="STUFF" /><set namelong="MORE STUFF" nameshort="M STUFF" /><set namelong="TESTING 123" nameshort="TEST" /></message>I can't get it. If I use: root(''), then it tells me: "Row tagomission (empty row tag name) cannot be used with attribute-centric FORXML serialization." I'm sure there is a trick to this-- anysuggestions?Many thanks.the chippster

View 5 Replies View Related

Remove ROOT Tags From SQL Task XML Results

Dec 19, 2007

Hi All,

I'm currently using an Execute SQL Task to return XML data from a query into an SSIS string variable. In my FOR XML clause in SQL I'm specifying a certain name for my root tag, called "Accounts". This works great in Management Studio, however, the Execute SQL Task appends a <ROOT> and </ROOT> tag to the start and end of the string, so now it looks like:

<ROOT><Accounts>...all my elements...</Accounts></ROOT>

I'd like to remove the ROOT tags so that the <Accounts> tags are actually the root for this doc. What would be the best way to remove the ROOT tags from the SSIS string variable?

Thanks!

View 9 Replies View Related

Need Help In Pulling Out A Date In The Root Portion Of An XML File

Jul 27, 2007

Below is a file that I have loaded into a Sql table:

<btb-root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" btb-num-trans="2" btb-date="2006-11-09" btb-time="22:40:03" btb-sender="BTB" btb-receipient="BPO-USR">

- <btb-request req-method="Asynchronous">

- <req-header>


<req-btb-id>68790</req-btb-id>

<req-client-id>1133</req-client-id>

<req-product>BPO-Exterior</req-product>

<req-loan-number>00000</req-loan-number>
</req-header>

- <req-property-address>


<addr1>1115 TEST DR</addr1>

<city>TEST</city>

<state>TEstate>

<zip>30044</zip>
</req-property-address>

- <req-borrowers borr-type="Borrower">


<first-name>Test</first-name>

<last-name>TE</last-name>
</req-borrowers>
</btb-request>

My goal is to take the btb-date and store it in the same table I loaded the seperate nodes to. Currently I am loading the req-header, req-property-address, and req-borrowers.

This date will be static in that it will remain the same for every record. My goal is to read it in and store it along with each record. Hope someone can give me some help.

Thanks.

View 4 Replies View Related







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