David Underwood’s Professional Blog

I found this nice article on using cursors to loop through a result set in a stored proc and take actions based on the value in the looped row:

This method will loop through all rows of a table regardless of what type the primary key is.

use YourDatabaseNameHere
go

declare @Field1 int
declare @Field2 int
declare @Field3 int

declare MyCursor cursor fast_forward for
select Field1, Field2, Field3 from YourTableNameHere

open MyCursor
fetch next from MyCursor
into @Field1, @Field2, @Field3

while @@fetch_status = 0
begin
— Perform Operations
declare myTest int
myTest = @Field1 + @Field2 + @Field3

– Advance the Cursor
fetch next from MyCursor
into @Field1, @Field2, @Field3
end

close MyCursor
deallocate MyCursor
go

This was on the following site: http://www.bunkerhollow.com/blogs/matt/archive/2009/03/10/ms-sql-loop-through-table-scripts.aspx

In Windows 7 while using SQL Express 2005 on my local dev machine and attempting to restore a database I received the following error:

the operating system returned the error ‘5 access is denied. ‘…

I found a nice posting on the MSDN social site at this link which helped to solve my problem.

Just wanted to say that I had the same problem – I could not restore from a .bak file.

error 5(error not found).
RESTORE HEADERONLY

Using SQL Configuration Manager I changed my Logon to ‘Local System’ and this worked a treat.

So take heart – it does work!!!!

My steps in detail.

1. open up SQL Server Configuration Manager (I am using SQL Server 2005)

2.right click on SQL Sever Express (I assume it is the same for the full version of SQL Server)

3.Choose Properties

4. in the Logon tab – click the built in account radio button and choose ‘Local system’ from the drop down.

This worked for me

I am not sure if this is the most elegant or correct solution but it worked for me for the moment on my dev machine to get my database restored.

Thanks

Here’s a handy query for finding duplicates in a table. Suppose you want to find allemail addresses in a table that exist more than once:

SELECT email,COUNT(email) AS NumOccurrencesFROM usersGROUP BY emailHAVING ( COUNT(email) > 1 )

You could also use this technique to find rows that occur exactly once:

SELECT emailFROM usersGROUP BY emailHAVING ( COUNT(email) = 1 )

Via HuajunZhai’s Blog. Also see Howto remove duplicate rows from a table – Microsoft Knowledge base article.

When working with dealer locators online it sometimes is necessary to find the distancebetween two points (the visitor’s address and a store). The easiest way to accomplishthis is to geocode each of the addresses and then find the distance using math.

A simple SQL function to do this follows:

CREATEFUNCTION dbo.udfComputeDistance
(
  @lat1 float,
  @lon1 float,
  @lat2 float,
  @lon2 float
)
RETURNS float
AS
begin
  – dLong represents the differences in longitudes
  – while dLat is the difference in latitudes
  declare @dLong float
  declare @dLat float
  – To keep the calculation easier to understand,
  – we have simplified it by computing it by parts.
  – This value temporarily holds the value of the
  – first calculation.
  declare @temp float
  – Convert the decimal degrees to radians
  set @lat2 = radians(@lat2)
  set @lon1 = radians(@lon1)
  set @lat1 = radians(@lat1)
  set @lon2 = radians(@lon2)
  – Compute the degree differences
  set @dLong = @lon2 – @lon1
  set @dLat = @lat1 – @lat2
  – Compute the first part of the equation
  set @temp = (square(sin(@dLat/2.0))) + cos(@lat2) * cos(@lat1) * (square(sin(@dLong/2.0)))
  – Return the approximate distance in miles
  – Note that 3956 is the approximate median radius of the Earth.
  return (2.0 * atn2(sqrt(@temp), sqrt(1.0-@temp)))*3956.0
end

You can find the article this code came from below:

http://www.dotnetjunkies.com/Article/AE9E07E6-612C-4709-9F20-5BA186A934E1.dcik

If you are interested in more information on the math of finding distances betweenlatitudes and longitudes check out the following sites:

http://mathforum.org/library/drmath/view/54680.html

http://members.tripod.com/~Paul_Kirby/appletgreatcircle/greatc.html

http://proj.maptools.org/man_geod.html

Source:

http://www.dotnetjunkies.com/Article/AE9E07E6-612C-4709-9F20-5BA186A934E1.dcik

Triggers are great- check out some information on CodeProject…

http://www.codeproject.com/database/SquaredRomis.asp

A great utility for helping ease the tedious amount of programming done that I wastold about by a friend is CodeSmith. I ran through the documentation (the helpfile installed with the program) and found it widely useful. You can check it outat http://www.codesmithtools.com

Sorry I’ve been a bit behind on posts and responses. It seems all I have time foris just getting things accomplished at work not necessarily documentation as wellas the wedding is coming up June 10th so it adds a bit of craziness to my life I’m looking forward to it and feel very blessed that the Lord has given me such awonderful lady as well as the ability to learn and share with everyone.

Along those lines I ran across the need to transfer data out of one table (with oneschema) into another table (with a different schema). At times I’ve just exportedthe data into another table but found a better way…

Iwas able to accomplish it using a select statement. Usually the columns have to benamed the same but you can use AS and make them appear to be the same.

For example:

MyNames (Table 1) Destination

FIRST      LAST      ZIP

=================

[empty]

Guests (Table 2) Source

FIRSTNAME       LASTNAME      ZIPCODE      

===============================

John                  Smith            12345

Allen                  Bell               67890

To transfer data from table 1 to table 2:

INSERT INTO MyNames

SELECT FIRSTNAME AS FIRST, LASTNAME AS LAST, ZIPCODEAS ZIP FROM Guests

If you have settings to not allow nulls and do havenulls you can use the ISNULL function. Let’s assume some of our records did not havezip codes. The new query would be:

INSERT INTO MyNames

Of course you could always use transformation services but thisis a quick and easy way in SQL.>

Server:

Tested on SQL Server 2005

Source:

http://www.databasejournal.com/features/mssql/article.php/3507171

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ia-iz_6mek.asp

Today I was attempting to use the aspnet_regsql tool to register my SQL Server toserve as a data store for my membership roles information in asp.net 2.0 (MembershipProvider). Instead of pointing to the EXPRESS SQL Server database I needed to usethe another SQL DB server.

When attempting to run the tool and obtain a listing of databases from the dropdownthe following error occurred:

“Failed to query a list of database names from the SQL server. Invalid object name’sysdatabases’.

This didn’t seem correct so I did some searching around.
It seems this is a bug. In order to correct this you will need to set the defaultdatabase to “master.” In order to do this you will have to go under Security and choosethe Login that you are running the aspnet_regsql tool under. Then set the defaultdatabase for that login to “master.” Close the tool and try again and the dropdownwill populate.

Sources
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=154332&SiteID=1