OPENQUERY UPDATE Syntax help needed

Hi All

I am updating a local table based on inner join between local table
and remote table.

Update LocalTable
SET Field1 = B.Field1
FROM LinkedServer.dbname.dbo.RemoteTable B
INNER JOIN LocalTable A
ON B.Field2 = A.Field2
AND B.Field3 = A.Field3

This query takes 18 minutes to run.
I am hoping to speed up the process by writing in OPENQUERY syntax.

Thanks
RS(rshivaraman@.gmail.com) writes:

Quote:

Originally Posted by

I am updating a local table based on inner join between local table
and remote table.
>
Update LocalTable
SET Field1 = B.Field1
FROM LinkedServer.dbname.dbo.RemoteTable B
INNER JOIN LocalTable A
ON B.Field2 = A.Field2
AND B.Field3 = A.Field3
>
This query takes 18 minutes to run.
I am hoping to speed up the process by writing in OPENQUERY syntax.

UPDATE LocalTable
SET Field1 = B.Field1
FROM OPENQUERY(LINKEDSERVER,
'SELECT Field1, Field2, Field3 FROM dbname.dbo.RemoteTable) B
INNER JOIN LocalTable A
ON B.Field2 = A.Field2
AND B.Field3 = A.Field3

I would not really expect this to perform better.

Distributed queries are always difficult, but it's difficult to suggest
anything without further knowledge about the table. How big are the
two tables?

--
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

openquery update and optimistic concurrency

Hi, I need to update a mySQL database through a linked server in SQL.

I can successfully add, delete, but struggle to update a row twice.

exec ('UPDATE OPENQUERY (SIBC, SELECT UID, value1, value2 FROM table1 WHERE UID= "SCEP"'')
SET value1= "hello" WHERE UID= "SCEP"')

The first time I run the update, it succeeds, but thereafter I get the following error message :

OLE DB provider 'MSDASQL' could not UPDATE table '[MSDASQL]'. The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized.

[OLE/DB provider returned message: Row cannot be located for updating. Some values may have been changed since it was last read.]

OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::SetData returned 0x80040e38: The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized.].

Any ideas ?

Thanks.

Here are some suggestions:
1. This might be a specific limitation in the ODBC driver and/or how it interacts with the OLE/DB for ODBC drivers Provider (MSDASQL). You might try recreating this on another type of back-end to see if it reproduces there.

2. If another process is updating values on the mysql database, you may very well have optimistic concurrency issues...

3. You could try using 4-part names instead of openquery:
update sibc.dbo.db.table1 set value1='hello' where uid='scep';

4. You could do a pass-through query, as you are really only running queries against this back-end and not passing any data from SQL Server.

Conor Cunningham

|||Hi Conor, thanks for your reply.

The only process updating the system is my application as it's on the development environment.

I have an unusual issue in that I can update a datetime field in mySQL only once I've provided it a value explicitly through a mySQL query analyser utility.

The other odd problem I have is that when I perform the update, it has to actually update a field otherwise it fails, thus if I try update a column TEMP1 with a value of 1, but it already contains a value of 1, it fails.

PS: the provider is a mySQL provider, which doesn't allow 4 part naming in SQL.

I've a feeling the issue could exist with the ODBC driver, but unfortunitely the mySQL and Microsoft communities do not seem to work together too nicely.

Thanks for your help.

Karlo
|||This looks unclear.

It doesen't make sense to me to update the results of a select query.
If this worked the first time, my guess is that the table in the database did not change, only the clients memory-representation of it, and this confused the driver at the second try.

Not sure if I'm on the right track, but you could try to send the update query directly to the linked server.

openquery update and optimistic concurrency

Hi, I need to update a mySQL database through a linked server in SQL.

I can successfully add, delete, but struggle to update a row twice.

exec ('UPDATE OPENQUERY (SIBC, SELECT UID, value1, value2 FROM table1 WHERE UID= "SCEP"'')
SET value1= "hello" WHERE UID= "SCEP"')

The first time I run the update, it succeeds, but thereafter I get the following error message :

OLE DB provider 'MSDASQL' could not UPDATE table '[MSDASQL]'. The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized.

[OLE/DB provider returned message: Row cannot be located for updating. Some values may have been changed since it was last read.]

OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::SetData returned 0x80040e38: The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized.].

Any ideas ?

Thanks.

Here are some suggestions:
1. This might be a specific limitation in the ODBC driver and/or how it interacts with the OLE/DB for ODBC drivers Provider (MSDASQL). You might try recreating this on another type of back-end to see if it reproduces there.

2. If another process is updating values on the mysql database, you may very well have optimistic concurrency issues...

3. You could try using 4-part names instead of openquery:
update sibc.dbo.db.table1 set value1='hello' where uid='scep';

4. You could do a pass-through query, as you are really only running queries against this back-end and not passing any data from SQL Server.

Conor Cunningham

|||Hi Conor, thanks for your reply.

The only process updating the system is my application as it's on the development environment.

I have an unusual issue in that I can update a datetime field in mySQL only once I've provided it a value explicitly through a mySQL query analyser utility.

The other odd problem I have is that when I perform the update, it has to actually update a field otherwise it fails, thus if I try update a column TEMP1 with a value of 1, but it already contains a value of 1, it fails.

PS: the provider is a mySQL provider, which doesn't allow 4 part naming in SQL.

I've a feeling the issue could exist with the ODBC driver, but unfortunitely the mySQL and Microsoft communities do not seem to work together too nicely.

Thanks for your help.

Karlo|||This looks unclear.

It doesen't make sense to me to update the results of a select query.
If this worked the first time, my guess is that the table in the database did not change, only the clients memory-representation of it, and this confused the driver at the second try.

Not sure if I'm on the right track, but you could try to send the update query directly to the linked server.

OpenQuery to a LinkedServer

I have SQL Server 2000 with Pervasive SQL 2000i attached
as a linked server. When I run the following...
UPDATE OPENQUERY(LINKEDSERVER, 'SELECT "START_ORDER_NO"
FROM "OECTLFIL" WHERE "FILE_KEY" = 1')
SET START_ORDER_NO = 0
--The START_ORDER_NO field contains a 48
Changing the SET clause to ...
SET START_ORDER_NO = 1~9
--The START_ORDER_NO field contains 49~57 respectively.
SET START_ORDER_NO = 10
--The START_ORDER_NO field contains 12337
SET START_ORDER_NO = 11
--The START_ORDER_NO field contains 12593
SET START_ORDER_NO = 12
--The START_ORDER_NO field contains 12849
incrementing by 256 as I increase the value I write...
The LinkedServer is Pervasive SQL 2000i using 'OLE DB
Provider for ODBC'
The START_ORDER_NO field is a Numeric(8,0)
I'm thinking some kind of Unicode, or translation or code
page issue, but I haven't had any luck yet.
Any help would be greatly appreciated.
Hi Bob,
Thanks for your post.
From your descriptions, I understood that you would like to set the number
to be one and it appears to be something else. Correct me if I was wrong.
This issue seems strange, here are some steps I think you could make a try
to see whether it will make any further progress
First of all, try to use xp_enum_loedb_providers listed in the document
below to setup your linked server.
INF: xp_enum_oledb_providers Enumerates the OLE DB Providers
http://support.microsoft.com/?id=216575
Secondly, could you use INTEGER or UINTEGER in Pervasive.SQL 2000 instead
of NUMERIC(8,0)?
Pervasive.SQL 2000 Supported Data Types
http://www.pervasive.com/library/doc...BtrDType2.html
Unfortuantely, I do not have Pervasive SQL 2000 installed and cannot
reporduce it. If I may, I would like to suggest you seeing whether
Pervasive Software had meet this before.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||You understand correctly.
I am using 'OLEDB Provider for ODBC' or MSDASQL
The field types have been defined by the original vendor, Pervasive SQL
2000 is part of a proprietary ERP system. I don't have the luxury of
changing field definitions.
Thanks for the information though.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Hi Bob,
Thanks for your prompt updates letting me know the status of the issue.
First of all, you may use (numeric(8,0), 0) to test whether it will make
any progress.
Secondly, what odbc driver you are using? I think you may try to check this
with that Pervasive ODBC Driver vendor about issue.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

OPENQUERY Question. How to update using 2 tables

All,

Can someone help me with the following SQL and help me write it in an
OPENQUERY format. I am running the following code from a SQL Server 7
box, trying to update a table in an Oracle Linked Server. The code
runs fine, except it takes almost an hour to complete. I know if I run
via OPENQUERY,I can get the same done in much less time.

Some of the relevant information is as follows:

ORACLE_HBCPRD04 is a linked Oracle Server.
SITEADDRESS is a table in Oracle
#SiteAddress_New is a table in SQL Server.

UPDATE ORACLE_HBCPRD04...SITEADDRESS
SET
CUST_ADDR1 = CASE WHEN SiteAddress_New.CUST_ADDR1 = '' THEN NULL
ELSE SiteAddress_New.CUST_ADDR1 END,
CUST_ADDR2 = CASE WHEN SiteAddress_New.CUST_ADDR2 = '' THEN NULL
ELSE SiteAddress_New.CUST_ADDR2 END ,
CUST_ADDR3 = CASE WHEN SiteAddress_New.CUST_ADDR3 = '' THEN NULL
ELSE SiteAddress_New.CUST_ADDR3 END,
CUST_ADDR4 = CASE WHEN SiteAddress_New.CUST_ADDR4 = '' THEN NULL
ELSE SiteAddress_New.CUST_ADDR4 END ,
CTY_NM = CASE WHEN SiteAddress_New.CTY_NM = '' THEN NULL ELSE
SiteAddress_New.CTY_NM END,
ST_ABBR = CASE WHEN SiteAddress_New.ST_ABBR = '' THEN NULL ELSE
SiteAddress_New.ST_ABBR END,
POST_CD = CASE WHEN SiteAddress_New.POST_CD = '' THEN NULL ELSE
SiteAddress_New.POST_CD END,
CNTY_NM = CASE WHEN SiteAddress_New.CNTY_NM = '' THEN NULL ELSE
SiteAddress_New.CNTY_NM END,
CNTRY_NM = CASE WHEN SiteAddress_New.CNTRY_NM = '' THEN NULL ELSE
SiteAddress_New.CNTRY_NM END,
ADDR_STAT = NULL ,
LAST_UPDATE_DATE = SiteAddress_New.LAST_UPDATE_DATE

FROM
ORACLE_HBCPRD04...SITEADDRESS SiteAddress INNER JOIN
#SiteAddress_New SiteAddress_New ON
SiteAddress.LEGACY_ADDR_ID = SiteAddress_New.LEGACY_ADDR_ID

WHERE
UPPER(SiteAddress_New.PROCESS_CODE) = 'U'

Best Regards,

addi"addi" <addi_s@.hotmail.com> wrote in message
news:6f426fb3.0406090845.6afdbf32@.posting.google.c om...
> All,
> Can someone help me with the following SQL and help me write it in an
> OPENQUERY format. I am running the following code from a SQL Server 7
> box, trying to update a table in an Oracle Linked Server. The code
> runs fine, except it takes almost an hour to complete. I know if I run
> via OPENQUERY,I can get the same done in much less time.
> Some of the relevant information is as follows:
> ORACLE_HBCPRD04 is a linked Oracle Server.
> SITEADDRESS is a table in Oracle
> #SiteAddress_New is a table in SQL Server.
> UPDATE ORACLE_HBCPRD04...SITEADDRESS
> SET
> CUST_ADDR1 = CASE WHEN SiteAddress_New.CUST_ADDR1 = '' THEN NULL
> ELSE SiteAddress_New.CUST_ADDR1 END,
> CUST_ADDR2 = CASE WHEN SiteAddress_New.CUST_ADDR2 = '' THEN NULL
> ELSE SiteAddress_New.CUST_ADDR2 END ,
> CUST_ADDR3 = CASE WHEN SiteAddress_New.CUST_ADDR3 = '' THEN NULL
> ELSE SiteAddress_New.CUST_ADDR3 END,
> CUST_ADDR4 = CASE WHEN SiteAddress_New.CUST_ADDR4 = '' THEN NULL
> ELSE SiteAddress_New.CUST_ADDR4 END ,
> CTY_NM = CASE WHEN SiteAddress_New.CTY_NM = '' THEN NULL ELSE
> SiteAddress_New.CTY_NM END,
> ST_ABBR = CASE WHEN SiteAddress_New.ST_ABBR = '' THEN NULL ELSE
> SiteAddress_New.ST_ABBR END,
> POST_CD = CASE WHEN SiteAddress_New.POST_CD = '' THEN NULL ELSE
> SiteAddress_New.POST_CD END,
> CNTY_NM = CASE WHEN SiteAddress_New.CNTY_NM = '' THEN NULL ELSE
> SiteAddress_New.CNTY_NM END,
> CNTRY_NM = CASE WHEN SiteAddress_New.CNTRY_NM = '' THEN NULL ELSE
> SiteAddress_New.CNTRY_NM END,
> ADDR_STAT = NULL ,
> LAST_UPDATE_DATE = SiteAddress_New.LAST_UPDATE_DATE
> FROM
> ORACLE_HBCPRD04...SITEADDRESS SiteAddress INNER JOIN
> #SiteAddress_New SiteAddress_New ON
> SiteAddress.LEGACY_ADDR_ID = SiteAddress_New.LEGACY_ADDR_ID
> WHERE
> UPPER(SiteAddress_New.PROCESS_CODE) = 'U'
> Best Regards,
> addi

I don't really understand your question - OPENQUERY() executes entirely on
the linked server, so it would not be possible to join to a local MSSQL
table.

It's hard to say what the issue is without knowing more about how many rows
are involved, but you might want to look at the REMOTE join hint, to see if
a remote join is faster. This may help if the MSSQL table is much smaller
than the Oracle one.

See also this post:

http://groups.google.com/groups?hl=...1.microsoft.com

Simon|||addi (addi_s@.hotmail.com) writes:
> Can someone help me with the following SQL and help me write it in an
> OPENQUERY format. I am running the following code from a SQL Server 7
> box, trying to update a table in an Oracle Linked Server. The code
> runs fine, except it takes almost an hour to complete. I know if I run
> via OPENQUERY,I can get the same done in much less time.

What you could do is to first insert the data in the temptable into a
table on the Oracle side. Then data in the target table does not have
to move forth and back across the network.

Then you would run the UPDATE statement in Oracle (with Oracle syntax).
The best method would be to so through a stored procedure, but I don't
know exactly what Oracle offers in this area.

OPERQUERY? It may work, but OPENQUERY is not intended for update statements,
but is a rowset provider. You could try:

SELECT 1 FROM OPENQUERY(ORACLE_HBCPRD04, 'UPDATE SITEADDRESS ...')

But if you try the same operation against SQL Server, this will fail
with the messages "...indicates that ... does not return any records".
And I would guess something similar will happen with Oracle. For SQL
Server I know of a poor workaround, but if there is something similar
for Oracle I don't know. Of course you could throw in a dummy SELECT
into the batch you pass to Oracle. And in any case, you will produce
a result set, which might be what calling program might expect. (You
could hide with INSERT EXEC though.)

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

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

Opendatasource

I was able to use the following select qry with an OpenDataSource, but I
recieve and error when I change it to an Update qry.
SELECT *
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="\\Posiden\apps\Access\MsData\holdbk.mdb";
User ID=Admin;Password='
)...[HoldOrderDetails_View] h,BoxIdCount b
Where h.[Line#]= b.BoxId and b.Task = 1
but when I change it to and Update it errors out. See code below
update h
set h.LOCATION = b.Location
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="\\Posiden\apps\Access\MsData\holdbk.mdb";
User ID=Admin;Password='
)...[HoldOrderDetails_View] h,BoxIdCount b
Where h.[Line#]= b.BoxId and b.Task = 1
I get the following error; Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'h'.
Any suggestions.
Thank you in advance for your helpdouble post by mistake?
"bhorwitz" <bhorwitz@.discussions.microsoft.com> wrote in message
news:33E09107-36E6-4131-A5F8-7D1053521CD3@.microsoft.com...
> I was able to use the following select qry with an OpenDataSource, but I
> recieve and error when I change it to an Update qry.
> SELECT *
> FROM OPENDATASOURCE(
> 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="\\Posiden\apps\Access\MsData\holdbk.mdb";
> User ID=Admin;Password='
> )...[HoldOrderDetails_View] h,BoxIdCount b
> Where h.[Line#]= b.BoxId and b.Task = 1
> but when I change it to and Update it errors out. See code below
> update h
> set h.LOCATION = b.Location
> FROM OPENDATASOURCE(
> 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="\\Posiden\apps\Access\MsData\holdbk.mdb";
> User ID=Admin;Password='
> )...[HoldOrderDetails_View] h,BoxIdCount b
> Where h.[Line#]= b.BoxId and b.Task = 1
> I get the following error; Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'h'.
> Any suggestions.
> Thank you in advance for your help|||try this..
untested..
update h
set LOCATION = b.Location
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="\\Posiden\apps\Access\MsData\holdbk.mdb";
User ID=Admin;Password='
)...[HoldOrderDetails_View] h,BoxIdCount b
Where h.[Line#]= b.BoxId and b.Task = 1

Opendatasource

I'm trying to use OPENDATASOURCE in t-sql to update data on our AS/400 from MSSQL but don't know the property value for provider_name. Please help!!!"Control Panel"\"Administrative Tools"\"Data Sources(ODBC)"
Select a tab "File DSN", click "Add..", select server and click "Advanced".
Now you see sub-driver for ODBC (MSDASQL)

For MS SQL Server in MS SQL Server
select *
from OpenRowset('MSDASQL', 'Driver={SQL Server};Server=MyServer;User=MyUser','select * from sysobjects')

I do not have A/400 driver installed, that is why I cannot test this for A/400.

Good luck !|||my diagnosis is the your transactional properties of your neural defilibrator is going to require that you just replace the AS400 with SQL Server.

you better get to work.sql

Open a DSN connection in TSQL

I'm trying to update an SQL DB from data in Quickbooks. I am trying to find if I can open a dsn connection in a stored procedure, called from an ASP page, to the quickbooks db using odbc. The driver I have for odbc does not allow for linked servers. I'm very new to stored procedures and such so please bear with me. Thxs.

Not easily would be my answer.

You could write a CLR procedure, but if your new to stored procedures I would assume C#/VB.Net is also something new.

You should be able to create a linked server Do you have an example connection string to connect to Quikck books

|||

You are correct, I don't know VB.Net either. I know ASP that is about it.

I don't have an example for you, I can create a linked server to the Quickbooks db in Ent Manager but then I can't figure out how to access the db from SQL.

If I am not mistaken you have to have four-part naming to access the db but I believe the ODBC driver supplied with Quickbooks does not support this, you can only use this with a driver purchased from the company that supplies the driver included with Quickbooks.

Any help is greatly appreciated. Thxs.

Only update changed columns

I have a stored procedure that inserts values into five columns of a
table. I need another stored procedure that will allow the user to pass
one or more of those parameters and update only the column for the data
that was passed. In other words, the update may only have one or two of
the parameters that was originally provided in the insert. Therefore, I
do not want to update the columns that did not change. What is the
proper way to handle this situation?binder (rgondzur@.gmail.com) writes:

Quote:

Originally Posted by

I have a stored procedure that inserts values into five columns of a
table. I need another stored procedure that will allow the user to pass
one or more of those parameters and update only the column for the data
that was passed. In other words, the update may only have one or two of
the parameters that was originally provided in the insert. Therefore, I
do not want to update the columns that did not change. What is the
proper way to handle this situation?

In our application, most insert/update procedures exposes about all
columns in the table, and when the client fills the GUI, it reads all
columns from the table. Thus there is little reason to check what
actually changed. (Except for auditing.)

If you want to expose an interface of a procedure where the caller only
specifies what the to change, because it has for some reason not read
all existing columns (and this makes sense for a client that is a
monitori or similar), you could do:

CREATE PROCEDURE update_sp @.keyvalue sometype,
@.par1 someothertype = NULL,
@.par2 yetanothertype = NULL,
... AS

UPDATE tbl
SET col1 = coalesce(@.par1, col1),
col2 = coalesce(@.par2, col2),
...
WHERE keycol = @.keyvalue

That is, the procedure accepts parameters for all updatable columns,
but the caller passes non-NULL values for those it does not want to
change. This presumes that a caller never want to set a column to
NULL. If this is a required, you need to add one flag parameter for
each value parameter.

--
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

Only seeing dbs that have access to

Hi,
I came across a script to update the sp_MSdbuseraccess SP so that when a
user logs in to SQL Server 2000 using Enterprise Manager in SQL 2000 they
only see databases that they have access to
(http://support.microsoft.com/default.aspx/kb/889696). This works fine apart
from if users are using the new SQL Management Studio tool to connect to the
SQL 2000 server. Now they can see all databases again! Has anyone overcome
this issue?
Thanks - Kate.This is most probably not possible. SSMS doesn't use stored procedures when
doing things like
querying for what databases exist (*). And there isn't anything in the 2000
engine for this. There
is in 2005, where you by default (at the engine level) only see objects that
you actually have
access to (except for databases - you see all, but this is controllable thou
gh a REVOKE statement).
(*) I just ran a profiler trace to see the SQL statement submitted when expa
nding the databases
folder or the drop-down lost for a query windows. In both cases there was a
SELECT against a system
table. This was against a 2005 instance (I don't have a setup right now wher
e I can connect SSMS
against a 2000 instance), but I very much doubt it is any different.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kate" <Kate_w112@.hotmail.com> wrote in message news:eHC6zgc3HHA.2064@.TK2MSFTNGP03.phx.gbl..
.
> Hi,
> I came across a script to update the sp_MSdbuseraccess SP so that when a u
ser logs in to SQL
> Server 2000 using Enterprise Manager in SQL 2000 they only see databases t
hat they have access to
> (http://support.microsoft.com/default.aspx/kb/889696). This works fine apa
rt from if users are
> using the new SQL Management Studio tool to connect to the SQL 2000 server
. Now they can see all
> databases again! Has anyone overcome this issue?
> Thanks - Kate.|||Ok great - what was the SQL statement?
thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Ozw%23nrd3HHA.2752@.TK2MSFTNGP06.phx.gbl...
> This is most probably not possible. SSMS doesn't use stored procedures
> when doing things like querying for what databases exist (*). And there
> isn't anything in the 2000 engine for this. There is in 2005, where you by
> default (at the engine level) only see objects that you actually have
> access to (except for databases - you see all, but this is controllable
> though a REVOKE statement).
> (*) I just ran a profiler trace to see the SQL statement submitted when
> expanding the databases folder or the drop-down lost for a query windows.
> In both cases there was a SELECT against a system table. This was against
> a 2005 instance (I don't have a setup right now where I can connect SSMS
> against a 2000 instance), but I very much doubt it is any different.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Kate" <Kate_w112@.hotmail.com> wrote in message
> news:eHC6zgc3HHA.2064@.TK2MSFTNGP03.phx.gbl...
>|||you mention a REVOKE statement for SQL 2005 installs - could you elaborate
on this?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Ozw%23nrd3HHA.2752@.TK2MSFTNGP06.phx.gbl...
> This is most probably not possible. SSMS doesn't use stored procedures
> when doing things like querying for what databases exist (*). And there
> isn't anything in the 2000 engine for this. There is in 2005, where you by
> default (at the engine level) only see objects that you actually have
> access to (except for databases - you see all, but this is controllable
> though a REVOKE statement).
> (*) I just ran a profiler trace to see the SQL statement submitted when
> expanding the databases folder or the drop-down lost for a query windows.
> In both cases there was a SELECT against a system table. This was against
> a 2005 instance (I don't have a setup right now where I can connect SSMS
> against a 2000 instance), but I very much doubt it is any different.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Kate" <Kate_w112@.hotmail.com> wrote in message
> news:eHC6zgc3HHA.2064@.TK2MSFTNGP03.phx.gbl...
>|||Basically a SELECT from sys.databases. Remember, I was running against a 200
5 instance, so you won't
see the same SELECT against a 2000 instance (you will probably see some SELE
CT from sysdatabases).
But you can easily check this yourself using the same method I did (using Pr
ofiler).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kate" <Kate_w112@.hotmail.com> wrote in message news:eLAEWnf3HHA.464@.TK2MSFTNGP02.phx.gbl...

> Ok great - what was the SQL statement?
> thanks.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:Ozw%23nrd3HHA.2752@.TK2MSFTNGP06.phx.gbl...
>|||Check out the "VIEW ANY DATABASE" privilege. Try below, for example:
CREATE LOGIN x WITH PASSWORD = 'hjus8#."3'
EXECUTE AS LOGIN = 'x'
SELECT name FROM sys.databases --14 rows
REVERT
DENY VIEW ANY DATABASE TO x
EXECUTE AS LOGIN = 'x'
SELECT name FROM sys.databases --2 rows
REVERT
GO
DROP LOGIN x
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kate" <Kate_w112@.hotmail.com> wrote in message news:Oj0C6nf3HHA.5796@.TK2MSFTNGP05.phx.gbl..
.
> you mention a REVOKE statement for SQL 2005 installs - could you elaborate
> on this?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:Ozw%23nrd3HHA.2752@.TK2MSFTNGP06.phx.gbl...
>

Only seeing dbs that have access to

Hi,
I came across a script to update the sp_MSdbuseraccess SP so that when a
user logs in to SQL Server 2000 using Enterprise Manager in SQL 2000 they
only see databases that they have access to
(http://support.microsoft.com/default.aspx/kb/889696). This works fine apart
from if users are using the new SQL Management Studio tool to connect to the
SQL 2000 server. Now they can see all databases again! Has anyone overcome
this issue?
Thanks - Kate.This is most probably not possible. SSMS doesn't use stored procedures when doing things like
querying for what databases exist (*). And there isn't anything in the 2000 engine for this. There
is in 2005, where you by default (at the engine level) only see objects that you actually have
access to (except for databases - you see all, but this is controllable though a REVOKE statement).
(*) I just ran a profiler trace to see the SQL statement submitted when expanding the databases
folder or the drop-down lost for a query windows. In both cases there was a SELECT against a system
table. This was against a 2005 instance (I don't have a setup right now where I can connect SSMS
against a 2000 instance), but I very much doubt it is any different.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kate" <Kate_w112@.hotmail.com> wrote in message news:eHC6zgc3HHA.2064@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I came across a script to update the sp_MSdbuseraccess SP so that when a user logs in to SQL
> Server 2000 using Enterprise Manager in SQL 2000 they only see databases that they have access to
> (http://support.microsoft.com/default.aspx/kb/889696). This works fine apart from if users are
> using the new SQL Management Studio tool to connect to the SQL 2000 server. Now they can see all
> databases again! Has anyone overcome this issue?
> Thanks - Kate.|||Ok great - what was the SQL statement?
thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Ozw%23nrd3HHA.2752@.TK2MSFTNGP06.phx.gbl...
> This is most probably not possible. SSMS doesn't use stored procedures
> when doing things like querying for what databases exist (*). And there
> isn't anything in the 2000 engine for this. There is in 2005, where you by
> default (at the engine level) only see objects that you actually have
> access to (except for databases - you see all, but this is controllable
> though a REVOKE statement).
> (*) I just ran a profiler trace to see the SQL statement submitted when
> expanding the databases folder or the drop-down lost for a query windows.
> In both cases there was a SELECT against a system table. This was against
> a 2005 instance (I don't have a setup right now where I can connect SSMS
> against a 2000 instance), but I very much doubt it is any different.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Kate" <Kate_w112@.hotmail.com> wrote in message
> news:eHC6zgc3HHA.2064@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I came across a script to update the sp_MSdbuseraccess SP so that when a
>> user logs in to SQL Server 2000 using Enterprise Manager in SQL 2000 they
>> only see databases that they have access to
>> (http://support.microsoft.com/default.aspx/kb/889696). This works fine
>> apart from if users are using the new SQL Management Studio tool to
>> connect to the SQL 2000 server. Now they can see all databases again! Has
>> anyone overcome this issue?
>> Thanks - Kate.
>|||you mention a REVOKE statement for SQL 2005 installs - could you elaborate
on this?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Ozw%23nrd3HHA.2752@.TK2MSFTNGP06.phx.gbl...
> This is most probably not possible. SSMS doesn't use stored procedures
> when doing things like querying for what databases exist (*). And there
> isn't anything in the 2000 engine for this. There is in 2005, where you by
> default (at the engine level) only see objects that you actually have
> access to (except for databases - you see all, but this is controllable
> though a REVOKE statement).
> (*) I just ran a profiler trace to see the SQL statement submitted when
> expanding the databases folder or the drop-down lost for a query windows.
> In both cases there was a SELECT against a system table. This was against
> a 2005 instance (I don't have a setup right now where I can connect SSMS
> against a 2000 instance), but I very much doubt it is any different.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Kate" <Kate_w112@.hotmail.com> wrote in message
> news:eHC6zgc3HHA.2064@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I came across a script to update the sp_MSdbuseraccess SP so that when a
>> user logs in to SQL Server 2000 using Enterprise Manager in SQL 2000 they
>> only see databases that they have access to
>> (http://support.microsoft.com/default.aspx/kb/889696). This works fine
>> apart from if users are using the new SQL Management Studio tool to
>> connect to the SQL 2000 server. Now they can see all databases again! Has
>> anyone overcome this issue?
>> Thanks - Kate.
>|||Basically a SELECT from sys.databases. Remember, I was running against a 2005 instance, so you won't
see the same SELECT against a 2000 instance (you will probably see some SELECT from sysdatabases).
But you can easily check this yourself using the same method I did (using Profiler).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kate" <Kate_w112@.hotmail.com> wrote in message news:eLAEWnf3HHA.464@.TK2MSFTNGP02.phx.gbl...
> Ok great - what was the SQL statement?
> thanks.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:Ozw%23nrd3HHA.2752@.TK2MSFTNGP06.phx.gbl...
>> This is most probably not possible. SSMS doesn't use stored procedures when doing things like
>> querying for what databases exist (*). And there isn't anything in the 2000 engine for this.
>> There is in 2005, where you by default (at the engine level) only see objects that you actually
>> have access to (except for databases - you see all, but this is controllable though a REVOKE
>> statement).
>> (*) I just ran a profiler trace to see the SQL statement submitted when expanding the databases
>> folder or the drop-down lost for a query windows. In both cases there was a SELECT against a
>> system table. This was against a 2005 instance (I don't have a setup right now where I can
>> connect SSMS against a 2000 instance), but I very much doubt it is any different.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Kate" <Kate_w112@.hotmail.com> wrote in message news:eHC6zgc3HHA.2064@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I came across a script to update the sp_MSdbuseraccess SP so that when a user logs in to SQL
>> Server 2000 using Enterprise Manager in SQL 2000 they only see databases that they have access
>> to (http://support.microsoft.com/default.aspx/kb/889696). This works fine apart from if users
>> are using the new SQL Management Studio tool to connect to the SQL 2000 server. Now they can see
>> all databases again! Has anyone overcome this issue?
>> Thanks - Kate.
>|||Check out the "VIEW ANY DATABASE" privilege. Try below, for example:
CREATE LOGIN x WITH PASSWORD = 'hjus8#."3'
EXECUTE AS LOGIN = 'x'
SELECT name FROM sys.databases --14 rows
REVERT
DENY VIEW ANY DATABASE TO x
EXECUTE AS LOGIN = 'x'
SELECT name FROM sys.databases --2 rows
REVERT
GO
DROP LOGIN x
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kate" <Kate_w112@.hotmail.com> wrote in message news:Oj0C6nf3HHA.5796@.TK2MSFTNGP05.phx.gbl...
> you mention a REVOKE statement for SQL 2005 installs - could you elaborate
> on this?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:Ozw%23nrd3HHA.2752@.TK2MSFTNGP06.phx.gbl...
>> This is most probably not possible. SSMS doesn't use stored procedures
>> when doing things like querying for what databases exist (*). And there
>> isn't anything in the 2000 engine for this. There is in 2005, where you by
>> default (at the engine level) only see objects that you actually have
>> access to (except for databases - you see all, but this is controllable
>> though a REVOKE statement).
>> (*) I just ran a profiler trace to see the SQL statement submitted when
>> expanding the databases folder or the drop-down lost for a query windows.
>> In both cases there was a SELECT against a system table. This was against
>> a 2005 instance (I don't have a setup right now where I can connect SSMS
>> against a 2000 instance), but I very much doubt it is any different.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Kate" <Kate_w112@.hotmail.com> wrote in message
>> news:eHC6zgc3HHA.2064@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I came across a script to update the sp_MSdbuseraccess SP so that when a
>> user logs in to SQL Server 2000 using Enterprise Manager in SQL 2000 they
>> only see databases that they have access to
>> (http://support.microsoft.com/default.aspx/kb/889696). This works fine
>> apart from if users are using the new SQL Management Studio tool to
>> connect to the SQL 2000 server. Now they can see all databases again! Has
>> anyone overcome this issue?
>> Thanks - Kate.
>

Only one expression can be specified in the select list when the s

Update COntract
Set DispositionDate = (SELECT Disp_Date,Contract_ID
FROM LinkedServer.Closed_Contract
where Disp_Date is not Null)
This Query gives me an Error as
Only one expression can be specified in the select list when the subquery is
not introduced with EXISTS.
My Select Query does return more then 1 record. is their any way to perform
this Query in a better way as the select Query retrieves more then 200,000
records.
LinkedServer = Linked server from one Server to another
Thanks
samayWhat is the purpose of Contract_ID in the inner SELECT?
Look at the query you're running. You're saying, basically, "... SET
some_date = (SELECT '20041110', 5) ... " ... what is that ,5 for? What is
the SET statement supposed to do with it?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:7FA9D7FF-A197-41FB-A931-780369D33180@.microsoft.com...
> Update COntract
> Set DispositionDate = (SELECT Disp_Date,Contract_ID
> FROM LinkedServer.Closed_Contract
> where Disp_Date is not Null)
> This Query gives me an Error as
> Only one expression can be specified in the select list when the subquery
is
> not introduced with EXISTS.
> My Select Query does return more then 1 record. is their any way to
perform
> this Query in a better way as the select Query retrieves more then 200,000
> records.
> LinkedServer = Linked server from one Server to another
> Thanks
> samay|||Absolutely right My mistake
I have updated my Query and if i run the below Query it Gives ame an Error as
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Update JAX_EOL_COntract
Set dDispositionDate = (SELECT Disp_Date
FROM LinkedServer.Closed_Contract
where Disp_Date is not Null)
please advice
Thanks
"Aaron [SQL Server MVP]" wrote:
> What is the purpose of Contract_ID in the inner SELECT?
> Look at the query you're running. You're saying, basically, "... SET
> some_date = (SELECT '20041110', 5) ... " ... what is that ,5 for? What is
> the SET statement supposed to do with it?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
> wrote in message news:7FA9D7FF-A197-41FB-A931-780369D33180@.microsoft.com...
> >
> > Update COntract
> > Set DispositionDate = (SELECT Disp_Date,Contract_ID
> > FROM LinkedServer.Closed_Contract
> > where Disp_Date is not Null)
> >
> > This Query gives me an Error as
> > Only one expression can be specified in the select list when the subquery
> is
> > not introduced with EXISTS.
> >
> > My Select Query does return more then 1 record. is their any way to
> perform
> > this Query in a better way as the select Query retrieves more then 200,000
> > records.
> >
> > LinkedServer = Linked server from one Server to another
> >
> > Thanks
> > samay
>
>|||Well if there are two dates in Closed_Contract where disp_date is not null,
let's say 20041110 and 20041108, which one do you want? You can use SELECT
MIN() or SELECT MAX() or some other condition which enforces only one row to
be returned.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:92C047DD-0247-4754-B9E3-1F006F30FCC8@.microsoft.com...
> Absolutely right My mistake
> I have updated my Query and if i run the below Query it Gives ame an Error
as
> Subquery returned more than 1 value. This is not permitted when the
subquery
> follows =, !=, <, <= , >, >= or when the subquery is used as an
expression.
> The statement has been terminated.
> Update JAX_EOL_COntract
> Set dDispositionDate = (SELECT Disp_Date
> FROM LinkedServer.Closed_Contract
> where Disp_Date is not Null)
> please advice
> Thanks
> "Aaron [SQL Server MVP]" wrote:
> > What is the purpose of Contract_ID in the inner SELECT?
> >
> > Look at the query you're running. You're saying, basically, "... SET
> > some_date = (SELECT '20041110', 5) ... " ... what is that ,5 for? What
is
> > the SET statement supposed to do with it?
> >
> > --
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
> >
> >
> > "KritiVerma@.hotmail.com"
<KritiVermahotmailcom@.discussions.microsoft.com>
> > wrote in message
news:7FA9D7FF-A197-41FB-A931-780369D33180@.microsoft.com...
> > >
> > > Update COntract
> > > Set DispositionDate = (SELECT Disp_Date,Contract_ID
> > > FROM LinkedServer.Closed_Contract
> > > where Disp_Date is not Null)
> > >
> > > This Query gives me an Error as
> > > Only one expression can be specified in the select list when the
subquery
> > is
> > > not introduced with EXISTS.
> > >
> > > My Select Query does return more then 1 record. is their any way to
> > perform
> > > this Query in a better way as the select Query retrieves more then
200,000
> > > records.
> > >
> > > LinkedServer = Linked server from one Server to another
> > >
> > > Thanks
> > > samay
> >
> >
> >|||Following up on Aaron's comment, possibly you need to add a restriction to
the subquery so it only returns the record for the current contract.
Something like
Update JAX_EOL_COntract JC
Set dDispositionDate = (SELECT Disp_Date
FROM LinkedServer.Closed_Contract CC
where Disp_Date is not Null and JC.ContractId = CC.ContractId)
Mike A.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:echAR10xEHA.2016@.TK2MSFTNGP15.phx.gbl...
> Well if there are two dates in Closed_Contract where disp_date is not
> null,
> let's say 20041110 and 20041108, which one do you want? You can use
> SELECT
> MIN() or SELECT MAX() or some other condition which enforces only one row
> to
> be returned.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
> wrote in message
> news:92C047DD-0247-4754-B9E3-1F006F30FCC8@.microsoft.com...
>> Absolutely right My mistake
>> I have updated my Query and if i run the below Query it Gives ame an
>> Error
> as
>> Subquery returned more than 1 value. This is not permitted when the
> subquery
>> follows =, !=, <, <= , >, >= or when the subquery is used as an
> expression.
>> The statement has been terminated.
>> Update JAX_EOL_COntract
>> Set dDispositionDate = (SELECT Disp_Date
>> FROM LinkedServer.Closed_Contract
>> where Disp_Date is not Null)
>> please advice
>> Thanks
>> "Aaron [SQL Server MVP]" wrote:
>> > What is the purpose of Contract_ID in the inner SELECT?
>> >
>> > Look at the query you're running. You're saying, basically, "... SET
>> > some_date = (SELECT '20041110', 5) ... " ... what is that ,5 for? What
> is
>> > the SET statement supposed to do with it?
>> >
>> > --
>> > http://www.aspfaq.com/
>> > (Reverse address to reply.)
>> >
>> >
>> >
>> >
>> > "KritiVerma@.hotmail.com"
> <KritiVermahotmailcom@.discussions.microsoft.com>
>> > wrote in message
> news:7FA9D7FF-A197-41FB-A931-780369D33180@.microsoft.com...
>> > >
>> > > Update COntract
>> > > Set DispositionDate = (SELECT Disp_Date,Contract_ID
>> > > FROM LinkedServer.Closed_Contract
>> > > where Disp_Date is not Null)
>> > >
>> > > This Query gives me an Error as
>> > > Only one expression can be specified in the select list when the
> subquery
>> > is
>> > > not introduced with EXISTS.
>> > >
>> > > My Select Query does return more then 1 record. is their any way to
>> > perform
>> > > this Query in a better way as the select Query retrieves more then
> 200,000
>> > > records.
>> > >
>> > > LinkedServer = Linked server from one Server to another
>> > >
>> > > Thanks
>> > > samay
>> >
>> >
>> >
>|||> Following up on Aaron's comment, possibly you need to add a restriction to
> the subquery so it only returns the record for the current contract.
> Something like
> Update JAX_EOL_COntract JC
> Set dDispositionDate = (SELECT Disp_Date
> FROM LinkedServer.Closed_Contract CC
> where Disp_Date is not Null and JC.ContractId = CC.ContractId)
Yes, this will work, assuming a one-to-one relationship (which, based on
table names, seems likely).

Only one expression can be specified in the select list when the s

Update COntract
Set DispositionDate = (SELECT Disp_Date,Contract_ID
FROM LinkedServer.Closed_Contract
where Disp_Date is not Null)
This Query gives me an Error as
Only one expression can be specified in the select list when the subquery is
not introduced with EXISTS.
My Select Query does return more then 1 record. is their any way to perform
this Query in a better way as the select Query retrieves more then 200,000
records.
LinkedServer = Linked server from one Server to another
Thanks
samay
What is the purpose of Contract_ID in the inner SELECT?
Look at the query you're running. You're saying, basically, "... SET
some_date = (SELECT '20041110', 5) ... " ... what is that ,5 for? What is
the SET statement supposed to do with it?
http://www.aspfaq.com/
(Reverse address to reply.)
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:7FA9D7FF-A197-41FB-A931-780369D33180@.microsoft.com...
> Update COntract
> Set DispositionDate = (SELECT Disp_Date,Contract_ID
> FROM LinkedServer.Closed_Contract
> where Disp_Date is not Null)
> This Query gives me an Error as
> Only one expression can be specified in the select list when the subquery
is
> not introduced with EXISTS.
> My Select Query does return more then 1 record. is their any way to
perform
> this Query in a better way as the select Query retrieves more then 200,000
> records.
> LinkedServer = Linked server from one Server to another
> Thanks
> samay

Only one expression can be specified in the select list when the s

Update COntract
Set DispositionDate = (SELECT Disp_Date,Contract_ID
FROM LinkedServer.Closed_Contract
where Disp_Date is not Null)
This Query gives me an Error as
Only one expression can be specified in the select list when the subquery is
not introduced with EXISTS.
My Select Query does return more then 1 record. is their any way to perform
this Query in a better way as the select Query retrieves more then 200,000
records.
LinkedServer = Linked server from one Server to another
Thanks
samayWhat is the purpose of Contract_ID in the inner SELECT?
Look at the query you're running. You're saying, basically, "... SET
some_date = (SELECT '20041110', 5) ... " ... what is that ,5 for? What is
the SET statement supposed to do with it?
http://www.aspfaq.com/
(Reverse address to reply.)
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:7FA9D7FF-A197-41FB-A931-780369D33180@.microsoft.com...
> Update COntract
> Set DispositionDate = (SELECT Disp_Date,Contract_ID
> FROM LinkedServer.Closed_Contract
> where Disp_Date is not Null)
> This Query gives me an Error as
> Only one expression can be specified in the select list when the subquery
is
> not introduced with EXISTS.
> My Select Query does return more then 1 record. is their any way to
perform
> this Query in a better way as the select Query retrieves more then 200,000
> records.
> LinkedServer = Linked server from one Server to another
> Thanks
> samay

Only members of sysadmin role are allowed to update or delete jobs owned by a different login

Question to those who may have had this same error- it seems that I am not able to delete some of the reports that I have created. This just started happening recently and according to our system admin nothing has changed as far as permissions are concernced. We installed SP2 the other day and I was wondering if this could have anything to do with the error message below

by the way I am a member of the sysadmin group

thanks in advance

km

System.Web.Services.Protocols.SoapException: Server was unable to process request. > System.Data.SqlClient.SqlException: Only members of sysadmin role are allowed to update or delete jobs owned by a different login. Only members of sysadmin role are allowed to update or delete jobs owned by a different login. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.DBInterface.DeleteObject(String objectName) at Microsoft.ReportingServices.Library.RSService._DeleteItem(String item) at Microsoft.ReportingServices.Library.RSService.ExecuteBatch(Guid batchId) at Microsoft.ReportingServices.WebServer.ReportingService2005.ExecuteBatch() End of inner exception stack trace

my bad

I need to move this over to SSRS

km

|||

Moved to RS as requested.

The error though is more general than RS, in fact it is a standard SQL Server Agent message. Unless there is a bug, I'd say you really are not a member of sysadmins, or at least not in the context of the RS WS call that is doing the work on yoru behalf. That may be the issue, the security context used by the WS itself. Perhaps run a profiler trace and see what login is being used, and also what job it is trying to delete. You can then verify the job owner. There should not be a problem as you are managing a RS created job, so RS should be able to delete its own jobs... maybe it is a RS thing?

|||Thanks
your info did the trick-- the account used was not a member of sysadmin-- my bad
thanks again
kam
|||

hi kam,

how did you solved the issue? i've added myself as the sysadmin on the server, but i'm still getting the same error. is there any other sysadmin than the sqladmin? thanks.

DK

|||actually, i've found the answer. whatever account used on the app pool need to have sysadmin, as this is the account that is updating/deleting the report. thanks.

Only members of sysadmin role are allowed to update or delete jobs owned by a different login

Question to those who may have had this same error- it seems that I am not able to delete some of the reports that I have created. This just started happening recently and according to our system admin nothing has changed as far as permissions are concernced. We installed SP2 the other day and I was wondering if this could have anything to do with the error message below

by the way I am a member of the sysadmin group

thanks in advance

km

System.Web.Services.Protocols.SoapException: Server was unable to process request. > System.Data.SqlClient.SqlException: Only members of sysadmin role are allowed to update or delete jobs owned by a different login. Only members of sysadmin role are allowed to update or delete jobs owned by a different login. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.DBInterface.DeleteObject(String objectName) at Microsoft.ReportingServices.Library.RSService._DeleteItem(String item) at Microsoft.ReportingServices.Library.RSService.ExecuteBatch(Guid batchId) at Microsoft.ReportingServices.WebServer.ReportingService2005.ExecuteBatch() End of inner exception stack trace

my bad

I need to move this over to SSRS

km

|||

Moved to RS as requested.

The error though is more general than RS, in fact it is a standard SQL Server Agent message. Unless there is a bug, I'd say you really are not a member of sysadmins, or at least not in the context of the RS WS call that is doing the work on yoru behalf. That may be the issue, the security context used by the WS itself. Perhaps run a profiler trace and see what login is being used, and also what job it is trying to delete. You can then verify the job owner. There should not be a problem as you are managing a RS created job, so RS should be able to delete its own jobs... maybe it is a RS thing?

|||Thanks
your info did the trick-- the account used was not a member of sysadmin-- my bad
thanks again
kam|||

hi kam,

how did you solved the issue? i've added myself as the sysadmin on the server, but i'm still getting the same error. is there any other sysadmin than the sqladmin? thanks.

DK

|||actually, i've found the answer. whatever account used on the app pool need to have sysadmin, as this is the account that is updating/deleting the report. thanks.

online index rebuild Problem

Hi

Please advice whether i have to do update stats after i do online index rebuild

I use Alter index with (options as below)

ONLINE = ON

SORT_IN_TEMPDB = ON

STATISTICS_NORECOMPUTE =OFF

Should i be doing update stats after this online index rebuild?

After doing online index rebuild when clients connecting to DB the Response time has increased ..

During Online INDEX REBUILD page file usage rises from 3 Gb to 10 GB and stays at 10 Gb even after online index rebuild

i have no idea on this ..

pls Advice ...I Need help..........................

Thanks in advance

By rebuilding the index, the default behavior of SQL Server 2005 is to autoupdate statistics

So no extra work is needed by you

|||

Pagefile is increased, then check the memory settings on SQL Server.

Also if there are tables with frequent inserts and updates then its better to schedule an intermittent UPDATE stats for optimum performance.

online index rebuild and update statistics

Hi ..

Please advice whether i have to do update statistics along with Index rebuild (online /off line)

Thanks in advance

This depends on the traffic on your DB. IF its an OLTP/heavily used DB you might keep this for off peak use. Your Index scripts could be scripted to either update or do not update stats. You dont *have to* update stats. Its optional. But generally its good to keep updated stats. Again, depending on your Db size and usage, you can turn Auto Statistics ON or OFF.|||

Manual statistics update is preferable in off-peak hours

|||To reword his question a little bit, if you perform Alter Index .... Rebuild, do you need to also perform an Update Statistics with full scan afterwards? The Alter Index ... Rebuild updates stats based on sampling rates. Is there any real advantage to updating the stats with a full scan afterwards?

online index rebuild and update statistics

Hi ..

Please advice whether i have to do update statistics along with Index rebuild (online /off line)

Thanks in advance

This depends on the traffic on your DB. IF its an OLTP/heavily used DB you might keep this for off peak use. Your Index scripts could be scripted to either update or do not update stats. You dont *have to* update stats. Its optional. But generally its good to keep updated stats. Again, depending on your Db size and usage, you can turn Auto Statistics ON or OFF.|||

Manual statistics update is preferable in off-peak hours

|||To reword his question a little bit, if you perform Alter Index .... Rebuild, do you need to also perform an Update Statistics with full scan afterwards? The Alter Index ... Rebuild updates stats based on sampling rates. Is there any real advantage to updating the stats with a full scan afterwards?