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

Changing Collation in SQL Server 2000

Hi.

I don't know how to change the collation in SQL Server 2000, my collation is Modern_Spanish_CI_AS, if somedoby knows the procedure to change the collation I would be thankful.

Thank you again and best regards.

Christian

Hi,

For database, ALTER DATABASE databasename COLLATE collation_name.

Change the order rule of tables, ALTER TABLE TabelName ALTER COLUMN ColumnName varchar(10)COLLATE ordername NOT NULL

Some good links for you to refer:

http://support.microsoft.com/kb/325335
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=60020&SiteID=1

Thanks.

Changing BLL parameter

I have inherited a web site from another developer. I am new to .net programming. Anyway I have had to add a parameter to an sql procedure and I thought i had made the change in the business logic layer correctly. I am using gridview to display and export to an excel spreadsheet. After my changes my page lets me select my parameters, but now my page come up blank, no error messages the only thing on the page are the save and export buttons.

Please help what have i missed.

Steve

You should provide more details, probably give some code excerpts for others to make suggestions.sql

changing an activatin stored procedure

Hello,

After I changed the activation stored procedure of a queue, the old activation stored procedure keeps being launched by the service associated with the queue. I turned the queue off and on, but to no avail. I also checked the dynamic view sys.service_queues to make sure the proper stored procedure exists in the field activation_procedure. Do you have any idea about what might have gone wrong?

Thanks

Mugur,

Changing the associated procedure does not shut down any running instance of the previous procedure. So if an instance or the procedure is running it will continue to run until it will exit on its own. One common problem is a logic defect in the procedure that causes it to never exit, in this case you must shut it down forcibly (i.e. using KILL, see http://msdn2.microsoft.com/en-us/library/ms173730.aspx)

HTH,
~ Remus

|||Thanks a lot Remus.

changing a database location for a report

hi,
i am new to crystal. i have a report that uses a stored procedure from a database A. I have the same stored proc. in a database B.
Please guide me through the steps to point my report to the Stored proc in database B.this is a lil urgent. Thanks,
matuGoto Database-->Set Location and choose the Stored Procedure

Change TSQl statement using case

Hello all, Thanks in advance to contributions.
I would like to be able to manipulate myTSQL in a stored procedure, based
on a value in a parameter, using the case statement.
I want to avoid Begin..End constructs
My code is such...
select field1,field2 etc etc
from Order o
inner join tablex oa on o.order_id = oa.order_id
-- etc etc p to 8 joins
where
-- some predicate
-- then the bit I want to case
case @.MyParameter
when 'IS_MANAGER' then (and o.reason = 'T1' or o.reason = 'T2')
when 'DE_MANAGER' then (and o.reason = 'T3')
end
So, what I am trying to do is change the overall selection predicate based
on the
value of the @.MyParameter. I am trying to avoid wrapping the whole code
block up into seperate Begin..End constructs if possible as it will make the
SP very big and ugly.
Amy I trying to break SLQ rules here?
Cheers
Scotchy
eg.Scotchy wrote:

> So, what I am trying to do is change the overall selection predicate
> based on the
> value of the @.MyParameter. I am trying to avoid wrapping the whole
> code block up into seperate Begin..End constructs if possible as it
> will make the SP very big and ugly.
> Amy I trying to break SLQ rules here?
That is indeed not possible. You have the same problem as a couple of
threads above, check "Stored procedure using dynamic WHERE statement"
for the offered solutions.
HTH,
Stijn Verrept.|||yep, I was clutching a straws and hoping :-)
cheers
"Stijn Verrept" wrote:

> Scotchy wrote:
>
> That is indeed not possible. You have the same problem as a couple of
> threads above, check "Stored procedure using dynamic WHERE statement"
> for the offered solutions.
> --
> HTH,
> Stijn Verrept.
>|||declare @.parm int
declare @.sql nvarchar(400)
set @.parm = 1
set @.sql = 'select * from table1 where Type IS NOT NULL and '
if ( @.parm = 1 )
set @.sql = @.sql + 'type = ''t1'''
else if ( @.parm = 2 )
set @.sql = @.sql + 'type = ''t2'''
else
print 'error'
exec (@.sql)
William Stacey [MVP]
"Scotchy" <Scotchy@.discussions.microsoft.com> wrote in message
news:5CB3CA78-0973-4ED8-B308-7B3028CBB8F2@.microsoft.com...
> Hello all, Thanks in advance to contributions.
> I would like to be able to manipulate myTSQL in a stored procedure, based
> on a value in a parameter, using the case statement.
> I want to avoid Begin..End constructs
> My code is such...
> select field1,field2 etc etc
> from Order o
> inner join tablex oa on o.order_id = oa.order_id
> -- etc etc p to 8 joins
> where
> -- some predicate
> -- then the bit I want to case
> case @.MyParameter
> when 'IS_MANAGER' then (and o.reason = 'T1' or o.reason = 'T2')
> when 'DE_MANAGER' then (and o.reason = 'T3')
> end
> So, what I am trying to do is change the overall selection predicate based
> on the
> value of the @.MyParameter. I am trying to avoid wrapping the whole code
> block up into seperate Begin..End constructs if possible as it will make
> the
> SP very big and ugly.
> Amy I trying to break SLQ rules here?
> Cheers
> Scotchy
>
>
>
> eg.|||-- Couple of things: do you need to CASE? Can't you do something like this
?
USE Northwind
GO
DECLARE @.EmployeeID INT
SET @.EmployeeID = 2
-- Normal query
SELECT *
FROM orders
WHERE EmployeeID = @.EmployeeID
-- You can use CASE in queries. Think of them like a function which returns
a value, so you have to put it on one side of an equals:
SELECT *
FROM orders
WHERE CustomerID =
CASE @.EmployeeID
WHEN 1 THEN 'ERNSH'
WHEN 2 THEN 'BLONP'
ELSE ''
END
-- Or multiple criteria. You could even nest your case statements.
SELECT *
FROM orders
WHERE CustomerID =
CASE
WHEN @.EmployeeID = 1 AND OrderDate < '19970101' THEN 'ERNSH'
WHEN @.EmployeeID = 2 AND OrderDate < '19970101' THEN 'BLONP'
ELSE ''
END
-- Let me know how you get on.
--
--
-- Damien
"Scotchy" wrote:

> Hello all, Thanks in advance to contributions.
> I would like to be able to manipulate myTSQL in a stored procedure, based
> on a value in a parameter, using the case statement.
> I want to avoid Begin..End constructs
> My code is such...
> select field1,field2 etc etc
> from Order o
> inner join tablex oa on o.order_id = oa.order_id
> -- etc etc p to 8 joins
> where
> -- some predicate
> -- then the bit I want to case
> case @.MyParameter
> when 'IS_MANAGER' then (and o.reason = 'T1' or o.reason = 'T2')
> when 'DE_MANAGER' then (and o.reason = 'T3')
> end
> So, what I am trying to do is change the overall selection predicate based
> on the
> value of the @.MyParameter. I am trying to avoid wrapping the whole code
> block up into seperate Begin..End constructs if possible as it will make t
he
> SP very big and ugly.
> Amy I trying to break SLQ rules here?
> Cheers
> Scotchy
>
>
>
> eg.|||On Tue, 20 Dec 2005 18:07:01 -0800, Scotchy wrote:

>Hello all, Thanks in advance to contributions.
>I would like to be able to manipulate myTSQL in a stored procedure, based
>on a value in a parameter, using the case statement.
>I want to avoid Begin..End constructs
>My code is such...
>select field1,field2 etc etc
>from Order o
>inner join tablex oa on o.order_id = oa.order_id
>-- etc etc p to 8 joins
>where
> -- some predicate
> -- then the bit I want to case
> case @.MyParameter
> when 'IS_MANAGER' then (and o.reason = 'T1' or o.reason = 'T2')
> when 'DE_MANAGER' then (and o.reason = 'T3')
> end
>So, what I am trying to do is change the overall selection predicate based
>on the
>value of the @.MyParameter. I am trying to avoid wrapping the whole code
>block up into seperate Begin..End constructs if possible as it will make th
e
>SP very big and ugly.
>Amy I trying to break SLQ rules here?
Hi Scotchy,
CASE is an expression, not a statement. Check the examples in Books
Online to get a feeling for what you can accomplish with CASE. And never
think that CASE in SQL Server is similar to CASE in languages such as C.
For your situation, a combination of OR and AND works better than a
CASE:
WHERE ...
AND ( (@.MyParameter = 'IS_MANAGER' AND o.reason IN ('T1', 'T2') )
OR (@.MyParameter = 'DE_MANAGER' AND o.reason = 'T3' ))
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Change the Column Heading on select statement

I have a problem I don't know if there's a way to solve it.

I have a store procedure with 2 parameter the first parameter is the booking ID which I used in the where statement the other one is the type "D" for Driver and "H" for helper actually they are on the same table I'm asking is it possible to change the column heading in the select below as you can see the "AS [DRIVER NAME]" what if the type passed is type "H" means is it possible to change it to "[HELPER NAME]" on the same select statement.

SELECT Booking_ID AS [BOOKING ID], UPPER(Lastname_VC)+', '+Firstname_VC+' '+Middlename_VC AS [DRIVER NAME] FROM Boo_DrvHlp_T INNER JOIN Boo_TripHist_T
ON Boo_DrvHlp_T.Employee_ID=Boo_TripHist_T.Employee_I D AND Boo_TripHist_T.Type=@.Type WHERE Booking_ID=@.BookingID

thanks.RE: I have a problem I don't know if there's a way to solve it.
I have a store procedure with 2 parameter the first parameter is the booking ID which I used in the where statement the other one is the type "D" for Driver and "H" for helper actually they are on the same table I'm asking is it possible to change the column heading in the select below as you can see the "AS [DRIVER NAME]" what if the type passed is type "H" means is it possible to change it to "[HELPER NAME]" on the same select statement.
SELECT Booking_ID AS [BOOKING ID], UPPER(Lastname_VC)+', '+Firstname_VC+' '+Middlename_VC AS [DRIVER NAME] FROM Boo_DrvHlp_T INNER JOIN Boo_TripHist_T
ON Boo_DrvHlp_T.Employee_ID=Boo_TripHist_T.Employee_I D AND Boo_TripHist_T.Type=@.Type WHERE Booking_ID=@.BookingID thanks.

Q1 [Is it possible to have a result set heading e.g. , 'DRIVER NAME', conditionally appear as something else e.g. , 'HELPER NAME', depending on a passed parameter being either 'D' or 'H'?]
A1 Yes. Assign the passed parameter to a variable in the stored procedure; use a Case statement to evaluate and appropriatly use either 'DRIVER NAME' or 'HELPER NAME' in your result header column.

the logic [psudocode] amounts to something like:
Case @.vPassedParameter = 'H'
Then @.vColumnHeader = 'HELPER NAME'
Case @.vPassedParameter = 'D'
Then @.vColumnHeader = 'DRIVER NAME'

Change the active user in sp

Hello,
I would like to change the active user in a stored procedure
Ex. :
I'm logged on sql as "userA".
I call the stored procedure "spGetInfo".
In the first line of "spGetInfo" I would like to do something like "su poweruser", query some data and do "su system_user"

Is there anything like the unix "su" command in SQL ?

Thank a lot

Felix Pageau
fpageau@.SPAMSUCK.str.caI'm not quite sure what you are asking. Assuming that the user has the permission to execute a stored procedure, that stored procedure executes in the security context of the user that CREATES the procedure. If the dbo creates a procedure, the procedure can do anything that the dbo can do when any user runs it.

The only exception is that dynamic SQL always runs in the context of the currently logged in user. That can hang you up, but otherwise you should be fine.

-PatP|||Hello,

my problem is that the stored procedure contain dynamic sql. I need to execute the dynamic sql query without giving the user to query the table.

I thought that with a kind of "impersonation" I would be able to do so but I haven't found any.

Do you know a way to execute dynamic sql in another context than then one of the user that has called the sp ?

Thank for your reply|||The quickest and easiest answer is probably an extended stored procedure (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_sa2_67vp.asp). I don't know of anything in Transact-SQL that will allow you to do that.

-PatP|||Do you mean I should create an extended sp with my sql code or that I should call an existing extend sp ?|||If you need to "switch context" to allow dynamic SQL to execute, I'm suggesting that you write an extended stored procedure that allows you to control what gets executed and how. I don't know of a way to get you what you want using just Transact-SQL.

-PatP|||Even if you change the user inside your stored procedure from simple user to system admin user, the proc would still be running under the simple user access rights.
Best would be to define the access rights using the role, stored procedure etc etc and then control the sql.

Unix and SQL are entirely two different world.

change table orientation

I'm looking for a stored procedure what will change the orientation of a
table, so the columns will become the rows and the rows will become the
columns, for example:
Original Table:
StateName StateLongNames StateNumber
========= ============== ===========
CA California 10
FL Florida 20
TX Texas 30
The resulting table should be:
Col1 Col2 Col3 Col4
============== ========== =========== ==========
StateName CA FL TX
StateLongNames California Florida Texas
StateNumber 10 20 30
Note 1: This is not the same as pivot or crosstab tables.
Note 2: No agregated functions are necessary.
Note 3: It has to work on SQL Server 2000.
Thank you,Check this link
http://www.sqlteam.com/item.asp?ItemID=2955
Hope this helps.
--
"wheresaldo" wrote:

> I'm looking for a stored procedure what will change the orientation of a
> table, so the columns will become the rows and the rows will become the
> columns, for example:
> Original Table:
> StateName StateLongNames StateNumber
> ========= ============== ===========
> CA California 10
> FL Florida 20
> TX Texas 30
> The resulting table should be:
> Col1 Col2 Col3 Col4
> ============== ========== =========== ==========
> StateName CA FL TX
> StateLongNames California Florida Texas
> StateNumber 10 20 30
> Note 1: This is not the same as pivot or crosstab tables.
> Note 2: No agregated functions are necessary.
> Note 3: It has to work on SQL Server 2000.
> Thank you,
>|||hi... I saw this article a couple of days ago, it does not what at need, as
it creates a pivot table. what I need is much more simpler, just flit the
orientation of the table and all the data. This is NOT the same as Pivot,
Crosstab, or Transpond procedures, but easily .
Thank you
"Omnibuzz" wrote:
> Check this link
> http://www.sqlteam.com/item.asp?ItemID=2955
> Hope this helps.
> --
>
>
> "wheresaldo" wrote:
>|||A friend told me to do this, but it's outside my SQL knowledge:
1. Select count(*) for old_table. This will tell you how many columns to
create. Put this count into a variable (or user a cursor)
2. Build the string: "Create table new_table (col" + i + " varchar(50),
col" + ..... And execute it to create your table
3. Select the table structure to get the column names, use a cursor
3.a for each $col_name, Select $col_name, key from old_table order by key,
use cursor2
3.b build the string: "Insert into new_table values (" + $col_name + ", "
+ cursor2_value + ", " + cursor2_value, .... and execute.
3.c repeat loops
Does this make any sense to anyone?
"Omnibuzz" wrote:
> Check this link
> http://www.sqlteam.com/item.asp?ItemID=2955
> Hope this helps.
> --
>
>
> "wheresaldo" wrote:
>|||You can do this with CASE expressions. See the following article for an
example. A query always returns a fixed number of columns so you will
always need to construct a query to match the appropriate number of
rows, either by hand or using dynamic SQL.
http://support.microsoft.com/defaul...b;EN-US;q175574
Hopefully you realize that this is generally a very silly to even
attempt in SQL.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I tried to apply the code you mentioned for the table below and it does not
seem to give the right result. Here is an example table:
CREATE TABLE #States
(
StateCode char(2),
StateName char(10),
StateNumber int,
StateCost decimal(18, 2)
)
INSERT INTO #States VALUES ('WI','Wisconsin',1, 45.11)
INSERT INTO #States VALUES ('OH','Ohio',2, 2.25)
INSERT INTO #States VALUES ('CA','California',3, 4.5)
INSERT INTO #States VALUES ('FL','Florida',1, 13.40)
INSERT INTO #States VALUES ('FL','Florida',3, 4.50)
As you can see, there is no unique id to use.
Here is how the table looks like:
StateCode StateName StateNumber StateCost
WI Wisconsin 1 45.11
OH Ohio 2 2.25
CA California 3 4.50
FL Florida 1 13.40
FL Florida 3 4.50
and I need it to look rotated or flipped:
Col1 Col2 Col3 Col3 Col4
WI OH CA FL FL
Wisconsin Ohio California Florida Florida
1 2 3 1 3
45.11 2.25 4.50 13.40 4.50
as you can see, the columns of the resulting table will have to receive
different data types, so I was thinking they can be all vchar(50). Also, as
the number of rows from the original table might vary, the resulting table
might have different amount of columns.
I could do the rotation or flip on the UI or front-end, but the reporting
tool I'm using expects a table... this is why I'm trying to do this on a
Stored Procedure.
I'm surpriced that I have not been able to find this on the web, I think
many people might have the same need.
"David Portas" wrote:

> You can do this with CASE expressions. See the following article for an
> example. A query always returns a fixed number of columns so you will
> always need to construct a query to match the appropriate number of
> rows, either by hand or using dynamic SQL.
> http://support.microsoft.com/defaul...b;EN-US;q175574
> Hopefully you realize that this is generally a very silly to even
> attempt in SQL.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||wheresaldo wrote:
> I tried to apply the code you mentioned for the table below and it does no
t
> seem to give the right result. Here is an example table:
>
I suspect that the reason you find this difficult is that your existing
data doesn't have enough information to solvet he problem. What is the
key of your table? Every table needs a key but you haven't specified
one.
Now, in your rotated output you have put "WI" in column 1 and "OH" in
column 2. But where did you get the idea that "WI" came first and "OH"
came second? That information (1st, 2nd, etc) isn't in your table at
all! So you can't possibly write a generic query to return that result.
Lastly, what is the key in the result you want to ouput? You originally
asked for the column names in col1 but those don't exist in the data
either so you'll have to supply them in your query. Without a key you
may have a hard time constructing any useful information from the
output.
The first example in the link I posted shows how you can do it with
subqueries. By implication you need to supply the missing information
as part of those subqueries.

> I could do the rotation or flip on the UI or front-end, but the reporting
> tool I'm using expects a table... this is why I'm trying to do this on a
> Stored Procedure.
For sure you'll need to supply a tabular result set to the reporting
tool. But every reporting tool I know of will construct this kind of
pivot table from the base data - provided of course that you supply
enough information to complete the task.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks for you input. Here is the code to rotate (or change the orientation
)
of a table. I'm not sure if it's the most effective and well-written code,
but it does exactly what I need it to do. Hope it can help someone else.
Feedback is welcome:
-- ########################################
######
-- Drop tables if already created.
-- Cannot use temp tables, becuase cannot
-- read structure.
-- ########################################
######
DROP TABLE source
DROP TABLE result
-- ########################################
######
-- create fake source table
-- ########################################
######
CREATE TABLE source
(
StateCode char(2),
StateName char(10),
StateNumber int,
StateCost decimal(18, 2)
)
INSERT INTO source VALUES ('WI','Wisconsin',1, 45.11)
INSERT INTO source VALUES ('OH','Ohio',2, 2.25)
INSERT INTO source VALUES ('CA','California',3, 4.5)
INSERT INTO source VALUES ('FL','Florida',1, 13.40)
INSERT INTO source VALUES ('FL','Florida',3, 4.50)
-- ########################################
######
-- Create result table.
-- The result table needs to have the same
-- number of columns as the source table
-- has rows, we do this dynamically.
-- Because a columns in the result table might
-- be pf different data types, each
-- column in the result table is a nvarchar(50).
-- ########################################
######
declare @.counter int
declare @.sql_create_table nvarchar(4000)
declare @.source_total_rows int
select @.source_total_rows=count(*) from source
set @.sql_create_table = 'CREATE TABLE result (columnheader varchar(100)'
set @.counter = 0
while @.counter < @.source_total_rows
begin
set @.counter = @.counter + 1
set @.sql_create_table = @.sql_create_table + ',col' + cast(@.counter as
char(1)) + ' varchar(50)'
end
set @.sql_create_table = @.sql_create_table + ')'
exec (@.sql_create_table)
-- ########################################
######
-- First Loop through the number of column from
-- the source table, we need this to get the
-- column names.
-- Second Loop reads each row from the source,
-- for one column, and then create an insert
-- statement to insert in the result table.
-- ########################################
######
declare @.sql_create_inserts nvarchar(4000)
declare @.source_column_name nvarchar(50)
declare @.source_number_of_columns int
declare @.temp_column_hold nvarchar(50)
SELECT @.source_number_of_columns = count(*)
FROM INFORMATION_SCHEMA.columns
WHERE TABLE_NAME = 'source'
set @.counter = 0
while @.counter < @.source_number_of_columns
begin
set @.counter = @.counter + 1
--#####################################
set @.source_column_name = COL_NAME(OBJECT_ID('source'), @.counter)
exec('declare cur cursor for select '+@.source_column_name+' from source')
open cur
fetch next from cur into @.temp_column_hold
set @.sql_create_inserts = 'insert into result values (''' +
@.source_column_name + ''''
while @.@.FETCH_STATUS = 0
begin
set @.sql_create_inserts = @.sql_create_inserts + ',''' +
cast(@.temp_column_hold As varchar(50)) + ''''
fetch next from cur into @.temp_column_hold
end
set @.sql_create_inserts = @.sql_create_inserts + ')'
exec(@.sql_create_inserts)
close cur
deallocate cur
--#####################################
end
select * from result
"David Portas" wrote:

> wheresaldo wrote:
> I suspect that the reason you find this difficult is that your existing
> data doesn't have enough information to solvet he problem. What is the
> key of your table? Every table needs a key but you haven't specified
> one.
> Now, in your rotated output you have put "WI" in column 1 and "OH" in
> column 2. But where did you get the idea that "WI" came first and "OH"
> came second? That information (1st, 2nd, etc) isn't in your table at
> all! So you can't possibly write a generic query to return that result.
> Lastly, what is the key in the result you want to ouput? You originally
> asked for the column names in col1 but those don't exist in the data
> either so you'll have to supply them in your query. Without a key you
> may have a hard time constructing any useful information from the
> output.
> The first example in the link I posted shows how you can do it with
> subqueries. By implication you need to supply the missing information
> as part of those subqueries.
>
> For sure you'll need to supply a tabular result set to the reporting
> tool. But every reporting tool I know of will construct this kind of
> pivot table from the base data - provided of course that you supply
> enough information to complete the task.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||I would also take care of the limitations:
- number of columns cannot exceed 1000
- row must fit on a page|||wheresaldo wrote:
> Thanks for you input. Here is the code to rotate (or change the orientati
on)
> of a table. I'm not sure if it's the most effective and well-written code
,
> but it does exactly what I need it to do. Hope it can help someone else.
> Feedback is welcome:
>
This bit is dangerous:

> exec('declare cur cursor for select '+@.source_column_name+' from source')
>
It retrieves the values in an undefined order. In itself that wouldn't
be too bad but because you go on to do the same for each column there
is absolutely no guarantee that the data for columns in the result will
line up with what was in each row in the source. What you should do is
add ORDER BY <some key>... but you still don't have a key in the source
table.
If you did have a key then you wouldn't need nested loops and cursors.
You could use a single query instead, which is generally preferable to
a cursor solution.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Change Stored Procedure name

Hello,

I want to change the name of a stored procedure. Does anyone know how I can do this?

If I simply change the name and repoint the report, the report thinks it's looking at an entirely new SP and so discards all the existing fields.

Any help would be great.

Thanks.Open the report and Do verify Database and save the report|||That won't work because the variables from the old SP are fully qualified.|||Hey

Under Database menu --> Set Location
Edit the SP's name in the Table field and it shouldnt toss ure
fields.

If u need..u can change the alias of the SP in Crystal to the new 1 also.
Cuz in CR8 the alias aint updated when u change the SP.

Hope this helps.|||Nice one.

Thanks.

Change SQL command at runtime using a stored procedure?

My query looks like this:
SELECT *
FROM table1 AS t1, table1 AS t2
WHERE t1.id =t2.id + @.myParam
I want @.myParam to have a value of =" AND t1.custId=t2.custId "
How can I make this work?
I did have my report working correctly with my source as an expression. But
with added complexities to the query, it will be easier to maintain in a
stored procedure.Sounds like you need to research dynamic sql
"KCBTDev" wrote:
> My query looks like this:
> SELECT *
> FROM table1 AS t1, table1 AS t2
> WHERE t1.id =t2.id + @.myParam
> I want @.myParam to have a value of =" AND t1.custId=t2.custId "
> How can I make this work?
> I did have my report working correctly with my source as an expression. But
> with added complexities to the query, it will be easier to maintain in a
> stored procedure.

change sql 2005 server name

I have Sql Server 2005 on my computer and i want to change the name of the server. I found this procedure but i didn't understand how it worked. So the actual name of my server is STATIE4 and i want to change it.

I executed this procedure, but the name of the server is still STATIE4. If i write sp_helpserver STATIE4 it doesn't find STATIE4. What have changed after using this procedure?

sp_dropserver old_name

GO

sp_addserver new_name, local

GO

to restart your SQL Server service. Connect agin and run the following command (It should output the new server name):

SELECT @.@.SERVERNAME
GO

HTH

Change SQL 2000 Server 64bit collation

Please point me to the procedure for changing the collation sequence on 64bit
installation of SQL Server 2000 (SP4). I can't find anything in KB or BOL.
Thanks,
Tom
Hi
Collations are a different levels, so there is not one place you will need
to change it. Changing the Collation of 64bit SQL Server is no different to
changing it on 32bit SQL Server. If this is a fresh install and you don't
have any databases to change then the best option is probably to re-install.
You can use rebuildm.exe to change the master database with a different
collation. Each database can have the collation changed using the ALTER
DATABASE command, this will not change the collation of existing columns that
have been set to the original collation. To do that you will need to run a
script that uses the ALTER TABLE command. If you the column are indexes or a
primary key, or have Foreign Keys pointing to them, the constraint will need
to be dropped. There are many threads on this for example
http://tinyurl.com/ky3q
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/arch...
Other links that may be useful:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/arch...
John
"T Morris" wrote:

> Please point me to the procedure for changing the collation sequence on 64bit
> installation of SQL Server 2000 (SP4). I can't find anything in KB or BOL.
> Thanks,
> Tom
|||Since I didn't find Rebuildm.exe in the binn folder for the 64bit
installation I thought there might be a different procedure.
But per your email, I use the same procedure. And I must find the
rebuildm.exe program.
Am I correct?
Thanks,
Tom
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Collations are a different levels, so there is not one place you will need
> to change it. Changing the Collation of 64bit SQL Server is no different to
> changing it on 32bit SQL Server. If this is a fresh install and you don't
> have any databases to change then the best option is probably to re-install.
> You can use rebuildm.exe to change the master database with a different
> collation. Each database can have the collation changed using the ALTER
> DATABASE command, this will not change the collation of existing columns that
> have been set to the original collation. To do that you will need to run a
> script that uses the ALTER TABLE command. If you the column are indexes or a
> primary key, or have Foreign Keys pointing to them, the constraint will need
> to be dropped. There are many threads on this for example
> http://tinyurl.com/ky3q
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/arch...
> Other links that may be useful:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/arch...
> John
> "T Morris" wrote:
|||Hi
I usually favour re-install rather than rebuildm.exe and on the 32 bit
version it is in the ...\80\tools\binn directory.
John
"T Morris" wrote:
[vbcol=seagreen]
> Since I didn't find Rebuildm.exe in the binn folder for the 64bit
> installation I thought there might be a different procedure.
> But per your email, I use the same procedure. And I must find the
> rebuildm.exe program.
> Am I correct?
> Thanks,
> Tom
> "John Bell" wrote:

Change SQL 2000 Server 64bit collation

Please point me to the procedure for changing the collation sequence on 64bi
t
installation of SQL Server 2000 (SP4). I can't find anything in KB or BOL.
Thanks,
TomHi
Collations are a different levels, so there is not one place you will need
to change it. Changing the Collation of 64bit SQL Server is no different to
changing it on 32bit SQL Server. If this is a fresh install and you don't
have any databases to change then the best option is probably to re-install.
You can use rebuildm.exe to change the master database with a different
collation. Each database can have the collation changed using the ALTER
DATABASE command, this will not change the collation of existing columns tha
t
have been set to the original collation. To do that you will need to run a
script that uses the ALTER TABLE command. If you the column are indexes or a
primary key, or have Foreign Keys pointing to them, the constraint will need
to be dropped. There are many threads on this for example
http://tinyurl.com/ky3q
http://msdn.microsoft.com/library/d...y/en-us/arch...
Other links that may be useful:
http://msdn.microsoft.com/library/d...y/en-us/arch...
John
"T Morris" wrote:

> Please point me to the procedure for changing the collation sequence on 64
bit
> installation of SQL Server 2000 (SP4). I can't find anything in KB or BOL.
> Thanks,
> Tom|||Since I didn't find Rebuildm.exe in the binn folder for the 64bit
installation I thought there might be a different procedure.
But per your email, I use the same procedure. And I must find the
rebuildm.exe program.
Am I correct?
Thanks,
Tom
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Collations are a different levels, so there is not one place you will need
> to change it. Changing the Collation of 64bit SQL Server is no different t
o
> changing it on 32bit SQL Server. If this is a fresh install and you don't
> have any databases to change then the best option is probably to re-instal
l.
> You can use rebuildm.exe to change the master database with a different
> collation. Each database can have the collation changed using the ALTER
> DATABASE command, this will not change the collation of existing columns t
hat
> have been set to the original collation. To do that you will need to run a
> script that uses the ALTER TABLE command. If you the column are indexes or
a
> primary key, or have Foreign Keys pointing to them, the constraint will ne
ed
> to be dropped. There are many threads on this for example
> http://tinyurl.com/ky3q
> http://msdn.microsoft.com/library/d...y/en-us/arch...
> Other links that may be useful:
> http://msdn.microsoft.com/library/d...y/en-us/arch...
> John
> "T Morris" wrote:
>|||Hi
I usually favour re-install rather than rebuildm.exe and on the 32 bit
version it is in the ...\80\tools\binn directory.
John
"T Morris" wrote:
[vbcol=seagreen]
> Since I didn't find Rebuildm.exe in the binn folder for the 64bit
> installation I thought there might be a different procedure.
> But per your email, I use the same procedure. And I must find the
> rebuildm.exe program.
> Am I correct?
> Thanks,
> Tom
> "John Bell" wrote:
>

Change sa password error

Hi all,
I have a customer who is trying to change their sa password
in MSDE 2000 using the sp_password procedure.
When they attempt this, they are returned the following error:
Msg 552, Level 16, State 1, Server abcdef\inst, Procedure
sp_password, Line 63
CryptoAPI function 'CryptAcquireContext' failed. Error
0x80090006: Invalid
Signature.
Can anyone provide any insight into this?
From my own research it seems like there might be a problem
with their crypto library, but I am unsure of how to fix this.
Thanks
Jay
That's not exact, but close to an issue I had trying to run MSDE 2000 on NT
4.0 server. If you run MSDE 2000 on NT 4.0 with >512 MB of RAM, then
CryptoAPI functions have problems. Check out KB321459 on Microsoft.
"Jay" <anonymous@.discussions.microsoft.com> wrote in message
news:2b3d01c4a796$3f625b40$a601280a@.phx.gbl...
> Hi all,
> I have a customer who is trying to change their sa password
> in MSDE 2000 using the sp_password procedure.
> When they attempt this, they are returned the following error:
> Msg 552, Level 16, State 1, Server abcdef\inst, Procedure
> sp_password, Line 63
> CryptoAPI function 'CryptAcquireContext' failed. Error
> 0x80090006: Invalid
> Signature.
> Can anyone provide any insight into this?
> From my own research it seems like there might be a problem
> with their crypto library, but I am unsure of how to fix this.
> Thanks
> Jay
|||Yes I had already seen that KB.
I forgot to add in my original post that this is on a Win2K
server (SP4) with all latest patches.

>--Original Message--
>That's not exact, but close to an issue I had trying to
run MSDE 2000 on NT
>4.0 server. If you run MSDE 2000 on NT 4.0 with >512 MB
of RAM, then
>CryptoAPI functions have problems. Check out KB321459 on
Microsoft.
|||For the benefit of those who encounter this problem later.
Microsoft advised me to re-install IE to repair the mismatched DLL's.
I am waiting on the client to do this so I can't vouch for it's success, but
thought I would post this as it may help others in the future.
Jay
"Jay" wrote:

> Hi all,
> I have a customer who is trying to change their sa password
> in MSDE 2000 using the sp_password procedure.
>

Change publisher server

I have transactional replication on SQL2000 with 60 subscribers.
For disaster recovery procedure i need to establish new replication within 15 minutes on another server with same subscribers.
Can i just confogure another DB as publisher and connect exist subscribers to new publisher ??

ThanX.Yes and no.

Before we begin, a cluster might be a better solution for this case. Either active-active or active-passive will allow you to avoid the down time and the hassle of the move. There is a trade off in terms of the complexity of the cluster, but that still seems like a better choice to me for your situation.

I'd suggest that you check KB #822400 (http://support.microsoft.com/default.aspx?scid=kb;en-us;822400) for more details on Disaster Recovery for SQL Server.

-PatP|||First of all thank you for answering.
Now i have cluster in my environment (Active / Passive ) .I am talking about real disaster - cluster have gone , so i need to move the publisher during minimal time to another location.
Thanx

Change publisher of replication

I have transactional replication on SQL2000 with 60 subscribers.
For disaster recovery procedure i need to establish new replication within 15 minutes on another server with same subscribers.
Can i just confogure another DB as publisher and connect exist subscribers to new publisher ?
ThanX.
This isn't quite so simple. How are you synchronizing with the standby publisher? Are you intending to use 'sync with backup' which ensures the publisher backup is never behind the distributor backup. Will you be restoring earlier copies of the subscriber
to ensure they are not ahead of the distributor? have a look in BOL at "Strategies for Backing Up and Restoring Transactional Replication".
HTH,
Paul Ibison

Change ownership of all objects owned by

HI, I thought I read somewhere about a Stored Procedure that can be used to
change all objects owned by x to owned by y. Does anyone know of such SP,
tell me about it, please. OR Tell me a better way of changing ownership of
ALL (DTS, Jobs, anything that has sa and the sa PW assigned to it) to
another SQL login I've created with SA privileges, Then I can change the PW
of sa. OR can this not be done. ? PLEASE ADVISE!!This free tool may be a start...
http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1116990,00.html?track=NL-464&ad=524311USCA
It uses a parameter of 'IF ^$^ <> ^dbo^ EXECUTE sp_changeobjectowner
^$.*^,^dbo^' to change the owner of selected objects.
"WANNABE" wrote:
> HI, I thought I read somewhere about a Stored Procedure that can be used to
> change all objects owned by x to owned by y. Does anyone know of such SP,
> tell me about it, please. OR Tell me a better way of changing ownership of
> ALL (DTS, Jobs, anything that has sa and the sa PW assigned to it) to
> another SQL login I've created with SA privileges, Then I can change the PW
> of sa. OR can this not be done. ? PLEASE ADVISE!!
>
>

Change ownership of all objects owned by

HI, I thought I read somewhere about a Stored Procedure that can be used to
change all objects owned by x to owned by y. Does anyone know of such SP,
tell me about it, please. OR Tell me a better way of changing ownership of
ALL (DTS, Jobs, anything that has sa and the sa PW assigned to it) to
another SQL login I've created with SA privileges, Then I can change the PW
of sa. OR can this not be done. ? PLEASE ADVISE!!This free tool may be a start...
http://searchsqlserver.techtarget.c...4&ad=524311USCA
It uses a parameter of 'IF ^$^ <> ^dbo^ EXECUTE sp_changeobjectowner
^$.*^,^dbo^' to change the owner of selected objects.
"WANNABE" wrote:

> HI, I thought I read somewhere about a Stored Procedure that can be used t
o
> change all objects owned by x to owned by y. Does anyone know of such SP,
> tell me about it, please. OR Tell me a better way of changing ownership
of
> ALL (DTS, Jobs, anything that has sa and the sa PW assigned to it) to
> another SQL login I've created with SA privileges, Then I can change the P
W
> of sa. OR can this not be done. ? PLEASE ADVISE!!
>
>

Change Owner Dts Package

Hi, i want to change owner to my dts package.
Can i do this problem to this procedure?
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
update sysdtspackages set owner = 'newname'
where owner ='oldname'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Because in this case i update all old name.
I also test sp_reassign_dtspackageowner but i don't know how this comand to do on the table?

Thanks

Don't bother is my advice, it is not permanent and largely cosmetic.

Package Ownership Issues
(http://www.sqldts.com/default.aspx?212)