More recursive questions

I have a list of categories that I have to find the path to each
my table is set up like
ID CategoryNum CategoryName ParentID
1 AA Top Level 1 0
2 AB Top Level 2 0
3 BA Second Level 1 1
I need my query/stored proc to return
Tope Level 1/Second Level 1 if I pass in the integer of 3
I can do this in programming but cant seem to wrap my head around it in SQL Server
TIA for the helpHi,
Is your requirement to get a Tree structure from SQL Server?
SQL Server has got FOR XML clause which will return the data in hierarchial manner. But it does have implementaiton in returning n-level tree. You have to use EXPLICIT method which will require you to do a lot of coding to get a hierarchial Tree.
May be if you can explain more on the format you would like to get the data, can help.
Thanks.|||no really just an arraylist (working in vb) of the output (Top Level 1/Second Level 1 etc. etc. ) - trying to do it on sqlserver because the recursive code (even thought the maximum level is 5) is taking too long to run
|||Dylan,
Try the link below most things you want to do with Arrays in SQL is covered by Joe Celko, browse his book in the link at your local book store. Hope this helps.
http://www.onlamp.com/pub/a/onlamp/2004/08/05/hierarchical_sql.html|||okay some of that makes sense - doing this in SQL is really really new to me - the 'Searching For Superiors' section seems to cover what I need but his example uses a path string to show the path to the node - I dont have that I am using a child sub child method
oh this is so fun|||Joe Celko is an ANSI SQL purist so what you are looking for he did not cover because he thinks most things you do in the database is covered by ANSI SQL. This new link I have given you before he uses T-SQL and there is code files you can download to play with. BTW click on 2000 so you don't have to go through old code for earlier versions. Hope this helps
http://www.sommarskog.se/arrays-in-sql.html#iterative|||

You can always utilize recursion within self-joining tables. For example a function to "expand" the hierarchy (assuming the table name is CATEGORIES)

CREATE Function [dbo].[GetParent](@.currentid int,@.includestartnode bit)
returns @.results TABLE(id int,orderid int)
AS

BEGIN

DECLAre @.level int;
SET @.level=-1

IF @.includestartnode=1
BEGIN
INSERT INTO @.results
VALUES(@.currentid,0)
END

WHILE EXISTS (SELECT parentid FROM CATEGORIES WHEREid=@.currentid)
BEGIN

SELECT @.currentid=parentId
FROM CATEGORIES
WHEREid=@.currentid

IF @.@.ROWCOUNT=0
BEGIN
BREAK
END

INSERT INTO @.results(id,orderid)
SELECT @.currentid, @.level
FROM CATEGORIES
WHEREid=@.currentid

SET @.level=@.level - 1

END

RETURN

END

Using this with a call:

SELECT idFROM dbo.GetParent(3,1)

ORDERby orderid

would return
ID
===
1
3
As you can see it returns the ID list of the hierarchical nodes so it is rather generic function (returns nodes who have parent-child relationship) going with "bottom-to-up" principle. You could utilize this in getting the "path"
==
DECLARE @.pathnvarchar(1000)

SELECT @.path=COALESCE(@.path+'/','')+

+CAST(CategoryNameasnvarchar)

from CATEGORIES cINNERJOIN dbo.GetParent(3,1) tmp

ON c.id=tmp.id

ORDERBY orderid

select @.path

Returns

Top level 1/Second level 1

Note that of course, if you want, you can combine getting the data into the function or just on plain proc to do the job. E.g you can do the recutrsive stuff which I here do in function, also in stored proc if you want to.

More Questions....Relationships

Ok heres the scenario:

3 tables

Movie - MovieID, MovieName, Starring1, Starring 2, Director

Director - MovieID, DirectorID, DirectorName

Actors - MovieID, ActorName

In bold are the names of the tables --

in bold+italic are the PK - Primary Keys.

Ive tried to create a relationship between:

1. Movie - MovieID to Director -MovieID - Movie is related to the director

2. Actors - MovieID to Movie-MovieID - Actor is related to Movie

Second relationship failed for some reason.

Im trying to achieve this result:

Movie = Pulp Fiction

Director = Quentin Tan

Actor = Sam L Jackson

Once i do reports etc.....What am i doing wrong here as i think its my relationships im having difficulty with?

Thank you in advance

try:

Select movieName,

DirectorName,

actorName

from movie m

join director d

on m.movieId = d.movieId

and m.movieName = 'Pulp Fiction'

and d.directorName = 'Quentin Tan'

join actor a

on m.movieId = a.movieId

and a.actorName = 'Sam L Jackson'

an alternative is:

Select movieName,

DirectorName,

actorName

from movie m

join director d

on m.movieId = d.movieId

join actor a

on m.movieId = a.movieId

where m.movieName = 'Pulp Fiction'

and d.directorName = 'Quentin Tan'

and a.actorName = 'Sam L Jackson'

|||Thanks but am i on the right lines about creating these relationships in the way i described or would you do anything differently?|||

Assuming that a movie has only one director, something like this:

Director -- DirectorID (PK), DirecorName

Actor -- ActorID (PK), ActorName

Movie -- MovieID (PK), MovieName, DirectorID

Cast -- MovieID, Order, Part, ActorID, (PK: MovieID, ActorID, Part)

The "Cast" table is necessary so that you can list all of the actors of the movie. I added in "Order" so that you can control "Promotion Order" or "Billing Order" or whatever you want to call it -- it is a control so that you can control the order in which the actors might be listed in credits.

I added in "Part" for movies in which somebody like Eddie Murphy might play multiple parts.

From my perspective the biggest problem here is the "Cast" table as I have made it. I still don't feel that it is general enough. For instance, say that you want to use this as the basis for listing movie credits. You want to include Producers, stunt coordinators, etc. It would be best to generalize the "Cast" table into a "Role" table and along the way establish "Subcategories" of the "Role" table. But now we are getting ahead of ourselves. This should get you started.

More Questions on Permission

Thanks Hari, That helps a lot.
Do I have a way to know previlage details about an Object?
such as is the SELECT on a table been Granted, revoked or
denied?
Greg Chang
Subject: Re: Questions about syspermissions
From: "Hari" <hari_prasad_k@.hotmail.com> Sent:
6/24/2004 9:29:17 PM
HI,
Have a look into sysprotects table, column action
contains the granted
previlages for each objects.
The explanation for Action column:-
26 = REFERENCES
178 = CREATE FUNCTION
193 = SELECT
195 = INSERT
196 = DELETE
197 = UPDATE
198 = CREATE TABLE
203 = CREATE DATABASE
207 = CREATE VIEW
222 = CREATE PROCEDURE
224 = EXECUTE
228 = BACKUP DATABASE
233 = CREATE DEFAULT
235 = BACKUP LOG
236 = CREATE RULE
Simply you can execute the below system procedure to get
the objct level
previlages:-
sp_helprotect <object_name>
Thanks
Hari
MCDBA
"Greg Chang" <anonymous@.discussions.microsoft.com> wrote
in message
news:210fa01c45a0e$23130eb0$a501280a@.phx.gbl...
> is syspermissions table contains the Permissions of
> delete, select, insert, update, execute and DRI on all
> the objects?
> how do I use this syspermissions table? For example,
> which column stored the INSERT Privileges?
> thanks
> Greg Chang
..
See the BOL for information about the sp_helprotect stored procedure.
Jim
"Greg Chang" <anonymous@.discussions.microsoft.com> wrote in message
news:21e9701c45d1b$c99a0170$a601280a@.phx.gbl...
> Thanks Hari, That helps a lot.
> Do I have a way to know previlage details about an Object?
> such as is the SELECT on a table been Granted, revoked or
> denied?
> Greg Chang
>
> Subject: Re: Questions about syspermissions
> From: "Hari" <hari_prasad_k@.hotmail.com> Sent:
> 6/24/2004 9:29:17 PM
> HI,
> Have a look into sysprotects table, column action
> contains the granted
> previlages for each objects.
> The explanation for Action column:-
> 26 = REFERENCES
> 178 = CREATE FUNCTION
> 193 = SELECT
> 195 = INSERT
> 196 = DELETE
> 197 = UPDATE
> 198 = CREATE TABLE
> 203 = CREATE DATABASE
> 207 = CREATE VIEW
> 222 = CREATE PROCEDURE
> 224 = EXECUTE
> 228 = BACKUP DATABASE
> 233 = CREATE DEFAULT
> 235 = BACKUP LOG
> 236 = CREATE RULE
>
> Simply you can execute the below system procedure to get
> the objct level
> previlages:-
> sp_helprotect <object_name>
> --
> Thanks
> Hari
> MCDBA
> "Greg Chang" <anonymous@.discussions.microsoft.com> wrote
> in message
> news:210fa01c45a0e$23130eb0$a501280a@.phx.gbl...
>
> .
>
|||What is BOL?

>--Original Message--
>See the BOL for information about the sp_helprotect
stored procedure.
>Jim
>"Greg Chang" <anonymous@.discussions.microsoft.com> wrote
in message[vbcol=seagreen]
>news:21e9701c45d1b$c99a0170$a601280a@.phx.gbl...
Object?[vbcol=seagreen]
or[vbcol=seagreen]
get[vbcol=seagreen]
wrote[vbcol=seagreen]
all
>
>.
>
|||See the BOL for information about the sp_helprotect stored procedure.
Jim
"Greg Chang" <anonymous@.discussions.microsoft.com> wrote in message
news:21e9701c45d1b$c99a0170$a601280a@.phx.gbl...
> Thanks Hari, That helps a lot.
> Do I have a way to know previlage details about an Object?
> such as is the SELECT on a table been Granted, revoked or
> denied?
> Greg Chang
>
> Subject: Re: Questions about syspermissions
> From: "Hari" <hari_prasad_k@.hotmail.com> Sent:
> 6/24/2004 9:29:17 PM
> HI,
> Have a look into sysprotects table, column action
> contains the granted
> previlages for each objects.
> The explanation for Action column:-
> 26 = REFERENCES
> 178 = CREATE FUNCTION
> 193 = SELECT
> 195 = INSERT
> 196 = DELETE
> 197 = UPDATE
> 198 = CREATE TABLE
> 203 = CREATE DATABASE
> 207 = CREATE VIEW
> 222 = CREATE PROCEDURE
> 224 = EXECUTE
> 228 = BACKUP DATABASE
> 233 = CREATE DEFAULT
> 235 = BACKUP LOG
> 236 = CREATE RULE
>
> Simply you can execute the below system procedure to get
> the objct level
> previlages:-
> sp_helprotect <object_name>
> --
> Thanks
> Hari
> MCDBA
> "Greg Chang" <anonymous@.discussions.microsoft.com> wrote
> in message
> news:210fa01c45a0e$23130eb0$a501280a@.phx.gbl...
>
> .
>
|||BOL = Books OnLine, which is the documentation for SQL Server. You can find
it in the SQL Server program group, and you can download the latest updated
version from www.microsoft.com/sql
Jacco Schalkwijk
SQL Server MVP
"Greg Chang" <anonymous@.discussions.microsoft.com> wrote in message
news:2239e01c45d22$4877f9b0$a301280a@.phx.gbl...[vbcol=seagreen]
> What is BOL?
> stored procedure.
> in message
> Object?
> or
> get
> wrote
> all
|||What is BOL?

>--Original Message--
>See the BOL for information about the sp_helprotect
stored procedure.
>Jim
>"Greg Chang" <anonymous@.discussions.microsoft.com> wrote
in message[vbcol=seagreen]
>news:21e9701c45d1b$c99a0170$a601280a@.phx.gbl...
Object?[vbcol=seagreen]
or[vbcol=seagreen]
get[vbcol=seagreen]
wrote[vbcol=seagreen]
all
>
>.
>
|||hi Greg,
"Greg Chang" <anonymous@.discussions.microsoft.com> ha scritto nel messaggio
news:2239e01c45d22$4877f9b0$a301280a@.phx.gbl...
> What is BOL?
BOL stands for Books On Line, the official on-line guide for SQL Server, you
can download, in localized version at
http://www.microsoft.com/sql/techinf...2000/books.asp
it's the best documentation ever about SQL Server...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||BOL = Books OnLine, which is the documentation for SQL Server. You can find
it in the SQL Server program group, and you can download the latest updated
version from www.microsoft.com/sql
Jacco Schalkwijk
SQL Server MVP
"Greg Chang" <anonymous@.discussions.microsoft.com> wrote in message
news:2239e01c45d22$4877f9b0$a301280a@.phx.gbl...[vbcol=seagreen]
> What is BOL?
> stored procedure.
> in message
> Object?
> or
> get
> wrote
> all
|||hi Greg,
"Greg Chang" <anonymous@.discussions.microsoft.com> ha scritto nel messaggio
news:2239e01c45d22$4877f9b0$a301280a@.phx.gbl...
> What is BOL?
BOL stands for Books On Line, the official on-line guide for SQL Server, you
can download, in localized version at
http://www.microsoft.com/sql/techinf...2000/books.asp
it's the best documentation ever about SQL Server...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

More questions about porting from MySQL to MS SQL

1) In several tables, in my MySQL version, I created columns using
something like the following:

`ab_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,

This allowed me to ensure that when a record is either added or edited,
the value in the field is set to the current date and time. I.E.,
ab_timestamp is given the current date and time when a record is
created, and then it is updated to the date and time at which the
record is updated. I learned the hard way that MS SQL does not like
"on update CURRENT_TIMESTAMP". So, it looks like MS SQL will allow me
to initialize ab_timestamp to the current date and time, but not
automatically update it to the date and time at which the record is
updated. I have plenty of code to port that depends on the behaviour
supported by MySQL. DO I have to modify all that code, or is there a
way to get MS SQL to provide it? (Yes, I know 'timestamp' is
deprecated in MS SQL and that I should use datetime instead, and in
fact have already done so.)

2) I began with a single SQL script that creates all the tables, views,
functions and triggers the database needs. On trying to get MS SQL to
accept it, I encountered a number of error messages saying that CREATE
FUNCTION and CREATE VIEW need to be the first statement in a script.
Why? I know I can work around this odd constraint by putting each
function and view (and IIRC trigger) into its own script, but that
seems like a make work effort imposed for some unknown reason by MS
SQL, unless there is another way to get around it.

3) I see, in the documentation for CREATE FUNCTION, functions are not
allowed to use a timestamp for either a parameter or a return value.
This is in reference to a pair of scalar functions I am using which
need to manipulate date and time values. For the purpose of
clarification, is this documentation refering to all date/time data
types, or only the deprecated timestamp type? As examples, consider
one function that needs to return the most recent date in a date column
in a specific table, or another function that computes a date from a
date and an offset (e.g. if called with the value returned by the first
function as the first argument and '-7' as the second, returns the date
of the day that is a week earlier than that date). These two functions
are frequently used in the SQL code I'm trying to port and I really
don't want to complicate so many of those statements if I don't have
to.

Thanks

TedTed wrote:

Quote:

Originally Posted by

1) In several tables, in my MySQL version, I created columns using
something like the following:
>
`ab_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,
>
This allowed me to ensure that when a record is either added or edited,
the value in the field is set to the current date and time. I.E.,
ab_timestamp is given the current date and time when a record is
created, and then it is updated to the date and time at which the
record is updated. I learned the hard way that MS SQL does not like
"on update CURRENT_TIMESTAMP". So, it looks like MS SQL will allow me
to initialize ab_timestamp to the current date and time, but not
automatically update it to the date and time at which the record is
updated. I have plenty of code to port that depends on the behaviour
supported by MySQL. DO I have to modify all that code, or is there a
way to get MS SQL to provide it? (Yes, I know 'timestamp' is
deprecated in MS SQL and that I should use datetime instead, and in
fact have already done so.)
>
2) I began with a single SQL script that creates all the tables, views,
functions and triggers the database needs. On trying to get MS SQL to
accept it, I encountered a number of error messages saying that CREATE
FUNCTION and CREATE VIEW need to be the first statement in a script.
Why? I know I can work around this odd constraint by putting each
function and view (and IIRC trigger) into its own script, but that
seems like a make work effort imposed for some unknown reason by MS
SQL, unless there is another way to get around it.
>
3) I see, in the documentation for CREATE FUNCTION, functions are not
allowed to use a timestamp for either a parameter or a return value.
This is in reference to a pair of scalar functions I am using which
need to manipulate date and time values. For the purpose of
clarification, is this documentation refering to all date/time data
types, or only the deprecated timestamp type? As examples, consider
one function that needs to return the most recent date in a date column
in a specific table, or another function that computes a date from a
date and an offset (e.g. if called with the value returned by the first
function as the first argument and '-7' as the second, returns the date
of the day that is a week earlier than that date). These two functions
are frequently used in the SQL code I'm trying to port and I really
don't want to complicate so many of those statements if I don't have
to.
>
Thanks
>
Ted

1) You can use an UPDATE trigger to simulate the same functionality.
Usually though it is better to use stored procedures to perform all
your data access. That way you can easily include the timestamp as part
of your update procs.

2) You don't need separate scripts for each View / Function. You do
need separate batches. A batch is separated using the GO keyword in
Query Analyzer.

3) Don't confuse TIMESTAMP with DATETIME. They are not at all the same!
You are referring to DATETIME values, which ARE permitted as parameters
and return values in functions. BTW, you don't need to write a function
to do date arithmetic - it already exists as a built-in function:
DATEADD().

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Ted (r.ted.byers@.rogers.com) writes:

Quote:

Originally Posted by

1) In several tables, in my MySQL version, I created columns using
something like the following:
>
`ab_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,
>
This allowed me to ensure that when a record is either added or edited,
the value in the field is set to the current date and time. I.E.,
ab_timestamp is given the current date and time when a record is
created, and then it is updated to the date and time at which the
record is updated. I learned the hard way that MS SQL does not like
"on update CURRENT_TIMESTAMP". So, it looks like MS SQL will allow me
to initialize ab_timestamp to the current date and time, but not
automatically update it to the date and time at which the record is
updated. I have plenty of code to port that depends on the behaviour
supported by MySQL. DO I have to modify all that code, or is there a
way to get MS SQL to provide it?

You will have to modify all that code. To have to be portable is indeed
painful, and a good start is to pay attention to what is in ANSI standards,
what is commonly supported. And not to the least to what are extensions
added by a certain vendor. My knowledge of ANSI and other engines are
poor (since I live in a sheltered world where I only need to support
SQL Server), but I would suspect that the ON UPDATE clause for the
default constraint is properitary to MySQL.

The way to do this in SQL Server is to use a trigger. Not that triggers
usually are very portable...

You can also modify the UPDATE statements so that they read:

UPDATE tbl
SET ...,
ab_timestamp = DEFAULT,
WHERE ...

I believe this syntax is portable.

Quote:

Originally Posted by

(Yes, I know 'timestamp' is deprecated in MS SQL and that I should use
datetime instead, and in fact have already done so.)

timestamp is not deprecated in SQL Server, but it's a completely different
data type, which is used to implement optimistic locking. A timestamp
is an 8-bit value is updated each time the row is updated and it's
unique within the database. Further more timestamp value are monotonically
increasing. But there is no correlation with time. Timestamp is
proprietary to SQL Server, so you should probably stay away from it
entirely.

Quote:

Originally Posted by

2) I began with a single SQL script that creates all the tables, views,
functions and triggers the database needs. On trying to get MS SQL to
accept it, I encountered a number of error messages saying that CREATE
FUNCTION and CREATE VIEW need to be the first statement in a script.
Why? I know I can work around this odd constraint by putting each
function and view (and IIRC trigger) into its own script, but that
seems like a make work effort imposed for some unknown reason by MS
SQL, unless there is another way to get around it.

Not a separate script, but a separate batch. Batches are separated with
"go" in all query tools. The graphic tools permit you to specify a
different batch separator.

Why? Because else it would be difficult to tell where a procedure ends.
Say that you have:

CREATE PROCEDURE ... AS
...
CREATE TABLE ...

Is that CREATE TABLE part of the procedure or not? (Yes, if you have
BEGIN END it's clear. But of legacy BEGIN END is not required in
stored procedures.)

Quote:

Originally Posted by

3) I see, in the documentation for CREATE FUNCTION, functions are not
allowed to use a timestamp for either a parameter or a return value.

As noted above, there is little reason for your to use the timestamp
data type.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

More questions about data mining plug-in algorithms?

Hi, all here,

I am having a more considertaion about Data Mining plug-in algorithms. When we say we are going to embed a uesr plug-in algorithm, so what is the context for that ? I mean in which case then we thing we need to embed a user plug-in algortihm? I know when we say we are going to embed a user costomermized plug-in algorithm, it means we want something more costomized. But what kind of customized features are generally concerned? Is it independant for different market sectors?

I dont think we can just try to embed a plug-in algorithm then compete it with avaialble algorithms to see which one is with better prediction accuracy?

Would please someone here give me some guidances about that?

Essentially, you would use a customized plug-in algorithm when one of the built-in algorithms doesn't perform the task you want. There are a wide variety of DM algorithms, and we have nine built in. For example, you may want hierarchical clustering, or link analysis, or a classification algorithm particularly suited towards fraud. Another example is an algorithm that allows you to specify additional penalties for specific incorrect predictions.

Considering your last question, yes, you can create a plug-in algorithm and compare it with the built in's to see which has better accuracy

|||

Hi, Jamie, thanks a lot for your very helpful guidance.

With best regards,

more questions

Lets say on tbDeposits I have a Index that is on both depositdue and deposit paid

why does this statement

Select * from tbDeposits Deposits where Deposits.DateDue IS NOT NULL
AND Deposits.DatePaid IS NULL

have 2 table scans in it? one for 83 percent?

A NULL value is 'unknown' and cannot be efficiently indexed.

Any time you are trying to find the absence of something, you have to look at everything you have to see if it is missing. That may require a table scan or it may require an index scan. (IF it is the clustered index key, then an index scan is still a table scan.)

sql

More Question on Permissions

I like to thanks all of you helps.
but I still can't find information I need among
sysprotects, sp_helpprotect,
INFORMATION_SCHEMA.table_priviledges, and
INFORMATION_SCHEMA.column_priviledges.
What I need to know is if a user don't have certain
priviledge, was it been revoked or denied?
For example, if a user has no UPDATE privildge on a
table's one column, was it been denied or revoked(never
been granted)?
Greg Chang
hi Greg,
"Greg Chang" <anonymous@.discussions.microsoft.com> ha scritto nel messaggio
news:2276c01c45d2c$35de6cd0$a501280a@.phx.gbl...
> I like to thanks all of you helps.
> but I still can't find information I need among
> sysprotects, sp_helpprotect,
> INFORMATION_SCHEMA.table_priviledges, and
> INFORMATION_SCHEMA.column_priviledges.
> What I need to know is if a user don't have certain
> priviledge, was it been revoked or denied?
> For example, if a user has no UPDATE privildge on a
> table's one column, was it been denied or revoked(never
> been granted)?
it all depends on user database role membership too...
SET NOCOUNT ON
CREATE DATABASE GREG
GO
USE GREG
GO
EXEC sp_adduser @.loginame = 'roby'
, @.name_in_db = 'roby'
GO
CREATE TABLE dbo.Protected (
Id INT NOT NULL ,
Data VARCHAR( 10 )
)
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON [dbo].[Protected] ([Id]) TO [roby]
DENY INSERT, UPDATE, DELETE ON [dbo].[Protected] ([Data]) TO [roby]
GO
EXEC sp_helprotect @.name = [Protected]
GO
SETUSER 'roby'
SELECT * FROM [dbo].[Protected]
SELECT [Id] FROM [dbo].[Protected]
GO
SETUSER
REVOKE ALL ON [dbo].[Protected] TO [roby]
GO
SETUSER 'roby'
SELECT * FROM [dbo].[Protected]
SELECT [Id] FROM [dbo].[Protected]
GO
SETUSER
GO
USE master
GO
DROP DATABASE GREG
--<---
Granted database access to 'roby'.
Owner Object Grantee Grantor ProtectType Action Column
-- -- -- -- -- -- --
dbo Protected roby dbo Deny Delete .
dbo Protected roby dbo Deny Insert .
dbo Protected roby dbo Deny Update Data
dbo Protected roby dbo Grant Select Id
dbo Protected roby dbo Grant Update Id
(5 row(s) affected)
Server: Msg 230, Level 14, State 1, Line 1
SELECT permission denied on column 'Data' of object 'Protected', database 'G
REG', owner 'dbo'.
Id
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Protected', database 'GREG', owner
'dbo'.
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Protected', database 'GREG', owner
'dbo'.
as you can see, user "roby" does not belong to particular database role, so
it has no explicit access to base table [dbo].[Protected]... he has benn
granted SELECT, INSERT, DELETE, UPDATE permission only on
[dbo].[Protected].[Id], while explicit DENY has ben set on
[dbo].[Protected].[Data] column...
and even after revoking all privleges, he still has no access to the base
table...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||hi Greg,
"Greg Chang" <anonymous@.discussions.microsoft.com> ha scritto nel messaggio
news:2276c01c45d2c$35de6cd0$a501280a@.phx.gbl...
> I like to thanks all of you helps.
> but I still can't find information I need among
> sysprotects, sp_helpprotect,
> INFORMATION_SCHEMA.table_priviledges, and
> INFORMATION_SCHEMA.column_priviledges.
> What I need to know is if a user don't have certain
> priviledge, was it been revoked or denied?
> For example, if a user has no UPDATE privildge on a
> table's one column, was it been denied or revoked(never
> been granted)?
it all depends on user database role membership too...
SET NOCOUNT ON
CREATE DATABASE GREG
GO
USE GREG
GO
EXEC sp_adduser @.loginame = 'roby'
, @.name_in_db = 'roby'
GO
CREATE TABLE dbo.Protected (
Id INT NOT NULL ,
Data VARCHAR( 10 )
)
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON [dbo].[Protected] ([Id]) TO [roby]
DENY INSERT, UPDATE, DELETE ON [dbo].[Protected] ([Data]) TO [roby]
GO
EXEC sp_helprotect @.name = [Protected]
GO
SETUSER 'roby'
SELECT * FROM [dbo].[Protected]
SELECT [Id] FROM [dbo].[Protected]
GO
SETUSER
REVOKE ALL ON [dbo].[Protected] TO [roby]
GO
SETUSER 'roby'
SELECT * FROM [dbo].[Protected]
SELECT [Id] FROM [dbo].[Protected]
GO
SETUSER
GO
USE master
GO
DROP DATABASE GREG
--<---
Granted database access to 'roby'.
Owner Object Grantee Grantor ProtectType Action Column
-- -- -- -- -- -- --
dbo Protected roby dbo Deny Delete .
dbo Protected roby dbo Deny Insert .
dbo Protected roby dbo Deny Update Data
dbo Protected roby dbo Grant Select Id
dbo Protected roby dbo Grant Update Id
(5 row(s) affected)
Server: Msg 230, Level 14, State 1, Line 1
SELECT permission denied on column 'Data' of object 'Protected', database 'G
REG', owner 'dbo'.
Id
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Protected', database 'GREG', owner
'dbo'.
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Protected', database 'GREG', owner
'dbo'.
as you can see, user "roby" does not belong to particular database role, so
it has no explicit access to base table [dbo].[Protected]... he has benn
granted SELECT, INSERT, DELETE, UPDATE permission only on
[dbo].[Protected].[Id], while explicit DENY has ben set on
[dbo].[Protected].[Data] column...
and even after revoking all privleges, he still has no access to the base
table...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

More queries more problems

I'm trying to write a query that returns a list of all employess from one table, and their hours worked from another table. If there are no entries in the hours table, I want to still show them with value of zero hours worked. I almost have this working, but I'm having some issues. Here's my query:

SELECT th.ID, tp.FirstName + ' ' + tp.LastName as Name, IsNull(Total_Hours, '0.0') as Hours1
FROM tblProfiles tp LEFT JOIN tblHours th on tp.ID = th.userID
WHERE (th.Start_Date = '" + myStartDate + "' OR th.Start_Date IS NULL)
ORDER BY tp.FirstName

For testing, in my profiles table, I have 5 users. In my hours table, I have entries for 3 of those users. Of those three users, 2 have entries for both 1/1/03 and 1/16/03, and the other has just 1 entry for 1/1/03.

When I run the query above for myStartDate = '1/1/03', I get the correct output, which shows the hours for 3 users, and shows 0 hours for the other 2. However, if I run the query on myStartDate = '1/16/03', my result set has the hours for the 2 users that have entries for that date, and also has 0 hours for the 2 users that have no entries. The user that has only 1 entry (for 1/1/03)should show up with 0 hours, but instead, it doesn't show up at all.

I'm guessing that the problem is with the way the table is joined, and that because that user has 1 valid Start_Date, it is getting excluded from the WHERE conditions, but I can't seem to make it work.

help!can you show the structure of the 2 tables ...|||tblProfiles has the following columns:

ID FirstName LastName ... (other unimportant columns)

tblHours has the following columns:

ID userID Start_Date End_Date Total_Hours

|||have you tried running this query in Query Analyzer as with the straight sql to make sure that you are getting the same results?|||yeah, I did... in Enterprise Manager, if I run the same query, I get the same results... the user with 1 entry for 1/1/03 does not show up at all if I run the query with:

WHERE Start_Date = '1/16/03' OR Start_Date IS NULL

I am assuming that "Start_Date IS NULL" is not evaluating to true because, with the table join, it is picking up the other record with the 1/1/03 Start_Date ...|||In my WHERE clause:

WHERE Start_Date = '" + myStartDate + "' OR Start_Date IS NULL

I think theStart_Date IS NULL part needs to specifically refer only the record for the given "myStartDate", but I can't make it jive...|||Yeah this is ringing a bell, I had something similiar. Maybe try wrapping everything in parens, like

WHERE ((Start_Date = '" + myStartDate + "') OR (Start_Date IS NULL))

Not sure but this might force SQL to evaluate the where clause correctly.|||I tried your suggestion, but it didn't change the results at all...|||have you thought abt using a cursor...
loop through for each record in tblprofiles...and for each record join with tblhours on id and start_date and get the hours..
this way you can get those ids tht dont have any entries...startdates/hours...etc and still show them.

HTH|||I'm not familiar with using cursors, but I'm willing to try anything... can you give me an example of how I would use it?|||heres a sample cursor tht i am using

DECLARE rs CURSOR
LOCAL
FORWARD_ONLY
OPTIMISTIC
TYPE_WARNING
FOR SELECT ID,FirstName,LastName, ... (other columns)
OPEN rs
fetch next from rs into @.id,@.fname,@.lname...( other columns)
WHILE ( @.@.FETCH_STATUS = 0 )
begin
 /* here do your stuff - join with the other table and get the stuff */
 use the @.id to join with tblhours..
 FETCH NEXT FROM rs INTO @.id,@.fname,@.lname...( other columns)
END
close rs
deallocate rs

HTH|||Ok, I'm getting lost in the syntax... here's what I have, but it's not working:

DECLARE @.id integer, @.fname varchar(50), @.lname varchar(50)
DECLARE rs CURSOR
LOCAL
FORWARD_ONLY
OPTIMISTIC
TYPE_WARNING
FOR SELECT tp.ID, tp.FirstName, tp.LastName FROM tblProfiles tp
OPEN rs
fetch next from rs into @.id,@.fname,@.lname
WHILE ( @.@.FETCH_STATUS = 0 )
begin
 SELECT @.id, @.fname, @.lname
SELECT th.Total_Hours FROM tblHours th WHERE th.userID = @.id AND th.Start_Date='1/16/03'
 fetch next from rs into @.id,@.fname,@.lname
END
close rs
deallocate rs

what am I doing wrong?|||I finally got it to work, and didn't have to use the cursor! Here's the working query:

SELECT IsNull(th.ID, '0') as ID, tp.FirstName + ' ' + tp.LastName as Name, IsNull(Total_Hours, '0.0') as Hours1
FROM tblProfiles tp LEFT JOIN tblHours th on tp.ID = th.userID AND th.Start_Date = '" + myStartDate + "'
WHERE ((th.Start_Date = '" + myStartDate + "') OR (th.Start_Date IS NULL)) ORDER BY th.Total_Hours DESC, tp.FirstName

The solution was to addStart_Date = '" + myStartDate + "' to the LEFT JOIN criteria.

Thanks for all the help along the way guys...
<sigh of relief>|||

CREATE PROCEDURE get_hrs AS
begin
DECLARE @.id as int, @.fname as varchar(50), @.lname as varchar(50)
declare @.mystartdate as datetime
set @.mystartdate='1/1/2003'
declare @.hrs as int
DECLARE rs CURSOR
LOCAL
FORWARD_ONLY
OPTIMISTIC
TYPE_WARNING
FOR SELECT [ID], FName, LName FROM tblpro
OPEN rs
fetch next from rs into @.id,@.fname,@.lname
WHILE ( @.@.FETCH_STATUS = 0 )
begin
select @.hrs=hours from tblhours where ([ID] =@.id and startdate=@.mystartdate)
print convert(varchar(5),@.id) + ' ' + @.fname + '-' + @.lname + ' ' + convert(nvarchar(10),@.hrs)
set @.hrs=0
 fetch next from rs into @.id,@.fname,@.lname
END
close rs
deallocate rs
end
GO

you might need to change the column names, table names...

HTH

More problems with updateText

Hi,

Basically I am trying to add 2 ntext fields together sandwhiched by a literal ( '<BR /><BR />' ) in a SP for a report I will be running.

First step is to add <BR /><BR />, which I have done with some help from this forum, using a cursor and temp tables.

The last step is add the second ntext column (if it exists for the case only though). So like the 1st step I am using cursor and updatetext to amend the temporary table. The problem is that rather than update the temp table, the query section I have highlighted is run and nothing at all gets added to temptable. Since the column I am trying to add is a ntext I can't create a local variable. Does anyone know where I am going wrong?

Thanks in advance

Declare @.value2 varbinary(16)
DECLARE TEXTPTR_CURSOR2 CURSOR FOR
SELECT TEXTPTR(RT.Status) FROM #TempReport RT
DECLARE @.CurrentCase int

OPEN TEXTPTR_CURSOR2
FETCH NEXT FROM TEXTPTR_CURSOR2 INTO @.value2

WHILE @.@.FETCH_STATUS = 0
BEGIN

SELECT @.CurrentCase = caseid from #tempreport rt
where TEXTPTR(RT.Status) = @.value2

If ( select count(*) from tbl_memo m
where caseid = @.CurrentCase
and memotypeid = 8) > 0
BEGIN

UPDATETEXT #tempreport.Status @.value2 null 0
(select IsNull(thevalue,'')
from tbl_memo m
where caseid = @.CurrentCase
and memotypeid = 8)

END

FETCH NEXT FROM TEXTPTR_CURSOR2 INTO @.value2

END

CLOSE TEXTPTR_CURSOR2
DEALLOCATE TEXTPTR_CURSOR2

For those interested, I managed to crack this by using another text pointer:

Declare @.value2 varbinary(16)
DECLARE TEXTPTR_CURSOR2 CURSOR FOR
SELECT TEXTPTR(RT.Status) FROM #TempReport RT
DECLARE @.CurrentCase int

OPEN TEXTPTR_CURSOR2
FETCH NEXT FROM TEXTPTR_CURSOR2 INTO @.value2

WHILE @.@.FETCH_STATUS = 0
BEGIN

SELECT @.CurrentCase = caseid from #tempreport rt
where TEXTPTR(RT.Status) = @.value2

If ( select count(*) from tbl_memo m
where caseid = @.CurrentCase
and memotypeid = 8) > 0
BEGIN
declare @.value3 varbinary(16)
select @.value3 = textptr(thevalue) from tbl_memo m where caseid = @.currentcase and memotypeid = 8

UPDATETEXT #tempreport.Status @.value2 null 0 tbl_memo.thevalue @.value3

END

FETCH NEXT FROM TEXTPTR_CURSOR2 INTO @.value2
END

CLOSE TEXTPTR_CURSOR2
DEALLOCATE TEXTPTR_CURSOR2

more problems with unique sequences

I have another different schema with the same data, but this one its like this

Code Snippet

CREATE Table events (
id INT not null,
PxMiss Real Not Null,
PyMiss Real Not Null,
filenames Varchar(50));

ALTER TABLE events
ADD CONSTRAINT pk_particle PRIMARY KEY (id,filenames);

GO

CREATE Table Muon (
idap INT Not Null,
id INT Not Null,
eventid INT Not Null,
Px Real,
Py Real,
Pz Real,
Kf Real,
Ee Real);

GO

CREATE Table Electron(
idap INT Not Null,
id INT NOT NULL,
eventid INT Not Null,
Px Real,
Py Real,
Pz Real,
Kf Real,
Ee Real);

GO

CREATE Table Jet (
idap INT Not Null,
id INT NOT NULL,
eventid INT Not Null,
Px Real,
Py Real,
Pz Real,
Kf Real,
Ee Real);

GO

Create View lepton AS
select * from Muon
Union all
select * from Electron;
GO

Create View particle AS
select * from lepton
Union all
select * from Jet;
GO

I need that every particle had a different idap, but all the date is filled in muon, electron and jet. and then is joined in a view called particle.

The way that you are going about this is likely to cause some problems.

For example, while it is possible to create a VIEW that would provide a unique idap for for each Particle, since it is a view and will be re-constituted at every execution, there is no certainity that the idap will be the same at each execution. (In my opinion -that is a big issue. -but maybe not for your situation...)

It seems more stable if you were to create a Particle idap that was the combination of each constituent Identifier + idap. Something like this for example:

CREATE VIEW Particle
AS

SELECT
'M' + cast( idap AS varchar(12)),
id,
eventid,
Px,
Py
Pz
Kf,
Ee
FROM Muon

UNION ALL

SELECT
'E' + cast( idap AS varchar(12),
id,
eventid,
Px,
Py,
Pz,
Kf,
Ee
FROM Electron

UNION ALL

SELECT
'J' + cast( idap AS varchar(12),
id,
eventid,
Px,
Py,
Pz,
Kf,
Ee
FROM Jet
GO

With this example, each idap will be unique, and also give you a clue about the constitutent component.

More problems with hidden parameters

I've read that in SP1, it should be possible to hide a parameter without
blanking the prompt and therefore making it read only.
I have attempted to do this in the recommended way, i.e. by unticking the
"prompt user" check box for the parameter within report manager and
leaving the prompt with a value in it. However, when I then try to pass a
value for the parameter at runtime via a URL I get the error "Parameter1 is
read-only and cannot be modified".
N.B. I notice that when I then go back into the properties of the report,
the prompt for the parameter has been
changed from what it originally was to "Parameter1:" which is the name of
the parameter. I should also mention that this report is running against a
Sybase database.This is the defined behavior. If the 'prompt user' check box is not checked
then the parameter value can never be passed in, not via URL or SOAP. If
the parameter is marked 'prompt user' but it has no prompt string then the
Report Server toolbar will not prompt for the parameter and the parameter
can be passed in. If the 'prompt user' check box is check and there is a
prompt string then the Report Server toolbar will prompt the user and the
value can be passed in.
Does that help?
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"GML" <g_m_lowe@.hotmail.co.uk> wrote in message
news:#9sQcGIgEHA.2020@.TK2MSFTNGP10.phx.gbl...
> I've read that in SP1, it should be possible to hide a parameter without
> blanking the prompt and therefore making it read only.
> I have attempted to do this in the recommended way, i.e. by unticking the
> "prompt user" check box for the parameter within report manager and
> leaving the prompt with a value in it. However, when I then try to pass a
> value for the parameter at runtime via a URL I get the error "Parameter1
is
> read-only and cannot be modified".
> N.B. I notice that when I then go back into the properties of the report,
> the prompt for the parameter has been
> changed from what it originally was to "Parameter1:" which is the name of
> the parameter. I should also mention that this report is running against
a
> Sybase database.
>
>|||Actually that helps me, wished I'd found this one before posting my own
question.
I should add that this is not the way the readme for SP1 suggests it should
work, it says that previously removing the check from the Promp User used to
make the parameter read only, but this behaviour has changed.
Regards
Mike Hanson
"Daniel Reib [MSFT]" wrote:
> This is the defined behavior. If the 'prompt user' check box is not checked
> then the parameter value can never be passed in, not via URL or SOAP. If
> the parameter is marked 'prompt user' but it has no prompt string then the
> Report Server toolbar will not prompt for the parameter and the parameter
> can be passed in. If the 'prompt user' check box is check and there is a
> prompt string then the Report Server toolbar will prompt the user and the
> value can be passed in.
> Does that help?
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "GML" <g_m_lowe@.hotmail.co.uk> wrote in message
> news:#9sQcGIgEHA.2020@.TK2MSFTNGP10.phx.gbl...
> > I've read that in SP1, it should be possible to hide a parameter without
> > blanking the prompt and therefore making it read only.
> > I have attempted to do this in the recommended way, i.e. by unticking the
> > "prompt user" check box for the parameter within report manager and
> > leaving the prompt with a value in it. However, when I then try to pass a
> > value for the parameter at runtime via a URL I get the error "Parameter1
> is
> > read-only and cannot be modified".
> >
> > N.B. I notice that when I then go back into the properties of the report,
> > the prompt for the parameter has been
> > changed from what it originally was to "Parameter1:" which is the name of
> > the parameter. I should also mention that this report is running against
> a
> > Sybase database.
> >
> >
> >
>
>sql

More problems with Gridview and SQL Procedures

I have successfully used SQL procedures to provide data to a Gridview previously, and I just can't see what's different about the simplified case that works and the real case that doesn't. I've extracted the code into a test page to avoid extraneous bumph.

1. I have developed a SQL procedure, GDB_P_Children, and tested this in SQL Server Management Studio Express. It returned exactly what it should. The procedure is: -

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GDB_P_Children]
@.IndiiD uniqueidentifier,
@.Option int,
@.Owner Varchar(50),
@.User Varchar(50)
AS
Select * from GDBChildren(@.Indiid, @.Option, @.Owner, @.User)
GO

2. I then tried to use it in my program to power a Gridview, but the gridview was blank. I put the Gridview into a test page that only had code: -

Option Compare Text
Partial Class test
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Session("INDIid") = "ffe1fb2f-88ce-4b64-a358-e21efd161d70"
Label2.Text = Session("INDIid") ' These are just for debugging
Session("Owner") = "robertb"
Label3.Text = Session("Owner") & ","
Session("Option") = 0
Label4.Text = Session("Option") & ","
Session("gdvChildUser") = ""
Label5.Text = Session("gdvChildUser") & ","
End Sub
End Class

I then regenerated the gridview from scratch: -

Toolbox: drag a gridview on to the page
Click "Configure Data sourec" => New Data Source => SQL Database. Name the datasource SQLChildren.
Connection string - select as normal
Configure: Specify a custom SQL statement or procedure
Select Stored Procedure, select GDB_P_Children
Set parameters to: -
INDIid Session("INDIid")
Option Session("Option")
Owner Session("Owner")
User Session("gdbChildUser")

Test Query showed that the first parameter, INDIid, had type Object, and I know that this will fail (seehttp://forums.asp.net/thread/1390374.aspx), so I finished data source configuration and edit the source of the test page to remove Type="Object" from the parameter definition. The parameter now has type "Empty", and filling it in with the values ffe1fb2f-88ce-4b64-a358-e21efd161d70, 0, robertb, and '' returns data as expected. Click Finish and the Gridview configures itself with the correct column headings: -

Exactly correct so far. Yet executing the page shows only my debugging labels, with no sign of the gridview.

I changed the stored procedure: -

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- Temporary testing version, stripped to bare essentials
ALTER PROCEDURE [dbo].[GDB_P_Children]
@.IndiiD uniqueidentifier
AS
Select INDIid, Indiusername, dbo.gdbindinamedate(INDIid) as IndiNameDate, Indisex
from nzgdb_Indi where INDIMothersindiid = @.INDIid

Data configuration was redone, then the page was displayed. It now correclty shows data from the database. So what's different?

I changed the procedure back to the original, and again the blank (except for debugging labels) page was displayed.

The data configuration was changed so that only the first parameter, @.INDIid, was passed, and the other were set to default values. Still the blank page.

Procedure GDB_P_Children was then changed to have only one parameter, with the other three declared and given values internally: -

ALTER PROCEDURE [dbo].[GDB_P_Children]
@.IndiiD uniqueidentifier
AS
Declare @.Option int
Declare @.Owner Varchar(50)
Declare @.User Varchar(50)
Set @.Option = 0
Set @.Owner = 'robertb'
set @.User =''
Select * from GDBChildren(@.Indiid, @.Option, @.Owner, @.User)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

The page is now OK, with the gridview showing data as expected.

So why can't I use this procedure with the four arguments that it is supposed to have? This is driving me crazy! I have other procedures with 2 arguments (both Uniqueidentifier) that work fine, so it's not the fact that this procedure has more than one argument that's causing problems. Help!!!!

Regards,

Robert Barnes

It look like a parameter issue to me. Can you open SQL profile and run a trace to see what is the parameter passed to the sp? normally that will tell you what is wrong

Hope this help

|||

My guess...

User you are setting to an empty string. The user parameter is set up to convert empty strings to a null. You also have the sqldatasource set up to cancel selects when one or more of the parameters is a null value.

|||Motely, you're right. The program worked fine when User is not null, and then when I changed the last parameter (User) to "Convert Empty String:False" it worked fine whether it was empty or not. Thank you.|||

David, I am having trouble running SQL profile. It won't recognise the SQL 2000 server on my laptop, even though it's started from Enterprise Manager which is running perfectly. In the meantime, Motely has solved my problem for me. Thank you for your help.

|||What's the meaning of "It won't recognise the SQL 2000 server on my laptop"? Did you mean SQL Profiler can not connect to the SQL 2000 instance by using the instance name? Then make sure you've enabled both TCP/IP and Named Pipes are enabled on the SQL instance as well as client.|||

"It won't recognize the SQL 2000 server on my laptop" means that: -

1. I have SQL 2000 running smoothly, using Windows Authentication. I can see it with either (or both) Enterprise Manager and with MS SQL Server Management Studio Express, and I can do any of the usual functions such as opening tables, running queries, ... The database is functioning normally with my application code.

2. I open SQL profiler (all programs/MS SQL Server/SQL Profiler) and click New Trace, which opens a dialog "Connect to SQL Server". Clicking the combo shows blank. Clicking the [...] opens another dialog, "Select server", but there is nothing in the list of active servers (there should be my laptop server, plus another). Going back to "Connect to SQL Server", I try typing the name of the server into the combo: the system thinks for a few seconds (30? I didn't time it) and then responds "SQL Server does not exist or access denied"

3. Using Enterprise Manager, I right-clicked my laptop server, clicked properties, on the general tab clicked Network Properties. TCP/IP and Named Pipes are already enabled.

So why won't SQL Profiler see the server?

|||

If you're using Profiler to connect to remote SQL2000 instance, and SQL2005/Express exists on the remote server, make sure the SQL Brower Service on the remote server is started. If the connection still fails, you can take a look at this post to get an idea of checking remote database connection:

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

|||Did this thread get mixed up - this seems to be part of another conversation. My SQL Server 2000, VWD2005, etc are ALL running locally on my laptop. Presumably my inablity to run SQL Profiler is not a network issue.|||

Of course we can disscuss this in a new post Then run 'cliconfg' to make sure you've enabled Shared Memory connectivity

|||

I ran cliconfg, and shared memory connectivity was already on: -

Click start button => click Run => Run dialog box. Enter cliconfg, click OK => SQL Server Client Network Connectivity. On General tab, towards the bottom, there is a check box "Enable Shared Memory Protocol". This is already checked.

What next?

Regards, Robert

|||There two other places you may be able to access Profiler connect to Query Analyzer and at the top you will see tools you should see the profiler there, another place is through the wizards most people don't know they exist because they are context sensitive so open your database and at the top of Enterprise Manager you will see the wizard and click on the index tuning wizard it is part of the profiler just like perf mon. Hope this helps.|||

Thanks Caddre, that looks promising. I'm still having a bit of difficulty - the profile isn't under Enterprise Manager/Tools, and when I go into the index tuning wizard it asks for a file that I haven't found (yet). I'll have to open up by SQL book and have a read. In the meantime I'd better close this thread off as I'm about to go on holiday, but I might be back in a few weeks.

Thanks for your help,

Robert

More problems with activation

I have two databases on the same instance.

One is Basket_ODS and the other is Intelligence_ODS. I am using service broker activation on a queue to move data from the Basket_ODS table to the Intelligence_ODS database. Previously I was able to move from table to table in Basket_ODS, however now that I am moving it to another database on the same instance it is no longer working.

If I set my active connection in SQL Management Studio to this user(BrokerUser) and execute the "move" procedure it works. When activated by Service Broker however, it does not. Here is the error message:

2006-05-09 14:47:52.940 spid86s The activated proc [ODS].[ProcessOrderQueue] running on queue Basket_ODS.ODS.Order Process Queue output the following: 'The server principal "BrokerUser" is not able to access the database "Intelligence_ODS" under the current security context.'

I'm sure I missed something becasue it works fine in the same database. BrokerUser has datareader and datawriter in both databases.

Thanks for any help on this matter.

Gary

Activated task run under impersonated security context, similar to using the EXECUTE AS clause. Let me cross-post a reply from Remus:

<remus>
The explanation is detailed in the 'Extending Database Impersonation by Using EXECUTE AS' chapter in BOL (http://msdn2.microsoft.com/en-us/library/ms188304(en-us,VS.90).aspx)

A short explanation is this: when executing under an EXECUTE AS context (as activated procedures always are), the trust is given by the dbo of the database. Therefore, the procedure is trusted only at the level of the database, not at the level of the server. Server level views require server level trust, and you execution context is lacking it. You execution context behaves as if you logged in with [Public] in the server. By marking the database as trustworthy, the dbo of the database is trusted at the server level and you execution context inherits all the permissions you expect at the server level.

Marking the database trustworthy is quite a powerfull step. The dbo of that database can elevate itself to sysadmin, there's no way of preventing it. A more refined approach is to sign the activated procedure with a server level certificate that has proper rights (ADD SIGNATURE).
</remus>

While the above could solve your problem, it may be worthwhile trying to ask yourself, why is the service located in Basket_ODS instead of Intelligence_ODS.

Hope that helps,
Rushi

|||

I actually have a sample showing how to do this: http://blogs.msdn.com/remusrusanu/archive/2006/03/07/545508.aspx

HTH,
~ Remus

|||

Thanks Rushi,

As usual you do a great job with follow up. The activated stored procedure copies data from the Basket_ODS database to the Intelligence_ODS database. If I move the proc to the Basket_ODS database, I ran into the same issue when it tried to copy over.

I did read the information on BOL and your earlier post on this. I guess I'll have to work my way through signing the stored procedure. The trust database option really won't be a player for me I think. I did set up trust tonight and it worked though. I have to do another run in the morning but it was incredibly slower than the copy to the same database.

I'll try to worth through the signing tomorrow. That seems the way to go for me. We are trying to operate in least-trust mode. The DBAs start getting white hair when I ask for things like database trust.

Thank you so much for your help and your contribution to the community.

Gary

|||

Thank's Remus. Wow that's a great sample. I wish I found that earlier today. Thanks again for the great information.

Gary

|||

I created the certificates as noted in your scripts. The user associated with the certificate in my system is BrokerUser2. I do have a question though.

why can't I do this?

ALTER QUEUE ODS.[Order Process Queue] WITH

ACTIVATION (

STATUS = ON,

PROCEDURE_NAME = ODS.ProcessOrderQueue,

MAX_QUEUE_READERS = 4,

EXECUTE AS 'BrokerUser2'

)

when I try this it says BrokerUser2 does not exist or I do not have access to it. I can do an sp_helpuser 'BrokerUser2' and see his rights though.

It did appear to work if I did this:

ALTER QUEUE ODS.[Order Process Queue] WITH

ACTIVATION (

STATUS = ON,

PROCEDURE_NAME = ODS.ProcessOrderQueue,

MAX_QUEUE_READERS = 4, EXECUTE AS OWNER)

I did create the ODS.ProcessOrderQueue stored procedure [With Execute As 'BrokerUser2'].

|||

The user executing the ALTER QUEUE statement must have IMPERSONATE permission over 'BrokerUser2'.

HTH,
~ Remus

More Problems With 32 Bit Package on x64 SQL Server

Hello,

I have gone through some documentation on how to execute a 32 bit package on SQL Server 2005 x64, and I'm still running into problems when I attempt to execute such a package from a Job.

Critical Points:

-The package was imported into an instance of Integration Services 2005 from the file system to a subdirectory of the MSDB folder. From Management Studio, Integration Services, the path to the package looks like this: Stored Packages\MSDB\CPS\CPS_Collections. 'CPS_Collections' is the name of the package.

-This package has a connection to an Access 2000 database using the Native OLE DB\Microsoft Jet 4.0 OLE DB Provider. I understand that there is not a 64 bit version of this provider. The Access database is stored on a different machine than the one that hosts the SQL Server 2005 x64 instance.

-According to this; http://msdn2.microsoft.com/en-us/library/ms141766.aspx I should be able to create the command line with the dtexecui.exe utility and copy and paste this line into a Job Step with the Job Step Type as operating system. Here is what my command line looks like: /SQL "\CPS\CPS_Collections" /SERVER bwdbfin1 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E . I have the Job Type set up as Operating System, and the Job runs as SQL Agent Service Account

-When I attempt to run the Job, the Job fails. Here is a part of the error message from the Job history:

The process could not be created for step 1 of job 0x9B318B226174A24B8BD63CE8F4814864 (reason: The system cannot find the file specified).

Does this point to a problem of where the Access datbase is located? Could it be that the account that runs SQL Agent does not have access rights to the directory? Is there a way to run an operating system command as someone other than SQL Agent? Is there something wrong with the command line that I am using?

Please share if you have any ideas on this.

Thank you for your help!

cdun2

On a 64-bit server, the SSIS job step calls the 64-bit version of DTEXEC. You need to call the 32-bit version (located in C:\Program Files (x86)\Microsoft Sql Server\90\DTS on most installs). To do this, you need to use a CmdExec job step.|||Hi,

Since the error description said : The system cannot find the file specified, maybe you set the location of Access database into a mapping drive or something, try using this format \\ip_address_of_the_machine\

Best regards,

Hery|||

cdun2 wrote:

-According to this; http://msdn2.microsoft.com/en-us/library/ms141766.aspx I should be able to create the command line with the dtexecui.exe utility and copy and paste this line into a Job Step with the Job Step Type as operating system. Here is what my command line looks like: /SQL "\CPS\CPS_Collections" /SERVER bwdbfin1 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E . I have the Job Type set up as Operating System, and the Job runs as SQL Agent Service Account

You can use DTEXECUI to construct the command arguments, but you still need to supply the executable name for the command line. I.e. your full command line should look like

"C:\Program Files (x86)\Microsoft Sql Server\90\DTS\Binn\DtExec.exe" /SQL "\CPS\CPS_Collections" /SERVER bwdbfin1 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E

|||

Thanks for the help, and sorry for the late response. I'll take your responses and look into this further. I can say that the location to the Access database is not expressed as a mapped drive.

More problems attaching a database

Hi Mike,

I've had a look at the white paper on use instances and also stopped using Management Studio, as you suggested. The problem is that I can run my application from VWD quite happily, until for no reason the application can't connect to the database. This happens ramdomly and is very frustrating. I have worked out a way to correct this problem with the following method (I don't know if this is correct but it's the only work around I know.

1) I close down VWD and open Management Studio,

2) I right click to attach a database, and then navigate to the .mdf file for the database, and click to attach it. I then delete the existing icon for that database (which I can't acess anyway, it only shows me the main icon but not the tables)

3) I rename the new instance of the database to the correct name (minus path info etc)

4) I create an SQL script as follows:

EXEC sp_grantlogin 'jon\ASPNET'

(This line executes correctly),

Then I enter these lines:

USE BalloonShop \*name of database \*

EXEC sp_grantdbaccess 'jon\ASPNET'

(At this stage when I execute the query, Management Studio informs me that this name already exists in the database and so doesn't execute)

5) I now close out of Management Studio and re-open VWD, and I can now run my application correctly.

I know I've got some configuration settings wrong somewhere but I find the whole user instance thing confusing. For the record I'm running the appllication through VWD's built in web server, I did originally have it through IIS but I moved it (this will explain the confusion of locations from my first post, I gave you the original location, sorry about that).

Any advice you can give would be gratefully recieved, ie is there an easy way to reconfigure my settings to make everything work as expected or have I got to start the database again from scratch (not a pleasent thought). Also is there an idiots guide to SQL Server Express anywhere that I can read.

Jon

ps I've just realised that I get the 'can't connect to database problem when running the application after I've made a change to the database from VWD ie adding a new stored proceedure, Don't know if this helps, but better you get the full picture.

I havn't read your first post.... what is the summary of what you are trying to acheive.

I have used SQL Express with VWD for a while now and have had no problems moving the database from my VWD environment to a production system. The first thing is that when using the VWD the Databases are created in the APP_Data directory and the connection string used inside the web.config file has a parameter that attaches the database at runtine to the Database Engine it also configures the security so only the Windows Account that has attached the Database can access the Database Files (User Instances). When moving to production systems or attaching it to an existing Engine the first step I would do is to take all of the files (MDF and LDF) for the database and put them in another directory, away from the ASP_Data directory in the root of your web application. Normally this would be the default data directories for the Database server that you are going to use. Next inside the management studio attach the database files and set up the security for the user that you are going to be using.

In my case here I normally create an application pool for the Web site to use in IIS and set the app pool to run under a user, I would then configure this user to have the access needed to run the database system ie Stored Procs and Table access. The last stage is to change the connection string in the web.config file for your application to reflect the new server. Making sure that the attach Database parameters are removed.

Hope this helps.

More ports for SQL Server

Hi,
Is it possible to configure SQL Server to listen to more ports beside 1433?
Or necessarily one port at a time can be used for SQL Server?
Thanks in advance,
LeilaYes you can use port other than 1433. Muliple SQL Server instance should use,
rather must use UNIQUE port number in a Server.
Piku.
"Leila" wrote:
> Hi,
> Is it possible to configure SQL Server to listen to more ports beside 1433?
> Or necessarily one port at a time can be used for SQL Server?
> Thanks in advance,
> Leila
>
>|||In addition, you can have a SQL instance listening on multiple ports. Just
add the additional port(s) in a comma-separated format (e.g. using Server
Network config), and restart the SQL instance.
Linchi
"Leila" wrote:
> Hi,
> Is it possible to configure SQL Server to listen to more ports beside 1433?
> Or necessarily one port at a time can be used for SQL Server?
> Thanks in advance,
> Leila
>
>sql

More ports for SQL Server

Hi,
Is it possible to configure SQL Server to listen to more ports beside 1433?
Or necessarily one port at a time can be used for SQL Server?
Thanks in advance,
LeilaYes you can use port other than 1433. Muliple SQL Server instance should use
,
rather must use UNIQUE port number in a Server.
Piku.
"Leila" wrote:

> Hi,
> Is it possible to configure SQL Server to listen to more ports beside 1433
?
> Or necessarily one port at a time can be used for SQL Server?
> Thanks in advance,
> Leila
>
>|||In addition, you can have a SQL instance listening on multiple ports. Just
add the additional port(s) in a comma-separated format (e.g. using Server
Network config), and restart the SQL instance.
Linchi
"Leila" wrote:

> Hi,
> Is it possible to configure SQL Server to listen to more ports beside 1433
?
> Or necessarily one port at a time can be used for SQL Server?
> Thanks in advance,
> Leila
>
>

More Partition Function and Partition Scheme questions.

Howdy again. Many of my queries are grouped by months. I want to potentially
make use of Partition Functions (PF) and Partition Schemes (PS) but I don't
want to have an out of control amount of of Files and File Groups to create
my PS's on. If I simply partition by year, will my month queries still be
sped up? Or is the only way to accomplish a speed advantage to partition by
month? Im a few weeks out from being able to do a real test, and the
suspence will kill me by then.
TIA, ChrisRPartitions help where the majority of the queries align themselves with your
partitions. In your case you will probably get some, but not the same amount
if you partition by month and year.
Note that you can park your partitions on different disks to spread the IO,
so you might not have 24 disks to spread your io, but you might have data
distribution patterns so that the volatile data could be on separate disks
and partition which are not queries as frequently could be on a single disk.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:%234a9SbNBHHA.3604@.TK2MSFTNGP04.phx.gbl...
> Howdy again. Many of my queries are grouped by months. I want to
> potentially
> make use of Partition Functions (PF) and Partition Schemes (PS) but I
> don't
> want to have an out of control amount of of Files and File Groups to
> create
> my PS's on. If I simply partition by year, will my month queries still be
> sped up? Or is the only way to accomplish a speed advantage to partition
> by
> month? Im a few weeks out from being able to do a real test, and the
> suspence will kill me by then.
> TIA, ChrisR
>|||> I want to potentially
> make use of Partition Functions (PF) and Partition Schemes (PS) but I don'
t
> want to have an out of control amount of of Files and File Groups to creat
e
> my PS's on.
You don't have to direct each partition to an unique filegroup. All can go t
o the same, or you can
distribute the partitions over the filegroups any way you want.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message news:%234a9SbNBHHA.3604@.TK2MSFTNGP04.ph
x.gbl...
> Howdy again. Many of my queries are grouped by months. I want to potential
ly
> make use of Partition Functions (PF) and Partition Schemes (PS) but I don'
t
> want to have an out of control amount of of Files and File Groups to creat
e
> my PS's on. If I simply partition by year, will my month queries still be
> sped up? Or is the only way to accomplish a speed advantage to partition b
y
> month? Im a few weeks out from being able to do a real test, and the
> suspence will kill me by then.
> TIA, ChrisR
>|||In addition to the fact that you can place multiple partitions on the same
filegroup, you can also take advantage of the ability to merge partitions. I
t
is a common practice to have many finer-grained partitions for more recent
and often more active data and few larger partitions for aged and often less
active data.
Linchi
"ChrisR" wrote:

> Howdy again. Many of my queries are grouped by months. I want to potential
ly
> make use of Partition Functions (PF) and Partition Schemes (PS) but I don'
t
> want to have an out of control amount of of Files and File Groups to creat
e
> my PS's on. If I simply partition by year, will my month queries still be
> sped up? Or is the only way to accomplish a speed advantage to partition b
y
> month? Im a few weeks out from being able to do a real test, and the
> suspence will kill me by then.
> TIA, ChrisR
>
>

More Partition Function and Partition Scheme questions.

Howdy again. Many of my queries are grouped by months. I want to potentially
make use of Partition Functions (PF) and Partition Schemes (PS) but I don't
want to have an out of control amount of of Files and File Groups to create
my PS's on. If I simply partition by year, will my month queries still be
sped up? Or is the only way to accomplish a speed advantage to partition by
month? Im a few weeks out from being able to do a real test, and the
suspence will kill me by then.
TIA, ChrisR
Partitions help where the majority of the queries align themselves with your
partitions. In your case you will probably get some, but not the same amount
if you partition by month and year.
Note that you can park your partitions on different disks to spread the IO,
so you might not have 24 disks to spread your io, but you might have data
distribution patterns so that the volatile data could be on separate disks
and partition which are not queries as frequently could be on a single disk.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:%234a9SbNBHHA.3604@.TK2MSFTNGP04.phx.gbl...
> Howdy again. Many of my queries are grouped by months. I want to
> potentially
> make use of Partition Functions (PF) and Partition Schemes (PS) but I
> don't
> want to have an out of control amount of of Files and File Groups to
> create
> my PS's on. If I simply partition by year, will my month queries still be
> sped up? Or is the only way to accomplish a speed advantage to partition
> by
> month? Im a few weeks out from being able to do a real test, and the
> suspence will kill me by then.
> TIA, ChrisR
>
|||In addition to the fact that you can place multiple partitions on the same
filegroup, you can also take advantage of the ability to merge partitions. It
is a common practice to have many finer-grained partitions for more recent
and often more active data and few larger partitions for aged and often less
active data.
Linchi
"ChrisR" wrote:

> Howdy again. Many of my queries are grouped by months. I want to potentially
> make use of Partition Functions (PF) and Partition Schemes (PS) but I don't
> want to have an out of control amount of of Files and File Groups to create
> my PS's on. If I simply partition by year, will my month queries still be
> sped up? Or is the only way to accomplish a speed advantage to partition by
> month? Im a few weeks out from being able to do a real test, and the
> suspence will kill me by then.
> TIA, ChrisR
>
>

More Partition Function and Partition Scheme questions.

Howdy again. Many of my queries are grouped by months. I want to potentially
make use of Partition Functions (PF) and Partition Schemes (PS) but I don't
want to have an out of control amount of of Files and File Groups to create
my PS's on. If I simply partition by year, will my month queries still be
sped up? Or is the only way to accomplish a speed advantage to partition by
month? Im a few weeks out from being able to do a real test, and the
suspence will kill me by then.
TIA, ChrisRPartitions help where the majority of the queries align themselves with your
partitions. In your case you will probably get some, but not the same amount
if you partition by month and year.
Note that you can park your partitions on different disks to spread the IO,
so you might not have 24 disks to spread your io, but you might have data
distribution patterns so that the volatile data could be on separate disks
and partition which are not queries as frequently could be on a single disk.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:%234a9SbNBHHA.3604@.TK2MSFTNGP04.phx.gbl...
> Howdy again. Many of my queries are grouped by months. I want to
> potentially
> make use of Partition Functions (PF) and Partition Schemes (PS) but I
> don't
> want to have an out of control amount of of Files and File Groups to
> create
> my PS's on. If I simply partition by year, will my month queries still be
> sped up? Or is the only way to accomplish a speed advantage to partition
> by
> month? Im a few weeks out from being able to do a real test, and the
> suspence will kill me by then.
> TIA, ChrisR
>|||> I want to potentially
> make use of Partition Functions (PF) and Partition Schemes (PS) but I don't
> want to have an out of control amount of of Files and File Groups to create
> my PS's on.
You don't have to direct each partition to an unique filegroup. All can go to the same, or you can
distribute the partitions over the filegroups any way you want.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message news:%234a9SbNBHHA.3604@.TK2MSFTNGP04.phx.gbl...
> Howdy again. Many of my queries are grouped by months. I want to potentially
> make use of Partition Functions (PF) and Partition Schemes (PS) but I don't
> want to have an out of control amount of of Files and File Groups to create
> my PS's on. If I simply partition by year, will my month queries still be
> sped up? Or is the only way to accomplish a speed advantage to partition by
> month? Im a few weeks out from being able to do a real test, and the
> suspence will kill me by then.
> TIA, ChrisR
>|||In addition to the fact that you can place multiple partitions on the same
filegroup, you can also take advantage of the ability to merge partitions. It
is a common practice to have many finer-grained partitions for more recent
and often more active data and few larger partitions for aged and often less
active data.
Linchi
"ChrisR" wrote:
> Howdy again. Many of my queries are grouped by months. I want to potentially
> make use of Partition Functions (PF) and Partition Schemes (PS) but I don't
> want to have an out of control amount of of Files and File Groups to create
> my PS's on. If I simply partition by year, will my month queries still be
> sped up? Or is the only way to accomplish a speed advantage to partition by
> month? Im a few weeks out from being able to do a real test, and the
> suspence will kill me by then.
> TIA, ChrisR
>
>

More on that login problem - found the logs -

2005-11-18 12:50:12.09 logon Login failed for user 'sa'.
2005-11-18 12:50:36.85 logon Login succeeded for user 'NT
AUTHORITY\SYSTEM'. Connection: Trusted.
and some more that might be useful -
2005-11-18 12:50:40.92 logon Login succeeded for user 'NT
AUTHORITY\SYSTEM'. Connection: Trusted.
2005-11-18 12:51:01.35 logon Login succeeded for user 'NT
AUTHORITY\SYSTEM'. Connection: Trusted.
2005-11-18 12:51:53.71 logon Login failed for user 'sa'.
2005-11-18 12:57:55.45 logon Login succeeded for user 'ART\art'.
Connection: Trusted.
2005-11-18 12:58:01.80 logon Login failed for user 'sa'.
2005-11-18 12:58:07.73 logon Login failed for user 'a'.
2005-11-18 12:58:16.75 logon Login failed for user 'a'.
2005-11-18 12:58:23.83 logon Login succeeded for user 'ART\art'.
Connection: Trusted.
2005-11-18 12:58:37.46 logon Login succeeded for user 'ART\art'.
Connection: Trusted.
2005-11-18 12:58:37.46 spid9 Starting up database 'test'.
2005-11-18 12:58:37.46 spid9 Opening file C:\MSSQL7\DATA\test.mdf.
2005-11-18 12:58:37.49 spid9 Opening file C:\MSSQL7\DATA\test.ldf.
2005-11-18 12:58:39.34 logon Login succeeded for user 'ART\art'.
Connection: Trusted.
2005-11-18 12:58:42.93 logon Login succeeded for user 'ART\art'.
Connection: Trusted.
2005-11-18 12:59:00.99 logon Login succeeded for user 'ART\art'.
Connection: Trusted.
2005-11-18 13:55:32.21 logon Login succeeded for user 'ART\w tilman'.
Connection: Trusted.
2005-11-18 13:55:34.14 logon Login succeeded for user 'ART\w tilman'.
Connection: Trusted.
2005-11-18 13:55:35.05 spid10 Using 'xpsql70.dll' version '1998.11.13' to
execute extended stored procedure 'xp_msver'.
Can connect with Windows NT authentication, but not with SQL server auth. So
all my
code has stopped working ...
When I try to make a new SQL Server registration
I get server does not exist or access denied...
Hi,
Verify that you are using both SQL Server and Windows authentication using
server Properties and look in the Security tab (I believe that you are
really using both SQL Server and Windows authentication since log messages
for Windows only authentication would show "Login failed for user 'sa'.
Reason: Not associated with a trusted SQL Server connection").
Which account are you using to connect to the database? Has the password for
this account been changed?
Ben Nevarez
"Barrett Bonden" <arthur@.networks-cc.com> wrote in message
news:sIJff.13085$ha2.13022@.fe08.lga...
> 2005-11-18 12:50:12.09 logon Login failed for user 'sa'.
> 2005-11-18 12:50:36.85 logon Login succeeded for user 'NT
> AUTHORITY\SYSTEM'. Connection: Trusted.
> and some more that might be useful -
> 2005-11-18 12:50:40.92 logon Login succeeded for user 'NT
> AUTHORITY\SYSTEM'. Connection: Trusted.
> 2005-11-18 12:51:01.35 logon Login succeeded for user 'NT
> AUTHORITY\SYSTEM'. Connection: Trusted.
> 2005-11-18 12:51:53.71 logon Login failed for user 'sa'.
> 2005-11-18 12:57:55.45 logon Login succeeded for user 'ART\art'.
> Connection: Trusted.
> 2005-11-18 12:58:01.80 logon Login failed for user 'sa'.
> 2005-11-18 12:58:07.73 logon Login failed for user 'a'.
> 2005-11-18 12:58:16.75 logon Login failed for user 'a'.
> 2005-11-18 12:58:23.83 logon Login succeeded for user 'ART\art'.
> Connection: Trusted.
> 2005-11-18 12:58:37.46 logon Login succeeded for user 'ART\art'.
> Connection: Trusted.
> 2005-11-18 12:58:37.46 spid9 Starting up database 'test'.
> 2005-11-18 12:58:37.46 spid9 Opening file C:\MSSQL7\DATA\test.mdf.
> 2005-11-18 12:58:37.49 spid9 Opening file C:\MSSQL7\DATA\test.ldf.
> 2005-11-18 12:58:39.34 logon Login succeeded for user 'ART\art'.
> Connection: Trusted.
> 2005-11-18 12:58:42.93 logon Login succeeded for user 'ART\art'.
> Connection: Trusted.
> 2005-11-18 12:59:00.99 logon Login succeeded for user 'ART\art'.
> Connection: Trusted.
> 2005-11-18 13:55:32.21 logon Login succeeded for user 'ART\w tilman'.
> Connection: Trusted.
> 2005-11-18 13:55:34.14 logon Login succeeded for user 'ART\w tilman'.
> Connection: Trusted.
> 2005-11-18 13:55:35.05 spid10 Using 'xpsql70.dll' version '1998.11.13'
> to
> execute extended stored procedure 'xp_msver'.
>
> Can connect with Windows NT authentication, but not with SQL server auth.
> So
> all my
> code has stopped working ...
> When I try to make a new SQL Server registration
> I get server does not exist or access denied...
>
>
|||Ben:
I see that I'm using both SQL Server and Windows authentication; I see too
that under
"Startup service account" (also in the security tab) startup runs the
System Account. WHen I choose "This account" it defaults to "LocalSystem"
and a password I don't now - I'm scared to try this -
Under "registered SQL server properties" "Use windows NT authentication"
is checked. WHen I try to use SQL authenticaton nothing works - no matter
what
combo of login name and password I try ...
Should I try to make a new SQL server registration ?
All this is clear as mud - if anyone from MS reads this; boys, why do you
have
to make it so hard, so confusing , so badly documented ? It's hard enough
using it
productivly when you can get it in in the first place ...
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:#cQs$CX7FHA.3684@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Verify that you are using both SQL Server and Windows authentication using
> server Properties and look in the Security tab (I believe that you are
> really using both SQL Server and Windows authentication since log messages
> for Windows only authentication would show "Login failed for user 'sa'.
> Reason: Not associated with a trusted SQL Server connection").
> Which account are you using to connect to the database? Has the password
for[vbcol=seagreen]
> this account been changed?
> Ben Nevarez
>
> "Barrett Bonden" <arthur@.networks-cc.com> wrote in message
> news:sIJff.13085$ha2.13022@.fe08.lga...
auth.
>
|||If your server registration is using Windows authentication and also you can
see the server properties that means that you are a member of the system
administrators groups. Do not change the registration properties.
Also, your server is running as the system account (Security tab). Do not
change that either.
Which account is not working? sa? Is it possible that the password was
changed?
Can you create, for testing purposes, another SQL Server login and connect
with Query Analyzer using that login?
Ben Nevarez
"Barrett Bonden" <arthur@.networks-cc.com> wrote in message
news:jCQff.26341$rc7.2259@.fe12.lga...
> Ben:
> I see that I'm using both SQL Server and Windows authentication; I see
> too
> that under
> "Startup service account" (also in the security tab) startup runs the
> System Account. WHen I choose "This account" it defaults to "LocalSystem"
> and a password I don't now - I'm scared to try this -
> Under "registered SQL server properties" "Use windows NT authentication"
> is checked. WHen I try to use SQL authenticaton nothing works - no matter
> what
> combo of login name and password I try ...
> Should I try to make a new SQL server registration ?
> All this is clear as mud - if anyone from MS reads this; boys, why do you
> have
> to make it so hard, so confusing , so badly documented ? It's hard enough
> using it
> productivly when you can get it in in the first place ...
>
>
>
> "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> news:#cQs$CX7FHA.3684@.TK2MSFTNGP12.phx.gbl...
> for
> auth.
>
|||Many thanks for your help; I changed the password on the sa account, and it
now works , along, strangely , with the the other accounts I had set up.
Clearly I need to learn more about this part of the program; so far I;ve
just experimented with making it a backend for
Access -
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:OGo3LKa7FHA.1020@.TK2MSFTNGP15.phx.gbl...
> If your server registration is using Windows authentication and also you
can[vbcol=seagreen]
> see the server properties that means that you are a member of the system
> administrators groups. Do not change the registration properties.
> Also, your server is running as the system account (Security tab). Do not
> change that either.
> Which account is not working? sa? Is it possible that the password was
> changed?
> Can you create, for testing purposes, another SQL Server login and connect
> with Query Analyzer using that login?
> Ben Nevarez
>
> "Barrett Bonden" <arthur@.networks-cc.com> wrote in message
> news:jCQff.26341$rc7.2259@.fe12.lga...
"LocalSystem"[vbcol=seagreen]
matter[vbcol=seagreen]
you[vbcol=seagreen]
enough[vbcol=seagreen]
password
>
sql

More on that login problem - found the logs -

2005-11-18 12:50:12.09 logon Login failed for user 'sa'.
2005-11-18 12:50:36.85 logon Login succeeded for user 'NT
AUTHORITY\SYSTEM'. Connection: Trusted.
and some more that might be useful -
2005-11-18 12:50:40.92 logon Login succeeded for user 'NT
AUTHORITY\SYSTEM'. Connection: Trusted.
2005-11-18 12:51:01.35 logon Login succeeded for user 'NT
AUTHORITY\SYSTEM'. Connection: Trusted.
2005-11-18 12:51:53.71 logon Login failed for user 'sa'.
2005-11-18 12:57:55.45 logon Login succeeded for user 'ART\art'.
Connection: Trusted.
2005-11-18 12:58:01.80 logon Login failed for user 'sa'.
2005-11-18 12:58:07.73 logon Login failed for user 'a'.
2005-11-18 12:58:16.75 logon Login failed for user 'a'.
2005-11-18 12:58:23.83 logon Login succeeded for user 'ART\art'.
Connection: Trusted.
2005-11-18 12:58:37.46 logon Login succeeded for user 'ART\art'.
Connection: Trusted.
2005-11-18 12:58:37.46 spid9 Starting up database 'test'.
2005-11-18 12:58:37.46 spid9 Opening file C:\MSSQL7\DATA\test.mdf.
2005-11-18 12:58:37.49 spid9 Opening file C:\MSSQL7\DATA\test.ldf.
2005-11-18 12:58:39.34 logon Login succeeded for user 'ART\art'.
Connection: Trusted.
2005-11-18 12:58:42.93 logon Login succeeded for user 'ART\art'.
Connection: Trusted.
2005-11-18 12:59:00.99 logon Login succeeded for user 'ART\art'.
Connection: Trusted.
2005-11-18 13:55:32.21 logon Login succeeded for user 'ART\w tilman'.
Connection: Trusted.
2005-11-18 13:55:34.14 logon Login succeeded for user 'ART\w tilman'.
Connection: Trusted.
2005-11-18 13:55:35.05 spid10 Using 'xpsql70.dll' version '1998.11.13' to
execute extended stored procedure 'xp_msver'.
Can connect with Windows NT authentication, but not with SQL server auth. So
all my
code has stopped working ...
When I try to make a new SQL Server registration
I get server does not exist or access denied...Hi,
Verify that you are using both SQL Server and Windows authentication using
server Properties and look in the Security tab (I believe that you are
really using both SQL Server and Windows authentication since log messages
for Windows only authentication would show "Login failed for user 'sa'.
Reason: Not associated with a trusted SQL Server connection").
Which account are you using to connect to the database? Has the password for
this account been changed?
Ben Nevarez
"Barrett Bonden" <arthur@.networks-cc.com> wrote in message
news:sIJff.13085$ha2.13022@.fe08.lga...
> 2005-11-18 12:50:12.09 logon Login failed for user 'sa'.
> 2005-11-18 12:50:36.85 logon Login succeeded for user 'NT
> AUTHORITY\SYSTEM'. Connection: Trusted.
> and some more that might be useful -
> 2005-11-18 12:50:40.92 logon Login succeeded for user 'NT
> AUTHORITY\SYSTEM'. Connection: Trusted.
> 2005-11-18 12:51:01.35 logon Login succeeded for user 'NT
> AUTHORITY\SYSTEM'. Connection: Trusted.
> 2005-11-18 12:51:53.71 logon Login failed for user 'sa'.
> 2005-11-18 12:57:55.45 logon Login succeeded for user 'ART\art'.
> Connection: Trusted.
> 2005-11-18 12:58:01.80 logon Login failed for user 'sa'.
> 2005-11-18 12:58:07.73 logon Login failed for user 'a'.
> 2005-11-18 12:58:16.75 logon Login failed for user 'a'.
> 2005-11-18 12:58:23.83 logon Login succeeded for user 'ART\art'.
> Connection: Trusted.
> 2005-11-18 12:58:37.46 logon Login succeeded for user 'ART\art'.
> Connection: Trusted.
> 2005-11-18 12:58:37.46 spid9 Starting up database 'test'.
> 2005-11-18 12:58:37.46 spid9 Opening file C:\MSSQL7\DATA\test.mdf.
> 2005-11-18 12:58:37.49 spid9 Opening file C:\MSSQL7\DATA\test.ldf.
> 2005-11-18 12:58:39.34 logon Login succeeded for user 'ART\art'.
> Connection: Trusted.
> 2005-11-18 12:58:42.93 logon Login succeeded for user 'ART\art'.
> Connection: Trusted.
> 2005-11-18 12:59:00.99 logon Login succeeded for user 'ART\art'.
> Connection: Trusted.
> 2005-11-18 13:55:32.21 logon Login succeeded for user 'ART\w tilman'.
> Connection: Trusted.
> 2005-11-18 13:55:34.14 logon Login succeeded for user 'ART\w tilman'.
> Connection: Trusted.
> 2005-11-18 13:55:35.05 spid10 Using 'xpsql70.dll' version '1998.11.13'
> to
> execute extended stored procedure 'xp_msver'.
>
> Can connect with Windows NT authentication, but not with SQL server auth.
> So
> all my
> code has stopped working ...
> When I try to make a new SQL Server registration
> I get server does not exist or access denied...
>
>|||Ben:
I see that I'm using both SQL Server and Windows authentication; I see too
that under
"Startup service account" (also in the security tab) startup runs the
System Account. WHen I choose "This account" it defaults to "LocalSystem"
and a password I don't now - I'm scared to try this -
Under "registered SQL server properties" "Use windows NT authentication"
is checked. WHen I try to use SQL authenticaton nothing works - no matter
what
combo of login name and password I try ...
Should I try to make a new SQL server registration ?
All this is clear as mud - if anyone from MS reads this; boys, why do you
have
to make it so hard, so confusing , so badly documented ? It's hard enough
using it
productivly when you can get it in in the first place ...
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:#cQs$CX7FHA.3684@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Verify that you are using both SQL Server and Windows authentication using
> server Properties and look in the Security tab (I believe that you are
> really using both SQL Server and Windows authentication since log messages
> for Windows only authentication would show "Login failed for user 'sa'.
> Reason: Not associated with a trusted SQL Server connection").
> Which account are you using to connect to the database? Has the password
for
> this account been changed?
> Ben Nevarez
>
> "Barrett Bonden" <arthur@.networks-cc.com> wrote in message
> news:sIJff.13085$ha2.13022@.fe08.lga...
auth.[vbcol=seagreen]
>|||If your server registration is using Windows authentication and also you can
see the server properties that means that you are a member of the system
administrators groups. Do not change the registration properties.
Also, your server is running as the system account (Security tab). Do not
change that either.
Which account is not working? sa? Is it possible that the password was
changed?
Can you create, for testing purposes, another SQL Server login and connect
with Query Analyzer using that login?
Ben Nevarez
"Barrett Bonden" <arthur@.networks-cc.com> wrote in message
news:jCQff.26341$rc7.2259@.fe12.lga...
> Ben:
> I see that I'm using both SQL Server and Windows authentication; I see
> too
> that under
> "Startup service account" (also in the security tab) startup runs the
> System Account. WHen I choose "This account" it defaults to "LocalSystem"
> and a password I don't now - I'm scared to try this -
> Under "registered SQL server properties" "Use windows NT authentication"
> is checked. WHen I try to use SQL authenticaton nothing works - no matter
> what
> combo of login name and password I try ...
> Should I try to make a new SQL server registration ?
> All this is clear as mud - if anyone from MS reads this; boys, why do you
> have
> to make it so hard, so confusing , so badly documented ? It's hard enough
> using it
> productivly when you can get it in in the first place ...
>
>
>
> "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> news:#cQs$CX7FHA.3684@.TK2MSFTNGP12.phx.gbl...
> for
> auth.
>|||Many thanks for your help; I changed the password on the sa account, and it
now works , along, strangely , with the the other accounts I had set up.
Clearly I need to learn more about this part of the program; so far I;ve
just experimented with making it a backend for
Access -
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:OGo3LKa7FHA.1020@.TK2MSFTNGP15.phx.gbl...
> If your server registration is using Windows authentication and also you
can
> see the server properties that means that you are a member of the system
> administrators groups. Do not change the registration properties.
> Also, your server is running as the system account (Security tab). Do not
> change that either.
> Which account is not working? sa? Is it possible that the password was
> changed?
> Can you create, for testing purposes, another SQL Server login and connect
> with Query Analyzer using that login?
> Ben Nevarez
>
> "Barrett Bonden" <arthur@.networks-cc.com> wrote in message
> news:jCQff.26341$rc7.2259@.fe12.lga...
"LocalSystem"[vbcol=seagreen]
matter[vbcol=seagreen]
you[vbcol=seagreen]
enough[vbcol=seagreen]
password[vbcol=seagreen]
>