Changing the logical name of a DB

Hi,
I recently restored one DB over another different one in order to import a
template of data into a new DB. I did this in Enterprise Manager. The gui
allowed me to change the physical file path & name to match the destination
file, but didn't allow me to change the logical name.
How can I now change the logical file names of the data & log files?
Many thanks for any help
You should do your best to learn the DDL equivalents of pointing and
clicking in the GUI, for two reasons: (a) you can easily store your scripts
in source control, and (b) you will have access to parts of the
functionality that didn't make it into the interface. Try this in Query
Analyzer:
ALTER DATABASE Database_Name
MODIFY FILE
(
NAME = Logical_Name,
NEWNAME = NEW_Logical_Name
);
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:8FFB9324-E3A9-4676-81C4-C8970539AC54@.microsoft.com...
> Hi,
> I recently restored one DB over another different one in order to import a
> template of data into a new DB. I did this in Enterprise Manager. The gui
> allowed me to change the physical file path & name to match the
> destination
> file, but didn't allow me to change the logical name.
> How can I now change the logical file names of the data & log files?
> Many thanks for any help
|||> How can I now change the logical file names of the data & log files?
You can use ALTER DATABASE...MODIFY FILE. For example:
ALTER DATABASE AdventureWorks
MODIFY FILE (
NAME='AdventureWorks_Data',
NEWNAME='AdventureWorks_Data_New')
See ALTER DATABASE in the Books Online for details.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:8FFB9324-E3A9-4676-81C4-C8970539AC54@.microsoft.com...
> Hi,
> I recently restored one DB over another different one in order to import a
> template of data into a new DB. I did this in Enterprise Manager. The gui
> allowed me to change the physical file path & name to match the
> destination
> file, but didn't allow me to change the logical name.
> How can I now change the logical file names of the data & log files?
> Many thanks for any help
|||Hello Dan,
Thank you very much for your help here.
Cheers
Ant
"Dan Guzman" wrote:

> You can use ALTER DATABASE...MODIFY FILE. For example:
> ALTER DATABASE AdventureWorks
> MODIFY FILE (
> NAME='AdventureWorks_Data',
> NEWNAME='AdventureWorks_Data_New')
> See ALTER DATABASE in the Books Online for details.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:8FFB9324-E3A9-4676-81C4-C8970539AC54@.microsoft.com...
>
|||What version are you running?
In SQL 2000 you cannot change the logical file names.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:8FFB9324-E3A9-4676-81C4-C8970539AC54@.microsoft.com...
> Hi,
> I recently restored one DB over another different one in order to import a
> template of data into a new DB. I did this in Enterprise Manager. The gui
> allowed me to change the physical file path & name to match the
> destination
> file, but didn't allow me to change the logical name.
> How can I now change the logical file names of the data & log files?
> Many thanks for any help
|||Hi Kalen,
Thanks for the reply but in fact it appears you can. I am using SQL 2000 &
have successfully changed the logical name simply by using the Alter table
modify file statement I was given above. It worked fine!
Thank you any way for your input on this though.
Cheer
Ant
"Kalen Delaney" wrote:

> What version are you running?
> In SQL 2000 you cannot change the logical file names.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:8FFB9324-E3A9-4676-81C4-C8970539AC54@.microsoft.com...
>
>
|||Hi, Kalen.

> What version are you running?
> In SQL 2000 you cannot change the logical file names.
Perhaps you are thinking of SQL 7. IIRC, the ability to change the logical
names was introduced in SQL 2000.
Hope this helps.
Dan Guzman
SQL Server MVP
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23dlcDj1UIHA.5980@.TK2MSFTNGP04.phx.gbl...
> What version are you running?
> In SQL 2000 you cannot change the logical file names.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:8FFB9324-E3A9-4676-81C4-C8970539AC54@.microsoft.com...
>
|||You're right... as Dan indicated it was actually SQL 7, when the new files
structures and ALTER DATABASE command were first introduced, that there was
no way to change the logical name.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:B2DD5503-5FC5-4B16-8B3B-C38655367FDF@.microsoft.com...[vbcol=seagreen]
> Hi Kalen,
> Thanks for the reply but in fact it appears you can. I am using SQL 2000 &
> have successfully changed the logical name simply by using the Alter table
> modify file statement I was given above. It worked fine!
> Thank you any way for your input on this though.
> Cheer
> Ant
> "Kalen Delaney" wrote:
|||Thanks, Dan.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:C6D04793-D874-4B42-A440-ED117325CE42@.microsoft.com...
> Hi, Kalen.
>
> Perhaps you are thinking of SQL 7. IIRC, the ability to change the
> logical names was introduced in SQL 2000.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23dlcDj1UIHA.5980@.TK2MSFTNGP04.phx.gbl...
>

Changing the logical name of a DB

Hi,
I recently restored one DB over another different one in order to import a
template of data into a new DB. I did this in Enterprise Manager. The gui
allowed me to change the physical file path & name to match the destination
file, but didn't allow me to change the logical name.
How can I now change the logical file names of the data & log files?
Many thanks for any helpYou should do your best to learn the DDL equivalents of pointing and
clicking in the GUI, for two reasons: (a) you can easily store your scripts
in source control, and (b) you will have access to parts of the
functionality that didn't make it into the interface. Try this in Query
Analyzer:
ALTER DATABASE Database_Name
MODIFY FILE
(
NAME = Logical_Name,
NEWNAME = NEW_Logical_Name
);
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:8FFB9324-E3A9-4676-81C4-C8970539AC54@.microsoft.com...
> Hi,
> I recently restored one DB over another different one in order to import a
> template of data into a new DB. I did this in Enterprise Manager. The gui
> allowed me to change the physical file path & name to match the
> destination
> file, but didn't allow me to change the logical name.
> How can I now change the logical file names of the data & log files?
> Many thanks for any help|||> How can I now change the logical file names of the data & log files?
You can use ALTER DATABASE...MODIFY FILE. For example:
ALTER DATABASE AdventureWorks
MODIFY FILE (
NAME='AdventureWorks_Data',
NEWNAME='AdventureWorks_Data_New')
See ALTER DATABASE in the Books Online for details.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:8FFB9324-E3A9-4676-81C4-C8970539AC54@.microsoft.com...
> Hi,
> I recently restored one DB over another different one in order to import a
> template of data into a new DB. I did this in Enterprise Manager. The gui
> allowed me to change the physical file path & name to match the
> destination
> file, but didn't allow me to change the logical name.
> How can I now change the logical file names of the data & log files?
> Many thanks for any help|||Hello Dan,
Thank you very much for your help here.
Cheers
Ant
"Dan Guzman" wrote:
> > How can I now change the logical file names of the data & log files?
> You can use ALTER DATABASE...MODIFY FILE. For example:
> ALTER DATABASE AdventureWorks
> MODIFY FILE (
> NAME='AdventureWorks_Data',
> NEWNAME='AdventureWorks_Data_New')
> See ALTER DATABASE in the Books Online for details.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:8FFB9324-E3A9-4676-81C4-C8970539AC54@.microsoft.com...
> > Hi,
> > I recently restored one DB over another different one in order to import a
> > template of data into a new DB. I did this in Enterprise Manager. The gui
> > allowed me to change the physical file path & name to match the
> > destination
> > file, but didn't allow me to change the logical name.
> >
> > How can I now change the logical file names of the data & log files?
> >
> > Many thanks for any help
>|||What version are you running?
In SQL 2000 you cannot change the logical file names.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:8FFB9324-E3A9-4676-81C4-C8970539AC54@.microsoft.com...
> Hi,
> I recently restored one DB over another different one in order to import a
> template of data into a new DB. I did this in Enterprise Manager. The gui
> allowed me to change the physical file path & name to match the
> destination
> file, but didn't allow me to change the logical name.
> How can I now change the logical file names of the data & log files?
> Many thanks for any help|||Hi Kalen,
Thanks for the reply but in fact it appears you can. I am using SQL 2000 &
have successfully changed the logical name simply by using the Alter table
modify file statement I was given above. It worked fine!
Thank you any way for your input on this though.
Cheer
Ant
"Kalen Delaney" wrote:
> What version are you running?
> In SQL 2000 you cannot change the logical file names.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:8FFB9324-E3A9-4676-81C4-C8970539AC54@.microsoft.com...
> > Hi,
> > I recently restored one DB over another different one in order to import a
> > template of data into a new DB. I did this in Enterprise Manager. The gui
> > allowed me to change the physical file path & name to match the
> > destination
> > file, but didn't allow me to change the logical name.
> >
> > How can I now change the logical file names of the data & log files?
> >
> > Many thanks for any help
>
>|||Hi, Kalen.
> What version are you running?
> In SQL 2000 you cannot change the logical file names.
Perhaps you are thinking of SQL 7. IIRC, the ability to change the logical
names was introduced in SQL 2000.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23dlcDj1UIHA.5980@.TK2MSFTNGP04.phx.gbl...
> What version are you running?
> In SQL 2000 you cannot change the logical file names.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:8FFB9324-E3A9-4676-81C4-C8970539AC54@.microsoft.com...
>> Hi,
>> I recently restored one DB over another different one in order to import
>> a
>> template of data into a new DB. I did this in Enterprise Manager. The gui
>> allowed me to change the physical file path & name to match the
>> destination
>> file, but didn't allow me to change the logical name.
>> How can I now change the logical file names of the data & log files?
>> Many thanks for any help
>|||You're right... as Dan indicated it was actually SQL 7, when the new files
structures and ALTER DATABASE command were first introduced, that there was
no way to change the logical name.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:B2DD5503-5FC5-4B16-8B3B-C38655367FDF@.microsoft.com...
> Hi Kalen,
> Thanks for the reply but in fact it appears you can. I am using SQL 2000 &
> have successfully changed the logical name simply by using the Alter table
> modify file statement I was given above. It worked fine!
> Thank you any way for your input on this though.
> Cheer
> Ant
> "Kalen Delaney" wrote:
>> What version are you running?
>> In SQL 2000 you cannot change the logical file names.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:8FFB9324-E3A9-4676-81C4-C8970539AC54@.microsoft.com...
>> > Hi,
>> > I recently restored one DB over another different one in order to
>> > import a
>> > template of data into a new DB. I did this in Enterprise Manager. The
>> > gui
>> > allowed me to change the physical file path & name to match the
>> > destination
>> > file, but didn't allow me to change the logical name.
>> >
>> > How can I now change the logical file names of the data & log files?
>> >
>> > Many thanks for any help
>>|||Thanks, Dan.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:C6D04793-D874-4B42-A440-ED117325CE42@.microsoft.com...
> Hi, Kalen.
>> What version are you running?
>> In SQL 2000 you cannot change the logical file names.
> Perhaps you are thinking of SQL 7. IIRC, the ability to change the
> logical names was introduced in SQL 2000.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23dlcDj1UIHA.5980@.TK2MSFTNGP04.phx.gbl...
>> What version are you running?
>> In SQL 2000 you cannot change the logical file names.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:8FFB9324-E3A9-4676-81C4-C8970539AC54@.microsoft.com...
>> Hi,
>> I recently restored one DB over another different one in order to import
>> a
>> template of data into a new DB. I did this in Enterprise Manager. The
>> gui
>> allowed me to change the physical file path & name to match the
>> destination
>> file, but didn't allow me to change the logical name.
>> How can I now change the logical file names of the data & log files?
>> Many thanks for any help
>>
>

changing the default value for a column

Does anybody know how I can change the default value for a column?
I was trying to remove the default value in order to add the new one
afterwards. This is what I tried:

alter table /table-name/ drop default for /column-name/
alter table /table-name/ alter column /column-name/
(/new-decl-without-default/)

It did not work. I cannot find a solution in the documentation. Maybe
you can help me out?

Thank you,
JohanALTER TABLE table_name
DROP CONSTRAINT name_of_default_constraint

ALTER TABLE table_name
ADD CONSTRAINT name_of_default_constraint DEFAULT 123 FOR column_name
--
David Portas
SQL Server MVP
--|||David Portas schreef:
> ALTER TABLE table_name
> DROP CONSTRAINT name_of_default_constraint

Thank you for the quick reply. But how do I know the
name_of_default_constraint? I created the default value like this:

alter table /table_name/ add default /default_value/ for /column_name/

Johan|||It's a good idea to give constraints meaningful names when you create
them, otherwise the server assigns them an obscure unique identifier
name. Use a consistent naming convention and then you'll know the
constraint names for tables and columns.

You can find the name of an existing default using the Object Browser
in Query Analyzer or in the output of sp_help 'table_name'
--
David Portas
SQL Server MVP
--|||Hi,

something like this could meet your requirements:

declare @.myStatement nvarchar(4000)

select @.myStatement=('ALTER TABLE RP_CUSTOM_ATTRIBUTE DROP CONSTRAINT ' +
(select so.name
from sysobjects so, syscolumns sc
where sc.name = 'YOUR_COLUMN_NAME' and sc.cdefault = so.id))

EXEC sp_executesql @.myStatement
go

Karsten

"Johan Vervloet" <johanv@.chiro.be> schrieb im Newsbeitrag
news:41fe1db7$0$28977$e4fe514c@.news.xs4all.nl...
> David Portas schreef:
> > ALTER TABLE table_name
> > DROP CONSTRAINT name_of_default_constraint
> Thank you for the quick reply. But how do I know the
> name_of_default_constraint? I created the default value like this:
> alter table /table_name/ add default /default_value/ for /column_name/
> Johan

Changing Table Owner and user Owner

Hello there
I'm using Windows authentication, and i'm suddenly don't member of Dbo
owners.
What i need to change on the server in order so when i create new object
it's written as dbo and not as my name?
after i change that how can i change the owner of tables, views and store
procedures to be dbo owner?
' 03-5611606
' 050-7709399
: roy@.atidsm.co.ilRoy,shalom
Pls read this article from BOL
Database Owner (dbo)
The dbo is a user that has implied permissions to perform all activities in
the database. Any member of the sysadmin fixed server role who uses a
database is mapped to the special user inside each database called dbo.
Also, any object created by any member of the sysadmin fixed server role
belongs to dbo automatically.
For example, if user Andrew is a member of the sysadmin fixed server role
and creates a table T1, T1 belongs to dbo and is qualified as dbo.T1, not as
Andrew.T1. Conversely, if Andrew is not a member of the sysadmin fixed
server role but is a member only of the db_owner fixed database role and
creates a table T1, T1 belongs to Andrew and is qualified as Andrew.T1. The
table belongs to Andrew because he did not qualify the table as dbo.T1.
The dbo user cannot be deleted and is always present in every database.
Only objects created by members of the sysadmin fixed server role (or by the
dbo user) belong to dbo. Objects created by any other user who is not also a
member of the sysadmin fixed server role (including members of the db_owner
fixed database role):
a.. Belong to the user creating the object, not dbo.
b.. Are qualified with the name of the user who created the object.
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:OWTo$T54FHA.3188@.TK2MSFTNGP15.phx.gbl...
> Hello there
> I'm using Windows authentication, and i'm suddenly don't member of Dbo
> owners.
> What i need to change on the server in order so when i create new object
> it's written as dbo and not as my name?
> after i change that how can i change the owner of tables, views and store
> procedures to be dbo owner?
> --
>
>
> ' 03-5611606
> ' 050-7709399
> : roy@.atidsm.co.il
>

Changing System Time on SQL Server Hardware

I'm trying to test a web-based, time and attendance
application. In order o compress the testing window, I'd
like to adjust the system time. The application pulls all
time stamps from the database server so when I need to
adjust the system time, I will be adjusting the database
server (SQL Server) time forward and backward.
I'm being told that if I adjust the system time on the
database tier (SQL Server) that I will destabilize the
database (specifically causing trouble with log files) and
my results will not be reliable. In the past while
testing a time sensitive, client/server application, I
adjusted the system time forwards and backwards without
any negative repercussions.
I'd like to know what would cause this instability, if the
information that I'm receiving is accurate and if there
are any suggested workarounds.
Changing the time should not cause any instability issues that I am aware
of. Log records are identified by their LSN (Log Sequence Number) and are
written serially. A lot of servers for example automatically change the time
for Daylight savings etc with no ill effects
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Caleb" <anonymous@.discussions.microsoft.com> wrote in message
news:1c71c01c42238$78611dc0$a501280a@.phx.gbl...
> I'm trying to test a web-based, time and attendance
> application. In order o compress the testing window, I'd
> like to adjust the system time. The application pulls all
> time stamps from the database server so when I need to
> adjust the system time, I will be adjusting the database
> server (SQL Server) time forward and backward.
> I'm being told that if I adjust the system time on the
> database tier (SQL Server) that I will destabilize the
> database (specifically causing trouble with log files) and
> my results will not be reliable. In the past while
> testing a time sensitive, client/server application, I
> adjusted the system time forwards and backwards without
> any negative repercussions.
> I'd like to know what would cause this instability, if the
> information that I'm receiving is accurate and if there
> are any suggested workarounds.

Changing System Time on SQL Server Hardware

I'm trying to test a web-based, time and attendance
application. In order o compress the testing window, I'd
like to adjust the system time. The application pulls all
time stamps from the database server so when I need to
adjust the system time, I will be adjusting the database
server (SQL Server) time forward and backward.
I'm being told that if I adjust the system time on the
database tier (SQL Server) that I will destabilize the
database (specifically causing trouble with log files) and
my results will not be reliable. In the past while
testing a time sensitive, client/server application, I
adjusted the system time forwards and backwards without
any negative repercussions.
I'd like to know what would cause this instability, if the
information that I'm receiving is accurate and if there
are any suggested workarounds.Changing the time should not cause any instability issues that I am aware
of. Log records are identified by their LSN (Log Sequence Number) and are
written serially. A lot of servers for example automatically change the time
for Daylight savings etc with no ill effects
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Caleb" <anonymous@.discussions.microsoft.com> wrote in message
news:1c71c01c42238$78611dc0$a501280a@.phx
.gbl...
> I'm trying to test a web-based, time and attendance
> application. In order o compress the testing window, I'd
> like to adjust the system time. The application pulls all
> time stamps from the database server so when I need to
> adjust the system time, I will be adjusting the database
> server (SQL Server) time forward and backward.
> I'm being told that if I adjust the system time on the
> database tier (SQL Server) that I will destabilize the
> database (specifically causing trouble with log files) and
> my results will not be reliable. In the past while
> testing a time sensitive, client/server application, I
> adjusted the system time forwards and backwards without
> any negative repercussions.
> I'd like to know what would cause this instability, if the
> information that I'm receiving is accurate and if there
> are any suggested workarounds.

Changing System Time on SQL Server Hardware

I'm trying to test a web-based, time and attendance
application. In order o compress the testing window, I'd
like to adjust the system time. The application pulls all
time stamps from the database server so when I need to
adjust the system time, I will be adjusting the database
server (SQL Server) time forward and backward.
I'm being told that if I adjust the system time on the
database tier (SQL Server) that I will destabilize the
database (specifically causing trouble with log files) and
my results will not be reliable. In the past while
testing a time sensitive, client/server application, I
adjusted the system time forwards and backwards without
any negative repercussions.
I'd like to know what would cause this instability, if the
information that I'm receiving is accurate and if there
are any suggested workarounds.Changing the time should not cause any instability issues that I am aware
of. Log records are identified by their LSN (Log Sequence Number) and are
written serially. A lot of servers for example automatically change the time
for Daylight savings etc with no ill effects
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Caleb" <anonymous@.discussions.microsoft.com> wrote in message
news:1c71c01c42238$78611dc0$a501280a@.phx.gbl...
> I'm trying to test a web-based, time and attendance
> application. In order o compress the testing window, I'd
> like to adjust the system time. The application pulls all
> time stamps from the database server so when I need to
> adjust the system time, I will be adjusting the database
> server (SQL Server) time forward and backward.
> I'm being told that if I adjust the system time on the
> database tier (SQL Server) that I will destabilize the
> database (specifically causing trouble with log files) and
> my results will not be reliable. In the past while
> testing a time sensitive, client/server application, I
> adjusted the system time forwards and backwards without
> any negative repercussions.
> I'd like to know what would cause this instability, if the
> information that I'm receiving is accurate and if there
> are any suggested workarounds.

changing sql server name

Hi
The name of my sql server has an underscore in it and I need to get rid of
it in order to set up replication. BOL is a bit unclear as do I have to
change the name of the pc first of all (which also has underscore in name)
and then run sp_dropserver and sp_addserver. Really, my question is it
possible to rename sql server without first changing the name of the pc? So
can I just use the 2sp's without changing the name of the pc?
Thanks
Jonjo
Hi
You need to rename the PC. sp_dropserver and sp_addserver are there to fix
up the row in Master where the SQL Server queries to find out about itself.
It does not change the network name.
Regards
Mike
"jonjo" wrote:

> Hi
> The name of my sql server has an underscore in it and I need to get rid of
> it in order to set up replication. BOL is a bit unclear as do I have to
> change the name of the pc first of all (which also has underscore in name)
> and then run sp_dropserver and sp_addserver. Really, my question is it
> possible to rename sql server without first changing the name of the pc? So
> can I just use the 2sp's without changing the name of the pc?
> Thanks
> Jonjo

changing sql server name

Hi
The name of my sql server has an underscore in it and I need to get rid of
it in order to set up replication. BOL is a bit unclear as do I have to
change the name of the pc first of all (which also has underscore in name)
and then run sp_dropserver and sp_addserver. Really, my question is it
possible to rename sql server without first changing the name of the pc? So
can I just use the 2sp's without changing the name of the pc?
Thanks
JonjoHi
You need to rename the PC. sp_dropserver and sp_addserver are there to fix
up the row in Master where the SQL Server queries to find out about itself.
It does not change the network name.
Regards
Mike
"jonjo" wrote:

> Hi
> The name of my sql server has an underscore in it and I need to get rid of
> it in order to set up replication. BOL is a bit unclear as do I have to
> change the name of the pc first of all (which also has underscore in name)
> and then run sp_dropserver and sp_addserver. Really, my question is it
> possible to rename sql server without first changing the name of the pc? S
o
> can I just use the 2sp's without changing the name of the pc?
> Thanks
> Jonjo

changing sql server name

Hi
The name of my sql server has an underscore in it and I need to get rid of
it in order to set up replication. BOL is a bit unclear as do I have to
change the name of the pc first of all (which also has underscore in name)
and then run sp_dropserver and sp_addserver. Really, my question is it
possible to rename sql server without first changing the name of the pc? So
can I just use the 2sp's without changing the name of the pc?
Thanks
JonjoHi
You need to rename the PC. sp_dropserver and sp_addserver are there to fix
up the row in Master where the SQL Server queries to find out about itself.
It does not change the network name.
Regards
Mike
"jonjo" wrote:
> Hi
> The name of my sql server has an underscore in it and I need to get rid of
> it in order to set up replication. BOL is a bit unclear as do I have to
> change the name of the pc first of all (which also has underscore in name)
> and then run sp_dropserver and sp_addserver. Really, my question is it
> possible to rename sql server without first changing the name of the pc? So
> can I just use the 2sp's without changing the name of the pc?
> Thanks
> Jonjo

Changing record number

Hi,
I have cancelled some records due to null fields,then the record number is not coming in order.how to code it to get it in orderwow, that's a short description.........
So I'm gonna have to guess the detail. You are probably, from the brief description, experiencing a problem because your report is having trouble with the nulls. More information, including database type, OS, CR version always helps us better assess the problem.
I think you need a formula to convert fields likely to be nulls, eg:
@.AccountBalance

if IsNull({tablename.AccountBalance}) then
formula = 0
else
formula = {tablename.AccountBalance}
end if

I've deliberatley kept this simple to demonstrate the principles.

Dave

Changing output order for xml explicit

This one shouldn't be complicated or unusual but it's got me stumped!
Here's an example query.
select 1 as Tag,
0 as Parent,
us_id as [Person!1!Person!hide],
'' as [Address!2!Address1!element],
'' as [Address!2!Postcode!element],
us_telephone as [Person!1!Telephone!element]
from tblUser
union
select 2 as Tag,
1 as Parent,
us_id as [Person!1!Person!hide],
us_addr1 as [Address!2!Address1!element],
us_postcode as [Address!2!Postcode!element],
us_telephone as [Person!1!Telephone!element]
from tblUser
order by [Person!1!Person!hide]
for xml explicit
This gives the output :-
<Person>
<Telephone>t1</Telephone>
<Address>
<Address1>a1</Address1>
<Postcode>p1</Postcode>
</Address>
</Person>
The output I require is :-
<Person>
<Address>
<Address1>a1</Address1>
<Postcode>p1</Postcode>
</Address>
<Telephone>t1</Telephone>
</Person>
i.e. put the telephone number after the address.
The application this is for is currently on SQLServer 2000, but can be
migrated to 2005 if that would help me get the data in the required format.
Thanks for any help.Hello maggi,
The order follows the order in your query have you tried the query below.
In addition you are populating the telephone number in the select for the
address element (Tag 2) even through it is populated on the main Person elem
ent
(Tag 1).
This is only required if you need to order by telephone number.
select 1 as Tag,
0 as Parent,
us_id as [Person!1!Person!hide],
us_telephone as [Person!1!Telephone!element]
'' as [Address!2!Address1!element],
'' as [Address!2!Postcode!element],
from tblUser
union
select 2 as Tag,
1 as Parent,
us_id as [Person!1!Person!hide],
us_addr1 as [Address!2!Address1!element],
us_postcode as [Address!2!Postcode!element],
null
from tblUser
order by [Person!1!Person!hide]
for xml explicit
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> This one shouldn't be complicated or unusual but it's got me stumped!
> Here's an example query.
> select 1 as Tag,
> 0 as Parent,
> us_id as [Person!1!Person!hide],
> '' as [Address!2!Address1!element],
> '' as [Address!2!Postcode!element],
> us_telephone as [Person!1!Telephone!element]
> from tblUser
> union
> select 2 as Tag,
> 1 as Parent,
> us_id as [Person!1!Person!hide],
> us_addr1 as [Address!2!Address1!element],
> us_postcode as [Address!2!Postcode!element],
> us_telephone as [Person!1!Telephone!element]
> from tblUser
> order by [Person!1!Person!hide]
> for xml explicit
> This gives the output :-
> <Person>
> <Telephone>t1</Telephone>
> <Address>
> <Address1>a1</Address1>
> <Postcode>p1</Postcode>
> </Address>
> </Person>
> The output I require is :-
> <Person>
> <Address>
> <Address1>a1</Address1>
> <Postcode>p1</Postcode>
> </Address>
> <Telephone>t1</Telephone>
> </Person>
> i.e. put the telephone number after the address.
> The application this is for is currently on SQLServer 2000, but can be
> migrated to 2005 if that would help me get the data in the required
> format.
> Thanks for any help.
>|||In 62959f1a501d68c92bc11929e988@.msnews.microsoft.com, Simon Sabin
<SimonSabin@.noemail.noemail> typed:

> The order follows the order in your query have you tried the query
> below. In addition you are populating the telephone number in the select
> for
> the address element (Tag 2) even through it is populated on the main
> Person element (Tag 1).
> This is only required if you need to order by telephone number.
> select 1 as Tag,
> 0 as Parent,
> us_id as [Person!1!Person!hide],
> us_telephone as [Person!1!Telephone!element]
> '' as [Address!2!Address1!element],
> '' as [Address!2!Postcode!element],
> from tblUser
> union
> select 2 as Tag,
> 1 as Parent,
> us_id as [Person!1!Person!hide],
> us_addr1 as [Address!2!Address1!element],
> us_postcode as [Address!2!Postcode!element],
> null
> from tblUser
> order by [Person!1!Person!hide]
> for xml explicit
Cheers Simon, but that still wouldn't sort the telephone number AFTER the
address details to give me
<Person>
<Address>
<Address1>a1</Address1>
<Postcode>p1</Postcode>
</Address>
<Telephone>t1</Telephone>
</Person>
I've given up on it in SQL Server 2000, and rewritten it using XML AUTO for
2005. I'd still be interested to know if anyone ever figures out a method
for doing it though.|||Try this...
select 1 as Tag,
NULL as Parent,
us_id as [Person!1!Person!hide],
NULL as [Address!2!Address1!element],
NULL as [Address!2!Postcode!element],
us_telephone as [Telephone!3]
from tblUser
union all
select 2 as Tag,
1 as Parent,
us_id as [Person!1!Person!hide],
us_addr1 as [Address!2!Address1!element],
us_postcode as [Address!2!Postcode!element],
us_telephone as [Telephone!3]
from tblUser
union all
select 3 as Tag,
1 as Parent,
us_id as [Person!1!Person!hide],
NULL as [Address!2!Address1!element],
NULL as [Address!2!Postcode!element],
us_telephone as [Telephone!3]
from tblUser
order by [Person!1!Person!hide]
for xml explicit
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
--
"maggi" <maggi_v@.gazeta.pl.invalid> wrote in message
news:eshutp$b8e$1@.inews.gazeta.pl...
> In 62959f1a501d68c92bc11929e988@.msnews.microsoft.com, Simon Sabin
> <SimonSabin@.noemail.noemail> typed:
>
> Cheers Simon, but that still wouldn't sort the telephone number AFTER the
> address details to give me
> <Person>
> <Address>
> <Address1>a1</Address1>
> <Postcode>p1</Postcode>
> </Address>
> <Telephone>t1</Telephone>
> </Person>
> I've given up on it in SQL Server 2000, and rewritten it using XML AUTO
> for 2005. I'd still be interested to know if anyone ever figures out a
> method for doing it though.
>|||In uWrpmZEYHHA.992@.TK2MSFTNGP02.phx.gbl, Peter W. DeBetta
<debettap@.hotmail.com> typed:

> Try this...
> select 1 as Tag,
> NULL as Parent,
> us_id as [Person!1!Person!hide],
> NULL as [Address!2!Address1!element],
> NULL as [Address!2!Postcode!element],
> us_telephone as [Telephone!3]
> from tblUser
> union all
> select 2 as Tag,
> 1 as Parent,
> us_id as [Person!1!Person!hide],
> us_addr1 as [Address!2!Address1!element],
> us_postcode as [Address!2!Postcode!element],
> us_telephone as [Telephone!3]
> from tblUser
> union all
> select 3 as Tag,
> 1 as Parent,
> us_id as [Person!1!Person!hide],
> NULL as [Address!2!Address1!element],
> NULL as [Address!2!Postcode!element],
> us_telephone as [Telephone!3]
> from tblUser
> order by [Person!1!Person!hide]
> for xml explicit
That's the one!
Thank you very much, Peter.

changing order of DTS package

Hi everyone,

I have a DTS package that exports table from SQL Server to an Oracle database. However, I want to change the order in which the tables get exported because of referential integrity. I can find no way in the DTS designer to do that! IS there a way to change this order other than specifying a querY for each and every table!

Thanks,

PankajIf you're using Data Driven Query task in your package, - no. You'll need to replace it with individual table transfer tasks per table, where you can specify the order in which each table is being transfered. It's more tedious to create, but gives you much more control.

Changing Order of columns in a table

Is there any way to change the order (position) of columns of a table ,
without the need of dropping and recreating the table itself !?
I found the 'colid' field of the 'syscolumn' system table: by changing the
colid of each column I got the desired result, unfortunately if I have to
create indexes ih the above columns later on I get the error
Location: record.cpp: 759
Expression: pbind-> fcheckfornull ()
spID: errore 56 o 61
process ID 2976 o 2452
ID code -2147467259
as a side effect.
Moreover , I saw that in the 'syscolumns' system table , the change in the
'colid' field was an insert rather than an update...
Thanks,
Massimo.If you monkey around with the data in the sys... tables you can screw up
your database permanently.
I think you should really drop the table and re-create it.
You could also create a view with the columns in the required order.
"news" <massimo.facchi@.getronics.com> wrote in message
news:eqacvBkjDHA.2656@.TK2MSFTNGP10.phx.gbl...
> Is there any way to change the order (position) of columns of a table ,
> without the need of dropping and recreating the table itself !?
> I found the 'colid' field of the 'syscolumn' system table: by changing the
> colid of each column I got the desired result, unfortunately if I have to
> create indexes ih the above columns later on I get the error
> Location: record.cpp: 759
> Expression: pbind-> fcheckfornull ()
> spID: errore 56 o 61
> process ID 2976 o 2452
> ID code -2147467259
> as a side effect.
>
> Moreover , I saw that in the 'syscolumns' system table , the change in the
> 'colid' field was an insert rather than an update...
> Thanks,
>
> Massimo.
>
>
>|||I used to worry about the order of columns when I first started using sql,
but I quickly found I was wasting my time... As the previous poster said,
messing with system tables is not a good way to go... The order of the
columns physically in the record is different than the order you put in the
create table...
Unless you have some huge, overriding reason don't worry about column
order... Otherwise, drop and re-create the table.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"news" <massimo.facchi@.getronics.com> wrote in message
news:eqacvBkjDHA.2656@.TK2MSFTNGP10.phx.gbl...
> Is there any way to change the order (position) of columns of a table ,
> without the need of dropping and recreating the table itself !?
> I found the 'colid' field of the 'syscolumn' system table: by changing the
> colid of each column I got the desired result, unfortunately if I have to
> create indexes ih the above columns later on I get the error
> Location: record.cpp: 759
> Expression: pbind-> fcheckfornull ()
> spID: errore 56 o 61
> process ID 2976 o 2452
> ID code -2147467259
> as a side effect.
>
> Moreover , I saw that in the 'syscolumns' system table , the change in the
> 'colid' field was an insert rather than an update...
> Thanks,
>
> Massimo.
>
>
>

Changing order of columns at run time

Here's the scenario:
I want to be able to create a report (typically these reports are
"table like" reports ... in other words, several columns with a list
of data underneath) and I want to be able to re-order the columns (not
the rows) in these reports at runtime.
So, for instance, if the "report design" is as follows:
COLUMNA COLUMNB COLUMNC
... data goes here ...
... I'd like to be able to pass a parameter in to the report that may
be, for instance, "COLUMNC,COLUMNB,COLUMNA" ... and report columns
would re-order, like follows:
COLUMNC COLUMNB COLUMNA
... data goes here ...
... I don't really care what report designer object is used (Table,
Matrix, etc) ... but I don't yet see a good way to do this. It seems
like the Matrix object might be able to do this via the ColumnGroups
"Sorting" tab ... but I don't seem to be able to come up with an
expression that can make this occur.
Any help is greatly appreciated.
Thanks,
RichardOn Apr 27, 9:39 am, Richard.G...@.Pa-Tech.Com wrote:
> Here's the scenario:
> I want to be able to create a report (typically these reports are
> "table like" reports ... in other words, several columns with a list
> of data underneath) and I want to be able to re-order the columns (not
> the rows) in these reports at runtime.
> So, for instance, if the "report design" is as follows:
> COLUMNA COLUMNB COLUMNC
> ... data goes here ...
> ... I'd like to be able to pass a parameter in to the report that may
> be, for instance, "COLUMNC,COLUMNB,COLUMNA" ... and report columns
> would re-order, like follows:
> COLUMNC COLUMNB COLUMNA
> ... data goes here ...
> ... I don't really care what report designer object is used (Table,
> Matrix, etc) ... but I don't yet see a good way to do this. It seems
> like the Matrix object might be able to do this via the ColumnGroups
> "Sorting" tab ... but I don't seem to be able to come up with an
> expression that can make this occur.
> Any help is greatly appreciated.
> Thanks,
> Richard
If you are using a matrix report, you can control the column layout
(assuming you know the column names prior to runtime) by adding spaces
in front of the names of the columns (like values in the pivot column)
and ordering/sorting by ascending order. If you are using a table
control, you can allow the user to select a sort order (asc/desc) in
the report and then set the columns in the returned resultset
accordingly. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Just Like the report was built by Report Builder..
when click title of column ,the order will be change,right?
how to do it in vs2005?
"EMartinez" <emartinez.pr1@.gmail.com>
':1177733247.239100.15830@.n59g2000hsh.googlegroups.com...
> On Apr 27, 9:39 am, Richard.G...@.Pa-Tech.Com wrote:
>> Here's the scenario:
>> I want to be able to create a report (typically these reports are
>> "table like" reports ... in other words, several columns with a list
>> of data underneath) and I want to be able to re-order the columns (not
>> the rows) in these reports at runtime.
>> So, for instance, if the "report design" is as follows:
>> COLUMNA COLUMNB COLUMNC
>> ... data goes here ...
>> ... I'd like to be able to pass a parameter in to the report that may
>> be, for instance, "COLUMNC,COLUMNB,COLUMNA" ... and report columns
>> would re-order, like follows:
>> COLUMNC COLUMNB COLUMNA
>> ... data goes here ...
>> ... I don't really care what report designer object is used (Table,
>> Matrix, etc) ... but I don't yet see a good way to do this. It seems
>> like the Matrix object might be able to do this via the ColumnGroups
>> "Sorting" tab ... but I don't seem to be able to come up with an
>> expression that can make this occur.
>> Any help is greatly appreciated.
>> Thanks,
>> Richard
>
> If you are using a matrix report, you can control the column layout
> (assuming you know the column names prior to runtime) by adding spaces
> in front of the names of the columns (like values in the pivot column)
> and ordering/sorting by ascending order. If you are using a table
> control, you can allow the user to select a sort order (asc/desc) in
> the report and then set the columns in the returned resultset
> accordingly. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||That's called interactive sort. click on the textbox or table textbox
properties and select the interactive sort tab and set your values.
Amarnath
"renhanyue" wrote:
> Just Like the report was built by Report Builder..
> when click title of column ,the order will be change,right?
> how to do it in vs2005?
> "EMartinez" <emartinez.pr1@.gmail.com>
> ':1177733247.239100.15830@.n59g2000hsh.googlegroups.com...
> > On Apr 27, 9:39 am, Richard.G...@.Pa-Tech.Com wrote:
> >> Here's the scenario:
> >>
> >> I want to be able to create a report (typically these reports are
> >> "table like" reports ... in other words, several columns with a list
> >> of data underneath) and I want to be able to re-order the columns (not
> >> the rows) in these reports at runtime.
> >>
> >> So, for instance, if the "report design" is as follows:
> >>
> >> COLUMNA COLUMNB COLUMNC
> >> ... data goes here ...
> >>
> >> ... I'd like to be able to pass a parameter in to the report that may
> >> be, for instance, "COLUMNC,COLUMNB,COLUMNA" ... and report columns
> >> would re-order, like follows:
> >>
> >> COLUMNC COLUMNB COLUMNA
> >> ... data goes here ...
> >>
> >> ... I don't really care what report designer object is used (Table,
> >> Matrix, etc) ... but I don't yet see a good way to do this. It seems
> >> like the Matrix object might be able to do this via the ColumnGroups
> >> "Sorting" tab ... but I don't seem to be able to come up with an
> >> expression that can make this occur.
> >>
> >> Any help is greatly appreciated.
> >>
> >> Thanks,
> >> Richard
> >
> >
> > If you are using a matrix report, you can control the column layout
> > (assuming you know the column names prior to runtime) by adding spaces
> > in front of the names of the columns (like values in the pivot column)
> > and ordering/sorting by ascending order. If you are using a table
> > control, you can allow the user to select a sort order (asc/desc) in
> > the report and then set the columns in the returned resultset
> > accordingly. Hope this helps.
> >
> > Regards,
> >
> > Enrique Martinez
> > Sr. Software Consultant
> >
>
>|||The issue is that I'm trying to change is the order of the columns
themselves (left to right) ... not the data "within" the column (which
would be the "top to bottom").
Normally, these reports would be simple "table" reports ... the
"extra" feature that I need to give to my users is the ability specify
the order of the columns (left to right)
To further clarify, if the report where a simple SQL statement, the
report designer might specify:
SELECT A, B, C FROM SOMETABLE
... but I want to give the report "user" the ability to say
SELECT C, B, A FROM SOMETABLE

Changing ORDER BY

Hi All
win 2k pro sql 2k IIS 5 ASP-vbscript
I got a stored proc which basically returns a set of records depending
on a category ID (or all if passed ID =0) - see below
The FAQ Category below is retuned mostly order by Title but in some
other areas of my site I need to return it orded by CatID.
I was wondering whats the best way to do this as I cant seem to pass a
column name to the procedure and put "ORDER BY @.SortOrder"
These are the other ways I thought of.
1) create a new procedure the same with order by catID.
2) pass a number and use CASE - but I get error 1008
eg ORDER BY CASE WHEN @.Order=0 THEN Title ELSE CatID END
I can see this becoming a common problem so wouldn't like to do #1
above as that would require alot of maintence to keep them the same if
I changed any of the table design.
Thanks for any help.
Al
CREATE PROCEDURE dbo.usp_Help_FindFAQCategoryByCatID
@.SelCatID TINYINT = 0
AS
SET NOCOUNT ON
-- Return all the FAQ Categories with Cat ID of the selected Category
SELECT CatID, ParentID, Title, FaqCount
FROM Help_FAQ_Categories
WHERE CatID= CASE WHEN @.SelCatID=0 THEN CatID ELSE @.SelCatID END
ORDER BY Title
GOTry
ORDER BY CASE WHEN @.sort = 'col1' THEN col1 END ASC,
"Harag" <harag@.softhome.net> wrote in message
news:3v4mlvo18e9vbjisl08jqrs9m75pc240gd@.4ax.com...
> Hi All
> win 2k pro sql 2k IIS 5 ASP-vbscript
> I got a stored proc which basically returns a set of records depending
> on a category ID (or all if passed ID =0) - see below
> The FAQ Category below is retuned mostly order by Title but in some
> other areas of my site I need to return it orded by CatID.
> I was wondering whats the best way to do this as I cant seem to pass a
> column name to the procedure and put "ORDER BY @.SortOrder"
> These are the other ways I thought of.
> 1) create a new procedure the same with order by catID.
> 2) pass a number and use CASE - but I get error 1008
> eg ORDER BY CASE WHEN @.Order=0 THEN Title ELSE CatID END
>
> I can see this becoming a common problem so wouldn't like to do #1
> above as that would require alot of maintence to keep them the same if
> I changed any of the table design.
> Thanks for any help.
> Al
>
> CREATE PROCEDURE dbo.usp_Help_FindFAQCategoryByCatID
> @.SelCatID TINYINT = 0
> AS
> SET NOCOUNT ON
> -- Return all the FAQ Categories with Cat ID of the selected Category
> SELECT CatID, ParentID, Title, FaqCount
> FROM Help_FAQ_Categories
> WHERE CatID=> CASE WHEN @.SelCatID=0 THEN CatID ELSE @.SelCatID END
> ORDER BY Title
> GO
>|||Harag,
it is almost correct. However, you cannot mix data types without
explicit conversions. The typical solution to this type of problem is to
use an ORDER BY column for each sort order:
ORDER BY CASE WHEN @.Order=0 THEN Title END,
CASE WHEN @.Order=1 THEN CatID END
Gert-Jan
Harag wrote:
> Hi All
> win 2k pro sql 2k IIS 5 ASP-vbscript
> I got a stored proc which basically returns a set of records depending
> on a category ID (or all if passed ID =0) - see below
> The FAQ Category below is retuned mostly order by Title but in some
> other areas of my site I need to return it orded by CatID.
> I was wondering whats the best way to do this as I cant seem to pass a
> column name to the procedure and put "ORDER BY @.SortOrder"
> These are the other ways I thought of.
> 1) create a new procedure the same with order by catID.
> 2) pass a number and use CASE - but I get error 1008
> eg ORDER BY CASE WHEN @.Order=0 THEN Title ELSE CatID END
> I can see this becoming a common problem so wouldn't like to do #1
> above as that would require alot of maintence to keep them the same if
> I changed any of the table design.
> Thanks for any help.
> Al
> CREATE PROCEDURE dbo.usp_Help_FindFAQCategoryByCatID
> @.SelCatID TINYINT = 0
> AS
> SET NOCOUNT ON
> -- Return all the FAQ Categories with Cat ID of the selected Category
> SELECT CatID, ParentID, Title, FaqCount
> FROM Help_FAQ_Categories
> WHERE CatID=> CASE WHEN @.SelCatID=0 THEN CatID ELSE @.SelCatID END
> ORDER BY Title
> GO|||Cool thanks guys this is what I wanted and will save alot of
maintence. :)
Thanks again
Al
On Sun, 07 Sep 2003 14:01:35 +0200, Gert-Jan Strik
<sorry@.toomuchspamalready.nl> wrote:
>Harag,
>it is almost correct. However, you cannot mix data types without
>explicit conversions. The typical solution to this type of problem is to
>use an ORDER BY column for each sort order:
>ORDER BY CASE WHEN @.Order=0 THEN Title END,
> CASE WHEN @.Order=1 THEN CatID END
>Gert-Jan
>
>Harag wrote:
>> Hi All
>> win 2k pro sql 2k IIS 5 ASP-vbscript
>> I got a stored proc which basically returns a set of records depending
>> on a category ID (or all if passed ID =0) - see below
>> The FAQ Category below is retuned mostly order by Title but in some
>> other areas of my site I need to return it orded by CatID.
>> I was wondering whats the best way to do this as I cant seem to pass a
>> column name to the procedure and put "ORDER BY @.SortOrder"
>> These are the other ways I thought of.
>> 1) create a new procedure the same with order by catID.
>> 2) pass a number and use CASE - but I get error 1008
>> eg ORDER BY CASE WHEN @.Order=0 THEN Title ELSE CatID END
>> I can see this becoming a common problem so wouldn't like to do #1
>> above as that would require alot of maintence to keep them the same if
>> I changed any of the table design.
>> Thanks for any help.
>> Al
>> CREATE PROCEDURE dbo.usp_Help_FindFAQCategoryByCatID
>> @.SelCatID TINYINT = 0
>> AS
>> SET NOCOUNT ON
>> -- Return all the FAQ Categories with Cat ID of the selected Category
>> SELECT CatID, ParentID, Title, FaqCount
>> FROM Help_FAQ_Categories
>> WHERE CatID=>> CASE WHEN @.SelCatID=0 THEN CatID ELSE @.SelCatID END
>> ORDER BY Title
>> GO

changing object owner syntax

Hi there,
We have a user name convention here that specifies users in the
following way:
jane.doe
john.smith

In order to change a database object I must specify the owner name along
with the object name. Does anyone know the proper syntax for doing this
with a user name that contains a period? When I put the user name
followed by the object name in quotes such as 'jane.doe.tb_test_table',
SQL Server can not find the object.
Any help is appreciated.
Kelly"Kelly Prendergast" <kelly.prendergast@.noaa.gov> wrote in message
news:403B9200.6882586F@.noaa.gov...
> Hi there,
> We have a user name convention here that specifies users in the
> following way:
> jane.doe
> john.smith
> In order to change a database object I must specify the owner name along
> with the object name. Does anyone know the proper syntax for doing this
> with a user name that contains a period? When I put the user name
> followed by the object name in quotes such as 'jane.doe.tb_test_table',
> SQL Server can not find the object.
> Any help is appreciated.
> Kelly

You will need to quote the owner name:

exec sp_changeobjectowner '[jane.doe].tb_test_table', 'dbo'

You may want to consider changing your naming convention - since the .
character delimits object name parts in MSSQL, it would probably be best to
avoid confusiong by not allowing it in user names. Although of course I
appreciate this may be beyond your control.

Simon

Changing join order based on parameters

Hi,
I keep coming across a performance problem in SQL that I feel must have
an elegant solution, I just can't find it.
The problem is quite obvious:
I have a query that performs a search based on some search parameters
which are given default values in the query of null.
I have a heirachical structure of tables which might be used in the
filtering
e.g.
SELECT *
FROM TableA
INNER JOIN TableB on FKa = FKb
INNER JOIN TableC on FKb = FKc
INNER JOIN TableD on FKc = FKd
I filter the result by adding to these join filters something like
SELECT *
FROM TableA
INNER JOIN TableB on FKa = FKb and (@.filterCol1 is null OR TableB.col1
= @.filterCol1 )
etc...
The problem comes when there are a few hundred thousand rows in each
table, and my search wants to filter on a parameter which affects
TableD. It appears from the execution plan (and indeed only seems
logical) that SQL has to join the entirity of tables A,B and C on their
foreign keys, before joining to D in order to filter. This of course
makes the query take ages.
I've got ideas of how I could get around this, but they are at best
"clunky".
for example:
1) I could have several different "selects" each joining in a
different order based on the parameters that are set.
2) I could build up a "filter" temporary table based on the parameters
passed, then only do the select once, starting with this filter table
first
3) I could come up with a nice elegant solution which currently eludes
me.
Any suggestions?
WillWill

> SELECT *
> FROM TableA
> INNER JOIN TableB on FKa = FKb and (@.filterCol1 is null OR TableB.col1
> = @.filterCol1 )
SELECT *
FROM TableA
INNER JOIN TableB on FKa = FKb OR TableB.col1 =
COALESCE(@.filterCol1,TableB.col1 )
WHERE @.filterCol1 IS NULL
"Will" <william_pegg@.yahoo.co.uk> wrote in message
news:1143715517.502232.295550@.z34g2000cwc.googlegroups.com...
> Hi,
> I keep coming across a performance problem in SQL that I feel must have
> an elegant solution, I just can't find it.
> The problem is quite obvious:
> I have a query that performs a search based on some search parameters
> which are given default values in the query of null.
> I have a heirachical structure of tables which might be used in the
> filtering
> e.g.
> SELECT *
> FROM TableA
> INNER JOIN TableB on FKa = FKb
> INNER JOIN TableC on FKb = FKc
> INNER JOIN TableD on FKc = FKd
> I filter the result by adding to these join filters something like
> SELECT *
> FROM TableA
> INNER JOIN TableB on FKa = FKb and (@.filterCol1 is null OR TableB.col1
> = @.filterCol1 )
> etc...
> The problem comes when there are a few hundred thousand rows in each
> table, and my search wants to filter on a parameter which affects
> TableD. It appears from the execution plan (and indeed only seems
> logical) that SQL has to join the entirity of tables A,B and C on their
> foreign keys, before joining to D in order to filter. This of course
> makes the query take ages.
> I've got ideas of how I could get around this, but they are at best
> "clunky".
> for example:
> 1) I could have several different "selects" each joining in a
> different order based on the parameters that are set.
> 2) I could build up a "filter" temporary table based on the parameters
> passed, then only do the select once, starting with this filter table
> first
> 3) I could come up with a nice elegant solution which currently eludes
> me.
> Any suggestions?
> Will
>|||Uri,
Thanks for replying.
I must admit I'm having trouble fully understanding what the aim of the
change is. As I see it if I pass something not null as @.filterCol1 then
I won't get any results back. This isn't what I wanted. Essentially the
point of the @.filtercol1 was to indicate that I'm using optional
parameters to filter it, so sometimes I want to filter on @.filterCol1,
sometimes the user might not select any filter for that, in which case
I'll want to filter on another variable @.filterCol2 (sorry I should
have put a fuller example in, I was abreviating it and only using one
variable). This behaviour in itself isn't causing me any problems. My
issue is that if the user only selects a parameter which filters on
tableD, I need this to be used to optimise my query. e.g.:
let's say the user selects @.filterCol1 = null and @.filterCol2 = 27
SELECT *
FROM TableA
INNER JOIN TableB on FKa = FKb and (@.filterCol1 is null or @.filterCol1
= TableB.Col1)
INNER JOIN TableC on FKb = FKc
INNER JOIN TableD on FKc = FKd and (@.filterCol2 is null or @.filterCol2
= TableD.Col2)
I need my sql query to realise that TableD is going to be the bounding
result set, evaluate this one first, then use that to restrict the
scope of the joins on the other 3 tables. However if the user were to
put a value into @.filterCol1 and null for @.filterCol2 then I would need
it to evaluate tableB first as this will be the smallest result set.
I don't think there's going to be a perfect answer, but this problem
must be encountered a lot.
Will|||Will
Well if I understood you cannot dictate to optimizer what order of joins to
be chosen. Yes, there are some JOIN hints but not in this case I think. Have
you considered ( I don't know your business requirements) to separe this
SELECT statement i mean JOIN two tables/three tables based on
parameteters that you've got?
"Will" <william_pegg@.yahoo.co.uk> wrote in message
news:1143720216.439397.65770@.t31g2000cwb.googlegroups.com...
> Uri,
> Thanks for replying.
> I must admit I'm having trouble fully understanding what the aim of the
> change is. As I see it if I pass something not null as @.filterCol1 then
> I won't get any results back. This isn't what I wanted. Essentially the
> point of the @.filtercol1 was to indicate that I'm using optional
> parameters to filter it, so sometimes I want to filter on @.filterCol1,
> sometimes the user might not select any filter for that, in which case
> I'll want to filter on another variable @.filterCol2 (sorry I should
> have put a fuller example in, I was abreviating it and only using one
> variable). This behaviour in itself isn't causing me any problems. My
> issue is that if the user only selects a parameter which filters on
> tableD, I need this to be used to optimise my query. e.g.:
> let's say the user selects @.filterCol1 = null and @.filterCol2 = 27
> SELECT *
> FROM TableA
> INNER JOIN TableB on FKa = FKb and (@.filterCol1 is null or @.filterCol1
> = TableB.Col1)
> INNER JOIN TableC on FKb = FKc
> INNER JOIN TableD on FKc = FKd and (@.filterCol2 is null or @.filterCol2
> = TableD.Col2)
> I need my sql query to realise that TableD is going to be the bounding
> result set, evaluate this one first, then use that to restrict the
> scope of the joins on the other 3 tables. However if the user were to
> put a value into @.filterCol1 and null for @.filterCol2 then I would need
> it to evaluate tableB first as this will be the smallest result set.
> I don't think there's going to be a perfect answer, but this problem
> must be encountered a lot.
> Will
>|||Uri,
Yes, splitting the select out into different ones dependent on
parameters seems to be the only way to go (but unfortunately not an
option as I won't be able to justify a potentially breaking change such
as that for performance increases). I was hoping that there would be
some nice sql trick that allows you to always hit the smaller tables
first.
Thanks for your help
Will|||If not specified otherwise (through the use of join hints) the Query
Optimizer will always choose the best order according to indexes, statistics
,
etc.
ML
http://milambda.blogspot.com/|||I know that the optimiser will try, and that I can override with join
hints, but in this case the optimiser can't help because the logic of
the join forces it to be evaluated in that order. What I was hoping for
was some kind of new way of laying out the query such that I could
"enter" my data structure from different directions depending on which
one has parameters defined to filter it. I suspect it's not possible,
but doesn't anyone else find this a problem? is there some better way
to either lay out the tables or to have filter procs with multiple
optional parameters?
Cheers
Will|||One way to filter the rows before the join is to use inline table functions,
that accept filtering values as parameters, I'm not sure, however, if this
will actually improve the performance. It's an option to consider and test.
ML
http://milambda.blogspot.com/|||"Will" <william_pegg@.yahoo.co.uk> wrote in message
news:1143723585.520821.232940@.e56g2000cwe.googlegroups.com...
>I know that the optimiser will try, and that I can override with join
> hints, but in this case the optimiser can't help because the logic of
> the join forces it to be evaluated in that order. What I was hoping for
> was some kind of new way of laying out the query such that I could
> "enter" my data structure from different directions depending on which
> one has parameters defined to filter it. I suspect it's not possible,
> but doesn't anyone else find this a problem? is there some better way
> to either lay out the tables or to have filter procs with multiple
> optional parameters?
>
The problem is that SQL Server will pick one query plan and reuse it for all
parameter sets. If this is in a stored procedure, you could try to mark it
WITH RECOMPILE, or use dynamic SQL.
David|||You have hit one of the problems with SQL Server. It was built for a
"departmetn level" database on a small machine. It keeps one exectuion
plan for each stored procedure and uses that plan when the proc is
invoked. They added parameter sniffing, but that can actually hurt.
Better products, meant for enterprise level applications and VLDB hold
multiple plans (Is DB2 keeping 16 or 32 now? I do not remember). Thus
they know that when the query looks like this (I will use the @. for
parameters even tho that is not Standard SQL):
SELECT x.y.x
FROM Foobar AS F1
WHERE F1.a = COALESCE (@.p1, a)
AND F1.b = COALESCE (@.p2, b)
AND WHERE F1.c = COALESCE (@.p3, c)
SQL needs to generate 8 execution plans, one for each combination of
NULLs. The best one will be picked at run time when we know (@.p1, @.p2,
@.p3) and twher the NULLs are that will turn a search condition into a
constant TRUE, FALSE or UNKNOWN.
This is your solution #1 -- fake a good optimizer and RDBMS engine in
T-SQL by hand. You can do it with IF-THEN-ELSE control flow in T-SQL.
It is a XXXXX to maintain, but easy to write with a cut and paste in a
text editor. It can run much faster than what you have now, however.

Changing Field/Column Name

Hello:
I'm working on a Crystal report for a client and, in order for it to work
without errors, I have to change the name of a field (column) in a table.
You see, the field name is two words called "[Print Count]". The space
between these two words, believe it or not, causes errors in my report.
So, I took out the space in this field so that the field name reads
"PrintCount". That fixed my report.
Well, unfortunately, doing this caused problems elsewhere as this name
change did not update (filter to) a SQL View that a user needs in order to
conduct his daily business activities. So, I had to go back and reverse what
I did and add that space back in.
I really did not think that changing the name of a field would cause issues.
But, like I said, changing the name in this table did not update the View
that is based on this table and other tables.
How do I change this field name to be without this space and have this
change update everywhere else effectively in the database including in this
View? I tried to change it subsequently in the View but could not figure out
how to do so.
Any insights would be appreciated!
Thanks!
childofthe1980s
> But, like I said, changing the name in this table did not update the View
> that is based on this table and other tables.
Correct. This is by design.

> How do I change this field name to be without this space and have this
> change update everywhere else effectively in the database including in this
> View?
There might exist some tools that keep control of all your objects and dependencies that can handle
this for you. There's nothing built into SQL Server or any of the tools that comes with SQL Server.

> I tried to change it subsequently in the View but could not figure out
> how to do so.
ALTER VIEW. Or, of you did SELECT * (horror), you could have used sp_refreshview.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
news:D2FF25C0-D581-4027-BA45-E2D5CC40D597@.microsoft.com...
> Hello:
> I'm working on a Crystal report for a client and, in order for it to work
> without errors, I have to change the name of a field (column) in a table.
> You see, the field name is two words called "[Print Count]". The space
> between these two words, believe it or not, causes errors in my report.
> So, I took out the space in this field so that the field name reads
> "PrintCount". That fixed my report.
> Well, unfortunately, doing this caused problems elsewhere as this name
> change did not update (filter to) a SQL View that a user needs in order to
> conduct his daily business activities. So, I had to go back and reverse what
> I did and add that space back in.
> I really did not think that changing the name of a field would cause issues.
> But, like I said, changing the name in this table did not update the View
> that is based on this table and other tables.
> How do I change this field name to be without this space and have this
> change update everywhere else effectively in the database including in this
> View? I tried to change it subsequently in the View but could not figure out
> how to do so.
> Any insights would be appreciated!
> Thanks!
> childofthe1980s
|||Is the view referencing the column name directly? If so, you'd need to
change it there too. If you're
doing a SELECT * on the table within the view, then run sp_refreshview to
update the view metadata.
For columns that have spaces, put brackets around them. ie select [print
count] from mytable
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:D2FF25C0-D581-4027-BA45-E2D5CC40D597@.microsoft.com...
> Hello:
> I'm working on a Crystal report for a client and, in order for it to work
> without errors, I have to change the name of a field (column) in a table.
> You see, the field name is two words called "[Print Count]". The space
> between these two words, believe it or not, causes errors in my report.
> So, I took out the space in this field so that the field name reads
> "PrintCount". That fixed my report.
> Well, unfortunately, doing this caused problems elsewhere as this name
> change did not update (filter to) a SQL View that a user needs in order to
> conduct his daily business activities. So, I had to go back and reverse
what
> I did and add that space back in.
> I really did not think that changing the name of a field would cause
issues.
> But, like I said, changing the name in this table did not update the View
> that is based on this table and other tables.
> How do I change this field name to be without this space and have this
> change update everywhere else effectively in the database including in
this
> View? I tried to change it subsequently in the View but could not figure
out
> how to do so.
> Any insights would be appreciated!
> Thanks!
> childofthe1980s

Changing Field/Column Name

Hello:
I'm working on a Crystal report for a client and, in order for it to work
without errors, I have to change the name of a field (column) in a table.
You see, the field name is two words called "[Print Count]". The space
between these two words, believe it or not, causes errors in my report.
So, I took out the space in this field so that the field name reads
"PrintCount". That fixed my report.
Well, unfortunately, doing this caused problems elsewhere as this name
change did not update (filter to) a SQL View that a user needs in order to
conduct his daily business activities. So, I had to go back and reverse what
I did and add that space back in.
I really did not think that changing the name of a field would cause issues.
But, like I said, changing the name in this table did not update the View
that is based on this table and other tables.
How do I change this field name to be without this space and have this
change update everywhere else effectively in the database including in this
View? I tried to change it subsequently in the View but could not figure out
how to do so.
Any insights would be appreciated!
Thanks!
childofthe1980s> But, like I said, changing the name in this table did not update the View
> that is based on this table and other tables.
Correct. This is by design.
> How do I change this field name to be without this space and have this
> change update everywhere else effectively in the database including in this
> View?
There might exist some tools that keep control of all your objects and dependencies that can handle
this for you. There's nothing built into SQL Server or any of the tools that comes with SQL Server.
> I tried to change it subsequently in the View but could not figure out
> how to do so.
ALTER VIEW. Or, of you did SELECT * (horror), you could have used sp_refreshview.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
news:D2FF25C0-D581-4027-BA45-E2D5CC40D597@.microsoft.com...
> Hello:
> I'm working on a Crystal report for a client and, in order for it to work
> without errors, I have to change the name of a field (column) in a table.
> You see, the field name is two words called "[Print Count]". The space
> between these two words, believe it or not, causes errors in my report.
> So, I took out the space in this field so that the field name reads
> "PrintCount". That fixed my report.
> Well, unfortunately, doing this caused problems elsewhere as this name
> change did not update (filter to) a SQL View that a user needs in order to
> conduct his daily business activities. So, I had to go back and reverse what
> I did and add that space back in.
> I really did not think that changing the name of a field would cause issues.
> But, like I said, changing the name in this table did not update the View
> that is based on this table and other tables.
> How do I change this field name to be without this space and have this
> change update everywhere else effectively in the database including in this
> View? I tried to change it subsequently in the View but could not figure out
> how to do so.
> Any insights would be appreciated!
> Thanks!
> childofthe1980s|||Is the view referencing the column name directly? If so, you'd need to
change it there too. If you're
doing a SELECT * on the table within the view, then run sp_refreshview to
update the view metadata.
For columns that have spaces, put brackets around them. ie select [print
count] from mytable
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:D2FF25C0-D581-4027-BA45-E2D5CC40D597@.microsoft.com...
> Hello:
> I'm working on a Crystal report for a client and, in order for it to work
> without errors, I have to change the name of a field (column) in a table.
> You see, the field name is two words called "[Print Count]". The space
> between these two words, believe it or not, causes errors in my report.
> So, I took out the space in this field so that the field name reads
> "PrintCount". That fixed my report.
> Well, unfortunately, doing this caused problems elsewhere as this name
> change did not update (filter to) a SQL View that a user needs in order to
> conduct his daily business activities. So, I had to go back and reverse
what
> I did and add that space back in.
> I really did not think that changing the name of a field would cause
issues.
> But, like I said, changing the name in this table did not update the View
> that is based on this table and other tables.
> How do I change this field name to be without this space and have this
> change update everywhere else effectively in the database including in
this
> View? I tried to change it subsequently in the View but could not figure
out
> how to do so.
> Any insights would be appreciated!
> Thanks!
> childofthe1980s