MSSMS 05 generates all stored procedures as dynamic sql

Hi,
For some odd reason my MSSMS05 has started generating all stored
procedure scripts as dynamic sql. I cannot seem to find any options
under options-->scripting that seem related to this.
To clarify, when right-clicking a stored procedure and selecting
'modify' or any of the options under 'Script stored procedure as', the
output is wrapped as a dynamic sql statement, wrapped in exec
sp_executesql statements.
What option have I foolishly turned on, and where can I turn it off
again?
best regards,
Henrik
Try the "Include IF NOT EXISTS clause" option under Tools | Options /
Scripting. Since this requires an IF block, and you can't have a GO inside
of this, you need to use dynamic SQL.
"Henrik" <nielsen.henrik@.gmail.com> wrote in message
news:185b136f-b493-4047-bb95-e96b44713c2c@.j44g2000hsj.googlegroups.com...
> Hi,
> For some odd reason my MSSMS05 has started generating all stored
> procedure scripts as dynamic sql. I cannot seem to find any options
> under options-->scripting that seem related to this.
> To clarify, when right-clicking a stored procedure and selecting
> 'modify' or any of the options under 'Script stored procedure as', the
> output is wrapped as a dynamic sql statement, wrapped in exec
> sp_executesql statements.
> What option have I foolishly turned on, and where can I turn it off
> again?
> best regards,
> Henrik
|||That did it, thank you very much Aaron!
On Dec 4, 1:18 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:[vbcol=seagreen]
> Try the "Include IF NOT EXISTS clause" option under Tools | Options /
> Scripting. Since this requires an IF block, and you can't have a GO inside
> of this, you need to use dynamic SQL.
> "Henrik" <nielsen.hen...@.gmail.com> wrote in message
> news:185b136f-b493-4047-bb95-e96b44713c2c@.j44g2000hsj.googlegroups.com...
>
>
sql

Msg 3616 Transaction doomed, Triggers and Error logging

I am trying to use the uspLogError and uspPrintError stored procedures found in AdventureWorks and I have a problem. I have a stored procedure called Is_uspProcessLeads that processes some customer information that is in xml format. Part of the process is to check to make sure that the xml is well formed and if it is validated. Each step along the way, Is_uspProcessLeads updates a status field to indicate how far along in the process the xml file got, and if there is an error, to capture that error in a log file. The error handling process uses a try..catch logic.

Is_uspProcessLeads does it's processing against a table called [Is.Leads] which contains the following fields...
IsLead_Id Primary Key, int
IsLead_XmlRaw varchar(max)
IsLead_XmlWellFormed untyped Xml data type
IsLead_XmlValidated typed Xml data type with a related xsd file
IsLead_LeadStatus_Id Status code field

So here's the problem. If I run Is_uspProcessLeads manually and I get an error (like not well formed) then it blows up as I would expect and logs the error correctly. But what I want to do is run Is_uspProcessLeads from the Insert trigger of [Is.Leads]. When I try that, I get an unexpected error in uspLogError when it tries to insert error information in the Error log table, therefore preventing me from logging my errors.

Here is the errors I get
V
Error: 3930
Severity: 16
State: 1
Procedure: IsuspLogError
Line: 43
ProcessLocationMsg: Confirm that the raw xml is well formed
Lead_Id: 20
ERROR_MESSAGE...
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
^
Msg 3616, Level 16, State 1, Line 12
Transaction doomed in trigger. Batch has been aborted.

Got any Ideas why this is happening?

I searched for the 3616 error message, and got nothing (I do however like the message about being doomed). It looks like SQL Server is treating the trigger event as a transaction

FYI: The error message inside the dashed lines comes from uspPrintError, which is executed inside the catch block of uspLogError

This error occurs when you try to make any modifications after a transaction that needs to be rolled back. You can detect this using XACT_STATE function in the CATCH block. If the value is -1 it indicates that the transaction cannot be committed and you need to issue a rollback so subsequent transactions cannot proceed. If you can post a simple repro of the problem it will be easier to suggest the modification.|||

The stored procedured Is_uspLogError already checks XACT_STATE Function, see below. Below are the relative three stored procs.

1) Is_trgLead_InsertTrigger is the trigger,

2) Is_uspProcessLeads has the try catch block of code and

3) Is_uspLogError logs the error

ALTER TRIGGER [Is_trgLead_InsertTrigger]
ON [dbo].[Is.Lead]
AFTER INSERT --FOR INSERT ?
AS
BEGIN
SET NOCOUNT ON; -- prevent extra result sets from interfering with SELECT statements.

-- =============================================
-- Declaration
-- =============================================
DECLARE @.ReturnCode int
DECLARE @.Lead_Id int
DECLARE @.FTP_Id int
DECLARE @.ProcessLocationMsg varchar(255)

Set @.ProcessLocationMsg = 'Initialization'
If (select Skip_IsLead_Trigger from dbo.[Is.Environment]) = 1
Return

SELECT
@.Lead_Id = IsLead_Id,
@.FTP_Id = IsLead_FTP_Id
from Inserted

Set @.ProcessLocationMsg = 'Call sproc IsProcessLeads'
EXEC @.ReturnCode = [Is_uspProcessLeads]
@.IsLead_Id = @.Lead_Id,
@.IsFTP_Id = @.FTP_Id

END

ALTER PROC [dbo].[Is_uspProcessLeads]
@.IsLead_Id int,
@.IsFTP_Id int
AS
/*

USE [Integration]
GO

DECLARE @.return_value int

EXEC @.return_value = [dbo].[IsProcessLeads]
@.IsLead_Id = 1,
@.IsFTP_Id = 1

SELECT 'Return Value' = @.return_value
GO

select * from dbo.[Is.ErrorLog]
Go

*/
BEGIN

--Declarations.

DECLARE @.FTP_TYPE_ID_AHG int -- American Home Guides
DECLARE @.FTP_TYPE_ID_BHI int -- Builder Homesite Inc
DECLARE @.FTP_TYPE_ID_HB int -- Homebuilder
DECLARE @.FTP_TYPE_ID_INEST int -- iNest
DECLARE @.FTP_TYPE_ID_RYLAND int -- Ryland.Com

DECLARE @.LEAD_STATUS_ID_START int
DECLARE @.LEAD_STATUS_ID_Completed int
DECLARE @.LEAD_STATUS_ID_XmlWellFormed int
DECLARE @.LEAD_STATUS_ID_XmlValid int
--SET @.LEAD_STATUS_ID_RaisedError int
DECLARE @.LEAD_STATUS_ID_EmailedCLEAR int
DECLARE @.LEAD_STATUS_ID_EmailedCustomer int

Declare @.CLEAREmail nvarchar(255)
Declare @.CLEAREmailMsg nvarchar(255)

Declare @.AutoResponseEmail nvarchar(255)
Declare @.AutoResponseEmailMsg nvarchar(255)

DECLARE @.ReturnCode int
Declare @.ProcessLocationMsg nvarchar(255)

--Initialization.
SET @.FTP_TYPE_ID_AHG = 1
SET @.FTP_TYPE_ID_BHI = 4
SET @.FTP_TYPE_ID_HB = 6
SET @.FTP_TYPE_ID_INEST = 7
SET @.FTP_TYPE_ID_RYLAND = 0

SET @.LEAD_STATUS_ID_START = 1
SET @.LEAD_STATUS_ID_Completed = 2
SET @.LEAD_STATUS_ID_XmlWellFormed = 3
SET @.LEAD_STATUS_ID_XmlValid = 4
--SET @.LEAD_STATUS_ID_RaisedError
SET @.LEAD_STATUS_ID_EmailedCLEAR = 6
SET @.LEAD_STATUS_ID_EmailedCustomer = 7

-- Confirm that the raw xml is well formed and valid, by updating the specific xml field
-- which is determined by the FTP_TYPE_ID, with the Raw xml

BEGIN TRY

Set @.ProcessLocationMsg = 'Confirm that the raw xml is well formed'
UPDATE [Is.Lead]
SET IsLead_XmlWellFormed = IsLead_XmlRaw,
IsLead_LeadStatus_Id = @.LEAD_STATUS_ID_XmlWellFormed
WHERE IsLead_Id = @.IsLead_Id

Set @.ProcessLocationMsg = 'Confirm that the well formed xml is valid'
IF @.IsFTP_Id = @.FTP_TYPE_ID_AHG
UPDATE [Is.Lead]
SET IsLead_XmlAHG = IsLead_XmlWellFormed
WHERE IsLead_Id = @.IsLead_Id
Else IF @.IsFTP_Id = @.FTP_TYPE_ID_BHI
UPDATE [Is.Lead]
SET IsLead_XmlBHI = IsLead_XmlWellFormed
WHERE IsLead_Id = @.IsLead_Id
Else IF @.IsFTP_Id = @.FTP_TYPE_ID_HB
UPDATE [Is.Lead]
SET IsLead_XmlHB = IsLead_XmlWellFormed
WHERE IsLead_Id = @.IsLead_Id
Else IF @.IsFTP_Id = @.FTP_TYPE_ID_INest
UPDATE [Is.Lead]
SET IsLead_XmliNest = IsLead_XmlWellFormed
WHERE IsLead_Id = @.IsLead_Id

-- Update status to show the xml is valid
Set @.ProcessLocationMsg = 'Update status to show the xml is valid'
UPDATE [Is.Lead]
SET IsLead_LeadStatus_Id = @.LEAD_STATUS_ID_XmlValid
WHERE IsLead_Id = @.IsLead_Id

-- Update status to show the xml is valid
Set @.ProcessLocationMsg = 'Get the count of leads'
UPDATE [Is.Lead]
SET IsLead_CountOfLeads = ([dbo].[Is_ufnCountOfLeads](@.IsLead_Id, @.IsFTP_Id))
WHERE IsLead_Id = @.IsLead_Id

Proc_Exit:

END TRY
BEGIN CATCH
PRINT 'Error inside Is_uspProcessLeads; calling Is_uspLogError'
EXECUTE Is_uspLogError @.IsLead_Id, @.ProcessLocationMsg;
END CATCH;

RETURN --@.@.ERROR

End

/*
IsuspLogError logs error information in the ErrorLog table about the
error that caused execution to jump to the CATCH block of a
TRY...CATCH construct. This should be executed from within the scope
of a CATCH block otherwise it will return without inserting error
information.
*/
ALTER PROCEDURE [dbo].[Is_uspLogError]
@.Lead_Id int,
@.ProcessLocationMsg varchar(255) = '',
@.ErrorLogID int = 0 OUTPUT -- contains the ErrorLogID of the row inserted
AS -- by IsuspLogError in the ErrorLog table
BEGIN
SET NOCOUNT ON;

-- Output parameter value of 0 indicates that error
-- information was not logged
SET @.ErrorLogID = 0;

BEGIN TRY
-- Return if there is no error information to log
IF ERROR_NUMBER() IS NULL
RETURN;

-- Return if inside an uncommittable transaction.
-- Data insertion/modification is not allowed when
-- a transaction is in an uncommittable state.
IF XACT_STATE() = -1
BEGIN
PRINT 'Cannot log error since the current transaction is in an uncommittable state. '
+ 'Rollback the transaction before executing IsuspLogError in order to successfully log error information.';
RETURN;
END

PRINT 'Inside Is_uspLogError, Before the insertion of [Is.ErrorLog]'
INSERT [dbo].[Is.ErrorLog]
(
[IsErrorLog_UserName],
[IsErrorLog_Number],
[IsErrorLog_Severity],
[IsErrorLog_State],
[IsErrorLog_Procedure],
[IsErrorLog_Line],
[IsErrorLog_ProcessLocationMsg],
[IsErrorLog_Message],
[IsErrorLog_Lead_Id]
)
VALUES
(
CONVERT(sysname, CURRENT_USER),
ERROR_NUMBER(),
ERROR_SEVERITY(),
ERROR_STATE(),
ERROR_PROCEDURE(),
ERROR_LINE(),
@.ProcessLocationMsg,
ERROR_MESSAGE(),
@.Lead_Id);
-- Pass back the ErrorLogID of the row inserted
SET @.ErrorLogID = @.@.IDENTITY;
END TRY
BEGIN CATCH
PRINT 'Inside Isusp_LogError!Begin Catch ';
EXECUTE [dbo].[Is_uspPrintError] @.Lead_Id, @.ProcessLocationMsg;
RETURN -1;
END CATCH
END;

|||

FYI: I did some more testing to see what the value of XACT_STATE() is, and it doesn't change, it's always zero. Inside Is_uspLogError, I did a print before the Insert statement, and inside the Catch statement, and it was always 0.

Error inside Is_uspProcessLeads; calling Is_uspLogError

1 XACT_STATE(): 0

Inside Is_uspLogError, Before the insertion of [Is.ErrorLog]

2 XACT_STATE(): 0

Inside Isusp_LogError!Begin Catch

|||

Since you are calling the SP inside the trigger all errors needs to be rolled back before you performing any writes. So in the Is_uspProcessLeads CATCH block you are missing the XACT_STATE() check and rollback. See below lines:

BEGIN CATCH

IF XACT_STATE() = -1 ROLLBACK - add this line here:
PRINT 'Error inside Is_uspProcessLeads; calling Is_uspLogError'
EXECUTE Is_uspLogError @.IsLead_Id, @.ProcessLocationMsg;
END CATCH;

Or you may just need to rollback the transaction without checking for XACT_STATE().

|||

Thanks for the response,

The problem with your solution is that I don't want to roll back the data that gets inserted in table [Is.Lead]. I want it there, so I can report what went wrong. If do a rollback, I will have an entry in the [Is.ErrorLog] table but nothing in [Is.Lead].

I guess my design is flawed, do you have any suggestions?

I think I need to remove the insert trigger, and have Is_uspProcessLeads executed some other way. I suppose I could do this by putting Is_uspProcessLeads on the scheduler, or do something with Services Broker.

Thanks

|||The issue is that when errors happen inside trigger most of them are of the transaction abort nature and the only recourse is to rollback the transaction. Since there is no support for autonomous transactions in SQL Server there is really no way to do what you are trying. Best is to move this logic to a stored procedure so that in case of an error you can catch it appropriately and take action. Note that this still depends on the setting of XACT_ABORT.|||

Im not seeing it, I need help. ANY help would be greatly appreciated

How can I get the try block in a trigger to stop throwing errors that im trying to skip over?

here is my example:

IF (OBJECT_ID ('cwtest') IS NOT NULL) drop table cwtest
go
create table cwtest (test varchar(20))
go
IF (OBJECT_ID ('cwtest_tr_IUD') IS NOT NULL) DROP trigger cwtest_tr_IUD
go
create trigger cwtest_tr_IUD on cwtest
for insert, update, delete
as
DECLARE
@.dml char(1)
begin
BEGIN TRY
select 1/0
END TRY
BEGIN CATCH

IF XACT_STATE() = -1 ROLLBACK - add this line here:
END CATCH
select 'why is the query completed with errors? Is this a bug?,This works fine anywhere else!'
end
go

insert into cwtest values('dd')
go

-- here is the base test works wonderfully!
begin
BEGIN TRY
select 1/0
END TRY
BEGIN CATCH
IF XACT_STATE() = -1 ROLLBACK - add this line here:
END CATCH;
select 'error is not returned,query executed successfully, whats up? '
end
go

and the output :

--

(0 row(s) affected)

why is the query completed with errors?

(1 row(s) affected)

Msg 3616, Level 16, State 1, Line 2

Transaction doomed in trigger. Batch has been aborted.

--

(0 row(s) affected)

-

error is not returned,query executed successfully, whats up?

(1 row(s) affected)

Msg "cannot add rows to sysdepends... procedure will still be created"

Can I get the missing rows added to sysdepends later, when I create procedures in the wrong order, thus getting the "Cannot add rows to sysdepends for the current stored procedure ... The stored procedure will still be created" ?Can I get the missing rows added to sysdepends later, when I create procedures in the wrong order, thus getting the "Cannot add rows to sysdepends for the current stored procedure ... The stored procedure will still be created" ?

Well?? whatcha guys say?|||Sure you can.

Say that MainProc calls SubProc, but when you create the procs in the database you create MainProc 1st. When you create MainProc, you will get the sysdepends message "Cannot add rows ...". When you create SubProc, sysdepends is **not** updated. If you then drop and add MainProc, sysdepends will be populated with the dependancy information.|||Yeah, that's how I've gotten around a few of the problems created by the scripting/transfers to other databases (mirrors, in my case).

However, being the bull-headed son-of-a-db2 programmer that I am, I refuse to do that to every freakin' proc/table/function/etc. in my script. Seems to me that if I use SQL Server to script out a humongous script and then use that script to build a new DB, that I shouldn't have to manually go through the process of dropping and rebuilding each object in the new db.

Perhaps Uncle Bill wants it that way, but I have always been a problem nephew, so I am trying to figure a more global, one-button solution. I did find this thread on the subject after posting the original query, however. http://www.dbforums.com/t406785.html

I will take a look at that solution and see how it works.

Don't get me wrong, I do truly appreciate your response. Sincerely. I just don't have the time and inclination to use the manual drop/readd thang...when that is actually only a tad more desireable than manually writing down the object dependencies on the chalkboard in the hallway. ;)

Thanks again...and sorry for the rant...I just don't like the appearance of bells and whistles that only impotently blow the ball around inside (though they might do so at a high rate of speed), or dog and pony shows consisting of a toy poodle and a plow nag.|||Argh...alas...the sp_recompile/sp_refreshview solution still only goes from the individual object perspective...so I still will need to order the objects according to referential sequences before doing the recompiles.

Still not my silver bullet.

I guess I'll just requisition a few more chalkboards ;) Thanks again!
Paul|||Dependency is resolved at the time of creation of the first execution plan. Thus, - necessary recompile that can be observed while running profiler.|||Dependency is resolved at the time of creation of the first execution plan. Thus, - necessary recompile that can be observed while running profiler.

Are you saying that just running a stored proc will "rebuild" the dependency information in the sysdepends table? If so, then theoretically that means that all I need to do is wait for all the stored procs in the database to run and then my sysdepends table will be all better?

That would be cool, but it doesn't seem to hold true in my (admittedly limited) experience. I'll have to check it out, but my stuff runs M-F here at work, and yet I still have "missing" or "incomplete" dependencies when I list dependencies in Enterprise Manager *scratching head*

Am I missing your point? Are you saying the profiler must be run to rebuild the dependencies? *LOL* I guess instead of guessing I'll wait until you clarify your comments...

As always, thanks!|||I THOUGHT you were going after a physical dependency, not the ability to execute a procedure even if sysdepends doesn't contain a reference...but hoped that that would not be the case after all...Oh well, you're write, dependency can only be reestablished if you either re-CREATE or ALTER the parent procedure after the child has been created. SP_RECOMPILE does not do the trick, BTW.|||I THOUGHT you were going after a physical dependency, not the ability to execute a procedure even if sysdepends doesn't contain a reference.

I probably know just enough to be dangerous to myself and those around me ;) but I really just wanted to build the sysdepends table so that when I DISPLAY dependencies of the various objects (i.e., right click->all tasks->display dependencies) it would be accurate.

I'm not sure which of your situations that desire falls under. sorry if I don't understand...I think it would be the physical dependency that I am after. As far as I know, I've always been able to execute a procedure even if the sysdepends info is bad (i.e., even if the dependency is not displayed in "display dependencies" - which I have understood to get its info from the sysdepends table).

In any case, I think the bottom line is that there is no easy way to rebuild things in sysdepends so I can be assured that all my dependencies are displayed when I try to see them via right-click...short of the drop/add thing.

Although, as I recall, if I go into the stored proc and modifiy something (as opposed to drop/add the proc) - I think the dependencies are re-evaluated when I save my changes...at least that's what I recall from earlier playing around with 'em|||That's because EM fires ALTER which reevaluates the dependencies, as I mentioned earlier. If you want to achieve the result desired you need to invest into something like DBArtisan, but if you just want to continue bashing EM, - be my guest ;)

MSDTC unavailable / JoinTransaction returned 0x8004d01c

Hi! We have 2 database server. 1 at our company, while the other with a web
hosting company.
We wrote stored procedures to update data from tables in our company's
server to the web hosting company's server. Out of 13 of these stored
procedures, 5 failed, giving me the error message that looks like the one
below,
Server: Msg 8501, Level 16, State 1, Procedure sp_ExportCalendar, Line 9
MSDTC on server 'ORION' is unavailable.
Server: Msg 7391, Level 16, State 1, Procedure sp_ExportCalendar, Line 9
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d01c].
Can anyone help me explain what the error mean? I can post stored procedure
if required. Thanks.Hi this is one of the stored procedures that doesn't work. tsp is my user
name, server name.
CREATE PROCEDURE sp_ExportSysConf AS
BEGIN DISTRIBUTED TRANSACTION
IF EXISTS (SELECT * FROM [SysConf])
BEGIN
--Step1: Remove records that do not exist in CIM
DELETE FROM [tsp].[tsp].[tsp].[SysConf]
FROM [tsp].[tsp].[tsp].[SysConf] as C2
WHERE NOT EXISTS (SELECT [C1].[ConfName], [C1].[ConfValue],
[C2].[LastUpdate] FROM [SMSBASE].[DBO].[SysConf] as [C1] where
[C1].[ConfName] = [C2].[ConfName] and [C1].[ConfValue] = [C2].[ConfValue])
IF (@.@.error = 0)
BEGIN
-- no error --> Step 2: Insert new records from CIM
INSERT [tsp].[tsp].[tsp].[SysConf]([ConfName], [ConfValue])
SELECT [ConfName],[ConfValue]
FROM [SMSBASE].[DBO].[SysConf] as C2
WHERE NOT EXISTS (SELECT * FROM [tsp].[tsp].[tsp].[SysConf] as [C1]
where [C1].[ConfName] = [C2].[ConfName] and [C1].[ConfValue] =
[C2].[ConfValue])
IF (@.@.error = 0)
-- no error -> commit
COMMIT TRANSACTION
ELSE
-- error occurred -> rollback
ROLLBACK TRANSACTION
END
ELSE ROLLBACK TRANSACTION
END
ELSE ROLLBACK TRANSACTION
GO

MSDTC on server 'SERVER_NAME' is unavailable

Hi
I have two stored procedures that use a linked server to access an Access
database from SQL Server 2000.
The first takes no input:
CREATE PROCEDURE GetModules
AS
INSERT INTO datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime) SELECT * FROM
OPENQUERY(ProjAForcBCase0,'select * from datCase')
GO
This procedure runs fine and does exactly as expected.
The second parameterises the linked server name (the idea being that these
will be created dynamically in code) so that it can be passed in to the
stored procedure.
CREATE PROCEDURE GetModulesDynamic
@.LinkedServer nvarchar(4000)
AS
DECLARE @.TSQL nvarchar(4000)
DECLARE @.OPENQUERY nvarchar(4000)
SET @.OPENQUERY = 'SELECT * FROM OPENQUERY('+ @.LinkedServer + ','''
SET @.TSQL = 'select * from datCase'')'
Insert into datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime)
EXEC(@.OPENQUERY+@.TSQL)
GO
However if I run this second stored procedure I am met with the error
message "MSDTC on server 'SERVER_NAME' is unavailable".
Both these stored procedures were executed on my PC (win 2K) using a local
SQL Server 2000 instance and a localo access database. As such I do not
understand why I should require MSDTC for the second procedure and not for
the first.
Any explanation would be extremely useful.
Also can MSDTC be installed on Win 2K and if so where can I get it from?
Thanks
TomHave just dicovered that I didn't have DTC running (had been looking for a
service called MSDTC as defined in the error message)
As such this now works as expected for both procedures, but if anyone knows
why it worked without DTC for the first procedure and not the second I would
be interested in an explanation.
"TomPearson" wrote:

> Hi
> I have two stored procedures that use a linked server to access an Access
> database from SQL Server 2000.
> The first takes no input:
> CREATE PROCEDURE GetModules
> AS
> INSERT INTO datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
> UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime) SELECT * FROM
> OPENQUERY(ProjAForcBCase0,'select * from datCase')
> GO
> This procedure runs fine and does exactly as expected.
> The second parameterises the linked server name (the idea being that these
> will be created dynamically in code) so that it can be passed in to the
> stored procedure.
> CREATE PROCEDURE GetModulesDynamic
> @.LinkedServer nvarchar(4000)
> AS
> DECLARE @.TSQL nvarchar(4000)
> DECLARE @.OPENQUERY nvarchar(4000)
> SET @.OPENQUERY = 'SELECT * FROM OPENQUERY('+ @.LinkedServer + ','''
> SET @.TSQL = 'select * from datCase'')'
> Insert into datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
> UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime)
> EXEC(@.OPENQUERY+@.TSQL)
> GO
> However if I run this second stored procedure I am met with the error
> message "MSDTC on server 'SERVER_NAME' is unavailable".
> Both these stored procedures were executed on my PC (win 2K) using a local
> SQL Server 2000 instance and a localo access database. As such I do not
> understand why I should require MSDTC for the second procedure and not for
> the first.
> Any explanation would be extremely useful.
> Also can MSDTC be installed on Win 2K and if so where can I get it from?
> Thanks
> Tom|||Most likely because in the second procedure, you used an
insert exec which would promote the transaction to a
distributed transaction. The first procedure used an insert
select so was a local transaction.
-Sue
On Mon, 31 Jan 2005 07:45:03 -0800, "TomPearson"
<TomPearson@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Have just dicovered that I didn't have DTC running (had been looking for a
>service called MSDTC as defined in the error message)
>As such this now works as expected for both procedures, but if anyone knows
>why it worked without DTC for the first procedure and not the second I woul
d
>be interested in an explanation.
>"TomPearson" wrote:
>

MSDTC on server 'SERVER_NAME' is unavailable

Hi
I have two stored procedures that use a linked server to access an Access
database from SQL Server 2000.
The first takes no input:
CREATE PROCEDURE GetModules
AS
INSERT INTO datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime) SELECT * FROM
OPENQUERY(ProjAForcBCase0,'select * from datCase')
GO
This procedure runs fine and does exactly as expected.
The second parameterises the linked server name (the idea being that these
will be created dynamically in code) so that it can be passed in to the
stored procedure.
CREATE PROCEDURE GetModulesDynamic
@.LinkedServer nvarchar(4000)
AS
DECLARE @.TSQL nvarchar(4000)
DECLARE @.OPENQUERY nvarchar(4000)
SET @.OPENQUERY = 'SELECT * FROM OPENQUERY('+ @.LinkedServer + ','''
SET @.TSQL = 'select * from datCase'')'
Insert into datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime)
EXEC(@.OPENQUERY+@.TSQL)
GO
However if I run this second stored procedure I am met with the error
message "MSDTC on server 'SERVER_NAME' is unavailable".
Both these stored procedures were executed on my PC (win 2K) using a local
SQL Server 2000 instance and a localo access database. As such I do not
understand why I should require MSDTC for the second procedure and not for
the first.
Any explanation would be extremely useful.
Also can MSDTC be installed on Win 2K and if so where can I get it from?
Thanks
Tom
Have just dicovered that I didn't have DTC running (had been looking for a
service called MSDTC as defined in the error message)
As such this now works as expected for both procedures, but if anyone knows
why it worked without DTC for the first procedure and not the second I would
be interested in an explanation.
"TomPearson" wrote:

> Hi
> I have two stored procedures that use a linked server to access an Access
> database from SQL Server 2000.
> The first takes no input:
> CREATE PROCEDURE GetModules
> AS
> INSERT INTO datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
> UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime) SELECT * FROM
> OPENQUERY(ProjAForcBCase0,'select * from datCase')
> GO
> This procedure runs fine and does exactly as expected.
> The second parameterises the linked server name (the idea being that these
> will be created dynamically in code) so that it can be passed in to the
> stored procedure.
> CREATE PROCEDURE GetModulesDynamic
> @.LinkedServer nvarchar(4000)
> AS
> DECLARE @.TSQL nvarchar(4000)
> DECLARE @.OPENQUERY nvarchar(4000)
> SET @.OPENQUERY = 'SELECT * FROM OPENQUERY('+ @.LinkedServer + ','''
> SET @.TSQL = 'select * from datCase'')'
> Insert into datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
> UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime)
> EXEC(@.OPENQUERY+@.TSQL)
> GO
> However if I run this second stored procedure I am met with the error
> message "MSDTC on server 'SERVER_NAME' is unavailable".
> Both these stored procedures were executed on my PC (win 2K) using a local
> SQL Server 2000 instance and a localo access database. As such I do not
> understand why I should require MSDTC for the second procedure and not for
> the first.
> Any explanation would be extremely useful.
> Also can MSDTC be installed on Win 2K and if so where can I get it from?
> Thanks
> Tom
|||Most likely because in the second procedure, you used an
insert exec which would promote the transaction to a
distributed transaction. The first procedure used an insert
select so was a local transaction.
-Sue
On Mon, 31 Jan 2005 07:45:03 -0800, "TomPearson"
<TomPearson@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Have just dicovered that I didn't have DTC running (had been looking for a
>service called MSDTC as defined in the error message)
>As such this now works as expected for both procedures, but if anyone knows
>why it worked without DTC for the first procedure and not the second I would
>be interested in an explanation.
>"TomPearson" wrote:

MSDTC on server 'SERVER_NAME' is unavailable

Hi
I have two stored procedures that use a linked server to access an Access
database from SQL Server 2000.
The first takes no input:
CREATE PROCEDURE GetModules
AS
INSERT INTO datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime) SELECT * FROM
OPENQUERY(ProjAForcBCase0,'select * from datCase')
GO
This procedure runs fine and does exactly as expected.
The second parameterises the linked server name (the idea being that these
will be created dynamically in code) so that it can be passed in to the
stored procedure.
CREATE PROCEDURE GetModulesDynamic
@.LinkedServer nvarchar(4000)
AS
DECLARE @.TSQL nvarchar(4000)
DECLARE @.OPENQUERY nvarchar(4000)
SET @.OPENQUERY = 'SELECT * FROM OPENQUERY('+ @.LinkedServer + ','''
SET @.TSQL = 'select * from datCase'')'
Insert into datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime)
EXEC(@.OPENQUERY+@.TSQL)
GO
However if I run this second stored procedure I am met with the error
message "MSDTC on server 'SERVER_NAME' is unavailable".
Both these stored procedures were executed on my PC (win 2K) using a local
SQL Server 2000 instance and a localo access database. As such I do not
understand why I should require MSDTC for the second procedure and not for
the first.
Any explanation would be extremely useful.
Also can MSDTC be installed on Win 2K and if so where can I get it from?
Thanks
TomHave just dicovered that I didn't have DTC running (had been looking for a
service called MSDTC as defined in the error message)
As such this now works as expected for both procedures, but if anyone knows
why it worked without DTC for the first procedure and not the second I would
be interested in an explanation.
"TomPearson" wrote:
> Hi
> I have two stored procedures that use a linked server to access an Access
> database from SQL Server 2000.
> The first takes no input:
> CREATE PROCEDURE GetModules
> AS
> INSERT INTO datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
> UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime) SELECT * FROM
> OPENQUERY(ProjAForcBCase0,'select * from datCase')
> GO
> This procedure runs fine and does exactly as expected.
> The second parameterises the linked server name (the idea being that these
> will be created dynamically in code) so that it can be passed in to the
> stored procedure.
> CREATE PROCEDURE GetModulesDynamic
> @.LinkedServer nvarchar(4000)
> AS
> DECLARE @.TSQL nvarchar(4000)
> DECLARE @.OPENQUERY nvarchar(4000)
> SET @.OPENQUERY = 'SELECT * FROM OPENQUERY('+ @.LinkedServer + ','''
> SET @.TSQL = 'select * from datCase'')'
> Insert into datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
> UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime)
> EXEC(@.OPENQUERY+@.TSQL)
> GO
> However if I run this second stored procedure I am met with the error
> message "MSDTC on server 'SERVER_NAME' is unavailable".
> Both these stored procedures were executed on my PC (win 2K) using a local
> SQL Server 2000 instance and a localo access database. As such I do not
> understand why I should require MSDTC for the second procedure and not for
> the first.
> Any explanation would be extremely useful.
> Also can MSDTC be installed on Win 2K and if so where can I get it from?
> Thanks
> Tom|||Most likely because in the second procedure, you used an
insert exec which would promote the transaction to a
distributed transaction. The first procedure used an insert
select so was a local transaction.
-Sue
On Mon, 31 Jan 2005 07:45:03 -0800, "TomPearson"
<TomPearson@.discussions.microsoft.com> wrote:
>Have just dicovered that I didn't have DTC running (had been looking for a
>service called MSDTC as defined in the error message)
>As such this now works as expected for both procedures, but if anyone knows
>why it worked without DTC for the first procedure and not the second I would
>be interested in an explanation.
>"TomPearson" wrote:
>> Hi
>> I have two stored procedures that use a linked server to access an Access
>> database from SQL Server 2000.
>> The first takes no input:
>> CREATE PROCEDURE GetModules
>> AS
>> INSERT INTO datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
>> UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime) SELECT * FROM
>> OPENQUERY(ProjAForcBCase0,'select * from datCase')
>> GO
>> This procedure runs fine and does exactly as expected.
>> The second parameterises the linked server name (the idea being that these
>> will be created dynamically in code) so that it can be passed in to the
>> stored procedure.
>> CREATE PROCEDURE GetModulesDynamic
>> @.LinkedServer nvarchar(4000)
>> AS
>> DECLARE @.TSQL nvarchar(4000)
>> DECLARE @.OPENQUERY nvarchar(4000)
>> SET @.OPENQUERY = 'SELECT * FROM OPENQUERY('+ @.LinkedServer + ','''
>> SET @.TSQL = 'select * from datCase'')'
>> Insert into datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
>> UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime)
>> EXEC(@.OPENQUERY+@.TSQL)
>> GO
>> However if I run this second stored procedure I am met with the error
>> message "MSDTC on server 'SERVER_NAME' is unavailable".
>> Both these stored procedures were executed on my PC (win 2K) using a local
>> SQL Server 2000 instance and a localo access database. As such I do not
>> understand why I should require MSDTC for the second procedure and not for
>> the first.
>> Any explanation would be extremely useful.
>> Also can MSDTC be installed on Win 2K and if so where can I get it from?
>> Thanks
>> Tom