Bulk insert of long unicode strings

Here is the situation, please let me know if you have any tips:

..TXT files in a share at \\foo

SPROCS run daily parses of many things, including data on that share. The
other day, we encountered rows in the TXT files which looked like:

column1Row1data,column2Row1data
column1Row2data,column2Row2data

...etc..

However, column2 was about 6000 bytes of unicode. We are bulk inserting
into a table specifying nvarchar(4000). When it encounters high unicode
rows, it throws a truncation error (16).

We really need information contained in the first 200 bytes of the string in
column2. However, the errors are causing the calling SPROC to abort.
Please let me know if you have any suggestions on workarounds for this
situation. Ideally, we would only Bulk Insert a sub-section of column2 if
possible.

Thanks!
/TyTy (tybala on the server at hotmail.com) writes:
> Here is the situation, please let me know if you have any tips:
> .TXT files in a share at \\foo
> SPROCS run daily parses of many things, including data on that share. The
> other day, we encountered rows in the TXT files which looked like:
> column1Row1data,column2Row1data
> column1Row2data,column2Row2data
> ..etc..
> However, column2 was about 6000 bytes of unicode. We are bulk inserting
> into a table specifying nvarchar(4000). When it encounters high unicode
> rows, it throws a truncation error (16).
> We really need information contained in the first 200 bytes of the
> string in column2.

You can use a format file like this one:

8.0
3
1 SQLNCHAR 0 200 "" 1 a Finnish_Swedish_CS_AS
2 SQLNCHAR 0 0 "," 0 dummy ""
3 SQLNCHAR 0 0 "\r\n" 2 b Finnish_Swedish_CS_AS

Here you defined the host file to have three fields: the first is a
200-character long fixed length field, the second is closed by a ,
and the third field is close by end-of-line. By specifying a 0 in
the sixth column in the format file for the second field, you specify
that this field is is not be imported into SQL Server.

You may want to change the collation what fits with the collation you
use in your database.

Note that this only works if all occurrances of the first field is
more than 200 characters. Would there be a record with a shorter
length of this field, it will steal characters from the second field.
(You would probably get an error when importing the file, as BCP will
not find the delimiter for the second field.)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I wanted to thank you for answering my question. We successfully
implemented a variant of your solution last night. The assistance is much
appreciated.

/Ty

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns9401F28A16A1CYazorman@.127.0.0.1...
> Ty (tybala on the server at hotmail.com) writes:
> > Here is the situation, please let me know if you have any tips:
> > .TXT files in a share at \\foo
> > SPROCS run daily parses of many things, including data on that share.
The
> > other day, we encountered rows in the TXT files which looked like:
> > column1Row1data,column2Row1data
> > column1Row2data,column2Row2data
> > ..etc..
> > However, column2 was about 6000 bytes of unicode. We are bulk inserting
> > into a table specifying nvarchar(4000). When it encounters high unicode
> > rows, it throws a truncation error (16).
> > We really need information contained in the first 200 bytes of the
> > string in column2.
> You can use a format file like this one:
> 8.0
> 3
> 1 SQLNCHAR 0 200 "" 1 a Finnish_Swedish_CS_AS
> 2 SQLNCHAR 0 0 "," 0 dummy ""
> 3 SQLNCHAR 0 0 "\r\n" 2 b Finnish_Swedish_CS_AS
> Here you defined the host file to have three fields: the first is a
> 200-character long fixed length field, the second is closed by a ,
> and the third field is close by end-of-line. By specifying a 0 in
> the sixth column in the format file for the second field, you specify
> that this field is is not be imported into SQL Server.
> You may want to change the collation what fits with the collation you
> use in your database.
> Note that this only works if all occurrances of the first field is
> more than 200 characters. Would there be a record with a shorter
> length of this field, it will steal characters from the second field.
> (You would probably get an error when importing the file, as BCP will
> not find the delimiter for the second field.)
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

builtin/administrators

Hi, our sql box has too many people (including sql service account) with
local admin rights, some even have domain admin rights. I am trying to
tighten the security on those boxes and decrease the security level of those
people. What I am planning to do is take builtin/administrator login out of
the sql box, and add sql services account back and grant SA rights to it.
Does anyone see any problem with this approach? Thanks.
I suggest you start with creating a new windows group, ad appropriate windows users, add it to SQL
Server and grant sysadmin for that. Unless you already have such, of course. :-)
One thing I know can be a problem is full-text search. I think that "local system" is granted login
to SQL server through builtin\Administrators, and hereby lies the problem. That should be OK by
adding "NT Authority" (or whatever the physical name for LocalSystem is). I suggest you Google the
archives for a bit more info on that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"flologic" <flo@.flo.net> wrote in message news:eReee6FhEHA.632@.TK2MSFTNGP12.phx.gbl...
> Hi, our sql box has too many people (including sql service account) with
> local admin rights, some even have domain admin rights. I am trying to
> tighten the security on those boxes and decrease the security level of those
> people. What I am planning to do is take builtin/administrator login out of
> the sql box, and add sql services account back and grant SA rights to it.
> Does anyone see any problem with this approach? Thanks.
>
|||flo,
Here is an article (talking about clusters) that addresses some of your
concerns.
http://support.microsoft.com/default...b;en-us;263712
One example of a side-effect that you must manage is:
http://support.microsoft.com/default...b;en-us;237604
Also, from the BOL on: Setting up Windows Services Accounts
If the startup account assigned to the MSSQLServer Service is not a member
of the Local Administrators group, or if the BUILTIN\Administrators SQL
Server login has been removed, you must add the startup account for the
MSSQLServer service or the SQLServerAgent service, or both, to the SQL
Server system administrators (sysadmin) role. Grant the [Domain\NTaccount]
user a logon to SQL Server.
Hope that helps you.
Russell Fields
"flologic" <flo@.flo.net> wrote in message
news:eReee6FhEHA.632@.TK2MSFTNGP12.phx.gbl...
> Hi, our sql box has too many people (including sql service account) with
> local admin rights, some even have domain admin rights. I am trying to
> tighten the security on those boxes and decrease the security level of
those
> people. What I am planning to do is take builtin/administrator login out
of
> the sql box, and add sql services account back and grant SA rights to it.
> Does anyone see any problem with this approach? Thanks.
>
|||The account for local system is NT Authority\SYSTEM.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||If you add ANY NT group and grant that group SQL Admin privileges then you
can NOT prevent the NT admins from coming in... All they have to do is add
themselves to the NT group;...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:Of5AOPGhEHA.1356@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> flo,
> Here is an article (talking about clusters) that addresses some of your
> concerns.
> http://support.microsoft.com/default...b;en-us;263712
> One example of a side-effect that you must manage is:
> http://support.microsoft.com/default...b;en-us;237604
> Also, from the BOL on: Setting up Windows Services Accounts
> If the startup account assigned to the MSSQLServer Service is not a member
> of the Local Administrators group, or if the BUILTIN\Administrators SQL
> Server login has been removed, you must add the startup account for the
> MSSQLServer service or the SQLServerAgent service, or both, to the SQL
> Server system administrators (sysadmin) role. Grant the [Domain\NTaccount]
> user a logon to SQL Server.
> Hope that helps you.
> Russell Fields
> "flologic" <flo@.flo.net> wrote in message
> news:eReee6FhEHA.632@.TK2MSFTNGP12.phx.gbl...
> those
> of
it.
>

builtin/administrators

Hi, our sql box has too many people (including sql service account) with
local admin rights, some even have domain admin rights. I am trying to
tighten the security on those boxes and decrease the security level of those
people. What I am planning to do is take builtin/administrator login out of
the sql box, and add sql services account back and grant SA rights to it.
Does anyone see any problem with this approach? Thanks.I suggest you start with creating a new windows group, ad appropriate windows users, add it to SQL
Server and grant sysadmin for that. Unless you already have such, of course. :-)
One thing I know can be a problem is full-text search. I think that "local system" is granted login
to SQL server through builtin\Administrators, and hereby lies the problem. That should be OK by
adding "NT Authority" (or whatever the physical name for LocalSystem is). I suggest you Google the
archives for a bit more info on that.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"flologic" <flo@.flo.net> wrote in message news:eReee6FhEHA.632@.TK2MSFTNGP12.phx.gbl...
> Hi, our sql box has too many people (including sql service account) with
> local admin rights, some even have domain admin rights. I am trying to
> tighten the security on those boxes and decrease the security level of those
> people. What I am planning to do is take builtin/administrator login out of
> the sql box, and add sql services account back and grant SA rights to it.
> Does anyone see any problem with this approach? Thanks.
>|||flo,
Here is an article (talking about clusters) that addresses some of your
concerns.
http://support.microsoft.com/default.aspx?scid=kb;en-us;263712
One example of a side-effect that you must manage is:
http://support.microsoft.com/default.aspx?scid=kb;en-us;237604
Also, from the BOL on: Setting up Windows Services Accounts
If the startup account assigned to the MSSQLServer Service is not a member
of the Local Administrators group, or if the BUILTIN\Administrators SQL
Server login has been removed, you must add the startup account for the
MSSQLServer service or the SQLServerAgent service, or both, to the SQL
Server system administrators (sysadmin) role. Grant the [Domain\NTaccount]
user a logon to SQL Server.
Hope that helps you.
Russell Fields
"flologic" <flo@.flo.net> wrote in message
news:eReee6FhEHA.632@.TK2MSFTNGP12.phx.gbl...
> Hi, our sql box has too many people (including sql service account) with
> local admin rights, some even have domain admin rights. I am trying to
> tighten the security on those boxes and decrease the security level of
those
> people. What I am planning to do is take builtin/administrator login out
of
> the sql box, and add sql services account back and grant SA rights to it.
> Does anyone see any problem with this approach? Thanks.
>|||The account for local system is NT Authority\SYSTEM.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||If you add ANY NT group and grant that group SQL Admin privileges then you
can NOT prevent the NT admins from coming in... All they have to do is add
themselves to the NT group;...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:Of5AOPGhEHA.1356@.TK2MSFTNGP09.phx.gbl...
> flo,
> Here is an article (talking about clusters) that addresses some of your
> concerns.
> http://support.microsoft.com/default.aspx?scid=kb;en-us;263712
> One example of a side-effect that you must manage is:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;237604
> Also, from the BOL on: Setting up Windows Services Accounts
> If the startup account assigned to the MSSQLServer Service is not a member
> of the Local Administrators group, or if the BUILTIN\Administrators SQL
> Server login has been removed, you must add the startup account for the
> MSSQLServer service or the SQLServerAgent service, or both, to the SQL
> Server system administrators (sysadmin) role. Grant the [Domain\NTaccount]
> user a logon to SQL Server.
> Hope that helps you.
> Russell Fields
> "flologic" <flo@.flo.net> wrote in message
> news:eReee6FhEHA.632@.TK2MSFTNGP12.phx.gbl...
> > Hi, our sql box has too many people (including sql service account) with
> > local admin rights, some even have domain admin rights. I am trying to
> > tighten the security on those boxes and decrease the security level of
> those
> > people. What I am planning to do is take builtin/administrator login out
> of
> > the sql box, and add sql services account back and grant SA rights to
it.
> > Does anyone see any problem with this approach? Thanks.
> >
> >
>

builtin/administrators

Hi, our sql box has too many people (including sql service account) with
local admin rights, some even have domain admin rights. I am trying to
tighten the security on those boxes and decrease the security level of those
people. What I am planning to do is take builtin/administrator login out of
the sql box, and add sql services account back and grant SA rights to it.
Does anyone see any problem with this approach? Thanks.I suggest you start with creating a new windows group, ad appropriate window
s users, add it to SQL
Server and grant sysadmin for that. Unless you already have such, of course.
:-)
One thing I know can be a problem is full-text search. I think that "local s
ystem" is granted login
to SQL server through builtin\Administrators, and hereby lies the problem. T
hat should be OK by
adding "NT Authority" (or whatever the physical name for LocalSystem is). I
suggest you Google the
archives for a bit more info on that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"flologic" <flo@.flo.net> wrote in message news:eReee6FhEHA.632@.TK2MSFTNGP12.phx.gbl...[vbcol
=seagreen]
> Hi, our sql box has too many people (including sql service account) with
> local admin rights, some even have domain admin rights. I am trying to
> tighten the security on those boxes and decrease the security level of tho
se
> people. What I am planning to do is take builtin/administrator login out o
f
> the sql box, and add sql services account back and grant SA rights to it.
> Does anyone see any problem with this approach? Thanks.
>[/vbcol]|||flo,
Here is an article (talking about clusters) that addresses some of your
concerns.
http://support.microsoft.com/defaul...kb;en-us;263712
One example of a side-effect that you must manage is:
http://support.microsoft.com/defaul...kb;en-us;237604
Also, from the BOL on: Setting up Windows Services Accounts
If the startup account assigned to the MSSQLServer Service is not a member
of the Local Administrators group, or if the BUILTIN\Administrators SQL
Server login has been removed, you must add the startup account for the
MSSQLServer service or the SQLServerAgent service, or both, to the SQL
Server system administrators (sysadmin) role. Grant the [Domain\NTaccoun
t]
user a logon to SQL Server.
Hope that helps you.
Russell Fields
"flologic" <flo@.flo.net> wrote in message
news:eReee6FhEHA.632@.TK2MSFTNGP12.phx.gbl...
> Hi, our sql box has too many people (including sql service account) with
> local admin rights, some even have domain admin rights. I am trying to
> tighten the security on those boxes and decrease the security level of
those
> people. What I am planning to do is take builtin/administrator login out
of
> the sql box, and add sql services account back and grant SA rights to it.
> Does anyone see any problem with this approach? Thanks.
>|||The account for local system is NT Authority\SYSTEM.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||If you add ANY NT group and grant that group SQL Admin privileges then you
can NOT prevent the NT admins from coming in... All they have to do is add
themselves to the NT group;...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:Of5AOPGhEHA.1356@.TK2MSFTNGP09.phx.gbl...
> flo,
> Here is an article (talking about clusters) that addresses some of your
> concerns.
> http://support.microsoft.com/defaul...kb;en-us;263712
> One example of a side-effect that you must manage is:
> http://support.microsoft.com/defaul...kb;en-us;237604
> Also, from the BOL on: Setting up Windows Services Accounts
> If the startup account assigned to the MSSQLServer Service is not a member
> of the Local Administrators group, or if the BUILTIN\Administrators SQL
> Server login has been removed, you must add the startup account for the
> MSSQLServer service or the SQLServerAgent service, or both, to the SQL
> Server system administrators (sysadmin) role. Grant the [Domain\NTacco
unt]
> user a logon to SQL Server.
> Hope that helps you.
> Russell Fields
> "flologic" <flo@.flo.net> wrote in message
> news:eReee6FhEHA.632@.TK2MSFTNGP12.phx.gbl...
> those
> of
it.[vbcol=seagreen]
>

Building Intelligent applications using SQL Server 2005 Data Mining

Hello,

Are there any samples on Building Intelligent applications using SQL Server 2005 Data Mining uisng Visual Basic.NET, including step-by-step guides? Most of them are in C#. Besides, the June 2005 CTP does not have tutorials on the said topic.

Thanks.We don't have sample code specifically in VB.NET but the object models are exactly the same in all .NET languages so the code should be readily portable. If you encounter any specific issues moving the sample code to VB.NET, we will be happy to help.

In case you missed the sample code (C#) from the recent programmability webcast, it can be found here: http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/1313.aspx

Raman Iyer
SQL Server Data Mining|||Thanks for this as I already have seen this. I sure hope I can work on this but I don't have the sample database named DataValidationModels. Can you post the database as well?

Thanks a lot|||You can download the DataValidation.zip files from SQLServerDataMining.com which contains instructions on how to create the model.|||I want to write DMX queries on the AdventureWorks database specified in the data mining tutorial(from June 2005 CTP) using ADOMD.NET. What are the available mining models in the sample database and do you have sample queries?

Thanks.|||The tutorial can be used to build mining models based on the AdventureWorks database for targeted mailing (using the decision trees, clustering & naive-bayes algorithms), forecasting using time series, market basket analysis (using association rules) and sequence clustering.

For DM programmability using ADOMD.NET (as well as other programming models), please see this article: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sqldmprgrm.asp. It includes sample code and queries.|||I want to use AMO in my application. One documentation specifically mentions to add a reference to the Microsoft.AnalysisServices assembly in the .NET framework 2.0 but this does not appear in the References list in VS.NET 2005. I am using VS.NET 2005 Beta 2 with .NET Framework version 2.0.50215 with June CTP of SQL Server 2005. What could I use instead?|||

If it doen't show up in the list, you can browse to this location and add Microsoft.AnalysisServices.DLL manually (go to the Browse tab in the Add Reference dialog:
\Program Files\Microsoft SQL Server\90\SDK\Assemblies\

|||

I have a question on connection string in ADOMD.NET when using the AdomdConnection object. Can you use other authentication methods other that Windows to connect to Analysis Services and access the mining models?