Hi,
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
Hi
I have developed an application in ASP/SQL server 7.
Ths system is single user. One of the tables is updated by usr actions( say Table A).
To make it multi user. I want to create table such as A_Username.
How can query be written for this. Also there are many stored procedures which will access this table. In these stored procedures i will send username as an input. Then the query in the stored procedure shd access the table as A_Username .
Such dynamic table name refrencing , how can it be done.. Is creating a string for the query and then executing it using sp_exec the only option?
pls suggest
I have been trying to access the PropertyNames and the data contained with in the PropertyNames and PropertyValuesString (aspnet_Profile Table). I have data contained in those Rows but can't seem to retrieve the data via SqlDataSource.
When i use a SqlDataSource with a FormView and "configure" the SqlDataSource the PropertyNames and PropertyValuesString isn't listed. In "Specify columns from a Table or View"
The only Columns that are showing up for the aspnet_Profile Table in (ASPNETDB)...
*
UserId
LastUpdatedDate
DataSize
So how can i retrieve the information contained in the PropertyNames & PropertyValuesString??
Hi there
I am using an SqlDataReader to read and write to my back end database and i have got it to work using the code: myDataReader["myFieldName"].ToString();
However, when i have two fields of the same name (e.g a "Surname" belonging to students in a table, and "Surname" belonging to teachers in a different table), I can't Pick up the two different fields even though i have given aliases.
I don't really want to access these fields using an integer as the index as this will make management in the future difficult.
as a side note, i have to use aliases as i access the teachers table a number of different times to specify who the tutors are and who teachers of all their different subjects are.
thanks
pete
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 RelatedI 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
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
Hello All,
I've been struggling with this for a few weeks now. I have this table with the following information:
08/10/2006 10:31:13 AM
08/14/2006 1:32:10 PM
38895.396528
38916.584873
38917.5639
38922.398727
38919.482083
9/20/2006 12:01:19 PM
09/08/2006 1:32:02 PM
38894.73316
38918.543229
38926.564954
08/10/2006 12:31:25 PM
08/10/2006 12:31:25 PM
08/09/2006 1:03:25 PM
What I'm trying to do is get these dates w/timestamps (38926.564954) into this format
08/09/2006 1:03:25 PM . Please help!!!!! Thanks.
ok can i append some data to the data inside a column??
View 4 Replies View RelatedI'm creating a new Integration Services Project that copies data out of a SQL 7 server, transforms it, and places the data on a SQL 2005 (SP 2) Server. When defining a lookup transformation, if I specify an OLE DB Connection to my server running SQL 7 as the reference table, as soon as I click on the Colums tab, Visual Studio closes / crashes and dumps me to windows. I don't get an error message. If however I specify a connection to a server running SQL 8, or SQL 2005, no problems.
Is this supposed to happen?
My workstation is running Windows XP Pro SP2, Visual Studio 2005 Pro.
Microsoft SQL Server Integration Services Designer
Version 9.00.1399.00
The server that doesn't work for a reference table is running Windows 2000 Server SP4
SQL 7.00.623
Thanks for your help,
Kirk
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
Hi,
I need to update just a portion of a column value in a table.
example,
Order_data = 'source=ABD00050&ordsrc=&ecode=ABD00001'
Order_data ='source=ABD00050&ecode=ABD00001'
for both of these values of Order_data I just need to update value of ecode..
Please help..
Thanks,
Reva
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
Hi,
I need to automate the procedure of selecting column with numeric and passing those column values as string to another stored procedure.
Here is sample code :
CREATE procedure procdeure1 @Utility varchar(50) WITH RECOMPILE
as
if @Utility='Electricity'
begin
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'gmmers1')
DROP TABLE gmmers1
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'gmmers2')
DROP TABLE gmmers2
create table gmmers1(sitecode varchar(15),
Gen_Electricity_Usage bigint
)
create table gmmers2 (gmmerssitecode varchar(15),
Gmers_Electricity_Usage bigint
)
insert into gmmers1
Select site.Sitecode,sum(isnull(AcctRptdata.ElectricityUse,0)*factor) as 'Electricity Usage'
From GEN..AcctRptdata AcctRptdata, GEN..site site,SuperAccesslevels.dbo.convfactor,SuperAccesslevels.dbo.countrysettings
Where ((AcctRptdata.Year*100)+AcctRptdata.Month) >= 200511 and
--((AcctRptdata.Year*100)+AcctRptdata.Month) < year(DATEADD(m,-1, GetDate()))*100+month(DATEADD(m,-1, GetDate())) and
((AcctRptdata.Year*100)+AcctRptdata.Month) >= 200511 and ((AcctRptdata.Year*100)+AcctRptdata.Month) <= 200707 and
site.idsite = AcctRptdata.siteid and site.sitecode not like ('C%')
and len(sitecode ) = 8
and AcctRptdata.idsvc in (100) and
SuperAccesslevels.dbo.convfactor.[Default] = SuperAccesslevels.dbo.countrysettings.IdUnitElec and
SuperAccesslevels.dbo.countrysettings.IdCountrySetting = Site.IdCustomerSetting and [user] = 1
and site.Sitecode not like '%B%'
and site.sitecode not like '9999%'
and AcctRptdata.ElectricityUse >0 --Added on 16th Aug
and site.sitecode in (select left (sitecode,8) from GEN..site where len(sitecode) >8)--= 11)
--and site.sitecode in (select left(sitecode,8) from gmers_prodn..facility_data where len(sitecode) = 11)
Group by site.Sitecode having sum(AcctRptdata.ElectricityUse*factor) > 0 Order by site.Sitecode
insert into gmmers2
select left (sitecode, 8) BU, sum(isnull(KWh_Purchased_Quantity, 0 )) as 'electric use' from gmers_prodn..electricity_data
where sitecode in --(select sitecode from facility_data where left (sitecode ,8) in
(select sitecode from gen..site where sitecode not like 'C%' and len(sitecode ) > 8 )
-- and len (sitecode) = 11)
and rpt_dt >= '2005-11-01' and rpt_dt < '2007-08-01'
--and sitecode like 'A0916323%'
and sitecode not like '%B%' and left(sitecode,8) not in ('N0010024','N0010088','N0010101','N0010173',
'N0010373','N0010447') and sitecode not like '9999%'
group by left (sitecode, 8) order by left (sitecode, 8) -- yyyy-mm-dd
select *,(Gen_Electricity_Usage-Gmers_Electricity_Usage)as Diff from gmmers1,gmmers2
where gmmers1.sitecode=gmmers2.gmmerssitecode and (Gen_Electricity_Usage-Gmers_Electricity_Usage) <>0
end
else if @UTILITY='GAS'
begin
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'gengas')
DROP TABLE gengas
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'gmmersgas')
DROP TABLE gmmersgas
create table gengas(sitecode varchar(15),
Gen_Gas_usage bigint
)
create table gmmersgas (gmmerssitecode varchar(15),
Gmers_Gas_Usage bigint
)
insert into gengas
Select site.Sitecode,sum(isnull(AcctRptdata.NaturalGasUse,0)*factor) as 'Gas Usage' From GEN..AcctRptdata AcctRptdata,
GEN..site site,SuperAccesslevels.dbo.convfactor,SuperAccesslevels.dbo.countrysettings
Where ((AcctRptdata.Year*100)+AcctRptdata.Month) >= 200511 and
--((AcctRptdata.Year*100)+AcctRptdata.Month) < year(DATEADD(m,-1, GetDate()))*100+month(DATEADD(m,-1, GetDate())) and
((AcctRptdata.Year*100)+AcctRptdata.Month) >= 200511 and ((AcctRptdata.Year*100)+AcctRptdata.Month) <= 200707 and
site.idsite = AcctRptdata.siteid and site.sitecode not like ('C%')and
len(sitecode ) = 8 and AcctRptdata.idsvc in (200,300) and
SuperAccesslevels.dbo.convfactor.[Default] = SuperAccesslevels.dbo.countrysettings.IdUnitGas and
SuperAccesslevels.dbo.countrysettings.IdCountrySetting = Site.IdCustomerSetting and [user] = 61
and site.sitecode not like '%B%' and site.sitecode not like '9999%'
and site.sitecode in (select left (sitecode,8) from GEN..site where len(sitecode) >8)
and AcctRptdata.NaturalGasUse >0 ----Added on 16th Aug
--and site.sitecode in (select left(sitecode,8) from gmers_prodn..facility_data where len(sitecode) = 11)
Group by site.Sitecode having sum(AcctRptdata.NaturalGasUse*factor) > 0 Order by site.Sitecode
insert into gmmersgas
select left( sitecode, 8 ) BU , sum(Utility_Natural_Gas_Volume) as 'gas use' from GMERS_PRODN..fuel_data
where sitecode in --(select sitecode from facility_data where left (sitecode,8) in
(select sitecode from gen..site where sitecode not like 'C%' and len(sitecode ) > 8 )
-- and len(sitecode )= 11 )
and rpt_dt >= '2005-11-01' and rpt_dt < '2007-08-01'
and sitecode not like '%B%' and sitecode not like '9999%'
group by left( sitecode, 8 ) order by left( sitecode, 8 )
select *,(Gen_Gas_usage-Gmers_Gas_Usage)as Diff from gengas,gmmersgas
where gengas.sitecode=gmmersgas.gmmerssitecode and (Gen_Gas_usage-Gmers_Gas_Usage)<>0
end
My aim is to pass sitecode having difference <>0 and from date and todate as an parameter to another stored procedure which updates sitewise for the utility so that in case a particular sitecode with same date range is included in one utility it should not be repeated in another utility.
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,
If I am taking a Matrix and right clicking on the column header and click on the SubTotal then it always place that column on the right of it .If I want to place that column to the left of my original column then I can't do it.
Adding manual column and then puuting the Expresstion =Sum(Fields!MyCol.Value) is not halping as it will give me the same value that is there in the column instead of giving me the column
-Thanks,
Digs
hi ,
this is my first post on MS Forums .
Q: my question is that , i want to format a date column .. d/m/yyyy .. but there is no any format facility ..
anybody can help me here ??
I'm in a middle of a project where replication established between Oracle 8i and SQL server 2005. Now we need to drop one column from on of the tables replicated from Oracle side. My question : Is there anyway I can do this in Oracle and automatically reflected on SQL server? If not what is the proper steps to do it. Realy appreciate your fast response.
Regards
I am designing a package to export staging tables into a flat file.The names of the tables will be: TableAStaging_YYYYMM and TableBStaging_YYYYMM. As you can see the names of the tables will be changing each month.
The flat files will have similar naming: C:MyPathFlatFileTableAStaging__YYYYMM and C:MyPathFlatFileTableAStaging__YYYYMM.I want to run the package as an sql job in two steps, one for each table.I need to dynamically pass the table names and file names (together with the path) to the IS package.
As part if a recent requirement I have to export Chinese/Singaporean names in a CSV file. The data in the tables is a NVARCHAR(256).
I am using a FlatFile Connection manager where all the present columns from the table are exported as NVARCHARs. My understanding was that the Chinese/Singaporean names would blend seamlessly with NVARCHARs in place. But, they get garbled when pushed to the CSV.
Here is the connection manager setup
There are a lot of suggestions of fixing this by copying/pasting to a notepad file and changing the formatting... But I cant do that since the file is generated using a schedules SSIS package. How can I tweak the process to fix the issue?
Hi
I was wondering if anyone has an idea of how we could find the table names and column names of the tables in our Sql server database at runtime/dynamically given our connection string? Please let me know.
Thanks.