Having problems creating a function or procedure

When I try to create functions or procedures sqlplus just hangs.
I have tried creating several function and procedures written in the
developing pl/sql manual. The 1st procedure I created worked. All the others will not compile. Any ideas on the problem?
ReneeHello,

could it be, that the procedures is still in creation status by another process - or it is running ?

Just an idea

Regards
Manfred Peter
(Alligator Company)
http://www.alligatorsql.com

Having a MAJOR brain fart here...

Guys I'm sorry to be asking such a routine question...

I'm having trouble figuring out how to make this function dynamic enough to handle multiple insert statements.

1public int Add()23{45string SQL;67SQL ="INSERT INTO [BuildingInterior] (PropertyID, CeilingHeight, " +89"LoadingDocks, PassengerElevators, FreightElevators, PassengerEscalators, " +1011"FireSprinklersID, SecurityCameras, SmokeDetection, FireAlarms, " +1213"GasDetection, SecureAccess, HeatTypeID, AirConditioningID, " +1415"AirExchange, InternetAccessID, InteriorDescription) " +1617"VALUES ( @.PropertyID, @.CeilingHeight, " +1819"@.LoadingDocks, @.PassengerElevators, @.FreightElevators, @.PassengerEscalators, " +2021"@.FireSprinklersID, @.SecurityCameras, @.SmokeDetection, @.FireAlarms, " +2223"@.GasDetection, @.SecureAccess, @.HeatTypeID, @.AirConditioningID, " +2425"@.AirExchange, @.InternetAccessID, @.InteriorDescription)";2627PropertyDB myConnection =new PropertyDB();2829SqlConnection conn = myConnection.GetOpenConnection();3031SqlCommand cmd =new SqlCommand(SQL, conn);3233cmd.Parameters.Add("@.PropertyID", SqlDbType.Int).Value = PropertyID;3435cmd.Parameters.Add("@.CeilingHeight", SqlDbType.NVarChar, 50).Value = CeilingHeight;3637cmd.Parameters.Add("@.LoadingDocks", SqlDbType.NVarChar, 50).Value = LoadingDocks;3839cmd.Parameters.Add("@.PassengerElevators", SqlDbType.NVarChar, 50).Value = PassengerElevators;4041cmd.Parameters.Add("@.FreightElevators", SqlDbType.NVarChar, 50).Value = FreightElevators;4243cmd.Parameters.Add("@.PassengerEscalators", SqlDbType.NVarChar, 50).Value = PassengerEscalators;4445cmd.Parameters.Add("@.FireSprinklersID", SqlDbType.Int).Value = FireSprinklersID;4647cmd.Parameters.Add("@.SecurityCameras", SqlDbType.NVarChar, 50).Value = SecurityCameras;4849cmd.Parameters.Add("@.SecurityAlarms", SqlDbType.NVarChar, 50).Value = SecurityAlarms;5051cmd.Parameters.Add("@.SmokeDetection", SqlDbType.NVarChar, 50).Value = SmokeDetection;5253cmd.Parameters.Add("@.FireAlarms", SqlDbType.NVarChar, 50).Value = FireAlarms;5455cmd.Parameters.Add("@.GasDetection", SqlDbType.NVarChar, 50).Value = GasDetection;5657cmd.Parameters.Add("@.SecureAccess", SqlDbType.NVarChar, 50).Value = SecureAccess;5859cmd.Parameters.Add("@.HeatTypeID", SqlDbType.Int).Value = HeatTypeID;6061cmd.Parameters.Add("@.AirConditioningID", SqlDbType.Int).Value = AirConditioningID;6263cmd.Parameters.Add("@.AirExchange", SqlDbType.NVarChar, 50).Value = AirExchange;6465cmd.Parameters.Add("@.InternetAccessID", SqlDbType.Int).Value = InternetAccessID;6667cmd.Parameters.Add("@.InteriorDescription", SqlDbType.NVarChar, 50).Value = InteriorDescription;6869cmd.ExecuteNonQuery();7071cmd.CommandText ="SELECT @.@.IDENTITY";7273this.BuildingInteriorID = Int32.Parse(cmd.ExecuteScalar().ToString());7475conn.Close();7677return this.BuildingInteriorID;7879}80

Should I just pass an array of column names and use the AddWithValues SqlCommand method while looping through the array?

Any comments are greatly welcomed.

Hi eterry,

As far as I can see there is no better way to assign value by iterating through each column like this.

But I think there is one thing that you can improve in your code. You can put SELECT SCOPE_IDENTITY() (use SCOPE_IDENTITY() instead of @.@.IDENTITY in SQL Server)at the end of the INSERT statement. Seperate them with a ";", like

INSERT INTO ......; SELECT SCOPE_IDENTITY()

Then you can use ExecuteScalar to have the 2 statement executed at once. This will have 2 advantages.

1. Save a roundtrip to the server and gain better performance.
2. Prevent the concurrency issues. In your code, if 2 users do this together, there is possibility that they will get wrong identity if one's execution is interrupted by the other.

HTH. If this does not answer you question, please feel free to mark the post as Not Answered and post your reply. Thanks!

|||

Thanks for the tip Kevin.

I was hoping that there would be a better way of doing, but it is what it is.

Haversine SQL trouble - Distance between zip codes

I am trying to use the haversine function to find the distance between
two points on a sphere, specifically two zip codes in my database. I'm
neither horribly familiar with SQL syntax nor math equations :), so I
was 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 at
all. 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 * @.C

thnx,
cjrsumnerchristian@.adminconsole.com (csumner) wrote in message news:<32458ea2.0402261803.1e72c1ef@.posting.google.com>...
> I am trying to use the haversine function to find the distance between
> two points on a sphere, specifically two zip codes in my database. I'm
> neither horribly familiar with SQL syntax nor math equations :), so I
> was 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 at
> all. 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 * @.C
>
> thnx,
> cjrsumner

It would help if you could post your DECLARE statments (different data
types can affect calculations in various ways), as well as some sample
data for cases which give the results you want and for cases which
don't.

Simon|||>> I am trying to use the haversine function to find the distance
between two points on a sphere, specifically two zip codes in my
database. <<

Do not re-invent (and have to maintain!!!) the wheel. KJL Software
(www.kjlsoftware.com) gives you 5-digit ZIP Code, City, State,USPS
Status Code, LATEST Area Code(s) from NANPA, Time Zone,Latitude, and
Longitude for all valid US Postal Service 5 digit ZIP Codes/City/State
combinations and a Distance Calculator.|||Just for the record and in SQL/PSM:

CREATE FUNCTION Distance
(IN latitude1 REAL, IN longitude1 REAL,
IN latitude2 REAL, IN longitude2 REAL)
RETURNS REAL
AS
BEGIN
DECLARE r REAL;
DECLARE lat REAL;
DECLARE lon REAL;
DECLARE a REAL;
DECLARE c REAL;
SET r = 6367.00 * 0.6214;

-- calculate the Deltas...
SET lon = longitude2 - longitude1;
SET lat = latitude2 - latitude1;

--Intermediate values...
SET a = SIN(lat / 2) + COS(latitude1)
* COS(latitude2) * SIN(lon / 2)

--Intermediate result c is the great circle distance in radians...
SET c = 2 * ARCSIN(LEAST(1.00, SQRT(a)))

--Multiply the radians by the radius to get the distance
RETURN (r * c)
END;

LEAST() function protects against possible roundoff errors that could
sabotage computation of the ARCSIN() if the two points are very nearly
antipodal. It exists as a vendor extension in Oracle, but can be
written with a CASE expression in Standard SQL.|||Okay, below is the whole function. Here are some values and their
results compared to zipfind.net. Note the strange behavior for 32610 and
32611, and others had this same value too.

Search for 10 mile radius of 32601 and get distance between zip codes
(This post is only concerned with the distance function part)

my results (sample):
zip code miles latitude longitude
32601 0.0 29.68040999998-82.345738999999995
32602 4.633365229.629887-82.396567000000005
32604 8.031078329.573293-82.397903999999997
32610 0.4907090429.68131199998-82.353862000000007
32611 0.4907090429.68131199998-82.353862000000007
...

zipfind.com results:
zip code miles
32601 0.0
32602 5.5
32604 1.5
32610 1.3
32611 0.2
...

CREATE FUNCTION dbo.GetDistance(
@.lat1 Float(8),
@.log1 Float(8),
@.lat2 Float(8),
@.log2 Float(8)
)
RETURNS Float(8)
AS
BEGIN

DECLARE @.distance Float(8)
DECLARE @.R int
DECLARE @.Dlog Float(8)
DECLARE @.Dlat Float(8)
DECLARE @.A Float(8)
DECLARE @.C FLoat(8)

SET @.lat1 = RADIANS(@.lat1)
SET @.lat2 = RADIANS(@.lat2)
SET @.log1 = RADIANS(@.log1)
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 * @.C

RETURN @.distance
END
GO

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||I dont' understand this: How can these three zip code finding website
have such drastically different results?:
Are their databases so different? This first one doesn't quite match up
with my lat/log values.

www.kjlsoftware.com
32601 GAINESVILLE FL AD 352 -5 29.653195 -82.3244 0
32602 GAINESVILLE FL AD 352 -5 29.665245 -82.336097 1.0884
32603 GAINESVILLE FL AD 352 -5 29.653145 -82.346901 1.3501
32604 GAINESVILLE FL AD 352 -5 29.665245 -82.336097 1.0884
32605 GAINESVILLE FL AD 352 -5 29.676006 -82.368897 3.0994
32606 GAINESVILLE FL AD 352 -5 29.681426 -82.415022 5.7754
32607 GAINESVILLE FL AD 352 -5 29.646189 -82.396588 4.3583
32608 GAINESVILLE FL AD 352 -5 29.611545 -82.394108 5.0763

http://zipfind.net
1 32601 Gainesville FL 17,760 0.0 Alachua 352 Eastern
2 32602 Gainesville FL 0 5.5 Alachua 352 Eastern
3 32603 Gainesville FL 10,034 1.4 Alachua 352 Eastern
4 32604 Gainesville FL 0 1.5 Alachua 352 Eastern
5 32605 Gainesville FL 21,539 3.3 Alachua 352 Eastern
6 32606 Gainesville FL 19,662 6.3 Alachua 352 Eastern
7 32607 Gainesville FL 26,666 4.8 Alachua 352 Eastern
8 32608 Gainesville FL 39,781 4.9 Alachua 352 Eastern

and http://www.cryptnet.net/fsp/zipdy/ gives different results too.

These results seem to be off too far to be accounted for with simple
rounding errors and the like. Anyone know what's going on here?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||thnx, ill try this out

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||>> These results seem to be off too far to be accounted for with simple
rounding errors and the like. Anyone know what's going on here? <<

Nope, but perhaps one uses the location of the post office that serves
the zipcode and the other uses a map with the centroid of the territory?

Another thought is that if we want the for mailing purposes, you can get
a table of zones for each zip code.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

hash?

Hi all,
Are there any hash function build in on SQL 2000?
or the SQL 2003?
Thx.
Rena.
Yes, check out CHECKSUM and BINARY_CHECKSUM in BOL.
"Rena" <rena@.mail.hongkong.com> wrote in message
news:Oroy%23C4jEHA.3712@.TK2MSFTNGP15.phx.gbl...
> Hi all,
> Are there any hash function build in on SQL 2000?
> or the SQL 2003?
> Thx.
> Rena.
>

hash?

Hi all,
Are there any hash function build in on SQL 2000?
or the SQL 2003?
Thx.
Rena.Yes, check out CHECKSUM and BINARY_CHECKSUM in BOL.
"Rena" <rena@.mail.hongkong.com> wrote in message
news:Oroy%23C4jEHA.3712@.TK2MSFTNGP15.phx.gbl...
> Hi all,
> Are there any hash function build in on SQL 2000?
> or the SQL 2003?
> Thx.
> Rena.
>

hash?

Hi all,
Are there any hash function build in on SQL 2000?
or the SQL 2003?
Thx.
Rena.Yes, check out CHECKSUM and BINARY_CHECKSUM in BOL.
"Rena" <rena@.mail.hongkong.com> wrote in message
news:Oroy%23C4jEHA.3712@.TK2MSFTNGP15.phx.gbl...
> Hi all,
> Are there any hash function build in on SQL 2000?
> or the SQL 2003?
> Thx.
> Rena.
>

HAS_DBACCESS for any user name

Hi All,
I need function looks like HAS_DBACCESS for any user name.
How can I check has user access to db? As a user or as a member of group.
Thanks.See whether this NG thread helps.
http://tinyurl.com/88jl2
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Oleg Cherkasenko" <oleg@.opel.com.ua> wrote in message
news:OMIZzsHrFHA.528@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I need function looks like HAS_DBACCESS for any user name.
> How can I check has user access to db? As a user or as a member of group.
> Thanks.
>
>|||Thanksm its useful.
The hard case is:
I have a windows user 'user1' which is a member of windows group 'group1'.
In SQL Server I added this group 'group1' only but not 'user1'.
'user1' has a db access via memebership in 'group1', but I cannot find this
user name in sql server tables and procedures.
Its a question.
Regards.
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:evwZvzHrFHA.908@.tk2msftngp13.phx.gbl...
> See whether this NG thread helps.
> http://tinyurl.com/88jl2
> --
> Roji. P. Thomas

> Net Asset Management
> http://toponewithties.blogspot.com
>
> "Oleg Cherkasenko" <oleg@.opel.com.ua> wrote in message
> news:OMIZzsHrFHA.528@.TK2MSFTNGP09.phx.gbl...
>sql

Hard coding colum names in returned DetailsView table

Hi all,

We're selecting data from our database, FirstName, LastName, MobileNumber etc.

We're using the detaials view function to return it in a table upon selection. However all of the variables are returned as they are in the database, ie:without spaces.

We tried putting in spaces by selecting "AS what ever", but MSSQL does not seem to like spaces.

Any ideas?

Thanks

Hey,

Are you trying to make the column names have spaces, or the data/ If the first, try using:

AS "First Name"

OR

AS [First Name]

|||

You can rename the columns like this:

SELECT PhoneNumber AS [Phone Number]
FROM TestTable

Handling nulls in matrix sub totals

I have a matrix where I am using the inscope function to return different
calculations depending on which group it is in (ie the detail is just the
value and the sub totals are sum(values)).
In for the subtotal group I have used:
iif(isnothing(sum(Fields!CurrentCount.Value)), 0,
sum(Fields!CurrentCount.Value)
But this is still returning blanks where there are no values in the group to
sum together.
Can anyone suggest how to get around this issue as it ?
TIA
Andrewyou might try iif(sum(Fields!CurrentCount.Value = nothing)
"Andrew Murphy" <AndrewMurphy@.discussions.microsoft.com> wrote in message
news:56B0AB62-6CDE-4F41-BF39-AC72FABAC2C0@.microsoft.com...
>I have a matrix where I am using the inscope function to return different
> calculations depending on which group it is in (ie the detail is just the
> value and the sub totals are sum(values)).
> In for the subtotal group I have used:
> iif(isnothing(sum(Fields!CurrentCount.Value)), 0,
> sum(Fields!CurrentCount.Value)
> But this is still returning blanks where there are no values in the group
> to
> sum together.
> Can anyone suggest how to get around this issue as it ?
> TIA
> Andrew
>|||also if this formula you put on here looks just like the one you have in the
expression then you need to add a perenthesis onto the end. I tried to
recreate your problem and I get a 0. Weird
"Andrew Murphy" <AndrewMurphy@.discussions.microsoft.com> wrote in message
news:56B0AB62-6CDE-4F41-BF39-AC72FABAC2C0@.microsoft.com...
>I have a matrix where I am using the inscope function to return different
> calculations depending on which group it is in (ie the detail is just the
> value and the sub totals are sum(values)).
> In for the subtotal group I have used:
> iif(isnothing(sum(Fields!CurrentCount.Value)), 0,
> sum(Fields!CurrentCount.Value)
> But this is still returning blanks where there are no values in the group
> to
> sum together.
> Can anyone suggest how to get around this issue as it ?
> TIA
> Andrew
>|||and aslo maybe you should try to set the null value to 0 before you sum it.
"Andrew Murphy" <AndrewMurphy@.discussions.microsoft.com> wrote in message
news:56B0AB62-6CDE-4F41-BF39-AC72FABAC2C0@.microsoft.com...
>I have a matrix where I am using the inscope function to return different
> calculations depending on which group it is in (ie the detail is just the
> value and the sub totals are sum(values)).
> In for the subtotal group I have used:
> iif(isnothing(sum(Fields!CurrentCount.Value)), 0,
> sum(Fields!CurrentCount.Value)
> But this is still returning blanks where there are no values in the group
> to
> sum together.
> Can anyone suggest how to get around this issue as it ?
> TIA
> Andrew
>

Handle datetime in SQL Server 2005

Are there any function in SQL Server 2005 which can help to calculate the total no. of days and months? Let's said if I provide 2 dates, 28-Feb-2001 and01-Mar-2004, it can return 36 Months and 2 Days. The concept is like the functionmonths_between in Oracle. Are there any function in SQL Server 2005 can achieve this?

You can use DATEDIFF function: for example

DATEDIFF

(m,'1/1/2007',getdate())as monthDiff

DATEDIFF(d,'1/1/2007',getdate())as dayDiff

|||

Try the DateDiff function

|||

try datediff function is very helpfull

Thanks