Change connected user to avoid owner prefix in queries

Hi everybody,

I've five instances of SQL Server 2000 with the SAME database with a
DIFFERENT owner in each server. I, as the administrator, have a lot of
queries that I have to execute in some or all servers. The problem is
that I have to connect to all servers with MY user, not each of the db
owners...

So I have queries this way:

select * from mike.table1 t1 join mike.table2 t2 on...

And when I connect to another server I have to change mike for jeremy
in all the SQLs...

And when I connect to another server I have to change jeremy for nina
in all the SQLs...

I know that there was an old, v7, deprecated way to change the
"schema", something like

change current user to kimberly
go
select * from table1 t1 join table2 t2 on...

This way, I'll change ONLY once the connected user. I could even do at
the beginning of the script an IF, to change the connected user
depending on @.@.SERVERNAME !!!

Can someone remember this instruction?

Thanks in advance for your help !!!Found it !!!

setuser 'q01'

-- quien importo una orden de transporte
select USERNAME, SYDATE, SYTIME, CLIENT
from TPLOG
where CMDSTRING like '%D02K909789%

Quote:

Originally Posted by

>From BOL:

SETUSER
Allows a member of the sysadmin fixed server role or db_owner fixed
database role to impersonate another user.

Important SETUSER is included in Microsoft? SQL Server? 2000 only for
backward compatibility, and its usage is not recommended. SETUSER may
not be supported in a future release of SQL Server.

Syntax
SETUSER [ 'username' [ WITH NORESET ] ]

Arguments
'username'

Is the name of a SQL Server or Microsoft Windows NT? user in the
current database that is impersonated. When username is not specified,
the original identity of the system administrator or database owner
impersonating the user is reestablished.

WITH NORESET

Specifies that subsequent SETUSER statements (with no specified
username) do not reset to the system administrator or database owner.

challenging sql queries

here is a structure of tables in a database

CUSTOMERS (CustID*, CompanyName, ContactName, ContactTitle, Address,
City, Region, PostalCode, Country, Phone, Fax)

EMPLOYEES (EmployeeID*, Lastname, Firstname, Title, TitleofCourtesy,
Birthdate, Hiredate, Address, City, Region, Postalcode, Country,
Homephone, Extension, Reportsto)

ORDERS (OrderID*, CustID, EmployeeID, OrderDate, RequiredDate,
ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity,
Shipregion, ShipPostalCode, ShipCountry)

ORDER_DETAILS (OrderID*, ProductID*, UnitPrice, Quantity, Discount)

PRODUCTS (ProductID*, ProductName, SupplierID, PL_ID, QuantityPerUnit,
UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued)

PRODUCT_LINES (PL_Id*, PL_Name, Description)

SHIPPERS (ShipperID*, CompanyName, Phone)

SUPPLIERS (Supplierid*, Companyname, Contactname, Contacttitle,
Address, City, Region, Postalcode, Country,Phone,Fax)

* - Primary keys

queries (these are problem questions given in a competition!!!)

1.) In which month of 1997 was the most number of orders placed? List
the month, number of orders placed during that month , number of
customers who placed orders in that month , gross (i.e. disregard
discount) total value of orders placed .

2.)
Determine the products for which there has been at least one
order during each month of 1997. Display for these products: the
Product ID, Product name, Product Line Name, number of orders placed
during 1997 , number of customers who have ordered the product in 1997
, and gross (i.e. disregarding discount) total value of orders placed
for the product in 1997 .

3.) Find the customers who have placed orders during 1996 & 1997 for
products in every product line AND who have had their orders handled
by either all employees or all but one employee. Display the customer
ID, company name, address, city, country and number of orders placed

4.) For each product line, find the top three customers (based on
maximum total purchase value; ignore discounts). Display the Product
Line name, the Customer ID & Company name, and the total purchase
value for that customer in the product line . Sort the output by
Product Line and within that, the line revenue figure (highest first).
i think rank() shud b used

any clues/ideas ?On 6 Mar 2004 02:15:00 -0800, usenetdada@.yahoo.com (dada) wrote:

>any clues/ideas ?

Go to class more often?

--
Mike Sherrill
Information Management Systems|||"dada" <usenetdada@.yahoo.com> wrote in message
news:f7851403.0403060215.7c7b3094@.posting.google.c om...
> here is a structure of tables in a database

This is not a structure of any sort of daatabase. There's nothing here
about datatypes.

Is CustID a character field? A numeric, or something else?

There's no sample data either.

Do your homework and then come back with proper DDL and example data and
then perhaps folks can help.

> CUSTOMERS (CustID*, CompanyName, ContactName, ContactTitle, Address,
> City, Region, PostalCode, Country, Phone, Fax)
> EMPLOYEES (EmployeeID*, Lastname, Firstname, Title, TitleofCourtesy,
> Birthdate, Hiredate, Address, City, Region, Postalcode, Country,
> Homephone, Extension, Reportsto)
> ORDERS (OrderID*, CustID, EmployeeID, OrderDate, RequiredDate,
> ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity,
> Shipregion, ShipPostalCode, ShipCountry)
> ORDER_DETAILS (OrderID*, ProductID*, UnitPrice, Quantity, Discount)
> PRODUCTS (ProductID*, ProductName, SupplierID, PL_ID, QuantityPerUnit,
> UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued)
> PRODUCT_LINES (PL_Id*, PL_Name, Description)
> SHIPPERS (ShipperID*, CompanyName, Phone)
> SUPPLIERS (Supplierid*, Companyname, Contactname, Contacttitle,
> Address, City, Region, Postalcode, Country,Phone,Fax)
> * - Primary keys
> queries (these are problem questions given in a competition!!!)
> 1.) In which month of 1997 was the most number of orders placed? List
> the month, number of orders placed during that month , number of
> customers who placed orders in that month , gross (i.e. disregard
> discount) total value of orders placed .
> 2.)
> Determine the products for which there has been at least one
> order during each month of 1997. Display for these products: the
> Product ID, Product name, Product Line Name, number of orders placed
> during 1997 , number of customers who have ordered the product in 1997
> , and gross (i.e. disregarding discount) total value of orders placed
> for the product in 1997 .
> 3.) Find the customers who have placed orders during 1996 & 1997 for
> products in every product line AND who have had their orders handled
> by either all employees or all but one employee. Display the customer
> ID, company name, address, city, country and number of orders placed
> 4.) For each product line, find the top three customers (based on
> maximum total purchase value; ignore discounts). Display the Product
> Line name, the Customer ID & Company name, and the total purchase
> value for that customer in the product line . Sort the output by
> Product Line and within that, the line revenue figure (highest first).
> i think rank() shud b used
> any clues/ideas ?|||At any of the universities I have attended or taught at, attempting to
present the work of other people as your own work is grounds for
expulsion or termination.

A few years back, I filed a compliant against a student at a University
in New Zealand for this. I do not know what action was against him.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Greg D. Moore (Strider) (mooregr_deleteth1s@.greenms.com) writes:
> This is not a structure of any sort of daatabase. There's nothing here
> about datatypes.
> Is CustID a character field? A numeric, or something else?
> There's no sample data either.

Of course there is! Don't you recognize Northwind when you see it?

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns94A57A1CE8C0Yazorman@.127.0.0.1...
> Greg D. Moore (Strider) (mooregr_deleteth1s@.greenms.com) writes:
> > This is not a structure of any sort of daatabase. There's nothing here
> > about datatypes.
> > Is CustID a character field? A numeric, or something else?
> > There's no sample data either.
> Of course there is! Don't you recognize Northwind when you see it?

Touche. I'll go slink back into my corner now. :-)

> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Joe,

the world is full of people trying to make something to survive, why
somes few others giving orders to, says "this is what I have done..."

Don't you recognize G. Bush and american soldiers ?
French humour, you can say !

A +

Joe Celko a crit:
> At any of the universities I have attended or taught at, attempting to
> present the work of other people as your own work is grounds for
> expulsion or termination.
> A few years back, I filed a compliant against a student at a University
> in New Zealand for this. I do not know what action was against him.
> --CELKO--
> ===========================
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, datatypes, etc. in your
> schema are.
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

--
Frdric BROUARD, MVP Microsoft SQL Server. Langage SQL / Delphi / web
Livre SQL - col. Rfrence : http://sqlpro.developpez.com/bookSQL.html
Le site du SQL, pour dbutants et pros : http://sqlpro.developpez.com
****************** mailto:brouardf@.club-internet.fr ******************

Challenge revisisted

Hi,
I'm trying to port som old access code to asp.net and the access solution ha
s a couple of queries to sql server that I'm not sure work as expected. I t
hink the idea of the query is to identify if the same event (slaugher of rei
ndeer) is registered in two
different places (field 5 in my last challenge), but two duplicate rows diff
ering only by place does not get selected by the query.
The query is as follows:
SELECT ImportID FROM Temp t WHERE ( EXISTS (
SELECT Year, Code1, Code2, Date FROM (
SELECT DISTINCT Year, Code1, Code2, Date , Place FROM Temp ) SUBQUERY
WHERE ( t.Year = SUBQUERY.Year )
AND ( t.Code1 = SUBQUERY.Code1 )
AND ( t.Code2 = SUBQUERY.Code2 )
AND ( t.Date = SUBQUERY.Date)
GROUP BY Year, Code1, Code2, Date
HAVING (COUNT(*) > 1) ))
Can anyone see if this query does anything useful?, or perhaps give me a que
ry that selects rows with same Year,Code1,Code2,Date, but with different Pla
ce.
The other query is essentially the same, using Zone instead of Place
Morten WennevikOn Mon, 14 Nov 2005 08:51:43 +0100, Morten Wennevik wrote:

>Hi,
>I'm trying to port som old access code to asp.net and the access solution has a cou
ple of queries to sql server that I'm not sure work as expected. I think the idea o
f the query is to identify if the same event (slaugher of reindeer) is registered in
tw
o different places (field 5 in my last challenge), but two duplicate rows differing only by
place does not get selected by the query.
>The query is as follows:
>SELECT ImportID FROM Temp t WHERE ( EXISTS (
> SELECT Year, Code1, Code2, Date FROM (
> SELECT DISTINCT Year, Code1, Code2, Date , Place FROM Temp ) SUBQUERY
> WHERE ( t.Year = SUBQUERY.Year )
> AND ( t.Code1 = SUBQUERY.Code1 )
> AND ( t.Code2 = SUBQUERY.Code2 )
> AND ( t.Date = SUBQUERY.Date)
> GROUP BY Year, Code1, Code2, Date
> HAVING (COUNT(*) > 1) ))
>Can anyone see if this query does anything useful?, or perhaps give me a qu
ery that selects rows with same Year,Code1,Code2,Date, but with different Pl
ace.
>The other query is essentially the same, using Zone instead of Place
>Morten Wennevik
Hi Morten,
I'm not sure what you want eactly. If you want to find year / code1 /
code2 / date combinations for which more than one row exist, use:
SELECT Year, Code1, Code2, Date, COUNT(*)
FROM Temp
GROUP BY Year, Code1, Code2, Date
HAVING COUNT(*) > 1
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Centralizing Two Stored Procedure Queries (one using Cursor Ou

Alejandro,
Sorry, one more question: In your example, would there be any way to return
only a subset of the fields in the stored proc query. For example, if you
wanted a temporary table with only the orderid field. Like this:
create table #t (orderid int)
insert into #t(orderid)
exec dbo.p1 @.sd, @.ed
I'll need it like this because my store proc "spTrucker" has many, many
fields, and I only need about 4 or 5 of them in the cursor.
Thanks again,
John
"Alejandro Mesa" wrote:
> John,
> You do not need a cursor output parameter. You can grab the result of the
sp
> "spTrucker" in sp "spDistinctCodes". See "insert into ... exec ..." in BOL
.
> Example:
> use northwind
> go
> create procedure dbo.p1
> @.sd datetime,
> @.ed datetime
> as
> set nocount on
> select
> orderid, orderdate, customerid
> from
> dbo.orders
> where
> orderdate >= convert(char(8), @.sd, 112)
> and orderdate < convert(char(8), dateadd(day, 1, @.ed), 112)
> return @.@.error
> go
> create procedure dbo.p2
> @.sd datetime,
> @.ed datetime
> as
> set nocount on
> declare @.orderid int
> declare @.orderdate varchar(25)
> declare @.customerid nchar(5)
> create table #t (orderid int, orderdate datetime, customerid nchar(5))
> insert into #t(orderid, orderdate, customerid)
> exec dbo.p1 @.sd, @.ed
> declare my_cursor cursor local fast_forward
> for
> select orderid, orderdate, customerid
> from #t
> order by orderdate
> open my_cursor
> while 1 = 1
> begin
> fetch next from my_cursor into @.orderid, @.orderdate, @.customerid
> if @.@.error != 0 or @.@.fetch_status != 0 break
> raiserror('%d %s %s', 10, 1, @.orderid, @.orderdate, @.customerid) with nowa
it
> end
> close my_cursor
> deallocate my_cursor
> go
> exec dbo.p2 '19970701', '19970731'
> go
> drop procedure p2, p1
> go
> How to share data between stored procedures
> http://www.sommarskog.se/share_data.html
>
> AMB
>
> "John Walker" wrote:
>John,
The ddl for the temporary table need to match all columns returned by the
sp. You can not grab just a subset.
AMB
"John Walker" wrote:
> Alejandro,
> Sorry, one more question: In your example, would there be any way to retu
rn
> only a subset of the fields in the stored proc query. For example, if you
> wanted a temporary table with only the orderid field. Like this:
> create table #t (orderid int)
> insert into #t(orderid)
> exec dbo.p1 @.sd, @.ed
> I'll need it like this because my store proc "spTrucker" has many, many
> fields, and I only need about 4 or 5 of them in the cursor.
> Thanks again,
> John
> "Alejandro Mesa" wrote:
>

cdata all columns automatically

I have an application that is extremely dynamic.
Users can type in their own sql statements and run queries however they
want.
Is there a way using SQL to automaticall wrap all the columns with a
CDATA notation?
EX:
Query
SELECT field1, field2, field3 FROM myTable FOR XML ...
Desired result
<row>
<field1><![CDATA[field1 data]]></field1>
<field2><![CDATA[field2 data]]></field2>
<field3><![CDATA[field3 data]]></field3>
</row>
**PLEASE NOTE** I have no control of the SELECT statement, the users
can select whatever they want. So i cannot use the regular EXPLICIT
col!cdata notation :(And why would you want to wrap the data into a CDATA section? CDATA sections
are there for lazy hand authors of XML documents that do not want to write
< or & everytime they need to write < or &. On the output-side CDATA
should not make any difference whatsoever since FOR XML automatically does
the needed entitization.
Best regards
Michael
"jamesd" <jamesd@.ring4freedom.com> wrote in message
news:1164860203.165600.219350@.j44g2000cwa.googlegroups.com...
>I have an application that is extremely dynamic.
> Users can type in their own sql statements and run queries however they
> want.
> Is there a way using SQL to automaticall wrap all the columns with a
> CDATA notation?
> EX:
> Query
> SELECT field1, field2, field3 FROM myTable FOR XML ...
> Desired result
> <row>
> <field1><![CDATA[field1 data]]></field1>
> <field2><![CDATA[field2 data]]></field2>
> <field3><![CDATA[field3 data]]></field3>
> </row>
> **PLEASE NOTE** I have no control of the SELECT statement, the users
> can select whatever they want. So i cannot use the regular EXPLICIT
> col!cdata notation :(
>

cdata all columns automatically

> I have an application that is extremely dynamic.
> Users can type in their own sql statements and run queries however they
> want.
> Is there a way using SQL to automaticall wrap all the columns with a
> CDATA notation?
> EX:
> Query
> SELECT field1, field2, field3 FROM myTable FOR XML ...
> Desired result
> <row>
> <field1><![CDATA[field1 data]]></field1>
> <field2><![CDATA[field2 data]]></field2>
> <field3><![CDATA[field3 data]]></field3>
> </row>
> **PLEASE NOTE** I have no control of the SELECT statement, the users
> can select whatever they want. So i cannot use the regular EXPLICIT
> col!cdata notation :(
Did anyone have a solution for this? I need the cdata tags for doublebyte v
alues.
BizTalk Utilities - Frustration free BizTalk Adapters
http://www.topxml.com/biztalkutilitiesFOR XML EXPLICIT without assigning it to an XML datatype can provide you
with CDATA sections. Note however, that there is really no reason to
generate CDATA sections since FOR XML is entitizing the characters for you
that you would otherwise have to wrap into a CDATA section.
There is no semantic difference between <a><![CDATA[a]]></a> and <a>a</a>.
Best regards
Michael
"kira" <HappyGirl101@.gmail.com> wrote in message
news:31cd9f77-c21c-439f-b6f2-169e2776756d@.text.giganews.com...
>
> Did anyone have a solution for this? I need the cdata tags for doublebyte
> values.
> BizTalk Utilities - Frustration free BizTalk Adapters
> http://www.topxml.com/biztalkutilities

cdata all columns automatically

I have an application that is extremely dynamic.
Users can type in their own sql statements and run queries however they
want.
Is there a way using SQL to automaticall wrap all the columns with a
CDATA notation?
EX:
Query
SELECT field1, field2, field3 FROM myTable FOR XML ...
Desired result
<row>
<field1><![CDATA[field1 data]]></field1>
<field2><![CDATA[field2 data]]></field2>
<field3><![CDATA[field3 data]]></field3>
</row>
**PLEASE NOTE** I have no control of the SELECT statement, the users
can select whatever they want. So i cannot use the regular EXPLICIT
col!cdata notation
And why would you want to wrap the data into a CDATA section? CDATA sections
are there for lazy hand authors of XML documents that do not want to write
< or & everytime they need to write < or &. On the output-side CDATA
should not make any difference whatsoever since FOR XML automatically does
the needed entitization.
Best regards
Michael
"jamesd" <jamesd@.ring4freedom.com> wrote in message
news:1164860203.165600.219350@.j44g2000cwa.googlegr oups.com...
>I have an application that is extremely dynamic.
> Users can type in their own sql statements and run queries however they
> want.
> Is there a way using SQL to automaticall wrap all the columns with a
> CDATA notation?
> EX:
> Query
> SELECT field1, field2, field3 FROM myTable FOR XML ...
> Desired result
> <row>
> <field1><![CDATA[field1 data]]></field1>
> <field2><![CDATA[field2 data]]></field2>
> <field3><![CDATA[field3 data]]></field3>
> </row>
> **PLEASE NOTE** I have no control of the SELECT statement, the users
> can select whatever they want. So i cannot use the regular EXPLICIT
> col!cdata notation
>
|||
> I have an application that is extremely dynamic.
> Users can type in their own sql statements and run queries however they
> want.
> Is there a way using SQL to automaticall wrap all the columns with a
> CDATA notation?
> EX:
> Query
> SELECT field1, field2, field3 FROM myTable FOR XML ...
> Desired result
> <row>
> <field1><![CDATA[field1 data]]></field1>
> <field2><![CDATA[field2 data]]></field2>
> <field3><![CDATA[field3 data]]></field3>
> </row>
> **PLEASE NOTE** I have no control of the SELECT statement, the users
> can select whatever they want. So i cannot use the regular EXPLICIT
> col!cdata notation
Did anyone have a solution for this? I need the cdata tags for doublebyte values.
BizTalk Utilities - Frustration free BizTalk Adapters
http://www.topxml.com/biztalkutilities
|||FOR XML EXPLICIT without assigning it to an XML datatype can provide you
with CDATA sections. Note however, that there is really no reason to
generate CDATA sections since FOR XML is entitizing the characters for you
that you would otherwise have to wrap into a CDATA section.
There is no semantic difference between <a><![CDATA[a]]></a> and <a>a</a>.
Best regards
Michael
"kira" <HappyGirl101@.gmail.com> wrote in message
news:31cd9f77-c21c-439f-b6f2-169e2776756d@.text.giganews.com...
>
> Did anyone have a solution for this? I need the cdata tags for doublebyte
> values.
> BizTalk Utilities - Frustration free BizTalk Adapters
> http://www.topxml.com/biztalkutilities

Catch and analyse incoming queries

Can I programatically (from f.e. c# program) catch incoming queries to
an SQL server?
I want to watch and analyse them, if such queries are
create/alter/drop I want to log this information in a database to view
changes history of database structure.
You can use profiler for these type of activities. I recollect reading on
Dejan blog to build a trigger on profiler based events. Can take a read at
: http://solidqualitylearning.com/blog...11/25/214.aspx
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"Sergi" <adamchuk@.gmail.com> wrote in message
news:6f504b3b.0502250002.103d18a5@.posting.google.c om...
> Can I programatically (from f.e. c# program) catch incoming queries to
> an SQL server?
> I want to watch and analyse them, if such queries are
> create/alter/drop I want to log this information in a database to view
> changes history of database structure.