I have one column name that is: description
when i write a query the world lights up with blue, I think I saw someone using [ ] around the word but I no longer remember if this is the way to handle reserve words that have been use as columns names
Hi there,
I'm trying to recreate a MS-SQL database in MySQL. One particular table has a column names "Precision", a reserved word in MySQL. I woudl really prefer to keep this name if possible as it will be referenced in all sorts of places. I've tried single and double quotes, that didn't work.
Is it possible to use reserveds word as column names? How?
cheers
Here we go,
I have a great question for all of you.
What is the problem in using reserved words in table columns name? Any one have a compeling reason not to use?
Here is an exemple:
/ ******** Table ********/
Entity
EntityId
Name
When you query this table you should use the [ ] like this:
SELECT EntityId,[Name] FROM Entity
Anyone have any objection to this, with actual facts?
The question why use this?
This about the programing object in C#
Entity oneEntity = new Entity();
oneEntity.Name = "Test Entity";
It could be:
oneEntity.EntityName = "Test Entity";
Any one???
We want to covert all reserved words
in procedures to upper case,any suggestions ?
Hello all,
I have a database that supports two different applications.
For arguments sake I'll call them "intelligent app" and "clueless app"
The client that provided me the schema for the clueless app used reserved words as field names.
The words used are:
value, state, and time
I explained to the client that modifying them could prevent current/future issues.
The client is an "oracle" db (and in my mind should know better but .....) and responded back to me with the following:
BEGIN QUOTE
They are not currently reserved and there is no guarantee they will ever be reserved. In fact, the SQL server line eliminates as many words from the reserved list as they add with new releases. There are other words on the
possible future reserved keyword list that I do not wish to avoid either unless forced to some day, like depth, size, class, zone, level and others.
END QUOTE
Now for the assistance request, where can I find the documented ramifications of using reserved words?
I want to have my documentation (and my ducks lined up) when this clients portion of the app fails.
Any and all assistance is greatly appreciated.
T. Mullins
I am attempting to define a Transactional Publication with Updateable Subscriptions.
One of the articles in the definition has a column called "insertDate" The attempt fails with"A .NET Framework error occurred during execution of user-defined routine or aggregate "sp_MSmakeconflicttable_ sqlclr" System.Data.SqlClient.SqlException: Column names in each table must be unique. Column name insertdata in table 'conflict_ <Publication Name>_<TableName>' is specified more than onceDoes sp_MSmakeconflicttable_sqlclr attempt to create a derived table with this additional column.
Must I rename my column.Are there any other names I should watch out for (other than the standard reserved keywords).
I need a querry to get all columns names.
thanks
Hey,
I'm creating registration form.
To show fields names I thought to read columns names.
It's ok if columns is named like "Name", "Age" etc.
But if the columns is named [Country, Address, PostCode] then, I think, it can course some problems. Am I right?
First problem I thought about - changing database in the future (Now MS SQL 2k to MySQL etc.)
Is this the only problem?
To solve this I think using table which store syscolumn names as user defined columns names.
My system is speed critical and using this I would get less performance.
Which way should I go?
Case saving columns names in table, how to generate safe column name from user specified name, which can have special charters.
Thanks
Hi, I need do get a columns names from a table? How to do this in pure SQL? I thought about creating a stored procedure or user function with a result of a string ( col1name,col2name ....) I do not know how to count the number of columns in a specyfied table? Any help would be appreciated. Thanks in advance. magicxxxx
View 2 Replies View RelatedHi,
I am looking for some help in MS SQL server. I want to access the columns of a table without using the names of the colulmns.
Example - SELECT table1.field[1], table1.field[2] FROM table1;
Any information to this effect is much appreciated.
cheers/- Pradeep
I've called a resultset from SQL Server
using an SQL Selection. I need to iterate over that entire result set
(200+ columns/fields) and all I need are the random numbers contained
in any of the rows/columns. I don't want to have to name each
field/column and then use an if > 0 statement.Isn't there
some way to generically loop through the column's by index or something
instead of their field name so I can just use an integer loop to walk
the dataset? I know there is I've done it about 5 years ago. The
question is how do you do it in C#?SqlConnection thisConn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLQuery"].ConnectionString); SqlCommand thisCmd = new SqlCommand("Command String", thisConn); thisCmd.CommandText = "Select * from SelectionsByCountry where [" + DropDownList1.SelectedItem.ToString() + "] > '0'"; thisConn.Open(); SqlDataReader thisReader = null; thisReader = thisCmd.ExecuteReader(CommandBehavior.CloseConnection); while (thisReader.Read()) { DropDownList2.Items.Add(thisReader["System"].ToString().Trim());/*** There are 200+ columns left I want to walk over using a loop structure of some sort. How do I do that?*/ }- Rex
I'm looking for a query that will return all index names, the table the index is on and the columns in the index...
View 1 Replies View RelatedI've got a database with an unknown number of columns. Hence, the column names are also unknown. What's the easiest SQL to present the values in each column and the column headings?
View 1 Replies View Relatedhi,
i am sure this question must have been anwsered some where, but after a lot of searching i still have not find the anwser.
i have flat files without column headers (267 columns in total).
since i have the file's description i have created a table to house these extracts with the columns in the same order as in the flat files.
additionally, i have an excel containing a list of the column names their data types and length as well as their position on the flat files.
in the old, DTS would map the columns without headers to those columns in the destination table using their order, in which case it works like a breeze for me. but i can not find a way of doing that in SSIS.
i would very much appreciate someone's assistance on this one since i am sure that there must be a better way than manually (and tediously & error prone) to map all those columns.
thanks in advance
I am able to get a list of columns in a table from the query I have written shown below:
select sc.name ColumnNames,st.name TableName from sys.columns sc inner join sys.tables st on sc.object_id=st.object_id
order by st.name
But I am looking for the resultset with the format below:
TableName  Columns
employee    employeeid,employeename,employeesalary
order       orderid,address,priceÂ
I am trying to do a PIVOT on a query result, but the column names created by the PIVOT function are dynamic.
For example (modified from the SQL Server 2005 Books Online documentation on the PIVOT operator) :
SELECT
Division,
[2] AS CurrentPeriod,
[1] AS PreviousPeriod
FROM
(
SELECT
Period,
Division,
Sales_Amount
FROM
Sales.SalesOrderHeader
WHERE
(
Period = @period
OR Period = @period - 1
)
) p
PIVOT
(
SUM (Sales_Amount)
FOR Period IN ( [2], [1] )
) AS pvt
Let's assume that any value 2 is selected for the @period parameter, and returns the sales by division for periods 2 and 1 (2 minus 1).
Division CurrentPeriod PreviousPeriodA 400 3000 B 400 100 C 470 300 D 800 2500 E 1000 1900
What if the value @period were to be changed, to say period 4 and it should returns the sales for periods 4 and 3 for example, is there a way I can change to code above to still perform the PIVOT while dynamically accepting the period values 4 and 3, applying it to the columns names in the first SELECT statement and the FOR ... IN clause in the PIVOT statement ?
Need a way to represent the following [2] and [1] column names dynamically depending on the value in the @period parameter.
[2] AS CurrentPeriod,
[1] AS PreviousPeriod
FOR Period IN ( [2], [1] )
I have tried to use the @period but it doesn't work.
Thanks in advance.
Kenny
I am imagining something you might pass the names of 2 stored procs (an old version and new one), and a query to produce valid parameters. It would then fire off each proc for a set number of executions, while storing off the results in temp tables, and at the end it would do a data compare, and store off performance data from dynamic management views.
Now I know how to get the parameters for a stored procedure out of the catalogue views, but is SQL Server aware at all of the schema of the results of stored procedures that return result sets, becuase I was thinking of doing something like...
INSERT INTO #datacompare(col1,col2)
EXEC mystoredprocedure
... but I can not seem to figure out how to dynamically gather the schema of the result set.
I am trying to figure out how to pivot a temporary table. I have a table which starts with a date but the number of columns and columns names will vary but will be type INT (Data, col2,col3,col4………….n)
So it could look like
Date , TS-Sales, Budget , Toms sales
01-Jan-14,100,120,300
02-Jan-14,80,150,300
03-Jan-14,100,20,180
Turned to this
01-jan-14, 02-jan-14, 03-jan-14
100,80,100
120,150,20
300,300,180
Or even just the date and a SUM
What I want is to be able to sum al the columns but without knowing the name and the amount columns to start with this is a manually processes. How could I automate this?
While looking forward to design a multi-columnar cross-tab query I am anxious to know if there could be a way to change the default names of the pivot columns? In other words for the query like the following can there be a way to apply anAS type command to reflect some other names, instead of having the four dates in heading? Something like Month_A, Month_B?
SELECT * FROM
(SELECT
X.REP_DT,
X.CUST_ID
AMOUNT_1
FROM
X) P
PIVOT (SUM(AMOUNT_1) FOR REP_DT IN ([2014-12-31], [2015-01-31], [2015-02-28], [2015-03-31])) PVT_01
I need to find column names if any after 2 “check” columns.
Scenario: I have a database, with approx 400-1500 tables, depending on installation of software. The software is structured so that, when it synchronizes the SQL database it will create all the columns e.g. custacc, custname etc. and then it will always put in two check columns “CheckOne” and “CheckTwo” these two columns has to be the two last ones. In 99.9 this always works fine, but sometime if the users creates a new field in the software, when it synchronizes the new field “lands” behind the two checkfields, which is not good.
So what I am after is a script, which can run through all user tables, tell me if there are columns after the two checkfields and list those tables if any.
Any help would be greatly appreciated.
Cheers
Henrik.
Hello,
I've been able to startup a transactional replication between 2 database for some tables that only have different table names. Now there are still some tables that not only have different names but also different column names, can this be done in the wizzard ??
Thx
I€™m using the ImportExport wizard to export the top 5 lines from a MS Sql table into a fixed format (€śragged€?) file. But I want the first record to contain the column names of the exported fields so I selected the €śColumn names in the first data row€? option of the €śChoose a Destination€? box. When I run the Package I get:
>>>
· Information 0x402090dc: Data Flow Task: The processing of file "C:arkingdogExportWithheader.txt" has started (SQL Server Import and Export Wizard)
· Error 0xc0202095: Data Flow Task: Failed to write out column name for column "CustomerID".
(SQL Server Import and Export Wizard)
Error 0xc004701a: Data Flow Task: component "Destination - ExportWithheader_txt" (49) failed the pre-execute phase and returned error code 0xC0202095.
(SQL Server Import and Export Wizard)
>>>
When I de-select the €śColumn names€? option, the package works fine. Other than manually, how can I et the column names in output file?
TIA,
Barkingdog
I am using CROSS APPLY instead of UNPIVOT to unpivot > one column. I am wondering if I can dynamically replace column names based on different tables? The example code that I have working is based on the "Allergy" table. I have thirty more specialty tables to go. I'll show the working code first, then an example of another table's columns to show differences:
select [uplift specialty], [member po],[practice unit name], [final nomination status]
,[final uplift status], [final rank], [final uplift percentage]
,practiceID=row_number() over (partition by [practice unit name] order by Metricname)
,metricname,Metricvalue, metricpercentilerank
[code]....
Rheumatology Table:The columns that vary start with "GDR" and [GDR Percentile Rank] so I'm just showing those:
GDR (nvarchar(255), null)
GDR Percentile Rank (nvarchar(255), null)
GDR PGS (nvarchar(255), null)
GDR Rank Number (nvarchar(255), null)
PMPM (nvarchar(255), null)
[Code] ....
These are imported from an Excel Workbook so that's why all the columns with spaces for now.
 I have a situation where I want to load the Excel file dynamically, and the excel file have different columns or even worksheet name. How I could approach this? I believe there's no way to modify the meta data (specifically the mapping) in the data flow.
View 6 Replies View RelatedHi,
We have a table with a column called 'text' Yes, text is a reserved keyword for data type. I can select it 'SELECT text FROM thistable' but when I try to use it in the order clause, 'SELECT text FROM thistable ORDER BY text', I get this error:
'The text, ntext, and image data types cannot be used in an ORDER BY clause'
I know, you're not supposed to used reserved keywords but the person who initially created this didn't know. We would prefer not to change the column name as this would require tons of changes in the code & COM object.
Is there a way to order this column?
Thanks.
The Query:
INSERT INTO Table1
VALUE('this doesn't work')
How can I Insert strings like the one above, into my database?
Does anyone know where I can get a 7.0 reserved word list?
View 1 Replies View RelatedI migrated a SQL7 database to a SQL2000 server by restoring from a backup file. Everything worked fine until we restarted the SQL Service a few days later and suddenly a table named Function caused me much grief
It seems Function is a reserved word in 2000 - but not in SQL 7.
I am guessing the restore fooled the system into thinking it was a SQL7 database until the restart? Anyone have any ideas why else it would have worked just fine for 5 days, then die on a restart of SQL service (not even a reboot!)
Sandie
I've got a sproc that we are migrating from SS2K that uses the reserved keyword 'Pivot'. Best way to replace it. I think it is renameable until the select from TempDB (which is mysterious as I can't see a table in tempDB by that name!!) as below...
CREATE PROCEDURE [dbo].[sp_CreateXtab]
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100),
@SQLOutput varchar(8000) OUTPUT
[code]....
Hi, I have got a problem. When I try to access my database table Users, I get the following error:
SELECT permission denied on object 'Users', database 'Users', owner 'dbo'.
So
I tried to grand this select command in MS Web Data Administration, but
it doesnt work. When I try to grand db_datareader role to dbo, I get
the following error
[Microsoft][ODBC SQL Server Driver][SQL
Server]Cannot use the reserved user or role name 'db_datareader'.
Does someone have an idea where could be a problem?
Dear Friends,I'm running a SQL 2005 database. Lately (in the last 4 months) my database size has grown exponentially and now totals 2.5 gig (it used to be about 100mb). When I run sp_spaceused i get the following info:
Reserved Space 2522MB
Data 450MB
Indexes 215MB
Unused 1856MB
<!--[if !supportLineBreakNewLine]-->
The unused space is reserved by my two largest tables, one being 542mb (which is a log I create and the size seems fine for the amount of data) reserving 1033mb and the other being 121mb (which is another log and also seems fine) reserving most of the rest.
I have tried running DBCC SHRINKFILE and DBCC CLEANTABLE commands on a backup copy of the database and cannot seem to free the unused space.
I have also tried copying the database in an attempt to reduce the unused reserved space and have tried creating a new database and importing the tables with no luck
Any help would be greatly appreciated, noting that I'm honestly not that technical and would probably need step by step instructions, but if you only have time to point me in the right direction that would also be of great help!
Jano
Hi!
We are encountering an issue of using OLE DB destination with the field name of the table is a reserved word in the database.
The database is DB2 V8 (mainframe). "partition" is one of the reserved word in V8 (It is not in the previous version and the whole thing worked fine).
It errors out when it reaches to the OLE DB destination task to do the insert. It complaints about using the reserved word, "partition". We could use an "OLE DB command" (instead of a to do an OLE DB Destination) with the insert SQL statement explicitly by putting double quote around the field name, partition in the insert statement. It works this way. But We wondered if there is any work around of using a reserved word in OLE DB destination in that case?
Thanks!
I have tried both in sql server 2000 and sql server 2005 the following code:
DECLARE @DOC VARCHAR(100)
DECLARE @HDOC INT
SET @Doc='<datos><texto><![CDATA[lĂnea átona]]></texto></datos>'
EXEC sp_xml_preparedocument @HDOC OUTPUT, @DOC
SELECT * FROM OPENXML(@HDOC,'datos',2) WITH (texto nvarchar(50))
I always get an error 6603 in SQL2000 and 6602+8179 in SQL2005, wich means it can't parse the 'Ă' and 'á' characters, though they are in a CDATA.
I have tried with <,>,@ characters and it works fine.
I have also tried using 'á' and 'í' without CDATA but I get the same message:
DECLARE @DOC VARCHAR(100)
DECLARE @HDOC INT
SET @Doc='<datos><texto><línea átona></texto></datos>'
EXEC sp_xml_preparedocument @HDOC OUTPUT, @DOC
SELECT * FROM OPENXML(@HDOC,'datos',2) WITH (texto nvarchar(50))