Changing the 6th character?

In a column I have some values for part names. The 6th character tells
you where the part came from, and this is the same scheme for every
single part in the database.
If I want to do something like return the basic name of a given part,
without the factory identifier character, I need to replace that
character with a '_' character. (So for instance '11256CA' and
'11265AA' and '11256MA' would all just get turned into '11256_A' and
only one row would be returned in the SELECT DISTINCT statement)
I know how to replace an instance of a given character using replace(),
but how can I alter a specific character in a string if all I know is
the index of the character within the string?
TIA,
-CS
scholzie wrote:
> In a column I have some values for part names. The 6th character tells
> you where the part came from, and this is the same scheme for every
> single part in the database.
> If I want to do something like return the basic name of a given part,
> without the factory identifier character, I need to replace that
> character with a '_' character. (So for instance '11256CA' and
> '11265AA' and '11256MA' would all just get turned into '11256_A' and
> only one row would be returned in the SELECT DISTINCT statement)
> I know how to replace an instance of a given character using
> replace(), but how can I alter a specific character in a string if
> all I know is the index of the character within the string?
> TIA,
> -CS
Declare @.s VARCHAR(10)
Set @.s = '1234567890'
SELECT STUFF(@.s, 6, 1, '_') -- 12345_7890
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Use STUFF...
SELECT STUFF('11265AA', 6, 1, '_')
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"scholzie" <scholzie@.gmail.com> wrote in message
news:1129066851.089291.37230@.o13g2000cwo.googlegro ups.com...
> In a column I have some values for part names. The 6th character tells
> you where the part came from, and this is the same scheme for every
> single part in the database.
> If I want to do something like return the basic name of a given part,
> without the factory identifier character, I need to replace that
> character with a '_' character. (So for instance '11256CA' and
> '11265AA' and '11256MA' would all just get turned into '11256_A' and
> only one row would be returned in the SELECT DISTINCT statement)
> I know how to replace an instance of a given character using replace(),
> but how can I alter a specific character in a string if all I know is
> the index of the character within the string?
> TIA,
> -CS
>
|||Wow, this is great! Someone recommended using substring and some ugly
concatenations but this works much nicer.
Thanks!
|||Why stuff and why not the replace function?
|||Got it... REPLACE doesnt take a index arugument. Sorry.

Changing the 6th character?

In a column I have some values for part names. The 6th character tells
you where the part came from, and this is the same scheme for every
single part in the database.
If I want to do something like return the basic name of a given part,
without the factory identifier character, I need to replace that
character with a '_' character. (So for instance '11256CA' and
'11265AA' and '11256MA' would all just get turned into '11256_A' and
only one row would be returned in the SELECT DISTINCT statement)
I know how to replace an instance of a given character using replace(),
but how can I alter a specific character in a string if all I know is
the index of the character within the string?
TIA,
-CSscholzie wrote:
> In a column I have some values for part names. The 6th character tells
> you where the part came from, and this is the same scheme for every
> single part in the database.
> If I want to do something like return the basic name of a given part,
> without the factory identifier character, I need to replace that
> character with a '_' character. (So for instance '11256CA' and
> '11265AA' and '11256MA' would all just get turned into '11256_A' and
> only one row would be returned in the SELECT DISTINCT statement)
> I know how to replace an instance of a given character using
> replace(), but how can I alter a specific character in a string if
> all I know is the index of the character within the string?
> TIA,
> -CS
Declare @.s VARCHAR(10)
Set @.s = '1234567890'
SELECT STUFF(@.s, 6, 1, '_') -- 12345_7890
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Use STUFF...
SELECT STUFF('11265AA', 6, 1, '_')
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"scholzie" <scholzie@.gmail.com> wrote in message
news:1129066851.089291.37230@.o13g2000cwo.googlegroups.com...
> In a column I have some values for part names. The 6th character tells
> you where the part came from, and this is the same scheme for every
> single part in the database.
> If I want to do something like return the basic name of a given part,
> without the factory identifier character, I need to replace that
> character with a '_' character. (So for instance '11256CA' and
> '11265AA' and '11256MA' would all just get turned into '11256_A' and
> only one row would be returned in the SELECT DISTINCT statement)
> I know how to replace an instance of a given character using replace(),
> but how can I alter a specific character in a string if all I know is
> the index of the character within the string?
> TIA,
> -CS
>|||Wow, this is great! Someone recommended using substring and some ugly
concatenations but this works much nicer.
Thanks!|||Why stuff and why not the replace function?|||Got it... REPLACE doesnt take a index arugument. Sorry.sql

Changing the 6th character?

In a column I have some values for part names. The 6th character tells
you where the part came from, and this is the same scheme for every
single part in the database.
If I want to do something like return the basic name of a given part,
without the factory identifier character, I need to replace that
character with a '_' character. (So for instance '11256CA' and
'11265AA' and '11256MA' would all just get turned into '11256_A' and
only one row would be returned in the SELECT DISTINCT statement)
I know how to replace an instance of a given character using replace(),
but how can I alter a specific character in a string if all I know is
the index of the character within the string?
TIA,
-CSscholzie wrote:
> In a column I have some values for part names. The 6th character tells
> you where the part came from, and this is the same scheme for every
> single part in the database.
> If I want to do something like return the basic name of a given part,
> without the factory identifier character, I need to replace that
> character with a '_' character. (So for instance '11256CA' and
> '11265AA' and '11256MA' would all just get turned into '11256_A' and
> only one row would be returned in the SELECT DISTINCT statement)
> I know how to replace an instance of a given character using
> replace(), but how can I alter a specific character in a string if
> all I know is the index of the character within the string?
> TIA,
> -CS
Declare @.s VARCHAR(10)
Set @.s = '1234567890'
SELECT STUFF(@.s, 6, 1, '_') -- 12345_7890
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Use STUFF...
SELECT STUFF('11265AA', 6, 1, '_')
--
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"scholzie" <scholzie@.gmail.com> wrote in message
news:1129066851.089291.37230@.o13g2000cwo.googlegroups.com...
> In a column I have some values for part names. The 6th character tells
> you where the part came from, and this is the same scheme for every
> single part in the database.
> If I want to do something like return the basic name of a given part,
> without the factory identifier character, I need to replace that
> character with a '_' character. (So for instance '11256CA' and
> '11265AA' and '11256MA' would all just get turned into '11256_A' and
> only one row would be returned in the SELECT DISTINCT statement)
> I know how to replace an instance of a given character using replace(),
> but how can I alter a specific character in a string if all I know is
> the index of the character within the string?
> TIA,
> -CS
>|||Wow, this is great! Someone recommended using substring and some ugly
concatenations but this works much nicer.
Thanks!|||Why stuff and why not the replace function?|||Got it... REPLACE doesnt take a index arugument. Sorry.

Changing the 6th character of a string?

In a column I have some values for part names. The 6th character tells
you where the part came from, and this is the same scheme for every
single part in the database.

If I want to do something like return the basic name of a given part,
without the factory identifier character, I need to replace that
character with a '_' character. (So for instance '11256CA' and
'11265AA' and '11256MA' would all just get turned into '11256_A' and
only one row would be returned in the SELECT DISTINCT statement)

I know how to replace an instance of a given character using replace(),
but how can I alter a specific character in a string if all I know is
the index of the character within the string?

TIA,
-CSscholzie (scholzie@.gmail.com) writes:
> In a column I have some values for part names. The 6th character tells
> you where the part came from, and this is the same scheme for every
> single part in the database.
> If I want to do something like return the basic name of a given part,
> without the factory identifier character, I need to replace that
> character with a '_' character. (So for instance '11256CA' and
> '11265AA' and '11256MA' would all just get turned into '11256_A' and
> only one row would be returned in the SELECT DISTINCT statement)
> I know how to replace an instance of a given character using replace(),
> but how can I alter a specific character in a string if all I know is
> the index of the character within the string?

Have you looked at substring()?

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||CREATE VIEW Parts (.., generic-part, ..)
AS
SELECT .. SUBSTRING part_id,1,5) + '_'[ + SUBSTRING part_id, 7, 1) ,
...
FROM Inventory;|||Thanks. I thought substring() was for finding the instance of a
character, but I guess I read wrong.

Appreciate the help!

Changing table names. Navision 3.7 - need help

Hello
I am trying to create a report set for a company with changing table names. Meaning that for each company the erp system is in operation for, the table names are different.
Mening that for one company the GL table is named like this:
[Company X$GL] while for another company the GL table is like this:
[Company Y$GL]
Should I use a stored procedure to handle it, a function... any good ideas before I start experimenting..
I have the possibility of getting the company name from the system, so thats not a problem...
hope for some good advice.David,
You could acheive this by using two reports; The first would get the appropriate company/table name, this report would contain a sub-report, passing the company/table name as a parameter. The sub-report would use a dynamic query, built from the parameter, such as;
="Select * From " & Parameter!Company.Value
You may be able to do it from one report, depending on the order in which the datasets are handled.
Hope that helps
"david" wrote:
> Hello
> I am trying to create a report set for a company with changing table names. Meaning that for each company the erp system is in operation for, the table names are different.
> Mening that for one company the GL table is named like this:
> [Company X$GL] while for another company the GL table is like this:
> [Company Y$GL]
> Should I use a stored procedure to handle it, a function... any good ideas before I start experimenting..
> I have the possibility of getting the company name from the system, so thats not a problem...
> hope for some good advice.|||Terribly sorry for having bothered you all. I was missing a white space in my table name.... *ashamed beyond belief*
"david" wrote:
> Now I tried going another way. I found a text replacing program, that simply runs through all the rdl files and replaces the company X string with the company Y string, so as to fit the database tables names.
> Now when i open the reports and try to open a dataset in the data view:
> all tables are flat, and i can only mark the All columns. When I try running the command, i get the following error:
> ADO error: Invalid object name 'compname$Value Entry'.
> Invalid object name 'Compname@.Customer'.
> Staement(s) could not be prepared. Deferred prepare could not be completed.
>
> heeeeeeeelp
> "david" wrote:
> > Thanks for the reply.
> > I will elaborate a little more on the problem.
> >
> > a Select to get all accounts in account ledger is for example:
> >
> > SELECT No_, Name
> > FROM [Company X$G_L Account]
> >
> > There fore i need to concatenate the company name with the table name. And table names may have blank spaces, so I need the [ ]
> >
> > Besides that, in the books online for reporting services it specifically says that everything need to be inline, and I have som loong select statements... what 2 do?
> >
> >
> > "Chris McGuigan" wrote:
> >
> > > David,
> > > You could acheive this by using two reports; The first would get the appropriate company/table name, this report would contain a sub-report, passing the company/table name as a parameter. The sub-report would use a dynamic query, built from the parameter, such as;
> > > ="Select * From " & Parameter!Company.Value
> > > You may be able to do it from one report, depending on the order in which the datasets are handled.
> > >
> > > Hope that helps
> > >
> > > "david" wrote:
> > >
> > > > Hello
> > > > I am trying to create a report set for a company with changing table names. Meaning that for each company the erp system is in operation for, the table names are different.
> > > >
> > > > Mening that for one company the GL table is named like this:
> > > > [Company X$GL] while for another company the GL table is like this:
> > > > [Company Y$GL]
> > > > Should I use a stored procedure to handle it, a function... any good ideas before I start experimenting..
> > > >
> > > > I have the possibility of getting the company name from the system, so thats not a problem...
> > > >
> > > > hope for some good advice.|||David,
The 1 line restrictions was removed with SP1. Any way by 1 line they really mean one sentence, so;
="SELECT * " &
"FROM [" & Parameters!Company.Value & " X$G_L Account]"
would work OK.
Regards
Chris McGuigan
"david" wrote:
> Thanks for the reply.
> I will elaborate a little more on the problem.
> a Select to get all accounts in account ledger is for example:
> SELECT No_, Name
> FROM [Company X$G_L Account]
> There fore i need to concatenate the company name with the table name. And table names may have blank spaces, so I need the [ ]
> Besides that, in the books online for reporting services it specifically says that everything need to be inline, and I have som loong select statements... what 2 do?
>
> "Chris McGuigan" wrote:
> > David,
> > You could acheive this by using two reports; The first would get the appropriate company/table name, this report would contain a sub-report, passing the company/table name as a parameter. The sub-report would use a dynamic query, built from the parameter, such as;
> > ="Select * From " & Parameter!Company.Value
> > You may be able to do it from one report, depending on the order in which the datasets are handled.
> >
> > Hope that helps
> >
> > "david" wrote:
> >
> > > Hello
> > > I am trying to create a report set for a company with changing table names. Meaning that for each company the erp system is in operation for, the table names are different.
> > >
> > > Mening that for one company the GL table is named like this:
> > > [Company X$GL] while for another company the GL table is like this:
> > > [Company Y$GL]
> > > Should I use a stored procedure to handle it, a function... any good ideas before I start experimenting..
> > >
> > > I have the possibility of getting the company name from the system, so thats not a problem...
> > >
> > > hope for some good advice.sql

Changing Table Name to Write data dynamically.

Hello there, folks.
I have 5 tables with exactly same column types, names, and constraints
but with differnt names.(e.g. table1, table2, ..., table5)
I have a stored procedure(sp_example) that writes some calculated data to
"table1".
but I would like to modify the stored procedure to write the similiar data
(but from different sources) to other tables(table2,... table5)
depending on value passed to the stored procedure
(e.g, exec sp_example '2' will write to table2, exec sp_example '3' will
write to table3 and so on)...
I have tried to use a pattern like
==============================
create procedure sp_example @.param
as
declare @.cmd as Varchar(8000)
set @.cmd = @.cmd + 'INSERT INTO ' + getTableName(@.param)
set @.cmd = @.cmd + ' SELECT * From sometable '
Exec(@.cmd)
==============================
But the problem is that stored procedure i am working with is roughly 1000
lines
and having to write "set @.cmd = @.cmd + '...'" seems like an overkill.
So is there any other way to change the "table" name only dynamically?
Thank you in advance.> I have 5 tables with exactly same column types, names, and
constraints
> but with differnt names.(e.g. table1, table2, ..., table5)
Why? Sounds like a design flaw. Use one table and add an extra column
for whatever attribute is represented by the different table names. You
can still create views with the original table names so you shouldn't
even need to change your code.
Failing that, you could consider using a partitioned view. See Books
Online for details. Good design is the right solution rather than messy
Dynamic SQL.
David Portas
SQL Server MVP
--|||> Why? Sounds like a design flaw. Use one table and add an extra column
Yeah, it seems like so but as a programmer, i have to deal with badly
designed table structures...

> Failing that, you could consider using a partitioned view. See Books
> Online for details. Good design is the right solution rather than messy
I don't think partitioned views can be applied to the problem i have after
going through the online book...

Changing Server Name And IP address

We are going to be moving our company to a new location. Changing server
names and Ip address of the servers. I am trying to find out and research
possible impact to the SQL databases we have. Any information on this links
etc would be a great help. If there are any impacts at all. This will be
servers only.Renaming a Serve
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_afterinstall_5r8f.asp
AMB
"RussN" wrote:
> We are going to be moving our company to a new location. Changing server
> names and Ip address of the servers. I am trying to find out and research
> possible impact to the SQL databases we have. Any information on this links
> etc would be a great help. If there are any impacts at all. This will be
> servers only.|||... which unfortunately doesn't mention jobs, so also look at
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/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:EEA5AE3D-5BBB-4335-99A0-71B0FB7C9556@.microsoft.com...
> Renaming a Server
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_afterinstall_5r8f.asp
>
> AMB
> "RussN" wrote:
>> We are going to be moving our company to a new location. Changing server
>> names and Ip address of the servers. I am trying to find out and research
>> possible impact to the SQL databases we have. Any information on this links
>> etc would be a great help. If there are any impacts at all. This will be
>> servers only.|||And then there is the whole cluster issue. If you have a cluster to move,
hire an expert. He will tell you to rebuild it from scratch in the new
location. :)
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e9NHYjQnFHA.3544@.TK2MSFTNGP15.phx.gbl...
> ... which unfortunately doesn't mention jobs, so also look at
> 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/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:EEA5AE3D-5BBB-4335-99A0-71B0FB7C9556@.microsoft.com...
>> Renaming a Server
>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_afterinstall_5r8f.asp
>>
>> AMB
>> "RussN" wrote:
>> We are going to be moving our company to a new location. Changing
>> server
>> names and Ip address of the servers. I am trying to find out and
>> research
>> possible impact to the SQL databases we have. Any information on this
>> links
>> etc would be a great help. If there are any impacts at all. This will
>> be
>> servers only.
>|||Good catch, Geoff. I've updated my article with a note on this. Let me know if you want me to remove
the credits, or change it. :-)
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/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:%23ZRYykQnFHA.3480@.TK2MSFTNGP10.phx.gbl...
> And then there is the whole cluster issue. If you have a cluster to move, hire an expert. He
> will tell you to rebuild it from scratch in the new location. :)
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:e9NHYjQnFHA.3544@.TK2MSFTNGP15.phx.gbl...
>> ... which unfortunately doesn't mention jobs, so also look at
>> 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/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
>> news:EEA5AE3D-5BBB-4335-99A0-71B0FB7C9556@.microsoft.com...
>> Renaming a Server
>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_afterinstall_5r8f.asp
>>
>> AMB
>> "RussN" wrote:
>> We are going to be moving our company to a new location. Changing server
>> names and Ip address of the servers. I am trying to find out and research
>> possible impact to the SQL databases we have. Any information on this links
>> etc would be a great help. If there are any impacts at all. This will be
>> servers only.
>|||Thanks for the credit.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uiBZBtQnFHA.1444@.TK2MSFTNGP10.phx.gbl...
> Good catch, Geoff. I've updated my article with a note on this. Let me
> know if you want me to remove the credits, or change it. :-)
> 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/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:%23ZRYykQnFHA.3480@.TK2MSFTNGP10.phx.gbl...
>> And then there is the whole cluster issue. If you have a cluster to
>> move, hire an expert. He will tell you to rebuild it from scratch in the
>> new location. :)
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:e9NHYjQnFHA.3544@.TK2MSFTNGP15.phx.gbl...
>> ... which unfortunately doesn't mention jobs, so also look at
>> 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/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
>> message news:EEA5AE3D-5BBB-4335-99A0-71B0FB7C9556@.microsoft.com...
>> Renaming a Server
>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_afterinstall_5r8f.asp
>>
>> AMB
>> "RussN" wrote:
>> We are going to be moving our company to a new location. Changing
>> server
>> names and Ip address of the servers. I am trying to find out and
>> research
>> possible impact to the SQL databases we have. Any information on this
>> links
>> etc would be a great help. If there are any impacts at all. This
>> will be
>> servers only.
>>
>

Changing PK Names

Hi,
I need to change the primary key names from
the 'PK_tablename' format to 'tablename_pk' format. Is
there any other place where the constraint name stored
than sysobjects and sysindexes table '
Is there an easy way to do this for 100 user tables ?
Thanks.Hi,
A PRIMARY KEY has a references in sysobjects as well as sysindexes. You can
use the below script to rename the Primary Key
sp_rename 'PK_tablename' , 'tablename_pk' , 'object'
Thanks
Hari
MCDBA
"Frank" <anonymous@.discussions.microsoft.com> wrote in message
news:15b3501c41678$2b9d1d20$a501280a@.phx
.gbl...
> Hi,
> I need to change the primary key names from
> the 'PK_tablename' format to 'tablename_pk' format. Is
> there any other place where the constraint name stored
> than sysobjects and sysindexes table '
> Is there an easy way to do this for 100 user tables ?
> Thanks.|||This may help:
DECLARE @.vTableName varchar(128)
DECLARE @.vObjectID int -- the object id of the table
DECLARE @.vIndexID smallint -- the index id of an index
DECLARE @.vIndexName sysname
DECLARE @.vNewName sysname
/*
Open Cursor over Tables which have pk indexes
*/
DECLARE table_cursor CURSOR LOCAL STATIC FOR
SELECT '[dbo].['+[name]+']' AS table_name, [id]
FROM sysobjects
WHERE xtype = 'U' AND (status & 64)=0
AND status > 0
AND [id] IN (
SELECT [id]
FROM sysindexes
WHERE indid > 0
AND indid < 255
AND (status & 64)=0
AND [name] LIKE 'PK_%')
OPEN table_cursor
FETCH table_cursor INTO @.vTableName, @.vObjectID
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
DECLARE index_cursor CURSOR LOCAL STATIC FOR
SELECT indid, [name]
FROM sysindexes
WHERE id = @.vObjectID
AND indid > 0
AND indid < 255
AND (status & 64)=0
AND [name] LIKE 'PK_%'
ORDER BY indid
OPEN index_cursor
FETCH index_cursor INTO @.vIndexID, @.vIndexName
-- IF NO INDEX, QUIT
IF @.@.FETCH_STATUS < 0
BEGIN
DEALLOCATE index_cursor
RAISERROR(15472,-1,-1) --'Object does not have any indexes.'
RETURN (0)
END
WHILE @.@.fetch_status >= 0
BEGIN
SET @.vNewname = REPLACE(LOWER(@.vIndexName), 'pk_', '') + '_pk'
/*
TODO: Change print to EXEC .. EXEC('EXEC sp_rename...)
*/
PRINT 'EXEC sp_rename ' + @.vIndexName + ', ' + @.vNewName
FETCH index_cursor INTO @.vIndexID, @.vIndexName
END
CLOSE index_cursor
DEALLOCATE index_cursor
END
FETCH NEXT FROM table_cursor INTO @.vTableName, @.vObjectID
END
CLOSE table_cursor
DEALLOCATE table_cursor
Christopher Winn
Business Intelligence Engineer
Edugration
"Frank" <anonymous@.discussions.microsoft.com> wrote in message
news:15b3501c41678$2b9d1d20$a501280a@.phx
.gbl...
> Hi,
> I need to change the primary key names from
> the 'PK_tablename' format to 'tablename_pk' format. Is
> there any other place where the constraint name stored
> than sysobjects and sysindexes table '
> Is there an easy way to do this for 100 user tables ?
> Thanks.|||Thanks.

>--Original Message--
>This may help:
>DECLARE @.vTableName varchar(128)
>DECLARE @.vObjectID int -- the object id of the table
>DECLARE @.vIndexID smallint -- the index id of an index
>DECLARE @.vIndexName sysname
>DECLARE @.vNewName sysname
>/*
> Open Cursor over Tables which have pk indexes
>*/
>DECLARE table_cursor CURSOR LOCAL STATIC FOR
> SELECT '[dbo].['+[name]+']' AS table_name, [id]
> FROM sysobjects
> WHERE xtype = 'U' AND (status & 64)=0
> AND status > 0
> AND [id] IN (
> SELECT [id]
> FROM sysindexes
> WHERE indid > 0
> AND indid < 255
> AND (status & 64)=0
> AND [name] LIKE 'PK_%')
>OPEN table_cursor
>FETCH table_cursor INTO @.vTableName, @.vObjectID
>WHILE (@.@.fetch_status <> -1)
>BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
>
> DECLARE index_cursor CURSOR LOCAL STATIC FOR
> SELECT indid, [name]
> FROM sysindexes
> WHERE id = @.vObjectID
> AND indid > 0
> AND indid < 255
> AND (status & 64)=0
> AND [name] LIKE 'PK_%'
> ORDER BY indid
> OPEN index_cursor
> FETCH index_cursor INTO @.vIndexID, @.vIndexName
> -- IF NO INDEX, QUIT
> IF @.@.FETCH_STATUS < 0
> BEGIN
> DEALLOCATE index_cursor
> RAISERROR(15472,-1,-1) --'Object does not have any
indexes.'
> RETURN (0)
> END
> WHILE @.@.fetch_status >= 0
> BEGIN
> SET @.vNewname = REPLACE(LOWER(@.vIndexName), 'pk_', '')
+ '_pk'
> /*
> TODO: Change print to EXEC .. EXEC('EXEC
sp_rename...)
>*/
>PRINT 'EXEC sp_rename ' + @.vIndexName + ', ' + @.vNewName
> FETCH index_cursor INTO @.vIndexID, @.vIndexName
> END
> CLOSE index_cursor
> DEALLOCATE index_cursor
>
> END
> FETCH NEXT FROM table_cursor INTO @.vTableName, @.vObjectID
>END
>CLOSE table_cursor
>DEALLOCATE table_cursor
>Christopher Winn
>Business Intelligence Engineer
>Edugration
>"Frank" <anonymous@.discussions.microsoft.com> wrote in
message
> news:15b3501c41678$2b9d1d20$a501280a@.phx
.gbl...
>
>.
>

Changing Machine Names MSDE

I have a local install of MSDE on my workstation. I have also defined a
linked server with another SQL Server on our network. I accepted all the
defaults when I installed MSDE and the server got named my machine name.
Everything is working great.

Our IT department now needs to change my machine name. What effect will
this have on my current SQL installation?

Help and suggestions appreciated.

Thanks,
Frank

*** Sent via Developersdex http://www.developersdex.com ***Frank Bishop (fbishop@.viper.com) writes:
> I have a local install of MSDE on my workstation. I have also defined a
> linked server with another SQL Server on our network. I accepted all the
> defaults when I installed MSDE and the server got named my machine name.
> Everything is working great.
> Our IT department now needs to change my machine name. What effect will
> this have on my current SQL installation?

If memory serves, the server name changes automatically. However,
@.@.servername usually gets messed up.

To this end to:

exec sp_dropserver 'oldname'
exec sp_addserver 'newname', 'local'

Then stop and restart SQL Server.

--
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 Logical Names

This is something I've always wondered. I know how to change the actual nam
e
of the mdf and ldf files on disk. You just detach, change the names on the
disk and reattach with the new names. But how do you change the logical
names of the mdf and ldf files, i.e. the names that you use when you do a
restore with move'CLM,
After restoring the database using "move" option, you can use "alter
database" to change the logical name of the files.
alter database northwind
modify file (name=northwind, newname=northwind_data)
go
exec sp_helpdb northwind
go
AMB
"CLM" wrote:

> This is something I've always wondered. I know how to change the actual n
ame
> of the mdf and ldf files on disk. You just detach, change the names on th
e
> disk and reattach with the new names. But how do you change the logical
> names of the mdf and ldf files, i.e. the names that you use when you do a
> restore with move'|||CLM,
After restoring the database using "move" option, you can use "alter
database" to change the logical name of the files.
alter database northwind
modify file (name=northwind, newname=northwind_data)
go
exec sp_helpdb northwind
go
AMB
"CLM" wrote:

> This is something I've always wondered. I know how to change the actual n
ame
> of the mdf and ldf files on disk. You just detach, change the names on th
e
> disk and reattach with the new names. But how do you change the logical
> names of the mdf and ldf files, i.e. the names that you use when you do a
> restore with move'

Changing Logical Names

This is something I've always wondered. I know how to change the actual name
of the mdf and ldf files on disk. You just detach, change the names on the
disk and reattach with the new names. But how do you change the logical
names of the mdf and ldf files, i.e. the names that you use when you do a
restore with move'CLM,
After restoring the database using "move" option, you can use "alter
database" to change the logical name of the files.
alter database northwind
modify file (name=northwind, newname=northwind_data)
go
exec sp_helpdb northwind
go
AMB
"CLM" wrote:
> This is something I've always wondered. I know how to change the actual name
> of the mdf and ldf files on disk. You just detach, change the names on the
> disk and reattach with the new names. But how do you change the logical
> names of the mdf and ldf files, i.e. the names that you use when you do a
> restore with move'

changing logical file names

Using SS2000 SP4. I want to create a new test database from an existing
database backup. This is the sql that I use:
RESTORE DATABASE SMC_Test
FROM disk = '\\Depts\Technology\SQLServer\Backups\Co
lu\SMS\smc_936.bak'
WITH RECOVERY,
MOVE 'SMC_Data' TO 'S:\SQL\Data\SMCLMS_Test_Data.mdf',
MOVE 'SMC_Log' TO 'L:\SQL\Logs\SMCLMS_Test_Log.ldf'
Is there a way to change the logical file names at the same time? In another
post I saw this sql:
USE master
go
ALTER DATABASE XXX MODIFY FILE
( NAME='XX_old Name',NEWNAME='XX_New Name' )
go
Do I have to run that after I restore the database or is there another way?
Thanks,
--
Dan D.You have to do it after the restore.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:1A8C3983-25E6-4762-A6D6-3C78BCC89114@.microsoft.com...
> Using SS2000 SP4. I want to create a new test database from an existing
> database backup. This is the sql that I use:
> RESTORE DATABASE SMC_Test
> FROM disk = '\\Depts\Technology\SQLServer\Backups\Co
lu\SMS\smc_936.bak'
> WITH RECOVERY,
> MOVE 'SMC_Data' TO 'S:\SQL\Data\SMCLMS_Test_Data.mdf',
> MOVE 'SMC_Log' TO 'L:\SQL\Logs\SMCLMS_Test_Log.ldf'
> Is there a way to change the logical file names at the same time? In anoth
er
> post I saw this sql:
> USE master
> go
> ALTER DATABASE XXX MODIFY FILE
> ( NAME='XX_old Name',NEWNAME='XX_New Name' )
> go
> Do I have to run that after I restore the database or is there another way
?
> Thanks,
> --
> Dan D.|||Thanks.
--
Dan D.
"Tibor Karaszi" wrote:

> You have to do it after the restore.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:1A8C3983-25E6-4762-A6D6-3C78BCC89114@.microsoft.com...
>
>

changing logical file names

Using SS2000 SP4. I want to create a new test database from an existing
database backup. This is the sql that I use:
RESTORE DATABASE SMC_Test
FROM disk = '\\Depts\Technology\SQLServer\Backups\Colu\SMS\smc_936.bak'
WITH RECOVERY,
MOVE 'SMC_Data' TO 'S:\SQL\Data\SMCLMS_Test_Data.mdf',
MOVE 'SMC_Log' TO 'L:\SQL\Logs\SMCLMS_Test_Log.ldf'
Is there a way to change the logical file names at the same time? In another
post I saw this sql:
USE master
go
ALTER DATABASE XXX MODIFY FILE
( NAME='XX_old Name',NEWNAME='XX_New Name' )
go
Do I have to run that after I restore the database or is there another way?
Thanks,
--
Dan D.You have to do it after the restore.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:1A8C3983-25E6-4762-A6D6-3C78BCC89114@.microsoft.com...
> Using SS2000 SP4. I want to create a new test database from an existing
> database backup. This is the sql that I use:
> RESTORE DATABASE SMC_Test
> FROM disk = '\\Depts\Technology\SQLServer\Backups\Colu\SMS\smc_936.bak'
> WITH RECOVERY,
> MOVE 'SMC_Data' TO 'S:\SQL\Data\SMCLMS_Test_Data.mdf',
> MOVE 'SMC_Log' TO 'L:\SQL\Logs\SMCLMS_Test_Log.ldf'
> Is there a way to change the logical file names at the same time? In another
> post I saw this sql:
> USE master
> go
> ALTER DATABASE XXX MODIFY FILE
> ( NAME='XX_old Name',NEWNAME='XX_New Name' )
> go
> Do I have to run that after I restore the database or is there another way?
> Thanks,
> --
> Dan D.|||Thanks.
--
Dan D.
"Tibor Karaszi" wrote:
> You have to do it after the restore.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:1A8C3983-25E6-4762-A6D6-3C78BCC89114@.microsoft.com...
> > Using SS2000 SP4. I want to create a new test database from an existing
> > database backup. This is the sql that I use:
> >
> > RESTORE DATABASE SMC_Test
> > FROM disk = '\\Depts\Technology\SQLServer\Backups\Colu\SMS\smc_936.bak'
> > WITH RECOVERY,
> > MOVE 'SMC_Data' TO 'S:\SQL\Data\SMCLMS_Test_Data.mdf',
> > MOVE 'SMC_Log' TO 'L:\SQL\Logs\SMCLMS_Test_Log.ldf'
> >
> > Is there a way to change the logical file names at the same time? In another
> > post I saw this sql:
> > USE master
> > go
> > ALTER DATABASE XXX MODIFY FILE
> > ( NAME='XX_old Name',NEWNAME='XX_New Name' )
> > go
> >
> > Do I have to run that after I restore the database or is there another way?
> >
> > Thanks,
> > --
> > Dan D.
>
>

Changing file names using xp_cmdshell

My goal is to run a script like the one below but for every time I run it I get a new year. For instance if I run it this year I'll get 2004.txt for my file name and next year I'll get 2005.txt for my file name. Is this possible? If so any suggestions.
declare @.firstyr int,
@.var sysname,
@.cmd sysname
select @.firstyr = YEAR ( getdate() )
SET @.var = 'c:\temp\' + convert (varchar(4), @.firstyr)
select @.var
SET @.cmd = @.var + '.txt'
select @.cmd
exec master.dbo.xp_cmdshell 'osql -Sserver -Usa -Ppass -o @.cmd -Q" set nocount on;select top 20 OrderId, CompanyName, OrderDate from Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId = C.CustomerId order by OrderDate desc"' , no_outpu
t
You can do that with dynamic sql, something like this:
declare @.firstyr int,
@.var varchar(1000),
@.cmd varchar(1000),
@.dcmd varchar(1000)
select @.firstyr = YEAR ( getdate() )
SET @.var = 'c:\temp\' + convert (varchar(4), @.firstyr)
select @.var
SET @.cmd = @.var + '.txt'
select @.cmd
--set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sserver -Usa -Ppass -o '
+ rtrim(@.cmd) + ' -Q" set nocount on;select top 20 OrderId, CompanyName,
OrderDate from Northwind.dbo.Orders O join Northwind.dbo.Customers C on
O.CustomerId = C.CustomerId order by OrderDate desc"'' , no_output'
set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -S. -E -o ' + rtrim(@.cmd) +
' -Q" set nocount on;select top 20 OrderId, CompanyName, OrderDate from
Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId =
C.CustomerId order by OrderDate desc"'' , no_output'
print @.dcmd
exec (@.dcmd)
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"D" <D@.discussions.microsoft.com> wrote in message
news:E7C149D3-BA9D-4CC0-A669-EA0C3EAA0633@.microsoft.com...
> My goal is to run a script like the one below but for every time I run it
I get a new year. For instance if I run it this year I'll get 2004.txt for
my file name and next year I'll get 2005.txt for my file name. Is this
possible? If so any suggestions.
> declare @.firstyr int,
> @.var sysname,
> @.cmd sysname
> select @.firstyr = YEAR ( getdate() )
> SET @.var = 'c:\temp\' + convert (varchar(4), @.firstyr)
> select @.var
> SET @.cmd = @.var + '.txt'
> select @.cmd
> exec master.dbo.xp_cmdshell 'osql -Sserver -Usa -Ppass -o @.cmd -Q" set
nocount on;select top 20 OrderId, CompanyName, OrderDate from
Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId =
C.CustomerId order by OrderDate desc"' , no_output
>
>
|||That worked. Thanks for your help. Final example below>>>
declare @.firstyr int,
@.var varchar(1000),
@.cmd varchar(1000),
@.dcmd varchar(1000)
select @.firstyr = YEAR ( getdate() )
SET @.var = 'c:\temp\' + convert (varchar(4), @.firstyr)
select @.var
SET @.cmd = @.var + '.txt'
select @.cmd
set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sserver -E -o ' + rtrim(@.cmd) + ' -Q" set nocount on;select top 20 OrderId, CompanyName, OrderDate from Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId = C.CustomerId order by OrderDa
te desc"'' , no_output'
print @.dcmd
exec (@.dcmd)
"Gregory A. Larsen" wrote:

> You can do that with dynamic sql, something like this:
> declare @.firstyr int,
> @.var varchar(1000),
> @.cmd varchar(1000),
> @.dcmd varchar(1000)
> select @.firstyr = YEAR ( getdate() )
> SET @.var = 'c:\temp\' + convert (varchar(4), @.firstyr)
> select @.var
> SET @.cmd = @.var + '.txt'
> select @.cmd
> --set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sserver -Usa -Ppass -o '
> + rtrim(@.cmd) + ' -Q" set nocount on;select top 20 OrderId, CompanyName,
> OrderDate from Northwind.dbo.Orders O join Northwind.dbo.Customers C on
> O.CustomerId = C.CustomerId order by OrderDate desc"'' , no_output'
> set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -S. -E -o ' + rtrim(@.cmd) +
> ' -Q" set nocount on;select top 20 OrderId, CompanyName, OrderDate from
> Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId =
> C.CustomerId order by OrderDate desc"'' , no_output'
> print @.dcmd
> exec (@.dcmd)
> --
> ----
> ----
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "D" <D@.discussions.microsoft.com> wrote in message
> news:E7C149D3-BA9D-4CC0-A669-EA0C3EAA0633@.microsoft.com...
> I get a new year. For instance if I run it this year I'll get 2004.txt for
> my file name and next year I'll get 2005.txt for my file name. Is this
> possible? If so any suggestions.
> nocount on;select top 20 OrderId, CompanyName, OrderDate from
> Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId =
> C.CustomerId order by OrderDate desc"' , no_output
>
>

Changing file names using xp_cmdshell

My goal is to run a script like the one below but for every time I run it I
get a new year. For instance if I run it this year I'll get 2004.txt for my
file name and next year I'll get 2005.txt for my file name. Is this possib
le? If so any suggestions.
declare @.firstyr int,
@.var sysname,
@.cmd sysname
select @.firstyr = YEAR ( getdate() )
SET @.var = 'c:\temp\' + convert (varchar(4), @.firstyr)
select @.var
SET @.cmd = @.var + '.txt'
select @.cmd
exec master.dbo.xp_cmdshell 'osql -Sserver -Usa -Ppass -o @.cmd -Q" set noco
unt on;select top 20 OrderId, CompanyName, OrderDate from Northwind.dbo.Orde
rs O join Northwind.dbo.Customers C on O.CustomerId = C.CustomerId order by
OrderDate desc"' , no_outpu
tYou can do that with dynamic sql, something like this:
declare @.firstyr int,
@.var varchar(1000),
@.cmd varchar(1000),
@.dcmd varchar(1000)
select @.firstyr = YEAR ( getdate() )
SET @.var = 'c:\temp' + convert (varchar(4), @.firstyr)
select @.var
SET @.cmd = @.var + '.txt'
select @.cmd
--set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sserver -Usa -Ppass -o '
+ rtrim(@.cmd) + ' -Q" set nocount on;select top 20 OrderId, CompanyName,
OrderDate from Northwind.dbo.Orders O join Northwind.dbo.Customers C on
O.CustomerId = C.CustomerId order by OrderDate desc"'' , no_output'
set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -S. -E -o ' + rtrim(@.cmd) +
' -Q" set nocount on;select top 20 OrderId, CompanyName, OrderDate from
Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId =
C.CustomerId order by OrderDate desc"'' , no_output'
print @.dcmd
exec (@.dcmd)
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"D" <D@.discussions.microsoft.com> wrote in message
news:E7C149D3-BA9D-4CC0-A669-EA0C3EAA0633@.microsoft.com...
> My goal is to run a script like the one below but for every time I run it
I get a new year. For instance if I run it this year I'll get 2004.txt for
my file name and next year I'll get 2005.txt for my file name. Is this
possible? If so any suggestions.
> declare @.firstyr int,
> @.var sysname,
> @.cmd sysname
> select @.firstyr = YEAR ( getdate() )
> SET @.var = 'c:\temp' + convert (varchar(4), @.firstyr)
> select @.var
> SET @.cmd = @.var + '.txt'
> select @.cmd
> exec master.dbo.xp_cmdshell 'osql -Sserver -Usa -Ppass -o @.cmd -Q" set
nocount on;select top 20 OrderId, CompanyName, OrderDate from
Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId =
C.CustomerId order by OrderDate desc"' , no_output
>
>|||That worked. Thanks for your help. Final example below>>>
declare @.firstyr int,
@.var varchar(1000),
@.cmd varchar(1000),
@.dcmd varchar(1000)
select @.firstyr = YEAR ( getdate() )
SET @.var = 'c:\temp' + convert (varchar(4), @.firstyr)
select @.var
SET @.cmd = @.var + '.txt'
select @.cmd
set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sserver -E -o ' + rtrim(@.c
md) + ' -Q" set nocount on;select top 20 OrderId, CompanyName, OrderDate fro
m Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId = C.
CustomerId order by OrderDa
te desc"'' , no_output'
print @.dcmd
exec (@.dcmd)
"Gregory A. Larsen" wrote:

> You can do that with dynamic sql, something like this:
> declare @.firstyr int,
> @.var varchar(1000),
> @.cmd varchar(1000),
> @.dcmd varchar(1000)
> select @.firstyr = YEAR ( getdate() )
> SET @.var = 'c:\temp' + convert (varchar(4), @.firstyr)
> select @.var
> SET @.cmd = @.var + '.txt'
> select @.cmd
> --set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sserver -Usa -Ppass -o
'
> + rtrim(@.cmd) + ' -Q" set nocount on;select top 20 OrderId, CompanyName,
> OrderDate from Northwind.dbo.Orders O join Northwind.dbo.Customers C on
> O.CustomerId = C.CustomerId order by OrderDate desc"'' , no_output'
> set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -S. -E -o ' + rtrim(@.cmd)
+
> ' -Q" set nocount on;select top 20 OrderId, CompanyName, OrderDate from
> Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId =
> C.CustomerId order by OrderDate desc"'' , no_output'
> print @.dcmd
> exec (@.dcmd)
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "D" <D@.discussions.microsoft.com> wrote in message
> news:E7C149D3-BA9D-4CC0-A669-EA0C3EAA0633@.microsoft.com...
> I get a new year. For instance if I run it this year I'll get 2004.txt fo
r
> my file name and next year I'll get 2005.txt for my file name. Is this
> possible? If so any suggestions.
> nocount on;select top 20 OrderId, CompanyName, OrderDate from
> Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId =
> C.CustomerId order by OrderDate desc"' , no_output
>
>

Changing Field names is SQL Server 2000

Why is it that when you change a field name is SQL Server it sometimes completely messes things up. I renamed a field in one of my tables from Emp# to EmpNumber. I had a view based on this table and naturally I knew I would have to change a view I had based on the table. I opened the view and changed the field from Emp# to EmpNumber but when I tried to open the view I got an error Invalid column Name EMP#. I have not been able to fix this. I have dropped and recreated the view, refreshed all the objects using enterprise manager, refreshed all the objects using Query Analyzer, shut down and restarted my computer, taken my database offline and put it back on line. The field name EMP# is not in any tables in my database and not referenced any views or procs . I am just starting on this database so I could verify this very easily because I only have a few views and procs.

Has anyone had this problem or more importantly does anyone know why this is happening or how to fix it?Not had this issue before. Did you try querying syscolumns for Emp#?

Regards,

hmscott|||I just queried syscolumns and there was a field name Emp# in an adhoc view that was unrelated to any other queries. I deleted the adhoc view but I'm still getting the the error on my other view for invalid column name Emp#.

One thing I didn't mention in my other post is that I'm also using an MS Access ADP with this database. I've heard that access can sometimes cause problems with SQL Server. I've been making view, procs and table design in both MS Access and SQL Server - it depends on what I'm doing. I don't Access could possibly cause this kind of problems.

Any other advice or possible solutions to this problem would be appreciated.|||When you say you opened the view and changed the view, what were you using? Where is the error message coming from? From Access, from SSMS (or SQL EM)?

Is there an associated error number?

If you are using MS Access and the view is defined as a linked table, you will have to delete and recreate the link.

Regards,

hmscott|||Thanks for your imput hmscott but Im fairly new to SQL Server so I really dont know what youre talking about when you say SSMS (or SQL EM)?

I dont remember whether or not I actually changed the view in Access or Enterprise manager. Im using an Access project that connects directly to the SQL Server database, its not like ODBC linked tables, I can make changes to any of the sql database objects directly through the Access UI.

I dont receive an error number , all I get is the error message Invalid field name. I get the same error in Access when I try to open the view -- when I try to open the view in enterprise manager -- and I have even copied the sql statement for the view and run it in Query analyzer and still get the same error.

Im completely baffled.

Thanks,
GEM|||As was said, you need to drop and recreate the table link. This is an Access problem, not a SQL Server problem.|||As I said before in my previous post - there is no link to drop. This is a MS Access Project (an ADP) not an ms access database (an .mdb) that has SQL tables and views linked via ODBC. If you are not familar with what an Access Project(ADP) is, then check it out - it's different than just having ODBC linked tables and views.

I get the same error regardless of whether it's through the MS Access Project(ADP), enterprise manager or run the sql through query analyzer....|||Sorry. Did not notice you were using an Access Data Project.

Post the code for the view that is giving you the error.|||Thanks for your imput hmscott but Im fairly new to SQL Server so I really dont know what youre talking about when you say SSMS (or SQL EM)?EM = ENterprise Manager (SQL 2000). SSMS = SQL Server Management Studio (SQL 2005). In case anyone blindsides with another, QA = Query Analyser (SQL 2000) :)|||EM = ENterprise Manager (SQL 2000). SSMS = SQL Server Management Studio (SQL 2005). In case anyone blindsides with another, QA = Query Analyser (SQL 2000) :)

EMP - Elecromagnetic Pulse
BOHICA - Bend Over, Here It Comes Again
OSWATB - Oh, sh##! Where are the backups?

:D

Regards,

hmscott

Changing Default Names

I am planning to change the default names from
'DF__tablename__columnname_28F7FFC9'to
'tablename_columnname_df'.
Depending on the number of default columns 'df1, df2,
df3 ....e.t.c' will be added.
I am going to rename them in the 'sysobjects' table where
I see the only location for them.
Is there any side effects to this ?.
Thanks for any help.Hard to say if there are side effects; it's not the supported way of
renaming objects. Instead, use sp_rename. Look up syntax in BOL.
"Dave" <anonymous@.discussions.microsoft.com> wrote in message
news:1444801c4182b$45f41160$a601280a@.phx
.gbl...
> I am planning to change the default names from
> 'DF__tablename__columnname_28F7FFC9'to
> 'tablename_columnname_df'.
> Depending on the number of default columns 'df1, df2,
> df3 ....e.t.c' will be added.
> I am going to rename them in the 'sysobjects' table where
> I see the only location for them.
> Is there any side effects to this ?.
> Thanks for any help.|||I can not use sp_rename because the defaults are not
recognized as objects.

>--Original Message--
>Hard to say if there are side effects; it's not the
supported way of
>renaming objects. Instead, use sp_rename. Look up
syntax in BOL.
>
>"Dave" <anonymous@.discussions.microsoft.com> wrote in
message
> news:1444801c4182b$45f41160$a601280a@.phx
.gbl...
where
>
>.
>|||It works for me... Does the following work for you:
create table a(id int)
GO
create table b (id int)
GO
alter table b add constraint r_b check (id = 1)
GO
sp_rename 'r_b', 'r_c', 'object'
GO
"Dave" <anonymous@.discussions.microsoft.com> wrote in message
news:1722a01c4182f$5d987aa0$a401280a@.phx
.gbl...
> I can not use sp_rename because the defaults are not
> recognized as objects.
>
> supported way of
> syntax in BOL.
> message
> where|||sorry, that was a CHECK constraint... here's a default:
create table a(id int)
GO
create table b (id int)
GO
alter table b add constraint r_b default (1) for id
GO
sp_rename 'r_b', 'r_c', 'object'
GO
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:#oIt9BDGEHA.4044@.TK2MSFTNGP10.phx.gbl...
> It works for me... Does the following work for you:
>
> create table a(id int)
> GO
> create table b (id int)
> GO
> alter table b add constraint r_b check (id = 1)
> GO
> sp_rename 'r_b', 'r_c', 'object'
> GO
> "Dave" <anonymous@.discussions.microsoft.com> wrote in message
> news:1722a01c4182f$5d987aa0$a401280a@.phx
.gbl...
>|||NO. Here is what I am running:
sp_rename 'DF__admuserpr__origi__2B0043CC', 'admuserpr_orig
i_df', 'object'
Here is what I get:
Server: Msg 15248, Level 11, State 1, Procedure sp_rename,
Line 223
Either the parameter @.objname is ambiguous or the claimed
@.objtype (object) is wrong.

>--Original Message--
>sorry, that was a CHECK constraint... here's a default:
>
>create table a(id int)
>GO
>create table b (id int)
>GO
>alter table b add constraint r_b default (1) for id
>GO
>sp_rename 'r_b', 'r_c', 'object'
>GO
>"Adam Machanic" <amachanic@.air-
worldwide.nospamallowed.com> wrote in message
>news:#oIt9BDGEHA.4044@.TK2MSFTNGP10.phx.gbl...
message
df2,
>
>.
>|||I just tried the same thing on my server, with no problem. Created the
default using the DEFAULT(n) syntax...
What version are you running?
"Dave" <anonymous@.discussions.microsoft.com> wrote in message
news:1725d01c41833$5dffcb20$a401280a@.phx
.gbl...
> NO. Here is what I am running:
> sp_rename 'DF__admuserpr__origi__2B0043CC', 'admuserpr_orig
> i_df', 'object'
>
> Here is what I get:
> Server: Msg 15248, Level 11, State 1, Procedure sp_rename,
> Line 223
> Either the parameter @.objname is ambiguous or the claimed
> @.objtype (object) is wrong.
>
>
> worldwide.nospamallowed.com> wrote in message
> message
> df2,|||Hi Dave
Is it possible the constraint has a different owner than the user you are
currently connected as?
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Dave" <anonymous@.discussions.microsoft.com> wrote in message
news:1725d01c41833$5dffcb20$a401280a@.phx
.gbl...
> NO. Here is what I am running:
> sp_rename 'DF__admuserpr__origi__2B0043CC', 'admuserpr_orig
> i_df', 'object'
>
> Here is what I get:
> Server: Msg 15248, Level 11, State 1, Procedure sp_rename,
> Line 223
> Either the parameter @.objname is ambiguous or the claimed
> @.objtype (object) is wrong.
>
>
> worldwide.nospamallowed.com> wrote in message
> message
> df2,|||I am connecting as sysadmin.

>--Original Message--
>Hi Dave
>Is it possible the constraint has a different owner than
the user you are
>currently connected as?
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"Dave" <anonymous@.discussions.microsoft.com> wrote in
message
> news:1725d01c41833$5dffcb20$a401280a@.phx
.gbl...
sp_rename 'DF__admuserpr__origi__2B0043CC', 'admuserpr_orig
sp_rename,
claimed
not
the
up
wrote in
table
>
>.
>|||SQL Server 2000 SP3.

>--Original Message--
>I just tried the same thing on my server, with no
problem. Created the
>default using the DEFAULT(n) syntax...
>What version are you running?
>
>"Dave" <anonymous@.discussions.microsoft.com> wrote in
message
> news:1725d01c41833$5dffcb20$a401280a@.phx
.gbl...
sp_rename 'DF__admuserpr__origi__2B0043CC', 'admuserpr_orig
sp_rename,
claimed
not
the
up
wrote in
table
>
>.
>

Changing Default Names

I am planning to change the default names from
'DF__tablename__columnname_28F7FFC9'to
'tablename_columnname_df'.
Depending on the number of default columns 'df1, df2,
df3 ....e.t.c' will be added.
I am going to rename them in the 'sysobjects' table where
I see the only location for them.
Is there any side effects to this ?.
Thanks for any help.
Hard to say if there are side effects; it's not the supported way of
renaming objects. Instead, use sp_rename. Look up syntax in BOL.
"Dave" <anonymous@.discussions.microsoft.com> wrote in message
news:1444801c4182b$45f41160$a601280a@.phx.gbl...
> I am planning to change the default names from
> 'DF__tablename__columnname_28F7FFC9'to
> 'tablename_columnname_df'.
> Depending on the number of default columns 'df1, df2,
> df3 ....e.t.c' will be added.
> I am going to rename them in the 'sysobjects' table where
> I see the only location for them.
> Is there any side effects to this ?.
> Thanks for any help.
|||I can not use sp_rename because the defaults are not
recognized as objects.

>--Original Message--
>Hard to say if there are side effects; it's not the
supported way of
>renaming objects. Instead, use sp_rename. Look up
syntax in BOL.
>
>"Dave" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1444801c4182b$45f41160$a601280a@.phx.gbl...
where
>
>.
>
|||It works for me... Does the following work for you:
create table a(id int)
GO
create table b (id int)
GO
alter table b add constraint r_b check (id = 1)
GO
sp_rename 'r_b', 'r_c', 'object'
GO
"Dave" <anonymous@.discussions.microsoft.com> wrote in message
news:1722a01c4182f$5d987aa0$a401280a@.phx.gbl...
> I can not use sp_rename because the defaults are not
> recognized as objects.
>
> supported way of
> syntax in BOL.
> message
> where
|||sorry, that was a CHECK constraint... here's a default:
create table a(id int)
GO
create table b (id int)
GO
alter table b add constraint r_b default (1) for id
GO
sp_rename 'r_b', 'r_c', 'object'
GO
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:#oIt9BDGEHA.4044@.TK2MSFTNGP10.phx.gbl...
> It works for me... Does the following work for you:
>
> create table a(id int)
> GO
> create table b (id int)
> GO
> alter table b add constraint r_b check (id = 1)
> GO
> sp_rename 'r_b', 'r_c', 'object'
> GO
> "Dave" <anonymous@.discussions.microsoft.com> wrote in message
> news:1722a01c4182f$5d987aa0$a401280a@.phx.gbl...
>
|||NO. Here is what I am running:
sp_rename 'DF__admuserpr__origi__2B0043CC', 'admuserpr_orig
i_df', 'object'
Here is what I get:
Server: Msg 15248, Level 11, State 1, Procedure sp_rename,
Line 223
Either the parameter @.objname is ambiguous or the claimed
@.objtype (object) is wrong.

>--Original Message--
>sorry, that was a CHECK constraint... here's a default:
>
>create table a(id int)
>GO
>create table b (id int)
>GO
>alter table b add constraint r_b default (1) for id
>GO
>sp_rename 'r_b', 'r_c', 'object'
>GO
>"Adam Machanic" <amachanic@.air-
worldwide.nospamallowed.com> wrote in message
>news:#oIt9BDGEHA.4044@.TK2MSFTNGP10.phx.gbl...
message
df2,
>
>.
>
|||I just tried the same thing on my server, with no problem. Created the
default using the DEFAULT(n) syntax...
What version are you running?
"Dave" <anonymous@.discussions.microsoft.com> wrote in message
news:1725d01c41833$5dffcb20$a401280a@.phx.gbl...
> NO. Here is what I am running:
> sp_rename 'DF__admuserpr__origi__2B0043CC', 'admuserpr_orig
> i_df', 'object'
>
> Here is what I get:
> Server: Msg 15248, Level 11, State 1, Procedure sp_rename,
> Line 223
> Either the parameter @.objname is ambiguous or the claimed
> @.objtype (object) is wrong.
>
> worldwide.nospamallowed.com> wrote in message
> message
> df2,
|||Hi Dave
Is it possible the constraint has a different owner than the user you are
currently connected as?
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Dave" <anonymous@.discussions.microsoft.com> wrote in message
news:1725d01c41833$5dffcb20$a401280a@.phx.gbl...
> NO. Here is what I am running:
> sp_rename 'DF__admuserpr__origi__2B0043CC', 'admuserpr_orig
> i_df', 'object'
>
> Here is what I get:
> Server: Msg 15248, Level 11, State 1, Procedure sp_rename,
> Line 223
> Either the parameter @.objname is ambiguous or the claimed
> @.objtype (object) is wrong.
>
> worldwide.nospamallowed.com> wrote in message
> message
> df2,
|||I am connecting as sysadmin.

>--Original Message--
>Hi Dave
>Is it possible the constraint has a different owner than
the user you are
>currently connected as?
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"Dave" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1725d01c41833$5dffcb20$a401280a@.phx.gbl...
sp_rename 'DF__admuserpr__origi__2B0043CC', 'admuserpr_orig
sp_rename,
claimed
not
the
up
wrote in
table
>
>.
>
|||SQL Server 2000 SP3.

>--Original Message--
>I just tried the same thing on my server, with no
problem. Created the
>default using the DEFAULT(n) syntax...
>What version are you running?
>
>"Dave" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1725d01c41833$5dffcb20$a401280a@.phx.gbl...
sp_rename 'DF__admuserpr__origi__2B0043CC', 'admuserpr_orig
sp_rename,
claimed
not
the
up
wrote in
table
>
>.
>

Changing default name in tasks ?

Best practice talks about the use of name conventions in the SSIS packages.

Is there a way to rename the default names of the tasks in the DataFlow and ControlFlow once and for all, so that each time I drag a new DataFlowTask in, the default name will be DFT and so on.

I suppose that programatically when you instantiates from ..Dts.Runtime.Package is possible (pkg.Connections.Item(0).Name...)

That's a good question.

|||

jam281 wrote:

Best practice talks about the use of name conventions in the SSIS packages.

Is there a way to rename the default names of the tasks in the DataFlow and ControlFlow once and for all, so that each time I drag a new DataFlowTask in, the default name will be DFT and so on.

Unfortunately not. That would be a nice-to-have though. perhaps you should suggest it at Microsoft Connect?

-Jamie

|||I think it would be a nice features, since every company have their own way of using name conventions - though i'm using yours Jamie :-)|||

jam281 wrote:

I think it would be a nice features, since every company have their own way of using name conventions - though i'm using yours Jamie :-)

That's good to know :)

Thanks

-Jamie

Changing constraintnames.

Although constraint names are of no consequence to a 'working'
database, they sometimes pose a problem when managing
databases.
So we try to keep control over het names of constraints.
Now we are changing the name of a table.
Should we alter al the constraintnames so that
the tablename in the constraintname reflects the
actual table, or should we leave the 'original' tablename.
Any thoughts about that ?
Is there an easy way to manage constraintnames, if the
database is implemented at several customers sites ?
Thanks for you attention,
ben brugmanAccording to the documentation, you should be able to rename a constraint us
ing sp_rename.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ben brugman" <ben@.niethier.nl> wrote in message news:eCo2wR%23pFHA.3160@.TK2MSFTNGP14.phx.gb
l...
> Although constraint names are of no consequence to a 'working'
> database, they sometimes pose a problem when managing
> databases.
> So we try to keep control over het names of constraints.
> Now we are changing the name of a table.
> Should we alter al the constraintnames so that
> the tablename in the constraintname reflects the
> actual table, or should we leave the 'original' tablename.
> Any thoughts about that ?
> Is there an easy way to manage constraintnames, if the
> database is implemented at several customers sites ?
> Thanks for you attention,
> ben brugman
>