|
Home > Archive > BizTalk Server General > July 2004 > Add generated data using SQL adapter error.
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Add generated data using SQL adapter error.
|
|
| Patrick 2004-07-09, 3:31 pm |
| I'm calling this sproc using SQL adapter, here's a part of my sproc:
****************************************
CREATE procedure dbo.somesproc(@server sysname, @database sysname, @uid sysname, @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 [' + @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 correct. The procedure 'sp_addlinkedserver' cannot be executed within a transaction.
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. 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!
Thanks,
Pat
| |
| Darrin Curtis [MSFT] 2004-07-09, 3:31 pm |
| 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
| |
| Patrick 2004-07-09, 3:31 pm |
| Thank you very much for you reply Darrin.
Ok here's the sproc modified with the 'for xml auto, xmldata' added. I got the same error has before. Any work around this?
****************************************
*************
CREATE procedure dbo.somesproc(@server sysname, @database sysname, @uid sysname, @pwd sysname)
as
set nocount on
declare @ldt datetime
declare @sql varchar(2000)
-- select @ldt = last_download_time from global_constant where global_constant_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 [' + @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.frequently_called_numbers_sid = dr.sid and dr.table_name = 'FREQUENTLY_CALLED_NUMBERS' + 'for xml auto, xmldata'
delete from common_phones_faxes
from common_phones_faxes cpf inner join t_deleted_rows dr on cpf.common_phones_faxes_sid = dr.sid and dr.table_name = 'COMMON_PHONES_FAXES' + 'for xml auto, 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.table_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.table_name = 'GUIDE' + 'for xml auto, xmldata'
delete from country_territory
from country_territory ct inner join t_deleted_rows dr on ct.country_territory_sid = dr.sid and dr.table_name = 'COUNTRY_TERRITORY' + 'for xml auto, xmldata'
delete from territory
from territory t inner join t_deleted_rows dr on t.territory_sid = dr.sid and 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_char, changed_date into t_country from [' + @server+'].'+@database+'.dbo.country where changed_date > ''' + convert(varchar(128), @ldt, 121)+'''' + 'for 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_description, tc.country_2_char, tc.changed_date from t_country tc left outer join country 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_territory 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 tt.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.territory_sid is NULL
set identity_insert territory off
set @sql = 'select country_territory_sid, country_sid, territory_sid, changed_date into t_country_territory from [' + @server+'].'+@database+'.dbo.country_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_territory_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.territory_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_territory_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
>
>
| |
| Patrick 2004-07-09, 3:31 pm |
| Thank you for you replie Darrin.
Here's the sporc with the 'for xml auto, xmldata' added. I got the same error has before.
****************************************
******
CREATE procedure dbo.somesproc(@server sysname, @database sysname, @uid sysname, @pwd sysname)
as
set nocount on
declare @ldt datetime
declare @sql varchar(2000)
-- select @ldt = last_download_time from global_constant where global_constant_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 [' + @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.frequently_called_numbers_sid = dr.sid and dr.table_name = 'FREQUENTLY_CALLED_NUMBERS' + 'for xml auto, xmldata'
delete from common_phones_faxes
from common_phones_faxes cpf inner join t_deleted_rows dr on cpf.common_phones_faxes_sid = dr.sid and dr.table_name = 'COMMON_PHONES_FAXES' + 'for xml auto, 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.table_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.table_name = 'GUIDE' + 'for xml auto, xmldata'
delete from country_territory
from country_territory ct inner join t_deleted_rows dr on ct.country_territory_sid = dr.sid and dr.table_name = 'COUNTRY_TERRITORY' + 'for xml auto, xmldata'
delete from territory
from territory t inner join t_deleted_rows dr on t.territory_sid = dr.sid and 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_char, changed_date into t_country from [' + @server+'].'+@database+'.dbo.country where changed_date > ''' + convert(varchar(128), @ldt, 121)+'''' + 'for 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_description, tc.country_2_char, tc.changed_date from t_country tc left outer join country 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_territory 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 tt.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.territory_sid is NULL
set identity_insert territory off
set @sql = 'select country_territory_sid, country_sid, territory_sid, changed_date into t_country_territory from [' + @server+'].'+@database+'.dbo.country_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_territory_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.territory_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_territory_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
>
>
| |
| Patrick 2004-07-09, 3:31 pm |
| Thank you for you replie Darrin.
Here's the sporc with the 'for xml auto, xmldata' added. I got the same error has before.
****************************************
******
procedure dbo.somesproc(@server sysname, @database sysname, @uid sysname, @pwd sysname)
as
set nocount on
declare @ldt datetime
declare @sql varchar(2000)
-- select @ldt = last_download_time from global_constant where global_constant_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 [' + @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.frequently_called_numbers_sid = dr.sid and dr.table_name = 'FREQUENTLY_CALLED_NUMBERS' + 'for xml auto, xmldata'
delete from common_phones_faxes
from common_phones_faxes cpf inner join t_deleted_rows dr on cpf.common_phones_faxes_sid = dr.sid and dr.table_name = 'COMMON_PHONES_FAXES' + 'for xml auto, 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.table_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.table_name = 'GUIDE' + 'for xml auto, xmldata'
delete from country_territory
from country_territory ct inner join t_deleted_rows dr on ct.country_territory_sid = dr.sid and dr.table_name = 'COUNTRY_TERRITORY' + 'for xml auto, xmldata'
delete from territory
from territory t inner join t_deleted_rows dr on t.territory_sid = dr.sid and 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_char, changed_date into t_country from [' + @server+'].'+@database+'.dbo.country where changed_date > ''' + convert(varchar(128), @ldt, 121)+'''' + 'for 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_description, tc.country_2_char, tc.changed_date from t_country tc left outer join country 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_territory 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 tt.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.territory_sid is NULL
set identity_insert territory off
set @sql = 'select country_territory_sid, country_sid, territory_sid, changed_date into t_country_territory from [' + @server+'].'+@database+'.dbo.country_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_territory_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.territory_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_territory_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
>
>
|
|
|
|
|