Fast way to get data into SQL Server from C# app
David
of data (doubles and strings mostly). I want to get it into a SQL Server
database. Adding individual rows to an ADO.NET dataset object is way too
slow!
My C# app generates arrays of doubles of length 2000. This is inside a loop
which has to run hundreds of thousands (or millions) of iterations. Each
iteration should be a row in the database, so I'm adding hundreds of
thousands of rows.
psuedo code:
loop 1,000,000 times
calculate and fill double array[2000]
add values from double array to new row in database
end loop
The calculations are fairly fast, but the bottleneck is getting the results
into the database. For example, my machine will do 1.5 million of these
calculations in 8 seconds. (The above loop is 2 billion calculations.)
However, just adding 1,500 rows to an ADO.NET dataset table takes 80
seconds. It would be faster to write the data to a flat file and then DTS
import it into SQL server. Obviously, this manual method isn't an option for
my end users. What are my other options?
Examples with code are greatly appreciated. However, tips or suggestions are
appreciated also! Any help is welcome!
David
Keith Kratochvil
import it into SQL server. Obviously, this manual method isn't an option for
my end users."
You could also look into BCP or Bulk Copy
Why is DTS not an option for your end users?
Can you build an app that calls BCP with the correct params to import the
data into the database?
--
Keith, SQL Server MVP
"David" <dsh...@yahoo.com> wrote in message
news:OrLORzYrBHA.2056@tkmsftngp03...
PPS
rows themselves
and later exploding them to rows ( if necessary ) using a stored procedure ?
"David" <dsh...@yahoo.com> wrote in message
news:OrLORzYrBHA.2056@tkmsftngp03...
James Berry
> I'm writing a C# scientific app (that calls a C DLL). My app generates
lots
> of data (doubles and strings mostly). I want to get it into a SQL Server
> database. Adding individual rows to an ADO.NET dataset object is way too
> slow!
Perhaps you should just call a stored procedure to do the addition, or the
relevant INSERT INTO statement, rather than adding to the locally held
dataset.
Best wishes
James
Eric Imhauser
I think you should udse an Insert command and MSMQ
Eric
"David" <dsh...@yahoo.com> wrote in message
news:OrLORzYrBHA.2056@tkmsftngp03...
[MVP] Thomas Tomiczek
Unless you just send up SOME small data, the MSMQ will blow.
Inserts are SLOW - make a SP.
Better - use BCP, thats way faster
--
Thomas Tomiczek
THONA Consulting Ltd.
(Microsoft MVP C#/.NET)
"Eric Imhauser" <er...@imhauser.com> wrote in message
news:OlKOYsZrBHA.428@tkmsftngp04...
David
statements from C# apps? Can I just use the SqlCommand object without a
dataset?
Question: why is using SQL to INSERT INTO the database (with the associated
disk access) significantly faster than adding a row to a dataset (in
memory)? Something seems wrong with the dataset class. Does anyone have a
reference for programming techniques to use ADO.NET datasets more
efficiently?
David
"James Berry" <ja...@adastra.co.uk> wrote in message
news:ejT5VoZrBHA.1600@tkmsftngp07...
David
(yet) know how I could make a SP call the DLL. I'll go study a SQL Server
book and see what I can find out.
Ultimately, what I am looking for is the best way to get the millions of
calculated values (doubles and strings) into SQL Server 2000. Isn't this an
issue that lots of people have to deal with? How does the rest of the world
get lots of numbers into a SQL database efficiently? Our application that
calls the DLL is being done in C#, but I can forego the use of ADO.NET if
needed.
David
"PPS" <pps...@yahoo.com> wrote in message
news:e35RXPZrBHA.2252@tkmsftngp03...
David
write them to a file and then read them from a file back into memory, just
to get them into SQL Server, to which my application can connect directly. I
know I must be missing something. Isn't this a general problem? I just want
to call the C language DLL, have it calculate my values and then insert them
(millions of them) into the SQL Server 2000 database. Thanks for any tips.
David
"Keith Kratochvil" <keith.krato...@novusprintmedia.com> wrote in
message news:u#cTAPZrBHA.2252@tkmsftngp03...
Keith Kratochvil
You could call a stored procedure (or a sql statement that inserts the data)
10000 times
OR you could write the data to a file and then use BCP to import the data
into SQL Server in one operation. BCP is designed to be fast. Really fast.
It will be lots faster than making 10000 calls to an insert stored
procedure.
I agree that it "sounds" funny (from memory - to a file - to memory again -
just to write it to a table within SQL Server) but as far as I know, that
will be the fastest method.
--
Keith, SQL Server MVP
"David" <dsh...@yahoo.com> wrote in message
news:ORyHV7arBHA.1608@tkmsftngp04...
David
BULK INSERT or BCP that will copy the data from a large array in memory into
the SQL table.
David
"Keith Kratochvil" <keith.krato...@novusprintmedia.com> wrote in
message news:Os0mXLbrBHA.2540@tkmsftngp04...
Sreelatha Samala
> calculated values (doubles and strings) into SQL Server 2000. Isn't this
an
> issue that lots of people have to deal with? How does the rest of the
world
> get lots of numbers into a SQL database efficiently? Our application that
> calls the DLL is being done in C#, but I can forego the use of ADO.NET if
> needed.
> David
>
IRowsetFastLoad in OleDB should do Bulk Copy.
Although I have not explored how to use it in C#.
Wells Caughey
> Thanks for the suggestion. What is the most efficient way to execute SQL
> statements from C# apps? Can I just use the SqlCommand object without a
> dataset?
You can use the method ExecuteNonQuery on SqlCommand to execute commands
on Sql Server without using a DataSet or DataAdapter. However it is not
likely that
you will see a large gain in performance. Every time you issue a command, a
handshake
of packets has to cross the network; wating for these packets serially for
each row is
probably the most expensive part of the entire operation.
In order to get the performance you seek you will probably have to resort to
native OLE DB
or some other mechinism where by you can insert many rows in a single
handshake.
Hope it helps,
Wells
Umachandar Jayachandran
in-memory structures. This is the fastest way than writing to a file & doing
BULK INSERT. I don't know how you can do these directly from C#. I think C#
is a bad choice for what you are doing & if performance is the main
criteria.
--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )
David
using C# for the main app is that it allows me to easily switch to C++ or
even C for portions of the code, with minimal effort. Most of my intensive
calculations are in a C DLL.
"Umachandar Jayachandran" <umach...@yahoo.com> wrote in message
news:ehwf9ycrBHA.2336@tkmsftngp03...
David
dataset is that I was doing it all on my local machine. Therefore, I wasn't
dealing with any network issues, nor even any source database updates. I was
just adding rows to an in-memory dataset.
I'm going to experiment with the suggestions I've gotten here. I have gotten
some very good ideas that should help.
Thanks.
David
"Wells Caughey" <we...@kudzu-tech.com> wrote in message
news:#fSEY6brBHA.2556@tkmsftngp04...
David
find info in online help, right?
Thanks.
David
"Sreelatha Samala" <ssa...@hotmail.com> wrote in message
news:OfbAYwbrBHA.2712@tkmsftngp05...
Carl Sommer
and do
a bulk insert. No dice. I looked at the Bulk Insert Task object, but it
requires an input
file. I then looked at the CMemFile:: class (in-memory file). But that
only gives a file
handle, where as the Bulk Inset Task object requires an input file name.
sigh. so close.
So I think we're headed down the OLE-DB path as well.
Carl
"David" <dsh...@yahoo.com> wrote in message
news:#teR#hdrBHA.1644@tkmsftngp03...
David
If you have any tips you care to share about going down the OLE-DB path, I'd
like to hear them. Code snippets or examples would be welcome, as would
pointers to good reference books.
David
"Carl Sommer" <Carl....@netiq.com> wrote in message
news:OYYhwvkrBHA.2472@tkmsftngp05...
dave mack
dll, but is it possible to do them within SQL? Doing
1,000s of inserts will always be slow if you have to make
1,000 separate calls. But could you call a SP that would
grab and compute all the values within the database? So
it's one stored proc, that may make multiple inserts
itself, but at least it's all running on the one machine.
Basically your removing ado.net completely, except with a
few lines of code that fire up the SP that does the main
work.
I think someone else suggested something similar.
>.
>
Gabriel Oancea
that
exposes the data to ADO or ADO.NET clients.
This will work if SQL Server is not an absolute must.
"David" <dsh...@yahoo.com> wrote in message
news:OrLORzYrBHA.2056@tkmsftngp03...
Greg Patnude
possibly your database transaction is just too big...
How about either ODBC or a direct connection to the SQL Server (bypass ADO) ?
Connect to the DB outside of the calc loop, do the calc & the insert INSIDE of
the loop, commit every time, then disconnect outside of the loop...
pseudo -> {
CONNECT (DB)
FOR i := 1 to 1000000
do your calc;
do your insert ;
commit your insert;
}
DISCONNECT (DB)
Gert van ' Slot
I would open one connection to the SQL server at the start of the
calculation, with every loop, I would create a SQL Insert statement, and
send this to the database using the SqlCommand class. Normally I would group
the commands, but you might run into limits of maximum command size already.
The problem might be that the Insert statement will be HUGE. If one row
contains 2000 columns, are you sure the database model is correct ? Because
it will be very hard to work with this data. A simple export to Excel will
become a task by itself.
Gert
User
1) group a limited and sensible number of rows together
2) convert those row values into a string representation
3) send the string to a SQL stored procedure
4) have the stored procedure parse the string and do a set of INSERTS
5) do the next group of rows
This reduces the number of round trips to the server.
I wonder if it would even be as fast or faster than DTS
If the iterations are fast, maybe it would be simpler to regenerate the data
rather than store it
If the iterations are slow then the SQL storage time doesn't matter much.
David
feedback and additional clarification in case this helps others.
In regard to the timing tests for the approaches I've tried, here is some
information:
1. An in-memory dataset with no connection to any other server and no disk
operations:
Iterating 10,000 times in my test took over 1 hour
2. A SqlCommand object and a SP in SQL Server database:
Iterating 10,000 times in my test took 110 seconds
3. A StreamWriter writing the data to a text file
Iterating 10,000 times in my test took 10 seconds (including converting
double values to strings)
4. A BinaryWriter writing the data to a binary file
Iterating 10,000 times in my test took 6 seconds
Next I want to try some of the bulk insert and/or OLE DB fastload
suggestions. However, my reference books haven't given me sufficient
information for using either of these approaches. I have Troelsen's C# book
and a Wrox C# book.
I would appreciate any references to OLE DB fast load in .NET. I'll report
my results here once I try these approaches.
Network communication issues and/or connecting to the database aren't
factors in my tests --
I'm running the SQL Server on my development machine, a 1 GHz IBM T22.
Other comments:
I want to keep my scientific data in binary format -- I don't want to
convert from C double to ASCII text and back to SQL FLOAT.
I need to store my data in the database for data mining purposes
My calculations can't be performed in TSQL -- they require lots of C code
that is very complex.
If I could have a SP call my C DLL directly, that might help -- I haven't
tried this, but I will if I get some help in figuring out how to do it.
Thanks again for all the comments and I hope this feedback is useful.
David
"David" <dsh...@yahoo.com> wrote in message
news:OrLORzYrBHA.2056@tkmsftngp03...
Keith Kratochvil
suggestions. However, my reference books haven't given me sufficient
information for using either of these approaches. I have Troelsen's C# book
and a Wrox C# book.<<
Have you read up on BCP, DTS, or Bulk Load within Books Online (within the
SQL Server program group)? You should find lots of information there on
these topics.
--
Keith, SQL Server MVP
"David" <dsh...@yahoo.com> wrote in message
news:#mVxk01rBHA.1452@tkmsftngp05...
Scott Swigart
inserts, then commit. Wrapping it in a transaction will make it orders of
magnitude faster. It still may not be as fast as BCP, but then again,
writing a million lines to a text file is not trivial either.
Scott Swigart
.NET Training and Consulting
3 Leaf Solutions, LLC
sc...@3leafsolutions.com
www.3leafsolutions.com
"David" <dsh...@yahoo.com> wrote in message
news:OWjg1lbrBHA.2324@tkmsftngp03...
David
good comparison test.
David
"Scott Swigart" <sc...@3leaf.com> wrote in message
news:#UZcxABsBHA.2216@tkmsftngp05...
David
I implemented your suggestion in my test program (iterating 10,000 times).
Here are the results:
1.Using a stored procedure to insert rows (inside the loop): 110 - 112
seconds
2. Same stored procedure with a begin transaction before loop and commit
after loop: 48-50 seconds
I think this is pretty impressive. The test is more than twice as fast!
Now, for the next comparison test I need to figure out how to call the BCP
API from C#...
David
"Scott Swigart" <sc...@3leaf.com> wrote in message
news:#UZcxABsBHA.2216@tkmsftngp05...
Andy Baron
XML Bulk Load, using SQLXML. When available, this may let you use
streams rather than having to write to files.
-- Andy
David
Thanks for the tip. I'll look into this.
David
"Andy Baron" <Andy_...@msn.com> wrote in message
news:apn66ushgfvjtd6na...@4ax.com...
David
program variables (ODBC)." I am implementing this in C#. I'll post what I
have done so far. If anyone has any tips, they are appreciated.
David
To use bulk copy functions directly on program variables
Allocate an environment handle and a connection handle.
Set SQL_COPT_SS_BCP and SQL_BCP_ON to enable bulk copy operations.
Connect to Microsoft® SQL ServerT.
Call bcp_init to set the following information:
The name of the table or view to bulk copy from or to.
Specify NULL for the name of the data file.
The name of an data file to receive any bulk copy error messages (specify
NULL if you do not want a message file).
The direction of the copy: DB_IN from the application to the view or table
or DB_OUT to the application from the table or view.
Call bcp_bind for each column in the bulk copy to bind the column to a
program variable.
Fill the program variables with data, and call bcp_sendrow to send a row of
data.
After several rows have been sent, call bcp_batch to checkpoint the rows
already sent. It is good practice to call bcp_batch at least once per 1000
rows.
After all rows have been sent, call bcp_done to complete the operation.
You can vary the location and length of program variables during a bulk copy
operation by calling bcp_colptr and bcp_collen.
Use bcp_control to set various bulk copy options. Use bcp_moretext to send
text, ntext, and image data in segments to the server.
Examples
The following example shows using bulk copy functions to bulk copy data from
program variables to SQL Server using bcp_bind and bcp_sendrow.
Error-checking code is removed to simplify this example.
// Sample showing ODBC bulk copy from program variables
// bound with bcp_bind; data sent with bcp_sendrow.
//
// Assumes server has:
//
// CREATE TABLE BCPSource (cola int PRIMARY KEY,
// colb CHAR(10) NULL)
// CREATE TABLE BCPTarget (cola int PRIMARY KEY,
// colb CHAR(10) NULL)
#include <stdio.h>
#include <string.h>
#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#include <odbcss.h>
SQLHENV henv = SQL_NULL_HENV;
HDBC hdbc1 = SQL_NULL_HDBC, hdbc2 = SQL_NULL_HDBC;
SQLHSTMT hstmt2 = SQL_NULL_HSTMT;
int main() {
RETCODE retcode;
// BCP variables.
char *terminator = "\0";
// bcp_done takes a different format return code
// because it returns number of rows bulk copied
// after the last bcp_batch call.
DBINT cRowsDone;
// Set up separate return code for bcp_sendrow so
// it is not using the same retcode as SQLFetch.
RETCODE SendRet;
// Column variables.
// cbCola and cbColb must be defined right before
// Cola and szColb because they are used as
// bulk copy indicator variables.
struct ColaData{
SQLINTEGER cbCola;
SQLINTEGER Cola;
} ColaInst;
struct ColbData{
SQLINTEGER cbColb;
SQLCHAR szColb[11];
} ColbInst;
// Allocate the ODBC environment and save handle.
retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv);
// Notify ODBC that this is an ODBC 3.0 app.
retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
(SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER);
// Allocate ODBC connection handle, set bulk copy mode, and connect.
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc1);
retcode = SQLSetConnectAttr(hdbc1, SQL_COPT_SS_BCP,
(void *)SQL_BCP_ON, SQL_IS_INTEGER);
retcode = SQLConnect(hdbc1, "MyDSN", SQL_NTS,
"sa", SQL_NTS, "MyPassWord", SQL_NTS);
// Initialize the bulk copy.
retcode = bcp_init(hdbc1, "pubs..BCPTarget", NULL,
NULL, DB_IN);
// Bind the program variables for the bulk copy.
retcode = bcp_bind(hdbc1, (BYTE *)&ColaInst.cbCola, 4,
SQL_VARLEN_DATA, NULL, (INT)NULL,
SQLINT4, 1);
// Could normally use strlen to calculate the bcp_bind
// cbTerm parameter, but this terminator is a null byte
// (\0), which gives strlen a value of 0. Explicitly give
// cbTerm a value of 1.
retcode = bcp_bind(hdbc1, (BYTE *)&ColbInst.cbColb, 4, 11,
terminator, 1, SQLCHARACTER, 2);
// Allocate second ODBC connection handle so that bulk copy
// and cursor operations do not conflict.
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc2);
retcode = SQLConnect(hdbc2, "MyDSN", SQL_NTS,
"sa", SQL_NTS, "MyPassWord", SQL_NTS);
// Allocate ODBC statement handle.
retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc2, &hstmt2);
// Bind the SELECT statement to the same program variables
// bound to the bulk copy operation.
retcode = SQLBindCol(hstmt2, 1, SQL_C_SLONG, &ColaInst.Cola, 0,
&ColaInst.cbCola);
retcode = SQLBindCol(hstmt2, 2, SQL_C_CHAR, &ColbInst.szColb, 11,
&ColbInst.cbColb);
// Execute a SELECT statement to build a cursor containing
// the data to be bulk copied to the new table.
retcode = SQLExecDirect(hstmt2,
"SELECT * FROM BCPSource",
SQL_NTS);
// Go into a loop fetching rows from the cursor until
// each row is fetched. Because the bcp_bind calls
// and SQLBindCol calls each reference the same
// variables, each fetch fills the variables used by
// bcp_sendrow, so all you have to do to send the data
// to SQL Server is to call bcp_sendrow.
while ( (retcode = SQLFetch(hstmt2) ) != SQL_NO_DATA) {
if ( (retcode != SQL_SUCCESS) &&
(retcode != SQL_SUCCESS_WITH_INFO) ) {
// Process error.
return(9);
}
if ( (SendRet = bcp_sendrow(hdbc1) ) != SUCCEED ) {
// Process error.
return(9);
}
}
// Signal the end of the bulk copy operation.
cRowsDone = bcp_done(hdbc1);
printf("Number of rows bulk copied after last bcp_batch
call = %d.\n", cRowsDone);
/* Clean up. */
SQLFreeHandle(SQL_HANDLE_STMT, hstmt2);
SQLDisconnect(hdbc1);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
SQLDisconnect(hdbc2);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc2);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
return(0);
}
See Also
bcp_batch
bcp_init
bcp_bind
bcp_sendrow
bcp_done
Bulk Copying from Program Variables
"David" <dsh...@yahoo.com> wrote in message
news:OWjg1lbrBHA.2324@tkmsftngp03...
David
welcome.
David
P.S. Would it be better to post the code as an attachment?
________________________________________________________________
using System;
using System.Runtime.InteropServices;
using System.Text;
namespace BCP_ODBC_Lib
{
/// <summary>
/// Summary description for OdbcSSHeader.
/// </summary>
public class OdbcSSHeader
{
public OdbcSSHeader()
{
//
// TODO: make this a static constructor and then set fields that depend on
ODBC version herein
//
}
public const int NULL=0;
//from SqlTypes.h
public const int SQL_MAX_NUMERIC_LEN=16;
//from SqlExt.h
public const int SQL_TYPE_NULL=0;
public const int SQL_ATTR_ENLIST_IN_DTC = 1207;
public const int SQL_ATTR_ENLIST_IN_XA = 1208;
public const int SQL_DESC_BASE_COLUMN_NAME = 22;
#region OdbcSS.H contents
///*
//** ODBCSS.H - This is the application include file for the
//** SQL Server driver specific defines.
//**
//** (C) Copyright 1993-1998 By Microsoft Corp.
//**
//*/
//#ifndef __ODBCSS
//#define __ODBCSS
//#ifdef __cplusplus
//extern "C" { /* Assume C declarations for C++ */
//#endif /* __cplusplus */
// Useful defines
public const int SQL_MAX_SQLSERVERNAME = 128; // max SQL Server identifier
length
// SQLSetConnectOption/SQLSetStmtOption driver specific defines.
// Microsoft has 1200 thru 1249 reserved for Microsoft SQL Server driver
usage.
// Connection Options
public const int SQL_COPT_SS_BASE = 1200;
public const int SQL_COPT_SS_REMOTE_PWD = (SQL_COPT_SS_BASE+1); // dbrpwset
SQLSetConnectOption only
public const int SQL_COPT_SS_USE_PROC_FOR_PREP = (SQL_COPT_SS_BASE+2); //
Use create proc for SQLPrepare
public const int SQL_COPT_SS_INTEGRATED_SECURITY = (SQL_COPT_SS_BASE+3); //
Force integrated security on login
public const int SQL_COPT_SS_PRESERVE_CURSORS = (SQL_COPT_SS_BASE+4); //
Preserve server cursors after SQLTransact
public const int SQL_COPT_SS_USER_DATA = (SQL_COPT_SS_BASE+5); //
dbgetuserdata/dbsetuserdata
public const int SQL_COPT_SS_ENLIST_IN_DTC = SQL_ATTR_ENLIST_IN_DTC; //
Enlist in a DTC transaction
public const int SQL_COPT_SS_ENLIST_IN_XA = SQL_ATTR_ENLIST_IN_XA; // Enlist
in a XA transaction
//SQL_ATTR_CONNECTION_DEAD 1209 (It will return current connection status,
it will not go to server)
public const int SQL_COPT_SS_FALLBACK_CONNECT = (SQL_COPT_SS_BASE+10); //
Enables FallBack connections
public const int SQL_COPT_SS_PERF_DATA = (SQL_COPT_SS_BASE+11); // Used to
access SQL Server ODBC driver performance data
public const int SQL_COPT_SS_PERF_DATA_LOG = (SQL_COPT_SS_BASE+12); // Used
to set the logfile name for the Performance data
public const int SQL_COPT_SS_PERF_QUERY_INTERVAL = (SQL_COPT_SS_BASE+13); //
Used to set the query logging threshold in milliseconds.
public const int SQL_COPT_SS_PERF_QUERY_LOG = (SQL_COPT_SS_BASE+14); // Used
to set the logfile name for saving queryies.
public const int SQL_COPT_SS_PERF_QUERY = (SQL_COPT_SS_BASE+15); // Used to
start and stop query logging.
public const int SQL_COPT_SS_PERF_DATA_LOG_NOW = (SQL_COPT_SS_BASE+16); //
Used to make a statistics log entry to disk.
public const int SQL_COPT_SS_QUOTED_IDENT = (SQL_COPT_SS_BASE+17); //
Enable/Disable Quoted Identifiers
public const int SQL_COPT_SS_ANSI_NPW = (SQL_COPT_SS_BASE+18); //
Enable/Disable ANSI NULL, Padding and Warnings
public const int SQL_COPT_SS_BCP = (SQL_COPT_SS_BASE+19); // Allow BCP usage
on connection
public const int SQL_COPT_SS_TRANSLATE = (SQL_COPT_SS_BASE+20); // Perform
code page translation
public const int SQL_COPT_SS_ATTACHDBFILENAME = (SQL_COPT_SS_BASE+21); //
File name to be attached as a database
public const int SQL_COPT_SS_CONCAT_NULL = (SQL_COPT_SS_BASE+22); //
Enable/Disable CONCAT_NULL_YIELDS_NULL
public const int SQL_COPT_SS_ENCRYPT = (SQL_COPT_SS_BASE+23); // Allow
strong encryption for data
public const int SQL_COPT_SS_MAX_USED = SQL_COPT_SS_ENCRYPT;
// Statement Options
public const int SQL_SOPT_SS_BASE = 1225;
public const int SQL_SOPT_SS_TEXTPTR_LOGGING = (SQL_SOPT_SS_BASE+0); // Text
pointer logging
public const int SQL_SOPT_SS_CURRENT_COMMAND = (SQL_SOPT_SS_BASE+1); //
dbcurcmd SQLGetStmtOption only
public const int SQL_SOPT_SS_HIDDEN_COLUMNS = (SQL_SOPT_SS_BASE+2); //
Expose FOR BROWSE hidden columns
public const int SQL_SOPT_SS_NOBROWSETABLE = (SQL_SOPT_SS_BASE+3); // Set
NOBROWSETABLE option
public const int SQL_SOPT_SS_REGIONALIZE = (SQL_SOPT_SS_BASE+4); //
Regionalize output character conversions
public const int SQL_SOPT_SS_CURSOR_OPTIONS = (SQL_SOPT_SS_BASE+5); //
Server cursor options
public const int SQL_SOPT_SS_NOCOUNT_STATUS = (SQL_SOPT_SS_BASE+6); // Real
vs. Not Real row count indicator
public const int SQL_SOPT_SS_DEFER_PREPARE = (SQL_SOPT_SS_BASE+7); // Defer
prepare until necessary
public const int SQL_SOPT_SS_MAX_USED = SQL_SOPT_SS_DEFER_PREPARE;
public const int SQL_COPT_SS_BASE_EX = 1240;
public const int SQL_COPT_SS_BROWSE_CONNECT = (SQL_COPT_SS_BASE_EX+1); //
Browse connect mode of operation
public const int SQL_COPT_SS_BROWSE_SERVER = (SQL_COPT_SS_BASE_EX+2); //
Single Server browse request.
public const int SQL_COPT_SS_WARN_ON_CP_ERROR = (SQL_COPT_SS_BASE_EX+3); //
Issues warning when data from the server
// had a loss during code page conversion.
public const int SQL_COPT_SS_CONNECTION_DEAD = (SQL_COPT_SS_BASE_EX+4); //
dbdead SQLGetConnectOption only
// It will try to ping the server.
// Expensive connection check
public const int SQL_COPT_SS_BROWSE_CACHE_DATA = (SQL_COPT_SS_BASE_EX+5);
//Determines if we should cache browse info
//Used when returned buffer is greater then ODBC limit (32K)
public const int SQL_COPT_SS_RESET_CONNECTION = (SQL_COPT_SS_BASE_EX+6);
//When this option is set, we will perform connection reset
//on next packet
public const int SQL_COPT_SS_EX_MAX_USED = SQL_COPT_SS_RESET_CONNECTION;
// Defines for use with SQL_COPT_SS_USE_PROC_FOR_PREP
public const int SQL_UP_OFF = 0; //was :dL // Procedures won't be used for
prepare
public const int SQL_UP_ON = 1; //was :dL // Procedures will be used for
prepare
public const int SQL_UP_ON_DROP = 2; //was :dL // Temp procedures will be
explicitly dropped
public const int SQL_UP_DEFAULT = SQL_UP_ON;
// Defines for use with SQL_COPT_SS_INTEGRATED_SECURITY - Pre-Connect Option
only
public const int SQL_IS_OFF = 0; //was :dL // Integrated security isn't used
public const int SQL_IS_ON = 1; //was :dL // Integrated security is used
public const int SQL_IS_DEFAULT = SQL_IS_OFF;
// Defines for use with SQL_COPT_SS_PRESERVE_CURSORS
public const int SQL_PC_OFF = 0; //was :dL // Cursors are closed on
SQLTransact
public const int SQL_PC_ON = 1; //was :dL // Cursors remain open on
SQLTransact
public const int SQL_PC_DEFAULT = SQL_PC_OFF;
// Defines for use with SQL_COPT_SS_USER_DATA
public const int SQL_UD_NOTSET = NULL; // No user data pointer set
// Defines for use with SQL_COPT_SS_TRANSLATE
public const int SQL_XL_OFF = 0; //was :dL // Code page translation is not
performed
public const int SQL_XL_ON = 1; //was :dL // Code page translation is
performed
public const int SQL_XL_DEFAULT = SQL_XL_ON;
// Defines for use with SQL_COPT_SS_FALLBACK_CONNECT - Pre-Connect Option
only
public const int SQL_FB_OFF = 0; //was :dL // FallBack connections are
disabled
public const int SQL_FB_ON = 1; //was :dL // FallBack connections are
enabled
public const int SQL_FB_DEFAULT = SQL_FB_OFF;
// Defines for use with SQL_COPT_SS_BCP - Pre-Connect Option only
public const int SQL_BCP_OFF = 0; //was :dL // BCP is not allowed on
connection
public const int SQL_BCP_ON = 1; //was :dL // BCP is allowed on connection
public const int SQL_BCP_DEFAULT = SQL_BCP_OFF;
// Defines for use with SQL_COPT_SS_QUOTED_IDENT
public const int SQL_QI_OFF = 0; //was :dL // Quoted identifiers are enable
public const int SQL_QI_ON = 1; //was :dL // Quoted identifiers are disabled
public const int SQL_QI_DEFAULT = SQL_QI_ON;
// Defines for use with SQL_COPT_SS_ANSI_NPW - Pre-Connect Option only
public const int SQL_AD_OFF = 0; //was :dL // ANSI NULLs, Padding and
Warnings are enabled
public const int SQL_AD_ON = 1; //was :dL // ANSI NULLs, Padding and
Warnings are disabled
public const int SQL_AD_DEFAULT = SQL_AD_ON;
// Defines for use with SQL_COPT_SS_CONCAT_NULL - Pre-Connect Option only
public const int SQL_CN_OFF = 0; //was :dL // CONCAT_NULL_YIELDS_NULL is off
public const int SQL_CN_ON = 1; //was :dL // CONCAT_NULL_YIELDS_NULL is on
public const int SQL_CN_DEFAULT = SQL_CN_ON;
// Defines for use with SQL_SOPT_SS_TEXTPTR_LOGGING
public const int SQL_TL_OFF = 0; //was :dL // No logging on text pointer ops
public const int SQL_TL_ON = 1; //was :dL // Logging occurs on text pointer
ops
public const int SQL_TL_DEFAULT = SQL_TL_ON;
// Defines for use with SQL_SOPT_SS_HIDDEN_COLUMNS
public const int SQL_HC_OFF = 0; //was :dL // FOR BROWSE columns are hidden
public const int SQL_HC_ON = 1; //was :dL // FOR BROWSE columns are exposed
public const int SQL_HC_DEFAULT = SQL_HC_OFF;
// Defines for use with SQL_SOPT_SS_NOBROWSETABLE
public const int SQL_NB_OFF = 0; //was :dL // NO_BROWSETABLE is off
public const int SQL_NB_ON = 1; //was :dL // NO_BROWSETABLE is on
public const int SQL_NB_DEFAULT = SQL_NB_OFF;
// Defines for use with SQL_SOPT_SS_REGIONALIZE
public const int SQL_RE_OFF = 0; //was :dL // No regionalization occurs on
output character conversions
public const int SQL_RE_ON = 1; //was :dL // Regionalization occurs on
output character conversions
public const int SQL_RE_DEFAULT = SQL_RE_OFF;
// Defines for use with SQL_SOPT_SS_CURSOR_OPTIONS
public const int SQL_CO_OFF = 0; //was :dL // Clear all cursor options
public const int SQL_CO_FFO = 1; //was :dL // Fast-forward cursor will be
used
public const int SQL_CO_AF = 2; //was :dL // Autofetch on cursor open
public const int SQL_CO_FFO_AF = (SQL_CO_FFO|SQL_CO_AF); // Fast-forward
cursor with autofetch
public const int SQL_CO_FIREHOSE_AF = 4; //was :dL // Auto fetch on
fire-hose cursors
public const int SQL_CO_DEFAULT = SQL_CO_OFF;
//SQL_SOPT_SS_NOCOUNT_STATUS
public const int SQL_NC_OFF = 0; //was :dL
public const int SQL_NC_ON = 1; //was :dL
//SQL_SOPT_SS_DEFER_PREPARE
public const int SQL_DP_OFF = 0; //was :dL
public const int SQL_DP_ON = 1; //was :dL
//SQL_COPT_SS_ENCRYPT
public const int SQL_EN_OFF = 0; //was :dL
public const int SQL_EN_ON = 1; //was :dL
//SQL_COPT_SS_BROWSE_CONNECT
public const int SQL_MORE_INFO_NO = 0; //was :dL
public const int SQL_MORE_INFO_YES = 1; //was :dL
//SQL_COPT_SS_BROWSE_CACHE_DATA
public const int SQL_CACHE_DATA_NO = 0; //was :dL
public const int SQL_CACHE_DATA_YES = 1; //was :dL
//SQL_COPT_SS_RESET_CONNECTION
public const int SQL_RESET_YES = 1; //was :dL
//SQL_COPT_SS_WARN_ON_CP_ERROR
public const int SQL_WARN_NO = 0; //was :dL
public const int SQL_WARN_YES = 1; //was :dL
// Defines returned by SQL_ATTR_CURSOR_TYPE/SQL_CURSOR_TYPE
public const int SQL_CURSOR_FAST_FORWARD_ONLY = 8; // Only returned by
SQLGetStmtAttr/Option
// SQLColAttributes driver specific defines.
// SQLSet/GetDescField driver specific defines.
// Microsoft has 1200 thru 1249 reserved for Microsoft SQL Server driver
usage.
public const int SQL_CA_SS_BASE = 1200;
public const int SQL_CA_SS_COLUMN_SSTYPE = (SQL_CA_SS_BASE+0); //
dbcoltype/dbalttype
public const int SQL_CA_SS_COLUMN_UTYPE = (SQL_CA_SS_BASE+1); //
dbcolutype/dbaltutype
public const int SQL_CA_SS_NUM_ORDERS = (SQL_CA_SS_BASE+2); // dbnumorders
public const int SQL_CA_SS_COLUMN_ORDER = (SQL_CA_SS_BASE+3); // dbordercol
public const int SQL_CA_SS_COLUMN_VARYLEN = (SQL_CA_SS_BASE+4); // dbvarylen
public const int SQL_CA_SS_NUM_COMPUTES = (SQL_CA_SS_BASE+5); //
dbnumcompute
public const int SQL_CA_SS_COMPUTE_ID = (SQL_CA_SS_BASE+6); // dbnextrow
status return
public const int SQL_CA_SS_COMPUTE_BYLIST = (SQL_CA_SS_BASE+7); // dbbylist
public const int SQL_CA_SS_COLUMN_ID = (SQL_CA_SS_BASE+8); // dbaltcolid
public const int SQL_CA_SS_COLUMN_OP = (SQL_CA_SS_BASE+9); // dbaltop
public const int SQL_CA_SS_COLUMN_SIZE = (SQL_CA_SS_BASE+10); // dbcollen
public const int SQL_CA_SS_COLUMN_HIDDEN = (SQL_CA_SS_BASE+11); // Column is
hidden (FOR BROWSE)
public const int SQL_CA_SS_COLUMN_KEY = (SQL_CA_SS_BASE+12); // Column is
key column (FOR BROWSE)
//#define SQL_DESC_BASE_COLUMN_NAME_OLD (SQL_CA_SS_BASE+13) //This is
defined at another location.
public const int SQL_CA_SS_COLUMN_COLLATION = (SQL_CA_SS_BASE+14); // Column
collation (only for chars)
public const int SQL_CA_SS_VARIANT_TYPE = (SQL_CA_SS_BASE+15);
public const int SQL_CA_SS_VARIANT_SQL_TYPE = (SQL_CA_SS_BASE+16);
public const int SQL_CA_SS_VARIANT_SERVER_TYPE = (SQL_CA_SS_BASE+17);
public const int SQL_CA_SS_MAX_USED = (SQL_CA_SS_BASE+18);
// SQL Server Data Type Tokens.
// New types for 6.0 and later servers
///* SQL Server Data Type Tokens. */
public const int SQLTEXT = 0x23;
public const int SQLVARBINARY = 0x25;
public const int SQLINTN = 0x26;
public const int SQLVARCHAR = 0x27;
public const int SQLBINARY = 0x2d;
public const int SQLIMAGE = 0x22;
public const int SQLCHARACTER = 0x2f;
public const int SQLINT1 = 0x30;
public const int SQLBIT = 0x32;
public const int SQLINT2 = 0x34;
public const int SQLINT4 = 0x38;
public const int SQLMONEY = 0x3c;
public const int SQLDATETIME = 0x3d;
public const int SQLFLT8 = 0x3e;
public const int SQLFLTN = 0x6d;
public const int SQLMONEYN = 0x6e;
public const int SQLDATETIMN = 0x6f;
public const int SQLFLT4 = 0x3b;
public const int SQLMONEY4 = 0x7a;
public const int SQLDATETIM4 = 0x3a;
// New types for 6.0 and later servers
public const int SQLDECIMAL = 0x6a;
public const int SQLNUMERIC = 0x6c;
// New types for 7.0 and later servers
public const int SQLUNIQUEID = 0x24;
public const int SQLBIGCHAR = 0xaf;
public const int SQLBIGVARCHAR = 0xa7;
public const int SQLBIGBINARY = 0xad;
public const int SQLBIGVARBINARY = 0xa5;
public const int SQLBITN = 0x68;
public const int SQLNCHAR = 0xef;
public const int SQLNVARCHAR = 0xe7;
public const int SQLNTEXT = 0x63;
// New for 7.x
public const int SQLINT8 = 0x7f;
public const int SQLVARIANT = 0x62;
// User Data Type definitions.
// Returned by SQLColAttributes/SQL_CA_SS_COLUMN_UTYPE.
public const int SQLudtBINARY = 3;
public const int SQLudtBIT = 16;
public const int SQLudtBITN = 0;
public const int SQLudtCHAR = 1;
public const int SQLudtDATETIM4 = 22;
public const int SQLudtDATETIME = 12;
public const int SQLudtDATETIMN = 15;
public const int SQLudtDECML = 24;
public const int SQLudtDECMLN = 26;
public const int SQLudtFLT4 = 23;
public const int SQLudtFLT8 = 8;
public const int SQLudtFLTN = 14;
public const int SQLudtIMAGE = 20;
public const int SQLudtINT1 = 5;
public const int SQLudtINT2 = 6;
public const int SQLudtINT4 = 7;
public const int SQLudtINTN = 13;
public const int SQLudtMONEY = 11;
public const int SQLudtMONEY4 = 21;
public const int SQLudtMONEYN = 17;
public const int SQLudtNUM = 10;
public const int SQLudtNUMN = 25;
public const int SQLudtSYSNAME = 18;
public const int SQLudtTEXT = 19;
public const int SQLudtTIMESTAMP = 80;
public const int SQLudtUNIQUEIDENTIFIER = 0;
public const int SQLudtVARBINARY = 4;
public const int SQLudtVARCHAR = 2;
public const int MIN_USER_DATATYPE = 256;
// Aggregate operator types.
// Returned by SQLColAttributes/SQL_CA_SS_COLUMN_OP.
public const int SQLAOPSTDEV = 0x30; // Standard deviation
public const int SQLAOPSTDEVP = 0x31; // Standard deviation population
public const int SQLAOPVAR = 0x32; // Variance
public const int SQLAOPVARP = 0x33; // Variance population
public const int SQLAOPCNT = 0x4b; // Count
public const int SQLAOPSUM = 0x4d; // Sum
public const int SQLAOPAVG = 0x4f; // Average
public const int SQLAOPMIN = 0x51; // Min
public const int SQLAOPMAX = 0x52; // Max
public const int SQLAOPANY = 0x53; // Any
public const int SQLAOPNOOP = 0x56; // None
// SQLGetInfo driver specific defines.
// Microsoft has 1151 thru 1200 reserved for Microsoft SQL Server driver
usage.
public const int SQL_INFO_SS_FIRST = 1199;
public const int SQL_INFO_SS_NETLIB_NAMEW = (SQL_INFO_SS_FIRST+0); //
dbprocinfo
public const int SQL_INFO_SS_NETLIB_NAMEA = (SQL_INFO_SS_FIRST+1); //
dbprocinfo
public const int SQL_INFO_SS_MAX_USED = SQL_INFO_SS_NETLIB_NAMEA;
//#ifdef UNICODE
public const int SQL_INFO_SS_NETLIB_NAME = SQL_INFO_SS_NETLIB_NAMEW;
//#else
//public const int SQL_INFO_SS_NETLIB_NAME = SQL_INFO_SS_NETLIB_NAMEA;
//#endif
// Driver specific SQL type defines.
// Microsoft has -150 thru -199 reserved for Microsoft SQL Server driver
usage.
public const int SQL_SS_VARIANT = -150;
// SQLGetDiagField driver specific defines.
// Microsoft has -1150 thru -1199 reserved for Microsoft SQL Server driver
usage.
public const int SQL_DIAG_SS_BASE = (-1150);
public const int SQL_DIAG_SS_MSGSTATE = (SQL_DIAG_SS_BASE);
public const int SQL_DIAG_SS_SEVERITY = (SQL_DIAG_SS_BASE-1);
public const int SQL_DIAG_SS_SRVNAME = (SQL_DIAG_SS_BASE-2);
public const int SQL_DIAG_SS_PROCNAME = (SQL_DIAG_SS_BASE-3);
public const int SQL_DIAG_SS_LINE = (SQL_DIAG_SS_BASE-4);
// SQLGetDiagField/SQL_DIAG_DYNAMIC_FUNCTION_CODE driver specific defines.
// Microsoft has -200 thru -299 reserved for Microsoft SQL Server driver
usage.
public const int SQL_DIAG_DFC_SS_BASE = (-200);
public const int SQL_DIAG_DFC_SS_ALTER_DATABASE = (SQL_DIAG_DFC_SS_BASE-0);
public const int SQL_DIAG_DFC_SS_CHECKPOINT = (SQL_DIAG_DFC_SS_BASE-1);
public const int SQL_DIAG_DFC_SS_CONDITION = (SQL_DIAG_DFC_SS_BASE-2);
public const int SQL_DIAG_DFC_SS_CREATE_DATABASE = (SQL_DIAG_DFC_SS_BASE-3);
public const int SQL_DIAG_DFC_SS_CREATE_DEFAULT = (SQL_DIAG_DFC_SS_BASE-4);
public const int SQL_DIAG_DFC_SS_CREATE_PROCEDURE =
(SQL_DIAG_DFC_SS_BASE-5);
public const int SQL_DIAG_DFC_SS_CREATE_RULE = (SQL_DIAG_DFC_SS_BASE-6);
public const int SQL_DIAG_DFC_SS_CREATE_TRIGGER = (SQL_DIAG_DFC_SS_BASE-7);
public const int SQL_DIAG_DFC_SS_CURSOR_DECLARE = (SQL_DIAG_DFC_SS_BASE-8);
public const int SQL_DIAG_DFC_SS_CURSOR_OPEN = (SQL_DIAG_DFC_SS_BASE-9);
public const int SQL_DIAG_DFC_SS_CURSOR_FETCH = (SQL_DIAG_DFC_SS_BASE-10);
public const int SQL_DIAG_DFC_SS_CURSOR_CLOSE = (SQL_DIAG_DFC_SS_BASE-11);
public const int SQL_DIAG_DFC_SS_DEALLOCATE_CURSOR =
(SQL_DIAG_DFC_SS_BASE-12);
public const int SQL_DIAG_DFC_SS_DBCC = (SQL_DIAG_DFC_SS_BASE-13);
public const int SQL_DIAG_DFC_SS_DISK = (SQL_DIAG_DFC_SS_BASE-14);
public const int SQL_DIAG_DFC_SS_DROP_DATABASE = (SQL_DIAG_DFC_SS_BASE-15);
public const int SQL_DIAG_DFC_SS_DROP_DEFAULT = (SQL_DIAG_DFC_SS_BASE-16);
public const int SQL_DIAG_DFC_SS_DROP_PROCEDURE = (SQL_DIAG_DFC_SS_BASE-17);
public const int SQL_DIAG_DFC_SS_DROP_RULE = (SQL_DIAG_DFC_SS_BASE-18);
public const int SQL_DIAG_DFC_SS_DROP_TRIGGER = (SQL_DIAG_DFC_SS_BASE-19);
public const int SQL_DIAG_DFC_SS_DUMP_DATABASE = (SQL_DIAG_DFC_SS_BASE-20);
public const int SQL_DIAG_DFC_SS_DUMP_TABLE = (SQL_DIAG_DFC_SS_BASE-21);
public const int SQL_DIAG_DFC_SS_DUMP_TRANSACTION =
(SQL_DIAG_DFC_SS_BASE-22);
public const int SQL_DIAG_DFC_SS_GOTO = (SQL_DIAG_DFC_SS_BASE-23);
public const int SQL_DIAG_DFC_SS_INSERT_BULK = (SQL_DIAG_DFC_SS_BASE-24);
public const int SQL_DIAG_DFC_SS_KILL = (SQL_DIAG_DFC_SS_BASE-25);
public const int SQL_DIAG_DFC_SS_LOAD_DATABASE = (SQL_DIAG_DFC_SS_BASE-26);
public const int SQL_DIAG_DFC_SS_LOAD_HEADERONLY =
(SQL_DIAG_DFC_SS_BASE-27);
public const int SQL_DIAG_DFC_SS_LOAD_TABLE = (SQL_DIAG_DFC_SS_BASE-28);
public const int SQL_DIAG_DFC_SS_LOAD_TRANSACTION =
(SQL_DIAG_DFC_SS_BASE-29);
public const int SQL_DIAG_DFC_SS_PRINT = (SQL_DIAG_DFC_SS_BASE-30);
public const int SQL_DIAG_DFC_SS_RAISERROR = (SQL_DIAG_DFC_SS_BASE-31);
public const int SQL_DIAG_DFC_SS_READTEXT = (SQL_DIAG_DFC_SS_BASE-32);
public const int SQL_DIAG_DFC_SS_RECONFIGURE = (SQL_DIAG_DFC_SS_BASE-33);
public const int SQL_DIAG_DFC_SS_RETURN = (SQL_DIAG_DFC_SS_BASE-34);
public const int SQL_DIAG_DFC_SS_SELECT_INTO = (SQL_DIAG_DFC_SS_BASE-35);
public const int SQL_DIAG_DFC_SS_SET = (SQL_DIAG_DFC_SS_BASE-36);
public const int SQL_DIAG_DFC_SS_SET_IDENTITY_INSERT =
(SQL_DIAG_DFC_SS_BASE-37);
public const int SQL_DIAG_DFC_SS_SET_ROW_COUNT = (SQL_DIAG_DFC_SS_BASE-38);
public const int SQL_DIAG_DFC_SS_SET_STATISTICS = (SQL_DIAG_DFC_SS_BASE-39);
public const int SQL_DIAG_DFC_SS_SET_TEXTSIZE = (SQL_DIAG_DFC_SS_BASE-40);
public const int SQL_DIAG_DFC_SS_SETUSER = (SQL_DIAG_DFC_SS_BASE-41);
public const int SQL_DIAG_DFC_SS_SHUTDOWN = (SQL_DIAG_DFC_SS_BASE-42);
public const int SQL_DIAG_DFC_SS_TRANS_BEGIN = (SQL_DIAG_DFC_SS_BASE-43);
public const int SQL_DIAG_DFC_SS_TRANS_COMMIT = (SQL_DIAG_DFC_SS_BASE-44);
public const int SQL_DIAG_DFC_SS_TRANS_PREPARE = (SQL_DIAG_DFC_SS_BASE-45);
public const int SQL_DIAG_DFC_SS_TRANS_ROLLBACK = (SQL_DIAG_DFC_SS_BASE-46);
public const int SQL_DIAG_DFC_SS_TRANS_SAVE = (SQL_DIAG_DFC_SS_BASE-47);
public const int SQL_DIAG_DFC_SS_TRUNCATE_TABLE = (SQL_DIAG_DFC_SS_BASE-48);
public const int SQL_DIAG_DFC_SS_UPDATE_STATISTICS =
(SQL_DIAG_DFC_SS_BASE-49);
public const int SQL_DIAG_DFC_SS_UPDATETEXT = (SQL_DIAG_DFC_SS_BASE-50);
public const int SQL_DIAG_DFC_SS_USE = (SQL_DIAG_DFC_SS_BASE-51);
public const int SQL_DIAG_DFC_SS_WAITFOR = (SQL_DIAG_DFC_SS_BASE-52);
public const int SQL_DIAG_DFC_SS_WRITETEXT = (SQL_DIAG_DFC_SS_BASE-53);
public const int SQL_DIAG_DFC_SS_DENY = (SQL_DIAG_DFC_SS_BASE-54);
public const int SQL_DIAG_DFC_SS_SET_XCTLVL = (SQL_DIAG_DFC_SS_BASE-55);
// Severity codes for SQL_DIAG_SS_SEVERITY
public const int EX_ANY = 0;
public const int EX_INFO = 10;
public const int EX_MAXISEVERITY = EX_INFO;
public const int EX_MISSING = 11;
public const int EX_TYPE = 12;
public const int EX_DEADLOCK = 13;
public const int EX_PERMIT = 14;
public const int EX_SYNTAX = 15;
public const int EX_USER = 16;
public const int EX_RESOURCE = 17;
public const int EX_INTOK = 18;
public const int MAXUSEVERITY = EX_INTOK;
public const int EX_LIMIT = 19;
public const int EX_CMDFATAL = 20;
public const int MINFATALERR = EX_CMDFATAL;
public const int EX_DBFATAL = 21;
public const int EX_TABCORRUPT = 22;
public const int EX_DBCORRUPT = 23;
public const int EX_HARDWARE = 24;
public const int EX_CONTROL = 25;
// Internal server datatypes - used when binding to SQL_C_BINARY
//#ifndef MAXNUMERICLEN // Resolve ODS/DBLib conflicts
// DB-Library datatypes
public const int DBMAXCHAR = (8000+1); // Max length of DBVARBINARY and
DBVARCHAR, etc. +1 for zero byte
public const int MAXNAME = (SQL_MAX_SQLSERVERNAME+1); // Max server
identifier length including zero byte
//#ifdef UNICODE
//typedef wchar_t DBCHAR;
//#else
//typedef char DBCHAR;
//#endif
//typedef unsigned char DBBINARY;
//typedef unsigned char DBTINYINT;
//typedef short DBSMALLINT;
//typedef unsigned short DBUSMALLINT;
//typedef double DBFLT8;
//typedef unsigned char DBBIT;
//typedef unsigned char DBBOOL;
//typedef float DBFLT4;
//typedef DBFLT4 DBREAL;
//typedef UINT DBUBOOL;
//typedef struct dbvarychar
//{
// DBSMALLINT len;
// DBCHAR str[DBMAXCHAR];
//} DBVARYCHAR;
//typedef struct dbvarybin
//{
// DBSMALLINT len;
// BYTE array[DBMAXCHAR];
//} DBVARYBIN;
//typedef struct dbmoney
//{ // Internal representation of MONEY data type
// LONG mnyhigh; // Money value *10,000 (High 32 bits/signed)
// ULONG mnylow; // Money value *10,000 (Low 32 bits/unsigned)
//} DBMONEY;
//typedef struct dbdatetime
//{ // Internal representation of DATETIME data type
// LONG dtdays; // No of days since Jan-1-1900 (maybe negative)
// ULONG dttime; // No. of 300 hundredths of a second since midnight
//} DBDATETIME;
//typedef struct dbdatetime4
//{ // Internal representation of SMALLDATETIME data type
// USHORT numdays; // No of days since Jan-1-1900
// USHORT nummins; // No. of minutes since midnight
//} DBDATETIM4;
//typedef LONG DBMONEY4; // Internal representation of SMALLMONEY data type
// Money value *10,000
//typedef DBNUM_PREC_TYPE = BYTE;
//typedef DBNUM_SCALE_TYPE = BYTE;
//typedef DBNUM_VAL_TYPE = BYTE;
//#if (ODBCVER < 0x0300)
//public const int MAXNUMERICLEN = 16;
//typedef struct dbnumeric
//{ // Internal representation of NUMERIC data type
// DBNUM_PREC_TYPE precision; // Precision
// DBNUM_SCALE_TYPE scale; // Scale
// BYTE sign; // Sign (1 if positive, 0 if negative)
// DBNUM_VAL_TYPE val[MAXNUMERICLEN]; // Value
//} DBNUMERIC;
//typedef DBNUMERIC DBDECIMAL;// Internal representation of DECIMAL data
type
//#else // Use ODBC 3.0 definitions since same as DBLib
public const int MAXNUMERICLEN = SQL_MAX_NUMERIC_LEN;
//typedef SQL_NUMERIC_STRUCT DBNUMERIC;
//typedef SQL_NUMERIC_STRUCT DBDECIMAL;
//#endif
//#endif // MAXNUMERICLEN
//#ifndef System.Int32 i
//typedef int System.Int32 i;
//typedef long System.Int64 dbint;
//#ifndef _LPCBYTE_DEFINED
//#define _LPCBYTE_DEFINED
//typedef const LPBYTE byte lpcbyte;
//#endif
//typedef System.Int64 dbint * LPDBINT;
//#endif
///*****************************************************************
// This struct is a global used for
// gathering statistical data on the driver.
// Access to this structure is controlled via the
// pStatCrit;
//******************************************************************/
//typedef struct sqlperf
//{
// Application Profile Statistics
// DWORD TimerResolution;
// DWORD SQLidu;
// DWORD SQLiduRows;
// DWORD SQLSelects;
// DWORD SQLSelectRows;
// DWORD Transactions;
// DWORD SQLPrepares;
// DWORD ExecDirects;
// DWORD SQLExecutes;
// DWORD CursorOpens;
// DWORD CursorSize;
// DWORD CursorUsed;
// LDOUBLE PercentCursorUsed;
// LDOUBLE AvgFetchTime;
// LDOUBLE AvgCursorSize;
// LDOUBLE AvgCursorUsed;
// DWORD SQLFetchTime;
// DWORD SQLFetchCount;
// DWORD CurrentStmtCount;
// DWORD MaxOpenStmt;
// DWORD SumOpenStmt;
// Connection Statistics
// DWORD CurrentConnectionCount;
// DWORD MaxConnectionsOpened;
// DWORD SumConnectionsOpened;
// DWORD SumConnectiontime;
// LDOUBLE AvgTimeOpened;
// Network Statistics
// DWORD ServerRndTrips;
// DWORD BuffersSent;
// DWORD BuffersRec;
// DWORD BytesSent;
// DWORD BytesRec;
// Time Statistics;
// DWORD msExecutionTime;
// DWORD msNetWorkServerTime;
//} SQLPERF;
// The following are options for SQL_COPT_SS_PERF_DATA and
SQL_COPT_SS_PERF_QUERY
public const int SQL_PERF_START = 1; // Starts the driver sampling
performance data.
public const int SQL_PERF_STOP = 2; // Stops the counters from sampling
performance data.
// The following are defines for SQL_COPT_SS_PERF_DATA_LOG
//public const int SQL_SS_DL_DEFAULT = TEXT("C:\\STATS.LOG");
// The following are defines for SQL_COPT_SS_PERF_QUERY_LOG
//public const int SQL_SS_QL_DEFAULT = TEXT("C:\\QUERY.LOG");
// The following are defines for SQL_COPT_SS_PERF_QUERY_INTERVAL
public const int SQL_SS_QI_DEFAULT = 30000; // 30,000 milliseconds
// ODBC BCP prototypes and defines
// Return codes
public const int SUCCEED = 1;
public const int FAIL = 0;
public const int SUCCEED_ABORT = 2;
public const int SUCCEED_ASYNC = 3;
// Transfer directions
public const int DB_IN = 1; // Transfer from client to server
public const int DB_OUT = 2; // Transfer from server to client
// bcp_control option
public const int BCPMAXERRS = 1; // Sets max errors allowed
public const int BCPFIRST = 2; // Sets first row to be copied out
public const int BCPLAST = 3; // Sets number of rows to be copied out
public const int BCPBATCH = 4; // Sets input batch size
public const int BCPKEEPNULLS = 5; // Sets to insert NULLs for empty input
values
public const int BCPABORT = 6; // Sets to have bcpexec return SUCCEED_ABORT
public const int BCPODBC = 7; // Sets ODBC canonical character output
public const int BCPKEEPIDENTITY = 8; // Sets IDENTITY_INSERT on
public const int BCP6xFILEFMT = 9; // DEPRECATED: Sets 6x file format on
public const int BCPHINTSA = 10; // Sets server BCP hints (ANSI string)
public const int BCPHINTSW = 11; // Sets server BCP hints (UNICODE string)
public const int BCPFILECP = 12; // Sets clients code page for the file
public const int BCPUNICODEFILE = 13; // Sets that the file contains unicode
header
public const int BCPTEXTFILE = 14; // Sets BCP mode to expect a text file
and to detect Unicode or ANSI automatically
public const int BCPFILEFMT = 15; // Sets file format version
// BCPFILECP values
// Any valid code page that is installed on the client can be passed plus:
public const int BCPFILECP_ACP = 0; // Data in file is in Windows code page
public const int BCPFILECP_OEMCP = 1; // Data in file is in OEM code page
(default)
public const int BCPFILECP_RAW = (-1);// Data in file is in Server code page
(no conversion)
// bcp_collen definition
public const int SQL_VARLEN_DATA = (-10); // Use default length for column
// BCP functions
[DllImport("Odbcbcp.lib")]
public static extern System.Int32 /*SQL_API*/ bcp_batch (System.Object
hdbc);
[DllImport("Odbcbcp.lib")]
public static extern System.Int16 /*SQL_API*/ bcp_bind ( System.Object hdbc,
byte lpcbyte1, System.Int32 i1, System.Int64 dbint, byte lpcbyte2,
System.Int32 i2, System.Int32 i3, System.Int32 i4);
[DllImport("Odbcbcp.lib")]
public static extern System.Int16 /*SQL_API*/ bcp_colfmt ( System.Object
hdbc, System.Int32 i1, byte b, System.Int32 i2, System.Int64 dbint, byte
lpcbyte, System.Int32 i3, System.Int32 i4);
[DllImport("Odbcbcp.lib")]
public static extern System.Int16 /*SQL_API*/ bcp_collen ( System.Object
hdbc, System.Int64 dbint, System.Int32 i);
[DllImport("Odbcbcp.lib")]
public static extern System.Int16 /*SQL_API*/ bcp_colptr ( System.Object
hdbc, byte lpcbyte, System.Int32 i);
[DllImport("Odbcbcp.lib")]
public static extern System.Int16 /*SQL_API*/ bcp_columns ( System.Object
hdbc, System.Int32 i);
[DllImport("Odbcbcp.lib")]
public static extern System.Int16 /*SQL_API*/ bcp_control ( System.Object
hdbc, System.Int32 i, object vptr);
[DllImport("Odbcbcp.lib")]
public static extern System.Int64 /*SQL_API*/ bcp_done ( System.Object
hdbc);
[DllImport("Odbcbcp.lib")]
public static extern System.Int16 /*SQL_API*/ bcp_exec ( System.Object hdbc,
System.Int64 lpdbint);
[DllImport("Odbcbcp.lib")]
public static extern System.Int16 /*SQL_API*/ bcp_getcolfmt ( System.Object
hdbc, System.Int32 i1, System.Int32 i2, object vptr, System.Int32 i3,
System.Int32 iptr);
[DllImport("Odbcbcp.lib")]
public static extern System.Int16 /*SQL_API*/ bcp_initA ( System.Object
hdbc, string s1, string s2, string s3, System.Int32 i);
[DllImport("Odbcbcp.lib")]
public static extern System.Int16 /*SQL_API*/ bcp_initW ( System.Object
hdbc, string s1, string s2, string s3, System.Int32 i);
[DllImport("Odbcbcp.lib")]
public static extern System.Int16 /*SQL_API*/ bcp_moretext ( System.Object
hdbc, System.Int64 dbint, byte lpcbyte);
[DllImport("Odbcbcp.lib")]
public static extern System.Int16 /*SQL_API*/ bcp_readfmtA ( System.Object
hdbc, string s);
[DllImport("Odbcbcp.lib")]
public static extern System.Int16 /*SQL_API*/ bcp_readfmtW ( System.Object
hdbc, string s);
[DllImport("Odbcbcp.lib")]
public static extern System.Int16 /*SQL_API*/ bcp_sendrow ( System.Object
hdbc);
[DllImport("Odbcbcp.lib")]
public static extern System.Int16 /*SQL_API*/ bcp_setcolfmt ( System.Object
hdbc, System.Int32 i1, System.Int32 i2, object vptr, System.Int32 i3);
[DllImport("Odbcbcp.lib")]
public static extern System.Int16 /*SQL_API*/ bcp_writefmtA ( System.Object
hdbc, string s);
[DllImport("Odbcbcp.lib")]
public static extern System.Int16 /*SQL_API*/ bcp_writefmtW ( System.Object
hdbc, string s);
[DllImport("Odbcbcp.lib")]
public static extern System.Text.StringBuilder /*SQL_API*/ dbprtypeA
(System.Int32 i);
[DllImport("Odbcbcp.lib")]
public static extern StringBuilder /*SQL_API*/ dbprtypeW (System.Int32 i);
//#ifdef UNICODE
//typedef bcp_init bcp_initW
//typedef bcp_readfmt bcp_readfmtW
//typedef bcp_writefmt bcp_writefmtW
//typedef dbprtype dbprtypeW
//typedef BCPHINTS BCPHINTSW
//#else
//typedef bcp_init bcp_initA
//typedef bcp_readfmt bcp_readfmtA
//typedef bcp_writefmt bcp_writefmtA
//typedef dbprtype dbprtypeA
//typedef BCPHINTS BCPHINTSA
//#endif
// SQL Server catalog extensions for distributed queries
[DllImport("Odbcbcp.lib")]
public static extern System.Int16 /*SQL_API*/ SQLLinkedServers
(System.Object sqlhstmt);
[DllImport("Odbcbcp.lib")]
public static extern System.Int16 /*SQL_API*/ SQLLinkedCatalogsA
(System.Object sqlhstmt, string s, System.Char w);
[DllImport("Odbcbcp.lib")]
public static extern System.Int16 /*SQL_API*/ SQLLinkedCatalogsW
(System.Object sqlhstmt, string s, System.Char w);
// SQL Server extensions for server enumeration
[DllImport("Odbcbcp.lib")]
public static extern System.Object /*SQL_API*/ SQLInitEnumServers
(StringBuilder pwchServerName,StringBuilder pwchInstanceName);
[DllImport("Odbcbcp.lib")]
public static extern System.Int16 /*SQL_API*/ SQLGetNextEnumeration
(System.Object hEnumHandle, byte ptrgEnumData, System.Int32 ptriEnumLength);
[DllImport("Odbcbcp.lib")]
public static extern System.Int16 /*SQL_API*/ SQLCloseEnumServers
(System.Object hEnumHandle);
//#ifdef UNICODE
//typedef SQLLinkedCatalogs = SQLLinkedCatalogsW;
//#else
//typedef SQLLinkedCatalogs = SQLLinkedCatalogsA;
//#endif
// BCP column format properties
public const int BCP_FMT_TYPE = 0x01;
public const int BCP_FMT_INDICATOR_LEN = 0x02;
public const int BCP_FMT_DATA_LEN = 0x03;
public const int BCP_FMT_TERMINATOR = 0x04;
public const int BCP_FMT_SERVER_COL = 0x05;
public const int BCP_FMT_COLLATION = 0x06;
public const int BCP_FMT_COLLATION_ID = 0x07;
// The following options have been deprecated
public const int SQL_FAST_CONNECT = (SQL_COPT_SS_BASE+0);
// Defines for use with SQL_FAST_CONNECT - only useable before connecting
public const int SQL_FC_OFF = 0; //was :dL // Fast connect is off
public const int SQL_FC_ON = 1; //was :dL // Fast connect is on
public const int SQL_FC_DEFAULT = SQL_FC_OFF;
public const int SQL_COPT_SS_ANSI_OEM = (SQL_COPT_SS_BASE+6);
public const int SQL_AO_OFF = 0; //was :dL
public const int SQL_AO_ON = 1; //was :dL
public const int SQL_AO_DEFAULT = SQL_AO_OFF;
// Define old names
public const int SQL_REMOTE_PWD = SQL_COPT_SS_REMOTE_PWD;
public const int SQL_USE_PROCEDURE_FOR_PREPARE =
SQL_COPT_SS_USE_PROC_FOR_PREP;
public const int SQL_INTEGRATED_SECURITY = SQL_COPT_SS_INTEGRATED_SECURITY;
public const int SQL_PRESERVE_CURSORS = SQL_COPT_SS_PRESERVE_CURSORS;
public const int SQL_TEXTPTR_LOGGING = SQL_SOPT_SS_TEXTPTR_LOGGING;
public const int SQL_CA_SS_BASE_COLUMN_NAME = SQL_DESC_BASE_COLUMN_NAME;
public const int SQLDECIMALN = 0x6a;
public const int SQLNUMERICN = 0x6c;
//#ifdef __cplusplus
//} /* End of extern "C" { */
//#endif /* __cplusplus */
//#endif
// End of odbcss.h
//
#endregion
}
struct DBNUMERIC
{ // Internal representation of NUMERIC data type
//typedef DBNUM_PREC_TYPE = BYTE;
//typedef DBNUM_SCALE_TYPE = BYTE;
//typedef DBNUM_VAL_TYPE = BYTE;
byte precision; // Precision
byte scale; // Scale
byte sign; // Sign (1 if positive, 0 if negative)
byte[] val; //[MAXNUMERICLEN]; // Value
}
}
Keith Kratochvil
Just make sure that you keep the file size small!!!!! Using maximum
compression is probably a good idea! Far too often people post 300k bitmaps
when a textual description would have been just fine. It takes a while to
download these images -- even over a nice connection.
--
Keith, SQL Server MVP
"David" <dsh...@yahoo.com> wrote in message
news:#sTGuSjsBHA.2396@tkmsftngp05...
ncaHammer
> It would probably be easier if you zipped your code and then posted the
zip.
> Just make sure that you keep the file size small!!!!! Using maximum
> compression is probably a good idea!
I disagree
Google do not cache any attachments and if anybody find this post 3 months
later will be unable to retrieve the code
Also this post will not be returned if someone is searching using some
keywords contained in the post's code
The problem arises when some newbies respond to those posts by quoting all
the body
> Far too often people post 300k bitmaps
> when a textual description would have been just fine. It takes a while to
> download these images -- even over a nice connection.
That is indeed bad manners of tooo lazy posters
The only solution I have thought is actually a wish :
the next version of OE to have additional rules/options (Tools/Message
Rules/News..)
e.g. if message size > 40K download header only
Regards
Keith Kratochvil
Text is probably the best.
--
Keith, SQL Server MVP
"ncaHammer" <ncah...@n.o.s.p.a.m.hotmail.com> wrote in message
news:Oo0u3oxsBHA.2500@tkmsftngp03...
David
David
"ncaHammer" <ncah...@n.o.s.p.a.m.hotmail.com> wrote in message
news:Oo0u3oxsBHA.2500@tkmsftngp03...
Wil Burton
extended stored procedure. I know nothing about this beyond you can't do it
in VB, only C++. I don't know if this has changed any for .NET
"David" <dsh...@yahoo.com> wrote in message
news:#mVxk01rBHA.1452@tkmsftngp05...
David
David
"Wil Burton" <wil.b...@internetrti.com> wrote in message
news:eBUk3GmtBHA.1860@tkmsftngp04...
Daniel van der Zee
Server books online). It allows you to store a large buffer of rows
locally and sent them in batches to SQL server. It is by far the fastest
(and lowest level) way of moving data to SQL server short of moving your
application into the SQL Server address space using an extended stored
procedure. It is also far easier to debug and doesn't affect SQL server
stability.
It would require some unmanaged C-coding but is well worth the effort.
It's been too long to provide example code, sorry. There are plenty of
examples in the documentation, however.
Enjoy,
Daniel
David
I have been looking at the bcp examples in BOL. I'm trying to decide whether
to use C# and interop thru platform invoke, or to use C++ and wrap the
unmanaged code in MC++ managed code. I will have a learning curve with
either method, so if anyone has suggestions I'm all ears.
David
"Daniel van der Zee" <z...@northrock.bm> wrote in message
news:3C6D9686...@northrock.bm...
Gunnar Skogsholm
performance by using multiple threads to send insert statements. We are
seeing 2380 insert statements/second.