[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

ODC Data Types for parameters?

Posted on 2014-07-30
1
Medium Priority
?
992 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
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

590 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