• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

Kriztine Mendoza

Software Engineer / Web Developer

  • Home
  • About
  • Work
  • Services
  • Contact Me

SQL

Solved: Truncation Error when Importing from MS Excel to SQL Server

August 28, 2017 by kriztine

Issue

I was trying to import an Excel sheet with cells that contain very long strings (more than 256 characters) to SQL Server through the SQL Server Import Data Wizard.

What I have tried to do

Since the cells contain more than 256 characters, I have changed all varchar’s (or nvarchar) size to MAX. However, it did not solve the issue.

The Solution

Aside from changing the size to MAX, I have transferred one of the rows with a cell that has long strings to the first row so SQL Server would know that, indeed, a particular contains a very long string!

I have read somewhere that you can change something in the registry so SQL Server would automatically treat long strings as nvarchar(MAX), but I haven’t tried that yet.

 

Filed Under: SQL Tagged With: SQL Server

Filter Records using Dynamic SQL Query

August 2, 2016 by kriztine

The Story

I  want to search or filter records from the database based on some parameters like First Name, Company Name, Category etc.

However, using static or hard-coded SQL statement just won’t cut it. I need to write a dynamic SQL statement so I can use a flexible statement that is created and executed on-the-fly or on run-time.

Solution

First, I need to write a stored procedure that accepts different parameters.
Then, I am going to construct the dynamic SQL statement by concatenating strings.
Lastly, I am going to use the EXEC or sp_executesql to execute the created dynamic SQL statement.

Below is a sample implementation of dynamic SQL via stored procedure:


CREATE PROCEDURE [dbo].[PeopleSearch]
@FirstName nvarchar(250),
@LastName nvarchar(700),
@JobTitle varchar(300),
@EmailAddress varchar(300),
@CompanyName nvarchar(250),
@CompanyCity nvarchar(50),
@CompanyState nvarchar(50),
@CompanyZip nvarchar(50),
@Longitude decimal(11, 8),
@Latitude decimal(10, 8),
@Radius decimal(11, 8),
@Category smallint,
@ActiveEmpMin int,
@ActiveEmpMax int,
@CompanyEmailAddress varchar(3000),
@OperatorFlag bit = 0
AS
BEGIN

DECLARE @Where varchar(8000)
DECLARE @Operator varchar(6)
DECLARE @WildCard varchar(1)

IF @OperatorFlag = 0
BEGIN
SET @Operator = ' LIKE '
SET @WildCard = '%'
END
ELSE
BEGIN
SET @Operator = ' = '
SET @WildCard = ''
END

SET @Where = ''

IF LEN(ISNULL(@FirstName, '')) > 0
BEGIN
IF LEN(@Where) > 0
SET @Where = @Where + CHAR(13) + CHAR(9) + 'AND '
ELSE
SET @Where = @Where + CHAR(13) + 'WHERE '
SET @Where = @Where + 'p.FirstName' + @Operator + '''' + @WildCard + @FirstName + @WildCard + ''''
END

IF LEN(ISNULL(@LastName, '')) > 0
BEGIN
IF LEN(@Where) > 0
SET @Where = @Where + CHAR(13) + CHAR(9) + 'AND '
ELSE
SET @Where = @Where + CHAR(13) + 'WHERE '
SET @Where = @Where + 'p.LastName' + @Operator + '''' + @WildCard + @LastName + @WildCard + ''''
END

IF LEN(ISNULL(@JobTitle, '')) > 0
BEGIN
IF LEN(@Where) > 0
SET @Where = @Where + CHAR(13) + CHAR(9) + 'AND '
ELSE
SET @Where = @Where + CHAR(13) + 'WHERE '
SET @Where = @Where + 'p.JobTitle' + @Operator + '''' + @WildCard + @JobTitle + @WildCard + ''''
END

IF LEN(ISNULL(@EmailAddress, '')) > 0
BEGIN
IF LEN(@Where) > 0
SET @Where = @Where + CHAR(13) + CHAR(9) + 'AND '
ELSE
SET @Where = @Where + CHAR(13) + 'WHERE '
SET @Where = @Where + 'p.EmailAddress' + @Operator + '''' + @WildCard + @EmailAddress + @WildCard + ''''
END

IF LEN(ISNULL(@CompanyName, '')) > 0
BEGIN
IF LEN(@Where) > 0
SET @Where = @Where + CHAR(13) + CHAR(9) + 'AND '
ELSE
SET @Where = @Where + CHAR(13) + 'WHERE '
SET @Where = @Where + 's.SPONSOR_NAME' + @Operator + '''' + @WildCard + @CompanyName + @WildCard + ''''
END

IF LEN(ISNULL(@CompanyCity, '')) > 0
BEGIN
IF LEN(@Where) > 0
SET @Where = @Where + CHAR(13) + CHAR(9) + 'AND '
ELSE
SET @Where = @Where + CHAR(13) + 'WHERE '
SET @Where = @Where + 's.SPONSOR_CITY' + @Operator + '''' + @WildCard + @CompanyCity + @WildCard + ''''
END

IF LEN(ISNULL(@CompanyState, '')) > 0
BEGIN
IF LEN(@Where) > 0
SET @Where = @Where + CHAR(13) + CHAR(9) + 'AND '
ELSE
SET @Where = @Where + CHAR(13) + 'WHERE '
SET @Where = @Where + 's.SPONSOR_STATE' + @Operator + '''' + @WildCard + @CompanyState + @WildCard + ''''
END

IF LEN(ISNULL(@CompanyZip, '')) > 0
BEGIN
IF LEN(@Where) > 0
SET @Where = @Where + CHAR(13) + CHAR(9) + 'AND '
ELSE
SET @Where = @Where + CHAR(13) + 'WHERE '
SET @Where = @Where + 's.SPONSOR_ZIP' + @Operator + '''' + @WildCard + @CompanyZip + @WildCard + ''''
END

IF LEN(ISNULL(@Latitude, '')) > 0 AND LEN(ISNULL(@Longitude, '')) > 0 AND LEN(ISNULL(@Radius, '')) > 0
BEGIN
IF LEN(@Where) > 0
SET @Where = @Where + CHAR(13) + CHAR(9) + 'AND '
ELSE
SET @Where = @Where + CHAR(13) + 'WHERE '
SET @Where = @Where + '(s.SPONSOR_LATITUDE Is Not Null AND s.SPONSOR_LONGITUDE Is Not Null)'
SET @Where = @Where + CHAR(13) + CHAR(9) + 'AND '
SET @Where = @Where + 'dbo.GetDistance(' + CONVERT(varchar, @Latitude) + ', ' + CONVERT(varchar, @Longitude) + ', CAST(s.SPONSOR_LATITUDE as float(8)), CAST(s.SPONSOR_LONGITUDE as float(8))) <= ' + CONVERT(varchar, @Radius)
END

IF @Category IS NOT NULL
BEGIN
IF LEN(@Where) > 0
SET @Where = @Where + CHAR(13) + CHAR(9) + 'AND '
ELSE
SET @Where = @Where + CHAR(13) + 'WHERE '
SET @Where = @Where + 's.SPONSOR_BUSINESS_CODE' + ' = ' + @Category
END

IF @ActiveEmpMin IS NOT NULL
BEGIN
IF LEN(@Where) > 0
SET @Where = @Where + CHAR(13) + CHAR(9) + 'AND '
ELSE
SET @Where = @Where + CHAR(13) + 'WHERE '
SET @Where = @Where + 'd.Total_Active_Participants' + ' >= ' + CONVERT(varchar, @ActiveEmpMin)
END

IF @ActiveEmpMax IS NOT NULL
BEGIN
IF LEN(@Where) > 0
SET @Where = @Where + CHAR(13) + CHAR(9) + 'AND '
ELSE
SET @Where = @Where + CHAR(13) + 'WHERE '
SET @Where = @Where + 'd.Total_Active_Participants' + ' <= ' + CONVERT(varchar, @ActiveEmpMax)
END

IF LEN(ISNULL(@CompanyEmailAddress, '')) > 0
BEGIN
IF LEN(@Where) > 0
SET @Where = @Where + CHAR(13) + CHAR(9) + 'AND '
ELSE
SET @Where = @Where + CHAR(13) + 'WHERE '
SET @Where = @Where + 'c.EmailAddress' + @Operator + '''' + @WildCard + @CompanyEmailAddress + @WildCard + ''''
END

DECLARE @Query varchar (MAX)

SET @Query =
'SELECT p.FirstName, p.LastName, p.JobTitle, e.EmailAddress, e.Verified, p.PrimaryContact, p.IsAdmin, p.Url, p.BloombergKeyExecUrl, p.ProfileId
FROM PeopleData d
INNER JOIN Sponsors s
ON d.SPONSOR_ID = s.SPONSOR_ID
INNER JOIN Contacts.Companies c
ON s.SPONSOR_EXTID = c.ExtId
INNER JOIN Contacts.People p
ON c.ExtId = p.ExtId
INNER JOIN Contacts.EmailAddresses e
ON p.ProfileId = e.ProfileId' +
@Where

EXEC (@Query)

END

Filed Under: SQL Tagged With: T-SQL

Convert PCX Image to PNG or JPG format

September 25, 2015 by kriztine

PixConverter

I am currently working on a task where I have to migrate data from Paradox Database (or simply, Paradox DB) to SQL Server Database.

The image files that the Paradox DB is using is in PCX format. And the system I am developing is required to read and display these images. Since no browser can display PCX image (you can see the image formats the browsers support in this link) using the <img> tag, the PCX files have to be converted to another image format that popular browsers support. I have chosen the PNG format as the final image format because it can preserve the fine details with the highest resolution with smaller sizes.

I am using the free software Coffee PixConverter to do the conversion. To save time, I am utilizing its bulk conversion feature. I am also saving the converted images to the same folder as the PCX files, so I would not have the update the current physical paths of the images.

Filed Under: How-To's, SQL, Tools Tagged With: Paradox

Delete Rows with Duplicate Values in a Particular Column

March 4, 2015 by kriztine

WITH CTE AS(
SELECT referenceNo, RN = ROW_NUMBER()OVER(PARTITION BY referenceNo ORDER BY referenceno )
FROM dbo.[Order]
)
DELETE FROM CTE WHERE RN &gt; 1

Filed Under: SQL Tagged With: T-SQL

Primary Sidebar

Recent Posts

  • The tooltip or Intellisense for methods do not appear!
  • Scrollbar doesn’t show on Safari
  • WebResource.axd File Compression
  • X-Frame-Options header and X-Content-Type-Options = nosniff
  • Solved: Truncation Error when Importing from MS Excel to SQL Server

Categories

  • .Net Development (7)
  • Design (4)
  • How-To's (3)
  • Marketing (1)
  • Project Management (2)
  • Projects (1)
  • SQL (4)
  • System Administration and Configuration (2)
  • Tools (8)

Archives

SERVICES
  • Responsive Web Design
  • Custom Web/Software Development
  • Content Management System
  • API Development and Integration
  • Database Design and Management
  • System Configuration and Administration
QUICK LINKS
  • About
  • Services
  • Dev Notes
  • Resources
  • Contact me
GET IN TOUCH
  • kriztine.mendoza
  • +6349.543.8107
  • contact@kriztine.com
  • kriztinesmendoza@gmail.com

© 2022 · All Rights Reserved · Kriztine Mendoza

Attributions · Privacy Policy

"Whatever you do, work at it with all your heart, as working for the Lord..." - Colossians 3:23