Data Source Column Changes

I have some Dynamic SQL that builds a table with the top 20 item numbers as columns, Stores as rows and an X marking the intersection of Store/Item. This produces a table which is a matrix showing which stores are low on which items.
This table is generated once a week and each week will have different column names because the item numbers which the stores are low on changes from week to week.
How can I write a report that will handle the column names of the data table changing without having to do maintenance on the report every week?

Thanks,
Mark Redman.

One way of doing this is to move the dynamic SQL into a stored procedure and return a dataset which always has fixed column names. The actual item information (item number) would be e.g. the first row in that generated dataset.

-- Robert

Data Processing Extensions and Data Sources

Anyone know about DPE? Specifically, I want to know if it can help me to
make a data source dynamic in a multi-user environment.
I need to integrate SRS into my web app, which allows the user to select a
database from a list before running a report. Several people could try to
run the same report at the same time. If I use the API
"SetDataReportSources" then this switches the data source for all users of
the report. As noted before in this group, this is a limitation of SQL RS.
What I really want to know is: Can I develop a DPE that allows me to
dynamically set the data source at run-time? This is a requirement for my
application, and I want to avoid creating multiple copies of reports or
passing the data source in a stored procedure. Either of these work-arounds
involve more maintenance and hassle for the future. Can DPE allow me to make
a permanent solution?
I looked in the books online, and read about implementing a connection
class. If I made my own connection object, could I set it dynamically at run
time?
Hopefully,
MalikI'm trying to do the same thing as well but we havn't had the time or
resource yet to finish it off. Once I have a DPE running I would be happy to
share it on gotdotnet. Or if you get there first maybe you could to.
Try this link for further info. This is where I got started. I have aslo
uploaded some VB code to Gavin's page to complement the C# he has already.
It's still not as straight forward as you might hope but it will acomplish
what you are after.
http://weblogs.asp.net/gavinjoyce/archive/2004/01/29/64339.aspx?Pending=true
Regards
Toby,.
"Abdul Malik Said" <diplacusis@.hotmNOSPAMail.com> wrote in message
news:O4sJVKDZEHA.556@.tk2msftngp13.phx.gbl...
> Anyone know about DPE? Specifically, I want to know if it can help me to
> make a data source dynamic in a multi-user environment.
> I need to integrate SRS into my web app, which allows the user to select a
> database from a list before running a report. Several people could try to
> run the same report at the same time. If I use the API
> "SetDataReportSources" then this switches the data source for all users of
> the report. As noted before in this group, this is a limitation of SQL RS.
> What I really want to know is: Can I develop a DPE that allows me to
> dynamically set the data source at run-time? This is a requirement for my
> application, and I want to avoid creating multiple copies of reports or
> passing the data source in a stored procedure. Either of these
work-arounds
> involve more maintenance and hassle for the future. Can DPE allow me to
make
> a permanent solution?
> I looked in the books online, and read about implementing a connection
> class. If I made my own connection object, could I set it dynamically at
run
> time?
> Hopefully,
> Malik
>

Data Proc Ext - Dynamic Connect String

I am trying to create a data processing extension that will allow me to
dynamically set the connect string. I have been able to successfully
register a sample data processing extension (from Teo Lachev's book), but I'm
not sure if I need to implement all of the classes included - can I just
implement the IDbConnection, IDbConnectionExtension, and IExtension much like
DsConnectionWrapper, but then use OleDbCommand rather than writing DsCommand,
etc.?
I really just need the ability to override the connection string based on
the user that authenticates to the report server. I have a security
extension in place for Forms authentication. I need to be able to switch
dev/test/prod connect strings in addition to changing databases based on who
connected.
BTW, I am using OLEDB and the OLAP provider to connect to an Analysis
Services database so I'm not sure I can just dynamically change my MDX like
some have suggested changing SQL.This is coming in SQL 2005 but I'm not sure it is possible in SQL2K. The
only thing I could think of is to pass the user ID and the query in on the
command and then connect and query at the same time.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
"danb" <danb@.discussions.microsoft.com> wrote in message
news:9E9697FD-86A2-45E4-9652-62CE1BCFCFC1@.microsoft.com...
>I am trying to create a data processing extension that will allow me to
> dynamically set the connect string. I have been able to successfully
> register a sample data processing extension (from Teo Lachev's book), but
> I'm
> not sure if I need to implement all of the classes included - can I just
> implement the IDbConnection, IDbConnectionExtension, and IExtension much
> like
> DsConnectionWrapper, but then use OleDbCommand rather than writing
> DsCommand,
> etc.?
> I really just need the ability to override the connection string based on
> the user that authenticates to the report server. I have a security
> extension in place for Forms authentication. I need to be able to switch
> dev/test/prod connect strings in addition to changing databases based on
> who
> connected.
> BTW, I am using OLEDB and the OLAP provider to connect to an Analysis
> Services database so I'm not sure I can just dynamically change my MDX
> like
> some have suggested changing SQL.

Data loss in replication

hi,

Im using merge replication.I have created one publisher with dynamic filtering and i have creted two subscribers from it(pull subscription).It was working fine.but after sysnchronizing 2-3 times data from some tables went missing

can any one help me....

thanks and regards

Dhanya

Can you provide more detail? If you don't mind, can you post your table schema and filter information or your replication setup script and indicate what was missing? Data from the publisher table or from the subscriber table?

Regards,

Gary

|||

It may be because your DML caused data to go out of partition from the subscribers.

Is this SQL 2000? Is the setting compensate_for_errors=true for the article you are experiencing data loss?

And by data loss, do you mean an insert done at publisher after synchronizing, does not exist anymore at the publisher and subscriber?

Data loss in replication

hi,

Im using merge replication.I have created one publisher with dynamic filtering and i have creted two subscribers from it(pull subscription).It was working fine.but after sysnchronizing 2-3 times data from some tables went missing

can any one help me....

thanks and regards

Dhanya

Can you provide more detail? If you don't mind, can you post your table schema and filter information or your replication setup script and indicate what was missing? Data from the publisher table or from the subscriber table?

Regards,

Gary

|||

It may be because your DML caused data to go out of partition from the subscribers.

Is this SQL 2000? Is the setting compensate_for_errors=true for the article you are experiencing data loss?

And by data loss, do you mean an insert done at publisher after synchronizing, does not exist anymore at the publisher and subscriber?