changing the server.database against which a script is executed

Hi there:
I write scripts which I must execute in multiple environments (Dev,
Test, UAT, Prod). Dev and Test are located on the same server, so
it's easy to change the db with the USE command. UAT and Prod are on
another server. If I close the script and re-open it, I'm not
prompted for connection information -- it simply defaults to whichever
connection I used last (because I haven't closed SSMS?).
So far, I've use the Object Browser to navigate to an object within
the server.db I want, click New Query, then copy & paste the query
into the new window. This seems pretty cheesy. Is there a more
elegant approach?
Thanks;
Duncan
Duncan A. McRae (google.com@.mcrae.ca) writes:
> I write scripts which I must execute in multiple environments (Dev,
> Test, UAT, Prod). Dev and Test are located on the same server, so
> it's easy to change the db with the USE command. UAT and Prod are on
> another server. If I close the script and re-open it, I'm not
> prompted for connection information -- it simply defaults to whichever
> connection I used last (because I haven't closed SSMS?).
> So far, I've use the Object Browser to navigate to an object within
> the server.db I want, click New Query, then copy & paste the query
> into the new window. This seems pretty cheesy. Is there a more
> elegant approach?
Yes. Right-click in the query window and select Change Connection from
the context menu.
Overall, I prefer Query Analyzer over Mgmt Studio, but being able to
change the connection for a query window is a great feature.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Changing Table Owner and user Owner

Hello there
I'm using Windows authentication, and i'm suddenly don't member of Dbo
owners.
What i need to change on the server in order so when i create new object
it's written as dbo and not as my name?
after i change that how can i change the owner of tables, views and store
procedures to be dbo owner?
' 03-5611606
' 050-7709399
: roy@.atidsm.co.ilRoy,shalom
Pls read this article from BOL
Database Owner (dbo)
The dbo is a user that has implied permissions to perform all activities in
the database. Any member of the sysadmin fixed server role who uses a
database is mapped to the special user inside each database called dbo.
Also, any object created by any member of the sysadmin fixed server role
belongs to dbo automatically.
For example, if user Andrew is a member of the sysadmin fixed server role
and creates a table T1, T1 belongs to dbo and is qualified as dbo.T1, not as
Andrew.T1. Conversely, if Andrew is not a member of the sysadmin fixed
server role but is a member only of the db_owner fixed database role and
creates a table T1, T1 belongs to Andrew and is qualified as Andrew.T1. The
table belongs to Andrew because he did not qualify the table as dbo.T1.
The dbo user cannot be deleted and is always present in every database.
Only objects created by members of the sysadmin fixed server role (or by the
dbo user) belong to dbo. Objects created by any other user who is not also a
member of the sysadmin fixed server role (including members of the db_owner
fixed database role):
a.. Belong to the user creating the object, not dbo.
b.. Are qualified with the name of the user who created the object.
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:OWTo$T54FHA.3188@.TK2MSFTNGP15.phx.gbl...
> Hello there
> I'm using Windows authentication, and i'm suddenly don't member of Dbo
> owners.
> What i need to change on the server in order so when i create new object
> it's written as dbo and not as my name?
> after i change that how can i change the owner of tables, views and store
> procedures to be dbo owner?
> --
>
>
> ' 03-5611606
> ' 050-7709399
> : roy@.atidsm.co.il
>

Changing server collation

Hello there
I've tried to change collation of my server using rebuildm
It gave me an error: Rebuild master failed with error: -1
The database was created succeffuly, but the collation hasn't changed.
where can be the problem?Roy
In that case I'd resinstall server
You change a collation per database/table/column
SELECT
'ALTER TABLE ' + TABLE_NAME +
' ALTER COLUMN ' + COLUMN_NAME +
' ' + DATA_TYPE +' '+
CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL
THEN '('+(CONVERT(VARCHAR,CHARACTER_MAXIMUM_L
ENGTH)+')' )
ELSE isnull(CONVERT(VARCHAR,CHARACTER_MAXIMUM
_LENGTH),' ')
END
+' COLLATE Put here your colation '+
CASE IS_NULLABLE
WHEN 'YES' THEN 'NULL'
WHEN 'No' THEN 'NOT NULL'
END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('varchar' ,'char','nvarchar','nchar')
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:u$jxwQFQGHA.3256@.TK2MSFTNGP15.phx.gbl...
> Hello there
> I've tried to change collation of my server using rebuildm
> It gave me an error: Rebuild master failed with error: -1
> The database was created succeffuly, but the collation hasn't changed.
> where can be the problem?
>|||Hi Roy
To add to Uri's post, if rebuildm does not work first time I usually resort
to re-installing as the time to run rebuildm twice is probably more than a
full install.
You may want to set the new collation to default rather than a specific one,
this will pick up the database collation (so change it first). Using '['
and
']' around your object names may also be necessary.
You may also want to check out other posts such as http://tinyurl.com/429qa
John
"Roy Goldhammer" wrote:

> Hello there
> I've tried to change collation of my server using rebuildm
> It gave me an error: Rebuild master failed with error: -1
> The database was created succeffuly, but the collation hasn't changed.
> where can be the problem?
>
>|||Whell Uri
This procedure has errors due to collation conflict, which it was suppose to
fix.
I've solved it, but it doesn't work if the column has index, constraint or
primary key.
The only way to solve it, is or by changing it with EM if the amount of
cases is realy small, or use generate databases script, and on the query
anlyser remove all the collations.
Is there better way to do this?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23ncWUmFQGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Roy
> In that case I'd resinstall server
> You change a collation per database/table/column
> SELECT
> 'ALTER TABLE ' + TABLE_NAME +
> ' ALTER COLUMN ' + COLUMN_NAME +
> ' ' + DATA_TYPE +' '+
> CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL
> THEN '('+(CONVERT(VARCHAR,CHARACTER_MAXIMUM_L
ENGTH)+')' )
> ELSE isnull(CONVERT(VARCHAR,CHARACTER_MAXIMUM
_LENGTH),' ')
> END
> +' COLLATE Put here your colation '+
> CASE IS_NULLABLE
> WHEN 'YES' THEN 'NULL'
> WHEN 'No' THEN 'NOT NULL'
> END
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE DATA_TYPE IN ('varchar' ,'char','nvarchar','nchar')
>
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:u$jxwQFQGHA.3256@.TK2MSFTNGP15.phx.gbl...
>|||Hi Roy
If you profiled what EM was doing you would probably see that it droped
constraints and indexes before issing the same statement. If your source cod
e
is in version control this would not be a big issue. You could use DMO or EM
to script these or the INFORMATION_SCHEMA.KEY_COLUMN_USAGE and sysindexes
tables to generate a script using T-SQL.
John
"Roy Goldhammer" wrote:

> Whell Uri
> This procedure has errors due to collation conflict, which it was suppose
to
> fix.
> I've solved it, but it doesn't work if the column has index, constraint or
> primary key.
> The only way to solve it, is or by changing it with EM if the amount of
> cases is realy small, or use generate databases script, and on the query
> anlyser remove all the collations.
> Is there better way to do this?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23ncWUmFQGHA.2628@.TK2MSFTNGP15.phx.gbl...
>
>