What I need to do, is come up with a script that will determine which Polygon contains each point. I'd like to be able to assign a PolygonID to each record in the Point table, or give it a 0 if there is no polygon that contains that point.
I'm looking for a way to do this within SQL Server (or possibly with the use of an extended stored procedure if necessary).
I have a table that contains spatial points and the name of the polygon they belong to (geography and varchar columns). I need to write a function to accept a point and determine which polygon contains that point. How can I write the query so that it will search through each polygon (derived from the geography points found in the table) and return the name of the polygon that contains that point?
I have a closed polygon that coincidently is in the shape of Iowa :) I have a point that is within the state and a point WELL outside it, but I get weird results that I don't expect when I try to get it to tell me that the point is within the polygon. Here is some basic code, with long coordinates data.
(1 row(s) affected)As I read that there is a distance of about 7864 meters, this is close to what I would expect, so that's ok. The point outside I would expect a distance as well so that is confusing.. Then we have the intersects, it says that the point inside does NOT intersect but the one outside DOES, this is backed up by the intersection values.
I am building a stored procedure that changes based on the data that is available to the query. See below. The query fails on line 24, I have the line highlighted like this. Can anyone point out any problems with the sql?
------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------ This is the error...
Msg 8114, Level 16, State 5, Procedure sp_SearchCandidatesAdvanced, Line 24
Error converting data type varchar to numeric.
------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------ This is the exec point...
EXEC [dbo].[sp_SearchCandidatesAdvanced]
@LicenseType = 4,
@PositionType = 4,
@BeginAvailableDate = '10/10/2006',
@EndAvailableDate = '10/31/2007',
@EmployerLatitude = 29.346675,
@EmployerLongitude = -89.42251,
@Radius = 50
GO
------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------ This is the STORED PROCEDURE...
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_SearchCandidatesAdvanced]
I am working on a report which requires me to draw two polygons (rectangles) on a scatter chart to visually indicate "good" and "bad" ranges of plotted data. Is there a way to do this out-of-the-box?
Otherwise, if I must create a custom report item, how do I draw on the chart surface? I have looked at the following articles on Custom Report Items, but when I open the sample solutions I can't put the custom report items on the chart surface.
First off, I appreciate the time that those of you reading and responding to this request are offering. My quesiton is a theoretical and hopefully simple one, and yet I have been unable to find an answer to it on other searches or sources.
Here's the situation. I am working with SQL Server 2005 on a Windows Server 2003 machine. I have a series of databases, all of which are in Full recovery mode, using a backup device for the full database backups and a separate device for the log backups. The full backups are run every four days during non-business hours. The log backups are run every half hour.
Last week, one of my coworkers found that some rarely-used data was unavailable, and wanted to restore a database to a point in time where the data was available. He told me that point in time was some time back in November.
To accomplish this, I restored the database (in a separate database, as to not overwrite my production database) using the Point in Time Recovery option. I selected November from the "To a point in time" window (I should note that this window is always grey, never white like most active windows, it seems), and the full database backup and the subsequent logs all became available in the "Select the backup sets to restore" window.
I then tried a bevy of different options from the "Options" screen. However, every restore succeeds (ie: it doesn't error out), but seems to be bringing the database back to a current point in time. It's never actually going back to the point in time I specify.
My questions are as follows:
a) Is it possible to do a point in time recovery to a point in time BEFORE the last full database backup?
b) If so, what options would you recommend I use? (ie: "Overwrite the existing database", restore with recovery, etc etc).
I again appreciate any and all advice I receive, and I look forward to hearing from anyone and everyone on this topic. Thank you.
I'm running SQL Server 2005 on a Server 2003 machine serving both our home network as well as a remote site through a point-to-point T1. While file transfer speeds are up to par, the remote site's interaction with SQL Server (Point of sale system) is very slow. After testing I am certain that it has nothing to do with the actual physical machine in place neither is it an issue with the program itself since speeds are as they are supposed to be over the home network lan. It seems that there might be a packet size issue or something of the sort. Has anyone dealt with this before or have any thoughts?
Can anyone please give me the equivalent tsql for sql server 2000 for the following two queries which works fine in sql server 2005
1 -- Full Table Structure
select t.object_id, t.name as 'tablename', c.name as 'columnname', y.name as 'typename', case y.namewhen 'varchar' then convert(varchar, c.max_length)when 'decimal' then convert(varchar, c.precision) + ', ' + convert(varchar, c.scale)else ''end attrib,y.*from sys.tables t, sys.columns c, sys.types ywhere t.object_id = c.object_idand t.name not in ('sysdiagrams')and c.system_type_id = y.system_type_idand c.system_type_id = y.user_type_idorder by t.name, c.column_id
2 -- PK and Index select t.name as 'tablename', i.name as 'indexname', c.name as 'columnname' , i.is_unique, i.is_primary_key, ic.is_descending_keyfrom sys.indexes i, sys.tables t, sys.index_columns ic, sys.columns cwhere t.object_id = i.object_idand t.object_id = ic.object_idand t.object_id = c.object_idand i.index_id = ic.index_idand c.column_id = ic.column_idand t.name not in ('sysdiagrams')order by t.name, i.index_id, ic.index_column_id
This sql is extracting some sort of the information about the structure of the sql server database[2005] I need a sql whihc will return the same result for sql server 2000
I normally develop for MySQL and PHP, but we have a new application that runs on MS SQL and I'm having difficulty using the same structure as MySQL to run queries. Can someone here point me to a good resource to learn the basics?
Is it possible to convert the Power Point file to SQL Server Version 7 or 2000? My PowerPoint files have no images or pictures, only lyrics for songs that are used in the church during sunday services.
I was appointed to make a system in VB6 but my first job is to convert the PowerPoint files to SQL Server. There are 2000 songs in the powerpoint right now.
Hello, thanks for reading this. I am trying to learn a bit about debveloping my own website, and I am in need of a basic direction on how to acomplish this task:
I have an local program running on my PC that creates an MS Access database export of my inventory. This export can be directly uploaded to my web server in a zip format. My web site is developed in Coldfusion MX7, and uses a MS SQL 2005 database. What I would like to do is create somewhat of an "Auto update" for my website, exporting my access database and updating the MS SQL database automatically.
I really can use some good direction on this problem. Thank you for any help in advance.
i have a DTS Package a saved when i set up the package. it's in local packages in Enterprise Manager now. is there any way i can simply set up a shortcut on my desktop to execute that DTS, rather then having to nav though Enterprise Manager?
I need to write a floating point value to a column in a SQL server database. I have the following line ofcode, that formats the float value before passing it off to the stored procedure. Please look at the line of code below. Does the 8 after SqlDbType.Foat mean that this will be a floatingpoint with 8 signigicant digits past the decimal point? mySqlCommand.Parameters.Add("@Price", SqlDbType.Float, 8).Value = float.Parse(InitialPrice);
Hi all, I want to select a column which is store email address. How to use the substring function from starting point '@' to end of string? Here is my syntax : select substring(emailaddress, [what syntax] ,len(emailaddress)) from table Thanks!
I can't see any reason for this error, not having a high level understanding of maths I thought I'd post it and hope someone could share some light on it.
I yesterday got called by a client who said that a payment for £15 + VAT was being passed to their payment gateway as 17.62 when it should be 17.63. The VAT calculation is performed in a SQL Server 2000 stored procedure. In the end I tracked it down and it wasn't a propblem with my calculation.
The price was coming out as 17.63 fine. The stored procedure then had to return this price in pence (17.63 * 100 = 17.63). When I put in a print statement with this calculation it was correct but when I output the variable that the result was assigned to it was coming out as 1762.
The variable that the result was being put into was of real datatype.
I then wrote a udf to test this. Here is the function:
CREATE FUNCTION dbo.POUNDS_TO_PENCE ( @POUNDVALUE real ) RETURNS INTEGER AS BEGIN
RETURN @POUNDVALUE * 100
END
As you can see nothing very special.
If you run this runction and pass in 17.63 it will return 1762!!!
The bit I don't get is if I change the @POUNDSVALUE intput variable to type float it returns the correct amount.
I've also found that the same problem occurs when passing in £30 + VAT (35.25) + 1pence. So, 35.26 comes out as 3525 instead of 3526. This is the case if you keep doubling the number (and adding a few pence here and there).
Does anyone know why this is or is it a bug in the processor?
The SQL books online say the following about the float and real data types:
-------------------------------------------------------- float and real (T-SQL) Approximate number data types for use with floating point numeric data. Floating point data is approximate; not all values in the data type range can be precisely represented.
Syntax float[(n)] Is a floating point number data from - 1.79E + 308 through 1.79E + 308. n is the number of bits used to store the mantissa of the float number in scientific notation and thus dictates the precision and storage size. n must be a value from 1 through 53.
n is Precision Storage size 1-24 7 digits 4 bytes 25-53 15 digits 8 bytes
The Microsoft® SQL Server™ float[(n)] data type conforms to the SQL-92 standard for all values of n from 1 to 53. The synonym for double precision is float(53).
real Floating point number data from –3.40E + 38 through 3.40E + 38. Storage size is 4 bytes. In SQL Server, the synonym for real is float(24).
I can't "point in time restore" a test DB if I had only a Full DB Backup (with overwrite option).
Example 12:00 Fullbackup new (overwrite) 12:01 update any rows 12:02 update any rows 12:03 delete any rows 12:05 Transaction Log backup (overwrite) RESTORE: We can't set "point in Time" to 12:01 od 12:02 ???
Hi, I am pretty new to Analysis Services and I have come across a small problem to do with point in time balances, I guess that this is similar shiver72's post titled 'Date Range Problem'. I am not really after a solution as such (although that would be great :-), more interested in a pointer to some resource(s) which I can read up on myself.
Here is the scenario:
A student enrols into a course that has a start and finish date. This enrolment has a certain value based upon its length, the course type etc which means that each enrolment's 'value' can vary at any time between the start and finish dates.
For example a student may enrol in a full year basket weaving course with a value of 0.0500, they then enrol in a part year course titled 'Dimensional Modelling 101' with a value of 1.000
The client wishes to know at any point in time the 'value' of their enrolments. These values can vary due to a student starting or finishing their enrolment, withdrawing, having their enrolment suspended etc, etc
Now, in the Data Warehouse Toolkit it mentions point in time balances in the 'Financial Services' section but it uses SQL to prove the point which is no use to me because I am using MS Analysis Services to dynamically create the result.
As it says in the book its no use creating a row in the fact table to represent the value for each day that the enrolment is in effect because with 400,000 enrolments that works out to over 140 million rows. The alternative is to create one row for each occurence of the variation in the value of the enrolment and then the facts would be completely additive and useful.
I would be looking at using something similar to the following:
fact table ---------- fkdate value fksudent fkcourse <possibly some degenerate measure like the UID of the course enrolment held in the source system>
dimensions ---------- Student - and of course a whole heap of related dimensions like gender, age, ethnicity etc Course
For example given the above information the fact table would look like this:
(Student UID is 2005123, course UIDs are 1000 and 1001)
That way I can run a query at, say 15-JUN-2005 and sum the values and it will come out with the correct balance - and yes I know that this idea is straight out of the book :-)
OK, if you've read to this point then I take it that you are an extremely patient person and therefore will forgive my next question.
My question is this, my clients use simple tools like Excel pivot tables to gouge the information they want out of the warehouse, sure, I can write an MDX query to get a point in time balance (just as soon as I learn a bit more about it) but is there _any_ way/means/algorithm/trick/way to hold your head to one side, that I can use so that they can still just 'drag and drop' using the pivot table service as opposed to having to me having to create an MDX-based report for them ?
Hi Guys, I wanted to know how to do a point in time recovery on sql server 2000. I wanted to go back in time when I restore the file. Can u also suggest any good book about it. Does BOL explains about it, I couldn't find it in BOL. Thanks in advance. Joe
The following SQL statement returns the correct totals except that the total value is shifted on decimal place to the right. i.e. a real total of 955.68 is displayed as 9,556.80. The total_ar field is a money type. Any help would be appreciated.
Mark
/* AR report Total Greater than 365 days sorted by Dept */
select
a.dept as `Department `, ` A/R 365+ `=sum(case when datediff(day,c.bill_date, getdate()) > 365 then b.total_ar else 0 end)
from
hbm_persnl a, blt_billm b, blt_bill c,hbl_dept d,hbm_matter e
where e.matter_uno = b.matter_uno and a.empl_uno = e.bill_empl_uno and b.bill_tran_uno = c.tran_uno and b.ar_status = `O` and e.status_code=`OPEN`
The other day I was checking the dboption and I couldnt see the "Truncate log on Check Point" option in the dboption window. Wondering if i'm looking at the right place.
Appreciated, If any one point me where can I look for this.
We have been testing point of time recovery using EM and found that this does not work. We enter date and time and do net get the logs restored. Even if we use the default date it does not work. In Query Analyser we have have managed to recover to a point in time. Anybody got any idea why EM does not work. We are using 2000 sp3
My database is in full recovery mode. When I have created some full backups of the database, I would like try to point in time restore. Unfortunately, this option is greyed out on the restore screen. What have I done wrong?
For complete database backup, I use the below syntax:
BACKUP DATABASE myDB TO DISK= @File1 WITH DESCRIPTION = @Desc
For transaction log backup, I use the below syntax: BACKUP LOG myDB TO DISK= @File1 WITH DESCRIPTION = @Desc
With this scenario, can I accomplish a point in time recovery? For example, if my database crashes on thursday night. How do i do complete recovery till that time?
I'm having a real problem with this query at the moment... Basically I have to produce a query which will tell me the total number of people employed by the company at any given date and the total salary for all these people.
We have a people table and a career table. People(unique_identifier, known_as_and_surname, start_date, termination_date ...) Career(unique_identifier, parent_identifier, career_date, basic_pay ...) Relationship people.unique_identifier = career.parent_identifier
Employees can be identified like so
SELECT * FROM people WHERE start_date <= DateSelected AND (termination_date > DateSelected OR termination_date IS NULL)
Passing the selected date to the query is no trouble at all I am just having problems with the point in time side of this.
All and any help is greatly appreciated :) ~George
Now the question is that an organization and a person both need to access the address table.
Is it better to have a PersonsAddress table and an OrganizationsAddress table, or is it better to have some sort of AddressAccess table that the persons and the organizations tables both relate to and have some sort of type field in there that specifies Org or Person?
I know NOTHING about SQL. I have a SQL 2005 database. I'm trying to restore a point in time. I get the error:
RESTORE FAILED...AMT12-2-13.TRN... CANNOT FIND THE FILE SPECIFIED.
The file exists, and in the proper location. something is wrong with it. Is there any way to rebuild the entire TRN so that a point in time recovery will work?
