Drag columns around?

Hello,

I am a complete newbie to CR and am evaluating CR-XI. I have a requirement to create user-customizable reports - i.e. reports where the user can move columns around to get a layout he/she likes for printing. For example, if there were 5 fields in the report:

Column1 Column2 Column3 Column4 Column5

and the user wanted to print the report with the field order:-

Column1 Column2 Column5 Column3 Column4

is it possible for the user to be able to drag the columns around? Being an amateur, could someone please list the steps I need to go thru to achieve this? Is this possible programatically - via VB6/VB.Net and if so could someone direct/show me some detailed code for this?

Apart from what I described above, the bigger requirement is to create reports at run-time from scratch. I need to create an application that will accept SQL queries from the user, parse it, run it, and display the resultant recordset as a CR. Could someone please direct/show me some code on how to do it - I am a beginner and so will need as much help as possibe.

I use Delphi 7/.Net but am pretty sure I will be able to convert VB to Delphi.

I very much appreciate any help I can get.

Thanks,
VrijeshSee if you find solution at the suppoet section of this site
www.BusinessObjects.comsql

DPE Error running on report Server

We created a DPE to pull data off a Web Service to be used as a datasource in
the reports we create. We created and added the code group to the
rssrvpolicy.config and rsmgrpolicy.config files. Also added the extension
name to the Data section of the RSReportServer.config file. The report works
correctly in debug designer mode but we receive the following error when
trying to run the report through the report manager or report server.
An error has occurred during report processing. (rsProcessingAborted) Get
Online Help
Query execution failed for data set 'NewDataset'. (rsErrorExecutingCommand)
Get Online Help
Request for the permission of type
System.Security.Permissions.EnvironmentPermission, mscorlib,
Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 failed.Check the documentation about how to deploy a similar DPE.
[WebService Data Processing Extension]
http://www.rdlcomponents.com/DTE/default.aspx
Thanks
Jerry
"Deb Taylor" wrote:
> We created a DPE to pull data off a Web Service to be used as a datasource in
> the reports we create. We created and added the code group to the
> rssrvpolicy.config and rsmgrpolicy.config files. Also added the extension
> name to the Data section of the RSReportServer.config file. The report works
> correctly in debug designer mode but we receive the following error when
> trying to run the report through the report manager or report server.
> An error has occurred during report processing. (rsProcessingAborted) Get
> Online Help
> Query execution failed for data set 'NewDataset'. (rsErrorExecutingCommand)
> Get Online Help
> Request for the permission of type
> System.Security.Permissions.EnvironmentPermission, mscorlib,
> Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 failed.
>

Dowt in create View

I want to create a view from dynamic tables

Query:

CREATE VIEW dbo.TrialView
AS
SELECT *
FROM (select name from dbo.sysobjects where name like 'scTabForm%' and type='u') p

Output:
Name
scTabForm0
scTabForm1

But I want all the fields from table starts with 'scTabForm'

like: select * from scTabForm0,scTabForm1,...

Regards
RaabuI think I know what you are getting at, but could you please clarify for me just a little bit? You have an unknown number of tables, with unknown structure. You want to create a view that will combine these and rename the columns to append the table they came from?

Downloading shared datasources

If there are multiple developers, can we download the shared data sources
that are stored on the server? Or does each report developer need to create
their own?
Thanks,Hey all the developers can use the same shared datasource but through VSS
then the reports can be well controlled.
Amarnath
"Mark" wrote:
> If there are multiple developers, can we download the shared data sources
> that are stored on the server? Or does each report developer need to create
> their own?
> Thanks,

Download database to local and use

I have SQL database hosted by my ISP. Every now and again we log on and create new tables using user XXX1. After getting a backup of the database, I have restored it on my local machine. When running the application on local, I get an error because there is a new user in database called XXX1.

I would like to change the user from XXX1 to dbo on my local machine for all tables, stored procedures and views. How do I do this easily?

Thanks in advance!

Dave

Hi,

http://groups.google.de/group/microsoft.public.sqlserver.programming/browse_frm/thread/f1625d70fb765701

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Download and create a Report Project from a Report Server

Before I start coding ...

Is there any existing software/trick/hack to create a Report Project from the DataSources and Folders/Reports on a Report Server, i.e. the inverse of Deployment?

Would there be anybody interested in such a thing, and, if not, why not?

I don't think there's any way to create a report project from the report server, but you can get the actual RDL files from the server.

Go to your Report Manager. (http://ServerName/Reports)
Drill through to the report you want the definition of.
Click on the properties tab.
Click on General from the options on the left.
Under the 'Report Definition' section, click 'Edit'.
This will prompt you to download the RDL file.
You can then add this to your report project that you already have created.

Hope this helps.

Jarret

Doubt

CREATE PROCEDURE emp_ins @.no integer,@.name varchar(20),@.sa integer,@.msg
varchar(20)output AS
if @.no<0
BEGIN
set @.msg='Empno>0'
RAISEERROR(@.msg,10,1)
end
insert into emp values(@.no,@.name,@.sa)

my requirement is i want to implement normal validations when the user exceeds the condition i want to store the message into out parameter and i want to return that message into the application. is it possible or not
waiting for valuable reply
Sri

If you want to abort processing after the error, I would add a RETURN statement immediately after the RAISERROR (still in the BEGIN...END block.)

Code Snippet

CREATE PROCEDURE Emp_Ins
( @.no integer,
@.name varchar(20),
@.sa integer,
@.msg varchar(20) OUTPUT
AS
IF ( @.no < 0 )
BEGIN
SET @.msg = 'Empno > 0'
RAISEERROR( @.msg, 10, 1 )
RETURN
END
INSERT INTO Emp VALUES ( @.no, @.name, @.sa )

|||Thank u Arnie.
Before u r reply i tried with the same code i got it additionally u given a point that with return statement transaction will be aborted.Arnie u may feel that it may be silly in case of multiple ifs how to write the program like prevoius program because i m ne to sqlserver envi i m much familiar with envi thats wy i m requesting u
Thanks and Regards
Sridhar
|||

Baba urf Sivaji wrote:

i want to return that message into the application. is it possible or not

You should use the proper Severity number & it should be 16 rather 10. You can return the control after the Raiseerror. Now the message will be captured on your UI & the control alos return back to the UI.

RAISERROR (@.msg,16,1);

Return;

|||You need to have the application declare an OUTPUT parameter to capture the value of @.Msg.

double count

Hello
got a small group by problem, i cant figure out how to divide the count with
"total" count for "each" day :)
CREATE TABLE #Test (
A char(1) NOT NULL,
B char(1) NOT NULL,
Somedate datetime NOT NULL
)
INSERT INTO #Test(A,B)VALUES('A','A','2001-01-01')
INSERT INTO #Test(A,B)VALUES('A','A','2001-01-01')
INSERT INTO #Test(A,B)VALUES('A','B','2001-01-01')
INSERT INTO #Test(A,B)VALUES('A','C','2001-01-01')
INSERT INTO #Test(A,B)VALUES('A','C','2001-01-01')
INSERT INTO #Test(A,B)VALUES('B','A','2001-01-01')
INSERT INTO #Test(A,B)VALUES('B','A','2001-01-01')
INSERT INTO #Test(A,B)VALUES('B','B','2001-01-01')
INSERT INTO #Test(A,B)VALUES('B','A','2001-01-02')
INSERT INTO #Test(A,B)VALUES('B','A','2001-01-02')
INSERT INTO #Test(A,B)VALUES('B','B','2001-01-02')
SELECT * FROM #Test
/* A COUNT COUNT/TOTAL Somedate
A 2 0.40 2001-01-01
A 1 0.20 2001-01-01
A 2 0.40 2001-01-01
B 1 0.50 2001-01-01
B 1 0.50 2001-01-01
B 1 0.50 2001-01-02
B 1 0.50 2001-01-02
*/
DROP TABLE #TestSELECT
T.A,
T.B,
T.SomeDate,
COUNT(*),
(COUNT(*) * 1.0) / T1.theCount
FROM #Test T
JOIN
(
SELECT
A,
COUNT(*) AS TheCount
FROM #Test
GROUP BY A
) T1 ON T1.A = T.A
GROUP BY
T.A,
T.B,
T.SomeDate,
T1.theCount
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:OZIvJGnzFHA.908@.tk2msftngp13.phx.gbl...
> Hello
> got a small group by problem, i cant figure out how to divide the count
> with
> "total" count for "each" day :)
>
> CREATE TABLE #Test (
> A char(1) NOT NULL,
> B char(1) NOT NULL,
> Somedate datetime NOT NULL
> )
>
> INSERT INTO #Test(A,B)VALUES('A','A','2001-01-01')
> INSERT INTO #Test(A,B)VALUES('A','A','2001-01-01')
> INSERT INTO #Test(A,B)VALUES('A','B','2001-01-01')
> INSERT INTO #Test(A,B)VALUES('A','C','2001-01-01')
> INSERT INTO #Test(A,B)VALUES('A','C','2001-01-01')
> INSERT INTO #Test(A,B)VALUES('B','A','2001-01-01')
> INSERT INTO #Test(A,B)VALUES('B','A','2001-01-01')
> INSERT INTO #Test(A,B)VALUES('B','B','2001-01-01')
> INSERT INTO #Test(A,B)VALUES('B','A','2001-01-02')
> INSERT INTO #Test(A,B)VALUES('B','A','2001-01-02')
> INSERT INTO #Test(A,B)VALUES('B','B','2001-01-02')
> SELECT * FROM #Test
> /* A COUNT COUNT/TOTAL Somedate
> A 2 0.40 2001-01-01
> A 1 0.20 2001-01-01
> A 2 0.40 2001-01-01
> B 1 0.50 2001-01-01
> B 1 0.50 2001-01-01
> B 1 0.50 2001-01-02
> B 1 0.50 2001-01-02
> */
> DROP TABLE #Test
>|||Fix your table to have a key so that there are no duplicates. That will save
you from dealing with complex formulations for simple queries in the first
place.
Anith

Dose it make sense to create indexted views on a single table?

Hi, all experts here,

Thank you very much for your kind attention.

I am wondering if there is any sense to create indexed views on single table? I simple want to improve the report query performance as most of the reports data are from a single table. As views most of the time are created as for joined across tables.

Thank you very much for your advices and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

H Helen,

Theres is no point creating a indexed view with a view that has the columns from single table.

Infact, you will creating an overhead on your transactions as they will have to keep the indexes on view upto date on top the indexes on the table.

regards

Jag

|||

Hi, Jag,

Thank you very much for your advices and kind help. It's been very helpful.

With best regards,

Yours sincerely,

|||

Jag,

What if the single table was a transaction table, and the view was an aggregation of that transaction table.

I was under the impression that that was one of the primary reasons to utilise Indexed Views, that the aggregated data is available without having to transverse the underlying table rows, therebye improving query performance.

Cheers,

Will

|||

i would agree with Will ... though the view is refering single table... if this view is aggregation and the table is having millions of records, it make sense that you may create indexed view.

Madhu

|||

Hi Will,

You are right, if view is going to be storing aggregated data, then It is worth creating an index on a view even if is a single table.

regards

Jag

Dont want to use NT Account

Good Day,
I would like to create 3 roles in SQL Server 2000 Reporting Services
but do not have corresponding NT groups.
The sys-admin has asked that I do not create these 3 NT groups to use
in Reporting Services, but to find some other way to create these roles
without corresponding NT groups. The 40 or so Users that would be
assigned to these 3 groups have NT user accounts. Does anyone know if
there is a way I can do this? (i hope that all made sense - clear as
mud)
The Books Online state "you must specify domain groups or users", so
I'm assuming this cannot easily be done. I also saw another post in
this forum that said if you do not use NT users/groups you must
implement some sort of custom authentication. Does anyone have a link
to some documentation on this?
Thanks!
MichelleI think you're mixing role and group, but I think I get the gist of what
you're saying.
You can create the roles in RS independent of any groups/users you have.
If you have multiple users to assign these roles to, you really should
create NT groups for them. Otherwise the managment cost just rises too
much. What is the administrator's justification for not allowing you to
create the desired groups? Can you reuse any existing groups?
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
<Michelle@.bwalk.com> wrote in message
news:1106774994.929964.6650@.z14g2000cwz.googlegroups.com...
> Good Day,
> I would like to create 3 roles in SQL Server 2000 Reporting Services
> but do not have corresponding NT groups.
> The sys-admin has asked that I do not create these 3 NT groups to use
> in Reporting Services, but to find some other way to create these roles
> without corresponding NT groups. The 40 or so Users that would be
> assigned to these 3 groups have NT user accounts. Does anyone know if
> there is a way I can do this? (i hope that all made sense - clear as
> mud)
> The Books Online state "you must specify domain groups or users", so
> I'm assuming this cannot easily be done. I also saw another post in
> this forum that said if you do not use NT users/groups you must
> implement some sort of custom authentication. Does anyone have a link
> to some documentation on this?
>
> Thanks!
> Michelle
>|||Note that you don't have to create groups in the domain. You can have a
local group that you add domain users or domain groups. Then you assign that
group to a role. Then the admin is out of the loop and everyone is happy.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Lukasz Pawlowski [MSFT]" <lukaszp@.online.microsoft.com> wrote in message
news:OK4btSCBFHA.2584@.TK2MSFTNGP09.phx.gbl...
> I think you're mixing role and group, but I think I get the gist of what
> you're saying.
> You can create the roles in RS independent of any groups/users you have.
> If you have multiple users to assign these roles to, you really should
> create NT groups for them. Otherwise the managment cost just rises too
> much. What is the administrator's justification for not allowing you to
> create the desired groups? Can you reuse any existing groups?
> -Lukasz
>
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> <Michelle@.bwalk.com> wrote in message
> news:1106774994.929964.6650@.z14g2000cwz.googlegroups.com...
> > Good Day,
> >
> > I would like to create 3 roles in SQL Server 2000 Reporting Services
> > but do not have corresponding NT groups.
> >
> > The sys-admin has asked that I do not create these 3 NT groups to use
> > in Reporting Services, but to find some other way to create these roles
> > without corresponding NT groups. The 40 or so Users that would be
> > assigned to these 3 groups have NT user accounts. Does anyone know if
> > there is a way I can do this? (i hope that all made sense - clear as
> > mud)
> >
> > The Books Online state "you must specify domain groups or users", so
> > I'm assuming this cannot easily be done. I also saw another post in
> > this forum that said if you do not use NT users/groups you must
> > implement some sort of custom authentication. Does anyone have a link
> > to some documentation on this?
> >
> >
> > Thanks!
> > Michelle
> >
>|||Hi guys,
My sys admin believes that creating these 3 NT groups will make make
the active directory tough to manage. I think that if these 3 whole
groups will set the managablilty over the top then perhaps they've got
other issues, but I'm no sys admin and I'm not privy to how they have
been setting up the NT user accounts and groups.
Would you happen to have instructions or documentation on how I add
roles in RS without referencing corresponding NT groups? All of the
documentation I have found only deals with NT accounts, and when I gave
it a try an error was thrown because an account wasn't found. What I
would like to do is create my own role in RS that I can add the
existing NT User accounts to.
Thanks again!
Michelle|||> My sys admin believes that creating these 3 NT groups will make make
> the active directory tough to manage.
Your admin likely doesn't want to handle the requests to add/'remove people
from the group. Can you think of creative solutions to this problem? Can
s/he assign you the permission to add/remove users from these groups?
> Would you happen to have instructions or documentation on how I add
> roles in RS without referencing corresponding NT groups? All of the
> documentation I have found only deals with NT accounts, and when I gave
> it a try an error was thrown because an account wasn't found. What I
> would like to do is create my own role in RS that I can add the
> existing NT User accounts to.
In RS a role has a particular meaning - it is a set of tasks you can assign
to a user or group on a particular item/scope. RS itself does not have a
groups mechanism. Natively we use the windows groups functionality. If
local machine groups do not suffice for you, you'll need to convince your
admin.
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
<Michelle@.bwalk.com> wrote in message
news:1106838254.192842.124560@.z14g2000cwz.googlegroups.com...
> Hi guys,
> My sys admin believes that creating these 3 NT groups will make make
> the active directory tough to manage. I think that if these 3 whole
> groups will set the managablilty over the top then perhaps they've got
> other issues, but I'm no sys admin and I'm not privy to how they have
> been setting up the NT user accounts and groups.
> Would you happen to have instructions or documentation on how I add
> roles in RS without referencing corresponding NT groups? All of the
> documentation I have found only deals with NT accounts, and when I gave
> it a try an error was thrown because an account wasn't found. What I
> would like to do is create my own role in RS that I can add the
> existing NT User accounts to.
>
> Thanks again!
> Michelle
>

Don't delete data from linkedserver table .

when our system upgrade to sql server 2005 ,and create a linked server to localhost database , the script of create linkedserver is :

/****** Object: LinkedServer [localhost_boston] Script Date: 05/07/2006 18:37:15 ******/
EXEC master.dbo.sp_addlinkedserver @.server = N'localhost_boston',@.srvproduct='', @.provider=N'SQLNCLI', @.datasrc=N'localhost', @.provstr=N'UID=sa;PWD=007;', @.catalog=N'boston'
GO
EXEC master.dbo.sp_serveroption @.server=N'localhost_boston', @.optname=N'collation compatible', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'localhost_boston', @.optname=N'data access', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'localhost_boston', @.optname=N'dist', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'localhost_boston', @.optname=N'pub', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'localhost_boston', @.optname=N'rpc', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'localhost_boston', @.optname=N'rpc out', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'localhost_boston', @.optname=N'sub', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'localhost_boston', @.optname=N'connect timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'localhost_boston', @.optname=N'collation name', @.optvalue=null
GO
EXEC master.dbo.sp_serveroption @.server=N'localhost_boston', @.optname=N'lazy schema validation', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'localhost_boston', @.optname=N'query timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'localhost_boston', @.optname=N'use remote collation', @.optvalue=N'true'

when I run the sql on local server :

delete a from localhost_boston.boston.dbo.Spot as a,
bica.tmpspot as b where a.spotid=b.spotid

it display 7 rows have deleted ,but when I run:

select * from localhost_boston.boston.dbo.Spot as a
join bica. tmpspot as b on a.spotid=b.spotid

find the 7 rows have not delete,I check the localhost_boston.boston.dbo.Spot

table ,and find 7 rows have deleted that it is not a.spotid=b.spotid ,why ?

If I run :

delete from localhost_boston.boston.dbo.Spot where spotid=28147

and the row can deleted .why ?

anyone can talk me how to do I can !

Thanks.

Your delete syntax is improper. You should use a subquery to constrain your deleted rows, not a join.

Code Snippet

delete localhost_boston.boston.dbo.Spot where spotid in (select spotid from bica.tmpspot)

Joins create a result set and using a generic join syntax you can get unexpected result sets. Always test your joins in a select statement 1st to make sure you are getting the results you expect.

Don't delete data from linkedserver table .

when our system upgrade to sql server 2005 ,and create a linked server to localhost database , the script of create linkedserver is :

/****** Object: LinkedServer [localhost_boston] Script Date: 05/07/2006 18:37:15 ******/
EXEC master.dbo.sp_addlinkedserver @.server = N'localhost_boston',@.srvproduct='', @.provider=N'SQLNCLI', @.datasrc=N'localhost', @.provstr=N'UID=sa;PWD=007;', @.catalog=N'boston'
GO
EXEC master.dbo.sp_serveroption @.server=N'localhost_boston', @.optname=N'collation compatible', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'localhost_boston', @.optname=N'data access', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'localhost_boston', @.optname=N'dist', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'localhost_boston', @.optname=N'pub', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'localhost_boston', @.optname=N'rpc', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'localhost_boston', @.optname=N'rpc out', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'localhost_boston', @.optname=N'sub', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'localhost_boston', @.optname=N'connect timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'localhost_boston', @.optname=N'collation name', @.optvalue=null
GO
EXEC master.dbo.sp_serveroption @.server=N'localhost_boston', @.optname=N'lazy schema validation', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'localhost_boston', @.optname=N'query timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'localhost_boston', @.optname=N'use remote collation', @.optvalue=N'true'

when I run the sql on local server :

delete a from localhost_boston.boston.dbo.Spot as a,
bica.tmpspot as b where a.spotid=b.spotid

it display 7 rows have deleted ,but when I run:

select * from localhost_boston.boston.dbo.Spot as a
join bica. tmpspot as b on a.spotid=b.spotid

find the 7 rows have not delete,I check the localhost_boston.boston.dbo.Spot

table ,and find 7 rows have deleted that it is not a.spotid=b.spotid ,why ?

If I run :

delete from localhost_boston.boston.dbo.Spot where spotid=28147

and the row can deleted .why ?

anyone can talk me how to do I can !

Thanks.

Your delete syntax is improper. You should use a subquery to constrain your deleted rows, not a join.

Code Snippet

delete localhost_boston.boston.dbo.Spot where spotid in (select spotid from bica.tmpspot)

Joins create a result set and using a generic join syntax you can get unexpected result sets. Always test your joins in a select statement 1st to make sure you are getting the results you expect.

Don

Hi every one,
I learn a lot reading your comments,
Does runnning "UPDATE STATISTICS MYBIGTABLE" will affect
performance or create locks ?
I mean : While UPDATE STATISTICS is runnning, will it
affect performance ?
Thanks !
DonYes it will have an impact. Whether this will be noticeable to users are
not is another question...
Regarding locking, SQL Server takes two types of locks:
"Sch-S: Schema Stability Lock
--
This lock ensures that a schema element, such as a table or index, will
not be dropped while any session holds a schema stability lock on the
schema element.
Sch-M-UPD-STATS: Schema Modification Lock
---
This is a non-blocking lock that is used by the system to ensure that
only one automatic UPDATE STATISTICS process is run against a table at
any given point in time. The sp_lock stored procedure will report this
lock has having a type = TAB, resouce = UPD-STATS and mode = SCH-M."
Above information found here:
INF: How SQL Server 7.0 and SQL Server 2000 Autostats Work
http://support.microsoft.com/kb/q195565/
They should not block usual DML queries (insert, update, delete), but
may block DDL queries (drop table, alter table, alter database, etc).
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
PERFORMANCE DURING UPDATING STATS wrote:
> Hi every one,
> I learn a lot reading your comments,
> Does runnning "UPDATE STATISTICS MYBIGTABLE" will affect
> performance or create locks ?
> I mean : While UPDATE STATISTICS is runnning, will it
> affect performance ?
> Thanks !
> Don|||"PERFORMANCE DURING UPDATING STATS" <anonymous@.discussions.microsoft.com>
wrote in message news:1ecd01c4b5d7$297b9ea0$a401280a@.phx.gbl...
> Does runnning "UPDATE STATISTICS MYBIGTABLE" will affect
> performance or create locks ?
> I mean : While UPDATE STATISTICS is runnning, will it
> affect performance ?
It will have an effect on performance, in that it uses system resources.
How much of an effect is dependant on your server's horsepower, the size of
the table you're updating statistics for, etc... It will, like any other
read operation, create shared locks, but I have not seen it create exclusive
locks. So blocking should not be an issue.|||"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:OtavF8dtEHA.2072@.tk2msftngp13.phx.gbl...
> any given point in time. The sp_lock stored procedure will report this
> lock has having a type = TAB, resouce = UPD-STATS and mode = SCH-M."
Mark,
I've never seen the SCH-M lock; I always get only SCH-S when I use
UPDATE STATISTICS on my systems. Is there an option that controls which
lock types are taken?|||Adam,
No, not that I'm aware of.
Adam Machanic wrote:
> I've never seen the SCH-M lock; I always get only SCH-S when I use
> UPDATE STATISTICS on my systems. Is there an option that controls which
> lock types are taken?
>|||UPDATE STATISTICS doesn't use locks, but it will affect performance, because
data pages are randomly accessed from the table to create the statistics.
Most likely not all of these pages are in memory and have to be read from
disk.
Jacco Schalkwijk
SQL Server MVP
"PERFORMANCE DURING UPDATING STATS" <anonymous@.discussions.microsoft.com>
wrote in message news:1ecd01c4b5d7$297b9ea0$a401280a@.phx.gbl...
> Hi every one,
> I learn a lot reading your comments,
> Does runnning "UPDATE STATISTICS MYBIGTABLE" will affect
> performance or create locks ?
> I mean : While UPDATE STATISTICS is runnning, will it
> affect performance ?
> Thanks !
> Don

Don

Hi every one,
I learn a lot reading your comments,
Does runnning "UPDATE STATISTICS MYBIGTABLE" will affect
performance or create locks ?
I mean : While UPDATE STATISTICS is runnning, will it
affect performance ?
Thanks !
Don
Yes it will have an impact. Whether this will be noticeable to users are
not is another question...
Regarding locking, SQL Server takes two types of locks:
"Sch-S: Schema Stability Lock
This lock ensures that a schema element, such as a table or index, will
not be dropped while any session holds a schema stability lock on the
schema element.
Sch-M-UPD-STATS: Schema Modification Lock
This is a non-blocking lock that is used by the system to ensure that
only one automatic UPDATE STATISTICS process is run against a table at
any given point in time. The sp_lock stored procedure will report this
lock has having a type = TAB, resouce = UPD-STATS and mode = SCH-M."
Above information found here:
INF: How SQL Server 7.0 and SQL Server 2000 Autostats Work
http://support.microsoft.com/kb/q195565/
They should not block usual DML queries (insert, update, delete), but
may block DDL queries (drop table, alter table, alter database, etc).
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
PERFORMANCE DURING UPDATING STATS wrote:
> Hi every one,
> I learn a lot reading your comments,
> Does runnning "UPDATE STATISTICS MYBIGTABLE" will affect
> performance or create locks ?
> I mean : While UPDATE STATISTICS is runnning, will it
> affect performance ?
> Thanks !
> Don
|||"PERFORMANCE DURING UPDATING STATS" <anonymous@.discussions.microsoft.com>
wrote in message news:1ecd01c4b5d7$297b9ea0$a401280a@.phx.gbl...
> Does runnning "UPDATE STATISTICS MYBIGTABLE" will affect
> performance or create locks ?
> I mean : While UPDATE STATISTICS is runnning, will it
> affect performance ?
It will have an effect on performance, in that it uses system resources.
How much of an effect is dependant on your server's horsepower, the size of
the table you're updating statistics for, etc... It will, like any other
read operation, create shared locks, but I have not seen it create exclusive
locks. So blocking should not be an issue.
|||"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:OtavF8dtEHA.2072@.tk2msftngp13.phx.gbl...
> any given point in time. The sp_lock stored procedure will report this
> lock has having a type = TAB, resouce = UPD-STATS and mode = SCH-M."
Mark,
I've never seen the SCH-M lock; I always get only SCH-S when I use
UPDATE STATISTICS on my systems. Is there an option that controls which
lock types are taken?
|||Adam,
No, not that I'm aware of.
Adam Machanic wrote:
> I've never seen the SCH-M lock; I always get only SCH-S when I use
> UPDATE STATISTICS on my systems. Is there an option that controls which
> lock types are taken?
>
|||UPDATE STATISTICS doesn't use locks, but it will affect performance, because
data pages are randomly accessed from the table to create the statistics.
Most likely not all of these pages are in memory and have to be read from
disk.
Jacco Schalkwijk
SQL Server MVP
"PERFORMANCE DURING UPDATING STATS" <anonymous@.discussions.microsoft.com>
wrote in message news:1ecd01c4b5d7$297b9ea0$a401280a@.phx.gbl...
> Hi every one,
> I learn a lot reading your comments,
> Does runnning "UPDATE STATISTICS MYBIGTABLE" will affect
> performance or create locks ?
> I mean : While UPDATE STATISTICS is runnning, will it
> affect performance ?
> Thanks !
> Don

Don

Hi every one,
I learn a lot reading your comments,
Does runnning "UPDATE STATISTICS MYBIGTABLE" will affect
performance or create locks ?
I mean : While UPDATE STATISTICS is runnning, will it
affect performance ?
Thanks !
DonYes it will have an impact. Whether this will be noticeable to users are
not is another question...
Regarding locking, SQL Server takes two types of locks:
"Sch-S: Schema Stability Lock
--
This lock ensures that a schema element, such as a table or index, will
not be dropped while any session holds a schema stability lock on the
schema element.
Sch-M-UPD-STATS: Schema Modification Lock
---
This is a non-blocking lock that is used by the system to ensure that
only one automatic UPDATE STATISTICS process is run against a table at
any given point in time. The sp_lock stored procedure will report this
lock has having a type = TAB, resouce = UPD-STATS and mode = SCH-M."
Above information found here:
INF: How SQL Server 7.0 and SQL Server 2000 Autostats Work
http://support.microsoft.com/kb/q195565/
They should not block usual DML queries (insert, update, delete), but
may block DDL queries (drop table, alter table, alter database, etc).
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
PERFORMANCE DURING UPDATING STATS wrote:
> Hi every one,
> I learn a lot reading your comments,
> Does runnning "UPDATE STATISTICS MYBIGTABLE" will affect
> performance or create locks ?
> I mean : While UPDATE STATISTICS is runnning, will it
> affect performance ?
> Thanks !
> Don|||"PERFORMANCE DURING UPDATING STATS" <anonymous@.discussions.microsoft.com>
wrote in message news:1ecd01c4b5d7$297b9ea0$a401280a@.phx.gbl...
> Does runnning "UPDATE STATISTICS MYBIGTABLE" will affect
> performance or create locks ?
> I mean : While UPDATE STATISTICS is runnning, will it
> affect performance ?
It will have an effect on performance, in that it uses system resources.
How much of an effect is dependant on your server's horsepower, the size of
the table you're updating statistics for, etc... It will, like any other
read operation, create shared locks, but I have not seen it create exclusive
locks. So blocking should not be an issue.|||"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:OtavF8dtEHA.2072@.tk2msftngp13.phx.gbl...
> any given point in time. The sp_lock stored procedure will report this
> lock has having a type = TAB, resouce = UPD-STATS and mode = SCH-M."
Mark,
I've never seen the SCH-M lock; I always get only SCH-S when I use
UPDATE STATISTICS on my systems. Is there an option that controls which
lock types are taken?|||Adam,
No, not that I'm aware of.
Adam Machanic wrote:
> I've never seen the SCH-M lock; I always get only SCH-S when I use
> UPDATE STATISTICS on my systems. Is there an option that controls which
> lock types are taken?
>|||UPDATE STATISTICS doesn't use locks, but it will affect performance, because
data pages are randomly accessed from the table to create the statistics.
Most likely not all of these pages are in memory and have to be read from
disk.
--
Jacco Schalkwijk
SQL Server MVP
"PERFORMANCE DURING UPDATING STATS" <anonymous@.discussions.microsoft.com>
wrote in message news:1ecd01c4b5d7$297b9ea0$a401280a@.phx.gbl...
> Hi every one,
> I learn a lot reading your comments,
> Does runnning "UPDATE STATISTICS MYBIGTABLE" will affect
> performance or create locks ?
> I mean : While UPDATE STATISTICS is runnning, will it
> affect performance ?
> Thanks !
> Don

Domain not visible

I just upgraded my 2000 installation to Sql Server 2005 Developer. When I try to create a new login I can only use local accounts. Search and the Locations only displays the computer name and not the domain. Any help would be greatly appreciated,

Robert van Poelgeest

Hi Robert - I assume you are using SSMS but correct me if I'm wrong.

Are you running SSMS (client) on the same machine as the Server?

Are you logged into client machine with a domain account from domain you want to add?

Can you add the desired domain account using the CREATE LOGIN command?

|||

Hi Bruce,

Yes I am using SSMS on the server itself and yes I am logged in using a domain account. I haven't tried using a CREATE LOGIN account but I will.

update: This whole problem was caused by a dns configuration problem and was not caused by anything in Sqlserver

Domain group server access?

Hi,
If a user needs access to a databases and I carry out the following steps:
1) Create a domain group
2) Put the users login details in this group
3) Give the domain group access to SQL Server via Enterprise Manager
4) Give the domain group database access via Enterprise Manager
Do I also need to give this domain group direct server access by adding it
to a group in:
Computer Management\Local Users and Groups\Groups ?
Ta
wendyalso, we are using active directory.
wendy
"Woo" wrote:

> Hi,
> If a user needs access to a databases and I carry out the following steps:
> 1) Create a domain group
> 2) Put the users login details in this group
> 3) Give the domain group access to SQL Server via Enterprise Manager
> 4) Give the domain group database access via Enterprise Manager
> Do I also need to give this domain group direct server access by adding it
> to a group in:
> Computer Management\Local Users and Groups\Groups ?
> Ta
> wendy|||No you don't need to give OS level permissions on the server
that is running SQL Server for the group to access SQL
Server. You only need to add the windows group to the SQL
Server logins (and whatever databases).
-Sue
On Fri, 3 Nov 2006 04:14:02 -0800, Woo
<Woo@.discussions.microsoft.com> wrote:

>Hi,
>If a user needs access to a databases and I carry out the following steps:
>1) Create a domain group
>2) Put the users login details in this group
>3) Give the domain group access to SQL Server via Enterprise Manager
>4) Give the domain group database access via Enterprise Manager
>Do I also need to give this domain group direct server access by adding it
>to a group in:
>Computer Management\Local Users and Groups\Groups ?
>Ta
>wendy|||And you will have to give the windows group permissions to access tables,
views, stored procedures, etc.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:bcjmk2h4eoundvep74genb5d43ldjveog2@.
4ax.com...
> No you don't need to give OS level permissions on the server
> that is running SQL Server for the group to access SQL
> Server. You only need to add the windows group to the SQL
> Server logins (and whatever databases).
> -Sue
> On Fri, 3 Nov 2006 04:14:02 -0800, Woo
> <Woo@.discussions.microsoft.com> wrote:
>
>

Domain group accounts

I have a problem with database access that I would like to sort out.
1. I gave a Windows 2003 server with SQL 2000 (SP3)
2. I decided to create different Windows Groups and add Windows accounts to
them to access different databases
3. This is where it starts giving me problem
4. I noticed that user belonging to one group (ie. database1 access) also
had access to database2
4. To test this I created a group called Test and addedd this to
Security/logins in Enterprise Manager and assigned this group access to
database1
5. I placed my own account into this group and that gave me access to all
other databases as well, even though the group Test is only set to
database1.
What is problem here'
OweTry running xp_logininfo to report the permission path(s) for the account.
For example
EXEC master..xp_logininfo 'MyDomain\Test', 'all'
For a Windows authenticated user to gain access to a database, one of the
following must be true:
- the account was granted database access
- the account is member of a Windows group than was granted database access
- the account is the database owner
- the account is a member of a sysadmin fixed server role
- the guest account is enabled in the database
Hope this helps.
Dan Guzman
SQL Server MVP
"Owe Armandt" <owe.armandt@.visma.se> wrote in message
news:ugQDkVnyFHA.2696@.TK2MSFTNGP10.phx.gbl...
>I have a problem with database access that I would like to sort out.
> 1. I gave a Windows 2003 server with SQL 2000 (SP3)
> 2. I decided to create different Windows Groups and add Windows accounts
> to them to access different databases
> 3. This is where it starts giving me problem
> 4. I noticed that user belonging to one group (ie. database1 access) also
> had access to database2
> 4. To test this I created a group called Test and addedd this to
> Security/logins in Enterprise Manager and assigned this group access to
> database1
> 5. I placed my own account into this group and that gave me access to all
> other databases as well, even though the group Test is only set to
> database1.
> What is problem here'
>
> Owe
>|||I helped a bit, I will test further tomorrow.
I found out that I (my windows account) happend to be owner of the database.
I have now changed the owner to 'sa' and then I get access only if the group
I belong to is set to have DB access.
One thing bothers me though, my college do not belong to any group that has
access to any database and still he could access the database that I was the
owner of.
I don't think he is part of admin ro anything, we try to kep our avccounts
clear in order to be as alike the users account as possible.
This is what I will try to check out tomorrow - I will be back tomorrow with
some info
Owe
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> skrev i meddelandet
news:OzfN9jnyFHA.464@.TK2MSFTNGP15.phx.gbl...
> Try running xp_logininfo to report the permission path(s) for the account.
> For example
> EXEC master..xp_logininfo 'MyDomain\Test', 'all'
> For a Windows authenticated user to gain access to a database, one of the
> following must be true:
> - the account was granted database access
> - the account is member of a Windows group than was granted database
> access
> - the account is the database owner
> - the account is a member of a sysadmin fixed server role
> - the guest account is enabled in the database
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Owe Armandt" <owe.armandt@.visma.se> wrote in message
> news:ugQDkVnyFHA.2696@.TK2MSFTNGP10.phx.gbl...
>

Domain and windows authentication

Hi,
We have a application that uses windows authentication to connect to sql
server. We have a domain and simply just create a login in sql server for
each user in the domain.
We have setup this application in the company that doesn't have domain.
We're not going to change the source of the application to accept sql server
authentication.
Can we still use the windows authentication to allow the users to log in?
Suppose that each user logs in his computer as administrator of that
computer. Can we prefix these administrators with the name of their
computers to create login in sql server and still use windows
authentication?
Any help would be greatly appreciated.
Amin
Microsoft SQL Server support only Domain Authentification, no Machine
Account Authentification (except the local machine of the sql server),
because Workgroups are not trustable.
Hope this helps,
Jens Smeyer
"Amin Sobati" <amins@.morva.net> schrieb im Newsbeitrag
news:%23ahXq45gEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Hi,
> We have a application that uses windows authentication to connect to sql
> server. We have a domain and simply just create a login in sql server for
> each user in the domain.
> We have setup this application in the company that doesn't have domain.
> We're not going to change the source of the application to accept sql
server
> authentication.
> Can we still use the windows authentication to allow the users to log in?
> Suppose that each user logs in his computer as administrator of that
> computer. Can we prefix these administrators with the name of their
> computers to create login in sql server and still use windows
> authentication?
> Any help would be greatly appreciated.
> Amin
>
|||Jens,
Therefore in workgroups there's no other solution except using sql server
authentication?
"Jens Smeyer" <MrIgel[RejectSpam|RemoveBracket]@.gmx.de> wrote in message
news:OCvmQg7gEHA.2764@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Microsoft SQL Server support only Domain Authentification, no Machine
> Account Authentification (except the local machine of the sql server),
> because Workgroups are not trustable.
> Hope this helps,
> Jens Smeyer
>
> "Amin Sobati" <amins@.morva.net> schrieb im Newsbeitrag
> news:%23ahXq45gEHA.1764@.TK2MSFTNGP10.phx.gbl...
for[vbcol=seagreen]
> server
in?
>
|||You should be able to use NT authentication by doing the following:
1. Create a local account on the SQL Server machine that matches each
individual users local machine account. Make sure the password is the same
for both accounts.
2. Create a login within SQL Server for each of these logins.
3. NT authentication should now work.
This could be difficult to administer becaues each time a user changes his
local opassword the pasword has to be changed at the SQL Server machine as
well.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||Rand,
What if users log into their computer with administrator account?
Necessarily they must use different user names?
Amin
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:Qxq9sH9gEHA.3340@.cpmsftngxa06.phx.gbl...
> You should be able to use NT authentication by doing the following:
> 1. Create a local account on the SQL Server machine that matches each
> individual users local machine account. Make sure the password is the same
> for both accounts.
> 2. Create a login within SQL Server for each of these logins.
> 3. NT authentication should now work.
> This could be difficult to administer becaues each time a user changes his
> local opassword the pasword has to be changed at the SQL Server machine as
> well.
> Rand
>
> This posting is provided "as is" with no warranties and confers no rights.
>

Domain and windows authentication

Hi,
We have a application that uses windows authentication to connect to sql
server. We have a domain and simply just create a login in sql server for
each user in the domain.
We have setup this application in the company that doesn't have domain.
We're not going to change the source of the application to accept sql server
authentication.
Can we still use the windows authentication to allow the users to log in?
Suppose that each user logs in his computer as administrator of that
computer. Can we prefix these administrators with the name of their
computers to create login in sql server and still use windows
authentication?
Any help would be greatly appreciated.
AminMicrosoft SQL Server support only Domain Authentification, no Machine
Account Authentification (except the local machine of the sql server),
because Workgroups are not trustable.
Hope this helps,
Jens Süßmeyer
"Amin Sobati" <amins@.morva.net> schrieb im Newsbeitrag
news:%23ahXq45gEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Hi,
> We have a application that uses windows authentication to connect to sql
> server. We have a domain and simply just create a login in sql server for
> each user in the domain.
> We have setup this application in the company that doesn't have domain.
> We're not going to change the source of the application to accept sql
server
> authentication.
> Can we still use the windows authentication to allow the users to log in?
> Suppose that each user logs in his computer as administrator of that
> computer. Can we prefix these administrators with the name of their
> computers to create login in sql server and still use windows
> authentication?
> Any help would be greatly appreciated.
> Amin
>|||Jens,
Therefore in workgroups there's no other solution except using sql server
authentication?
"Jens Süßmeyer" <MrIgel[RejectSpam|RemoveBracket]@.gmx.de> wrote in message
news:OCvmQg7gEHA.2764@.TK2MSFTNGP11.phx.gbl...
> Microsoft SQL Server support only Domain Authentification, no Machine
> Account Authentification (except the local machine of the sql server),
> because Workgroups are not trustable.
> Hope this helps,
> Jens Süßmeyer
>
> "Amin Sobati" <amins@.morva.net> schrieb im Newsbeitrag
> news:%23ahXq45gEHA.1764@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> > We have a application that uses windows authentication to connect to sql
> > server. We have a domain and simply just create a login in sql server
for
> > each user in the domain.
> > We have setup this application in the company that doesn't have domain.
> > We're not going to change the source of the application to accept sql
> server
> > authentication.
> > Can we still use the windows authentication to allow the users to log
in?
> > Suppose that each user logs in his computer as administrator of that
> > computer. Can we prefix these administrators with the name of their
> > computers to create login in sql server and still use windows
> > authentication?
> > Any help would be greatly appreciated.
> > Amin
> >
> >
>|||You should be able to use NT authentication by doing the following:
1. Create a local account on the SQL Server machine that matches each
individual users local machine account. Make sure the password is the same
for both accounts.
2. Create a login within SQL Server for each of these logins.
3. NT authentication should now work.
This could be difficult to administer becaues each time a user changes his
local opassword the pasword has to be changed at the SQL Server machine as
well.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Rand,
What if users log into their computer with administrator account?
Necessarily they must use different user names?
Amin
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:Qxq9sH9gEHA.3340@.cpmsftngxa06.phx.gbl...
> You should be able to use NT authentication by doing the following:
> 1. Create a local account on the SQL Server machine that matches each
> individual users local machine account. Make sure the password is the same
> for both accounts.
> 2. Create a login within SQL Server for each of these logins.
> 3. NT authentication should now work.
> This could be difficult to administer becaues each time a user changes his
> local opassword the pasword has to be changed at the SQL Server machine as
> well.
> Rand
>
> This posting is provided "as is" with no warranties and confers no rights.
>