Archive for the ‘SQL Server / SSIS’ Category

SQL Server 2005 No Corresponding BEGIN TRANSACTION

November 3, 2010

This one turned out to be yet another highly misleading error message. It also only depleted me of about 24 mins of life-force, so therefore does not strictly qualify as a Journey Of Pain, but what the heck, with my insatiable public constantly demanding more material sometimes quality is compromised.

In the beginning was a tiny SQL Script no more than 10 lines or so; and this script had one purpose in life and that was to add a new NOT NULL column to an existing table: I will call it BRUCE, tblBruce to be precise. So here is the script:

BEGIN TRAN
BEGIN TRY
ALTER TABLE tblBruce ADD IsMandatory BIT NOT NULL
END TRY

BEGIN CATCH
ROLLBACK;
END CATCH

COMMIT;

..and that friends will return you the highly misleading error message “No Corresponding BEGIN TRANSACTION”.

It lies…while not making me quite as angry as the spurious reasons advanced for the Australian Intervention in East Timor in 1999, does tee me off a bit. I’d rate it a ‘Laksa stain on my tie while eating lunch’ – grade tee-off.

The real error is that I have not specified a DEFAULT VALUE for the new column. Because the column is new no values exist for the existing rows, and because we have specified NOT NULL we have to provide a DEFAULT VALUE for all those existing rows, like so:

BEGIN TRAN
— Add new column to Bruce with default value
BEGIN TRY
ALTER TABLE tblBruce ADD IsMandatory BIT NOT NULL DEFAULT 0
END TRY
BEGIN CATCH
ROLLBACK;
END CATCH
COMMIT;

So when adding a new NOT NULL column to an existing table, provide a default value for the existing rows and you will be spared 24 mins of Laksa-stain grade ennui.

You know it makes sense.

Advertisements

Powershell: “Failed To Connect To Server SERVERNAME” Remote SQL Server 2005 using SMO

June 18, 2009

That title is a ripper isn’t it ?
It reminds me of this limerick

There once was a young man from Japan
Whose limericks never did scan.
When told this was so,
He said, “Yes, I know.
I think it’s because I try and get as many words into the last line as I possibly can.”

So I was magnificently running the following Powershell fragment:

[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’)
$serverInstance = New-Object(‘Microsoft.SqlServer.Management.SMO.Server’) $MyRemoreDbServer
$MyDatabase = $serverInstance.Databases | where {$_.Name -eq $DbVariableName}

…when one day it doesn’t work, throwing up the luminous red error “Failure To Connect To Server MyDatabase”. I was shocked. Four hours later I was still shocked. It worked on a local connection but not on a remote connection.

Then, another freaking genius of a colleague (different to the last one) came in and said, “Check your Windows Firewall settings”, to which I said “How”, and he showed me.

Which is just as well because Windows Firewall was set to ON and when we set it to OFF my code fragment started working again. Huzzah!

This is the page which had the magic answer on it. I read it but only understood about a quarter of it. I’m sure you will do better. Its called “SQL Server 2005 Remote Connectivity Issue Troubleshoot” and its on a blog called SQL Protocols maintained by the Elders of Zion, Microsoft SQL Server protocols team. Fans of Aztec-Mayan Glyphs will find this page relatively easy to decipher. The repeated references to Windows Firewall didn’t mean anything to me until now.

What you specifically need to do is

1) Enable “Fire and Printer Sharing” in Firewall exception list in Windows Firewall on your remote database server.

2) Add TCP port or sqlservr.exe to Firewall exception list, either add “..\Binn\sqlsevr.exe” or add port.

OR Just turn the Windows Firewall off. If you dare.

I can haz MCSA ?

SQL Server 2005 Installation From Command Line: How To Avoid General Carnage

June 12, 2009

Tadpolers (or should that be ‘Biters’):

Testing on my current project require the installation of various products into Virtual Machines via Powershell scripts. Hence I am becoming more familiar with command-line execution of MSIs and SetUp.exes of various breeds.

Here’s a few issues I encountered before, during and after command-line installation of SQL Server 2005 and links to the solutions.

1. SQL Server 2005 Management Studio Not Installed
This comes about when you install Enterprise Edition on the same machine as SQL Express. Uninstall both instances (MSSQLSERVER and SQLEXPRESS plus any others you may have there) and reinstall Enterprise Edition. Management Console will be there afterwards. Promise.

Thanks to ssjaronyx4 on this thread at Proprofs

If you don’t have Managment Console present after a fresh install (i.e. SQLExpress was not there to begin with) then do a “Modify Installation’ and choose ONLY ‘Management Tools’ and ‘Business Intelligence Development Studio’ from Client Tools. Thanks to thef150 at Dev Newsgroups.

2. SQL Browser Service Not Running And Won’t Start
Quite possibly SQL Browser Service is disabled. Enable it via Surface Area Configuration Utility. Thanks to chuber on this thread at Blackthorne Discussion Groups

3. Autopeotomy During Install
My, you have had a bad time of it, haven’t you ? Unfortunately this is not fixable. Massive fail.

4. Passwords and Accounts
…such as SQLACCOUNT, SQLPASSWORD AGTACCOUNT, and AGTPASSWORD.
If possible, don’t specify them. Leave them blank and take the defaults. It’ll make your ife a bit easier…

BUT If you are installing into a Virtual Machine, however, you must specify them.

I used Network Service i.e. account name “NT AUTHORITY\NETWORK SERVICE”, password “NT AUTHORITY\NETWORK SERVICE”.

Make sure you specify those account names in double quotes just like Haidong Ji or you will suffer like a dog.

Here’s the MSDN reference ‘How To Install SQL Server 2005 From The Command Prompt’

5. Installation Display Switches
Use /qb (Quiet with basic UI) instead of /qn (Quiet with no UI).
/qb will throw up the dialogs, but without requiring user input. This way you can see how the install is progressing and if any error messages come up you will see a nice Message Box instead of having to rummage through log files.

SQL Server 2005 Failure May Be Caused Because Default Settings Does Not Allow Remote Connections

May 2, 2009

Look, I don’t like to mention this…but…this error can be caused because your SQL Server 2005 service is STOPPED. (Quickly pulls paper bag over head).

So have a look in SQL Server 2005 Configuration Manager and do the right thing before anyone notices.

But can I say this error message must be one of the more obtuse of its kind? Here it is in its full glory:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005 this failure can be caused by the fact that under the default settings SQL Server does not allow remote connections (provider: Shared Memory Provider, error 40: could not open a connection to SQL Server)

How about flamin’ well saying that the service is not running ???

For its coma-inducing combination of tendentiousness and irrelevance I vote THIS error message ‘Most Obtuse, 2009’ . Any seconders ?

More fixes for this problem are described at this excellent blog entry SQL SERVER – Fix : Error : 40 – could not open a connection to SQL Server by Pinal Dave.

Some More Obscure Error Messages

Here’s a great article called The 13 Other Greatest Error Messages Of All Time, with links to the original 13 Greatest and its comment list of 400 or so other wonderfully obscure and weird error messages.

Then try these from MYOB.

SSIS Excel Data Source: Error with output column “Comments” (5691) on output “Excel Source Output” (5596). The column status returned was: “Text was truncated or one or more characters had no match in the target code page.”

April 28, 2008

Using SQL Server 2005 SSIS to import an Excel Spreadsheet into a SQL Server database,  I got the error shown in the title.

The problem was that the Excel spreadsheet column ‘Comments’ contained data which was bigger than the maximum size of the Excel Source External Column ‘Comments’ as defined in the Excel Data Source.

The data types of the Excel Source External Columns are automatically determined by SSIS based on sampling of the spreadsheet rows. For string columns if the sample does not reveal cells with greater than 255 characters, the data type is always DT_WSTR (Unicode string) which has maximum length 255. If the sample reveals cells with greater than 255 chars then the datatype will be DT_NTEXT (Unicode text stream).

I was unaware of how the Excel Source datatypes were derived and since the SSIS sample came up with only short strings in ‘Comments’, it assigned it a datatype of DT_WSTR. My full spreadsheet data for ‘Comments’, however, contained cells with strings of greater than this length, hence the truncation error when the SSIS package was executed.

The reason that the datatype for an Excel spreadsheet string column are always DT_WSTR or DT_NTEXT is given by this Microsoft Technet article, ‘Excel Source’ which explains that the datatypes are determined by the Jet Database Engine used by the SSIS Excel Connection Manager to communicate with Excel.

It is impossible to edit the datatype of an Excel Data Source column in SSIS therefore it is impossible to avoid this truncation error unless you edit your spreadsheet data, convert the Excel spreadsheet to a flat file, or increase the sample size as described in the Technet article in the link above.

I decided to convert the Excel spreadsheet to a flat file.

Convert Excel Spreadsheet To A Pipe-Delimited File

This is where my forty minutes turned into three hours.

Since I was not aware of how to increase SSIS sample size at the time, I tried to convert the Excel spreadsheet into a flat-file. Stupidly, I first converted it into a comma-delimited (.csv) file. Of course I ran into problems with phantom columns and incorrectly parsed data because my spreadsheet has embedded commas in its data.

So I tried a Find and Replace in Excel to convert commas to spaces and ran into the Excel ‘Formula Too Big’ error for cell data greater than 900 characters. Since Find and Replace could not work, I located a VBA macro written by aht-ga which did it. This Macro works, but took three hours to run over 1MB of data (cell selection was 1230 rows x 25 columns).

Gibbering with horror at the prospect of ever having to run this Macro again, an epiphany struck me in the form of rememberance of pipe-delimited files. Excel can be forced to make pipe-delimited files by this trick posted by Martin Naranjo.

I now possesed a beautiful pipe-delimited file which I included in my SSIS package using a Flat File Data Source. Flat File Data Sources, unlike Excel Data Sources, can have the datatype of their External Columns edited.

Editing The DataType Of SSIS Data Source Columns

Bring up the SSIS Connection Manager Editor by double clicking on the Connection Manager
for your Data Source in the package’s Connection Managers tab. When you do this for an Excel DataSource you will see a landscape as barren as the Gobi Desert Municipal Swimming Centre, but if you do this for the far more flexible Flat File Data Source a plethora of options will greet you. Click on ‘Advanced’ and slash away to your heart’s content.

Moral Of The Story
When using an Excel Data Source, increase your SSIS sample size to a humungous number so that it can assign the correct datatype to the Excel Data Source External Columns.

Problem Metrics:

    Life Energy Consumed: 1 Hr. (not including Macro running time)

Caveats, Apologies and Clarifications:

Aht-ga’s VBA Macro is excellent. It just took too long to run in my scenario.