How can I bind a ReportViewer to a DataSourceControl?

I have created a System.Web.UI.DataSourceControl which interfaces with an external application and, with the help of a DataSourceView, generates an IEnumerable collection which can be used as a binding source. The primary use is to publish a table of data to a web page using a GridView which is bound to the DataSourceControl via it's DataSourceID property. All of this is working perfectly and now I want to be able to publish this same grid to a ReportViewer running in Local Mode. What is the proper way to do this? I know I need to design a report, but am unclear about how to make the connection between my DataSourceControl and the report. Here's what I have done so far, but am not sure if I am on the right path.

Starting with my existing, working page which has one DataSourceControl and one Gridview bound to the control.

1. Add a ReportViewer to the page.
2. Select "Design a new report" from the viewers smart-tag
3. Add a Table to the report
4. Add a DataSet to the project
5. Add a DataTable to the DataSet
6. Add 2 columns to the DataTable (assuming the case where my DataSourceControl will be returning 2 columns)
7. Now that I've added the DataSet and DataTable, it appears in the ReportViewer's "Website Data Sources" toolbox, so I drag "Column1" and Column2" out onto the table. This gives me 2 headers and the data cells look like "=Fields!Column1.Value" etc.
8. Only after I have added the columns can I go back to the ReportViewer's smart tag and select "Choose Data Sources". This brings up a dialog with a 2 column table with the headings "Report Data Source" and "Data Source Instance". The ReportDataSource is already set to "DataSet1_DataTable1" and the "DataSourceInstance is defaulted to "(None)" but has a drop-down with which I can either select the instance of my DataSourceControl which is on the page (DataSourceControl1) or else select "<New Data Source...> which brings up a Data Source Configuration Wizard. I choose to select my Data Source Control instance.
9. I select "Rebind Data Sources" on the Report Viewer's smart-tag. This adds a new "Object Data Source" to the page, but I don't see how this has any relation to my DataSourceControl. It's SelectMethod is set to "GetData" and its TypeName is set to "DataSet1TableAdapters." which doesn't make much sense to me.

What am I missing? Is there a simpler way to do this? Can I bind a ReportViewer to my DataSource control directly? or do I need to add methods to my DataSourceControl which return the tabular data as a DataTable inside of a DataSet?

Thanks for any help. This is way too confusing.no replys? is my question too complex? i would think this would not be too hard. any help would be greatly appreciated.

How can I automate importing tab delimited file into SQL Server?

Hi,
We import data from our phone system every morning which is a tab delimited
file. I'd like to automate this process. I'd appreciate some pointers on thi
s.
Thanks,
SamThere are a few methods to do this, such as DTS, BULK INSERT or BCP. The
basic pattern is to import into a staging table, validate/scrub and then
insert the new/changed data into your permanent table.
The default text file format for BULK INSERT and BCP is tab-delimited,
carriage-return/line-feed terminated so you can probably import without a
format file as long as your target table matches the fields in the files.
My personal preference would be to use DTS for this task.
Hope this helps.
Dan Guzman
SQL Server MVP
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:29C44B40-6625-48DE-90DF-BAF7FBA6EB76@.microsoft.com...
> Hi,
> We import data from our phone system every morning which is a tab
> delimited
> file. I'd like to automate this process. I'd appreciate some pointers on
> this.
> --
> Thanks,
> Sam

How can I append children to parents in a SSIS data flow task?

I need to extract data to send to an external agency in their supplied format. The data is normalised in our system in a one to many relationship. The external agency needs it denormalised.

In our system, the parent p has p_id, p_attribute_1, p_attribute_2, p_attribute_3 and the child has c_id, c_attribute_a, c_attribute_b, c_parent_id_fk

The external agency can only use a delimited file looking like

p_id, p_attribute_1, p_attribute_2, p_attribute_3, c1_attribute_a, c1_attribute_b, c2_attribute_a, c2_attribute_b, ...., cn_attribute_a, cn_attribute_b

where n is the number of children a parent may have. Each parent can have 0 or more children - typically between 1 and 20.

How can I achieve this using SSIS? In the past I have used custom built VB apps with the ADO SHAPE command but this is not ideal as I have to rebuild each time to alter the selection criteria and and VB is not a good SQL tool.

You can use the pivot transform to move rows to columns, but it doesn't handle a variable number of columns very well. I'd try creating a script component that outputs the set of children for a parent as one long string. Then you can append it to the parent value.|||

jwelch wrote:

You can use the pivot transform to move rows to columns, but it doesn't handle a variable number of columns very well. I'd try creating a script component that outputs the set of children for a parent as one long string. Then you can append it to the parent value.

I agree with John.

The way I would approach this is to join the parent and child records together in a SQL statement so you have a row for each parent/child combination. Use this statement in the Source of a Data Flow and run the rows into an asynchronous script transformation component. You'll write code in the script to denormalize the incoming rows into a single output row for each parent. SSIS won't adapt to having an arbitrary number of child columns and I don't think there is a need for it. The output of the script should be a single column (string if it can fit, otherwise text) that is already delimited and can go directly into a flat file destination.

So the script is the only tricky part. It will look at the p_id on each row it receives to determine if this is the same parent as the last input row. If it isn't, then it will write the output row it was working on for the previous p_id to the output and start a new one by appending the p_id and parent and child attributes to a string. If it is the same p_id, it will continue to append the child attributes until it sees the next p_id or the last row.

Something like this oughta do it:

Code Snippet

Public Class ScriptMain
Inherits UserComponent

Dim last_pid As Integer = -1
Dim current_row As StringBuilder = Nothing

Public Overrides Sub FinishOutputs()
' the input is finished, flush the current row
WriteRow()
End Sub

Public Sub WriteRow()
If Not current_row Is Nothing Then
With Output0Buffer
.AddRow()
.OutputLine = current_row.ToString()
' if we use a dt_text column
'.OutputLine.AddBlobData(ASCIIEncoding.ASCII.GetBytes(current_row.ToString()))
End With
End If

End Sub

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'
' Add your code here
'
If Row.pid <> last_pid Then

' this is a new p_id starting, write the previous one to the output
WriteRow()

' remember this pid for future rows
last_pid = Row.pid

' create new buffer
current_row = New StringBuilder()
' append parent attributes
With current_row
.Append(Row.pid.ToString())
.Append(",")
.Append(Row.pattribute1)
.Append(",")
.Append(Row.pattribute2)
.Append(",")
.Append(Row.pattribute3)
End With
End If

' append child attributes
With current_row
.Append(",")
.Append(Row.cattributea)
.Append(",")
.Append(Row.cattributeb)
End With
End Sub

End Class

Note: The JOIN should group all of the parents together, but just in case you should probably throw an ORDER BY p_id in for that SQL statement.

|||

Phew. I went out 2 hours ago with the express purpose of coming back and answering this but John and Jay have beaten me to it

I was going to suggest exactly what Jay has said. Its kind of hobson's choice on this one - the metadata is unknown at design-time therefore you HAVE to have a single column with all the values concatenated. The fact that you are outputting to a file means that it really doesn't matter anyway.

I would add that this would be a great candidate for a custom component if you can be bothered to build it.

-Jamie

how can get all users database name?

i want to get all users database name
but sp_helpdb return all database,and
ms advice don't use system table ,how can i do this?
thank youDid u mean, to get all the users in the database. Then use,
sp_helplogins
Thanks
Hari
MCDBA
"frank" <anonymous@.discussions.microsoft.com> wrote in message
news:cab701c3ba43$10011f30$a601280a@.phx.gbl...
> i want to get all users database name
> but sp_helpdb return all database,and
> ms advice don't use system table ,how can i do this?
> thank you|||It's okay to just query it. Updating it is really something you don't want
to do.
btw, you could also use:
select catalog_name [Name of the database where the current user has
permissions.]
from information_schema.schemata
where catalog_name not in
('master','msdb','tempdb','model','northwind','pubs')
--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net
"frank" <anonymous@.discussions.microsoft.com> wrote in message
news:cab701c3ba43$10011f30$a601280a@.phx.gbl...
> i want to get all users database name
> but sp_helpdb return all database,and
> ms advice don't use system table ,how can i do this?
> thank you|||thanks a lot oj,it's so helpful
>--Original Message--
>It's okay to just query it. Updating it is really
something you don't want
>to do.
>btw, you could also use:
>select catalog_name [Name of the database where the
current user has
>permissions.]
>from information_schema.schemata
>where catalog_name not in
>('master','msdb','tempdb','model','northwind','pubs')
>--
>-oj
>RAC v2.2 & QALite!
>http://www.rac4sql.net
>
>"frank" <anonymous@.discussions.microsoft.com> wrote in
message
>news:cab701c3ba43$10011f30$a601280a@.phx.gbl...
>> i want to get all users database name
>> but sp_helpdb return all database,and
>> ms advice don't use system table ,how can i do this?
>> thank you
>
>.
>

How can a non-admin see all system catalog data?

I have given the following SQL to database user who with db_SecurityAdmin & db_AccessAdmin database roles. He doesn't see any more than his data when he runs it. I am an sa on the database and see all of the data. What security does he need in order to pull all data as a non-sa or is it possible for a user other than sa to see it all?

The other idea: If this SQL was placed in a stored procedure - would a non-sa be able to pull all of the data from it? Is there a way for them to execute the proc as sa?

SELECT sys.sql_logins.name as Login_name, sys.database_principals.name as Principal_name , sys.database_principals.type_desc , database_principals1 .name AS role_name

FROM sys.database_principals

INNER JOIN

sys.sql_logins on sys.database_principals.sid = sys.sql_logins.sid

INNER JOIN

sys.database_role_members ON sys.database_principals.principal_id = sys.database_role_members.member_principal_id

INNER JOIN

sys.database_principals AS database_principals1 ON sys.database_role_members .role_principal_id = database_principals1.principal_id

Thanks for your help!

You have this user accessing restricted tables.

You could try adding the user to the securityadmin role. If that doesn't work for you, you could create the stored procedure with EXECUTE AS permissions, and then GRANT the user permission to EXECUTE the stored procedure

|||

These are system catalogs - views not tables.

This is what Online books suggested for use as the system tables could change in future releases.

If this is not the correct source for this information, where should it be retrieved from?

|||And these 'views' are accessing restricted system tables.|||

If I place the SQL in a stored procedure,

what do I need to add to ensure that the user can execute it as SA.

I have tried the EXECUTE as 'sa' statement and I must be missing something because it doesn't work.

THANKS!

|||

You don't have to do anything.

Anyone placed in the [sysadmin] Role (sa), can do anything in the server, including, executing any stored procedures. The [sysadmin] Role totally controls the server and all databases on the server.

You might wish to read up on 'Roles' in Books Online.

|||

Even the "public" role as select permission on the catalog views. In 2005, though, this isn't enough, and accounts must be granted "view definition" permissions. For example, this statement grants permission to the "public" role to see all system metadata:

use master; grant view any definition to public

See this for more info:

http://msdn2.microsoft.com/en-us/library/ms175808.aspx

Ron Rice

How can a non-admin see all system catalog data?

I have given the following SQL to database user who with db_SecurityAdmin & db_AccessAdmin database roles. He doesn't see any more than his data when he runs it. I am an sa on the database and see all of the data. What security does he need in order to pull all data as a non-sa or is it possible for a user other than sa to see it all?

The other idea: If this SQL was placed in a stored procedure - would a non-sa be able to pull all of the data from it? Is there a way for them to execute the proc as sa?

SELECT sys.sql_logins.name as Login_name, sys.database_principals.name as Principal_name , sys.database_principals.type_desc , database_principals1 .name AS role_name

FROM sys.database_principals

INNER JOIN

sys.sql_logins on sys.database_principals.sid = sys.sql_logins.sid

INNER JOIN

sys.database_role_members ON sys.database_principals.principal_id = sys.database_role_members.member_principal_id

INNER JOIN

sys.database_principals AS database_principals1 ON sys.database_role_members .role_principal_id = database_principals1.principal_id

Thanks for your help!

You have this user accessing restricted tables.

You could try adding the user to the securityadmin role. If that doesn't work for you, you could create the stored procedure with EXECUTE AS permissions, and then GRANT the user permission to EXECUTE the stored procedure

|||

These are system catalogs - views not tables.

This is what Online books suggested for use as the system tables could change in future releases.

If this is not the correct source for this information, where should it be retrieved from?

|||And these 'views' are accessing restricted system tables.|||

If I place the SQL in a stored procedure,

what do I need to add to ensure that the user can execute it as SA.

I have tried the EXECUTE as 'sa' statement and I must be missing something because it doesn't work.

THANKS!

|||

You don't have to do anything.

Anyone placed in the [sysadmin] Role (sa), can do anything in the server, including, executing any stored procedures. The [sysadmin] Role totally controls the server and all databases on the server.

You might wish to read up on 'Roles' in Books Online.

|||

Even the "public" role as select permission on the catalog views. In 2005, though, this isn't enough, and accounts must be granted "view definition" permissions. For example, this statement grants permission to the "public" role to see all system metadata:

use master; grant view any definition to public

See this for more info:

http://msdn2.microsoft.com/en-us/library/ms175808.aspx

Ron Rice

How allow normal users using profiler

Hi guys,
Is there any way to allow a local users using Profiler without SQL System
Administrator Server Roles?
If yes, how can I do it?
Many Thanks
FrancescoIn 2005: yes (GRANT ALTER TRACE).
In 2000: no.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"RizFra" <RizFra@.discussions.microsoft.com> wrote in message
news:F651A849-72B2-456C-9876-7D5929142D1A@.microsoft.com...
> Hi guys,
> Is there any way to allow a local users using Profiler without SQL System
> Administrator Server Roles?
> If yes, how can I do it?
> Many Thanks
> Francesco|||Thanks Tibor!
"Tibor Karaszi" wrote:
> In 2005: yes (GRANT ALTER TRACE).
> In 2000: no.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "RizFra" <RizFra@.discussions.microsoft.com> wrote in message
> news:F651A849-72B2-456C-9876-7D5929142D1A@.microsoft.com...
> > Hi guys,
> > Is there any way to allow a local users using Profiler without SQL System
> > Administrator Server Roles?
> > If yes, how can I do it?
> >
> > Many Thanks
> > Francesco
>|||This is a way to run Profiler w/o direct SA privileges.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47384
- ray|||Thanks Ray
"raybouk" wrote:
> This is a way to run Profiler w/o direct SA privileges.
> http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47384
> - ray
>

Hot vs. warm standby

We have neither log shipping nor database mirroring on our system, so the
only way I could experiment with a standby database was to restore a backup
to a new database, using the standby option. Is the database I created a hot
or a warm standby? Can both kinds be used as a read-only data source for
queries or reports?I'd call your implementation a warm standby.
Yes, you can query such a database restored using STANDBY.
As for LS and M:
For LS, you can query the db is you restore using STANDBY, but you have to kick out the users for
each new log restore. Not very practical.
For mirroring, you can create a database snapshot of the mirrored database and query that snapshot.
I do recommend that you evaluate your HS requirements and your scalability requirements separately
and then decide which technology is best suited for each (HA and scalability). It might happen that
the same technology is good for both, but it isn't uncommon to use different technologies for the
two.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:6F37569F-1767-4F58-B0AB-90EBB83620AE@.microsoft.com...
> We have neither log shipping nor database mirroring on our system, so the
> only way I could experiment with a standby database was to restore a backup
> to a new database, using the standby option. Is the database I created a hot
> or a warm standby? Can both kinds be used as a read-only data source for
> queries or reports?
>|||Generally:
They call "Hot standby" when there is automatic failover in the high
availbility system. (Like SQL Server Failover Clustering or Database
Mirroring with High Availibility mode)
"Warm standby" when you can failover manually. (Log Shipping and Database
Mirroring's High Performance and Protection modes etc.)
Attach\detach, backup\restore kind of stuff is called "Cold standby"
Also I wanted to add to Tibor's message that, you'll need Enterprise Edition
of SQL Server 2005 to be able to use Database Snapshot in your environment.
--
Ekrem Ã?nsoy
http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
MCBDA, MCITP:DBA, MCSD.Net, MCSE, MCBMSP, MCT
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:6F37569F-1767-4F58-B0AB-90EBB83620AE@.microsoft.com...
> We have neither log shipping nor database mirroring on our system, so the
> only way I could experiment with a standby database was to restore a
> backup
> to a new database, using the standby option. Is the database I created a
> hot
> or a warm standby? Can both kinds be used as a read-only data source for
> queries or reports?
>

Hot opening for IT Manager(System Admin) in World Class Product Development

Hi
Allow me to take this opportunity to introduce to you our "TechUnified
Consulting" has been adjudged as the "Best Emerging Company" for the
year 2007.We are a premier HR consulting based in Bangalore, currently
working with a large number of IT companies and helping them in
meeting their hiring requirements.
TechUnified Consulting won this award at the "Recruitment excellence
Awards 2007",
Currently we have an Urgent requirement for some of our US based and
Product Development clients in Bangalore.
Kindly find below the world class organization for the position of IT
Manager where you can make your future bright.
..
5. Search Engine Start up
Location : Bangalore.
Skill Sets: UNIX, Linux, Scripting [Shell/Perl/Python],
Networking,
Web Hosting
Experience : 5.5 yrs - 8.5 Yrs
Kindly send your updated profile to vijay.k@.tuconsulting.com along
with the following details:
Current CTC:
Expected CTC:
Notice Period :
Also refer your friends or colleagues who are looking for a change, as
we got more openings.
Expecting your early reply to proceed further.
Regards,
Vijay Kumar
Talent Acquisition Executive
vijay.k@.tuconsulting.com
Is this an opportunity for US professionals to go half way around
the world to work for a US company at a quarter of the rate
they might pay here?

Hot opening for IT Manager(System Admin) in World Class Product Development

Hi
Allow me to take this opportunity to introduce to you our "TechUnified
Consulting" has been adjudged as the "Best Emerging Company" for the
year 2007.We are a premier HR consulting based in Bangalore, currently
working with a large number of IT companies and helping them in
meeting their hiring requirements.
TechUnified Consulting won this award at the "Recruitment excellence
Awards 2007",
Currently we have an Urgent requirement for some of our US based and
Product Development clients in Bangalore.
Kindly find below the world class organization for the position of IT
Manager where you can make your future bright.
.
5. Search Engine Start up
Location : Bangalore.
Skill Sets : UNIX, Linux, Scripting [Shell/Perl/Python],
Networking,
Web Hosting
Experience : 5.5 yrs - 8.5 Yrs
Kindly send your updated profile to vijay.k@.tuconsulting.com along
with the following details:
Current CTC:
Expected CTC:
Notice Period :
Also refer your friends or colleagues who are looking for a change, as
we got more openings.
Expecting your early reply to proceed further.
Regards,
Vijay Kumar
Talent Acquisition Executive
vijay.k@.tuconsulting.comIs this an opportunity for US professionals to go half way around
the world to work for a US company at a quarter of the rate
they might pay here?

Hot opening for IT Manager(System Admin) in World Class Product Development

Hi
Allow me to take this opportunity to introduce to you our "TechUnified
Consulting" has been adjudged as the "Best Emerging Company" for the
year 2007.We are a premier HR consulting based in Bangalore, currently
working with a large number of IT companies and helping them in
meeting their hiring requirements.
TechUnified Consulting won this award at the "Recruitment excellence
Awards 2007",
Currently we have an Urgent requirement for some of our US based and
Product Development clients in Bangalore.
Kindly find below the world class organization for the position of IT
Manager where you can make your future bright.
.
5. Search Engine Start up
Location : Bangalore.
Skill Sets : UNIX, Linux, Scripting [Shell/Perl/Python],
Networking,
Web Hosting
Experience : 5.5 yrs - 8.5 Yrs
Kindly send your updated profile to vijay.k@.tuconsulting.com along
with the following details:
Current CTC:
Expected CTC:
Notice Period :
Also refer your friends or colleagues who are looking for a change, as
we got more openings.
Expecting your early reply to proceed further.
Regards,
Vijay Kumar
Talent Acquisition Executive
vijay.k@.tuconsulting.comIs this an opportunity for US professionals to go half way around
the world to work for a US company at a quarter of the rate
they might pay here?