Changing columns name in a temp table

Dear All,

I'm trying to alter the name of several columns' in a table which gets created in a stored procedure.

trying to use:

exec sp_rename '#tblBd.week1', '2007_18', 'COLUMN'

I get:

Server: Msg 15248, Level 11, State 1, Procedure sp_rename, Line 163
Either the parameter @.objname is ambiguous or the claimed @.objtype (COLUMN) is wrong.

There is no mistype, the table name and column name are correct.

Can temp table's column names not be altered?

If yes, how?

Thanks in advance!Hi gorgenyi

Let's cover the obvious stuff first of all to get an idea of what you are doing and why.
1) Why not define your temp table with the correct column names in the first place?
2) Why not alias your column names in the result set you create from the temp table?|||ok, here it goes.

I created a report with a stored procedure which displays resource allocation to projects in the BD pipeline week by week for the next 10 weeks ahead.

the end reult looks like this:

Project name, w1, w2, w3, w4, w5, w6 etc...
Prj A 1 2 2 1
Prj B 1 2 2 2.5 2 2
Prj C .5 1 1 1.5 1
...

I'm using w1, w2, w3 for the weeks ahead during the stored procedure for multiple joins, however I'd like to change them to the always relevant week numbers programatically in the follwoing format '2007-17', '2007-18', '2007-19' ... etc at the end so I can publish these as the datagrid's header.

Did I explain clearly? What would you recomend? Thanks for your help on this.|||You are making the all too common mistake of trying to use SQL and/or Query Analyzer as a reporting environment. It is not designed for this.
What you are trying to do is modify the way the data is displayed, and this is a job for Crystal Reports/Active Reports/Reporting Services/MS Access/Excel, or whatever reporting/interface platform you are using.
Dynamic column names are BAD, because then whatever reporting tool you use will never know what output schema to expect.|||this is a job for Crystal Reports/Active Reports/Reporting Services/MS Access/Excel, or whatever reporting/interface platform you are using.Somehow you conspired to miss with all those I think. I suspect this is just some .NET front end.

You can (I think) dynamically set up your column headers in a datagrid but I am afraid I cannot remember how.

blindman is right though - this is best handled in your front end rather than SQL.|||...or .Net or ASP.|||It just seemed very simple, and must say I do not have experience of the reporting tools. Here is what I did:

temp table to get next 10 weeks ids
temp table to get active BD items and the 10 columns with 0 values
then a double loop where I update each cell with a resource number to the appropriate BD item and the appropriate week number.

Dispay the data in datagrid, with headings for the weeks as Week 1, Week 2, etc. while adding the sums up / week in ASP.NET and do various other things like colouring background etc.

But I'd like to get the actually internal week reference displayed on the grid, which of course chnage weekly, so want to do it dynamicaly. But yes, point taken on ASP.NET won't know what col name they will be.

And yes, this is ASP.NET front end.|||Some ideas from the web. Maybe consider posting in an ASP.NET site if you can't follow them. You defo do not want to do this at the database.
http://www.thescripts.com/forum/thread320779.html
http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/Q_20976549.html

Change the Column datatype

Dear Friends
I need to change the column datatype from Real to Float
as now i am facing the problem when my user put value
140189.14 it is accepting only 104189.1
Please note that i have the same table in merge
replication and i do not want to stop the replication.
Please guide me to solve the problem.
Best regards
Sharad
the best way to do this is to use sp_repladdcolumn and sp_repldropcolumn
here are the steps
1) use sp_repladdcolumn to add a dummy column with the float data type. Call
this column dummy
2) update the table on the publisher so the dummy column has the values of
the column whose data type you are trying to change.
3) use sp_repldropcolumn to drop the column containing the real datatype
4) use sp_repladdcolumn to add the column back with the new float data type
5) update the table on the publisher so the new column has the values of the
dummy column
6) use sp_repldropcolumn to drop the dummy column
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Sharad" <niitmalad@.yahoo.co.in> wrote in message
news:172a01c4f3f6$6ccdaf60$a501280a@.phx.gbl...
> Dear Friends
> I need to change the column datatype from Real to Float
> as now i am facing the problem when my user put value
> 140189.14 it is accepting only 104189.1
> Please note that i have the same table in merge
> replication and i do not want to stop the replication.
> Please guide me to solve the problem.
> Best regards
> Sharad
|||I have an idea, which may simplify this routine. I did not tried it myself
yet, but it should work just fine I guess
Let's say you have tableX with columnA which type should be changed from
real to float
1) Create temporary tableY with PK column (or columns) from tableX, and also
with columnA
2) Copy data from tableX to tableY
3) use sp_repldropcolumn to drop columnA (containing the real datatype) from
tableX
4) use sp_repladdcolumn to add the columnA back with the new float data type
5) Copy data from tableY column A to tableY columnA
6) Drop temporary tableY
This way replication subsystem has to do following steps:
1) Drop column
2) Add column
3) Process insert of data into columnA
Compared to the steps which should be performed when using earlier mentioned
approach:
1) Add dummy column
2) Process insert of data into dummy column from columnA
3) Drop column
4) Add column
5) Process insert of data into columnA from dummy column
6) Drop dummy column
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u5trHx$8EHA.1408@.TK2MSFTNGP10.phx.gbl...
> the best way to do this is to use sp_repladdcolumn and sp_repldropcolumn
> here are the steps
> 1) use sp_repladdcolumn to add a dummy column with the float data type.
Call
> this column dummy
> 2) update the table on the publisher so the dummy column has the values of
> the column whose data type you are trying to change.
> 3) use sp_repldropcolumn to drop the column containing the real datatype
> 4) use sp_repladdcolumn to add the column back with the new float data
type
> 5) update the table on the publisher so the new column has the values of
the
> dummy column
> 6) use sp_repldropcolumn to drop the dummy column
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Sharad" <niitmalad@.yahoo.co.in> wrote in message
> news:172a01c4f3f6$6ccdaf60$a501280a@.phx.gbl...
>
|||Yes, this is a much simpler and better way of doing it. Thanks Kestutis!
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Kestutis Adomavicius" <kicker.lt@.nospaamm_tut.by> wrote in message
news:uVd2uDA9EHA.3756@.TK2MSFTNGP14.phx.gbl...
> I have an idea, which may simplify this routine. I did not tried it myself
> yet, but it should work just fine I guess
> Let's say you have tableX with columnA which type should be changed from
> real to float
> 1) Create temporary tableY with PK column (or columns) from tableX, and
also
> with columnA
> 2) Copy data from tableX to tableY
> 3) use sp_repldropcolumn to drop columnA (containing the real datatype)
from
> tableX
> 4) use sp_repladdcolumn to add the columnA back with the new float data
type
> 5) Copy data from tableY column A to tableY columnA
> 6) Drop temporary tableY
> This way replication subsystem has to do following steps:
> 1) Drop column
> 2) Add column
> 3) Process insert of data into columnA
> Compared to the steps which should be performed when using earlier
mentioned[vbcol=seagreen]
> approach:
> 1) Add dummy column
> 2) Process insert of data into dummy column from columnA
> 3) Drop column
> 4) Add column
> 5) Process insert of data into columnA from dummy column
> 6) Drop dummy column
> --
> Regards,
> Kestutis Adomavicius
> Consultant
> UAB "Baltic Software Solutions"
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:u5trHx$8EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Call
of
> type
> the
>

Change text color

Dear all,

May I know how can I change a text color if the data meet certain condition?

Thanks in advance

Levine

You can set this in the properties window.
Click on the textbox you want to change and then click in the "Color" box.. select the dropdown in that field and clickon "Expression..".

Then use a formula such as:

=IIF( Sum(Fields!Hours.Value)>=7 , "Black", "Crimson")

The expression builder can assist you with your own data fields etc.

Change Table's Font

Dear All
I would like to change TABLE FONT other english lange in MS SQL SERVER
2000,can? if yes How could we do?
TIATables don't have fonts. The display font is determined by your client
application and you haven't told us what that is. Assuming you are
using Query Analyzer, go to Tools/Options/Fonts.
--
David Portas
SQL Server MVP
--

Change Table's Font

Dear All
I would like to change TABLE FONT other english lange in MS SQL SERVER
2000,can? if yes How could we do?
TIA
Tables don't have fonts. The display font is determined by your client
application and you haven't told us what that is. Assuming you are
using Query Analyzer, go to Tools/Options/Fonts.
David Portas
SQL Server MVP

Change Table's Font

Dear All
I would like to change TABLE FONT other english lange in MS SQL SERVER
2000,can? if yes How could we do?
TIATables don't have fonts. The display font is determined by your client
application and you haven't told us what that is. Assuming you are
using Query Analyzer, go to Tools/Options/Fonts.
--
David Portas
SQL Server MVP
--

change standard security password of sa

Dear All,
I failed to change the standard security password of sa
which is generated automatically. Whatever I changed it by
the function sp_password and it alerted successful. The
password of sa is still the same as before. How to change
the password of sa ......../.According to Books Online:
sp_password [ [ @.old =3D ] 'old_password' , ] { [ @.new =3D] 'new_password' } [ , [ @.loginame =3D ] 'login' ]
So, you would use something like this:
sp_password 'thecurrentpassword', 'thenewpassword', 'sa'
-- Keith
"ken" <anonymous@.discussions.microsoft.com> wrote in message =news:09c701c3c626$86cca000$a301280a@.phx.gbl...
> Dear All,
> I failed to change the standard security password of sa
> which is generated automatically. Whatever I changed it by
> the function sp_password and it alerted successful. The
> password of sa is still the same as before. How to change
> the password of sa ......../.|||When SQL Server is isntalled the sa password in NULL. There is not
automatically generated password. So if after installation the password for
sa in not NULL then someone is changing it after the installation.
Rand
This posting is provided "as is" with no warranties and confers no rights.

change standard security password of sa

Dear All,
I failed to change the standard security password of sa
which is generated automatically. Whatever I changed it by
the function sp_password and it alerted successful. The
password of sa is still the same as before. How to change
the password of sa ......../.Try logging into SQL Server using Windows Authentication as an
administrator on the machine. Then use Enterprise Manager, right click on
the sa account and change the password.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Change SA password. Any effect to replication?

Dear all,

I have some questions regard subject above. I want to know the impact of replication process after SA pasword been changed.

Example: server1 (hq), server2 (district) & server3 (district).

If server1, SA password been changed, is it server2 & server3 need to change too?

may i know where is it the replication SA password stored? is it replication script refer ID (SA) from Security -> Logins?

I hope someone can help me.. thanks.

changing the Sa password would not harm the replication process

ideally...

replication is using the account used by the sqlserver agent services.

to know what account is your agent services running.

click start>run>type "services.msc">look for sqlserver agent

Click on Log On> this is the account used by replication.

usually this is a domain account..

if your system is configure in this manner, then changing the "Sa" password

will not matter at all.

If it configured some other way around then you have to watch out for the account

being used in the replication

Change of NT password user affect to service

Dear all,
Whenever that I change my password on domain NT, then MSSQLSERVER
service failed (local sql server of course). So that I've got to enter in
that service
and update the password manually on LOG ON flap.
At the same time when I open my enterprise manager not appears all those
Network Sql Serverthat before I had. '?
I would like to have automatically this change.
Any help will be welcomed,
Thanks in advance,Enric,
That means that the service is using your account and every time you change
your password, you have to update the service.
Why you do not create an account just for the service?
See "Setting up Windows Services Accounts" in BOL.
AMB
"Enric" wrote:

> Dear all,
> Whenever that I change my password on domain NT, then MSSQLSERVER
> service failed (local sql server of course). So that I've got to enter in
> that service
> and update the password manually on LOG ON flap.
> At the same time when I open my enterprise manager not appears all those
> Network Sql Serverthat before I had. '?
> I would like to have automatically this change.
> Any help will be welcomed,
> Thanks in advance,|||It is better to create a special service account rather than using your own
for SQL Server services.
Hope this helps.
Dan Guzman
SQL Server MVP
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:DEF51AED-F720-46A1-9512-EBEE6E6DBFA2@.microsoft.com...
> Dear all,
> Whenever that I change my password on domain NT, then MSSQLSERVER
> service failed (local sql server of course). So that I've got to enter in
> that service
> and update the password manually on LOG ON flap.
> At the same time when I open my enterprise manager not appears all those
> Network Sql Serverthat before I had. '
> I would like to have automatically this change.
> Any help will be welcomed,
> Thanks in advance,|||Local System Account?
take care,
"Dan Guzman" wrote:

> It is better to create a special service account rather than using your ow
n
> for SQL Server services.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:DEF51AED-F720-46A1-9512-EBEE6E6DBFA2@.microsoft.com...
>
>|||Enric,
He meant a "domain user account".
AMB
"Enric" wrote:
> Local System Account?
> take care,
> "Dan Guzman" wrote:
>|||You could use the build-in Local System account if access to network
resources isn't needed (e.g. backup to UNC). As Alejandro suggested, I was
thinking of a domain account since this is apparently what you are currently
using.
Hope this helps.
Dan Guzman
SQL Server MVP
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:E2E61BAB-6C00-4B9A-AC44-FCD759E22E6E@.microsoft.com...
> Local System Account?
> take care,
> "Dan Guzman" wrote:
>

Change of Behavior in Pipeline

Dear Forum Members,

As part of the pipeline work to improve performance and stability, we are considering a change in how buffers are managed, which will affect custom transforms. Michael Entin explained what is about to change in the link below:

http://blogs.msdn.com/michen/archive/2007/08/31/Buffer.EndOfRowset.aspx

My questions are :

What is the likelihood of your scenarios broken by this?

How expensive is it to change your custom component code as Michael suggests in his blog?

Thanks,

Deniz Erkan - msft

To summarize the impact, can I assume that we're only talking about SQL Server 2008, with which custom transformations for SQL 2005 would need to be recoded anyway so as to take advantage of the new interfaces (e.g. IDTSxxxx100), for example?

In other words, it shouldn't be a huge issue as SQL Server 2008 isn't even released, correct? And custom component development at this juncture might be a bit premature?

We're not changing the behavior in SQL Server 2005, are we?|||I have no problem with this because I saw that blog entry and fixed my code. |||

>We're not changing the behavior in SQL Server 2005, are we?

The SQL 2005 behavior will not be changed.

The concern is scenario when someone takes a component developed for SQL 2005, mechanically renames the interfaces, recompiles it and and uses it in SQL 2008. If the component used incorrect pattern, the new version will be producing incorrect results in SQL 2008.

|||

Phil,

Yes, your assumptions are correct. This is only for 2008, and custom components developed for 2005 will have to be re-compiled at a minimum to work against the new interfaces.

However, what otherwise would be a simple re-compile job, now would be an additional check on the semantics of the custom component to make sure the "end of buffer logic" is done properly as per Michael's post.

oh, and I didn't see Michael's post prior to this, apologies for repetition.

|||

I don't see a major issue with this. As part of an ETL process, the record counts should be reconciled anyway, so this should be easy to catch.

|||

Transform component authors need to change behavior together for 2008, as not everyone has access to the source code of all the non stock components they use. To me, this issue is more coordination then rocket science...

As jwelch mentioned, I don't think the problem would be all that difficult to detect, since rowcounts should be part of an etl process, but then there's the fix and recompile part...

For example, we don't have source code for some non-stock components, but Reflector shows they exhibit the incorrect behavior. So while I've recompiled owned source code, I just hope everybody gets the message, or is willing to part with source.

|||

I rememeber this confusing me at the time I started writing components. I got it into my head that calling buffer.NextRow() when there weren't any caused an error so I put the EndOfRowset check first.

Not too difficult to change in my components.

Will SQL2005 compiled components work in a SQL2008 pipeline, if not then everyone has to recompile there code anyway.

|||

SimonS_ wrote:

Will SQL2005 compiled components work in a SQL2008 pipeline, if not then everyone has to recompile there [sic] code anyway.

No. You have to edit and recompile to use the new interfaces.

http://blogs.msdn.com/mattm/archive/2007/06/05/katmai-custom-components-and-upgrade.aspx|||

There's no big issue here that affects me. I have one question though - this will ge changed in the docs right?

And, have the SSIS2005 docs been updated yet as per Michael's post? I know Michael said that this was work-in-progress.

-Jamie

Change of Behavior in Pipeline

Dear Forum Members,

As part of the pipeline work to improve performance and stability, we are considering a change in how buffers are managed, which will affect custom transforms. Michael Entin explained what is about to change in the link below:

http://blogs.msdn.com/michen/archive/2007/08/31/Buffer.EndOfRowset.aspx

My questions are :

What is the likelihood of your scenarios broken by this?

How expensive is it to change your custom component code as Michael suggests in his blog?

Thanks,

Deniz Erkan - msft

To summarize the impact, can I assume that we're only talking about SQL Server 2008, with which custom transformations for SQL 2005 would need to be recoded anyway so as to take advantage of the new interfaces (e.g. IDTSxxxx100), for example?

In other words, it shouldn't be a huge issue as SQL Server 2008 isn't even released, correct? And custom component development at this juncture might be a bit premature?

We're not changing the behavior in SQL Server 2005, are we?|||I have no problem with this because I saw that blog entry and fixed my code. |||

>We're not changing the behavior in SQL Server 2005, are we?

The SQL 2005 behavior will not be changed.

The concern is scenario when someone takes a component developed for SQL 2005, mechanically renames the interfaces, recompiles it and and uses it in SQL 2008. If the component used incorrect pattern, the new version will be producing incorrect results in SQL 2008.

|||

Phil,

Yes, your assumptions are correct. This is only for 2008, and custom components developed for 2005 will have to be re-compiled at a minimum to work against the new interfaces.

However, what otherwise would be a simple re-compile job, now would be an additional check on the semantics of the custom component to make sure the "end of buffer logic" is done properly as per Michael's post.

oh, and I didn't see Michael's post prior to this, apologies for repetition.

|||

I don't see a major issue with this. As part of an ETL process, the record counts should be reconciled anyway, so this should be easy to catch.

|||

Transform component authors need to change behavior together for 2008, as not everyone has access to the source code of all the non stock components they use. To me, this issue is more coordination then rocket science...

As jwelch mentioned, I don't think the problem would be all that difficult to detect, since rowcounts should be part of an etl process, but then there's the fix and recompile part...

For example, we don't have source code for some non-stock components, but Reflector shows they exhibit the incorrect behavior. So while I've recompiled owned source code, I just hope everybody gets the message, or is willing to part with source.

|||

I rememeber this confusing me at the time I started writing components. I got it into my head that calling buffer.NextRow() when there weren't any caused an error so I put the EndOfRowset check first.

Not too difficult to change in my components.

Will SQL2005 compiled components work in a SQL2008 pipeline, if not then everyone has to recompile there code anyway.

|||

SimonS_ wrote:

Will SQL2005 compiled components work in a SQL2008 pipeline, if not then everyone has to recompile there [sic] code anyway.

No. You have to edit and recompile to use the new interfaces.

http://blogs.msdn.com/mattm/archive/2007/06/05/katmai-custom-components-and-upgrade.aspx|||

There's no big issue here that affects me. I have one question though - this will ge changed in the docs right?

And, have the SSIS2005 docs been updated yet as per Michael's post? I know Michael said that this was work-in-progress.

-Jamie

Change of Behavior in Pipeline

Dear Forum Members,

As part of the pipeline work to improve performance and stability, we are considering a change in how buffers are managed, which will affect custom transforms. Michael Entin explained what is about to change in the link below:

http://blogs.msdn.com/michen/archive/2007/08/31/Buffer.EndOfRowset.aspx

My questions are :

What is the likelihood of your scenarios broken by this?

How expensive is it to change your custom component code as Michael suggests in his blog?

Thanks,

Deniz Erkan - msft

To summarize the impact, can I assume that we're only talking about SQL Server 2008, with which custom transformations for SQL 2005 would need to be recoded anyway so as to take advantage of the new interfaces (e.g. IDTSxxxx100), for example?

In other words, it shouldn't be a huge issue as SQL Server 2008 isn't even released, correct? And custom component development at this juncture might be a bit premature?

We're not changing the behavior in SQL Server 2005, are we?|||I have no problem with this because I saw that blog entry and fixed my code. |||

>We're not changing the behavior in SQL Server 2005, are we?

The SQL 2005 behavior will not be changed.

The concern is scenario when someone takes a component developed for SQL 2005, mechanically renames the interfaces, recompiles it and and uses it in SQL 2008. If the component used incorrect pattern, the new version will be producing incorrect results in SQL 2008.

|||

Phil,

Yes, your assumptions are correct. This is only for 2008, and custom components developed for 2005 will have to be re-compiled at a minimum to work against the new interfaces.

However, what otherwise would be a simple re-compile job, now would be an additional check on the semantics of the custom component to make sure the "end of buffer logic" is done properly as per Michael's post.

oh, and I didn't see Michael's post prior to this, apologies for repetition.

|||

I don't see a major issue with this. As part of an ETL process, the record counts should be reconciled anyway, so this should be easy to catch.

|||

Transform component authors need to change behavior together for 2008, as not everyone has access to the source code of all the non stock components they use. To me, this issue is more coordination then rocket science...

As jwelch mentioned, I don't think the problem would be all that difficult to detect, since rowcounts should be part of an etl process, but then there's the fix and recompile part...

For example, we don't have source code for some non-stock components, but Reflector shows they exhibit the incorrect behavior. So while I've recompiled owned source code, I just hope everybody gets the message, or is willing to part with source.

|||

I rememeber this confusing me at the time I started writing components. I got it into my head that calling buffer.NextRow() when there weren't any caused an error so I put the EndOfRowset check first.

Not too difficult to change in my components.

Will SQL2005 compiled components work in a SQL2008 pipeline, if not then everyone has to recompile there code anyway.

|||

SimonS_ wrote:

Will SQL2005 compiled components work in a SQL2008 pipeline, if not then everyone has to recompile there [sic] code anyway.

No. You have to edit and recompile to use the new interfaces.

http://blogs.msdn.com/mattm/archive/2007/06/05/katmai-custom-components-and-upgrade.aspx|||

There's no big issue here that affects me. I have one question though - this will ge changed in the docs right?

And, have the SSIS2005 docs been updated yet as per Michael's post? I know Michael said that this was work-in-progress.

-Jamie