public class ben:harrell

July 10, 2007

SSIS Row Limits and DefaultBufferMaxRows

Filed under: C#, database, errors, Integration Servicees, SQL Server 2005, SSIS, Technology — benjamin harrell @ 9:51 pm

I had an odd issue during some testing I was doing on a custom source component I’m writing for EBCDIC data conversion.  I first assumed that surely my code was at fault but the test just seemed to simple to be “my code”.  So here’s the scenario,  I have a file with 100,000 bytes of the number ‘1’ in EBCDIC.  I created a simple package to import the file as 1 byte rows into a Dataset Destination.  I was surprised when I executed the package and found that only 65,536 rows were processed.  Now if you have been around software for more than a week you probably recognize this magic number and unfortunately it triggers all sorts of theories about what could possibly be going wrong.  Well, I’m still trying to investigate the “why” and I’ll update this post and welcome comments if anyone has input but for now the solution to this oddity is to increase the DefaultBufferMaxRows to greater than your number of records.  This seems like a horrible workaround for a situation that should happen quite often so the hunt continues…

January 15, 2007

Cannot open user default database. Login failed. Login failed for user ‘UserName’. (Microsoft SQL Server, Error: 4064)

Filed under: database, dba, errors, SQL Server 2005 — Tags: , , , , , , — benjamin harrell @ 8:28 pm

Cannot open user default database. Login failed. Login failed for user ‘UserName’. (Microsoft SQL Server, Error: 4064)

This error bugged the crap out of me tonight and I saw all kinds of crazy answers usually involving some sort of SQLCMD -blah -blah…i think this maybe the problem with command line happy developers/dbas but to make a long story short the fix is EASY and you will kick yourself for not trying this.

1.  click the options button if the options are not showing.

2.  click the connection properties tab if it is not active

3.  click on the Connect to database: dropdown

4.  type in the name of a database that still exists (ex. ‘master’)

5. connect!

I heard all kinds of rants and raves about how atrocious it was that microsoft did this to us and I agree it does suck that SQL Server doesn’t do something about this automagically but what would you suggest it do?

 

 

[UPDATE]  I wanted to move some of the great ideas in the comments to the top so that people could find other solutions more quickly:

From Commenter Fritz Laurel:

ALTER LOGIN loginid
WITH DEFAULT_DATABASE = master
GO

From commenter Das:

For those of you who use their Domain accounts, here is the Alter statement to run once you assign the MASTER DB from the above step.

ALTER LOGIN [BUILTIN\Administrators] WITH DEFAULT_DATABASE=master

Replace [BUILTIN\Administrators] with your [domain\usr]

NOTE:[Brackets] required on Domain\User accounts

From commenter Breb02 (SQL 2000?)

RUN–>cmd
sqlcmd -E -d master (osql -E -d master for SQL 2000)
1>sp_defaultdb ‘Computer\username’, ‘master’
2>go

Later SQL Server versions:
sqlcmd -E -d master

From commenter Fuad Ahmed

And the solution for me was just switching the registration property of the server from sql authentication to windows authentication.

I think I was lucky that the login sa had different default database and the windows authentication had different.

From commenter Tony G

I’m using VS2008 Server Explorer. I opened Modify Connection, Advanced Properties, and just set User Instance to true. That allowed my local SQL Server to open the local mdf. HTH

From commenter BobG

I had to.. Under my “registered servers” list, pick the offending server/instance and then right click, choosing “properties”, then choosing “connection properties”, and then type in a database name in the offending server/instance in the “Connect to database:” drop down box.

Then I was able to get in but no offered choices from this post worked for me beyond getting back in, and I had very little rights/access.
I wound up deleting my login from the Server/instance under “Security”. Then I was able to close MS SQL Studio and went back in without further issue.
Note: I also have that same account, a domain account, that I had deleted listed under other SQL Domain Groups for the offending server/instance.

From commenter PeterG

I am running Win7x64 and once of a sudden could not open my DB anymore. I ended up having to “take ownership” of the folder with the DB inside and viola – it worked

From commenter Adrian

To repair an an SQL Server 2000 with default db detached I made the following :

1. I used SQL Server 2005 management console from a client PC and get connected to the server to another existing database;

2. Created another user :
EXEC sp_addlogin ‘Adrian’, ‘p1234′

3. Re-attached the default database with :
EXEC sp_attach_db @dbname = ‘ATOTAL’,
@filename1 = ‘C:\ATOTAL DB\atotal_Data.MDF’,
@filename2 = ‘C:\ATOTAL DB\atotal_LOG.LDF’

From commenter spiritos

I couldn’t connect either (default db not found) so tried this fix but I couldn’t even browse the server to select a new db to use. As a result I started looking at the Security for the db… turns out there was a db that was restored (just happened to be my normal login’s default db!) but in the process some Users had been dropped from the Security folder – including the group that my “normal” login belonged to! Added these groups back in then could connect again!

From commenter One IT Services

You don’t have to use any commands at all, if you still have a login that works then use that to first login to the object explorer.
If you don’t then when logging in make sure you do as stated in the blog which was to click the options button on the Connect to Server Screen.
Then on Connect to Database just click Browse Server, say yes to connect and select any database from the list.
Then once you’re in the Object Explorer, expand Security, Logins and select the username you need to fix.
At the bottom of the Login Properties box you just need to select a Default Database, most likely Master.

From commenter vishalsharma

Note : you have to type master in default database drop down. You won’t see it there in the list.

 

I hope some of these most appreciated comments will help others!

Create a free website or blog at WordPress.com.