Question regarding Sql Server memory and cursors

Hello,

We make use of ADO (not ADO.NET) and server-side cursors in one of our products and when viewing Performance Statistics for Sql Server 2005 using Windows Performance Monitor we have noticed that the "Number of active cursor plans" never decreases. In our queries we maintain several open, server-side cursors concurrently. We use .NET/C# and the standard ADO import library. We are closing our recordsets.

The number of active plans jumps by 40 or 50 for each of our queries and as far as we can tell, never decreases (the number of Active Cursors, however, does decrease back to zero.) MSDN/Documentation was somewhat cryptic on what an Active Cursor Plan actually is and Google didn't have any useful information, either.

So, the question is, does this matter? What is an Active Cursor Plan? Is there something other than the recordset and connection that needs to be closed?

Any information or a reference to any useful documentation would be greatly appreciated.

Thanks a lot,

George Kondiles

See this http://blogs.msdn.com/sqlprogrammability/archive/2006/04/27/585170.aspx that says everything.

Question regarding returning data from stored procedure.

Hi all,
I am facing one problem while getting data from stored procedure into
.net application in dataset.
I have two tables say table1 and table2 in which i am continuously
adding data. When i am adding data to table1 same time i am adding data
to table2. This whole process is done through some other application
and not through my application.
But i have one stored procedure which is returing me data from table1
and table2. My logic is i am getting data from table1 into temp table(i
have some more condition for that purpose i need temp table) and then
for those records which are there in temp table i am finding its
equivalent entry in table2. means i should nearly get data same from
both the table.
But what is happening is when i am running same queries which i am
using in sp through query analyzer it is giving me proper resule. Bue
when i try to run those sp and store result of those sp into dataset i
am not getting all records at the same time.
So my question is why this is happening. Is is like that if some other
applications are updating those table then i can't retrieve those
records.
Please clear me if i am wrong.
Thanks in advance.sounds like a concurrency issue.
Please google or BoL for concurrency, locking, blocking, deadlocs and
phantom updates.
"Archana" wrote:

> Hi all,
> I am facing one problem while getting data from stored procedure into
> ..net application in dataset.
> I have two tables say table1 and table2 in which i am continuously
> adding data. When i am adding data to table1 same time i am adding data
> to table2. This whole process is done through some other application
> and not through my application.
> But i have one stored procedure which is returing me data from table1
> and table2. My logic is i am getting data from table1 into temp table(i
> have some more condition for that purpose i need temp table) and then
> for those records which are there in temp table i am finding its
> equivalent entry in table2. means i should nearly get data same from
> both the table.
> But what is happening is when i am running same queries which i am
> using in sp through query analyzer it is giving me proper resule. Bue
> when i try to run those sp and store result of those sp into dataset i
> am not getting all records at the same time.
> So my question is why this is happening. Is is like that if some other
> applications are updating those table then i can't retrieve those
> records.
> Please clear me if i am wrong.
> Thanks in advance.
>

Question re: security issue detailed in KB887459

Is the Report Manager vulnerable to the issue described here:
http://support.microsoft.com/?kbid=887459
I'm not very experienced with ASP.NET - can I assume that any additional
safeguards for the "canonicalization" issues would have to come from
Microsoft in the case of Report Manager as it is a compiled app? TIA.
-BAHI Brian:
There is now an MSI file that will install an HttpModule to protect
all ASP.NET applications.
See: http://www.microsoft.com/security/incident/aspnet.mspx
--
Scott
http://www.OdeToCode.com/
On Thu, 07 Oct 2004 17:00:46 -0700, Brian Almond
<pythonista@.sbcglobal.net> wrote:
>Is the Report Manager vulnerable to the issue described here:
>http://support.microsoft.com/?kbid=887459
>I'm not very experienced with ASP.NET - can I assume that any additional
>safeguards for the "canonicalization" issues would have to come from
>Microsoft in the case of Report Manager as it is a compiled app? TIA.
>-BA|||Scott Allen wrote:
> There is now an MSI file that will install an HttpModule to protect
> all ASP.NET applications.
> See: http://www.microsoft.com/security/incident/aspnet.mspx
Thanks for posting the link Scott. Unfortunately it looks like
something that MSI does has confused Report Manager on my our
development RS box so that now I'm getting a security exception when
browsing to it. Playing with it now to see if I just need to make
simple config. changes or if it's something more involved causing me
trouble.
-BA|||Interesting, I'll give it a try tommorow at home and see what happens.
--
Scott
http://www.OdeToCode.com/
On Fri, 08 Oct 2004 10:02:30 -0700, Brian Almond
<pythonista@.sbcglobal.net> wrote:
>Scott Allen wrote:
>> There is now an MSI file that will install an HttpModule to protect
>> all ASP.NET applications.
>> See: http://www.microsoft.com/security/incident/aspnet.mspx
>Thanks for posting the link Scott. Unfortunately it looks like
>something that MSI does has confused Report Manager on my our
>development RS box so that now I'm getting a security exception when
>browsing to it. Playing with it now to see if I just need to make
>simple config. changes or if it's something more involved causing me
>trouble.
>-BA|||Brian,
I had the exact same problem when I installed - let me know if you get a
resolution on this.
Thanks,
Dan
"Brian Almond" <pythonista@.sbcglobal.net> wrote in message
news:u8eYViVrEHA.3172@.TK2MSFTNGP10.phx.gbl...
> Scott Allen wrote:
> > There is now an MSI file that will install an HttpModule to protect
> > all ASP.NET applications.
> >
> > See: http://www.microsoft.com/security/incident/aspnet.mspx
> Thanks for posting the link Scott. Unfortunately it looks like
> something that MSI does has confused Report Manager on my our
> development RS box so that now I'm getting a security exception when
> browsing to it. Playing with it now to see if I just need to make
> simple config. changes or if it's something more involved causing me
> trouble.
> -BA|||Yes, it's a problem, unfortunately.
I have everything working again after adding a new CodeGroup to both
policy config files, see:
http://odetocode.com/Blogs/scott/archive/2004/10/08/538.aspx
Let me know if this gets you up and running again. If anyone from MS
has an official recommendation I'll update the blog.
--
Scott
http://www.OdeToCode.com/
On Fri, 8 Oct 2004 19:57:15 -0400, "Dan Plaskon"
<dplaskon@.sympatico.ca> wrote:
>Brian,
>I had the exact same problem when I installed - let me know if you get a
>resolution on this.
>Thanks,
>Dan
>"Brian Almond" <pythonista@.sbcglobal.net> wrote in message
>news:u8eYViVrEHA.3172@.TK2MSFTNGP10.phx.gbl...
>> Scott Allen wrote:
>> > There is now an MSI file that will install an HttpModule to protect
>> > all ASP.NET applications.
>> >
>> > See: http://www.microsoft.com/security/incident/aspnet.mspx
>> Thanks for posting the link Scott. Unfortunately it looks like
>> something that MSI does has confused Report Manager on my our
>> development RS box so that now I'm getting a security exception when
>> browsing to it. Playing with it now to see if I just need to make
>> simple config. changes or if it's something more involved causing me
>> trouble.
>> -BA
>|||That change doesn't work on my server. I get a parse error on the
ValidatePathModule line of
machine.config... Very strange error as it only says "?" as error message.
/Per Salmi
"Scott Allen" <bitmask@.[nospam].fred.net> skrev i meddelandet
news:4viem0dkbh4apfl41f6btmtufl6pq8mio0@.4ax.com...
> Yes, it's a problem, unfortunately.
> I have everything working again after adding a new CodeGroup to both
> policy config files, see:
> http://odetocode.com/Blogs/scott/archive/2004/10/08/538.aspx
> Let me know if this gets you up and running again. If anyone from MS
> has an official recommendation I'll update the blog.
> --
> Scott
> http://www.OdeToCode.com/
> On Fri, 8 Oct 2004 19:57:15 -0400, "Dan Plaskon"
> <dplaskon@.sympatico.ca> wrote:
>>Brian,
>>I had the exact same problem when I installed - let me know if you get a
>>resolution on this.
>>Thanks,
>>Dan
>>"Brian Almond" <pythonista@.sbcglobal.net> wrote in message
>>news:u8eYViVrEHA.3172@.TK2MSFTNGP10.phx.gbl...
>> Scott Allen wrote:
>> > There is now an MSI file that will install an HttpModule to protect
>> > all ASP.NET applications.
>> >
>> > See: http://www.microsoft.com/security/incident/aspnet.mspx
>> Thanks for posting the link Scott. Unfortunately it looks like
>> something that MSI does has confused Report Manager on my our
>> development RS box so that now I'm getting a security exception when
>> browsing to it. Playing with it now to see if I just need to make
>> simple config. changes or if it's something more involved causing me
>> trouble.
>> -BA
>|||Tried the same thing on another server and now the parse error on
machine.config says:
Description: An error occurred during the processing of a configuration file
required to service this request. Please review the specific error details
below and modify your configuration file appropriately.
Parser Error Message: Assembly microsoft.web.validatepathmodule.dll security
permission grant set is incompatible between appdomains.
Source Error:
Line 320: <add name="FileAuthorization"
type="System.Web.Security.FileAuthorizationModule"/>
Line 321: <add name="ErrorHandlerModule"
type="System.Web.Mobile.ErrorHandlerModule, System.Web.Mobile,
Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
Line 322: <add name="ValidatePathModule"
type="Microsoft.Web.ValidatePathModule, Microsoft.Web.ValidatePathModule,
Version=1.0.0.0, Culture=neutral,
PublicKeyToken=eba19824f86fdadd"/></httpModules>
Line 323: <!--
Line 324: processModel Attributes:
/Per Salmi
"Scott Allen" <bitmask@.[nospam].fred.net> skrev i meddelandet
news:4viem0dkbh4apfl41f6btmtufl6pq8mio0@.4ax.com...
> Yes, it's a problem, unfortunately.
> I have everything working again after adding a new CodeGroup to both
> policy config files, see:
> http://odetocode.com/Blogs/scott/archive/2004/10/08/538.aspx
> Let me know if this gets you up and running again. If anyone from MS
> has an official recommendation I'll update the blog.
> --
> Scott
> http://www.OdeToCode.com/
> On Fri, 8 Oct 2004 19:57:15 -0400, "Dan Plaskon"
> <dplaskon@.sympatico.ca> wrote:
>>Brian,
>>I had the exact same problem when I installed - let me know if you get a
>>resolution on this.
>>Thanks,
>>Dan
>>"Brian Almond" <pythonista@.sbcglobal.net> wrote in message
>>news:u8eYViVrEHA.3172@.TK2MSFTNGP10.phx.gbl...
>> Scott Allen wrote:
>> > There is now an MSI file that will install an HttpModule to protect
>> > all ASP.NET applications.
>> >
>> > See: http://www.microsoft.com/security/incident/aspnet.mspx
>> Thanks for posting the link Scott. Unfortunately it looks like
>> something that MSI does has confused Report Manager on my our
>> development RS box so that now I'm getting a security exception when
>> browsing to it. Playing with it now to see if I just need to make
>> simple config. changes or if it's something more involved causing me
>> trouble.
>> -BA
>|||If you restart the web server after making the configuration changes
it should all be working then.
--
Scott
http://www.OdeToCode.com/
On Mon, 11 Oct 2004 12:11:14 +0200, "Per Salmi"
<per.salmi@.nospam.nospam> wrote:
>Tried the same thing on another server and now the parse error on
>machine.config says:
>Description: An error occurred during the processing of a configuration file
>required to service this request. Please review the specific error details
>below and modify your configuration file appropriately.
>Parser Error Message: Assembly microsoft.web.validatepathmodule.dll security
>permission grant set is incompatible between appdomains.
>Source Error:
>Line 320: <add name="FileAuthorization"
>type="System.Web.Security.FileAuthorizationModule"/>
>Line 321: <add name="ErrorHandlerModule"
>type="System.Web.Mobile.ErrorHandlerModule, System.Web.Mobile,
>Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
>Line 322: <add name="ValidatePathModule"
>type="Microsoft.Web.ValidatePathModule, Microsoft.Web.ValidatePathModule,
>Version=1.0.0.0, Culture=neutral,
>PublicKeyToken=eba19824f86fdadd"/></httpModules>
>Line 323: <!--
>Line 324: processModel Attributes:
>
>/Per Salmi
>
>"Scott Allen" <bitmask@.[nospam].fred.net> skrev i meddelandet
>news:4viem0dkbh4apfl41f6btmtufl6pq8mio0@.4ax.com...
>> Yes, it's a problem, unfortunately.
>> I have everything working again after adding a new CodeGroup to both
>> policy config files, see:
>> http://odetocode.com/Blogs/scott/archive/2004/10/08/538.aspx
>> Let me know if this gets you up and running again. If anyone from MS
>> has an official recommendation I'll update the blog.
>> --
>> Scott
>> http://www.OdeToCode.com/
>> On Fri, 8 Oct 2004 19:57:15 -0400, "Dan Plaskon"
>> <dplaskon@.sympatico.ca> wrote:
>>Brian,
>>I had the exact same problem when I installed - let me know if you get a
>>resolution on this.
>>Thanks,
>>Dan
>>"Brian Almond" <pythonista@.sbcglobal.net> wrote in message
>>news:u8eYViVrEHA.3172@.TK2MSFTNGP10.phx.gbl...
>> Scott Allen wrote:
>> > There is now an MSI file that will install an HttpModule to protect
>> > all ASP.NET applications.
>> >
>> > See: http://www.microsoft.com/security/incident/aspnet.mspx
>> Thanks for posting the link Scott. Unfortunately it looks like
>> something that MSI does has confused Report Manager on my our
>> development RS box so that now I'm getting a security exception when
>> browsing to it. Playing with it now to see if I just need to make
>> simple config. changes or if it's something more involved causing me
>> trouble.
>> -BA
>>
>|||I called Microsoft support services, they were clueless. I'm blogging
about it at http://www.dogcaught.com/dpack/index.php?p=52
Aaron
http://www.hockley.org
"Per Salmi" <per.salmi@.nospam.nospam> wrote in message news:<ebdglq3rEHA.192@.tk2msftngp13.phx.gbl>...
> Tried the same thing on another server and now the parse error on
> machine.config says:
> Description: An error occurred during the processing of a configuration file
> required to service this request. Please review the specific error details
> below and modify your configuration file appropriately.
> Parser Error Message: Assembly microsoft.web.validatepathmodule.dll security
> permission grant set is incompatible between appdomains.
> Source Error:
> Line 320: <add name="FileAuthorization"
> type="System.Web.Security.FileAuthorizationModule"/>
> Line 321: <add name="ErrorHandlerModule"
> type="System.Web.Mobile.ErrorHandlerModule, System.Web.Mobile,
> Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
> Line 322: <add name="ValidatePathModule"
> type="Microsoft.Web.ValidatePathModule, Microsoft.Web.ValidatePathModule,
> Version=1.0.0.0, Culture=neutral,
> PublicKeyToken=eba19824f86fdadd"/></httpModules>
> Line 323: <!--
> Line 324: processModel Attributes:
>
> /Per Salmi
>|||Scott Allen wrote:
> I have everything working again after adding a new CodeGroup to both
> policy config files, see:
> http://odetocode.com/Blogs/scott/archive/2004/10/08/538.aspx
> Let me know if this gets you up and running again. If anyone from MS
> has an official recommendation I'll update the blog.
I have to admit that I'm left wondering why Microsoft released the patch
without getting a green light on their web apps. Coincidentally I
have tried your fix for their patch, but am now getting an error message
complaining about a request for StrongNameIdentityPermission. I guess
I'm going to have to bear down and study materials on configuration
ASP.NET applications if I want to get any semblance of a grip on this.
-BA|||Brian Almond wrote:
> configuration ASP.NET applications
_Configuring_ ASP.NET applications. Bah! I should really review my
messages prior to posting ;)|||Just an FYI for everyone. This doesn't help solve the issue with a system
being messed up but I don't think that RS is vulnerable to this exploit. I
have had some discussions with MS people and the bottom line is that Report
Server stores all of its secure content in the database. Report Manager is
the portal to Report Server and although it could be affected that there
would not be an exploit because it does not have secure content.
Further explanation given to me is that this exploit is seen when using form
authentication and you use the result of authentication to grant
permissions to files inside your application vroot which Report Manager does
not do.
If you think about how Report Server is designed as a service and if you
look on the server you will not see any rdl files. SQL Server db is used to
store all this information. So it is not like Report Manager is just opening
up report files.
I hesitated to jump in but I hate to see people messing around and wasting
time and energy on a patch that isn't needed. Please note that I am not the
official MS voice. At a minimum I would delay working on it. Hopefully we
can get an official MS person to bless what I said above.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Brian Almond" <pythonista@.sbcglobal.net> wrote in message
news:%23oJu899rEHA.2684@.TK2MSFTNGP12.phx.gbl...
> Scott Allen wrote:
> > I have everything working again after adding a new CodeGroup to both
> > policy config files, see:
> > http://odetocode.com/Blogs/scott/archive/2004/10/08/538.aspx
> >
> > Let me know if this gets you up and running again. If anyone from MS
> > has an official recommendation I'll update the blog.
> I have to admit that I'm left wondering why Microsoft released the patch
> without getting a green light on their web apps. Coincidentally I
> have tried your fix for their patch, but am now getting an error message
> complaining about a request for StrongNameIdentityPermission. I guess
> I'm going to have to bear down and study materials on configuration
> ASP.NET applications if I want to get any semblance of a grip on this.
> -BA|||Bruce,
Thanks for commenting on this. I hope we do get an official word at
some point on this issue. It would be nice not to have to worry about
this issue in the future.
Unfortunately, at this point I have a 'dead' report server. Luckily
it's my test server, but I would like to get it back up without
reinstalling RS if possible. (Uninstalling the MS patch doesn't seem to
revert all of its changes.) I didn't backup all of the configuration
files before applying the Microsoft patch, so I've given myself a more
difficult restore situation than I could have had otherwise.
It is certainly time better spent elsewhere.
-BA|||Hi Bruce:
I'm inclined to agree with you after some experimenting today.
In the case where someone *has* to install the module on a machine
with SSRS (because there are other ASP.NET applications present), the
fix is to put the following entry in the web.config file (both
ReportManager and ReportServer config files) in the system.web
section:
<httpModules>
<remove name="ValidatePathModule"/>
</httpModules>
This disables the module for just the SSRS applications.
There sure has been some confusion. I've seen a couple reputable
sources say the vulnerability exists for Windows authentication in
addition to forms authentication. I've also heard that Windows 2003 is
affected, even though I haven't been able to exploit the vulnerability
on any of my 2003 machines.
--
Scott
http://www.OdeToCode.com/
On Mon, 11 Oct 2004 17:33:19 -0500, "Bruce L-C [MVP]"
<bruce_lcNOSPAM@.hotmail.com> wrote:
>Just an FYI for everyone. This doesn't help solve the issue with a system
>being messed up but I don't think that RS is vulnerable to this exploit. I
>have had some discussions with MS people and the bottom line is that Report
>Server stores all of its secure content in the database. Report Manager is
>the portal to Report Server and although it could be affected that there
>would not be an exploit because it does not have secure content.
>Further explanation given to me is that this exploit is seen when using form
>authentication and you use the result of authentication to grant
>permissions to files inside your application vroot which Report Manager does
>not do.
>If you think about how Report Server is designed as a service and if you
>look on the server you will not see any rdl files. SQL Server db is used to
>store all this information. So it is not like Report Manager is just opening
>up report files.
>I hesitated to jump in but I hate to see people messing around and wasting
>time and energy on a patch that isn't needed. Please note that I am not the
>official MS voice. At a minimum I would delay working on it. Hopefully we
>can get an official MS person to bless what I said above.|||We are working on a KB article that will have the offical workaround for
this. We hope to have it posted tomorrow.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Scott Allen" <bitmask@.[nospam].fred.net> wrote in message
news:kobmm0hepkp6gh7voej1d882b56mpbt1en@.4ax.com...
> Hi Bruce:
> I'm inclined to agree with you after some experimenting today.
> In the case where someone *has* to install the module on a machine
> with SSRS (because there are other ASP.NET applications present), the
> fix is to put the following entry in the web.config file (both
> ReportManager and ReportServer config files) in the system.web
> section:
> <httpModules>
> <remove name="ValidatePathModule"/>
> </httpModules>
> This disables the module for just the SSRS applications.
> There sure has been some confusion. I've seen a couple reputable
> sources say the vulnerability exists for Windows authentication in
> addition to forms authentication. I've also heard that Windows 2003 is
> affected, even though I haven't been able to exploit the vulnerability
> on any of my 2003 machines.
> --
> Scott
> http://www.OdeToCode.com/
> On Mon, 11 Oct 2004 17:33:19 -0500, "Bruce L-C [MVP]"
> <bruce_lcNOSPAM@.hotmail.com> wrote:
>>Just an FYI for everyone. This doesn't help solve the issue with a system
>>being messed up but I don't think that RS is vulnerable to this exploit. I
>>have had some discussions with MS people and the bottom line is that
>>Report
>>Server stores all of its secure content in the database. Report Manager is
>>the portal to Report Server and although it could be affected that there
>>would not be an exploit because it does not have secure content.
>>Further explanation given to me is that this exploit is seen when using
>>form
>>authentication and you use the result of authentication to grant
>>permissions to files inside your application vroot which Report Manager
>>does
>>not do.
>>If you think about how Report Server is designed as a service and if you
>>look on the server you will not see any rdl files. SQL Server db is used
>>to
>>store all this information. So it is not like Report Manager is just
>>opening
>>up report files.
>>I hesitated to jump in but I hate to see people messing around and wasting
>>time and energy on a patch that isn't needed. Please note that I am not
>>the
>>official MS voice. At a minimum I would delay working on it. Hopefully we
>>can get an official MS person to bless what I said above.
>|||You are right in this that the RS might not be affected by the vulnerability
but as there might be lots of other asp.net applications running on the same
server that are vulnerable it would feel better to have the patch installed,
and still have a working report server application.
Best regards,
Per Salmi
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> skrev i meddelandet
news:ODmNQJ%23rEHA.2096@.TK2MSFTNGP11.phx.gbl...
> I hesitated to jump in but I hate to see people messing around and wasting
> time and energy on a patch that isn't needed. Please note that I am not
> the
> official MS voice. At a minimum I would delay working on it. Hopefully we
> can get an official MS person to bless what I said above.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services|||There is now a KB article that describes the workaround at
http://support.microsoft.com/?kbid=887787.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Per Salmi" <per.salmi@.nospam.nospam> wrote in message
news:uBzQx2BsEHA.3748@.TK2MSFTNGP09.phx.gbl...
> You are right in this that the RS might not be affected by the
> vulnerability but as there might be lots of other asp.net applications
> running on the same server that are vulnerable it would feel better to
> have the patch installed, and still have a working report server
> application.
> Best regards,
> Per Salmi
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> skrev i meddelandet
> news:ODmNQJ%23rEHA.2096@.TK2MSFTNGP11.phx.gbl...
>> I hesitated to jump in but I hate to see people messing around and
>> wasting
>> time and energy on a patch that isn't needed. Please note that I am not
>> the
>> official MS voice. At a minimum I would delay working on it. Hopefully we
>> can get an official MS person to bless what I said above.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>|||Thanks, Brian.
--
Scott
http://www.OdeToCode.com/
On Tue, 12 Oct 2004 16:09:32 -0700, "Brian Welcker [MSFT]"
<bwelcker@.online.microsoft.com> wrote:
>There is now a KB article that describes the workaround at
>http://support.microsoft.com/?kbid=887787.|||Thanks! That worked perfectly on both of our servers.
/Per Salmi
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> skrev i meddelandet
news:eXlZICLsEHA.2560@.tk2msftngp13.phx.gbl...
> There is now a KB article that describes the workaround at
> http://support.microsoft.com/?kbid=887787.
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server Reporting Services

Question on SSIS and .NET 1.1

We are migrating our databases from SQL Server 2000 on Win2k to SQL Server 2005 on Win 2k3 machines. Our applications useDTS packages , like creating dynamically in the (application) front end dynamically and sometimes just passing global parameters.

Now with SQL Server 2005 , we have decided to move to SSIS, as SQL Server 2005 has limited support for DTS. Now we were using a com+ assembly from microsoft for using these DTS packages from the front-end(Application). Can we accomplish the same with SSIS using applications developed in .Net Framework 1.1? Are there any work arounds? If not how can we accomplish this in 1.1 framework. Does SSIS expose any API that can be leveraged using .NET 1.1?

Did anyone face any other problems apart from these trying to make 1.1 apps talk to SQL 2005?

I guess this is more of a question to Microsoft experts, or programmers who already had similar experience already migrating to 2005.

It is hard to find information online on this subject. If anyone has valuable information regarding this, please reply with links..

Thanks in advance,

S

My understanding is that a .NET 1.1 application cannot interoperate directly with .NET 2.0 libraries such as the SSIS API. A web service should fit the bill nicely, though as far as just executing packages and passing in variables. If you're actually generating packages, that logic would probably need to be moved completely into the service which would also help insulate your application against future SSIS API changes. Your .NET 1.1 app could talk to the .NET 2.0 service, which could in turn automate the SSIS APIs.

In general, a .NET 1.1 application should not have much trouble making the switch from SQL Server 2000 to 2005, especially if you're using managed providers and calling stored procedures. If the app is calling the database with direct SQL queries, then you may have some minor syntax issues. If you're using procedures the Upgrade Advisor should be able to notify you of any problems, otherwise you'll have to test thoroughly.|||

In addition to nice Jay's wrap up, some mode ideas:

http://blogs.msdn.com/michen/archive/2007/03/22/running-ssis-package-programmatically.aspx

|||Thanks for all the replies..

Question on security

Hi,
I have a feeling what I want to do is not possible. I have a reportserver
(everything works fine) and I have an asp.net app which allows users to run
some reports. I dont use the reportviewer control, I just opent the report in
a new IE window by using url access e.g.
http://reportserver/reports/TimeReport&rs:command=render.
This works fine but is there a way of preventing users just typing in
http://reportserver/reports into the url and therefore accessing all the
reports. I know I could use windows authentication for the reports but I cant
do that for various reasons.
Is there any setting I can set on the http://reportserver/reports site to
only allow certain users access it but yet allow url access to reports as
normal using e.g. http://reportserver/reports/TimeReport&rs:command=render.
Its a bit of a contradiction really, I want the reportsite to be secure but
yet be able to run url access reports from it.
Thanks
NYou could deny access to the root from the report manager. Just allow
access to the subfolders/individual reports. Of course, they could then
directly access the subfolder...
Mike G.
"NH" <NH@.discussions.microsoft.com> wrote in message
news:846A037D-582D-498E-A6A1-5BF59AA772AF@.microsoft.com...
> Hi,
> I have a feeling what I want to do is not possible. I have a reportserver
> (everything works fine) and I have an asp.net app which allows users to
> run
> some reports. I dont use the reportviewer control, I just opent the report
> in
> a new IE window by using url access e.g.
> http://reportserver/reports/TimeReport&rs:command=render.
> This works fine but is there a way of preventing users just typing in
> http://reportserver/reports into the url and therefore accessing all the
> reports. I know I could use windows authentication for the reports but I
> cant
> do that for various reasons.
> Is there any setting I can set on the http://reportserver/reports site to
> only allow certain users access it but yet allow url access to reports as
> normal using e.g.
> http://reportserver/reports/TimeReport&rs:command=render.
> Its a bit of a contradiction really, I want the reportsite to be secure
> but
> yet be able to run url access reports from it.
> Thanks
> N
>|||Thanks Mike G, I dont think there is a way to do this.
"Mike G." wrote:
> You could deny access to the root from the report manager. Just allow
> access to the subfolders/individual reports. Of course, they could then
> directly access the subfolder...
> Mike G.
>
> "NH" <NH@.discussions.microsoft.com> wrote in message
> news:846A037D-582D-498E-A6A1-5BF59AA772AF@.microsoft.com...
> > Hi,
> >
> > I have a feeling what I want to do is not possible. I have a reportserver
> > (everything works fine) and I have an asp.net app which allows users to
> > run
> > some reports. I dont use the reportviewer control, I just opent the report
> > in
> > a new IE window by using url access e.g.
> > http://reportserver/reports/TimeReport&rs:command=render.
> >
> > This works fine but is there a way of preventing users just typing in
> > http://reportserver/reports into the url and therefore accessing all the
> > reports. I know I could use windows authentication for the reports but I
> > cant
> > do that for various reasons.
> >
> > Is there any setting I can set on the http://reportserver/reports site to
> > only allow certain users access it but yet allow url access to reports as
> > normal using e.g.
> > http://reportserver/reports/TimeReport&rs:command=render.
> > Its a bit of a contradiction really, I want the reportsite to be secure
> > but
> > yet be able to run url access reports from it.
> >
> > Thanks
> > N
> >
> >
>
>

Question on restoring encrypted databases on different servers

I have run into problems trying to use backed-up encrypted databases. I have an ASP.Net/SqlServer 2005 website for which I have production, test, and development environments, each on its own box. I have set up encryption using Master Keys, Certificates, and Symmetric Keys using the following commands:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'

CREATE CERTIFICATE CERTIFICATE_NAME WITH SUBJECT = 'My Website'

CREATE SYMMETRIC KEY KEY_NAME WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE CERTIFICATE_NAME.

All databases have the same certificate and symmetric key names. The Production database has a different password than the development and test databases.

I am running into problems when I back up the development database and try to move it to the test environment. When I do the move, and then try to access the encrypted data, I get an error "An error occurred during decryption", even though they are the "same" database (I originally detached and copied the development database to the test box when I set up the test environment) with the same password.

I then try the following commands, which I used when I brought the development database over to the test box the first time:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'password'

This caused the following error:

"An error occurred while decrypting certificate 'CERTIFICATE_NAME' that was encrypted by the old master key. The FORCE option can be used to ignore this error and continue the operation, but data that cannot be decrypted by the old master key will become unavailable."

So, how do you set up encrypted databases so that you can backup from one database server and restore on another? This problem will be very important when I set up a backup production database server.

From the error message and your description, I am guessing that somehow while moving the DB from one environment to another the metadata for either the master key (DBMK) or the certificate got somehow corrupted, or rather out of sync.

I am assuming that on the test environment the DB and an earlier copy of both the cert and DBMK (or only the DBMK) exist; based on that, this is my guess. On the dev environment you have DBMK_1 protecting cert_pvk_1, but when the DB is moved to the test environment, one of the following situations happened:

1) DBMK_1 does not replaced DBMK_0, and cert_pvk_1 protected by DBMK_1 (which is not present)

2) cert_pvk_1 metadata is not updated, and we have cert_pvk_1 protected by DBMK_0, but DBMK_1 replaced DBMK_0

Can you please verify if my assumption is correct? If so, I can try to reproduce the scenario in my environment to verify. If my assumption is not correct we will need more information. What are the OS versions running in both environments? Is there any significant difference between both environments (i.e. OS language, any third party software that may affect the crypto-API calls, etc)?

Can you also please send me a copy of the backup/restore commands you used in your scenario? I can use that information as well to try to reproduce this problem.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Also, the commands that you need to execute when moving a database to another server are:

OPEN MASTE KEY DECRYPTION BY PASSWORD = 'password'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

The REGENERATE option should be used only if you want to have a new master key in the database.

Thanks
Laurentiu

|||

This solves my problem. Thank you for your help.

However, it has opened up a new line of thought for me. If you don't use the ALTER MASTER KEY command in the backup database, you have to open the master key every time you want to access the encrypted data. I think I would like that requirement in the original database(it always bothered me that if you can decrypt at will if you know the name of the symmetric key and the certificate).

So, how do you make the original database require the Open Master Key Statement?

|||

You can use ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY.

Thanks
Laurentiu

|||

You can use the following statement:

ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY

Just to verify it:

-- Should fail with error 15581:

-- Please create a master key in the database or open the master key in the session before performing this operation.

create certificate foo with subject = 'foo'

go

OPEN MASTER KEY DECRYPTION BY PASSWORD = '<<Your DBMK p@.ssw0rD>>'

go

-- Will succeed

create certificate foo with subject = 'foo'

go

CLOSE MASTER KEY

Go

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi,

I am also trying to restore a database which has an encrypted column. I used methods from Larentiu's blog post - column encryption demo.

I execute this command and get:

OPEN MASTER KEY DECRYPTION BY PASSWORD = '<PWD>'

Msg 15313, Level 16, State 1, Line 1

The key is not encrypted using the specified decryptor.

I try this on my original database and I get the same result - what have i done?! I'm guessing my password is wrong, but i do have the script I used to create it right in front of me, and written: and they match.

|||

Hi Sam,

I was browsing for answer on my problem which happen to be your previous problem on open master key command. I get the same error when opening the master key. How did you resolved this?

thanks...

|||

I just answered a similar problem in the following thread of this forum:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1848415&SiteID=1

Hopefully this information will help, but if you still have any problems let us know.

-Raul Garcia

SDE/T

SQL Server Engine

Question on restoring encrypted databases on different servers

I have run into problems trying to use backed-up encrypted databases. I have an ASP.Net/SqlServer 2005 website for which I have production, test, and development environments, each on its own box. I have set up encryption using Master Keys, Certificates, and Symmetric Keys using the following commands:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'

CREATE CERTIFICATE CERTIFICATE_NAME WITH SUBJECT = 'My Website'

CREATE SYMMETRIC KEY KEY_NAME WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE CERTIFICATE_NAME.

All databases have the same certificate and symmetric key names. The Production database has a different password than the development and test databases.

I am running into problems when I back up the development database and try to move it to the test environment. When I do the move, and then try to access the encrypted data, I get an error "An error occurred during decryption", even though they are the "same" database (I originally detached and copied the development database to the test box when I set up the test environment) with the same password.

I then try the following commands, which I used when I brought the development database over to the test box the first time:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'password'

This caused the following error:

"An error occurred while decrypting certificate 'CERTIFICATE_NAME' that was encrypted by the old master key. The FORCE option can be used to ignore this error and continue the operation, but data that cannot be decrypted by the old master key will become unavailable."

So, how do you set up encrypted databases so that you can backup from one database server and restore on another? This problem will be very important when I set up a backup production database server.

From the error message and your description, I am guessing that somehow while moving the DB from one environment to another the metadata for either the master key (DBMK) or the certificate got somehow corrupted, or rather out of sync.

I am assuming that on the test environment the DB and an earlier copy of both the cert and DBMK (or only the DBMK) exist; based on that, this is my guess. On the dev environment you have DBMK_1 protecting cert_pvk_1, but when the DB is moved to the test environment, one of the following situations happened:

1) DBMK_1 does not replaced DBMK_0, and cert_pvk_1 protected by DBMK_1 (which is not present)

2) cert_pvk_1 metadata is not updated, and we have cert_pvk_1 protected by DBMK_0, but DBMK_1 replaced DBMK_0

Can you please verify if my assumption is correct? If so, I can try to reproduce the scenario in my environment to verify. If my assumption is not correct we will need more information. What are the OS versions running in both environments? Is there any significant difference between both environments (i.e. OS language, any third party software that may affect the crypto-API calls, etc)?

Can you also please send me a copy of the backup/restore commands you used in your scenario? I can use that information as well to try to reproduce this problem.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Also, the commands that you need to execute when moving a database to another server are:

OPEN MASTE KEY DECRYPTION BY PASSWORD = 'password'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

The REGENERATE option should be used only if you want to have a new master key in the database.

Thanks
Laurentiu

|||

This solves my problem. Thank you for your help.

However, it has opened up a new line of thought for me. If you don't use the ALTER MASTER KEY command in the backup database, you have to open the master key every time you want to access the encrypted data. I think I would like that requirement in the original database(it always bothered me that if you can decrypt at will if you know the name of the symmetric key and the certificate).

So, how do you make the original database require the Open Master Key Statement?

|||

You can use ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY.

Thanks
Laurentiu

|||

You can use the following statement:

ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY

Just to verify it:

-- Should fail with error 15581:

-- Please create a master key in the database or open the master key in the session before performing this operation.

create certificate foo with subject = 'foo'

go

OPEN MASTER KEY DECRYPTION BY PASSWORD = '<<Your DBMK p@.ssw0rD>>'

go

-- Will succeed

create certificate foo with subject = 'foo'

go

CLOSE MASTER KEY

Go

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi,

I am also trying to restore a database which has an encrypted column. I used methods from Larentiu's blog post - column encryption demo.

I execute this command and get:

OPEN MASTER KEY DECRYPTION BY PASSWORD = '<PWD>'

Msg 15313, Level 16, State 1, Line 1

The key is not encrypted using the specified decryptor.

I try this on my original database and I get the same result - what have i done?! I'm guessing my password is wrong, but i do have the script I used to create it right in front of me, and written: and they match.

|||

Hi Sam,

I was browsing for answer on my problem which happen to be your previous problem on open master key command. I get the same error when opening the master key. How did you resolved this?

thanks...

|||

I just answered a similar problem in the following thread of this forum:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1848415&SiteID=1

Hopefully this information will help, but if you still have any problems let us know.

-Raul Garcia

SDE/T

SQL Server Engine

Question on restoring encrypted databases on different servers

I have run into problems trying to use backed-up encrypted databases. I have an ASP.Net/SqlServer 2005 website for which I have production, test, and development environments, each on its own box. I have set up encryption using Master Keys, Certificates, and Symmetric Keys using the following commands:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'

CREATE CERTIFICATE CERTIFICATE_NAME WITH SUBJECT = 'My Website'

CREATE SYMMETRIC KEY KEY_NAME WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE CERTIFICATE_NAME.

All databases have the same certificate and symmetric key names. The Production database has a different password than the development and test databases.

I am running into problems when I back up the development database and try to move it to the test environment. When I do the move, and then try to access the encrypted data, I get an error "An error occurred during decryption", even though they are the "same" database (I originally detached and copied the development database to the test box when I set up the test environment) with the same password.

I then try the following commands, which I used when I brought the development database over to the test box the first time:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'password'

This caused the following error:

"An error occurred while decrypting certificate 'CERTIFICATE_NAME' that was encrypted by the old master key. The FORCE option can be used to ignore this error and continue the operation, but data that cannot be decrypted by the old master key will become unavailable."

So, how do you set up encrypted databases so that you can backup from one database server and restore on another? This problem will be very important when I set up a backup production database server.

From the error message and your description, I am guessing that somehow while moving the DB from one environment to another the metadata for either the master key (DBMK) or the certificate got somehow corrupted, or rather out of sync.

I am assuming that on the test environment the DB and an earlier copy of both the cert and DBMK (or only the DBMK) exist; based on that, this is my guess. On the dev environment you have DBMK_1 protecting cert_pvk_1, but when the DB is moved to the test environment, one of the following situations happened:

1) DBMK_1 does not replaced DBMK_0, and cert_pvk_1 protected by DBMK_1 (which is not present)

2) cert_pvk_1 metadata is not updated, and we have cert_pvk_1 protected by DBMK_0, but DBMK_1 replaced DBMK_0

Can you please verify if my assumption is correct? If so, I can try to reproduce the scenario in my environment to verify. If my assumption is not correct we will need more information. What are the OS versions running in both environments? Is there any significant difference between both environments (i.e. OS language, any third party software that may affect the crypto-API calls, etc)?

Can you also please send me a copy of the backup/restore commands you used in your scenario? I can use that information as well to try to reproduce this problem.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Also, the commands that you need to execute when moving a database to another server are:

OPEN MASTE KEY DECRYPTION BY PASSWORD = 'password'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

The REGENERATE option should be used only if you want to have a new master key in the database.

Thanks
Laurentiu

|||

This solves my problem. Thank you for your help.

However, it has opened up a new line of thought for me. If you don't use the ALTER MASTER KEY command in the backup database, you have to open the master key every time you want to access the encrypted data. I think I would like that requirement in the original database(it always bothered me that if you can decrypt at will if you know the name of the symmetric key and the certificate).

So, how do you make the original database require the Open Master Key Statement?

|||

You can use ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY.

Thanks
Laurentiu

|||

You can use the following statement:

ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY

Just to verify it:

-- Should fail with error 15581:

-- Please create a master key in the database or open the master key in the session before performing this operation.

create certificate foo with subject = 'foo'

go

OPEN MASTER KEY DECRYPTION BY PASSWORD = '<<Your DBMK p@.ssw0rD>>'

go

-- Will succeed

create certificate foo with subject = 'foo'

go

CLOSE MASTER KEY

Go

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi,

I am also trying to restore a database which has an encrypted column. I used methods from Larentiu's blog post - column encryption demo.

I execute this command and get:

OPEN MASTER KEY DECRYPTION BY PASSWORD = '<PWD>'

Msg 15313, Level 16, State 1, Line 1

The key is not encrypted using the specified decryptor.

I try this on my original database and I get the same result - what have i done?! I'm guessing my password is wrong, but i do have the script I used to create it right in front of me, and written: and they match.

|||

Hi Sam,

I was browsing for answer on my problem which happen to be your previous problem on open master key command. I get the same error when opening the master key. How did you resolved this?

thanks...

|||

I just answered a similar problem in the following thread of this forum:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1848415&SiteID=1

Hopefully this information will help, but if you still have any problems let us know.

-Raul Garcia

SDE/T

SQL Server Engine

Question on restoring encrypted databases on different servers

I have run into problems trying to use backed-up encrypted databases. I have an ASP.Net/SqlServer 2005 website for which I have production, test, and development environments, each on its own box. I have set up encryption using Master Keys, Certificates, and Symmetric Keys using the following commands:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'

CREATE CERTIFICATE CERTIFICATE_NAME WITH SUBJECT = 'My Website'

CREATE SYMMETRIC KEY KEY_NAME WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE CERTIFICATE_NAME.

All databases have the same certificate and symmetric key names. The Production database has a different password than the development and test databases.

I am running into problems when I back up the development database and try to move it to the test environment. When I do the move, and then try to access the encrypted data, I get an error "An error occurred during decryption", even though they are the "same" database (I originally detached and copied the development database to the test box when I set up the test environment) with the same password.

I then try the following commands, which I used when I brought the development database over to the test box the first time:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'password'

This caused the following error:

"An error occurred while decrypting certificate 'CERTIFICATE_NAME' that was encrypted by the old master key. The FORCE option can be used to ignore this error and continue the operation, but data that cannot be decrypted by the old master key will become unavailable."

So, how do you set up encrypted databases so that you can backup from one database server and restore on another? This problem will be very important when I set up a backup production database server.

From the error message and your description, I am guessing that somehow while moving the DB from one environment to another the metadata for either the master key (DBMK) or the certificate got somehow corrupted, or rather out of sync.

I am assuming that on the test environment the DB and an earlier copy of both the cert and DBMK (or only the DBMK) exist; based on that, this is my guess. On the dev environment you have DBMK_1 protecting cert_pvk_1, but when the DB is moved to the test environment, one of the following situations happened:

1) DBMK_1 does not replaced DBMK_0, and cert_pvk_1 protected by DBMK_1 (which is not present)

2) cert_pvk_1 metadata is not updated, and we have cert_pvk_1 protected by DBMK_0, but DBMK_1 replaced DBMK_0

Can you please verify if my assumption is correct? If so, I can try to reproduce the scenario in my environment to verify. If my assumption is not correct we will need more information. What are the OS versions running in both environments? Is there any significant difference between both environments (i.e. OS language, any third party software that may affect the crypto-API calls, etc)?

Can you also please send me a copy of the backup/restore commands you used in your scenario? I can use that information as well to try to reproduce this problem.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Also, the commands that you need to execute when moving a database to another server are:

OPEN MASTE KEY DECRYPTION BY PASSWORD = 'password'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

The REGENERATE option should be used only if you want to have a new master key in the database.

Thanks
Laurentiu

|||

This solves my problem. Thank you for your help.

However, it has opened up a new line of thought for me. If you don't use the ALTER MASTER KEY command in the backup database, you have to open the master key every time you want to access the encrypted data. I think I would like that requirement in the original database(it always bothered me that if you can decrypt at will if you know the name of the symmetric key and the certificate).

So, how do you make the original database require the Open Master Key Statement?

|||

You can use ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY.

Thanks
Laurentiu

|||

You can use the following statement:

ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY

Just to verify it:

-- Should fail with error 15581:

-- Please create a master key in the database or open the master key in the session before performing this operation.

create certificate foo with subject = 'foo'

go

OPEN MASTER KEY DECRYPTION BY PASSWORD = '<<Your DBMK p@.ssw0rD>>'

go

-- Will succeed

create certificate foo with subject = 'foo'

go

CLOSE MASTER KEY

Go

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi,

I am also trying to restore a database which has an encrypted column. I used methods from Larentiu's blog post - column encryption demo.

I execute this command and get:

OPEN MASTER KEY DECRYPTION BY PASSWORD = '<PWD>'

Msg 15313, Level 16, State 1, Line 1

The key is not encrypted using the specified decryptor.

I try this on my original database and I get the same result - what have i done?! I'm guessing my password is wrong, but i do have the script I used to create it right in front of me, and written: and they match.

|||

Hi Sam,

I was browsing for answer on my problem which happen to be your previous problem on open master key command. I get the same error when opening the master key. How did you resolved this?

thanks...

|||

I just answered a similar problem in the following thread of this forum:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1848415&SiteID=1

Hopefully this information will help, but if you still have any problems let us know.

-Raul Garcia

SDE/T

SQL Server Engine

Question on passing multi-value parameter for multiple branches

Hello. We are using asp .net and reporting services, and trying to pass a multi-value parameter into reporting services that will show data for multiple branches.

Dim paramList As New Generic.List(Of Microsoft.Reporting.WebForms.ReportParameter)

paramList.Add(New Microsoft.Reporting.WebForms.ReportParameter("BranchNumber", 1))

ReportViewer1.ServerReport.SetParameters(paramList)

pInfo = ReportViewer1.ServerReport.GetParameters()

Let me know if you have any suggestions!

Thanks.

Did you try passing "1,2,3,4" instead of "1" ?

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||We tried that also, without any luck.|||

We have already figured out how to move between seperate branches, we just want a corporate option that will show all branches as a whole.

Any ideas?

Thanks

|||There is no way to post the "All" option, you will either have to pass all values to display or use an additional (hidden) parametert which uses the "All" option behind the scenes, something like:

Where SomeVar IN (@.TheValues) OR @.TheMagicParameter = 1 (Where TheMagicParameter is the magic hidden parameter)

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Question on correct syntax for stored procedure

I have an ASP.NET datagrid in which I am passing the following:
PartNumber (which is actually a varchar)
PartType (varchar)
The PartType is just the description. In the PartType table I have two field
s:
ptID (what I actually need to access)
Description (what is being passed to the stored procedure)
What I want to do is insert into the Parts table the PartNumber (no problem)
and the ptID.
When I call existing records I use the following SQL statement:
SELECT p.PartNumber, p.ID as ptID, pt.description FROM parts p INNER JOIN
PartTypes pt on pt.ID =p.PartTypeID WHERE p.PartNumber = @.PartID;
What I am trying to figure out is where in the stored procedure do I fit the
inner join? This is what I have thus far:
@.PartNumber varchar (15),
@.PartType varchar (100),
AS
if NOT EXISTS (Select * from Parts where PartNumber = @.PartNumber)
BEGIN
insert into Parts
(PartNumber, PartTypeID)
VALUES
(
@.PartNumber
@.PartType <--but I don't actually want the PartType, I want the PartTypeID
)
END
How do I do this? Let me know if you need more info. Thanks!Leckey,
You can fire the query you have mentioned just before the insert statement
which
you have given in your stored procedure. Query would be something like
SELECT p.PartNumber, @.PART_ID = p.ID as ptID, pt.description FROM parts p
INNER JOIN
> PartTypes pt on pt.ID =p.PartTypeID WHERE p.PartNumber = @.PartID;
@.PART_ID need to be declared within the SP as an integer type variable. Now
you can this
@.PART_ID in your insert statement as the second param.
Hope this helps.
Regards,
Joe.
"leckey" <u23706@.uwe> wrote in message news:62b346eec9d0a@.uwe...
> I have an ASP.NET datagrid in which I am passing the following:
> PartNumber (which is actually a varchar)
> PartType (varchar)
> The PartType is just the description. In the PartType table I have two
fields:
> ptID (what I actually need to access)
> Description (what is being passed to the stored procedure)
> What I want to do is insert into the Parts table the PartNumber (no
problem)
> and the ptID.
> When I call existing records I use the following SQL statement:
> SELECT p.PartNumber, p.ID as ptID, pt.description FROM parts p INNER JOIN
> PartTypes pt on pt.ID =p.PartTypeID WHERE p.PartNumber = @.PartID;
> What I am trying to figure out is where in the stored procedure do I fit
the
> inner join? This is what I have thus far:
> @.PartNumber varchar (15),
> @.PartType varchar (100),
> AS
> if NOT EXISTS (Select * from Parts where PartNumber = @.PartNumber)
> BEGIN
> insert into Parts
> (PartNumber, PartTypeID)
>
> VALUES
> (
> @.PartNumber
> @.PartType <--but I don't actually want the PartType, I want the PartTypeID
> )
> END
> How do I do this? Let me know if you need more info. Thanks!

Question on copying over overlapping data from one database to another...

... and preserving the relationships.
(Note, this is more of a SQL question than a SQL-related ASP.NET question...)
Say I have two databases, D1 and D2, with the same three tables:
Companies
Departments
Employees
With the standard one-to-many relationships down the line, with each
table having a PK, IDENTITY field like CompanyID, DepartmentID, and
EmployeeID.

I have a smattering of data in each of D1 and D2 for these tables with
overlaps in the ID field values. What I want to be able to do is copy
over D1's data to D2, preserving the relationships in D1 even though
there are ID overlaps in D2. So the tool I'd use would have to be
smart enough to check for ID dups in D2 and appropriate change the ID
values in D1's tables, maintaining the relationships.
Is there some built-in SQL tool to do this or do I have to do this myself?
Thanks!

There may be 3rd Party tools that do this but there's not any way, I'm aware of, of doing it in Enterprise Manager or Management Server Express. I do have an idea, though, of a way you could at least merge the tables using standard SQL:

For instance, if we take the Northwind database and imagine you have two Products tables in it, both with the exact same columns and both populated with data where the ProductID (PK) might be the same in both tables. We shall call these two tablesProducts andProducts2. Now, if you wanted to merge these two tables into one new table with a new, unqiue primary key you would do this:

Create a new table called (say) Products_Temp with the same structure as Products and make sure that ProductID is set as an Identity column. The easiest way is to create a script from Products table which would look like:

CREATETABLE [dbo].[Products](

[ProductID] [int]IDENTITY(1,1)NOTNULL,

[ProductName] [nvarchar](40)COLLATE Latin1_General_CI_ASNOTNULL,

[SupplierID] [int]NULL,

[CategoryID] [int]NULL,

[QuantityPerUnit] [nvarchar](20)COLLATE Latin1_General_CI_ASNULL,

[UnitPrice] [money]NULLCONSTRAINT [DF_Products_UnitPrice]DEFAULT((0)),

[UnitsInStock] [smallint]NULLCONSTRAINT [DF_Products_UnitsInStock]DEFAULT((0)),

[UnitsOnOrder] [smallint]NULLCONSTRAINT [DF_Products_UnitsOnOrder]DEFAULT((0)),

[ReorderLevel] [smallint]NULLCONSTRAINT [DF_Products_ReorderLevel]DEFAULT((0)),

[Discontinued] [bit]NOTNULLCONSTRAINT [DF_Products_Discontinued]DEFAULT((0)),

CONSTRAINT [PK_Products]PRIMARYKEYCLUSTERED

(

[ProductID]ASC

)WITH(IGNORE_DUP_KEY=OFF)ON [PRIMARY]

)ON [PRIMARY]

Then to merge the two tables you would UNION them together on all columnsexceptthe primary key column (ProductID) and place the results into the Products_Temp table you just created. Eg.

SELECT ProductName, SupplierID, CategoryID, QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued

INTO Product_TEMP

FROM

(SELECT ProductName, SupplierID, CategoryID, QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued

FROM Products

UNION

SELECT ProductName, SupplierID, CategoryID, QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued

FROM Products2)

AS Temp;

Now Products_Temp will contain the merged data from both tables (with any exact duplicate rows removed) and each row will now have a unique primary key (because of Identity insert). I know this isn't that much help because it doesn't preserve the PK > FK relationships between multiple tables, but it might give you some ideas or inspiration... Good luck :)

|||Scott,
If you have sql enterprise you can use there management studio and do this with a breeze. even if the database is local or remote. You can execute scripts on the databases, copy, backup, merge. basically everyhing. I hope this helps.
|||Connect, I was not aware of any built-in functionality in SQL Server 2000, and figured it would have to be scripted.
Does anyone know of a third-party tool that accomplishes this?

Blake05 wrote:

Scott,
If you have sql enterprise you can use there management studio and do this with a breeze. even if the database is local or remote. You can execute scripts on the databases, copy, backup, merge. basically everyhing. I hope this helps.

Blake05, how can I use EM to copy data from one DB to another when there are overlapping ID values between the two databases' tables?
Thanks
|||Yes you can
|||You don't need third party tool create Unique index with IGNORE_DUP_KEY option and do standard INSERT INTO and in SQL Server 2000 and below that statement will not affect update statements. What happens is SQL Server will not insert the duplicate values it will insert only the unique values. I am assuming this is what you want and run a search for the above in SQL Server BOL (books online). Hope this helps.