Inserting Null Value: type

Showing posts with label type. Show all posts

Wednesday, March 28, 2012

install Office ifilter on SQL Server 2005

Hi there
I might be being dense, but I can't seem to get the Full Text Search
service working for office type documents. The service works OK for
text and RTF files, so I know that the Full Text indexing service is
there, but just doesn't seem to be working for Office docs -
specifially Word .doc files.
Digging a little deeper, it seems that the relevant ifilter isn't
installed. When I run
select * from sys.fulltext_document_types
I get the list of document types that can be indexed and searched by
SQL server 2005. For .the .rtf entry, I get a path of
c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\msfte.dll
for the ifilter, version 12.0.6214.0 which is where I would expect it
to be (ie I can see the msfte.dll file in the Binn directory).
For the .doc entry, I get a path of
c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\offfilt.dll
for the ifilter, but no version number, and when I look at the Binn
directory, the file is not there.
Is there any way I can install the office ifilter on SQL server 2005?
Note that this is a database server so does not have IIS, Sharepoint,
Desktop search or any other search mechanism installed. I would like
the entire office doc search facility to be confined to the SQL server
2005 installation if possible - and from the path specified in the
fulltext_document_types view I take it that it should work this way.
Any help / pointers would be very gratefully received.
Thanks is advance
Jeremy
Hi Jeremy. I see the same thing on my machine, but it does work on my
machine.
Basically you will find the iFilter in %windir%\system32 which is likely
c:\Windows\System32. I am not sure why it refers to this location. You will
find that the persistent handler associated with the .doc extension points
here.
Can you check your Word Docs to make sure that they are not saved in the
fast save format? the Office iFilter does not understand this format.
Also download filtdump from the platform sdk and run your word docs through
this to make sure the iFilter understands them.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jeremy Holland" <jeremy.holland@.konetic.com> wrote in message
news:1165402032.796856.43690@.l12g2000cwl.googlegro ups.com...
> Hi there
> I might be being dense, but I can't seem to get the Full Text Search
> service working for office type documents. The service works OK for
> text and RTF files, so I know that the Full Text indexing service is
> there, but just doesn't seem to be working for Office docs -
> specifially Word .doc files.
> Digging a little deeper, it seems that the relevant ifilter isn't
> installed. When I run
> select * from sys.fulltext_document_types
> I get the list of document types that can be indexed and searched by
> SQL server 2005. For .the .rtf entry, I get a path of
> c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\msfte.dll
> for the ifilter, version 12.0.6214.0 which is where I would expect it
> to be (ie I can see the msfte.dll file in the Binn directory).
> For the .doc entry, I get a path of
> c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\offfilt.dll
> for the ifilter, but no version number, and when I look at the Binn
> directory, the file is not there.
> Is there any way I can install the office ifilter on SQL server 2005?
> Note that this is a database server so does not have IIS, Sharepoint,
> Desktop search or any other search mechanism installed. I would like
> the entire office doc search facility to be confined to the SQL server
> 2005 installation if possible - and from the path specified in the
> fulltext_document_types view I take it that it should work this way.
> Any help / pointers would be very gratefully received.
> Thanks is advance
> Jeremy
>
|||Hi Hilary
Thanks for your reply - I'll try as you suggest.
Jeremy
|||Hi Hilary
Thanks for that - I registered the OS ifilters and ignored that view as
you suggested.
It seems to work fine now with Word docs - thanks a lot for that.
Jeremy
Jeremy Holland wrote:

> Hi Hilary
> Thanks for your reply - I'll try as you suggest.
> Jeremy

Friday, March 23, 2012

Install important update from MS Corporation

--jwkvmscbnfdwes
Content-Type: multipart/related; boundary="kioohlnya";
type="multipart/alternative"
--kioohlnya
Content-Type: multipart/alternative; boundary="gimzwyinehsg"
--gimzwyinehsg
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable
Microsoft User
this is the latest version of security update, the
"October 2003, Cumulative Patch" update which fixes
all known security vulnerabilities affecting
MS Internet Explorer, MS Outlook and MS Outlook Express
as well as three newly discovered vulnerabilities.
Install now to continue keeping your computer secure
from these vulnerabilities, the most serious of which could
allow an attacker to run executable on your computer.
This update includes the functionality = of all previously released patches.
System requirements: Windows 95/98/Me/2000/NT/XP
This update applies to:
- MS Internet Explorer, version 4.01 and later
- MS Outlook, version 8.00 and later
- MS Outlook Express, version 4.01 and later
Recommendation: Customers should install the patch = at the earliest opportunity.
How to install: Run attached file. Choose Yes on displayed dialog box.
How to use: You don't need to do anything after installing this item.
Microsoft Product Support Services and Knowledge Base articles = can be found on the Microsoft Technical Support web site.
http://support.microsoft.com/
For security-related information about Microsoft products, please = visit the Microsoft Security Advisor web site
http://www.microsoft.com/security/
Thank you for using Microsoft products.
Please do not reply to this message.
It was sent from an unmonitored e-mail address and we are unable = to respond to any replies.
---
The names of the actual companies and products mentioned = herein are the trademarks of their respective owners.
Copyright 2003 Microsoft Corporation.
--gimzwyinehsg
Content-Type: text/html
Content-Transfer-Encoding: quoted-printable
&
.navtext{color:#ffffff;text-decoration:none}
Microsoft
All Products |
Support |
Search |
Microsoft.com Guide
Microsoft Home
Microsoft User
this is the latest version of security update, the
"October 2003, Cumulative Patch" update which fixes
all known security vulnerabilities affecting
MS Internet Explorer, MS Outlook and MS Outlook Express
as well as three newly discovered vulnerabilities.
Install now to continue keeping your computer secure
from these vulnerabilities, the most serious of which could
allow an attacker to run executable on your computer.
This update includes the functionality = of all previously released patches.
System requirements
Windows 95/98/Me/2000/NT/XP
This update applies to
MS Internet Explorer, version 4.01 and later
MS Outlook, version 8.00 and later
MS Outlook Express, version 4.01 and later
Recommendation
Customers should install the patch = at the earliest opportunity.
How to install
Run attached file. = Choose Yes on displayed dialog box.
How to use
You don't need to do = anything after installing this item.
Microsoft Product Support Services and Knowledge Base articles
can be found on the Microsoft Technical Support web site. = For security-related information about Microsoft products, please = visit the
Microsoft Security Advisor web site, = or Contact Us.
Thank you for using Microsoft products.
Please do not reply to this message. = It was sent from an unmonitored e-mail address and we are unable = to respond to any replies.
The names of the actual companies and = products mentioned herein are the trademarks = of their respective owners.
Contact Us
|
Legal
|
TRUSTe
©2003 Microsoft Corporation. All rights reserved.
Terms of Use
|
Privacy Statement |
Accessibility

--gimzwyinehsg--
--kioohlnya
Content-Type: image/gif
Content-Transfer-Encoding: base64
Content-ID: <oudvryd>
R0lGODlhaAA7APcAAP///+rp6puSp6GZrDUjUUc6Zn53mFJMdbGvvVtXh2xre8bF1x8cU4yLprOy
zIGArlZWu25ux319xWpqnnNzppaWy46OvKKizZqavLa2176+283N5sfH34uLmpKSoNvb7c7O3L29
yqOjrtTU4crK1Nvb5erq9O/v+O7u99PT2sbGzePj6vLy99jY3Pv7/vb2+fn5++/v8Kqr0oWHuNbX
55SVoszN28vM2pGUr7S1vqqtv52frOPl8CQvaquz2Ojp7pmn3Ozu83OPzmmT6F1/xo6Voh9p2C5z
3EWC31mS40Zxr4uw6LXN8iZkuXmn55q97PH2/Yir1rbL5iVTh3Oj2cvX5Pv9/+/w8QF8606h62Wk
3n+dubnY9abB2c7n/83h9Nji6weK+CGJ4Vim6WyKpKWssgFyyAaV/0Km8Gyx6HW57FJxicDP2+Tt
9Pj8/wOa/wmL5wqd/w6V8heb91e5+mS9+VmLr4vD6qvc/b/j/Mbn/sTi9rvX6szq/tPt/9ju/dzx
/+n2/+74//P6/+3w8hOh/xOW6yCm/iuu/zWv/0m4/XTH/IXK95TP9qPV9bfi/tDn9tfp9OP0/93r
9L3Izy6Vzj22/lrC/mfG/JvJ5JGntAyd6IbX/3zD6GzP/3jV/2uoxHqbqujv8g6MvJTj/2HF5pXV
606zz6Hp/63v/7j1/8Ps88b8/rbj5RKOkE2wr3OGhoKGhv7///Dx8V2alqvm4Zni1YPRvx5uVwyO
X0q2hLTvw8X10gx2H4PXkkuoV5zkoQeADZu7mmzIVEO7HIXbaGfLMPz8+97d2/Px7v///+bl5eHg
4P7+/v39/fT09PLy8u7u7gAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACwAAAAAaAA7AAAI/gCVCRxI
sKDBgwgTKlzIsKHDhxAjKgwiqs2kSJEgQfqyp2PHLxoxTmojSpTEkyglBrGYcU+el3n09PEDSFKg
mzclAfLTRw/MPV4gjTSZsmhRURchuXwUs88fSYIGubEiqyqAq1gBNLPiRlCgPz197tE4MojRswuD
JHX5UiagQILcNMtKl26zu3etuBgUaKcePXv0QIo0iSjaw8raROKYh6nbuFbmVpVlpbKby4Mya858
eWrlrV0l/fECWDBhw4hPimoJUw9NQVa0Yg6kk6dPmD9xt/Xi52kgKG4GCRLtpTjZNmZTQ5yktLXT
QFNDA+qJe2wkkgkrrmWrx4tv0X6M/gvFrnzh6uaO+wCKOhzs7TzWyUesyDom7z9//EAKOh51eYKK
sdWWH1D15cd78J12GFJKufRXcfwNNtR/ANYXE006UfdSfBQq1lxM3fFHWFlojRBCCA5goMMK5y3V
1B879VGdUMlRqIxaG7kUmHEikVTjQyuAcGIGDmSQwQUYzPBAA1UIKJMfUCI4Vhs2EjTJKrWYwogp
mXSxY0iTTLhQAC2ocKIDHGywgAwYWPDAm3AeIIVztr3E1FiFVSnQJLXc4ksxuujyiy6npNGFYBKK
WRAzKZipAgkp8ACCAyLg0MClDcD5ppIUVNCFFDL1oSF8Qvn3nyi8+KIqMH8aQwwx/66EMQcoVQxG
mI/KBEBCCCSo0MIPLJSJwA6YFvsmBlFkYgopUTxwgQ8XXGBBBRUA0QUXeJp6qi2r2rKLLcAU42qs
WIRhR623YpdDNM4wQ0IOInggrwfFNoCDDl20wooqqaSCCil3SHCBBgQXnAGbFmCAgQMkBKDnLsMU
4wswvPCySy3DuLpJGFiY4YodX6RrUhnOIFDDvPNeqkkXfKzCyssv8+svwM5uYPPNONusAZszEEEE
GoooQsfQdRRdxyJII83I0ow04nQjjkTtCB5cVN3KMBEXA8wuFbMC6Cu5jIJFLsG4oonIQeQQQw4o
a5KsI6moogrMMMvt77+kCPzB3v589+03BxdQ0IFyotyCdTFap7I1K7Z4YskmcIwSTC+9KMHGSD6S
0AIJHkRxByekkIJKv3LPXbfMeOddgQmst+466xoAIUEEEUzAQNBD02H00UkvwnTTT0s9ddV4ZPEK
1hH/qTUnlyDyRi659BJMMLiEgrkoQSwTAjMefPIJ6KKPHnfppfeLCt6cCDFDmjT8AMP7MJywwQW0
1187Aco5osUYyGNtjC+ccFwhzuCK6U0OF2uoQht8FAMEoMADnfge+M7Xrwpa8HyhI0X6JGCwDGhg
fvYLoe1wRzSj9c53THsa1KRGNS6oYQxZ0AXyjKGLUlzCEoeIQxjIRjnKTYESC/7EnjJyYAIRRMF7
4Auf+Cp4vtRxghNOiEAHjxTC+k3gfsp5ghPSAIqMBeoUlkjEIeYgBzjwEBdonEIOgmgWSDlgC0h8
YgabSEcncuITUZQBwYxERftRYAIToEDtbie0EhbthL9TofBa6IT9jeEVgQpUJcZoCDEUcHqUw8UU
ysBGZZQgBAvAgSfimMQMmjJ0T/SeGiKgRw3w8QKz+2Mgp/UALKamC1FYwha1AElJzkEMYiDb5HqB
wE2SRIjR0MEIGoCJUUqwlKd84h0/4QlMRKACezQSLAM5A2pR6wF/JGTudofIFAaPhVW7AxWooIX9
ZSELv4hnJYA5CjQScw1rUP/jMQeCgA/gQA2ecOYzpUnQaVKzmtfM5pEkMIFpebMCtZwA/lJTBR88
YQlRcIITQBHPeNrhCEcwQhPQmM8EALEkAwnBDTBAhWYG1HukTCVMD4oJTBDBAgrNAEOnZYE/vomh
4jQk75KWyHNGrYWO0KUT1tlOWnRUCUdQQhOaoIQ12GEKsVCgEAVSAge88RIufelMxxrQal7iEkLg
oCv5uFOffvOPE0XMMvjggy74IAoZ3UI8aYEEJUh1CkoggxIOUIbCbFUZyczADM4K1rI69rHVxARj
kyDFtRppp9OawR8pAFQS6s6EvSuq0xZZNS444gkZ1SgVQkELWvjMr1QlQgT+pgALG+yTIDrgwAPo
wFiwhtWxNZUsYxVBWYX6YAYT0CwgHwDRB0i0PNGoghTsCoQoaEIYQhCCz7ZLhCYoIAdD+ZEyQqAB
C4xBEb09a3Brmt5LBE0RWYiAB/mo2EBSoJvfdG5QP3vI0JpztOgsLR8y8QTU4jUK2U2wEIagBAWU
AQy3JcgIUqSF97b3wu9VhCXQwErLKpYCDvXmmygQV+UEQLpScKUPfACEFjuBCGuAhQ4gXBLxIjZa
QrBEhtGL3rPyOMOWCHIiOkxfCzT0oc2lwH7J6d+lKTLAVfPIdAu8hCUAwQlCIIMBikAJCEeYIMm4
gAxmkIggB3nHOzazJcb+QIXZ6bHIIPZmT0FMYj2RyUw50EEZRIAASnzheoctSJEekIgyq/nQalaE
E2QXAYHlFANx1iyILYDcJYOWqP9d4VFLi62PgEQkGAl1mI5p44HcYMxoQISqC21oIYcxDUuowOwk
IAMOTDEDGAAnBR5gARyAE5Al1pMytIM5UiuEBxWwQBIOoepmO1sRd/BBBWgnMGo9a758xECmcOBr
QE5Av55lMqadbNThldYjX/h0qEVyvVIDiFpEOIS85b3qOjBBBrODgL4foCZoWVsG2cZAt5fL7ToL
WyAVWeAxA42QScjgAkQoRCHmrYhGgDAC+s54AjbAAQ4s4GDeFHOuvf3/ABwMQBgiUHK4L620TJP2
3J7WSEhG1MmJRKILsJzDxBfxhfLWL+MZn4AGOm5rgj2cWrJ8wAB2sAMRFEMYBtcTRUpCdXcbZDV8
sIAExoAHHuA7At2sYv3Q5PEOQmvXTE/7DlCu8kLyd6gtJzeANw3zPaRb5uwOIkoV0gY2SNsCgG+0
DFJwJFhWMbkDK7qHRcD4xjMeBxMoQAGEHYSpWz0hPlhANHxggWtyYBnMQAYIKvBwCZj+9GCHqAUc
kFMdOF4EOzBAAXoA2JX3d9zAm7u5oxxzW4164doaiAM0rwwU0IAHz4hGAEDfAjH74PTQn4G0EpAA
Z9HX9Y03wAEKcIAB/oDAYQc/CQkcEIBoPAMGzoDBM2KwfGa0QAMXOBLg5y8B6V/gAVNowhQogIEV
61kEDXAAPdADTVAJaKBjtgd3KCR3mrZ7nWZ36kZzx0QIV5AQGNAC5Xd+x6B+7Md8KYBN0oZkziIt
E4AAKTAACtBQ8ZIA3NcBKrAMMRB+RfEAzLAM0aAMz/ACLwANyrcMyNACKXABCwA40VKEFPBwRtYE
cjAHhmAEU5AAAzgFYjAHrHZmCVhODPhyvAeBtkJzNUYIs5AQNLgM5VeBV9CDoQeEIZABICADbviG
FBAtRqYAzCAQAVACOSAACFACMngYFqACNRgAgiiIy+CDLQCEJCAD/yWgAV7ViHF4ATOQAFMABxI3
cWM0B6tWhQjoduIWd7nXgC20hXfHbkOBPRSYECFgAchQg4VYiMyQhikAAjdwAStgAydyIm1yARVA
AQXQASvQhzYSAA2AAav4iq/4g0AYiyRwATRQAiqgAggwAxYgA7t4AAcQAjcIjBTSAgYwAySADOB4
iMkoi7uCAQuQJBYgZj3FfQOwDNpYJSnQAROAAZozjuS4AAsAfzLgAGzyACzYfXX4jlVSAmVAfQ+w
MCRgAyRAAvhIMCmCXNtXAAYQAu4okHryAzaAARNgjQYJJxNAfRF5AAaQAy2QjRYpdWBQBV2QawrA
gpLHfQpgAA1ggiMrYJInKWxIsRhfUAU82ZMj0Iwr8AM3qY3E9ntVV3lDWSUBAQA7
--kioohlnya
Content-Type: image/gif
Content-Transfer-Encoding: base64
Content-ID: <alzjijk>
R0lGODlhDAAMANUAAP////f3//f39+/v9+/v797m987W787W5sXW5rXF76295qW975y175St75St
3pSlzoyl1oSl5oylzoycxXOU3nOMxWOM5mOM3mOE1lqE3mOEvVKE1lp7xVJ71lJ7zlJ7xVJ7vUp7
zkpzzkpzxVJzrUprvUJrxUJrvUJjtTpjtTpjrTparTpapQAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACwAAAAADAAMAAAIjAABAAhwwMGFCxAQ
CACwkICDDBYSLGjQwQEBhg8zDBAIYIEIBwIQdLjAoOOFgSFMIICwIUMEAxQwCBxhAgKHDh5C6DQA
IIGJEyA4fPAwYoQCAAVKoEgBQsKJEidQ8CyRYumDA1VTqNBQQYXXFQofsPB6AIAKFiweNBTLoiza
BxcFCjgwgQSJCQcWCggIADs=
--kioohlnya--
--jwkvmscbnfdwes
Content-Type: application/x-compressed; name="Installation7.zip"
Content-Transfer-Encoding: base64
Content-Disposition: attachment
--jwkvmscbnfdwes--This is a multi-part message in MIME format.
--=_NextPart_000_007B_01C3899F.FBB4C780
Content-Type: multipart/alternative;
boundary="--=_NextPart_001_007C_01C3899F.FBB4C780"
--=_NextPart_001_007C_01C3899F.FBB4C780
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
VIRUS ALERT!!!
-- Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=3Ddjq&as =ugroup=3Dmicrosoft.public.sqlserver
"Nice - Venan" <gofkkgjfzp@.hgu.com> wrote in message =news:OkM4AeNiDHA.1872@.TK2MSFTNGP10.phx.gbl...
Microsoft All Products | Support | Search | =Microsoft.com Guide Microsoft Home
Microsoft User
this is the latest version of security update, the "October =2003, Cumulative Patch" update which fixes all known security =vulnerabilities affecting MS Internet Explorer, MS Outlook and MS =Outlook Express as well as three newly discovered vulnerabilities. =Install now to continue keeping your computer secure from these =vulnerabilities, the most serious of which could allow an attacker to =run executable on your computer. This update includes the functionality =of all previously released patches.
System requirements Windows 95/98/Me/2000/NT/XP This update applies to MS Internet Explorer, version 4.01 and =later
MS Outlook, version 8.00 and later
MS Outlook Express, version 4.01 and later Recommendation Customers should install the patch at the =earliest opportunity. How to install Run attached file. Choose Yes on displayed =dialog box. How to use You don't need to do anything after installing this =item.
Microsoft Product Support Services and Knowledge Base articles =can be found on the Microsoft Technical Support web site. For =security-related information about Microsoft products, please visit the =Microsoft Security Advisor web site, or Contact Us.
Thank you for using Microsoft products.
Please do not reply to this message. It was sent from an =unmonitored e-mail address and we are unable to respond to any replies.
---
The names of the actual companies and products mentioned herein =are the trademarks of their respective owners.
Contact Us | Legal | TRUSTe =A92003 Microsoft Corporation. All rights reserved. Terms of Use =| Privacy Statement | Accessibility --=_NextPart_001_007C_01C3899F.FBB4C780
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

.navtext {
COLOR: #ffffff; TEXT-DECORATION: none
}
VIRUS ALERT!!!
-- Tibor Karaszi, SQL Server MVPArchive at: http://groups.google.com/groups?oi=3Ddjq&as">http://groups.go=ogle.com/groups?oi=3Ddjq&as ugroup=3Dmicrosoft.public.sqlserver
"Nice - Venan" wrote in =message news:OkM4AeNiDHA.1872=@.TK2MSFTNGP10.phx.gbl...
Microsoft
All Products | Support | Search | Microsoft.com Guide
Microsoft Home
Microsoft Userthis is the latest =version of security update, the "October 2003, Cumulative Patch" update =which fixes all known security vulnerabilities affecting MS Internet =Explorer, MS Outlook and MS Outlook Express as well as three newly discovered = vulnerabilities. Install now to continue keeping your computer =secure from these vulnerabilities, the most serious of which could =allow an attacker to run executable on your computer. This update =includes the functionality of all previously released patches.
System requirements
Windows =95/98/Me/2000/NT/XP
This update applies to
MS Internet Explorer, version 4.01 and laterMS Outlook, version 8.00 and laterMS Outlook =Express, version 4.01 and later
Recommendation
Customers should install the patch at =the earliest opportunity.
How to install
Run attached file. Choose Yes on =displayed dialog box.
How to use
You don't need to do anything after =installing this item.
Microsoft Product Support Services and =Knowledge Base articles can be found on the Microsoft Technical Support web site. For security-related information about Microsoft products, please =visit the Microsoft Security Advisor web site, or Contact Us. Thank you for using =Microsoft products.Please do not reply to =this message. It was sent from an unmonitored e-mail address and we =are unable to respond to any replies.
The names of the actual companies =and products mentioned herein are the trademarks of their respective =owners.
Contact Us | Legal = | TRUSTe
=A92003 Microsoft Corporation. =All rights reserved. Terms of Use | Privacy Statement | Accessibility =

--=_NextPart_001_007C_01C3899F.FBB4C780--
--=_NextPart_000_007B_01C3899F.FBB4C780
Content-Type: image/gif
Content-Transfer-Encoding: base64
Content-ID: <006e01c3898f$3822cfc0$216411ac@.tibork>
R0lGODlhaAA7APcAAP///+rp6puSp6GZrDUjUUc6Zn53mFJMdbGvvVtXh2xre8bF1x8cU4yLprOy
zIGArlZWu25ux319xWpqnnNzppaWy46OvKKizZqavLa2176+283N5sfH34uLmpKSoNvb7c7O3L29
yqOjrtTU4crK1Nvb5erq9O/v+O7u99PT2sbGzePj6vLy99jY3Pv7/vb2+fn5++/v8Kqr0oWHuNbX
55SVoszN28vM2pGUr7S1vqqtv52frOPl8CQvaquz2Ojp7pmn3Ozu83OPzmmT6F1/xo6Voh9p2C5z
3EWC31mS40Zxr4uw6LXN8iZkuXmn55q97PH2/Yir1rbL5iVTh3Oj2cvX5Pv9/+/w8QF8606h62Wk
3n+dubnY9abB2c7n/83h9Nji6weK+CGJ4Vim6WyKpKWssgFyyAaV/0Km8Gyx6HW57FJxicDP2+Tt
9Pj8/wOa/wmL5wqd/w6V8heb91e5+mS9+VmLr4vD6qvc/b/j/Mbn/sTi9rvX6szq/tPt/9ju/dzx
/+n2/+74//P6/+3w8hOh/xOW6yCm/iuu/zWv/0m4/XTH/IXK95TP9qPV9bfi/tDn9tfp9OP0/93r
9L3Izy6Vzj22/lrC/mfG/JvJ5JGntAyd6IbX/3zD6GzP/3jV/2uoxHqbqujv8g6MvJTj/2HF5pXV
606zz6Hp/63v/7j1/8Ps88b8/rbj5RKOkE2wr3OGhoKGhv7///Dx8V2alqvm4Zni1YPRvx5uVwyO
X0q2hLTvw8X10gx2H4PXkkuoV5zkoQeADZu7mmzIVEO7HIXbaGfLMPz8+97d2/Px7v///+bl5eHg
4P7+/v39/fT09PLy8u7u7gAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACwAAAAAaAA7AAAI/gCVCRxI
sKDBgwgTKlzIsKHDhxAjKgwiqs2kSJEgQfqyp2PHLxoxTmojSpTEkyglBrGYcU+el3n09PEDSFKg
mzclAfLTRw/MPV4gjTSZsmhRURchuXwUs88fSYIGubEiqyqAq1gBNLPiRlCgPz197tE4MojRswuD
JHX5UiagQILcNMtKl26zu3etuBgUaKcePXv0QIo0iSjaw8raROKYh6nbuFbmVpVlpbKby4Mya858
eWrlrV0l/fECWDBhw4hPimoJUw9NQVa0Yg6kk6dPmD9xt/Xi52kgKG4GCRLtpTjZNmZTQ5yktLXT
QFNDA+qJe2wkkgkrrmWrx4tv0X6M/gvFrnzh6uaO+wCKOhzs7TzWyUesyDom7z9//EAKOh51eYKK
sdWWH1D15cd78J12GFJKufRXcfwNNtR/ANYXE006UfdSfBQq1lxM3fFHWFlojRBCCA5goMMK5y3V
1B879VGdUMlRqIxaG7kUmHEikVTjQyuAcGIGDmSQwQUYzPBAA1UIKJMfUCI4Vhs2EjTJKrWYwogp
mXSxY0iTTLhQAC2ocKIDHGywgAwYWPDAm3AeIIVztr3E1FiFVSnQJLXc4ksxuujyiy6npNGFYBKK
WRAzKZipAgkp8ACCAyLg0MClDcD5ppIUVNCFFDL1oSF8Qvn3nyi8+KIqMH8aQwwx/66EMQcoVQxG
mI/KBEBCCCSo0MIPLJSJwA6YFvsmBlFkYgopUTxwgQ8XXGBBBRUA0QUXeJp6qi2r2rKLLcAU42qs
WIRhR623YpdDNM4wQ0IOInggrwfFNoCDDl20wooqqaSCCil3SHCBBgQXnAGbFmCAgQMkBKDnLsMU
4wswvPCySy3DuLpJGFiY4YodX6RrUhnOIFDDvPNeqkkXfKzCyssv8+svwM5uYPPNONusAZszEEEE
GoooQsfQdRRdxyJII83I0ow04nQjjkTtCB5cVN3KMBEXA8wuFbMC6Cu5jIJFLsG4oonIQeQQQw4o
a5KsI6moogrMMMvt77+kCPzB3v589+03BxdQ0IFyotyCdTFap7I1K7Z4YskmcIwSTC+9KMHGSD6S
0AIJHkRxByekkIJKv3LPXbfMeOddgQmst+466xoAIUEEEUzAQNBD02H00UkvwnTTT0s9ddV4ZPEK
1hH/qTUnlyDyRi659BJMMLiEgrkoQSwTAjMefPIJ6KKPHnfppfeLCt6cCDFDmjT8AMP7MJywwQW0
1187Aco5osUYyGNtjC+ccFwhzuCK6U0OF2uoQht8FAMEoMADnfge+M7Xrwpa8HyhI0X6JGCwDGhg
fvYLoe1wRzSj9c53THsa1KRGNS6oYQxZ0AXyjKGLUlzCEoeIQxjIRjnKTYESC/7EnjJyYAIRRMF7
4Auf+Cp4vtRxghNOiEAHjxTC+k3gfsp5ghPSAIqMBeoUlkjEIeYgBzjwEBdonEIOgmgWSDlgC0h8
YgabSEcncuITUZQBwYxERftRYAIToEDtbie0EhbthL9TofBa6IT9jeEVgQpUJcZoCDEUcHqUw8UU
ysBGZZQgBAvAgSfimMQMmjJ0T/SeGiKgRw3w8QKz+2Mgp/UALKamC1FYwha1AElJzkEMYiDb5HqB
wE2SRIjR0MEIGoCJUUqwlKd84h0/4QlMRKACezQSLAM5A2pR6wF/JGTudofIFAaPhVW7AxWooIX9
ZSELv4hnJYA5CjQScw1rUP/jMQeCgA/gQA2ecOYzpUnQaVKzmtfM5pEkMIFpebMCtZwA/lJTBR88
YQlRcIITQBHPeNrhCEcwQhPQmM8EALEkAwnBDTBAhWYG1HukTCVMD4oJTBDBAgrNAEOnZYE/vomh
4jQk75KWyHNGrYWO0KUT1tlOWnRUCUdQQhOaoIQ12GEKsVCgEAVSAge88RIufelMxxrQal7iEkLg
oCv5uFOffvOPE0XMMvjggy74IAoZ3UI8aYEEJUh1CkoggxIOUIbCbFUZyczADM4K1rI69rHVxARj
kyDFtRppp9OawR8pAFQS6s6EvSuq0xZZNS444gkZ1SgVQkELWvjMr1QlQgT+pgALG+yTIDrgwAPo
wFiwhtWxNZUsYxVBWYX6YAYT0CwgHwDRB0i0PNGoghTsCoQoaEIYQhCCz7ZLhCYoIAdD+ZEyQqAB
C4xBEb09a3Brmt5LBE0RWYiAB/mo2EBSoJvfdG5QP3vI0JpztOgsLR8y8QTU4jUK2U2wEIagBAWU
AQy3JcgIUqSF97b3wu9VhCXQwErLKpYCDvXmmygQV+UEQLpScKUPfACEFjuBCGuAhQ4gXBLxIjZa
QrBEhtGL3rPyOMOWCHIiOkxfCzT0oc2lwH7J6d+lKTLAVfPIdAu8hCUAwQlCIIMBikAJCEeYIMm4
gAxmkIggB3nHOzazJcb+QIXZ6bHIIPZmT0FMYj2RyUw50EEZRIAASnzheoctSJEekIgyq/nQalaE
E2QXAYHlFANx1iyILYDcJYOWqP9d4VFLi62PgEQkGAl1mI5p44HcYMxoQISqC21oIYcxDUuowOwk
IAMOTDEDGAAnBR5gARyAE5Al1pMytIM5UiuEBxWwQBIOoepmO1sRd/BBBWgnMGo9a758xECmcOBr
QE5Av55lMqadbNThldYjX/h0qEVyvVIDiFpEOIS85b3qOjBBBrODgL4foCZoWVsG2cZAt5fL7ToL
WyAVWeAxA42QScjgAkQoRCHmrYhGgDAC+s54AjbAAQ4s4GDeFHOuvf3/ABwMQBgiUHK4L620TJP2
3J7WSEhG1MmJRKILsJzDxBfxhfLWL+MZn4AGOm5rgj2cWrJ8wAB2sAMRFEMYBtcTRUpCdXcbZDV8
sIAExoAHHuA7At2sYv3Q5PEOQmvXTE/7DlCu8kLyd6gtJzeANw3zPaRb5uwOIkoV0gY2SNsCgG+0
DFJwJFhWMbkDK7qHRcD4xjMeBxMoQAGEHYSpWz0hPlhANHxggWtyYBnMQAYIKvBwCZj+9GCHqAUc
kFMdOF4EOzBAAXoA2JX3d9zAm7u5oxxzW4164doaiAM0rwwU0IAHz4hGAEDfAjH74PTQn4G0EpAA
Z9HX9Y03wAEKcIAB/oDAYQc/CQkcEIBoPAMGzoDBM2KwfGa0QAMXOBLg5y8B6V/gAVNowhQogIEV
61kEDXAAPdADTVAJaKBjtgd3KCR3mrZ7nWZ36kZzx0QIV5AQGNAC5Xd+x6B+7Md8KYBN0oZkziIt
E4AAKTAACtBQ8ZIA3NcBKrAMMRB+RfEAzLAM0aAMz/ACLwANyrcMyNACKXABCwA40VKEFPBwRtYE
cjAHhmAEU5AAAzgFYjAHrHZmCVhODPhyvAeBtkJzNUYIs5AQNLgM5VeBV9CDoQeEIZABICADbviG
FBAtRqYAzCAQAVACOSAACFACMngYFqACNRgAgiiIy+CDLQCEJCAD/yWgAV7ViHF4ATOQAFMABxI3
cWM0B6tWhQjoduIWd7nXgC20hXfHbkOBPRSYECFgAchQg4VYiMyQhikAAjdwAStgAydyIm1yARVA
AQXQASvQhzYSAA2AAav4iq/4g0AYiyRwATRQAiqgAggwAxYgA7t4AAcQAjcIjBTSAgYwAySADOB4
iMkoi7uCAQuQJBYgZj3FfQOwDNpYJSnQAROAAZozjuS4AAsAfzLgAGzyACzYfXX4jlVSAmVAfQ+w
MCRgAyRAAvhIMCmCXNtXAAYQAu4okHryAzaAARNgjQYJJxNAfRF5AAaQAy2QjRYpdWBQBV2QawrA
gpLHfQpgAA1ggiMrYJInKWxIsRhfUAU82ZMj0Iwr8AM3qY3E9ntVV3lDWSUBAQA7
--=_NextPart_000_007B_01C3899F.FBB4C780
Content-Type: image/gif
Content-Transfer-Encoding: base64
Content-ID: <007001c3898f$3822cfc0$216411ac@.tibork>
R0lGODlhDAAMANUAAP////f3//f39+/v9+/v797m987W787W5sXW5rXF76295qW975y175St75St
3pSlzoyl1oSl5oylzoycxXOU3nOMxWOM5mOM3mOE1lqE3mOEvVKE1lp7xVJ71lJ7zlJ7xVJ7vUp7
zkpzzkpzxVJzrUprvUJrxUJrvUJjtTpjtTpjrTparTpapQAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACwAAAAADAAMAAAIjAABAAhwwMGFCxAQ
CACwkICDDBYSLGjQwQEBhg8zDBAIYIEIBwIQdLjAoOOFgSFMIICwIUMEAxQwCBxhAgKHDh5C6DQA
IIGJEyA4fPAwYoQCAAVKoEgBQsKJEidQ8CyRYumDA1VTqNBQQYXXFQofsPB6AIAKFiweNBTLoiza
BxcFCjgwgQSJCQcWCggIADs=--=_NextPart_000_007B_01C3899F.FBB4C780--

Wednesday, March 7, 2012

Inserting Unicode data

Hi.

We have a sql server db that we need to store Unicode text in. The fields are of the type nvarchar, ntext and nchar. Our solution uses both Oracle and SqlServer as a backing database. In Oracle there is a connection string switch "Unicode=True" that fixes the problem. Is there something similar in SqlServer? Since the db layer is generic we'ed like to avoid using a N' prefix on text strings in query statements.

Hi Kim,

As far as I can see, when the fields types are set to such as NVarChar and the update parameters have been set to the corresponding type, the data will be updated as Unicode in SQL Server. When updating the database, the N' prefix will be added automatically by ADO.NET.

That means you don't need to add anything additional to achieve this.

Are you getting some problem when updating the data in the way I mentioned? If so, please let me know the problem. Thanks!

|||

Hi Kevin,

Thank you for the response. The problem is that this is a system that's gone into production. The code is not written by me and I'm sorry to say it doesn't use command parameters for transfering variables. I guess the fix will have to wait until the next upgrade.

Friday, February 24, 2012

inserting single precision data into sql server float column

Hi,
iam using the bcp api to load data into sql server. The data to be loaded
is single precision and hence my bcp_bind type is SQLFLT4. The column in my
sql server table is a FLOAT(which is of course double precision).
If i try to insert say a value 73.22 it gets inserted as 73.22000122070313.
I mean the documentation says that implicit conversion for these types are
allowed. So iam not sure why this happens.
Appreciate any inputs.
VivekHi Vivek,
Thats the way float works:
"Approximate-number data types for use with floating point numeric
data. Floating point data is approximate; therefore, not all values in
the data type range can be represented exactly. "
DECLARE @.SOMEValue Float(2)
SEt @.SomeValue = 1.100001
SELECT @.SOMEValue
For more precicion you have to use another database like decimal.
HTH, Jens Suessmeyer.|||Because a float in SQL Server is an *approximate* floating point
representation, essentially meaning if you round it to the appropriate
number of significant digits then you'll get the number you're after but
it's only stored as accurately as the binary numbering system can manage
(defined by IEEE 754). The same would happen if you used real rather
than float. I think what you're after is an *exact* floating point
representation, which corresponds to the numeric (or decimal) data types
in SQL Server (i.e. fixed precision & scale).
See Using decimal, float and real data
<http://msdn.microsoft.com/library/e...con_03_6mht.asp> in
SQL Books Online.
*mike hodgson*
http://sqlnerd.blogspot.com
Vivek wrote:

>Hi,
> iam using the bcp api to load data into sql server. The data to be loaded
>is single precision and hence my bcp_bind type is SQLFLT4. The column in my
>sql server table is a FLOAT(which is of course double precision).
>If i try to insert say a value 73.22 it gets inserted as 73.22000122070313.
>I mean the documentation says that implicit conversion for these types are
>allowed. So iam not sure why this happens.
>Appreciate any inputs.
>Vivek
>|||I think my query was not stated clearly. If i load the same value into a
REAL column it shows exactly what i inserted. (73.22)
Similarily if i store that value in a double precision program variable and
load into a FLOAT column it shows exactly what i stored.
The problem is when the value is in a single precision program variable and
i load into a FLOAT
"Mike Hodgson" wrote:

> Because a float in SQL Server is an *approximate* floating point
> representation, essentially meaning if you round it to the appropriate
> number of significant digits then you'll get the number you're after but
> it's only stored as accurately as the binary numbering system can manage
> (defined by IEEE 754). The same would happen if you used real rather
> than float. I think what you're after is an *exact* floating point
> representation, which corresponds to the numeric (or decimal) data types
> in SQL Server (i.e. fixed precision & scale).
> See Using decimal, float and real data
> <http://msdn.microsoft.com/library/e...con_03_6mht.asp> in
> SQL Books Online.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> Vivek wrote:
>
>|||> I think my query was not stated clearly. If i load the same value into a
> REAL column it shows exactly what i inserted. (73.22)
We understood the question. You do not understand the issue. Did you read
BOL regarding their definition and usage (Accessing and Changing Relational
Data / Using decimal, float, and real Data)? If so, then contine with
http://docs.sun.com/source/806-3568/ncg_goldberg.html. Do not confuse
representation of a value with the actual value. What you claim to see can
also be an artifact of whatever technique you use to "see" the value after
storage in the database. Below is a script that demonstrates the problem
more clearly.
set nocount on
declare @.test1 real, @.test2 float, @.test3 float(2)
set @.test1 = 73.22
set @.test2 = 73.22
set @.test3 = 73.22
select @.test1, @.test2, @.test3
select cast(@.test1 as varbinary(8)), cast(@.test2 as varbinary(8)),
cast(@.test3 as varbinary(8))
print @.test1
print @.test2
print @.test3|||That's what i thought initially. (I use Query Analyzer btw) After inserting
the same value (from a double precision and single precision variable
respectively) into a FLOAT column, if i do a 'select * from tfloat' i get
this output:
73.22
73.22000122070313
Would you say that the actual values of both rows are the same regardless of
what i see above?
The binary values are:
0x40524E147AE147AE
0x40524E1480000000
"Scott Morris" wrote:

> We understood the question. You do not understand the issue. Did you re
ad
> BOL regarding their definition and usage (Accessing and Changing Relationa
l
> Data / Using decimal, float, and real Data)? If so, then contine with
> http://docs.sun.com/source/806-3568/ncg_goldberg.html. Do not confuse
> representation of a value with the actual value. What you claim to see ca
n
> also be an artifact of whatever technique you use to "see" the value after
> storage in the database. Below is a script that demonstrates the problem
> more clearly.
> set nocount on
> declare @.test1 real, @.test2 float, @.test3 float(2)
> set @.test1 = 73.22
> set @.test2 = 73.22
> set @.test3 = 73.22
> select @.test1, @.test2, @.test3
> select cast(@.test1 as varbinary(8)), cast(@.test2 as varbinary(8)),
> cast(@.test3 as varbinary(8))
> print @.test1
> print @.test2
> print @.test3
>
>|||On Mon, 23 Jan 2006 03:39:03 -0800, Vivek wrote:

>That's what i thought initially. (I use Query Analyzer btw) After inserting
>the same value (from a double precision and single precision variable
>respectively) into a FLOAT column, if i do a 'select * from tfloat' i get
>this output:
>73.22
>73.22000122070313
>Would you say that the actual values of both rows are the same regardless o
f
>what i see above?
>The binary values are:
>0x40524E147AE147AE
>0x40524E1480000000
Hi Vivek,
These binary values explainexactly what's going on.
The closest representation in a double precision representation is,
obviosuly, 0x40524E147AE147AE. When you store that in a single precision
variable or column, it has to be rounded to the closest that can be
represented in the 24 bits set aside for single precision, which is
apparently 0x40524E148. If you then store this in a double precision
column, the extra bits are added again - but of course as 0 bits, since
SQL Server has no memory of the bits that were prreviously lost. And so
it ends up as 0x40524E1480000000.
Hugo Kornelis, SQL Server MVP|||Thanks Hugo. That sounds reasonable. So do i just avoid these kind of
insertions and stick to single to single and double to double precision
insertions?
"Hugo Kornelis" wrote:

> On Mon, 23 Jan 2006 03:39:03 -0800, Vivek wrote:
>
> Hi Vivek,
> These binary values explainexactly what's going on.
> The closest representation in a double precision representation is,
> obviosuly, 0x40524E147AE147AE. When you store that in a single precision
> variable or column, it has to be rounded to the closest that can be
> represented in the 24 bits set aside for single precision, which is
> apparently 0x40524E148. If you then store this in a double precision
> column, the extra bits are added again - but of course as 0 bits, since
> SQL Server has no memory of the bits that were prreviously lost. And so
> it ends up as 0x40524E1480000000.
> --
> Hugo Kornelis, SQL Server MVP
>|||On Mon, 23 Jan 2006 20:35:02 -0800, Vivek wrote:

>Thanks Hugo. That sounds reasonable. So do i just avoid these kind of
>insertions and stick to single to single and double to double precision
>insertions?
Hi Vivek,
I don't know what the requirements of your applications are. But as a
rule of thumb, I'd recommend to avoid conversions as much as possible,
stick to the same precision. Once you've lost precision, there's no way
to get it back. But OTOH, storing data at more than required precision
is just a waste of space.
Find the precision you need, then design your DB and application around
that.
Hugo Kornelis, SQL Server MVP|||Thank you guys.
"Hugo Kornelis" wrote:

> On Mon, 23 Jan 2006 20:35:02 -0800, Vivek wrote:
>
> Hi Vivek,
> I don't know what the requirements of your applications are. But as a
> rule of thumb, I'd recommend to avoid conversions as much as possible,
> stick to the same precision. Once you've lost precision, there's no way
> to get it back. But OTOH, storing data at more than required precision
> is just a waste of space.
> Find the precision you need, then design your DB and application around
> that.
> --
> Hugo Kornelis, SQL Server MVP
>

Sunday, February 19, 2012

Inserting Records from Table type to Temp table

Hi,
I want to insert records from table type to temp table without using cursors
in SQL Server 2000 stored procedure.
Regards,
ShanmugamYou mean like below?
DECLARE @.t TABLE (c1 int)
INSERT INTO @.t (c1) VALUES(1)
CREATE TABLE #t (c1 int)
INSERT INTO #t
SELECT c1 FROM @.t
SELECT * FROM @.t
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Uma" <uma@.cspl.com> wrote in message news:OZAWvwpHGHA.3936@.TK2MSFTNGP12.phx.gbl...arkred">
> Hi,
> I want to insert records from table type to temp table without using curso
rs
> in SQL Server 2000 stored procedure.
> Regards,
> Shanmugam
>
>

Inserting Records from Table type to Temp table

Hi,
I want to insert records from table type to temp table without using cursors
in SQL Server 2000 stored procedure.
Regards,
ShanmugamInsert into #temp(columns)
Select columns from @.tab
Madhivananl|||Insert into #temp(columns)
Select columns from @.tab
Madhivanan|||Insert into #temp(columns)
Select columns from @.tab
Madhivanan|||Thanks.
Is it possible to give like below.
DECLARE @.a VARCHAR(50)
SET @.a = '#temp'
Insert into @.a(columns)
Select columns from @.tab
any other way?
Regards,
Shanmugam
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1138177782.869581.254030@.z14g2000cwz.googlegroups.com...
> Insert into #temp(columns)
> Select columns from @.tab
> Madhivanan
>|||You'd have to use dynamic SQL for that. Why don't you know the names of your
objects at design time?
ML
http://milambda.blogspot.com/

inserting records

I am trying to insert records from one table to another and I get the
following error.
The conversion of char data type to smalldatetime data type resulted in an
out-of-range smalldatetime value.
The statement has been terminated.
What do I need to do to get around this?
The statement I am using to insert the records is:
INSERT INTO TIME_DIM2
select DISTINCT
Date_occured_from AS FULL_DATE,
datepart(DW,Date_occured_from ) as DAY_OF_WEEK,
datepart(DD,Date_occured_from ) as DAY_OF_MONTH,
datepart(DY,Date_occured_from ) as DAY_OF_YEAR,
datepart(wk,Date_occured_from ) as WEEK_NUMBER,
datepart(MM,Date_occured_from ) as MONTH_NUMBER,
datepart(YY,Date_occured_from ) as YEAR_NUMBER,
Datename(month,Date_occured_from ) as MONTH_NAME,
datepart(MM,Date_occured_from ) as FISICAL_PERIOD_NO,
datepart(YY,Date_occured_from ) as FISICAL_YEAR,
CASE
WHEN datepart(MM,Date_occured_from )IN('1','2','3')THEN 'Q1'
WHEN datepart(MM,Date_occured_from )IN('4','5','6')THEN 'Q2'
WHEN datepart(MM,Date_occured_from )IN('7','8','9')THEN 'Q3'
WHEN datepart(MM,Date_occured_from )IN('10','11','12')THEN 'Q4'
end as QUARTER,
'MIS' as CREATED_BY,
GETDATE() as CREATED_DATE,
NULL as UPDATED_BY,
NULL as UPDATED_DATE
FROM bi..Allfile_up
The source table has the follwing structure
[Det_coll] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[File] [numeric](18, 0) NULL ,
[Unit_coll] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Date_updated] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ORI] [char] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OSR_code] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Details] [char] (240) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Date_open] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Ass_coll] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Jur_coll] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Status] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Member_out] [char] (26) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Diary_date] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Date_occured_from] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Time_occured_from] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Date_occured_to] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Time_occured_to] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Restriction] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Location] [char] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Extract_date] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[count] [numeric](18, 0) NULL
The destination table has the following structure
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[time_dim2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[time_dim2]
GO
[TIME_KY] [int] IDENTITY (1, 1) NOT NULL ,
[FULL_DATE] [smalldatetime] NOT NULL ,
[DAY_OF_WEEK] [smallint] NULL ,
[DAY_OF_MONTH] [smallint] NULL ,
[DAY_OF_YEAR] [smallint] NULL ,
[WEEK_NUMBER] [smallint] NULL ,
[MONTH_NUMBER] [smallint] NULL ,
[YEAR_NUMBER] [smallint] NULL ,
[MONTH_NAME] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FISICAL_PERIOD_NO] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FISICAL_YEAR] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[QUARTER] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CREATED_BY] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CREATED_DATE] [smalldatetime] NULL ,
[UPDATED_BY] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UPDATED_DATE] [smalldatetime] NULL
ThanksMunch,
What format are you using to represent the date value in Date_occured_from?.
Smalldatetime data type range is 19000101 through 20790606.
AMB
"Munch" wrote:

> I am trying to insert records from one table to another and I get the
> following error.
> The conversion of char data type to smalldatetime data type resulted in an
> out-of-range smalldatetime value.
> The statement has been terminated.
> What do I need to do to get around this?
>
> The statement I am using to insert the records is:
> INSERT INTO TIME_DIM2
> select DISTINCT
> Date_occured_from AS FULL_DATE,
> datepart(DW,Date_occured_from ) as DAY_OF_WEEK,
> datepart(DD,Date_occured_from ) as DAY_OF_MONTH,
> datepart(DY,Date_occured_from ) as DAY_OF_YEAR,
> datepart(wk,Date_occured_from ) as WEEK_NUMBER,
> datepart(MM,Date_occured_from ) as MONTH_NUMBER,
> datepart(YY,Date_occured_from ) as YEAR_NUMBER,
> Datename(month,Date_occured_from ) as MONTH_NAME,
> datepart(MM,Date_occured_from ) as FISICAL_PERIOD_NO,
> datepart(YY,Date_occured_from ) as FISICAL_YEAR,
> CASE
> WHEN datepart(MM,Date_occured_from )IN('1','2','3')THEN 'Q1'
> WHEN datepart(MM,Date_occured_from )IN('4','5','6')THEN 'Q2'
> WHEN datepart(MM,Date_occured_from )IN('7','8','9')THEN 'Q3'
> WHEN datepart(MM,Date_occured_from )IN('10','11','12')THEN 'Q4'
> end as QUARTER,
> 'MIS' as CREATED_BY,
> GETDATE() as CREATED_DATE,
> NULL as UPDATED_BY,
> NULL as UPDATED_DATE
> FROM bi..Allfile_up
>
> The source table has the follwing structure
> [Det_coll] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [File] [numeric](18, 0) NULL ,
> [Unit_coll] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Date_updated] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ORI] [char] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [OSR_code] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Details] [char] (240) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Date_open] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Ass_coll] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Jur_coll] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Status] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Member_out] [char] (26) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Diary_date] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Date_occured_from] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [Time_occured_from] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Date_occured_to] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Time_occured_to] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Restriction] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Location] [char] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Extract_date] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [count] [numeric](18, 0) NULL
> The destination table has the following structure
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[time_dim2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[time_dim2]
> GO
> [TIME_KY] [int] IDENTITY (1, 1) NOT NULL ,
> [FULL_DATE] [smalldatetime] NOT NULL ,
> [DAY_OF_WEEK] [smallint] NULL ,
> [DAY_OF_MONTH] [smallint] NULL ,
> [DAY_OF_YEAR] [smallint] NULL ,
> [WEEK_NUMBER] [smallint] NULL ,
> [MONTH_NUMBER] [smallint] NULL ,
> [YEAR_NUMBER] [smallint] NULL ,
> [MONTH_NAME] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [FISICAL_PERIOD_NO] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [FISICAL_YEAR] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [QUARTER] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [CREATED_BY] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CREATED_DATE] [smalldatetime] NULL ,
> [UPDATED_BY] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [UPDATED_DATE] [smalldatetime] NULL
>
> Thanks|||Some date in the table is outside the range acceptable for a smalldatetime..
run the following and it will identify the bad recoreds:
Select Date_occured_from
FROM bi..Allfile_up
Where IsDate(Date_occured_from) = 0 Or
(IsDate(Date_occured_from) = 1 And
Date_occured_from Not Between '19000101' And '20790606')
"Munch" wrote:

> I am trying to insert records from one table to another and I get the
> following error.
> The conversion of char data type to smalldatetime data type resulted in an
> out-of-range smalldatetime value.
> The statement has been terminated.
> What do I need to do to get around this?
>
> The statement I am using to insert the records is:
> INSERT INTO TIME_DIM2
> select DISTINCT
> Date_occured_from AS FULL_DATE,
> datepart(DW,Date_occured_from ) as DAY_OF_WEEK,
> datepart(DD,Date_occured_from ) as DAY_OF_MONTH,
> datepart(DY,Date_occured_from ) as DAY_OF_YEAR,
> datepart(wk,Date_occured_from ) as WEEK_NUMBER,
> datepart(MM,Date_occured_from ) as MONTH_NUMBER,
> datepart(YY,Date_occured_from ) as YEAR_NUMBER,
> Datename(month,Date_occured_from ) as MONTH_NAME,
> datepart(MM,Date_occured_from ) as FISICAL_PERIOD_NO,
> datepart(YY,Date_occured_from ) as FISICAL_YEAR,
> CASE
> WHEN datepart(MM,Date_occured_from )IN('1','2','3')THEN 'Q1'
> WHEN datepart(MM,Date_occured_from )IN('4','5','6')THEN 'Q2'
> WHEN datepart(MM,Date_occured_from )IN('7','8','9')THEN 'Q3'
> WHEN datepart(MM,Date_occured_from )IN('10','11','12')THEN 'Q4'
> end as QUARTER,
> 'MIS' as CREATED_BY,
> GETDATE() as CREATED_DATE,
> NULL as UPDATED_BY,
> NULL as UPDATED_DATE
> FROM bi..Allfile_up
>
> The source table has the follwing structure
> [Det_coll] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [File] [numeric](18, 0) NULL ,
> [Unit_coll] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Date_updated] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ORI] [char] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [OSR_code] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Details] [char] (240) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Date_open] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Ass_coll] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Jur_coll] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Status] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Member_out] [char] (26) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Diary_date] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Date_occured_from] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [Time_occured_from] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Date_occured_to] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Time_occured_to] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Restriction] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Location] [char] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Extract_date] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [count] [numeric](18, 0) NULL
> The destination table has the following structure
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[time_dim2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[time_dim2]
> GO
> [TIME_KY] [int] IDENTITY (1, 1) NOT NULL ,
> [FULL_DATE] [smalldatetime] NOT NULL ,
> [DAY_OF_WEEK] [smallint] NULL ,
> [DAY_OF_MONTH] [smallint] NULL ,
> [DAY_OF_YEAR] [smallint] NULL ,
> [WEEK_NUMBER] [smallint] NULL ,
> [MONTH_NUMBER] [smallint] NULL ,
> [YEAR_NUMBER] [smallint] NULL ,
> [MONTH_NAME] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [FISICAL_PERIOD_NO] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [FISICAL_YEAR] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [QUARTER] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [CREATED_BY] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CREATED_DATE] [smalldatetime] NULL ,
> [UPDATED_BY] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [UPDATED_DATE] [smalldatetime] NULL
>
> Thanks

inserting ole-object

Hello

I want to insert an iostream object into a ms acces db. I use ole-obect as
the data type for the specific column. Inserting a new record works fine but
somehow the field where the ole-object (iostream) has to be interted stays
empty.

Is ole-obect a proper data type for an iostream or should I use something
else.

thanks for the response

stijn[posted and mailed]

Stijn Oude Brunink (soudebrunink@.chello.nl) writes:
> I want to insert an iostream object into a ms acces db. I use ole-obect
> as the data type for the specific column. Inserting a new record works
> fine but somehow the field where the ole-object (iostream) has to be
> interted stays empty.
> Is ole-obect a proper data type for an iostream or should I use something
> else.

You should probably ask this question in comp.databases.ms-access. This
newsgroup is for MS SQL Server, which is something else.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Inserting Null Value

Is there a way, I can insert NULL value to "DT_Date" type Row Column using Script Component Transformation of Data flow?

Ie. I have a column named Ordered Date which is type DT_Date. Based on some condition within a script component task, I want to set the value to NULL, however, since the type is date it will not allow nulls.

Found the answer, it was fairly simple. Row.FieldName_ISNULL() property is a read/write property. This property can be used to set nulls.

|||I'm assuming you are also doing some other work in the Script, but I thought I would mention that you can also do this in the Derived Column transform with the NULL() function. In your case, NULL(DT_DATE).|||Let me just add that the <columnname>_IsNull property may be read/write, or may be read-only, depending on the corresponding Usage Type specified for the individual column.

-Doug
|||

I have a simple package that takes data from excel sources, runs a Data Transformation to get the decimal conversion etc into the same format as SQL ... but some of the columns are nullable - so my Data Transformation fails when it hits a null value in the source... (converting from float in the source to decimal in the destination.. though becuase of the empty values it sees the source as a string). Seems like it should be simple to allow it to pass nulls but I can't seem to figure it out (am new to SSIS). None of the fields in the transformation appear to be editable so that I can add the NULL() as mentioned above - if I got into the Advanced editor I can manually type the data type but NULL(DT_DECIMAL) gives me error "DataTypeConverter cannot convert from System.String."

Can anyone point me in the right direction? :)

|||

<<a Data Transformation to get the decimal conversion etc into the same format as SQL >>

I believe your problem is in the decimal conversion processing you mention. The expression - or code - should be written to handle the occurence of nulls without bombing...

|||

That is just it... I am not "writing" anything - its not a script component transformation task but rather just a "Data Conversion" object. If you edit the Data Conversion object it has input and then output... you can change the output alias, datatype, scale, codepage etc but there isn't any options regarding nulls that I can find.

|||

So the problem is that the source data looks like an empty string, and in that case you want to put NULL in the destination? You might try using a Derived Column instead of the Data Conversion. There, you can write an expression.

For example, if your string input column is named "InputCol", and you are converting to DT_DECIMAL with scale 10, your expression for the new column would look like:

LEN(InputCol) == 0 ? NULL(DT_DECIMAL, 10) : (DT_DECIMAL, 10)InputCol

Let me know if that helps.

Mark

Inserting Null Value

Is there a way, I can insert NULL value to "DT_Date" type Row Column using Script Component Transformation of Data flow?

Found the answer, it was fairly simple. Row.FieldName_ISNULL() property is a read/write property. This property can be used to set nulls.

-Doug
|||

Can anyone point me in the right direction? :)

|||

<<a Data Transformation to get the decimal conversion etc into the same format as SQL >>

I believe your problem is in the decimal conversion processing you mention. The expression - or code - should be written to handle the occurence of nulls without bombing...

|||

For example, if your string input column is named "InputCol", and you are converting to DT_DECIMAL with scale 10, your expression for the new column would look like:

LEN(InputCol) == 0 ? NULL(DT_DECIMAL, 10) : (DT_DECIMAL, 10)InputCol

Let me know if that helps.

Mark

Wednesday, March 28, 2012

install Office ifilter on SQL Server 2005

Friday, March 23, 2012

Install important update from MS Corporation

Wednesday, March 7, 2012

Inserting Unicode data

Friday, February 24, 2012

inserting single precision data into sql server float column

Sunday, February 19, 2012

Inserting Records from Table type to Temp table

Inserting Records from Table type to Temp table

inserting records

inserting records

inserting ole-object

Inserting Null Value

Inserting Null Value

Inserting Null Value

Blog Archive

About Me