Change connection with T-SQL?

Hi,

I know I can right-click in query editor window and choose "Connection->Change Connection", but is there any T-SQL code or system stored proc I can use to dynamically change server connection in the middle of a script?

Thanks,

Dave

The sqlcmd command prompt utility which can also be run in Query Editor in sqlcmd mode.

See SQL Server 2005 Books Online topic for script information

Using the sqlcmd Utility

http://msdn2.microsoft.com/en-us/library/ms180944.aspx

E. Using sqlcmd to execute code on multiple instances

The following code in a file shows a script that connects to two instances. Notice the GO before the connection to the second instance.

:CONNECT <server>\,<instance1>

EXEC dbo.SomeProcedure

GO

:CONNECT <server>\,<instance2>

EXEC dbo.SomeProcedure

GO

See SQL Server 2005 Books Onlinetopic

Editing SQLCMD Scripts with Query Editor

http://msdn2.microsoft.com/en-gb/library/ms174187.aspx

Change Column Ordinal Position with T-SQL

Hi Friends ...
I need to change the ordinal position of a column in an existing table using
T-SQL through QA - anybody know the proprer syntax?
Thanks for your help ...Hi,
If you don't use bad syntax as "select * from MyTable", you shouldn't have
tou deal with ordinal position.
Anyway, there's no solution to change ordinal position, because there
shouldn't be any need to.
The only way to do this is to drop - recreate the table.
JN.
"bill_morgan" wrote:

> Hi Friends ...
> I need to change the ordinal position of a column in an existing table usi
ng
> T-SQL through QA - anybody know the proprer syntax?
> Thanks for your help ...|||If you want to see the script involved with your table,
do this in Enterprise Manager but before saving the changes,
click on the icon to see the script.
"Jean-Nicolas BERGER" <JeanNicolasBERGER@.discussions.microsoft.com> wrote in
message news:09BBB5B8-2830-474F-BC84-471F33DAFCA7@.microsoft.com...
> Hi,
> If you don't use bad syntax as "select * from MyTable", you shouldn't have
> tou deal with ordinal position.
> Anyway, there's no solution to change ordinal position, because there
> shouldn't be any need to.
> The only way to do this is to drop - recreate the table.
> JN.
> "bill_morgan" wrote:
>|||> I need to change the ordinal position of a column in an existing table
> using
> T-SQL through QA - anybody know the proprer syntax?
There is no such thing, at least not in a single statement.
http://www.aspfaq.com/2528|||Actually, the local admin changed orginal position, and I wanted to make sur
e
there wasn't syntax in this regard I hadn't learned yet. He must have done i
t
through EM.
Thanks ...
"Jean-Nicolas BERGER" wrote:
> Hi,
> If you don't use bad syntax as "select * from MyTable", you shouldn't have
> tou deal with ordinal position.
> Anyway, there's no solution to change ordinal position, because there
> shouldn't be any need to.
> The only way to do this is to drop - recreate the table.
> JN.
> "bill_morgan" wrote:
>|||Thanks for your response ... mainly wanted to see if it's possible in QA
rather than having to use EM. Now I know ...
Thanks again ...
"Raymond D'Anjou" wrote:

> If you want to see the script involved with your table,
> do this in Enterprise Manager but before saving the changes,
> click on the icon to see the script.
> "Jean-Nicolas BERGER" <JeanNicolasBERGER@.discussions.microsoft.com> wrote
in
> message news:09BBB5B8-2830-474F-BC84-471F33DAFCA7@.microsoft.com...
>
>|||Don't forget, this is easily done on an empty table.
On a table with millions of rows, you can imagine the time this operation
can take.
But as others have said, column order should not be a concern.
"bill_morgan" <billmorgan@.discussions.microsoft.com> wrote in message
news:77D61C48-5CF5-4502-B35C-11D8ACA160B6@.microsoft.com...
> Actually, the local admin changed orginal position, and I wanted to make
> sure
> there wasn't syntax in this regard I hadn't learned yet. He must have done
> it
> through EM.
> Thanks ...
> "Jean-Nicolas BERGER" wrote:
>

Change column from datetime to int

I want to change a column from datetime to int. I'm using this T-SQL:
ALTER TABLE WebPages
ALTER COLUMN EndDate int;
I get this result:
Server: Msg 260, Level 16, State 1, Line 1
Disallowed implicit conversion from data type datetime to data type int,
table 'gIQInternetMaster.dbo.WebPages', column 'EndDate'. Use the CONVERT
function to run this query.
If I use Enterprise Manager the change happens without error. However, I
need to do this conversion as part of a larger script so I need to do it in
code. Can anyone tell me what EM is doing behind the scenes that allows this
to succeed? Thank you!Hi Ron
Please always state what version you are using.
I assume you are using SQL 2000 since you referred to Enterprise Manager. If
you trace what SQL Server is doing when you change datetime to int in EM,
you will see that it is actually recreating the entire table, selecting from
the old table using convert for the EndDate column, inserting into a new
table, dropping the original table and renaming the new table to the old
name. All indexes and triggers need to be rebuilt. This can be quite a
time-consuming process for a large table, but it is do-able. Just not with a
single statement.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in message
news:%23LP8GFhVHHA.2212@.TK2MSFTNGP02.phx.gbl...
>I want to change a column from datetime to int. I'm using this T-SQL:
> ALTER TABLE WebPages
> ALTER COLUMN EndDate int;
> I get this result:
> Server: Msg 260, Level 16, State 1, Line 1
> Disallowed implicit conversion from data type datetime to data type int,
> table 'gIQInternetMaster.dbo.WebPages', column 'EndDate'. Use the CONVERT
> function to run this query.
> If I use Enterprise Manager the change happens without error. However, I
> need to do this conversion as part of a larger script so I need to do it
> in
> code. Can anyone tell me what EM is doing behind the scenes that allows
> this
> to succeed? Thank you!
>
>

cdonts send mail T-SQL - IIS SMTP setup

hello,

I would like to send mail from sql server using CDONTS.

I have the sql code for this and it works for internal addresses.

I get an error/warning saying that it cannot relay for external addresses when I try an outside domain.

Is there settings in IIS for the SMTP service that I can change to allow it to relay to the external domains?

I have everything running on the SQL Server, and I do not have access to the exchange server.

ThanksCDONTS sends email to the Local SMTP server. You can specify a "smart host" that the local server can forward any email it can't deliver.

Search the help for the IIS SMTP Virtual Server. It's likely the network blocks any unapproved system from using the smtp port, and it's also likely the approved ones will need to allow your system to relay through them.

Jay Grubb
Technical Consultant
OpenLink Software
Web: http://www.openlinksw.com:
Product Weblogs:
Virtuoso: http://www.openlinksw.com/weblogs/virtuoso
UDA: http://www.openlinksw.com/weblogs/uda
Universal Data Access & Virtual Database Technology Providers

CASTING statement for a date

I am selecting older legacy data from an AS400 mainframe that we still use. I am fairly new to constructing T-SQL statements so I hope I am doing this correctly. There is a table from the AS400 that has been setup with a field for TMONTH, TDAY, and TYEAR. Instead of having one field for a date, for some reason years ago this was set up this way.

I now have this statement in my SELECT statement and it is not working:

WHERE (CAST(OWNR.TMONTH + '/' + OWNR.TDAY + '/' + OWNR.TYEAR AS DATETIME) >= @.startdate)

I am not getting a syntax error, however I am getting "Error Converting data type varchar to numeric. These fields on the AS400 are set up as numeric fields.

What do I need to do differently? Should I use a CONVERT instead and if so, how would I structure that statement.

Thanks for the help

SQL Server will assume if one value is numeric that there has to be done an addition rather than a concatenation. So you will have to CAST all the numerics to chars like that:

WHERE (CAST(CAST(OWNR.TMONTH AS VARCHAR(2))+ '/' + CAST(OWNR.TDAY AS VARCHAR(2)) + '/' + CAST(OWNR.TYEAR AS VARCHAR(2)) AS DATETIME) >= @.startdate)

Or something like that:

WHERE CAST((100*OWNR.TMONTH + OWNR.TDAY + OWNR.TYEAR*10000) AS DATETIME) >= @.startdate)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

You may want to have a look at the answer to question 2 that I received in my post for Questions on dates. Kenneth has provided a means for using an international date format that is simple.

Code is:

cast(@.year as char(4)) + right('0' + cast(@.month as varchar(2)),2) + right('0' + cast(@.day as varchar(2)),2)

which may be assigned to a date variable. You will need to ensure that you have error checking to ensure that the date is valid.

Regards,

Flavelle