SQLAgentMail: guys

Showing posts with label guys. Show all posts

Wednesday, March 28, 2012

Question regarding SP

Hi guys
I have written one stored procedure. At the end it returns the set of rows.
I want to define cursor on the result which is sent by stored procedure.
How can i do that ?
ThanksPatrick wrote:
> Hi guys
> I have written one stored procedure. At the end it returns the set of
> rows. I want to define cursor on the result which is sent by stored
> procedure. How can i do that ?
> Thanks
How do you mean? You want to use a cursor from the application? Or are
you saying you want to use the results from one stored procedure in
another stored procedure? Or are you saying you want to define a cursor
on the results from within the procedure so you can perform row-by-row
processing? Or are you saying something else altogether?
Please provide some details about what you are doing. Cursors on SQL
Server are very slow and there may be other set-based solutions you can
use.
David Gugick
Imceda Software
www.imceda.com|||Seeing as you are asking this in the DTS group, amongst others, you may want to look at
How to loop through a global variable Rowset
(http://www.sqldts.com/default.aspx?298)
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com
"Patrick" <kingofusa@.juno.com> wrote in message news:emOql5lAFHA.1396@.tk2msftngp13.phx.gbl...
> Hi guys
> I have written one stored procedure. At the end it returns the set of rows. I want to define cursor on the result which is sent by
> stored procedure.
> How can i do that ?
> Thanks
>|||I Want to use cursor with in the stored procedure itself.
My main problem is how can I catch the result returned by one procedure in
another procedure.
Marmik
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
> Patrick wrote:
>> Hi guys
>> I have written one stored procedure. At the end it returns the set of
>> rows. I want to define cursor on the result which is sent by stored
>> procedure. How can i do that ?
>> Thanks
> How do you mean? You want to use a cursor from the application? Or are you
> saying you want to use the results from one stored procedure in another
> stored procedure? Or are you saying you want to define a cursor on the
> results from within the procedure so you can perform row-by-row
> processing? Or are you saying something else altogether?
> Please provide some details about what you are doing. Cursors on SQL
> Server are very slow and there may be other set-based solutions you can
> use.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Patrick wrote:
> I Want to use cursor with in the stored procedure itself.
> My main problem is how can I catch the result returned by one
> procedure in another procedure.
> Marmik
>
Those are two different things. Do you want to use the cursor from
within the procedure? if so, see DECLARE CURSOR in the help file.
Do you want to catch the results of one procedure in another? You'll
have to use a temp table (or a real table) to store the results.
David Gugick
Imceda Software
www.imceda.com|||Here you go...
create table TestTable
( id int not null,
name varchar(50) not null)
go
Insert into TestTable values (1,'Test Name 1')
Insert into TestTable values (2,'Test Name 2')
Insert into TestTable values (3,'Test Name 3')
Insert into TestTable values (4,'Test Name 4')
go
Create Proc TestProc1
as
Select * from TestTable
go
Create Proc TestProc2
as
Declare @.tmpTable table
( id int not null,
name varchar(50) not null)
Insert into @.tmpTable Exec TestProc1
Select *,'Result From Proc2' from @.tmpTable
Run this example script ... I hope it anser your question.
-Sarav
"Patrick" <kingofusa@.juno.com> wrote in message
news:emOql5lAFHA.1396@.tk2msftngp13.phx.gbl...
> Hi guys
> I have written one stored procedure. At the end it returns the set of
rows.
> I want to define cursor on the result which is sent by stored procedure.
> How can i do that ?
> Thanks
>|||Hello,
What kind of result are you trying to get in your stored procedure that is
calling another stored procedure?
Are you looking for a scalar datatype or a record set? And if you're looking
for a record set how much data are you going to return (for example a
single, a few rows, or a lot of rows)?
If you're looking for a small recordset, I've had great success using User
Defined Functions to return TABLE Variables into my stored procedures. For a
lot more data I use temp tables or regular tables.
Chris
"Patrick" <kingofusa@.juno.com> wrote in message
news:u9gcCImAFHA.3492@.TK2MSFTNGP12.phx.gbl...
>I Want to use cursor with in the stored procedure itself.
> My main problem is how can I catch the result returned by one procedure in
> another procedure.
> Marmik
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
>> Patrick wrote:
>> Hi guys
>> I have written one stored procedure. At the end it returns the set of
>> rows. I want to define cursor on the result which is sent by stored
>> procedure. How can i do that ?
>> Thanks
>> How do you mean? You want to use a cursor from the application? Or are
>> you saying you want to use the results from one stored procedure in
>> another stored procedure? Or are you saying you want to define a cursor
>> on the results from within the procedure so you can perform row-by-row
>> processing? Or are you saying something else altogether?
>> Please provide some details about what you are doing. Cursors on SQL
>> Server are very slow and there may be other set-based solutions you can
>> use.
>>
>> --
>> David Gugick
>> Imceda Software
>> www.imceda.com
>|||You could store the result of a SP in a Temp Table and build a Cursor based
on that Temp Table.
Gopi
"Patrick" <kingofusa@.juno.com> wrote in message
news:u9gcCImAFHA.3492@.TK2MSFTNGP12.phx.gbl...
>I Want to use cursor with in the stored procedure itself.
> My main problem is how can I catch the result returned by one procedure in
> another procedure.
> Marmik
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
>> Patrick wrote:
>> Hi guys
>> I have written one stored procedure. At the end it returns the set of
>> rows. I want to define cursor on the result which is sent by stored
>> procedure. How can i do that ?
>> Thanks
>> How do you mean? You want to use a cursor from the application? Or are
>> you saying you want to use the results from one stored procedure in
>> another stored procedure? Or are you saying you want to define a cursor
>> on the results from within the procedure so you can perform row-by-row
>> processing? Or are you saying something else altogether?
>> Please provide some details about what you are doing. Cursors on SQL
>> Server are very slow and there may be other set-based solutions you can
>> use.
>>
>> --
>> David Gugick
>> Imceda Software
>> www.imceda.com
>

Monday, March 26, 2012

Question regarding Identitiy field

Guys,
I have a identity flag set on a column. When i am testing the system,
inserting records the identity goes all the way to 60 and so on. I need to
put the table in production system now. However i need to reset the identity
field to start from 1. How can i clear the identity field and make it start
from the begining.
Thanks
MannyUse this
DBCC CheckIdent('TableName')
"Manny Chohan" wrote:

> Guys,
> I have a identity flag set on a column. When i am testing the system,
> inserting records the identity goes all the way to 60 and so on. I need to
> put the table in production system now. However i need to reset the identi
ty
> field to start from 1. How can i clear the identity field and make it star
t
> from the begining.
> Thanks
> Manny|||I'm not sure why you care about the starting value but you can either
truncate the table or execute DBCC CHECKIDENT with the RESEED option.
Hope this helps.
Dan Guzman
SQL Server MVP
"Manny Chohan" <MannyChohan@.discussions.microsoft.com> wrote in message
news:6C4D0EBE-7AA5-4F18-9EAA-ED2547B1BD23@.microsoft.com...
> Guys,
> I have a identity flag set on a column. When i am testing the system,
> inserting records the identity goes all the way to 60 and so on. I need to
> put the table in production system now. However i need to reset the
> identity
> field to start from 1. How can i clear the identity field and make it
> start
> from the begining.
> Thanks
> Manny|||Dan,
Just fyi, dbcc checkident defaults to reseed... so,
checkident ('TableName')
is equivilent to
checkident ('TableName', RESEED)
"Dan Guzman" wrote:

> I'm not sure why you care about the starting value but you can either
> truncate the table or execute DBCC CHECKIDENT with the RESEED option.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Manny Chohan" <MannyChohan@.discussions.microsoft.com> wrote in message
> news:6C4D0EBE-7AA5-4F18-9EAA-ED2547B1BD23@.microsoft.com...
>
>|||Thanks All
"CBretana" wrote:
> Dan,
> Just fyi, dbcc checkident defaults to reseed... so,
> checkident ('TableName')
> is equivilent to
> checkident ('TableName', RESEED)
> "Dan Guzman" wrote:
>|||I am with Dan all the way on this one. Why do you care about the value of
the identity? I admit that I would probably want to reset it myself when
going into production, just because it looks more "tidy." It is however,
always concerning when someone states that they want to know how to do this
because it often means they are using these values in some manner where the
user will care about the value, and identities are pretty bad for this sort
of thing (users HATE gaps, and gaps are just generally part of the identity
experience, since row with the identity property cannot be updated (ever).)
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Manny Chohan" <MannyChohan@.discussions.microsoft.com> wrote in message
news:6C4D0EBE-7AA5-4F18-9EAA-ED2547B1BD23@.microsoft.com...
> Guys,
> I have a identity flag set on a column. When i am testing the system,
> inserting records the identity goes all the way to 60 and so on. I need to
> put the table in production system now. However i need to reset the
> identity
> field to start from 1. How can i clear the identity field and make it
> start
> from the begining.
> Thanks
> Manny|||In theory, I agree 100% with you and Dan on this... I do not believe the
value of any surrogate key, much less an Identoty, should be significant to
anyone...
However, in practice, simply out ofa sense of esthetics, I find myself doing
exactly the same thing this gentleman asked about...
In theory, if we really didn't care about the actual values, we wouldn't
always set the seed value for Identity columns = 1, we'd set it to the lowes
t
legal value for the underlying datatype, (TinyInt, SmallInt, Int).
(Either zero, (0), -32,768, or -2,147,483,648, respectively)
But practically, we do care what the value is, because in coding, and
debugging, and manipulating the data in Query Analyzer, we use these values,
and so they do matter.
"Louis Davidson" wrote:

> I am with Dan all the way on this one. Why do you care about the value of
> the identity? I admit that I would probably want to reset it myself when
> going into production, just because it looks more "tidy." It is however,
> always concerning when someone states that they want to know how to do thi
s
> because it often means they are using these values in some manner where th
e
> user will care about the value, and identities are pretty bad for this sor
t
> of thing (users HATE gaps, and gaps are just generally part of the identit
y
> experience, since row with the identity property cannot be updated (ever).
)
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "Manny Chohan" <MannyChohan@.discussions.microsoft.com> wrote in message
> news:6C4D0EBE-7AA5-4F18-9EAA-ED2547B1BD23@.microsoft.com...
>
>|||That was pretty much what I meant too. It is really a weird thing too.
Sure when we start out programming it is easier to have the value start out
at 1, since it is easier to type while doing initial programming, but by all
logic if we really mean that we don't care about the value then starting
at -minvalue would be better (if we are lucky our values will get that big
anyhow!)
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:7EF6C6ED-EE40-4AA2-8173-8A99226E0CEA@.microsoft.com...
> In theory, I agree 100% with you and Dan on this... I do not believe the
> value of any surrogate key, much less an Identoty, should be significant
> to
> anyone...
> However, in practice, simply out ofa sense of esthetics, I find myself
> doing
> exactly the same thing this gentleman asked about...
> In theory, if we really didn't care about the actual values, we wouldn't
> always set the seed value for Identity columns = 1, we'd set it to the
> lowest
> legal value for the underlying datatype, (TinyInt, SmallInt, Int).
> (Either zero, (0), -32,768, or -2,147,483,648, respectively)
> But practically, we do care what the value is, because in coding, and
> debugging, and manipulating the data in Query Analyzer, we use these
> values,
> and so they do matter.
>
> "Louis Davidson" wrote:
>|||Manny,
you have two options:
1. use DBCC CHECKIDENT statement with RESEED option (see Books OnLine),
2. use TRUNCATE TABLE on your table (however THIS WILL DELETE ALL ROWS IN A
TABLE WITHOUT ROLLBACK OPTION!)
Pawel
"Manny Chohan" wrote:

> Guys,
> I have a identity flag set on a column. When i am testing the system,
> inserting records the identity goes all the way to 60 and so on. I need to
> put the table in production system now. However i need to reset the identi
ty
> field to start from 1. How can i clear the identity field and make it star
t
> from the begining.
> Thanks
> Manny|||> 2. use TRUNCATE TABLE on your table (however THIS WILL DELETE ALL ROWS IN
> A
> TABLE WITHOUT ROLLBACK OPTION!)
TRUNCATE does allow a ROLLBACK. However, like other SQL data modification
statements, the transaction will need to be started explicitly unless
IMPLICIT_TRANSACTIONS is on.
CREATE TABLE MyTable(Col1 int NOT NULL)
INSERT INTO MyTable VALUES(1)
BEGIN TRAN
TRUNCATE TABLE MyTable
SELECT Col1 FROM MyTable
ROLLBACK
SELECT Col1 FROM MyTable
Hope this helps.
Dan Guzman
SQL Server MVP
"Pawel Potasinski" <PawePotasiski@.discussions.microsoft.com> wrote in
message news:11BF931F-D7BC-41A3-B0FD-E2F85943B14B@.microsoft.com...
> Manny,
> you have two options:
> 1. use DBCC CHECKIDENT statement with RESEED option (see Books OnLine),
> 2. use TRUNCATE TABLE on your table (however THIS WILL DELETE ALL ROWS IN
> A
> TABLE WITHOUT ROLLBACK OPTION!)
> Pawel
> "Manny Chohan" wrote:
>

Question on validation of mining models

Hi, guys,

Thanks for your kind attention.

Just want to make things perfectly work and make the most of our fantastic SQL Server 2005 Data Mining Engine. Can any of you here give me some super advices on the validation of the mining models. As we always see, the 3 aspects of a mining model are: Score, Population correct, and Predict Probability. So the question is: How can we combine these three aspects to best judge the mining models by being able to tell which model is the best one? And to what extent can we really trust these mining models?

These are very important before we can actually bring the models into work to convince other people who have no ideas what are going on with these models. Yes, we just want to convince them with the results of these models and make the most of them and best help them getting the most from their business operations etc.

By the way please can you explain a bit details on each of these aspects? Thanks again.

I am looking forward to hearing from you shortly and thanks bunch for your help.

With best regards,

Yours sincerely,

Hi, Guys,

I am still waiting for your advices.

Thanks.

With best regards,

Yours sincerely,

|||

I heard about a book "Quality Measures in Data Mining" by Fabrice Guillet and Howard J. Hamilton. It may contain some super advices.

Gigi Ciubuc

www.sqlserver.ro

|||

Hi, Gigi Ciubuc,

Thanks for the imformation. Any other ideas from any other experts here as well?

Thanks.

With best regards,

Yours sincerely,

Monday, March 12, 2012

Question on Naive Bayes Viewer

Hi, guys,

I encountered a very weird question on Naive Bayes viewer, that is : one of the attributes does not appeared in Naive Bayes viewer? The original attribute data type is int data type, but then within the mining structure, I change it to discrete with Text as its data type. But the problem is after I trained the model, on the naive bayes viewer, that attribute does not appear at all? Why is that?

I have set the dependency value to be very low to enable all attributes to appear. But only that attribute got the problem?

I am looking forward to hearing from you shortly and thanks a lot in advance.

With best regards,

Yours sincerely

When you change a column's content type or data type (e.g from Integer to Text), BI Developer Studio might mark the column as Ignorable in the mining models that use it.

Could this be the issue you are seeing?

|||

No, the column is still labelled as input.

|||Was there a specific reason you needed to change the data type to Text? As far as the model is concerned, there's no difference between text and int for discrete attributes.

|||

Hi, Raman,

Thank you very much.

I think I know what you mean now, set the content type to be discrete, but the data type remains as Int? As when I left the data type as Int and content type as Continuous, the model treated all the data as continous. Is that right? Thank you.

With best regards,

Yours sincerely,

|||Correct - if you want ints to be treated as discrete, you just need to make sure that the content type is set correctly (to Discrete or Discretized).

|||

Hi, Raman,

Thanks.

With best regards,

Yours sincerely,

Question on Naive Bayes Viewer

Hi, guys,

I have set the dependency value to be very low to enable all attributes to appear. But only that attribute got the problem?

I am looking forward to hearing from you shortly and thanks a lot in advance.

With best regards,

Yours sincerely

When you change a column's content type or data type (e.g from Integer to Text), BI Developer Studio might mark the column as Ignorable in the mining models that use it.

Could this be the issue you are seeing?

|||

No, the column is still labelled as input.

|||Was there a specific reason you needed to change the data type to Text? As far as the model is concerned, there's no difference between text and int for discrete attributes.

|||

Hi, Raman,

Thank you very much.

With best regards,

Yours sincerely,

|||Correct - if you want ints to be treated as discrete, you just need to make sure that the content type is set correctly (to Discrete or Discretized).

|||

Hi, Raman,

Thanks.

With best regards,

Yours sincerely,

Friday, March 9, 2012

Question on Links coefficiency of dependency network

Hi, guys,

Thanks for your kind attention.

Just wonder is there any way to get the link value on dependency network? Yes, I know we can move along the slider to get the link strength between different attributes. However, it will be more convinced that if we can get the value of the links and say we have a standard for it to judge if the link is truly strong enough?

Thanks in advance for your advices.

And I am very much looking forward to hearing from you.

With best regards,

Yours sincerely,

You could use the Profiler to see the calls executed against the server when the dep net viewer is populated.

If you execute the stored procedure from, say, SSMS, you will get back the whole serialized graph, with the weight associated with each edge

|||Data Mining Add-in for Visio can show edge strength (there is a button for it in the Data Mining toolbar).|||

Hi, thanks for the advices.

Best regards,

Yours sincerely,

Question on how to display data in this unique situation.....

Hi guys,

Man I do come up with strange scenarios, but that is the joy of working in software field right ? ;-)
First off, thanks to anyone taking their time to read this, and Ihope this post paints a clearer picture better than my previous posts.
I have an old stored procedure (which I didn't create) that produces a dataset of the following:-

((All names and values had been changed to protect confidentiality))

region agent_type mailpackage1 mailpackage2 mailpackage3
New York Agenttype1 2000 2300 0
New York Agenttype2 0 0 5
New York Agenttype3 150 2 4000
Central Agenttype2 1234 5678 9
Central Agenttype4 435 1 0
MidWest Agenttype1 555 0 0
West Agenttype1 1 45 0
West Agenttype2 0 2 3

A little bit of explanation:-
Each region can have any type of agents, specified by the number to distinguish different agent types. these agent types mail specific packages to their customers depending on the situation and what the customers asked for. the numbers in each mail package indicate the total that had been sent out by a particular type of an agent. So in this case we are not dealing wtih how many agents are there, just how many packages had been sent out by a specific type of an agent in a region.

Previously the report was produced like you would see in the above dataset. However the client would want it the other way around. Though I didn't show it here, there are plenty of other packages but I am picking three for clarity sake.

So the "new" Report would have to look something like this.

Region: New York
AgentType1 AgentType2 AgentType3 AgentType4
Package1 2000 0 150 0
Package2 2300 0 2 0
Package3 0 5 4000 0

break page

Region: Central
AgentType1 AgentType2 AgentType3 AgentType4
Package1 0 1234 0 435
Package2 0 5678 0 1
Package3 0 9 0 0

- break Page -- and so on

I had created a table in the RS that looked like the above with expressions written into the each cell that holds a value. The expression is

=IIF(Fields!agent_type = "AgentType1", mailpackage1.Value, Cint(0)) in the first row, first column of the table.
=IIF(Fields!agent_type = "AgentType1", mailpackage2.Value, Cint(0)) in the second row, first column of the table.
=IIF(Fields!agent_type = "AgentType1", mailpackage3.Value, Cint(0)) in the third row, First column of the table.
And so on....... alternating between agent_type and mailpackage for each cell.

Grouping1: Group by Region, insert page after each group.

What happened was the following:- ((I am putting the first region, because it is also happening for the other regions too)

Region: New York
AgentType1 AgentType2 AgentType3 AgentType4
Package1 2000 0 0 0
Package2 2300 0 0 0
Package3 0 0 0 0

break page
Region: New York
AgentType1 AgentType2 AgentType3 AgentType4
Package1 0 0 0 0
Package2 0 0 0 0
Package3 0 5 0 0

break page

Region: New York
AgentType1 AgentType2 AgentType3 AgentType4
Package1 0 0 150 0
Package2 0 0 2 0
Package3 0 0 4000 0

break page
(on a side note, this region didn't print out AgentType4 because there were no data associated with it)

The question is, is there anything else I could have done to prevent this ? as you can see, the data is correct and placed in their right cells but somehow, they won't join together. I got a feelin that it has something to do with the expression that I had put in each cell.

Can someone help or point me in the right direction ? This is really bothering me and I couldn't figure out why it was doing this. Couldnt find any links or maybe i am putting in the wrong keywords in the search. Thanks muchly !

Bernard Ong

May it be that yor "new" Report is a changed copy of the old one and you didn't remove the grouping by AgentType?|||

Hi there Markus.

Thanks for the reply.

Actually, The old report was html generated. I am basically creating the new report with the new requirements/design using SSRS 2000 now.

At first I thought ti was the agent type too. But when I checked my groupings again, it only has the region grouping.

I even recreated the whole table, start fresh again and made certain that it is only grouped by Region. Same problem...

Thanks for pointing out the agent type grouping though!

Bernard Ong

|||Well it certainly looks more like a Matrix than a Table layout. If you switch from the table to a matrix, you do not need all the expressions at all. If you do not want to change your report design, try to do it in the query instead then by joining a sub-query for each agent. HTH|||

Thanks for the reply BYU

I am looking up on how to do a matrix, but it seems that I still need a recursive or repetitive row, in this case from what I had seen in the dataset, would be the agent-type. Unless there was another way to do it ?

However, due to the many packages that an agent could mail out, (even in landscape) it would be way too long for the printer or even the preview to look.

Thanks for taking a look into this. I appreciate any more tips or hints with this scenario.

Sincerely,

Bernard Ong

|||

Hello all again,

I am trying to figure out how to use the matrix (never really use them before, always with tables) and I am a little frustrated on how to get it to work or even trying to understand it.
Any references or help which I can refer to, to get me going ? my so called "Matrix" looks like some daVinci code that needs to be broken so that it could be interpreted ! :)

Thanks !

Bernard Ong

|||

Hi,

Could you please check if your initial SQL query has a 'order by region,agenttype' in it ?

If you clients are not really concerned about the excel export of this report - you can consider having a table with a grouping - 'region' and a matrix inside the group displaying data for each region repetitively.

so say table 1

create a group1 header -based on 'region'

add another header row for this group

In this second header row insert a matrix

row field- Agent type

column field- mailpackage types

let me know if you need more explanation. however, this report cannot be exported to excel since export of nested data regions is not supported.

Thanks,

|||

Hi PB

Thanks for the reply !
I am going to try your suggestion and see whether that works. I never thought of using a matrix inside a table, and that might just work.
To answer your question:- Yes the query has a group by region AND agent_Type.

Thanks to the post by BYU, I am currently trying to wrestle with Matrix ("the blue or red pill" :) ) and it is slowly doing what I "think" it is doing.
It is just confusing because the matrix controls work VERY differently with the table. I guess I have to "Add Row" in the cell instead of highlighting the whole row.

I am not sure about this, but with the way I am doing it right now, sounds similar to what you saying except without the header. is that right ?

Sincerely,
Bernard Ong

|||

Hi,

I was talking more about an ORDER BY clause after the Group BY.

select region,etc,cetc

GROUP BY region, agent type,etc

ORDER BY Region,Agent Type.

That probably will make your original table layout also work.

Good luck,

|||

Sorry ORDER BY REgion , Package , Agent Type

|||

Hi PB,

Sorry, must have read the post wrongly. and yes, i do have an order by clause, but with the package in there, it won't work.

The way it is as the dataset is shown, package1, package2 and so on, are the names of the packages(mailpackages column), and the data associated with them are counts or totals.

Only region and agent_type are string characters, so using the package name in the order by won't work.

Thanks though !

Bernard Ong

|||

Hi Bernard,

I'm still a little unsure of your problem ....could you please explain how the data is stored...I mean how do you get the mailpackage1,mailpackage2 in the stored procedure ....is this a column indicating the type/category of package ?

In that case even if it is a number field , you should order by that before agent type

say you have a table with following columns

region id, mail_package_count, agent_type, packageNo.

newyork, 200, 1 45

new york 500 2 45

new york 30 3 500

This indicates that 200 counts of package45 were delivered by agent 1 , 500 counts of package45 were delivered by agent2 .....

IS that how it is?

I think you may have to make a small change in your stored procedure itself...returning the dataset ordered by region and packaget instead of agent type.

Please ignore this post if I got your problem wrong and Sorry if I confused you even more...

Thanks,

|||

Hi Pbala,

Yeah ! that is exactly how it is without the packageNo. I am sorry that it wasn't clear enough, but in my situation, the numbers represent the count of the packages delivered. I still need the agent type because the client wants to see how many packages were sent out by a particular agent type.

Such as an Elite Agent type, or a basic agent, and they would make a decision out of it. To quote, this is how it is going to look like.

region id, mail_package_1_count, mail_package_2_count, mail_package_3_count, agent_type

newyork, 200 123 456 1

new york 500 342 688 2

new york 30 0 0 3

Hope this makes more sense now.

Still working on the problem but I am getting somewhere though. Right now, with using the matrix, i am able to get the right totals and how it would look like on the report with a "specific" region.

however, when I tried to have the all regions, it just clumped everything into one matrix without showing the other regions. this meaning that i got the total for ALL regions in one page, instead of them breaking out in different pages with each region.

Is there an expression or something am I missing to do ?

Thanks.
Bernard Ong

|||

Hello all once again :)

Just an update on the situation here.
First off, want to thank Pbala, BYU, and Mark for helping out with this thread.
After much reading and trying to figure out the matrix, I got the report working.
I was confused with how to use the Matrix which in the end did more harm than good and finally got the hang of it.
It was all about the groupings and how the Matrix does not act like a table. I resolve this issue by doing the following groupings.

For the Row Groupings:
I only needed to use ONE row Grouping and I ended up putting alot of row groupings thinking that they acted like rows from the table.
Boy, was I dead wrong. I found out that you have to select a cell (Row Section specifically) in the matrix to add a row in the column that would extend to the end.
BY clicking on the row header, it only allows you to add a row grouping and not a row. Either way. I grouped only the region in the
Matrix Properties->Groupings. NExt I keep adding the amount of rows needed to represent a package and drag their fields into the Data section.

For the Column Groupings:
Essentially the same thing, BUT I put the field!agent_type in the column section. Next I entered the the agent_type grouping in the column
grouping of the matrix.

The trouble i was having was not understanding the difference between the Row Groupings and Rows. I keep adding the row groupings which ended up not printing
What i needed for the report. After realizing that problem, I did a bit more research, and figured out that only the cell of the matrix allows Rows.

After that, I put the Subtotal field and it works ! My report printed exactly like the scenario.

Couldn't have done this without this forum and without the ideas and suggestions presented by the posters of this thread ! Thanks alot !

However, I do have another question concerning about the matrix, Is it possible to have an actual total of everything at the bottom of the screen like how the Table Footer would have ? Or the Matrix is only limited to having a subtotal ? The reason why I ask this because I have a option called "All Regions" which should give me the totals of all the regions including each individual region printed out on every page. I achieved this by having a table at the bottom of the matrix to collect the totals. I made the table into a table footer so that it encapsulates everything, and using the Visibility feature, Turn it off if the user only selected a specific region, but turn it on if the user selected "All region". was wondering whether the matrix could also achieve this but in an easier way like how the table would ?

thanks !

Bernard Ong

Question on how to display data in this unique situation.....

Hi guys,

((All names and values had been changed to protect confidentiality))

So the "new" Report would have to look something like this.

Region: New York
AgentType1 AgentType2 AgentType3 AgentType4
Package1 2000 0 150 0
Package2 2300 0 2 0
Package3 0 5 4000 0

break page

Region: Central
AgentType1 AgentType2 AgentType3 AgentType4
Package1 0 1234 0 435
Package2 0 5678 0 1
Package3 0 9 0 0

- break Page -- and so on

I had created a table in the RS that looked like the above with expressions written into the each cell that holds a value. The expression is

Grouping1: Group by Region, insert page after each group.

What happened was the following:- ((I am putting the first region, because it is also happening for the other regions too)

Region: New York
AgentType1 AgentType2 AgentType3 AgentType4
Package1 2000 0 0 0
Package2 2300 0 0 0
Package3 0 0 0 0

break page
Region: New York
AgentType1 AgentType2 AgentType3 AgentType4
Package1 0 0 0 0
Package2 0 0 0 0
Package3 0 5 0 0

break page

Region: New York
AgentType1 AgentType2 AgentType3 AgentType4
Package1 0 0 150 0
Package2 0 0 2 0
Package3 0 0 4000 0

break page
(on a side note, this region didn't print out AgentType4 because there were no data associated with it)

Bernard Ong

May it be that yor "new" Report is a changed copy of the old one and you didn't remove the grouping by AgentType?|||

Hi there Markus.

Thanks for the reply.

Actually, The old report was html generated. I am basically creating the new report with the new requirements/design using SSRS 2000 now.

At first I thought ti was the agent type too. But when I checked my groupings again, it only has the region grouping.

I even recreated the whole table, start fresh again and made certain that it is only grouped by Region. Same problem...

Thanks for pointing out the agent type grouping though!

Bernard Ong

Thanks for the reply BYU

However, due to the many packages that an agent could mail out, (even in landscape) it would be way too long for the printer or even the preview to look.

Thanks for taking a look into this. I appreciate any more tips or hints with this scenario.

Sincerely,

Bernard Ong

|||

Hello all again,

Thanks !

Bernard Ong

|||

Hi,

Could you please check if your initial SQL query has a 'order by region,agenttype' in it ?

so say table 1

create a group1 header -based on 'region'

add another header row for this group

In this second header row insert a matrix

row field- Agent type

column field- mailpackage types

let me know if you need more explanation. however, this report cannot be exported to excel since export of nested data regions is not supported.

Thanks,

|||

Hi PB

I am not sure about this, but with the way I am doing it right now, sounds similar to what you saying except without the header. is that right ?

Sincerely,
Bernard Ong

|||

Hi,

I was talking more about an ORDER BY clause after the Group BY.

select region,etc,cetc

GROUP BY region, agent type,etc

ORDER BY Region,Agent Type.

That probably will make your original table layout also work.

Good luck,

|||

Sorry ORDER BY REgion , Package , Agent Type

|||

Hi PB,

Sorry, must have read the post wrongly. and yes, i do have an order by clause, but with the package in there, it won't work.

The way it is as the dataset is shown, package1, package2 and so on, are the names of the packages(mailpackages column), and the data associated with them are counts or totals.

Only region and agent_type are string characters, so using the package name in the order by won't work.

Thanks though !

Bernard Ong

|||

Hi Bernard,

In that case even if it is a number field , you should order by that before agent type

say you have a table with following columns

region id, mail_package_count, agent_type, packageNo.

newyork, 200, 1 45

new york 500 2 45

new york 30 3 500

This indicates that 200 counts of package45 were delivered by agent 1 , 500 counts of package45 were delivered by agent2 .....

IS that how it is?

I think you may have to make a small change in your stored procedure itself...returning the dataset ordered by region and packaget instead of agent type.

Please ignore this post if I got your problem wrong and Sorry if I confused you even more...

Thanks,

|||

Hi Pbala,

Such as an Elite Agent type, or a basic agent, and they would make a decision out of it. To quote, this is how it is going to look like.

region id, mail_package_1_count, mail_package_2_count, mail_package_3_count, agent_type

newyork, 200 123 456 1

new york 500 342 688 2

new york 30 0 0 3

Hope this makes more sense now.

Still working on the problem but I am getting somewhere though. Right now, with using the matrix, i am able to get the right totals and how it would look like on the report with a "specific" region.

Is there an expression or something am I missing to do ?

Thanks.
Bernard Ong

|||

Hello all once again :)

For the Column Groupings:
Essentially the same thing, BUT I put the field!agent_type in the column section. Next I entered the the agent_type grouping in the column
grouping of the matrix.

After that, I put the Subtotal field and it works ! My report printed exactly like the scenario.

Couldn't have done this without this forum and without the ideas and suggestions presented by the posters of this thread ! Thanks alot !

thanks !

Bernard Ong

Question on how to display data in this unique situation using Matrix.....

Hi guys,

((All names and values had been changed to protect confidentiality))

So the "new" Report would have to look something like this.

Region: New York
AgentType1 AgentType2 AgentType3 AgentType4
Package1 2000 0 150 0
Package2 2300 0 2 0
Package3 0 5 4000 0

break page

Region: Central
AgentType1 AgentType2 AgentType3 AgentType4
Package1 0 1234 0 435
Package2 0 5678 0 1
Package3 0 9 0 0

- break Page -- and so on

I had created a table in the RS that looked like the above with expressions written into the each cell that holds a value. The expression is

Grouping1: Group by Region, insert page after each group.

What happened was the following:- ((I am putting the first region, because it is also happening for the other regions too)

Region: New York
AgentType1 AgentType2 AgentType3 AgentType4
Package1 2000 0 0 0
Package2 2300 0 0 0
Package3 0 0 0 0

break page
Region: New York
AgentType1 AgentType2 AgentType3 AgentType4
Package1 0 0 0 0
Package2 0 0 0 0
Package3 0 5 0 0

break page

Region: New York
AgentType1 AgentType2 AgentType3 AgentType4
Package1 0 0 150 0
Package2 0 0 2 0
Package3 0 0 4000 0

break page
(on a side note, this region didn't print out AgentType4 because there were no data associated with it)

Bernard Ong

May it be that yor "new" Report is a changed copy of the old one and you didn't remove the grouping by AgentType?|||

Hi there Markus.

Thanks for the reply.

Actually, The old report was html generated. I am basically creating the new report with the new requirements/design using SSRS 2000 now.

At first I thought ti was the agent type too. But when I checked my groupings again, it only has the region grouping.

I even recreated the whole table, start fresh again and made certain that it is only grouped by Region. Same problem...

Thanks for pointing out the agent type grouping though!

Bernard Ong

Thanks for the reply BYU

However, due to the many packages that an agent could mail out, (even in landscape) it would be way too long for the printer or even the preview to look.

Thanks for taking a look into this. I appreciate any more tips or hints with this scenario.

Sincerely,

Bernard Ong

|||

Hello all again,

Thanks !

Bernard Ong

|||

Hi,

Could you please check if your initial SQL query has a 'order by region,agenttype' in it ?

so say table 1

create a group1 header -based on 'region'

add another header row for this group

In this second header row insert a matrix

row field- Agent type

column field- mailpackage types

let me know if you need more explanation. however, this report cannot be exported to excel since export of nested data regions is not supported.

Thanks,

|||

Hi PB

I am not sure about this, but with the way I am doing it right now, sounds similar to what you saying except without the header. is that right ?

Sincerely,
Bernard Ong

|||

Hi,

I was talking more about an ORDER BY clause after the Group BY.

select region,etc,cetc

GROUP BY region, agent type,etc

ORDER BY Region,Agent Type.

That probably will make your original table layout also work.

Good luck,

|||

Sorry ORDER BY REgion , Package , Agent Type

|||

Hi PB,

Sorry, must have read the post wrongly. and yes, i do have an order by clause, but with the package in there, it won't work.

The way it is as the dataset is shown, package1, package2 and so on, are the names of the packages(mailpackages column), and the data associated with them are counts or totals.

Only region and agent_type are string characters, so using the package name in the order by won't work.

Thanks though !

Bernard Ong

|||

Hi Bernard,

In that case even if it is a number field , you should order by that before agent type

say you have a table with following columns

region id, mail_package_count, agent_type, packageNo.

newyork, 200, 1 45

new york 500 2 45

new york 30 3 500

This indicates that 200 counts of package45 were delivered by agent 1 , 500 counts of package45 were delivered by agent2 .....

IS that how it is?

I think you may have to make a small change in your stored procedure itself...returning the dataset ordered by region and packaget instead of agent type.

Please ignore this post if I got your problem wrong and Sorry if I confused you even more...

Thanks,

|||

Hi Pbala,

Such as an Elite Agent type, or a basic agent, and they would make a decision out of it. To quote, this is how it is going to look like.

region id, mail_package_1_count, mail_package_2_count, mail_package_3_count, agent_type

newyork, 200 123 456 1

new york 500 342 688 2

new york 30 0 0 3

Hope this makes more sense now.

Still working on the problem but I am getting somewhere though. Right now, with using the matrix, i am able to get the right totals and how it would look like on the report with a "specific" region.

Is there an expression or something am I missing to do ?

Thanks.
Bernard Ong

|||

Hello all once again :)

For the Column Groupings:
Essentially the same thing, BUT I put the field!agent_type in the column section. Next I entered the the agent_type grouping in the column
grouping of the matrix.

After that, I put the Subtotal field and it works ! My report printed exactly like the scenario.

Couldn't have done this without this forum and without the ideas and suggestions presented by the posters of this thread ! Thanks alot !

thanks !

Bernard Ong

Saturday, February 25, 2012

Question on changing data types on a table

Hi Guys,
I have always wondered what SQL Server actually does when I try to change
the data type on any column in a table. I know that SQL Server will rebuild
the whole table when its changing the data type, so if I had multiple columns
that I wanted to change data types for, this is the only way I have been able
to do it using TSQL:
ALTER TABLE TEST
ALTER COLUMN A (char(4))
ALTER TABLE TEST
ALTER COLUMN B (INT)
ALTER TABLE TEST
ALTER COLUMN C (FLOAT)
Does the above mean that SQL Server has to rebuild my table 3 different
times? How could I rewrite this code so that SQL Server can convert all my
columns with one rebuild? Thanks guys!well! u dont have that flexibility :(
u can add multiple columns using ALTER
--
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Shishir Viriyala" wrote:
> Hi Guys,
> I have always wondered what SQL Server actually does when I try to change
> the data type on any column in a table. I know that SQL Server will rebuild
> the whole table when its changing the data type, so if I had multiple columns
> that I wanted to change data types for, this is the only way I have been able
> to do it using TSQL:
> ALTER TABLE TEST
> ALTER COLUMN A (char(4))
> ALTER TABLE TEST
> ALTER COLUMN B (INT)
> ALTER TABLE TEST
> ALTER COLUMN C (FLOAT)
> Does the above mean that SQL Server has to rebuild my table 3 different
> times? How could I rewrite this code so that SQL Server can convert all my
> columns with one rebuild? Thanks guys!
>|||Okay, how about doing it in Enterprise Manager's Design Table section? I can
see all the columns with their data types at once... if I changed multiple
data types there, does SQL Server rebuild the table for each column, or does
do everything in one shot? Appreciate the help!
"Chandra" wrote:
> well! u dont have that flexibility :(
> u can add multiple columns using ALTER
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Shishir Viriyala" wrote:
> > Hi Guys,
> >
> > I have always wondered what SQL Server actually does when I try to change
> > the data type on any column in a table. I know that SQL Server will rebuild
> > the whole table when its changing the data type, so if I had multiple columns
> > that I wanted to change data types for, this is the only way I have been able
> > to do it using TSQL:
> >
> > ALTER TABLE TEST
> > ALTER COLUMN A (char(4))
> >
> > ALTER TABLE TEST
> > ALTER COLUMN B (INT)
> >
> > ALTER TABLE TEST
> > ALTER COLUMN C (FLOAT)
> >
> > Does the above mean that SQL Server has to rebuild my table 3 different
> > times? How could I rewrite this code so that SQL Server can convert all my
> > columns with one rebuild? Thanks guys!
> >|||it rebuilds for each column.
--
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Shishir Viriyala" wrote:
> Okay, how about doing it in Enterprise Manager's Design Table section? I can
> see all the columns with their data types at once... if I changed multiple
> data types there, does SQL Server rebuild the table for each column, or does
> do everything in one shot? Appreciate the help!
> "Chandra" wrote:
> > well! u dont have that flexibility :(
> > u can add multiple columns using ALTER
> >
> > --
> > best Regards,
> > Chandra
> > http://chanduas.blogspot.com/
> > http://groups.msn.com/SQLResource/
> > ---
> >
> >
> >
> > "Shishir Viriyala" wrote:
> >
> > > Hi Guys,
> > >
> > > I have always wondered what SQL Server actually does when I try to change
> > > the data type on any column in a table. I know that SQL Server will rebuild
> > > the whole table when its changing the data type, so if I had multiple columns
> > > that I wanted to change data types for, this is the only way I have been able
> > > to do it using TSQL:
> > >
> > > ALTER TABLE TEST
> > > ALTER COLUMN A (char(4))
> > >
> > > ALTER TABLE TEST
> > > ALTER COLUMN B (INT)
> > >
> > > ALTER TABLE TEST
> > > ALTER COLUMN C (FLOAT)
> > >
> > > Does the above mean that SQL Server has to rebuild my table 3 different
> > > times? How could I rewrite this code so that SQL Server can convert all my
> > > columns with one rebuild? Thanks guys!
> > >|||Check and see :-). Press the "save change script" button and you will see the SQL that EM is about
to execute.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Shishir Viriyala" <ShishirViriyala@.discussions.microsoft.com> wrote in message
news:F183A811-5BBF-4BA0-9E6D-BE671FDB8837@.microsoft.com...
> Okay, how about doing it in Enterprise Manager's Design Table section? I can
> see all the columns with their data types at once... if I changed multiple
> data types there, does SQL Server rebuild the table for each column, or does
> do everything in one shot? Appreciate the help!
> "Chandra" wrote:
>> well! u dont have that flexibility :(
>> u can add multiple columns using ALTER
>> --
>> best Regards,
>> Chandra
>> http://chanduas.blogspot.com/
>> http://groups.msn.com/SQLResource/
>> ---
>>
>> "Shishir Viriyala" wrote:
>> > Hi Guys,
>> >
>> > I have always wondered what SQL Server actually does when I try to change
>> > the data type on any column in a table. I know that SQL Server will rebuild
>> > the whole table when its changing the data type, so if I had multiple columns
>> > that I wanted to change data types for, this is the only way I have been able
>> > to do it using TSQL:
>> >
>> > ALTER TABLE TEST
>> > ALTER COLUMN A (char(4))
>> >
>> > ALTER TABLE TEST
>> > ALTER COLUMN B (INT)
>> >
>> > ALTER TABLE TEST
>> > ALTER COLUMN C (FLOAT)
>> >
>> > Does the above mean that SQL Server has to rebuild my table 3 different
>> > times? How could I rewrite this code so that SQL Server can convert all my
>> > columns with one rebuild? Thanks guys!
>> >|||Aha! Thanks for the tip Tibor! I have included what the change script was
showing me. The two columns for which I changed data types were CASEID and
DX. Note that both those columns are being converted in one statement.
Using EP is definitely the way to go!
Thanks to you and Chandra for the input......
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_ip_adm_dx
(
CASEID int NULL,
DX varchar(5) NULL,
DXNO int NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.ip_adm_dx)
EXEC('INSERT INTO dbo.Tmp_ip_adm_dx (CASEID, DX, DXNO)
SELECT CONVERT(int, CASEID), CONVERT(varchar(5), DX), DXNO FROM
dbo.ip_adm_dx TABLOCKX')
GO
DROP TABLE dbo.ip_adm_dx
GO
EXECUTE sp_rename N'dbo.Tmp_ip_adm_dx', N'ip_adm_dx', 'OBJECT'
GO
COMMIT
"Tibor Karaszi" wrote:
> Check and see :-). Press the "save change script" button and you will see the SQL that EM is about
> to execute.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Shishir Viriyala" <ShishirViriyala@.discussions.microsoft.com> wrote in message
> news:F183A811-5BBF-4BA0-9E6D-BE671FDB8837@.microsoft.com...
> > Okay, how about doing it in Enterprise Manager's Design Table section? I can
> > see all the columns with their data types at once... if I changed multiple
> > data types there, does SQL Server rebuild the table for each column, or does
> > do everything in one shot? Appreciate the help!
> >
> > "Chandra" wrote:
> >
> >> well! u dont have that flexibility :(
> >> u can add multiple columns using ALTER
> >>
> >> --
> >> best Regards,
> >> Chandra
> >> http://chanduas.blogspot.com/
> >> http://groups.msn.com/SQLResource/
> >> ---
> >>
> >>
> >>
> >> "Shishir Viriyala" wrote:
> >>
> >> > Hi Guys,
> >> >
> >> > I have always wondered what SQL Server actually does when I try to change
> >> > the data type on any column in a table. I know that SQL Server will rebuild
> >> > the whole table when its changing the data type, so if I had multiple columns
> >> > that I wanted to change data types for, this is the only way I have been able
> >> > to do it using TSQL:
> >> >
> >> > ALTER TABLE TEST
> >> > ALTER COLUMN A (char(4))
> >> >
> >> > ALTER TABLE TEST
> >> > ALTER COLUMN B (INT)
> >> >
> >> > ALTER TABLE TEST
> >> > ALTER COLUMN C (FLOAT)
> >> >
> >> > Does the above mean that SQL Server has to rebuild my table 3 different
> >> > times? How could I rewrite this code so that SQL Server can convert all my
> >> > columns with one rebuild? Thanks guys!
> >> >
>|||On Fri, 5 Aug 2005 08:15:38 -0700, Shishir Viriyala wrote:
>Okay, how about doing it in Enterprise Manager's Design Table section? I can
>see all the columns with their data types at once... if I changed multiple
>data types there, does SQL Server rebuild the table for each column, or does
>do everything in one shot? Appreciate the help!
Hi Shishir,
Neither. Enterprise Manager will simply create a new table, copy over
the data, then drop the old table. If the table is involved in any
foreign key relationships, it will drop and recreate them as well. And
if the table has any triggers, they too will be recreated.
Copying the data over to a new table might be the best strategy in your
specific case, but in general, the methods chosen by EM to apply changes
tend to be overkill. (And very slow on large tables!)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Question on changing data types on a table

u can add multiple columns using ALTER
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Shishir Viriyala" wrote:

> Hi Guys,
> I have always wondered what SQL Server actually does when I try to change
> the data type on any column in a table. I know that SQL Server will rebui
ld
> the whole table when its changing the data type, so if I had multiple colu
mns
> that I wanted to change data types for, this is the only way I have been a
ble
> to do it using TSQL:
> ALTER TABLE TEST
> ALTER COLUMN A (char(4))
> ALTER TABLE TEST
> ALTER COLUMN B (INT)
> ALTER TABLE TEST
> ALTER COLUMN C (FLOAT)
> Does the above mean that SQL Server has to rebuild my table 3 different
> times? How could I rewrite this code so that SQL Server can convert all m
y
> columns with one rebuild? Thanks guys!
>|||Okay, how about doing it in Enterprise Manager's Design Table section? I ca
n
see all the columns with their data types at once... if I changed multiple
data types there, does SQL Server rebuild the table for each column, or does
do everything in one shot? Appreciate the help!
"Chandra" wrote:
[vbcol=seagreen]
> well! u dont have that flexibility

> u can add multiple columns using ALTER
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Shishir Viriyala" wrote:
>|||it rebuilds for each column.
--
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Shishir Viriyala" wrote:
[vbcol=seagreen]
> Okay, how about doing it in Enterprise Manager's Design Table section? I
can
> see all the columns with their data types at once... if I changed multipl
e
> data types there, does SQL Server rebuild the table for each column, or do
es
> do everything in one shot? Appreciate the help!
> "Chandra" wrote:
>|||Check and see :-). Press the "save change script" button and you will see th
e SQL that EM is about
to execute.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Shishir Viriyala" <ShishirViriyala@.discussions.microsoft.com> wrote in mess
age
news:F183A811-5BBF-4BA0-9E6D-BE671FDB8837@.microsoft.com...[vbcol=seagreen]
> Okay, how about doing it in Enterprise Manager's Design Table section? I
can
> see all the columns with their data types at once... if I changed multipl
e
> data types there, does SQL Server rebuild the table for each column, or do
es
> do everything in one shot? Appreciate the help!
> "Chandra" wrote:
>|||Aha! Thanks for the tip Tibor! I have included what the change script was
showing me. The two columns for which I changed data types were CASEID and
DX. Note that both those columns are being converted in one statement.
Using EP is definitely the way to go!
Thanks to you and Chandra for the input......
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_ip_adm_dx
(
CASEID int NULL,
DX varchar(5) NULL,
DXNO int NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.ip_adm_dx)
EXEC('INSERT INTO dbo.Tmp_ip_adm_dx (CASEID, DX, DXNO)
SELECT CONVERT(int, CASEID), CONVERT(varchar(5), DX), DXNO FROM
dbo.ip_adm_dx TABLOCKX')
GO
DROP TABLE dbo.ip_adm_dx
GO
EXECUTE sp_rename N'dbo.Tmp_ip_adm_dx', N'ip_adm_dx', 'OBJECT'
GO
COMMIT
"Tibor Karaszi" wrote:

> Check and see :-). Press the "save change script" button and you will see
the SQL that EM is about
> to execute.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Shishir Viriyala" <ShishirViriyala@.discussions.microsoft.com> wrote in me
ssage
> news:F183A811-5BBF-4BA0-9E6D-BE671FDB8837@.microsoft.com...
>|||On Fri, 5 Aug 2005 08:15:38 -0700, Shishir Viriyala wrote:

>Okay, how about doing it in Enterprise Manager's Design Table section? I c
an
>see all the columns with their data types at once... if I changed multiple
>data types there, does SQL Server rebuild the table for each column, or doe
s
>do everything in one shot? Appreciate the help!
Hi Shishir,
Neither. Enterprise Manager will simply create a new table, copy over
the data, then drop the old table. If the table is involved in any
foreign key relationships, it will drop and recreate them as well. And
if the table has any triggers, they too will be recreated.
Copying the data over to a new table might be the best strategy in your
specific case, but in general, the methods chosen by EM to apply changes
tend to be overkill. (And very slow on large tables!)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Question on changing data types on a table

u can add multiple columns using ALTER
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
"Shishir Viriyala" wrote:

> Hi Guys,
> I have always wondered what SQL Server actually does when I try to change
> the data type on any column in a table. I know that SQL Server will rebuild
> the whole table when its changing the data type, so if I had multiple columns
> that I wanted to change data types for, this is the only way I have been able
> to do it using TSQL:
> ALTER TABLE TEST
> ALTER COLUMN A (char(4))
> ALTER TABLE TEST
> ALTER COLUMN B (INT)
> ALTER TABLE TEST
> ALTER COLUMN C (FLOAT)
> Does the above mean that SQL Server has to rebuild my table 3 different
> times? How could I rewrite this code so that SQL Server can convert all my
> columns with one rebuild? Thanks guys!
>
|||Okay, how about doing it in Enterprise Manager's Design Table section? I can
see all the columns with their data types at once... if I changed multiple
data types there, does SQL Server rebuild the table for each column, or does
do everything in one shot? Appreciate the help!
"Chandra" wrote:
[vbcol=seagreen]
> well! u dont have that flexibility

> u can add multiple columns using ALTER
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
>
> "Shishir Viriyala" wrote:
|||it rebuilds for each column.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
"Shishir Viriyala" wrote:
[vbcol=seagreen]
> Okay, how about doing it in Enterprise Manager's Design Table section? I can
> see all the columns with their data types at once... if I changed multiple
> data types there, does SQL Server rebuild the table for each column, or does
> do everything in one shot? Appreciate the help!
> "Chandra" wrote:
|||Check and see :-). Press the "save change script" button and you will see the SQL that EM is about
to execute.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Shishir Viriyala" <ShishirViriyala@.discussions.microsoft.com> wrote in message
news:F183A811-5BBF-4BA0-9E6D-BE671FDB8837@.microsoft.com...[vbcol=seagreen]
> Okay, how about doing it in Enterprise Manager's Design Table section? I can
> see all the columns with their data types at once... if I changed multiple
> data types there, does SQL Server rebuild the table for each column, or does
> do everything in one shot? Appreciate the help!
> "Chandra" wrote:
|||Aha! Thanks for the tip Tibor! I have included what the change script was
showing me. The two columns for which I changed data types were CASEID and
DX. Note that both those columns are being converted in one statement.
Using EP is definitely the way to go!
Thanks to you and Chandra for the input......
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_ip_adm_dx
(
CASEID int NULL,
DX varchar(5) NULL,
DXNO int NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.ip_adm_dx)
EXEC('INSERT INTO dbo.Tmp_ip_adm_dx (CASEID, DX, DXNO)
SELECT CONVERT(int, CASEID), CONVERT(varchar(5), DX), DXNO FROM
dbo.ip_adm_dx TABLOCKX')
GO
DROP TABLE dbo.ip_adm_dx
GO
EXECUTE sp_rename N'dbo.Tmp_ip_adm_dx', N'ip_adm_dx', 'OBJECT'
GO
COMMIT
"Tibor Karaszi" wrote:

> Check and see :-). Press the "save change script" button and you will see the SQL that EM is about
> to execute.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Shishir Viriyala" <ShishirViriyala@.discussions.microsoft.com> wrote in message
> news:F183A811-5BBF-4BA0-9E6D-BE671FDB8837@.microsoft.com...
>
|||On Fri, 5 Aug 2005 08:15:38 -0700, Shishir Viriyala wrote:

>Okay, how about doing it in Enterprise Manager's Design Table section? I can
>see all the columns with their data types at once... if I changed multiple
>data types there, does SQL Server rebuild the table for each column, or does
>do everything in one shot? Appreciate the help!
Hi Shishir,
Neither. Enterprise Manager will simply create a new table, copy over
the data, then drop the old table. If the table is involved in any
foreign key relationships, it will drop and recreate them as well. And
if the table has any triggers, they too will be recreated.
Copying the data over to a new table might be the best strategy in your
specific case, but in general, the methods chosen by EM to apply changes
tend to be overkill. (And very slow on large tables!)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Wednesday, March 28, 2012

Question regarding SP

Monday, March 26, 2012

Question regarding Identitiy field

Question on validation of mining models

Monday, March 12, 2012

Question on Naive Bayes Viewer

Question on Naive Bayes Viewer

Friday, March 9, 2012

Question on Links coefficiency of dependency network

Question on how to display data in this unique situation.....

Question on how to display data in this unique situation.....

Question on how to display data in this unique situation using Matrix.....

Saturday, February 25, 2012

Question on changing data types on a table

Question on changing data types on a table

Question on changing data types on a table

SQLAgentMail

Blog Archive

About Me