db time conversion

Wednesday, March 7, 2012

q; Registration access denied

Am I able to register SQL2005 server in Enterprise Manager of SQL2000, or
SQL2000 server in SQL Server Management Studio?You can register a SQL 2000 instance in SSMS but not a SQL 2005 instance in
EM. However, the subject of your message indicates an 'access denied'
error, which is different than a version incompatibility. Try to connecting
with SSMS and post the full error message.
Hope this helps.
Dan Guzman
SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:03DDAA79-2041-44F7-8FF2-ABA2BB9D2BCA@.microsoft.com...
> Am I able to register SQL2005 server in Enterprise Manager of SQL2000, or
> SQL2000 server in SQL Server Management Studio?|||EM will not be able to register SQL 2005 servers, AND SSMS will be able to
register SQL 2000 servers.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:03DDAA79-2041-44F7-8FF2-ABA2BB9D2BCA@.microsoft.com...
> Am I able to register SQL2005 server in Enterprise Manager of SQL2000, or
> SQL2000 server in SQL Server Management Studio?

q; Registration access denied

Am I able to register SQL2005 server in Enterprise Manager of SQL2000, or
SQL2000 server in SQL Server Management Studio?
You can register a SQL 2000 instance in SSMS but not a SQL 2005 instance in
EM. However, the subject of your message indicates an 'access denied'
error, which is different than a version incompatibility. Try to connecting
with SSMS and post the full error message.
Hope this helps.
Dan Guzman
SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:03DDAA79-2041-44F7-8FF2-ABA2BB9D2BCA@.microsoft.com...
> Am I able to register SQL2005 server in Enterprise Manager of SQL2000, or
> SQL2000 server in SQL Server Management Studio?
|||EM will not be able to register SQL 2005 servers, AND SSMS will be able to
register SQL 2000 servers.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:03DDAA79-2041-44F7-8FF2-ABA2BB9D2BCA@.microsoft.com...
> Am I able to register SQL2005 server in Enterprise Manager of SQL2000, or
> SQL2000 server in SQL Server Management Studio?

q; radius search

I have latitude and longitude in my database, can anyone give me an sql
query how I can make radius search based on that?Not sure about radius but this function will return the distance between two
lat and lons. I am not really sure what you are speaking of in terms of
radius search...Perhaps this will lead you in the right direction...
Create FUNCTION [dbo].[fn_distance_fl](@.sLat int, @.sLon int, @.dLat int,
@.dLon int)
RETURNS float
AS
BEGIN
DECLARE @.fn_distance_fl float
SET @.fn_distance_fl = SQRT( ((0.006917 * (@.sLat - @.dLat) ) * (0.006917 *
(@.sLat - @.dLat)) + ( 0.005756 * (@.sLon - @.dLon) ) * ( 0.005756 * (@.sLon -
@.dLon))))
RETURN(@.fn_distance_fl)
END
thanks,
--
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:DEB1B553-ECF2-4A26-B09D-BDF622779463@.microsoft.com...
> I have latitude and longitude in my database, can anyone give me an sql
> query how I can make radius search based on that?
>|||Thanks for the reply. Ok. Here is an example, :
http://www.trucktraderonline.com/dealersearch.html there is a zip and there
is a distance search feature in this site, how are they performing this
search, I need to do the same thing, ge a zip code and do distance search, it
does not seem it involves two lat, long values.
"Warren Brunk" wrote:
> Not sure about radius but this function will return the distance between two
> lat and lons. I am not really sure what you are speaking of in terms of
> radius search...Perhaps this will lead you in the right direction...
> Create FUNCTION [dbo].[fn_distance_fl](@.sLat int, @.sLon int, @.dLat int,
> @.dLon int)
> RETURNS float
> AS
> BEGIN
> DECLARE @.fn_distance_fl float
> SET @.fn_distance_fl = SQRT( ((0.006917 * (@.sLat - @.dLat) ) * (0.006917 *
> (@.sLat - @.dLat)) + ( 0.005756 * (@.sLon - @.dLon) ) * ( 0.005756 * (@.sLon -
> @.dLon))))
> RETURN(@.fn_distance_fl)
> END
> thanks,
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:DEB1B553-ECF2-4A26-B09D-BDF622779463@.microsoft.com...
> >
> > I have latitude and longitude in my database, can anyone give me an sql
> > query how I can make radius search based on that?
> >
>
>|||Thanks for your reply. Can anybody write me that stored procedure,
MyTable: has fields: ID, Zip,Lat,Long
Assuming user entered UserZip and UserRadius=5miles. If these are parameters
for my stored procedure, how should I write my stored procedure to return all
the IDs that meet this criteria.
"Kent Tegels" wrote:
> Hello JIM.H.,
> Actually, it *probably* does. They know table that has the lat/long for some
> number of zip codes, and a table that knows that dealers are in what zip
> codes. So they take your input zip code, get the lat/lon for it, run a query
> against their lat/lon table ordering by the result the distance ascending,
> then join that back to dealer zip codes.
> Its unlike that match found this way is *exactly* 25 miles away from the
> customer, but "as the bird flies," the dealership should be within a 25 mile
> radius "give or take."
> Thanks,
> Kent Tegels
> http://staff.develop.com/ktegels/
>
>|||Jim,
Do you have a database of Zip Codes that map a Code to a LAT LON. You will
need to start there. Then you could use my function inside of a stored proc
doing something like.
Select * from fn_Distance(Lat1,Lon1,Lat2,Lon2)
where [Value] < 5
Lat 1 and Lon1 should be the starting zip code and lat lon 2 should be the
zip code of the dealership.
Hope that helps...
--
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:276E2AA0-B200-4F48-B8AE-D6F2009F5DAD@.microsoft.com...
> Thanks for your reply. Can anybody write me that stored procedure,
> MyTable: has fields: ID, Zip,Lat,Long
> Assuming user entered UserZip and UserRadius=5miles. If these are
> parameters
> for my stored procedure, how should I write my stored procedure to return
> all
> the IDs that meet this criteria.
>
> "Kent Tegels" wrote:
>> Hello JIM.H.,
>> Actually, it *probably* does. They know table that has the lat/long for
>> some
>> number of zip codes, and a table that knows that dealers are in what zip
>> codes. So they take your input zip code, get the lat/lon for it, run a
>> query
>> against their lat/lon table ordering by the result the distance
>> ascending,
>> then join that back to dealer zip codes.
>> Its unlike that match found this way is *exactly* 25 miles away from the
>> customer, but "as the bird flies," the dealership should be within a 25
>> mile
>> radius "give or take."
>> Thanks,
>> Kent Tegels
>> http://staff.develop.com/ktegels/
>>|||Hi,
Thanks for your help, I have two problems,
1.Your lat, long value are int, mine are float
2. Your function returns only one value I need to find IDs that mach the
criteria
"Warren Brunk" wrote:
> Jim,
> Do you have a database of Zip Codes that map a Code to a LAT LON. You will
> need to start there. Then you could use my function inside of a stored proc
> doing something like.
> Select * from fn_Distance(Lat1,Lon1,Lat2,Lon2)
> where [Value] < 5
> Lat 1 and Lon1 should be the starting zip code and lat lon 2 should be the
> zip code of the dealership.
> Hope that helps...
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:276E2AA0-B200-4F48-B8AE-D6F2009F5DAD@.microsoft.com...
> > Thanks for your reply. Can anybody write me that stored procedure,
> > MyTable: has fields: ID, Zip,Lat,Long
> >
> > Assuming user entered UserZip and UserRadius=5miles. If these are
> > parameters
> > for my stored procedure, how should I write my stored procedure to return
> > all
> > the IDs that meet this criteria.
> >
> >
> > "Kent Tegels" wrote:
> >
> >> Hello JIM.H.,
> >>
> >> Actually, it *probably* does. They know table that has the lat/long for
> >> some
> >> number of zip codes, and a table that knows that dealers are in what zip
> >> codes. So they take your input zip code, get the lat/lon for it, run a
> >> query
> >> against their lat/lon table ordering by the result the distance
> >> ascending,
> >> then join that back to dealer zip codes.
> >>
> >> Its unlike that match found this way is *exactly* 25 miles away from the
> >> customer, but "as the bird flies," the dealership should be within a 25
> >> mile
> >> radius "give or take."
> >>
> >> Thanks,
> >> Kent Tegels
> >> http://staff.develop.com/ktegels/
> >>
> >>
> >>
>
>|||1. You may change the values to a FLOAT with no ramifications.
2. When you are selecting from a table you can use the function to return
distance for a number of rows...When you use the scalar function on a select
statement it will give you the distance for each row in the select.
thanks,
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:A17B835D-E718-409E-BC2F-754A93F77732@.microsoft.com...
> Hi,
> Thanks for your help, I have two problems,
> 1.Your lat, long value are int, mine are float
> 2. Your function returns only one value I need to find IDs that mach the
> criteria
>
> "Warren Brunk" wrote:
>> Jim,
>> Do you have a database of Zip Codes that map a Code to a LAT LON. You
>> will
>> need to start there. Then you could use my function inside of a stored
>> proc
>> doing something like.
>> Select * from fn_Distance(Lat1,Lon1,Lat2,Lon2)
>> where [Value] < 5
>> Lat 1 and Lon1 should be the starting zip code and lat lon 2 should be
>> the
>> zip code of the dealership.
>> Hope that helps...
>> --
>> /*
>> Warren Brunk - MCITP - SQL 2005, MCDBA
>> www.techintsolutions.com
>> */
>>
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:276E2AA0-B200-4F48-B8AE-D6F2009F5DAD@.microsoft.com...
>> > Thanks for your reply. Can anybody write me that stored procedure,
>> > MyTable: has fields: ID, Zip,Lat,Long
>> >
>> > Assuming user entered UserZip and UserRadius=5miles. If these are
>> > parameters
>> > for my stored procedure, how should I write my stored procedure to
>> > return
>> > all
>> > the IDs that meet this criteria.
>> >
>> >
>> > "Kent Tegels" wrote:
>> >
>> >> Hello JIM.H.,
>> >>
>> >> Actually, it *probably* does. They know table that has the lat/long
>> >> for
>> >> some
>> >> number of zip codes, and a table that knows that dealers are in what
>> >> zip
>> >> codes. So they take your input zip code, get the lat/lon for it, run a
>> >> query
>> >> against their lat/lon table ordering by the result the distance
>> >> ascending,
>> >> then join that back to dealer zip codes.
>> >>
>> >> Its unlike that match found this way is *exactly* 25 miles away from
>> >> the
>> >> customer, but "as the bird flies," the dealership should be within a
>> >> 25
>> >> mile
>> >> radius "give or take."
>> >>
>> >> Thanks,
>> >> Kent Tegels
>> >> http://staff.develop.com/ktegels/
>> >>
>> >>
>> >>
>>|||Thanks Warren,
I tested your function, my test show this always returns numbers close to 0,
what is the unit in here? I need to is the miles.
Thanks for your help.
"Warren Brunk" wrote:
> 1. You may change the values to a FLOAT with no ramifications.
> 2. When you are selecting from a table you can use the function to return
> distance for a number of rows...When you use the scalar function on a select
> statement it will give you the distance for each row in the select.
> thanks,
>
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:A17B835D-E718-409E-BC2F-754A93F77732@.microsoft.com...
> > Hi,
> > Thanks for your help, I have two problems,
> > 1.Your lat, long value are int, mine are float
> > 2. Your function returns only one value I need to find IDs that mach the
> > criteria
> >
> >
> > "Warren Brunk" wrote:
> >
> >> Jim,
> >> Do you have a database of Zip Codes that map a Code to a LAT LON. You
> >> will
> >> need to start there. Then you could use my function inside of a stored
> >> proc
> >> doing something like.
> >>
> >> Select * from fn_Distance(Lat1,Lon1,Lat2,Lon2)
> >> where [Value] < 5
> >>
> >> Lat 1 and Lon1 should be the starting zip code and lat lon 2 should be
> >> the
> >> zip code of the dealership.
> >>
> >> Hope that helps...
> >>
> >> --
> >> /*
> >> Warren Brunk - MCITP - SQL 2005, MCDBA
> >> www.techintsolutions.com
> >> */
> >>
> >>
> >> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> >> news:276E2AA0-B200-4F48-B8AE-D6F2009F5DAD@.microsoft.com...
> >> > Thanks for your reply. Can anybody write me that stored procedure,
> >> > MyTable: has fields: ID, Zip,Lat,Long
> >> >
> >> > Assuming user entered UserZip and UserRadius=5miles. If these are
> >> > parameters
> >> > for my stored procedure, how should I write my stored procedure to
> >> > return
> >> > all
> >> > the IDs that meet this criteria.
> >> >
> >> >
> >> > "Kent Tegels" wrote:
> >> >
> >> >> Hello JIM.H.,
> >> >>
> >> >> Actually, it *probably* does. They know table that has the lat/long
> >> >> for
> >> >> some
> >> >> number of zip codes, and a table that knows that dealers are in what
> >> >> zip
> >> >> codes. So they take your input zip code, get the lat/lon for it, run a
> >> >> query
> >> >> against their lat/lon table ordering by the result the distance
> >> >> ascending,
> >> >> then join that back to dealer zip codes.
> >> >>
> >> >> Its unlike that match found this way is *exactly* 25 miles away from
> >> >> the
> >> >> customer, but "as the bird flies," the dealership should be within a
> >> >> 25
> >> >> mile
> >> >> radius "give or take."
> >> >>
> >> >> Thanks,
> >> >> Kent Tegels
> >> >> http://staff.develop.com/ktegels/
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>

q; radius search

q; please check this trigger

Apparently I could not insert a text field to another table from INSERTED in
a trigger.
It seems the following is working, do you see any problem joining INSERTED
to the mySrcTable which is the table that has this trigger
INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigText)
SELECT i.myTrID, i.myFirstName, p.myBigText
FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID
WHERE (i.myTrType = 'In')
Thanks,
Hi Jim
From Books Online "Create Trigger" topic
In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text,
ntext, or image column references in the inserted and deleted tables if the
compatibility level is equal to 70. The text, ntext, and image values in the
inserted and deleted tables cannot be accessed. To retrieve the new value in
either an INSERT or UPDATE trigger, join the inserted table with the original
update table. When the compatibility level is 65 or lower, null values are
returned for inserted or deleted text, ntext, or image columns that allow
null values; zero-length strings are returned if the columns are not
nullable.
If the compatibility level is 80 or higher, SQL Server allows the update of
text, ntext, or image columns through the INSTEAD OF trigger on tables or
views.
If you use an instead of trigger your logic will have to be different as
mentioned in one of your earlier posts. If you are not going to hold more
than 8000 characters in this column change it to varchar.
John
"JIM.H." wrote:

> Apparently I could not insert a text field to another table from INSERTED in
> a trigger.
> It seems the following is working, do you see any problem joining INSERTED
> to the mySrcTable which is the table that has this trigger
> INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigText)
> SELECT i.myTrID, i.myFirstName, p.myBigText
> FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID
> WHERE (i.myTrType = 'In')
> Thanks,
>
|||Thanks John.
"John Bell" wrote:
[vbcol=seagreen]
> Hi Jim
> From Books Online "Create Trigger" topic
> In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text,
> ntext, or image column references in the inserted and deleted tables if the
> compatibility level is equal to 70. The text, ntext, and image values in the
> inserted and deleted tables cannot be accessed. To retrieve the new value in
> either an INSERT or UPDATE trigger, join the inserted table with the original
> update table. When the compatibility level is 65 or lower, null values are
> returned for inserted or deleted text, ntext, or image columns that allow
> null values; zero-length strings are returned if the columns are not
> nullable.
> If the compatibility level is 80 or higher, SQL Server allows the update of
> text, ntext, or image columns through the INSTEAD OF trigger on tables or
> views.
> If you use an instead of trigger your logic will have to be different as
> mentioned in one of your earlier posts. If you are not going to hold more
> than 8000 characters in this column change it to varchar.
> John
> "JIM.H." wrote:

q; please check this trigger

Apparently I could not insert a text field to another table from INSERTED in
a trigger.
It seems the following is working, do you see any problem joining INSERTED
to the mySrcTable which is the table that has this trigger
INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigText)
SELECT i.myTrID, i.myFirstName, p.myBigText
FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID
WHERE (i.myTrType = 'In')
Thanks,Hi Jim
From Books Online "Create Trigger" topic
In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text,
ntext, or image column references in the inserted and deleted tables if the
compatibility level is equal to 70. The text, ntext, and image values in the
inserted and deleted tables cannot be accessed. To retrieve the new value in
either an INSERT or UPDATE trigger, join the inserted table with the original
update table. When the compatibility level is 65 or lower, null values are
returned for inserted or deleted text, ntext, or image columns that allow
null values; zero-length strings are returned if the columns are not
nullable.
If the compatibility level is 80 or higher, SQL Server allows the update of
text, ntext, or image columns through the INSTEAD OF trigger on tables or
views.
If you use an instead of trigger your logic will have to be different as
mentioned in one of your earlier posts. If you are not going to hold more
than 8000 characters in this column change it to varchar.
John
"JIM.H." wrote:
> Apparently I could not insert a text field to another table from INSERTED in
> a trigger.
> It seems the following is working, do you see any problem joining INSERTED
> to the mySrcTable which is the table that has this trigger
> INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigText)
> SELECT i.myTrID, i.myFirstName, p.myBigText
> FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID
> WHERE (i.myTrType = 'In')
> Thanks,
>|||Thanks John.
"John Bell" wrote:
> Hi Jim
> From Books Online "Create Trigger" topic
> In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text,
> ntext, or image column references in the inserted and deleted tables if the
> compatibility level is equal to 70. The text, ntext, and image values in the
> inserted and deleted tables cannot be accessed. To retrieve the new value in
> either an INSERT or UPDATE trigger, join the inserted table with the original
> update table. When the compatibility level is 65 or lower, null values are
> returned for inserted or deleted text, ntext, or image columns that allow
> null values; zero-length strings are returned if the columns are not
> nullable.
> If the compatibility level is 80 or higher, SQL Server allows the update of
> text, ntext, or image columns through the INSTEAD OF trigger on tables or
> views.
> If you use an instead of trigger your logic will have to be different as
> mentioned in one of your earlier posts. If you are not going to hold more
> than 8000 characters in this column change it to varchar.
> John
> "JIM.H." wrote:
> > Apparently I could not insert a text field to another table from INSERTED in
> > a trigger.
> > It seems the following is working, do you see any problem joining INSERTED
> > to the mySrcTable which is the table that has this trigger
> >
> > INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigText)
> > SELECT i.myTrID, i.myFirstName, p.myBigText
> > FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID
> > WHERE (i.myTrType = 'In')
> >
> > Thanks,
> >

Wednesday, March 7, 2012

q; Registration access denied

q; Registration access denied

q; radius search

q; radius search

q; radius search

q; please check this trigger

q; please check this trigger

db time conversion

Blog Archive

About Me