changing servers dynamically via TSQL

I know you can easily change databases, as in Use "dbname". Can one
also easily change database servers without having to manually select
the server name from the dropdown?
Thanks a million!
Not at the TSQL level. TSQL is executed by the database server, so you are already connected to the
database server when your TSQL code is executed. So this would have to be done by the client app.
SQL Server 2005 has a successor to OSQL.EXE named SQLCMD.EXE, and I believe that this tool has some
such functionality.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<google.1.jvmail@.spamgourmet.com> wrote in message
news:1143501877.734933.108360@.i39g2000cwa.googlegr oups.com...
>I know you can easily change databases, as in Use "dbname". Can one
> also easily change database servers without having to manually select
> the server name from the dropdown?
> Thanks a million!
>
|||no can do with tsql. if you use sql2k5 sqlcmd, you can use :Connect to
switch server connection.
:Connect server_name[\instance_name] [-l timeout] [-U user_name [-P
password]] ,
Connects to an instance of SQL Server. Also closes the current connection.
-oj
<google.1.jvmail@.spamgourmet.com> wrote in message
news:1143501877.734933.108360@.i39g2000cwa.googlegr oups.com...
>I know you can easily change databases, as in Use "dbname". Can one
> also easily change database servers without having to manually select
> the server name from the dropdown?
> Thanks a million!
>
|||Thanks all for the replies. Not what I wanted to hear, but truth leads
to wisdom, or so they say. :-)
Thanks again.

changing servers dynamically via TSQL

I know you can easily change databases, as in Use "dbname". Can one
also easily change database servers without having to manually select
the server name from the dropdown?
Thanks a million!Not at the TSQL level. TSQL is executed by the database server, so you are already connected to the
database server when your TSQL code is executed. So this would have to be done by the client app.
SQL Server 2005 has a successor to OSQL.EXE named SQLCMD.EXE, and I believe that this tool has some
such functionality.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<google.1.jvmail@.spamgourmet.com> wrote in message
news:1143501877.734933.108360@.i39g2000cwa.googlegroups.com...
>I know you can easily change databases, as in Use "dbname". Can one
> also easily change database servers without having to manually select
> the server name from the dropdown?
> Thanks a million!
>|||no can do with tsql. if you use sql2k5 sqlcmd, you can use :Connect to
switch server connection.
:Connect server_name[\instance_name] [-l timeout] [-U user_name [-P
password]] ,
Connects to an instance of SQL Server. Also closes the current connection.
-oj
<google.1.jvmail@.spamgourmet.com> wrote in message
news:1143501877.734933.108360@.i39g2000cwa.googlegroups.com...
>I know you can easily change databases, as in Use "dbname". Can one
> also easily change database servers without having to manually select
> the server name from the dropdown?
> Thanks a million!
>|||Thanks all for the replies. Not what I wanted to hear, but truth leads
to wisdom, or so they say. :-)
Thanks again.

changing servers dynamically via TSQL

I know you can easily change databases, as in Use "dbname". Can one
also easily change database servers without having to manually select
the server name from the dropdown?
Thanks a million!Not at the TSQL level. TSQL is executed by the database server, so you are a
lready connected to the
database server when your TSQL code is executed. So this would have to be do
ne by the client app.
SQL Server 2005 has a successor to OSQL.EXE named SQLCMD.EXE, and I believe
that this tool has some
such functionality.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<google.1.jvmail@.spamgourmet.com> wrote in message
news:1143501877.734933.108360@.i39g2000cwa.googlegroups.com...
>I know you can easily change databases, as in Use "dbname". Can one
> also easily change database servers without having to manually select
> the server name from the dropdown?
> Thanks a million!
>|||no can do with tsql. if you use sql2k5 sqlcmd, you can use :Connect to
switch server connection.
:Connect server_name[\instance_name] [-l timeout] [-U user_name
[-P
password]] ,
Connects to an instance of SQL Server. Also closes the current connection.
-oj
<google.1.jvmail@.spamgourmet.com> wrote in message
news:1143501877.734933.108360@.i39g2000cwa.googlegroups.com...
>I know you can easily change databases, as in Use "dbname". Can one
> also easily change database servers without having to manually select
> the server name from the dropdown?
> Thanks a million!
>|||Thanks all for the replies. Not what I wanted to hear, but truth leads
to wisdom, or so they say. :-)
Thanks again.

Changing Field name of an existing Table

Hi,
Is it possible to change any fieldname of an existing table?I mean to say
by TSQL statement.We know that we can alter the data type and width etc.
But I haven't got any info about filedname change.So if it is possible
Please help...
And Is there any TSQL command to alter multiple columns in a single statement?

Please help...
Thanks!!
Joydeepif it is not a primary or a foriegn key you can do something like so...

ALTER TABLE Mytable
ADD INQUIRYID2 BIGINT
GO
UPDATE MyTable SET INQUIRYID2 = INQUIRYID
GO
ALTER TABLE MyTable
DROP COLUMN INQUIRYID

But this a bad idea. What about the existing database object that reference the existing column. Oh well too late. A thousand application buga have just been created. Update your resume. You want biggie size|||if you can connect the db thru EM, just type the new name....|||if you can connect the db thru EM, just type the new name....

The Holy Book says ...

sp_rename

Changes the name of a user-created object (for example, table, column, or user-defineddata type) in the current database.
Syntax

sp_rename [ @.objname = ] 'object_name' ,
[ @.newname = ] 'new_name'
[ , [ @.objtype = ] 'object_type' ]
Arguments

[@.objname =] 'object_name'
Is the current name of the user object (table, view, column, stored procedure, trigger, default, database, object, or rule) or data type. If the object to be renamed is a column in a table, object_name must be in the form table.column. If the object to be renamed is an index, object_name must be in the form table.index. object_name is nvarchar(776), with no default.
[@.newname =] 'new_name'
Is the new name for the specified object. new_name must be a one-part name and must follow the rules for identifiers. newname is sysname, with no default.
[@.objtype =] 'object_type'
Is the type of object being renamed. object_type is varchar(13), with a default of NULL, and can be one of these values.
ValueDescriptionCOLUMNA column to be renamed.DATABASEA user-defined database. This option is required when renaming a database.INDEXA user-defined index.OBJECTAn item of a type tracked in sysobjects. For example, OBJECT could be used to rename objects including constraints (CHECK, FOREIGN KEY, PRIMARY/UNIQUE KEY), user tables, views, stored procedures, triggers, and rules.USERDATATYPEA user-defined data type added by executing sp_addtype.


I don't know why you are providing a round about way of doing the same thing.|||You can try out this one.

EXECUTE sp_rename N'MyTable.MyCurrFName', N'MyNewFName', 'COLUMN'

MyTable has a Field MyCurrFName. It will be renamed to MyNewFName|||You are better off either a). Create a view that has the name you want, or b). unloading, dropping, create, and load

There was a very good article that discusses why do the alter causes sql server to waste a lot of space on the data pages...Now if I can find on Nigel's web site I'll post a link|||Here's the link

http://www.mindsdoor.net/SQLAdmin/AlterTableProblems.html|||thanks Brett, thats really a good link. never thought of it.

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
>