Prev: Newbie Installing AdventrueWorksDW2008 in another Drive
Next: Parse Name Field into First Name Last Name
From: Tim P on 8 Dec 2009 10:32
Hi

A few years back some programmers in India wrote me a VB application to
export data from a sql 2k db. One part of this programme involves invoking a
stored procedure. Earlier this year my host compulsorily upgraded my db to
sql 2008 and since then this part of the app is broken. When I start the
export it (the VB app) gives me the error:

Run time error '-2147217900 (80040e14)'
Implicit conversion of varchar value to varchar cannot be performed because
the collation of the value is unresolved due to a collation conflict.

If I try to execute the SP directly on the server I get an error message
related to the date delimeter / (I'm in the UK).

The SP is:

USE [dbExpo]
GO
/****** Object: StoredProcedure [dbo].[sp_Magazine] Script Date:
12/08/2009 15:23:38 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[sp_Magazine] @startdate smalldatetime,@enddate
smalldatetime
as
SELECT
TOP 100 PERCENT
CASE WHEN tblExhibition.ExShortNm IS NULL THEN tblExhibition.ExLongNm WHEN
tblExhibition.ExShortNm = '' THEN tblExhibition.ExLongNm
ELSE tblExhibition.ExShortNm
END AS ShowNm,
dbo.tblExhibition.StartDate, dbo.tblExhibition.EndDate, dbo.tblTown.TownNm,
dbo.tblVenue.VenShortNm, dbo.tblCountry.CountryID, dbo.tblExhibition.ExURL,
dbo.tblExhibition.ExURLPubYN, dbo.tblCompany.CoURL,
dbo.tblCompany.CoURLPubYN,
CASE WHEN dbo.tblExhibition.ExURL IS NULL AND dbo.tblCompany.CoURLPubYN =
'T' THEN dbo.tblCompany.CoURL
WHEN dbo.tblExhibition.ExURL IS NOT NULL AND dbo.tblExhibition.ExURLPubYN =
'T' THEN dbo.tblExhibition.ExURL
END AS ShowURL
FROM dbo.tblCountry INNER JOIN dbo.tblTown ON dbo.tblCountry.CountryID =
dbo.tblTown.CountryID INNER JOIN dbo.tblVenue ON dbo.tblTown.TownID =
dbo.tblVenue.VenTownID INNER JOIN dbo.tblExhibition ON dbo.tblVenue.VenueID =
dbo.tblExhibition.VenueID INNER JOIN dbo.tblCompany ON
dbo.tblExhibition.OrgCoID = dbo.tblCompany.CompanyID
WHERE (dbo.tblCountry.CountryID = 75) and (dbo.tblExhibition.StartDate >=
@startdate and dbo.tblExhibition.EndDate <= @enddate)
ORDER BY dbo.tblExhibition.StartDate, dbo.tblExhibition.EndDate,
dbo.tblTown.TownNm

The account which executes the SP has execute permissions and is the same
account which performs 7 other date-related tasks within the same app.

If I use SSIS to make a local copy of the db on my test server, I can then
do the export fine, using the same login.

Can anyone give me any pointers how to fix this, please?

Thanks in advance for any pointers.

TP
From: Sylvain Lafontaine on 8 Dec 2009 12:58
Your date problem when you execute this SP directly on the server might be
related to the default language associated with your logon on the remote
server. Compare it with what you have on your test server. Normally, this
shouldn't be a problem if you are using exclusively datetime and
smalldatetime but I won't be surprised if some of the dates on your database
are stored as strings of characters instead of the datetime format.

For the collation problem, the collation for many of your fields must now be
different from the default collation of the newly installed database or be
different between themselves. Take a look at the default collation of the
server, of the database and the collations on the fields in your tables.
Without seeing the design of your tables in details, taking a look at your
SP is useless for solving this kind of problem.

Finally, don't use the prefix "sp_" for your SP. This prefix has a special
meaning to SQL-Server and using it will slightly degrade the performance of
your SP and can lead to some subtle bugs in some cases.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)


"Tim P" <TimP(a)discussions.microsoft.com> wrote in message
news:12F56460-3AAF-4250-B9F7-4308DE0C4803(a)microsoft.com...
> Hi
>
> A few years back some programmers in India wrote me a VB application to
> export data from a sql 2k db. One part of this programme involves invoking
> a
> stored procedure. Earlier this year my host compulsorily upgraded my db to
> sql 2008 and since then this part of the app is broken. When I start the
> export it (the VB app) gives me the error:
>
> Run time error '-2147217900 (80040e14)'
> Implicit conversion of varchar value to varchar cannot be performed
> because
> the collation of the value is unresolved due to a collation conflict.
>
> If I try to execute the SP directly on the server I get an error message
> related to the date delimeter / (I'm in the UK).
>
> The SP is:
>
> USE [dbExpo]
> GO
> /****** Object: StoredProcedure [dbo].[sp_Magazine] Script Date:
> 12/08/2009 15:23:38 ******/
> SET ANSI_NULLS OFF
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> ALTER PROCEDURE [dbo].[sp_Magazine] @startdate smalldatetime,@enddate
> smalldatetime
> as
> SELECT
> TOP 100 PERCENT
> CASE WHEN tblExhibition.ExShortNm IS NULL THEN tblExhibition.ExLongNm WHEN
> tblExhibition.ExShortNm = '' THEN tblExhibition.ExLongNm
> ELSE tblExhibition.ExShortNm
> END AS ShowNm,
> dbo.tblExhibition.StartDate, dbo.tblExhibition.EndDate,
> dbo.tblTown.TownNm,
> dbo.tblVenue.VenShortNm, dbo.tblCountry.CountryID,
> dbo.tblExhibition.ExURL,
> dbo.tblExhibition.ExURLPubYN, dbo.tblCompany.CoURL,
> dbo.tblCompany.CoURLPubYN,
> CASE WHEN dbo.tblExhibition.ExURL IS NULL AND dbo.tblCompany.CoURLPubYN =
> 'T' THEN dbo.tblCompany.CoURL
> WHEN dbo.tblExhibition.ExURL IS NOT NULL AND dbo.tblExhibition.ExURLPubYN
> =
> 'T' THEN dbo.tblExhibition.ExURL
> END AS ShowURL
> FROM dbo.tblCountry INNER JOIN dbo.tblTown ON dbo.tblCountry.CountryID =
> dbo.tblTown.CountryID INNER JOIN dbo.tblVenue ON dbo.tblTown.TownID =
> dbo.tblVenue.VenTownID INNER JOIN dbo.tblExhibition ON
> dbo.tblVenue.VenueID =
> dbo.tblExhibition.VenueID INNER JOIN dbo.tblCompany ON
> dbo.tblExhibition.OrgCoID = dbo.tblCompany.CompanyID
> WHERE (dbo.tblCountry.CountryID = 75) and (dbo.tblExhibition.StartDate >=
> @startdate and dbo.tblExhibition.EndDate <= @enddate)
> ORDER BY dbo.tblExhibition.StartDate, dbo.tblExhibition.EndDate,
> dbo.tblTown.TownNm
>
> The account which executes the SP has execute permissions and is the same
> account which performs 7 other date-related tasks within the same app.
>
> If I use SSIS to make a local copy of the db on my test server, I can then
> do the export fine, using the same login.
>
> Can anyone give me any pointers how to fix this, please?
>
> Thanks in advance for any pointers.
>
> TP


From: Erland Sommarskog on 8 Dec 2009 18:13
Tim P (TimP(a)discussions.microsoft.com) writes:

> If I try to execute the SP directly on the server I get an error message
> related to the date delimeter / (I'm in the UK).

Then you are calling the procedure incorrectly; the procedure
itself does not perform any date-string handling as far as I can see.
Pass dates as YYYYMMDD, this format always works.

>Implicit conversion of varchar value to varchar cannot be performed because
>the collation of the value is unresolved due to a collation conflict.

I would firstmost suspect these, and check that the columns returned
in the THEN branches have the same collation. You can view this with
sp_help.


> CASE WHEN tblExhibition.ExShortNm IS NULL THEN tblExhibition.ExLongNm WHEN
> tblExhibition.ExShortNm = '' THEN tblExhibition.ExLongNm
> ELSE tblExhibition.ExShortNm
> END AS ShowNm,
>
> CASE WHEN dbo.tblExhibition.ExURL IS NULL AND dbo.tblCompany.CoURLPubYN =
> 'T' THEN dbo.tblCompany.CoURL
> WHEN dbo.tblExhibition.ExURL IS NOT NULL
> AND dbo.tblExhibition.ExURLPubYN =
> 'T' THEN dbo.tblExhibition.ExURL
> END AS ShowURL


Then again, I'm not fully sure that this error even comes from SQL Server -
I have not seen that one before.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: Newbie Installing AdventrueWorksDW2008 in another Drive
Next: Parse Name Field into First Name Last Name