The ‘SQL Server Import and Export Wizard’ can fail when trying to import a spreadsheet which was originally created by Open Office and converted by Excel into an Excel format.
Symptom
Symptoms of this problem can be seen when you edit mappings within the Wizard there are always 255 columns with the extra columns being prefixed with an ‘F’.
Even though these extra columns are not selected for import, you will get a violation of a datatype for the first column.
Solution
If this is the case, simply copy and paste the data from one document to a new blank document created by Excel.
Versions
Microsoft SQL Server Management Studio 10.0.2531.0
SQLServer 2008
Excel 2007
OpenOffice
Techie
Bug, SQL
I’ve just discovered yet another glitch with the ‘Import Data’ tool within the ‘Microsoft SQL Server Management Studio’. It took me a while to figure this one out as the error message was of a slightly unhelpful wild goose chase.
Error
If you have a column within excel that contains a mix of both data and null cells, the import tool fails even if the destination column is set to ‘Nullable’ within ‘Column Mappings’.
“Text was truncated or one or more characters had no match in the target code page.”
Solution
The only work around I have found is to separate the null columns and import them separately.
As alternative I think you can place a single quote (’) in each null cell, this forces the excel engine to register the cell as text rather then a null type and import it as an empty piece of data; I’ve used this technique before to force mixed numeric and text fields to be solely text, as using ‘format cell’ and selecting ‘text’ still imports the cell as a numeric value. I haven’t tested it for this scenario, but it sounds plausible that it will have the same effect with null values.
Versions
Microsoft SQL Server Management Studio 10.0.2531.0
SQLServer 2008
Excel 2007
Techie
Bug, SQL
I’ve just installed a new version of the AdWords Editor onto a new Vista machine which had Chrome and Firefox installed.
Both Chrome, Firefox and AdWords Editor use SQLite for their database. I’m not sure whether there is a conflict with the way these are installed, but every now and then the AdWords Editor software crashes, and windows reports that there was an error with SQLite.
I have sucessfully resolved this problem by downloading the latest SQLite binary libraries, and extracting the sqlite3.dll into the Windows System32 directory. Upon reloading the AdWords Editor, everything begins to work fine.
Techie
AdWords, Bug, Quickies, SQL
Recently I attempted to setup a replication service with SQL server 2008, bound to an alternative port. The publication was setup fine, however when attempting to subscribe to the publication from a remote network, I was coming up with the following error.
Cannot connect to Server_Name,xxxx
Sql Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, ‘Server_Name‘. (Replication.Utilites)
To begin with I thought it was because I was entering the IP address/domain of the remote server; Publications weirdly require the connecting hostname to be the name of the servers computer name, what I can only assume is to tighten security maybe. I initially resolved this by adding the ip and computer name to the ‘hosts’ file located ‘%WINDIR%\system32\drivers\etc\hosts’, however I later found that the error was still occurring.
I looked at the error more closely, and saw that it was showing the server name used in the connection, and also what the server name should be at the end of the message, the difference between these two was the port number. Removing the port number and setting the publication server to the default binding demonstrated that this was indeed the problem, that you cannot establish a subscription by including a connection to a port via appending ‘,1234′ to the hostname.
Solution
Initially this seems to be a rather evil restriction in replication, preventing you to subscribe to publications bound to a non-default port number, however SQL Server does enable aliases to be set. To do this follow the following instructions:
- Load up ‘Sql Server Configuration Manager’
- Expand ‘SQL Native Client 10.0 Configuration’ (or whichever SQL version you have), and select ‘Aliases’
- Right click and select ‘New Alias…’
- Under ‘Alias Name’ enter the computer name of the server where the publication is hosted
- Fill in the ‘Port No’ and ‘Server’ with the ip address or domain name
This enables you to simply type the alias within the connection details emitting the need to supply a port number.
Techie
SQL
