Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







Great Circle Distance Calculation


 
Great Circle distance calculation
Is there any stored procedure or application that implements Great Circle distance calculation
 


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Great Circle Distance Calculation
Great Circle distance calculation
Is there any stored procedure or application that implements Great Circle distance calculation 
 

View Replies !   View Related
Great Circle Distance Function - Haversine Formula
This function computes the great circle distance in Kilometers using the Haversine formula distance calculation.

If you want it in miles, change the average radius of Earth to miles in the function.

create function dbo.F_GREAT_CIRCLE_DISTANCE
(
@Latitude1 float,
@Longitude1 float,
@Latitude2 float,
@Longitude2 float
)
returns float
as
/*
fUNCTION: F_GREAT_CIRCLE_DISTANCE

Computes the Great Circle distance in kilometers
between two points on the Earth using the
Haversine formula distance calculation.

Input Parameters:
@Longitude1 - Longitude in degrees of point 1
@Latitude1 - Latitude in degrees of point 1
@Longitude2 - Longitude in degrees of point 2
@Latitude2 - Latitude in degrees of point 2

*/
begin
declare @radius float

declare @lon1 float
declare @lon2 float
declare @lat1 float
declare @lat2 float

declare @a float
declare @distance float

-- Sets average radius of Earth in Kilometers
set @radius = 6371.0E

-- Convert degrees to radians
set @lon1 = radians( @Longitude1 )
set @lon2 = radians( @Longitude2 )
set @lat1 = radians( @Latitude1 )
set @lat2 = radians( @Latitude2 )

set @a = sqrt(square(sin((@lat2-@lat1)/2.0E)) +
(cos(@lat1) * cos(@lat2) * square(sin((@lon2-@lon1)/2.0E))) )

set @distance =
@radius * ( 2.0E *asin(case when 1.0E < @a then 1.0E else @a end ))

return @distance

end


Edit: corrected spelling


CODO ERGO SUM

View Replies !   View Related
Help W/ Distance Calculation Query
I'm trying to run a dyncamic query that returns all records within a specific distance of a certain point. The longitude and latitude of each record is stored in the database. The query is constructed from two dynamic variables $StartLatitude and $StartLongitude with represent the starting point.

SELECT UserID, ACOS(SIN($StartLatitude * PI() / 180) * SIN(Latitude * PI() / 180) + COS($StartLatitude * PI() / 180) * COS(Latitude * PI() / 180) * COS(($StartLongitude - Longitude) * PI() / 180)) * 180 / PI() * 60 * 1.1515 AS Distance
FROM HPN_Painters
HAVING (Distance <= 150)

It runs fine until I add the 'HAVING (Distance <= 150)' clause, in which I recieve the error: Invalid column name 'Distance' It seems that Distance cannot be referenced in the HAVING clause.

View Replies !   View Related
Job Fails, How Do I Reset Circle With Red X
When a job fails, the right pane of EM for Jobs show a circle with a red X to the left
of the job. Thus making it easy to find problems.

Is there a way to reset the job to a status that eliminates the circle with the red X,
without successfully reruning the job?

Thanks.

View Replies !   View Related
Converting Oracle Calculation To Sql Server 2005 Calculation
Hi I am having to convert some oracle reports to Reporting Services. Where I am having difficulty is with the

calculations.

Oracle

TO_DATE(TO_CHAR(Visit Date+Visit Time/24/60/60,'DD-Mon-YYYY HH24:MISS'),'DD-Mon-YYYY HH24:MISS')

 

this is a sfar as I have got  with the sql version

SQLSERVER2005

= DateAdd("s",Fields!VISIT_DATE.Value,Fields!VISIT_TIME.Value246060 )

 

visit_date is date datatype visit_time is number datatype. have removed : from MI(here)SS as was showing as smiley.

 

using:

VS 2005 BI Tools

SQLServer 2005

 

 

View Replies !   View Related
SQL Server - Green Circle With White Arrow Question
I know that this may be a stupid question but I have yet to find the exact answer. Within the SSMS the servers are displayed in the registered servers. I register the server and then connect successfully. What I don't understand is although the server is running or the sql server agent is running the green dot with the white arrow is completely white. I can click on the server and look at the databases so I know that the server is running. This happens on remote servers and servers here at our location. Does anyone know why the white dot is not green with a white arrow? I'm just curious to know why this is like that. Thanks for any help you can provide.
 

View Replies !   View Related
Nearest Distance
Hi
How do I get a nearest distance of a point? For example, I have two tables A and B and I want to find the nearest distance between the records of the two tables. In addition, one of the tables should also give me the distance. The data I have geo spatial data. Can this be done in SQL
Help will be appreciated

View Replies !   View Related
Clustering Distance
Hi !

My question may sound silly but I am new to data mining so I do not know much. Can I define a custom distance measure for a clustering algorithm ie. k-means ? I have created some clr utds and I want to try a clustering algorithm on them. Can I do this? How is the distance calculated ?

Sorry for my poor english.
Thank
ST.

View Replies !   View Related
Edit Distance
Hi,
please, it is possible to know the edit distance used in the fuzzy lookup/grouping.
On this forum I read fuzzy lookup use 4-gram with fix size.
Does exist any document explaining how fuzzy lookup calculate the similarity? In other word, what kind of edit distance, algorithm is used by fuzzy lookup/grouping?
I hope I was enough clear with my poor english.
Thanks All

View Replies !   View Related
Mirroring Over Distance????
Is there a recommended practice for mirroring in regards to distance?  Is it best practice to mirror with both nodes at the same physical location and use another method for failing over to a remote location or can one just put the other node in the mirror a few thousand miles away?  I'm suspecting not. 

Any comments??

View Replies !   View Related
Distance Between Postal Codes
I'm looking to find out how I'd go about setting up a database where avisitor to my site could punch in their postal code, and find out how farthey are from another postal code. For example, AutoTrader has this featureI believe to tell you how far the vehicle is from you. Dating sites havethem so you can do proximity searches.Anyone have any ideas where I could start? I'm thinking the post office,but if anyone else has suggestions, I'm open to hear them.Thanks!

View Replies !   View Related
Cluster Euclidean Distance
I am new to data mining so please excuse my ignorance. Lets assume

- i have created a cluser model

- identified 3 clusters ( a, b, c)

- each record consists of 15 columns

- collecting new records( 15 variables) real time

what i would like to do is  plot these new records programmatically as i collect them realtime. I assume this new record will belong to one of these three clusters. I believe we can find the cluster this new record belongs to by ' SELECT Cluster()....' and distance from the center of the cluster by ClusterDistance(). To plot this on a 2-dimentional space i need (x, y).

ClusterDistance() could be Y but what will be X.

 

thanks.

View Replies !   View Related
Database Mirrors Over Distance
Various posts have noted that mirroring over distance is not advisable or that either async connections should be used.

Are there any limits/recommendations i.e. if two datacenters are a couple of files part with 10GBs fibre links and <50ms response times would this be acceptable for high-availability mirroring?

 

View Replies !   View Related
Distance Between Two Points Lat/long
I have a user defined function, I want to determine the distance between the 2 points. I have it working but i'm having a problem getting to print.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++




Code Snippetcreate function dbo.Distance( @lat1 float , @long1 float , @lat2 float , @long2 float)
returns float

as

begin

declare @DegToRad as float
declare @Ans as float
declare @Miles as float

set @DegToRad = 57.29577951
set @Ans = 0
set @Miles = 0

if @lat1 is null or @lat1 = 0 or @long1 is null or @long1 = 0 or @lat2 is
null or @lat2 = 0 or @long2 is null or @long2 = 0

begin

return ( @Miles )

end

set @Ans = SIN(@lat1 / @DegToRad) * SIN(@lat2 / @DegToRad) + COS(@lat1 / @DegToRad ) * COS( @lat2 / @DegToRad ) * COS(ABS(@long2 - @long1 )/@DegToRad)

set @Miles = 3959 * ATAN(SQRT(1 - SQUARE(@Ans)) / @Ans)

set @Miles = CEILING(@Miles)

return ( @Miles )

end

DECLARE @RC float
EXEC Distance '39.943762', '-78.122265', '32.334709', '-96.633546'
PRINT @RC /* in miles */
 
 

View Replies !   View Related
Haversine SQL Trouble - Distance Between Zip Codes
I am trying to use the haversine function to find the distance betweentwo points on a sphere, specifically two zip codes in my database. I'mneither horribly familiar with SQL syntax nor math equations :), so Iwas hoping I could get some help. Below is what I'm using and it is,as best as I can figure, the correct formula. It is not however,giving me correct results. Some are close, others don't seem right atall. Any ideas?SET @lat1 = RADIANS(@lat1)SET @log1 = RADIANS(@log1)SET @lat2 = RADIANS(@lat2)SET @log2 = RADIANS(@log2)SET @Dlat = ABS(@lat2 - @lat1)SET @Dlog = ABS(@log2 - @log1)SET @R = 3956 /*Approximate radius of earth in miles*/SET @A = SQUARE(SIN(@Dlat/2)) + COS(@lat1) * COS(@lat2) *SQUARE(SIN(@Dlog/2))SET @C = 2 * ATN2(SQRT(@A), SQRT(1 - @A))/*SET @C = 2 * ASIN(min(SQRT(@A))) Alternative calculation*/SET @distance = @R * @Cthnx,cjrsumner

View Replies !   View Related
Levenshtein Edit Distance Algorithm
See here www.merriampark.com/ld.htm for information about the algorithm. This page has a link (http://www.merriampark.com/ldtsql.htm) to a T-SQL implementation by Joseph Gama: unfortunately, that function doesn't work. There is a debugged version in the also-referenced package of TSQL functions (http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=502&lngWId=5), but this still has the fundamental problem that it only works on pairs of strings up to 49 characters.


CREATE FUNCTION edit_distance(@s1 nvarchar(3999), @s2 nvarchar(3999))
RETURNS int
AS
BEGIN
DECLARE @s1_len int, @s2_len int, @i int, @j int, @s1_char nchar, @c int, @c_temp int,
@cv0 varbinary(8000), @cv1 varbinary(8000)
SELECT @s1_len = LEN(@s1), @s2_len = LEN(@s2), @cv1 = 0x0000, @j = 1, @i = 1, @c = 0
WHILE @j <= @s2_len
SELECT @cv1 = @cv1 + CAST(@j AS binary(2)), @j = @j + 1
WHILE @i <= @s1_len
BEGIN
SELECT @s1_char = SUBSTRING(@s1, @i, 1), @c = @i, @cv0 = CAST(@i AS binary(2)), @j = 1
WHILE @j <= @s2_len
BEGIN
SET @c = @c + 1
SET @c_temp = CAST(SUBSTRING(@cv1, @j+@j-1, 2) AS int) +
CASE WHEN @s1_char = SUBSTRING(@s2, @j, 1) THEN 0 ELSE 1 END
IF @c > @c_temp SET @c = @c_temp
SET @c_temp = CAST(SUBSTRING(@cv1, @j+@j+1, 2) AS int)+1
IF @c > @c_temp SET @c = @c_temp
SELECT @cv0 = @cv0 + CAST(@c AS binary(2)), @j = @j + 1
END
SELECT @cv1 = @cv0, @i = @i + 1
END
RETURN @c
END

View Replies !   View Related
Calculating Distance Based On Latitude And Longitude
I need to be able to take the latitude and logitude of two locations and compare then to determine the number of miles between each point.  It doesn't need to account for elevation, but assumes a flat plane with lat and long.

Does anyone have any algorithms in T-SQL to do this?

View Replies !   View Related
How To Control The Distance Between The Two Matrix? Or (matirx And Table )
hi  everyone:

the  report  show  two tables   two matrixs

how  can  i  control   the distance between them

I  want  to  set  the  same  distance between the table  and  matrix

or  (table and  table )

 

 

 

View Replies !   View Related
Any Distance Limited For Failover Clustering Solution?
Could I implement a failover cluster solution on the two DBs which are based in two different cities?
Possible?

View Replies !   View Related
How To I Enter A Small Square Or A Small Circle In A NVARCHAR Field?
I want to store a small cirle in a text field. Can anyone tell me how I can enter it in ascii code.

Thanks

View Replies !   View Related
Stored Procedure To Retrieve Zipcodes Within A Specified Zipcode And Distance
Hi All,
Does anyone have a Stored Procedure that works perfectly to retrieve all zipcodes within a specified zipcode and distance radius - a zipcode and radius is passed and the Store Procedure result shows all zipcodes that falls within that range.
 
Thanks in advance
 
Ade

View Replies !   View Related
Need Great Help
hi, and I appreciate your help.
I have the following table:

id shipmentid cust_id invoice# amount shipmentid vendorid invoice#amount
1 610 891 109012 22594.00 610 1586 17601 392.70
2 610 891 109012 22594.00 610 1586 17600 419.40
3 610 891 109012 22594.00 610 1586 17599 396.45

This table contains invoices for both customers and vendors. Both invoices have $amounts . The customer invoices are duplicates which resuts in duplicate amount. How can I remove the duplicate amount from the customer side and keep a distinc invoice for customers .

thanks for your help

Ali

Ali

View Replies !   View Related
SQL Hell....little Help Would Be Great!
hi, im currently on sql hell right now. im having a hard time learning this sql thingie....

...the thing is this: im currently using the book ASP.NET Unleashed and most of the examples there are on SQL. what i was trying to do before was convert everything to OleDb to fit the ms access which i have right now.

unfortunately, some of the codes seem not to work properly. maybe its because of im using OleDb...

so what i did was i downloaded the MSDE sp3 package and installed it on my PC. now that i have an sql server for my WebMatrix, i just dont know what to do next? i mean, where do i put the sql sample databases like northwind and pubs???

im really confused about this sql thing. i really hate it.

help!!!

View Replies !   View Related
Great Job Board
There is a great job board located at the employment section ofhttp://www.4charlesson.com . So pass it on to anyone looking for a job.

View Replies !   View Related
Great New Application
http://www.iqub.comThis is a wicked program . great price too ... Check it out....IQub Browser Professional Edition is a feature-rich applicationproviding online multidimensional analysis. By connecting directly toMicrosoft's SQL Server MS-OLAP/Analysis Server cubes, or to localcubes, you can explore large volumes of summarized data quickly andefficiently. With Professional Edition you can create local cubecopies from MS-OLAP/Analysis Server cubes, perfect for company widedistribution or taking on the road.CheersChris

View Replies !   View Related
Great MS SQL Sites?
Does anyone know of any links to some great MS SQL sites I can check out to learn from?

Thanks for your thoughts.

Sincerely,

Tim

View Replies !   View Related
Great Oppurtunity
hello
Great oppurtunity for MCDBA candidates with 10+ years of exp.
OLAP and .NET exp. is big+
Its a long term project.pls send ur resume in a doc format.

thanks
bala

View Replies !   View Related
Morning - Any Help Would Be Great
I have two servers both with different collation. Server A being SQL_Latin1_General_CP1_CI_AS and the live server and Server B being Latin1_General_CI_AS and a dev server. Now i have a load of data on the dev which i'm query to see if its on the live server.
select * from ServerA.Table1 where Col1 in
(select Col1 from ServerB.Table1)

I get this Error message.
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

View Replies !   View Related
TARA Is Great
I am SQL server DBA for 4 years. ALways i am getting help from TARA and other SQL gurus. Salute to all

View Replies !   View Related
Great Plains
Has anyone found or know of a site or place where there is a community of Great Plains SRS Report writers.

At Convergence people spoke of a place to share and help each other.

 

Thanks

Eric

View Replies !   View Related
Great Plains V8
Hope someone can solve my problem

I have a working version of the above installed on a win 2000 machine with SQL ver (MSDE2000A.exe)


I purchase a laptop with vista home premium installed on it


MSDE2000A.exe would not install hence I installed SQL Server Express I managed to install the server and connect but when trying to login through Great Plains it says that I need SQL version 7. I have done a little research and found that SQL 7 is not supported by Vista.

Which way do I go now ?????


Do I install a copy of xp, update Great Plains or wait for an updated version of SQL Server Express SP ???


Please can anyone help !!!
and please dont get too technical Im not an expert


Many Thanks
Mr Magoo

View Replies !   View Related
Sql 2005 Full-Text Based Web Searches With Levenstein Edit Distance And Double Metaphone Matches
Hi,
 
Is it possible to incorporate levenshtein edit distance and double-metaphone matching into Sql Server 2005's Full-Text Search queries using FREETEXTTABLE or CONTAINSTABLE?
 
I'm working on a web-site that allows user to search multiple tables, and i want the ability to pull back rows where the spelling is slightly out, or contains words that sound like those provided in the query, a bit like Google.
 
Also, I'd like to rank the results so that exact matches are returned at the top of the result-set, and nearest-match or phonetic matches lower down the list.
 
I've come across UDF's that provide these features but they don't appear to be compatible with the FTS engine. I've also heard of third-party alternatives to the FTS engine that integrate with Sql Server but I can't remember what they're called.
 
Any suggestions?
 
Ben

View Replies !   View Related
Help Me!! About Sqlserver2000 Function ! Thanks Great!!
i want to select a <img> tab from  a sqlserver2000  ntext field  and result like follow:http://app.zd168.com/recordpic/200403/25874/attrcis4/DSCF0007.jpgI declare a sqlserver2000 function :create function SelectImgTag(@c nvarchar(4000))returns nvarchar(2000)asbegindeclare @p int declare @PP intset @p= charindex('<img',@c,0)select @pp=charindex('>',@c,@p)select  @c=substring(@c,@p,@pp)select @p= charindex('<img',@c,0)select @pp=charindex('>',@c,@p)select  @c=substring(@c,@p,@pp)set @p= charindex('src=',@c,0)select @pp=charindex('G"',@c,@p) select @pp=@pp-10If (@p<len(@c)-5)  select @p=@p+5ElseReturn NULLIf (@PP<Len(@C)) beginif (@pp-@p>5) select  @c=substring(@c,@p,@pp)elsereturn nullENDreturn(@c)endsome time it can result my wan like  follow:http://app.zd168.com/recordpic/200403/25874/attrcis4/DSCF0007.jpgbut some time it result like follow:http://app.zd168.com/recordpic/200506/48677/attrcis4/%CE%B4%C3%FC%C3%FB.jpg" width=672this's out my want.ho~who can help me to correct my function ???thanks great!!

View Replies !   View Related
Great News Blog!
http://www.newsblog2005.blogspot.com << Great News Blog!

View Replies !   View Related
I Found This Great Little Site
I found this great little site. I signed up two weeks ago and got 2 Disney tickets and this week they are sending me 2 Universal Studios tickets. Here's the link http://66.219.102.40/ and by the way I am a real person, this is my real email address. I'm not a spammer and didn't appreciate the nasty email I got last time I tried to post this link.Thanks, Lisahttp://66.219.102.40/http://66.219.102.40/

View Replies !   View Related
Where&#39;s That Great &#34;SQL As Listserv&#34; Article?
A while back, somebody wrote a great article on how to wrangle SQL into being a sort of Listserv but I cannot find it on Swynk or 15 seconds. (I could swear it was on either of those.) Of course, if Swynk's search page wouldn't timeout, I may have better luck.

<ignore>
<i>Random</i> validation test.
</ignore>

View Replies !   View Related
Great Plains Deployment
Just deployed great plains, trying to get into the login for the utilities, keeps saying that I have the wrong account or ID, anyone run into this issue before? I have treid all ID's, all administrator combos, everything. Its the default rollout, all I did was install the prog

View Replies !   View Related
Installing Great Plains V8.0
Here is my problem.

I am setting up a new server with Great Plains v8.0 (I have migrated all the databases from the other server, which has a working version of Great Plains v8.0 at the present moment). We are planning on getting rid of that server as it causing a lot of problems at the moment. I start the install of Great Plains v8.0 on the new server, install the components. After Great Plains v8.0 installs I go into Great Plains Utilities, and get the following message (I have installed SQL Server 2005 on the new server, and SQL Server 2000 is being used on the old server).

After I run Great Plains Utilities, I get the following error message -
"The stored procedure verifyServerVersion() of form duSQLinstall: 111
Pass Through SQL returned the following results: DBMS: 0, Great Plains: 0."

and when I click OK, it gives me another notification message -
"Your current SQL SERVER is not a support version

Req: Microsoft SQL Server 7.0
Act: Microsoft Server 2005

You need to upgrade to SQL Server 7.0 before continuing".

Why am I get that error message when I have SQL Server 2005 installed?

Can someone point me in the right direction.

Thanks

View Replies !   View Related
Help With Great Plains Query
Greetings,

We currently use GP 8.00 with the SQL Server 8.0.

We are trying to develop a view based on manfacturing orders - when a finished good is placed on hold and then calculating the componet parts that are on hold.

The calculation runs fine, we multiply end quanity x componet quanity found on the BOM. But we are not getting the correct componet item numbers to display.

Here is the syntax we are using:

SELECT DISTINCT
TOP 100 dbo.BM010115.QUANTITY_I, dbo.WO010032.ENDQTY_I, dbo.WO010032.ENDQTY_I * dbo.BM010115.QUANTITY_I AS QTY_REQHOLDMO,
dbo.BM010115.CPN_I, dbo.WO010032.MANUFACTUREORDER_I, dbo.WO010032.ITEMNMBR, dbo.WO010032.MANUFACTUREORDERST_I
FROM dbo.TEC_MOSumm RIGHT OUTER JOIN
dbo.WO010032 ON dbo.TEC_MOSumm.ITEMNMBR = dbo.WO010032.ITEMNMBR RIGHT OUTER JOIN
dbo.BM010115 ON dbo.WO010032.BOMCAT_I = dbo.BM010115.BOMCAT_I
WHERE (dbo.WO010032.MANUFACTUREORDERST_I = 4)

We have tried several different types of joins but still no luck. The upshot is we need to know that when a finished good is placed on hold, - how many of its componet parts are placed on hold.

Any assistance you can provide will be appreciated.

Sam

View Replies !   View Related
Got Some Great Replication Links????
can anybody give me some nice links with a detailed explaination of the various replication errors and solution

 

 

thanks in advance

Jacx

View Replies !   View Related
Great New Learning Resource
 We've been working hard with our teams here to get better/more/good information out to our users. We€™ve created a new a customized Windows Live search, that limits results to Books Online. Check this out, and make sure you let your contacts know to visit it and provide feedback:
http://search.live.com/macros/sql_server_user_education/booksonline
 
We want to generate as much traffic as possible here so that we can see if this is useful to our users. We€™d love to hear back from everyone we can!

View Replies !   View Related
Anyone Know Of A Great Book For Learning Transact SQL?
Hello, I have just started working with Microsoft SQL Server 7.0 and was wondering if anyone could recommend a great book for learning T-SQL. I was looking for something that would provide several examples on triggers and stored procedures.

View Replies !   View Related
Does Anyone Know Of A Great Access Help Site Similar To This.
Does any one have information on any good Access Sites that are similar to this one, which could be helpful in completing some task. Thanks.

View Replies !   View Related
Great Plains Database Replication
Our company has a database server (Windows Server 2003 x64 EE w/ SQL Server 2005 x64 EE) that has Great Plains installed.  We have a database for one of our companies that I would like to begin replicating to another server.  The reason behind this is that we want to run the GP/CRM Connector which requires 32-bit ODBC connections.

Anyways, what I need to do is enable replication to our 32-bit server.  We have been successful in making this happen from 64-bit to 32-bit with a much smaller database.  When I go to enable peer-to-peer in the properties of the publication (which I set up using all of the default settings), SQL Management Studio will hang and run for days while appearing to be working.  However, when we end up killing Management Studio days later (we let it run for a week once) the peer-to-peer option does not seem to have been set properly.

Any ideas?

View Replies !   View Related
Replication Of Great Plains Data
Hi All,

We have the requirement to replicate financial data to Aus from the UK, however I dont know if Replication is the best solution around?

Reason why I ask this, is that to create the publication, there are in excess of 10000 articles, which takes forever and a day to create, then when setting up the push subscription, this takes equally as long.

DB's physically range between 100MB and 2GB. Link to Aus is 2MB E1.

The accounts server isnt the most powerful of beasts (HP DL380, 1x1.4Ghx CPU) and with 4 DB's to setup and replicate, it's going to take some time.

With this in mind, I would also be looking to script out the publication should there be any failures and put it into SourceSafe, however this would also take a vast amount of time.

I've thought about using Log Shipping, however I dont know if there are any better ways
?

Thoughts appreciated.

Steve

View Replies !   View Related
Great Tool To Compare Data From Two Different Queries
http://www.download.com/Firefly-Dat...j=uo&tag=button

View Replies !   View Related
MS SQL/Great Plains Server Utilization Nightmare!
1. Issue started popping up a month ago.
2. Microsoft says its a "network issue", and that we need to update our LAN infrastructure to Gbit ethernet.
3. The scenario:

1. Great Plains/MS SQL running on a dual Xeon w. 6GB onboard memory.
2. 30-40 users accessing GP @ any given time.
3. Server utilization (memory at about 1.9 for the OS & other processes/ MSSQL session taking about 1.7-1.9GB, so I still have a comfortable leeway of about 2GB)
4. Issues of server utilization spikes happens when running certain number of batches of report queries. Users created special forms using the VBA (Visual Basic for Applications) IDE. The other 30 or so users begin to timeout/loose db connectivity/and or sessions die during this process.
5. Not sure if DPS (Distributed Process Server) is being run to spread the workload on other workstations on the network. The forcefulness of Microsoft to say this is a network traffic/bandwidth utilization issues makes me think they are employing DPS for this application.

My questions are:

1. Could those customized VBA scripted reports be generating unecessary queries to tables due to sloppy coding, hence causing SQL to spike in cpu/memory utilization? Could there be dependencies hidden to the scripters that are kicking off that are hidden to them? Could certain stored procedures be kicking off due to hidden/unknown dependencies required in order to generate the reports being requested? Is there a log that shows these hidden dependencies?

2. If DPS is being employed to distribute the process workload for these jobs, that would cause traffic storms, even on a 10/100 switched network? If I don't have a layer 3 switch, is there a way to sniff out this traffic/strip the packets to view/ferret out this traffic generated?

3. We can run this report directly on the server, so this lends me to believe that this is not traffic generated between the SQL/GP server to the client, but a direct spike in server utilization, causing SQL/GP server response time to all the other clients to decrease, hence causing lost sessions/timeouts to the rest of the clients. Does this make sense?

4. Are there SQL performance tweaks we can make to optimize the performance/response time of SQL/GP, even under the extreme report generation/posting procedures? Is DPS the only way to go to relieve the workload on the SQL/GP server?

5. Now, the users also mentioned some "patches" that were made to the GP app, in order to repair some corrupted files. Unclear on that one.

Thanks in advance for your help!

View Replies !   View Related

Copyright © 2005-08 www.BigResource.com, All rights reserved