Changing 100 table object owner names at once

Is there any way I can change the object owner for over
100 tables in a single data base at once in SQL 7 without
using the sp on each table?
Well you can do an update on sysobjects but if you look at the sp it does a lot of checking and clearing up.
Better to code a loop to execute it on all the tables necessary.
"Smith" wrote:

> Is there any way I can change the object owner for over
> 100 tables in a single data base at once in SQL 7 without
> using the sp on each table?
>
sql

change user mode

Hi group,
I have a db that I changed from multiple user to single user, and want to
change it back now. However, it keeps giving me the message below. I have
checked with EM and sysprocesses that there is no connection to it. Any
suggestion what I can do?
Thanks.
Quentin
message:
Server: Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database 'mydb' cannot be made at this
time. The database is in single-user mode, and a user is currently connected
to it.
Server: Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Try running the following script from Query Analyzer:
ALTER DATABASE mydb
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE mydb
SET MULTI_USER
Hope this helps.
Dan Guzman
SQL Server MVP
"Quentin Ran" <ab@.who.com> wrote in message
news:uiK0UuSPEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Hi group,
> I have a db that I changed from multiple user to single user, and want to
> change it back now. However, it keeps giving me the message below. I
have
> checked with EM and sysprocesses that there is no connection to it. Any
> suggestion what I can do?
> Thanks.
> Quentin
>
> message:
> Server: Msg 5064, Level 16, State 1, Line 1
> Changes to the state or options of database 'mydb' cannot be made at this
> time. The database is in single-user mode, and a user is currently
connected
> to it.
> Server: Msg 5069, Level 16, State 1, Line 1
> ALTER DATABASE statement failed.
>
|||Thanks Dan. The error message war the result of the T-sql statement.
Quentin
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:e0yhhFTPEHA.632@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Try running the following script from Query Analyzer:
> ALTER DATABASE mydb
> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> ALTER DATABASE mydb
> SET MULTI_USER
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Quentin Ran" <ab@.who.com> wrote in message
> news:uiK0UuSPEHA.1340@.TK2MSFTNGP12.phx.gbl...
to[vbcol=seagreen]
> have
this
> connected
>

change user mode

Hi group,
I have a db that I changed from multiple user to single user, and want to
change it back now. However, it keeps giving me the message below. I have
checked with EM and sysprocesses that there is no connection to it. Any
suggestion what I can do?
Thanks.
Quentin
message:
Server: Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database 'mydb' cannot be made at this
time. The database is in single-user mode, and a user is currently connected
to it.
Server: Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.Try running the following script from Query Analyzer:
ALTER DATABASE mydb
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE mydb
SET MULTI_USER
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Quentin Ran" <ab@.who.com> wrote in message
news:uiK0UuSPEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Hi group,
> I have a db that I changed from multiple user to single user, and want to
> change it back now. However, it keeps giving me the message below. I
have
> checked with EM and sysprocesses that there is no connection to it. Any
> suggestion what I can do?
> Thanks.
> Quentin
>
> message:
> Server: Msg 5064, Level 16, State 1, Line 1
> Changes to the state or options of database 'mydb' cannot be made at this
> time. The database is in single-user mode, and a user is currently
connected
> to it.
> Server: Msg 5069, Level 16, State 1, Line 1
> ALTER DATABASE statement failed.
>|||Thanks Dan. The error message war the result of the T-sql statement.
Quentin
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:e0yhhFTPEHA.632@.TK2MSFTNGP12.phx.gbl...
> Try running the following script from Query Analyzer:
> ALTER DATABASE mydb
> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> ALTER DATABASE mydb
> SET MULTI_USER
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Quentin Ran" <ab@.who.com> wrote in message
> news:uiK0UuSPEHA.1340@.TK2MSFTNGP12.phx.gbl...
> > Hi group,
> >
> > I have a db that I changed from multiple user to single user, and want
to
> > change it back now. However, it keeps giving me the message below. I
> have
> > checked with EM and sysprocesses that there is no connection to it. Any
> > suggestion what I can do?
> >
> > Thanks.
> >
> > Quentin
> >
> >
> > message:
> >
> > Server: Msg 5064, Level 16, State 1, Line 1
> > Changes to the state or options of database 'mydb' cannot be made at
this
> > time. The database is in single-user mode, and a user is currently
> connected
> > to it.
> > Server: Msg 5069, Level 16, State 1, Line 1
> > ALTER DATABASE statement failed.
> >
> >
>

change user mode

Hi group,
I have a db that I changed from multiple user to single user, and want to
change it back now. However, it keeps giving me the message below. I have
checked with EM and sysprocesses that there is no connection to it. Any
suggestion what I can do?
Thanks.
Quentin
message:
Server: Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database 'mydb' cannot be made at this
time. The database is in single-user mode, and a user is currently connected
to it.
Server: Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.Try running the following script from Query Analyzer:
ALTER DATABASE mydb
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE mydb
SET MULTI_USER
Hope this helps.
Dan Guzman
SQL Server MVP
"Quentin Ran" <ab@.who.com> wrote in message
news:uiK0UuSPEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Hi group,
> I have a db that I changed from multiple user to single user, and want to
> change it back now. However, it keeps giving me the message below. I
have
> checked with EM and sysprocesses that there is no connection to it. Any
> suggestion what I can do?
> Thanks.
> Quentin
>
> message:
> Server: Msg 5064, Level 16, State 1, Line 1
> Changes to the state or options of database 'mydb' cannot be made at this
> time. The database is in single-user mode, and a user is currently
connected
> to it.
> Server: Msg 5069, Level 16, State 1, Line 1
> ALTER DATABASE statement failed.
>|||Thanks Dan. The error message war the result of the T-sql statement.
Quentin
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:e0yhhFTPEHA.632@.TK2MSFTNGP12.phx.gbl...
> Try running the following script from Query Analyzer:
> ALTER DATABASE mydb
> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> ALTER DATABASE mydb
> SET MULTI_USER
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Quentin Ran" <ab@.who.com> wrote in message
> news:uiK0UuSPEHA.1340@.TK2MSFTNGP12.phx.gbl...
to[vbcol=seagreen]
> have
this[vbcol=seagreen]
> connected
>

change null field of rollup summary

hi all

i m using sql server 2005. i m using rollup query to get total sum and sum with group within single query. it shows me total amount but with null field . i want to change this null field with "total " caption.

pls suggest how this thing is possible.

thanks and regards

Ganesh

Moved to t-sql forum...|||

That is the way a Rollup query works, and, unfortunately, as far as I know, there is no way to get around it.

Of course, your client side application could easily do that. (After all, presentation is the point of client applications, data storage and retrieval is the point of SQL Server.)

|||

Use the GROUPING function in the SELECT list like:

CASE GROUPING(col1) WHEN 1 THEN 'Total' ELSE Col1 END

See Books Online for more details. Or post your sample query here to get the corrected one.

|||Good Idea Umachardar, I consider it, but discarded the idea since it didn't quite seem to fit the OP's request. But perhaps it will.

change nchar to char

Can I change a double byte (nchar) to 2 single byte (char)?Win
You can lose date
CREATE TABLE #T
(
col NCHAR(2) NOT NULL
)
INSERT INTO #T VALUES (N'K?')
GO
SELECT * FROM #T
--K?
GO
ALTER TABLE #T ALTER COLUMN col CHAR(2)
GO
SELECT * FROM #T
--K?
"Win" <aaa@.aaa.com> wrote in message
news:uZPIZ8CYFHA.3320@.TK2MSFTNGP12.phx.gbl...
> Can I change a double byte (nchar) to 2 single byte (char)?
>
>
>|||Hi
nchar will explicitly convert to char see
http://msdn.microsoft.com/library/d...br />
2f3o.asp
If you want to separate each byte of an nchar into separate characters does
not make sense!
If you want to change the datatype for a column you can use alter table
statement, but (as in the recent conversation http://tinyurl.com/9bz6c) you
have to be careful with ANSI_PADDING and you may be better to create a
separate column, update it, and the drop the original column.
This may help!
SET ANSI_PADDING OFF
CREATE TABLE MyTable ( ncharcolumn nchar(10) NULL )
INSERT INTO MyTable ( ncharcolumn )
SELECT 'ABC '
UNION ALL SELECT 'DEF '
UNION ALL SELECT 'GHI'
SELECT ncharcolumn, DATALENGTH(ncharcolumn)
FROM MyTable
/*
ncharcolumn
-- --
ABC 20
DEF 20
GHI 20
*/
ALTER TABLE MyTable ADD charcolumn char(10) NULL
UPDATE MyTable
SET charcolumn = ncharcolumn
SELECT ncharcolumn, DATALENGTH(ncharcolumn), charcolumn,
DATALENGTH(charcolumn)
FROM MyTable
/*
ncharcolumn charcolumn
-- -- -- --
ABC 20 ABC 3
DEF 20 DEF 3
GHI 20 GHI 3
*/
ALTER TABLE MyTable ALTER COLUMN ncharcolumn char(10) NULL
SELECT ncharcolumn, DATALENGTH(ncharcolumn), charcolumn,
DATALENGTH(charcolumn)
FROM MyTable
/*
ncharcolumn charcolumn
-- -- -- --
ABC 10 ABC 3
DEF 10 DEF 3
GHI 10 GHI 3
*/
SET ANSI_PADDING ON
DROP TABLE MyTable
CREATE TABLE MyTable ( ncharcolumn nchar(10) NULL )
INSERT INTO MyTable ( ncharcolumn )
SELECT 'ABC '
UNION ALL SELECT 'DEF '
UNION ALL SELECT 'GHI'
SELECT ncharcolumn, DATALENGTH(ncharcolumn)
FROM MyTable
/*
ncharcolumn
-- --
ABC 20
DEF 20
GHI 20
*/
ALTER TABLE MyTable ADD charcolumn char(10) NULL
UPDATE MyTable
SET charcolumn = ncharcolumn
SELECT ncharcolumn, DATALENGTH(ncharcolumn), charcolumn,
DATALENGTH(charcolumn)
FROM MyTable
/*
ncharcolumn charcolumn
-- -- -- --
ABC 20 ABC 10
DEF 20 DEF 10
GHI 20 GHI 10
*/
ALTER TABLE MyTable ALTER COLUMN ncharcolumn char(10) NULL
SELECT ncharcolumn, DATALENGTH(ncharcolumn), charcolumn,
DATALENGTH(charcolumn)
FROM MyTable
/*
ncharcolumn charcolumn
-- -- -- --
ABC 10 ABC 10
DEF 10 DEF 10
GHI 10 GHI 10
*/
John
"Win" wrote:

> Can I change a double byte (nchar) to 2 single byte (char)?
>
>
>
>|||Can I convert 1 double byte character to 2 signle byte characters.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ecvfkHDYFHA.2740@.TK2MSFTNGP14.phx.gbl...
> Win
> You can lose date
> CREATE TABLE #T
> (
> col NCHAR(2) NOT NULL
> )
> INSERT INTO #T VALUES (N'K?')
> GO
> SELECT * FROM #T
> --K?
> GO
> ALTER TABLE #T ALTER COLUMN col CHAR(2)
> GO
> SELECT * FROM #T
> --K?
>
>
> "Win" <aaa@.aaa.com> wrote in message
> news:uZPIZ8CYFHA.3320@.TK2MSFTNGP12.phx.gbl...
>|||Hi
Yes, but as Uri says there may be data loss or if you convert the two
bytes to ascii values there are not meaningful This may help explain:
DECLARE @.nstring NCHAR(1)
DECLARE @.charmask CHAR(2)
DECLARE @.binarymask BINARY(2)
SET @.nstring = NCHAR(260)
SELECT UNICODE(@.nstring),
NCHAR(UNICODE(@.nstring)),
CAST(@.nstring AS CHAR(1)),
ASCII(CAST(@.nstring AS CHAR(1)))
SELECT @.charmask = CAST(@.nstring as char(2)), @.binarymask =
CAST(@.nstring as binary(2))
SELECT @.charmask, ASCII(SUBSTRING(@.charmask, 1, 1)),
ASCII(SUBSTRING(@.charmask, 2, 1)),
@.binarymask, ASCII(SUBSTRING(@.binarymask, 1, 1)),
ASCII(SUBSTRING(@.binarymask, 2, 1))
John|||Hi John,
Actually I am using powerbuilder to make reports.
When I input chinese word (double byte) to a field, it will change it to 2
single byte (using powerbuilder).
But the datawindow can read the "2 single byte" data and print the chinese
word.
However, when I use web programs (ASP) and (nvarchar) to store the chinese
word, the report cannot read the double byte character.
It just shows ?.
Should I change the 1 double byte data to 2 single byte data?
How can I print the report in chinese word and using nvarchar to store data?
Win
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1116937596.821455.153100@.g49g2000cwa.googlegroups.com...
> Hi
> Yes, but as Uri says there may be data loss or if you convert the two
> bytes to ascii values there are not meaningful This may help explain:
> DECLARE @.nstring NCHAR(1)
> DECLARE @.charmask CHAR(2)
> DECLARE @.binarymask BINARY(2)
> SET @.nstring = NCHAR(260)
> SELECT UNICODE(@.nstring),
> NCHAR(UNICODE(@.nstring)),
> CAST(@.nstring AS CHAR(1)),
> ASCII(CAST(@.nstring AS CHAR(1)))
> SELECT @.charmask = CAST(@.nstring as char(2)), @.binarymask =
> CAST(@.nstring as binary(2))
> SELECT @.charmask, ASCII(SUBSTRING(@.charmask, 1, 1)),
> ASCII(SUBSTRING(@.charmask, 2, 1)),
> @.binarymask, ASCII(SUBSTRING(@.binarymask, 1, 1)),
> ASCII(SUBSTRING(@.binarymask, 2, 1))
> John
>|||Hi
I don't know what powerbuilder is doing under the covers to make sense of
this. If your database is currently nchar then how do you know Powerbuilder
is using 2 single byte characters?
When your asp page displays a '?' this usually means your browser can not
interpret the character to display it. You may need to set the codepage as i
n
http://msdn.microsoft.com/library/d...ge
n.asp
This page and the links may also be worth reading:
http://support.microsoft.com/defaul...kb;en-us;303612
HTH
John
"Win" wrote:

> Hi John,
> Actually I am using powerbuilder to make reports.
> When I input chinese word (double byte) to a field, it will change it to 2
> single byte (using powerbuilder).
> But the datawindow can read the "2 single byte" data and print the chinese
> word.
> However, when I use web programs (ASP) and (nvarchar) to store the chinese
> word, the report cannot read the double byte character.
> It just shows ?.
> Should I change the 1 double byte data to 2 single byte data?
> How can I print the report in chinese word and using nvarchar to store dat
a?
> Win
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:1116937596.821455.153100@.g49g2000cwa.googlegroups.com...
>
>