Search Results

Search found 95301 results on 3813 pages for 'client server'.

Page 110/3813 | < Previous Page | 106 107 108 109 110 111 112 113 114 115 116 117  | Next Page >

  • Issue Calculating from Rows and Columns(Summing two columns with the third of a different row)

    - by vstsdev
    With reference to my previous question Adding columns resulting from GROUP BY clause SELECT AcctId,Date, Sum(CASE WHEN DC = 'C' THEN TrnAmt ELSE 0 END) AS C, Sum(CASE WHEN DC = 'D' THEN TrnAmt ELSE 0 END) AS D FROM Table1 where AcctId = '51' GROUP BY AcctId,Date ORDER BY AcctId,Date I executed the above query and got my desired result.. AcctId Date C D 51 2012-12-04 15000 0 51 2012-12-05 150000 160596 51 2012-12-06 600 0 now I have a another operation to do on the same query i.e. I need the result to be like this AcctId Date Result 51 2012-12-04 (15000-0)-> 15000 51 2012-12-05 (150000-160596) + (15000->The first value) 4404 51 2012-12-06 600-0 +(4404 ->The calculated 2nd value) 5004 Is it possible with the same query??.

    Read the article

  • sql 2008 metadata modified date

    - by Kumar
    Is there a way to identify the timestamp when an object(table/view/stored proc...) was modified ? there's a refdate in sysobjects but it's always the same as crdate atleast in my case and i know that alter view/alter table/alter proc commands have been run many times post creation

    Read the article

  • How to create a SOAP REQUEST using ASP.NET (VB) without using Visual

    - by user311691
    Hi all , I urgently need your help . I am new to consuming a web service using SOAP protocol. I have been given a demo webservice URL which ends in .WSDL and NOT .asml?WSDL. The problem is I cannot add a web reference using Visual studio OR Disco.exe or Wsdl.exe - This webservice has been created on a java platform and for security reasons the only way to make a invoke the webservice is at runtime using SOAP protocol IN asp.net (VB). I I have created some code but cannot seem to send the soap object to the receiving web service. If I could get a solution with step by step instructions on how I can send a SOAP REQUEST. Below is my code and all am trying to do is send a SOAP REQUEST and receive a SOAP RESPONSE which I will display in my browser. <%@ page language="vb" %> <%@ Import Namespace="System.Data"%> <%@ Import Namespace="System.Xml"%> <%@ Import Namespace="System.Net"%> <%@ Import Namespace="System.IO"%> <%@ Import Namespace="System.Text"%> <script runat=server> Private Sub Page_Load() Dim objHTTPReq As HttpWebRequest Dim WebserviceUrl As String = "http://xx.xx.xx:8084/asy/wsdl/asy.wsdl" objHTTPReq = CType(WebRequest.Create(WebserviceUrl), HttpWebRequest) Dim soapXML As String soapXML = "<?xml version='1.0' encoding='utf-8'?>" & _ " <soap:Envelope xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'" & _ " xmlns:xsd='http://www.w3.org/2001/XMLSchema'"& _ " xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/' >"& _ " <soap:Body> "& _ " <validatePaymentData xmlns='http://asybanks.webservices.asycuda.org'> " & _ " <bankCode>"& bankCode &"</bankCode> " & _ " <PaymentDataType>" & _ " <paymentType>"& payment_type &"</paymentType> " & _ " <amount>"& ass_amount &"</amount> " & _ " <ReferenceType>" & _ " <year>"& year &"</year> " & _ " <customsOfficeCode>"& station &"</customsOfficeCode> " & _ " </ReferenceType>" & _ " <accountNumber>"& zra_account &"</accountNumber> " & _ " </PaymentDataType> " & _ " </validatePaymentData> " & _ " </soap:Body> " & _ " </soap:Envelope> " objHTTPReq.Headers.Add("SOAPAction", "http://asybanks.webservices.asycuda.org") objHTTPReq.ContentType = "text/xml; charset=utf-8" objHTTPReq.ContentLength = soapXML.Length objHTTPReq.Accept = "text/xml" objHTTPReq.Method = "POST" Dim objHTTPRes As HttpWebResponse = CType(objHTTPReq.GetResponse(), HttpWebResponse) Dim dataStream As Stream = objHTTPRes.GetResponseStream() Dim reader As StreamReader = new StreamReader(dataStream) Dim responseFromServer As String = reader.ReadToEnd() OurXml.text = responseFromServer End Sub </script> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title> XML TRANSACTION SIMULATION - N@W@ TJ </title> </head> <body> <form id="form1" runat="server"> <div> <p>ZRA test Feedback:</p> <asp:label id="OurXml" runat="server"/> </div> </form> </body> </html> the demo webservice looks like this: <?xml version="1.0" encoding="UTF-8" ?> - <!-- WEB SERVICE JAVA DEMO --> - <definitions targetNamespace="http://asybanks.webservices.asycuda.org" xmlns="http://schemas.xmlsoap.org/wsdl/" xmlns:apachesoap="http://xml.apache.org/xml-soap" xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:y="http://asybanks.webservices.asycuda.org"> - <types> - <xs:schema elementFormDefault="qualified" targetNamespace="http://asybanks.webservices.asycuda.org" xmlns="http://www.w3.org/2001/XMLSchema"> SOME OTHER INFORMATION AT THE BOTTOM <soap:address location="http://xx.xx.xx:8084/asy/services/asy" /> </port> </service> </definitions> From the above excerpt of the wsdl url webservice, I am not sure which namespace to use for soapACTION - please advise.... Please if you could comment every stage of a soap request and provide a working demo - I would be most grateful as I would be learning rather than just assuming stuff :)

    Read the article

  • Where clause in joins vs Where clause in Sub Query

    - by Kanavi
    DDL create table t ( id int Identity(1,1), nam varchar(100) ) create table t1 ( id int Identity(1,1), nam varchar(100) ) DML Insert into t( nam)values( 'a') Insert into t( nam)values( 'b') Insert into t( nam)values( 'c') Insert into t( nam)values( 'd') Insert into t( nam)values( 'e') Insert into t( nam)values( 'f') Insert into t1( nam)values( 'aa') Insert into t1( nam)values( 'bb') Insert into t1( nam)values( 'cc') Insert into t1( nam)values( 'dd') Insert into t1( nam)values( 'ee') Insert into t1( nam)values( 'ff') Query - 1 Select t.*, t1.* From t t Inner join t1 t1 on t.id = t1.id Where t.id = 1 Query 1 SQL profiler Result Reads = 56, Duration = 4 Query - 2 Select T1.*, K.* from ( Select id, nam from t Where id = 1 )K Inner Join t1 T1 on T1.id = K.id Query 2 SQL Profiler Results Reads = 262 and Duration = 2 You can also see my SQlFiddle Query - Which query should be used and why?

    Read the article

  • Dynamic openrowset in T-Sql Function or viable alternative?

    - by IronicMuffin
    I'm not quite sure how to phrase this. Here is the problem: I have 1-n items that I need to join to a different system (AS400) to get some data. The openrowset takes forever if I specify the where criteria outside of the openrowset, e.g.: select * from openrowset('my connection string', 'select code, myfield from myTable') where code = @code My idea was to create a function that takes in the item number and uses dynamic sql to inject it into the openrowset string, a la: declare @cmd varchar(1000) set @cmd = 'select * from openrowset('my connection string', ''select code, myfield from myTable where code = ' + @code + ''')' Apparently I can't use the insert.. exec.. strategy inside of a function. Is there any better way to achieve this? I was going to use this in joins where I needed the external data using cross apply. I'm not married to tvf and cross apply, but I do need a method of getting this data quickly. Thanks for any help.

    Read the article

  • Cannot delete from sys.tables

    - by Shimmy
    How can I perform this query on whatever way: delete from sys.tables where is_ms_shipped = 0 What happened is, I executed a very large query and I forgot to put USE directive on top of it, now I got a zillion tables on my master db, and don't want to delete them one by one. UPDATE: It's a brand new database, so I don't have to care about any previous data, the final result I want to achieve is to reset the master db to factory shipping.

    Read the article

  • CTE and last known date processing

    - by stackoverflowuser
    Input @StartDate = '01/25/2010' @EndDate = '02/06/2010' I have 2 CTEs in a stored procedure as follows: with CTE_A as ( [gives output A..Shown below] ), with CTE_B as ( Here, I want to check if @StartDate is NOT in output A then replace it with the last known date. In this case, since @startdate is less than any date in output A hence @StartDate will become 02/01/2010. Also to check if @EndDate is NOT in output A then replace it with the last known date. In this case, since @enddate is 02/06/2010 hence it will be replace with 02/05/2010. // Here there is a query using @startDate and @EndDate. ) output A Name Date A 02/01/2010 B 02/01/2010 C 02/05/2010 D 02/10/2010

    Read the article

  • Strange Values in SYS.DM_TRAN_LOCKS table RESOURCE_ASSOCIATED_ENTITY_ID column

    - by AJM
    I’ve been trying to understand some strange values in the RESOURCE_ASSOCIATED_ENTITY_ID column of SYS.DM_TRAN_LOCKS when RESOURCE_TYPE is “OBJECT”. Although these should be object Ids, I cannot determine what object they actually refer to. I’ve tried everything I can think of, including querying all system tables with columns of type INT and BIGINT to see if I can find the value. No luck. The funny values actually appear in SYS.DM_TRAN_LOCKS, SYS.SYSLOCKINFO and SP_LOCK.

    Read the article

  • SQL 2005 - Search stored procedures for text (Not all text is being searched)

    - by hamlin11
    The following bits of code do not seem to be searching the entire routine definition. Code block 1: select top 50 * from information_schema.routines where routine_definition like '%09/01/2008%' and specific_Name like '%NET' Code Block 2: SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%EffectiveDate%' AND ROUTINE_TYPE='PROCEDURE' and ROUTINE_NAME like '%NET' I know for a fact that these bits of SQL work under most circumstances. The problem is this: When I run this for "EffectiveDate" which is buried at line ~800 in a few stored procedures, these stored procedures never show up in the results. It's as if "like" only searches so deep. Any tips on fixing this? I want to search the ENTIRE stored procedure for the specified text. Thanks!

    Read the article

  • Migrating from Physical SQL (SQL2000) To VMWare machine (SQL2008) - Transferring Large DB

    - by alex
    We're in the middle of migrating from a windows & SQL 2000 box to a Virtualised Win & SQL 2k8 box The VMWare box is on a different site, with better hardware, connectivity etc... The old(current) physical machine is still in constant use - I've taken a backup of the DB on this machine, which is 21GB Transfering this to our virtual machine took around 7+ hours - which isn't ideal when we do the "actual" switchover. My question is - How should I handle the migration better? Could i set up our current machine to do log shipping to the VM machine to keep up to date? then, schedule down time out of hours to do the switch over? Is there a better way?

    Read the article

  • How would I design this table in SQL?

    - by RSharma
    I have a parent master table that is generic enough to hold the common information of the children. Since the children were substantially different, we created separate tables for them. So I have something like this: tblMaster -------- MasterID int Name varchar(50) --Common to all children and there are a bunch of fields like this ChildType int -- Type of Child either ChildOne or ChildTwo ChildID int -- need to store ChildOneID or ChildTwoID depending on type of Child, so that i can refer to children tblChild1 -------- ChildOneID int IDENTITY tblChild2 --------- ChildTwoID int IDENTITY Should I have a ChildID in the master that is either ChildOneID or ChildTwoID based on the ChildType column? I have a number of children and I have simplified it for this question. The other way is to add ChildOneID and ChildTwoID as columns in the master, but since i have a number of columns, I will have a lot of null columns EDIT: Any help is appreciated

    Read the article

  • Passing delimited string to stored procedure to search database

    - by rs
    How can i pass a string delimited by space or comma to stored procedure and filter result? I'm trying to do something like - Parameter Value -------------------------- @keywords key1 key2 key3 Then is stored procedure i want to first find all records with first or last name like key1 filter step 1 with first or last name like key2 filter step 2 with first or last name like key 3

    Read the article

  • Will a SQL Server client alias survive a sysprep?

    - by shufler
    I want to sysprep a Windows Server 2008 R2 SP1 machine that has SQL Server 2008 R2 SP1 installed (for reference, SQL Server 2008 R2 has a new sysprep feature that allows the instance to be sysprepped). On the server is a SQL Server client alias that points to the default SQL Server database engine instance. For reference, the alias is called Alias-SQLServer and has been configured in both 32-bit and 64-bit cliconfig versions (that is, both registry keys exist) The alias points to the local instance as the image will be used to create development VMs and the installation script for the application that is being developed will use the SQL Server client alias in order to generalize the installation scripts. I can't seem to find information about whether the sysprep tool will update the SQL Server client alias's registry keys with the server's new name once it's unsealed. My guess is that it is not; how is sysprep to know that the server name the alias points to will be different for each image? Right? Perhaps if the alias points to localhost instead of the server name this will work?

    Read the article

  • How do you unit test your T-SQL

    - by AlexKuznetsov
    How do you unit test your T-SQL? Which libraries/tools do you use? What percentage of your code is covered by unit tests and how do you measure it? Do you think the time and effort which you invested in your unit testing harness has paid off or not? If you do not use unit testing, can you explain why not?

    Read the article

  • Is there a way to prevent Triggers from being disabled?

    - by HAdes
    I have a trigger on a table that should never be disabled. It performs certain checks and there have been occasions when other developers have disabled it to get around it. This is not good so I want to be able to turn off trigger disablement on this table alone. Is this possible? If not, any suggestions please. thanks.

    Read the article

  • Why use the INCLUDE clause when creating an index?

    - by Cory
    While studying for the 70-433 exam I noticed you can create a covering index in one of the following two ways. CREATE INDEX idx1 ON MyTable (Col1, Col2, Col3) -- OR -- CREATE INDEX idx1 ON MyTable (Col1) INCLUDE (Col2, Col3) The INCLUDE clause is new to me. Why would you use it and what guidelines would you suggest in determining whether to create a covering index with or without the INCLUDE clause?

    Read the article

  • Insert Statment with Case for avoid duplicate record insertion

    - by rama
    I have written the below SP for Precheck for Duplicate records before insert into Table . but it is not allow me yo write insert staement inside the CASE . how can I write Stored Procedure for fist Check the value @Ordername into table After that if it is not present then it should inserted into Database . CREATE PROCEDURE [Test Procedure ] ( @section varchar(70), @mark varchar(70), @qty decimal(18,2), @Weight decimal(18,2), @dateupdateremark int, @OrderName varchar(70) ) AS BEGIN SET NOCOUNT ON; select case(@OrderName) when (select OrderName from dbo.tbl_insertxmldetails where(@OrderName) not in (select OrderName from tbl_insertxmldetails)) then insert into dbo.tbl_insertxmldetails (Section, Mark, QTY,Weight,Dateupdateremark ,OrderName,SystemDate) values (@Section, @Mark, @QTY,@Weight, @Dateupdateremark,@OrderName,GETDATE()) else 'File already Exists' end

    Read the article

  • SQL SERVER – Fix : Error 3623 – An invalid floating point operation occurred

    - by pinaldave
    Going back in time, I always had a problem with mathematics. It was a great subject and I loved it a lot but I only mastered it after practices a lot. I learned that mathematics problems should be addressed systematically and being verbose is not a trick, I learned to solve any problem. Recently one of reader sent me an email with the title “Mathematics problem – please help!” and I was a bit scared. I was good at mathematics but not the best. When I opened the email I was relieved as it was Mathematics problem with SQL Server. My friend received following error while working with SQL Server. Msg 3623, Level 16, State 1, Line 1 An invalid floating point operation occurred. The reasons for the error is simply that invalid usage of the mathematical function is attempted. Let me give you a few examples of the same. SELECT SQRT(-5); SELECT ACOS(-3); SELECT LOG(-9); If you run any of the above functions they will give you an error related to invalid floating point. Honestly there is no workaround except passing the function appropriate values. SQRT of a negative number will give you result in real numbers which is not supported at this point of time as well LOG of a negative number is not possible (because logarithm is the inverse function of an exponential function and the exponential function is NEVER negative). When I send above reply to my friend he did understand that he was passing incorrect value to the function. As mentioned earlier the only way to fix this issue is finding incorrect value and avoid passing it to the function. Every mathematics function is different and there is not a single solution to identify erroneous value passed. If you are facing this error and not able to figure out the solution. Post a comment and I will do my best to figure out the solution. Reference: Pinal Dave (http://blog.sqlauthority.com) Filed under: PostADay, SQL, SQL Authority, SQL Error Messages, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology

    Read the article

  • SQL SERVER – Signal Wait Time Introduction with Simple Example – Wait Type – Day 2 of 28

    - by pinaldave
    In this post, let’s delve a bit more in depth regarding wait stats. The very first question: when do the wait stats occur? Here is the simple answer. When SQL Server is executing any task, and if for any reason it has to wait for resources to execute the task, this wait is recorded by SQL Server with the reason for the delay. Later on we can analyze these wait stats to understand the reason the task was delayed and maybe we can eliminate the wait for SQL Server. It is not always possible to remove the wait type 100%, but there are few suggestions that can help. Before we continue learning about wait types and wait stats, we need to understand three important milestones of the query life-cycle. Running - a query which is being executed on a CPU is called a running query. This query is responsible for CPU time. Runnable – a query which is ready to execute and waiting for its turn to run is called a runnable query. This query is responsible for Signal Wait time. (In other words, the query is ready to run but CPU is servicing another query). Suspended – a query which is waiting due to any reason (to know the reason, we are learning wait stats) to be converted to runnable is suspended query. This query is responsible for wait time. (In other words, this is the time we are trying to reduce). In simple words, query execution time is a summation of the query Executing CPU Time (Running) + Query Wait Time (Suspended) + Query Signal Wait Time (Runnable). Again, it may be possible a query goes to all these stats multiple times. Let us try to understand the whole thing with a simple analogy of a taxi and a passenger. Two friends, Tom and Danny, go to the mall together. When they leave the mall, they decide to take a taxi. Tom and Danny both stand in the line waiting for their turn to get into the taxi. This is the Signal Wait Time as they are ready to get into the taxi but the taxis are currently serving other customer and they have to wait for their turn. In other word they are in a runnable state. Now when it is their turn to get into the taxi, the taxi driver informs them he does not take credit cards and only cash is accepted. Neither Tom nor Danny have enough cash, they both cannot get into the vehicle. Tom waits outside in the queue and Danny goes to ATM to fetch the cash. During this time the taxi cannot wait, they have to let other passengers get into the taxi. As Tom and Danny both are outside in the queue, this is the Query Wait Time and they are in the suspended state. They cannot do anything till they get the cash. Once Danny gets the cash, they are both standing in the line again, creating one more Signal Wait Time. This time when their turn comes they can pay the taxi driver in cash and reach their destination. The time taken for the taxi to get from the mall to the destination is running time (CPU time) and the taxi is running. I hope this analogy is bit clear with the wait stats. You can check the Signalwait stats using following query of Glenn Berry. -- Signal Waits for instance SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) AS [%signal (cpu) waits], CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) AS [%resource waits] FROM sys.dm_os_wait_stats OPTION (RECOMPILE); Higher the Signal wait stats are not good for the system. Very high value indicates CPU pressure. In my experience, when systems are running smooth and without any glitch the Signal wait stat is lower than 20%. Again, this number can be debated (and it is from my experience and is not documented anywhere). In other words, lower is better and higher is not good for the system. In future articles we will discuss in detail the various wait types and wait stats and their resolution. Read all the post in the Wait Types and Queue series. Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: Pinal Dave, PostADay, SQL, SQL Authority, SQL DMV, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, SQL Wait Stats, SQL Wait Types, T SQL, Technology

    Read the article

  • SQL SERVER – Single Wait Time Introduction with Simple Example – Wait Type – Day 2 of 28

    - by pinaldave
    In this post, let’s delve a bit more in depth regarding wait stats. The very first question: when do the wait stats occur? Here is the simple answer. When SQL Server is executing any task, and if for any reason it has to wait for resources to execute the task, this wait is recorded by SQL Server with the reason for the delay. Later on we can analyze these wait stats to understand the reason the task was delayed and maybe we can eliminate the wait for SQL Server. It is not always possible to remove the wait type 100%, but there are few suggestions that can help. Before we continue learning about wait types and wait stats, we need to understand three important milestones of the query life-cycle. Running - a query which is being executed on a CPU is called a running query. This query is responsible for CPU time. Runnable – a query which is ready to execute and waiting for its turn to run is called a runnable query. This query is responsible for Single Wait time. (In other words, the query is ready to run but CPU is servicing another query). Suspended – a query which is waiting due to any reason (to know the reason, we are learning wait stats) to be converted to runnable is suspended query. This query is responsible for wait time. (In other words, this is the time we are trying to reduce). In simple words, query execution time is a summation of the query Executing CPU Time (Running) + Query Wait Time (Suspended) + Query Single Wait Time (Runnable). Again, it may be possible a query goes to all these stats multiple times. Let us try to understand the whole thing with a simple analogy of a taxi and a passenger. Two friends, Tom and Danny, go to the mall together. When they leave the mall, they decide to take a taxi. Tom and Danny both stand in the line waiting for their turn to get into the taxi. This is the Signal Wait Time as they are ready to get into the taxi but the taxis are currently serving other customer and they have to wait for their turn. In other word they are in a runnable state. Now when it is their turn to get into the taxi, the taxi driver informs them he does not take credit cards and only cash is accepted. Neither Tom nor Danny have enough cash, they both cannot get into the vehicle. Tom waits outside in the queue and Danny goes to ATM to fetch the cash. During this time the taxi cannot wait, they have to let other passengers get into the taxi. As Tom and Danny both are outside in the queue, this is the Query Wait Time and they are in the suspended state. They cannot do anything till they get the cash. Once Danny gets the cash, they are both standing in the line again, creating one more Single Wait Time. This time when their turn comes they can pay the taxi driver in cash and reach their destination. The time taken for the taxi to get from the mall to the destination is running time (CPU time) and the taxi is running. I hope this analogy is bit clear with the wait stats. You can check the single wait stats using following query of Glenn Berry. -- Signal Waits for instance SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) AS [%signal (cpu) waits], CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) AS [%resource waits] FROM sys.dm_os_wait_stats OPTION (RECOMPILE); Higher the single wait stats are not good for the system. Very high value indicates CPU pressure. In my experience, when systems are running smooth and without any glitch the single wait stat is lower than 20%. Again, this number can be debated (and it is from my experience and is not documented anywhere). In other words, lower is better and higher is not good for the system. In future articles we will discuss in detail the various wait types and wait stats and their resolution. Read all the post in the Wait Types and Queue series. Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: Pinal Dave, PostADay, SQL, SQL Authority, SQL DMV, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, SQL Wait Stats, SQL Wait Types, T SQL, Technology

    Read the article

  • SQLAuthority News – Best Practices for Data Warehousing with SQL Server 2008 R2

    - by pinaldave
    An integral part of any BI system is the data warehouse—a central repository of data that is regularly refreshed from the source systems. The new data is transferred at regular intervals  by extract, transform, and load (ETL) processes. This whitepaper talks about what are best practices for Data Warehousing. This whitepaper discusses ETL, Analysis, Reporting as well relational database. The main focus of this whitepaper is on mainly ‘architecture’ and ‘performance’. Download Best Practices for Data Warehousing with SQL Server 2008 R2 Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: Best Practices, Data Warehousing, PostADay, SQL, SQL Authority, SQL Documentation, SQL Download, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology

    Read the article

  • SQL SERVER – Finding Shortest Distance between Two Shapes using Spatial Data Classes – Ramsetu or Adam’s Bridge

    - by pinaldave
    Recently I was reading excellent blog post by Lenni Lobel on Spatial Database. He has written very interesting function ShortestLineTo in Spatial Data Classes. I really loved this new feature of the finding shortest distance between two shapes in SQL Server. Following is the example which is same as Lenni talk on his blog article . DECLARE @Shape1 geometry = 'POLYGON ((-20 -30, -3 -26, 14 -28, 20 -40, -20 -30))' DECLARE @Shape2 geometry = 'POLYGON ((-18 -20, 0 -10, 4 -12, 10 -20, 2 -22, -18 -20))' SELECT @Shape1 UNION ALL SELECT @Shape2 UNION ALL SELECT @Shape1.ShortestLineTo(@Shape2).STBuffer(.25) GO When you run this script SQL Server finds out the shortest distance between two shapes and draws the line. We are using STBuffer so we can see the connecting line clearly. Now let us modify one of the object and then we see how the connecting shortest line works. DECLARE @Shape1 geometry = 'POLYGON ((-20 -30, -3 -30, 14 -28, 20 -40, -20 -30))' DECLARE @Shape2 geometry = 'POLYGON ((-18 -20, 0 -10, 4 -12, 10 -20, 2 -22, -18 -20))' SELECT @Shape1 UNION ALL SELECT @Shape2 UNION ALL SELECT @Shape1.ShortestLineTo(@Shape2).STBuffer(.25) GO Now once again let us modify one of the script and see how the shortest line to works. DECLARE @Shape1 geometry = 'POLYGON ((-20 -30, -3 -30, 14 -28, 20 -40, -20 -30))' DECLARE @Shape2 geometry = 'POLYGON ((-18 -20, 0 -10, 4 -12, 10 -20, 2 -18, -18 -20))' SELECT @Shape1 UNION ALL SELECT @Shape2 UNION ALL SELECT @Shape1.ShortestLineTo(@Shape2).STBuffer(.25) SELECT @Shape1.STDistance(@Shape2) GO You can see as the objects are changing the shortest lines are moving at appropriate place. I think even though this is very small feature this is really cool know. While I was working on this example, I suddenly thought about distance between Sri Lanka and India. The distance is very short infect it is less than 30 km by sea. I decided to map India and Sri Lanka using spatial data classes. To my surprise the plotted shortest line is the same as Adam’s Bridge or Ramsetu. Adam’s Bridge starts as chain of shoals from the Dhanushkodi tip of India’s Pamban Island and ends at Sri Lanka’s Mannar Island. Geological evidence suggests that this bridge is a former land connection between India and Sri Lanka. Reference: Pinal Dave (http://blog.sqlauthority.com) Filed under: PostADay, SQL, SQL Authority, SQL Function, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology Tagged: Spatial Database, SQL Spatial

    Read the article

< Previous Page | 106 107 108 109 110 111 112 113 114 115 116 117  | Next Page >