determine a role

hi,

since I am kind o'new with SQL, I preffer get an advice fro you pro's: I created an application which performs access to a database on an SQL server. the application will be used by a few different users, each on a different computer. the application calls stored procedures, updates\inserts records in tables on the SQL and delete rows. what would be the best role to define the users activity ? How do I limit their activity ONLY to the specified actions ?

For the tasks you mentioned, typically there will be permissions at the granularity you want (for example EXECUTE on a stored procedure, INSERT and UPDATE on a table, etc.), but I would strongly suggest referring to BOL to learn more about this topic. A good starting point can be

“Security Considerations for Databases and Database Applications” (http://msdn2.microsoft.com/en-us/library/ms187648(SQL.90).aspx).

I hope this helps, but let us know if you have any further question.

-Raul Garcia

SDE/T

SQL Server Engine

Detecting the render method using an expression

Is there a way of detecting (in an expression in the report) the render
method that is being used to render the report?
Scenario
I have created a custom assembly that sits in the footer of a report and
writes the total number of pages to a database table. However, the total
number of pages varies depending on the render method used, and I need to
capture this render method along with the total pages.
I can't pass the render method into the report as a parameter because, even
when rendering with a single render format such as PDF the report seems to
render twice:
string format = "PDF";
results = viewer.ServerReport.Render(format, deviceInfo, out mimeType, out
encoding, out fileNameExtension, out streamIDs, out warnings);
The above code inserts two records into the database table (once as either
XML or HTML and then as PDF). The first record reflects the total pages
displayed in the viewer, and the second record reflects totalPages as if
exported into PDF.
If we could detect which format was being used at render time then we could
get around this double rendering problem.Hello Stu,
I undertstand that you want to pass the render type in the expression.
Well you could not pass the render type in the expression.
Could you please let me know how your custom code to insert the total page
information?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei Lu,
The expression in the report is:
--
="Page "&Globals!PageNumber & " of "
&LogAttributes.CommonFunctions.LogReportDetails(Globals!ReportName,
Globals!TotalPages, Globals!PageNumber, Parameters!SnapshotID.Value,
Parameters!SchemaName.Value,
Parameters!Database.Value,Parameters!UserID.Value,Parameters!Password.Value,
Parameters!Server.Value)
--
And the code in the custom assembly is:
--
public static int LogReportDetails(string reportName, int
totalNumberOfPages, int currentPage, int snapShotID, string schema,
string database, string userID, string password, string server)
{
bool testRun = false;
if (currentPage == totalNumberOfPages) { testRun = true; } else
{ testRun = false; }
if (testRun)
{
int totalNumPages;
bool ok = true;
TableOfContents toc = new TableOfContents();
toc.Description = reportName;
toc.TotalNumberOfPages = totalNumberOfPages;
toc.CurrentPage = currentPage;
toc.SnapshotID = snapShotID;
toc.SchemaName = schema;
toc.Database = database;
toc.UserID = userID;
toc.Password = password;
toc.Server = server;
SqlTOCDalc dalc = new SqlTOCDalc();
totalNumPages = dalc.InsertTocRow(toc, ok);
return totalNumPages;
}
else
{
return totalNumberOfPages;
}
}
--
Thanks
Stu
"Wei Lu [MSFT]" wrote:
> Hello Stu,
> I undertstand that you want to pass the render type in the expression.
> Well you could not pass the render type in the expression.
> Could you please let me know how your custom code to insert the total page
> information?
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Stu,
Well, unfortunately, you could not refer the render method in the custom
code. And the only workaround I thought is that you may need to add the
custom application to call the report instead of the access the report via
the web browser.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Detail row doesn't repeat!

How do I set the detail row to show all similar records?
I created another report with same exact query and it shows all 25.
Thanks,
TrintPlease provide more details than this for us to be able to help you.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"trint" <trinity.smith@.gmail.com> wrote in message
news:1105127010.082333.226250@.z14g2000cwz.googlegroups.com...
> How do I set the detail row to show all similar records?
> I created another report with same exact query and it shows all 25.
> Thanks,
> Trint
>|||header --> stufff
details--> one row and should be 25.|||header --> stufff
details--> one row and should be 25.|||Sounds like a developer error to me.
You're obviously not eager to work at explaining your problem. Why should
we work to help you solve it?
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"trint" <trinity.smith@.gmail.com> wrote in message
news:1105129238.972872.279370@.c13g2000cwb.googlegroups.com...
> header --> stufff
> details--> one row and should be 25.
>|||Ok,
What is required of a 'detail' row to display multiple instances of
records with, let's say '123' as the first similar column...is that a
setting in the layout?
Thanks,
Trint|||The hide duplicates property.
"trint" wrote:
> Ok,
> What is required of a 'detail' row to display multiple instances of
> records with, let's say '123' as the first similar column...is that a
> setting in the layout?
> Thanks,
> Trint
>

Detach & Attach Database

I've just created ASPNETDB database with ASP.NET Security. Now, I want to send this db to orther computer.First, I detached this db, then when I used attach database in that computer, there is an error :

Error 602: Could not find row in sysindexes for database ID 8, object ID 1, indext ID 1. RUN DBCC CHECKTABLE on sysindexes.

Please help me .Thank.

I guess you're trying to attach a database from SQL2005/Express to a SQL2000 instance. Since most system objects have been changed in SQL2005, you can't use a SQL2005 database in SQL2000. What you can do is to transfer database objects/data/schema from SQL2005 to SQL2000.

|||How I do ? Could you guide me step by step ! Thank.|||You can open VS2005->new an Integration Service Project->add a Transfer SQL Server Objects Task

DESPERATE help needed....

Hello all,

I am a total newbie to SQL. I created this sp and then with C++,
called it. My return value was 100 (which it was)

CREATE PROCEDURE sp_StoreIPs
@.IPSource varchar(16),
@.IPTarget varchar(16),
@.TimeDate varchar(20),
@.Name varchar(250)
as
declare @.iReturn int
Set @.iReturn = 100
return @.iReturn
GO

Then when I added a INSERT statement like ...

CREATE PROCEDURE sp_StoreIPs
@.IPSource varchar(16),
@.IPTarget varchar(16),
@.TimeDate varchar(20),
@.Name varchar(250)
as
declare @.iReturn int
Insert into LookUP (IPSource, IPTarget,TimeDate, Name) Values
(@.IPSource,@.IPTarget,@.TimeDate,@.Name)
Set @.iReturn = 100
return @.iReturn
GO

My return value was 0. I am assuming the the INSERT statement is
returning the 0 but how can I get around this?

Thanks
Ralph Krausse
www.consiliumsoft.com
Use the START button? Then you need CSFastRunII...
A new kind of application launcher integrated in the taskbar!
ScreenShot - http://www.consiliumsoft.com/ScreenShot.jpg"Ralph Krausse" wrote:

<snip
> CREATE PROCEDURE sp_StoreIPs
> @.IPSource varchar(16),
> @.IPTarget varchar(16),
> @.TimeDate varchar(20),
> @.Name varchar(250)
> as
> declare @.iReturn int
> Insert into LookUP (IPSource, IPTarget,TimeDate, Name) Values
> (@.IPSource,@.IPTarget,@.TimeDate,@.Name)
> Set @.iReturn = 100
> return @.iReturn
> GO
>
> My return value was 0. I am assuming the the INSERT statement is
> returning the 0 but how can I get around this?

<snip
Ralph,

I think you're half correct: an insert can return a message about records
affected... but I don't know why that would affect the return value unless
the insert failed: are you checking to insure successful execution? My
guess would be that the INSERT is failing.

In any event, to suppress the record count coming back as a message, you can
use SET NOCOUNT ON/OFF:

CREATE PROCEDURE blah
AS

SET NOCOUNT ON

INSERT SomeTable (fld) VALUES ('a')

SET NOCOUNT OFF

RETURN 100
GO

And the NOCOUNT setting can be handy even if this isn't your problem: it's
always nice to eliminate unnecessary network chitchat :)

Craig|||[posted and mailed, please reply in news]

Ralph Krausse (gordingin@.consiliumsoft.com) writes:
> I am a total newbie to SQL. I created this sp and then with C++,
> called it. My return value was 100 (which it was)

And what API did you use to call the procedure from C++?

> CREATE PROCEDURE sp_StoreIPs

Don't use the sp_ prefix for the names of stored procedure. This prefix
is reserved for system procedures, and SQL Server first looks for
procedures with this prefix in the master database.

> Then when I added a INSERT statement like ...
>...
> My return value was 0. I am assuming the the INSERT statement is
> returning the 0 but how can I get around this?

As Craig said, the INSERT statement generates a kind of result set to
inform of the number of rows consumed. If you don't get that result
set, you will not get the return value, nor the value of output parameter,
since these are not availble until all result sets have been consumed.

Since I don't know which API you are using, I cannot say which methods you
should use, but you should always make it a habit to fetch all result set.

SET NOCOUNT ON, which Craig suggested, is a very good idea, if you are
not interested in these record counts, since they cause extra round-trips
to the server. However, result sets may appear of other reasons, for
instance a trigger with a SELECT statement in it. (Which is poor practice,
but accidents happens.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

Design View

I have a database created by another developer that I purchased and was
recently instructed to change some values in some tables form one kind to
another like "NOT NULL" to "NULL" or change something to a "FLOAT" I am not
that good at SQL but have always done this stuff with the Query Anylizer.. I
kept getting errors due to the relations in the tables and was instructed to
do the following?
"You should just make these changes in the design view of Enterprise
Manager, not through SQL commands. It should automatically update any
relationships for you."
I created a new view but still do not see where I can change these values,
all 3 of my SQL books seem to not cover this or the Enterprise Manager very
much.. And the hlp file is not clear on this either.
Thanks for any help.
Don
Hi
If you have purchased this database then I would expect it is the
responsibility of the developer that sold it you to create the appropriate
upgrade scripts!
There is nothing wrong with doing this in Query Analyser and most
experienced DBAs will do it that way. Enterprise manager will do some of the
harder work required to change columns when they are part of a PK or FK. You
can use profiler to see what EM does when you save changes made.
Using EM to get into design mode for a table, open up the tables branch in
EM and right click the table, you will then have a design table option on
the menu.
John
"Don Stull" <dstull1@.msn.com> wrote in message
news:eW7AeQuUEHA.1764@.TK2MSFTNGP10.phx.gbl...
> I have a database created by another developer that I purchased and was
> recently instructed to change some values in some tables form one kind to
> another like "NOT NULL" to "NULL" or change something to a "FLOAT" I am
not
> that good at SQL but have always done this stuff with the Query Anylizer..
I
> kept getting errors due to the relations in the tables and was instructed
to
> do the following?
> "You should just make these changes in the design view of Enterprise
> Manager, not through SQL commands. It should automatically update any
> relationships for you."
> I created a new view but still do not see where I can change these values,
> all 3 of my SQL books seem to not cover this or the Enterprise Manager
very
> much.. And the hlp file is not clear on this either.
> Thanks for any help.
> Don
>
>

Design table option not available in EM

I restored a database from one server to another. I created a SQL Windows
Authenticated login account for a developer and granted access to the
restored database as owner. When developer right clicks a table, the design
table option is not available. Does anyone know why this occurs in EM?
Is that account the one in which the server is registered in EM?

Design table option not available in EM

I restored a database from one server to another. I created a SQL Windows
Authenticated login account for a developer and granted access to the
restored database as owner. When developer right clicks a table, the design
table option is not available. Does anyone know why this occurs in EM'Is that account the one in which the server is registered in EM?

design question / confirmation

I have a fact table that has several fields that are Y/N in the source system.

Originally, I created the fact table converting the data from Y to 1 and N to zero, with the measure aggregate function property set to "sum".

However after reading some of the posts by Mosha, I think I should be converting these N's to NULLs instead of Zero's to take advantage of NON EMPTY.

Is my logic correct?

It really depends on what you want to do with this data. It's hard to give advice without more details. Typically, in the questionarie type of models, the Y/N columns are converted to attributes, not to measures, and the measure is count. This way it is easy to get how many people answered Yes and how many people answered No. By doing this as measure - you will have easy way to find out how many said Yes, but you will lose info about how many said No.|||

In this case the yes / no indicate if a particular production step was performed. So the values will get summed up for a total.

the no's mean nothing to me.

that total may then also be used to determine a percent of all products that had that production step performed.

|||Then it is possible to model it the way you proposed (although I still think that making a Y/N attribute is a good idea).

design question / confirmation

I have a fact table that has several fields that are Y/N in the source system.

Originally, I created the fact table converting the data from Y to 1 and N to zero, with the measure aggregate function property set to "sum".

However after reading some of the posts by Mosha, I think I should be converting these N's to NULLs instead of Zero's to take advantage of NON EMPTY.

Is my logic correct?

It really depends on what you want to do with this data. It's hard to give advice without more details. Typically, in the questionarie type of models, the Y/N columns are converted to attributes, not to measures, and the measure is count. This way it is easy to get how many people answered Yes and how many people answered No. By doing this as measure - you will have easy way to find out how many said Yes, but you will lose info about how many said No.|||

In this case the yes / no indicate if a particular production step was performed. So the values will get summed up for a total.

the no's mean nothing to me.

that total may then also be used to determine a percent of all products that had that production step performed.

|||Then it is possible to model it the way you proposed (although I still think that making a Y/N attribute is a good idea).

design pattern question

Say I have an object that can be created by some users and not others. And, I have a method something like -function createObject(userId as Integer, objectName as String). The method calls a stored procedure using the two input arguments as parameters.
Now, what I am wondering is this; I am obviously going to check in my SP to ensure that the user has the rights to create the new object before inserting the new record, and there will be an output parameter to send the new record ID back to the method, but do I
a) have a separate output parameter to report on the success of the creation (e.g. @.Err Int OUTPUT)
b) use the existing output (used to send the new record ID back) and send an error code (e.g. if newRecordId = -9999)
c) somehow try to cause a failure of the SP that can be caught in a try/catch block
Thanks in advance,
MartinI would use option #2. Since you already have this variable, it can easily be checked for an error code and Im sure you are probably doing other types of checks as well. I would never ever ever use option #3. try/catches should always only be used from true exceptions. I always send myself an error email when a catch is experienced and would always be receiving these emails when there really wasnt an error.
Nick