Changing the Default Port

Is it possible to change the default port of 1433 for Sql Server after the
database has been installed? If so, can someone point me in the right
direction?Use Server Server Network Utility
"Simon" <nospam@.nospam.nospam> wrote in message
news:%23$HR7tzuDHA.2448@.TK2MSFTNGP09.phx.gbl...
> Is it possible to change the default port of 1433 for Sql Server after the
> database has been installed? If so, can someone point me in the right
> direction?
>|||Yes. You can do this from the Server Network Utility program. Select the
TCP/IP netlib and then click properties.
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Simon" <nospam@.nospam.nospam> wrote in message
news:%23$HR7tzuDHA.2448@.TK2MSFTNGP09.phx.gbl...
> Is it possible to change the default port of 1433 for Sql Server after the
> database has been installed? If so, can someone point me in the right
> direction?
>|||Duh... Brian, thank you!
BTW, great articles in SQL Server magazine!
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:O9oOSxzuDHA.620@.TK2MSFTNGP10.phx.gbl...
> Yes. You can do this from the Server Network Utility program. Select the
> TCP/IP netlib and then click properties.
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "Simon" <nospam@.nospam.nospam> wrote in message
> news:%23$HR7tzuDHA.2448@.TK2MSFTNGP09.phx.gbl...
> >
> > Is it possible to change the default port of 1433 for Sql Server after
the
> > database has been installed? If so, can someone point me in the
right
> > direction?
> >
> >
>|||I'm glad you enjoy them!
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Simon" <nospam@.nospam.nospam> wrote in message
news:ebG5A2zuDHA.2340@.TK2MSFTNGP12.phx.gbl...
> Duh... Brian, thank you!
> BTW, great articles in SQL Server magazine!
> "Brian Moran" <brian@.solidqualitylearning.com> wrote in message
> news:O9oOSxzuDHA.620@.TK2MSFTNGP10.phx.gbl...
> > Yes. You can do this from the Server Network Utility program. Select the
> > TCP/IP netlib and then click properties.
> >
> > --
> >
> > Brian Moran
> > Principal Mentor
> > Solid Quality Learning
> > SQL Server MVP
> > http://www.solidqualitylearning.com
> >
> >
> > "Simon" <nospam@.nospam.nospam> wrote in message
> > news:%23$HR7tzuDHA.2448@.TK2MSFTNGP09.phx.gbl...
> > >
> > > Is it possible to change the default port of 1433 for Sql Server after
> the
> > > database has been installed? If so, can someone point me in the
> right
> > > direction?
> > >
> > >
> >
> >
>

Changing the color of the series label

I am able to change the colors of the series inside the graph, however I
would like to make one data point stand out based on a parameter that is
passed in. I have already set up the color schemes for the series to do this
inside the chart but would like the label to make that distinction as well.
For example I would like to set the series label to red for one data point
while all of the other labels would be black. Is this possible?
ThanksIf you have at least RS 2000 SP1 installed, you can edit the data point
label properties (font, color, etc.) and use expressions to determine the
color based on certain conditions.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Todd" <Todd@.discussions.microsoft.com> wrote in message
news:EAEF7051-1909-4CD8-93D8-F6842C2887B9@.microsoft.com...
>I am able to change the colors of the series inside the graph, however I
> would like to make one data point stand out based on a parameter that is
> passed in. I have already set up the color schemes for the series to do
> this
> inside the chart but would like the label to make that distinction as
> well.
> For example I would like to set the series label to red for one data point
> while all of the other labels would be black. Is this possible?
> Thanks|||I am running with SP2 and have attempted to put in the code to make this
happen but it renders all of the labels with the color not just the one that
I am looking for. I have tested the snippet of code elsewhere and it works
fine. It seems as if the chart rendering looks at the first attribute passed
for the series labels and stops looking at the attributes for the rest of the
data points.
"Robert Bruckner [MSFT]" wrote:
> If you have at least RS 2000 SP1 installed, you can edit the data point
> label properties (font, color, etc.) and use expressions to determine the
> color based on certain conditions.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Todd" <Todd@.discussions.microsoft.com> wrote in message
> news:EAEF7051-1909-4CD8-93D8-F6842C2887B9@.microsoft.com...
> >I am able to change the colors of the series inside the graph, however I
> > would like to make one data point stand out based on a parameter that is
> > passed in. I have already set up the color schemes for the series to do
> > this
> > inside the chart but would like the label to make that distinction as
> > well.
> >
> > For example I would like to set the series label to red for one data point
> > while all of the other labels would be black. Is this possible?
> >
> > Thanks
>
>|||Does the chart have a series grouping?
If yes, then assuming you have a series grouping called "ProductCategory",
you have to use a style color expression with an aggregate function:
= iif(First(Fields!Abc.Value, "ProductCategory") > 10, "Red", Nothing)
The important part is the aggregate scope which has to be identical to the
chart series grouping name. Just using the First aggregate without the scope
will give you incorrect results, because the aggregate will be just scoped
for every chart datapoint (and therefore null if you don't have any
datapoints for a particular series group / category group combination).
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Todd" <Todd@.discussions.microsoft.com> wrote in message
news:3048F968-1AA1-474D-9EEA-4C985F5DC1DA@.microsoft.com...
>I am running with SP2 and have attempted to put in the code to make this
> happen but it renders all of the labels with the color not just the one
> that
> I am looking for. I have tested the snippet of code elsewhere and it
> works
> fine. It seems as if the chart rendering looks at the first attribute
> passed
> for the series labels and stops looking at the attributes for the rest of
> the
> data points.
> "Robert Bruckner [MSFT]" wrote:
>> If you have at least RS 2000 SP1 installed, you can edit the data point
>> label properties (font, color, etc.) and use expressions to determine the
>> color based on certain conditions.
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Todd" <Todd@.discussions.microsoft.com> wrote in message
>> news:EAEF7051-1909-4CD8-93D8-F6842C2887B9@.microsoft.com...
>> >I am able to change the colors of the series inside the graph, however I
>> > would like to make one data point stand out based on a parameter that
>> > is
>> > passed in. I have already set up the color schemes for the series to
>> > do
>> > this
>> > inside the chart but would like the label to make that distinction as
>> > well.
>> >
>> > For example I would like to set the series label to red for one data
>> > point
>> > while all of the other labels would be black. Is this possible?
>> >
>> > Thanks
>>|||No, The chart does not have series grouping. I also wonder if I have
communicated this effectively. I am talking about the labels on the x-axis.
I am only concerned with the series labels not data point labels. Does this
help to clarify?
"Robert Bruckner [MSFT]" wrote:
> Does the chart have a series grouping?
> If yes, then assuming you have a series grouping called "ProductCategory",
> you have to use a style color expression with an aggregate function:
> = iif(First(Fields!Abc.Value, "ProductCategory") > 10, "Red", Nothing)
> The important part is the aggregate scope which has to be identical to the
> chart series grouping name. Just using the First aggregate without the scope
> will give you incorrect results, because the aggregate will be just scoped
> for every chart datapoint (and therefore null if you don't have any
> datapoints for a particular series group / category group combination).
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Todd" <Todd@.discussions.microsoft.com> wrote in message
> news:3048F968-1AA1-474D-9EEA-4C985F5DC1DA@.microsoft.com...
> >I am running with SP2 and have attempted to put in the code to make this
> > happen but it renders all of the labels with the color not just the one
> > that
> > I am looking for. I have tested the snippet of code elsewhere and it
> > works
> > fine. It seems as if the chart rendering looks at the first attribute
> > passed
> > for the series labels and stops looking at the attributes for the rest of
> > the
> > data points.
> >
> > "Robert Bruckner [MSFT]" wrote:
> >
> >> If you have at least RS 2000 SP1 installed, you can edit the data point
> >> label properties (font, color, etc.) and use expressions to determine the
> >> color based on certain conditions.
> >>
> >> -- Robert
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "Todd" <Todd@.discussions.microsoft.com> wrote in message
> >> news:EAEF7051-1909-4CD8-93D8-F6842C2887B9@.microsoft.com...
> >> >I am able to change the colors of the series inside the graph, however I
> >> > would like to make one data point stand out based on a parameter that
> >> > is
> >> > passed in. I have already set up the color schemes for the series to
> >> > do
> >> > this
> >> > inside the chart but would like the label to make that distinction as
> >> > well.
> >> >
> >> > For example I would like to set the series label to red for one data
> >> > point
> >> > while all of the other labels would be black. Is this possible?
> >> >
> >> > Thanks
> >>
> >>
> >>
>
>|||Sorry, the individual x-axis labels share the same color and font settings.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Todd" <Todd@.discussions.microsoft.com> wrote in message
news:7391723B-1CAB-4990-A5A7-E41A41DAF65E@.microsoft.com...
> No, The chart does not have series grouping. I also wonder if I have
> communicated this effectively. I am talking about the labels on the
> x-axis.
> I am only concerned with the series labels not data point labels. Does
> this
> help to clarify?
> "Robert Bruckner [MSFT]" wrote:
>> Does the chart have a series grouping?
>> If yes, then assuming you have a series grouping called
>> "ProductCategory",
>> you have to use a style color expression with an aggregate function:
>> = iif(First(Fields!Abc.Value, "ProductCategory") > 10, "Red", Nothing)
>> The important part is the aggregate scope which has to be identical to
>> the
>> chart series grouping name. Just using the First aggregate without the
>> scope
>> will give you incorrect results, because the aggregate will be just
>> scoped
>> for every chart datapoint (and therefore null if you don't have any
>> datapoints for a particular series group / category group combination).
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Todd" <Todd@.discussions.microsoft.com> wrote in message
>> news:3048F968-1AA1-474D-9EEA-4C985F5DC1DA@.microsoft.com...
>> >I am running with SP2 and have attempted to put in the code to make this
>> > happen but it renders all of the labels with the color not just the one
>> > that
>> > I am looking for. I have tested the snippet of code elsewhere and it
>> > works
>> > fine. It seems as if the chart rendering looks at the first attribute
>> > passed
>> > for the series labels and stops looking at the attributes for the rest
>> > of
>> > the
>> > data points.
>> >
>> > "Robert Bruckner [MSFT]" wrote:
>> >
>> >> If you have at least RS 2000 SP1 installed, you can edit the data
>> >> point
>> >> label properties (font, color, etc.) and use expressions to determine
>> >> the
>> >> color based on certain conditions.
>> >>
>> >> -- Robert
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >>
>> >>
>> >> "Todd" <Todd@.discussions.microsoft.com> wrote in message
>> >> news:EAEF7051-1909-4CD8-93D8-F6842C2887B9@.microsoft.com...
>> >> >I am able to change the colors of the series inside the graph,
>> >> >however I
>> >> > would like to make one data point stand out based on a parameter
>> >> > that
>> >> > is
>> >> > passed in. I have already set up the color schemes for the series
>> >> > to
>> >> > do
>> >> > this
>> >> > inside the chart but would like the label to make that distinction
>> >> > as
>> >> > well.
>> >> >
>> >> > For example I would like to set the series label to red for one data
>> >> > point
>> >> > while all of the other labels would be black. Is this possible?
>> >> >
>> >> > Thanks
>> >>
>> >>
>> >>
>>sql

changing in sysaltfiles doesnt work

HI All,

I have started sqlserver in single user mode and changed filename in sysaltfiles for tempdb too point to new location. While starting sqlserver in normal it points to the old path and doesnt get updated with the new path.

1.)is there any systable still to be altered?
2.) i have even tried alter database, doesnt work.
3.)The master files have been taken from server1 (where tempdb points to d:\data) to server2 ( where i need to point tempdb to point E:\mssql\data). i can detach and attach msdb and model successfully, but in the case of tempdb, i cant either alter sysaltfiles or detach and attach tempdb to new path.

, too tired in trying all the possiblities... Is there any possibility to update tempdb to point to new path?

These should work and take effect after a restart.

ALTER DATABASE tempdb MODIFY FILE (NAME='tempdev', FILENAME='e:\mssql\data\tempdb.mdf')

GO

ALTER DATABASE tempdb MODIFY FILE (NAME='templog', FILENAME='e:\mssql\data\tempdb.ldf')

GO

SHUTDOWN
GO

What version of SQL Server are you running?

|||Hi Peter,

If tempdb exists on D:\data drive then above script is working fine. Here in my case no D:\data exists, ive copied master from another server and i need this to point to E: drive. I have tried the above and got a error as

tempdb not found
connection broken

hence i cant change the tempdb path alone.

Thx|||

Could you please post what version of SQL Server are you running? Did you run the statment from master? What is in the error log?

-Sue

|||

You can start the server with the -f parameter to get a minimal sized tempdb in the same directory as master, then move it where you want.

i.e., for the default instance, you would do:

net start mssqserver /f

changing in sysaltfiles doesnt work

HI All,

I have started sqlserver in single user mode and changed filename in sysaltfiles for tempdb too point to new location. While starting sqlserver in normal it points to the old path and doesnt get updated with the new path.

1.)is there any systable still to be altered?
2.) i have even tried alter database, doesnt work.
3.)The master files have been taken from server1 (where tempdb points to d:\data) to server2 ( where i need to point tempdb to point E:\mssql\data). i can detach and attach msdb and model successfully, but in the case of tempdb, i cant either alter sysaltfiles or detach and attach tempdb to new path.

, too tired in trying all the possiblities... Is there any possibility to update tempdb to point to new path?

These should work and take effect after a restart.

ALTER DATABASE tempdb MODIFY FILE (NAME='tempdev', FILENAME='e:\mssql\data\tempdb.mdf')

GO

ALTER DATABASE tempdb MODIFY FILE (NAME='templog', FILENAME='e:\mssql\data\tempdb.ldf')

GO

SHUTDOWN
GO

What version of SQL Server are you running?

|||Hi Peter,

If tempdb exists on D:\data drive then above script is working fine. Here in my case no D:\data exists, ive copied master from another server and i need this to point to E: drive. I have tried the above and got a error as

tempdb not found
connection broken

hence i cant change the tempdb path alone.

Thx|||

Could you please post what version of SQL Server are you running? Did you run the statment from master? What is in the error log?

-Sue

|||

You can start the server with the -f parameter to get a minimal sized tempdb in the same directory as master, then move it where you want.

i.e., for the default instance, you would do:

net start mssqserver /f

Changing Database Mail Host Database

Hi

I am trying to change the host database (MSDB) of Database Mail to some new database (say NewDB) and

unable to make DatabaseMail90.exe point to this new database (NewDB).

Is it possible to do this?

Thanks

Uddemarri

When I passed the database name and server name as command line parameters to DatabaseMail90.exe , it picked messages from the queue but did not send any mail. The following error is logged in sysmail_event_log.

1) Exception Information =================== Exception Type: System.NullReferenceException Message: Object reference not set to an instance of an object. Data: System.Collections.ListDictionaryInternal TargetSite: Microsoft.SqlServer.Management.SqlIMail.Server.Objects.Account GetAccount(Int32) HelpLink: NULL Source: DatabaseMailEngine StackTrace Information =================== at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.DataAccessAdapter.GetAccount(Int32 accountID) at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.SessionManager.GetAccount(Int32 accountID) at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandFactory.CreateSendMailCommand(DBSession dbSession) at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandFactory.CreateCommand(DBSession dbSession) at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandRunner.Run(DBSession db) at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.ThreadCallBack.MailOperation(Object o)

Any ideas?

|||That's definitely not a supported or tested option. I don't think you can start the mail service yourself.|||Thanks Roger.

Changing database in CR9/XI and VB

I have a sql database and a set of reports that point to that database. I have now copied that database to a 'new' database on the same sql server. I have made some changes to the data so that I would know the different but the structure and object names remain exactly the same. This 'new' database will be our demo version.

I went into CR9 and opened up one of the reports and then chose Set Database Location. I created a new SQL connect to the same server, same User ID, same password, but a different database. It seemed to work and when I looked at he properties under the server name the reading against 'Initial Catalog' was the 'new' database. However when I chose to preview the report it ran ok but it show data from it's original database not the 'new' database.

In VB/ASP code using connectionproperties.item("Initial Catalog") = . I also tried it in CR XI but got the same result. From experience I do know that changing server, user id, and password work.

My first thoughts was that I was doing something wrong, missing something but I cannot think what. Can you please help meOpen the report and do verify database

Changing Cube source in a EXCEL pivot table

Hi

I have developed a cube on a "test" server - using Excel 2003 as teh front end. All is well with the cube an dI now want to point Excel to the "production" server. I have modified the .oqy file to point to teh new server but Excel does not seem to use this - unless I start over and import data.

is there anyway of pointing to a different server (teh database/cube names are the same - I did an Archive and Restore to move teh database over)

thanks

Steve

By the way - this is SQL Analysis Server 2000 and Excel 2003|||

There are 2 techniques I know of to change the data source for an Excel pivot table.

The "low tech" one is to shut down the Analysis Services instance on your test server. Then when you open up the Excel file, Excel will prompt you for a server.

The "hight tech" option is to write some VBA code to change the connection string. The trick to getting at the connection string is that it is stored on the connection property of the PivotCache object. To change a single pivot table on the current sheet you would write something like the following:

thisWorkbook.ActiveSheet.PivotTables(1).PivotCache.Connection = "Provider-MSOLAP.2;Data Source=<Server>;Initial Catalog=<Database>"

|||Another technique, which was mentioned on the public SQL OLAP Newsgroup, is to save the pivot table as an XML file. Then you can find and edit the connection string (search for the server/DB/cube names), and re-open and save the XML back as .XLS.|||

Thanks to all.

I checked out the XML and the VBA solutions - at least I have options now.

Changing Cube source in a EXCEL pivot table

Hi

I have developed a cube on a "test" server - using Excel 2003 as teh front end. All is well with the cube an dI now want to point Excel to the "production" server. I have modified the .oqy file to point to teh new server but Excel does not seem to use this - unless I start over and import data.

is there anyway of pointing to a different server (teh database/cube names are the same - I did an Archive and Restore to move teh database over)

thanks

Steve

By the way - this is SQL Analysis Server 2000 and Excel 2003|||

There are 2 techniques I know of to change the data source for an Excel pivot table.

The "low tech" one is to shut down the Analysis Services instance on your test server. Then when you open up the Excel file, Excel will prompt you for a server.

The "hight tech" option is to write some VBA code to change the connection string. The trick to getting at the connection string is that it is stored on the connection property of the PivotCache object. To change a single pivot table on the current sheet you would write something like the following:

thisWorkbook.ActiveSheet.PivotTables(1).PivotCache.Connection = "Provider-MSOLAP.2;Data Source=<Server>;Initial Catalog=<Database>"

|||Another technique, which was mentioned on the public SQL OLAP Newsgroup, is to save the pivot table as an XML file. Then you can find and edit the connection string (search for the server/DB/cube names), and re-open and save the XML back as .XLS.|||

Thanks to all.

I checked out the XML and the VBA solutions - at least I have options now.