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 the datatype of a column which is part of a PK

Hi All
I have a table made up of 2 columns as the PK. I am trying to write a script
that changes the datatype of one of the columns which is part of the primary
key. This is what i have tried but i seem not to be getting anywhere:
ALTER TABLE TB1 DROP CONSTRAINT PK_TB1
ALTER COLUMN ACCOUNT VARCHAR(20) NOT NULL
Is there a way to do this? I tried to drop the PKs and then do the changes
and then recreate the PK again. All this gave me errors. Thank you in
advance.> Is there a way to do this? I tried to drop the PKs and then do the changes
> and then recreate the PK again. All this gave me errors.
What does "tried" mean? What errors?
This is the way you do it. You drop the constraints (both primary *and*
foreign key), you change the column, you re-apply the constraints.|||Thank you. It worked.
"Aaron Bertrand [SQL Server MVP]" wrote:

> What does "tried" mean? What errors?
> This is the way you do it. You drop the constraints (both primary *and*
> foreign key), you change the column, you re-apply the constraints.
>
>

Changing Table Name to Write data dynamically.

Hello there, folks.
I have 5 tables with exactly same column types, names, and constraints
but with differnt names.(e.g. table1, table2, ..., table5)
I have a stored procedure(sp_example) that writes some calculated data to
"table1".
but I would like to modify the stored procedure to write the similiar data
(but from different sources) to other tables(table2,... table5)
depending on value passed to the stored procedure
(e.g, exec sp_example '2' will write to table2, exec sp_example '3' will
write to table3 and so on)...
I have tried to use a pattern like
==============================
create procedure sp_example @.param
as
declare @.cmd as Varchar(8000)
set @.cmd = @.cmd + 'INSERT INTO ' + getTableName(@.param)
set @.cmd = @.cmd + ' SELECT * From sometable '
Exec(@.cmd)
==============================
But the problem is that stored procedure i am working with is roughly 1000
lines
and having to write "set @.cmd = @.cmd + '...'" seems like an overkill.
So is there any other way to change the "table" name only dynamically?
Thank you in advance.> I have 5 tables with exactly same column types, names, and
constraints
> but with differnt names.(e.g. table1, table2, ..., table5)
Why? Sounds like a design flaw. Use one table and add an extra column
for whatever attribute is represented by the different table names. You
can still create views with the original table names so you shouldn't
even need to change your code.
Failing that, you could consider using a partitioned view. See Books
Online for details. Good design is the right solution rather than messy
Dynamic SQL.
David Portas
SQL Server MVP
--|||> Why? Sounds like a design flaw. Use one table and add an extra column
Yeah, it seems like so but as a programmer, i have to deal with badly
designed table structures...

> Failing that, you could consider using a partitioned view. See Books
> Online for details. Good design is the right solution rather than messy
I don't think partitioned views can be applied to the problem i have after
going through the online book...

changing English Date to Iranian date

Hi Everyone,

I want to change the form of Date in SQL Server 2005,from English Date,to Iranian Date.

For example, instead of inserting 2007/1/1 write 85/10/11.

Thanks,

Nassa

I am not familiar with the iranian date, is the first date you mentioend correlated to the second one ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

FROM SQL Server 2005 Books Online topic 'CAST and CONVERT (Transact-SQL)'.

SQL Server supports the date format in Arabic style by using the Kuwaiti algorithm.
Hijri is a calendar system with several variations. SQL Server 2005 uses the Kuwaiti algorithm.

See for custom code:
http://www.codeproject.com/useritems/Hijri_Shamsi_Date.asp?df=100&forumid=254936&exp=0&select=1371550

General international resource:
http://www.microsoft.com/globaldev/DrIntl/columns/002/default.mspx

Changing DB with a script using a variable.

I am trying to write a TSQL Script for maintenance that willl read the
databases from sysdatabases and change to each database inturn to perform
some maintenance tasks.
Problem is I can't get SQL to change to the next database.
Tried Exec('Use' +@.dbname) and it doesnt error out, but it also doesn't
change the database.
I know there has to be a way... any ideas?
Has to be available in TSQL as this will become a Stored Procedure when
finished.
Raymond Laubert
MCSE, MCDBA, MCT
You can create your maintenance task as a SP, and then you can execute the
following
EXEC dbo.sp_MSforeachdb @.command1 = "use ? exec <your spname>" But this one
executes on the system dbs also.
The other way is to include the following in your T-SQL:
'USE [' + @.dbname + ']' + char(13) +'Go'
This should work i believe..I used it long time somewhere...
thks,
Manikanth
"Ray Laubert" <ray@.rsl-webhosting.com> wrote in message
news:58B901C8-4603-4B6E-B8AF-C6DBEDD50EFC@.microsoft.com...
>I am trying to write a TSQL Script for maintenance that willl read the
> databases from sysdatabases and change to each database inturn to perform
> some maintenance tasks.
> Problem is I can't get SQL to change to the next database.
> Tried Exec('Use' +@.dbname) and it doesnt error out, but it also doesn't
> change the database.
> I know there has to be a way... any ideas?
> Has to be available in TSQL as this will become a Stored Procedure when
> finished.
>
> --
> Raymond Laubert
> MCSE, MCDBA, MCT

Changing DB with a script using a variable.

I am trying to write a TSQL Script for maintenance that willl read the
databases from sysdatabases and change to each database inturn to perform
some maintenance tasks.
Problem is I can't get SQL to change to the next database.
Tried Exec('Use' +@.dbname) and it doesnt error out, but it also doesn't
change the database.
I know there has to be a way... any ideas?
Has to be available in TSQL as this will become a Stored Procedure when
finished.
Raymond Laubert
MCSE, MCDBA, MCTYou can create your maintenance task as a SP, and then you can execute the
following
EXEC dbo.sp_MSforeachdb @.command1 = "use ? exec <your spname>" But this one
executes on the system dbs also.
The other way is to include the following in your T-SQL:
'USE [' + @.dbname + ']' + char(13) +'Go'
This should work i believe..I used it long time somewhere...
thks,
Manikanth
"Ray Laubert" <ray@.rsl-webhosting.com> wrote in message
news:58B901C8-4603-4B6E-B8AF-C6DBEDD50EFC@.microsoft.com...
>I am trying to write a TSQL Script for maintenance that willl read the
> databases from sysdatabases and change to each database inturn to perform
> some maintenance tasks.
> Problem is I can't get SQL to change to the next database.
> Tried Exec('Use' +@.dbname) and it doesnt error out, but it also doesn't
> change the database.
> I know there has to be a way... any ideas?
> Has to be available in TSQL as this will become a Stored Procedure when
> finished.
>
> --
> Raymond Laubert
> MCSE, MCDBA, MCT|||Hi,
the problem with your command is, that EXEC will open a connection, change
the database on close the connection again. EXEC will use another scope than
the actual script is running in. As Manikanth said, you will have to include
the Script within your EXEC call to execute it on the same execution
context.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--
"Ray Laubert" <ray@.rsl-webhosting.com> wrote in message
news:58B901C8-4603-4B6E-B8AF-C6DBEDD50EFC@.microsoft.com...
>I am trying to write a TSQL Script for maintenance that willl read the
> databases from sysdatabases and change to each database inturn to perform
> some maintenance tasks.
> Problem is I can't get SQL to change to the next database.
> Tried Exec('Use' +@.dbname) and it doesnt error out, but it also doesn't
> change the database.
> I know there has to be a way... any ideas?
> Has to be available in TSQL as this will become a Stored Procedure when
> finished.
>
> --
> Raymond Laubert
> MCSE, MCDBA, MCT
>