Solved

ODC Data Types for parameters?

Posted on 2014-07-30
1
847 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 500 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

615 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