Changing the SQL Server Name

I have SQL 2000 std installed on a win2000 box with latest svc packs and box
was originally designated with a development name which SQL also inherited.
Now the server box name has been changed but the server still indicates the
old box name.
What are the implications if I leave as is and how difficult is it to change
it?
Can I just update sysserver in the master db through sp_addserver or are
there other steps?
Any registry impaacts? I noticed that some maintenance jobs I had defined
within SQL became orphaned after making changes to the maintenance plan and
had to be re-defined.
thks
Did SQL Server start with an error along the lines of "has beeen tampered
with?"
You will have to pop in the SQL Server CD and choose install. This step
will not actually install SQL Server...it will just fix up some registry
entries.
In order to fix the jobs you will have to update the value
as stored within the originating_server column.
SELECT originating_server FROM msdb..sysjobs
If @.@.servername is incorrect you will have to drop and add server using the
stored procedures listed below:
sp_dropserver 'OldServerName'
go
sp_addserver 'NewServerName', 'local'
Keith
"tom frost" <tomfrost@.discussions.microsoft.com> wrote in message
news:82A1DBBE-53DA-4349-A122-32B5BD6571E6@.microsoft.com...
> I have SQL 2000 std installed on a win2000 box with latest svc packs and
box
> was originally designated with a development name which SQL also
inherited.
> Now the server box name has been changed but the server still indicates
the
> old box name.
> What are the implications if I leave as is and how difficult is it to
change
> it?
> Can I just update sysserver in the master db through sp_addserver or are
> there other steps?
> Any registry impaacts? I noticed that some maintenance jobs I had defined
> within SQL became orphaned after making changes to the maintenance plan
and
> had to be re-defined.
> thks
>
|||Some info here: http://www.karaszi.com/SQLServer/inf...erver_name.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tom frost" <tomfrost@.discussions.microsoft.com> wrote in message
news:82A1DBBE-53DA-4349-A122-32B5BD6571E6@.microsoft.com...
>I have SQL 2000 std installed on a win2000 box with latest svc packs and box
> was originally designated with a development name which SQL also inherited.
> Now the server box name has been changed but the server still indicates the
> old box name.
> What are the implications if I leave as is and how difficult is it to change
> it?
> Can I just update sysserver in the master db through sp_addserver or are
> there other steps?
> Any registry impaacts? I noticed that some maintenance jobs I had defined
> within SQL became orphaned after making changes to the maintenance plan and
> had to be re-defined.
> thks
>

Changing the SQL Server Name

I have SQL 2000 std installed on a win2000 box with latest svc packs and box
was originally designated with a development name which SQL also inherited.
Now the server box name has been changed but the server still indicates the
old box name.
What are the implications if I leave as is and how difficult is it to change
it?
Can I just update sysserver in the master db through sp_addserver or are
there other steps?
Any registry impaacts? I noticed that some maintenance jobs I had defined
within SQL became orphaned after making changes to the maintenance plan and
had to be re-defined.
thksDid SQL Server start with an error along the lines of "has beeen tampered
with?"
You will have to pop in the SQL Server CD and choose install. This step
will not actually install SQL Server...it will just fix up some registry
entries.
In order to fix the jobs you will have to update the value
as stored within the originating_server column.
SELECT originating_server FROM msdb..sysjobs
If @.@.servername is incorrect you will have to drop and add server using the
stored procedures listed below:
sp_dropserver 'OldServerName'
go
sp_addserver 'NewServerName', 'local'
Keith
"tom frost" <tomfrost@.discussions.microsoft.com> wrote in message
news:82A1DBBE-53DA-4349-A122-32B5BD6571E6@.microsoft.com...
> I have SQL 2000 std installed on a win2000 box with latest svc packs and
box
> was originally designated with a development name which SQL also
inherited.
> Now the server box name has been changed but the server still indicates
the
> old box name.
> What are the implications if I leave as is and how difficult is it to
change
> it?
> Can I just update sysserver in the master db through sp_addserver or are
> there other steps?
> Any registry impaacts? I noticed that some maintenance jobs I had defined
> within SQL became orphaned after making changes to the maintenance plan
and
> had to be re-defined.
> thks
>|||Some info here: http://www.karaszi.com/SQLServer/in...server_name.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tom frost" <tomfrost@.discussions.microsoft.com> wrote in message
news:82A1DBBE-53DA-4349-A122-32B5BD6571E6@.microsoft.com...
>I have SQL 2000 std installed on a win2000 box with latest svc packs and bo
x
> was originally designated with a development name which SQL also inherited
.
> Now the server box name has been changed but the server still indicates th
e
> old box name.
> What are the implications if I leave as is and how difficult is it to chan
ge
> it?
> Can I just update sysserver in the master db through sp_addserver or are
> there other steps?
> Any registry impaacts? I noticed that some maintenance jobs I had defined
> within SQL became orphaned after making changes to the maintenance plan an
d
> had to be re-defined.
> thks
>

Changing the SQL Server Name

I have SQL 2000 std installed on a win2000 box with latest svc packs and box
was originally designated with a development name which SQL also inherited.
Now the server box name has been changed but the server still indicates the
old box name.
What are the implications if I leave as is and how difficult is it to change
it?
Can I just update sysserver in the master db through sp_addserver or are
there other steps?
Any registry impaacts? I noticed that some maintenance jobs I had defined
within SQL became orphaned after making changes to the maintenance plan and
had to be re-defined.
thksDid SQL Server start with an error along the lines of "has beeen tampered
with?"
You will have to pop in the SQL Server CD and choose install. This step
will not actually install SQL Server...it will just fix up some registry
entries.
In order to fix the jobs you will have to update the value
as stored within the originating_server column.
SELECT originating_server FROM msdb..sysjobs
If @.@.servername is incorrect you will have to drop and add server using the
stored procedures listed below:
sp_dropserver 'OldServerName'
go
sp_addserver 'NewServerName', 'local'
Keith
"tom frost" <tomfrost@.discussions.microsoft.com> wrote in message
news:82A1DBBE-53DA-4349-A122-32B5BD6571E6@.microsoft.com...
> I have SQL 2000 std installed on a win2000 box with latest svc packs and
box
> was originally designated with a development name which SQL also
inherited.
> Now the server box name has been changed but the server still indicates
the
> old box name.
> What are the implications if I leave as is and how difficult is it to
change
> it?
> Can I just update sysserver in the master db through sp_addserver or are
> there other steps?
> Any registry impaacts? I noticed that some maintenance jobs I had defined
> within SQL became orphaned after making changes to the maintenance plan
and
> had to be re-defined.
> thks
>|||Some info here: http://www.karaszi.com/SQLServer/info_change_server_name.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tom frost" <tomfrost@.discussions.microsoft.com> wrote in message
news:82A1DBBE-53DA-4349-A122-32B5BD6571E6@.microsoft.com...
>I have SQL 2000 std installed on a win2000 box with latest svc packs and box
> was originally designated with a development name which SQL also inherited.
> Now the server box name has been changed but the server still indicates the
> old box name.
> What are the implications if I leave as is and how difficult is it to change
> it?
> Can I just update sysserver in the master db through sp_addserver or are
> there other steps?
> Any registry impaacts? I noticed that some maintenance jobs I had defined
> within SQL became orphaned after making changes to the maintenance plan and
> had to be re-defined.
> thks
>

Changing the server name and IP address

A server with SQL 2000 installed is moving to a new physical location. Our
company standards mean that the name of the server and the IP address of the
server will change also. This SQL instance name is the same as the server
name. Will I have to uninstall and reinstall SQL Server (and recover the
databases) when this move is made so that the instance name equals the
server name? Is there a stored procedure that changes the instance name to
the server name? Thank you.[posted and mailed, please reply in news]

steven virnig (pezguy@.mn.rr.com) writes:
> A server with SQL 2000 installed is moving to a new physical location.
> Our company standards mean that the name of the server and the IP
> address of the server will change also. This SQL instance name is the
> same as the server name. Will I have to uninstall and reinstall SQL
> Server (and recover the databases) when this move is made so that the
> instance name equals the server name? Is there a stored procedure that
> changes the instance name to the server name? Thank you.

If memory serves, it's as simple as:

exec sp_dropserver @.@.servername
exec sp_addserver 'NewName', 'local'

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Changing the Server Name

Hi Guys,

I am using a MS SQL Server 2005 express edition for storing the meta-files of a b2b application.

The SQL 2005 is installed localy. Now I must change my server (machine) name cause a internal application need this action.

What is the best practice to do this?

I've got 2 databases and a lot of tabeles within the installed sql server.

I am happy for all tutorials or instructions to handle this proposal nearyl perfectyl.

Many Regards

Check out http://msdn2.microsoft.com/en-us/library/ms143799.aspx

Mike

Changing the port that Reporting Services 2000 is running on after activation.

Hi,

I have just installed reporting services 2000 on a non-standard port and have used the rsactivate tool to activate it.

I then installed Microsoft CRM which seems not to like the non-standard port for reporting services, how do I go about deactivating the webservice so I can change the port and then activate it again?

Thanks

I am not too familiar with RS2000, but I think all you have to do is to change the port in IIS, then change the url in the report server and report manager config files. You do not need to run reactivate again.

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 TCP/IP IP addresses under Protocols for SQLExpress

SQL Server Configuraton Manager,

Protocols for sqlexpress,

TCP/IP

When I initially installed SQL Server 2005 Express, it automatically picked up all the IP addresses (60 of them) I had on the server, and filled them in under TCP/IP - IP adresses Tab.

I recently took those IPs off the server and replaced them with 30 new IP addresses. Is there a way to have SQL Server 2005 Express read the Network configuration on the server and take the old IPs off the TCP/IP - IP addresses tab and put the new one's there?

Thanks,

Bill

You have to reinstall your SQL Server to pick the new IP addresses. Are you listening on each IP address seperately or listening on IPALL? Those IPs does not matter if your server listens on IPALL.|||

Thanks a lot Xinwei, I reinstalled SQL Server and everything went fine.

Bill

Changing SQL2K port no. on Windows 2003

Hi,
I installed SQL2K SP3a on Windows 2003 server. During the install I changed
the SQL port number 1433 to 14330. Everything finished successfully.
But when the clients are trying to build the ODBC to connect to SQL server,
they cann't connect if they leave the Dynamically determine port check box
checked under the client configuration, but once we unchecked it and put the
port no. 14330 it will connect.
Shouldn't the Dynamically determine port no. option let the client get the
port no from the server? Or is there any security lockdown on Win2003
preventing the server to publish the port no.?
Thanks
AyadThey dynamically determine port only works for named instances. A default
instance will always default to 1433. You can change this but then the
client will always attempt to use port 1433o for every SQL Server it tries
to connect to.
If you choose to use a non-standard port for the default instance, you will
have to have each client change to connect using that port.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Changing SQL2K port no. on Windows 2003

Hi,
I installed SQL2K SP3a on Windows 2003 server. During the install I changed
the SQL port number 1433 to 14330. Everything finished successfully.
But when the clients are trying to build the ODBC to connect to SQL server,
they cann't connect if they leave the Dynamically determine port check box
checked under the client configuration, but once we unchecked it and put the
port no. 14330 it will connect.
Shouldn't the Dynamically determine port no. option let the client get the
port no from the server? Or is there any security lockdown on Win2003
preventing the server to publish the port no.?
Thanks
Ayad
They dynamically determine port only works for named instances. A default
instance will always default to 1433. You can change this but then the
client will always attempt to use port 1433o for every SQL Server it tries
to connect to.
If you choose to use a non-standard port for the default instance, you will
have to have each client change to connect using that port.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Changing SQL Service Account

Has anyone ever converted from running SQL Server under the Local System account to running under a Domain User account?

I have often installed SQL using a Domain User account, but I am inheriting a couple of SQL Servers that were set up to run under Local System. I have never had to convert "on the fly" before.

If you have any input or insights, I would be grateful.

Regards,

hmscottI have done this several times, and have had no issues (knock on wood). Make sure that the Domain account has sufficient permissions on the local machine, and you shold be ok.|||Is "Power User" sufficient, or do I have to grant local admin to the account?

Regards,

hmscott|||If any of the jobs on the local involves deleting/creating files then better to give admin and its no harm is allocating this privilege for SQL service accounts.|||Originally posted by Satya
If any of the jobs on the local involves deleting/creating files then better to give admin and its no harm is allocating this privilege for SQL service accounts.

8-O

That's wrong! Basic tenet of security is least privileges of course. Required permissions are outlined in this article:

http://support.microsoft.com/?id=283811

Quote from the article: "...running SQL Server under such high user rights is not recommended."|||No such threat at our end, so far so good.
It purely depend how you secure the network and connections.|||Due to the nature of what our SQL Servers do, we make most of the machines run as LocalSystem. We basically make each machine run with the lowest level of privledge that it needs to do its job.

We do have one machine that is our interface/automation server that does all kinds of things like copying data from one server to another, runs DTS packages that affect multiple machines, etc that has privleges similar to a Domain Admin (because it must touch nearly every machine in the Data Center). Only the Domain Admins and a few select IT staff can even see this box, much less touch it!

-PatP

Changing SQL Server Logon Config.

Hello,

When I installed MS SQL Server 2000 on our client's computer I installed it under a local account using Windows Authentication as the server logon and am now accessing it using a network login (remoting into the same box). The result is that if I change the password for the local Windows account and reboot the server, SQL server cannot be restarted; I get an error:

A connection could not be established to (LOCAL)

Reason: SQL SERVER does not exist or access denied.
ConnectionOpen (Connect())..

Please verify SQL Server is running and check your SQL Server registration properties and try again.

I've changed the registration properties to use SQL Server Authenticaiton instead of Windows to no avail. Whichever connection option I use the server will not start unless I change the local account's password back to what it was when I installed. This works, but our client wants to have that local account's password changed for security/peace of mind. Any help or advice would be appreciated here :)

Thank youI always use a dedicated domain account for many reasons. exchange integration, access to file servers etc... and you should set that domain account to never have a password that never expires.|||... and you should set that domain account to never have a password that never expires.I suspect that you might have "over nevered" here. I would want the domain account to have a password that never expired (or I'd create a single application to change the service passwords and the AD password, all in one swell foop).

-PatP|||I'm afraid I don't quite get what you're saying here. The domain login is to stay the same in this case. It is the local account that the sql server installation is tied to that must have its password altered. The problem is that upon altering the password in Windows, SQL server will not start up the instance, giving that invalid logon message. Sorry to say that I'm still at a loss here. :confused:

Changing SQL 2000 Server colation - urgent!

Hi,
I've installed the SQL 2000 Server with SQL_Latin1_General_CP1_CI_AS
collation.
I've restored databases from different server with Hebrew_CI_AS collation,
and there is a oproblem to see Hebrew characters in the databases.
Can i change the collation for entire server (preferably without reboot) in
order to see characters well in all DBs and how exactly can i do that?
Or i need to make the collation change for every DB separately, and how it
can be done?
Thanks,
BarakBarak Turovsky wrote:
> Hi,
> I've installed the SQL 2000 Server with SQL_Latin1_General_CP1_CI_AS
> collation.
> I've restored databases from different server with Hebrew_CI_AS
> collation, and there is a oproblem to see Hebrew characters in the
> databases.
> Can i change the collation for entire server (preferably without
> reboot) in order to see characters well in all DBs and how exactly
> can i do that?
> Or i need to make the collation change for every DB separately, and
> how it can be done?
See "How to transfer a database from one collation to another collation in
SQL Server"
http://support.microsoft.com/?kbid=325335#8
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Shalom,Barak
Try to use ALTER TABLE command with the option to change COLLATION.
Did you try to change these column to NVARCHAR datatype to see if you can
see the data?
Did you have your Server as 'Hebrew Enabled'?
You may encounter some problems when you try to JOIN the columns with
differnt collations.
"Barak Turovsky" <baraktur@.mail.ru> wrote in message
news:#1lWDgLEEHA.2576@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I've installed the SQL 2000 Server with SQL_Latin1_General_CP1_CI_AS
> collation.
> I've restored databases from different server with Hebrew_CI_AS collation,
> and there is a oproblem to see Hebrew characters in the databases.
> Can i change the collation for entire server (preferably without reboot)
in
> order to see characters well in all DBs and how exactly can i do that?
> Or i need to make the collation change for every DB separately, and how it
> can be done?
> Thanks,
> Barak
>

Changing Server Name

I change the server name where SQL was original installed but the result of
'select @.@.servername' is still returning the name of the original server. Hod
dow I correct this?
Thanks
check out sp_addserver in BOL. That should help you.
"Emma" <Emma@.discussions.microsoft.com> wrote in message
news:362A7AD0-8C08-4BB7-B27C-4B9F4B3E1F3F@.microsoft.com...
> I change the server name where SQL was original installed but the result
of
> 'select @.@.servername' is still returning the name of the original server.
Hod
> dow I correct this?
> Thanks
|||Do:
EXEC sp_dropserver 'oldname'
GO
EXEC sp_addserver 'newname', 'local', null
GO
Restart the server & @.@.SERVERNAME should return 'newname'. See the topic
"Renaming a Server" in SQL Server Books Online.
Anith

Changing Server Name

I change the server name where SQL was original installed but the result of
'select @.@.servername' is still returning the name of the original server. Hod
dow I correct this?
Thankscheck out sp_addserver in BOL. That should help you.
"Emma" <Emma@.discussions.microsoft.com> wrote in message
news:362A7AD0-8C08-4BB7-B27C-4B9F4B3E1F3F@.microsoft.com...
> I change the server name where SQL was original installed but the result
of
> 'select @.@.servername' is still returning the name of the original server.
Hod
> dow I correct this?
> Thanks|||Do:
EXEC sp_dropserver 'oldname'
GO
EXEC sp_addserver 'newname', 'local', null
GO
Restart the server & @.@.SERVERNAME should return 'newname'. See the topic
"Renaming a Server" in SQL Server Books Online.
--
Anith

changing server name

i Changed the name of my server with SQL Server
previously installed. How do I make SQL Server to reflect
the new name of the server.
Thanks!see "sp_dropserver" & "sp_addserver" from BOL. note the @.local param.
hth,
--oliver.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.
"mar" <mar10c@.cox.net> wrote in message
news:2da201c373cb$ac8f3e90$a401280a@.phx.gbl...
> i Changed the name of my server with SQL Server
> previously installed. How do I make SQL Server to reflect
> the new name of the server.
> Thanks!

Changing Server Name

I change the server name where SQL was original installed but the result of
'select @.@.servername' is still returning the name of the original server. Ho
d
dow I correct this?
Thankscheck out sp_addserver in BOL. That should help you.
"Emma" <Emma@.discussions.microsoft.com> wrote in message
news:362A7AD0-8C08-4BB7-B27C-4B9F4B3E1F3F@.microsoft.com...
> I change the server name where SQL was original installed but the result
of
> 'select @.@.servername' is still returning the name of the original server.
Hod
> dow I correct this?
> Thanks|||Do:
EXEC sp_dropserver 'oldname'
GO
EXEC sp_addserver 'newname', 'local', null
GO
Restart the server & @.@.SERVERNAME should return 'newname'. See the topic
"Renaming a Server" in SQL Server Books Online.
Anith

changing security model

how do i reconfigure an installed sql server to use standard security
(sql login/pwd) instead of only accepting trusted connections
(requiring windows login/pwd)
thanks,
jasonnever mind, i found it in the enterprise manager server properties
security tab.

Changing Query behavior based on local vs. remote context?

Hello,
I'm not sure if this is the right ng for this question, but here goes...
I just installed a product that uses SQL Express. When I run queries
against the installed instance I get different behavior depending on if the
query is run from Management Studio locally or from SSMS running on a remote
machine. In both cases the user is a domain admin and the query is
identical. The local query returns 1000+ rows, the remote query returns
ZERO. If the query returns less than say 100 rows, there is no difference
between remote and local.
Has anyone else seen this or use use/implemented it themselves?
I'm curious; how this behavior is defined/implemented? Is it database or
instance specific? It seems to be instance-wide in my case but I'm not sure
yet. I've been digging around in SSMS but haven't found any settings or
properties that seem to apply to this, so any sugestions on where to look
would be appreciated.
Thanks!
KeithMy guess would be that there is a problem on the remote machine. Have you
tried a 2nd remote query?
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Keith" <keith@.alh.com> wrote in message
news:%23Ej27KC4HHA.1824@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I'm not sure if this is the right ng for this question, but here goes...
> I just installed a product that uses SQL Express. When I run queries
> against the installed instance I get different behavior depending on if
> the query is run from Management Studio locally or from SSMS running on a
> remote machine. In both cases the user is a domain admin and the query is
> identical. The local query returns 1000+ rows, the remote query returns
> ZERO. If the query returns less than say 100 rows, there is no difference
> between remote and local.
> Has anyone else seen this or use use/implemented it themselves?
> I'm curious; how this behavior is defined/implemented? Is it database or
> instance specific? It seems to be instance-wide in my case but I'm not
> sure yet. I've been digging around in SSMS but haven't found any
> settings or properties that seem to apply to this, so any sugestions on
> where to look would be appreciated.
> Thanks!
> Keith
>|||Yes...a dozen or so actually. And I've tried them on different remote
machines as well with SSMS and SSMSe, in addition to using queries from the
query window and "Open Table/View" from the Object Explorer.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OTMOdYC4HHA.2312@.TK2MSFTNGP06.phx.gbl...
> My guess would be that there is a problem on the remote machine. Have you
> tried a 2nd remote query?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Keith" <keith@.alh.com> wrote in message
> news:%23Ej27KC4HHA.1824@.TK2MSFTNGP04.phx.gbl...
>|||Sounds like larger network packets are getting dropped. Some bridges that
do not do packet splitting can cause this.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Keith" <keith@.alh.com> wrote in message
news:%23Ej27KC4HHA.1824@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I'm not sure if this is the right ng for this question, but here goes...
> I just installed a product that uses SQL Express. When I run queries
> against the installed instance I get different behavior depending on if
> the query is run from Management Studio locally or from SSMS running on a
> remote machine. In both cases the user is a domain admin and the query is
> identical. The local query returns 1000+ rows, the remote query returns
> ZERO. If the query returns less than say 100 rows, there is no difference
> between remote and local.
> Has anyone else seen this or use use/implemented it themselves?
> I'm curious; how this behavior is defined/implemented? Is it database or
> instance specific? It seems to be instance-wide in my case but I'm not
> sure yet. I've been digging around in SSMS but haven't found any
> settings or properties that seem to apply to this, so any sugestions on
> where to look would be appreciated.
> Thanks!
> Keith
>|||OK then I would have to agree with Geoff in that it sounds like an issue
with the servers communications. If you can run this fine locally and all
other machines have issues it kind of narrows it down to that machine.
Check the event logs and see if there are errors on that server.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Keith" <keith@.alh.com> wrote in message
news:e8xPg6C4HHA.5724@.TK2MSFTNGP05.phx.gbl...
> Yes...a dozen or so actually. And I've tried them on different remote
> machines as well with SSMS and SSMSe, in addition to using queries from
> the query window and "Open Table/View" from the Object Explorer.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OTMOdYC4HHA.2312@.TK2MSFTNGP06.phx.gbl...
>|||Geoff N. Hiten (SQLCraftsman@.gmail.com) writes:
> Sounds like larger network packets are getting dropped. Some bridges that
> do not do packet splitting can cause this.
Keith's description reminds me of a very weird error that a formed DSL
provider of mine had. I was mainly reading my after a holiday in SSH2
connection to a Unix account. And that worked fine. But then I got the
idea to look at some web site, but I could not access it. Tried another.
Did not work. A third one. Eventually I tried running lynx from the Unix
account. And that hung too! But I could open a new connection and read my
mail.
Finally I came around to put a very short text file on my web site, and
sure enough, this page did turn up in the browser. So I concluded they
had an error where split packets got lost, but as long as the packets
were small, things worked.
(The reason this provider is a former provider, is simply because I moved
to a new flat, and they could not deliver DSL there.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:erXXwEE4HHA.5980@.TK2MSFTNGP04.phx.gbl...
> Sounds like larger network packets are getting dropped. Some bridges that
> do not do packet splitting can cause this.
I should add that this behavior does not happen running queries against any
other SQL Express, SQL 2005, SQL 2000, or MSDE instance - named or
otherwise, physical or virtual machine - anywhere on my network.
Only this particular instance behaves this way.|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OeY8weE4HHA.3916@.TK2MSFTNGP02.phx.gbl...
> OK then I would have to agree with Geoff in that it sounds like an issue
> with the servers communications. If you can run this fine locally and all
> other machines have issues it kind of narrows it down to that machine.
Unless you install the product on another machine and get the same behavior,
which I have.

> Check the event logs and see if there are errors on that server.
The Event Viewer does not show any errors, nor does the error log in the
instance's data directory.
Considering that this behavior is present along with the installation of
this particular product, and that other named SQL and SQL Express instances
on the same machine do not demonstrate this behavior - even when returning
result sets orders of magnitude larger and on the same physical network -
I'm inclined to believe that this behavior is not machine specific but
rather localized to the named instance installed by this particular product.
Meaning, some configurable property of SQL Server.
I'm totally willing to believe that this is a bug in the product - a
misconfiguration of some kind - but it sounds like most folks don't think
that this type of (mis)configuration is even possible.
Oh, did I mention that this is a Microsoft product's install of SQL Express
;-)
k|||> Unless you install the product on another machine and get the same
> behavior, which I have.

> Considering that this behavior is present along with the installation of
> this particular product, and that other named SQL and SQL Express
> instances on the same machine do not demonstrate this behavior - even when
> returning result sets orders of magnitude larger and on the same physical
> network - I'm inclined to believe that this behavior is not machine
> specific but rather localized to the named instance installed by this
> particular product. Meaning, some configurable property of SQL Server.
Tidbits like these are always good to know. The answers we gave were based
on the info given. I would ping the makers of the product then and see if
they can provide a clue. Other than some ANSI settings at the connection or
database object level that may return different results I know of no
settings or configuration that would produce this behavior. Things like ANSI
PADDING, ANSI NULLS etc can affect how SQL Server treats the data for joins
and the Where clause which can produce different result sets. But none of
that is based on the size of the result set. I would check to see if the
connection settings are the same for both the local and remote SSMS. But
like I said that doesn't explain all the symptoms you mention.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Keith" <keith@.alh.com> wrote in message
news:O%23h0HtF4HHA.484@.TK2MSFTNGP06.phx.gbl...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OeY8weE4HHA.3916@.TK2MSFTNGP02.phx.gbl...
> Unless you install the product on another machine and get the same
> behavior, which I have.
>
> The Event Viewer does not show any errors, nor does the error log in the
> instance's data directory.
> Considering that this behavior is present along with the installation of
> this particular product, and that other named SQL and SQL Express
> instances on the same machine do not demonstrate this behavior - even when
> returning result sets orders of magnitude larger and on the same physical
> network - I'm inclined to believe that this behavior is not machine
> specific but rather localized to the named instance installed by this
> particular product. Meaning, some configurable property of SQL Server.
> I'm totally willing to believe that this is a bug in the product - a
> misconfiguration of some kind - but it sounds like most folks don't think
> that this type of (mis)configuration is even possible.
> Oh, did I mention that this is a Microsoft product's install of SQL
> Express ;-)
> k
>
>|||I discovered this when I was a network admin/dba many years ago. We had a
business network and a process control network that had to be splittable for
political reasons. We had an ethernet bridge set up that we could unplug to
isolate the systems. I was setting up Replication (SQL 6.0/6.5, I think)
between two servers. The both had FDDI interfaces , but there was the
ethernet bridge between them. They negotiated large frame sizes since they
were both on FDDI, not considering that the equipment in the middle couldn't
pass that big a packet. Whatever genius designed the netotiation protocol
didn't bother to test and see if an actual large packet could make it
through. Drove me buggy troubleshooting it. Test queries worked. Ping
never failed. Drives mapped. Then replication would fail to sync. I
eventually found the issue and limited the packet size on the
publisher/distributor and never had another problem.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns998F55155F70Yazorman@.127.0.0.1...
> Geoff N. Hiten (SQLCraftsman@.gmail.com) writes:
> Keith's description reminds me of a very weird error that a formed DSL
> provider of mine had. I was mainly reading my after a holiday in SSH2
> connection to a Unix account. And that worked fine. But then I got the
> idea to look at some web site, but I could not access it. Tried another.
> Did not work. A third one. Eventually I tried running lynx from the Unix
> account. And that hung too! But I could open a new connection and read my
> mail.
> Finally I came around to put a very short text file on my web site, and
> sure enough, this page did turn up in the browser. So I concluded they
> had an error where split packets got lost, but as long as the packets
> were small, things worked.
>
> (The reason this provider is a former provider, is simply because I moved
> to a new flat, and they could not deliver DSL there.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx

Changing paper size through asp

I have designed a report on my local drive to be used as a label (4" X2") I've installed the driver for the label printer and set the paper size. Works Great. I put the report on our Crystal Server where it is called through asp. Instead of getting individual 4X2 labels, I get an 8X10 with all the labels listed. Is there a way to define the paper size through the asp page? Or does the printer driver need to be installed on the server? I don't want to mess things up for our IT people and none of them know the solution. Thanks!Hi,

To set the papersize you can use,

session("oRpt").PaperSize property.

=>session("oRpt") is the Report Object

But I'm not sure about the driver. Postively it will expect all the resources in Server.