Changing Field Names
Jan 27, 2008This 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
ADVERTISEMENT
Changing Field Names Is SQL Server 2000
May 23, 2007Why 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?
Table Names And Field Names
Jan 21, 2004I'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?
Changing Server Names
Jan 23, 2008I 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
Changing Machine Names MSDE
Mar 11, 2006I 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 RelatedChanging Linked Server Names Between Environments
May 1, 2007If 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.
Table Names Are Not Changing With Change In Schema Name
Aug 8, 2007Hello 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
Exporting A Table With Changing Column Names To An Excel File
Oct 25, 2006I'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)
How Do I Create A Package That Imports An Excel File Where Column Names Are Changing.
Sep 29, 2006Hi,
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.
Field Names From SQL Statement
Mar 9, 2004Is 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
Return Field Names
Jun 1, 2005Howdy 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 RelatedSQL For Field Names From A Table
Oct 6, 2005Hi,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 RelatedShow Field Names/Schema
Jan 15, 2004I 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
SELECT Table Field Names
Nov 14, 2001I 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
Trying To Display Alias Field Names
Nov 20, 2006Hello 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
How To Select Field Names Of Table?
Mar 15, 2008Can 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 :)
Crosstab Qry With Dynamic Field Names
Jul 20, 2005Hi,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 RelatedFinding Out Field Names By Lineage ID
Apr 26, 2006Hi,
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,
How To Get All Field Names In A Table Using Sql Query?
Sep 11, 2006Hi,
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.
Scrambled Field Names In Views
Dec 19, 2007While 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
Two Different Datasets With Same Field Names Used In Same Report
Oct 10, 2007I 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
Changing Value Of A Field
Jun 5, 2006Bob 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."
Changing A Field Value In SRS
Jan 27, 2008Hello,
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
A Strange Problem With SQL Query Fro Getting Field Names
Aug 9, 2004Hello 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
Finding Unique Field Names With Different Values
Jun 8, 2005I 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
Copy/paste Grid From QA - No Field Names!
Nov 23, 2004for 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 RelatedCan You Pass Field Names In Stored Procs?
Jun 30, 2006CREATE PROCEDURE [dbo].[removeContact]
@RemoveType VARCHAR (50),
@ListID INT,
@TargetField VARCHAR (50),
@TargetValue VARCHAR (150),
@RowsAffected INT OUTPUT
AS
UPDATE [tblContacts]
SET @RemoveType = 'yes'
WHERE [list_id]=@ListID
AND @TargetField = @TargetValue
SELECT @RowsAffected = @@ROWCOUNT
GO
declare @P1 int
set @P1=0
exec removeContact '[unsubscribe]', 6, '[email]', 'email@domain.com', @P1 output
select @P1
I've tried this with and without the square brackets. Either way, nothing gets updated. Am I barking up the wrong tree?
SELECT Statement - How To Not Get Column Field Names?
Jan 24, 2007I do a SELECT * from table command in an ASP page to build a text fileout on our server, but the export is not to allow a field name rows ofrecords. The first thing I get is a row with all the field names. Whydo these come in if they are not part of the table records? How do Ieliminate this from being produced? Here's the ASP code....<html><head><title>Package Tracking Results - Client Feed</title></head><body><%' define variablesdim oConn ' ADO Connectiondim oRSc ' ADO Recordset - Courier tabledim cSQLstr ' SQL string - Courier tabledim oRSn ' ADO Recordset - NAN tabledim nSQLstr ' SQL string - NAN tabledim objFSO ' FSO Connectiondim objTextFile ' Text File' set and define FSO connection and text file object locationSet objFSO = CreateObject("Scripting.FileSystemObject")'Set objTextFile =objFSO.CreateTextFile(Server.MapPath("textfile.txt"))'Response.Write (Server.MapPath("textfile.txt") & "<br />")Set objTextFile = objFSO.OpenTextFile("C: extfile.txt",2)' write text to text file'objTextFile.WriteLine "This text is in the file ""textfile.txt""!"' SQL strings for Courier and NAN tablescSQLstr = "SELECT * FROM Courier"' set and open ADO connection & oRSc recordsetsset oConn=Server.CreateObject("ADODB.connection")oConn.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" &"c:/Database/QaTracking/QaTracking.mdb" & ";"set oRSc=Server.CreateObject("ADODB.Recordset")oRSc.Open cSQLstr, oConnResponse.ContentType = "text/plain"Dim i, j, tmpIf Not oRSc.EOF ThenFor i = 1 To oRSc.Fields.CountobjTextFile.Write oRSc.Fields(i-1).NameIf i < oRSc.Fields.Count ThenobjTextFile.Write " "End IfNextobjTextFile.WriteLineWhile Not oRSc.EOFFor i = 1 To oRSc.Fields.CountIf oRSc.Fields(i-1) <"" Thentmp = oRSc.Fields(i-1)' If TypeName(tmp) = "String" Then' objTextFile.Write "" &_'Replace(oRSc.Fields(i-1),vbCrLf,"") & ""' ElseobjTextFile.Write oRSc.Fields(i-1)' End IfEnd IfIf i < oRSc.Fields.Count ThenobjTextFile.Write " "End IfNextobjTextFile.WriteLineoRSc.MoveNextWendEnd IfobjTextFile.CloseSet objTextFile = NothingSet objFSO = NothingoRSc.CloseSet oRSc = NothingoConn.CloseSet oConn = Nothing%></body></html>
View 1 Replies View RelatedBest Approach To Sending Field Names Dynamically
Mar 9, 2007Hi,I have a C# web app that searches my database table using thefollowing search parametersSearch string, criteria (< =) and the field you want to perform yoursearch on. My understanding is that stored procedure is the way to go.What's the best way of doing this using stored procedures. Can Idefine a placeholder for the field name?Ex.SELECT field1, field2... FROM Table WHERE field1='value1' where field1and value1 are both sent from code.If it's not possible then what is the best way to approach thisproblem? I see so many searches like that on the internet. I can onlydo them with inline SQL and not stored procedure.Thank youMaz.
View 1 Replies View RelatedQuery Returning Duplicate Field Names
Jul 20, 2005I have a .NET program that can connect to either an Access 97 database or anSQL Server 7 database. In the database I have two tables which have a fieldcalled ID. When I run a query like "SELECT A.*, B.* FROM A, B", the queryreturns those fields as "A.ID" and "B.ID" when connected to Access 97, butas "ID" and "ID" in SQL Server 7. Is there anyway to get SQL Server toprepend the table name to the field name in a case like this and not returnduplicate field names like that without having to specify aliases for thefields?- Don
View 1 Replies View Related.dbf File Import (duplicate Field Names)
Mar 27, 2006I am importing a file creating by an application which exports the file into .dbf format. Very unfortunately, this .dbf file can have fields with IDENTICAL column_names. Utilizing ActiveX, I create an ado connection to the .dbf file using a visual foxpro drver. However, and not unexpectantly, I can not do the 'select *' from the file if there are duplicate names.
Can anyone make recommendations here that might help?
Oh, this is SQL200 in case that impacts what you might advise!!!!
Changing Year In Field
Jan 15, 2007Hi all:
I need to change the year to many records that, by mistake, were put with a 2007 date, but they should be still with 2006, can anyone help me ?
I use Sql Server 2000 and the field is of type datetime
Thanks:eek:
Splitting Out Names From One Field Delimted By Comma And A Space
Apr 28, 2004have a field in a table that has combined lastname,firstname and middle name like
combs,albert mike
woods-athere,jane alice
The last and first name are separated by a comma, and the middle name by a space
I need in tsql to split them to last name,first name and middle name
I used below
select substring (longname, 1, patindex( '%,%' , longname) -1 ) 'firstname',
substring (longname, patindex( '%,%', longname) + 1, len(longname)) 'lastname',
substring (longname, patindex( '% %', longname) + 1, len(longname)) 'middlename'
FROM Demographic_staging
I get the names all split, but I get the middle name with the first name. How can I limit the first name to recoginze the comma, but stop at the space