Solved

ODC Data Types for parameters?

Posted on 2014-07-30
1
716 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 39

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

773 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