question with sql 2005 named instance port assignment

hi
i have a sql 2000 default instance and i just installed a sql 2005
named instance. now i want to assign a specific port to my sql 2005
instance
i am trying to figure out the IPAll section of the TCP/IP Properties --
> IP Addresses tab of the sql server configuration manager.
my entries look like this
IP1 has the following settings (i've masked out the IP addresses with
x's)
Active: Yes
Enabled: No
IP Address: xxx.xxx.x.xxx
TCP Dynamic Ports: 0
TCP Port:
IP2 has the following settings:
Active: Yes
Enabled: No
IP Address: xxx.x.x.x
TCP Dynamic Ports: 0
TCP Port:
IPAll
TCP Dynamic Ports: 1500
TCP Port:
it looks like sql server 2005 named instance now listens on port
1500. I then read this in the books on line:
To configure a static port, leave the TCP Dynamic Ports box blank and
provide an available port number in the TCP Port box
TCP Dynamic Ports
Blank, if dynamic ports are not enabled. To use dynamic ports, set to
0.
For IPAll, displays the port number of the dynamic port used.
which is correct?
Delete the TCP Dynamic Ports value and supply a value for TCP Port and
(re)start SQL Service. Check the SQL Errorlog to make sure it's picked up
the change.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Derek" <gepetto_2000@.yahoo.com> wrote in message
news:1173191178.548086.32920@.c51g2000cwc.googlegro ups.com...
> hi
> i have a sql 2000 default instance and i just installed a sql 2005
> named instance. now i want to assign a specific port to my sql 2005
> instance
> i am trying to figure out the IPAll section of the TCP/IP Properties --
> my entries look like this
> IP1 has the following settings (i've masked out the IP addresses with
> x's)
> Active: Yes
> Enabled: No
> IP Address: xxx.xxx.x.xxx
> TCP Dynamic Ports: 0
> TCP Port:
> IP2 has the following settings:
> Active: Yes
> Enabled: No
> IP Address: xxx.x.x.x
> TCP Dynamic Ports: 0
> TCP Port:
> IPAll
> TCP Dynamic Ports: 1500
> TCP Port:
> it looks like sql server 2005 named instance now listens on port
> 1500. I then read this in the books on line:
> To configure a static port, leave the TCP Dynamic Ports box blank and
> provide an available port number in the TCP Port box
> TCP Dynamic Ports
> Blank, if dynamic ports are not enabled. To use dynamic ports, set to
> 0.
> For IPAll, displays the port number of the dynamic port used.
>
> which is correct?
>
|||On Mar 6, 4:07 pm, "Jasper Smith" <jasper_smi...@.hotmail.com> wrote:[vbcol=seagreen]
> Delete the TCP Dynamic Ports value and supply a value for TCP Port and
> (re)start SQL Service. Check the SQL Errorlog to make sure it's picked up
> the change.
> --
> HTH,
> Jasper Smith (SQL Server MVP)http://www.sqldbatips.com
> "Derek" <gepetto_2...@.yahoo.com> wrote in message
> news:1173191178.548086.32920@.c51g2000cwc.googlegro ups.com...
>
>
>
>
>
>
>
>
>
Hi Jasper, thank you. is there a reason why to choose this method
over the other?

question with sql 2005 named instance port assignment

hi
i have a sql 2000 default instance and i just installed a sql 2005
named instance. now i want to assign a specific port to my sql 2005
instance
i am trying to figure out the IPAll section of the TCP/IP Properties --
> IP Addresses tab of the sql server configuration manager.
my entries look like this
IP1 has the following settings (i've masked out the IP addresses with
x's)
Active: Yes
Enabled: No
IP Address: xxx.xxx.x.xxx
TCP Dynamic Ports: 0
TCP Port:
IP2 has the following settings:
Active: Yes
Enabled: No
IP Address: xxx.x.x.x
TCP Dynamic Ports: 0
TCP Port:
IPAll
TCP Dynamic Ports: 1500
TCP Port:
it looks like sql server 2005 named instance now listens on port
1500. I then read this in the books on line:
To configure a static port, leave the TCP Dynamic Ports box blank and
provide an available port number in the TCP Port box
TCP Dynamic Ports
Blank, if dynamic ports are not enabled. To use dynamic ports, set to
0.
For IPAll, displays the port number of the dynamic port used.
which is correct?Delete the TCP Dynamic Ports value and supply a value for TCP Port and
(re)start SQL Service. Check the SQL Errorlog to make sure it's picked up
the change.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Derek" <gepetto_2000@.yahoo.com> wrote in message
news:1173191178.548086.32920@.c51g2000cwc.googlegroups.com...
> hi
> i have a sql 2000 default instance and i just installed a sql 2005
> named instance. now i want to assign a specific port to my sql 2005
> instance
> i am trying to figure out the IPAll section of the TCP/IP Properties --
> my entries look like this
> IP1 has the following settings (i've masked out the IP addresses with
> x's)
> Active: Yes
> Enabled: No
> IP Address: xxx.xxx.x.xxx
> TCP Dynamic Ports: 0
> TCP Port:
> IP2 has the following settings:
> Active: Yes
> Enabled: No
> IP Address: xxx.x.x.x
> TCP Dynamic Ports: 0
> TCP Port:
> IPAll
> TCP Dynamic Ports: 1500
> TCP Port:
> it looks like sql server 2005 named instance now listens on port
> 1500. I then read this in the books on line:
> To configure a static port, leave the TCP Dynamic Ports box blank and
> provide an available port number in the TCP Port box
> TCP Dynamic Ports
> Blank, if dynamic ports are not enabled. To use dynamic ports, set to
> 0.
> For IPAll, displays the port number of the dynamic port used.
>
> which is correct?
>|||On Mar 6, 4:07 pm, "Jasper Smith" <jasper_smi...@.hotmail.com> wrote:[vbcol=seagreen]
> Delete the TCP Dynamic Ports value and supply a value for TCP Port and
> (re)start SQL Service. Check the SQL Errorlog to make sure it's picked up
> the change.
> --
> HTH,
> Jasper Smith (SQL Server MVP)http://www.sqldbatips.com
> "Derek" <gepetto_2...@.yahoo.com> wrote in message
> news:1173191178.548086.32920@.c51g2000cwc.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
Hi Jasper, thank you. is there a reason why to choose this method
over the other?

question with sql 2005 named instance port assignment

hi
i have a sql 2000 default instance and i just installed a sql 2005
named instance. now i want to assign a specific port to my sql 2005
instance
i am trying to figure out the IPAll section of the TCP/IP Properties --
> IP Addresses tab of the sql server configuration manager.
my entries look like this
IP1 has the following settings (i've masked out the IP addresses with
x's)
Active: Yes
Enabled: No
IP Address: xxx.xxx.x.xxx
TCP Dynamic Ports: 0
TCP Port:
IP2 has the following settings:
Active: Yes
Enabled: No
IP Address: xxx.x.x.x
TCP Dynamic Ports: 0
TCP Port:
IPAll
TCP Dynamic Ports: 1500
TCP Port:
it looks like sql server 2005 named instance now listens on port
1500. I then read this in the books on line:
To configure a static port, leave the TCP Dynamic Ports box blank and
provide an available port number in the TCP Port box
TCP Dynamic Ports
Blank, if dynamic ports are not enabled. To use dynamic ports, set to
0.
For IPAll, displays the port number of the dynamic port used.
which is correct?Delete the TCP Dynamic Ports value and supply a value for TCP Port and
(re)start SQL Service. Check the SQL Errorlog to make sure it's picked up
the change.
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Derek" <gepetto_2000@.yahoo.com> wrote in message
news:1173191178.548086.32920@.c51g2000cwc.googlegroups.com...
> hi
> i have a sql 2000 default instance and i just installed a sql 2005
> named instance. now i want to assign a specific port to my sql 2005
> instance
> i am trying to figure out the IPAll section of the TCP/IP Properties --
>> IP Addresses tab of the sql server configuration manager.
> my entries look like this
> IP1 has the following settings (i've masked out the IP addresses with
> x's)
> Active: Yes
> Enabled: No
> IP Address: xxx.xxx.x.xxx
> TCP Dynamic Ports: 0
> TCP Port:
> IP2 has the following settings:
> Active: Yes
> Enabled: No
> IP Address: xxx.x.x.x
> TCP Dynamic Ports: 0
> TCP Port:
> IPAll
> TCP Dynamic Ports: 1500
> TCP Port:
> it looks like sql server 2005 named instance now listens on port
> 1500. I then read this in the books on line:
> To configure a static port, leave the TCP Dynamic Ports box blank and
> provide an available port number in the TCP Port box
> TCP Dynamic Ports
> Blank, if dynamic ports are not enabled. To use dynamic ports, set to
> 0.
> For IPAll, displays the port number of the dynamic port used.
>
> which is correct?
>|||On Mar 6, 4:07 pm, "Jasper Smith" <jasper_smi...@.hotmail.com> wrote:
> Delete the TCP Dynamic Ports value and supply a value for TCP Port and
> (re)start SQL Service. Check the SQL Errorlog to make sure it's picked up
> the change.
> --
> HTH,
> Jasper Smith (SQL Server MVP)http://www.sqldbatips.com
> "Derek" <gepetto_2...@.yahoo.com> wrote in message
> news:1173191178.548086.32920@.c51g2000cwc.googlegroups.com...
>
> > hi
> > i have a sql 2000 default instance and i just installed a sql 2005
> > named instance. now i want to assign a specific port to my sql 2005
> > instance
> > i am trying to figure out the IPAll section of the TCP/IP Properties --
> >> IP Addresses tab of the sql server configuration manager.
> > my entries look like this
> > IP1 has the following settings (i've masked out the IP addresses with
> > x's)
> > Active: Yes
> > Enabled: No
> > IP Address: xxx.xxx.x.xxx
> > TCP Dynamic Ports: 0
> > TCP Port:
> > IP2 has the following settings:
> > Active: Yes
> > Enabled: No
> > IP Address: xxx.x.x.x
> > TCP Dynamic Ports: 0
> > TCP Port:
> > IPAll
> > TCP Dynamic Ports: 1500
> > TCP Port:
> > it looks like sql server 2005 named instance now listens on port
> > 1500. I then read this in the books on line:
> > To configure a static port, leave the TCP Dynamic Ports box blank and
> > provide an available port number in the TCP Port box
> > TCP Dynamic Ports
> > Blank, if dynamic ports are not enabled. To use dynamic ports, set to
> > 0.
> > For IPAll, displays the port number of the dynamic port used.
> > which is correct?
Hi Jasper, thank you. is there a reason why to choose this method
over the other?sql

Question with Email Subscription

Hi,

I have installed Visual Studio 2005 Reporting Services and try to make some subscription. The strange thing is that after I clicked "New Subscription" in the Reporting Manager and Report Delivery Option only shows the option "Report Server File Share" .I cannot select email subscription. Did I miss something out here?

THanks for the help

Josh

Hi Josh,

sure that you have configured the mail-settings using the RS-Frontend?

cheers
markus

Question Regarding Job Schedules

Hi

My instance of SQL Server 2005 is installed on a Win Server 2003 box. I am trying to schedule an SSIS job. I have created a proxy using my NTLogin as Credential. When I run the job, it returns with the following error:

Message
[298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'XXXXXXXX', error code 0x54b. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
where XXXXXXXX is my Domain\NTLogin

I access the Server box and discovered that the Windows Task Scheduling Service is not running. Does SQL Server Scheduling depend on this?

I'm quite sure that SQL Server Agent has nothing to do with Windows Task Scheduler. However I have no idea about your error...|||

Hi

Thanks for the reply. Here is what I am doing to test this:

I have a Test SSIS package that has a singe Execute SQL Task which connects to a SQL 2000 db and makes an insert into a table. This works fine when I run it through the VS Studio IDE.

After this, I registered the Package with the Integration Services and when I execute the package by right clicking and selecting the Execute Package option it executes again.

I restarted the SQL Server Agent to run under my NT account. Now, the first piece of the puzzle is that when I try to create a new credential with my NT Account, it throws an error saying that :
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

An error occurred during encryption. (Microsoft SQL Server, Error: 15467)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=09.00.1116&EvtSrc=MSSQLServer&EvtID=15467&LinkId=20476
I ran a Create Credential Script to insert the credential and created a proxy using that. Then I created a job with a new SSIS step that runs under the proxy. Now when I try to run the job it fails to execute with the following two entires in the log:

Message
[298] SQLServer Error: 3621, The statement has been terminated. [SQLSTATE 01000] (ConnIsLoginSysAdmin)

Message
[298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'CSFB\jgattani', error code 0x5. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
Can I provide you with any more information that might assist you in trying to figure out whats going on?

Regards
Jay

|||Hi

I had posted my question a couple of days back and did not get a response. Is the question vague or does it need more information. Please let me know if I could provide more details in understanding the problem I am facing.

We are stuck at this point and would like to get this resolved and use the SQL Server 2005 instead of good old SQL Server 2000.

Thanks
|||Hi,

just an idea... Changing the service login wasn't supported in older builds of SQL Server 2005. Perhaps there is still a problem with that... I would not change the service's credentials...

Encryption problem can also occur when you store "save" data (like database logins) within the package and encrypt the package with the user key. You should use a password for the package instead and use it when you run the package...

Again: just an idea...|||Hi

Thanks for the response. I think that the root cause of the issue is my inability to create the credential. If I have the SQL Server Agent running under my NT Acount and then the job Steps running under the "SQL Agent Service Account" and my nt account as the owner of the job, everything works perfect.

sql

Question regarding Instance

If I do a default SQL Server installation, and then find that I'd like to
have several instances installed after all, will I be stuck?
Thanks,
- Joe Geretz -
Hi,
When ever you do a new SQL server Installation after a default installation,
The Installation program will identify the old instance
and will create a NEW NAMED INSTANCE automatically.
All the subsequent installation after the default installation the server
name will be "MACHINENAME\<NAME YOU GIVE>"
So no worry, you will never get struck.
Thanks
Hari
MCDBA
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:#lMllL4FEHA.1396@.TK2MSFTNGP11.phx.gbl...
> If I do a default SQL Server installation, and then find that I'd like to
> have several instances installed after all, will I be stuck?
> Thanks,
> - Joe Geretz -
>

Question regarding Instance

If I do a default SQL Server installation, and then find that I'd like to
have several instances installed after all, will I be stuck?
Thanks,
- Joe Geretz -Hi,
When ever you do a new SQL server Installation after a default installation,
The Installation program will identify the old instance
and will create a NEW NAMED INSTANCE automatically.
All the subsequent installation after the default installation the server
name will be "MACHINENAME\<NAME YOU GIVE>"
So no worry, you will never get struck.
Thanks
Hari
MCDBA
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:#lMllL4FEHA.1396@.TK2MSFTNGP11.phx.gbl...
> If I do a default SQL Server installation, and then find that I'd like to
> have several instances installed after all, will I be stuck?
> Thanks,
> - Joe Geretz -
>

Question on Installing Named Instance

Does anyone know if a named instance can be installed without having to
reboot the server right away? Would like to install it now but schedule a
reboot when it would be more convienent.
ThanksForgot to mention, will be installing SQL Server 2000 Standard Edition with
SP3a on Windows Server 2003 Standard edition.
"MACason" wrote:
> Does anyone know if a named instance can be installed without having to
> reboot the server right away? Would like to install it now but schedule a
> reboot when it would be more convienent.
> Thanks

Question on Installing Named Instance

Does anyone know if a named instance can be installed without having to
reboot the server right away? Would like to install it now but schedule a
reboot when it would be more convienent.
ThanksForgot to mention, will be installing SQL Server 2000 Standard Edition with
SP3a on Windows Server 2003 Standard edition.
"MACason" wrote:

> Does anyone know if a named instance can be installed without having to
> reboot the server right away? Would like to install it now but schedule a
> reboot when it would be more convienent.
> Thanks

Question on connecting to Oracle

Hello all,

I'm using a OLE DB Connection Manager to Oracle (I've installed the client) and using Oracle Native OLE DB Provider.

The problem was the view In Oracle I'm returning a column that uses a fuction on a numeric field in the the select part of the statement, and by this, the Precition is not displayed when I'm trying to look at the data types of the fields in the view.

The Precision is shown as 0 in the external field part of the component.

I have read the previous post concerning the problem connecting to Oracle Using the Oracle provider, and solved it by using the Microsoft provider, but still I have a question about the oracle provider:

I changed the precision on the External column (from 0 to 15) and also an the Output column, but now I'm getting an error on about error output not matching:

Error 6 Validation error. Data Flow Task: OLE DB Source 1 [8970]: The output column "UPDATE_TIME_NUM" (9333) on the non-error output has no corresponding output column on the error output. Dim_registered_user.dtsx 0 0

I cannot change the error output datatype, cannot delete it or do anything at all.

Is there a way to fix the problem?

This may or may not help you, but I've switch my Oracle connections over to the ADO.NET provider for Oracle. The only side effect is that the resulting data flow is Unicode by default...so all your string data is DT_WSTR instead of DT_STR. You can change that, but it's a manual operation go through all the External Columns and change their type.

I had some weird data issues like you describe above and after poking around the internet came across this article by Donald Farmer (http://www.sqljunkies.com/WebLog/donald_farmer/archive/2005/03/13/8819.aspx). That article explains alot about SSIS Oracle issues.

|||The problem with ADO.NET provider is that it doesn't allow the use of variables as a source SQL