Need NULL Rows
Dec 11, 2007
I'm banging my head trying to figure out why this simple query isn't returning null rows for sales. I'm using a calendar table (MetaDates) that should return all 12 months whether there are sales or not (null). I've tried several combinations without any success. Any help with the below query is much appreciated.
SELECT Customer.Country, MetaDates.[Month], MetaDates.[Year], isNull(SUM(Order_Line_Invoice.Sales),0)
FROM Order_Line_Invoice inner join Customer on customer.custid = Order_Line_Invoice.custid RIGHT OUTER JOIN
MetaDates ON MetaDates.[Date] = Order_Line_Invoice.InvoiceDate
WHERE (MetaDates.[Year] = 2007) AND (Customer.Country IN ('ar'))
GROUP BY MetaDates.[Year], MetaDates.[Month], Customer.Country
ORDER BY MetaDates.[Year], MetaDates.[Month]
View 3 Replies
ADVERTISEMENT
Jul 15, 2015
I have a flat file with the following columns
SampleID Rep_Number Product Protein Fat Solids
In the flat file SampleID and Product are populated in the first row only, rest of the rows only have values for Rep_Number, Protein, Fat, Solids.
SampleID and Product are blank for the rest of the rows. So my task is to fill those blank rows with the first row that has the sampleID and Product and load into the table.
View 7 Replies
View Related
Jan 25, 2008
I have a pivot transform that pivots a batch type. After the pivot, each batch type has its own row with null values for the other batch types that were pivoted. I want to group two fields and max() the remaining batch types so that the multiple rows are displayed on one row. I tried using the aggregate transform, but since the batch type field is a string, the max() function fails in the package. Is there another transform or can I use the aggragate transform another way so that the max() will work on a string?
-- Ryan
View 7 Replies
View Related
Sep 21, 2015
It's been a while since I last posted on here, about 4 years maybe more. Anyway I'm happy to be back. I have an existing query that I needed to add two columns to. I did that but now my dilemma is to have the query NOT return NULL values in a particular column (pd.premium_amount). I tried adding WHERE pd.premium_amount IS NOT NULL and that did not do the trick. I was thinking about trying to use CASE statement but didn't really see that as being an option. Below is my code.
USE [AmLink_DW]
DECLARE @Bil_Grps TABLE (bil_grp_id int, bil_grp_sname varchar(50))
INSERT INTO @Bil_Grps (bil_grp_id, bil_grp_sname)
SELECT *
FROM (
VALUES
[Code] .....
View 9 Replies
View Related
Nov 17, 2007
Hi,
I have to import data from Excel to SQL Server using SSIS. My fields in sql don't take null values however for some reason i don't understand I see that the SSIS is also trying to import null rows into the database. For example I have the following excel file:
Name Postcode State
XXX 123 ON
YXX 345 IO
The rows following the last line are all blank but when I preview the Excel Source data flow task it returns the empty rows as null and therfore, my package fails as db fields don't accept null values.
How can I prevent SSIS from taking null rows? Can I maybe write a query in SSIS to say only to take rows where postcode<>null ?
Thanks in advance for any help i get :-)
<?xml version="1.0"?><DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:ExecutableType="MSDTS.Package.1"><DTS:Property DTS:Name="PackageFormatVersion">2</DTS:Property><DTS:Property DTS:Name="VersionComments"></DTS:Property><DTS:Property DTS:Name="CreatorName">BUSINESS-SOLUTIavital</DTS:Property><DTS:Property DTS:Name="CreatorComputerName">NADINLEP</DTS:Property><DTS:Property DTS:Name="CreationDate" DTS:DataType="7">9/20/2007 3:16:03 PM</DTS:Property><DTS:Property DTS:Name="PackageType">5</DTS:Property><DTS:Property DTS:Name="ProtectionLevel">1</DTS:Property><DTS:Property DTS:Name="MaxConcurrentExecutables">-1</DTS:Property><DTS:Property DTS:Name="PackagePriorityClass">0</DTS:Property><DTS:Property DTS:Name="VersionMajor">1</DTS:Property><DTS:Property DTS:Name="VersionMinor">0</DTS:Property><DTS:Property DTS:Name="VersionBuild">10</DTS:Property><DTS:Property DTS:Name="VersionGUID">{C9421EB6-00F3-4CB6-9D51-10C744818FE8}</DTS:Property><DTS:Property DTS:Name="EnableConfig">0</DTS:Property><DTS:Property DTS:Name="CheckpointFileName"></DTS:Property><DTS:Property DTS:Name="SaveCheckpoints">0</DTS:Property><DTS:Property DTS:Name="CheckpointUsage">0</DTS:Property><DTS:Property DTS:Name="SuppressConfigurationWarnings">0</DTS:Property>
<DTS:ConnectionManager><DTS:Property DTS:Name="DelayValidation">0</DTS:Property><DTS:Property DTS:Name="ObjectName">BS-SERVER1BS.AHT_DW</DTS:Property><DTS:Property DTS:Name="DTSID">{E5A37824-5AE6-4EFF-9714-8D2B16C31C78}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName">OLEDB</DTS:Property><DTS:ObjectData><DTS:ConnectionManager><DTS:Property DTS:Name="Retain">0</DTS:Property><DTS:Property DTS:Name="ConnectionString">Data Source=NADINLEP;Initial Catalog=AHT_DW;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;</DTS:Property></DTS:ConnectionManager></DTS:ObjectData></DTS:ConnectionManager>
<DTS:ConnectionManager><DTS:Property DTS:Name="DelayValidation">0</DTS:Property><DTS:Property DTS:Name="ObjectName">Excel Connection Manager</DTS:Property><DTS:Property DTS:Name="DTSID">{127C18FB-4D7B-4A91-8D1E-8D1F0ABB2B3F}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName">EXCEL</DTS:Property><DTS:ObjectData><DTS:ConnectionManager><DTS:Property DTS:Name="Retain">0</DTS:Property><DTS:Property DTS:Name="ConnectionString">Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Documents and SettingsavitalMy DocumentsAHTAccount Codes.xls;Extended Properties="EXCEL 8.0;HDR=YES";</DTS:Property></DTS:ConnectionManager></DTS:ObjectData></DTS:ConnectionManager>
<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><Package xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"><dwd:DtsControlFlowDiagram><dwd:BoundingTop>1000</dwd:BoundingTop><dwd:Layout><dds>
<diagram fontclsid="{0BE35203-8F91-11CE-9DE3-00AA004BB851}" mouseiconclsid="{0BE35204-8F91-11CE-9DE3-00AA004BB851}" defaultlayout="Microsoft.DataWarehouse.Layout.GraphLayout" defaultlineroute="Microsoft.DataWarehouse.Layout.GraphLayout" version="7" nextobject="5" scale="100" pagebreakanchorx="0" pagebreakanchory="0" pagebreaksizex="0" pagebreaksizey="0" scrollleft="0" scrolltop="0" gridx="150" gridy="150" marginx="1000" marginy="1000" zoom="100" x="26405" y="7832" backcolor="15334399" defaultpersistence="2" PrintPageNumbersMode="3" PrintMarginTop="0" PrintMarginBottom="635" PrintMarginLeft="0" PrintMarginRight="0" marqueeselectionmode="1" mousepointer="0" snaptogrid="0" autotypeannotation="1" showscrollbars="0" viewpagebreaks="0" donotforceconnectorsbehindshapes="1" backpictureclsid="{00000000-0000-0000-0000-000000000000}">
<font>
<ddsxmlobjectstreamwrapper binary="01010000900180380100065461686f6d61" />
</font>
<mouseicon>
<ddsxmlobjectstreamwrapper binary="6c74000000000000" />
</mouseicon>
</diagram>
<layoutmanager>
<ddsxmlobj />
</layoutmanager>
<ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.1" tooltip="Data Flow Task" left="0" top="1000" logicalid="2" controlid="1" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0">
<control>
<ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" />
</control>
<layoutobject>
<ddsxmlobj>
<property name="LogicalObject" value="{8A45A222-5E60-4F75-93A7-7BCE950E56DE}" vartype="8" />
<property name="ShowConnectorSource" value="0" vartype="2" />
</ddsxmlobj>
</layoutobject>
<shape groupshapeid="0" groupnode="0" />
</ddscontrol>
</dds></dwd:Layout></dwd:DtsControlFlowDiagram></Package></DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{8707563F-57F7-4679-BFC4-6EC66F6A5BD3}</DTS:Property><DTS:Property DTS:Name="DTSID">{5DE9985A-8AEC-4571-8CBD-AE6A21F557ED}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable>
<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><TaskHost xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"><dwd:DtsDataFlowDiagram><dwd:BoundingTop>2175</dwd:BoundingTop><dwd:Layout><dds>
<diagram fontclsid="{0BE35203-8F91-11CE-9DE3-00AA004BB851}" mouseiconclsid="{0BE35204-8F91-11CE-9DE3-00AA004BB851}" defaultlayout="Microsoft.DataWarehouse.Layout.GraphLayout" defaultlineroute="Microsoft.DataWarehouse.Layout.GraphLayout" version="7" nextobject="13" scale="100" pagebreakanchorx="0" pagebreakanchory="0" pagebreaksizex="0" pagebreaksizey="0" scrollleft="0" scrolltop="1302" gridx="150" gridy="150" marginx="1000" marginy="1000" zoom="100" x="17568" y="11456" backcolor="15334399" defaultpersistence="2" PrintPageNumbersMode="3" PrintMarginTop="0" PrintMarginBottom="635" PrintMarginLeft="0" PrintMarginRight="0" marqueeselectionmode="1" mousepointer="0" snaptogrid="0" autotypeannotation="1" showscrollbars="0" viewpagebreaks="0" donotforceconnectorsbehindshapes="0" backpictureclsid="{00000000-0000-0000-0000-000000000000}">
<font>
<ddsxmlobjectstreamwrapper binary="01000000900144420100065461686f6d61" />
</font>
<mouseicon>
<ddsxmlobjectstreamwrapper binary="6c74000000000000" />
</mouseicon>
</diagram>
<layoutmanager>
<ddsxmlobj />
</layoutmanager>
<ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.1" tooltip="Excel Source" left="0" top="2175" logicalid="4" controlid="1" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0">
<control>
<ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" />
</control>
<layoutobject>
<ddsxmlobj>
<property name="LogicalObject" value="{8A45A222-5E60-4F75-93A7-7BCE950E56DE}/components/366" vartype="8" />
<property name="ShowConnectorSource" value="0" vartype="2" />
</ddsxmlobj>
</layoutobject>
<shape groupshapeid="0" groupnode="0" />
</ddscontrol>
<ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.1" tooltip="OLE DB Destination" left="26" top="4715" logicalid="5" controlid="2" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0">
<control>
<ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" />
</control>
<layoutobject>
<ddsxmlobj>
<property name="LogicalObject" value="{8A45A222-5E60-4F75-93A7-7BCE950E56DE}/components/413" vartype="8" />
<property name="ShowConnectorSource" value="0" vartype="2" />
</ddsxmlobj>
</layoutobject>
<shape groupshapeid="0" groupnode="0" />
</ddscontrol>
<ddscontrol controlprogid="MSDDS.Polyline" left="1400" top="2940" logicalid="6" controlid="3" masterid="0" hint1="0" hint2="0" width="825" height="2275" noresize="0" nomove="0" nodefaultattachpoints="1" autodrag="0" usedefaultiddshape="0" selectable="1" showselectionhandles="0" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0">
<control>
<ddsxmlobj>
<polyline endtypedst="3" endtypesrc="1" usercolor="32768" linestyle="0" linerender="1" customendtypedstid="0" customendtypesrcid="0" adornsvisible="1" />
</ddsxmlobj>
</control>
<layoutobject>
<ddsxmlobj>
<property name="LogicalObject" value="{8A45A222-5E60-4F75-93A7-7BCE950E56DE}/paths/430" vartype="8" />
<property name="Virtual" value="0" vartype="11" />
<property name="VisibleAP" value="0" vartype="3" />
</ddsxmlobj>
</layoutobject>
<connector lineroutestyle="Microsoft.DataWarehouse.Layout.GraphLayout" sourceid="1" destid="2" sourceattachpoint="7" destattachpoint="6" segmenteditmode="0" bendpointeditmode="0" bendpointvisibility="2" relatedid="0" virtual="0">
<point x="1799" y="3339" />
<point x="1799" y="4027" />
<point x="1825" y="4027" />
<point x="1825" y="4715" />
</connector>
</ddscontrol>
</dds></dwd:Layout><dwd:PersistedViewPortTop>1302</dwd:PersistedViewPortTop></dwd:DtsDataFlowDiagram><dwd:DtsComponentDesignerPropertiesList><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">366TableInfoObjectType</dwd:key><dwd:value xsi:type="xsd:string">Table</dwd:value></dwd:DtsComponentDesignTimeProperty><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">413TableInfoObjectType</dwd:key><dwd:value xsi:type="xsd:string">Table</dwd:value></dwd:DtsComponentDesignTimeProperty><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">32TableInfoObjectType</dwd:key><dwd:value xsi:type="xsd:string">Table</dwd:value></dwd:DtsComponentDesignTimeProperty><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">9DataSourceViewID</dwd:key></dwd:DtsComponentDesignTimeProperty><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">32DataSourceViewID</dwd:key></dwd:DtsComponentDesignTimeProperty><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">9TableInfoObjectType</dwd:key><dwd:value xsi:type="xsd:string">Table</dwd:value></dwd:DtsComponentDesignTimeProperty><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">366DataSourceViewID</dwd:key></dwd:DtsComponentDesignTimeProperty><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">413DataSourceViewID</dwd:key></dwd:DtsComponentDesignTimeProperty><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">103DataSourceViewID</dwd:key></dwd:DtsComponentDesignTimeProperty><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">103TableInfoObjectType</dwd:key><dwd:value xsi:type="xsd:string">Table</dwd:value></dwd:DtsComponentDesignTimeProperty></dwd:DtsComponentDesignerPropertiesList></TaskHost></DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{8A45A222-5E60-4F75-93A7-7BCE950E56DE}</DTS:Property><DTS:Property DTS:Name="DTSID">{AD0D1824-9E01-4A79-8938-352FF3AC648F}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable>
<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><PipelinePath xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"><dwd:PathAnnotation>AsNeeded</dwd:PathAnnotation><dwd:DestinationName>OLE DB Destination Input</dwd:DestinationName><dwd:SourceName>Excel Source Output</dwd:SourceName></PipelinePath></DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{8A45A222-5E60-4F75-93A7-7BCE950E56DE}-430</DTS:Property><DTS:Property DTS:Name="DTSID">{8F8C297C-23B0-4AD0-9E76-7D23440CA867}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">1033</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions>
<DTS:Executable DTS:ExecutableType="DTS.Pipeline.1"><DTS:Property DTS:Name="ExecutionLocation">0</DTS:Property><DTS:Property DTS:Name="ExecutionAddress"></DTS:Property><DTS:Property DTS:Name="TaskContact">Performs high-performance data extraction, transformation and loading;Microsoft Corporation; Microsoft SQL Server v9; (C) 2004 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;1</DTS:Property><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">-1</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions><DTS:Property DTS:Name="ObjectName">Import AccountsDim sheet</DTS:Property><DTS:Property DTS:Name="DTSID">{8A45A222-5E60-4F75-93A7-7BCE950E56DE}</DTS:Property><DTS:Property DTS:Name="Description">Data Flow Task</DTS:Property><DTS:Property DTS:Name="CreationName">DTS.Pipeline.1</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property><DTS:ObjectData><pipeline id="0" name="pipelineXml" description="pipelineXml" defaultBufferMaxRows="10000" engineThreads="5" defaultBufferSize="10485760" BLOBTempStoragePath="" bufferTempStoragePath="" runInOptimizedMode="true">
<components>
<component id="366" name="Excel Source" componentClassID="{B551FCA8-23BD-4719-896F-D8F352A5283C}" description="Excel Source" localeId="-1" usesDispositions="true" validateExternalMetadata="True" version="1" pipelineVersion="0" contactInfo="Excel Source;Microsoft Corporation;Microsoft SqlServer v9; (C) 2005 Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;1">
<properties>
<property id="367" name="CommandTimeout" dataType="System.Int32" state="default" isArray="false" description="The number of seconds before a command times out. A value of 0 indicates an infinite time-out." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">0</property>
<property id="368" name="OpenRowset" dataType="System.String" state="default" isArray="false" description="Specifies the name of the database object used to open a rowset." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">Sheet1$</property>
<property id="369" name="OpenRowsetVariable" dataType="System.String" state="default" isArray="false" description="Specifies the variable that contains the name of the database object used to open a rowset." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></property>
<property id="370" name="SqlCommand" dataType="System.String" state="default" isArray="false" description="The SQL command to be executed." typeConverter="" UITypeEditor="Microsoft.DataTransformationServices.Controls.ModalMultilineStringEditor, Microsoft.DataTransformationServices.Controls, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" containsID="false" expressionType="None"></property>
<property id="371" name="SqlCommandVariable" dataType="System.String" state="default" isArray="false" description="The variable that contains the SQL command to be executed." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></property>
<property id="372" name="AccessMode" dataType="System.Int32" state="default" isArray="false" description="Specifies the mode used to access the database." typeConverter="AccessMode" UITypeEditor="" containsID="false" expressionType="None">0</property>
<property id="378" name="ParameterMapping" dataType="System.String" state="default" isArray="false" description="The mapping from parameters in the SQL command to variables." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></property></properties>
<connections>
<connection id="373" name="OleDbConnection" description="The OLE DB runtime connection used to access the database." connectionManagerID="{127C18FB-4D7B-4A91-8D1E-8D1F0ABB2B3F}"/></connections>
<outputs>
<output id="374" name="Excel Source Output" description="" exclusionGroup="0" synchronousInputId="0" deleteOutputOnPathDetached="false" hasSideEffects="false" dangling="false" isErrorOut="false" isSorted="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><outputColumns>
<outputColumn id="396" name="îôúç çùáåï" description="" lineageId="396" precision="0" scale="0" length="255" dataType="wstr" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="395"/>
<outputColumn id="399" name="ùí äçùáåï" description="" lineageId="399" precision="0" scale="0" length="255" dataType="wstr" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="398"/>
<outputColumn id="405" name="F5" description="" lineageId="405" precision="0" scale="0" length="255" dataType="wstr" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="404"/>
<outputColumn id="408" name="A#T" description="" lineageId="408" precision="0" scale="0" length="255" dataType="wstr" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="407"/>
<outputColumn id="411" name="A#C" description="" lineageId="411" precision="0" scale="0" length="255" dataType="wstr" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="410"/></outputColumns><externalMetadataColumns isUsed="True">
<externalMetadataColumn id="392" name="÷åã îéåï" description="" precision="0" scale="0" length="0" dataType="r8" codePage="0"/>
<externalMetadataColumn id="395" name="îôúç çùáåï" description="" precision="0" scale="0" length="255" dataType="wstr" codePage="0"/>
<externalMetadataColumn id="398" name="ùí äçùáåï" description="" precision="0" scale="0" length="255" dataType="wstr" codePage="0"/>
<externalMetadataColumn id="401" name="F4" description="" precision="0" scale="0" length="255" dataType="wstr" codePage="0"/>
<externalMetadataColumn id="404" name="F5" description="" precision="0" scale="0" length="255" dataType="wstr" codePage="0"/>
<externalMetadataColumn id="407" name="A#T" description="" precision="0" scale="0" length="255" dataType="wstr" codePage="0"/>
<externalMetadataColumn id="410" name="A#C" description="" precision="0" scale="0" length="255" dataType="wstr" codePage="0"/></externalMetadataColumns></output>
<output id="375" name="Excel Source Error Output" description="" exclusionGroup="0" synchronousInputId="0" deleteOutputOnPathDetached="false" hasSideEffects="false" dangling="false" isErrorOut="true" isSorted="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><outputColumns>
<outputColumn id="397" name="îôúç çùáåï" description="" lineageId="397" precision="0" scale="0" length="255" dataType="wstr" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>
<outputColumn id="400" name="ùí äçùáåï" description="" lineageId="400" precision="0" scale="0" length="255" dataType="wstr" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>
<outputColumn id="406" name="F5" description="" lineageId="406" precision="0" scale="0" length="255" dataType="wstr" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>
<outputColumn id="409" name="A#T" description="" lineageId="409" precision="0" scale="0" length="255" dataType="wstr" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>
<outputColumn id="412" name="A#C" description="" lineageId="412" precision="0" scale="0" length="255" dataType="wstr" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>
<outputColumn id="376" name="ErrorCode" description="" lineageId="376" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="1" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>
<outputColumn id="377" name="ErrorColumn" description="" lineageId="377" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="2" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/></outputColumns><externalMetadataColumns isUsed="False"/></output>
</outputs>
</component>
<component id="413" name="OLE DB Destination" componentClassID="{E2568105-9550-4F71-A638-B7FE42E66922}" description="OLE DB Destination" localeId="-1" usesDispositions="true" validateExternalMetadata="True" version="4" pipelineVersion="0" contactInfo="OLE DB Destination;Microsoft Corporation;Microsoft SqlServer v9; (C) 2005 Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;4">
<properties>
<property id="414" name="CommandTimeout" dataType="System.Int32" state="default" isArray="false" description="The number of seconds before a command times out. A value of 0 indicates an infinite time-out." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">0</property>
<property id="415" name="OpenRowset" dataType="System.String" state="default" isArray="false" description="Specifies the name of the database object used to open a rowset." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">[dbo].[AccountsDim]</property>
<property id="416" name="OpenRowsetVariable" dataType="System.String" state="default" isArray="false" description="Specifies the variable that contains the name of the database object used to open a rowset." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></property>
<property id="417" name="SqlCommand" dataType="System.String" state="default" isArray="false" description="The SQL command to be executed." typeConverter="" UITypeEditor="Microsoft.DataTransformationServices.Controls.ModalMultilineStringEditor, Microsoft.DataTransformationServices.Controls, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" containsID="false" expressionType="None"></property>
<property id="418" name="DefaultCodePage" dataType="System.Int32" state="default" isArray="false" description="Specifies the column code page to use when code page information is unavailable from the data source." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">1252</property>
<property id="419" name="AlwaysUseDefaultCodePage" dataType="System.Boolean" state="default" isArray="false" description="Forces the use of the DefaultCodePage property value when describing character data." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property>
<property id="420" name="AccessMode" dataType="System.Int32" state="default" isArray="false" description="Specifies the mode used to access the database." typeConverter="AccessMode" UITypeEditor="" containsID="false" expressionType="None">3</property>
<property id="422" name="FastLoadKeepIdentity" dataType="System.Boolean" state="default" isArray="false" description="Indicates whether the values supplied for identity columns will be copied to the destination. If false, values for identity columns will be auto-generated at the destination. Applies only if fast load is turned on." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property>
<property id="423" name="FastLoadKeepNulls" dataType="System.Boolean" state="default" isArray="false" description="Indicates whether the columns containing null will have null inserted in the destination. If false, columns containing null will have their default values inserted at the destinaton. Applies only if fast load is turned on." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property>
<property id="424" name="FastLoadOptions" dataType="System.String" state="default" isArray="false" description="Specifies options to be used with fast load. Applies only if fast load is turned on." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">TABLOCK,CHECK_CONSTRAINTS</property>
<property id="425" name="FastLoadMaxInsertCommitSize" dataType="System.Int32" state="default" isArray="false" description="Specifies when commits are issued during data insertion. A value of 0 specifies that one commit will be issued at the end of data insertion. Applies only if fast load is turned on." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">0</property></properties>
<connections>
<connection id="421" name="OleDbConnection" description="The OLE DB runtime connection used to access the database." connectionManagerID="{E5A37824-5AE6-4EFF-9714-8D2B16C31C78}"/></connections>
<inputs>
<input id="426" name="OLE DB Destination Input" description="" hasSideEffects="true" dangling="false" errorOrTruncationOperation="Insert" errorRowDisposition="FailComponent" truncationRowDisposition="NotUsed"><inputColumns>
<inputColumn id="453" name="" description="" lineageId="411" usageType="readOnly" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="448"/>
<inputColumn id="454" name="" description="" lineageId="399" usageType="readOnly" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="452"/>
<inputColumn id="455" name="" description="" lineageId="408" usageType="readOnly" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="449"/>
<inputColumn id="456" name="" description="" lineageId="405" usageType="readOnly" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="450"/>
<inputColumn id="457" name="" description="" lineageId="396" usageType="readOnly" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="451"/>
</inputColumns><externalMetadataColumns isUsed="True">
<externalMetadataColumn id="448" name="AccountGroup" description="" precision="0" scale="0" length="300" dataType="wstr" codePage="0"/>
<externalMetadataColumn id="449" name="AccountType" description="" precision="0" scale="0" length="300" dataType="wstr" codePage="0"/>
<externalMetadataColumn id="450" name="AccountSubType" description="" precision="0" scale="0" length="300" dataType="wstr" codePage="0"/>
<externalMetadataColumn id="451" name="AccountNumber" description="" precision="0" scale="0" length="300" dataType="wstr" codePage="0"/>
<externalMetadataColumn id="452" name="AccountName" description="" precision="0" scale="0" length="300" dataType="wstr" codePage="0"/></externalMetadataColumns></input>
</inputs>
<outputs>
<output id="427" name="OLE DB Destination Error Output" description="" exclusionGroup="1" synchronousInputId="426" deleteOutputOnPathDetached="false" hasSideEffects="false" dangling="false" isErrorOut="true" isSorted="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><outputColumns>
<outputColumn id="428" name="ErrorCode" description="" lineageId="428" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="1" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>
<outputColumn id="429" name="ErrorColumn" description="" lineageId="429" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="2" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/></outputColumns><externalMetadataColumns isUsed="False"/></output>
</outputs>
</component>
</components>
<paths>
<path id="430" name="Excel Source Output" description="" startId="374" endId="426"/>
</paths></pipeline></DTS:ObjectData></DTS:Executable><DTS:Property DTS:Name="ObjectName">AccountsDim</DTS:Property><DTS:Property DTS:Name="DTSID">{8707563F-57F7-4679-BFC4-6EC66F6A5BD3}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName">MSDTS.Package.1</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property></DTS:Executable>
Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.
View 1 Replies
View Related
Apr 17, 2007
Iam extracting data from Excel to SQL Destination. In my excel file there are some null values rows are there, just want to skip those null and rest have to export to SQL server Destination. Anyonce can helpme in this.
View 3 Replies
View Related
Apr 1, 2008
Hello,
I have a stored procedure that accepts a number of different input parameters that populate some variables in my stored procedure.
I want to have this stored procedure return nothing if some of these variables aren't filled out (they are populated by a search page the user fills out).
I'm not very familiar with writing stored procedures, so any help you can give me is appreciated.
Thanks!
View 2 Replies
View Related
Oct 8, 2014
Is there a way to allow the count () to return a 0 for the rows with a NULL value instead of not returning that row?
View 15 Replies
View Related
Dec 30, 2006
Hi, how do i do a select statement with asp.net to return a record if a field is null. I have the following code:
SqlCommand cmd = new SqlCommand("SELECT * FROM Categories WHERE ParentId = @ParentId", cn);.Parameters.Add("@ParentId", SqlDbType.Int).Value = parentId != null ? (object) parentId : DBNull.Value;cn.Open();
The variable parentId is a nullable int. However when i pass in a null value it doesn't return anything even though there are records in the database that have a null value for the ParentId field.
Appreciate if someone could tell me what i am doing wrong. Thanks
View 6 Replies
View Related
Jan 16, 2007
I have been looking for the answer to this for a while, but probably haven't found the right place or query. I want to achieve the following:Table 1Table 2
DateShiftDateShiftData
---------------------------------------------------------------------------------
1/1/200711/1/20072Some data
1/1/200721/2/20073Some more data
1/1/20073
1/2/20071
1/1/20072
1/1/20073
and generate:
Table 3
DateShiftData
--------------------------------------------------------
1/1/20071NULL
1/1/20072Some data
1/1/20073NULL
1/2/20071NULL
1/1/20072NULL
1/1/20073Some more data
This way, the information can be displayed and show that some of the entries were not entered for the dates with NULL. Thanks for the help, -Syn
View 2 Replies
View Related
Nov 2, 2006
Hi, I need to build a query that just includes certain columns if they haven't NULL row values.For example:SELECT NAME_COLUMN IF not NULL, SELECT GENDER_COLUMN if not NULL, otherwise won't select these 2 columns i used as example.Any help, will be greatly appreciated,Best regards,Teixeira
View 3 Replies
View Related
Aug 8, 2012
This should be a simple solution, but it has been a long time since I've done any query writing (mostly in Oracle) and I am stumped, so here goes:
We are in the process of converting Access database to MSSQL with web form front ends.
I have a table, all columns are nullable, and want users to be able to query from a form, which has a field for each column and defaults to a % wild card for the entered value.
I want the users to be able to put any string in any field, and have it return each row that matches that, including rows with null values in the other columns, but not the column with the entered criteria.
Here is a sample of the data:
Code:
SQL> select * from test;
COL1 COL2 COL3 COL4
----- ----- ----- -----
this is a test
this is not test
this is not
this is test too
is test too
is too
is too
7 rows selected.
Now, if I have this SQL run, it will return only rows that have no nulls in any columns:
Code:
select
col1,
col2,
col3,
col4from test
where
col1 like'th%'
and col2 like '%'
and col3 like '%'
and col4 like '%';
COL1 COL2 COL3 COL4
----- ----- ----- -----
this is a test
this is not test
this is test too
Now, if I use an OR clause for each column, this mostly works, but the trouble is it will also return rows with null values for the field that has criteria entered in it:
Code:
select
col1,
col2,
col3,
col4from test
where
(col1 like'th%' OR col1 is null)
and (col2 like '%' OR col2 is null)
and (col3 like '%' OR col3 is null)
and (col4 like '%' OR col4 is null);
COL1 COL2 COL3 COL4
----- ----- ----- -----
this is a test
this is not test
this is not
this is test too
is test too
is too
is too
The idea is to only select the first 4 rows in the above example.
I was playing with ISNULL in the select clause, but all it does is substitute a string for a null, and I think CASE will do the same thing.
Is there a way I can write this query so it will return rows with NULL values in any column, except the one(column) that has user entered criteria in it?
View 9 Replies
View Related
Aug 29, 2006
I cannot in the life of me understand what goes wrong here... In a webapplication (C#.NET) I traced an inability to retrieve existing records to SQL Server, where I cannot do the same either. The problem is that in the parameterized query, some fields can be null, and thus when the corresponding fields in the database record are null also, they should be selected. But this won't happen for some reason.
I wrote a test SQL statement that gives me the same bogus:
DECLARE @institution int, @collection int, @serialnr int, @subnr nvarchar(50)SET @institution = 1 SET @collection = 1SET @serialnr = 240 SET @subnr = NULLSELECT ID, Institution, Collection, SerialNumber, SubNumber, AccessionYear, Sagsnummer, DanekraeNr, TaxonIdentified, Stratigraphy, TypeStatus, PlacementRoom, PlacementCabinet, PlacementDrawer, UnitNotesFROM SpecimensWHERE (Institution = @institution) AND (Collection = @collection) AND (SerialNumber = @serialnr) AND (SubNumber = @subnr)
Now there is at least one row with corresponding fields values (1, 1, 240, null), but it won't be selected! What is wrong!?
View 4 Replies
View Related
Dec 31, 2007
Need some join help...
Table 1 - Modules:
ID | Name
1 | A
2 | B
3 | C
Table 2 - CompanyModules
ModuleID | CompanyID
1 | 1
2 | 1
3 | 1
1 | 2
I'd like to return the following result set:
CompanyModules.CompanyID | Modules.Name | Present
1 | A | True
1 | B | True
1 | C | True
2 | A | True
2 | B | False
2 | C | False
What would be the query for this? Thanks.
Edit: This is the query I have tried:
select CompanyModules.CompanyID, Modules.Name, count(Modules.ID) as Present from
CompanyModules RIGHT outer Join Modules on CompanyModules.ModuleID = Modules.ID
group By CompanyModules.CompanyID, Modules.Name
Order by CompanyID
However, it only returns a partial result set:
CompanyModules.CompanyID | Modules.Name | Present
1 | A | 1
1 | B | 1
1 | C | 1
2 | A | 1
View 1 Replies
View Related
Jun 17, 2007
The following is a simplified version of my SQL statement. I am attempting to do a simple count(*) with two groupings and a where clause. This is Select command for a GridView. However, I am unable to display zeros. The rows are completely missing and I would greatly appreciate someone's help. I have already tried Group By All, but that, unfortunately, does not work. Here is my SQL statement:
"SELECT [GENDER], [RACE], COUNT(*) FROM [TABLE] WHERE ([COLUMNNAME] ='SOMETHING') GROUP BY [GENDER], [RACE]"
Thanks for the help in advance!
View 1 Replies
View Related
Oct 17, 2007
Hello,
I have this INNER JOIN that is fine to show all possible combinations. But I need to show only rows that have one or more Null values in tbIntersect.
Should I use nested LEFT JOINT? How?
This is the SQL statement:
sSQL = "SELECT DISTINCT tbCar100.Car100_ID, tbCar100.Description100 AS [Caractéristique 100], " & _
"tbCar200.Car200_ID, tbCar200.Description200 AS [Caractéristique 200], " & _
"tbCar300.Car300_ID, tbCar300.Description300 AS [Caractéristique 300], " & _
"tbCar400.Car400_ID, tbCar400.Description400 AS [Caractéristique 400], " & _
"tbCar500.Car500_ID, tbCar500.Description500 AS [Caractéristique 500], " & _
"tbCar600.Car600_ID, tbCar600.Description600 AS [Caractéristique 600], " & _
"tbCar700.Car700_ID, tbCar700.Description700 AS [Caractéristique 700], " & _
"tbProducts.Prod_ID, tbProducts.PartNumber AS [Part Number] , tbProducts.Description AS [Description] , tbProducts.DateAdded AS [Date] " & _
"FROM tbProducts INNER JOIN (tbCar700 INNER JOIN (tbCar600 INNER JOIN (tbCar500 INNER JOIN (tbCar400 INNER JOIN (tbCar300 INNER JOIN (tbCar100 INNER JOIN " & _
"(tbCar200 INNER JOIN tbIntersect ON tbCar200.Car200_ID = tbIntersect.Car200_ID) " & _
"ON tbCar100.Car100_ID = tbIntersect.Car100_ID) ON tbCar300.Car300_ID = tbIntersect.Car300_ID) ON tbCar400.Car400_ID = tbIntersect.Car400_ID) ON tbCar500.Car500_ID = tbIntersect.Car500_ID) ON tbCar600.Car600_ID = tbIntersect.Car600_ID) ON tbCar700.Car700_ID = tbIntersect.Car700_ID) ON tbProducts.Prod_ID = tbIntersect.Prod_ID " & _
";"
Here is the content of the tbIntersect table:
Car100_ID Car200_ID Car300_ID Car400_ID Car500_ID Car600_ID Car700_ID Prod_ID ID
1 1 1 1 1 1 1 1 1
1 2 1 1 1 1 1 19
1 3 1 1 1 1 1 20
I need to return the rows that have null data, ex: second row because Prod_ID is NULL and third row because Car300_ID is NULL. In fact I need the data from the other joint tables that correspond to these ID fields.
Thanks
View 5 Replies
View Related
Nov 16, 2015
I need to look at all tables in a database that has a column name of GEO
Then look for all values in each table where the GEO value is NULL and delete each of the records found...
View 6 Replies
View Related
Oct 19, 2015
how to do a check for 2 columns. As long as there is data for at least one of the columns I want to return rows.
Example Data
create table test
(
ID int,
set1 varchar(50),
set2 varchar(50),
[code]....
View 4 Replies
View Related
Jul 21, 2007
I am copying a simple table from a Sql Server 2005 database to an *.sdf mobile database.
I am brand new to SSIS and I am probably doing something wrong. But after executing the SSIS package all the rows and all the fields are NULL in the destination database. I put a datagrid viewer between the OLE DB Source and the Sql Server compact edition destination and I can see the real data which is obviously not ALL NULL.
Does anyone have a clue as to why it would be doing this?
Any help would be much appreciated.
Thanks...
View 1 Replies
View Related
Sep 20, 2006
Hey. I need to substitute a value from a table if the input var is null. This is fine if the value coming from table is not null. But, it the table value is also null, it doesn't work. The problem I'm getting is in the isnull line which is in Dark green color because @inFileVersion is set to null explicitly and when the isnull function evaluates, value returned from DR.FileVersion is also null which is correct. I want the null=null to return true which is why i set ansi_nulls off. But it doesn't return anything. And the select statement should return something but in my case it returns null. If I comment the isnull statements in the where clause, everything works fine. Please tell me what am I doing wrong. Is it possible to do this without setting the ansi_nulls to off??? Thank you
set ansi_nulls off
go
declare
@inFileName VARCHAR (100),
@inFileSize INT,
@Id int,
@inlanguageid INT,
@inFileVersion VARCHAR (100),
@ExeState int
set @inFileName = 'A0006337.EXE'
set @inFileSize = 28796
set @Id= 1
set @inlanguageid =null
set @inFileVersion =NULL
set @ExeState =0
select Dr.StateID from table1 dR
where
DR.[FileName] = @inFileName
AND DR.FileSize =@inFileSize
AND DR.FileVersion = isnull(@inFileVersion,DR.FileVersion)
AND DR.languageid = isnull(@inlanguageid,null)
AND DR.[ID]= @ID
)
go
set ansi_nulls on
View 3 Replies
View Related
Jan 9, 2007
I know this is probably a flick of a switch but I cannot figure out which switch. Setup is SQL Server / Stored Procedures / DAL / BLL(skipped for testing) / PL. The stored procedure queries from only one table and two columns are ignored because they are being phased out. I can run the stored procedure and preview the data in the DAL but when I create a page with an ODS linked to the DAL and a GridView I get this error. I checked every column that does not allow nulls and they all have values. I checked unique columns (ID is the only unique and is Identity=Yes in the table definition). I checked foreign-key columns for values that are not in the foreign table and there are none. Any ideas why do I get this?
Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
View 3 Replies
View Related
Jan 17, 2008
Hi,
I am getting the above error when trying to load a report into my Web Application, I have tracked the error down to one specific field in my database. Even though this field is a NVarChar field and is of size 30 it would seem that there is an issue returning the value from the field. I can write it into the database no problems but when I try to get it out of the database it returns the above error.
e.g
MOB 401.908.804 - Fails
0401.907.324 - okay
8239 9082 (pager) - fails
Anyone got an idea on how to fix this????
Regards..
Peter.
View 7 Replies
View Related
Oct 20, 2015
In my report i have CNAME parameter , which allows null value. I checked Allow null value check box in report parameter properties.
when i preview the report , it displays checked NULL check box beside CNAME parameter . I want to give some meaningful name(i.e.ALLCustomers) to this checkbox instead of NULL.
Is it possible through SSRS designer?
View 5 Replies
View Related
May 3, 2007
I have a report that is run on a monthly basis with a default date of null. The stored procedure determines the month-end date that it should use should it be sent a null date.
The report works fine when I tell it to create a history entry; however, when I try to add a subscription it doesn't appear to like the null parameter value. Since I have told the report to have a default value of null it doesn't allow me to enter a value on the subscription page.
Now, I suppose I could remove the parameter altogether from the stored proc, but then the users would never be able to run the report for a previous time period. Can someone explain to me why default values aren't allowed to be used on subscriptions when they seem to work fine for ad hoc and scheduled reports? This is really quite frustrating as most of my reports require a date value and default to null so that the user doesn't have to enter them for the latest data.
An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help
Key cannot be null. Parameter name: key
View 1 Replies
View Related
Nov 5, 2007
I run a stored procedure for which I have a return variable. The stored procedure returns the ID of a row in a table if it exists:
m_sqlCmd.ExecuteScalar();
The m_sqlCmd has been fed an SQLParameter with direction set to output.
When the stored proc returns, I want to test it. Now when there IS a row it returns the ID ok.
When the row doesn't exist, in my watch I have:
m_sqlParam.SqlValue with value {Null}
I can't seem to work out how to test this value out.
I've tried several things but none seem to work.
This line compiles ok, but the following runs into the IF statement as if the SqlValue is null??
if (m_sqlParam.SqlValue != null)....
{
// I'm here!! I thought the watch says this is null???
}
Sorry if this is obvious, but I can't work this one out!!
View 3 Replies
View Related
Mar 26, 2007
Looks like there was a fix and then I read this fix is not a fix. Does anyone know how this can be rectified? Does it mean that only Windows authentiation is the only way it works. The Software is over 2 years old, there are no excuses.
View 1 Replies
View Related
Apr 2, 2007
I am getting this error: "Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails." -- But my value is not null. I did a response.write on it and it show the value. Of course, it would be nice if I could do a breakpoint but that doesn't seem to be working. I'll attach a couple of images below of my code, the error, and the breakpoint error.
Server Error in '/' Application.
Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails.Source Error:
Line 89: sContact.Phone = sPhone.Text.Trim
Line 90: sContact.Email = sEmail.Text.Trim
Line 91: sContact.Save()
Line 92:
Line 93: Dim bContact As Contact = New Contact()Source File: F:InetpubwwwrootOutman KnifeCheckout.aspx.vb Line: 91 Stack Trace:
[SqlException (0x80131904): Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857354
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734966
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
System.Data.SqlClient.SqlDataReader.HasMoreRows() +150
System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) +214
System.Data.SqlClient.SqlDataReader.Read() +9
System.Data.SqlClient.SqlCommand.CompleteExecuteScalar(SqlDataReader ds, Boolean returnSqlValue) +39
System.Data.SqlClient.SqlCommand.ExecuteScalar() +148
SubSonic.SqlDataProvider.ExecuteScalar(QueryCommand qry) +209
SubSonic.DataService.ExecuteScalar(QueryCommand cmd) +37
SubSonic.ActiveRecord`1.Save(String userName) +120
SubSonic.ActiveRecord`1.Save() +31
Checkout.btnCheckout_Click(Object sender, EventArgs e) in F:InetpubwwwrootOutman KnifeCheckout.aspx.vb:91
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42
View 8 Replies
View Related
Jun 28, 2004
I would drop and add the table but the data can't be deleted. So if anyone could help with the statement it would be greatly appreciated. Thanks
View 7 Replies
View Related
Feb 26, 2004
I'm trying to set the concat... option to OFF
all the time and in all my databases
I tried this command
USE master
EXEC sp_dboption 'DatabaseName', 'concat null yields null', 'FALSE'
but it doesn't change anything
Select NULL + 'TOTO'
----> NULL
(it should be 'TOTO')
View 2 Replies
View Related
Aug 3, 2005
hi,my structure table in database:Amount float(53) not null default 0when i try to run his script:alter table ABC alter column Amount float(53) nullit can only set the Amount to allow null, but can't set the defaultvalue to empty.anyone know how to set the field to allow null and default set toempty, no value.thanks
View 5 Replies
View Related
May 13, 2015
It's giving me an error while I'm trying to change column value from not null to null..
'tblid' table
- Unable to modify table.
Cannot insert the value NULL into column 'ValidID', table 'Xe01.dbo.Tmp_tblid'; column does not allow nulls. INSERT fails.
The statement has been terminated.
View 4 Replies
View Related
May 3, 2006
Hello.
I've built a sample CLR function with the following declaration....
CREATE FUNCTION GetManager(@DeptCode nvarchar(3))
RETURNS nvarchar(1000)
WITH RETURNS NULL ON NULL INPUT
AS
EXTERNAL NAME Assembly1.[ClassLibrary1.MyVBClass].MyManager
And it works as expected, except when I use NULL:
DECLARE @MyManager nvarchar(1000)
EXEC @MyManager = dbo.GetManager NULL
PRINT @MyManager
It returns the value "Unknown" as it would have for any unknown DeptCode, as-programmed.
I'm of the theory it should have returned NULL without actually firing the function? Or is this only for non-CLR items... or stored procedures, not functions?
View 3 Replies
View Related
Oct 27, 2015
We have a case where in we should show date based on conditions for e.g if we had a column defined as
col varchar(10) then we would show col as 'NULL' for some condition and actual value when no condition
Normaly date values are stored here e.g under col 20150901 .
Case
when col>'20150901' then 'NULL'
else col
end as Derivedcol
Note this is an extract process and we are presenting data by pumping the data in a table .
Now there is another similar column -colz varchar(10) which stores date but doesnt have case condition so whenever date has no value its shows null which is database null.
So whats the difference between database null and string null ?
How can we show database null for the case condition instead of string "null"?
View 10 Replies
View Related