Changing Field Names Is SQL Server 2000
May 23, 2007
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?
View 9 Replies
ADVERTISEMENT
Jan 27, 2008
This is a followup to my last post. If a field name is changed in the database, what is the easiest way to determine what stored procedures and triggers that reference that field are now broken?
View 3 Replies
View Related
Jan 23, 2008
I changed the name of a server that runs SQL Server 2005 and Reporting Services web site. I can't connect back to reporting services now using the new server name. I had to do some things to get SQL Server and Analysis Services to work, but I can't find any information on getting Reporting Services to run, I found something about changing config files, but no direction on what those files are.
Thanks for any help,
JB
View 4 Replies
View Related
May 1, 2007
If I have stored procedures that reference linked servers, is there any way to avoid changing the procedures when moving between environments (dev, qa, prod)?
Do i resort to dynamic sql? Use case statements to switch between environments?
Any experience or advice is appreciated.
View 1 Replies
View Related
Jan 21, 2004
I'm trying to do an update query that looks like this:
UPDATE
PAEMPLOYEE
SET PAEMPLOYEE.LOCAT_CODE = EMPLOYEE.PROCESS_LEVEL
FROM
PAEMPLOYEE A
JOIN EMPLOYEE B ON A.EMPLOYEE = B.EMPLOYEE
It's erroring out on the Employee prefix B.EMPLOYEE saying:
..."does not match with a table name or alias name used in the query"
Is it wrong or will it cause problems to have a field name the same as the table name?
View 5 Replies
View Related
Jul 8, 2014
I have created a table name "tblPopulation" with fields 'Country', 'State' etc....
When i query it with table name as tblpopulation with fields 'country', 'state' then i get error as invalid object name.
View 9 Replies
View Related
Nov 18, 2014
I am trying to replace names found in 'xml' field name as hyperlinks, by matching with the names in database_tags.I am using the following function below, however the UDF is only recognizing one name from the XML fieldname data, instead of all the names present in the XML data.
ALTER FUNCTION [dbo].[ReplaceTags](@XML VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
[code]...
for example, if the XML input data is the following: It consists of: BANKP, BCJA, BCJAM, BFTH, BFTH, and EMPOP.But the updated function is only recognizing two of names BFTH, BFTH, as hyperlinks, from the database_tags table.Is there a way to get the function to recognize more than one names as hyperlinks.
View 1 Replies
View Related
Apr 9, 2014
Is this a correct syntax to populate a field name PHONES in my CUSTOMERS TABLE
case when(d.phone = (SELECT phone from CALLS where exists(select home_phone, mobile, toll_free from CALLS2) Then 1 END 'PHONES'
View 1 Replies
View Related
Mar 11, 2006
I have a local install of MSDE on my workstation. I have also defined alinked server with another SQL Server on our network. I accepted all thedefaults 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 willthis have on my current SQL installation?Help and suggestions appreciated.Thanks,Frank*** Sent via Developersdex http://www.developersdex.com ***
View 1 Replies
View Related
Aug 8, 2007
Hello Everyone,
I am working on a dtsx package wherein i am sending the data from OLE DB Source (SQL Server) to OLE DB Destination (Oracle). For development purpose i use DEVLOPMENT environment on oracle but for unit testing i have to use QA or Some other Schema. when i use DEVELOPMENT Schema in ole db destination, tables are accessed under Schema name eg. "DEVELOPMENT"."EMPLOYEE", but when i m chenging schema name to QA table names are not changing as "QA"."EMPLOYEE". Data Flow Task is pushing the data to DEVELOPMENT environment only.
Can Anyone suggest me any remedy for it ?
Or this is one more BUG in SQL Server 2005.
Advice and suggestions are highly appreciated !
Thanks
View 3 Replies
View Related
Jun 19, 2007
Hi.
I don't know how to change the collation in SQL Server 2000, my collation is Modern_Spanish_CI_AS, if somedoby knows the procedure to change the collation I would be thankful.
Thank you again and best regards.
Christian
View 1 Replies
View Related
Jul 23, 2005
Dear All,I have one field in my table which shows the day of the week. It isshowingSunday = 1Monday = 2Tuesday = 3But i want Monday is the first day. I know i can use DATEFIRST tochange it but it works in Query Analyzerbut when i come and see mytable it is showing the old settings. Is it possible i can change itpermanently. Any help in this regard will be higly appreciatedRegardsS
View 2 Replies
View Related
Oct 25, 2006
I'm trying to write an SSIS package that exports a table that has changing column names to an excel file. The column names change due to the fact that the table is created by a pivot daily. the only thing I'm missing is the ability to dynamically map the tables' columns to the excel destination. Is this possible?
I read in another thread that
"It is not possible to create packages or new objects within packages using SSIS."
I also read in the books online that "The input and the input columns of the Excel destination have no custom
properties." To me this means that I cannot programmatically create or remove columns in the excel destination. Please tell me I'm wrong.
So, to summarize my research so far. In writing an SSIS package, I cannot programmatically create a new excel destination object and I can't manipulate an existing one. I hope I'm wrong. Can anyone help me? (and please correct any wrong assumptions I may have stated)
View 2 Replies
View Related
Dec 26, 2007
Greetings,
I have been tasked with changing our local domian name from .com to .local. I want to make sure I understand the risks to SQL server 2000 when i make this change. We use SQL for Great Plains version 8, here is my simple plan:
1. Dis-join all workstations from .com domain
2. Make a full backup of all databases iin SQL server 2000 -- all databases use the SA account and not NT authentication
3. Dis-join SQL server
4. Change domain name
5. Re-join SQL server box and workstations
6. Launch Great Plains and go home happy!
I am having trouble identifying the risk to my plan and am wondering if:
1. SQL will launch under the new domain
2. Will the backup I made restore under the new domian
3. Will I experience authentication problems even though we use the SA account?
I am not a DB admin and am feeling a little unsure about this task any help on the risks or links to "how to" guides would be appreciated.
Robert
View 4 Replies
View Related
Sep 29, 2006
Hi,
Our company wants to allow our customers the ability to import employee data. Each customer's employee data changes depending on things like organisation structure etc. so the format of the data to be imported needs to change. We can do a lot of this with dynamic SQL, but are looking at moving it to SSIS as we think it will save us a lot of pain later on and for other features in SSIS.
We're stuck at a fairly early part of the process as we don't know how to dynamically import our spreadsheet. So far our best idea is to connect to the excel connection as the only step in the data task and then to use a script task on the control task, write code to connect to the excel source (set the excel source to not treat the first row as headers so we can do a 'select top 1 *...' ) then create a dynamic sql command to create the table.
Once this is done we would then have another data task that actually puts the data into the newly created table. This all sounds very difficult though. What are the options for doing what we want to do... have a feeling that we're missing something basic.
Any help appreciated,
TIA, Anthony.
View 4 Replies
View Related
Mar 27, 2007
Hello...
When I migrated data from one SQL Server to another I got collection problems because collation of the target server was different from the source one.
The best solution I thought about was to change collation of the database in target server to be equal to the server collation so that when a temporary table is created, and the collation used would be the server collation, no error would occur. All sounds logic, but, after I ran ALTER DATABASE command and changed the collation of the database, I verified that all varchar fields of all database tables retained the old collation, not the new database collation I set.
Is there any way to change the collation of all fields at once when I change the database collation?
Thanks for your help
Jaime
View 1 Replies
View Related
Mar 9, 2004
Is there anyway to determine what the resulting Field Names are going to be from a SQL Statement?
For example:
SELECT TABLE1.FIELD1, TABLE1.FIELD2, TABLE1.FIELD3, TABLE2.FIELD1 AS ANOTHERNAME
FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.PK = TABLE2.FK
resulting field names:
FIELD1
FIELD2
FIELD3
ANOTHERNAME
Seems easy enough splitting all values before "FROM" by comma and doing some manipulation to remove table names and anything before the word "AS". However, it gets more difficult when you have complex CASE statements embedded in you query that may also contain commas.
Just a shot in the dark because I don't know if anyone has already done something like this before.
Thank you in advance,
Jeff
View 2 Replies
View Related
Jun 1, 2005
Howdy all,I'm wishing to write a stored proc to return only the field names from a table. What I've tried gets the field names but also returns all of the data in each row. I only want the field names. Is this possible?Thanks!JP
View 7 Replies
View Related
Oct 6, 2005
Hi,We have a database with some tables with (what I woulddenote as) 'referred field names'.Like this:DataTable1 with fields F1, F2, F3DataTable2 with fields F3, F4, F5DataTable3 with fields F1, F5, F2We also have a table with field namesFieldNameTable with fields FIELD, NAMEcontaining data like:FIELD NAME----------------F1 FieldName1F2 FieldName2F3 FieldName3F4 FieldName4F5 FieldName5Now, we need a way to query the data of these tables, butthe result of the query should show the 'referred field names'from the FieldNameTable.For example, querying DataTable3 should produce the outputFieldName1 FieldName5 FieldName2------------------------------------------... ... ...... ... ...Any idea how (and whether) this can be done with an SQL query?Thanks in advance for tips & tricks.Dirk Vdm
View 6 Replies
View Related
Jan 15, 2004
I can't seem to find a sample code, either here or on the net - - so I'll go ahead and ask...
What I'm looking for is a sample of how to query a database, so that I can populate a listbox with the table names from a database - - and then, populate another listobx with the field names from the database, in order to build a user-driven sql statement builder....
either of the above (at least the field name part) - either a code sample, or a link, will be greatly appreciated) - -
Thanks
View 1 Replies
View Related
Nov 14, 2001
I need a statement or sp that will display, for a given user database, an individual table's fieldnames, datatype, and length. Any help is appreciated.
Randy
View 6 Replies
View Related
Nov 20, 2006
Hello All,
I have the following code:
USE MLS
select sc.name,f.field#,fdesc,flong
from sysobjects so join syscolumns sc
on so.id = sc.id
join fld f
on f.field# = replace(sc.name,'_','')
where so.name = 'dbo.tbl_MLS_Leads_Trans'
I am trying to get the description which is flong and I get the following error message:
Msg 208, Level 16, State 1, Line 2
Invalid object name 'fld'.
What am I doing wrong?
TIA
Kurt
View 2 Replies
View Related
Mar 15, 2008
Can someone write a query that select all the fields of tables in database that have type 'image'?
Something like this:
Select TableName, FieldName FROM TableWhereTheyKeepThoseThings
WHERE TableWhereTheyKeepThoseThings.FieldType='Image'
... olny it should work :)
View 2 Replies
View Related
Jul 20, 2005
Hi,I am trying to create a stored procedures (SQL 7.0), to provide dataina crosstab format.(I'm using Crystal Reports 8.5, but the Crosstab capabilities areterrible, so I have to do as much as possible on the SQL side)I have a table [Occurrences] with the following fields:Year (int)Month (int)Occurs (int)Claims (int)I need a query to give me the following format:Acct_Month 2001 2002 2003Occurs Claims Occurs Claims Occurs ClaimsJanuary 120 180 132 196 110 140February 154 210 165 202 144 178March etc.......Catch! I need the Year field name to be the contents of the fieldYear in the Table (2001, 2002, 2003...). Not the usual Year_1, Year_2approach.I got the month name ok...Acct_Month = DATENAME(month, Convert(Varchar(2), Month) + '/01/'+Convert(Char(4),Year))Is it possible to do this easely, without the use of cursors?Any help would be much appreciated.Luis Pinto
View 1 Replies
View Related
Apr 26, 2006
Hi,
is there any "robust" way to find out the name of a field in the pipeline by it's Lineage ID programatically? There is a sample code out there (on one of the blogs) but it seams not to be reliable...
The usecase is easy... What is the field name in an error output of that column that causes the error? We don't want to have hardcoded LineageIDs in the error handling so I think it's the best idea to go with field names... However we only get that LineageID...
Thanks,
View 1 Replies
View Related
Sep 11, 2006
Hi,
How canI get all field names in a table using sql query? Now I am not interested in the data in this table, what I am interested in is just the schema, i.e. all field names. Thanks.
View 22 Replies
View Related
Dec 19, 2007
While creating a view in SQl Server 2005 Management Studio Or Interation Services on a SQL Server 2000 database using "*" something very strange happens. Has anyone every seen this happen before?
The new view is created something like the following:
CREATE VIEW [dbo].[new_view]
AS
SELECT *
FROM Table1
GO
However, when right-clicking on the new view, and choosing "Design", the SELECT scrambles the field names with aliases of the other field names. When the view is run, the result set is incorrect. It may look something like the following:
SELECT
Field1,
Field2 AS Field3,
Field3 AS Field4,
Field4 AS Field5,
.
.
.
From Table1
GO
View 2 Replies
View Related
Oct 10, 2007
I am having two datasets in the same report, the column names are the same, in both the datasets, Now How can I use the individual fields in same report.
If I use (Fields!xyz.Value, "dsDataSet1") and (Fields!xyz.Value, "dsDataSet2")
It is giving me syntax error.
How can I use both these fieds, I do not want any aggregates
View 7 Replies
View Related
Jun 5, 2006
Bob writes "I want to change the value of a field but it is part of a
unique index. Can someone help me do this.
I need to update about 55000 rows.
thank you very much."
View 2 Replies
View Related
Jan 27, 2008
Hello,
I have a field on my SRS Report called directioncode.
This field gives me a value of 1 or 0.
How do I fix this code so
if the value of directioncode is 0, the text "Incoming" is displayed
if the value of directioncode is 1, the text "Outgoing" is displayed?
Current value in the expression area of my field....
=Fields!directioncode.Value
View 5 Replies
View Related
Aug 9, 2004
Hello All,
I have been trying to get this code work, but I could not. Every thing seems going well. However, The result of running the sql query is strange. It shows the field names twice.
Eg:) if you have a table called "newtable" that has two fields[Custnumber, Custname], you will get somthing like this [Custnumber, Custname Custnumber, Custname]. I have tried many times, but I couldn't fix it.
Sub Page_Load(sender As Object, e As EventArgs) handles Mybase.Load
if not page.Ispostback then
try
Sqlconnection = New Sqlconnection (connectionString)
querystring = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNs
WHERE TABLE_NAME = 'Newtable'"
SqlCommand = New SqlCommand(queryString, Sqlconnection)
SqlConnection.Open
dataReader = SqlCommand.ExecuteReader(CommandBehavior.CloseConnection)
while dataReader.Read()
Tablefields_txt.text += dataReader.Getstring(0) & ", "
End while
catch ex as Exception
msgbox("An error has occured: " + ex.Message,0, "Error Message")
finally
SqlConnection.Close()
End try
End if
Any help , please
View 3 Replies
View Related
Jun 8, 2005
I am in a situation where I need to find out unique field names with different values in a table having 200+ columns.
Let's say I have two rows with 200+ columns ( I exported these rows from Lotus Notes to SQL Server) I am not sure what columns makes unique of these rows. It's going to be tedious by checking each column values.
Is there anyway I can write a squl query on these two rows which outputs column names which are having unique values.
I would appreciate If anybody gives me hint about achieving desired result
View 2 Replies
View Related
Nov 23, 2004
for debug purposes i tried to copy paste some Query Analyser output into Excel... the data pastes fine, but i can't get the field-names to copy/paste. is there a trick? izy
View 3 Replies
View Related
