|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
how to obtain further error information?I'm using DBlib to access a SQL Server DB. Some very trivial calls however
fail, and the call to dbresults() returns 2. Is there any API I can call to obtain more detailed error information? - Balt Hi
Look at dberrhandle Unless you are using this for backway compatibility look at using a different API to DB-Library as this has been depricated. John Show quote "Balt" <B***@discussions.microsoft.com> wrote in message news:9B126F36-67FC-4EE7-B5AD-2AAF7A68991A@microsoft.com... > I'm using DBlib to access a SQL Server DB. Some very trivial calls however > fail, and the call to dbresults() returns 2. Is there any API I can call > to > obtain more detailed error information? > > - Balt Balt (B***@discussions.microsoft.com) writes:
> I'm using DBlib to access a SQL Server DB. Some very trivial calls As noted in another reply 2 is NO_MORE_RESULTS, and is a perfectly normal> however fail, and the call to dbresults() returns 2. Is there any API I > can call to obtain more detailed error information? return from dbresults. You should establish a message handler with dbmsghandle for messages from SQL Server, including error messages, and an error handler with dberrhandle for errors from DB-Library itself. Error handling in DB- Library is by callback. But since you apparently is a beginner in DB-Library, do yourself a favour and switch to ODBC instead. -- Erland Sommarskog, SQL Server MVP, esq***@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,
thanks for your suggestions. I now have both a error handler and message handler installed and working. I seem to be getting a "207 invalid column name" error back from SQL Server. This is odd, as the exact same statement works fine with SQLite as well as in the SQL Server Manager Query window. Does it maybe have to do with single/double quotes around field names? The reason I'm using DBLib is performance: I don't have a single processor cycle to spare. This is for a database that will be 1.5TB in size and gets filled with data processed from raw data files, it's a rather simple process with only little preprocessing done to the data (but just enought so that bcp won't do). Currently, it will take about a week to fill the database. With ODBS we're talking a month... I wish MS would keep a high performance option into SQL Server open and supperted... Cheers - Balt Show quote "Erland Sommarskog" wrote: > Balt (B***@discussions.microsoft.com) writes: > > I'm using DBlib to access a SQL Server DB. Some very trivial calls > > however fail, and the call to dbresults() returns 2. Is there any API I > > can call to obtain more detailed error information? > > As noted in another reply 2 is NO_MORE_RESULTS, and is a perfectly normal > return from dbresults. > > You should establish a message handler with dbmsghandle for messages > from SQL Server, including error messages, and an error handler with > dberrhandle for errors from DB-Library itself. Error handling in DB- > Library is by callback. > > But since you apparently is a beginner in DB-Library, do yourself a > favour and switch to ODBC instead. > > > -- > Erland Sommarskog, SQL Server MVP, esq***@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 > > I wish MS would keep a high performance option into SQL Server open and If performance is important to you, consider inserting data using a bulk > supperted... insert method. This will improve insert performance by at least an order of magnitude compared to traditional inserts using obsolete DBLib. You can bulk insert directly from you application using ODBC BCP (what BCP uses), OLE DB IRowsetFastLoad (what DTS uses) or from managed code using the new SqlBulkCopy class introduced with .Net 2.0. In fact, you will probably find that it's much faster for your app to create a text files and then import using BCP/DTS than execute individual insert statements. -- Show quoteHope this helps. Dan Guzman SQL Server MVP "Balt" <B***@discussions.microsoft.com> wrote in message news:CAE5AFBF-DDA1-47CE-BE08-8066930C8C24@microsoft.com... > Hi, > > thanks for your suggestions. I now have both a error handler and message > handler installed and working. I seem to be getting a "207 invalid column > name" error back from SQL Server. This is odd, as the exact same statement > works fine with SQLite as well as in the SQL Server Manager Query window. > Does it maybe have to do with single/double quotes around field names? > > The reason I'm using DBLib is performance: I don't have a single processor > cycle to spare. This is for a database that will be 1.5TB in size and gets > filled with data processed from raw data files, it's a rather simple > process > with only little preprocessing done to the data (but just enought so that > bcp > won't do). Currently, it will take about a week to fill the database. With > ODBS we're talking a month... > > I wish MS would keep a high performance option into SQL Server open and > supperted... > > Cheers > > - Balt > > "Erland Sommarskog" wrote: > >> Balt (B***@discussions.microsoft.com) writes: >> > I'm using DBlib to access a SQL Server DB. Some very trivial calls >> > however fail, and the call to dbresults() returns 2. Is there any API I >> > can call to obtain more detailed error information? >> >> As noted in another reply 2 is NO_MORE_RESULTS, and is a perfectly normal >> return from dbresults. >> >> You should establish a message handler with dbmsghandle for messages >> from SQL Server, including error messages, and an error handler with >> dberrhandle for errors from DB-Library itself. Error handling in DB- >> Library is by callback. >> >> But since you apparently is a beginner in DB-Library, do yourself a >> favour and switch to ODBC instead. >> >> >> -- >> Erland Sommarskog, SQL Server MVP, esq***@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 >> That's a very interesting approach and I definitely should give that further
consideration. One of the main concerns that comes to mind immediately is the creation of indices: is it possible to have indices created with a bulk import? if a after-the-fact index creation is an option, what is performing better, having the index created during the insert or bulk importing and creating the index afterwards? - Balt Show quote "Dan Guzman" wrote: > > I wish MS would keep a high performance option into SQL Server open and > > supperted... > > If performance is important to you, consider inserting data using a bulk > insert method. This will improve insert performance by at least an order of > magnitude compared to traditional inserts using obsolete DBLib. You can > bulk insert directly from you application using ODBC BCP (what BCP uses), > OLE DB IRowsetFastLoad (what DTS uses) or from managed code using the new > SqlBulkCopy class introduced with .Net 2.0. In fact, you will probably find > that it's much faster for your app to create a text files and then import > using BCP/DTS than execute individual insert statements. > > -- > Hope this helps. > > Dan Guzman > SQL Server MVP > > "Balt" <B***@discussions.microsoft.com> wrote in message > news:CAE5AFBF-DDA1-47CE-BE08-8066930C8C24@microsoft.com... > > Hi, > > > > thanks for your suggestions. I now have both a error handler and message > > handler installed and working. I seem to be getting a "207 invalid column > > name" error back from SQL Server. This is odd, as the exact same statement > > works fine with SQLite as well as in the SQL Server Manager Query window. > > Does it maybe have to do with single/double quotes around field names? > > > > The reason I'm using DBLib is performance: I don't have a single processor > > cycle to spare. This is for a database that will be 1.5TB in size and gets > > filled with data processed from raw data files, it's a rather simple > > process > > with only little preprocessing done to the data (but just enought so that > > bcp > > won't do). Currently, it will take about a week to fill the database. With > > ODBS we're talking a month... > > > > I wish MS would keep a high performance option into SQL Server open and > > supperted... > > > > Cheers > > > > - Balt > > > > "Erland Sommarskog" wrote: > > > >> Balt (B***@discussions.microsoft.com) writes: > >> > I'm using DBlib to access a SQL Server DB. Some very trivial calls > >> > however fail, and the call to dbresults() returns 2. Is there any API I > >> > can call to obtain more detailed error information? > >> > >> As noted in another reply 2 is NO_MORE_RESULTS, and is a perfectly normal > >> return from dbresults. > >> > >> You should establish a message handler with dbmsghandle for messages > >> from SQL Server, including error messages, and an error handler with > >> dberrhandle for errors from DB-Library itself. Error handling in DB- > >> Library is by callback. > >> > >> But since you apparently is a beginner in DB-Library, do yourself a > >> favour and switch to ODBC instead. > >> > >> > >> -- > >> Erland Sommarskog, SQL Server MVP, esq***@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
Creating the indexes after your data has been loaded should improve the performance of the inserts and speed up the overall time. There are many ways to create your indexes either programatically or through scripts. John Show quote "Balt" wrote: > That's a very interesting approach and I definitely should give that further > consideration. One of the main concerns that comes to mind immediately is the > creation of indices: is it possible to have indices created with a bulk > import? if a after-the-fact index creation is an option, what is performing > better, having the index created during the insert or bulk importing and > creating the index afterwards? > > - Balt > > "Dan Guzman" wrote: > > > > I wish MS would keep a high performance option into SQL Server open and > > > supperted... > > > > If performance is important to you, consider inserting data using a bulk > > insert method. This will improve insert performance by at least an order of > > magnitude compared to traditional inserts using obsolete DBLib. You can > > bulk insert directly from you application using ODBC BCP (what BCP uses), > > OLE DB IRowsetFastLoad (what DTS uses) or from managed code using the new > > SqlBulkCopy class introduced with .Net 2.0. In fact, you will probably find > > that it's much faster for your app to create a text files and then import > > using BCP/DTS than execute individual insert statements. > > > > -- > > Hope this helps. > > > > Dan Guzman > > SQL Server MVP > > > > "Balt" <B***@discussions.microsoft.com> wrote in message > > news:CAE5AFBF-DDA1-47CE-BE08-8066930C8C24@microsoft.com... > > > Hi, > > > > > > thanks for your suggestions. I now have both a error handler and message > > > handler installed and working. I seem to be getting a "207 invalid column > > > name" error back from SQL Server. This is odd, as the exact same statement > > > works fine with SQLite as well as in the SQL Server Manager Query window. > > > Does it maybe have to do with single/double quotes around field names? > > > > > > The reason I'm using DBLib is performance: I don't have a single processor > > > cycle to spare. This is for a database that will be 1.5TB in size and gets > > > filled with data processed from raw data files, it's a rather simple > > > process > > > with only little preprocessing done to the data (but just enought so that > > > bcp > > > won't do). Currently, it will take about a week to fill the database. With > > > ODBS we're talking a month... > > > > > > I wish MS would keep a high performance option into SQL Server open and > > > supperted... > > > > > > Cheers > > > > > > - Balt > > > > > > "Erland Sommarskog" wrote: > > > > > >> Balt (B***@discussions.microsoft.com) writes: > > >> > I'm using DBlib to access a SQL Server DB. Some very trivial calls > > >> > however fail, and the call to dbresults() returns 2. Is there any API I > > >> > can call to obtain more detailed error information? > > >> > > >> As noted in another reply 2 is NO_MORE_RESULTS, and is a perfectly normal > > >> return from dbresults. > > >> > > >> You should establish a message handler with dbmsghandle for messages > > >> from SQL Server, including error messages, and an error handler with > > >> dberrhandle for errors from DB-Library itself. Error handling in DB- > > >> Library is by callback. > > >> > > >> But since you apparently is a beginner in DB-Library, do yourself a > > >> favour and switch to ODBC instead. > > >> > > >> > > >> -- > > >> Erland Sommarskog, SQL Server MVP, esq***@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 > > >> > > > > > > To expand on John's response, bulk inserts are minimally logged when you
have no indexes and are using the SIMPLE or BULK_LOGGED recovery model. Performance gains of minimal logging plus avoiding index maintenance during the load make 'fast' bulk loading attractive. The exception is when your input data is sequenced by your clustered index key, such as a datetime column, identity column or sequential GUID. In that case, it's often better to load with the clustered index in place and then build the non-clustered indexes afterward. Just to give you an idea of performance, I have a simple single-threaded C# app that uses SqlBulkCopy. The app parses a CSV and imports data into SQL at a rate well over 10K rows per second. Importantly, this is with no application tuning. I expect much better throughput could be achieved with some attention to detail on the app side, multi-threading and/or multiple application instances. -- Show quoteHope this helps. Dan Guzman SQL Server MVP "Balt" <B***@discussions.microsoft.com> wrote in message news:D782F812-1D36-4785-A4AB-4846C283E1F2@microsoft.com... > That's a very interesting approach and I definitely should give that > further > consideration. One of the main concerns that comes to mind immediately is > the > creation of indices: is it possible to have indices created with a bulk > import? if a after-the-fact index creation is an option, what is > performing > better, having the index created during the insert or bulk importing and > creating the index afterwards? > > - Balt > > "Dan Guzman" wrote: > >> > I wish MS would keep a high performance option into SQL Server open and >> > supperted... >> >> If performance is important to you, consider inserting data using a bulk >> insert method. This will improve insert performance by at least an order >> of >> magnitude compared to traditional inserts using obsolete DBLib. You can >> bulk insert directly from you application using ODBC BCP (what BCP uses), >> OLE DB IRowsetFastLoad (what DTS uses) or from managed code using the new >> SqlBulkCopy class introduced with .Net 2.0. In fact, you will probably >> find >> that it's much faster for your app to create a text files and then import >> using BCP/DTS than execute individual insert statements. >> >> -- >> Hope this helps. >> >> Dan Guzman >> SQL Server MVP >> >> "Balt" <B***@discussions.microsoft.com> wrote in message >> news:CAE5AFBF-DDA1-47CE-BE08-8066930C8C24@microsoft.com... >> > Hi, >> > >> > thanks for your suggestions. I now have both a error handler and >> > message >> > handler installed and working. I seem to be getting a "207 invalid >> > column >> > name" error back from SQL Server. This is odd, as the exact same >> > statement >> > works fine with SQLite as well as in the SQL Server Manager Query >> > window. >> > Does it maybe have to do with single/double quotes around field names? >> > >> > The reason I'm using DBLib is performance: I don't have a single >> > processor >> > cycle to spare. This is for a database that will be 1.5TB in size and >> > gets >> > filled with data processed from raw data files, it's a rather simple >> > process >> > with only little preprocessing done to the data (but just enought so >> > that >> > bcp >> > won't do). Currently, it will take about a week to fill the database. >> > With >> > ODBS we're talking a month... >> > >> > I wish MS would keep a high performance option into SQL Server open and >> > supperted... >> > >> > Cheers >> > >> > - Balt >> > >> > "Erland Sommarskog" wrote: >> > >> >> Balt (B***@discussions.microsoft.com) writes: >> >> > I'm using DBlib to access a SQL Server DB. Some very trivial calls >> >> > however fail, and the call to dbresults() returns 2. Is there any >> >> > API I >> >> > can call to obtain more detailed error information? >> >> >> >> As noted in another reply 2 is NO_MORE_RESULTS, and is a perfectly >> >> normal >> >> return from dbresults. >> >> >> >> You should establish a message handler with dbmsghandle for messages >> >> from SQL Server, including error messages, and an error handler with >> >> dberrhandle for errors from DB-Library itself. Error handling in DB- >> >> Library is by callback. >> >> >> >> But since you apparently is a beginner in DB-Library, do yourself a >> >> favour and switch to ODBC instead. >> >> >> >> >> >> -- >> >> Erland Sommarskog, SQL Server MVP, esq***@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 >> >> >> >> >> Balt (B***@discussions.microsoft.com) writes:
> The reason I'm using DBLib is performance: I don't have a single processor One could not believe that when looking at your code, as you are using a> cycle to spare. very ineffectient method to load the data. Besides, how do you know that DB-Lib is so much faster? Since you apparenly are new to DB-Lib, you apparently haven't benchmarked it. > This is for a database that will be 1.5TB in size and gets filled with As other have suggestion, look at the various bulk-load methods. You can> data processed from raw data files, it's a rather simple process with > only little preprocessing done to the data (but just enought so that bcp > won't do). Currently, it will take about a week to fill the database. > With ODBS we're talking a month... do bulk-insert through DB-Library as well, but if that is the faster option, I don't know. You should review this thread on Google News: http://groups.google.com/group/microsoft.public.sqlserver.programming/browse _frm/thread/b5c03764a15aba28/b1accc067407eb7f?tvc=1&q=bulk+BCP+native+group% 3Amicrosoft.public.sqlserver.programming+author%3AErland+author%3ASommarskog &hl=sv#b1accc067407eb7f Particularly check out the benchmarks performed by Bert, this indicates that you should use BULK INSERT or the IRowsetFastLoad interface in SQLOLDDB for best performance. And if you have the possibility to scale out and perform the transformation to native format outside SQL Server. You should of course investigate the bulk-facilities in SqlClient, and of course you need to check out DTS. At least if what you say that you have "no single processor cycle to spare". If you are in that situation, you cannot to just pick one solution, particularly when in it's one you don't even master. -- Erland Sommarskog, SQL Server MVP, esq***@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  |
|||||||||||||||||||||||
Other interesting topics