>Should we send the Wizard back to Hogwarts?


>The SQL Server Import and Export Wizard in SQL Server 2005 is a handy tool to quickly transfer data from one data source to another, though on most occasions I use it to transfer data between two SQL Server instances. During these data transfer sessions, I always could not connect to a particular SQL Server instance using SQL authentication, though I could connect to it throught SSMS without any worries. The only difference between this particular SQL Server and the others was that, this one had the password as P@ssW0r;D. Whereas the others had simple passwords; either blip or bleep. So where would the problem lie?

Well you see, the Import and Export wizard in its internals uses SSIS, and within that it uses an OLEDB connection. An as usual an OLEDB connection uses a connection string, and when the password is given in the connection string… all hell breaks lose (if I were to exaggerate a little). Why you ask? Because the connection string would now have an extra ‘;’ in its midst:
Data Source=SERVERX;User ID=sa;Password=P@ssW0r;D;Initial Catalog=DBX

To correct this problem, I simply included double quotes at the beginning and at the end of the password in the password field of the wizard.

This worked like a charm, since the connection string in the internal workings of the wizard would now look something like this:
Data Source=SERVERX;User ID=sa;Password=”P@ssW0r;D”Initial Catalog=DBX

Now, this got me thinking: “What if my password contained a semi-colon (;) and a double-quote () ?”
Answer: I didn’t want to find out.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s