[SQLServer2005] Select from Excel Spreadsheet for SQL-Rejects

This is DBA bread and butter - not being a DBA and having no will whatsoever to become one (I am too weak) I always forget the syntax for this procedure, so here we go.

In order to be able to query an excel spreadsheet we need to enable ad hoc distributed queries, to do so follow the procedure in this other post.

Once that's sorted - assuming you have an excel spreadsheet with column names (in the example I have Column1, Column2, Column3, Column4) in the first row - this is how you go about selecting the spreadsheet content into a temporary table:

USE myDB

-- drop temp table
DROP TABLE #tempTable

-- select spreadsheet content into #tempTable
SELECT S.[Column1], S.[Column2], S.[Column3], S.[Column4]
INTO #tempTable
FROM OPENROWSET
('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\mtSpreadsheet.xls;HDR=YES',
'select * from [Query1$]') AS S;

-- check if the stuff is there
select *
from #tempTable

That's it. Hope it helps some other fellow SQL-Reject.

[SQLServer2005] Enable Ad hoc distributed queries

The Ad Hoc Distrubuted Queries advanced feature is disabled by default - it needs to be enabled if you wanna run OpenRowSet or OpenDataSource operations (queries on excel spreadsheets and other stuff). 

To enable it run the following script and you should be OK:

-- allows you to see advaced options status
sp_configure 'show advanced options', 1
RECONFIGURE
GO
-- enables Ad Hoc Distributed Queries
sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE
GO
-- shows you the full list of features
sp_configure

The same result can be achieved by opening SQL Server Area Configuration tool for features and after picking Ad Hoc Distributed Queries ticking the "Enable ..." checkbox.

That'd be all.

[SQLServer] Indexing and Scalability Tips

Recently I spent a lot of time messing about with indexes. Not because I like it - but because there was a little bit of a fuckup at work with scalability issues on a DB. After messing around with SQLProfiler (real life-saver in this case) I gained a bit more of understanding about indexes as I never got the chance to do before.

What I learned about indexes and scalability can be summarized in 2 points:

1) You don't wanna have a clustered index on the primary key on a table once it gets huge (just make it nonclustered) if you plan on running loads of inserts

This is not a problem when the table is small - and since it's good for fast selects everyone's cool with it. Once the table gets populated with LOADS of records or it gets HUGE in size this WILL KILL YOU on any insert or update operation.

Just to give a few figures:

- A clustered index on a table with 45 million records was killing my SQL server - every insert from code was resulting in nasty timeouts (it was taking more than the default 30 secs for standard inserts - Increasing your timeout tolerance won't solve your scalability problems!)

- A clustered index on a HUGE table (15 gigs) with a few million records was slowing down inserts to the point it was taking 15 times as much as it was taking on an empty db (we're talking mSecs but on big batches it's gonna kill you)

2) You wanna have a non-clustered index for frequently selected fields on any table you plan on running extensive selects on (add the fields you select often to a single index)

- On a medium size table (40k records) select time on a given set of fields was doubling in time compared to the same table with a lot less data before adding a triple-index on the set of given fields.

Following the points above I was able to solve my scalability issues (the process I was running is now taking the same time on a huge db compared to an empty one). Obviosuly these rules are not applicable in every case - but understanding the points above might help people when 'scalability' hits the fan.

[SQLServer2005] computer localhost does not exist on the network

This really bugged me out.

Talking about useful error messages from SQL Server, After installing SQL Server 2005 whatever edition on Vista and setting up a few DBs on management studio I tried to access SQL Server Surface Area Configuration and I got the following genial error message:

"computer localhost does not exist on the network (...)"

this is some epic bullshit, obviously followed by some other useless crap which I won't mention.

This obviously gave me no clue - so just out of curiosity I tried to open SQL Server Configuration Manager and I got another happy error message which makes even less sense:

"Cannot connect to WMI provider"

WTF is this supposed to mean? I have no clue what a WMI provider is. Anyway - the only resort I was left was good old Google. So I started googlin' like crazy and I found some crazy SOAB of an MVP on a msdn forum thread who was trying to convince some indian guy to reinstall Windows and SQLServer. 

I kept looking till I found something that did the trick: 

Apparently the problem is due to some SQLServer installation fuckup with some MOF files.

If you run this in your cmd line it should solve it:

C:\Program Files\Microsoft SQL Server\90\Shared>mofcomp "C:\Program Files\Microsoft SQL Server\90\Shared\sqlmgmproviderxpsp2up.mof"

[SQLServer] Error 3205: too many devices specified for backup or restore only 64 are allowed

Problem: restoring a backup on SQLServer I get the following error:
[Error 3205: too many devices specified for backup or restore only 64 are allowed]

Solution: you're probably trying to restore a SQL2005 backup on a SQL2000 instance. There's no backward compatibility for backup operations bewteen SQL2005 and SQL2000. Installing SQL2005 will make the error go away (I know - it sucks).

I recently came across the cryptic error message in subject. At first I was puzzled - then google helped me understand the problem was not my lack of sweet SQLServer skills...

Let that boy copy paste (with SQL2005 and Excel)

I heard Papa tell Mama

Let that boy copy paste from SQLServer2005 to Excel
It's in him

And it got to come out

John Lee Hooker - Boogie Chillen (1948)

Being a software engineer - I pretty much hate messing with DBs.

Nonetheless, more often than not, even the brightest designers - such as myself - are called to get their hands dirty with some SQL or moving some data around when the team's DB guy (usually indian) is badly sick at home.

I remember how painful it was the first time I had to understand with SQL2000 how to export to excel a simple resultset from a select - must have wasted a full afternoon of frustration trying to do that a few years back. 

A couple of months ago I was shocked in discovering that with SQL2005 you can just select all and copy/paste a resultset to excel. This means that if you have to import/export some records you don't need to mess with import/export wizards and all that comes after. Shrinking to 3 clicks (select all - copy - paste) a 5 minutes painful procedure. 

Forget about TRY CATCH, CLR support, and all that fancy stuff, copy/paste to/from excel is my favourite SQL2005 new feature (ok - this sounds something like 3 years late, but it's not what this post is about).

DB purists might not like it but copy/paste is "de way 2 go".  

[SSIS] How to remotely run a DTSX package from bat file?

Short answer (or at least the conclusion I came to after a fair amount of research  - and plenty of hair pulling): you can't .

Long Answer: I started from this cmd, which works like charm locally:

DTEXEC /DTS "\File System\MY_PACKAGE_NAME" /SERVER MY_SERVER_NAME /MAXCONCURRENT " -1 " /CHECKPOINTING OFF  /REPORTING V

If you try to use the same cmd remotely it will miserably fail with some nasty timeout.

After a bit of googling it looks like it is impossible to run DTEXEC cmd remotely (it needs to be run locally - remote execution apparently is not supported).

To overcome this limitation the following method seems to be broadly implemented:

• set up a SQL job to run the DTSX package
  
• set up a Stored Procedure to run the job
  
• use isql command line in a BAT file (remotely executed) to run the stored procedure on the relevant SQL instance (with SQL credentials and not machine credentials)

Might work - but kinda sucks.

[SQL Server 2005] SQL Server 2005 hangs on "Setting [File/Registry ] Security"

Prolem: SQL Server 2005 (any edition including express) installation hangs on "Setting [File/Registry ] Security".

Solution: Either remove the network cable and restart or Go out for lunch and enjoy (it takes ages, 2-3 hours for the whole installation for big networks).

This is a very common problem that occurs often in presence of huge networks (global corporate networks are good candidates); the reason why it all happens seems to be related to some domain resolutions. The installation runs smoothly to this point, then it appears to hang for a long time, so long the temptation cancel the installation is almost irresistible. What stops you from doing it is obviously the fact that from taskmanager everything seems to be just fine. If you let it be for a couple of hours it evetually will finish the installation alright. I am at the moment not aware of other solutions rather than disconnect the machine from the network or - my favourite - go out for lunch, and make sure to have a big one.