Data extension question?

Hi,
when you use sql server data extension and set the query to a stored
proceduere, Reporting services automatically creates report parameters based
on the stored proc parameters. I'm creating a custom data processing
extension and i would like to create parameters based on a config file. How
do i do this?
thanks
shankarIn your custom data processing extension, you should implement
IDbCommandAnalysis. In GetParameters, you can access your config file.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"shankar" <sramasubramanian@.ozcap.com> wrote in message
news:uk7P%23hXdEHA.3132@.TK2MSFTNGP11.phx.gbl...
> Hi,
> when you use sql server data extension and set the query to a stored
> proceduere, Reporting services automatically creates report parameters
based
> on the stored proc parameters. I'm creating a custom data processing
> extension and i would like to create parameters based on a config file.
How
> do i do this?
> thanks
> shankar
>|||Thanks, Chris. I have one more question. I implemented IDbCommandAnalysis
interface and i got all the parameters from my config file. In the config
file , i also stored designer default values for all the parameter. I would
like the Reportdesigner to use the designer value when it executes the query
command. You could view the designer value as dummy value. For Example, if
you are using sql server stored proc, you would pass all the required
parameter as a part of command text.
When i implemented, IDbCommandAnalysis interface, i passed this
parameter,designer value pair. I think reporting services uses this
interface only to get the list of parameters and it simply ignores default
value.Is there any way to do this.
thanks
shankar
"Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
news:ueGeyMadEHA.2384@.TK2MSFTNGP09.phx.gbl...
> In your custom data processing extension, you should implement
> IDbCommandAnalysis. In GetParameters, you can access your config file.
> --
> This post is provided 'AS IS' with no warranties, and confers no rights.
All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No
user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach
of
> children under 3.
> "shankar" <sramasubramanian@.ozcap.com> wrote in message
> news:uk7P%23hXdEHA.3132@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> > when you use sql server data extension and set the query to a stored
> > proceduere, Reporting services automatically creates report parameters
> based
> > on the stored proc parameters. I'm creating a custom data processing
> > extension and i would like to create parameters based on a config file.
> How
> > do i do this?
> >
> > thanks
> >
> > shankar
> >
> >
>|||That is correct. The design tool uses the interface only to obtain the
parameter names, not to retrieve default values.
Using it for default values as well is a good idea. I'll forward it on to
the designer team. Thanks.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"shankar" <sramasubramanian@.ozcap.com> wrote in message
news:OjXL2YLeEHA.3864@.TK2MSFTNGP10.phx.gbl...
> Thanks, Chris. I have one more question. I implemented IDbCommandAnalysis
> interface and i got all the parameters from my config file. In the config
> file , i also stored designer default values for all the parameter. I
would
> like the Reportdesigner to use the designer value when it executes the
query
> command. You could view the designer value as dummy value. For Example, if
> you are using sql server stored proc, you would pass all the required
> parameter as a part of command text.
> When i implemented, IDbCommandAnalysis interface, i passed this
> parameter,designer value pair. I think reporting services uses this
> interface only to get the list of parameters and it simply ignores default
> value.Is there any way to do this.
> thanks
> shankar
> "Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
> news:ueGeyMadEHA.2384@.TK2MSFTNGP09.phx.gbl...
> > In your custom data processing extension, you should implement
> > IDbCommandAnalysis. In GetParameters, you can access your config file.
> >
> > --
> > This post is provided 'AS IS' with no warranties, and confers no rights.
> All
> > rights reserved. Some assembly required. Batteries not included. Your
> > mileage may vary. Objects in mirror may be closer than they appear. No
> user
> > serviceable parts inside. Opening cover voids warranty. Keep out of
reach
> of
> > children under 3.
> > "shankar" <sramasubramanian@.ozcap.com> wrote in message
> > news:uk7P%23hXdEHA.3132@.TK2MSFTNGP11.phx.gbl...
> > > Hi,
> > > when you use sql server data extension and set the query to a
stored
> > > proceduere, Reporting services automatically creates report parameters
> > based
> > > on the stored proc parameters. I'm creating a custom data processing
> > > extension and i would like to create parameters based on a config
file.
> > How
> > > do i do this?
> > >
> > > thanks
> > >
> > > shankar
> > >
> > >
> >
> >
>

Data extension and ad hoc reporting

I've created a data processing extension that retrieves data from a
particular table based on the parameters given by the end-user. Of course
the tables do not have the same columns, so creating the report in the report
designer has been problematic. So far I've found one possible solution,
generating the rdl dynamically, similar to what is done in the "Ad hoc"
sample. I have been unable to successfully tie the dynamically generated rdl
to my data extension. I'm hoping that someone can point me in the right
direction.Sorry I forgot to include the following information.
RS 2000 Sp2
VB.NET
"Tamichan" wrote:
> I've created a data processing extension that retrieves data from a
> particular table based on the parameters given by the end-user. Of course
> the tables do not have the same columns, so creating the report in the report
> designer has been problematic. So far I've found one possible solution,
> generating the rdl dynamically, similar to what is done in the "Ad hoc"
> sample. I have been unable to successfully tie the dynamically generated rdl
> to my data extension. I'm hoping that someone can point me in the right
> direction.

Data export to individual files

Hello,
I am looking to T-SQL extract records from a table and have each record
stored in individual .eml files. A query based DTS doesn't let me
specify what extension my files will have AND I'm unsure how to have
each record written to a separate file? Any ideas on how I can pull
this off?
Much appreciated,
Prpryan75 wrote:
> Hello,
> I am looking to T-SQL extract records from a table and have each record
> stored in individual .eml files. A query based DTS doesn't let me
> specify what extension my files will have AND I'm unsure how to have
> each record written to a separate file? Any ideas on how I can pull
> this off?
> Much appreciated,
> Pr
>
A few possibilities:
1. Use OSQL to run the query and pipe the output to the desired file
2. Use xp_cmdshell to issue DOS "ECHO" commands to write the desired file
3. Use a combination of xp_cmdshell and OPENROWSET to create and then
write the desired file
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Data export to individual files

Hello,
I am looking to T-SQL extract records from a table and have each record
stored in individual .eml files. A query based DTS doesn't let me
specify what extension my files will have AND I'm unsure how to have
each record written to a separate file? Any ideas on how I can pull
this off?
Much appreciated,
Prpryan75 wrote:
> Hello,
> I am looking to T-SQL extract records from a table and have each record
> stored in individual .eml files. A query based DTS doesn't let me
> specify what extension my files will have AND I'm unsure how to have
> each record written to a separate file? Any ideas on how I can pull
> this off?
> Much appreciated,
> Pr
>
A few possibilities:
1. Use OSQL to run the query and pipe the output to the desired file
2. Use xp_cmdshell to issue DOS "ECHO" commands to write the desired file
3. Use a combination of xp_cmdshell and OPENROWSET to create and then
write the desired file
Tracy McKibben
MCDBA
http://www.realsqlguy.com

data driven subscription does not work

I defined a data driven subscription based on a query which returns a list of compagnies (90). For each one, the subcription has to fire a report which extracts the order for the compagny.

After lauching the subcription (apply button , the subscription status is Processed 0 for 90, 0 errors

I do not get any error message.

The report works fine via a subscription base on calendar (with a compagny by default), but not via the data driven subcription.

What could be the reason of this pb ?

By advance thanks

Georges

DId you have a look in the log files ? If the information gven there is not sufficient you can turn on verbose logging, which can be either turned on via the GUI (in SQL Server 2005) or by edition the .config file.

Also make sure that you retrieval process / query doesn′t create any locks on the table.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

I tuned on verbose the log, but nothing special that could help me, may be this extrat.

String was not recognized as a valid Boolean. from the ReportServerService file

Microsoft.ReportingServices.FileShareDeliveryProvider.FileShareProvider.Deliver(Notification notification)
ReportingServicesService!library!b7c!05/27/2006-21:49:13:: Data Driven Notification for activation id e9779dcd-c025-4c70-bdad-957582640df2 was saved.
ReportingServicesService!library!b7c!05/27/2006-21:49:13:: Status: Failure writing file NorthWind : String was not recognized as a valid Boolean.
ReportingServicesService!dbpolling!b7c!05/27/2006-21:49:13:: NotificationPolling finished processing item b2f499cf-11d1-4a72-ad46-5b15335c4dca
ReportingServicesService!dbpolling!b20!27/05/2006-21:49:13:: NotificationPolling processing 1 more items. 1 Total items in internal queue.
ReportingServicesService!dbpolling!2f4!05/27/2006-21:49:13:: NotificationPolling processing item d62cfef7-babb-45b5-89d3-8e51fab3b995
ReportingServicesService!library!2f4!05/27/2006-21:49:13:: v VERBOSE: Transaction begin.
ReportingServicesService!chunks!2f4!05/27/2006-21:49:13:: v VERBOSE: ### ReportSnapshot(359966ad-f99e-4d32-a0eb-17696e8d0651) constructor of existing snapshot.
ReportingServicesService!chunks!2f4!05/27/2006-21:49:13:: v VERBOSE: ### ReportSnapshot(43b6cae4-0795-4c73-8e6c-8a98a0eb708c) constructor of existing snapshot.
ReportingServicesService!runningjobs!2f4!05/27/2006-21:49:13:: v VERBOSE: ThreadJobContext.BeginCancelableState
ReportingServicesService!chunks!2f4!05/27/2006-21:49:13:: v VERBOSE: ### GetReportChunk('Main', Main) this=43b6cae4-0795-4c73-8e6c-8a98a0eb708c, #ReadChunks=0, #WriteChunks=0
ReportingServicesService!chunks!2f4!05/27/2006-21:49:13:: v VERBOSE: ### ChunkWriteStream - constructor(43b6cae4-0795-4c73-8e6c-8a98a0eb708c, 'Main', 0)
ReportingServicesService!chunks!2f4!05/27/2006-21:49:13:: v VERBOSE: ### ChunkReadStream - constructor(43b6cae4-0795-4c73-8e6c-8a98a0eb708c, 'Main', 0)
ReportingServicesService!chunks!2f4!05/27/2006-21:49:13:: v VERBOSE: ### GetChunkPointerAndLength(43b6cae4-0795-4c73-8e6c-8a98a0eb708c, Main, 0)
ReportingServicesService!library!2f4!05/27/2006-21:49:13:: v VERBOSE: Transaction begin.
ReportingServicesService!chunks!2f4!05/27/2006-21:49:13:: v VERBOSE: ### ChunkReadStream.Close() - closing... id=43b6cae4-0795-4c73-8e6c-8a98a0eb708c, name='Main'
ReportingServicesService!library!2f4!05/27/2006-21:49:13:: v VERBOSE: Transaction commit.
ReportingServicesService!chunks!2f4!05/27/2006-21:49:13:: v VERBOSE: ### ChunkReadStream - Closed! id=43b6cae4-0795-4c73-8e6c-8a98a0eb708c, name='Main'
ReportingServicesService!chunks!2f4!05/27/2006-21:49:13:: v VERBOSE: ### GetReportChunk('Main', Main) this=43b6cae4-0795-4c73-8e6c-8a98a0eb708c, #ReadChunks=1, #WriteChunks=0
ReportingServicesService!chunks!2f4!05/27/2006-21:49:13:: v VERBOSE: ### ChunkWriteStream - constructor(43b6cae4-0795-4c73-8e6c-8a98a0eb708c, 'Main', 0)
ReportingServicesService!chunks!2f4!05/27/2006-21:49:13:: v VERBOSE: ### ChunkReadStream - constructor(43b6cae4-0795-4c73-8e6c-8a98a0eb708c, 'Main', 0)
ReportingServicesService!chunks!2f4!05/27/2006-21:49:13:: v VERBOSE: ### GetChunkPointerAndLength(43b6cae4-0795-4c73-8e6c-8a98a0eb708c, Main, 0)
ReportingServicesService!library!2f4!05/27/2006-21:49:13:: v VERBOSE: Transaction begin.
ReportingServicesService!chunks!2f4!05/27/2006-21:49:13:: v VERBOSE: ### ChunkReadStream.Close() - closing... id=43b6cae4-0795-4c73-8e6c-8a98a0eb708c, name='Main'
ReportingServicesService!library!2f4!05/27/2006-21:49:13:: v VERBOSE: Transaction commit.
ReportingServicesService!chunks!2f4!05/27/2006-21:49:13:: v VERBOSE: ### ChunkReadStream - Closed! id=43b6cae4-0795-4c73-8e6c-8a98a0eb708c, name='Main'
ReportingServicesService!runningjobs!2f4!05/27/2006-21:49:13:: v VERBOSE: ThreadJobContext.EndCancelableState
ReportingServicesService!runningjobs!2f4!05/27/2006-21:49:13:: v VERBOSE: ThreadJobContext.WaitForCancelException entered
ReportingServicesService!runningjobs!2f4!05/27/2006-21:49:13:: v VERBOSE: ThreadJobContext.WaitForCancelException finished
ReportingServicesService!runningjobs!2f4!05/27/2006-21:49:13:: v VERBOSE: RunningJobList.RemoveJob: uljnfsigggboaxb5liswsa45 was removed
ReportingServicesService!library!2f4!05/27/2006-21:49:13:: v VERBOSE: Transaction commit.
ReportingServicesService!library!2f4!05/27/2006-21:49:13:: v VERBOSE: Call to ListProviders: type (Render).
ReportingServicesService!library!2f4!05/27/2006-21:49:13:: v VERBOSE: Call to ListProviders completed.
ReportingServicesService!crypto!2f4!05/27/2006-21:49:13:: v VERBOSE: Starting crypto operation DBUnProtectData
ReportingServicesService!crypto!2f4!05/27/2006-21:49:13:: v VERBOSE: Completed crypto operation DBUnProtectData
ReportingServicesService!crypto!2f4!05/27/2006-21:49:13:: v VERBOSE: Starting crypto operation DBUnProtectData
ReportingServicesService!crypto!2f4!05/27/2006-21:49:13:: v VERBOSE: Completed crypto operation DBUnProtectData
ReportingServicesService!notification!2f4!05/27/2006-21:49:13:: Handling notification of subscription to report NorthWind_multisite via delivery provider Report Server FileShare
ReportingServicesService!library!2f4!05/27/2006-21:49:13:: v VERBOSE: Call to ListProviders: type (Render).
ReportingServicesService!library!2f4!05/27/2006-21:49:13:: v VERBOSE: Call to ListProviders completed.
ReportingServicesService!subscription!2f4!05/27/2006-21:49:13:: System.FormatException: String was not recognized as a valid Boolean.
at System.Boolean.Parse(String value)
at Microsoft.ReportingServices.FileShareDeliveryProvider.SubscriptionData.FromSettings(Setting[] settings)
at Microsoft.ReportingServices.FileShareDeliveryProvider.FileShareProvider.Deliver(Notification notification)

|||In the data driven subscriptions, are you sure that you have all boolean values which are configurable through the query of the datasource set to either true or false ? Otherwise the reporting service can not convert if you map the "Include Report" property to "SomeCustomerNameValue". You have to make sure that the data is consistent through all your available data, otherwise it can be that the subscriptions breaks.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de
|||

This is the query I defined. The only parameter I pass to the report is companyname. The others are defined via the subcription tab (path, report name, user, password ...)

Is my query not correct ? How can I find the wrong parameter ?

My query :

select companyname from customers

|||

Hi,

the query seems ok, perhaps you check the other settings which might be not set all appropiate. But I hope you don′t you the companyname in a column / property of the scheduled report which expects a boolean value.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

I found the raison of the problem. It was the file suffix property (True/false) I set up to a wrong value !

Data Display in Reports based on windows Authentication ID

Hi Experts,

I have a reporting scenario, where the reports are fetched from Analysis Services.

The reports should display data only spcecific to that user.

All users except those in admin roles should be validated using the Windows Authentication ID and data specific to them has to be displayed.

Any pointers/suggestions on how to implement this in Reporting services/ Analysis Services 2005 would be highly appreciated

Thanks,

Hi,

Easier said than done. I guess that the easier path would be to use data filtering based on parameters values limited by userid.

It depends, if you access cubes directly as well, i.e. from Excel, then that would be different. You would have to create custom MDX dimension access formulas based on roles and map these roles to Windows groups.

Assuming you are only going after AS data from RS, then you would still need to create an access policy.

i.e. you can segregate access to data slices based on membership to Windows groups.

We have found that using cumulative membership to groups was the most flexible and did not require too many groups.

You have to find a way to load LDAP (or Windows AD groups and membership) data, then each time an user access the report, you use the userid info to check what is the acceptable list of values for your parameters.

This way, a given user will be able to see and select only parameters values acceptable for her job function.

That is quite a lot of code, functions and procedures to write, no quick fix.

First thing first, you need the AD or LDAP data, there are quite a few ways to get it. I remember that SQL Server magazine ran an article a few month ago about getting it with a SSIS package.

You can also settle for another easier way, like calling Exporter Pro from a Windows scheduled task. Exporter Pro is part of the Hyena software.

Your question is not the type of question where a quick and precise answer is available. I can only give some high level clues. The rest would take time and require insider knowledge of your situation and requirements.

Hope it helps somehow

Philippe

Data Dictionary for Dimensions

Is there any easy way to create a dictionary or lookup function based on
Dimensions in a cube? We only have 4 cubes so far but the big one has some
50 dimensions and sometimes finding the attribute you want to report on is
consuming.
Would like to for example to search somewhere for where the attribute
"model" is, from example below and have it return the cube and dimension
it's in:
Something like:
Cube XYZ
Dimension: Autos
Attributes:
Make
Model
Year Released
..Hello Joe,
I am not sure what version of Analysis services you are using so I am
going to assume that you are using AS 2005.
With AS 2005 you have the ability to create perspectives. These can be
used to group dimensions and measures into common areas for reporting.
Perspectives can be defined to the attribute and measure level. You can
also create a linked cube that can maintain the perspectives across all
your cubes.
You could also consider using report builder as a front end. It
provides a good ad hoc query interface, but is not a replacement for
pivot tables in Excel. Report Builder creates a semantic layer on top
of the cube that allows users to search for attributes and entities
when creating a query.
To use this you will need Reporting services 2005 installed. Check out
my blog post on creating a report model against AS 2005 cubes.
http://bi-on-sql-server.blogspot.co...er-and-udm.html
Hope this helps,
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/|||underprocessable|||Hello Joe,
I have seen this error before. It was caused by the SQL server being
renamed Check the following:
1. Use the full server name instead of local alias
2. Check that the server has not been renamed
3. Try to connect to the RS server through Management Studio
Are you only getting this error in BIDS. Have you tried accessing
Reports in Report Manager?
Myles|||I got it working well on my local box - so it must be related to the server
I'm deploying too...maybe security issues...
<Myles.Matheson@.gmail.com> wrote in message
news:1156928164.274497.324030@.i42g2000cwa.googlegroups.com...
> Hello Joe,
> I have seen this error before. It was caused by the SQL server being
> renamed Check the following:
> 1. Use the full server name instead of local alias
> 2. Check that the server has not been renamed
> 3. Try to connect to the RS server through Management Studio
> Are you only getting this error in BIDS. Have you tried accessing
> Reports in Report Manager?
> Myles
>

data corruption question

sql2k sp3
Based on recent experience Ive got a few questions about data corruption:
1> How does it happen?
2> What can be done to prevent it?
3> If it does happen, what are the proper steps to try to fix it without
restoring?
4> If I do need to restore, how do I know how far back to restore? I wouldnt
want to go through a lengthy restore process and then find out I still have
the corruption. Is there a way to know the day/time it first started?
TIA, ChrisRHi
99.999% of all corruption is caused by hardware.
Get good and reputable hardware and don't cut corners when installing it.
Have a UPS to keep the server up during power interruptions. Have battery
backed up RAID controller cards.
Make very regular backups, including transaction logs and get them off to
tape ASAP. Run regular DBCC's to check the consistency of the databases, and
have 'Torn page detection' on for each database. This will tell you very
quickly that some corruption has occurred. How often? Well, depending on
your uptime requirements, DBCC's daily does not sound like a bad idea.
Some times, corruption is not detected until weeks after it occurred so you
loose a lot. Daily, at worst case you loose a day's data.
Run the latest BIOS and driver versions, keep up to date with Windows SP's
and hotfixes and SQL SP's.
Don't plug you server and the kettle into the same wall plug, and keep the
Managers away from the servers. LOL.
Most of it is just common sense.
Regards
--
--
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/
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:1A640B78-E981-4F57-B8A9-AF324C0ECE10@.microsoft.com...
> sql2k sp3
> Based on recent experience Ive got a few questions about data corruption:
> 1> How does it happen?
> 2> What can be done to prevent it?
> 3> If it does happen, what are the proper steps to try to fix it without
> restoring?
> 4> If I do need to restore, how do I know how far back to restore? I
wouldnt
> want to go through a lengthy restore process and then find out I still
have
> the corruption. Is there a way to know the day/time it first started?
> TIA, ChrisR

Daft DTS Data Driven task question

Hi, I'm trying to get a Data Driven DTS task to update a table based on a .csv source file. I've got a numeric ID Identity column (unique) to give me a record number, and I'm using the following code on the update query to try and update the relevant record:

UPDATE [DR-TestDB].dbo.[Test - CustAddress]
SET
County = ?
WHERE (ID = ?)

The problem I'm getting is a conversion error from VarChar to Numeric when I run the task. I've tried using a type conversion in the transformation, but that doesn't appear to help.

Anyone know the answer - I'll bet it's simple :)

Cheers,

MenthosHmmm... ok, I seem to have resolved this - just rebuilt the task from scratch and it appears to have worked fine.

Very strange.

CustomQueryDesigner Problem: React to "Resize" Event of QueryDesigner Window

Hello everybody!

I am currently developing a CustomQueryDesigner which is based on a Windows.Form that I have already developed.

Currently the QueryDesigner Class contains two UserControls: the ToolBar and the class that wrapps my own QueryDesigner.

So what I want to do now, is to resize my Windows.Form which is encapsulated in the second UserControl whenever the size of the window, that contains these two UserControls, is modified.

I already tried to use the "Parent" property and the "FindForm()" method but both just return "null".

--> I do not unterstand how I can access these informations!

Please help me out!

help!!!!

Customizing Report Parameter control; handling "available values"

I have a report that I show a bill of material based on a selected
parent. I have about 17,000 parents to select from. When I use
"available values from query" the current behavior on the report is a
drop down box where I can choose between all 17,000 where at least for
large collections I'd prefer being able to start typing in and have the
control filter the query based on what's typed into the parameter. Is
there a way to do this?Hi Benjamin.
I believe what you're looking for can be accomplished with a custom
parameter page. There is no way I know of with reporting services to
provide narrowing filter functionality out of the box.
"Benjamin Chan" <benjamin.no.spam.chan@.controlproductsinc.com> wrote in
message news:u1CkX$$gGHA.5096@.TK2MSFTNGP02.phx.gbl...
>I have a report that I show a bill of material based on a selected parent.
>I have about 17,000 parents to select from. When I use "available values
>from query" the current behavior on the report is a drop down box where I
>can choose between all 17,000 where at least for large collections I'd
>prefer being able to start typing in and have the control filter the query
>based on what's typed into the parameter. Is there a way to do this?