Data SubSet

Is it possible to perform an SQL query on a ReportingServices DataSet... I
would like to write a statement like this:
SELECT COUNT(DISTINCT ServiceID) FROM RS_DataSet WHERE ServiceStatus = "ACTIVE"
I know it would be easy to create a new DataSet with this query... however
RS it getting this table from an application... not directly from a
database...Couldn't you just filter the dataset that you already have?
"MER78" wrote:
> Is it possible to perform an SQL query on a ReportingServices DataSet... I
> would like to write a statement like this:
> SELECT COUNT(DISTINCT ServiceID) FROM RS_DataSet WHERE ServiceStatus => "ACTIVE"
> I know it would be easy to create a new DataSet with this query... however
> RS it getting this table from an application... not directly from a
> database...|||Well that is what I was talking about... but I can't figure out how I would
do that without making invisible tables that filter and count for each
condition... the table I have displaying my DataSet is supposed to show all
the data... but counting say ACTIVE records... would be a subset of that
DataSet.
"B. Mark McKinney" wrote:
> Couldn't you just filter the dataset that you already have?|||I figured out a solution for my problem... with a little help from Google...
for anyone that might b interested...
=CountDistinct(IIF(Fields!ServiceStatus.Value = 1, Fields!ServiceID.Value,
NOTHING))
"MER78" wrote:
> Is it possible to perform an SQL query on a ReportingServices DataSet... I
> would like to write a statement like this:
> SELECT COUNT(DISTINCT ServiceID) FROM RS_DataSet WHERE ServiceStatus => "ACTIVE"
> I know it would be easy to create a new DataSet with this query... however
> RS it getting this table from an application... not directly from a
> database...

Data security

My boss would like to store sensitive information in our company SQL Server
database. At the momnt everyone has write access to all data in the
database. in addition the developers can basically do anything with the
database.
I am using Windows security and need to deny acces to the data in the table
to eeryone apart from a couple of groups. In some cases i need to deny
access to data just in one column. I know you can do this, but how reliable
is it?
I am trying to figure out where to start on this.
Should the sensitive data only live in the live database? Should we release
the database and revoke rights at tat moment in time? Presumably i could
write a script to do this.
Is there any way to have the sensitive data in a database that the
developers are developing and be comfortable that the data is secure?
Is the only really safe harbour encryption?
Also, if anyone out there knows a good book on security architecture for SQL
Server, a recommendation would be greatly appreciated.
TIA
SarahSQL Server security is reliable and it works... Regarding DEV... Some
companies treat highly sensitive data by jumbling up anything that
uniquelly identifies the individual prior to making a copy of the data for
devs.
I worked in Healthcare Development, and we all tested with copies of live
sensitive data most of the time... However there were occasions when we
would get copies of data from hospitals that came in with all identifying
data scrambled or removed.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sarah" <Sarah@.TT.com> wrote in message
news:%23mrTozL8DHA.2044@.TK2MSFTNGP10.phx.gbl...
> My boss would like to store sensitive information in our company SQL
Server
> database. At the momnt everyone has write access to all data in the
> database. in addition the developers can basically do anything with the
> database.
> I am using Windows security and need to deny acces to the data in the
table
> to eeryone apart from a couple of groups. In some cases i need to deny
> access to data just in one column. I know you can do this, but how
reliable
> is it?
> I am trying to figure out where to start on this.
> Should the sensitive data only live in the live database? Should we
release
> the database and revoke rights at tat moment in time? Presumably i could
> write a script to do this.
> Is there any way to have the sensitive data in a database that the
> developers are developing and be comfortable that the data is secure?
> Is the only really safe harbour encryption?
> Also, if anyone out there knows a good book on security architecture for
SQL
> Server, a recommendation would be greatly appreciated.
> TIA
> Sarah
>|||Why not put it in a seperate database or table, linked to your original
database, and then you have complete control on permissions, without as much
chance of making an 'error' and granting access to the wrong peeps ?
"Sarah" <Sarah@.TT.com> wrote in message
news:%23mrTozL8DHA.2044@.TK2MSFTNGP10.phx.gbl...
> My boss would like to store sensitive information in our company SQL
Server
> database. At the momnt everyone has write access to all data in the
> database. in addition the developers can basically do anything with the
> database.
> I am using Windows security and need to deny acces to the data in the
table
> to eeryone apart from a couple of groups. In some cases i need to deny
> access to data just in one column. I know you can do this, but how
reliable
> is it?
> I am trying to figure out where to start on this.
> Should the sensitive data only live in the live database? Should we
release
> the database and revoke rights at tat moment in time? Presumably i could
> write a script to do this.
> Is there any way to have the sensitive data in a database that the
> developers are developing and be comfortable that the data is secure?
> Is the only really safe harbour encryption?
> Also, if anyone out there knows a good book on security architecture for
SQL
> Server, a recommendation would be greatly appreciated.
> TIA
> Sarah
>|||look into using Stored procedures. these can be used with logins. and grant
rights.
it is a lot of work, but will give you the best protection.
Next is to use the encrypt table. I don't like that option my self.
the quickest way to isolate a DB is to user another DB to make the calls to
the stored procedures.
Note: if you SQL server, can be physically accessed by anyone, then all
these procedures are useless.
"Sarah" <Sarah@.TT.com> wrote in message
news:#mrTozL8DHA.2044@.TK2MSFTNGP10.phx.gbl...
> My boss would like to store sensitive information in our company SQL
Server
> database. At the momnt everyone has write access to all data in the
> database. in addition the developers can basically do anything with the
> database.
> I am using Windows security and need to deny acces to the data in the
table
> to eeryone apart from a couple of groups. In some cases i need to deny
> access to data just in one column. I know you can do this, but how
reliable
> is it?
> I am trying to figure out where to start on this.
> Should the sensitive data only live in the live database? Should we
release
> the database and revoke rights at tat moment in time? Presumably i could
> write a script to do this.
> Is there any way to have the sensitive data in a database that the
> developers are developing and be comfortable that the data is secure?
> Is the only really safe harbour encryption?
> Also, if anyone out there knows a good book on security architecture for
SQL
> Server, a recommendation would be greatly appreciated.
> TIA
> Sarah
>|||Use a copy of the database and run a qry on User & Company identifying
details and Swap all the identifying records around.
Did this when I pass on a production db to another developer.
Don Grover
"Sarah" <Sarah@.TT.com> wrote in message
news:%23mrTozL8DHA.2044@.TK2MSFTNGP10.phx.gbl...
> My boss would like to store sensitive information in our company SQL
Server
> database. At the momnt everyone has write access to all data in the
> database. in addition the developers can basically do anything with the
> database.
> I am using Windows security and need to deny acces to the data in the
table
> to eeryone apart from a couple of groups. In some cases i need to deny
> access to data just in one column. I know you can do this, but how
reliable
> is it?
> I am trying to figure out where to start on this.
> Should the sensitive data only live in the live database? Should we
release
> the database and revoke rights at tat moment in time? Presumably i could
> write a script to do this.
> Is there any way to have the sensitive data in a database that the
> developers are developing and be comfortable that the data is secure?
> Is the only really safe harbour encryption?
> Also, if anyone out there knows a good book on security architecture for
SQL
> Server, a recommendation would be greatly appreciated.
> TIA
> Sarah
>

Data security

My boss would like to store sensitive information in our company SQL Server
database. At the momnt everyone has write access to all data in the
database. in addition the developers can basically do anything with the
database.
I am using Windows security and need to deny acces to the data in the table
to eeryone apart from a couple of groups. In some cases i need to deny
access to data just in one column. I know you can do this, but how reliable
is it?
I am trying to figure out where to start on this.
Should the sensitive data only live in the live database? Should we release
the database and revoke rights at tat moment in time? Presumably i could
write a script to do this.
Is there any way to have the sensitive data in a database that the
developers are developing and be comfortable that the data is secure?
Is the only really safe harbour encryption?
Also, if anyone out there knows a good book on security architecture for SQL
Server, a recommendation would be greatly appreciated.
TIA
SarahSQL Server security is reliable and it works... Regarding DEV... Some
companies treat highly sensitive data by jumbling up anything that
uniquelly identifies the individual prior to making a copy of the data for
devs.
I worked in Healthcare Development, and we all tested with copies of live
sensitive data most of the time... However there were occasions when we
would get copies of data from hospitals that came in with all identifying
data scrambled or removed.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sarah" <Sarah@.TT.com> wrote in message
news:%23mrTozL8DHA.2044@.TK2MSFTNGP10.phx.gbl...
> My boss would like to store sensitive information in our company SQL
Server
> database. At the momnt everyone has write access to all data in the
> database. in addition the developers can basically do anything with the
> database.
> I am using Windows security and need to deny acces to the data in the
table
> to eeryone apart from a couple of groups. In some cases i need to deny
> access to data just in one column. I know you can do this, but how
reliable
> is it?
> I am trying to figure out where to start on this.
> Should the sensitive data only live in the live database? Should we
release
> the database and revoke rights at tat moment in time? Presumably i could
> write a script to do this.
> Is there any way to have the sensitive data in a database that the
> developers are developing and be comfortable that the data is secure?
> Is the only really safe harbour encryption?
> Also, if anyone out there knows a good book on security architecture for
SQL
> Server, a recommendation would be greatly appreciated.
> TIA
> Sarah
>|||Why not put it in a seperate database or table, linked to your original
database, and then you have complete control on permissions, without as much
chance of making an 'error' and granting access to the wrong peeps ?
"Sarah" <Sarah@.TT.com> wrote in message
news:%23mrTozL8DHA.2044@.TK2MSFTNGP10.phx.gbl...
> My boss would like to store sensitive information in our company SQL
Server
> database. At the momnt everyone has write access to all data in the
> database. in addition the developers can basically do anything with the
> database.
> I am using Windows security and need to deny acces to the data in the
table
> to eeryone apart from a couple of groups. In some cases i need to deny
> access to data just in one column. I know you can do this, but how
reliable
> is it?
> I am trying to figure out where to start on this.
> Should the sensitive data only live in the live database? Should we
release
> the database and revoke rights at tat moment in time? Presumably i could
> write a script to do this.
> Is there any way to have the sensitive data in a database that the
> developers are developing and be comfortable that the data is secure?
> Is the only really safe harbour encryption?
> Also, if anyone out there knows a good book on security architecture for
SQL
> Server, a recommendation would be greatly appreciated.
> TIA
> Sarah
>|||look into using Stored procedures. these can be used with logins. and grant
rights.
it is a lot of work, but will give you the best protection.
Next is to use the encrypt table. I don't like that option my self.
the quickest way to isolate a DB is to user another DB to make the calls to
the stored procedures.
Note: if you SQL server, can be physically accessed by anyone, then all
these procedures are useless.
"Sarah" <Sarah@.TT.com> wrote in message
news:#mrTozL8DHA.2044@.TK2MSFTNGP10.phx.gbl...
> My boss would like to store sensitive information in our company SQL
Server
> database. At the momnt everyone has write access to all data in the
> database. in addition the developers can basically do anything with the
> database.
> I am using Windows security and need to deny acces to the data in the
table
> to eeryone apart from a couple of groups. In some cases i need to deny
> access to data just in one column. I know you can do this, but how
reliable
> is it?
> I am trying to figure out where to start on this.
> Should the sensitive data only live in the live database? Should we
release
> the database and revoke rights at tat moment in time? Presumably i could
> write a script to do this.
> Is there any way to have the sensitive data in a database that the
> developers are developing and be comfortable that the data is secure?
> Is the only really safe harbour encryption?
> Also, if anyone out there knows a good book on security architecture for
SQL
> Server, a recommendation would be greatly appreciated.
> TIA
> Sarah
>|||Use a copy of the database and run a qry on User & Company identifying
details and Swap all the identifying records around.
Did this when I pass on a production db to another developer.
Don Grover
"Sarah" <Sarah@.TT.com> wrote in message
news:%23mrTozL8DHA.2044@.TK2MSFTNGP10.phx.gbl...
> My boss would like to store sensitive information in our company SQL
Server
> database. At the momnt everyone has write access to all data in the
> database. in addition the developers can basically do anything with the
> database.
> I am using Windows security and need to deny acces to the data in the
table
> to eeryone apart from a couple of groups. In some cases i need to deny
> access to data just in one column. I know you can do this, but how
reliable
> is it?
> I am trying to figure out where to start on this.
> Should the sensitive data only live in the live database? Should we
release
> the database and revoke rights at tat moment in time? Presumably i could
> write a script to do this.
> Is there any way to have the sensitive data in a database that the
> developers are developing and be comfortable that the data is secure?
> Is the only really safe harbour encryption?
> Also, if anyone out there knows a good book on security architecture for
SQL
> Server, a recommendation would be greatly appreciated.
> TIA
> Sarah
>

Data Read / Write problem with concurrent users on with dll

Hi,

I have a web app, that runs fine, except for one particular section that uses a class called by an event in the code behind. The class resides as a dll in the bin folder.

We had no problems during testing, when only one user was running this dll. Problems soon occurred when multiple users tried running it. Here's the error & stack:

06/02/2007 09:25:26 ==> cburns ==> There is already an open DataReader associated with this Command which must be closed first. at System.Data.SqlClient.SqlInternalConnectionTds.ValidateConnectionForExecute(SqlCommand command)
at System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command)
at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at ESP.Validator.Data.DatabaseEvents.DatabaseEventManager.Read(IEventable eventObject, Int16 eventType, DateTime earliestDate, DateTime latestDate) in C:\My Path\Validator\Validator.NET\Data\DatabaseEvents\DatabaseEventManager.cs:line 92
at ESP.Validator.Data.Translink.CATCard.GetDespatchDate() in C:\My Path\Projects\Validator\Validator.NET\Data\Translink\CATCard.cs:line 94
at ESP.Validator.Data.Translink.ExistingSchemeEntitlement.ReadCards() in C:\My Path\Projects\Validator\Validator.NET\Data\Translink\ExistingSchemeEntitlement.cs:line 215
at ESP.Validator.Data.Translink.ExistingSchemeEntitlement.Read() in C:\My Path\Projects\Validator\Validator.NET\Data\Translink\ExistingSchemeEntitlement.cs:line 147
at ESP.Validator.Data.Translink.TranslinkApplicant.ReadEntitlements() in C:\My Path\Projects\Validator\Validator.NET\Data\Translink\TranslinkApplicant.cs:line 369
at ESP.Validator.Data.Translink.TranslinkApplicant.Read() in C:\My Path\Projects\Validator\Validator.NET\Data\Translink\TranslinkApplicant.cs:line 353
at ESP.Validator.Data.Translink.PrePrintedLetter.Read() in C:\My Path\Projects\Validator\Validator.NET\Data\Translink\PrePrintedLetter.cs:line 282
at ESP.Validator.ValidationProcessor.Read(ValidationSubject subject) in C:\My Path\Projects\Validator\Validator.NET\ValidationProcessor.cs:line 82
at clear_applications_scan_applications.ProcessValidation()

It seems the data reader is getting reused. We have ensured after each read the reader is closed. Though all users are using the same connection string. Could it be a connection pool problem, with the connection being overwritten during execution? Should i edit it according to the users logon?

I am really at a loss for ideas, and I don't mind admitting I am a bit out of my depth with this one!!

Any ideas/suggestions would be greatly appreciated.

Thanks

Hi Assimalyst,

Based on the exception message, we can see that this is not because of reusing of the SqlDataReader, but reusing the SqlConnection.

Generally, on a single SqlConnection, there can only be 1 open SqlDataReader exist at the same time. If a using is trying to open another one, this exception is thrown.

In ADO.NET 2.0, a new concept is involved called Multiple Active Result Sets (MARS). This enables you open multiple DataReaders on a single connection. To achieve this, you only need to add "MultipleActiveResultSets=True" in your connection string. That will prevent the exception from being thrown.

Please check the following links for more information:

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

HTH. If anything is unclear, please feel free to mark this post as Not Answered and post your reply. Thanks!

sql

data processing extension sample

I'm trying to write some code to retrieve a reports list from a report folder.
Is it possible to create data processing extension in old asp? Either yes
or no, could someone post a link to some sample code, aspx samples would be
just fine?I forgot to mention that I only know VB. So please provide the samples in
VB. Thx a mil!
"JL" wrote:
> I'm trying to write some code to retrieve a reports list from a report folder.
> Is it possible to create data processing extension in old asp? Either yes
> or no, could someone post a link to some sample code, aspx samples would be
> just fine?|||http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5
try this one. the best i have found.
[]s
Renato
"JL" <JL@.discussions.microsoft.com> escreveu na mensagem
news:1E1F4C39-7C3E-4263-B883-D2865507C968@.microsoft.com...
> I forgot to mention that I only know VB. So please provide the samples in
> VB. Thx a mil!
> "JL" wrote:
> > I'm trying to write some code to retrieve a reports list from a report
folder.
> >
> > Is it possible to create data processing extension in old asp? Either
yes
> > or no, could someone post a link to some sample code, aspx samples would
be
> > just fine?|||Thx a lot. But I only want to retrieve the reports list from a report
folder. Do I need to use the same extension? Or is there an alternative
(easier) way? Thx.
"Renato Aloi" wrote:
> http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5
> try this one. the best i have found.
> []s
> Renato
> "JL" <JL@.discussions.microsoft.com> escreveu na mensagem
> news:1E1F4C39-7C3E-4263-B883-D2865507C968@.microsoft.com...
> > I forgot to mention that I only know VB. So please provide the samples in
> > VB. Thx a mil!
> >
> > "JL" wrote:
> >
> > > I'm trying to write some code to retrieve a reports list from a report
> folder.
> > >
> > > Is it possible to create data processing extension in old asp? Either
> yes
> > > or no, could someone post a link to some sample code, aspx samples would
> be
> > > just fine?
>
>|||I saw a sample that demonstrate this... But I don't remmember where... What
I remmember is that you can write some code to do that, using ListChildren
method of Reporting Service Web Server, like this:
dim items() as CatalogItem
items = (new ReportingService).ListChildren("/", True)
then populate a list:
for each item as CatalogItem in items
cboWhatever.Items.Add(new ReportItem(item.name, item.Path))
next
That is it. Do not forget to reference the RS web service...
[]s
Renato
"JL" <JL@.discussions.microsoft.com> escreveu na mensagem
news:F408C4D6-DB48-40F1-A6A5-22BE8E958114@.microsoft.com...
> Thx a lot. But I only want to retrieve the reports list from a report
> folder. Do I need to use the same extension? Or is there an alternative
> (easier) way? Thx.
> "Renato Aloi" wrote:
> >
http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5
> >
> > try this one. the best i have found.
> >
> > []s
> > Renato
> >
> > "JL" <JL@.discussions.microsoft.com> escreveu na mensagem
> > news:1E1F4C39-7C3E-4263-B883-D2865507C968@.microsoft.com...
> > > I forgot to mention that I only know VB. So please provide the
samples in
> > > VB. Thx a mil!
> > >
> > > "JL" wrote:
> > >
> > > > I'm trying to write some code to retrieve a reports list from a
report
> > folder.
> > > >
> > > > Is it possible to create data processing extension in old asp?
Either
> > yes
> > > > or no, could someone post a link to some sample code, aspx samples
would
> > be
> > > > just fine?
> >
> >
> >

Data Mining Clusters

In a response posted Nov 21 (Clustering Dimension), Jamie wrote...

"The only option of using a table-based model as a dimension is to write out the cluster labels and simply make the cluster label as a dimension attribute. You could even append the cluster label to the source data (e.g. the customer table) and not have a seperate dimension, simply a browseable attribute on the dimension of interest"

Jamie, can you provide more information on how to do this? We'd like to have a series of clusters in an existing household dimension. That is, we need multiple occurences of cluster model results over time browsable in the source cube. I've looked at the data source, dimension, and cube created by the data mining model, but I don't see where the case ID (Household Key) and the cluster name could be extracted to update the existing dimension. We're using the cube for the data mining source.

This would also help to fix a recurring problem we have with keeping the linked cube and the source cube metadata in sync. If I make a change to the source cube, say by adding a new measure, the metadata for the linked cube gets out of sync. I've been deleting the data mining dimension, cube, and dsv and them adding them back in using the data mining menu in the model.

Sorry for the late reply - you can use SSIS to take the results of a query e.g. SELECT t.HouseHoldID, Cluster() FROM MyModel PREDICTION JOIN .... , and save to a table. Then add the table to the DSV you use to process the cube. It may be easier to process the cluster model from the source data rather than the cube, although either option is entirely possible, and if you are using aggregated measures found in the cube, the cube method is likely better.

Unfortunately for your latter question, there is no way to keep the cube metadata in synch with linked measure groups. However, if the only difference is the presence of a single DM dimension, it is easy to drop and recreate the linked cube from the user interface. If you have more than one DM dimension, it's more difficult, but it's probably more reliable than trying to make all of your other changes in both places.

|||

Thanks for the repy Jamie. We are using aggregated values from the cube in our model. We'll probably use 4-5 cluster models in our cube, so being able to query them and add them to the DSV will save a lot of time rebuilding the data mining dimensions with the wizard.

Since we're not doing prediction in the query, wouldn't the DMX query be something like:

Select [Household ID], Cluster() From [MyModel]

When I run this I get:

Error (Data mining): Only a predictable column (or a column that is related to a predictable column) can be referenced from the mining model in the context at line 2, column 8.

Thanks for your help

|||

After some more reading, I think I understand the error. I've been thinking of retrieving the case ID and the cluster from the trained model, not issuing a prediction query to get the cluster name for a new case.

In our model, we're using clustering against a household dimension with a nested fact table containing a profitability fact and the fact components that make up profitability (i.e. clustering around the factors that make someone profitable or not.) These are not attributes that can be used to predict profitability, rather they describe it. It sounds like you're saying that we need to make profitability predictable and issue a prediction query on these facts to get the case ID (Household Key) and Cluster(). Is this correct? I've been trying to use the Mining Model Prediction tab to help me build the query, but I don't see how to get the nested case data in the query.

|||

You can enable drillthrough on the model (available in the wizard or the property page for the model) and then you can issue statements like

SELECT * FROM MyModel.CASES

If you want to get the cluster membership for each row you can issue a query like (check syntax) (you won't be able to create with the prediction query builder - you will have to enter by hand)

SELECT t.[Household ID], Cluster() FROM MyModel NATURAL PREDICTION JOIN (SELECT * FROM MyModel.CASES) as t

FYI, you can just add the nested table to a query, e.g.

SELECT t.[Household ID], t.[MyNestedtable], Cluster() ...

or if you want everything

SELECT t.*, Cluster() ...

Data Mining

Hi,

I've come along way learning about databases and SQL. I can write basic queries now. Even some with subQueries. What I need to learn, is how to approach data mining. Can someone suggest the best path to follow, to learn how to accomplish data mining from a very large database?
I don't just need to produce reports of acquired data. I need to go in and grab data and look for patterns against known result sets. I hope that makes sense.

Thanks,
MilfredoWell, do you have a basic grounding in statistics?
TSQL lends itself fairly well to exception reporting, but unfortunately it does not have a rich function set for correlational studies. I've written my own code for simple linear regression, but I haven't attempted true multi-linear analysis.
SQL Server 2005 has the CLR toolkit that allows you to build custom aggregate functions, so it may prove more useful for data analysis and I wouldn't be surprised to see 3rd party developers marketing more advanced statistical functions. In the meantime, you will need to spin off subsets of data for further analysis is a stronger statistical tool, such as Excel or SAS.|||Thanks Blindman,

I'm looking into some third party software. There seems to be several things on the market that will alllow me to drill down into my data and do some analysis. For single user's the price isn't bad.

Milfredo|||If you are just looking at pivoting and drill-downs, a simple Excel pivot table linked to a SQL Server view might get you started.|||Thanks. I actually over simplified my project. I have a fairly extensive database, that I need to work with. I need to select certain data set and see what factors that I can add that will make it more predictive. I'm test driving a software called Purple Mineset at present.

Milfredo

data import question

Hi,
I am writing a script for a client to update TableA using the data in
TableB. I know how to write an UPDATE query to do this. But the client wants
an ability to add or remove fields that they update using this script. I
know I can do this by letting the client enter the names of the source and
destination fields in a custom table and generate a dynamic query to update
the provided columns. But the problem is there are large number of fields
and sql has the limit of 8000 characters on variables write a dynamic query.
So writing a dynamic query is not an easy option either. So my question is
how do I solve this problem. Please not that we need to update 150+ fields
in 500,000+ records using this script. So it also needs to be as fast as
possible.
Thanks in advance.helpful sql wrote:

> Hi,
> I am writing a script for a client to update TableA using the data in
> TableB. I know how to write an UPDATE query to do this. But the client wan
ts
> an ability to add or remove fields that they update using this script. I
> know I can do this by letting the client enter the names of the source and
> destination fields in a custom table and generate a dynamic query to updat
e
> the provided columns. But the problem is there are large number of fields
> and sql has the limit of 8000 characters on variables write a dynamic quer
y.
> So writing a dynamic query is not an easy option either. So my question is
> how do I solve this problem. Please not that we need to update 150+ fields
> in 500,000+ records using this script. So it also needs to be as fast as
> possible.
> Thanks in advance.
Here's one possibility:
UPDATE A
SET
col1 = CASE WHEN @.col1 = 'Y' THEN B.col1 ELSE A.col1 END,
col2 = CASE WHEN @.col2 = 'Y' THEN B.col2 ELSE A.col2 END,
col3 = CASE WHEN @.col3 = 'Y' THEN B.col3 ELSE A.col3 END
..
WHERE ...?
David Portas
SQL Server MVP
--|||Dynamic SQL can take several variables to exceed the 8000 byte limit.
DECLARE @.X1 VARCHAR(8000), @.X2 VARCHAR(8000)
EXEC(@.X1 + @.X2)
Andrew J. Kelly SQL MVP
"helpful sql" <nospam@.stopspam.com> wrote in message
news:eC2Vg$IDGHA.3748@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am writing a script for a client to update TableA using the data in
> TableB. I know how to write an UPDATE query to do this. But the client
> wants an ability to add or remove fields that they update using this
> script. I know I can do this by letting the client enter the names of the
> source and destination fields in a custom table and generate a dynamic
> query to update the provided columns. But the problem is there are large
> number of fields and sql has the limit of 8000 characters on variables
> write a dynamic query. So writing a dynamic query is not an easy option
> either. So my question is how do I solve this problem. Please not that we
> need to update 150+ fields in 500,000+ records using this script. So it
> also needs to be as fast as possible.
> Thanks in advance.
>

Data Formating for export

I need to have a date that is exporting as this 2005-05-18 to export as
5/18/2005.
Is there anyway to write this into the query?I figured out my own answer CONVERT(char(10), DATE , 101)
"Diane" wrote:

> I need to have a date that is exporting as this 2005-05-18 to export as
> 5/18/2005.
> Is there anyway to write this into the query?|||convert(varchar(10),'2005-05-18',101)
--
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Diane" wrote:

> I need to have a date that is exporting as this 2005-05-18 to export as
> 5/18/2005.
> Is there anyway to write this into the query?