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
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
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
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
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
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
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
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
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
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
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
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
Aug 9, 2006
hi all:
do ms sql 2005 have chi-square test?
sincerly!
View 5 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
