I'm calling this sproc using SQL adapter, here's a part of my sproc:

 ****************************************

CREATE procedure dbo.somesproc(@server sysname, @database sysname, @uid sysn
ame, @pwd sysname)
as
declare @ldt datetime
declare @sql varchar(2000)

set @ldt = '1955-07-28'

EXEC sp_addlinkedserver @server=@server, @srvproduct=N'SQL Server'
exec sp_addlinkedsrvlogin @server, false, NULL, @uid, @pwd


set @sql = 'SELECT sid, table_name, deleted_date into t_deleted_rows from &#
91;' + @server+'].'+@database+'.dbo.deleted_rows where deleted_date > ''' + 
convert(varchar(128), @ldt, 121)+''''
print @sql
execute (@sql)
 ****************************************
******

Here's the error I get:

---------------------------
Error
---------------------------
Failed to execute SQL Statement. Please ensure that the supplied syntax is c
orrect. The procedure 'sp_addlinkedserver' cannot be executed within a trans
action.

The procedure 'sp_addlinkedsrvlogin' cannot be executed within a transaction
.

Invalid object name 't_deleted_rows'.
-------------------------------------------------

Remember that i'm not executing it yet. I simply want to get the schema. Wha
t transaction? Can't biztalk handle temp tables? Or is it the "exec sp_" spr
oc the issue? I run this sproc in Query Analyzer and it runs perfectly...hel
p please!

Thanks,

Pat

[ Post a follow-up to this message ]

The Wizard need to be able to run the SQL command within a transaction so
that it can use the returned XML data to generate the schema. It is within
a transaction so that the action can be rolled back (i.e., the XML data is
returned from the sproc but the actions the sproc normally take don't
happen).

I don't know that much about 'sp_addlinkedserver' but if it can't be
included in a transaction it will not be able to be used by SQL Adapter.

Also, I don't see the 'for xml auto, xmldata' tag in your select statement
below. Not sure if you just didn't include it in this post or if I am
missing something, but you would not be able to generate a schema without
it.

HTH

Thanks,
Darrin

--------------------[vbcol=seagreen] 
sysname, @pwd sysname)[vbcol=seagreen] 
[' + @server+'].'+@database+'.dbo.deleted_rows where deleted_date > ''' 
+
convert(varchar(128), @ldt, 121)+''''[vbcol=seagreen] 
is correct. The procedure 'sp_addlinkedserver' cannot be executed within a
transaction.[vbcol=seagreen] 
transaction.[vbcol=seagreen] 
What transaction? Can't biztalk handle temp tables? Or is it the "exec sp_"
sproc the issue? I run this sproc in Query Analyzer and it runs
perfectly...help please![vbcol=seagreen] 

This posting is provided "AS IS" with no warranties, and confers no rights.

EBusiness Server Team

[ Post a follow-up to this message ]

Thank you very much for you reply Darrin.

Ok here's the sproc modified with the 'for xml auto, xmldata' added. I got t
he same error has before. Any work around this?

 ****************************************
*************
CREATE procedure dbo.somesproc(@server sysname, @database sysname, @uid sysn
ame, @pwd sysname)
as
set nocount on

declare @ldt datetime
declare @sql varchar(2000)

-- select @ldt = last_download_time from global_constant where global_consta
nt_sid = 1
set @ldt = '1955-07-28'

EXEC sp_addlinkedserver @server=@server, @srvproduct=N'SQL Server'
exec sp_addlinkedsrvlogin @server, false, NULL, @uid, @pwd


set @sql = 'SELECT sid, table_name, deleted_date into t_deleted_rows from &#
91;' + @server+'].'+@database+'.dbo.deleted_rows where deleted_date > ''' + 
convert(varchar(128), @ldt, 121)+'''' + 'for xml auto, xmldata'
print @sql
execute (@sql)

delete from frequently_called_numbers
from frequently_called_numbers fcn inner join t_deleted_rows dr on fcn.frequ
ently_called_numbers_sid = dr.sid and dr.table_name = 'FREQUENTLY_CALLED_NUM
BERS' + 'for xml auto, xmldata'
delete from common_phones_faxes
from common_phones_faxes cpf inner join t_deleted_rows dr on cpf.common_phon
es_faxes_sid = dr.sid and dr.table_name = 'COMMON_PHONES_FAXES' + 'for xml a
uto, xmldata'
delete from employee
from employee e inner join t_deleted_rows dr on e.employee_sid = dr.sid and 
dr.table_name = 'EMPLOYEE' + 'for xml auto, xmldata'
delete from department
from department d inner join t_deleted_rows dr on d.department_sid = dr.sid 
and dr.table_name = 'DEPARTMENT' + 'for xml auto, xmldata'
delete from business
from business b inner join t_deleted_rows dr on b.business_sid = dr.sid and 
dr.table_name = 'BUSINESS' + 'for xml auto, xmldata'
delete from region
from region r inner join t_deleted_rows dr on r.region_sid = dr.sid and dr.t
able_name = 'REGION' + 'for xml auto, xmldata'
delete from guide
from guide g inner join t_deleted_rows dr on g.guide_sid = dr.sid and dr.tab
le_name = 'GUIDE' + 'for xml auto, xmldata'
delete from country_territory
from country_territory ct inner join t_deleted_rows dr on ct.country_territo
ry_sid = dr.sid and dr.table_name = 'COUNTRY_TERRITORY' + 'for xml auto, xml
data'
delete from territory
from territory t inner join t_deleted_rows dr on t.territory_sid = dr.sid an
d dr.table_name = 'TERRITORY' + 'for xml auto, xmldata'
delete from country
from country c inner join t_deleted_rows dr on c.country_sid = dr.sid and dr
.table_name = 'COUNTRY' + 'for xml auto, xmldata'


set @sql = 'select country_sid, description, short_description, country_2_ch
ar, changed_date into t_country from [' + @server+'].'+@database+'.dbo.c
ountry where changed_date > ''' + convert(varchar(128), @ldt, 121)+'''' + 'f
or xml auto, xmldata'
print @sql
execute (@sql)
update country
set description = tc.description,
short_description = tc.short_description,
country_2_char = tc.country_2_char,
changed_date = tc.changed_date
from country c inner join t_country tc on c.country_sid = tc.country_sid + '
for xml auto, xmldata'

set identity_insert country on
insert into country (country_sid, description, short_description, country_2_
char, changed_date) select tc.country_sid, tc.description, tc.short_descript
ion, tc.country_2_char, tc.changed_date from t_country tc left outer join co
untry c on tc.country_sid
= c.country_sid where c.country_sid is NULL
set identity_insert country off

set @sql = 'select territory_sid, territory_name, changed_date into t_territ
ory from [' + @server+'].'+@database+'.dbo.territory where changed_date 
> ''' + convert(varchar(128), @ldt, 121)+'''' + 'for xml auto, xmldata'
print @sql
execute (@sql)
update territory
set territory_name = tt.territory_name,
changed_date = tt.changed_date
from territory t inner join t_territory tt on t.territory_sid = tt.territory
_sid + 'for xml auto, xmldata'

set identity_insert territory on
insert into territory (territory_sid, territory_name, changed_date) select t
t.territory_sid, tt.territory_name, tt.changed_date from t_territory tt left
 outer join territory t on tt.territory_sid = t.territory_sid where t.territ
ory_sid is NULL
set identity_insert territory off

set @sql = 'select country_territory_sid, country_sid, territory_sid, change
d_date into t_country_territory from [' + @server+'].'+@database+'.dbo.c
ountry_territory where changed_date > ''' + convert(varchar(128), @ldt, 121)
+''''
print @sql
execute (@sql)
update country_territory
set country_sid = tct.country_sid,
territory_sid = tct.territory_sid,
changed_date = tct.changed_date
from country_territory ct inner join t_country_territory tct on ct.country_t
erritory_sid = tct.country_territory_sid + 'for xml auto, xmldata'

set identity_insert country_territory on
insert into country_territory (country_territory_sid, country_sid, territory
_sid, changed_date) select tct.country_territory_sid, tct.country_sid, tct.t
erritory_sid, tct.changed_date from t_country_territory tct left outer join 
country_territory ct on tc
t.country_territory_sid = ct.country_territory_sid where ct.country_territor
y_sid is NULL
set identity_insert country_territory off


drop table t_country_territory
drop table t_territory
drop table t_country
drop table t_deleted_rows
exec sp_droplinkedsrvlogin @server,NULL
exec sp_dropserver @server
GO
 ****************************************
******

"Darrin Curtis [MSFT]" wrote:

> The Wizard need to be able to run the SQL command within a transaction so
> that it can use the returned XML data to generate the schema. It is within
> a transaction so that the action can be rolled back (i.e., the XML data is
> returned from the sproc but the actions the sproc normally take don't
> happen).
>
> I don't know that much about 'sp_addlinkedserver' but if it can't be
> included in a transaction it will not be able to be used by SQL Adapter.
>
> Also, I don't see the 'for xml auto, xmldata' tag in your select statement
> below. Not sure if you just didn't include it in this post or if I am
> missing something, but you would not be able to generate a schema without
> it.
>
> HTH
>
> Thanks,
> Darrin
>
> -------------------- 
> sysname, @pwd sysname) 
> [' + @server+'].'+@database+'.dbo.deleted_rows where deleted_date > ''
' +
> convert(varchar(128), @ldt, 121)+'''' 
> is correct. The procedure 'sp_addlinkedserver' cannot be executed within a
> transaction. 
> transaction. 
> What transaction? Can't biztalk handle temp tables? Or is it the "exec sp_
"
> sproc the issue? I run this sproc in Query Analyzer and it runs
> perfectly...help please! 
>
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> EBusiness Server Team
>
>

[ Post a follow-up to this message ]

Thank you for you replie Darrin.

Here's the sporc with the 'for xml auto, xmldata' added. I got the same erro
r has before.

 ****************************************
******
CREATE procedure dbo.somesproc(@server sysname, @database sysname, @uid sysn
ame, @pwd sysname)
as
set nocount on

declare @ldt datetime
declare @sql varchar(2000)

-- select @ldt = last_download_time from global_constant where global_consta
nt_sid = 1
set @ldt = '1955-07-28'

EXEC sp_addlinkedserver @server=@server, @srvproduct=N'SQL Server'
exec sp_addlinkedsrvlogin @server, false, NULL, @uid, @pwd


set @sql = 'SELECT sid, table_name, deleted_date into t_deleted_rows from &#
91;' + @server+'].'+@database+'.dbo.deleted_rows where deleted_date > ''' + 
convert(varchar(128), @ldt, 121)+'''' + 'for xml auto, xmldata'
print @sql
execute (@sql)

delete from frequently_called_numbers
from frequently_called_numbers fcn inner join t_deleted_rows dr on fcn.frequ
ently_called_numbers_sid = dr.sid and dr.table_name = 'FREQUENTLY_CALLED_NUM
BERS' + 'for xml auto, xmldata'
delete from common_phones_faxes
from common_phones_faxes cpf inner join t_deleted_rows dr on cpf.common_phon
es_faxes_sid = dr.sid and dr.table_name = 'COMMON_PHONES_FAXES' + 'for xml a
uto, xmldata'
delete from employee
from employee e inner join t_deleted_rows dr on e.employee_sid = dr.sid and 
dr.table_name = 'EMPLOYEE' + 'for xml auto, xmldata'
delete from department
from department d inner join t_deleted_rows dr on d.department_sid = dr.sid 
and dr.table_name = 'DEPARTMENT' + 'for xml auto, xmldata'
delete from business
from business b inner join t_deleted_rows dr on b.business_sid = dr.sid and 
dr.table_name = 'BUSINESS' + 'for xml auto, xmldata'
delete from region
from region r inner join t_deleted_rows dr on r.region_sid = dr.sid and dr.t
able_name = 'REGION' + 'for xml auto, xmldata'
delete from guide
from guide g inner join t_deleted_rows dr on g.guide_sid = dr.sid and dr.tab
le_name = 'GUIDE' + 'for xml auto, xmldata'
delete from country_territory
from country_territory ct inner join t_deleted_rows dr on ct.country_territo
ry_sid = dr.sid and dr.table_name = 'COUNTRY_TERRITORY' + 'for xml auto, xml
data'
delete from territory
from territory t inner join t_deleted_rows dr on t.territory_sid = dr.sid an
d dr.table_name = 'TERRITORY' + 'for xml auto, xmldata'
delete from country
from country c inner join t_deleted_rows dr on c.country_sid = dr.sid and dr
.table_name = 'COUNTRY' + 'for xml auto, xmldata'


set @sql = 'select country_sid, description, short_description, country_2_ch
ar, changed_date into t_country from [' + @server+'].'+@database+'.dbo.c
ountry where changed_date > ''' + convert(varchar(128), @ldt, 121)+'''' + 'f
or xml auto, xmldata'
print @sql
execute (@sql)
update country
set description = tc.description,
short_description = tc.short_description,
country_2_char = tc.country_2_char,
changed_date = tc.changed_date
from country c inner join t_country tc on c.country_sid = tc.country_sid + '
for xml auto, xmldata'

set identity_insert country on
insert into country (country_sid, description, short_description, country_2_
char, changed_date) select tc.country_sid, tc.description, tc.short_descript
ion, tc.country_2_char, tc.changed_date from t_country tc left outer join co
untry c on tc.country_sid
= c.country_sid where c.country_sid is NULL
set identity_insert country off

set @sql = 'select territory_sid, territory_name, changed_date into t_territ
ory from [' + @server+'].'+@database+'.dbo.territory where changed_date 
> ''' + convert(varchar(128), @ldt, 121)+'''' + 'for xml auto, xmldata'
print @sql
execute (@sql)
update territory
set territory_name = tt.territory_name,
changed_date = tt.changed_date
from territory t inner join t_territory tt on t.territory_sid = tt.territory
_sid + 'for xml auto, xmldata'

set identity_insert territory on
insert into territory (territory_sid, territory_name, changed_date) select t
t.territory_sid, tt.territory_name, tt.changed_date from t_territory tt left
 outer join territory t on tt.territory_sid = t.territory_sid where t.territ
ory_sid is NULL
set identity_insert territory off

set @sql = 'select country_territory_sid, country_sid, territory_sid, change
d_date into t_country_territory from [' + @server+'].'+@database+'.dbo.c
ountry_territory where changed_date > ''' + convert(varchar(128), @ldt, 121)
+''''
print @sql
execute (@sql)
update country_territory
set country_sid = tct.country_sid,
territory_sid = tct.territory_sid,
changed_date = tct.changed_date
from country_territory ct inner join t_country_territory tct on ct.country_t
erritory_sid = tct.country_territory_sid + 'for xml auto, xmldata'

set identity_insert country_territory on
insert into country_territory (country_territory_sid, country_sid, territory
_sid, changed_date) select tct.country_territory_sid, tct.country_sid, tct.t
erritory_sid, tct.changed_date from t_country_territory tct left outer join 
country_territory ct on tc
t.country_territory_sid = ct.country_territory_sid where ct.country_territor
y_sid is NULL
set identity_insert country_territory off


drop table t_country_territory
drop table t_territory
drop table t_country
drop table t_deleted_rows
exec sp_droplinkedsrvlogin @server,NULL
exec sp_dropserver @server
GO

 ****************************************
******

"Darrin Curtis [MSFT]" wrote:

> The Wizard need to be able to run the SQL command within a transaction so
> that it can use the returned XML data to generate the schema. It is within
> a transaction so that the action can be rolled back (i.e., the XML data is
> returned from the sproc but the actions the sproc normally take don't
> happen).
>
> I don't know that much about 'sp_addlinkedserver' but if it can't be
> included in a transaction it will not be able to be used by SQL Adapter.
>
> Also, I don't see the 'for xml auto, xmldata' tag in your select statement
> below. Not sure if you just didn't include it in this post or if I am
> missing something, but you would not be able to generate a schema without
> it.
>
> HTH
>
> Thanks,
> Darrin
>
> -------------------- 
> sysname, @pwd sysname) 
> [' + @server+'].'+@database+'.dbo.deleted_rows where deleted_date > ''
' +
> convert(varchar(128), @ldt, 121)+'''' 
> is correct. The procedure 'sp_addlinkedserver' cannot be executed within a
> transaction. 
> transaction. 
> What transaction? Can't biztalk handle temp tables? Or is it the "exec sp_
"
> sproc the issue? I run this sproc in Query Analyzer and it runs
> perfectly...help please! 
>
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> EBusiness Server Team
>
>

[ Post a follow-up to this message ]

Thank you for you replie Darrin.

Here's the sporc with the 'for xml auto, xmldata' added. I got the same erro
r has before.

 ****************************************
******
procedure dbo.somesproc(@server sysname, @database sysname, @uid sysname, @p
wd sysname)
as
set nocount on

declare @ldt datetime
declare @sql varchar(2000)

-- select @ldt = last_download_time from global_constant where global_consta
nt_sid = 1
set @ldt = '1955-07-28'

EXEC sp_addlinkedserver @server=@server, @srvproduct=N'SQL Server'
exec sp_addlinkedsrvlogin @server, false, NULL, @uid, @pwd


set @sql = 'SELECT sid, table_name, deleted_date into t_deleted_rows from &#
91;' + @server+'].'+@database+'.dbo.deleted_rows where deleted_date > ''' + 
convert(varchar(128), @ldt, 121)+'''' + 'for xml auto, xmldata'
print @sql
execute (@sql)

delete from frequently_called_numbers
from frequently_called_numbers fcn inner join t_deleted_rows dr on fcn.frequ
ently_called_numbers_sid = dr.sid and dr.table_name = 'FREQUENTLY_CALLED_NUM
BERS' + 'for xml auto, xmldata'
delete from common_phones_faxes
from common_phones_faxes cpf inner join t_deleted_rows dr on cpf.common_phon
es_faxes_sid = dr.sid and dr.table_name = 'COMMON_PHONES_FAXES' + 'for xml a
uto, xmldata'
delete from employee
from employee e inner join t_deleted_rows dr on e.employee_sid = dr.sid and 
dr.table_name = 'EMPLOYEE' + 'for xml auto, xmldata'
delete from department
from department d inner join t_deleted_rows dr on d.department_sid = dr.sid 
and dr.table_name = 'DEPARTMENT' + 'for xml auto, xmldata'
delete from business
from business b inner join t_deleted_rows dr on b.business_sid = dr.sid and 
dr.table_name = 'BUSINESS' + 'for xml auto, xmldata'
delete from region
from region r inner join t_deleted_rows dr on r.region_sid = dr.sid and dr.t
able_name = 'REGION' + 'for xml auto, xmldata'
delete from guide
from guide g inner join t_deleted_rows dr on g.guide_sid = dr.sid and dr.tab
le_name = 'GUIDE' + 'for xml auto, xmldata'
delete from country_territory
from country_territory ct inner join t_deleted_rows dr on ct.country_territo
ry_sid = dr.sid and dr.table_name = 'COUNTRY_TERRITORY' + 'for xml auto, xml
data'
delete from territory
from territory t inner join t_deleted_rows dr on t.territory_sid = dr.sid an
d dr.table_name = 'TERRITORY' + 'for xml auto, xmldata'
delete from country
from country c inner join t_deleted_rows dr on c.country_sid = dr.sid and dr
.table_name = 'COUNTRY' + 'for xml auto, xmldata'


set @sql = 'select country_sid, description, short_description, country_2_ch
ar, changed_date into t_country from [' + @server+'].'+@database+'.dbo.c
ountry where changed_date > ''' + convert(varchar(128), @ldt, 121)+'''' + 'f
or xml auto, xmldata'
print @sql
execute (@sql)
update country
set description = tc.description,
short_description = tc.short_description,
country_2_char = tc.country_2_char,
changed_date = tc.changed_date
from country c inner join t_country tc on c.country_sid = tc.country_sid + '
for xml auto, xmldata'

set identity_insert country on
insert into country (country_sid, description, short_description, country_2_
char, changed_date) select tc.country_sid, tc.description, tc.short_descript
ion, tc.country_2_char, tc.changed_date from t_country tc left outer join co
untry c on tc.country_sid
= c.country_sid where c.country_sid is NULL
set identity_insert country off

set @sql = 'select territory_sid, territory_name, changed_date into t_territ
ory from [' + @server+'].'+@database+'.dbo.territory where changed_date 
> ''' + convert(varchar(128), @ldt, 121)+'''' + 'for xml auto, xmldata'
print @sql
execute (@sql)
update territory
set territory_name = tt.territory_name,
changed_date = tt.changed_date
from territory t inner join t_territory tt on t.territory_sid = tt.territory
_sid + 'for xml auto, xmldata'

set identity_insert territory on
insert into territory (territory_sid, territory_name, changed_date) select t
t.territory_sid, tt.territory_name, tt.changed_date from t_territory tt left
 outer join territory t on tt.territory_sid = t.territory_sid where t.territ
ory_sid is NULL
set identity_insert territory off

set @sql = 'select country_territory_sid, country_sid, territory_sid, change
d_date into t_country_territory from [' + @server+'].'+@database+'.dbo.c
ountry_territory where changed_date > ''' + convert(varchar(128), @ldt, 121)
+''''
print @sql
execute (@sql)
update country_territory
set country_sid = tct.country_sid,
territory_sid = tct.territory_sid,
changed_date = tct.changed_date
from country_territory ct inner join t_country_territory tct on ct.country_t
erritory_sid = tct.country_territory_sid + 'for xml auto, xmldata'

set identity_insert country_territory on
insert into country_territory (country_territory_sid, country_sid, territory
_sid, changed_date) select tct.country_territory_sid, tct.country_sid, tct.t
erritory_sid, tct.changed_date from t_country_territory tct left outer join 
country_territory ct on tc
t.country_territory_sid = ct.country_territory_sid where ct.country_territor
y_sid is NULL
set identity_insert country_territory off


drop table t_country_territory
drop table t_territory
drop table t_country
drop table t_deleted_rows
exec sp_droplinkedsrvlogin @server,NULL
exec sp_dropserver @server

 ****************************************
******

"Darrin Curtis [MSFT]" wrote:

> The Wizard need to be able to run the SQL command within a transaction so
> that it can use the returned XML data to generate the schema. It is within
> a transaction so that the action can be rolled back (i.e., the XML data is
> returned from the sproc but the actions the sproc normally take don't
> happen).
>
> I don't know that much about 'sp_addlinkedserver' but if it can't be
> included in a transaction it will not be able to be used by SQL Adapter.
>
> Also, I don't see the 'for xml auto, xmldata' tag in your select statement
> below. Not sure if you just didn't include it in this post or if I am
> missing something, but you would not be able to generate a schema without
> it.
>
> HTH
>
> Thanks,
> Darrin
>
> -------------------- 
> sysname, @pwd sysname) 
> [' + @server+'].'+@database+'.dbo.deleted_rows where deleted_date > ''
' +
> convert(varchar(128), @ldt, 121)+'''' 
> is correct. The procedure 'sp_addlinkedserver' cannot be executed within a
> transaction. 
> transaction. 
> What transaction? Can't biztalk handle temp tables? Or is it the "exec sp_
"
> sproc the issue? I run this sproc in Query Analyzer and it runs
> perfectly...help please! 
>
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> EBusiness Server Team
>
>

[ Post a follow-up to this message ]