Decimal in Design Table

Hi, this is a trivial question.
I am trying to make a 2 point decimal stored in the sql database.
For example,
a field called, "Points", will have values such as 100.00, 107.07, 99.88, 56.01 etc...
and all decimals like 108.009 would round up to 108.01.

Now, in the design table of the sql server.
Column Name Data Type Length Allow Nulls
Points Decimal 9 Yes
------------
Description (blank)
Default Value (blank)
Precision 18
Scale 0
Formula (blank)
The numbers in red are assign by default. How would I alter that to make it a 2 decimal point number?
What should I change to make it work?
Thank youyou can do it in the design view. or you can also do it from query analyzer with an ALTER TABLE stmt. From books on line:

C. Changing the data type of a column

The following example changes a column of a table fromINT toDECIMAL.

	Copy Code
CREATE TABLE doc_exy ( column_a INT ) ;GOINSERT INTO doc_exy (column_a)VALUES (10) ;GOALTER TABLE doc_exy ALTER COLUMN column_a DECIMAL (5, 2) ;GODROP TABLE doc_exy ;GO

|||Thank you so much. It's always good to know how to do it in code. However, how do I do it in design view?
|||ChangeScaleto 2.
decimal[(p[,s])] andnumeric[(p[,s])]

Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The SQL-92 synonyms fordecimal aredec anddec(p,s).numeric is functionally equivalent todecimal.

p (precision)

The maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision, which is 38. The default precision is 18.

s(scale)

The maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 throughp. Scale can be specified only if precision is specified. The default scale is 0; therefore, 0 <=s <=p. Maximum storage sizes vary, based on the precision.

|||Thanks. That's the exact information I was looking for.

Debugging problem with break points not being picked up

I have Team suite VS2005 sp1 installed and I can't debug my SSIS packages. If I put any break points in the Script code when I try to debug I get the message.

"SQL Server Integration Services Script Task has encountered a problem and needs to close. We are sorry for the inconvenience."

I click on close and it runs the package but doesn't stop at any break point. If I take out all break points it will run without this error. A couple of times I have managed to put in a single break point early in the package within a different script component which causes it to break... but at a completely different point within a completely different script component. It's so weird. Any help would be appreciated.

Hoots.

I've moved this thread to the SSIS forum. The core VS Debugger team does not cover this area,

John

|||

An excert from Books Online -

Debugging the Script Task

To debug the code in your Script task, set at least one breakpoint in the code, and then close the VSA IDE to run the package in Business Intelligence Development Studio. When package execution enters the Script task, the VSA IDE reopens and displays your code in read-only mode. After execution reaches your breakpoint, you can examine variable values and step through the remaining code.

Note: You must execute the package to debug into your Script task. If you execute only the individual task, breakpoints in the Script task code are ignored.

Note:

You cannot debug a Script task when you run the Script task as part of a child package that is run from an Execute Package task. Breakpoints that you set within the Script task in the child package are disregarded in these circumstances. You can debug the child package normally by running it separately.

In addition -

You cannot debug Script tasks in event handlers.

When you debug a package that contains multiple Script tasks, the debugger hits breakpoints in only one Script task and will ignore breakpoints in the other Script tasks. If a Script task is part of a Foreach Loop or For Loop container, the debugger ignores breakpoints in the Script task after the first iteration of the loop.

|||I know how to debug a script task, it just doesn't. Clicking on Debug instead of close at the error message that's received brings up the Visual Studio Just in time debugger dialog which says:

An unhandled exception ('System.Runtime.InteropServices.COMException') occured in DTAttach.exe [3148]

It then gives me options to continue debugging in a new instance of Microsoft CLR debugger 2005 or a new instance of Visual Studio 2005. Choosing the CLT debugger brings up the dialog box saying:

"An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in Microsoft.SqlServer.ScriptTask.dll

Additional information: File position not valid for new breakpoint."

But the CLR debugger for DTAttach just reports that "(Disassembly cannot be displayed in run mode.)"

This seems to be a problem that has been around for a while, see this other post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=889400&SiteID=1

I too get the "Microsoft Visual Studio has lost its link to..." message. I have searched and searched, it seems quite a few other people are having the same problem but so far I haven't found a fix yet which is why I posted here.

Please help, is there a fix, can anyone tell me which file break points are stored in so that I can delete it manually as it could be corrupt?

thanks

Hoots.

|||I got a similar error without reference to the 'DTAttach'. Clicked the Debug button (Debug, Send Error Report, Don't Send) and continued from the break point that caused the error. Hope it works because that's all I got.|||Thanks for the response, unfortunately I think it just depends on what it feels like!! I can't get round it cos the whole debug thing just seems to be really flakey and am having to do it the old fashioned way with lots of msgbox commands. Would really appreciate a comment from a MVP or Microsoft itself on this one.

|||

Was having this problem just now. realized I had a breakpoint in a disabled script task. Once I took it out, not only did it stop throwing an error, it stopped on my other breakpoints.

Hope this helps.

|||Non of my script tasks are disabled. I think it's just a problem with the BIDS debugger. I'd love to have that confirmed and to find out when a fix is going to be available though.

|||

Hi there,

The problem you're seeing can be related to the fact that the precompiled script is out of sync with the code. One way to fix it is to remove the breakpoints on that script, regenerate the script binary ( either by changing the script by adding a comment or by flipping the precompiled property on the task off and back on), add the breakpoints back and save the script task and see if it works.

This assumes that you are not running on Windows Vista for which the debugging is currently broken due to an incompatibility between VSA and the CLR installed with the OS. On Vista the problem should be fixed at a later time but only for SQL Server SP2.

Let me know if refreshing the script binary caused the breakpoints to be valid again.

Thanks,

Silviu

|||Thanks for the info, unfortunately it didn't work. My package is made up predominantly of script components and contains a Foreach Loop and an additional Foreach loop within the other Loop, both are variable enumerators.

I went through each script component, took out any break points and put in additional comments. I then put a single break point in one of the scripts and ran it again but it still comes up with the same problem.

thanks

Hoots.
|||But break points are not supported in SSIS 2005... You have to use message boxes instead. This is a well documented "feature."|||Well documented... Really? Could you point me to the document that describes the features that causes BIDS to come up with the exception:

"An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in Microsoft.SqlServer.ScriptTask.dll

Seriously, I really would like to be able to read something definitive on break points in any script tasks not being supported for SSIS. After a large amount of searching and finding other people with the same problem, this is the first I've heard of them not being supported.

thanks

Hoots.

|||Have you read through this document? I see it claims support for script tasks, but not script components. I was under the assumption that breakpoints inside either script element were not supported.

http://msdn2.microsoft.com/en-us/library/ms140033.aspx|||Sorry, I meant script task rather than script component although I can't even see anything called a script component, what is it? Just as a note, all my script tasks are all set with precompiled as false.

Hoots.

|||Yep, read it now, I seem to be doing everything right, it just doesn't work.

Debugging problem with break points not being picked up

I have Team suite VS2005 sp1 installed and I can't debug my SSIS packages. If I put any break points in the Script code when I try to debug I get the message.

"SQL Server Integration Services Script Task has encountered a problem and needs to close. We are sorry for the inconvenience."

I click on close and it runs the package but doesn't stop at any break point. If I take out all break points it will run without this error. A couple of times I have managed to put in a single break point early in the package within a different script component which causes it to break... but at a completely different point within a completely different script component. It's so weird. Any help would be appreciated.

Hoots.

I've moved this thread to the SSIS forum. The core VS Debugger team does not cover this area,

John

|||

An excert from Books Online -

Debugging the Script Task

To debug the code in your Script task, set at least one breakpoint in the code, and then close the VSA IDE to run the package in Business Intelligence Development Studio. When package execution enters the Script task, the VSA IDE reopens and displays your code in read-only mode. After execution reaches your breakpoint, you can examine variable values and step through the remaining code.

Note: You must execute the package to debug into your Script task. If you execute only the individual task, breakpoints in the Script task code are ignored.

Note:

You cannot debug a Script task when you run the Script task as part of a child package that is run from an Execute Package task. Breakpoints that you set within the Script task in the child package are disregarded in these circumstances. You can debug the child package normally by running it separately.

In addition -

You cannot debug Script tasks in event handlers.

When you debug a package that contains multiple Script tasks, the debugger hits breakpoints in only one Script task and will ignore breakpoints in the other Script tasks. If a Script task is part of a Foreach Loop or For Loop container, the debugger ignores breakpoints in the Script task after the first iteration of the loop.

|||I know how to debug a script task, it just doesn't. Clicking on Debug instead of close at the error message that's received brings up the Visual Studio Just in time debugger dialog which says:

An unhandled exception ('System.Runtime.InteropServices.COMException') occured in DTAttach.exe [3148]

It then gives me options to continue debugging in a new instance of Microsoft CLR debugger 2005 or a new instance of Visual Studio 2005. Choosing the CLT debugger brings up the dialog box saying:

"An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in Microsoft.SqlServer.ScriptTask.dll

Additional information: File position not valid for new breakpoint."

But the CLR debugger for DTAttach just reports that "(Disassembly cannot be displayed in run mode.)"

This seems to be a problem that has been around for a while, see this other post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=889400&SiteID=1

I too get the "Microsoft Visual Studio has lost its link to..." message. I have searched and searched, it seems quite a few other people are having the same problem but so far I haven't found a fix yet which is why I posted here.

Please help, is there a fix, can anyone tell me which file break points are stored in so that I can delete it manually as it could be corrupt?

thanks

Hoots.

|||I got a similar error without reference to the 'DTAttach'. Clicked the Debug button (Debug, Send Error Report, Don't Send) and continued from the break point that caused the error. Hope it works because that's all I got.|||Thanks for the response, unfortunately I think it just depends on what it feels like!! I can't get round it cos the whole debug thing just seems to be really flakey and am having to do it the old fashioned way with lots of msgbox commands. Would really appreciate a comment from a MVP or Microsoft itself on this one.

|||

Was having this problem just now. realized I had a breakpoint in a disabled script task. Once I took it out, not only did it stop throwing an error, it stopped on my other breakpoints.

Hope this helps.

|||Non of my script tasks are disabled. I think it's just a problem with the BIDS debugger. I'd love to have that confirmed and to find out when a fix is going to be available though.

|||

Hi there,

The problem you're seeing can be related to the fact that the precompiled script is out of sync with the code. One way to fix it is to remove the breakpoints on that script, regenerate the script binary ( either by changing the script by adding a comment or by flipping the precompiled property on the task off and back on), add the breakpoints back and save the script task and see if it works.

This assumes that you are not running on Windows Vista for which the debugging is currently broken due to an incompatibility between VSA and the CLR installed with the OS. On Vista the problem should be fixed at a later time but only for SQL Server SP2.

Let me know if refreshing the script binary caused the breakpoints to be valid again.

Thanks,

Silviu

|||Thanks for the info, unfortunately it didn't work. My package is made up predominantly of script components and contains a Foreach Loop and an additional Foreach loop within the other Loop, both are variable enumerators.

I went through each script component, took out any break points and put in additional comments. I then put a single break point in one of the scripts and ran it again but it still comes up with the same problem.

thanks

Hoots.
|||But break points are not supported in SSIS 2005... You have to use message boxes instead. This is a well documented "feature."|||Well documented... Really? Could you point me to the document that describes the features that causes BIDS to come up with the exception:

"An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in Microsoft.SqlServer.ScriptTask.dll

Seriously, I really would like to be able to read something definitive on break points in any script tasks not being supported for SSIS. After a large amount of searching and finding other people with the same problem, this is the first I've heard of them not being supported.

thanks

Hoots.

|||Have you read through this document? I see it claims support for script tasks, but not script components. I was under the assumption that breakpoints inside either script element were not supported.

http://msdn2.microsoft.com/en-us/library/ms140033.aspx|||Sorry, I meant script task rather than script component although I can't even see anything called a script component, what is it? Just as a note, all my script tasks are all set with precompiled as false.

Hoots.

|||Yep, read it now, I seem to be doing everything right, it just doesn't work.

debugging in SSIS

I have a script task in SSIS package. I want to debug the code . but even after setting break points control does not go through the code. So am using msgbox for debuggin purpose ( but its really painfull) can anybody guide me on this.

Thanks,

Prash

first I suggest posting to the SSIS forum

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=80&SiteID=1

Second, this absolutely should be working.

With the VSA designer opened, f9 to set a breakpoint on some code.

when the package executes (from within the design environment, executing outside the designer will not bring up a jit type debugger) the breakpoint should hit.

If you continue to see this problem, post to the SSIS forum to see if anyone can think of things to try.

|||Moving to the right forum|||

prashant550806 wrote:

I have a script task in SSIS package. I want to debug the code . but even after setting break points control does not go through the code. So am using msgbox for debuggin purpose ( but its really painfull) can anybody guide me on this.

Thanks,

Prash

It isn't possible at the moment.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=359859&SiteID=1|||Just a minor point of clarification - you can debug script tasks (in the Control Flow), but you can't debug script components (in the Data Flow). I'm guessing from your problem that you are trying to debug a script component, not a script task.|||

I have tried both script task in control flow as well as script component in data flow but wasnt successful

regards,

Prash

|||

its pitty that SSIS does not support debug mode in script component/task...

Prash

|||

Phil Brammer wrote:

prashant550806 wrote:

I have a script task in SSIS package. I want to debug the code . but even after setting break points control does not go through the code. So am using msgbox for debuggin purpose ( but its really painfull) can anybody guide me on this.

Thanks,

Prash

It isn't possible at the moment.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=359859&SiteID=1

Phil,

That's not true. It is not possible to debug script components but it IS possible to debug script tasks - which is what the original poster said he wanted to do.

-Jamie

|||

prashant550806 wrote:

its pitty that SSIS does not support debug mode in script component/task...

Prash

It does. You CAN debug script tasks - as is stated elsewhere in this thread.

-Jamie