Bind to multiple tables from stored procedure

I know a sql stored procedure can return >1 tables. How can I use .Net 2.0 to read these tables one at a time, for example the first one could iterate Forum entries and the second one all internal links used in these forums... The idea is to use fewer backtrips to the sql server?

Isthis article of any use?|||no fortunately not :(

Binary Data on Multiple Servers

I'm using EncryptByKey to encrypt data in my SS2005 database. Since our server is really slow to access from home to work on, I used the Database Publishing Wizard and installed the db to work on at home. Then I created the certificate and symmetric key in my home db.

When I pull info using the DecryptByKey on our database at work on Windows 2003 Server, no problem, the data is decrypted. However, the same data does not decrypt at home on my Windows XP computer. I'm using TripleDes on both machines for the symmetric key (AES won't work on XP).

--To create my cert and key:

USE My_DB;
CREATE CERTIFICATE MyCert
ENCRYPTION BY PASSWORD = 'some password'
WITH SUBJECT = My Data',
START_DATE = '01/01/2007',
EXPIRY_DATE = '01/01/2099';
GO

CREATE SYMMETRIC KEY MyKey WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE MyCert;
GO

To encrypt:

OPEN SYMMETRIC KEY MyKey

DECRYPTION BY CERTIFICATE MyCert

WITH PASSWORD = 'same password as above';

Insert my record, use scope_identity to return primary key into @.CustomerID.

INSERT INTO [Customers] (EncryptByKey(Key_GUID('MyKey'), @.DataToEncrypt, 1, CONVERT( varbinary, @.CustomerID)))

CLOSE SYMMETRIC KEY MyKey

To decrypt:

SELECT CONVERT(varchar(3925), DecryptByKey(EncryptedField, 1, CONVERT( varbinary, @.CustomerID))) as PlainTextData

FROM Customers

WHERE (CustomerID= @.CustomerID)

Everything works fine when I run the decrypt query on the database on our work server. But I'm not getting decrypted data at home. Is the symmetric key or certificate machine specific? If so, that will cause a huge problem when we deploy to a production server.

Thanks in advance for your help!

Did you up the database master key on the server and restore it to your home machine? If not, then the data will not decrypt on your home machine. The database master key is used to encrypt the symmetric key which would then be used to decrypt your data. If you create the "same" symmetric key, but use a different database master key, then the identity will be different which will prevent decryption. This is by design, because it ensures that someone can fake security credentials and gain access to your data.|||

That's one thing I didn't do! Thanks Michael. That's got to be it.

Thank you,

Richard

|||

You generated different keys. Have a look at http://blogs.msdn.com/lcris/archive/2006/07/06/658364.aspx for how you can regenerate the same key on different machines.

Thanks
Laurentiu

|||

Laurentiu, great article! I didn't realize the Key_Source and Identity_Value were essential to make the key work in multiple databases. I thought as long as the algorithm and name were identical everything would decrypt properly. Once I created a new key like you mentioned in your article, the decryption worked great across both databases. Thanks again!

Richard

sql

Best way to stop Transactional Replication

Dear All,
Can anyone point me to a resource for the best way to disable transactional
replication for one database where there are multiple databases on a server
being replicated.
Thanks
Peter
Drop the publications. To do this expand your database, expand the
publications folder and right click on each one and select delete.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Peter Nolan" <PeterNolan@.discussions.microsoft.com> wrote in message
news:E6A1ACD5-D9A0-4091-ABDE-0E677A132485@.microsoft.com...
> Dear All,
> Can anyone point me to a resource for the best way to disable
transactional
> replication for one database where there are multiple databases on a
server
> being replicated.
> Thanks
> Peter
|||Thanks Hilary,
We have done that in the past, however the last time we did it we ran into a
problem. Although the publication had been deleted using the method you said,
when we went to modify the table stucture of the publication database we
recieved the 'Cannot modify schema as its used in replication error'
Finally I had to create a new database, copy the data over, then apply the
changes. We have another release this Thursday, is there anything you can
sugest ?
Thanks for your time
Peter
"Hilary Cotter" wrote:

> Drop the publications. To do this expand your database, expand the
> publications folder and right click on each one and select delete.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Peter Nolan" <PeterNolan@.discussions.microsoft.com> wrote in message
> news:E6A1ACD5-D9A0-4091-ABDE-0E677A132485@.microsoft.com...
> transactional
> server
>
>
|||You could use sp_MSunmarkreplinfo to allow modifications
after dropping the publication.
HTH,
Paul Ibison, SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul,
I will try that if things become pear shaped.
Peter
"Paul Ibison" wrote:

> You could use sp_MSunmarkreplinfo to allow modifications
> after dropping the publication.
> HTH,
> Paul Ibison, SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>

Best way to prevent multiple notifications for an event

I am setting up a notification system using the SQL Event Provider, and I have a question about preventing multiple notifications for a single event. The events are provided from a SQL select, and the notification emails are sent once a day. How does NS decide if it's already handled an event? If I change the email frequency, do I need to add a check in my SQL select, or does NS handle this?

Thanks
Erick

Chronicles tables can be used to prevent duplicate notifications. Check out ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlntsv9/html/17e2debd-485f-4962-b974-6be6597983f9.htm for more information.

HTH...

Best way to make a report multi-cultural

We wanted to add a language parameter to a report and allow the user to
output the report to multiple languages. We did this using three
techniques:
Static resource strings:
We created a custom assembly with resource files. We have one property and
one method. Each report has a textbox as it's first item that passes the
report parameter and sets the culture property. Each textbox then uses the
method and passes in a stringID. We configured the component to use an
instance in the report. I believe this will hurt scalability, to what
degree, uncertain, and will need to test. Looking into ways to set a
property on the report and use that instead so that the component method
could be static and shared across all report instances.
Strings from queries:
We used a Translation web service found on CodeProject to access BabelFish
and perform translations. We plan on using dual columns in the db, but for
current proof of concept this seemed like an easy approach. We may plan on
using this in the future for text fields input from the user where they do
not want to translate. We are not sure on this item yet. Either we add a
drop down and they can enter the text in multiple languages, we translate at
time of data input, we translate at time of report generation.
Strings from queries (incomplete):
We need to modify the source data to accomodate multiple languages, and add
support to our stored procedures for this. This will eliminate or greatly
reduce the dependency on BabelFish which will speed up the report generation
and scale.
I'd appreciate feedback, or what others have done to accomplish the same
feature.Rather than use a hidden textbox to set the property, used the following:
<Code>Protected Overrides Sub OnInit()
cust.SetLanguage(Report.Parameters!Language.Value)
End Sub</Code>
"Steve Munson" <smunson@.clearwire.net> wrote in message
news:eBlPycWFGHA.3984@.TK2MSFTNGP14.phx.gbl...
> We wanted to add a language parameter to a report and allow the user to
> output the report to multiple languages. We did this using three
> techniques:
> Static resource strings:
> We created a custom assembly with resource files. We have one property
> and one method. Each report has a textbox as it's first item that passes
> the report parameter and sets the culture property. Each textbox then
> uses the method and passes in a stringID. We configured the component to
> use an instance in the report. I believe this will hurt scalability, to
> what degree, uncertain, and will need to test. Looking into ways to set a
> property on the report and use that instead so that the component method
> could be static and shared across all report instances.
> Strings from queries:
> We used a Translation web service found on CodeProject to access BabelFish
> and perform translations. We plan on using dual columns in the db, but
> for current proof of concept this seemed like an easy approach. We may
> plan on using this in the future for text fields input from the user where
> they do not want to translate. We are not sure on this item yet. Either
> we add a drop down and they can enter the text in multiple languages, we
> translate at time of data input, we translate at time of report
> generation.
> Strings from queries (incomplete):
> We need to modify the source data to accomodate multiple languages, and
> add support to our stored procedures for this. This will eliminate or
> greatly reduce the dependency on BabelFish which will speed up the report
> generation and scale.
> I'd appreciate feedback, or what others have done to accomplish the same
> feature.
>|||I am not understanding how you did this exactly .. I am trying to get this
going too. I added custom code which is of course NOT the way to go. I am
trying to change over to a class. I am not following this that you wrote. I
have never set up resource files and I am very new to that. I have written
custom code for MRS and used that. But I need something that can be shared
across all report instances as you say and the method you suggest will work.
I just need the steps to create the resource files and class. Thanks.
"Steve Munson" wrote:
> Rather than use a hidden textbox to set the property, used the following:
> <Code>Protected Overrides Sub OnInit()
> cust.SetLanguage(Report.Parameters!Language.Value)
> End Sub</Code>
>
> "Steve Munson" <smunson@.clearwire.net> wrote in message
> news:eBlPycWFGHA.3984@.TK2MSFTNGP14.phx.gbl...
> > We wanted to add a language parameter to a report and allow the user to
> > output the report to multiple languages. We did this using three
> > techniques:
> >
> > Static resource strings:
> > We created a custom assembly with resource files. We have one property
> > and one method. Each report has a textbox as it's first item that passes
> > the report parameter and sets the culture property. Each textbox then
> > uses the method and passes in a stringID. We configured the component to
> > use an instance in the report. I believe this will hurt scalability, to
> > what degree, uncertain, and will need to test. Looking into ways to set a
> > property on the report and use that instead so that the component method
> > could be static and shared across all report instances.
> >
> > Strings from queries:
> > We used a Translation web service found on CodeProject to access BabelFish
> > and perform translations. We plan on using dual columns in the db, but
> > for current proof of concept this seemed like an easy approach. We may
> > plan on using this in the future for text fields input from the user where
> > they do not want to translate. We are not sure on this item yet. Either
> > we add a drop down and they can enter the text in multiple languages, we
> > translate at time of data input, we translate at time of report
> > generation.
> >
> > Strings from queries (incomplete):
> > We need to modify the source data to accomodate multiple languages, and
> > add support to our stored procedures for this. This will eliminate or
> > greatly reduce the dependency on BabelFish which will speed up the report
> > generation and scale.
> >
> > I'd appreciate feedback, or what others have done to accomplish the same
> > feature.
> >
>
>|||I have a set of power point slides and a sample project. In the sample I
add Spanish to one of the RS sample reports.
"MJT" <MJT@.discussions.microsoft.com> wrote in message
news:4A51D2B7-4FBF-4D32-8CD0-34C598DBA7DE@.microsoft.com...
>I am not understanding how you did this exactly .. I am trying to get this
> going too. I added custom code which is of course NOT the way to go. I
> am
> trying to change over to a class. I am not following this that you wrote.
> I
> have never set up resource files and I am very new to that. I have
> written
> custom code for MRS and used that. But I need something that can be
> shared
> across all report instances as you say and the method you suggest will
> work.
> I just need the steps to create the resource files and class. Thanks.
> "Steve Munson" wrote:
>> Rather than use a hidden textbox to set the property, used the following:
>> <Code>Protected Overrides Sub OnInit()
>> cust.SetLanguage(Report.Parameters!Language.Value)
>> End Sub</Code>
>>
>> "Steve Munson" <smunson@.clearwire.net> wrote in message
>> news:eBlPycWFGHA.3984@.TK2MSFTNGP14.phx.gbl...
>> > We wanted to add a language parameter to a report and allow the user to
>> > output the report to multiple languages. We did this using three
>> > techniques:
>> >
>> > Static resource strings:
>> > We created a custom assembly with resource files. We have one property
>> > and one method. Each report has a textbox as it's first item that
>> > passes
>> > the report parameter and sets the culture property. Each textbox then
>> > uses the method and passes in a stringID. We configured the component
>> > to
>> > use an instance in the report. I believe this will hurt scalability,
>> > to
>> > what degree, uncertain, and will need to test. Looking into ways to
>> > set a
>> > property on the report and use that instead so that the component
>> > method
>> > could be static and shared across all report instances.
>> >
>> > Strings from queries:
>> > We used a Translation web service found on CodeProject to access
>> > BabelFish
>> > and perform translations. We plan on using dual columns in the db, but
>> > for current proof of concept this seemed like an easy approach. We may
>> > plan on using this in the future for text fields input from the user
>> > where
>> > they do not want to translate. We are not sure on this item yet.
>> > Either
>> > we add a drop down and they can enter the text in multiple languages,
>> > we
>> > translate at time of data input, we translate at time of report
>> > generation.
>> >
>> > Strings from queries (incomplete):
>> > We need to modify the source data to accomodate multiple languages, and
>> > add support to our stored procedures for this. This will eliminate or
>> > greatly reduce the dependency on BabelFish which will speed up the
>> > report
>> > generation and scale.
>> >
>> > I'd appreciate feedback, or what others have done to accomplish the
>> > same
>> > feature.
>> >
>>|||The attachments for source code are too large (200 kb), I'll try and figure
out where I can post it.
"MJT" <MJT@.discussions.microsoft.com> wrote in message
news:4A51D2B7-4FBF-4D32-8CD0-34C598DBA7DE@.microsoft.com...
>I am not understanding how you did this exactly .. I am trying to get this
> going too. I added custom code which is of course NOT the way to go. I
> am
> trying to change over to a class. I am not following this that you wrote.
> I
> have never set up resource files and I am very new to that. I have
> written
> custom code for MRS and used that. But I need something that can be
> shared
> across all report instances as you say and the method you suggest will
> work.
> I just need the steps to create the resource files and class. Thanks.
> "Steve Munson" wrote:
>> Rather than use a hidden textbox to set the property, used the following:
>> <Code>Protected Overrides Sub OnInit()
>> cust.SetLanguage(Report.Parameters!Language.Value)
>> End Sub</Code>
>>
>> "Steve Munson" <smunson@.clearwire.net> wrote in message
>> news:eBlPycWFGHA.3984@.TK2MSFTNGP14.phx.gbl...
>> > We wanted to add a language parameter to a report and allow the user to
>> > output the report to multiple languages. We did this using three
>> > techniques:
>> >
>> > Static resource strings:
>> > We created a custom assembly with resource files. We have one property
>> > and one method. Each report has a textbox as it's first item that
>> > passes
>> > the report parameter and sets the culture property. Each textbox then
>> > uses the method and passes in a stringID. We configured the component
>> > to
>> > use an instance in the report. I believe this will hurt scalability,
>> > to
>> > what degree, uncertain, and will need to test. Looking into ways to
>> > set a
>> > property on the report and use that instead so that the component
>> > method
>> > could be static and shared across all report instances.
>> >
>> > Strings from queries:
>> > We used a Translation web service found on CodeProject to access
>> > BabelFish
>> > and perform translations. We plan on using dual columns in the db, but
>> > for current proof of concept this seemed like an easy approach. We may
>> > plan on using this in the future for text fields input from the user
>> > where
>> > they do not want to translate. We are not sure on this item yet.
>> > Either
>> > we add a drop down and they can enter the text in multiple languages,
>> > we
>> > translate at time of data input, we translate at time of report
>> > generation.
>> >
>> > Strings from queries (incomplete):
>> > We need to modify the source data to accomodate multiple languages, and
>> > add support to our stored procedures for this. This will eliminate or
>> > greatly reduce the dependency on BabelFish which will speed up the
>> > report
>> > generation and scale.
>> >
>> > I'd appreciate feedback, or what others have done to accomplish the
>> > same
>> > feature.
>> >
>>|||If you are able to post the example you have ... maybe is someone's blog ...
then please post the location. Thanks.
"Steve MunLeeuw" wrote:
> The attachments for source code are too large (200 kb), I'll try and figure
> out where I can post it.
> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> news:4A51D2B7-4FBF-4D32-8CD0-34C598DBA7DE@.microsoft.com...
> >I am not understanding how you did this exactly .. I am trying to get this
> > going too. I added custom code which is of course NOT the way to go. I
> > am
> > trying to change over to a class. I am not following this that you wrote.
> > I
> > have never set up resource files and I am very new to that. I have
> > written
> > custom code for MRS and used that. But I need something that can be
> > shared
> > across all report instances as you say and the method you suggest will
> > work.
> > I just need the steps to create the resource files and class. Thanks.
> >
> > "Steve Munson" wrote:
> >
> >> Rather than use a hidden textbox to set the property, used the following:
> >> <Code>Protected Overrides Sub OnInit()
> >>
> >> cust.SetLanguage(Report.Parameters!Language.Value)
> >>
> >> End Sub</Code>
> >>
> >>
> >> "Steve Munson" <smunson@.clearwire.net> wrote in message
> >> news:eBlPycWFGHA.3984@.TK2MSFTNGP14.phx.gbl...
> >> > We wanted to add a language parameter to a report and allow the user to
> >> > output the report to multiple languages. We did this using three
> >> > techniques:
> >> >
> >> > Static resource strings:
> >> > We created a custom assembly with resource files. We have one property
> >> > and one method. Each report has a textbox as it's first item that
> >> > passes
> >> > the report parameter and sets the culture property. Each textbox then
> >> > uses the method and passes in a stringID. We configured the component
> >> > to
> >> > use an instance in the report. I believe this will hurt scalability,
> >> > to
> >> > what degree, uncertain, and will need to test. Looking into ways to
> >> > set a
> >> > property on the report and use that instead so that the component
> >> > method
> >> > could be static and shared across all report instances.
> >> >
> >> > Strings from queries:
> >> > We used a Translation web service found on CodeProject to access
> >> > BabelFish
> >> > and perform translations. We plan on using dual columns in the db, but
> >> > for current proof of concept this seemed like an easy approach. We may
> >> > plan on using this in the future for text fields input from the user
> >> > where
> >> > they do not want to translate. We are not sure on this item yet.
> >> > Either
> >> > we add a drop down and they can enter the text in multiple languages,
> >> > we
> >> > translate at time of data input, we translate at time of report
> >> > generation.
> >> >
> >> > Strings from queries (incomplete):
> >> > We need to modify the source data to accomodate multiple languages, and
> >> > add support to our stored procedures for this. This will eliminate or
> >> > greatly reduce the dependency on BabelFish which will speed up the
> >> > report
> >> > generation and scale.
> >> >
> >> > I'd appreciate feedback, or what others have done to accomplish the
> >> > same
> >> > feature.
> >> >
> >>
> >>
> >>
>
>|||Sorry for the delay, and thanks for the info on the other post with respect
to the custom assembly, preview tab.
http://smunson.jot.com/WikiHome/Reporting+Services
"MJT" <MJT@.discussions.microsoft.com> wrote in message
news:0E5DE435-1C2A-4AC8-88A7-6EB22EC20D2C@.microsoft.com...
> If you are able to post the example you have ... maybe is someone's blog
> ...
> then please post the location. Thanks.
> "Steve MunLeeuw" wrote:
>> The attachments for source code are too large (200 kb), I'll try and
>> figure
>> out where I can post it.
>> "MJT" <MJT@.discussions.microsoft.com> wrote in message
>> news:4A51D2B7-4FBF-4D32-8CD0-34C598DBA7DE@.microsoft.com...
>> >I am not understanding how you did this exactly .. I am trying to get
>> >this
>> > going too. I added custom code which is of course NOT the way to go.
>> > I
>> > am
>> > trying to change over to a class. I am not following this that you
>> > wrote.
>> > I
>> > have never set up resource files and I am very new to that. I have
>> > written
>> > custom code for MRS and used that. But I need something that can be
>> > shared
>> > across all report instances as you say and the method you suggest will
>> > work.
>> > I just need the steps to create the resource files and class. Thanks.
>> >
>> > "Steve Munson" wrote:
>> >
>> >> Rather than use a hidden textbox to set the property, used the
>> >> following:
>> >> <Code>Protected Overrides Sub OnInit()
>> >>
>> >> cust.SetLanguage(Report.Parameters!Language.Value)
>> >>
>> >> End Sub</Code>
>> >>
>> >>
>> >> "Steve Munson" <smunson@.clearwire.net> wrote in message
>> >> news:eBlPycWFGHA.3984@.TK2MSFTNGP14.phx.gbl...
>> >> > We wanted to add a language parameter to a report and allow the user
>> >> > to
>> >> > output the report to multiple languages. We did this using three
>> >> > techniques:
>> >> >
>> >> > Static resource strings:
>> >> > We created a custom assembly with resource files. We have one
>> >> > property
>> >> > and one method. Each report has a textbox as it's first item that
>> >> > passes
>> >> > the report parameter and sets the culture property. Each textbox
>> >> > then
>> >> > uses the method and passes in a stringID. We configured the
>> >> > component
>> >> > to
>> >> > use an instance in the report. I believe this will hurt
>> >> > scalability,
>> >> > to
>> >> > what degree, uncertain, and will need to test. Looking into ways to
>> >> > set a
>> >> > property on the report and use that instead so that the component
>> >> > method
>> >> > could be static and shared across all report instances.
>> >> >
>> >> > Strings from queries:
>> >> > We used a Translation web service found on CodeProject to access
>> >> > BabelFish
>> >> > and perform translations. We plan on using dual columns in the db,
>> >> > but
>> >> > for current proof of concept this seemed like an easy approach. We
>> >> > may
>> >> > plan on using this in the future for text fields input from the user
>> >> > where
>> >> > they do not want to translate. We are not sure on this item yet.
>> >> > Either
>> >> > we add a drop down and they can enter the text in multiple
>> >> > languages,
>> >> > we
>> >> > translate at time of data input, we translate at time of report
>> >> > generation.
>> >> >
>> >> > Strings from queries (incomplete):
>> >> > We need to modify the source data to accomodate multiple languages,
>> >> > and
>> >> > add support to our stored procedures for this. This will eliminate
>> >> > or
>> >> > greatly reduce the dependency on BabelFish which will speed up the
>> >> > report
>> >> > generation and scale.
>> >> >
>> >> > I'd appreciate feedback, or what others have done to accomplish the
>> >> > same
>> >> > feature.
>> >> >
>> >>
>> >>
>> >>
>>|||Thanks Steve for the link, however I tried to access it and get an error
Insufficient Privileges.
"Steve MunLeeuw" wrote:
> Sorry for the delay, and thanks for the info on the other post with respect
> to the custom assembly, preview tab.
> http://smunson.jot.com/WikiHome/Reporting+Services
>
> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> news:0E5DE435-1C2A-4AC8-88A7-6EB22EC20D2C@.microsoft.com...
> > If you are able to post the example you have ... maybe is someone's blog
> > ...
> > then please post the location. Thanks.
> >
> > "Steve MunLeeuw" wrote:
> >
> >> The attachments for source code are too large (200 kb), I'll try and
> >> figure
> >> out where I can post it.
> >>
> >> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> >> news:4A51D2B7-4FBF-4D32-8CD0-34C598DBA7DE@.microsoft.com...
> >> >I am not understanding how you did this exactly .. I am trying to get
> >> >this
> >> > going too. I added custom code which is of course NOT the way to go.
> >> > I
> >> > am
> >> > trying to change over to a class. I am not following this that you
> >> > wrote.
> >> > I
> >> > have never set up resource files and I am very new to that. I have
> >> > written
> >> > custom code for MRS and used that. But I need something that can be
> >> > shared
> >> > across all report instances as you say and the method you suggest will
> >> > work.
> >> > I just need the steps to create the resource files and class. Thanks.
> >> >
> >> > "Steve Munson" wrote:
> >> >
> >> >> Rather than use a hidden textbox to set the property, used the
> >> >> following:
> >> >> <Code>Protected Overrides Sub OnInit()
> >> >>
> >> >> cust.SetLanguage(Report.Parameters!Language.Value)
> >> >>
> >> >> End Sub</Code>
> >> >>
> >> >>
> >> >> "Steve Munson" <smunson@.clearwire.net> wrote in message
> >> >> news:eBlPycWFGHA.3984@.TK2MSFTNGP14.phx.gbl...
> >> >> > We wanted to add a language parameter to a report and allow the user
> >> >> > to
> >> >> > output the report to multiple languages. We did this using three
> >> >> > techniques:
> >> >> >
> >> >> > Static resource strings:
> >> >> > We created a custom assembly with resource files. We have one
> >> >> > property
> >> >> > and one method. Each report has a textbox as it's first item that
> >> >> > passes
> >> >> > the report parameter and sets the culture property. Each textbox
> >> >> > then
> >> >> > uses the method and passes in a stringID. We configured the
> >> >> > component
> >> >> > to
> >> >> > use an instance in the report. I believe this will hurt
> >> >> > scalability,
> >> >> > to
> >> >> > what degree, uncertain, and will need to test. Looking into ways to
> >> >> > set a
> >> >> > property on the report and use that instead so that the component
> >> >> > method
> >> >> > could be static and shared across all report instances.
> >> >> >
> >> >> > Strings from queries:
> >> >> > We used a Translation web service found on CodeProject to access
> >> >> > BabelFish
> >> >> > and perform translations. We plan on using dual columns in the db,
> >> >> > but
> >> >> > for current proof of concept this seemed like an easy approach. We
> >> >> > may
> >> >> > plan on using this in the future for text fields input from the user
> >> >> > where
> >> >> > they do not want to translate. We are not sure on this item yet.
> >> >> > Either
> >> >> > we add a drop down and they can enter the text in multiple
> >> >> > languages,
> >> >> > we
> >> >> > translate at time of data input, we translate at time of report
> >> >> > generation.
> >> >> >
> >> >> > Strings from queries (incomplete):
> >> >> > We need to modify the source data to accomodate multiple languages,
> >> >> > and
> >> >> > add support to our stored procedures for this. This will eliminate
> >> >> > or
> >> >> > greatly reduce the dependency on BabelFish which will speed up the
> >> >> > report
> >> >> > generation and scale.
> >> >> >
> >> >> > I'd appreciate feedback, or what others have done to accomplish the
> >> >> > same
> >> >> > feature.
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||Can you try the following link, and if that doesn't work I can email them.
http://smunson.jot.com/Reporting+Services
The two files are at the bottom.
SSRS 05 multicultural parameterized reports.ppt
SSRS 05 multicultural parameterized reports.zip
Any feedback on the code or ppt is appreciated.
Steve MunLeeuw
"MJT" <MJT@.discussions.microsoft.com> wrote in message
news:C77162ED-AD12-4956-B458-31DA2EFA25B1@.microsoft.com...
> Thanks Steve for the link, however I tried to access it and get an error
> Insufficient Privileges.
>
> "Steve MunLeeuw" wrote:
>> Sorry for the delay, and thanks for the info on the other post with
>> respect
>> to the custom assembly, preview tab.
>> http://smunson.jot.com/WikiHome/Reporting+Services
>>
>> "MJT" <MJT@.discussions.microsoft.com> wrote in message
>> news:0E5DE435-1C2A-4AC8-88A7-6EB22EC20D2C@.microsoft.com...
>> > If you are able to post the example you have ... maybe is someone's
>> > blog
>> > ...
>> > then please post the location. Thanks.
>> >
>> > "Steve MunLeeuw" wrote:
>> >
>> >> The attachments for source code are too large (200 kb), I'll try and
>> >> figure
>> >> out where I can post it.
>> >>
>> >> "MJT" <MJT@.discussions.microsoft.com> wrote in message
>> >> news:4A51D2B7-4FBF-4D32-8CD0-34C598DBA7DE@.microsoft.com...
>> >> >I am not understanding how you did this exactly .. I am trying to get
>> >> >this
>> >> > going too. I added custom code which is of course NOT the way to
>> >> > go.
>> >> > I
>> >> > am
>> >> > trying to change over to a class. I am not following this that you
>> >> > wrote.
>> >> > I
>> >> > have never set up resource files and I am very new to that. I
>> >> > have
>> >> > written
>> >> > custom code for MRS and used that. But I need something that can be
>> >> > shared
>> >> > across all report instances as you say and the method you suggest
>> >> > will
>> >> > work.
>> >> > I just need the steps to create the resource files and class.
>> >> > Thanks.
>> >> >
>> >> > "Steve Munson" wrote:
>> >> >
>> >> >> Rather than use a hidden textbox to set the property, used the
>> >> >> following:
>> >> >> <Code>Protected Overrides Sub OnInit()
>> >> >>
>> >> >> cust.SetLanguage(Report.Parameters!Language.Value)
>> >> >>
>> >> >> End Sub</Code>
>> >> >>
>> >> >>
>> >> >> "Steve Munson" <smunson@.clearwire.net> wrote in message
>> >> >> news:eBlPycWFGHA.3984@.TK2MSFTNGP14.phx.gbl...
>> >> >> > We wanted to add a language parameter to a report and allow the
>> >> >> > user
>> >> >> > to
>> >> >> > output the report to multiple languages. We did this using three
>> >> >> > techniques:
>> >> >> >
>> >> >> > Static resource strings:
>> >> >> > We created a custom assembly with resource files. We have one
>> >> >> > property
>> >> >> > and one method. Each report has a textbox as it's first item
>> >> >> > that
>> >> >> > passes
>> >> >> > the report parameter and sets the culture property. Each textbox
>> >> >> > then
>> >> >> > uses the method and passes in a stringID. We configured the
>> >> >> > component
>> >> >> > to
>> >> >> > use an instance in the report. I believe this will hurt
>> >> >> > scalability,
>> >> >> > to
>> >> >> > what degree, uncertain, and will need to test. Looking into ways
>> >> >> > to
>> >> >> > set a
>> >> >> > property on the report and use that instead so that the component
>> >> >> > method
>> >> >> > could be static and shared across all report instances.
>> >> >> >
>> >> >> > Strings from queries:
>> >> >> > We used a Translation web service found on CodeProject to access
>> >> >> > BabelFish
>> >> >> > and perform translations. We plan on using dual columns in the
>> >> >> > db,
>> >> >> > but
>> >> >> > for current proof of concept this seemed like an easy approach.
>> >> >> > We
>> >> >> > may
>> >> >> > plan on using this in the future for text fields input from the
>> >> >> > user
>> >> >> > where
>> >> >> > they do not want to translate. We are not sure on this item yet.
>> >> >> > Either
>> >> >> > we add a drop down and they can enter the text in multiple
>> >> >> > languages,
>> >> >> > we
>> >> >> > translate at time of data input, we translate at time of report
>> >> >> > generation.
>> >> >> >
>> >> >> > Strings from queries (incomplete):
>> >> >> > We need to modify the source data to accomodate multiple
>> >> >> > languages,
>> >> >> > and
>> >> >> > add support to our stored procedures for this. This will
>> >> >> > eliminate
>> >> >> > or
>> >> >> > greatly reduce the dependency on BabelFish which will speed up
>> >> >> > the
>> >> >> > report
>> >> >> > generation and scale.
>> >> >> >
>> >> >> > I'd appreciate feedback, or what others have done to accomplish
>> >> >> > the
>> >> >> > same
>> >> >> > feature.
>> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>

Best way to get TOP 10 CPU intensive SQL

I run a shared hosting server where multiple people share a single SQL
server (each of them have their own data base). I need a way to monitor to
find out what % of the resources each user is taking as we don't want any
one person abusing the system. What is the best way to do this
Jay
Jay
Run SQL Server Profiler tool.
"Jay Janarthanan" <jay@.objectcube.com> wrote in message
news:e4AaSxinEHA.648@.tk2msftngp13.phx.gbl...
> I run a shared hosting server where multiple people share a single SQL
> server (each of them have their own data base). I need a way to monitor to
> find out what % of the resources each user is taking as we don't want any
> one person abusing the system. What is the best way to do this
> Jay
>
|||See if this helps:
Identifying SQL Server Perfromance Issues Using Profiler
http://vyaskn.tripod.com/analyzing_profiler_output.htm
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Jay Janarthanan" <jay@.objectcube.com> wrote in message
news:e4AaSxinEHA.648@.tk2msftngp13.phx.gbl...
> I run a shared hosting server where multiple people share a single SQL
> server (each of them have their own data base). I need a way to monitor to
> find out what % of the resources each user is taking as we don't want any
> one person abusing the system. What is the best way to do this
> Jay
>

Best way to get TOP 10 CPU intensive SQL

I run a shared hosting server where multiple people share a single SQL
server (each of them have their own data base). I need a way to monitor to
find out what % of the resources each user is taking as we don't want any
one person abusing the system. What is the best way to do this
JayJay
Run SQL Server Profiler tool.
"Jay Janarthanan" <jay@.objectcube.com> wrote in message
news:e4AaSxinEHA.648@.tk2msftngp13.phx.gbl...
> I run a shared hosting server where multiple people share a single SQL
> server (each of them have their own data base). I need a way to monitor to
> find out what % of the resources each user is taking as we don't want any
> one person abusing the system. What is the best way to do this
> Jay
>|||See if this helps:
Identifying SQL Server Perfromance Issues Using Profiler
http://vyaskn.tripod.com/analyzing_profiler_output.htm
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Jay Janarthanan" <jay@.objectcube.com> wrote in message
news:e4AaSxinEHA.648@.tk2msftngp13.phx.gbl...
> I run a shared hosting server where multiple people share a single SQL
> server (each of them have their own data base). I need a way to monitor to
> find out what % of the resources each user is taking as we don't want any
> one person abusing the system. What is the best way to do this
> Jay
>

best way to combine columns

Hi all,

I have a table with multiple rows with the same ID.

a) How do I combine all columns into one row with the same ID?

b) Is this better to do the combine in the store procedure/trigger or a sub that involked by some sort of datarepeater, datagrid controls?

Since I am trying to brush up my sql. I appreciate any examples on top of conceptual solution.

Thanks

I take that my question is not so clear so here is the example of what I wanted to do:

I have a sql 2000 table like this:

ID col1 col2

7777 itemx 12/02/07 00:00:10

7777 itemy 12/02/07 10:00:00

7777 itemz 12/02/07 12:10:60

8888 itemA 12/02/07 01:01:00

888 itemB 12/02/07 02:00:00

..........................................

I like to combine all rows with the same ID together like the followings:

7777 itemx itemy itemz 12/02/07

888 itemA itemB...................

The question has 3 parts:

1) what is the best way to do this? using trigger/stored procedure or just a vb sub with somesort of datarepeater, datalist controls

2) can you show me some examples of each way if any?

3) Can crystal report do something like this?

I am open to create another table or just plain writting them out on page.

Thanks

|||

Some one mentioned to me that 'trigger' could cause some confusion in reading the question. So , please do skip the trigger part.

|||

Depending on how many records will be pulled out, you could do it at the report level or at the DB level. You could write a function that takes the ID as parameter and returns a concatenated string for items and use the function in the SELECT.

SELECT Id, dbo.fnGetItems(ID), col2...

FROM ...

and create the function with a SELECT as

SELECT @.val = ISNULL(@.val,'') + ' ' + Convert(Varchar, col2) FROM YourTable WHERE ID = @.Id

and return the @.val.

|||

Thanks ndinakar. Anyone has any other solutions/ideas or clearer/complete solution? thanks

|||

Hi tvfoto,

This depends on if this table has a primary key.

If yes, I would suggest you read everything into a DataSet. Process the data combination

within the DataSet and update it back to the server, because in the .NET code, you will

have better flexibility for the combination logic.

If the table doesn't have a primary key, as ndinakar suggested, you can use some stored

procedure to achieve this.

HTH. If this does not answer you question, please feel free to mark it as Not Answered and post your reply. Thanks!

|||

THis table does not have a primary key. The following code works, however I dont know how to seperate them out as individual columns so I can give them a proper heading. Any suggestion? Thanks

//my user function.

create functionGetItems(@.mId int)

returns varchar(1000)As

Begin

declare @.values varchar(1000)

Set @.values=''

Select @.values=@.values+','+ myItemColumnName from myTable whereID=@.mId

return @.values

End

Go

//my aspx code

<

body><formid="form1"runat="server"><div><asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:myTableConnectionString %>"SelectCommand="SELECT DISTINCT [ID],dbo.GetItems(ID) as [xyzValues] FROM [myTable] "></asp:SqlDataSource>

<

asp:RepeaterID="Repeater1"runat="server"DataSourceID="SqlDataSource1">

<HeaderTemplate><tablestyle="background:#ebebeb"><tr><tdcolspan="5"> </td></tr><trstyle="text-align:left; background:yellow"><th>ID</th><th> </th><th>item1,item2,item3,item4, item5...</h>///////header for different items here...not a good way

</tr>

</HeaderTemplate><ItemTemplate><tr><tdstyle="width:25px"><%#DataBinder.Eval(Container.DataItem,"ID")%></td>

<td> </td><td><%#DataBinder.Eval(Container.DataItem,"xzyValues")%></td>.................................

</div></form></body>

Best way for the performance

I would like to know which way is better for the performance of my
system:
Creating a huge table with more than 5 000 000 000 rows or multiple
tables with the same structure containing 5 000 000 rows?
Here the context of our application:
Our application offers the possibility to create up to 3000 projects.
Each project contains a list of tags and a list of value-timestamp for
each tag.
So, a project can contain up to 500 tags with a maximum of 5000 values
for each tag. In a hypothetical scenario where everything is at
maximun, only one table containing every value-timestamp would be huge
with more than 7 500 000 000 rows.
My first thought was to create a table containning the values for each
project.
Project
=========== Project_ID - PK
ValDataName_ID
... etc...
ValDataName
=========== ValDataName_ID - PK
TableName (represents the ValDataXXX)
Project_ID
ValDataXXX where XXX is an unique id
=========== ValDataXXX_ID - PK
Tag_ID
TagVal
TagTime
So, I would have a table (ValDataName) containing the names of each
ValData table linked to the project and a table containing my projects.
Then, I would have up to 5000 tables of type ValData.
Since my ValData tables are completely independant, any action
affecting one table does not affect the other.
So, which way would be the better approach?
Thanks.Francis B. wrote:
> I would like to know which way is better for the performance of my
> system:
> Creating a huge table with more than 5 000 000 000 rows or multiple
> tables with the same structure containing 5 000 000 rows?
> Here the context of our application:
> Our application offers the possibility to create up to 3000 projects.
> Each project contains a list of tags and a list of value-timestamp for
> each tag.
> So, a project can contain up to 500 tags with a maximum of 5000 values
> for each tag. In a hypothetical scenario where everything is at
> maximun, only one table containing every value-timestamp would be huge
> with more than 7 500 000 000 rows.
> My first thought was to create a table containning the values for each
> project.
> Project
> ===========> Project_ID - PK
> ValDataName_ID
> ... etc...
> ValDataName
> ===========> ValDataName_ID - PK
> TableName (represents the ValDataXXX)
> Project_ID
> ValDataXXX where XXX is an unique id
> ===========> ValDataXXX_ID - PK
> Tag_ID
> TagVal
> TagTime
> So, I would have a table (ValDataName) containing the names of each
> ValData table linked to the project and a table containing my projects.
> Then, I would have up to 5000 tables of type ValData.
> Since my ValData tables are completely independant, any action
> affecting one table does not affect the other.
> So, which way would be the better approach?
> Thanks.
You didn't mention one very important piece of information. What
version of SQL Server are you using? SQL Server 2005 has a lot of new
scalability features, especially table partitioning. For a major new
application I assume SQL Server 2005 will be the obvious choice for
you.
Achieving a scalable solution will depend very much on the design of
your indexing, queries, your application and your hardware
implementation. You've told us nothing of those. I can only suggest
that creating new tables in a bid to improve performance should
generally be a long way down your list of options. Usually there are
much more effective ways to optimise performance. More likely you might
choose to implement horizontal partitioning in order to make certain
admin tasks easier. How you do that depends a lot on the version and
edition you are using.
My other suggestion would be that you rethink very, very carefully
about logical design. When I see tables with names like "ValDataXXX"
and columns with names like "Tag_ID" and "TagVal" I just get an a awful
sinking feeling that this is some naive data-model-on-the-fly kind of
solution - what is sometimes euphemistically called an "EAV" model. If
I'm right then you can probably say goodbye to scalability, performance
and data integrity. Your database will have none of them.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On 19 Sep 2006 12:59:25 -0700, "Francis B."
<francisbeliveau@.gmail.com> wrote:
>I would like to know which way is better for the performance of my
>system:
>Creating a huge table with more than 5 000 000 000 rows or multiple
>tables with the same structure containing 5 000 000 rows?
>Here the context of our application:
>Our application offers the possibility to create up to 3000 projects.
>Each project contains a list of tags and a list of value-timestamp for
>each tag.
No way should a project management system have 10^9 rows.
Beyond that, I second what David has already said.
J.