- This topic has 5 replies, 1 voice, and was last updated 9 years, 6 months ago by
.
-
Posts
-
-
Hello all,
After completing the SQL Schema Generation Wizard (via Add Generated Items wizard), nothing happens. No new items are added to the project and I don't receive any errors. At the statement section of the wizard I execute the below stored proc. The last step I get to is Statement Information, where the stored proc is selected. After clicking Generate and then Next at this step nothing else happens. No errors or warnings appear in the Event Viewer.
CREATE PROCEDURE dbo.Transmission_Co_to_BizTalk_Sub_CoEntity
AS
DECLARE @Retrieve_Time DateTime
SET @Retrieve_Time=GetDate()
UPDATE Co_Entity_CoEntityChanged_Subscription
SET RetrievedTime = @Retrieve_Time
WHERE RetrievedTime is NULL
SELECT Co_Entity.Entity_ID, Co_Entity.Name, Co_Entity.Inactive, Co_Entity.OnSite, Co_Entity.Action
FROM Co_Entity
WHERE RetrievedTime = @Retrieve_Time for xml auto, elements, xmldata
GOAny thoughts on why this might be happening? Any suggestions would be greatly appreciated.
Thanks,
Shawn
-
I’m not really a SQL Adapter Expect, but I’d start by removing the where clause and the logic before the select. Just see if you can hit that to get the schema.
Also, make sure you do not have any unsupported data types like BLOB or XML DATA.
If you still have problems, maybe it’s a permissions issue with the account you are running under?
-
Thanks for the ideas Stephen. I tried simplying the stored proc per your suggestion but the schema still wouldn't generate. Monitoring SQL Profiler indicates that the wizard isn't even executing the stored proc. However, if I enable the sql receive port for which I'm trying to generate the schema then I can see that it does execute the stored proc. The data link settings are the same for both, so it seems that the account has sufficient permissions. I've also tried just running a select statement instead of the stored proc at the wizard. The wizard says to not include the xmldata predicate in select statements. Is this because the predicate's native schema builder handles what the predicate does? Should I also leave the predicate out of the stored proc?
Well the main thing I need to figure out now is why the wizard isn't even able to execute the stored prod.
-
-
I assume the proc runs where executed by isql. Have you tried having parameters in the procedure?
I usually have a begin and end statement in the proc, but maybe that is implicit.
Doug
-
I don't think the stored proc is at fault. Typically if the select statement or stored procedure is at fault you will get an error message.
Have you checked MSDTC connectivity to this SQL Server.
A quick check is to run the wizard and use a select statement you know will work
like: select * from sysobjects for xml auto-
Doug,
I did try adding a parameter to the stored proc but that didn't make a difference. Thanks for the tip.
Greg,
I tried running a simple select on the generator but I also so no activity in SQL Profiler when running it. Everything looks good in MSDTC. The same stored proc does execute from the SQL receive port, so permissions and communications seem ok.
Anyway, as a workaround, I deployed the BizTalk project to another machine on which both BT and SQL for BT run. I created a database on this machine with a table and stored proc identical to those from which I was trying to create the schema. Running the wizard on this machine, pointing to the local database's stored proc, the schema generator worked and I got my schema, which is now working fine on the original BT machine. When I have some free time I'll try to troubleshoot the original problem out of curiosity but for now I have a workaround. I'll be sure to post what I find out.
Thanks to everyone for the suggestions. They're much appreciated!
– Shawn
-
-
-
- The forum ‘BizTalk 2004 – BizTalk 2010’ is closed to new topics and replies.