Hi All
I want to rename the column name by using the dynamic value as like
declare @name as varchar(12) set @name='ss'
sp_rename 'dbo.employees.id3',@name, 'COLUMN';
go
but it shows the error
Incorrect syntax near 'sp_rename'.
any idea?
Hi,
Please let me know which query we will to rename a column in a table. I am using MS-SQL 2000.
Hi,
Is there a way to rename column like below?
ALTER TABLE table_name RENAME COLUMN from_column TO to_column
the statement above i found it from a web site, did not worked...
Hai
XYZ (Table name)
a (column name)
Now I have to rename column name a to abc.
alter table xyz
RENAME column a to abc
With this statement, I'm not getting. Its throwing an error
Error : Incorrect syntax near the keyword 'column'.
Can anyone solve this
Thanks in Advance.
Suresh Kumar
will there be any issues if i rename a primary key column name,which is already beging referenced as a foreign key by other tables.
View 1 Replies View Related
I am have this problem, that would not go away, can some please help me.
I want to rename or delete column that is bound to a tableadapter using SQL Express at runtime. is it possibleI have no idea on what to do.
When I execute EXEC sp_rename 'PATHSPECIMEN.PVVALUE', 'PVPROC', 'COLUMN', I am getting the following error:
Major Error 0x80004005, Minor Error 25621
> EXEC sp_rename 'PATHSPECIMEN.PVVALUE', 'PVPROC', 'COLUMN'
The specified argument value for the procedure is not valid. [ Argument # = 3,Name of procedure(if known) = sp_rename ]
Could some one guide me where I am wrong?
I am executing this query against SqlCE in Sql Server 2005 Management Studio.
Hi Craig/Kamal,
I got your email address from your web cast. I really enjoyed the web cast and found it to be
very informative.
Our company is planning to use SSIS (VS 2005 / SQL Server 2005). I have a quick question
regarding the product. I have looked for the information on the web, but was not able to find
relevant information.
We are getting Source data from two of our client in the form of Excel Sheet. These Excel sheets
Are generated using reporting services. On examining the excel sheet, I found out that the name
Of the columns contain data itself, so the names are not static such as Jan 2007 Sales, Feb 2007 Sales etc etc.
And even the number of columns are not static. It depends upon the range of date selected by the user.
I wanted to know, if there is a way to import Excel sheet using Integration Services by defining the position
Of column, instead of column name and I am not sure if there is a way for me to import excel with dynamic
Number of columns.
Your help in this respect is highly appreciated!
Thanks,
Hi Anthony, I am glad the Web cast was helpful.
Kamal and I have both moved on to other teams in MSFT and I am a little rusty in that area, though in general dynamic numbers of columns in any format is always tricky. I am just assuming its not feasible for you to try and get the source for SSIS a little closer to home, e.g. rather than using Excel output from Reporting Services, use the same/some form of the query/data source that RS is using.
I suggest you post a question on the SSIS forum on MSDN and you should get some good answers.
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
Thanks
Craig Guyer
SQL Server Reporting Services
I would like to select the second column from a table reguardless of what it is named and rename it AS Description. Is there a way to do that without knowing the name of that Column?
View 1 Replies View RelatedWhat is the way to rename a column in a table along with its datatype.
Thanks
I am using the compact edition on a desktop using VS2005 as well as SQL Server Management Studio. None of those tools allow me to rename a column or rename a table. Can someone point me a tool the runs on the desktop (as opposed to running on a CE device) that allows me to do the renaming?
Thanks
Is there a way to bulk remove spaces from column names from all tables in a db?
View 6 Replies View RelatedI tried to run this(below) the table is replicated(transactional).
EXECUTE
sp_rename N'dbo.Tablename.Columnname, N'New_Columnname', 'COLUMN'But getting this error message:
Msg 15051, Level 11, State 1, Procedure sp_rename, Line 227
Cannot rename the table because it is published for replication.
I want to alter my all tables to change the name of all columns.
I need this because all column names were created with space and I want to remove the space for future work .
For example – In Table Customer there is a column name [Cust_Id ] and I want to change it with [Cust_Id]
Hi there,
I am a new member of this site and I am not very much aware of T-sql's working.
My question is what if I need to get one column's data to be the heading of another column.
To be very exact I have a school's database. The table I am talking about is of the results of students. The table contains Student ID, Subject ID, Total marks of the subject, Marks obtained in the subject. Now I want to print a report by generating data from this table. Right now the data is something like this
StuID - - - SubID - - - -Tot - - -Obt
1 - - - - - - -1 - - - - - - -50 - - - 38
1 - - - - - - -2 - - - - - - -50 - - - 41
1 - - - - - - -3 - - - - - - -50 - - - 42
1 - - - - - - -4 - - - - - - -50 - - - 40
2 - - - - - - -1 - - - - - - -50 - - - 35
2 - - - - - - -2 - - - - - - -50 - - - 40
2 - - - - - - -3 - - - - - - -50 - - - 42
2 - - - - - - -4 - - - - - - -50 - - - 41
StudentID and SubjectID fields are related to other tables so I can get the names from there but when I need the report I need the data in the form of
StuID - Sub 1 - - - Sub 2 - - - Sub 3 - - - -Sub4
1 - - - - 38 - - - - - - 41 - - - - - - 42 - - - - - - 40
2 - - - - 35 - - - - - - 40 - - - - - - 42 - - - - - - 41
The Subjects can be different for different students so the query should be dynamic instead of hard coding the names of the subjects. I hope I am clear with my question. The subjectIDs or their names will become the headings and they will contain the obtained marks for that subjects in their columns just for the reports. I have also checked the PIVOT function but was not able to do what I wanted.
Thanks.
Hello All,
I'm trying to us a parameter as column name like:
Select * From SM_Clients
where @Selection like @SelectionChar+'%'
This dos not work.
does someone have a solution :(
tks stroek
Hello All,
I'm trying to us a parameter as column name like:
Select * From SM_Clients
where @Selection like @SelectionChar+'%'
This dos not work.
does someone have a solution
tks stroek
Hello,I'm still extremely novice to SQL and I've tried googling how to produce this result but I've been encountering a lot dynamic sql commands which isn't exactly what I want. If this is an ignorant question I do apologize but heres my scenario: I have a table with columns of the day, for instance: Monday_hasData (bit)Monday_DataAnd what I want to do is essentially pass in "Monday" as a parameter and rather than doing an If statement on each row, I would like to just like to do something like assign @dayCheck + "_hasData" to a variable and then use @dayCheck as part of my query. Is this possible or am I going to have to have 7 cases? Thank you for any input,Chance
View 2 Replies View RelatedHi is possible to create dynamic column name
example
Declare
@StartDate as dateTime
Select @StartDate = '2004-06-05'
select
SUM(Case When table1_date BETWEEN dateadd(day,-6,@StartDate) and @StartDate then 1 else 0 end)AS [dateadd(day,-6,@StartDate)],
SUM(Case When table1_date BETWEEN dateadd(day,-13,@StartDate) and dateadd(day,-7,@StartDate) then 1 else 0 end)AS [dateadd(day,-13,@StartDate)]
from
table1
thanx for the help in advance,
i have a table with Monday, Tuesday, Wednesday. .... Sunday
i get the weekday by
select @stat = datename(dw, getdate()) which in this case is Wednesday
but when i do something like
select top 2 * from myTable
where 'myTable.'+@stat = 1
which is as same as
select top 2 * from myTable
where myTable.wednesday = 1
i receive a syntax error. how can i dynamically select myTable.Wednesday ?
i also tried using
select top 2 * from Intercon
where '@stat' = '1'
but then this doesn't return anything where it should.
any help?
Dear All,
We are trying to unpivot the columns into rows but the colunms are changing dynamically, want to know how to set the dynamic value or variable in unpivot query, query is mentioned below:
declare @aw Varchar(100)
set @aw = '1990,1991'
SELECT [name], [year], [data]
FROM (SELECT * FROM t1 where [name] != 'name') p
UNPIVOT
([data] FOR [year] IN
(@aw)
)AS unpvt
The above query while executing geting error message "Incorrect syntax near '@aw'"
please can anybody solve this issue.
Thanks,
Syed
Hi everyone,
So I have to export from a SQL 2005 table to a dBaseIV table using SSIS. Easy enough, however the catch is that the tables being exported will vary. I can send variables to the the package calling the sql table name and creating the DBF file with the same columns on the fly.
The problem is mapping the columns. From what I've been reading there is no way to alter the mapping in a package at runtime. I was hoping that there would be some sort of auto mapping setting that would match on the field names but I guess not. Anybody run in to this issue and have a work around? Thanks in advance....
Scott
Got my first real job to do in SSIS and it's not really much fun. Here goes. I have excel files from various vendors, none of which are in the same format. Now I'm less concerned with the names, I have those in variables so that's not a big deal. My issue is around the column names. Let's say we have file a, b and c. In file a the first name field is called [first name], in file b it's first_name and in file c it's firstname. What I want to do is create 1 package that can handle these files regardless of how first name is spelled. I was thinking I could open the file and somehow search for a column name that contained the word "first%" and then map the column name to a variable. I'm sort of stuck though on how to actually do that. Help. And please don't tell me to tell the vendors to standardize. That's the end goal but it's like herding cats.
Mike
"oh, that monkey is going to pay"
Hi guys 'n gals,
I am trying to achieve something a bit complex (or at least appears to be for me).
I have the following table structure:
UID, GroupID, ColumnName, ColumnValue
here is some example data:
UID, GroupId, ColumnName, ColumnValue
1, 1, MAC Address, 8a7sd87sad
2, 1, IP Address, 192.168.1.100
3, 1, Name, John
4, 2, MAC Address, 09a8sd098as
5, 2, Name, Steven
and here is what I would like the example to come out like:
GroupID, MAC Address, IP Address, Name
1, 8a7sd87sad, 192.168.1.100, John
2, 09a8sd098as, NULL, Steven
It needs to be completely dynamic though, as a new column name could be entered to the source table at any time...
I have tried Dynamic SQL and JOINs, but can only seem to get it to work correctly. I was starting to consider doing a loop similar to this:
Code Snippet
DECLARE @ColumnName varchar(128)
DECLARE @Sql varchar(255)
SELECT @ColumnName = [ColumnName] FROM tblSourceData
SELECT @Sql = 'SELECT [GroupId], [ColumnValue] AS ['+@ColumnName+'] FROM tblSourceData'
EXEC (@Sql)
Could somebody please point me in the right direction? I've heard a bit about Pivot tables in my search for this solution, is that perhaps the route I need to go?
Regards,
Justin
Hi
In SSIS i have an execute SQL task where i get the job_id as the output which i set it to a package variable.
Now what I need to do is i have to map this value to one of the columns of the OLEDB destination.
How can I do it? Please help me ASAP.
Thanks
Sai
Hi All,
We are trying to unpivot the columns into rows but the colunms are changing dynamically, want to know how to set the dynamic value or variable in unpivot query, query is mentioned below:
declare @aw Varchar(100)
set @aw = '1990,1991'
SELECT [name], [year], [data]
FROM (SELECT * FROM t1 where [name] != 'name') p
UNPIVOT
([data] FOR [year] IN
(@aw)
)AS unpvt
The above query while executing geting error message "Incorrect syntax near '@aw'"
please can anybody solve this issue.
Thanks,
Syed
Hi,
I have to choose a particular column from a table whenever a condition met. That means i have to use the column in the select query whenever the condition met otherwise i have to create a dummy column with the same column name.
I tried to use the following sample query.
Eg:
Create table temp
(test_id int,
test varchar(100))
Declare @var varchar(100)
Set @Var='Hi'
Select
case when @Var='HI' then "None" else Test_new End as Test
from Temp
But it throw error as "Invalid column 'Test_new' "
As per my situation the Test_new column will be available in temp table whenever the condition is not met.So when @var<>'HI' i have to get the details of the test_new column otherwise i have to create a dummy column with the same name.
Hey, I have a search form with a selectbox. This selectbox contains the columnnames.I want when I put a text in a textbox and select a value in the selectbox and click submit that it search database.The Columnnames I put in a session.
If you see I have put in the querystring as columnname @sescolumn which I have initialised as asp:sessionparameter.But it gives no results. When I put @sescolumn between [] like normal columnnames are it doesn't work also.
Can someon put my on the right path?
<asp:SqlDataSource ID="Database_ecars" runat="server" ConnectionString="<%$ ConnectionStrings:connectionstring %>"
SelectCommand="SELECT [AutoID], [Merk], [Kleur], [Type], [Autotype], [prijs], [Zitplaatsen], [Afbeelding1], [Afbeelding2], [Afbeelding3], [Afbeelding4] FROM [Auto] where @sescolumn like @seskeyword and [AutoID] not in (select [AutoID] from [verhuring] where [StartVerhuur] >= @sesdatefrom and [Eindeverhuur] <= @sesdatetill)" >
<SelectParameters>
<asp:SessionParameter Name="sesdatefrom" SessionField="datefrom" Type="Decimal" />
<asp:SessionParameter Name="sesdatetill" SessionField="datetill" Type="Decimal" />
<asp:SessionParameter Name="seskeyword" SessionField="keyword" Type="string" />
<asp:SessionParameter Name="sescolumn" SessionField="columnname" Type="string" />
</SelectParameters>
</asp:SqlDataSource>
How can I make this stored procedure concept work:
-----------------------------------------------
Create Procedure MyProc @MyValue varchar(5)
As
Declare @ColumnName as varchar(11)
Set @ColumnName = 'Price_' + @MyValue
Select Sum(Price) As @ColumnName --????
From MyTable where Item = @MyValue
Return
-----------------------------------------------
Using @MyValue = 23 should make the Sum(Price) column name 'Price_23'. It's not working. What am I doing wrong?
Bjorn
I am unfortunately lumered with a table I cannot edit that stores a division 2 characters along with boolean fields '0506', '0607', '0708' ... (academic years) to represent if that particular division is active in the current academic year. This has made me have to resort to dynamic sql within a stored procedure to input the appropriate field names.
Is there a better way to do it, it's not mission critical to make it not use dynamic sql but I would prefer not to.
I want to write a query that will give me a dynamic amount of columns back.What I want to do, I want to create a calendar application, in which foreach employee, I want to show if he is in the office or not.this should look like:ID, Name, 1,2,3,4,5,6,7,888,Leo,0,0,1,1,1,0,0,1The amount of columns is dynamic, and is a period of time, with a column foreach day.Any suggestions what the best approach to this could be?ThanksLeo
View 3 Replies View RelatedIll start off by saying that I am an ASP.NET developer and I am self-taught in using SSRS2005. (quite poorly I might add) That being said I am having a problem with a report im building. This report has 10-12 different metrics I am reporting on and I have each of them displayed in their own matrix. The first matrix was created by the report wizard that launches when you add a new report.
To give you an overview of how I am setting up these reports I will describe the structure I am using on nearly all of the matrices. I am reporting on classes that are managed by my company and held at 3rd party locations. My matrix has the 3rd party location in the rows and the quarter the class was held is the column. Matrix setup looks like this:
________|_______Quarter____________
|MetricA|MetricB|MetricC|etc...
LocationA|
LocationB|
LocationC|
The problem Im having is this. All matrices that weren't created by the wizard don't grow dynamically. Say I have 2 quarters of data I am reporting on, Instead of having a column for quarter1 and quarter 2, the report generates separate matrices for each quarter. This is true for each matrix that I created using a matrix control from the toolbox. The matrix that was created by the wizard however, has dynamic columns that grow to the right as expected.
I am populating my datasets using Stored Procedures and the matrix that behaves normally uses a different dataset than the 4 others I have completed thus far which share a dataset.
I have looked everywhere I can think of for a checkbox or a property I need to adjust and I cant find one. Anyone have any ideas??
hello,
in the script task component - i need to refer to Row.Column9.ToString() but the Column9 is coming from a variable. meaning - at run time it can be Column9 or Column7 at other runs. how can i write Row.ColumnName.ToString() in a dynamic way? (aka that the ColumnName is dynamic and not hard coded?)
thanks!
Arik