BCP Table Named "Function"

Dear all,
Can a table be named as "Function" in SQL 2000?
I have a table using this name. When I tried to BCP it (to extract
rows out), I got error message ".... near Function."
The BCP command I used was:
BCP mydb.dbo.function OUT d:\extract\Function.bcp -Smypc -Usa -
Pmypwd -c
Thanks in advance.
Regards,
Goh Tiam TjaiTry brackets around the table name'
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
<gohtiamtjai@.gmail.com> wrote in message
news:963eea6a-681b-4f81-b50f-3fa3792a7a77@.q39g2000hsf.googlegroups.com...
> Dear all,
> Can a table be named as "Function" in SQL 2000?
> I have a table using this name. When I tried to BCP it (to extract
> rows out), I got error message ".... near Function."
> The BCP command I used was:
> BCP mydb.dbo.function OUT d:\extract\Function.bcp -Smypc -Usa -
> Pmypwd -c
> Thanks in advance.
>
> Regards,
> Goh Tiam Tjai|||Thanks, Kevin.
It works with brackets:
BCP mydb.dbo.[function] OUT d:\extract\Function.bcp -Smypc -Usa
-
Pmypwd -c
Regards,
Goh Tiam Tjai
On Jan 9, 9:01 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> Try brackets around the table name'
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net|||You can also use the quotename() function
--
Sincerely,
John K
Knowledgy Consulting, LLC
knowledgy.org
Atlanta's Business Intelligence and Data Warehouse Experts
<gohtiamtjai@.gmail.com> wrote in message
news:963eea6a-681b-4f81-b50f-3fa3792a7a77@.q39g2000hsf.googlegroups.com...
> Dear all,
> Can a table be named as "Function" in SQL 2000?
> I have a table using this name. When I tried to BCP it (to extract
> rows out), I got error message ".... near Function."
> The BCP command I used was:
> BCP mydb.dbo.function OUT d:\extract\Function.bcp -Smypc -Usa -
> Pmypwd -c
> Thanks in advance.
>
> Regards,
> Goh Tiam Tjai|||I met this kinda problem at a customer's environment while trying to set up
a Merge Replication.
Developers used "Percent" for a user-defined data type and while I was
trying to create the publication it caused lots of errors. It took some time
to find it out. However this kind of mistakes (or whatever you call it) can
take more time to find out.
Avoid using special words for your stuff.
--
Ekrem Önsoy
<gohtiamtjai@.gmail.com> wrote in message
news:963eea6a-681b-4f81-b50f-3fa3792a7a77@.q39g2000hsf.googlegroups.com...
> Dear all,
> Can a table be named as "Function" in SQL 2000?
> I have a table using this name. When I tried to BCP it (to extract
> rows out), I got error message ".... near Function."
> The BCP command I used was:
> BCP mydb.dbo.function OUT d:\extract\Function.bcp -Smypc -Usa -
> Pmypwd -c
> Thanks in advance.
>
> Regards,
> Goh Tiam Tjai|||"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13o9kt9hv3fuffd@.corp.supernews.com...
> Try brackets around the table name'
>
I think you left out the part about beating the DB designer around the head
for using a reserved name like this. :-)
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.htmlsql

BCP table export and Shared Locks

Hi,
We use BCP commands like this:
BCP ACC.Dbo.[TableName] out CSV\ TableName.csv /c /k /t "|" /r
"\n" -Sservername -Uname -Ppass
to extract data from SQL Server into CSV files.
I want BCP don't put any lock, including shared lock, on table records. How
can I supply locking hint (NOLOCK) along with the table name?
We try not to use the actual query and just put the table name on the
command line.
Thank you,
AlanTry append -hnolock to the command.
Lucas
"Maxwell2006" wrote:
> Hi,
>
> We use BCP commands like this:
>
> BCP ACC.Dbo.[TableName] out CSV\ TableName.csv /c /k /t "|" /r
> "\n" -Sservername -Uname -Ppass
>
> to extract data from SQL Server into CSV files.
>
> I want BCP don't put any lock, including shared lock, on table records. How
> can I supply locking hint (NOLOCK) along with the table name?
>
> We try not to use the actual query and just put the table name on the
> command line.
>
> Thank you,
> Alan
>
>
>|||Hi Max,
Thank you for posting.
As for the SQL server bcp utility, currently there is only a "TABLOCK" hint
option whch can help switch the lock (when performing bulk
importing/exporting) between row level lock and table level lock.
Therefore, if we need to completely disable any lock when performing the
bulk exporting, we still have to use explicit T-SQL script to do it.
#bcp Utility
http://msdn2.microsoft.com/en-us/library/ms162802.aspx
BTW, if you do not want to pass the T-SQL directly in command prompt, do
you think it possible that we use a batch/script file to programmatically
load such T-SQL script and launch the bcp utility command?
Anyway, please feel free to let me know if you have any other consideration.
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Thank you Steven.
I changed our export scripts to use the query "select * from tableName
(NOLOCK)".
Max
"Steven Cheng[MSFT]" <stcheng@.online.microsoft.com> wrote in message
news:t8tbYX3jGHA.4528@.TK2MSFTNGXA01.phx.gbl...
> Hi Max,
> Thank you for posting.
> As for the SQL server bcp utility, currently there is only a "TABLOCK"
> hint
> option whch can help switch the lock (when performing bulk
> importing/exporting) between row level lock and table level lock.
> Therefore, if we need to completely disable any lock when performing the
> bulk exporting, we still have to use explicit T-SQL script to do it.
> #bcp Utility
> http://msdn2.microsoft.com/en-us/library/ms162802.aspx
> BTW, if you do not want to pass the T-SQL directly in command prompt, do
> you think it possible that we use a batch/script file to programmatically
> load such T-SQL script and launch the bcp utility command?
> Anyway, please feel free to let me know if you have any other
> consideration.
> Regards,
> Steven Cheng
> Microsoft MSDN Online Support Lead
>
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> Get Secure! www.microsoft.com/security
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Thanks for your followup Max,
Glad that you've got a solution to work on it. If there is anything else we
can help, please feel free to post here.
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

BCP Out Slow?

I am running the following BCP to extract a table with 156641604 rows.

bcp TestDB..data out test3.bcp -T -b1000000 -a32000

When running this i notice that the disk read bytes\sec counter in performance monitor on the drive that has the database devices is only reading 30mb\sec. I am writing the bcp file to a different drive. Both drives are far more capable of achieving much higher IO. Is this a limitation with BCP or are there futher switches available that would speed this process up. Also the drives are both local so the bottle neck is not network. Any ideas?

Hi Andy,

Try not using the -a option, and reducing the batch size to 100000.

What is the purpose of extracting the data. Do you need it to be in char format.

If use -n for native, it will be a bit quicker.

Jag

|||

Hi Jag

That does help with throughput. The thing I don't understand is that even if I run a Select * From TestDB..data i get low read IO on this table. I know the disk IO is far more capable and if I run a DBCC SHOWCONTIG command for instance, the disk read bytes\sec counter in performance monitor jumps to over 70mb\sec. My question is, why doesn't BCP or a Select statement achieve this IO?

|||That's because it might not need to. The DBCC is going to explicitly hit the disk drive. Doing a BCP or a SELECT is going to utilize the normal infrastructure. It is going to read it from memory and then go out to disk as needed. Since SQL Server has an intelligent read ahead capability, it might only need 30mb/sec to keep up with the write process out the other side.|||Ok i can see what you are saying but the write IO is hovering around 30mb\sec also when it can write much faster than that. Is this IO limitation possibly due to the row count being so high, or some other factor?|||The row count shouldn't have any effect on this. BCP is going to run just as fast, regardless of the number of rows. It simply starts at the beginning and is done when SQL Server doesn't send anything else to it. I would suggest comparing this to the performance you get using SSIS. From everything that I've seen thus far, SSIS is going to move stuff a lot faster than BCP. Has to do with the basic interface that is used to connect to and get the data.

BCP Out Command

I'm using the BCP OUT command to extract the contents of a SQL table to a
text file. When doing this is there an parameter to format the data into a
fixed format?
Script being used:
bcp.exe databasename.dbo.table OUT datafile -t
"," -c -CRAW -Sservername -Uuser -Ppassword
ThanksBrent Stevenson (essexbs@.insightbb.com) writes:
> I'm using the BCP OUT command to extract the contents of a SQL table to a
> text file. When doing this is there an parameter to format the data into a
> fixed format?
> Script being used:
> bcp.exe databasename.dbo.table OUT datafile -t
> "," -c -CRAW -Sservername -Uuser -Ppassword
Do you mean a format where each column has a fixed length? You would need to
use a format file for that.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

bcp extract with , (comma) as decimal

Hello,
I am desperatly trying to export a table from my sql server.
Using bcp I only have my csv file with . as decimal for all numbers (I also
tried the export wizard of the sql console).
Is there a way to obtain these figures with a comma , as decimal?
The ddl of the table is below and numbers are stored in :
[number_value] [float]
CREATE TABLE [dbo].[table] (
[id] [int] NOT NULL ,
[id2] [int] NOT NULL ,
[date_lo] [smalldatetime] NOT NULL ,
[number_type] [int] NOT NULL ,
[is_estimated] [varchar] (2) NULL ,
[number_value] [float] NULL ,
[date_value] [smalldatetime] NULL ,
[string_value] [varchar] (255) NULL ,
[number_int_value] [int] NULL
) ON [PRIMARY]
GO
Thanks in advance for your help.
You can try DTS and edit the default transformation script to be like this
Function Main()
DTSDestination("id") = DTSSource("id")
DTSDestination("id2") = DTSSource("id2")
DTSDestination("date_lo") = DTSSource("date_lo")
DTSDestination("number_type") = DTSSource("number_type")
DTSDestination("is_estimated") = DTSSource("is_estimated")
DTSDestination("number_value") = replace(DTSSource("number_value"),".",",")
' Do replacement here
DTSDestination("date_value") = DTSSource("date_value")
DTSDestination("string_value") = DTSSource("string_value")
DTSDestination("number_int_value") = DTSSource("number_int_value")
Main = DTSTransformStat_OK
End Function
Is this what you need?
Thanks,
Mohamed Sharaf
MEA Developer Support Center
ITWorx on behalf Microsoft EMEA GTSC
<grille11@.yahoo.com> wrote in message
news:cni2ha$88a$1@.reader1.imaginet.fr...
> Hello,
> I am desperatly trying to export a table from my sql server.
> Using bcp I only have my csv file with . as decimal for all numbers (I
> also
> tried the export wizard of the sql console).
> Is there a way to obtain these figures with a comma , as decimal?
> The ddl of the table is below and numbers are stored in :
> [number_value] [float]
>
> CREATE TABLE [dbo].[table] (
> [id] [int] NOT NULL ,
> [id2] [int] NOT NULL ,
> [date_lo] [smalldatetime] NOT NULL ,
> [number_type] [int] NOT NULL ,
> [is_estimated] [varchar] (2) NULL ,
> [number_value] [float] NULL ,
> [date_value] [smalldatetime] NULL ,
> [string_value] [varchar] (255) NULL ,
> [number_int_value] [int] NULL
> ) ON [PRIMARY]
> GO
>
> Thanks in advance for your help.
>
>
|||I have an error code: 0
description: Invalid use of Null: 'replace'
The first 40000 rows have a null value and the rest as a value with a
deciaml as .
Here is an example, the first row as no value and the one below has
1513.2527515762899 that need to be changed as 1513,2527515762899.
1;0;0;2004-02-27 00:00:00;3;0;N;;2004-02-27 00:00:00;;
1;1990;2;2003-01-09 00:00:00;5;0;N;1513.2527515762899;;;
"Mohamed Sharaf" <Mohamed.Sharaf@.egdsc.microsoft.com> wrote in message
news:uReyO5WzEHA.1292@.TK2MSFTNGP10.phx.gbl...
> You can try DTS and edit the default transformation script to be like this
> Function Main()
> DTSDestination("id") = DTSSource("id")
> DTSDestination("id2") = DTSSource("id2")
> DTSDestination("date_lo") = DTSSource("date_lo")
> DTSDestination("number_type") = DTSSource("number_type")
> DTSDestination("is_estimated") = DTSSource("is_estimated")
> DTSDestination("number_value") =
replace(DTSSource("number_value"),".",",")
> ' Do replacement here
> DTSDestination("date_value") = DTSSource("date_value")
> DTSDestination("string_value") = DTSSource("string_value")
> DTSDestination("number_int_value") = DTSSource("number_int_value")
> Main = DTSTransformStat_OK
> End Function
> Is this what you need?
> Thanks,
> --
> Mohamed Sharaf
> MEA Developer Support Center
> ITWorx on behalf Microsoft EMEA GTSC
>
> <grille11@.yahoo.com> wrote in message
> news:cni2ha$88a$1@.reader1.imaginet.fr...
>

bcp extract with , (comma) as decimal

Hello,
I am desperatly trying to export a table from my sql server.
Using bcp I only have my csv file with . as decimal for all numbers (I also
tried the export wizard of the sql console).
Is there a way to obtain these figures with a comma , as decimal?
The ddl of the table is below and numbers are stored in :
[number_value] [float]
CREATE TABLE [dbo].[table] (
[id] [int] NOT NULL ,
[id2] [int] NOT NULL ,
[date_lo] [smalldatetime] NOT NULL ,
[number_type] [int] NOT NULL ,
[is_estimated] [varchar] (2) NULL ,
[number_value] [float] NULL ,
[date_value] [smalldatetime] NULL ,
[string_value] [varchar] (255) NULL ,
[number_int_value] [int] NULL
) ON [PRIMARY]
GO
Thanks in advance for your help.
You can try DTS and edit the default transformation script to be like this
Function Main()
DTSDestination("id") = DTSSource("id")
DTSDestination("id2") = DTSSource("id2")
DTSDestination("date_lo") = DTSSource("date_lo")
DTSDestination("number_type") = DTSSource("number_type")
DTSDestination("is_estimated") = DTSSource("is_estimated")
DTSDestination("number_value") = replace(DTSSource("number_value"),".",",")
' Do replacement here
DTSDestination("date_value") = DTSSource("date_value")
DTSDestination("string_value") = DTSSource("string_value")
DTSDestination("number_int_value") = DTSSource("number_int_value")
Main = DTSTransformStat_OK
End Function
Is this what you need?
Thanks,
Mohamed Sharaf
MEA Developer Support Center
ITWorx on behalf Microsoft EMEA GTSC
<grille11@.yahoo.com> wrote in message
news:cni2ha$88a$1@.reader1.imaginet.fr...
> Hello,
> I am desperatly trying to export a table from my sql server.
> Using bcp I only have my csv file with . as decimal for all numbers (I
> also
> tried the export wizard of the sql console).
> Is there a way to obtain these figures with a comma , as decimal?
> The ddl of the table is below and numbers are stored in :
> [number_value] [float]
>
> CREATE TABLE [dbo].[table] (
> [id] [int] NOT NULL ,
> [id2] [int] NOT NULL ,
> [date_lo] [smalldatetime] NOT NULL ,
> [number_type] [int] NOT NULL ,
> [is_estimated] [varchar] (2) NULL ,
> [number_value] [float] NULL ,
> [date_value] [smalldatetime] NULL ,
> [string_value] [varchar] (255) NULL ,
> [number_int_value] [int] NULL
> ) ON [PRIMARY]
> GO
>
> Thanks in advance for your help.
>
>
|||I have an error code: 0
description: Invalid use of Null: 'replace'
The first 40000 rows have a null value and the rest as a value with a
deciaml as .
Here is an example, the first row as no value and the one below has
1513.2527515762899 that need to be changed as 1513,2527515762899.
1;0;0;2004-02-27 00:00:00;3;0;N;;2004-02-27 00:00:00;;
1;1990;2;2003-01-09 00:00:00;5;0;N;1513.2527515762899;;;
"Mohamed Sharaf" <Mohamed.Sharaf@.egdsc.microsoft.com> wrote in message
news:uReyO5WzEHA.1292@.TK2MSFTNGP10.phx.gbl...
> You can try DTS and edit the default transformation script to be like this
> Function Main()
> DTSDestination("id") = DTSSource("id")
> DTSDestination("id2") = DTSSource("id2")
> DTSDestination("date_lo") = DTSSource("date_lo")
> DTSDestination("number_type") = DTSSource("number_type")
> DTSDestination("is_estimated") = DTSSource("is_estimated")
> DTSDestination("number_value") =
replace(DTSSource("number_value"),".",",")
> ' Do replacement here
> DTSDestination("date_value") = DTSSource("date_value")
> DTSDestination("string_value") = DTSSource("string_value")
> DTSDestination("number_int_value") = DTSSource("number_int_value")
> Main = DTSTransformStat_OK
> End Function
> Is this what you need?
> Thanks,
> --
> Mohamed Sharaf
> MEA Developer Support Center
> ITWorx on behalf Microsoft EMEA GTSC
>
> <grille11@.yahoo.com> wrote in message
> news:cni2ha$88a$1@.reader1.imaginet.fr...
>

bcp extract with , (comma) as decimal

Hello,
I am desperatly trying to export a table from my sql server.
Using bcp I only have my csv file with . as decimal for all numbers (I also
tried the export wizard of the sql console).
Is there a way to obtain these figures with a comma , as decimal?
The ddl of the table is below and numbers are stored in :
[number_value] [float]
CREATE TABLE [dbo].[table] (
[id] [int] NOT NULL ,
[id2] [int] NOT NULL ,
[date_lo] [smalldatetime] NOT NULL ,
[number_type] [int] NOT NULL ,
[is_estimated] [varchar] (2) NULL ,
[number_value] [float] NULL ,
[date_value] [smalldatetime] NULL ,
[string_value] [varchar] (255) NULL ,
[number_int_value] [int] NULL
) ON [PRIMARY]
GO
Thanks in advance for your help.
You can try DTS and edit the default transformation script to be like this
Function Main()
DTSDestination("id") = DTSSource("id")
DTSDestination("id2") = DTSSource("id2")
DTSDestination("date_lo") = DTSSource("date_lo")
DTSDestination("number_type") = DTSSource("number_type")
DTSDestination("is_estimated") = DTSSource("is_estimated")
DTSDestination("number_value") = replace(DTSSource("number_value"),".",",")
' Do replacement here
DTSDestination("date_value") = DTSSource("date_value")
DTSDestination("string_value") = DTSSource("string_value")
DTSDestination("number_int_value") = DTSSource("number_int_value")
Main = DTSTransformStat_OK
End Function
Is this what you need?
Thanks,
Mohamed Sharaf
MEA Developer Support Center
ITWorx on behalf Microsoft EMEA GTSC
<grille11@.yahoo.com> wrote in message
news:cni2ha$88a$1@.reader1.imaginet.fr...
> Hello,
> I am desperatly trying to export a table from my sql server.
> Using bcp I only have my csv file with . as decimal for all numbers (I
> also
> tried the export wizard of the sql console).
> Is there a way to obtain these figures with a comma , as decimal?
> The ddl of the table is below and numbers are stored in :
> [number_value] [float]
>
> CREATE TABLE [dbo].[table] (
> [id] [int] NOT NULL ,
> [id2] [int] NOT NULL ,
> [date_lo] [smalldatetime] NOT NULL ,
> [number_type] [int] NOT NULL ,
> [is_estimated] [varchar] (2) NULL ,
> [number_value] [float] NULL ,
> [date_value] [smalldatetime] NULL ,
> [string_value] [varchar] (255) NULL ,
> [number_int_value] [int] NULL
> ) ON [PRIMARY]
> GO
>
> Thanks in advance for your help.
>
>
|||I have an error code: 0
description: Invalid use of Null: 'replace'
The first 40000 rows have a null value and the rest as a value with a
deciaml as .
Here is an example, the first row as no value and the one below has
1513.2527515762899 that need to be changed as 1513,2527515762899.
1;0;0;2004-02-27 00:00:00;3;0;N;;2004-02-27 00:00:00;;
1;1990;2;2003-01-09 00:00:00;5;0;N;1513.2527515762899;;;
"Mohamed Sharaf" <Mohamed.Sharaf@.egdsc.microsoft.com> wrote in message
news:uReyO5WzEHA.1292@.TK2MSFTNGP10.phx.gbl...
> You can try DTS and edit the default transformation script to be like this
> Function Main()
> DTSDestination("id") = DTSSource("id")
> DTSDestination("id2") = DTSSource("id2")
> DTSDestination("date_lo") = DTSSource("date_lo")
> DTSDestination("number_type") = DTSSource("number_type")
> DTSDestination("is_estimated") = DTSSource("is_estimated")
> DTSDestination("number_value") =
replace(DTSSource("number_value"),".",",")
> ' Do replacement here
> DTSDestination("date_value") = DTSSource("date_value")
> DTSDestination("string_value") = DTSSource("string_value")
> DTSDestination("number_int_value") = DTSSource("number_int_value")
> Main = DTSTransformStat_OK
> End Function
> Is this what you need?
> Thanks,
> --
> Mohamed Sharaf
> MEA Developer Support Center
> ITWorx on behalf Microsoft EMEA GTSC
>
> <grille11@.yahoo.com> wrote in message
> news:cni2ha$88a$1@.reader1.imaginet.fr...
>
|||Hello,
This error happens because of null values so we can change the code
to become
Function Main()
DTSDestination("id") = DTSSource("id")
DTSDestination("id2") = DTSSource("id2")
DTSDestination("date_lo") = DTSSource("date_lo")
DTSDestination("number_type") = DTSSource("number_type")
DTSDestination("is_estimated") = DTSSource("is_estimated")
if DTSSource("number_value")<>"" then
DTSDestination("number_value") =
replace(DTSSource("number_value"),".",",") ' Do replacement here
end if
DTSDestination("date_value") = DTSSource("date_value")
DTSDestination("string_value") = DTSSource("string_value")
DTSDestination("number_int_value") = DTSSource("number_int_value")
Main = DTSTransformStat_OK
End Function
I didn't try this code , so it may still needs some modifications, please
try it.
Best regards,
Mohamed Sharaf
MEA Developer Support Center
ITWorx on behalf Microsoft EMEA GTSC

bcp extract with , (comma) as decimal

Hello,
I am desperatly trying to export a table from my sql server.
Using bcp I only have my csv file with . as decimal for all numbers (I also
tried the export wizard of the sql console).
Is there a way to obtain these figures with a comma , as decimal?
The ddl of the table is below and numbers are stored in :
[number_value] [float]
CREATE TABLE [dbo].[table] (
[id] [int] NOT NULL ,
[id2] [int] NOT NULL ,
[date_lo] [smalldatetime] NOT NULL ,
[number_type] [int] NOT NULL ,
[is_estimated] [varchar] (2) NULL ,
[number_value] [float] NULL ,
[date_value] [smalldatetime] NULL ,
[string_value] [varchar] (255) NULL ,
[number_int_value] [int] NULL
) ON [PRIMARY]
GO
Thanks in advance for your help.You can try DTS and edit the default transformation script to be like this
Function Main()
DTSDestination("id") = DTSSource("id")
DTSDestination("id2") = DTSSource("id2")
DTSDestination("date_lo") = DTSSource("date_lo")
DTSDestination("number_type") = DTSSource("number_type")
DTSDestination("is_estimated") = DTSSource("is_estimated")
DTSDestination("number_value") = replace(DTSSource("number_value"),".",",")
' Do replacement here
DTSDestination("date_value") = DTSSource("date_value")
DTSDestination("string_value") = DTSSource("string_value")
DTSDestination("number_int_value") = DTSSource("number_int_value")
Main = DTSTransformStat_OK
End Function
Is this what you need?
Thanks,
--
Mohamed Sharaf
MEA Developer Support Center
ITWorx on behalf Microsoft EMEA GTSC
<grille11@.yahoo.com> wrote in message
news:cni2ha$88a$1@.reader1.imaginet.fr...
> Hello,
> I am desperatly trying to export a table from my sql server.
> Using bcp I only have my csv file with . as decimal for all numbers (I
> also
> tried the export wizard of the sql console).
> Is there a way to obtain these figures with a comma , as decimal?
> The ddl of the table is below and numbers are stored in :
> [number_value] [float]
>
> CREATE TABLE [dbo].[table] (
> [id] [int] NOT NULL ,
> [id2] [int] NOT NULL ,
> [date_lo] [smalldatetime] NOT NULL ,
> [number_type] [int] NOT NULL ,
> [is_estimated] [varchar] (2) NULL ,
> [number_value] [float] NULL ,
> [date_value] [smalldatetime] NULL ,
> [string_value] [varchar] (255) NULL ,
> [number_int_value] [int] NULL
> ) ON [PRIMARY]
> GO
>
> Thanks in advance for your help.
>
>|||I have an error code: 0
description: Invalid use of Null: 'replace'
The first 40000 rows have a null value and the rest as a value with a
deciaml as .
Here is an example, the first row as no value and the one below has
1513.2527515762899 that need to be changed as 1513,2527515762899.
1;0;0;2004-02-27 00:00:00;3;0;N;;2004-02-27 00:00:00;;
1;1990;2;2003-01-09 00:00:00;5;0;N;1513.2527515762899;;;
"Mohamed Sharaf" <Mohamed.Sharaf@.egdsc.microsoft.com> wrote in message
news:uReyO5WzEHA.1292@.TK2MSFTNGP10.phx.gbl...
> You can try DTS and edit the default transformation script to be like this
> Function Main()
> DTSDestination("id") = DTSSource("id")
> DTSDestination("id2") = DTSSource("id2")
> DTSDestination("date_lo") = DTSSource("date_lo")
> DTSDestination("number_type") = DTSSource("number_type")
> DTSDestination("is_estimated") = DTSSource("is_estimated")
> DTSDestination("number_value") =
replace(DTSSource("number_value"),".",",")
> ' Do replacement here
> DTSDestination("date_value") = DTSSource("date_value")
> DTSDestination("string_value") = DTSSource("string_value")
> DTSDestination("number_int_value") = DTSSource("number_int_value")
> Main = DTSTransformStat_OK
> End Function
> Is this what you need?
> Thanks,
> --
> Mohamed Sharaf
> MEA Developer Support Center
> ITWorx on behalf Microsoft EMEA GTSC
>
> <grille11@.yahoo.com> wrote in message
> news:cni2ha$88a$1@.reader1.imaginet.fr...
>

bcp extract with , (comma) as decimal

Hello,
I am desperatly trying to export a table from my sql server.
Using bcp I only have my csv file with . as decimal for all numbers (I also
tried the export wizard of the sql console).
Is there a way to obtain these figures with a comma , as decimal?
The ddl of the table is below and numbers are stored in :
[number_value] [float]
CREATE TABLE [dbo].[table] (
[id] [int] NOT NULL ,
[id2] [int] NOT NULL ,
[date_lo] [smalldatetime] NOT NULL ,
[number_type] [int] NOT NULL ,
[is_estimated] [varchar] (2) NULL ,
[number_value] [float] NULL ,
[date_value] [smalldatetime] NULL ,
[string_value] [varchar] (255) NULL ,
[number_int_value] [int] NULL
) ON [PRIMARY]
GO
Thanks in advance for your help.You can try DTS and edit the default transformation script to be like this
Function Main()
DTSDestination("id") = DTSSource("id")
DTSDestination("id2") = DTSSource("id2")
DTSDestination("date_lo") = DTSSource("date_lo")
DTSDestination("number_type") = DTSSource("number_type")
DTSDestination("is_estimated") = DTSSource("is_estimated")
DTSDestination("number_value") = replace(DTSSource("number_value"),".",",")
' Do replacement here
DTSDestination("date_value") = DTSSource("date_value")
DTSDestination("string_value") = DTSSource("string_value")
DTSDestination("number_int_value") = DTSSource("number_int_value")
Main = DTSTransformStat_OK
End Function
Is this what you need?
Thanks,
--
Mohamed Sharaf
MEA Developer Support Center
ITWorx on behalf Microsoft EMEA GTSC
<grille11@.yahoo.com> wrote in message
news:cni2ha$88a$1@.reader1.imaginet.fr...
> Hello,
> I am desperatly trying to export a table from my sql server.
> Using bcp I only have my csv file with . as decimal for all numbers (I
> also
> tried the export wizard of the sql console).
> Is there a way to obtain these figures with a comma , as decimal?
> The ddl of the table is below and numbers are stored in :
> [number_value] [float]
>
> CREATE TABLE [dbo].[table] (
> [id] [int] NOT NULL ,
> [id2] [int] NOT NULL ,
> [date_lo] [smalldatetime] NOT NULL ,
> [number_type] [int] NOT NULL ,
> [is_estimated] [varchar] (2) NULL ,
> [number_value] [float] NULL ,
> [date_value] [smalldatetime] NULL ,
> [string_value] [varchar] (255) NULL ,
> [number_int_value] [int] NULL
> ) ON [PRIMARY]
> GO
>
> Thanks in advance for your help.
>
>|||I have an error code: 0
description: Invalid use of Null: 'replace'
The first 40000 rows have a null value and the rest as a value with a
deciaml as .
Here is an example, the first row as no value and the one below has
1513.2527515762899 that need to be changed as 1513,2527515762899.
1;0;0;2004-02-27 00:00:00;3;0;N;;2004-02-27 00:00:00;;
1;1990;2;2003-01-09 00:00:00;5;0;N;1513.2527515762899;;;
"Mohamed Sharaf" <Mohamed.Sharaf@.egdsc.microsoft.com> wrote in message
news:uReyO5WzEHA.1292@.TK2MSFTNGP10.phx.gbl...
> You can try DTS and edit the default transformation script to be like this
> Function Main()
> DTSDestination("id") = DTSSource("id")
> DTSDestination("id2") = DTSSource("id2")
> DTSDestination("date_lo") = DTSSource("date_lo")
> DTSDestination("number_type") = DTSSource("number_type")
> DTSDestination("is_estimated") = DTSSource("is_estimated")
> DTSDestination("number_value") =replace(DTSSource("number_value"),".",",")
> ' Do replacement here
> DTSDestination("date_value") = DTSSource("date_value")
> DTSDestination("string_value") = DTSSource("string_value")
> DTSDestination("number_int_value") = DTSSource("number_int_value")
> Main = DTSTransformStat_OK
> End Function
> Is this what you need?
> Thanks,
> --
> Mohamed Sharaf
> MEA Developer Support Center
> ITWorx on behalf Microsoft EMEA GTSC
>
> <grille11@.yahoo.com> wrote in message
> news:cni2ha$88a$1@.reader1.imaginet.fr...
> > Hello,
> >
> > I am desperatly trying to export a table from my sql server.
> > Using bcp I only have my csv file with . as decimal for all numbers (I
> > also
> > tried the export wizard of the sql console).
> > Is there a way to obtain these figures with a comma , as decimal?
> > The ddl of the table is below and numbers are stored in :
> >
> > [number_value] [float]
> >
> >
> >
> > CREATE TABLE [dbo].[table] (
> > [id] [int] NOT NULL ,
> > [id2] [int] NOT NULL ,
> > [date_lo] [smalldatetime] NOT NULL ,
> > [number_type] [int] NOT NULL ,
> > [is_estimated] [varchar] (2) NULL ,
> > [number_value] [float] NULL ,
> > [date_value] [smalldatetime] NULL ,
> > [string_value] [varchar] (255) NULL ,
> > [number_int_value] [int] NULL
> > ) ON [PRIMARY]
> > GO
> >
> >
> > Thanks in advance for your help.
> >
> >
> >
>

bcp and trigger: missing data in bcp out file

I have made trigger on table 'FER' that would be fired if data is
inserted, updated to the table. And also, I made batch file using bcp
to extract the newly updated / inserted records.

But I got missing data in bcp out file like this:

Missing 1200 records, blocked at:
/*
777946 296188 2007-01-29 21:25:45.063

778145 296494 2007-01-29 21:25:47.063
*/

1. trigger.sql
CREATE TABLE [FERUpdate] (
[id] [int] NOT NULL ,
[fid] [int] NOT NULL ,
[sid] [int] NOT NULL ,
[UpdatePass] [int] NULL
) ON [PRIMARY]
GO
create trigger trgFERUpdate on FER For Insert,Update as
insert into FERUpdate(id,fid,sid) select ins.id, ins.fid,ins.sid from
inserted ins

2. bcp.bat
--
isql -U <user-P <pw-S server -Q "update AA..FERUpdate set
UpdatePass=1 where UpdatePass is null"

bcp "select a.* from AA..FER a, AA..FERUpdate b where a.fid=b.fid and
a.sid=b.sid and b.fid<>-1 and b.sid<>-1 and b.updatepass=1" queryout
%TFN_NOW%.wrk -U <user-P <pw-S server -f FER.fmt

isql -U <user-P <pw-S server -Q "delete from AA..FERUpdate where
UpdatePass=1"
--
--

I have been struggling with this for these two days. Your any helps
are appreciated, Please help me out!! Thanks!!!(danceli@.gmail.com) writes:

Quote:

Originally Posted by

I have made trigger on table 'FER' that would be fired if data is
inserted, updated to the table. And also, I made batch file using bcp
to extract the newly updated / inserted records.
>
But I got missing data in bcp out file like this:
>
Missing 1200 records, blocked at:
/*
777946 296188 2007-01-29 21:25:45.063
>
778145 296494 2007-01-29 21:25:47.063
*/

What numbers are these?

Quote:

Originally Posted by

2. bcp.bat
--
isql -U <user-P <pw-S server -Q "update AA..FERUpdate set
UpdatePass=1 where UpdatePass is null"
>
bcp "select a.* from AA..FER a, AA..FERUpdate b where a.fid=b.fid and
a.sid=b.sid and b.fid<>-1 and b.sid<>-1 and b.updatepass=1" queryout
%TFN_NOW%.wrk -U <user-P <pw-S server -f FER.fmt
>
isql -U <user-P <pw-S server -Q "delete from AA..FERUpdate where
UpdatePass=1"
--

How often do you run this?
What is the meaning if the <-1 things?

And how do you conclude that the data is missing? There is no
ORDER BY clause in your SELECT, so the missing rows may be elsewhere
in the file.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Either you query is wrong or you did the DELETE operation before the
bcp out command.

BCP and Column Names

Hi -
I would like to export the column names and data from a table to a tab delimited file. I understand that when using BCP, one would first extract the column names and then concatenate the data. I am looking for a means to dump the column names. I am
an extremely novice user, so an example would be greatly appreciated.
Thanks!
Lily
Actually it's very easy, open query analyzer, go to options, select
Results tab, then select Results to File in Default results target drop
down box, then select the delimiter in the drop down box below, make
sure Print column headers box is checked, then click OK
then type in the follow:
set nocount on
select * from [your table]
run the query and it will ask you for the filename
Eric Li
SQL DBA
MCDBA
Lily wrote:
> Hi -
> I would like to export the column names and data from a table to a tab delimited file. I understand that when using BCP, one would first extract the column names and then concatenate the data. I am looking for a means to dump the column names. I a
m an extremely novice user, so an example would be greatly appreciated.
> Thanks!
> Lily
|||BCP will not export the column heading. You can use OSQL to do this. Look
at Books on LIne for the arguments to pass to get it to be formated as you
want.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||Hi,
Easy way is to use the Import and Export Utility from SQL server program
groups. Select the source as sql server and destination as Text file.
In the "destination file format" Screen in the wizard check the option
"First row has column names".
This will give you the text file out put with comma seperated with column
headings.
Thanks
Hari
MCDBA
"Lily" <anonymous@.discussions.microsoft.com> wrote in message
news:88A999B6-73A3-4612-A6CE-93F9403039D1@.microsoft.com...
> Hi -
> I would like to export the column names and data from a table to a tab
delimited file. I understand that when using BCP, one would first extract
the column names and then concatenate the data. I am looking for a means
to dump the column names. I am an extremely novice user, so an example
would be greatly appreciated.
> Thanks!
> Lily
|||Everyone always suggests using the EM or QA tools - but they are not on every workstation - nor should they be.
How about going into EXCEL - DATA>IMPORT EXTERNAL DATA>IMPORT DATA>NEW SQL SERVER CONNECTION...
Follow that through - open the table you want and you get data in EXCEL - with headings.
EXCEL can save as TDF, CSV, XLS - whatever you want.
Once you create a NEW SQL SERVER CONNECTION, it remains in the list for future use.

BCP and Column Names

Hi -
I would like to export the column names and data from a table to a tab delim
ited file. I understand that when using BCP, one would first extract the c
olumn names and then concatenate the data. I am looking for a means to dum
p the column names. I am
an extremely novice user, so an example would be greatly appreciated.
Thanks!
LilyActually it's very easy, open query analyzer, go to options, select
Results tab, then select Results to File in Default results target drop
down box, then select the delimiter in the drop down box below, make
sure Print column headers box is checked, then click OK
then type in the follow:
set nocount on
select * from [your table]
run the query and it will ask you for the filename
Eric Li
SQL DBA
MCDBA
Lily wrote:
> Hi -
> I would like to export the column names and data from a table to a tab delimited f
ile. I understand that when using BCP, one would first extract the column names an
d then concatenate the data. I am looking for a means to dump the column names.
I a
m an extremely novice user, so an example would be greatly appreciated.
> Thanks!
> Lily|||BCP will not export the column heading. You can use OSQL to do this. Look
at Books on LIne for the arguments to pass to get it to be formated as you
want.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Hi,
Easy way is to use the Import and Export Utility from SQL server program
groups. Select the source as sql server and destination as Text file.
In the "destination file format" Screen in the wizard check the option
"First row has column names".
This will give you the text file out put with comma seperated with column
headings.
Thanks
Hari
MCDBA
"Lily" <anonymous@.discussions.microsoft.com> wrote in message
news:88A999B6-73A3-4612-A6CE-93F9403039D1@.microsoft.com...
> Hi -
> I would like to export the column names and data from a table to a tab
delimited file. I understand that when using BCP, one would first extract
the column names and then concatenate the data. I am looking for a means
to dump the column names. I am an extremely novice user, so an example
would be greatly appreciated.
> Thanks!
> Lily|||Everyone always suggests using the EM or QA tools - but they are not on ever
y workstation - nor should they be.
How about going into EXCEL - DATA>IMPORT EXTERNAL DATA>IMPORT DATA>NEW SQL S
ERVER CONNECTION...
Follow that through - open the table you want and you get data in EXCEL - wi
th headings.
EXCEL can save as TDF, CSV, XLS - whatever you want.
Once you create a NEW SQL SERVER CONNECTION, it remains in the list for futu
re use.

bcp : Error trying to connect !

Platform:
Windows XP, SQL Server 2000 sp3a enterprise
We tried to use bcp to extract a simple table into a file
serious error:
SQLState = 08001, NativeError = 17
Error = [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not
exist or access denied.
SQLState = 01000, NativeError = 2
Warning = [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen
(Connect()).
Any idea please !!
there is no sufficient ressources on the net
Thanks.
Maggie
Resolved !
c:> bcp base.dbo.table out "c:\tt.txt" -S server\instance -U sa -P password
instance name and server at the last of command !
"404 found" wrote:

> Platform:
> Windows XP, SQL Server 2000 sp3a enterprise
> We tried to use bcp to extract a simple table into a file
> serious error:
> SQLState = 08001, NativeError = 17
> Error = [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not
> exist or access denied.
> SQLState = 01000, NativeError = 2
> Warning = [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen
> (Connect()).
> Any idea please !!
> there is no sufficient ressources on the net
> Thanks.
> Maggie

bcp : Error trying to connect !

Platform:
Windows XP, SQL Server 2000 sp3a enterprise
We tried to use bcp to extract a simple table into a file
serious error:
SQLState = 08001, NativeError = 17
Error = [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server
does not
exist or access denied.
SQLState = 01000, NativeError = 2
Warning = [Microsoft][ODBC SQL Server Driver][DBNETLIB]Connectio
nOpen
(Connect()).
Any idea please !!
there is no sufficient ressources on the net
Thanks.
MaggieResolved !
c:> bcp base.dbo.table out "c:\tt.txt" -S server\instance -U sa -P password
instance name and server at the last of command !
"404 found" wrote:

> Platform:
> Windows XP, SQL Server 2000 sp3a enterprise
> We tried to use bcp to extract a simple table into a file
> serious error:
> SQLState = 08001, NativeError = 17
> Error = [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Serve
r does not
> exist or access denied.
> SQLState = 01000, NativeError = 2
> Warning = [Microsoft][ODBC SQL Server Driver][DBNETLIB]Connect
ionOpen
> (Connect()).
> Any idea please !!
> there is no sufficient ressources on the net
> Thanks.
> Maggie

bcp : Error trying to connect !

Platform:
Windows XP, SQL Server 2000 sp3a enterprise
We tried to use bcp to extract a simple table into a file
serious error:
SQLState = 08001, NativeError = 17
Error = [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not
exist or access denied.
SQLState = 01000, NativeError = 2
Warning = [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen
(Connect()).
Any idea please !!
there is no sufficient ressources on the net :(
Thanks.
Maggie"404 found" <404found@.discussions.microsoft.com> wrote in message
news:2B3F0821-E3CD-4555-8D1E-2D9949642128@.microsoft.com...
> Platform:
> Windows XP, SQL Server 2000 sp3a enterprise
> We tried to use bcp to extract a simple table into a file
> serious error:
> SQLState = 08001, NativeError = 17
> Error = [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not
> exist or access denied.
> SQLState = 01000, NativeError = 2
> Warning = [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen
> (Connect()).
> Any idea please !!
> there is no sufficient ressources on the net :(
> Thanks.
> Maggie
What does your bcp command look like? It sounds like you didn't specify a
server, or valid login credentials to the server.
Here is a sample one that works on a base table:
bcp Northwind.dbo.Customers out
C:\temp\Customers.dat -SMySQLServer -Usa -PMyPassword -n
Rick Sawtell
MCT, MCSD, MCDBA|||Resolved !
c:> bcp base.dbo.table out "c:\tt.txt" -S server\instance -U sa -P password
instance name and server at the last of command !
"404 found" wrote:
> Platform:
> Windows XP, SQL Server 2000 sp3a enterprise
> We tried to use bcp to extract a simple table into a file
> serious error:
> SQLState = 08001, NativeError = 17
> Error = [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not
> exist or access denied.
> SQLState = 01000, NativeError = 2
> Warning = [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen
> (Connect()).
> Any idea please !!
> there is no sufficient ressources on the net :(
> Thanks.
> Maggie

bcp - Run time file name generation

Hi,
I am currently running a bcp command to dump a table at run-time. This
requirement has slightly changed and now I need to extract data based on
certain time interval (once in 5 minutes). Because of this, I need to name my
output files as <tablename>_<timestamp>.
My current bcp command looks like this
bcp "select * from asu..t_Skill_Group_Half_Hour WITH (NOLOCK) where
DbDateTime >= '2007-3-5 10:00:00' and DbDateTime < '2007-3-5 10:05:00'"
queryout "table_name.txt" -c -SMySQLServer -T
I need the table name as "table_name_<timestamp>.txt"
Can someone help me with this please?
Thank you.
Regards,
Karthik
Karthik (Karthik@.discussions.microsoft.com) writes:
> I am currently running a bcp command to dump a table at run-time. This
> requirement has slightly changed and now I need to extract data based on
> certain time interval (once in 5 minutes). Because of this, I need to
> name my output files as <tablename>_<timestamp>.
> My current bcp command looks like this
> bcp "select * from asu..t_Skill_Group_Half_Hour WITH (NOLOCK) where
> DbDateTime >= '2007-3-5 10:00:00' and DbDateTime < '2007-3-5 10:05:00'"
> queryout "table_name.txt" -c -SMySQLServer -T
> I need the table name as "table_name_<timestamp>.txt"
> Can someone help me with this please?
From where do you run this BCP command?
An CmdExec job in SQL Server Agent?
A stored procedure?
A BAT file?
Something else?
If you need to extract data as often as every five minutes, I can't escape
the reflection that it may be worth considering using replication.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Hi Erland,
I run this from a SQL Server Stored procedure (which is scheduled to run
once in 5 mins).
I can't use replication as I dont need deletes happening on the publisher to
be propogated. That is why I am using this way to pull the data based on the
datetime column in tables I pull only data that has been inserted and not
deleted.
Thank you.
Regards,
Karthik
"Erland Sommarskog" wrote:

> Karthik (Karthik@.discussions.microsoft.com) writes:
> From where do you run this BCP command?
> An CmdExec job in SQL Server Agent?
> A stored procedure?
> A BAT file?
> Something else?
> If you need to extract data as often as every five minutes, I can't escape
> the reflection that it may be worth considering using replication.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
|||Karthik (Karthik@.discussions.microsoft.com) writes:
> I run this from a SQL Server Stored procedure (which is scheduled to run
> once in 5 mins).
So your current code goes something like this:
SELECT @.cmd = 'bcp "select * from asu..t_Skill_Group_Half_Hour WITH ' +
'(NOLOCK) where DbDateTime >= ''' + convert(char(19), @.start, 126) +
''' and DbDateTime < ''' + convert(char(19), @.stop, 126) +
'queryout "table_name.txt" -c -SMySQLServer -T'
Just change it to:
SELECT @.time = convert(char(8), @.start, 112) +
replace(convert(char(8), @.start, 108), ':', '')
SELECT @.cmd = 'bcp "select * from asu..t_Skill_Group_Half_Hour WITH ' +
'(NOLOCK) where DbDateTime >= ''' + convert(char(19), @.start, 126) +
''' and DbDateTime < ''' + convert(char(19), @.stop, 126) +
'queryout "table_name_' + @.time + '.txt" -c -SMySQLServer -T'
Or am I miassing something?

> I can't use replication as I dont need deletes happening on the publisher
> to be propogated.
The little I know of replication tells me that is possible to configure
so that it does not happen. But it's true that would make the replication
venture more complex.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Batch Insert With SQL_ATTR_PARAMSET_SIZE Slow

The following ODBC Tracing extract shows the approach used to batch insert
thousands of rows, using the SQL_PARC_BATCH feature with ODBC 3.0 over 3.520
Manager & SQL Server 2000:
SQLAllocHandle
SQLSetStmtAttr <SQL_ATTR_PARAMSET_SIZE> = 10003
SQLSetStmtAttr <SQL_ATTR_PARAM_STATUS_PTR>
SQLSetStmtAttr <SQL_ATTR_PARAMS_PROCESSED_PTR>
SQLBindParameter(...) // column-wise binding
SQLBindParameter(...) // column-wise binding
SQLBindParameter(...) // column-wise binding
SQLExecDirect "INSERT INTO TEST(SSS,NNN,DDD) VALUES(?,?,?)"
The same test is executed on Oracle, Sybase Adaptive Server Anywhere and MS
SQL Server. Unfortunately, SQL Server is ten times slower than the other two
databases. Here are the times in seconds:
Oracle 9i = 1 second
Sybase ASA 8 = 1 second
SQL Server 2000 = 10 seconds
All tests are executed on the same machine, the test table is empty before
the test is run. Why is SQL Server so slow? What's wrong here?
Spike
What transaction mode do you use (auto/manual? Is it the same for Oracle,
Sybase, and SQL Server?
"Spike" wrote:

> The following ODBC Tracing extract shows the approach used to batch insert
> thousands of rows, using the SQL_PARC_BATCH feature with ODBC 3.0 over 3.520
> Manager & SQL Server 2000:
> SQLAllocHandle
> SQLSetStmtAttr <SQL_ATTR_PARAMSET_SIZE> = 10003
> SQLSetStmtAttr <SQL_ATTR_PARAM_STATUS_PTR>
> SQLSetStmtAttr <SQL_ATTR_PARAMS_PROCESSED_PTR>
> SQLBindParameter(...) // column-wise binding
> SQLBindParameter(...) // column-wise binding
> SQLBindParameter(...) // column-wise binding
> SQLExecDirect "INSERT INTO TEST(SSS,NNN,DDD) VALUES(?,?,?)"
> The same test is executed on Oracle, Sybase Adaptive Server Anywhere and MS
> SQL Server. Unfortunately, SQL Server is ten times slower than the other two
> databases. Here are the times in seconds:
> Oracle 9i = 1 second
> Sybase ASA 8 = 1 second
> SQL Server 2000 = 10 seconds
> All tests are executed on the same machine, the test table is empty before
> the test is run. Why is SQL Server so slow? What's wrong here?
> --
> Spike

Batch Insert With SQL_ATTR_PARAMSET_SIZE Slow

The following ODBC Tracing extract shows the approach used to batch insert
thousands of rows, using the SQL_PARC_BATCH feature with ODBC 3.0 over 3.520
Manager & SQL Server 2000:
SQLAllocHandle
SQLSetStmtAttr <SQL_ATTR_PARAMSET_SIZE> = 10003
SQLSetStmtAttr <SQL_ATTR_PARAM_STATUS_PTR>
SQLSetStmtAttr <SQL_ATTR_PARAMS_PROCESSED_PTR>
SQLBindParameter(...) // column-wise binding
SQLBindParameter(...) // column-wise binding
SQLBindParameter(...) // column-wise binding
SQLExecDirect "INSERT INTO TEST(SSS,NNN,DDD) VALUES(?,?,?)"
The same test is executed on Oracle, Sybase Adaptive Server Anywhere and MS
SQL Server. Unfortunately, SQL Server is ten times slower than the other two
databases. Here are the times in seconds:
Oracle 9i = 1 second
Sybase ASA 8 = 1 second
SQL Server 2000 = 10 seconds
All tests are executed on the same machine, the test table is empty before
the test is run. Why is SQL Server so slow? What's wrong here?
--
SpikeWhat transaction mode do you use (auto/manual? Is it the same for Oracle,
Sybase, and SQL Server?
"Spike" wrote:

> The following ODBC Tracing extract shows the approach used to batch insert
> thousands of rows, using the SQL_PARC_BATCH feature with ODBC 3.0 over 3.5
20
> Manager & SQL Server 2000:
> SQLAllocHandle
> SQLSetStmtAttr <SQL_ATTR_PARAMSET_SIZE> = 10003
> SQLSetStmtAttr <SQL_ATTR_PARAM_STATUS_PTR>
> SQLSetStmtAttr <SQL_ATTR_PARAMS_PROCESSED_PTR>
> SQLBindParameter(...) // column-wise binding
> SQLBindParameter(...) // column-wise binding
> SQLBindParameter(...) // column-wise binding
> SQLExecDirect "INSERT INTO TEST(SSS,NNN,DDD) VALUES(?,?,?)"
> The same test is executed on Oracle, Sybase Adaptive Server Anywhere and M
S
> SQL Server. Unfortunately, SQL Server is ten times slower than the other t
wo
> databases. Here are the times in seconds:
> Oracle 9i = 1 second
> Sybase ASA 8 = 1 second
> SQL Server 2000 = 10 seconds
> All tests are executed on the same machine, the test table is empty before
> the test is run. Why is SQL Server so slow? What's wrong here?
> --
> Spike