Swedish SQL Server User Group

Just wanted to let you know that the Swedish SQL Server User Group (SQLUG.se) is making a comeback after hibernating for some time. Can't say I was too active the last time around but I'm planning to change that now. They're kicking it off with a meeting featuring Kalen Delaney as a speaker so it should be prove interesting enough.

Office 2010 Beta

Microsoft Office 2010 is now out as a public beta. Available at: http://www.microsoft.com/office/2010/en/download-office-professional-plus/default.aspx

Enjoy

SSIS, Oracle and x64

I hope I can spare someone the time I just spent to get this working...

The scenario is as follows:
We have SSIS 2008 installed on a windows 2008 x64 server and want to connect to a Oracle database and the standard drivers doesn't work. Something with version 7.3.3 requiring another set of drivers. Anyway, here is what I did. (You don't have to download the complete 1,87 GB package)

I chose to go through ODBC.

1. Download Instant Client Package - Basic (~40Mb) from Oracle (11g) for both 32 and 64-bit.

x64 found here:
http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/winx64soft.html
and 32-bits found here:
http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/winsoft.html

2. At the same location, download Instant Client Package - ODBC (1Mb) for both 32 and 64-bits.

3. I started with the 64-bits install. Create a folder somewhere, i.e. C:\Oracle\InstantClient_64 and extract the files from the basic package (64-bit) to that folder. Extract the ODBC package (64-bit) to the same catalog. Open a commandprompt and run odbc_install. Be sure to start the prompt with administrator privileges.

4. Now we need som .ora files, sqlnet.ora and tnsnames.ora was enough for me. I copied them from another server. You will find lots of examples on the internet if you try searching google. I created a folder C:\Oracle\InstantClient_64\network\admin and placed them there.

5. Now we need to create some enviroment variables. Theese are shared between 32 and 64 bits enviroment. First add to the Path variable the directory you just created (C:\Oracle\InstantClient_64). Second create a new variable called TNS_ADMIN pointing to the location of you .ora files (C:\Oracle\instantclient_64\network\admin)

6. Run the ODBC-administrator with administrator privileges and try creating a system-dsn, it should work. The problem is that BIDS is a 32-bit application, so you can't see the 64-bits odbc sources from BIDS.

7. Create a folder somewhere, i.e. C:\Oracle\InstantClient_32 and extract the files from the basic package (32-bits) to that folder. Extract the ODBC package (32-bit) to the same catalog. Now here is the thing: open a commandprompt from C:\Windows\SysWOW64\cmd.exe and run odbc_install.exe. This installs this as a 32-bits driver.

8. Add the directory containing 32-bits package to the path variable (C:\Oracle\InstantClient_32)

9. Run 32-bits ODBC-administrator from: C:\Windows\SysWOW64\odbcad32.exe and verify that it works.

10. When you create the DSN, make sure you call them the same name in both 32 and 64 bits. Also, clear the username and make sure it's a system dsn.

11. Done.

Hope this will help someone, or me when this is long forgotten.

Cisco VPN 64 bit

Cisco VPN client for 64-bit does not yet exist as far as I know. Try the Shrew client, http://www.shrew.net instead. Also, it doesn't cut your internet access.

2009 Pass Summit Day 1

So today was the day the conference started for real. The day started with opening remarks from PASS's President Wayne Snyder and was followed by a keynote by Bob Muglia and Ted Kummert of Microsoft. They talked about SQL Server in general and the upcoming 2008 R2 release in particylar.

The session then were hit and miss. Highlights include Grant Fritchey's     spotlight Best Practices for Working with Execution Plans. While not exactly a BI-related session it was packed of useful information that anyone working with SQL Server will find useful.

The other highlight was hearing Italians Marco Russo and Alberto Ferrari of SQLBI discussing their SQLBI Methodology. In short is a mix between the Inmon and Kimball approaches to data warehousing (see here or google Kimball vs Inmon for a summary). Not only was it thought provoking to hear someone not subscribing 100 % to the Kimball approach, they also managed to combine outlining the differences between Kimbal vs Inmon and describing their methodology with being absolutely hilarious at the same time.

I might write a separate post just about that.

@@Identity with ASP

T-SQL note
@@Identity should be safe to use, unless you are using triggers, if you want to retrieve the latest ídentity value in a table.

Insert into myTable (Col1) Values('hello')
Select @@Identity

I know the @@identity is global within the connection, but it should be safe to use unless you are using triggers. When running from a ASP web application, there seem to be some connection pooling that makes this fail, or rather return the wrong ID. So if two concurrent users are calling the same procedure through an ASP application they might get each others id's.

Solution is as simple as the example: just use scope_identity() function instead or the output variable available in SQL2005 and later.

2009 Pass Summit Day 0 - Pre conference seminars

The pre-conference seminars were titled Delivering BI to the Masses. Maybe not too much of the material was that new to me the great speaker Peter Myers still made it worthwhile.

In the evening we were treated to a tour of the Microsoft Campus by Swedish developer Tobias Ternström and a very nice dinner at Elliot's. Courtesy of Microsoft Sweden. Thanks guys!

(BTW I just realised that you could publish blog post from Word 2007. This is really going to improve my English spelling and grammar)

2009 Pass Summit

So I and two of my esteemed colleagues are in Seattle for the PASS Summit this year. None of us had been before so we were really excited.

First I must say that Seattle is a really great city. We came here on Sunday before the conference, to attend a pre-conference session on Monday. Weather was lovely with the sun shining most of the time with hardly any rain at all. Great food too.

I will try to do a write up of each day. But there will be latency – I won't promise live blogging.