Default Non-queried report parameter not updated when project is deployed

Adding a value to a non-queried default report parameter value does not update on the target server after deployment.

To recreate

1. Create a report in Visual Studio and add a report parameter with the following properties:

Multi-value is checked Available values = "From Query" Dataset = [create a dataset that returns a table w/ a Id and Description column] Value field = [the Id field from the table] Label field=[the Description column from the table] Default values = "Non-queried" (add several values the match the IDs from the table so that some of the values in the report dropdown will show up as checked when rendering the report)

2. Build and deploy the report to the reporting server. View the report and verify the specified items are checked in the report parameter.

3. Go back to Visual Studio and add a value to the Non-queried Default values.

4. Build and deploy the report again. View the report. The newly added item is not selected.

Notes

I verified that the newly added ID exists in the rdl file (as xml) on both the development box and the server where the report was deployed. However, when I view the report parameter using Management Studio (connect to the reporting server), the newly added value for the report parameter does not exist. I verified that changes are being deployed by adding new parameters and changing other properties of the parameter. I thought maybe the rdl itself was being cached somehow - I tried restarting IIS, SQL Server, and SQL Reporting services. None worked. Note that running the report on the development box by running the project through Visual Studio DOES reflect the change to the parameter.

Work-arounds

1. Create a dataset for the report that returns a table of the Ids that you want pre-selected. The query could be something like this:

SELECT '4' AS SelectedId
UNION
SELECT '5' AS SelectedId
UNION
SELECT '6' AS SelectedId

2. Delete the report in Management Studio, then redeploy.

I have issue w/ both workarounds because for 1) it is not intuitive and you have to remember to do this for every similar case, and 2) this extra step has to occur each time the report is deployed w/ changes to the report parameter.

Hi there!

I recently discovered the same problem.
The option of deleting the report and do a redeploy is nothing for us, because of already made subscribtions that will also dissapear..

Is there a release comming to fix the problem?

Option 2 (using datasources) is not the most efficient one..
Any words from the developers?

//P?r

|||

My collegue and I are also having this problem. Non-queried defaults don't seem to work.

When will this be fixed?

|||

I have not reported this, other than posting here. Can anyone tell me the best place to submit this to support?

I tried going to http://support.microsoft.com/contactussupport/?ws=support and I received the following message:

"If you think you have found a bug in a Microsoft product, contact our Microsoft Product Support Services department.
(800) MICROSOFT (642-7676)."

I then tried to call the phone number and got a voice menu. After a couple of minutes of searching for the correct option, I gave up.

|||This is by design. The best way to update parameters on server is to delete existing report before deploying a new one.|||This is "by design"? That's not a logical response. If you read the entire thread you will see that deleting the report is not an option for many of us since all subscriptions will be lost. This is a serious bug that should be fixed ASAP.|||

Actually it really is By Design. I understand that you don't agree with the design. The reason for this design choice is that some things associated with the report depend on a certain parameter state, like subscriptions. Therefore, we consider the server's parameter state to be the "master". In order to change the parameter state, you need to do so on the server, either through Report Manager, Management Studio, or SharePoint integrated UI.

The alternative would be to allow republishing to reset the parameter state. In this case, any changes made to the parameters through a server call would be wiped out, since server changes don't cascade back to Report Designer. For example, Mary publishes the RDL with a display prompt of "Enter a parameter value." Some time later, Bob changes the display prompt (using Report Manager) to "Please enter the desired value." Some time after that, Mary republishes the report, this time with a display prompt of "Here is where you enter the user's department name." Which version of the display value is used? Sure, Mary and Bob shouldn't have made conflicting changes, but it happens, and the SW has to make a choice.

It is possible, of course, to enable a comingling of the server state and the project state, but that requires additional complexity and UI to allow conflict resolution. Such a conflict resolution feature is something we haven't implemented to-date. It's possible that this could be implemented in a future release.

A different workaround for the specific case of default values is to modify the default values in Report Manager, Management Studio, or SharePoint integrated UI. That doesn't require deleting the report, and allows you to retain the subscriptions and other bound entities.

Default Non-queried report parameter not updated when project is deployed

Adding a value to a non-queried default report parameter value does not update on the target server after deployment.

To recreate

1. Create a report in Visual Studio and add a report parameter with the following properties:

Multi-value is checked

Available values = "From Query"

Dataset = [create a dataset that returns a table w/ a Id and Description column]

Value field = [the Id field from the table]

Label field=[the Description column from the table]

Default values = "Non-queried" (add several values the match the IDs from the table so that some of the values in the report dropdown will show up as checked when rendering the report)

2. Build and deploy the report to the reporting server. View the report and verify the specified items are checked in the report parameter.

3. Go back to Visual Studio and add a value to the Non-queried Default values.

4. Build and deploy the report again. View the report. The newly added item is not selected.

Notes

I verified that the newly added ID exists in the rdl file (as xml) on both the development box and the server where the report was deployed. However, when I view the report parameter using Management Studio (connect to the reporting server), the newly added value for the report parameter does not exist. I verified that changes are being deployed by adding new parameters and changing other properties of the parameter. I thought maybe the rdl itself was being cached somehow - I tried restarting IIS, SQL Server, and SQL Reporting services. None worked. Note that running the report on the development box by running the project through Visual Studio DOES reflect the change to the parameter.

Work-arounds

1. Create a dataset for the report that returns a table of the Ids that you want pre-selected. The query could be something like this:

SELECT '4' AS SelectedId
UNION
SELECT '5' AS SelectedId
UNION
SELECT '6' AS SelectedId

2. Delete the report in Management Studio, then redeploy.

I have issue w/ both workarounds because for 1) it is not intuitive and you have to remember to do this for every similar case, and 2) this extra step has to occur each time the report is deployed w/ changes to the report parameter.

Hi there!

I recently discovered the same problem.
The option of deleting the report and do a redeploy is nothing for us, because of already made subscribtions that will also dissapear..

Is there a release comming to fix the problem?

Option 2 (using datasources) is not the most efficient one..
Any words from the developers?

//P?r

|||

My collegue and I are also having this problem. Non-queried defaults don't seem to work.

When will this be fixed?

|||

I have not reported this, other than posting here. Can anyone tell me the best place to submit this to support?

I tried going to http://support.microsoft.com/contactussupport/?ws=support and I received the following message:

"If you think you have found a bug in a Microsoft product, contact our Microsoft Product Support Services department.
(800) MICROSOFT (642-7676)."

I then tried to call the phone number and got a voice menu. After a couple of minutes of searching for the correct option, I gave up.

|||This is by design. The best way to update parameters on server is to delete existing report before deploying a new one.|||This is "by design"? That's not a logical response. If you read the entire thread you will see that deleting the report is not an option for many of us since all subscriptions will be lost. This is a serious bug that should be fixed ASAP.|||

Actually it really is By Design. I understand that you don't agree with the design. The reason for this design choice is that some things associated with the report depend on a certain parameter state, like subscriptions. Therefore, we consider the server's parameter state to be the "master". In order to change the parameter state, you need to do so on the server, either through Report Manager, Management Studio, or SharePoint integrated UI.

The alternative would be to allow republishing to reset the parameter state. In this case, any changes made to the parameters through a server call would be wiped out, since server changes don't cascade back to Report Designer. For example, Mary publishes the RDL with a display prompt of "Enter a parameter value." Some time later, Bob changes the display prompt (using Report Manager) to "Please enter the desired value." Some time after that, Mary republishes the report, this time with a display prompt of "Here is where you enter the user's department name." Which version of the display value is used? Sure, Mary and Bob shouldn't have made conflicting changes, but it happens, and the SW has to make a choice.

It is possible, of course, to enable a comingling of the server state and the project state, but that requires additional complexity and UI to allow conflict resolution. Such a conflict resolution feature is something we haven't implemented to-date. It's possible that this could be implemented in a future release.

A different workaround for the specific case of default values is to modify the default values in Report Manager, Management Studio, or SharePoint integrated UI. That doesn't require deleting the report, and allows you to retain the subscriptions and other bound entities.

Default installation

I have built an application that uses SQLExpress. When I build the setup with a new setup Project I include in the prerequisites SQLExpress so that if the target PC has no SQL server installed the setup sequence will automatically install an instance.

The Serup Sequence installes SQL Server with default Instance name and settings. I need to make the setup package to install the SQL instance with a specific name and some other setting. These settings would be

/qb ADDLOCAL=ALL SECURITYMODE=SQL SAPWD=pass INSTANCENAME=instname DISABLENETWORKPROTOCOLS=0

if i run it from Command Line.

I searched a lot on my own and didnt find any way of predefining these setting. Tnx in advance

The instance name is hard coded into the manifests used to generate the SQL Express prerequisite and there is no way to dynamically change it. You would need to go in and alter the command line used to install SQL Express that is encoded in the manifest.

You can find more about how the prerequisties in VS work by searching MSDN for "generic bootstrapper". I've also posted instruction on how to build your own prerequisite for SQL Express SP2 (VS only has RTM) that may be instructive. My sample also installs to the default named instance, but again, you can make any changes you want. Check out this blog post for more information.

Finally, you can choose to write your own install wrapper and take full control over the installation process. There is a white paper that discusses how to embed SQL Express installation in a custom wrapper here.

Mike

Default installation

I have built an application that uses SQLExpress. When I build the setup with a new setup Project I include in the prerequisites SQLExpress so that if the target PC has no SQL server installed the setup sequence will automatically install an instance.

The Serup Sequence installes SQL Server with default Instance name and settings. I need to make the setup package to install the SQL instance with a specific name and some other setting. These settings would be

/qb ADDLOCAL=ALL SECURITYMODE=SQL SAPWD=pass INSTANCENAME=instname DISABLENETWORKPROTOCOLS=0

if i run it from Command Line.

I searched a lot on my own and didnt find any way of predefining these setting. Tnx in advance

The instance name is hard coded into the manifests used to generate the SQL Express prerequisite and there is no way to dynamically change it. You would need to go in and alter the command line used to install SQL Express that is encoded in the manifest.

You can find more about how the prerequisties in VS work by searching MSDN for "generic bootstrapper". I've also posted instruction on how to build your own prerequisite for SQL Express SP2 (VS only has RTM) that may be instructive. My sample also installs to the default named instance, but again, you can make any changes you want. Check out this blog post for more information.

Finally, you can choose to write your own install wrapper and take full control over the installation process. There is a white paper that discusses how to embed SQL Express installation in a custom wrapper here.

Mike

sql

Default font properties

I know it is probably something that is staring me right in the face but...
how do you set the default font properties for a project (or all projects to
be created) so that for all objects you pull on to the report they default to
this font property? Is there a RS setting or a Visual Studio setting
somewhere that nominates the default font (I'm particularly after font family
& size defaults). The reason I am asking is that I don't want to have to
reset object by object/ group of objects every report I create...
...hoping someone can save me some time...
THX.in visual studio... tools... options... then under Environment look at "fonts
and colors"
i think this is what you are looking for?
"Bridget" wrote:
> I know it is probably something that is staring me right in the face but...
> how do you set the default font properties for a project (or all projects to
> be created) so that for all objects you pull on to the report they default to
> this font property? Is there a RS setting or a Visual Studio setting
> somewhere that nominates the default font (I'm particularly after font family
> & size defaults). The reason I am asking is that I don't want to have to
> reset object by object/ group of objects every report I create...
> ...hoping someone can save me some time...
> THX.|||Thanks Mike I was hoping that would be it - but I have tested it and have got
someone I work with to double check and changing these settings seems to just
change the environment as opposed to the defaults for the objects which is
what I am after... i.e. when I pull on a text box (or field) I want to be
able to preset the font size at which this initiates with...so I guess what
I am after is how to set the default properties for the various objects ...
any ideas..?
thx
"mike" wrote:
> in visual studio... tools... options... then under Environment look at "fonts
> and colors"
> i think this is what you are looking for?
> "Bridget" wrote:
> > I know it is probably something that is staring me right in the face but...
> > how do you set the default font properties for a project (or all projects to
> > be created) so that for all objects you pull on to the report they default to
> > this font property? Is there a RS setting or a Visual Studio setting
> > somewhere that nominates the default font (I'm particularly after font family
> > & size defaults). The reason I am asking is that I don't want to have to
> > reset object by object/ group of objects every report I create...
> >
> > ...hoping someone can save me some time...
> >
> > THX.sql

Default Folder for Deploying Projects

I have a real (hopefully) easy question...
I'm wanting to deploy a Reporting Services project to a particular folder on
my virtual server. For example, whenever you deploy a project it goes
straight to the home directory and creates the MODELS and DATA SOURCES
folders.
I would like to deploy my project to a folder I created called REPORTING
COMPONENTS.
Is this possible?
Thanks in advance for any help!!!Right mouse click, properties on the project. It brings up a property page
where you specify what you want it called on the server.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
news:1D246E5F-00EA-48CA-BC29-B6A661F0DD31@.microsoft.com...
>I have a real (hopefully) easy question...
> I'm wanting to deploy a Reporting Services project to a particular folder
> on
> my virtual server. For example, whenever you deploy a project it goes
> straight to the home directory and creates the MODELS and DATA SOURCES
> folders.
> I would like to deploy my project to a folder I created called REPORTING
> COMPONENTS.
> Is this possible?
> Thanks in advance for any help!!!|||I've tried that in the past and when I do I get the following error when
specifying any directory besides the default.
Here's where I want them to go:
http://10.90.160.13/ReportServerTest/Reporting Components:
Here's the error:
Client found response content type of '', but expected 'text/xml'.
The request failed with an empty response.
(Microsoft.ReportingServices.SemanticQueryDesign)
--
Program Location:
at
Microsoft.SqlServer.ReportingServices2005.RSConnection.GetSecureMethods()
at
Microsoft.SqlServer.ReportingServices2005.RSConnection.IsSecureMethod(String
methodname)
at
Microsoft.SqlServer.ReportingServices2005.RSConnection.GetItemType(String
Item)
at
Microsoft.ReportingServices.ModelDesigner.Project.ReportServiceClient.GetItemType(String item)
at
Microsoft.ReportingServices.ModelDesigner.Project.ReportServiceClient.CheckAuthorized()
at
Microsoft.ReportingServices.ModelDesigner.Project.ModelClientManager.GetCredentials(String url)
at
Microsoft.ReportingServices.ModelDesigner.Project.ModelProjectDeployer.PrepareDeploy()
"Bruce L-C [MVP]" wrote:
> Right mouse click, properties on the project. It brings up a property page
> where you specify what you want it called on the server.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
> news:1D246E5F-00EA-48CA-BC29-B6A661F0DD31@.microsoft.com...
> >I have a real (hopefully) easy question...
> >
> > I'm wanting to deploy a Reporting Services project to a particular folder
> > on
> > my virtual server. For example, whenever you deploy a project it goes
> > straight to the home directory and creates the MODELS and DATA SOURCES
> > folders.
> >
> > I would like to deploy my project to a folder I created called REPORTING
> > COMPONENTS.
> >
> > Is this possible?
> >
> > Thanks in advance for any help!!!
>
>|||Is ReportServerTest the name of you reportserver directory? Usually this
would look like http://10.90.160.13/ReportServer/Reporting Components
RS creates two websites: ReportServer and Reports. Reports is the portal.
ReportServer is the asp.net application. Did you not install in the default
area?
You can specify the final directory, you cannot specify anything but
ReportServer (or whatever RS website is called).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
news:50DD68B4-C241-4068-B176-BB1D302862B6@.microsoft.com...
> I've tried that in the past and when I do I get the following error when
> specifying any directory besides the default.
> Here's where I want them to go:
> http://10.90.160.13/ReportServerTest/Reporting Components:
> Here's the error:
> Client found response content type of '', but expected 'text/xml'.
> The request failed with an empty response.
> (Microsoft.ReportingServices.SemanticQueryDesign)
> --
> Program Location:
> at
> Microsoft.SqlServer.ReportingServices2005.RSConnection.GetSecureMethods()
> at
> Microsoft.SqlServer.ReportingServices2005.RSConnection.IsSecureMethod(String
> methodname)
> at
> Microsoft.SqlServer.ReportingServices2005.RSConnection.GetItemType(String
> Item)
> at
> Microsoft.ReportingServices.ModelDesigner.Project.ReportServiceClient.GetItemType(String
> item)
> at
> Microsoft.ReportingServices.ModelDesigner.Project.ReportServiceClient.CheckAuthorized()
> at
> Microsoft.ReportingServices.ModelDesigner.Project.ModelClientManager.GetCredentials(String
> url)
> at
> Microsoft.ReportingServices.ModelDesigner.Project.ModelProjectDeployer.PrepareDeploy()
>
> "Bruce L-C [MVP]" wrote:
>> Right mouse click, properties on the project. It brings up a property
>> page
>> where you specify what you want it called on the server.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
>> news:1D246E5F-00EA-48CA-BC29-B6A661F0DD31@.microsoft.com...
>> >I have a real (hopefully) easy question...
>> >
>> > I'm wanting to deploy a Reporting Services project to a particular
>> > folder
>> > on
>> > my virtual server. For example, whenever you deploy a project it goes
>> > straight to the home directory and creates the MODELS and DATA SOURCES
>> > folders.
>> >
>> > I would like to deploy my project to a folder I created called
>> > REPORTING
>> > COMPONENTS.
>> >
>> > Is this possible?
>> >
>> > Thanks in advance for any help!!!
>>|||I eventually figured it out... :-)
There are three properties: target folderr for models, data sources, and
URL. I was modifying the URL...no no no!!!
I modified the target folders and everything is peachy...
Thanks for your help Bruce...!
"Bruce L-C [MVP]" wrote:
> Right mouse click, properties on the project. It brings up a property page
> where you specify what you want it called on the server.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
> news:1D246E5F-00EA-48CA-BC29-B6A661F0DD31@.microsoft.com...
> >I have a real (hopefully) easy question...
> >
> > I'm wanting to deploy a Reporting Services project to a particular folder
> > on
> > my virtual server. For example, whenever you deploy a project it goes
> > straight to the home directory and creates the MODELS and DATA SOURCES
> > folders.
> >
> > I would like to deploy my project to a folder I created called REPORTING
> > COMPONENTS.
> >
> > Is this possible?
> >
> > Thanks in advance for any help!!!
>
>

Default Date Format used by Mssql

Hi there,

What is the default date format used by transact sql? Would YYYY-MM-DD HOUR:MIN:SECS format work on Mssql?

I am working on a project that needs to work with atleast two databases (Mysql/Mssql). I use the above date format and while it works perfectly on all Mysql databases, it gives me trouble in some Mssql setups.

Most of the trouble arises when I am doing INSERT or SELECT queries.

How do I handle this? Is there some way that I can tell Mssql that I am using the yyyy-mm-dd format or should I find out what format that particular mssql is using and adopt it?YYYY-MM-DD hh:mm:ss should work fine with mssql. that's basically the ODBC format.

See http://msdn2.microsoft.com/en-us/library/ms187928.aspx for a list of all the different date formats in sql server.|||I've never had the ISO standard temporal format (YYYY-MM-DD HH:MM:SS.TTT) give me trouble with Microsoft SQL, that is actually the preferred format for dates and times. The only thing I've had trouble with Microsoft-SQL handling DATETIME values in that time format was because MS-SQL can only resolve time down to 3 ms so it sees all three of the following times as identical:

2006-08-07 06:05:04.000
2006-08-07 06:05:04.001
2006-08-07 06:05:04.002

If you are using SMALLDATETIME values, things get more interesting quickly, since those are only accurate to the minute. That might be a whole different issue.

-PatP|||One of my clients use Mssql and her server uses the YYYY-DD-MM date format. I had to change the date formatting in the code to make it work for her.

I would really need to know whether this is an issue in some mssql servers.|||No, I don't know of ANY condition under which MS-SQL 7.0 or later versions have any problem interpreting dates formatted as YYYY-MM-DD as long as the date is valid for the datatype you are using.

As I don't know what made you think you needed to change the date formatting, all I can do is say that the date format isn't the problem, something else is.

-PatP|||Yeah, When I changed my date format, the queries worked. So, the problem is really with the format.|||Maybe I'm not making this clear, but the ISO string format you are using for the date is NOT the problem. I don't know what is the problem, but the ISO format isn't it.

-PatP

Debugging Parent / Child Packages

Hi there,

I have a Business Intelligence project containing 4 packages. One of the packages is a parent package that just runs the other 3 packages sequentially (passing in variables via Package Configurations).

When I set the properties of the Execute Package tasks in the parent package, it seems that I can only select SQL Server or File System locations for the child packages.

What I want to do is for the parent to run the packages in the Visual Studio Project so that I can debug the whole process. I don't want to be bothered deploying the child packages so that I can run them from the parent, at least not while I am debugging the whole process.

How do I get the children to run within Visual Studio as well as the parent? I can't for the life of me find anything on the MSDN about this.

Many thanks in advance.

Richard F

I am not sure what your problem is. If I execute the parent package in BIDS; all children packages are open and execute in debug mode as well. At least that is the behavior I get when storing the packages in .dtsx files; I don't know if the behavior is diffrent when storing them in the DB.

|||Hi there,

Thanks for your reply.

Can I ask what you do when you create the parent package?

I did the following:
- Added an "Execute Package" task to the parent
- Edited properties of the task
- Set the location to File System
- Added a new connection manager of type File Connection and pointed this to the location of the child package in the development folder (i.e. the folder used by the VS.NET package project)
- Set the connection of the parent package equal to this connection manager

Then when running the parent package in VS.NET, it did not step into the child package as you described, and to be honest I'm not really surprised since at no point when specifying the child package from the parent did I tell it to run in VS.NET.

How did you reference the child package from the parent?

Many thanks in advance.

Richard F|||It's real simple. In the parent package, use an Execute Package task to call your child package. Double click on the Execute Package task, and select the package you want to run.

Then, when you are in the parent package, you can run the debug (start button, or F5, I think) which will start the child package accordingly.

Debugging Parent / Child Packages

Hi there,

I have a Business Intelligence project containing 4 packages. One of the packages is a parent package that just runs the other 3 packages sequentially (passing in variables via Package Configurations).

When I set the properties of the Execute Package tasks in the parent package, it seems that I can only select SQL Server or File System locations for the child packages.

What I want to do is for the parent to run the packages in the Visual Studio Project so that I can debug the whole process. I don't want to be bothered deploying the child packages so that I can run them from the parent, at least not while I am debugging the whole process.

How do I get the children to run within Visual Studio as well as the parent? I can't for the life of me find anything on the MSDN about this.

Many thanks in advance.

Richard F

I am not sure what your problem is. If I execute the parent package in BIDS; all children packages are open and execute in debug mode as well. At least that is the behavior I get when storing the packages in .dtsx files; I don't know if the behavior is diffrent when storing them in the DB.

|||Hi there,

Thanks for your reply.

Can I ask what you do when you create the parent package?

I did the following:
- Added an "Execute Package" task to the parent
- Edited properties of the task
- Set the location to File System
- Added a new connection manager of type File Connection and pointed this to the location of the child package in the development folder (i.e. the folder used by the VS.NET package project)
- Set the connection of the parent package equal to this connection manager

Then when running the parent package in VS.NET, it did not step into the child package as you described, and to be honest I'm not really surprised since at no point when specifying the child package from the parent did I tell it to run in VS.NET.

How did you reference the child package from the parent?

Many thanks in advance.

Richard F|||It's real simple. In the parent package, use an Execute Package task to call your child package. Double click on the Execute Package task, and select the package you want to run.

Then, when you are in the parent package, you can run the debug (start button, or F5, I think) which will start the child package accordingly.

Debugging CLR Objects

Hi,
I was able to use SQL Server Project template in VS to debug my CLR-SPs. But
I'd like to know if it is possible to debug those CLR-Objects while
executing from SSMS.
Any help would be greatly appreciated.
Leila
Hi
No, CLR objects must be debugged from within Visual Studio 2005.
From SQL Server 2005 BOL
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/denet9/html/1332035c-d6ed-424d-8234-46ad21168319.htm):
"SQL Server 2005 does not ship with a debugger. Microsoft Visual Studio 2005
supports debugging connections to SQL Server. For more information about how
to debug SQL Server 2005 CLR stored procedures, functions, triggers, user
defined types, and aggregates from Visual Studio, see the Visual Studio 2005
documentation."
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Leila" <Leilas@.hotpop.com> wrote in message
news:eibf7eb%23FHA.600@.tk2msftngp13.phx.gbl...
> Hi,
> I was able to use SQL Server Project template in VS to debug my CLR-SPs.
> But
> I'd like to know if it is possible to debug those CLR-Objects while
> executing from SSMS.
> Any help would be greatly appreciated.
> Leila
>
|||Thanks,
But what about attaching processes from VS IDE? It seems there are some
facilities for Analysis Services.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:ecWBBrb#FHA.1332@.tk2msftngp13.phx.gbl...
> Hi
> No, CLR objects must be debugged from within Visual Studio 2005.
> From SQL Server 2005 BOL
>
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/denet9/html/1332035c-d6ed-424d-8234-4
6ad21168319.htm):
> "SQL Server 2005 does not ship with a debugger. Microsoft Visual Studio
2005
> supports debugging connections to SQL Server. For more information about
how
> to debug SQL Server 2005 CLR stored procedures, functions, triggers, user
> defined types, and aggregates from Visual Studio, see the Visual Studio
2005
> documentation."
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:eibf7eb%23FHA.600@.tk2msftngp13.phx.gbl...
>

Debugging CLR Objects

Hi,
I was able to use SQL Server Project template in VS to debug my CLR-SPs. But
I'd like to know if it is possible to debug those CLR-Objects while
executing from SSMS.
Any help would be greatly appreciated.
LeilaHi
No, CLR objects must be debugged from within Visual Studio 2005.
From SQL Server 2005 BOL
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/denet9/html/1332035c-d6ed-424d-8234-4
6ad21168319.htm):
"SQL Server 2005 does not ship with a debugger. Microsoft Visual Studio 2005
supports debugging connections to SQL Server. For more information about how
to debug SQL Server 2005 CLR stored procedures, functions, triggers, user
defined types, and aggregates from Visual Studio, see the Visual Studio 2005
documentation."
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Leila" <Leilas@.hotpop.com> wrote in message
news:eibf7eb%23FHA.600@.tk2msftngp13.phx.gbl...
> Hi,
> I was able to use SQL Server Project template in VS to debug my CLR-SPs.
> But
> I'd like to know if it is possible to debug those CLR-Objects while
> executing from SSMS.
> Any help would be greatly appreciated.
> Leila
>|||Thanks,
But what about attaching processes from VS IDE? It seems there are some
facilities for Analysis Services.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:ecWBBrb#FHA.1332@.tk2msftngp13.phx.gbl...
> Hi
> No, CLR objects must be debugged from within Visual Studio 2005.
> From SQL Server 2005 BOL
>
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/denet9/html/1332035c-d6ed-424d-8234-4
6ad21168319.htm):
> "SQL Server 2005 does not ship with a debugger. Microsoft Visual Studio
2005
> supports debugging connections to SQL Server. For more information about
how
> to debug SQL Server 2005 CLR stored procedures, functions, triggers, user
> defined types, and aggregates from Visual Studio, see the Visual Studio
2005
> documentation."
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:eibf7eb%23FHA.600@.tk2msftngp13.phx.gbl...
>

Debugging CLR Objects

Hi,
I was able to use SQL Server Project template in VS to debug my CLR-SPs. But
I'd like to know if it is possible to debug those CLR-Objects while
executing from SSMS.
Any help would be greatly appreciated.
LeilaHi
No, CLR objects must be debugged from within Visual Studio 2005.
From SQL Server 2005 BOL
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/denet9/html/1332035c-d6ed-424d-8234-46ad21168319.htm):
"SQL Server 2005 does not ship with a debugger. Microsoft Visual Studio 2005
supports debugging connections to SQL Server. For more information about how
to debug SQL Server 2005 CLR stored procedures, functions, triggers, user
defined types, and aggregates from Visual Studio, see the Visual Studio 2005
documentation."
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Leila" <Leilas@.hotpop.com> wrote in message
news:eibf7eb%23FHA.600@.tk2msftngp13.phx.gbl...
> Hi,
> I was able to use SQL Server Project template in VS to debug my CLR-SPs.
> But
> I'd like to know if it is possible to debug those CLR-Objects while
> executing from SSMS.
> Any help would be greatly appreciated.
> Leila
>|||Thanks,
But what about attaching processes from VS IDE? It seems there are some
facilities for Analysis Services.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:ecWBBrb#FHA.1332@.tk2msftngp13.phx.gbl...
> Hi
> No, CLR objects must be debugged from within Visual Studio 2005.
> From SQL Server 2005 BOL
>
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/denet9/html/1332035c-d6ed-424d-8234-4
6ad21168319.htm):
> "SQL Server 2005 does not ship with a debugger. Microsoft Visual Studio
2005
> supports debugging connections to SQL Server. For more information about
how
> to debug SQL Server 2005 CLR stored procedures, functions, triggers, user
> defined types, and aggregates from Visual Studio, see the Visual Studio
2005
> documentation."
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:eibf7eb%23FHA.600@.tk2msftngp13.phx.gbl...
> > Hi,
> > I was able to use SQL Server Project template in VS to debug my CLR-SPs.
> > But
> > I'd like to know if it is possible to debug those CLR-Objects while
> > executing from SSMS.
> > Any help would be greatly appreciated.
> > Leila
> >
> >
>

Debugging CLR Objects

Hi,
I was able to use SQL Server Project template in VS to debug my CLR-SPs. But
I'd like to know if it is possible to debug those CLR-Objects while
executing from SSMS.
Any help would be greatly appreciated.
LeilaHi
No, CLR objects must be debugged from within Visual Studio 2005.
From SQL Server 2005 BOL
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/denet9/html/1332035c-d6ed-424d-8234-4
6ad21168319.htm):
"SQL Server 2005 does not ship with a debugger. Microsoft Visual Studio 2005
supports debugging connections to SQL Server. For more information about how
to debug SQL Server 2005 CLR stored procedures, functions, triggers, user
defined types, and aggregates from Visual Studio, see the Visual Studio 2005
documentation."
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Leila" <Leilas@.hotpop.com> wrote in message
news:eibf7eb%23FHA.600@.tk2msftngp13.phx.gbl...
> Hi,
> I was able to use SQL Server Project template in VS to debug my CLR-SPs.
> But
> I'd like to know if it is possible to debug those CLR-Objects while
> executing from SSMS.
> Any help would be greatly appreciated.
> Leila
>|||Thanks,
But what about attaching processes from VS IDE? It seems there are some
facilities for Analysis Services.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:ecWBBrb#FHA.1332@.tk2msftngp13.phx.gbl...
> Hi
> No, CLR objects must be debugged from within Visual Studio 2005.
> From SQL Server 2005 BOL
>
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/denet9/html/1332035c-d6ed-424d-8234-4
6ad21168319.htm):
> "SQL Server 2005 does not ship with a debugger. Microsoft Visual Studio
2005
> supports debugging connections to SQL Server. For more information about
how
> to debug SQL Server 2005 CLR stored procedures, functions, triggers, user
> defined types, and aggregates from Visual Studio, see the Visual Studio
2005
> documentation."
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:eibf7eb%23FHA.600@.tk2msftngp13.phx.gbl...
>

Debugging a customponent used in a Integration Services project

Hi there
I'm building a SQL Server integration services project with a custom component. I created the custom component as a separate C# library project and compiled it. With the help of some online resources I was able to make it appear in the Dataflow Transformations toolbox items of Integration Services IDE. But this component is throwing some errors. My question how do I setup the environment such that I can set break points and debug the component's source while debugging the integration services project?

Any help will be greatly appretiated.

Thanks in advance.

Arun M

Hi Arun,

open the SSIS designer with your component in it, open another VS window with your C# project where you would like to debug. For design-time debugging, attach the VS debugger (using Debug\Attach to Process...) to devenv.exe process that hosts the SSIS designer. For runtime debugging, attach your debugger to DtsDebugHost.exe.

HTH.

Thanks,

|||Here's another way to debug things, a little different than the way Bob suggested above:

Setup the Custom task to run using DTEXEC as it's startup
program. In the CommandLine arguments, pass in the
filename of the DTSX package that includes the custom
task. Then start the debugger and it should hit your
breakpoints...|||

I am trying to use the second method of DTEXEC but I am getting a "The product level is insufficient for component ..." message on one of my machines and ONLY ONE of them.

The notable difference is that I am connecting to my local machine running the databases VS a remote database. The one using the remote database is failing with the above message and the local database machine debugs just fine.

Note: Running the package with the custom component through VS works just fine.

I am going to attempt the DtsHost... item above but I don't like that one as you have to close the VS environment over and over to do testing of changes!

|||This issue has already been discussed in this forum. To use dtexec you must have a server OS.|||

Then why would I get one working and not the other when they are both on Windows XP SP2?

Is it that DTEXEC can only connect remotely while on a server OS? That seems rather strange to me...

|||No you must have installed SSIS. DTEXEC runs fine on all OS's that SSIS runs on, which includes XP. The message is usally encountered when using the Wizard, and only having installed workstation components, and not the SSIS server components.|||

I am pretty sure I have it all installed and not just the workstation components as I am developing and running on the machine itself.

The component runs fine in BIDS both at design and run-time but only fails with the message when I try to run using DTEXEC.

Darren: Thank you for the pointer to the Regex example that was recently released. I am building a component that will use Regex to allow validation with custom messages logged, data type reformatting based on the matches (e.g. yyyyMMdd -> ^(\d{4})(\d{2})(\d{2})$ would validate the data and $1-$2-$3 would convert the data to a data string that can be type cast to DT_DBTIMESTAMP). Makes the date conversion much simpler.

Debugging a customponent used in a Integration Services project

Hi there
I'm building a SQL Server integration services project with a custom component. I created the custom component as a separate C# library project and compiled it. With the help of some online resources I was able to make it appear in the Dataflow Transformations toolbox items of Integration Services IDE. But this component is throwing some errors. My question how do I setup the environment such that I can set break points and debug the component's source while debugging the integration services project?

Any help will be greatly appretiated.

Thanks in advance.

Arun M

Hi Arun,

open the SSIS designer with your component in it, open another VS window with your C# project where you would like to debug. For design-time debugging, attach the VS debugger (using Debug\Attach to Process...) to devenv.exe process that hosts the SSIS designer. For runtime debugging, attach your debugger to DtsDebugHost.exe.

HTH.

Thanks,

|||Here's another way to debug things, a little different than the way Bob suggested above:

Setup the Custom task to run using DTEXEC as it's startup
program. In the CommandLine arguments, pass in the
filename of the DTSX package that includes the custom
task. Then start the debugger and it should hit your
breakpoints...
|||

I am trying to use the second method of DTEXEC but I am getting a "The product level is insufficient for component ..." message on one of my machines and ONLY ONE of them.

The notable difference is that I am connecting to my local machine running the databases VS a remote database. The one using the remote database is failing with the above message and the local database machine debugs just fine.

Note: Running the package with the custom component through VS works just fine.

I am going to attempt the DtsHost... item above but I don't like that one as you have to close the VS environment over and over to do testing of changes!

|||This issue has already been discussed in this forum. To use dtexec you must have a server OS.
|||

Then why would I get one working and not the other when they are both on Windows XP SP2?

Is it that DTEXEC can only connect remotely while on a server OS? That seems rather strange to me...

|||No you must have installed SSIS. DTEXEC runs fine on all OS's that SSIS runs on, which includes XP. The message is usally encountered when using the Wizard, and only having installed workstation components, and not the SSIS server components.|||

I am pretty sure I have it all installed and not just the workstation components as I am developing and running on the machine itself.

The component runs fine in BIDS both at design and run-time but only fails with the message when I try to run using DTEXEC.

Darren: Thank you for the pointer to the Regex example that was recently released. I am building a component that will use Regex to allow validation with custom messages logged, data type reformatting based on the matches (e.g. yyyyMMdd -> ^(\d{4})(\d{2})(\d{2})$ would validate the data and $1-$2-$3 would convert the data to a data string that can be type cast to DT_DBTIMESTAMP). Makes the date conversion much simpler.

debbuging SPs in VS.NET

I have followed examples in a couple of books but am still unable to step
into Stored Procedures in VS.NET.
I enable sql debugging in the project properties, open the sp in server
explorer and highlight a line where I want to start stepping through, then I
select a line in my VB code such as cmd.ExecuteNonQuery().
But vs.net does not step into the SP it just steps over the
cmd.ExecuteNonQuery() even though I am clicking on the Step Into icon on the
toolbar (F8 doesn't work on my computer)
Am I missing something?
Thanks,
GAnswered in .NET groups
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"G Dean Blake" wrote:

> I have followed examples in a couple of books but am still unable to step
> into Stored Procedures in VS.NET.
> I enable sql debugging in the project properties, open the sp in server
> explorer and highlight a line where I want to start stepping through, then
I
> select a line in my VB code such as cmd.ExecuteNonQuery().
> But vs.net does not step into the SP it just steps over the
> cmd.ExecuteNonQuery() even though I am clicking on the Step Into icon on t
he
> toolbar (F8 doesn't work on my computer)
> Am I missing something?
> Thanks,
> G
>
>