| Author |
Error configuring SQL Adapter
|
|
| Curt Hagenlocher 2004-12-27, 5:48 pm |
| When I try to add a SQL Adapter, I get the following error message from the
SQL Transport Schema Generation Wizard: "Failed to execute SQL Statement.
Please ensure that the supplied syntax is correct. The transaction has
already been implicitly or explicitly committed or aborted". Profiler does
not show any queries being run against the server.
Any ideas?
--
Curt Hagenlocher
curt@hagenlocher.org
| |
| Jon Flanders 2004-12-27, 5:48 pm |
| What query or stored proc are you using?
Query must have FOR XML <OPTIONS> in it.
Stored proc must return the schema (using xmldata option)
--
Jon Flanders
http://staff.develop.com/jfland/
"Curt Hagenlocher" <faustus@pureevil.org> wrote in message
news:%23AoVWGE7EHA.1260@TK2MSFTNGP12.phx.gbl...
> When I try to add a SQL Adapter, I get the following error message from
the
> SQL Transport Schema Generation Wizard: "Failed to execute SQL Statement.
> Please ensure that the supplied syntax is correct. The transaction has
> already been implicitly or explicitly committed or aborted". Profiler
does
> not show any queries being run against the server.
>
> Any ideas?
>
> --
> Curt Hagenlocher
> curt@hagenlocher.org
>
>
>
| |
| Curt Hagenlocher 2004-12-27, 5:48 pm |
| "Jon Flanders" <jfland@develop.com> wrote in message
news:OVRX0%23E7EHA.1524@TK2MSFTNGP09.phx.gbl...
> What query or stored proc are you using?
>
> Query must have FOR XML <OPTIONS> in it.
>
> Stored proc must return the schema (using xmldata option)
The stored procedure only performs an update. It does not return any data.
I'm trying to orchestrate an import, and the only possible results are
success or a raised error.
Shouldn't I have seen something in SQL Profiler?
--
Curt Hagenlocher
curt@hagenlocher.org
| |
| Jon Flanders 2004-12-28, 2:46 am |
| Ahh - you are running the wizard and probably specified arguments for the
input parameters? I have found the wizard to act a little, umm goofy, I
find that dialog (the one where you specify the the stored proc) works best
if I just leave things alone, certainly don't let it stay null (and yes
you'd think you should see some activity - *but* i have also found in many
places that the UI doesn't always report things quite acurately.)
--
Jon Flanders
http://staff.develop.com/jfland/
"Curt Hagenlocher" <faustus@pureevil.org> wrote in message
news:e6vWR6F7EHA.1524@TK2MSFTNGP09.phx.gbl...
> "Jon Flanders" <jfland@develop.com> wrote in message
> news:OVRX0%23E7EHA.1524@TK2MSFTNGP09.phx.gbl...
>
> The stored procedure only performs an update. It does not return any
data.
> I'm trying to orchestrate an import, and the only possible results are
> success or a raised error.
>
> Shouldn't I have seen something in SQL Profiler?
>
> --
> Curt Hagenlocher
> curt@hagenlocher.org
>
>
| |
| Curt Hagenlocher 2004-12-28, 5:49 pm |
| "Jon Flanders" <jfland@develop.com> wrote in message
news:%23QXLkzI7EHA.2600@TK2MSFTNGP09.phx.gbl...
> Ahh - you are running the wizard and probably specified arguments for the
> input parameters? I have found the wizard to act a little, umm goofy, I
> find that dialog (the one where you specify the the stored proc) works
> best
> if I just leave things alone, certainly don't let it stay null (and yes
> you'd think you should see some activity - *but* i have also found in many
> places that the UI doesn't always report things quite acurately.)
I've done it two ways: leaving all the parameters alone, and setting all
the parameters to null. Both give the same error. One of my parameters is
numeric; is this a problem? It looks like the generated statement always
tries to set parameters to a string value, even if they're numeric. I also
tried again with a different procedure that has only char and varchar
arguments, but had the same error message.
--
Curt Hagenlocher
curt@hagenlocher.org
| |
| Jon Flanders 2004-12-28, 5:49 pm |
| I just did this:
create proc proc_test_bt
@n numeric,
@c varchar(11)
as
return 0
And it worked for me by selecting the numeric value in the grid - so that
the generated statement had @n=NULL.
HTH. If you want to ship me the proc parameters offline I'll give it a
shot.
--
Jon Flanders
http://staff.develop.com/jfland/
"Curt Hagenlocher" <faustus@pureevil.org> wrote in message
news:O4s0k8P7EHA.208@TK2MSFTNGP12.phx.gbl...
> "Jon Flanders" <jfland@develop.com> wrote in message
> news:%23QXLkzI7EHA.2600@TK2MSFTNGP09.phx.gbl...
the[vbcol=seagreen]
many[vbcol=seagreen]
>
> I've done it two ways: leaving all the parameters alone, and setting all
> the parameters to null. Both give the same error. One of my parameters
is
> numeric; is this a problem? It looks like the generated statement always
> tries to set parameters to a string value, even if they're numeric. I
also
> tried again with a different procedure that has only char and varchar
> arguments, but had the same error message.
>
> --
> Curt Hagenlocher
> curt@hagenlocher.org
>
>
| |
| Curt Hagenlocher 2004-12-28, 5:49 pm |
| "Jon Flanders" <jfland@develop.com> wrote in message
news:OrozwDQ7EHA.3148@TK2MSFTNGP10.phx.gbl...
>I just did this:
> create proc proc_test_bt
> @n numeric,
> @c varchar(11)
>
> as
> return 0
>
> And it worked for me by selecting the numeric value in the grid - so that
> the generated statement had @n=NULL.
This doesn't work for me either, so there's clearly something else going on.
I'm not running as admin. I'm accessing a server that's different than the
BT configuration server. Could those be problems? DTC is definitely
running on both servers, and they're both SQL2K.
--
Curt Hagenlocher
curt@hagenlocher.org
| |
| Jon Flanders 2004-12-28, 5:49 pm |
| That could be the problem Curt - but not having tried that configuration I
am not sure. Maybe someone else can pipe in.
--
Jon Flanders
http://staff.develop.com/jfland/
"Curt Hagenlocher" <faustus@pureevil.org> wrote in message
news:OiQs8HQ7EHA.3616@TK2MSFTNGP11.phx.gbl...
> "Jon Flanders" <jfland@develop.com> wrote in message
> news:OrozwDQ7EHA.3148@TK2MSFTNGP10.phx.gbl...
that[vbcol=seagreen]
>
> This doesn't work for me either, so there's clearly something else going
on.
>
> I'm not running as admin. I'm accessing a server that's different than
the
> BT configuration server. Could those be problems? DTC is definitely
> running on both servers, and they're both SQL2K.
>
> --
> Curt Hagenlocher
> curt@hagenlocher.org
>
>
| |
| Curt Hagenlocher 2004-12-28, 5:49 pm |
|
"Curt Hagenlocher" <faustus@pureevil.org> wrote in message
news:OiQs8HQ7EHA.3616@TK2MSFTNGP11.phx.gbl...
> I'm not running as admin. I'm accessing a server that's different than
> the BT configuration server. Could those be problems? DTC is definitely
> running on both servers, and they're both SQL2K.
These were easy enough variables to eliminate -- it turns out that when I
tried to use an sproc on the BT configuration server, I was able to get past
this point.
Thanks for your help!
--
Curt Hagenlocher
curt@hagenlocher.org
|
|
|
|