page break

I have a matrix and I want to insert a page break on a column group. I dont
see the option under grouping and sorting properties for my column groups
only on the rows?On Nov 2, 10:39 am, RYF <R...@.discussions.microsoft.com> wrote:
> I have a matrix and I want to insert a page break on a column group. I dont
> see the option under grouping and sorting properties for my column groups
> only on the rows?
Unfortunately, this functionality does not exist currently. A
workaround would be to create 2 matrices/matrix controls in the report
and have the columns you want on the odd pages in the first matrix
control (and do a page break after it) and the columns you want on the
even pages in the second matrix control (and do a page break after it
as well). Sorry that I could not be of greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Padding identity column values with zeros

Hi Guys

Is there an easy way to pad identity column values with zeros to the left (0001, 0010,0100, etc) or I will just have to format them before displaying

George

This works for me a lot of times.

REPLICATE('0', 4-len(cast(id as char(4))) + cast(id as char(4))

|||

You could use an expression like:

right(replicate('0', 8) + cast($identity as varchar), 8)

-- or

right(replicate('0', 8) + cast(identitycol as varchar), 8)

You could either use a computed column or a view with the expression. Alternatively, you could simply do these type of operations on the client side.

Padding an auto-incremented Int column to ten zeros

Good day gentleman,
Is it possible to pad columns of Int data type with zeros?

I currently have a primary key column of data type Int which is auto-incremented by 1. My problem is I need to pad the auto-incremented ID's with ten zeros so, 0000000009 instead of 9.

I can pad the ID's in the c# dataaccess layer, but i need to generate/store them padded in the database to allow direct reporting.

I am using Enterprise Manager and i can see a Formula field when the table is in design view, can i use this? i am just guessing here.

Any comments or pointers would be a big help.What you are discussing is a display issue. An int is, by definition, isolated from any particular display formatting. It is just a number. You could add an additional column (nvarchar) and have a trigger on inserts that populates that nvarchar() column with a string value with 0 padding.

PackageID in Logging Provider

Is there a technical reason (I imagine the real reason is time) that PackageID wasn't included as a Logging Provider column to log? While you can still track this by doing some fun things like event handlers or self-joining, I would think from a usability perspective, you should avoid that altogether by just adding the column.

I added a suggestion here to vote on if anyone else agrees:

http://lab.msdn.microsoft.com/ProductFeedback/viewfeedback.aspx?feedbackid=e58141d5-859f-4941-a675-cb9352d85575

-- Brian

I'd rather have PackageName!!!

Mind you, one thing I do is dynamically set up the name of my log file using the following expression:

REPLACE(@.[System::PackageName], " ", ".") + (DT_STR, 4, 1252) DATEPART( "yyyy", @.[System::StartTime] ) + RIGHT("0" + (DT_STR, 2, 1252) DATEPART( "mm", @.[System::StartTime] ), 2) + RIGHT("0" + (DT_STR, 4, 1252) DATEPART( "dd", @.[System::StartTime] ), 2) + RIGHT("0" + (DT_STR, 4, 1252) DATEPART( "hh", @.[System::StartTime] ), 2) + RIGHT("0" + (DT_STR, 4, 1252) DATEPART( "mi", @.[System::StartTime] ), 2) + RIGHT("0" + (DT_STR, 4, 1252) DATEPART( "ss", @.[System::StartTime] ), 2) + ".log"

Which gives a package logfilename of MyPackageName20060111142153.log

i.e. Something that contains the package name, and the added benefit that you get a new file for each execution

-Jamie

Override Identity Column using Datatable

I need copy a table from a remote (hosted) SQL 2000 database server to my local machine. I don't have access to backups and am unable to correctly configure my local machine to add a linked server. So I plan to retrieve the data to a datatable, copy it in code and save it to my local server. But the table contains an identity column which I will need to insert the values manually so they match the original.

Can anyone tell me how I can set the datatable's save to use my manual values instead of the autonumber value?

Thanks.

there are two options

1. First remove the constraint on identity column, then import the data, after that apply the constraint on iidentity column.

2. Add another column for it,

|||

SET IDENTITY_INSERT {YourTableName} ON

INSERT INTO {YourTableName} ....

SET IDENTITY_INSERT {YourTableName} OFF

|||

Thank you for the response Motley, but I don't want to have to create SQL statements; I want to do this directly in code using a datatable. Do you know how to do that?

|||

If you do not want to write sql query, then I suggest to go for answer1, turn off identity feature, import the data and turn it on.

|||

You would use the "SQL Statements" I gave above in a sqldataadapters's updatecommand.

|||

Hi,

Actually the code is also calling the SQL statements to do the update. So you will always need some SQL Statements for updating.

I suggest you use the way Girijesh has provided. Turn off the identity contraint off and import data.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

Overlay charts

Hello all.
I need to overlay two charts (column & line) & I keep seeing references
to this technique on these groups but no explanation of how to do it
(nor in the help). At the moment when I overlay charts the top one is
visible but not the bottom on,e & I can't see how to make the
background transparent.
Has anyone does this before?
All help appreciated.
Cheers
AustinHTML renderer (which is used by report viewer in Report Manager) does not
support overlayed controls. Although if you render such report to pdf or jpg
you will get charts overlayed.
"AustinRathe" <austinr@.gmail.com> wrote in message
news:1115648678.321251.127760@.o13g2000cwo.googlegroups.com...
> Hello all.
> I need to overlay two charts (column & line) & I keep seeing references
> to this technique on these groups but no explanation of how to do it
> (nor in the help). At the moment when I overlay charts the top one is
> visible but not the bottom on,e & I can't see how to make the
> background transparent.
> Has anyone does this before?
> All help appreciated.
> Cheers
> Austin
>

overlapped point labels

Hi, i've a stacked column chart and the values are show overlapped.
I've tried auto and all positions, but its always overlapped, someone know
how to avoid this ?
Thanks in advanceAnyone install SP2 yet? Any problems or issues?
Thanks,
Fredrick

Overflowed int column

I am trying to run a stored procedure but I get the
following error:
Server: Msg 248, Level 16, State 1, Procedure
sp_MarriageLookup, Line 34
The conversion of the varchar value '2820103430'
overflowed an int column. Maximum integer value exceeded.
Stored Procedure: Unifirst919.dbo.sp_MarriageLookup
Return Code = -6
Output Parameter(s):
@.Barcode = 2820103430
The length of the @.Barcode variable is 10 characters and
it is a nchar type variable so I don't understand the 'int
column' overflow issue.
Also, the wierd thing is that I can run this same stored
procedure on another server (similar database, just
different data) and it works just fine. See below:
Stored Procedure: Unifirst.dbo.sp_MarriageLookup
Return Code = 0
Output Parameter(s):
@.Barcode = 4320000849
I don't see anything noticibly different between the two
databases.
What causes this problem and how do I correct?
Roger.Maximum value for int (SQL Server) data type is 2147483647.
So, if you convert varchar value '2820103430' to int,
overflow error is normal.
In the secend case, when this stored procedure
work correctly on another server, check data type
of the column to which this sp convert varchar parameter
ph

> I am trying to run a stored procedure but I get the
> following error:
> Server: Msg 248, Level 16, State 1, Procedure
> sp_MarriageLookup, Line 34
> The conversion of the varchar value '2820103430'
> overflowed an int column. Maximum integer value exceeded.
> Stored Procedure: Unifirst919.dbo.sp_MarriageLookup
> Return Code = -6
> Output Parameter(s):
> @.Barcode = 2820103430
> The length of the @.Barcode variable is 10 characters and
> it is a nchar type variable so I don't understand the 'int
> column' overflow issue.
> Also, the wierd thing is that I can run this same stored
> procedure on another server (similar database, just
> different data) and it works just fine. See below:
> Stored Procedure: Unifirst.dbo.sp_MarriageLookup
> Return Code = 0
> Output Parameter(s):
> @.Barcode = 4320000849
> I don't see anything noticibly different between the two
> databases.
> What causes this problem and how do I correct?
> Roger.

Overflowed int column

I am trying to run a stored procedure but I get the
following error:
Server: Msg 248, Level 16, State 1, Procedure
sp_MarriageLookup, Line 34
The conversion of the varchar value '2820103430'
overflowed an int column. Maximum integer value exceeded.
Stored Procedure: Unifirst919.dbo.sp_MarriageLookup
Return Code = -6
Output Parameter(s):
@.Barcode = 2820103430
The length of the @.Barcode variable is 10 characters and
it is a nchar type variable so I don't understand the 'int
column' overflow issue.
Also, the wierd thing is that I can run this same stored
procedure on another server (similar database, just
different data) and it works just fine. See below:
Stored Procedure: Unifirst.dbo.sp_MarriageLookup
Return Code = 0
Output Parameter(s):
@.Barcode = 4320000849
I don't see anything noticibly different between the two
databases.
What causes this problem and how do I correct?
Roger.Here is a copy of the SP:
/*
Purpose: Given a marriage tag code, return the original
barcode
Input params: Marriage tag code, type varchar(10)
Output params: Original bar code, type varchar(10)
Error params: Output of '9' indicates lookup failed
*/
CREATE PROCEDURE sp_MarriageLookup
@.Tag varchar(10),
@.Barcode varchar(10) output
AS
Declare @.eventid int
Declare cur_Marriage cursor for
Select CurrentTag from tblMarriage
Where MarriageTag = @.Tag
Open cur_Marriage
Fetch next from cur_Marriage into @.Barcode
If (@.@.Fetch_Status <> 0)
Begin
/* Error log: Lookup failed */
Set @.Barcode = 9
Set @.eventid = 901
End
Else Begin
Fetch Next from cur_Marriage into @.Barcode
If (@.@.Fetch_Status = 0)
Begin
/* Error log: More than one entry for the marriage
code */
Set @.Barcode = 9
Set @.eventid = 902
End
End
Close cur_Marriage
Deallocate cur_Marriage
If (@.Barcode = 9)
Begin
Execute sp_LogError 1, @.eventid, 0, @.Tag
End
GO
>--Original Message--
>I am trying to run a stored procedure but I get the
>following error:
>Server: Msg 248, Level 16, State 1, Procedure
>sp_MarriageLookup, Line 34
>The conversion of the varchar value '2820103430'
>overflowed an int column. Maximum integer value exceeded.
>Stored Procedure: Unifirst919.dbo.sp_MarriageLookup
> Return Code = -6
> Output Parameter(s):
> @.Barcode = 2820103430
>The length of the @.Barcode variable is 10 characters and
>it is a nchar type variable so I don't understand
the 'int
>column' overflow issue.
>Also, the wierd thing is that I can run this same stored
>procedure on another server (similar database, just
>different data) and it works just fine. See below:
>Stored Procedure: Unifirst.dbo.sp_MarriageLookup
> Return Code = 0
> Output Parameter(s):
> @.Barcode = 4320000849
>I don't see anything noticibly different between the two
>databases.
>What causes this problem and how do I correct?
>Roger.
>.
>|||Maximum value for int (SQL Server) data type is 2147483647.
So, if you convert varchar value '2820103430' to int,
overflow error is normal.
In the secend case, when this stored procedure
work correctly on another server, check data type
of the column to which this sp convert varchar parameter
ph
> I am trying to run a stored procedure but I get the
> following error:
> Server: Msg 248, Level 16, State 1, Procedure
> sp_MarriageLookup, Line 34
> The conversion of the varchar value '2820103430'
> overflowed an int column. Maximum integer value exceeded.
> Stored Procedure: Unifirst919.dbo.sp_MarriageLookup
> Return Code = -6
> Output Parameter(s):
> @.Barcode = 2820103430
> The length of the @.Barcode variable is 10 characters and
> it is a nchar type variable so I don't understand the 'int
> column' overflow issue.
> Also, the wierd thing is that I can run this same stored
> procedure on another server (similar database, just
> different data) and it works just fine. See below:
> Stored Procedure: Unifirst.dbo.sp_MarriageLookup
> Return Code = 0
> Output Parameter(s):
> @.Barcode = 4320000849
> I don't see anything noticibly different between the two
> databases.
> What causes this problem and how do I correct?
> Roger.

Overflow error

On Thu, 13 Oct 2005 19:35:16 GMT, Mike wrote:

>I have the SQL table column PRICE set for decimal (14,14).

Hi Mike,

That means that you have a total of 14 digits, 14 of which are to the
right of the decimal. Leaving no digits to the left.

>Any one know why I would get an overflow error.

Probably because there's a value above 1.000 or below -1.000 in your
data.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)I am trying to use the bulk insert command but I am getting the following
error:

--Begin Error Msg--
Server: Msg 4867, Level 16, State 1, Line 1
Bulk insert data conversion error (overflow) for row 1, column 3 (PRICE).
--End Error Msg--

The data is formatted as a number, single with 14 decimal. I am exporting
to csv txt file from MS-Access.

I have the SQL table column PRICE set for decimal (14,14).

Any one know why I would get an overflow error.

Mike
m charney at dunlap hospital dot org|||Ok I fix those now I have the following error

--Begin Error Msg--
Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 1, column 14
(STDCOST).
--End Error Msg--

The STDCOST is set to decimal (28,14) and is a formatted in Access as a
number, single with 14 decimal. I don't know why I would be getting a Type
Mismatch error.

Any idea?

Mike

"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:a3etk1hrq4v7odfrk71r0hli5lfivqebgt@.4ax.com...
> On Thu, 13 Oct 2005 19:35:16 GMT, Mike wrote:
>>I have the SQL table column PRICE set for decimal (14,14).
> Hi Mike,
> That means that you have a total of 14 digits, 14 of which are to the
> right of the decimal. Leaving no digits to the left.
>>Any one know why I would get an overflow error.
> Probably because there's a value above 1.000 or below -1.000 in your
> data.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Thu, 13 Oct 2005 20:34:14 GMT, Mike wrote:

>Ok I fix those now I have the following error
>--Begin Error Msg--
>Server: Msg 4864, Level 16, State 1, Line 1
>Bulk insert data conversion error (type mismatch) for row 1, column 14
>(STDCOST).
>--End Error Msg--
>The STDCOST is set to decimal (28,14) and is a formatted in Access as a
>number, single with 14 decimal. I don't know why I would be getting a Type
>Mismatch error.
>Any idea?

Hi Mike,

My guess is that there's some dirty data in the file you're trying to
read in.

You might want to double-check the data. And the format file you used.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Sorry for the double post. I dont know what I was thinking.

I looked for bad data but I did not see any. With the column being set
to numeric and singal in MS-Access, I dont think there could be any non-
numeric data in the column. The only thought I would have is Null values
being in the column but the SQL table is set to allow Null values.

What exactly is a Type Mismatch error? What does it mean? I thought it
might mean I was trying to input non-numeric data into a numeric column,
but I dont see any.

I am stuck...

Mike Charney

Hugo Kornelis <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in
news:k0jtk1dd153rcs6fvv6eb5le1rukn57qot@.4ax.com:

> On Thu, 13 Oct 2005 20:34:14 GMT, Mike wrote:
>>Ok I fix those now I have the following error
>>
>>--Begin Error Msg--
>>Server: Msg 4864, Level 16, State 1, Line 1
>>Bulk insert data conversion error (type mismatch) for row 1, column 14
>>(STDCOST).
>>--End Error Msg--
>>
>>The STDCOST is set to decimal (28,14) and is a formatted in Access as
>>a number, single with 14 decimal. I don't know why I would be getting
>>a Type Mismatch error.
>>
>>Any idea?
> Hi Mike,
> My guess is that there's some dirty data in the file you're trying to
> read in.
> You might want to double-check the data. And the format file you used.
> Best, Hugo|||On Thu, 13 Oct 2005 23:34:13 GMT, Mike wrote:

>Sorry for the double post. I dont know what I was thinking.
>I looked for bad data but I did not see any. With the column being set
>to numeric and singal in MS-Access, I dont think there could be any non-
>numeric data in the column. The only thought I would have is Null values
>being in the column but the SQL table is set to allow Null values.

Hi Mike,

I see in the other thread that you solved this problem and are now
fighting the next problem. Do post the information Erland requested. I'm
reading that thread as well, so it's quite likely that at least one of
us can spot the problem if you provide something for us to look at.

>What exactly is a Type Mismatch error? What does it mean? I thought it
>might mean I was trying to input non-numeric data into a numeric column,
>but I dont see any.

Yes, that is exactly what it means: the data in the input is not
compatible with the datatype in the table. Someting like "one" in a
numeric column, or "30 feb 2005" in a datetime column.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)