It can be hard getting back to blogging after some time off, but here goes. I moved in March/April and I’ll be unpacking for a long time, but I do want to be blogging it is just hard to find the time. I figure the best way to get back into it is to start small.
When importing data from an Oracle database to a SQL Server database, there are all kinds of strange conversions you have to make. In part it looks this way because of the syntax and commands you have to use. You have to use an OpenQuery select to access the data and if you want to limit what you’re returning, you end up having to use Dynamic SQL to create a string and then execute that string. As a result, you end up with a LOT of single quotes and it can be hard to determine exactly how many you need on a side of a variable. For example
DECLARE @TestQuery as varchar(2000)
DECLARE @TestDate as varchar(20)
SET @TestDate = CONVERT(VARCHAR(20), GETDATE(), 106)
@TestQuery= ‘INSERT INTO Sales(Store, SaleDate, SaleAmount, DataSource))
SELECT Store, SaleDate, SaleAmount, ”Oracle”
FROM (SELECT Store, SaleDate, SaleAmount
FROM OPENQUERY(Registers, ”SELECT Store, SaleDate, SaleAmount
WHERE SaleDate = ””’ + @TestDate + ”””’))
As much fun as it can be to try to determine how many quotes you need inside a quoted string so that you’ll still have the right number of sincle quotes within the string, that’s not the part that bit me recently.
We’re upgrading from a SQL Server 2000 database to a SQL Server 2008 R2 database. The code on SQL Server 2000 to get the data from Oracle is almost the same as on SQL Server 2008 R2 with one important difference. The above SQL is on 2000 and it works well. It will also work if you just pass a datetime variable.
I’m not a fan of using formatting or passing text instead of the actual data type, so when I converted this to 2008 R2 I declared it as datetime and tested it.
It didn’t work. I tried it with the original CONVERT, it didn’t work. What finally worked?
DECLARE @TestDate as Date = CAST(GETDATE() as date)
2000 doesn’t have this data type, but Oracle is able to handle the implicit conversion. Formatting it as text also works in this case. When passing it from 2008 R2, it needed to be passed as a date type to parse correctly.