openning cursor inside trigger works in sql2000 but not in 2005

Hello everyone,
I have a delete trigger on table, inside which there is cursor opened using
a dynamically generated query.
this worked fine on sql 2000, but on 2005, I get the following error:
Msg 16958
Could not complete cursor operation because the set options have changed
since the cursor was declared.
for testing, I replaced the generated query by a static one and it worked
fine.
any Ideas ?
here is the code:
select * into #TabTmp from deleted
set @.req = 'declare CUR1 cursor for select ' + @.Cle + ' from #TabTmp'
execute(@.req)
OPEN CUR1
FETCH CUR into @.val
the error is generated on the "OPEN CUR1" statement.
I did a DBCC USEROPTIONS before and after the "execute" statement but there
was no change.
thanks in advance.
It sounds like you've hit this bug:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=247905
-Sue
On Mon, 14 May 2007 12:16:02 -0700, r_samir
<rsamir@.discussions.microsoft.com> wrote:

>Hello everyone,
>I have a delete trigger on table, inside which there is cursor opened using
>a dynamically generated query.
>this worked fine on sql 2000, but on 2005, I get the following error:
>Msg 16958
>Could not complete cursor operation because the set options have changed
>since the cursor was declared.
>for testing, I replaced the generated query by a static one and it worked
>fine.
>any Ideas ?
>here is the code:
>----
>select * into #TabTmp from deleted
>set @.req = 'declare CUR1 cursor for select ' + @.Cle + ' from #TabTmp'
>execute(@.req)
>OPEN CUR1
>FETCH CUR into @.val
>---
>the error is generated on the "OPEN CUR1" statement.
>I did a DBCC USEROPTIONS before and after the "execute" statement but there
>was no change.
>thanks in advance.
|||Maybe it's not the same bug - I missed the part where you
said you replaced the dynamic SQL and it worked.
So if that worked and then cursors inside triggers aren't
necessarily the best idea, if dynamic sql isn't necessarily
the best idea, then maybe it's better to just redo the logic
and change the code for the trigger?
-Sue
On Mon, 14 May 2007 12:16:02 -0700, r_samir
<rsamir@.discussions.microsoft.com> wrote:

>Hello everyone,
>I have a delete trigger on table, inside which there is cursor opened using
>a dynamically generated query.
>this worked fine on sql 2000, but on 2005, I get the following error:
>Msg 16958
>Could not complete cursor operation because the set options have changed
>since the cursor was declared.
>for testing, I replaced the generated query by a static one and it worked
>fine.
>any Ideas ?
>here is the code:
>----
>select * into #TabTmp from deleted
>set @.req = 'declare CUR1 cursor for select ' + @.Cle + ' from #TabTmp'
>execute(@.req)
>OPEN CUR1
>FETCH CUR into @.val
>---
>the error is generated on the "OPEN CUR1" statement.
>I did a DBCC USEROPTIONS before and after the "execute" statement but there
>was no change.
>thanks in advance.
|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:cp6i43p2dgq16qcic4d36dodp6pl3pmq3q@.4ax.com...
> Maybe it's not the same bug - I missed the part where you
> said you replaced the dynamic SQL and it worked.
> So if that worked and then cursors inside triggers aren't
> necessarily the best idea, if dynamic sql isn't necessarily
> the best idea, then maybe it's better to just redo the logic
> and change the code for the trigger?
A bug that should guide the user to a more logical
and socially accepted solution. Kewl spin-a-rama.
Why waste such talent on such a niche audience.
It's the government that really rewards such
artistic sophistry -
|||thanks for your prompt response
redoing the logic is feasable but would take some time, we are currently
investigating this way.
but is there a way to verify whether we fall into that bug or not ?
"Sue Hoegemeier" wrote:

> Maybe it's not the same bug - I missed the part where you
> said you replaced the dynamic SQL and it worked.
> So if that worked and then cursors inside triggers aren't
> necessarily the best idea, if dynamic sql isn't necessarily
> the best idea, then maybe it's better to just redo the logic
> and change the code for the trigger?
> -Sue
> On Mon, 14 May 2007 12:16:02 -0700, r_samir
> <rsamir@.discussions.microsoft.com> wrote:
>
>

openning cursor inside trigger works in sql2000 but not in 2005

Hello everyone,
I have a delete trigger on table, inside which there is cursor opened using
a dynamically generated query.
this worked fine on sql 2000, but on 2005, I get the following error:
Msg 16958
Could not complete cursor operation because the set options have changed
since the cursor was declared.
for testing, I replaced the generated query by a static one and it worked
fine.
any Ideas ?
here is the code:
----
select * into #TabTmp from deleted
set @.req = 'declare CUR1 cursor for select ' + @.Cle + ' from #TabTmp'
execute(@.req)
OPEN CUR1
FETCH CUR into @.val
---
the error is generated on the "OPEN CUR1" statement.
I did a DBCC USEROPTIONS before and after the "execute" statement but there
was no change.
thanks in advance.It sounds like you've hit this bug:
https://connect.microsoft.com/SQLSe...=2479
05
-Sue
On Mon, 14 May 2007 12:16:02 -0700, r_samir
<rsamir@.discussions.microsoft.com> wrote:

>Hello everyone,
>I have a delete trigger on table, inside which there is cursor opened using
>a dynamically generated query.
>this worked fine on sql 2000, but on 2005, I get the following error:
>Msg 16958
>Could not complete cursor operation because the set options have changed
>since the cursor was declared.
>for testing, I replaced the generated query by a static one and it worked
>fine.
>any Ideas ?
>here is the code:
>----
>select * into #TabTmp from deleted
>set @.req = 'declare CUR1 cursor for select ' + @.Cle + ' from #TabTmp'
>execute(@.req)
>OPEN CUR1
>FETCH CUR into @.val
>---
>the error is generated on the "OPEN CUR1" statement.
>I did a DBCC USEROPTIONS before and after the "execute" statement but ther
e
>was no change.
>thanks in advance.|||Maybe it's not the same bug - I missed the part where you
said you replaced the dynamic SQL and it worked.
So if that worked and then cursors inside triggers aren't
necessarily the best idea, if dynamic sql isn't necessarily
the best idea, then maybe it's better to just redo the logic
and change the code for the trigger?
-Sue
On Mon, 14 May 2007 12:16:02 -0700, r_samir
<rsamir@.discussions.microsoft.com> wrote:

>Hello everyone,
>I have a delete trigger on table, inside which there is cursor opened using
>a dynamically generated query.
>this worked fine on sql 2000, but on 2005, I get the following error:
>Msg 16958
>Could not complete cursor operation because the set options have changed
>since the cursor was declared.
>for testing, I replaced the generated query by a static one and it worked
>fine.
>any Ideas ?
>here is the code:
>----
>select * into #TabTmp from deleted
>set @.req = 'declare CUR1 cursor for select ' + @.Cle + ' from #TabTmp'
>execute(@.req)
>OPEN CUR1
>FETCH CUR into @.val
>---
>the error is generated on the "OPEN CUR1" statement.
>I did a DBCC USEROPTIONS before and after the "execute" statement but ther
e
>was no change.
>thanks in advance.|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:cp6i43p2dgq16qcic4d36dodp6pl3pmq3q@.
4ax.com...
> Maybe it's not the same bug - I missed the part where you
> said you replaced the dynamic SQL and it worked.
> So if that worked and then cursors inside triggers aren't
> necessarily the best idea, if dynamic sql isn't necessarily
> the best idea, then maybe it's better to just redo the logic
> and change the code for the trigger?
A bug that should guide the user to a more logical
and socially accepted solution. Kewl spin-a-rama.
Why waste such talent on such a niche audience.
It's the government that really rewards such
artistic sophistry -|||thanks for your prompt response
redoing the logic is feasable but would take some time, we are currently
investigating this way.
but is there a way to verify whether we fall into that bug or not ?
"Sue Hoegemeier" wrote:

> Maybe it's not the same bug - I missed the part where you
> said you replaced the dynamic SQL and it worked.
> So if that worked and then cursors inside triggers aren't
> necessarily the best idea, if dynamic sql isn't necessarily
> the best idea, then maybe it's better to just redo the logic
> and change the code for the trigger?
> -Sue
> On Mon, 14 May 2007 12:16:02 -0700, r_samir
> <rsamir@.discussions.microsoft.com> wrote:
>
>

openning cursor inside trigger works in sql2000 but not in 2005

Hello everyone,
I have a delete trigger on table, inside which there is cursor opened using
a dynamically generated query.
this worked fine on sql 2000, but on 2005, I get the following error:
Msg 16958
Could not complete cursor operation because the set options have changed
since the cursor was declared.
for testing, I replaced the generated query by a static one and it worked
fine.
any Ideas ?
here is the code:
----
select * into #TabTmp from deleted
set @.req = 'declare CUR1 cursor for select ' + @.Cle + ' from #TabTmp'
execute(@.req)
OPEN CUR1
FETCH CUR into @.val
---
the error is generated on the "OPEN CUR1" statement.
I did a DBCC USEROPTIONS before and after the "execute" statement but there
was no change.
thanks in advance.It sounds like you've hit this bug:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=247905
-Sue
On Mon, 14 May 2007 12:16:02 -0700, r_samir
<rsamir@.discussions.microsoft.com> wrote:
>Hello everyone,
>I have a delete trigger on table, inside which there is cursor opened using
>a dynamically generated query.
>this worked fine on sql 2000, but on 2005, I get the following error:
>Msg 16958
>Could not complete cursor operation because the set options have changed
>since the cursor was declared.
>for testing, I replaced the generated query by a static one and it worked
>fine.
>any Ideas ?
>here is the code:
>----
>select * into #TabTmp from deleted
>set @.req = 'declare CUR1 cursor for select ' + @.Cle + ' from #TabTmp'
>execute(@.req)
>OPEN CUR1
>FETCH CUR into @.val
>---
>the error is generated on the "OPEN CUR1" statement.
>I did a DBCC USEROPTIONS before and after the "execute" statement but there
>was no change.
>thanks in advance.|||Maybe it's not the same bug - I missed the part where you
said you replaced the dynamic SQL and it worked.
So if that worked and then cursors inside triggers aren't
necessarily the best idea, if dynamic sql isn't necessarily
the best idea, then maybe it's better to just redo the logic
and change the code for the trigger?
-Sue
On Mon, 14 May 2007 12:16:02 -0700, r_samir
<rsamir@.discussions.microsoft.com> wrote:
>Hello everyone,
>I have a delete trigger on table, inside which there is cursor opened using
>a dynamically generated query.
>this worked fine on sql 2000, but on 2005, I get the following error:
>Msg 16958
>Could not complete cursor operation because the set options have changed
>since the cursor was declared.
>for testing, I replaced the generated query by a static one and it worked
>fine.
>any Ideas ?
>here is the code:
>----
>select * into #TabTmp from deleted
>set @.req = 'declare CUR1 cursor for select ' + @.Cle + ' from #TabTmp'
>execute(@.req)
>OPEN CUR1
>FETCH CUR into @.val
>---
>the error is generated on the "OPEN CUR1" statement.
>I did a DBCC USEROPTIONS before and after the "execute" statement but there
>was no change.
>thanks in advance.|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:cp6i43p2dgq16qcic4d36dodp6pl3pmq3q@.4ax.com...
> Maybe it's not the same bug - I missed the part where you
> said you replaced the dynamic SQL and it worked.
> So if that worked and then cursors inside triggers aren't
> necessarily the best idea, if dynamic sql isn't necessarily
> the best idea, then maybe it's better to just redo the logic
> and change the code for the trigger?
A bug that should guide the user to a more logical
and socially accepted solution. Kewl spin-a-rama.
Why waste such talent on such a niche audience.
It's the government that really rewards such
artistic sophistry -:)|||thanks for your prompt response
redoing the logic is feasable but would take some time, we are currently
investigating this way.
but is there a way to verify whether we fall into that bug or not ?
"Sue Hoegemeier" wrote:
> Maybe it's not the same bug - I missed the part where you
> said you replaced the dynamic SQL and it worked.
> So if that worked and then cursors inside triggers aren't
> necessarily the best idea, if dynamic sql isn't necessarily
> the best idea, then maybe it's better to just redo the logic
> and change the code for the trigger?
> -Sue
> On Mon, 14 May 2007 12:16:02 -0700, r_samir
> <rsamir@.discussions.microsoft.com> wrote:
> >Hello everyone,
> >
> >I have a delete trigger on table, inside which there is cursor opened using
> >a dynamically generated query.
> >this worked fine on sql 2000, but on 2005, I get the following error:
> >Msg 16958
> >Could not complete cursor operation because the set options have changed
> >since the cursor was declared.
> >
> >for testing, I replaced the generated query by a static one and it worked
> >fine.
> >
> >any Ideas ?
> >
> >here is the code:
> >
> >----
> >select * into #TabTmp from deleted
> >
> >set @.req = 'declare CUR1 cursor for select ' + @.Cle + ' from #TabTmp'
> >
> >execute(@.req)
> >
> >OPEN CUR1
> >
> >FETCH CUR into @.val
> >---
> >
> >the error is generated on the "OPEN CUR1" statement.
> >I did a DBCC USEROPTIONS before and after the "execute" statement but there
> >was no change.
> >
> >thanks in advance.
>

Opening port 1434

You will need to set up the port in Client Network Util -
add alias for sql box and uncheck dynamically determine
ort and place 1434 in the box it opens up

>--Original Message--
>Hi.. I'm trying to connect a Sharepoint Portal Server to
a second named
>instance on a SQL box.
>After some trial and error I find that I need to have
port 1434 open. Now my
>question is how do I do that?
>.
>
That would imply that SQL Server is listening on port 1434. Usually SQL
Server listens on port 1433, and the reason you'd need port 1434 is for UDP
1434 to allow the "SQL Browser" capabilities of SQL Server to locate named
instances on a server.
Richard Waymire, MCSE, MCDBA
This posting is provided "AS IS" with no warranties, and confers no rights.
"kjackson" <kh2ojz@.discussions.microsoft.com> wrote in message
news:0cbb01c47b0e$25d8a490$a401280a@.phx.gbl...[vbcol=seagreen]
> You will need to set up the port in Client Network Util -
> add alias for sql box and uncheck dynamically determine
> ort and place 1434 in the box it opens up
>
> a second named
> port 1434 open. Now my

Opening port 1434

You will need to set up the port in Client Network Util -
add alias for sql box and uncheck dynamically determine
ort and place 1434 in the box it opens up

>--Original Message--
>Hi.. I'm trying to connect a Sharepoint Portal Server to
a second named
>instance on a SQL box.
>After some trial and error I find that I need to have
port 1434 open. Now my
>question is how do I do that?
>.
>That would imply that SQL Server is listening on port 1434. Usually SQL
Server listens on port 1433, and the reason you'd need port 1434 is for UDP
1434 to allow the "SQL Browser" capabilities of SQL Server to locate named
instances on a server.
Richard Waymire, MCSE, MCDBA
This posting is provided "AS IS" with no warranties, and confers no rights.
"kjackson" <kh2ojz@.discussions.microsoft.com> wrote in message
news:0cbb01c47b0e$25d8a490$a401280a@.phx.gbl...[vbcol=seagreen]
> You will need to set up the port in Client Network Util -
> add alias for sql box and uncheck dynamically determine
> ort and place 1434 in the box it opens up
>
> a second named
> port 1434 open. Now my

Open .RDL in ASP.NET without ReportServer

Is it possible to simply add an .RDL to an asp.net project, and access the
report dynamically without any report url or anything.
I just want to pass the database authentication to the .RDL [reportViewer?]
and a few parameters, and run the report. But I do not want the report to
exist in a subweb or http://reportServer etc. I just want to drop it right
into my project, and go.
Thanks for any links or advice.This is totally possible with the new controls that ship with VS 2005. You
can develop the report and then view it in your app using the controls. Note
that this is more work. You have to handle subreports, drill through etc.
Also, I found the design environment in VS 2005 that comes with VS to design
reports nowhere as near as nice as the report designer that comes with SQL
Server. Note that if you have SQL Server, the designer comes with it. You
can develop and preview the reports without deploying them, then just change
the extension and bring them into your project.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Chris" <cmill575@.hotmail.com> wrote in message
news:u5QFgtdBGHA.3604@.TK2MSFTNGP09.phx.gbl...
> Is it possible to simply add an .RDL to an asp.net project, and access the
> report dynamically without any report url or anything.
> I just want to pass the database authentication to the .RDL
> [reportViewer?] and a few parameters, and run the report. But I do not
> want the report to exist in a subweb or http://reportServer etc. I just
> want to drop it right into my project, and go.
> Thanks for any links or advice.
>|||Thanks for your response. That's good news. I hope MS improves the designer
in VS.
Cant you just design the report using the SQL Server report designer and
then drop the rdl into your web project?
Anyway, thanks again.
BTW: Does anyone have any walkthrough's using this new functionality? I
havent used RS since it first came out.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:u1R684jBGHA.3292@.TK2MSFTNGP09.phx.gbl...
> This is totally possible with the new controls that ship with VS 2005. You
> can develop the report and then view it in your app using the controls.
> Note that this is more work. You have to handle subreports, drill through
> etc. Also, I found the design environment in VS 2005 that comes with VS to
> design reports nowhere as near as nice as the report designer that comes
> with SQL Server. Note that if you have SQL Server, the designer comes with
> it. You can develop and preview the reports without deploying them, then
> just change the extension and bring them into your project.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Chris" <cmill575@.hotmail.com> wrote in message
> news:u5QFgtdBGHA.3604@.TK2MSFTNGP09.phx.gbl...
>> Is it possible to simply add an .RDL to an asp.net project, and access
>> the report dynamically without any report url or anything.
>> I just want to pass the database authentication to the .RDL
>> [reportViewer?] and a few parameters, and run the report. But I do not
>> want the report to exist in a subweb or http://reportServer etc. I just
>> want to drop it right into my project, and go.
>> Thanks for any links or advice.
>|||You can use the SQL Server report designer and then rename the extension and
then use that. That is really the easiest. The issue with VS is that there
is no preview. You have to deploy and use either your web page or your
winform to view the report.
Just dropping the rdl into your web project makes no sense unless there is
something to render the report. That is where the new controls come in.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Chris" <cmill575@.hotmail.com> wrote in message
news:%23Xt1UVpBGHA.2300@.TK2MSFTNGP15.phx.gbl...
> Thanks for your response. That's good news. I hope MS improves the
> designer in VS.
> Cant you just design the report using the SQL Server report designer and
> then drop the rdl into your web project?
> Anyway, thanks again.
> BTW: Does anyone have any walkthrough's using this new functionality? I
> havent used RS since it first came out.
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:u1R684jBGHA.3292@.TK2MSFTNGP09.phx.gbl...
>> This is totally possible with the new controls that ship with VS 2005.
>> You can develop the report and then view it in your app using the
>> controls. Note that this is more work. You have to handle subreports,
>> drill through etc. Also, I found the design environment in VS 2005 that
>> comes with VS to design reports nowhere as near as nice as the report
>> designer that comes with SQL Server. Note that if you have SQL Server,
>> the designer comes with it. You can develop and preview the reports
>> without deploying them, then just change the extension and bring them
>> into your project.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Chris" <cmill575@.hotmail.com> wrote in message
>> news:u5QFgtdBGHA.3604@.TK2MSFTNGP09.phx.gbl...
>> Is it possible to simply add an .RDL to an asp.net project, and access
>> the report dynamically without any report url or anything.
>> I just want to pass the database authentication to the .RDL
>> [reportViewer?] and a few parameters, and run the report. But I do not
>> want the report to exist in a subweb or http://reportServer etc. I just
>> want to drop it right into my project, and go.
>> Thanks for any links or advice.
>>
>