Error loading files from temporary folder - see below

• mohamed.mono

  SSC Rookie

  Points: 47

  More actions

  September 20, 2011 at 9:03 am

  #248168

  SQL Execution resulted in following Exceptions: System.Data.SqlClient.SqlException: Cannot resolve collation conflict for column 11 in SELECT statement. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at DotNetNuke.Data.SqlDataProvider.ExecuteADOScript(String SQL) at DotNetNuke.Data.SqlDataProvider.ExecuteScript(String Script, Boolean UseTransactions)

  CREATE procedure dbo.GetEvent @ItemId int, @ModuleId int as select Events.ItemId, Events.ModuleId, Events.Description, Events.DateTime, Events.Title, Events.ExpireDate, 'CreatedByUser' = Users.FirstName + ' ' + Users.LastName, Events.CreatedDate, Events.Every, Events.Period, 'IconFile' = case when Files.FileName is null then Events.IconFile else Files.Folder + Files.FileName end, Events.AltText from Events left outer join Users on Events.CreatedByUser = Users.UserId left outer join Files on Events.IconFile = 'fileid=' + convert(varchar,Files.FileID) where ItemId = @ItemId and ModuleId = @ModuleId System.Data.SqlClient.SqlException: Cannot resolve collation conflict for column 11 in SELECT statement. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at DotNetNuke.Data.SqlDataProvider.ExecuteADOScript(String SQL) at DotNetNuke.Data.SqlDataProvider.ExecuteScript(String Script, Boolean UseTransactions) CREATE procedure dbo.GetEventsByDate @ModuleId int, @StartDate datetime, @EndDate datetime as select Events.ItemId, Events.ModuleId, Events.Description, Events.DateTime, Events.Title, Events.ExpireDate, Events.CreatedByUser, Events.CreatedDate, Events.Every, Events.Period, 'IconFile' = case when Files.FileName is null then Events.IconFile else Files.Folder + Files.FileName end, Events.AltText from Events left outer join Files on Events.IconFile = 'fileid=' + convert(varchar,Files.FileID) where ModuleId = @ModuleId and ( (Period is null and (DateTime >= @StartDate and DateTime <= @EndDate)) or Period is not null ) order by DateTime System.Data.SqlClient.SqlException: Cannot resolve collation conflict for column 11 in SELECT statement. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at DotNetNuke.Data.SqlDataProvider.ExecuteADOScript(String SQL) at DotNetNuke.Data.SqlDataProvider.ExecuteScript(String Script, Boolean UseTransactions) CREATE procedure dbo.GetEvents @ModuleId int as select Events.ItemId, Events.ModuleId, Events.Description, Events.DateTime, Events.Title, Events.ExpireDate, Events.CreatedByUser, Events.CreatedDate, Events.Every, Events.Period, 'IconFile' = case when Files.FileName is null then Events.IconFile else Files.Folder + Files.FileName end, Events.AltText, 'MaxWIdth' = (select max(WIdth) from Events left outer join Files on Events.IconFile = 'fileid=' + convert(varchar,Files.FileID) where ModuleId = @ModuleId and (ExpireDate > getdate() or ExpireDate is null)) from Events left outer join Files on Events.IconFile = 'fileid=' + convert(varchar,Files.FileID) where ModuleId = @ModuleId and (ExpireDate > getdate() or ExpireDate is null) order by DateTime

• Pam Brisjar

  SSChampion

  Points: 12094

  More actions

  September 21, 2011 at 2:26 pm

  #1384807

  This line is the issue:

  'IconFile' = CASE WHEN Files.FileName IS NULL THEN Events.IconFile

  ELSE Files.Folder + Files.FileName

  END ,

  I suspect it's the " Files.Folder + Files.FileName" part.

  Can you give us table definitions.

  Here's a better-formated version of your code for other readers:

  CREATE PROCEDURE dbo.GetEvent

  @ItemId INT ,

  @ModuleId INT

  AS

  SELECT Events.ItemId ,

  Events.ModuleId ,

  Events.Description ,

  Events.DateTime ,

  Events.Title ,

  Events.ExpireDate ,

  'CreatedByUser' = Users.FirstName + ' ' + Users.LastName ,

  Events.CreatedDate ,

  Events.Every ,

  Events.Period ,

  'IconFile' = CASE WHEN Files.FileName IS NULL THEN Events.IconFile

  ELSE Files.Folder + Files.FileName

  END ,

  Events.AltText

  FROM Events

  LEFT OUTER JOIN Users ON Events.CreatedByUser = Users.UserId

  LEFT OUTER JOIN Files ON Events.IconFile = 'fileid='

  + CONVERT(VARCHAR, Files.FileID)

  WHERE ItemId = @ItemId

  AND ModuleId = @ModuleId

  --System.Data.SqlClient.SqlException: Cannot resolve collation conflict for column 11 in SELECT statement.

  --at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

  --at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

  --at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

  --at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

  --at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)

  --at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)

  --at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

  --at DotNetNuke.Data.SqlDataProvider.ExecuteADOScript(String SQL)

  --at DotNetNuke.Data.SqlDataProvider.ExecuteScript(String Script, Boolean UseTransactions)

  CREATE PROCEDURE dbo.GetEventsByDate

  @ModuleId INT ,

  @StartDate DATETIME ,

  @EndDate DATETIME

  AS

  SELECT Events.ItemId ,

  Events.ModuleId ,

  Events.Description ,

  Events.DateTime ,

  Events.Title ,

  Events.ExpireDate ,

  Events.CreatedByUser ,

  Events.CreatedDate ,

  Events.Every ,

  Events.Period ,

  'IconFile' = CASE WHEN Files.FileName IS NULL THEN Events.IconFile

  ELSE Files.Folder + Files.FileName

  END ,

  Events.AltText

  FROM Events

  LEFT OUTER JOIN Files ON Events.IconFile = 'fileid='

  + CONVERT(VARCHAR, Files.FileID)

  WHERE ModuleId = @ModuleId

  AND ( ( Period IS NULL

  AND ( DateTime >= @StartDate

  AND DateTime <= @EndDate

  )

  )

  OR Period IS NOT NULL

  )

  ORDER BY DateTime

  --System.Data.SqlClient.SqlException: Cannot resolve collation conflict for column 11 in SELECT statement.

  --at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

  --at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

  --at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

  --at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

  --at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)

  --at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at DotNetNuke.Data.SqlDataProvider.ExecuteADOScript(String SQL) at DotNetNuke.Data.SqlDataProvider.ExecuteScript(String Script, Boolean UseTransactions)

  --

  CREATE PROCEDURE dbo.GetEvents @ModuleId INT

  AS

  SELECT Events.ItemId ,

  Events.ModuleId ,

  Events.Description ,

  Events.DateTime ,

  Events.Title ,

  Events.ExpireDate ,

  Events.CreatedByUser ,

  Events.CreatedDate ,

  Events.Every ,

  Events.Period ,

  'IconFile' = CASE WHEN Files.FileName IS NULL THEN Events.IconFile

  ELSE Files.Folder + Files.FileName

  END ,

  Events.AltText ,

  'MaxWIdth' = ( SELECT MAX(WIdth)

  FROM Events

  LEFT OUTER JOIN Files ON Events.IconFile = 'fileid='

  + CONVERT(VARCHAR, Files.FileID)

  WHERE ModuleId = @ModuleId

  AND ( ExpireDate > GETDATE()

  OR ExpireDate IS NULL

  )

  )

  FROM Events

  LEFT OUTER JOIN Files ON Events.IconFile = 'fileid='

  + CONVERT(VARCHAR, Files.FileID)

  WHERE ModuleId = @ModuleId

  AND ( ExpireDate > GETDATE()

  OR ExpireDate IS NULL

  )

  ORDER BY DateTime

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736212

  More actions

  September 21, 2011 at 4:52 pm

  #1384889

  you've really got to format your errors and code and separate them out, as well as ask a question in a professional manner if you want help.

  I think you have a few problems. A collation issue is usually a problem with the structure of your table and perhaps tempdb when there are other languages involved. This might help you with that part.

  http://voiceofthedba.wordpress.com/2011/05/23/collation-conflicts-in-a-sql-server-join/

  Pam has some a good possibility as well, but without more information it is hard to know.