This is a multi-part message in MIME format.
--=_NextPart_000_00A3_01C79966.58D0C7A0
Content-Type: text/plain;
charset="iso-8859-2"
Content-Transfer-Encoding: quoted-printable
Hi,
I have two sql server. One is sql 2000, and another is sql 2005
On sql 2000 I try to add linked to sql 2005.
(like this)
exec sp_addlinkedserver @.server =3D N'ZAGREB_NEW',
@.srvproduct =3D N' ',
@.provider =3D N'SQLNCLI',
@.datasrc =3D N'',
@.catalog =3D N'komitenti',
@.provstr =3D N'SERVER=3DZAGREB_NEW;uid=3Dradovan;pwd=3DDELTA555'
GO
I try
SELECT top 1 adresa from zagreb_new.komitenti.dbo.komitenti
and have followig error:
OLE DB error trace [Non-interface error: Column 'MB' (compile-time = ordinal 4) of object '"komitenti"."dbo"."komitenti"' was reported to = have changed. The exact nature of the change is unknown].
Msg 7356, Level 16, State 1, Line 1
OLE DB provider 'SQLNCLI' supplied inconsistent metadata for a column. = Metadata information was changed at execution time.
When I try:
SELECT * FROM OPENQUERY(ZAGREB_NEW, 'SELECT TOP 1 * FROM = komitenti.dbo.komitenti')
I got result.
How can I exec stored procedure on linked server, because
exec Zagreb_new.database.dbo.Mystored doesn't work
error is:
Msg 7212, Level 17, State 1, Line 1
Could not execute procedure 'spG_ProvjeriMB' on remote server = 'zagreb_new'.
--=_NextPart_000_00A3_01C79966.58D0C7A0
Content-Type: text/html;
charset="iso-8859-2"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hi,
I have two sql server. One is sql 2000, = and another is sql 2005
On sql 2000 I try to add linked to sql 2005.
(like this)
exec sp_addlinkedserver @.server =3D N'ZAGREB_NEW',
@.srvproduct =3D N' ',
@.provider =3D N'SQLNCLI',
@.datasrc =3D N'',
@.catalog =3D N'komitenti',
@.provstr =3D N'SERVER=3DZAGREB_NEW;uid=3Dradovan;pwd=3DDELTA555'
GO
I try
SELECT top 1 adresa from zagreb_new.komitenti.dbo.komitenti
and have followig error:
OLE DB error trace [Non-interface error: = Column 'MB' (compile-time ordinal 4) of object '"komitenti"."dbo"."komitenti"' was = reported to have changed. The exact nature of the change is unknown].
Msg 7356, Level 16, State 1, Line = 1
OLE DB provider 'SQLNCLI' supplied = inconsistent metadata for a column. Metadata information was changed at execution time.
When I try:
SELECT * FROM OPENQUERY(ZAGREB_NEW, = 'SELECT TOP 1 * FROM komitenti.dbo.komitenti')
I got result.
How can I exec stored procedure on linked = server, because
exec Zagreb_new.database.dbo.Mystored = doesn't work
error is:
Msg 7212, Level 17, State 1, Line 1
Could not execute procedure 'spG_ProvjeriMB' on remote = server 'zagreb_new'.
--=_NextPart_000_00A3_01C79966.58D0C7A0--Hi
"Radovan DobriÄ?" wrote:
> Hi,
> I have two sql server. One is sql 2000, and another is sql 2005
> On sql 2000 I try to add linked to sql 2005.
> (like this)
> exec sp_addlinkedserver @.server = N'ZAGREB_NEW',
> @.srvproduct = N' ',
> @.provider = N'SQLNCLI',
> @.datasrc = N'',
> @.catalog = N'komitenti',
> @.provstr = N'SERVER=ZAGREB_NEW;uid=radovan;pwd=DELTA555'
> GO
>
> I try
> SELECT top 1 adresa from zagreb_new.komitenti.dbo.komitenti
> and have followig error:
>
> OLE DB error trace [Non-interface error: Column 'MB' (compile-time ordinal 4) of object '"komitenti"."dbo"."komitenti"' was reported to have changed. The exact nature of the change is unknown].
> Msg 7356, Level 16, State 1, Line 1
> OLE DB provider 'SQLNCLI' supplied inconsistent metadata for a column. Metadata information was changed at execution time.
>
> When I try:
> SELECT * FROM OPENQUERY(ZAGREB_NEW, 'SELECT TOP 1 * FROM komitenti.dbo.komitenti')
> I got result.
>
> How can I exec stored procedure on linked server, because
> exec Zagreb_new.database.dbo.Mystored doesn't work
> error is:
>
> Msg 7212, Level 17, State 1, Line 1
> Could not execute procedure 'spG_ProvjeriMB' on remote server 'zagreb_new'
The only thing I have found about this is that "it could be fixed in SP2"
which versions are the servers running? Is the client one of the servers?
Is a login defined for the linked server? See
http://msdn2.microsoft.com/en-us/library/ms189811.aspx or are you using the
default mapping, in which case will the login have permissions on the remote
server? Your error message when running the remote procedure would indicate
that they don't have permissions to run this.
John|||Hi,
I have sp2 on server (I try it on another server with sp2 in another firm
and have exactly same result).
I try it with sql server account (and did mapping on that account). That
account have admin privil.
radovan
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:BE5DF476-CF21-4F8C-8634-D4461EA9132F@.microsoft.com...
> Hi
> "Radovan Dobriæ" wrote:
>> Hi,
>> I have two sql server. One is sql 2000, and another is sql 2005
>> On sql 2000 I try to add linked to sql 2005.
>> (like this)
>> exec sp_addlinkedserver @.server = N'ZAGREB_NEW',
>> @.srvproduct = N' ',
>> @.provider = N'SQLNCLI',
>> @.datasrc = N'',
>> @.catalog = N'komitenti',
>> @.provstr = N'SERVER=ZAGREB_NEW;uid=radovan;pwd=DELTA555'
>> GO
>>
>> I try
>> SELECT top 1 adresa from zagreb_new.komitenti.dbo.komitenti
>> and have followig error:
>>
>> OLE DB error trace [Non-interface error: Column 'MB' (compile-time
>> ordinal 4) of object '"komitenti"."dbo"."komitenti"' was reported to have
>> changed. The exact nature of the change is unknown].
>> Msg 7356, Level 16, State 1, Line 1
>> OLE DB provider 'SQLNCLI' supplied inconsistent metadata for a column.
>> Metadata information was changed at execution time.
>>
>> When I try:
>> SELECT * FROM OPENQUERY(ZAGREB_NEW, 'SELECT TOP 1 * FROM
>> komitenti.dbo.komitenti')
>> I got result.
>>
>> How can I exec stored procedure on linked server, because
>> exec Zagreb_new.database.dbo.Mystored doesn't work
>> error is:
>>
>> Msg 7212, Level 17, State 1, Line 1
>> Could not execute procedure 'spG_ProvjeriMB' on remote server
>> 'zagreb_new'
> The only thing I have found about this is that "it could be fixed in SP2"
> which versions are the servers running? Is the client one of the servers?
> Is a login defined for the linked server? See
> http://msdn2.microsoft.com/en-us/library/ms189811.aspx or are you using
> the
> default mapping, in which case will the login have permissions on the
> remote
> server? Your error message when running the remote procedure would
> indicate
> that they don't have permissions to run this.
> John
>|||Hi
Have you tried creating the linked server as
EXEC sp_addlinkedserver @.server = 'ZAGREB_NEW',
@.srvproduct = N'',
@.provider = N'SQLNCLI',
@.datasrc = N'ZAGREB_NEW'
or
EXEC sp_addlinkedserver 'ZAGREB_NEW', N'SQL Server'
John
"Radovan Dobriæ" wrote:
> Hi,
> I have sp2 on server (I try it on another server with sp2 in another firm
> and have exactly same result).
> I try it with sql server account (and did mapping on that account). That
> account have admin privil.
> radovan
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:BE5DF476-CF21-4F8C-8634-D4461EA9132F@.microsoft.com...
> > Hi
> >
> > "Radovan Dobriæ" wrote:
> >
> >> Hi,
> >> I have two sql server. One is sql 2000, and another is sql 2005
> >>
> >> On sql 2000 I try to add linked to sql 2005.
> >>
> >> (like this)
> >> exec sp_addlinkedserver @.server = N'ZAGREB_NEW',
> >>
> >> @.srvproduct = N' ',
> >>
> >> @.provider = N'SQLNCLI',
> >>
> >> @.datasrc = N'',
> >>
> >> @.catalog = N'komitenti',
> >>
> >> @.provstr = N'SERVER=ZAGREB_NEW;uid=radovan;pwd=DELTA555'
> >>
> >> GO
> >>
> >>
> >>
> >> I try
> >>
> >> SELECT top 1 adresa from zagreb_new.komitenti.dbo.komitenti
> >>
> >> and have followig error:
> >>
> >>
> >>
> >> OLE DB error trace [Non-interface error: Column 'MB' (compile-time
> >> ordinal 4) of object '"komitenti"."dbo"."komitenti"' was reported to have
> >> changed. The exact nature of the change is unknown].
> >>
> >> Msg 7356, Level 16, State 1, Line 1
> >>
> >> OLE DB provider 'SQLNCLI' supplied inconsistent metadata for a column.
> >> Metadata information was changed at execution time.
> >>
> >>
> >>
> >> When I try:
> >>
> >> SELECT * FROM OPENQUERY(ZAGREB_NEW, 'SELECT TOP 1 * FROM
> >> komitenti.dbo.komitenti')
> >>
> >> I got result.
> >>
> >>
> >>
> >> How can I exec stored procedure on linked server, because
> >>
> >> exec Zagreb_new.database.dbo.Mystored doesn't work
> >>
> >> error is:
> >>
> >>
> >>
> >> Msg 7212, Level 17, State 1, Line 1
> >>
> >> Could not execute procedure 'spG_ProvjeriMB' on remote server
> >> 'zagreb_new'
> >
> > The only thing I have found about this is that "it could be fixed in SP2"
> > which versions are the servers running? Is the client one of the servers?
> >
> > Is a login defined for the linked server? See
> > http://msdn2.microsoft.com/en-us/library/ms189811.aspx or are you using
> > the
> > default mapping, in which case will the login have permissions on the
> > remote
> > server? Your error message when running the remote procedure would
> > indicate
> > that they don't have permissions to run this.
> >
> > John
> >
> >
>
>|||That was my first try.
Linked server works fine with
SELECT * FROM OPENQUERY(ZAGREB_NEW, 'SELECT TOP 1 * FROM
komitenti.dbo.komitenti')
but not with
SELECT TOP 1 * FROM zagreb_new.komitenti.dbo.komitenti'
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:03897BB2-0508-4F7C-A923-090DB6AE439B@.microsoft.com...
> Hi
> Have you tried creating the linked server as
> EXEC sp_addlinkedserver @.server = 'ZAGREB_NEW',
> @.srvproduct = N'',
> @.provider = N'SQLNCLI',
> @.datasrc = N'ZAGREB_NEW'
> or
> EXEC sp_addlinkedserver 'ZAGREB_NEW', N'SQL Server'
>
> John
> "Radovan Dobria" wrote:
>> Hi,
>> I have sp2 on server (I try it on another server with sp2 in another firm
>> and have exactly same result).
>> I try it with sql server account (and did mapping on that account). That
>> account have admin privil.
>> radovan
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:BE5DF476-CF21-4F8C-8634-D4461EA9132F@.microsoft.com...
>> > Hi
>> >
>> > "Radovan Dobria" wrote:
>> >
>> >> Hi,
>> >> I have two sql server. One is sql 2000, and another is sql 2005
>> >>
>> >> On sql 2000 I try to add linked to sql 2005.
>> >>
>> >> (like this)
>> >> exec sp_addlinkedserver @.server = N'ZAGREB_NEW',
>> >>
>> >> @.srvproduct = N' ',
>> >>
>> >> @.provider = N'SQLNCLI',
>> >>
>> >> @.datasrc = N'',
>> >>
>> >> @.catalog = N'komitenti',
>> >>
>> >> @.provstr = N'SERVER=ZAGREB_NEW;uid=radovan;pwd=DELTA555'
>> >>
>> >> GO
>> >>
>> >>
>> >>
>> >> I try
>> >>
>> >> SELECT top 1 adresa from zagreb_new.komitenti.dbo.komitenti
>> >>
>> >> and have followig error:
>> >>
>> >>
>> >>
>> >> OLE DB error trace [Non-interface error: Column 'MB' (compile-time
>> >> ordinal 4) of object '"komitenti"."dbo"."komitenti"' was reported to
>> >> have
>> >> changed. The exact nature of the change is unknown].
>> >>
>> >> Msg 7356, Level 16, State 1, Line 1
>> >>
>> >> OLE DB provider 'SQLNCLI' supplied inconsistent metadata for a column.
>> >> Metadata information was changed at execution time.
>> >>
>> >>
>> >>
>> >> When I try:
>> >>
>> >> SELECT * FROM OPENQUERY(ZAGREB_NEW, 'SELECT TOP 1 * FROM
>> >> komitenti.dbo.komitenti')
>> >>
>> >> I got result.
>> >>
>> >>
>> >>
>> >> How can I exec stored procedure on linked server, because
>> >>
>> >> exec Zagreb_new.database.dbo.Mystored doesn't work
>> >>
>> >> error is:
>> >>
>> >>
>> >>
>> >> Msg 7212, Level 17, State 1, Line 1
>> >>
>> >> Could not execute procedure 'spG_ProvjeriMB' on remote server
>> >> 'zagreb_new'
>> >
>> > The only thing I have found about this is that "it could be fixed in
>> > SP2"
>> > which versions are the servers running? Is the client one of the
>> > servers?
>> >
>> > Is a login defined for the linked server? See
>> > http://msdn2.microsoft.com/en-us/library/ms189811.aspx or are you using
>> > the
>> > default mapping, in which case will the login have permissions on the
>> > remote
>> > server? Your error message when running the remote procedure would
>> > indicate
>> > that they don't have permissions to run this.
>> >
>> > John
>> >
>> >
>>|||Hi
On May 23, 3:40 pm, "Radovan Dobri=E6" <rado...@.servis24.hr> wrote:
> That was my first try.
> Linked server works fine with
> SELECT * FROM OPENQUERY(ZAGREB_NEW, 'SELECT TOP 1 * FROM
> komitenti.dbo.komitenti')
> but not with
> SELECT TOP 1 * FROM zagreb_new.komitenti.dbo.komitenti'
>
The error you are getting would indicate that the user context that
each one is running under is different in each case.
Try mapping the local login to a different remote login using
sp_addlinkedsrvlogin
e=2Eg.
EXEC sp_addlinkedsrvlogin 'Accounts', 'false', 'Domain\Mary', 'MaryP',
'd89q3w4u'
and/or make sure that you can connect as the same user on the remote
system and execute the procedure.
John
Showing posts with label plain. Show all posts
Showing posts with label plain. Show all posts
Wednesday, March 28, 2012
Saturday, February 25, 2012
problem with DBLib access to SQL Server 2005
Hi all,
I'm having a (probably) fundamental problem executing an insert statement on
SQL Server 2005 from an app written in plain C, using the DBLib API. Please
DO NOT comment on the fact the DBlib is obsolete, I know it is. It's just th
e
fastest way with the least overhead, and as such my method of choice and
currently still available with SQL Server 2005.
All appears to go well for the init, login and begin transaction calls. The
first and any subsequent inserts however cause dbresults to return 2, which,
depending on where and what in the docs you want to believe, means
NO_MORE_RESULTS. In the context of an insert, that doesn't make much sense.
In any case, the data did not get inserted.
When I keep SQL Monitor open during those calls, I see the calls are being
made, the insert statements transferred, and when I copy/paste them into the
SQL Manager window and execute them, they work just fine, no syntax problems
there.
Here are a few code snippets, maybe somebody can see where I'm messing up.
the database init:
// Initialize DB-Library.
dbinit();
// Get a LOGINREC.
login = dblogin ();
DBSETLSECURE (login);
DBSETLAPP(login, "mydb");
DBSETLUSER(login, "TITAN\\user");
// Get a DBPROCESS structure for communication with SQL Server.
dbproc = dbopen(login, "Titan");
//switch the default database to the mydb DB:
dbcmd (dbproc, "EXEC sp_defaultdb 'sa', 'mydb'");
dbsqlexec (dbproc);
This part does not produce any error messages. I'm logging into sql server
as the logged in user, which should be the sa in my default environment.
Then, I initiate a transaction. Each transaction will consist of about
20'000 inserts:
strcpy_s(cBuf, sizeof(cBuf), "BEGIN TRANSACTION");
// Send the command to SQL Server and start execution.
dbcmd (dbproc, cBuf);
dbsqlexec (dbproc);
// Process the results.
rc = dbresults (dbproc);
if (rc != SUCCEED && rc != NO_MORE_RESULTS) {
printf("Command failed: %s", cBuf);
dbexit();
exit(1);
}
This returns rc = 1 (i.e. SUCCEED).
Then I begin with the insert statement:
dbcmd (dbproc, "INSERT INTO data (\"Field1\") VALUES (-12);");
rc = dbsqlexec (dbproc);
// Process the results.
rc = dbresults(dbproc);
if (rc != SUCCEED && rc != NO_MORE_RESULTS) {
fprintf(stderr, "dbresults returned: %d\n", rc);
dbexit();
exit(1);
}
Now, rc = 2. Why? The same statement executed in the SQL Server Manager
window works just fine, the same statement in SQLite worked just fine as wel
l
(actually, the " were ' and had to be changed to " because SQL Server
complained about syntax).
This happens regardless of whether I start with a "BEGIN TRANSACTION" call
or not.
Cheers
- BaltBalt,
fastest way with the least overhead, and as such my method of choice and
currently still available with SQL Server 2005. <<<
Actually it is not still available with 2005 you have to get it from a
previous version to even use it. It is obsolete for a reason and your
excuse for using it simply will not cut it. Even if you get thru this
hurdle you will ultimately hit another. If you are trying to insert 20K rows
as fast as possible I suggest you use one of the newer bulk insert api's
instead. I know this is not what you wanted to hear but this is a loosing
proposition in the long run.
Andrew J. Kelly SQL MVP
"Balt" <Balt@.discussions.microsoft.com> wrote in message
news:CBB6F9F3-711F-4837-B831-C62DEA5F411A@.microsoft.com...
> Hi all,
> I'm having a (probably) fundamental problem executing an insert statement
> on
> SQL Server 2005 from an app written in plain C, using the DBLib API.
> Please
> DO NOT comment on the fact the DBlib is obsolete, I know it is. It's just
> the
> fastest way with the least overhead, and as such my method of choice and
> currently still available with SQL Server 2005.
> All appears to go well for the init, login and begin transaction calls.
> The
> first and any subsequent inserts however cause dbresults to return 2,
> which,
> depending on where and what in the docs you want to believe, means
> NO_MORE_RESULTS. In the context of an insert, that doesn't make much
> sense.
> In any case, the data did not get inserted.
> When I keep SQL Monitor open during those calls, I see the calls are being
> made, the insert statements transferred, and when I copy/paste them into
> the
> SQL Manager window and execute them, they work just fine, no syntax
> problems
> there.
> Here are a few code snippets, maybe somebody can see where I'm messing up.
> the database init:
> // Initialize DB-Library.
> dbinit();
> // Get a LOGINREC.
> login = dblogin ();
> DBSETLSECURE (login);
> DBSETLAPP(login, "mydb");
> DBSETLUSER(login, "TITAN\\user");
>
> // Get a DBPROCESS structure for communication with SQL Server.
> dbproc = dbopen(login, "Titan");
> //switch the default database to the mydb DB:
> dbcmd (dbproc, "EXEC sp_defaultdb 'sa', 'mydb'");
> dbsqlexec (dbproc);
> This part does not produce any error messages. I'm logging into sql server
> as the logged in user, which should be the sa in my default environment.
> Then, I initiate a transaction. Each transaction will consist of about
> 20'000 inserts:
> strcpy_s(cBuf, sizeof(cBuf), "BEGIN TRANSACTION");
> // Send the command to SQL Server and start execution.
> dbcmd (dbproc, cBuf);
> dbsqlexec (dbproc);
> // Process the results.
> rc = dbresults (dbproc);
> if (rc != SUCCEED && rc != NO_MORE_RESULTS) {
> printf("Command failed: %s", cBuf);
> dbexit();
> exit(1);
> }
> This returns rc = 1 (i.e. SUCCEED).
> Then I begin with the insert statement:
> dbcmd (dbproc, "INSERT INTO data (\"Field1\") VALUES (-12);");
> rc = dbsqlexec (dbproc);
> // Process the results.
> rc = dbresults(dbproc);
> if (rc != SUCCEED && rc != NO_MORE_RESULTS) {
> fprintf(stderr, "dbresults returned: %d\n", rc);
> dbexit();
> exit(1);
> }
> Now, rc = 2. Why? The same statement executed in the SQL Server Manager
> window works just fine, the same statement in SQLite worked just fine as
> well
> (actually, the " were ' and had to be changed to " because SQL Server
> complained about syntax).
> This happens regardless of whether I start with a "BEGIN TRANSACTION" call
> or not.
> Cheers
> - Balt
>|||Balt (Balt@.discussions.microsoft.com) writes:
> All appears to go well for the init, login and begin transaction calls.
> The first and any subsequent inserts however cause dbresults to return
> 2, which, depending on where and what in the docs you want to believe,
> means NO_MORE_RESULTS. In the context of an insert, that doesn't make
> much sense.
Actually, it does. An INSERT statement will return a rowcount (the
thing you see as a ("1 row affected") in Query Analyzer or Mgmt Studio,
unless SET NOCOUNT ON.
> //switch the default database to the mydb DB:
> dbcmd (dbproc, "EXEC sp_defaultdb 'sa', 'mydb'");
> dbsqlexec (dbproc);
This seems like a very funny thing to do. And dangerous. Changing the
default database for sa is something I never had the guts to try.
And why would you change the default database for sa, when you logged
in with integrated security?
> This part does not produce any error messages. I'm logging into sql server
> as the logged in user, which should be the sa in my default environment.
No! "sa" is an SQL login, and you login with Integrated Security. If you
have admin rights in Windows, you will have sy
min rights in SQL Server,
but you will not be sa.
> Then, I initiate a transaction. Each transaction will consist of about
> 20'000 inserts:
As Andrew said, consider using bulk operations instead.
Another alternative is to build an XML document of all the 20000
rows, send that to SQL Server and then shred it into the target table(s).
If you really want to send row-by-row, use a stored procedure that you
call through RPC instead. This is faster, as you get a cached query plan,
and there is no need for parsing the query each time.
And if you don't want to use stored procedures, at least call
sp_executesql through RPC instead. Hm, wait, sp_executesql insists
on parameters on nvarchar that DB-Library cannot handle. So that won't
fly.
Anyway, whatever, don't send 20000 INSERT statements over the wire. It's
simple ineffecient.
> dbcmd (dbproc, "INSERT INTO data (\"Field1\") VALUES (-12);");
For this to work, the setting QUOTED_IDENTIFIER must be ON. Which it is
by default with ODBC, OLE DB and SqlClient. But not with DB-Library. So
you would need to issue SET QUOTED_IDENTIFIER ON from your program. Or
use [] to quote your identifiers instead.
Then again, I told you not to send INSERT statements.
> rc = dbsqlexec (dbproc);
> // Process the results.
> rc = dbresults(dbproc);
> if (rc != SUCCEED && rc != NO_MORE_RESULTS) {
> fprintf(stderr, "dbresults returned: %d\n", rc);
> dbexit();
> exit(1);
> }
> Now, rc = 2. Why?
Because there are no more results. But not from the INSERT statment.
You failed to check the return code from dbsqlexec, but I bet it says
FAIL. Because you previous call to dbresults return SUCCEED, and this
means that you have not cleared out the previous command, so you
get an error when trying to start a new one. You should always loop
over dbresults until it returns FAIL or NO_MORE_RESULTS.
Finally, there are tons of limitations you run into when you use
DB-Library, so it is very difficult to recommend it. It is a very nice
client API once you have learnt to master it. Particularly, it does not
perform tricks behind your back. However, it certainly has a taste
of black-and-white television these days.
--
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
I'm having a (probably) fundamental problem executing an insert statement on
SQL Server 2005 from an app written in plain C, using the DBLib API. Please
DO NOT comment on the fact the DBlib is obsolete, I know it is. It's just th
e
fastest way with the least overhead, and as such my method of choice and
currently still available with SQL Server 2005.
All appears to go well for the init, login and begin transaction calls. The
first and any subsequent inserts however cause dbresults to return 2, which,
depending on where and what in the docs you want to believe, means
NO_MORE_RESULTS. In the context of an insert, that doesn't make much sense.
In any case, the data did not get inserted.
When I keep SQL Monitor open during those calls, I see the calls are being
made, the insert statements transferred, and when I copy/paste them into the
SQL Manager window and execute them, they work just fine, no syntax problems
there.
Here are a few code snippets, maybe somebody can see where I'm messing up.
the database init:
// Initialize DB-Library.
dbinit();
// Get a LOGINREC.
login = dblogin ();
DBSETLSECURE (login);
DBSETLAPP(login, "mydb");
DBSETLUSER(login, "TITAN\\user");
// Get a DBPROCESS structure for communication with SQL Server.
dbproc = dbopen(login, "Titan");
//switch the default database to the mydb DB:
dbcmd (dbproc, "EXEC sp_defaultdb 'sa', 'mydb'");
dbsqlexec (dbproc);
This part does not produce any error messages. I'm logging into sql server
as the logged in user, which should be the sa in my default environment.
Then, I initiate a transaction. Each transaction will consist of about
20'000 inserts:
strcpy_s(cBuf, sizeof(cBuf), "BEGIN TRANSACTION");
// Send the command to SQL Server and start execution.
dbcmd (dbproc, cBuf);
dbsqlexec (dbproc);
// Process the results.
rc = dbresults (dbproc);
if (rc != SUCCEED && rc != NO_MORE_RESULTS) {
printf("Command failed: %s", cBuf);
dbexit();
exit(1);
}
This returns rc = 1 (i.e. SUCCEED).
Then I begin with the insert statement:
dbcmd (dbproc, "INSERT INTO data (\"Field1\") VALUES (-12);");
rc = dbsqlexec (dbproc);
// Process the results.
rc = dbresults(dbproc);
if (rc != SUCCEED && rc != NO_MORE_RESULTS) {
fprintf(stderr, "dbresults returned: %d\n", rc);
dbexit();
exit(1);
}
Now, rc = 2. Why? The same statement executed in the SQL Server Manager
window works just fine, the same statement in SQLite worked just fine as wel
l
(actually, the " were ' and had to be changed to " because SQL Server
complained about syntax).
This happens regardless of whether I start with a "BEGIN TRANSACTION" call
or not.
Cheers
- BaltBalt,
fastest way with the least overhead, and as such my method of choice and
currently still available with SQL Server 2005. <<<
Actually it is not still available with 2005 you have to get it from a
previous version to even use it. It is obsolete for a reason and your
excuse for using it simply will not cut it. Even if you get thru this
hurdle you will ultimately hit another. If you are trying to insert 20K rows
as fast as possible I suggest you use one of the newer bulk insert api's
instead. I know this is not what you wanted to hear but this is a loosing
proposition in the long run.
Andrew J. Kelly SQL MVP
"Balt" <Balt@.discussions.microsoft.com> wrote in message
news:CBB6F9F3-711F-4837-B831-C62DEA5F411A@.microsoft.com...
> Hi all,
> I'm having a (probably) fundamental problem executing an insert statement
> on
> SQL Server 2005 from an app written in plain C, using the DBLib API.
> Please
> DO NOT comment on the fact the DBlib is obsolete, I know it is. It's just
> the
> fastest way with the least overhead, and as such my method of choice and
> currently still available with SQL Server 2005.
> All appears to go well for the init, login and begin transaction calls.
> The
> first and any subsequent inserts however cause dbresults to return 2,
> which,
> depending on where and what in the docs you want to believe, means
> NO_MORE_RESULTS. In the context of an insert, that doesn't make much
> sense.
> In any case, the data did not get inserted.
> When I keep SQL Monitor open during those calls, I see the calls are being
> made, the insert statements transferred, and when I copy/paste them into
> the
> SQL Manager window and execute them, they work just fine, no syntax
> problems
> there.
> Here are a few code snippets, maybe somebody can see where I'm messing up.
> the database init:
> // Initialize DB-Library.
> dbinit();
> // Get a LOGINREC.
> login = dblogin ();
> DBSETLSECURE (login);
> DBSETLAPP(login, "mydb");
> DBSETLUSER(login, "TITAN\\user");
>
> // Get a DBPROCESS structure for communication with SQL Server.
> dbproc = dbopen(login, "Titan");
> //switch the default database to the mydb DB:
> dbcmd (dbproc, "EXEC sp_defaultdb 'sa', 'mydb'");
> dbsqlexec (dbproc);
> This part does not produce any error messages. I'm logging into sql server
> as the logged in user, which should be the sa in my default environment.
> Then, I initiate a transaction. Each transaction will consist of about
> 20'000 inserts:
> strcpy_s(cBuf, sizeof(cBuf), "BEGIN TRANSACTION");
> // Send the command to SQL Server and start execution.
> dbcmd (dbproc, cBuf);
> dbsqlexec (dbproc);
> // Process the results.
> rc = dbresults (dbproc);
> if (rc != SUCCEED && rc != NO_MORE_RESULTS) {
> printf("Command failed: %s", cBuf);
> dbexit();
> exit(1);
> }
> This returns rc = 1 (i.e. SUCCEED).
> Then I begin with the insert statement:
> dbcmd (dbproc, "INSERT INTO data (\"Field1\") VALUES (-12);");
> rc = dbsqlexec (dbproc);
> // Process the results.
> rc = dbresults(dbproc);
> if (rc != SUCCEED && rc != NO_MORE_RESULTS) {
> fprintf(stderr, "dbresults returned: %d\n", rc);
> dbexit();
> exit(1);
> }
> Now, rc = 2. Why? The same statement executed in the SQL Server Manager
> window works just fine, the same statement in SQLite worked just fine as
> well
> (actually, the " were ' and had to be changed to " because SQL Server
> complained about syntax).
> This happens regardless of whether I start with a "BEGIN TRANSACTION" call
> or not.
> Cheers
> - Balt
>|||Balt (Balt@.discussions.microsoft.com) writes:
> All appears to go well for the init, login and begin transaction calls.
> The first and any subsequent inserts however cause dbresults to return
> 2, which, depending on where and what in the docs you want to believe,
> means NO_MORE_RESULTS. In the context of an insert, that doesn't make
> much sense.
Actually, it does. An INSERT statement will return a rowcount (the
thing you see as a ("1 row affected") in Query Analyzer or Mgmt Studio,
unless SET NOCOUNT ON.
> //switch the default database to the mydb DB:
> dbcmd (dbproc, "EXEC sp_defaultdb 'sa', 'mydb'");
> dbsqlexec (dbproc);
This seems like a very funny thing to do. And dangerous. Changing the
default database for sa is something I never had the guts to try.
And why would you change the default database for sa, when you logged
in with integrated security?
> This part does not produce any error messages. I'm logging into sql server
> as the logged in user, which should be the sa in my default environment.
No! "sa" is an SQL login, and you login with Integrated Security. If you
have admin rights in Windows, you will have sy
min rights in SQL Server,but you will not be sa.
> Then, I initiate a transaction. Each transaction will consist of about
> 20'000 inserts:
As Andrew said, consider using bulk operations instead.
Another alternative is to build an XML document of all the 20000
rows, send that to SQL Server and then shred it into the target table(s).
If you really want to send row-by-row, use a stored procedure that you
call through RPC instead. This is faster, as you get a cached query plan,
and there is no need for parsing the query each time.
And if you don't want to use stored procedures, at least call
sp_executesql through RPC instead. Hm, wait, sp_executesql insists
on parameters on nvarchar that DB-Library cannot handle. So that won't
fly.
Anyway, whatever, don't send 20000 INSERT statements over the wire. It's
simple ineffecient.
> dbcmd (dbproc, "INSERT INTO data (\"Field1\") VALUES (-12);");
For this to work, the setting QUOTED_IDENTIFIER must be ON. Which it is
by default with ODBC, OLE DB and SqlClient. But not with DB-Library. So
you would need to issue SET QUOTED_IDENTIFIER ON from your program. Or
use [] to quote your identifiers instead.
Then again, I told you not to send INSERT statements.
> rc = dbsqlexec (dbproc);
> // Process the results.
> rc = dbresults(dbproc);
> if (rc != SUCCEED && rc != NO_MORE_RESULTS) {
> fprintf(stderr, "dbresults returned: %d\n", rc);
> dbexit();
> exit(1);
> }
> Now, rc = 2. Why?
Because there are no more results. But not from the INSERT statment.
You failed to check the return code from dbsqlexec, but I bet it says
FAIL. Because you previous call to dbresults return SUCCEED, and this
means that you have not cleared out the previous command, so you
get an error when trying to start a new one. You should always loop
over dbresults until it returns FAIL or NO_MORE_RESULTS.
Finally, there are tons of limitations you run into when you use
DB-Library, so it is very difficult to recommend it. It is a very nice
client API once you have learnt to master it. Particularly, it does not
perform tricks behind your back. However, it certainly has a taste
of black-and-white television these days.
--
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
Subscribe to:
Posts (Atom)