rda submitsql: text

Showing posts with label text. Show all posts

Friday, March 23, 2012

-R option in BCP

Hi all,
I m trying to use BCP utility to upload a huge text file.
As per microsoft documentation, -R is used to specify the
regional settings. My computer (client) and the sql server
(running in a network machine) has dd/mm/yyyy as the
regional settings. The incomming text file also has date
in dd/mm/yyyy format. But when i execute BCP with -R
option, i still get an invalid date format error message.
I went through a few microsoft support information, that
told me that SP1 should solve this problem. I got SP3a
installed (both in client and in the server).
So what am i missing here? I m sure some of u here
should have also faced the problem. Any type of help is
greatly appreciated.
thanks and regards,
s.ravi sankarAlready answered in several other ng's.
--
Andrew J. Kelly
SQL Server MVP
"Ravi Sankar" <ravi_pv@.lycos.com> wrote in message
news:0aff01c3b026$b426bfb0$a101280a@.phx.gbl...
> Hi all,
> I m trying to use BCP utility to upload a huge text file.
> As per microsoft documentation, -R is used to specify the
> regional settings. My computer (client) and the sql server
> (running in a network machine) has dd/mm/yyyy as the
> regional settings. The incomming text file also has date
> in dd/mm/yyyy format. But when i execute BCP with -R
> option, i still get an invalid date format error message.
> I went through a few microsoft support information, that
> told me that SP1 should solve this problem. I got SP3a
> installed (both in client and in the server).
> So what am i missing here? I m sure some of u here
> should have also faced the problem. Any type of help is
> greatly appreciated.
> thanks and regards,
> s.ravi sankar
>

Wednesday, March 21, 2012

Quotes In BCP

I'm exporting via BCP. I'd like to have quotes around the text values. In
DTS, you have the text qualifier option. Is there a BCP option that
corresponds to the DTS option known as the Text Qualifier?
"SR" <mv2k_2003-news@.yahoo.com> wrote in message
news:7L9mc.5460$1T4.3283@.newssvr27.news.prodigy.co m...
> I'm exporting via BCP. I'd like to have quotes around the text values. In
> DTS, you have the text qualifier option. Is there a BCP option that
> corresponds to the DTS option known as the Text Qualifier?
>
In checking the BCP options, I could not find one that corresponds to DTS...
Steve
|||I cannot find a BCP way to do what you ask. You cannot make a " be the
field terminator. What you can do is still create a DTS package and run it
from the DTSRUN utility if you needed it to be command line.
Jeff Duncan
MCDBA, MCSE+I
"Steve Thompson" <SteveThompson@.nomail.please> wrote in message
news:eSZSddtMEHA.2468@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> "SR" <mv2k_2003-news@.yahoo.com> wrote in message
> news:7L9mc.5460$1T4.3283@.newssvr27.news.prodigy.co m...
In
> In checking the BCP options, I could not find one that corresponds to
DTS...
> Steve
>
|||Yeah I knew about the DTS option. Thanks for the help everybody.
"Jeff Duncan" <jduncan@.gtefcu.org> wrote in message
news:%23hpg0CuMEHA.936@.TK2MSFTNGP11.phx.gbl...
> I cannot find a BCP way to do what you ask. You cannot make a " be the
> field terminator. What you can do is still create a DTS package and run
it
> from the DTSRUN utility if you needed it to be command line.
> --
> Jeff Duncan
> MCDBA, MCSE+I
> "Steve Thompson" <SteveThompson@.nomail.please> wrote in message
> news:eSZSddtMEHA.2468@.TK2MSFTNGP11.phx.gbl...
> In
> DTS...
>
|||SR,

> I'm exporting via BCP. I'd like to have quotes around the text
> values. In DTS, you have the text qualifier option. Is there a
> BCP option that corresponds to the DTS option known as the Text
> Qualifier?
You need to use a format file for this. Using the pubs..authors
table as an example, we'll bcp out of a view that looks like this:
use pubs
go
create view authors_csv as
select null first_quote, * from authors
Note that we are including a dummy column called first_quote
that just returns NULL. It's just a little trick to get the leading
quote on the first column.
The format file looks like this:
8.0
10
1 SQLCHAR 0 0 "\"" 1 first_quote ""
2 SQLCHAR 0 11 "\",\"" 2 au_id ""
3 SQLCHAR 0 40 "\",\"" 3 au_lname ""
4 SQLCHAR 0 20 "\",\"" 4 au_fname ""
5 SQLCHAR 0 12 "\",\"" 5 phone ""
6 SQLCHAR 0 40 "\",\"" 6 address ""
7 SQLCHAR 0 20 "\",\"" 7 city ""
8 SQLCHAR 0 2 "\",\"" 8 state ""
9 SQLCHAR 0 5 "\",\"" 9 zip ""
10 SQLCHAR 0 1 "\"\r\n" 10 contract ""
That dummy column is also in the format file to get the leading
quote on au_id.
Here's the command line:
bcp pubs..authors_csv out authors_csv.dat -fauthors_csv.bcp -S. -T
Linda

Quoted literal strings won't force a phrase match

Hello all,
From what I've read, SQL Server is supposed to do a phrase match when
you do a full text search that contains quoted literal strings. So,
for example, if I did a full text search on the phrase "time out" and
I put it in quotes, it's supposed to search for the full phrase "time
out" and not just look for rows that contain the words "time" or
"out." However, this isn't working for me.
Here is the query that I'm using :
SELECT *
FROM Content_Items ci
INNER JOIN FREETEXTTABLE(Content_Items, hed, '"time out"') AS ft
ON ci.contentItemId = ft.[KEY]
ORDER BY ft.RANK DESC
What's it's doing is this : it's returning a bunch of rows that have
the words "time" or "out" in the column called hed. It's also
returning rows that have the full phrase "time out", but it's giving
those rows the same rank as rows that only contain the word "time."
In this case, that rank is 180.
Is there anything else I should be doing in my query, or is there some
configuration option I should have turned on?
Thanks.
Ok, I've made some progress on this problem. Apparently SQL Server is
ignoring noise words in my phrase match.
For example, I ran this query :
SELECT *
FROM Content_Items ci
INNER JOIN FREETEXTTABLE(Content_Items, hed, '"time capsule"') AS ft
ON ci.contentItemId = ft.[KEY]
ORDER BY ft.RANK DESC
And it did exactly what it was supposed to do, since neither "time"
nor "capsule" is a noise word.
My impression was that noise words aren't stripped out of a full text
search if the search phrase is a quoted literal. Thus, my search for
"time out" should look for the full phrase "time out", and not just
the word "time."
Does anybody know why SQL Server is removing my noise word from the
phrase match?
On Jan 18, 12:49 pm, Afrobla...@.gmail.com wrote:
> Hello all,
> From what I've read, SQL Server is supposed to do a phrase match when
> you do a full text search that contains quoted literal strings. So,
> for example, if I did a full text search on the phrase "time out" and
> I put it in quotes, it's supposed to search for the full phrase "time
> out" and not just look for rows that contain the words "time" or
> "out." However, this isn't working for me.
> Here is the query that I'm using :
> SELECT *
> FROM Content_Items ci
> INNER JOIN FREETEXTTABLE(Content_Items, hed, '"time out"') AS ft
> ON ci.contentItemId = ft.[KEY]
> ORDER BY ft.RANK DESC
> What's it's doing is this : it's returning a bunch of rows that have
> the words "time" or "out" in the column called hed. It's also
> returning rows that have the full phrase "time out", but it's giving
> those rows the same rank as rows that only contain the word "time."
> In this case, that rank is 180.
> Is there anything else I should be doing in my query, or is there some
> configuration option I should have turned on?
> Thanks.
|||A noise word is always a noise word. Noise words are applied to the
building of the index, so the full-text search has nothing to find.
Therefore, if you change the noise word list, you must rebuild the index
before you can search for the former noise word. (It is common to run with
either a single blank or a single nonsense word in the noise word file, so
as to get no noise words.)
Of course, you can do a string search for '%time out%' in addition to the
full-text query.
RLF
"Lepidopterist" <jeremypollack@.gmail.com> wrote in message
news:19bd6a5a-c6b0-486b-a69a-45fc1d5b9e92@.f47g2000hsd.googlegroups.com...
> Ok, I've made some progress on this problem. Apparently SQL Server is
> ignoring noise words in my phrase match.
> For example, I ran this query :
> SELECT *
> FROM Content_Items ci
> INNER JOIN FREETEXTTABLE(Content_Items, hed, '"time capsule"') AS ft
> ON ci.contentItemId = ft.[KEY]
> ORDER BY ft.RANK DESC
> And it did exactly what it was supposed to do, since neither "time"
> nor "capsule" is a noise word.
> My impression was that noise words aren't stripped out of a full text
> search if the search phrase is a quoted literal. Thus, my search for
> "time out" should look for the full phrase "time out", and not just
> the word "time."
> Does anybody know why SQL Server is removing my noise word from the
> phrase match?
> On Jan 18, 12:49 pm, Afrobla...@.gmail.com wrote:
>

Quoted identifiers

SQL 2000: I have a DB maintenance plan where one step rebuilds indexes.
I get the following entries in the text log file that the step creates:
Starting maintenance plan 'CDW Backup' on 2/13/2006 1:00:02 AM
[1] Database cdw: Index Rebuild (leaving 5%% free space)...
Rebuilding indexes for table 'Accounts'
Rebuilding indexes for table 'Advisor Names'
Rebuilding indexes for table 'Advisor Rep Nums'
Rebuilding indexes for table 'Asset Master'
Rebuilding indexes for table 'BP Claim Filed'
Rebuilding indexes for table 'Breakpoints'
Rebuilding indexes for table 'BSE Trans Prefixes'
Rebuilding indexes for table 'BSE Trans Prefixes Rev'
Rebuilding indexes for table 'BSE Trans Prefixes Std'
Rebuilding indexes for table 'Core_MM_NFS'
Rebuilding indexes for table 'Core_MM_Pershing'
Rebuilding indexes for table 'Dates'
Rebuilding indexes for table 'Dates_MktOpen'
Rebuilding indexes for table 'DAZL Delete'
Rebuilding indexes for table 'Direct Business Transactions'
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC
SQL Server Driver][SQL Server]DBCC failed because the following SET
options have incorrect settings: 'QUOTED_IDENTIFIER'.
Well it would be nice if it didn't say "INCORRECT SETTING" but instead
would say that the setting should be ON, or should be OFF. (Quoted
Identifiers is ON in my database, according to Select
databasepropertyex.)
After reading all about quoted identifiers, I didn't think they were
stored by table -- I thought it was a database-wide property setting.
According to BOL, "When a table is created, the QUOTED IDENTIFIER option
is always stored as ON in the table's meta data even if the option is
set to OFF when the table is created."
SO, why would so many tables successfully get reindexed and then one of
them (was it 'Direct Business Transactions' or the next one in line?)
fail?
This doesn't make sense. What direction does Set Quoted Identifiers
need to be in for the reindex step of the DB maintenance plan to
succeed? I wish the actual DBCC command was displayed somewhere in this
log also, that would make debugging easier.
Thanks for any information.
David WalkerMore than likely you have a computed column or an indexed view. Both require
certain settings that the MP can't handle. Although I believe this was fixed
in SP4. Otherwise you need to do the reindex in your own custom job with
the proper settings set.
--
Andrew J. Kelly SQL MVP
"DWalker" <none@.none.com> wrote in message
news:uWrSsgLMGHA.1532@.TK2MSFTNGP12.phx.gbl...
> SQL 2000: I have a DB maintenance plan where one step rebuilds indexes.
> I get the following entries in the text log file that the step creates:
> Starting maintenance plan 'CDW Backup' on 2/13/2006 1:00:02 AM
> [1] Database cdw: Index Rebuild (leaving 5%% free space)...
> Rebuilding indexes for table 'Accounts'
> Rebuilding indexes for table 'Advisor Names'
> Rebuilding indexes for table 'Advisor Rep Nums'
> Rebuilding indexes for table 'Asset Master'
> Rebuilding indexes for table 'BP Claim Filed'
> Rebuilding indexes for table 'Breakpoints'
> Rebuilding indexes for table 'BSE Trans Prefixes'
> Rebuilding indexes for table 'BSE Trans Prefixes Rev'
> Rebuilding indexes for table 'BSE Trans Prefixes Std'
> Rebuilding indexes for table 'Core_MM_NFS'
> Rebuilding indexes for table 'Core_MM_Pershing'
> Rebuilding indexes for table 'Dates'
> Rebuilding indexes for table 'Dates_MktOpen'
> Rebuilding indexes for table 'DAZL Delete'
> Rebuilding indexes for table 'Direct Business Transactions'
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC
> SQL Server Driver][SQL Server]DBCC failed because the following SET
> options have incorrect settings: 'QUOTED_IDENTIFIER'.
> Well it would be nice if it didn't say "INCORRECT SETTING" but instead
> would say that the setting should be ON, or should be OFF. (Quoted
> Identifiers is ON in my database, according to Select
> databasepropertyex.)
> After reading all about quoted identifiers, I didn't think they were
> stored by table -- I thought it was a database-wide property setting.
> According to BOL, "When a table is created, the QUOTED IDENTIFIER option
> is always stored as ON in the table's meta data even if the option is
> set to OFF when the table is created."
> SO, why would so many tables successfully get reindexed and then one of
> them (was it 'Direct Business Transactions' or the next one in line?)
> fail?
> This doesn't make sense. What direction does Set Quoted Identifiers
> need to be in for the reindex step of the DB maintenance plan to
> succeed? I wish the actual DBCC command was displayed somewhere in this
> log also, that would make debugging easier.
> Thanks for any information.
> David Walker|||Hi David
Check out http://support.microsoft.com/default.aspx?scid=kb;en-us;902388,
the particular table probably failed because it is the first that has the
index on a computed column.
If not you may want to look at SQL profiler to see exactly what statements
are being sent to the database by the maintenance plan.
John
"DWalker" wrote:
> SQL 2000: I have a DB maintenance plan where one step rebuilds indexes.
> I get the following entries in the text log file that the step creates:
> Starting maintenance plan 'CDW Backup' on 2/13/2006 1:00:02 AM
> [1] Database cdw: Index Rebuild (leaving 5%% free space)...
> Rebuilding indexes for table 'Accounts'
> Rebuilding indexes for table 'Advisor Names'
> Rebuilding indexes for table 'Advisor Rep Nums'
> Rebuilding indexes for table 'Asset Master'
> Rebuilding indexes for table 'BP Claim Filed'
> Rebuilding indexes for table 'Breakpoints'
> Rebuilding indexes for table 'BSE Trans Prefixes'
> Rebuilding indexes for table 'BSE Trans Prefixes Rev'
> Rebuilding indexes for table 'BSE Trans Prefixes Std'
> Rebuilding indexes for table 'Core_MM_NFS'
> Rebuilding indexes for table 'Core_MM_Pershing'
> Rebuilding indexes for table 'Dates'
> Rebuilding indexes for table 'Dates_MktOpen'
> Rebuilding indexes for table 'DAZL Delete'
> Rebuilding indexes for table 'Direct Business Transactions'
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC
> SQL Server Driver][SQL Server]DBCC failed because the following SET
> options have incorrect settings: 'QUOTED_IDENTIFIER'.
> Well it would be nice if it didn't say "INCORRECT SETTING" but instead
> would say that the setting should be ON, or should be OFF. (Quoted
> Identifiers is ON in my database, according to Select
> databasepropertyex.)
> After reading all about quoted identifiers, I didn't think they were
> stored by table -- I thought it was a database-wide property setting.
> According to BOL, "When a table is created, the QUOTED IDENTIFIER option
> is always stored as ON in the table's meta data even if the option is
> set to OFF when the table is created."
> SO, why would so many tables successfully get reindexed and then one of
> them (was it 'Direct Business Transactions' or the next one in line?)
> fail?
> This doesn't make sense. What direction does Set Quoted Identifiers
> need to be in for the reindex step of the DB maintenance plan to
> succeed? I wish the actual DBCC command was displayed somewhere in this
> log also, that would make debugging easier.
> Thanks for any information.
> David Walker
>|||Thanks to you both. I do have SP4. I'll double-check that table for a
computed column; I created it a long time ago.
David
=?Utf-8?B?Sm9obiBCZWxs?= <jbellnewsposts@.hotmail.com> wrote in
news:D8618655-6673-4DBD-BB77-7E0EE21F75C1@.microsoft.com:
> Hi David
> Check out
> http://support.microsoft.com/default.aspx?scid=kb;en-us;902388, the
> particular table probably failed because it is the first that has the
> index on a computed column.
> If not you may want to look at SQL profiler to see exactly what
> statements are being sent to the database by the maintenance plan.
> John
>|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in
news:#UsAzvMMGHA.2628@.TK2MSFTNGP15.phx.gbl:
> More than likely you have a computed column or an indexed view. Both
> require certain settings that the MP can't handle. Although I believe
> this was fixed in SP4. Otherwise you need to do the reindex in your
> own custom job with the proper settings set.
>
Ah, so the maintenance plan generator can't maintain any table that has
computed coumns? Interesting.
It also was complaining about other settings that required the database to
be in single-user mode. WTF? It tries to run statements that require
single-user mode? Maybe it ought to try to put the database in single-user
mode, or maybe that's not a good idea. The check-marks in the maintenance
plan generator ought to tell you that single-user mode is required.
My conclusion from this is that the maintenace plan generator is not
suitable for use on databases in the real world. Is that the right
conclusion?
Thanks.
David|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in
news:#UsAzvMMGHA.2628@.TK2MSFTNGP15.phx.gbl:
> More than likely you have a computed column or an indexed view. Both
> require certain settings that the MP can't handle. Although I believe
> this was fixed in SP4. Otherwise you need to do the reindex in your
> own custom job with the proper settings set.
>
So yes, the table did have one computed column. The table can't be
maintained with a maintenance plan?
Or the indexes can't ever be rebuilt?
David|||Hi David
You may want to look at the code in:
http://support.microsoft.com/default.aspx?scid=kb;en-us;301292
John
"DWalker" wrote:
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in
> news:#UsAzvMMGHA.2628@.TK2MSFTNGP15.phx.gbl:
> > More than likely you have a computed column or an indexed view. Both
> > require certain settings that the MP can't handle. Although I believe
> > this was fixed in SP4. Otherwise you need to do the reindex in your
> > own custom job with the proper settings set.
> >
> So yes, the table did have one computed column. The table can't be
> maintained with a maintenance plan?
> Or the indexes can't ever be rebuilt?
> David
>|||=?Utf-8?B?Sm9obiBCZWxs?= <jbellnewsposts@.hotmail.com> wrote in
news:D8618655-6673-4DBD-BB77-7E0EE21F75C1@.microsoft.com:
> Hi David
> Check out
> http://support.microsoft.com/default.aspx?scid=kb;en-us;902388, the
> particular table probably failed because it is the first that has the
> index on a computed column.
> If not you may want to look at SQL profiler to see exactly what
> statements are being sent to the database by the maintenance plan.
> John
>
That article is sure confusing; it says "These statements require that
the QUOTED_IDENTIFIER SET option is set to ON."
It *IS* set to On in my database. Then it goes on to say, I think, that
the command is built wrong by the maintenance wizard. Why it couldn't
have been fixed in SP4 without requiring me to add the -
SupportcomputedColumn parameter is not explained...
I added the -SupportComputedColumn option to the plan step, and it will
probably fix it, but I'm still a bit confused by the wording in the
article.
Tthanks for the pointer though!
David Walker|||> That article is sure confusing; it says "These statements require that
> the QUOTED_IDENTIFIER SET option is set to ON."
> It *IS* set to On in my database.
The database setting is essentially useless since it will be overridden by session setting. And most
API's will set this setting, whether the developer is aware of it or not.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"DWalker" <none@.none.com> wrote in message news:%23ZTTnSYMGHA.1532@.TK2MSFTNGP12.phx.gbl...
> =?Utf-8?B?Sm9obiBCZWxs?= <jbellnewsposts@.hotmail.com> wrote in
> news:D8618655-6673-4DBD-BB77-7E0EE21F75C1@.microsoft.com:
>> Hi David
>> Check out
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;902388, the
>> particular table probably failed because it is the first that has the
>> index on a computed column.
>> If not you may want to look at SQL profiler to see exactly what
>> statements are being sent to the database by the maintenance plan.
>> John
> That article is sure confusing; it says "These statements require that
> the QUOTED_IDENTIFIER SET option is set to ON."
> It *IS* set to On in my database. Then it goes on to say, I think, that
> the command is built wrong by the maintenance wizard. Why it couldn't
> have been fixed in SP4 without requiring me to add the -
> SupportcomputedColumn parameter is not explained...
> I added the -SupportComputedColumn option to the plan step, and it will
> probably fix it, but I'm still a bit confused by the wording in the
> article.
> Tthanks for the pointer though!
> David Walker|||Hi David
You may want to use the send feedback option at the bottom of the article.
John
"DWalker" wrote:
> =?Utf-8?B?Sm9obiBCZWxs?= <jbellnewsposts@.hotmail.com> wrote in
> news:D8618655-6673-4DBD-BB77-7E0EE21F75C1@.microsoft.com:
> > Hi David
> >
> > Check out
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;902388, the
> > particular table probably failed because it is the first that has the
> > index on a computed column.
> >
> > If not you may want to look at SQL profiler to see exactly what
> > statements are being sent to the database by the maintenance plan.
> >
> > John
> >
> That article is sure confusing; it says "These statements require that
> the QUOTED_IDENTIFIER SET option is set to ON."
> It *IS* set to On in my database. Then it goes on to say, I think, that
> the command is built wrong by the maintenance wizard. Why it couldn't
> have been fixed in SP4 without requiring me to add the -
> SupportcomputedColumn parameter is not explained...
> I added the -SupportComputedColumn option to the plan step, and it will
> probably fix it, but I'm still a bit confused by the wording in the
> article.
> Tthanks for the pointer though!
> David Walker
>|||=?Utf-8?B?Sm9obiBCZWxs?= <jbellnewsposts@.hotmail.com> wrote in
news:D4BDB694-0A2E-408D-AA7A-562DBEC42515@.microsoft.com:
> Hi David
> You may want to use the send feedback option at the bottom of the
> article.
> John
>
I'll do that.|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in news:OsfT#fYMGHA.3460@.TK2MSFTNGP15.phx.gbl:
>> That article is sure confusing; it says "These statements require
>> that the QUOTED_IDENTIFIER SET option is set to ON."
>> It *IS* set to On in my database.
> The database setting is essentially useless since it will be
> overridden by session setting. And most API's will set this setting,
> whether the developer is aware of it or not.
>
Thanks, Tibor.
Davidsql

Quoted identifiers

SQL 2000: I have a DB maintenance plan where one step rebuilds indexes.
I get the following entries in the text log file that the step creates:
Starting maintenance plan 'CDW Backup' on 2/13/2006 1:00:02 AM
[1] Database cdw: Index Rebuild (leaving 5%% free space)...
Rebuilding indexes for table 'Accounts'
Rebuilding indexes for table 'Advisor Names'
Rebuilding indexes for table 'Advisor Rep Nums'
Rebuilding indexes for table 'Asset Master'
Rebuilding indexes for table 'BP Claim Filed'
Rebuilding indexes for table 'Breakpoints'
Rebuilding indexes for table 'BSE Trans Prefixes'
Rebuilding indexes for table 'BSE Trans Prefixes Rev'
Rebuilding indexes for table 'BSE Trans Prefixes Std'
Rebuilding indexes for table 'Core_MM_NFS'
Rebuilding indexes for table 'Core_MM_Pershing'
Rebuilding indexes for table 'Dates'
Rebuilding indexes for table 'Dates_MktOpen'
Rebuilding indexes for table 'DAZL Delete'
Rebuilding indexes for table 'Direct Business Transactions'
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft]&#
91;ODBC
SQL Server Driver][SQL Server]DBCC failed because the following SET
options have incorrect settings: 'QUOTED_IDENTIFIER'.
Well it would be nice if it didn't say "INCORRECT SETTING" but instead
would say that the setting should be ON, or should be OFF. (Quoted
Identifiers is ON in my database, according to Select
databasepropertyex.)
After reading all about quoted identifiers, I didn't think they were
stored by table -- I thought it was a database-wide property setting.
According to BOL, "When a table is created, the QUOTED IDENTIFIER option
is always stored as ON in the table's meta data even if the option is
set to OFF when the table is created."
SO, why would so many tables successfully get reindexed and then one of
them (was it 'Direct Business Transactions' or the next one in line?)
fail?
This doesn't make sense. What direction does Set Quoted Identifiers
need to be in for the reindex step of the DB maintenance plan to
succeed? I wish the actual DBCC command was displayed somewhere in this
log also, that would make debugging easier.
Thanks for any information.
David WalkerMore than likely you have a computed column or an indexed view. Both require
certain settings that the MP can't handle. Although I believe this was fixed
in SP4. Otherwise you need to do the reindex in your own custom job with
the proper settings set.
Andrew J. Kelly SQL MVP
"DWalker" <none@.none.com> wrote in message
news:uWrSsgLMGHA.1532@.TK2MSFTNGP12.phx.gbl...
> SQL 2000: I have a DB maintenance plan where one step rebuilds indexes.
> I get the following entries in the text log file that the step creates:
> Starting maintenance plan 'CDW Backup' on 2/13/2006 1:00:02 AM
> [1] Database cdw: Index Rebuild (leaving 5%% free space)...
> Rebuilding indexes for table 'Accounts'
> Rebuilding indexes for table 'Advisor Names'
> Rebuilding indexes for table 'Advisor Rep Nums'
> Rebuilding indexes for table 'Asset Master'
> Rebuilding indexes for table 'BP Claim Filed'
> Rebuilding indexes for table 'Breakpoints'
> Rebuilding indexes for table 'BSE Trans Prefixes'
> Rebuilding indexes for table 'BSE Trans Prefixes Rev'
> Rebuilding indexes for table 'BSE Trans Prefixes Std'
> Rebuilding indexes for table 'Core_MM_NFS'
> Rebuilding indexes for table 'Core_MM_Pershing'
> Rebuilding indexes for table 'Dates'
> Rebuilding indexes for table 'Dates_MktOpen'
> Rebuilding indexes for table 'DAZL Delete'
> Rebuilding indexes for table 'Direct Business Transactions'
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft]
[ODBC
> SQL Server Driver][SQL Server]DBCC failed because the following SET
> options have incorrect settings: 'QUOTED_IDENTIFIER'.
> Well it would be nice if it didn't say "INCORRECT SETTING" but instead
> would say that the setting should be ON, or should be OFF. (Quoted
> Identifiers is ON in my database, according to Select
> databasepropertyex.)
> After reading all about quoted identifiers, I didn't think they were
> stored by table -- I thought it was a database-wide property setting.
> According to BOL, "When a table is created, the QUOTED IDENTIFIER option
> is always stored as ON in the table's meta data even if the option is
> set to OFF when the table is created."
> SO, why would so many tables successfully get reindexed and then one of
> them (was it 'Direct Business Transactions' or the next one in line?)
> fail?
> This doesn't make sense. What direction does Set Quoted Identifiers
> need to be in for the reindex step of the DB maintenance plan to
> succeed? I wish the actual DBCC command was displayed somewhere in this
> log also, that would make debugging easier.
> Thanks for any information.
> David Walker|||Hi David
Check out http://support.microsoft.com/defaul...b;en-us;902388,
the particular table probably failed because it is the first that has the
index on a computed column.
If not you may want to look at SQL profiler to see exactly what statements
are being sent to the database by the maintenance plan.
John
"DWalker" wrote:

> SQL 2000: I have a DB maintenance plan where one step rebuilds indexes.
> I get the following entries in the text log file that the step creates:
> Starting maintenance plan 'CDW Backup' on 2/13/2006 1:00:02 AM
> [1] Database cdw: Index Rebuild (leaving 5%% free space)...
> Rebuilding indexes for table 'Accounts'
> Rebuilding indexes for table 'Advisor Names'
> Rebuilding indexes for table 'Advisor Rep Nums'
> Rebuilding indexes for table 'Asset Master'
> Rebuilding indexes for table 'BP Claim Filed'
> Rebuilding indexes for table 'Breakpoints'
> Rebuilding indexes for table 'BSE Trans Prefixes'
> Rebuilding indexes for table 'BSE Trans Prefixes Rev'
> Rebuilding indexes for table 'BSE Trans Prefixes Std'
> Rebuilding indexes for table 'Core_MM_NFS'
> Rebuilding indexes for table 'Core_MM_Pershing'
> Rebuilding indexes for table 'Dates'
> Rebuilding indexes for table 'Dates_MktOpen'
> Rebuilding indexes for table 'DAZL Delete'
> Rebuilding indexes for table 'Direct Business Transactions'
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft]
[ODBC
> SQL Server Driver][SQL Server]DBCC failed because the following SET
> options have incorrect settings: 'QUOTED_IDENTIFIER'.
> Well it would be nice if it didn't say "INCORRECT SETTING" but instead
> would say that the setting should be ON, or should be OFF. (Quoted
> Identifiers is ON in my database, according to Select
> databasepropertyex.)
> After reading all about quoted identifiers, I didn't think they were
> stored by table -- I thought it was a database-wide property setting.
> According to BOL, "When a table is created, the QUOTED IDENTIFIER option
> is always stored as ON in the table's meta data even if the option is
> set to OFF when the table is created."
> SO, why would so many tables successfully get reindexed and then one of
> them (was it 'Direct Business Transactions' or the next one in line?)
> fail?
> This doesn't make sense. What direction does Set Quoted Identifiers
> need to be in for the reindex step of the DB maintenance plan to
> succeed? I wish the actual DBCC command was displayed somewhere in this
> log also, that would make debugging easier.
> Thanks for any information.
> David Walker
>|||Thanks to you both. I do have SP4. I'll double-check that table for a
computed column; I created it a long time ago.
David
examnotes <jbellnewsposts@.hotmail.com> wrote in
news:D8618655-6673-4DBD-BB77-7E0EE21F75C1@.microsoft.com:

> Hi David
> Check out
> http://support.microsoft.com/defaul...b;en-us;902388, the
> particular table probably failed because it is the first that has the
> index on a computed column.
> If not you may want to look at SQL profiler to see exactly what
> statements are being sent to the database by the maintenance plan.
> John
>|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in
news:#UsAzvMMGHA.2628@.TK2MSFTNGP15.phx.gbl:

> More than likely you have a computed column or an indexed view. Both
> require certain settings that the MP can't handle. Although I believe
> this was fixed in SP4. Otherwise you need to do the reindex in your
> own custom job with the proper settings set.
>
Ah, so the maintenance plan generator can't maintain any table that has
computed coumns? Interesting.
It also was complaining about other settings that required the database to
be in single-user mode. WTF? It tries to run statements that require
single-user mode? Maybe it ought to try to put the database in single-user
mode, or maybe that's not a good idea. The check-marks in the maintenance
plan generator ought to tell you that single-user mode is required.
My conclusion from this is that the maintenace plan generator is not
suitable for use on databases in the real world. Is that the right
conclusion?
Thanks.
David|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in
news:#UsAzvMMGHA.2628@.TK2MSFTNGP15.phx.gbl:

> More than likely you have a computed column or an indexed view. Both
> require certain settings that the MP can't handle. Although I believe
> this was fixed in SP4. Otherwise you need to do the reindex in your
> own custom job with the proper settings set.
>
So yes, the table did have one computed column. The table can't be
maintained with a maintenance plan?
Or the indexes can't ever be rebuilt?
David|||Hi David
You may want to look at the code in:
http://support.microsoft.com/defaul...kb;en-us;301292
John
"DWalker" wrote:

> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in
> news:#UsAzvMMGHA.2628@.TK2MSFTNGP15.phx.gbl:
>
> So yes, the table did have one computed column. The table can't be
> maintained with a maintenance plan?
> Or the indexes can't ever be rebuilt?
> David
>|||examnotes <jbellnewsposts@.hotmail.com> wrote in
news:D8618655-6673-4DBD-BB77-7E0EE21F75C1@.microsoft.com:

> Hi David
> Check out
> http://support.microsoft.com/defaul...b;en-us;902388, the
> particular table probably failed because it is the first that has the
> index on a computed column.
> If not you may want to look at SQL profiler to see exactly what
> statements are being sent to the database by the maintenance plan.
> John
>
That article is sure confusing; it says "These statements require that
the QUOTED_IDENTIFIER SET option is set to ON."
It *IS* set to On in my database. Then it goes on to say, I think, that
the command is built wrong by the maintenance wizard. Why it couldn't
have been fixed in SP4 without requiring me to add the -
SupportcomputedColumn parameter is not explained...
I added the -SupportComputedColumn option to the plan step, and it will
probably fix it, but I'm still a bit confused by the wording in the
article.
Tthanks for the pointer though!
David Walker|||> That article is sure confusing; it says "These statements require that
> the QUOTED_IDENTIFIER SET option is set to ON."
> It *IS* set to On in my database.
The database setting is essentially useless since it will be overridden by s
ession setting. And most
API's will set this setting, whether the developer is aware of it or not.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"DWalker" <none@.none.com> wrote in message news:%23ZTTnSYMGHA.1532@.TK2MSFTNGP12.phx.gbl...[v
bcol=seagreen]
> examnotes <jbellnewsposts@.hotmail.com> wrote in
> news:D8618655-6673-4DBD-BB77-7E0EE21F75C1@.microsoft.com:
>
> That article is sure confusing; it says "These statements require that
> the QUOTED_IDENTIFIER SET option is set to ON."
> It *IS* set to On in my database. Then it goes on to say, I think, that
> the command is built wrong by the maintenance wizard. Why it couldn't
> have been fixed in SP4 without requiring me to add the -
> SupportcomputedColumn parameter is not explained...
> I added the -SupportComputedColumn option to the plan step, and it will
> probably fix it, but I'm still a bit confused by the wording in the
> article.
> Tthanks for the pointer though!
> David Walker[/vbcol]|||Hi David
You may want to use the send feedback option at the bottom of the article.
John
"DWalker" wrote:

> examnotes <jbellnewsposts@.hotmail.com> wrote in
> news:D8618655-6673-4DBD-BB77-7E0EE21F75C1@.microsoft.com:
>
> That article is sure confusing; it says "These statements require that
> the QUOTED_IDENTIFIER SET option is set to ON."
> It *IS* set to On in my database. Then it goes on to say, I think, that
> the command is built wrong by the maintenance wizard. Why it couldn't
> have been fixed in SP4 without requiring me to add the -
> SupportcomputedColumn parameter is not explained...
> I added the -SupportComputedColumn option to the plan step, and it will
> probably fix it, but I'm still a bit confused by the wording in the
> article.
> Tthanks for the pointer though!
> David Walker
>

Quoted identifiers

SQL 2000: I have a DB maintenance plan where one step rebuilds indexes.
I get the following entries in the text log file that the step creates:
Starting maintenance plan 'CDW Backup' on 2/13/2006 1:00:02 AM
[1] Database cdw: Index Rebuild (leaving 5%% free space)...
Rebuilding indexes for table 'Accounts'
Rebuilding indexes for table 'Advisor Names'
Rebuilding indexes for table 'Advisor Rep Nums'
Rebuilding indexes for table 'Asset Master'
Rebuilding indexes for table 'BP Claim Filed'
Rebuilding indexes for table 'Breakpoints'
Rebuilding indexes for table 'BSE Trans Prefixes'
Rebuilding indexes for table 'BSE Trans Prefixes Rev'
Rebuilding indexes for table 'BSE Trans Prefixes Std'
Rebuilding indexes for table 'Core_MM_NFS'
Rebuilding indexes for table 'Core_MM_Pershing'
Rebuilding indexes for table 'Dates'
Rebuilding indexes for table 'Dates_MktOpen'
Rebuilding indexes for table 'DAZL Delete'
Rebuilding indexes for table 'Direct Business Transactions'
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC
SQL Server Driver][SQL Server]DBCC failed because the following SET
options have incorrect settings: 'QUOTED_IDENTIFIER'.
Well it would be nice if it didn't say "INCORRECT SETTING" but instead
would say that the setting should be ON, or should be OFF. (Quoted
Identifiers is ON in my database, according to Select
databasepropertyex.)
After reading all about quoted identifiers, I didn't think they were
stored by table -- I thought it was a database-wide property setting.
According to BOL, "When a table is created, the QUOTED IDENTIFIER option
is always stored as ON in the table's meta data even if the option is
set to OFF when the table is created."
SO, why would so many tables successfully get reindexed and then one of
them (was it 'Direct Business Transactions' or the next one in line?)
fail?
This doesn't make sense. What direction does Set Quoted Identifiers
need to be in for the reindex step of the DB maintenance plan to
succeed? I wish the actual DBCC command was displayed somewhere in this
log also, that would make debugging easier.
Thanks for any information.
David Walker
More than likely you have a computed column or an indexed view. Both require
certain settings that the MP can't handle. Although I believe this was fixed
in SP4. Otherwise you need to do the reindex in your own custom job with
the proper settings set.
Andrew J. Kelly SQL MVP
"DWalker" <none@.none.com> wrote in message
news:uWrSsgLMGHA.1532@.TK2MSFTNGP12.phx.gbl...
> SQL 2000: I have a DB maintenance plan where one step rebuilds indexes.
> I get the following entries in the text log file that the step creates:
> Starting maintenance plan 'CDW Backup' on 2/13/2006 1:00:02 AM
> [1] Database cdw: Index Rebuild (leaving 5%% free space)...
> Rebuilding indexes for table 'Accounts'
> Rebuilding indexes for table 'Advisor Names'
> Rebuilding indexes for table 'Advisor Rep Nums'
> Rebuilding indexes for table 'Asset Master'
> Rebuilding indexes for table 'BP Claim Filed'
> Rebuilding indexes for table 'Breakpoints'
> Rebuilding indexes for table 'BSE Trans Prefixes'
> Rebuilding indexes for table 'BSE Trans Prefixes Rev'
> Rebuilding indexes for table 'BSE Trans Prefixes Std'
> Rebuilding indexes for table 'Core_MM_NFS'
> Rebuilding indexes for table 'Core_MM_Pershing'
> Rebuilding indexes for table 'Dates'
> Rebuilding indexes for table 'Dates_MktOpen'
> Rebuilding indexes for table 'DAZL Delete'
> Rebuilding indexes for table 'Direct Business Transactions'
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC
> SQL Server Driver][SQL Server]DBCC failed because the following SET
> options have incorrect settings: 'QUOTED_IDENTIFIER'.
> Well it would be nice if it didn't say "INCORRECT SETTING" but instead
> would say that the setting should be ON, or should be OFF. (Quoted
> Identifiers is ON in my database, according to Select
> databasepropertyex.)
> After reading all about quoted identifiers, I didn't think they were
> stored by table -- I thought it was a database-wide property setting.
> According to BOL, "When a table is created, the QUOTED IDENTIFIER option
> is always stored as ON in the table's meta data even if the option is
> set to OFF when the table is created."
> SO, why would so many tables successfully get reindexed and then one of
> them (was it 'Direct Business Transactions' or the next one in line?)
> fail?
> This doesn't make sense. What direction does Set Quoted Identifiers
> need to be in for the reindex step of the DB maintenance plan to
> succeed? I wish the actual DBCC command was displayed somewhere in this
> log also, that would make debugging easier.
> Thanks for any information.
> David Walker
|||Hi David
Check out http://support.microsoft.com/default...;en-us;902388,
the particular table probably failed because it is the first that has the
index on a computed column.
If not you may want to look at SQL profiler to see exactly what statements
are being sent to the database by the maintenance plan.
John
"DWalker" wrote:

> SQL 2000: I have a DB maintenance plan where one step rebuilds indexes.
> I get the following entries in the text log file that the step creates:
> Starting maintenance plan 'CDW Backup' on 2/13/2006 1:00:02 AM
> [1] Database cdw: Index Rebuild (leaving 5%% free space)...
> Rebuilding indexes for table 'Accounts'
> Rebuilding indexes for table 'Advisor Names'
> Rebuilding indexes for table 'Advisor Rep Nums'
> Rebuilding indexes for table 'Asset Master'
> Rebuilding indexes for table 'BP Claim Filed'
> Rebuilding indexes for table 'Breakpoints'
> Rebuilding indexes for table 'BSE Trans Prefixes'
> Rebuilding indexes for table 'BSE Trans Prefixes Rev'
> Rebuilding indexes for table 'BSE Trans Prefixes Std'
> Rebuilding indexes for table 'Core_MM_NFS'
> Rebuilding indexes for table 'Core_MM_Pershing'
> Rebuilding indexes for table 'Dates'
> Rebuilding indexes for table 'Dates_MktOpen'
> Rebuilding indexes for table 'DAZL Delete'
> Rebuilding indexes for table 'Direct Business Transactions'
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC
> SQL Server Driver][SQL Server]DBCC failed because the following SET
> options have incorrect settings: 'QUOTED_IDENTIFIER'.
> Well it would be nice if it didn't say "INCORRECT SETTING" but instead
> would say that the setting should be ON, or should be OFF. (Quoted
> Identifiers is ON in my database, according to Select
> databasepropertyex.)
> After reading all about quoted identifiers, I didn't think they were
> stored by table -- I thought it was a database-wide property setting.
> According to BOL, "When a table is created, the QUOTED IDENTIFIER option
> is always stored as ON in the table's meta data even if the option is
> set to OFF when the table is created."
> SO, why would so many tables successfully get reindexed and then one of
> them (was it 'Direct Business Transactions' or the next one in line?)
> fail?
> This doesn't make sense. What direction does Set Quoted Identifiers
> need to be in for the reindex step of the DB maintenance plan to
> succeed? I wish the actual DBCC command was displayed somewhere in this
> log also, that would make debugging easier.
> Thanks for any information.
> David Walker
>
|||Thanks to you both. I do have SP4. I'll double-check that table for a
computed column; I created it a long time ago.
David
=?Utf-8?B?Sm9obiBCZWxs?= <jbellnewsposts@.hotmail.com> wrote in
news:D8618655-6673-4DBD-BB77-7E0EE21F75C1@.microsoft.com:

> Hi David
> Check out
> http://support.microsoft.com/default...;en-us;902388, the
> particular table probably failed because it is the first that has the
> index on a computed column.
> If not you may want to look at SQL profiler to see exactly what
> statements are being sent to the database by the maintenance plan.
> John
>
|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in
news:#UsAzvMMGHA.2628@.TK2MSFTNGP15.phx.gbl:

> More than likely you have a computed column or an indexed view. Both
> require certain settings that the MP can't handle. Although I believe
> this was fixed in SP4. Otherwise you need to do the reindex in your
> own custom job with the proper settings set.
>
Ah, so the maintenance plan generator can't maintain any table that has
computed coumns? Interesting.
It also was complaining about other settings that required the database to
be in single-user mode. WTF? It tries to run statements that require
single-user mode? Maybe it ought to try to put the database in single-user
mode, or maybe that's not a good idea. The check-marks in the maintenance
plan generator ought to tell you that single-user mode is required.
My conclusion from this is that the maintenace plan generator is not
suitable for use on databases in the real world. Is that the right
conclusion?
Thanks.
David
|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in
news:#UsAzvMMGHA.2628@.TK2MSFTNGP15.phx.gbl:

> More than likely you have a computed column or an indexed view. Both
> require certain settings that the MP can't handle. Although I believe
> this was fixed in SP4. Otherwise you need to do the reindex in your
> own custom job with the proper settings set.
>
So yes, the table did have one computed column. The table can't be
maintained with a maintenance plan?
Or the indexes can't ever be rebuilt?
David
|||Hi David
You may want to look at the code in:
http://support.microsoft.com/default...b;en-us;301292
John
"DWalker" wrote:

> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in
> news:#UsAzvMMGHA.2628@.TK2MSFTNGP15.phx.gbl:
>
> So yes, the table did have one computed column. The table can't be
> maintained with a maintenance plan?
> Or the indexes can't ever be rebuilt?
> David
>
|||=?Utf-8?B?Sm9obiBCZWxs?= <jbellnewsposts@.hotmail.com> wrote in
news:D8618655-6673-4DBD-BB77-7E0EE21F75C1@.microsoft.com:

> Hi David
> Check out
> http://support.microsoft.com/default...;en-us;902388, the
> particular table probably failed because it is the first that has the
> index on a computed column.
> If not you may want to look at SQL profiler to see exactly what
> statements are being sent to the database by the maintenance plan.
> John
>
That article is sure confusing; it says "These statements require that
the QUOTED_IDENTIFIER SET option is set to ON."
It *IS* set to On in my database. Then it goes on to say, I think, that
the command is built wrong by the maintenance wizard. Why it couldn't
have been fixed in SP4 without requiring me to add the -
SupportcomputedColumn parameter is not explained...
I added the -SupportComputedColumn option to the plan step, and it will
probably fix it, but I'm still a bit confused by the wording in the
article.
Tthanks for the pointer though!
David Walker
|||> That article is sure confusing; it says "These statements require that
> the QUOTED_IDENTIFIER SET option is set to ON."
> It *IS* set to On in my database.
The database setting is essentially useless since it will be overridden by session setting. And most
API's will set this setting, whether the developer is aware of it or not.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"DWalker" <none@.none.com> wrote in message news:%23ZTTnSYMGHA.1532@.TK2MSFTNGP12.phx.gbl...
> =?Utf-8?B?Sm9obiBCZWxs?= <jbellnewsposts@.hotmail.com> wrote in
> news:D8618655-6673-4DBD-BB77-7E0EE21F75C1@.microsoft.com:
>
> That article is sure confusing; it says "These statements require that
> the QUOTED_IDENTIFIER SET option is set to ON."
> It *IS* set to On in my database. Then it goes on to say, I think, that
> the command is built wrong by the maintenance wizard. Why it couldn't
> have been fixed in SP4 without requiring me to add the -
> SupportcomputedColumn parameter is not explained...
> I added the -SupportComputedColumn option to the plan step, and it will
> probably fix it, but I'm still a bit confused by the wording in the
> article.
> Tthanks for the pointer though!
> David Walker
|||Hi David
You may want to use the send feedback option at the bottom of the article.
John
"DWalker" wrote:

> =?Utf-8?B?Sm9obiBCZWxs?= <jbellnewsposts@.hotmail.com> wrote in
> news:D8618655-6673-4DBD-BB77-7E0EE21F75C1@.microsoft.com:
>
> That article is sure confusing; it says "These statements require that
> the QUOTED_IDENTIFIER SET option is set to ON."
> It *IS* set to On in my database. Then it goes on to say, I think, that
> the command is built wrong by the maintenance wizard. Why it couldn't
> have been fixed in SP4 without requiring me to add the -
> SupportcomputedColumn parameter is not explained...
> I added the -SupportComputedColumn option to the plan step, and it will
> probably fix it, but I'm still a bit confused by the wording in the
> article.
> Tthanks for the pointer though!
> David Walker
>

Tuesday, March 20, 2012

Quoted Field and Escape Character Issue

I am attempting to import a flat file and have come accross and issue that I do not know how to fix in SSIS. The issue is that some of the text fields use quoted identifiers. This is not an issue in itself. The problem is they also use quotes as escape character if quotes are on the field.

So I see instances of "" because inside the quoted field is a quote. How do i specify an escape character?

Unfortuanately, the current Flat file parser does not know how to parse embedded qualifiers.

As a workaround, you should probably keep the qualifiers in the flat file source and process them downstream using either the script component or Derived Column.

Thanks.

Quick Transaction Question.

If in .NET I open a connection to my database, then use some sql text to start a transaction, then reuse that same open connection to call several stored procedures (using SqlCommand with CommandType.StoredProcedure), before ending the transaction. Will that run as a single transaction that can be rolled back? or are the stored procedure calls unable to roll-back after each one completes?

the stored procedures are themselves atomic and would need their own rollbacks. your use of transaction would work if all your statements were sql strings executing one after the other. -- jp

|||

Did a bit of research, and it appears there is a BeginTransaction method that can be used as part of the SqlConnection object. Which allowed me to start a transaction on a connection, then call serveral stored procedures using a SqlCommand object set to CommandType.StoredProcedure, and it all works as a single transaction, rolling everything back if any of then calls fail (internally or enternally). Here is a quick code snippet:

1protected void btnSave_Click(object sender, EventArgs e) {2//Create connection string for SQL query3 String strConnect;4 strConnect = WebConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString;56//Generate call to stored procedure7 SqlConnection con =new SqlConnection(strConnect);8 SqlTransaction trans =null;910//Make Calls11string myNull =null;12try {13 con.Open();14 trans = con.BeginTransaction();15int ret1 = spCall(con, trans,"two");16int ret3 = spCall(con, trans, myNull);17int ret2 = spCall(con, trans,"three");18 trans.Commit();19 Master.Message.CssClass ="Text_Message";20 Master.Message.Text = ret1.ToString();21 Master.Message.Visible =true;22 }23catch (Exception sql) {24 Master.Message.CssClass ="Text_Error";25 Master.Message.Text = sql.Message;26 Master.Message.Visible =true;27if (null != trans) {28 trans.Rollback();29 }30 }31finally {32 con.Close();33 con.Dispose();34 }35 }3637protected int spCall(SqlConnection myConn, SqlTransaction myTrans,string myParam) {3839//Generate call to stored procedure40 SqlCommand storedProcCommand =new SqlCommand("spTest", myConn);41 storedProcCommand.CommandType = CommandType.StoredProcedure;4243//Build SQL parameter list44 storedProcCommand.Parameters.AddWithValue("@.value", myParam);4546//Return code47 SqlParameter retParam = storedProcCommand.Parameters.Add("@.ReturnValue", SqlDbType.Int);48 retParam.Direction = ParameterDirection.ReturnValue;4950//Bind to transaction51 storedProcCommand.Transaction = myTrans;5253//Run stored procedure54int retCode = 0;55 SqlDataReader Reader = storedProcCommand.ExecuteReader();56 retCode = (int)storedProcCommand.Parameters["@.ReturnValue"].Value;57 Reader.Close();5859return retCode;60 }

 1CREATE PROCEDURE [dbo].[spTest]2--Parameters3 @.valuevarchar(50) =null4AS56BEGIN78SET NOCOUNT ON;910INSERT INTO tbTest11 (12 [value]13 )14VALUES15 (16 @.value17 )1819RETURN@.@.Identity2021END
My first call inserts okay, the second call fails because nulls are not accepted by [value] in the table definition., and the third call never happens due to the exception thrown by the second call, which forced a rollback of the entire transaction, of which each stored procedure is a call of. Did a fair amount of testing and everything appears to be in order, if anyone notices anything I overlooked and or that could be problematic, please let me know.

Friday, March 9, 2012

Quick Question Importing

Two Quick Question's,

Are you better off importing data through Excel or through Text files in terms of ease of use \ Speed \ Efficency etc or does it make a differance ?

Also if I am loading data into a SQL database should I always use the "SQL Server Destination" rather than the "OLE DB Destination"

Hope Q's aren't too basic, both seem to work for me, but I just want to make sure im using the right one.

Thank you

You may find text files a little easier than excel files, because in case of excel files, you may need to do a transformation from unicode to non-unicode characters.

For your second question, SQL Server destination can be used only if you are loading the data to the local server. It can't be used for a remote server. When using a local server, SQL Server Destination, will have a performance benefit over OLEDB Destination. But if you are working with a remote server, OLEDB destination is the only one you can use.

|||

Thank you very much Ranjeeta that was exactly what I was looking for :)

Just as an afterthought in the case of Excel "you may need to do a transformation from unicode to non-unicode characters. "

Is this tricky to do ? or would it be a case of using "Data Conversion" to change the datatypes from unicode to non unicode charactors

Again thanks

|||Not tricky. Just the matter of using a "Data conversion", so just an additional step.|||I would recommend not using Excel to much... You might get into trouble because of Excel interpreting fields sometimes as text sometimes as numbers... That's not always a problem (depends on the structure of you tables) and it's nothing making Excel as a source impossible but it can take you some time to get it working...|||

Thanks for the help, I have opted to not use excel and have resorted instead to text files or csv files.

However I still seem to be running into the same type of problem. All the text being imported weither text, date's or numbers all come in as Data Type "DT_STR" Code 1252 (not sure if the code bit is relevant).

I have then inserted a Data Conversion task to try and change the datatypes but none of it seems to work, I keep getting the same type of error

"Error at Data Flow [SQL Server Destination[9]]: The Column "Copy of Column 0" can't be inserted because the conversion between types DT_R8 and DT_STR is not supported."

Error at Data Flow task [DTS Pipeline]: "Componant SQL Server Destination" (9) failed validation and returned status "VS_ISBROKEN"

I have also tried DT_R4, DT_u18, DT_Date etc all with the same errror.

Ive worked on this for the last few days now but it always seem to be the same error so Im second guessing everything now trying to find a solution.

Surely I should be using "Data Conversion" to change the data types inbetween my source and destination column. ?

Im sure its user error somehow but I cant understand whats going wrong

|||Did you set the field's properties correctly in the data source adapter? If you did so we have to sort out where the pipeline changes from having correct metadata to only having strings there... Check the output's settings from the data source and the pipeline's metadata just after the data source... If there are only strings (and you have set the field's properties) perhaps recreate the data source adapter for the text file...|||

Sorry for the Delay in getting back to you Thomas I have been trying to figure out where I have been running into errors so I deleted all and am in the process of building them back up again.

When I rebuilt the flat file source connection I used Suggest Data Types for the following Sample Data
10012 C961910 1 380.92 12/05/1997 IE HELENA 1
10013 C961711 3 3174.35 27/07/1999 FR MARYM 7
10013 C961711 3 2459.23 25/05/1999 FR MAURA 6

and these are the data types it suggested I should use

Column 0 eight-byte signed integer [DT_18]

Column 1 string [DT_STR]

Column 2 eight-byte signed integer [DT_18]

Column 3 double-precision float [DT_R8]

Column 4 string [DT_STR]

Column 5 string [DT_STR]

Column 6 eight-byte signed integer [DT_18]

Im a little cautous of "string [DT_STR] so I thought maybe the following would be more accurate ? although I didnt use them in this instance but when I do I get the same error

eight-byte unsigned integer [DT_U18]
text stream [DT_TEXT]
eight-byte unsigned integer [DT_U18]
currency [DT_CY]
date [DT_DATE]
text stream [DT_TEXT]
text stream [DT_TEXT]
eight-byte unsigned integer [DT_U18]

It then goes straight to the "SQL Destination" and create a table

CREATE TABLE [SQL Server Destination] (
[Column 0] BIGINT,
[Column 1] VARCHAR(7),
[Column 2] BIGINT,
[Column 3] DOUBLE PRECISION,
[Column 4] DATETIME,
[Column 5] VARCHAR(2),
[Column 6] VARCHAR(7),
[Column 7] BIGINT
)

Then when I run the package it gives me the following errors

"Error at data flow task SQL Server Destination 75: The Column "Column 4" can't be inserted because the conversion between types DT_DATE and DT_DBTIMESTAMP is not supported.
"Error at Data flow task [DTS PIPELINE] :Compnant "SQL Server Destination" 75 Faileds validation and returned validation status "VS_ISBROKEN"

Any idea's ?

And thanks for all your help on this :)

|||Did you try using DT_DBTIMESTAMP instead of DT_DATE in your source's output?|||

Yes it seems no matter what datatype I give that column

database date [DT_DBDATE]
database time [DT_DBTIME]
database timestamp [DT_DBTIMESTAMP]
date [DT_DATE]

They all come back with the same error

|||

Is it possible to use something like the following to edit the date format ?

http://sqlservercode.blogspot.com/2005/09/date-formatting-in-sql-server_21.html

I tried to use it but I couldn't get it working at all

|||

Something what should work is:

- import the date as string

- use an expression in a "Derived Column" transform to cut the date into pieces, rearange it to a format wórking with SQL Server and convert to a "working" datatype

I wrote already about that: http://forums.microsoft.com/MSDN/showpost.aspx?postid=199322&siteid=1

|||

Perfect Thomas thanks so much for your help

In the end the derived column between the source and destination fixed 90% of my woes :) which is excellent news for me

Again thank you, I appreciate it.

|||

Also the excel worksheet has a limit of 65536 recrods where as flat file has a limit of one million.