Why don't you just try it and find out for sure instead of being rude to people trying to help. :|

BEGIN TRANSACTION

INSERT something INTO table

ROLLBACK TRANSACTION

INTERSECT

might also be useful for this scenario.

Without a dataset I can't be sure, but have you tried

SELECT DISTINCT * FROM...

You could develop a small app which can be run as a scheduled task every minute or so which will capture data changed from Oracle and transfer to SQL Server. You will need something on Oracle which will place the data in a temporary storage area (probably a table) eg trigger or addition to stored procedures etc. This could be as simple as creating an insert or update statement etc. This will be captured by the app and executed on SQL Server.

Wow, ASP. Thats going back a bit. Here is an example

Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")
Set Flds = iConf.Fields

			With Flds
			  .Item("http://schemas.microsoft.com/cdo/configuration/sendusing")		= 2 
			  .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver")		= smtpserver
			  .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport")	= 25
			  .Update
			End With

				strDesc = "Message contents"
			With iMsg
		 		Set	.Configuration = iConf
		  	   		.To = "myrealemailaddress@myrealemailcompany.com"
		     			.From = "mymail@mydomain.com"
	  				.Sender = "mymail@mydomain.com"
					.ReplyTo = "mymail@mydomain.com"
	 	  			.Subject = "Sending email with CDO"
		   			.textbody = strDesc
	   				.Send
				End With				

			Set iConf= Nothing

You may have to tweak it a little, but should work. Make sure IIS is configured to serve ASP pages as well. I don't think IIS 6+ is configured to do so by default.

Try a different cd/dvd unit - I had exactly the same problem over the weekend. Used an external USB dvd and no problems.

http://www.austiger.com.au/site/index.cfm[^] - Highly recommended, I've used them for about six years without any issues.

Try http://www.codeproject.com/KB/database/AdvancedSearchSP.aspx[^]

I've just put a system live today and I'm having issues connecting to SQL 2005 from w2k. SQL 2005 (64 bit with latest service pack) is on windows 2008, 64 bit server. Everything was tested before going live and worked fine. The only difference now is that there is a firewall between the systems. From the w2k box, I cannot connect to SQL 2005 via SSMS. Interestingly, there is also a problem actually using the website from the same computer. When submitting a form, the system just hangs. This happens both on IE and firefox, so I'm guessing its not a browser issue. Error message from SSMS is tcp error 0, the specified network is no longer available. SQL error : 64. Google has not thrown up any ideas so far (and I've been looking for about 6 hours now) I can access the website from XP laptop and also from W2k3 server. I have tried installing SQL 2005 on laptop, but I had 2008 trial on it some months ago and it seems some remnants of that are causing the install to fail, but thats another story.... Also of note, I CAN connect to the SQL 2005 box from w2k using Sql 2000 query analyser OK and if I change the connection string on dev system on w2k box to the live SQL server, it connects fine. Has anyone experienced this before? Any ideas on workarounds / fixes

Put SQL on a different port and open that (requires a little bit of reconfig, but not hard). Make sure the firewall only allows certain external IPs through (assuming you are connecting on a static IP). You should be able to connect reasonably securely on that. Can't see why they would have a problem setting that up for you.

Use XML

DECLARE @CSV XML
SET @CSV = '<key><id>1</id><id>2</id><id>3</id></key>'

SELECT
ParamValues.id.value('.','VARCHAR(100)')
FROM
@CSV.nodes('/key/id') AS ParamValues(id)

Run that and you should get three rows with values 1,2 & 3. Join that to your query or use in the WHERE clause Don't forget to parse the XML before presenting it to SQL in case there are any nasties on the input

Has anyone downloaded the trial version of Windows Server 2008 Web edition(64 bit) (6001.18000.080118-1840_amd64fre_ServerWeb_en-us-KRMWXFRE_EN_DVD.iso from http://www.microsoft.com/downloads/details.aspx?FamilyId=B1CB6D54-7268-4791-9C5D-CDB21516BF5A&WT.sp=_msdn_,dcsmgru7m99k7mqmgrhudo0k8_8c6m&displaylang=en[^]) and managed to install it? I've tried downloading it four times in the last few days and I'm convinced the file is corrupted. Boots fine, but when it starts installing windows, it fails with message 0x80070017 The file may be corrupt or missing. Installing on DL360 G5 with 4GB Ram, 72GB HDD primary. Also tried with HP SmartStart v 8.00 to install necessary drivers etc and this fails when starting to load windows with message winsetup.dll cannot be found (0x7B). 32 bit version works fine. Perhaps I'll try enterprise version of it and see if that works.

Thanks for the replies. Took the whole thing apart and ran each query separately, works as expected. Put back together, commented out the 3rd INSERT to just leave select. Pulls no records as expected. Commented out select and hardcoded insert with some data that I knew insert1 & 2 would create and works fine. Headbutted wall for a while, cracked open a bottle of wine, rebooted server and whadaya know...Works perfectly. Removed WITH(NOLOCK)s and... works perfectly. Removed READ UNCOMMITTED, works perfectly. No idea what caused it. Could have been some other lock, although nothing else should have had a lock. Its all marked for some serious testing and I'll see if its reproducable! Anyway, thanks for the help. Hope I can reciprocate one day.

I'm having a locking issue I think. Rather than post a whole heap of code, here is basically what I'm trying: Begin trans Insert into table1 Insert into table2 Insert into table3 select from table1, table2, table3 plus a few other unmodified tables. Commit trans Basically what seems to be happening is that the select on 3rd statement returns no records (because they are locked). After committing, same select returns exactly what it should. I could probably do this with several non-nested transactions, but that kind of defeats the purpose of the transaction. I have tried SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED before and during the main trans. Also tried wrapping the first two inserts in begin / commit and SELECT FROM WITH(NOLOCK). Have googled nolock and also TRANSACTION ISOLATION LEVEL but not really come up with anything useful. Does anyone have any ideas or can point me in the direction of a URL with some information please?

modified on Monday, June 16, 2008 4:33 AM

Does anyone know how to (or if it is possible) to change SQL 2005 editor to select text inside square brackets without the brackets? eg USE [MyDatabase] If you doubleclick on 'MyDatabase' it selects the brackets as well. Good old QA in SQL 2000 just selected the text.

Thanks - appreciate the info. I had a look at the data and there are indeed many instances where there is the same locality name spanning different postcodes, however, when you look at the reasons for their existance, then each one is actually a different physical location. In most cases this seems to be semantic although technically a different location. Examples are mainly for a large organisation to sort mail eg to differentiate say general mail, competition entry mail and perhaps parcels which are delivered to (most likely) the same building. I couldn't see any instance of a locale (if you include the delivery office and category + state) spanning multiple postcodes, although I have to admit I only scanned the data. I'll run some queries on it later and post the results, although I guess you have already done this at some point :-)

I had a look at Amazon and its very basic. They must rely on server power rather than elegent design. There is no difference between data in different countries. Ebay is much better, groups by country and then county/state/province etc. Doesn't appear to then divide into city and locality though, so although its part way to what I want, its seems to be less than my current design. When I say lowest element, I think the official term is granularity, eg in Australia, my address is 'my place', THORNTON, NSW, 2322, Australia. Thornton is the most granular element (apart from the actual street address) which is a member of postcode(2322) which itself is a member of state (NSW) et seq. In contrast the UK addresses most granular element is the postcode followed by region/city, county et seq. Perhaps I'm just getting too picky!

Does anyone know of, or can point me in the direction of a database address schema? Ideally I would like a schema that can handle addresses throughout the world. My current design uses the lowest element (excluding street address) in the address as one location id (eg in Australia it would be the suburb and I can then recurse to find postcode, state and country. In (say UK), this doesn't work too well as the postcode is the lowest element in an address followed by city, county and country (and no doubt other variations). I'm guessing some other countries have more elements in the address as well, so it would be nice to be able to handle them as well.

Try SET CONCAT_NULL_YIELDS_NULL OFF before you begin the query. Works in 2000, not usre about 2005.

Editpad