fjkilken
asked on
ODC Data Types for parameters?
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
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>ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.