Re: CONVERT

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Adam Machanic (amachanic_at_air-worldwide.nospamallowed.com)
Date: 03/23/04 

Date: Tue, 23 Mar 2004 10:09:38 -0500

First of all, please read this article which will show you how to get rid of
the dynamic SQL using a table-valued UDF:

http://www.algonet.se/~sommar/arrays-in-sql.html

Second, you have not posted DDL and therefore it is impossible to know what
datatypes are doing what in this query. I'm also wondering what
GeographyName is doing internally -- could that be your culprit? If you
want to get rid of the nulls, use COALESCE -- look it up in BOL.

"Jaco" <anonymous@discussions.microsoft.com> wrote in message
news:275D67DA-795F-4759-B7C3-71018D03A728@microsoft.com...
> Hello
>
> I am trying to run this SP. The problem is that if one of my fields have
null values it returns with an error 'Implicit conversion from data type
text to nvarchar is not allowed. Use the CONVERT function to run this
query' -
> any help please.
>
>
>
>
>
>
>
>
>
> CREATE PROCEDURE qfm_DiaryFormPMSchedule
>
> @Parms nvarchar(4000)
>
> AS
>
> Declare @QUERY nvarChar(4000)
> set @QUERY = N'Select [Site] = (dbo.GeographyName((Select
Inventory.GeographyID from Geography,Inventory where
Geography.GeographyID=Inventory.GeographyID and
Inventory.InventoryID=InventoryPM.InventoryID),1,NULL)), '
> set @QUERY = @QUERY + N'[InventoryID] as [Asset No], '
> set @QUERY = @QUERY + N'(Select Model from Model where ModelID in (select
ModelID from Inventory where InventoryPM.InventoryID=Inventory.InventoryID))
as Model, '
> set @QUERY = @QUERY + N'(Select EquipmentReference from Inventory where
Inventory.InventoryID=InventoryPM.InventoryID) as [Equipment Ref], '
> set @QUERY = @QUERY + N'(Select Serial from Inventory where
Inventory.InventoryID=InventoryPM.InventoryID) as [Serial], '
> set @QUERY = @QUERY + N'[PMDescription] as [PM Description], '
> set @QUERY = @QUERY + N'[PMStartDate] as [Start Date], '
> set @QUERY = @QUERY + N'[EndDate] as [End Date], '
> set @QUERY = @QUERY + N'[LastPMDate] as [Last Inspection], '
> set @QUERY = @QUERY + N'[PMFrequency] as Frequency, '
> set @QUERY = @QUERY + N'[DurationDays] as [Duration (Days)], '
> set @QUERY = @QUERY + N'[DurationHours] as [Duration (Hours)], '
> set @QUERY = @QUERY + N'[DurationMins] as [Duration (Mins)], '
> set @QUERY = @QUERY + N'(Select Name from Agent where
InventoryPM.AgentID=Agent.AgentID) as Contractor, '
> set @QUERY = @QUERY + N'(Select Name from Priority where
InventoryPM.PriorityID=Priority.PriorityID) as Priority, '
> set @QUERY = @QUERY + N'[Instructions] as [Instructions], '
> set @QUERY = @QUERY + N'[Remarks] as [Remarks], '
> set @QUERY = @QUERY + N'InventoryID as [NavigationID] '
> set @QUERY = @QUERY + N'FROM InventoryPM '
> set @QUERY = @QUERY + N'WHERE InventoryPMID IN (' + @Parms + ')'
>
> --print @QUERY
>
> Exec sp_executesql @QUERY
> GO
>

Relevant Pages

  • Re: Subform Requery Doesnt Show Updates to Data
    ... I did not bind the main form to a query and I ... Private Sub cmdClose_Click ... Get rid of the DoCmd.RunCommand acCmdSaveRecord line. ... About the only other idea I have about the delay is maybe ...
    (microsoft.public.access.formscoding)
  • Re: Date Query
    ... Query one to get FirstDate in Month for each RID ... requested input value for year ... an expression that will tell me the FIRST Weight in January (of the ...
    (microsoft.public.access.queries)
  • Re: Tips to speed up query with aggregate functions on millions of
    ... How can I get rid of the date function? ... Get rid of the functions in your group by and order clause so that the ... covering) for queries that hit more than 1-2% of the total rows in a table. ... If your large query is doing index seeks and bookmark lookups, ...
    (microsoft.public.sqlserver.programming)
  • Re: Unidentified Publisher etc. Message
    ... Yes, my friend, I read those posts myself before posting my query, and did ... The following thread should give you some insight (as a side note, ... All applications that were compared, ... the difference and to get rid of this annoying message. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Unidentified Publisher etc. Message
    ... Yes, my friend, I read those posts myself before posting my query, and did ... The following thread should give you some insight (as a side note, ... All applications that were compared, ... the difference and to get rid of this annoying message. ...
    (microsoft.public.dotnet.languages.csharp)