do process

how can I do
perform a stored procedure with paramert from my client application but my q
uestion is sent call but transfer process to sqlserver a for example i can c
lose my application but process continue running in a sqlserver until finiss
hed, similar a job but with
out job.
Thank You
AlfSome environments and providers (e.g. .NET) will allow you to make a stored
procedure call asynchronous, meaning it does not have to wait for the
result.
So, it is hard to say if your client application can do that, because you
forgot to tell us anything about your client application.
A
<ag> wrote in message news:%23iXkfN6QGHA.4740@.TK2MSFTNGP14.phx.gbl...
> how can I do
>
> perform a stored procedure with paramert from my client application but my
> question is sent call but transfer process to sqlserver a for example i
> can close my application but process continue running in a sqlserver until
> finisshed, similar a job but without job.
> Thank You
> Alf
>

Do I need cursors?

I am creating a stored procedure to send emails (with xp_sendmail, I think)
based on certain conditions. This is my logic:
I need to loop through all of the Table1 records.
if Table2 exists for Table1 and Table2.column='T'
send variation 1 of email
else if table2 exists for table 1 and Table2.column='F'
send variation 2 of email
else if table3 exists for table 1
send variation 3 of email
Should I use a cursor to loop through the Table1 records? Or should I join
Table1 and Table2/Table3 and not have a top level query? I'm thinking I
should not use a cursor.
My second problem is this. Depending on the email variation I need to loop
through some records and concatenate their values. Do I need a cursor for
that? Or do I have other options?
Thanks for any help, I really appreciate it.Nick
DECLARE @.EmailName VARCHAR(100),@.userid VARCHAR(20)
IF EXISTS (SELECT * FROM Table2 JOIN Table1 ON Table2.pk=Table1.pk AND
Table2.column='T')

> My second problem is this. Depending on the email variation I need to loop
> through some records and concatenate their values. Do I need a cursor for
> that? Or do I have other options?
SET @.userid='john,arie,alex'
SELECT @.EmailName=@.EmailName+COALESCE(Emailadd,
'') +',' FROM users where
CHARINDEX(',' + userid + ',',','+ @.userid +',')>0 and EmailName IS NOT
NULL
SET @.EmailName=LEFT(@.EmailName,LEN(@.EmailNam
e)-1)
--Send emails
I'm currently unable to test it but I'm sure it gives you an idea.
"Nick" <nickfinity@.nospam.nospam> wrote in message
news:48C35529-4878-455F-9DCC-F97057A4A5B8@.microsoft.com...
>I am creating a stored procedure to send emails (with xp_sendmail, I think)
> based on certain conditions. This is my logic:
> I need to loop through all of the Table1 records.
> if Table2 exists for Table1 and Table2.column='T'
> send variation 1 of email
> else if table2 exists for table 1 and Table2.column='F'
> send variation 2 of email
> else if table3 exists for table 1
> send variation 3 of email
> Should I use a cursor to loop through the Table1 records? Or should I join
> Table1 and Table2/Table3 and not have a top level query? I'm thinking I
> should not use a cursor.
> My second problem is this. Depending on the email variation I need to loop
> through some records and concatenate their values. Do I need a cursor for
> that? Or do I have other options?
> Thanks for any help, I really appreciate it.|||>From the limited information you've posted, it sounds like a CASE
statement is what you're looking for. In general, the only time I ever
find that I must use a cursor is when I have to call a stored procedure
on each value in a resultset. They seem to be much more useful to me in
ad hoc situations than in deployed solutions.
CASE Example in an UPDATE (sorry for the poor formatting):
UPDATE <tablealias1>
SET EmailAddress = CASE WHEN <condition1> THEN <expression>
WHEN <condition2> THEN <expression> ELSE <expression> END
FROM Table1 <tablealias1> INNER JOIN Table2 ON <...>
WHERE <condition>

DNN DAL SqlDataProvider Passing NULL to SQL Stored Procedure

Hello,

I'm trying to pass a null object to a stored procedure to update a SQL Table boolean field with a null value. My SQL Table boolean column allows nulls.

I'm using an InfoObject which has several properties all corresponding to fields in the SQL Table. One of those fields is a boolean. I create an instance of the InfoObject in my code and assigns values to the various properties. The boolean property in question (call it InfoOjbect.BooleanProperty) is not assigned anything. I then call my StoredProcedure passing the InfoObject to it (using the DotNetNuke DAL architecture) and the final result is the Table's boolean column is populated with a 0 and not a NULL. If I explicitly define the InfoObject.BooleanProperty = null.nullboolean before passing it to the Stored Procedure, the same thing happens. How do I pass a null to the SQL database for a boolean field? I've tried making InfoObject.BooleanProperty = dbnull.value but it won't let me do this saying "dbnull cannot be converted to a boolean." Do I have to explicitly create my InfoObject properties to allow for a null to be assigned to it?

Any help would be greatly appreciated. I'm using the DotNetNuke DAL architecture passing my InfoObject through a dataprovider to the sqldataprovider which calls the SQL Stored Procedure to add the new record to the Table.

Thanks in advance for any help.Please help?!|||The issue was with my InfoObject construction. DNN Core Team provided the solution. You can see it athttp://www.dotnetnuke.com/Community/ForumsDotNetNuke/tabid/795/forumid/118/threadid/41618/threadpage/3/scope/posts/Default.aspx

DMX in TSQL?

Hi, I'm new to Transact-SQL and I'm trying to throw a DMX query I have created into a stored procedure because I think it's the only way to pass variables into an openquery statement! It seems that no matter what I try gets me the error 'Incorrect usage of quotes'. I'm trying to use code like this:

Code Snippet

AS

BEGIN

DECLARE @.OPENQUERY nvarchar(4000), @.TSQL nvarchar(4000), @.LinkedServer nvarchar(4000)

SET @.LinkedServer = 'DMSERVER'

SET @.OPENQUERY = 'SELECT * FROM OPENQUERY('+ @.LinkedServer + ','''

SET @.TSQL = 'SELECT FLATTENED * FROM ['+@.miningModel+'].CONTENT'')'

EXEC (@.OPENQUERY+@.TSQL)

END

Which I found from some one else's posting on the data mining forums. I'm completely clueless as to how to make this work - Can I even do what I want to do? It seems like the brackets are causing all the fuss, but they're necessary for DMX queries. Any ideas? Thanks!

Code Snippet

AS

BEGIN

DECLARE @.OPENQUERY nvarchar(4000), @.TSQL nvarchar(4000), @.LinkedServer nvarchar(4000)

SET @.LinkedServer = 'DMSERVER'

SET @.OPENQUERY = 'SELECT * FROM OPENQUERY('+ @.LinkedServer + ','''

SET @.TSQL = 'SELECT * FROM ['+@.miningModel+'].CONTENT'')'

EXEC (@.OPENQUERY+@.TSQL)

END

|||

Still doesn't work...here's the error:

Code Snippet

TITLE: Microsoft Report Designer

An error occurred while retrieving the parameters in the query.
SqlCommand.DeriveParameters failed because the SqlCommand.CommandText property value is an invalid multipart name "AS
BEGIN
DECLARE @.OPENQUERY nvarchar(4000), @.TSQL nvarchar(4000), @.LinkedServer nvarchar(4000)
SET @.LinkedServer = 'abc'
SET @.OPENQUERY = 'SELECT * FROM OPENQUERY('+ @.LinkedServer + ','''
SET @.TSQL = 'SELECT * FROM ['+@.miningModel+'].CONTENT'')'
EXEC (@.OPENQUERY+@.TSQL)

END", incorrect usage of quotes.

ADDITIONAL INFORMATION:

SqlCommand.DeriveParameters failed because the SqlCommand.CommandText property value is an invalid multipart name "AS
BEGIN
DECLARE @.OPENQUERY nvarchar(4000), @.TSQL nvarchar(4000), @.LinkedServer nvarchar(4000)
SET @.LinkedServer = 'abc'
SET @.OPENQUERY = 'SELECT * FROM OPENQUERY('+ @.LinkedServer + ','''
SET @.TSQL = 'SELECT * FROM ['+@.miningModel+'].CONTENT'')'
EXEC (@.OPENQUERY+@.TSQL)

END", incorrect usage of quotes. (System.Data)

BUTTONS:

OK

|||are you initializing the variable @.miningModel?

|||

I have it defined as a report parameter in reporting svcs, with default value of 'PRRelational'... Even if I make it a local variable it still throws the same error...

EDIT: Turns out I was doing this from the wrong location...! Thanks. Will post if I get it to work.

EDIT2: Post shows how to do this: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1676053&SiteID=1&mode=1

DML delete issues

Hi all,

I'm trying to return a processed Excel XML document from a stored procedure.

The procedure pulls a template document in XML format from a table, inserts data using DML and returns the result. The problem I've hit is in removing worksheets from the document prior to returning it. Inserts work fine when I try to remove a worksheet everything hangs. I'm thinking this could be a namespace problem, but am at a loss.

A small example(assume that @.XML contains a standard ExcelXML document):

set @.XML.modify('declare namespace ss="urnchemas-microsoft-comfficepreadsheet";
declare namespace o="urnchemas-microsoft-comfficeffice";
declare namespace x="urnchemas-microsoft-comffice:excel";
delete (/ss:Workbook[1]/ss:Worksheet[1])')

Any help greatly appreciated...

Regards,

Andy

I tried your case (delete the first Worksheet from a excel file in 2003 xml format) and it works fine:

Code Snippet

declare @.x xml = '<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Author>jinghaol</Author>
<LastAuthor>jinghaol</LastAuthor>
<Created>2007-04-24T16:15:41Z</Created>
<LastSaved>2007-04-24T16:17:18Z</LastSaved>
<Company>Microsoft</Company>
<Version>12.00</Version>
</DocumentProperties>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>12345</WindowHeight>
<WindowWidth>18960</WindowWidth>
<WindowTopX>120</WindowTopX>
<WindowTopY>75</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
</Styles>
<Worksheet ss:Name="Sheet1">
<Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
x:FullRows="1" ss:DefaultRowHeight="15">
<Row>
<Cell><Data ss:Type="String">adsf</Data></Cell>
</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Header x:Margin="0.3"/>
<Footer x:Margin="0.3"/>
<PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
</PageSetup>
<Selected/>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet2">
<Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
x:FullRows="1" ss:DefaultRowHeight="15">
<Row>
<Cell><Data ss:Type="String">asdf</Data></Cell>
</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Header x:Margin="0.3"/>
<Footer x:Margin="0.3"/>
<PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
</PageSetup>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet3">
<Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="2" x:FullColumns="1"
x:FullRows="1" ss:DefaultRowHeight="15">
<Row ss:Index="2">
<Cell><Data ss:Type="String">asdf</Data></Cell>
</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Header x:Margin="0.3"/>
<Footer x:Margin="0.3"/>
<PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
</PageSetup>
<Panes>
<Pane>
<Number>3</Number>
<ActiveRow>1</ActiveRow>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>'
select @.x
set @.x.modify('
declare namespace ss="urn:schemas-microsoft-com:office:spreadsheet";
declare namespace o="urn:schemas-microsoft-com:office:office";
declare namespace x="urn:schemas-microsoft-com:office:excel";
delete (/ss:Workbook[1]/ss:Worksheet[1])')
select @.x
go

I couldn't exactly copy/paste/run your query because the editor puts many and in namespace declare part.

DLL initialization failure using CDOSYS mail

We have a SQL 2000 stored procedure to send notification emails using CDOSYS and OLE Automation. It has been happily sending out emails for quite a while now from both of our dev and prod machines.

The other day I added a line of code to format the message body variable. I tested the change in a T-SQL script in dev, then added the line into the procedure and recompiled it in dev using an ALTER PROC script. I then called the dev proc and everything is still good. The change has no impact to the sp_OA* commands.

So then I used the same ALTER PROC script and pointed it to production. There is no difference between the dev and prod procs so this was OK. The script ran OK and the proc was updated with the change. However, now only the prod proc doesn't work. Further, the same code in a T-SQL script also fails. But everything remains fine in the dev environment.

We restored the database that had the email SP to a point prior to the change, but the problem persists. It is as if recompiling the proc has disabled the CDOSYS capability from SQL server. CDOSYS still works from VBscript on the server.

The error message:

Msg 50000, Level 18, State 3, Procedure usp_SendEmail, Line 154

Error in Email Object: Source: CDO.Configuration.1 . Description: A dynamic link library (DLL) initialization routine failed.

(EOLIAN.Tools.dbo.usp_SendEmail)

Here's a bit of the code:

DECLARE @.iMsg int,

@.hr int

EXEC @.hr = sp_OACreate 'CDO.Message', @.iMsg OUT

IF @.HR <> 0 GOTO Error_Handling

EXEC @.hr = sp_OASetProperty @.iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'

IF @.HR <> 0 GOTO Error_Handling

I encountered a similar problem a few months ago when we collocated our DB server (Win2K, SQL 2005) and changed the domain it was in. At the time, granting the login that runs the SQL Server service access to the System32/InetSrv directory fixed the problem (seemed to be a metabase access issue). The one difference is that we use the Pickup directory (SendUsing=1).

A few weeks later, things stopped working again. Like you, I've tested using a VBScript logged in as the same account that's running the SQL Server service and the emails gets generated without difficulty. But using the stored proc or a pared-down SQL script generates the same DLL initialization error in CDO.Configuration.1.

This proc has been in use since we converted to SQL 2000 and has not been altered for some time (>12mo). The only change is the domain change which clearly introduced a number of security implications. However, that doesn't explain why it worked and then just stopped working.

If I figure out the problem, I'll post again. If you figure out hte problem, please post as well as we may be chasing the same issue.

|||Moving to the T-SQl group.|||The problem is resolved, although not understood. We rebooted the server and restarted SQL and all is well again. Not sure what caused the problem, or what the problem was.|||The solution was short lived. The DLL initialization failure message is back. :-(

dll & Web Service in VB.Net 2005 Stored Procedure

Hi,
I am writing a vb.net2005 program that needs to create a stored procedure
with SqlServerProject Template.
Now, I have two questions for this stored procedure.
1) How can I import and execute the .dll in this Stored Procedures?
2) How can I connect the Web Service and get the result in this Stored
Procedures?
---
Partial Public Class StoredProcedures1
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub GetTable()
Dim strSQL As String = "SELECT * FROM Table1"
Using conn As New SqlConnection("context connection=true")
Using cmd As New SqlCommand
With cmd
.Connection = conn
.CommandText = strSQL
.CommandType = CommandType.Text
conn.Open()
SqlContext.Pipe.ExecuteAndSend(cmd)
.Connection.Close()
End With
End Using
End Using
End Sub
End Class
---
Thanks!"James Wong" <cphk_msdn@.nospam.nospam> wrote in message
news:u155F84iGHA.1260@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I am writing a vb.net2005 program that needs to create a stored procedure
> with SqlServerProject Template.
> Now, I have two questions for this stored procedure.
> 1) How can I import and execute the .dll in this Stored Procedures?
You need to deploy the assembly into your database (CREATE ASSEMBLY) and
then add a stored procedure referencing the method in the assembly (CREATE
PROCEDURE). Visual Studio will do this for you if you right-click on the
project and select Deploy.
Here's how you would do it by hand:
CREATE ASSEMBLY [SqlServerProject2]
FROM 'c:\SqlServerProject2.dll'
WITH PERMISSION_SET = SAFE
GO
CREATE PROCEDURE [GetTable]
AS
EXTERNAL NAME
[SqlServerProject2].[SqlServerProject2.StoredProcedures].[GetTable]

> 2) How can I connect the Web Service and get the result in this Stored
> Procedures?
>
From the web service just connect to the database and execute it with a
SqlCommand.
David|||Hi David,
sorry, i think that u were misunderstood my problem.
My Stored Procedure "StoredProcedures1.GetTable()" is need to call another
.dll & web service to get some value.
Thanks
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> glsD:O3xF9H5iGHA.45
04@.TK2MSFTNGP05.phx.gbl...
> "James Wong" <cphk_msdn@.nospam.nospam> wrote in message
> news:u155F84iGHA.1260@.TK2MSFTNGP05.phx.gbl...
> You need to deploy the assembly into your database (CREATE ASSEMBLY) and
> then add a stored procedure referencing the method in the assembly (CREATE
> PROCEDURE). Visual Studio will do this for you if you right-click on the
> project and select Deploy.
> Here's how you would do it by hand:
> CREATE ASSEMBLY [SqlServerProject2]
> FROM 'c:\SqlServerProject2.dll'
> WITH PERMISSION_SET = SAFE
> GO
> CREATE PROCEDURE [GetTable]
> AS
> EXTERNAL NAME
> [SqlServerProject2].[SqlServerProject2.StoredProcedures].[GetTable]
>
> From the web service just connect to the database and execute it with a
> SqlCommand.
> David
>|||"James Wong" <cphk_msdn@.nospam.nospam> wrote in message
news:eWXrxU5iGHA.412@.TK2MSFTNGP05.phx.gbl...
> Hi David,
> sorry, i think that u were misunderstood my problem.
> My Stored Procedure "StoredProcedures1.GetTable()" is need to call another
> .dll & web service to get some value.
>
To use another .dll add a reference to your database project. To use a web
service add a web reference.
David|||Hi David,
1) For DLL, VS.Net 2005 is not allow me to import a new References in
SqlServerProject Template.
2) For Web Service, it will occur error when running.
----
--
Partial Public Class StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub SPWithWebService()
Dim WebService As New SqlServerProject1.localhost.Service
Dim msg As String = CStr(WebService.HelloWorld())
SqlContext.Pipe.Send(msg)
End Sub
End Class
----
--
Thanks
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> glsD:OqYxDh5iGHA.34
96@.TK2MSFTNGP04.phx.gbl...
> "James Wong" <cphk_msdn@.nospam.nospam> wrote in message
> news:eWXrxU5iGHA.412@.TK2MSFTNGP05.phx.gbl...
> To use another .dll add a reference to your database project. To use a
> web service add a web reference.
> David
>|||Hello James,
When you are using the VS SQL Server Project you are restricted to what
assemblies you can reference. This behavior is by design. However, you
could create a normal class library project, add references as per usual
and then manually create your assembly through CREATE ASSEMBLY. Make sure
your referenced
assemblies are in the same directory as your user assembly.
If you want to use the VS SQL Server Project you can create the assembly
you want to reference in the database before you try to reference it. You
need do a manual CREATE ASSEMBLY against the DLL you want.
As for Web service , you will also need to use the CLR SDK tool SGEN.EXE to
create and register a helper assembly for the assembly generated by WSDL,
as described in:
http://blogs.msdn.com/sqlclr/archiv.../25/Vineet.aspx
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

divide by zero exception

How I can rectify divide by zero exception in stored procedure

By checking first, to make sure you're not about to divide by zero ? I suspect a CASE statement is the easiest way to do this.

|||i know it, but actually I want that display 0 or my error message instead of server error message.|||

OK - so what do you need to know ? In your stored proc, return the result of the divide if it's not going to divide by zero, or zero if it is. Then you can check for 0 and show an error if you prefer.

|||

when i write Query like

select 4/0 then it display error msg - 8134

but i don't wan't to display it, i want it as 0.

i have too much table like apr0405i, aprs0506i similary as years wise table. So i create sp and passing '0405' value of parameter of sp which select the table of corresponding year then it display value. but in 0506 then it display the divide by zero exception. so i want to suppress this error message and display 0. because it can't possible to use case with big sql query since i am using more than 15 sql query in one query statement and stored that value in parameter & then print value of paramter

|||

OK - so your database design is not so great ? You create a table for each month ?

I don't know of any way to make SQL Server return 0 when you divide by 0.

|||Joining this thread a little late, oh well....

As far as I am aware, you can't make SQL Server return 0 but you can override the default behaviour (suppressing error messages and the like) and define your own behaviour, such as returning 0. For example:

/*
Override default behaviour
*/
SET ARITHABORT OFF -- Divide by 0 does not halt execution
SET ARITHIGNORE ON -- Divide by 0 does not display error message
SET ANSI_WARNINGS OFF -- Divide by 0 does not display error message

DECLARE @.returnOfCalc As INT

SELECT @.returnOfCalc = 4/0
IF @.returnOfCalc IS NULL
BEGIN
SET @.returnOfCalc = 0
PRINT @.returnOfCalc -- Optional, just as a demo of what can be done
END

/*
Restore default behaviour
*/
SET ARITHABORT ON
SET ARITHIGNORE OFF
SET ANSI_WARNINGS ON

So in your stored procedure, you would issue the first bunch of SET statements at the beginning of the procedure (to override default behaviour) and just before the end of the stored procedure you would issue the second set of SET statements (to restore the default behaviour).

I really don't suggest you mess with the default behaviour in SQL Server, but if you really want/need to then knock yourself out. Just remember to be a good citizen and reset the default behaviour when you finish.

Hope that helps a bit, but sorry if it doesn't
|||

You can do something like:

select coalesce(@.i/nullif(@.j, 0), 0)

-- or

select case @.j when 0 then @.j else @.i/@.j end

|||

use the try... catch

feature of sql2k5

|||

Thanks for ur suggestion, but I already solved it by

case method it goes too much bigger but it gives satisfied results

well, ur answer giving very well theoritical concept, Thank u once again, Next I will send u another query, if i have

|||Thank U chandra ji once again, i was already using same ideas which u send. But Its very well felling inside me, Thanks once again

Divide by number of days in the year.

How can I create a function that will divide a parameter passed into the
stored procedure by the number of days in the current year? I know that I
can not just use 365 since it will not take into account leap years.
Thanks in advanceHere's how to get the number of days in the current year:
select
case
when year (getdate()) / 100 % 4 = 0 then 365
when year (getdate()) % 4 = 0 then 366
else 365
end
However, in a UDF, you cannot have a non-deterministic function within it.
Thus, you cannot use getdate() directly. However, you could feed a date to
the function.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"scuba79" <scuba79@.discussions.microsoft.com> wrote in message
news:19165261-4C92-45C9-97B5-00AC910B17FE@.microsoft.com...
How can I create a function that will divide a parameter passed into the
stored procedure by the number of days in the current year? I know that I
can not just use 365 since it will not take into account leap years.
Thanks in advance|||For fun, here's a compact way:
select 365+isdate(str(year(getdate()))+'0229')
and more fun:
select
368-month(dateadd(yy,year(getdate())-1900,60))
and finally, one that's wrong, but rarely:
select
datediff(d,getdate(),dateadd(yy,1,getdat
e()))
Steve Kass
Drew University
Tom Moreau wrote:

>Here's how to get the number of days in the current year:
>select
> case
> when year (getdate()) / 100 % 4 = 0 then 365
> when year (getdate()) % 4 = 0 then 366
> else 365
> end
>However, in a UDF, you cannot have a non-deterministic function within it.
>Thus, you cannot use getdate() directly. However, you could feed a date to
>the function.
>
>|||scuba
Here is a stright but bit complicated one. This is useful evenif calender
changes(yuck!) provided years starts from jan1 and ends with 31 dec(kidding)
SELECT DATEDIFF(DAY, CAST('01-01-' + cast(YEAR(GETDATE()) as varchar(4)) AS
DATETIME),CAST('12-31-' + cast(YEAR(GETDATE()) as varchar(4)) AS DATETIME))+
1
Regards
R.D
"scuba79" wrote:

> How can I create a function that will divide a parameter passed into the
> stored procedure by the number of days in the current year? I know that I
> can not just use 365 since it will not take into account leap years.
> Thanks in advance|||:-)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Steve Kass" <skass@.drew.edu> wrote in message
news:e%23m2LE3rFHA.1032@.TK2MSFTNGP12.phx.gbl...
For fun, here's a compact way:
select 365+isdate(str(year(getdate()))+'0229')
and more fun:
select
368-month(dateadd(yy,year(getdate())-1900,60))
and finally, one that's wrong, but rarely:
select
datediff(d,getdate(),dateadd(yy,1,getdat
e()))
Steve Kass
Drew University
Tom Moreau wrote:

>Here's how to get the number of days in the current year:
>select
> case
> when year (getdate()) / 100 % 4 = 0 then 365
> when year (getdate()) % 4 = 0 then 366
> else 365
> end
>However, in a UDF, you cannot have a non-deterministic function within it.
>Thus, you cannot use getdate() directly. However, you could feed a date to
>the function.
>
>

Distriubtion Error on Stored Procedure

Setting up Transactional Replication.

Log Reader Agent successfull.

Snapshot Agent successfull.

Distribution Agent Error (Replication is failing because of this error. I don't know what is wrong with the stored procedure that causes it not to replicate):

Date 1/19/2007 10:28:13 AM
Log Job History (IS36-MMS_20061213-MMSRepTest-IS4-30)

Step ID 2
Server IS4
Job Name IS36-MMS_20061213-MMSRepTest-IS4-30
Step Name Run agent.
Duration 00:00:11
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
2007-01-19 16:28:24.700 Category:COMMAND
Source: Failed Command
Number:
Message: CREATE PROCEDURE "dbo"."spBTG_GetEventsSince"(@.EventMin datetime, @.BoatID int) AS

BEGIN
create table #TmpEvents
(
BoatHistoryID int null,
PositionID int null,
Event varchar(50) null,
EventDateTime datetime null,
Direction char(1) null,
River char(3) null,
Mile numeric(6,2) null
)

insert into #TmpEvents
select bh.BoatHistoryID, null, Event, EventDateTime, Direction, riverloc, mileloc
--jds 6/22/05 hardcoded index to fix it when you call it from vb
-- from
2007-01-19 16:28:24.700 Category:NULL
Source: Microsoft SQL Native Client
Number: 1018
Message: Incorrect syntax near 'index'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

Here is the entire source of the sp:

GO

CREATE PROCEDURE [dbo].[spBTG_GetEventsSince](@.EventMin datetime, @.BoatID int) AS

BEGIN
create table #TmpEvents
(
BoatHistoryID int null,
PositionID int null,
Event varchar(50) null,
EventDateTime datetime null,
Direction char(1) null,
River char(3) null,
Mile numeric(6,2) null
)

insert into #TmpEvents
select bh.BoatHistoryID, null, Event, EventDateTime, Direction, riverloc, mileloc
--jds 6/22/05 hardcoded index to fix it when you call it from vb
-- from mtsEventHistory eh (NOLOCK)
-- left join mtsBoatHistory bh (NOLOCK) on bh.EventID = eh.EventID
from mtsEventHistory eh (NOLOCK, index(PK_MtsEventHistory))
left join mtsBoatHistory bh (NOLOCK, index(IDX_MtsBoatHistory_BoatID)) on bh.EventID = eh.EventID
where EventDateTime > @.EventMin
and BoatID = @.BoatID
and voidflag = 0
And eh.Event IN ('Pickup','Drop','Log Exchange','Off-Layup','Off-Repair','On-Layup','On-Repair','Morning Log','Bt Trip Dir Chg','End Boat Charter Out', 'Begin Boat Charter Out', 'Begin Boat Charter In')
insert into #TmpEvents
select null,BoatPositionID, Description, PositionDateTime, Direction, river, mile
from mtsBoatPosition (NOLOCK)
where PositionDateTime > @.EventMin
and BoatID = @.BoatID
select * from #TmpEvents Order By EventDateTime
drop table #TmpEvents
END

Linda, you can either change the stored procedure definition in the script generated by the snapshot agent to the following:

CREATE PROCEDURE [dbo].[spBTG_GetEventsSince](@.EventMin datetime, @.BoatID int) AS

BEGIN
create table #TmpEvents
(
BoatHistoryID int null,
PositionID int null,
Event varchar(50) null,
EventDateTime datetime null,
Direction char(1) null,
River char(3) null,
Mile numeric(6,2) null
)

insert into #TmpEvents
select bh.BoatHistoryID, null, Event, EventDateTime, Direction, riverloc, mileloc
--jds 6/22/05 hardcoded index to fix it when you call it from vb
-- from mtsEventHistory eh (NOLOCK)
-- left join mtsBoatHistory bh (NOLOCK) on bh.EventID = eh.EventID
from mtsEventHistory eh with (NOLOCK, index(PK_MtsEventHistory))
left join mtsBoatHistory bh with (NOLOCK, index(IDX_MtsBoatHistory_BoatID)) on bh.EventID = eh.EventID
where EventDateTime > @.EventMin
and BoatID = @.BoatID
and voidflag = 0
And eh.Event IN ('Pickup','Drop','Log Exchange','Off-Layup','Off-Repair','On-Layup','On-Repair','Morning Log','Bt Trip Dir Chg','End Boat Charter Out', 'Begin Boat Charter Out', 'Begin Boat Charter In')
insert into #TmpEvents
select null,BoatPositionID, Description, PositionDateTime, Direction, river, mile
from mtsBoatPosition (NOLOCK)
where PositionDateTime > @.EventMin
and BoatID = @.BoatID
select * from #TmpEvents Order By EventDateTime
drop table #TmpEvents
END

Or, you can change the compatibility level of your subscriber database to 80 or lower.

-Raymond

|||

Raymond,

This sp currently runs on SQL2000. However, before we implement replication, it will be running on 2005. Is the change "adding with" something that will be required for SQL 2005 or does it have to do with replication?

Linda

|||

The use of the "with" keyword with query hints is a SQL2005 requirement that is not directly related to replication. As I mentioned before, you can always change the dbcmptlevel of your subscriber database to 80 or lower so the old syntax in your procedure can be accepted.

-Raymond

|||

Raymond,

On the subscriber database, do I run this command:

EXEC sp_dbcmptlevel MMS_20061213, 80;

to change the cmptlevel?

I did that and now I am getting this error:

Command attempted:

/* ============================================================ */
/* View: vMcsHeaders */
/* ============================================================ */

CREATE VIEW "dbo"."vMcsHeaders" AS

/* VIEW: vMcsHeaders
ABSTRACT: Used by the Contract System to work with contract headers
AUTHOR DATE
EJB 3/31/98 Created
SRM 4/8/98 - Added Customer_Code
EJB 5/1/98 - added filter for only contracts that are not deleteme = 1
EJB 6/10/98 - Ad
(Transaction sequence number: 0x00046CEA0000F21D005600000000, Command ID: 3385)

Error messages:

Invalid object name 'McsDemurrageSetUp'. (Source: MSSQLServer, Error number: 208)
Get help: http://help/208

Invalid object name 'McsDemurrageSetUp'. (Source: MSSQLServer, Error number: 208)
Get help: http://help/208

I notice the McsDemurrageSetUp is it referencing is spelled McsDemurrageSetup. How do I track down what kind of error this is? (Perhaps, I did not get the compatibility set correctly to 80?)

|||

It would appear that you are replicating from a case-insensitive database to a case-sensitive database, this is not a supported scenario.

|||

Raymond,

If I run the command: sp_server_info, both databases say "MIXED" under identifier case. However, I did figure out the problem. The storedprocedure is referencing a table that was not replicated because it did not have a primary key. I did not notice that initially when I noticed the case was different. The database I am working with needs a lot of work before it can successfully be replicated!

Thanks,

Linda

Distribution Agent - Stored Procedure Error Logging

I know how I can set-up my distribution agent jobs to log them
to a file and up the verbosity of the procedure.
I am in the process of creating snap-shot's of my stored procedures
for replication. The Snapshot agent works just fine, it is
when I use the distribution agent to send it to the subscriber
that it will stop on the first error it finds.
Is there a way to have it run through all the Stored Procedures
so that I don't have to constantly re-create the publication
by removing the first problem sp?
It helps me give the sp's to our developers in one shot
rather than one at a time.
Dave
I think your best bet is to create a separate publication for each stored
procedure. This way the only procs which fail to be replicated are the
problem ones - the remainder will be replicated.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"David Gresham" <gresham@.panix.com> wrote in message
news:d62hq1$mar$1@.reader1.panix.com...
> I know how I can set-up my distribution agent jobs to log them
> to a file and up the verbosity of the procedure.
> I am in the process of creating snap-shot's of my stored procedures
> for replication. The Snapshot agent works just fine, it is
> when I use the distribution agent to send it to the subscriber
> that it will stop on the first error it finds.
>
> Is there a way to have it run through all the Stored Procedures
> so that I don't have to constantly re-create the publication
> by removing the first problem sp?
> It helps me give the sp's to our developers in one shot
> rather than one at a time.
>
> Dave
>

distributed transaction problem on remote server

I am getting a problem in executing this distributed query



CREATE PROCEDURE [sp_ec2fetch] AS



set XACT_ABORT on

begin distributed tran



select * from OPENDATASOURCE(

'SQLOLEDB',

'Data Source=[ip];User ID=[uid];Password=[pass]'

).dml.dbo.act where code like '4010101000'

commit tran

GO



When I run it with a local LAN IP it works fine, but when I use a public ip my analyzer hangs and doesnt give me any output. I am using a dedicated dialup 64kbps connection. I have tried this query without begin distributed trans and commit trans and it worked fine, may be there are extra overheads with transactions, if so let me know



As an alternate I have tried registering server with sp_addlinkedserver and then executing a query with direct server reference, but had the same problem with it.Ensure MSDTC is started on both the servers.

Distributed Transaction problem

Hi,
We have a procedure that delete the records from a local
database table and then delete the records from another
database table that can be on local or remote machine. I
am testing the procedure with both the databases on same
local machine but getting following error when trying to
execute procedure:
The operation could not be performed because the OLE DB
provider 'SQLOLEDB' was unable to begin a distributed
transaction.
[OLE/DB provider returned message: New transaction cannot
enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
I have a databaseA on local machine HARV and databaseB
also on HARV
I created a linked server QA1 for databaseB
Following select statement works fine:
select * from qa1.databaseB.dbo.tab1
and following delete statement also works fine:
delete from qa1.databaseB.dbo.tab1 where col1 = 1
BUT when I want to use begin and commit tran it doesn't
worked:
I tried using begin distributed tran but didn't helped
OS is Windows 2000 and standard edition (not win 2003 or
enterprise edition)
MSDTC is running
@.@.servername is not null
There are no triggers on tables
Any feedback will be appreciated?
Thanks
--HarvinderThat is a known limitation of loopback linked servers and DTC
Look up loopback linked server in BOL. Scroll down and read:
--snip--
Loopback linked servers cannot be used in a distributed transaction.
Attempting a distributed query against a loopback linked server from within
a distributed transaction causes an error:
Msg: 3910 Level: 16 State: 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction conte
xt in use by
another session.
--snip--Bummer.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Harvinder" <hs@.metratech.com> wrote in message
news:1259b01c410fd$d1a135b0$a101280a@.phx
.gbl...
> Hi,
> We have a procedure that delete the records from a local
> database table and then delete the records from another
> database table that can be on local or remote machine. I
> am testing the procedure with both the databases on same
> local machine but getting following error when trying to
> execute procedure:
> The operation could not be performed because the OLE DB
> provider 'SQLOLEDB' was unable to begin a distributed
> transaction.
> [OLE/DB provider returned message: New transaction cannot
> enlist in the specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> I have a databaseA on local machine HARV and databaseB
> also on HARV
> I created a linked server QA1 for databaseB
> Following select statement works fine:
> select * from qa1.databaseB.dbo.tab1
> and following delete statement also works fine:
> delete from qa1.databaseB.dbo.tab1 where col1 = 1
> BUT when I want to use begin and commit tran it doesn't
> worked:
> I tried using begin distributed tran but didn't helped
> OS is Windows 2000 and standard edition (not win 2003 or
> enterprise edition)
> MSDTC is running
> @.@.servername is not null
> There are no triggers on tables
> Any feedback will be appreciated?
> Thanks
> --Harvinder
>

Distributed transaction error, need help please!

Hi, I have configured a linked server, and i have a procedure which makes an UPDATE in a local table using the data in the linked server.

Specifically, I have a function which checks if a given code exists in a linked server's table. The UPDATE changes the value of a column in a local table, if the function returns 1.

I've run the procedure and it gave an error after a few hours cause a simple conversion error inside the function. I solved the error. After this, the procedure did not work more. It gives me the following message:

Server: Msg 7391, Level 16, State 1, Procedure EXISTEONC, Line 16
The operation could not be performed because the OLE DB provider 'MSDASQL'
was unable to begin a distributed transaction.

(EXISTEONC is the function, and in the line 16 there is an OPENQUERY)
Im sure MSDTC is working... i'm lost because i dont know why it worked the first time and not now. Ive also wrote the function again as it was before, but it still doesent works.

Thanks a lot...don't mean to ask a silly question but have you recompiled the stroed procedure ?|||Do you mean opening the procedure and closing it again??
Yes, I did that...|||i'm trying to understand - is the function being called from a stroed proc

wording is not too clear|||i'll try to explain me...

I have a stored proc, it looks like this:

BEGIN TRANS
UPDATE CodeTable SET Found='Yes' WHERE EXISTEONC(ONCCode)=1
COMMIT

the function EXISTEONC takes a code (ONCCode is a field in CodeTable) and searchs it in a table in the linked server. If it was found, it returns 1.

The error appears in line 16 of the function (in the openquery statement to search the code), but only if i execute the Procedure:
If i write this in the Analyzer:
PRINT EXISTEONC('1234')
there is no error, i think, the error appears only if the function is executed inside a transaction ...|||First thing is I would be 100% sure that DTC is running ok

If this is OK go backa and drop and recreate everything i.e. stroed proc and the function

If this is still not working then it may be some sort of corruption in the data access components on your server thats messed up the DLL's responsible for OLE DB.

Not sure how to approach this - Reinstall the MDAC?

Any ideas folks ??|||I'm 100% sure DTC is running (i stopped it, and restarted it about 500000 times).
I compiled the function and the procedure again...
With SQL server I can see the data in the linked server. Remember: if I execute the function outside the UPDATE, for example:
PRINT dbo.EXISTEONC('1111')
it works, so, the connection is working and the data is not corrupted.

but, the *&#%#@. procedure still doesent work...

I forgot to say this: the server database is a Sybase Adaptiver Server Anywhere 6.0, and I connect to it through an ODBC... i access the ODBC in Sql Server 2000.

distributed transaction error from CLR Trigger

I have a stored procedure in SQL2005 that queries and updates a linked oracle server. The sp runs fine from Management Studio, but when called by a CLR trigger I get the following error message:

Command attempted:

if @.@.trancount > 0 rollback tran
(Transaction sequence number: 0x000000000000000032DD00000000, Command ID: 1)

Error messages:

The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" was unable to begin a distributed transaction. (Source: MSSQLServer, Error number: 7391)
Get help: http://help/7391

The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" was unable to begin a distributed transaction. (Source: MSSQLServer, Error number: 7391)
Get help: http://help/7391

A .NET Framework error occurred during execution of user defined routine or aggregate 'PriorityTrigger':
System.Data.SqlClient.SqlException: The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" was unable to begin a distributed transaction.
Changed database context to 'pims'.
OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" returned message "New transaction cannot enlist in the specified transaction coordinator. ".
System.Data.S (Source: MSSQLServer, Error number: 6549)
Get help: http://help/6549

Any thoughts or direction appreciated

Richard

Hi!

You may try to play with settings of OraOLEDB (DistribTX).

|||The procedure will probably fail the same way when run from a standard TSQL trigger. DTC is pulled in when you run the update within an existing local transaction. There should be an option for your linked server that will disable automatic enlistment if you don't need the transaction distributed to the remote machine. Otherwise you'll have to play with the Oledb provider & DTC setup to get distributed transactions working between the two.|||
--
HOW TO CONFIGURE DISTIBUTED TRANSACTION COORDINATION
--

First verify the "Distribute Transaction Coordinator" Service is
running on both database server computer and client computers
1. Go to "Administrative Tools > Services"
2. Turn on the "Distribute Transaction Coordinator" Service if it is not running

If it is running and client application is not on the same computer as
the database server, on the computer running database server
1. Go to "Administrative Tools > Component Services"
2. On the left navigation tree, go to "Component Services > Computers
> My Computer" (you may need to double click and wait as some nodes
need time to expand)
3. Right click on "My Computer", select "Properties"
4. Select "MSDTC" tab
5. Click "Security Configuration"
6. Make sure you check "Network DTC Access", "Allow Remote Client",
"Allow Inbound/Outbound", "Enable TIP" (Some option may not be
necessary, have a try to get your configuration)
7. The service will restart
8. BUT YOU MAY NEED TO REBOOT YOUR SERVER IF IT STILL DOESN'T WORK
(This is the thing drove me crazy before)

On your client computer use the same above procedure to open the
"Security Configuration" setting, make sure you check "Network DTC
Access", "Allow Inbound/Outbound" option, restart service and computer
if necessary.

On you SQL server service manager, click "Service" dropdown, select
"Distribute Transaction Coordinator", it should be also running on
your server computer.

distributed transaction error from CLR Trigger

I have a stored procedure in SQL2005 that queries and updates a linked oracle server. The sp runs fine from Management Studio, but when called by a CLR trigger I get the following error message:

Command attempted:

if @.@.trancount > 0 rollback tran
(Transaction sequence number: 0x000000000000000032DD00000000, Command ID: 1)

Error messages:

The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" was unable to begin a distributed transaction. (Source: MSSQLServer, Error number: 7391)
Get help: http://help/7391

The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" was unable to begin a distributed transaction. (Source: MSSQLServer, Error number: 7391)
Get help: http://help/7391

A .NET Framework error occurred during execution of user defined routine or aggregate 'PriorityTrigger':
System.Data.SqlClient.SqlException: The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" was unable to begin a distributed transaction.
Changed database context to 'pims'.
OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" returned message "New transaction cannot enlist in the specified transaction coordinator. ".
System.Data.S (Source: MSSQLServer, Error number: 6549)
Get help: http://help/6549

Any thoughts or direction appreciated

Richard

Hi!

You may try to play with settings of OraOLEDB (DistribTX).

|||The procedure will probably fail the same way when run from a standard TSQL trigger. DTC is pulled in when you run the update within an existing local transaction. There should be an option for your linked server that will disable automatic enlistment if you don't need the transaction distributed to the remote machine. Otherwise you'll have to play with the Oledb provider & DTC setup to get distributed transactions working between the two.|||
--
HOW TO CONFIGURE DISTIBUTED TRANSACTION COORDINATION
--

First verify the "Distribute Transaction Coordinator" Service is
running on both database server computer and client computers
1. Go to "Administrative Tools > Services"
2. Turn on the "Distribute Transaction Coordinator" Service if it is not running

If it is running and client application is not on the same computer as
the database server, on the computer running database server
1. Go to "Administrative Tools > Component Services"
2. On the left navigation tree, go to "Component Services > Computers
> My Computer" (you may need to double click and wait as some nodes
need time to expand)
3. Right click on "My Computer", select "Properties"
4. Select "MSDTC" tab
5. Click "Security Configuration"
6. Make sure you check "Network DTC Access", "Allow Remote Client",
"Allow Inbound/Outbound", "Enable TIP" (Some option may not be
necessary, have a try to get your configuration)
7. The service will restart
8. BUT YOU MAY NEED TO REBOOT YOUR SERVER IF IT STILL DOESN'T WORK
(This is the thing drove me crazy before)

On your client computer use the same above procedure to open the
"Security Configuration" setting, make sure you check "Network DTC
Access", "Allow Inbound/Outbound" option, restart service and computer
if necessary.

On you SQL server service manager, click "Service" dropdown, select
"Distribute Transaction Coordinator", it should be also running on
your server computer.

Distributed transaction error

Hi,
I have written a procedure as below. I'm connected to a remote server server1 with valid

login id and permissions. When I run the procedure, I get the following error.

Server: Msg 7391, Level 16, State 1, Procedure procAddMissingAssociates, Line 09
The operation could not be performed because the OLE DB provider 'SQLOLEDB' does not support

distributed transactions.
[OLE/DB provider returned message: Distributed transaction error]

MSDTC is enabled on both the server, the local as well as the remote ones.
In the example below ASSOCIATE_ID in table2 is a primary key.

Please help,
Thanks
P.C. Vaidyanathan

CREATE PROCEDURE procAddMissingAssociates
AS
DECLARE @.USERID INT

BEGIN
SET TRANSACTION ISOLATION LEVEL repeatable read

BEGIN DISTRIBUTED TRANSACTION
SET XACT_ABORT ON

DECLARE MISSING_CURSOR CURSOR FOR
SELECT USR_ID
FROM SERVER1.DB1.DBO.TABLE1
WHERE USR_ID NOT IN (SELECT COLUMN2
FROM TABLE2)

OPEN MISSING_CURSOR
FETCH FROM MISSING_CURSOR
INTO @.USERID

WHILE (@.@.FETCH_STATUS=0)
BEGIN
INSERT INTO TABLE2 (ASSOCIATE_ID,
REDEEM_POINTS,
UPDATED_DATE)
VALUES (@.USERID,
0,
GETDATE())
FETCH NEXT FROM MISSING_CURSOR
INTO @.USERID
END
CLOSE MISSING_CURSOR
DEALLOCATE MISSING_CURSOR
COMMIT TRANSACTION
END
GOif your servers connect to each other trough the Firewall, check that all necessary ports are open in both directions. As usually it is 1433 and 134|||SERVER1.DB1.DBO.TABLE1

are both sql servers on the same network and starting as the same user? how are you authenticating with server1? linked server?

the above post is right, if you are on seperate networks, you'll need to have access to the other machine thru the firewall.

if you cant/dont want to setup a linked server, you can also use OPENROWSET in an encrypted view.

if thats not the problem.. from another forum

"To work around this problem, set XACT_ABORT to ON before the transaction. This causes SQL Server to terminate the surrounding transaction when an error occurs while processing the data modification statement. If SET XACT_ABORT is ON, SQL Server does not require nested transaction support from the OLE DB Provider. "

Also, try the following:

http://dbforums.com/t361216.html

Distributed Transaction causes hanging.

I have a stored procedure utilizing distributed transactions. When I run
the stored procedure with "BEGIN DISTRIBUTED TRANSACTION" specified, it
hangs where it runs a select statement against the remote server. With the
"BEGIN DISTRIBUTED TRANSACTION" commented, the stored procedure runs ok.
All suggestions are appreciated.
--
Message posted via http://www.sqlmonster.comAn update to this...
SQL Server 2003 comes with DCTS switched on by default (which I saw in the
properties). However, there is then a security setting that must be set to
allow distributed transactions. I will not know if this did it until the
the setting has been changed and the server rebooted.
--
Message posted via http://www.sqlmonster.com

Distributed Transaction causes hanging.

I have a stored procedure utilizing distributed transactions. When I run
the stored procedure with "BEGIN DISTRIBUTED TRANSACTION" specified, it
hangs where it runs a select statement against the remote server. With the
"BEGIN DISTRIBUTED TRANSACTION" commented, the stored procedure runs ok.
All suggestions are appreciated.
Message posted via http://www.sqlmonster.com
An update to this...
SQL Server 2003 comes with DCTS switched on by default (which I saw in the
properties). However, there is then a security setting that must be set to
allow distributed transactions. I will not know if this did it until the
the setting has been changed and the server rebooted.
Message posted via http://www.sqlmonster.com

Distributed Transaction causes hanging.

I have a stored procedure utilizing distributed transactions. When I run
the stored procedure with "BEGIN DISTRIBUTED TRANSACTION" specified, it
hangs where it runs a select statement against the remote server. With the
"BEGIN DISTRIBUTED TRANSACTION" commented, the stored procedure runs ok.
All suggestions are appreciated.
Message posted via http://www.droptable.comAn update to this...
SQL Server 2003 comes with DCTS switched on by default (which I saw in the
properties). However, there is then a security setting that must be set to
allow distributed transactions. I will not know if this did it until the
the setting has been changed and the server rebooted.
Message posted via http://www.droptable.com