Solved

ODC Data Types for parameters?

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
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…

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now