Hide parameter for some users

I'm trying to decide whether I can use sql reports for my application, and
the main problem I've run into thus far is that my available parameters need
to change based on the user who is running the report. For instance,
low-level users should only be able to see data about themselves, whereas
managers should be able to select from a drop-down list of all users. Is
there any way to set the user prompt/visibility for a parameter based on the
role of the person running the report?The easiest way I can think of to manage this is to have two versions of the
same report. With the regular version, pass =User!UserID into the query
parameter. In the manager's version, populate the same parameter from a
drop-down list. Then set different permissions when you deploy the reports.
If you need to do it in a single report, you might be able to write some
custom code to pass in flags to the query and work out the difference
between managers and employees in your data source query.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"jgephart" <jgephart@.discussions.microsoft.com> wrote in message
news:36979870-6D83-4C49-B1E6-88D8CBC51678@.microsoft.com...
> I'm trying to decide whether I can use sql reports for my application, and
> the main problem I've run into thus far is that my available parameters
> need
> to change based on the user who is running the report. For instance,
> low-level users should only be able to see data about themselves, whereas
> managers should be able to select from a drop-down list of all users. Is
> there any way to set the user prompt/visibility for a parameter based on
> the
> role of the person running the report?|||Thanks, Jeff. This will affect a whole series of reports, so I don't really
want to maintain separate versions. I think I'm going to be able to go with
passing in User!UserID to the query that builds the drop-down list of users.
I can't hide the drop-down list, but at least I can make sure it's only
populated with a single user if that user doesn't have permission to see
other people's data.
"Jeff A. Stucker" wrote:
> The easiest way I can think of to manage this is to have two versions of the
> same report. With the regular version, pass =User!UserID into the query
> parameter. In the manager's version, populate the same parameter from a
> drop-down list. Then set different permissions when you deploy the reports.
> If you need to do it in a single report, you might be able to write some
> custom code to pass in flags to the query and work out the difference
> between managers and employees in your data source query.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---|||To protect it on the back side (from someone simply altering the URL to look
at someone else's data) you could always create a permissions table that
contains all of the usernames that each user is allowed to look at, and have
that table always be in the source query join, with User!UserID passed into
the query. We're doing a variation on this (a join to a parameterized UDF)
for our data security solution.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"jgephart" <jgephart@.discussions.microsoft.com> wrote in message
news:F5C7BDD7-4F0D-488C-80CB-7F81201B08A4@.microsoft.com...
> Thanks, Jeff. This will affect a whole series of reports, so I don't
> really
> want to maintain separate versions. I think I'm going to be able to go
> with
> passing in User!UserID to the query that builds the drop-down list of
> users.
> I can't hide the drop-down list, but at least I can make sure it's only
> populated with a single user if that user doesn't have permission to see
> other people's data.
>
> "Jeff A. Stucker" wrote:
>> The easiest way I can think of to manage this is to have two versions of
>> the
>> same report. With the regular version, pass =User!UserID into the query
>> parameter. In the manager's version, populate the same parameter from a
>> drop-down list. Then set different permissions when you deploy the
>> reports.
>> If you need to do it in a single report, you might be able to write some
>> custom code to pass in flags to the query and work out the difference
>> between managers and employees in your data source query.
>> --
>> Cheers,
>> '(' Jeff A. Stucker
>> \
>> Business Intelligence
>> www.criadvantage.com
>> ---|||You could use linked reports instead, possibly in conjunction with the
My Reports feature. Linked reports are effectively shortcuts to a
report.
But, the nice touch is that you can set the parameters to behave a bit
differently, for instance there is a prompt user check box on each
parameter in the linked reports properties. This doesn't affect the
original report. If you update the original, the linked report will
reflect these changes too. Security can be set completely independantly
too.
Chris
Jeff A. Stucker wrote:
> To protect it on the back side (from someone simply altering the URL
> to look at someone else's data) you could always create a permissions
> table that contains all of the usernames that each user is allowed to
> look at, and have that table always be in the source query join, with
> User!UserID passed into the query. We're doing a variation on this
> (a join to a parameterized UDF) for our data security solution.sql

Hidden vs. Internal Parameters -- Got to be a better way...

We have an application that gets a report's parameters and parameter values.
We're using SQL Server 2005. The problem is that we need a couple of the
report's parameters to be non-prompting AND not read-only.
In our application, we dynamically build the UI based on the parameters and
parameter values retrieved from the report, but we only want to display
parameters that are PromptUser = true and not hidden and not read-only.
Here's the code:
ReportingService.ReportingService2005 rs = new
ReportingService.ReportingService2005();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
string historyID = null;
bool forRendering = true;
ReportingService.ParameterValue[] values = null;
ReportingService.DataSourceCredentials[] credentials = null;
ReportingService.ReportParameter[] parameters = null;
parameters = rs.GetReportParameters(sReportAndPath, historyID, forRendering,
values, credentials);
if ((parameters != null) && (parameters.Length > 0))
{
foreach (ReportingService.ReportParameter rp in parameters)
{
if (rp.PromptUser)
{
// Display parameter and parameter default values on screen
// Only display parameters that are NOT internal AND NOT hidden
// PromptUser property takes care of the internal
// hidden parameters appear to have a PromptUser = true
}
}
}
How can we do this?
If we set the parameter to internal, then it doesn't display, but we get an
error later when we try to update the parameter value (because it's
read-only). If we set the parameter to hidden, then it gets displayed on
screen because the PromptUser property is true.
Basically, we want to be able to change the parameter value (thus NOT
read-only), but, at the same time, not display on screen (PromptUser = false).
Hope this makes sense.
Any suggestions?I will be very interested in this answer. I have had it work but only after
messing around with the parameters in report manager. I would be interested
in your type of app that dynamically builds the UI. That is something that
we want to do.
can I contact you by email to discuss ... or can you post here.
"steggun" wrote:
> We have an application that gets a report's parameters and parameter values.
> We're using SQL Server 2005. The problem is that we need a couple of the
> report's parameters to be non-prompting AND not read-only.
> In our application, we dynamically build the UI based on the parameters and
> parameter values retrieved from the report, but we only want to display
> parameters that are PromptUser = true and not hidden and not read-only.
> Here's the code:
>
> ReportingService.ReportingService2005 rs = new
> ReportingService.ReportingService2005();
> rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
> string historyID = null;
> bool forRendering = true;
> ReportingService.ParameterValue[] values = null;
> ReportingService.DataSourceCredentials[] credentials = null;
> ReportingService.ReportParameter[] parameters = null;
> parameters = rs.GetReportParameters(sReportAndPath, historyID, forRendering,
> values, credentials);
> if ((parameters != null) && (parameters.Length > 0))
> {
> foreach (ReportingService.ReportParameter rp in parameters)
> {
> if (rp.PromptUser)
> {
> // Display parameter and parameter default values on screen
> // Only display parameters that are NOT internal AND NOT hidden
> // PromptUser property takes care of the internal
> // hidden parameters appear to have a PromptUser = true
> }
> }
> }
>
> How can we do this?
> If we set the parameter to internal, then it doesn't display, but we get an
> error later when we try to update the parameter value (because it's
> read-only). If we set the parameter to hidden, then it gets displayed on
> screen because the PromptUser property is true.
> Basically, we want to be able to change the parameter value (thus NOT
> read-only), but, at the same time, not display on screen (PromptUser => false).
> Hope this makes sense.
> Any suggestions?
>
>|||"steggun" wrote:
> We have an application that gets a report's parameters and parameter values.
> We're using SQL Server 2005. The problem is that we need a couple of the
> report's parameters to be non-prompting AND not read-only.
> In our application, we dynamically build the UI based on the parameters and
> parameter values retrieved from the report, but we only want to display
> parameters that are PromptUser = true and not hidden and not read-only.
> Here's the code:
>
> ReportingService.ReportingService2005 rs = new
> ReportingService.ReportingService2005();
> rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
> string historyID = null;
> bool forRendering = true;
> ReportingService.ParameterValue[] values = null;
> ReportingService.DataSourceCredentials[] credentials = null;
> ReportingService.ReportParameter[] parameters = null;
> parameters = rs.GetReportParameters(sReportAndPath, historyID, forRendering,
> values, credentials);
> if ((parameters != null) && (parameters.Length > 0))
> {
> foreach (ReportingService.ReportParameter rp in parameters)
> {
> if (rp.PromptUser)
> {
> // Display parameter and parameter default values on screen
> // Only display parameters that are NOT internal AND NOT hidden
> // PromptUser property takes care of the internal
> // hidden parameters appear to have a PromptUser = true
> }
> }
> }
>
> How can we do this?
> If we set the parameter to internal, then it doesn't display, but we get an
> error later when we try to update the parameter value (because it's
> read-only). If we set the parameter to hidden, then it gets displayed on
> screen because the PromptUser property is true.
> Basically, we want to be able to change the parameter value (thus NOT
> read-only), but, at the same time, not display on screen (PromptUser => false).
> Hope this makes sense.
> Any suggestions?
>
>|||What I have found is the a hidden parameter and an actual parameter to
be displayed are only different based on the prompt. An internal's
promptuser will be false and there will be no prompt.
If you remember in RS2000, you had to go into the Report Server
Interface and set the parameters promptuser to true and clear out the
prompt. Although a "hidden" indicator is now available in the IDE for
RS2005, the identification through the web service seems not to have
changed.
So, if you look for rp.promptuser && (rp.Prompt.Length > 0), you will
get the correct parameters to display in the UI. If you want to set
the hidden parameters, obmit the (rp.Prompt.Length > 0).
Hopes this helps.
steggun wrote:
> We have an application that gets a report's parameters and parameter values.
> We're using SQL Server 2005. The problem is that we need a couple of the
> report's parameters to be non-prompting AND not read-only.
> In our application, we dynamically build the UI based on the parameters and
> parameter values retrieved from the report, but we only want to display
> parameters that are PromptUser = true and not hidden and not read-only.
> Here's the code:
>
> ReportingService.ReportingService2005 rs = new
> ReportingService.ReportingService2005();
> rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
> string historyID = null;
> bool forRendering = true;
> ReportingService.ParameterValue[] values = null;
> ReportingService.DataSourceCredentials[] credentials = null;
> ReportingService.ReportParameter[] parameters = null;
> parameters = rs.GetReportParameters(sReportAndPath, historyID, forRendering,
> values, credentials);
> if ((parameters != null) && (parameters.Length > 0))
> {
> foreach (ReportingService.ReportParameter rp in parameters)
> {
> if (rp.PromptUser)
> {
> // Display parameter and parameter default values on screen
> // Only display parameters that are NOT internal AND NOT hidden
> // PromptUser property takes care of the internal
> // hidden parameters appear to have a PromptUser = true
> }
> }
> }
>
> How can we do this?
> If we set the parameter to internal, then it doesn't display, but we get an
> error later when we try to update the parameter value (because it's
> read-only). If we set the parameter to hidden, then it gets displayed on
> screen because the PromptUser property is true.
> Basically, we want to be able to change the parameter value (thus NOT
> read-only), but, at the same time, not display on screen (PromptUser => false).
> Hope this makes sense.
> Any suggestions?

Hidden vs. Internal Parameters -- Got to be a better way...

We have an application that gets a report's parameters and parameter values. We're using SQL Server 2005. The problem is that we need a couple of the report's parameters to be non-prompting AND not read-only.

In our application, we dynamically build the UI based on the parameters and parameter values retrieved from the report, but we only want to display parameters that are PromptUser = true and not hidden and not read-only. Here's the code:

ReportingService.ReportingService2005 rs = new ReportingService.ReportingService2005();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

string historyID = null;
bool forRendering = true;
ReportingService.ParameterValue[] values = null;
ReportingService.DataSourceCredentials[] credentials = null;
ReportingService.ReportParameter[] parameters = null;

parameters = rs.GetReportParameters(sReportAndPath, historyID, forRendering, values, credentials);

if ((parameters != null) && (parameters.Length > 0))
{
foreach (ReportingService.ReportParameter rp in parameters)
{
if (rp.PromptUser)
{
// Display parameter and parameter default values on screen
// Only display parameters that are NOT internal AND NOT hidden
// PromptUser property takes care of the internal
// hidden parameters appear to have a PromptUser = true
}
}
}

How can we do this?

If we set the parameter to internal, then it doesn't display, but we get an error later when we try to update the parameter value (because it's read-only). If we set the parameter to hidden, then it gets displayed on screen because the PromptUser property is true.

Basically, we want to be able to change the parameter value (thus NOT read-only), but, at the same time, not display on screen (PromptUser = false).

Hope this makes sense.

Any suggestions?

If the parameter is hidden, string.IsNullOrEmpty(rp.Prompt) == true && rp.PromptUser == true.sql

Hibernate Layer

Hi,
Does any one have any experience with the Hibernate layer [which acts as an
abstraction layer between DB & application layers]? The factor which is
bothering me is the absence of stored procedures. All transactions are done
via this Hibernate. What I am looking at is a system which does not use any
stored procedures at all. This will prevent the db server from using\reusing
a compiled execution plan coupled with all good stuff such as distribution
statistics etc…
The other way to look at is that the sql queries generated & submitted to
the db engine from Hibernate layer will force the query optimizer to do its
work and create an execution plan and cache it in procedure cache [do not
know if they can use a sp_executesql in Hibernate layer…]
I assume that this will introduce a lot of load on the db system for
read\write operations. The folks are already noticing some issues at the
locking\blocking areas with this infrastructure. The real locking\blocking
effects are yet to be seen!
Any thoughts\comments are greatly appreciated.
TIA
Manoj
Senior SQL DBA
Hi Manoj,
I not have direct experience with Hibernate but I recall that NHibernate
(the .NET version) has recently added stored proc support. NHibernate
follow Hibernate (Java version) evolutions. Try find informations on Web.
My Regards
Pierpaolo Simoncini
MCP Sql Server 2000 Ent.Ed.

> Hi,
> Does any one have any experience with the Hibernate layer [which acts as an
> abstraction layer between DB & application layers]? The factor which is
> bothering me is the absence of stored procedures. All transactions are done
> via this Hibernate. What I am looking at is a system which does not use any
> stored procedures at all. This will prevent the db server from using\reusing
> a compiled execution plan coupled with all good stuff such as distribution
> statistics etc…
> The other way to look at is that the sql queries generated & submitted to
> the db engine from Hibernate layer will force the query optimizer to do its
> work and create an execution plan and cache it in procedure cache [do not
> know if they can use a sp_executesql in Hibernate layer…]
> I assume that this will introduce a lot of load on the db system for
> read\write operations. The folks are already noticing some issues at the
> locking\blocking areas with this infrastructure. The real locking\blocking
> effects are yet to be seen!
> Any thoughts\comments are greatly appreciated.
> TIA
> Manoj
> Senior SQL DBA
>
>
sql

Hi Need Help

I have an application in c#. Which is intended to give student with highest marks in each class.

There are 10 classes

The requirement is to find top student of each class and display it in columnwise like below

Class Student Name

Can anyone help me on that.

Thanks in advance

Hi,

Prepare the SQL Query which returns the Top 10 values from each class vise..

Select Top 10 Stud-ID,Stud_Name from StudentTable where StudentClass = UserParameter;

Write the C# Method which accept the One Parameter from the User and return the result set.

public DataSet GetTopTen(string strClassParam)
{
ds = new DataSet();
if (ObjCon.State == ConnectionState.Open) ObjCon.Close();
ObjCon.Open();
string strQuery = "Select Top 10 Stud-ID,Stud_Name from StudentTable where StudentClass ="+strClassParam; (Change the Query according to your need..)
sc = new SqlCommand(strQuery, ObjCon);
sda = new SqlDataAdapter(sc);
sda.Fill(ds);
ObjCon.Close();
return ds;
}

Biind the Result Set to the DataGrid or Gridview to show it.

That is it.

|||

hi,

i assume that u r geeting yr data from sql qury. if so, then u can get ur data by firing such query

selectdistinct class,studentnamefrom studenttablewhere mark=(selectmax(mark)from studenttable)

you can getthis by declaring dataset.u can bind gridview withclass and studentname bounded columns and assigning datasource to it like

gridview1.datasource = ds.tables[0].defaultview;

gridview1.databind();

Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set

Hi,

I have a problem with linked servers.

I have an application running against a SQLServer 2005 Express. For some limitations, I had to access from the same application to another database, but I cannot change to another server.

So I have 2 created a second instances, where the first one refers the second one and I created synonyms in the first one to access to all the objects in the second one, to emulate a database in the first instances, but running on the second one. The final idea is to move to another server, but for the testing I use another instance.

But when I try to access to the aplication database, I hav the following error: Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.

I searched solutions for this issue, but I only found to add SET ANSI_NULLS ON and SET ANSI_WARNINGS ON to my connection, before the queries, but I can't, because I cannot change the application.

If anyone can help me, I'd be veri greatfull

Best regards, ArielAriel,
I had a similar problem with SQL 2000 when I added a stored procedure to access the data on a linked server. I had created the stored proc through Query Analyzer and the work around was to create the stored proc through Enterprise Manager. Once I created within Enterprise Manager it worked.
A google search of this problem will provide results.|||The problem is the application have a lot of SPs, tables and views I have created the synonims.

The problem is only with the SPs?

If that, I can remove the synonims and create SPs that access the original ones.

Thanks, Ariel

Help-Name Standardization Problem

Is there an application out there that will allow us to cleanse/merge-purge
the name fields for a consumer as well as for a commercial data warehouse?
In other words, we want to take, for example, Walmart, Wal-Mart, Walmart
Corp, etc and boil it down to a single WalMart Corporation entity.
An example of the consumer side would be Jim Smith, James E. Smith, Jimmy
Smith, etc.
Thanks!Welcome to the world of data scrubbing. There is no corporate lookup
function, you will have to do some string manipulation and parsing to clean
the data. If you can convince the users/business units to do this, the
better.
Ray Higdon MCSE, MCDBA, CCNA
--
"Paul Stanial" <p_stanial@.webfirst.com> wrote in message
news:102lb9shn77u392@.corp.supernews.com...
> Is there an application out there that will allow us to
cleanse/merge-purge
> the name fields for a consumer as well as for a commercial data warehouse?
> In other words, we want to take, for example, Walmart, Wal-Mart, Walmart
> Corp, etc and boil it down to a single WalMart Corporation entity.
> An example of the consumer side would be Jim Smith, James E. Smith, Jimmy
> Smith, etc.
>
> Thanks!
>
>|||Thanks for the response. I guess I'm thinking that if we could go against a
table with all registered U.S. corporations (e.g. D&B?), then we could
relate the names in the records, along with their addresses and City to the
correct corporate entity and then aggregate the relevent fields from each
into the new "master record."
Thanks again...
"Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
news:OpSpUiR8DHA.360@.TK2MSFTNGP12.phx.gbl...
> Welcome to the world of data scrubbing. There is no corporate lookup
> function, you will have to do some string manipulation and parsing to
clean
> the data. If you can convince the users/business units to do this, the
> better.
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "Paul Stanial" <p_stanial@.webfirst.com> wrote in message
> news:102lb9shn77u392@.corp.supernews.com...
> cleanse/merge-purge
warehouse?
Jimmy
>|||I don't think it exists or at least published anywhere, I could be wrong.
Ray Higdon MCSE, MCDBA, CCNA
--
"Paul Stanial" <p_stanial@.webfirst.com> wrote in message
news:102nk04oc8h1q91@.corp.supernews.com...
> Thanks for the response. I guess I'm thinking that if we could go against
a
> table with all registered U.S. corporations (e.g. D&B?), then we could
> relate the names in the records, along with their addresses and City to
the
> correct corporate entity and then aggregate the relevent fields from each
> into the new "master record."
> Thanks again...
> "Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
> news:OpSpUiR8DHA.360@.TK2MSFTNGP12.phx.gbl...
> clean
> warehouse?
Walmart
> Jimmy
>

Help: SQL 2000 connection error(.NET 2.0, VS 2005)

Moderated by XIII, please use a normal font size for better readability:

for some reason, my web application can not connect to my SQL 2000

my application is based on .net 2.0 (IIS 5.1) using vs 2005workstation does not install any sql serversql 2000 locates at the remote server.the sql server can be connected via vs environment (data connnection), therefore, i think the connection string, server name and instance name is correcti have tried all the solution i can find online, but it is not workingLocalSQLServer connection string has been removed in GobalSettingalso remove all default connection string in web.config (<clear />)aspnet_regsql can register the sql 2000 db successfulvs 2005 can access and display the data in sql 2000 quite wellhas been reboot lots of times.still bug out

any body have any idea? thanks, it already costs me the whole day

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Can you telnet to the remote server (test TCP/IP)? And how about "net use\\servername\IPC$" the remote server (test Named pipe)? You can take a look at this post:

http://forums.asp.net/thread/1266057.aspx

|||

Thank Jay,

I can telnet to the remote server. Actually, I think the connection to the remote server is correct (in VS 2005 environment the data can be display quite well), the only problem is .NET 2.0 always think the remote server is SQL 2005. I have removed the localSQLServer connection string in web.config file (<clear />) but still not working.

|||

Then how about force to use TCP/IP to connect? You can do this by usingtcp:myinstance,portas Data Source, where port is the tcp port used the SQL instance service. Actually there should be no difference when connect to SQL2005 or SQL2000, you can take a look at this article which shows how to place membership on SQL2000:

http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx

HELP: Installing SQl server databases

Dear all,
One of our application consist of configuring an SQL server database with
user rights to tables and preparing corresponding store procedure.
usually when we do this, we prepare the server in our test environment and
then ship the whole hardware pre configure to customers.
in some cases we do not have the customer server, so we need to do the whole
configuration on site and in cas of errors its hard to find.
is there an easy way to prepare the SQL confisuration database, store
procedure, table rights on a test PC, then do a kind of straight forward
deployement on any server running SQL ?
thanks for your help
regards
SergeHi,
You can create the customer database in your local sql server. Do all the
setups in that new database. backup the database or detach the database.
Copy the backup or MDF and LDF file and take it in a tape. Restore the files
from tape to Customer side server and
do a Database restore or Attach the MDF and LDF.
Incase if you have login an user suid mismacth use the system stored
procedure sp_change_users_login to fix.
This will ensure that all the configurations are avaiable.
Thanks
Hari
SQL Server MVP
"serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
message news:EDEAF3C8-903B-49D6-8A48-9FD1B71E379F@.microsoft.com...
> Dear all,
> One of our application consist of configuring an SQL server database with
> user rights to tables and preparing corresponding store procedure.
> usually when we do this, we prepare the server in our test environment and
> then ship the whole hardware pre configure to customers.
> in some cases we do not have the customer server, so we need to do the
> whole
> configuration on site and in cas of errors its hard to find.
> is there an easy way to prepare the SQL confisuration database, store
> procedure, table rights on a test PC, then do a kind of straight forward
> deployement on any server running SQL ?
> thanks for your help
> regards
> Serge|||I have seen the method suggested earlier about taking the mdf and ldf and
using sp_attach_Db and in most cases you should be OK ( except for
permissions.) However the risk you are taking is with the file version...
The physical files will only have a limited life span, as MS changes things
usually attach works for one release backwards...
Another way is to generate scripts... These take longer to run, but are
usually safer because basic syntax does not change that often...One thing
you might do is combine the two... In your release, generate the scripts.
Then when you are about to go and setup a db for a user, THEN run the
scripts, and take the resulting mdf and ldf...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
message news:EDEAF3C8-903B-49D6-8A48-9FD1B71E379F@.microsoft.com...
> Dear all,
> One of our application consist of configuring an SQL server database with
> user rights to tables and preparing corresponding store procedure.
> usually when we do this, we prepare the server in our test environment and
> then ship the whole hardware pre configure to customers.
> in some cases we do not have the customer server, so we need to do the
> whole
> configuration on site and in cas of errors its hard to find.
> is there an easy way to prepare the SQL confisuration database, store
> procedure, table rights on a test PC, then do a kind of straight forward
> deployement on any server running SQL ?
> thanks for your help
> regards
> Serge|||I am a bit confuse as I am new in that part.
let say that my ServerA is my test server in my office on which I prepare
every thing
lets call ServerB the target server on which th final DB will run
Could you please details me which operation to do on which server?
What is the content of those MDF and LDF files ?
How to deploy rights or group from server A to ServerB at the same time
thanks and sorry to disturb
experince will come with the time :-)
serge
"Wayne Snyder" wrote:
> I have seen the method suggested earlier about taking the mdf and ldf and
> using sp_attach_Db and in most cases you should be OK ( except for
> permissions.) However the risk you are taking is with the file version...
> The physical files will only have a limited life span, as MS changes things
> usually attach works for one release backwards...
> Another way is to generate scripts... These take longer to run, but are
> usually safer because basic syntax does not change that often...One thing
> you might do is combine the two... In your release, generate the scripts.
> Then when you are about to go and setup a db for a user, THEN run the
> scripts, and take the resulting mdf and ldf...
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.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
> "serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
> message news:EDEAF3C8-903B-49D6-8A48-9FD1B71E379F@.microsoft.com...
> > Dear all,
> >
> > One of our application consist of configuring an SQL server database with
> > user rights to tables and preparing corresponding store procedure.
> >
> > usually when we do this, we prepare the server in our test environment and
> > then ship the whole hardware pre configure to customers.
> >
> > in some cases we do not have the customer server, so we need to do the
> > whole
> > configuration on site and in cas of errors its hard to find.
> >
> > is there an easy way to prepare the SQL confisuration database, store
> > procedure, table rights on a test PC, then do a kind of straight forward
> > deployement on any server running SQL ?
> >
> > thanks for your help
> > regards
> > Serge
>
>|||On ServerA you will prepare the database to be installed, call it DBa. Once
this is complete you have 2 choices. 1) You can dump DBa and use the
resulting dump file, or 2) you can detach DBa and use the MDF and LDF files.
Copy either the dump file or MDF and LDF files to ServerB.
If you selected the database dump option, restore DBa. See Books Online for
the syntax.
If you selected the detach option, attach DBa. Again see Books Online for
the syntax.
As mentioned in one of the earlier posts if you created any data server
logins and database users you will need to fix those up via the
sp_change_users_login stored proc. See Books Online for documentation.
"serge calderara" wrote:
> I am a bit confuse as I am new in that part.
> let say that my ServerA is my test server in my office on which I prepare
> every thing
> lets call ServerB the target server on which th final DB will run
> Could you please details me which operation to do on which server?
> What is the content of those MDF and LDF files ?
> How to deploy rights or group from server A to ServerB at the same time
> thanks and sorry to disturb
> experince will come with the time :-)
> serge
> "Wayne Snyder" wrote:
> > I have seen the method suggested earlier about taking the mdf and ldf and
> > using sp_attach_Db and in most cases you should be OK ( except for
> > permissions.) However the risk you are taking is with the file version...
> > The physical files will only have a limited life span, as MS changes things
> > usually attach works for one release backwards...
> >
> > Another way is to generate scripts... These take longer to run, but are
> > usually safer because basic syntax does not change that often...One thing
> > you might do is combine the two... In your release, generate the scripts.
> > Then when you are about to go and setup a db for a user, THEN run the
> > scripts, and take the resulting mdf and ldf...
> >
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > www.mariner-usa.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
> >
> > "serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
> > message news:EDEAF3C8-903B-49D6-8A48-9FD1B71E379F@.microsoft.com...
> > > Dear all,
> > >
> > > One of our application consist of configuring an SQL server database with
> > > user rights to tables and preparing corresponding store procedure.
> > >
> > > usually when we do this, we prepare the server in our test environment and
> > > then ship the whole hardware pre configure to customers.
> > >
> > > in some cases we do not have the customer server, so we need to do the
> > > whole
> > > configuration on site and in cas of errors its hard to find.
> > >
> > > is there an easy way to prepare the SQL confisuration database, store
> > > procedure, table rights on a test PC, then do a kind of straight forward
> > > deployement on any server running SQL ?
> > >
> > > thanks for your help
> > > regards
> > > Serge
> >
> >
> >

Help: How to detect inserts, updates, deleted on a table from within C++ application?

Hopefully someone can at least point me in the right direction for more
research (e.g.: correct terminology). My only previous experience was just
dumping data into a database using ODBC, and that was some years ago so now
mostly forgotten.

I need to write an NT Service/Application (in C/C++) that will be getting
data sent to it via SQL Server 2000. The data will arrive in my SQL Server
(read-only access), via replication of tables from another remote SQL
Server.

My application needs know when new row are inserted, or updated so it can to
read this data (needs to be quick/timely so hopefully no polling) to then
interface with other remote proprietary systems.

T.I.A.

PS: If you can recommend appropriate books on SQL Server 2000 that would
also be useful.L,
There isn't an easy way to do this. You don't say how many tables you
need info for, but I think the best way to do this is to create and insert
and update trigger on the rows which would insert the primary key for that
table into another table or tables. This way, your application could poll
the new table and grab the primary keys that were inserted or changed, then
delete all data in the table.
Or, you could add a last_modified field which would be updated by
trigger anytime the row was inserted or updated. This might be a little
slower for your application, but easier to maintain data integrity. The
problem with the first solution is you need to be careful if an insert or
update is happening at the same time that you poll your new table.

I hope this helps.
Better than books on MS SQL Server 2000, you might take a look at our
video series at www.TechnicalVideos.net. In just a few hours, you can go
from newbie to expert. Download and watch the .wmv videos on your computer
and our experts will show you how to do things that we really use; things
you won't find in books. There's nothing like having an expert show you
first hand.

Best regards,
Chuck Conover
www.TechnicalVideos.net

"L. Blunt" <withheld@.my.choice> wrote in message
news:40471e58$0$28281$cc9e4d1f@.news.dial.pipex.com ...
> Hopefully someone can at least point me in the right direction for more
> research (e.g.: correct terminology). My only previous experience was just
> dumping data into a database using ODBC, and that was some years ago so
now
> mostly forgotten.
> I need to write an NT Service/Application (in C/C++) that will be getting
> data sent to it via SQL Server 2000. The data will arrive in my SQL Server
> (read-only access), via replication of tables from another remote SQL
> Server.
> My application needs know when new row are inserted, or updated so it can
to
> read this data (needs to be quick/timely so hopefully no polling) to then
> interface with other remote proprietary systems.
> T.I.A.
> PS: If you can recommend appropriate books on SQL Server 2000 that would
> also be useful.|||L. Blunt (withheld@.my.choice) writes:
> Hopefully someone can at least point me in the right direction for more
> research (e.g.: correct terminology). My only previous experience was
> just dumping data into a database using ODBC, and that was some years
> ago so now mostly forgotten.
> I need to write an NT Service/Application (in C/C++) that will be getting
> data sent to it via SQL Server 2000. The data will arrive in my SQL Server
> (read-only access), via replication of tables from another remote SQL
> Server.
> My application needs know when new row are inserted, or updated so it
> can to read this data (needs to be quick/timely so hopefully no polling)
> to then interface with other remote proprietary systems.

Chuck suggested triggers, and that is about the only way to go in SQL
2000, if you don't want to poll. The trigger would in that case invoke
an exentended stored procedure, or an OLE object to alert your application
that there is data to find.

But there might be a catch, since you are using replication. I don't relly
know what happens with triggers on tables in a subscriber database when
you set up replication. There may be a risk that all at a sudden the
triggers are gone.

So intensive polling might be better. Then there is a question on how to
poll, but knowing nothing about the database, this is a little difficult
to discuss.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp