Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ODC Data Types for parameters?

Posted on 2014-07-30
1
Medium Priority
?
900 Views
Last Modified: 2014-08-25
Hi
I have an ODC (Office data Connection) file which my Excel uses to fetch info from SQL server.
It works mostly fine, but I need to fully understand the DataType used for the parameter aspect of the query.
By default, whenaadd a parameter, it's given a DataType of "12".
When I try to pass mor ethan appro 300 characters back to the SQL database I get an error, if I reduce the size of data being passed (eg; 200 chars) it works fine.
I'm thinking this could be related to the  DataType value of "12" I'm using - I wonder what other values are available and do they allow to pass a larger string size of parameter value?
See below ODC code for ref


<html xmlns:o="urn:schemas-microsoft-com:office:office"
	xmlns="http://www.w3.org/TR/REC-html40" xmlns:msdt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882">
	<head>
		<meta http-equiv=Content-Type content="text/x-ms-odc; charset=utf-8">
		<meta name=ProgId content=ODC.Table>
		<meta name=SourceType content=ODBC>
		<title>CWL</title>
		<xml id=docprops><o:DocumentProperties
		  xmlns:o="urn:schemas-microsoft-com:office:office"
		  xmlns="http://www.w3.org/TR/REC-html40">
		  <o:Description>Channel Warranty List</o:Description>
		  <o:Name>CWL</o:Name>
		 </o:DocumentProperties>
		</xml>
		<xml id=msodc>
			<odc:OfficeDataConnection
				xmlns:odc="urn:schemas-microsoft-com:office:odc"
				xmlns="http://www.w3.org/TR/REC-html40">
				<odc:Connection odc:Type="ODBC">
					 <odc:ConnectionString>DRIVER=SQL Server;SERVER=myserver,3180;Trusted_Connection=Yes;DATABASE=mydb</odc:ConnectionString>
						[b] <odc:Parameter>
							<odc:Name>Parameter1</odc:Name>
							<odc:DataType>12</odc:DataType>
						   </odc:Parameter>[/b]
						<odc:CommandText>{call dbo.sp_name(?,?)}</odc:CommandText>
						<odc:SSOApplicationID>SSO_CPLG-GRL</odc:SSOApplicationID>
						<odc:CredentialsMethod>Stored</odc:CredentialsMethod>
						<odc:AlwaysUseConnectionFile/>
				</odc:Connection>
			</odc:OfficeDataConnection>
		</xml>
	</head>
</html>

Open in new window

0
Comment
Question by:fjkilken
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 40

Accepted Solution

by:
lcohan earned 2000 total points
ID: 40229057
I believe by default Excel data type is tied to varchar(255) and please see details below about how to adjust or workaround by eventually using different connectors instead(like ODBC or OLEDB)

"The truncated text problem

A very similar issue is when you have a text column in your Excel sheets that sometimes has more than 255 characters in a single cell. A common example is a “comments” column.
"

...
"The solution part III

The problem is the same as before: the provider scans the first 8 rows and finds only text of normal length. SSIS takes the default data type of (DT_WSTR,255) for all string data. Our comments are sometimes larger than 255 characters, causing the truncation error.

If your comments have a maximum length lower than the 4000 character limit of DT_WSTR, you can simply adjust the length of the column in the advanced editor of the source component or you can configure the source to ignore the failure.
"

http://blogs.lessthandot.com/index.php/datamgmt/dbprogramming/mssqlserver/what-s-the-deal-with/
http://microsoft-ssis.blogspot.ca/2011/06/mixed-data-types-in-excel-column.html
http://office.microsoft.com/en-ca/excel-help/overview-of-connecting-importing-data-HP010201710.aspx#BMusing_external_data_ranges_and_proper
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question